AP Receiving SLA Accounting Report

 AP Receiving SLA Accounting Report

SELECT Prd.Period_Name Period,

       Xah.Ledger_Id Ledger_Id,

       Lgr.Name Ledger_Name,

       hp.party_Name Vendor_Name,

       Sup.Segment1 Vendor_Number,

       Sups.Vendor_Site_Code Vendor_Site,

       hl.Country Vendor_Country_Code,

       Hdr.Segment1 Po_Number,

       Pol.Line_Num Po_Line_Num,

       Pol.Item_Description Po_Line_Desc,

       Pol.Quantity Po_Line_Quantity,

       Glcc.Segment1 Co,

       Glcc.Segment3 Gac,

       Glcc.Segment4 Lac,

       Glcc.Segment5 Dept,

       Glcc.Segment7 Ico,

       Glcc.Segment8 Proj,

       Glcc.Segment6 Bu,

       Shp.Receipt_Num Receipt_Number,

       Rcv.Transaction_Date Received_Date,

       Pol.Unit_Price Unit_Price,

       Rcv.Quantity Received_Quantity,

       Xal.Accounting_Class_Code Line_Type,

       Xal.Currency_Code Sla_Cy_Cd,

       (Nvl(Xal.Entered_Dr, 0) - Nvl(Xal.Entered_Cr, 0)) Entered_Net_Amt,

       (Nvl(Xal.Accounted_Dr, 0) - Nvl(Xal.Accounted_Cr, 0)) Accounted_Net_Amt,

       Btc.Name Gl_Batch_Name,

       Ghd.Name Gl_Header_Name,

       Gir.Je_Line_Num Gl_Je_Line_Num,

       --Hdr.Org_Id Po_Org_Id,

   hou.name po_org_id,

   --hdr.BILLTO_BU_ID Po_Org_Id,

       Xah.Accounting_Date Acctg_Date,

       Xah.Gl_Transfer_Status_Code Gl_Xfer_Status,

       Decode(Xlae.Event_Status_Code,

              'P',

              'Processed',

              'U',

              'Unprocessed',

              'I',

              'Incomplete',

              'N',

              'No Action',

              Xlae.Event_Status_Code) Event_Status,

       Xal.Description Je_Description,

       sysdate timestamp

  FROM Xla_Ae_Headers          Xah,

       Xla_Ae_Lines            Xal,

       Xla_Transaction_Entities Xte,

       Xla_Events              Xlae,

       Gl_Periods              Prd,

       Gl_Code_Combinations    Glcc,

       Gl_Ledgers              Lgr,

       --Ap_Suppliers            Sup,

   poz_suppliers    sup,

       --Ap_Supplier_Sites_All   Sups,

   poz_supplier_sites_all_m  sups,

       Rcv_Transactions        Rcv,

       Rcv_Shipment_Headers    Shp,

       Po_Headers_All          Hdr,

       Po_Lines_All            Pol,

       Gl_Import_References    Gir,

       Gl_Je_Batches           Btc,

       Gl_Je_Headers           Ghd,

       --Fnd_Application         Fa,

   hz_parties              hp,

   hz_locations    hl,

   hr_operating_units hou

 WHERE Glcc.Code_Combination_Id = Xal.Code_Combination_Id

   AND Lgr.Ledger_Id = Xah.Ledger_Id

   AND Xal.Ae_Header_Id = Xah.Ae_Header_Id

   AND Prd.Period_Set_Name = '4-4-5'

   --AND Prd.Period_Type = 'Month'

   AND Xah.Accounting_Date BETWEEN Prd.Start_Date AND Prd.End_Date

   AND Xlae.Event_Id = Xah.Event_Id

   AND Xte.Entity_Id = Xah.Entity_Id

   AND Xte.Entity_Id = Xlae.Entity_Id

   AND Xah.Application_Id = Xlae.Application_Id

   AND Rcv.Transaction_Id = Nvl(Xte.Source_Id_Int_1, -99)

   AND Shp.Shipment_Header_Id = Rcv.Shipment_Header_Id

   and hdr.prc_bu_id = hou.ORGANIZATION_id

   AND Hdr.Po_Header_Id = Rcv.Po_Header_Id

   AND Pol.Po_Line_Id = Rcv.Po_Line_Id

   AND Hdr.Vendor_Id = Sup.Vendor_Id

   AND Hdr.Vendor_Site_Id = Sups.Vendor_Site_Id

   AND Xte.Entity_Code = 'RCV_ACCOUNTING_EVENTS'

   AND Xte.Entity_Code  = 'RECEIVING'

   --AND Fa.Application_Id = Xah.Application_Id

   --AND Fa.Application_Id = Xte.Application_Id

   AND Xal.Application_Id = Xah.Application_Id

   --AND Fa.Application_Short_Name = 'CST'

   AND sup.party_id = hp.party_id

   AND Gir.Gl_Sl_Link_Id = Xal.Gl_Sl_Link_Id

   AND Gir.Gl_Sl_Link_Table = 'XLAJEL'

   AND Btc.Je_Batch_Id = Gir.Je_Batch_Id

   AND Ghd.Je_Header_Id = Gir.Je_Header_Id

 --  and Ghd.je_source =  'Receipt Accounting'

   AND sups.location_id = hl.location_id

   

   /*

   AND Prd.Period_Name = NVL(:P_PERIOD,Prd.Period_Name)

   AND Lgr.Name = NVL(:P_LEDGER,Lgr.Name)

   AND Glcc.Segment1 = NVL(:P_CO,Glcc.Segment1)

   --AND hdr.org_id = NVL(:P_ORG_ID,hdr.org_id)

   AND hp.party_Name = NVL(:P_VENDOR_NAME,hp.party_Name)

   AND Sup.Segment1 = NVL(:P_VENDOR_NUMBER,Sup.Segment1)

   AND Sups.Vendor_Site_Code = NVL(:P_VENDOR_SITE_CODE,Sups.Vendor_Site_Code)

   AND Hdr.Segment1 = NVL(:P_PO_NUM,Hdr.Segment1)

   AND Glcc.Segment3 = NVL(:P_GAC,Glcc.Segment3)

   AND Shp.Receipt_Num = NVL(:P_RECEIPT_NUM,Shp.Receipt_Num)

   AND Btc.Name = NVL(:P_NAME,Btc.Name)

   */

   

   

AND ( CASE

WHEN Prd.Period_Name IN ( :P_PERIOD ) THEN 1

WHEN ( coalesce(NULL, :P_PERIOD) IS NULL ) THEN 1

END = 1 )

AND ( CASE

WHEN Lgr.Name IN ( :P_LEDGER ) THEN 1

WHEN ( coalesce(NULL, :P_LEDGER) IS NULL ) THEN 1

END = 1 )

AND ( CASE

WHEN Glcc.Segment1 IN ( :P_CO ) THEN 1

WHEN ( coalesce(NULL, :P_CO) IS NULL ) THEN 1

END = 1 )

AND ( CASE

WHEN hdr.prc_bu_id IN ( :P_ORG_ID ) THEN 1

WHEN ( coalesce(NULL, :P_ORG_ID) IS NULL ) THEN 1

END = 1 )

AND ( CASE

WHEN hp.party_Name IN ( :P_VENDOR_NAME ) THEN 1

WHEN ( coalesce(NULL, :P_VENDOR_NAME) IS NULL ) THEN 1

END = 1 )

AND ( CASE

WHEN Sup.Segment1 IN ( :P_VENDOR_NUMBER ) THEN 1

WHEN ( coalesce(NULL, :P_VENDOR_NUMBER) IS NULL ) THEN 1

END = 1 )

AND ( CASE

WHEN Sups.Vendor_Site_Code IN ( :P_VENDOR_SITE_CODE ) THEN 1

WHEN ( coalesce(NULL, :P_VENDOR_SITE_CODE) IS NULL ) THEN 1

END = 1 )

AND ( CASE

WHEN Hdr.Segment1 IN ( :P_PO_NUM ) THEN 1

WHEN ( coalesce(NULL, :P_PO_NUM) IS NULL ) THEN 1

END = 1 )

AND ( CASE

WHEN Glcc.Segment3 IN ( :P_GAC ) THEN 1

WHEN ( coalesce(NULL, :P_GAC) IS NULL ) THEN 1

END = 1 )

AND ( CASE

WHEN Shp.Receipt_Num IN ( :P_RECEIPT_NUM ) THEN 1

WHEN ( coalesce(NULL, :P_RECEIPT_NUM) IS NULL ) THEN 1

END = 1 )

AND ( CASE

WHEN Btc.Name IN ( :P_NAME ) THEN 1

WHEN ( coalesce(NULL, :P_NAME) IS NULL ) THEN 1

END = 1 )