VIEW: APPS.EDWBV_TPRT_TPARTNER_LOC_LCV

Source

View Text - Preformatted

SELECT vns.vendor_site_id || '-' || org_id || '-' || inst.instance_code || '-' || 'SUPPLIER_SITE', vnd.vendor_id || '-' || inst.instance_code || '-' || 'SUPPLIER', vns.ADDRESS_LINE1, vns.ADDRESS_LINE2, vns.ADDRESS_LINE3, NULL, vns.CITY, vns.COUNTY, vns.STATE, vns.ZIP, vns.PROVINCE, vns.COUNTRY, 'VENDOR SITE', vns.vendor_site_code || '(' || vnd.vendor_name || ')', vns.vendor_site_code || '(' || vnd.vendor_name || ')', to_date(NULL), vns.inactive_date, vns.purchasing_site_flag, vns.rfq_only_site_flag, vns.pay_site_flag, apt.name, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, to_number(NULL), NULL, NULL, NULL, NULL, NULL, NULL, inst.instance_code, vns.vendor_site_id, greatest(vns.last_update_date,vnd.last_update_date), vns.creation_date, NULL, NULL, NULL, NULL, NULL, NULL, 'LOCATION', '_DF:PO:PO_VENDOR_SITES:vns', '_DF:_DUMMY:AR:RA_ADDRESSES_HZ:hcas', '_DF:_DUMMY:AR:RA_SITE_USES_HZ:hcss' FROM edw_local_instance inst, (select term_id, name from ap_terms_tl WHERE language = userenv('LANG')) apt, po_vendors vnd, po_vendor_sites_all vns WHERE vns.vendor_id = vnd.vendor_id AND vns.terms_id = apt.term_id (+) UNION ALL SELECT hcss.site_use_id || '-' || inst.instance_code || '-' || 'CUST_SITE_USE', hzca.cust_account_id || '-' || inst.instance_code || '-' || 'CUST_ACCT', parties.address1, parties.address2, parties.address3, parties.address4, parties.city, parties.county, parties.state, parties.postal_code, parties.province, parties.country, 'CUSTOMER SITE USE', hcss.site_use_code || ' (' || hcss.location || ', ' || substrb(parties.party_name, 1, 150) || ')', hcss.site_use_code || ' (' || hcss.location || ', ' || substrb(parties.party_name, 1, 150) || ')', to_date(NULL), to_date(NULL), NULL, NULL, NULL, NULL, hcss.site_use_code, hcss.location, hcss.primary_flag, hcss.status, hcss.orig_system_reference, hcss.sic_code, apt.name, hcss.gsa_indicator, hcss.ship_partial, hcss.ship_via, hcss.fob_point, ot.name, substrb(pl.name, 1, 40), hcss.freight_term, tr.name, hcss.tax_reference, hcss.sort_priority, hcss.tax_code, hcss.demand_class_code, NULL, hcss.tax_header_level_flag, hcss.tax_rounding_rule, substrb(rs.name, 1, 120), inst.instance_code, to_number(NULL), greatest(hcss.last_update_date, hzca.last_update_date, hcas.last_update_date, parties.last_update_date), hcss.creation_date, NULL, NULL, NULL, NULL, NULL, NULL, 'LOCATION', '_DF:_DUMMY:PO:PO_VENDOR_SITES:vns', '_DF:AR:RA_ADDRESSES_HZ:hcas', '_DF:AR:RA_SITE_USES_HZ:hcss' FROM edw_local_instance inst, (select term_id, name from ap_terms_tl where language = userenv('LANG')) apt, ra_salesreps_all rs, ra_territories tr, (select transaction_type_id, name from oe_transaction_types_tl where language = userenv('LANG')) ot, (select price_list_id, name from so_price_lists_tl where language = userenv('LANG')) pl, HZ_PARTIES parties, HZ_CUST_ACCOUNTS hzca, HZ_CUST_ACCT_SITES_ALL hcas, HZ_CUST_SITE_USES_ALL hcss WHERE hcss.CUST_ACCT_SITE_ID = hcas.CUST_ACCT_SITE_ID AND hcas.CUST_ACCOUNT_ID = hzca.CUST_ACCOUNT_ID AND hzca.party_id = parties.party_id AND hcss.primary_salesrep_id = rs.salesrep_id (+) AND hcss.org_id = rs.org_id (+) AND hcss.territory_id = tr.territory_id (+) AND hcss.payment_term_id = apt.term_id (+) AND hcss.price_list_id = pl.price_list_id (+) AND hzca.order_type_id = ot.transaction_type_id (+) UNION ALL SELECT vnd.vendor_id || '-' || inst.instance_code || '-' || 'SUPPLIER' || '-' || 'TPRT', vnd.vendor_id || '-' || inst.instance_code || '-' || 'SUPPLIER', NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, 'VENDOR SITE', vnd.vendor_name, vnd.vendor_name, vnd.start_date_active, vnd.end_date_active, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, to_number(NULL), NULL, NULL, NULL, NULL, NULL, NULL, inst.instance_code, to_number(NULL), vnd.last_update_date, vnd.creation_date, NULL, NULL, NULL, NULL, NULL, NULL, 'TRADE PARTNER', '_DF:_DUMMY:PO:PO_VENDOR_SITES:vns', '_DF:_DUMMY:AR:RA_ADDRESSES_HZ:hcas', '_DF:_DUMMY:AR:RA_SITE_USES_HZ:hcss' FROM edw_local_instance inst, po_vendors vnd UNION ALL SELECT hzca.cust_account_id || '-' || inst.instance_code || '-' || 'CUST_ACCT' || '-' || 'TPRT', hzca.cust_account_id || '-' || inst.instance_code || '-' || 'CUST_ACCT', parties.address1, parties.address2, parties.address3, parties.address4, parties.city, parties.county, parties.state, parties.postal_code, parties.province, parties.country, 'CUSTOMER ACCOUNT', substrb(parties.party_name,1,80) || '(' || hzca.account_number || ')', substrb(parties.party_name,1,80) || '(' || hzca.account_number || ')', hzca.account_activation_date, hzca.account_termination_date, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, parties.gsa_indicator_flag, hzca.ship_partial, hzca.ship_via, hzca.fob_point, NULL, NULL, hzca.freight_term, NULL, NULL, to_number(NULL), hzca.tax_code, NULL, NULL, hzca.tax_header_level_flag, hzca.tax_rounding_rule, NULL, inst.instance_code, hzca.cust_account_id, greatest(parties.last_update_date, hzca.last_update_date), hzca.creation_date, NULL, NULL, NULL, NULL, NULL, NULL, 'TRADE PARTNER', '_DF:_DUMMY:PO:PO_VENDOR_SITES:vns', '_DF:_DUMMY:AR:RA_ADDRESSES_HZ:hcas', '_DF:_DUMMY:AR:RA_SITE_USES_HZ:hcss' FROM edw_local_instance inst, hz_cust_accounts hzca, hz_parties parties WHERE hzca.party_id = parties.party_id UNION ALL SELECT parties.party_id || '-' || inst.instance_code || '-' || 'PARTY' || '-' || 'TPRT', parties.party_id || '-' || inst.instance_code || '-' || 'PARTY', parties.address1, parties.address2, parties.address3, parties.address4, parties.city, parties.county, parties.state, parties.postal_code, parties.province, parties.country, 'PARTY', substrb(parties.party_name,1,320), substrb(parties.party_name,1,320), to_date(NULL), to_date(NULL), NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, to_char(NULL), parties.gsa_indicator_flag, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, to_number(NULL), NULL, NULL, NULL, NULL, NULL, NULL, inst.instance_code, to_number(NULL), parties.last_update_date, parties.creation_date, NULL, NULL, NULL, NULL, NULL, NULL, 'TRADE PARTNER', '_DF:_DUMMY:PO:PO_VENDOR_SITES:vns', '_DF:_DUMMY:AR:RA_ADDRESSES_HZ:hcas', '_DF:_DUMMY:AR:RA_SITE_USES_HZ:hcss' FROM edw_local_instance inst, hz_parties parties

View Text - HTML Formatted

SELECT VNS.VENDOR_SITE_ID || '-' || ORG_ID || '-' || INST.INSTANCE_CODE || '-' || 'SUPPLIER_SITE'
, VND.VENDOR_ID || '-' || INST.INSTANCE_CODE || '-' || 'SUPPLIER'
, VNS.ADDRESS_LINE1
, VNS.ADDRESS_LINE2
, VNS.ADDRESS_LINE3
, NULL
, VNS.CITY
, VNS.COUNTY
, VNS.STATE
, VNS.ZIP
, VNS.PROVINCE
, VNS.COUNTRY
, 'VENDOR SITE'
, VNS.VENDOR_SITE_CODE || '(' || VND.VENDOR_NAME || ')'
, VNS.VENDOR_SITE_CODE || '(' || VND.VENDOR_NAME || ')'
, TO_DATE(NULL)
, VNS.INACTIVE_DATE
, VNS.PURCHASING_SITE_FLAG
, VNS.RFQ_ONLY_SITE_FLAG
, VNS.PAY_SITE_FLAG
, APT.NAME
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, INST.INSTANCE_CODE
, VNS.VENDOR_SITE_ID
, GREATEST(VNS.LAST_UPDATE_DATE
, VND.LAST_UPDATE_DATE)
, VNS.CREATION_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'LOCATION'
, '_DF:PO:PO_VENDOR_SITES:VNS'
, '_DF:_DUMMY:AR:RA_ADDRESSES_HZ:HCAS'
, '_DF:_DUMMY:AR:RA_SITE_USES_HZ:HCSS'
FROM EDW_LOCAL_INSTANCE INST
, (SELECT TERM_ID
, NAME
FROM AP_TERMS_TL
WHERE LANGUAGE = USERENV('LANG')) APT
, PO_VENDORS VND
, PO_VENDOR_SITES_ALL VNS
WHERE VNS.VENDOR_ID = VND.VENDOR_ID
AND VNS.TERMS_ID = APT.TERM_ID (+) UNION ALL SELECT HCSS.SITE_USE_ID || '-' || INST.INSTANCE_CODE || '-' || 'CUST_SITE_USE'
, HZCA.CUST_ACCOUNT_ID || '-' || INST.INSTANCE_CODE || '-' || 'CUST_ACCT'
, PARTIES.ADDRESS1
, PARTIES.ADDRESS2
, PARTIES.ADDRESS3
, PARTIES.ADDRESS4
, PARTIES.CITY
, PARTIES.COUNTY
, PARTIES.STATE
, PARTIES.POSTAL_CODE
, PARTIES.PROVINCE
, PARTIES.COUNTRY
, 'CUSTOMER SITE USE'
, HCSS.SITE_USE_CODE || ' (' || HCSS.LOCATION || '
, ' || SUBSTRB(PARTIES.PARTY_NAME
, 1
, 150) || ')'
, HCSS.SITE_USE_CODE || ' (' || HCSS.LOCATION || '
, ' || SUBSTRB(PARTIES.PARTY_NAME
, 1
, 150) || ')'
, TO_DATE(NULL)
, TO_DATE(NULL)
, NULL
, NULL
, NULL
, NULL
, HCSS.SITE_USE_CODE
, HCSS.LOCATION
, HCSS.PRIMARY_FLAG
, HCSS.STATUS
, HCSS.ORIG_SYSTEM_REFERENCE
, HCSS.SIC_CODE
, APT.NAME
, HCSS.GSA_INDICATOR
, HCSS.SHIP_PARTIAL
, HCSS.SHIP_VIA
, HCSS.FOB_POINT
, OT.NAME
, SUBSTRB(PL.NAME
, 1
, 40)
, HCSS.FREIGHT_TERM
, TR.NAME
, HCSS.TAX_REFERENCE
, HCSS.SORT_PRIORITY
, HCSS.TAX_CODE
, HCSS.DEMAND_CLASS_CODE
, NULL
, HCSS.TAX_HEADER_LEVEL_FLAG
, HCSS.TAX_ROUNDING_RULE
, SUBSTRB(RS.NAME
, 1
, 120)
, INST.INSTANCE_CODE
, TO_NUMBER(NULL)
, GREATEST(HCSS.LAST_UPDATE_DATE
, HZCA.LAST_UPDATE_DATE
, HCAS.LAST_UPDATE_DATE
, PARTIES.LAST_UPDATE_DATE)
, HCSS.CREATION_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'LOCATION'
, '_DF:_DUMMY:PO:PO_VENDOR_SITES:VNS'
, '_DF:AR:RA_ADDRESSES_HZ:HCAS'
, '_DF:AR:RA_SITE_USES_HZ:HCSS'
FROM EDW_LOCAL_INSTANCE INST
, (SELECT TERM_ID
, NAME
FROM AP_TERMS_TL
WHERE LANGUAGE = USERENV('LANG')) APT
, RA_SALESREPS_ALL RS
, RA_TERRITORIES TR
, (SELECT TRANSACTION_TYPE_ID
, NAME
FROM OE_TRANSACTION_TYPES_TL
WHERE LANGUAGE = USERENV('LANG')) OT
, (SELECT PRICE_LIST_ID
, NAME
FROM SO_PRICE_LISTS_TL
WHERE LANGUAGE = USERENV('LANG')) PL
, HZ_PARTIES PARTIES
, HZ_CUST_ACCOUNTS HZCA
, HZ_CUST_ACCT_SITES_ALL HCAS
, HZ_CUST_SITE_USES_ALL HCSS
WHERE HCSS.CUST_ACCT_SITE_ID = HCAS.CUST_ACCT_SITE_ID
AND HCAS.CUST_ACCOUNT_ID = HZCA.CUST_ACCOUNT_ID
AND HZCA.PARTY_ID = PARTIES.PARTY_ID
AND HCSS.PRIMARY_SALESREP_ID = RS.SALESREP_ID (+)
AND HCSS.ORG_ID = RS.ORG_ID (+)
AND HCSS.TERRITORY_ID = TR.TERRITORY_ID (+)
AND HCSS.PAYMENT_TERM_ID = APT.TERM_ID (+)
AND HCSS.PRICE_LIST_ID = PL.PRICE_LIST_ID (+)
AND HZCA.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID (+) UNION ALL SELECT VND.VENDOR_ID || '-' || INST.INSTANCE_CODE || '-' || 'SUPPLIER' || '-' || 'TPRT'
, VND.VENDOR_ID || '-' || INST.INSTANCE_CODE || '-' || 'SUPPLIER'
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'VENDOR SITE'
, VND.VENDOR_NAME
, VND.VENDOR_NAME
, VND.START_DATE_ACTIVE
, VND.END_DATE_ACTIVE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, INST.INSTANCE_CODE
, TO_NUMBER(NULL)
, VND.LAST_UPDATE_DATE
, VND.CREATION_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'TRADE PARTNER'
, '_DF:_DUMMY:PO:PO_VENDOR_SITES:VNS'
, '_DF:_DUMMY:AR:RA_ADDRESSES_HZ:HCAS'
, '_DF:_DUMMY:AR:RA_SITE_USES_HZ:HCSS'
FROM EDW_LOCAL_INSTANCE INST
, PO_VENDORS VND UNION ALL SELECT HZCA.CUST_ACCOUNT_ID || '-' || INST.INSTANCE_CODE || '-' || 'CUST_ACCT' || '-' || 'TPRT'
, HZCA.CUST_ACCOUNT_ID || '-' || INST.INSTANCE_CODE || '-' || 'CUST_ACCT'
, PARTIES.ADDRESS1
, PARTIES.ADDRESS2
, PARTIES.ADDRESS3
, PARTIES.ADDRESS4
, PARTIES.CITY
, PARTIES.COUNTY
, PARTIES.STATE
, PARTIES.POSTAL_CODE
, PARTIES.PROVINCE
, PARTIES.COUNTRY
, 'CUSTOMER ACCOUNT'
, SUBSTRB(PARTIES.PARTY_NAME
, 1
, 80) || '(' || HZCA.ACCOUNT_NUMBER || ')'
, SUBSTRB(PARTIES.PARTY_NAME
, 1
, 80) || '(' || HZCA.ACCOUNT_NUMBER || ')'
, HZCA.ACCOUNT_ACTIVATION_DATE
, HZCA.ACCOUNT_TERMINATION_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, PARTIES.GSA_INDICATOR_FLAG
, HZCA.SHIP_PARTIAL
, HZCA.SHIP_VIA
, HZCA.FOB_POINT
, NULL
, NULL
, HZCA.FREIGHT_TERM
, NULL
, NULL
, TO_NUMBER(NULL)
, HZCA.TAX_CODE
, NULL
, NULL
, HZCA.TAX_HEADER_LEVEL_FLAG
, HZCA.TAX_ROUNDING_RULE
, NULL
, INST.INSTANCE_CODE
, HZCA.CUST_ACCOUNT_ID
, GREATEST(PARTIES.LAST_UPDATE_DATE
, HZCA.LAST_UPDATE_DATE)
, HZCA.CREATION_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'TRADE PARTNER'
, '_DF:_DUMMY:PO:PO_VENDOR_SITES:VNS'
, '_DF:_DUMMY:AR:RA_ADDRESSES_HZ:HCAS'
, '_DF:_DUMMY:AR:RA_SITE_USES_HZ:HCSS'
FROM EDW_LOCAL_INSTANCE INST
, HZ_CUST_ACCOUNTS HZCA
, HZ_PARTIES PARTIES
WHERE HZCA.PARTY_ID = PARTIES.PARTY_ID UNION ALL SELECT PARTIES.PARTY_ID || '-' || INST.INSTANCE_CODE || '-' || 'PARTY' || '-' || 'TPRT'
, PARTIES.PARTY_ID || '-' || INST.INSTANCE_CODE || '-' || 'PARTY'
, PARTIES.ADDRESS1
, PARTIES.ADDRESS2
, PARTIES.ADDRESS3
, PARTIES.ADDRESS4
, PARTIES.CITY
, PARTIES.COUNTY
, PARTIES.STATE
, PARTIES.POSTAL_CODE
, PARTIES.PROVINCE
, PARTIES.COUNTRY
, 'PARTY'
, SUBSTRB(PARTIES.PARTY_NAME
, 1
, 320)
, SUBSTRB(PARTIES.PARTY_NAME
, 1
, 320)
, TO_DATE(NULL)
, TO_DATE(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_CHAR(NULL)
, PARTIES.GSA_INDICATOR_FLAG
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, INST.INSTANCE_CODE
, TO_NUMBER(NULL)
, PARTIES.LAST_UPDATE_DATE
, PARTIES.CREATION_DATE
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, 'TRADE PARTNER'
, '_DF:_DUMMY:PO:PO_VENDOR_SITES:VNS'
, '_DF:_DUMMY:AR:RA_ADDRESSES_HZ:HCAS'
, '_DF:_DUMMY:AR:RA_SITE_USES_HZ:HCSS'
FROM EDW_LOCAL_INSTANCE INST
, HZ_PARTIES PARTIES