AP Payment SLA Accounting Report

 AP Payment SLA Accounting Report

SELECT Prd.Period_Name Period,

       to_char(Xah.Accounting_Date, 'MM/DD/YYYY')   Acctg_Date,

       Lgr.Name Ledger_Name,

       Apc.Check_Number Payment_Number,

       Apc.Checkrun_Name Payment_Batch_Name,

       to_char(Apc.Check_Date, 'MM/DD/YYYY') Payment_Date,

       Apc.Amount Payment_Amt,

       Apc.Currency_Code Pmt_Curr_Code,

       Apc.Bank_Account_Name Bank_Acct,

       Apc.Status_Lookup_Code Payment_Status,

       Apc.Cleared_Amount Cleared_Amount,

       TO_CHAR(Apc.Cleared_Date,'MM/DD/YYYY') Cleared_Date,

       Apc.Check_Id Check_Id,

       Apc.Payment_Type_Flag Payment_Type,

       (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,

       hp.party_Name Vendor_Name,

       Sup.Segment1 Vendor_Num,

       Sups.Vendor_Site_Code Vendor_Site,

     --  Sups.Country_of_origin_code Vendor_Country_Cd,

(select distinct country from HZ_LOCATIONS hl

where hl.location_id = sups.location_id) Vendor_Country_Cd,

       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,

       Glcc.Segment1 Co,

   Glcc.Segment2 Loc,

       Glcc.Segment3 Gac,

       Glcc.Segment4 Lac,

       Glcc.Segment5 Dept,

       Glcc.Segment8 Proj,

       Glcc.Concatenated_Segments Acct_Code_Comb,

       Xal.Ae_Line_Num Ae_Line_Num,

       Xal.Accounting_Class_Code Line_Type,

       Xal.Description Je_Description,

       Xal.Currency_Code Sla_Curr_Code,

       Btc.Name Gl_Batch_Name,

       Ghd.Name Gl_Header_Name,

       Gir.Je_Line_Num Gl_Je_Line_Num,

       to_char(sysdate, 'MM/DD/YYYY HH12:MI:SS AM') 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,

     poz_SUPPLIERS                       SUP, 

hz_parties                          hp,

     poz_SUPPLIER_SITES_ALL_m            SUPS,

     AP_CHECKS_ALL                      APC,

     GL_IMPORT_REFERENCES               GIR,

     GL_JE_BATCHES                      BTC,

     GL_JE_HEADERS                      GHD 

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            = 'MONTH8731158251'

  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 APC.CHECK_ID               = NVL(XTE.SOURCE_ID_INT_1,-99) 

  AND SUP.VENDOR_ID(+)           = XAL.PARTY_ID

  and sup.party_id               = hp.party_id

  and apc.vendor_id              = SUP.vendor_id

  and apc.vendor_site_id         = SUPS.vendor_site_id

  and SUP.vendor_id              = SUPS.vendor_id

  AND XTE.ENTITY_CODE            = 'AP_PAYMENTS'

  --AND XAH.APPLICATION_ID         = 200

  --AND XAL.APPLICATION_ID         = 200

  --AND XTE.APPLICATION_ID         = 200

  --AND XLAE.APPLICATION_ID        = 200

  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 Prd.Period_Name  = nvl(:P_PERIOD_NAME,Prd.Period_Name)

  AND Xah.Ledger_Id IN

       (SELECT Gl.Ledger_Id FROM Gl_Ledgers Gl WHERE Gl.Name = nvl(:P_LEDGER_NAME,Gl.Name) )

  AND Glcc.Segment1 =  nvl(:P_COMPANY,Glcc.Segment1)

AND Apc.Org_Id = (select Organization_Id FROM  HR_ORGANIZATION_UNITS hou where hou.Organization_Id= nvl(:P_ORG_ID,Apc.Org_Id)) 

  AND Apc.Bank_Account_Name = nvl(:P_BANK_ACCOUNT_NAME,Apc.Bank_Account_Name)

  AND Apc.Status_Lookup_Code = nvl(:P_CHECK_STATUS_CODE,Apc.Status_Lookup_Code)

  AND Glcc.Segment3 = nvl(:P_GLOBAL_ACCT,Glcc.Segment3 )

  AND Apc.Check_Number = nvl(:P_CHECK_NUMBER,Apc.Check_Number)

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

  AND Glcc.Segment8  = nvl(:P_PROJECT,Glcc.Segment8)