[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT count(*)
INTO l_count
FROM CST_COST_GROUPS
WHERE cost_group_id = i_org_cost_group_id
AND TRUNC(NVL(disable_date, sysdate)) >= TRUNC(sysdate)
AND cost_group_type = 2;
SELECT legal_entity, organization_id
INTO l_legal_entity, l_master_org_id
FROM CST_COST_GROUPS
WHERE cost_group_id = i_org_cost_group_id;
SELECT count(*)
INTO l_count
FROM CST_LE_COST_TYPES
WHERE legal_entity = l_legal_entity
AND cost_type_id = i_cost_type_id;
SELECT gl.set_of_books_id, gl.chart_of_accounts_id,
gl.period_set_name, gl.accounted_period_type
INTO l_sob_id, l_chart_account_id,
l_period_set_name, l_period_type
FROM CST_LE_COST_TYPES clct, GL_SETS_OF_BOOKS gl
WHERE clct.set_of_books_id = gl.set_of_books_id
AND clct.legal_entity = l_legal_entity
AND cost_type_id = i_cost_type_id;
SELECT count(*)
INTO l_count
FROM MTL_PARAMETERS
WHERE organization_id = i_org_id
AND master_organization_id = i_org_id
AND i_org_id = l_master_org_id;
SELECT NVL(MAX(pac_period_id),-1)
INTO l_period_id
FROM CST_PAC_PERIODS
WHERE legal_entity = l_legal_entity
AND cost_type_id = i_cost_type_id
AND i_transaction_date between period_start_date and period_end_date;
SELECT decode(open_flag,'Y',decode(period_close_date,NULL,'Y','N'),'N')
INTO l_open_flag
FROM CST_PAC_PERIODS
WHERE pac_period_id = l_period_id;
SELECT count(*)
INTO l_count
FROM GL_PERIODS
WHERE period_set_name = l_period_set_name
AND period_type = l_period_type
AND i_transaction_date between start_date and end_date;
The following must be true for periodic cost update :
- Among new_cost, value change, percentage change column on the mti,
the user can only enter it one. Also can't have null value
- The same is true in detail.
- The sum of new_cost in mtcdi must be the same with one in mti.
New cost must be >= 0
- The sum of value change in mtcdi must be the same with one in mti.
- Percentage change >= -100
*/
l_stmt_num := 100;
SELECT count(*)
INTO l_num_details
FROM MTL_TXN_COST_DET_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id;
SELECT count(*), SUM(new_average_cost)
INTO l_count, l_sum_new_cost
FROM MTL_TXN_COST_DET_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id AND NVL(new_average_cost,-1) >= 0
AND percentage_change IS NULL AND value_change IS NULL;
SELECT count(*)
INTO l_count
FROM MTL_TXN_COST_DET_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id AND new_average_cost IS NULL
AND NVL(percentage_change,-999) >= -100 AND value_change IS NULL;
SELECT count(*), SUM(value_change)
INTO l_count, l_sum_value_change
FROM MTL_TXN_COST_DET_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id AND new_average_cost IS NULL
AND percentage_change IS NULL AND value_change IS NOT NULL;
SELECT count(*)
INTO l_count
FROM GL_CODE_COMBINATIONS
WHERE code_combination_id = i_accnt
AND chart_of_accounts_id = i_chart_of_accounts;
SELECT count(*)
INTO l_num_detail
FROM MTL_TXN_COST_DET_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id;
SELECT legal_entity
INTO l_legal_entity
FROM CST_COST_GROUPS
WHERE cost_group_id = i_org_cost_group_id;
SELECT NVL(MAX(pac_period_id),-1)
INTO l_pac_period_id
FROM CST_PAC_PERIODS
WHERE cost_type_id = i_cost_type_id
AND legal_entity = l_legal_entity
AND i_transaction_date between period_start_date and period_end_date;
* In this case, call cstpacit.cost_det_new_insert.
*/
IF (l_num_detail = 0) THEN
cstppcit.periodic_cost_det_new_insert(l_pac_period_id, l_legal_entity,
i_cost_type_id,
i_txn_id, i_txn_action_id, i_org_id,
i_item_id, i_org_cost_group_id, i_txn_cost,
i_new_avg_cost, i_per_change, i_val_change,
i_mat_accnt, i_mat_ovhd_accnt, i_res_accnt,
i_osp_accnt, i_ovhd_accnt,
i_user_id, i_login_id, i_request_id,
i_prog_appl_id, i_prog_id,
l_err_num, l_err_code, l_err_msg);
INSERT INTO MTL_PAC_TXN_COST_DETAILS (
pac_period_id,
cost_group_id,
cost_type_id,
transaction_id,
inventory_item_id,
cost_element_id,
level_type,
transaction_cost,
new_periodic_cost,
percentage_change,
value_change,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
) SELECT
l_pac_period_id,
i_org_cost_group_id,
i_cost_type_id,
i_txn_id,
i_item_id,
cost_element_id,
level_type,
transaction_cost,
new_average_cost,
percentage_change,
value_change,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate
FROM MTL_TXN_COST_DET_INTERFACE
WHERE TRANSACTION_INTERFACE_ID = i_txn_interface_id;
PROCEDURE periodic_cost_det_new_insert (
i_pac_period_id in number,
i_legal_entity in number,
i_cost_type_id in number,
i_txn_id in number,
i_txn_action_id in number,
i_org_id in number,
i_item_id in number,
i_org_cost_group_id in number,
i_txn_cost in number,
i_new_avg_cost in number,
i_per_change in number,
i_val_change in number,
i_mat_accnt in number,
i_mat_ovhd_accnt in number,
i_res_accnt in number,
i_osp_accnt in number,
i_ovhd_accnt in number,
i_user_id in number,
i_login_id in number,
i_request_id in number,
i_prog_appl_id in number,
i_prog_id in number,
o_err_num out NOCOPY number,
o_err_code out NOCOPY varchar2,
o_err_msg out NOCOPY varchar2
) IS
l_err_num number;
SELECT cpicd.cost_element_id
FROM CST_PAC_ITEM_COST_DETAILS cpicd
WHERE cpicd.cost_layer_id = l_layer_id;
SELECT NVL(MAX(pac_period_id), -1)
INTO l_prior_close_period_id
FROM CST_PAC_PERIODS
WHERE cost_type_id = i_cost_type_id AND legal_entity = i_legal_entity
AND open_flag = 'N' AND period_close_date IS NOT NULL;
* insert each elements into MTL_PAC_TXN_COST_DETAILS.
*/
IF (l_cost_layer_id <> 0) THEN
FOR l_cost_element IN cost_element_cursor(l_cost_layer_id) LOOP
IF ((l_cost_element.cost_element_id = 1 AND i_mat_accnt IS NULL) OR
(l_cost_element.cost_element_id = 2 AND i_mat_ovhd_accnt IS NULL) OR
(l_cost_element.cost_element_id = 3 AND i_res_accnt IS NULL) OR
(l_cost_element.cost_element_id = 4 AND i_osp_accnt IS NULL) OR
(l_cost_element.cost_element_id = 5 AND i_ovhd_accnt IS NULL)) THEN
-- Error occured
FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_ACCOUNT');
SELECT item_cost
INTO l_item_cost
FROM CST_PAC_ITEM_COSTS
WHERE cost_layer_id = l_cost_layer_id;
SELECT count(cost_element_id)
INTO cost_element_count
FROM CST_PAC_ITEM_COST_DETAILS
WHERE cost_layer_id = l_cost_layer_id;
INSERT INTO MTL_PAC_TXN_COST_DETAILS (
pac_period_id,
cost_group_id,
cost_type_id,
transaction_id,
inventory_item_id,
cost_element_id,
level_type,
transaction_cost,
new_periodic_cost,
percentage_change,
value_change,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
SELECT
i_pac_period_id,
i_org_cost_group_id,
i_cost_type_id,
i_txn_id,
i_item_id,
cost_element_id,
level_type,
DECODE(l_item_cost, 0, i_txn_cost/cost_element_count,
i_txn_cost * item_cost/l_item_cost),
DECODE(l_item_cost, 0, i_new_avg_cost/cost_element_count,
i_new_avg_cost * item_cost/l_item_cost),
i_per_change,
DECODE(l_item_cost, 0, i_val_change/cost_element_count,
i_val_change * item_cost/l_item_cost),
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate
FROM CST_PAC_ITEM_COST_DETAILS
WHERE cost_layer_id = l_cost_layer_id;
/* If layer detail does not exist, then insert a new row
* as a this level material.
*/
ELSE
IF (i_mat_accnt is null) THEN
FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_ACCOUNT');
INSERT INTO MTL_PAC_TXN_COST_DETAILS (
pac_period_id,
cost_group_id,
cost_type_id,
transaction_id,
inventory_item_id,
cost_element_id,
level_type,
transaction_cost,
new_periodic_cost,
percentage_change,
value_change,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date
)
VALUES (
i_pac_period_id,
i_org_cost_group_id,
i_cost_type_id,
i_txn_id,
i_item_id,
1,
1,
i_txn_cost,
i_new_avg_cost,
i_per_change,
i_val_change,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate);
o_err_msg := 'CSTPPCIT.PERIODIC_COST_DET_NEW_INSERT:' || l_err_msg;
o_err_msg := 'CSTPPCIT.PERIODIC_COST_DET_NEW_INSERT:' ||
substr(SQLERRM,1,150);
END periodic_cost_det_new_insert;