View: AR_BR_MAIN_REMIT_BATCH_V

Product:	AR - Receivables
Description:
Implementation/DBA Data:	APPS.AR_BR_MAIN_REMIT_BATCH_V

View Text

SELECT C.ROWID
, /* REMITTANCE */ BA.BATCH_ID
, /* TRANSACTION TYPE */ CTT.NAME
, PS.CUST_TRX_TYPE_ID
, CTT.SIGNED_FLAG
, CTT.DRAWEE_ISSUED_FLAG
, /* CUSTOMER TRX */ PS.PAYMENT_SCHEDULE_ID
, C.CUSTOMER_TRX_ID
, C.PRINTING_LAST_PRINTED
, C.COMMENTS
, PS.INVOICE_CURRENCY_CODE
, PS.TRX_DATE
, PS.AMOUNT_DUE_REMAINING
, PS.ACCTD_AMOUNT_DUE_REMAINING
, PS.DUE_DATE
, PS.TRX_NUMBER
, C.SPECIAL_INSTRUCTIONS
, PS.ATTRIBUTE_CATEGORY
, PS.ATTRIBUTE1
, PS.ATTRIBUTE2
, PS.ATTRIBUTE3
, PS.ATTRIBUTE4
, PS.ATTRIBUTE5
, PS.ATTRIBUTE6
, PS.ATTRIBUTE7
, PS.ATTRIBUTE8
, PS.ATTRIBUTE9
, PS.ATTRIBUTE10
, PS.ATTRIBUTE11
, PS.ATTRIBUTE12
, PS.ATTRIBUTE13
, PS.ATTRIBUTE14
, PS.ATTRIBUTE15
, /* DRAWEE */ SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CUST_ACCT.ACCOUNT_NUMBER
, PARTY.JGZZ_FISCAL_CODE
, CUST_ACCT.CUSTOMER_CLASS_CODE
, PARTY.CATEGORY_CODE
, PC.NAME
, /* DRAWEE SITE */ LOC.ADDRESS1 || LOC.ADDRESS2 || LOC.ADDRESS3
, LOC.CITY
, LOC.POSTAL_CODE
, LOC.STATE
, LOC.COUNTRY
, /* DRAWEE BANK */ IBY.BANK_NAME DRAWEE_BANK_NAME
, IBY.BANK_NUMBER DRAWEE_BANK_NUMBER
, /* DRAWEE BRANCH */ IBY.BANK_BRANCH_NAME DRAWEE_BRANCH_NAME
, NULL DRAWEE_BRANCH_CITY
, NULL DRAWEE_BRANCH_STATE
, NULL DRAWEE_BRANCH_COUNTRY
, NULL DRAWEE_BRANCH_POSTAL_CODE
, NULL DRAWEE_BRANCH_NUMBER
, /* REMITTANCE BANK */ REMITBANKPARTY.PARTY_NAME
, /* STANDARD WHO COLUMNS */ C.CREATED_BY
, C.CREATION_DATE
, C.LAST_UPDATED_BY
, C.LAST_UPDATE_DATE
, C.LAST_UPDATE_LOGIN
FROM AR_PAYMENT_SCHEDULES PS
, AR_TRANSACTION_HISTORY RAH
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, RA_CUST_TRX_TYPES CTT
, RA_CUSTOMER_TRX C
, HZ_CUSTOMER_PROFILES CP
, HZ_CUST_PROFILE_CLASSES PC
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_SITE_USES SU
, IBY_TRXN_EXTENSIONS_V IBY
, AR_BATCHES BA
, AR_LOOKUPS ARS
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES_OU_V RABA
, HZ_PARTIES REMITBRANCHPARTY
, HZ_PARTIES REMITBANKPARTY
, HZ_RELATIONSHIPS REMITBRREL
, HZ_ORGANIZATION_PROFILES REMITBANKORGPROFILE
, HZ_ORGANIZATION_PROFILES REMITBRANCHORGPROFILE
WHERE BA.BATCH_ID = PS.RESERVED_VALUE
AND PS.RESERVED_TYPE = 'REMITTANCE'
AND RAH.CURRENT_RECORD_FLAG = 'Y'
AND RAH.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
AND C.DRAWEE_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND RAH.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
AND PS.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
AND CP.PROFILE_CLASS_ID = PC.PROFILE_CLASS_ID
AND CP.SITE_USE_ID IS NULL
AND C.DRAWEE_SITE_USE_ID = SU.SITE_USE_ID
AND SU.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND CUST_ACCT.CUST_ACCOUNT_ID=ACCT_SITE.CUST_ACCOUNT_ID
AND IBY.TRXN_EXTENSION_ID(+) = C.PAYMENT_TRXN_EXTENSION_ID
AND C.REMIT_BANK_ACCT_USE_ID = RABA.BANK_ACCT_USE_ID(+)
AND RAH.STATUS = ARS.LOOKUP_CODE
AND ARS.LOOKUP_TYPE = 'TRANSACTION_HISTORY_STATUS'
AND CTT.SIGNED_FLAG IN ('Y'
, 'N')
AND CTT.DRAWEE_ISSUED_FLAG IN ('Y'
, 'N')
AND REMITBRANCHPARTY.PARTY_ID (+) = CBA.BANK_BRANCH_ID
AND REMITBRREL.OBJECT_ID = REMITBANKPARTY.PARTY_ID (+)
AND REMITBRREL.SUBJECT_ID (+) = REMITBRANCHPARTY.PARTY_ID
AND REMITBRREL.RELATIONSHIP_TYPE (+) = 'BANK_AND_BRANCH'
AND REMITBRREL.RELATIONSHIP_CODE (+) = 'BRANCH_OF'
AND REMITBRREL.SUBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND REMITBRREL.SUBJECT_TYPE (+) = 'ORGANIZATION'
AND REMITBRREL.OBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND REMITBRREL.OBJECT_TYPE (+) = 'ORGANIZATION'
AND REMITBANKORGPROFILE.PARTY_ID (+) = REMITBANKPARTY.PARTY_ID
AND REMITBRANCHORGPROFILE.PARTY_ID (+) = REMITBRANCHPARTY.PARTY_ID
AND SYSDATE BETWEEN NVL(TRUNC(REMITBANKORGPROFILE.EFFECTIVE_START_DATE)
, SYSDATE-1)
AND NVL(TRUNC(REMITBANKORGPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND SYSDATE BETWEEN NVL(TRUNC(REMITBRANCHORGPROFILE.EFFECTIVE_START_DATE)
, SYSDATE-1)
AND NVL(TRUNC(REMITBRANCHORGPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND RABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID (+) UNION SELECT C.ROWID
, /* REMITTANCE */ BA.BATCH_ID
, /* TRANSACTION TYPE */ CTT.NAME
, PS.CUST_TRX_TYPE_ID
, CTT.SIGNED_FLAG
, CTT.DRAWEE_ISSUED_FLAG
, /* CUSTOMER TRX */ PS.PAYMENT_SCHEDULE_ID
, C.CUSTOMER_TRX_ID
, C.PRINTING_LAST_PRINTED
, C.COMMENTS
, PS.INVOICE_CURRENCY_CODE
, PS.TRX_DATE
, ARP_BR_REMIT_FUNCTION.GET_AMOUNT(RAH.TRANSACTION_HISTORY_ID
, RAH.STATUS
, C.CUSTOMER_TRX_ID)
, ARP_BR_REMIT_FUNCTION.GET_ACCTD_AMOUNT(RAH.TRANSACTION_HISTORY_ID
, RAH.STATUS
, C.CUSTOMER_TRX_ID)
, PS.DUE_DATE
, PS.TRX_NUMBER
, C.SPECIAL_INSTRUCTIONS
, PS.ATTRIBUTE_CATEGORY
, PS.ATTRIBUTE1
, PS.ATTRIBUTE2
, PS.ATTRIBUTE3
, PS.ATTRIBUTE4
, PS.ATTRIBUTE5
, PS.ATTRIBUTE6
, PS.ATTRIBUTE7
, PS.ATTRIBUTE8
, PS.ATTRIBUTE9
, PS.ATTRIBUTE10
, PS.ATTRIBUTE11
, PS.ATTRIBUTE12
, PS.ATTRIBUTE13
, PS.ATTRIBUTE14
, PS.ATTRIBUTE15
, /* DRAWEE */ SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CUST_ACCT.ACCOUNT_NUMBER
, PARTY.JGZZ_FISCAL_CODE
, CUST_ACCT.CUSTOMER_CLASS_CODE
, PARTY.CATEGORY_CODE
, PC.NAME
, /* DRAWEE SITE */ LOC.ADDRESS1 || LOC.ADDRESS2 || LOC.ADDRESS3
, LOC.CITY
, LOC.POSTAL_CODE
, LOC.STATE
, LOC.COUNTRY
, /* DRAWEE BANK */ IBY.BANK_NAME DRAWEE_BANK_NAME
, IBY.BANK_NUMBER DRAWEE_BANK_NUMBER
, /* DRAWEE BRANCH */ BANK_BRANCH_NAME DRAWEE_BRANCH_NAME
, NULL DRAWEE_BRANCH_CITY
, NULL DRAWEE_BRANCH_STATE
, NULL DRAWEE_BRANCH_COUNTRY
, NULL DRAWEE_BRANCH_POSTAL_CODE
, NULL DRAWEE_BRANCH_NUMBER
, /* REMITTANCE BANK */ REMITBANKPARTY.PARTY_NAME
, /* STANDARD WHO COLUMNS */ C.CREATED_BY
, C.CREATION_DATE
, C.LAST_UPDATED_BY
, C.LAST_UPDATE_DATE
, C.LAST_UPDATE_LOGIN
FROM AR_PAYMENT_SCHEDULES PS
, AR_TRANSACTION_HISTORY RAH
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, RA_CUST_TRX_TYPES CTT
, RA_CUSTOMER_TRX C
, HZ_CUSTOMER_PROFILES CP
, HZ_CUST_PROFILE_CLASSES PC
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_SITE_USES SU
, IBY_TRXN_EXTENSIONS_V IBY
, AR_BATCHES BA
, AR_LOOKUPS ARS
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES_OU_V RABA
, HZ_PARTIES REMITBRANCHPARTY
, HZ_PARTIES REMITBANKPARTY
, HZ_RELATIONSHIPS REMITBRREL
, HZ_ORGANIZATION_PROFILES REMITBANKORGPROFILE
, HZ_ORGANIZATION_PROFILES REMITBRANCHORGPROFILE
WHERE BA.BATCH_ID = RAH.BATCH_ID
AND ((BA.REMIT_METHOD_CODE = 'STANDARD'
AND RAH.EVENT = 'REMITTED') OR (BA.REMIT_METHOD_CODE = 'FACTORING'
AND BA.WITH_RECOURSE_FLAG = 'Y'
AND RAH.EVENT = 'FACTORED') OR (BA.REMIT_METHOD_CODE = 'FACTORING'
AND BA.WITH_RECOURSE_FLAG = 'N'
AND RAH.EVENT = 'CLOSED'))
AND RAH.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
AND C.DRAWEE_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND RAH.CUSTOMER_TRX_ID = PS.CUSTOMER_TRX_ID
AND PS.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND CUST_ACCT.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
AND CP.PROFILE_CLASS_ID = PC.PROFILE_CLASS_ID
AND CP.SITE_USE_ID IS NULL
AND C.DRAWEE_SITE_USE_ID = SU.SITE_USE_ID
AND SU.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID
AND LOC.LOCATION_ID = PARTY_SITE.LOCATION_ID
AND CUST_ACCT.CUST_ACCOUNT_ID=ACCT_SITE.CUST_ACCOUNT_ID
AND IBY.TRXN_EXTENSION_ID(+) = C.PAYMENT_TRXN_EXTENSION_ID
AND C.REMIT_BANK_ACCT_USE_ID = RABA.BANK_ACCT_USE_ID(+)
AND RAH.STATUS = ARS.LOOKUP_CODE
AND ARS.LOOKUP_TYPE = 'TRANSACTION_HISTORY_STATUS'
AND CTT.SIGNED_FLAG IN ('Y'
, 'N')
AND CTT.DRAWEE_ISSUED_FLAG IN ('Y'
, 'N')
AND REMITBRANCHPARTY.PARTY_ID (+) = CBA.BANK_BRANCH_ID
AND REMITBRREL.OBJECT_ID = REMITBANKPARTY.PARTY_ID (+)
AND REMITBRREL.SUBJECT_ID (+) = REMITBRANCHPARTY.PARTY_ID
AND REMITBRREL.RELATIONSHIP_TYPE (+) = 'BANK_AND_BRANCH'
AND REMITBRREL.RELATIONSHIP_CODE (+) = 'BRANCH_OF'
AND REMITBRREL.SUBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND REMITBRREL.SUBJECT_TYPE (+) = 'ORGANIZATION'
AND REMITBRREL.OBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND REMITBRREL.OBJECT_TYPE (+) = 'ORGANIZATION'
AND REMITBANKORGPROFILE.PARTY_ID (+) = REMITBANKPARTY.PARTY_ID
AND REMITBRANCHORGPROFILE.PARTY_ID (+) = REMITBRANCHPARTY.PARTY_ID
AND SYSDATE BETWEEN NVL(TRUNC(REMITBANKORGPROFILE.EFFECTIVE_START_DATE)
, SYSDATE-1)
AND NVL(TRUNC(REMITBANKORGPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND SYSDATE BETWEEN NVL(TRUNC(REMITBRANCHORGPROFILE.EFFECTIVE_START_DATE)
, SYSDATE-1)
AND NVL(TRUNC(REMITBRANCHORGPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND RABA.BANK_ACCOUNT_ID = CBA.BANK_ACCOUNT_ID (+)

Columns