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.

VIEW: APPS.RCV_OPSM_CUSTOMERADDRESS_V

Source

View Text - Preformatted


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'

View Text - HTML Formatted

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'