VIEW: APPS.RCV_OPSM_MANUFACT_ADDRESS_V

Source

View Text - Preformatted

SELECT RSH.SHIPMENT_HEADER_ID         ,
    RSH.ORGANIZATION_ID                  ,
    RSH.RECEIPT_NUM                      ,
    RSH.BILL_OF_LADING                   ,
    RSH.CREATED_BY CREATED_BY            ,
    RSH.CREATION_DATE CREATION_DATE      ,
    RSH.LAST_UPDATE_DATE LAST_UPDATE_DATE,
    RSH.LAST_UPDATED_BY LAST_UPDATED_BY  ,
    RSH.SHIP_TO_ORG_ID                   ,
    RSL.SHIPMENT_LINE_ID                 ,
    RSL.ITEM_ID                          ,
    RSL.ITEM_DESCRIPTION                 ,
    OOLA.SHIP_FROM_ORG_ID SHIPTOORGID    ,
    OOD.ORGANIZATION_CODE SHIPTOORGCODE  ,
    OOD.ORGANIZATION_NAME SHIPTOORGNAME  ,
    HLA.ADDRESS_LINE_1 SHIPTOLINE1       ,
    HLA.ADDRESS_LINE_2 SHIPTOLINE2       ,
    HLA.ADDRESS_LINE_3 SHIPTOLINE3       ,
    HLA.TOWN_OR_CITY SHIPTOCITY          ,
    HLA.REGION_2 SHIPTOSTATE             ,
    HLA.COUNTRY SHIPTOCOUNTRY            ,
    HLA.POSTAL_CODE SHIPTOPOSTALCODE     ,
    PAPF.FULL_NAME SHIPTOCONTACTNAME     ,
    (SELECT PHONE_NUMBER
       FROM PER_PHONES PP
      WHERE PP.parent_id(+)=PAPF.PERSON_ID
    AND PP.PARENT_TABLE(+) ='PER_ALL_PEOPLE_F'
    AND PP.PHONE_TYPE(+)   ='W1'
    ) SHIPTOCONTACTPHONE                 ,
    PAPF.EMAIL_ADDRESS SHIPTOCONTACTEMAIL,
    OOLA.SOLD_FROM_ORG_ID SOLDTOORGID     ,
    NULL SOLDTOORGCODE                    ,
    HOUV.NAME SOLDTOORGNAME               ,
    HLA1.ADDRESS_LINE_1 SOLDTOLINE1      ,
    HLA1.ADDRESS_LINE_2 SOLDTOLINE2      ,
    HLA1.ADDRESS_LINE_3 SOLDTOLINE3      ,
    HLA1.TOWN_OR_CITY SOLDTOCITY         ,
    HLA1.REGION_2 SOLDTOSTATE            ,
    HLA1.COUNTRY SOLDTOCOUNTRY           ,
    HLA1.POSTAL_CODE SOLDTOPOSTALCODE    ,
    PAPF1.FULL_NAME SOLDTOCONTACTNAME    ,
    (SELECT PHONE_NUMBER
       FROM PER_PHONES PP
      WHERE PP.PARENT_ID(+)=PAPF1.PERSON_ID
    AND PP.PARENT_TABLE(+) ='PER_ALL_PEOPLE_F'
    AND PP.PHONE_TYPE(+)   ='W1'
    ) SOLDTOCONTACTPHONE,
    PAPF1.EMAIL_ADDRESS SOLDTOCONTACTEMAIL
     FROM RCV_SHIPMENT_HEADERS RSH   ,
    RCV_SHIPMENT_LINES RSL           ,
    OE_ORDER_LINES_ALL OOLA          ,
    ORG_ORGANIZATION_DEFINITIONS OOD ,
    HR_ALL_ORGANIZATION_UNITS HAU    ,
    HR_LOCATIONS_ALL HLA             ,
    PER_ALL_PEOPLE_F PAPF            ,    
    HR_ORGANIZATION_UNITS_V HOUV   ,
    HR_LOCATIONS_ALL HLA1            ,
    PER_ALL_PEOPLE_F PAPF1
    WHERE RSH.SHIPMENT_HEADER_ID =RSL.SHIPMENT_HEADER_ID
  AND RSL.OE_ORDER_HEADER_ID     =OOLA.HEADER_ID
  AND RSL.OE_ORDER_LINE_ID       =OOLA.LINE_ID
  AND OOLA.SHIP_FROM_ORG_ID      =OOD.ORGANIZATION_ID
  AND OOD.ORGANIZATION_ID        =HAU.ORGANIZATION_ID
  AND HAU.LOCATION_ID            =HLA.LOCATION_ID(+)
  AND PAPF.PERSON_ID(+)          = HLA.DESIGNATED_RECEIVER_ID
  AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE(+) AND PAPF.EFFECTIVE_END_DATE(+)
  AND OOLA.SOLD_FROM_ORG_ID     =HOUV.ORGANIZATION_ID
  AND HOUV.LOCATION_ID          =HLA1.LOCATION_ID(+)
  AND PAPF1.PERSON_ID(+)        = HLA1.DESIGNATED_RECEIVER_ID
  AND SYSDATE BETWEEN PAPF1.EFFECTIVE_START_DATE(+) AND PAPF1.EFFECTIVE_END_DATE(+)
   

View Text - HTML Formatted

SELECT RSH.SHIPMENT_HEADER_ID
, RSH.ORGANIZATION_ID
, RSH.RECEIPT_NUM
, RSH.BILL_OF_LADING
, RSH.CREATED_BY CREATED_BY
, RSH.CREATION_DATE CREATION_DATE
, RSH.LAST_UPDATE_DATE LAST_UPDATE_DATE
, RSH.LAST_UPDATED_BY LAST_UPDATED_BY
, RSH.SHIP_TO_ORG_ID
, RSL.SHIPMENT_LINE_ID
, RSL.ITEM_ID
, RSL.ITEM_DESCRIPTION
, OOLA.SHIP_FROM_ORG_ID SHIPTOORGID
, OOD.ORGANIZATION_CODE SHIPTOORGCODE
, OOD.ORGANIZATION_NAME SHIPTOORGNAME
, HLA.ADDRESS_LINE_1 SHIPTOLINE1
, HLA.ADDRESS_LINE_2 SHIPTOLINE2
, HLA.ADDRESS_LINE_3 SHIPTOLINE3
, HLA.TOWN_OR_CITY SHIPTOCITY
, HLA.REGION_2 SHIPTOSTATE
, HLA.COUNTRY SHIPTOCOUNTRY
, HLA.POSTAL_CODE SHIPTOPOSTALCODE
, PAPF.FULL_NAME SHIPTOCONTACTNAME
, (SELECT PHONE_NUMBER
FROM PER_PHONES PP
WHERE PP.PARENT_ID(+)=PAPF.PERSON_ID
AND PP.PARENT_TABLE(+) ='PER_ALL_PEOPLE_F'
AND PP.PHONE_TYPE(+) ='W1' ) SHIPTOCONTACTPHONE
, PAPF.EMAIL_ADDRESS SHIPTOCONTACTEMAIL
, OOLA.SOLD_FROM_ORG_ID SOLDTOORGID
, NULL SOLDTOORGCODE
, HOUV.NAME SOLDTOORGNAME
, HLA1.ADDRESS_LINE_1 SOLDTOLINE1
, HLA1.ADDRESS_LINE_2 SOLDTOLINE2
, HLA1.ADDRESS_LINE_3 SOLDTOLINE3
, HLA1.TOWN_OR_CITY SOLDTOCITY
, HLA1.REGION_2 SOLDTOSTATE
, HLA1.COUNTRY SOLDTOCOUNTRY
, HLA1.POSTAL_CODE SOLDTOPOSTALCODE
, PAPF1.FULL_NAME SOLDTOCONTACTNAME
, (SELECT PHONE_NUMBER
FROM PER_PHONES PP
WHERE PP.PARENT_ID(+)=PAPF1.PERSON_ID
AND PP.PARENT_TABLE(+) ='PER_ALL_PEOPLE_F'
AND PP.PHONE_TYPE(+) ='W1' ) SOLDTOCONTACTPHONE
, PAPF1.EMAIL_ADDRESS SOLDTOCONTACTEMAIL
FROM RCV_SHIPMENT_HEADERS RSH
, RCV_SHIPMENT_LINES RSL
, OE_ORDER_LINES_ALL OOLA
, ORG_ORGANIZATION_DEFINITIONS OOD
, HR_ALL_ORGANIZATION_UNITS HAU
, HR_LOCATIONS_ALL HLA
, PER_ALL_PEOPLE_F PAPF
, HR_ORGANIZATION_UNITS_V HOUV
, HR_LOCATIONS_ALL HLA1
, PER_ALL_PEOPLE_F PAPF1
WHERE RSH.SHIPMENT_HEADER_ID =RSL.SHIPMENT_HEADER_ID
AND RSL.OE_ORDER_HEADER_ID =OOLA.HEADER_ID
AND RSL.OE_ORDER_LINE_ID =OOLA.LINE_ID
AND OOLA.SHIP_FROM_ORG_ID =OOD.ORGANIZATION_ID
AND OOD.ORGANIZATION_ID =HAU.ORGANIZATION_ID
AND HAU.LOCATION_ID =HLA.LOCATION_ID(+)
AND PAPF.PERSON_ID(+) = HLA.DESIGNATED_RECEIVER_ID
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE(+)
AND PAPF.EFFECTIVE_END_DATE(+)
AND OOLA.SOLD_FROM_ORG_ID =HOUV.ORGANIZATION_ID
AND HOUV.LOCATION_ID =HLA1.LOCATION_ID(+)
AND PAPF1.PERSON_ID(+) = HLA1.DESIGNATED_RECEIVER_ID
AND SYSDATE BETWEEN PAPF1.EFFECTIVE_START_DATE(+)
AND PAPF1.EFFECTIVE_END_DATE(+)