View: AR_CUSTOMER_BILLS_TRX_V

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

View Text

SELECT C.ROWID
, BS.NAME
, BS.BATCH_SOURCE_ID
, BS.AUTO_TRX_NUMBERING_FLAG
, /* TRANSACTION TYPE */ CTT.NAME
, CTT.CUST_TRX_TYPE_ID
, CTT.SIGNED_FLAG
, CTT.DRAWEE_ISSUED_FLAG
, /* REMITTANCE BANK ACCOUNT */ CTT.MAGNETIC_FORMAT_CODE
, CTT.POST_TO_GL
, CTT.DEFAULT_PRINTING_OPTION
, CTT.FORMAT_PROGRAM_ID
, /* CUSTOMER TRX */ C.CUSTOMER_TRX_ID
, C.PRINTING_LAST_PRINTED
, C.CUSTOMER_REFERENCE
, C.DOC_SEQUENCE_VALUE
, C.DOC_SEQUENCE_ID
, C.COMMENTS
, C.INVOICE_CURRENCY_CODE
, C.TRX_DATE
, C.BR_AMOUNT
, C.BR_UNPAID_FLAG
, C.BR_ON_HOLD_FLAG
, C.TERM_DUE_DATE
, C.TRX_NUMBER
, C.OLD_TRX_NUMBER
, C.SPECIAL_INSTRUCTIONS
, C.PRINTING_OPTION
, C.COMPLETE_FLAG
, C.OVERRIDE_REMIT_ACCOUNT_FLAG
, C.EXCHANGE_RATE
, C.EXCHANGE_RATE_TYPE
, C.EXCHANGE_DATE
, C.ATTRIBUTE_CATEGORY
, C.ATTRIBUTE1
, C.ATTRIBUTE2
, C.ATTRIBUTE3
, C.ATTRIBUTE4
, C.ATTRIBUTE5
, C.ATTRIBUTE6
, C.ATTRIBUTE7
, C.ATTRIBUTE8
, C.ATTRIBUTE9
, C.ATTRIBUTE10
, C.ATTRIBUTE11
, C.ATTRIBUTE12
, C.ATTRIBUTE13
, C.ATTRIBUTE14
, C.ATTRIBUTE15
, C.CREATED_FROM
, /* TRANSACTION BATCH */ C.BATCH_ID
, /* CONTACT */ SUBSTRB(PARTY_CONT.PERSON_LAST_NAME
, 1
, 50) || '
, ' || SUBSTRB(PARTY_CONT.PERSON_FIRST_NAME
, 1
, 40)
, ACCT_ROLE.CUST_ACCOUNT_ROLE_ID
, /* DRAWEE */ C.DRAWEE_ID
, SUBSTRB(PARTY.PARTY_NAME
, 1
, 50)
, CUST_ACCT.ACCOUNT_NUMBER
, PARTY.JGZZ_FISCAL_CODE
, PARTY.TAX_REFERENCE
, CUST_ACCT.CUSTOMER_CLASS_CODE
, PARTY.CATEGORY_CODE
, CCAT.MEANING
, /* SITE */ SU.LOCATION
, C.DRAWEE_SITE_USE_ID
, ACCT_SITE.CUST_ACCT_SITE_ID
, LOC.ADDRESS1
, LOC.ADDRESS2
, /* LOC.ADDRESS3 || LOC.CITY || LOC.PROVINCE || LOC.POSTAL_CODE || LOC.STATE || FT.TERRITORY_CODE
, */ DECODE(ACCT_SITE.CUST_ACCT_SITE_ID
, NULL
, NULL
, ARH_ADDR_PKG.FORMAT_LAST_ADDRESS_LINE(LOC.ADDRESS_STYLE
, LOC.ADDRESS3
, LOC.ADDRESS4
, LOC.CITY
, LOC.COUNTY
, LOC.STATE
, LOC.PROVINCE
, FT.TERRITORY_SHORT_NAME
, LOC.POSTAL_CODE ) )
, LOC.ADDRESS3
, LOC.CITY
, LOC.POSTAL_CODE
, LOC.STATE
, NVL(LOC.STATE
, LOC.PROVINCE )
, FT.TERRITORY_CODE
, SU.PRIMARY_FLAG
, /* DRAWEE BANK NAME */ BANKPARTY.PARTY_NAME
, BRANCHORGPROFILE.BANK_OR_BRANCH_NUMBER
, BANKORGPROFILE.BANK_OR_BRANCH_NUMBER
, BRANCHPARTY.PARTY_NAME
, ABA.BANK_ACCOUNT_NUM
, ABA.BANK_ACCOUNT_NAME
, ABA.CURRENCY_CODE
, BRANCHPARTY.ADDRESS1
, BRANCHPARTY.ADDRESS2
, BRANCHPARTY.ADDRESS3 || BRANCHPARTY.CITY || BRANCHPARTY.PROVINCE || BRANCHPARTY.POSTAL_CODE || BRANCHPARTY.STATE
, BRANCHPARTY.ADDRESS3
, BRANCHPARTY.CITY
, BRANCHPARTY.POSTAL_CODE
, BRANCHPARTY.PROVINCE
, BRANCHPARTY.STATE
, NVL(BRANCHPARTY.PROVINCE
, BRANCHPARTY.STATE)
, BRANCHPARTY.COUNTRY
, NULL
, /* REMITTANCE BANK ACCOUNT */ REMITBANKPARTY.PARTY_NAME
, REMITBRANCHORGPROFILE.BANK_OR_BRANCH_NUMBER
, REMITBANKORGPROFILE.BANK_OR_BRANCH_NUMBER
, REMITBRANCHPARTY.PARTY_NAME
, CBA.BANK_ACCOUNT_NUM
, CBA.BANK_ACCOUNT_NAME
, CBA.CURRENCY_CODE
, REMITBRANCHPARTY.ADDRESS1
, REMITBRANCHPARTY.ADDRESS2
, REMITBRANCHPARTY.ADDRESS3 || REMITBRANCHPARTY.CITY || REMITBRANCHPARTY.PROVINCE || REMITBRANCHPARTY.POSTAL_CODE || REMITBRANCHPARTY.STATE
, REMITBRANCHPARTY.ADDRESS3
, REMITBRANCHPARTY.CITY
, REMITBRANCHPARTY.POSTAL_CODE
, REMITBRANCHPARTY.PROVINCE
, REMITBRANCHPARTY.STATE
, NVL(REMITBRANCHPARTY.PROVINCE
, REMITBRANCHPARTY.STATE)
, REMITBRANCHPARTY.COUNTRY
, RABA.BANK_ACCOUNT_ID
, RAH.STATUS
, ARS.MEANING
, ARPT_SQL_FUNC_UTIL.GET_ORIG_GL_DATE(C.CUSTOMER_TRX_ID)
, RAH.POSTING_CONTROL_ID
, C.REMITTANCE_BATCH_ID
, C.CREATED_BY
, C.CREATION_DATE
, C.LAST_UPDATED_BY
, C.LAST_UPDATE_DATE
, C.LAST_UPDATE_LOGIN
, C.ORG_ID
, C.LEGAL_ENTITY_ID
, C.PAYMENT_TRXN_EXTENSION_ID
FROM RA_BATCH_SOURCES_ALL BS
, RA_CUST_TRX_TYPES_ALL CTT
, RA_CUSTOMER_TRX C
, HZ_CUST_ACCT_SITES ACCT_SITE
, HZ_PARTY_SITES PARTY_SITE
, HZ_LOCATIONS LOC
, HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
, HZ_PARTIES PARTY_CONT
, HZ_RELATIONSHIPS REL
, HZ_CUST_SITE_USES SU
, FND_TERRITORIES_VL FT
, IBY_EXT_BANK_ACCOUNTS ABA
, HZ_CUST_ACCOUNTS CUST_ACCT
, HZ_PARTIES PARTY
, AR_LOOKUPS ARS
, AR_LOOKUPS CCAT
, AR_TRANSACTION_HISTORY RAH
, CE_BANK_ACCOUNTS CBA
, CE_BANK_ACCT_USES_OU_V RABA
, HZ_PARTIES BRANCHPARTY
, HZ_PARTIES BANKPARTY
, HZ_RELATIONSHIPS BRREL
, HZ_ORGANIZATION_PROFILES BANKORGPROFILE
, HZ_ORGANIZATION_PROFILES BRANCHORGPROFILE
, HZ_PARTIES REMITBRANCHPARTY
, HZ_PARTIES REMITBANKPARTY
, HZ_RELATIONSHIPS REMITBRREL
, HZ_ORGANIZATION_PROFILES REMITBANKORGPROFILE
, HZ_ORGANIZATION_PROFILES REMITBRANCHORGPROFILE
, IBY_TRXN_EXTENSIONS_V IBY
WHERE C.BATCH_SOURCE_ID = BS.BATCH_SOURCE_ID
AND C.ORG_ID = BS.ORG_ID
AND C.CUST_TRX_TYPE_ID = CTT.CUST_TRX_TYPE_ID
AND C.ORG_ID = CTT.ORG_ID
AND C.DRAWEE_SITE_USE_ID = SU.SITE_USE_ID (+)
AND C.ORG_ID = SU.ORG_ID (+)
AND SU.CUST_ACCT_SITE_ID = ACCT_SITE.CUST_ACCT_SITE_ID (+)
AND SU.ORG_ID = ACCT_SITE.ORG_ID (+)
AND SU.ORG_ID = ACCT_SITE.ORG_ID (+)
AND ACCT_SITE.PARTY_SITE_ID = PARTY_SITE.PARTY_SITE_ID(+)
AND LOC.LOCATION_ID(+) = PARTY_SITE.LOCATION_ID
AND C.PAYMENT_TRXN_EXTENSION_ID = IBY.TRXN_EXTENSION_ID (+)
AND C.DRAWEE_ID = CUST_ACCT.CUST_ACCOUNT_ID
AND CUST_ACCT.PARTY_ID = PARTY.PARTY_ID
AND C.REMIT_BANK_ACCT_USE_ID = RABA.BANK_ACCT_USE_ID (+)
AND C.ORG_ID = RABA.ORG_ID (+)
AND C.DRAWEE_CONTACT_ID = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID (+)
AND ACCT_ROLE.PARTY_ID = REL.PARTY_ID(+)
AND REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG(+) = 'F'
AND ACCT_ROLE.ROLE_TYPE(+) = 'CONTACT'
AND REL.SUBJECT_ID = PARTY_CONT.PARTY_ID(+)
AND LOC.COUNTRY = FT.TERRITORY_CODE (+)
AND RAH.CUSTOMER_TRX_ID = C.CUSTOMER_TRX_ID
AND RAH.ORG_ID = C.ORG_ID
AND RAH.CURRENT_RECORD_FLAG = 'Y'
AND ARS.LOOKUP_CODE = RAH.STATUS
AND ARS.LOOKUP_TYPE = 'TRANSACTION_HISTORY_STATUS'
AND CCAT.LOOKUP_TYPE (+) = 'CUSTOMER_CATEGORY'
AND CCAT.LOOKUP_CODE (+) = PARTY.CATEGORY_CODE
AND BRANCHPARTY.PARTY_ID (+) = ABA.BRANCH_ID
AND BRREL.OBJECT_ID = BANKPARTY.PARTY_ID (+)
AND BRREL.SUBJECT_ID (+) = BRANCHPARTY.PARTY_ID
AND BRREL.RELATIONSHIP_TYPE (+) = 'BANK_AND_BRANCH'
AND BRREL.RELATIONSHIP_CODE (+) = 'BRANCH_OF'
AND BRREL.SUBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND BRREL.SUBJECT_TYPE (+) = 'ORGANIZATION'
AND BRREL.OBJECT_TABLE_NAME (+) = 'HZ_PARTIES'
AND BRREL.OBJECT_TYPE (+) = 'ORGANIZATION'
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 BANKORGPROFILE.PARTY_ID (+) = BANKPARTY.PARTY_ID
AND BRANCHORGPROFILE.PARTY_ID (+) = BRANCHPARTY.PARTY_ID
AND SYSDATE BETWEEN NVL(TRUNC(BANKORGPROFILE.EFFECTIVE_START_DATE)
, SYSDATE-1)
AND NVL(TRUNC(BANKORGPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)
AND SYSDATE BETWEEN NVL(TRUNC(BRANCHORGPROFILE.EFFECTIVE_START_DATE)
, SYSDATE-1)
AND NVL(TRUNC(BRANCHORGPROFILE.EFFECTIVE_END_DATE)
, SYSDATE+1)
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 (+)
AND DECODE(IBY.INSTRUMENT_TYPE
, 'BANKACCOUNT'
, IBY.INSTRUMENT_ID
, NULL)=ABA.EXT_BANK_ACCOUNT_ID(+)

Columns