View: ECE_POO_SHIPMENTS_V

Product:	EC - e-Commerce Gateway
Description:	/*#* This view extracts shipment information* for the outbound Purchase Order (850/ORDERS) transaction.* @rep:scope private* @rep:product PO* @rep:lifecycle active* @rep:displayname Purchase Order Shipment View* @rep:category BUSINESS_ENTIT
Implementation/DBA Data:	APPS.ECE_POO_SHIPMENTS_V

View Text

SELECT POLL.SHIPMENT_NUM SHIPMENT_NUMBER
, POLL.QUANTITY QUANTITY_ORIGINAL
, NULL UOM_CODE
, POLL.NEED_BY_DATE SHIPMENT_NEED_BY_DATE
, POLL.PROMISED_DATE SHIPMENT_PROMISED_DATE
, POLL.LAST_ACCEPT_DATE SHIPMENT_LAST_ACCEPTABLE_DATE
, POLL.QUANTITY_CANCELLED QUANTITY_CANCELLED
, POLL.QUANTITY_RECEIVED QUANTITY_RECEIVED
, POLL.PRICE_OVERRIDE PRICE_OVERRIDE
, POLL.CANCEL_FLAG CANCELLED_FLAG
, POLL.CANCEL_DATE CANCELLED_DATE
, POLL.SHIP_VIA_LOOKUP_CODE SHIP_VIA
, POLL.FOB_LOOKUP_CODE FOB_CODE
, POLL.FREIGHT_TERMS_LOOKUP_CODE FREIGHT_TERMS
, POLL.TAXABLE_FLAG TAXABLE_FLAG
, POLL.ATTRIBUTE_CATEGORY SHIPMENT_ATTRIBUTE_CATEGORY
, POLL.ATTRIBUTE1 SHIPMENT_ATTRIBUTE1
, POLL.ATTRIBUTE2 SHIPMENT_ATTRIBUTE2
, POLL.ATTRIBUTE3 SHIPMENT_ATTRIBUTE3
, POLL.ATTRIBUTE4 SHIPMENT_ATTRIBUTE4
, POLL.ATTRIBUTE5 SHIPMENT_ATTRIBUTE5
, POLL.ATTRIBUTE6 SHIPMENT_ATTRIBUTE6
, POLL.ATTRIBUTE7 SHIPMENT_ATTRIBUTE7
, POLL.ATTRIBUTE8 SHIPMENT_ATTRIBUTE8
, POLL.ATTRIBUTE9 SHIPMENT_ATTRIBUTE9
, POLL.ATTRIBUTE10 SHIPMENT_ATTRIBUTE10
, POLL.ATTRIBUTE11 SHIPMENT_ATTRIBUTE11
, POLL.ATTRIBUTE12 SHIPMENT_ATTRIBUTE12
, POLL.ATTRIBUTE13 SHIPMENT_ATTRIBUTE13
, POLL.ATTRIBUTE14 SHIPMENT_ATTRIBUTE14
, POLL.ATTRIBUTE15 SHIPMENT_ATTRIBUTE15
, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, HRL.LOCATION_CODE SHIP_TO_LOCATION_CODE
, PPF.LAST_NAME SHIP_TO_CONTACT_LAST_NAME
, PPF.FIRST_NAME SHIP_TO_CONTACT_FIRST_NAME
, HRL.ADDRESS_LINE_1 SHIP_TO_ADDRESS_LINE_1
, HRL.ADDRESS_LINE_2 SHIP_TO_ADDRESS_LINE_2
, HRL.ADDRESS_LINE_3 SHIP_TO_ADDRESS_LINE_3
, HRL.TOWN_OR_CITY SHIP_TO_CITY
, HRL.POSTAL_CODE SHIP_TO_POSTAL_CODE
, HRL.COUNTRY SHIP_TO_COUNTRY
, HRL.REGION_1 SHIP_TO_REGION_1
, HRL.REGION_2 SHIP_TO_REGION_2
, HRL.REGION_3 SHIP_TO_REGION_3
, HRL.TELEPHONE_NUMBER_1 SHIP_TO_PHONE1
, HRL.TELEPHONE_NUMBER_2 SHIP_TO_PHONE2
, HRL.TELEPHONE_NUMBER_3 SHIP_TO_PHONE3
, POLL.PO_HEADER_ID PO_HEADER_ID
, POLL.PO_LINE_ID PO_LINE_ID
, NVL(POLL.PO_RELEASE_ID
, 0) POR_RELEASE_ID
, 0 POR_RELEASE_NUM
, POLL.LINE_LOCATION_ID LINE_LOCATION_ID
, HAOU.NAME SHIP_TO_ORGANIZATION_NAME
, HRL.ECE_TP_LOCATION_CODE SHIP_TO_EDI_LOCATION_CODE
, POLL.START_DATE START_DATE
, POLL.END_DATE END_DATE
, POLL.CONSIGNED_FLAG CONSIGNED_FLAG
, HRL.DESCRIPTION SHIP_TO_SITE_NAME
FROM PO_LINE_LOCATIONS POLL
, PER_ALL_PEOPLE_F PPF
, HR_LOCATIONS_ALL HRL
, HR_LOCATIONS_ALL_TL HRLT
, HR_ALL_ORGANIZATION_UNITS HAOU
, PER_ALL_ASSIGNMENTS_F PAAF
WHERE NOT EXISTS (SELECT 1
FROM OE_DROP_SHIP_SOURCES B
WHERE POLL.LINE_LOCATION_ID = B.LINE_LOCATION_ID)
AND POLL.SHIP_TO_LOCATION_ID = HRL.LOCATION_ID (+)
AND PPF.PERSON_ID (+) = HRL.DESIGNATED_RECEIVER_ID
AND NVL(POLL.CANCEL_FLAG
, 'N') <> 'Y'
AND NVL(HRL.BUSINESS_GROUP_ID
, NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, -99)) = NVL(HR_GENERAL.GET_BUSINESS_GROUP_ID
, -99)
AND PPF.PERSON_ID = PAAF.PERSON_ID (+)
AND TRUNC(SYSDATE) >= PAAF.EFFECTIVE_START_DATE (+)
AND TRUNC(SYSDATE) <= PAAF.EFFECTIVE_END_DATE (+)
AND PAAF.ORGANIZATION_ID = HAOU.ORGANIZATION_ID (+)
AND HRL.LOCATION_ID = HRLT.LOCATION_ID (+)
AND HRLT.LANGUAGE (+) = USERENV('LANG')
AND TRUNC(SYSDATE) >= PPF.EFFECTIVE_START_DATE (+)
AND TRUNC(SYSDATE) <= PPF.EFFECTIVE_END_DATE (+) UNION ALL SELECT DISTINCT POLL.SHIPMENT_NUM SHIPMENT_NUMBER
, POLL.QUANTITY QUANTITY_ORIGINAL
, NULL UOM_CODE
, POLL.NEED_BY_DATE SHIPMENT_NEED_BY_DATE
, POLL.PROMISED_DATE SHIPMENT_PROMISED_DATE
, POLL.LAST_ACCEPT_DATE SHIPMENT_LAST_ACCEPTABLE_DATE
, POLL.QUANTITY_CANCELLED QUANTITY_CANCELLED
, POLL.QUANTITY_RECEIVED QUANTITY_RECEIVED
, POLL.PRICE_OVERRIDE PRICE_OVERRIDE
, POLL.CANCEL_FLAG CANCELLED_FLAG
, POLL.CANCEL_DATE CANCELLED_DATE
, POLL.SHIP_VIA_LOOKUP_CODE SHIP_VIA
, POLL.FOB_LOOKUP_CODE FOB_CODE
, POLL.FREIGHT_TERMS_LOOKUP_CODE FREIGHT_TERMS
, POLL.TAXABLE_FLAG TAXABLE_FLAG
, POLL.ATTRIBUTE_CATEGORY SHIPMENT_ATTRIBUTE_CATEGORY
, POLL.ATTRIBUTE1 SHIPMENT_ATTRIBUTE1
, POLL.ATTRIBUTE2 SHIPMENT_ATTRIBUTE2
, POLL.ATTRIBUTE3 SHIPMENT_ATTRIBUTE3
, POLL.ATTRIBUTE4 SHIPMENT_ATTRIBUTE4
, POLL.ATTRIBUTE5 SHIPMENT_ATTRIBUTE5
, POLL.ATTRIBUTE6 SHIPMENT_ATTRIBUTE6
, POLL.ATTRIBUTE7 SHIPMENT_ATTRIBUTE7
, POLL.ATTRIBUTE8 SHIPMENT_ATTRIBUTE8
, POLL.ATTRIBUTE9 SHIPMENT_ATTRIBUTE9
, POLL.ATTRIBUTE10 SHIPMENT_ATTRIBUTE10
, POLL.ATTRIBUTE11 SHIPMENT_ATTRIBUTE11
, POLL.ATTRIBUTE12 SHIPMENT_ATTRIBUTE12
, POLL.ATTRIBUTE13 SHIPMENT_ATTRIBUTE13
, POLL.ATTRIBUTE14 SHIPMENT_ATTRIBUTE14
, POLL.ATTRIBUTE15 SHIPMENT_ATTRIBUTE15
, POLL.SHIP_TO_LOCATION_ID SHIP_TO_LOCATION_ID
, CSU.LOCATION SHIP_TO_LOCATION_CODE
, PT.PERSON_LAST_NAME SHIP_TO_CONTACT_LAST_NAME
, PT.PERSON_FIRST_NAME SHIP_TO_CONTACT_FIRST_NAME
, LC.ADDRESS1 SHIP_TO_ADDRESS_LINE_1
, LC.ADDRESS2 SHIP_TO_ADDRESS_LINE_2
, LC.ADDRESS3 SHIP_TO_ADDRESS_LINE_3
, LC.CITY SHIP_TO_CITY
, LC.POSTAL_CODE SHIP_TO_POSTAL_CODE
, LC.COUNTRY SHIP_TO_COUNTRY
, LC.PROVINCE SHIP_TO_REGION_1
, LC.STATE SHIP_TO_REGION_2
, NULL SHIP_TO_REGION_3
, OC.CONTACT_NUMBER SHIP_TO_PHONE1
, NULL SHIP_TO_PHONE2
, NULL SHIP_TO_PHONE3
, POLL.PO_HEADER_ID PO_HEADER_ID
, POLL.PO_LINE_ID PO_LINE_ID
, NVL(POLL.PO_RELEASE_ID
, 0) POR_RELEASE_ID
, 0 POR_RELEASE_NUM
, POLL.LINE_LOCATION_ID LINE_LOCATION_ID
, NULL SHIP_TO_ORGANIZATION_NAME
, CAS.ECE_TP_LOCATION_CODE SHIP_TO_EDI_LOCATION_CODE
, POLL.START_DATE START_DATE
, POLL.END_DATE END_DATE
, POLL.CONSIGNED_FLAG CONSIGNED_FLAG
, NULL SHIP_TO_SITE_NAME
FROM PO_LINE_LOCATIONS POLL
, OE_DROP_SHIP_SOURCES ODSS
, OE_ORDER_HEADERS_ALL OEH
, HZ_CUST_ACCOUNT_ROLES ACR
, HZ_PARTIES PT
, HZ_RELATIONSHIPS REL
, HZ_ORG_CONTACTS OC
, HZ_CUST_SITE_USES_ALL CSU
, HZ_LOCATIONS LC
, HZ_CUST_ACCT_SITES_ALL CAS
WHERE NVL(POLL.CANCEL_FLAG
, 'N') <> 'Y'
AND POLL.LINE_LOCATION_ID = ODSS.LINE_LOCATION_ID
AND POLL.PO_HEADER_ID = ODSS.PO_HEADER_ID
AND ODSS.HEADER_ID = OEH.HEADER_ID
AND OEH.SHIP_TO_CONTACT_ID = ACR.CUST_ACCOUNT_ROLE_ID (+)
AND ACR.ROLE_TYPE (+) = 'CONTACT'
AND ACR.PARTY_ID = REL.PARTY_ID (+)
AND REL.SUBJECT_ID = PT.PARTY_ID (+)
AND REL.RELATIONSHIP_ID= OC.PARTY_RELATIONSHIP_ID (+)
AND OEH.SHIP_TO_ORG_ID = CSU.SITE_USE_ID (+)
AND CSU.CUST_ACCT_SITE_ID = CAS.CUST_ACCT_SITE_ID (+)
AND POLL.SHIP_TO_LOCATION_ID = LC.LOCATION_ID

Columns