[Home] [Help]
PACKAGE BODY: APPS.GMPRELAP
Source
1 PACKAGE BODY GMPRELAP AS
2 /* $Header: GMPRELAB.pls 120.24.12020000.2 2012/11/20 11:04:16 vkinduri ship $ */
3
4 PROCEDURE create_new_batch(
5 Errbuf Out Nocopy Varchar2, -- vkinduri bug # 15863070
6 retcode OUT NOCOPY VARCHAR2, -- vkinduri bug # 15863070
7 p_process_id IN NUMBER,
8 p_inventory_item_id IN NUMBER, -- For R12.0
9 p_item_no IN VARCHAR2, -- For R12.0
10 p_primary_uom_code IN VARCHAR2, -- For R12.0
11 p_organization_id IN NUMBER, -- For R12.0
12 p_batch_id IN NUMBER,
13 p_effectivity_id IN NUMBER,
14 p_plan_quantity IN NUMBER,
15 p_start_date IN DATE,
16 p_end_date IN DATE,
17 p_required_completion IN DATE, -- For R12.0
18 p_order_priority IN NUMBER, -- For R12.0
19 p_firmed_ind IN NUMBER, -- For R12.0
20 p_header_id IN NUMBER,
21 p_scheduling_method IN NUMBER,
22 p_batch_no IN VARCHAR2,
23 p_batch_type IN NUMBER,
24 p_rowid IN ROWID
25 ) ;
26
27 PROCEDURE validate_effectivities(
28 p_organization_id IN NUMBER,
29 p_inventory_item_id IN NUMBER,
30 p_effectivity_id IN NUMBER,
31 p_plan_quantity IN NUMBER,
32 p_batch_type IN NUMBER, /* B5259453 */
33 p_start_date IN DATE,
34 p_end_date IN DATE,
35 p_recipe_id OUT NOCOPY NUMBER, -- For R12.0
36 p_return OUT NOCOPY NUMBER); -- For R12.0
37
38 PROCEDURE scheduling_details_create(
39 p_batch_id IN NUMBER,
40 p_process_id IN NUMBER,
41 p_header_id IN NUMBER,
42 p_plan_start_date IN DATE,
43 p_plan_end_date IN DATE,
44 p_required_completion IN DATE, -- For R12.0
45 p_order_priority IN NUMBER, -- For R12.0
46 p_organization_id IN NUMBER, -- For R12.0
47 p_eff_id IN NUMBER);
48
49 PROCEDURE reschedule_batch(
50 p_process_id IN NUMBER,
51 p_organization_id IN NUMBER, -- For R12.0
52 p_plan_quantity IN NUMBER,
53 p_start_date IN DATE,
54 p_end_date IN DATE,
55 p_required_completion IN DATE, -- For R12.0
56 p_order_priority IN NUMBER, -- For R12.0
57 p_scheduling_method IN NUMBER,
58 p_batch_id IN NUMBER,
59 p_header_id IN NUMBER,
60 p_processed_ind IN NUMBER,
61 p_rowid IN ROWID
62 ) ;
63
64 PROCEDURE scheduling_details_resc(
65 p_batch_id IN NUMBER,
66 p_process_id IN NUMBER,
67 p_header_id IN NUMBER,
68 p_plan_start_date IN DATE,
69 p_plan_end_date IN DATE,
70 p_required_completion IN DATE, -- For R12.0
71 p_order_priority IN NUMBER, -- For R12.0
72 p_organization_id IN NUMBER, -- For R12.0
73 p_eff_id IN NUMBER,
74 return_status OUT NOCOPY NUMBER) ;
75
76 PROCEDURE cancel_batch(
77 p_process_id IN NUMBER,
78 p_organization_id IN NUMBER, -- For R12.0
79 p_start_date IN DATE,
80 p_end_date IN DATE,
81 p_batch_id IN NUMBER,
82 p_header_id IN NUMBER,
83 p_processed_ind IN NUMBER,
84 p_rowid IN ROWID
85 ) ;
86 /*
87 REM+========================================================================+
88 REM| Procedure: |
89 REM| implement_aps_plng_sugg |
90 REM| |
91 REM| DESCRIPTION: |
92 REM| |
93 REM| Calls the appropriate procedure depending on the Action Type |
94 REM| |
95 REM| History : |
96 REM| Sridhar 30-SEP-2003 Initial implementation |
97 REM| Rajesh Patangya 22-AUG-2005 Changes for Release 12.0 |
98 REM| Vpedarla Bug: 7258717 Added new parameter p_process_id |
99 REM+=======================================================================*/
100 PROCEDURE Implement_Aps_Plng_Sugg(
101 errbuf OUT NOCOPY VARCHAR2,
102 retcode OUT NOCOPY VARCHAR2,
103 p_organization_id IN NUMBER ,
104 p_process_id IN NUMBER,
105 p_fitem_no IN VARCHAR2,
106 p_titem_no IN VARCHAR2,
107 p_fdate IN VARCHAR2,
108 p_tdate IN VARCHAR2,
109 p_order_type IN NUMBER) IS
110
111 CURSOR Cur_gmp_output_tbl(
112 c_process_id NUMBER,
113 c_organization_id NUMBER,
114 c_fitem_no VARCHAR2,
115 c_titem_no VARCHAR2,
116 c_fdate DATE,
117 c_tdate DATE
118 ) IS
119 SELECT gmp.process_id,
120 gmp.inventory_item_id,
121 mtl.segment1,
122 mtl.primary_uom_code,
123 gmp.organization_id,
124 gmp.batch_id,
125 gmp.effectivity_id,
126 gmp.plan_quantity,
127 gmp.plan_start_date,
128 gmp.plan_end_date,
129 nvl( gmp.required_completion_date,gmp.plan_end_date) , -- Vpedarla Bug: 8348883
130 -- gmp.required_completion_date, /* APS K */
131 gmp.order_priority,
132 gmp.firm_flag,
133 gmp.action_type,
134 gmp.processed_ind,
135 gmp.header_id,
136 gmp.scheduling_method,
137 gmp.rowid
138 FROM gmp_aps_output_tbl gmp,
139 mtl_system_items mtl
140 WHERE gmp.processed_ind > 0
141 AND gmp.process_id = nvl(c_process_id,gmp.process_id) /* vpedarla bug: 7258717 */
142 AND gmp.inventory_item_id = mtl.inventory_item_id
143 AND gmp.organization_id = mtl.organization_id
144 AND gmp.organization_id = nvl(c_organization_id,gmp.organization_id)
145 AND mtl.segment1 >= nvl(c_fitem_no,mtl.segment1)
146 AND mtl.segment1 <= nvl(c_titem_no,mtl.segment1)
147 AND gmp.plan_start_date >= nvl(c_fdate,gmp.plan_start_date)
148 AND gmp.plan_start_date <= nvl(c_tdate,gmp.plan_start_date)
149 AND gmp.action_type = 1
150 UNION ALL
151 SELECT gmp.process_id,
152 gmp.inventory_item_id,
153 to_char(NULL),
154 to_char(NULL),
155 gmp.organization_id,
156 gmp.batch_id,
157 gmp.effectivity_id,
158 gmp.plan_quantity,
159 gmp.plan_start_date,
160 gmp.plan_end_date,
161 nvl( gmp.required_completion_date,gmp.plan_end_date) , -- Vpedarla Bug: 8348883
162 -- gmp.required_completion_date, /* APS K */
163 gmp.order_priority,
164 gmp.firm_flag,
165 gmp.action_type,
166 gmp.processed_ind,
167 gmp.header_id,
168 gmp.scheduling_method,
169 gmp.rowid
170 FROM gmp_aps_output_tbl gmp
171 WHERE gmp.processed_ind > 0
172 AND gmp.process_id = nvl(c_process_id,gmp.process_id) /* vpedarla bug: 7258717 */
173 AND gmp.organization_id = nvl(c_organization_id,gmp.organization_id)
174 AND gmp.plan_start_date >= nvl(c_fdate,gmp.plan_start_date)
175 AND gmp.plan_start_date <= nvl(c_tdate,gmp.plan_start_date)
176 AND gmp.action_type <> 1 ;
177
178 TYPE get_data_typ is RECORD
179 (
180 process_id NUMBER,
181 inventory_item_id NUMBER,
182 item_no VARCHAR2(240),
183 primary_uom_code VARCHAR2(3),
184 organization_id NUMBER,
185 batch_id NUMBER,
186 effectivity_id NUMBER,
187 plan_quantity NUMBER,
188 plan_start_date DATE,
189 plan_end_date DATE,
190 required_completion_date DATE,
191 order_priority NUMBER,
192 firmed_ind NUMBER,
193 action_type NUMBER,
194 processed_ind NUMBER,
195 header_id NUMBER,
196 scheduling_method NUMBER,
197 rowid VARCHAR2(25)
198 );
199
200 get_data_rec get_data_typ;
201 v_batch_no VARCHAR2(32);
202 v_batch_type NUMBER(5);
203 v_errbuf VARCHAR2(2000);
204 v_retcode VARCHAR2(15);
205 X_assignment_type NUMBER;
206 X_count NUMBER ;
207 NO_ROWS EXCEPTION;
208 MANUAL_DOC_TYPE EXCEPTION;
209 CHECK_DATE EXCEPTION;
210 l_from_date DATE;
211 l_to_date DATE;
212 l_updt_offsets NUMBER; /*B5148802 - sowsubra - Activity offset updation*/
213
214 BEGIN
215 X_count := 0;
216 retcode := '0';
217
218 /* B7609461 Rajesh Patangya 11-DEC-2008 */
219 gmd_p_fs_context.set_additional_attr;
220
221 l_updt_offsets := nvl(FND_PROFILE.VALUE('GMP_UPDATE_ACTIVITY_OFFSETS'),0);/*B5148802*/
222 /* B7530107 Reset everything for GME so that
223 for every record it will go and set the org, calendar information */
224 gme_common_pvt.g_setup_done := FALSE ;
225
226 FND_FILE.PUT_LINE ( FND_FILE.LOG,'===== Input Parameters ===== ');
227 FND_FILE.PUT_LINE ( FND_FILE.LOG,'p_process_id = ' || p_process_id);
228 FND_FILE.PUT_LINE ( FND_FILE.LOG,'p_organization_id = ' || p_organization_id);
229 FND_FILE.PUT_LINE ( FND_FILE.LOG,'p_fitem_no = ' || p_fitem_no);
230 FND_FILE.PUT_LINE ( FND_FILE.LOG,'p_titem_no = ' || p_titem_no);
231 FND_FILE.PUT_LINE ( FND_FILE.LOG,'p_fdate = ' || p_fdate);
232 FND_FILE.PUT_LINE ( FND_FILE.LOG,'p_tdate = ' || p_tdate);
233
234 /* { */
235 IF p_tdate IS NOT NULL AND p_fdate IS NOT NULL THEN
236
237 /* B5857203, Rajesh Patangya ONDEMAND:ASCP/OPM BATCH RELEASE FAILURE * ORA-06550 */
238
239 l_from_date := fnd_date.canonical_to_date(p_fdate);
240 l_to_date := fnd_date.canonical_to_date(p_tdate);
241
242 -- l_from_date := fnd_date.charDT_to_date(p_fdate);
243 -- l_to_date := fnd_date.charDT_to_date(p_tdate);
244
245 FND_FILE.PUT_LINE ( FND_FILE.LOG,'l_from_date = ' || l_from_date);
246 FND_FILE.PUT_LINE ( FND_FILE.LOG,'l_to_date = ' || l_to_date);
247
248 IF l_updt_offsets = 1 THEN
249 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Profile Update activity offset is set');
250 ELSE
251 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Profile Update activity offset is not set');
252 END IF;
253
254 IF (l_to_date < l_from_date) THEN
255 RAISE CHECK_DATE;
256 END IF;
257
258 ELSE
259 l_from_date := NULL;
260 l_to_date := NULL;
261 END IF;
262
263 OPEN Cur_gmp_output_tbl(p_process_id , p_organization_id,p_fitem_no,p_titem_no,
264 l_from_date,l_to_date);
265 LOOP
266 FETCH Cur_gmp_output_tbl INTO get_data_rec;
267 EXIT WHEN Cur_gmp_output_tbl%NOTFOUND;
268 IF get_data_rec.action_type = 1 THEN /* New Batch */
269
270 IF ( p_order_type = 1) THEN
271 -- FPO
272 SELECT FPO_DOC_NUMBERING
273 INTO X_assignment_type
274 FROM gme_parameters
275 WHERE organization_id = get_data_rec.organization_id ;
276
277 v_batch_type := 10 ;
278 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Implementing FPOs ');
279 ELSE
280 -- Batch
281 SELECT BATCH_DOC_NUMBERING
282 INTO X_assignment_type
283 FROM gme_parameters
284 WHERE organization_id = get_data_rec.organization_id ;
285
286 v_batch_type := 0 ;
287 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Implementing Batches ');
288 END IF;
289
290 IF (X_assignment_type = 1) THEN -- Manual Numbering
291 RAISE MANUAL_DOC_TYPE;
292 END IF;
293
294 FND_MESSAGE.SET_NAME('GMA','SY_$NEW');
295 v_batch_no := FND_MESSAGE.GET; -- Manual Batch Number
296 /* Start Processing */
297 create_new_batch(
298 Errbuf, -- vkinduri bug # 15863070
299 retcode, -- vkinduri bug # 15863070
300 get_data_rec.process_id,
301 get_data_rec.inventory_item_id,
302 get_data_rec.item_no,
303 get_data_rec.primary_uom_code,
304 get_data_rec.organization_id,
305 get_data_rec.batch_id,
306 get_data_rec.effectivity_id,
307 get_data_rec.plan_quantity,
308 get_data_rec.plan_start_date,
309 get_data_rec.plan_end_date,
310 get_data_rec.required_completion_date,
311 get_data_rec.order_priority,
312 get_data_rec.firmed_ind,
313 get_data_rec.header_id,
314 get_data_rec.scheduling_method,
315 v_batch_no,
316 v_batch_type,
317 get_data_rec.rowid
318 );
319 /*B5148802 - sowsubra - Activity offset updation*/
320 IF l_updt_offsets = 1 THEN
321 gmp_aps_writer.update_activity_offsets (get_data_rec.batch_id);
322 END IF;
323
324 ELSIF get_data_rec.action_type = 3 THEN /* Reschedule */
325 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Implementing Reschedule Batches ');
326
327 reschedule_batch(
328 get_data_rec.process_id,
329 get_data_rec.organization_id,
330 get_data_rec.plan_quantity,
331 get_data_rec.plan_start_date,
332 get_data_rec.plan_end_date,
333 get_data_rec.required_completion_date,
334 get_data_rec.order_priority,
335 get_data_rec.scheduling_method,
336 get_data_rec.batch_id,
337 get_data_rec.header_id,
338 get_data_rec.processed_ind,
339 get_data_rec.rowid
340 );
341
342 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Batch Id - '|| get_data_rec.batch_id);
343
344 /* B5897392 Rajesh Patangya firm Flag */
345 IF get_data_rec.firmed_ind = 1 THEN
346 UPDATE GME_BATCH_HEADER
347 SET firmed_ind = get_data_rec.firmed_ind
348 WHERE batch_id = get_data_rec.batch_id ;
349 --batch id value passed directly
350 END IF;
351
352 /*B5148802 - sowsubra - Activity offset updation*/
353 IF l_updt_offsets = 1 THEN
354 gmp_aps_writer.update_activity_offsets (get_data_rec.batch_id);
355 END IF;
356
357 ELSIF get_data_rec.action_type = -1 THEN /* Cancel */
358
359 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Implementing Cancel Batches ');
360
361 cancel_batch(
362 get_data_rec.process_id,
363 get_data_rec.organization_id,
364 get_data_rec.plan_start_date,
365 get_data_rec.plan_end_date,
366 get_data_rec.batch_id,
367 get_data_rec.header_id,
368 get_data_rec.processed_ind,
369 get_data_rec.rowid
370 );
371 END IF;
372
373 X_count := X_count + 1;
374 END LOOP;
375 /* } */
376 CLOSE Cur_gmp_output_tbl;
377
378 COMMIT;
379
380 /* The following IF condition is to check if the Cursor gave any rows
381 for the Imput data passed */
382 IF X_count = 0
383 THEN
384 RAISE NO_ROWS;
385 END IF;
386
387 EXCEPTION
388 WHEN MANUAL_DOC_TYPE THEN
389 errbuf := sqlerrm;
390 FND_MESSAGE.SET_NAME('GMP','SY_INVALID_MANUAL_DOC');
391 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
392 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cannot convert to Batch/FPO, Manual Doc Type '||sqlerrm);
393 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL)) THEN
394 NULL;
395 END IF;
396 retcode := '3';
397
398 WHEN CHECK_DATE THEN
399 errbuf := sqlerrm;
400 FND_MESSAGE.SET_NAME('GMP','MR_INV_CALENDAR_RANGE');
401 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
402 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL)) THEN
403 NULL;
404 END IF;
405 retcode := '3';
406
407 WHEN NO_ROWS THEN
408 errbuf := sqlerrm;
409 FND_MESSAGE.SET_NAME('GMA','SY_NO_ROWS_SELECTED');
410 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
411 --IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL)) THEN
412 /*Bug:6408133 GMPRELAP COMPLETES WITH WARNING WHEN THERE ARE NO ORDERS TO BE CONVERTED */
413
414 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('NORMAL',NULL)) THEN
415
416 NULL;
417 END IF;
418 retcode := '3';
419 WHEN OTHERS THEN
420 errbuf := sqlerrm;
421 FND_FILE.PUT_LINE(FND_FILE.LOG,' Error in Implementing APS Plng Suggestions '||sqlerrm);
422 retcode := '2';
423
424 END Implement_Aps_Plng_Sugg;
425
426 /*
427 REM+==========================================================================+
428 REM| |
429 REM|PROCEDURE NAME create_new_batch |
430 REM| |
431 REM|DESCRIPTION Each record is processed to create batch or Reschedule|
432 REM| or Cancel a Batch |
433 REM| The following Return Status can occur while creating |
434 REM| a Batch |
435 REM| S - Success |
436 REM| E - Error |
437 REM| U - Unexpected Error |
438 REM| V - Inventory shortage exists |
439 REM| |
440 REM| MODIFICATION HISTORY |
441 REM| 09/30/03 Sridhar Gidugu ----- created |
442 REM| Rajesh Patangya 22-AUG-2005 Changes for Release 12.0 |
443 REM+=========================================================================*/
444 PROCEDURE create_new_batch(
445 Errbuf Out Nocopy Varchar2, -- vkinduri bug # 15863070
446 retcode OUT NOCOPY VARCHAR2, -- vkinduri bug # 15863070
447 p_process_id IN NUMBER,
448 p_inventory_item_id IN NUMBER, -- For R12.0
449 p_item_no IN VARCHAR2, -- For R12.0
450 p_primary_uom_code IN VARCHAR2, -- For R12.0
451 p_organization_id IN NUMBER, -- For R12.0
452 p_batch_id IN NUMBER,
453 p_effectivity_id IN NUMBER,
454 p_plan_quantity IN NUMBER,
455 p_start_date IN DATE,
456 p_end_date IN DATE,
457 p_required_completion IN DATE, -- For R12.0
458 p_order_priority IN NUMBER, -- For R12.0
459 p_firmed_ind IN NUMBER, -- For R12.0
460 p_header_id IN NUMBER,
461 p_scheduling_method IN NUMBER,
462 p_batch_no IN VARCHAR2,
463 p_batch_type IN NUMBER,
464 p_rowid IN ROWID
465 ) IS
466
467 -- Contiguity Override changes
468 Cursor calendar_associated(l_organization_id NUMBER) IS
469 SELECT calendar_code,organization_code FROM mtl_parameters
470 WHERE organization_id = l_organization_id ;
471
472 l_gme_batch_header GME_BATCH_HEADER%ROWTYPE;
473 x_gme_batch_header2 GME_BATCH_HEADER%ROWTYPE;
474 x_message_count NUMBER;
475 x_message_list VARCHAR2(2000);
476 x_return_status VARCHAR2(1);
477 CREATE_BATCH_FAILED EXCEPTION;
478 ERROR_MESSAGE EXCEPTION;
479 GMP_CHECK_EFFECTIVITY EXCEPTION;
480 GMP_SHOP_NON_WKG_START EXCEPTION;
481 GMP_SHOP_NON_WKG_END EXCEPTION;
482 l_action_code VARCHAR2(50);
483 x_use_workday_cal VARCHAR2(1) ;
484 l_contiguity_override NUMBER ;
485 x_contiguity_override VARCHAR2(1) ;
486 l_org_code VARCHAR2(10) ;
487 l_return_status VARCHAR2(1) ;
488 x_exception_tbl gme_common_pvt.exceptions_tab;
489 l_inv_shortage BOOLEAN;
490 l_item_no VARCHAR2(240);
491 l_cal_code VARCHAR2(10);
492 l_is_associated NUMBER ;
493 v_batch_no VARCHAR2(32);
494 v_start_date DATE;
495 v_end_date DATE;
496 v_due_date DATE;
497 v_batch_id NUMBER(10);
498 l_recipe_id NUMBER;
499 l_return NUMBER;
500 v_orig_plan_cmplt_date DATE ;
501 x_data VARCHAR2(250) ;
502 l_dummy_cnt NUMBER ;
503 l_profile VARCHAR2(1) ;
504 l_end_date DATE; -- Bug:6265867 Kbanddyo
505 u_user_id NUMBER;
506 l_sum_all_prod_lines VARCHAR2(1) ;
507 l_fixed_process_loss_ind VARCHAR2(1) ; /* B8290677 */
508
509 -- Bug: 8625112 Vpedarla
510 CURSOR Cur_get_item_uoms IS
511 SELECT msi.PRIMARY_UOM_CODE , gmd.detail_uom from
512 (select PRIMARY_UOM_CODE, inventory_item_id , organization_id from mtl_system_items) msi,
513 (select inventory_item_id , recipe_validity_rule_id , DETAIL_UOM from gmd_recipe_validity_rules ) gmd
514 where msi.organization_id = p_organization_id
515 AND msi.inventory_item_id = p_inventory_item_id
516 AND msi.inventory_item_id = gmd.inventory_item_id
517 AND gmd.recipe_validity_rule_id = p_effectivity_id ;
518
519 -- Bug: 8625112 Vpedarla
520 l_rec_uom VARCHAR2(10);
521 l_prim_uom VARCHAR2(10);
522 l_new_plan_qty NUMBER;
523
524 BEGIN
525 x_use_workday_cal := 'T';
526 x_contiguity_override := 'F';
527 l_is_associated := 0;
528 l_contiguity_override := 1;
529 l_item_no := p_item_no ;
530 l_profile := 'N';
531 /* B6994378, Rajesh Patangya GME does need intialization parameters */
532 gme_common_pvt.g_setup_done := FALSE ;
533
534
535 -- Bug: 8625112 Vpedarla
536 l_rec_uom := NULL;
537 l_prim_uom := NULL;
538 l_new_plan_qty := 0;
539
540
541 /* B6994378, Rajesh Patangya GME does need intialization parameters */
542 u_user_id := FND_PROFILE.VALUE('user_id') ;
543 fnd_profile.initialize (u_user_id);
544 -- Line below required if formula security is used. It sets the security context.
545 fnd_global.apps_initialize (user_id => u_user_id,
546 resp_id => NULL,
547 resp_appl_id => NULL
548 );
549 fnd_profile.put ('AFLOG_LEVEL', '1');
550 /* B7577478 Rajesh Patangya 18-NOV-2008
551 'S' -- if multiple product lines for the same items are present in formula details, APS
552 suggest the suggestion based on the product line quantity and hence GME should
553 be using the quantity passed as total quantity for the product (which is the
554 minimum line number of the product in formula details) and not
555 adding quantities for all the coproducts.
556 'A' -- if only one line for the product is present, altough formula have multiple co-products
557 then GME should be using the quantity passed as total quantity to create the batch
558 */
559 l_sum_all_prod_lines := 'S' ;
560
561 FND_FILE.PUT_LINE(FND_FILE.LOG,'New btach Processing for Group ID = ' || p_process_id || ' Header ID = ' || p_header_id);
562
563 OPEN calendar_associated(p_organization_id);
564 FETCH calendar_associated INTO l_cal_code,l_org_code;
565 CLOSE calendar_associated;
566
567 IF l_cal_code IS NOT NULL THEN
568 l_is_associated := 1;
569 END IF;
570
571 IF l_is_associated = 1 THEN
572 l_profile := NVL(FND_PROFILE.VALUE('GMP_USE_MANUFACTURING_CAL'),'N');
573 ELSE
574 l_profile := 'N';
575 END IF;
576
577 -- For R12.0
578 IF p_required_completion IS NOT NULL THEN
579 l_gme_batch_header.due_date := p_required_completion;
580 ELSE
581 l_gme_batch_header.due_date := p_end_date;
582 END IF;
583
584 l_gme_batch_header.organization_id := p_organization_id ;
585 l_gme_batch_header.plan_cmplt_date := p_end_date;
586 l_gme_batch_header.batch_type := p_batch_type;
587 l_gme_batch_header.laboratory_ind := 0 ;
588 l_gme_batch_header.update_inventory_ind := 0 ;
589 l_gme_batch_header.gl_posted_ind := 0;
590 l_action_code := 'PRODUCT';
591 l_gme_batch_header.text_code := NULL;
592 l_gme_batch_header.order_priority := p_order_priority ;
593 v_orig_plan_cmplt_date := p_end_date ;
594 l_gme_batch_header.firmed_ind := p_firmed_ind ;
595
596
597 /* B8290677 consider Fixed Process_loss */
598 -- Autorelease consider it only when user will set the parameter to 'all batches'
599 SELECT fixed_process_loss_ind
600 INTO l_fixed_process_loss_ind
601 FROM gme_parameters
602 WHERE organization_id = p_organization_id ;
603
604 IF l_fixed_process_loss_ind = 1 THEN
605 l_gme_batch_header.fixed_process_loss_applied := 'N' ;
606 ELSIF l_fixed_process_loss_ind = 2 THEN
607 l_gme_batch_header.fixed_process_loss_applied := 'Y' ;
608 ELSE
609 l_gme_batch_header.fixed_process_loss_applied := NULL ;
610 END IF;
611
612 /* Assuming the fmeff_id is coming from APS Engine */
613 l_recipe_id := 0 ;
614 l_return := -1;
615
616 FND_FILE.PUT_LINE(FND_FILE.LOG,'Before validate Effectivity ');
617 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_organization_id '||p_organization_id);
618 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_inventory_item_id '||p_inventory_item_id);
619 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_effectivity_id '||p_effectivity_id);
620 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_plan_quantity '||p_plan_quantity);
621 FND_FILE.PUT_LINE(FND_FILE.LOG,'Batch type '||p_batch_type);
622 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_start_date '||p_start_date);
623 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_end_date '||p_end_date);
624
625 -- Bug: 8625112 Vpedarla
626 IF p_effectivity_id is not null then
627 OPEN Cur_get_item_uoms;
628 FETCH Cur_get_item_uoms INTO l_prim_uom,l_rec_uom ;
629 CLOSE Cur_get_item_uoms;
630
631
632 IF (l_prim_uom <> l_rec_uom ) THEN
633 FND_FILE.PUT_LINE(FND_FILE.LOG,' Item UOM '||l_prim_uom||' different from validity rule uom '||l_rec_uom);
634 l_new_plan_qty := inv_convert.inv_um_convert(
635 p_inventory_item_id ,
636 NULL,
637 p_organization_id,
638 5,
639 p_plan_quantity,
640 l_prim_uom,
641 l_rec_uom,
642 NULL,
643 NULL);
644 FND_FILE.PUT_LINE(FND_FILE.LOG,'New plan quantity in validity rule uom -'||l_new_plan_qty );
645 ELSE
646 FND_FILE.PUT_LINE(FND_FILE.LOG,' No effectivity id ');
647 l_new_plan_qty := p_plan_quantity ;
648 END IF;
649 END IF;
650
651 validate_effectivities(p_organization_id,
652 p_inventory_item_id,
653 p_effectivity_id, l_new_plan_qty, -- bug: 8625112
654 p_batch_type, /* B5259453 */
655 p_start_date,p_end_date,
656 l_recipe_id,l_return
657 );
658
659 FND_FILE.PUT_LINE(FND_FILE.LOG,'After validate Effectivity ');
660 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_recipe_id '||l_recipe_id);
661 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_return '||l_return);
662
663 IF l_return <> 0 THEN
664 l_gme_batch_header.RECIPE_VALIDITY_RULE_ID := p_effectivity_id;
665 ELSE
666 RAISE GMP_CHECK_EFFECTIVITY;
667 END IF;
668
669 -- Contiguity Override changes, always there for batch
670 GMD_RECIPE_FETCH_PUB.FETCH_CONTIGUOUS_IND(
671 p_recipe_id => l_recipe_id
672 ,p_orgn_id => p_organization_id
673 ,p_recipe_validity_rule_id =>p_effectivity_id
674 ,x_contiguous_ind => l_contiguity_override
675 ,x_return_status => l_return_status);
676
677 IF l_contiguity_override = 0 THEN
678 x_contiguity_override := 'T' ;
679 ELSE
680 x_contiguity_override := 'F' ;
681 END IF;
682
683 FND_FILE.PUT_LINE(FND_FILE.LOG,'Calendar Code '||l_cal_code);
684 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start Date '||to_char(p_start_date,'MM/DD/YYYY HH24:MI:SS'));
685 /*
686 IF p_start_date IS NOT NULL THEN
687 IF l_profile = 'Y' THEN
688 IF NOT gmp_calendar_api.is_working_daytime (
689 1,
690 TRUE,
691 l_cal_code,
692 p_start_date,
693 0,
694 x_return_status ) THEN
695 RAISE GMP_SHOP_NON_WKG_START ;
696 END IF;
697 END IF;
698
699 END IF;
700 */
701
702 FND_FILE.PUT_LINE(FND_FILE.LOG,'End Date '||to_char(p_end_date,'MM/DD/YYYY HH24:MI:SS'));
703 IF p_end_date IS NOT NULL THEN
704 IF l_profile = 'Y' THEN
705 IF NOT gmp_calendar_api.is_working_daytime (
706 1,
707 TRUE,
708 l_cal_code,
709 p_end_date,
710 1, /* B3615325 , this should be 1 instead of 0 */
711 x_return_status ) THEN
712
713 --Bug:6265867(kbanddyo)START B5378109 of Teva, For Unconstrained plan suggestions, if Suggested due date
714 -- is not a working Daytime, find the nearest working time
715 IF (nvl(p_scheduling_method,0) <> 1) THEN
716 l_end_date := NULL ;
717 x_return_status := 'E' ;
718 gmp_calendar_api.get_nearest_workdaytime(
719 1,
720 TRUE ,
721 l_cal_code,
722 p_end_date,
723 0,
724 l_end_date,
725 x_return_status
726 ) ;
727
728 IF x_return_status = 'S' THEN
729 l_gme_batch_header.plan_cmplt_date := l_end_date;
730 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Nearest End Date = '||to_char(l_end_date,'MM/DD/YYYY HH24:MI:SS'));
731 ELSE
732 RAISE GMP_SHOP_NON_WKG_END ;
733 END IF;
734 END IF;
735 -- ELSE
736 -- RAISE GMP_SHOP_NON_WKG_END ; bug:6788788 removed the exception in the else clause
737 -- Bug:6265867 END Teva changes Ends
738 END IF;
739 END IF;
740 END IF;
741 -- End of Contiguity Override changes
742
743 select DECODE(l_profile,'N','F','Y','T') into x_use_workday_cal from dual;
744
745 -- Call the GME API to create a Batch
746 gme_api_pub.create_batch(
747 p_api_version => 2.0
748 ,p_validation_level => 100
749 ,p_init_msg_list => 'T'
750 ,p_commit => 'T'
751 ,x_message_count => x_message_count
752 ,x_message_list => x_message_list
753 ,x_return_status => x_return_status
754 ,p_org_code => l_org_code
755 ,p_batch_header_rec => l_gme_batch_header
756 ,x_batch_header_rec => x_gme_batch_header2
757 ,p_batch_size => p_plan_quantity
758 ,p_batch_size_uom => p_primary_uom_code -- 3 character UOM_code
759 ,p_creation_mode => l_action_code
760 ,p_ignore_qty_below_cap => 'T'
761 ,p_use_workday_cal => x_use_workday_cal
762 ,p_contiguity_override => x_contiguity_override
763 ,p_use_least_cost_validity_rule => 'F'
764 ,p_sum_all_prod_lines => l_sum_all_prod_lines /* B7530107 */
765 ,x_exception_material_tbl => x_exception_tbl) ;
766
767
768 IF (x_return_status = 'C') THEN
769 x_contiguity_override := 'T' ;
770 x_message_list := NULL;
771 x_return_status := NULL ;
772
773 FND_MESSAGE.SET_NAME('GME','GME_SHOP_NOT_ONE_CONT');
774 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
775
776 gme_api_pub.create_batch(
777 p_api_version => 2.0
778 ,p_validation_level => 100
779 ,p_init_msg_list => 'T'
780 ,p_commit => 'T'
781 ,x_message_count => x_message_count
782 ,x_message_list => x_message_list
783 ,x_return_status => x_return_status
784 ,p_org_code => l_org_code
785 ,p_batch_header_rec => l_gme_batch_header
786 ,x_batch_header_rec => x_gme_batch_header2
787 ,p_batch_size => p_plan_quantity
788 ,p_batch_size_uom => p_primary_uom_code -- 3 char UOM
789 ,p_creation_mode => l_action_code
790 ,p_ignore_qty_below_cap => 'T'
791 ,p_use_workday_cal => x_use_workday_cal
792 ,p_contiguity_override => x_contiguity_override
793 ,p_use_least_cost_validity_rule => 'F'
794 ,p_sum_all_prod_lines => l_sum_all_prod_lines /* B7530107 */
795 ,x_exception_material_tbl => x_exception_tbl) ;
796
797 END IF;
798
799 IF (x_return_Status NOT IN ('E','U')) THEN
800
801 IF p_batch_type = 10 THEN
802 FND_MESSAGE.SET_NAME('GME','GME_FPO_CREATED');
803 FND_MESSAGE.SET_TOKEN('FPO_NO',x_gme_batch_header2.batch_no);
804 ELSE
805 FND_MESSAGE.SET_NAME('GME','GME_BATCH_CREATED');
806 FND_MESSAGE.SET_TOKEN('BATCH_NO',x_gme_batch_header2.batch_no);
807 END IF;
808 FND_FILE.PUT_LINE(FND_FILE.LOG,'Item Number '||l_item_no||' - '||FND_MESSAGE.GET);
809 v_batch_no := x_gme_batch_header2.batch_no;
810 v_start_date := x_GME_BATCH_HEADER2.plan_start_date;
811 v_end_date := x_GME_BATCH_HEADER2.PLAN_CMPLT_DATE;
812 v_due_date := x_GME_BATCH_HEADER2.due_date;
813 v_batch_id := x_gme_batch_header2.batch_id;
814 END IF;
815 IF (x_return_status in ('E','U')) THEN
816 -- Errors
817 RAISE ERROR_MESSAGE;
818 ELSIF (x_return_status = 'S') THEN
819 -- Production Batch was sucessfully inserted.
820 v_batch_no := x_gme_batch_header2.batch_no;
821 v_start_date := x_GME_BATCH_HEADER2.plan_start_date;
822 v_end_date := x_GME_BATCH_HEADER2.PLAN_CMPLT_DATE;
823 v_due_date := x_GME_BATCH_HEADER2.due_date;
824 IF p_batch_type = 10 THEN
825 FND_MESSAGE.SET_NAME('GME','GME_FPO_CREATED');
826
827 FND_MESSAGE.SET_TOKEN('FPO_NO',x_gme_batch_header2.batch_no);
828 ELSE
829
830 FND_MESSAGE.SET_NAME('GME','GME_BATCH_CREATED');
831 FND_MESSAGE.SET_TOKEN('BATCH_NO',x_gme_batch_header2.batch_no);
832 END IF;
833 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
834
835 ELSIF (x_return_status = 'V') THEN
836 -- Inventory shortage, Production Batch was sucessfully inserte.
837
838 v_batch_no := x_gme_batch_header2.batch_no;
839 FND_MESSAGE.SET_NAME('GME','GME_INV_SHORTAGES');
840 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET||'-'||v_batch_no);
841 END IF;
842
843 UPDATE gmp_aps_output_tbl
844 SET processed_ind = 0 ,
845 batch_id = v_batch_id
846 WHERE rowid = p_rowid
847 AND processed_ind > 0
848 AND header_id = p_header_id;
849
850 IF (nvl(p_scheduling_method,0) = 1) THEN
851
852 UPDATE gmp_aps_output_dtl
853 SET wip_entity_id = v_batch_id
854 WHERE parent_header_id = p_header_id
855 AND group_id = p_process_id ;
856
857 scheduling_details_create(v_batch_id , /* B3590089 */
858 p_process_id ,
859 p_header_id ,
860 p_start_date ,
861 v_orig_plan_cmplt_date ,
862 p_required_completion , -- For R12.0
863 p_order_priority , -- For R12.0
864 p_organization_id , -- For R12.0
865 p_effectivity_id
866 );
867 END IF ;
868
869 EXCEPTION
870 WHEN GMP_CHECK_EFFECTIVITY THEN
871 -- B4610275 changed the message to include Group ID
872 FND_FILE.PUT_LINE ( FND_FILE.LOG,'GMP Item Effectivity for Group Id = '
873 || p_process_id || ' is Invalid for Item_no(id) = '||l_item_no ||
874 '(' ||p_inventory_item_id ||')' );
875 FND_MESSAGE.SET_NAME('GME','GME_INVALID_VALIDITY_RULE_PROD');
876 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
877
878
879 --Bug#6414610 KBANDDYO uncommented following code
880 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL)) THEN
881 NULL;
882 END IF;
883 -- vkinduri bug # 15863070 Start
884 Errbuf := 'GMP Item Effectivity for Group Id = ' || P_Process_Id || ' is Invalid for Item_no(id) = '||L_Item_No ||
885 '(' ||P_Inventory_Item_Id ||')';
886 retcode := 1; /* Warning */
887 -- vkinduri bug # 15863070 End
888 WHEN GMP_SHOP_NON_WKG_START THEN
889 FND_FILE.PUT_LINE(FND_FILE.LOG,'Message from GMP ');
890 FND_MESSAGE.SET_NAME('GME','GME_SHOP_NON_WKG');
891 FND_MESSAGE.SET_TOKEN('PDATE',to_char(p_start_date,'MM/DD/YYYY HH24:MI:SS'));
892 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
893
894 WHEN GMP_SHOP_NON_WKG_END THEN
895 FND_FILE.PUT_LINE(FND_FILE.LOG,'Message from GMP 2 ');
896 FND_MESSAGE.SET_NAME('GME','GME_SHOP_NON_WKG');
897 FND_MESSAGE.SET_TOKEN('PDATE',to_char(p_end_date,'MM/DD/YYYY HH24:MI:SS'));
898 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
899
900 WHEN ERROR_MESSAGE THEN
901 FND_FILE.PUT_LINE(FND_FILE.LOG,' Error - Status is E or U '||sqlerrm);
902 FOR i in 1..fnd_msg_pub.count_msg LOOP
903 FND_MSG_PUB.Get(
904 p_msg_index => i,
905 p_data => x_data,
906 p_encoded => FND_API.G_FALSE,
907 p_msg_index_out => l_dummy_cnt);
908
909 FND_FILE.PUT_LINE(FND_FILE.LOG,':'|| i || ':' || x_data );
910 END LOOP ;
911
912 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL)) THEN
913 NULL;
914 END IF;
915 WHEN OTHERS THEN
916 FND_FILE.PUT_LINE(FND_FILE.LOG,' Error in Create New Batch '||sqlerrm);
917
918 END create_new_batch;
919
920 /*
921 REM+========================================================================+
922 REM| |
923 REM| PROCEDURE_NAME validate_effectivities |
924 REM| |
925 REM| DESCRIPTION Validates the existence of Effectivity Id |
926 REM| |
927 REM| MODIFICATION HISTORY |
928 REM| 09/09/03 Sridhar Gidugu ----- created |
929 REM| Rajesh Patangya 22-AUG-2005 Changes for Release 12.0 |
930 REM+=======================================================================*/
931 PROCEDURE validate_effectivities(
932 p_organization_id IN NUMBER,
933 p_inventory_item_id IN NUMBER,
934 p_effectivity_id IN NUMBER,
935 p_plan_quantity IN NUMBER,
936 p_batch_type IN NUMBER, /* B5259453 */
937 p_start_date IN DATE,
938 p_end_date IN DATE,
939 p_recipe_id OUT NOCOPY NUMBER, -- For R12.0
940 p_return OUT NOCOPY NUMBER) IS -- For R12.0
941
942 -- B5259453, FPO can use planning and production validity rules
943 CURSOR Cur_get_eff_id IS
944 SELECT gr.recipe_id, count(1)
945 FROM gmd_recipes_b gr,
946 gmd_recipe_validity_rules grv,
947 gmd_status_b gs
948 WHERE grv.validity_rule_status in ('700','900')
949 AND gr.recipe_id = grv.recipe_id
950 AND grv.validity_rule_status = gs.status_code
951 AND gs.status_type in ('700','900')
952 AND grv.delete_mark = 0
953 AND (( p_batch_type = 0
954 AND grv.recipe_use = 0 ) -- Production Use only
955 OR ( p_batch_type = 10
956 AND grv.recipe_use IN (0,1) )) -- Planning/Production Use only
957 AND grv.inventory_item_id = p_inventory_item_id
958 AND nvl(grv.organization_id,p_organization_id) = p_organization_id
959 AND grv.recipe_validity_rule_id = p_effectivity_id
960 AND grv.min_qty <= nvl(p_plan_quantity, grv.min_qty )
961 AND grv.max_qty >= nvl( p_plan_quantity , grv.max_qty )
962 AND trunc(grv.start_date) <= trunc(p_start_date) -- Falls within a data range
963 -- AND NVL(grv.end_date,(sysdate+8000)) >= trunc(p_end_date)
964 -- Bug: 8467054 Vpedarla commented the above where condition.
965 GROUP BY gr.recipe_id;
966
967 l_count NUMBER ;
968 l_recipe_id NUMBER ;
969
970 BEGIN
971 l_count := 0 ;
972 l_recipe_id := 0 ;
973
974 OPEN Cur_get_eff_id;
975 FETCH Cur_get_eff_id INTO l_recipe_id,l_count ;
976 CLOSE Cur_get_eff_id;
977
978 p_recipe_id := l_recipe_id ;
979 p_return := l_count ;
980
981
982 EXCEPTION
983 WHEN OTHERS THEN
984 FND_FILE.PUT_LINE(FND_FILE.LOG,' Error Validate Effectivities '||sqlerrm);
985 END validate_effectivities;
986
987 /*
988 REM+========================================================================+
989 REM| PROCEDURE NAME SCHEDULING_DETAILS_CREATE |
990 REM| |
991 REM| DESCRIPTION Process the APS generated scheduling details to |
992 REM| update GME |
993 REM| |
994 REM| MODIFICATION HISTORY |
995 REM| 10/01/03 Sridhar Gidugu ----- created |
996 REM| Rajesh Patangya 22-AUG-2005 Changes for Release 12.0 |
997 REM+=======================================================================*/
998 PROCEDURE scheduling_details_create(
999 p_batch_id IN NUMBER,
1000 p_process_id IN NUMBER,
1001 p_header_id IN NUMBER,
1002 p_plan_start_date IN DATE,
1003 p_plan_end_date IN DATE,
1004 p_required_completion IN DATE, -- For R12.0
1005 p_order_priority IN NUMBER, -- For R12.0
1006 p_organization_id IN NUMBER, -- For R12.0
1007 p_eff_id IN NUMBER) IS
1008
1009 err_msg VARCHAR2(4000) ;
1010 l_msg_data VARCHAR2(4000) ;
1011 ret_code NUMBER ;
1012
1013 BEGIN
1014 err_msg := '' ;
1015 ret_code := -1;
1016
1017 FND_FILE.PUT_LINE(FND_FILE.LOG,'Calling GMP APS Writer program ');
1018 FND_FILE.PUT_LINE(FND_FILE.LOG,'Batch Id '||p_batch_id);
1019 FND_FILE.PUT_LINE(FND_FILE.LOG,'process Id '||p_process_id);
1020 FND_FILE.PUT_LINE(FND_FILE.LOG,'Header Id '||p_header_id);
1021 FND_FILE.PUT_LINE(FND_FILE.LOG,'Start Date '||p_plan_start_date);
1022 FND_FILE.PUT_LINE(FND_FILE.LOG,'End Date '||p_plan_end_date);
1023 FND_FILE.PUT_LINE(FND_FILE.LOG,'Req completion = '||p_required_completion);
1024 FND_FILE.PUT_LINE(FND_FILE.LOG,'Priority = '||p_order_priority);
1025 FND_FILE.PUT_LINE(FND_FILE.LOG,'Orgn Id = '||p_organization_id);
1026 FND_FILE.PUT_LINE(FND_FILE.LOG,'Eff Id = '||p_eff_id);
1027
1028 GMP_APS_WRITER.main_process(p_batch_id,
1029 p_process_id,
1030 p_header_id,
1031 p_plan_start_date,
1032 p_plan_end_date,
1033 p_required_completion,
1034 p_order_priority,
1035 p_organization_id,
1036 p_eff_id,
1037 1, -- Action_type
1038 sysdate, -- Creation_date
1039 FND_PROFILE.VALUE('user_id'),
1040 FND_PROFILE.VALUE('user_id'),
1041 err_msg,
1042 ret_code) ;
1043
1044 -- We shall add a check for return code also if negative means an error
1045 -- Then show a message
1046
1047 IF ret_code <> 0 THEN
1048 l_msg_data := FND_MSG_PUB.GET(
1049 p_msg_index =>FND_MSG_PUB.Count_msg,
1050 p_encoded => 'F') ;
1051
1052 IF l_msg_data is NOT NULL THEN
1053 FND_FILE.PUT_LINE(FND_FILE.LOG,'Return from the main process is '||l_msg_data);
1054 END IF ;
1055 END IF ;
1056
1057 EXCEPTION
1058 WHEN OTHERS THEN
1059 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in process scheduling details create '||p_batch_id||'-'||sqlerrm);
1060 END scheduling_details_create ;
1061
1062 /*
1063 REM+=========================================================================+
1064 REM| |
1065 REM| PROCEDURE NAME reschedule_batch |
1066 REM| |
1067 REM| DESCRIPTION Each record is processed to Reschedule a Batch |
1068 REM| |
1069 REM| MODIFICATION HISTORY |
1070 REM| 10/01/03 Sridhar Gidugu ----- created |
1071 REM| Rajesh Patangya 22-AUG-2005 Changes for Release 12.0 |
1072 REM+========================================================================*/
1073 PROCEDURE reschedule_batch(
1074 p_process_id IN NUMBER,
1075 p_organization_id IN NUMBER, -- For R12.0
1076 p_plan_quantity IN NUMBER,
1077 p_start_date IN DATE,
1078 p_end_date IN DATE,
1079 p_required_completion IN DATE, -- For R12.0
1080 p_order_priority IN NUMBER, -- For R12.0
1081 p_scheduling_method IN NUMBER,
1082 p_batch_id IN NUMBER,
1083 p_header_id IN NUMBER,
1084 p_processed_ind IN NUMBER,
1085 p_rowid IN ROWID
1086 ) IS
1087
1088 CURSOR Cur_get_batch_data IS
1089 SELECT a.batch_no, a.batch_type, a.batch_status,
1090 a.plan_start_date, a.plan_cmplt_date,
1091 a.recipe_validity_rule_id, b.recipe_id
1092 FROM gme_batch_header a,
1093 gmd_recipe_validity_rules b
1094 WHERE a.batch_id = p_batch_id
1095 AND a.organization_id = p_organization_id
1096 AND a.recipe_validity_rule_id = b.recipe_validity_rule_id
1097 AND b.delete_mark = 0
1098 AND b.recipe_use IN (0,1) ;
1099
1100 -- Contiguity Override changes
1101 Cursor calendar_associated(l_organization_id NUMBER) IS
1102 SELECT calendar_code, organization_code FROM mtl_parameters
1103 WHERE organization_id = l_organization_id ;
1104
1105 x_message_count NUMBER;
1106 x_message_list VARCHAR2(2000);
1107 x_return_status VARCHAR2(2000);
1108 x_batch_header gme_batch_header%ROWTYPE;
1109 x_use_workday_cal VARCHAR2(1) ;
1110 l_contiguity_override NUMBER ;
1111 x_contiguity_override VARCHAR2(1) ;
1112 l_return_status VARCHAR2(2000);
1113 l_gme_batch_header GME_BATCH_HEADER%ROWTYPE;
1114 l_gme_batch_header2 GME_BATCH_HEADER%ROWTYPE;
1115 t_ret_code NUMBER ;
1116 x_decision NUMBER ;
1117 c_batch_id NUMBER(10);
1118 l_cal_code VARCHAR2(10);
1119 l_is_associated NUMBER ;
1120 v_scheduling_method NUMBER ;
1121 l_org_code VARCHAR2(10);
1122 l_batch_no VARCHAR2(32);
1123 l_batch_type NUMBER(5);
1124 l_batch_status NUMBER(5);
1125 l_old_pst DATE;
1126 l_plan_start_date DATE;
1127 l_plan_end_date DATE;
1128 l_recipe_validity_rule_id NUMBER;
1129 l_recipe_id NUMBER;
1130 expct_cmplt_date DATE;
1131 x_data VARCHAR2(250) ;
1132 l_dummy_cnt NUMBER ;
1133 ERROR_MESSAGE EXCEPTION;
1134 l_profile VARCHAR2(1) ;
1135 GMP_SHOP_NON_WKG_END EXCEPTION; -- Bug:6265867 Kbanddyo
1136 l_end_date DATE; -- Bug:6265867 Kbanddyo
1137
1138 l_update_due_date NUMBER; -- Vpedarla Bug: 8348883
1139
1140
1141 -- Bug: 8663941 Vpedarla
1142 CURSOR Cur_get_item_uoms(eff_id NUMBER) IS
1143 SELECT msi.CONCATENATED_SEGMENTS , msi.inventory_item_id from
1144 (select PRIMARY_UOM_CODE, inventory_item_id , organization_id , CONCATENATED_SEGMENTS from mtl_system_items_kfv) msi,
1145 (select inventory_item_id , recipe_validity_rule_id , DETAIL_UOM from gmd_recipe_validity_rules ) gmd
1146 where msi.organization_id = p_organization_id
1147 AND msi.inventory_item_id = gmd.inventory_item_id
1148 AND gmd.recipe_validity_rule_id = eff_id ;
1149
1150 -- Bug: 8663941 Vpedarla
1151 l_new_plan_qty NUMBER;
1152 l_val_recipe_id NUMBER;
1153 l_val_return NUMBER;
1154 GMP_CHECK_EFFECTIVITY EXCEPTION;
1155 l_item_no VARCHAR2(250);
1156 l_item_id NUMBER;
1157
1158 BEGIN
1159 x_use_workday_cal := 'T';
1160 x_contiguity_override := 'F';
1161 l_contiguity_override := 1;
1162 l_is_associated := 0;
1163 x_decision := NULL;
1164 t_ret_code := -1 ;
1165 l_profile := 'N';
1166 l_item_no := ' ';
1167 l_item_id := 0;
1168
1169 -- Bug: 8262503 Vpedarla
1170 v_scheduling_method := p_scheduling_method;
1171
1172 -- Vpedarla Bug: 8348883
1173 l_update_due_date := nvl(FND_PROFILE.VALUE('GMP:UPDATE_DUE_DATE_FOR_BATCHES_RESCHEDULE_BY_ASCP'),0) ;
1174
1175 /* { */
1176 OPEN Cur_get_batch_data;
1177 FETCH Cur_get_batch_data INTO
1178 l_batch_no, l_batch_type, l_batch_status,
1179 l_old_pst, expct_cmplt_date,
1180 l_recipe_validity_rule_id, l_recipe_id ;
1181 CLOSE Cur_get_batch_data;
1182
1183 FND_FILE.PUT_LINE(FND_FILE.LOG,'Now Rescheduling Batch = ' || l_batch_no);
1184
1185 /* Calculate plan_start_date, as APS is not sending in
1186 wip_job_schedule_interface */
1187 l_plan_start_Date := p_start_date ;
1188 l_plan_end_date := p_end_date ;
1189
1190 IF l_plan_start_date is NULL then
1191 l_plan_start_Date := l_old_pst + (l_plan_end_date - expct_cmplt_date) ;
1192 END IF;
1193
1194 OPEN calendar_associated(p_organization_id);
1195 FETCH calendar_associated INTO l_cal_code,l_org_code;
1196 CLOSE calendar_associated;
1197
1198 IF l_cal_code IS NOT NULL THEN
1199 l_is_associated := 1;
1200 END IF;
1201
1202 IF l_is_associated = 1 THEN
1203 l_profile := NVL(FND_PROFILE.VALUE('GMP_USE_MANUFACTURING_CAL'),'N');
1204 ELSE
1205 l_profile := 'N';
1206 END IF;
1207
1208 IF l_batch_status in (1,2) THEN
1209 IF ((l_batch_status = 2) AND (l_old_pst > l_plan_start_date))
1210 THEN
1211 -- Do not reschedule via APS or GME way
1212 FND_MESSAGE.SET_NAME('GMP','GMP_WIP_RESCHEDULE_IN_NA');
1213 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
1214 v_scheduling_method := -1 ;
1215 END IF;
1216
1217 -- Bug: 8663941 Vpedarla
1218 IF l_recipe_validity_rule_id is not null then
1219 OPEN Cur_get_item_uoms(l_recipe_validity_rule_id);
1220 FETCH Cur_get_item_uoms INTO l_item_no, l_item_id ;
1221 CLOSE Cur_get_item_uoms;
1222
1223 FND_FILE.PUT_LINE(FND_FILE.LOG,'Before validate Effectivity ');
1224 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_organization_id '||p_organization_id);
1225 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_effectivity_id '||l_recipe_validity_rule_id);
1226 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_inventory_item_id '||l_item_id);
1227 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_start_date '||p_start_date);
1228 FND_FILE.PUT_LINE(FND_FILE.LOG,'p_end_date '||p_end_date);
1229
1230 validate_effectivities(p_organization_id,
1231 l_item_id,
1232 l_recipe_validity_rule_id, to_number(NULL) , -- bug: 8625112
1233 l_batch_type, /* B5259453 */
1234 p_start_date,p_end_date,
1235 l_val_recipe_id,l_val_return
1236 );
1237
1238 FND_FILE.PUT_LINE(FND_FILE.LOG,'After validate Effectivity ');
1239 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_val_recipe_id '||l_val_recipe_id);
1240 FND_FILE.PUT_LINE(FND_FILE.LOG,'l_val_return '||l_val_return);
1241
1242 IF l_val_return <> 0 THEN
1243 l_gme_batch_header.RECIPE_VALIDITY_RULE_ID := l_recipe_validity_rule_id;
1244 ELSE
1245 RAISE GMP_CHECK_EFFECTIVITY;
1246 END IF;
1247 END IF;
1248
1249 IF nvl(p_scheduling_method,2) = 1 THEN
1250 IF l_update_due_date = 0 THEN -- Vpedarla bug: 8348883
1251 scheduling_details_resc(
1252 p_batch_id,
1253 p_process_id,
1254 p_header_id,
1255 p_start_date,
1256 p_end_date,
1257 NULL , -- For R12.0
1258 p_order_priority , -- For R12.0
1259 p_organization_id , -- For R12.0
1260 l_recipe_validity_rule_id,
1261 t_ret_code
1262 );
1263 ELSE
1264 scheduling_details_resc(
1265 p_batch_id,
1266 p_process_id,
1267 p_header_id,
1268 p_start_date,
1269 p_end_date,
1270 p_required_completion , -- For R12.0
1271 p_order_priority , -- For R12.0
1272 p_organization_id , -- For R12.0
1273 l_recipe_validity_rule_id,
1274 t_ret_code
1275 );
1276 END IF;
1277
1278 IF t_ret_code < 0 THEN
1279 FND_MESSAGE.SET_NAME('GMP','GMP_RESCHEDULE_CONTINUE');
1280 -- x_decision := FND_MESSAGE.QUESTION('YES','NO',NULL);
1281 /* Purposely making the Reschedule to Contine */
1282 x_decision := 1;
1283 IF x_decision = 1 THEN
1284 v_scheduling_method := 2;
1285 END IF;
1286 END IF; /* End if for ret_code */
1287
1288 END IF;
1289 -- The start and end dates will not have correct date stamps
1290 -- We also have to check with APS team to see if we get correct end date
1291
1292 IF v_scheduling_method = 2 THEN
1293
1294 IF (l_batch_type = 0) AND (l_profile = 'Y') THEN
1295 GMD_RECIPE_FETCH_PUB.FETCH_CONTIGUOUS_IND(
1296 p_recipe_id => l_recipe_id
1297 ,p_orgn_id => p_organization_id
1298 ,p_recipe_validity_rule_id => l_recipe_validity_rule_id
1299 ,x_contiguous_ind => l_contiguity_override
1300 ,x_return_status => l_return_status);
1301
1302 IF l_contiguity_override = 0 THEN
1303 x_contiguity_override := 'T' ;
1304 ELSE
1305 x_contiguity_override := 'F' ;
1306 END IF;
1307 END IF;
1308
1309 -- Bug: 8262503 Vpedarla commented below code.
1310 /* IF p_start_date IS NOT NULL THEN
1311 IF l_profile = 'Y' THEN
1312 IF NOT gmp_calendar_api.is_working_daytime (
1313 1,
1314 TRUE,
1315 l_cal_code,
1316 p_start_date,
1317 0,
1318 x_return_status ) THEN
1319 FND_MESSAGE.SET_NAME('GME','GME_SHOP_NON_WKG');
1320 FND_MESSAGE.SET_TOKEN('PDATE',to_char(p_start_date,'MM/DD/YYYY HH24:MI:SS'));
1321 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
1322 END IF;
1323 END IF;
1324 END IF; */
1325
1326 IF p_end_date IS NOT NULL THEN
1327 IF l_profile = 'Y' THEN
1328 IF NOT gmp_calendar_api.is_working_daytime (
1329 1,
1330 TRUE,
1331 l_cal_code,
1332 p_end_date,
1333 0,
1334 x_return_status ) THEN
1335
1336 -- Bug: 6265867 (kbanddyo)START - B5378109 Teva, For Unconstrained plan suggestions, if Suggested due date
1337 -- is not a working Daytime, find the nearest working time
1338 l_end_date := NULL ;
1339 x_return_status := 'E' ;
1340 gmp_calendar_api.get_nearest_workdaytime(
1341 1,
1342 TRUE ,
1343 l_cal_code,
1344 p_end_date,
1345 0,
1346 l_end_date,
1347 x_return_status
1348 ) ;
1349
1350 FND_MESSAGE.SET_NAME('GME','GME_SHOP_NON_WKG');
1351 FND_MESSAGE.SET_TOKEN('PDATE',to_char(p_end_date,'MM/DD/YYYY HH24:MI:SS'));
1352 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
1353
1354 IF x_return_status = 'S' THEN
1355 l_gme_batch_header.plan_cmplt_date := l_end_date;
1356 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Nearest End Date = '||to_char(l_end_date,'MM/DD/YYYY HH24:MI:SS'));
1357 ELSE
1358 RAISE GMP_SHOP_NON_WKG_END ;
1359 END IF;
1360 ----Bug: 6265867 Teva changes Ends
1361
1362 FND_MESSAGE.SET_NAME('GME','GME_SHOP_NON_WKG');
1363 FND_MESSAGE.SET_TOKEN('PDATE',to_char(p_end_date,'MM/DD/YYYY HH24:MI:SS'));
1364 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
1365 END IF;
1366 END IF;
1367 END IF;
1368
1369 SELECT DECODE(l_profile,'N','F','Y','T') INTO x_use_workday_cal FROM dual;
1370 -- End of Contiguity Override changes
1371
1372 -- Batch will be rescheduled via GME mechanism
1373 l_gme_batch_header.batch_id := p_batch_id ;
1374 l_gme_batch_header.organization_id := p_organization_id ;
1375 l_gme_batch_header.RECIPE_VALIDITY_RULE_ID := l_recipe_validity_rule_id;
1376 l_gme_batch_header.batch_type := l_batch_type; -- vkinduri, Added batch_type as per bug # 12603013
1377 -- Bug: 8262503 Vpedarla commented below line.
1378 -- l_gme_batch_header.plan_start_date := p_start_date;
1379 l_gme_batch_header.plan_cmplt_date := p_end_date;
1380
1381 gme_api_pub.reschedule_batch(
1382 p_api_version => 2
1383 ,p_validation_level => 100
1384 ,p_init_msg_list => 'T'
1385 ,p_commit => 'T'
1386 ,p_org_code => l_org_code
1387 ,p_use_workday_cal => x_use_workday_cal
1388 ,p_contiguity_override => x_contiguity_override
1389 ,x_message_count => x_message_count
1390 ,x_message_list => x_message_list
1391 ,x_return_status => x_return_status
1392 ,p_batch_header_rec => l_gme_batch_header
1393 ,x_batch_header_rec => l_gme_batch_header2 );
1394
1395 IF x_return_status = 'C' THEN
1396
1397 FND_MESSAGE.SET_NAME('GME','GME_SHOP_NOT_ONE_CONT');
1398 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
1399
1400 gme_api_pub.reschedule_batch(
1401 p_api_version => 2
1402 ,p_validation_level => 100
1403 ,p_init_msg_list => 'T'
1404 ,p_commit => 'T'
1405 ,p_org_code => l_org_code
1406 ,p_use_workday_cal => x_use_workday_cal
1407 ,p_contiguity_override => 'T'
1408 ,x_message_count => x_message_count
1409 ,x_message_list => x_message_list
1410 ,x_return_status => x_return_status
1411 ,p_batch_header_rec => l_gme_batch_header
1412 ,x_batch_header_rec => x_batch_header );
1413
1414 END IF; /* for return status C */
1415
1416 IF X_return_status <> 'S' THEN
1417 RAISE ERROR_MESSAGE;
1418 ELSE
1419 FND_MESSAGE.SET_NAME('GME','GME_RESCHEDULE');
1420 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET||' Return Success');
1421
1422 -- Vpedarla Bug: 8348883 start
1423 IF l_update_due_date = 1 THEN
1424 FND_FILE.PUT_LINE ( FND_FILE.LOG,'due date update');
1425 UPDATE gme_batch_header
1426 SET due_date = p_required_completion
1427 WHERE BATCH_ID = l_gme_batch_header.batch_id;
1428 END IF;
1429 -- Vpedarla Bug: 8348883 end
1430
1431 UPDATE gmp_aps_output_tbl
1432 SET processed_ind = 0
1433 WHERE rowid = p_rowid
1434 AND header_id = p_header_id
1435 AND processed_ind > 0
1436 AND batch_id = p_batch_id ;
1437
1438 END IF; /* End if for GME Return Status */
1439
1440 END IF ; /* End of second scheduling method */
1441
1442 ELSE
1443 FND_MESSAGE.SET_NAME('GMP','MR_USE_PM_RESP_TO_RESCHEDULE');
1444 FND_MESSAGE.SET_TOKEN('BATCH_NO',l_batch_no);
1445 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
1446 END IF; /* } */
1447
1448 EXCEPTION
1449 WHEN GMP_CHECK_EFFECTIVITY THEN
1450 -- B4610275 changed the message to include Group ID
1451 FND_FILE.PUT_LINE ( FND_FILE.LOG,'GMP Item Effectivity for Group Id = '
1452 || p_process_id || ' is Invalid for Item_no(id) = '||l_item_no ||
1453 '(' ||l_item_id ||')' );
1454 FND_MESSAGE.SET_NAME('GME','GME_INVALID_VALIDITY_RULE_PROD');
1455 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
1456
1457
1458 --Bug#6414610 KBANDDYO uncommented following code
1459 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL)) THEN
1460 NULL;
1461 END IF;
1462 WHEN ERROR_MESSAGE THEN
1463 FND_FILE.PUT_LINE(FND_FILE.LOG,' Error in Reschedule Batch - Status Not in S '||p_batch_id||'-'||sqlerrm);
1464
1465 FOR i in 1..fnd_msg_pub.count_msg LOOP
1466 FND_MSG_PUB.Get(
1467 p_msg_index => i,
1468 p_data => x_data,
1469 p_encoded => FND_API.G_FALSE,
1470 p_msg_index_out => l_dummy_cnt);
1471
1472 FND_FILE.PUT_LINE(FND_FILE.LOG,':'|| i || ':' || x_data );
1473 END LOOP ;
1474
1475 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL)) THEN
1476 NULL;
1477 END IF;
1478 WHEN OTHERS THEN
1479 FND_FILE.PUT_LINE(FND_FILE.LOG,' Error in Reschedule Batch '||sqlerrm);
1480 END reschedule_batch;
1481
1482 /*
1483 REM+=======================================================================+
1484 REM| |
1485 REM| PROCEDURE NAME SCHEDULING_DETAILS_RESC |
1486 REM| |
1487 REM| DESCRIPTION Process the APS generated scheduling details to |
1488 REM| update GME |
1489 REM| |
1490 REM| MODIFICATION HISTORY |
1491 REM| 10/01/03 Sridhar Gidugu ----- created |
1492 REM| Rajesh Patangya 22-AUG-2005 Changes for Release 12.0 |
1493 REM+======================================================================*/
1494 PROCEDURE scheduling_details_resc(
1495 p_batch_id IN NUMBER,
1496 p_process_id IN NUMBER,
1497 p_header_id IN NUMBER,
1498 p_plan_start_date IN DATE,
1499 p_plan_end_date IN DATE,
1500 p_required_completion IN DATE, -- For R12.0
1501 p_order_priority IN NUMBER, -- For R12.0
1502 p_organization_id IN NUMBER, -- For R12.0
1503 p_eff_id IN NUMBER,
1504 return_status OUT NOCOPY NUMBER) IS
1505
1506 err_msg VARCHAR2(3000) ;
1507 l_msg_data VARCHAR2(3000) ;
1508 ret_code NUMBER ;
1509
1510 BEGIN
1511 ret_code := -1 ;
1512 err_msg := '';
1513 gmp_aps_writer.main_process(p_batch_id,
1514 p_process_id,
1515 p_header_id,
1516 p_plan_start_date,
1517 p_plan_end_date,
1518 p_required_completion, -- Vpedarla Bug: 8348883
1519 p_order_priority,
1520 p_organization_id,
1521 p_eff_id,
1522 3,
1523 sysdate,
1524 FND_PROFILE.VALUE('USER_ID'),
1525 FND_PROFILE.VALUE('USER_ID'),
1526 err_msg,
1527 ret_code) ;
1528 -- We shall add a check for return code also if negative means an error
1529 -- Then show a message
1530
1531 IF ret_code <> 0 THEN
1532
1533 l_msg_data := FND_MSG_PUB.GET(
1534 p_msg_index =>FND_MSG_PUB.Count_msg,
1535 p_encoded => 'F') ;
1536 IF l_msg_data is NOT NULL THEN
1537 FND_FILE.PUT_LINE(FND_FILE.LOG,'::'||to_char(ret_code) ||'::'||l_msg_data);
1538 END IF ;
1539
1540 END IF ;
1541 return_status := ret_code ;
1542
1543 EXCEPTION
1544 WHEN OTHERS THEN
1545 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in process scheduling details Reschedule '||p_batch_id||'-'||sqlerrm);
1546 END scheduling_details_resc;
1547
1548 /*
1549 REM+========================================================================+
1550 REM| |
1551 REM| PROCEDURE NAME cancel_batch |
1552 REM| |
1553 REM| DESCRIPTION Each record is processed to Cancel the batch |
1554 REM| |
1555 REM| MODIFICATION HISTORY |
1556 REM| 10/01/03 Sridhar Gidugu ----- created |
1557 REM| Rajesh Patangya 22-AUG-2005 Changes for Release 12.0 |
1558 REM+=======================================================================*/
1559 PROCEDURE cancel_batch(
1560 p_process_id IN NUMBER,
1561 p_organization_id IN NUMBER, -- For R12.0
1562 p_start_date IN DATE,
1563 p_end_date IN DATE,
1564 p_batch_id IN NUMBER,
1565 p_header_id IN NUMBER,
1566 p_processed_ind IN NUMBER,
1567 p_rowid IN ROWID
1568 ) IS
1569
1570 CURSOR get_batch_status IS
1571 SELECT gbh.batch_no, gbh.batch_status,
1572 gbh.recipe_validity_rule_id,
1573 mp.organization_code,
1574 gbh.batch_type
1575 FROM gme_batch_header gbh, mtl_parameters mp
1576 WHERE gbh.batch_id = p_batch_id
1577 AND gbh.organization_id = mp.organization_id
1578 AND mp.organization_id = p_organization_id;
1579
1580 x_message_count NUMBER;
1581 x_message_list VARCHAR2(2000);
1582 x_return_status VARCHAR2(2000);
1583 X_msg_count NUMBER;
1584 l_gme_batch_header GME_BATCH_HEADER%ROWTYPE;
1585 l_gme_batch_header2 GME_BATCH_HEADER%ROWTYPE;
1586 l_batch_status NUMBER(5);
1587 l_batch_no VARCHAR2(32);
1588 l_effectivity_id NUMBER(10) ;
1589 l_org_code VARCHAR2(10);
1590 x_data VARCHAR2(250) ;
1591 l_dummy_cnt NUMBER ;
1592 ERROR_MESSAGE EXCEPTION;
1593 l_batch_type NUMBER;
1594 BEGIN
1595 /* { */
1596 OPEN get_batch_status;
1597 FETCH get_batch_status INTO l_batch_no,l_batch_status,
1598 l_effectivity_id, l_org_code, l_batch_type;
1599 CLOSE get_batch_status;
1600 FND_FILE.PUT_LINE ( FND_FILE.LOG,'From cancel batch: ');
1601 FND_FILE.PUT_LINE ( FND_FILE.LOG,'l_batch_no = ' || l_batch_no);
1602 FND_FILE.PUT_LINE ( FND_FILE.LOG,'l_batch_status = ' || l_batch_status);
1603 FND_FILE.PUT_LINE ( FND_FILE.LOG,'l_effectivity_id = ' || l_effectivity_id);
1604 FND_FILE.PUT_LINE ( FND_FILE.LOG,'l_org_code = ' || l_org_code);
1605 FND_FILE.PUT_LINE ( FND_FILE.LOG,'l_batch_type = ' || l_batch_type);
1606
1607 IF l_batch_status <> 1 THEN
1608 FND_MESSAGE.SET_NAME('GMP','MR_INVALID_BATCH_STATUS_USE_GM');
1609 FND_MESSAGE.SET_TOKEN('BATCH_NO',l_batch_no);
1610 FND_FILE.PUT_LINE(FND_FILE.LOG,'-'||FND_MESSAGE.GET);
1611 ELSE
1612 -- Batch will be rescheduled via GME mechanism
1613 l_gme_batch_header.batch_id := p_batch_id ;
1614 l_gme_batch_header.organization_id := p_organization_id;
1615 l_gme_batch_header.RECIPE_VALIDITY_RULE_ID := l_effectivity_id ;
1616 l_gme_batch_header.batch_type := l_batch_type;
1617
1618 GME_API_PUB.cancel_batch
1619 ( p_api_version => 2.0,
1620 p_validation_level => 100,
1621 p_init_msg_list => 'T',
1622 p_commit => 'T',
1623 x_message_count => x_message_count,
1624 x_message_list => x_message_list,
1625 x_return_status => X_return_status,
1626 p_org_code => l_org_code,
1627 p_batch_header_rec => l_gme_batch_header,
1628 x_batch_header_rec => l_gme_batch_header2);
1629
1630 IF X_return_status <> 'S' THEN
1631 RAISE ERROR_MESSAGE;
1632 ELSE
1633 FND_MESSAGE.SET_NAME('GME','GME_API_BATCH_CANCELLED');
1634 FND_FILE.PUT_LINE(FND_FILE.LOG,'- '||FND_MESSAGE.GET||l_batch_no);
1635
1636 UPDATE gmp_aps_output_tbl
1637 SET processed_ind = 0
1638 WHERE rowid = p_rowid
1639 AND processed_ind > 0 ;
1640
1641 END IF; /* End if for GME Return Status */
1642
1643 END IF; /* } */
1644
1645 EXCEPTION
1646 WHEN ERROR_MESSAGE THEN
1647 FND_FILE.PUT_LINE(FND_FILE.LOG,' Error in Cancel Batch - Status Not in S '||p_batch_id||'-'||sqlerrm);
1648
1649 FOR i in 1..fnd_msg_pub.count_msg LOOP
1650 FND_MSG_PUB.Get(
1651 p_msg_index => i,
1652 p_data => x_data,
1653 p_encoded => FND_API.G_FALSE,
1654 p_msg_index_out => l_dummy_cnt);
1655
1656 FND_FILE.PUT_LINE(FND_FILE.LOG,':'|| i || ':' || x_data );
1657 END LOOP ;
1658
1659 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL)) THEN
1660 NULL;
1661 END IF;
1662 WHEN OTHERS THEN
1663 FND_FILE.PUT_LINE(FND_FILE.LOG,' Error in Cancel Batch '||sqlerrm);
1664 END cancel_batch;
1665
1666 END GMPRELAP;
1667