[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT DECODE(inventory_asset_flag,'Y',0,1)
INTO l_exp_item
FROM mtl_system_items
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id;
SELECT DECODE(l_exp_item,1,1,DECODE(asset_inventory,1,0,1))
INTO l_exp_flag
FROM mtl_secondary_inventories
WHERE secondary_inventory_name = p_subinventory_code
AND organization_id = p_org_id;
G_EXPENSE_FLAG_CACHE.DELETE;
G_EXPENSE_ITEM_CACHE.DELETE;
SELECT
concatenated_segments
FROM
MTL_SYSTEM_ITEMS_B_KFV
WHERE inventory_item_id = c_inventory_item_id
AND rownum = 1;
SELECT
process_status
INTO x_status
FROM
cst_pac_process_phases
WHERE pac_period_id = p_pac_period_id
AND process_phase = p_phase
AND rownum = 1;
SELECT
process_status
INTO x_status
FROM
cst_pac_process_phases
WHERE pac_period_id = p_pac_period_id
AND process_phase = p_phase
AND cost_group_id = p_cost_group_id;
SELECT
TO_CHAR(process_upto_date, 'YYYY/MM/DD HH24:MI:SS')
FROM
cst_pac_process_phases
WHERE pac_period_id = c_pac_period_id
AND process_phase = 7;
PROCEDURE Periodic_Cost_Update_By_Level
( p_period_id IN NUMBER
, p_legal_entity IN NUMBER
, p_cost_type_id IN NUMBER
, p_cost_group_id IN NUMBER
, p_inventory_item_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_uom_control IN NUMBER
, p_low_level_code 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_by_level';
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
WHERE mmt.transaction_date BETWEEN c_start_date AND c_end_date
AND mmt.transaction_action_id = 24
AND mmt.transaction_source_type_id = 14
AND value_change IS NOT NULL
AND mmt.primary_quantity = 0
AND NVL(org_cost_group_id,-1) = c_cost_group_id
AND NVL(cost_type_id,-1) = c_cost_type_id
AND mmt.inventory_item_id = c_inventory_item_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_item_cppb(i_pac_period_id => p_period_id
,i_cost_group_id => p_cost_group_id
,i_txn_category => l_txn_category
,i_item_id => p_inventory_item_id
,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_By_Level;
SELECT nvl(min(process_phase), 1)
INTO l_starting_phase
FROM cst_pac_process_phases
WHERE pac_period_id = p_period_id
AND cost_group_id = p_cost_group_id
AND process_status = 3
AND ( process_phase <= 5 OR process_phase = 7);
SELECT nvl(min(process_phase), 7)
INTO l_starting_phase
FROM cst_pac_process_phases
WHERE pac_period_id = p_period_id
AND cost_group_id = p_cost_group_id
AND process_status = 3
AND process_phase IN (8);
SELECT
count(1)
INTO l_count
FROM cst_pac_process_phases
WHERE pac_period_id = p_period_id
AND cost_group_id = p_cost_group_id
AND process_phase = 5
AND process_upto_date <= p_end_date;
SELECT
count(1)
INTO l_count
FROM cst_pac_process_phases
WHERE pac_period_id = p_period_id
AND cost_group_id = p_cost_group_id
AND process_phase = l_starting_phase - 1
AND process_upto_date <= p_end_date;
SELECT control_level
INTO l_uom_control
FROM mtl_item_attributes
WHERE attribute_name = 'MTL_SYSTEM_ITEMS.PRIMARY_UNIT_OF_MEASURE';
SELECT NVL(organization_id,-1)
INTO l_master_org_id
FROM cst_cost_groups
WHERE cost_group_id = p_cost_group_id;
SELECT count(1)
INTO l_count
FROM mtl_parameters mp
WHERE mp.master_organization_id <> l_master_org_id
AND mp.organization_id IN (
SELECT organization_id
FROM cst_cost_group_assignments ccga
WHERE ccga.cost_group_id = p_cost_group_id)
AND rownum = 1;
SELECT
nvl(max(pac_rates_cost_type_id),-1)
, nvl(max(material_relief_algorithm),1)
INTO l_pac_rates_id
,l_mat_relief_algorithm
FROM cst_le_cost_types
WHERE legal_entity = p_legal_entity
AND cost_type_id = p_cost_type_id;
SELECT count(1)
INTO l_count
FROM CST_PAC_PERIODS cpp
WHERE cpp.pac_period_id = p_period_id
AND TRUNC(cpp.period_end_date) >= l_process_upto_date
AND TRUNC(cpp.period_start_date) <= l_process_upto_date;
SELECT count(1)
INTO l_num_of_assignments
FROM cst_cost_group_assignments ccga
WHERE ccga.cost_group_id = p_cost_group_id;
UPDATE cst_pac_process_phases
SET process_status = 3,
process_date = SYSDATE,
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;
SELECT nvl(max(primary_cost_method),-1)
INTO l_cost_method
FROM cst_le_cost_types clct
WHERE clct.legal_entity = p_legal_entity
AND clct.cost_type_id = p_cost_type_id;
SELECT count(1)
INTO l_count
FROM cst_cost_groups ccg
WHERE ccg.legal_entity = p_legal_entity
AND ccg.cost_group_id = p_cost_group_id
AND trunc(nvl(ccg.disable_date, SYSDATE+1)) > trunc(SYSDATE)
AND EXISTS (
SELECT 'X'
FROM cst_pac_process_phases cppp
WHERE cppp.cost_group_id = ccg.cost_group_id
AND cppp.pac_period_id = p_period_id);
SELECT TO_CHAR(period_start_date,'YYYY/MM/DD HH24:MI:SS')
,p_process_upto_date
INTO l_start_date
,l_end_date
FROM cst_pac_periods cpp
WHERE cpp.pac_period_id = p_period_id
AND cpp.legal_entity = p_legal_entity
AND cpp.cost_type_id = p_cost_type_id;
SELECT count(1)
INTO l_count
FROM cst_pac_process_phases
WHERE cost_group_id = p_cost_group_id
AND pac_period_id = p_period_id;
SELECT nvl(max(cpp.pac_period_id), -1)
INTO l_prev_period_id
FROM cst_pac_periods cpp
WHERE cpp.legal_entity = p_legal_entity
AND cpp.cost_type_id = p_cost_type_id
AND cpp.pac_period_id < p_period_id;
SELECT count(1)
INTO l_period_closed
FROM cst_pac_periods cpp
WHERE cpp.pac_period_id = l_prev_period_id
AND cpp.legal_entity = p_legal_entity
AND cpp.cost_type_id = p_cost_type_id
AND cpp.open_flag = 'N'
AND cpp.period_close_date IS NOT NULL;
SELECT count(1)
INTO l_count
FROM cst_pac_periods cpp
WHERE cpp.legal_entity = p_legal_entity
AND cpp.cost_type_id = p_cost_type_id
AND cpp.pac_period_id = p_period_id
AND cpp.open_flag = 'Y'
AND cpp.period_close_date IS NULL;
SELECT count(1)
INTO l_count
FROM cst_cost_types cct
WHERE cct.cost_type_id = p_cost_type_id
AND cct.organization_id IS NULL
AND cct.allow_updates_flag = 2
AND trunc(nvl(cct.disable_date, SYSDATE+1)) > trunc(SYSDATE);
SELECT count(1)
INTO l_count
FROM cst_le_cost_types clct
WHERE clct.legal_entity = p_legal_entity
AND clct.cost_type_id = 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
, nvl(mmt.transfer_price,0) transfer_price
FROM mtl_material_transactions mmt
WHERE transaction_date between c_period_start_date AND c_period_end_date
AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
AND mmt.inventory_item_id = c_inventory_item_id
AND nvl(mmt.owning_tp_type,2) = 2
AND EXISTS (select 'X'
from mtl_parameters mp
where mmt.organization_id = mp.organization_id
AND nvl(mp.process_enabled_flag, 'N') = 'N')
AND transaction_action_id in (3,12,21)
AND EXISTS (SELECT 'EXISTS'
FROM cst_cost_group_assignments ccga
WHERE ccga.cost_group_id = c_cost_group_id
AND (ccga.organization_id = mmt.organization_id OR
ccga.organization_id = mmt.transfer_organization_id))
AND (
(mmt.transaction_source_type_id = 13
AND EXISTS (select 'X'
from mtl_parameters mp2
where mp2.organization_id = mmt.transfer_organization_id
AND mp2.process_enabled_flag = 'Y'))
OR (mmt.transaction_source_type_id in (7,8)
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(mmt.transaction_action_id,21,
mmt.organization_id,mmt.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(mmt.transaction_action_id,21,
mmt.transfer_organization_id, mmt.organization_id)
AND hoi2.org_information_context = 'Accounting Information')))
)
AND (transaction_action_id IN (3,12,21)
AND NOT EXISTS (SELECT 'X'
FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
WHERE c1.organization_id = mmt.organization_id
AND c2.organization_id = mmt.transfer_organization_id
AND c1.cost_group_id = c2.cost_group_id)
AND (
(mmt.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 = mmt.organization_id
AND mmt.primary_quantity < 0)
)
OR (mmt.transaction_action_id = 21
AND EXISTS (SELECT 'X'
FROM cst_cost_group_assignments ccga2
WHERE ccga2.organization_id = mmt.organization_id
AND ccga2.cost_group_id = c_cost_group_id)
)
OR (mmt.transaction_action_id = 12
AND EXISTS (SELECT 'X'
FROM mtl_interorg_parameters mip
WHERE mip.from_organization_id = mmt.transfer_organization_id
AND mip.to_organization_id = mmt.organization_id
AND (
(NVL(mmt.fob_point,mip.fob_point) = 1
AND EXISTS (SELECT 'X'
FROM cst_cost_group_assignments ccga2
WHERE ccga2.organization_id = mip.to_organization_id
AND ccga2.cost_group_id = c_cost_group_id)
)
OR (NVL(mmt.fob_point,mip.fob_point) = 2
AND EXISTS (SELECT 'X'
FROM cst_cost_group_assignments ccga3
WHERE ccga3.organization_id = mip.from_organization_id
AND ccga3.cost_group_id = c_cost_group_id)
)
)
)
)
)
)
AND NOT EXISTS (SELECT 'X'
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = mmt.inventory_item_id
AND cpllc.pac_period_id = c_pac_period_id
AND cpllc.cost_group_id = c_cost_group_id)
UNION ALL
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
, nvl(mmt.transfer_price,0) transfer_price
FROM mtl_material_transactions mmt
WHERE transaction_date between c_period_start_date AND c_period_end_date
AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
AND mmt.inventory_item_id = c_inventory_item_id
AND nvl(mmt.owning_tp_type,2) = 2
AND EXISTS (select 'X'
from mtl_parameters mp
where mmt.organization_id = mp.organization_id
AND nvl(mp.process_enabled_flag, 'N') = 'N')
AND (
(mmt.transaction_action_id = 22
AND EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments ccga0
WHERE ccga0.organization_id = mmt.organization_id
AND ccga0.cost_group_id = c_cost_group_id))
OR ( (mmt.transaction_action_id IN (12,21) OR (mmt.transaction_action_id = 3 AND mmt.primary_quantity < 0))
AND EXISTS (SELECT 'X'
FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
WHERE c1.organization_id = mmt.organization_id
AND c2.organization_id = mmt.transfer_organization_id
AND c1.cost_group_id = c2.cost_group_id
AND c1.cost_group_id = c_cost_group_id))
)
AND NOT EXISTS (SELECT 'X'
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = mmt.inventory_item_id
AND cpllc.pac_period_id = c_pac_period_id
AND cpllc.cost_group_id = c_cost_group_id)
UNION ALL
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
, nvl(mmt.transfer_price,0) transfer_price
FROM
mtl_material_transactions mmt
, cst_cost_group_assignments ccga
WHERE transaction_date between c_period_start_date AND c_period_end_date
AND transaction_action_id in (4,8,28,33,34,1,2,5,27)
AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
AND mmt.inventory_item_id = c_inventory_item_id
AND nvl(mmt.owning_tp_type,2) = 2
AND ccga.cost_group_id = c_cost_group_id
AND ccga.organization_id = mmt.organization_id
AND nvl(mmt.logical_transactions_created, 1) <> 2
AND nvl(mmt.logical_transaction, 3) <> 1
AND (transaction_action_id IN (4,8,28,33,34)
OR (transaction_action_id IN (2,5) AND primary_quantity < 0)
OR (transaction_action_id in (1, 27)
AND transaction_source_type_id IN (3,6,13)
AND transaction_cost IS NULL)
OR (transaction_action_id in (1,27)
AND transaction_source_type_id NOT IN (1,3,6,13)) )
AND NOT EXISTS (
SELECT 'X'
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = mmt.inventory_item_id
AND cpllc.pac_period_id = c_pac_period_id
AND cpllc.cost_group_id = c_cost_group_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
, nvl(mmt.transfer_price,0) transfer_price
FROM mtl_material_transactions mmt
WHERE transaction_date between c_period_start_date AND c_period_end_date
AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
AND mmt.inventory_item_id = c_inventory_item_id
AND nvl(mmt.owning_tp_type,2) = 2
AND EXISTS (select 'X'
from mtl_parameters mp
where mmt.organization_id = mp.organization_id
AND nvl(mp.process_enabled_flag, 'N') = 'N')
AND transaction_action_id in (3,12,21)
AND EXISTS (SELECT 'EXISTS'
FROM cst_cost_group_assignments ccga
WHERE ccga.cost_group_id = c_cost_group_id
AND (ccga.organization_id = mmt.organization_id OR
ccga.organization_id = mmt.transfer_organization_id))
AND (
(mmt.transaction_source_type_id = 13
AND EXISTS (select 'X'
from mtl_parameters mp2
where mp2.organization_id = mmt.transfer_organization_id
AND mp2.process_enabled_flag = 'Y'))
OR (mmt.transaction_source_type_id in (7,8)
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(mmt.transaction_action_id,21,
mmt.organization_id,mmt.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(mmt.transaction_action_id,21,
mmt.transfer_organization_id, mmt.organization_id)
AND hoi2.org_information_context = 'Accounting Information')))
)
AND (transaction_action_id IN (3,12,21)
AND NOT EXISTS (SELECT 'X'
FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
WHERE c1.organization_id = mmt.organization_id
AND c2.organization_id = mmt.transfer_organization_id
AND c1.cost_group_id = c2.cost_group_id)
AND (
(mmt.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 = mmt.organization_id
AND mmt.primary_quantity < 0)
)
OR (mmt.transaction_action_id = 21
AND EXISTS (SELECT 'X'
FROM cst_cost_group_assignments ccga2
WHERE ccga2.organization_id = mmt.organization_id
AND ccga2.cost_group_id = c_cost_group_id)
)
OR (mmt.transaction_action_id = 12
AND EXISTS (SELECT 'X'
FROM mtl_interorg_parameters mip
WHERE mip.from_organization_id = mmt.transfer_organization_id
AND mip.to_organization_id = mmt.organization_id
AND (
(NVL(mmt.fob_point,mip.fob_point) = 1
AND EXISTS (SELECT 'X'
FROM cst_cost_group_assignments ccga2
WHERE ccga2.organization_id = mip.to_organization_id
AND ccga2.cost_group_id = c_cost_group_id)
)
OR (NVL(mmt.fob_point,mip.fob_point) = 2
AND EXISTS (SELECT 'X'
FROM cst_cost_group_assignments ccga3
WHERE ccga3.organization_id = mip.from_organization_id
AND ccga3.cost_group_id = c_cost_group_id)
)
)
)
)
)
)
AND EXISTS (SELECT 'X'
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = mmt.inventory_item_id
AND cpllc.pac_period_id = c_pac_period_id
AND cpllc.cost_group_id = c_cost_group_id)
UNION ALL
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
, nvl(mmt.transfer_price,0) transfer_price
FROM mtl_material_transactions mmt
WHERE transaction_date between c_period_start_date AND c_period_end_date
AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
AND mmt.inventory_item_id = c_inventory_item_id
AND nvl(mmt.owning_tp_type,2) = 2
AND EXISTS (select 'X'
from mtl_parameters mp
where mmt.organization_id = mp.organization_id
AND nvl(mp.process_enabled_flag, 'N') = 'N')
AND (
(mmt.transaction_action_id = 22
AND EXISTS ( SELECT 'X'
FROM cst_cost_group_assignments ccga0
WHERE ccga0.organization_id = mmt.organization_id
AND ccga0.cost_group_id = c_cost_group_id))
OR ( (mmt.transaction_action_id IN (12,21) OR (mmt.transaction_action_id = 3 AND mmt.primary_quantity < 0))
AND EXISTS (SELECT 'X'
FROM cst_cost_group_assignments c1, cst_cost_group_assignments c2
WHERE c1.organization_id = mmt.organization_id
AND c2.organization_id = mmt.transfer_organization_id
AND c1.cost_group_id = c2.cost_group_id
AND c1.cost_group_id = c_cost_group_id))
)
AND EXISTS (SELECT 'X'
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = mmt.inventory_item_id
AND cpllc.pac_period_id = c_pac_period_id
AND cpllc.cost_group_id = c_cost_group_id)
UNION ALL
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
, nvl(mmt.transfer_price,0) transfer_price
FROM
mtl_material_transactions mmt
, cst_cost_group_assignments ccga
WHERE transaction_date between c_period_start_date AND c_period_end_date
AND transaction_action_id in (4,8,28,33,34,1,2,5,27)
AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
AND mmt.inventory_item_id = c_inventory_item_id
AND nvl(mmt.owning_tp_type,2) = 2
AND ccga.cost_group_id = c_cost_group_id
AND ccga.organization_id = mmt.organization_id
AND nvl(mmt.logical_transactions_created, 1) <> 2
AND nvl(mmt.logical_transaction, 3) <> 1
AND (transaction_action_id IN (4,8,28,33,34)
OR (transaction_action_id IN (2,5) AND primary_quantity < 0)
OR (transaction_action_id in (1,27)
AND transaction_source_type_id IN (3,6,13)
AND transaction_cost IS NULL)
OR (transaction_action_id in (1,27)
AND transaction_source_type_id NOT IN (1,3,5,6,13) )
OR (
((transaction_action_id IN (1,27) AND transaction_source_type_id = 5)
OR transaction_action_id IN (33,34))
AND NOT EXISTS (
SELECT 'X'
FROM wip_entities we
WHERE we.wip_entity_id = mmt.transaction_source_id
AND we.primary_item_id = mmt.inventory_item_id)) )
AND EXISTS (
SELECT 'X'
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = mmt.inventory_item_id
AND cpllc.pac_period_id = c_pac_period_id
AND cpllc.cost_group_id = c_cost_group_id);
SELECT
low_level_code
FROM cst_pac_low_level_codes
WHERE pac_period_id = c_pac_period_id
AND cost_group_id = c_cost_group_id
AND inventory_item_id = c_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_cg_tab(l_current_index).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
);
-- insert into cppb
l_error_num := 0;
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
,i_cost_group_id => p_cg_tab(l_current_index).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
);
FND_MESSAGE.set_token('MESSAGE', 'Error in insert/update cpbb for cost group id '
||p_cg_tab(l_current_index).cost_group_id||' ( '||l_error_code||' ) '||l_error_msg);
CSTPPWAC.insert_into_cppb(i_pac_period_id => p_period_id
,i_cost_group_id => p_cg_tab(l_current_index).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_item_cppb(i_pac_period_id => p_period_id
,i_cost_group_id => p_cg_tab(l_current_index).cost_group_id
,i_txn_category => l_txn_category
,i_item_id => p_inventory_item_id
,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
);
FND_MESSAGE.set_token('MESSAGE', 'Error in insert/update cpbb for cost group id '||p_cg_tab(l_current_index).cost_group_id||' ( '||l_error_code||' ) '||l_error_msg);
l_cost_update_type NUMBER;
SELECT
low_level_code
FROM cst_pac_low_level_codes
WHERE pac_period_id = c_pac_period_id
AND cost_group_id = c_cost_group_id
AND inventory_item_id = c_inventory_item_id;
SELECT
inventory_item_id
FROM cst_pac_itms_absl_codes
WHERE pac_period_id = c_pac_period_id
AND absorption_level_code = c_absorption_level_code
FOR UPDATE OF absorption_level_code;
SELECT distinct(mst.concatenated_segments)
FROM cst_pac_itms_absl_codes cpiac, MTL_SYSTEM_ITEMS_B_KFV mst
WHERE cpiac.pac_period_id = c_pac_period_id
AND cpiac.absorption_level_code < c_absorption_level_code
AND cpiac.inventory_item_id = mst.inventory_item_id;
DELETE cst_pac_itms_absl_codes
WHERE pac_period_id = p_period_id;
INSERT INTO cst_pac_itms_absl_codes
(inventory_item_id
,pac_period_id
,absorption_level_code
,process_flag
)
SELECT /*+ leading (MMT) INDEX (MMT MTL_MATERIAL_TRANSACTIONS_N5)*/
distinct(mmt.inventory_item_id)
,p_period_id
,1000
,'N'
FROM mtl_material_transactions mmt, cst_cost_groups ccg, cst_cost_group_assignments ccga
WHERE mmt.transaction_date between p_period_start_date AND p_period_end_date
AND mmt.organization_id = nvl(mmt.owning_organization_id, mmt.organization_id)
AND nvl(mmt.owning_tp_type,2) = 2
AND ccg.legal_entity = p_legal_entity_id
AND ccga.organization_id = mmt.organization_id
AND ccga.cost_group_id = ccg.cost_group_id
AND NOT EXISTS (SELECT /*+ no_unnest*/ 'X'
FROM cst_pac_low_level_codes cpllc
WHERE cpllc.inventory_item_id = mmt.inventory_item_id
AND cpllc.pac_period_id = p_period_id);
SELECT min(low_level_code) top_most_bom_level_code
, count(low_level_code) low_level_count
INTO l_topmost_bom_level_code
,l_low_level_count
FROM cst_pac_low_level_codes
WHERE pac_period_id = p_period_id;
INSERT INTO cst_pac_itms_absl_codes
(pac_period_id
,inventory_item_id
,absorption_level_code
,process_flag
)
SELECT
p_period_id
,cpllc.inventory_item_id
,min(low_level_code) bom_highest_level_code
,'N'
FROM cst_pac_low_level_codes cpllc
WHERE pac_period_id = p_period_id
GROUP BY inventory_item_id;
SELECT min(absorption_level_code)
INTO l_topmost_absl_level_code
FROM cst_pac_itms_absl_codes
WHERE pac_period_id = p_period_id;
SELECT min(cpiac.absorption_level_code) min_child_absl_level_code
INTO l_min_child_absl_level_code
FROM cst_pac_itms_absl_codes cpiac
WHERE cpiac.pac_period_id = p_period_id
AND cpiac.inventory_item_id IN (SELECT DISTINCT cpet.component_item_id
FROM cst_pac_explosion_temp cpet
WHERE cpet.pac_period_id = cpiac.pac_period_id
AND cpet.assembly_item_id = items_in_current_absl_row.inventory_item_id
AND cpet.component_item_id <> cpet.assembly_item_id
);
UPDATE CST_PAC_ITMS_ABSL_CODES
SET absorption_level_code = l_reposition_absl_level_code
WHERE CURRENT OF items_in_current_absl_cur;
SELECT count(1)
INTO l_count_rows
FROM mtl_material_transactions_temp mmtt
WHERE NVL(mmtt.transaction_status,0) <> 2
AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(mmtt.organization_id) = 'Y'
AND mmtt.transaction_date BETWEEN p_period_start_date AND p_period_end_date
AND ROWNUM = 1;
SELECT count(1)
INTO l_count_rows
FROM mtl_transactions_interface mti
WHERE CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(mti.organization_id) = 'Y'
AND mti.transaction_date BETWEEN p_period_start_date AND p_period_end_date
AND ROWNUM = 1;
SELECT count(1)
INTO l_count_rows
FROM wip_cost_txn_interface wcti
WHERE CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(wcti.organization_id) = 'Y'
AND wcti.transaction_date BETWEEN p_period_start_date AND p_period_end_date
AND ROWNUM = 1;
SELECT count(1)
INTO l_count_rows
FROM rcv_transactions_interface rti
WHERE rti.to_organization_code IN
(SELECT ood.organization_code
FROM cst_organization_definitions ood
WHERE CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(ood.organization_id) = 'Y'
)
AND rti.transaction_date BETWEEN p_period_start_date AND p_period_end_date
AND ROWNUM = 1;
SELECT count(1)
INTO l_count_rows
FROM mtl_material_transactions mmt
WHERE mmt.creation_date > ( SELECT MIN(cppp.process_date)
FROM cst_pac_process_phases cppp
WHERE
( (cppp.process_phase <= 4 OR cppp.process_phase = 7)
AND cppp.process_upto_date IS NOT NULL)
AND cppp.pac_period_id = p_pac_period_id
AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group(cppp.cost_group_id) = 'Y'
)
AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(mmt.organization_id) = 'Y'
AND mmt.transaction_date BETWEEN p_period_start_date AND p_period_end_date
AND ROWNUM = 1;
SELECT count(1)
INTO l_count_rows
FROM wip_transactions wt
WHERE wt.creation_date > ( SELECT MIN(cppp.process_date)
FROM cst_pac_process_phases cppp
WHERE
( (cppp.process_phase <= 4 OR cppp.process_phase = 7)
AND cppp.process_upto_date IS NOT NULL)
AND cppp.pac_period_id = p_pac_period_id
AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group(cppp.cost_group_id) = 'Y'
)
AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(wt.organization_id) = 'Y'
AND wt.transaction_date BETWEEN p_period_start_date AND p_period_end_date
AND ROWNUM = 1;
SELECT count(1)
INTO l_count_rows
FROM rcv_transactions rt
WHERE rt.creation_date > (SELECT MIN(cppp.process_date)
FROM cst_pac_process_phases cppp
WHERE
( (cppp.process_phase <= 4 OR cppp.process_phase = 7)
AND cppp.process_upto_date IS NOT NULL)
AND cppp.pac_period_id = p_pac_period_id
AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group(cppp.cost_group_id) = 'Y'
)
AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(rt.organization_id) = 'Y'
AND rt.transaction_date BETWEEN p_period_start_date AND p_period_end_date
AND ROWNUM = 1;
SELECT count(1)
INTO l_count_rows
FROM rcv_accounting_events rae
WHERE rae.creation_date > ( SELECT MIN(cppp.process_date)
FROM cst_pac_process_phases cppp
WHERE
( (cppp.process_phase <= 4 OR cppp.process_phase = 7)
AND cppp.process_upto_date IS NOT NULL)
AND cppp.pac_period_id = p_pac_period_id
AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group(cppp.cost_group_id) = 'Y'
)
AND CST_PAC_ITERATION_PROCESS_PVT.Check_Cst_Group_Org(rae.organization_id) = 'Y'
AND rae.transaction_date BETWEEN p_period_start_date AND p_period_end_date
AND rae.event_type_id IN (7,8, 9, 10)
AND ROWNUM = 1;
PROCEDURE Insert_Ending_Balance_All_Cgs
(p_pac_period_id IN NUMBER
,p_cg_tab IN CST_PERIODIC_ABSORPTION_PROC.tbl_type
,p_end_date IN DATE
,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
-- routine name local constant variable
l_routine CONSTANT VARCHAR2(30) := 'Insert_Ending_Balance_All_Cgs';
, '>> CST_PERIODIC_ABSORPTION_PROC.Insert_Ending_Balance_All_Cgs'
);
CSTPPWAC.insert_ending_balance(i_pac_period_id => p_pac_period_id
,i_cost_group_id => p_cg_tab(l_cg_idx).cost_group_id
,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
);
FND_MESSAGE.set_token('MESSAGE', 'Error in CSTPPWAC.insert_ending_balance for cost group id '||p_cg_tab(l_cg_idx).cost_group_id||' ( '||l_error_code||' ) '||l_error_msg);
, '<< CST_PERIODIC_ABSORPTION_PROC.Insert_Ending_Balance_All_Cgs'
);
END Insert_Ending_Balance_All_Cgs;
SELECT
inventory_item_id
FROM cst_pac_itms_absl_codes
WHERE pac_period_id = c_pac_period_id
AND absorption_level_code = c_absorption_level_code
AND process_flag = 'N';
SELECT low_level_code
FROM cst_pac_low_level_codes
WHERE pac_period_id = c_pac_period_id
AND inventory_item_id = c_inventory_item_id
AND rownum = 1;
SELECT
low_level_code
FROM cst_pac_low_level_codes
WHERE pac_period_id = c_pac_period_id
AND cost_group_id = c_cost_group_id
AND inventory_item_id = c_inventory_item_id;
l_cost_update_type NUMBER;
SELECT nvl(iteration_proc_flag,'N')
INTO l_iteration_proc_flag
FROM cst_le_cost_types
WHERE legal_entity = p_legal_entity
AND cost_type_id = p_cost_type_id;
UPDATE CST_PAC_INTORG_ITMS_TEMP cpiit
SET cpiit.absorption_level_code =
(SELECT absorption_level_code
FROM cst_pac_itms_absl_codes
WHERE pac_period_id = cpiit.pac_period_id
AND inventory_item_id = cpiit.inventory_item_id
)
WHERE cpiit.pac_period_id = p_period_id;
SELECT NVL(min(absorption_level_code),1000)
INTO l_topmost_absl_level_code
FROM cst_pac_itms_absl_codes
WHERE pac_period_id = p_period_id;
SELECT NVL(max(absorption_level_code),1000)
INTO l_lowest_absl_level_code
FROM cst_pac_itms_absl_codes
WHERE pac_period_id = p_period_id
AND process_flag = 'N';
CST_PERIODIC_ABSORPTION_PROC.G_ITEM_LEVEL_TBL.DELETE;
SELECT count(1)
INTO
l_interorg_item_flag
FROM
cst_pac_intorg_itms_temp
WHERE inventory_item_id = l_inventory_item_id
AND pac_period_id = p_period_id
AND absorption_level_code = l_current_level_code
AND rownum = 1;
SELECT decode(max(iteration_count), 0, 'N', 'Y')
INTO l_assembly_processed_flag
FROM
cst_pac_intorg_itms_temp
WHERE pac_period_id = p_period_id
AND absorption_level_code = l_current_level_code
AND inventory_item_id = l_inventory_item_id;
l_cost_update_type := 2; -- PCU Value Change
-- Periodic Cost Update value change only for non-interorg items
-- both completion and no completion items are included
CST_PERIODIC_ABSORPTION_PROC.Periodic_Cost_Update_By_Level
(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_cg_tab(l_cg_idx).cost_group_id
,p_inventory_item_id => l_inventory_item_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_cg_tab(l_cg_idx).master_org_id
,p_uom_control => p_uom_control
,p_low_level_code => l_low_level_code
,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);
SELECT count(1)
INTO l_tol_item_flag
FROM cst_pac_intorg_itms_temp
WHERE pac_period_id = p_period_id
AND inventory_item_id = l_inventory_item_id
AND tolerance_flag = 'N'
AND rownum = 1;
UPDATE CST_PAC_ITMS_ABSL_CODES
SET process_flag = 'Y'
WHERE pac_period_id = p_period_id
AND inventory_item_id = l_inventory_item_id
AND absorption_level_code = l_current_level_code;
Insert_Ending_Balance_All_Cgs(p_pac_period_id => p_period_id
,p_cg_tab => p_cg_tab
,p_end_date => p_end_date
,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
);
END IF; -- insert into CPPB only at the period end
SELECT count(1)
INTO l_tolerance_flag
FROM cst_pac_intorg_itms_temp
WHERE pac_period_id = p_period_id
AND tolerance_flag = 'N'
AND rownum = 1;
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
, cst_cost_group_assignments ccga
WHERE mmt.transaction_date BETWEEN c_start_date AND c_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
/* exclude OPM logical intransit receipts */
AND mmt.transaction_action_id <> 15
/* Ensure that only logical transactions get picked up */
AND ((nvl(mmt.logical_transaction,3) = 1
AND mmt.parent_transaction_id IS NOT NULL
AND nvl(mmt.logical_trx_type_code,6) <= 5)
OR mmt.transaction_type_id = 20)
ORDER BY
transaction_date
, transaction_id;
SELECT phase_code
FROM FND_CONCURRENT_REQUESTS
WHERE request_id = c_request_id;
G_REQUEST_TABLE.delete;
SELECT
ccg.cost_group_id cost_group_id
, ccg.cost_group cost_group
FROM cst_cost_groups ccg
, cst_le_cost_types clct
WHERE ccg.legal_entity = clct.legal_entity
AND clct.legal_entity = p_legal_entity
AND clct.cost_type_id = p_cost_type_id;