[Home] [Help]
View: WSH_OPSM_ASN_DELIVERIES_V
View Text
SELECT --+ RULE
DISTINCT SYSDATE TRANSACTION_DATE
,
WND.NAME DOCUMENT_CODE
,
WND.ORGANIZATION_ID ORGANIZATION_ID
,
WND.DELIVERY_ID DELIVERY_ID
,
WND.NAME DELIVERY_NAME
,
MTP.ORGANIZATION_CODE ORGANIZATION_CODE
,
WND.INITIAL_PICKUP_LOCATION_ID WAREHOUSE_LOCATION_ID
,
MTP.ORGANIZATION_CODE WAREHOUSE_CODE_INT
,
HOU.NAME WAREHOUSE_NAME
,
WSHL.ADDRESS1 WAREHOUSE_ADDRESS1
,
WSHL.ADDRESS2 WAREHOUSE_ADDRESS2
,
WSHL.ADDRESS3 WAREHOUSE_ADDRESS3
,
WSHL.ADDRESS4 WAREHOUSE_ADDRESS4
,
WSHL.CITY WAREHOUSE_CITY
,
WSHL.POSTAL_CODE WAREHOUSE_POSTAL_CODE
,
WSHL.COUNTRY WAREHOUSE_COUNTRY_INT
,
WSHL.COUNTY WAREHOUSE_REGION1_INT
,
WSHL.STATE WAREHOUSE_REGION2_INT
,
PAPF.FULL_NAME WAREHOUSE_CONTACT_NAME
,
PAPF.EMAIL_ADDRESS WAREHOUSE_CONTACT_EMAILID
,
(SELECT PP.PHONE_NUMBER
FROM PER_PHONES PP
WHERE PAPF.PERSON_ID = PP.PARENT_ID
AND PP.PARENT_TABLE = 'PER_ALL_PEOPLE_F'
AND PP.PHONE_TYPE = 'W1'
) WAREHOUSE_CONTACT_PHONENUMBER
,
HCSU.LOCATION DESTINATION_CODE_INT
,
SUBSTRB(PARTY1.PARTY_NAME
, 1
, 50) DESTINATION_NAME
,
WSHL2.ADDRESS1 DESTINATION_ADDRESS1
,
WSHL2.ADDRESS2 DESTINATION_ADDRESS2
,
WSHL2.ADDRESS3 DESTINATION_ADDRESS3
,
WSHL2.ADDRESS4 DESTINATION_ADDRESS4
,
WSHL2.CITY DESTINATION_CITY
,
WSHL2.POSTAL_CODE DESTINATION_POSTAL_CODE
,
WSHL2.COUNTRY DESTINATION_COUNTRY_INT
,
WSHL2.STATE DESTINATION_STATE_INT
,
WSHL2.PROVINCE DESTINATION_PROVINCE_INT
,
WSHL2.COUNTY DESTINATION_COUNTY
,
DESTINATION_CONTACT_DETAILS.CONTACT_ID DESTINATION_CONT_ID
,
DESTINATION_CONTACT_DETAILS.CONTACT_AREA_CODE DESTINATION_CONT_AREA_CODE
,
DESTINATION_CONTACT_DETAILS.CONTACT_PHONE_NUMBER DESTINATION_CONT_PHONE_NUMBER
,
DESTINATION_CONTACT_DETAILS.CONTACT_NAME DESTINATION_CONT_NAME
,
DESTINATION_CONTACT_DETAILS.CONTACT_EMAIL DESTINATION_CONT_EMAIL
,
DESTINATION_CONTACT_DETAILS.CONTACT_COUNTRY_CODE DESTINATION_CONT_COUNTRY_CODE
,
DESTINATION_CONTACT_DETAILS.CONTACT_PHONE_EXTN DESTINATION_CONT_PHONE_EXTN
,
WDI.SEQUENCE_NUMBER BILL_OF_LADING
,
WND.FREIGHT_TERMS_CODE FREIGHT_TERMS_CODE_INT
,
WND.FOB_CODE FOB_POINT_CODE_INT
,
WND.INITIAL_PICKUP_DATE INITIAL_PICKUP_DATE
,
WND.PORT_OF_LOADING PORT_OF_LOADING
,
(DECODE (
(SELECT CUSTOMER_TYPE
FROM AR_CUSTOMERS_ACTIVE_V
WHERE CUSTOMER_ID = CUST_ACCT1.CUST_ACCOUNT_ID
)
, 'I'
, 0
, 1)) CHANGE_OF_OWNERSHIP
,
WND.DOCK_CODE DOCK_CODE
,
WND.STATUS_CODE STATUS_CODE
,
WND.CREATED_BY CREATED_BY
,
WND.CREATION_DATE CREATION_DATE
,
WND.LAST_UPDATED_BY LAST_UPDATED_BY
,
WND.LAST_UPDATE_DATE LAST_UPDATE_DATE
,
SPEC.INVOICE_TO_ORG_ID
,
SPEC.INVOICE_TO_CONTACT_ID
,
SPEC.INVOICE_NAME
,
SPEC.INVOICE_ADDRESS1
,
SPEC.INVOICE_ADDRESS2
,
SPEC.INVOICE_ADDRESS3
,
SPEC.INVOICE_ADDRESS4
,
SPEC.INVOICE_CITY
,
SPEC.INVOICE_POSTAL_CODE
,
SPEC.INVOICE_COUNTRY_INT
,
SPEC.INVOICE_STATE_INT
,
SPEC.INVOICE_PROVINCE_INT
,
SPEC.INVOICE_COUNTY
,
SPEC.INVOICE_CONT_NAME
,
SPEC.INVOICE_CONT_JOB_TITLE
,
SPEC.INVOICE_CONT_EMAIL_ADDRESS
,
SPEC.INVOICE_CONT_COUNTRY_CODE
,
SPEC.INVOICE_CONT_AREA_CODE
,
SPEC.INVOICE_CONT_PHONE_NUMBER
,
SPEC.INVOICE_CONT_PHONE_EXTN
,
COMM.SOLDTO_CUSTOMER_ID
,
COMM.SOLDTO_CONTACT_ID
,
COMM.SOLDTO_ADDRESS_ID
,
COMM.SOLDTO_NAME
,
COMM.SOLDTO_ADDRESS1
,
COMM.SOLDTO_ADDRESS2
,
COMM.SOLDTO_ADDRESS3
,
COMM.SOLDTO_ADDRESS4
,
COMM.SOLDTO_CITY
,
COMM.SOLDTO_POSTAL_CODE
,
COMM.SOLDTO_COUNTRY_INT
,
COMM.SOLDTO_STATE_INT
,
COMM.SOLDTO_PROVINCE_INT
,
COMM.SOLDTO_COUNTY
,
COMM.SOLDTO_CONT_NAME
,
COMM.SOLDTO_CONT_JOB_TITLE
,
COMM.SOLDTO_CONT_EMAIL
,
COMM.SOLDTO_CONT_COUNTRY_CODE
,
COMM.SOLDTO_CONT_AREA_CODE
,
COMM.SOLDTO_CONT_PHONE_NUMBER
,
COMM.SOLDTO_CONT_PHONE_EXTN
,
COMM.SOLDBY_LOCATION_CODE
,
COMM.SOLDBY_COUNTRY
,
COMM.SOLDBY_ADDRESS_LINE_1
,
COMM.SOLDBY_ADDRESS_LINE_2
,
COMM.SOLDBY_ADDRESS_LINE_3
,
COMM.SOLDBY_ADDRESS_LINE_4
,
COMM.SOLDBY_COUNTY
,
COMM.SOLDBY_STATE
,
COMM.SOLDBY_POSTAL_CODE
,
COMM.SOLDBY_CITY
,
COMM.SOLDBY_ORGANIZATION_ID
,
COMM.SOLDBY_NAME
,
COMM.SOLDBY_CONTACT_NAME
,
COMM.SOLDBY_CONTACT_EMAILID
,
COMM.SOLDBY_CONTACT_PHONENUMBER
,
WDA.DELIVERY_DETAIL_ID
FROM WSH_TRIP_STOPS WTS
,
WSH_TRIPS WTP
,
WSH_TRIP_STOPS WTS2
,
WSH_DELIVERY_LEGS WDL
,
WSH_NEW_DELIVERIES WND
,
WSH_DELIVERY_ASSIGNMENTS WDA
,
WSH_DELIVERY_DETAILS WDD
,
WSH_LOCATIONS WSHL
,
WSH_LOCATIONS WSHL2
,
HR_LOCATIONS_ALL HRL
,
PER_ALL_PEOPLE_F PAPF
,
HR_ORGANIZATION_UNITS HOU
,
MTL_PARAMETERS MTP
,
WSH_DOCUMENT_INSTANCES WDOC
,
WSH_DOCUMENT_INSTANCES WDI
,
HZ_PARTY_SITES HPS
,
HZ_CUST_ACCT_SITES_ALL HCAS
,
HZ_CUST_SITE_USES_ALL HCSU
,
HZ_CUST_ACCOUNTS CUST_ACCT1
,
HZ_PARTIES PARTY1
,
WSH_OPSM_ASN_ORDERS_SPECIFIC_V SPEC
,
WSH_OPSM_ASN_ORDERS_COMM_V COMM
,
(SELECT REL_PARTY.PERSON_FIRST_NAME
||' '
||REL_PARTY.PERSON_LAST_NAME CONTACT_NAME
,
CONTACT.PHONE_COUNTRY_CODE CONTACT_COUNTRY_CODE
,
CONTACT.PHONE_AREA_CODE CONTACT_AREA_CODE
,
CONTACT.PHONE_NUMBER CONTACT_PHONE_NUMBER
,
CONTACT.PHONE_EXTENSION CONTACT_PHONE_EXTN
,
ACCT_ROLE.CUST_ACCOUNT_ROLE_ID CONTACT_ID
,
CONTACT1.EMAIL_ADDRESS CONTACT_EMAIL
FROM 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
WHERE REL.PARTY_ID(+) = ACCT_ROLE.PARTY_ID+0
AND ACCT_ROLE.ROLE_TYPE = 'CONTACT'
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'
) DESTINATION_CONTACT_DETAILS
WHERE WND.INITIAL_PICKUP_LOCATION_ID+0 = WSHL.WSH_LOCATION_ID
AND WSHL.SOURCE_LOCATION_ID +0 = HRL.LOCATION_ID
AND HRL.DESIGNATED_RECEIVER_ID +0 = PAPF.PERSON_ID (+)
AND SYSDATE BETWEEN PAPF.EFFECTIVE_START_DATE (+)
AND PAPF.EFFECTIVE_END_DATE (+)
AND WSHL.LOCATION_SOURCE_CODE = 'HR'
AND HOU.ORGANIZATION_ID = WND.ORGANIZATION_ID+0
AND HOU.ORGANIZATION_ID +0 = MTP.ORGANIZATION_ID
AND WND.DELIVERY_ID +0 = WDL.DELIVERY_ID
AND WTS2.STOP_ID = WDL.DROP_OFF_STOP_ID
AND WND.ULTIMATE_DROPOFF_LOCATION_ID +0 = WTS2.STOP_LOCATION_ID
AND WND.ULTIMATE_DROPOFF_LOCATION_ID +0 = WSHL2.WSH_LOCATION_ID
AND WDL.PICK_UP_STOP_ID +0 = WTS.STOP_ID
AND WTP.TRIP_ID = WTS2.TRIP_ID+0
AND WTS.TRIP_ID = WTS2.TRIP_ID+0
AND WTP.TRIP_ID = WTS.TRIP_ID +0
AND WTS2.STOP_LOCATION_ID +0 = HPS.LOCATION_ID
AND WSHL2.LOCATION_SOURCE_CODE = 'HZ'
AND WSHL2.SOURCE_LOCATION_ID = HPS.LOCATION_ID +0
AND HPS.PARTY_SITE_ID +0 = HCAS.PARTY_SITE_ID
AND CUST_ACCT1.CUST_ACCOUNT_ID = HCAS.CUST_ACCOUNT_ID+0
AND HCAS.CUST_ACCT_SITE_ID +0 = HCSU.CUST_ACCT_SITE_ID
AND ( HCAS.ORG_ID IS NULL
OR HCAS.ORG_ID IN
(SELECT FIRST_VALUE(WDD.ORG_ID) OVER(ORDER BY COUNT(WDD.ORG_ID) DESC ) AS ORG_ID
FROM WSH_DELIVERY_ASSIGNMENTS WDA
,
WSH_DELIVERY_DETAILS WDD
WHERE WDD.DELIVERY_DETAIL_ID = WDA.DELIVERY_DETAIL_ID
AND WDA.DELIVERY_ID = WND.DELIVERY_ID
AND WDD.CONTAINER_FLAG = 'N'
GROUP BY ORG_ID
))
AND NVL(HCAS.ORG_ID
, -999) = NVL(HCSU.ORG_ID
, -999)
AND HCSU.SITE_USE_CODE = 'SHIP_TO'
AND HCSU.STATUS = 'A'
AND CUST_ACCT1.STATUS = 'A'
AND WND.DELIVERY_ID +0 = WDA.DELIVERY_ID
AND WDA.DELIVERY_DETAIL_ID+0 = WDD.DELIVERY_DETAIL_ID
AND WND.DELIVERY_ID +0 = WDOC.ENTITY_ID (+)
AND WDOC.ENTITY_NAME(+) = 'WSH_NEW_DELIVERIES'
AND WDOC.DOCUMENT_TYPE(+) = 'PACK_TYPE'
AND WDL.DELIVERY_LEG_ID+0 = WDI.ENTITY_ID (+)
AND WDI.ENTITY_NAME(+) = 'WSH_DELIVERY_LEGS'
AND HPS.PARTY_ID +0 = CUST_ACCT1.PARTY_ID
AND CUST_ACCT1.PARTY_ID +0 = PARTY1.PARTY_ID
AND WDD.SHIP_TO_CONTACT_ID +0 = DESTINATION_CONTACT_DETAILS.CONTACT_ID(+)
AND NVL(WDD.SHIP_TO_CONTACT_ID +0
, -999) = NVL(SPEC.SHIP_TO_CONTACT_ID
, -999)
AND WDD.SOURCE_HEADER_ID +0 = SPEC.SOURCE_HEADER_ID(+)
AND SPEC.SOURCE_HEADER_ID +0 = COMM.ORDER_HEADER_ID
AND NVL(WND.SHIPMENT_DIRECTION
, 'O') IN ('O'
, 'IO')
APPS.WSH_OPSM_ASN_DELIVERIES_V