[Home] [Help]
90: If there is more than one SCU on the same day, use the latest
91: cost on that day.
92: 2. If csc is has no data prior to the start date, but has data
93: after the start date,
94: ----Then use the cost in the mmt (mtl_material_transactions)
95: because the cic (cst_item_costs) no longer has the
96: historical cost.
97: ----Else use the cost in the CIC
98:
149: WHERE csc.organization_id = p_org_id
150: AND csc.inventory_item_id = p_item_id;
151:
152: -- Cursor to get the historical item cost from the
153: -- mtl_material_transactions (mmt). If there are no cost updates prior
154: -- to the date, but one after the given date, then the historical cost
155: -- cannot be obtained from the csc or the cic. We need to go back to the
156: -- mmt.
157: -- digupta 07/10/02 -- filtered out certain transactions that do not
159: CURSOR mmt_historical_cost_cur (p_org_id NUMBER, p_item_id NUMBER,
160: p_cost_date DATE)
161: IS
162: SELECT actual_cost
163: FROM mtl_material_transactions
164: WHERE transaction_id =
165: (SELECT max(transaction_id)
166: FROM mtl_material_transactions
167: WHERE inventory_item_id = p_item_id
162: SELECT actual_cost
163: FROM mtl_material_transactions
164: WHERE transaction_id =
165: (SELECT max(transaction_id)
166: FROM mtl_material_transactions
167: WHERE inventory_item_id = p_item_id
168: AND organization_id = p_org_id
169: AND actual_cost IS NOT NULL
170: AND transaction_type_id NOT IN
173: AND organization_id = NVL(owning_organization_id, organization_id)
174: AND NVL(OWNING_TP_TYPE,2) = 2
175: AND transaction_date =
176: (SELECT max(transaction_date)
177: FROM mtl_material_transactions
178: WHERE inventory_item_id = p_item_id
179: AND organization_id = p_org_id
180: AND (transaction_date) <
181: trunc(p_cost_date) + 1
287: -- ltong 01/20/2003. Filtered out consigned inventory.
288: SELECT max (macd.transaction_id)
289: INTO l_trx_id
290: FROM mtl_cst_actual_cost_details macd,
291: mtl_material_transactions mmt
292: WHERE mmt.transaction_id = macd.transaction_id
293: AND mmt.organization_id = p_organization_id
294: AND mmt.inventory_item_id = p_item_id
295: AND nvl (mmt.cost_group_id, -999) = nvl (p_cost_group_id, -999)
302: SELECT transaction_date
303: FROM
304: (SELECT /*+ first_rows */ mt.transaction_date
305: FROM mtl_cst_actual_cost_details mcacd,
306: mtl_material_transactions mt
307: WHERE mt.transaction_id = mcacd.transaction_id
308: AND mt.transaction_date < p_cost_date + 1
309: AND mt.organization_id = p_organization_id
310: AND mt.inventory_item_id = p_item_id
385: where
386: mp.process_enabled_flag <> 'Y' AND
387: exists
388: (select 'there are transactions'
389: from mtl_material_transactions mmt
390: where mmt.organization_id = mp.organization_id
391: and mmt.transaction_date between l_from_date and l_to_date)
392: or exists
393: (select 'there are transactions'
404: cursor c_inv_org_first_push is
405: select mp.organization_id from
406: mtl_parameters mp,
407: (select distinct organization_id
408: from mtl_material_transactions
409: where transaction_date >= l_from_date
410: UNION
411: select distinct organization_id
412: from wip_transactions
1238: mmt.REVISION,
1239: mmt.SUBINVENTORY_CODE,
1240: mmt.LOCATOR_ID,
1241: sum(mmt.PRIMARY_QUANTITY)
1242: FROM MTL_MATERIAL_TRANSACTIONS mmt,
1243: MTL_SYSTEM_ITEMS msi
1244: WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1245: AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1246: AND mmt.ORGANIZATION_ID=Org_id
1268: mtln.LOT_NUMBER,
1269: mmt.SUBINVENTORY_CODE,
1270: mmt.LOCATOR_ID,
1271: sum(mtln.PRIMARY_QUANTITY)
1272: FROM MTL_MATERIAL_TRANSACTIONS mmt,
1273: MTL_SYSTEM_ITEMS msi,
1274: MTL_TRANSACTION_LOT_NUMBERS mtln
1275: WHERE mmt.INVENTORY_ITEM_ID=msi.INVENTORY_ITEM_ID
1276: AND mmt.ORGANIZATION_ID=msi.ORGANIZATION_ID
1990: mmt.REVISION,
1991: decode(msi.LOT_CONTROL_CODE,2,nvl(mtln.LOT_NUMBER,'-99'),NULL) LOT_NUMBER, --bug 4561628 Forward ported
1992: mmt.SUBINVENTORY_CODE,
1993: mmt.LOCATOR_ID
1994: FROM MTL_MATERIAL_TRANSACTIONS mmt,
1995: MTL_TRANSACTION_LOT_NUMBERS mtln,
1996: mtl_system_items msi
1997: WHERE mmt.ORGANIZATION_ID=Org_id
1998: AND mmt.transaction_date >= Trunc(p_from_date)
2068: -- affect inventory quantity or balance.
2069: -- mochawla 10/29/2003. filtered out logical transactions from net quantity
2070: SELECT sum(primary_quantity)
2071: INTO net_transacted_quantity
2072: FROM mtl_material_transactions
2073: WHERE INVENTORY_ITEM_ID = l_item_id
2074: AND ORGANIZATION_ID = Org_id
2075: AND Nvl(subinventory_code,-999) = Nvl(l_subinventory,-999)
2076: AND nvl(REVISION,-999) = nvl(l_revision,-999)
2086: -- affect inventory quantity or balance.
2087: -- mochawla 10/29/2003. filtered out logical transactions from transacted quantity
2088: SELECT sum(primary_quantity)
2089: INTO from_date_transacted_quantity
2090: FROM mtl_material_transactions
2091: WHERE INVENTORY_ITEM_ID = l_item_id
2092: AND ORGANIZATION_ID = Org_id
2093: AND Nvl(subinventory_code,-999) = Nvl(l_subinventory,-999)
2094: AND nvl(REVISION,-999) = nvl(l_revision,-999)
2107: -- ltong 01/20/2003. Filtered out consigned inventory.
2108: -- mochawla 10/29/2003. filtered out logical transactions from net quantity
2109: SELECT sum(mtln.primary_quantity)
2110: INTO net_transacted_quantity
2111: FROM mtl_material_transactions mmt,
2112: MTL_TRANSACTION_LOT_NUMBERS mtln
2113: WHERE mmt.INVENTORY_ITEM_ID = l_item_id
2114: AND mmt.ORGANIZATION_ID = Org_id
2115: AND Nvl(mmt.subinventory_code,-999) = Nvl(l_subinventory,-999)
2129: -- ltong 01/20/2003. Filtered out consigned inventory.
2130: -- mochawla 10/29/2003. filtered out logical transactions from quantity
2131: SELECT sum(mtln.primary_quantity)
2132: INTO from_date_transacted_quantity
2133: FROM mtl_material_transactions mmt,
2134: MTL_TRANSACTION_LOT_NUMBERS mtln
2135: WHERE mmt.INVENTORY_ITEM_ID = l_item_id
2136: AND mmt.ORGANIZATION_ID = Org_id
2137: AND Nvl(mmt.subinventory_code,-999) = Nvl(l_subinventory,-999)
2214: -- ltong 01/20/2003. Filtered out consigned inventory.
2215: SELECT MAX(macd.transaction_id)
2216: INTO l_trx_id
2217: FROM mtl_cst_actual_cost_details macd,
2218: mtl_material_transactions mmt
2219: WHERE mmt.transaction_id = macd.transaction_id
2220: AND mmt.ORGANIZATION_ID = Org_id
2221: AND mmt.INVENTORY_ITEM_ID = l_item_id
2222: AND Nvl(mmt.cost_group_id,-999) = Nvl(l_cost_group_id, -999)
2230: (SELECT transaction_date
2231: FROM
2232: (SELECT /*+ first_rows */ mt.transaction_date
2233: FROM mtl_cst_actual_cost_details mcacd,
2234: mtl_material_transactions mt
2235: WHERE mt.transaction_id = mcacd.transaction_id
2236: AND mt.TRANSACTION_DATE < Trunc( p_from_date+1)
2237: AND mt.ORGANIZATION_ID = Org_id
2238: AND mt.INVENTORY_ITEM_ID = l_item_id
2382: mmt.actual_cost,
2383: msi.inventory_item_status_code,
2384: msi.item_type,
2385: msi.primary_uom_code
2386: FROM MTL_MATERIAL_TRANSACTIONS mmt,
2387: mtl_system_items msi
2388: WHERE ( mmt.ORGANIZATION_ID=Org_id or mmt.transfer_organization_id =Org_id)
2389: AND mmt.organization_id=msi.organization_id
2390: AND mmt.inventory_item_id=msi.inventory_item_id