VIEW: APPS.PO_LINES_XML

Source

View Text - Preformatted

SELECT PL.ITEM_REVISION, PL.LINE_NUM, DECODE(nvl(MSI.ALLOW_ITEM_DESC_UPDATE_FLAG,'Y'),'Y', PL.ITEM_DESCRIPTION, DECODE(PL.ORDER_TYPE_LOOKUP_CODE,'QUANTITY', MSI.DESCRIPTION, PL.ITEM_DESCRIPTION)) ITEM_DESCRIPTION, NVL(PL.CANCEL_FLAG,'N') CANCEL_FLAG, TO_CHAR(PL.CANCEL_DATE,'DD-MON-YYYY HH24:MI:SS') CANCEL_DATE, PL.CANCEL_REASON, PL.VENDOR_PRODUCT_NUM, PL.NOTE_TO_VENDOR, NVL(MUM.UNIT_OF_MEASURE_TL, PL.UNIT_MEAS_LOOKUP_CODE) UNIT_MEAS_LOOKUP_CODE, PUN.UN_NUMBER, PUN.DESCRIPTION UN_DESC, PHC.HAZARD_CLASS, PLT.ORDER_TYPE_LOOKUP_CODE, DECODE(NVL(PL.CONTRACT_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETSEGMENTNUM(PL.CONTRACT_ID)) CONTRACT_NUM, DECODE(NVL(PL.FROM_HEADER_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETSEGMENTNUM(PL.FROM_HEADER_ID)) SEGMENT1, DECODE(NVL(PL.FROM_HEADER_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETAGREEMENTFLAG()) GLOBAL_AGREEMENT_FLAG, DECODE(NVL(PL.FROM_HEADER_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETQUOTENUMBER()) QUOTE_VENDOR_QUOTE_NUMBER, DECODE(NVL(PL.FROM_LINE_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETAGREEMENTLINENUMBER(PL.FROM_LINE_ID)) QUOTATION_LINE, PL.ATTRIBUTE_CATEGORY, PL.ATTRIBUTE1, PL.ATTRIBUTE2, PL.ATTRIBUTE3, PL.ATTRIBUTE4, PL.ATTRIBUTE5, PL.ATTRIBUTE6, PL.ATTRIBUTE7, PL.ATTRIBUTE8, PL.ATTRIBUTE9, PL.ATTRIBUTE10, PL.ATTRIBUTE11, PL.ATTRIBUTE12, PL.ATTRIBUTE13, PL.ATTRIBUTE14, PL.ATTRIBUTE15, PL.UNIT_PRICE, PL.QUANTITY, PL.QUANTITY_COMMITTED, PL.PO_HEADER_ID, PL.PO_LINE_ID, PL.ITEM_ID, PL.FROM_HEADER_ID, PL.FROM_LINE_ID, PL.REFERENCE_NUM, TO_CHAR(PL.MIN_RELEASE_AMOUNT,PGT.FORMAT_MASK) MIN_RELEASE_AMOUNT, PL.PRICE_TYPE_LOOKUP_CODE, PL.CLOSED_CODE, PL.PRICE_BREAK_LOOKUP_CODE, PL.USSGL_TRANSACTION_CODE, PL.GOVERNMENT_CONTEXT, PL.REQUEST_ID, PL.PROGRAM_APPLICATION_ID, PL.PROGRAM_ID, TO_CHAR(PL.PROGRAM_UPDATE_DATE,'DD-MON-YYYY HH24:MI:SS') PROGRAM_UPDATE_DATE, TO_CHAR(PL.CLOSED_DATE,'DD-MON-YYYY HH24:MI:SS') CLOSED_DATE, PL.CLOSED_REASON, PL.CLOSED_BY, PL.TRANSACTION_REASON_CODE, PL.ORG_ID, PL.HAZARD_CLASS_ID, PL.MIN_ORDER_QUANTITY, PL.MAX_ORDER_QUANTITY, PL.QTY_RCV_TOLERANCE, PL.OVER_TOLERANCE_ERROR_FLAG, PL.MARKET_PRICE, PL.UNORDERED_FLAG, PL.CLOSED_FLAG, PL.USER_HOLD_FLAG, PL.CANCELLED_BY, PL.FIRM_STATUS_LOOKUP_CODE, TO_CHAR(PL.FIRM_DATE, 'DD-MON-YYYY HH24:MI:SS') FIRM_DATE, PL.TAXABLE_FLAG, PL.TYPE_1099, PL.CAPITAL_EXPENSE_FLAG, PL.NEGOTIATED_BY_PREPARER_FLAG, PL.QC_GRADE, PL.BASE_UOM, PL.BASE_QTY, PL.SECONDARY_UOM, PL.SECONDARY_QTY, TO_CHAR(PL.LAST_UPDATE_DATE,'DD-MON-YYYY HH24:MI:SS') LAST_UPDATE_DATE, PL.LAST_UPDATED_BY, PL.LINE_TYPE_ID, PL.LAST_UPDATE_LOGIN, TO_CHAR(PL.CREATION_DATE,'DD-MON-YYYY HH24:MI:SS') CREATION_DATE, PL.CREATED_BY, PL.CATEGORY_ID, TO_CHAR(PL.COMMITTED_AMOUNT,PGT.FORMAT_MASK) COMMITTED_AMOUNT, PL.ALLOW_PRICE_OVERRIDE_FLAG, PL.NOT_TO_EXCEED_PRICE, PL.LIST_PRICE_PER_UNIT, PL.UN_NUMBER_ID, PL.GLOBAL_ATTRIBUTE_CATEGORY, PL.GLOBAL_ATTRIBUTE1, PL.GLOBAL_ATTRIBUTE2, PL.GLOBAL_ATTRIBUTE3, PL.GLOBAL_ATTRIBUTE4, PL.GLOBAL_ATTRIBUTE5, PL.GLOBAL_ATTRIBUTE6, PL.GLOBAL_ATTRIBUTE7, PL.GLOBAL_ATTRIBUTE8, PL.GLOBAL_ATTRIBUTE9, PL.GLOBAL_ATTRIBUTE10, PL.GLOBAL_ATTRIBUTE11, PL.GLOBAL_ATTRIBUTE12, PL.GLOBAL_ATTRIBUTE13, PL.GLOBAL_ATTRIBUTE14, PL.GLOBAL_ATTRIBUTE15, PL.GLOBAL_ATTRIBUTE16, PL.GLOBAL_ATTRIBUTE17, PL.GLOBAL_ATTRIBUTE18, PL.GLOBAL_ATTRIBUTE19, PL.GLOBAL_ATTRIBUTE20, PL.LINE_REFERENCE_NUM, PL.PROJECT_ID, PL.TASK_ID, TO_CHAR(PL.EXPIRATION_DATE,'DD-MON-YYYY HH24:MI:SS') EXPIRATION_DATE, PL.TAX_CODE_ID, PL.OKE_CONTRACT_HEADER_ID, PL.OKE_CONTRACT_VERSION_ID, PL.TAX_NAME, PL.SECONDARY_UNIT_OF_MEASURE, PL.SECONDARY_QUANTITY, PL.PREFERRED_GRADE, PL.AUCTION_HEADER_ID, PL.AUCTION_DISPLAY_NUMBER, PL.AUCTION_LINE_NUMBER, PL.BID_NUMBER, PL.BID_LINE_NUMBER, PL.RETROACTIVE_DATE, PL.SUPPLIER_REF_NUMBER, PL.CONTRACT_ID, PL.JOB_ID, PL.AMOUNT, TO_CHAR(PL.START_DATE,'DD-MON-YYYY HH24:MI:SS') START_DATE, PLT.ORDER_TYPE_LOOKUP_CODE LINE_TYPE, PLT.PURCHASE_BASIS, MSI.CONCATENATED_SEGMENTS ITEM_NUM, DECODE(NVL(PL.JOB_ID,-1),-1, NULL, PO_COMMUNICATION_PVT.GETJOB(PL.JOB_ID)) JOB_NAME, PL.CONTRACTOR_FIRST_NAME, PL.CONTRACTOR_LAST_NAME, TO_CHAR(DECODE(PGT.PO_RELEASE_ID, NULL, PO_CORE_S.GET_TOTAL('L',PL.PO_LINE_ID), PO_CORE_S.GET_RELEASE_LINE_TOTAL(PL.PO_LINE_ID, PGT.PO_RELEASE_ID)),PGT.FORMAT_MASK) LINE_AMOUNT, DECODE(PL.CANCEL_FLAG,'Y',TO_CHAR( PO_COMMUNICATION_PVT.getCanceledAmount(PL.PO_LINE_ID, NULL, PL.PO_HEADER_ID), PGT.FORMAT_MASK), NULL ) CANCELED_AMOUNT, DECODE(PL.CANCEL_FLAG,'Y',TO_CHAR( PO_COMMUNICATION_PVT.getLineOriginalAmount(), PGT.FORMAT_MASK), NULL ) TOTAL_LINE_AMOUNT, PL.BASE_UNIT_PRICE, PL.MANUAL_PRICE_CHANGE_FLAG, PL.MATCHING_BASIS, PL.SVC_AMOUNT_NOTIF_SENT, PL.SVC_COMPLETION_NOTIF_SENT, PL.FROM_LINE_LOCATION_ID, PL.RETAINAGE_RATE, TO_CHAR(PL.MAX_RETAINAGE_AMOUNT,PGT.FORMAT_MASK) MAX_RETAINAGE_AMOUNT, PL.PROGRESS_PAYMENT_RATE, PL.RECOUPMENT_RATE FROM PO_LINE_TYPES_B PLT, PO_LINES_ALL PL, PO_UN_NUMBERS_TL PUN, PO_HAZARD_CLASSES_TL PHC, MTL_UNITS_OF_MEASURE_TL MUM, MTL_SYSTEM_ITEMS_KFV MSI, FINANCIALS_SYSTEM_PARAMS_ALL FSP, PO_COMMUNICATION_GT PGT WHERE PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID AND PL.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+) AND PL.UN_NUMBER_ID = PUN.UN_NUMBER_ID (+) AND PL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+) AND PL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+) AND NVL(MSI.ORGANIZATION_ID, FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID AND PHC.LANGUAGE(+) = USERENV('LANG') AND PUN.LANGUAGE(+) = USERENV('LANG') AND MUM.LANGUAGE(+) = USERENV('LANG') AND PL.ORG_ID = FSP.ORG_ID

View Text - HTML Formatted

SELECT PL.ITEM_REVISION
, PL.LINE_NUM
, DECODE(NVL(MSI.ALLOW_ITEM_DESC_UPDATE_FLAG
, 'Y')
, 'Y'
, PL.ITEM_DESCRIPTION
, DECODE(PL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, MSI.DESCRIPTION
, PL.ITEM_DESCRIPTION)) ITEM_DESCRIPTION
, NVL(PL.CANCEL_FLAG
, 'N') CANCEL_FLAG
, TO_CHAR(PL.CANCEL_DATE
, 'DD-MON-YYYY HH24:MI:SS') CANCEL_DATE
, PL.CANCEL_REASON
, PL.VENDOR_PRODUCT_NUM
, PL.NOTE_TO_VENDOR
, NVL(MUM.UNIT_OF_MEASURE_TL
, PL.UNIT_MEAS_LOOKUP_CODE) UNIT_MEAS_LOOKUP_CODE
, PUN.UN_NUMBER
, PUN.DESCRIPTION UN_DESC
, PHC.HAZARD_CLASS
, PLT.ORDER_TYPE_LOOKUP_CODE
, DECODE(NVL(PL.CONTRACT_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETSEGMENTNUM(PL.CONTRACT_ID)) CONTRACT_NUM
, DECODE(NVL(PL.FROM_HEADER_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETSEGMENTNUM(PL.FROM_HEADER_ID)) SEGMENT1
, DECODE(NVL(PL.FROM_HEADER_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETAGREEMENTFLAG()) GLOBAL_AGREEMENT_FLAG
, DECODE(NVL(PL.FROM_HEADER_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETQUOTENUMBER()) QUOTE_VENDOR_QUOTE_NUMBER
, DECODE(NVL(PL.FROM_LINE_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETAGREEMENTLINENUMBER(PL.FROM_LINE_ID)) QUOTATION_LINE
, PL.ATTRIBUTE_CATEGORY
, PL.ATTRIBUTE1
, PL.ATTRIBUTE2
, PL.ATTRIBUTE3
, PL.ATTRIBUTE4
, PL.ATTRIBUTE5
, PL.ATTRIBUTE6
, PL.ATTRIBUTE7
, PL.ATTRIBUTE8
, PL.ATTRIBUTE9
, PL.ATTRIBUTE10
, PL.ATTRIBUTE11
, PL.ATTRIBUTE12
, PL.ATTRIBUTE13
, PL.ATTRIBUTE14
, PL.ATTRIBUTE15
, PL.UNIT_PRICE
, PL.QUANTITY
, PL.QUANTITY_COMMITTED
, PL.PO_HEADER_ID
, PL.PO_LINE_ID
, PL.ITEM_ID
, PL.FROM_HEADER_ID
, PL.FROM_LINE_ID
, PL.REFERENCE_NUM
, TO_CHAR(PL.MIN_RELEASE_AMOUNT
, PGT.FORMAT_MASK) MIN_RELEASE_AMOUNT
, PL.PRICE_TYPE_LOOKUP_CODE
, PL.CLOSED_CODE
, PL.PRICE_BREAK_LOOKUP_CODE
, PL.USSGL_TRANSACTION_CODE
, PL.GOVERNMENT_CONTEXT
, PL.REQUEST_ID
, PL.PROGRAM_APPLICATION_ID
, PL.PROGRAM_ID
, TO_CHAR(PL.PROGRAM_UPDATE_DATE
, 'DD-MON-YYYY HH24:MI:SS') PROGRAM_UPDATE_DATE
, TO_CHAR(PL.CLOSED_DATE
, 'DD-MON-YYYY HH24:MI:SS') CLOSED_DATE
, PL.CLOSED_REASON
, PL.CLOSED_BY
, PL.TRANSACTION_REASON_CODE
, PL.ORG_ID
, PL.HAZARD_CLASS_ID
, PL.MIN_ORDER_QUANTITY
, PL.MAX_ORDER_QUANTITY
, PL.QTY_RCV_TOLERANCE
, PL.OVER_TOLERANCE_ERROR_FLAG
, PL.MARKET_PRICE
, PL.UNORDERED_FLAG
, PL.CLOSED_FLAG
, PL.USER_HOLD_FLAG
, PL.CANCELLED_BY
, PL.FIRM_STATUS_LOOKUP_CODE
, TO_CHAR(PL.FIRM_DATE
, 'DD-MON-YYYY HH24:MI:SS') FIRM_DATE
, PL.TAXABLE_FLAG
, PL.TYPE_1099
, PL.CAPITAL_EXPENSE_FLAG
, PL.NEGOTIATED_BY_PREPARER_FLAG
, PL.QC_GRADE
, PL.BASE_UOM
, PL.BASE_QTY
, PL.SECONDARY_UOM
, PL.SECONDARY_QTY
, TO_CHAR(PL.LAST_UPDATE_DATE
, 'DD-MON-YYYY HH24:MI:SS') LAST_UPDATE_DATE
, PL.LAST_UPDATED_BY
, PL.LINE_TYPE_ID
, PL.LAST_UPDATE_LOGIN
, TO_CHAR(PL.CREATION_DATE
, 'DD-MON-YYYY HH24:MI:SS') CREATION_DATE
, PL.CREATED_BY
, PL.CATEGORY_ID
, TO_CHAR(PL.COMMITTED_AMOUNT
, PGT.FORMAT_MASK) COMMITTED_AMOUNT
, PL.ALLOW_PRICE_OVERRIDE_FLAG
, PL.NOT_TO_EXCEED_PRICE
, PL.LIST_PRICE_PER_UNIT
, PL.UN_NUMBER_ID
, PL.GLOBAL_ATTRIBUTE_CATEGORY
, PL.GLOBAL_ATTRIBUTE1
, PL.GLOBAL_ATTRIBUTE2
, PL.GLOBAL_ATTRIBUTE3
, PL.GLOBAL_ATTRIBUTE4
, PL.GLOBAL_ATTRIBUTE5
, PL.GLOBAL_ATTRIBUTE6
, PL.GLOBAL_ATTRIBUTE7
, PL.GLOBAL_ATTRIBUTE8
, PL.GLOBAL_ATTRIBUTE9
, PL.GLOBAL_ATTRIBUTE10
, PL.GLOBAL_ATTRIBUTE11
, PL.GLOBAL_ATTRIBUTE12
, PL.GLOBAL_ATTRIBUTE13
, PL.GLOBAL_ATTRIBUTE14
, PL.GLOBAL_ATTRIBUTE15
, PL.GLOBAL_ATTRIBUTE16
, PL.GLOBAL_ATTRIBUTE17
, PL.GLOBAL_ATTRIBUTE18
, PL.GLOBAL_ATTRIBUTE19
, PL.GLOBAL_ATTRIBUTE20
, PL.LINE_REFERENCE_NUM
, PL.PROJECT_ID
, PL.TASK_ID
, TO_CHAR(PL.EXPIRATION_DATE
, 'DD-MON-YYYY HH24:MI:SS') EXPIRATION_DATE
, PL.TAX_CODE_ID
, PL.OKE_CONTRACT_HEADER_ID
, PL.OKE_CONTRACT_VERSION_ID
, PL.TAX_NAME
, PL.SECONDARY_UNIT_OF_MEASURE
, PL.SECONDARY_QUANTITY
, PL.PREFERRED_GRADE
, PL.AUCTION_HEADER_ID
, PL.AUCTION_DISPLAY_NUMBER
, PL.AUCTION_LINE_NUMBER
, PL.BID_NUMBER
, PL.BID_LINE_NUMBER
, PL.RETROACTIVE_DATE
, PL.SUPPLIER_REF_NUMBER
, PL.CONTRACT_ID
, PL.JOB_ID
, PL.AMOUNT
, TO_CHAR(PL.START_DATE
, 'DD-MON-YYYY HH24:MI:SS') START_DATE
, PLT.ORDER_TYPE_LOOKUP_CODE LINE_TYPE
, PLT.PURCHASE_BASIS
, MSI.CONCATENATED_SEGMENTS ITEM_NUM
, DECODE(NVL(PL.JOB_ID
, -1)
, -1
, NULL
, PO_COMMUNICATION_PVT.GETJOB(PL.JOB_ID)) JOB_NAME
, PL.CONTRACTOR_FIRST_NAME
, PL.CONTRACTOR_LAST_NAME
, TO_CHAR(DECODE(PGT.PO_RELEASE_ID
, NULL
, PO_CORE_S.GET_TOTAL('L'
, PL.PO_LINE_ID)
, PO_CORE_S.GET_RELEASE_LINE_TOTAL(PL.PO_LINE_ID
, PGT.PO_RELEASE_ID))
, PGT.FORMAT_MASK) LINE_AMOUNT
, DECODE(PL.CANCEL_FLAG
, 'Y'
, TO_CHAR( PO_COMMUNICATION_PVT.GETCANCELEDAMOUNT(PL.PO_LINE_ID
, NULL
, PL.PO_HEADER_ID)
, PGT.FORMAT_MASK)
, NULL ) CANCELED_AMOUNT
, DECODE(PL.CANCEL_FLAG
, 'Y'
, TO_CHAR( PO_COMMUNICATION_PVT.GETLINEORIGINALAMOUNT()
, PGT.FORMAT_MASK)
, NULL ) TOTAL_LINE_AMOUNT
, PL.BASE_UNIT_PRICE
, PL.MANUAL_PRICE_CHANGE_FLAG
, PL.MATCHING_BASIS
, PL.SVC_AMOUNT_NOTIF_SENT
, PL.SVC_COMPLETION_NOTIF_SENT
, PL.FROM_LINE_LOCATION_ID
, PL.RETAINAGE_RATE
, TO_CHAR(PL.MAX_RETAINAGE_AMOUNT
, PGT.FORMAT_MASK) MAX_RETAINAGE_AMOUNT
, PL.PROGRESS_PAYMENT_RATE
, PL.RECOUPMENT_RATE
FROM PO_LINE_TYPES_B PLT
, PO_LINES_ALL PL
, PO_UN_NUMBERS_TL PUN
, PO_HAZARD_CLASSES_TL PHC
, MTL_UNITS_OF_MEASURE_TL MUM
, MTL_SYSTEM_ITEMS_KFV MSI
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_COMMUNICATION_GT PGT
WHERE PL.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PL.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID (+)
AND PL.UN_NUMBER_ID = PUN.UN_NUMBER_ID (+)
AND PL.UNIT_MEAS_LOOKUP_CODE = MUM.UNIT_OF_MEASURE(+)
AND PL.ITEM_ID = MSI.INVENTORY_ITEM_ID(+)
AND NVL(MSI.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID
AND PHC.LANGUAGE(+) = USERENV('LANG')
AND PUN.LANGUAGE(+) = USERENV('LANG')
AND MUM.LANGUAGE(+) = USERENV('LANG')
AND PL.ORG_ID = FSP.ORG_ID