[Home] [Help]
View: RCV_OPSM_CUSTOMERADDRESS_V
View Text
SELECT RSL.SHIPMENT_HEADER_ID
,
RSL.SHIPMENT_LINE_ID
,
RSL.ITEM_ID
,
RSL.ITEM_DESCRIPTION
,
(DECODE (
(SELECT CUSTOMER_TYPE
FROM AR_CUSTOMERS_ACTIVE_V
WHERE CUSTOMER_ID = HCASA1.CUST_ACCOUNT_ID
)
, 'I'
, 0
, 1)) CHANGEOFOWNERSHIP
,
OOHA.SOLD_TO_ORG_ID SOLDBYORGID
,
OOHA.SOLD_TO_CONTACT_ID SOLDBYCONTACTID
,
HP.PARTY_ID SOLDBYORGCODE
,
HP.PARTY_NAME SOLDBYORGNAME
,
HL.LOCATION_ID SOLDBYLOCATIONID
,
HL.ADDRESS1 SOLDBYLINE1
,
HL.ADDRESS2 SOLDBYLINE2
,
HL.ADDRESS3 SOLDBYLINE3
,
HL.ADDRESS4 SOLDBYLINE4
,
HL.CITY SOLDBYCITY
,
HL.POSTAL_CODE SOLDBYPOSTALCODE
,
HL.COUNTRY SOLDBYCOUNTRY
,
HL.STATE SOLDBYSTATE
,
HL.PROVINCE SOLDBYPROVINCE
,
(SELECT REL_PARTY.PARTY_NAME
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
,
HZ_RELATIONSHIPS REL
,
HZ_ORG_CONTACTS ORG_CONT
,
HZ_PARTIES REL_PARTY
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = OOHA.SOLD_TO_CONTACT_ID
AND REL.PARTY_ID = ACCT_ROLE.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND REL.RELATIONSHIP_ID = 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 = REL_PARTY.PARTY_ID
) SOLDBYCONTACTNAME
,
HCP.PHONE_COUNTRY_CODE SOLDBYPHONECOUNTRYCODE
,
HCP.PHONE_AREA_CODE SOLDBYPHONEAREACODE
,
HCP.PHONE_NUMBER SOLDBYPHONENUMBER
,
HCP.PHONE_EXTENSION SOLDBYPHONEEXTENSION
,
(SELECT HCP.EMAIL_ADDRESS
FROM HZ_CONTACT_POINTS HCP
,
HZ_CUST_ACCOUNT_ROLES HCAR
WHERE HCAR.CUST_ACCOUNT_ROLE_ID(+)=OOHA.SOLD_TO_CONTACT_ID
AND HCP.OWNER_TABLE_ID(+) =HCAR.PARTY_ID
AND HCP.OWNER_TABLE_NAME(+) ='HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE(+) ='EMAIL'
AND HCP.STATUS(+) ='A'
AND HCP.PRIMARY_FLAG(+) ='Y'
) SOLDBYCONTACTEMAIL
,
OOLA.SHIP_TO_ORG_ID SHIPFROMORGID
,
OOLA.SHIP_TO_CONTACT_ID SHIPFROMCONTACTID
,
HP1.PARTY_ID SHIPFROMORGCODE
,
HP1.PARTY_NAME SHIPFROMORGNAME
,
HL1.LOCATION_ID SHIPFROMLOCATIONID
,
HL1.ADDRESS1 SHIPFROMLINE1
,
HL1.ADDRESS2 SHIPFROMLINE2
,
HL1.ADDRESS3 SHIPFROMLINE3
,
HL1.ADDRESS4 SHIPFROMLINE4
,
HL1.CITY SHIPFROMCITY
,
HL1.STATE SHIPFROMSTATE
,
HL1.PROVINCE SHIPFROMPROVINCE
,
HL1.COUNTRY SHIPFROMCOUNTRY
,
HL1.POSTAL_CODE SHIPFROMPOSTALCODE
,
(SELECT REL_PARTY.PARTY_NAME
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
,
HZ_RELATIONSHIPS REL
,
HZ_ORG_CONTACTS ORG_CONT
,
HZ_PARTIES REL_PARTY
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = OOLA.SHIP_TO_CONTACT_ID
AND REL.PARTY_ID = ACCT_ROLE.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND REL.RELATIONSHIP_ID = 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 = REL_PARTY.PARTY_ID
) SHIPFROMCONTACTNAME
,
HCP1.PHONE_COUNTRY_CODE SHIPFROMPHONECOUNTRYCODE
,
HCP1.PHONE_AREA_CODE SHIPFROMPHONEAREACODE
,
HCP1.PHONE_NUMBER SHIPFROMPHONENUMBER
,
HCP1.PHONE_EXTENSION SHIPFROMPHONEEXTENSION
,
(SELECT HCP.EMAIL_ADDRESS
FROM HZ_CONTACT_POINTS HCP
,
HZ_CUST_ACCOUNT_ROLES HCAR
WHERE HCAR.CUST_ACCOUNT_ROLE_ID(+)=OOLA.SHIP_TO_CONTACT_ID
AND HCP.OWNER_TABLE_ID(+) =HCAR.PARTY_ID
AND HCP.OWNER_TABLE_NAME(+) ='HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE(+) ='EMAIL'
AND HCP.STATUS(+) ='A'
AND HCP.PRIMARY_FLAG(+) ='Y'
) SHIPFROMCONTACTEMAIL
,
OOLA.INVOICE_TO_ORG_ID BILLEDBYORGID
,
OOLA.INVOICE_TO_CONTACT_ID BILLEDBYCONTACTID
,
HP2.PARTY_ID BILLEDBYORGCODE
,
HP2.PARTY_NAME BILLEDBYORGNAME
,
HL2.LOCATION_ID BILLEDBYLOCATIONID
,
HL2.ADDRESS1 BILLEDBYLINE1
,
HL2.ADDRESS2 BILLEDBYLINE2
,
HL2.ADDRESS3 BILLEDBYLINE3
,
HL2.ADDRESS4 BILLEDBYLINE4
,
HL2.CITY BILLEDBYCITY
,
HL2.STATE BILLEDBYSTATE
,
HL2.COUNTRY BILLEDBYCOUNTRY
,
HL2.PROVINCE BILLEDBYPROVINCE
,
HL2.POSTAL_CODE BILLEDBYPOSTALCODE
,
(SELECT REL_PARTY.PARTY_NAME
FROM HZ_CUST_ACCOUNT_ROLES ACCT_ROLE
,
HZ_RELATIONSHIPS REL
,
HZ_ORG_CONTACTS ORG_CONT
,
HZ_PARTIES REL_PARTY
WHERE ACCT_ROLE.CUST_ACCOUNT_ROLE_ID = OOLA.INVOICE_TO_CONTACT_ID
AND REL.PARTY_ID = ACCT_ROLE.PARTY_ID
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
AND REL.RELATIONSHIP_ID = 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 = REL_PARTY.PARTY_ID
) BILLEDBYCONTACTNAME
,
HCP2.PHONE_COUNTRY_CODE BILLEDBYPHONECOUNTRYCODE
,
HCP2.PHONE_AREA_CODE BILLEDBYPHONEAREACODE
,
HCP2.PHONE_NUMBER BILLEDBYPHONENUMBER
,
HCP2.PHONE_EXTENSION BILLEDBYPHONEEXTENSION
,
(SELECT HCP.EMAIL_ADDRESS
FROM HZ_CONTACT_POINTS HCP
,
HZ_CUST_ACCOUNT_ROLES HCAR
WHERE HCAR.CUST_ACCOUNT_ROLE_ID(+)=OOLA.INVOICE_TO_CONTACT_ID
AND HCP.OWNER_TABLE_ID(+) =HCAR.PARTY_ID
AND HCP.OWNER_TABLE_NAME(+) ='HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE(+) ='EMAIL'
AND HCP.STATUS(+) ='A'
AND HCP.PRIMARY_FLAG(+) ='Y'
) BILLEDBYCONTACTEMAIL
FROM RCV_SHIPMENT_LINES RSL
,
OE_ORDER_HEADERS_ALL OOHA
,
OE_ORDER_LINES_ALL OOLA
,
HZ_CUST_SITE_USES_ALL HCSUA
,
HZ_CUST_ACCT_SITES_ALL HCASA
,
HZ_PARTY_SITES HPS
,
HZ_LOCATIONS HL
,
HZ_PARTIES HP
,
HZ_CUST_ACCOUNT_ROLES HCAR
,
HZ_CONTACT_POINTS HCP
,
HZ_CUST_SITE_USES_ALL HCSUA1
,
HZ_CUST_ACCT_SITES_ALL HCASA1
,
HZ_PARTY_SITES HPS1
,
HZ_LOCATIONS HL1
,
HZ_PARTIES HP1
,
HZ_CUST_ACCOUNT_ROLES HCAR1
,
HZ_CONTACT_POINTS HCP1
,
HZ_CUST_ACCT_SITES_ALL HCASA2
,
HZ_CUST_SITE_USES_ALL HCSUA2
,
HZ_PARTY_SITES HPS2
,
HZ_LOCATIONS HL2
,
HZ_PARTIES HP2
,
HZ_CUST_ACCOUNT_ROLES HCAR2
,
HZ_CONTACT_POINTS HCP2
WHERE RSL.OE_ORDER_HEADER_ID = OOLA.HEADER_ID
AND RSL.OE_ORDER_LINE_ID = OOLA.LINE_ID
AND OOLA.HEADER_ID = OOHA.HEADER_ID
AND OOHA.SOLD_TO_SITE_USE_ID = HCSUA.SITE_USE_ID(+)
AND HCSUA.SITE_USE_CODE(+) ='SOLD_TO' --HARDCODED
AND HCSUA.PRIMARY_FLAG(+) = 'Y'
AND HCSUA.CUST_ACCT_SITE_ID = HCASA.CUST_ACCT_SITE_ID(+)
AND HCASA.PARTY_SITE_ID =HPS.PARTY_SITE_ID(+)
AND HPS.LOCATION_ID =HL.LOCATION_ID(+)
AND HP.PARTY_ID(+) =HPS.PARTY_ID
AND HCAR.CUST_ACCOUNT_ROLE_ID(+) = OOHA.SOLD_TO_CONTACT_ID
AND HCP.OWNER_TABLE_ID(+) =HCAR.PARTY_ID
AND HCP.OWNER_TABLE_NAME(+) ='HZ_PARTIES'
AND HCP.CONTACT_POINT_TYPE(+) ='PHONE'
AND HCP.STATUS(+) ='A'
AND HCP.PRIMARY_FLAG(+) ='Y'
AND OOLA.SHIP_TO_ORG_ID =HCSUA1.SITE_USE_ID(+)
AND HCSUA1.SITE_USE_CODE(+) ='SHIP_TO' --HARDCODED
AND HCSUA1.CUST_ACCT_SITE_ID =HCASA1.CUST_ACCT_SITE_ID(+)
AND HCASA1.PARTY_SITE_ID =HPS1.PARTY_SITE_ID(+)
AND HPS1.LOCATION_ID =HL1.LOCATION_ID(+)
AND HP1.PARTY_ID(+) =HPS1.PARTY_ID
AND HCAR1.CUST_ACCOUNT_ROLE_ID(+) =OOLA.SHIP_TO_CONTACT_ID
AND HCP1.OWNER_TABLE_ID(+) =HCAR1.PARTY_ID
AND HCP1.OWNER_TABLE_NAME(+) ='HZ_PARTIES'
AND HCP1.CONTACT_POINT_TYPE(+) ='PHONE'
AND HCP1.STATUS(+) ='A'
AND HCP1.PRIMARY_FLAG(+) ='Y'
AND OOLA.INVOICE_TO_ORG_ID =HCSUA2.SITE_USE_ID(+)
AND HCSUA2.SITE_USE_CODE ='BILL_TO' --HARDCODED
AND HCSUA2.CUST_ACCT_SITE_ID =HCASA2.CUST_ACCT_SITE_ID(+)
AND HCASA2.PARTY_SITE_ID =HPS2.PARTY_SITE_ID(+)
AND HPS2.LOCATION_ID =HL2.LOCATION_ID(+)
AND HP2.PARTY_ID =HPS2.PARTY_ID
AND HCAR2.CUST_ACCOUNT_ROLE_ID(+) =OOLA.INVOICE_TO_CONTACT_ID
AND HCP2.OWNER_TABLE_ID(+) =HCAR2.PARTY_ID
AND HCP2.OWNER_TABLE_NAME(+) ='HZ_PARTIES'
AND HCP2.CONTACT_POINT_TYPE(+) ='PHONE'
AND HCP2.STATUS(+) ='A'
AND HCP2.PRIMARY_FLAG(+) ='Y'