View: AR_TA_VUP_DETAILS_V

View Text

SELECT ART.TRANSMISSION_NAME
, RH.BATCH_NAME
, CR.DEPOSIT_DATE
, BB.BANK_NAME
, BA.BANK_ACCOUNT_NUM
, BB1.BANK_NUM TRANSIT_ROUTING_NUMBER
, RH.LOCKBOX_NUMBER
, RH.BATCH_SEQUENCE
, RH.RECEIPT_SEQUENCE
, RH.APPLICATION_SEQUENCE
, CR.RECEIPT_NUMBER
, CR.CASH_RECEIPT_ID
, CR.AMOUNT
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, SU.LOCATION
, COL.NAME COLLECTOR
, ' ' SHORT_NAME
, PL.NAME
, RA.STATUS
, 0 UNID_AMOUNT
, SUM(NVL(RA.AMOUNT_APPLIED
, 0)) UNAPP_AMOUNT
, 0 ONACC_CM_AMOUNT
FROM AR_CASH_RECEIPTS CR
, AR_RECEIVABLE_APPLICATIONS RA
, AR_TA_REMIT_HIST RH
, AR_TRANSMISSIONS ART
, AP_BANK_BRANCHES BB
, AP_BANK_BRANCHES BB1
, AP_BANK_ACCOUNTS BA
, AP_BANK_ACCOUNTS BA1
, HZ_CUSTOMER_PROFILES CPL_CUS
, HZ_CUSTOMER_PROFILES CPL_SITE
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, HZ_CUST_SITE_USES SU
, AR_COLLECTORS COL
, AR_TA_REMIT_PROF PL
WHERE CR.REQUEST_ID IS NOT NULL
AND NVL(CR.CONFIRMED_FLAG
, 'Y')='Y'
AND CR.STATUS = 'UNAPP'
AND RA.STATUS = 'UNAPP'
AND NVL(RA.CONFIRMED_FLAG
, 'Y')='Y'
AND CR.CASH_RECEIPT_ID=RA.CASH_RECEIPT_ID
AND RH.RECORD_TYPE=5
AND RH.CASH_RECEIPT_ID=CR.CASH_RECEIPT_ID
AND RH.TRANSMISSION_REQUEST_ID=ART.TRANSMISSION_REQUEST_ID
AND BA.BANK_ACCOUNT_ID = CR.REMITTANCE_BANK_ACCOUNT_ID
AND BA.BANK_BRANCH_ID=BB.BANK_BRANCH_ID
AND RH.CUSTOMER_BANK_ACCOUNT_ID=BA1.BANK_ACCOUNT_ID(+)
AND BA1.BANK_BRANCH_ID=BB1.BANK_BRANCH_ID(+)
AND CUST_ACCT.CUST_ACCOUNT_ID = CR.PAY_FROM_CUSTOMER
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND CPL_SITE.SITE_USE_ID(+) = CR.CUSTOMER_SITE_USE_ID
AND CPL_CUS.SITE_USE_ID IS NULL
AND CPL_CUS.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND NVL(CPL_SITE.COLLECTOR_ID
, CPL_CUS.COLLECTOR_ID) = COL.COLLECTOR_ID
AND CR.CUSTOMER_SITE_USE_ID=SU.SITE_USE_ID
AND RH.PROFILE_ID=PL.PROFILE_ID GROUP BY CR.RECEIPT_NUMBER
, CR.CASH_RECEIPT_ID
, RA.STATUS
, ART.TRANSMISSION_NAME
, CR.DEPOSIT_DATE
, BB.BANK_NAME
, BA.BANK_ACCOUNT_NUM
, BB1.BANK_NUM
, RH.LOCKBOX_NUMBER
, RH.BATCH_SEQUENCE
, RH.RECEIPT_SEQUENCE
, RH.APPLICATION_SEQUENCE
, CR.AMOUNT
, PARTY.PARTY_NAME
, SU.LOCATION
, COL.NAME
, PL.NAME
, RH.BATCH_NAME HAVING SUM(NVL(RA.AMOUNT_APPLIED
, 0))<> 0 UNION ALL SELECT ' ' TRANSMISSION_NAME
, ' ' BATCH_NAME
, CR.DEPOSIT_DATE
, BB.BANK_NAME
, BA.BANK_ACCOUNT_NUM
, ' ' TRANSIT_ROUTING_NUMBER
, ' ' LOCKBOX_NUMBER
, 0 BATCH_SEQUENCE
, 0 RECEIPT_SEQUENCE
, 0 APPLICATION_SEQUENCE
, CR.RECEIPT_NUMBER
, CR.CASH_RECEIPT_ID
, CR.AMOUNT
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, SU.LOCATION
, COL.NAME
, ' ' SHORT_NAME
, ' ' NAME
, RA.STATUS
, 0 UNID_AMOUNT
, SUM(NVL(RA.AMOUNT_APPLIED
, 0)) UNAPP_AMOUNT
, 0 ONACC_CM_AMOUNT
FROM HZ_CUSTOMER_PROFILES CPL_CUS
, HZ_CUSTOMER_PROFILES CPL_SITE
, AR_COLLECTORS COL
, HZ_CUST_SITE_USES SU
, AP_BANK_ACCOUNTS BA
, AP_BANK_BRANCHES BB
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_RECEIVABLE_APPLICATIONS RA
, AR_CASH_RECEIPTS CR
WHERE BA.BANK_BRANCH_ID = BB.BANK_BRANCH_ID
AND CPL_SITE.SITE_USE_ID(+) = CR.CUSTOMER_SITE_USE_ID
AND CPL_CUS.SITE_USE_ID IS NULL
AND CPL_CUS.CUST_ACCOUNT_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND NVL(CPL_SITE.COLLECTOR_ID
, CPL_CUS.COLLECTOR_ID) = COL.COLLECTOR_ID
AND CR.REMITTANCE_BANK_ACCOUNT_ID=BA.BANK_ACCOUNT_ID
AND CR.CUSTOMER_SITE_USE_ID=SU.SITE_USE_ID
AND CR.PAY_FROM_CUSTOMER=CUST_ACCT.CUST_ACCOUNT_ID
AND CR.CASH_RECEIPT_ID=RA.CASH_RECEIPT_ID
AND NVL(RA.CONFIRMED_FLAG
, 'Y')='Y'
AND RA.STATUS = 'UNAPP'
AND NVL(CR.CONFIRMED_FLAG
, 'Y')='Y'
AND CR.REQUEST_ID IS NULL
AND CR.STATUS = 'UNAPP' GROUP BY CR.RECEIPT_NUMBER
, CR.CASH_RECEIPT_ID
, RA.STATUS
, CR.DEPOSIT_DATE
, BB.BANK_NAME
, BA.BANK_ACCOUNT_NUM
, CR.AMOUNT
, PARTY.PARTY_NAME
, SU.LOCATION
, COL.NAME HAVING SUM(NVL(RA.AMOUNT_APPLIED
, 0))<>0 UNION ALL SELECT ' ' TRANSMISSION_NAME
, ' ' BATCH_NAME
, CR.DEPOSIT_DATE
, BB.BANK_NAME
, BA.BANK_ACCOUNT_NUM
, ' ' TRANSIT_ROUTING_NUMBER
, ' ' LOCKBOX_NUMBER
, 0 BATCH_SEQUENCE
, 0 RECEIPT_SEQUENCE
, 0 APPLICATION_SEQUENCE
, CR.RECEIPT_NUMBER
, CR.CASH_RECEIPT_ID
, CR.AMOUNT
, ' ' CUSTOMER_NAME
, ' ' LOCATION
, ' ' NAME
, ' ' SHORT_NAME
, ' ' NAME
, RA.STATUS
, SUM(NVL(RA.AMOUNT_APPLIED
, 0)) UNID_AMOUNT
, 0 UNAPP_AMOUNT
, 0 ONACC_CM_AMOUNT
FROM AP_BANK_ACCOUNTS BA
, AP_BANK_BRANCHES BB
, AR_RECEIVABLE_APPLICATIONS RA
, AR_CASH_RECEIPTS CR
WHERE BA.BANK_BRANCH_ID=BB.BANK_BRANCH_ID
AND CR.REMITTANCE_BANK_ACCOUNT_ID=BA.BANK_ACCOUNT_ID
AND CR.CASH_RECEIPT_ID=RA.CASH_RECEIPT_ID
AND RA.STATUS = 'UNID'
AND CR.STATUS = 'UNID' GROUP BY CR.DEPOSIT_DATE
, BB.BANK_NAME
, BA.BANK_ACCOUNT_NUM
, CR.RECEIPT_NUMBER
, CR.CASH_RECEIPT_ID
, CR.AMOUNT
, RA.STATUS HAVING SUM(NVL(RA.AMOUNT_APPLIED
, 0))<>0 UNION ALL SELECT ART.TRANSMISSION_NAME
, RH.BATCH_NAME
, CR.DEPOSIT_DATE
, BB.BANK_NAME
, BA.BANK_ACCOUNT_NUM
, BB1.BANK_NUM TRANSIT_ROUTING_NUMBER
, RH.LOCKBOX_NUMBER
, RH.BATCH_SEQUENCE
, RH.RECEIPT_SEQUENCE
, RH.APPLICATION_SEQUENCE
, CR.RECEIPT_NUMBER
, CR.CASH_RECEIPT_ID
, CR.AMOUNT
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, SU.LOCATION
, DECODE(DED.ASSIGNED_TO_SALESREP_ID
, NULL
, DECODE(DED.ASSIGNED_TO_COLLECTOR_ID
, NULL
, NULL
, COL.NAME)
, ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER(DED.ASSIGNED_TO_SALESREP_ID
, 'NAME')) COLLECTOR
, ' ' SHORT_NAME
, PL.NAME
, CR.STATUS
, 0 UNID_AMOUNT
, 0 UNAPP_AMOUNT
, SUM(DECODE(PS.CLASS
, 'CM'
, ABS(NVL(PS.AMOUNT_DUE_REMAINING
, 0))
, 0)) ONACC_CM_AMOUNT
FROM AR_TA_REMIT_PROF PL
, HZ_CUST_SITE_USES SU
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_COLLECTORS COL
, RA_CUST_TRX_TYPES CTT
, AR_TA_DEDN_ASSGNS DED
, AP_BANK_BRANCHES BB
, AP_BANK_ACCOUNTS BA
, AP_BANK_BRANCHES BB1
, AP_BANK_ACCOUNTS BA1
, AR_TRANSMISSIONS ART
, AR_PAYMENT_SCHEDULES PS
, AR_TA_REMIT_HIST RH1
, AR_TA_REMIT_HIST RH
, AR_CASH_RECEIPTS CR
WHERE CR.STATUS ='APP'
AND NVL(CR.CONFIRMED_FLAG
, 'Y')='Y'
AND RH.RECORD_TYPE=6
AND RH.EVENT_TYPE='OP'
AND RH.CREATED_FROM = 'CASH_ENGINE'
AND RH.CASH_RECEIPT_ID=CR.CASH_RECEIPT_ID
AND RH.TRANSMISSION_REQUEST_ID=ART.TRANSMISSION_REQUEST_ID
AND BA.BANK_ACCOUNT_ID = CR.REMITTANCE_BANK_ACCOUNT_ID
AND BA.BANK_BRANCH_ID=BB.BANK_BRANCH_ID
AND RH.CUSTOMER_BANK_ACCOUNT_ID=BA1.BANK_ACCOUNT_ID(+)
AND BA1.BANK_BRANCH_ID=BB1.BANK_BRANCH_ID(+)
AND RH.CUSTOMER_ID=CUST_ACCT.CUST_ACCOUNT_ID
AND RH.PROFILE_ID=PL.PROFILE_ID
AND RH1.RECORD_TYPE=7
AND RH.HISTORY_LINE_ID = RH1.PARENT_HISTORY_LINE_ID
AND RH1.NEW_TRX_ID = PS.CUSTOMER_TRX_ID
AND PS.CUSTOMER_SITE_USE_ID=SU.SITE_USE_ID
AND DED.DEDUCTN_ASSGNMT_ID IN ( SELECT MAX(DEDUCTN_ASSGNMT_ID)
FROM AR_TA_DEDN_ASSGNS
WHERE CUSTOMER_TRX_ID = RH1.NEW_TRX_ID )
AND CTT.TYPE IN ('DM'
, 'CM')
AND CTT.ATTRIBUTE10 = 'DED'
AND PS.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND PS.CUSTOMER_TRX_ID = DED.CUSTOMER_TRX_ID(+)
AND DED.TRANSFER_TO_COLLECTOR_ID = COL.COLLECTOR_ID(+) GROUP BY ART.TRANSMISSION_NAME
, CR.DEPOSIT_DATE
, BB.BANK_NAME
, BA.BANK_ACCOUNT_NUM
, BB1.BANK_NUM
, RH.LOCKBOX_NUMBER
, RH.BATCH_SEQUENCE
, RH.RECEIPT_SEQUENCE
, RH.APPLICATION_SEQUENCE
, CR.RECEIPT_NUMBER
, CR.CASH_RECEIPT_ID
, CR.AMOUNT
, PARTY.PARTY_NAME
, SU.LOCATION
, DECODE(DED.ASSIGNED_TO_SALESREP_ID
, NULL
, DECODE(DED.ASSIGNED_TO_COLLECTOR_ID
, NULL
, NULL
, COL.NAME)
, ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER(DED.ASSIGNED_TO_SALESREP_ID
, 'NAME'))
, PL.NAME
, CR.STATUS
, RH.BATCH_NAME HAVING SUM(DECODE(PS.CLASS
, 'CM'
, ABS(NVL(PS.AMOUNT_DUE_REMAINING
, 0))
, 0))<>0 UNION ALL SELECT ' ' TRANSMISSION_NAME
, ' ' BATCH_NAME
, CR.DEPOSIT_DATE
, BB.BANK_NAME
, BA.BANK_ACCOUNT_NUM
, ' ' TRANSIT_ROUTING_NUMBER
, ' ' LOCKBOX_NUMBER
, 0 BATCH_SEQUENCE
, 0 RECEIPT_SEQUENCE
, 0 APPLICATION_SEQUENCE
, CR.RECEIPT_NUMBER
, CR.CASH_RECEIPT_ID
, CR.AMOUNT
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50) CUSTOMER_NAME
, SU.LOCATION
, DECODE(DED.ASSIGNED_TO_SALESREP_ID
, NULL
, DECODE(DED.ASSIGNED_TO_COLLECTOR_ID
, NULL
, NULL
, COL.NAME)
, ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER(DED.ASSIGNED_TO_SALESREP_ID
, 'NAME')) COLLECTOR
, ' ' SHORT_NAME
, ' ' NAME
, CR.STATUS
, 0 UNID_AMOUNT
, 0 UNAPP_AMOUNT
, SUM(DECODE(PS.CLASS
, 'CM'
, ABS(NVL(PS.AMOUNT_DUE_REMAINING
, 0))
, 0)) ONACC_CM_AMOUNT
FROM HZ_CUST_SITE_USES SU
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_COLLECTORS COL
, RA_CUST_TRX_TYPES CTT
, AR_TA_DEDN_ASSGNS DED
, AP_BANK_BRANCHES BB
, AP_BANK_ACCOUNTS BA
, AR_PAYMENT_SCHEDULES PS
, AR_TA_REMIT_HIST RH1
, AR_TA_REMIT_HIST RH
, AR_CASH_RECEIPTS CR
WHERE PS.CUSTOMER_SITE_USE_ID=SU.SITE_USE_ID
AND BA.BANK_ACCOUNT_ID = CR.REMITTANCE_BANK_ACCOUNT_ID
AND BA.BANK_BRANCH_ID=BB.BANK_BRANCH_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = PS.CUSTOMER_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND DED.DEDUCTN_ASSGNMT_ID IN ( SELECT MAX(DEDUCTN_ASSGNMT_ID)
FROM AR_TA_DEDN_ASSGNS
WHERE CUSTOMER_TRX_ID = RH1.NEW_TRX_ID )
AND RH1.NEW_TRX_ID = PS.CUSTOMER_TRX_ID
AND RH.HISTORY_LINE_ID = RH1.PARENT_HISTORY_LINE_ID
AND RH1.RECORD_TYPE=7
AND RH.CASH_RECEIPT_ID=CR.CASH_RECEIPT_ID
AND RH.CREATED_FROM = 'MANUAL'
AND RH.EVENT_TYPE='OP'
AND RH.RECORD_TYPE=6
AND NVL(CR.CONFIRMED_FLAG
, 'Y')='Y'
AND CR.STATUS = 'APP'
AND CTT.TYPE IN ('DM'
, 'CM')
AND CTT.ATTRIBUTE10 = 'DED'
AND PS.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND PS.CUSTOMER_TRX_ID = DED.CUSTOMER_TRX_ID(+)
AND DED.TRANSFER_TO_COLLECTOR_ID = COL.COLLECTOR_ID(+) GROUP BY CR.DEPOSIT_DATE
, BB.BANK_NAME
, BA.BANK_ACCOUNT_NUM
, CR.RECEIPT_NUMBER
, CR.CASH_RECEIPT_ID
, CR.AMOUNT
, PARTY.PARTY_NAME
, SU.LOCATION
, DECODE(DED.ASSIGNED_TO_SALESREP_ID
, NULL
, DECODE(DED.ASSIGNED_TO_COLLECTOR_ID
, NULL
, NULL
, COL.NAME)
, ARPT_SQL_FUNC_UTIL.GET_SALESREP_NAME_NUMBER(DED.ASSIGNED_TO_SALESREP_ID
, 'NAME'))
, CR.STATUS HAVING SUM(DECODE(PS.CLASS
, 'CM'
, ABS(NVL(PS.AMOUNT_DUE_REMAINING
, 0))
, 0))<>0

Columns