Notes

"
CREATE OR REPLACE FORCE EDITIONABLE VIEW "APPS"."XXAR01_CUSTOMER_V" ("PARTY_ID", "PARTY_NAME", "PARTY_NUMBER", "CUST_ACCOUNT_ID", "ACCOUNT_NUMBER", "ACCOUNT_NAME", "PARTY_SITE_ID", "PARTY_SITE_NUMBER", "PARTY_SITE_NAME", "OPERATING_UNIT_CODE", "CREATION_DATE", "ORGANIZATION_NAME_PHONETIC", "PARTY_ADDRESS1", "PARTY_ADDRESS2", "PARTY_ADDRESS3", "PARTY_ADDRESS4", "PARTY_CITY", "PARTY_COUNTY", "PARTY_STATE", "PARTY_POSTAL_CODE", "PARTY_COUNTRY", "LEGACY_NUM", "CUST_ACCT_SITE_ID", "SITE_USE_ID", "SITE_USE_CODE", "LOCATION_ID", "ALTERNATE_NAME", "ADDRESS1", "ADDRESS2", "ADDRESS3", "CITY", "COUNTY", "STATE", "COUNTRY", "PROVINCE", "POSTAL_CODE", "LOCATION", "PRIMARY_FLAG", "ECE_TP_LOCATION_CODE", "ORG_ID", "STATUS", "SALES_REP_DESC", "DEMAND_CLASS_CODE", "CUST_CLASSIFICATION", "GL_ID_REC", "RECEIVABLE_ACNT", "GL_ID_REV", "REVENUE_ACNT", "ATTRIBUTE_1", "ATTRIBUTE_2", "ATTRIBUTE_3", "ATTRIBUTE_5", "ATTRIBUTE_6", "ATTRIBUTE_7", "ATTRIBUTE_8", "ATTRIBUTE_9", "ATTRIBUTE_10", "ATTRIBUTE_11", "ATTRIBUTE_12", "ATTRIBUTE_13", "ATTRIBUTE_14", "ATTRIBUTE_15", "ATTRIBUTE_16", "ATTRIBUTE_17", "ATTRIBUTE_18", "ATTRIBUTE_19", "ATTRIBUTE_20", "ATTRIBUTE_21", "ATTRIBUTE_22", "ATTRIBUTE_23", "ATTRIBUTE_24", "ATTRIBUTE_25", "ATTRIBUTE_26", "ATTRIBUTE_27", "ATTRIBUTE_28", "ATTRIBUTE_29", "ATTRIBUTE_30", "ATTRIBUTE_31", "ATTRIBUTE_32", "FREIGHT_TERM", "CUSTOMER_CLASS_CODE", "PERSON_FIRST_NAME", "PAYMENT_TERM_ID", "SHIP_VIA", "CUST_ROUTE_NUMBER", "WAREHOUSE_ID", "DROP_DAY_SV", "PRIMARY_PHONE_NUMBER", "PERSON_LAST_NAME", "EMAIL_ADDRESS", "TERRITORY_ID", "ACCNT_STATUS", "SHIP_TO_FLAG") AS
SELECT p.party_id, p.party_name, p.party_number, site.cust_account_id,
site.account_number, site.account_name, site.party_site_id,
site.party_site_number, site.party_site_name,
hou.NAME operating_unit_code, site.creation_date,
p.organization_name_phonetic, TRIM (p.address1) party_address1,
TRIM (p.address2) party_address2, TRIM (p.address3) party_address3,
TRIM (p.address4) party_address4, TRIM (p.city) party_city,
TRIM (p.county) party_county, TRIM (p.state) party_state,
TRIM (p.postal_code) party_postal_code, p.country party_country,
TRIM (site.orig_system_reference) legacy_num,
site.cust_acct_site_id, site.site_use_id,
TRIM (site.site_use_code) site_use_code, site.location_id,
DECODE (site.address_lines_phonetic,
NULL, site.address1,
site.address_lines_phonetic
) alternate_name,
DECODE (site.address_lines_phonetic,
NULL, site.address2,
site.address1
) address1,
DECODE (site.address_lines_phonetic,
NULL, site.address3,
site.address2
) address2,
DECODE (site.address_lines_phonetic,
NULL, site.address4,
site.address3
) address3,
TRIM (site.city) city, TRIM (site.county) county,
TRIM (site.state) state, site.country country, site.province,
site.postal_code, site.LOCATION, site.primary_flag,
site.ece_tp_location_code, site.org_id, site.status,
NVL
((SELECT DISTINCT rsa.NAME
FROM ra_salesreps_all rsa
WHERE rsa.salesrep_id = site.primary_salesrep_id
AND rsa.status = 'A'
AND SYSDATE BETWEEN NVL (rsa.start_date_active,
SYSDATE
)
AND NVL (rsa.end_date_active,
SYSDATE
)),
'N/A'
) sales_rep_desc,
site.demand_class_code, site.cust_classification, site.gl_id_rec,
gcc_rec.concatenated_segments receivable_acnt, site.gl_id_rev,
gcc_rec.concatenated_segments revenue_acnt,NULL attribute_1, NULL attribute_2,
NULL attribute_3, NULL attribute_5, NULL attribute_6,
NULL attribute_7, NULL attribute_8, NULL attribute_9,
NULL attribute_10, NULL attribute_11, NULL attribute_12,
NULL attribute_13, NULL attribute_14, NULL attribute_15,
NULL attribute_16, NULL attribute_17, NULL attribute_18, NULL attribute_19,
NULL attribute_20, NULL attribute_21, NULL attribute_22,
NULL attribute_23, NULL attribute_24, NULL attribute_25,
NULL attribute_26, NULL attribute_27, NULL attribute_28,
NULL attribute_29, NULL attribute_30, NULL attribute_31,
NULL attribute_32, site.freight_term, site.customer_class_code,
p.person_first_name, site.payment_term_id, site.ship_via,
site.attribute1 drop_day_sv, site.warehouse_id,
site.attribute4 cust_route_number, p.primary_phone_number,
p.person_last_name, p.email_address, site.territory_id,site.accnt_status, site.ship_to_flag
FROM hz_parties p,
hr_operating_units hou,
gl_code_combinations_kfv gcc_rec,
gl_code_combinations_kfv gcc_rev,
(SELECT /*Select BILL_TO*/
c_bill.party_id, cs_bill.cust_account_id,
cu_bill.cust_acct_site_id, cu_bill.site_use_id,
cu_bill.site_use_code, cs_bill.orig_system_reference,
l_bill.location_id, l_bill.address_lines_phonetic,
l_bill.address1, l_bill.address2, l_bill.address3,
l_bill.address4, l_bill.city, l_bill.county, l_bill.state,
l_bill.country, l_bill.province, l_bill.postal_code,
cs_bill.creation_date site_creation_date,
c_bill.account_number, c_bill.account_name,
ps_bill.party_site_number, ps_bill.party_site_id,
ps_bill.creation_date, cu_bill.LOCATION,
cu_bill.primary_flag, cs_bill.ece_tp_location_code,
cu_bill.org_id, ps_bill.status, ps_bill.party_site_name,
cu_bill.primary_salesrep_id, cu_bill.demand_class_code,
cs_bill.attribute11 cust_classification, cu_bill.gl_id_rec,
cu_bill.gl_id_rev, NULL attribute_1, NULL attribute_2, NULL attribute_3,
NULL attribute_5, NULL attribute_6, NULL attribute_7,
NULL attribute_8, NULL attribute_9, NULL attribute_10,
NULL attribute_11, NULL attribute_12, NULL attribute_13,
NULL attribute_14, NULL attribute_15, NULL attribute_16 ,NULL attribute_17,
NULL attribute_18, NULL attribute_19, NULL attribute_20,
NULL attribute_21, NULL attribute_22, NULL attribute_23,
NULL attribute_24, NULL attribute_25, NULL attribute_26,
NULL attribute_27, NULL attribute_28, NULL attribute_29,
NULL attribute_30, NULL attribute_31, NULL attribute_32,
cu_bill.freight_term, c_bill.customer_class_code,
cu_bill.payment_term_id, cu_bill.ship_via,
cu_bill.attribute1, cu_bill.warehouse_id,
cu_bill.attribute4, cu_bill.territory_id,c_bill.status accnt_status,cs_bill.ship_to_flag
FROM hz_cust_accounts c_bill,
hz_cust_acct_sites_all cs_bill,
(SELECT *
FROM hz_cust_site_uses_all
WHERE site_use_code = 'BILL_TO') cu_bill,
hz_party_sites ps_bill,
hz_locations l_bill
WHERE c_bill.cust_account_id = cs_bill.cust_account_id
AND cs_bill.cust_acct_site_id = cu_bill.cust_acct_site_id
AND cs_bill.party_site_id = ps_bill.party_site_id
AND ps_bill.location_id = l_bill.location_id
UNION ALL
SELECT /*Select SHIP_TO*/
c_ship.party_id, cs_ship.cust_account_id,
cu_ship.cust_acct_site_id, cu_ship.site_use_id,
cu_ship.site_use_code, cs_ship.orig_system_reference,
l_ship.location_id, l_ship.address_lines_phonetic,
l_ship.address1, l_ship.address2, l_ship.address3,
l_ship.address4, l_ship.city, l_ship.county, l_ship.state,
l_ship.country, l_ship.province, l_ship.postal_code,
cs_ship.creation_date cs_ship_creation_date,
c_ship.account_number, c_ship.account_name,
ps_ship.party_site_number, ps_ship.party_site_id,
ps_ship.creation_date, cu_ship.LOCATION,
cu_ship.primary_flag, cs_ship.ece_tp_location_code,
cu_ship.org_id, ps_ship.status, ps_ship.party_site_name,
cu_ship.primary_salesrep_id, cu_ship.demand_class_code,
cs_ship.attribute11 cust_classification, cu_ship.gl_id_rec,
cu_ship.gl_id_rev,NULL attribute_1, NULL attribute_2, NULL attribute_3,
NULL attribute_5, NULL attribute_6, NULL attribute_7,
NULL attribute_8, NULL attribute_9, NULL attribute_10,
NULL attribute_11, NULL attribute_12, NULL attribute_13,
NULL attribute_14, NULL attribute_15, NULL attribute_16,NULL attribute_17,
NULL attribute_18, NULL attribute_19, NULL attribute_20,
NULL attribute_21, NULL attribute_22, NULL attribute_23,
NULL attribute_24, NULL attribute_25, NULL attribute_26,
NULL attribute_27, NULL attribute_28, NULL attribute_29,
NULL attribute_30, NULL attribute_31, NULL attribute_32,
cu_ship.freight_term, c_ship.customer_class_code,
cu_ship.payment_term_id, cu_ship.ship_via,
cu_ship.attribute1, cu_ship.warehouse_id,
cu_ship.attribute4, cu_ship.territory_id,c_ship.status accnt_status,cs_ship.ship_to_flag
FROM hz_cust_accounts c_ship,
hz_cust_acct_sites_all cs_ship,
(SELECT *
FROM hz_cust_site_uses_all
WHERE site_use_code = 'SHIP_TO') cu_ship,
hz_party_sites ps_ship,
hz_locations l_ship
WHERE c_ship.cust_account_id = cs_ship.cust_account_id
AND cs_ship.cust_acct_site_id = cu_ship.cust_acct_site_id
AND cs_ship.party_site_id = ps_ship.party_site_id
AND ps_ship.location_id = l_ship.location_id
UNION ALL
SELECT /*Select SOLD_TO*/
c_sold.party_id, cs_sold.cust_account_id,
cu_sold.cust_acct_site_id, cu_sold.site_use_id,
cu_sold.site_use_code, cs_sold.orig_system_reference,
l_sold.location_id, l_sold.address_lines_phonetic,
l_sold.address1, l_sold.address2, l_sold.address3,
l_sold.address4, l_sold.city, l_sold.county, l_sold.state,
l_sold.country, l_sold.province, l_sold.postal_code,
cs_sold.creation_date cs_ship_creation_date,
c_sold.account_number, c_sold.account_name,
ps_sold.party_site_number, ps_sold.party_site_id,
ps_sold.creation_date, cu_sold.LOCATION,
cu_sold.primary_flag, cs_sold.ece_tp_location_code,
cu_sold.org_id, ps_sold.status, ps_sold.party_site_name,
cu_sold.primary_salesrep_id, cu_sold.demand_class_code,
cs_sold.attribute11 cust_classification, cu_sold.gl_id_rec,
cu_sold.gl_id_rev,NULL attribute_1, NULL attribute_2, NULL attribute_3,
NULL attribute_5, NULL attribute_6, NULL attribute_7,
NULL attribute_8, NULL attribute_9, NULL attribute_10,
NULL attribute_11, NULL attribute_12, NULL attribute_13,
NULL attribute_14, NULL attribute_15, NULL attribute_16,NULL attribute_17,
NULL attribute_18, NULL attribute_19, NULL attribute_20,
NULL attribute_21, NULL attribute_22, NULL attribute_23,
NULL attribute_24, NULL attribute_25, NULL attribute_26,
NULL attribute_27, NULL attribute_28, NULL attribute_29,
NULL attribute_30, NULL attribute_31, NULL attribute_32,
cu_sold.freight_term, c_sold.customer_class_code,
cu_sold.payment_term_id, cu_sold.ship_via,
cu_sold.attribute1, cu_sold.warehouse_id,
cu_sold.attribute4, cu_sold.territory_id,c_sold.status accnt_status,cs_sold.ship_to_flag
FROM hz_cust_accounts c_sold,
hz_cust_acct_sites_all cs_sold,
(SELECT *
FROM hz_cust_site_uses_all
WHERE site_use_code = 'SOLD_TO') cu_sold,
hz_party_sites ps_sold,
hz_locations l_sold
WHERE c_sold.cust_account_id = cs_sold.cust_account_id
AND cs_sold.cust_acct_site_id = cu_sold.cust_acct_site_id
AND cs_sold.party_site_id = ps_sold.party_site_id
AND ps_sold.location_id = l_sold.location_id
UNION ALL
SELECT /*Select INTER_SHIPTO*/
c_ship.party_id, cs_ship.cust_account_id,
cu_ship.cust_acct_site_id, cu_ship.site_use_id,
cu_ship.site_use_code, cs_ship.orig_system_reference,
l_ship.location_id, l_ship.address_lines_phonetic,
l_ship.address1, l_ship.address2, l_ship.address3,
l_ship.address4, l_ship.city, l_ship.county, l_ship.state,
l_ship.country, l_ship.province, l_ship.postal_code,
cs_ship.creation_date cs_ship_creation_date,
c_ship.account_number, c_ship.account_name,
ps_ship.party_site_number, ps_ship.party_site_id,
ps_ship.creation_date, cu_ship.LOCATION,
cu_ship.primary_flag, cs_ship.ece_tp_location_code,
cu_ship.org_id, ps_ship.status, ps_ship.party_site_name,
cu_ship.primary_salesrep_id, cu_ship.demand_class_code,
cs_ship.attribute11 cust_classification, cu_ship.gl_id_rec,
cu_ship.gl_id_rev, NULL attribute_1, NULL attribute_2, NULL attribute_3,
NULL attribute_5, NULL attribute_6, NULL attribute_7,
NULL attribute_8, NULL attribute_9, NULL attribute_10,
NULL attribute_11, NULL attribute_12, NULL attribute_13,
NULL attribute_14, NULL attribute_15, NULL attribute_16,NULL attribute_17,
NULL attribute_18, NULL attribute_19, NULL attribute_20,
NULL attribute_21, NULL attribute_22, NULL attribute_23,
NULL attribute_24, NULL attribute_25, NULL attribute_26,
NULL attribute_27, NULL attribute_28, NULL attribute_29,
NULL attribute_30, NULL attribute_31, NULL attribute_32,
cu_ship.freight_term, c_ship.customer_class_code,
cu_ship.payment_term_id, cu_ship.ship_via,
cu_ship.attribute1, cu_ship.warehouse_id,
cu_ship.attribute4, cu_ship.territory_id,c_ship.status accnt_status,cs_ship.ship_to_flag
FROM hz_cust_accounts c_ship,
hz_cust_acct_sites_all cs_ship,
(SELECT *
FROM hz_cust_site_uses_all
WHERE site_use_code = 'INTER_SHIPTO') cu_ship,
hz_party_sites ps_ship,
hz_locations l_ship
WHERE c_ship.cust_account_id = cs_ship.cust_account_id
AND cs_ship.cust_acct_site_id = cu_ship.cust_acct_site_id
AND cs_ship.party_site_id = ps_ship.party_site_id
AND ps_ship.location_id = l_ship.location_id) site
WHERE p.party_id = site.party_id
AND p.party_type = 'ORGANIZATION'
AND site.org_id = hou.organization_id(+)
AND site.gl_id_rec = gcc_rec.code_combination_id(+)
AND site.gl_id_rev = gcc_rev.code_combination_id(+)"