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