VIEW: APPS.PO_SGD_LINES_V

Source

View Text - Preformatted

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 ) )

View Text - HTML Formatted

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 ) )