View: PO_SGD_LINES_V

Product:	PO - Purchasing
Description:
Implementation/DBA Data:	APPS.PO_SGD_LINES_V

View Text

SELECT PO_HEADER_ID PK1_VALUE
, DRAFT_ID PK2_VALUE
, PL_PO_LINE_ID PK3_VALUE
, NULL PK4_VALUE
, NULL PK5_VALUE
, COL_NAME
, COL_VALUE
, DECODE(COL_NAME
, 'LINE_TYPE_ID'
, (SELECT LINE_TYPE
FROM PO_LINE_TYPES
WHERE LINE_TYPE_ID = COL_VALUE)
, 'USER_DOCUMENT_STATUS'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'PO_USER_DOCUMENT_STATUS')
, 'CATEGORY_ID'
, (SELECT CONCATENATED_SEGMENTS
FROM MTL_CATEGORIES_KFV
WHERE CATEGORY_ID = COL_VALUE)
, 'CONTRACT_TYPE'
, (SELECT DESCRIPTION
FROM PO_LOOKUP_CODES
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'PO_FEDERAL_CONTRACT_TYPES_AMT' )
, 'COST_CONSTRAINT'
, (SELECT DESCRIPTION
FROM PO_LOOKUP_CODES
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'PO_FEDERAL_COST_CONSTRAINTS')
, 'CLM_IDC_TYPE'
, (SELECT DISPLAYED_FIELD
FROM PO_LOOKUP_CODES
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'PO_FEDERAL_IDC_TYPES')
, 'UNIT_MEAS_LOOKUP_CODE'
, (SELECT UNIT_OF_MEASURE_TL
FROM MTL_UNITS_OF_MEASURE_TL
WHERE UNIT_OF_MEASURE = COL_VALUE
AND LANGUAGE = USERENV('LANG'))
, 'NEGOTIATED_BY_PREPARER_FLAG'
, (SELECT MEANING
FROM FND_LOOKUPS
WHERE LOOKUP_CODE = COL_VALUE
AND LOOKUP_TYPE = 'YES_NO')
, 'FROM_HEADER_ID'
, (SELECT CLM_DOCUMENT_NUMBER
FROM PO_HEADERS_ALL
WHERE PO_HEADER_ID = COL_VALUE)
, 'FROM_LINE_ID'
, (SELECT LINE_NUM_DISPLAY
FROM PO_LINES_ALL
WHERE PO_LINE_ID = COL_VALUE)
, 'OKE_CONTRACT_HEADER_ID'
, (SELECT CONTRACT_NUMBER
FROM OKC_K_HEADERS_ALL_B
WHERE ID = COL_VALUE)
, 'ITEM_ID'
, (SELECT MSI.CONCATENATED_SEGMENTS || NVL2(PLA.ITEM_REVISION
, '
, '
, '') || PLA.ITEM_REVISION || JOBS.NAME
FROM MTL_SYSTEM_ITEMS_VL MSI
, FINANCIALS_SYSTEM_PARAMS_ALL FSP
, PER_JOBS_VL JOBS
, PO_LINES_ALL PLA
WHERE MSI.INVENTORY_ITEM_ID = COL_VALUE
AND NVL(MSI.ORGANIZATION_ID
, FSP.INVENTORY_ORGANIZATION_ID) = FSP.INVENTORY_ORGANIZATION_ID
AND FSP.ORG_ID = PLA.ORG_ID
AND JOBS.JOB_ID (+) = PLA.JOB_ID
AND PLA.PO_LINE_ID = PL_PO_LINE_ID )
, NULL) COL_DESC
FROM ( SELECT PL.PO_HEADER_ID
, NVL(PL.DRAFT_ID
, -1) DRAFT_ID
, PL.PO_LINE_ID PL_PO_LINE_ID
, TO_CHAR(PL.LINE_NUM) LINE_NUM
, PL.LINE_NUM_DISPLAY
, TO_CHAR(PL.LINE_TYPE_ID) LINE_TYPE_ID
, PL.ORDER_TYPE_LOOKUP_CODE
, PL.PURCHASE_BASIS
, PL.MATCHING_BASIS
, TO_CHAR(PL.ITEM_ID) ITEM_ID
, PL.ITEM_DESCRIPTION
, PL.ITEM_REVISION
, PL.VENDOR_PRODUCT_NUM
, PL.USER_DOCUMENT_STATUS
, TO_CHAR(PL.CATEGORY_ID) CATEGORY_ID
, PL.CONTRACT_TYPE
, PL.COST_CONSTRAINT
, PL.CLM_IDC_TYPE
, DECODE(PL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, PL.UNIT_MEAS_LOOKUP_CODE
, NULL) UNIT_MEAS_LOOKUP_CODE
, TO_CHAR(DECODE(PL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, PL.UNIT_PRICE
, NULL)) UNIT_PRICE
, TO_CHAR(DECODE(PL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, PL.QUANTITY
, NULL)) QUANTITY
, TO_CHAR(DECODE(PL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, PL.LIST_PRICE_PER_UNIT
, NULL)) LIST_PRICE_PER_UNIT
, TO_CHAR(DECODE(PL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, PL.AMOUNT
, NULL)) AMOUNT
, PL.NEGOTIATED_BY_PREPARER_FLAG
, PL.CLM_OPTION_INDICATOR
, TO_CHAR(PL.CLM_BASE_LINE_NUM) CLM_BASE_LINE_NUM
, TO_CHAR(PL.CLM_OPTION_NUM) CLM_OPTION_NUM
, TO_CHAR(PL.CLM_OPTION_FROM_DATE) CLM_OPTION_FROM_DATE
, TO_CHAR(PL.CLM_OPTION_TO_DATE) CLM_OPTION_TO_DATE
, PL.CLM_EXERCISED_FLAG
, TO_CHAR(PL.CLM_EXERCISED_DATE) CLM_EXERCISED_DATE
, PL.CLM_INFO_FLAG
, TO_CHAR(PL.FROM_HEADER_ID) FROM_HEADER_ID
, TO_CHAR(PL.FROM_LINE_ID) FROM_LINE_ID
, TO_CHAR(PL.FROM_LINE_LOCATION_ID) FROM_LINE_LOCATION_ID
, TO_CHAR(PL.OKE_CONTRACT_HEADER_ID) OKE_CONTRACT_HEADER_ID
, TO_CHAR(PL.OKE_CONTRACT_VERSION_ID) OKE_CONTRACT_VERSION_ID
, TO_CHAR(DECODE(PL.ORDER_TYPE_LOOKUP_CODE
, 'QUANTITY'
, NVL(PL.QUANTITY
, 0)*NVL(PL.UNIT_PRICE
, 0)
, NULL)) TOTAL_AMOUNT
, PL.NOTE_TO_VENDOR
, PL.CONTRACTOR_FIRST_NAME
, PL.CONTRACTOR_LAST_NAME
, (PL.CONTRACTOR_FIRST_NAME || NVL2(PL.CONTRACTOR_LAST_NAME
, '
, '
, '') || PL.CONTRACTOR_LAST_NAME) CONTRACTOR_NAME
, TO_CHAR(PL.START_DATE) START_DATE
, TO_CHAR(PL.EXPIRATION_DATE) EXPIRATION_DATE
, TO_CHAR(PL.JOB_ID) JOB_ID
, PL.CLM_FUNDED_FLAG
, TO_CHAR(PL.NOT_TO_EXCEED_PRICE) NOT_TO_EXCEED_PRICE
, PL.ALLOW_PRICE_OVERRIDE_FLAG
, TO_CHAR(PL.MIN_RELEASE_AMOUNT) MIN_RELEASE_AMOUNT
, TO_CHAR(PL.QUANTITY_COMMITTED) QUANTITY_COMMITTED
, TO_CHAR(PL.COMMITTED_AMOUNT) COMMITTED_AMOUNT
, TO_CHAR(PL.CLM_ORDER_START_DATE) CLM_ORDER_START_DATE
, TO_CHAR(PL.CLM_ORDER_END_DATE) CLM_ORDER_END_DATE
, TO_CHAR(PL.CLM_MAX_TOTAL_AMOUNT) CLM_MAX_TOTAL_AMOUNT
, TO_CHAR(PL.CLM_MIN_TOTAL_AMOUNT) CLM_MIN_TOTAL_AMOUNT
, TO_CHAR(PL.CLM_MAX_TOTAL_QUANTITY) CLM_MAX_TOTAL_QUANTITY
, TO_CHAR(PL.CLM_MIN_TOTAL_QUANTITY) CLM_MIN_TOTAL_QUANTITY
, TO_CHAR(PL.CLM_MAX_ORDER_AMOUNT) CLM_MAX_ORDER_AMOUNT
, TO_CHAR(PL.CLM_MIN_ORDER_AMOUNT) CLM_MIN_ORDER_AMOUNT
, TO_CHAR(PL.CLM_MAX_ORDER_QUANTITY) CLM_MAX_ORDER_QUANTITY
, TO_CHAR(PL.CLM_MIN_ORDER_QUANTITY) CLM_MIN_ORDER_QUANTITY
, TO_CHAR(PL.CLM_TOTAL_AMOUNT_ORDERED) CLM_TOTAL_AMOUNT_ORDERED
, TO_CHAR(PL.CLM_TOTAL_QUANTITY_ORDERED) CLM_TOTAL_QUANTITY_ORDERED
, PL.ATTRIBUTE13
, NULL AS CHANGE_STATUS
FROM PO_LINES_ALL PL
WHERE PL.PO_HEADER_ID = PO_GEN_DIFF_PKG.GETBASEPK1
AND NVL(PL.DRAFT_ID
, -1) = PO_GEN_DIFF_PKG.GETBASEPK2
AND PL.PO_LINE_ID = NVL(PO_GEN_DIFF_PKG.GETBASEPK3
, PL.PO_LINE_ID)
AND EXISTS (SELECT 1
FROM PO_LINES_DRAFT_ALL
WHERE DRAFT_ID = PO_GEN_DIFF_PKG.GETMODPK2
AND PO_LINE_ID = PL.PO_LINE_ID
AND CHANGE_STATUS IN ('UPDATE')) ) PO_LINE_UNPIVOT_DATA UNPIVOT INCLUDE NULLS ( COL_VALUE FOR COL_NAME IN ( LINE_NUM
, LINE_NUM_DISPLAY
, LINE_TYPE_ID
, ORDER_TYPE_LOOKUP_CODE
, PURCHASE_BASIS
, MATCHING_BASIS
, ITEM_ID
, ITEM_DESCRIPTION
, ITEM_REVISION
, VENDOR_PRODUCT_NUM
, USER_DOCUMENT_STATUS
, CATEGORY_ID
, CONTRACT_TYPE
, COST_CONSTRAINT
, CLM_IDC_TYPE
, UNIT_MEAS_LOOKUP_CODE
, UNIT_PRICE
, QUANTITY
, LIST_PRICE_PER_UNIT
, AMOUNT
, NEGOTIATED_BY_PREPARER_FLAG
, CLM_OPTION_INDICATOR
, CLM_BASE_LINE_NUM
, CLM_OPTION_NUM
, CLM_OPTION_FROM_DATE
, CLM_OPTION_TO_DATE
, CLM_EXERCISED_FLAG
, CLM_EXERCISED_DATE
, CLM_INFO_FLAG
, FROM_HEADER_ID
, FROM_LINE_ID
, FROM_LINE_LOCATION_ID
, OKE_CONTRACT_HEADER_ID
, OKE_CONTRACT_VERSION_ID
, TOTAL_AMOUNT
, NOTE_TO_VENDOR
, CONTRACTOR_FIRST_NAME
, CONTRACTOR_LAST_NAME
, CONTRACTOR_NAME
, START_DATE
, EXPIRATION_DATE
, JOB_ID
, CLM_FUNDED_FLAG
, NOT_TO_EXCEED_PRICE
, ALLOW_PRICE_OVERRIDE_FLAG
, MIN_RELEASE_AMOUNT
, QUANTITY_COMMITTED
, COMMITTED_AMOUNT
, CLM_ORDER_START_DATE
, CLM_ORDER_END_DATE
, CLM_MAX_TOTAL_AMOUNT
, CLM_MIN_TOTAL_AMOUNT
, CLM_MAX_TOTAL_QUANTITY
, CLM_MIN_TOTAL_QUANTITY
, CLM_MAX_ORDER_AMOUNT
, CLM_MIN_ORDER_AMOUNT
, CLM_MAX_ORDER_QUANTITY
, CLM_MIN_ORDER_QUANTITY
, CLM_TOTAL_AMOUNT_ORDERED
, CLM_TOTAL_QUANTITY_ORDERED
, ATTRIBUTE13
, CHANGE_STATUS ) )

Columns