[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select count(*)
into l_num_of_award_lines
from pon_award_items_interface
where batch_id = p_batch_id
and rownum = 1;
SELECT decode(count(interface_type), 0, 'N', 'Y')
INTO l_has_errors
FROM pon_interface_errors
WHERE batch_id = p_batch_id
AND rownum = 1;
SELECT '_' || dt.message_suffix
INTO l_suffix
FROM pon_auc_doctypes dt,
pon_auction_headers_all ah
WHERE dt.doctype_id = ah.doctype_id
AND ah.auction_header_id = p_auction_id
AND rownum =1;
SELECT pon_auc_doctype_rules.VALIDITY_FLAG
INTO l_valid_flag
FROM
PON_AUC_DOCTYPE_RULES pon_auc_doctype_rules
, PON_AUC_BIZRULES pon_auc_bizrules
WHERE pon_auc_doctype_rules.BIZRULE_ID = pon_auc_bizrules.BIZRULE_ID
AND pon_auc_doctype_rules.DOCTYPE_ID = p_doctype_Id
AND pon_auc_bizrules.NAME = p_bizrule_name;
SELECT pon_auc_doctype_rules.REQUIRED_FLAG
INTO l_required_flag
FROM
PON_AUC_DOCTYPE_RULES pon_auc_doctype_rules
, PON_AUC_BIZRULES pon_auc_bizrules
WHERE pon_auc_doctype_rules.BIZRULE_ID = pon_auc_bizrules.BIZRULE_ID
AND pon_auc_doctype_rules.DOCTYPE_ID = p_doctype_Id
AND pon_auc_bizrules.NAME = p_bizrule_name;
SELECT
m.uom_code, m.unit_of_measure_tl
INTO
p_amount_based_uom,
p_amount_based_unit_of_measure
FROM
mtl_units_of_measure_tl m
,pon_party_preferences p
WHERE
p.PARTY_ID = p_trading_partner_id
and PREFERENCE_NAME = 'AMOUNT_BASED_UOM'
and m.language = p_language
and m.uom_code = p.PREFERENCE_VALUE;
SELECT inventory_organization_id
INTO p_inventory_org
FROM financials_system_params_all
WHERE nvl(org_id, -9999) = nvl(p_org_id, -9999);
select count(*)
into l_num_of_award_lines
from pon_award_items_interface
where batch_id = p_batch_id
and rownum = 1;
select paha.auction_header_id , paha.request_id, paha.contract_type, paha.price_tiers_indicator
into l_auction_id, l_request_id, l_contract_type, l_price_tiers_indicator
from pon_auction_headers_all paha,
pon_award_items_interface paii
where paii.auction_header_id = paha.auction_header_id
and paii.batch_id = p_batch_id
and rownum = 1;
UPDATE PON_AWARD_ITEMS_INTERFACE PAII
set AWARD_SHIPMENT_NUMBER = ( select -1
from pon_bid_item_prices pbip
where pbip.bid_number = PAII.bid_number
and pbip.auction_line_number = PAII.auction_line_number
and pbip.has_quantity_tiers = 'Y'
)
WHERE PAII.batch_id = p_batch_id
AND PAII.award_status = 'Y';
UPDATE PON_AWARD_ITEMS_INTERFACE PAII
set AWARD_SHIPMENT_NUMBER = (select nvl(( select shipment_number
from pon_bid_shipments pbs
where pbs.bid_number = PAII.bid_number
and pbs.auction_line_number = PAII.AUCTION_LINE_NUMBER
and PAII.award_quantity >= pbs.quantity
and PAII.award_quantity <= pbs.max_quantity ),-1)
from dual)
WHERE PAII.batch_id = p_batch_id
AND PAII.award_status = 'Y'
AND PAII.AWARD_SHIPMENT_NUMBER = -1;
INSERT ALL
-- VALIDATION #1:
-- Check that the bid number is valid for this auction and this line number
WHEN NOT EXISTS (SELECT 'Y'
FROM pon_bid_item_prices bp
WHERE s_auction_header_id = bp.auction_header_id
AND s_auction_line_number = bp.line_number
AND s_bid_number = bp.bid_number)
THEN
INTO PON_INTERFACE_ERRORS
( interface_type
, column_name
, error_message_name
, error_value
, table_name
, batch_id
, worksheet_name
, worksheet_sequence_number
, entity_message_code
, interface_line_id
, request_id
, expiration_date
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
VALUES ( 'AWARDBID'
, fnd_message.get_string('PON','PON_INTEL_BID_NUMBER' || l_suffix)
, 'PON_AUC_BID_NUMBER_INVALID' || l_suffix
, s_bid_number
, 'PON_AWARD_ITEMS_INTERFACE'
, s_batch_id
, s_worksheet_name
, s_worksheet_sequence_number
, s_entity_message_code
, s_interface_line_id
, l_request_id
, l_exp_date
, l_user_id
, sysdate
, l_user_id
, sysdate
, l_login_id
)
-- VALIDATION #2:
-- Check that there is no award decision made on this auction and this line number
WHEN (s_bid_line_award_status = 'Y' OR
s_award_quantity > 0 OR
s_awardreject_reason is not null)
AND s_line_award_status = 'COMPLETED'
THEN
INTO PON_INTERFACE_ERRORS
( interface_type
, column_name
, error_message_name
, table_name
, batch_id
, worksheet_name
, worksheet_sequence_number
, entity_message_code
, interface_line_id
, request_id
, expiration_date
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
VALUES ( 'AWARDBID'
, fnd_message.get_string('PON','PON_INTEL_BID_NUMBER' || l_suffix)
, 'PON_AUC_ITEM_AWARDED'
, 'PON_AWARD_ITEMS_INTERFACE'
, s_batch_id
, s_worksheet_name
, s_worksheet_sequence_number
, s_entity_message_code
, s_interface_line_id
, l_request_id
, l_exp_date
, l_user_id
, sysdate
, l_user_id
, sysdate
, l_login_id
)
-- VALIDATION #3
-- Check if any award reco made for NOT shortlisted bids
WHEN (s_bid_line_award_status = 'Y' OR
s_award_quantity > 0 OR
s_awardreject_reason is not null)
AND s_shortlist_flag = 'N'
THEN
INTO PON_INTERFACE_ERRORS
( interface_type
, column_name
, error_message_name
, error_value
, table_name
, batch_id
, worksheet_name
, worksheet_sequence_number
, entity_message_code
, interface_line_id
, request_id
, expiration_date
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
VALUES ( 'AWARDBID'
, fnd_message.get_string('PON','PON_INTEL_BID_NUMBER' || l_suffix)
, 'PON_AWARD_EXCLUDE_SHLIST_ERR' || l_suffix
, s_bid_number
, 'PON_AWARD_ITEMS_INTERFACE'
, s_batch_id
, s_worksheet_name
, s_worksheet_sequence_number
, s_entity_message_code
, s_interface_line_id
, l_request_id
, l_exp_date
, l_user_id
, sysdate
, l_user_id
, sysdate
, l_login_id
)
-- VALIDATION #4
-- Check if bid is active
WHEN (s_bid_line_award_status = 'Y' OR
s_award_quantity > 0 OR
s_awardreject_reason is not null)
AND s_bid_status <> 'ACTIVE'
THEN
INTO PON_INTERFACE_ERRORS
( interface_type
, column_name
, error_message_name
, error_value
, table_name
, batch_id
, worksheet_name
, worksheet_sequence_number
, entity_message_code
, interface_line_id
, request_id
, expiration_date
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
VALUES ( 'AWARDBID'
, fnd_message.get_string('PON','PON_INTEL_BID_NUMBER' || l_suffix)
, 'PON_AWARD_BID_NOT_ACTIVE' || l_suffix
, s_bid_number
, 'PON_AWARD_ITEMS_INTERFACE'
, s_batch_id
, s_worksheet_name
, s_worksheet_sequence_number
, s_entity_message_code
, s_interface_line_id
, l_request_id
, l_exp_date
, l_user_id
, sysdate
, l_user_id
, sysdate
, l_login_id
)
-- VALIDATION #5
-- Check if supplier is active
WHEN (s_bid_line_award_status = 'Y' OR
s_award_quantity > 0 OR
s_awardreject_reason is not null)
AND s_end_date_active is not null and s_end_date_active <= trunc(sysdate)
THEN
INTO PON_INTERFACE_ERRORS
( interface_type
, column_name
, error_message_name
, error_value
, table_name
, batch_id
, worksheet_name
, worksheet_sequence_number
, entity_message_code
, interface_line_id
, request_id
, expiration_date
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
VALUES ( 'AWARDBID'
, decode(p_spreadsheet_type, PON_AWARD_PKG.g_xml_upload_mode, fnd_message.get_string('PON','PON_ACCTS_SUPPLIER'), fnd_message.get_string('PON','PON_BIDS_BIDDER' || l_suffix))
, 'PON_AWARD_INACTIVE_SUPPLIER'
, s_trading_partner_name
, 'PON_AWARD_ITEMS_INTERFACE'
, s_batch_id
, s_worksheet_name
, s_worksheet_sequence_number
, s_entity_message_code
, s_interface_line_id
, l_request_id
, l_exp_date
, l_user_id
, sysdate
, l_user_id
, sysdate
, l_login_id
)
-- VALIDATION #6
-- Check if award quantity is > 0 when uploading an XML file
WHEN p_spreadsheet_type = PON_AWARD_PKG.g_xml_upload_mode
AND s_award_quantity < 0
THEN
INTO PON_INTERFACE_ERRORS
( interface_type
, column_name
, error_message_name
, error_value_number
, error_value_datatype
, table_name
, batch_id
, worksheet_name
, worksheet_sequence_number
, entity_message_code
, interface_line_id
, request_id
, expiration_date
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
VALUES ( 'AWARDBID'
, decode(l_contract_type, 'STANDARD', fnd_message.get_string('PON','PON_AUCTION_AWARD_QTY'), fnd_message.get_string('PON','PON_AUCTS_AGREED_QUANTITY'))
, decode(l_contract_type, 'STANDARD', 'PON_AUC_AWARD_QTY_COL_NEG', 'PON_AUC_QTY_AGREED_COL_NEG')
, s_award_quantity
, 'NUM'
, 'PON_AWARD_ITEMS_INTERFACE'
, s_batch_id
, s_worksheet_name
, s_worksheet_sequence_number
, s_entity_message_code
, s_interface_line_id
, l_request_id
, l_exp_date
, l_user_id
, sysdate
, l_user_id
, sysdate
, l_login_id
)
-- VALIDATION #7
-- Check if award quantity is entered when document is of type SPO, award status is Y and
-- an XML file is being uploaded
WHEN p_spreadsheet_type = PON_AWARD_PKG.g_xml_upload_mode
AND l_contract_type = 'STANDARD'
AND s_order_type_lookup_code = 'QUANTITY'
AND s_bid_line_award_status = 'Y'
AND s_award_quantity is null
THEN
INTO PON_INTERFACE_ERRORS
( interface_type
, column_name
, error_message_name
, table_name
, batch_id
, worksheet_name
, worksheet_sequence_number
, entity_message_code
, interface_line_id
, request_id
, expiration_date
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
)
VALUES ( 'AWARDBID'
, fnd_message.get_string('PON','PON_AUCTION_AWARD_QTY')
, 'PON_AUCTS_MUST_AWARD'
, 'PON_AWARD_ITEMS_INTERFACE'
, s_batch_id
, s_worksheet_name
, s_worksheet_sequence_number
, s_entity_message_code
, s_interface_line_id
, l_request_id
, l_exp_date
, l_user_id
, sysdate
, l_user_id
, sysdate
, l_login_id
)
-- VALIDATION #8
-- Awarded Qty should fall within Qty tiers provided on the bid, by supplier
-- for tab-delimited spreasheet
WHEN l_price_tiers_indicator = 'QUANTITY_BASED'
AND s_bid_line_award_status = 'Y'
AND s_award_shipment_number = -1
AND p_spreadsheet_type = g_txt_upload_mode
AND nvl(s_award_quantity , 0) > 0
THEN
INTO PON_INTERFACE_ERRORS
( interface_type
, column_name
, error_message_name
, table_name
, error_value
, batch_id
, worksheet_name
, worksheet_sequence_number
, entity_message_code
, interface_line_id
, request_id
, expiration_date
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, TOKEN1_NAME
, TOKEN1_VALUE
)
VALUES ( 'AWARDBID'
, fnd_message.get_string('PON','PON_AUCTION_AWARD_QTY')
, 'PON_QUANTITY_TIER_VIOLATION' || l_suffix
, 'PON_AWARD_ITEMS_INTERFACE'
, s_award_quantity
, s_batch_id
, s_worksheet_name
, s_worksheet_sequence_number
, s_entity_message_code
, s_interface_line_id
, l_request_id
, l_exp_date
, l_user_id
, sysdate
, l_user_id
, sysdate
, l_login_id
, 'BID_NUM'
, s_bid_number
)
SELECT
ap.batch_id AS s_batch_id
, ap.auction_header_id AS s_auction_header_id
, ap.bid_number AS s_bid_number
, ap.auction_line_number AS s_auction_line_number
, ap.worksheet_name AS s_worksheet_name
, ap.worksheet_sequence_number AS s_worksheet_sequence_number
, 'PON_AUC_ITEMS' AS s_entity_message_code
, ap.interface_line_id AS s_interface_line_id
, ap.award_status AS s_bid_line_award_status
, ap.award_quantity AS s_award_quantity
, ap.awardreject_reason AS s_awardreject_reason
, ai.award_status AS s_line_award_status
, ai.order_type_lookup_code AS s_order_type_lookup_code
, bh.trading_partner_name AS s_trading_partner_name
, bh.shortlist_flag AS s_shortlist_flag
, bh.bid_status AS s_bid_status
, pv.end_date_active AS s_end_date_active
, ap.award_shipment_number as s_award_shipment_number
FROM pon_award_items_interface ap
, pon_auction_item_prices_all ai
, pon_bid_headers bh
, po_vendors pv
WHERE ap.batch_id = p_batch_id
AND ap.auction_header_id = ai.auction_header_id
AND ap.auction_line_number = ai.line_number
AND ap.bid_number = bh.bid_number (+)
AND bh.vendor_id = pv.vendor_id (+);
INSERT INTO PON_INTERFACE_ERRORS
( interface_type
, column_name
, error_message_name
, error_value
, table_name
, batch_id
, worksheet_name
, worksheet_sequence_number
, entity_message_code
, interface_line_id
, request_id
, expiration_date
, created_by
, creation_date
, last_updated_by
, last_update_date
, last_update_login
, TOKEN1_NAME
, TOKEN1_VALUE
)
SELECT
'AWARDBID'
, fnd_message.get_string('PON','PON_AUC_LINE_TYPE')
, 'PON_AWARD_FIXED_PRICE'|| l_suffix
, pltt.line_type
, 'PON_AWARD_ITEMS_INTERFACE'
, paii.BATCH_ID
, paii.worksheet_name
, paii.worksheet_sequence_number
, 'PON_AUC_ITEMS' entity_message_code
, to_number(null) interface_line_id
, l_request_id
, l_exp_date
, l_user_id
, sysdate
, l_user_id
, sysdate
, l_login_id
, 'LINE_NUMBER'
, ai.document_disp_line_number
FROM PON_AWARD_ITEMS_INTERFACE paii,
pon_auction_item_prices_all ai,
pon_auction_headers_all ah,
po_line_types_tl pltt
WHERE paii.batch_id = p_batch_id
AND paii.award_status = 'Y'
AND ah.auction_header_id = paii.auction_header_id
AND ah.contract_type = 'STANDARD'
AND ai.line_number = paii.auction_line_number
AND ai.auction_header_id = paii.auction_header_id
AND ai.order_type_lookup_code = 'FIXED PRICE'
AND ai.line_type_id = pltt.line_type_id (+)
AND pltt.language (+) = userenv('LANG')
GROUP BY paii.batch_id,
paii.worksheet_name,
paii.worksheet_sequence_number,
ai.document_disp_line_number,
pltt.line_type
HAVING count(paii.award_status) >1;
SELECT pipi.interface_line_id, pipi.document_disp_line_number, pipi.auction_line_number,
pipi.project_id, pipi.project_task_id, pipi.project_expenditure_type,
pipi.project_exp_organization_id, pipi.project_expenditure_item_date,
pipi.auction_header_id, pipi.interface_type
FROM PON_ITEM_PRICES_INTERFACE pipi
WHERE pipi.batch_id=p_batch_id
AND pipi.project_id IS NOT NULL
AND pipi.project_task_id IS NOT NULL
AND pipi.project_expenditure_type IS NOT NULL
AND pipi.project_exp_organization_id IS NOT NULL
AND pipi.project_expenditure_item_date IS NOT NULL;
INSERT ALL
WHEN (p_progress_payment_type <> 'NONE' AND
line_type_id IS NOT NULL AND
NOT ((order_type_lookup_code = 'FIXED PRICE' AND purchase_basis = 'SERVICES') OR
(order_type_lookup_code = 'QUANTITY' AND purchase_basis = 'GOODS')))
OR po_outside_operation_flag = 'Y' THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_AUC_LINE_TYPE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
line_type, 'PON_INVALID_STYLE_LINETYPE', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
SELECT
pipi.BATCH_ID,
pipi.INTERFACE_LINE_ID,
pipi.INTERFACE_TYPE,
pipi.AUCTION_HEADER_ID,
pipi.DOCUMENT_DISP_LINE_NUMBER,
pipi.PURCHASE_BASIS,
pipi.ORDER_TYPE_LOOKUP_CODE,
pipi.auction_line_number s_line_number,
plt.outside_operation_flag po_outside_operation_flag,
plt.line_type_id,
plt.line_type
FROM PON_ITEM_PRICES_INTERFACE pipi,
PO_LINE_TYPES plt
WHERE batch_id = p_batch_id
AND pipi.line_type_id = plt.line_type_id (+)
AND pipi.group_type NOT IN ('GROUP','LOT_LINE');
INSERT ALL
WHEN S_CLM_INFO_FLAG = 'N' AND
retainage_rate_percent IS NOT NULL AND (retainage_rate_percent < 0 OR retainage_rate_percent > 100) THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_RETAINAGE_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
retainage_rate_percent, 'PON_RTNG_RATE_WRONG', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
max_retainage_amount IS NOT NULL AND max_retainage_amount < 0 THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_MAX_RETAINAGE_AMOUNT'), 'PON_ITEM_PRICES_INTERFACE', -- 1
max_retainage_amount , 'PON_MAX_RTNG_WRONG', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
advance_amount IS NOT NULL AND advance_amount < 0 THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_ITEM_PRICES_INTERFACE', -- 1
advance_amount, 'PON_ADV_AMT_WRONG', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
progress_pymt_rate_percent IS NOT NULL AND (progress_pymt_rate_percent < 0 OR progress_pymt_rate_percent > 100) then
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
progress_pymt_rate_percent, 'PON_PROG_PYMT_RATE_WRONG', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
recoupment_rate_percent IS NOT NULL AND (recoupment_rate_percent < 0 OR recoupment_rate_percent > 100) THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
recoupment_rate_percent, 'PON_RECOUP_RATE_WRONG', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
p_progress_payment_type = 'FINANCE' AND progress_pymt_rate_percent IS NULL THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_PROGRESS_PYMT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
progress_pymt_rate_percent, 'PON_FIELD_MUST_BE_ENTERED', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
progress_pymt_rate_percent IS NOT NULL AND
recoupment_rate_percent IS NULL AND
p_recoupment_negotiable_flag = 'N' THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
recoupment_rate_percent, 'PON_RECUP_NEEDED_WITH_PPRATE', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
((advance_amount IS NOT NULL OR p_advance_negotiable_flag = 'Y') AND
(recoupment_rate_percent IS NULL AND p_recoupment_negotiable_flag = 'N')) THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
recoupment_rate_percent, 'PON_RECUP_NEEDED_WITH_ADVAMT', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
target_price IS NOT NULL AND advance_amount IS NOT NULL
AND (advance_amount > nvl(s_quantity,1) * target_price) THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_ADVANCE_AMOUNT_FLAG'), 'PON_ITEM_PRICES_INTERFACE', -- 1
advance_amount, 'PON_ADV_AMT_MORE', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
p_progress_payment_type <> 'NONE' AND recoupment_rate_percent IS NOT NULL
AND advance_amount IS NOT NULL AND target_price IS NOT NULL
AND (recoupment_rate_percent < (advance_amount * 100)/(nvl(s_quantity,1) * target_price)) THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_RECOUPMENT_RATE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
recoupment_rate_percent, 'PON_RECOUP_LESS_THAN_ADV', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
(p_progress_payment_type <> 'NONE' AND
line_type_id IS NOT NULL AND
NOT ((order_type_lookup_code = 'FIXED PRICE' AND purchase_basis = 'SERVICES') OR
(order_type_lookup_code = 'QUANTITY' AND purchase_basis = 'GOODS')))
OR po_outside_operation_flag = 'Y' THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_AUC_LINE_TYPE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
line_type, 'PON_INVALID_STYLE_LINETYPE', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND line_origination_code <> 'REQUISITION' AND project_number IS NOT NULL AND pro_project_id IS NULL THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT'), 'PON_ITEM_PRICES_INTERFACE', -- 1
project_number, 'PON_PROJ_NUM_INVALID', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
line_origination_code <> 'REQUISITION'
AND pro_project_id IS NOT NULL
AND project_task_number IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM PA_TASKS_EXPEND_V task
WHERE task.project_id = pro_project_id AND task.task_number = project_task_number) THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_AUCTS_TASK'), 'PON_ITEM_PRICES_INTERFACE', -- 1
project_task_number, 'PON_PROJ_TASK_INVALID', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
line_origination_code <> 'REQUISITION'
AND pro_project_id IS NOT NULL
AND project_task_number IS NOT NULL
AND project_award_number IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM GMS_AWARDS_BASIC_V award,
PA_TASKS_EXPEND_V task
WHERE award.project_id = pro_project_id
AND task.task_number = project_task_number
AND award.task_id = task.task_id
AND task.project_id = pro_project_id) THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT_AWARD'), 'PON_ITEM_PRICES_INTERFACE', -- 1
project_award_number, 'PON_PROJ_AWARD_INVALID', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
line_origination_code <> 'REQUISITION' AND project_exp_organization_name IS NOT NULL
AND porg_proj_exp_organization_id IS NULL THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_AUCTS_EXPENDITUE_ORG'), 'PON_ITEM_PRICES_INTERFACE', -- 1
project_exp_organization_name,'PON_PROJ_EXPORG_INVALID', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
s_project_exp_type IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM pa_expenditure_types_expend_v exptype
WHERE system_linkage_function = 'VI'
AND exptype.expenditure_type = s_project_exp_type
AND trunc(sysdate) BETWEEN nvl(exptype.expnd_typ_start_date_active, trunc(sysdate))
AND nvl(exptype.expnd_typ_end_date_Active, trunc(sysdate))
AND trunc(sysdate) BETWEEN nvl(exptype.sys_link_start_date_active, trunc(sysdate))
AND nvl(exptype.sys_link_end_date_Active, trunc(sysdate))) THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_AUCTS_EXPENDITUE_TYPE'), 'PON_ITEM_PRICES_INTERFACE', -- 1
s_project_exp_type, 'PON_PROJ_EXPTYPE_INVALID', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
pro_project_id IS NOT NULL
AND project_award_number IS NULL
AND PON_NEGOTIATION_PUBLISH_PVT.IS_PROJECT_SPONSORED(pro_project_id) = 'Y' THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT_AWARD'), 'PON_ITEM_PRICES_INTERFACE', -- 1
project_award_number, 'PON_PROJ_AWARD_NULL', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
line_origination_code <> 'REQUISITION' AND
((project_number IS NULL OR project_task_number IS NULL OR s_project_exp_type IS NULL
OR project_exp_organization_name IS NULL OR project_expenditure_item_date IS NULL) AND
(project_number IS NOT NULL OR project_task_number IS NOT NULL OR s_project_exp_type IS NOT NULL
OR project_exp_organization_name IS NOT NULL OR project_expenditure_item_date IS NOT NULL)) THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_AUCTS_PROJECT'), 'PON_ITEM_PRICES_INTERFACE', -- 1
NULL, 'PON_PROJ_INFO_INCOMPLETE', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
WHEN S_CLM_INFO_FLAG = 'N' AND
work_approver_user_name IS NOT NULL
AND NOT EXISTS (SELECT 1
FROM PER_WORKFORCE_CURRENT_X peo,
FND_USER fu
WHERE fu.user_name = work_approver_user_name
AND fu.employee_id = peo.person_id
AND SYSDATE >= nvl(fu.start_date, SYSDATE)
AND SYSDATE <= nvl(fu.end_date, SYSDATE) )
THEN
INTO pon_interface_errors
(
interface_type, column_name, table_name, -- 1
error_value, error_message_name, batch_id, -- 2
interface_line_id, auction_header_id, line_number, -- 3
token1_name, token1_value, expiration_date, -- 4
created_by, creation_date, last_updated_by, -- 5
last_update_date, last_update_login -- 6
)
VALUES
(
interface_type, fnd_message.get_string('PON','PON_DEFAULT_OWNER'), 'PON_ITEM_PRICES_INTERFACE', -- 1
NULL, 'PON_LIN_OWNER_INVALID', batch_id, -- 2
interface_line_id, auction_header_id, s_line_number, -- 3
NULL, NULL, l_exp_date, -- 4
l_userid, SYSDATE, l_userid, -- 5
SYSDATE, l_loginid -- 6
)
SELECT
pipi.BATCH_ID,
pipi.INTERFACE_LINE_ID,
pipi.INTERFACE_TYPE,
pipi.AUCTION_HEADER_ID,
pipi.DOCUMENT_DISP_LINE_NUMBER,
pipi.ADVANCE_AMOUNT,
pipi.RECOUPMENT_RATE_PERCENT,
pipi.PROGRESS_PYMT_RATE_PERCENT,
pipi.RETAINAGE_RATE_PERCENT,
pipi.MAX_RETAINAGE_AMOUNT,
pipi.WORK_APPROVER_USER_NAME,
pipi.PROJECT_NUMBER,
pipi.PROJECT_TASK_NUMBER,
pipi.PROJECT_AWARD_NUMBER,
pipi.PROJECT_EXPENDITURE_TYPE s_project_exp_type,
pipi.PROJECT_EXP_ORGANIZATION_NAME,
pipi.PROJECT_EXPENDITURE_ITEM_DATE,
pipi.PURCHASE_BASIS,
pipi.ORDER_TYPE_LOOKUP_CODE,
NVL(pipi.LINE_ORIGINATION_CODE,'-9997') LINE_ORIGINATION_CODE,
pipi.auction_line_number s_line_number,
pipi.target_price,
pipi.quantity s_quantity,
pro.project_id pro_project_id,
porg.organization_id porg_proj_exp_organization_id,
plt.outside_operation_flag po_outside_operation_flag,
plt.line_type_id,
plt.line_type,
Nvl(pipi.CLM_INFO_FLAG, 'N') S_CLM_INFO_FLAG
FROM PON_ITEM_PRICES_INTERFACE pipi,
PA_PROJECTS_EXPEND_V pro,
PA_ORGANIZATIONS_EXPEND_V porg,
PO_LINE_TYPES plt
WHERE batch_id = p_batch_id
AND pipi.project_number = pro.project_number (+)
AND pipi.project_exp_organization_name = porg.name(+)
AND pipi.line_type_id = plt.line_type_id (+)
AND pipi.group_type NOT IN ('GROUP','LOT_LINE');
UPDATE PON_ITEM_PRICES_INTERFACE pipi1
SET (PROJECT_ID, PROJECT_TASK_ID, PROJECT_AWARD_ID, PROJECT_EXP_ORGANIZATION_ID) =
(SELECT pro.project_id, task.task_id, award.award_id, porg.organization_id
FROM PA_PROJECTS_ALL pro,
PA_TASKS_EXPEND_V task,
GMS_AWARDS_ALL award,
HR_ALL_ORGANIZATION_UNITS porg,
PON_ITEM_PRICES_INTERFACE pipi
WHERE pipi.project_number = pro.segment1
AND pipi.project_task_number = task.task_number
AND pro.project_id = task.project_id
AND pipi.project_award_number = award.award_number(+)
AND pipi.project_exp_organization_name = porg.name
AND pipi.batch_id = pipi1.batch_id
AND pipi.interface_line_id = pipi1.interface_line_id)
WHERE pipi1.batch_id = p_batch_id;
UPDATE PON_ITEM_PRICES_INTERFACE pipi
SET (WORK_APPROVER_USER_ID) =
(SELECT fu.user_id
FROM FND_USER fu
WHERE pipi.work_approver_user_name = fu.user_name)
WHERE batch_id = p_batch_id;
select ah.po_style_id
into x_po_style_id
from pon_auction_headers_all ah,
pon_item_prices_interface ipi
where ipi.batch_id = p_batch_id
and ipi.auction_header_id = ah.auction_header_id
and rownum = 1;
SELECT
INTERFACE_LINE_ID
, SHIP_TO_LOCATION_ID
FROM PON_ITEM_PRICES_INTERFACE
WHERE BATCH_ID = c_batch_id
AND SHIP_TO_LOCATION_ID <> -1
AND SHIP_TO_LOCATION <> 'SHIP_NONE_ENTERED';
select auction_header_id
into l_auction_header_id
from pon_item_prices_interface
where batch_id = p_batch_id
and auction_header_id is not null
and rownum =1;
SELECT nvl(auction_round_number,0),
progress_payment_type,
advance_negotiable_flag,
recoupment_negotiable_flag
INTO l_auction_round_number,
l_progress_payment_type,
l_advance_negotiable_flag,
l_recoupment_negotiable_flag
FROM
pon_auction_headers_all
WHERE
auction_header_id = l_auction_header_id;
SELECT doctype_id INTO l_doctype_id FROM pon_auction_headers_all WHERE auction_Header_id = l_auction_header_id;
SELECT
pon_auc_doctype_rules.DEFAULT_VALUE INTO l_is_fed
FROM PON_AUC_DOCTYPE_RULES pon_auc_doctype_rules
, PON_AUC_BIZRULES pon_auc_bizrules
WHERE pon_auc_doctype_rules.BIZRULE_ID = pon_auc_bizrules.BIZRULE_ID
AND pon_auc_doctype_rules.DOCTYPE_ID = l_doctype_id
AND pon_auc_bizrules.NAME = 'FEDERAL_NEGOTIATION';
SELECT Count(*) INTO l_sol_line_count FROM pon_auction_item_prices_all
WHERE auction_header_id = l_auction_header_id
AND group_line_id IS NULL;
SELECT Count(*) INTO l_int_line_count FROM pon_item_prices_interface
WHERE batch_id = p_batch_id
AND group_line_id IS NULL;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUC_TYPE_REQ'),
'Number of clins can not exceed 9999',
'PON_TOO_MANY_CLINS',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where batch_id = p_batch_id
AND ROWNUM < 2;
FOR more_slins IN (SELECT group_line_id,clm_info_flag FROM pon_item_prices_interface WHERE batch_id = p_batch_id
AND group_line_id IS NOT NULL
GROUP BY clm_info_flag,group_line_id
HAVING Count(*) > Decode(nvl(clm_info_flag,'N'),'Y',99,'N',576)) LOOP
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUC_TYPE_REQ'),
'Slins',
'PON_TOO_MANY_SLINS',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where batch_id = p_batch_id
AND auction_line_number = more_slins.group_line_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_QUANTITY'),
quantity,
'PON_AUCTS_PR_QT_NOT_APPLY',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where nvl(price_and_quantity_apply, 'Y') = 'N' and
quantity is not null and
batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTION_UOM'),
unit_of_measure,
'PON_AUCTS_PR_QT_NOT_APPLY',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where nvl(price_and_quantity_apply, 'Y') = 'N' and
(unit_of_measure is not null and unit_of_measure <> 'UOM_NONE_ENTERED') and
batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_TARGET_PRICE'),
target_price,
'PON_AUCTS_PR_QT_NOT_APPLY',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where nvl(price_and_quantity_apply, 'Y') = 'N' and
target_price is not null and
batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_CURRENT_PRICE'),
current_price,
'PON_AUCTS_PR_QT_NOT_APPLY',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where nvl(price_and_quantity_apply, 'Y') = 'N' and
current_price is not null and
batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
'PON_FIELD_MUST_BE_ENTERED',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where line_type = 'LINE_TYPE_NONE_ENTERED'
and batch_id = p_batch_id
and group_type <> 'GROUP'
AND Nvl(clm_info_flag,'N') <> 'Y';
update pon_item_prices_interface p1
set (line_type_id,order_type_lookup_code,purchase_basis,outside_operation_flag) =
(select nvl(po2.line_type_id,-9999), po2.order_type_lookup_code,po2.purchase_basis,po2.outside_operation_flag
FROM po_line_types_vl po2 WHERE upper(p1.line_type) = upper(po2.line_type(+))
and (po2.inactive_date is null or po2.inactive_date > sysdate))
where batch_id = p_batch_id
and line_type <> 'LINE_TYPE_NONE_ENTERED'
AND Nvl(clm_info_flag,'N') <> 'Y'
and line_type is not null;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
line_type,
'PON_AUC_LINE_TYPE_ERR',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where line_type_id is null
and line_type <> 'LINE_TYPE_NONE_ENTERED'
and batch_id = p_batch_id
AND Nvl(clm_info_flag,'N') <> 'Y';
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
line_type,
'PON_AUC_LINE_TYPE_ERR',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where line_type_id not in ( select line_type_id
from po_style_enabled_line_types
where style_id = l_po_style_id)
and line_type_id is not null
and line_type <> 'LINE_TYPE_NONE_ENTERED'
and batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
line_type,
'PON_AUC_LINE_TYPE_ERR',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where purchase_basis not in ( select purchase_basis
from po_style_enabled_pur_bases
where style_id = l_po_style_id)
and line_type_id is not null
and line_type <> 'LINE_TYPE_NONE_ENTERED'
and batch_id = p_batch_id
AND Nvl(clm_info_flag,'N') <> 'Y';
INSERT INTO pon_interface_errors
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
SELECT interface_type,
fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
line_type,
'PON_STANDARD_LINE_TYPES',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
FROM pon_item_prices_interface
WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
AND batch_id = p_batch_id
AND purchase_basis = 'TEMP LABOR';
INSERT INTO pon_interface_errors
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
SELECT interface_type,
fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
line_type,
'PON_GLOBAL_LINE_TYPES',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
FROM pon_item_prices_interface
WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
AND batch_id = p_batch_id
AND purchase_basis = 'TEMP LABOR';
insert into pon_interface_errors
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
SELECT interface_type,
fnd_message.get_string('PON','PON_AUC_LINE_TYPE'),
line_type,
'PON_AUC_GLOBAL_OP_LINE',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
FROM pon_item_prices_interface
WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
AND batch_id = p_batch_id
AND outside_operation_flag = 'Y';
insert into pon_interface_errors
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
SELECT interface_type,
fnd_message.get_string('PON','PON_AUCTS_ITEM'),
null,
'PON_AUC_OPL_ITEM_REQ',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
FROM pon_item_prices_interface
WHERE line_type <> 'LINE_TYPE_NONE_ENTERED'
AND item_number = 'ITEM_NUMBER_NONE_ENTERED'
AND batch_id = p_batch_id
AND outside_operation_flag = 'Y';
INSERT INTO PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
SELECT interface_type,
fnd_message.get_string('PON','PON_ITEM_JOB'),
'',
'PON_LINE_TYPE_JOB_REQ',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
FROM pon_item_prices_interface
WHERE batch_id = p_batch_id
AND purchase_basis = 'TEMP LABOR'
AND item_number = 'ITEM_NUMBER_NONE_ENTERED';
UPDATE pon_item_prices_interface p1
SET job_id =
(SELECT nvl(max(poj.job_id),-1)
FROM po_job_associations poj,
per_jobs pj,
per_jobs_vl pjvl
WHERE pjvl.name = p1.item_number AND
pjvl.job_id = pj.job_id AND
pj.job_id = poj.job_id AND
sysdate < nvl(poj.inactive_date, sysdate + 1) AND
sysdate between pj.date_from and nvl(pj.date_to, sysdate + 1))
WHERE batch_id = p_batch_id AND
purchase_basis = 'TEMP LABOR' AND
item_number <> 'ITEM_NUMBER_NONE_ENTERED';
UPDATE pon_item_prices_interface p1
SET (item_description, category_name) =
(SELECT poj.job_long_description,
FND_FLEX_EXT.get_segs('INV', 'MCAT', CAT.STRUCTURE_ID, CAT.CATEGORY_ID)
FROM po_job_associations poj,
mtl_categories_kfv cat
WHERE cat.category_id = poj.category_id AND
poj.job_id = p1.job_id)
WHERE batch_id = p_batch_id AND
purchase_basis = 'TEMP LABOR' AND
item_number <> 'ITEM_NUMBER_NONE_ENTERED' AND
job_id <> -1;
INSERT INTO PON_INTERFACE_ERRORS (
interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
SELECT interface_type,
fnd_message.get_string('PON','PON_ITEM_JOB'),
item_number,
'PON_JOB_INVALID',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
FROM pon_item_prices_interface p1
WHERE batch_id = p_batch_id AND
purchase_basis = 'TEMP LABOR' AND
item_number <> 'ITEM_NUMBER_NONE_ENTERED' AND
job_id = -1;
UPDATE pon_item_prices_interface p1
SET quantity = NULL,
unit_of_measure = NULL
WHERE batch_id = p_batch_id
AND order_type_lookup_code = 'FIXED PRICE'
AND purchase_basis = 'SERVICES';
UPDATE pon_item_prices_interface p1
SET quantity = null
WHERE batch_id = p_batch_id
AND order_type_lookup_code = 'FIXED PRICE'
AND purchase_basis = 'TEMP LABOR';
UPDATE pon_item_prices_interface p1
SET (item_description, additional_job_details, category_id, category_name) =
(SELECT poj.job_description,
decode(nvl(p1.additional_job_details,'JOB_DETAILS_NONE_ENTERED'),'JOB_DETAILS_NONE_ENTERED',poj.job_long_description, p1.additional_job_details),
cat.category_id,
FND_FLEX_EXT.get_segs('INV', 'MCAT', CAT.STRUCTURE_ID, CAT.CATEGORY_ID)
FROM po_job_associations poj,
mtl_categories_kfv cat
WHERE poj.job_id = p1.job_id
AND cat.category_id = poj.category_id)
WHERE batch_id = p_batch_id AND
purchase_basis = 'TEMP LABOR' AND
job_id <> -1;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_PRICE_DIFF_RESPONSE'),
differential_response_type,
'PON_INVALID_DIFF_RESPONSE',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface p1
where batch_id = p_batch_id and
purchase_basis = 'TEMP LABOR' and
differential_response_type not in ('DIFF_NONE_ENTERED',
fnd_message.get_string('PON','PON_AUCTS_REQUIRED'),
fnd_message.get_string('PON','PON_AUCTS_OPTIONAL'),
fnd_message.get_string('PON','PON_AUCTS_DISPLAY_ONLY'));
update pon_item_prices_interface
set differential_response_type = null
where batch_id = p_batch_id and
(purchase_basis = 'TEMP LABOR' and
differential_response_type not in (fnd_message.get_string('PON','PON_AUCTS_REQUIRED'),
fnd_message.get_string('PON','PON_AUCTS_OPTIONAL'),
fnd_message.get_string('PON','PON_AUCTS_DISPLAY_ONLY'))) or
(purchase_basis <> 'TEMP LABOR');
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_ITEM'),
item_number,
'PON_AUCTS_SS_INVALID_INV_NUM',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface p1
where batch_id = p_batch_id and
item_number <> 'ITEM_NUMBER_NONE_ENTERED' and
upper(order_type_lookup_code) <> upper('AMOUNT') and
purchase_basis <> 'TEMP LABOR' and
order_type_lookup_code <> 'FIXED PRICE' and
not exists (SELECT '1'
FROM mtl_system_items_kfv msi,
mtl_default_sets_view mdsv,
mtl_item_categories mic,
mtl_categories_kfv mck
WHERE msi.concatenated_segments = p1.item_number and
msi.organization_id = l_inventory_org_id and
nvl(msi.outside_operation_flag, 'N') = nvl(p1.outside_operation_flag, 'N') and
msi.purchasing_enabled_flag = 'Y' and
mdsv.functional_area_id = 2 and
mic.inventory_item_id = msi.inventory_item_id and
mic.organization_id = msi.organization_id and
mic.category_set_id = mdsv.category_set_id and
mck.category_id = mic.category_id
and mck.enabled_flag = 'Y'
and sysdate between nvl(mck.start_date_active, sysdate) and
nvl(mck.end_date_active, sysdate)
and nvl(mck.disable_date, sysdate + 1) > sysdate
and (mdsv.validate_flag='Y' and mck.category_id in (select mcsv.category_id from mtl_category_set_valid_cats mcsv where mcsv.category_set_id = mdsv.category_set_id) or mdsv.validate_flag <> 'Y'));
update pon_item_prices_interface p1
set item_number = null,
item_revision = null
where batch_id = p_batch_id and
item_number = 'ITEM_NUMBER_NONE_ENTERED' and
(group_type = 'GROUP' or Nvl(clm_info_flag,'N') = 'Y');
update pon_item_prices_interface p1
set item_number = null,
item_revision = null
where batch_id = p_batch_id AND
purchase_basis = 'SERVICES' or
(purchase_basis = 'GOODS' AND
(item_number = 'ITEM_NUMBER_NONE_ENTERED') OR
(item_number <> 'ITEM_NUMBER_NONE_ENTERED' and
not exists (select '1'
from mtl_system_items_kfv msi,
mtl_default_sets_view mdsv,
mtl_item_categories mic,
mtl_categories_kfv mck
where msi.concatenated_segments = p1.item_number and
msi.organization_id = l_inventory_org_id and
nvl(msi.outside_operation_flag, 'N') = nvl(p1.outside_operation_flag, 'N') and
msi.purchasing_enabled_flag = 'Y' and
mdsv.functional_area_id = 2 and
mic.inventory_item_id = msi.inventory_item_id and
mic.organization_id = msi.organization_id and
mic.category_set_id = mdsv.category_set_id and
mck.category_id = mic.category_id
and mck.enabled_flag = 'Y'
and sysdate between nvl(mck.start_date_active, sysdate) and
nvl(mck.end_date_active, sysdate)
and nvl(mck.disable_date, sysdate + 1) > sysdate
and (mdsv.validate_flag='Y' and mck.category_id in (select mcsv.category_id from mtl_category_set_valid_cats mcsv where mcsv.category_set_id = mdsv.category_set_id) or mdsv.validate_flag <> 'Y'))));
update pon_item_prices_interface p1
set (item_id, item_description, allow_item_desc_update_flag, unit_of_measure) =
(select msi.inventory_item_id,
decode(p1.item_description, 'ITEM_NONE_ENTERED', msitl.description, p1.item_description),
msi.allow_item_desc_update_flag,
decode(p1.unit_of_measure, 'UOM_NONE_ENTERED', uom.unit_of_measure_tl, p1.unit_of_measure)
from mtl_system_items_kfv msi,
mtl_system_items_tl msitl,
mtl_units_of_measure_tl uom
where msi.concatenated_segments = p1.item_number and
msi.organization_id = l_inventory_org_id and
nvl(msi.outside_operation_flag, 'N') = nvl(p1.outside_operation_flag, 'N') and
msi.purchasing_enabled_flag = 'Y' and
msi.inventory_item_id = msitl.inventory_item_id and
msi.organization_id = msitl.organization_id and
msitl.language = p_language and
msi.primary_uom_code = uom.uom_code and
uom.language = p_language)
where batch_id = p_batch_id and
item_number is not NULL and
purchase_basis = 'GOODS';
UPDATE pon_item_prices_interface
SET unit_of_measure = NULL
WHERE batch_id = p_batch_id
AND Nvl(clm_info_flag,'N') = 'Y'
AND unit_of_measure = 'UOM_NONE_ENTERED'; -- bug 9504539
update pon_item_prices_interface p1
set category_name = (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
from mtl_default_sets_view mdsv,
mtl_item_categories mic,
mtl_categories_kfv mck
where mdsv.functional_area_id = 2 and
mic.inventory_item_id = p1.item_id and
mic.organization_id = l_inventory_org_id and
mic.category_set_id = mdsv.category_set_id and
mck.category_id = mic.category_id)
where batch_id = p_batch_id and
purchase_basis <> 'TEMP LABOR' and
item_number is not null and
(category_name = 'CAT_NONE_ENTERED' or category_name is null);
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_ITEM_DESC'),
item_description,
'PON_AUCTS_INVALID_INV_DESC',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface p1
where batch_id = p_batch_id and
purchase_basis <> 'TEMP LABOR' and
item_number is not null and
allow_item_desc_update_flag = 'N' and
item_description <> (select msitl.description
from mtl_system_items_kfv msi,
mtl_system_items_tl msitl
where msi.inventory_item_id = p1.item_id and
msi.organization_id = l_inventory_org_id and
msi.inventory_item_id = msitl.inventory_item_id and
msi.organization_id = msitl.organization_id and
msitl.language = p_language);
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_REVISION'),
item_revision,
'PON_AUCTS_INVALID_INV_REV',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface p1
where batch_id = p_batch_id and
purchase_basis <> 'TEMP LABOR' and
item_number is not null and
item_revision not in (select revision
from mtl_item_revisions_all_v
where inventory_item_id = p1.item_id and
organization_id = l_inventory_org_id);
update pon_item_prices_interface p1
set CATEGORY_NAME = (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
from
MTL_CATEGORIES_KFV mck
,PO_LINE_TYPES plt
where
plt.line_type_id = p1.line_type_id
and plt.category_id = mck.category_id)
where batch_id = p_batch_id
and ((CATEGORY_NAME = 'CAT_NONE_ENTERED') or (CATEGORY_NAME is null));
update pon_item_prices_interface p1
set UNIT_OF_MEASURE = nvl(l_amount_based_unit_of_measure,'UOM_NONE_ENTERED')
where batch_id = p_batch_id
and ((UNIT_OF_MEASURE = 'UOM_NONE_ENTERED') or (UNIT_OF_MEASURE is null))
and ( upper(order_type_lookup_code) = upper('AMOUNT'))
AND Nvl(clm_info_flag,'N') <> 'Y';
update pon_item_prices_interface p1
set UNIT_OF_MEASURE = (select plt.unit_of_measure
from
PO_LINE_TYPES plt
where
plt.line_type_id = p1.line_type_id)
where batch_id = p_batch_id
and ((UNIT_OF_MEASURE = 'UOM_NONE_ENTERED') or (UNIT_OF_MEASURE is null))
AND Nvl(clm_info_flag,'N') <> 'Y';
INSERT INTO PON_INTERFACE_ERRORS
( interface_type
, column_name
, error_value
, error_message_name
, table_name
, batch_id
, interface_line_id
)
SELECT INTERFACE_TYPE
, fnd_message.get_string('PON','PON_AUCTION_UOM')
, unit_of_measure
, 'PON_AUC_LINE_UOM_ERR'
, 'PON_ITEM_PRICES_INTERFACE'
, BATCH_ID
, INTERFACE_LINE_ID
FROM pon_item_prices_interface
where batch_id = p_batch_id
and nvl(price_and_quantity_apply, 'Y') = 'Y'
and order_type_lookup_code = 'AMOUNT'
and NOT((upper(UNIT_OF_MEASURE) = UPPER(l_amount_based_uom) ) or
(upper(UNIT_OF_MEASURE) = UPPER(l_amount_based_unit_of_measure) ))
AND Nvl(clm_info_flag,'N') <> 'Y';
INSERT INTO PON_INTERFACE_ERRORS
( interface_type
, column_name
, error_value
, error_message_name
, table_name
, batch_id
, interface_line_id
)
SELECT
INTERFACE_TYPE
, fnd_message.get_string('PON',decode(p_contract_type,'STANDARD','PON_AUCTS_QUANTITY','PON_AUCTS_EST_QUANTITY'))
, quantity
, 'PON_AUC_LINE_QUAN_ERR'
, 'PON_ITEM_PRICES_INTERFACE'
, BATCH_ID
, INTERFACE_LINE_ID
FROM pon_item_prices_interface
where batch_id = p_batch_id
and nvl(price_and_quantity_apply, 'Y') = 'Y'
and ( upper(order_type_lookup_code) = upper('AMOUNT'))
--Bug 16801061
--When quantity is given as null, Error message should not be
--thrown for amount based lines
and ( NOT(nvl(quantity,1) = 1)) ;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_CATEGORY'),
'PON_FIELD_MUST_BE_ENTERED',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where ((category_name = 'CAT_NONE_ENTERED') or (category_name is null))
AND batch_id = p_batch_id
and group_type <> 'GROUP'
AND Nvl(clm_info_flag,'N') <> 'Y';
update pon_item_prices_interface p
set category_id = (select Nvl(MAX(MCK.category_id),-1)
FROM (select category_id,
FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID) CONCATENATED_SEGMENTS,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
STRUCTURE_ID,
DISABLE_DATE
from MTL_CATEGORIES_KFV) MCK,
MTL_CATEGORY_SETS MCS,
MTL_DEFAULT_CATEGORY_SETS MDCS,
MTL_CATEGORIES MC
WHERE MCK.CONCATENATED_SEGMENTS = p.category_name
AND MCK.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(MCK.START_DATE_ACTIVE, SYSDATE) AND
NVL(MCK.END_DATE_ACTIVE, SYSDATE) AND
MCS.CATEGORY_SET_id=MDCS.CATEGORY_SET_ID AND
MDCS.FUNCTIONAL_AREA_ID=2 AND MCK.STRUCTURE_ID=MCS.STRUCTURE_ID
AND NVL(mck.DISABLE_DATE, SYSDATE + 1) > SYSDATE
AND (MCS.VALIDATE_FLAG='Y' AND mck.CATEGORY_ID IN
(SELECT MCSV.CATEGORY_ID FROM MTL_CATEGORY_SET_VALID_CATS MCSV WHERE
MCSV.CATEGORY_SET_ID=MCS.CATEGORY_SET_ID) OR MCS.VALIDATE_FLAG <> 'Y')
AND MC.CATEGORY_ID = MCK.CATEGORY_ID)
where batch_id = p_batch_id
and category_name <> 'CAT_NON_ENTERED';
update pon_item_prices_interface p
set category_id = (select Nvl(MAX(MCK.category_id),-1)
FROM (select category_id,
FND_FLEX_EXT.get_segs('INV', 'MCAT', STRUCTURE_ID, CATEGORY_ID) CONCATENATED_SEGMENTS,
ENABLED_FLAG,
START_DATE_ACTIVE,
END_DATE_ACTIVE,
STRUCTURE_ID,
DISABLE_DATE
from MTL_CATEGORIES_KFV) MCK,
MTL_CATEGORY_SETS MCS,
MTL_DEFAULT_CATEGORY_SETS MDCS,
MTL_CATEGORIES MC
WHERE UPPER(MCK.CONCATENATED_SEGMENTS) = UPPER(p.category_name)
AND MCK.ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(MCK.START_DATE_ACTIVE, SYSDATE) AND
NVL(MCK.END_DATE_ACTIVE, SYSDATE) AND
MCS.CATEGORY_SET_id=MDCS.CATEGORY_SET_ID AND
MDCS.FUNCTIONAL_AREA_ID=2 AND MCK.STRUCTURE_ID=MCS.STRUCTURE_ID
AND NVL(mck.DISABLE_DATE, SYSDATE + 1) > SYSDATE
AND (MCS.VALIDATE_FLAG='Y' AND mck.CATEGORY_ID IN
(SELECT MCSV.CATEGORY_ID FROM MTL_CATEGORY_SET_VALID_CATS MCSV WHERE
MCSV.CATEGORY_SET_ID=MCS.CATEGORY_SET_ID) OR MCS.VALIDATE_FLAG <> 'Y')
AND MC.CATEGORY_ID = MCK.CATEGORY_ID)
where batch_id = p_batch_id
and category_name <> 'CAT_NON_ENTERED'
and ( category_id is null or category_id = -1 );
-- we need to update all valid user entered category names to the
-- actual case sensitive value
/*
update pon_item_prices_interface p
set category_name = (select Nvl(MAX(category_name),p.category_name)
from icx_por_categories_tl i
where i.rt_category_id = p.category_id
and type=2 and i.language= p_language)
where batch_id = p_batch_id
and category_name <> 'CAT_NON_ENTERED'
AND category_id <> -1;
update pon_item_prices_interface p
set category_name = (select Nvl(MAX(FND_FLEX_EXT.get_segs('INV', 'MCAT', i.STRUCTURE_ID, i.CATEGORY_ID)),p.category_name)
from mtl_categories_kfv i
where i.category_id = p.category_id)
where batch_id = p_batch_id
and category_name <> 'CAT_NON_ENTERED'
AND category_id <> -1;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_CATEGORY'),
category_name,
decode(purchase_basis,'TEMP LABOR','PON_INVALID_TEMP_LABOR_CAT','PON_CATEGORY_ID_NOT_FOUND'),
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where category_id = -1
AND batch_id = p_batch_id
AND category_name <> 'CAT_NON_ENTERED';
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_CATEGORY'),
category_name,
'PON_AUCTS_INVALID_INV_CAT',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface p1
where category_id <> -1 and
batch_id = p_batch_id and
purchase_basis <> 'TEMP LABOR' and
order_type_lookup_code <> 'FIXED PRICE' and
item_number is not null and
category_name <> (select FND_FLEX_EXT.get_segs('INV', 'MCAT', MCK.STRUCTURE_ID, MCK.CATEGORY_ID) concatenated_segments
from mtl_default_sets_view mdsv,
mtl_item_categories mic,
mtl_categories_kfv mck
where mdsv.functional_area_id = 2 and
mic.inventory_item_id = p1.item_id and
mic.organization_id = l_inventory_org_id and
mic.category_set_id = mdsv.category_set_id and
mck.category_id = mic.category_id);
update pon_item_prices_interface p1
set ip_category_name = (select category_name
from icx_cat_categories_v
where rt_category_id = decode(pon_auction_pkg.get_mapped_ip_category(p1.category_id), -2, null, pon_auction_pkg.get_mapped_ip_category(p1.category_id)) and
language = p_language)
where batch_id = p_batch_id and
(action is null or action = '+') and
p1.category_id <> -1 and
(p1.ip_category_name is null or p1.ip_category_name = 'IP_CAT_NONE_ENTERED');
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_SHOPPING_CAT'),
'PON_SHOP_CAT_NOT_VALID',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface p1
where p1.batch_id = p_batch_id and
p1.ip_category_name is not null and
p1.ip_category_name <> 'IP_CAT_NONE_ENTERED' and
not exists (select null
from icx_cat_categories_v icx
where icx.category_name = p1.ip_category_name and
icx.language = p_language)
AND Nvl(clm_info_flag,'N') <> 'Y';
update pon_item_prices_interface p1
set ip_category_name = null
where p1.batch_id = p_batch_id and
p1.ip_category_name is not null and
p1.ip_category_name <> 'IP_CAT_NONE_ENTERED' and
not exists (select null
from icx_cat_categories_v icx
where icx.category_name = p1.ip_category_name and
icx.language = p_language);
update pon_item_prices_interface p1
set ip_category_id = (select rt_category_id
from icx_cat_categories_v icx
where icx.category_name = p1.ip_category_name and
language = p_language and rownum=1 )
where p1.batch_id = p_batch_id and
p1.ip_category_name is not null and
p1.ip_category_name <> 'IP_CAT_NONE_ENTERED';
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_ITEM_DESC'),
'PON_FIELD_MUST_BE_ENTERED',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where item_description = 'ITEM_NONE_ENTERED'
AND batch_id = p_batch_id
and nvl(purchase_basis,'NULL') <> 'TEMP LABOR';
update pon_item_prices_interface p1
set item_description = null
where batch_id = p_batch_id and
item_description = 'ITEM_NONE_ENTERED';
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_ORDER_UNIT_H'),
'PON_FIELD_MUST_BE_ENTERED',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where ((unit_of_measure = 'UOM_NONE_ENTERED') or (unit_of_measure is null))
AND nvl(price_and_quantity_apply, 'Y') = 'Y'
AND order_type_lookup_code <> 'FIXED PRICE'
AND batch_id = p_batch_id
and group_type <> 'GROUP'
AND Nvl(clm_info_flag,'N') <> 'Y';
update pon_item_prices_interface p
set uom_code = (select nvl(max(uom_code),'XXX') from
mtl_units_of_measure_tl m
where language = p_language
and unit_of_measure_tl = p.unit_of_measure
and (p.purchase_basis <> 'TEMP LABOR' or
(p.purchase_basis = 'TEMP LABOR' and
exists (select 1 from mtl_uom_conversions_val_v where
m.unit_of_measure = unit_of_measure and
uom_class = FND_PROFILE.VALUE('PO_RATE_UOM_CLASS')))))
where batch_id = p_batch_id
and unit_of_measure <> 'UOM_NONE_ENTERED';
update pon_item_prices_interface p
set uom_code = (select nvl(max(uom_code),'XXX') from
mtl_units_of_measure_tl m
where language = p_language
and upper(unit_of_measure_tl) = upper(p.unit_of_measure)
and (p.purchase_basis <> 'TEMP LABOR' or
(p.purchase_basis = 'TEMP LABOR' and
exists (select 1 from mtl_uom_conversions_val_v where
m.unit_of_measure = unit_of_measure and
uom_class = FND_PROFILE.VALUE('PO_RATE_UOM_CLASS')))))
where batch_id = p_batch_id
and unit_of_measure <> 'UOM_NONE_ENTERED'
and uom_code = 'XXX';
update pon_item_prices_interface p
set uom_code = (select nvl(max(uom_code),'XXX') from
mtl_units_of_measure_tl m
where language = p_language
and uom_code = p.unit_of_measure
and (p.purchase_basis <> 'TEMP LABOR' or
(p.purchase_basis = 'TEMP LABOR' and
exists (select 1 from mtl_uom_conversions_val_v where
m.unit_of_measure = unit_of_measure and
uom_class = FND_PROFILE.VALUE('PO_RATE_UOM_CLASS')))))
where batch_id = p_batch_id
and unit_of_measure <> 'UOM_NONE_ENTERED'
and uom_code = 'XXX';
update pon_item_prices_interface p
set uom_code = (select nvl(max(uom_code),'XXX') from
mtl_units_of_measure_tl m
where language = p_language
and upper(uom_code) = upper(p.unit_of_measure)
and (p.purchase_basis <> 'TEMP LABOR' or
(p.purchase_basis = 'TEMP LABOR' and
exists (select 1 from mtl_uom_conversions_val_v where
m.unit_of_measure = unit_of_measure and
uom_class = FND_PROFILE.VALUE('PO_RATE_UOM_CLASS')))))
where batch_id = p_batch_id
and unit_of_measure <> 'UOM_NONE_ENTERED'
and uom_code = 'XXX';
-- we need to update all valid user entered unit of measures to the
-- actual case sensitive value
update pon_item_prices_interface p
set unit_of_measure = (select nvl(max(unit_of_measure_tl),p.unit_of_measure) from
mtl_units_of_measure_tl m
where language = p_language
and uom_code = p.uom_code)
where batch_id = p_batch_id
and unit_of_measure <> 'UOM_NONE_ENTERED'
AND uom_code <> 'XXX';
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTION_UOM'),
unit_of_measure,
decode(purchase_basis,'TEMP LABOR','PON_INVALID_TEMP_LABOR_UOM','PON_INVALID_UOM'),
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface p
where unit_of_measure <> 'UOM_NONE_ENTERED'
AND batch_id = p_batch_id
AND uom_code = 'XXX'
AND nvl(price_and_quantity_apply, 'Y') = 'Y';
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTION_UOM'),
unit_of_measure,
'PON_AUCTS_INVALID_INV_UOM',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface p1
where batch_id = p_batch_id and
purchase_basis <> 'TEMP LABOR' and
item_number is not null and
uom_code not in (select uom_code
from mtl_item_uoms_view
where inventory_item_id = p1.item_id and
organization_id = l_inventory_org_id) and
nvl(price_and_quantity_apply, 'Y') = 'Y';
update pon_item_prices_interface p1
set unit_of_measure = null,
uom_code = null
where batch_id = p_batch_id and
nvl(price_and_quantity_apply, 'Y') = 'N';
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_QUANTITY'),
'PON_FIELD_MUST_BE_ENTERED',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where quantity is null
and nvl(price_and_quantity_apply, 'Y') = 'Y'
and batch_id = p_batch_id
and purchase_basis <> 'TEMP LABOR'
and order_type_lookup_code <> 'AMOUNT'
and order_type_lookup_code <> 'FIXED PRICE'
and group_type <> 'GROUP'
AND Nvl(clm_info_flag,'N') <> 'Y';
INSERT INTO pon_interface_errors
(interface_type,
column_name,
error_value,
error_message_name,
token1_name,
token1_value,
table_name,
batch_id,
interface_line_id)
SELECT
interface_type,
fnd_message.get_string('PON', 'PON_AUCTS_EST_QUANTITY'),
quantity,
'PON_AUC_QUAN_FIXED_AMT',
'LINENUM',
interface_line_id,
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
FROM pon_item_prices_interface
WHERE
quantity IS NULL
AND order_type_lookup_code <> 'FIXED PRICE'
AND order_type_lookup_code <> 'AMOUNT'
AND nvl(price_and_quantity_apply, 'Y') = 'Y'
AND batch_id = p_batch_id
AND EXISTS (SELECT 1
FROM
pon_auc_price_elements_int pfs,
fnd_lookup_values lookups
WHERE
pfs.batch_id = pon_item_prices_interface.batch_id
AND pfs.auction_header_id = pon_item_prices_interface.auction_header_id
AND pfs.interface_line_id = pon_item_prices_interface.interface_line_id
AND lookups.lookup_type = 'PON_PRICING_BASIS'
AND lookups.lookup_code = 'FIXED_AMOUNT'
AND lookups.view_application_id = 0
AND lookups.security_group_id = 0
AND lookups.meaning = pfs.pricing_basis_name
AND lookups.language = USERENV('LANG'))
AND Nvl(clm_info_flag,'N') <> 'Y';
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
decode(p_contract_type, 'BLANKET','PON_AUCTS_EST_QUANTITY', 'CONTRACT', 'PON_AUCTS_EST_QUANTITY', 'PON_AUCTS_QUANTITY'),
quantity,
'PON_MUST_BE_POSITIVE_NUMBER',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where quantity <= 0
AND order_type_lookup_code <> 'FIXED PRICE'
AND nvl(price_and_quantity_apply, 'Y') = 'Y'
AND batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
'',
'',
'PON_NEED_BY_DATE_POP_DATE',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where ( (nvl(l_is_fed,'N')='Y') and
(p_contract_type = 'STANDARD') and
( (clm_need_by_date is not null and need_by_start_date is not null and need_by_date is not null)
/* Bug : 13700330 : Need By or POP dates are not mandatory for non-inventory lines. */
--or (clm_need_by_date is null and ( (need_by_start_date is null and need_by_date is null))
or (clm_need_by_date is not null and ( (need_by_start_date is null and need_by_date is not null)
or (need_by_start_date is not null and need_by_date is null))
)
)
)
and batch_id = p_batch_id
and nvl(clm_info_flag,'N') = 'N';
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
'',
'',
'PON_POP_START_DATE_REQD',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where ( (nvl(l_is_fed,'N')='Y') and
(p_contract_type = 'STANDARD') and
( (clm_need_by_date is null and (need_by_start_date is null and need_by_date is not null)))
)
and batch_id = p_batch_id
and nvl(clm_info_flag,'N') = 'N';
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
'',
'',
'PON_POP_END_DATE_REQUIRED',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where ( (nvl(l_is_fed,'N')='Y') and
(p_contract_type = 'STANDARD') and
( (clm_need_by_date is null and (need_by_start_date is not null and need_by_date is null)))
)
and batch_id = p_batch_id
and nvl(clm_info_flag,'N') = 'N';
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
-- CLM : For federal documents, display Period Of Performance Start Date
decode(l_is_fed,'Y',fnd_message.get_string('PON','PON_CLM_PERIOD_PERF_START_DATE'),fnd_message.get_string('PON','PON_AUC_NEED_BY_FROM_DATE')),
need_by_start_date,
'PON_DATE_MUST_BE_GT_TODAY',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where need_by_start_date < sysdate
and batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
-- CLM : For federal documents, display Period Of Performance End Date
decode(l_is_fed,'Y',fnd_message.get_string('PON','PON_CLM_PERIOD_PERF_END_DATE'),fnd_message.get_string('PON','PON_AUC_NEED_BY_TO_DATE')),
need_by_date,
'PON_DATE_MUST_BE_GT_TODAY',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where need_by_date < sysdate
and batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_NEED_BY_DATE'),
clm_need_by_date,
'PON_DATE_MUST_BE_GT_TODAY',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where clm_need_by_date < sysdate
and batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
--CLM : For Federal, show Period of Performance end date
decode(l_is_fed,'Y',fnd_message.get_string('PON','PON_CLM_PERIOD_PERF_END_DATE'),fnd_message.get_string('PON','PON_AUC_NEED_BY_TO_DATE')),
need_by_date,
-- CLM : For federal display Period Of Perf dates message
decode(l_is_fed,'Y','PON_AUC_IMPORT_POP_BEFORE_FROM','PON_AUC_NEEDBY_BEFORE_FROM_SS'),
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
--Bug : 13700330 : For clm, POP Start Date should be less than End Date
where ( (l_is_fed = 'Y' AND (clm_need_by_date IS NULL AND (need_by_date <= need_by_start_date)))
OR (l_is_fed = 'N' AND need_by_date < need_by_start_date))
and batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select ip.interface_type,
fnd_message.get_string('PON','PON_AUCTS_NEEDBY'),
null,
'PON_NEED_BY_DATE_REQ_SPD',
'PON_ITEM_PRICES_INTERFACE',
ip.batch_id,
ip.interface_line_id
from pon_item_prices_interface ip
where ip.batch_id = p_batch_id
and ( (l_is_fed = 'Y' AND ip.clm_need_by_date IS null)
OR (l_is_fed = 'N' AND (ip.need_by_date is null AND ip.need_by_start_date is NULL)))
and ip.item_id is not null
and exists ( SELECT 'x'
FROM mtl_system_items_kfv msi,
financials_system_params_all fsp
WHERE nvl(fsp.org_id, -9999) = nvl(p_org_id,-9999)
and msi.organization_id = fsp.inventory_organization_id
and msi.inventory_item_id = ip.item_id
and (msi.INVENTORY_PLANNING_CODE in (1, 2) or msi.MRP_PLANNING_CODE in
(3, 4, 7, 8, 9))
)
AND Nvl(clm_info_flag,'N') <> 'Y';
update pon_item_prices_interface p
set p.CLM_DELIVERY_EVENT_CODE = (SELECT lookup_code
FROM fnd_lookup_values_vl
WHERE lookup_type = 'CLM_DELIVERY_EVENT'
AND meaning = p.CLM_DELIVERY_EVENT);
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_CLM_DEL_EVENT_CODE'),
CLM_DELIVERY_EVENT,
'PON_INV_DEL_EVENT_CODE',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where CLM_DELIVERY_EVENT_CODE IS NULL
and CLM_DELIVERY_EVENT IS NOT NULL;
update pon_item_prices_interface p
set p.CLM_DELIVERY_PERIOD_UOM_CODE = (SELECT lookup_code
FROM fnd_lookup_values_vl
WHERE lookup_type = 'CLM_PERIOD'
AND meaning = p.CLM_DELIVERY_PERIOD_UOM);
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_CLM_DEL_PERIOD_UOM'),
CLM_DELIVERY_PERIOD_UOM,
'PON_INV_PERIOD_UOM_CODE',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where CLM_DELIVERY_PERIOD_UOM_CODE IS NULL
and CLM_DELIVERY_PERIOD_UOM IS NOT NULL;
update pon_item_prices_interface p
set p.CLM_POP_DURATION_UOM_CODE = (SELECT lookup_code
FROM fnd_lookup_values_vl
WHERE lookup_type = 'CLM_PERIOD'
AND meaning = p.CLM_POP_DURATION_UOM);
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_CLM_POP_DUR_UOM'),
CLM_POP_DURATION_UOM,
'PON_INV_POP_UOM_CODE',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where CLM_POP_DURATION_UOM_CODE IS NULL
and CLM_POP_DURATION_UOM IS NOT NULL;
insert into PON_INTERFACE_ERRORS
( interface_type
, column_name
, error_message_name
, table_name
, batch_id
, interface_line_id
)
select interface_type
, fnd_message.get_string('PON','PON_AUCTS_SHIP_TO_LOC')
, 'PON_FIELD_MUST_BE_ENTERED'
, 'PON_ITEM_PRICES_INTERFACE'
, batch_id
, interface_line_id
from pon_item_prices_interface
where batch_id = p_batch_id
AND ship_to_location = 'SHIP_NONE_ENTERED'
and group_type <> 'GROUP'
AND Nvl(clm_info_flag,'N') <> 'Y';
update pon_item_prices_interface p
set ship_to_location_id = (select (nvl(max(location_id), -1))
from po_ship_to_loc_org_v po_v
where po_v.location_code = p.ship_to_location)
where batch_id = p_batch_id
and ship_to_location <> 'SHIP_NONE_ENTERED';
update pon_item_prices_interface p
set ship_to_location_id = (select (nvl(max(location_id), -1))
from po_ship_to_loc_org_v po_v
where upper(po_v.location_code) = upper(p.ship_to_location))
where batch_id = p_batch_id
and ship_to_location <> 'SHIP_NONE_ENTERED'
and ship_to_location_id = -1;
-- we need to update all valid user entered shipping locations to the
-- actual case sensitive value fph
update pon_item_prices_interface p
set ship_to_location = (select (nvl(max(location_code), -1))
from po_ship_to_loc_org_v po_v
where po_v.location_id = p.ship_to_location_id)
where batch_id = p_batch_id
and ship_to_location <> 'SHIP_NONE_ENTERED'
AND ship_to_location_id <> -1;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_SHIP_TO_LOC'),
ship_to_location,
'PON_CAT_INVALID_VALUE',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where ship_to_location_id = -1
AND ship_to_location <> 'SHIP_NONE_ENTERED'
AND batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_TARGET_PRICE'),
target_price,
'PON_MUST_BE_POSITIVE_NUMBER',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where TARGET_PRICE <= 0
and nvl(price_and_quantity_apply, 'Y') = 'Y'
and batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
bid_start_price,
'PON_MUST_BE_POSITIVE_NUMBER',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where bid_start_price <= 0
and batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_CURRENT_PRICE'),
current_price,
'PON_MUST_BE_POSITIVE_NUMBER',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where current_price <= 0
and nvl(price_and_quantity_apply, 'Y') = 'Y'
and batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_RESERVE_PRICE'),
reserve_price,
'PON_MUST_BE_POSITIVE_NUMBER',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where reserve_price <= 0 AND
batch_id = p_batch_id;
SELECT TRANSACTION_TYPE
INTO l_transaction_type
FROM PON_AUC_DOCTYPES
WHERE DOCTYPE_ID = p_doctype_Id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
bid_start_price,
'PON_TARGET_GTR_BID_START',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where bid_start_price <= target_price AND
batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
bid_start_price,
'PON_AUCTS_START_LT_TARGET',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where bid_start_price >= target_price AND
batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTION_BID_START_PRICE'),
bid_start_price,
'PON_AUCTS_START_LT_RESERVE',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where reserve_price < bid_start_price AND
batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_RESERVE_PRICE'),
reserve_price,
'PON_AUCTS_RESERVE_LT_TARGET',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where reserve_price > target_price AND
batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_ITEM_PRICE_TARGET_VALUE'),
unit_target_price,
'PON_AUC_POSITIVE_OR_ZERO',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where UNIT_TARGET_PRICE < 0
and nvl(price_and_quantity_apply, 'Y') = 'Y'
and batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_CLM_OPTION_FROM_DATE'),
'PON_FIELD_MUST_BE_ENTERED',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where CLM_BASE_LINE_NUM IS NOT NULL
AND CLM_OPTION_FROM_DATE IS NULL
and batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_CLM_OPTION_TO_DATE'),
'PON_FIELD_MUST_BE_ENTERED',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where CLM_BASE_LINE_NUM IS NOT NULL
AND CLM_OPTION_TO_DATE IS NULL
and batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_CLM_OPTION_FROM_DATE'),
CLM_OPTION_FROM_DATE,
'PON_CLM_OPT_FROMDATE_ERR',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
WHERE CLM_BASE_LINE_NUM IS NOT NULL
AND CLM_OPTION_FROM_DATE < sysdate
and batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_CLM_OPTION_TO_DATE'),
CLM_OPTION_TO_DATE,
'PON_CLM_OPT_TODATE_ERR',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where CLM_BASE_LINE_NUM IS NOT NULL
AND CLM_OPTION_TO_DATE < CLM_OPTION_FROM_DATE
and batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
(select interface_type,
fnd_message.get_string('PON', 'PON_CLM_AMOUNT'),
'Slins',
'PON_CLM_AMOUNT_ERR',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface P1
where batch_id = p_batch_id
AND P1.CLM_AMOUNT IS NOT NULL
AND P1.LINE_TYPE_ID NOT IN (SELECT LINE_TYPE_ID FROM po_line_types_b WHERE ORDER_TYPE_LOOKUP_CODE = 'AMOUNT')
);
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
(select interface_type,
fnd_message.get_string('PON', 'PON_CLM_UNIT_PRICE'),
'Slins',
'PON_CLM_UNIT_PRICE_ERR',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface P1
where batch_id = p_batch_id
AND P1.CLM_UNIT_PRICE IS NOT NULL
AND P1.LINE_TYPE_ID NOT IN (SELECT LINE_TYPE_ID FROM po_line_types_b WHERE ORDER_TYPE_LOOKUP_CODE = 'QUANTITY')
);
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
--Bug 16567154
--Removing spaces in the error message
(select interface_type,
fnd_message.get_string('PON', 'PON_CLM_CONTRACT_TYPE'),
'Slins',
'PON_CLM_CONTRACT_TYPE_ERR',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where batch_id = p_batch_id
AND CLM_CONTRACT_TYPE IS NOT NULL
AND CLM_CONTRACT_TYPE NOT IN
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'PO_FEDERAL_CONTRACT_TYPES_QTY'
AND LANGUAGE = USERENV('LANG')
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1)
));
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
--Bug 16567154
--Removing spaces in the error message
(select interface_type,
fnd_message.get_string('PON', 'PON_CLM_COST_CONSTRAINT'),
'Slins',
'PON_CLM_COST_CONSTRAINT_ERR',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where batch_id = p_batch_id
AND CLM_COST_CONSTRAINT IS NOT NULL
AND CLM_COST_CONSTRAINT NOT IN
(SELECT LOOKUP_CODE
FROM FND_LOOKUP_VALUES
WHERE LOOKUP_TYPE = 'PO_FEDERAL_COST_CONSTRAINTS'
AND LANGUAGE = USERENV('LANG')
AND ENABLED_FLAG = 'Y'
AND SYSDATE BETWEEN NVL(START_DATE_ACTIVE, SYSDATE - 1) AND NVL(END_DATE_ACTIVE, SYSDATE + 1)
));
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
--Bug 16567154
--Removing spaces in the error message
(select interface_type,
fnd_message.get_string('PON', 'PON_CLM_BASE_LINE_NUM'),
'Slins',
'PON_CLM_BASE_LINE_NUM_ERR',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface P1
where batch_id = p_batch_id
AND P1.CLM_BASE_LINE_NUM IS NOT NULL
--bug 16567154
--Modifying the condition for option lines without base lines spreadsheet import
AND (P1.CLM_BASE_LINE_NUM<>-9999)
AND P1.CLM_BASE_LINE_NUM NOT IN
(SELECT P2.AUCTION_LINE_NUMBER from
pon_item_prices_interface P2
where auction_header_id = l_auction_header_id )
);
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
--Bug 16567154
--Removing spaces in the error message
(select interface_type,
fnd_message.get_string('PON', 'PON_CLM_OPTION_INDICATOR'),
'Slins',
'PON_CLM_OPTION_INDICATOR_ERR',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface P1
where batch_id = p_batch_id
AND P1.CLM_OPTION_INDICATOR = 'Y' AND
(P1.CLM_OPTION_FROM_DATE IS NULL OR P1.CLM_OPTION_TO_DATE IS NULL )
);
SELECT price_tiers_indicator INTO l_price_tiers_indicator FROM pon_auction_headers_all WHERE auction_header_id = l_auction_header_id;
/* insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AGREEMENT_RELEASE_QUANTITY') || ',' || fnd_message.get_string('PON','PON_AUCTS_PB_RESPONSE'),
null,
'PON_AUCTS_PB_FP_LINE_ERR',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where ORDER_TYPE_LOOKUP_CODE IN ( 'AMOUNT','FIXED PRICE')
AND ( price_break_type IS NOT NULL OR price_break_neg_flag IS NOT NULL )
AND Nvl(clm_info_flag,'N') <> 'Y'
AND group_type <> 'GROUP'
and batch_id = p_batch_id;
UPDATE pon_item_prices_interface p1
SET price_break_type = l_price_break_type,price_break_neg_flag = l_price_break_neg_flag
WHERE ORDER_TYPE_LOOKUP_CODE IN ( 'AMOUNT','FIXED PRICE')
AND Nvl(clm_info_flag,'N') <> 'Y'
AND group_type <> 'GROUP'
and batch_id = p_batch_id;*/
UPDATE pon_item_prices_interface p1
SET price_break_type = null,price_break_neg_flag = null
WHERE ( ORDER_TYPE_LOOKUP_CODE IN ( 'AMOUNT','FIXED PRICE')
OR Nvl(clm_info_flag,'N') = 'Y'
OR group_type = 'GROUP' )
AND batch_id = p_batch_id;
UPDATE pon_item_prices_interface p1
SET p1.price_break_type =
(SELECT Decode(Nvl(p2.price_break_type,'NULL'),'NULL',l_price_break_type,Decode(p2.price_break_neg_flag,l_none,'NONE',Decode(p2.price_break_type,l_cumulative,'CUMULATIVE',Decode(p2.price_break_type,l_noncumulative,'NON-CUMULATIVE',null))))
FROM pon_item_prices_interface p2
WHERE p2.INTERFACE_LINE_ID = p1.INTERFACE_LINE_ID
AND p2.batch_id = p_batch_id
),
p1.price_break_neg_flag =
(SELECT Decode(Nvl(p2.price_break_neg_flag,'NULL'),'NULL',l_price_break_neg_flag,Decode(p2.price_break_neg_flag,l_none,l_price_break_neg_flag,Decode(p2.price_break_neg_flag,l_required,'N',Decode(p2.price_break_neg_flag,l_optional,'Y',null))))
FROM pon_item_prices_interface p2
WHERE p2.INTERFACE_LINE_ID = p1.INTERFACE_LINE_ID
AND p2.batch_id = p_batch_id
)
where ORDER_TYPE_LOOKUP_CODE NOT IN( 'AMOUNT','FIXED PRICE')
AND Nvl(clm_info_flag,'N') <> 'Y'
AND group_type <> 'GROUP'
and batch_id = p_batch_id;
SELECT price_tiers_indicator INTO l_price_tiers_indicator FROM pon_auction_headers_all WHERE auction_header_id = l_auction_header_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AGREEMENT_RELEASE_QUANTITY') || ',' || fnd_message.get_string('PON','PON_AUCTS_PB_RESPONSE'),
null,
'PON_INVALID_PB_SETTING',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface
where ( price_break_type IS NULL OR price_break_neg_flag IS NULL)
AND ORDER_TYPE_LOOKUP_CODE NOT IN( 'AMOUNT','FIXED PRICE')
AND Nvl(clm_info_flag,'N') <> 'Y'
AND group_type <> 'GROUP'
and batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_PB_RESPONSE'),
null,
'PON_AUC_PB_NOT_EMPTY',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface p1
where price_break_type = 'NONE'
AND Nvl((SELECT has_shipments_flag FROM pon_auction_item_prices_all WHERE line_number = p1.auction_line_number AND auction_header_id = l_auction_header_id),'N') = 'Y'
AND ORDER_TYPE_LOOKUP_CODE NOT IN( 'AMOUNT','FIXED PRICE')
AND Nvl(clm_info_flag,'N') <> 'Y'
AND group_type <> 'GROUP'
AND batch_id = p_batch_id;
INSERT INTO PON_INTERFACE_ERRORS
(
COLUMN_NAME ,
INTERFACE_TYPE ,
ERROR_MESSAGE_NAME,
batch_id ,
table_name ,
AUCTION_HEADER_ID ,
TOKEN1_NAME ,
TOKEN1_VALUE ,
interface_line_id
)
select
pon_auction_pkg.getMessage('PON_AGREEMENT_RELEASE_QUANTITY'),
interface_type , --INTERFACE_TYPE
'PON_AUC_BAD_PBTYPE_GLOBAL' , -- ERROR_MESSAGE_NAME
batch_id , -- BATCH_ID
'PON_ITEM_PRICES_INTERFACE' , -- ENTITY_TYPE
auction_header_id , -- AUCTION_HEADER_ID
'LINENUMBER' , -- TOKEN1_NAME
null, -- TOKEN1_VALUE
interface_line_id
FROM pon_item_prices_interface p1
where price_break_type = 'CUMULATIVE'
AND Nvl((SELECT global_agreement_flag FROM pon_auction_headers_all WHERE auction_header_id = l_auction_header_id),'N') = 'Y'
AND ORDER_TYPE_LOOKUP_CODE NOT IN( 'AMOUNT','FIXED PRICE')
AND Nvl(clm_info_flag,'N') <> 'Y'
AND group_type <> 'GROUP'
AND batch_id = p_batch_id;
insert into PON_INTERFACE_ERRORS
(interface_type,
column_name,
error_value,
error_message_name,
table_name,
batch_id,
interface_line_id)
select interface_type,
fnd_message.get_string('PON','PON_AUCTS_LINENUMBER'),
null,
'PON_AUCTS_PB_TBL_LINE_ERR',
'PON_ITEM_PRICES_INTERFACE',
batch_id,
interface_line_id
from pon_item_prices_interface p1
where (PURCHASE_BASIS = 'TEMP LABOR' and order_type_lookup_code = 'RATE')
AND price_break_type = 'CUMULATIVE'
AND Nvl(clm_info_flag,'N') <> 'Y'
AND group_type <> 'GROUP'
AND batch_id = p_batch_id;
UPDATE pon_item_prices_interface p1
SET price_break_type = l_price_break_type,price_break_neg_flag = l_price_break_neg_flag
WHERE batch_id = p_batch_id;