VIEW: APPS.PO_SGD_LINELOCS_V

Source

View Text - Preformatted

SELECT po_header_id pk1_value, draft_id pk2_value, po_line_id pk3_value, line_location_id pk4_value, null pk5_value, col_name , col_value, decode(col_name, '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')), null) col_desc from ( SELECT pll.po_header_id, nvl(pll.draft_id, -1) draft_id, pll.po_line_id, pll.line_location_id, to_char(pll.shipment_num) shipment_num, to_char(pll.ship_to_location_id) ship_to_location_id, to_char(pll.need_by_date) need_by_date, to_char(pll.promised_date) promised_date, to_char(pll.clm_period_perf_start_date) clm_period_perf_start_date, to_char(pll.clm_period_perf_end_date) clm_period_pef_end_date, to_char(pll.days_early_receipt_allowed) days_early_receipt_allowed, to_char(pll.days_late_receipt_allowed) days_late_receipt_allowed, to_char(pll.last_accept_date) last_accept_date, pll.receipt_days_exception_code, pll.allow_substitute_receipts_flag, to_char(pll.receive_close_tolerance) receive_close_tolerance, to_char(qty_rcv_tolerance) qty_rcv_tolerance, pll.qty_rcv_exception_code, to_char(receiving_routing_id) receiving_routing_id, pll.enforce_ship_to_location_code, pll.accrue_on_receipt_flag, pll.match_option, to_char(pll.invoice_close_tolerance) invoice_close_tolerance, pll.note_to_receiver, to_char(pll.quantity) quantity, to_char(pll.quantity_received) quantity_received, to_char(pll.quantity_accepted) quantity_accepted, to_char(pll.quantity_rejected) quantity_rejected, to_char(pll.quantity_billed) quantity_billed, to_char(pll.quantity_cancelled) quantity_cancelled, to_char(pll.amount) amount, to_char(pll.amount_received) amount_received, to_char(pll.amount_billed) amount_billed, to_char(pll.amount_cancelled) amount_cancelled, to_char(pll.amount_rejected) amount_rejected, to_char(pll.amount_accepted) amount_accepted, to_char(pll.amount_shipped) amount_shipped, pll.unit_meas_lookup_code, to_char(pll.price_override) price_override, null as change_status FROM po_line_locations_all pll WHERE pll.po_header_id = PO_GEN_DIFF_PKG.getBasePK1 and nvl(pll.draft_id, -1) = PO_GEN_DIFF_PKG.getBasePK2 and pll.po_line_id = nvl(PO_GEN_DIFF_PKG.getBasePK3, pll.po_line_id) and pll.line_location_id = nvl(PO_GEN_DIFF_PKG.getBasePK4, pll.line_location_id ) and EXISTS (select 1 from po_line_locations_draft_all where draft_id = PO_GEN_DIFF_PKG.getModPK2 and line_location_id = pll.line_location_id and change_status IN ('UPDATE')) )po_lineloc_unpivot_data unpivot include nulls ( col_value for col_name in ( SHIPMENT_NUM, SHIP_TO_LOCATION_ID, NEED_BY_DATE, PROMISED_DATE, CLM_PERIOD_PERF_START_DATE, CLM_PERIOD_PEF_END_DATE, DAYS_EARLY_RECEIPT_ALLOWED, DAYS_LATE_RECEIPT_ALLOWED, LAST_ACCEPT_DATE, RECEIPT_DAYS_EXCEPTION_CODE, ALLOW_SUBSTITUTE_RECEIPTS_FLAG, RECEIVE_CLOSE_TOLERANCE, QTY_RCV_TOLERANCE, QTY_RCV_EXCEPTION_CODE, RECEIVING_ROUTING_ID, ENFORCE_SHIP_TO_LOCATION_CODE, ACCRUE_ON_RECEIPT_FLAG, MATCH_OPTION, INVOICE_CLOSE_TOLERANCE, NOTE_TO_RECEIVER, QUANTITY, QUANTITY_RECEIVED, QUANTITY_ACCEPTED, QUANTITY_REJECTED, QUANTITY_BILLED, QUANTITY_CANCELLED, AMOUNT, AMOUNT_RECEIVED, AMOUNT_BILLED, AMOUNT_CANCELLED, AMOUNT_REJECTED, AMOUNT_ACCEPTED, AMOUNT_SHIPPED, UNIT_MEAS_LOOKUP_CODE, PRICE_OVERRIDE, CHANGE_STATUS ) )

View Text - HTML Formatted

SELECT PO_HEADER_ID PK1_VALUE
, DRAFT_ID PK2_VALUE
, PO_LINE_ID PK3_VALUE
, LINE_LOCATION_ID PK4_VALUE
, NULL PK5_VALUE
, COL_NAME
, COL_VALUE
, DECODE(COL_NAME
, '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'))
, NULL) COL_DESC
FROM ( SELECT PLL.PO_HEADER_ID
, NVL(PLL.DRAFT_ID
, -1) DRAFT_ID
, PLL.PO_LINE_ID
, PLL.LINE_LOCATION_ID
, TO_CHAR(PLL.SHIPMENT_NUM) SHIPMENT_NUM
, TO_CHAR(PLL.SHIP_TO_LOCATION_ID) SHIP_TO_LOCATION_ID
, TO_CHAR(PLL.NEED_BY_DATE) NEED_BY_DATE
, TO_CHAR(PLL.PROMISED_DATE) PROMISED_DATE
, TO_CHAR(PLL.CLM_PERIOD_PERF_START_DATE) CLM_PERIOD_PERF_START_DATE
, TO_CHAR(PLL.CLM_PERIOD_PERF_END_DATE) CLM_PERIOD_PEF_END_DATE
, TO_CHAR(PLL.DAYS_EARLY_RECEIPT_ALLOWED) DAYS_EARLY_RECEIPT_ALLOWED
, TO_CHAR(PLL.DAYS_LATE_RECEIPT_ALLOWED) DAYS_LATE_RECEIPT_ALLOWED
, TO_CHAR(PLL.LAST_ACCEPT_DATE) LAST_ACCEPT_DATE
, PLL.RECEIPT_DAYS_EXCEPTION_CODE
, PLL.ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, TO_CHAR(PLL.RECEIVE_CLOSE_TOLERANCE) RECEIVE_CLOSE_TOLERANCE
, TO_CHAR(QTY_RCV_TOLERANCE) QTY_RCV_TOLERANCE
, PLL.QTY_RCV_EXCEPTION_CODE
, TO_CHAR(RECEIVING_ROUTING_ID) RECEIVING_ROUTING_ID
, PLL.ENFORCE_SHIP_TO_LOCATION_CODE
, PLL.ACCRUE_ON_RECEIPT_FLAG
, PLL.MATCH_OPTION
, TO_CHAR(PLL.INVOICE_CLOSE_TOLERANCE) INVOICE_CLOSE_TOLERANCE
, PLL.NOTE_TO_RECEIVER
, TO_CHAR(PLL.QUANTITY) QUANTITY
, TO_CHAR(PLL.QUANTITY_RECEIVED) QUANTITY_RECEIVED
, TO_CHAR(PLL.QUANTITY_ACCEPTED) QUANTITY_ACCEPTED
, TO_CHAR(PLL.QUANTITY_REJECTED) QUANTITY_REJECTED
, TO_CHAR(PLL.QUANTITY_BILLED) QUANTITY_BILLED
, TO_CHAR(PLL.QUANTITY_CANCELLED) QUANTITY_CANCELLED
, TO_CHAR(PLL.AMOUNT) AMOUNT
, TO_CHAR(PLL.AMOUNT_RECEIVED) AMOUNT_RECEIVED
, TO_CHAR(PLL.AMOUNT_BILLED) AMOUNT_BILLED
, TO_CHAR(PLL.AMOUNT_CANCELLED) AMOUNT_CANCELLED
, TO_CHAR(PLL.AMOUNT_REJECTED) AMOUNT_REJECTED
, TO_CHAR(PLL.AMOUNT_ACCEPTED) AMOUNT_ACCEPTED
, TO_CHAR(PLL.AMOUNT_SHIPPED) AMOUNT_SHIPPED
, PLL.UNIT_MEAS_LOOKUP_CODE
, TO_CHAR(PLL.PRICE_OVERRIDE) PRICE_OVERRIDE
, NULL AS CHANGE_STATUS
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.PO_HEADER_ID = PO_GEN_DIFF_PKG.GETBASEPK1
AND NVL(PLL.DRAFT_ID
, -1) = PO_GEN_DIFF_PKG.GETBASEPK2
AND PLL.PO_LINE_ID = NVL(PO_GEN_DIFF_PKG.GETBASEPK3
, PLL.PO_LINE_ID)
AND PLL.LINE_LOCATION_ID = NVL(PO_GEN_DIFF_PKG.GETBASEPK4
, PLL.LINE_LOCATION_ID )
AND EXISTS (SELECT 1
FROM PO_LINE_LOCATIONS_DRAFT_ALL
WHERE DRAFT_ID = PO_GEN_DIFF_PKG.GETMODPK2
AND LINE_LOCATION_ID = PLL.LINE_LOCATION_ID
AND CHANGE_STATUS IN ('UPDATE')) )PO_LINELOC_UNPIVOT_DATA UNPIVOT INCLUDE NULLS ( COL_VALUE FOR COL_NAME IN ( SHIPMENT_NUM
, SHIP_TO_LOCATION_ID
, NEED_BY_DATE
, PROMISED_DATE
, CLM_PERIOD_PERF_START_DATE
, CLM_PERIOD_PEF_END_DATE
, DAYS_EARLY_RECEIPT_ALLOWED
, DAYS_LATE_RECEIPT_ALLOWED
, LAST_ACCEPT_DATE
, RECEIPT_DAYS_EXCEPTION_CODE
, ALLOW_SUBSTITUTE_RECEIPTS_FLAG
, RECEIVE_CLOSE_TOLERANCE
, QTY_RCV_TOLERANCE
, QTY_RCV_EXCEPTION_CODE
, RECEIVING_ROUTING_ID
, ENFORCE_SHIP_TO_LOCATION_CODE
, ACCRUE_ON_RECEIPT_FLAG
, MATCH_OPTION
, INVOICE_CLOSE_TOLERANCE
, NOTE_TO_RECEIVER
, QUANTITY
, QUANTITY_RECEIVED
, QUANTITY_ACCEPTED
, QUANTITY_REJECTED
, QUANTITY_BILLED
, QUANTITY_CANCELLED
, AMOUNT
, AMOUNT_RECEIVED
, AMOUNT_BILLED
, AMOUNT_CANCELLED
, AMOUNT_REJECTED
, AMOUNT_ACCEPTED
, AMOUNT_SHIPPED
, UNIT_MEAS_LOOKUP_CODE
, PRICE_OVERRIDE
, CHANGE_STATUS ) )