[Home] [Help]
313: --* HALUTHRA BUG 7249505 In case of return to vendor process the transaction as an adjustment
314: --* 21-MAY-2009 Bug 6165255 and BUG 8330088 HARI LUTHRA
315: --* Modified the cursor unassociated_ings_cursor and unassociated_prds_cursor so as to include ingrediant transactions
316: --* which are non lot controlled and also to take the primary qty and primary UOM of item from mtl_material_transactions
317: --* and mtl_system_items_b instead of transaction_qty and transaction_uom to avoid the dual_uom issues.
318: --* 27-MAY-2009 Hari Luthra Bug 5473138/8533290
319: --* Modified item_cost_cursor , item_cost_detail_cursor to avoid issues with cost warehouse assosciation
320: --* Also modified query which picks up all the transactions on the basis of the parameters fed while running Lot Cost Process
321: --* Made changes in this query to handle phantom item issues.
369: -- 7) Load only lot costs from prior 60 days.
370: -- C4 Changes.
371: -- 8) Burden indicater.
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) ;
517:
518: -- Alternate Period cost caching
519: TYPE Alter_cost_typ IS RECORD
520: (
521: inventory_item_id mtl_system_items.Inventory_item_id%type ,
522: organization_id mtl_parameters.organization_id%TYPE ,
523: period_id gmf_period_statuses.period_id%TYPE ,
524: cost_details SYSTEM.gmf_cost_tab ,
525: total_cost NUMBER
996: FROM DUAL) AS SYSTEM.gmf_cost_tab
997: ), -- Bug 7317270,
998: gmd.cost_alloc
999: )
1000: FROM mtl_system_items_b iimb,
1001: mtl_material_transactions mmt,
1002: gme_batch_step_items gbsi,
1003: gme_material_details gmd,
1004: mtl_transaction_lot_numbers mtln,
1093: 0,
1094: gmd.plan_qty,
1095: gmd.actual_qty,
1096: gmd.dtl_um, gmd.cost_alloc
1097: FROM mtl_system_items_b iimb,
1098: mtl_material_transactions mmt,
1099: gme_material_details gmd,
1100: mtl_transaction_lot_numbers mtln,
1101: mtl_parameters mp,
1171: FROM DUAL) AS SYSTEM.gmf_cost_tab
1172: ), -- Bug 7317270
1173: gmd.cost_alloc
1174: )
1175: FROM mtl_system_items_b iimb,
1176: mtl_material_transactions mmt,
1177: gme_material_details gmd,
1178: mtl_transaction_lot_numbers mtln,
1179: hr_organization_information hoi
1216: FROM DUAL) AS SYSTEM.gmf_cost_tab
1217: ), -- Bug 7317270
1218: gme.cost_alloc
1219: )
1220: FROM mtl_system_items_b iimb,
1221: mtl_material_transactions mmt,
1222: gme_material_details gme,
1223: mtl_transaction_lot_numbers mtln,
1224: gme_transaction_pairs gtp
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
1752: AND lci.inventory_item_id = msi.inventory_item_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
1766: AND msi.lot_control_code = 2
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 */
1846: AND gpo.organization_id = msi.organization_id
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
1863: AND g.delete_mark = 0
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 */
8910: AND gpo.organization_id = msi.organization_id
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
8948: AND mdc.functional_area_id = 19
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
8998: AND mdc.functional_area_id = 19
10639: 0 as oc2,
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
11018: 0 as oc2,
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
11767:
11768: IF NOT l_itemno_tab.exists(p_item_id) THEN
11769:
11770: SELECT SUBSTR(msi.concatenated_segments,1,50) INTO p_item_no
11771: FROM mtl_system_items_kfv msi
11772: WHERE msi.inventory_item_id = p_item_id
11773: AND msi.organization_id = p_orgn_id ;
11774:
11775: l_itemno_tab(p_item_id) := p_item_no;