VIEW: APPS.POS_PO_LINES_V

Source

View Text - Preformatted

SELECT PLL.DAYS_EARLY_RECEIPT_ALLOWED, PLL.PO_LINE_ID, PLL.CREATION_DATE, DECODE(PLL.SHIPMENT_TYPE, 'PRICE BREAK', TO_NUMBER(NULL), PLL.QUANTITY - PLL.QUANTITY_CANCELLED), PLL.QUANTITY_REJECTED, PLL.SHIP_TO_LOCATION_ID, PLL.NEED_BY_DATE, PLL.LAST_ACCEPT_DATE, DECODE(PLL.SHIPMENT_TYPE, 'PRICE BREAK', POL.UNIT_PRICE, PLL.PRICE_OVERRIDE), NVL(PLL.CLOSED_CODE,'OPEN'), PLL.CLOSED_DATE, PLL.CLOSED_REASON, PLL.SHIPMENT_NUM, PLL.DAYS_LATE_RECEIPT_ALLOWED, PLL.ENFORCE_SHIP_TO_LOCATION_CODE, PLL.INSPECTION_REQUIRED_FLAG, PLL.RECEIPT_REQUIRED_FLAG, DECODE (PLL.RECEIPT_REQUIRED_FLAG, 'N', DECODE (PLL.INSPECTION_REQUIRED_FLAG,'N', '2-Way',NULL), 'Y', DECODE (PLL.INSPECTION_REQUIRED_FLAG,'N', '3-Way', 'Y','4-Way', NULL)), PLL.SHIP_TO_ORGANIZATION_ID, PLL.RECEIPT_DAYS_EXCEPTION_CODE, PLL.CLOSED_BY, PLL.APPROVED_FLAG, PLL.CLOSED_FLAG, DECODE(PLL.CANCEL_FLAG, 'I', NULL, PLL.CANCEL_FLAG), PLL.CANCELLED_BY, PLL.CANCEL_DATE, PLL.CANCEL_REASON, PLL.LINE_LOCATION_ID, PLL.PO_HEADER_ID, PLL.CREATED_BY, PLL.QUANTITY_RECEIVED, PLL.QUANTITY_ACCEPTED, PLL.QUANTITY_BILLED, PLL.QUANTITY_CANCELLED, PLL.PO_RELEASE_ID, NVL(PLL.PROMISED_DATE, PLL.NEED_BY_DATE), PLL.TAXABLE_FLAG, '', PLL.ATTRIBUTE_CATEGORY, PLL.ATTRIBUTE1, PLL.ATTRIBUTE2, PLL.ATTRIBUTE3, PLL.ATTRIBUTE4, PLL.ATTRIBUTE5, PLL.ATTRIBUTE6, PLL.ATTRIBUTE7, PLL.ATTRIBUTE8, PLL.ATTRIBUTE9, PLL.ATTRIBUTE10, PLL.ATTRIBUTE11, PLL.ATTRIBUTE12, PLL.ATTRIBUTE13, PLL.ATTRIBUTE14, PLL.ATTRIBUTE15, RRH.ROUTING_NAME, POLC2.DISPLAYED_FIELD, POLC3.DISPLAYED_FIELD, POLC4.DISPLAYED_FIELD, HRL.LOCATION_CODE, HROU.NAME, PLL2.SHIPMENT_NUM, POS_GET.GET_PERSON_NAME(PLL.CANCELLED_BY), POS_GET.GET_PERSON_NAME(PLL.CLOSED_BY), POH.SEGMENT1 || DECODE(POR.RELEASE_NUM, NULL, '', '-'||POR.RELEASE_NUM), POH.TYPE_LOOKUP_CODE, POR.RELEASE_NUM, POR.RELEASE_TYPE, DECODE(PLL.PO_RELEASE_ID, NULL, DECODE(POH.AGENT_ID, NULL, TO_NUMBER(NULL), POH.AGENT_ID), POR.AGENT_ID), DECODE(PLL.PO_RELEASE_ID, NULL, DECODE(POH.AGENT_ID, NULL, NULL, POS_GET.GET_PERSON_NAME(POH.AGENT_ID)), POS_GET.GET_PERSON_NAME(POR.AGENT_ID)), POH.VENDOR_ID, POV.VENDOR_NAME, POH.VENDOR_SITE_ID, POVS.VENDOR_SITE_CODE, DECODE (POVC.LAST_NAME, NULL, NULL, POVC.LAST_NAME||', '|| POVC.FIRST_NAME), POH.CURRENCY_CODE, 'APPROVED', DECODE(PLL.PO_RELEASE_ID, NULL, DECODE(POH.REVISION_NUM, NULL, TO_NUMBER(NULL), POH.REVISION_NUM), POR.REVISION_NUM), DECODE(PLL.PO_RELEASE_ID, NULL, PDT.TYPE_NAME, PDT2.TYPE_NAME), DECODE(PLL.PO_RELEASE_ID, NULL, DECODE(POH.CREATION_DATE, NULL, TO_DATE(NULL), POH.CREATION_DATE), POR.CREATION_DATE), DECODE(PLL.PO_RELEASE_ID, NULL, DECODE(POH.APPROVED_DATE, NULL, TO_DATE(NULL), POH.APPROVED_DATE), POR.APPROVED_DATE), DECODE(PLL.PO_RELEASE_ID, NULL, DECODE(POH.REVISED_DATE, NULL, NULL, POH.REVISED_DATE), POR.REVISED_DATE), POH.SHIP_VIA_LOOKUP_CODE, POLC8.DISPLAYED_FIELD, POLC9.DISPLAYED_FIELD, POH.RATE_TYPE, POH.RATE_DATE, POH.RATE, POH.CONFIRMING_ORDER_FLAG, POH.ACCEPTANCE_DUE_DATE, POL.LINE_NUM, PLT.LINE_TYPE, POL.ITEM_ID, POL.ITEM_REVISION, POL.ITEM_DESCRIPTION, POL.CATEGORY_ID, POL.CONTRACT_NUM, POL.UNIT_MEAS_LOOKUP_CODE, POL.ALLOW_PRICE_OVERRIDE_FLAG, POL.NOT_TO_EXCEED_PRICE, POL.NOTE_TO_VENDOR, POL.VENDOR_PRODUCT_NUM, NVL(PLT.OUTSIDE_OPERATION_FLAG,'N'), PLL.PRICE_OVERRIDE * (PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED, 0) ), NVL(POL.ATTRIBUTE14, MSI.ATTRIBUTE14), POV.ATTRIBUTE14, MSI.CONCATENATED_SEGMENTS, DECODE(PLL.SHIPMENT_TYPE, 'PRICE BREAK', PLL.QUANTITY, NULL), DECODE(PLL.SHIPMENT_TYPE, 'PRICE BREAK', PLL.PRICE_OVERRIDE, NULL), DECODE(PLL.SHIPMENT_TYPE, 'PRICE BREAK', PLL.PRICE_DISCOUNT, NULL) FROM PO_LOOKUP_CODES POLC2, PO_LOOKUP_CODES POLC3, PO_LOOKUP_CODES POLC4, PO_LOOKUP_CODES POLC8, PO_LOOKUP_CODES POLC9, HR_LOCATIONS_ALL HRL, HR_ORGANIZATION_UNITS HROU, RCV_ROUTING_HEADERS RRH, PO_VENDORS POV, PO_VENDOR_SITES_ALL POVS, PO_VENDOR_CONTACTS POVC, PO_DOCUMENT_TYPES_ALL_VL PDT, PO_DOCUMENT_TYPES_ALL_VL PDT2, PO_LINE_TYPES PLT, PO_LINE_LOCATIONS_ALL PLL2, MTL_SYSTEM_ITEMS_KFV MSI, PO_RELEASES_ALL POR, PO_HEADERS_ALL POH, FINANCIALS_SYSTEM_PARAMS_ALL FSP, PO_LINES_ALL POL, PO_LINE_LOCATIONS_ALL PLL WHERE POL.PO_LINE_ID = PLL.PO_LINE_ID AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID AND DECODE(PLL.PO_RELEASE_ID, NULL, POH.AUTHORIZATION_STATUS, POR.AUTHORIZATION_STATUS) = 'APPROVED' AND POR.PO_RELEASE_ID (+) = PLL.PO_RELEASE_ID AND PLL2.LINE_LOCATION_ID (+) = PLL.SOURCE_SHIPMENT_ID AND RRH.ROUTING_HEADER_ID (+) = PLL.RECEIVING_ROUTING_ID AND POLC2.LOOKUP_TYPE (+) = 'RECEIVING CONTROL LEVEL' AND POLC2.LOOKUP_CODE (+) = PLL.RECEIPT_DAYS_EXCEPTION_CODE AND POLC3.LOOKUP_TYPE (+) = 'RECEIVING CONTROL LEVEL' AND POLC3.LOOKUP_CODE (+) = PLL.QTY_RCV_EXCEPTION_CODE AND POLC4.LOOKUP_TYPE = 'DOCUMENT STATE' AND POLC4.LOOKUP_CODE = NVL(PLL.CLOSED_CODE,'OPEN') AND HRL.LOCATION_ID (+) = PLL.SHIP_TO_LOCATION_ID AND HROU.ORGANIZATION_ID (+) = PLL.SHIP_TO_ORGANIZATION_ID AND POV.VENDOR_ID = POH.VENDOR_ID AND POVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID AND POVC.VENDOR_CONTACT_ID (+) = POH.VENDOR_CONTACT_ID AND PDT.DOCUMENT_TYPE_CODE IN ('PO', 'PA') AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE AND PDT.ORG_ID = POH.ORG_ID AND PDT2.DOCUMENT_TYPE_CODE(+) = 'RELEASE' AND PDT2.DOCUMENT_SUBTYPE(+) = POR.RELEASE_TYPE AND PDT2.ORG_ID (+) = POR.ORG_ID AND POLC8.LOOKUP_TYPE (+) = 'FOB' AND POLC8.LOOKUP_CODE (+) = POH.FOB_LOOKUP_CODE AND POLC9.LOOKUP_TYPE (+) = 'FREIGHT TERMS' AND POLC9.LOOKUP_CODE (+) = POH.FREIGHT_TERMS_LOOKUP_CODE AND PLT.LINE_TYPE_ID (+) = POL.LINE_TYPE_ID AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+) AND FSP.ORG_ID = POH.ORG_ID AND FSP.INVENTORY_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID)

View Text - HTML Formatted

SELECT PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.PO_LINE_ID
, PLL.CREATION_DATE
, DECODE(PLL.SHIPMENT_TYPE
, 'PRICE BREAK'
, TO_NUMBER(NULL)
, PLL.QUANTITY - PLL.QUANTITY_CANCELLED)
, PLL.QUANTITY_REJECTED
, PLL.SHIP_TO_LOCATION_ID
, PLL.NEED_BY_DATE
, PLL.LAST_ACCEPT_DATE
, DECODE(PLL.SHIPMENT_TYPE
, 'PRICE BREAK'
, POL.UNIT_PRICE
, PLL.PRICE_OVERRIDE)
, NVL(PLL.CLOSED_CODE
, 'OPEN')
, PLL.CLOSED_DATE
, PLL.CLOSED_REASON
, PLL.SHIPMENT_NUM
, PLL.DAYS_LATE_RECEIPT_ALLOWED
, PLL.ENFORCE_SHIP_TO_LOCATION_CODE
, PLL.INSPECTION_REQUIRED_FLAG
, PLL.RECEIPT_REQUIRED_FLAG
, DECODE (PLL.RECEIPT_REQUIRED_FLAG
, 'N'
, DECODE (PLL.INSPECTION_REQUIRED_FLAG
, 'N'
, '2-WAY'
, NULL)
, 'Y'
, DECODE (PLL.INSPECTION_REQUIRED_FLAG
, 'N'
, '3-WAY'
, 'Y'
, '4-WAY'
, NULL))
, PLL.SHIP_TO_ORGANIZATION_ID
, PLL.RECEIPT_DAYS_EXCEPTION_CODE
, PLL.CLOSED_BY
, PLL.APPROVED_FLAG
, PLL.CLOSED_FLAG
, DECODE(PLL.CANCEL_FLAG
, 'I'
, NULL
, PLL.CANCEL_FLAG)
, PLL.CANCELLED_BY
, PLL.CANCEL_DATE
, PLL.CANCEL_REASON
, PLL.LINE_LOCATION_ID
, PLL.PO_HEADER_ID
, PLL.CREATED_BY
, PLL.QUANTITY_RECEIVED
, PLL.QUANTITY_ACCEPTED
, PLL.QUANTITY_BILLED
, PLL.QUANTITY_CANCELLED
, PLL.PO_RELEASE_ID
, NVL(PLL.PROMISED_DATE
, PLL.NEED_BY_DATE)
, PLL.TAXABLE_FLAG
, ''
, PLL.ATTRIBUTE_CATEGORY
, PLL.ATTRIBUTE1
, PLL.ATTRIBUTE2
, PLL.ATTRIBUTE3
, PLL.ATTRIBUTE4
, PLL.ATTRIBUTE5
, PLL.ATTRIBUTE6
, PLL.ATTRIBUTE7
, PLL.ATTRIBUTE8
, PLL.ATTRIBUTE9
, PLL.ATTRIBUTE10
, PLL.ATTRIBUTE11
, PLL.ATTRIBUTE12
, PLL.ATTRIBUTE13
, PLL.ATTRIBUTE14
, PLL.ATTRIBUTE15
, RRH.ROUTING_NAME
, POLC2.DISPLAYED_FIELD
, POLC3.DISPLAYED_FIELD
, POLC4.DISPLAYED_FIELD
, HRL.LOCATION_CODE
, HROU.NAME
, PLL2.SHIPMENT_NUM
, POS_GET.GET_PERSON_NAME(PLL.CANCELLED_BY)
, POS_GET.GET_PERSON_NAME(PLL.CLOSED_BY)
, POH.SEGMENT1 || DECODE(POR.RELEASE_NUM
, NULL
, ''
, '-'||POR.RELEASE_NUM)
, POH.TYPE_LOOKUP_CODE
, POR.RELEASE_NUM
, POR.RELEASE_TYPE
, DECODE(PLL.PO_RELEASE_ID
, NULL
, DECODE(POH.AGENT_ID
, NULL
, TO_NUMBER(NULL)
, POH.AGENT_ID)
, POR.AGENT_ID)
, DECODE(PLL.PO_RELEASE_ID
, NULL
, DECODE(POH.AGENT_ID
, NULL
, NULL
, POS_GET.GET_PERSON_NAME(POH.AGENT_ID))
, POS_GET.GET_PERSON_NAME(POR.AGENT_ID))
, POH.VENDOR_ID
, POV.VENDOR_NAME
, POH.VENDOR_SITE_ID
, POVS.VENDOR_SITE_CODE
, DECODE (POVC.LAST_NAME
, NULL
, NULL
, POVC.LAST_NAME||'
, '|| POVC.FIRST_NAME)
, POH.CURRENCY_CODE
, 'APPROVED'
, DECODE(PLL.PO_RELEASE_ID
, NULL
, DECODE(POH.REVISION_NUM
, NULL
, TO_NUMBER(NULL)
, POH.REVISION_NUM)
, POR.REVISION_NUM)
, DECODE(PLL.PO_RELEASE_ID
, NULL
, PDT.TYPE_NAME
, PDT2.TYPE_NAME)
, DECODE(PLL.PO_RELEASE_ID
, NULL
, DECODE(POH.CREATION_DATE
, NULL
, TO_DATE(NULL)
, POH.CREATION_DATE)
, POR.CREATION_DATE)
, DECODE(PLL.PO_RELEASE_ID
, NULL
, DECODE(POH.APPROVED_DATE
, NULL
, TO_DATE(NULL)
, POH.APPROVED_DATE)
, POR.APPROVED_DATE)
, DECODE(PLL.PO_RELEASE_ID
, NULL
, DECODE(POH.REVISED_DATE
, NULL
, NULL
, POH.REVISED_DATE)
, POR.REVISED_DATE)
, POH.SHIP_VIA_LOOKUP_CODE
, POLC8.DISPLAYED_FIELD
, POLC9.DISPLAYED_FIELD
, POH.RATE_TYPE
, POH.RATE_DATE
, POH.RATE
, POH.CONFIRMING_ORDER_FLAG
, POH.ACCEPTANCE_DUE_DATE
, POL.LINE_NUM
, PLT.LINE_TYPE
, POL.ITEM_ID
, POL.ITEM_REVISION
, POL.ITEM_DESCRIPTION
, POL.CATEGORY_ID
, POL.CONTRACT_NUM
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.ALLOW_PRICE_OVERRIDE_FLAG
, POL.NOT_TO_EXCEED_PRICE
, POL.NOTE_TO_VENDOR
, POL.VENDOR_PRODUCT_NUM
, NVL(PLT.OUTSIDE_OPERATION_FLAG
, 'N')
, PLL.PRICE_OVERRIDE * (PLL.QUANTITY - NVL(PLL.QUANTITY_CANCELLED
, 0) )
, NVL(POL.ATTRIBUTE14
, MSI.ATTRIBUTE14)
, POV.ATTRIBUTE14
, MSI.CONCATENATED_SEGMENTS
, DECODE(PLL.SHIPMENT_TYPE
, 'PRICE BREAK'
, PLL.QUANTITY
, NULL)
, DECODE(PLL.SHIPMENT_TYPE
, 'PRICE BREAK'
, PLL.PRICE_OVERRIDE
, NULL)
, DECODE(PLL.SHIPMENT_TYPE
, 'PRICE BREAK'
, PLL.PRICE_DISCOUNT
, NULL)
FROM PO_LOOKUP_CODES POLC2
, PO_LOOKUP_CODES POLC3
, PO_LOOKUP_CODES POLC4
, PO_LOOKUP_CODES POLC8
, PO_LOOKUP_CODES POLC9
, HR_LOCATIONS_ALL HRL
, HR_ORGANIZATION_UNITS HROU
, RCV_ROUTING_HEADERS RRH
, PO_VENDORS POV
, PO_VENDOR_SITES_ALL POVS
, PO_VENDOR_CONTACTS POVC
, PO_DOCUMENT_TYPES_ALL_VL PDT
, PO_DOCUMENT_TYPES_ALL_VL PDT2
, PO_LINE_TYPES PLT
, PO_LINE_LOCATIONS_ALL PLL2
, MTL_SYSTEM_ITEMS_KFV MSI
, PO_RELEASES_ALL POR
, PO_HEADERS_ALL POH
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS_ALL PLL
WHERE POL.PO_LINE_ID = PLL.PO_LINE_ID
AND POH.PO_HEADER_ID = PLL.PO_HEADER_ID
AND DECODE(PLL.PO_RELEASE_ID
, NULL
, POH.AUTHORIZATION_STATUS
, POR.AUTHORIZATION_STATUS) = 'APPROVED'
AND POR.PO_RELEASE_ID (+) = PLL.PO_RELEASE_ID
AND PLL2.LINE_LOCATION_ID (+) = PLL.SOURCE_SHIPMENT_ID
AND RRH.ROUTING_HEADER_ID (+) = PLL.RECEIVING_ROUTING_ID
AND POLC2.LOOKUP_TYPE (+) = 'RECEIVING CONTROL LEVEL'
AND POLC2.LOOKUP_CODE (+) = PLL.RECEIPT_DAYS_EXCEPTION_CODE
AND POLC3.LOOKUP_TYPE (+) = 'RECEIVING CONTROL LEVEL'
AND POLC3.LOOKUP_CODE (+) = PLL.QTY_RCV_EXCEPTION_CODE
AND POLC4.LOOKUP_TYPE = 'DOCUMENT STATE'
AND POLC4.LOOKUP_CODE = NVL(PLL.CLOSED_CODE
, 'OPEN')
AND HRL.LOCATION_ID (+) = PLL.SHIP_TO_LOCATION_ID
AND HROU.ORGANIZATION_ID (+) = PLL.SHIP_TO_ORGANIZATION_ID
AND POV.VENDOR_ID = POH.VENDOR_ID
AND POVS.VENDOR_SITE_ID = POH.VENDOR_SITE_ID
AND POVC.VENDOR_CONTACT_ID (+) = POH.VENDOR_CONTACT_ID
AND PDT.DOCUMENT_TYPE_CODE IN ('PO'
, 'PA')
AND PDT.DOCUMENT_SUBTYPE = POH.TYPE_LOOKUP_CODE
AND PDT.ORG_ID = POH.ORG_ID
AND PDT2.DOCUMENT_TYPE_CODE(+) = 'RELEASE'
AND PDT2.DOCUMENT_SUBTYPE(+) = POR.RELEASE_TYPE
AND PDT2.ORG_ID (+) = POR.ORG_ID
AND POLC8.LOOKUP_TYPE (+) = 'FOB'
AND POLC8.LOOKUP_CODE (+) = POH.FOB_LOOKUP_CODE
AND POLC9.LOOKUP_TYPE (+) = 'FREIGHT TERMS'
AND POLC9.LOOKUP_CODE (+) = POH.FREIGHT_TERMS_LOOKUP_CODE
AND PLT.LINE_TYPE_ID (+) = POL.LINE_TYPE_ID
AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND FSP.ORG_ID = POH.ORG_ID
AND FSP.INVENTORY_ORGANIZATION_ID = NVL(MSI.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID)