[Home] [Help]
PACKAGE BODY: APPS.MSC_CL_CONT_COLL_FW
Source
1 PACKAGE BODY MSC_CL_CONT_COLL_FW AS -- body
2 /* $Header: MSCCONTB.pls 120.7 2011/09/27 10:28:14 lsindhur ship $*/
3
4 v_cont_coll_thresh number;
5 v_process_org_present NUMBER := MSC_UTIL.SYS_NO;
6
7 TSK_RM_ASSIGNMENT_SETS NUMBER := 1;
8 TSK_RM_ATP_RULES NUMBER := 1;
9 TSK_RM_BILL_OF_RESOURCES NUMBER := 1;
10 TSK_RM_BIS_BUSINESS_PLANS NUMBER := 1;
11 TSK_RM_BIS_PERIODS NUMBER := 1;
12 TSK_RM_BIS_PFMC_MEASURES NUMBER := 1;
13 TSK_RM_BIS_TARGET_LEVELS NUMBER := 1;
14 TSK_RM_BIS_TARGETS NUMBER := 1;
15 TSK_RM_BOM_COMPONENTS NUMBER := 1;
16 TSK_RM_BOMS NUMBER := 1;
17 TSK_RM_BOR_REQUIREMENTS NUMBER := 1;
18 TSK_RM_CAL_WEEK_START_DATES NUMBER := 1;
19 TSK_RM_CAL_YEAR_START_DATES NUMBER := 1;
20 TSK_RM_CALENDAR_DATES NUMBER := 1;
21 TSK_RM_CALENDAR_SHIFTS NUMBER := 1;
22 TSK_RM_CALENDAR_ASSIGNMENTS NUMBER := 1;
23 TSK_RM_CATEGORY_SETS NUMBER := 1;
24 TSK_RM_CARRIER_SERVICES NUMBER := 1;
25 TSK_RM_COMPONENT_SUBSTITUTES NUMBER := 1;
26 TSK_RM_DEMAND_CLASSES NUMBER := 1;
27 TSK_RM_DEMANDS NUMBER := 3;
28 TSK_RM_DEPARTMENT_RESOURCES NUMBER := 1;
29 TSK_RM_DESIGNATORS NUMBER := 1;
30 TSK_RM_INTERORG_SHIP_METHODS NUMBER := 1;
31 TSK_RM_ITEM_CATEGORIES NUMBER := 1;
32 TSK_RM_ITEM_SUBSTITUTES NUMBER := 1;
33 TSK_RM_ITEM_SUPPLIERS NUMBER := 1;
34 TSK_RM_LOCATION_ASSOCIATIONS NUMBER := 1;
35 TSK_RM_SOURCING_RULES NUMBER := 1;
36 TSK_RM_OPERATION_COMPONENTS NUMBER := 1;
37 TSK_RM_OPERATION_RESOURCE_SEQS NUMBER := 1;
38 TSK_RM_OPERATION_RESOURCES NUMBER := 1;
39 TSK_RM_PARAMETERS NUMBER := 1;
40 TSK_RM_PARTNER_CONTACTS NUMBER := 2;
41 TSK_RM_PERIOD_START_DATES NUMBER := 1;
42 TSK_RM_PLANNERS NUMBER := 1;
43 TSK_RM_PROCESS_EFFECTIVITY NUMBER := 1;
44 TSK_RM_PROJECT_TASKS NUMBER := 1;
45 TSK_RM_PROJECTS NUMBER := 1;
46 TSK_RM_REGIONS NUMBER := 1;
47 TSK_RM_REGION_SITES NUMBER := 1;
48 TSK_RM_RESERVATIONS NUMBER := 1;
49 TSK_RM_RESOURCE_CHANGES NUMBER := 1;
50 TSK_RM_RESOURCE_GROUPS NUMBER := 1;
51 TSK_RM_RESOURCE_REQUIREMENTS NUMBER := 1;
52 TSK_RM_RESOURCE_SHIFTS NUMBER := 1;
53 TSK_RM_ROUTING_OPERATIONS NUMBER := 1;
54 TSK_RM_ROUTINGS NUMBER := 1;
55 TSK_RM_SAFETY_STOCKS NUMBER := 1;
56 TSK_RM_SALES_ORDERS NUMBER := 1;
57 TSK_RM_JOB_OP_NETWORKS NUMBER := 1;
58 TSK_RM_JOB_OPERATIONS NUMBER := 1;
59 TSK_RM_JOB_REQUIREMENT_OPS NUMBER := 1;
60 TSK_RM_JOB_OP_RESOURCES NUMBER := 1;
61 TSK_RM_SHIFT_DATES NUMBER := 1;
62 TSK_RM_SHIFT_EXCEPTIONS NUMBER := 1;
63 TSK_RM_SHIFT_TIMES NUMBER := 1;
64 TSK_RM_SIMULATION_SETS NUMBER := 1;
65 TSK_RM_SR_ASSIGNMENTS NUMBER := 1;
66 TSK_RM_SR_RECEIPT_ORG NUMBER := 1;
67 TSK_RM_SR_SOURCE_ORG NUMBER := 1;
68 TSK_RM_SUB_INVENTORIES NUMBER := 1;
69 TSK_RM_SUPPLIER_CAPACITIES NUMBER := 1;
70 TSK_RM_SUPPLIER_FLEX_FENCES NUMBER := 1;
71 TSK_RM_SUPPLIES NUMBER := 7; -- 5 changed to 7 .
72 TSK_RM_SYSTEM_ITEMS NUMBER := 3;
73 TSK_RM_TRADING_PARTNER_SITES NUMBER := 1;
74 TSK_RM_TRADING_PARTNERS NUMBER := 1;
75 TSK_RM_TRIPS NUMBER := 1;
76 TSK_RM_TRIP_STOPS NUMBER := 1;
77 TSK_RM_UNIT_NUMBERS NUMBER := 1;
78 TSK_RM_UNITS_OF_MEASURE NUMBER := 1;
79 TSK_RM_UOM_CLASS_CONVERSIONS NUMBER := 1;
80 TSK_RM_UOM_CONVERSIONS NUMBER := 1;
81 TSK_RM_ZONE_REGIONS NUMBER := 1;
82 /* ds change start */
83 TSK_RM_RESOURCE_SETUP NUMBER := 1;
84 TSK_RM_RESOURCE_INSTANCE NUMBER := 1;
85 /* ds change end */
86 TSK_RM_ABC_CLASSES NUMBER := 1;
87 TSK_RM_SALES_CHANNEL NUMBER := 1;
88 TSK_RM_FISCAL_CALENDAR NUMBER := 1;
89 TSK_RM_INTERNAL_REPAIR NUMBER := 1;
90 TSK_RM_EXTERNAL_REPAIR NUMBER := 1;
91
92 lv_is_item_refresh_type_target NUMBER := MSC_UTIL.SYS_NO;
93
94
95 PROCEDURE check_entity_cont_ref_type(p_entity_name in varchar2,
96 p_entity_lrn in number,
97 entity_flag OUT NOCOPY number,
98 p_org_str in varchar2,
99 p_coll_thresh in number,
100 p_last_tgt_cont_coll_time in date)
101 IS
102 v_sql_stmt Varchar2(2000);
103 lv_status number := MSC_UTIL.G_SUCCESS;
104 lv_msg varchar2(500);
105 lv_last_coll_time date;
106 BEGIN
107
108 IF ( lv_is_item_refresh_type_target = MSC_UTIL.SYS_YES AND
109 p_entity_name <> 'FCST' AND p_entity_name <> 'WSH') THEN
110 entity_flag := MSC_UTIL.SYS_TGT;
111 RETURN;
112 END IF;
113
114 IF p_last_tgt_cont_coll_time IS NULL THEN
115 lv_last_coll_time := sysdate;
116 ELSE
117 lv_last_coll_time := p_last_tgt_cont_coll_time;
118 END IF;
119
120 v_sql_stmt:=
121 'BEGIN MRP_CL_REFRESH_SNAPSHOT.CHECK_ENTITY_CONT_REF_TYPE'||MSC_CL_PULL.v_dblink||'('
122 ||' p_entity_name => :p_entity_name,'
123 ||' p_entity_lrn => :p_entity_lrn,'
124 ||' entity_flag => :entity_flag,'
125 ||' p_org_str => :p_org_str,'
126 ||' p_coll_thresh => :p_coll_thresh,'
127 ||' p_last_tgt_cont_coll_time => :lv_last_coll_time,'
128 ||' p_ret_code => :p_ret_code,'
129 ||' p_err_buf => :p_err_buf
130 );'
131 ||'END;';
132 /*||' p_application_id=> :lv_application_id );' */
133
134 EXECUTE IMMEDIATE v_sql_stmt
135 USING IN p_entity_name,
136 IN p_entity_lrn,
137 OUT entity_flag,
138 IN p_org_str,
139 IN p_coll_thresh,
140 IN lv_last_coll_time,
141 OUT lv_status,
142 OUT lv_msg;
143
144 IF lv_status <> MSC_UTIL.G_SUCCESS THEN
145 --entity_flag := MSC_UTIL.SYS_TGT;
146 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Error while deciding the collection type for Entity ' || p_entity_name);
147 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'Assumed incremental collection for following MVs');
148 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_msg);
149
150 END IF;
151 lv_status :=MSC_UTIL.G_SUCCESS;
152 lv_msg := '';
153 END;
154
155 FUNCTION get_refresh_type (p_param1 in number:=0,
156 p_param2 in number:=0,
157 p_param3 in number:=0,
158 p_coll_thresh in number)
159 RETURN number
160 IS
161
162 lv_refresh_type number;
163
164
165 BEGIN
166
167 IF ( p_param3 <> 0 ) THEN
168
169 if ( (p_param1 + p_param2) <> 0 ) THEN
170
171 if (( (p_param1 + p_param2) /p_param3)*100 <= p_coll_thresh) then
172 lv_refresh_type := MSC_UTIL.SYS_INCR; -- do incremental refresh
173 else
174 lv_refresh_type := MSC_UTIL.SYS_TGT; -- do targeted refresh
175 end if;
176
177 else
178 lv_refresh_type := MSC_UTIL.SYS_NO;
179 end if;
180
181 ELSE
182 if ( p_param1 <> 0 ) THEN
183 lv_refresh_type := MSC_UTIL.SYS_TGT;
184 else
185 lv_refresh_type := MSC_UTIL.SYS_NO;
186 end if ;
187 END IF;
188
189
190 RETURN lv_refresh_type;
191
192 EXCEPTION
193 when others then
194 lv_refresh_type := MSC_UTIL.SYS_NO;
195 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
196 RETURN lv_refresh_type;
197
198 END get_refresh_type;
199
200
201 -- Entry point for continuous collections
202
203
204 PROCEDURE init_entity_refresh_type(p_coll_thresh in number,
205 p_coll_freq in number,
206 p_last_tgt_cont_coll_time in date,
207 p_dblink in varchar2,
208 p_instance_id in number,
209 prec in MSC_UTIL.CollParamREC,
210 p_org_group in varchar2,
211 p_bom_sn_flag out NOCOPY number,
212 p_bor_sn_flag out NOCOPY number,
213 p_item_sn_flag out NOCOPY number,
214 p_oh_sn_flag out NOCOPY number,
215 p_usup_sn_flag out NOCOPY number,
216 p_udmd_sn_flag out NOCOPY number,
217 p_so_sn_flag out NOCOPY number,
218 p_fcst_sn_flag out NOCOPY number,
219 p_wip_sn_flag out NOCOPY number,
220 p_supcap_sn_flag out NOCOPY number,
221 p_po_sn_flag out NOCOPY number,
222 p_mds_sn_flag out NOCOPY number,
223 p_mps_sn_flag out NOCOPY number,
224 p_nosnap_flag out NOCOPY number,
225 p_suprep_sn_flag in out NOCOPY number,
226 p_trip_sn_flag out NOCOPY number)
227 is
228
229 lv_sup_cap_lrn number;
230 lv_bom_lrn number;
231 lv_bor_lrn number;
232 lv_forecast_lrn number;
233 lv_item_lrn number;
234 lv_mds_lrn number;
235 lv_mps_lrn number;
236 lv_oh_lrn number;
237 lv_rsv_lrn number;
238 lv_po_lrn number;
239 lv_so_lrn number;
240 lv_usd_lrn number;
241 lv_wip_lrn number;
242 lv_nra_lrn number;
243 lv_saf_stock_lrn number;
244 lv_unit_no_lrn number;
245 lv_uom_lrn number;
246 lv_calendar_lrn number;
247 lv_apps_lrn number;
248 lv_trip_lrn number;
249
250 lv_param1 number;
251 lv_param2 number;
252 lv_param3 number;
253 lv_param4 number;
254 lv_param5 number;
255 lv_param6 number;
256 lv_param7 number;
257 lv_param8 number;
258 lv_param9 number;
259
260 lv_bom1 number := MSC_UTIL.SYS_NO;
261 lv_bom2 number := MSC_UTIL.SYS_NO;
262 lv_bom3 number := MSC_UTIL.SYS_NO;
263 lv_bom4 number := MSC_UTIL.SYS_NO;
264 lv_bom5 number := MSC_UTIL.SYS_NO;
265 lv_bom6 number := MSC_UTIL.SYS_NO;
266 lv_bom7 number := MSC_UTIL.SYS_NO;
267 lv_bom8 number := MSC_UTIL.SYS_NO;
268 lv_bom9 number := MSC_UTIL.SYS_NO;
269 lv_bom10 number := MSC_UTIL.SYS_NO;
270 lv_bom11 number := MSC_UTIL.SYS_NO;
271 lv_bom12 number := MSC_UTIL.SYS_NO;
272
273 lv_bor1 number := MSC_UTIL.SYS_NO;
274
275 lv_item1 number := MSC_UTIL.SYS_NO;
276 lv_item2 number := MSC_UTIL.SYS_NO;
277
278 lv_oh1 number := MSC_UTIL.SYS_NO;
279
280 lv_usup1 number := MSC_UTIL.SYS_NO;
281 lv_udmd1 number := MSC_UTIL.SYS_NO;
282
283 lv_so1 number := MSC_UTIL.SYS_NO;
284
285 lv_fcst1 number := MSC_UTIL.SYS_NO;
286 lv_fcst2 number := MSC_UTIL.SYS_NO;
287
288 lv_wip1 number := MSC_UTIL.SYS_NO;
289 lv_wip2 number := MSC_UTIL.SYS_NO;
290 lv_wip3 number := MSC_UTIL.SYS_NO;
291
292 lv_supcap1 number := MSC_UTIL.SYS_NO;
293
294 lv_po1 number := MSC_UTIL.SYS_NO;
295
296 lv_mds1 number := MSC_UTIL.SYS_NO;
297
298 lv_mps1 number := MSC_UTIL.SYS_NO;
299
300 lv_trip1 number := MSC_UTIL.SYS_NO;
301 lv_trip2 number := MSC_UTIL.SYS_NO;
302
303 lv_in_org_str VARCHAR2(32767):='NULL';
304
305 lv_sql_stmt VARCHAR2(32767);
306
307 lv_sn_flag number;
308
309 lv_status_decided_bom NUMBER := MSC_UTIL.SYS_NO;
310 lv_status_decided_bor NUMBER := MSC_UTIL.SYS_NO;
311 lv_status_decided_item NUMBER := MSC_UTIL.SYS_NO;
312 lv_status_decided_oh NUMBER := MSC_UTIL.SYS_NO;
313 lv_status_decided_usup NUMBER := MSC_UTIL.SYS_NO;
314 lv_status_decided_udem NUMBER := MSC_UTIL.SYS_NO;
315 lv_status_decided_so NUMBER := MSC_UTIL.SYS_NO;
316 lv_status_decided_fcst NUMBER := MSC_UTIL.SYS_NO;
317 lv_status_decided_wip NUMBER := MSC_UTIL.SYS_NO;
318 lv_status_decided_app_supp_cap NUMBER := MSC_UTIL.SYS_NO;
319 lv_status_decided_po NUMBER := MSC_UTIL.SYS_NO;
320 lv_status_decided_mds NUMBER := MSC_UTIL.SYS_NO;
321 lv_status_decided_mps NUMBER := MSC_UTIL.SYS_NO;
322 lv_status_decided_trip NUMBER := MSC_UTIL.SYS_NO;
323
324 lv_cond_str_enabled_flag VARCHAR2(64);
325 lv_cond_str_org_grp VARCHAR2(100);
326 lv_application_name VARCHAR2(240):= NULL;
327 lv_application_id NUMBER;
328
329
330 begin
331
332 IF (msc_cl_pull.v_is_cont_refresh = MSC_UTIL.SYS_YES) THEN
333
334 IF (MSC_CL_PULL.v_instance_type = MSC_UTIL.G_INS_MIXED) THEN
335
336 SELECT FND_GLOBAL.APPLICATION_NAME
337 INTO lv_application_name
338 FROM dual;
339
340 SELECT APPLICATION_ID
341 INTO lv_application_id
342 FROM FND_APPLICATION_VL
343 WHERE APPLICATION_NAME = lv_application_name;
344
345 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'app id - ' || lv_application_id);
346 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'app name- ' || lv_application_name);
347
348 IF lv_application_id = 722 THEN
349 lv_cond_str_enabled_flag:=' nvl(mio.dp_enabled_flag,1) = 1 ';
350 ELSE
351 lv_cond_str_enabled_flag:=' mio.enabled_flag= 1 ';
352 END IF;
353
354 IF p_org_group = MSC_UTIL.G_ALL_ORGANIZATIONS THEN
355 lv_cond_str_org_grp := ' ';
356 ELSE
357 lv_cond_str_org_grp := ' AND mio.org_group= ''' || p_org_group ||''' ';
358 END IF;
359
360 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' --Discrete and Process Instance--');
361 BEGIN
362 lv_sql_stmt:=' SELECT 1 '
363 ||' FROM mtl_parameters'||p_dblink||' mp,'
364 ||' msc_instance_orgs mio'
365 ||' WHERE mio.sr_instance_id= :p_instance_id'
366 ||' AND ' || lv_cond_str_enabled_flag
367 ||' AND mio.organization_id=mp.organization_id'
368 ||' AND mp.process_enabled_flag='||'''Y'''
369 || lv_cond_str_org_grp
370 ||' AND ROWNUM <2 ';
371
372 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, lv_sql_stmt );
373
374 EXECUTE IMMEDIATE lv_sql_stmt
375 INTO v_process_org_present
376 USING p_instance_id;
377
378 EXCEPTION
379 WHEN NO_DATA_FOUND THEN
380 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' No Data Found');
381 v_process_org_present := MSC_UTIL.SYS_NO;
382 WHEN OTHERS THEN
383 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, SQLERRM);
384 RETURN;
385 END;
386 END IF;
387 END IF;
388 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' v_process_org_present: ' || v_process_org_present);
389 IF (v_process_org_present = MSC_UTIL.SYS_YES) THEN
390
391 IF prec.bom_flag = MSC_UTIL.SYS_YES THEN
392 p_bom_sn_flag := MSC_UTIL.SYS_TGT;
393 END IF;
394
395 IF prec.bor_flag = MSC_UTIL.SYS_YES THEN
396 p_bor_sn_flag := MSC_UTIL.SYS_TGT;
397 END IF;
398
399 IF prec.item_flag = MSC_UTIL.SYS_YES THEN
400 p_item_sn_flag := MSC_UTIL.SYS_TGT;
401 END IF;
402
403 IF prec.oh_flag = MSC_UTIL.SYS_YES THEN
404 p_oh_sn_flag := MSC_UTIL.SYS_TGT;
405 END IF;
406
407 IF prec.user_supply_demand_flag = MSC_UTIL.SYS_YES THEN
408 p_usup_sn_flag := MSC_UTIL.SYS_TGT;
409 p_udmd_sn_flag := MSC_UTIL.SYS_TGT;
410 END IF;
411
412 IF (prec.sales_order_flag =MSC_UTIL.SYS_YES) THEN
413 p_so_sn_flag := MSC_UTIL.SYS_TGT;
414 END IF;
415
416 IF prec.forecast_flag = MSC_UTIL.SYS_YES THEN
417 p_fcst_sn_flag := MSC_UTIL.SYS_TGT;
418 END IF;
419
420 IF prec.wip_flag = MSC_UTIL.SYS_YES THEN
421 p_wip_sn_flag := MSC_UTIL.SYS_TGT;
422 END IF;
423
424 IF prec.app_supp_cap_flag = MSC_UTIL.ASL_YES or
425 prec.app_supp_cap_flag =MSC_UTIL.ASL_YES_RETAIN_CP THEN
426 p_supcap_sn_flag := MSC_UTIL.SYS_TGT;
427 END IF;
428
429 IF prec.po_flag = MSC_UTIL.SYS_YES THEN
430 p_po_sn_flag := MSC_UTIL.SYS_TGT;
431 END IF;
432
433 IF prec.mds_flag = MSC_UTIL.SYS_YES THEN
434 p_mds_sn_flag := MSC_UTIL.SYS_TGT;
435 END IF;
436
437 IF prec.mps_flag = MSC_UTIL.SYS_YES THEN
438 p_mps_sn_flag := MSC_UTIL.SYS_TGT;
439 END IF;
440
441 IF prec.trip_flag = MSC_UTIL.SYS_YES THEN
442 p_trip_sn_flag := MSC_UTIL.SYS_TGT;
443 END IF;
444
445 ELSIF MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS121 THEN
446
447 lv_in_org_str:= MSC_CL_PULL.GET_ORG_STR(p_instance_id,2);
448
449 select apps_lrn
450 into lv_apps_lrn
451 from msc_apps_instances
452 where instance_id = p_instance_id;
453
454 select min(nvl(supplier_capacity_lrn,lv_apps_lrn)),
455 min(nvl(bom_lrn,lv_apps_lrn)),
456 min(nvl(bor_lrn,lv_apps_lrn)),
457 min(nvl(forecast_lrn,lv_apps_lrn)),
458 min(nvl(item_lrn,lv_apps_lrn)),
459 min(nvl(mds_lrn,lv_apps_lrn)),
460 min(nvl(mps_lrn,lv_apps_lrn)),
461 min(nvl(oh_lrn,lv_apps_lrn)),
462 min(nvl(reservations_lrn,lv_apps_lrn)),
463 min(nvl(po_lrn,lv_apps_lrn)),
464 min(nvl(so_lrn,lv_apps_lrn)),
465 min(nvl(user_supply_demand_lrn,lv_apps_lrn)),
466 min(nvl(wip_lrn,lv_apps_lrn)),
467 min(nvl(nra_lrn,lv_apps_lrn)),
468 min(nvl(saf_stock_lrn,lv_apps_lrn)),
469 min(nvl(unit_no_lrn,lv_apps_lrn)),
470 min(nvl(uom_lrn,lv_apps_lrn)),
471 min(nvl(calendar_lrn,lv_apps_lrn)),
472 min(nvl(trip_lrn,lv_apps_lrn))
473 into
474 lv_sup_cap_lrn,
475 lv_bom_lrn,
476 lv_bor_lrn,
477 lv_forecast_lrn,
478 lv_item_lrn,
479 lv_mds_lrn,
480 lv_mps_lrn,
481 lv_oh_lrn,
482 lv_rsv_lrn,
483 lv_po_lrn,
484 lv_so_lrn,
485 lv_usd_lrn,
486 lv_wip_lrn,
487 lv_nra_lrn,
488 lv_saf_stock_lrn,
489 lv_unit_no_lrn,
490 lv_uom_lrn,
491 lv_calendar_lrn,
492 lv_trip_lrn
493 from msc_instance_orgs
494 WHERE ((p_org_group =MSC_UTIL.G_ALL_ORGANIZATIONS) or (org_group=p_org_group))
495 AND sr_instance_id = p_instance_id;
496
497
498
499 /*Check for each entity*/
500
501 -- p_item_sn_flag
502 IF prec.item_flag = MSC_UTIL.SYS_YES THEN
503 check_entity_cont_ref_type('ITEM', lv_item_lrn, p_item_sn_flag, lv_in_org_str, p_coll_thresh, p_last_tgt_cont_coll_time);
504 IF p_item_sn_flag = MSC_UTIL.SYS_TGT THEN
505 lv_is_item_refresh_type_target := MSC_UTIL.SYS_YES;
506 END IF;
507 END IF;
508
509 -- p_bom_sn_flag
510 IF prec.bom_flag = MSC_UTIL.SYS_YES THEN
511 check_entity_cont_ref_type('BOM', lv_bom_lrn, p_bom_sn_flag, lv_in_org_str, p_coll_thresh, p_last_tgt_cont_coll_time);
512 END IF;
513
514 -- p_bor_sn_flag
515 IF prec.bor_flag = MSC_UTIL.SYS_YES THEN
516 check_entity_cont_ref_type('BOR', lv_bor_lrn, p_bor_sn_flag, lv_in_org_str, p_coll_thresh, p_last_tgt_cont_coll_time);
517 END IF;
518
519 --p_oh_sn_flag
520 IF prec.oh_flag = MSC_UTIL.SYS_YES THEN
521 check_entity_cont_ref_type('OH', lv_oh_lrn, p_oh_sn_flag, lv_in_org_str, p_coll_thresh, p_last_tgt_cont_coll_time);
522 END IF;
523
524
525 --p_usup_sn_flag
526 IF prec.user_supply_demand_flag = MSC_UTIL.SYS_YES THEN
527 check_entity_cont_ref_type('USUD', lv_usd_lrn, p_usup_sn_flag, lv_in_org_str, p_coll_thresh, p_last_tgt_cont_coll_time);
528 p_udmd_sn_flag := p_usup_sn_flag;
529 END IF;
530
531 --p_so_sn_flag
532 IF (prec.sales_order_flag =MSC_UTIL.SYS_YES) THEN
533 check_entity_cont_ref_type('ONT', lv_so_lrn, p_so_sn_flag, lv_in_org_str, p_coll_thresh, p_last_tgt_cont_coll_time);
534 IF p_so_sn_flag <> MSC_UTIL.SYS_TGT THEN
535 check_entity_cont_ref_type('RES', lv_so_lrn, lv_sn_flag, lv_in_org_str, p_coll_thresh, p_last_tgt_cont_coll_time);
536 IF lv_sn_flag = MSC_UTIL.SYS_TGT THEN
537 p_so_sn_flag := lv_sn_flag;
538 END IF;
539 END IF;
540 END IF;
541
542 -- p_wip_sn_flag
543 IF prec.wip_flag = MSC_UTIL.SYS_YES THEN
544 check_entity_cont_ref_type('WIP', lv_wip_lrn, p_wip_sn_flag, lv_in_org_str, p_coll_thresh, p_last_tgt_cont_coll_time);
545 IF p_so_sn_flag <> MSC_UTIL.SYS_TGT THEN
546 check_entity_cont_ref_type('EAM', lv_wip_lrn, lv_sn_flag, lv_in_org_str, p_coll_thresh, p_last_tgt_cont_coll_time);
547 IF lv_sn_flag = MSC_UTIL.SYS_TGT THEN
548 p_wip_sn_flag := lv_sn_flag;
549 END IF;
550 END IF;
551
552 END IF; -- wip_flag
553
554 --p_supcap_sn_flag
555 IF prec.app_supp_cap_flag = MSC_UTIL.SYS_YES or prec.app_supp_cap_flag =MSC_UTIL.ASL_YES_RETAIN_CP THEN
556 --check_entity_cont_ref_type('SCAP', lv_sup_cap_lrn, p_supcap_sn_flag, lv_in_org_str, p_coll_thresh, p_last_tgt_cont_coll_time);
557 --Using the old logic
558 lv_status_decided_app_supp_cap := MSC_UTIL.SYS_NO;
559
560 --lv_supcap1
561 IF lv_status_decided_app_supp_cap = MSC_UTIL.SYS_NO THEN
562 lv_sql_stmt:= 'select count(*) from MRP_AD_SUPPLIER_CAPACITIES_V'||p_dblink
563 ||' where RN > :lv_sup_cap_lrn '
564 ||' and organization_id '|| lv_in_org_str;
565
566
567 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_sup_cap_lrn;
568
569 lv_sql_stmt:= 'select count(*) '
570 ||' from MRP_AP_SUPPLIER_CAPACITIES_V'||p_dblink||' x '
571 ||' where ( x.RN1 > :lv_sup_cap_lrn '
572 ||' OR x.RN2 > :lv_sup_cap_lrn ) '
573 ||' and x.organization_id '|| lv_in_org_str;
574
575
576 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_sup_cap_lrn,
577 lv_sup_cap_lrn;
578
579
580 lv_sql_stmt:= 'select count(*) from MRP_AP_SUPPLIER_CAPACITIES_V'||p_dblink
581 ||' where organization_id '|| lv_in_org_str;
582
583 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
584
585 lv_supcap1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
586 ELSE
587 lv_supcap1 := MSC_UTIL.SYS_TGT;
588 END IF;
589 p_supcap_sn_flag := lv_supcap1;
590 END IF;
591
592 --p_po_sn_flag
593 IF prec.po_flag = MSC_UTIL.SYS_YES THEN
594 --check_entity_cont_ref_type('PO', lv_po_lrn, p_po_sn_flag, lv_in_org_str, p_coll_thresh, p_last_tgt_cont_coll_time);
595 --Using the old logic
596 lv_status_decided_po := MSC_UTIL.SYS_NO;
597 --lv_po1
598 IF lv_status_decided_po = MSC_UTIL.SYS_NO THEN
599 lv_sql_stmt:= 'select count(*) from MRP_AD_PO_SUPPLIES_V'||p_dblink
600 ||' where RN > :lv_po_lrn'
601 ||' and organization_id '|| lv_in_org_str;
602
603
604 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_po_lrn;
605
606
607 lv_sql_stmt:= 'select count(*) '
608 ||' from MRP_AP_PO_PO_SUPPLY_V'||p_dblink||' x '
609 ||' where ( x.RN2 > :lv_po_lrn '
610 ||' OR x.RN3 > :lv_po_lrn ) '
611 ||' and x.organization_id '|| lv_in_org_str;
612
613
614 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_po_lrn,
615 lv_po_lrn;
616
617 lv_sql_stmt:= 'select count(*) '
618 ||' from MRP_AP_PO_SHIP_SUPPLY_V'||p_dblink||' x '
619 ||' where ( x.RN2 > :lv_po_lrn '
620 ||' OR x.RN3 > :lv_po_lrn )'
621 ||' and x.organization_id '|| lv_in_org_str;
622
623
624 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3 USING lv_po_lrn,
625 lv_po_lrn;
626
627 lv_sql_stmt:= 'select count(*) '
628 ||' from MRP_AP_PO_REQ_SUPPLY_V'||p_dblink||' x '
629 ||' where ( x.RN2 > :lv_po_lrn '
630 ||' OR x.RN3 > :lv_po_lrn )'
631 ||' and x.organization_id '|| lv_in_org_str;
632
633
634 EXECUTE IMMEDIATE lv_sql_stmt into lv_param4 USING lv_po_lrn,
635 lv_po_lrn;
636
637 lv_sql_stmt:= 'select count(*) '
638 ||' from MRP_AP_PO_SHIP_RCV_SUPPLY_V'||p_dblink||' x '
639 ||' where ( x.RN2 > :lv_po_lrn '
640 ||' OR x.RN3 > :lv_po_lrn ) '
641 ||' and x.organization_id '|| lv_in_org_str;
642
643
644 EXECUTE IMMEDIATE lv_sql_stmt into lv_param5 USING lv_po_lrn,
645 lv_po_lrn;
646
647 lv_sql_stmt:= 'select count(*) '
648 ||' from MRP_AP_PO_RCV_SUPPLY_V'||p_dblink||' x '
649 ||' where ( x.RN2 > :lv_po_lrn '
650 ||' OR x.RN3 > :lv_po_lrn ) '
651 ||' and x.organization_id '|| lv_in_org_str;
652
653
654 EXECUTE IMMEDIATE lv_sql_stmt into lv_param6 USING lv_po_lrn,
655 lv_po_lrn;
656
657
658 lv_sql_stmt:= 'select count(*) '
659 ||' from MRP_AP_INTRANSIT_SUPPLIES_V'||p_dblink||' x '
660 ||' where x.RN2 > :lv_po_lrn '
661 ||' and x.organization_id '|| lv_in_org_str;
662
663
664 EXECUTE IMMEDIATE lv_sql_stmt into lv_param7 USING lv_po_lrn;
665
666 lv_param2:=lv_param2 + lv_param3 + lv_param4 + lv_param5 + lv_param6 + lv_param7;
667
668 lv_sql_stmt:= 'select count(*) from MRP_AP_PO_PO_SUPPLY_V'||p_dblink
669 ||' where organization_id '|| lv_in_org_str;
670
671 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
672
673 lv_sql_stmt:= 'select count(*) from MRP_AP_PO_SHIP_SUPPLY_V'||p_dblink
674 ||' where organization_id '|| lv_in_org_str;
675
676 EXECUTE IMMEDIATE lv_sql_stmt into lv_param4;
677
678 lv_sql_stmt:= 'select count(*) from MRP_AP_PO_REQ_SUPPLY_V'||p_dblink
679 ||' where organization_id '|| lv_in_org_str;
680
681 EXECUTE IMMEDIATE lv_sql_stmt into lv_param5;
682
683 lv_sql_stmt:= 'select count(*) from MRP_AP_PO_SHIP_RCV_SUPPLY_V'||p_dblink
684 ||' where organization_id '|| lv_in_org_str;
685
686 EXECUTE IMMEDIATE lv_sql_stmt into lv_param6;
687
688 lv_sql_stmt:= 'select count(*) from MRP_AP_PO_RCV_SUPPLY_V'||p_dblink
689 ||' where organization_id '|| lv_in_org_str;
690
691 EXECUTE IMMEDIATE lv_sql_stmt into lv_param7;
692
693 lv_sql_stmt:= 'select count(*) from MRP_AP_INTRANSIT_SUPPLIES_V'||p_dblink
694 ||' where organization_id '|| lv_in_org_str;
695
696 EXECUTE IMMEDIATE lv_sql_stmt into lv_param8;
697
698 lv_param3:=lv_param3 + lv_param4 + lv_param5 + lv_param6 + lv_param7 + lv_param8;
699
700 lv_po1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
701 ELSE
702 lv_po1 := MSC_UTIL.SYS_TGT;
703 END IF;
704 p_po_sn_flag := lv_po1;
705 END IF;
706
707 --p_mds_sn_flag
708 IF prec.mds_flag = MSC_UTIL.SYS_YES THEN
709 check_entity_cont_ref_type('MRP', lv_mds_lrn, p_mds_sn_flag, lv_in_org_str, p_coll_thresh, p_last_tgt_cont_coll_time);
710
711 END IF;
712
713 --p_mps_sn_flag
714 IF prec.mps_flag = MSC_UTIL.SYS_YES THEN
715 p_mps_sn_flag := p_mds_sn_flag;
716 END IF;
717
718 --p_fcst_sn_flag **
719 IF prec.forecast_flag = MSC_UTIL.SYS_YES THEN
720 check_entity_cont_ref_type('FCST', lv_forecast_lrn, p_fcst_sn_flag, lv_in_org_str, p_coll_thresh, p_last_tgt_cont_coll_time);
721 END IF;
722
723 --p_trip_sn_flag**
724 IF prec.trip_flag = MSC_UTIL.SYS_YES THEN
725 --check_entity_cont_ref_type('WSH', lv_trip_lrn, p_trip_sn_flag, lv_in_org_str, p_coll_thresh, p_last_tgt_cont_coll_time);
726 --Using the old logic
727 lv_status_decided_trip := MSC_UTIL.SYS_NO;
728 --lv_trip1
729 IF lv_status_decided_trip = MSC_UTIL.SYS_NO THEN
730 lv_sql_stmt:= 'select count(*) from MRP_AD_TRIPS_V'||p_dblink
731 ||' where RN > :lv_trip_lrn ';
732
733
734 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_trip_lrn;
735
736 lv_sql_stmt:= 'select count(*) '
737 ||' from MRP_AP_TRIPS_V'||p_dblink||' x '
738 ||' where x.RN > :lv_trip_lrn ';
739
740
741 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_trip_lrn;
742
743
744 lv_sql_stmt:= 'select count(*) from MRP_AP_TRIPS_V'||p_dblink;
745
746
747 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
748
749 lv_trip1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
750 IF lv_trip1 = MSC_UTIL.SYS_TGT THEN
751 lv_status_decided_trip := MSC_UTIL.SYS_YES;
752 END IF;
753
754 ELSE
755 lv_trip1 := MSC_UTIL.SYS_TGT;
756 END IF;
757
758 IF lv_status_decided_trip = MSC_UTIL.SYS_NO THEN
759 --lv_trip2
760 lv_sql_stmt:= 'select count(*) from MRP_AD_TRIP_STOPS_V'||p_dblink
761 ||' where RN > :lv_trip_lrn ';
762
763
764 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_trip_lrn;
765
766 lv_sql_stmt:= 'select count(*) '
767 ||' from MRP_AP_TRIP_STOPS_V'||p_dblink||' x '
768 ||' where x.RN > :lv_trip_lrn ';
769
770
771 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_trip_lrn;
772
773
774 lv_sql_stmt:= 'select count(*) from MRP_AP_TRIP_STOPS_V'||p_dblink;
775
776
777 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
778
779 lv_trip2 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
780 END IF;
781 if ((lv_trip1 = MSC_UTIL.SYS_TGT) or
782 (lv_trip2 = MSC_UTIL.SYS_TGT)) then
783 p_trip_sn_flag := MSC_UTIL.SYS_TGT;
784 elsif ((lv_trip1 = MSC_UTIL.SYS_NO) and
785 (lv_trip2 = MSC_UTIL.SYS_NO)) then
786 p_trip_sn_flag := MSC_UTIL.SYS_NO;
787 else
788 p_trip_sn_flag := MSC_UTIL.SYS_INCR;
789 end if;
790 END IF ;
791 ELSE --For backward compatbility IF version <121
792 ------ set lv_in_org_str ----------
793 lv_in_org_str:= MSC_CL_PULL.GET_ORG_STR(p_instance_id,2);
794
795 select apps_lrn
796 into lv_apps_lrn
797 from msc_apps_instances
798 where instance_id = p_instance_id;
799
800 select min(nvl(supplier_capacity_lrn,lv_apps_lrn)),
801 min(nvl(bom_lrn,lv_apps_lrn)),
802 min(nvl(bor_lrn,lv_apps_lrn)),
803 min(nvl(forecast_lrn,lv_apps_lrn)),
804 min(nvl(item_lrn,lv_apps_lrn)),
805 min(nvl(mds_lrn,lv_apps_lrn)),
806 min(nvl(mps_lrn,lv_apps_lrn)),
807 min(nvl(oh_lrn,lv_apps_lrn)),
808 min(nvl(reservations_lrn,lv_apps_lrn)),
809 min(nvl(po_lrn,lv_apps_lrn)),
810 min(nvl(so_lrn,lv_apps_lrn)),
811 min(nvl(user_supply_demand_lrn,lv_apps_lrn)),
812 min(nvl(wip_lrn,lv_apps_lrn)),
813 min(nvl(nra_lrn,lv_apps_lrn)),
814 min(nvl(saf_stock_lrn,lv_apps_lrn)),
815 min(nvl(unit_no_lrn,lv_apps_lrn)),
816 min(nvl(uom_lrn,lv_apps_lrn)),
817 min(nvl(calendar_lrn,lv_apps_lrn)),
818 min(nvl(trip_lrn,lv_apps_lrn))
819 into
820 lv_sup_cap_lrn,
821 lv_bom_lrn,
822 lv_bor_lrn,
823 lv_forecast_lrn,
824 lv_item_lrn,
825 lv_mds_lrn,
826 lv_mps_lrn,
827 lv_oh_lrn,
828 lv_rsv_lrn,
829 lv_po_lrn,
830 lv_so_lrn,
831 lv_usd_lrn,
832 lv_wip_lrn,
833 lv_nra_lrn,
834 lv_saf_stock_lrn,
835 lv_unit_no_lrn,
836 lv_uom_lrn,
837 lv_calendar_lrn,
838 lv_trip_lrn
839 from msc_instance_orgs
840 WHERE ((p_org_group =MSC_UTIL.G_ALL_ORGANIZATIONS) or (org_group=p_org_group))
841 AND sr_instance_id = p_instance_id;
842
843 -- p_bom_sn_flag
844
845 IF prec.bom_flag = MSC_UTIL.SYS_YES THEN
846
847 BEGIN
848 SELECT MSC_UTIL.SYS_YES
849 INTO lv_status_decided_bom
850 FROM fnd_lookup_values
851 WHERE lookup_type = 'MSC_COLL_SNAPSHOTS' AND
852 enabled_flag = 'Y' AND
853 view_application_id = 700 AND
854 language = userenv('lang') AND
855 attribute2 in
856 ('BOM_BOMS_SN', 'BOM_INV_COMPS_SN', 'BOM_OPR_NETWORKS_SN',
857 'BOM_OPR_RESS_SN', 'BOM_OPR_RTNS_SN', 'BOM_OPR_SEQS_SN',
858 'BOM_RES_CHNGS_SN', 'BOM_RES_INST_CHNGS_SN', 'BOM_SUB_COMPS_SN',
859 'BOM_SUB_OPR_RESS_SN', 'MTL_SYS_ITEMS_SN') AND
860 attribute13 = 'COMPLETE' AND
861 rownum = 1;
862 EXCEPTION
863 WHEN NO_DATA_FOUND THEN
864 lv_status_decided_bom := MSC_UTIL.SYS_NO;
865 END;
866
867 --lv_bom1
868 IF lv_status_decided_bom = MSC_UTIL.SYS_NO THEN
869 lv_sql_stmt:= ' select count(*) from MRP_AD_BOM_COMPONENTS_V'||p_dblink
870 ||' where RN > :lv_bom_lrn '
871 ||' and organization_id '|| lv_in_org_str;
872
873
874 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param1 USING lv_bom_lrn;
875
876 lv_sql_stmt:= ' select count(*) '
877 ||' from MRP_AP_BOM_COMPONENTS_V'||p_dblink||' x '
878 ||' where ( x.RN1 > :lv_bom_lrn '
879 ||' OR x.RN2 > :lv_bom_lrn '
880 ||' OR x.RN3 > :lv_bom_lrn '
881 ||' OR x.RN4 > :lv_bom_lrn )'
882 ||' and x.organization_id '|| lv_in_org_str;
883
884
885 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param2 USING lv_bom_lrn,
886 lv_bom_lrn,
887 lv_bom_lrn,
888 lv_bom_lrn;
889
890
891 lv_sql_stmt:= ' select count(*) from MRP_AP_BOM_COMPONENTS_V'||p_dblink
892 ||' where organization_id '|| lv_in_org_str;
893
894 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param3;
895
896 lv_bom1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
897
898 IF lv_bom1 = MSC_UTIL.SYS_TGT THEN
899 lv_status_decided_bom := MSC_UTIL.SYS_YES;
900 END IF;
901 ELSE
902 lv_bom1 := MSC_UTIL.SYS_TGT;
903 END IF;
904
905
906 --lv_bom2
907 IF lv_status_decided_bom = MSC_UTIL.SYS_NO THEN
908 lv_sql_stmt:= 'select count(*) from MRP_AD_BOMS_V'||p_dblink
909 ||' where RN > :lv_bom_lrn'
910 ||' and organization_id '|| lv_in_org_str;
911
912
913 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param1 USING lv_bom_lrn;
914
915 lv_sql_stmt:= 'select count(*) '
916 ||' from MRP_AP_BOMS_V'||p_dblink||' x '
917 ||' where ( x.RN1 > :lv_bom_lrn '
918 ||' OR x.RN2 > :lv_bom_lrn ) '
919 ||' and x.organization_id '|| lv_in_org_str;
920
921
922 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param2 USING lv_bom_lrn,
923 lv_bom_lrn;
924
925 lv_sql_stmt:= 'select count(*) from MRP_AP_BOMS_V'||p_dblink
926 ||' where organization_id '|| lv_in_org_str;
927
928 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param3;
929
930
931 lv_bom2 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
932 IF lv_bom2 = MSC_UTIL.SYS_TGT THEN
933 lv_status_decided_bom := MSC_UTIL.SYS_YES;
934 END IF;
935 END IF;
936
937 --lv_bom3
938 IF lv_status_decided_bom = MSC_UTIL.SYS_NO THEN
939 lv_sql_stmt:= 'select count(*) from MRP_AD_SUB_COMPS_V'||p_dblink
940 ||' where RN > :lv_bom_lrn'
941 ||' and organization_id '|| lv_in_org_str;
942
943 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param1 USING lv_bom_lrn;
944
945 lv_sql_stmt:= ' select count(*) '
946 ||' from MRP_AP_COMPONENT_SUBSTITUTES_V'||p_dblink||' x '
947 ||' where ( x.RN1 > :lv_bom_lrn '
948 ||' OR x.RN2 > :lv_bom_lrn '
949 ||' OR x.RN3 > :lv_bom_lrn '
950 ||' OR x.RN4 > :lv_bom_lrn ) '
951 ||' and x.organization_id '|| lv_in_org_str;
952
953
954 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param2 USING lv_bom_lrn,
955 lv_bom_lrn,
956 lv_bom_lrn,
957 lv_bom_lrn;
958
959 lv_sql_stmt:= 'select count(*) from MRP_AP_COMPONENT_SUBSTITUTES_V'||p_dblink
960 ||' where organization_id '|| lv_in_org_str;
961
962 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param3;
963
964
965 lv_bom3 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
966 IF lv_bom3 = MSC_UTIL.SYS_TGT THEN
967 lv_status_decided_bom := MSC_UTIL.SYS_YES;
968 END IF;
969 END IF;
970
971 --lv_bom4
972 IF lv_status_decided_bom = MSC_UTIL.SYS_NO THEN
973 lv_sql_stmt:= 'select count(*) from MRP_AD_ROUTINGS_V'||p_dblink
974 ||' where RN > :lv_bom_lrn '
975 ||' and organization_id '|| lv_in_org_str;
976
977
978 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param1 USING lv_bom_lrn;
979
980 lv_sql_stmt:= 'select count(*) '
981 ||' from MRP_AP_ROUTINGS_V'||p_dblink||' x '
982 ||' where ( x.RN1 > :lv_bom_lrn '
983 ||' OR x.RN2 > :lv_bom_lrn ) '
984 ||' and x.organization_id '|| lv_in_org_str;
985
986
987 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param2 USING lv_bom_lrn,
988 lv_bom_lrn;
989
990 lv_sql_stmt:= 'select count(*) from MRP_AP_ROUTINGS_V'||p_dblink
991 ||' where organization_id '|| lv_in_org_str;
992
993 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param3;
994
995 lv_bom4 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
996 IF lv_bom4 = MSC_UTIL.SYS_TGT THEN
997 lv_status_decided_bom := MSC_UTIL.SYS_YES;
998 END IF;
999 END IF;
1000
1001 --lv_bom5
1002 /* IF lv_status_decided_bom = MSC_UTIL.SYS_NO THEN
1003 lv_sql_stmt:= 'select count(*) from MRP_AD_OPER_NETWORKS_V'||p_dblink
1004 ||' where RN > :lv_bom_lrn '
1005 ||' and organization_id '|| lv_in_org_str;
1006
1007
1008 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param1 USING lv_bom_lrn ;
1009
1010 lv_sql_stmt:= 'select count(*) '
1011 ||' from MRP_AP_OPER_NETWORKS_V'||p_dblink||' x '
1012 ||' where ( x.RN2 > :lv_bom_lrn '
1013 ||' OR x.RN3 > :lv_bom_lrn '
1014 ||' OR x.RN4 > :lv_bom_lrn '
1015 ||' OR x.RN5 > :lv_bom_lrn '
1016 ||' OR x.RN6 > :lv_bom_lrn ) '
1017 ||' and x.organization_id '|| lv_in_org_str;
1018
1019
1020 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param2 USING lv_bom_lrn,
1021 lv_bom_lrn,
1022 lv_bom_lrn,
1023 lv_bom_lrn,
1024 lv_bom_lrn;
1025
1026 lv_sql_stmt:= ' select count(*) from MRP_AP_BOM_COMPONENTS_V'||p_dblink
1027 ||' where organization_id '|| lv_in_org_str;
1028
1029 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param3;
1030
1031
1032 lv_bom5 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1033 IF lv_bom5 = MSC_UTIL.SYS_TGT THEN
1034 lv_status_decided_bom := MSC_UTIL.SYS_YES;
1035 END IF;
1036 END IF;
1037
1038 */
1039 --lv_bom6
1040 IF lv_status_decided_bom = MSC_UTIL.SYS_NO THEN
1041 lv_sql_stmt:= 'select count(*) from MRP_AD_ROUTING_OPERATIONS_V'||p_dblink
1042 ||' where RN > :lv_bom_lrn '
1043 ||' and organization_id '|| lv_in_org_str;
1044
1045
1046 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param1 USING lv_bom_lrn;
1047
1048 lv_sql_stmt:= 'select count(*) '
1049 ||' from MRP_AP_ROUTING_OPERATIONS_V'||p_dblink||' x '
1050 ||' where ( x.RN1 > :lv_bom_lrn '
1051 ||' OR x.RN2 > :lv_bom_lrn '
1052 ||' OR x.RN3 > :lv_bom_lrn ) '
1053 ||' and x.organization_id '|| lv_in_org_str;
1054
1055
1056 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param2 USING lv_bom_lrn,
1057 lv_bom_lrn,
1058 lv_bom_lrn;
1059
1060
1061 lv_sql_stmt:= 'select count(*) from MRP_AP_ROUTING_OPERATIONS_V'||p_dblink
1062 ||' where organization_id '|| lv_in_org_str;
1063
1064 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param3;
1065
1066
1067 lv_bom6 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1068 IF lv_bom6 = MSC_UTIL.SYS_TGT THEN
1069 lv_status_decided_bom := MSC_UTIL.SYS_YES;
1070 END IF;
1071 END IF;
1072
1073 --lv_bom7
1074 IF lv_status_decided_bom = MSC_UTIL.SYS_NO THEN
1075 lv_sql_stmt:= 'select count(*) from MRP_AD_OP_RESOURCE_SEQS_V'||p_dblink
1076 ||' where RN > :lv_bom_lrn '
1077 ||' and organization_id '|| lv_in_org_str;
1078
1079
1080 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param1 USING lv_bom_lrn;
1081
1082 lv_sql_stmt:= 'select count(*) '
1083 ||' from MRP_AP_OP_RESOURCE_SEQS_V'||p_dblink||' x '
1084 ||' where ( x.RN2 > :lv_bom_lrn '
1085 ||' OR x.RN3 > :lv_bom_lrn '
1086 ||' OR x.RN4 > :lv_bom_lrn '
1087 ||' OR x.RN5 > :lv_bom_lrn ) '
1088 ||' and x.organization_id '|| lv_in_org_str;
1089
1090
1091 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param2 USING lv_bom_lrn,
1092 lv_bom_lrn,
1093 lv_bom_lrn,
1094 lv_bom_lrn;
1095
1096
1097 lv_sql_stmt:= 'select count(*) from MRP_AP_OP_RESOURCE_SEQS_V'||p_dblink
1098 ||' where organization_id '|| lv_in_org_str;
1099
1100 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param3;
1101
1102
1103 lv_bom7 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1104 IF lv_bom7 = MSC_UTIL.SYS_TGT THEN
1105 lv_status_decided_bom := MSC_UTIL.SYS_YES;
1106 END IF;
1107 END IF;
1108
1109 --lv_bom8
1110 IF lv_status_decided_bom = MSC_UTIL.SYS_NO THEN
1111 lv_sql_stmt:= 'select count(*) from MRP_AD_OPERATION_RESOURCES_V'||p_dblink
1112 ||' where RN > :lv_bom_lrn '
1113 ||' and organization_id '|| lv_in_org_str;
1114
1115
1116 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param1 USING lv_bom_lrn;
1117
1118 lv_sql_stmt:= 'select count(*) from MRP_AD_SUB_OPER_RESS_V'||p_dblink
1119 ||' where RN > :lv_bom_lrn '
1120 ||' and organization_id '|| lv_in_org_str;
1121
1122 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param4 USING lv_bom_lrn;
1123
1124 lv_param1 := lv_param1 + lv_param4;
1125
1126 lv_sql_stmt:= 'select count(*) '
1127 ||' from MRP_AP_OPERATION_RESOURCES_V'||p_dblink||' x '
1128 ||' where ( x.RN2 > :lv_bom_lrn '
1129 ||' OR x.RN3 > :lv_bom_lrn '
1130 ||' OR x.RN4 > :lv_bom_lrn ) '
1131 ||' and x.organization_id '|| lv_in_org_str;
1132
1133
1134 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param2 USING lv_bom_lrn,
1135 lv_bom_lrn,
1136 lv_bom_lrn;
1137
1138
1139 lv_sql_stmt:= 'select count(*) from MRP_AP_OP_RESOURCE_SEQS_V'||p_dblink
1140 ||' where organization_id '|| lv_in_org_str;
1141
1142 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param3;
1143
1144
1145 lv_bom8 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1146 IF lv_bom8 = MSC_UTIL.SYS_TGT THEN
1147 lv_status_decided_bom := MSC_UTIL.SYS_YES;
1148 END IF;
1149 END IF;
1150
1151 END IF ;
1152
1153 /* commented the below code
1154 this will be called in the 'IF prec.wip_flag = MSC_UTIL.SYS_YES THEN...' section which handles wip entities
1155 --lv_bom10 views are used only when wip_flag
1156 IF ( prec.wip_flag = MSC_UTIL.SYS_YES) THEN
1157
1158 --lv_bom10
1159 IF lv_status_decided_bom = MSC_UTIL.SYS_NO THEN
1160 lv_sql_stmt:= 'select count(*) from MRP_AD_RESOURCE_REQUIREMENTS_V'||p_dblink
1161 ||' where RN > :lv_wip_lrn '
1162 ||' and organization_id '|| lv_in_org_str;
1163
1164
1165 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_wip_lrn;
1166
1167 lv_sql_stmt:= 'select count(*) '
1168 ||' from MRP_AP_RESOURCE_REQUIREMENTS_V'||p_dblink||' x '
1169 ||' where ( x.RN1 > :lv_wip_lrn '
1170 ||' OR x.RN2 > :lv_wip_lrn '
1171 ||' OR x.RN3 > :lv_wip_lrn ) '
1172 ||' and x.organization_id '|| lv_in_org_str;
1173
1174
1175 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_wip_lrn,
1176 lv_wip_lrn,
1177 lv_wip_lrn;
1178
1179
1180 lv_sql_stmt:= 'select count(*) from MRP_AP_RESOURCE_REQUIREMENTS_V'||p_dblink
1181 ||' where organization_id '|| lv_in_org_str;
1182
1183 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
1184
1185 lv_sql_stmt:= 'select count(*) from MRP_AD_DJOB_SUB_OP_RESOURCES_V'||p_dblink
1186 ||' where RN > :lv_wip_lrn '
1187 ||' and organization_id '|| lv_in_org_str;
1188
1189
1190 EXECUTE IMMEDIATE lv_sql_stmt into lv_param4 USING lv_wip_lrn;
1191
1192 lv_sql_stmt:= 'select count(*) '
1193 ||' from MRP_AP_DJOB_SUB_OP_RESOURCES_V'||p_dblink||' x '
1194 ||' where ( x.RN1 > :lv_wip_lrn '
1195 ||' OR x.RN2 > :lv_wip_lrn '
1196 ||' OR x.RN3 > :lv_wip_lrn )'
1197 ||' and x.organization_id '|| lv_in_org_str;
1198
1199
1200 EXECUTE IMMEDIATE lv_sql_stmt into lv_param5 USING lv_wip_lrn,
1201 lv_wip_lrn,
1202 lv_wip_lrn;
1203
1204
1205 lv_sql_stmt:= 'select count(*) from MRP_AP_DJOB_SUB_OP_RESOURCES_V'||p_dblink
1206 ||' where organization_id '|| lv_in_org_str;
1207
1208 EXECUTE IMMEDIATE lv_sql_stmt into lv_param6;
1209
1210
1211 lv_sql_stmt:= 'select count(*) from MRP_AD_RES_INSTANCE_REQS_V'||p_dblink
1212 ||' where RN > :lv_wip_lrn '
1213 ||' and organization_id '|| lv_in_org_str;
1214
1215
1216 EXECUTE IMMEDIATE lv_sql_stmt into lv_param7 USING lv_wip_lrn;
1217 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'count for ad of res inst req = '||to_char(lv_param7));
1218
1219 lv_sql_stmt:= 'select count(*) '
1220 ||' from MRP_AP_RES_INSTANCE_REQS_V'||p_dblink||' x '
1221 ||' where ( x.RN1 > :lv_wip_lrn '
1222 ||' OR x.RN2 > :lv_wip_lrn '
1223 ||' OR x.RN3 > :lv_wip_lrn '
1224 ||' OR x.RN4 > :lv_wip_lrn ) '
1225 ||' and x.organization_id '|| lv_in_org_str;
1226
1227
1228 EXECUTE IMMEDIATE lv_sql_stmt into lv_param8 USING lv_wip_lrn,
1229 lv_wip_lrn,
1230 lv_wip_lrn,
1231 lv_wip_lrn;
1232
1233 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'count for ap res inst req witl lrn = '||to_char(lv_param8));
1234 lv_sql_stmt:= 'select count(*) from MRP_AP_RES_INSTANCE_REQS_V'||p_dblink
1235 ||' where organization_id '|| lv_in_org_str;
1236
1237 EXECUTE IMMEDIATE lv_sql_stmt into lv_param9 ;
1238 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'count for ap res inst req without lrn = '||to_char(lv_param8));
1239
1240
1241 lv_param1 := lv_param1 + lv_param4 + lv_param7;
1242 lv_param2 := lv_param2 + lv_param5 + lv_param8;
1243 lv_param3 := lv_param3 + lv_param6 + lv_param9;
1244
1245 lv_bom10 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1246 IF lv_bom10 = MSC_UTIL.SYS_TGT THEN
1247 lv_status_decided_bom := MSC_UTIL.SYS_YES;
1248 END IF;
1249 END IF;
1250
1251 END IF;
1252 */
1253
1254 IF prec.bom_flag = MSC_UTIL.SYS_YES THEN
1255 --lv_bom11
1256 IF lv_status_decided_bom = MSC_UTIL.SYS_NO THEN
1257 lv_sql_stmt:= 'select count(*) from MRP_AD_OPERATION_COMPONENTS_V'||p_dblink
1258 ||' where RN > :lv_bom_lrn '
1259 ||' and organization_id '|| lv_in_org_str;
1260
1261
1262 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_bom_lrn;
1263
1264 lv_sql_stmt:= ' select count(*) '
1265 ||' from MRP_AP_OPERATION_COMPONENTS_V'||p_dblink||' x '
1266 ||' where ( x.RN1 > :lv_bom_lrn '
1267 ||' OR x.RN2 > :lv_bom_lrn '
1268 ||' OR x.RN3 > :lv_bom_lrn '
1269 ||' OR x.RN4 > :lv_bom_lrn '
1270 ||' OR x.RN5 > :lv_bom_lrn '
1271 ||' OR x.RN6 > :lv_bom_lrn ) '
1272 ||' and x.organization_id '|| lv_in_org_str;
1273
1274 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_bom_lrn,
1275 lv_bom_lrn,
1276 lv_bom_lrn,
1277 lv_bom_lrn,
1278 lv_bom_lrn,
1279 lv_bom_lrn;
1280
1281 lv_sql_stmt:= 'select count(*) from MRP_AP_OPERATION_COMPONENTS_V'||p_dblink
1282 ||' where organization_id '|| lv_in_org_str;
1283
1284 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
1285
1286
1287 lv_bom11 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1288 IF lv_bom11 = MSC_UTIL.SYS_TGT THEN
1289 lv_status_decided_bom := MSC_UTIL.SYS_YES;
1290 END IF;
1291 END IF;
1292
1293 --lv_bom12
1294 IF lv_status_decided_bom = MSC_UTIL.SYS_NO THEN
1295 lv_sql_stmt:= 'select count(*) from MRP_AD_PROCESS_EFFECTIVITY_V'||p_dblink
1296 ||' where RN > :lv_bom_lrn '
1297 ||' and organization_id '|| lv_in_org_str;
1298
1299
1300 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_bom_lrn;
1301
1302 lv_sql_stmt:= 'select count(*) '
1303 ||' from MRP_AP_PROCESS_EFFECTIVITY_V'||p_dblink||' x '
1304 ||' where ( x.RN2 > :lv_bom_lrn '
1305 ||' OR x.RN3 > :lv_bom_lrn '
1306 ||' OR x.RN4 > :lv_bom_lrn ) '
1307 ||' and x.organization_id '|| lv_in_org_str;
1308
1309
1310 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_bom_lrn,
1311 lv_bom_lrn,
1312 lv_bom_lrn;
1313
1314 lv_sql_stmt:= 'select count(*) from MRP_AP_PROCESS_EFFECTIVITY_V'||p_dblink
1315 ||' where organization_id '|| lv_in_org_str;
1316
1317 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
1318
1319 lv_bom12 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1320 IF lv_bom12 = MSC_UTIL.SYS_TGT THEN
1321 lv_status_decided_bom := MSC_UTIL.SYS_YES;
1322 END IF;
1323 END IF;
1324
1325 END IF;
1326 -- p_bor_sn_flag
1327
1328 IF prec.bor_flag = MSC_UTIL.SYS_YES THEN
1329
1330 --lv_bor1
1331 BEGIN
1332 SELECT MSC_UTIL.SYS_YES
1333 INTO lv_status_decided_bor
1334 FROM fnd_lookup_values
1335 WHERE lookup_type = 'MSC_COLL_SNAPSHOTS' AND
1336 enabled_flag = 'Y' AND
1337 view_application_id = 700 AND
1338 language = userenv('lang') AND
1339 attribute2 = 'MTL_SYS_ITEMS_SN' AND
1340 attribute13 = 'COMPLETE' AND
1341 rownum = 1;
1342 EXCEPTION
1343 WHEN NO_DATA_FOUND THEN
1344 lv_status_decided_bor := MSC_UTIL.SYS_NO;
1345 END;
1346
1347 IF lv_status_decided_bor = MSC_UTIL.SYS_NO THEN
1348 lv_sql_stmt:= 'select count(*) '
1349 ||' from MRP_AP_CRP_RESOURCE_HOURS_V'||p_dblink||' x '
1350 ||' where x.RN2 > :lv_bor_lrn '
1351 ||' and x.organization_id '|| lv_in_org_str;
1352
1353
1354 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_bor_lrn;
1355
1356
1357 lv_sql_stmt:= 'select count(*) from MRP_AP_CRP_RESOURCE_HOURS_V'||p_dblink
1358 ||' where organization_id '|| lv_in_org_str;
1359
1360 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
1361
1362 lv_param2 :=0;
1363
1364 lv_bor1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1365 ELSE
1366 lv_bor1 := MSC_UTIL.SYS_TGT;
1367 END IF;
1368
1369 END IF;
1370
1371 -- p_item_sn_flag
1372
1373 IF prec.item_flag = MSC_UTIL.SYS_YES THEN
1374
1375 BEGIN
1376 SELECT MSC_UTIL.SYS_YES
1377 INTO lv_status_decided_item
1378 FROM fnd_lookup_values
1379 WHERE lookup_type = 'MSC_COLL_SNAPSHOTS' AND
1380 enabled_flag = 'Y' AND
1381 view_application_id = 700 AND
1382 language = userenv('lang') AND
1383 attribute2 IN( 'MTL_SYS_ITEMS_SN','MTL_ITEM_CATS_SN') AND
1384 attribute13 = 'COMPLETE' AND
1385 rownum = 1;
1386 EXCEPTION
1387 WHEN NO_DATA_FOUND THEN
1388 lv_status_decided_item := MSC_UTIL.SYS_NO;
1389 END;
1390
1391 IF lv_status_decided_item = MSC_UTIL.SYS_NO THEN
1392
1393 --lv_item1
1394 lv_sql_stmt:= 'select count(*) from MRP_AD_ITEM_CATEGORIES_V'||p_dblink
1395 ||' where RN > :lv_item_lrn '
1396 ||' and organization_id '|| lv_in_org_str;
1397
1398
1399 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_item_lrn;
1400
1401 lv_sql_stmt:= 'select count(*) '
1402 ||' from MRP_AP_ITEM_CATEGORIES_V'||p_dblink||' x '
1403 ||' where ( x.RN1 > :lv_item_lrn '
1404 ||' OR x.RN2 > :lv_item_lrn ) '
1405 ||' and x.organization_id '|| lv_in_org_str;
1406
1407
1408 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_item_lrn,lv_item_lrn;
1409
1410
1411 lv_sql_stmt:= 'select count(*) from MRP_AP_ITEM_CATEGORIES_V'||p_dblink
1412 ||' where organization_id '|| lv_in_org_str;
1413
1414 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
1415
1416
1417
1418
1419 lv_item1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1420 IF lv_item1 = MSC_UTIL.SYS_TGT THEN
1421 lv_status_decided_item := MSC_UTIL.SYS_YES;
1422 END IF;
1423 ELSE
1424 lv_item1 := MSC_UTIL.SYS_TGT;
1425 END IF;
1426
1427 --lv_item2
1428 IF lv_status_decided_item = MSC_UTIL.SYS_NO THEN
1429 lv_sql_stmt:= 'select count(*) '
1430 ||' from MRP_AP_SYSTEM_ITEMS_V'||p_dblink||' x '
1431 ||' where x.RN1 > :lv_item_lrn '
1432 ||' and x.organization_id '|| lv_in_org_str;
1433
1434
1435 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_item_lrn;
1436
1437
1438 lv_sql_stmt:= 'select count(*) from MRP_AP_SYSTEM_ITEMS_V'||p_dblink
1439 ||' where organization_id '|| lv_in_org_str;
1440
1441 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
1442
1443 lv_param2 :=0;
1444
1445 lv_item2 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1446 END IF;
1447
1448 END IF;
1449
1450 --p_oh_sn_flag
1451 IF prec.oh_flag = MSC_UTIL.SYS_YES THEN
1452 --lv_oh1
1453 BEGIN
1454 SELECT MSC_UTIL.SYS_YES
1455 INTO lv_status_decided_oh
1456 FROM fnd_lookup_values
1457 WHERE lookup_type = 'MSC_COLL_SNAPSHOTS' AND
1458 enabled_flag = 'Y' AND
1459 view_application_id = 700 AND
1460 language = userenv('lang') AND
1461 attribute2 IN('MTL_OH_QTYS_SN', 'MTL_SYS_ITEMS_SN') AND
1462 attribute13 = 'COMPLETE' AND
1463 rownum = 1;
1464 EXCEPTION
1465 WHEN NO_DATA_FOUND THEN
1466 lv_status_decided_oh := MSC_UTIL.SYS_NO;
1467 END;
1468
1469 IF lv_status_decided_oh = MSC_UTIL.SYS_NO THEN
1470 lv_sql_stmt:= 'select count(*) from MRP_AD_ONHAND_SUPPLIES_V'||p_dblink
1471 ||' where RN > :lv_oh_lrn '
1472 ||' and organization_id '|| lv_in_org_str;
1473
1474
1475 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_oh_lrn;
1476
1477 lv_sql_stmt:= 'select count(*) '
1478 ||' from MRP_AP_ONHAND_SUPPLIES_V'||p_dblink||' x '
1479 ||' where ( x.RN1 > :lv_oh_lrn '
1480 ||' OR x.RN2 > :lv_oh_lrn ) '
1481 ||' and x.organization_id '|| lv_in_org_str;
1482
1483
1484 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_oh_lrn,
1485 lv_oh_lrn;
1486
1487
1488 lv_sql_stmt:= 'select count(*) from MRP_AP_ONHAND_SUPPLIES_V'||p_dblink
1489 ||' where organization_id '|| lv_in_org_str;
1490
1491 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
1492
1493
1494 lv_oh1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1495 ELSE
1496 lv_oh1 := MSC_UTIL.SYS_TGT;
1497 END IF;
1498
1499 END IF;
1500
1501
1502 --p_usup_sn_flag
1503 IF prec.user_supply_demand_flag = MSC_UTIL.SYS_YES THEN
1504
1505 BEGIN
1506 SELECT MSC_UTIL.SYS_YES
1507 INTO lv_status_decided_usup
1508 FROM fnd_lookup_values
1509 WHERE lookup_type = 'MSC_COLL_SNAPSHOTS' AND
1510 enabled_flag = 'Y' AND
1511 view_application_id = 700 AND
1512 language = userenv('lang') AND
1513 attribute2 IN ('MTL_U_SUPPLY_SN', 'MTL_SYS_ITEMS_SN') AND
1514 attribute13 = 'COMPLETE' AND
1515 rownum = 1;
1516 EXCEPTION
1517 WHEN NO_DATA_FOUND THEN
1518 lv_status_decided_usup := MSC_UTIL.SYS_NO;
1519 END;
1520
1521 IF lv_status_decided_usup = MSC_UTIL.SYS_NO THEN
1522 --lv_usup1
1523 lv_sql_stmt:= 'select count(*) from MRP_AD_USER_SUPPLIES_V'||p_dblink
1524 ||' where RN > :lv_usd_lrn '
1525 ||' and organization_id '|| lv_in_org_str;
1526
1527
1528 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_oh_lrn;
1529
1530 lv_sql_stmt:= 'select count(*) '
1531 ||' from MRP_AP_USER_SUPPLIES_V'||p_dblink||' x '
1532 ||' where ( x.RN1 > :lv_usd_lrn '
1533 ||' OR x.RN2 > :lv_usd_lrn ) '
1534 ||' and x.organization_id '|| lv_in_org_str;
1535
1536
1537 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_usd_lrn,
1538 lv_usd_lrn;
1539
1540
1541 lv_sql_stmt:= 'select count(*) from MRP_AP_USER_SUPPLIES_V'||p_dblink
1542 ||' where organization_id '|| lv_in_org_str;
1543
1544 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
1545
1546 lv_usup1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1547 ELSE
1548 lv_usup1 := MSC_UTIL.SYS_TGT;
1549 END IF;
1550
1551 --p_udmd_sn_flag
1552 BEGIN
1553 SELECT MSC_UTIL.SYS_YES
1554 INTO lv_status_decided_udem
1555 FROM fnd_lookup_values
1556 WHERE lookup_type = 'MSC_COLL_SNAPSHOTS' AND
1557 enabled_flag = 'Y' AND
1558 view_application_id = 700 AND
1559 language = userenv('lang') AND
1560 attribute2 IN ('MTL_U_DEMAND_SN', 'MTL_SYS_ITEMS_SN') AND
1561 attribute13 = 'COMPLETE' AND
1562 rownum = 1;
1563 EXCEPTION
1564 WHEN NO_DATA_FOUND THEN
1565 lv_status_decided_udem := MSC_UTIL.SYS_NO;
1566 END;
1567
1568 IF lv_status_decided_udem = MSC_UTIL.SYS_NO THEN
1569 --lv_udmd1
1570 lv_sql_stmt:= 'select count(*) from MRP_AD_USER_DEMANDS_V'||p_dblink
1571 ||' where RN > :lv_usd_lrn '
1572 ||' and organization_id '|| lv_in_org_str;
1573
1574
1575 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_oh_lrn;
1576
1577 lv_sql_stmt:= 'select count(*) '
1578 ||' from MRP_AP_USER_DEMANDS_V'||p_dblink||' x '
1579 ||' where ( x.RN1 > :lv_usd_lrn '
1580 ||' OR x.RN2 > :lv_usd_lrn ) '
1581 ||' and x.organization_id '|| lv_in_org_str;
1582
1583
1584 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_usd_lrn,
1585 lv_usd_lrn;
1586
1587
1588 lv_sql_stmt:= 'select count(*) from MRP_AP_USER_DEMANDS_V'||p_dblink
1589 ||' where organization_id '|| lv_in_org_str;
1590
1591 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
1592
1593
1594 lv_udmd1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1595 ELSE
1596 lv_udmd1 := MSC_UTIL.SYS_TGT;
1597 END IF;
1598
1599 END IF;
1600
1601
1602 --p_so_sn_flag
1603 IF (prec.sales_order_flag =MSC_UTIL.SYS_YES) THEN
1604 BEGIN
1605 SELECT MSC_UTIL.SYS_YES
1606 INTO lv_status_decided_so
1607 FROM fnd_lookup_values
1608 WHERE lookup_type = 'MSC_COLL_SNAPSHOTS' AND
1609 enabled_flag = 'Y' AND
1610 view_application_id = 700 AND
1611 language = userenv('lang') AND
1612 attribute2 IN ('MTL_DEMAND_SN', 'OE_ODR_LINES_SN', 'MTL_SYS_ITEMS_SN') AND
1613 attribute13 = 'COMPLETE' AND
1614 rownum = 1;
1615 EXCEPTION
1616 WHEN NO_DATA_FOUND THEN
1617 lv_status_decided_so := MSC_UTIL.SYS_NO;
1618 END;
1619 IF lv_status_decided_so = MSC_UTIL.SYS_NO THEN
1620 --lv_so1
1621 lv_sql_stmt:= 'select count(*) from MRP_AD_HARD_RESERVATIONS_V'||p_dblink
1622 ||' where RN > :lv_rsv_lrn '
1623 ||' and organization_id '|| lv_in_org_str;
1624
1625
1626 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_rsv_lrn;
1627
1628
1629 lv_sql_stmt:= 'select count(*) from MRP_AD_SALES_ORDERS_V'||p_dblink
1630 ||' where RN > :lv_so_lrn '
1631 ||' and organization_id '|| lv_in_org_str;
1632
1633
1634 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_so_lrn;
1635
1636 lv_param1 :=lv_param1 + lv_param2;
1637
1638
1639 lv_sql_stmt:= 'select count(*) '
1640 ||' from MRP_AN1_SALES_ORDERS_V'||p_dblink||' x '
1641 ||' where ( x.RN1 > :lv_so_lrn '
1642 ||' OR x.RN2 > :lv_so_lrn '
1643 ||' OR x.RN3 > :lv_so_lrn ) '
1644 ||' and x.organization_id '|| lv_in_org_str;
1645
1646
1647 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_so_lrn,
1648 lv_so_lrn,
1649 lv_so_lrn;
1650
1651 lv_sql_stmt:= 'select count(*) '
1652 ||' from MRP_AN2_SALES_ORDERS_V'||p_dblink||' x '
1653 ||' where ( x.RN1 > :lv_so_lrn '
1654 ||' OR x.RN2 > :lv_so_lrn ) '
1655 ||' and x.organization_id '|| lv_in_org_str;
1656
1657
1658 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3 USING lv_so_lrn,
1659 lv_so_lrn;
1660
1661 lv_sql_stmt:= 'select count(*) '
1662 ||' from MRP_AN3_SALES_ORDERS_V'||p_dblink||' x '
1663 ||' where ( x.RN1 > :lv_so_lrn '
1664 ||' OR x.RN2 > :lv_so_lrn ) '
1665 ||' and x.organization_id '|| lv_in_org_str;
1666
1667
1668 EXECUTE IMMEDIATE lv_sql_stmt into lv_param4 USING lv_so_lrn,
1669 lv_so_lrn;
1670 /*
1671 lv_sql_stmt:= 'select count(*) '
1672 ||' from MRP_AN4_SALES_ORDERS_V'||p_dblink||' x '
1673 ||' where ( x.RN1 > :lv_so_lrn '
1674 ||' OR x.RN2 > :lv_so_lrn ) '
1675 ||' and x.organization_id '|| lv_in_org_str;
1676
1677
1678 EXECUTE IMMEDIATE lv_sql_stmt into lv_param5 USING lv_so_lrn,
1679 lv_so_lrn;
1680 */
1681 lv_param2 := lv_param2 + lv_param3 + lv_param4; -- + lv_param5;
1682
1683
1684 lv_sql_stmt:= 'select count(*) from MRP_AP1_SALES_ORDERS_V'||p_dblink
1685 ||' where organization_id '|| lv_in_org_str;
1686
1687 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
1688
1689 lv_sql_stmt:= 'select count(*) from MRP_AP2_SALES_ORDERS_V'||p_dblink
1690 ||' where organization_id '|| lv_in_org_str;
1691
1692 EXECUTE IMMEDIATE lv_sql_stmt into lv_param4;
1693
1694 lv_sql_stmt:= 'select count(*) from MRP_AP3_SALES_ORDERS_V'||p_dblink
1695 ||' where organization_id '|| lv_in_org_str;
1696
1697 EXECUTE IMMEDIATE lv_sql_stmt into lv_param5;
1698 /*
1699 lv_sql_stmt:= 'select count(*) from MRP_AP4_SALES_ORDERS_V'||p_dblink
1700 ||' where organization_id '|| lv_in_org_str;
1701
1702 EXECUTE IMMEDIATE lv_sql_stmt into lv_param6;
1703 */
1704
1705 lv_param3 := lv_param3 + lv_param4 + lv_param5; -- + lv_param6;
1706
1707 lv_so1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1708 ELSE
1709 lv_so1 := MSC_UTIL.SYS_TGT;
1710 END IF;
1711
1712 END IF;
1713
1714 --p_fcst_sn_flag
1715 IF prec.forecast_flag = MSC_UTIL.SYS_YES THEN
1716 BEGIN
1717 SELECT MSC_UTIL.SYS_YES
1718 INTO lv_status_decided_fcst
1719 FROM fnd_lookup_values
1720 WHERE lookup_type = 'MSC_COLL_SNAPSHOTS' AND
1721 enabled_flag = 'Y' AND
1722 view_application_id = 700 AND
1723 language = userenv('lang') AND
1724 attribute2 = 'MRP_FORECAST_DSGN_SN' AND
1725 attribute13 = 'COMPLETE' AND
1726 rownum = 1;
1727 EXCEPTION
1728 WHEN NO_DATA_FOUND THEN
1729 lv_status_decided_fcst := MSC_UTIL.SYS_NO;
1730 END;
1731
1732 IF lv_status_decided_fcst = MSC_UTIL.SYS_NO THEN
1733 --lv_fcst1
1734
1735 lv_sql_stmt:= 'select count(*) from MRP_AD_FORECAST_DSGN_V'||p_dblink
1736 ||' where RN > :lv_forecast_lrn '
1737 ||' and organization_id '|| lv_in_org_str;
1738
1739
1740 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_forecast_lrn;
1741
1742 lv_sql_stmt:= 'select count(*) '
1743 ||' from MRP_AP_FORECAST_DSGN_V'||p_dblink||' x'
1744 ||' where x.RN1 > :lv_forecast_lrn '
1745 ||' and x.organization_id '|| lv_in_org_str;
1746
1747
1748 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_forecast_lrn;
1749
1750 lv_sql_stmt:= 'select count(*) from MRP_AP_FORECAST_DSGN_V'||p_dblink
1751 ||' where organization_id '|| lv_in_org_str;
1752
1753 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
1754
1755 lv_fcst1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1756
1757 --lv_fcst2
1758
1759 lv_sql_stmt:= 'select count(*) from MRP_AD_FORECAST_DEMAND_V'||p_dblink
1760 ||' where RN > :lv_forecast_lrn '
1761 ||' and organization_id '|| lv_in_org_str;
1762
1763
1764 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_forecast_lrn;
1765
1766 lv_sql_stmt:= 'select count(*) '
1767 ||' from MRP_AP_FORECAST_DEMAND_V'||p_dblink||' x'
1768 ||' where x.RN1 > :lv_forecast_lrn '
1769 ||' and x.organization_id '|| lv_in_org_str;
1770
1771
1772 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_forecast_lrn;
1773
1774 lv_sql_stmt:= 'select count(*) from MRP_AP_FORECAST_DEMAND_V'||p_dblink
1775 ||' where organization_id '|| lv_in_org_str;
1776
1777 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
1778
1779 lv_fcst2 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1780
1781 ELSE
1782 lv_fcst1 := MSC_UTIL.SYS_TGT;
1783 lv_fcst2 := MSC_UTIL.SYS_TGT;
1784 END IF;
1785
1786 END IF;
1787
1788 -- p_wip_sn_flag
1789 IF prec.wip_flag = MSC_UTIL.SYS_YES THEN
1790
1791 BEGIN
1792 SELECT MSC_UTIL.SYS_YES
1793 INTO lv_status_decided_wip
1794 FROM fnd_lookup_values
1795 WHERE lookup_type = 'MSC_COLL_SNAPSHOTS' AND
1796 enabled_flag = 'Y' AND
1797 view_application_id = 700 AND
1798 language = userenv('lang') AND
1799 attribute2 IN
1800 ('WIP_DSCR_JOBS_SN', 'WIP_FLOW_SCHDS_SN', 'WIP_OPR_RES_INSTS_SN', 'MTL_SYS_ITEMS_SN',
1801 'WIP_REPT_ITEMS_SN', 'WIP_REPT_SCHDS_SN', 'WIP_WLINES_SN', 'WIP_WOPR_NETWORKS_SN',
1802 'WIP_WOPR_RESS_SN', 'WIP_WOPR_SUB_RESS_SN', 'WIP_WOPRS_SN', 'WIP_WREQ_OPRS_SN') AND
1803 attribute13 = 'COMPLETE' AND
1804 rownum = 1;
1805 EXCEPTION
1806 WHEN NO_DATA_FOUND THEN
1807 lv_status_decided_wip := MSC_UTIL.SYS_NO;
1808 END;
1809
1810 IF lv_status_decided_wip = MSC_UTIL.SYS_NO THEN
1811 --lv_wip1
1812
1813 lv_sql_stmt:= 'select count(*) from MRP_AD_WIP_JOB_SUPPLIES_V'||p_dblink
1814 ||' where RN > :lv_wip_lrn '
1815 ||' and organization_id '|| lv_in_org_str;
1816
1817
1818 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_wip_lrn;
1819
1820 lv_sql_stmt:= 'select count(*) from MRP_AD_WIP_COMP_SUPPLIES_V'||p_dblink
1821 ||' where RN > :lv_wip_lrn '
1822 ||' and organization_id '|| lv_in_org_str;
1823
1824
1825 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_wip_lrn;
1826
1827 lv_sql_stmt:= 'select count(*) from MRP_AD_REPT_ITEM_SUPPLIES_V'||p_dblink
1828 ||' where RN > :lv_wip_lrn '
1829 ||' and organization_id '|| lv_in_org_str;
1830
1831
1832 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3 USING lv_wip_lrn;
1833
1834 lv_param1 := lv_param1 + lv_param2 + lv_param3;
1835
1836 lv_sql_stmt:= 'select count(*) '
1837 ||' from MRP_AP_WIP_JOB_SUPPLIES_V'||p_dblink||' x '
1838 ||' where ( x.RN1 > :lv_wip_lrn '
1839 ||' OR x.RN2 > :lv_wip_lrn )'
1840 ||' and x.organization_id '|| lv_in_org_str;
1841
1842
1843 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_wip_lrn,
1844 lv_wip_lrn;
1845
1846 lv_sql_stmt:= 'select count(*) '
1847 ||' from MRP_AP_WIP_COMP_SUPPLIES_V'||p_dblink||' x '
1848 ||' where ( x.RN1 > :lv_wip_lrn '
1849 ||' OR x.RN2 > :lv_wip_lrn'
1850 ||' OR x.RN3 > :lv_wip_lrn )'
1851 ||' and x.organization_id '|| lv_in_org_str;
1852
1853
1854 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3 USING lv_wip_lrn,
1855 lv_wip_lrn,
1856 lv_wip_lrn;
1857
1858 lv_sql_stmt:= 'select count(*) '
1859 ||' from MRP_AP_REPT_ITEM_SUPPLIES_V'||p_dblink||' x '
1860 ||' where ( x.RN1 > :lv_wip_lrn '
1861 ||' OR x.RN2 > :lv_wip_lrn '
1862 ||' OR x.RN3 > :lv_wip_lrn '
1863 ||' OR x.RN4 > :lv_wip_lrn ) '
1864 ||' and x.organization_id '|| lv_in_org_str;
1865
1866
1867 EXECUTE IMMEDIATE lv_sql_stmt into lv_param4 USING lv_wip_lrn,
1868 lv_wip_lrn,
1869 lv_wip_lrn,
1870 lv_wip_lrn;
1871
1872 lv_param2 := lv_param2 + lv_param3 + lv_param4;
1873
1874
1875 lv_sql_stmt:= 'select count(*) from MRP_AP_WIP_JOB_SUPPLIES_V'||p_dblink
1876 ||' where organization_id '|| lv_in_org_str;
1877
1878 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
1879
1880 lv_sql_stmt:= 'select count(*) from MRP_AP_WIP_COMP_SUPPLIES_V'||p_dblink
1881 ||' where organization_id '|| lv_in_org_str;
1882
1883 EXECUTE IMMEDIATE lv_sql_stmt into lv_param4;
1884
1885 lv_sql_stmt:= 'select count(*) from MRP_AP_REPT_ITEM_SUPPLIES_V'||p_dblink
1886 ||' where organization_id '|| lv_in_org_str;
1887
1888 EXECUTE IMMEDIATE lv_sql_stmt into lv_param5;
1889
1890 lv_param3 := lv_param3 + lv_param4 + lv_param5;
1891
1892 lv_wip1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1893
1894 IF lv_wip1 = MSC_UTIL.SYS_TGT THEN
1895 lv_status_decided_wip := MSC_UTIL.SYS_YES;
1896 END IF;
1897 ELSE
1898 lv_wip1 := MSC_UTIL.SYS_TGT;
1899 END IF;
1900
1901 IF lv_status_decided_wip = MSC_UTIL.SYS_NO THEN
1902 --lv_wip2
1903
1904 lv_sql_stmt:= 'select count(*) from MRP_AD_WIP_COMP_DEMANDS_V'||p_dblink
1905 ||' where RN > :lv_wip_lrn '
1906 ||' and organization_id '|| lv_in_org_str;
1907
1908
1909 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_wip_lrn;
1910
1911 lv_sql_stmt:= 'select count(*) from MRP_AD_WIP_FLOW_DEMANDS_V'||p_dblink
1912 ||' where RN > :lv_wip_lrn '
1913 ||' and organization_id '|| lv_in_org_str;
1914
1915
1916 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_wip_lrn;
1917
1918 lv_sql_stmt:= 'select count(*) from MRP_AD_REPT_ITEM_DEMANDS_V'||p_dblink
1919 ||' where RN > :lv_wip_lrn '
1920 ||' and organization_id '|| lv_in_org_str;
1921
1922
1923 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3 USING lv_wip_lrn;
1924
1925
1926 lv_param1 := lv_param1 + lv_param2 + lv_param3;
1927
1928
1929 lv_sql_stmt:= ' select count(*) '
1930 ||' from MRP_AP_WIP_COMP_DEMANDS_V'||p_dblink||' x '
1931 ||' where ( x.RN1 > :lv_wip_lrn '
1932 ||' OR x.RN2 > :lv_wip_lrn '
1933 ||' OR x.RN3 > :lv_wip_lrn ) '
1934 ||' and x.organization_id '|| lv_in_org_str;
1935
1936
1937 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_wip_lrn,
1938 lv_wip_lrn,
1939 lv_wip_lrn;
1940
1941 lv_sql_stmt:= 'select count(*) '
1942 ||' from MRP_AP_REPT_ITEM_DEMANDS_V'||p_dblink||' x '
1943 ||' where ( x.RN1 > :lv_wip_lrn '
1944 ||' OR x.RN2 > :lv_wip_lrn '
1945 ||' OR x.RN3 > :lv_wip_lrn '
1946 ||' OR x.RN4 > :lv_wip_lrn '
1947 ||' OR x.RN5 > :lv_wip_lrn ) '
1948 ||' and x.organization_id '|| lv_in_org_str;
1949
1950
1951 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3 USING lv_wip_lrn,
1952 lv_wip_lrn,
1953 lv_wip_lrn,
1954 lv_wip_lrn,
1955 lv_wip_lrn;
1956 lv_param2 := lv_param2 + lv_param3;
1957
1958 lv_sql_stmt:= 'select count(*) from MRP_AP_WIP_COMP_DEMANDS_V'||p_dblink
1959 ||' where organization_id '|| lv_in_org_str;
1960
1961 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
1962
1963 lv_sql_stmt:= 'select count(*) from MRP_AP_REPT_ITEM_DEMANDS_V'||p_dblink
1964 ||' where organization_id '|| lv_in_org_str;
1965
1966 EXECUTE IMMEDIATE lv_sql_stmt into lv_param4;
1967
1968 lv_param3 := lv_param3 + lv_param4;
1969
1970 lv_wip2 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
1971
1972 IF lv_wip2 = MSC_UTIL.SYS_TGT THEN
1973 lv_status_decided_wip := MSC_UTIL.SYS_YES;
1974 END IF;
1975 END IF;
1976
1977
1978 -- lv_wip3
1979 IF lv_status_decided_wip = MSC_UTIL.SYS_NO THEN
1980 lv_sql_stmt:= 'select LBJ_DETAILS '
1981 || ' from msc_apps_instances '
1982 || ' where instance_id = ' || p_instance_id ;
1983 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1;
1984
1985 if(lv_param1 = 1) then
1986 lv_sql_stmt:= 'select count(*) from MRP_AD_JOB_OP_NETWORKS_V'||p_dblink
1987 ||' where RN > :lv_wip_lrn '
1988 ||' and organization_id '|| lv_in_org_str;
1989
1990
1991 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_wip_lrn;
1992
1993 lv_sql_stmt:= 'select count(*) from MRP_AD_JOB_OPERATIONS_V'||p_dblink
1994 ||' where RN > :lv_wip_lrn '
1995 ||' and organization_id '|| lv_in_org_str;
1996
1997
1998 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_wip_lrn;
1999
2000 lv_sql_stmt:= 'select count(*) from MRP_AD_REQUIREMENT_OPS_V'||p_dblink
2001 ||' where RN > :lv_wip_lrn '
2002 ||' and organization_id '|| lv_in_org_str;
2003
2004
2005 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3 USING lv_wip_lrn;
2006
2007 lv_sql_stmt:= 'select count(*) from MRP_AD_JOB_OP_RESOURCES_V'||p_dblink
2008 ||' where RN > :lv_wip_lrn '
2009 ||' and organization_id '|| lv_in_org_str;
2010
2011
2012 EXECUTE IMMEDIATE lv_sql_stmt into lv_param4 USING lv_wip_lrn;
2013
2014 lv_sql_stmt:= 'select count(*) from MRP_AD_LJ_SUB_OP_RESOURCES_V'||p_dblink
2015 ||' where RN > :lv_wip_lrn '
2016 ||' and organization_id '|| lv_in_org_str;
2017
2018
2019 EXECUTE IMMEDIATE lv_sql_stmt into lv_param5 USING lv_wip_lrn;
2020
2021 /* ds change */
2022 lv_sql_stmt:= 'select count(*) from MRP_AD_LJ_OPR_RES_INSTS_V'||p_dblink
2023 ||' where RN > :lv_wip_lrn '
2024 ||' and organization_id '|| lv_in_org_str;
2025 EXECUTE IMMEDIATE lv_sql_stmt into lv_param6 USING lv_wip_lrn;
2026 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'count for ad lj opr res inst = '||to_char(lv_param6));
2027 /* ds change *end/
2028
2029 lv_param1 := lv_param1 + lv_param2 + lv_param3 + lv_param4 + lv_param5
2030 + lv_param6;
2031
2032 lv_sql_stmt:= 'select count(*) '
2033 ||' from MRP_AP_JOB_OP_NETWORKS_V'||p_dblink||' x '
2034 ||' where ( x.RN > :lv_wip_lrn '
2035 ||' OR x.RN1 > :lv_wip_lrn '
2036 ||' OR x.RN2 > :lv_wip_lrn ) '
2037 ||' and x.organization_id '|| lv_in_org_str;
2038
2039
2040 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_wip_lrn,
2041 lv_wip_lrn,
2042 lv_wip_lrn;
2043
2044 lv_sql_stmt:= 'select count(*) '
2045 ||' from MRP_AP_JOB_OPERATIONS_V'||p_dblink||' x '
2046 ||' where ( x.RN > :lv_wip_lrn '
2047 ||' OR x.RN1 > :lv_wip_lrn '
2048 ||' OR x.RN2 > :lv_wip_lrn ) '
2049 ||' and x.organization_id '|| lv_in_org_str;
2050
2051
2052 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3 USING lv_wip_lrn,
2053 lv_wip_lrn,
2054 lv_wip_lrn;
2055
2056 lv_sql_stmt:= 'select count(*) '
2057 ||' from MRP_AP_JOB_REQUIREMENT_OPS_V'||p_dblink||' x '
2058 ||' where ( x.RN > :lv_wip_lrn '
2059 ||' OR x.RN1 > :lv_wip_lrn '
2060 ||' OR x.RN2 > :lv_wip_lrn ) '
2061 ||' and x.organization_id '|| lv_in_org_str;
2062
2063
2064 EXECUTE IMMEDIATE lv_sql_stmt into lv_param4 USING lv_wip_lrn,
2065 lv_wip_lrn,
2066 lv_wip_lrn;
2067
2068 lv_sql_stmt:= 'select count(*) '
2069 ||' from MRP_AP_JOB_OP_RESOURCES_V'||p_dblink||' x '
2070 ||' where ( x.RN > :lv_wip_lrn '
2071 ||' OR x.RN1 > :lv_wip_lrn '
2072 ||' OR x.RN2 > :lv_wip_lrn ) '
2073 ||' and x.organization_id '|| lv_in_org_str;
2074
2075
2076 EXECUTE IMMEDIATE lv_sql_stmt into lv_param5 USING lv_wip_lrn,
2077 lv_wip_lrn,
2078 lv_wip_lrn;
2079
2080 lv_sql_stmt:= 'select count(*) '
2081 ||' from MRP_AP_LJ_SUB_OP_RESOURCES_V'||p_dblink||' x '
2082 ||' where ( x.RN1 > :lv_wip_lrn '
2083 ||' OR x.RN2 > :lv_wip_lrn '
2084 ||' OR x.RN3 > :lv_wip_lrn )'
2085 ||' and x.organization_id '|| lv_in_org_str;
2086
2087
2088 EXECUTE IMMEDIATE lv_sql_stmt into lv_param6 USING lv_wip_lrn,
2089 lv_wip_lrn,
2090 lv_wip_lrn;
2091 /* ds change */
2092 lv_sql_stmt:= 'select count(*) '
2093 ||' from MRP_AP_JOB_RES_INSTANCES_V'||p_dblink||' x '
2094 ||' where ( x.RN1 > :lv_wip_lrn '
2095 ||' OR x.RN2 > :lv_wip_lrn '
2096 ||' OR x.RN3 > :lv_wip_lrn ) '
2097 ||' and x.organization_id '|| lv_in_org_str;
2098 EXECUTE IMMEDIATE lv_sql_stmt into lv_param7 USING
2099 lv_wip_lrn,
2100 lv_wip_lrn,
2101 lv_wip_lrn;
2102 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'count for ap job res inst = '||to_char(lv_param7));
2103 /* ds change end */
2104 lv_param2 := lv_param2 + lv_param3 + lv_param4 + lv_param5 + lv_param6
2105 + lv_param7;
2106
2107 lv_sql_stmt:= 'select count(*) from MRP_AP_JOB_OP_NETWORKS_V'||p_dblink
2108 ||' where organization_id '|| lv_in_org_str;
2109
2110 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
2111
2112 lv_sql_stmt:= 'select count(*) from MRP_AP_JOB_OPERATIONS_V'||p_dblink
2113 ||' where organization_id '|| lv_in_org_str;
2114
2115 EXECUTE IMMEDIATE lv_sql_stmt into lv_param4;
2116
2117 lv_sql_stmt:= 'select count(*) from MRP_AP_JOB_REQUIREMENT_OPS_V'||p_dblink
2118 ||' where organization_id '|| lv_in_org_str;
2119
2120 EXECUTE IMMEDIATE lv_sql_stmt into lv_param5;
2121
2122 lv_sql_stmt:= 'select count(*) from MRP_AP_JOB_OP_RESOURCES_V'||p_dblink
2123 ||' where organization_id '|| lv_in_org_str;
2124
2125 EXECUTE IMMEDIATE lv_sql_stmt into lv_param6;
2126
2127 lv_sql_stmt:= 'select count(*) from MRP_AP_LJ_SUB_OP_RESOURCES_V'||p_dblink
2128 ||' where organization_id '|| lv_in_org_str;
2129
2130 EXECUTE IMMEDIATE lv_sql_stmt into lv_param7;
2131
2132 /* ds change start */
2133 lv_sql_stmt:= 'select count(*) from MRP_AP_JOB_RES_INSTANCES_V'||p_dblink
2134 ||' where organization_id '|| lv_in_org_str;
2135
2136 EXECUTE IMMEDIATE lv_sql_stmt into lv_param8;
2137 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'count for ap job res inst = '||to_char(lv_param8));
2138 /* ds change end */
2139
2140 lv_param3 := lv_param3 + lv_param4 + lv_param5 + lv_param6 + lv_param7
2141 + lv_param8;
2142
2143 lv_wip3 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
2144 IF lv_wip3 = MSC_UTIL.SYS_TGT THEN
2145 lv_status_decided_wip := MSC_UTIL.SYS_YES;
2146 END IF;
2147 end if; -- lv_param1 = 1
2148 END IF; -- lv_wip3 status
2149
2150 IF lv_status_decided_wip = MSC_UTIL.SYS_NO THEN
2151 -- lv_bom10
2152 lv_sql_stmt:= 'select count(*) from MRP_AD_RESOURCE_REQUIREMENTS_V'||p_dblink
2153 ||' where RN > :lv_wip_lrn '
2154 ||' and organization_id '|| lv_in_org_str;
2155
2156
2157 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_wip_lrn;
2158
2159 lv_sql_stmt:= 'select count(*) '
2160 ||' from MRP_AP_RESOURCE_REQUIREMENTS_V'||p_dblink||' x '
2161 ||' where ( x.RN1 > :lv_wip_lrn '
2162 ||' OR x.RN2 > :lv_wip_lrn '
2163 ||' OR x.RN3 > :lv_wip_lrn ) '
2164 ||' and x.organization_id '|| lv_in_org_str;
2165
2166
2167 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_wip_lrn,
2168 lv_wip_lrn,
2169 lv_wip_lrn;
2170
2171
2172 lv_sql_stmt:= 'select count(*) from MRP_AP_RESOURCE_REQUIREMENTS_V'||p_dblink
2173 ||' where organization_id '|| lv_in_org_str;
2174
2175 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
2176
2177 lv_sql_stmt:= 'select count(*) from MRP_AD_DJOB_SUB_OP_RESOURCES_V'||p_dblink
2178 ||' where RN > :lv_wip_lrn '
2179 ||' and organization_id '|| lv_in_org_str;
2180
2181
2182 EXECUTE IMMEDIATE lv_sql_stmt into lv_param4 USING lv_wip_lrn;
2183
2184 lv_sql_stmt:= 'select count(*) '
2185 ||' from MRP_AP_DJOB_SUB_OP_RESOURCES_V'||p_dblink||' x '
2186 ||' where ( x.RN1 > :lv_wip_lrn '
2187 ||' OR x.RN2 > :lv_wip_lrn '
2188 ||' OR x.RN3 > :lv_wip_lrn )'
2189 ||' and x.organization_id '|| lv_in_org_str;
2190
2191
2192 EXECUTE IMMEDIATE lv_sql_stmt into lv_param5 USING lv_wip_lrn,
2193 lv_wip_lrn,
2194 lv_wip_lrn;
2195
2196
2197 lv_sql_stmt:= 'select count(*) from MRP_AP_DJOB_SUB_OP_RESOURCES_V'||p_dblink
2198 ||' where organization_id '|| lv_in_org_str;
2199
2200 EXECUTE IMMEDIATE lv_sql_stmt into lv_param6;
2201
2202
2203 /* ds change start */
2204 lv_sql_stmt:= 'select count(*) from MRP_AD_RES_INSTANCE_REQS_V'||p_dblink
2205 ||' where RN > :lv_wip_lrn '
2206 ||' and organization_id '|| lv_in_org_str;
2207
2208
2209 EXECUTE IMMEDIATE lv_sql_stmt into lv_param7 USING lv_wip_lrn;
2210 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'count for ad of res inst req = '||to_char(lv_param7));
2211
2212 lv_sql_stmt:= 'select count(*) '
2213 ||' from MRP_AP_RES_INSTANCE_REQS_V'||p_dblink||' x '
2214 ||' where ( x.RN1 > :lv_wip_lrn '
2215 ||' OR x.RN2 > :lv_wip_lrn '
2216 ||' OR x.RN3 > :lv_wip_lrn '
2217 ||' OR x.RN4 > :lv_wip_lrn ) '
2218 ||' and x.organization_id '|| lv_in_org_str;
2219
2220
2221 EXECUTE IMMEDIATE lv_sql_stmt into lv_param8 USING lv_wip_lrn,
2222 lv_wip_lrn,
2223 lv_wip_lrn,
2224 lv_wip_lrn;
2225
2226 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'count for ap res inst req witl lrn = '||to_char(lv_param8));
2227 lv_sql_stmt:= 'select count(*) from MRP_AP_RES_INSTANCE_REQS_V'||p_dblink
2228 ||' where organization_id '|| lv_in_org_str;
2229
2230 EXECUTE IMMEDIATE lv_sql_stmt into lv_param9 ;
2231 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_DEBUG_1, 'count for ap res inst req without lrn = '||to_char(lv_param8));
2232
2233 /* ds change end */
2234
2235 lv_param1 := lv_param1 + lv_param4 + lv_param7;
2236 lv_param2 := lv_param2 + lv_param5 + lv_param8;
2237 lv_param3 := lv_param3 + lv_param6 + lv_param9;
2238
2239 lv_bom10 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
2240 IF lv_bom10 = MSC_UTIL.SYS_TGT THEN
2241 lv_status_decided_wip := MSC_UTIL.SYS_YES;
2242 END IF;
2243 END IF;
2244
2245 END IF; -- wip_flag
2246
2247 --lv_bom9
2248 IF (prec.bom_flag = MSC_UTIL.SYS_YES OR prec.wip_flag = MSC_UTIL.SYS_YES) THEN
2249 IF lv_status_decided_bom = MSC_UTIL.SYS_NO AND lv_status_decided_wip = MSC_UTIL.SYS_NO THEN
2250 lv_sql_stmt:= 'select count(*) '
2251 ||' from MRP_AP_LINE_RESOURCES_V'||p_dblink||' x '
2252 ||' where x.RN1 > :lv_bom_lrn '
2253 ||' and x.organization_id '|| lv_in_org_str;
2254
2255
2256 EXECUTE IMMEDIATE lv_sql_stmt INTO lv_param1 USING lv_bom_lrn;
2257
2258 lv_sql_stmt:= 'select count(*) from MRP_AP_LINE_RESOURCES_V'||p_dblink
2259 ||' where organization_id '|| lv_in_org_str;
2260
2261 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
2262
2263 lv_param2 :=0;
2264
2265 lv_bom9 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
2266 IF lv_bom9 = MSC_UTIL.SYS_TGT THEN
2267 lv_status_decided_bom := MSC_UTIL.SYS_YES;
2268 END IF;
2269 END IF;
2270 END IF;
2271
2272 --p_supcap_sn_flag
2273
2274 IF prec.app_supp_cap_flag = MSC_UTIL.SYS_YES or prec.app_supp_cap_flag =MSC_UTIL.ASL_YES_RETAIN_CP THEN
2275 BEGIN
2276 SELECT MSC_UTIL.SYS_YES
2277 INTO lv_status_decided_app_supp_cap
2278 FROM fnd_lookup_values
2279 WHERE lookup_type = 'MSC_COLL_SNAPSHOTS' AND
2280 enabled_flag = 'Y' AND
2281 view_application_id = 700 AND
2282 language = userenv('lang') AND
2283 attribute2 IN
2284 ('PO_SI_CAPA_SN', 'MTL_SYS_ITEMS_SN') AND
2285 attribute13 = 'COMPLETE' AND
2286 rownum = 1;
2287 EXCEPTION
2288 WHEN NO_DATA_FOUND THEN
2289 lv_status_decided_app_supp_cap := MSC_UTIL.SYS_NO;
2290 END;
2291 --lv_supcap1
2292 IF lv_status_decided_app_supp_cap = MSC_UTIL.SYS_NO THEN
2293 lv_sql_stmt:= 'select count(*) from MRP_AD_SUPPLIER_CAPACITIES_V'||p_dblink
2294 ||' where RN > :lv_sup_cap_lrn '
2295 ||' and organization_id '|| lv_in_org_str;
2296
2297
2298 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_sup_cap_lrn;
2299
2300 lv_sql_stmt:= 'select count(*) '
2301 ||' from MRP_AP_SUPPLIER_CAPACITIES_V'||p_dblink||' x '
2302 ||' where ( x.RN1 > :lv_sup_cap_lrn '
2303 ||' OR x.RN2 > :lv_sup_cap_lrn ) '
2304 ||' and x.organization_id '|| lv_in_org_str;
2305
2306
2307 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_sup_cap_lrn,
2308 lv_sup_cap_lrn;
2309
2310
2311 lv_sql_stmt:= 'select count(*) from MRP_AP_SUPPLIER_CAPACITIES_V'||p_dblink
2312 ||' where organization_id '|| lv_in_org_str;
2313
2314 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
2315
2316 lv_supcap1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
2317 ELSE
2318 lv_supcap1 := MSC_UTIL.SYS_TGT;
2319 END IF;
2320
2321 END IF;
2322
2323 --p_po_sn_flag
2324
2325 IF prec.po_flag = MSC_UTIL.SYS_YES THEN
2326 BEGIN
2327 SELECT MSC_UTIL.SYS_YES
2328 INTO lv_status_decided_po
2329 FROM fnd_lookup_values
2330 WHERE lookup_type = 'MSC_COLL_SNAPSHOTS' AND
2331 enabled_flag = 'Y' AND
2332 view_application_id = 700 AND
2333 language = userenv('lang') AND
2334 attribute2 IN
2335 ('MTL_SUPPLY_SN', 'MTL_SYS_ITEMS_SN') AND
2336 attribute13 = 'COMPLETE' AND
2337 rownum = 1;
2338 EXCEPTION
2339 WHEN NO_DATA_FOUND THEN
2340 lv_status_decided_po := MSC_UTIL.SYS_NO;
2341 END;
2342 --lv_po1
2343 IF lv_status_decided_po = MSC_UTIL.SYS_NO THEN
2344 lv_sql_stmt:= 'select count(*) from MRP_AD_PO_SUPPLIES_V'||p_dblink
2345 ||' where RN > :lv_po_lrn'
2346 ||' and organization_id '|| lv_in_org_str;
2347
2348
2349 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_po_lrn;
2350
2351
2352 lv_sql_stmt:= 'select count(*) '
2353 ||' from MRP_AP_PO_PO_SUPPLY_V'||p_dblink||' x '
2354 ||' where ( x.RN2 > :lv_po_lrn '
2355 ||' OR x.RN3 > :lv_po_lrn ) '
2356 ||' and x.organization_id '|| lv_in_org_str;
2357
2358
2359 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_po_lrn,
2360 lv_po_lrn;
2361
2362 lv_sql_stmt:= 'select count(*) '
2363 ||' from MRP_AP_PO_SHIP_SUPPLY_V'||p_dblink||' x '
2364 ||' where ( x.RN2 > :lv_po_lrn '
2365 ||' OR x.RN3 > :lv_po_lrn )'
2366 ||' and x.organization_id '|| lv_in_org_str;
2367
2368
2369 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3 USING lv_po_lrn,
2370 lv_po_lrn;
2371
2372 lv_sql_stmt:= 'select count(*) '
2373 ||' from MRP_AP_PO_REQ_SUPPLY_V'||p_dblink||' x '
2374 ||' where ( x.RN2 > :lv_po_lrn '
2375 ||' OR x.RN3 > :lv_po_lrn )'
2376 ||' and x.organization_id '|| lv_in_org_str;
2377
2378
2379 EXECUTE IMMEDIATE lv_sql_stmt into lv_param4 USING lv_po_lrn,
2380 lv_po_lrn;
2381
2382 lv_sql_stmt:= 'select count(*) '
2383 ||' from MRP_AP_PO_SHIP_RCV_SUPPLY_V'||p_dblink||' x '
2384 ||' where ( x.RN2 > :lv_po_lrn '
2385 ||' OR x.RN3 > :lv_po_lrn ) '
2386 ||' and x.organization_id '|| lv_in_org_str;
2387
2388
2389 EXECUTE IMMEDIATE lv_sql_stmt into lv_param5 USING lv_po_lrn,
2390 lv_po_lrn;
2391
2392 lv_sql_stmt:= 'select count(*) '
2393 ||' from MRP_AP_PO_RCV_SUPPLY_V'||p_dblink||' x '
2394 ||' where ( x.RN2 > :lv_po_lrn '
2395 ||' OR x.RN3 > :lv_po_lrn ) '
2396 ||' and x.organization_id '|| lv_in_org_str;
2397
2398
2399 EXECUTE IMMEDIATE lv_sql_stmt into lv_param6 USING lv_po_lrn,
2400 lv_po_lrn;
2401
2402
2403 lv_sql_stmt:= 'select count(*) '
2404 ||' from MRP_AP_INTRANSIT_SUPPLIES_V'||p_dblink||' x '
2405 ||' where x.RN2 > :lv_po_lrn '
2406 ||' and x.organization_id '|| lv_in_org_str;
2407
2408
2409 EXECUTE IMMEDIATE lv_sql_stmt into lv_param7 USING lv_po_lrn;
2410
2411 lv_param2:=lv_param2 + lv_param3 + lv_param4 + lv_param5 + lv_param6 + lv_param7;
2412
2413 lv_sql_stmt:= 'select count(*) from MRP_AP_PO_PO_SUPPLY_V'||p_dblink
2414 ||' where organization_id '|| lv_in_org_str;
2415
2416 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
2417
2418 lv_sql_stmt:= 'select count(*) from MRP_AP_PO_SHIP_SUPPLY_V'||p_dblink
2419 ||' where organization_id '|| lv_in_org_str;
2420
2421 EXECUTE IMMEDIATE lv_sql_stmt into lv_param4;
2422
2423 lv_sql_stmt:= 'select count(*) from MRP_AP_PO_REQ_SUPPLY_V'||p_dblink
2424 ||' where organization_id '|| lv_in_org_str;
2425
2426 EXECUTE IMMEDIATE lv_sql_stmt into lv_param5;
2427
2428 lv_sql_stmt:= 'select count(*) from MRP_AP_PO_SHIP_RCV_SUPPLY_V'||p_dblink
2429 ||' where organization_id '|| lv_in_org_str;
2430
2431 EXECUTE IMMEDIATE lv_sql_stmt into lv_param6;
2432
2433 lv_sql_stmt:= 'select count(*) from MRP_AP_PO_RCV_SUPPLY_V'||p_dblink
2434 ||' where organization_id '|| lv_in_org_str;
2435
2436 EXECUTE IMMEDIATE lv_sql_stmt into lv_param7;
2437
2438 lv_sql_stmt:= 'select count(*) from MRP_AP_INTRANSIT_SUPPLIES_V'||p_dblink
2439 ||' where organization_id '|| lv_in_org_str;
2440
2441 EXECUTE IMMEDIATE lv_sql_stmt into lv_param8;
2442
2443 lv_param3:=lv_param3 + lv_param4 + lv_param5 + lv_param6 + lv_param7 + lv_param8;
2444
2445 lv_po1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
2446 ELSE
2447 lv_po1 := MSC_UTIL.SYS_TGT;
2448 END IF;
2449 END IF;
2450
2451 --p_mds_sn_flag
2452
2453 IF prec.mds_flag = MSC_UTIL.SYS_YES THEN
2454
2455 BEGIN
2456 SELECT MSC_UTIL.SYS_YES
2457 INTO lv_status_decided_mds
2458 FROM fnd_lookup_values
2459 WHERE lookup_type = 'MSC_COLL_SNAPSHOTS' AND
2460 enabled_flag = 'Y' AND
2461 view_application_id = 700 AND
2462 language = userenv('lang') AND
2463 attribute2 IN
2464 ('MRP_SCHD_DATES_SN', 'MTL_SYS_ITEMS_SN') AND
2465 attribute13 = 'COMPLETE' AND
2466 rownum = 1;
2467 EXCEPTION
2468 WHEN NO_DATA_FOUND THEN
2469 lv_status_decided_mds := MSC_UTIL.SYS_NO;
2470 END;
2471
2472 IF lv_status_decided_mds = MSC_UTIL.SYS_NO THEN
2473 --lv_mds1
2474 lv_sql_stmt:= 'select count(*) from MRP_AD_MDS_DEMANDS_V'||p_dblink
2475 ||' where RN > :lv_mds_lrn '
2476 ||' and organization_id '|| lv_in_org_str;
2477
2478
2479 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_sup_cap_lrn;
2480
2481 lv_sql_stmt:= 'select count(*) '
2482 ||' from MRP_AP_MDS_DEMANDS_V'||p_dblink||' x '
2483 ||' where ( x.RN2 > :lv_mds_lrn '
2484 ||' OR x.RN3 > :lv_mds_lrn ) '
2485 ||' and x.organization_id '|| lv_in_org_str;
2486
2487
2488 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_mds_lrn,
2489 lv_mds_lrn;
2490
2491
2492 lv_sql_stmt:= 'select count(*) from MRP_AP_MDS_DEMANDS_V'||p_dblink
2493 ||' where organization_id '|| lv_in_org_str;
2494
2495 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
2496
2497 lv_mds1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
2498 ELSE
2499 lv_mds1 := MSC_UTIL.SYS_TGT;
2500 END IF;
2501
2502 END IF;
2503
2504 --p_mps_sn_flag
2505
2506 IF prec.mps_flag = MSC_UTIL.SYS_YES THEN
2507 BEGIN
2508 SELECT MSC_UTIL.SYS_YES
2509 INTO lv_status_decided_mps
2510 FROM fnd_lookup_values
2511 WHERE lookup_type = 'MSC_COLL_SNAPSHOTS' AND
2512 enabled_flag = 'Y' AND
2513 view_application_id = 700 AND
2514 language = userenv('lang') AND
2515 attribute2 IN
2516 ('MRP_SCHD_DATES_SN', 'MTL_SYS_ITEMS_SN') AND
2517 attribute13 = 'COMPLETE' AND
2518 rownum = 1;
2519 EXCEPTION
2520 WHEN NO_DATA_FOUND THEN
2521 lv_status_decided_mps := MSC_UTIL.SYS_NO;
2522 END;
2523
2524 IF lv_status_decided_mps = MSC_UTIL.SYS_NO THEN
2525
2526 --lv_mps1
2527 lv_sql_stmt:= 'select count(*) from MRP_AD_MPS_SUPPLIES_V'||p_dblink
2528 ||' where RN > :lv_mps_lrn '
2529 ||' and organization_id '|| lv_in_org_str;
2530
2531
2532 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_sup_cap_lrn;
2533
2534 lv_sql_stmt:= 'select count(*) '
2535 ||' from MRP_AP_MPS_SUPPLIES_V'||p_dblink||' x '
2536 ||' where ( x.RN2 > :lv_mps_lrn '
2537 ||' OR x.RN3 > :lv_mps_lrn ) '
2538 ||' and x.organization_id '|| lv_in_org_str;
2539
2540
2541 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_mps_lrn,
2542 lv_mps_lrn;
2543
2544
2545 lv_sql_stmt:= 'select count(*) from MRP_AP_MPS_SUPPLIES_V'||p_dblink
2546 ||' where organization_id '|| lv_in_org_str;
2547
2548 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
2549 lv_mps1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
2550 ELSE
2551 lv_mps1 := MSC_UTIL.SYS_TGT;
2552 END IF;
2553
2554 END IF;
2555
2556 --p_trip_sn_flag
2557
2558 IF prec.trip_flag = MSC_UTIL.SYS_YES THEN
2559 BEGIN
2560 SELECT MSC_UTIL.SYS_YES
2561 INTO lv_status_decided_trip
2562 FROM fnd_lookup_values
2563 WHERE lookup_type = 'MSC_COLL_SNAPSHOTS' AND
2564 enabled_flag = 'Y' AND
2565 view_application_id = 700 AND
2566 language = userenv('lang') AND
2567 attribute2 IN
2568 ('WSH_TRIP_SN', 'WSH_TRIP_STOP_SN') AND
2569 attribute13 = 'COMPLETE' AND
2570 rownum = 1;
2571 EXCEPTION
2572 WHEN NO_DATA_FOUND THEN
2573 lv_status_decided_trip := MSC_UTIL.SYS_NO;
2574 END;
2575
2576
2577 --lv_trip1
2578 IF lv_status_decided_trip = MSC_UTIL.SYS_NO THEN
2579 lv_sql_stmt:= 'select count(*) from MRP_AD_TRIPS_V'||p_dblink
2580 ||' where RN > :lv_trip_lrn ';
2581
2582
2583 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_trip_lrn;
2584
2585 lv_sql_stmt:= 'select count(*) '
2586 ||' from MRP_AP_TRIPS_V'||p_dblink||' x '
2587 ||' where x.RN > :lv_trip_lrn ';
2588
2589
2590 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_trip_lrn;
2591
2592
2593 lv_sql_stmt:= 'select count(*) from MRP_AP_TRIPS_V'||p_dblink;
2594
2595
2596 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
2597
2598 lv_trip1 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
2599 IF lv_trip1 = MSC_UTIL.SYS_TGT THEN
2600 lv_status_decided_trip := MSC_UTIL.SYS_YES;
2601 END IF;
2602
2603 ELSE
2604 lv_trip1 := MSC_UTIL.SYS_TGT;
2605 END IF;
2606
2607 IF lv_status_decided_trip = MSC_UTIL.SYS_NO THEN
2608 --lv_trip2
2609 lv_sql_stmt:= 'select count(*) from MRP_AD_TRIP_STOPS_V'||p_dblink
2610 ||' where RN > :lv_trip_lrn ';
2611
2612
2613 EXECUTE IMMEDIATE lv_sql_stmt into lv_param1 USING lv_trip_lrn;
2614
2615 lv_sql_stmt:= 'select count(*) '
2616 ||' from MRP_AP_TRIP_STOPS_V'||p_dblink||' x '
2617 ||' where x.RN > :lv_trip_lrn ';
2618
2619
2620 EXECUTE IMMEDIATE lv_sql_stmt into lv_param2 USING lv_trip_lrn;
2621
2622
2623 lv_sql_stmt:= 'select count(*) from MRP_AP_TRIP_STOPS_V'||p_dblink;
2624
2625
2626 EXECUTE IMMEDIATE lv_sql_stmt into lv_param3;
2627
2628 lv_trip2 := get_refresh_type(lv_param1,lv_param2,lv_param3,p_coll_thresh);
2629 END IF;
2630
2631 END IF;
2632 /*----------------------------------------------- */
2633
2634
2635 if ((lv_bom1 = MSC_UTIL.SYS_TGT) or
2636 (lv_bom2 = MSC_UTIL.SYS_TGT) or
2637 (lv_bom3 = MSC_UTIL.SYS_TGT) or
2638 (lv_bom4 = MSC_UTIL.SYS_TGT) or
2639 -- (lv_bom5 = MSC_UTIL.SYS_TGT) or
2640 (lv_bom6 = MSC_UTIL.SYS_TGT) or
2641 (lv_bom7 = MSC_UTIL.SYS_TGT) or
2642 (lv_bom8 = MSC_UTIL.SYS_TGT) or
2643 (lv_bom9 = MSC_UTIL.SYS_TGT) or
2644 -- lv_bom10 should only be used when wip is enabled.Its views are used in load_wip (mscclaab) only when wip is enabled.
2645 -- (lv_bom10 = MSC_UTIL.SYS_TGT) or
2646 (lv_bom11 = MSC_UTIL.SYS_TGT) or
2647 (lv_bom12 = MSC_UTIL.SYS_TGT)) then
2648 p_bom_sn_flag := MSC_UTIL.SYS_TGT;
2649 elsif ((lv_bom1 = MSC_UTIL.SYS_NO) and
2650 (lv_bom2 = MSC_UTIL.SYS_NO) and
2651 (lv_bom3 = MSC_UTIL.SYS_NO) and
2652 (lv_bom4 = MSC_UTIL.SYS_NO) and
2653 -- (lv_bom5 = MSC_UTIL.SYS_NO) and
2654 (lv_bom6 = MSC_UTIL.SYS_NO) and
2655 (lv_bom7 = MSC_UTIL.SYS_NO) and
2656 (lv_bom8 = MSC_UTIL.SYS_NO) and
2657 (lv_bom9 = MSC_UTIL.SYS_NO) and
2658 -- (lv_bom10 = MSC_UTIL.SYS_NO) and
2659 (lv_bom11 = MSC_UTIL.SYS_NO) and
2660 (lv_bom12 = MSC_UTIL.SYS_NO)) then
2661 p_bom_sn_flag := MSC_UTIL.SYS_NO;
2662 else
2663 p_bom_sn_flag := MSC_UTIL.SYS_INCR;
2664 end if;
2665
2666
2667 p_bor_sn_flag :=lv_bor1;
2668
2669
2670 if ((lv_item1 = MSC_UTIL.SYS_TGT) or
2671 (lv_item2 = MSC_UTIL.SYS_TGT)) then
2672 p_item_sn_flag := MSC_UTIL.SYS_TGT;
2673 elsif ((lv_item1 = MSC_UTIL.SYS_NO) and
2674 (lv_item2 = MSC_UTIL.SYS_NO)) then
2675 p_item_sn_flag := MSC_UTIL.SYS_NO;
2676 else
2677 p_item_sn_flag := MSC_UTIL.SYS_INCR;
2678 end if;
2679
2680
2681 p_oh_sn_flag := lv_oh1;
2682
2683 p_usup_sn_flag := lv_usup1;
2684
2685 p_udmd_sn_flag := lv_udmd1;
2686
2687 p_so_sn_flag := lv_so1;
2688
2689 if ((lv_fcst1 = MSC_UTIL.SYS_TGT) or
2690 (lv_fcst2 = MSC_UTIL.SYS_TGT)) then
2691 p_fcst_sn_flag := MSC_UTIL.SYS_TGT;
2692 elsif ((lv_fcst1 = MSC_UTIL.SYS_NO) and
2693 (lv_fcst2 = MSC_UTIL.SYS_NO)) then
2694 p_fcst_sn_flag := MSC_UTIL.SYS_NO;
2695 else
2696 p_fcst_sn_flag := MSC_UTIL.SYS_INCR;
2697 end if;
2698
2699
2700 if ((lv_wip1 = MSC_UTIL.SYS_TGT) or
2701 (lv_wip2 = MSC_UTIL.SYS_TGT) or
2702 (lv_wip3 = MSC_UTIL.SYS_TGT) or
2703 (lv_bom9 = MSC_UTIL.SYS_TGT) or
2704 (lv_bom10 = MSC_UTIL.SYS_TGT)) then
2705 p_wip_sn_flag := MSC_UTIL.SYS_TGT;
2706 elsif ((lv_wip1 = MSC_UTIL.SYS_NO) and
2707 (lv_wip2 = MSC_UTIL.SYS_NO) and
2708 (lv_wip3 = MSC_UTIL.SYS_NO) and
2709 (lv_bom9 = MSC_UTIL.SYS_NO) and
2710 (lv_bom10 = MSC_UTIL.SYS_NO)) then
2711 p_wip_sn_flag := MSC_UTIL.SYS_NO;
2712 else
2713 p_wip_sn_flag := MSC_UTIL.SYS_INCR;
2714 end if;
2715
2716 p_supcap_sn_flag := lv_supcap1;
2717
2718 p_po_sn_flag := lv_po1;
2719
2720 p_mds_sn_flag := lv_mds1;
2721
2722 p_mps_sn_flag := lv_mps1;
2723
2724 if ((lv_trip1 = MSC_UTIL.SYS_TGT) or
2725 (lv_trip2 = MSC_UTIL.SYS_TGT)) then
2726 p_trip_sn_flag := MSC_UTIL.SYS_TGT;
2727 elsif ((lv_trip1 = MSC_UTIL.SYS_NO) and
2728 (lv_trip2 = MSC_UTIL.SYS_NO)) then
2729 p_trip_sn_flag := MSC_UTIL.SYS_NO;
2730 else
2731 p_trip_sn_flag := MSC_UTIL.SYS_INCR;
2732 end if;
2733
2734 /*
2735 if ((p_last_tgt_cont_coll_time is null) or
2736 (p_last_tgt_cont_coll_time + (p_coll_freq/24) <= sysdate)) then
2737 p_nosnap_flag := MSC_UTIL.SYS_YES;
2738 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' p_nosnap_flag is YES ');
2739 else
2740 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' p_nosnap_flag is NO ');
2741 p_nosnap_flag := MSC_UTIL.SYS_NO;
2742 end if;
2743
2744 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, ' p_nosnap_flag : '||p_nosnap_flag);
2745 */
2746 -- For Future Use only. Time Frequency for Non Snapshot Entities
2747
2748 p_nosnap_flag := -1;
2749
2750 /* IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
2751 p_supcap_sn_flag := lv_po_supcap_snrt;
2752
2753 ELSE -- For 110/107 source , supplier capacity is associated with other setup entities -nosnap_flag
2754 p_supcap_sn_flag := p_nosnap_flag;
2755 END IF;
2756 */
2757
2758 END IF ;
2759 END init_entity_refresh_type;
2760
2761 FUNCTION set_cont_refresh_type (p_instance_id in NUMBER,
2762 p_task_num in NUMBER,
2763 prec in MSC_UTIL.CollParamREC,
2764 p_lrnn in number,
2765 p_cont_lrnn out NOCOPY number)
2766
2767 RETURN BOOLEAN AS
2768
2769 BEGIN
2770
2771 IF p_task_num = MSC_CL_PULL.TASK_SUPPLIER_CAPACITY THEN
2772 IF (MSC_CL_PULL.v_apps_ver >= MSC_UTIL.G_APPS115) THEN
2773 if (prec.supcap_sn_flag = MSC_UTIL.SYS_INCR) then
2774 -- do net-change for this entity
2775 p_cont_lrnn := p_lrnn;
2776 RETURN TRUE;
2777 elsif (prec.supcap_sn_flag = MSC_UTIL.SYS_TGT) then
2778 -- do targeted for this entity
2779 p_cont_lrnn := -1;
2780 RETURN TRUE;
2781 else
2782 -- do nothing
2783 RETURN FALSE;
2784 end if;
2785 ELSE --- For 110/107 source instance, supplier capacity is associated with Other setup entities
2786 IF (prec.app_supp_cap_flag = MSC_UTIL.ASL_YES OR prec.app_supp_cap_flag =MSC_UTIL.ASL_YES_RETAIN_CP) THEN
2787 -- do targeted for this entity
2788 p_cont_lrnn := -1;
2789 RETURN TRUE;
2790 else
2791 -- do nothing
2792 RETURN FALSE;
2793 end if;
2794 END IF;
2795 END IF;
2796
2797 IF ((p_task_num = MSC_CL_PULL.TASK_BOM) or
2798 (p_task_num = MSC_CL_PULL.TASK_ROUTING) or
2799 (p_task_num = MSC_CL_PULL.TASK_OPER_NETWORKS) or
2800 (p_task_num = MSC_CL_PULL.TASK_ROUTING_OPERATIONS) or
2801 (p_task_num = MSC_CL_PULL.TASK_OPERATION_RES_SEQS) or
2802 (p_task_num = MSC_CL_PULL.TASK_OPERATION_RESOURCES) or
2803 (p_task_num = MSC_CL_PULL.TASK_OPERATION_COMPONENTS) or
2804 (p_task_num = MSC_CL_PULL.TASK_PROCESS_EFFECTIVITY) ) THEN
2805 -- LOAD_BOM,
2806 -- LOAD_ROUTING,
2807 -- LOAD_OPER_NETWORKS
2808 -- LOAD_ROUTING_OPERATIONS
2809 -- LOAD_OPERATION_RES_SEQS
2810 -- LOAD_OPERATION_RESOURCES
2811 -- LOAD_OPERATION_COMPONENTS
2812 -- LOAD_PROCESS_EFFECTIVITY
2813 -- extract effectivities are performed
2814 if (prec.bom_sn_flag = MSC_UTIL.SYS_INCR) then
2815 -- do net-change for this entity
2816 p_cont_lrnn := p_lrnn;
2817 RETURN TRUE;
2818 elsif (prec.bom_sn_flag = MSC_UTIL.SYS_TGT) then
2819 -- do targeted for this entity
2820 p_cont_lrnn := -1;
2821 RETURN TRUE;
2822 else
2823 -- do nothing
2824 RETURN FALSE;
2825 end if;
2826 END IF;
2827
2828
2829 IF ( p_task_num = MSC_CL_PULL.TASK_BOR ) THEN
2830 if (prec.bor_sn_flag = MSC_UTIL.SYS_INCR) then
2831 -- do net-change for this entity
2832 p_cont_lrnn := p_lrnn;
2833 RETURN TRUE;
2834 elsif (prec.bor_sn_flag = MSC_UTIL.SYS_TGT) then
2835 -- do targeted for this entity
2836 p_cont_lrnn := -1;
2837 RETURN TRUE;
2838 else
2839 -- do nothing
2840 RETURN FALSE;
2841 end if;
2842 END IF;
2843
2844 IF (p_task_num = MSC_CL_PULL.TASK_LOAD_FORECAST) THEN
2845 -- both of the ones below will get executed
2846 -- LOAD_FORECASTS
2847 -- LOAD_ITEM_FORECASTS
2848 if (prec.fcst_sn_flag = MSC_UTIL.SYS_INCR) then
2849 -- do net-change for this entity
2850 p_cont_lrnn := p_lrnn;
2851 RETURN TRUE;
2852 elsif (prec.fcst_sn_flag = MSC_UTIL.SYS_TGT) then
2853 -- do targeted for this entity
2854 p_cont_lrnn := -1;
2855 RETURN TRUE;
2856 else
2857 -- do nothing
2858 RETURN FALSE;
2859 end if;
2860 END IF;
2861
2862
2863 IF ((p_task_num = MSC_CL_PULL.TASK_CATEGORY) or
2864 (p_task_num = MSC_CL_PULL.TASK_ITEM1) or
2865 (p_task_num = MSC_CL_PULL.TASK_ITEM2) or
2866 (p_task_num = MSC_CL_PULL.TASK_ITEM3) ) THEN
2867
2868 if (prec.item_sn_flag = MSC_UTIL.SYS_INCR) then
2869 -- do net-change for this entity
2870 p_cont_lrnn := p_lrnn;
2871 RETURN TRUE;
2872 elsif (prec.item_sn_flag = MSC_UTIL.SYS_TGT) then
2873 -- do targeted for this entity
2874 p_cont_lrnn := -1;
2875 RETURN TRUE;
2876 else
2877 -- do nothing
2878 RETURN FALSE;
2879 end if;
2880 END IF;
2881
2882
2883 IF (p_task_num = MSC_CL_PULL.TASK_MDS_DEMAND) THEN
2884 if (prec.mds_sn_flag = MSC_UTIL.SYS_INCR) then
2885 -- do net-change for this entity
2886 p_cont_lrnn := p_lrnn;
2887 RETURN TRUE;
2888 elsif (prec.mds_sn_flag = MSC_UTIL.SYS_TGT) then
2889 -- do targeted for this entity
2890 p_cont_lrnn := -1;
2891 RETURN TRUE;
2892 else
2893 -- do nothing
2894 RETURN FALSE;
2895 end if;
2896 END IF;
2897
2898
2899 IF (p_task_num = MSC_CL_PULL.TASK_MPS_SUPPLY) THEN
2900 if (prec.mps_sn_flag = MSC_UTIL.SYS_INCR) then
2901 -- do net-change for this entity
2902 p_cont_lrnn := p_lrnn;
2903 RETURN TRUE;
2904 elsif (prec.mps_sn_flag = MSC_UTIL.SYS_TGT) then
2905 -- do targeted for this entity
2906 p_cont_lrnn := -1;
2907 RETURN TRUE;
2908 else
2909 -- do nothing
2910 RETURN FALSE;
2911 end if;
2912 END IF;
2913
2914 IF (p_task_num = MSC_CL_PULL.TASK_SCHEDULE) THEN
2915 if (prec.mds_sn_flag = MSC_UTIL.SYS_TGT) or (prec.mps_sn_flag = MSC_UTIL.SYS_TGT) THEN
2916 -- do targeted for this entity
2917 p_cont_lrnn := -1;
2918 RETURN TRUE;
2919 elsif (prec.mds_sn_flag = MSC_UTIL.SYS_INCR) OR (prec.mps_sn_flag = MSC_UTIL.SYS_INCR) THEN
2920 -- do net-change for this entity
2921 p_cont_lrnn := p_lrnn;
2922 RETURN TRUE;
2923 else
2924 -- do nothing
2925 RETURN FALSE;
2926 end if;
2927 END IF;
2928
2929 IF p_task_num = MSC_CL_PULL.TASK_OH_SUPPLY THEN
2930 if (prec.oh_sn_flag = MSC_UTIL.SYS_INCR) then
2931 -- do net-change for this entity
2932 p_cont_lrnn := p_lrnn;
2933 RETURN TRUE;
2934 elsif (prec.oh_sn_flag = MSC_UTIL.SYS_TGT) then
2935 -- do targeted for this entity
2936 p_cont_lrnn := -1;
2937 RETURN TRUE;
2938 else
2939 -- do nothing
2940 RETURN FALSE;
2941 end if;
2942 END IF;
2943
2944
2945 -- IF p_task_num = TASK_PO_SUPPLY THEN
2946 IF ((p_task_num = MSC_CL_PULL.TASK_PO_SUPPLY) or
2947 (p_task_num = MSC_CL_PULL.TASK_PO_PO_SUPPLY) or
2948 (p_task_num = MSC_CL_PULL.TASK_PO_REQ_SUPPLY)) THEN
2949
2950 if (prec.po_sn_flag = MSC_UTIL.SYS_INCR) then
2951 -- do net-change for this entity
2952 p_cont_lrnn := p_lrnn;
2953 RETURN TRUE;
2954 elsif (prec.po_sn_flag = MSC_UTIL.SYS_TGT) then
2955 -- do targeted for this entity
2956 p_cont_lrnn := -1;
2957 RETURN TRUE;
2958 else
2959 -- do nothing
2960 RETURN FALSE;
2961 end if;
2962 END IF;
2963
2964
2965 IF p_task_num in (MSC_CL_PULL.TASK_SALES_ORDER1,MSC_CL_PULL.TASK_SALES_ORDER2,MSC_CL_PULL.TASK_SALES_ORDER3,MSC_CL_PULL.TASK_AHL) THEN
2966 if (prec.so_sn_flag = MSC_UTIL.SYS_INCR) then
2967 -- do net-change for this entity
2968 p_cont_lrnn := p_lrnn;
2969 RETURN TRUE;
2970 elsif (prec.so_sn_flag = MSC_UTIL.SYS_TGT) then
2971 -- do targeted for this entity
2972 p_cont_lrnn := -1;
2973 RETURN TRUE;
2974 else
2975 -- do nothing
2976 RETURN FALSE;
2977 end if;
2978 END IF;
2979
2980
2981 IF ((p_task_num = MSC_CL_PULL.TASK_USER_SUPPLY) OR (p_task_num = MSC_CL_PULL.TASK_USER_DEMAND)) THEN
2982 if (prec.usup_sn_flag = MSC_UTIL.SYS_INCR) then
2983 -- do net-change for this entity
2984 p_cont_lrnn := p_lrnn;
2985 RETURN TRUE;
2986 elsif (prec.usup_sn_flag = MSC_UTIL.SYS_TGT) then
2987 -- do targeted for this entity
2988 p_cont_lrnn := -1;
2989 RETURN TRUE;
2990 else
2991 -- do nothing
2992 RETURN FALSE;
2993 end if;
2994 END IF;
2995
2996
2997 -- FOR LOAD_WIP_SUPPLY
2998 -- FOR LOAD_WIP_DEMAND
2999 IF ( (p_task_num = MSC_CL_PULL.TASK_WIP_SUPPLY) OR
3000 (p_task_num = MSC_CL_PULL.TASK_WIP_DEMAND) ) THEN
3001 if (prec.wip_sn_flag = MSC_UTIL.SYS_INCR) then
3002 -- do net-change for this entity
3003 p_cont_lrnn := p_lrnn;
3004 RETURN TRUE;
3005 elsif (prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3006 -- do targeted for this entity
3007 p_cont_lrnn := -1;
3008 RETURN TRUE;
3009 else
3010 -- do nothing
3011 RETURN FALSE;
3012 end if;
3013 END IF;
3014
3015 IF (p_task_num = MSC_CL_PULL.TASK_RESOURCE) THEN
3016 if ( (prec.bom_sn_flag = MSC_UTIL.SYS_TGT) or (prec.wip_sn_flag = MSC_UTIL.SYS_TGT) ) then
3017 -- do targeted for this entity
3018 p_cont_lrnn := -1;
3019 RETURN TRUE;
3020 elsif ( (prec.bom_sn_flag = MSC_UTIL.SYS_INCR) or (prec.wip_sn_flag = MSC_UTIL.SYS_INCR) ) then
3021 -- do net-change for this entity
3022 p_cont_lrnn := p_lrnn;
3023 RETURN TRUE;
3024 else
3025 -- do nothing
3026 RETURN FALSE;
3027 end if;
3028 END IF;
3029
3030 /* ds change start */
3031 IF (p_task_num = MSC_CL_PULL.TASK_RESOURCE_INSTANCE) THEN
3032 if ( (prec.bom_sn_flag = MSC_UTIL.SYS_TGT) or (prec.wip_sn_flag = MSC_UTIL.SYS_TGT) ) then
3033 -- do targeted for this entity
3034 p_cont_lrnn := -1;
3035 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'TASK_RESOURCE_INSTANCE is targetted ');
3036 RETURN TRUE;
3037 elsif ( (prec.bom_sn_flag = MSC_UTIL.SYS_INCR) or (prec.wip_sn_flag = MSC_UTIL.SYS_INCR) ) then
3038 -- do net-change for this entity
3039 p_cont_lrnn := p_lrnn;
3040 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'TASK_RESOURCE_INSTANCE is incremental ');
3041 RETURN TRUE;
3042 else
3043 -- do nothing
3044 RETURN FALSE;
3045 end if;
3046 END IF;
3047
3048 IF (p_task_num = MSC_CL_PULL.TASK_RESOURCE_SETUP) THEN
3049 IF (prec.bom_sn_flag = MSC_UTIL.SYS_TGT) THEN
3050 p_cont_lrnn := -1;
3051 MSC_UTIL.LOG_MSG(MSC_UTIL.G_D_STATUS, 'TASK_RESOURCE_SETUP is targetted ');
3052 RETURN TRUE;
3053 ELSE
3054 RETURN FALSE;
3055 END IF;
3056 END IF;
3057
3058 /* ds change end */
3059
3060 IF (p_task_num = MSC_CL_PULL.TASK_ATP_RULES) THEN
3061 IF (prec.atp_rules_flag = MSC_UTIL.SYS_YES) THEN
3062 p_cont_lrnn := -1;
3063 RETURN TRUE;
3064 ELSE
3065 RETURN FALSE;
3066 END IF;
3067 END IF;
3068
3069 IF ( p_task_num = MSC_CL_PULL.TASK_CALENDAR_DATE ) THEN
3070 IF prec.calendar_flag = MSC_UTIL.SYS_YES THEN
3071 p_cont_lrnn := -1;
3072 RETURN TRUE;
3073 else
3074 RETURN FALSE;
3075 end if;
3076 END IF;
3077
3078 IF ( p_task_num = MSC_CL_PULL.TASK_DEMAND_CLASS ) THEN
3079 IF prec.demand_class_flag = MSC_UTIL.SYS_YES THEN
3080 p_cont_lrnn := -1;
3081 RETURN TRUE;
3082 else
3083 RETURN FALSE;
3084 end if;
3085 END IF;
3086
3087 IF ( p_task_num = MSC_CL_PULL.TASK_BIS ) THEN
3088 IF prec.kpi_bis_flag = MSC_UTIL.SYS_YES THEN
3089 p_cont_lrnn := -1;
3090 RETURN TRUE;
3091 else
3092 RETURN FALSE;
3093 end if;
3094 END IF;
3095
3096 IF ( p_task_num = MSC_CL_PULL.TASK_PARAMETER ) THEN
3097 IF prec.parameter_flag = MSC_UTIL.SYS_YES THEN
3098 p_cont_lrnn := -1;
3099 RETURN TRUE;
3100 else
3101 RETURN FALSE;
3102 end if;
3103 END IF;
3104
3105 IF ( p_task_num = MSC_CL_PULL.TASK_PLANNERS ) THEN
3106 IF prec.planner_flag = MSC_UTIL.SYS_YES THEN
3107 p_cont_lrnn := -1;
3108 RETURN TRUE;
3109 else
3110 RETURN FALSE;
3111 end if;
3112 END IF;
3113
3114 IF ( p_task_num = MSC_CL_PULL.TASK_PROJECT ) THEN
3115 IF prec.project_flag = MSC_UTIL.SYS_YES THEN
3116 p_cont_lrnn := -1;
3117 RETURN TRUE;
3118 else
3119 RETURN FALSE;
3120 end if;
3121 END IF;
3122
3123 IF ( p_task_num = MSC_CL_PULL.TASK_HARD_RESERVATION ) THEN
3124 IF prec.reserves_flag = MSC_UTIL.SYS_YES THEN
3125 p_cont_lrnn := -1;
3126 RETURN TRUE;
3127 else
3128 RETURN FALSE;
3129 end if;
3130 END IF;
3131
3132 IF ( p_task_num = MSC_CL_PULL.TASK_SAFETY_STOCK ) THEN
3133 IF prec.saf_stock_flag = MSC_UTIL.SYS_YES THEN
3134 p_cont_lrnn := -1;
3135 RETURN TRUE;
3136 else
3137 RETURN FALSE;
3138 end if;
3139 END IF;
3140
3141 IF ( p_task_num = MSC_CL_PULL.TASK_SOURCING ) THEN
3142 IF prec.sourcing_rule_flag = MSC_UTIL.SYS_YES THEN
3143 p_cont_lrnn := -1;
3144 RETURN TRUE;
3145 else
3146 RETURN FALSE;
3147 end if;
3148 END IF;
3149
3150 IF ( p_task_num = MSC_CL_PULL.TASK_SUB_INVENTORY ) THEN
3151 IF prec.sub_inventory_flag = MSC_UTIL.SYS_YES THEN
3152 p_cont_lrnn := -1;
3153 RETURN TRUE;
3154 else
3155 RETURN FALSE;
3156 end if;
3157 END IF;
3158
3159 IF (p_task_num = MSC_CL_PULL.TASK_TRADING_PARTNER) OR (p_task_num = MSC_CL_PULL.TASK_BUYER_CONTACT) THEN
3160 IF (prec.tp_customer_flag = MSC_UTIL.SYS_YES ) OR (prec.tp_vendor_flag = MSC_UTIL.SYS_YES) THEN
3161 p_cont_lrnn := -1;
3162 RETURN TRUE;
3163 else
3164 RETURN FALSE;
3165 end if;
3166 END IF;
3167
3168 IF ( p_task_num = MSC_CL_PULL.TASK_UNIT_NUMBER ) THEN
3169 IF prec.unit_number_flag = MSC_UTIL.SYS_YES THEN
3170 p_cont_lrnn := -1;
3171 RETURN TRUE;
3172 else
3173 RETURN FALSE;
3174 end if;
3175 END IF;
3176
3177 IF ( p_task_num = MSC_CL_PULL.TASK_UOM ) THEN
3178 IF prec.uom_flag = MSC_UTIL.SYS_YES THEN
3179 p_cont_lrnn := -1;
3180 RETURN TRUE;
3181 else
3182 RETURN FALSE;
3183 end if;
3184 END IF;
3185
3186 IF ( p_task_num = MSC_CL_PULL.TASK_ITEM_SUBSTITUTES ) THEN
3187 IF prec.item_subst_flag = MSC_UTIL.SYS_YES THEN
3188 p_cont_lrnn := -1;
3189 RETURN TRUE;
3190 else
3191 RETURN FALSE;
3192 end if;
3193 END IF;
3194
3195 IF ( p_task_num = MSC_CL_PULL.TASK_USER_COMPANY ) THEN
3196 IF (prec.user_company_flag = 2) OR (prec.user_company_flag = 3) THEN
3197 p_cont_lrnn := -1;
3198 RETURN TRUE;
3199 else
3200 RETURN FALSE;
3201 end if;
3202 END IF;
3203
3204 /* CP-AUTO */
3205 IF ( p_task_num = MSC_CL_PULL.TASK_SUPPLIER_RESPONSE ) THEN
3206
3207 if (prec.suprep_sn_flag = MSC_UTIL.SYS_INCR) then
3208 -- do net-change for this entity
3209 p_cont_lrnn := p_lrnn;
3210
3211 RETURN TRUE;
3212 elsif (prec.suprep_sn_flag = MSC_UTIL.SYS_TGT) then
3213 -- do targeted for this entity
3214 p_cont_lrnn := -1;
3215
3216 RETURN TRUE;
3217 else
3218 -- do nothing
3219 RETURN FALSE;
3220 end if;
3221 END IF;
3222
3223 IF ( p_task_num = MSC_CL_PULL.TASK_TRIP ) THEN
3224 if (prec.trip_sn_flag = MSC_UTIL.SYS_INCR) then
3225 -- do net-change for this entity
3226 p_cont_lrnn := p_lrnn;
3227 RETURN TRUE;
3228 elsif (prec.trip_sn_flag = MSC_UTIL.SYS_TGT) then
3229 -- do targeted for this entity
3230 p_cont_lrnn := -1;
3231 RETURN TRUE;
3232 else
3233 -- do nothing
3234 RETURN FALSE;
3235 end if;
3236 END IF;
3237
3238 RETURN FALSE;
3239
3240 END set_cont_refresh_type;
3241
3242 --=========================================================================
3243 FUNCTION set_cont_refresh_type_ODS(p_task_num in NUMBER,
3244 prec in MSC_CL_EXCHANGE_PARTTBL.CollParamRec,
3245 p_is_incremental_refresh out NOCOPY boolean,
3246 p_is_partial_refresh out NOCOPY boolean,
3247 p_exchange_mode out NOCOPY number)
3248 RETURN BOOLEAN AS
3249 BEGIN
3250
3251 p_is_incremental_refresh := FALSE;
3252 p_is_partial_refresh := FALSE;
3253
3254 IF (p_task_num = MSC_CL_COLLECTION.PTASK_SUPPLIER_CAPACITY) THEN
3255 if (MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag = MSC_UTIL.SYS_INCR) then
3256 -- do net-change for this entity
3257 p_is_incremental_refresh := TRUE;
3258 p_is_partial_refresh := FALSE;
3259 p_exchange_mode := MSC_UTIL.SYS_NO;
3260 RETURN TRUE;
3261 elsif (MSC_CL_COLLECTION.v_coll_prec.supcap_sn_flag = MSC_UTIL.SYS_TGT) then
3262 -- do targeted for this entity
3263 p_is_incremental_refresh := FALSE;
3264 p_is_partial_refresh := TRUE;
3265 p_exchange_mode := MSC_UTIL.SYS_YES;
3266 RETURN TRUE;
3267 else
3268 -- do nothing
3269 RETURN FALSE;
3270 end if;
3271
3272 END IF;
3273
3274 if p_task_num in (MSC_CL_COLLECTION.PTASK_BOM_COMPONENTS,MSC_CL_COLLECTION.PTASK_BOM,MSC_CL_COLLECTION.PTASK_COMPONENT_SUBSTITUTE,MSC_CL_COLLECTION.PTASK_ROUTING,
3275 MSC_CL_COLLECTION.PTASK_ROUTING_OPERATIONS,MSC_CL_COLLECTION.PTASK_OPERATION_RESOURCES,MSC_CL_COLLECTION.PTASK_RESOURCE,MSC_CL_COLLECTION.PTASK_OP_RESOURCE_SEQ,
3276 MSC_CL_COLLECTION.PTASK_PROCESS_EFFECTIVITY,MSC_CL_COLLECTION.PTASK_OPERATION_COMPONENTS,MSC_CL_COLLECTION.PTASK_OPERATION_NETWORKS,
3277 MSC_CL_COLLECTION.PTASK_RESOURCE_SETUP,MSC_CL_COLLECTION.PTASK_SETUP_TRANSITION,MSC_CL_COLLECTION.PTASK_STD_OP_RESOURCES) then /* ds change */
3278
3279 if (MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag = MSC_UTIL.SYS_INCR) then
3280 -- do net-change for this entity
3281 p_is_incremental_refresh := TRUE;
3282 p_is_partial_refresh := FALSE;
3283 p_exchange_mode := MSC_UTIL.SYS_NO;
3284 MSC_CL_COLLECTION.v_bom_refresh_type := 1;
3285 RETURN TRUE;
3286 elsif (MSC_CL_COLLECTION.v_coll_prec.bom_sn_flag = MSC_UTIL.SYS_TGT) then
3287 -- do targeted for this entity
3288 p_is_incremental_refresh := FALSE;
3289 p_is_partial_refresh := TRUE;
3290 p_exchange_mode := MSC_UTIL.SYS_YES;
3291 MSC_CL_COLLECTION.v_bom_refresh_type := 2;
3292 RETURN TRUE;
3293 else
3294 -- do nothing
3295 MSC_CL_COLLECTION.v_bom_refresh_type := 3;
3296 RETURN FALSE;
3297 end if;
3298 end if;
3299 -- grouping bor w/ the bom tasks
3300 IF (p_task_num = MSC_CL_COLLECTION.PTASK_BOR) THEN
3301 if (MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag = MSC_UTIL.SYS_INCR) then
3302 -- do net-change for this entity
3303 p_is_incremental_refresh := TRUE;
3304 p_is_partial_refresh := FALSE;
3305 p_exchange_mode := MSC_UTIL.SYS_NO;
3306 RETURN TRUE;
3307 elsif (MSC_CL_COLLECTION.v_coll_prec.bor_sn_flag = MSC_UTIL.SYS_TGT) then
3308 -- do targeted for this entity
3309 p_is_incremental_refresh := FALSE;
3310 p_is_partial_refresh := TRUE;
3311 p_exchange_mode := MSC_UTIL.SYS_YES;
3312 RETURN TRUE;
3313 else
3314 -- do nothing
3315 RETURN FALSE;
3316 end if;
3317 END IF;
3318
3319 IF (p_task_num = MSC_CL_COLLECTION.PTASK_FORECAST_DEMAND) THEN
3320 -- (p_task_num = MSC_CL_COLLECTION.PTASK_FORECASTS) /*This will be done in launch_mon_partial*/
3321 -- (p_task_num = MSC_CL_COLLECTION.PTASK_ODS_DEMAND)) /* this will be done in supply */
3322 if (MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag = MSC_UTIL.SYS_INCR) then
3323 -- do net-change for this entity
3324 p_is_incremental_refresh := TRUE;
3325 p_is_partial_refresh := FALSE;
3326 p_exchange_mode := MSC_UTIL.SYS_NO;
3327 RETURN TRUE;
3328 elsif (MSC_CL_COLLECTION.v_coll_prec.fcst_sn_flag = MSC_UTIL.SYS_TGT) then
3329 -- do targeted for this entity
3330 p_is_incremental_refresh := FALSE;
3331 p_is_partial_refresh := TRUE;
3332 p_exchange_mode := MSC_UTIL.SYS_YES;
3333 RETURN TRUE;
3334 else
3335 -- do nothing
3336 RETURN FALSE;
3337 end if;
3338 end if;
3339 IF ((p_task_num = MSC_CL_COLLECTION.PTASK_ITEM) or (p_task_num = MSC_CL_COLLECTION.PTASK_CATEGORY_ITEM)) THEN
3340 if (MSC_CL_COLLECTION.v_coll_prec.item_sn_flag = MSC_UTIL.SYS_INCR) then
3341 -- do net-change for this entity
3342 p_is_incremental_refresh := TRUE;
3343 p_is_partial_refresh := FALSE;
3344 p_exchange_mode := MSC_UTIL.SYS_NO;
3345 RETURN TRUE;
3346 elsif (MSC_CL_COLLECTION.v_coll_prec.item_sn_flag = MSC_UTIL.SYS_TGT) then
3347 -- do targeted for this entity
3348 p_is_incremental_refresh := FALSE;
3349 p_is_partial_refresh := TRUE;
3350 p_exchange_mode := MSC_UTIL.SYS_YES;
3351 RETURN TRUE;
3352 else
3353 -- do nothing
3354 RETURN FALSE;
3355 end if;
3356 END IF;
3357
3358 IF (p_task_num = MSC_CL_COLLECTION.PTASK_MDS_DEMAND) THEN
3359 ---- (p_task_num = PTASK_DESIGNATOR) or
3360 ---- Currently LOAD_DESIGNATOR called in the LAUNCH_MONITOR itself.
3361 if (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_INCR) then
3362 -- do net-change for this entity
3363 p_is_incremental_refresh := TRUE;
3364 p_is_partial_refresh := FALSE;
3365 p_exchange_mode := MSC_UTIL.SYS_NO;
3366 RETURN TRUE;
3367 elsif (MSC_CL_COLLECTION.v_coll_prec.mds_sn_flag = MSC_UTIL.SYS_TGT) then
3368 -- do targeted for this entity
3369 p_is_incremental_refresh := FALSE;
3370 p_is_partial_refresh := TRUE;
3371 p_exchange_mode := MSC_UTIL.SYS_YES;
3372 RETURN TRUE;
3373 else
3374 -- do nothing
3375 RETURN FALSE;
3376 end if;
3377 END IF;
3378 /* supply is handled differently in execute_task_partial simply return true here */
3379
3380 IF (p_task_num = MSC_CL_COLLECTION.PTASK_SUPPLY) then
3381 RETURN TRUE;
3382 end if;
3383
3384 /* If the Task is ODS_DEMAND, just retrun true since all the logic is in execute_part_task */
3385 IF ( p_task_num = MSC_CL_COLLECTION.PTASK_ODS_DEMAND ) THEN
3386 RETURN TRUE;
3387 END IF;
3388
3389 IF (p_task_num = MSC_CL_COLLECTION.PTASK_NET_RESOURCE_AVAIL) THEN
3390 RETURN TRUE;
3391 END IF;
3392
3393 IF (p_task_num = MSC_CL_COLLECTION.PTASK_TRADING_PARTNER) THEN
3394 RETURN FALSE; -- This will be done in Launch_mon_partial
3395 END IF;
3396
3397 IF (p_task_num = MSC_CL_COLLECTION.PTASK_UOM) THEN
3398 RETURN FALSE; -- This will be done in Launch_mon_partial
3399 END IF;
3400
3401 IF (p_task_num = MSC_CL_COLLECTION.PTASK_SALES_ORDER) THEN
3402 if (MSC_CL_COLLECTION.v_coll_prec.so_sn_flag = MSC_UTIL.SYS_TGT) then
3403 -- do targeted for this entity
3404 p_is_incremental_refresh := FALSE;
3405 p_is_partial_refresh := TRUE;
3406 p_exchange_mode := MSC_UTIL.SYS_YES;
3407 RETURN TRUE;
3408 else
3409 -- do net-change for this entity
3410 p_is_incremental_refresh := TRUE;
3411 p_is_partial_refresh := FALSE;
3412 p_exchange_mode := MSC_UTIL.SYS_NO;
3413 RETURN TRUE;
3414 end if;
3415 END IF;
3416
3417 IF (p_task_num in ( MSC_CL_COLLECTION.PTASK_WIP_RES_REQ, MSC_CL_COLLECTION.PTASK_WIP_DEMAND,MSC_CL_COLLECTION.PTASK_RES_INST_REQ ) ) THEN
3418 if (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_INCR) then
3419 -- do net-change for this entity
3420 p_is_incremental_refresh := TRUE;
3421 p_is_partial_refresh := FALSE;
3422 p_exchange_mode := MSC_UTIL.SYS_NO;
3423 RETURN TRUE;
3424 elsif (MSC_CL_COLLECTION.v_coll_prec.wip_sn_flag = MSC_UTIL.SYS_TGT) then
3425 -- do targeted for this entity
3426 p_is_incremental_refresh := FALSE;
3427 p_is_partial_refresh := TRUE;
3428 p_exchange_mode := MSC_UTIL.SYS_YES;
3429 RETURN TRUE;
3430 else
3431 RETURN FALSE;
3432 end if;
3433 END IF;
3434 IF (p_task_num = MSC_CL_COLLECTION.PTASK_ATP_RULES) THEN
3435 if (MSC_CL_COLLECTION.v_coll_prec.atp_rules_flag = MSC_UTIL.SYS_YES) then
3436 p_is_incremental_refresh := FALSE;
3437 p_is_partial_refresh := TRUE;
3438 RETURN TRUE;
3439 else
3440 RETURN FALSE;
3441 end if;
3442 END IF;
3443
3444 IF (p_task_num = MSC_CL_COLLECTION.PTASK_CALENDAR_DATE OR
3445 (p_task_num = MSC_CL_COLLECTION.PTASK_BIS_PERIODS ) ) THEN
3446 if (MSC_CL_COLLECTION.v_coll_prec.calendar_flag = MSC_UTIL.SYS_YES) then
3447 p_is_incremental_refresh := FALSE;
3448 p_is_partial_refresh := TRUE;
3449 RETURN TRUE;
3450 else
3451 RETURN FALSE;
3452 end if;
3453 END IF;
3454
3455 IF (p_task_num = MSC_CL_COLLECTION.PTASK_DEMAND_CLASS) THEN
3456 if (MSC_CL_COLLECTION.v_coll_prec.demand_class_flag = MSC_UTIL.SYS_YES) then
3457 p_is_incremental_refresh := FALSE;
3458 p_is_partial_refresh := TRUE;
3459 RETURN TRUE;
3460 else
3461 RETURN FALSE;
3462 end if;
3463 END IF;
3464
3465 IF ((p_task_num = MSC_CL_COLLECTION.PTASK_BIS_PFMC_MEASURES) OR
3466 (p_task_num = MSC_CL_COLLECTION.PTASK_BIS_TARGET_LEVELS) OR
3467 (p_task_num = MSC_CL_COLLECTION.PTASK_BIS_TARGETS ) OR
3468 (p_task_num = MSC_CL_COLLECTION.PTASK_BIS_BUSINESS_PLANS)) THEN
3469 IF MSC_CL_COLLECTION.v_coll_prec.kpi_bis_flag = MSC_UTIL.SYS_YES THEN
3470 p_is_incremental_refresh := FALSE;
3471 p_is_partial_refresh := TRUE;
3472 RETURN TRUE;
3473 else
3474 RETURN FALSE;
3475 end if;
3476 END IF;
3477
3478 IF (p_task_num = MSC_CL_COLLECTION.PTASK_PARAMETER) THEN
3479 if (MSC_CL_COLLECTION.v_coll_prec.parameter_flag = MSC_UTIL.SYS_YES) then
3480 p_is_incremental_refresh := FALSE;
3481 p_is_partial_refresh := TRUE;
3482 RETURN TRUE;
3483 else
3484 RETURN FALSE;
3485 end if;
3486 END IF;
3487
3488 IF (p_task_num = MSC_CL_COLLECTION.PTASK_PLANNERS) THEN
3489 if (MSC_CL_COLLECTION.v_coll_prec.planner_flag = MSC_UTIL.SYS_YES) then
3490 p_is_incremental_refresh := FALSE;
3491 p_is_partial_refresh := TRUE;
3492 RETURN TRUE;
3493 else
3494 RETURN FALSE;
3495 end if;
3496 END IF;
3497
3498 IF (p_task_num = MSC_CL_COLLECTION.PTASK_PROJECT) THEN
3499 if (MSC_CL_COLLECTION.v_coll_prec.project_flag = MSC_UTIL.SYS_YES) then
3500 p_is_incremental_refresh := FALSE;
3501 p_is_partial_refresh := TRUE;
3502 RETURN TRUE;
3503 else
3504 RETURN FALSE;
3505 end if;
3506 END IF;
3507
3508 IF (p_task_num = MSC_CL_COLLECTION.PTASK_HARD_RESERVATION) THEN
3509 if (MSC_CL_COLLECTION.v_coll_prec.reserves_flag = MSC_UTIL.SYS_YES) then
3510 p_is_incremental_refresh := FALSE;
3511 p_is_partial_refresh := TRUE;
3512 RETURN TRUE;
3513 else
3514 RETURN FALSE;
3515 end if;
3516 END IF;
3517
3518 IF (p_task_num = MSC_CL_COLLECTION.PTASK_SAFETY_STOCK) THEN
3519 if (MSC_CL_COLLECTION.v_coll_prec.saf_stock_flag = MSC_UTIL.SYS_YES) then
3520 p_is_incremental_refresh := FALSE;
3521 p_is_partial_refresh := TRUE;
3522 RETURN TRUE;
3523 else
3524 RETURN FALSE;
3525 end if;
3526 END IF;
3527
3528 IF (p_task_num = MSC_CL_COLLECTION.PTASK_SOURCING) THEN
3529 if (MSC_CL_COLLECTION.v_coll_prec.sourcing_rule_flag = MSC_UTIL.SYS_YES) then
3530 p_is_incremental_refresh := FALSE;
3531 p_is_partial_refresh := TRUE;
3532 RETURN TRUE;
3533 else
3534 RETURN FALSE;
3535 end if;
3536 END IF;
3537
3538 IF (p_task_num = MSC_CL_COLLECTION.PTASK_SUB_INVENTORY) THEN
3539 if (MSC_CL_COLLECTION.v_coll_prec.sub_inventory_flag = MSC_UTIL.SYS_YES) then
3540 p_is_incremental_refresh := FALSE;
3541 p_is_partial_refresh := TRUE;
3542 RETURN TRUE;
3543 else
3544 RETURN FALSE;
3545 end if;
3546 END IF;
3547
3548 IF (p_task_num = MSC_CL_COLLECTION.PTASK_UNIT_NUMBER) THEN
3549 if (MSC_CL_COLLECTION.v_coll_prec.unit_number_flag = MSC_UTIL.SYS_YES) then
3550 p_is_incremental_refresh := FALSE;
3551 p_is_partial_refresh := TRUE;
3552 RETURN TRUE;
3553 else
3554 RETURN FALSE;
3555 end if;
3556 END IF;
3557
3558 IF (p_task_num = MSC_CL_COLLECTION.PTASK_ITEM_SUBSTITUTES) THEN
3559 if (MSC_CL_COLLECTION.v_coll_prec.item_subst_flag = MSC_UTIL.SYS_YES) then
3560 p_is_incremental_refresh := FALSE;
3561 p_is_partial_refresh := TRUE;
3562 RETURN TRUE;
3563 else
3564 RETURN FALSE;
3565 end if;
3566 END IF;
3567
3568 IF (p_task_num = MSC_CL_COLLECTION.PTASK_COMPANY_USERS) THEN
3569 if (MSC_CL_COLLECTION.v_coll_prec.user_company_flag = MSC_UTIL.COMPANY_ONLY) OR
3570 (MSC_CL_COLLECTION.v_coll_prec.user_company_flag = MSC_UTIL.USER_AND_COMPANY) then
3571 p_is_incremental_refresh := FALSE;
3572 p_is_partial_refresh := TRUE;
3573 RETURN TRUE;
3574 else
3575 RETURN FALSE;
3576 end if;
3577 END IF;
3578
3579 RETURN FALSE;
3580
3581 END set_cont_refresh_type_ODS;
3582 --============================================================================
3583 END MSC_CL_CONT_COLL_FW;