VIEW: APPS.POFV_BKT_PO_LINES

Source

View Text - Preformatted

SELECT PL.PO_LINE_ID, PL.LINE_NUM, PL.PO_HEADER_ID, PH.SEGMENT1, PL.ORG_ID, OP.NAME, LT.LINE_TYPE, PL.ITEM_DESCRIPTION, PL.UNIT_PRICE, PL.LIST_PRICE_PER_UNIT, PL.MARKET_PRICE, '_LA:pl.PRICE_TYPE_LOOKUP_CODE:PO_LOOKUP_CODES:PRICE TYPE:DISPLAYED_FIELD', '_LA:pl.PRICE_BREAK_LOOKUP_CODE:PO_LOOKUP_CODES:PRICE BREAK TYPE:DISPLAYED_FIELD', PL.COMMITTED_AMOUNT, PL.QUANTITY_COMMITTED, '_LA:pl.ALLOW_PRICE_OVERRIDE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', PL.NOT_TO_EXCEED_PRICE, PL.MIN_RELEASE_AMOUNT, '_LA:pl.CANCEL_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', PL.CANCEL_DATE, PL.CANCEL_REASON, '_LA:pl.CLOSED_CODE:PO_LOOKUP_CODES:DOCUMENT STATE:DISPLAYED_FIELD', PL.CLOSED_DATE, PL.CLOSED_REASON, '_LA:pl.NEGOTIATED_BY_PREPARER_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', '_LA:pl.CAPITAL_EXPENSE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', PL.NOTE_TO_VENDOR, PL.VENDOR_PRODUCT_NUM, '_LA:pl.TAXABLE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD', PL.TYPE_1099, PL.TRANSACTION_REASON_CODE, PL.EXPIRATION_DATE, PL.USSGL_TRANSACTION_CODE, '_DF:PO:PO_LINES:pl', PL.ITEM_ID, '_KF:INV:MSTK:si', PL.ITEM_REVISION, SI.ORGANIZATION_ID, MP.ORGANIZATION_CODE, PL.CATEGORY_ID, '_KF:INV:MCAT:ct', PL.UNIT_MEAS_LOOKUP_CODE, UM.UOM_CLASS, UM.DESCRIPTION, PL.HAZARD_CLASS_ID, HC.HAZARD_CLASS, PL.UN_NUMBER_ID, UN.UN_NUMBER, PL.FROM_HEADER_ID, FH.SEGMENT1, '_LA:ph.TYPE_LOOKUP_CODE:PO_LOOKUP_CODES:SOURCE DOCUMENT TYPE:DISPLAYED_FIELD', PL.FROM_LINE_ID, FL.LINE_NUM, PL.CANCELLED_BY, CA.EMPLOYEE_NUMBER, CA.FULL_NAME, PL.CLOSED_BY, CL.EMPLOYEE_NUMBER, CL.FULL_NAME, PL.TAX_CODE_ID, NULL, PL.CREATION_DATE, PL.CREATED_BY, PL.LAST_UPDATE_DATE, PL.LAST_UPDATED_BY, PL.SECONDARY_QUANTITY, PL.SECONDARY_UNIT_OF_MEASURE, PL.PREFERRED_GRADE, PL.AUCTION_DISPLAY_NUMBER, PL.AUCTION_HEADER_ID, PL.AUCTION_LINE_NUMBER, PL.BID_NUMBER, PL.BID_LINE_NUMBER, PL.RETROACTIVE_DATE, PL.OKE_CONTRACT_HEADER_ID, PL.OKE_CONTRACT_VERSION_ID, OKH.CONTRACT_NUMBER, OKV.MAJOR_VERSION, OKL.LINE_NUMBER, PL.BASE_UNIT_PRICE, PL.JOB_ID, PJVL.NAME, PL.AMOUNT, PL.PURCHASE_BASIS, PL.MATCHING_BASIS, PL.ORDER_TYPE_LOOKUP_CODE, PL.RETAINAGE_RATE, PL.MAX_RETAINAGE_AMOUNT, PL.PROGRESS_PAYMENT_RATE, PL.RECOUPMENT_RATE, PL.TAX_ATTRIBUTE_UPDATE_CODE FROM PO_LINES_ALL PL, PO_HEADERS_ALL PH, HR_ALL_ORGANIZATION_UNITS OP, PO_HAZARD_CLASSES HC, PO_UN_NUMBERS UN, PER_PEOPLE_F CA, PER_PEOPLE_F CL, MTL_CATEGORIES CT, MTL_ITEM_REVISIONS IR, MTL_SYSTEM_ITEMS SI, FINANCIALS_SYSTEM_PARAMS_ALL FP, MTL_PARAMETERS MP, PO_LINE_TYPES LT, MTL_UNITS_OF_MEASURE UM, PO_HEADERS_ALL FH, PO_LINES_ALL FL, OKC_K_HEADERS_B OKH, OKC_K_VERS_NUMBERS_V OKV, OKC_K_LINES_B OKL, PER_JOBS_VL PJVL WHERE PH.TYPE_LOOKUP_CODE = 'BLANKET' AND PL.ORG_ID = FP.ORG_ID AND FP.INVENTORY_ORGANIZATION_ID = MP.ORGANIZATION_ID AND PL.PO_HEADER_ID = PH.PO_HEADER_ID AND PL.ORG_ID = OP.ORGANIZATION_ID AND PL.HAZARD_CLASS_ID = HC.HAZARD_CLASS_ID(+) AND PL.UN_NUMBER_ID = UN.UN_NUMBER_ID(+) AND PL.CANCELLED_BY = CA.PERSON_ID(+) AND CA.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE) AND CA.EFFECTIVE_END_DATE(+) >= TRUNC(SYSDATE) AND PL.CLOSED_BY = CL.PERSON_ID(+) AND CL.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE) AND CL.EFFECTIVE_END_DATE(+) >= TRUNC(SYSDATE) AND PL.CATEGORY_ID = CT.CATEGORY_ID(+) AND PL.ITEM_REVISION = IR.REVISION(+) AND PL.ITEM_ID = IR.INVENTORY_ITEM_ID(+) AND NVL(IR.ORGANIZATION_ID, FP.INVENTORY_ORGANIZATION_ID) = FP.INVENTORY_ORGANIZATION_ID AND PL.ITEM_ID = SI.INVENTORY_ITEM_ID(+) AND NVL(SI.ORGANIZATION_ID, FP.INVENTORY_ORGANIZATION_ID) = FP.INVENTORY_ORGANIZATION_ID AND PL.LINE_TYPE_ID = LT.LINE_TYPE_ID(+) AND PL.UNIT_MEAS_LOOKUP_CODE = UM.UNIT_OF_MEASURE(+) AND PL.FROM_HEADER_ID = FH.PO_HEADER_ID(+) AND PL.FROM_LINE_ID = FL.PO_LINE_ID(+) AND PL.OKE_CONTRACT_HEADER_ID = OKH.ID(+) AND OKH.ID = OKL.CHR_ID(+) AND OKH.ID = OKV.CHR_ID(+) AND PJVL.JOB_ID (+) = PL.JOB_ID WITH READ ONLY

View Text - HTML Formatted

SELECT PL.PO_LINE_ID
, PL.LINE_NUM
, PL.PO_HEADER_ID
, PH.SEGMENT1
, PL.ORG_ID
, OP.NAME
, LT.LINE_TYPE
, PL.ITEM_DESCRIPTION
, PL.UNIT_PRICE
, PL.LIST_PRICE_PER_UNIT
, PL.MARKET_PRICE
, '_LA:PL.PRICE_TYPE_LOOKUP_CODE:PO_LOOKUP_CODES:PRICE TYPE:DISPLAYED_FIELD'
, '_LA:PL.PRICE_BREAK_LOOKUP_CODE:PO_LOOKUP_CODES:PRICE BREAK TYPE:DISPLAYED_FIELD'
, PL.COMMITTED_AMOUNT
, PL.QUANTITY_COMMITTED
, '_LA:PL.ALLOW_PRICE_OVERRIDE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, PL.NOT_TO_EXCEED_PRICE
, PL.MIN_RELEASE_AMOUNT
, '_LA:PL.CANCEL_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, PL.CANCEL_DATE
, PL.CANCEL_REASON
, '_LA:PL.CLOSED_CODE:PO_LOOKUP_CODES:DOCUMENT STATE:DISPLAYED_FIELD'
, PL.CLOSED_DATE
, PL.CLOSED_REASON
, '_LA:PL.NEGOTIATED_BY_PREPARER_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, '_LA:PL.CAPITAL_EXPENSE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, PL.NOTE_TO_VENDOR
, PL.VENDOR_PRODUCT_NUM
, '_LA:PL.TAXABLE_FLAG:PO_LOOKUP_CODES:YES/NO:DISPLAYED_FIELD'
, PL.TYPE_1099
, PL.TRANSACTION_REASON_CODE
, PL.EXPIRATION_DATE
, PL.USSGL_TRANSACTION_CODE
, '_DF:PO:PO_LINES:PL'
, PL.ITEM_ID
, '_KF:INV:MSTK:SI'
, PL.ITEM_REVISION
, SI.ORGANIZATION_ID
, MP.ORGANIZATION_CODE
, PL.CATEGORY_ID
, '_KF:INV:MCAT:CT'
, PL.UNIT_MEAS_LOOKUP_CODE
, UM.UOM_CLASS
, UM.DESCRIPTION
, PL.HAZARD_CLASS_ID
, HC.HAZARD_CLASS
, PL.UN_NUMBER_ID
, UN.UN_NUMBER
, PL.FROM_HEADER_ID
, FH.SEGMENT1
, '_LA:PH.TYPE_LOOKUP_CODE:PO_LOOKUP_CODES:SOURCE DOCUMENT TYPE:DISPLAYED_FIELD'
, PL.FROM_LINE_ID
, FL.LINE_NUM
, PL.CANCELLED_BY
, CA.EMPLOYEE_NUMBER
, CA.FULL_NAME
, PL.CLOSED_BY
, CL.EMPLOYEE_NUMBER
, CL.FULL_NAME
, PL.TAX_CODE_ID
, NULL
, PL.CREATION_DATE
, PL.CREATED_BY
, PL.LAST_UPDATE_DATE
, PL.LAST_UPDATED_BY
, PL.SECONDARY_QUANTITY
, PL.SECONDARY_UNIT_OF_MEASURE
, PL.PREFERRED_GRADE
, PL.AUCTION_DISPLAY_NUMBER
, PL.AUCTION_HEADER_ID
, PL.AUCTION_LINE_NUMBER
, PL.BID_NUMBER
, PL.BID_LINE_NUMBER
, PL.RETROACTIVE_DATE
, PL.OKE_CONTRACT_HEADER_ID
, PL.OKE_CONTRACT_VERSION_ID
, OKH.CONTRACT_NUMBER
, OKV.MAJOR_VERSION
, OKL.LINE_NUMBER
, PL.BASE_UNIT_PRICE
, PL.JOB_ID
, PJVL.NAME
, PL.AMOUNT
, PL.PURCHASE_BASIS
, PL.MATCHING_BASIS
, PL.ORDER_TYPE_LOOKUP_CODE
, PL.RETAINAGE_RATE
, PL.MAX_RETAINAGE_AMOUNT
, PL.PROGRESS_PAYMENT_RATE
, PL.RECOUPMENT_RATE
, PL.TAX_ATTRIBUTE_UPDATE_CODE
FROM PO_LINES_ALL PL
, PO_HEADERS_ALL PH
, HR_ALL_ORGANIZATION_UNITS OP
, PO_HAZARD_CLASSES HC
, PO_UN_NUMBERS UN
, PER_PEOPLE_F CA
, PER_PEOPLE_F CL
, MTL_CATEGORIES CT
, MTL_ITEM_REVISIONS IR
, MTL_SYSTEM_ITEMS SI
, FINANCIALS_SYSTEM_PARAMS_ALL FP
, MTL_PARAMETERS MP
, PO_LINE_TYPES LT
, MTL_UNITS_OF_MEASURE UM
, PO_HEADERS_ALL FH
, PO_LINES_ALL FL
, OKC_K_HEADERS_B OKH
, OKC_K_VERS_NUMBERS_V OKV
, OKC_K_LINES_B OKL
, PER_JOBS_VL PJVL
WHERE PH.TYPE_LOOKUP_CODE = 'BLANKET'
AND PL.ORG_ID = FP.ORG_ID
AND FP.INVENTORY_ORGANIZATION_ID = MP.ORGANIZATION_ID
AND PL.PO_HEADER_ID = PH.PO_HEADER_ID
AND PL.ORG_ID = OP.ORGANIZATION_ID
AND PL.HAZARD_CLASS_ID = HC.HAZARD_CLASS_ID(+)
AND PL.UN_NUMBER_ID = UN.UN_NUMBER_ID(+)
AND PL.CANCELLED_BY = CA.PERSON_ID(+)
AND CA.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE)
AND CA.EFFECTIVE_END_DATE(+) >= TRUNC(SYSDATE)
AND PL.CLOSED_BY = CL.PERSON_ID(+)
AND CL.EFFECTIVE_START_DATE(+) <= TRUNC(SYSDATE)
AND CL.EFFECTIVE_END_DATE(+) >= TRUNC(SYSDATE)
AND PL.CATEGORY_ID = CT.CATEGORY_ID(+)
AND PL.ITEM_REVISION = IR.REVISION(+)
AND PL.ITEM_ID = IR.INVENTORY_ITEM_ID(+)
AND NVL(IR.ORGANIZATION_ID
, FP.INVENTORY_ORGANIZATION_ID) = FP.INVENTORY_ORGANIZATION_ID
AND PL.ITEM_ID = SI.INVENTORY_ITEM_ID(+)
AND NVL(SI.ORGANIZATION_ID
, FP.INVENTORY_ORGANIZATION_ID) = FP.INVENTORY_ORGANIZATION_ID
AND PL.LINE_TYPE_ID = LT.LINE_TYPE_ID(+)
AND PL.UNIT_MEAS_LOOKUP_CODE = UM.UNIT_OF_MEASURE(+)
AND PL.FROM_HEADER_ID = FH.PO_HEADER_ID(+)
AND PL.FROM_LINE_ID = FL.PO_LINE_ID(+)
AND PL.OKE_CONTRACT_HEADER_ID = OKH.ID(+)
AND OKH.ID = OKL.CHR_ID(+)
AND OKH.ID = OKV.CHR_ID(+)
AND PJVL.JOB_ID (+) = PL.JOB_ID WITH READ ONLY