[Home] [Help]
516: /* Bug 5703541 Added update stmt and loop for mtl_lot_conv_audit */
517: IF (g_debug <= gme_debug.g_log_statement) THEN
518: gme_debug.put_line('Update mtl_lot_conv_audit');
519: END IF;
520: FOR get_rec IN (SELECT old_batch_id, new_batch_id FROM gme_batch_mapping_mig
521: WHERE old_batch_id IN (SELECT DISTINCT(batch_id) FROM mtl_lot_conv_audit)) LOOP
522: UPDATE mtl_lot_conv_audit
523: SET batch_id = get_rec.new_batch_id
524: WHERE batch_id = get_rec.old_batch_id;
1357:
1358: PROCEDURE create_batch_mapping(p_batch_header_mig IN gme_batch_header_mig%ROWTYPE,
1359: p_batch_header IN gme_batch_header%ROWTYPE) IS
1360: BEGIN
1361: INSERT INTO gme_batch_mapping_mig(plant_code,
1362: old_batch_id,
1363: old_batch_no,
1364: organization_id,
1365: new_batch_id,
1383: PROCEDURE create_phantom_links IS
1384: CURSOR Cur_get_phantoms IS
1385: SELECT d.material_detail_id new_ing_line_id, d.phantom_id old_phantom_id, d.inventory_item_id,
1386: m.plant_code, m.new_batch_no
1387: FROM gme_material_details d, gme_batch_mapping_mig m
1388: WHERE d.batch_id = m.new_batch_id
1389: AND d.line_type = -1
1390: AND d.phantom_id > 0
1391: AND d.phantom_id NOT IN (SELECT new_batch_id FROM gme_batch_mapping_mig);
1387: FROM gme_material_details d, gme_batch_mapping_mig m
1388: WHERE d.batch_id = m.new_batch_id
1389: AND d.line_type = -1
1390: AND d.phantom_id > 0
1391: AND d.phantom_id NOT IN (SELECT new_batch_id FROM gme_batch_mapping_mig);
1392: CURSOR Cur_new_phant_batch(v_batch_id NUMBER) IS
1393: SELECT new_batch_id
1394: FROM gme_batch_mapping_mig
1395: WHERE old_batch_id = v_batch_id;
1390: AND d.phantom_id > 0
1391: AND d.phantom_id NOT IN (SELECT new_batch_id FROM gme_batch_mapping_mig);
1392: CURSOR Cur_new_phant_batch(v_batch_id NUMBER) IS
1393: SELECT new_batch_id
1394: FROM gme_batch_mapping_mig
1395: WHERE old_batch_id = v_batch_id;
1396: CURSOR Cur_new_phant_prod(v_batch_id NUMBER, v_inventory_item_id NUMBER) IS
1397: SELECT material_detail_id
1398: FROM gme_material_details
1489:
1490: PROCEDURE release_batches IS
1491: CURSOR Cur_wip_batches IS
1492: SELECT m.*, o.actual_start_date
1493: FROM gme_batch_header_mig o, gme_batch_mapping_mig m
1494: WHERE o.batch_status = gme_common_pvt.g_batch_wip
1495: AND m.old_batch_id = o.batch_id
1496: AND o.parentline_id IS NULL
1497: AND m.new_batch_id NOT IN (SELECT batch_id
1515: WHERE EXISTS (SELECT batch_id
1516: FROM gme_material_details
1517: WHERE batch_id = v_batch_id
1518: AND phantom_type > 0
1519: AND phantom_id NOT IN (SELECT new_batch_id FROM gme_batch_mapping_mig));
1520: l_date DATE;
1521: l_temp NUMBER;
1522: l_msg_cnt NUMBER;
1523: l_current_org_id NUMBER;
3314:
3315: PROCEDURE close_steps IS
3316: CURSOR Cur_get_steps IS
3317: SELECT s.batchstep_id, m.step_close_date, bm.new_batch_no, s.batchstep_no, bm.plant_code, bm.old_batch_id
3318: FROM gme_batch_steps_mig m, gme_batch_steps s, gme_batch_mapping_mig bm
3319: WHERE m.step_status = gme_common_pvt.g_step_closed
3320: AND bm.old_batch_id = m.batch_id
3321: AND s.batch_id = bm.new_batch_id
3322: AND s.batchstep_no = m.batchstep_no
3415: PROCEDURE insert_lab_lots IS
3416: l_api_name VARCHAR2(30) := 'insert_lab_lots';
3417: CURSOR Cur_lab_lots IS
3418: SELECT l.*, m.organization_id, m.new_batch_no, m.new_batch_id, m.plant_code, l.rowid
3419: FROM gme_batch_mapping_mig m, gme_lab_batch_lots l
3420: WHERE m.old_batch_id = l.batch_id
3421: AND NVL(attribute27, 'A') <> 'M';
3422: CURSOR Cur_item_dtl(v_organization_id NUMBER, v_inventory_item_id NUMBER) IS
3423: SELECT i.segment1, i.lot_control_code