View: AR_CONS_INV_V

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

View Text

SELECT CI.CUSTOMER_ID
, HZP.PARTY_NAME
, CA.ACCOUNT_NUMBER
, HZPC.NAME PROFILE_CLASS_NAME
, SU_BILL.LOCATION
, CI.ORG_ID
, HR.NAME OPERATING_UNIT_NAME
, CI.DUE_DATE
, CI.CURRENCY_CODE
, CI.CONS_BILLING_NUMBER
, RAA_BILL_LOC.ADDRESS1
, RAA_BILL_LOC.ADDRESS2
, RAA_BILL_LOC.ADDRESS3
, RAA_BILL_LOC.ADDRESS4
, HZ_FORMAT_PUB.FORMAT_ADDRESS(RAA_BILL_LOC.LOCATION_ID) FORMATTED_BILL_TO_ADDRESS
, RAA_BILL_LOC.CITY
, RAA_BILL_LOC.COUNTY
, RAA_BILL_LOC.STATE
, RAA_BILL_LOC.PROVINCE
, RAA_BILL_LOC.POSTAL_CODE
, FT_BILL.TERRITORY_SHORT_NAME
, RAA_REMIT_LOC.ADDRESS1
, RAA_REMIT_LOC.ADDRESS2
, RAA_REMIT_LOC.ADDRESS3
, RAA_REMIT_LOC.ADDRESS4
, HZ_FORMAT_PUB.FORMAT_ADDRESS(RAA_REMIT_LOC.LOCATION_ID) FORMATTED_REMIT_TO_ADDRESS
, RAA_REMIT_LOC.CITY
, RAA_REMIT_LOC.COUNTY
, RAA_REMIT_LOC.STATE
, RAA_REMIT_LOC.PROVINCE
, RAA_REMIT_LOC.POSTAL_CODE
, FT_REMIT.TERRITORY_SHORT_NAME
, 'ACCOUNT' BILL_LEVEL
, CI.CONS_INV_TYPE
, CI.BILLING_CYCLE_ID
, CI.LAST_BILLING_DATE
, CI.BILLING_DATE
, CI_P.ATTRIBUTE_CATEGORY
, CI_P.ATTRIBUTE1
, CI_P.ATTRIBUTE2
, CI_P.ATTRIBUTE3
, CI_P.ATTRIBUTE4
, CI_P.ATTRIBUTE5
, CI_P.ATTRIBUTE6
, CI_P.ATTRIBUTE7
, CI_P.ATTRIBUTE8
, CI_P.ATTRIBUTE9
, CI_P.ATTRIBUTE10
, CI_P.ATTRIBUTE11
, CI_P.ATTRIBUTE12
, CI_P.ATTRIBUTE13
, CI_P.ATTRIBUTE14
, CI_P.ATTRIBUTE15
, NULL
, MAX(CI.UNPAID_REASON)
, MAX(CI.CONCURRENT_REQUEST_ID)
, CI.TERM_ID
, RT.NAME TERM_NAME
, NULL SITE_USE_ID
, MAX(CI.ISSUE_DATE)
, SUM(CI.BEGINNING_BALANCE)
, SUM(CI.ENDING_BALANCE)
, SUM(CI.TOTAL_RECEIPTS_AMT)
, SUM(CI.TOTAL_ADJUSTMENTS_AMT)
, SUM(CI.TOTAL_CREDITS_AMT)
, SUM(CI.TOTAL_FINANCE_CHARGES_AMT)
, SUM(CI.TOTAL_TRX_AMT)
, SUM(CI.TOTAL_TAX_AMT)
, SUM(CI.AGING_BUCKET1_AMT)
, SUM(CI.AGING_BUCKET2_AMT)
, SUM(CI.AGING_BUCKET3_AMT)
, SUM(CI.AGING_BUCKET4_AMT)
, SUM(CI.AGING_BUCKET5_AMT)
, SUM(CI.AGING_BUCKET6_AMT)
, SUM(CI.AGING_BUCKET7_AMT)
FROM AR_CONS_INV CI
, AR_CONS_INV CI_P
, HZ_CUSTOMER_PROFILES CP
, HZ_CUST_ACCOUNTS CA
, HZ_CUST_ACCT_SITES RAA_BILL
, HZ_PARTY_SITES RAA_BILL_PS
, HZ_LOCATIONS RAA_BILL_LOC
, HZ_CUST_SITE_USES SU_BILL
, FND_TERRITORIES_VL FT_BILL
, HZ_CUST_ACCT_SITES RAA_REMIT
, HZ_PARTY_SITES RAA_REMIT_PS
, HZ_PARTIES HZP
, HZ_LOCATIONS RAA_REMIT_LOC
, HZ_CUST_PROFILE_CLASSES HZPC
, FND_TERRITORIES_VL FT_REMIT
, HR_OPERATING_UNITS HR
, RA_TERMS_VL RT
WHERE CI.CUSTOMER_ID = CP.CUST_ACCOUNT_ID
AND CI.BILL_LEVEL_FLAG = 'A'
AND CP.SITE_USE_ID IS NULL
AND CA.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
AND CP.PROFILE_CLASS_ID = HZPC.PROFILE_CLASS_ID
AND CI.CUSTOMER_ID = RAA_BILL.CUST_ACCOUNT_ID
AND SU_BILL.CUST_ACCT_SITE_ID = RAA_BILL.CUST_ACCT_SITE_ID
AND SU_BILL.SITE_USE_CODE = 'BILL_TO'
AND SU_BILL.PRIMARY_FLAG = 'Y'
AND RAA_BILL.PARTY_SITE_ID = RAA_BILL_PS.PARTY_SITE_ID
AND RAA_BILL_LOC.LOCATION_ID = RAA_BILL_PS.LOCATION_ID
AND RAA_BILL_LOC.COUNTRY = FT_BILL.TERRITORY_CODE(+)
AND CI_P.SITE_USE_ID = SU_BILL.SITE_USE_ID
AND CI_P.CONS_BILLING_NUMBER = CI.CONS_BILLING_NUMBER
AND CI_P.REMIT_TO_ADDRESS_ID = RAA_REMIT.CUST_ACCT_SITE_ID(+)
AND RAA_REMIT.PARTY_SITE_ID = RAA_REMIT_PS.PARTY_SITE_ID(+)
AND RAA_REMIT_LOC.LOCATION_ID(+) = RAA_REMIT_PS.LOCATION_ID
AND RAA_REMIT_LOC.COUNTRY = FT_REMIT.TERRITORY_CODE(+)
AND HZP.PARTY_ID = CA.PARTY_ID
AND HR.ORGANIZATION_ID = CI.ORG_ID
AND RT.TERM_ID = CI.TERM_ID GROUP BY CI.CUSTOMER_ID
, HZP.PARTY_NAME
, CA.ACCOUNT_NUMBER
, HZPC.NAME
, CI.ORG_ID
, HR.NAME
, CI.DUE_DATE
, CI.CURRENCY_CODE
, CI.CONS_BILLING_NUMBER
, SU_BILL.LOCATION
, RAA_BILL_LOC.ADDRESS1
, RAA_BILL_LOC.ADDRESS2
, RAA_BILL_LOC.ADDRESS3
, RAA_BILL_LOC.ADDRESS4
, HZ_FORMAT_PUB.FORMAT_ADDRESS( RAA_BILL_LOC.LOCATION_ID)
, RAA_BILL_LOC.CITY
, RAA_BILL_LOC.COUNTY
, RAA_BILL_LOC.STATE
, RAA_BILL_LOC.PROVINCE
, RAA_BILL_LOC.POSTAL_CODE
, FT_BILL.TERRITORY_SHORT_NAME
, RAA_REMIT_LOC.ADDRESS1
, RAA_REMIT_LOC.ADDRESS2
, RAA_REMIT_LOC.ADDRESS3
, RAA_REMIT_LOC.ADDRESS4
, HZ_FORMAT_PUB.FORMAT_ADDRESS(RAA_REMIT_LOC.LOCATION_ID)
, RAA_REMIT_LOC.CITY
, RAA_REMIT_LOC.COUNTY
, RAA_REMIT_LOC.STATE
, RAA_REMIT_LOC.PROVINCE
, RAA_REMIT_LOC.POSTAL_CODE
, FT_REMIT.TERRITORY_SHORT_NAME
, CI.CONS_INV_TYPE
, CI.BILLING_CYCLE_ID
, CI.LAST_BILLING_DATE
, CI.BILLING_DATE
, CI_P.ATTRIBUTE_CATEGORY
, CI_P.ATTRIBUTE1
, CI_P.ATTRIBUTE2
, CI_P.ATTRIBUTE3
, CI_P.ATTRIBUTE4
, CI_P.ATTRIBUTE5
, CI_P.ATTRIBUTE6
, CI_P.ATTRIBUTE7
, CI_P.ATTRIBUTE8
, CI_P.ATTRIBUTE9
, CI_P.ATTRIBUTE10
, CI_P.ATTRIBUTE11
, CI_P.ATTRIBUTE12
, CI_P.ATTRIBUTE13
, CI_P.ATTRIBUTE14
, CI_P.ATTRIBUTE15
, CI.TERM_ID
, RT.NAME UNION SELECT CI.CUSTOMER_ID
, HZP.PARTY_NAME
, CA.ACCOUNT_NUMBER
, HZPC.NAME PROFILE_CLASS_NAME
, SU_BILL.LOCATION
, CI.ORG_ID
, HR.NAME OPERATING_UNIT_NAME
, CI.DUE_DATE
, CI.CURRENCY_CODE
, CI.CONS_BILLING_NUMBER
, RAA_BILL_LOC.ADDRESS1
, RAA_BILL_LOC.ADDRESS2
, RAA_BILL_LOC.ADDRESS3
, RAA_BILL_LOC.ADDRESS4
, HZ_FORMAT_PUB.FORMAT_ADDRESS(RAA_BILL_LOC.LOCATION_ID) FORMATTED_BILL_TO_ADDRESS
, RAA_BILL_LOC.CITY
, RAA_BILL_LOC.COUNTY
, RAA_BILL_LOC.STATE
, RAA_BILL_LOC.PROVINCE
, RAA_BILL_LOC.POSTAL_CODE
, FT_BILL.TERRITORY_SHORT_NAME
, RAA_REMIT_LOC.ADDRESS1
, RAA_REMIT_LOC.ADDRESS2
, RAA_REMIT_LOC.ADDRESS3
, RAA_REMIT_LOC.ADDRESS4
, HZ_FORMAT_PUB.FORMAT_ADDRESS(RAA_REMIT_LOC.LOCATION_ID) FORMATTED_REMIT_TO_ADDRESS
, RAA_REMIT_LOC.CITY
, RAA_REMIT_LOC.COUNTY
, RAA_REMIT_LOC.STATE
, RAA_REMIT_LOC.PROVINCE
, RAA_REMIT_LOC.POSTAL_CODE
, FT_REMIT.TERRITORY_SHORT_NAME
, 'SITE' BILL_LEVEL
, CI.CONS_INV_TYPE
, CI.BILLING_CYCLE_ID
, LAST_BILLING_DATE
, BILLING_DATE
, CI.ATTRIBUTE_CATEGORY
, CI.ATTRIBUTE1
, CI.ATTRIBUTE2
, CI.ATTRIBUTE3
, CI.ATTRIBUTE4
, CI.ATTRIBUTE5
, CI.ATTRIBUTE6
, CI.ATTRIBUTE7
, CI.ATTRIBUTE8
, CI.ATTRIBUTE9
, CI.ATTRIBUTE10
, CI.ATTRIBUTE11
, CI.ATTRIBUTE12
, CI.ATTRIBUTE13
, CI.ATTRIBUTE14
, CI.ATTRIBUTE15
, CI.CONS_INV_ID
, CI.UNPAID_REASON
, CI.CONCURRENT_REQUEST_ID
, CI.TERM_ID
, RT.NAME TERM_NAME
, CI.SITE_USE_ID
, CI.ISSUE_DATE
, CI.BEGINNING_BALANCE
, CI.ENDING_BALANCE
, TOTAL_RECEIPTS_AMT
, TOTAL_ADJUSTMENTS_AMT
, TOTAL_CREDITS_AMT
, TOTAL_FINANCE_CHARGES_AMT
, TOTAL_TRX_AMT
, TOTAL_TAX_AMT
, AGING_BUCKET1_AMT
, AGING_BUCKET2_AMT
, AGING_BUCKET3_AMT
, AGING_BUCKET4_AMT
, AGING_BUCKET5_AMT
, AGING_BUCKET6_AMT
, AGING_BUCKET7_AMT
FROM AR_CONS_INV CI
, HZ_CUSTOMER_PROFILES CP
, HZ_CUST_ACCOUNTS CA
, HZ_CUST_ACCT_SITES RAA_BILL
, HZ_PARTY_SITES RAA_BILL_PS
, HZ_LOCATIONS RAA_BILL_LOC
, HZ_CUST_SITE_USES SU_BILL
, FND_TERRITORIES_VL FT_BILL
, HZ_CUST_ACCT_SITES RAA_REMIT
, HZ_PARTY_SITES RAA_REMIT_PS
, HZ_PARTIES HZP
, HZ_LOCATIONS RAA_REMIT_LOC
, HZ_CUST_PROFILE_CLASSES HZPC
, FND_TERRITORIES_VL FT_REMIT
, RA_TERMS_VL RT
, HR_OPERATING_UNITS HR
WHERE CI.CUSTOMER_ID = CP.CUST_ACCOUNT_ID
AND CI.BILL_LEVEL_FLAG = 'S'
AND CP.SITE_USE_ID = CI.SITE_USE_ID
AND CA.CUST_ACCOUNT_ID = CP.CUST_ACCOUNT_ID
AND CP.PROFILE_CLASS_ID = HZPC.PROFILE_CLASS_ID
AND CA.PARTY_ID = HZP.PARTY_ID
AND CI.SITE_USE_ID = SU_BILL.SITE_USE_ID
AND SU_BILL.CUST_ACCT_SITE_ID = RAA_BILL.CUST_ACCT_SITE_ID
AND RAA_BILL.PARTY_SITE_ID = RAA_BILL_PS.PARTY_SITE_ID
AND RAA_BILL_LOC.LOCATION_ID = RAA_BILL_PS.LOCATION_ID
AND RAA_BILL_LOC.COUNTRY = FT_BILL.TERRITORY_CODE(+)
AND CI.REMIT_TO_ADDRESS_ID = RAA_REMIT.CUST_ACCT_SITE_ID(+)
AND RAA_REMIT.PARTY_SITE_ID = RAA_REMIT_PS.PARTY_SITE_ID(+)
AND RAA_REMIT_LOC.LOCATION_ID(+) = RAA_REMIT_PS.LOCATION_ID
AND RAA_REMIT_LOC.COUNTRY = FT_REMIT.TERRITORY_CODE(+)
AND RT.TERM_ID = CI.TERM_ID
AND HR.ORGANIZATION_ID = CI.ORG_ID

Columns