3 Replies Latest reply on Apr 8, 2011 7:39 AM by 669818

    Receiving Transactions Register

    669818
      Hi,

      Does anyone have an SQL to replicate the core Oracle report 'Receiving Transactions Register' in Discoverer?

      Many thanks
        • 1. Re: Receiving Transactions Register
          669818
          I have the following report but does not give me the Receipted cost:

          SELECT

          a.Organization_ID,
          a.Transaction_ID                               Receipt_Id,
          substr(a.Creation_Date ,1,10)                         Receipt_Creation_Date,
          substr(a.Creation_Date,4,6)                    Creation_Month,
          a.Created_By                                   Creator_User_ID,
          b.Description                                   Created_By,
          a.Transaction_Type,
          a.Transaction_Date                          Receipt_Transaction_Date,
          a.Quantity                                   Quantity_Received,
          a.Unit_of_Measure,
          d.Item_Description                              Order_Line_Description,
          a.PO_Line_ID,
          a.Vendor_ID,
          c.Vendor_Name,
          d.ITEM_ID,
          e.PO_NUM,
          msib.segment1 item,
          msib.description

          FROM


          PO.RCV_Transactions a,
          APPLSYS.FND_User b,
          PO.PO_Vendors c,
          PO.PO_Lines_All d,
          APPS.RCV_VRC_TXS_V e,
          mtl_system_items_b msib

          WHERE


          a.Created_By = b.User_ID
          and
          a.Vendor_ID = c.Vendor_ID
          and
          a.PO_Line_ID = d.PO_Line_ID
          and
          e.PO_Line_ID = a.PO_Line_ID
          and
          e.item_id = msib.inventory_item_id(+)
          and
          msib.segment1 IS NOT NULL
          and
          c.vendor_name not like '%~%'
          • 2. Re: Receiving Transactions Register
            Sandeep Gandhi, Consultant
            Try a.Quantity * d.unit_price

            A suggestion - use meaningful aliases such as fu > fnd_user, po_lines_all > pla or lines instead of a,b,c etc.

            Hope this answers your question,
            Sandeep Gandhi
            • 3. Re: Receiving Transactions Register
              669818
              Great Thanks