AR Cash Application Summary Report

 AR Cash Application Summary Report

SELECT AL1.Company_Code CO,

       AL1.Currency_Code CY_CO,

       AL1.Application_Type,

   AL1.Period,

       SUM(AL1.Amount_Applied) Amount_Applied,

       SUM(AL1.Earned_Discount_Taken) Earned_Discount_Taken,

       SUM(AL1.Unearned_Discount_Taken) Unearned_Discount_Taken,

       SUM(AL1.Receivable_Charges_Applied) Receivable_Charges_Applied,

(to_char(sysdate,'mm/dd/yyyy hh:mi:ss AM')) timestamp

  FROM (SELECT CDE.SEGMENT1                                 Company_Code,

       PRD.PERIOD_NAME                              Period,

       APL.APPLICATION_TYPE                         Application_Type,

       RCP.CURRENCY_CODE                            Currency_Code,

       APL.AMOUNT_APPLIED                           Amount_Applied,

       APL.EARNED_DISCOUNT_TAKEN                    Earned_Discount_Taken,

       APL.UNEARNED_DISCOUNT_TAKEN                  Unearned_Discount_Taken,

       APL.RECEIVABLES_CHARGES_APPLIED              Receivable_Charges_Applied

FROM AR_CASH_RECEIPTS_ALL            RCP, 

     AR_PAYMENT_SCHEDULES_ALL        PMT, 

     AR_RECEIVABLE_APPLICATIONS_ALL  APL,

     AR_RECEIVABLES_TRX_ALL          RTX,

     AR_RECEIPT_METHODS              MET,

     RA_CUSTOMER_TRX_ALL             CTX,  

     GL_CODE_COMBINATIONS            CDE, 

     GL_PERIODS                      PRD, 

     HZ_CUST_ACCOUNTS                CAC,

     HZ_CUST_SITE_USES_ALL           CST,

     HZ_CUST_ACCT_SITES_ALL          SIT,

     HZ_CUST_SITE_USES_ALL           CSH,

     HZ_PARTIES                      PAR,

     HZ_PARTY_SITES                  PTS,

     HZ_LOCATIONS                    LOC,

PER_USERS                       PSR

     --FND_USER                        USR

WHERE RCP.CASH_RECEIPT_ID(+)             = APL.CASH_RECEIPT_ID

  and PMT.PAYMENT_SCHEDULE_ID            = APL.APPLIED_PAYMENT_SCHEDULE_ID

  and CDE.CODE_COMBINATION_ID            = APL.CODE_COMBINATION_ID

  and CAC.CUST_ACCOUNT_ID                = PMT.CUSTOMER_ID

  and APL.GL_DATE BETWEEN PRD.START_DATE AND PRD.END_DATE

  AND PRD.PERIOD_SET_NAME                = '4-4-5'

  AND PRD.PERIOD_TYPE                    like 'MONTH%'

  AND APL.DISPLAY                        = 'Y'

  AND CST.SITE_USE_ID(+)                 = PMT.CUSTOMER_SITE_USE_ID 

  and CSH.SITE_USE_ID(+)                 = CTX.SHIP_TO_SITE_USE_ID

  and RTX.RECEIVABLES_TRX_ID(+)          = APL.RECEIVABLES_TRX_ID 

  and MET.RECEIPT_METHOD_ID(+)           = RCP.RECEIPT_METHOD_ID

  AND PAR.PARTY_ID(+)                    = CAC.PARTY_ID

  AND CTX.CUSTOMER_TRX_ID(+)             = PMT.CUSTOMER_TRX_ID

  AND PSR.USERNAME                       = APL.CREATED_BY

  AND SIT.CUST_ACCT_SITE_ID(+)           = CST.CUST_ACCT_SITE_ID

  AND PTS.PARTY_SITE_ID(+)               = SIT.PARTY_SITE_ID

  AND LOC.LOCATION_ID(+)                 = PTS.LOCATION_ID) AL1

 WHERE 1=1

  AND AL1.Application_Type = nvl(:p_application_Type, AL1.Application_Type)

  AND AL1.company_code = nvl(:p_company_code, AL1.company_code)

  AND AL1.PERIOD = nvl(:p_period, AL1.PERIOD)

 GROUP BY AL1.Company_Code, AL1.Currency_Code, AL1.Application_Type,AL1.Period

 ORDER BY Company_code, Currency_Code, Application_type,Period