Receipt History Report

SELECT /*+ parallel(12) */
    acra.receipt_number,
    acra.amount AS receipt_amount,
    rb.unapplied_balance AS unapplied_onaccount_balance,
    acra.CURRENCY_CODE AS entered_currency,
    hou.name AS business_unit,
    cc.segment1 AS Legal_Entity,
    le_name.description AS Legal_Entity_Name,
    hca.account_number AS customer_number,
    hp.party_name AS customer_name,
    hcsua.location AS Bill_to_location,
    arm.name AS Receipt_method,
    TO_CHAR(acrha.GL_DATE, 'MM/DD/YYYY') AS GL_DATE,
    TO_CHAR(acra.receipt_date, 'MM/DD/YYYY') AS receipt_date,
    araa.application_ref_reason,
    (CASE WHEN rcta.customer_trx_id IS NULL OR rcta.customer_trx_id = -1 
          THEN araa.customer_reference 
          ELSE rcta.customer_reference 
     END) AS Customer_Reference
    ,TO_CHAR(araa.apply_date, 'MM/DD/YYYY') AS apply_date
	,nvl( (select CLASS from ar_payment_schedules_all 
                    where  TRX_NUMBER not in ('Claim Investigation','Receipt Write-off','On Account','Refund')
                     and payment_schedule_id =araa.applied_payment_schedule_id )
    ,apsa1.trx_number) Application_Type   -- Transaction/Application Type

--,nvl(araa.APPLICATION_REF_NUM, apsa1.trx_number) Application_Reference 
-- applicaiton reference logic is if Application type = application Reference then display receivable activity

, nvl((case when  ( nvl( (select CLASS from ar_payment_schedules_all  where  TRX_NUMBER not in ('Claim Investigation','Receipt Write-off','On Account')
                     and payment_schedule_id =araa.applied_payment_schedule_id )
              ,apsa1.trx_number) = nvl(araa.APPLICATION_REF_NUM, apsa1.trx_number) )
	    then nvl((select NAME	from AR_RECEIVABLES_TRX_ALL	where RECEIVABLES_TRX_ID =araa.RECEIVABLES_TRX_ID and org_id=araa.org_id ),araa.APPLICATION_REF_NUM)
		else
		        nvl((select araa.APPLICATION_REF_NUM from dual where araa.RECEIVABLES_TRX_ID is not null) , apsa1.trx_number)
		end )
      ,apsa1.trx_number )Application_Reference
	  
    ,araa.amount_applied
    ,to_char(rcta.trx_date,'MM/DD/YYYY') Transaction_Date
	,(case when rcta.customer_trx_id is not null 
      then to_char(apsa1.due_date,'MM/DD/YYYY') END  ) Transaction_due_date
	,(select sum (AMOUNT_DUE_ORIGINAL) from ar_payment_schedules_all where CUSTOMER_TRX_ID=rcta.customer_trx_id ) Transaction_Original_Amount  
    ,NVL(araa.earned_discount_taken, 0) AS earned_discount_taken
	,nvl(apsa1.amount_adjusted,0)amount_adjusted
    ,NVL(apsa1.amount_due_remaining, 0) AS amount_due_remaining
	,araa.application_ref_type
	,rcta.ct_reference application_ref_num_trx
   ,rcta.customer_trx_id
,cc.segment1 company_segment
,acra.cash_receipt_id
,cc.code_combination_id
,acrha.account_code_combination_id
FROM 
    AR_RECEIVABLE_APPLICATIONS_ALL araa
    INNER JOIN ar_cash_receipts_all acra ON araa.cash_receipt_id = acra.cash_receipt_id
    INNER JOIN ar_cash_receipt_history_all acrha ON acra.cash_receipt_id = acrha.cash_receipt_id 
         AND acrha.current_record_flag = 'Y'
    INNER JOIN ar_receipt_methods arm ON arm.receipt_method_id = acra.receipt_method_id
    INNER JOIN hr_operating_units hou ON hou.organization_id = acra.org_id
    INNER JOIN hz_cust_accounts hca ON acra.pay_from_customer = hca.cust_account_id
    INNER JOIN hz_parties hp ON hca.party_id = hp.party_id
    INNER JOIN gl_code_combinations cc ON araa.code_combination_id = cc.code_combination_id
    LEFT JOIN RA_CUSTOMER_TRX_ALL rcta ON araa.applied_customer_trx_id = rcta.customer_trx_id
    LEFT JOIN AR_PAYMENT_SCHEDULES_ALL apsa1 ON araa.applied_payment_schedule_id = apsa1.payment_schedule_id
    LEFT JOIN HZ_CUST_SITE_USES_ALL hcsua ON acra.CUSTOMER_SITE_USE_ID = hcsua.site_use_id
    -- Optimized: Pre-calculate unapplied sums
    LEFT JOIN (
        SELECT cash_receipt_id, SUM(Amount_Applied) AS unapplied_balance
        FROM Ar_Receivable_Applications_All 
        WHERE STATUS IN ('UNAPP','UNID','ACC')
        GROUP BY cash_receipt_id
    ) rb ON acra.cash_receipt_id = rb.cash_receipt_id
    -- Optimized: Use LATERAL for complex description lookups to keep it in the parallel stream
    OUTER APPLY (
        SELECT ffv.description 
        FROM FND_ID_FLEX_SEGMENTS_VL ffs
        JOIN fnd_flex_values_vl ffv ON ffs.FLEX_VALUE_SET_ID = ffv.FLEX_VALUE_SET_ID
        JOIN gl_ledgers gl ON gl.chart_of_accounts_id = ffs.ID_FLEX_NUM
        JOIN GL_LEDGER_NORM_SEG_VALS leseg ON leseg.ledger_id = gl.ledger_id AND leseg.SEGMENT_VALUE = ffv.flex_value
        WHERE ffs.segment_name = 'Entity' 
          AND hou.SET_OF_BOOKS_ID = gl.LEDGER_ID 
          AND ffv.flex_value = cc.segment1 
          AND ROWNUM = 1
    ) le_name
WHERE 
    araa.display = 'Y'
    -- Data Security Join
    AND EXISTS (
        SELECT 1 FROM fusion.fun_all_business_units_v bu
        JOIN fusion.fun_user_role_data_asgnmnts role ON role.org_id = bu.bu_id
        JOIN fusion.per_users pu ON pu.user_guid = role.user_guid
		JOIN fusion.per_roles_dn_vl pr ON pr.role_common_name = role.ROLE_NAME
        JOIN fusion.per_roles_dn_tl prtl ON pr.role_id=prtl.role_id  and prtl.LANGUAGE='US'
        WHERE bu.bu_id = acra.org_id
          AND role.active_flag = 'Y' 
          AND pu.username = fnd_global.user_name
		  and prtl.ROLE_NAME  in  ('XXC Accounts Receivable Analyst'
						,'XXC Accounts Receivable Manager'
						,'XXC Receivables Inquiry'
						,'XXC Billing Manager'
						,'XXC Billing Analyst'
						)
    )
    -- Parameter Joins
    AND (acra.org_id IN (:p_org_id) OR 'All' IN (:p_org_id || 'All'))
    AND (cc.segment1 IN (:P_ENTITY) OR 'All' IN (:P_ENTITY || 'All'))
    AND (hca.account_number IN (:p_cust_num) OR 'All' IN (:p_cust_num || 'All'))
    AND araa.apply_date BETWEEN NVL(:P_DATE_FROM, araa.apply_date) AND NVL(:P_DATE_TO, araa.apply_date)
    AND acra.receipt_date BETWEEN NVL(:P_RECEIPT_DATE_FROM, acra.receipt_date) AND NVL(:P_RECEIPT_DATE_TO, acra.receipt_date)
    AND (arm.name IN (:p_receipt_method) OR 'All' IN (:p_receipt_method || 'All'))
    AND acra.receipt_number = NVL(:p_receipt_number, acra.receipt_number)
ORDER BY hou.name, cc.segment1, hp.party_name, acra.receipt_number, acra.receipt_date, araa.apply_date