[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
, token1_name
, token1_value
)
SELECT
x_result_set_id
, c_ENTITY_TYPE_LINE
, p_line_id_tbl(i)
, c_QUANTITY
, TO_CHAR(p_quantity_tbl(i))
, (CASE
WHEN POLL_TOTAL.qty_bill_actuals = POLL_TOTAL.qty_executed
THEN PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_BILLED_NA
ELSE PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_RCVD_NA
END
)
, (CASE
WHEN POLL_TOTAL.qty_bill_actuals = POLL_TOTAL.qty_executed
THEN PO_MESSAGE_S.c_QTY_BILLED_token
ELSE PO_MESSAGE_S.c_QTY_RCVD_token
END
)
, (CASE
WHEN POLL_TOTAL.qty_bill_actuals = POLL_TOTAL.qty_executed
THEN TO_CHAR(POLL_TOTAL.qty_bill_actuals)
ELSE TO_CHAR(POLL_TOTAL.qty_recv_actuals)
END
)
FROM
( SELECT
sum_qty_recv_actuals qty_recv_actuals,
sum_qty_bill_actuals qty_bill_actuals,
GREATEST(sum_qty_recv_actuals, sum_qty_bill_actuals) qty_executed
FROM
( SELECT
NVL(SUM(
(CASE
WHEN PLL.shipment_type <> c_STANDARD THEN 0
ELSE NVL(PLL.quantity_received, 0)
END)), 0) sum_qty_recv_actuals,
NVL(SUM(
(CASE
WHEN PLL.shipment_type <> c_STANDARD THEN 0
ELSE GREATEST(NVL(PLL.quantity_billed, 0),
NVL(PLL.quantity_financed, 0))
END)), 0) sum_qty_bill_actuals
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_line_id = p_line_id_tbl(i)
AND NVL(PLL.payment_type, c_DELIVERY) <> c_MILESTONE
)
) POLL_TOTAL, PO_LINES_ALL POL
WHERE
POL.po_line_id = p_line_id_tbl(i)
AND p_quantity_tbl(i) IS NOT NULL
-- Quantity is being reduced below the current transaction quantity:
AND p_quantity_tbl(i) < POL.quantity
AND p_quantity_tbl(i) < POLL_TOTAL.qty_executed
;
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
--PBWC Message Change Impact: Removing a token
, token1_name
, token1_value
--, token2_name
--, token2_value
)
SELECT
x_result_set_id
, c_ENTITY_TYPE_LINE
, p_line_id_tbl(i)
, c_QUANTITY
, TO_CHAR(p_quantity_tbl(i))
, PO_MESSAGE_S.PO_PO_QTY_ORD_LT_QTY_ENC_NA
--PBWC Message Change Impact: Removing a token
--, PO_MESSAGE_S.c_QTY_ORD_token
--, TO_CHAR(p_quantity_tbl(i))
, PO_MESSAGE_S.c_QTY_ENC_token
, TO_CHAR(DIST_TOTAL.quantity_encumbered)
FROM
( SELECT NVL(SUM(POD.quantity_ordered),0) quantity_encumbered
FROM
PO_DISTRIBUTIONS_ALL POD
WHERE
POD.po_line_id = p_line_id_tbl(i)
AND POD.distribution_type IN (c_STANDARD,c_PLANNED)
AND POD.encumbered_flag = 'Y'
) DIST_TOTAL
WHERE
p_quantity_tbl(i) IS NOT NULL
AND p_quantity_tbl(i) < DIST_TOTAL.quantity_encumbered
;
INSERT INTO PO_SESSION_GT
( key
, index_num1 -- po_line_id
, char1 -- currency_code
, num1 -- minimum_accountable_unit
, num2 -- precision
)
SELECT
l_gt_key
, p_line_id_tbl(i)
, p_currency_code_tbl(i)
, cur.minimum_accountable_unit
, cur.precision
FROM
fnd_currencies cur
WHERE
cur.currency_code = p_currency_code_tbl(i)
;
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
--PBWC Message Change Impact: Adding a token
, token1_name
, token1_value
)
SELECT
x_result_set_id
, c_ENTITY_TYPE_LINE
, p_line_id_tbl(i)
, c_AMOUNT
, TO_CHAR(p_amount_tbl(i))
, (CASE
WHEN POLL_TOTAL.amt_bill_actuals = POLL_TOTAL.amt_executed
THEN PO_MESSAGE_S.PO_PO_AMT_ORD_LT_AMT_BILLED_NA
ELSE PO_MESSAGE_S.PO_PO_AMT_ORD_LT_AMT_RCVD_NA
END
)
--PBWC Message Change Impact: Adding a token
, (CASE
WHEN POLL_TOTAL.amt_bill_actuals = POLL_TOTAL.amt_executed
THEN PO_MESSAGE_S.c_AMT_BILLED_TOKEN
ELSE PO_MESSAGE_S.c_AMT_RCVD_TOKEN
END
)
, (CASE
WHEN POLL_TOTAL.amt_bill_actuals = POLL_TOTAL.amt_executed
THEN TO_CHAR(POLL_TOTAL.amt_bill_actuals)
ELSE TO_CHAR(POLL_TOTAL.amt_recv_actuals)
END
)
--End PBWC Message Change Impact: Adding a token
FROM
( SELECT
sum_amt_recv_actuals amt_recv_actuals,
sum_amt_bill_actuals amt_bill_actuals,
GREATEST(sum_amt_recv_actuals, sum_amt_bill_actuals) amt_executed
FROM
( SELECT
NVL(SUM(
(CASE
WHEN PLL.shipment_type <> c_STANDARD
THEN 0
WHEN PLL.payment_type = c_RATE
THEN
CASE
WHEN gtt.num1 IS NOT NULL THEN
-- Round to minimum accountable unit.
ROUND(
NVL(PLL.quantity_received*PLL.price_override,0) / gtt.num1
) * gtt.num1
ELSE
-- Round to currency precision.
ROUND( NVL(PLL.quantity_received*PLL.price_override,0)
, gtt.num2)
END
ELSE NVL(PLL.amount_received, 0)
END)), 0) sum_amt_recv_actuals,
NVL(SUM(
(CASE
WHEN PLL.shipment_type <> c_STANDARD
THEN 0
WHEN PLL.payment_type = c_RATE
THEN
CASE
WHEN gtt.num1 IS NOT NULL THEN
-- Round to minimum accountable unit.
ROUND(
NVL(PLL.quantity_billed*PLL.price_override,0) / gtt.num1
) * gtt.num1
ELSE
-- Round to currency precision.
ROUND( NVL(PLL.quantity_billed*PLL.price_override,0)
, gtt.num2)
END
ELSE GREATEST(NVL(PLL.amount_billed, 0),
NVL(PLL.amount_financed, 0))
END)), 0) sum_amt_bill_actuals
FROM PO_LINE_LOCATIONS_ALL PLL
, PO_SESSION_GT GTT
WHERE PLL.po_line_id = p_line_id_tbl(i)
AND GTT.key = l_gt_key
AND GTT.index_num1(+) = PLL.po_line_id
)
) POLL_TOTAL
, PO_LINES_ALL POL
WHERE
POL.po_line_id = p_line_id_tbl(i)
AND p_amount_tbl(i) IS NOT NULL
-- Amount is being reduced below the current transaction amount:
AND p_amount_tbl(i) < POL.amount
AND p_amount_tbl(i) < POLL_TOTAL.amt_executed
;
INSERT INTO PO_SESSION_GT
( key
, num1
, num2
)
VALUES
( l_data_key
, p_line_id_tbl(i)
, p_amount_tbl(i)
)
;
SELECT
SES.num1
, SES.num2
BULK COLLECT INTO
l_line_id_tbl
, l_amount_tbl
FROM
PO_SESSION_GT SES
, PO_LINES_ALL LINE
, PO_HEADERS_ALL HEADER
WHERE
SES.key = l_data_key
AND LINE.po_line_id = SES.num1
AND HEADER.po_header_id = LINE.po_header_id
AND HEADER.type_lookup_code = c_STANDARD
AND LINE.order_type_lookup_code = c_RATE
AND SES.num2 < LINE.amount
;
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, result_type
, entity_type
, entity_id
, column_name
, column_val
, message_name
)
SELECT
x_result_set_id
, PO_VALIDATIONS.c_result_type_WARNING
, c_ENTITY_TYPE_LINE
, p_line_id_tbl(i)
, c_ITEM_ID
, TO_CHAR(p_item_id_tbl(i))
, PO_MESSAGE_S.PO_VMI_ASL_EXIST
FROM
PO_APPROVED_SUPPLIER_LIS_VAL_V PASL
, PO_ASL_ATTRIBUTES PAA
, PO_ASL_STATUS_RULES_V PASR
WHERE
-- item is not null
p_item_id_tbl(i) IS NOT NULL
-- Document is standard PO
AND p_type_lookup_code_tbl(i) = c_STANDARD
--VMI is enabled
AND paa.enable_vmi_flag = 'Y'
AND pasl.item_id = p_item_id_tbl(i)
AND pasl.vendor_id = p_vendor_id_tbl(i)
AND nvl(pasl.vendor_site_id,-1) = nvl(p_vendor_site_id_tbl(i),-1)
AND pasl.using_organization_id IN (p_org_id_tbl(i), -1)
AND pasl.asl_id = paa.asl_id
AND pasr.business_rule = c_2_SOURCING
AND pasr.allow_action_flag = 'Y'
AND pasr.status_id = pasl.asl_status_id
AND paa.using_organization_id =
(SELECT max(paa2.using_organization_id)
FROM po_asl_attributes paa2
WHERE paa2.asl_id = pasl.asl_id
AND paa2.using_organization_id IN (-1, p_org_id_tbl(i)));
, p_column_val_selector => NULL
, p_message_name => PO_MESSAGE_S.PO_SVC_ASSIGNMENT_DATES
, x_results => x_results
, x_result_type => x_result_type
);
, p_column_val_selector => NULL
, p_message_name => PO_MESSAGE_S.PO_IDV_LINE_ORDER_DATES
, x_results => x_results
, x_result_type => x_result_type
);
SELECT Count(po_line_id)
INTO line_count
FROM PO_LINES_MERGE_V
WHERE (draft_id= -1
OR draft_id IS NULL)
AND po_line_id = p_line_id_tbl(i);
INSERT INTO PO_SESSION_GT
( key
, num1
, num2
, char1
, char2 -- -
)
VALUES
( l_data_key
, p_line_id_tbl(i)
, p_unit_price_tbl(i)
, p_price_break_lookup_code_tbl(i)
, p_amt_changed_flag_tbl(i) -- --
)
;
SELECT
SES.num1
, SES.char1
, SES.char2 -- --
BULK COLLECT INTO
l_line_id_tbl
, l_price_break_lookup_code_tbl
, l_amount_changed_flag_tbl -- --
FROM
PO_SESSION_GT SES
, PO_LINES_ALL SAVED_LINE
WHERE
SES.key = l_data_key
AND SAVED_LINE.po_line_id = SES.num1
AND SAVED_LINE.order_type_lookup_code IN (c_QUANTITY, c_RATE)
AND ( SES.num2 <> SAVED_LINE.unit_price
OR (SES.num2 IS NULL AND SAVED_LINE.unit_price IS NOT NULL)
OR (SES.num2 IS NOT NULL AND SAVED_LINE.unit_price IS NULL)
)
;
, p_column_val_selector => PO_VALIDATION_HELPER.c_END_DATE
, p_message_name => PO_MESSAGE_S.POX_EXPIRATION_DATES
, x_results => x_results
, x_result_type => x_result_type
);
, p_column_val_selector => PO_VALIDATION_HELPER.c_START_DATE
, p_message_name => PO_MESSAGE_S.POX_EXPIRATION_DATES
, x_results => x_results
, x_result_type => x_result_type
);
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
)
SELECT
x_result_set_id
, c_ENTITY_TYPE_LINE
, p_line_id_tbl(i)
, c_QUANTITY
, TO_CHAR(p_quantity_tbl(i))
, (CASE
WHEN POLL_TOTAL.max_qty_bill = POLL_TOTAL.max_qty_executed
THEN 'CWPOTODOMESSAGE'
ELSE 'CWPOTODOMESSAGE'
END
)
FROM
( SELECT
max_qty_recv,
max_qty_bill,
GREATEST(max_qty_recv, max_qty_bill) max_qty_executed
FROM
( SELECT
NVL(MAX(quantity_received), 0) max_qty_recv,
NVL(MAX(quantity_billed), 0) max_qty_bill
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_line_id = p_line_id_tbl(i)
AND PLL.payment_type = c_MILESTONE
AND PLL.value_basis = c_QUANTITY
AND PLL.shipment_type = c_STANDARD
)
) POLL_TOTAL, PO_LINES_ALL POL
WHERE
POL.po_line_id = p_line_id_tbl(i)
AND p_quantity_tbl(i) IS NOT NULL
AND p_quantity_tbl(i) < POL.quantity
AND p_quantity_tbl(i) < POLL_TOTAL.max_qty_executed
;
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
)
SELECT
x_result_set_id
, c_ENTITY_TYPE_LINE
, p_line_id_tbl(i)
, c_UNIT_PRICE
, TO_CHAR(p_price_tbl(i))
, 'CWPOTODOMESSAGE'
FROM
( SELECT
NVL(SUM(CASE
WHEN PLL.quantity_received > 0
THEN NVL(PLL.price_override, 0)
WHEN PLL.quantity_billed > 0
THEN NVL(PLL.price_override, 0)
ELSE 0
END),0) sum_price_executed
FROM PO_LINE_LOCATIONS_ALL PLL
WHERE PLL.po_line_id = p_line_id_tbl(i)
AND PLL.payment_type = c_MILESTONE
AND PLL.value_basis = c_QUANTITY
AND PLL.shipment_type = c_STANDARD
) POLL_TOTAL, PO_LINES_ALL POL
WHERE
POL.po_line_id = p_line_id_tbl(i)
AND p_price_tbl(i) IS NOT NULL
AND p_price_tbl(i) < POL.unit_price
AND p_price_tbl(i) < POLL_TOTAL.sum_price_executed
;
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, message_name
)
SELECT
x_result_set_id
, c_ENTITY_TYPE_LINE
, l_id_tbl(i)
, c_ITEM_ID
, p_message_name
FROM
PO_LINE_TYPES_B PLT
WHERE
PLT.line_type_id = l_line_type_id_tbl(i)
AND PLT.outside_operation_flag = 'Y'
;
select inventory_organization_id
into l_def_inv_org_id
from financials_system_parameters;
select inventory_organization_id
into l_def_inv_org_id
from financials_system_parameters;
SELECT type_lookup_code
INTO l_src_doc_type_lookup_code
FROM po_headers_all
WHERE po_header_id = l_from_header_id;
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
, token1_name
, token1_value
, token2_name
, token2_value
)
SELECT
x_result_set_id
, c_ENTITY_TYPE_LINE
, p_line_id_tbl(i)
, c_MAX_RETAINAGE_AMOUNT
, TO_CHAR(p_max_retain_amt_tbl(i))
, PO_MESSAGE_S.PO_MAX_RET_AMT_GE_RETAINED
, PO_MESSAGE_S.c_MAX_RET_AMT_token
, TO_CHAR(p_max_retain_amt_tbl(i))
, PO_MESSAGE_S.c_AMT_RETAINED_token
, TO_CHAR(LOCATIONS_TOTAL.amount_retained)
FROM
( SELECT NVL(SUM(POLL.retainage_withheld_amount),0) amount_retained
FROM
PO_LINE_LOCATIONS_ALL POLL
WHERE
POLL.po_line_id = p_line_id_tbl(i)
) LOCATIONS_TOTAL
WHERE
p_max_retain_amt_tbl(i) < LOCATIONS_TOTAL.amount_retained
;
SELECT CLM_OPTION_FROM_DATE
INTO document_option_date
FROM PO_LINES_MERGE_V
WHERE (draft_id = -1
OR draft_id IS NULL)
AND po_line_id = p_line_id_tbl(i);
SELECT 1 INTO l_excep FROM dual
WHERE p_line_num_disp_tbl(i) LIKE p_clm_exhibit_name_tbl(i)||'%';
INSERT INTO po_validation_results_gt
(result_set_id,
entity_type,
entity_id,
message_name,
column_name,
column_val)
SELECT x_result_set_id,
c_entity_type_LINE
,p_line_id_tbl(i)
,PO_MESSAGE_S.PO_MOD_CANCEL_INVALID
,c_CONTROL_ACTION
,TO_CHAR(p_control_action_tbl(i))
FROM DUAL
WHERE nvl(p_control_action_tbl(i), '*') = 'CANCEL'
AND not exists
(SELECT 'Y'
FROM PO_LINE_LOCATIONS_ALL
WHERE po_line_id = p_line_id_tbl(i)
AND quantity > quantity_received);
INSERT INTO PO_SESSION_GT
( key
, index_num1
, index_num2
, num1
, char1
)
SELECT distinct
l_data_key
, LINE.po_header_id
, LINE.po_line_id
, LINE.group_line_id
, LINE.line_num_display
FROM PO_LINES_MERGE_V LINE
WHERE LINE.po_header_id =l_parent_id_tbl(i)
AND LINE.draft_id <> p_draft_id_tbl(i)
AND LINE.draft_id NOT IN (SELECT * FROM TABLE(l_par_draft_id_tbl))
AND Nvl(LINE.mod_line_id, -999) <> p_line_id_tbl(i); --
WHEN MATCHED THEN UPDATE SET
SES.num1 = p_group_line_id_tbl(i),
SES.char1 = p_line_num_display_tbl(i),
SES.index_char2 = c_NEW
WHEN NOT MATCHED THEN
INSERT
( key
, index_num1
, index_num2
, num1
, char1
, index_char2
)
VALUES
( l_data_key
, p_header_id_tbl(i)
, p_line_id_tbl(i)
, p_group_line_id_tbl(i)
, p_line_num_display_tbl(i)
, c_NEW
);
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
)
SELECT
x_result_set_id
, c_entity_type_LINE
, CHILD.index_num2
, c_LINE_NUM_DISP
, CHILD.char1
, PO_MESSAGE_S.PO_PO_ENTER_UNIQUE_LINE_NUM
FROM
PO_SESSION_GT CHILD
WHERE
CHILD.key = l_data_key
AND CHILD.index_char2 = c_NEW -- check only new and modified records
AND EXISTS
( SELECT null
FROM PO_SESSION_GT SIBLING
WHERE
SIBLING.key = l_data_key
AND SIBLING.index_num1 = CHILD.index_num1 -- header_id
AND SIBLING.char1 = CHILD.char1 -- line num disp
AND SIBLING.index_num2 <> CHILD.index_num2 -- line_id
);
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
)
SELECT
x_result_set_id
, c_entity_type_LINE
, SLIN.index_num2
, c_LINE_NUM_DISP
, SLIN.char1
, 'PO_CLIN_SLIN_NUM_NOT_SYNC'
FROM
PO_SESSION_GT CLIN,
PO_SESSION_GT SLIN
WHERE
CLIN.key = l_data_key
AND SLIN.key = l_data_key
AND CLIN.num1 IS NULL -- CLIN.group_line_id
AND CLIN.index_num2 = SLIN.num1 -- CLIN.po_line_id = SLIN.group_line_id
AND CLIN.char1 <> SUBSTR(SLIN.char1,0,4) -- line_num_display
AND CLIN.index_char2 = c_NEW; -- check only new and modified CLINs
SELECT Count(interface_line_id)
INTO l_line_count
FROM po_lines_interface pclin
WHERE pclin.interface_line_id = p_intf_line_id_tbl(i)
AND pclin.group_line_id IS NULL /*clin*/
AND Nvl(pclin.clm_info_flag,'N') = 'N' /*priced clin*/
--AND Nvl(PLI.draft_id,-1) = Nvl(p_draft_id_tbl(i),-1)
AND EXISTS ( --Priced SLIN
SELECT interface_line_id
FROM po_lines_interface
WHERE interface_header_id = pclin.interface_header_id
AND group_line_id IS NOT NULL
AND Nvl(clm_info_flag,'N') = 'N'
AND group_line_id = pclin.interface_line_id
--AND Nvl(draft_id,-1) = Nvl(pclin.draft_id,-1)
);
SELECT 'Y' INTO is_contract_type_valid
FROM dual
WHERE EXISTS ( SELECT 1 FROM po_lookup_codes
WHERE lookup_type = Decode(p_order_type_lookup_code_tbl(i),
'FIXED PRICE','PO_FEDERAL_CONTRACT_TYPES_AMT',
'AMOUNT','PO_FEDERAL_CONTRACT_TYPES_AMT',
'QUANTITY','PO_FEDERAL_CONTRACT_TYPES_QTY',
'*')
AND lookup_code = p_contract_type_tbl(i)
);
SELECT 'Y' INTO is_cost_constraint_valid
FROM dual
WHERE EXISTS ( SELECT 1 FROM po_lookup_codes
WHERE lookup_type = 'PO_FEDERAL_COST_CONSTRAINTS'
AND lookup_code = p_cost_constraint_tbl(i)
);
SELECT 'Y' INTO is_contract_valid
FROM dual
WHERE EXISTS ( SELECT 1 FROM okc_k_headers_b
WHERE id = p_oke_contract_header_id_tbl(i)
);
SELECT 'Y' INTO is_contract_valid
FROM dual
WHERE EXISTS ( SELECT major_version FROM oke_k_vers_numbers_v
WHERE chr_id = p_oke_contract_header_id_tbl(i)
AND major_version = p_oke_contract_version_id_tbl(i)
UNION
SELECT major_version FROM okc_k_vers_numbers_h
WHERE chr_id = p_oke_contract_header_id_tbl(i)
AND major_version = p_oke_contract_version_id_tbl(i)
);
SELECT interface_header_id INTO l_intf_header_id
FROM po_lines_interface
WHERE interface_line_id = p_intf_line_id_tbl(i);
SELECT Nvl(clm_info_flag,'N'),
Nvl(clm_option_indicator,'*')
INTO l_clm_info_flag,
l_clm_option_indicator
FROM po_lines_interface
WHERE interface_line_id = p_clm_base_line_num_tbl(i)
AND interface_header_id = l_intf_header_id;
SELECT Count(interface_line_id)
INTO l_dup_option_num_count
FROM po_lines_interface
WHERE clm_base_line_num = Nvl(p_clm_base_line_num_tbl(i),-1)
AND clm_option_num = p_clm_option_num_tbl(i);
SELECT 'Y' INTO is_valid_idc_type
FROM dual
WHERE EXISTS ( SELECT 1 FROM PO_LOOKUP_CODES
WHERE LOOKUP_TYPE = 'PO_FEDERAL_IDC_TYPES'
AND lookup_code = p_clm_idc_type_tbl(i)
);
INSERT INTO PO_SESSION_GT(
KEY,
num1)
SELECT
l_data_key,
p_intf_line_id_tbl(i)
FROM po_headers_draft_all phd, po_headers_interface phi, po_lines_interface pli
WHERE phd.po_header_id = phi.po_header_id
AND phd.type_lookup_code = 'STANDARD'
AND phi.interface_header_id = PLI.interface_header_id
AND PLI.interface_line_id = p_intf_line_id_tbl(i)
AND Nvl(phd.clm_source_document_id,-1) <> Nvl(p_from_header_id_tbl(i),-1);
DELETE FROM po_session_gt
WHERE key = l_data_key
RETURNING num1 BULK COLLECT INTO l_intf_line_id_tbl;
INSERT INTO PO_SESSION_GT(
KEY,
num1)
SELECT
l_data_key,
p_intf_line_id_tbl(i)
FROM po_line_types
WHERE p_line_type_id_tbl(i) IS NOT NULL
AND line_type_id = p_line_type_id_tbl(i)
AND order_type_lookup_code = 'RATE'
AND purchase_basis = 'TEMP LABOR';
DELETE FROM po_session_gt
WHERE key = l_data_key
RETURNING num1 BULK COLLECT INTO l_intf_line_id_tbl;
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, column_name
, column_val
, message_name
)
SELECT
x_result_set_id
, c_ENTITY_TYPE_LINE
, p_line_id_tbl(i)
, c_CLM_EXHIBIT_NAME
, TO_CHAR(p_exhibit_name_tbl(i))
, PO_MESSAGE_S.PO_INVALID_EXHIBIT_NAME
FROM PO_LINES_MERGE_V POL
WHERE NOT EXISTS (SELECT 1
FROM FND_LOOKUP_VALUES LK
WHERE LOOKUP_TYPE = 'PO_CLM_EXHIBIT_NUMBER'
AND ENABLED_FLAG = 'Y'
AND LANGUAGE = FND_GLOBAL.CURRENT_LANGUAGE
AND START_DATE_ACTIVE <= SYSDATE
AND (END_DATE_ACTIVE IS NULL OR END_DATE_ACTIVE >=SYSDATE )
AND NOT EXISTS ( SELECT 1 FROM PO_EXHIBIT_DETAILS_MERGE_V POE
WHERE NVL(POE.IS_CDRL,'N') = 'Y'
AND POE.PO_HEADER_ID = p_header_id_tbl(i)
AND POE.DRAFT_ID = p_draft_id_tbl(i)
AND LK.LOOKUP_CODE = POE.EXHIBIT_NAME)
AND LK.LOOKUP_CODE = p_exhibit_name_tbl(i)
)
AND p_exhibit_name_tbl(i) IS NOT NULL
AND POL.PO_LINE_ID =p_line_id_tbl(i)
AND POL.DRAFT_ID =p_draft_id_tbl(i) ;
SELECT undef_amt,
greatest(changed_ext_price,0) changed_ext_price,
po_line_uca_id,
draft_id
bulk collect INTO l_undef_amt_tbl, l_extended_price_tbl, l_po_line_uca_id_tbl,
l_po_draft_id_tbl
FROM (SELECT undef.undef_amt,
uca_ids.po_line_uca_id,
po_line_ids.po_line_id
FROM (SELECT ROWNUM rn,
column_value undef_amt
FROM TABLE(p_undef_amount)) undef,
(SELECT ROWNUM rn,
column_value po_line_uca_id
FROM TABLE(p_po_line_uca_id)) uca_ids,
(SELECT ROWNUM rn,
column_value po_line_id
FROM TABLE(p_ucas_po_line_id)) po_line_ids
WHERE uca_ids.rn = undef.rn
AND po_line_ids.rn = undef.rn) ucas,
(SELECT po_line_ids.po_line_id,
otlc.order_type_lookup_code,
podraftid.draft_id,
( CASE
WHEN podraftid.draft_id = -1 THEN Nvl((
qtys.quantity * ups.unit_price ), 0)
ELSE Nvl(( qtys.quantity * ups.unit_price ), 0) - Nvl((
oqtys.old_quantity * oups.old_unit_price ), 0)
END ) changed_ext_price
FROM (SELECT ROWNUM rn,
column_value po_line_id
FROM TABLE(p_po_line_id)) po_line_ids,
(SELECT ROWNUM rn,
column_value order_type_lookup_code
FROM TABLE(p_order_type_lookup_code)) otlc,
(SELECT ROWNUM rn,
column_value unit_price
FROM TABLE(p_unit_price)) ups,
(SELECT ROWNUM rn,
column_value quantity
FROM TABLE(p_quantity)) qtys,
(SELECT ROWNUM rn,
column_value old_quantity
FROM TABLE(p_old_quantity)) oqtys,
(SELECT ROWNUM rn,
column_value old_unit_price
FROM TABLE(p_old_unit_price)) oups,
(SELECT ROWNUM rn,
column_value draft_id
FROM TABLE(p_draft_id)) podraftid
WHERE otlc.rn = po_line_ids.rn
AND qtys.rn = otlc.rn
AND oqtys.rn = otlc.rn
AND oups.rn = otlc.rn
AND ups.rn = otlc.rn
AND podraftid.rn = otlc.rn)polines
WHERE ucas.po_line_id = polines.po_line_id
AND order_type_lookup_code = 'QUANTITY';