[Home] [Help]
96: FROM gme_resource_txns_mig
97: WHERE line_id = v_batchstep_rsrc_id;
98: BEGIN
99: IF (g_debug IS NOT NULL) THEN
100: gme_debug.log_initialize('Migration');
101: END IF;
102: IF (g_debug <= gme_debug.g_log_procedure) THEN
103: gme_debug.put_line('Start procedure '||l_api_name);
104: END IF;
98: BEGIN
99: IF (g_debug IS NOT NULL) THEN
100: gme_debug.log_initialize('Migration');
101: END IF;
102: IF (g_debug <= gme_debug.g_log_procedure) THEN
103: gme_debug.put_line('Start procedure '||l_api_name);
104: END IF;
105: g_migration_run_id := gma_migration.gma_migration_start ('GME', 'RECREATE_OPEN_BATCHES');
106: IF (g_debug <= gme_debug.g_log_statement) THEN
99: IF (g_debug IS NOT NULL) THEN
100: gme_debug.log_initialize('Migration');
101: END IF;
102: IF (g_debug <= gme_debug.g_log_procedure) THEN
103: gme_debug.put_line('Start procedure '||l_api_name);
104: END IF;
105: g_migration_run_id := gma_migration.gma_migration_start ('GME', 'RECREATE_OPEN_BATCHES');
106: IF (g_debug <= gme_debug.g_log_statement) THEN
107: gme_debug.put_line('Migration RUN ID = '||g_migration_run_id);
102: IF (g_debug <= gme_debug.g_log_procedure) THEN
103: gme_debug.put_line('Start procedure '||l_api_name);
104: END IF;
105: g_migration_run_id := gma_migration.gma_migration_start ('GME', 'RECREATE_OPEN_BATCHES');
106: IF (g_debug <= gme_debug.g_log_statement) THEN
107: gme_debug.put_line('Migration RUN ID = '||g_migration_run_id);
108: END IF;
109: FOR get_batches IN Cur_get_batches LOOP
110: BEGIN
103: gme_debug.put_line('Start procedure '||l_api_name);
104: END IF;
105: g_migration_run_id := gma_migration.gma_migration_start ('GME', 'RECREATE_OPEN_BATCHES');
106: IF (g_debug <= gme_debug.g_log_statement) THEN
107: gme_debug.put_line('Migration RUN ID = '||g_migration_run_id);
108: END IF;
109: FOR get_batches IN Cur_get_batches LOOP
110: BEGIN
111: IF (g_debug <= gme_debug.g_log_statement) THEN
107: gme_debug.put_line('Migration RUN ID = '||g_migration_run_id);
108: END IF;
109: FOR get_batches IN Cur_get_batches LOOP
110: BEGIN
111: IF (g_debug <= gme_debug.g_log_statement) THEN
112: gme_debug.put_line('Current batch is '||get_batches.plant_code||'-'||get_batches.batch_no);
113: END IF;
114: gme_common_pvt.set_timestamp;
115: IF (l_current_org_id <> get_batches.organization_id) THEN
108: END IF;
109: FOR get_batches IN Cur_get_batches LOOP
110: BEGIN
111: IF (g_debug <= gme_debug.g_log_statement) THEN
112: gme_debug.put_line('Current batch is '||get_batches.plant_code||'-'||get_batches.batch_no);
113: END IF;
114: gme_common_pvt.set_timestamp;
115: IF (l_current_org_id <> get_batches.organization_id) THEN
116: IF (g_debug <= gme_debug.g_log_statement) THEN
112: gme_debug.put_line('Current batch is '||get_batches.plant_code||'-'||get_batches.batch_no);
113: END IF;
114: gme_common_pvt.set_timestamp;
115: IF (l_current_org_id <> get_batches.organization_id) THEN
116: IF (g_debug <= gme_debug.g_log_statement) THEN
117: gme_debug.put_line('Doing setup for org = '||get_batches.organization_id);
118: END IF;
119: IF NOT (gme_common_pvt.setup(p_org_id => get_batches.organization_id)) THEN
120: RAISE setup_failed;
113: END IF;
114: gme_common_pvt.set_timestamp;
115: IF (l_current_org_id <> get_batches.organization_id) THEN
116: IF (g_debug <= gme_debug.g_log_statement) THEN
117: gme_debug.put_line('Doing setup for org = '||get_batches.organization_id);
118: END IF;
119: IF NOT (gme_common_pvt.setup(p_org_id => get_batches.organization_id)) THEN
120: RAISE setup_failed;
121: END IF;
133: SET wip_entity_name = wip_entity_name||'-M'
134: WHERE entity_type = DECODE(get_batches.batch_type, 10, gme_common_pvt.g_wip_entity_type_fpo, gme_common_pvt.g_wip_entity_type_batch)
135: AND organization_id = get_batches.organization_id
136: AND wip_entity_name = l_prefix||get_batches.batch_no;
137: IF (g_debug <= gme_debug.g_log_statement) THEN
138: gme_debug.put_line('Start batch header');
139: END IF;
140: /* Process batch header record */
141: build_batch_hdr(p_batch_header_mig => get_batches,
134: WHERE entity_type = DECODE(get_batches.batch_type, 10, gme_common_pvt.g_wip_entity_type_fpo, gme_common_pvt.g_wip_entity_type_batch)
135: AND organization_id = get_batches.organization_id
136: AND wip_entity_name = l_prefix||get_batches.batch_no;
137: IF (g_debug <= gme_debug.g_log_statement) THEN
138: gme_debug.put_line('Start batch header');
139: END IF;
140: /* Process batch header record */
141: build_batch_hdr(p_batch_header_mig => get_batches,
142: x_batch_header => l_batch_header);
147: l_batch_header.terminated_ind := 0;
148: l_batch_header.enhanced_pi_ind := 'N';
149: l_batch_header.batch_id := l_wip_entity_id;
150: l_batch_header.wip_whse_code := NULL;
151: IF (g_debug <= gme_debug.g_log_statement) THEN
152: gme_debug.put_line('Before wip entities');
153: END IF;
154: INSERT INTO wip_entities
155: (wip_entity_id, organization_id
148: l_batch_header.enhanced_pi_ind := 'N';
149: l_batch_header.batch_id := l_wip_entity_id;
150: l_batch_header.wip_whse_code := NULL;
151: IF (g_debug <= gme_debug.g_log_statement) THEN
152: gme_debug.put_line('Before wip entities');
153: END IF;
154: INSERT INTO wip_entities
155: (wip_entity_id, organization_id
156: ,last_update_date, last_updated_by
165: ,DECODE (l_batch_header.batch_type
166: ,0, gme_common_pvt.g_wip_entity_type_batch
167: ,gme_common_pvt.g_wip_entity_type_fpo)
168: ,mtl_gen_object_id_s.NEXTVAL);
169: IF (g_debug <= gme_debug.g_log_statement) THEN
170: gme_debug.put_line('After wip entities');
171: END IF;
172: l_batch_tbl(1) := l_batch_header;
173: FORALL a IN 1..l_batch_tbl.count
166: ,0, gme_common_pvt.g_wip_entity_type_batch
167: ,gme_common_pvt.g_wip_entity_type_fpo)
168: ,mtl_gen_object_id_s.NEXTVAL);
169: IF (g_debug <= gme_debug.g_log_statement) THEN
170: gme_debug.put_line('After wip entities');
171: END IF;
172: l_batch_tbl(1) := l_batch_header;
173: FORALL a IN 1..l_batch_tbl.count
174: INSERT INTO gme_batch_header VALUES l_batch_tbl(a);
171: END IF;
172: l_batch_tbl(1) := l_batch_header;
173: FORALL a IN 1..l_batch_tbl.count
174: INSERT INTO gme_batch_header VALUES l_batch_tbl(a);
175: IF (g_debug <= gme_debug.g_log_statement) THEN
176: gme_debug.put_line('After batch header');
177: END IF;
178: /* Process material details records */
179: OPEN Cur_get_materials(get_batches.batch_id);
172: l_batch_tbl(1) := l_batch_header;
173: FORALL a IN 1..l_batch_tbl.count
174: INSERT INTO gme_batch_header VALUES l_batch_tbl(a);
175: IF (g_debug <= gme_debug.g_log_statement) THEN
176: gme_debug.put_line('After batch header');
177: END IF;
178: /* Process material details records */
179: OPEN Cur_get_materials(get_batches.batch_id);
180: FETCH Cur_get_materials BULK COLLECT INTO l_mtl_dtl_mig_tbl;
178: /* Process material details records */
179: OPEN Cur_get_materials(get_batches.batch_id);
180: FETCH Cur_get_materials BULK COLLECT INTO l_mtl_dtl_mig_tbl;
181: CLOSE Cur_get_materials;
182: IF (g_debug <= gme_debug.g_log_statement) THEN
183: gme_debug.put_line('No. of materials = '||l_mtl_dtl_mig_tbl.count);
184: END IF;
185: build_mtl_dtl(p_mtl_dtl_mig => l_mtl_dtl_mig_tbl,
186: x_mtl_dtl => l_mtl_dtl_tbl);
179: OPEN Cur_get_materials(get_batches.batch_id);
180: FETCH Cur_get_materials BULK COLLECT INTO l_mtl_dtl_mig_tbl;
181: CLOSE Cur_get_materials;
182: IF (g_debug <= gme_debug.g_log_statement) THEN
183: gme_debug.put_line('No. of materials = '||l_mtl_dtl_mig_tbl.count);
184: END IF;
185: build_mtl_dtl(p_mtl_dtl_mig => l_mtl_dtl_mig_tbl,
186: x_mtl_dtl => l_mtl_dtl_tbl);
187: FOR i IN 1..l_mtl_dtl_tbl.count LOOP
231: END LOOP;
232:
233: FORALL a IN 1..l_mtl_dtl_tbl.count
234: INSERT INTO gme_material_details VALUES l_mtl_dtl_tbl(a);
235: IF (g_debug <= gme_debug.g_log_statement) THEN
236: gme_debug.put_line('After materials');
237: END IF;
238: /* Process batch step records */
239: l_steps_tbl.delete;
232:
233: FORALL a IN 1..l_mtl_dtl_tbl.count
234: INSERT INTO gme_material_details VALUES l_mtl_dtl_tbl(a);
235: IF (g_debug <= gme_debug.g_log_statement) THEN
236: gme_debug.put_line('After materials');
237: END IF;
238: /* Process batch step records */
239: l_steps_tbl.delete;
240: l_activities_tbl.delete;
243: l_rsrc_txns_tbl.delete;
244: OPEN Cur_get_steps(get_batches.batch_id);
245: FETCH Cur_get_steps BULK COLLECT INTO l_steps_mig_tbl;
246: CLOSE Cur_get_steps;
247: IF (g_debug <= gme_debug.g_log_statement) THEN
248: gme_debug.put_line('No.of steps = '||l_steps_mig_tbl.count);
249: END IF;
250: build_steps(p_steps_mig => l_steps_mig_tbl,
251: x_steps => l_steps_tbl);
244: OPEN Cur_get_steps(get_batches.batch_id);
245: FETCH Cur_get_steps BULK COLLECT INTO l_steps_mig_tbl;
246: CLOSE Cur_get_steps;
247: IF (g_debug <= gme_debug.g_log_statement) THEN
248: gme_debug.put_line('No.of steps = '||l_steps_mig_tbl.count);
249: END IF;
250: build_steps(p_steps_mig => l_steps_mig_tbl,
251: x_steps => l_steps_tbl);
252: FOR i IN 1..l_steps_tbl.count LOOP
264: CLOSE Cur_get_activities;
265: l_actv_count := l_activities_tbl.count;
266: build_activities(p_activities_mig => l_activities_mig_tbl,
267: x_activities => l_activities_tbl);
268: IF (g_debug <= gme_debug.g_log_statement) THEN
269: gme_debug.put_line('No. of activities = '||l_activities_mig_tbl.count);
270: END IF;
271: FOR j IN (l_actv_count + 1)..l_activities_tbl.count LOOP
272: l_old_bstep_activ_id := l_activities_tbl(j).batchstep_activity_id;
265: l_actv_count := l_activities_tbl.count;
266: build_activities(p_activities_mig => l_activities_mig_tbl,
267: x_activities => l_activities_tbl);
268: IF (g_debug <= gme_debug.g_log_statement) THEN
269: gme_debug.put_line('No. of activities = '||l_activities_mig_tbl.count);
270: END IF;
271: FOR j IN (l_actv_count + 1)..l_activities_tbl.count LOOP
272: l_old_bstep_activ_id := l_activities_tbl(j).batchstep_activity_id;
273: SELECT gme_batch_step_activity_s.NEXTVAL INTO l_activities_tbl(j).batchstep_activity_id FROM DUAL;
281: CLOSE Cur_get_resources;
282: l_rsrc_count := l_resources_tbl.count;
283: build_resources(p_resources_mig => l_resources_mig_tbl,
284: x_resources => l_resources_tbl);
285: IF (g_debug <= gme_debug.g_log_statement) THEN
286: gme_debug.put_line('No. of resources = '||l_resources_mig_tbl.count);
287: END IF;
288: FOR k IN (l_rsrc_count + 1)..l_resources_tbl.count LOOP
289: l_old_bstep_rsrc_id := l_resources_tbl(k).batchstep_resource_id;
282: l_rsrc_count := l_resources_tbl.count;
283: build_resources(p_resources_mig => l_resources_mig_tbl,
284: x_resources => l_resources_tbl);
285: IF (g_debug <= gme_debug.g_log_statement) THEN
286: gme_debug.put_line('No. of resources = '||l_resources_mig_tbl.count);
287: END IF;
288: FOR k IN (l_rsrc_count + 1)..l_resources_tbl.count LOOP
289: l_old_bstep_rsrc_id := l_resources_tbl(k).batchstep_resource_id;
290: SELECT gem5_batchstepline_id_s.NEXTVAL INTO l_resources_tbl(k).batchstep_resource_id FROM DUAL;
299: CLOSE Cur_get_process_params;
300: l_pprm_count := l_proc_param_tbl.count;
301: build_parameters(p_parameters_mig => l_proc_param_mig_tbl,
302: x_parameters => l_proc_param_tbl);
303: IF (g_debug <= gme_debug.g_log_statement) THEN
304: gme_debug.put_line('No. of process params = '||l_proc_param_mig_tbl.count);
305: END IF;
306: FOR m IN (l_pprm_count + 1)..l_proc_param_tbl.count LOOP
307: SELECT gme_process_parameters_id_s.NEXTVAL INTO l_proc_param_tbl(m).process_param_id FROM DUAL;
300: l_pprm_count := l_proc_param_tbl.count;
301: build_parameters(p_parameters_mig => l_proc_param_mig_tbl,
302: x_parameters => l_proc_param_tbl);
303: IF (g_debug <= gme_debug.g_log_statement) THEN
304: gme_debug.put_line('No. of process params = '||l_proc_param_mig_tbl.count);
305: END IF;
306: FOR m IN (l_pprm_count + 1)..l_proc_param_tbl.count LOOP
307: SELECT gme_process_parameters_id_s.NEXTVAL INTO l_proc_param_tbl(m).process_param_id FROM DUAL;
308: l_proc_param_tbl(m).batch_id := l_batch_header.batch_id;
315: CLOSE Cur_get_rsrc_txns;
316: l_rtxn_count := l_rsrc_txns_tbl.count;
317: build_rsrc_txns(p_rsrc_txns_mig => l_rsrc_txns_mig_tbl,
318: x_rsrc_txns => l_rsrc_txns_tbl);
319: IF (g_debug <= gme_debug.g_log_statement) THEN
320: gme_debug.put_line('No. of rsrc txns = '||l_rsrc_txns_mig_tbl.count);
321: END IF;
322: FOR n IN (l_rtxn_count + 1)..l_rsrc_txns_tbl.count LOOP
323: SELECT gem5_poc_trans_id_s.NEXTVAL INTO l_rsrc_txns_tbl(n).poc_trans_id FROM DUAL;
316: l_rtxn_count := l_rsrc_txns_tbl.count;
317: build_rsrc_txns(p_rsrc_txns_mig => l_rsrc_txns_mig_tbl,
318: x_rsrc_txns => l_rsrc_txns_tbl);
319: IF (g_debug <= gme_debug.g_log_statement) THEN
320: gme_debug.put_line('No. of rsrc txns = '||l_rsrc_txns_mig_tbl.count);
321: END IF;
322: FOR n IN (l_rtxn_count + 1)..l_rsrc_txns_tbl.count LOOP
323: SELECT gem5_poc_trans_id_s.NEXTVAL INTO l_rsrc_txns_tbl(n).poc_trans_id FROM DUAL;
324: l_rsrc_txns_tbl(n).orgn_code := NULL;
327: END LOOP; /* Resource Txns Loop */
328: END LOOP; /* Resources Loop */
329: END LOOP; /* Activities Loop */
330: END LOOP; /* Steps Loop */
331: IF (g_debug <= gme_debug.g_log_statement) THEN
332: gme_debug.put_line('After steps processing now inserting all step data');
333: END IF;
334: FORALL a IN 1..l_steps_tbl.count
335: INSERT INTO gme_batch_steps VALUES l_steps_tbl(a);
328: END LOOP; /* Resources Loop */
329: END LOOP; /* Activities Loop */
330: END LOOP; /* Steps Loop */
331: IF (g_debug <= gme_debug.g_log_statement) THEN
332: gme_debug.put_line('After steps processing now inserting all step data');
333: END IF;
334: FORALL a IN 1..l_steps_tbl.count
335: INSERT INTO gme_batch_steps VALUES l_steps_tbl(a);
336: IF (g_debug <= gme_debug.g_log_statement) THEN
332: gme_debug.put_line('After steps processing now inserting all step data');
333: END IF;
334: FORALL a IN 1..l_steps_tbl.count
335: INSERT INTO gme_batch_steps VALUES l_steps_tbl(a);
336: IF (g_debug <= gme_debug.g_log_statement) THEN
337: gme_debug.put_line('After insert steps');
338: END IF;
339: FORALL a IN 1..l_activities_tbl.count
340: INSERT INTO gme_batch_step_activities VALUES l_activities_tbl(a);
333: END IF;
334: FORALL a IN 1..l_steps_tbl.count
335: INSERT INTO gme_batch_steps VALUES l_steps_tbl(a);
336: IF (g_debug <= gme_debug.g_log_statement) THEN
337: gme_debug.put_line('After insert steps');
338: END IF;
339: FORALL a IN 1..l_activities_tbl.count
340: INSERT INTO gme_batch_step_activities VALUES l_activities_tbl(a);
341: IF (g_debug <= gme_debug.g_log_statement) THEN
337: gme_debug.put_line('After insert steps');
338: END IF;
339: FORALL a IN 1..l_activities_tbl.count
340: INSERT INTO gme_batch_step_activities VALUES l_activities_tbl(a);
341: IF (g_debug <= gme_debug.g_log_statement) THEN
342: gme_debug.put_line('After insert activities');
343: END IF;
344: FORALL a IN 1..l_resources_tbl.count
345: INSERT INTO gme_batch_step_resources VALUES l_resources_tbl(a);
338: END IF;
339: FORALL a IN 1..l_activities_tbl.count
340: INSERT INTO gme_batch_step_activities VALUES l_activities_tbl(a);
341: IF (g_debug <= gme_debug.g_log_statement) THEN
342: gme_debug.put_line('After insert activities');
343: END IF;
344: FORALL a IN 1..l_resources_tbl.count
345: INSERT INTO gme_batch_step_resources VALUES l_resources_tbl(a);
346: IF (g_debug <= gme_debug.g_log_statement) THEN
342: gme_debug.put_line('After insert activities');
343: END IF;
344: FORALL a IN 1..l_resources_tbl.count
345: INSERT INTO gme_batch_step_resources VALUES l_resources_tbl(a);
346: IF (g_debug <= gme_debug.g_log_statement) THEN
347: gme_debug.put_line('After insert resources');
348: END IF;
349: FORALL a IN 1..l_proc_param_tbl.count
350: INSERT INTO gme_process_parameters VALUES l_proc_param_tbl(a);
343: END IF;
344: FORALL a IN 1..l_resources_tbl.count
345: INSERT INTO gme_batch_step_resources VALUES l_resources_tbl(a);
346: IF (g_debug <= gme_debug.g_log_statement) THEN
347: gme_debug.put_line('After insert resources');
348: END IF;
349: FORALL a IN 1..l_proc_param_tbl.count
350: INSERT INTO gme_process_parameters VALUES l_proc_param_tbl(a);
351: IF (g_debug <= gme_debug.g_log_statement) THEN
347: gme_debug.put_line('After insert resources');
348: END IF;
349: FORALL a IN 1..l_proc_param_tbl.count
350: INSERT INTO gme_process_parameters VALUES l_proc_param_tbl(a);
351: IF (g_debug <= gme_debug.g_log_statement) THEN
352: gme_debug.put_line('After insert process params');
353: END IF;
354: FORALL a IN 1..l_rsrc_txns_tbl.count
355: INSERT INTO gme_resource_txns VALUES l_rsrc_txns_tbl(a);
348: END IF;
349: FORALL a IN 1..l_proc_param_tbl.count
350: INSERT INTO gme_process_parameters VALUES l_proc_param_tbl(a);
351: IF (g_debug <= gme_debug.g_log_statement) THEN
352: gme_debug.put_line('After insert process params');
353: END IF;
354: FORALL a IN 1..l_rsrc_txns_tbl.count
355: INSERT INTO gme_resource_txns VALUES l_rsrc_txns_tbl(a);
356: IF (g_debug <= gme_debug.g_log_statement) THEN
352: gme_debug.put_line('After insert process params');
353: END IF;
354: FORALL a IN 1..l_rsrc_txns_tbl.count
355: INSERT INTO gme_resource_txns VALUES l_rsrc_txns_tbl(a);
356: IF (g_debug <= gme_debug.g_log_statement) THEN
357: gme_debug.put_line('After insert rsrc txns');
358: END IF;
359: create_step_dependencies(p_old_batch_id => get_batches.batch_id,
360: p_new_batch_id => l_batch_header.batch_id);
353: END IF;
354: FORALL a IN 1..l_rsrc_txns_tbl.count
355: INSERT INTO gme_resource_txns VALUES l_rsrc_txns_tbl(a);
356: IF (g_debug <= gme_debug.g_log_statement) THEN
357: gme_debug.put_line('After insert rsrc txns');
358: END IF;
359: create_step_dependencies(p_old_batch_id => get_batches.batch_id,
360: p_new_batch_id => l_batch_header.batch_id);
361: IF (g_debug <= gme_debug.g_log_statement) THEN
357: gme_debug.put_line('After insert rsrc txns');
358: END IF;
359: create_step_dependencies(p_old_batch_id => get_batches.batch_id,
360: p_new_batch_id => l_batch_header.batch_id);
361: IF (g_debug <= gme_debug.g_log_statement) THEN
362: gme_debug.put_line('After create dependencies');
363: END IF;
364: create_item_step_assoc(p_old_batch_id => get_batches.batch_id,
365: p_new_batch_id => l_batch_header.batch_id);
358: END IF;
359: create_step_dependencies(p_old_batch_id => get_batches.batch_id,
360: p_new_batch_id => l_batch_header.batch_id);
361: IF (g_debug <= gme_debug.g_log_statement) THEN
362: gme_debug.put_line('After create dependencies');
363: END IF;
364: create_item_step_assoc(p_old_batch_id => get_batches.batch_id,
365: p_new_batch_id => l_batch_header.batch_id);
366: IF (g_debug <= gme_debug.g_log_statement) THEN
362: gme_debug.put_line('After create dependencies');
363: END IF;
364: create_item_step_assoc(p_old_batch_id => get_batches.batch_id,
365: p_new_batch_id => l_batch_header.batch_id);
366: IF (g_debug <= gme_debug.g_log_statement) THEN
367: gme_debug.put_line('After create associations');
368: END IF;
369: create_batch_step_charges(p_old_batch_id => get_batches.batch_id,
370: p_new_batch_id => l_batch_header.batch_id);
363: END IF;
364: create_item_step_assoc(p_old_batch_id => get_batches.batch_id,
365: p_new_batch_id => l_batch_header.batch_id);
366: IF (g_debug <= gme_debug.g_log_statement) THEN
367: gme_debug.put_line('After create associations');
368: END IF;
369: create_batch_step_charges(p_old_batch_id => get_batches.batch_id,
370: p_new_batch_id => l_batch_header.batch_id);
371: IF (g_debug <= gme_debug.g_log_statement) THEN
367: gme_debug.put_line('After create associations');
368: END IF;
369: create_batch_step_charges(p_old_batch_id => get_batches.batch_id,
370: p_new_batch_id => l_batch_header.batch_id);
371: IF (g_debug <= gme_debug.g_log_statement) THEN
372: gme_debug.put_line('After create charges');
373: END IF;
374: create_batch_step_transfers(p_old_batch_id => get_batches.batch_id,
375: p_new_batch_id => l_batch_header.batch_id);
368: END IF;
369: create_batch_step_charges(p_old_batch_id => get_batches.batch_id,
370: p_new_batch_id => l_batch_header.batch_id);
371: IF (g_debug <= gme_debug.g_log_statement) THEN
372: gme_debug.put_line('After create charges');
373: END IF;
374: create_batch_step_transfers(p_old_batch_id => get_batches.batch_id,
375: p_new_batch_id => l_batch_header.batch_id);
376: IF (g_debug <= gme_debug.g_log_statement) THEN
372: gme_debug.put_line('After create charges');
373: END IF;
374: create_batch_step_transfers(p_old_batch_id => get_batches.batch_id,
375: p_new_batch_id => l_batch_header.batch_id);
376: IF (g_debug <= gme_debug.g_log_statement) THEN
377: gme_debug.put_line('After create transfers');
378: END IF;
379: FOR i IN 1..l_mtl_dtl_tbl.count LOOP
380: gme_common_pvt.calc_mtl_req_date(p_batch_header_rec => l_batch_header
373: END IF;
374: create_batch_step_transfers(p_old_batch_id => get_batches.batch_id,
375: p_new_batch_id => l_batch_header.batch_id);
376: IF (g_debug <= gme_debug.g_log_statement) THEN
377: gme_debug.put_line('After create transfers');
378: END IF;
379: FOR i IN 1..l_mtl_dtl_tbl.count LOOP
380: gme_common_pvt.calc_mtl_req_date(p_batch_header_rec => l_batch_header
381: ,p_batchstep_rec => NULL
385: IF (l_return_status <> fnd_api.g_ret_sts_success) THEN
386: RAISE calc_mtl_req_date_err;
387: END IF;
388: END LOOP;
389: IF (g_debug <= gme_debug.g_log_statement) THEN
390: gme_debug.put_line('After mtl_req_date');
391: END IF;
392: IF l_batch_header.batch_type = 0 AND NVL (l_batch_header.update_inventory_ind, 'Y') = 'Y' THEN
393: gme_move_orders_pvt.create_move_order_hdr
386: RAISE calc_mtl_req_date_err;
387: END IF;
388: END LOOP;
389: IF (g_debug <= gme_debug.g_log_statement) THEN
390: gme_debug.put_line('After mtl_req_date');
391: END IF;
392: IF l_batch_header.batch_type = 0 AND NVL (l_batch_header.update_inventory_ind, 'Y') = 'Y' THEN
393: gme_move_orders_pvt.create_move_order_hdr
394: (p_organization_id => l_batch_header.organization_id
410: RAISE create_mo_line_err;
411: END IF;
412: END IF;
413: END IF;
414: IF (g_debug <= gme_debug.g_log_statement) THEN
415: gme_debug.put_line('After create move order');
416: END IF;
417: UPDATE gme_batch_header
418: SET move_order_header_id = l_batch_header.move_order_header_id
411: END IF;
412: END IF;
413: END IF;
414: IF (g_debug <= gme_debug.g_log_statement) THEN
415: gme_debug.put_line('After create move order');
416: END IF;
417: UPDATE gme_batch_header
418: SET move_order_header_id = l_batch_header.move_order_header_id
419: WHERE batch_id = l_batch_header.batch_id;
423: SET move_order_line_id = l_mtl_dtl_tbl(a).move_order_line_id,
424: material_requirement_date = l_mtl_dtl_tbl(a).material_requirement_date
425: WHERE material_detail_id = l_mtl_dtl_tbl(a).material_detail_id;
426: END LOOP;
427: IF (g_debug <= gme_debug.g_log_statement) THEN
428: gme_debug.put_line('After update material');
429: END IF;
430: create_batch_mapping(p_batch_header_mig => get_batches,
431: p_batch_header => l_batch_header);
424: material_requirement_date = l_mtl_dtl_tbl(a).material_requirement_date
425: WHERE material_detail_id = l_mtl_dtl_tbl(a).material_detail_id;
426: END LOOP;
427: IF (g_debug <= gme_debug.g_log_statement) THEN
428: gme_debug.put_line('After update material');
429: END IF;
430: create_batch_mapping(p_batch_header_mig => get_batches,
431: p_batch_header => l_batch_header);
432: UPDATE gme_batch_header_mig
431: p_batch_header => l_batch_header);
432: UPDATE gme_batch_header_mig
433: SET migrated_batch_ind = 'M'
434: WHERE batch_id = get_batches.batch_id;
435: IF (g_debug <= gme_debug.g_log_statement) THEN
436: gme_debug.put_line('Done batch basic processing');
437: END IF;
438: EXCEPTION
439: WHEN item_not_defined THEN
432: UPDATE gme_batch_header_mig
433: SET migrated_batch_ind = 'M'
434: WHERE batch_id = get_batches.batch_id;
435: IF (g_debug <= gme_debug.g_log_statement) THEN
436: gme_debug.put_line('Done batch basic processing');
437: END IF;
438: EXCEPTION
439: WHEN item_not_defined THEN
440: ROLLBACK;
467: p_param1 => get_batches.plant_code||'-'||get_batches.batch_no,
468: p_token2 => 'MSG',
469: p_param2 => l_msg_data);
470: WHEN OTHERS THEN
471: IF (g_debug <= gme_debug.g_log_unexpected) THEN
472: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
473: END IF;
474: ROLLBACK;
475: gma_common_logging.gma_migration_central_log
468: p_token2 => 'MSG',
469: p_param2 => l_msg_data);
470: WHEN OTHERS THEN
471: IF (g_debug <= gme_debug.g_log_unexpected) THEN
472: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
473: END IF;
474: ROLLBACK;
475: gma_common_logging.gma_migration_central_log
476: (p_run_id => g_migration_run_id,
485: p_param2 => SQLERRM);
486: END;
487: COMMIT;
488: END LOOP;
489: IF (g_debug <= gme_debug.g_log_statement) THEN
490: gme_debug.put_line('Create phantom links');
491: END IF;
492: create_phantom_links;
493: IF (g_debug <= gme_debug.g_log_statement) THEN
486: END;
487: COMMIT;
488: END LOOP;
489: IF (g_debug <= gme_debug.g_log_statement) THEN
490: gme_debug.put_line('Create phantom links');
491: END IF;
492: create_phantom_links;
493: IF (g_debug <= gme_debug.g_log_statement) THEN
494: gme_debug.put_line('Create Reservations/Pending Lots');
489: IF (g_debug <= gme_debug.g_log_statement) THEN
490: gme_debug.put_line('Create phantom links');
491: END IF;
492: create_phantom_links;
493: IF (g_debug <= gme_debug.g_log_statement) THEN
494: gme_debug.put_line('Create Reservations/Pending Lots');
495: END IF;
496: /* Bug 5620671 Added param completed ind */
497: create_txns_reservations(0);
490: gme_debug.put_line('Create phantom links');
491: END IF;
492: create_phantom_links;
493: IF (g_debug <= gme_debug.g_log_statement) THEN
494: gme_debug.put_line('Create Reservations/Pending Lots');
495: END IF;
496: /* Bug 5620671 Added param completed ind */
497: create_txns_reservations(0);
498: IF (g_debug <= gme_debug.g_log_statement) THEN
494: gme_debug.put_line('Create Reservations/Pending Lots');
495: END IF;
496: /* Bug 5620671 Added param completed ind */
497: create_txns_reservations(0);
498: IF (g_debug <= gme_debug.g_log_statement) THEN
499: gme_debug.put_line('Release batches');
500: END IF;
501: gme_common_pvt.g_transaction_header_id := NULL;
502: release_batches;
495: END IF;
496: /* Bug 5620671 Added param completed ind */
497: create_txns_reservations(0);
498: IF (g_debug <= gme_debug.g_log_statement) THEN
499: gme_debug.put_line('Release batches');
500: END IF;
501: gme_common_pvt.g_transaction_header_id := NULL;
502: release_batches;
503: IF (g_debug <= gme_debug.g_log_statement) THEN
499: gme_debug.put_line('Release batches');
500: END IF;
501: gme_common_pvt.g_transaction_header_id := NULL;
502: release_batches;
503: IF (g_debug <= gme_debug.g_log_statement) THEN
504: gme_debug.put_line('Create transactions');
505: END IF;
506: /* Bug 5620671 Added param completed ind */
507: create_txns_reservations(1);
500: END IF;
501: gme_common_pvt.g_transaction_header_id := NULL;
502: release_batches;
503: IF (g_debug <= gme_debug.g_log_statement) THEN
504: gme_debug.put_line('Create transactions');
505: END IF;
506: /* Bug 5620671 Added param completed ind */
507: create_txns_reservations(1);
508: IF (g_debug <= gme_debug.g_log_statement) THEN
504: gme_debug.put_line('Create transactions');
505: END IF;
506: /* Bug 5620671 Added param completed ind */
507: create_txns_reservations(1);
508: IF (g_debug <= gme_debug.g_log_statement) THEN
509: gme_debug.put_line('Insert lab lots');
510: END IF;
511: insert_lab_lots;
512: IF (g_debug <= gme_debug.g_log_statement) THEN
505: END IF;
506: /* Bug 5620671 Added param completed ind */
507: create_txns_reservations(1);
508: IF (g_debug <= gme_debug.g_log_statement) THEN
509: gme_debug.put_line('Insert lab lots');
510: END IF;
511: insert_lab_lots;
512: IF (g_debug <= gme_debug.g_log_statement) THEN
513: gme_debug.put_line('Close Steps');
508: IF (g_debug <= gme_debug.g_log_statement) THEN
509: gme_debug.put_line('Insert lab lots');
510: END IF;
511: insert_lab_lots;
512: IF (g_debug <= gme_debug.g_log_statement) THEN
513: gme_debug.put_line('Close Steps');
514: END IF;
515: close_steps;
516: /* Bug 5703541 Added update stmt and loop for mtl_lot_conv_audit */
509: gme_debug.put_line('Insert lab lots');
510: END IF;
511: insert_lab_lots;
512: IF (g_debug <= gme_debug.g_log_statement) THEN
513: gme_debug.put_line('Close Steps');
514: END IF;
515: close_steps;
516: /* Bug 5703541 Added update stmt and loop for mtl_lot_conv_audit */
517: IF (g_debug <= gme_debug.g_log_statement) THEN
513: gme_debug.put_line('Close Steps');
514: END IF;
515: close_steps;
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
514: END IF;
515: close_steps;
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;
525: END LOOP;
526:
527: IF (g_debug <= gme_debug.g_log_statement) THEN
528: gme_debug.put_line('Call QM');
529: END IF;
530: gmd_qc_migb12.gmd_qc_migrate_batch_id(p_migration_run_id => g_migration_run_id,
531: p_commit => FND_API.G_TRUE,
524: WHERE batch_id = get_rec.old_batch_id;
525: END LOOP;
526:
527: IF (g_debug <= gme_debug.g_log_statement) THEN
528: gme_debug.put_line('Call QM');
529: END IF;
530: gmd_qc_migb12.gmd_qc_migrate_batch_id(p_migration_run_id => g_migration_run_id,
531: p_commit => FND_API.G_TRUE,
532: x_exception_count => l_msg_count);
529: END IF;
530: gmd_qc_migb12.gmd_qc_migrate_batch_id(p_migration_run_id => g_migration_run_id,
531: p_commit => FND_API.G_TRUE,
532: x_exception_count => l_msg_count);
533: IF (g_debug <= gme_debug.g_log_procedure) THEN
534: gme_debug.put_line('End procedure '||l_api_name);
535: END IF;
536: gma_migration.gma_migration_end (l_run_id => g_migration_run_id);
537: EXCEPTION
530: gmd_qc_migb12.gmd_qc_migrate_batch_id(p_migration_run_id => g_migration_run_id,
531: p_commit => FND_API.G_TRUE,
532: x_exception_count => l_msg_count);
533: IF (g_debug <= gme_debug.g_log_procedure) THEN
534: gme_debug.put_line('End procedure '||l_api_name);
535: END IF;
536: gma_migration.gma_migration_end (l_run_id => g_migration_run_id);
537: EXCEPTION
538: WHEN OTHERS THEN
535: END IF;
536: gma_migration.gma_migration_end (l_run_id => g_migration_run_id);
537: EXCEPTION
538: WHEN OTHERS THEN
539: IF (g_debug <= gme_debug.g_log_unexpected) THEN
540: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
541: END IF;
542: gma_common_logging.gma_migration_central_log
543: (p_run_id => g_migration_run_id,
536: gma_migration.gma_migration_end (l_run_id => g_migration_run_id);
537: EXCEPTION
538: WHEN OTHERS THEN
539: IF (g_debug <= gme_debug.g_log_unexpected) THEN
540: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
541: END IF;
542: gma_common_logging.gma_migration_central_log
543: (p_run_id => g_migration_run_id,
544: p_log_level => fnd_log.level_unexpected,
1137: TYPE step_dep_tab IS TABLE OF gme_batch_step_dependencies%ROWTYPE INDEX BY BINARY_INTEGER;
1138: l_step_dep_tbl step_dep_tab;
1139: l_api_name VARCHAR2(30) := 'create_step_dependencies';
1140: BEGIN
1141: IF (g_debug <= gme_debug.g_log_procedure) THEN
1142: gme_debug.put_line('Start procedure '||l_api_name);
1143: END IF;
1144: OPEN Cur_get_deps(p_old_batch_id);
1145: FETCH Cur_get_deps BULK COLLECT INTO l_step_dep_mig_tbl;
1138: l_step_dep_tbl step_dep_tab;
1139: l_api_name VARCHAR2(30) := 'create_step_dependencies';
1140: BEGIN
1141: IF (g_debug <= gme_debug.g_log_procedure) THEN
1142: gme_debug.put_line('Start procedure '||l_api_name);
1143: END IF;
1144: OPEN Cur_get_deps(p_old_batch_id);
1145: FETCH Cur_get_deps BULK COLLECT INTO l_step_dep_mig_tbl;
1146: CLOSE Cur_get_deps;
1196: l_step_dep_tbl(i).chargeable_ind := l_step_dep_mig_tbl(i).chargeable_ind;
1197: END LOOP;
1198: FORALL a IN 1..l_step_dep_tbl.count
1199: INSERT INTO gme_batch_step_dependencies VALUES l_step_dep_tbl(a);
1200: IF (g_debug <= gme_debug.g_log_procedure) THEN
1201: gme_debug.put_line('End procedure '||l_api_name);
1202: END IF;
1203: END create_step_dependencies;
1204:
1197: END LOOP;
1198: FORALL a IN 1..l_step_dep_tbl.count
1199: INSERT INTO gme_batch_step_dependencies VALUES l_step_dep_tbl(a);
1200: IF (g_debug <= gme_debug.g_log_procedure) THEN
1201: gme_debug.put_line('End procedure '||l_api_name);
1202: END IF;
1203: END create_step_dependencies;
1204:
1205: PROCEDURE create_item_step_assoc(p_old_batch_id IN NUMBER,
1213: TYPE item_step_tab IS TABLE OF gme_batch_step_items%ROWTYPE INDEX BY BINARY_INTEGER;
1214: l_item_step_tbl item_step_tab;
1215: l_api_name VARCHAR2(30) := 'create_item_step_assoc';
1216: BEGIN
1217: IF (g_debug <= gme_debug.g_log_procedure) THEN
1218: gme_debug.put_line('Start procedure '||l_api_name);
1219: END IF;
1220: OPEN Cur_item_assocs;
1221: FETCH Cur_item_assocs BULK COLLECT INTO l_item_step_mig_tbl;
1214: l_item_step_tbl item_step_tab;
1215: l_api_name VARCHAR2(30) := 'create_item_step_assoc';
1216: BEGIN
1217: IF (g_debug <= gme_debug.g_log_procedure) THEN
1218: gme_debug.put_line('Start procedure '||l_api_name);
1219: END IF;
1220: OPEN Cur_item_assocs;
1221: FETCH Cur_item_assocs BULK COLLECT INTO l_item_step_mig_tbl;
1222: CLOSE Cur_item_assocs;
1235: l_item_step_tbl(i).minimum_transfer_qty := l_item_step_mig_tbl(i).minimum_transfer_qty;
1236: END LOOP;
1237: FORALL a IN 1..l_item_step_tbl.count
1238: INSERT INTO gme_batch_step_items VALUES l_item_step_tbl(a);
1239: IF (g_debug <= gme_debug.g_log_procedure) THEN
1240: gme_debug.put_line('End procedure '||l_api_name);
1241: END IF;
1242: END create_item_step_assoc;
1243:
1236: END LOOP;
1237: FORALL a IN 1..l_item_step_tbl.count
1238: INSERT INTO gme_batch_step_items VALUES l_item_step_tbl(a);
1239: IF (g_debug <= gme_debug.g_log_procedure) THEN
1240: gme_debug.put_line('End procedure '||l_api_name);
1241: END IF;
1242: END create_item_step_assoc;
1243:
1244: PROCEDURE create_batch_step_charges(p_old_batch_id IN NUMBER,
1252: TYPE step_chrg_tab IS TABLE OF gme_batch_step_charges%ROWTYPE INDEX BY BINARY_INTEGER;
1253: l_step_chrg_tbl step_chrg_tab;
1254: l_api_name VARCHAR2(30) := 'create_batch_step_charges';
1255: BEGIN
1256: IF (g_debug <= gme_debug.g_log_procedure) THEN
1257: gme_debug.put_line('Start procedure '||l_api_name);
1258: END IF;
1259: OPEN Cur_step_charges;
1260: FETCH Cur_step_charges BULK COLLECT INTO l_step_chrg_mig_tbl;
1253: l_step_chrg_tbl step_chrg_tab;
1254: l_api_name VARCHAR2(30) := 'create_batch_step_charges';
1255: BEGIN
1256: IF (g_debug <= gme_debug.g_log_procedure) THEN
1257: gme_debug.put_line('Start procedure '||l_api_name);
1258: END IF;
1259: OPEN Cur_step_charges;
1260: FETCH Cur_step_charges BULK COLLECT INTO l_step_chrg_mig_tbl;
1261: CLOSE Cur_step_charges;
1275: l_step_chrg_tbl(i).last_update_date := l_step_chrg_mig_tbl(i).last_update_date;
1276: END LOOP;
1277: FORALL a IN 1..l_step_chrg_tbl.count
1278: INSERT INTO gme_batch_step_charges VALUES l_step_chrg_tbl(a);
1279: IF (g_debug <= gme_debug.g_log_procedure) THEN
1280: gme_debug.put_line('End procedure '||l_api_name);
1281: END IF;
1282: END create_batch_step_charges;
1283:
1276: END LOOP;
1277: FORALL a IN 1..l_step_chrg_tbl.count
1278: INSERT INTO gme_batch_step_charges VALUES l_step_chrg_tbl(a);
1279: IF (g_debug <= gme_debug.g_log_procedure) THEN
1280: gme_debug.put_line('End procedure '||l_api_name);
1281: END IF;
1282: END create_batch_step_charges;
1283:
1284: PROCEDURE create_batch_step_transfers(p_old_batch_id IN NUMBER,
1292: TYPE step_txfrs_tab IS TABLE OF gme_batch_step_transfers%ROWTYPE INDEX BY BINARY_INTEGER;
1293: l_step_txfrs_tbl step_txfrs_tab;
1294: l_api_name VARCHAR2(30) := 'create_batch_step_transfers';
1295: BEGIN
1296: IF (g_debug <= gme_debug.g_log_procedure) THEN
1297: gme_debug.put_line('Start procedure '||l_api_name);
1298: END IF;
1299: OPEN Cur_step_txfrs;
1300: FETCH Cur_step_txfrs BULK COLLECT INTO l_step_txfrs_mig_tbl;
1293: l_step_txfrs_tbl step_txfrs_tab;
1294: l_api_name VARCHAR2(30) := 'create_batch_step_transfers';
1295: BEGIN
1296: IF (g_debug <= gme_debug.g_log_procedure) THEN
1297: gme_debug.put_line('Start procedure '||l_api_name);
1298: END IF;
1299: OPEN Cur_step_txfrs;
1300: FETCH Cur_step_txfrs BULK COLLECT INTO l_step_txfrs_mig_tbl;
1301: CLOSE Cur_step_txfrs;
1349: l_step_txfrs_tbl(i).trans_qty_um := l_step_txfrs_mig_tbl(i).trans_qty_um;
1350: END LOOP;
1351: FORALL a IN 1..l_step_txfrs_tbl.count
1352: INSERT INTO gme_batch_step_transfers VALUES l_step_txfrs_tbl(a);
1353: IF (g_debug <= gme_debug.g_log_procedure) THEN
1354: gme_debug.put_line('End procedure '||l_api_name);
1355: END IF;
1356: END create_batch_step_transfers;
1357:
1350: END LOOP;
1351: FORALL a IN 1..l_step_txfrs_tbl.count
1352: INSERT INTO gme_batch_step_transfers VALUES l_step_txfrs_tbl(a);
1353: IF (g_debug <= gme_debug.g_log_procedure) THEN
1354: gme_debug.put_line('End procedure '||l_api_name);
1355: END IF;
1356: END create_batch_step_transfers;
1357:
1358: PROCEDURE create_batch_mapping(p_batch_header_mig IN gme_batch_header_mig%ROWTYPE,
1405: l_new_prod_line_id NUMBER;
1406: new_phant_batch_not_found EXCEPTION;
1407: new_phant_prod_not_found EXCEPTION;
1408: BEGIN
1409: IF (g_debug <= gme_debug.g_log_procedure) THEN
1410: gme_debug.put_line('Start procedure '||l_api_name);
1411: END IF;
1412: FOR get_phantoms IN Cur_get_phantoms LOOP
1413: BEGIN
1406: new_phant_batch_not_found EXCEPTION;
1407: new_phant_prod_not_found EXCEPTION;
1408: BEGIN
1409: IF (g_debug <= gme_debug.g_log_procedure) THEN
1410: gme_debug.put_line('Start procedure '||l_api_name);
1411: END IF;
1412: FOR get_phantoms IN Cur_get_phantoms LOOP
1413: BEGIN
1414: /* Get new phantom batch ID */
1463: p_app_short_name => 'GME',
1464: p_token1 => 'BATCH_NO',
1465: p_param1 => get_phantoms.plant_code||'-'||get_phantoms.new_batch_no);
1466: WHEN OTHERS THEN
1467: IF (g_debug <= gme_debug.g_log_unexpected) THEN
1468: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
1469: END IF;
1470: ROLLBACK;
1471: gma_common_logging.gma_migration_central_log
1464: p_token1 => 'BATCH_NO',
1465: p_param1 => get_phantoms.plant_code||'-'||get_phantoms.new_batch_no);
1466: WHEN OTHERS THEN
1467: IF (g_debug <= gme_debug.g_log_unexpected) THEN
1468: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
1469: END IF;
1470: ROLLBACK;
1471: gma_common_logging.gma_migration_central_log
1472: (p_run_id => g_migration_run_id,
1481: p_param2 => SQLERRM);
1482: END;
1483: COMMIT;
1484: END LOOP;
1485: IF (g_debug <= gme_debug.g_log_procedure) THEN
1486: gme_debug.put_line('End procedure '||l_api_name);
1487: END IF;
1488: END create_phantom_links;
1489:
1482: END;
1483: COMMIT;
1484: END LOOP;
1485: IF (g_debug <= gme_debug.g_log_procedure) THEN
1486: gme_debug.put_line('End procedure '||l_api_name);
1487: END IF;
1488: END create_phantom_links;
1489:
1490: PROCEDURE release_batches IS
1534: step_cmplt_err EXCEPTION;
1535: release_batch_err EXCEPTION;
1536: inv_phantoms_found EXCEPTION;
1537: BEGIN
1538: IF (g_debug <= gme_debug.g_log_procedure) THEN
1539: gme_debug.put_line('Start procedure '||l_api_name);
1540: END IF;
1541: gme_release_batch_pvt.g_bypass_txn_creation := 1;
1542: FOR get_wip_batches IN Cur_wip_batches LOOP
1535: release_batch_err EXCEPTION;
1536: inv_phantoms_found EXCEPTION;
1537: BEGIN
1538: IF (g_debug <= gme_debug.g_log_procedure) THEN
1539: gme_debug.put_line('Start procedure '||l_api_name);
1540: END IF;
1541: gme_release_batch_pvt.g_bypass_txn_creation := 1;
1542: FOR get_wip_batches IN Cur_wip_batches LOOP
1543: BEGIN
1673: p_param2 => get_steps.batchstep_no,
1674: p_token3 => 'MSG',
1675: p_param3 => l_msg_data);
1676: WHEN OTHERS THEN
1677: IF (g_debug <= gme_debug.g_log_unexpected) THEN
1678: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
1679: END IF;
1680: gma_common_logging.gma_migration_central_log
1681: (p_run_id => g_migration_run_id,
1674: p_token3 => 'MSG',
1675: p_param3 => l_msg_data);
1676: WHEN OTHERS THEN
1677: IF (g_debug <= gme_debug.g_log_unexpected) THEN
1678: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
1679: END IF;
1680: gma_common_logging.gma_migration_central_log
1681: (p_run_id => g_migration_run_id,
1682: p_log_level => fnd_log.level_error,
1717: p_param1 => get_wip_batches.plant_code||'-'||get_wip_batches.new_batch_no,
1718: p_token2 => 'MSG',
1719: p_param2 => l_msg_data);
1720: WHEN OTHERS THEN
1721: IF (g_debug <= gme_debug.g_log_unexpected) THEN
1722: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
1723: END IF;
1724: gma_common_logging.gma_migration_central_log
1725: (p_run_id => g_migration_run_id,
1718: p_token2 => 'MSG',
1719: p_param2 => l_msg_data);
1720: WHEN OTHERS THEN
1721: IF (g_debug <= gme_debug.g_log_unexpected) THEN
1722: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
1723: END IF;
1724: gma_common_logging.gma_migration_central_log
1725: (p_run_id => g_migration_run_id,
1726: p_log_level => fnd_log.level_error,
1733: p_token2 => 'MSG',
1734: p_param2 => SQLERRM);
1735: END;
1736: END LOOP;
1737: IF (g_debug <= gme_debug.g_log_procedure) THEN
1738: gme_debug.put_line('End procedure '||l_api_name);
1739: END IF;
1740: END release_batches;
1741:
1734: p_param2 => SQLERRM);
1735: END;
1736: END LOOP;
1737: IF (g_debug <= gme_debug.g_log_procedure) THEN
1738: gme_debug.put_line('End procedure '||l_api_name);
1739: END IF;
1740: END release_batches;
1741:
1742: PROCEDURE check_date(p_organization_id IN NUMBER,
1904: WHERE location = p_location;
1905: l_loc_rec ic_loct_mst%ROWTYPE;
1906: l_api_name VARCHAR2(30) := 'create_locator';
1907: BEGIN
1908: IF (g_debug <= gme_debug.g_log_procedure) THEN
1909: gme_debug.put_line('Start procedure '||l_api_name);
1910: END IF;
1911: IF (g_debug <= gme_debug.g_log_statement) THEN
1912: gme_debug.put_line('creating locator '||p_location||'->'||p_organization_id||'-'||p_subinventory_code);
1905: l_loc_rec ic_loct_mst%ROWTYPE;
1906: l_api_name VARCHAR2(30) := 'create_locator';
1907: BEGIN
1908: IF (g_debug <= gme_debug.g_log_procedure) THEN
1909: gme_debug.put_line('Start procedure '||l_api_name);
1910: END IF;
1911: IF (g_debug <= gme_debug.g_log_statement) THEN
1912: gme_debug.put_line('creating locator '||p_location||'->'||p_organization_id||'-'||p_subinventory_code);
1913: END IF;
1907: BEGIN
1908: IF (g_debug <= gme_debug.g_log_procedure) THEN
1909: gme_debug.put_line('Start procedure '||l_api_name);
1910: END IF;
1911: IF (g_debug <= gme_debug.g_log_statement) THEN
1912: gme_debug.put_line('creating locator '||p_location||'->'||p_organization_id||'-'||p_subinventory_code);
1913: END IF;
1914: OPEN Cur_loc_details;
1915: FETCH Cur_loc_details INTO l_loc_rec;
1908: IF (g_debug <= gme_debug.g_log_procedure) THEN
1909: gme_debug.put_line('Start procedure '||l_api_name);
1910: END IF;
1911: IF (g_debug <= gme_debug.g_log_statement) THEN
1912: gme_debug.put_line('creating locator '||p_location||'->'||p_organization_id||'-'||p_subinventory_code);
1913: END IF;
1914: OPEN Cur_loc_details;
1915: FETCH Cur_loc_details INTO l_loc_rec;
1916: CLOSE Cur_loc_details;
1942: p_segment17 => NULL,
1943: p_segment18 => NULL,
1944: p_segment19 => NULL,
1945: p_segment20 => NULL);
1946: IF (g_debug <= gme_debug.g_log_procedure) THEN
1947: gme_debug.put_line('End procedure '||l_api_name);
1948: END IF;
1949: END create_locator;
1950:
1943: p_segment18 => NULL,
1944: p_segment19 => NULL,
1945: p_segment20 => NULL);
1946: IF (g_debug <= gme_debug.g_log_procedure) THEN
1947: gme_debug.put_line('End procedure '||l_api_name);
1948: END IF;
1949: END create_locator;
1950:
1951: PROCEDURE get_subinv_locator_type(p_subinventory IN VARCHAR2,
2040: batch_fetch_err EXCEPTION;
2041: expected_error EXCEPTION;
2042: defined_error EXCEPTION;
2043: BEGIN
2044: IF (g_debug <= gme_debug.g_log_procedure) THEN
2045: gme_debug.put_line('Start procedure '||l_api_name);
2046: END IF;
2047: /* Bug 5620671 Added param completed ind */
2048: OPEN Cur_get_txns(p_completed_ind);
2041: expected_error EXCEPTION;
2042: defined_error EXCEPTION;
2043: BEGIN
2044: IF (g_debug <= gme_debug.g_log_procedure) THEN
2045: gme_debug.put_line('Start procedure '||l_api_name);
2046: END IF;
2047: /* Bug 5620671 Added param completed ind */
2048: OPEN Cur_get_txns(p_completed_ind);
2049: FETCH Cur_get_txns BULK COLLECT INTO l_txns_tbl;
2048: OPEN Cur_get_txns(p_completed_ind);
2049: FETCH Cur_get_txns BULK COLLECT INTO l_txns_tbl;
2050: CLOSE Cur_get_txns;
2051: l_count := l_txns_tbl.count;
2052: IF (g_debug <= gme_debug.g_log_statement) THEN
2053: gme_debug.put_line('No. of txns = '||l_count);
2054: END IF;
2055: FOR i IN 1..l_count LOOP
2056: BEGIN
2049: FETCH Cur_get_txns BULK COLLECT INTO l_txns_tbl;
2050: CLOSE Cur_get_txns;
2051: l_count := l_txns_tbl.count;
2052: IF (g_debug <= gme_debug.g_log_statement) THEN
2053: gme_debug.put_line('No. of txns = '||l_count);
2054: END IF;
2055: FOR i IN 1..l_count LOOP
2056: BEGIN
2057: IF (g_debug <= gme_debug.g_log_statement) THEN
2053: gme_debug.put_line('No. of txns = '||l_count);
2054: END IF;
2055: FOR i IN 1..l_count LOOP
2056: BEGIN
2057: IF (g_debug <= gme_debug.g_log_statement) THEN
2058: gme_debug.put_line('Processing trans_id = '||l_txns_tbl(i).trans_id);
2059: gme_debug.put_line('l_curr_org_id = '||l_curr_org_id||' l_txns_tbl(i).organization_id = '||l_txns_tbl(i).organization_id);
2060: END IF;
2061: gme_common_pvt.g_transaction_header_id := NULL;
2054: END IF;
2055: FOR i IN 1..l_count LOOP
2056: BEGIN
2057: IF (g_debug <= gme_debug.g_log_statement) THEN
2058: gme_debug.put_line('Processing trans_id = '||l_txns_tbl(i).trans_id);
2059: gme_debug.put_line('l_curr_org_id = '||l_curr_org_id||' l_txns_tbl(i).organization_id = '||l_txns_tbl(i).organization_id);
2060: END IF;
2061: gme_common_pvt.g_transaction_header_id := NULL;
2062: l_lot_number := NULL;
2055: FOR i IN 1..l_count LOOP
2056: BEGIN
2057: IF (g_debug <= gme_debug.g_log_statement) THEN
2058: gme_debug.put_line('Processing trans_id = '||l_txns_tbl(i).trans_id);
2059: gme_debug.put_line('l_curr_org_id = '||l_curr_org_id||' l_txns_tbl(i).organization_id = '||l_txns_tbl(i).organization_id);
2060: END IF;
2061: gme_common_pvt.g_transaction_header_id := NULL;
2062: l_lot_number := NULL;
2063: l_subinventory := NULL;
2071: IF NOT (gme_common_pvt.setup(p_org_id => l_txns_tbl(i).organization_id)) THEN
2072: RAISE setup_failed;
2073: END IF;
2074: l_curr_org_id := l_txns_tbl(i).organization_id;
2075: IF (g_debug <= gme_debug.g_log_statement) THEN
2076: gme_debug.put_line('gme_common_pvt.g_organization_id = '||gme_common_pvt.g_organization_id||' gme_common_pvt.g_organization_code = '||gme_common_pvt.g_organization_code);
2077: END IF;
2078: END IF;
2079: IF (l_curr_batch_id <> l_txns_tbl(i).new_batch_id) THEN
2072: RAISE setup_failed;
2073: END IF;
2074: l_curr_org_id := l_txns_tbl(i).organization_id;
2075: IF (g_debug <= gme_debug.g_log_statement) THEN
2076: gme_debug.put_line('gme_common_pvt.g_organization_id = '||gme_common_pvt.g_organization_id||' gme_common_pvt.g_organization_code = '||gme_common_pvt.g_organization_code);
2077: END IF;
2078: END IF;
2079: IF (l_curr_batch_id <> l_txns_tbl(i).new_batch_id) THEN
2080: l_batch_hdr.batch_id := l_txns_tbl(i).new_batch_id;
2090: OPEN Cur_mtl_dtl(l_mat_detail_id);
2091: FETCH Cur_mtl_dtl INTO l_mtl_rec;
2092: CLOSE Cur_mtl_dtl;
2093: END IF;
2094: IF (g_debug <= gme_debug.g_log_statement) THEN
2095: gme_debug.put_line('Processing transaction for Batch = '||gme_common_pvt.g_organization_code||'-'||l_batch_hdr.batch_no||' Line Type = '||l_mtl_rec.line_type||' Line No = '||l_mtl_rec.line_no||' Phantom_type = '||l_mtl_rec.phantom_type);
2096: END IF;
2097: /* Do not create phantom ing txns these will be created by phantom prod txns */
2098: IF NOT(l_mtl_rec.line_type = gme_common_pvt.g_line_type_ing AND l_mtl_rec.phantom_type IN (gme_common_pvt.g_auto_phantom, gme_common_pvt.g_manual_phantom)) THEN
2091: FETCH Cur_mtl_dtl INTO l_mtl_rec;
2092: CLOSE Cur_mtl_dtl;
2093: END IF;
2094: IF (g_debug <= gme_debug.g_log_statement) THEN
2095: gme_debug.put_line('Processing transaction for Batch = '||gme_common_pvt.g_organization_code||'-'||l_batch_hdr.batch_no||' Line Type = '||l_mtl_rec.line_type||' Line No = '||l_mtl_rec.line_no||' Phantom_type = '||l_mtl_rec.phantom_type);
2096: END IF;
2097: /* Do not create phantom ing txns these will be created by phantom prod txns */
2098: IF NOT(l_mtl_rec.line_type = gme_common_pvt.g_line_type_ing AND l_mtl_rec.phantom_type IN (gme_common_pvt.g_auto_phantom, gme_common_pvt.g_manual_phantom)) THEN
2099: IF (g_debug <= gme_debug.g_log_statement) THEN
2095: gme_debug.put_line('Processing transaction for Batch = '||gme_common_pvt.g_organization_code||'-'||l_batch_hdr.batch_no||' Line Type = '||l_mtl_rec.line_type||' Line No = '||l_mtl_rec.line_no||' Phantom_type = '||l_mtl_rec.phantom_type);
2096: END IF;
2097: /* Do not create phantom ing txns these will be created by phantom prod txns */
2098: IF NOT(l_mtl_rec.line_type = gme_common_pvt.g_line_type_ing AND l_mtl_rec.phantom_type IN (gme_common_pvt.g_auto_phantom, gme_common_pvt.g_manual_phantom)) THEN
2099: IF (g_debug <= gme_debug.g_log_statement) THEN
2100: gme_debug.put_line('Not a phantom ing txn');
2101: END IF;
2102: IF (l_txns_tbl(i).completed_ind = 1) THEN
2103: IF (g_debug <= gme_debug.g_log_statement) THEN
2096: END IF;
2097: /* Do not create phantom ing txns these will be created by phantom prod txns */
2098: IF NOT(l_mtl_rec.line_type = gme_common_pvt.g_line_type_ing AND l_mtl_rec.phantom_type IN (gme_common_pvt.g_auto_phantom, gme_common_pvt.g_manual_phantom)) THEN
2099: IF (g_debug <= gme_debug.g_log_statement) THEN
2100: gme_debug.put_line('Not a phantom ing txn');
2101: END IF;
2102: IF (l_txns_tbl(i).completed_ind = 1) THEN
2103: IF (g_debug <= gme_debug.g_log_statement) THEN
2104: gme_debug.put_line('This is a completed txn');
2099: IF (g_debug <= gme_debug.g_log_statement) THEN
2100: gme_debug.put_line('Not a phantom ing txn');
2101: END IF;
2102: IF (l_txns_tbl(i).completed_ind = 1) THEN
2103: IF (g_debug <= gme_debug.g_log_statement) THEN
2104: gme_debug.put_line('This is a completed txn');
2105: END IF;
2106: IF (l_batch_hdr.batch_status <= 1) THEN
2107: l_msg_name := 'GME_MIG_BATCH_INVALID_FOR_TXN';
2100: gme_debug.put_line('Not a phantom ing txn');
2101: END IF;
2102: IF (l_txns_tbl(i).completed_ind = 1) THEN
2103: IF (g_debug <= gme_debug.g_log_statement) THEN
2104: gme_debug.put_line('This is a completed txn');
2105: END IF;
2106: IF (l_batch_hdr.batch_status <= 1) THEN
2107: l_msg_name := 'GME_MIG_BATCH_INVALID_FOR_TXN';
2108: RAISE defined_error;
2111: l_msg_name := 'GME_MIG_ITEM_NOT_TXNS_ENABLED';
2112: RAISE defined_error;
2113: END IF;
2114: SELECT mtl_material_transactions_s.NEXTVAL INTO gme_common_pvt.g_transaction_header_id FROM DUAL;
2115: IF (g_debug <= gme_debug.g_log_statement) THEN
2116: gme_debug.put_line('Transaction header ID = '||gme_common_pvt.g_transaction_header_id);
2117: END IF;
2118: l_mmti_rec.transaction_source_id := l_mtl_rec.batch_id;
2119: l_mmti_rec.trx_source_line_id := l_mtl_rec.material_detail_id;
2112: RAISE defined_error;
2113: END IF;
2114: SELECT mtl_material_transactions_s.NEXTVAL INTO gme_common_pvt.g_transaction_header_id FROM DUAL;
2115: IF (g_debug <= gme_debug.g_log_statement) THEN
2116: gme_debug.put_line('Transaction header ID = '||gme_common_pvt.g_transaction_header_id);
2117: END IF;
2118: l_mmti_rec.transaction_source_id := l_mtl_rec.batch_id;
2119: l_mmti_rec.trx_source_line_id := l_mtl_rec.material_detail_id;
2120: l_mmti_rec.inventory_item_id := l_mtl_rec.inventory_item_id;
2127: END IF;
2128: ELSE
2129: l_mmti_rec.revision := NULL;
2130: END IF;
2131: IF (g_debug <= gme_debug.g_log_statement) THEN
2132: gme_debug.put_line('After revision before check date');
2133: END IF;
2134: check_date(p_organization_id => l_mtl_rec.organization_id,
2135: p_date => l_txns_tbl(i).trans_date,
2128: ELSE
2129: l_mmti_rec.revision := NULL;
2130: END IF;
2131: IF (g_debug <= gme_debug.g_log_statement) THEN
2132: gme_debug.put_line('After revision before check date');
2133: END IF;
2134: check_date(p_organization_id => l_mtl_rec.organization_id,
2135: p_date => l_txns_tbl(i).trans_date,
2136: x_date => l_date,
2175: p_param3 => l_txn_data,
2176: p_token4 => 'REASON_CODE',
2177: p_param4 => l_txns_tbl(i).reason_code);
2178: END IF;
2179: IF (g_debug <= gme_debug.g_log_statement) THEN
2180: gme_debug.put_line('After putting all values in l_mmti_rec');
2181: END IF;
2182: /* If item is location controlled then get locator/sub/org otherwise get sub/org */
2183: IF (NVL(l_txns_tbl(i).location, l_def_location) <> l_def_location) THEN
2176: p_token4 => 'REASON_CODE',
2177: p_param4 => l_txns_tbl(i).reason_code);
2178: END IF;
2179: IF (g_debug <= gme_debug.g_log_statement) THEN
2180: gme_debug.put_line('After putting all values in l_mmti_rec');
2181: END IF;
2182: /* If item is location controlled then get locator/sub/org otherwise get sub/org */
2183: IF (NVL(l_txns_tbl(i).location, l_def_location) <> l_def_location) THEN
2184: get_locator(p_location => l_txns_tbl(i).location,
2186: x_organization_id => l_org_id,
2187: x_locator_id => l_locator_id,
2188: x_subinventory => l_subinventory);
2189: END IF;
2190: IF (g_debug <= gme_debug.g_log_statement) THEN
2191: gme_debug.put_line('One l_org_id = '||l_org_id||' l_subinventory = '||l_subinventory||' l_locator_id = '||l_locator_id );
2192: END IF;
2193:
2194: /* If we have sub it means locator exists otherwise get sub */
2187: x_locator_id => l_locator_id,
2188: x_subinventory => l_subinventory);
2189: END IF;
2190: IF (g_debug <= gme_debug.g_log_statement) THEN
2191: gme_debug.put_line('One l_org_id = '||l_org_id||' l_subinventory = '||l_subinventory||' l_locator_id = '||l_locator_id );
2192: END IF;
2193:
2194: /* If we have sub it means locator exists otherwise get sub */
2195: IF (l_subinventory IS NULL) THEN
2200: l_msg_name := 'GME_MIG_SUBINV_NOT_FOUND';
2201: RAISE defined_error;
2202: END IF;
2203: END IF;
2204: IF (g_debug <= gme_debug.g_log_statement) THEN
2205: gme_debug.put_line('Two l_org_id = '||l_org_id||' l_subinventory = '||l_subinventory||' l_locator_id = '||l_locator_id );
2206: gme_debug.put_line('l_org_id = '||l_org_id||' l_mmti_rec.organization_id = '||l_mmti_rec.organization_id);
2207: END IF;
2208: /* If subinventory is in same org as batch then it is fine or we have to do issue/receipt */
2201: RAISE defined_error;
2202: END IF;
2203: END IF;
2204: IF (g_debug <= gme_debug.g_log_statement) THEN
2205: gme_debug.put_line('Two l_org_id = '||l_org_id||' l_subinventory = '||l_subinventory||' l_locator_id = '||l_locator_id );
2206: gme_debug.put_line('l_org_id = '||l_org_id||' l_mmti_rec.organization_id = '||l_mmti_rec.organization_id);
2207: END IF;
2208: /* If subinventory is in same org as batch then it is fine or we have to do issue/receipt */
2209: IF (l_org_id <> l_mmti_rec.organization_id) THEN
2202: END IF;
2203: END IF;
2204: IF (g_debug <= gme_debug.g_log_statement) THEN
2205: gme_debug.put_line('Two l_org_id = '||l_org_id||' l_subinventory = '||l_subinventory||' l_locator_id = '||l_locator_id );
2206: gme_debug.put_line('l_org_id = '||l_org_id||' l_mmti_rec.organization_id = '||l_mmti_rec.organization_id);
2207: END IF;
2208: /* If subinventory is in same org as batch then it is fine or we have to do issue/receipt */
2209: IF (l_org_id <> l_mmti_rec.organization_id) THEN
2210: /* Create a misc issue in l_org_id and a receipt in l_mmti_rec.organization_id */
2207: END IF;
2208: /* If subinventory is in same org as batch then it is fine or we have to do issue/receipt */
2209: IF (l_org_id <> l_mmti_rec.organization_id) THEN
2210: /* Create a misc issue in l_org_id and a receipt in l_mmti_rec.organization_id */
2211: IF (g_debug <= gme_debug.g_log_statement) THEN
2212: gme_debug.put_line('creating issue/receipt from org = '||l_org_id||' to org = '||l_mmti_rec.organization_id);
2213: END IF;
2214: create_issue_receipt(p_curr_org_id => l_org_id,
2215: p_inventory_item_id => l_mtl_rec.inventory_item_id,
2208: /* If subinventory is in same org as batch then it is fine or we have to do issue/receipt */
2209: IF (l_org_id <> l_mmti_rec.organization_id) THEN
2210: /* Create a misc issue in l_org_id and a receipt in l_mmti_rec.organization_id */
2211: IF (g_debug <= gme_debug.g_log_statement) THEN
2212: gme_debug.put_line('creating issue/receipt from org = '||l_org_id||' to org = '||l_mmti_rec.organization_id);
2213: END IF;
2214: create_issue_receipt(p_curr_org_id => l_org_id,
2215: p_inventory_item_id => l_mtl_rec.inventory_item_id,
2216: p_txn_rec => l_txns_tbl(i),
2226: IF (l_return_status <> FND_API.G_RET_STS_SUCCESS) THEN
2227: RAISE expected_error;
2228: END IF;
2229: ELSE
2230: IF (g_debug <= gme_debug.g_log_statement) THEN
2231: gme_debug.put_line('All in same org');
2232: END IF;
2233: IF NOT (gme_common_pvt.check_subinventory(p_organization_id => l_mtl_rec.organization_id
2234: ,p_subinventory => l_subinventory
2227: RAISE expected_error;
2228: END IF;
2229: ELSE
2230: IF (g_debug <= gme_debug.g_log_statement) THEN
2231: gme_debug.put_line('All in same org');
2232: END IF;
2233: IF NOT (gme_common_pvt.check_subinventory(p_organization_id => l_mtl_rec.organization_id
2234: ,p_subinventory => l_subinventory
2235: ,p_inventory_item_id => l_mtl_rec.inventory_item_id
2281: END IF;
2282: l_mmti_rec.subinventory_code := l_subinventory;
2283: l_mmti_rec.locator_id := l_locator_id;
2284: END IF;
2285: IF (g_debug <= gme_debug.g_log_statement) THEN
2286: gme_debug.put_line('Lot control code is '||l_mtl_rec.lot_control_code);
2287: END IF;
2288: IF (l_mtl_rec.lot_control_code = 2) THEN
2289: IF (l_lot_number IS NULL) THEN
2282: l_mmti_rec.subinventory_code := l_subinventory;
2283: l_mmti_rec.locator_id := l_locator_id;
2284: END IF;
2285: IF (g_debug <= gme_debug.g_log_statement) THEN
2286: gme_debug.put_line('Lot control code is '||l_mtl_rec.lot_control_code);
2287: END IF;
2288: IF (l_mtl_rec.lot_control_code = 2) THEN
2289: IF (l_lot_number IS NULL) THEN
2290: inv_opm_lot_migration.get_odm_lot(p_migration_run_id => g_migration_run_id,
2303: END IF;
2304: END IF;
2305: l_mmli_tbl(1).lot_number := l_lot_number;
2306: END IF;
2307: IF (g_debug <= gme_debug.g_log_statement) THEN
2308: gme_debug.put_line('After Lot processing lot is '||l_lot_number);
2309: END IF;
2310: IF (l_mtl_rec.dtl_um <> l_mtl_rec.primary_uom_code) THEN
2311: l_mmti_rec.transaction_quantity := inv_convert.inv_um_convert(item_id => l_mtl_rec.inventory_item_id
2304: END IF;
2305: l_mmli_tbl(1).lot_number := l_lot_number;
2306: END IF;
2307: IF (g_debug <= gme_debug.g_log_statement) THEN
2308: gme_debug.put_line('After Lot processing lot is '||l_lot_number);
2309: END IF;
2310: IF (l_mtl_rec.dtl_um <> l_mtl_rec.primary_uom_code) THEN
2311: l_mmti_rec.transaction_quantity := inv_convert.inv_um_convert(item_id => l_mtl_rec.inventory_item_id
2312: ,lot_number => l_lot_number
2328: l_mmli_tbl(1).secondary_transaction_quantity := l_mmti_rec.secondary_transaction_quantity;
2329: END IF;
2330: l_new_data := gme_common_pvt.g_organization_code||'->'||l_mtl_rec.item_no||'->'||l_mmti_rec.revision||'->'||
2331: l_mmti_rec.subinventory_code||'->'||l_mmti_rec.locator_id||'->'||l_lot_number||'->'||l_mmti_rec.transaction_quantity||'->'||l_mmti_rec.transaction_uom||'->'||TO_CHAR(l_mmti_rec.transaction_date, 'DD-MON-YYYY HH24:MI:SS');
2332: IF (g_debug <= gme_debug.g_log_statement) THEN
2333: gme_debug.put_line('Creating TXN with '||l_new_data);
2334: END IF;
2335: gme_api_pub.create_material_txn(p_api_version => 2.0,
2336: p_validation_level => gme_common_pvt.g_max_errors,
2329: END IF;
2330: l_new_data := gme_common_pvt.g_organization_code||'->'||l_mtl_rec.item_no||'->'||l_mmti_rec.revision||'->'||
2331: l_mmti_rec.subinventory_code||'->'||l_mmti_rec.locator_id||'->'||l_lot_number||'->'||l_mmti_rec.transaction_quantity||'->'||l_mmti_rec.transaction_uom||'->'||TO_CHAR(l_mmti_rec.transaction_date, 'DD-MON-YYYY HH24:MI:SS');
2332: IF (g_debug <= gme_debug.g_log_statement) THEN
2333: gme_debug.put_line('Creating TXN with '||l_new_data);
2334: END IF;
2335: gme_api_pub.create_material_txn(p_api_version => 2.0,
2336: p_validation_level => gme_common_pvt.g_max_errors,
2337: p_init_msg_list => fnd_api.g_false,
2354: l_msg_name := 'GME_CREATE_TXN_FAIL';
2355: RAISE create_txn_rsv_pp_err;
2356: END IF;
2357: ELSE /* IF (l_txns_tbl(i).completed_ind = 1) THEN */
2358: IF (g_debug <= gme_debug.g_log_statement) THEN
2359: gme_debug.put_line('This is a pending txn');
2360: END IF;
2361: l_mmti_rec.transaction_source_id := l_mtl_rec.batch_id;
2362: l_mmti_rec.trx_source_line_id := l_mtl_rec.material_detail_id;
2355: RAISE create_txn_rsv_pp_err;
2356: END IF;
2357: ELSE /* IF (l_txns_tbl(i).completed_ind = 1) THEN */
2358: IF (g_debug <= gme_debug.g_log_statement) THEN
2359: gme_debug.put_line('This is a pending txn');
2360: END IF;
2361: l_mmti_rec.transaction_source_id := l_mtl_rec.batch_id;
2362: l_mmti_rec.trx_source_line_id := l_mtl_rec.material_detail_id;
2363: l_mmti_rec.inventory_item_id := l_mtl_rec.inventory_item_id;
2362: l_mmti_rec.trx_source_line_id := l_mtl_rec.material_detail_id;
2363: l_mmti_rec.inventory_item_id := l_mtl_rec.inventory_item_id;
2364: l_mmti_rec.organization_id := l_mtl_rec.organization_id;
2365: IF (l_mtl_rec.line_type = gme_common_pvt.g_line_type_ing) THEN
2366: IF (g_debug <= gme_debug.g_log_statement) THEN
2367: gme_debug.put_line('Ing so we will create a reservation');
2368: END IF;
2369: IF (l_mtl_rec.reservable_type <> 1) THEN
2370: l_msg_name := 'GME_MIG_ITEM_NOT_RESV_ENABLED';
2363: l_mmti_rec.inventory_item_id := l_mtl_rec.inventory_item_id;
2364: l_mmti_rec.organization_id := l_mtl_rec.organization_id;
2365: IF (l_mtl_rec.line_type = gme_common_pvt.g_line_type_ing) THEN
2366: IF (g_debug <= gme_debug.g_log_statement) THEN
2367: gme_debug.put_line('Ing so we will create a reservation');
2368: END IF;
2369: IF (l_mtl_rec.reservable_type <> 1) THEN
2370: l_msg_name := 'GME_MIG_ITEM_NOT_RESV_ENABLED';
2371: RAISE defined_error;
2423: END IF;
2424: ELSE
2425: l_mtl_dtl_rec.revision := NULL;
2426: END IF;
2427: IF (g_debug <= gme_debug.g_log_statement) THEN
2428: gme_debug.put_line('After defaulting ing');
2429: END IF;
2430: IF (NVL(l_txns_tbl(i).location, l_def_location) <> l_def_location) THEN
2431: get_locator(p_location => l_txns_tbl(i).location,
2424: ELSE
2425: l_mtl_dtl_rec.revision := NULL;
2426: END IF;
2427: IF (g_debug <= gme_debug.g_log_statement) THEN
2428: gme_debug.put_line('After defaulting ing');
2429: END IF;
2430: IF (NVL(l_txns_tbl(i).location, l_def_location) <> l_def_location) THEN
2431: get_locator(p_location => l_txns_tbl(i).location,
2432: p_whse_code => l_txns_tbl(i).whse_code,
2433: x_organization_id => l_org_id,
2434: x_locator_id => l_locator_id,
2435: x_subinventory => l_subinventory);
2436: END IF;
2437: IF (g_debug <= gme_debug.g_log_statement) THEN
2438: gme_debug.put_line('Three locator_id = '||l_locator_id||' subinventory = '||l_subinventory||' org_id = '||l_org_id);
2439: END IF;
2440: IF (l_subinventory IS NULL) THEN
2441: get_subinventory(p_whse_code => l_txns_tbl(i).whse_code,
2434: x_locator_id => l_locator_id,
2435: x_subinventory => l_subinventory);
2436: END IF;
2437: IF (g_debug <= gme_debug.g_log_statement) THEN
2438: gme_debug.put_line('Three locator_id = '||l_locator_id||' subinventory = '||l_subinventory||' org_id = '||l_org_id);
2439: END IF;
2440: IF (l_subinventory IS NULL) THEN
2441: get_subinventory(p_whse_code => l_txns_tbl(i).whse_code,
2442: x_subinventory => l_subinventory,
2442: x_subinventory => l_subinventory,
2443: x_organization_id => l_org_id);
2444: END IF;
2445: /* If this txn is in a different org than the batch org then do a issue and receipt */
2446: IF (g_debug <= gme_debug.g_log_statement) THEN
2447: gme_debug.put_line('Four l_subinventory = '||l_subinventory||' l_org_id = '||l_org_id||' l_mmti_rec.organization_id = '||l_mmti_rec.organization_id);
2448: gme_debug.put_line('creating issue/receipt from org = '||l_org_id||' to org = '||l_mmti_rec.organization_id);
2449: END IF;
2450: IF (l_org_id <> l_mmti_rec.organization_id) THEN
2443: x_organization_id => l_org_id);
2444: END IF;
2445: /* If this txn is in a different org than the batch org then do a issue and receipt */
2446: IF (g_debug <= gme_debug.g_log_statement) THEN
2447: gme_debug.put_line('Four l_subinventory = '||l_subinventory||' l_org_id = '||l_org_id||' l_mmti_rec.organization_id = '||l_mmti_rec.organization_id);
2448: gme_debug.put_line('creating issue/receipt from org = '||l_org_id||' to org = '||l_mmti_rec.organization_id);
2449: END IF;
2450: IF (l_org_id <> l_mmti_rec.organization_id) THEN
2451: /* Create a misc issue in l_org_id and a receipt in l_mmti_rec.organization_id */
2444: END IF;
2445: /* If this txn is in a different org than the batch org then do a issue and receipt */
2446: IF (g_debug <= gme_debug.g_log_statement) THEN
2447: gme_debug.put_line('Four l_subinventory = '||l_subinventory||' l_org_id = '||l_org_id||' l_mmti_rec.organization_id = '||l_mmti_rec.organization_id);
2448: gme_debug.put_line('creating issue/receipt from org = '||l_org_id||' to org = '||l_mmti_rec.organization_id);
2449: END IF;
2450: IF (l_org_id <> l_mmti_rec.organization_id) THEN
2451: /* Create a misc issue in l_org_id and a receipt in l_mmti_rec.organization_id */
2452: l_mmti_rec.primary_quantity := ROUND(ABS(l_txns_tbl(i).trans_qty),5);
2471: RAISE defined_error;
2472: END IF;
2473: l_mmti_rec.transaction_date := l_date;
2474: SELECT mtl_material_transactions_s.NEXTVAL INTO gme_common_pvt.g_transaction_header_id FROM DUAL;
2475: IF (g_debug <= gme_debug.g_log_statement) THEN
2476: gme_debug.put_line('Transaction header ID for reservation is '||gme_common_pvt.g_transaction_header_id);
2477: END IF;
2478: l_in_subinventory := l_subinventory;
2479: create_issue_receipt(p_curr_org_id => l_org_id,
2472: END IF;
2473: l_mmti_rec.transaction_date := l_date;
2474: SELECT mtl_material_transactions_s.NEXTVAL INTO gme_common_pvt.g_transaction_header_id FROM DUAL;
2475: IF (g_debug <= gme_debug.g_log_statement) THEN
2476: gme_debug.put_line('Transaction header ID for reservation is '||gme_common_pvt.g_transaction_header_id);
2477: END IF;
2478: l_in_subinventory := l_subinventory;
2479: create_issue_receipt(p_curr_org_id => l_org_id,
2480: p_inventory_item_id => l_mtl_rec.inventory_item_id,
2499: l_msg_name := 'GME_MIG_INV_TRANSFER_FAIL';
2500: RAISE create_txn_rsv_pp_err;
2501: END IF;
2502: ELSE
2503: IF (g_debug <= gme_debug.g_log_statement) THEN
2504: gme_debug.put_line('No transfer for this reservation needed');
2505: END IF;
2506: IF (l_subinventory IS NOT NULL) THEN
2507: IF NOT (gme_common_pvt.check_subinventory(p_organization_id => l_mtl_rec.organization_id
2500: RAISE create_txn_rsv_pp_err;
2501: END IF;
2502: ELSE
2503: IF (g_debug <= gme_debug.g_log_statement) THEN
2504: gme_debug.put_line('No transfer for this reservation needed');
2505: END IF;
2506: IF (l_subinventory IS NOT NULL) THEN
2507: IF NOT (gme_common_pvt.check_subinventory(p_organization_id => l_mtl_rec.organization_id
2508: ,p_subinventory => l_subinventory
2567: l_msg_name := 'GME_MIG_ITEM_LOT_ENABLED';
2568: RAISE defined_error;
2569: END IF;
2570: END IF;
2571: IF (g_debug <= gme_debug.g_log_statement) THEN
2572: gme_debug.put_line('Lot control is for this reservation = '||l_mtl_rec.lot_control_code);
2573: END IF;
2574: IF (l_mtl_rec.lot_control_code = 2) THEN
2575: inv_opm_lot_migration.get_odm_lot(p_migration_run_id => g_migration_run_id,
2568: RAISE defined_error;
2569: END IF;
2570: END IF;
2571: IF (g_debug <= gme_debug.g_log_statement) THEN
2572: gme_debug.put_line('Lot control is for this reservation = '||l_mtl_rec.lot_control_code);
2573: END IF;
2574: IF (l_mtl_rec.lot_control_code = 2) THEN
2575: inv_opm_lot_migration.get_odm_lot(p_migration_run_id => g_migration_run_id,
2576: p_item_id => l_txns_tbl(i).item_id,
2587: RAISE defined_error;
2588: END IF;
2589: END IF;
2590: END IF;
2591: IF (g_debug <= gme_debug.g_log_statement) THEN
2592: gme_debug.put_line('Lot is '||l_lot_number);
2593: END IF;
2594: l_mtl_dtl_rec.revision := l_mmti_rec.revision;
2595: l_new_data := gme_common_pvt.g_organization_code||'->'||l_mtl_rec.item_no||'->'||l_mmti_rec.revision||'->'||l_subinventory||'->'||l_locator_id||'->'||l_lot_number||'->'||ABS(l_txns_tbl(i).trans_qty)||'->'||l_mtl_rec.primary_uom_code;
2588: END IF;
2589: END IF;
2590: END IF;
2591: IF (g_debug <= gme_debug.g_log_statement) THEN
2592: gme_debug.put_line('Lot is '||l_lot_number);
2593: END IF;
2594: l_mtl_dtl_rec.revision := l_mmti_rec.revision;
2595: l_new_data := gme_common_pvt.g_organization_code||'->'||l_mtl_rec.item_no||'->'||l_mmti_rec.revision||'->'||l_subinventory||'->'||l_locator_id||'->'||l_lot_number||'->'||ABS(l_txns_tbl(i).trans_qty)||'->'||l_mtl_rec.primary_uom_code;
2596: IF (g_debug <= gme_debug.g_log_statement) THEN
2592: gme_debug.put_line('Lot is '||l_lot_number);
2593: END IF;
2594: l_mtl_dtl_rec.revision := l_mmti_rec.revision;
2595: l_new_data := gme_common_pvt.g_organization_code||'->'||l_mtl_rec.item_no||'->'||l_mmti_rec.revision||'->'||l_subinventory||'->'||l_locator_id||'->'||l_lot_number||'->'||ABS(l_txns_tbl(i).trans_qty)||'->'||l_mtl_rec.primary_uom_code;
2596: IF (g_debug <= gme_debug.g_log_statement) THEN
2597: gme_debug.put_line('Creating reservation with '||l_new_data);
2598: END IF;
2599: gme_reservations_pvt.create_material_reservation(p_matl_dtl_rec => l_mtl_dtl_rec,
2600: p_resv_qty => ABS(l_txns_tbl(i).trans_qty),
2593: END IF;
2594: l_mtl_dtl_rec.revision := l_mmti_rec.revision;
2595: l_new_data := gme_common_pvt.g_organization_code||'->'||l_mtl_rec.item_no||'->'||l_mmti_rec.revision||'->'||l_subinventory||'->'||l_locator_id||'->'||l_lot_number||'->'||ABS(l_txns_tbl(i).trans_qty)||'->'||l_mtl_rec.primary_uom_code;
2596: IF (g_debug <= gme_debug.g_log_statement) THEN
2597: gme_debug.put_line('Creating reservation with '||l_new_data);
2598: END IF;
2599: gme_reservations_pvt.create_material_reservation(p_matl_dtl_rec => l_mtl_dtl_rec,
2600: p_resv_qty => ABS(l_txns_tbl(i).trans_qty),
2601: p_sec_resv_qty => ABS(l_txns_tbl(i).trans_qty2),
2609: RAISE create_txn_rsv_pp_err;
2610: END IF;
2611: END IF;
2612: ELSE /* products and byproducts create pending product lots */
2613: IF (g_debug <= gme_debug.g_log_statement) THEN
2614: gme_debug.put_line('Creating pending product lot');
2615: END IF;
2616: l_mtl_dtl_rec.material_requirement_date := l_mtl_rec.material_requirement_date;
2617: l_mtl_dtl_rec.organization_id := l_mtl_rec.organization_id;
2610: END IF;
2611: END IF;
2612: ELSE /* products and byproducts create pending product lots */
2613: IF (g_debug <= gme_debug.g_log_statement) THEN
2614: gme_debug.put_line('Creating pending product lot');
2615: END IF;
2616: l_mtl_dtl_rec.material_requirement_date := l_mtl_rec.material_requirement_date;
2617: l_mtl_dtl_rec.organization_id := l_mtl_rec.organization_id;
2618: l_mtl_dtl_rec.inventory_item_id := l_mtl_rec.inventory_item_id;
2672: l_plot_in_rec.secondary_quantity := l_txns_tbl(i).trans_qty2;
2673: l_plot_in_rec.reason_id := get_reason(l_txns_tbl(i).reason_code);
2674: l_plot_in_rec.sequence := NULL;
2675: l_new_data := gme_common_pvt.g_organization_code||'->'||l_mtl_rec.item_no||'->'||l_plot_in_rec.revision||'->'||l_plot_in_rec.lot_number||'->'||l_plot_in_rec.quantity||'->'||l_mtl_dtl_rec.dtl_um;
2676: IF (g_debug <= gme_debug.g_log_statement) THEN
2677: gme_debug.put_line('Creating pending product lot with '||l_new_data);
2678: END IF;
2679: gme_api_pub.create_pending_product_lot(p_api_version => 2.0,
2680: p_validation_level => gme_common_pvt.g_max_errors,
2673: l_plot_in_rec.reason_id := get_reason(l_txns_tbl(i).reason_code);
2674: l_plot_in_rec.sequence := NULL;
2675: l_new_data := gme_common_pvt.g_organization_code||'->'||l_mtl_rec.item_no||'->'||l_plot_in_rec.revision||'->'||l_plot_in_rec.lot_number||'->'||l_plot_in_rec.quantity||'->'||l_mtl_dtl_rec.dtl_um;
2676: IF (g_debug <= gme_debug.g_log_statement) THEN
2677: gme_debug.put_line('Creating pending product lot with '||l_new_data);
2678: END IF;
2679: gme_api_pub.create_pending_product_lot(p_api_version => 2.0,
2680: p_validation_level => gme_common_pvt.g_max_errors,
2681: p_init_msg_list => fnd_api.g_false,
2701: END IF; /* IF NOT(l_mtl_rec.line_type = gme_common_pvt.g_line_type_ing AND l_mtl_rec.phantom_type IN (gme_common_pvt.g_auto_phantom, gme_common_pvt.g_manual_phantom)) THEN */
2702: UPDATE gme_batch_txns_mig
2703: SET migrated_ind = 1
2704: WHERE trans_id = l_txns_tbl(i).trans_id;
2705: IF (g_debug <= gme_debug.g_log_statement) THEN
2706: gme_debug.put_line('Done transaction');
2707: END IF;
2708: COMMIT;
2709: EXCEPTION
2702: UPDATE gme_batch_txns_mig
2703: SET migrated_ind = 1
2704: WHERE trans_id = l_txns_tbl(i).trans_id;
2705: IF (g_debug <= gme_debug.g_log_statement) THEN
2706: gme_debug.put_line('Done transaction');
2707: END IF;
2708: COMMIT;
2709: EXCEPTION
2710: WHEN setup_failed OR batch_fetch_err THEN
2803: p_token5 => 'NEW_DATA',
2804: p_param5 => l_new_data);
2805: ROLLBACK;
2806: WHEN OTHERS THEN
2807: IF (g_debug <= gme_debug.g_log_unexpected) THEN
2808: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
2809: END IF;
2810: l_txn_data := l_mtl_rec.item_no||'->'||l_txns_tbl(i).whse_code||'->'||l_txns_tbl(i).location||'->'||
2811: l_txns_tbl(i).lot_id||'->'||l_txns_tbl(i).trans_qty||'->'||l_mtl_rec.primary_uom_code||'->'||TO_CHAR(l_txns_tbl(i).trans_date, 'DD-MON-YYYY HH24:MI:SS');
2804: p_param5 => l_new_data);
2805: ROLLBACK;
2806: WHEN OTHERS THEN
2807: IF (g_debug <= gme_debug.g_log_unexpected) THEN
2808: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
2809: END IF;
2810: l_txn_data := l_mtl_rec.item_no||'->'||l_txns_tbl(i).whse_code||'->'||l_txns_tbl(i).location||'->'||
2811: l_txns_tbl(i).lot_id||'->'||l_txns_tbl(i).trans_qty||'->'||l_mtl_rec.primary_uom_code||'->'||TO_CHAR(l_txns_tbl(i).trans_date, 'DD-MON-YYYY HH24:MI:SS');
2812: gma_common_logging.gma_migration_central_log
2824: p_param3 => l_txn_data);
2825: ROLLBACK;
2826: END;
2827: END LOOP;
2828: IF (g_debug <= gme_debug.g_log_procedure) THEN
2829: gme_debug.put_line('End procedure '||l_api_name);
2830: END IF;
2831: END create_txns_reservations;
2832:
2825: ROLLBACK;
2826: END;
2827: END LOOP;
2828: IF (g_debug <= gme_debug.g_log_procedure) THEN
2829: gme_debug.put_line('End procedure '||l_api_name);
2830: END IF;
2831: END create_txns_reservations;
2832:
2833: PROCEDURE create_issue_receipt(p_curr_org_id IN NUMBER,
2886: expected_error EXCEPTION;
2887: item_not_defined EXCEPTION;
2888: no_open_period_err EXCEPTION;
2889: BEGIN
2890: IF (g_debug <= gme_debug.g_log_procedure) THEN
2891: gme_debug.put_line('Start procedure '||l_api_name);
2892: END IF;
2893: IF (g_debug <= gme_debug.g_log_statement) THEN
2894: gme_debug.put_line('Moving from org = '||p_curr_org_id||' to org = '||p_batch_org_id);
2887: item_not_defined EXCEPTION;
2888: no_open_period_err EXCEPTION;
2889: BEGIN
2890: IF (g_debug <= gme_debug.g_log_procedure) THEN
2891: gme_debug.put_line('Start procedure '||l_api_name);
2892: END IF;
2893: IF (g_debug <= gme_debug.g_log_statement) THEN
2894: gme_debug.put_line('Moving from org = '||p_curr_org_id||' to org = '||p_batch_org_id);
2895: END IF;
2889: BEGIN
2890: IF (g_debug <= gme_debug.g_log_procedure) THEN
2891: gme_debug.put_line('Start procedure '||l_api_name);
2892: END IF;
2893: IF (g_debug <= gme_debug.g_log_statement) THEN
2894: gme_debug.put_line('Moving from org = '||p_curr_org_id||' to org = '||p_batch_org_id);
2895: END IF;
2896: x_return_status := FND_API.G_RET_STS_SUCCESS;
2897: OPEN Cur_get_org_params(p_curr_org_id);
2890: IF (g_debug <= gme_debug.g_log_procedure) THEN
2891: gme_debug.put_line('Start procedure '||l_api_name);
2892: END IF;
2893: IF (g_debug <= gme_debug.g_log_statement) THEN
2894: gme_debug.put_line('Moving from org = '||p_curr_org_id||' to org = '||p_batch_org_id);
2895: END IF;
2896: x_return_status := FND_API.G_RET_STS_SUCCESS;
2897: OPEN Cur_get_org_params(p_curr_org_id);
2898: FETCH Cur_get_org_params INTO l_allow_neg_inv, l_org_loc_control, l_organization_code;
2897: OPEN Cur_get_org_params(p_curr_org_id);
2898: FETCH Cur_get_org_params INTO l_allow_neg_inv, l_org_loc_control, l_organization_code;
2899: CLOSE Cur_get_org_params;
2900: l_txn_data := p_item_no||'->'||p_txn_rec.whse_code||'->'||p_txn_rec.location||'->'||p_txn_rec.lot_id||'->'||p_txn_rec.trans_qty||'->'||p_txn_rec.trans_um||'->'||to_char(p_txn_rec.trans_date, 'DD-MON-YYYY HH24:MI:SS');
2901: IF (g_debug <= gme_debug.g_log_statement) THEN
2902: gme_debug.put_line('TXN Data = '||l_txn_data);
2903: END IF;
2904: gma_common_logging.gma_migration_central_log
2905: (p_run_id => g_migration_run_id,
2898: FETCH Cur_get_org_params INTO l_allow_neg_inv, l_org_loc_control, l_organization_code;
2899: CLOSE Cur_get_org_params;
2900: l_txn_data := p_item_no||'->'||p_txn_rec.whse_code||'->'||p_txn_rec.location||'->'||p_txn_rec.lot_id||'->'||p_txn_rec.trans_qty||'->'||p_txn_rec.trans_um||'->'||to_char(p_txn_rec.trans_date, 'DD-MON-YYYY HH24:MI:SS');
2901: IF (g_debug <= gme_debug.g_log_statement) THEN
2902: gme_debug.put_line('TXN Data = '||l_txn_data);
2903: END IF;
2904: gma_common_logging.gma_migration_central_log
2905: (p_run_id => g_migration_run_id,
2906: p_log_level => fnd_log.level_error,
2938: l_msg_name := 'GME_MIG_ITEM_LOT_ENABLED';
2939: RAISE defined_error;
2940: END IF;
2941: END IF;
2942: IF (g_debug <= gme_debug.g_log_statement) THEN
2943: gme_debug.put_line('Before subinventory');
2944: END IF;
2945: /* Validate if sub found is valid in this org */
2946: IF NOT (gme_common_pvt.check_subinventory(p_organization_id => p_curr_org_id
2939: RAISE defined_error;
2940: END IF;
2941: END IF;
2942: IF (g_debug <= gme_debug.g_log_statement) THEN
2943: gme_debug.put_line('Before subinventory');
2944: END IF;
2945: /* Validate if sub found is valid in this org */
2946: IF NOT (gme_common_pvt.check_subinventory(p_organization_id => p_curr_org_id
2947: ,p_subinventory => p_subinventory
3062: l_issue_lot_rec.lot_number := l_lot_number;
3063: l_issue_lot_rec.transaction_quantity := -1 * ABS(p_mmti_rec.primary_quantity);
3064: l_issue_lot_rec.secondary_transaction_quantity := -1 * ABS(p_mmti_rec.secondary_transaction_quantity);
3065: END IF;
3066: IF (g_debug <= gme_debug.g_log_statement) THEN
3067: gme_debug.put_line('Issue record '||l_issue_rec.organization_id||'->'||p_item_no||'->'||l_issue_rec.subinventory_code||'->'||
3068: l_issue_rec.locator_id||'->'||l_lot_number||'->'||l_issue_rec.transaction_quantity||'->'||l_issue_rec.transaction_uom||'->'||to_char(l_issue_rec.transaction_date, 'DD-MON-YYYY HH24:MI:SS'));
3069: END IF;
3070: insert_interface_recs(p_mti_rec => l_issue_rec,
3063: l_issue_lot_rec.transaction_quantity := -1 * ABS(p_mmti_rec.primary_quantity);
3064: l_issue_lot_rec.secondary_transaction_quantity := -1 * ABS(p_mmti_rec.secondary_transaction_quantity);
3065: END IF;
3066: IF (g_debug <= gme_debug.g_log_statement) THEN
3067: gme_debug.put_line('Issue record '||l_issue_rec.organization_id||'->'||p_item_no||'->'||l_issue_rec.subinventory_code||'->'||
3068: l_issue_rec.locator_id||'->'||l_lot_number||'->'||l_issue_rec.transaction_quantity||'->'||l_issue_rec.transaction_uom||'->'||to_char(l_issue_rec.transaction_date, 'DD-MON-YYYY HH24:MI:SS'));
3069: END IF;
3070: insert_interface_recs(p_mti_rec => l_issue_rec,
3071: p_mtli_rec => l_issue_lot_rec,
3168: l_receipt_lot_rec.lot_number := l_lot_number;
3169: l_receipt_lot_rec.transaction_quantity := l_receipt_rec.transaction_quantity;
3170: l_receipt_lot_rec.secondary_transaction_quantity := l_receipt_rec.secondary_transaction_quantity;
3171: END IF;
3172: IF (g_debug <= gme_debug.g_log_statement) THEN
3173: gme_debug.put_line('Receipt record '||l_receipt_rec.organization_id||'->'||p_item_no||'->'||l_receipt_rec.subinventory_code||'->'||
3174: l_receipt_rec.locator_id||'->'||l_lot_number||'->'||l_receipt_rec.transaction_quantity||'->'||l_receipt_rec.transaction_uom||'->'||to_char(l_receipt_rec.transaction_date, 'DD-MON-YYYY HH24:MI:SS'));
3175: END IF;
3176: insert_interface_recs(p_mti_rec => l_receipt_rec,
3169: l_receipt_lot_rec.transaction_quantity := l_receipt_rec.transaction_quantity;
3170: l_receipt_lot_rec.secondary_transaction_quantity := l_receipt_rec.secondary_transaction_quantity;
3171: END IF;
3172: IF (g_debug <= gme_debug.g_log_statement) THEN
3173: gme_debug.put_line('Receipt record '||l_receipt_rec.organization_id||'->'||p_item_no||'->'||l_receipt_rec.subinventory_code||'->'||
3174: l_receipt_rec.locator_id||'->'||l_lot_number||'->'||l_receipt_rec.transaction_quantity||'->'||l_receipt_rec.transaction_uom||'->'||to_char(l_receipt_rec.transaction_date, 'DD-MON-YYYY HH24:MI:SS'));
3175: END IF;
3176: insert_interface_recs(p_mti_rec => l_receipt_rec,
3177: p_mtli_rec => l_receipt_lot_rec,
3181: END IF;
3182: x_subinventory := l_receipt_rec.subinventory_code;
3183: x_locator_id := l_receipt_rec.locator_id;
3184: x_lot_number := l_receipt_lot_rec.lot_number;
3185: IF (g_debug <= gme_debug.g_log_procedure) THEN
3186: gme_debug.put_line('End procedure '||l_api_name);
3187: END IF;
3188: EXCEPTION
3189: WHEN item_not_defined THEN
3182: x_subinventory := l_receipt_rec.subinventory_code;
3183: x_locator_id := l_receipt_rec.locator_id;
3184: x_lot_number := l_receipt_lot_rec.lot_number;
3185: IF (g_debug <= gme_debug.g_log_procedure) THEN
3186: gme_debug.put_line('End procedure '||l_api_name);
3187: END IF;
3188: EXCEPTION
3189: WHEN item_not_defined THEN
3190: gma_common_logging.gma_migration_central_log
3253: p_token3 => 'TXN_DATA',
3254: p_param3 => l_txn_data);
3255: x_return_status := FND_API.G_RET_STS_ERROR;
3256: WHEN OTHERS THEN
3257: IF (g_debug <= gme_debug.g_log_unexpected) THEN
3258: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
3259: END IF;
3260: l_txn_data := p_item_no||'->'||p_txn_rec.whse_code||'->'||p_txn_rec.location||'->'||p_txn_rec.lot_id||'->'||p_txn_rec.trans_qty||'->'||l_item_rec.primary_uom_code||'->'||to_char(p_txn_rec.trans_date, 'DD-MON-YYYY HH24:MI:SS');
3261: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3254: p_param3 => l_txn_data);
3255: x_return_status := FND_API.G_RET_STS_ERROR;
3256: WHEN OTHERS THEN
3257: IF (g_debug <= gme_debug.g_log_unexpected) THEN
3258: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
3259: END IF;
3260: l_txn_data := p_item_no||'->'||p_txn_rec.whse_code||'->'||p_txn_rec.location||'->'||p_txn_rec.lot_id||'->'||p_txn_rec.trans_qty||'->'||l_item_rec.primary_uom_code||'->'||to_char(p_txn_rec.trans_date, 'DD-MON-YYYY HH24:MI:SS');
3261: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3262: gma_common_logging.gma_migration_central_log
3280: l_mti_tbl gme_common_pvt.mtl_tran_int_tbl;
3281: l_mtli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
3282: l_api_name VARCHAR2(30) := 'insert_interface_recs';
3283: BEGIN
3284: IF (g_debug <= gme_debug.g_log_procedure) THEN
3285: gme_debug.put_line('Start procedure '||l_api_name);
3286: END IF;
3287: x_return_status := FND_API.G_RET_STS_SUCCESS;
3288: l_mti_tbl(1) := p_mti_rec;
3281: l_mtli_tbl gme_common_pvt.mtl_trans_lots_inter_tbl;
3282: l_api_name VARCHAR2(30) := 'insert_interface_recs';
3283: BEGIN
3284: IF (g_debug <= gme_debug.g_log_procedure) THEN
3285: gme_debug.put_line('Start procedure '||l_api_name);
3286: END IF;
3287: x_return_status := FND_API.G_RET_STS_SUCCESS;
3288: l_mti_tbl(1) := p_mti_rec;
3289: IF (p_mtli_rec.lot_number IS NOT NULL) THEN
3292: FORALL a IN 1..l_mti_tbl.COUNT
3293: INSERT INTO mtl_transactions_interface VALUES l_mti_tbl(a);
3294: FORALL b IN 1..l_mtli_tbl.COUNT
3295: INSERT INTO mtl_transaction_lots_interface VALUES l_mtli_tbl(b);
3296: IF (g_debug <= gme_debug.g_log_procedure) THEN
3297: gme_debug.put_line('End procedure '||l_api_name);
3298: END IF;
3299: EXCEPTION
3300: WHEN OTHERS THEN
3293: INSERT INTO mtl_transactions_interface VALUES l_mti_tbl(a);
3294: FORALL b IN 1..l_mtli_tbl.COUNT
3295: INSERT INTO mtl_transaction_lots_interface VALUES l_mtli_tbl(b);
3296: IF (g_debug <= gme_debug.g_log_procedure) THEN
3297: gme_debug.put_line('End procedure '||l_api_name);
3298: END IF;
3299: EXCEPTION
3300: WHEN OTHERS THEN
3301: IF (g_debug <= gme_debug.g_log_unexpected) THEN
3297: gme_debug.put_line('End procedure '||l_api_name);
3298: END IF;
3299: EXCEPTION
3300: WHEN OTHERS THEN
3301: IF (g_debug <= gme_debug.g_log_unexpected) THEN
3302: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
3303: END IF;
3304: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3305: gma_common_logging.gma_migration_central_log
3298: END IF;
3299: EXCEPTION
3300: WHEN OTHERS THEN
3301: IF (g_debug <= gme_debug.g_log_unexpected) THEN
3302: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
3303: END IF;
3304: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3305: gma_common_logging.gma_migration_central_log
3306: (p_run_id => g_migration_run_id,
3344: l_return_status VARCHAR2(1);
3345: l_api_name VARCHAR2(30) := 'close_steps';
3346: step_close_fail EXCEPTION;
3347: BEGIN
3348: IF (g_debug <= gme_debug.g_log_procedure) THEN
3349: gme_debug.put_line('Start procedure '||l_api_name);
3350: END IF;
3351: FOR get_steps IN Cur_get_steps LOOP
3352: BEGIN
3345: l_api_name VARCHAR2(30) := 'close_steps';
3346: step_close_fail EXCEPTION;
3347: BEGIN
3348: IF (g_debug <= gme_debug.g_log_procedure) THEN
3349: gme_debug.put_line('Start procedure '||l_api_name);
3350: END IF;
3351: FOR get_steps IN Cur_get_steps LOOP
3352: BEGIN
3353: l_step_rec.batchstep_id := get_steps.batchstep_id;
3388: p_param2 => get_steps.batchstep_no,
3389: p_token3 => 'MSG',
3390: p_param3 => l_msg_data);
3391: WHEN OTHERS THEN
3392: IF (g_debug <= gme_debug.g_log_unexpected) THEN
3393: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
3394: END IF;
3395: gma_common_logging.gma_migration_central_log
3396: (p_run_id => g_migration_run_id,
3389: p_token3 => 'MSG',
3390: p_param3 => l_msg_data);
3391: WHEN OTHERS THEN
3392: IF (g_debug <= gme_debug.g_log_unexpected) THEN
3393: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
3394: END IF;
3395: gma_common_logging.gma_migration_central_log
3396: (p_run_id => g_migration_run_id,
3397: p_log_level => fnd_log.level_error,
3406: p_token3 => 'MSG',
3407: p_param3 => SQLERRM);
3408: END;
3409: END LOOP;
3410: IF (g_debug <= gme_debug.g_log_procedure) THEN
3411: gme_debug.put_line('End procedure '||l_api_name);
3412: END IF;
3413: END close_steps;
3414:
3407: p_param3 => SQLERRM);
3408: END;
3409: END LOOP;
3410: IF (g_debug <= gme_debug.g_log_procedure) THEN
3411: gme_debug.put_line('End procedure '||l_api_name);
3412: END IF;
3413: END close_steps;
3414:
3415: PROCEDURE insert_lab_lots IS
3446: l_plot_in_rec gme_pending_product_lots%ROWTYPE;
3447: batch_fetch_err EXCEPTION;
3448: create_pp_lot_err EXCEPTION;
3449: BEGIN
3450: IF (g_debug <= gme_debug.g_log_procedure) THEN
3451: gme_debug.put_line('Start procedure '||l_api_name);
3452: END IF;
3453: FOR get_lots IN Cur_lab_lots LOOP
3454: BEGIN
3447: batch_fetch_err EXCEPTION;
3448: create_pp_lot_err EXCEPTION;
3449: BEGIN
3450: IF (g_debug <= gme_debug.g_log_procedure) THEN
3451: gme_debug.put_line('Start procedure '||l_api_name);
3452: END IF;
3453: FOR get_lots IN Cur_lab_lots LOOP
3454: BEGIN
3455: l_lot_number := NULL;
3552: p_token5 => 'NEW_DATA',
3553: p_param5 => l_new_data);
3554: ROLLBACK;
3555: WHEN OTHERS THEN
3556: IF (g_debug <= gme_debug.g_log_unexpected) THEN
3557: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
3558: END IF;
3559: l_txn_data := l_item_rec.segment1||'->'||get_lots.lot_id||'->'||get_lots.qty||'->'||get_lots.qty2;
3560: gma_common_logging.gma_migration_central_log
3553: p_param5 => l_new_data);
3554: ROLLBACK;
3555: WHEN OTHERS THEN
3556: IF (g_debug <= gme_debug.g_log_unexpected) THEN
3557: gme_debug.put_line('When others in '||l_api_name||' '||SQLERRM);
3558: END IF;
3559: l_txn_data := l_item_rec.segment1||'->'||get_lots.lot_id||'->'||get_lots.qty||'->'||get_lots.qty2;
3560: gma_common_logging.gma_migration_central_log
3561: (p_run_id => g_migration_run_id,
3571: p_param2 => SQLERRM);
3572: ROLLBACK;
3573: END;
3574: END LOOP;
3575: IF (g_debug <= gme_debug.g_log_procedure) THEN
3576: gme_debug.put_line('End procedure '||l_api_name);
3577: END IF;
3578: END insert_lab_lots;
3579: END gme_post_migration;
3572: ROLLBACK;
3573: END;
3574: END LOOP;
3575: IF (g_debug <= gme_debug.g_log_procedure) THEN
3576: gme_debug.put_line('End procedure '||l_api_name);
3577: END IF;
3578: END insert_lab_lots;
3579: END gme_post_migration;