VIEW: APPS.ECE_POO_SHIPMENTS_V

Source

View Text - Preformatted

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

View Text - HTML Formatted

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