VIEW: APPS.PO_LINE_LOCATIONS_V

Source

View Text - Preformatted

SELECT PLL.ROWID , PLL.PO_HEADER_ID , PLL.PO_LINE_ID , PLL.LINE_LOCATION_ID , PLL.SHIPMENT_NUM , PLL.SHIPMENT_TYPE , PLL.SHIP_TO_ORGANIZATION_ID , OOD.ORGANIZATION_CODE , PLL.SHIP_TO_LOCATION_ID , HRL.LOCATION_CODE , PLL.QUANTITY , PLL.QUANTITY_ACCEPTED , PLL.QUANTITY_BILLED , PLL.QUANTITY_CANCELLED , PLL.QUANTITY_RECEIVED , PLL.QUANTITY_REJECTED ,DECODE(PLL.SHIPMENT_TYPE,'RFQ',PLL.UNIT_MEAS_LOOKUP_CODE,'QUOTATION',PLL.UNIT_MEAS_LOOKUP_CODE, POL.UNIT_MEAS_LOOKUP_CODE) , PLL.PRICE_OVERRIDE , POL.UNIT_PRICE , POL.NOT_TO_EXCEED_PRICE , POL.ALLOW_PRICE_OVERRIDE_FLAG , POL.PRICE_BREAK_LOOKUP_CODE , PLL.AMOUNT , PO_HEADERS_SV3.GET_CURRENCY_CODE(PLL.PO_HEADER_ID) , PLL.LAST_ACCEPT_DATE , PLL.NEED_BY_DATE , PLL.PROMISED_DATE , PLL.FIRM_STATUS_LOOKUP_CODE , PLL.PRICE_DISCOUNT , PLL.START_DATE , PLL.END_DATE , PLL.LEAD_TIME , PLL.LEAD_TIME_UNIT , PLL.TERMS_ID , APT.NAME , PLL.FREIGHT_TERMS_LOOKUP_CODE , PLL.FOB_LOOKUP_CODE , PLL.SHIP_VIA_LOOKUP_CODE , NULL , PLL.TAXABLE_FLAG , PLL.INSPECTION_REQUIRED_FLAG , PLL.RECEIPT_REQUIRED_FLAG , PLL.RECEIVE_CLOSE_TOLERANCE , PLL.INVOICE_CLOSE_TOLERANCE , PLL.QTY_RCV_EXCEPTION_CODE , PLL.DAYS_EARLY_RECEIPT_ALLOWED , PLL.DAYS_LATE_RECEIPT_ALLOWED , PLL.QTY_RCV_TOLERANCE , PLL.RECEIVING_ROUTING_ID , PLL.ENFORCE_SHIP_TO_LOCATION_CODE , PLL.RECEIPT_DAYS_EXCEPTION_CODE , PLL.ACCRUE_ON_RECEIPT_FLAG , PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG , PLL.FROM_HEADER_ID , PLL.FROM_LINE_ID , PLL.FROM_LINE_LOCATION_ID , NVL(PLL.ENCUMBERED_FLAG, 'N') , PLL.ENCUMBERED_DATE , PLL.APPROVED_FLAG , PLL.APPROVED_DATE , PLL.CLOSED_CODE , DECODE(NVL(PLL.CLOSED_CODE,'OPEN'),'OPEN', NULL, POLC1.DISPLAYED_FIELD)||DECODE(PLL.CANCEL_FLAG,'Y',','||''|| POLC2.DISPLAYED_FIELD, NULL) , PLL.CANCEL_FLAG , PLL.CANCEL_DATE , PLL.CANCEL_REASON , PLL.CANCELLED_BY , PLL.CLOSED_FLAG , PLL.CLOSED_BY , PLL.CLOSED_DATE , PLL.CLOSED_REASON , PLL.USSGL_TRANSACTION_CODE , PLL.GOVERNMENT_CONTEXT , POL.LINE_TYPE_ID , PLT.LINE_TYPE , PLT.OUTSIDE_OPERATION_FLAG , PLT.RECEIVING_FLAG , POL.ITEM_ID , POL.ITEM_DESCRIPTION , POL.ITEM_REVISION , DECODE(MSI.MRP_PLANNING_CODE, 3, 'Y', 4, 'Y', 7, 'Y', 8, 'Y', 9, 'Y', DECODE(MSI.INVENTORY_PLANNING_CODE, 1, 'Y', 2, 'Y', 'N')) , MSI.STOCK_ENABLED_FLAG , MSI.OUTSIDE_OPERATION_UOM_TYPE , MCA.CONCATENATED_SEGMENTS , POL.CATEGORY_ID , POL.VENDOR_PRODUCT_NUM , PLL.LAST_UPDATE_DATE , PLL.LAST_UPDATED_BY , PLL.LAST_UPDATE_LOGIN , PLL.CREATED_BY , PLL.CREATION_DATE , PLL.PROGRAM_ID , PLL.REQUEST_ID , PLL.PROGRAM_APPLICATION_ID , PLL.PROGRAM_UPDATE_DATE , 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 , PLL.ATTRIBUTE_CATEGORY ,PLL.GLOBAL_ATTRIBUTE_CATEGORY ,PLL.GLOBAL_ATTRIBUTE1 ,PLL.GLOBAL_ATTRIBUTE2 ,PLL.GLOBAL_ATTRIBUTE3 ,PLL.GLOBAL_ATTRIBUTE4 ,PLL.GLOBAL_ATTRIBUTE5 ,PLL.GLOBAL_ATTRIBUTE6 ,PLL.GLOBAL_ATTRIBUTE7 ,PLL.GLOBAL_ATTRIBUTE8 ,PLL.GLOBAL_ATTRIBUTE9 ,PLL.GLOBAL_ATTRIBUTE10 ,PLL.GLOBAL_ATTRIBUTE11 ,PLL.GLOBAL_ATTRIBUTE12 ,PLL.GLOBAL_ATTRIBUTE13 ,PLL.GLOBAL_ATTRIBUTE14 ,PLL.GLOBAL_ATTRIBUTE15 ,PLL.GLOBAL_ATTRIBUTE16 ,PLL.GLOBAL_ATTRIBUTE17 ,PLL.GLOBAL_ATTRIBUTE18 ,PLL.GLOBAL_ATTRIBUTE19 ,PLL.GLOBAL_ATTRIBUTE20 ,PLL.COUNTRY_OF_ORIGIN_CODE ,PLL.TAX_USER_OVERRIDE_FLAG ,PLL.MATCH_OPTION ,PLL.TAX_CODE_ID ,PLL.CALCULATE_TAX_FLAG ,PLL.NOTE_TO_RECEIVER ,PLL.SECONDARY_UNIT_OF_MEASURE ,PLL.SECONDARY_QUANTITY ,PLL.PREFERRED_GRADE ,PLL.SECONDARY_QUANTITY_RECEIVED ,PLL.SECONDARY_QUANTITY_ACCEPTED ,PLL.SECONDARY_QUANTITY_REJECTED ,PLL.SECONDARY_QUANTITY_CANCELLED ,PLL.VMI_FLAG ,PLL.CONSIGNED_FLAG , PLL.AMOUNT_RECEIVED , PLL.AMOUNT_BILLED , PLL.AMOUNT_CANCELLED , PLL. AMOUNT_ACCEPTED , PLL.AMOUNT_REJECTED , PLL.DROP_SHIP_FLAG , PLL.SALES_ORDER_UPDATE_DATE , PLL.TRANSACTION_FLOW_HEADER_ID , PLL.SECONDARY_QUANTITY_SHIPPED , PLL.ORG_ID ,PLL.OUTSOURCED_ASSEMBLY FROM PO_LINE_TYPES PLT, PO_LOOKUP_CODES POLC1, PO_LOOKUP_CODES POLC2, HR_LOCATIONS_ALL_TL HRL, ORG_ORGANIZATION_DEFINITIONS OOD, AP_TERMS APT, MTL_SYSTEM_ITEMS MSI, FINANCIALS_SYSTEM_PARAMS_ALL FSP, MTL_CATEGORIES_KFV MCA, PO_LINES_ALL POL, PO_LINE_LOCATIONS PLL WHERE PLL.PO_LINE_ID = POL.PO_LINE_ID AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+) AND NVL(MSI.ORGANIZATION_ID,FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID AND POL.CATEGORY_ID = MCA.CATEGORY_ID AND PLL.SHIPMENT_TYPE IN ('STANDARD', 'PLANNED', 'PRICE BREAK','RFQ', 'QUOTATION') AND HRL.LOCATION_ID (+) = PLL.SHIP_TO_LOCATION_ID AND HRL.LANGUAGE(+) = USERENV('LANG') AND OOD.ORGANIZATION_ID(+) = PLL.SHIP_TO_ORGANIZATION_ID AND APT.TERM_ID (+) = PLL.TERMS_ID AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND POLC1.LOOKUP_TYPE(+) = 'DOCUMENT STATE' AND POLC1.LOOKUP_CODE(+) = NVL(PLL.CLOSED_CODE, 'OPEN') AND POLC2.LOOKUP_TYPE (+) = 'DOCUMENT STATE' AND POLC2.LOOKUP_CODE (+) = 'CANCELLED' AND FSP.ORG_ID = PLL.ORG_ID

View Text - HTML Formatted

SELECT PLL.ROWID
, PLL.PO_HEADER_ID
, PLL.PO_LINE_ID
, PLL.LINE_LOCATION_ID
, PLL.SHIPMENT_NUM
, PLL.SHIPMENT_TYPE
, PLL.SHIP_TO_ORGANIZATION_ID
, OOD.ORGANIZATION_CODE
, PLL.SHIP_TO_LOCATION_ID
, HRL.LOCATION_CODE
, PLL.QUANTITY
, PLL.QUANTITY_ACCEPTED
, PLL.QUANTITY_BILLED
, PLL.QUANTITY_CANCELLED
, PLL.QUANTITY_RECEIVED
, PLL.QUANTITY_REJECTED
, DECODE(PLL.SHIPMENT_TYPE
, 'RFQ'
, PLL.UNIT_MEAS_LOOKUP_CODE
, 'QUOTATION'
, PLL.UNIT_MEAS_LOOKUP_CODE
, POL.UNIT_MEAS_LOOKUP_CODE)
, PLL.PRICE_OVERRIDE
, POL.UNIT_PRICE
, POL.NOT_TO_EXCEED_PRICE
, POL.ALLOW_PRICE_OVERRIDE_FLAG
, POL.PRICE_BREAK_LOOKUP_CODE
, PLL.AMOUNT
, PO_HEADERS_SV3.GET_CURRENCY_CODE(PLL.PO_HEADER_ID)
, PLL.LAST_ACCEPT_DATE
, PLL.NEED_BY_DATE
, PLL.PROMISED_DATE
, PLL.FIRM_STATUS_LOOKUP_CODE
, PLL.PRICE_DISCOUNT
, PLL.START_DATE
, PLL.END_DATE
, PLL.LEAD_TIME
, PLL.LEAD_TIME_UNIT
, PLL.TERMS_ID
, APT.NAME
, PLL.FREIGHT_TERMS_LOOKUP_CODE
, PLL.FOB_LOOKUP_CODE
, PLL.SHIP_VIA_LOOKUP_CODE
, NULL
, PLL.TAXABLE_FLAG
, PLL.INSPECTION_REQUIRED_FLAG
, PLL.RECEIPT_REQUIRED_FLAG
, PLL.RECEIVE_CLOSE_TOLERANCE
, PLL.INVOICE_CLOSE_TOLERANCE
, PLL.QTY_RCV_EXCEPTION_CODE
, PLL.DAYS_EARLY_RECEIPT_ALLOWED
, PLL.DAYS_LATE_RECEIPT_ALLOWED
, PLL.QTY_RCV_TOLERANCE
, PLL.RECEIVING_ROUTING_ID
, PLL.ENFORCE_SHIP_TO_LOCATION_CODE
, PLL.RECEIPT_DAYS_EXCEPTION_CODE
, PLL.ACCRUE_ON_RECEIPT_FLAG
, PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, PLL.FROM_HEADER_ID
, PLL.FROM_LINE_ID
, PLL.FROM_LINE_LOCATION_ID
, NVL(PLL.ENCUMBERED_FLAG
, 'N')
, PLL.ENCUMBERED_DATE
, PLL.APPROVED_FLAG
, PLL.APPROVED_DATE
, PLL.CLOSED_CODE
, DECODE(NVL(PLL.CLOSED_CODE
, 'OPEN')
, 'OPEN'
, NULL
, POLC1.DISPLAYED_FIELD)||DECODE(PLL.CANCEL_FLAG
, 'Y'
, '
, '||''|| POLC2.DISPLAYED_FIELD
, NULL)
, PLL.CANCEL_FLAG
, PLL.CANCEL_DATE
, PLL.CANCEL_REASON
, PLL.CANCELLED_BY
, PLL.CLOSED_FLAG
, PLL.CLOSED_BY
, PLL.CLOSED_DATE
, PLL.CLOSED_REASON
, PLL.USSGL_TRANSACTION_CODE
, PLL.GOVERNMENT_CONTEXT
, POL.LINE_TYPE_ID
, PLT.LINE_TYPE
, PLT.OUTSIDE_OPERATION_FLAG
, PLT.RECEIVING_FLAG
, POL.ITEM_ID
, POL.ITEM_DESCRIPTION
, POL.ITEM_REVISION
, DECODE(MSI.MRP_PLANNING_CODE
, 3
, 'Y'
, 4
, 'Y'
, 7
, 'Y'
, 8
, 'Y'
, 9
, 'Y'
, DECODE(MSI.INVENTORY_PLANNING_CODE
, 1
, 'Y'
, 2
, 'Y'
, 'N'))
, MSI.STOCK_ENABLED_FLAG
, MSI.OUTSIDE_OPERATION_UOM_TYPE
, MCA.CONCATENATED_SEGMENTS
, POL.CATEGORY_ID
, POL.VENDOR_PRODUCT_NUM
, PLL.LAST_UPDATE_DATE
, PLL.LAST_UPDATED_BY
, PLL.LAST_UPDATE_LOGIN
, PLL.CREATED_BY
, PLL.CREATION_DATE
, PLL.PROGRAM_ID
, PLL.REQUEST_ID
, PLL.PROGRAM_APPLICATION_ID
, PLL.PROGRAM_UPDATE_DATE
, 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
, PLL.ATTRIBUTE_CATEGORY
, PLL.GLOBAL_ATTRIBUTE_CATEGORY
, PLL.GLOBAL_ATTRIBUTE1
, PLL.GLOBAL_ATTRIBUTE2
, PLL.GLOBAL_ATTRIBUTE3
, PLL.GLOBAL_ATTRIBUTE4
, PLL.GLOBAL_ATTRIBUTE5
, PLL.GLOBAL_ATTRIBUTE6
, PLL.GLOBAL_ATTRIBUTE7
, PLL.GLOBAL_ATTRIBUTE8
, PLL.GLOBAL_ATTRIBUTE9
, PLL.GLOBAL_ATTRIBUTE10
, PLL.GLOBAL_ATTRIBUTE11
, PLL.GLOBAL_ATTRIBUTE12
, PLL.GLOBAL_ATTRIBUTE13
, PLL.GLOBAL_ATTRIBUTE14
, PLL.GLOBAL_ATTRIBUTE15
, PLL.GLOBAL_ATTRIBUTE16
, PLL.GLOBAL_ATTRIBUTE17
, PLL.GLOBAL_ATTRIBUTE18
, PLL.GLOBAL_ATTRIBUTE19
, PLL.GLOBAL_ATTRIBUTE20
, PLL.COUNTRY_OF_ORIGIN_CODE
, PLL.TAX_USER_OVERRIDE_FLAG
, PLL.MATCH_OPTION
, PLL.TAX_CODE_ID
, PLL.CALCULATE_TAX_FLAG
, PLL.NOTE_TO_RECEIVER
, PLL.SECONDARY_UNIT_OF_MEASURE
, PLL.SECONDARY_QUANTITY
, PLL.PREFERRED_GRADE
, PLL.SECONDARY_QUANTITY_RECEIVED
, PLL.SECONDARY_QUANTITY_ACCEPTED
, PLL.SECONDARY_QUANTITY_REJECTED
, PLL.SECONDARY_QUANTITY_CANCELLED
, PLL.VMI_FLAG
, PLL.CONSIGNED_FLAG
, PLL.AMOUNT_RECEIVED
, PLL.AMOUNT_BILLED
, PLL.AMOUNT_CANCELLED
, PLL. AMOUNT_ACCEPTED
, PLL.AMOUNT_REJECTED
, PLL.DROP_SHIP_FLAG
, PLL.SALES_ORDER_UPDATE_DATE
, PLL.TRANSACTION_FLOW_HEADER_ID
, PLL.SECONDARY_QUANTITY_SHIPPED
, PLL.ORG_ID
, PLL.OUTSOURCED_ASSEMBLY
FROM PO_LINE_TYPES PLT
, PO_LOOKUP_CODES POLC1
, PO_LOOKUP_CODES POLC2
, HR_LOCATIONS_ALL_TL HRL
, ORG_ORGANIZATION_DEFINITIONS OOD
, AP_TERMS APT
, MTL_SYSTEM_ITEMS MSI
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, MTL_CATEGORIES_KFV MCA
, PO_LINES_ALL POL
, PO_LINE_LOCATIONS PLL
WHERE PLL.PO_LINE_ID = POL.PO_LINE_ID
AND POL.ITEM_ID = MSI.INVENTORY_ITEM_ID (+)
AND NVL(MSI.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID
AND POL.CATEGORY_ID = MCA.CATEGORY_ID
AND PLL.SHIPMENT_TYPE IN ('STANDARD'
, 'PLANNED'
, 'PRICE BREAK'
, 'RFQ'
, 'QUOTATION')
AND HRL.LOCATION_ID (+) = PLL.SHIP_TO_LOCATION_ID
AND HRL.LANGUAGE(+) = USERENV('LANG')
AND OOD.ORGANIZATION_ID(+) = PLL.SHIP_TO_ORGANIZATION_ID
AND APT.TERM_ID (+) = PLL.TERMS_ID
AND POL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND POLC1.LOOKUP_TYPE(+) = 'DOCUMENT STATE'
AND POLC1.LOOKUP_CODE(+) = NVL(PLL.CLOSED_CODE
, 'OPEN')
AND POLC2.LOOKUP_TYPE (+) = 'DOCUMENT STATE'
AND POLC2.LOOKUP_CODE (+) = 'CANCELLED'
AND FSP.ORG_ID = PLL.ORG_ID