[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Update_ListPrice(
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_txn_hdr_rec_type
,p_item_cost_tbl IN dpp_txn_line_tbl_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_ListPrice';
l_exe_update_rec DPP_ExecutionDetails_PVT.DPP_EXE_UPDATE_REC_TYPE;
l_status_Update_tbl DPP_ExecutionDetails_PVT.dpp_status_Update_tbl_type;
SELECT DECODE( p_control_level, 1, mp.master_organization_id, 2, mp.organization_id) organization_id,
msi.concatenated_segments item_number
FROM mtl_parameters mp,
financials_system_params_all fspa,
mtl_system_items_kfv msi
WHERE mp.organization_id = fspa.inventory_organization_id and
mp.organization_id = msi.organization_id and
msi.inventory_item_id = p_inventory_item_id and
fspa.org_id = p_org_id;
SAVEPOINT Update_ListPrice_PVT;
SELECT control_level
INTO l_control_level
FROM mtl_item_attributes_v miav,
mtl_item_attr_appl_inst_v miaaiv
WHERE status_control_code IS NULL
AND miaaiv.attribute_name = miav.attribute_name
AND miaaiv.attribute_name = 'MTL_SYSTEM_ITEMS.LIST_PRICE_PER_UNIT';
inv_item_grp.Update_Item(p_commit => fnd_api.g_FALSE
, p_lock_rows => fnd_api.g_TRUE
, p_validation_level => fnd_api.g_VALID_LEVEL_FULL
, p_Item_rec => l_item_rec
, x_Item_rec => l_x_item_rec
, x_return_status => l_return_status
, x_Error_tbl => l_error_tbl
, p_Template_Id => NULL
, p_Template_Name => NULL
, p_Revision_rec => l_revision_rec
);
l_item_cost_tbl(i).update_status := 'Y';
INSERT INTO DPP_OUTPUT_XML_GT(Item_Number,
NewPrice,
Currency,
Reason_For_Failure)
VALUES(l_item_cost_tbl(i).item_number,
l_item_rec.list_price_per_unit,
l_item_cost_tbl(i).Currency,
NULL);
l_item_cost_tbl(i).update_status := 'N';
INSERT INTO DPP_OUTPUT_XML_GT(
Item_Number,NewPrice,
Currency,Reason_For_Failure)
VALUES(
l_item_cost_tbl(i).item_number,l_item_rec.list_price_per_unit,
l_item_cost_tbl(i).Currency,l_item_cost_tbl(i).Reason_For_Failure);
l_item_cost_tbl(i).update_status := 'N';
SELECT fnd_message.get_string('DPP','DPP_INVALID_ITEM')
INTO l_reason
FROM dual;
SELECT DISTINCT(concatenated_segments)
INTO l_item_number
FROM mtl_system_items_kfv
WHERE inventory_item_id = l_item_cost_tbl(i).inventory_item_id;
INSERT INTO DPP_OUTPUT_XML_GT(Item_Number,
NewPrice,
Currency,
Reason_For_Failure)
VALUES(l_item_number,
NVL(l_to_amount,l_item_cost_tbl(i).new_price),
l_item_cost_tbl(i).Currency,
l_reason);
l_status_Update_tbl(i).transaction_line_id := l_item_cost_tbl(i).transaction_line_id;
l_status_Update_tbl(i).update_status := l_item_cost_tbl(i).update_status;
l_queryCtx := dbms_xmlquery.newContext('SELECT '||l_Transaction_Number||' TXNNUMBER,
CURSOR (Select Item_Number ITEMNUMBER,
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_LISTPRICE_PVT.Update_ListPrice');
SELECT DECODE(x_return_status,'S','SUCCESS','WARNING')
INTO l_execution_status
FROM DUAL;
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
);
FND_MESSAGE.set_name('DPP', 'DPP_UPDATE_ITEM_ERR');
ROLLBACK TO UPDATE_LISTPRICE_PVT;
ROLLBACK TO UPDATE_LISTPRICE_PVT;
ROLLBACK TO UPDATE_LISTPRICE_PVT;
ROLLBACK TO UPDATE_LISTPRICE_PVT;
END Update_ListPrice;