[Home] [Help]
636: /* Bug 5703541 Added update stmt and loop for mtl_lot_conv_audit */
637: IF (g_debug <= gme_debug.g_log_statement) THEN
638: gme_debug.put_line('Update mtl_lot_conv_audit');
639: END IF;
640: FOR get_rec IN (SELECT old_batch_id, new_batch_id FROM gme_batch_mapping_mig
641: WHERE old_batch_id IN (SELECT DISTINCT(batch_id) FROM mtl_lot_conv_audit)) LOOP
642: UPDATE mtl_lot_conv_audit
643: SET batch_id = get_rec.new_batch_id
644: WHERE batch_id = get_rec.old_batch_id;
1488:
1489: PROCEDURE create_batch_mapping(p_batch_header_mig IN gme_batch_header_mig%ROWTYPE,
1490: p_batch_header IN gme_batch_header%ROWTYPE) IS
1491: BEGIN
1492: INSERT INTO gme_batch_mapping_mig(plant_code,
1493: old_batch_id,
1494: old_batch_no,
1495: organization_id,
1496: new_batch_id,
1514: PROCEDURE create_phantom_links IS
1515: CURSOR Cur_get_phantoms IS
1516: SELECT d.material_detail_id new_ing_line_id, d.phantom_id old_phantom_id, d.inventory_item_id,
1517: m.plant_code, m.new_batch_no
1518: FROM gme_material_details d, gme_batch_mapping_mig m
1519: WHERE d.batch_id = m.new_batch_id
1520: AND d.line_type = -1
1521: AND d.phantom_id > 0
1522: AND d.phantom_id NOT IN (SELECT new_batch_id FROM gme_batch_mapping_mig);
1518: FROM gme_material_details d, gme_batch_mapping_mig m
1519: WHERE d.batch_id = m.new_batch_id
1520: AND d.line_type = -1
1521: AND d.phantom_id > 0
1522: AND d.phantom_id NOT IN (SELECT new_batch_id FROM gme_batch_mapping_mig);
1523:
1524: CURSOR Cur_new_phant_batch(v_batch_id NUMBER) IS
1525: SELECT new_batch_id
1526: FROM gme_batch_mapping_mig
1522: AND d.phantom_id NOT IN (SELECT new_batch_id FROM gme_batch_mapping_mig);
1523:
1524: CURSOR Cur_new_phant_batch(v_batch_id NUMBER) IS
1525: SELECT new_batch_id
1526: FROM gme_batch_mapping_mig
1527: WHERE old_batch_id = v_batch_id;
1528:
1529: CURSOR Cur_new_phant_prod(v_batch_id NUMBER, v_inventory_item_id NUMBER) IS
1530: SELECT material_detail_id
1623:
1624: PROCEDURE release_batches IS
1625: CURSOR Cur_wip_batches IS
1626: SELECT m.*, o.actual_start_date
1627: FROM gme_batch_header_mig o, gme_batch_mapping_mig m
1628: WHERE o.batch_status = gme_common_pvt.g_batch_wip
1629: AND m.old_batch_id = o.batch_id
1630: AND o.parentline_id IS NULL
1631: AND m.new_batch_id NOT IN (SELECT batch_id
1648: -- Bug 13706812 - Use old batch id to make use of index.
1649: -- Bug - Get the mismatching wip plan qty.
1650: CURSOR Cur_get_wip_plan(v_old_batch_id NUMBER, v_new_batch_id NUMBER) IS
1651: SELECT d.material_detail_id, dm.wip_plan_qty
1652: FROM gme_material_details d, gme_material_details_mig dm, gme_batch_mapping_mig hmap
1653: WHERE d.line_type = dm.line_type
1654: AND d.line_no = dm.line_no
1655: AND d.item_id = dm.item_id
1656: AND d.batch_id = hmap.new_batch_id
1665: WHERE EXISTS (SELECT batch_id
1666: FROM gme_material_details
1667: WHERE batch_id = v_batch_id
1668: AND phantom_type > 0
1669: AND phantom_id NOT IN (SELECT new_batch_id FROM gme_batch_mapping_mig));
1670:
1671: l_date DATE;
1672: l_min_start_date DATE;
1673: l_temp NUMBER;
3864:
3865: PROCEDURE close_steps IS
3866: CURSOR Cur_get_steps IS
3867: SELECT s.batchstep_id, m.step_close_date, bm.new_batch_no, s.batchstep_no, bm.plant_code, bm.old_batch_id
3868: FROM gme_batch_steps_mig m, gme_batch_steps s, gme_batch_mapping_mig bm
3869: WHERE m.step_status = gme_common_pvt.g_step_closed
3870: AND bm.old_batch_id = m.batch_id
3871: AND s.batch_id = bm.new_batch_id
3872: AND s.batchstep_no = m.batchstep_no
3965: PROCEDURE insert_lab_lots IS
3966: l_api_name VARCHAR2(30) := 'insert_lab_lots';
3967: CURSOR Cur_lab_lots IS
3968: SELECT l.*, m.organization_id, m.new_batch_no, m.new_batch_id, m.plant_code, l.rowid
3969: FROM gme_batch_mapping_mig m, gme_lab_batch_lots l
3970: WHERE m.old_batch_id = l.batch_id
3971: AND NVL(attribute27, 'A') <> 'M';
3972: CURSOR Cur_item_dtl(v_organization_id NUMBER, v_inventory_item_id NUMBER) IS
3973: SELECT i.segment1, i.lot_control_code