[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE update_ids_push_log (p_ids_key VARCHAR2,
p_bal_rec bal_rec ) IS
BEGIN
UPDATE opi_ids_push_log
SET
beg_int_qty = p_bal_rec.end_int_qty,
beg_int_val_b = p_bal_rec.end_int_val_b,
beg_onh_qty = p_bal_rec.end_onh_qty,
beg_onh_val_b = p_bal_rec.end_onh_val_b,
beg_wip_qty = p_bal_rec.end_wip_qty,
beg_wip_val_b = p_bal_rec.end_wip_val_b,
end_int_qty = p_bal_rec.end_int_qty,
end_int_val_b = p_bal_rec.end_int_val_b,
end_onh_qty = p_bal_rec.end_onh_qty,
end_onh_val_b = p_bal_rec.end_onh_val_b,
end_wip_qty = p_bal_rec.end_wip_qty,
end_wip_val_b = p_bal_rec.end_wip_val_b,
base_uom = p_bal_rec.base_uom,
item_status = p_bal_rec.item_status,
item_type = p_bal_rec.item_type,
nettable_flag = p_bal_rec.nettable_flag,
push_flag =1
WHERE ids_key = p_ids_key;
END update_ids_push_log;
PROCEDURE insert_ids_push_log ( p_ids_key VARCHAR2,
p_trx_date DATE,
p_period_flag NUMBER,
p_key key_rec,
p_bal_rec bal_rec) IS
BEGIN
--dbms_output.put_line('count = 1 ' || p_key.organization_id);
INSERT INTO opi_ids_push_log
(ids_key,
cost_group_id,
organization_id,
inventory_item_id,
revision,
subinventory_code,
locator_id,
project_locator_id,
lot_number,
trx_date,
period_flag,
push_flag,
beg_int_qty, beg_int_val_b,
beg_onh_qty, beg_onh_val_b,
beg_wip_qty, beg_wip_val_b,
end_int_qty, end_int_val_b,
end_onh_qty, end_onh_val_b,
end_wip_qty, end_wip_val_b,
base_uom,
item_status,
item_type,
nettable_flag)
VALUES
( p_ids_key,
p_key.cost_group_id,
p_key.organization_id,
p_key.inventory_item_id,
p_key.revision,
p_key.subinventory_code,
p_key.locator_id,
p_key.project_locator_id,
p_key.lot_number,
p_trx_date,
p_period_flag,
1,
p_bal_rec.end_int_qty, p_bal_rec.end_int_val_b,
p_bal_rec.end_onh_qty, p_bal_rec.end_onh_val_b,
p_bal_rec.end_wip_qty, p_bal_rec.end_wip_val_b,
p_bal_rec.end_int_qty, p_bal_rec.end_int_val_b,
p_bal_rec.end_onh_qty, p_bal_rec.end_onh_val_b,
p_bal_rec.end_wip_qty, p_bal_rec.end_wip_val_b,
p_bal_rec.base_uom,
p_bal_rec.item_status,
p_bal_rec.item_type,
p_bal_rec.nettable_flag);
END insert_ids_push_log;
select max( last_push_inv_txn_date), max(last_push_wip_txn_date)
from opi_ids_push_date_log
where organization_id = p_organization_id;
SELECT DISTINCT inventory_item_id,
organization_id,
cost_group_id,
revision,
lot_number,
subinventory_code,
locator_id,
project_locator_id
FROM opi_ids_push_log
WHERE trx_date BETWEEN (p_from_date -1) AND p_to_date
AND organization_id = p_organization_id;
SELECT Trunc(period_start_date) start_date,
Trunc(schedule_close_date) end_date
FROM org_acct_periods
WHERE organization_id = l_organization_id
AND (( period_start_date between p_from_date
and p_to_date )
OR( schedule_close_date between p_from_date
and p_to_date )
OR
( (p_from_date between period_start_date and schedule_close_date)
AND (p_to_date between period_start_date and
schedule_close_date) )
)
ORDER BY start_date;
SELECT beg_int_qty, beg_int_val_b,
beg_onh_qty, beg_onh_val_b,
beg_wip_qty, beg_wip_val_b,
end_int_qty, end_int_val_b,
end_onh_qty, end_onh_val_b,
end_wip_qty, end_wip_val_b,
trx_date, base_uom, item_status, item_type, nettable_flag
FROM opi_ids_push_log
WHERE ids_key = l_ids_key
AND period_flag = 1;
SELECT beg_int_qty, beg_int_val_b,
beg_onh_qty, beg_onh_val_b,
beg_wip_qty, beg_wip_val_b,
end_int_qty, end_int_val_b,
end_onh_qty, end_onh_val_b,
end_wip_qty, end_wip_val_b,
trx_date, base_uom, item_status, item_type, nettable_flag
FROM opi_ids_push_log
WHERE ids_key = l_ids_key;
SELECT beg_int_qty, beg_int_val_b,
beg_onh_qty, beg_onh_val_b,
beg_wip_qty, beg_wip_val_b,
end_int_qty, end_int_val_b,
end_onh_qty, end_onh_val_b,
end_wip_qty, end_wip_val_b,
trx_date, base_uom, item_status, item_type, nettable_flag
FROM opi_ids_push_log
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
AND Nvl(cost_group_id, -999) = Nvl(l_cost_group_id, -999)
AND Nvl(revision, '-999') = Nvl(l_revision, '-999')
AND Nvl(lot_number, '-999') = Nvl(l_lot_number, '-999')
AND Nvl(subinventory_code, '-999') = Nvl(l_subinventory_code, '-999')
AND Nvl(project_locator_id, -999) = Nvl(l_locator_id, -999)
AND trx_date IN ( SELECT MAX(trx_date)
FROM opi_ids_push_log
WHERE inventory_item_id = l_inventory_item_id
AND organization_id = l_organization_id
AND Nvl(cost_group_id, -999) = Nvl(l_cost_group_id, -999)
AND Nvl(revision, '-999') = Nvl(l_revision, '-999')
AND Nvl(lot_number, '-999') = Nvl(l_lot_number, '-999')
AND Nvl(subinventory_code, '-999') =
Nvl(l_subinventory_code, '-999')
AND Nvl(project_locator_id, -999) = Nvl(l_locator_id, -999)
AND trx_date BETWEEN l_trx_start_date AND l_trx_end_date
-- activity check
AND ( (Nvl(beg_int_qty,0) - Nvl(end_int_qty,0)) <> 0
OR ( Nvl(beg_int_val_b,0) - Nvl(end_int_val_b,0)) <> 0
OR ( Nvl(beg_onh_qty,0) - Nvl(end_onh_qty,0) ) <> 0
OR ( Nvl(beg_onh_val_b,0) - Nvl(end_onh_val_b,0)) <> 0
OR ( Nvl(beg_wip_qty,0) - Nvl(end_wip_qty,0) ) <> 0
OR ( Nvl(beg_wip_val_b,0) - Nvl(end_wip_val_b,0)) <> 0
OR nvl(total_rec_qty,0) <> 0
OR nvl(total_rec_val_b, 0) <> 0
OR nvl(tot_issues_qty,0) <> 0
OR nvl(tot_issues_val_b,0) <> 0
OR Nvl(from_org_qty,0) <> 0
OR Nvl(from_org_val_b,0) <> 0
OR Nvl(inv_adj_qty,0) <> 0
OR Nvl(inv_adj_val_b,0) <> 0
OR Nvl(po_del_qty, 0) <> 0
OR Nvl(po_del_val_b, 0) <> 0
OR Nvl(to_org_qty,0) <> 0
OR Nvl(to_org_val_b,0) <> 0
OR Nvl(tot_cust_ship_qty,0) <> 0
OR Nvl(tot_cust_ship_val_b, 0) <> 0
OR Nvl(wip_assy_qty, 0) <> 0
OR Nvl(wip_assy_val_b,0) <> 0
OR Nvl(wip_comp_qty,0) <> 0
OR Nvl(wip_comp_val_b,0) <> 0
OR Nvl(wip_issue_qty,0) <> 0
OR Nvl(wip_issue_val_b,0) <> 0
)
);
SELECT Trunc(MAX(period_start_date)) start_date,
Trunc(MAX(schedule_close_date)) end_date
FROM org_acct_periods
WHERE organization_id = l_organization_id
AND schedule_close_date
SELECT Decode( nvl(period_flag, 999), 999,999,
-- no period start/end entry
1) period_start_flag,
Decode( (Nvl(beg_int_qty,0) - Nvl(end_int_qty,0)), 0,
Decode( ( Nvl(beg_int_val_b,0) - Nvl(end_int_val_b,0)), 0,
Decode( ( Nvl(beg_onh_qty,0) - Nvl(end_onh_qty,0) ), 0,
Decode( ( Nvl(beg_onh_val_b,0) - Nvl(end_onh_val_b,0)), 0,
Decode( ( Nvl(beg_wip_qty,0) - Nvl(end_wip_qty,0) ), 0,
Decode( ( Nvl(beg_wip_val_b,0) - Nvl(end_wip_val_b,0)), 0,
decode( nvl(total_rec_qty,0), 0,
decode( nvl(total_rec_val_b, 0), 0,
decode( nvl(tot_issues_qty,0), 0,
decode( nvl(tot_issues_val_b,0), 0,
Decode(Nvl(from_org_qty,0), 0,
Decode(Nvl(from_org_val_b,0),0,
Decode(Nvl(inv_adj_qty,0),0,
Decode(Nvl(inv_adj_val_b,0),0,
Decode(Nvl(po_del_qty, 0),0,
Decode(Nvl(po_del_val_b, 0),0,
Decode( Nvl(to_org_qty,0),0,
Decode( Nvl(to_org_val_b,0),0,
Decode( Nvl(tot_cust_ship_qty,0),0,
Decode( Nvl(tot_cust_ship_val_b, 0),0,
Decode( Nvl(wip_assy_qty, 0),0,
Decode( Nvl(wip_assy_val_b,0),0,
Decode( Nvl(wip_comp_qty,0),0,
Decode( Nvl(wip_comp_val_b,0),0,
Decode( Nvl(wip_issue_qty,0),0,
Decode( Nvl(wip_issue_val_b,0),0,
0, -- no activity at all
1), 1), 1), 1), 1), 1), 1), 1), 1), 1), 1), 1), 1), 1),1), 1),
1), 1), 1), 1), 1), 1), 1), 1), 1), 1) activity_flag
FROM opi_ids_push_log
WHERE ids_key = l_ids_key;
select Trunc( min(trx_date) )
into l_first_push_date
from opi_ids_push_log
where organization_id = p_organization_id;
select least(p_from_date,
nvl(l_inv_txn_date, to_date('01-12-3000','DD-MM-YYYY') ),
nvl(l_wip_txn_date, to_date('01-12-3000','DD-MM-YYYY') ) )
into l_from_date
from dual;
SELECT max (trunc (schedule_close_date))
INTO l_combs_start_date
FROM org_acct_periods
WHERE organization_id = p_organization_id
AND schedule_close_date < trunc (l_from_date);
SELECT max (trunc (period_start_date))
INTO l_combs_start_date
FROM org_acct_periods
WHERE organization_id = p_organization_id
AND period_start_date <= trunc (l_from_date);
INSERT INTO opi_ids_push_log
(ids_key, cost_group_id,
organization_id,inventory_item_id,
revision, subinventory_code,
locator_id, project_locator_id,
lot_number, trx_date,
period_flag, push_flag,
beg_int_qty, beg_int_val_b,
beg_onh_qty, beg_onh_val_b,
beg_wip_qty, beg_wip_val_b,
end_int_qty, end_int_val_b,
end_onh_qty, end_onh_val_b,
end_wip_qty, end_wip_val_b,
base_uom, item_status,
item_type,nettable_flag)
VALUES
( l_prd_start_ids_key, l_key.cost_group_id,
l_key.organization_id,
l_key.inventory_item_id,
l_key.revision, l_key.subinventory_code,
l_key.locator_id, l_key.project_locator_id,
l_key.lot_number, l_period.start_date,
0, 1, 0,0,0,0,0,0, 0,0,0,0,0,0,
l_latest_activity_entry.base_uom,
l_latest_activity_entry.item_status,
l_latest_activity_entry.item_type,
l_latest_activity_entry.nettable_flag);
UPDATE opi_ids_push_log
SET period_flag = 0,
push_flag =1
WHERE ids_key = l_prd_start_ids_key;
UPDATE opi_ids_push_log
SET
beg_int_qty = 0,
beg_int_val_b = 0,
beg_onh_qty = 0,
beg_onh_val_b = 0,
beg_wip_qty = 0,
beg_wip_val_b = 0,
end_int_qty = 0,
end_int_val_b = 0,
end_onh_qty = 0,
end_onh_val_b = 0,
end_wip_qty = 0,
end_wip_val_b = 0,
base_uom =
l_latest_activity_entry.base_uom,
item_status =
l_latest_activity_entry.item_status,
item_type =
l_latest_activity_entry.item_type,
nettable_flag =
l_latest_activity_entry.nettable_flag,
push_flag = 1
WHERE ids_key = l_prd_start_ids_key;
insert_ids_push_log(l_prd_start_ids_key,
l_period.start_date,
0, -- p_period_flag
l_key,
l_last_period_end_entry );
UPDATE opi_ids_push_log
SET period_flag = 0,
push_flag =1
WHERE ids_key = l_prd_start_ids_key;
UPDATE opi_ids_push_log
SET period_flag = 0,
push_flag =1
WHERE ids_key = l_prd_start_ids_key;
update_ids_push_log(l_prd_start_ids_key,
l_last_period_end_entry);
update_ids_push_log(l_prd_end_ids_key,
l_latest_activity_entry);
insert_ids_push_log(l_prd_end_ids_key,
l_period.end_date,
1, -- p_period_flag
l_key,
l_latest_activity_entry);
UPDATE opi_ids_push_log
SET period_flag = 1, push_flag = 1
WHERE ids_key = l_prd_end_ids_key;
DELETE opi_ids_push_log
WHERE ids_key = l_prd_start_ids_key;
DELETE opi_ids_push_log
WHERE ids_key = l_prd_start_ids_key;
UPDATE opi_ids_push_log
SET period_flag = 0, push_flag = 1
WHERE ids_key = l_prd_start_ids_key;
DELETE opi_ids_push_log
WHERE ids_key = l_prd_start_ids_key;
update_ids_push_log(l_prd_start_ids_key,
l_last_period_end_entry);
insert_ids_push_log(l_prd_start_ids_key,
l_period.start_date,
0,
l_key,
l_last_period_end_entry);
UPDATE opi_ids_push_log
SET period_flag = 0, push_flag = 1
WHERE ids_key = l_prd_start_ids_key;
update_ids_push_log(l_prd_end_ids_key,
l_beg_inv_bal_prd_start_entry);
update_ids_push_log(l_prd_end_ids_key,
l_beg_inv_bal_prd_start_entry);
insert_ids_push_log(l_prd_end_ids_key,
l_period.end_date,
1,
l_key,
l_beg_inv_bal_prd_start_entry);
insert_ids_push_log(l_prd_end_ids_key,
l_period.end_date,
1,
l_key,
l_beg_inv_bal_prd_start_entry);
UPDATE opi_ids_push_log
SET period_flag = 1, push_flag = 1
WHERE ids_key = l_prd_end_ids_key;
DELETE opi_ids_push_log
WHERE ids_key = l_prd_end_ids_key;
cost_update_inventory (p_from_date, p_to_date, p_organization_id,x_status);
SELECT csc.standard_cost unit_cost -- Standard cost method logic.
FROM CST_STANDARD_COSTS csc
WHERE csc.ORGANIZATION_ID = org_id
AND csc.INVENTORY_ITEM_ID = item_id
AND csc.STANDARD_COST_REVISION_DATE =
(SELECT max(csc2.STANDARD_COST_REVISION_DATE)
FROM CST_STANDARD_COSTS csc2
WHERE csc2.ORGANIZATION_ID = org_id
AND csc2.INVENTORY_ITEM_ID = item_id
AND csc2.STANDARD_COST_REVISION_DATE <
trunc(cost_date) + 1 );
select actual_cost
from mtl_material_transactions
where transaction_id = (
select max(transaction_id)
from mtl_material_transactions
where inventory_item_id=item_id
and organization_id=org_id
and actual_cost is not null
and transaction_date =
(select max(transaction_date)
from mtl_material_transactions
where inventory_item_id=item_id
and organization_id=org_id
and trunc(transaction_date) <= p_cost_date
and actual_cost is not null));
SELECT ids_key
FROM opi_ids_push_log
WHERE organization_id = p_organization_id
AND inventory_item_id = inv_item_id
AND trx_date = day_to_check
AND ( NVL(beg_int_val_b,0) - NVL(end_int_val_b,0) <> 0
OR NVL(beg_onh_val_b,0) - NVL(end_onh_val_b,0) <> 0
OR NVL(beg_wip_val_b,0) - NVL(end_wip_val_b,0) <> 0);
SELECT min(push_log.trx_date)
FROM opi_ids_push_log push_log
WHERE push_log.period_flag = 0
AND push_log.organization_id = p_organization_id
AND push_log.inventory_item_id = p_inventory_item_id
AND push_log.trx_date > v_curr_date;
PROCEDURE cost_update_inventory (p_from_date DATE, p_to_date DATE,
p_organization_id NUMBER, p_status OUT NOCOPY NUMBER)
IS
-- cost update transactions are stored in the mtl_material_transaction
-- table
-- standard cost update transaction type IDs in mtl_material_transaction
-- table is 24
COST_UPDATE_TRX_ID CONSTANT NUMBER := 24;
PRIMARY_COST_METHOD_UPDATE CONSTANT NUMBER := 1;
CURSOR cost_update_trx_csr (p_from_date DATE, p_to_date DATE,
p_organization_id NUMBER)
IS
SELECT DISTINCT mmt.inventory_item_id inventory_item_id,
mmt.transaction_date transaction_date
FROM mtl_material_transactions mmt, mtl_parameters mp,
mtl_system_items_b msi
WHERE mmt.transaction_type_id = 24
AND mmt.transaction_date BETWEEN p_from_date AND p_to_date
AND mmt.organization_id = p_organization_id
AND mmt.organization_id = mp.organization_id -- standard costing org
AND msi.organization_id = mmt.organization_id
AND msi.inventory_item_id = mmt.inventory_item_id
AND msi.inventory_asset_flag = 'Y' -- don't pick expense items
AND mp.primary_cost_method = 1
ORDER BY mmt.inventory_item_id, mmt.transaction_date;
SELECT push_log.trx_date trx_date
FROM opi_ids_push_log push_log
WHERE push_log.organization_id = p_organization_id
AND push_log.inventory_item_id = p_inventory_item_id
AND push_log.period_flag = 1
AND push_log.push_flag = 1
AND push_log.trx_date >= p_from_date
GROUP BY push_log.trx_date
ORDER BY trx_date;
SELECT period_start_date
INTO l_from_date_per_start
FROM org_acct_periods
WHERE period_start_date <= trunc (p_from_date)
AND schedule_close_date >= trunc (p_from_date)
AND organization_id = p_organization_id;
FOR cost_update_item_org IN
cost_update_trx_csr (l_from_date_per_start, p_to_date,
p_organization_id)
LOOP
--DBMS_OUTPUT.PUT_LINE ('Looking for period end entries.');
cost_update_item_org.inventory_item_id,
l_from_date_per_start)
LOOP
--DBMS_OUTPUT.PUT_LINE ('Looking for new cost.');
cost_update_item_org.inventory_item_id,
sub_period_end.trx_date);
cost_update_item_org.inventory_item_id))
THEN
-- Find the cost at the start of the period end date
-- i.e. the cost up to the day before
period_end_beg_unit_cost :=
cost_on_date (p_organization_id,
cost_update_item_org.inventory_item_id,
sub_period_end.trx_date - 1);
UPDATE opi_ids_push_log
SET
beg_int_val_b = beg_int_qty * period_end_beg_unit_cost,
beg_onh_val_b = beg_onh_qty * period_end_beg_unit_cost,
beg_wip_val_b = beg_wip_qty * period_end_beg_unit_cost,
end_int_val_b = end_int_qty * period_end_unit_cost,
end_onh_val_b = end_onh_qty * period_end_unit_cost,
end_wip_val_b = end_wip_qty * period_end_unit_cost
WHERE organization_id = p_organization_id
AND inventory_item_id = cost_update_item_org.inventory_item_id
AND trx_date = sub_period_end.trx_date
AND subinventory_code NOT IN -- don't update expense sub
(SELECT secondary_inventory_name
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND asset_inventory <> 1) -- expense sub
AND period_flag = 1 -- just to be safe
AND push_flag = 1; -- just to be safe
cost_update_item_org.inventory_item_id);
cost_update_item_org.inventory_item_id))
THEN
-- get the cost as of this day
next_period_start_unit_cost :=
cost_on_date (p_organization_id,
cost_update_item_org.inventory_item_id,
next_period_start);
UPDATE opi_ids_push_log
SET
beg_int_val_b = beg_int_qty * period_end_unit_cost,
beg_onh_val_b = beg_onh_qty * period_end_unit_cost,
beg_wip_val_b = beg_wip_qty * period_end_unit_cost,
end_int_val_b = end_int_qty * next_period_start_unit_cost,
end_onh_val_b = end_onh_qty * next_period_start_unit_cost,
end_wip_val_b = end_wip_qty * next_period_start_unit_cost
WHERE organization_id = p_organization_id
AND inventory_item_id = cost_update_item_org.inventory_item_id
AND subinventory_code NOT IN -- don't update expense sub
(SELECT secondary_inventory_name
FROM mtl_secondary_inventories
WHERE organization_id = p_organization_id
AND asset_inventory <> 1) -- expense sub
AND trx_date = next_period_start
AND period_flag = 0 -- just to be safe
AND push_flag = 1; -- just to be safe
END LOOP; -- end FOR cost_update_item_org
EDW_LOG.PUT_LINE ('Exception raised in cost_update_inventory');
END cost_update_inventory;