View: ITG_PO_LINE_ARCH_V

Product:	ITG - Internet Procurement Enterprise Connector
Description:	Used for XML mapping in the line level
Implementation/DBA Data:	APPS.ITG_PO_LINE_ARCH_V

View Text

SELECT PLA.PO_HEADER_ID PO_HEADER_ID
, PLA.LINE_NUM LINE_NUM
, PLA.PO_LINE_ID PO_LINE_ID
, PLA.REVISION_NUM REVISION_NUM
, NVL(PLLA.PO_RELEASE_ID
, -9999) RELEASE_ID
, PLA.QUANTITY QUANTITY
, MUOMTL.UOM_CODE QUANT_UOM
, PLA.VENDOR_PRODUCT_NUM ITEMX
, MSIV.CONCATENATED_SEGMENTS ITEM
, PLA.ITEM_DESCRIPTION DESCRIPTION
, PHC.HAZARD_CLASS HAZRDMTL
, PLA.ITEM_REVISION ITEMRV
, PLA.NOTE_TO_VENDOR NOTE_TO_VENDOR
, PLA.UNIT_PRICE PRICE
, 'UNIT' UNIT
, MCK.CONCATENATED_SEGMENTS CATEGORYID
, PLA.CONTRACT_NUM CONTRACT
, PLA.TASK_ID TASKID
, PLA.PROJECT_ID PROJECTID
, (SELECT SEGMENT1
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID = PLA.CONTRACT_ID) CONTRACT_NUM
, PHA.QUOTE_VENDOR_QUOTE_NUMBER VENDORQUOTENUM
, PLA.LIST_PRICE_PER_UNIT LISTPRICE
, PLA.MARKET_PRICE MARKETPRICE
, PLA.NOT_TO_EXCEED_PRICE PRICENOTTOEXCEED
, PLA.NEGOTIATED_BY_PREPARER_FLAG NEGPRICE
, PLA.TAXABLE_FLAG TAXABLE
, PLA.TRANSACTION_REASON_CODE TXNREASONCODE
, PLA.TYPE_1099 TYPE1099
, PLT.LINE_TYPE LINEORDERTYPE
, PUNT.UN_NUMBER HAZRDUNNUM
, PUNT.DESCRIPTION HAZRDUNDESC
, PLA.ATTRIBUTE1 DFF_LINE_ATTR1
, PLA.ATTRIBUTE2 DFF_LINE_ATTR2
, PLA.ATTRIBUTE3 DFF_LINE_ATTR3
, PLA.ATTRIBUTE4 DFF_LINE_ATTR4
, PLA.ATTRIBUTE5 DFF_LINE_ATTR5
, PLA.ATTRIBUTE6 DFF_LINE_ATTR6
, PLA.ATTRIBUTE7 DFF_LINE_ATTR7
, PLA.ATTRIBUTE8 DFF_LINE_ATTR8
, PLA.ATTRIBUTE9 DFF_LINE_ATTR9
, PLA.ATTRIBUTE10 DFF_LINE_ATTR10
, PLA.ATTRIBUTE11 DFF_LINE_ATTR11
, PLA.ATTRIBUTE12 DFF_LINE_ATTR12
, PLA.ATTRIBUTE13 DFF_LINE_ATTR13
, PLA.ATTRIBUTE14 DFF_LINE_ATTR14
, PLA.ATTRIBUTE15 DFF_LINE_ATTR15
, PLA.ATTRIBUTE_CATEGORY DFF_LINE_ATTR16
, MCK.ATTRIBUTE1 DFF_ITEM_ATTR1
, MCK.ATTRIBUTE2 DFF_ITEM_ATTR2
, MCK.ATTRIBUTE3 DFF_ITEM_ATTR3
, MCK.ATTRIBUTE4 DFF_ITEM_ATTR4
, MCK.ATTRIBUTE5 DFF_ITEM_ATTR5
, MCK.ATTRIBUTE6 DFF_ITEM_ATTR6
, MCK.ATTRIBUTE7 DFF_ITEM_ATTR7
, MCK.ATTRIBUTE8 DFF_ITEM_ATTR8
, MCK.ATTRIBUTE9 DFF_ITEM_ATTR9
, MCK.ATTRIBUTE10 DFF_ITEM_ATTR10
, MCK.ATTRIBUTE11 DFF_ITEM_ATTR11
, MCK.ATTRIBUTE12 DFF_ITEM_ATTR12
, MCK.ATTRIBUTE13 DFF_ITEM_ATTR13
, MCK.ATTRIBUTE14 DFF_ITEM_ATTR14
, MCK.ATTRIBUTE15 DFF_ITEM_ATTR15
, MCK.ATTRIBUTE_CATEGORY DFF_ITEM_ATTR16
, MCK.SEGMENT1 KFF_ITEM_ATTR1
, MCK.SEGMENT2 KFF_ITEM_ATTR2
, MCK.SEGMENT3 KFF_ITEM_ATTR3
, MCK.SEGMENT4 KFF_ITEM_ATTR4
, MCK.SEGMENT5 KFF_ITEM_ATTR5
, MCK.SEGMENT6 KFF_ITEM_ATTR6
, MCK.SEGMENT7 KFF_ITEM_ATTR7
, MCK.SEGMENT8 KFF_ITEM_ATTR8
, MCK.SEGMENT9 KFF_ITEM_ATTR9
, MCK.SEGMENT10 KFF_ITEM_ATTR10
, MCK.SEGMENT11 KFF_ITEM_ATTR11
, MCK.SEGMENT12 KFF_ITEM_ATTR12
, MCK.SEGMENT13 KFF_ITEM_ATTR13
, MCK.SEGMENT14 KFF_ITEM_ATTR14
, MCK.SEGMENT15 KFF_ITEM_ATTR15
, MCK.SEGMENT16 KFF_ITEM_ATTR16
, MCK.SEGMENT17 KFF_ITEM_ATTR17
, MCK.SEGMENT18 KFF_ITEM_ATTR18
, MCK.SEGMENT19 KFF_ITEM_ATTR19
, MCK.SEGMENT20 KFF_IEM_ATTR20
, PLA.ITEM_REVISION ITEMRVX
, PLA.LATEST_EXTERNAL_FLAG LATEST_EXTERNAL_FLAG
, PLA.CONTRACT_ID CONTRACT_ID
, PLA.SUPPLIER_REF_NUMBER SUPPLIER_REF_NUMBER
, DECODE(PLA.CANCEL_FLAG
, 'Y'
, 'CANCELLED'
, 'OPEN') POLNSTATUS
, PJ.NAME JOBTITLE
, PLA.CONTRACTOR_FIRST_NAME CONTRACTORFIRSTNAME
, PLA.CONTRACTOR_LAST_NAME CONTRACTORLASTNAME
, PLA.START_DATE STARTDATE
, PLA.EXPIRATION_DATE ENDDATE
, PLA.AMOUNT AMOUNT_SERVICES
FROM PO_DISTINCT_RELID_LINEID_V PLLA
, PO_LINES_ARCHIVE_ALL PLA
, PO_HEADERS_ALL PHA
, MTL_SYSTEM_ITEMS_B_KFV MSIV
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_HAZARD_CLASSES PHC
, MTL_CATEGORIES_KFV MCK
, PO_LINE_TYPES PLT
, PO_UN_NUMBERS_TL PUNT
, MTL_UNITS_OF_MEASURE_TL MUOMTL
, PER_JOBS PJ
WHERE NVL(MSIV.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID
AND FSP.ORG_ID = PLA.ORG_ID
AND MSIV.INVENTORY_ITEM_ID(+) = PLA.ITEM_ID
AND PLA.PO_LINE_ID = PLLA.PO_LINE_ID
AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND (PLA.CANCEL_FLAG <> 'Y' OR PLA.CANCEL_FLAG IS NULL )
AND (PLA.CLOSED_CODE = 'OPEN' OR PLA.CLOSED_CODE IS NULL )
AND PLA.UNIT_MEAS_LOOKUP_CODE = MUOMTL.UNIT_OF_MEASURE(+)
AND MUOMTL.LANGUAGE(+) = USERENV('LANG')
AND PLA.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID(+)
AND PHA.TYPE_LOOKUP_CODE IN ('BLANKET'
, 'PLANNED')
AND MCK.CATEGORY_ID = PLA.CATEGORY_ID
AND PLA.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PLA.UN_NUMBER_ID = PUNT.UN_NUMBER_ID(+)
AND PUNT.LANGUAGE(+) = USERENV('LANG')
AND PJ.JOB_ID(+) = PLA.JOB_ID UNION ALL SELECT PLA.PO_HEADER_ID PO_HEADER_ID
, PLA.LINE_NUM LINE_NUM
, PLA.PO_LINE_ID PO_LINE_ID
, PLA.REVISION_NUM REVISION_NUM
, -9999 RELEASE_ID
, PLA.QUANTITY QUANTITY
, MUOMTL.UOM_CODE QUANT_UOM
, PLA.VENDOR_PRODUCT_NUM ITEMX
, MSIV.CONCATENATED_SEGMENTS ITEM
, PLA.ITEM_DESCRIPTION DESCRIPTION
, PHC.HAZARD_CLASS HAZRDMTL
, PLA.ITEM_REVISION ITEMRV
, PLA.NOTE_TO_VENDOR NOTE_TO_VENDOR
, PLA.UNIT_PRICE PRICE
, 'UNIT' UNIT
, MCK.CONCATENATED_SEGMENTS CATEGORYID
, PLA.CONTRACT_NUM CONTRACT
, PLA.TASK_ID TASKID
, PLA.PROJECT_ID PROJECTID
, (SELECT SEGMENT1
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID = PLA.CONTRACT_ID) CONTRACT_NUM
, PHA.QUOTE_VENDOR_QUOTE_NUMBER VENDORQUOTENUM
, PLA.LIST_PRICE_PER_UNIT LISTPRICE
, PLA.MARKET_PRICE MARKETPRICE
, PLA.NOT_TO_EXCEED_PRICE PRICENOTTOEXCEED
, PLA.NEGOTIATED_BY_PREPARER_FLAG NEGPRICE
, PLA.TAXABLE_FLAG TAXABLE
, PLA.TRANSACTION_REASON_CODE TXNREASONCODE
, PLA.TYPE_1099 TYPE1099
, PLT.LINE_TYPE LINEORDERTYPE
, PUNT.UN_NUMBER HAZRDUNNUM
, PUNT.DESCRIPTION HAZRDUNDESC
, PLA.ATTRIBUTE1 DFF_LINE_ATTR1
, PLA.ATTRIBUTE2 DFF_LINE_ATTR2
, PLA.ATTRIBUTE3 DFF_LINE_ATTR3
, PLA.ATTRIBUTE4 DFF_LINE_ATTR4
, PLA.ATTRIBUTE5 DFF_LINE_ATTR5
, PLA.ATTRIBUTE6 DFF_LINE_ATTR6
, PLA.ATTRIBUTE7 DFF_LINE_ATTR7
, PLA.ATTRIBUTE8 DFF_LINE_ATTR8
, PLA.ATTRIBUTE9 DFF_LINE_ATTR9
, PLA.ATTRIBUTE10 DFF_LINE_ATTR10
, PLA.ATTRIBUTE11 DFF_LINE_ATTR11
, PLA.ATTRIBUTE12 DFF_LINE_ATTR12
, PLA.ATTRIBUTE13 DFF_LINE_ATTR13
, PLA.ATTRIBUTE14 DFF_LINE_ATTR14
, PLA.ATTRIBUTE15 DFF_LINE_ATTR15
, PLA.ATTRIBUTE_CATEGORY DFF_LINE_ATTR16
, MCK.ATTRIBUTE1 DFF_ITEM_ATTR1
, MCK.ATTRIBUTE2 DFF_ITEM_ATTR2
, MCK.ATTRIBUTE3 DFF_ITEM_ATTR3
, MCK.ATTRIBUTE4 DFF_ITEM_ATTR4
, MCK.ATTRIBUTE5 DFF_ITEM_ATTR5
, MCK.ATTRIBUTE6 DFF_ITEM_ATTR6
, MCK.ATTRIBUTE7 DFF_ITEM_ATTR7
, MCK.ATTRIBUTE8 DFF_ITEM_ATTR8
, MCK.ATTRIBUTE9 DFF_ITEM_ATTR9
, MCK.ATTRIBUTE10 DFF_ITEM_ATTR10
, MCK.ATTRIBUTE11 DFF_ITEM_ATTR11
, MCK.ATTRIBUTE12 DFF_ITEM_ATTR12
, MCK.ATTRIBUTE13 DFF_ITEM_ATTR13
, MCK.ATTRIBUTE14 DFF_ITEM_ATTR14
, MCK.ATTRIBUTE15 DFF_ITEM_ATTR15
, MCK.ATTRIBUTE_CATEGORY DFF_ITEM_ATTR16
, MCK.SEGMENT1 KFF_ITEM_ATTR1
, MCK.SEGMENT2 KFF_ITEM_ATTR2
, MCK.SEGMENT3 KFF_ITEM_ATTR3
, MCK.SEGMENT4 KFF_ITEM_ATTR4
, MCK.SEGMENT5 KFF_ITEM_ATTR5
, MCK.SEGMENT6 KFF_ITEM_ATTR6
, MCK.SEGMENT7 KFF_ITEM_ATTR7
, MCK.SEGMENT8 KFF_ITEM_ATTR8
, MCK.SEGMENT9 KFF_ITEM_ATTR9
, MCK.SEGMENT10 KFF_ITEM_ATTR10
, MCK.SEGMENT11 KFF_ITEM_ATTR11
, MCK.SEGMENT12 KFF_ITEM_ATTR12
, MCK.SEGMENT13 KFF_ITEM_ATTR13
, MCK.SEGMENT14 KFF_ITEM_ATTR14
, MCK.SEGMENT15 KFF_ITEM_ATTR15
, MCK.SEGMENT16 KFF_ITEM_ATTR16
, MCK.SEGMENT17 KFF_ITEM_ATTR17
, MCK.SEGMENT18 KFF_ITEM_ATTR18
, MCK.SEGMENT19 KFF_ITEM_ATTR19
, MCK.SEGMENT20 KFF_ITEM_ATTR20
, PLA.ITEM_REVISION ITEMRVX
, PLA.LATEST_EXTERNAL_FLAG LATEST_EXTERNAL_FLAG
, PLA.CONTRACT_ID CONTRACT_ID
, PLA.SUPPLIER_REF_NUMBER SUPPLIER_REF_NUMBER
, DECODE(PLA.CANCEL_FLAG
, 'Y'
, 'CANCELLED'
, 'OPEN') POLNSTATUS
, PJ.NAME JOBTITLE
, PLA.CONTRACTOR_FIRST_NAME CONTRACTORFIRSTNAME
, PLA.CONTRACTOR_LAST_NAME CONTRACTORLASTNAME
, PLA.START_DATE STARTDATE
, PLA.EXPIRATION_DATE ENDDATE
, PLA.AMOUNT AMOUNT_SERVICES
FROM PO_LINES_ARCHIVE_ALL PLA
, PO_HEADERS_ALL PHA
, MTL_SYSTEM_ITEMS_B_KFV MSIV
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PO_HAZARD_CLASSES PHC
, MTL_CATEGORIES_KFV MCK
, PO_LINE_TYPES PLT
, PO_UN_NUMBERS_TL PUNT
, MTL_UNITS_OF_MEASURE_TL MUOMTL
, PER_JOBS PJ
WHERE NVL(MSIV.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID
AND FSP.ORG_ID = PLA.ORG_ID
AND MSIV.INVENTORY_ITEM_ID(+) = PLA.ITEM_ID
AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID
AND (PLA.CANCEL_FLAG <> 'Y' OR PLA.CANCEL_FLAG IS NULL )
AND (PLA.CLOSED_CODE = 'OPEN' OR PLA.CLOSED_CODE IS NULL )
AND PLA.UNIT_MEAS_LOOKUP_CODE = MUOMTL.UNIT_OF_MEASURE(+)
AND MUOMTL.LANGUAGE(+) = USERENV('LANG')
AND PLA.HAZARD_CLASS_ID = PHC.HAZARD_CLASS_ID(+)
AND PHA.TYPE_LOOKUP_CODE = 'STANDARD'
AND MCK.CATEGORY_ID = PLA.CATEGORY_ID
AND PLA.LINE_TYPE_ID = PLT.LINE_TYPE_ID
AND PLA.UN_NUMBER_ID = PUNT.UN_NUMBER_ID(+)
AND PUNT.LANGUAGE(+) = USERENV('LANG')
AND PJ.JOB_ID(+) = PLA.JOB_ID

Columns