[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE Select_CoveredInventory
(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_Inv_hdr_rec IN DPP_INV_HDR_REC_TYPE,
p_Covered_Inv_Tbl IN OUT NOCOPY DPP_INV_COV_TBL_TYPE)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Select_CoveredInventory';
SELECT sum(case when ( (NVL(moqd.orig_date_received,moqd.date_received) >= p_effective_start_date
AND NVL(moqd.orig_date_received,moqd.date_received) < p_effective_end_date))
--BETWEEN p_effective_start_date and p_effective_end_date)
then moqd.transaction_quantity else 0 end) covered_qty,
sum(moqd.transaction_quantity) onhand_qty,
moqd.transaction_uom_code
FROM mtl_onhand_quantities_detail moqd,
org_organization_definitions ood,
mtl_parameters mp
WHERE moqd.organization_id = ood.organization_id
AND moqd.inventory_item_id = p_inventory_item_id
AND mp.organization_id = ood.organization_id
AND NVL(ood.disable_date,SYSDATE + 1) > SYSDATE
AND ood.operating_unit = p_org_id
AND moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
AND moqd.PLANNING_TP_TYPE = 2
AND moqd.OWNING_TP_TYPE = 2
AND moqd.IS_CONSIGNED = 2
GROUP BY moqd.transaction_uom_code;
SELECT
SUM(moqd.transaction_quantity) sum,
ood.organization_name warehouse,
ood.organization_id warehouse_id
FROM
mtl_onhand_quantities_detail moqd,
org_organization_definitions ood,
mtl_parameters mp
WHERE moqd.organization_id = ood.organization_id
AND moqd.inventory_item_id = p_inventory_item_id
AND ood.operating_unit = p_org_id
AND mp.organization_id = ood.organization_id
AND NVL(ood.disable_date,SYSDATE + 1) > SYSDATE
AND (NVL(moqd.orig_date_received,moqd.date_received) >= p_effective_start_date
AND NVL(moqd.orig_date_received,moqd.date_received) < p_effective_end_date)
--BETWEEN p_effective_start_date and p_effective_end_date
AND moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
AND moqd.PLANNING_TP_TYPE = 2
AND moqd.OWNING_TP_TYPE = 2
AND moqd.IS_CONSIGNED = 2
GROUP BY ood.organization_name,ood.organization_id;
SELECT
(NVL(moqd.orig_date_received,moqd.date_received)) date_received,
SUM(moqd.transaction_quantity) sum
FROM
mtl_onhand_quantities_detail moqd,
org_organization_definitions ood,
mtl_parameters mp
WHERE
moqd.organization_id = ood.organization_id AND
moqd.inventory_item_id = p_inventory_item_id AND
ood.operating_unit = p_org_id AND
mp.organization_id = ood.organization_id AND
NVL(ood.disable_date,SYSDATE + 1) > SYSDATE AND
moqd.organization_id = p_warehouse_id AND
moqd.PLANNING_ORGANIZATION_ID = mp.organization_id
AND moqd.OWNING_ORGANIZATION_ID = mp.organization_id
AND moqd.PLANNING_TP_TYPE = 2
AND moqd.OWNING_TP_TYPE = 2
AND moqd.IS_CONSIGNED = 2
GROUP BY (NVL(moqd.orig_date_received,moqd.date_received));
SAVEPOINT Select_CoveredInventory_PVT;
l_covered_inv_wh_tbl.delete;
SELECT primary_uom_code
INTO l_primary_uom_code
FROM mtl_system_items msi,
mtl_parameters mp
WHERE inventory_item_id = l_covered_inv_tbl(i).inventory_item_id
AND mp.organization_id = msi.organization_id
AND mp.organization_id = mp.master_organization_id
AND rownum = 1;
DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'select_coveredinventory(): x_return_status: ' || x_return_status);
ROLLBACK TO Select_CoveredInventory_PVT;
ROLLBACK TO Select_CoveredInventory_PVT;
ROLLBACK TO Select_CoveredInventory_PVT;
fnd_message.set_token('ROUTINE', 'DPP_COVEREDINVENTORY_PVT.Select_CoveredInventory');
END Select_CoveredInventory;
UPDATE DPP_EXECUTION_DETAILS
SET execution_end_date = sysdate
,execution_status = DECODE(l_return_status,FND_API.G_RET_STS_SUCCESS,'SUCCESS','WARNING')
,last_update_date = sysdate
,last_updated_by = l_inv_hdr_rec.Last_Updated_By
,last_update_login = l_inv_hdr_rec.Last_Updated_By
,provider_process_id = l_inv_hdr_rec.Provider_Process_Id
,provider_process_instance_id = l_inv_hdr_rec.Provider_Process_Instance_id
,output_xml = XMLTYPE(l_inv_hdr_rec.Output_XML)
WHERE execution_detail_id = l_inv_hdr_rec.Execution_Detail_ID;
DPP_COVEREDINVENTORY_PVT.Update_CoveredInventory(
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_inv_hdr_rec => l_inv_hdr_rec
,p_covered_inv_tbl => l_covered_inv_tbl
);
PROCEDURE Update_CoveredInventory(
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_inv_hdr_rec IN dpp_inv_hdr_rec_type
,p_covered_inv_tbl IN dpp_inv_cov_tbl_type
)
IS
l_api_name CONSTANT VARCHAR2(30) := 'Update_CoveredInventory';
SAVEPOINT Update_CoveredInventory_PVT;
IF l_inv_hdr_rec.Last_Updated_By IS NULL THEN
FND_MESSAGE.set_name('DPP', 'DPP_API_INPUT_ID_MISSING');
FND_MESSAGE.set_token('ID', 'Last Updated By');
UPDATE DPP_TRANSACTION_LINES_ALL
SET covered_inventory = NVL(l_covered_inv_tbl(i).Covered_quantity,0),
approved_inventory = NVL(l_covered_inv_tbl(i).Covered_quantity,0),
onhand_inventory = NVL(l_covered_inv_tbl(i).Onhand_Quantity,0),
UOM = l_covered_inv_tbl(i).UOM_Code,
last_update_date = l_sysdate,
last_updated_by = l_inv_hdr_rec.Last_Updated_By,
last_calculated_by = l_inv_hdr_rec.Last_Updated_By,
last_update_login = FND_GLOBAL.LOGIN_ID,
last_calculated_date = l_sysdate
WHERE transaction_line_id = l_covered_inv_tbl(i).Transaction_Line_Id;
DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_EXCEPTION, 'dpp.plsql.' || L_FULL_NAME, substr(('Error in Updating DPP_TRANSACTION_LINES_ALL: ' || SQLERRM || ' from Update Covered Inventory API'),1,4000));
SELECT nvl(create_claim_price_increase,'N')
INTO l_price_change_flag
FROM ozf_supp_trd_prfls_all ostp,
dpp_transaction_headers_all dtha
WHERE ostp.supplier_id = to_number(dtha.vendor_id)
AND ostp.supplier_site_id = to_number(dtha.vendor_site_id)
AND ostp.org_id = to_number(dtha.org_id)
AND dtha.transaction_header_id = l_inv_hdr_rec.transaction_header_id;
UPDATE dpp_transaction_lines_all dtla
SET dtla.claim_amount = dtla.approved_inventory * price_change,
dtla.object_version_number = dtla.object_version_number +1,
dtla.last_updated_by = nvl(l_user_id,0),
dtla.last_update_login = nvl(l_user_id,0),
dtla.last_update_date = sysdate
WHERE dtla.transaction_header_id = l_inv_hdr_rec.transaction_header_id
AND dtla.transaction_line_id = l_covered_inv_tbl(i).transaction_line_id
AND dtla.price_change > 0;
UPDATE dpp_transaction_lines_all dtla
SET dtla.claim_amount = dtla.approved_inventory * price_change,
dtla.object_version_number = dtla.object_version_number +1,
dtla.last_updated_by = nvl(l_user_id,0),
dtla.last_update_login = nvl(l_user_id,0),
dtla.last_update_date = sysdate
WHERE dtla.transaction_header_id = l_inv_hdr_rec.transaction_header_id
AND dtla.transaction_line_id = l_covered_inv_tbl(i).transaction_line_id
AND dtla.price_change <> 0;
l_txn_lines_tbl(i).last_update_date := l_sysdate;
l_txn_lines_tbl(i).last_updated_by := l_inv_hdr_rec.Last_Updated_By;
l_txn_lines_tbl(i).created_by := l_inv_hdr_rec.Last_Updated_By;
l_txn_lines_tbl(i).last_update_login := FND_GLOBAL.LOGIN_ID;
l_txn_lines_tbl(i).last_calculated_by := l_inv_hdr_rec.Last_Updated_By;
SELECT
inventory_details_id
BULK COLLECT INTO
inventory_details_ids
FROM
dpp_inventory_details_all
WHERE
org_id = l_inv_hdr_rec.org_id
AND transaction_line_id = l_covered_inv_tbl(i).Transaction_Line_Id;
DELETE
FROM DPP_INVENTORY_DETAILS_ADJ_ALL
WHERE INVENTORY_DETAILS_ID = inventory_details_ids(indx);
DELETE
FROM DPP_INVENTORY_DETAILS_ALL
WHERE org_id = l_inv_hdr_rec.org_id
AND transaction_line_id = l_covered_inv_tbl(i).Transaction_Line_Id;
fnd_message.set_token('ROUTINE', 'DPP_COVEREDINVENTORY_PVT.Update_CoveredInventory - Delete rows');
SELECT DPP_INVENTORY_DETAILS_SEQ.nextval
INTO l_inv_details_id
FROM DUAL;
INSERT INTO DPP_INVENTORY_DETAILS_ALL(
inventory_details_id,
transaction_line_id,
quantity,
uom,
include_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
inventory_item_id,
org_id,
organization_id,
object_version_number)
VALUES( l_inv_details_id,
l_covered_inv_tbl(i).Transaction_Line_Id,
NVL(l_covered_inv_tbl(i).wh_line_tbl(j).Covered_quantity,0),
l_covered_inv_tbl(i).UOM_Code,
'N',
l_sysdate,
l_inv_hdr_rec.Last_Updated_By,
l_sysdate,
l_inv_hdr_rec.Last_Updated_By,
l_inv_hdr_rec.Last_Updated_By,
l_covered_inv_tbl(i).inventory_item_id,
l_inv_hdr_rec.org_id,
l_covered_inv_tbl(i).wh_line_tbl(j).Warehouse_id,
1);
INSERT INTO DPP_INVENTORY_DETAILS_ADJ_ALL(
inv_details_adj_id,
inventory_details_id,
date_received,
days_out,
quantity,
uom,
comments,
include_flag,
creation_date,
created_by,
last_update_date,
last_updated_by,
last_update_login,
org_id,
object_version_number)
VALUES(dpp_inv_details_adj_id_seq.nextval,
l_inv_details_id,
l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).date_received,
l_days_out,
NVL(l_covered_inv_tbl(i).wh_line_tbl(j).rct_line_tbl(k).Onhand_quantity,0),
l_covered_inv_tbl(i).UOM_Code,
null,
l_include_flag,
l_sysdate,
l_inv_hdr_rec.Last_Updated_By,
l_sysdate,
l_inv_hdr_rec.Last_Updated_By,
l_inv_hdr_rec.Last_Updated_By,
l_inv_hdr_rec.org_id,
1
);
UPDATE DPP_INVENTORY_DETAILS_ALL
SET include_flag = 'Y',
object_version_number = object_version_number + 1,
last_update_date = l_sysdate,
last_updated_by = l_inv_hdr_rec.Last_Updated_By,
last_update_login = l_inv_hdr_rec.Last_Updated_By
WHERE inventory_details_id = l_inv_details_id;
DPP_LOG_PVT.Insert_LinesLog(p_api_version => 1.0
,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_txn_lines_tbl => l_txn_lines_tbl
);
DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, 'return status for Insert_LinesLog =>'||l_return_status);
DPP_UTILITY_PVT.DEBUG_MESSAGE( FND_LOG.LEVEL_STATEMENT, 'dpp.plsql.' || L_FULL_NAME, substr(('Message dat for the DPP Insert_LinesLog API =>'||l_msg_data),1,4000));
ROLLBACK TO Update_CoveredInventory_PVT;
ROLLBACK TO Update_CoveredInventory_PVT;
ROLLBACK TO Update_CoveredInventory_PVT;
ROLLBACK TO Update_CoveredInventory_PVT;
fnd_message.set_token('ROUTINE', 'DPP_COVEREDINVENTORY_PVT.Update_CoveredInventory');
END Update_CoveredInventory;