AP Onsemi Payment Distribution Report

 AP Onsemi Payment Distribution 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.Check_number 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,

     --Sup.Vendor_Name Vendor_Name,Infosys: replaced with oracle fusion column

SupplierPartyName.PARTY_NAME AS Vendor_Name,

     Sup.Segment1 Vendor_Num,

     Sups.Vendor_Site_Code Vendor_Site,

     --Sups.Country Vendor_Country_Cd,Infosys: replaced with oracle fusion column

SupplierLocation.COUNTRY 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, Infosys: replaced with oracle fusion segment.

     --Glcc.Segment2 Gac,Infosys: replaced with oracle fusion segment.

     --Glcc.Segment3 Lac,Infosys: replaced with oracle fusion segment.

     --Glcc.Segment4 Dept,Infosys: replaced with oracle fusion segment.

     --Glcc.Segment6 Proj,Infosys: replaced with oracle fusion segment.

  --Glcc.Segment1 CO,Infosys: replaced with oracle fusion segment.

  Glcc.Segment1 Co,

  Glcc.Segment3 Gac,

      Glcc.Segment2 Lac,

  Glcc.segment5 dept,

  Glcc.segment8 Program,

--Glcc.Segment4 Deprn_Exp_Local_Acct

--Glcc.segment6 PAL,

--Glcc.segment7 ICO,

     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 

--,

--SUPS.LOCATION_ID,

--SupplierLocation.LOCATION_ID locationid,

--Apc.Org_Id, -- REMOVE LATER,

,Bu.NAME Org

FROM XLA_AE_HEADERS                     XAH,

     XLA_AE_LINES                       XAL ,

     XLA_TRANSACTION_ENTITIES           XTE,

     XLA_EVENTS                         XLAE,

     GL_PERIODS                         PRD ,

     --GL_CODE_COMBINATIONS_kfv           GLCC, Infosys: Replaced with Gl_Code_Combinations in oracle fusion

Gl_Code_Combinations      GLCC,  

     GL_LEDGERS                         LGR,

     --AP_SUPPLIERS                       SUP,Infosys: Replaced with POZ_SUPPLIERS in oracle fusion

     POZ_SUPPLIERS                           SUP,  

     --AP_SUPPLIER_SITES_ALL              SUPS, Infosys: Replaced with POZ_SUPPLIER_SITES_ALL_M in oracle fusion

POZ_SUPPLIER_SITES_ALL_M SUPS,

     AP_CHECKS_ALL                      APC,

     GL_IMPORT_REFERENCES               GIR,

     GL_JE_BATCHES                      BTC,

     GL_JE_HEADERS                      GHD ,

HZ_PARTIES SupplierPartyName,

HZ_LOCATIONS SupplierLocation,

HR_ORGANIZATION_V Bu

WHERE 

1=1

AND 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 UPPER(PRD.PERIOD_TYPE)      like '%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 SUP.VENDOR_ID(+)           = XAL.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 APC.CHECK_ID               = NVL(XTE.SOURCE_ID_INT_1,-99)

  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 SUP.PARTY_ID = SupplierPartyName.PARTY_ID(+)

  AND SUPS.LOCATION_ID=SupplierLocation.LOCATION_ID(+)

  AND Apc.Org_Id = Bu.ORGANIZATION_ID(+) and trunc(sysdate) BETWEEN Bu.EFFECTIVE_START_DATE(+) AND Bu.EFFECTIVE_END_DATE(+)

  AND Bu.CLASSIFICATION_CODE(+) = 'FUN_BUSINESS_UNIT'

  

--%IF ':PARM01' = '' %THENDO; 

--%ELSE;

--AND (Prd.Period_Name :SPARM01)

--%ENDIF;

  AND NVL(Prd.Period_Name,'xxxxx') = CASE WHEN :P_PERIOD IS NULL THEN NVL(Prd.Period_Name,'xxxxx') ELSE :P_PERIOD END 

--%IF ':PARM02' = '' %THENDO; 

--%ELSE;

--AND Xah.Ledger_Id IN

--     (SELECT Gl.Ledger_Id FROM Gl_Ledgers Gl WHERE (Gl.Name :SPARM02))

--%ENDIF;

  AND NVL(Xah.Ledger_Id,'xxxxx') = CASE WHEN :P_LEDGER_NAME IS NULL THEN NVL(Xah.Ledger_Id,'xxxxx') ELSE (SELECT Gl.Ledger_Id FROM Gl_Ledgers Gl WHERE (Gl.Name=:P_LEDGER_NAME)) END

 --%IF ':PARM03' = '' %THENDO; 

 --%ELSE;

 --AND (Glcc.Segment1 :SPARM03)

 --%ENDIF;

  AND NVL(Glcc.Segment1,'xxxxx') = CASE WHEN :P_CO IS NULL THEN NVL(Glcc.Segment1,'xxxxx') ELSE :P_CO END

 

 --%IF ':PARM04' = '' %THENDO; 

 --%ELSE;

 --AND (Apc.Org_Id :SPARM04)

 --%ENDIF;

  AND NVL(Bu.NAME,'xxxxx') = CASE WHEN :P_BU IS NULL THEN NVL(Bu.NAME,'xxxxx') ELSE :P_BU END

 

 --%IF ':PARM05' = '' %THENDO; 

 --%ELSE;

 --AND (Apc.Bank_Account_Name :SPARM05)

 --%ENDIF;

  AND NVL(Apc.Bank_Account_Name,'xxxxx') = CASE WHEN :P_BANK_ACCT IS NULL THEN NVL(Apc.Bank_Account_Name,'xxxxx') ELSE :P_BANK_ACCT END

 

 --%IF ':PARM06' = '' %THENDO; 

 --%ELSE;

 --AND (Apc.Status_Lookup_Code :SPARM06)

 --%ENDIF;

  AND NVL(Apc.Status_Lookup_Code,'xxxxx') = CASE WHEN :P_PAYMENT_STATUS IS NULL THEN NVL(Apc.Status_Lookup_Code,'xxxxx') ELSE :P_PAYMENT_STATUS END

 

 --%IF ':PARM07' = '' %THENDO; 

 --%ELSE;

 --AND (Glcc.Segment2 :SPARM07)

 --%ENDIF;

  AND NVL(Glcc.Segment3,'xxxxx') = CASE WHEN :P_GAC IS NULL THEN NVL(Glcc.Segment3,'xxxxx') ELSE :P_GAC END

 

 --%IF ':PARM08' = '' %THENDO; 

 --%ELSE;

 --AND (Apc.Check_Number :SPARM08)

 --%ENDIF;

  AND NVL(Apc.Check_Number,'xxxxx') = CASE WHEN TO_NUMBER(:P_PAYMENT_NUM) IS NULL THEN NVL(Apc.Check_Number,'xxxxx') ELSE TO_NUMBER(:P_PAYMENT_NUM) END

 

 --%IF ':PARM09' = '' %THENDO; 

 --%ELSE;

 --AND (Sup.Segment1 :SPARM09)

 --%ENDIF;

  AND NVL(Sup.Segment1,'xxxxx') = CASE WHEN :P_VENDOR_NUM IS NULL THEN NVL(Sup.Segment1,'xxxxx') ELSE :P_VENDOR_NUM END

 

 --%IF ':PARM10' = '' %THENDO; 

 --%ELSE;

 --AND (Glcc.Segment6 :SPARM10)

 --%ENDIF;

  AND NVL(Glcc.segment8,'xxxxx') = CASE WHEN :P_PROGRAM IS NULL THEN NVL(Glcc.segment8,'xxxxx') ELSE :P_PROGRAM END

  

  --AND to_char(Apc.Cleared_Date,'MM/DD/YYYY')  is not null

  --AND Ghd.Name = 'OCT-24 Payments'

  --AND Glcc.Segment3=21215

  --AND Xal.Description = 'Invoice Number : APIN1021-7'

order by Prd.Period_Name,Apc.Check_Number,Lgr.Name,Btc.Name,Gl_Header_Name,Gir.Je_Line_Num