[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
UPDATE cst_pac_process_phases
SET process_status = p_status,
process_date = SYSDATE,
process_upto_date = p_end_date,
last_update_date = SYSDATE,
last_updated_by = p_user_id,
request_id = p_req_id,
program_application_id = p_prg_appid,
program_id = p_prg_id,
program_update_date = SYSDATE,
last_update_login = p_login_id
WHERE pac_period_id = p_period_id
AND cost_group_id = p_cost_group_id
AND process_phase = p_phase;
DELETE CST_PAC_INTORG_ITMS_TEMP
WHERE pac_period_id = p_period_id
AND cost_group_id = p_cost_group_id;
DELETE MTL_PAC_ACT_CST_DTL_TEMP
WHERE pac_period_id = p_period_id
AND cost_group_id = p_cost_group_id;
PROCEDURE Periodic_Cost_Update
( p_period_id IN NUMBER
, p_legal_entity IN NUMBER
, p_cost_type_id IN NUMBER
, p_cost_group_id IN NUMBER
, p_cost_method IN NUMBER
, p_start_date IN DATE
, p_end_date IN DATE
, p_pac_rates_id IN NUMBER
, p_master_org_id IN NUMBER
, p_cost_update_type IN NUMBER
, p_uom_control IN NUMBER
, p_user_id IN NUMBER
, p_login_id IN NUMBER
, p_req_id IN NUMBER
, p_prg_id IN NUMBER
, p_prg_appid IN NUMBER
)
IS
l_routine CONSTANT VARCHAR2(30) := 'periodic_cost_update';
SELECT mmt.transaction_id
, mmt.transaction_action_id
, mmt.transaction_source_type_id
, mmt.inventory_item_id
, mmt.primary_quantity
, mmt.organization_id
, nvl(mmt.transfer_organization_id,-1) transfer_organization_id
, mmt.subinventory_code
FROM mtl_material_transactions mmt
, mtl_transaction_types mtt
WHERE mmt.transaction_date between p_start_date
AND p_end_date
AND mmt.transaction_action_id = 24
AND mmt.transaction_source_type_id = 14
AND mtt.transaction_action_id = mmt.transaction_action_id
AND mtt.transaction_source_type_id = mmt.transaction_source_type_id
AND mmt.transaction_type_id = mtt.transaction_type_id
AND (new_cost IS NOT NULL or percentage_change IS NOT NULL)
AND NVL(org_cost_group_id,-1) = p_cost_group_id
AND NVL(cost_type_id,-1) = p_cost_type_id;
SELECT mmt.transaction_id
, mmt.transaction_action_id
, mmt.transaction_source_type_id
, mmt.inventory_item_id
, mmt.primary_quantity
, mmt.organization_id
, nvl(mmt.transfer_organization_id,-1) transfer_organization_id
, mmt.subinventory_code
FROM mtl_material_transactions mmt
, mtl_transaction_types mtt
WHERE mmt.transaction_date between p_start_date
AND p_end_date
AND mmt.transaction_action_id = 24
AND mmt.transaction_source_type_id = 14
AND mtt.transaction_action_id = mmt.transaction_action_id
AND mtt.transaction_source_type_id = mmt.transaction_source_type_id
AND mmt.transaction_type_id = mtt.transaction_type_id
AND mmt.value_change IS NOT NULL
AND mmt.primary_quantity > 0
AND NVL(org_cost_group_id,-1) = p_cost_group_id
AND NVL(cost_type_id,-1) = p_cost_type_id;
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
,i_cost_group_id => p_cost_group_id
,i_txn_category => l_txn_category
,i_user_id => p_user_id
,i_login_id => p_login_id
,i_request_id => p_req_id
,i_prog_id => p_prg_id
,i_prog_appl_id => p_prg_appid
,o_err_num => l_error_num
,o_err_code => l_error_code
,o_err_msg => l_error_msg
);
,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
);
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
,i_cost_group_id => p_cost_group_id
,i_txn_category => l_txn_category
,i_user_id => p_user_id
,i_login_id => p_login_id
,i_request_id => p_req_id
,i_prog_id => p_prg_id
,i_prog_appl_id => p_prg_appid
,o_err_num => l_error_num
,o_err_code => l_error_code
,o_err_msg => l_error_msg
);
,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
);
CSTPPWAC.update_cppb(i_pac_period_id => p_period_id
,i_cost_group_id => p_cost_group_id
,i_txn_category => l_txn_category
,i_low_level_code => -2
,i_user_id => p_user_id
,i_login_id => p_login_id
,i_request_id => p_req_id
,i_prog_id => p_prg_id
,i_prog_appl_id => p_prg_appid
,o_err_num => l_error_num
,o_err_code => l_error_code
,o_err_msg => l_error_msg
);
,'After calling update_item_cppb:'|| l_error_num || l_error_code || l_error_msg
);
END Periodic_Cost_Update;
SELECT mmt.transaction_id transaction_id
, mmt.transaction_action_id transaction_action_id
, mmt.transaction_source_type_id transaction_source_type_id
, mmt.inventory_item_id inventory_item_id
, mmt.primary_quantity primary_quantity
, mmt.organization_id organization_id
, NVL(mmt.transfer_organization_id,-1) transfer_organization_id
, mmt.subinventory_code subinventory_code
, nvl(mmt.transfer_price,0) transfer_price
FROM mtl_material_transactions mmt
, cst_cost_group_assignments ccga
WHERE mmt.transaction_date BETWEEN c_period_start_date
AND c_period_end_date
AND ccga.organization_id = mmt.organization_id
AND ccga.cost_group_id = c_cost_group_id
AND mmt.organization_id = nvl(mmt.owning_organization_id,mmt.organization_id)
AND nvl(mmt.owning_tp_type,2) = 2
AND mmt.parent_transaction_id is null
AND mmt.transaction_type_id <> 20
AND (transaction_source_type_id = 1
OR transaction_action_id = 29
OR ((transaction_action_id = 1
OR transaction_action_id = 27
OR transaction_action_id = 6)
AND transaction_source_type_id IN (3,6,13)
AND transaction_cost IS NOT NULL)
OR (transaction_action_id = 27 AND transaction_source_type_id = 12))
UNION
SELECT
mmt1.transaction_id transaction_id
, mmt1.transaction_action_id transaction_action_id
, mmt1.transaction_source_type_id transaction_source_type_id
, mmt1.inventory_item_id inventory_item_id
, mmt1.primary_quantity primary_quantity
, mmt1.organization_id organization_id
, nvl(mmt1.transfer_organization_id,-1) transfer_organization_id
, mmt1.subinventory_code subinventory_code
, nvl(mmt1.transfer_price,0) transfer_price
FROM
mtl_material_transactions mmt1
, mtl_parameters mp1
WHERE mmt1.transaction_date BETWEEN c_period_start_date AND c_period_end_date
AND mmt1.organization_id = nvl(mmt1.owning_organization_id, mmt1.organization_id)
AND nvl(mmt1.owning_tp_type,2) = 2
AND mmt1.organization_id = mp1.organization_id
AND nvl(mp1.process_enabled_flag,'N') = 'N'
AND EXISTS (SELECT 'X'
FROM mtl_intercompany_parameters mip
WHERE nvl(fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER'),0) = 1
AND mip.flow_type = 1
AND nvl(fnd_profile.value('CST_TRANSFER_PRICING_OPTION'),0) = 2
AND mip.ship_organization_id = (select to_number(hoi.org_information3)
from hr_organization_information hoi
where hoi.organization_id = decode(mmt1.transaction_action_id,21,
mmt1.organization_id,mmt1.transfer_organization_id)
AND hoi.org_information_context = 'Accounting Information')
AND mip.sell_organization_id = (select to_number(hoi2.org_information3)
from hr_organization_information hoi2
where hoi2.organization_id = decode(mmt1.transaction_action_id,21,
mmt1.transfer_organization_id, mmt1.organization_id)
AND hoi2.org_information_context = 'Accounting Information'))
AND NOT EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
WHERE c1.organization_id = mmt1.organization_id
AND c2.organization_id = mmt1.transfer_organization_id
AND c1.cost_group_id = c2.cost_group_id)
AND (
(mmt1.transaction_action_id = 3 AND mmt1.transaction_source_type_id = 8
AND EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments ccga1
WHERE ccga1.cost_group_id = c_cost_group_id
AND ccga1.organization_id = mmt1.organization_id
AND mmt1.primary_quantity > 0))
OR (mmt1.transaction_action_id = 21 AND mmt1.transaction_source_type_id IN (7,8)
AND EXISTS ( SELECT 'X'
FROM mtl_interorg_parameters mip,
cst_cost_group_assignments ccga2
WHERE mip.from_organization_id = mmt1.organization_id
AND mip.to_organization_id = mmt1.transfer_organization_id
AND nvl(mmt1.fob_point,mip.fob_point) = 1
AND ccga2.organization_id = mip.to_organization_id
AND ccga2.cost_group_id = c_cost_group_id))
OR (mmt1.transaction_action_id = 12 AND mmt1.transaction_source_type_id IN (7,8)
AND EXISTS ( SELECT 'X'
FROM mtl_interorg_parameters mip,
cst_cost_group_assignments ccga2
WHERE mip.from_organization_id = mmt1.transfer_organization_id
AND mip.to_organization_id = mmt1.organization_id
AND nvl(mmt1.fob_point,mip.fob_point) = 2
AND ccga2.organization_id = mip.to_organization_id
AND ccga2.cost_group_id = c_cost_group_id)))
UNION
SELECT
mmt1.transaction_id transaction_id
, mmt1.transaction_action_id transaction_action_id
, mmt1.transaction_source_type_id transaction_source_type_id
, mmt1.inventory_item_id inventory_item_id
, mmt1.primary_quantity primary_quantity
, mmt1.organization_id organization_id
, nvl(mmt1.transfer_organization_id,-1) transfer_organization_id
, mmt1.subinventory_code subinventory_code
, nvl(mmt1.transfer_price,0) transfer_price
FROM
mtl_material_transactions mmt1
, mtl_parameters mp1
WHERE mmt1.transaction_date BETWEEN c_period_start_date AND c_period_end_date
AND mmt1.organization_id = nvl(mmt1.owning_organization_id, mmt1.organization_id)
AND nvl(mmt1.owning_tp_type,2) = 2
AND mmt1.organization_id = mp1.organization_id
AND nvl(mp1.process_enabled_flag,'N') = 'N'
AND NOT EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2, cst_cost_groups ccg1, cst_cost_groups ccg2
WHERE c1.organization_id = mmt1.organization_id
AND c2.organization_id = mmt1.transfer_organization_id
AND c1.cost_group_id = ccg1.cost_group_id
AND c2.cost_group_id = ccg2.cost_group_id
AND ccg1.legal_entity = ccg2.legal_entity)
AND (
(mmt1.transaction_action_id = 3
AND EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments ccga1
WHERE ccga1.cost_group_id = c_cost_group_id
AND ccga1.organization_id = mmt1.organization_id
AND mmt1.primary_quantity > 0))
OR (mmt1.transaction_action_id = 21
AND EXISTS ( SELECT 'X'
FROM mtl_interorg_parameters mip,
cst_cost_group_assignments ccga2
WHERE mip.from_organization_id = mmt1.organization_id
AND mip.to_organization_id = mmt1.transfer_organization_id
AND nvl(mmt1.fob_point,mip.fob_point) = 1
AND ccga2.organization_id = mip.to_organization_id
AND ccga2.cost_group_id = c_cost_group_id))
OR (mmt1.transaction_action_id = 12
AND EXISTS ( SELECT 'X'
FROM mtl_interorg_parameters mip,
cst_cost_group_assignments ccga2
WHERE mip.from_organization_id = mmt1.transfer_organization_id
AND mip.to_organization_id = mmt1.organization_id
AND nvl(mmt1.fob_point,mip.fob_point) = 2
AND ccga2.organization_id = mip.to_organization_id
AND ccga2.cost_group_id = c_cost_group_id)))
UNION
SELECT
mmt2.transaction_id transaction_id
, mmt2.transaction_action_id transaction_action_id
, mmt2.transaction_source_type_id transaction_source_type_id
, mmt2.inventory_item_id inventory_item_id
, mmt2.primary_quantity primary_quantity
, mmt2.organization_id organization_id
, nvl(mmt2.transfer_organization_id,-1) transfer_organization_id
, mmt2.subinventory_code subinventory_code
, nvl(mmt2.transfer_price,0) transfer_price
FROM
mtl_material_transactions mmt2
, mtl_parameters mp2
WHERE mmt2.transaction_date BETWEEN c_period_start_date AND c_period_end_date
AND mmt2.organization_id = nvl(mmt2.owning_organization_id, mmt2.organization_id)
AND nvl(mmt2.owning_tp_type,2) = 2
AND mmt2.organization_id = mp2.organization_id
AND nvl(mp2.process_enabled_flag,'N') = 'N'
AND mmt2.primary_quantity > 0
AND NOT EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
WHERE c1.organization_id = mmt2.organization_id
AND c2.organization_id = mmt2.transfer_organization_id
AND c1.cost_group_id = c2.cost_group_id)
AND (
(mmt2.transaction_action_id = 15
AND EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments ccga2
WHERE ccga2.organization_id = mmt2.organization_id
AND ccga2.cost_group_id = c_cost_group_id))
OR (mmt2.transaction_action_id = 3
AND EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments ccga3
,mtl_parameters mp3
WHERE mp3.organization_id = mmt2.transfer_organization_id
AND nvl(mp3.process_enabled_flag,'N') = 'Y'
AND ccga3.organization_id = mmt2.organization_id
AND ccga3.cost_group_id = c_cost_group_id ))
)
ORDER BY inventory_item_id;
-- insert into cppb
l_error_num := 0;
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
,i_cost_group_id => p_cost_group_id
,i_txn_category => l_txn_category
,i_user_id => p_user_id
,i_login_id => p_login_id
,i_request_id => p_req_id
,i_prog_id => p_prg_id
,i_prog_appl_id => p_prg_appid
,o_err_num => l_error_num
,o_err_code => l_error_code
,o_err_msg => l_error_msg
);
,'After calling insert_into_cppb1:'|| l_error_num || l_error_code || l_error_msg
);
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
,i_cost_group_id => p_cost_group_id
,i_txn_category => l_txn_category
,i_user_id => p_user_id
,i_login_id => p_login_id
,i_request_id => p_req_id
,i_prog_id => p_prg_id
,i_prog_appl_id => p_prg_appid
,o_err_num => l_error_num
,o_err_code => l_error_code
,o_err_msg => l_error_msg
);
,'After calling insert_into_cppb:'|| l_error_num || l_error_code || l_error_msg
);
CSTPPWAC.update_cppb(i_pac_period_id => p_period_id
,i_cost_group_id => p_cost_group_id
,i_txn_category => l_txn_category
,i_low_level_code => -2
,i_user_id => p_user_id
,i_login_id => p_login_id
,i_request_id => p_req_id
,i_prog_id => p_prg_id
,i_prog_appl_id => p_prg_appid
,o_err_num => l_error_num
,o_err_code => l_error_code
,o_err_msg => l_error_msg
);
,'After calling update_cppb:'|| l_error_num || l_error_code || l_error_msg
);
FND_MESSAGE.set_token('MESSAGE', 'Error in insert/update cppb for '||p_cost_group_id||' ('||l_error_code||') '||l_error_msg);
l_cost_update_type NUMBER;
l_cost_update_type := 1;
periodic_cost_update
(p_period_id => p_period_id
,p_legal_entity => p_legal_entity
,p_cost_type_id => p_cost_type_id
,p_cost_group_id => p_cost_group_id
,p_cost_method => p_cost_method
,p_start_date => p_start_date
,p_end_date => p_end_date
,p_pac_rates_id => p_pac_rates_id
,p_master_org_id => p_master_org_id
,p_cost_update_type => l_cost_update_type
,p_uom_control => p_uom_control
,p_user_id => p_user_id
,p_login_id => p_login_id
,p_req_id => p_req_id
,p_prg_id => p_prg_id
,p_prg_appid => p_prg_appid);