VIEW: APPS.AR_CONS_INV_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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