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