[Home] [Help]
View: WSH_OPSM_ASN_ORDERS_SPECIFIC_V
View Text
SELECT /*+FIRST_ROWS*/
DISTINCT
'OE' SOURCE_CODE
,
OEH.HEADER_ID SOURCE_HEADER_ID
,
OEH.ORDERED_DATE SALES_ORDER_DATE
,
OEH.ORDER_NUMBER SALES_ORDER_NUMBER
,
OET.NAME ORDER_TYPE_INT
,
OEH.ORDER_CATEGORY_CODE ORDER_CATEGORY_CODE
,
OEL.INVOICE_TO_ORG_ID
,
OEL.INVOICE_TO_CONTACT_ID
,
OEL.SHIP_TO_CONTACT_ID
,
SUBSTRB(PARTY2.PARTY_NAME
, 1
, 50) INVOICE_NAME
,
ACCT_SITE.CUST_ACCT_SITE_ID INVOICE_ADDRESS_ID
,
HCSU1.LOCATION INVOICE_CODE_INT
,
LOC.ADDRESS1 INVOICE_ADDRESS1
,
LOC.ADDRESS2 INVOICE_ADDRESS2
,
LOC.ADDRESS3 INVOICE_ADDRESS3
,
LOC.ADDRESS4 INVOICE_ADDRESS4
,
LOC.CITY INVOICE_CITY
,
LOC.POSTAL_CODE INVOICE_POSTAL_CODE
,
LOC.COUNTRY INVOICE_COUNTRY_INT
,
LOC.STATE INVOICE_STATE_INT
,
LOC.PROVINCE INVOICE_PROVINCE_INT
,
LOC.COUNTY INVOICE_COUNTY
,
REL_PARTY.PERSON_FIRST_NAME||' '||REL_PARTY.PERSON_LAST_NAME INVOICE_CONT_NAME
,
ORG_CONT.JOB_TITLE INVOICE_CONT_JOB_TITLE
,
CONTACT1.EMAIL_ADDRESS INVOICE_CONT_EMAIL_ADDRESS
,
CONTACT.PHONE_COUNTRY_CODE INVOICE_CONT_COUNTRY_CODE
,
CONTACT.PHONE_AREA_CODE INVOICE_CONT_AREA_CODE
,
CONTACT.PHONE_NUMBER INVOICE_CONT_PHONE_NUMBER
,
CONTACT.PHONE_EXTENSION INVOICE_CONT_PHONE_EXTN
FROM OE_ORDER_HEADERS_ALL OEH
,
OE_ORDER_LINES_ALL OEL
,
HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
,
HZ_RELATIONSHIPS REL
,
HZ_ORG_CONTACTS ORG_CONT
,
HZ_CONTACT_POINTS CONTACT
,
HZ_CONTACT_POINTS CONTACT1
,
HZ_PARTIES REL_PARTY
,
HZ_PARTY_SITES PARTY_SITE
,
HZ_LOCATIONS LOC
,
HZ_CUST_ACCT_SITES_ALL ACCT_SITE
,
OE_TRANSACTION_TYPES_TL OET
,
FND_LANGUAGES FL
,
HZ_CUST_SITE_USES_ALL HCSU1
,
HZ_PARTIES PARTY2
,
HZ_CUST_ACCOUNTS CUST_ACCT2
WHERE OEH.HEADER_ID = OEL.HEADER_ID(+)
AND OEH.ORDER_TYPE_ID+0 = OET.TRANSACTION_TYPE_ID(+)
AND OET.LANGUAGE = FL.LANGUAGE_CODE
AND FL.INSTALLED_FLAG = 'B'
AND OEL.INVOICE_TO_ORG_ID+0 = HCSU1.SITE_USE_ID(+)
AND OEL.SOLD_TO_ORG_ID+0 = CUST_ACCT2.CUST_ACCOUNT_ID(+)
AND CUST_ACCT2.PARTY_ID+0 = PARTY2.PARTY_ID(+)
AND OEL.INVOICE_TO_CONTACT_ID+0 = ACCT_ROLE.CUST_ACCOUNT_ROLE_ID(+)
AND ACCT_ROLE.ROLE_TYPE(+) = 'CONTACT'
AND REL.PARTY_ID(+) = ACCT_ROLE.PARTY_ID+0
AND REL.RELATIONSHIP_ID+0 = ORG_CONT.PARTY_RELATIONSHIP_ID(+)
AND REL.SUBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.OBJECT_TABLE_NAME(+) = 'HZ_PARTIES'
AND REL.DIRECTIONAL_FLAG(+)||'' = 'F'
AND REL.SUBJECT_ID+0 = REL_PARTY.PARTY_ID(+)
AND ACCT_ROLE.PARTY_ID +0 = CONTACT.OWNER_TABLE_ID(+)
AND CONTACT.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONTACT.CONTACT_POINT_TYPE(+) = 'PHONE'
AND CONTACT.PRIMARY_FLAG(+) = 'Y'
AND CONTACT.STATUS(+) = 'A'
AND ACCT_ROLE.PARTY_ID +0 = CONTACT1.OWNER_TABLE_ID(+)
AND CONTACT1.OWNER_TABLE_NAME(+) = 'HZ_PARTIES'
AND CONTACT1.CONTACT_POINT_TYPE(+)= 'EMAIL'
AND CONTACT1.PRIMARY_FLAG(+) = 'Y'
AND CONTACT1.STATUS(+) = 'A'
AND HCSU1.CUST_ACCT_SITE_ID+0 = ACCT_SITE.CUST_ACCT_SITE_ID(+)
AND ACCT_SITE.PARTY_SITE_ID+0 = PARTY_SITE.PARTY_SITE_ID(+)
AND LOC.LOCATION_ID(+) = PARTY_SITE.LOCATION_ID
APPS.WSH_OPSM_ASN_ORDERS_SPECIFIC_V