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