[Home] [Help]
PACKAGE BODY: APPS.MRP_SELECT_ALL_FOR_RELEASE_PUB
Source
1 PACKAGE BODY MRP_SELECT_ALL_FOR_RELEASE_PUB AS
2 /* $Header: MRPSARPB.pls 120.3.12000000.2 2007/02/20 09:13:38 arrsubra ship $ */
3
4 MRP_CALENDAR_RET_DATES NUMBER := 0;
5 mrp_calendar_cal_code VARCHAR2(10) := '17438gdjh';
6 mrp_calendar_excep_set NUMBER := -23453;
7 var_calendar_code VARCHAR2(10);
8 var_exception_set_id NUMBER;
9 max_date DATE;
10 var_return_date DATE;
11 min_date DATE;
12 min_period_date DATE;
13 min_week_date DATE;
14 max_week_date DATE;
15 min_seq_num NUMBER;
16 max_period_date DATE;
17 max_seq_num NUMBER;
18 min_week_seq_num NUMBER;
19 max_week_seq_num NUMBER;
20 var_prev_seq_num NUMBER;
21 var_prev_seq_num2 NUMBER;
22 var_prev_work_day DATE;
23 var_return_number NUMBER;
24 var_prev_work_day2 DATE;
25
26 TYPE_DAILY_BUCKET CONSTANT NUMBER := 1;
27 TYPE_WEEKLY_BUCKET CONSTANT NUMBER := 2;
28 TYPE_MONTHLY_BUCKET CONSTANT NUMBER := 3;
29
30 PROCEDURE Update_Implement_Attrib(p_where_clause IN VARCHAR2,
31 p_employee_id IN NUMBER,
32 p_demand_class IN VARCHAR2,
33 p_def_job_class IN VARCHAR2,
34 p_def_firm_jobs IN VARCHAR2,
35 p_total_rows OUT NOCOPY NUMBER,
36 p_succ_rows OUT NOCOPY NUMBER,
37 p_error_rows OUT NOCOPY NUMBER
38 ) IS
39 l_where_clause VARCHAR2(32767) := NULL;
40 l_demand_class VARCHAR2(30) := NULL;
41 l_def_job_class VARCHAR2(10) := NULL;
42 l_def_firm_jobs VARCHAR2(1) := 'N';
43
44
45 l_employee_id NUMBER;
46 l_no_rec_rows NUMBER;
47 l_no_rep_rows NUMBER;
48 l_error_rows NUMBER;
49 l_total_rows NUMBER;
50 l_succ_rows NUMBER;
51
52 BEGIN
53
54 l_where_clause := p_where_clause;
55 l_employee_id := p_employee_id;
56 l_demand_class := p_demand_class;
57 l_def_job_class := p_def_job_class;
58 l_def_firm_jobs := p_def_firm_jobs;
59 l_total_rows := 0;
60 l_error_rows := 0;
61 l_succ_rows := 0;
62
63 /* --------------------------------------------------+
64 | Call function to select rows which will be included|
65 | for update in mrp_recommendations and |
66 | mrp_sugg_rep_schedules. |
67 +----------------------------------------------------*/
68
69 l_no_rec_rows := select_rec_rows(l_where_clause);
70 l_no_rep_rows := select_rep_rows(l_where_clause);
71
72 /*----------------------------------------------------+
73 | First call function to update error messages for |
74 | all records. |
75 +----------------------------------------------------*/
76
77
78 update_pre_process_errors(l_no_rec_rows,
79 l_no_rep_rows);
80
81 /*---------------------------------------------------+
82 | Process rows in mrp_recommendations. |
83 +----------------------------------------------------*/
84
85
86 if(l_no_rec_rows > 0) then
87
88
89 /*---------------------------------------------------+
90 | Update Attributes in MRP_RECOMMENDATIONS |
91 +----------------------------------------------------*/
92
93 update_recom_attrib(
94 l_employee_id,
95 l_demand_class,
96 l_def_job_class,
97 l_def_firm_jobs);
98
99
100
101 end if;
102
103 /*---------------------------------------------------+
104 | Process rows in mrp_sugg_rep_schedules |
105 +----------------------------------------------------*/
106
107 if(l_no_rep_rows > 0) then
108
109 /*---------------------------------------------------+
110 | First call function to update error messages for |
111 | all records. |
112 +----------------------------------------------------*/
113
114
115
116
117 /*---------------------------------------------------+
118 | Update Attributes in MRP_SUGG_REP_SCHEDULES |
119 +----------------------------------------------------*/
120
121 update_rep_attrib(l_demand_class);
122
123 end if;
124
125 /*----------------------------------------------------------+
126 | Get the total number of error rows in the rows processed |
127 | and update row counts. |
128 +-----------------------------------------------------------*/
129
130 l_error_rows := Count_Row_Errors;
131
132 p_total_rows := l_no_rep_rows + l_no_rec_rows;
133 p_error_rows := l_error_rows;
134 p_succ_rows := p_total_rows - p_error_rows;
135
136 /*---------------------------------------------------+
137 | Delete rows from mrp_form_query. |
138 +----------------------------------------------------*/
139
140 delete mrp_form_query
141 where query_id = g_rec_query_id;
142
143 delete mrp_form_query
144 where query_id = g_rep_query_id;
145
146
147 EXCEPTION
148 WHEN OTHERS THEN
149 p_total_rows := 0;
150 p_error_rows := 0;
151 p_succ_rows := 0;
152 END;
153
154 /************************************************************
155 | This procedure sets release errors to valid error messages |
156 | based on checking the values of certain attributes. |
157 **************************************************************/
158
159 Procedure Update_Pre_Process_Errors(p_no_rec_rows IN NUMBER,
160 p_no_rep_rows IN NUMBER) IS
161 l_no_rec_rows NUMBER;
162 l_no_rep_rows NUMBER;
163 l_mesg_str VARCHAR2(2000) := NULL;
164 l_user_id NUMBER;
165 release_configs VARCHAR2(1) := 'N';
166 BEGIN
167
168 l_no_rep_rows := p_no_rep_rows;
169 l_no_rec_rows := p_no_rec_rows;
170 l_user_id := FND_PROFILE.VALUE('USER_ID');
171 begin
172 select nvl(ORDERS_RELEASE_CONFIGS,'N')
173 into release_configs
174 from mrp_workbench_display_options
175 where user_id = l_user_id;
176 exception
177 when others then
178 release_configs := 'N';
179 end;
180 if(l_no_rec_rows > 0) then
181
182 /*-----------------------------------------------------+
183 | Set All Old Error messages to Null. |
184 +------------------------------------------------------*/
185
186 update mrp_recommendations
187 set release_errors = NULL
188 where transaction_id IN
189 (SELECT number1 from mrp_form_query
190 where
191 query_id = g_rec_query_id)
192 AND release_errors is NOT NULL;
193
194 /*-----------------------------------------------------+
195 | Update Error Message that Models/Option Classes |
196 | cannot be released. |
197 +--------------------------------------------------- --*/
198
199 l_mesg_str := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_MODEL');
200
201 update mrp_recommendations mr
202 set release_errors = l_mesg_str
203 where transaction_id IN
204 (SELECT number1 from mrp_form_query
205 where
206 query_id = g_rec_query_id)
207 and (inventory_item_id, organization_id) IN
208 (select inventory_item_id,
209 organization_id
210 from mrp_system_items msi
211 where msi.compile_designator = mr.compile_designator
212 AND msi.bom_item_type in (1, 2, 3, 5));
213 --Bug3294041 Do not let user release Action=none records.
214 l_mesg_str := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ACTION_NONE');
215 update mrp_recommendations mr
216 set release_errors = l_mesg_str
217 where transaction_id IN
218 (SELECT number1 from mrp_form_query
219 where
220 query_id = g_rec_query_id)
221 and (((inventory_item_id, organization_id) IN
222 (select inventory_item_id,
223 organization_id
224 from mrp_system_items msi
225 where msi.compile_designator = mr.compile_designator
226 AND msi.base_item_id is not null
227 and release_configs = 'N')) OR
228 ( mr.rescheduled_flag =1) OR
229 ( mr.firm_planned_type =1 and mr.order_type <> 5) OR
230 ( mr.order_type =5 and
231 nvl(mr.implemented_quantity,0)+nvl(mr.quantity_in_process,0)
232 ---Bug 4372937 New PO line is not released
233 --- >= mr.new_order_quantity) OR
234 >= nvl(mr.firm_quantity,mr.new_order_quantity) and
235 nvl(mr.release_status,0) <> 1) OR --bug 4655229
236 (mr.old_schedule_date = mr.new_schedule_date and
237 DISPOSITION_STATUS_TYPE <> 2 and
238 mr.order_type IN (2,3)));
239
240 /*-------------------------------------------------------+
241 | Add Error Message that Kanban Items Cannot be Released.|
242 +--------------------------------------------------------*/
243
244 l_mesg_str := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_KANBAN');
245
246 update mrp_recommendations mr
247 set release_errors = release_errors||l_mesg_str
248 where transaction_id IN
249 (SELECT number1 from mrp_form_query
250 where
251 query_id = g_rec_query_id)
252 and (inventory_item_id, organization_id) IN
253 (select flex.inventory_item_id,
254 flex.organization_id
255 from mrp_system_items msi,
256 mtl_item_flexfields flex
257 where msi.compile_designator = mr.compile_designator
258 and flex.inventory_item_id = msi.inventory_item_id
259 and flex.organization_id = msi.organization_id
260 and flex.release_time_fence_code = 6);
261
262 /*--------------------------------------------------------------+
263 | Add Error Message that Items in Orgs modelled as Customer or |
264 | Supplier cannot be released. |
265 +---------------------------------------------------------------*/
266
267 l_mesg_str := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_CUST_SUPP');
268
269 update mrp_recommendations mr
270 set release_errors = release_errors||l_mesg_str
271 where transaction_id IN
272 (SELECT number1 from mrp_form_query
273 where
274 query_id = g_rec_query_id)
275 AND organization_id IN (select organization_id
276 from mrp_cust_sup_org_v);
277
278 /*---------------------------------------------------------------+
279 | Add Error Message that Record was generated as part of some |
280 | other plan/schedule. |
281 +----------------------------------------------------------------*/
282
283 l_mesg_str := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_IN_SOURCE_PLAN');
284
285 update mrp_recommendations mr
286 set release_errors = release_errors||l_mesg_str
287 where transaction_id IN
288 (SELECT number1 from mrp_form_query
289 where
290 query_id = g_rec_query_id)
291 and (inventory_item_id, organization_id) IN
292 (select inventory_item_id, organization_id
293 from mrp_system_items msi
294 where msi.compile_designator = mr.compile_designator
295 and msi.inventory_item_id = mr.inventory_item_id
296 and msi.organization_id = mr.organization_id
297 and msi.in_source_plan = 1);
298
299 l_mesg_str := FND_MESSAGE.GET_STRING('MRP', 'MRP_NO_FLOW_ROUTING');
300 -- bug 5462184: added check of source_vendor_id and organization_id in where clause
301 -- This will not allow release of recommendations whose source type is Make-At
302
303 update mrp_recommendations mr
304 set release_errors = release_errors||l_mesg_str
305 where transaction_id IN
306 (SELECT number1 from mrp_form_query
307 where query_id = g_rec_query_id)
308 and mr.order_type =5
309 and mr.source_vendor_id is null
310 and mr.organization_id = nvl(mr.source_organization_id,mr.organization_id)
311 and exists ( select 1 from bom_operational_routings
312 where assembly_item_id = mr.inventory_item_id
313 and organization_id = mr.organization_id
314 and nvl(alternate_routing_designator,'-23453') =
315 nvl(mr.alternate_routing_designator,'-23453')
316 and cfm_routing_flag = 1);
317
318 end if;
319
320 if(l_no_rep_rows > 0) then
321
322 /*-----------------------------------------------------+
323 | Set All Old Error messages to Null. |
324 +------------------------------------------------------*/
325
326 update mrp_sugg_rep_schedules
327 set release_errors = NULL
328 where rowid IN
329 (SELECT char1 from mrp_form_query
330 where
331 query_id = g_rep_query_id)
332 AND release_errors is NOT NULL;
333
334 /*-----------------------------------------------------+
335 | Update Error Message that Models/Option Classes |
336 | cannot be released. |
337 +--------------------------------------------------- --*/
338
339 l_mesg_str := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_MODEL');
340
341 update mrp_sugg_rep_schedules msrs
342 set release_errors = l_mesg_str
343 where rowid IN
344 (SELECT char1 from mrp_form_query
345 where
346 query_id = g_rep_query_id)
347 and (inventory_item_id, organization_id) IN
348 (select inventory_item_id,
349 organization_id
350 from mrp_system_items msi
351 where msi.compile_designator = msrs.compile_designator
352 AND msi.bom_item_type in (1, 2, 3, 5));
353
354
355 /*--------------------------------------------------------------+
356 | Add Error Message that Items in Orgs modelled as Customer or |
357 | Supplier cannot be released. |
358 +---------------------------------------------------------------*/
359
360 l_mesg_str := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_CUST_SUPP');
361
362 update mrp_sugg_rep_schedules msrs
363 set release_errors = release_errors||l_mesg_str
364 where rowid IN
365 (SELECT char1 from mrp_form_query
366 where
367 query_id = g_rep_query_id)
368 AND organization_id IN (select organization_id
369 from mrp_cust_sup_org_v);
370
371
372 /*---------------------------------------------------------------+
373 | Add Error Message that Record was generated as part of some |
374 | other plan/schedule. |
375 +----------------------------------------------------------------*/
376
377 l_mesg_str := FND_MESSAGE.GET_STRING('MRP',
378 'MRP_REL_ALL_IN_SOURCE_PLAN');
379
380 update mrp_sugg_rep_schedules msrs
381 set release_errors = release_errors||l_mesg_str
382 where rowid IN
383 (SELECT char1 from mrp_form_query
384 where
385 query_id = g_rep_query_id)
386 and (inventory_item_id, organization_id) IN
387 (select inventory_item_id, organization_id
391 and msi.organization_id = msrs.organization_id
388 from mrp_system_items msi
389 where msi.compile_designator = msrs.compile_designator
390 and msi.inventory_item_id = msrs.inventory_item_id
392 and msi.in_source_plan = 1);
393
394
395
396 end if;
397
398 END;
399
400 /******************************************************************
401 * This function inserts rows in mrp_form_query for all the *
402 * transaction id of the records which need to be selected for *
403 * release. This is done for records in table mrp_recommendations. *
404 ******************************************************************/
405
406 FUNCTION Select_Rec_Rows(p_where_clause IN VARCHAR2) return NUMBER
407 IS
408 l_where_clause VARCHAR2(32767) := NULL;
409 l_sql_stmt VARCHAR2(32767) := NULL;
410
411 l_rows_processed INTEGER;
412 l_cursor INTEGER;
413
414 CURSOR GET_QUERY_ID IS
415 SELECT MRP_FORM_QUERY_S.NEXTVAL
416 FROM DUAL;
417 BEGIN
418
419 l_where_clause := p_where_clause;
420
421
422 OPEN GET_QUERY_ID;
423 FETCH GET_QUERY_ID INTO g_rec_query_id;
424 CLOSE GET_QUERY_ID;
425
426
427 l_sql_stmt :=
428 'INSERT INTO mrp_form_query ( '||
429 'query_id, last_update_date, last_updated_by, '||
430 'creation_date, created_by, number1)' ||
431 'SELECT ' ||
432 g_rec_query_id || ',' ||
433 'TRUNC(SYSDATE), '||
434 '-1, '||
435 'TRUNC(SYSDATE), '||
436 '-1, ' ||
437 'mr.transaction_id ' ||
438 ' from mrp_recommendations mr' ||
439 ' where transaction_id IN ' ||
440 '(SELECT transaction_id from mrp_orders_sc_v ' ||
441 ' where ' ||
442 l_where_clause ||
443 ' and order_type IN (2, 3, 5))';
444
445 -- get a cursor handle
446 l_cursor := dbms_sql.open_cursor;
447
448
449 -- parse the sql statement that we just built
450 dbms_sql.parse (l_cursor, l_sql_stmt, dbms_sql.native);
451
452 -- now execute the sql stmt
453 l_rows_processed := dbms_sql.execute(l_cursor);
454
455 -- close the cursor
456 dbms_sql.close_cursor (l_cursor);
457
458 return l_rows_processed;
459
460 END;
461
462 /******************************************************************
463 * This function inserts rows in mrp_form_query for all the *
464 * transaction id of the records which need to be selected for *
465 * release. This is done for records in table *
466 * mrp_sugg_rep_schedules. *
467 ******************************************************************/
468
469 FUNCTION Select_Rep_Rows(p_where_clause IN VARCHAR2) return NUMBER
470 IS
471 l_where_clause VARCHAR2(32767) := NULL;
472 l_sql_stmt VARCHAR2(32767) := NULL;
473
474 l_rows_processed INTEGER;
475 l_cursor INTEGER;
476
477 CURSOR GET_QUERY_ID IS
478 SELECT MRP_FORM_QUERY_S.NEXTVAL
479 FROM DUAL;
480 BEGIN
481
482 l_where_clause := p_where_clause;
483
484
485 OPEN GET_QUERY_ID;
486 FETCH GET_QUERY_ID INTO g_rep_query_id;
487 CLOSE GET_QUERY_ID;
488
489
490 l_sql_stmt :=
491 'INSERT INTO mrp_form_query ( '||
492 'query_id, last_update_date, last_updated_by, '||
493 'creation_date, created_by, number1,char1)' ||
494 'SELECT ' ||
495 g_rep_query_id || ',' ||
496 'TRUNC(SYSDATE), '||
497 '-1, '||
498 'TRUNC(SYSDATE), '||
499 '-1, ' ||
500 'mr.transaction_id ,'||
501 'mr.rowid ' ||
502 ' from mrp_sugg_rep_schedules mr' ||
503 ' where rowid IN ' ||
504 '(SELECT row_id from mrp_orders_sc_v ' ||
505 ' where ' ||
506 l_where_clause ||
507 ' and order_type =13)'||
508 ' AND nvl(status,0) <> 3' ; --bug2797945
509
510 -- get a cursor handle
511 l_cursor := dbms_sql.open_cursor;
512
513
514 -- parse the sql statement that we just built
515 dbms_sql.parse (l_cursor, l_sql_stmt, dbms_sql.native);
516
517 -- now execute the sql stmt
518 l_rows_processed := dbms_sql.execute(l_cursor);
519
520 -- close the cursor
521 dbms_sql.close_cursor (l_cursor);
522
523 return l_rows_processed;
524
525 END;
526
527 /***********************************************************************
528 * This procedure performs 2 sets of functions:
529
530 --- Updates Attributes in mrp_recommendations for records to be selected
531 for release.
532
533 --- Updated post processing errors to the release errors column
534 in mrp_recommendations. The errors are updated
535 in between attribut update statements. The order of these
536 SQL statements is of great significance, otherwise client side
537 field properties such (as REQUIRED/UPDATABLE are affected).
538 Indiscriminately changing the order will create Client Side
539 Bugs.
540 ***********************************************************************/
541
542
543 PROCEDURE Update_Recom_Attrib(
544 p_employee_id IN NUMBER,
545 p_demand_class IN VARCHAR2,
546 p_def_job_class IN VARCHAR2,
547 p_def_firm_jobs IN VARCHAR2) IS
548
549
553 l_wip_seq VARCHAR2(200) := NULL;
550 l_sql_stmt VARCHAR2(32767) := NULL;
551 l_rows_processed INTEGER;
552 l_wip_job_prefix VARCHAR2(240) := NULL;
554 l_def_job_class VARCHAR2(10) := NULL;
555 l_def_firm_jobs VARCHAR2(1) := 'N';
556 l_demand_class VARCHAR2(30) := NULL;
557 l_err_mesg1 VARCHAR2(200);
558 l_err_class1 VARCHAR2(200);
559 l_err_mesg2 VARCHAR2(200);
560 l_err_class2 VARCHAR2(200);
561 l_mesg_str VARCHAR2(2000) := NULL;
562
563 l_cursor INTEGER;
564
565 l_employee_id NUMBER;
566 l_user_id NUMBER;
567 l_session_id NUMBER;
568
569
570 BEGIN
571
572
573 l_wip_job_prefix := FND_PROFILE.VALUE('WIP_JOB_PREFIX');
574 l_user_id := FND_PROFILE.VALUE('USER_ID');
575 --l_wip_seq := 'to_char(wip_job_number_s.nextval)';
576
577 l_employee_id := p_employee_id;
578 l_demand_class := p_demand_class;
579 l_def_job_class := p_def_job_class;
580 l_def_firm_jobs := p_def_firm_jobs;
581
582 select userenv('SESSIONID') into l_session_id from dual;
583
584 /*-------------------------------------------------------------+
585 | Update implement as attribute for planned orders. |
586 +--------------------------------------------------------------*/
587
588 update mrp_recommendations mr
589 set implement_as =
590 (select
591 mfg.lookup_code
592 from
593 mfg_lookups mfg,
594 mrp_system_items msi
595 where mfg.lookup_code =
596 DECODE(mr.source_organization_id,
597 mr.organization_id,
598 DECODE(msi.build_in_wip_flag, 1, 3, 1),
599 DECODE(msi.purchasing_enabled_flag, 1, 2, 1))
600 and mfg.lookup_type = 'MRP_WORKBENCH_IMPLEMENT_AS'
601 and msi.inventory_item_id = mr.inventory_item_id
602 and msi.organization_id = mr.organization_id
603 and msi.compile_designator = mr.compile_designator)
604 where transaction_id IN
605 (SELECT number1 from mrp_form_query
606 where
607 query_id = g_rec_query_id)
608 AND mr.release_errors is NULL
609 AND mr.order_type = 5
610 AND (mr.status <> 0 or nvl(mr.applied, 0) <> 2)
611 AND mr.implement_as IS NULL;
612 --
613 -- missed out in 115.16, bug 2601516
614 --
615 update mrp_recommendations mr
616 set implement_as =
617 (select
618 mfg.lookup_code
619 from mfg_lookups mfg,
620 mrp_system_items msi
621 where mfg.lookup_code =
622 DECODE(msi.planning_make_buy_code,
623 1, DECODE(msi.build_in_wip_flag, 1, 3, 1),
624 DECODE(msi.purchasing_enabled_flag, 1, 2, 1))
625 and mfg.lookup_type = 'MRP_WORKBENCH_IMPLEMENT_AS'
626 and msi.inventory_item_id = mr.inventory_item_id
627 and msi.organization_id = mr.organization_id
628 and msi.compile_designator = mr.compile_designator)
629 where transaction_id IN
630 (SELECT number1 from mrp_form_query
631 where
632 query_id = g_rec_query_id)
633 AND mr.order_type = 5
634 AND mr.status = 0
635 AND nvl(mr.applied, 0) = 2
636 AND mr.implement_as IS NULL;
637 --
638 update mrp_recommendations mr
639 set implement_status_code =
640 (select
641 orders_default_job_status
642 from mrp_workbench_display_options
643 WHERE user_id = l_user_id),
644 mr.created_by = l_user_id
645 where transaction_id IN
646 (SELECT number1 from mrp_form_query
647 where
648 query_id = g_rec_query_id)
649 AND mr.order_type = 5
650 AND mr.implement_as = 3
651 AND mr.release_errors is NULL;
652 /*--------------------------------------------------------------+
653 | default implement unit number for planned orders which are |
654 | unit number control.
655 ---------------------------------------------------------------*/
656
657 update mrp_recommendations mr
658 set implement_end_item_unit_number =
659 nvl(implement_end_item_unit_number,end_item_unit_number)
660 where transaction_id in
661 (select number1 from mrp_form_query
662 where query_id=g_rec_query_id)
663 and release_errors is null
664 and order_type =5
665 and (inventory_item_id, organization_id) in
666 (select inventory_item_id, organization_id
667 from mrp_system_items msi
668 where msi.compile_designator=mr.compile_designator
669 and msi.effectivity_control=2);
670 /*--------------------------------------------------------------+
671 | add error message if implement_unit_number is null |
672 | for planned order which is unit number control |
673 ---------------------------------------------------------------*/
674
675 l_mesg_str := fnd_message.get_string('MRP', 'MRP_REL_ALL_UNIT_NUMBER');
676 update mrp_recommendations mr
677 set release_errors = release_errors || l_mesg_str
678 where transaction_id in
679 (select number1 from mrp_form_query
680 where query_id=g_rec_query_id)
681 and implement_end_item_unit_number is null
682 and (inventory_item_id, organization_id) in
686 and msi.effectivity_control=2);
683 (select inventory_item_id, organization_id
684 from mrp_system_items msi
685 where msi.compile_designator=mr.compile_designator
687
688 /*--------------------------------------------------------------+
689 | Update the following attributes in mrp_recommendations for |
690 | Planned orders being implemented as Purchase Requisitions and |
691 | Purchase Requisitions being rescheduled. |
692 | |
693 +---------------------------------------------------------------*/
694
695 update mrp_recommendations mr
696 set implement_location_id =
697 (select
698 hr.location_id
699 from hr_locations hr,
700 hr_organization_units per
701 where per.organization_id = mr.organization_id
702 and per.location_id = hr.location_id)
703 where transaction_id IN
704 (SELECT number1 from mrp_form_query
705 where
706 query_id = g_rec_query_id)
707 AND ((mr.order_type = 5 AND mr.implement_as = 2) OR
708 mr.order_type = 2)
709 AND mr.implement_location_id is NULL
710 AND mr.release_errors IS NULL;
711
712 /*--------------------------------------------------------------+
713 | Set Error Messages and Update release status to no for records|
714 | which fail certain criteria. |
715 +---------------------------------------------------------------*/
716
717 l_mesg_str := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_LOCATION');
718
719 update mrp_recommendations mr
720 set release_errors = release_errors || l_mesg_str
721 where transaction_id IN
722 (SELECT number1 from mrp_form_query
723 where
724 query_id = g_rec_query_id)
725 AND ((mr.order_type = 5 AND mr.implement_as = 2) OR
726 mr.order_type = 2)
727 AND mr.implement_location_id is NULL;
728
729 if(l_employee_id is NULL) then
730
731 l_mesg_str := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_EMPLOYEE');
732
733 update mrp_recommendations mr
734 set release_errors = release_errors || l_mesg_str,
735 implement_as = NULL,
736 release_status = 2
737 where transaction_id IN
738 (SELECT number1 from mrp_form_query
739 where
740 query_id = g_rec_query_id)
741 AND ((mr.order_type = 5 AND mr.implement_as = 2) OR
742 mr.order_type = 2);
743
744
745 end if;
746
747 /*------------------------------------------------------+
748 | Update following attributes in mrp_recommendations |
749 | for planned orders, purchase reqs and |
750 | discete jobs. |
751 | |
752 | implement job_name implement_employee_id |
753 | implement_vendor_id implement_vendor_site_id |
754 | implement_source_org_id implement_demand_class |
755 | implement_date release_status |
756 | implement_quantity implement_project_id |
757 | implement_task_id rescheduled_flag |
758 | implement_firm |
759 +-------------------------------------------------------*/
760 --dbms_output.put_line('going ot implement_date');
761 update mrp_recommendations mr
762 SET
763 implement_date = nvl(mr.implement_date, relall_next_work_day(
764 mr.organization_id,
765 1,
766 GREATEST(NVL(mr.firm_date,
767 mr.new_schedule_date),
768 TRUNC(SYSDATE)))),
769 implement_project_id = nvl(mr.implement_project_id,
770 DECODE(mr.order_type, 2,
771 NULL,
772 nvl(mr.implement_project_id,
773 mr.project_id))),
774 implement_task_id = nvl(mr.implement_task_id,
775 DECODE(mr.order_type, 2,
776 NULL,
777 nvl(mr.implement_task_id,
778 mr.task_id)))
779 where transaction_id IN
780 (SELECT number1 from mrp_form_query
781 where
782 query_id = g_rec_query_id)
783 AND mr.release_errors IS NULL
784 AND mr.order_type IN (2, 3, 5);
785
786
787
788 /*--------------------------------------------------------------+
789 | Update Error Message for Records with implement date equals |
790 | to NULL. |
791 +---------------------------------------------------------------*/
792
793 l_mesg_str := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_IMPL_DATE');
794
795 update mrp_recommendations mr
796 set release_errors = release_errors || l_mesg_str
797 where transaction_id IN
798 (SELECT number1 from mrp_form_query
799 where
800 query_id = g_rec_query_id)
801 AND mr.implement_date is NULL
802 AND mr.release_errors is NULL;
803
804
805 /*--------------------------------------------------------------+
806 | Update Project Control Level Specific Error Messages |
807 | |
808 +---------------------------------------------------------------*/
809
810 l_mesg_str := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_NO_PROJECT');
811
812 update mrp_recommendations mr
813 set release_errors = release_errors || l_mesg_str
814 where transaction_id IN
818 and implement_project_id IS NULL
815 (SELECT number1 from mrp_form_query
816 where
817 query_id = g_rec_query_id)
819 and order_type <>2
820 and mr.project_id is NOT NULL
821 and organization_id IN
822 (select organization_id from mtl_parameters mp
823 where mp.project_control_level = 1
824 and mp.project_reference_enabled = 1
825 and mp.organization_id = mr.organization_id);
826
827 l_mesg_str := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_NO_TASK');
828
829 update mrp_recommendations mr
830 set release_errors = release_errors || l_mesg_str
831 where transaction_id IN
832 (SELECT number1 from mrp_form_query
833 where
834 query_id = g_rec_query_id)
835 and implement_task_id IS NULL
836 and order_type <>2
837 and mr.project_id is NOT NULL
838 and mr.task_id is NOT NULL
839 and organization_id IN
840 (select organization_id from mtl_parameters mp
841 where mp.project_control_level = 2
842 and mp.project_reference_enabled = 1
843 and mp.organization_id = mr.organization_id);
844
845 /*------------------------------------------------------+
846 | Update following attributes in mrp_recommendations |
847 | for planned orders, purchase reqs and |
848 | discete jobs. |
849 | |
850 | implement job_name implement_employee_id |
851 | implement_vendor_id implement_vendor_site_id |
852 | implement_source_org_id implement_demand_class |
853 | implement_date release_status |
854 | implement_quantity implement_project_id |
855 | implement_task_id rescheduled_flag |
856 | implement_firm |
857 +-------------------------------------------------------*/
858
859 update mrp_recommendations mr
860 SET
861 implement_quantity =
862 nvl(implement_quantity,
863 DECODE(mr.disposition_status_type,
864 2, 0,
865 GREATEST(NVL(mr.firm_quantity, mr.new_order_quantity)
866 - NVL(mr.quantity_in_process, 0)
867 - NVL(mr.implemented_quantity, 0), 0))),
868 implement_status_code=DECODE(mr.order_type,3,
869 nvl(implement_status_code, DECODE(mr.disposition_status_type,2,7,NULL)),
870 implement_status_code),
871 release_status = 1,
872 implement_job_name= nvl(mr.implement_job_name,decode(mr.implement_as,3,l_session_id,NULL)),
873 implement_employee_id =
874 DECODE(mr.implement_as, 2,
875 l_employee_id,
876 NULL),
877 implement_vendor_id =
878 nvl(mr.implement_vendor_id,DECODE(mr.implement_as, 2,
879 nvl(mr.implement_vendor_id, mr.source_vendor_id),
880 mr.implement_vendor_id)),
881 implement_vendor_site_id =
882 DECODE(mr.implement_as, 2,
883 nvl(mr.implement_vendor_site_id, mr.source_vendor_site_id),
884 mr.implement_vendor_site_id),
885 implement_source_org_id =
886 DECODE(mr.implement_as, 2,
887 DECODE(mr.source_organization_id,
888 mr.organization_id, NULL,
889 mr.source_organization_id),
890 NULL),
891 implement_demand_class =
892 DECODE(mr.implement_as,
893 3,nvl(mr.implement_demand_class, l_demand_class),
894 NULL),
895 rescheduled_flag = DECODE(mr.order_type, 5,
896 2, 1),
897 implement_alternate_routing = nvl(implement_alternate_routing,
898 alternate_routing_designator),
899 implement_alternate_bom = nvl(implement_alternate_bom,
900 alternate_bom_designator),
901 implement_firm = DECODE(mr.implement_as, 3,
902 nvl(mr.implement_firm,
903 DECODE(l_def_firm_jobs, 'Y',
904 1, 2)),
905 NULL)
906 where transaction_id IN
907 (SELECT number1 from mrp_form_query
908 where
909 query_id = g_rec_query_id)
910 AND mr.release_errors IS NULL
911 AND mr.order_type IN (2, 3, 5);
912
913 /*--------------------------------------------------------------+
914 | Update the following attributes in mrp_recommendations for |
915 | planned orders: |
916 | Quantity In Process Implement WIP Class Code |
917 +---------------------------------------------------------------*/
918
919 /* The rel_all_quantity holds the old implement quantity used in the
920 ** Select All for Release Session. If rel_all_qty is -9999, it implies that
921 ** the quantity in process is already updated in the client and we don't
922 ** need to do it here.
923 */
924
925 update mrp_recommendations mr
926 set quantity_in_process =
927 DECODE(mr.number1,
928 -9999, mr.quantity_in_process,
929 GREATEST(0,
930 NVL(mr.quantity_in_process, 0) +
931 NVL(mr.implement_quantity, 0) -
932 NVL(mr.number1,0))),
933 implement_wip_class_code =
934 nvl(mr.implement_wip_class_code, DECODE(mr.implement_as, 3,
935 nvl(l_def_job_class, relall_default_acc_class(
936 mr.organization_id,
937 mr.inventory_item_id,
938 1,
939 nvl(mr.implement_project_id,
940 mr.project_id))),
944 mr.number1)
941 NULL)),
942 number1 = DECODE(mr.order_type,
943 5, mr.implement_quantity,
945 where transaction_id IN
946 (SELECT number1 from mrp_form_query
947 where
948 query_id = g_rec_query_id)
949 AND mr.release_errors IS NULL
950 AND mr.order_type = 5;
951
952 /*-------------------------------------------------------------------+
953 | update load_type
954 +--------------------------------------------------------------------*/
955 update mrp_recommendations mr
956 SET
957 load_type =decode(order_type, 5, decode(implement_as, 3, 1, 2, 8, null),
958 3, 4, 2, 16, null)
959 where transaction_id in
960 (select number1 from mrp_form_query
961 where query_id = g_rec_query_id)
962 AND mr.release_status=1;
963
964 /*--------------------------------------------------------------+
965 | Update attributes for Errored Records |
966 +---------------------------------------------------------------*/
967
968 update mrp_recommendations
969 SET
970 implement_as = NULL,
971 implement_quantity = NULL,
972 implement_date = NULL,
973 release_status = 2
974 where transaction_id in
975 (select number1 from mrp_form_query
976 where query_id = g_rec_query_id)
977 and release_errors is not NULL;
978 --
979 /*______________________________________________________________+
980 | Bug 1826152 Set Status And Applied fields for color change |
981 +_______________________________________________________________*/
982 --
983 UPDATE mrp_recommendations
984 SET status = 0,
985 applied = 2
986 WHERE order_type IN (2,3,5)
987 AND nvl(release_status,2) = 1
988 AND release_errors is NULL
989 AND transaction_id in
990 (select number1 from mrp_form_query
991 where query_id = g_rec_query_id);
992 --
993 -- End of Change for bug 1826152
994 --
995 END;
996
997
998 PROCEDURE Update_Rep_Attrib(p_demand_class IN VARCHAR2) IS
999 l_demand_class VARCHAR2(30) := NULL;
1000 l_mesg_str VARCHAR2(2000) := NULL;
1001 BEGIN
1002
1003 l_demand_class := p_demand_class;
1004
1005
1006 /*------------------------------------------------------+
1007 | Update the following attributes in mrp_sugg_rep_ |
1008 | schedules |
1009 | |
1010 | |
1011 | implement_date release_status |
1012 | implement_daily_rate implement_demand_class |
1013 | implement_line_id |
1014 +------------------------------------------------------*/
1015 update mrp_sugg_rep_schedules msrs
1016 SET implement_date = nvl(msrs.implement_date,
1017 msrs.last_unit_completion_date),
1018 implement_daily_rate = nvl(msrs.implement_daily_rate,
1019 msrs.daily_rate),
1020 implement_demand_class = nvl(msrs.implement_demand_class,
1021 l_demand_class),
1022 implement_line_id = nvl(msrs.implement_line_id,
1023 msrs.repetitive_line),
1024 implement_processing_days = DECODE(msrs.implement_processing_days,
1025 NULL,
1026 relall_days_between(
1027 msrs.organization_id,
1028 1,
1029 msrs.last_unit_completion_date,
1030 msrs.first_unit_completion_date)
1031 +1,
1032 msrs.implement_processing_days),
1033 release_status = 1
1034 where rowid IN
1035 (SELECT char1 from mrp_form_query
1036 where
1037 query_id = g_rep_query_id)
1038 AND msrs.release_errors is NULL;
1039
1040 /*--------------------------------------------------------------+
1041 | Update Error Message for Records with implement date equals |
1042 | to NULL. |
1043 +---------------------------------------------------------------*/
1044
1045
1046 l_mesg_str := FND_MESSAGE.GET_STRING('MRP', 'MRP_REL_ALL_IMPL_DATE');
1047
1048 update mrp_sugg_rep_schedules msrs
1049 set release_errors = release_errors || l_mesg_str,
1050 release_status = 2
1051 where rowid IN
1052 (SELECT char1 from mrp_form_query
1053 where
1054 query_id = g_rep_query_id)
1055 AND msrs.implement_date is NULL
1056 AND msrs.release_errors is NULL;
1057
1058 /*-------------------------------------------------------------+
1059 | update load_type
1060 +--------------------------------------------------------------*/
1061
1062 update mrp_sugg_rep_schedules msrs
1063 SET
1064 load_type=2
1065 where rowid IN
1066 (SELECT char1 from mrp_form_query
1067 where
1068 query_id = g_rep_query_id)
1069 AND release_status=1;
1070
1071 -- Not there in 115.16
1072 -- Bug 1783575(--bug2503086)
1073 -- Change color to pink and action to None after clicking
1074 -- select all for release
1075 -- 2797945
1076 UPDATE mrp_sugg_rep_schedules
1077 SET status = 1
1078 WHERE rowid IN
1079 (SELECT char1 from mrp_form_query
1080 where
1084 AND release_errors is NULL;
1081 query_id = g_rep_query_id)
1082 AND release_status = 1
1083 AND status <> 3
1085
1086 END;
1087
1088
1089 /******************************************************************
1090 * Function to find number of records with release errors in *
1091 * mrp_recommendations and mrp_sugg_rep_schedules. *
1092 ******************************************************************/
1093
1094 FUNCTION Count_Row_Errors return NUMBER IS
1095 l_rep_errors NUMBER;
1096 l_rec_errors NUMBER;
1097 l_tot_errors NUMBER;
1098 BEGIN
1099
1100 l_rep_errors := 0;
1101 l_rec_errors := 0;
1102 l_tot_errors := 0;
1103
1104 SELECT
1105 count(1)
1106 into
1107 l_rec_errors
1108 from mrp_recommendations
1109 where transaction_id IN
1110 (SELECT number1 from mrp_form_query
1111 where
1112 query_id = g_rec_query_id)
1113 and release_errors is NOT NULL;
1114
1115 SELECT
1116 count(1)
1117 into
1118 l_rep_errors
1119 from mrp_sugg_rep_schedules
1120 where rowid IN
1121 (SELECT char1 from mrp_form_query
1122 where
1123 query_id = g_rep_query_id)
1124 and release_errors is not NULL;
1125
1126 l_tot_errors := l_rep_errors + l_rec_errors;
1127
1128 return(l_tot_errors);
1129
1130
1131 END;
1132
1133 /*------------------------------------------------------------------+
1134 | Calendar Routines copied from mrp_calendar to this Package to |
1135 | take care of pragma issues. |
1136 +-------------------------------------------------------------------*/
1137
1138
1139 FUNCTION RELALL_NEXT_WORK_DAY(arg_org_id IN NUMBER,
1140 arg_bucket IN NUMBER,
1141 arg_date IN DATE) RETURN DATE IS
1142 BEGIN
1143
1144 --dbms_output.put_line('arg date'||arg_date);
1145 --dbms_output.put_line('bucket'||arg_bucket);
1146 --dbms_output.put_line('org'|| arg_org_id);
1147 IF arg_date is NULL or arg_org_id is NULL or arg_bucket is NULL THEN
1148 RETURN NULL;
1149 END IF;
1150 relall_select_cal_defaults(arg_org_id,
1151 var_calendar_code, var_exception_set_id);
1152
1153
1154 RELALL_MRP_CAL_INIT_GLOBAL(var_calendar_code, var_exception_set_id);
1155 IF arg_bucket = TYPE_DAILY_BUCKET THEN
1156
1157 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date >= max_date THEN
1158 var_return_date := max_date;
1159 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date < min_date THEN
1160 var_return_date := min_date;
1161 ELSE
1162 SELECT cal.next_date
1163 INTO var_return_date
1164 FROM bom_calendar_dates cal
1165 WHERE cal.exception_set_id = var_exception_set_id
1166 AND cal.calendar_code = var_calendar_code
1167 AND cal.calendar_date = TRUNC(arg_date);
1168 END IF;
1169
1170 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
1171 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date >= max_week_date THEN
1172 var_return_date := max_week_date;
1173 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date < min_week_date THEN
1174 var_return_date := min_week_date;
1175 ELSE
1176 SELECT MIN(cal.week_start_date)
1177 INTO var_return_date
1178 FROM bom_cal_week_start_dates cal
1179 WHERE cal.exception_set_id = var_exception_set_id
1180 AND cal.calendar_code = var_calendar_code
1181 AND cal.week_start_date >= TRUNC(arg_date);
1182 END IF;
1183
1184 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
1185 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date >= max_period_date THEN
1186 var_return_date := max_period_date;
1187 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date < min_period_date THEN
1188 var_return_date := min_period_date;
1189 ELSE
1190 SELECT MIN(cal.period_start_date)
1191 INTO var_return_date
1192 FROM bom_period_start_dates cal
1193 WHERE cal.exception_set_id = var_exception_set_id
1194 AND cal.calendar_code = var_calendar_code
1195 AND cal.period_start_date >= TRUNC(arg_date);
1196 END IF;
1197 END IF;
1198
1199 return var_return_date;
1200 EXCEPTION
1201 WHEN NO_DATA_FOUND THEN NULL;
1202 END RELALL_NEXT_WORK_DAY;
1203
1204 PROCEDURE RELALL_SELECT_CAL_DEFAULTS(
1205 arg_org_id IN NUMBER,
1206 arg_calendar_code OUT NOCOPY VARCHAR2,
1207 arg_exception_set_id OUT NOCOPY NUMBER) IS
1208
1209 BEGIN
1210 SELECT calendar_code,
1211 calendar_exception_set_id
1212 INTO arg_calendar_code,
1213 arg_exception_set_id
1214 FROM mtl_parameters
1215 WHERE organization_id = arg_org_id;
1216
1217 IF SQL%NOTFOUND THEN
1218 raise_application_error(-200000, 'Cannot select calendar defaults');
1219 END IF;
1220
1221 END RELALL_SELECT_CAL_DEFAULTS;
1222
1223 PROCEDURE RELALL_MRP_CAL_INIT_GLOBAL( arg_calendar_code VARCHAR,
1224 arg_exception_set_id NUMBER) IS
1225 temp_char VARCHAR2(30);
1229 arg_exception_set_id <> mrp_calendar_excep_set THEN
1226 BEGIN
1227 --dbms_output.put_line('In MRP_CAL_INIT_GLOBAL');
1228 IF arg_calendar_code <> mrp_calendar_cal_code OR
1230
1231 SELECT min(calendar_date), max(calendar_date), min(seq_num),
1232 max(seq_num)
1233 INTO min_date, max_date, min_seq_num, max_seq_num
1234 FROM bom_calendar_dates
1235 WHERE calendar_code = arg_calendar_code
1236 AND seq_num is not null
1237 AND exception_set_id = arg_exception_set_id;
1238
1239 SELECT min(period_start_date), max(period_start_date)
1240 INTO min_period_date, max_period_date
1241 FROM bom_period_start_dates
1242 WHERE calendar_code = arg_calendar_code
1243 AND exception_set_id = arg_exception_set_id;
1244
1245 SELECT min(week_start_date), max(week_start_date), min(seq_num),
1246 max(seq_num)
1247 INTO min_week_date, max_week_date, min_week_seq_num,
1248 max_week_seq_num
1249 FROM bom_cal_week_start_dates
1250 WHERE calendar_code = arg_calendar_code
1251 AND exception_set_id = arg_exception_set_id;
1252
1253 mrp_calendar_cal_code := arg_calendar_code;
1254 mrp_calendar_excep_set := arg_exception_set_id;
1255 END IF;
1256
1257 IF MRP_CALENDAR_RET_DATES = 0 THEN
1258 --dbms_output.put_line('Getting value of profile');
1259 temp_Char := FND_PROFILE.VALUE('MRP_RETAIN_DATES_WTIN_CAL_BOUNDARY');
1260 IF temp_Char = 'Y' THEN
1261 MRP_CALENDAR_RET_DATES := 1;
1262 ELSE
1263 MRP_CALENDAR_RET_DATES := 2;
1264 END IF;
1265 END IF;
1266 --dbms_output.put_line(to_char(MRP_CALENDAR_RET_DATES));
1267 EXCEPTION
1268 WHEN NO_DATA_FOUND THEN NULL;
1269 END RELALL_MRP_CAL_INIT_GLOBAL;
1270
1271
1272 FUNCTION RELALL_DAYS_BETWEEN( arg_org_id IN NUMBER,
1273 arg_bucket IN NUMBER,
1274 arg_date1 IN DATE,
1275 arg_date2 IN DATE) RETURN NUMBER IS
1276 BEGIN
1277 relall_select_cal_defaults(arg_org_id,
1278 var_calendar_code, var_exception_set_id);
1279
1280 IF arg_date1 is NULL or arg_bucket is null or arg_org_id is null
1281 or arg_date2 IS NULL THEN
1282 RETURN NULL;
1283 END IF;
1284
1285 RELALL_MRP_CAL_INIT_GLOBAL(var_calendar_code, var_exception_set_id);
1286 IF (arg_bucket <> TYPE_MONTHLY_BUCKET) THEN
1287 var_prev_seq_num := RELALL_PREV_WORK_DAY_SEQNUM(arg_org_id, arg_bucket, arg_date1);
1288 var_prev_seq_num2 := RELALL_PREV_WORK_DAY_SEQNUM(arg_org_id, arg_bucket, arg_date2);
1289 var_return_number := ABS(var_prev_seq_num2 - var_prev_seq_num);
1290 ELSE
1291 var_prev_work_day := RELALL_PREV_WORK_DAY(arg_org_id, arg_bucket, arg_date1);
1292 var_prev_work_day2 := RELALL_PREV_WORK_DAY(arg_org_id, arg_bucket, arg_date2);
1293 SELECT count(period_start_date)
1294 INTO var_return_number
1295 FROM bom_period_start_dates cal
1296 WHERE cal.exception_set_id = var_exception_set_id
1297 AND cal.calendar_code = var_calendar_code
1298 AND cal.period_start_date between var_prev_work_day
1299 and var_prev_work_day2
1300 AND cal.period_start_date <> var_prev_work_day2;
1301
1302 END IF;
1303
1304 return var_return_number;
1305 EXCEPTION
1306 WHEN NO_DATA_FOUND THEN NULL;
1307 END RELALL_DAYS_BETWEEN;
1308
1309 FUNCTION RELALL_PREV_WORK_DAY(arg_org_id IN NUMBER,
1310 arg_bucket IN NUMBER,
1311 arg_date IN DATE) RETURN DATE IS
1312 BEGIN
1313 IF arg_date is NULL or arg_org_id is NULL or arg_bucket is NULL THEN
1314 RETURN NULL;
1315 END IF;
1316 relall_select_cal_defaults(arg_org_id,
1317 var_calendar_code, var_exception_set_id);
1318
1319 RELALL_MRP_CAL_INIT_GLOBAL(var_calendar_code, var_exception_set_id);
1320 IF arg_bucket = TYPE_DAILY_BUCKET THEN
1321 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date > max_date THEN
1322 var_return_date := max_date;
1323 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date <= min_date THEN
1324 var_return_date := min_date;
1325 ELSE
1326 SELECT cal.prior_date
1327 INTO var_return_date
1328 FROM bom_calendar_dates cal
1329 WHERE cal.exception_set_id = var_exception_set_id
1330 AND cal.calendar_code = var_calendar_code
1331 AND cal.calendar_date = TRUNC(arg_date);
1332 END IF;
1333 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
1334 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date > max_week_date THEN
1335 var_return_date := max_week_date;
1336 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date <= min_week_date THEN
1337 var_return_date := min_week_date;
1338 ELSE
1339 SELECT MAX(cal.week_start_date)
1340 INTO var_return_date
1341 FROM bom_cal_week_start_dates cal
1342 WHERE cal.exception_set_id = var_exception_set_id
1343 AND cal.calendar_code = var_calendar_code
1344 AND cal.week_start_date <= TRUNC(arg_date);
1348 var_return_date := max_period_date;
1345 END IF;
1346 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
1347 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date > max_period_date THEN
1349 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date <= min_period_date THEN
1350 var_return_date := min_period_date;
1351 ELSE
1352 SELECT MAX(cal.period_start_date)
1353 INTO var_return_date
1354 FROM bom_period_start_dates cal
1355 WHERE cal.exception_set_id = var_exception_set_id
1356 AND cal.calendar_code = var_calendar_code
1357 AND cal.period_start_date <= TRUNC(arg_date);
1358 END IF;
1359 END IF;
1360
1361 return var_return_date;
1362 EXCEPTION
1363 WHEN NO_DATA_FOUND THEN NULL;
1364 END RELALL_PREV_WORK_DAY;
1365
1366 FUNCTION RELALL_PREV_WORK_DAY_SEQNUM(arg_org_id IN NUMBER,
1367 arg_bucket IN NUMBER,
1368 arg_date IN DATE) RETURN NUMBER IS
1369 BEGIN
1370 relall_select_cal_defaults(arg_org_id,
1371 var_calendar_code, var_exception_set_id);
1372
1373 IF arg_bucket = TYPE_DAILY_BUCKET THEN
1374 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date > max_date THEN
1375 var_return_number := max_seq_num;
1376 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date <= min_date THEN
1377 var_return_number := min_seq_num;
1378 ELSE
1379 SELECT cal.prior_seq_num
1380 INTO var_return_number
1381 FROM bom_calendar_dates cal
1382 WHERE cal.exception_set_id = var_exception_set_id
1383 AND cal.calendar_code = var_calendar_code
1384 AND cal.calendar_date = TRUNC(arg_date);
1385 END IF;
1386 ELSIF arg_bucket = TYPE_WEEKLY_BUCKET THEN
1387 IF MRP_CALENDAR_RET_DATES = 1 AND arg_date > max_week_date THEN
1388 var_return_number := max_week_seq_num;
1389 ELSIF MRP_CALENDAR_RET_DATES = 1 AND arg_date <= min_week_date THEN
1390 var_return_number := min_week_seq_num;
1391 ELSE
1392 SELECT MAX(cal.seq_num)
1393 INTO var_return_number
1394 FROM bom_cal_week_start_dates cal
1395 WHERE cal.exception_set_id = var_exception_set_id
1396 AND cal.calendar_code = var_calendar_code
1397 AND cal.week_start_date <= TRUNC(arg_date);
1398 END IF;
1399 ELSIF arg_bucket = TYPE_MONTHLY_BUCKET THEN
1400 raise_application_error(-20000, 'Invalid bucket type');
1401 END IF;
1402
1403 return var_return_number;
1404 EXCEPTION
1405 WHEN NO_DATA_FOUND THEN NULL;
1406 END RELALL_PREV_WORK_DAY_SEQNUM;
1407
1408 /*--------------------------------------------------------------+
1409 | Copy of WIP routines to determine default job class. These are|
1410 | copid here from wip_common to work around Pragma Issus. |
1411 +--------------------------------------------------------------*/
1412 Function RELALL_DEFAULT_ACC_CLASS
1413 (X_ORG_ID IN NUMBER,
1414 X_ITEM_ID IN NUMBER,
1415 X_ENTITY_TYPE IN NUMBER,
1416 X_PROJECT_ID IN NUMBER
1417 )
1418 return VARCHAR2 IS
1419 V_PRODUCT_LINE CONSTANT NUMBER := 8;
1420 V_COST_METHOD NUMBER(1);
1421 V_COST_GROUP_ID NUMBER;
1422 V_DISC_CLASS VARCHAR2(10);
1423 V_REP_CLASS VARCHAR2(10);
1424 V_PRJ_DEF_CLASS VARCHAR2(10);
1425 V_DISABLE_DATE DATE;
1426 V_RET NUMBER;
1427 V_RET1 NUMBER;
1428 begin
1429 select primary_cost_method
1430 into V_COST_METHOD
1431 from mtl_parameters
1432 where
1433 organization_id = X_ORG_ID;
1434 if( V_COST_METHOD = 1 ) then
1435 -- Standard Costing Organization
1436 begin
1437 select wdcac.std_discrete_class, wdcac.repetitive_assy_class
1438 into V_DISC_CLASS, V_REP_CLASS
1439 from mtl_default_category_sets mdcs, mtl_item_categories mic,
1440 wip_def_cat_acc_classes wdcac
1441 where
1442 mdcs.functional_area_id = V_PRODUCT_LINE and
1443 mdcs.category_set_id = mic.category_set_id and
1444 mic.organization_id = X_ORG_ID and
1445 mic.inventory_item_id = X_ITEM_ID and
1446 wdcac.organization_id = X_ORG_ID and
1447 mic.category_id = wdcac.category_id and
1448 wdcac.cost_group_id IS NULL;
1449
1450 if( X_ENTITY_TYPE in (1,4) ) then
1451 v_ret := relall_check_disabled
1452 (V_DISC_CLASS, X_ORG_ID, X_ENTITY_TYPE);
1453 elsif ( X_ENTITY_TYPE = 2) then
1454 v_ret := relall_check_disabled
1455 (V_REP_CLASS, X_ORG_ID, X_ENTITY_TYPE);
1456 end if;
1457
1458 if( v_ret = 1 ) then
1459 if (X_ENTITY_TYPE in (1,4) ) then
1460 return(V_DISC_CLASS);
1461 else
1462 return(V_REP_CLASS);
1463 end if;
1464 else
1465 if( X_ENTITY_TYPE in (1,4) ) then
1466 V_DISC_CLASS := NULL;
1467 elsif( X_ENTITY_TYPE = 2) then
1468 return(NULL);
1469 end if;
1470 end if;
1471
1472 exception
1473 when NO_DATA_FOUND then
1474 if( X_ENTITY_TYPE = 2) then
1475 return(NULL);
1476 end if;
1477 end;
1478 begin
1479 if X_PROJECT_ID IS NOT NULL then
1483 where
1480 select wip_acct_class_code
1481 into V_PRJ_DEF_CLASS
1482 from mrp_project_parameters mpp
1484 mpp.project_id = X_PROJECT_ID and
1485 mpp.organization_id = X_ORG_ID;
1486 end if;
1487 exception
1488 when NO_DATA_FOUND then
1489 NULL;
1490 end;
1491 elsif( V_COST_METHOD = 2) then
1492 -- Average Costing Organization
1493 if X_PROJECT_ID IS NOT NULL then
1494 select NVL(costing_group_id,1), wip_acct_class_code
1495 into V_COST_GROUP_ID, V_PRJ_DEF_CLASS
1496 from mrp_project_parameters mpp
1497 where
1498 mpp.project_id = X_PROJECT_ID and
1499 mpp.organization_id = X_ORG_ID;
1500 else
1501 V_COST_GROUP_ID := 1;
1502 end if;
1503
1504 begin
1505 select wdcac.std_discrete_class
1506 into V_DISC_CLASS
1507 from mtl_default_category_sets mdcs, mtl_item_categories mic,
1508 wip_def_cat_acc_classes wdcac
1509 where
1510 mdcs.functional_area_id = V_PRODUCT_LINE and
1511 mdcs.category_set_id = mic.category_set_id and
1512 mic.organization_id = X_ORG_ID and
1513 mic.inventory_item_id = X_ITEM_ID and
1514 wdcac.organization_id = X_ORG_ID and
1515 mic.category_id = wdcac.category_id and
1516 wdcac.cost_group_id = V_COST_GROUP_ID;
1517
1518 v_ret := relall_check_disabled(V_DISC_CLASS, X_ORG_ID, X_ENTITY_TYPE);
1519 if( v_ret = 1) then
1520 return(V_DISC_CLASS);
1521 else
1522 V_DISC_CLASS := NULL;
1523 end if;
1524 exception
1525 when NO_DATA_FOUND then
1526 NULL;
1527 end;
1528 end if;
1529
1530 if X_PROJECT_ID is null and V_DISC_CLASS is null then
1531 -- Default from wip_parameters IFF there is no project and no class
1532 -- defined yet.
1533
1534 SELECT wp.DEFAULT_DISCRETE_CLASS
1535 into V_DISC_CLASS
1536 FROM WIP_PARAMETERS wp
1537 WHERE wp.ORGANIZATION_ID = X_ORG_ID;
1538 v_ret := relall_check_disabled(V_DISC_CLASS, X_ORG_ID, X_ENTITY_TYPE);
1539 if ( v_ret = 0) then
1540 return(NULL);
1541 else
1542 v_ret1 := relall_check_valid_class(V_DISC_CLASS, X_ORG_ID);
1543 if( v_ret1 = 1) then
1544 return(V_DISC_CLASS);
1545 else
1546 return(NULL);
1547 end if;
1548 end if;
1549 elsif X_PROJECT_ID is not NULL and V_PRJ_DEF_CLASS is not null then
1550 -- Default from mrp_project_parameters
1551
1552 V_DISC_CLASS := V_PRJ_DEF_CLASS;
1553 v_ret := relall_check_disabled(V_DISC_CLASS, X_ORG_ID, X_ENTITY_TYPE);
1554 if( v_ret = 1) then
1555 return(V_DISC_CLASS);
1556 else
1557 return(NULL);
1558 end if;
1559 else
1560 return(NULL);
1561 -- Project Id is defined but no class defined
1562 -- in mrp_project_parameters or wip_def_cat_acc_classes
1563 end if;
1564
1565 exception
1566 when NO_DATA_FOUND then
1567 return(NULL);
1568
1569 END;
1570
1571 FUNCTION RELALL_CHECK_DISABLED(
1572 X_CLASS IN VARCHAR2,
1573 X_ORG_ID IN NUMBER,
1574 X_ENTITY_TYPE IN NUMBER)
1575 return number is
1576 V_DISABLE_DATE DATE;
1577
1578 BEGIN
1579 select nvl(wac.disable_date, SYSDATE + 1) into V_DISABLE_DATE
1580 from wip_accounting_classes wac
1581 where
1582 wac.organization_id = X_ORG_ID and
1583 wac.class_type = DECODE(X_ENTITY_TYPE,1,1,2,2,4,1) and
1584 wac.class_code = X_CLASS;
1585
1586 if V_DISABLE_DATE <= SYSDATE then
1587 return(0);
1588 else
1589 return(1);
1590 end if;
1591
1592 END;
1593
1594
1595 FUNCTION RELALL_CHECK_VALID_CLASS(
1596 X_CLASS IN VARCHAR2,
1597 X_ORG_ID IN NUMBER)
1598 return number is
1599 dummy VARCHAR2(40);
1600 v_primary_cost_method number;
1601 v_project_reference_enabled number;
1602 BEGIN
1603 select PRIMARY_COST_METHOD, PROJECT_REFERENCE_ENABLED
1604 into v_primary_cost_method, v_project_reference_enabled
1605 from mtl_parameters mp
1606 where
1607 mp.organization_id = X_ORG_ID;
1608
1609 if v_primary_cost_method = 2 and v_project_reference_enabled = 1 then
1610 begin
1611 select distinct class_code
1612 into dummy
1613 from cst_cg_wip_acct_classes ccwac
1614 where
1615 ccwac.organization_id = X_ORG_ID and
1616 ccwac.class_code = X_CLASS and
1617 nvl(ccwac.disable_date, SYSDATE + 1) > SYSDATE;
1618 return(1);
1619 exception
1620 when NO_DATA_FOUND then
1621 return(0);
1622 end ;
1623 else
1624 return(1); -- For any other org, we don't care about cost_group
1625 end if;
1626
1627 END;
1628
1629 /************************************************************
1630 This procedure does a rollback if the user decides to rollback
1631 select all for release changes.
1632 *************************************************************/
1633
1634 Procedure Rollback_Action IS
1635 BEGIN
1636
1637 rollback;
1638
1642 BEGIN
1639 END;
1640
1641 Procedure Commit_Action IS
1643
1644 commit;
1645
1646 END;
1647
1648 Procedure Update_Job_Name
1649 ( arg_org_id IN NUMBER
1650 , arg_compile_designator IN VARCHAR2
1651 ) IS
1652 l_wip_job_prefix VARCHAR2(240);
1653 l_session_id NUMBER;
1654 count1 NUMBER;
1655 BEGIN
1656
1657 l_wip_job_prefix := FND_PROFILE.VALUE('WIP_JOB_PREFIX');
1658
1659 select userenv('SESSIONID') into l_session_id from dual;
1660
1661 update mrp_recommendations mr set
1662 implement_job_name = l_wip_job_prefix||to_char(wip_job_number_s.nextval)
1663 where implement_job_name=to_char(l_session_id)
1664 and mr.implement_as =3
1665 --and mr.organization_id=arg_org_id /*5735558*/
1666 and mr.compile_designator=arg_compile_designator;
1667
1668 END;
1669
1670 /***============= Bug 4990499 chg begins ===========
1671 This procedure updates the records with duplicate
1672 job names with the wip job number sequence.
1673 ******/
1674 /* Bug 5735558. This procedure will no longer be called. It is replaced
1675 by call to Update_Job_Name which will take care of the duplicate job name.
1676 */
1677
1678 Procedure Update_Identical_Job_Name
1679 ( arg_org_id IN NUMBER
1680 , arg_compile_desig IN VARCHAR2
1681 ) IS
1682 WIP_DIS_MASS_LOAD CONSTANT INTEGER := 1;
1683 l_wip_job_prefix VARCHAR2(240);
1684
1685 BEGIN
1686
1687 l_wip_job_prefix := FND_PROFILE.VALUE('WIP_JOB_PREFIX');
1688
1689 update mrp_recommendations mru set implement_job_name = l_wip_job_prefix||to_char(wip_job_number_s.nextval)
1690 where 1 < (select count(*)
1691 FROM mrp_recommendations mr
1692 WHERE mr.release_errors is NULL
1693 AND mr.implement_quantity > 0
1694 -- AND mr.organization_id = arg_org_id
1695 AND mr.compile_designator = arg_compile_desig
1696 AND mr.load_type = WIP_DIS_MASS_LOAD
1697 AND mr.implement_job_name = mru.implement_job_name)
1698 --AND mru.organization_id = arg_org_id
1699 AND mru.compile_designator = arg_compile_desig;
1700
1701 END;
1702 /*========== Bug 4990499 chg ends ======*/
1703
1704 END MRP_SELECT_ALL_FOR_RELEASE_PUB;