[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
Select To_Number(((To_Char(Sysdate, 'J') - 1 ) * 86400) + To_Char(Sysdate, 'SSSss'))
Into STime From Sys.Dual;
SELECT
count(*)
INTO
l_interface_pending_count
FROM
mtl_transactions_interface
WHERE
source_code = 'Price Protection' AND
source_header_id = in_execution_detail_id AND
transaction_header_id = in_transaction_header_id AND
process_flag = 1;
Select To_Number(((To_Char(Sysdate, 'J') - 1 ) * 86400) + To_Char(Sysdate, 'SSSss'))
Into ETime From Sys.Dual;
PROCEDURE Update_ItemCost
(p_api_Version IN NUMBER,
p_Init_msg_List IN VARCHAR2 := fnd_api.g_False,
p_Commit IN VARCHAR2 := fnd_api.g_False,
p_Validation_Level IN NUMBER := fnd_api.g_Valid_Level_Full,
x_Return_Status OUT NOCOPY VARCHAR2,
x_msg_Count OUT NOCOPY NUMBER,
x_msg_Data OUT NOCOPY VARCHAR2,
p_txn_hdr_rec IN DPP_CST_HDR_REC_TYPE,
p_Item_Cost_Tbl IN DPP_TXN_LINE_TBL_TYPE)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_ItemCost';
l_transaction_type_id NUMBER := 80; -- seeded type for average cost update
l_transaction_action_id NUMBER := 24; -- seeded for cost update
l_insert_xla_header VARCHAR2(1) := 'N';
l_exe_update_rec DPP_ExecutionDetails_PVT.dpp_exe_update_rec_type;
l_status_Update_tbl DPP_ExecutionDetails_PVT.dpp_status_Update_tbl_type;
l_module CONSTANT VARCHAR2(100) := 'dpp.plsql.DPP_ITEMCOST_PVT.UPDATE_ITEMCOST';
SELECT mp.organization_id,msi.primary_uom_code transaction_uom,
msi.concatenated_segments item_number, mp.primary_cost_method, cod.organization_name,
mp.default_cost_group_id cost_group_id,
cod.currency_code
FROM mtl_parameters mp,
mtl_system_items_kfv msi,
cst_organization_definitions cod
WHERE mp.organization_id = msi.organization_id
AND mp.primary_cost_method IN (1,2)
AND msi.inventory_item_id = p_inventory_item_id
AND msi.inventory_asset_flag = 'Y'
AND cod.organization_id = mp.organization_id
AND cod.operating_unit = p_org_id
-- AND NVL(mp.consigned_flag,'N') = 'N'
AND mp.process_enabled_flag = 'N'
AND NVL(cod.disable_date,p_trunc_sysdate + 1) > p_trunc_sysdate;
SELECT DISTINCT organization_id from cst_item_costs
WHERE cost_type_id = p_cost_type_id
AND request_id = p_request_id;
ELSIF l_txn_hdr_rec.last_updated_by IS NULL THEN
FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
FND_MESSAGE.set_token('ID', 'User ID - Last_Updated_By');
SELECT user_name
INTO l_user_name
FROM fnd_user
WHERE user_id = l_txn_hdr_rec.last_updated_by;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd
WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (l_txn_hdr_rec.org_id)
AND fpov.level_id = 10004
AND furgd.user_id = fpov.level_value
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_txn_hdr_rec.last_updated_by
AND ROWNUM = 1;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd,
per_security_profiles psp
WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
AND ((psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NOT NULL
AND EXISTS (SELECT 1
FROM hr_operating_units hr
WHERE hr.business_group_id = psp.business_group_id
AND hr.usable_flag IS NULL
AND hr.organization_id =
l_txn_hdr_rec.org_id))
OR (psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NULL)
OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
AND EXISTS (SELECT 1
FROM per_organization_list per,
hr_operating_units hr
WHERE per.security_profile_id = psp.security_profile_id
AND hr.organization_id = per.organization_id
AND hr.usable_flag IS NULL
AND per.organization_id = l_txn_hdr_rec.org_id)))
AND fpov.level_id = 10004
AND furgd.user_id = fpov.level_value
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_txn_hdr_rec.last_updated_by
AND ROWNUM = 1;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd
WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (l_txn_hdr_rec.org_id)
AND fpov.level_id = 10003
AND frv.responsibility_id = fpov.level_value
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_txn_hdr_rec.last_updated_by
AND ROWNUM = 1;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd,
per_security_profiles psp
WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
AND ((psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NOT NULL
AND EXISTS (SELECT 1
FROM hr_operating_units hr
WHERE hr.business_group_id = psp.business_group_id
AND hr.usable_flag IS NULL
AND hr.organization_id = l_txn_hdr_rec.org_id))
OR (psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NULL)
OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
AND EXISTS (SELECT 1
FROM per_organization_list per,
hr_operating_units hr
WHERE per.security_profile_id = psp.security_profile_id
AND hr.organization_id = per.organization_id
AND hr.usable_flag IS NULL
AND per.organization_id = l_txn_hdr_rec.org_id)))
AND fpov.level_id = 10003
AND frv.responsibility_id = fpov.level_value
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_txn_hdr_rec.last_updated_by
AND ROWNUM = 1;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd
WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (l_txn_hdr_rec.org_id)
AND fpov.level_id = 10002
AND frv.application_id = fpov.level_value
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_txn_hdr_rec.last_updated_by
AND ROWNUM = 1;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd,
per_security_profiles psp
WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
AND ((psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NOT NULL
AND EXISTS (SELECT 1
FROM hr_operating_units hr
WHERE hr.business_group_id = psp.business_group_id
AND hr.usable_flag IS NULL
AND hr.organization_id = l_txn_hdr_rec.org_id))
OR (psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NULL)
OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
AND EXISTS (SELECT 1
FROM per_organization_list per,
hr_operating_units hr
WHERE per.security_profile_id = psp.security_profile_id
AND hr.organization_id = per.organization_id
AND hr.usable_flag IS NULL
AND per.organization_id = l_txn_hdr_rec.org_id)))
AND fpov.level_id = 10002
AND frv.application_id = fpov.level_value
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_txn_hdr_rec.last_updated_by
AND ROWNUM = 1;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd
WHERE fpo.profile_option_name IN ('ORG_ID', 'DEFAULT_ORG_ID')
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (l_txn_hdr_rec.org_id)
AND fpov.level_id = 10001
AND fpov.level_value = 0
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date, TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_txn_hdr_rec.last_updated_by
AND ROWNUM = 1;
SELECT frv.responsibility_id
INTO l_responsibility_id
FROM fnd_profile_options fpo,
fnd_profile_option_values fpov,
fnd_responsibility_vl frv,
fnd_user_resp_groups_direct furgd,
per_security_profiles psp
WHERE fpo.profile_option_name = 'XLA_MO_SECURITY_PROFILE_LEVEL'
AND fpo.profile_option_id = fpov.profile_option_id
AND fpov.profile_option_value = TO_CHAR (psp.security_profile_id)
AND ((psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NOT NULL
AND EXISTS (SELECT 1
FROM hr_operating_units hr
WHERE hr.business_group_id = psp.business_group_id
AND hr.usable_flag IS NULL
AND hr.organization_id = l_txn_hdr_rec.org_id))
OR (psp.view_all_organizations_flag = 'Y'
AND psp.business_group_id IS NULL)
OR (NVL (psp.view_all_organizations_flag, 'N') <> 'Y'
AND EXISTS (SELECT 1
FROM per_organization_list per,
hr_operating_units hr
WHERE per.security_profile_id = psp.security_profile_id
AND hr.organization_id = per.organization_id
AND hr.usable_flag IS NULL
AND per.organization_id = l_txn_hdr_rec.org_id)))
AND fpov.level_id = 10001
AND fpov.level_value = 0
AND frv.application_id = 9000
AND TRUNC (SYSDATE) BETWEEN NVL (frv.start_date, TRUNC (SYSDATE))
AND NVL (frv.end_date, TRUNC (SYSDATE))
AND TRUNC (SYSDATE) BETWEEN NVL (furgd.start_date,TRUNC (SYSDATE))
AND NVL (furgd.end_date, TRUNC (SYSDATE))
AND furgd.responsibility_id = frv.responsibility_id
AND furgd.responsibility_application_id = frv.application_id
AND furgd.user_id = l_txn_hdr_rec.last_updated_by
AND ROWNUM = 1;
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Price Protection responsibility not available for Last updated user'||l_user_name);
FND_GLOBAL.APPS_INITIALIZE(l_txn_hdr_rec.last_updated_by,l_responsibility_id,l_dpp_application_id);
SELECT cost_type
INTO l_cost_type
FROM cst_cost_types
WHERE cost_type_id = 8;
SELECT dpp_cst_group_id_seq.nextval
INTO l_import_cost_group_id
FROM dual;
SELECT count(*)
INTO l_bom_installed
FROM bom_parameters
WHERE organization_id = l_txn_hdr_rec.org_id;
SELECT chart_of_accounts_id
INTO l_chart_of_accounts_id
FROM gl_sets_of_books sob,
hr_operating_units hr
WHERE hr.set_of_books_id = sob.set_of_books_id
AND hr.organization_id = l_txn_hdr_rec.org_id;
SAVEPOINT Update_ItemCost_PVT;
l_status_Update_tbl(i).transaction_line_id := l_item_cost_tbl(i).transaction_line_id;
l_status_Update_tbl(i).update_status := 'Y';
SELECT NVL(ctc.item_cost,0) prior_cost
INTO l_prior_cost
FROM cst_item_costs ctc
WHERE ctc.organization_id = Item_rec.organization_id
AND ctc.inventory_item_id = l_item_cost_tbl(i).inventory_item_id
AND ctc.cost_type_id = 1;
SELECT NVL(item_cost,0)
INTO l_prior_cost
FROM cst_quantity_layers
WHERE organization_id = Item_rec.organization_id
AND inventory_item_id = l_item_cost_tbl(i).inventory_item_id
AND cost_group_id = item_rec.cost_group_id;
INSERT INTO DPP_OUTPUT_XML_GT(Item_Number,
NewPrice,
Currency,
Inventory_Org_Name,
inventory_item_id,
transaction_subtype,
transaction_line_id,
organization_id)
VALUES (l_item_cost_tbl(i).item_number,
l_item_cost_tbl(i).new_price,
l_item_cost_tbl(i).currency,
Item_rec.organization_name,
l_item_cost_tbl(i).inventory_item_id,
l_txn_subtype,
l_item_cost_tbl(i).transaction_line_id,
Item_rec.organization_id);
UPDATE DPP_OUTPUT_XML_GT
SET reason_for_failure = l_item_cost_tbl(i).Reason_for_failure
WHERE organization_id = Item_rec.organization_id
AND inventory_item_id = l_item_cost_tbl(i).inventory_item_id;
l_status_Update_tbl(i).update_status := 'N';
SELECT 1
INTO l_incorrect_price_exists
FROM dual
WHERE EXISTS (SELECT cis.organization_id
FROM cst_item_costs cis,
org_organization_definitions ood
WHERE cis.organization_id = ood.organization_id
AND cis.organization_id = Item_rec.organization_id
AND ood.operating_unit = l_txn_hdr_rec.org_id
AND cis.cost_type_id = 1
AND cis.inventory_item_id = l_item_cost_tbl(i).inventory_item_id
AND cis.item_cost > 0
AND (((cis.item_cost-l_to_amount) >0
AND l_item_cost_tbl(i).price_change <0)
OR ((cis.item_cost-l_to_amount) <0 AND l_item_cost_tbl(i).price_change >0)));
SELECT 1
INTO l_incorrect_price_exists
FROM dual
WHERE EXISTS (SELECT cql.organization_id
FROM cst_quantity_layers cql,
org_organization_definitions ood
WHERE cql.organization_id = ood.organization_id
AND cql.organization_id = Item_rec.organization_id
AND ood.operating_unit = l_txn_hdr_rec.org_id
AND cql.inventory_item_id = l_item_cost_tbl(i).inventory_item_id
AND cql.cost_group_id = item_rec.cost_group_id
AND cql.item_cost > 0
AND(((cql.item_cost -l_to_amount) > 0
AND l_item_cost_tbl(i).price_change < 0)
OR((item_cost -l_to_amount) < 0 AND l_item_cost_tbl(i).price_change > 0)));
UPDATE DPP_OUTPUT_XML_GT
SET reason_for_failure = l_item_cost_tbl(i).Reason_for_failure
WHERE organization_id = Item_rec.organization_id
AND inventory_item_id = l_item_cost_tbl(i).inventory_item_id;
l_status_Update_tbl(i).update_status := 'N';
l_insert_xla_header := 'Y';
INSERT INTO cst_Item_cst_dtls_InterFace(Inventory_Item_Id,
Organization_Id,
Item_Cost,
Basis_Type,
Usage_Rate_Or_Amount,
Cost_Type_Id,
Cost_Type,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Group_Id,
Process_Flag,
Cost_Element,
Cost_Element_Id,
Net_Yield_Or_Shrinkage_Factor,
Level_Type)
VALUES(L_item_cost_tbl(i).inventory_item_id,
Item_rec.Organization_Id,
NULL,
1,
l_To_Amount,
l_Cost_Type_Id,
l_Cost_Type,
l_sysDate,
l_txn_hdr_rec.Last_Updated_By,
l_sysDate,
l_txn_hdr_rec.Last_Updated_By,
l_Import_Cost_Group_Id,
1,
NULL,
1,
1,
1);
l_status_Update_tbl(i).update_status := 'Y';
INSERT INTO mtl_Transactions_InterFace(Transaction_InterFace_Id,
Transaction_Header_Id,
Source_Code,
Source_Line_Id,
Source_Header_Id,
Process_Flag,
Transaction_Mode,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Organization_Id,
Transaction_Quantity,
Transaction_uom,
Transaction_Date,
Transaction_Type_Id,
Inventory_Item_Id,
New_Average_Cost,
Currency_Code,
Cost_Group_Id,
Material_Account,
Transaction_Reference)
VALUES (dpp_mtl_txn_IfAce_Id_seq.Nextval,
l_txn_hdr_rec.Transaction_Header_Id,
'Price Protection',
l_txn_hdr_rec.Execution_Detail_Id,
l_txn_hdr_rec.Execution_Detail_Id,
1, -- Process is 1
3, -- Background is 3
l_sysDate,
l_txn_hdr_rec.Last_Updated_By,
SYSDATE,
l_txn_hdr_rec.Last_Updated_By,
Item_rec.Organization_Id,
1,
Item_rec.Transaction_uom,
l_sysDate,
l_Transaction_Type_Id,
L_item_cost_tbl(i).inventory_item_id,
l_To_Amount,
L_item_cost_tbl(i).currency,
Item_rec.Cost_Group_Id,
Nvl(x_Return_ccId,l_txn_hdr_rec.Cost_Adjustment_Account),
l_txn_SubType);
INSERT INTO mtl_txn_Cost_det_InterFace(Transaction_InterFace_Id,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Organization_Id,
Cost_Element_Id,
Level_Type,
New_Average_Cost)
VALUES (dpp_mtl_txn_IfAce_Id_seq.Currval,
l_sysDate,
l_txn_hdr_rec.Last_Updated_By,
l_sysDate,
l_txn_hdr_rec.Last_Updated_By,
Item_rec.Organization_Id,
1,
1,
l_To_Amount);
l_status_Update_tbl(i).update_status := 'Y';
l_status_Update_tbl(i).update_status := 'N';
UPDATE DPP_OUTPUT_XML_GT
SET reason_for_failure = l_item_cost_tbl(i).Reason_for_failure
WHERE organization_id = Item_rec.organization_id
AND inventory_item_id = l_item_cost_tbl(i).inventory_item_id;
INSERT INTO cst_Item_cst_dtls_InterFace(Inventory_Item_Id,
Organization_Id,
Item_Cost,
Basis_Type,
Usage_Rate_Or_Amount,
Cost_Type_Id,
Cost_Type,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Group_Id,
Process_Flag,
Cost_Element,
Cost_Element_Id,
Net_Yield_Or_Shrinkage_Factor,
Level_Type)
VALUES(L_item_cost_tbl(i).inventory_item_id,
Item_rec.Organization_Id,
NULL,
1,
l_To_Amount,
l_Cost_Type_Id,
l_Cost_Type,
l_sysDate,
l_txn_hdr_rec.Last_Updated_By,
l_sysDate,
l_txn_hdr_rec.Last_Updated_By,
l_Import_Cost_Group_Id,
1,
NULL,
1,
1,
1);
l_status_Update_tbl(i).update_status := 'Y';
INSERT INTO mtl_Transactions_InterFace(Transaction_InterFace_Id,
Transaction_Header_Id,
Source_Code,
Source_Line_Id,
Source_Header_Id,
Process_Flag,
Transaction_Mode,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Organization_Id,
Transaction_Quantity,
Transaction_uom,
Transaction_Date,
Transaction_Type_Id,
Inventory_Item_Id,
New_Average_Cost,
Currency_Code,
Cost_Group_Id,
Material_Account,
Transaction_Reference)
VALUES (dpp_mtl_txn_IfAce_Id_seq.Nextval,
l_txn_hdr_rec.Transaction_Header_Id,
'Price Protection',
l_txn_hdr_rec.Execution_Detail_Id,
l_txn_hdr_rec.Execution_Detail_Id,
1, -- Process is 1
3, -- Background is 3
l_sysDate,
l_txn_hdr_rec.Last_Updated_By,
l_sysDate,
l_txn_hdr_rec.Last_Updated_By,
Item_rec.Organization_Id,
1,
Item_rec.Transaction_uom,
l_sysDate,
l_Transaction_Type_Id,
L_item_cost_tbl(i).inventory_item_id,
l_To_Amount,
L_item_cost_tbl(i).currency,
Item_rec.Cost_Group_Id,
Nvl(x_Return_ccId,l_txn_hdr_rec.Cost_Adjustment_Account),
l_txn_SubType);
INSERT INTO mtl_txn_Cost_det_InterFace(Transaction_InterFace_Id,
Last_Update_Date,
Last_Updated_By,
Creation_Date,
Created_By,
Organization_Id,
Cost_Element_Id,
Level_Type,
New_Average_Cost)
VALUES (dpp_mtl_txn_IfAce_Id_seq.Currval,
l_sysDate,
l_txn_hdr_rec.Last_Updated_By,
l_sysDate,
l_txn_hdr_rec.Last_Updated_By,
Item_rec.Organization_Id,
1,
1,
l_To_Amount);
l_status_Update_tbl(i).update_status := 'Y';
argument7 => 1); --Delete successful rows
argument6 => 'DPP Std Cost Update - Execution Detail ID: '||l_txn_hdr_rec.execution_detail_id,
argument7 => 1,
argument8 => 1,
argument9 => 3,
argument10 => null,
argument11 => null,
argument12 => null,
argument13 => null,
argument14 => null,
argument15 => null,
argument16 => null,
argument17 => null,
argument18 => null,
argument19 => null,
argument20 => null,
argument21 => null,
argument22 => null,
argument23 => 1,
argument24 => 2);
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Std Cost Update Request ID: '||l_cost_upd_req_id);
UPDATE DPP_OUTPUT_XML_GT
SET reason_for_failure = 'Std Cost Update Request ID: '||l_cost_upd_req_id||' '|| l_dev_phase||' with '||l_dev_status
WHERE organization_id = Organization_Rec.organization_id;
FOR i IN l_status_Update_tbl.FIRST..l_status_Update_tbl.LAST
LOOP
l_status_Update_tbl(i).update_status := 'N';
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'After Std Cost Update request:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
UPDATE DPP_OUTPUT_XML_GT
SET reason_for_failure = 'Item Cost Import Request ID: '||l_cost_import_req_id||' '|| l_dev_phase||' with '||l_dev_status;
FOR i IN l_status_Update_tbl.FIRST..l_status_Update_tbl.LAST
LOOP
l_status_Update_tbl(i).update_status := 'N';
SAVEPOINT Update_ItemCost_PVT;
SELECT Inventory_Item_Id,
Error_Explanation
BULK COLLECT INTO Inventory_Item_Ids,
Error_Explanations
FROM cst_Item_cst_dtls_InterFace
WHERE Cost_Type_Id = l_Cost_Type_Id
AND Group_Id = l_Import_Cost_Group_Id
AND Process_Flag = 3;
UPDATE DPP_OUTPUT_XML_GT
SET reason_for_failure = error_explanations(indx)
WHERE inventory_item_id = inventory_item_ids(indx);
SELECT COUNT(* )
INTO l_InterFace_Pending_Count
FROM mtl_Transactions_InterFace
WHERE Source_Code = 'Price Protection'
AND Source_Header_Id = l_txn_hdr_rec.Execution_Detail_Id
AND Transaction_Header_Id = l_txn_hdr_rec.Transaction_Header_Id
AND Process_Flag = 1;
SELECT Source_Line_Id,
Nvl(Error_Explanation,Error_Code) Error_Explanation
BULK COLLECT INTO Source_Line_Ids,
Error_Explanations
FROM mtl_Transactions_InterFace
WHERE Source_Code = 'Price Protection'
AND Source_Header_Id = l_txn_hdr_rec.Execution_Detail_Id
AND Transaction_Header_Id = l_txn_hdr_rec.Transaction_Header_Id
AND Process_Flag = 3;
UPDATE DPP_OUTPUT_XML_GT
SET reason_for_failure = error_explanations(indx)
WHERE transaction_line_id = source_line_ids(indx);
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Inserting into SLA tables: ' || l_return_status);
IF l_insert_xla_header = 'Y' THEN
BEGIN
INSERT INTO DPP_XLA_HEADERS(
transaction_header_id
,pp_transaction_type
,base_transaction_header_id
,processed_flag
,creation_date
,created_by
,last_update_date
,last_updated_by
,last_update_login)
VALUES(
l_txn_hdr_rec.Transaction_Header_ID
,'COST_UPDATE'
,l_txn_hdr_rec.Execution_Detail_ID
,l_processed_flag
,l_sysdate
,l_txn_hdr_rec.last_updated_by
,l_sysdate
,l_txn_hdr_rec.last_updated_by
,FND_GLOBAL.login_id);
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'After SLA Hdr Insert:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Inserting into SLA Lines table for Avg Cost Update ');
SELECT
mmt.transaction_id,
dpp_gt.transaction_line_id,
dpp_gt.transaction_subtype
BULK COLLECT INTO
transaction_ids,
transaction_line_ids,
transaction_subtypes
FROM
mtl_material_transactions mmt,
DPP_OUTPUT_XML_GT dpp_gt
WHERE
mmt.transaction_source_type_id = 13 -- Inventory
AND mmt.source_line_id = l_txn_hdr_rec.Execution_Detail_ID
AND mmt.transaction_type_id = l_transaction_type_id
AND mmt.transaction_action_id = l_transaction_action_id
AND dpp_gt.organization_id = mmt.organization_id
AND dpp_gt.inventory_item_id = mmt.inventory_item_id;
INSERT INTO DPP_XLA_LINES(
transaction_header_id
,transaction_line_id
,base_transaction_header_id
,base_transaction_line_id
,transaction_sub_type
,creation_date
,created_by
,last_update_date
,last_updated_by)
VALUES(
l_txn_hdr_rec.Transaction_Header_ID
,transaction_line_ids(indx)
,l_txn_hdr_rec.Execution_Detail_ID
,transaction_ids(indx)
,transaction_subtypes(indx)
,l_sysdate
,l_txn_hdr_rec.last_updated_by
,l_sysdate
,l_txn_hdr_rec.last_updated_by);
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'No Data Found for SLA lines table insertion - Avg Costing...');
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Inserting into SLA Lines table for Std Cost Update: ');
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Before SLA Line Insert - Std:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
SELECT
mmt.transaction_id,
dpp_gt.transaction_line_id,
dpp_gt.transaction_subtype
BULK COLLECT INTO
transaction_ids,
transaction_line_ids,
transaction_subtypes
FROM
mtl_material_transactions mmt,
cst_cost_updates ccu,
DPP_OUTPUT_XML_GT dpp_gt
WHERE
mmt.transaction_source_id = ccu.cost_update_id
AND mmt.transaction_source_type_id = 11
AND mmt.transaction_action_id = l_transaction_action_id
AND ccu.description = 'DPP Std Cost Update - Execution Detail ID: '||l_txn_hdr_rec.execution_detail_id
AND ccu.cost_type_id = l_cost_type_id
AND dpp_gt.organization_id = mmt.organization_id
AND dpp_gt.inventory_item_id = mmt.inventory_item_id;
INSERT INTO DPP_XLA_LINES(
transaction_header_id
,transaction_line_id
,base_transaction_header_id
,base_transaction_line_id
,transaction_sub_type
,creation_date
,created_by
,last_update_date
,last_updated_by)
VALUES(
l_txn_hdr_rec.Transaction_Header_ID
,transaction_line_ids(indx)
,l_txn_hdr_rec.Execution_Detail_ID
,transaction_ids(indx)
,transaction_subtypes(indx)
,l_sysdate
,l_txn_hdr_rec.last_updated_by
,l_sysdate
,l_txn_hdr_rec.last_updated_by);
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'No Data Found for SLA table insertion...');
fnd_message.set_token('ROUTINE', 'DPP_ITEMCOST_PVT.Update_ItemCost-SLA Tables Insertion');
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Error in SLA Tables Insertion:' || sqlerrm);
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Before Exe Dtls Update:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
SELECT DECODE(l_return_status,FND_API.G_RET_STS_SUCCESS,'SUCCESS','WARNING')
INTO l_execution_status
FROM DUAL;
l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_Transaction_Number||' TXNNUMBER,
CURSOR (Select Item_Number ITEMNUMBER,
inventory_org_name ORGNAME,
NewPrice NEWPRICE,
Currency CURRENCY,
Reason_For_Failure REASON
from DPP_OUTPUT_XML_GT
where Reason_For_Failure IS NOT NULL) TRANSACTION from dual');
l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_Transaction_Number||' TXNNUMBER from dual');
fnd_message.set_token('ROUTINE', 'DPP_ITEMCOST_PVT.Update_ItemCost-XML Generation');
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'Status before calling update API: ' || l_return_status);
l_exe_update_rec.Transaction_Header_ID := l_txn_hdr_rec.Transaction_Header_ID;
l_exe_update_rec.Org_ID := l_txn_hdr_rec.Org_ID;
l_exe_update_rec.Execution_Detail_ID := l_txn_hdr_rec.Execution_Detail_ID;
l_exe_update_rec.Output_XML := l_output_xml;
l_exe_update_rec.execution_status := l_execution_status;
l_exe_update_rec.Execution_End_Date := SYSDATE;
l_exe_update_rec.Provider_Process_Id := l_txn_hdr_rec.Provider_Process_Id;
l_exe_update_rec.Provider_Process_Instance_id := l_txn_hdr_rec.Provider_Process_Instance_id;
l_exe_update_rec.Last_Updated_By := l_txn_hdr_rec.Last_Updated_By;
DPP_ExecutionDetails_PVT.Update_ExecutionDetails(
p_api_version => l_api_version
,p_init_msg_list => FND_API.G_FALSE
,p_commit => FND_API.G_FALSE
,p_validation_level => FND_API.G_VALID_LEVEL_FULL
,x_return_status => l_return_status
,x_msg_count => l_msg_count
,x_msg_data => l_msg_data
,p_EXE_UPDATE_rec => l_exe_update_rec
,p_status_Update_tbl=> l_status_Update_tbl
);
dpp_utility_pvt.debug_message(FND_LOG.LEVEL_STATEMENT, l_module, 'After Exe Dtls Update:'||to_char(sysdate,'dd-mon-yyyy hh24:mi:ss'));
ROLLBACK TO UPDATE_ITEMCOST_PVT;
ROLLBACK TO UPDATE_ITEMCOST_PVT;
fnd_message.set_token('ROUTINE', 'DPP_ITEMCOST_PVT.Update_ItemCost');
END Update_ItemCost;