[Home] [Help]
PACKAGE BODY: APPS.GMP_CALENDAR_PKG
Source
1 PACKAGE BODY GMP_CALENDAR_PKG as
2 /* $Header: GMPDCALB.pls 120.25.12010000.2 2008/11/01 22:17:05 rpatangy ship $ */
3
4 invalid_string_value EXCEPTION;
5 v_cp_enabled BOOLEAN := FALSE;
6 V_YES NUMBER := 1;
7 V_WPS CONSTANT VARCHAR2(4) := 'WPS';
8 V_APS CONSTANT VARCHAR2(4) := 'APS';
9 V_FROM_RSRC VARCHAR2(40) ;
10 V_TO_RSRC VARCHAR2(16) ;
11 no_of_secs CONSTANT REAL := 86400;
12 p_orgn_code VARCHAR2(3);
13
14 TYPE ref_cursor_typ is REF CURSOR;
15
16 TYPE cal_shift_typ is RECORD
17 ( cal_date DATE,
18 shift_num PLS_INTEGER,
19 from_time PLS_INTEGER,
20 to_time PLS_INTEGER
21 );
22 calendar_record cal_shift_typ;
23 TYPE cal_tab is table of cal_shift_typ index by BINARY_INTEGER;
24 new_rec cal_tab;
25
26 --BUG6732449 Rpatangy- changed the length of cal_desc from 40 to 240.
27 TYPE cal_detail_typ is RECORD
28 (calendar_no VARCHAR2(16),
29 calendar_desc VARCHAR2(240),
30 orgn_code VARCHAR2(4),
31 organization_id PLS_INTEGER,
32 posted PLS_INTEGER
33 );
34
35 cursor_rec cal_detail_typ;
36 TYPE tab_cal_typ is table of cal_detail_typ INDEX BY BINARY_INTEGER;
37 plsqltbl_rec tab_cal_typ;
38
39 PROCEDURE log_message(pBUFF IN VARCHAR2) IS
40 BEGIN
41 IF fnd_global.conc_request_id > 0 THEN
42 FND_FILE.PUT_LINE( FND_FILE.LOG, pBUFF);
43 ELSE
44 null;
45 END IF;
46 EXCEPTION
47 WHEN others THEN
48 RETURN;
49 END log_message;
50
51 /*
52 REM+==========================================================================+
53 REM| PROCEDURE NAME |
54 REM| rsrc_extract |
55 REM| |
56 REM| Type |
57 REM| public |
58 REM| |
59 REM| DESCRIPTION |
60 REM| The following procedure rows into msc_st_department_resources |
61 REM| |
62 REM| Input Parameters |
63 REM| p_instance_id - Instance Id |
64 REM| p_db_link - Database Link |
65 REM| return_status - Status return variable |
66 REM| |
67 REM| Output Parameters |
68 REM| None |
69 REM| |
70 REM| |
71 REM| HISTORY |
72 REM| Created 5th Aug 1999 by Sridhar Gidugu (OPM Development Oracle US) |
73 REM| 8/17/99 - Changed to Dynamic SQL , added db_link |
74 REM| 10/13/99 - Added deleted_flag in the insert statement |
75 REM| 11/23/99 - Changed value of aggregate_resource_flag from 1 to 2 |
76 REM| 01/12/00 - Added owning_department_id column in the Insert statement |
77 REM| - Bug# 1140113 |
78 REM| 4/03/00 - using mtl_organization_id from ic_whse_mst instead of |
79 REM| - organization_id from mtl_parameters - Bug# 1252322 |
80 REM| 4/18/00 - Fixed Bug# 1273557 - Department count is Zero |
81 REM| - Changes made to the insert statement, changed |
82 REM| - s.organization_id to w.mtl_organization_id |
83 REM| 12/26/01 - Adding Code changes for Resource Utilization and Resource |
84 REM| Efficiency - B2163006 |
85 REM| 12/20/02 - Sridhar Gidugu B2714583, Populated 3 new columns for |
86 REM| msc_st_department_resources |
87 REM| 1.Resource_excess_type, |
88 REM| 2.Resource_shortage_type |
89 REM| 3.User_time_fence |
90 REM| 01/09/03 - Sridhar Gidugu Used mrp_planning_exception_sets |
91 REM| instead of mrp_planning_exception_sets_v |
92 REM| also added extra join with Organization_id |
93 REM| 01/22/03 - Sridhar Gidugu Insert statement for Resource Groups |
94 REM| 05/11/03 - Rajesh Patangya Used to_number(NULL) in palce of NULL |
95 REM| 05/20/03 - Sridhar Gidugu B2971120 Populating new columns |
96 REM| Over_utilized_percent and |
97 REM| under_utilized_percent in dept_rsc table |
98 REM| 04/21/2004 - Navin Sinha - B3577871 -ST:OSFME2: collections failing |
99 REM| in planning data pull. |
100 REM| Added handling of NO_DATA_FOUND Exception.|
101 REM| And return the return_status as TRUE. |
102 REM+==========================================================================+
103 */
104
105 PROCEDURE rsrc_extract(p_instance_id IN PLS_INTEGER,
106 p_db_link IN VARCHAR2,
107 return_status OUT NOCOPY BOOLEAN) IS
108
109 ins_dept_res varchar2(25000);
110 ins_res_group varchar2(25000);
111 ins_res_instance varchar2(25000);
112 dep_ref_cursor ref_cursor_typ;
113
114 BEGIN
115
116 /* New changes made for msc_st_department_resources- using mtl_organization_id
117 from ic_whse_mst instead of organization_id from mtl_parameters
118 table - Bug # 1252322
119 Commented the Where clause resource_whse_code is NOT NULL as whse code in
120 ic_whse_mst is never NULL - 04/03/2000
121 */
122 IF return_status THEN
123 v_cp_enabled := TRUE;
124 ELSE
125 v_cp_enabled := FALSE;
126 END IF;
127
128 /* populate the org_string */
129 IF gmp_calendar_pkg.org_string(p_instance_id) THEN
130 NULL ;
131 ELSE
132 RAISE invalid_string_value ;
133 END IF;
134 /* note that we introduced substr(resources) as the
135 final msc table has the column at 10 char only. If and when the MSC
136 column width increases we shall remove substr */
137
138 ins_dept_res := ' INSERT INTO msc_st_department_resources '
139 || ' ( organization_id, '
140 || ' sr_instance_id, '
141 || ' resource_id, '
142 || ' department_id, '
143 || ' resource_code, '
144 || ' resource_description, '
145 || ' department_code, '
146 || ' owning_department_id, '
147 || ' line_flag, '
148 || ' aggregated_resource_flag, '
149 || ' capacity_units, '
150 || ' available_24_hours_flag, '
151 || ' resource_cost, '
152 || ' ctp_flag, '
153 || ' deleted_flag, '
154 || ' resource_excess_type, '
155 || ' resource_shortage_type, '
156 || ' user_time_fence, '
157 || ' over_utilized_percent, ' /* B2971120 */
158 || ' under_utilized_percent, ' /* B2971120 */
159 || ' efficiency, '
160 || ' utilization, '
161 || ' planning_exception_set, '
162 || ' resource_group_name, '
163 || ' bottleneck_flag, '
164 || ' chargeable_flag, '
165 || ' capacity_tolerance, '
166 || ' batchable_flag, ' /* B4157063 Resource Batching */
167 || ' batching_window, ' /* B4157063 Resource Batching */
168 || ' min_capacity, '
169 || ' max_capacity, '
170 || ' unit_of_measure, '
171 || ' idle_time_tolerance, '
172 || ' sds_scheduling_window, '
173 || ' batching_penalty, '
174 || ' schedule_to_instance, '
175 || ' resource_type ' /*B4487118 - HLINK GC:(RV): MULTIPLE ROWS ARE DISPALYED FOR A RESOURCE IN THE RV*/
176 || ') '
177 || ' SELECT p.organization_id , '
178 || ' :instance_id, '
179 || ' ((r.resource_id * 2) + 1),' /* B1177070 encoded */
180 || ' ((p.organization_id * 2) + 1) department_id,' /* B1177070 encoded */
181 || ' r.resources , '
182 || ' m.resource_desc, ' /*Sowmya-Changed from resources to resource_desc*/
183 || ' p.organization_code , '
184 || ' ((p.organization_id * 2) + 1) , ' /* B1177070 */
185 || ' 2, ' /* Line Flag */
186 || ' 2, ' /* Yes = 1 and No = 2 resource Flag */
187 || ' r.assigned_qty, '
188 || ' 2, ' /* Avail 24 hrs flag */
189 || ' r.nominal_cost, '
190 || ' 1,' /* for ATP to check Resources (RDP)*/
191 || ' 2, '
192 || ' mrp.resource_excess_type, ' /* B2714583 */
193 || ' mrp.resource_shortage_type, ' /* B2714583 */
194 || ' mrp.user_time_fence, ' /* B2714583 */
195 || ' mrp.over_utilized_percent, ' /* B2971120 */
196 || ' mrp.under_utilized_percent, ' /* B2971120 */
197 || ' r.efficiency, ' /* B2163006 */
198 || ' r.utilization, ' /* B2163006 */
199 || ' r.planning_exception_set, ' /* B2714583 */
200 || ' r.group_resource, '
201 || ' NULL, '
202 || ' decode(r.capacity_constraint,1,1,2), '
203 || ' r.capacity_tolerance, '
204 || ' r.batchable_flag, ' /* B4157063 Resource Batching */
205 || ' r.batch_window, ' /* B4157063 Resource Batching */
206 || ' r.min_capacity, '
207 || ' r.max_capacity, '
208 || ' r.capacity_um, '
209 || ' r.idle_time_tolerence, '
210 || ' r.sds_window, '
211 || ' NULL, '
212 /* If the Resource is scheduled to Instance, then value is Yes else No */
213 || ' decode(r.schedule_ind,2,1,2), '
214 || ' 1 '/*B4487118 - HLINK GC:(RV): MULTIPLE ROWS ARE DISPALYED FOR A RESOURCE IN THE RV*/
215 /*sowsubra - ME changes - Start*/
216 /* Replace the use of sy_orgn_mst with mtl_parameters and hr_organization_units */
217 || ' FROM cr_rsrc_dtl'||p_db_link||' r, '
218 || ' cr_rsrc_mst'||p_db_link||' m, '
219 || ' mrp_planning_exception_sets'||p_db_link||' mrp, '
220 || ' mtl_parameters'||p_db_link||' p, '
221 || ' hr_organization_units'||p_db_link||' hr '
222 || ' WHERE r.organization_id = p.organization_id '
223 || ' AND r.resources = m.resources '
224 || ' AND p.organization_id = hr.organization_id '
225 || ' AND nvl(hr.date_to,sysdate) >= sysdate '
226 || ' AND r.planning_exception_set = mrp.exception_set_name '
227 || ' AND p.organization_id = mrp.organization_id '
228 || ' AND p.process_enabled_flag = '||''''||'Y'||'''' ;
229
230 IF gmp_calendar_pkg.g_in_str_org IS NOT NULL THEN
231 ins_dept_res := ins_dept_res
232 ||' AND p.organization_id ' || gmp_calendar_pkg.g_in_str_org ;
233 END IF;
234 /*sowsubra - ME changes - End*/
235
236 ins_dept_res := ins_dept_res
237 || ' AND r.delete_mark = 0 '
238 || ' UNION ALL '
239 || ' SELECT p.organization_id , '
240 || ' :instance_id1, '
241 || ' ((r.resource_id * 2) + 1),' /* B1177070 encoded */
242 || ' ((p.organization_id * 2) + 1),' /* B1177070 encoded */
243 || ' r.resources ,'
244 || ' m.resource_desc, ' /*Sowmya-Changed from resources to resource_desc*/
245 || ' p.organization_code , '
246 || ' ((p.organization_id * 2) + 1) , ' /* B1177070 */
247 || ' 2, ' /* Line Flag */
248 || ' 2, ' /* Yes = 1 and No = 2 resource Flag */
249 || ' r.assigned_qty, '
250 || ' 2, ' /* Avail 24 hrs flag */
251 || ' r.nominal_cost, '
252 || ' 1,' /* for ATP to check Resources (RDP)*/
253 || ' 2, '
254 || ' to_number(NULL), ' /* B2714583 */
255 || ' to_number(NULL), ' /* B2714583 */
256 || ' to_number(NULL), ' /* B2714583 */
257 || ' to_number(NULL), ' /* B2971120 */
258 || ' to_number(NULL), ' /* B2971120 */
259 || ' r.efficiency, ' /* B2163006 */
260 || ' r.utilization, ' /* B2163006 */
261 || ' r.planning_exception_set, ' /* B2714583 */
262 || ' r.group_resource, '
263 || ' NULL, '
264 || ' decode(r.capacity_constraint,1,1,2), '
265 || ' r.capacity_tolerance, '
266 || ' r.batchable_flag, ' /* B4157063 Resource Batching */
267 || ' r.batch_window, ' /* B4157063 Resource Batching */
268 || ' r.min_capacity, '
269 || ' r.max_capacity, '
270 || ' r.capacity_um, '
271 || ' r.idle_time_tolerence, '
272 || ' r.sds_window, '
273 || ' NULL, '
274 /* If the Resource is scheduled to Instance, then value is Yes else No */
275 || ' decode(r.schedule_ind,2,1,2), '
276 || ' 1 ' /*B4487118 - HLINK GC:(RV): MULTIPLE ROWS ARE DISPALYED FOR A RESOURCE IN THE RV*/
277 || ' FROM cr_rsrc_dtl'||p_db_link||' r, '
278 /*sowsubra - ME changes - Start*/
279 /* Replace the use of sy_orgn_mst with mtl_parameters and hr_organization_units */
280 || ' mtl_parameters'||p_db_link||' p, '
281 || ' cr_rsrc_mst'||p_db_link||' m, '
282 || ' hr_organization_units'||p_db_link||' hr '
283 || ' where r.organization_id = p.organization_id '
284 || ' AND r.resources = m.resources '
285 || ' AND r.planning_exception_set IS NULL '
286 || ' AND p.organization_id = hr.organization_id '
287 || ' AND nvl(hr.date_to,sysdate) >= sysdate '
288 || ' AND p.process_enabled_flag = '||''''||'Y'||''''
289 || ' AND r.delete_mark = 0 ' ;
290
291 IF gmp_calendar_pkg.g_in_str_org IS NOT NULL THEN
292 ins_dept_res := ins_dept_res
293 ||' AND p.organization_id ' || gmp_calendar_pkg.g_in_str_org ;
294 END IF;
295 /*sowsubra - ME changes - end*/
296 EXECUTE IMMEDIATE ins_dept_res USING p_instance_id, p_instance_id;
297
298 /* Insert into MSC_ST_RESOURCE_GROUPS for Bottleneck Resources
299 Sending only those resources that are used in Planning for APS
300 */
301 ins_res_group := ' INSERT INTO msc_st_resource_groups '
302 || ' ( group_code, '
303 || ' meaning, '
304 || ' description, '
305 || ' from_date, '
306 || ' to_date, '
307 || ' enabled_flag, '
308 || ' sr_instance_id '
309 || ' ) '
310 || ' SELECT distinct crd.group_resource , '
311 || ' crm.resource_desc,'
312 || ' crm.resource_desc,'
313 || ' sysdate,'
314 || ' NULL,'
315 || ' 1,'
316 || ' :instance_id '
317 /*sowsubra - ME changes - Start*/
318 || ' FROM mtl_parameters'||p_db_link||' p, '
319 || ' hr_organization_units'||p_db_link||' hr, '
320 || ' cr_rsrc_dtl'||p_db_link||' crd, '
321 || ' cr_rsrc_mst'||p_db_link||' crm '
322 || ' WHERE p.organization_id = crd.organization_id '
323 || ' AND p.organization_id = hr.organization_id '
324 || ' AND nvl(hr.date_to,sysdate) >= sysdate '
325 || ' AND p.process_enabled_flag = '||''''||'Y'||''''
326 || ' AND crd.resources = crm.resources '
327 || ' AND crd.group_resource = crm.resources '
328 || ' AND crd.delete_mark = 0 ';
329
330 IF gmp_calendar_pkg.g_in_str_org IS NOT NULL THEN
331 ins_res_group := ins_res_group
332 ||' AND EXISTS ( SELECT 1 FROM mtl_system_items'||p_db_link||' msi'
333 ||' WHERE msi.organization_id = p.organization_id )' ;
334 END IF;
335 /*sowsubra - ME changes - End*/
336
337 EXECUTE IMMEDIATE ins_res_group USING p_instance_id;
338
339 /* Now extract the resource instances too -
340 The instance extraction was put under resource avaialbility extraction
341 but to keep it in synch with Discrete collection, it is being
342 moved here. */
343
344 ins_res_instance := ' INSERT INTO msc_st_dept_res_instances '
345 ||' ( sr_instance_id, '
346 ||' res_instance_id, '
347 ||' resource_id, '
348 ||' department_id, '
349 ||' organization_id, '
350 ||' serial_number, '
351 ||' equipment_item_id, '
352 ||' last_known_setup, '
353 ||' effective_start_date, '
354 ||' effective_end_date, '
355 ||' deleted_flag '
356 ||' ) '
357 ||' SELECT :instance_id, '
358 ||' ((gri.instance_id * 2) + 1), '
359 ||' ((gri.resource_id * 2) + 1) x_resource_id, '
360 ||' ((m.organization_id * 2) + 1) department_id, ' /* encoded */
361 ||' m.organization_id, '
362 ||' NVL(gri.eqp_serial_number, to_char(gri.instance_number)), '
363 ||' gri.equipment_item_id, '
364 ||' gri.last_setup_id, ' -- Conc Prog routine will populate this
365 ||' gri.eff_start_date, '
366 ||' gri.eff_end_date, '
367 ||' 2 '
368 ||' FROM '
369 ||' gmp_resource_instances'||p_db_link||' gri, '
370 ||' cr_rsrc_dtl'||p_db_link||' c, '
371 ||' hr_organization_units'||p_db_link||' hr, '
372 ||' mtl_parameters'||p_db_link||' m '
373 ||' WHERE '
374 ||' gri.resource_id = c.resource_id '
375 || ' AND m.organization_id = hr.organization_id '
376 || ' AND nvl(hr.date_to,sysdate) >= sysdate '
377 ||' AND c.schedule_ind = 2 '
378 ||' AND c.organization_id = m.organization_id '
379 ||' AND m.process_enabled_flag = '||''''||'Y'||''''
380 ||' AND gri.inactive_ind = 0 '
381 ||' AND c.delete_mark = 0 ';
382
383 IF gmp_calendar_pkg.g_in_str_org IS NOT NULL THEN
384 ins_res_instance := ins_res_instance
385 ||' AND m.organization_id ' || gmp_calendar_pkg.g_in_str_org ;
386 END IF;
387
388 EXECUTE IMMEDIATE ins_res_instance USING p_instance_id;
389
390 return_status := TRUE;
391
392 EXCEPTION
393 WHEN invalid_string_value THEN
394 log_message('APS string is Invalid, check for Error condition' );
395 return_status := FALSE;
396 WHEN NO_DATA_FOUND THEN /* B3577871 */
397 log_message('NO_DATA_FOUND exception raised in Procedure: Gmp_calendar_pkg.Rsrc_extract ' );
398 return_status := TRUE;
399 WHEN OTHERS THEN
400 log_message('Error in department/Res Group Insert: '||p_instance_id);
401 log_message(sqlerrm);
402 return_status := FALSE;
403
404 END rsrc_extract;
405
406 /*
407 REM+==========================================================================+
408 REM| PROCEDURE NAME |
409 REM| populate_rsrc_cal |
410 REM| |
411 REM| Type |
412 REM| public |
413 REM| |
414 REM| DESCRIPTION |
415 REM| |
416 REM| |
417 REM| Input Parameters |
418 REM| p_orgn_code - Orgn Code |
419 REM| p_org_id - Organization id |
420 REM| p_cal_id - calendar_id |
421 REM| p_instance_id - Instance Id |
422 REM| p_delimiter - Delimiter |
423 REM| p_db_link - Data Base Link |
424 REM| p_nra_enabled - flag to build net resource available |
425 REM| |
426 REM| Output Parameters |
427 REM| return_status |
428 REM| |
429 REM| |
430 REM| HISTORY |
431 REM| Created 5th Aug 1999 by Sridhar Gidugu (OPM Development Oracle US) |
432 REM| 9/1/99 - Main Proc calls the populate_cal_dates |
433 REM| Update trading Partners and net_rsrc_insert procedure. |
434 REM| |
435 REM| 9/7/99 - Changed the Main Procedure, removed UNION ALL for main cursor|
436 REM| 9/28/99 - Changed the main query ordering by Organization Id and |
437 REM| - changed logic for populating plsqltbl |
438 REM| 4/03/00 - using mtl_organization_id from ic_whse_mst instead of |
439 REM| - organization_id from mtl_parameters - Bug# 1252322 |
440 REM| 5/03/00 - Add instance code as a prefix to the calendar code |
441 REM| - Bug # 1288143 |
442 REM| 7/07/00 - Anchor Date Problem Fixed in the Calendar Code |
443 REM| - Bug # 1337084. |
444 REM| 7/12/00 - Removed the Debugging Statement shcl.calendar_id in |
445 REM| - (121,126) - bug#1353845 |
446 REM| 10/18/01 - B2041247 - Modified the cursor to consider Calendars |
447 REM| associated with the OPM Plants |
448 REM| |
449 REM| 7th Mar 2003 -- Performance issue fix and B2671540 00:00 shift fix |
450 REM| 04/21/2004 - Navin Sinha - B3577871 -ST:OSFME2: collections failing |
451 REM| in planning data pull. |
452 REM| Added handling of NO_DATA_FOUND Exception.|
453 REM| And return the return_status as TRUE. |
454 REM| |
455 REM| 07-May-2004 - Sowmya - B3599089 - ST: ORG SPECIFIC COMPLETE COLLETION |
456 REM| FOR OPM ORGS TAKING MORE TIME. |
457 REM| As the varaibale l_org_specific was not getting |
458 REM| refreshed,the resource availability |
459 REM| was getting collected irrespective of whether or|
460 REM| not the org is enabled. To overcome this, added |
461 REM| if clause containing the l_cur%NOTFOUND.So when |
462 REM| the no values are returned the l_org_specific= 0|
463 REM| |
464 REM+==========================================================================+
465 */
466 PROCEDURE populate_rsrc_cal(p_run_date IN date,
467 p_instance_id IN PLS_INTEGER,
468 p_delimiter IN varchar2,
469 p_db_link IN varchar2,
470 p_nra_enabled IN NUMBER,
471 return_status OUT NOCOPY BOOLEAN) IS
472
473 union_cal_ref ref_cursor_typ;
474 upd_res_avl varchar2(25000);
475 inst_resavl varchar2(25000);
476 sql_allcal varchar2(25000);
477 sql_regen varchar2(25000);
478 inst_stmt varchar2(25000);
479 ins_res_avl varchar2(25000);
480 ins_res_shft varchar2(25000);
481 Upd_Process_Org varchar2(25000);
482 stmt_no integer;
483 l_prev_calendar VARCHAR2(14) ;
484 l_calendar_no VARCHAR2(14) ;
485 v_icode varchar2(4);
486 simulation_set varchar2(10) ;
487 v_errbuf varchar2(2000) ;
488 v_retcode number ;
489 res_passed varchar2(40);
490
491 BEGIN
492 v_icode := '';
493 simulation_set := NULL;
494 ins_res_shft := NULL;
495 inst_resavl := NULL;
496 upd_res_avl := NULL;
497 sql_allcal := NULL;
498 sql_regen := NULL;
499 ins_res_avl := NULL;
500 Upd_Process_Org := NULL;
501 l_prev_calendar := NULL;
502 l_calendar_no := NULL;
503 v_errbuf := null;
504 v_retcode := 0;
505
506 /* Following statements are added to include the instance Code as
507 a Prefix to the Calendar Code, this done to maintain the uniqueness
508 of a calendar code across instances, prior to this change the
509 calendar code was not prefixed with Instance code and this caused
510 unique constraint problems - Bug# 1288143
511 */
512
513 IF return_status THEN
514 v_cp_enabled := TRUE;
515 ELSE
516 v_cp_enabled := FALSE;
517 END IF;
518
519 /* Retrieving the Instance code from MSC_APPS_INSTANCES table - Bug#1288143 */
520 stmt_no := 05;
521 inst_stmt := ' SELECT instance_code '
522 || ' FROM msc_apps_instances'
523 || ' WHERE instance_id = :instance_id ';
524
525 EXECUTE IMMEDIATE inst_stmt INTO v_icode USING p_instance_id ;
526 stmt_no := 10;
527
528 /* populate the org_string */
529 IF gmp_calendar_pkg.org_string(p_instance_id) THEN
530 NULL ;
531 ELSE
532 RAISE invalid_string_value ;
533 END IF;
534 /* mtl_parameters.organization_id - Bug# 1252322 */
535
536 /* Select All the calendars which have calendar_code associated with
537 resources and if not associated then organization calendar_code */
538 stmt_no := 15;
539 sql_allcal := sql_allcal
540 || ' SELECT unique cal.calendar_code, '
541 || ' cal.description, '
542 || ' mp.organization_code, '
543 || ' mp.organization_id organization_id, '
544 || ' 0 '
545 || ' FROM bom_calendars'||p_db_link||' cal, '
546 || ' hr_organization_units'||p_db_link||' hr, '
547 || ' mtl_parameters'||p_db_link||' mp, '
548 || ' cr_rsrc_dtl'||p_db_link||' crd '
549 || ' WHERE mp.organization_id = hr.organization_id '
550 || ' AND nvl(hr.date_to,sysdate) >= sysdate '
551 || ' AND mp.process_enabled_flag = '||''''||'Y'||''''
552 || ' AND crd.organization_id = mp.organization_id '
553 || ' AND NVL(crd.calendar_code,mp.calendar_code)=cal.calendar_code'
554 || ' AND crd.delete_mark = 0 ' ;
555
556 IF gmp_calendar_pkg.g_in_str_org IS NOT NULL THEN
557 sql_allcal := sql_allcal
558 ||' AND mp.organization_id ' || gmp_calendar_pkg.g_in_str_org ;
559 END IF;
560 sql_allcal := sql_allcal
561 || ' ORDER BY 1,4 ';
562
563 stmt_no := 20;
564 OPEN union_cal_ref FOR sql_allcal ;
565 LOOP
566 FETCH union_cal_ref INTO cursor_rec;
567 EXIT WHEN union_cal_ref%NOTFOUND;
568
569 IF l_prev_calendar is NULL OR
570 l_prev_calendar <> cursor_rec.calendar_no THEN
571 l_prev_calendar := cursor_rec.calendar_no ;
572 l_calendar_no := v_icode ||':'||cursor_rec.calendar_no ;
573 -- log_message(l_calendar_no || cursor_rec.organization_id);
574 retrieve_calendar_detail(l_calendar_no,
575 cursor_rec.calendar_no,
576 p_run_date,
577 p_db_link,
578 p_instance_id,
579 V_APS,
580 return_status
581 );
582 END IF ;
583 update_trading_partners(cursor_rec.organization_id,
584 l_calendar_no,
585 return_status
586 );
587 IF p_nra_enabled = 3 THEN
588 /* REGENERATE THE CALENDAR AND THEN INSERT IT INTO MSC TABLE */
589 res_passed := 'DONOT_RETRIEVE_CALENDAR' ;
590
591 sql_regen := sql_regen ||' begin '
592 ||' gmp_calendar_pkg.insert_gmp_resource_avail'||p_db_link
593 ||' (:p1, '
594 ||' :p2, '
595 ||' :p3, '
596 ||' :p4, '
597 ||' NULL, '
598 ||' :p5); '
599 ||' end ; ' ;
600
601 EXECUTE IMMEDIATE sql_regen USING
602 OUT v_errbuf ,
603 OUT v_retcode,
604 cursor_rec.organization_id ,
605 res_passed,
606 cursor_rec.calendar_no ;
607 END IF ;
608 log_message(' After Source Call = ' || v_errbuf || ' REG '|| v_retcode );
609 sql_regen := NULL;
610
611 END LOOP; /* End loop for Main Cursor */
612
613 CLOSE union_cal_ref;
614
615 /* B4751574 Rajesh All the process orgs should have organization_type=2 */
616 BEGIN
617 stmt_no := 29 ;
618 Upd_Process_Org := 'UPDATE MSC_ST_TRADING_PARTNERS'
619 ||' SET organization_type = 2'
620 ||' WHERE sr_tp_id in (SELECT organization_id '
621 ||' FROM mtl_parameters'||p_db_link
622 ||' WHERE process_enabled_flag = '||''''||'Y'||'''' || ')'
623 ||' AND partner_type = 3' ;
624
625 EXECUTE IMMEDIATE Upd_Process_Org;
626 log_message('Trading Partner Update is Done' );
627
628 EXCEPTION
629 WHEN NO_DATA_FOUND THEN
630 NULL ;
631 WHEN OTHERS THEN
632 log_message('Error in UPDATE TRADING_PARTNERS '||stmt_no);
633 log_message(SQLERRM);
634 return_status := FALSE;
635 END ;
636
637 /* ======================= Staging table Inserts ===================*/
638
639 stmt_no := 30;
640 ins_res_avl := ' INSERT INTO msc_st_net_resource_avail '
641 || ' ( organization_id, '
642 || ' sr_instance_id, '
643 || ' resource_id, '
644 || ' department_id, '
645 || ' simulation_set, '
646 || ' shift_num, '
647 || ' shift_date, '
648 || ' from_time, '
649 || ' to_time, '
650 || ' capacity_units, '
651 || ' deleted_flag '
652 || ' ) '
653 || ' SELECT '
654 || ' gra.organization_id, '
655 || ' :instance_id, '
656 || ' ((gra.resource_id*2)+1), '
657 || ' ((gra.organization_id*2)+1) department_id, ' /* encoded */
658 || ' :simulation_set, '
659 || ' gra.shift_num, '
660 || ' gra.shift_date, '
661 || ' gra.from_time, '
662 || ' gra.to_time, '
663 || ' gra.resource_units, '
664 || ' 2 '
665 || ' FROM '
666 || ' gmp_resource_avail'||p_db_link||' gra, '
667 || ' mtl_parameters'||p_db_link||' m, '
668 || ' cr_rsrc_dtl'||p_db_link||' crd '
669 || ' WHERE nvl(gra.resource_instance_id,0) = 0 '
670 || ' AND nvl(crd.calendar_code,m.calendar_code) = gra.calendar_code '
671 || ' AND gra.organization_id = crd.organization_id '
672 || ' AND gra.organization_id = m.organization_id '
673 || ' AND gra.resource_id = crd.resource_id '
674 || ' AND m.process_enabled_flag = '||''''||'Y'||''''
675 || ' AND crd.delete_mark = 0 ';
676
677 IF gmp_calendar_pkg.g_in_str_org IS NOT NULL THEN
678 ins_res_avl := ins_res_avl
679 ||' AND gra.organization_id ' || gmp_calendar_pkg.g_in_str_org ;
680 END IF;
681
682 EXECUTE IMMEDIATE ins_res_avl USING p_instance_id, simulation_set;
683 log_message('Resource Calendar Insertion Is Done' );
684
685 stmt_no := 31;
686 inst_resavl := ' INSERT INTO msc_st_net_res_inst_avail '
687 || ' ( res_instance_id, '
688 || ' serial_number, '
689 || ' equipment_item_id, '
690 || ' Organization_Id, '
691 || ' Resource_Id, '
692 || ' Shift_Num, '
693 || ' Shift_Date, '
694 || ' From_Time, '
695 || ' To_Time, '
696 || ' Department_id, '
697 || ' sr_instance_id '
698 || ' ) '
699 || ' SELECT '
700 || ' ((gri.instance_id*2)+1), '
701 || ' NVL(gri.eqp_serial_number, to_char(gri.instance_number)), '
702 || ' gri.equipment_item_id, '
703 || ' gra.organization_id, '
704 || ' ((gra.resource_id*2)+1), ' /* B4223622 */
705 || ' gra.shift_num, '
706 || ' gra.shift_date, '
707 || ' gra.from_time, '
708 || ' gra.to_time, '
709 || ' ((gra.organization_id*2)+1) department_id, '/* encoded */
710 || ' :instance_id '
711 || ' FROM '
712 || ' gmp_resource_instances'||p_db_link||' gri, '
713 || ' gmp_resource_avail'||p_db_link||' gra, '
714 || ' mtl_parameters'||p_db_link||' m, '
715 || ' cr_rsrc_dtl'||p_db_link||' crd '
716 || ' WHERE gri.resource_id = gra.resource_id '
717 || ' AND gri.instance_id = gra.resource_instance_id '
718 || ' AND gri.inactive_ind = 0 '
719 || ' AND nvl(crd.calendar_code,m.calendar_code) = gra.calendar_code '
720 || ' AND gra.organization_id = crd.organization_id '
721 || ' AND gra.organization_id = m.organization_id '
722 || ' AND gra.resource_id = crd.resource_id '
723 || ' AND m.process_enabled_flag = '||''''||'Y'||''''
724 || ' AND crd.delete_mark = 0 ';
725
726 IF gmp_calendar_pkg.g_in_str_org IS NOT NULL THEN
727 inst_resavl := inst_resavl
728 ||' AND gra.organization_id ' || gmp_calendar_pkg.g_in_str_org ;
729 END IF;
730
731 EXECUTE IMMEDIATE inst_resavl USING p_instance_id;
732 log_message('Resource Calendar Instance Insertion Is Done' );
733
734 /* Insert for msc_st_resource_shifts Starts here - 2213101 */
735
736 stmt_no := 32;
737 ins_res_shft := ' INSERT INTO msc_st_resource_shifts '
738 || ' ( department_id, '
739 || ' shift_num, '
740 || ' resource_id, '
741 || ' deleted_flag, '
742 || ' sr_instance_id, '
743 || ' capacity_units '
744 || ' ) '
745 || ' SELECT unique '
746 || ' ((m.organization_id*2)+1) , ' /* encoded */
747 || ' gtmp.shift_num, '
748 || ' ((crd.resource_id*2)+1), '
749 || ' 2, '
750 || ' :instance_id, '
751 || ' crd.assigned_qty '
752 || ' FROM gmp_calendar_detail_gtmp'||p_db_link||' gtmp, '
753 || ' mtl_parameters'||p_db_link||' m, '
754 || ' cr_rsrc_dtl'||p_db_link||' crd '
755 || ' WHERE NVL(crd.calendar_code,m.calendar_code) = gtmp.calendar_code '
756 || ' AND m.organization_id = crd.organization_id '
757 || ' AND m.process_enabled_flag = '||''''||'Y'||''''
758 || ' AND crd.delete_mark = 0 ';
759
760 IF gmp_calendar_pkg.g_in_str_org IS NOT NULL THEN
761 ins_res_shft := ins_res_shft
762 ||' AND m.organization_id ' || gmp_calendar_pkg.g_in_str_org ;
763 END IF;
764
765 EXECUTE IMMEDIATE ins_res_shft USING p_instance_id;
766 log_message('Resource Shift Insertion Is Done' );
767
768 stmt_no := 33;
769 upd_res_avl := 'UPDATE msc_st_net_resource_avail '
770 ||' SET to_time = 86400 '
771 ||' WHERE to_time = 86399 '
772 ||' AND shift_num >= 99999 ' ;
773 EXECUTE IMMEDIATE upd_res_avl;
774
775 stmt_no := 34;
776 upd_res_avl := NULL ;
777 upd_res_avl := 'UPDATE msc_st_net_resource_avail '
778 ||' SET shift_num = (shift_num - 99999) '
779 ||' WHERE shift_num >= 99999 ' ;
780 EXECUTE IMMEDIATE upd_res_avl;
781 COMMIT ;
782
783 return_status := TRUE;
784
785 EXCEPTION
786 WHEN invalid_string_value THEN
787 log_message('APS string is Invalid, check for Error condition' );
788 return_status := FALSE;
789 WHEN NO_DATA_FOUND THEN /* B3577871 */
790 log_message(' NO_DATA_FOUND exception : Gmp_calendar_pkg.Populate_rsrc_cal ' );
791 return_status := TRUE;
792 WHEN OTHERS THEN
793 log_message('Error in Populate Rsrc cal construct: '||stmt_no);
794 log_message('Error : '||v_icode);
795 log_message(SQLERRM);
796 return_status := FALSE;
797
798 END populate_rsrc_cal; /* End of Main Procedure */
799
800 /*
801 REM+==========================================================================+
802 REM| PROCEDURE NAME |
803 REM| update_trading_partners |
804 REM| |
805 REM| Type |
806 REM| public |
807 REM| |
808 REM| DESCRIPTION |
809 REM| |
810 REM| This procedure updates the following table : |
811 REM| |
812 REM| 1. msc_st_trading_partners |
813 REM| |
814 REM| Input Parameters |
815 REM| p_org_id - Organization_id |
816 REM| p_cal_code - Calendar_code |
817 REM| |
818 REM| Output Parameters |
819 REM| None |
820 REM| |
821 REM| |
822 REM| HISTORY |
823 REM| Created 5th Aug 1999 by Sridhar Gidugu (OPM Development Oracle US) |
824 REM| 8/30/99 - Removed the existing Trading Partner Procedure and changed |
825 REM| to a single Update Procedure. |
826 REM| 10/1/99 - Changed Updating Trading Partners, |
827 REM| - Updated Organization_typw with a value 2 and changed |
828 REM| - partner_type = 3 |
829 REM| |
830 REM| |
831 REM+==========================================================================+
832 */
833 PROCEDURE update_trading_partners(p_org_id IN PLS_INTEGER,
834 p_cal_code IN varchar2,
835 return_status OUT NOCOPY BOOLEAN) IS
836 BEGIN
837
838 IF return_status THEN
839 v_cp_enabled := TRUE;
840 ELSE
841 v_cp_enabled := FALSE;
842 END IF;
843
844 -- The Following Update statement the Trading Parters table with the
845 -- Calendar Code for the Organization that uses the Calendar.
846 UPDATE MSC_ST_TRADING_PARTNERS
847 -- No need to update calendar code B5926204 Rajesh Patangya
848 -- SET calendar_code = p_cal_code,
849 SET organization_type = 2
850 WHERE sr_tp_id = p_org_id
851 AND partner_type = 3;
852
853
854 return_status := TRUE;
855 EXCEPTION
856 WHEN OTHERS THEN
857 log_message('Failure:Trading Partners Update Occured ');
858 return_status := FALSE;
859
860 end update_trading_partners; /* End of Updating Trading partners */
861
862 /*
863 REM+==========================================================================+
864 REM| PROCEDURE NAME |
865 REM| retrieve_calendar_detail |
866 REM| |
867 REM| Type |
868 REM| public |
869 REM| |
870 REM| DESCRIPTION |
871 REM| |
872 REM| |
873 REM| Input Parameters |
874 REM| p_orgn_code - Orgn Code |
875 REM| p_org_id - Organization id |
876 REM| p_cal_id - calendar_id |
877 REM| p_instance_id - Instance Id |
878 REM| p_delimiter - Delimiter |
879 REM| p_db_link - Data Base Link |
880 REM| |
881 REM| Output Parameters |
882 REM| return_status |
883 REM| |
884 REM| |
885 REM| HISTORY |
886 REM| Created 5th Aug 1999 by Sridhar Gidugu (OPM Development Oracle US) |
887 REM| 9/20/99 - created the Retrieve calendar Procedure |
888 REM| 10/13/99 - Added deleted_flag in the insert statement |
889 REM| 10/18/99 - Changed value of Exception set Id from 1 to -1 |
890 REM| 12/09/99 - Added Code to include all Calendar Days |
891 REM| 12/17/99 - Fixed Code for Bug# 1117565 |
892 REM| 02/01/00 - next seq and prior seqs are made same as seq number in |
893 REM| - msc_calendar_dates insert, bug#1175906 |
894 REM| - similarly for next date and prior date are same as calendar|
895 REM| - dates |
896 REM| 03/01/00 - Added Code to not to include rows which have |
897 REM| shift_duration as zero seconds - Bug#1221285 |
898 REM| 03/20/03 - Added Inserts to msc_st_shift_times table - 2213101 |
899 REM| 03/20/03 - Added Inserts to msc_st_shift_dates table - 2213101 |
900 REM| |
901 REM| |
902 REM+==========================================================================+
903 */
904 PROCEDURE retrieve_calendar_detail( p_calendar_code IN VARCHAR2,
905 p_cal_desc IN VARCHAR2,
906 p_run_date IN DATE,
907 p_db_link IN VARCHAR2,
908 p_instance_id IN PLS_INTEGER,
909 p_usage IN VARCHAR2,
910 return_status OUT NOCOPY BOOLEAN) IS
911 n_calendar_code varchar2(40);
912 cal_count number ;
913 cal_start_date date;
914 cal_end_date date;
915 get_shift_time varchar2(15000);
916 sql_cal varchar2(15000);
917 cal_cur ref_cursor_typ;
918 i integer ;
919 j integer ;
920 stmt_no integer ;
921 wps_index integer ;
922 ins_stmt VARCHAR2(10000) ;
923 ins_stmt1 VARCHAR2(10000) ;
924 temp_from_date DATE ;
925 temp_to_date DATE ;
926 temp_to_time NUMBER ;
927 temp_shift_num NUMBER ;
928
929 TYPE w_st_dt IS TABLE OF bom_cal_week_start_dates.week_start_date%TYPE;
930 week_st_date w_st_dt ;
931
932 TYPE w_next_dt IS TABLE OF bom_cal_week_start_dates.week_start_date%TYPE;
933 week_next_date w_next_dt ;
934
935 BEGIN
936 /* 12/13/02 - Rajesh Patangya B2710601, Added database link */
937 i := 0;
938 j := 0;
939 cal_count := 0;
940 wps_index := 0;
941 ins_stmt := null;
942 get_shift_time := null;
943 sql_cal := null;
944 ins_stmt := null;
945 ins_stmt1 := null;
946 temp_from_date := null ;
947 temp_to_date := null ;
948 temp_to_time := 0 ;
949 temp_shift_num := 0 ;
950
951 /* Insert for Net Resource starts here, The following select statement gets
952 the period that are availble for a given calendar, From time and To Time
953 are taken in seconds here.
954 */
955
956 IF return_status
957 THEN
958 v_cp_enabled := TRUE;
959 ELSE
960 v_cp_enabled := FALSE;
961 END IF;
962
963 sql_cal := ' SELECT sd.shift_date calendar_date, '
964 || ' sd.shift_num shift_no, '
965 || ' st.from_time from_time, '
966 || ' st.to_time to_time '
967 || ' FROM bom_calendars'||p_db_link||' cal, '
968 || ' bom_shift_dates'||p_db_link||' sd, '
969 || ' bom_shift_times'||p_db_link||' st '
970 || ' WHERE cal.calendar_code = :curr_cal_code '
971 || ' AND sd.calendar_code = cal.calendar_code '
972 || ' AND st.calendar_code = sd.calendar_code '
973 || ' AND sd.shift_num = st.shift_num '
974 || ' AND sd.seq_num is not null '
975 || ' ORDER BY calendar_date,from_time,to_time ';
976
977 IF new_rec.COUNT > 0
978 THEN
979 new_rec.delete;
980 END IF;
981
982 /*Sowmya - nra_enabled changes - added this chk as the code is
983 passed in as description here only*/
984
985 stmt_no := 10;
986 IF p_usage ='APS' THEN
987 /*Sowmya - Also introduced a new variable to hold the calendar code to
988 be inserted in gtmp table. The gtmp table will have the calendar code
989 thats not associated to the instance. This change has been
990 done esp. when the user choses to generate and collect the resource
991 data. */
992 n_calendar_code := p_cal_desc;
993 OPEN cal_cur FOR sql_cal USING p_cal_desc;
994 -- log_message('For APS means gmp +MSC rsce avail table');
995 -- log_message(n_calendar_code || '-' || p_cal_desc);
996 ELSE
997 log_message('For WPS means gmp reosurce avail only table');
998 log_message(p_calendar_code || '-' || p_cal_desc);
999 n_calendar_code := p_calendar_code;
1000 OPEN cal_cur FOR sql_cal USING p_calendar_code;
1001 END IF;
1002
1003 stmt_no := 20;
1004 i := 0;
1005 LOOP
1006 FETCH cal_cur INTO calendar_record;
1007 EXIT WHEN cal_cur%NOTFOUND;
1008
1009 /* Check for the First record */
1010 IF i = 0 THEN
1011
1012 /* Check if the first row to time is spilling over */
1013 IF calendar_record.to_time < calendar_record.from_time THEN
1014 i := i + 1;
1015 new_rec(i).cal_date := calendar_record.cal_date ;
1016 log_message(calendar_record.cal_date);
1017 new_rec(i).shift_num := calendar_record.shift_num ;
1018 new_rec(i).from_time := calendar_record.from_time;
1019 new_rec(i).to_time := no_of_secs ;
1020
1021 /* Add more record for the spilled over shift */
1022
1023 i := i +1 ;
1024 new_rec(i).cal_date := calendar_record.cal_date + 1 ;
1025 new_rec(i).shift_num := calendar_record.shift_num ;
1026 new_rec(i).from_time := 0 ;
1027 new_rec(i).to_time := calendar_record.to_time;
1028 ELSE
1029 /* Else Store the values in the PL/sql table */
1030
1031 i := i + 1;
1032 new_rec(i).cal_date := calendar_record.cal_date ;
1033 new_rec(i).shift_num := calendar_record.shift_num ;
1034 new_rec(i).from_time := calendar_record.from_time;
1035 new_rec(i).to_time := calendar_record.to_time;
1036
1037 END IF;
1038
1039 /* If not the first record, then check if the Calendar date
1040 is greater than the Previous cal date in the PL/sql table */
1041 ELSE
1042 IF calendar_record.cal_date > new_rec(i).cal_date THEN
1043
1044 /* Check if the Date, to_time is spilling over */
1045 IF calendar_record.to_time < calendar_record.from_time THEN
1046 i := i + 1;
1047 new_rec(i).cal_date := calendar_record.cal_date;
1048 new_rec(i).from_time := calendar_record.from_time;
1049 new_rec(i).shift_num := calendar_record.shift_num;
1050 new_rec(i).to_time := no_of_secs;
1051
1052 /* Add more record for the spilled over shift */
1053 i := i + 1;
1054 new_rec(i).cal_date := calendar_record.cal_date + 1;
1055 new_rec(i).shift_num := calendar_record.shift_num;
1056 new_rec(i).from_time := 0;
1057 new_rec(i).to_time := calendar_record.to_time ;
1058 ELSE
1059 /* Else Store the values in the PL/sql table */
1060
1061 i := i + 1 ;
1062 new_rec(i).cal_date := calendar_record.cal_date ;
1063 new_rec(i).shift_num := calendar_record.shift_num ;
1064 new_rec(i).from_time := calendar_record.from_time;
1065 new_rec(i).to_time := calendar_record.to_time;
1066
1067 END IF;
1068
1069 /* If not the first record, then check if the Calendar date
1070 is equal to the Previous cal date in the PL/sql table */
1071
1072 ELSIF calendar_record.cal_date = new_rec(i).cal_date THEN
1073
1074 /* Checking if the Cursor from_time is greater than Previous record to_time */
1075
1076 IF calendar_record.from_time > new_rec(i).to_time THEN
1077 /* Check if the Date, to_time is spilling over */
1078 IF calendar_record.to_time < calendar_record.from_time THEN
1079 i := i + 1;
1080 new_rec(i).cal_date := calendar_record.cal_date;
1081 new_rec(i).from_time := calendar_record.from_time;
1082 new_rec(i).shift_num := calendar_record.shift_num;
1083 new_rec(i).to_time := no_of_secs;
1084
1085 /* Add more record for the spilled over shift */
1086 i := i + 1;
1087 new_rec(i).cal_date := calendar_record.cal_date + 1 ;
1088 new_rec(i).from_time := 0 ;
1089 new_rec(i).shift_num := calendar_record.shift_num;
1090 new_rec(i).to_time := calendar_record.to_time ;
1091 ELSE
1092 i := i + 1;
1093 new_rec(i).cal_date := calendar_record.cal_date ;
1094 new_rec(i).shift_num := calendar_record.shift_num ;
1095 new_rec(i).from_time := calendar_record.from_time;
1096 new_rec(i).to_time := calendar_record.to_time;
1097 END IF ;
1098 ELSE /* Merge time !!!
1099 Shifts Merge is the start time of the shift is Less than
1100 the Previous record to_time
1101 Checking if the record that is Merged is spilling Over to next day */
1102 IF calendar_record.to_time < calendar_record.from_time THEN
1103 new_rec(i).to_time := no_of_secs ;
1104 /* Add more record for the spilled over shift */
1105 i := i + 1;
1106 new_rec(i).cal_date := calendar_record.cal_date + 1;
1107 new_rec(i).from_time := 0 ;
1108 new_rec(i).shift_num := calendar_record.shift_num;
1109 new_rec(i).to_time := calendar_record.to_time ;
1110 ELSE
1111 IF calendar_record.to_time > new_rec(i).to_time THEN
1112 new_rec(i).to_time := calendar_record.to_time ;
1113 END IF ;
1114 END IF ;
1115 END IF ; /* End OF Merge time */
1116
1117 /* checking if the Calendar date is less than the Previous cal date
1118 in the PL/sql table This check is useful when two shifts in a day
1119 are crossing Midnight Then in that case we need to compare the start
1120 time with the Previously completed shift end time and the dates too. */
1121
1122 ELSIF calendar_record.cal_date < new_rec(i).cal_date THEN
1123 IF calendar_record.to_time > no_of_secs THEN
1124 IF calendar_record.to_time - no_of_secs > new_rec(i).to_time THEN
1125 new_rec(i).to_time := calendar_record.to_time - no_of_secs ;
1126 END IF;
1127 END IF ;
1128
1129 END IF ; /* End if for date check */
1130 END IF; /* End if for i = 0 */
1131
1132 END LOOP;
1133
1134 /* cal count gives the Number of rows after the Calendar is exploded */
1135 cal_count := new_rec.COUNT ;
1136 /* Calendar Start date and End dates are Calculated here */
1137 cal_start_date := new_rec(1).cal_date;
1138 cal_end_date := new_rec(cal_count).cal_date;
1139
1140 CLOSE cal_cur;
1141
1142 /* 12/13/02 - Rajesh Patangya B2710601, Added database link */
1143 wps_index := 1 ;
1144 ins_stmt := 'INSERT INTO gmp_calendar_detail_gtmp'||p_db_link
1145 ||' ( '
1146 ||' calendar_code, '
1147 ||' shift_num, '
1148 ||' shift_date, '
1149 ||' from_time, '
1150 ||' to_time, '
1151 ||' from_date, '
1152 ||' to_date '
1153 ||' ) '
1154 ||' VALUES '
1155 ||' ( :p1,:p2,:p3,:p4,:p5,:p6,:p7)';
1156
1157 /* ins_stmt1 := 'INSERT INTO temp_cal'||p_db_link
1158 ||' ( '
1159 ||' calendar_code, '
1160 ||' shift_num, '
1161 ||' shift_date, '
1162 ||' from_time, '
1163 ||' to_time, '
1164 ||' from_date, '
1165 ||' to_date '
1166 ||' ) '
1167 ||' VALUES '
1168 ||' ( :p1,:p2,:p3,:p4,:p5,:p6,:p7)';
1169 */
1170
1171 log_message(n_calendar_code || '-GTMP-' || new_rec.COUNT);
1172 FOR wps_index IN 1..new_rec.COUNT
1173 LOOP
1174
1175 temp_from_date := (new_rec(wps_index).cal_date +
1176 (new_rec(wps_index).from_time/86400)) ;
1177
1178 IF new_rec(wps_index).to_time = 86400 THEN
1179 temp_to_time := new_rec(wps_index).to_time - 1 ;
1180 temp_shift_num := new_rec(wps_index).shift_num + 99999 ;
1181 ELSE
1182 temp_to_time := new_rec(wps_index).to_time ;
1183 temp_shift_num := new_rec(wps_index).shift_num;
1184 END IF ;
1185
1186 temp_to_date := (new_rec(wps_index).cal_date + (temp_to_time /86400)) ;
1187
1188 EXECUTE IMMEDIATE ins_stmt USING
1189 n_calendar_code,
1190 temp_shift_num,
1191 new_rec(wps_index).cal_date,
1192 new_rec(wps_index).from_time,
1193 temp_to_time,
1194 temp_from_date,
1195 temp_to_date ;
1196 END LOOP;
1197
1198 /*TDD - Sowmya - As the calendar is a bom calendar, the calendar details will be
1199 collected by discrete collection. This package just collects msc_st_shift_times
1200 which to have cleaned calendar data.*/
1201 stmt_no := 41;
1202 IF p_usage = 'APS' THEN
1203
1204 get_shift_time := ' INSERT INTO msc_st_shift_times '
1205 || ' ( shift_num, '
1206 || ' calendar_code, '
1207 || ' from_time, '
1208 || ' to_time, '
1209 || ' deleted_flag, '
1210 || ' sr_instance_id '
1211 || ' ) '
1212 || ' SELECT distinct shift_num ,'
1213 || ' :v_calendar , '
1214 || ' from_time, '
1215 || ' to_time, '
1216 || ' 2 , '
1217 || ' :instance_id '
1218 || ' FROM gmp_calendar_detail_gtmp'||p_db_link||' gtmp '
1219 || ' WHERE calendar_code = :curr_cal_code '
1220 || ' ORDER BY shift_num,from_time,to_time ';
1221
1222 EXECUTE IMMEDIATE get_shift_time USING n_calendar_code,
1223 p_instance_id, n_calendar_code;
1224
1225 END IF ; /* End if for usage */
1226 return_status := TRUE;
1227
1228 EXCEPTION
1229 WHEN NO_DATA_FOUND THEN
1230 log_message('Calendar has no days set in the Calendar Detail : '||p_calendar_code);
1231 log_message(sqlerrm);
1232 return_status := FALSE;
1233 WHEN OTHERS THEN
1234 log_message('Error in retrieve Calendar Detail : ');
1235 log_message(sqlerrm);
1236 return_status := FALSE;
1237
1238 END retrieve_calendar_detail;
1239
1240 /*
1241 REM+==========================================================================+
1242 REM| PROCEDURE NAME |
1243 REM| net_rsrc_insert |
1244 REM| |
1245 REM| Type |
1246 REM| public |
1247 REM| |
1248 REM| DESCRIPTION |
1249 REM| |
1250 REM| |
1251 REM| Input Parameters |
1252 REM| p_org_id - Organization id |
1253 REM| p_orgn_code - Orgn Code |
1254 REM| p_calendar_id - calendar_id |
1255 REM| p_instance_id - Instance Id |
1256 REM| p_usage - Used foir APS or WPS |
1257 REM| p_db_link - Data Base Link |
1258 REM| |
1259 REM| Output Parameters |
1260 REM| return_status |
1261 REM| |
1262 REM| HISTORY |
1263 REM| Created on 4th Jan 2002 By Rajesh Patangya |
1264 REM| 7th Mar 2003 -- Performance issue fix and B2671540 00:00 shift fix |
1265 REM| B3161696 - 26-SEP-2003 TARGETTED RESOURCE AVAILABILITY PLACEHOLDER BUG |
1266 REM| |
1267 REM+==========================================================================+
1268 */
1269 PROCEDURE net_rsrc_insert(p_org_id IN PLS_INTEGER,
1270 p_orgn_code IN varchar2,
1271 p_simulation_set IN varchar2,
1272 p_db_link IN varchar2,
1273 p_instance_id IN PLS_INTEGER,
1274 p_run_date IN DATE ,
1275 p_calendar_code IN varchar2,
1276 p_usage IN varchar2,
1277 return_status OUT NOCOPY BOOLEAN) IS
1278
1279 /* Local array definition */
1280 TYPE interval_typ_r is RECORD
1281 (
1282 resource_count PLS_INTEGER,
1283 resource_id PLS_INTEGER,
1284 instance_id PLS_INTEGER,
1285 instance_number number,
1286 shift_num number,
1287 from_date date,
1288 to_date date
1289 );
1290
1291 interval_record_r interval_typ_r;
1292 ri_shift_interval ref_cursor_typ;
1293
1294 sqlstmt VARCHAR2(32700) ;
1295 sql_stmt1 VARCHAR2(32700) ;
1296 g_calendar_code VARCHAR2(10) ;
1297 stmt_no INTEGER;
1298 i INTEGER ;
1299 j INTEGER ;
1300 /* B3347284, Performance Issue */
1301 first_index NUMBER ;
1302 last_index NUMBER ;
1303 end_index NUMBER ;
1304 first_in NUMBER ;
1305 last_in NUMBER ;
1306
1307 TYPE rsrc_cnt IS TABLE OF cr_rsrc_dtl.assigned_qty%TYPE;
1308 resource_count rsrc_cnt ;
1309
1310 TYPE rsrc_id IS TABLE OF cr_rsrc_dtl.resource_id%TYPE;
1311 resource_id rsrc_id ;
1312
1313 TYPE inst_id IS TABLE OF gmp_resource_instances.instance_id%TYPE;
1314 instance_id inst_id ;
1315
1316 TYPE inst_num IS TABLE OF gmp_resource_instances.instance_number%TYPE;
1317 instance_number inst_num ;
1318
1319 /* B3482001 - taking shift number from gmp_calendar_detail_gtmp */
1320 TYPE shift_no IS TABLE OF gmp_calendar_detail_gtmp.shift_num%TYPE;
1321 shift_num shift_no ;
1322
1323 TYPE f_dt IS TABLE OF bom_shift_dates.shift_date%TYPE;
1324 f_date f_dt ;
1325
1326 TYPE t_dt IS TABLE OF bom_shift_dates.shift_date%TYPE;
1327 t_date t_dt ;
1328
1329 BEGIN
1330
1331 sqlstmt := NULL;
1332 sql_stmt1 := NULL;
1333 g_calendar_code := NULL;
1334 stmt_no := 0 ;
1335 i := 1;
1336 j := 1;
1337 first_index := 0 ;
1338 last_index := 0 ;
1339 end_index := 0 ;
1340 first_in := 0 ;
1341 last_in := 0 ;
1342
1343 stmt_no := 72;
1344 -- Rajesh Patangya B4724360, When the calendar is not assigned to
1345 -- resource then organization calendar should be considered
1346 sqlstmt := ' SELECT calendar_code '
1347 || ' FROM mtl_parameters'||p_db_link
1348 || ' WHERE organization_id = :orgn_id1 ';
1349
1350 EXECUTE IMMEDIATE sqlstmt INTO g_calendar_code USING p_org_id ;
1351
1352 IF g_calendar_code IS NULL THEN
1353 log_message('Warning : '||p_org_id||
1354 ' does not have manufacturing calendar, continuing ...') ;
1355 END IF;
1356
1357 /* Interval Cursor gives the all the point of inflections */
1358
1359 /*sowsubra - resource model ME changes - The orgn_code in cr_rsrc_dtl
1360 has been replaced with the organization_id in all the below union
1361 statements */
1362
1363 stmt_no := 73;
1364 sql_stmt1 := ' SELECT /*+ ALL_ROWS */ '
1365 || ' decode(rt.interval_date,rt.lead_idate,rt.assigned_qty,decode(rt.rsum,0,rt.assigned_qty,rt.assigned_qty-rt.rsum)) resource_count '
1366 || ' ,rt.resource_id '
1367 || ' ,0 instance_id '
1368 || ' ,0 instance_number '
1369 || ' ,rt.shift_num '
1370 || ' ,rt.interval_date from_date '
1371 || ' ,rt.lead_idate to_date '
1372 || ' FROM '
1373 || ' ( '
1374 || ' SELECT '
1375 || ' t.resource_id '
1376 || ' ,t.shift_num '
1377 || ' ,t.interval_date '
1378 || ' ,t.assigned_qty '
1379 || ' ,nvl(sum(u.resource_units),0) rsum '
1380 || ' ,max(t.lead_idate) lead_idate '
1381 || ' FROM '
1382 || ' ( '
1383 || ' SELECT unique resource_id,instance_number,from_date, '
1384 || ' to_date to_date1,resource_units '
1385 || ' FROM ( '
1386 || ' SELECT un.resource_id, '
1387 || ' gri.instance_number, '
1388 || ' un.from_date, '
1389 || ' un.to_date, '
1390 || ' 1 resource_units'
1391 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
1392 || ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
1393 || ' gmp_resource_instances'||p_db_link||' gri '
1394 || ' WHERE crd.resource_id = un.resource_id '
1395 || ' AND crd.resource_id = gri.resource_id '
1396 || ' AND un.instance_id = gri.instance_id '
1397 || ' AND crd.schedule_ind = 2 '
1398 || ' AND crd.delete_mark = 0 '
1399 || ' AND crd.organization_id = :orgn_id1 '
1400 || ' AND nvl(crd.calendar_code,:g_default_code1)=:l_cal_code1';
1401
1402 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
1403 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc ' ;
1404 END IF ;
1405
1406 sql_stmt1 := sql_stmt1
1407 || ' AND nvl(un.instance_id,0) <> 0 '
1408 || ' UNION ALL '
1409 || ' SELECT un.resource_id, '
1410 || ' gri.instance_number, '
1411 || ' un.from_date, '
1412 || ' un.to_date, '
1413 || ' 1 resource_units'
1414 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
1415 || ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
1416 || ' gmp_resource_instances'||p_db_link||' gri '
1417 || ' WHERE crd.resource_id = un.resource_id '
1418 || ' AND crd.resource_id = gri.resource_id '
1419 || ' AND crd.delete_mark = 0 '
1420 || ' AND crd.schedule_ind = 2 '
1421 || ' AND nvl(un.instance_id,0) = 0 '
1422 || ' AND crd.organization_id = :orgn_id2 '
1423 || ' AND nvl(crd.calendar_code,:g_default_code2)=:l_cal_code2';
1424
1425 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
1426 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc ' ;
1427 END IF ;
1428
1429 sql_stmt1 := sql_stmt1
1430 || ' AND gri.instance_number in '
1431 || ' ( select tgri.instance_number '
1432 || ' FROM gmp_resource_instances'||p_db_link||' tgri '
1433 || ' WHERE tgri.resource_id = crd.resource_id '
1434 || ' AND rownum <= un.resource_units '
1435 || ' ) '
1436 || ' UNION ALL '
1437 || ' SELECT un.resource_id, '
1438 || ' 0 instance_number, '
1439 || ' un.from_date, '
1440 || ' un.to_date, '
1441 || ' un.resource_units '
1442 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
1443 || ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un'
1444 || ' WHERE crd.resource_id = un.resource_id '
1445 || ' AND crd.delete_mark = 0 '
1446 || ' AND crd.organization_id = :orgn_id3 '
1447 || ' AND nvl(crd.calendar_code,:g_default_code3)=:l_cal_code3';
1448
1449 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
1450 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc ' ;
1451 END IF ;
1452
1453 sql_stmt1 := sql_stmt1
1454 || ' AND NOT EXISTS '
1455 || ' (SELECT 1 '
1456 || ' FROM gmp_resource_instances'||p_db_link||' gri '
1457 || ' WHERE gri.resource_id = un.resource_id ) '
1458 || ' ) '
1459 || ' ) u, '
1460 || ' ( '
1461 || ' SELECT resource_id,shift_num,interval_date, '
1462 || ' assigned_qty,lead_idate '
1463 || ' FROM '
1464 || ' ( '
1465 || ' SELECT resource_id,shift_num,interval_date, '
1466 || ' assigned_qty '
1467 || ' ,lead(resource_id,1) over(order by '
1468 || ' resource_id,interval_date,shift_num) as lead_rid '
1469 || ' ,lead(interval_date,1) over(order by '
1470 || ' resource_id,interval_date,shift_num) as lead_idate '
1471 || ' ,lead(shift_num,1) over(order by '
1472 || ' resource_id,interval_date,shift_num) as lead_snum '
1473 || ' FROM '
1474 || ' ( '
1475 || ' SELECT unique cmd.resource_id, '
1476 || ' 0 , '
1477 || ' exp.shift_num, '
1478 || ' 0 , '
1479 || ' cmd.interval_date, '
1480 || ' cmd.assigned_qty '
1481 || ' FROM ( '
1482 || ' SELECT un.resource_id resource_id, '
1483 || ' gri.instance_number instance_number,'
1484 || ' 0 shift_num,'
1485 || ' 0 resource_count,'
1486 || ' un.from_date interval_date, '
1487 || ' crd.assigned_qty assigned_qty '
1488 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
1489 || ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
1490 || ' gmp_resource_instances'||p_db_link||' gri '
1491 || ' WHERE crd.resource_id = un.resource_id '
1492 || ' AND crd.resource_id = gri.resource_id '
1493 || ' AND un.instance_id = gri.instance_id '
1494 || ' AND crd.schedule_ind = 2 '
1495 || ' AND crd.delete_mark = 0 '
1496 || ' AND nvl(un.instance_id,0) <> 0 '
1497 || ' AND crd.organization_id = :orgn_id4 '
1498 || ' AND nvl(crd.calendar_code,:g_default_code4)=:l_cal_code4';
1499
1500 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
1501 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc ' ;
1502 END IF ;
1503
1504 sql_stmt1 := sql_stmt1
1505 || ' UNION ALL '
1506 || ' SELECT un.resource_id resource_id, '
1507 || ' gri.instance_number instance_number,'
1508 || ' 0 shift_num,'
1509 || ' 0 resource_count,'
1510 || ' un.to_date interval_date, '
1511 || ' crd.assigned_qty assigned_qty '
1512 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
1513 || ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
1514 || ' gmp_resource_instances'||p_db_link||' gri '
1515 || ' WHERE crd.resource_id = un.resource_id '
1516 || ' AND crd.resource_id = gri.resource_id '
1517 || ' AND un.instance_id = gri.instance_id '
1518 || ' AND crd.schedule_ind = 2 '
1519 || ' AND crd.delete_mark = 0 '
1520 || ' AND nvl(un.instance_id,0) <> 0 '
1521 || ' AND crd.organization_id = :orgn_id5 '
1522 || ' AND nvl(crd.calendar_code,:g_default_code5)=:l_cal_code5';
1523
1524 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
1525 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc ' ;
1526 END IF ;
1527
1528 sql_stmt1 := sql_stmt1
1529 || ' UNION ALL '
1530 || ' SELECT un.resource_id resource_id, '
1531 || ' gri.instance_number instance_number,'
1532 || ' 0 shift_num,'
1533 || ' 0 resource_count,'
1534 || ' un.from_date interval_date, '
1535 || ' crd.assigned_qty assigned_qty '
1536 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
1537 || ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
1538 || ' gmp_resource_instances'||p_db_link||' gri '
1539 || ' WHERE crd.resource_id = un.resource_id '
1540 || ' AND crd.resource_id = gri.resource_id '
1541 || ' AND crd.schedule_ind = 2 '
1542 || ' AND crd.delete_mark = 0 '
1543 || ' AND nvl(un.instance_id,0) = 0 '
1544 || ' AND crd.organization_id = :orgn_id6 '
1545 || ' AND nvl(crd.calendar_code,:g_default_code6)=:l_cal_code6';
1546
1547 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
1548 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc ' ;
1549 END IF ;
1550
1551 sql_stmt1 := sql_stmt1
1552 || ' AND gri.instance_number in '
1553 || ' ( select tgri.instance_number '
1554 || ' FROM gmp_resource_instances'||p_db_link||' tgri '
1555 || ' WHERE tgri.resource_id = crd.resource_id '
1556 || ' AND rownum <= un.resource_units '
1557 || ' ) '
1558 || ' UNION ALL '
1559 || ' SELECT un.resource_id resource_id, '
1560 || ' gri.instance_number instance_number,'
1561 || ' 0 shift_num,'
1562 || ' 0 resource_count,'
1563 || ' un.to_date interval_date, '
1564 || ' crd.assigned_qty assigned_qty '
1565 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
1566 || ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
1567 || ' gmp_resource_instances'||p_db_link||' gri '
1568 || ' WHERE crd.resource_id = un.resource_id '
1569 || ' AND crd.resource_id = gri.resource_id '
1570 || ' AND crd.delete_mark = 0 '
1571 || ' AND crd.schedule_ind = 2 '
1572 || ' AND nvl(un.instance_id,0) = 0 '
1573 || ' AND crd.organization_id = :orgn_id7 '
1574 || ' AND nvl(crd.calendar_code,:g_default_code7)=:l_cal_code7 ';
1575
1576 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
1577 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc7 ' ;
1578 END IF ;
1579
1580 sql_stmt1 := sql_stmt1
1581 || ' AND gri.instance_number in '
1582 || ' ( select tgri.instance_number '
1583 || ' FROM gmp_resource_instances'||p_db_link||' tgri '
1584 || ' WHERE tgri.resource_id = crd.resource_id '
1585 || ' AND rownum <= un.resource_units '
1586 || ' ) '
1587 || ' UNION ALL '
1588 || ' SELECT un.resource_id, '
1589 || ' 0 instance_number, '
1590 || ' 0 shift_num,'
1591 || ' 0 resource_count,'
1592 || ' un.from_date interval_date, '
1593 || ' crd.assigned_qty assigned_qty '
1594 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
1595 || ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un '
1596 || ' WHERE crd.resource_id = un.resource_id '
1597 || ' AND crd.delete_mark = 0 '
1598 || ' AND crd.organization_id = :orgn_id8 '
1599 || ' AND nvl(crd.calendar_code,:g_default_code8)=:l_cal_code8 ';
1600
1601 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
1602 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc8 ' ;
1603 END IF ;
1604
1605 sql_stmt1 := sql_stmt1
1606 || ' AND NOT EXISTS '
1607 || ' (SELECT 1 '
1608 || ' FROM gmp_resource_instances'||p_db_link||' gri '
1609 || ' WHERE gri.resource_id = un.resource_id ) '
1610 || ' UNION ALL '
1611 || ' SELECT un.resource_id, '
1612 || ' 0 instance_number, '
1613 || ' 0 shift_num,'
1614 || ' 0 resource_count,'
1615 || ' un.to_date interval_date, '
1616 || ' crd.assigned_qty assigned_qty '
1617 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
1618 || ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un '
1619 || ' WHERE crd.resource_id = un.resource_id '
1620 || ' AND crd.delete_mark = 0 '
1621 || ' AND crd.organization_id = :orgn_id9 '
1622 || ' AND nvl(crd.calendar_code,:g_default_code9)=:l_cal_code9';
1623
1624 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
1625 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc9 ' ;
1626 END IF ;
1627
1628 sql_stmt1 := sql_stmt1
1629 || ' AND NOT EXISTS '
1630 || ' (SELECT 1 '
1631 || ' FROM gmp_resource_instances'||p_db_link||' gri '
1632 || ' WHERE gri.resource_id = un.resource_id ) '
1633 || ' ) cmd, '
1634 || ' gmp_calendar_detail_gtmp'||p_db_link||' exp '
1635 || ' WHERE exp.calendar_code = :CAL91 '
1636 || ' AND cmd.interval_date BETWEEN '
1637 || ' exp.from_date AND exp.to_date '
1638 || ' UNION ALL '
1639 || ' SELECT crd.resource_id , '
1640 || ' 0 , '
1641 || ' exp.shift_num, '
1642 || ' 0 , '
1643 || ' exp.from_date interval_date, '
1644 || ' crd.assigned_qty assigned_qty '
1645 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
1646 || ' gmp_calendar_detail_gtmp'||p_db_link||' exp '
1647 || ' WHERE crd.delete_mark = 0 '
1648 || ' AND crd.organization_id = :orgn_id10 '
1649 || ' AND nvl(crd.calendar_code,:g_default_code10)=:l_cal_code10 ';
1650
1651 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
1652 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc10 ' ;
1653 END IF ;
1654
1655 sql_stmt1 := sql_stmt1
1656 || ' AND exp.calendar_code = :CAL101 '
1657 || ' UNION ALL '
1658 || ' SELECT crd.resource_id , '
1659 || ' 0 , '
1660 || ' exp.shift_num, '
1661 || ' 0 , '
1662 || ' exp.to_date interval_date, '
1663 || ' crd.assigned_qty assigned_qty '
1664 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
1665 || ' gmp_calendar_detail_gtmp'||p_db_link||' exp '
1666 || ' WHERE crd.delete_mark = 0 '
1667 || ' AND crd.organization_id = :orgn_id11 '
1668 || ' AND nvl(crd.calendar_code,:g_default_code11)=:l_cal_code11 ';
1669
1670 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
1671 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc and :trsrc11 ' ;
1672 END IF ;
1673
1674 sql_stmt1 := sql_stmt1
1675 || ' AND exp.calendar_code = :CAL111 ' /* Sowmya. BOM calendars */
1676 || ' ) '
1677 || ' ) '
1678 || ' WHERE '
1679 || ' resource_id = lead_rid '
1680 || ' AND trunc(interval_date) = trunc(lead_idate) '
1681 || ' AND interval_date < lead_idate '
1682 || ' AND shift_num = lead_snum '
1683 || ' ) t '
1684 || ' WHERE '
1685 || ' t.interval_date >= u.from_date(+) '
1686 || ' AND t.lead_idate <= u.to_date1 (+) '
1687 || ' AND t.resource_id = u.resource_id(+) '
1688 || ' GROUP BY '
1689 || ' t.resource_id '
1690 || ' ,t.shift_num '
1691 || ' ,t.interval_date '
1692 || ' ,t.assigned_qty '
1693 || ' ) rt '
1694 || ' WHERE '
1695 || ' (rt.interval_date = rt.lead_idate OR rt.rsum=0) '
1696 || ' OR '
1697 || ' ( rt.interval_date <> rt.lead_idate '
1698 || ' AND rt.rsum <> 0 '
1699 || ' AND rt.assigned_qty>rsum) '
1700 || ' ORDER BY 2,6,5 ';
1701
1702
1703 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
1704 -- RDP B4724360 Pass correct parameters
1705 OPEN ri_shift_interval FOR sql_stmt1 USING
1706 p_org_id,g_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC ,
1707 p_org_id,g_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC ,
1708 p_org_id,g_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC ,
1709 p_org_id,g_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC ,
1710 p_org_id,g_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC ,
1711 p_org_id,g_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC ,
1712 p_org_id,g_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC ,
1713 p_org_id,g_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC ,
1714 p_org_id,g_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC ,
1715 p_calendar_code ,
1716 p_org_id,g_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC ,
1717 p_calendar_code ,
1718 p_org_id,g_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC ,
1719 p_calendar_code ;
1720
1721 ELSE
1722
1723 -- RDP B4724360 Pass correct parameters
1724 OPEN ri_shift_interval FOR sql_stmt1 USING
1725 p_org_id,g_calendar_code,p_calendar_code,
1726 p_org_id,g_calendar_code,p_calendar_code,
1727 p_org_id,g_calendar_code,p_calendar_code,
1728 p_org_id,g_calendar_code,p_calendar_code,
1729 p_org_id,g_calendar_code,p_calendar_code,
1730 p_org_id,g_calendar_code,p_calendar_code,
1731 p_org_id,g_calendar_code,p_calendar_code,
1732 p_org_id,g_calendar_code,p_calendar_code,
1733 p_org_id,g_calendar_code,p_calendar_code,p_calendar_code,
1734 p_org_id,g_calendar_code,p_calendar_code,p_calendar_code,
1735 p_org_id,g_calendar_code,p_calendar_code,p_calendar_code ;
1736
1737 END IF;
1738
1739 /* B3347284, Performance Issue */
1740 stmt_no := 73;
1741 LOOP
1742 FETCH ri_shift_interval BULK COLLECT INTO resource_count, resource_id,
1743 instance_id, instance_number, shift_num, f_date, t_date ;
1744 EXIT WHEN ri_shift_interval%NOTFOUND;
1745 END LOOP ;
1746 CLOSE ri_shift_interval;
1747
1748 stmt_no := 74;
1749 IF (resource_id.FIRST > 0) THEN /* Only if any resource */
1750
1751 first_index := resource_id.FIRST ;
1752 last_index := resource_id.LAST ;
1753 end_index := ceil(last_index/50000) ;
1754
1755 first_in := first_index ;
1756 last_in := 50000 ;
1757
1758 IF last_index >= last_in THEN
1759 NULL ;
1760 ELSE
1761 last_in := last_index ;
1762 END IF;
1763
1764 FOR j IN first_index..end_index LOOP
1765
1766 IF (p_usage = 'WPS') THEN /* Usage WPS */
1767
1768 FORALL i IN first_in..last_in
1769 INSERT INTO gmp_resource_avail
1770 (
1771 instance_id, organization_id, resource_id,
1772 calendar_code, resource_instance_id, shift_num,
1773 shift_date, from_time, to_time,
1774 resource_units, creation_date, created_by,
1775 last_update_date, last_updated_by, last_update_login
1776 ) VALUES
1777 (
1778 p_instance_id,
1779 p_org_id,
1780 resource_id(i),
1781 p_calendar_code,
1782 instance_id(i),
1783 shift_num(i),
1784 trunc(f_date(i)),
1785 ((f_date(i) - trunc(f_date(i))) * 86400 ),
1786 ((t_date(i) - trunc(t_date(i))) * 86400 ),
1787 resource_count(i),
1788 sysdate,
1789 FND_GLOBAL.USER_ID,
1790 sysdate,
1791 FND_GLOBAL.USER_ID,
1792 FND_GLOBAL.USER_ID
1793 );
1794
1795 END IF; /* Usage */
1796
1797 first_in := last_in + 1;
1798 last_in := last_in + 50000 ;
1799
1800 IF last_index >= last_in THEN
1801 NULL ;
1802 ELSE
1803 last_in := last_index ;
1804 END IF;
1805
1806 -- B5083216, commit ends the session, if called remotely
1807 -- COMMIT; /* Save remaining records */
1808 END LOOP ;
1809
1810 END IF; /* Only if any resource */
1811
1812 /* NAMIT_RAC */
1813 /* Moved this select statement above. This procedure will not be called
1814 for APS. So this Select statement will never get executed. To handle this,
1815 moving this select statement in procedure populate_rsrc_cal*/
1816 /* Insert for msc_st_resource_shifts Starts here - 2213101 */
1817
1818 return_status := TRUE;
1819
1820 EXCEPTION
1821 WHEN NO_DATA_FOUND THEN
1822 log_message('NO DATA FOUND exception: Gmp_calendar_pkg.net_rsrc_insert');
1823 return_status := TRUE;
1824 WHEN OTHERS THEN
1825 log_message('Error in Net Resource Insert: '||stmt_no);
1826 log_message(sqlerrm);
1827 return_status := FALSE;
1828
1829 end net_rsrc_insert;
1830
1831
1832 /*
1833 REM+==========================================================================+
1834 REM| PROCEDURE NAME |
1835 REM| insert_gmp_resource_avail |
1836 REM| |
1837 REM| Type |
1838 REM| public |
1839 REM| |
1840 REM| DESCRIPTION |
1841 REM| |
1842 REM| Input Parameters |
1843 REM| p_orgn_code - Orgn Code |
1844 REM| |
1845 REM| Output Parameters |
1846 REM| errbuf and retcode |
1847 REM| |
1848 REM| HISTORY |
1849 REM| Created on 4th Jan 2002 By Rajesh Patangya |
1850 REM| B3161696 - 26-SEP-2003 TARGETTED RESOURCE AVAILABILITY PLACEHOLDER BUG |
1851 REM| sowsubra - calendar convergence ME changes - |
1852 REM| Replace the calendar id with calendar code,orgn_code with organization_id|
1853 REM| B4724360 - 12-DEC-2005 Modified code to TO ADD TIME OR A SHIFT TO A |
1854 REM| PLANT RESOURCE |
1855 REM+==========================================================================+
1856 */
1857 PROCEDURE insert_gmp_resource_avail(errbuf OUT NOCOPY varchar2,
1858 retcode OUT NOCOPY number ,
1859 p_org_id IN PLS_INTEGER ,
1860 p_from_rsrc IN varchar2 ,
1861 p_to_rsrc IN varchar2 ,
1862 p_calendar_code IN varchar2 ) IS
1863
1864 TYPE cal_shift_typ is RECORD
1865 (
1866 calendar_no varchar2(16),
1867 calendar_desc varchar2(40)
1868 );
1869
1870 TYPE cal_shift_tab is table of cal_shift_typ index by BINARY_INTEGER;
1871 cal_shift_record cal_shift_typ;
1872 cal_shift_rec cal_shift_tab;
1873
1874 i integer ;
1875 cal_index integer ;
1876 ret_status boolean ;
1877 stmt_no integer ;
1878
1879 cal_detail_ref ref_cursor_typ;
1880 get_org_code ref_cursor_typ;
1881 delete_stmt VARCHAR2(9000) ;
1882 sql_get_cal VARCHAR2(9000) ;
1883 sql_get_orgn VARCHAR2(9000) ;
1884 sql_get_def_cal VARCHAR2(9000) ;
1885
1886 BEGIN
1887 cal_index := 1 ;
1888 i := 1 ;
1889 stmt_no := 0 ;
1890 delete_stmt := NULL ;
1891 sql_get_cal := NULL ;
1892 sql_get_orgn := NULL ;
1893 sql_get_def_cal := NULL ;
1894
1895 IF (NVL(P_FROM_RSRC,'RETRIEVE') = 'DONOT_RETRIEVE_CALENDAR') THEN
1896 -- Called for APS Regenerate
1897 V_FROM_RSRC := NULL;
1898 V_TO_RSRC := NULL ;
1899 ELSE
1900 V_FROM_RSRC := p_from_rsrc;
1901 V_TO_RSRC := p_to_rsrc ;
1902 END IF;
1903
1904
1905 stmt_no := 1 ;
1906 sql_get_orgn := ' SELECT m.organization_code '
1907 ||' FROM hr_organization_units hr, '
1908 ||' mtl_parameters m '
1909 ||' WHERE m.organization_id = :orgn_id '
1910 ||' AND m.organization_id = hr.organization_id '
1911 ||' AND nvl(hr.date_to,sysdate) >= sysdate '
1912 ||' AND m.process_enabled_flag = '||''''||'Y'||'''';
1913
1914 OPEN get_org_code FOR sql_get_orgn USING p_org_id;
1915 FETCH get_org_code INTO p_orgn_code;
1916 CLOSE get_org_code;
1917
1918
1919 stmt_no := 2 ;
1920 IF cal_shift_rec.COUNT > 0 THEN
1921 cal_shift_rec.DELETE;
1922 END IF;
1923
1924 stmt_no := 3 ;
1925 -- RDP B4724360 Case I - Calendar is blank
1926 IF p_calendar_code IS NULL THEN
1927
1928 sql_get_cal := ' SELECT DISTINCT cal.calendar_code, '
1929 ||' cal.description '
1930 ||' FROM bom_calendars cal, '
1931 ||' hr_organization_units hr, '
1932 ||' mtl_parameters m, '
1933 ||' cr_rsrc_dtl crd '
1934 ||' WHERE m.organization_id = :orgn_id '
1935 ||' AND m.organization_id = hr.organization_id '
1936 ||' AND nvl(hr.date_to,sysdate) >= sysdate '
1937 ||' AND m.process_enabled_flag = '||''''||'Y'||''''
1938 ||' AND crd.organization_id = m.organization_id '
1939 ||' AND NVL(crd.calendar_code,m.calendar_code)=cal.calendar_code '
1940 ||' AND crd.delete_mark = 0 ' ;
1941
1942 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NULL) THEN
1943 -- Case A - From Resource is entered and To Resource is blank
1944 sql_get_cal := sql_get_cal || ' AND crd.resources >= :frsrc ' ;
1945 OPEN cal_detail_ref FOR sql_get_cal USING p_org_id,v_from_rsrc ;
1946
1947 ELSE
1948 -- Case B - From and TO resources are entered
1949 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
1950 sql_get_cal := sql_get_cal || ' AND crd.resources BETWEEN :frsrc and :trsrc ';
1951 OPEN cal_detail_ref FOR sql_get_cal USING p_org_id,v_from_rsrc,v_to_rsrc ;
1952
1953 ELSIF ( v_from_rsrc IS NULL AND v_to_rsrc IS NULL) THEN
1954 -- Case C - From and TO resources are blank
1955 OPEN cal_detail_ref FOR sql_get_cal USING p_org_id;
1956 END IF ;
1957 END IF;
1958
1959 ELSE
1960 -- Case II Calendar is entered
1961 -- Case A- Both from and To resources are entered
1962
1963 sql_get_cal := ' SELECT cal.calendar_code, '
1964 ||' cal.description '
1965 ||' FROM bom_calendars cal '
1966 ||' WHERE cal.calendar_code = :cal_code ';
1967
1968 OPEN cal_detail_ref FOR sql_get_cal USING p_calendar_code ;
1969 -- log_message('Calendar Code Is Passed = ' || p_calendar_code);
1970
1971 END IF; /* Calendar_code */
1972
1973 LOOP
1974 FETCH cal_detail_ref INTO cal_shift_record;
1975 EXIT WHEN cal_detail_ref%NOTFOUND;
1976 cal_shift_rec(cal_index).calendar_no :=
1977 cal_shift_record.calendar_no;
1978 cal_shift_rec(cal_index).calendar_desc :=
1979 cal_shift_record.calendar_desc ;
1980 cal_index := cal_index + 1;
1981 END LOOP;
1982 CLOSE cal_detail_ref;
1983 log_message('The Calendar Detail Size is = ' || cal_shift_rec.COUNT);
1984
1985 FOR i in 1..cal_shift_rec.COUNT
1986 LOOP
1987
1988 stmt_no := 1 ;
1989 delete_stmt := 'DELETE FROM gmp_resource_avail '||
1990 ' WHERE CALENDAR_CODE = :cal_code ' ||
1991 ' AND organization_id = :org_id1 ';
1992
1993 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
1994 delete_stmt := delete_stmt ||' AND resource_id in (select resource_id '
1995 ||' FROM cr_rsrc_dtl '
1996 ||' WHERE organization_id = :org_id2 '
1997 ||' AND resources BETWEEN :frsrc and :trsrc ) ';
1998 EXECUTE IMMEDIATE delete_stmt USING cal_shift_rec(i).calendar_no, p_org_id,
1999 p_org_id, v_from_rsrc, v_to_rsrc;
2000
2001 ELSIF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NULL) THEN
2002 delete_stmt := delete_stmt ||' AND resource_id in (select resource_id '
2003 ||' FROM cr_rsrc_dtl '
2004 ||' WHERE organization_id = :org_id2 '
2005 ||' AND resources > :frsrc ) ';
2006 EXECUTE IMMEDIATE delete_stmt USING cal_shift_rec(i).calendar_no, p_org_id,
2007 p_org_id, v_from_rsrc;
2008
2009 ELSIF (v_from_rsrc IS NULL AND v_to_rsrc IS NULL) THEN
2010
2011 EXECUTE IMMEDIATE delete_stmt USING cal_shift_rec(i).calendar_no, p_org_id;
2012 END IF ;
2013 log_message('Deletion from Resource Avail Table is DONE');
2014
2015 IF (NVL(P_FROM_RSRC,'RETRIEVE') <> 'DONOT_RETRIEVE_CALENDAR') THEN
2016 log_message('Calling retrieve_calendar_detail');
2017 retrieve_calendar_detail(cal_shift_rec(i).calendar_no,
2018 cal_shift_rec(i).calendar_desc,
2019 null,
2020 null,
2021 null,
2022 V_WPS,
2023 ret_status) ;
2024
2025 /* Summary rows for WPS */
2026
2027 IF ret_status THEN
2028 log_message('Calling net_rsrc_insert WPS Summary Rows');
2029 ELSE
2030 log_message('FAILED retrieve_calendar_detail');
2031 END IF;
2032
2033 END IF ;
2034
2035 net_rsrc_insert(p_org_id, /*sowsubra - org_id is passed.*/
2036 p_orgn_code,
2037 null,
2038 null,
2039 0,
2040 sysdate,
2041 cal_shift_rec(i).calendar_no,
2042 V_WPS,
2043 ret_status) ;
2044
2045 -- B5083216, commit ends the session, if called remotely
2046 IF (NVL(P_FROM_RSRC,'RETRIEVE') <> 'DONOT_RETRIEVE_CALENDAR') THEN
2047 -- Called for APS Regenerate
2048 COMMIT ;
2049 END IF;
2050
2051 /* Instance number rows for WPS */
2052 IF ret_status THEN
2053 log_message('Calling net_rsrc_avail_calculate WPS Instance Rows');
2054 ELSE
2055 log_message('FAILED net_rsrc_insert WPS Summary Rows');
2056 END IF;
2057 net_rsrc_avail_calculate(null,
2058 p_org_id,
2059 cal_shift_rec(i).calendar_no,
2060 null,
2061 ret_status) ;
2062
2063 END LOOP ;
2064
2065 -- B5083216, commit ends the session, if called remotely
2066 IF (NVL(P_FROM_RSRC,'RETRIEVE') <> 'DONOT_RETRIEVE_CALENDAR') THEN
2067 -- Called for APS Regenerate
2068 COMMIT ;
2069 END IF;
2070
2071 retcode := 0 ;
2072
2073 EXCEPTION
2074 WHEN NO_DATA_FOUND THEN
2075 log_message('Manufacturing Calendar is not assigned to '|| p_orgn_code);
2076 retcode := 1 ;
2077 WHEN OTHERS THEN
2078 log_message(sqlerrm);
2079 retcode := 1 ;
2080
2081 END insert_gmp_resource_avail;
2082
2083 /*
2084 REM+==========================================================================+
2085 REM| PROCEDURE NAME |
2086 REM| net_rsrc_avail_calculate |
2087 REM| |
2088 REM| Type |
2089 REM| public |
2090 REM| |
2091 REM| DESCRIPTION |
2092 REM| |
2093 REM| Input Parameters |
2094 REM| p_nstance_id - Instance_id |
2095 REM| p_orgn_code - Orgn Code |
2096 REM| p_instance_id - Instance Id |
2097 REM| p_db_link - Data Base Link |
2098 REM| |
2099 REM| Output Parameters |
2100 REM| return_status |
2101 REM| |
2102 REM| HISTORY |
2103 REM| Created on 4th Jan 2002 By Rajesh Patangya |
2104 REM| 7th Mar 2003 -- Performance issue fix and B2671540 00:00 shift fix |
2105 REM| B3161696 - 26-SEP-2003 TARGETTED RESOURCE AVAILABILITY PLACEHOLDER BUG |
2106 REM| |
2107 REM+==========================================================================+
2108 */
2109
2110 PROCEDURE net_rsrc_avail_calculate(p_instance_id IN PLS_INTEGER,
2111 p_org_id IN PLS_INTEGER,
2112 p_calendar_code IN varchar2,
2113 p_db_link IN varchar2,
2114 return_status OUT NOCOPY BOOLEAN) IS
2115
2116 /* Local array definition */
2117 TYPE interval_typ is RECORD
2118 (
2119 resource_id PLS_INTEGER,
2120 resource_instance_id PLS_INTEGER,
2121 shift_date date,
2122 shift_num number,
2123 resource_units number,
2124 from_time number,
2125 to_time number
2126 );
2127
2128 TYPE interval_tab is table of interval_typ index by BINARY_INTEGER;
2129 interval_record interval_typ;
2130
2131 ri_assembly ref_cursor_typ;
2132 ri_shift_interval ref_cursor_typ;
2133 sql_del varchar2(32700) ;
2134 sqlupt varchar2(32700) ;
2135 sql_stmt1 varchar2(32700) ;
2136 sql_assembly varchar2(32700) ;
2137 sqlstmt varchar2(32700) ;
2138 g_calendar_code VARCHAR2(10) ;
2139 stmt_no integer ;
2140 i integer ;
2141 first_index number ;
2142 last_index number ;
2143 end_index number ;
2144 first_in number ;
2145 last_in number ;
2146
2147 TYPE rsrc_cnt IS TABLE OF cr_rsrc_dtl.assigned_qty%TYPE;
2148 resource_count rsrc_cnt ;
2149
2150 TYPE rsrc_id IS TABLE OF cr_rsrc_dtl.resource_id%TYPE;
2151 resource_id rsrc_id ;
2152
2153 TYPE inst_id IS TABLE OF gmp_resource_instances.instance_id%TYPE;
2154 instance_id inst_id ;
2155
2156 TYPE inst_num IS TABLE OF gmp_resource_instances.instance_number%TYPE;
2157 instance_number inst_num ;
2158
2159 /* B3482001 - taking shift number from gmp_calendar_detail_gtmp */
2160 TYPE shift_no IS TABLE OF gmp_calendar_detail_gtmp.shift_num%TYPE;
2161 shift_num shift_no ;
2162
2163 TYPE f_dt IS TABLE OF mr_shcl_dtl.calendar_date%TYPE;
2164 f_date f_dt ;
2165
2166 TYPE t_dt IS TABLE OF mr_shcl_dtl.calendar_date%TYPE;
2167 t_date t_dt ;
2168
2169 BEGIN
2170 sql_del := NULL;
2171 sqlupt := NULL;
2172 sql_stmt1 := NULL;
2173 sql_assembly := NULL;
2174 first_index := 0 ;
2175 last_index := 0 ;
2176 end_index := 0 ;
2177 first_in := 0 ;
2178 last_in := 0 ;
2179 stmt_no := 0 ;
2180 i := 1;
2181 g_calendar_code := NULL;
2182
2183 -- Rajesh Patangya B4724360, When the calendar is not assigned to
2184 -- resource then organization calendar should be considered
2185 sqlstmt := ' SELECT calendar_code '
2186 || ' FROM mtl_parameters'||p_db_link
2187 || ' WHERE organization_id = :orgn_id1 ';
2188
2189 EXECUTE IMMEDIATE sqlstmt INTO g_calendar_code USING p_org_id ;
2190
2191 IF g_calendar_code IS NULL THEN
2192 log_message('Warning : '||p_org_id||
2193 ' does not have manufacturing calendar, continuing ...') ;
2194 END IF;
2195
2196 /* Interval Cursor gives the all the point of inflections */
2197 /* 03/26/02 Rajesh Patangya B2282409, Filter extra resource information */
2198 stmt_no := 63;
2199 sql_stmt1 := ' SELECT /*+ ALL_ROWS */ '
2200 || ' decode(rt.interval_date,rt.lead_idate,rt.assigned_qty,'
2201 || ' (rt.assigned_qty-nvl(rt.rsum,0))) resource_count '
2202 || ' ,rt.resource_id '
2203 || ' ,rt.instance_id '
2204 || ' ,rt.shift_num '
2205 || ' ,rt.interval_date '
2206 || ' ,rt.lead_idate '
2207 || ' FROM '
2208 || ' ( '
2209 || ' SELECT '
2210 || ' t.resource_id '
2211 || ' ,t.instance_id '
2212 || ' ,t.shift_num '
2213 || ' ,t.interval_date '
2214 || ' ,t.assigned_qty '
2215 || ' ,nvl(u.resource_units,0) rsum '
2216 || ' ,max(t.lead_idate) lead_idate '
2217 || ' FROM ( '
2218 || ' SELECT unique resource_id,instance_id,from_date, '
2219 || ' to_date to_date1,resource_units '
2220 || ' FROM ( '
2221 || ' SELECT un.resource_id, '
2222 || ' gri.instance_id, '
2223 || ' un.from_date, '
2224 || ' un.to_date, '
2225 || ' 1 resource_units'
2226 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
2227 || ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
2228 || ' gmp_resource_instances'||p_db_link||' gri '
2229 || ' WHERE crd.resource_id = un.resource_id '
2230 || ' AND crd.resource_id = gri.resource_id '
2231 || ' AND un.instance_id = gri.instance_id '
2232 || ' AND crd.organization_id = :orgn_id1 '
2233 || ' AND nvl(crd.calendar_code,:g_default_cal_code1)=:l_cal_code1'
2234 || ' AND crd.schedule_ind = 2 '
2235 || ' AND crd.delete_mark = 0 '
2236 || ' AND nvl(un.instance_id,0) <> 0 ' ;
2237
2238 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
2239 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc1 and :trsrc2 ' ;
2240 END IF ;
2241
2242 sql_stmt1 := sql_stmt1
2243 || ' UNION ALL '
2244 || ' SELECT un.resource_id, '
2245 || ' gri.instance_id, '
2246 || ' un.from_date, '
2247 || ' un.to_date, '
2248 || ' 1 resource_units'
2249 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
2250 || ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
2251 || ' gmp_resource_instances'||p_db_link||' gri '
2252 || ' WHERE crd.resource_id = un.resource_id '
2253 || ' AND crd.resource_id = gri.resource_id '
2254 || ' AND crd.organization_id = :orgn_id2 '
2255 || ' AND nvl(crd.calendar_code,:g_default_cal_code2)=:l_cal_code2'
2256 || ' AND crd.delete_mark = 0 '
2257 || ' AND crd.schedule_ind = 2 '
2258 || ' AND nvl(un.instance_id,0) = 0 ' ;
2259
2260 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
2261 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc2 and :trsrc2 ' ;
2262 END IF ;
2263
2264 sql_stmt1 := sql_stmt1
2265 || ' AND gri.instance_number in '
2266 || ' ( select tgri.instance_number '
2267 || ' FROM gmp_resource_instances'||p_db_link||' tgri '
2268 || ' WHERE tgri.resource_id = crd.resource_id '
2269 || ' AND rownum <= un.resource_units '
2270 || ' ) '
2271 || ' ) '
2272 || ' ) u, '
2273 || ' ( '
2274 || ' SELECT resource_id,instance_id, shift_num, '
2275 || ' interval_date,assigned_qty,lead_idate '
2276 || ' FROM '
2277 || ' ( '
2278 || ' SELECT '
2279 || ' resource_id,instance_id,shift_num, '
2280 || ' interval_date,1 assigned_qty, '
2281 || ' lead(resource_id,1) over(order by '
2282 || ' resource_id,instance_id,interval_date,shift_num) as lead_rid, '
2283 || ' lead(instance_id,1) over(order by '
2284 || ' resource_id,instance_id,interval_date,shift_num) as lead_iid, '
2285 || ' lead(interval_date,1) over(order by '
2286 || ' resource_id,instance_id,interval_date,shift_num) as lead_idate, '
2287 || ' lead(shift_num,1) over(order by '
2288 || ' resource_id,instance_id,interval_date,shift_num) as lead_snum '
2289 || ' FROM '
2290 || ' ( '
2291 || ' SELECT unique cmd.resource_id, '
2292 || ' cmd.instance_id, '
2293 || ' exp.shift_num, '
2294 || ' 1 , '
2295 || ' cmd.interval_date '
2296 || ' FROM ( '
2297 || ' SELECT un.resource_id resource_id, '
2298 || ' gri.instance_id instance_id,'
2299 || ' 0 shift_num,'
2300 || ' 1 resource_count,'
2301 || ' un.from_date interval_date '
2302 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
2303 || ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
2304 || ' gmp_resource_instances'||p_db_link||' gri '
2305 || ' WHERE crd.resource_id = un.resource_id '
2306 || ' AND crd.resource_id = gri.resource_id '
2307 || ' AND un.instance_id = gri.instance_id '
2308 || ' AND crd.organization_id = :orgn_id3 '
2309 || ' AND nvl(crd.calendar_code,:g_default_cal_code3)=:l_cal_code3'
2310 || ' AND crd.schedule_ind = 2 '
2311 || ' AND crd.delete_mark = 0 '
2312 || ' AND nvl(un.instance_id,0) <> 0 ' ;
2313
2314 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
2315 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc3 and :trsrc3 ' ;
2316 END IF ;
2317
2318 sql_stmt1 := sql_stmt1
2319 || ' UNION ALL '
2320 || ' SELECT un.resource_id resource_id, '
2321 || ' gri.instance_id instance_id,'
2322 || ' 0 shift_num,'
2323 || ' 1 resource_count,'
2324 || ' un.to_date interval_date '
2325 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
2326 || ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
2327 || ' gmp_resource_instances'||p_db_link||' gri '
2328 || ' WHERE crd.resource_id = un.resource_id '
2329 || ' AND crd.resource_id = gri.resource_id '
2330 || ' AND un.instance_id = gri.instance_id '
2331 || ' AND crd.organization_id = :orgn_id4 '
2332 || ' AND nvl(crd.calendar_code,:g_default_cal_code4)=:l_cal_code4'
2333 || ' AND crd.schedule_ind = 2 '
2334 || ' AND crd.delete_mark = 0 '
2335 || ' AND nvl(un.instance_id,0) <> 0 ' ;
2336
2337 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
2338 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc4 and :trsrc4 ' ;
2339 END IF ;
2340
2341 sql_stmt1 := sql_stmt1
2342 || ' UNION ALL '
2343 || ' SELECT un.resource_id resource_id, '
2344 || ' gri.instance_id instance_id,'
2345 || ' 0 shift_num,'
2346 || ' 1 resource_count,'
2347 || ' un.from_date interval_date '
2348 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
2349 || ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
2350 || ' gmp_resource_instances'||p_db_link||' gri '
2351 || ' WHERE crd.resource_id = un.resource_id '
2352 || ' AND crd.resource_id = gri.resource_id '
2353 || ' AND crd.organization_id = :orgn_id5 '
2354 || ' AND nvl(crd.calendar_code,:g_default_cal_code5)=:l_cal_code5'
2355 || ' AND crd.schedule_ind = 2 '
2356 || ' AND crd.delete_mark = 0 '
2357 || ' AND nvl(un.instance_id,0) = 0 ' ;
2358
2359 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
2360 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc5 and :trsrc5 ' ;
2361 END IF ;
2362
2363 sql_stmt1 := sql_stmt1
2364 || ' AND gri.instance_number in '
2365 || ' ( select tgri.instance_number '
2366 || ' FROM gmp_resource_instances'||p_db_link||' tgri '
2367 || ' WHERE tgri.resource_id = crd.resource_id '
2368 || ' AND rownum <= un.resource_units '
2369 || ' ) '
2370 || ' UNION ALL '
2371 || ' SELECT un.resource_id resource_id, '
2372 || ' gri.instance_id instance_id,'
2373 || ' 0 shift_num,'
2374 || ' 1 resource_count,'
2375 || ' un.to_date interval_date '
2376 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
2377 || ' gmp_rsrc_unavail_dtl_v'||p_db_link||' un, '
2378 || ' gmp_resource_instances'||p_db_link||' gri '
2379 || ' WHERE crd.resource_id = un.resource_id '
2380 || ' AND crd.resource_id = gri.resource_id '
2381 || ' AND crd.organization_id = :orgn_id6 '
2382 || ' AND nvl(crd.calendar_code,:g_default_cal_code6)=:l_cal_code6'
2383 || ' AND crd.delete_mark = 0 '
2384 || ' AND crd.schedule_ind = 2 '
2385 || ' AND nvl(un.instance_id,0) = 0 ' ;
2386
2387 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
2388 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc6 and :trsrc6 ' ;
2389 END IF ;
2390
2391 sql_stmt1 := sql_stmt1
2392 || ' AND gri.instance_number in '
2393 || ' ( select tgri.instance_number '
2394 || ' FROM gmp_resource_instances'||p_db_link||' tgri '
2395 || ' WHERE tgri.resource_id = crd.resource_id '
2396 || ' AND rownum <= un.resource_units '
2397 || ' ) '
2398 || ' ) cmd, '
2399 || ' gmp_calendar_detail_gtmp'||p_db_link||' exp '
2400 || ' WHERE exp.calendar_code = :curr_cal1 '
2401 || ' AND cmd.interval_date BETWEEN '
2402 || ' exp.from_date AND exp.to_date '
2403 || ' UNION ALL '
2404 || ' SELECT crd.resource_id , '
2405 || ' gri.instance_id, '
2406 || ' exp.shift_num, '
2407 || ' 1 , '
2408 || ' (exp.shift_date + '
2409 || ' (exp.from_time/86400)) interval_date '
2410 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
2411 || ' gmp_calendar_detail_gtmp'||p_db_link||' exp, '
2412 || ' gmp_resource_instances'||p_db_link||' gri '
2413 || ' WHERE crd.organization_id = :orgn_id7 '
2414 || ' AND nvl(crd.calendar_code,:g_default_cal_code7)=:l_cal_code7'
2415 || ' AND exp.calendar_code = :curr_cal2 '
2416 || ' AND crd.resource_id = gri.resource_id '
2417 || ' AND crd.delete_mark = 0 '
2418 || ' AND crd.schedule_ind = 2 ' ;
2419
2420 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
2421 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc7 and :trsrc7 ' ;
2422 END IF ;
2423
2424 sql_stmt1 := sql_stmt1
2425 || ' UNION ALL '
2426 || ' SELECT crd.resource_id , '
2427 || ' gri.instance_id, '
2428 || ' exp.shift_num, '
2429 || ' 1 , '
2430 || ' (exp.shift_date + '
2431 || ' (exp.to_time/86400)) interval_date '
2432 || ' FROM cr_rsrc_dtl'||p_db_link||' crd, '
2433 || ' gmp_calendar_detail_gtmp'||p_db_link||' exp, '
2434 || ' gmp_resource_instances'||p_db_link||' gri '
2435 || ' WHERE crd.organization_id = :orgn_id8 '
2436 || ' AND nvl(crd.calendar_code,:g_default_cal_code8)=:l_cal_code8'
2437 || ' AND exp.calendar_code = :curr_cal3 '
2438 || ' AND crd.resource_id = gri.resource_id '
2439 || ' AND crd.delete_mark = 0 ' ;
2440
2441 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
2442 sql_stmt1 := sql_stmt1 || ' AND crd.resources BETWEEN :frsrc8 and :trsrc8 ' ;
2443 END IF ;
2444
2445 sql_stmt1 := sql_stmt1
2446 || ' AND crd.schedule_ind = 2 '
2447 || ' ) '
2448 || ' ) '
2449 || ' WHERE resource_id = lead_rid '
2450 || ' AND instance_id = lead_iid '
2451 || ' AND trunc(interval_date) = trunc(lead_idate) '
2452 || ' AND interval_date < lead_idate '
2453 || ' AND shift_num = lead_snum '
2454 || ' ) t '
2455 || ' WHERE '
2456 || ' t.interval_date >= u.from_date(+) '
2457 || ' AND t.lead_idate <= u.to_date1 (+) '
2458 || ' AND t.resource_id = u.resource_id(+) '
2459 || ' AND t.instance_id = u.instance_id(+) '
2460 || ' GROUP BY '
2461 || ' t.resource_id '
2462 || ' ,t.instance_id '
2463 || ' ,t.shift_num '
2464 || ' ,t.interval_date '
2465 || ' ,u.resource_units '
2466 || ' ,t.assigned_qty '
2467 || ' ) rt '
2468 || ' WHERE '
2469 || ' decode(rt.interval_date,rt.lead_idate,rt.assigned_qty,'
2470 || ' (rt.assigned_qty - nvl(rt.rsum,0))) > 0 '
2471 || ' ORDER BY rt.resource_id ,rt.instance_id, '
2472 || ' rt.interval_date,rt.shift_num ' ;
2473
2474 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
2475
2476 -- HW B4309093 Pass correct parameters
2477 OPEN ri_shift_interval FOR sql_stmt1 USING
2478 p_org_id,p_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC,
2479 p_org_id,p_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC,
2480 p_org_id,p_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC,
2481 p_org_id,p_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC,
2482 p_org_id,p_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC,
2483 p_org_id,p_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC,
2484 p_calendar_code,
2485 p_org_id,p_calendar_code,p_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC,
2486 p_org_id,p_calendar_code,p_calendar_code,p_calendar_code,V_FROM_RSRC, V_TO_RSRC;
2487
2488 ELSE
2489
2490 -- HW B4309093 Pass correct parameters
2491 OPEN ri_shift_interval FOR sql_stmt1 USING
2492 p_org_id,g_calendar_code,p_calendar_code,
2493 p_org_id,g_calendar_code,p_calendar_code,
2494 p_org_id,g_calendar_code,p_calendar_code,
2495 p_org_id,g_calendar_code,p_calendar_code,
2496 p_org_id,g_calendar_code,p_calendar_code,
2497 p_org_id,g_calendar_code,p_calendar_code,p_calendar_code,
2498 p_org_id,g_calendar_code,p_calendar_code,p_calendar_code,
2499 p_org_id,g_calendar_code,p_calendar_code,p_calendar_code ;
2500
2501 END IF;
2502
2503 /* B3347284, Performance Issue */
2504 stmt_no := 664;
2505 LOOP
2506 FETCH ri_shift_interval BULK COLLECT INTO resource_count, resource_id,
2507 instance_id, shift_num, f_date, t_date ;
2508 EXIT WHEN ri_shift_interval%NOTFOUND;
2509 END LOOP ;
2510 CLOSE ri_shift_interval;
2511
2512 stmt_no := 665;
2513
2514 IF resource_id.FIRST > 0 OR resource_id.LAST > 0 THEN
2515
2516 first_index := resource_id.FIRST ;
2517 last_index := resource_id.LAST ;
2518 end_index := ceil(last_index/50000) ;
2519
2520 first_in := first_index ;
2521 last_in := 50000 ;
2522
2523 IF last_index >= last_in THEN
2524 NULL ;
2525 ELSE
2526 last_in := last_index ;
2527 END IF;
2528
2529 /*sowsubra - calendar convergence ME changes - organization_id is used in place
2530 of organization code in gmp_resource_avail*/
2531
2532 FOR j IN first_index..end_index LOOP
2533
2534 FORALL i IN first_in..last_in
2535 INSERT INTO gmp_resource_avail
2536 (
2537 instance_id, organization_id, resource_id,
2538 calendar_code, resource_instance_id, shift_num,
2539 shift_date, from_time, to_time,
2540 resource_units, creation_date, created_by,
2541 last_update_date, last_updated_by, last_update_login
2542 ) VALUES
2543 (
2544 p_instance_id,
2545 p_org_id,
2546 resource_id(i),
2547 p_calendar_code,
2548 instance_id(i),
2549 shift_num(i),
2550 trunc(f_date(i)),
2551 ((f_date(i) - trunc(f_date(i))) * 86400 ),
2552 ((t_date(i) - trunc(t_date(i))) * 86400 ),
2553 resource_count(i),
2554 sysdate,
2555 FND_GLOBAL.USER_ID,
2556 sysdate,
2557 FND_GLOBAL.USER_ID,
2558 FND_GLOBAL.USER_ID
2559 ) ;
2560
2561 first_in := last_in + 1;
2562 last_in := last_in + 50000 ;
2563
2564 IF last_index >= last_in THEN
2565 NULL ;
2566 ELSE
2567 last_in := last_index ;
2568 END IF;
2569
2570 -- B5083216, commit ends the session, if called remotely
2571 -- COMMIT ;
2572 END LOOP ;
2573
2574 END IF;
2575
2576 /* This logic introduced for Net resource availablility to
2577 write consolidated rows once final available rows are in place */
2578 stmt_no := 666;
2579 sql_assembly := ' SELECT /*+ ALL_ROWS */ '
2580 || ' net.resource_id , '
2581 || ' net.resource_instance_id, '
2582 || ' net.shift_date , '
2583 || ' net.shift_num , '
2584 || ' net.resource_units , '
2585 || ' min(net.from_time) from_time, '
2586 || ' max(net.lead_tt) to_time '
2587 || ' FROM ( '
2588 || ' SELECT resource_id , '
2589 || ' resource_instance_id, '
2590 || ' shift_date , '
2591 || ' shift_num , '
2592 || ' from_time , '
2593 || ' to_time , '
2594 || ' resource_units , '
2595 || ' lead(resource_id,1) over(order by resource_id,resource_instance_id, '
2596 || ' shift_date, shift_num,from_time,to_time,resource_units) as lead_rid, '
2597 || ' lead(resource_instance_id,1) over(order by resource_id, '
2598 || ' resource_instance_id, '
2599 || ' shift_date, shift_num,from_time,to_time,resource_units) as lead_iid, '
2600 || ' lead(shift_date,1) over(order by resource_id,resource_instance_id, '
2601 || ' shift_date, shift_num,from_time,to_time,resource_units) as lead_sdt, '
2602 || ' lead(shift_num,1) over(order by resource_id,resource_instance_id, '
2603 || ' shift_date, shift_num,from_time,to_time,resource_units) as lead_sn, '
2604 || ' lead(from_time,1) over(order by resource_id,resource_instance_id, '
2605 || ' shift_date, shift_num,from_time,to_time,resource_units) as lead_ft, '
2606 || ' lead(to_time,1) over(order by resource_id,resource_instance_id, '
2607 || ' shift_date, shift_num,from_time,to_time,resource_units) as lead_tt, '
2608 || ' lead(resource_units,1) over(order by resource_id, '
2609 || ' resource_instance_id, '
2610 || ' shift_date, shift_num,from_time,to_time,resource_units) as lead_rc '
2611 || ' FROM gmp_resource_avail'
2612 || ' WHERE organization_id = :orgn_id1 '
2613 || ' AND calendar_code = :cal_code ' ;
2614
2615 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
2616
2617 sql_assembly := sql_assembly ||' AND resource_id in (select resource_id '
2618 ||' from cr_rsrc_dtl'||p_db_link
2619 ||' WHERE organization_id = :orgn_id2 '
2620 ||' AND resources BETWEEN :frsrc and :trsrc )' ;
2621 END IF ;
2622
2623 sql_assembly := sql_assembly || ' ) net '
2624 || ' WHERE net.resource_id = net.lead_rid '
2625 || ' AND net.resource_instance_id = net.lead_iid '
2626 || ' AND net.shift_num = net.lead_sn '
2627 || ' AND net.shift_date = net.lead_sdt '
2628 || ' AND net.to_time = net.lead_ft '
2629 || ' AND net.resource_units = net.lead_rc '
2630 || ' GROUP BY '
2631 || ' net.resource_id , '
2632 || ' net.resource_instance_id , '
2633 || ' net.shift_date , '
2634 || ' net.shift_num , '
2635 || ' net.resource_units ' ;
2636
2637 stmt_no := 66;
2638
2639 IF (v_from_rsrc IS NOT NULL AND v_to_rsrc IS NOT NULL) THEN
2640 OPEN ri_assembly FOR sql_assembly USING p_org_id, p_calendar_code,
2641 p_org_id, v_from_rsrc, v_to_rsrc ;
2642 ELSE
2643 OPEN ri_assembly FOR sql_assembly USING p_org_id, p_calendar_code ;
2644 END IF;
2645
2646 LOOP
2647 FETCH ri_assembly INTO interval_record;
2648 EXIT WHEN ri_assembly%NOTFOUND;
2649
2650 sql_del := 'DELETE FROM gmp_resource_avail'
2651 || ' WHERE organization_id = :org_id1 '
2652 || ' AND calendar_code = :cal_code '
2653 || ' AND resource_id = :prid '
2654 || ' AND resource_instance_id = :piid '
2655 || ' AND shift_date = :psdt '
2656 || ' AND shift_num = :psn '
2657 || ' AND from_time >= :pft '
2658 || ' AND to_time <= :ptt '
2659 || ' AND resource_units = :prc ' ;
2660
2661 stmt_no := 67;
2662 EXECUTE immediate sql_del USING
2663 p_org_id , p_calendar_code ,
2664 interval_record.resource_id,
2665 interval_record.resource_instance_id,
2666 trunc(interval_record.shift_date),
2667 interval_record.shift_num,
2668 interval_record.from_time, interval_record.to_time,
2669 interval_record.resource_units ;
2670
2671 stmt_no := 68;
2672 net_rsrc_avail_insert(
2673 p_instance_id,
2674 p_org_id,
2675 interval_record.resource_instance_id,
2676 p_calendar_code,
2677 interval_record.resource_id,
2678 interval_record.resource_units,
2679 interval_record.shift_num,
2680 interval_record.shift_date,
2681 interval_record.from_time,
2682 interval_record.to_time
2683 );
2684
2685 -- B5083216, commit ends the session, if called remotely
2686 -- COMMIT ;
2687 END LOOP;
2688 CLOSE ri_assembly;
2689
2690 stmt_no := 69;
2691 sqlupt := 'UPDATE gmp_resource_avail'
2692 ||' SET to_time = 86400 '
2693 ||' WHERE to_time = 86399 '
2694 ||' AND shift_num >= 99999 ' ;
2695
2696 EXECUTE immediate sqlupt ;
2697 sqlupt := null ;
2698 sqlupt := 'UPDATE gmp_resource_avail'
2699 ||' SET shift_num = (shift_num - 99999) '
2700 ||' WHERE shift_num >= 99999 ' ;
2701
2702 EXECUTE immediate sqlupt ;
2703 -- B5083216, commit ends the session, if called remotely
2704 -- COMMIT ;
2705
2706 return_status := TRUE ;
2707 EXCEPTION
2708 WHEN NO_DATA_FOUND THEN
2709 log_message('NO DATA FOUND exception: net_rsrc_avail_calculate');
2710 return_status := TRUE;
2711 WHEN OTHERS THEN
2712 log_message('Error in Net Resource Instance Insert: '||stmt_no);
2713 log_message(sqlerrm);
2714 return_status := FALSE ;
2715
2716 end net_rsrc_avail_calculate;
2717
2718 /*
2719 REM+==========================================================================+
2720 REM| PROCEDURE NAME |
2721 REM| net_rsrc_avail_insert |
2722 REM| |
2723 REM| Type |
2724 REM| public |
2725 REM| |
2726 REM| DESCRIPTION |
2727 REM| The following procedure inserts rows into gmp_resource_avail |
2728 REM| |
2729 REM| Input Parameters |
2730 REM| p_instance_id - Instance Id |
2731 REM| p_orgn_code - Plant Code |
2732 REM| p_resource_instance_id - Resource Instance Id |
2733 REM| p_Calendar_id - Calendar id |
2734 REM| p_resource_id - Resource Id |
2735 REM| p_assigned_qty - Resource units |
2736 REM| p_shift_num - Shift number |
2737 REM| p_calendar_date - Calendar date |
2738 REM| p_from_time - shift starting time |
2739 REM| p_to_time - Shift Ending time |
2740 REM| |
2741 REM| Output Parameters |
2742 REM| None |
2743 REM| |
2744 REM| HISTORY |
2745 REM| Created on 4th Jan 2002 By Rajesh Patangya |
2746 REM| |
2747 REM+==========================================================================+
2748 */
2749
2750 PROCEDURE net_rsrc_avail_insert(p_instance_id IN PLS_INTEGER,
2751 p_org_id IN PLS_INTEGER,
2752 p_resource_instance_id IN PLS_INTEGER,
2753 p_calendar_code IN VARCHAR2,
2754 p_resource_id IN PLS_INTEGER,
2755 p_assigned_qty IN NUMBER,
2756 p_shift_num IN PLS_INTEGER,
2757 p_calendar_date IN DATE,
2758 p_from_time IN NUMBER,
2759 p_to_time IN NUMBER ) IS
2760
2761 BEGIN
2762 IF nvl(p_from_time,0) = 0 AND nvl(p_to_time,0) = 0 THEN
2763 NULL ;
2764 ELSE
2765 INSERT INTO gmp_resource_avail (
2766 instance_id, plant_code, resource_id,
2767 calendar_code, resource_instance_id, shift_num,
2768 shift_date, from_time, to_time,
2769 resource_units, creation_date, created_by,
2770 last_update_date, last_updated_by, last_update_login )
2771 VALUES (
2772 p_instance_id,
2773 p_org_id,
2774 p_resource_id,
2775 p_calendar_code,
2776 p_resource_instance_id,
2777 p_shift_num,
2778 p_calendar_date,
2779 p_from_time,
2780 p_to_time,
2781 p_assigned_qty,
2782 sysdate,
2783 FND_GLOBAL.USER_ID,
2784 sysdate,
2785 FND_GLOBAL.USER_ID,
2786 FND_GLOBAL.USER_ID ) ;
2787 END IF;
2788 EXCEPTION
2789 WHEN OTHERS THEN
2790 log_message('Error in Net Resource Avail Insert ');
2791 log_message(sqlerrm);
2792
2793 END net_rsrc_avail_insert;
2794
2795 /*
2796 REM+=========================================================================+
2797 REM| FUNCTION NAME |
2798 REM| ORG_STRING |
2799 REM| DESCRIPTION |
2800 REM| To find out the organization string |
2801 REM| HISTORY |
2802 REM| 12/21/2005 Rajesh Patangya |
2803 REM+=========================================================================+
2804 */
2805 FUNCTION ORG_STRING(instance_id IN PLS_INTEGER) return BOOLEAN IS
2806
2807 sql_stmt varchar2(7000);
2808 c_str ref_cursor_typ ;
2809 l_aps_compatible PLS_INTEGER ;
2810 org_str varchar2(32767) ;
2811 in_position PLS_INTEGER ;
2812
2813 BEGIN
2814 sql_stmt := NULL ;
2815 l_aps_compatible := 0 ;
2816 org_str := NULL ;
2817 in_position := -10 ;
2818
2819 SELECT MSC_CL_GMP_UTILITY.is_aps_compatible
2820 INTO l_aps_compatible FROM DUAL ;
2821
2822 IF l_aps_compatible = 1 THEN
2823
2824 -- sql_stmt := 'SELECT MSC_CL_PULL.get_org_str(' || instance_id || ') FROM dual ' ;
2825 -- OPEN c_str FOR sql_stmt ;
2826 --
2827 -- FETCH c_str INTO org_str ;
2828 -- log_message(' String From APS : ' || org_str);
2829 -- CLOSE c_str ;
2830 /* Bug 5148376 per base bug changes */
2831 org_str := MSC_CL_PULL.get_org_str(instance_id);
2832
2833 in_position := instr(org_str,'IN');
2834
2835 /* B3450303, For all org or specific org, APS will provide valid org string
2836 We have to find the IN part in the string, otherwise have to raise
2837 Exception message for error condition */
2838
2839 IF in_position > 0 THEN
2840 gmp_calendar_pkg.g_in_str_org := org_str ;
2841 return TRUE ;
2842 ELSE
2843 gmp_calendar_pkg.g_in_str_org := NULL ;
2844 return FALSE ;
2845 END IF;
2846 ELSE
2847 /* For older patchset This value should be TRUE */
2848 gmp_calendar_pkg.g_in_str_org := NULL ;
2849 return TRUE ;
2850 END IF;
2851
2852 EXCEPTION
2853 WHEN OTHERS THEN
2854 log_message('Error in org_string ');
2855 log_message(sqlerrm);
2856 gmp_calendar_pkg.g_in_str_org := NULL ;
2857 return FALSE ;
2858 END ORG_STRING;
2859
2860 /*
2861 REM+=========================================================================+
2862 REM| PROCEDURE NAME |
2863 REM| time_stamp |
2864 REM| DESCRIPTION |
2865 REM| |
2866 REM| HISTORY |
2867 REM| 12/21/2005 Rajesh Patangya |
2868 REM+=========================================================================+
2869 */
2870 PROCEDURE time_stamp IS
2871
2872 cur_time VARCHAR2(25) ;
2873 BEGIN
2874 cur_time := NULL ;
2875
2876 SELECT to_char(sysdate,'DD-MON-RRRR HH24:MI:SS')
2877 INTO cur_time FROM sys.dual ;
2878
2879 log_message(cur_time);
2880 EXCEPTION
2881 WHEN OTHERS THEN
2882 log_message('Failure occured in time_stamp');
2883 log_message(sqlerrm);
2884 RAISE;
2885 END time_stamp ;
2886
2887 END gmp_calendar_pkg;