eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

[Home] [Help]

View: EDWBV_TPRT_TRADE_PARTNER_LCV

Product:	PO - Purchasing
Description:
Implementation/DBA Data:	Not implemented in this database

View Text

SELECT HZCA.CUST_ACCOUNT_ID || '-' || INST.INSTANCE_CODE || '-' || 'CUST_ACCT'
, NULL
, SUBSTRB(PARTIES.PARTY_NAME
, 1
, 80) || '(' || HZCA.ACCOUNT_NUMBER || ')'
, SUBSTRB(PARTIES.PARTY_NAME
, 1
, 80) || '(' || HZCA.ACCOUNT_NUMBER || ')'
, PARTIES.ORGANIZATION_NAME_PHONETIC
, HZCA.ACCOUNT_ACTIVATION_DATE
, HZCA.ACCOUNT_TERMINATION_DATE
, PARTIES.SIC_CODE
, PARTIES.TAX_REFERENCE
, PARTIES.JGZZ_FISCAL_CODE
, APT.NAME
, NULL
, /* VNDR_NUMBER (?PARTIES.PARTY_NUMBER?) */ NULL
, /* VNDR_TYPE (?PARTIES.PARTY_TYPE?) */ NULL
, /* VND.ONE_TIME_FLAG
, */ NULL
, /* VND.MINORITY_GROUP_LOOKUP_CODE
, */ NULL
, /* VND.WOMEN_OWNED_FLAG
, */ NULL
, /* VND.SMALL_BUSINESS_FLAG
, */ NULL
, /* VND.HOLD_FLAG
, */ NULL
, /* VND.INSPECTION_REQUIRED_FLAG
, */ NULL
, /* VND.RECEIPT_REQUIRED_FLAG
, */ NULL
, /* VND.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, */ NULL
, /* VND.ALLOW_UNORDERED_RECEIPTS_FLAG
, */ HZCA.ACCOUNT_NUMBER
, HZCA.ORIG_SYSTEM_REFERENCE
, HZCA.STATUS
, HZCA.CUSTOMER_TYPE
, DECODE(PARTIES.TOTAL_NUM_OF_ORDERS
, 0
, 'PROSPECT'
, 1
, 'CUSTOMER'
, 'CUSTOMER')
, HZCA.CUSTOMER_CLASS_CODE
, NULL
, /* FOR SALESREP
, NO ORG_ID
, CAN'T FIND SALESREPS */ HZCA.SALES_CHANNEL_CODE
, OT.NAME
, SUBSTRB(PL.NAME
, 1
, 40)
, PARTIES.ANALYSIS_FY
, NULL
, /* HZCA.CATEGORY_CODE
, *****NOT IN INTERNALS***** */ SUBSTRB(PARTIES.CUSTOMER_KEY
, 1
, 80)
, PARTIES.FISCAL_YEAREND_MONTH
, PARTIES.EMPLOYEES_TOTAL
, PARTIES.CURR_FY_POTENTIAL_REVENUE
, PARTIES.NEXT_FY_POTENTIAL_REVENUE
, NULL
, /* HZCA.REFERENCE_USE_FLAG
, *****NOT IN INTERNALS***** */ HZCA.TAX_CODE
, NULL
, /* HZCA.THIRD_PARTY_FLAG
, *****NOT IN INTERNALS***** */ NULL
, /* ACCESS_TEMPLATE_ENTITY_CODE ***OBSOLETED*** */ NULL
, /* HZCA.COMPETITOR_FLAG
, *****NOT IN INTERNALS***** */ NULL
, /* ORIG_SYSTEM ***OBSOLETED*** */ PARTIES.YEAR_ESTABLISHED
, HZCA.COTERMINATE_DAY_MONTH
, HZCA.FOB_POINT
, HZCA.FREIGHT_TERM
, PARTIES.GSA_INDICATOR_FLAG
, HZCA.SHIP_PARTIAL
, HZCA.SHIP_VIA
, PARTIES.DO_NOT_MAIL_FLAG
, HZCA.TAX_HEADER_LEVEL_FLAG
, HZCA.TAX_ROUNDING_RULE
, NULL
, /* USER_ATTRIBUTE 1-5 */ NULL
, NULL
, NULL
, NULL
, GREATEST(HZCA.LAST_UPDATE_DATE
, PARTIES.LAST_UPDATE_DATE)
, HZCA.CREATION_DATE
, NULL
, /* OPERATION_CODE */ HZCA.PARTY_ID
, HZCA.CUST_ACCOUNT_ID
, INST.INSTANCE_CODE
, '_DF:_DUMMY:PO:PO_VENDORS:VND'
, '_DF:AR:HZ_PARTIES:PARTIES'
, '_DF:AR:RA_CUSTOMERS_HZ:HZCA'
FROM EDW_LOCAL_INSTANCE INST
, (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
, (SELECT TERM_ID
, NAME
FROM AP_TERMS_TL
WHERE LANGUAGE = USERENV('LANG')) APT
, HZ_CUST_ACCOUNTS HZCA
, HZ_PARTIES PARTIES
WHERE HZCA.ORDER_TYPE_ID = OT.TRANSACTION_TYPE_ID (+)
AND HZCA.PAYMENT_TERM_ID = APT.TERM_ID (+)
AND HZCA.PRICE_LIST_ID = PL.PRICE_LIST_ID (+)
AND HZCA.PARTY_ID = PARTIES.PARTY_ID UNION ALL /* VENDORS
FROM PO_VENDORS ONLY (WITH PARENTS) */ /*FOR LEVEL > 4
, PARENT_TPARTNER_FK WILL BE POPULATED IN THE PUSH PROGRAM*/ SELECT VND.VENDOR_ID || '-' || INST.INSTANCE_CODE || '-' || 'SUPPLIER'
, DECODE(ELE.HIERARCHY_LEVEL
, 1
, VND.VENDOR_ID ||'-'|| INST.INSTANCE_CODE ||'-'|| 'SUPPLIER'
, 2
, VND.VENDOR_ID ||'-'|| INST.INSTANCE_CODE ||'-'|| 'SUPPLIER'
, 3
, VND.VENDOR_ID ||'-'|| INST.INSTANCE_CODE ||'-'|| 'SUPPLIER'
, 4
, ELE.PARENT_VENDOR_ID ||'-'|| INST.INSTANCE_CODE ||'-'|| 'SUPPLIER'
, NULL)
, VND.VENDOR_NAME
, VND.VENDOR_NAME
, VND.VENDOR_NAME_ALT
, VND.START_DATE_ACTIVE
, VND.END_DATE_ACTIVE
, VND.STANDARD_INDUSTRY_CLASS
, VND.VAT_REGISTRATION_NUM
, VND.NUM_1099
, APT.NAME
, VND.SEGMENT1
, VND.VENDOR_TYPE_LOOKUP_CODE
, VND.ONE_TIME_FLAG
, VND.MINORITY_GROUP_LOOKUP_CODE
, VND.WOMEN_OWNED_FLAG
, VND.SMALL_BUSINESS_FLAG
, VND.HOLD_FLAG
, VND.INSPECTION_REQUIRED_FLAG
, VND.RECEIPT_REQUIRED_FLAG
, VND.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, VND.ALLOW_UNORDERED_RECEIPTS_FLAG
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, VND.LAST_UPDATE_DATE
, VND.CREATION_DATE
, NULL
, VND.VENDOR_ID
, TO_NUMBER(NULL)
, INST.INSTANCE_CODE
, '_DF:PO:PO_VENDORS:VND'
, '_DF:_DUMMY:AR:HZ_PARTIES:PARTIES'
, '_DF:_DUMMY:AR:RA_CUSTOMERS_HZ:HZCA'
FROM EDW_LOCAL_INSTANCE INST
, EDW_PO_VENDOR_HIERARCHIES_V ELE
, (SELECT TERM_ID
, NAME
FROM AP_TERMS_TL
WHERE LANGUAGE = USERENV('LANG')) APT
, PO_VENDORS VND
WHERE ELE.VENDOR_ID = VND.VENDOR_ID
AND VND.TERMS_ID = APT.TERM_ID (+) UNION ALL /* VENDORS
FROM PO_VENDORS ONLY (WITHOUT PARENT) */ SELECT VND.VENDOR_ID || '-' || INST.INSTANCE_CODE || '-' || 'SUPPLIER'
, VND.VENDOR_ID ||'-'|| INST.INSTANCE_CODE ||'-'|| 'SUPPLIER'
, VND.VENDOR_NAME
, VND.VENDOR_NAME
, VND.VENDOR_NAME_ALT
, VND.START_DATE_ACTIVE
, VND.END_DATE_ACTIVE
, VND.STANDARD_INDUSTRY_CLASS
, VND.VAT_REGISTRATION_NUM
, VND.NUM_1099
, APT.NAME
, VND.SEGMENT1
, VND.VENDOR_TYPE_LOOKUP_CODE
, VND.ONE_TIME_FLAG
, VND.MINORITY_GROUP_LOOKUP_CODE
, VND.WOMEN_OWNED_FLAG
, VND.SMALL_BUSINESS_FLAG
, VND.HOLD_FLAG
, VND.INSPECTION_REQUIRED_FLAG
, VND.RECEIPT_REQUIRED_FLAG
, VND.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, VND.ALLOW_UNORDERED_RECEIPTS_FLAG
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, TO_NUMBER(NULL)
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, NULL
, VND.LAST_UPDATE_DATE
, VND.CREATION_DATE
, NULL
, VND.VENDOR_ID
, TO_NUMBER(NULL)
, INST.INSTANCE_CODE
, '_DF:PO:PO_VENDORS:VND'
, '_DF:_DUMMY:AR:HZ_PARTIES:PARTIES'
, '_DF:_DUMMY:AR:RA_CUSTOMERS_HZ:HZCA'
FROM EDW_LOCAL_INSTANCE INST
, (SELECT TERM_ID
, NAME
FROM AP_TERMS_TL
WHERE LANGUAGE = USERENV('LANG')) APT
, PO_VENDORS VND
WHERE VND.PARENT_VENDOR_ID IS NULL
AND VND.TERMS_ID = APT.TERM_ID (+) UNION ALL /* PARTY INFORMATION ONLY*/ SELECT PARTIES.PARTY_ID || '-' || INST.INSTANCE_CODE || '-' || 'PARTY'
, NULL
, SUBSTRB(PARTIES.PARTY_NAME
, 1
, 120)
, SUBSTRB(PARTIES.PARTY_NAME
, 1
, 120)
, PARTIES.ORGANIZATION_NAME_PHONETIC
, TO_DATE(NULL)
, /*HZCA.ACCOUNT_ACTIVATION_DATE
, */ TO_DATE(NULL)
, /*HZCA.ACCOUNT_TERMINATION_DATE
, */ PARTIES.SIC_CODE
, PARTIES.TAX_REFERENCE
, PARTIES.JGZZ_FISCAL_CODE
, NULL
, /*HZCA.PAYMENT_TERM_ID
, */ PARTIES.PARTY_NUMBER
, PARTIES.PARTY_TYPE
, NULL
, /* VND.ONE_TIME_FLAG
, */ NULL
, /* VND.MINORITY_GROUP_LOOKUP_CODE
, */ NULL
, /* VND.WOMEN_OWNED_FLAG
, */ NULL
, /* VND.SMALL_BUSINESS_FLAG
, */ NULL
, /* VND.HOLD_FLAG
, */ NULL
, /* VND.INSPECTION_REQUIRED_FLAG
, */ NULL
, /* VND.RECEIPT_REQUIRED_FLAG
, */ NULL
, /* VND.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, */ NULL
, /* VND.ALLOW_UNORDERED_RECEIPTS_FLAG
, */ NULL
, /* HZCA.ACCOUNT_NUMBER
, */ NULL
, /* HZCA.ORIG_SYSTEM_REFERENCE
, */ NULL
, /* HZCA.STATUS
, */ NULL
, /* HZCA.CUSTOMER_TYPE
, */ DECODE(PARTIES.TOTAL_NUM_OF_ORDERS
, 0
, 'PROSPECT'
, 1
, 'CUSTOMER'
, 'CUSTOMER')
, NULL
, /* HZCA.CUSTOMER_CLASS_CODE
, */ NULL
, /* HZCA.PRIMARY_SALESREP_ID
, */ NULL
, /* HZCA.SALES_CHANNEL_CODE
, */ NULL
, /* OT.NAME
, */ NULL
, /* PL.NAME
, */ PARTIES.ANALYSIS_FY
, NULL
, /* HZCA.CATEGORY_CODE
, *****NOT IN INTERNALS***** */ SUBSTRB(PARTIES.CUSTOMER_KEY
, 1
, 80)
, PARTIES.FISCAL_YEAREND_MONTH
, PARTIES.EMPLOYEES_TOTAL
, PARTIES.CURR_FY_POTENTIAL_REVENUE
, PARTIES.NEXT_FY_POTENTIAL_REVENUE
, NULL
, /* HZCA.REFERENCE_USE_FLAG
, *****NOT IN INTERNALS***** */ PARTIES.TAX_NAME
, /* HZCA.TAX_CODE
, */ NULL
, /* HZCA.THIRD_PARTY_FLAG
, *****NOT IN INTERNALS***** */ NULL
, /* ACCESS_TEMPLATE_ENTITY_CODE ***OBSOLETED*** */ NULL
, /* HZCA.COMPETITOR_FLAG
, *****NOT IN INTERNALS***** */ NULL
, /* ORIG_SYSTEM ***OBSOLETED*** */ PARTIES.YEAR_ESTABLISHED
, NULL
, /* HZCA.COTERMINATE_DAY_MONTH
, */ NULL
, /* HZCA.FOB_POINT
, */ NULL
, /* HZCA.FREIGHT_TERM
, */ PARTIES.GSA_INDICATOR_FLAG
, NULL
, /* HZCA.SHIP_PARTIAL
, */ NULL
, /* HZCA.SHIP_VIA
, */ PARTIES.DO_NOT_MAIL_FLAG
, NULL
, /* HZCA.TAX_HEADER_LEVEL_FLAG
, */ NULL
, /* HZCA.TAX_ROUNDING_RULE
, */ NULL
, /* USER_ATTRIBUTE 1-5 */ NULL
, NULL
, NULL
, NULL
, PARTIES.LAST_UPDATE_DATE
, PARTIES.CREATION_DATE
, NULL
, /* OPERATION_CODE */ PARTIES.PARTY_ID
, TO_NUMBER(NULL)
, INST.INSTANCE_CODE
, '_DF:_DUMMY:PO:PO_VENDORS:VND'
, '_DF:AR:HZ_PARTIES:PARTIES'
, '_DF:_DUMMY:AR:RA_CUSTOMERS_HZ:HZCA'
FROM EDW_LOCAL_INSTANCE INST
, HZ_PARTIES PARTIES

Columns

Name
TRADE_PARTNER_PK
PARENT_TPARTNER_FK
TRADE_PARTNER_DP
NAME
ALTERNATE_NAME
START_ACTIVE_DATE
END_ACTIVE_DATE
SIC_CODE
TAX_REG_NUM
TAXPAYER_ID
PAYMENT_TERMS
VNDR_NUMBER
VNDR_TYPE
VNDR_ONE_TIME
VNDR_MINORITY_GRP
VNDR_WOMEN_OWNED
VNDR_SMALL_BUS
VNDR_HOLD_FLAG
VNDR_INSPECT_REQ
VNDR_RECEIPT_REQ
VNDR_SUB_RECEIPT
VNDR_UNORDER_RCV
CUST_NUMBER
CUST_ORIG_SYS_REF
CUST_STATUS
CUST_TYPE
CUST_PROSPECT
CUST_CLASS
CUST_SALES_REP
CUST_SALES_CHNL
CUST_ORDER_TYPE
CUST_PRICE_LIST
CUST_ANALYSIS_FY
CUST_CAT_CODE
CUST_KEY
CUST_FISCAL_END
CUST_NUM_EMP
CUST_REVENUE_CURR
CUST_REVENUE_NEXT
CUST_REF_USE_FLAG
CUST_TAX_CODE
CUST_THIRD_PARTY
CUST_ACCESS_TMPL
CUST_COMPETITOR
CUST_ORIG_SYS
CUST_YEAR_EST
CUST_COTERM_DATE
CUST_FOB_POINT
CUST_FREIGHT
CUST_GSA_IND
CUST_SHIP_PARTIAL
CUST_SHIP_VIA
CUST_DO_NOT_MAIL
CUST_TAX_HDR_FLAG
CUST_TAX_ROUND
USER_ATTRIBUTE1
USER_ATTRIBUTE2
USER_ATTRIBUTE3
USER_ATTRIBUTE4
USER_ATTRIBUTE5
LAST_UPDATE_DATE
CREATION_DATE
OPERATION_CODE
VENDOR_ID
CUSTOMER_ID
INSTANCE
"_DF:VNDR:_EDW"
"_DF:PARTY:_EDW"
"_DF:CUST_ACCT:_EDW"