Cash Settlement Report

SELECT HCA.ACCOUNT_NUMBER CUSTOMER_NUMBER
	,HP.PARTY_NAME CUSTOMER_NAME
	,HL.ADDRESS1 CUSTOMER_ADDRESS1
	,HL.ADDRESS2 CUSTOMER_ADDRESS2
	,HL.ADDRESS3 CUSTOMER_ADDRESS3
	,HL.CITY CUSTOMER_CITY
	,HL.STATE CUSTOMER_STATE
	,HL.PROVINCE CUSTOMER_PROVINCE
	,HL.POSTAL_CODE CUSTOMER_POSTAL_CODE
	,HCA.CUSTOMER_CLASS_CODE CUSTOMER_CLASS
	,RCTT.NAME DOCUMENT_TYPE
	,APSA.TRX_NUMBER DOCUMENT_NUMBER
	,ACIA.CONS_BILLING_NUMBER CONS_BILLING_NUMBER
	,TO_CHAR(APSA.TRX_DATE, 'MM/DD/YYYY' ) DOCUMENT_DATE
	,TO_CHAR(APSA.DUE_DATE, 'MM/DD/YYYY') DOCUMENT_DUE_DATE
	,DECODE(APSA.STATUS, 'CL', 'CLOSED', 'OP', 'OPEN', APSA.STATUS) DOCUMENT_STATUS
	,TO_CHAR(ARAA.GL_DATE, 'MM/DD/YYYY') GL_DATE
	,RCTL.LINE_NUMBER LINE_NUMBER
	,DECODE(RCTLD.ACCOUNT_CLASS, 'REV', 'REVENUE', 'TAX', 'TAX', RCTLD.ACCOUNT_CLASS) GL_ACCT_TYPE
	,GCC.SEGMENT1 || '.' || GCC.SEGMENT2 || '.' || GCC.SEGMENT3 || '.' || GCC.SEGMENT4 || '.' || GCC.SEGMENT5 || '.' || GCC.SEGMENT6 || '.' || GCC.SEGMENT7 || '.' || GCC.SEGMENT8 || '.' || GCC.SEGMENT9 GL_ACCOUNT
	,TO_CHAR(ACR.DEPOSIT_DATE, 'MM/DD/YYYY') DEPOSIT_DATE
	,TO_CHAR(ARAA.APPLY_DATE, 'MM/DD/YYYY') APPLY_DATE
	,ACR.CURRENCY_CODE ENTERED_CURRENCY
	,CASE 
		WHEN REC_AMT_TEMP.REC_AMOUNT = 0
			THEN 0
		ELSE ROUND((RCTLD.AMOUNT / REC_AMT_TEMP.REC_AMOUNT) * DECODE(ARAA.AMOUNT_APPLIED, 0, 0, ARAA.AMOUNT_APPLIED), 2)
		END ENTERED_CURRENCY_AMT_APPLIED
	,(
		SELECT CURRENCY_CODE
		FROM GL_LEDGERS
		WHERE LEDGER_ID = RCT.SET_OF_BOOKS_ID
		) FUNCTIONAL_CURRENCY
	,CASE 
		WHEN REC_AMT_TEMP.REC_ACCTD_AMOUNT = 0
			THEN 0
		ELSE ROUND((RCTLD.ACCTD_AMOUNT / REC_AMT_TEMP.REC_ACCTD_AMOUNT) * DECODE(ARAA.ACCTD_AMOUNT_APPLIED_FROM, 0, 0, ARAA.ACCTD_AMOUNT_APPLIED_FROM), 2)
		END ALLOCATED_RECEIPT_AMOUNT_BASE
	,ACR.RECEIPT_NUMBER RECEIPT_NUMBER
	,TO_CHAR(ACR.RECEIPT_DATE, 'MM/DD/YYYY') RECEIPT_DATE
	--,DECODE(ARAA.APPLICATION_TYPE, 'CM', RCTT.NAME, ARM.NAME) RECEIPT_TYPE
	 ,ARM.NAME Receipt_Method
	,REPLACE(ACR.COMMENTS, CHR(10), '') RECEIPT_COMMENTS
	--,RCTL.INTERFACE_LINE_ATTRIBUTE1 REFERENCE
	,RCT.STRUCTURED_PAYMENT_REFERENCE REFERENCE
	--,DECODE(GCC.SEGMENT2, '000', RCTL.INTERFACE_LINE_ATTRIBUTE2, (GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID, 2, GCC.SEGMENT2))) BRAND
	,(GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID,6,GCC.SEGMENT6))BRAND
	/* ,(
		SELECT SALESREP_NUMBER
		FROM jtf_rs_salesreps --RA_SALESREPS_ALL
		WHERE SALESREP_ID = RCT.PRIMARY_SALESREP_ID
		) LOB */
	,(GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID,2,GCC.SEGMENT2)) SELLING_METHOD
	,(GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(GCC.CHART_OF_ACCOUNTS_ID,5,GCC.SEGMENT5)) LOCATION
	,RCTL.INTERFACE_LINE_ATTRIBUTE3 TERRITORY
	--,RCT.PURCHASE_ORDER CONTRACT
	,RCTL.INTERFACE_LINE_ATTRIBUTE1 CONTRACT
	--,RCTL.CUSTOMER_TRX_ID CUSTOMER_TRX_ID
	,(select location from hz_cust_site_uses_all  where SITE_USE_ID = rct.BILL_TO_SITE_USE_ID) Bill_to_Location
	,RCT.purchase_order PO_Number
FROM (
	SELECT AMOUNT REC_AMOUNT
		,ACCTD_AMOUNT REC_ACCTD_AMOUNT
		,CUSTOMER_TRX_ID
	FROM RA_CUST_TRX_LINE_GL_DIST_ALL
	WHERE ACCOUNT_CLASS = 'REC'
	) REC_AMT_TEMP
	,HZ_PARTIES HP
	,HZ_PARTY_SITES HPS
	,HZ_LOCATIONS HL
	,HZ_CUST_ACCOUNTS HCA
	,GL_CODE_COMBINATIONS GCC
	,AR_CASH_RECEIPTS_ALL ACR
	,AR_PAYMENT_SCHEDULES_ALL APSA
	,AR_RECEIVABLE_APPLICATIONS_ALL ARAA
	,AR_CONS_INV_ALL ACIA
	,AR_RECEIPT_METHODS ARM
	,RA_CUSTOMER_TRX_ALL RCT
	,RA_CUSTOMER_TRX_LINES_ALL RCTL
	,RA_CUST_TRX_TYPES_ALL RCTT
	,RA_CUST_TRX_LINE_GL_DIST_ALL RCTLD
	,AR_CASH_RECEIPT_HISTORY_ALL ACRHA
    ,HR_OPERATING_UNITS HOU
WHERE 1 = 1
    AND EXISTS (SELECT 1
						FROM 
						 fusion.fun_user_role_data_asgnmnts role
						,fusion.per_users pu
						,fusion.per_roles_dn_vl pr
						WHERE 1=1
						AND role.org_id = hou.organization_id
						AND pu.user_guid = role.user_guid
						AND pr.role_common_name = role.ROLE_NAME
						and role.active_flag ='Y' 
						and pu.username = fnd_global.user_name)
    AND HOU.ORGANIZATION_ID= RCT.ORG_ID
	AND ARAA.CASH_RECEIPT_ID = ACR.CASH_RECEIPT_ID
	AND ACRHA.CASH_RECEIPT_ID = ARAA.CASH_RECEIPT_ID
	AND ACRHA.CURRENT_RECORD_FLAG='Y'
	AND ACR.RECEIPT_METHOD_ID = ARM.RECEIPT_METHOD_ID
	AND RCT.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
	AND RCTT.CUST_TRX_TYPE_SEQ_ID = RCT.CUST_TRX_TYPE_SEQ_ID
	AND RCTLD.CUSTOMER_TRX_LINE_ID = RCTL.CUSTOMER_TRX_LINE_ID
	AND GCC.CODE_COMBINATION_ID = RCTLD.CODE_COMBINATION_ID
	AND APSA.PAYMENT_SCHEDULE_ID = ARAA.APPLIED_PAYMENT_SCHEDULE_ID
	AND APSA.CUSTOMER_TRX_ID = RCT.CUSTOMER_TRX_ID
	AND REC_AMT_TEMP.CUSTOMER_TRX_ID = RCTL.CUSTOMER_TRX_ID
	AND APSA.CONS_INV_ID= ACIA.CONS_INV_ID(+)
	AND RCTLD.ACCOUNT_CLASS IN (
		'REV'
		,'TAX'
		)
	AND ARAA.STATUS IN (
		'APP'
		,'ACTIVITY'
		)
	AND APSA.CLASS IN (
		'INV'
		,'DM'
		,'CM'
		)
	AND (
		ARAA.DISPLAY = 'Y'
		OR ARAA.REVERSAL_GL_DATE IS NOT NULL
		)
	AND HCA.CUST_ACCOUNT_ID = APSA.CUSTOMER_ID
	AND HCA.PARTY_ID = HP.PARTY_ID(+)
	AND HP.PARTY_ID = HPS.PARTY_ID
	AND HPS.LOCATION_ID = HL.LOCATION_ID
	AND NVL(HPS.IDENTIFYING_ADDRESS_FLAG, 'Y') = 'Y'
	AND EXISTS (
		SELECT 'Y'
		FROM DUAL
		WHERE RCTL.LINE_TYPE = 'LINE'
		
		UNION
		
		SELECT 'Y'
		FROM DUAL
		WHERE RCTL.TAX_RATE > 0
			AND RCTL.LINE_TYPE = 'TAX'
		)
	/*AND TO_CHAR(ARAA.CREATION_DATE,'MM/DD/YYYY HH24:MI:SS') BETWEEN NVL(TO_CHAR(:P_APPL_DATE_FROM, 'MM/DD/YYYY HH24:MI:SS'), TO_CHAR(ARAA.CREATION_DATE,'MM/DD/YYYY HH24:MI:SS')) AND NVL(TO_CHAR(:P_APPL_DATE_TO, 'MM/DD/YYYY HH24:MI:SS'), TO_CHAR(ARAA.CREATION_DATE,'MM/DD/YYYY HH24:MI:SS'))
	AND ACR.RECEIPT_NUMBER BETWEEN NVL(:P_RECEIPT_NUM_FROM, ACR.RECEIPT_NUMBER)
		AND NVL(:P_RECEIPT_NUM_TO, ACR.RECEIPT_NUMBER)*/
	AND ARAA.CREATION_DATE BETWEEN NVL(:P_APPL_DATE_FROM, ARAA.CREATION_DATE) AND NVL(:P_APPL_DATE_TO, ARAA.CREATION_DATE)
	AND ACR.RECEIPT_NUMBER BETWEEN NVL(:P_RECEIPT_NUM_FROM, ACR.RECEIPT_NUMBER) AND NVL(:P_RECEIPT_NUM_TO, ACR.RECEIPT_NUMBER)
	--AND rct.ORG_ID = NVL(:P_ORG_ID,RCT.ORG_ID)
	AND ((gcc.segment1 in (:p_entity)) OR COALESCE(:p_entity,null) is null )
	AND ((gcc.segment2 in (:p_selling_method)) OR COALESCE(:P_SELLING_METHOD,null) is null )
	AND (rct.org_id IN (:p_org_id) OR  COALESCE(:p_org_id,null) is null )	
    AND rct.org_id IN (
                        SELECT distinct bu.bu_id
                          FROM fusion.fun_all_business_units_v bu
                              ,fusion.fun_user_role_data_asgnmnts role
                              ,fusion.per_users pu
                              ,fusion.per_roles_dn_vl pr
                         WHERE role.org_id = bu.bu_id
                           AND pu.user_guid = role.user_guid
                           AND pr.role_common_name = role.role_name
                           AND role.active_flag ='Y' 
                           AND pu.username = fnd_global.user_name
                       )

--AND HOU.NAME= NVL(:P_ORG_NAME,HOU.NAME)
--AND HOU.NAME='US BU USD'
ORDER BY 
HCA.ACCOUNT_NUMBER
,ACR.RECEIPT_NUMBER
,APSA.STATUS
,ARAA.GL_DATE