The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO MTL_CST_TXN_COST_DETAILS (
transaction_id,
organization_id,
inventory_item_id,
cost_element_id,
level_type,
transaction_cost,
new_average_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_txn_id,
i_org_id,
i_inv_item_id,
1, -- material
1, -- this level
i_txn_cost,
NULL,
NULL,
NULL,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate);
INSERT INTO MTL_CST_ACTUAL_COST_DETAILS (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
inventory_item_id,
actual_cost,
prior_cost,
new_cost,
insertion_flag,
variance_amount,
user_entered)
SELECT
i_txn_id,
i_org_id,
-1, -- layer_id = -1 for std.
cost_element_id,
level_type,
i_txn_action_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
i_inv_item_id,
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,1,ITEM_COST,
2,ITEM_COST,
3,ITEM_COST,
4,ITEM_COST,
5,ITEM_COST),
1,DECODE(COST_ELEMENT_ID,1,ITEM_COST,
2,ITEM_COST,
3,ITEM_COST,
4,ITEM_COST,
5,ITEM_COST))),
NULL,
NULL,
'N',
NULL,
'N'
FROM CST_ITEM_COST_DETAILS CICD,
MTL_PARAMETERS MP
WHERE MP.organization_id = i_org_id
AND CICD.organization_id = MP.cost_organization_id
AND CICD.inventory_item_id = i_inv_item_id
AND CICD.cost_type_id = 1
GROUP BY CICD.level_type, CICD.cost_element_id;
only insert into MACS
*/
l_earn_moh := 1;
INSERT INTO mtl_actual_cost_subelement(
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
resource_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
actual_cost,
user_entered)
SELECT i_txn_id,
i_org_id,
-1, -- layer_id = -1 for std.
cost_element_id,
level_type,
resource_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
item_cost,
'N'
FROM CST_ITEM_COST_DETAILS CICD,
MTL_PARAMETERS MP
WHERE MP.organization_id = i_org_id
AND CICD.organization_id = MP.cost_organization_id
AND CICD.inventory_item_id = i_inv_item_id
AND CICD.cost_type_id = 1
AND CICD.level_type = 1
AND CICD.cost_element_id = 2;
INSERT INTO MTL_CST_ACTUAL_COST_DETAILS (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
inventory_item_id,
actual_cost,
prior_cost,
new_cost,
insertion_flag,
variance_amount,
user_entered
)
VALUES(
i_txn_id,
i_org_id,
-1, -- layer_id = -1 for std.
1, -- material
1, -- this level
i_txn_action_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
i_inv_item_id,
i_txn_cost,
NULL,
NULL,
'N',
NULL,
'N');
INSERT INTO MTL_CST_ACTUAL_COST_DETAILS (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
inventory_item_id,
actual_cost,
prior_cost,
new_cost,
insertion_flag,
variance_amount,
user_entered
)
VALUES(
i_txn_id,
i_org_id,
-1, -- layer_id = -1 for std.
1, -- material
1, -- this level
i_txn_action_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
i_inv_item_id,
i_actual_cost,
NULL,
NULL,
'N',
NULL,
'N');
SELECT primary_quantity, organization_id, transfer_organization_id
INTO l_txn_qty, l_org_id, l_txfr_org_id
FROM mtl_material_transactions
WHERE transaction_id = i_txn_id;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting into MCACD... ');*/
INSERT INTO MTL_CST_ACTUAL_COST_DETAILS (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
inventory_item_id,
actual_cost,
prior_cost,
new_cost,
insertion_flag,
variance_amount,
user_entered)
SELECT
i_txn_id,
i_org_id,
-1, -- layer_id = -1 for std.
cost_element_id,
level_type,
i_txn_action_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
i_inv_item_id,
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,1,ITEM_COST,
2,ITEM_COST,
3,ITEM_COST,
4,ITEM_COST,
5,ITEM_COST),
1,DECODE(COST_ELEMENT_ID,1,ITEM_COST,
2,ITEM_COST,
3,ITEM_COST,
4,ITEM_COST,
5,ITEM_COST))),
NULL,
NULL,
'N',
NULL,
'N'
FROM CST_ITEM_COST_DETAILS CICD,
MTL_PARAMETERS MP
WHERE MP.organization_id = i_org_id
AND CICD.organization_id = MP.cost_organization_id
AND CICD.inventory_item_id = i_inv_item_id
AND CICD.cost_type_id = 1
GROUP BY CICD.level_type, CICD.cost_element_id;
INSERT INTO MTL_CST_ACTUAL_COST_DETAILS (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
inventory_item_id,
actual_cost,
prior_cost,
new_cost,
insertion_flag,
variance_amount,
user_entered)
SELECT
i_txn_id,
i_org_id,
-1, -- layer_id = -1 for std.
cost_element_id,
level_type,
i_txn_action_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
i_inv_item_id,
0,
NULL,
NULL,
'N',
NULL,
'N'
FROM CST_ITEM_COST_DETAILS CICD,
MTL_PARAMETERS MP
WHERE MP.organization_id = i_org_id
AND CICD.organization_id = MP.cost_organization_id
AND CICD.inventory_item_id = i_inv_item_id
AND CICD.cost_type_id = 1
GROUP BY CICD.level_type, CICD.cost_element_id;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting into MCACD for receiving org... ');
INSERT INTO MTL_CST_ACTUAL_COST_DETAILS (
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
transaction_action_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
inventory_item_id,
actual_cost,
prior_cost,
new_cost,
insertion_flag,
variance_amount,
user_entered)
SELECT
i_txn_id,
i_org_id,
-1, -- layer_id = -1 for std.
cost_element_id,
level_type,
i_txn_action_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
i_inv_item_id,
SUM(DECODE(LEVEL_TYPE,2,DECODE(COST_ELEMENT_ID,1,ITEM_COST,
2,ITEM_COST,
3,ITEM_COST,
4,ITEM_COST,
5,ITEM_COST),
1,DECODE(COST_ELEMENT_ID,1,ITEM_COST,
2,ITEM_COST,
3,ITEM_COST,
4,ITEM_COST,
5,ITEM_COST))),
NULL,
NULL,
'N',
NULL,
'N'
FROM CST_ITEM_COST_DETAILS CICD,
MTL_PARAMETERS MP
WHERE MP.organization_id = i_org_id
AND CICD.organization_id = MP.cost_organization_id
AND CICD.inventory_item_id = i_inv_item_id
AND CICD.cost_type_id = 1
AND CICD.cost_element_id = 2
GROUP BY CICD.level_type, CICD.cost_element_id;
SELECT nvl(MMT.fob_point, MIP.fob_point)
INTO l_fob_point
FROM MTL_INTERORG_PARAMETERS MIP, MTL_MATERIAL_TRANSACTIONS MMT
WHERE MIP.from_organization_id = l_org_id
AND MIP.to_organization_id = l_txfr_org_id
AND MMT.transaction_id = i_txn_id;
SELECT nvl(MMT.fob_point, MIP.fob_point)
INTO l_fob_point
FROM MTL_INTERORG_PARAMETERS MIP, MTL_MATERIAL_TRANSACTIONS MMT
WHERE MIP.from_organization_id = l_txfr_org_id
AND MIP.to_organization_id = l_org_id
AND MMT.transaction_id = i_txn_id;
only insert into MACS
*/
l_earn_moh := 1;
FND_FILE.PUT_LINE(FND_FILE.LOG, 'Inserting into MACS... ');
INSERT INTO mtl_actual_cost_subelement(
transaction_id,
organization_id,
layer_id,
cost_element_id,
level_type,
resource_id,
last_update_date,
last_updated_by,
creation_date,
created_by,
last_update_login,
request_id,
program_application_id,
program_id,
program_update_date,
actual_cost,
user_entered)
SELECT i_txn_id,
i_org_id,
-1, -- layer_id = -1 for std.
cost_element_id,
level_type,
resource_id,
sysdate,
i_user_id,
sysdate,
i_user_id,
i_login_id,
i_request_id,
i_prog_appl_id,
i_prog_id,
sysdate,
item_cost,
'N'
FROM CST_ITEM_COST_DETAILS CICD,
MTL_PARAMETERS MP
WHERE MP.organization_id = i_org_id
AND CICD.organization_id = MP.cost_organization_id
AND CICD.inventory_item_id = i_inv_item_id
AND CICD.cost_type_id = 1
AND CICD.level_type = 1
AND CICD.cost_element_id = 2;