[Home] [Help]
View: RCV_OPSM_MANUFACT_ADDRESS_V
View Text
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(+)
APPS.RCV_OPSM_MANUFACT_ADDRESS_V