[Home] [Help]
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
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
|
|
|
|