[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
c_PRICE_UPDATE_TOLERANCE CONSTANT VARCHAR2(30) := 'PRICE_UPDATE_TOLERANCE';
D_price_update_tol_ge_zero CONSTANT VARCHAR2(100) :=
PO_LOG.get_subprogram_base(D_PACKAGE_BASE,'price_update_tol_ge_zero');
PROCEDURE price_update_tol_ge_zero(
p_header_id_tbl IN PO_TBL_NUMBER
, p_price_update_tol_tbl IN PO_TBL_NUMBER
, x_results IN OUT NOCOPY PO_VALIDATION_RESULTS_TYPE
, x_result_type OUT NOCOPY VARCHAR2
)
IS
BEGIN
PO_VALIDATION_HELPER.greater_or_equal_zero(
p_calling_module => D_price_update_tol_ge_zero
, p_null_allowed_flag => PO_CORE_S.g_parameter_YES
, p_value_tbl => p_price_update_tol_tbl
, p_entity_id_tbl => p_header_id_tbl
, p_entity_type => c_entity_type_HEADER
, p_column_name => c_PRICE_UPDATE_TOLERANCE
, p_message_name => PO_MESSAGE_S.PO_ALL_ENTER_VALUE_GE_ZERO
, x_results => x_results
, x_result_type => x_result_type
);
END price_update_tol_ge_zero;
SELECT Nvl(clm_flag,'N')
INTO l_is_clm_po
FROM po_doc_style_headers
WHERE style_id = p_style_id_tbl(p_style_id_tbl.COUNT);
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_HEADER
, p_header_id_tbl(i)
, c_VENDOR_ID
, TO_CHAR(p_vendor_id_tbl(i))
, PO_MESSAGE_S.PO_PO_VENDOR_ON_HOLD
FROM
PO_VENDORS SUPPLIER
WHERE
SUPPLIER.vendor_id = p_vendor_id_tbl(i)
AND SUPPLIER.hold_flag = 'Y'
;
SELECT
BOOKS.currency_code
INTO
l_func_currency_code
FROM
FINANCIALS_SYSTEM_PARAMS_ALL FIN_PARAMS
, GL_SETS_OF_BOOKS BOOKS
WHERE
FIN_PARAMS.org_id = p_org_id_tbl(i)
AND BOOKS.set_of_books_id = FIN_PARAMS.set_of_books_id
;
, p_column_val_selector => PO_VALIDATION_HELPER.c_END_DATE
, p_message_name => l_message_name
, 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_HEADER
, p_header_id_tbl(i)
, c_START_DATE
, TO_CHAR(p_start_date_tbl(i))
, PO_MESSAGE_S.PO_CONTRACT_ST_LT_REF_CR
FROM DUAL
WHERE p_type_lookup_code_tbl(i) IN (c_CONTRACT,c_BLANKET)
AND EXISTS
(
SELECT NULL
FROM PO_LINES_ALL ORDER_LINE
WHERE ORDER_LINE.from_header_id = p_header_id_tbl(i)
AND p_start_date_tbl(i) > ORDER_LINE.creation_date
);
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_HEADER
, p_header_id_tbl(i)
, c_START_DATE
, TO_CHAR(p_start_date_tbl(i))
, PO_MESSAGE_S.PO_IDV_EFF_DT_LT_ORD_ST_DT_CLM
FROM DUAL
WHERE p_type_lookup_code_tbl(i) IN (c_CONTRACT,c_BLANKET)
AND p_start_date_tbl(i) < p_clm_effective_date_tbl(i);
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_HEADER
, p_header_id_tbl(i)
, c_AMOUNT_LIMIT
, TO_CHAR(p_amount_limit_tbl(i))
, PO_MESSAGE_S.PO_IDV_MX_CL_AMT_GT_RLSD_CLM
FROM DUAL
WHERE p_type_lookup_code_tbl(i) IN (c_CONTRACT,c_BLANKET)
AND p_amount_limit_tbl(i) < p_min_grnt_awd_amt_tbl(i);
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_HEADER
, p_header_id_tbl(i)
, c_AMOUNT_LIMIT
, TO_CHAR(p_amount_limit_tbl(i))
, PO_MESSAGE_S.PO_IDV_MX_CL_AMT_GT_MNPM_CLM
FROM DUAL
WHERE p_type_lookup_code_tbl(i) IN (c_CONTRACT,c_BLANKET)
AND p_amount_limit_tbl(i) < p_min_order_amt_tbl(i);
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_HEADER
, p_header_id_tbl(i)
, c_AMOUNT_LIMIT
, TO_CHAR(p_amount_limit_tbl(i))
, PO_MESSAGE_S.PO_IDV_MX_CL_AMT_GT_MXPM_CLM
FROM DUAL
WHERE p_type_lookup_code_tbl(i) IN (c_CONTRACT,c_BLANKET)
AND p_amount_limit_tbl(i) < p_max_order_amt_tbl(i);
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_HEADER
, p_header_id_tbl(i)
, c_CLM_MAX_ORD_AMT
, TO_CHAR(p_max_order_amt_tbl(i))
, PO_MESSAGE_S.PO_IDV_MX_OD_AMT_GT_MN_OD_CLM
FROM DUAL
WHERE p_type_lookup_code_tbl(i) IN (c_CONTRACT,c_BLANKET)
AND p_max_order_amt_tbl(i) < p_min_order_amt_tbl(i);
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_HEADER
, p_header_id_tbl(i)
, decode(nvl(p_document_format_tbl(i), '*'), '*', c_CLM_DOCUMENT_FORMAT, c_CLM_STANDARD_FORM)
, null
, PO_MESSAGE_S.PO_PRINT_FORM_FORMAT_INVALID
FROM PO_DOC_STYLE_HEADERS PDSH
WHERE PDSH.style_id = p_style_id_tbl(i)
AND nvl(clm_flag, 'N') ='Y'
AND NOT EXISTS (
SELECT 1
FROM po_print_form_formats ppff
WHERE ppff.document_type = decode(nvl(p_draft_type_tbl(i), '*'),
'MOD', 'PO_MOD_STD_FORM', -- bug 10389450 start
decode(p_type_lookup_code_tbl(i), c_STANDARD,
decode(p_clm_award_type_tbl(i),
'BPA_CALL', 'PO_DEL_ORD_STD_FORM',
'DELIVERY_ORD', 'PO_DEL_ORD_STD_FORM',
'PO_AWARD_STD_FORM'),
'PO_IDV_STD_FORM')) -- bug 10389450 end
AND ppff.standard_form = p_clm_standard_form_tbl(i)
AND ppff.document_format = p_document_format_tbl(i)
AND trunc(sysdate) <= trunc(nvl(ppff.inactive_date, sysdate +1))
)
AND Nvl(p_draft_type_tbl(i),'*') <> 'PAR'; --
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_HEADER
, p_header_id_tbl(i)
, c_END_DATE
, TO_CHAR(p_end_date_tbl(i))
, PO_MESSAGE_S.PO_CONTRACT_ST_GT_REF_CR
FROM DUAL
WHERE
p_type_lookup_code_tbl(i) IN (c_CONTRACT,c_BLANKET)
AND EXISTS
( SELECT NULL
FROM
PO_LINES_ALL ORDER_LINE
, PO_HEADERS_ALL ORDER_HEADER
WHERE
ORDER_LINE.from_header_id = p_header_id_tbl(i)
-- Bug # 13550798 Changed logic based on approved_date
--AND p_end_date_tbl(i) < TRUNC(ORDER_LINE.creation_date)
AND ORDER_HEADER.po_header_id = ORDER_LINE.po_header_id
AND ORDER_HEADER.approved_date IS NOT NULL
AND TRUNC(p_end_date_tbl(i)+ nvl(FND_PROFILE.VALUE('PO_REL_CREATE_TOLERANCE'),0)) < TRUNC(ORDER_HEADER.approved_date)
AND NVL(ORDER_HEADER.closed_code,'X') <> c_FINALLY_CLOSED
AND NVL(ORDER_HEADER.cancel_flag,'N') <> 'Y'
)
;
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_HEADER
, p_header_id_tbl(i)
, c_START_DATE
, TO_CHAR(p_start_date_tbl(i))
, PO_MESSAGE_S.PO_CONTRACT_ST_LT_REF_CR
FROM DUAL
WHERE
p_type_lookup_code_tbl(i) = c_CONTRACT
AND EXISTS
( SELECT NULL
FROM
PO_LINES_ALL ORDER_LINE
, PO_HEADERS_ALL ORDER_HEADER
WHERE
ORDER_LINE.contract_id = p_header_id_tbl(i)
AND TRUNC(p_start_date_tbl(i)) > ORDER_LINE.creation_date
AND ORDER_HEADER.po_header_id = ORDER_LINE.po_header_id
AND ORDER_HEADER.approved_date IS NOT NULL
AND NVL(ORDER_HEADER.closed_code,'X') <> c_FINALLY_CLOSED
AND NVL(ORDER_HEADER.cancel_flag,'N') <> 'Y'
)
;
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_HEADER
, p_header_id_tbl(i)
, c_END_DATE
, TO_CHAR(p_end_date_tbl(i))
, PO_MESSAGE_S.PO_CONTRACT_ST_GT_REF_CR
FROM DUAL
WHERE
p_type_lookup_code_tbl(i) = c_CONTRACT
AND EXISTS
( SELECT NULL
FROM
PO_LINES_ALL ORDER_LINE
, PO_HEADERS_ALL ORDER_HEADER
WHERE
ORDER_LINE.contract_id = p_header_id_tbl(i)
-- Bug # 13550798 Changed logic based on approved_date
-- AND p_end_date_tbl(i) < TRUNC(ORDER_LINE.creation_date)
AND ORDER_HEADER.po_header_id = ORDER_LINE.po_header_id
AND ORDER_HEADER.approved_date IS NOT NULL
AND TRUNC(p_end_date_tbl(i)+ nvl(FND_PROFILE.VALUE('PO_REL_CREATE_TOLERANCE'),0)) < TRUNC(ORDER_HEADER.approved_date)
AND NVL(ORDER_HEADER.closed_code,'X') <> c_FINALLY_CLOSED
AND NVL(ORDER_HEADER.cancel_flag,'N') <> 'Y'
)
;
INSERT INTO PO_SESSION_GT
( key
, num1
, num2
, num3
, char1
)
VALUES
( l_data_key
, p_header_id_tbl(i)
, p_org_id_tbl(i)
, p_style_id_tbl(i)
, p_segment1_tbl(i)
)
;
SELECT
num1
, char1
BULK COLLECT INTO
l_header_id_tbl
, l_segment1_tbl
FROM
PO_SESSION_GT SES
, PO_SYSTEM_PARAMETERS_ALL PARAMS
, PO_DOC_STYLE_HEADERS PDSH
WHERE
SES.key = l_data_key
AND SES.num2 = PARAMS.org_id
AND PARAMS.manual_po_num_type = c_NUMERIC
AND PDSH.style_id = SES.num3
AND NVL(PDSH.clm_flag,'N') <> 'Y'
;
We are selecting the segment1 from po_headers_all using po_header_id and segment1.
If it finds any record then we need not do the document numbering validation as it
is already created and we are updating now. If there is no such record in
po_headers_all then l_temp_segment value will be null and validation will be fired.
*/
BEGIN
SELECT SEGMENT1 INTO L_TEMP_SEGMENT
FROM PO_HEADERS_ALL
WHERE
SEGMENT1=l_segment1_tbl(i)
AND
PO_HEADER_ID=l_header_id_tbl(i);
INSERT INTO PO_SESSION_GT
( key
, num1
, num2
, index_char1
, char2
)
SELECT
l_data_key
, p_header_id_tbl(i)
, p_org_id_tbl(i)
, p_segment1_tbl(i)
, p_type_lookup_code_tbl(i)
FROM
PO_SYSTEM_PARAMETERS_ALL PARAMS
WHERE
PARAMS.org_id = p_org_id_tbl(i)
AND PARAMS.user_defined_po_num_code = c_MANUAL
AND p_segment1_tbl(i) IS NOT NULL
AND NOT EXISTS
( SELECT NULL
FROM PO_HEADERS_ALL SAVED_HEADER
WHERE SAVED_HEADER.po_header_id = p_header_id_tbl(i)
)
;
UPDATE PO_SESSION_GT SES
SET index_char2 = 'X'
WHERE
SES.key = l_data_key
AND
(
-- Check for currently existing documents.
EXISTS
( SELECT NULL
FROM PO_HEADERS_ALL HEADER
WHERE
HEADER.org_id = SES.num2
AND HEADER.segment1 = SES.index_char1
AND HEADER.type_lookup_code IN (c_STANDARD,c_PLANNED,c_CONTRACT,c_BLANKET)
AND HEADER.po_header_id <> SES.num1
)
OR
-- Check for previously purged documents.
EXISTS
( SELECT NULL
FROM PO_HISTORY_POS_ALL DELETED_HEADER
WHERE
DELETED_HEADER.org_id = SES.num2
AND DELETED_HEADER.segment1 = SES.index_char1
AND DELETED_HEADER.type_lookup_code IN (c_STANDARD,c_PLANNED,c_CONTRACT,c_BLANKET)
)
OR
-- Check for other in-memory documents.
EXISTS
( SELECT NULL
FROM PO_SESSION_GT UNSAVED_DATA
WHERE
UNSAVED_DATA.key = l_data_key
AND UNSAVED_DATA.num2 = SES.num2 -- org_id
AND UNSAVED_DATA.index_char1 = SES.index_char1 -- segment1
AND UNSAVED_DATA.num1 <> SES.num1 -- po_header_id
)
)
;
SELECT
SES.num1
, SES.num2
, SES.index_char1
, SES.index_char2
BULK COLLECT INTO
l_header_id_tbl
, l_org_id_tbl
, l_segment1_tbl
, l_nonunique_tbl
FROM
PO_SESSION_GT SES
WHERE
SES.key = l_data_key
AND
( SES.index_char2 = 'X'
OR
(
l_check_sourcing_flag = 'I'
AND SES.char2 IN (c_STANDARD,c_BLANKET)
)
)
;
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( online_report_id
, last_update_login
, last_updated_by
, last_update_date
, created_by
, creation_date
, line_num
, shipment_num
, distribution_num
, sequence
, message_name
, text_line
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, LINE.line_num
, 0
, 0
, x_sequence + rownum
, l_message_name
, SUBSTR(l_linemsg || c_delim || TO_CHAR(LINE.line_num) || c_delim
|| l_text,1,240)
FROM
PO_LINES_GT LINE
, PO_HEADERS_GT HEADER
WHERE
LINE.expiration_date < HEADER.start_date
OR LINE.expiration_date > HEADER.end_date
;
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( online_report_id
, last_update_login
, last_updated_by
, last_update_date
, created_by
, creation_date
, line_num
, shipment_num
, distribution_num
, sequence
, message_name
, text_line
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, LINE.line_num
, PRICE_BREAK.shipment_num
, 0
, x_sequence + rownum
, l_message_name
, SUBSTR(l_linemsg || c_delim || TO_CHAR(LINE.line_num) || c_delim
|| TO_CHAR(PRICE_BREAK.shipment_num) -- TODO: Need token from PM
|| l_text,1,240)
FROM
PO_LINE_LOCATIONS_ALL PRICE_BREAK
, PO_LINES_GT LINE
, PO_HEADERS_GT HEADER
WHERE
PRICE_BREAK.po_line_id = LINE.po_line_id
AND PRICE_BREAK.start_date > HEADER.end_date
;
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( online_report_id
, last_update_login
, last_updated_by
, last_update_date
, created_by
, creation_date
, line_num
, shipment_num
, distribution_num
, sequence
, message_name
, text_line
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, LINE.line_num
, PRICE_BREAK.shipment_num
, 0
, x_sequence + rownum
, l_message_name
, SUBSTR(l_linemsg || c_delim || TO_CHAR(LINE.line_num) || c_delim
|| TO_CHAR(PRICE_BREAK.shipment_num) -- TODO: Need token from PM
|| l_text,1,240)
FROM
PO_LINE_LOCATIONS_ALL PRICE_BREAK
, PO_LINES_GT LINE
, PO_HEADERS_GT HEADER
WHERE
PRICE_BREAK.po_line_id = LINE.po_line_id
AND PRICE_BREAK.start_date < HEADER.start_date
;
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( online_report_id
, last_update_login
, last_updated_by
, last_update_date
, created_by
, creation_date
, line_num
, shipment_num
, distribution_num
, sequence
, message_name
, text_line
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, LINE.line_num
, PRICE_BREAK.shipment_num
, 0
, x_sequence + rownum
, l_message_name
, SUBSTR(l_linemsg || c_delim || TO_CHAR(LINE.line_num) || c_delim
|| TO_CHAR(PRICE_BREAK.shipment_num) -- TODO: Need token from PM
|| l_text,1,240)
FROM
PO_LINE_LOCATIONS_ALL PRICE_BREAK
, PO_LINES_GT LINE
, PO_HEADERS_GT HEADER
WHERE
PRICE_BREAK.po_line_id = LINE.po_line_id
AND PRICE_BREAK.end_date > HEADER.end_date
;
INSERT INTO PO_ONLINE_REPORT_TEXT_GT
( online_report_id
, last_update_login
, last_updated_by
, last_update_date
, created_by
, creation_date
, line_num
, shipment_num
, distribution_num
, sequence
, message_name
, text_line
)
SELECT
p_online_report_id
, p_login_id
, p_user_id
, SYSDATE
, p_user_id
, SYSDATE
, LINE.line_num
, PRICE_BREAK.shipment_num
, 0
, x_sequence + rownum
, l_message_name
, SUBSTR(l_linemsg || c_delim || TO_CHAR(LINE.line_num) || c_delim
|| TO_CHAR(PRICE_BREAK.shipment_num) -- TODO: Need token from PM
|| l_text,1,240)
FROM
PO_LINE_LOCATIONS_ALL PRICE_BREAK
, PO_LINES_GT LINE
, PO_HEADERS_GT HEADER
WHERE
PRICE_BREAK.po_line_id = LINE.po_line_id
AND PRICE_BREAK.end_date < HEADER.start_date
;
INSERT INTO PO_VALIDATION_RESULTS_GT
( result_set_id
, entity_type
, entity_id
, message_name
, column_name
)
SELECT
x_result_set_id
, c_entity_type_HEADER
, p_header_id_tbl(i)
, PO_MESSAGE_S.PO_ALL_NOT_NULL
, c_SEGMENT1
FROM
PO_SYSTEM_PARAMETERS_ALL
WHERE
org_id = p_org_id_tbl(i)
AND USER_DEFINED_PO_NUM_CODE = c_MANUAL
AND p_segment1_tbl(i) IS NULL;
INSERT INTO po_validation_results_gt
(result_set_id,
result_type,
entity_type,
message_name,
column_name,
column_val,
token1_name,
token1_value,
validation_id)
SELECT x_result_set_id,
po_validations.c_result_type_failure,
c_entity_type_header,
'PO_PDOI_INVALID_FREIGHT_CARR',
'SHIP_VIA_LOOKUP_CODE',
p_ship_via_lookup_code_tbl(i),
'VALUE',
p_ship_via_lookup_code_tbl(i),
PO_VAL_CONSTANTS.c_ship_via_lookup_code
FROM dual
WHERE NOT EXISTS (SELECT 1 FROM org_freight ofr
WHERE p_ship_via_lookup_code_tbl(i) = ofr.freight_code
AND SYSDATE < NVL(ofr.disable_date, SYSDATE + 1)
--Bug 12409257 start
--AND p_org_id_tbl(i)=ofr.organization_id);
--AND ofr.organization_id in (SELECT inventory_organization_id
-- FROM hr_locations_v
-- WHERE ship_to_location_id = --p_ship_to_location_id_tbl(i)
-- AND ship_to_site_flag = 'Y'));
AND ofr.organization_id in (SELECT inventory_organization_id
FROM financials_system_params_all
WHERE org_id = p_org_id_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_HEADER
,p_header_id_tbl(i)
,PO_MESSAGE_S.PO_MOD_EFFECT_DT_INVALID
,c_MOD_EFFECTIVE_DATE
,TO_CHAR(p_mod_eff_date_tbl(i))
FROM DUAL
WHERE p_mod_eff_date_tbl(i) is not NULL
AND p_mod_eff_date_tbl(i) > (
SELECT min(nvl(need_by_date, p_mod_eff_date_tbl(i)+1))
FROM PO_LINE_LOCATIONS_ALL
WHERE po_header_id = p_header_id_tbl(i));
INSERT INTO po_validation_results_gt
(result_set_id,
entity_type,
entity_id,
message_name,
column_name)
SELECT x_result_set_id,
c_entity_type_HEADER
,p_header_id_tbl(i)
,PO_MESSAGE_S.PO_MOD_EFFECT_DT_NOTNULL
,c_MOD_EFFECTIVE_DATE
FROM DUAL
WHERE p_mod_eff_date_tbl(i) is NULL
AND nvl(p_draft_type_tbl(i), '*') = 'MOD';
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_HEADER
,p_header_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_header_id = p_header_id_tbl(i)
AND quantity > quantity_received);
SELECT 'Y' INTO l_valid_standard_form
FROM dual
WHERE p_clm_standard_form_tbl(i) IN ( SELECT DISTINCT standard_form
FROM PO_PRINT_FORM_FORMATS
WHERE style_id = p_style_id_tbl(i)
AND document_type = Decode (PO_PDOI_PARAMS.g_request.document_type,
PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD,'PO_AWARD_STD_FORM')
AND inactive_date IS NULL
);
SELECT 'Y' INTO l_valid_document_format
FROM dual
WHERE p_clm_document_format_tbl(i) IN ( SELECT document_format
FROM PO_PRINT_FORM_FORMATS
WHERE style_id = p_style_id_tbl(i)
AND standard_form = p_clm_standard_form_tbl(i)
AND document_type = Decode (PO_PDOI_PARAMS.g_request.document_type,
PO_PDOI_CONSTANTS.g_DOC_TYPE_STANDARD,'PO_AWARD_STD_FORM')
AND inactive_date IS NULL
);
SELECT 'Y' INTO is_valid_src_doc_id
FROM dual
WHERE EXISTS (
select poh.po_header_id
FROM po_headers_all POH,
hr_all_organization_units_tl HOUTL,
po_doc_style_lines_vl PDSL,
po_document_types_all_tl PDTL,
po_doc_style_headers PDSH
WHERE HOUTL.organization_id = POH.org_id
AND HOUTL.language = USERENV('LANG')
AND PDSL.document_subtype(+) = POH.type_lookup_code
AND PDSL.style_id(+) = POH.style_id
AND ((POH.type_lookup_code = 'QUOTATION'
AND PDTL.document_type_code = POH.type_lookup_code
AND PDTL.document_subtype = POH.quote_type_lookup_code
)
OR
(PDTL.document_type_code IN ('PO','PA')
AND PDTL.document_subtype = POH.type_lookup_code
)
)
AND PDTL.org_id = POH.org_id
AND PDTL.language = USERENV('LANG')
AND ( POH.currency_code = p_currency_code_tbl(i)
or (POH.type_lookup_code = 'CONTRACT' and POH.org_id = p_org_id_tbl(i))
)
AND ( (POH.type_lookup_code IN ('BLANKET', 'CONTRACT')
AND NVL(POH.closed_code, 'OPEN') = 'OPEN'
AND NVL(POH.cancel_flag, 'N') = 'N'
AND NVL(POH.frozen_flag,'N') = 'N'
AND NVL(POH.user_hold_flag, 'N') = 'N'
AND ((POH.type_lookup_code = 'CONTRACT'
AND NVL(FND_PROFILE.VALUE('ALLOW_REFERENCING_CPA_UNDER_AMENDMENT'),'N') = 'Y'
AND POH.approved_date is not null)
OR
POH.authorization_status = 'APPROVED'
)
))
AND POH.STYLE_ID = PDSH.STYLE_ID
AND Nvl(PDSH.CLM_FLAG, 'N') = 'Y'
AND POH.CLM_AWARD_TYPE IS NOT NULL
and poh.po_header_id = p_src_doc_id_tbl(i)
);
SELECT 'Y' INTO is_valid_award_admin
FROM dual
WHERE EXISTS (
SELECT FULL_NAME
FROM
PO_BUYERS_VAL_V
WHERE('Y' = 'Y' or BUSINESS_GROUP_ID = HR_GENERAL.get_business_group_id)
AND employee_id = To_Number(p_award_admin_tbl(i))
);
SELECT 'Y' INTO is_valid_contract_officer
FROM dual
WHERE EXISTS (
SELECT FULL_NAME
FROM
PO_BUYERS_VAL_V
WHERE('Y' = 'Y' or BUSINESS_GROUP_ID = HR_GENERAL.get_business_group_id)
AND employee_id = p_contract_officer_tbl(i)
AND Nvl(contract_officer,'N') = 'Y'
);
SELECT 'Y' INTO is_valid_umbrella_prg_id
FROM dual
WHERE p_umbrella_program_id_tbl(i) = (
SELECT Nvl(umbrella_program_id,-1)
FROM po_headers_all
WHERE po_header_id = p_src_doc_id_tbl(i)
);
SELECT 'Y' INTO is_valid_awd_type
FROM dual
WHERE EXISTS ( SELECT 1 FROM po_lookup_codes
WHERE lookup_type IN ('PO_CLM_AWD_TYPE_SPO',
'PO_CLM_AWD_TYPE_ORD_OFF_IDV'
)
AND lookup_code = p_clm_award_type_tbl(i)
);
SELECT 'Y' INTO is_valid_awd_type
FROM dual
WHERE EXISTS ( SELECT 1 FROM po_lookup_codes
WHERE lookup_type = Decode(p_type_lookup_code_tbl(i),
PO_PDOI_CONSTANTS.g_DOC_TYPE_BLANKET,'PO_CLM_AWD_TYPE_BPA',
PO_PDOI_CONSTANTS.g_DOC_TYPE_CONTRACT,'PO_CLM_AWD_TYPE_CPA')
AND lookup_code = p_clm_award_type_tbl(i)
);
SELECT style_id
BULK COLLECT INTO l_clm_style_id_tbl
FROM po_doc_style_headers
WHERE status = 'ACTIVE'
AND Nvl(clm_flag,'N') = 'Y';
INSERT INTO po_session_gt(KEY,num1,num2)
SELECT l_data_key,
p_intf_header_id(i),
p_style_id_tbl(i)
FROM dual
WHERE p_style_id_tbl(i) IS NULL
OR p_style_id_tbl(i) NOT IN ( SELECT column_value FROM TABLE(l_clm_style_id_tbl) );
DELETE FROM po_session_gt
WHERE key = l_data_key
RETURNING num1,num2 BULK COLLECT INTO l_intf_header_id_tbl,l_style_id_tbl;
INSERT INTO po_session_gt(KEY,num1, num2)
SELECT l_data_key,
p_intf_header_id(i),
p_style_id_tbl(i)
FROM po_headers_all
WHERE p_clm_source_document_id_tbl(i) IS NOT NULL
AND po_header_id = p_clm_source_document_id_tbl(i)
AND Nvl(style_id,-1) <> Nvl(p_style_id_tbl(i),-1);
DELETE FROM po_session_gt
WHERE key = l_data_key
RETURNING num1, num2 BULK COLLECT INTO l_intf_header_id_tbl, l_style_id_tbl;
INSERT INTO po_session_gt(KEY,num1, num2)
SELECT l_data_key,
p_intf_header_id(i),
p_vendor_id_tbl(i)
FROM po_headers_all
WHERE p_clm_source_document_id_tbl(i) IS NOT NULL
AND p_vendor_id_tbl(i) IS NOT NULL
AND po_header_id = p_clm_source_document_id_tbl(i)
AND vendor_id <> p_vendor_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = l_data_key
RETURNING num1, num2 BULK COLLECT INTO l_intf_header_id_tbl, l_vendor_id_tbl;
INSERT INTO po_session_gt(KEY,num1, num2)
SELECT l_data_key,
p_intf_header_id(i),
p_vendor_site_id_tbl(i)
FROM po_headers_all
WHERE p_clm_source_document_id_tbl(i) IS NOT NULL
AND p_vendor_site_id_tbl(i) IS NOT NULL
AND po_header_id = p_clm_source_document_id_tbl(i)
AND vendor_site_id <> p_vendor_site_id_tbl(i);
DELETE FROM po_session_gt
WHERE key = l_data_key
RETURNING num1, num2 BULK COLLECT INTO l_intf_header_id_tbl, l_vendor_site_id_tbl;