[Home] [Help]
46: --* *
47: --* 10-Nov-2003 PJS If we encounter a consumption transaction (eg OMSO) but the lot has not *
48: --* been costed, report an error and skip the transaction. *
49: --* Also fixed the cursors that retrieve uncosted transactions as a new *
50: --* column has been added to the gmf_lot_costed_items table (co_code) and *
51: --* this created a semi-cartesian join. *
52: --* *
53: --* 17-Nov-2003 PJS Reinstate the code that handles acquisition costs as it somehow became *
54: --* lost. *
63: --* support update or alterations to it. Also, Oracle db itself doesn't *
64: --* support updates to nested tables - we need to drop and recreate it. *
65: --* *
66: --* 21-Nov-2003 umoogala ic_item_mst.lot_costed_flag does not exist. To work around this loaded *
67: --* all lot costed item from gmf_lot_costed_items_flag into lc_item_tab. If *
68: --* item exists in this table then lot_costed_flag is set to 1 otherwise to 0*
69: --* Called new function is_item_lot_costed to do this in material_cursor. *
70: --* *
71: --* 30-Nov-2003 umoogala Now passing co_code to main routines ROLLUP_LOT_COSTS and removed *
372: -- 9) inv_convert only when different
373: -- 10) inv_tran_cursor, no mtl_system_items in lot cost adjustment query section.
374: -- 11) Removing Le_id join for process_org_gt table
375: -- 12) suggesting following
376: --CREATE INDEX gmf_lot_costed_items_gt_N1 ON gmf_lot_costed_items_gt
377: -- (inventory_item_id,organization_id) ;
378: -- CREATE INDEX GMF_PROCESS_ORGN_GT_N1 ON GMF_PROCESS_ORGANIZATIONS_GT
379: -- (organization_id) ;
380: -- 13) org colleciton
958: --* This cursor would ideally be nested inside the above cursor, but SQL syntax does not allow *
959: --* an order by clause in a subquery, and we need to have the transactions ordered by date *
960: --* *
961: --* umoogala: ic_item_mst.lot_costed_flag does not exist. To work around this loaded all lot *
962: --* costed item from gmf_lot_costed_items_flag into lc_item_tab. If item exists in this table *
963: --* then lot_costed_flag is set to 1 otherwise to 0. *
964: --*
965: --* Girish - Bug 4094132 Modified this materials cursor NOT IN clause to add lot_id and reverse_id
966: --* Since we are filtering the query using "NOT IN" the product yielded into multiple lots
1661:
1662: CURSOR lot_costed_items
1663: IS
1664: SELECT g.inventory_item_id,g.organization_id
1665: FROM gmf_lot_costed_items_gt g
1666: ORDER BY organization_id,inventory_item_id
1667: ;
1668:
1669:
1743: SELECT distinct inventory_item_id into l_inv_id FROM
1744: (
1745: SELECT
1746: msi.inventory_item_id
1747: FROM gmf_lot_costed_items lci,
1748: mtl_system_items_b msi
1749: WHERE lci.legal_entity_id = l_le_id
1750: AND lci.delete_mark = 0 /* ANTHIYAG Bug#5279681 06-Jun-2006 */
1751: AND msi.lot_control_code = 2
1757: AND msi.organization_id = p_orgn_id
1758: UNION
1759: SELECT msi.inventory_item_id
1760: FROM mtl_item_categories mic,
1761: gmf_lot_costed_items g,
1762: mtl_system_items_b msi
1763: WHERE g.cost_category_id = mic.category_id
1764: AND g.legal_entity_id = l_le_id
1765: AND g.delete_mark = 0
1822: IF l_debug_level >= l_debug_level_medium THEN
1823: fnd_file.put_line(fnd_file.log,'Entered Procedure: '||procedure_name);
1824: END IF;
1825:
1826: DELETE FROM GMF_LOT_COSTED_ITEMS_GT ;
1827:
1828: -- Bug 8730374 Modified the insert to load items assigned by category as well
1829:
1830: INSERT
1827:
1828: -- Bug 8730374 Modified the insert to load items assigned by category as well
1829:
1830: INSERT
1831: INTO GMF_LOT_COSTED_ITEMS_GT
1832: (
1833: organization_id,
1834: inventory_item_id,
1835: primary_uom_code
1837: SELECT
1838: msi.organization_id,
1839: msi.inventory_item_id,
1840: msi.primary_uom_code
1841: FROM gmf_lot_costed_items lci,
1842: mtl_system_items_b msi,
1843: gmf_process_organizations_gt gpo
1844: WHERE lci.legal_entity_id = l_le_id
1845: AND lci.delete_mark = 0 /* ANTHIYAG Bug#5279681 06-Jun-2006 */
1854: mic.organization_id, /*ANTHIYAG Bug#5279681 06-Jun-2006 */
1855: mic.inventory_item_id, /*ANTHIYAG Bug#5279681 06-Jun-2006 */
1856: i.primary_uom_code
1857: FROM mtl_item_categories mic,
1858: gmf_lot_costed_items g,
1859: mtl_system_items_b i,
1860: gmf_process_organizations_gt gpo
1861: WHERE g.cost_category_id = mic.category_id
1862: AND g.legal_entity_id = l_le_id
3572: select NVL(mtln.primary_quantity, mmt.primary_quantity), -- B9131983 used NVL
3573: lcig.primary_uom_code
3574: from mtl_material_transactions mmt,
3575: mtl_transaction_lot_numbers mtln,
3576: gmf_lot_costed_items_gt lcig
3577: where mmt.transaction_id = transaction_row.transfer_transaction_id
3578: AND mmt.transaction_id = mtln.transaction_id
3579: AND mmt.inventory_item_id = lcig.inventory_item_id
3580: AND mmt.organization_id = lcig.organization_id;
8115: -- Open a cursor that retrieves only the item/lot/sublot specified
8116: OPEN Cur_lc_header FOR
8117: SELECT glc.header_id, glc.rowid
8118: FROM gmf_lot_costs glc,
8119: gmf_lot_costed_items_gt gpo
8120: WHERE glc.organization_id = gpo.organization_id
8121: AND glc.inventory_item_id = gpo.inventory_item_id
8122: AND glc.cost_type_id = l_cost_type_id
8123: AND gpo.organization_id IN (select organization_id from gmf_process_organizations_gt
8891: l_org_tab(cur_rec.organization_id) := cur_rec.organization_code;
8892: END LOOP;
8893:
8894: INSERT
8895: INTO GMF_LOT_COSTED_ITEMS_GT
8896: (
8897: organization_id,
8898: inventory_item_id,
8899: primary_uom_code
8901: SELECT
8902: msi.organization_id,
8903: msi.inventory_item_id,
8904: msi.primary_uom_code
8905: FROM gmf_lot_costed_items lci,
8906: mtl_system_items_b msi,
8907: gmf_process_organizations_gt gpo
8908: WHERE lci.legal_entity_id = l_le_id
8909: AND lci.delete_mark = 0 /* ANTHIYAG Bug#5279681 06-Jun-2006 */
8926: )
8927: );
8928:
8929: INSERT
8930: INTO GMF_LOT_COSTED_ITEMS_GT
8931: (
8932: organization_id,
8933: inventory_item_id,
8934: primary_uom_code
8939: i.primary_uom_code
8940: FROM mtl_item_categories mic,
8941: mtl_default_category_sets mdc,
8942: mtl_category_sets mcs,
8943: gmf_lot_costed_items g,
8944: mtl_system_items_b i,
8945: gmf_process_organizations_gt gpo
8946: WHERE g.cost_category_id = mic.category_id
8947: AND g.legal_entity_id = l_le_id
8969: )
8970: )
8971: AND not exists ( -- Exclude item/orgs from 1st insert
8972: select 1
8973: from GMF_LOT_COSTED_ITEMS_GT lcig
8974: where lcig.inventory_item_id = i.inventory_item_id
8975: and lcig.organization_id = i.organization_id
8976: );
8977:
8976: );
8977:
8978: /* Bug 8730374 added this union to load one item if assigned by cost category */
8979: INSERT
8980: INTO GMF_LOT_COSTED_ITEMS_GT
8981: (
8982: organization_id,
8983: inventory_item_id,
8984: primary_uom_code
8989: i.primary_uom_code
8990: FROM mtl_item_categories mic,
8991: mtl_default_category_sets mdc,
8992: mtl_category_sets mcs,
8993: gmf_lot_costed_items g,
8994: mtl_system_items_b i,
8995: gmf_process_organizations_gt gpo
8996: WHERE g.cost_category_id = mic.category_id
8997: AND g.legal_entity_id = l_le_id
9008: AND g.cost_type_id = l_cost_type_id
9009: AND mic.inventory_item_id = p_item_id
9010: and not exists ( -- Exclude item/orgs from 1st, 2nd inserts
9011: select 1
9012: from GMF_LOT_COSTED_ITEMS_GT lcig
9013: where lcig.inventory_item_id = mic.inventory_item_id
9014: and lcig.organization_id = mic.organization_id
9015: );
9016:
10221: -- There are two possibilities for the costing. A lot might be manufactured
10222: -- or bought in. Occasionally a lot might be bought in and later added to by a
10223: -- production batch or vice versa. No matter how the lot is created and/or replenished
10224: -- the sole criterion to decide whether to cost an item's lots using lot costing will
10225: -- be an entry for the item, or its cost_class, in gmf_lot_costed_items
10226:
10227: -- In the case of a lot that is an output of a production batch, we need
10228: -- to explode it and find out what lots went into its manufacture.
10229:
10278: -- rows that are returned. There are several cursors depending on how the
10279: -- user has launched the rollup:
10280: --
10281: -- A full rollup will retrieve all transactions for all items/cost classes
10282: -- listed in the gmf_lot_costed_items table. Note that this retrieval will not
10283: -- possess a date range.
10284: --
10285: -- An item-specific rollup will only retrieve transactions for the item specified.
10286: -- If the item has a date range in the gmf_lot_costed_items table this will be
10282: -- listed in the gmf_lot_costed_items table. Note that this retrieval will not
10283: -- possess a date range.
10284: --
10285: -- An item-specific rollup will only retrieve transactions for the item specified.
10286: -- If the item has a date range in the gmf_lot_costed_items table this will be
10287: -- used. This option also allows a user to specify an optional lot and sublot.
10288: --
10289: -- A cost class-specific rollup will only retrieved transactions for items in the
10290: -- cost class specified. If the cost class possesses a date range in gmf_lot_costed_items
10286: -- If the item has a date range in the gmf_lot_costed_items table this will be
10287: -- used. This option also allows a user to specify an optional lot and sublot.
10288: --
10289: -- A cost class-specific rollup will only retrieved transactions for items in the
10290: -- cost class specified. If the cost class possesses a date range in gmf_lot_costed_items
10291: -- this will be used.
10292:
10293:
10294: --
10297: -- Also, added lot_ctl = 1 condition
10298: -- to where clause
10299: --
10300:
10301: -- Since the cursors below were written, a new column (co_code) has been added to the gmf_lot_costed_items table
10302: -- with the result that it is now possible to have rows differing only in this column.
10303:
10304: -- Cursors now possess a third branch that is 'union all'd' into the result set to retrieve the lot cost
10305: -- adjustments. These are disguised as transactions of type LADJ.
10348:
10349: fnd_file.put_line(fnd_File.LOG,'Reading uncosted transactions: '|| to_char(sysdate,'DD-MON-YYYY HH24:MI:SS') );
10350:
10351: -- Perf B14616815 Inv_tran_cursor query is now split into two one for specific lot and another for all lots.
10352: -- One item all lots would still be part of first query. Works as gmf_lot_costed_items_gt would have one item.
10353:
10354: IF l_lot_no IS NULL THEN
10355:
10356: OPEN inv_tran_cursor FOR
10426: mtl_transaction_lot_numbers mtln,
10427: (Select pair_type, transaction_id1, transaction_id2 From gme_transaction_pairs where pair_type = 1) gtp, -- PK Bug 9069363
10428: gmf_process_organizations_gt gpo,
10429: gme_batch_header gbh,
10430: gmf_lot_costed_items_gt lcig
10431: WHERE
10432: gpo.organization_id = mmt.organization_id
10433: -- AND gpo.legal_entity_id = l_le_id -- B 8687115
10434: -- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
10482: 0 as routing_id
10483: FROM mtl_material_transactions mmt,
10484: mtl_transaction_lot_numbers mtln,
10485: gmf_process_organizations_gt gpo,
10486: gmf_lot_costed_items_gt lcig
10487: WHERE
10488: gpo.organization_id = mmt.organization_id
10489: -- AND gpo.legal_entity_id = l_le_id -- B 8687115
10490: -- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
10536: 0 as routing_id
10537: FROM mtl_material_transactions mmt,
10538: mtl_transaction_lot_numbers mtln,
10539: gmf_process_organizations_gt gpo,
10540: gmf_lot_costed_items_gt lcig
10541: WHERE
10542: gpo.organization_id = mmt.owning_organization_id
10543: -- AND gpo.legal_entity_id = l_le_id -- B 8687115
10544: -- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
10589: 0 as routing_id
10590: FROM mtl_material_transactions mmt,
10591: mtl_transaction_lot_numbers mtln,
10592: gmf_process_organizations_gt gpo,
10593: gmf_lot_costed_items_gt lcig
10594: WHERE
10595: gpo.organization_id = mmt.organization_id
10596: -- AND gpo.legal_entity_id = l_le_id -- B 8687115
10597: -- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
10640: -9 as oc3,
10641: 0 as routing_id
10642: FROM gmf_lot_cost_adjustments glca,
10643: -- mtl_system_items_b iimb,
10644: gmf_lot_costed_items_gt glci
10645: WHERE glca.applied_ind = 'N'
10646: -- AND glca.adjustment_date >= NVL(l_trans_start_date, glca.adjustment_date) changed for performance as below B14616815
10647: AND glca.adjustment_date >= l_trans_start_date
10648: AND glca.legal_entity_id = l_le_id
10695: rcv_transactions rt,
10696: mtl_material_transactions mmt, -- Bug 13906192
10697: mtl_transaction_lot_numbers mtln, -- Bug 13906192
10698: gmf_process_organizations_gt gpo,
10699: gmf_lot_costed_items_gt glci
10700: WHERE glat.transaction_date >= l_trans_start_date
10701: -- glat.transaction_date >= NVL(l_trans_start_date,glat.transaction_date) performance changed as above B14616815
10702: -- AND glat.legal_entity_id = l_le_id -- bug 12866252
10703: AND rt.transaction_id = mmt.rcv_transaction_id -- Bug 13906192
10801: mtl_transaction_lot_numbers mtln,
10802: (Select pair_type, transaction_id1, transaction_id2 From gme_transaction_pairs where pair_type = 1) gtp, -- PK Bug 9069363
10803: gmf_process_organizations_gt gpo,
10804: gme_batch_header gbh,
10805: gmf_lot_costed_items_gt lcig
10806: WHERE
10807: gpo.organization_id = mmt.organization_id
10808: -- AND gpo.legal_entity_id = l_le_id -- B 8687115
10809: -- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
10858: 0 as routing_id
10859: FROM mtl_material_transactions mmt,
10860: mtl_transaction_lot_numbers mtln,
10861: gmf_process_organizations_gt gpo,
10862: gmf_lot_costed_items_gt lcig
10863: WHERE
10864: gpo.organization_id = mmt.organization_id
10865: -- AND gpo.legal_entity_id = l_le_id -- B 8687115
10866: -- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
10913: 0 as routing_id
10914: FROM mtl_material_transactions mmt,
10915: mtl_transaction_lot_numbers mtln,
10916: gmf_process_organizations_gt gpo,
10917: gmf_lot_costed_items_gt lcig
10918: WHERE
10919: gpo.organization_id = mmt.owning_organization_id
10920: -- AND gpo.legal_entity_id = l_le_id -- B 8687115
10921: -- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
10967: 0 as routing_id
10968: FROM mtl_material_transactions mmt,
10969: mtl_transaction_lot_numbers mtln,
10970: gmf_process_organizations_gt gpo,
10971: gmf_lot_costed_items_gt lcig
10972: WHERE
10973: gpo.organization_id = mmt.organization_id
10974: -- AND gpo.legal_entity_id = l_le_id -- B 8687115
10975: -- AND mmt.transaction_date >= NVL(l_trans_start_date, mmt.transaction_date) changed for performance as below B14616815
11019: -9 as oc3,
11020: 0 as routing_id
11021: FROM gmf_lot_cost_adjustments glca,
11022: -- mtl_system_items_b iimb,
11023: gmf_lot_costed_items_gt glci
11024: WHERE glca.applied_ind = 'N'
11025: -- AND glca.adjustment_date >= NVL(l_trans_start_date, glca.adjustment_date) changed for performance as below B14616815
11026: AND glca.adjustment_date >= l_trans_start_date
11027: AND glca.legal_entity_id = l_le_id
11075: rcv_transactions rt,
11076: mtl_material_transactions mmt, -- Bug 13906192
11077: mtl_transaction_lot_numbers mtln, -- Bug 13906192
11078: gmf_process_organizations_gt gpo,
11079: gmf_lot_costed_items_gt glci
11080: WHERE glat.transaction_date >= l_trans_start_date
11081: -- glat.transaction_date >= NVL(l_trans_start_date,glat.transaction_date) performance changed as above B14616815
11082: -- AND glat.legal_entity_id = l_le_id -- bug 12866252
11083: AND rt.transaction_id = mmt.rcv_transaction_id -- Bug 13906192