1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

warehouse recieving report

Discussion in 'Oracle Apps Technical' started by rogerbnny, Feb 23, 2009.

  1. rogerbnny

    rogerbnny Active Member

    Messages:
    7
    Likes Received:
    0
    Trophy Points:
    75
    SELECT
    RSH.RECEIPT_NUM RECEIPT_NUM,
    RT.TRANSACTION_DATE TRX_DATE,
    POH.SEGMENT1 PO_NUM,
    POH.CREATION_DATE PO_DATE,
    POV.VENDOR_NAME VENDOR_NAME,
    RSH.VENDOR_ID,
    MTL.INVENTORY_ITEM_ID ITEM_ID,
    MTL.SEGMENT1||'.'||MTL.SEGMENT2 I| '.'|MTL.SEGMENT3||'.'||MTL.SEGMENT4 ITEM_CODE,
    RSL.ITEM_DESC ITM_DESC,
    RSL.TRANSACT_UOM UOM,
    POL.QUANTITY PO_QTY,
    POL.UNIT_PRICE RATE,(POL.UNIT_PRICE*POL.QUANTITY) TOTAL,
    OOD.ORGANIZATION_CODE||' '||OOD.ORGANIZATION_NAME

    FROM

    RCV_SHIPMENT_HEADERS RSH,
    RCV_TRANSACTIONS RT,
    PO_HEADERS_ALL POH,
    PO_VENDORS POV,
    PO_LINES_ALL POL,
    RCV_SHIPMENT_LINES RSL,
    ORG_ORGANIZATION_DEFINITIONS OOD,
    MTL_SYSTEM_ITEMS_B ITM

    WHERE

    RSH.ORGANIZATION_ID = ITM.ORGANIZATION_ID
    AND POH.PO_HEADER_ID = RSL.PO_HEADER_ID
    AND POH.PO_HEADER_ID = POL.PO_HEADER_ID
    AND POL.PO_LINE_ID = RSL.PO_LINE_ID
    AND RSL.ITEM_ID = MTL.INVENTORY_ITEM_ID


    ORDER BY
    TRX_DATE desc,
    RECEIPT_NUM



    FOR LOCN ORG_ID||ORGANIZATION NAME

    L340 IS ORG_ID
    ORGANIZATIONNAME

    THE LAST COMBINATION AND RSL.ITEM_ID = MTL.INVENTORY_ITEM_ID IS CORRECT OR NOT?
    can any body correct this query by looking at this report
     

    Attached Files:

    • RR.jpg
      RR.jpg
      File size:
      88.8 KB
      Views:
      21
  2. tyro

    tyro Forum Genius

    Messages:
    368
    Likes Received:
    20
    Trophy Points:
    260
    Location:
    India
    Re: wharehouse recieving report

    hi, i have no idea about what you are trying to do but i just corrected your select query where you had some character errors. This select statement will work.
    Code (Text):

    SELECT   rsh.receipt_num receipt_num, rt.transaction_date trx_date,
             poh.segment1 po_num, poh.creation_date po_date,
             pov.vendor_name vendor_name, rsh.vendor_id,
             mtl.inventory_item_id item_id,
                mtl.segment1
             || '.'
             || mtl.segment2
             || '.'
             || mtl.segment3
             || '.'
             || mtl.segment4 item_code,
             rsl.item_desc itm_desc, rsl.transact_uom uom, pol.quantity po_qty,
             pol.unit_price rate, (pol.unit_price * pol.quantity) total,
             ood.organization_code || ' ' || ood.organization_name
        FROM rcv_shipment_headers rsh,
             rcv_transactions rt,
             po_headers_all poh,
             po_vendors pov,
             po_lines_all pol,
             rcv_shipment_lines rsl,
             org_organization_definitions ood,
             mtl_system_items_b itm
       WHERE rsh.organization_id = itm.organization_id
         AND poh.po_header_id = rsl.po_header_id
         AND poh.po_header_id = pol.po_header_id
         AND pol.po_line_id = rsl.po_line_id
         AND rsl.item_id = mtl.inventory_item_id
    ORDER BY trx_date DESC, receipt_num
     
  3. Sadik

    Sadik Community Moderator Forum Guru

    Messages:
    1,906
    Likes Received:
    252
    Trophy Points:
    1,455
    Hi you really need to explain more what you want help witj. As tyro has corrected your select query, see if that resolves your problem. If you are having some issue with not getting proper data, you need to tell us exactly what you are trying to do and what is the exact problem.
     
  4. rajavu

    rajavu Forum Guru

    Messages:
    815
    Likes Received:
    52
    Trophy Points:
    610
    Location:
    @ Bangalore , India
    Moreover , you are using 8 tables in From clause and and only 5 joins used in the where clause and only 6 . You need to use at least seven exclusive Joins to avoid Cartesian product in the result unless you want to display some single row table fields.

    Best thing is to ensure your self it return what you need by executing the query for some predefined entity. Or provide a test case here with Create and insert script to produce the issue.