VIEW: APPS.PO_LINES_ARCHIVE_XML

Source

View Text - Preformatted

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

View Text - HTML Formatted

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