Golden Tax Reconciliation Report

SELECT 
    HCA.CUSTOMER_CLASS_CODE AS CLASSIFICATION,
    HP.ORIG_SYSTEM_REFERENCE AS HOLDING_CUSTOMER_NUMBER,
    HP.PARTY_NAME AS HOLDING_CUSTOMER_NAME,
	HCA.ACCOUNT_NUMBER AS CUSTOMER_ACCOUNT_NUMBER,
REGEXP_SUBSTR(HCCSAM.ATTRIBUTE1, '[^-]+', 1, 3) AS CUSTOMER_BILL_TO_NUMBER,
    HCCSAM.TRANSLATED_CUSTOMER_NAME AS CUSTOMER_BILL_TO_NAME,
    TYP.NAME AS TRANSACTION_TYPE,
	to_char(APSA.GL_DATE, 'MM-DD-YYYY' ,'NLS_DATE_LANGUAGE = English') AS TRANSACTION_RECEIPT_DATE,
    RCTA.INTERFACE_HEADER_ATTRIBUTE1 AS SALES_ORDER_NUMBER,
    RCTA.TRX_NUMBER AS INVOICE_NUM,
    NVL(APSA.AMOUNT_LINE_ITEMS_ORIGINAL, 0)+NVL(APSA.TAX_ORIGINAL,0) AS ORIGINAL_INVOICE_AMOUNT,
    NULL AS REMAINING_BALANCE_UNAPP,
    NVL(APSA.AMOUNT_DUE_REMAINING, 0) AS REMAINING_BALANCE,
    --JCTHA.GTA_TRX_NUMBER AS GOLDEN_TAX_INVOICE_NUMBER,
	
	CASE WHEN JCTHA.STATUS = 'COMPLETED' THEN (SELECT INVOICE_NUMBER FROM JA_CN_VAT_HEADERS_ALL WHERE JCTHA.GTA_TRX_HEADER_ID = GTA_TRX_HEADER_ID)
	ELSE ( SELECT NVL(INVOICE_NUMBER,NULL) FROM JA_CN_TRX_HEADERS_ALL A, JA_CN_VAT_HEADERS_ALL B WHERE JCTHA.CONSOLI_PARENT_TRX_HDR_ID = A.GTA_TRX_HEADER_ID
	AND A.GTA_TRX_HEADER_ID = B.GTA_TRX_HEADER_ID)
	END AS GOLDEN_TAX_INVOICE_NUMBER,
	
	CASE WHEN JCTHA.STATUS = 'COMPLETED' THEN (SELECT INVOICE_DATE FROM JA_CN_VAT_HEADERS_ALL WHERE JCTHA.GTA_TRX_HEADER_ID = GTA_TRX_HEADER_ID)
	ELSE ( SELECT NVL(INVOICE_DATE,NULL) FROM JA_CN_TRX_HEADERS_ALL A, JA_CN_VAT_HEADERS_ALL B WHERE JCTHA.CONSOLI_PARENT_TRX_HDR_ID = A.GTA_TRX_HEADER_ID
	AND A.GTA_TRX_HEADER_ID = B.GTA_TRX_HEADER_ID)
	END AS GOLDEN_TAX_INVOICE_DATE,
	
    --JCTHA.TRANSACTION_DATE AS GOLDEN_TAX_INVOICE_DATE,
    'Trx' AS TYP,
    NULL AS APPLICATION_REF_NUM,
	NULL AS CLAIM_NUMBER
FROM
    RA_CUSTOMER_TRX_ALL RCTA
    JOIN FUN_ALL_BUSINESS_UNITS_V BU ON RCTA.ORG_ID = BU.BU_ID
    JOIN HZ_CUST_ACCOUNTS HCA ON RCTA.BILL_TO_CUSTOMER_ID = HCA.CUST_ACCOUNT_ID
    JOIN HZ_PARTIES HP ON HCA.PARTY_ID = HP.PARTY_ID
    JOIN AR_PAYMENT_SCHEDULES_ALL APSA ON RCTA.CUSTOMER_TRX_ID = APSA.CUSTOMER_TRX_ID
    JOIN HZ_CUST_SITE_USES_ALL HCSUA ON APSA.CUSTOMER_SITE_USE_ID = HCSUA.SITE_USE_ID
    JOIN HZ_CUST_ACCT_SITES_ALL HCCSAM ON HCSUA.CUST_ACCT_SITE_ID = HCCSAM.CUST_ACCT_SITE_ID
    JOIN HZ_PARTY_SITES HPS ON HCCSAM.PARTY_SITE_ID = HPS.PARTY_SITE_ID
    JOIN HZ_LOCATIONS HL ON HPS.LOCATION_ID = HL.LOCATION_ID
    JOIN RA_CUST_TRX_TYPES_ALL TYP ON RCTA.CUST_TRX_TYPE_SEQ_ID = TYP.CUST_TRX_TYPE_SEQ_ID
    LEFT JOIN JA_CN_TRX_HEADERS_ALL JCTHA ON RCTA.ORG_ID = JCTHA.ORG_ID AND RCTA.TRX_NUMBER = JCTHA.RA_TRX_NUMBER
WHERE
    BU.BU_NAME IN (:P_BU_NAME)
    AND (HCA.CUSTOMER_CLASS_CODE IN (:P_CLASSIFICATION) OR 'ALL' IN (:P_CLASSIFICATION || 'ALL'))
    AND (HP.PARTY_NAME IN (:P_CUST_NAME) OR 'ALL' IN (:P_CUST_NAME || 'ALL'))
    AND (HP.ORIG_SYSTEM_REFERENCE IN (:P_CUST_NUM) OR 'ALL' IN (:P_CUST_NUM || 'ALL'))
    AND (HCCSAM.TRANSLATED_CUSTOMER_NAME IN (:P_CUST_BILL_TO_NAME) OR 'ALL' IN (:P_CUST_BILL_TO_NAME || 'ALL'))
    AND (HCA.ACCOUNT_NUMBER IN (:P_CUST_BILL_TO_NUM) OR 'ALL' IN (:P_CUST_BILL_TO_NUM || 'ALL'))
    AND APSA.GL_DATE BETWEEN :P_FROM_GL_DATE AND :P_TO_GL_DATE
    AND (RCTA.TRX_NUMBER = :P_TRX_NUM OR 'ALL' IN (:P_TRX_NUM || 'ALL'))
    AND RCTA.COMPLETE_FLAG = 'Y'
    AND RCTA.ORG_ID IN (
        SELECT DISTINCT bu.bu_id
        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
        WHERE role.active_flag = 'Y'
        AND pu.username = fnd_global.user_name
        AND role.ROLE_NAME IN ('XXC_BILLING_ANALYST_JOB', 'XXC_CHINA_GOLDEN_TAX_JOB', 'XXC_BILLING_MANAGER_JOB') 
    )
UNION ALL
SELECT 
    HCA.CUSTOMER_CLASS_CODE AS CLASSIFICATION,
    HP.ORIG_SYSTEM_REFERENCE AS HOLDING_CUSTOMER_NUMBER,
    HP.PARTY_NAME AS HOLDING_CUSTOMER_NAME,
	HCA.ACCOUNT_NUMBER AS CUSTOMER_ACCOUNT_NUMBER,
    REGEXP_SUBSTR(HCCSAM.ATTRIBUTE1, '[^-]+', 1, 3) AS CUSTOMER_BILL_TO_NUMBER,
    HCCSAM.TRANSLATED_CUSTOMER_NAME AS CUSTOMER_BILL_TO_NAME,
    DECODE(ARCA.TYPE, 'CASH', 'Standard', 'MISC', 'Miscellaneous', NULL) AS TRANSACTION_TYPE,
	to_char(APSA.GL_DATE, 'MM-DD-YYYY' ,'NLS_DATE_LANGUAGE = English') AS TRANSACTION_RECEIPT_DATE,
    NULL AS SALES_ORDER_NUMBER,
    ARCA.RECEIPT_NUMBER AS INVOICE_NUM,
    NVL(ARCA.AMOUNT, 0) AS ORIGINAL_INVOICE_AMOUNT,
    NVL((SELECT SUM(ARAA.amount_applied) * -1
         FROM ar_receivable_applications_all ARAA
         WHERE NVL(ARAA.confirmed_flag, 'Y') = 'Y'
         AND ARAA.status = 'UNAPP'
         AND ARAA.payment_schedule_id = APSA.payment_schedule_id
         GROUP BY ARAA.payment_schedule_id), 0) AS REMAINING_BALANCE_UNAPP,
    ARA.AMOUNT_APPLIED * -1 AS REMAINING_BALANCE,
    NULL AS GOLDEN_TAX_INVOICE_NUMBER,
    NULL AS GOLDEN_TAX_INVOICE_DATE,
    'Receipt' AS TYP,
    SUBSTR(NVL(ARA.APPLICATION_REF_NUM, DECODE(ARA.STATUS, 'ACC', 'On Account', NULL)), 1, 3) AS APPLICATION_REF_NUM,
	(Case when SUBSTR(NVL(ARA.APPLICATION_REF_NUM, DECODE(ARA.STATUS, 'ACC', 'On Account', NULL)), 1, 3) in ('DED','OPM') THEN ARA.APPLICATION_REF_NUM
	ELSE NULL END) AS CLAIM_NUMBER
FROM
    AR_CASH_RECEIPTS_ALL ARCA
    JOIN FUN_ALL_BUSINESS_UNITS_V BU ON ARCA.ORG_ID = BU.BU_ID
    JOIN HZ_CUST_ACCOUNTS HCA ON ARCA.PAY_FROM_CUSTOMER = HCA.CUST_ACCOUNT_ID
    JOIN HZ_PARTIES HP ON HCA.PARTY_ID = HP.PARTY_ID
    JOIN AR_PAYMENT_SCHEDULES_ALL APSA ON ARCA.CASH_RECEIPT_ID = APSA.CASH_RECEIPT_ID
    JOIN HZ_CUST_SITE_USES_ALL HCSUA ON APSA.CUSTOMER_SITE_USE_ID = HCSUA.SITE_USE_ID
    JOIN HZ_CUST_ACCT_SITES_ALL HCCSAM ON HCSUA.CUST_ACCT_SITE_ID = HCCSAM.CUST_ACCT_SITE_ID
    JOIN HZ_PARTY_SITES HPS ON HCCSAM.PARTY_SITE_ID = HPS.PARTY_SITE_ID
    JOIN HZ_LOCATIONS HL ON HPS.LOCATION_ID = HL.LOCATION_ID
    JOIN AR_RECEIVABLE_APPLICATIONS_ALL ARA ON ARA.CASH_RECEIPT_ID = ARCA.CASH_RECEIPT_ID
WHERE
    BU.BU_NAME IN (:P_BU_NAME)
    AND (HCA.CUSTOMER_CLASS_CODE IN (:P_CLASSIFICATION) OR 'ALL' IN (:P_CLASSIFICATION || 'ALL'))
    AND (HP.PARTY_NAME IN (:P_CUST_NAME) OR 'ALL' IN (:P_CUST_NAME || 'ALL'))
    AND (HP.ORIG_SYSTEM_REFERENCE IN (:P_CUST_NUM) OR 'ALL' IN (:P_CUST_NUM || 'ALL'))
    AND (HCCSAM.TRANSLATED_CUSTOMER_NAME IN (:P_CUST_BILL_TO_NAME) OR 'ALL' IN (:P_CUST_BILL_TO_NAME || 'ALL'))
    AND (HCA.ACCOUNT_NUMBER IN (:P_CUST_BILL_TO_NUM) OR 'ALL' IN (:P_CUST_BILL_TO_NUM || 'ALL'))
    AND APSA.GL_DATE BETWEEN :P_FROM_GL_DATE AND :P_TO_GL_DATE
    AND (ARCA.RECEIPT_NUMBER = :P_TRX_NUM OR 'ALL' IN (:P_TRX_NUM || 'ALL'))
    AND (ARA.APPLICATION_REF_TYPE = 'CLAIM_INVESTIGATION' OR ARA.STATUS = 'ACC')
    AND ARA.DISPLAY = 'Y'
    AND ARCA.ORG_ID IN (
        SELECT DISTINCT bu.bu_id
        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
        WHERE role.active_flag = 'Y'
        AND pu.username = fnd_global.user_name
        AND role.ROLE_NAME IN ('XXC_BILLING_ANALYST_JOB', 'XXC_CHINA_GOLDEN_TAX_JOB', 'XXC_BILLING_MANAGER_JOB') 
    )
ORDER BY
    HOLDING_CUSTOMER_NUMBER,
    CUSTOMER_BILL_TO_NUMBER,
    SALES_ORDER_NUMBER,
    INVOICE_NUM