[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
SELECT cic.item_cost item_cost
FROM cst_item_costs cic
WHERE cic.organization_id = p_org_id
AND cic.inventory_item_id = p_item_id
and cost_type_id=1;
corresponding to a standard cost update (SCU). When we look
up the item cost for an item with a SCU which:
1. Was made prior to the start date of the collection program
2. Was the last transaction on the item before the start
date of the collection program,
we get the wrong cost from the actual_cost column of mcacd
as the starting cost for the collection program.
To correct this, we need to do the following:
1. Get the historical item cost from the csc (cst_standard_costs)
table. This table has the historical costs after an SCU is
made. We just need the latest cost prior to the start date.
If there is more than one SCU on the same day, use the latest
cost on that day.
2. If csc is has no data prior to the start date, but has data
after the start date,
----Then use the cost in the mmt (mtl_material_transactions)
because the cic (cst_item_costs) no longer has the
historical cost.
----Else use the cost in the CIC
Parameters IN: p_org_id - standard costing organization_id
p_item_id - inventory item id
p_date - date for which we need cost
Return values: item_cost (NUMBER) - item cost
Error Handling:
Date Author Action
25th Sept, 2002 Dinkar Gupta Wrote function
*/
FUNCTION std_costing_org_item_cost (p_org_id IN NUMBER, p_item_id IN NUMBER,
p_date IN DATE)
RETURN NUMBER
IS
-- procedure name
proc_name VARCHAR2(30) := 'std_costing_org_item_cost';
SELECT csc.standard_cost unit_cost
FROM cst_standard_costs csc
WHERE csc.organization_id = p_org_id
AND csc.inventory_item_id = p_item_id
AND csc.standard_cost_revision_date =
(SELECT max(csc2.standard_cost_revision_date)
FROM cst_standard_costs csc2
WHERE csc2.organization_id = p_org_id
AND csc2.inventory_item_id = p_item_id
AND csc2.standard_cost_revision_date <
trunc(p_cost_date) + 1);
SELECT csc.standard_cost unit_cost
FROM cst_standard_costs csc
WHERE csc.organization_id = p_org_id
AND csc.inventory_item_id = p_item_id;
SELECT actual_cost
FROM mtl_material_transactions
WHERE transaction_id =
(SELECT max(transaction_id)
FROM mtl_material_transactions
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND actual_cost IS NOT NULL
AND transaction_type_id NOT IN
(73, 80, 25, 26, 28, 90, 91, 92,
55, 56, 57, 58, 87, 88, 89, 24)
AND organization_id = NVL(owning_organization_id, organization_id)
AND NVL(OWNING_TP_TYPE,2) = 2
AND transaction_date =
(SELECT max(transaction_date)
FROM mtl_material_transactions
WHERE inventory_item_id = p_item_id
AND organization_id = p_org_id
AND (transaction_date) <
trunc(p_cost_date) + 1
AND actual_cost IS NOT NULL
AND transaction_type_id NOT IN
(73, 80, 25, 26, 28, 90, 91, 92,
55, 56, 57, 58, 87, 88, 89, 24)));
SELECT max (macd.transaction_id)
INTO l_trx_id
FROM mtl_cst_actual_cost_details macd,
mtl_material_transactions mmt
WHERE mmt.transaction_id = macd.transaction_id
AND mmt.organization_id = p_organization_id
AND mmt.inventory_item_id = p_item_id
AND nvl (mmt.cost_group_id, -999) = nvl (p_cost_group_id, -999)
AND mmt.transaction_type_id NOT IN
(73, 80, 25, 26, 28, 90, 91, 92,
55, 56, 57, 58, 87, 88, 89, 24)
AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
AND NVL(MMT.OWNING_TP_TYPE,2) = 2
AND mmt.transaction_date = (
SELECT transaction_date
FROM
(SELECT /*+ first_rows */ mt.transaction_date
FROM mtl_cst_actual_cost_details mcacd,
mtl_material_transactions mt
WHERE mt.transaction_id = mcacd.transaction_id
AND mt.transaction_date < p_cost_date + 1
AND mt.organization_id = p_organization_id
AND mt.inventory_item_id = p_item_id
AND mt.transaction_type_id NOT IN
(73, 80, 25, 26, 28, 90, 91, 92, 55, 56,
57, 58, 87, 88, 89, 24)
AND nvl (mt.cost_group_id,-999) = nvl (p_cost_group_id,
-999)
ORDER BY mt.transaction_date DESC)
WHERE rownum = 1);
SELECT SUM(macd.new_cost)
INTO l_item_cost
FROM mtl_cst_actual_cost_details macd
WHERE macd.transaction_id = l_trx_id
AND macd.organization_id = p_organization_id; /* Bug 3661478 - add filter on organization_id*/
select_cursor NUMBER:=0;
select mp.organization_id
from mtl_parameters mp
where
mp.process_enabled_flag <> 'Y' AND
exists
(select 'there are transactions'
from mtl_material_transactions mmt
where mmt.organization_id = mp.organization_id
and mmt.transaction_date between l_from_date and l_to_date)
or exists
(select 'there are transactions'
from wip_transactions wt
where wt.organization_id = mp.organization_id
and wt.transaction_date between l_from_date and l_to_date);
select mp.organization_id from
mtl_parameters mp,
(select distinct organization_id
from mtl_material_transactions
where transaction_date >= l_from_date
UNION
select distinct organization_id
from wip_transactions
where transaction_date >= l_from_date
UNION
select distinct organization_id
from mtl_onhand_quantities) mtl
where mp.organization_id = mtl.organization_id and
mp.process_enabled_flag <> 'Y';
SELECT mp.organization_id
from mtl_parameters mp
WHERE organization_id IN (606);
SELECT mp.organization_id
from mtl_parameters mp
WHERE organization_id IN (606);
SELECT max(last_push_inv_txn_date) l_date
FROM opi_ids_push_date_log
GROUP BY organization_id
UNION
SELECT max(last_push_wip_txn_date) l_date
FROM opi_ids_push_date_log
GROUP BY organization_id
ORDER BY 1;
SELECT Trunc(period_start_date) start_date,
Trunc(schedule_close_date) end_date
FROM org_acct_periods
WHERE organization_id = p_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 sysdate into l_print_date from dual;
select sum(1)
into select_cursor
from opi_ids_push_date_log
where rownum < 2;
select mp.organization_id into org_id
from mtl_parameters mp
where organization_code = p_org_code
and mp.process_enabled_flag <> 'Y';
if (select_cursor > 1) then
OPEN c_inv_org;
if (select_cursor > 1) then
FETCH c_inv_org into org_id;
SELECT trunc (min (trx_date)) -- must drop time stamp timestamp.
INTO l_edw_start_date_org
FROM opi_ids_push_log
WHERE organization_id = org_id;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
DELETE FROM opi_ids_push_log
WHERE trx_date BETWEEN inv_from_date AND to_date
AND trx_date <> l_edw_start_date_org
AND organization_id = org_id;
select sysdate into l_print_date from dual;
SELECT period_start_date
FROM org_acct_periods
WHERE organization_id = org_id
AND period_start_date <= p_from_date
AND schedule_close_date >= p_from_date;
SELECT MIN(period_start_date)
FROM org_acct_periods
WHERE organization_id = org_id
AND period_start_date BETWEEN p_from_date AND p_to_date;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
select sysdate into l_print_date from dual;
insert into opi_ids_push_date_log
(organization_id,
last_push_date,
last_push_inv_txn_id,
last_push_inv_txn_date,
last_push_wip_txn_id,
last_push_wip_txn_date,
creation_date,
last_update_date)
values
(org_id,
sysdate,
inv_trx_id,
trunc (inv_trx_date),
wip_trx_id,
trunc (wip_trx_date),
sysdate,
sysdate);
select sysdate into l_print_date from dual;
SELECT trunc(mmt.TRANSACTION_DATE),
mmt.ORGANIZATION_ID,
mmt.INVENTORY_ITEM_ID,
mmt.COST_GROUP_ID,
mmt.REVISION,
mmt.SUBINVENTORY_CODE,
mmt.LOCATOR_ID,
sum(mmt.PRIMARY_QUANTITY)
FROM MTL_MATERIAL_TRANSACTIONS mmt,
MTL_SYSTEM_ITEMS msi
WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
AND mmt.ORGANIZATION_ID=Org_id
AND msi.LOT_CONTROL_CODE = 1
AND mmt.transaction_date >= p_from_date
AND mmt.transaction_date <= p_to_date
AND mmt.transaction_type_id NOT IN (73, 80, 25, 26, 28, 90, 91, 92, 55, 56, 57, 58, 87, 88, 89, 24)
AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
AND NVL(MMT.OWNING_TP_TYPE,2) = 2
AND NVL(mmt.logical_transaction, 2) <> 1 /*11.5.10 changes*/
GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,
mmt.COST_GROUP_ID,mmt.REVISION,mmt.SUBINVENTORY_CODE,mmt.locator_id
ORDER BY trunc(mmt.TRANSACTION_DATE); -- added by rjin
SELECT trunc(mmt.TRANSACTION_DATE),
mmt.ORGANIZATION_ID,
mmt.INVENTORY_ITEM_ID,
mmt.COST_GROUP_ID,
mmt.REVISION,
mtln.LOT_NUMBER,
mmt.SUBINVENTORY_CODE,
mmt.LOCATOR_ID,
sum(mtln.PRIMARY_QUANTITY)
FROM MTL_MATERIAL_TRANSACTIONS mmt,
MTL_SYSTEM_ITEMS msi,
MTL_TRANSACTION_LOT_NUMBERS mtln
WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
AND mmt.ORGANIZATION_ID=Org_id
AND msi.LOT_CONTROL_CODE = 2
AND mmt.transaction_date >= p_from_date
AND mmt.transaction_date <= p_to_date
AND mmt.transaction_id = mtln.transaction_id
AND mmt.transaction_type_id NOT IN (73, 80, 25, 26, 28, 90, 91, 92,
55, 56, 57, 58, 87, 88, 89, 24)
AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
AND NVL(MMT.OWNING_TP_TYPE,2) = 2
AND NVL(mmt.logical_transaction, 2) <> 1 /*11.5.10 changes*/
GROUP BY trunc(mmt.TRANSACTION_DATE),mmt.ORGANIZATION_ID,mmt.INVENTORY_ITEM_ID,
mmt.COST_GROUP_ID,mmt.REVISION,mtln.lot_number,mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID
ORDER BY trunc(mmt.TRANSACTION_DATE);
SELECT trunc (min (trx_date)) -- must drop time stamp timestamp.
INTO l_edw_start_date_org
FROM opi_ids_push_log
WHERE organization_id = org_id;
SELECT INVENTORY_ITEM_STATUS_CODE,
ITEM_TYPE,
PRIMARY_UOM_CODE
INTO l_item_status,
l_item_type,
l_base_uom
FROM mtl_system_items
WHERE inventory_item_id=l_item_id
AND organization_id = l_organization_id;
SELECT INVENTORY_ITEM_STATUS_CODE,
ITEM_TYPE,
PRIMARY_UOM_CODE
INTO l_item_status,
l_item_type,
l_base_uom
FROM mtl_system_items
WHERE inventory_item_id=l_item_id
AND organization_id = l_organization_id;
SELECT trunc(MAX(trx_date))
FROM OPI_IDS_PUSH_LOG
WHERE ORGANIZATION_ID = p_organization_id
AND INVENTORY_ITEM_ID = p_item_id
AND nvl(COST_GROUP_ID,-999) = nvl(p_cost_group_id,-999)
AND nvl(REVISION,-999) = nvl(p_revision,-999)
AND nvl(LOT_NUMBER,-999) = nvl(p_lot_number,-999)
AND nvl(SUBINVENTORY_CODE,-999)= nvl(p_subinventory,-999)
AND nvl(project_locator_id, nvl(LOCATOR_ID,-999)) = nvl(p_locator,-999) -- Suhasini Added project_locator_id,Forward port from 11.5.9.3
AND trx_date < p_trx_date; -- added by rjin
SELECT primary_cost_method
INTO cost_method
FROM mtl_parameters
WHERE Organization_id=p_organization_id;
SELECT nvl(sum(nvl(beg_onh_qty, 0)),0),
nvl(sum(nvl(beg_onh_val_b, 0)),0)
-- if nothing is found, then there
-- must never have been an
-- inception qty
INTO l_last_end_qty, l_last_end_val
FROM opi_ids_push_log
WHERE IDS_KEY = l_ids_key;
SELECT Nvl(end_onh_val_b,0), Nvl(end_onh_qty,0)
INTO l_last_end_val, l_last_end_qty
FROM OPI_IDS_PUSH_LOG
WHERE IDS_KEY = l_ids_key;
SELECT asset_inventory
INTO asset_sub
FROM mtl_secondary_inventories sub
WHERE sub.SECONDARY_INVENTORY_NAME=p_subinventory
AND sub.organization_id = p_organization_id;
SELECT inventory_asset_flag
INTO non_expense_item
FROM mtl_system_items
WHERE organization_id = p_organization_id
AND inventory_item_id = p_item_id;
OPIMPXIN.Insert_update_push_log(
p_trx_date => p_trx_date ,
p_organization_id => p_organization_id,
p_item_id => p_item_id,
p_cost_group_id => p_cost_group_id,
p_revision => p_revision,
p_lot_number => p_lot_number,
p_subinventory => p_subinventory,
p_locator => p_locator,
p_item_status => p_item_status,
p_item_type => p_item_type,
p_base_uom => p_base_uom,
p_col_name1 => 'beg_onh_qty',
p_total1 => l_start_qty,
p_col_name2 => 'beg_onh_val_b',
p_total2 => l_start_val,
p_col_name3 => 'end_onh_qty',
p_total3 => l_end_qty,
p_col_name4 => 'end_onh_val_b',
p_total4 => l_end_val,
p_col_name5 => 'avg_onh_val_b',
p_total5 => l_avg_val,
p_col_name6 => 'avg_onh_qty',
p_total6 => l_avg_qty,
selector => 2,
success => l_status);
edw_log.put_line('Error in Insert_update_push_log');
select min(trx_date)
into l_purge_from_date
from opi_ids_push_log ipl
where push_flag = 0 -- already pushed
and period_flag is null -- not start or end period rows
and organization_id = i_org_id;
select max(last_push_inv_txn_date),max(last_push_wip_txn_date)
into l_last_push_inv_date,
l_last_push_wip_date
from opi_ids_push_date_log
where organization_id = i_org_id;
select max(oap.schedule_close_date)
into l_purge_to_date
from org_acct_periods oap
where oap.organization_id = i_org_id
and oap.period_close_date is not null
and oap.schedule_close_date < l_last_push_date;
delete from opi_ids_push_log ipl
where ipl.organization_id = i_org_id
and ipl.trx_date between l_purge_from_date
and l_purge_to_date
and ipl.push_flag = 0
and ipl.period_flag is null;
|| ' delete rowcount ' || SQL%rowcount );
SELECT mmt.INVENTORY_ITEM_ID,
mmt.COST_GROUP_ID,
mmt.REVISION,
decode(msi.LOT_CONTROL_CODE,2,nvl(mtln.LOT_NUMBER,'-99'),NULL) LOT_NUMBER, --bug 4561628 Forward ported
mmt.SUBINVENTORY_CODE,
mmt.LOCATOR_ID
FROM MTL_MATERIAL_TRANSACTIONS mmt,
MTL_TRANSACTION_LOT_NUMBERS mtln,
mtl_system_items msi
WHERE mmt.ORGANIZATION_ID=Org_id
AND mmt.transaction_date >= Trunc(p_from_date)
AND mmt.transaction_id = mtln.transaction_id (+)
and msi.organization_id = mmt.organization_id
and msi.inventory_item_id = mmt.inventory_item_id
AND mmt.transaction_type_id NOT IN (73, 80, 25, 26, 28, 90, 91, 92,
55, 56, 57, 58, 87, 88, 89, 24)
AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
AND NVL(MMT.OWNING_TP_TYPE,2) = 2
AND NVL(mmt.logical_transaction, 2) <> 1 /*11.5.10 changes*/
GROUP BY mmt.INVENTORY_ITEM_ID, mmt.COST_GROUP_ID,mmt.REVISION,mtln.lot_number,msi.LOT_CONTROL_CODE,
mmt.SUBINVENTORY_CODE,mmt.LOCATOR_ID
UNION
SELECT INVENTORY_ITEM_ID,
COST_GROUP_ID,
REVISION,
LOT_NUMBER,
SUBINVENTORY_CODE,
LOCATOR_ID
FROM mtl_onhand_quantities
WHERE ORGANIZATION_ID=Org_id
GROUP BY INVENTORY_ITEM_ID,COST_GROUP_ID,REVISION,LOT_NUMBER,SUBINVENTORY_CODE,locator_id;
SELECT primary_cost_method
INTO cost_method
FROM mtl_parameters
WHERE Organization_id=Org_id;
SELECT sum(TRANSACTION_QUANTITY)
INTO current_onhand_qty
FROM mtl_onhand_quantities
WHERE INVENTORY_ITEM_ID = l_item_id
AND ORGANIZATION_ID = Org_id
AND SUBINVENTORY_CODE = l_subinventory
AND nvl(REVISION,-999) = nvl(l_revision,-999)
AND nvl(LOCATOR_ID,-999) = nvl(l_locator,-999)
AND nvl(LOT_NUMBER,-999) = nvl(l_lot_number,-999)
AND Nvl(cost_group_id,-999) = Nvl(l_cost_group_id,-999);
SELECT sum(primary_quantity)
INTO net_transacted_quantity
FROM mtl_material_transactions
WHERE INVENTORY_ITEM_ID = l_item_id
AND ORGANIZATION_ID = Org_id
AND Nvl(subinventory_code,-999) = Nvl(l_subinventory,-999)
AND nvl(REVISION,-999) = nvl(l_revision,-999)
AND nvl(LOCATOR_ID,-999) = nvl(l_locator,-999)
AND Nvl(cost_group_id,-999) = Nvl(l_cost_group_id,-999)
AND TRANSACTION_DATE >= Trunc(p_from_date+1)
AND transaction_type_id NOT IN (73, 80, 25, 26, 28, 90, 91, 92, 55, 56, 57, 58, 87, 88, 89, 24)
AND organization_id = NVL(owning_organization_id, organization_id)
AND NVL(OWNING_TP_TYPE,2) = 2
AND NVL(logical_transaction, 2) <> 1; /*11.5.10 changes*/
SELECT sum(primary_quantity)
INTO from_date_transacted_quantity
FROM mtl_material_transactions
WHERE INVENTORY_ITEM_ID = l_item_id
AND ORGANIZATION_ID = Org_id
AND Nvl(subinventory_code,-999) = Nvl(l_subinventory,-999)
AND nvl(REVISION,-999) = nvl(l_revision,-999)
AND nvl(LOCATOR_ID,-999) = nvl(l_locator,-999)
AND Nvl(cost_group_id,-999) = Nvl(l_cost_group_id,-999)
AND TRANSACTION_DATE >= Trunc(p_from_date)
AND transaction_date < Trunc(p_from_date+1)
AND transaction_type_id NOT IN (73, 80, 25, 26, 28, 90, 91, 92, 55, 56, 57, 58, 87, 88, 89, 24)
AND organization_id = NVL(owning_organization_id, organization_id)
AND NVL(OWNING_TP_TYPE,2) = 2
AND NVL(logical_transaction, 2) <> 1; /*11.5.10 changes*/
SELECT sum(mtln.primary_quantity)
INTO net_transacted_quantity
FROM mtl_material_transactions mmt,
MTL_TRANSACTION_LOT_NUMBERS mtln
WHERE mmt.INVENTORY_ITEM_ID = l_item_id
AND mmt.ORGANIZATION_ID = Org_id
AND Nvl(mmt.subinventory_code,-999) = Nvl(l_subinventory,-999)
AND nvl(REVISION,-999) = nvl(l_revision,-999)
AND nvl(LOCATOR_ID,-999) = nvl(l_locator,-999)
AND nvl(mtln.LOT_NUMBER,-999) = nvl(l_lot_number,-999)
AND Nvl(mmt.cost_group_id,-999) = Nvl(l_cost_group_id,-999)
AND mmt.TRANSACTION_DATE >= Trunc( p_from_date +1)
AND mmt.transaction_id = mtln.transaction_id
AND mmt.transaction_type_id NOT IN (73, 80, 25, 26, 28, 90, 91, 92, 55, 56, 57, 58, 87, 88, 89, 24)
AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
AND NVL(MMT.OWNING_TP_TYPE,2) = 2
AND NVL(mmt.logical_transaction, 2) <> 1; /*11.5.10 changes*/
SELECT sum(mtln.primary_quantity)
INTO from_date_transacted_quantity
FROM mtl_material_transactions mmt,
MTL_TRANSACTION_LOT_NUMBERS mtln
WHERE mmt.INVENTORY_ITEM_ID = l_item_id
AND mmt.ORGANIZATION_ID = Org_id
AND Nvl(mmt.subinventory_code,-999) = Nvl(l_subinventory,-999)
AND nvl(REVISION,-999) = nvl(l_revision,-999)
AND nvl(LOCATOR_ID,-999) = nvl(l_locator,-999)
AND nvl(mtln.LOT_NUMBER,-999) = nvl(l_lot_number,-999)
AND Nvl(mmt.cost_group_id,-999) = Nvl(l_cost_group_id,-999)
AND mmt.TRANSACTION_DATE >= Trunc(p_from_date)
AND mmt.transaction_date < Trunc( p_from_date +1)
AND mmt.transaction_id = mtln.transaction_id
AND mmt.transaction_type_id NOT IN (73, 80, 25, 26, 28, 90, 91, 92, 55, 56, 57, 58, 87, 88, 89, 24)
AND MMT.organization_id = NVL(MMT.owning_organization_id,MMT.organization_id)
AND NVL(MMT.OWNING_TP_TYPE,2) = 2
AND NVL(mmt.logical_transaction, 2) <> 1; /*11.5.10 changes*/
SELECT asset_inventory
INTO asset_sub
FROM mtl_secondary_inventories sub
WHERE sub.SECONDARY_INVENTORY_NAME=l_subinventory
AND sub.organization_id = Org_id;
SELECT inventory_asset_flag
INTO non_expense_item
FROM mtl_system_items
WHERE organization_id = org_id
AND inventory_item_id = l_item_id;
SELECT MAX(macd.transaction_id)
INTO l_trx_id
FROM mtl_cst_actual_cost_details macd,
mtl_material_transactions mmt
WHERE mmt.transaction_id = macd.transaction_id
AND mmt.ORGANIZATION_ID = Org_id
AND mmt.INVENTORY_ITEM_ID = l_item_id
AND Nvl(mmt.cost_group_id,-999) = Nvl(l_cost_group_id, -999)
AND mmt.transaction_type_id NOT IN
(73, 80, 25, 26, 28, 90, 91, 92, 55, 56, 57, 58,
87, 88, 89, 24)
AND MMT.organization_id = NVL(MMT.owning_organization_id,
MMT.organization_id)
AND NVL(MMT.OWNING_TP_TYPE,2) = 2
AND mmt.transaction_date =
(SELECT transaction_date
FROM
(SELECT /*+ first_rows */ mt.transaction_date
FROM mtl_cst_actual_cost_details mcacd,
mtl_material_transactions mt
WHERE mt.transaction_id = mcacd.transaction_id
AND mt.TRANSACTION_DATE < Trunc( p_from_date+1)
AND mt.ORGANIZATION_ID = Org_id
AND mt.INVENTORY_ITEM_ID = l_item_id
AND mt.transaction_type_id NOT IN
(73, 80, 25, 26, 28, 90, 91, 92, 55, 56,
57, 58, 87, 88, 89, 24)
AND Nvl(mt.cost_group_id,-999) =
Nvl(l_cost_group_id,-999)
ORDER BY mt.transaction_date DESC)
WHERE rownum = 1);
SELECT sum(macd.NEW_cost)
INTO item_cost
FROM mtl_cst_actual_cost_details macd
WHERE macd.transaction_id=l_trx_id
AND macd.organization_id = Org_id; /* Bug 3661478 - add filter on organization_id*/
SELECT INVENTORY_ITEM_STATUS_CODE,
ITEM_TYPE,
PRIMARY_UOM_CODE
INTO l_item_status,
l_item_type,
l_base_uom
FROM mtl_system_items
WHERE inventory_item_id=l_item_id
AND organization_id = Org_id;
OPIMPXIN.Insert_update_push_log(
p_trx_date => p_from_date ,
p_organization_id => Org_id,
p_item_id => l_item_id,
p_cost_group_id => l_cost_group_id,
p_revision => l_revision,
p_lot_number => l_lot_number,
p_subinventory => l_subinventory,
p_locator => l_locator,
p_item_status => l_item_status,
p_item_type => l_item_type,
p_base_uom => l_base_uom,
p_col_name1 => 'beg_onh_qty',
p_total1 => beg_onh_qty,
p_col_name2 => 'beg_onh_val_b',
p_total2 => beg_onh_val,
p_col_name3 => 'end_onh_qty',
p_total3 => end_onh_qty, -- Setting end_onh_qty same as beg_onh_qty. It will
p_col_name4 => 'end_onh_val_b', -- get changed if there are activities on that day.
p_total4 => end_onh_val,
p_col_name5 => 'avg_onh_val_b',
p_total5 => avg_onh_val,
p_col_name6 => 'avg_onh_qty',
p_total6 => avg_onh_qty,
selector => 2,
success => l_status);
EDW_LOG.PUT_LINE('Inserted '||to_char(p_from_date)||','||to_char(Org_id)||','||to_char(l_item_id)||','||to_char(l_cost_group_id)||','||l_revision||',');
edw_log.put_line('Error in Insert_update_push_log');
SELECT trunc(mmt.TRANSACTION_DATE),
mmt.organization_id,
mmt.INVENTORY_ITEM_ID,
mmt.COST_GROUP_ID,
mmt.PRIMARY_QUANTITY,
mmt.transaction_action_id,
mmt.transfer_organization_id,
mmt.actual_cost,
msi.inventory_item_status_code,
msi.item_type,
msi.primary_uom_code
FROM MTL_MATERIAL_TRANSACTIONS mmt,
mtl_system_items msi
WHERE ( mmt.ORGANIZATION_ID=Org_id or mmt.transfer_organization_id =Org_id)
AND mmt.organization_id=msi.organization_id
AND mmt.inventory_item_id=msi.inventory_item_id
AND mmt.transaction_action_id in (12,21)
AND mmt.transaction_date >= p_from_date
AND mmt.transaction_date <= p_to_date
ORDER BY trunc(mmt.TRANSACTION_DATE),mmt.inventory_item_id;
SELECT 1
FROM opi_ids_push_log
WHERE IDS_KEY=l_pk;
SELECT trunc(MAX(trx_date))
FROM OPI_IDS_PUSH_LOG
WHERE ORGANIZATION_ID = l_organization_id
AND INVENTORY_ITEM_ID = l_item_id
AND nvl(COST_GROUP_ID,-999) = nvl(l_cost_group_id,-999)
AND REVISION is null
AND LOT_NUMBER is null
AND SUBINVENTORY_CODE is null
AND LOCATOR_ID is null
AND trx_date < l_trx_date;
select fob_point
into l_fob_pt
from mtl_interorg_parameters
where from_organization_id = l_org_id
and to_organization_id = xfr_org_id;
select fob_point
into l_fob_pt
from mtl_interorg_parameters
where from_organization_id = xfr_org_id
and to_organization_id = l_org_id;
UPDATE opi_ids_push_log
SET end_int_qty = (end_int_qty + total_qty)
WHERE IDS_KEY = l_pk;
SELECT Nvl(end_int_val_b,0), Nvl(end_int_qty,0)
INTO l_last_end_val, l_last_end_qty
FROM OPI_IDS_PUSH_LOG
WHERE IDS_KEY = l_max_trx_date||'-'||l_item_id||'-'||l_organization_id||'-'||l_cost_group_id||'-'||'-'||'-'||'-';
select process_enabled_flag into l_process_org
from mtl_parameters where organization_id = l_organization_id;
INSERT INTO opi_ids_push_log
(ids_key,
cost_group_id,
organization_id,
inventory_item_id,
trx_date,
push_flag,
beg_int_qty, beg_int_val_b,
end_int_qty, end_int_val_b,
avg_int_qty, avg_int_val_b,
base_uom,
item_status,
item_type )
VALUES
(l_pk,
l_cost_group_id,
l_organization_id,
l_item_id,
l_trx_date,
1,
l_beg_int_qty,
l_beg_int_val_b,
l_end_int_qty,
l_end_int_val_b,
l_avg_int_qty,
l_avg_int_val_b,
l_base_uom,
l_item_status,
l_item_type );