VIEW: APPS.PO_LINES_V

Source

View Text - Preformatted

SELECT POL.ROWID , POL.PO_LINE_ID , POL.LAST_UPDATE_DATE , POL.LAST_UPDATED_BY , POL.CREATION_DATE , POL.CREATED_BY , POL.LAST_UPDATE_LOGIN , POL.REQUEST_ID , POL.PROGRAM_APPLICATION_ID , POL.PROGRAM_ID , POL.PROGRAM_UPDATE_DATE , POL.PO_HEADER_ID , POL.LINE_TYPE_ID , POL.LINE_NUM , PLTT.LINE_TYPE , POL.ITEM_ID , SUBSTR(MSI.SEGMENT1,1,40) , POL.ITEM_REVISION , POL.CATEGORY_ID , POL.ITEM_DESCRIPTION , POL.UNIT_MEAS_LOOKUP_CODE , POL.QUANTITY_COMMITTED , POL.COMMITTED_AMOUNT , POL.ALLOW_PRICE_OVERRIDE_FLAG , POL.NOT_TO_EXCEED_PRICE , POL.LIST_PRICE_PER_UNIT , POL.UNIT_PRICE , POL.QUANTITY , POL.VENDOR_PRODUCT_NUM , POL.UN_NUMBER_ID , POUN.UN_NUMBER , POL.HAZARD_CLASS_ID , PHC.HAZARD_CLASS , POL.MIN_ORDER_QUANTITY , POL.MAX_ORDER_QUANTITY , POL.QTY_RCV_TOLERANCE , POL.OVER_TOLERANCE_ERROR_FLAG , POL.MARKET_PRICE , POL.UNORDERED_FLAG , POL.CLOSED_FLAG , POL.USER_HOLD_FLAG , POL.CANCEL_FLAG , POL.CANCELLED_BY , POL.CANCEL_DATE , POL.CANCEL_REASON , POL.FIRM_STATUS_LOOKUP_CODE , POL.FIRM_DATE , POL.CONTRACT_NUM , POL.NOTE_TO_VENDOR , POL.FROM_HEADER_ID , POL.FROM_LINE_ID , POL.TAXABLE_FLAG , NULL , POL.TYPE_1099 , POL.CAPITAL_EXPENSE_FLAG , POL.NEGOTIATED_BY_PREPARER_FLAG , POL.MIN_RELEASE_AMOUNT , POL.PRICE_TYPE_LOOKUP_CODE , POL.PRICE_BREAK_LOOKUP_CODE , DECODE(POL.item_id, NULL, 'Y', MSI.ALLOW_ITEM_DESC_UPDATE_FLAG) , PLTB.ORDER_TYPE_LOOKUP_CODE , NVL(PLTB.OUTSIDE_OPERATION_FLAG,'N') , POL.TRANSACTION_REASON_CODE , POL.CLOSED_BY , POL.CLOSED_DATE , POL.CLOSED_CODE , POL.CLOSED_REASON , POL.GOVERNMENT_CONTEXT , POL.USSGL_TRANSACTION_CODE , POL.REFERENCE_NUM , POL.ATTRIBUTE_CATEGORY , POL.ATTRIBUTE1 , POL.ATTRIBUTE2 , POL.ATTRIBUTE3 , POL.ATTRIBUTE4 , POL.ATTRIBUTE5 , POL.ATTRIBUTE6 , POL.ATTRIBUTE7 , POL.ATTRIBUTE8 , POL.ATTRIBUTE9 , POL.ATTRIBUTE10 , POL.ATTRIBUTE11 , POL.ATTRIBUTE12 , POL.ATTRIBUTE13 , POL.ATTRIBUTE14 , POL.ATTRIBUTE15 , POL.UNIT_PRICE * POL.QUANTITY , DECODE(POL.item_id, NULL, MUOM1.UOM_CLASS , MUOM2.UOM_CLASS) , POLC1.DISPLAYED_FIELD , POLC2.DISPLAYED_FIELD , DECODE(POL.item_id, NULL, 'N', 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.ALLOWED_UNITS_LOOKUP_CODE , MSI.OUTSIDE_OPERATION_UOM_TYPE ,POL.GLOBAL_ATTRIBUTE_CATEGORY ,POL.GLOBAL_ATTRIBUTE1 ,POL.GLOBAL_ATTRIBUTE2 ,POL.GLOBAL_ATTRIBUTE3 ,POL.GLOBAL_ATTRIBUTE4 ,POL.GLOBAL_ATTRIBUTE5 ,POL.GLOBAL_ATTRIBUTE6 ,POL.GLOBAL_ATTRIBUTE7 ,POL.GLOBAL_ATTRIBUTE8 ,POL.GLOBAL_ATTRIBUTE9 ,POL.GLOBAL_ATTRIBUTE10 ,POL.GLOBAL_ATTRIBUTE11 ,POL.GLOBAL_ATTRIBUTE12 ,POL.GLOBAL_ATTRIBUTE13 ,POL.GLOBAL_ATTRIBUTE14 ,POL.GLOBAL_ATTRIBUTE15 ,POL.GLOBAL_ATTRIBUTE16 ,POL.GLOBAL_ATTRIBUTE17 ,POL.GLOBAL_ATTRIBUTE18 ,POL.GLOBAL_ATTRIBUTE19 ,POL.GLOBAL_ATTRIBUTE20 ,POL.EXPIRATION_DATE ,POL.TAX_CODE_ID ,POL.QC_GRADE ,POL.BASE_UOM ,POL.BASE_QTY ,POL.SECONDARY_UOM ,POL.SECONDARY_QTY ,POL.OKE_CONTRACT_HEADER_ID ,POL.OKE_CONTRACT_VERSION_ID ,POL.SECONDARY_UNIT_OF_MEASURE ,POL.SECONDARY_QUANTITY ,POL.PREFERRED_GRADE ,POL.AUCTION_HEADER_ID ,POL.AUCTION_DISPLAY_NUMBER ,POL.AUCTION_LINE_NUMBER ,POL.BID_NUMBER ,POL.BID_LINE_NUMBER ,POL.SUPPLIER_REF_NUMBER ,POL.CONTRACT_ID ,POL.JOB_ID ,POL.AMOUNT ,POL.START_DATE ,POL.CONTRACTOR_FIRST_NAME ,POL.CONTRACTOR_LAST_NAME ,PLTB.PURCHASE_BASIS ,PLTB.MATCHING_BASIS ,POL.FROM_LINE_LOCATION_ID ,POL.BASE_UNIT_PRICE ,POL.MANUAL_PRICE_CHANGE_FLAG ,DECODE(MSI.TRACKING_QUANTITY_IND,'PS',MSI.SECONDARY_DEFAULT_IND,NULL) ,DECODE(MSI.TRACKING_QUANTITY_IND,'PS',MSI.SECONDARY_UOM_CODE,NULL) ,MSI.GRADE_CONTROL_FLAG ,POL.ORG_ID FROM PO_LINE_TYPES_B PLTB, PO_LINE_TYPES_TL PLTT, MTL_UNITS_OF_MEASURE MUOM1, MTL_UNITS_OF_MEASURE MUOM2, PO_UN_NUMBERS_TL POUN, PO_HAZARD_CLASSES_TL PHC, PO_LOOKUP_CODES POLC1, PO_LOOKUP_CODES POLC2 , MTL_SYSTEM_ITEMS MSI, PO_LINES POL WHERE POL.LINE_TYPE_ID = PLTB.LINE_TYPE_ID(+) AND POL.LINE_TYPE_ID = PLTT.LINE_TYPE_ID(+) AND PLTT.LANGUAGE(+)= USERENV('LANG') AND MSI.INVENTORY_ITEM_ID(+) = POL.ITEM_ID AND MSI.ORGANIZATION_ID(+) = PO_LINES_SV4.get_inventory_orgid(POL.org_id) AND MUOM1.UNIT_OF_MEASURE(+) = POL.UNIT_MEAS_LOOKUP_CODE AND MUOM2.UNIT_OF_MEASURE(+) = MSI.PRIMARY_UNIT_OF_MEASURE AND POUN.UN_NUMBER_ID (+) = POL.UN_NUMBER_ID AND POUN.LANGUAGE(+) = USERENV('LANG') AND PHC.HAZARD_CLASS_ID (+) = POL.HAZARD_CLASS_ID AND PHC.LANGUAGE(+) = USERENV('LANG') AND POLC1.LOOKUP_TYPE (+) = 'PRICE TYPE' AND POLC1.LOOKUP_CODE (+) = POL.PRICE_TYPE_LOOKUP_CODE AND POLC2.LOOKUP_TYPE (+)= 'TRANSACTION REASON' AND POLC2.LOOKUP_CODE (+)= POL.TRANSACTION_REASON_CODE

View Text - HTML Formatted

SELECT POL.ROWID
, POL.PO_LINE_ID
, POL.LAST_UPDATE_DATE
, POL.LAST_UPDATED_BY
, POL.CREATION_DATE
, POL.CREATED_BY
, POL.LAST_UPDATE_LOGIN
, POL.REQUEST_ID
, POL.PROGRAM_APPLICATION_ID
, POL.PROGRAM_ID
, POL.PROGRAM_UPDATE_DATE
, POL.PO_HEADER_ID
, POL.LINE_TYPE_ID
, POL.LINE_NUM
, PLTT.LINE_TYPE
, POL.ITEM_ID
, SUBSTR(MSI.SEGMENT1
, 1
, 40)
, POL.ITEM_REVISION
, POL.CATEGORY_ID
, POL.ITEM_DESCRIPTION
, POL.UNIT_MEAS_LOOKUP_CODE
, POL.QUANTITY_COMMITTED
, POL.COMMITTED_AMOUNT
, POL.ALLOW_PRICE_OVERRIDE_FLAG
, POL.NOT_TO_EXCEED_PRICE
, POL.LIST_PRICE_PER_UNIT
, POL.UNIT_PRICE
, POL.QUANTITY
, POL.VENDOR_PRODUCT_NUM
, POL.UN_NUMBER_ID
, POUN.UN_NUMBER
, POL.HAZARD_CLASS_ID
, PHC.HAZARD_CLASS
, POL.MIN_ORDER_QUANTITY
, POL.MAX_ORDER_QUANTITY
, POL.QTY_RCV_TOLERANCE
, POL.OVER_TOLERANCE_ERROR_FLAG
, POL.MARKET_PRICE
, POL.UNORDERED_FLAG
, POL.CLOSED_FLAG
, POL.USER_HOLD_FLAG
, POL.CANCEL_FLAG
, POL.CANCELLED_BY
, POL.CANCEL_DATE
, POL.CANCEL_REASON
, POL.FIRM_STATUS_LOOKUP_CODE
, POL.FIRM_DATE
, POL.CONTRACT_NUM
, POL.NOTE_TO_VENDOR
, POL.FROM_HEADER_ID
, POL.FROM_LINE_ID
, POL.TAXABLE_FLAG
, NULL
, POL.TYPE_1099
, POL.CAPITAL_EXPENSE_FLAG
, POL.NEGOTIATED_BY_PREPARER_FLAG
, POL.MIN_RELEASE_AMOUNT
, POL.PRICE_TYPE_LOOKUP_CODE
, POL.PRICE_BREAK_LOOKUP_CODE
, DECODE(POL.ITEM_ID
, NULL
, 'Y'
, MSI.ALLOW_ITEM_DESC_UPDATE_FLAG)
, PLTB.ORDER_TYPE_LOOKUP_CODE
, NVL(PLTB.OUTSIDE_OPERATION_FLAG
, 'N')
, POL.TRANSACTION_REASON_CODE
, POL.CLOSED_BY
, POL.CLOSED_DATE
, POL.CLOSED_CODE
, POL.CLOSED_REASON
, POL.GOVERNMENT_CONTEXT
, POL.USSGL_TRANSACTION_CODE
, POL.REFERENCE_NUM
, POL.ATTRIBUTE_CATEGORY
, POL.ATTRIBUTE1
, POL.ATTRIBUTE2
, POL.ATTRIBUTE3
, POL.ATTRIBUTE4
, POL.ATTRIBUTE5
, POL.ATTRIBUTE6
, POL.ATTRIBUTE7
, POL.ATTRIBUTE8
, POL.ATTRIBUTE9
, POL.ATTRIBUTE10
, POL.ATTRIBUTE11
, POL.ATTRIBUTE12
, POL.ATTRIBUTE13
, POL.ATTRIBUTE14
, POL.ATTRIBUTE15
, POL.UNIT_PRICE * POL.QUANTITY
, DECODE(POL.ITEM_ID
, NULL
, MUOM1.UOM_CLASS
, MUOM2.UOM_CLASS)
, POLC1.DISPLAYED_FIELD
, POLC2.DISPLAYED_FIELD
, DECODE(POL.ITEM_ID
, NULL
, 'N'
, 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.ALLOWED_UNITS_LOOKUP_CODE
, MSI.OUTSIDE_OPERATION_UOM_TYPE
, POL.GLOBAL_ATTRIBUTE_CATEGORY
, POL.GLOBAL_ATTRIBUTE1
, POL.GLOBAL_ATTRIBUTE2
, POL.GLOBAL_ATTRIBUTE3
, POL.GLOBAL_ATTRIBUTE4
, POL.GLOBAL_ATTRIBUTE5
, POL.GLOBAL_ATTRIBUTE6
, POL.GLOBAL_ATTRIBUTE7
, POL.GLOBAL_ATTRIBUTE8
, POL.GLOBAL_ATTRIBUTE9
, POL.GLOBAL_ATTRIBUTE10
, POL.GLOBAL_ATTRIBUTE11
, POL.GLOBAL_ATTRIBUTE12
, POL.GLOBAL_ATTRIBUTE13
, POL.GLOBAL_ATTRIBUTE14
, POL.GLOBAL_ATTRIBUTE15
, POL.GLOBAL_ATTRIBUTE16
, POL.GLOBAL_ATTRIBUTE17
, POL.GLOBAL_ATTRIBUTE18
, POL.GLOBAL_ATTRIBUTE19
, POL.GLOBAL_ATTRIBUTE20
, POL.EXPIRATION_DATE
, POL.TAX_CODE_ID
, POL.QC_GRADE
, POL.BASE_UOM
, POL.BASE_QTY
, POL.SECONDARY_UOM
, POL.SECONDARY_QTY
, POL.OKE_CONTRACT_HEADER_ID
, POL.OKE_CONTRACT_VERSION_ID
, POL.SECONDARY_UNIT_OF_MEASURE
, POL.SECONDARY_QUANTITY
, POL.PREFERRED_GRADE
, POL.AUCTION_HEADER_ID
, POL.AUCTION_DISPLAY_NUMBER
, POL.AUCTION_LINE_NUMBER
, POL.BID_NUMBER
, POL.BID_LINE_NUMBER
, POL.SUPPLIER_REF_NUMBER
, POL.CONTRACT_ID
, POL.JOB_ID
, POL.AMOUNT
, POL.START_DATE
, POL.CONTRACTOR_FIRST_NAME
, POL.CONTRACTOR_LAST_NAME
, PLTB.PURCHASE_BASIS
, PLTB.MATCHING_BASIS
, POL.FROM_LINE_LOCATION_ID
, POL.BASE_UNIT_PRICE
, POL.MANUAL_PRICE_CHANGE_FLAG
, DECODE(MSI.TRACKING_QUANTITY_IND
, 'PS'
, MSI.SECONDARY_DEFAULT_IND
, NULL)
, DECODE(MSI.TRACKING_QUANTITY_IND
, 'PS'
, MSI.SECONDARY_UOM_CODE
, NULL)
, MSI.GRADE_CONTROL_FLAG
, POL.ORG_ID
FROM PO_LINE_TYPES_B PLTB
, PO_LINE_TYPES_TL PLTT
, MTL_UNITS_OF_MEASURE MUOM1
, MTL_UNITS_OF_MEASURE MUOM2
, PO_UN_NUMBERS_TL POUN
, PO_HAZARD_CLASSES_TL PHC
, PO_LOOKUP_CODES POLC1
, PO_LOOKUP_CODES POLC2
, MTL_SYSTEM_ITEMS MSI
, PO_LINES POL
WHERE POL.LINE_TYPE_ID = PLTB.LINE_TYPE_ID(+)
AND POL.LINE_TYPE_ID = PLTT.LINE_TYPE_ID(+)
AND PLTT.LANGUAGE(+)= USERENV('LANG')
AND MSI.INVENTORY_ITEM_ID(+) = POL.ITEM_ID
AND MSI.ORGANIZATION_ID(+) = PO_LINES_SV4.GET_INVENTORY_ORGID(POL.ORG_ID)
AND MUOM1.UNIT_OF_MEASURE(+) = POL.UNIT_MEAS_LOOKUP_CODE
AND MUOM2.UNIT_OF_MEASURE(+) = MSI.PRIMARY_UNIT_OF_MEASURE
AND POUN.UN_NUMBER_ID (+) = POL.UN_NUMBER_ID
AND POUN.LANGUAGE(+) = USERENV('LANG')
AND PHC.HAZARD_CLASS_ID (+) = POL.HAZARD_CLASS_ID
AND PHC.LANGUAGE(+) = USERENV('LANG')
AND POLC1.LOOKUP_TYPE (+) = 'PRICE TYPE'
AND POLC1.LOOKUP_CODE (+) = POL.PRICE_TYPE_LOOKUP_CODE
AND POLC2.LOOKUP_TYPE (+)= 'TRANSACTION REASON'
AND POLC2.LOOKUP_CODE (+)= POL.TRANSACTION_REASON_CODE