[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
rowid
, secondary_inventory_name
, material_account
, material_overhead_account
, resource_account
, overhead_account
, outside_processing_account
, expense_account
, encumbrance_account
from mtl_secondary_inventories
where (
default_cost_group_id is null AND
organization_id = l_organization_id
);
update mtl_secondary_inventories
set default_cost_group_id = l_cost_group_id
where organization_id = l_organization_id
and secondary_inventory_name = l_secondary_inventory_name;
SELECT
-- ROWID,
acct_period_id
, inventory_type
, cost_group_id
, sum(inventory_value) inventory_value
from mtl_period_summary
where organization_id = l_organization_id
group by
acct_period_id
, organization_id
, inventory_type
, cost_group_id;
select
rowid
, secondary_inventory
from mtl_period_summary
where
(
cost_group_id is null AND
organization_id = l_organization_id
);
update mtl_period_summary
set cost_group_id = l_cost_group_id
where rowid = l_rowid;
update mtl_period_summary
set cost_group_id = l_cost_group_id
where rowid = l_rowid;
** Delete data. It will be reloaded from memory in just a second
*/
delete mtl_period_summary WHERE organization_id = l_organization_id;
select sysdate into l_date from dual;
insert into mtl_period_summary(
ACCT_PERIOD_ID
, ORGANIZATION_ID
, INVENTORY_TYPE
, SECONDARY_INVENTORY
, LAST_UPDATE_DATE
, LAST_UPDATED_BY
, CREATION_DATE
, CREATED_BY
, LAST_UPDATE_LOGIN
, INVENTORY_VALUE
, REQUEST_ID
, PROGRAM_APPLICATION_ID
, PROGRAM_ID
, PROGRAM_UPDATE_DATE
, COST_GROUP_ID)
values(
l_ps_tbl(i).acct_period_id
, l_ps_tbl(i).organization_id
, l_ps_tbl(i).inventory_type
, NULL
, l_date
, l_user_id
, l_date
, l_user_id
, l_login_id
, l_ps_tbl(i).inventory_value
, l_request_id
, l_prog_appl_id
, l_program_id
, l_date
, l_ps_tbl(i).cost_group_id);
select
ROWID
, material_account
, material_overhead_account
, resource_account
, overhead_account
, outside_processing_account
, expense_account
, encumbrance_account
from mtl_parameters
where (
default_cost_group_id is null and
organization_id = l_organization_id
);
update mtl_parameters
set default_cost_group_id = l_cost_group_id
where organization_id = l_organization_id;
select
rowid
, subinventory_code
, locator_id
, project_id
, task_id
from mtl_onhand_quantities
where organization_id = l_organization_id
and cost_group_id is null;
select
to_number(nvl(segment19,'0'))
, to_number(nvl(segment20,'0'))
into
v_project_id
, v_task_id
from mtl_item_locations
where organization_id = l_organization_id
and inventory_location_id = l_locator_id;
SELECT NVL(primary_cost_method,1) INTO l_cost_method
FROM mtl_parameters WHERE
organization_id = l_organization_id ;
select costing_group_id
into l_cost_group_id
from pjm_project_parameters
where project_id = v_project_id
and organization_id = l_organization_id;
update mtl_onhand_quantities
set
cost_group_id = l_cost_group_id
, project_id = v_project_id
, task_id = v_task_id
where rowid = l_rowid;
SELECT NVL(primary_cost_method,1) INTO l_cost_method
FROM mtl_parameters WHERE
organization_id = l_organization_id ;
select nvl(default_cost_group_id,0)
into l_org_cost_group_id
from mtl_parameters
where organization_id = l_organization_id;
update mtl_onhand_quantities
set cost_group_id = l_cost_group_id
where rowid = l_rowid;
select
ROWID
, transaction_id
, subinventory_code
, transfer_organization_id
, transfer_subinventory
, project_id
, to_project_id
, cost_group_id
, transfer_cost_group_id
, transfer_transaction_id
, transaction_action_id
, shipment_number
, inventory_item_id
from mtl_material_transactions
where organization_id = l_organization_id;
select
ms.rowid
, ms.intransit_owning_org_id
from mtl_supply ms,
rcv_shipment_headers rsh
where rsh.shipment_num = l_shipment_number
and ms.shipment_header_id = rsh.shipment_header_id
and ms.supply_type_code = 'SHIPMENT'
and ms.intransit_owning_org_id is not null
and ms.item_id = l_inventory_item_id
and ms.from_organization_id = l_organization_id
and ms.cost_group_id is null;
l_cost_group_update boolean;
l_transfer_cost_group_update boolean;
l_cost_group_update := FALSE;
l_transfer_cost_group_update := FALSE;
l_cost_group_update := TRUE;
l_transfer_cost_group_update := TRUE;
if (l_cost_group_update = TRUE) and
(l_transfer_cost_group_update = TRUE) then
update mtl_material_transactions
set
cost_group_id = l_cost_group_id
, transfer_cost_group_id = l_transfer_cost_group_id
where transaction_id = l_transaction_id;
elsif (l_cost_group_update = TRUE) then
update mtl_material_transactions
set cost_group_id = l_cost_group_id
where transaction_id = l_transaction_id;
elsif (l_transfer_cost_group_update = TRUE) then
update mtl_material_transactions
set transfer_cost_group_id = l_transfer_cost_group_id
where transaction_id = l_transaction_id;
** If intransit shipment(action_id =21), we have to update
** corresponding record in MTL_SUPPLY too
*/
if (l_transaction_action_id = 21) and
(l_shipment_number is not null) THEN
l_table_name := 'mmt_supply';
update mtl_supply
set cost_group_id = l_ms_cost_group_id
where rowid = l_ms_rowid;
select to_char(sysdate, 'DD-MON-YY HH24:MI:SS')
into l_date from dual;
insert into mmt_summary values('Org ' ||
to_char(l_organization_id) ||
':' || l_date);
INSERT INTO COST_UPGR_ERROR_TABLE ( table_name, rowid_value, org_id,
error_mesg, proc_name)
VALUES ( p_table_name, p_rowid, p_org_id, substr(l_msg,1,800), p_proc_name);
cursor oid_cursor is select organization_id
from mtl_parameters ;