1 PACKAGE MSC_CL_PULL AUTHID CURRENT_USER AS
2 /* $Header: MSCCLFAS.pls 120.21 2012/06/05 12:22:41 lsindhur ship $ */
3
4 -- SYS_YES NUMBER := MSC_UTIL.SYS_YES;
5 -- SYS_NO NUMBER := MSC_UTIL.SYS_NO ;
6
7 -- SYS_INCR NUMBER := MSC_UTIL.SYS_INCR; -- incr refresh
8 -- SYS_TGT NUMBER := MSC_UTIL.SYS_TGT; -- targeted refresh
9
10 -- G_SUCCESS NUMBER := MSC_UTIL.G_SUCCESS;
11 --G_WARNING NUMBER := MSC_UTIL.G_WARNING;
12 --G_ERROR NUMBER := MSC_UTIL.G_ERROR ;
13
14 --G_COMPLETE CONSTANT NUMBER := MSC_UTIL.G_COMPLETE ;
15 -- G_INCREMENTAL CONSTANT NUMBER := MSC_UTIL.G_INCREMENTAL;
16 -- G_PARTIAL CONSTANT NUMBER := MSC_UTIL.G_PARTIAL ;
17 -- G_TARGETED CONSTANT NUMBER := MSC_UTIL.G_TARGETED ;
18 -- agmcont:
19 -- G_CONT CONSTANT NUMBER := MSC_UTIL.G_CONT ;
20
21 -- G_ST_EMPTY CONSTANT NUMBER := MSC_UTIL.G_ST_EMPTY ;
22 -- G_ST_PULLING CONSTANT NUMBER := MSC_UTIL.G_ST_PULLING ;
23 -- G_ST_READY CONSTANT NUMBER := MSC_UTIL.G_ST_READY ;
24 -- G_ST_COLLECTING CONSTANT NUMBER := MSC_UTIL.G_ST_COLLECTING;
25 -- G_ST_PURGING CONSTANT NUMBER := MSC_UTIL.G_ST_PURGING ;
26
27 -- G_APPS107 NUMBER := MSC_UTIL.G_APPS107;
28 -- G_APPS110 NUMBER := MSC_UTIL.G_APPS110;
29 -- G_APPS115 NUMBER := MSC_UTIL.G_APPS115;
30 -- G_APPS120 NUMBER := MSC_UTIL.G_APPS120;
31
32 --G_ALL_ORGANIZATIONS CONSTANT NUMBER := MSC_UTIL.G_ALL_ORGANIZATIONS;
33
34 -- added for process mfg: OPM
35 v_delimiter CONSTANT VARCHAR2(1) := '/';
36 v_discrete_flag NUMBER;
37 v_process_flag NUMBER;
38
39 ----- PROFILE OPTION --------------------------------------------------
40
41 v_so_ship_arrive_value NUMBER;
42 v_mps_consume_profile_value NUMBER;
43 v_spread_load NUMBER;
44 v_hour_uom VARCHAR2(3);
45 v_lang VARCHAR2(4);
46 v_oe_install VARCHAR2(170);
47
48 ----- PARAMETERS --------------------------------------------------------
49
50
51 v_lrnn NUMBER; -- Last Refresh Number
52 v_lrn VARCHAR2(38);
53 v_so_lrn NUMBER; -- Last Refresh Number(Sales Orders)
54 v_crn NUMBER; -- Current Refresh Number
55 v_validation_org_id NUMBER;
56 v_refresh_id NUMBER;
57
58 v_apps_ver NUMBER;
59 v_instance_id NUMBER;
60 v_instance_type NUMBER;
61
62 v_instance_code VARCHAR2(10);
63 v_dest_a2m VARCHAR2(128);
64
65 v_debug BOOLEAN := FALSE;
66 v_DSMode NUMBER;
67
68 -- MSC_UTIL.SYS_YES: This program is launched as a concurrent program.
69
70
71
72 v_icode VARCHAR2(4); -- INSTANCE CODE
73
74
75 v_current_date DATE;
76 v_current_user NUMBER;
77
78 v_dblink VARCHAR2(128);
79 v_dgmt NUMBER; -- Time difference to GMT ( unit: Days);
80
81 v_debug BOOLEAN := FALSE;
82
83 v_org_group VARCHAR2(30);
84
85 -- agmcont: add flag to indicate we are doing cont refresh
86 v_is_cont_refresh number := MSC_UTIL.SYS_NO;
87 V_IS_PARTIAL_REFRESH number := MSC_UTIL.SYS_NO;
88 v_cont_coll_thresh number;
89 v_cont_coll_freq number;
90
91
92 -- Task Control --
93
94 v_pipe_task_que VARCHAR2(32);
95 v_pipe_wm VARCHAR2(32);
96 v_pipe_mw VARCHAR2(32);
97 v_pipe_status VARCHAR2(32);
98
99
100 v_table_name VARCHAR2(32);
101 v_view_name VARCHAR2(32);
102
103 v_distributed_config_flag NUMBER;
104 v_monitor_request_id NUMBER;
105
106 ----- MSC PROFILE OPTION --------------------------------------------------
107
108 G_COLLECT_ITEM_COSTS varchar2(1) := NVL(FND_PROFILE.VALUE('MSC_COLLECT_COSTS_FOR_ITEM'),'Y');
109
110
111 v_mso_item_dmd_penalty NUMBER;
112 v_mso_item_cap_penalty NUMBER;
113 v_mso_org_dmd_penalty NUMBER;
114 v_mso_org_item_penalty NUMBER;
115 v_mso_org_res_penalty NUMBER;
116 v_mso_org_trsp_penalty NUMBER;
117 v_msc_aggreg_res_name NUMBER;
118 v_mso_res_penalty NUMBER;
119 v_mso_sup_cap_penalty NUMBER;
120 v_msc_bom_subst_priority NUMBER;
121 v_mso_trsp_penalty NUMBER;
122 v_msc_alt_bom_cost NUMBER;
123 v_mso_fcst_penalty NUMBER;
124 v_mso_so_penalty NUMBER;
125 v_msc_alt_op_res NUMBER;
126 v_msc_alt_res_priority NUMBER;
127 v_msc_batchable_flag NUMBER;
128 v_msc_batching_window NUMBER;
129 v_msc_min_capacity NUMBER;
130 v_msc_max_capacity NUMBER;
131 v_msc_unit_of_measure NUMBER;
132 v_msc_simul_res_seq NUMBER;
133 v_mrp_bis_av_discount NUMBER;
134 v_mrp_bis_price_list NUMBER;
135 v_msc_dmd_priority_flex_num NUMBER;
136 v_msc_fcst_priority_flex_num NUMBER;
137 -- v_msc_hub_curr_code VARCHAR2(30); -- bug # 6469722
138 -- v_msc_curr_conv_type VARCHAR2(30);
139 -- v_msc_future_days NUMBER;
140 -- v_msc_past_days NUMBER;
141
142 v_collect_completed_jobs number;
143 v_schedule_flag NUMBER;
144
145 ----- FLAGS -----------------------------------------------------------
146
147 ITEM_ENABLED NUMBER; -- ITEM, CATEGORY
148 VENDOR_ENABLED NUMBER;
149 FORECAST_ENABLED NUMBER;
150 CUSTOMER_ENABLED NUMBER;
151 BOM_ENABLED NUMBER;
152 HARD_RESRVS_ENABLED NUMBER;
153 SOURCING_ENABLED NUMBER; -- SOURCING, INTER_ORG_SHIPMENT
154 WIP_ENABLED NUMBER;
155 SS_ENABLED NUMBER;
156 PO_ENABLED NUMBER; -- PO, INTRANSIT
157 ITEM_SUBST_ENABLED NUMBER;
158 OH_ENABLED NUMBER;
159 SUPPLIER_CAP_ENABLED NUMBER;
160 UOM_ENABLED NUMBER;
161 MDS_ENABLED NUMBER;
162 MPS_ENABLED NUMBER;
163 NRA_ENABLED NUMBER;
164 SH_ENABLED NUMBER; -- sourcing history
165 TRIP_ENABLED NUMBER;
166 PO_RECEIPTS_ENABLED NUMBER;
167 INTERNAL_REPAIR_ENABLED NUMBER; -- For Bug 5909379
168 EXTERNAL_REPAIR_ENABLED NUMBER; -- For Bug 5935273
169 ----- FLAGS ADDED FOR SCE ---------------------------------------
170 USER_COMPANY_ENABLED NUMBER;
171 /* CP-ACK starts */
172 SUPPLIER_RESPONSE_ENABLED NUMBER;
173 /* CP-ACK ends */
174
175
176 ----- WORKER STATUS ---------------------------------------------
177
178 OK CONSTANT NUMBER := 1;
179 FAIL CONSTANT NUMBER := 0;
180
181 ----- TASK NUMBR -----------------------------------
182 ----- Assign a lower number to the task whose load is higher.
183
184 TOTAL_IWN CONSTANT NUMBER := 3;
185
186 TASK_ITEM1 CONSTANT NUMBER := 1;
187 TASK_ITEM2 CONSTANT NUMBER := 2;
188 TASK_ITEM3 CONSTANT NUMBER := 3;
189 TASK_OPER_NETWORKS CONSTANT NUMBER := 4;
190 TASK_ROUTING_OPERATIONS CONSTANT NUMBER := 5;
191 TASK_OPERATION_RES_SEQS CONSTANT NUMBER := 6;
192 TASK_OPERATION_RESOURCES CONSTANT NUMBER := 7;
193 TASK_OPERATION_COMPONENTS CONSTANT NUMBER := 8;
194 TASK_PROCESS_EFFECTIVITY CONSTANT NUMBER := 9;
195 TASK_SALES_ORDER1 CONSTANT NUMBER := 10;
196 TASK_SALES_ORDER2 CONSTANT NUMBER := 11;
197 TASK_BOM CONSTANT NUMBER := 12;
198 TASK_ROUTING CONSTANT NUMBER := 13;
199 TASK_CALENDAR_DATE CONSTANT NUMBER := 14;
200 TASK_MDS_DEMAND CONSTANT NUMBER := 15;
201 TASK_WIP_DEMAND CONSTANT NUMBER := 16;
202 TASK_TRADING_PARTNER CONSTANT NUMBER := 17;
203 TASK_SUB_INVENTORY CONSTANT NUMBER := 18;
204 TASK_HARD_RESERVATION CONSTANT NUMBER := 19;
205 TASK_SOURCING CONSTANT NUMBER := 20;
206 TASK_SUPPLIER_CAPACITY CONSTANT NUMBER := 21;
207 TASK_CATEGORY CONSTANT NUMBER := 22;
208 TASK_BOR CONSTANT NUMBER := 23;
209 TASK_UNIT_NUMBER CONSTANT NUMBER := 24;
210 TASK_SAFETY_STOCK CONSTANT NUMBER := 25;
211 TASK_PROJECT CONSTANT NUMBER := 26;
212 TASK_PARAMETER CONSTANT NUMBER := 27;
213 TASK_UOM CONSTANT NUMBER := 28;
214 TASK_ATP_RULES CONSTANT NUMBER := 29;
215 TASK_SALES_ORDER3 CONSTANT NUMBER := 30;
216 -- TASK_SALES_ORDER CONSTANT NUMBER := 31;
217 TASK_PLANNERS CONSTANT NUMBER := 31;
218 TASK_DEMAND_CLASS CONSTANT NUMBER := 32;
219 TASK_BUYER_CONTACT CONSTANT NUMBER := 33;
220 TASK_LOAD_FORECAST CONSTANT NUMBER := 34;
221 TASK_PO_SUPPLY CONSTANT NUMBER := 35;
222 TASK_WIP_SUPPLY CONSTANT NUMBER := 36;
223 TASK_OH_SUPPLY CONSTANT NUMBER := 37;
224 TASK_MPS_SUPPLY CONSTANT NUMBER := 38;
225 TASK_SCHEDULE CONSTANT NUMBER := 39;
226 TASK_RESOURCE CONSTANT NUMBER := 40;
227 -- New task added for Product Substitution ---
228 TASK_ITEM_SUBSTITUTES CONSTANT NUMBER := 41;
229
230 -- New SCE tasks --
231 TASK_USER_COMPANY CONSTANT NUMBER := 42;
232
233 /* CP-ACK starts */
234 TASK_SUPPLIER_RESPONSE CONSTANT NUMBER := 43;
235 /* CP-ACK ends */
236 TASK_BIS CONSTANT NUMBER := 44;
237 TASK_USER_SUPPLY CONSTANT NUMBER := 45;
238 TASK_USER_DEMAND CONSTANT NUMBER := 46;
239 TASK_TRIP CONSTANT NUMBER := 47;
240
241 /* AHL Visits - Modelled as Sales Orders */
242 TASK_AHL CONSTANT NUMBER := 48;
243
244 /*ds_plan: change start */
245 TASK_RESOURCE_INSTANCE CONSTANT NUMBER := 49;
246 TASK_RESOURCE_SETUP CONSTANT NUMBER := 50;
247 /*ds_plan: change end */
248
249 TASK_PO_PO_SUPPLY CONSTANT NUMBER := 51;
250 TASK_PO_REQ_SUPPLY CONSTANT NUMBER := 52;
251 TASK_ABC_CLASSES CONSTANT NUMBER :=53;
252
253 TASK_SALES_CHANNEL CONSTANT NUMBER := 54;
254 TASK_FISCAL_CALENDAR CONSTANT NUMBER := 55;
255
256 /* For SRP Collection Of Repair Order Data bug# 5909379*/
257 TASK_IRO CONSTANT NUMBER:=56 ;
258 TASK_IRO_DEMAND CONSTANT NUMBER:=57;
259
260 TASK_ERO CONSTANT NUMBER:=58 ;
261 TASK_ERO_DEMAND CONSTANT NUMBER:=59;
262
263 TASK_PAYBACK_DEMAND_SUPPLY NUMBER:=60;
264 TASK_CURRENCY_CONVERSION CONSTANT NUMBER := 61; -- bug # 6469722
265 TASK_DELIVERY_DETAILS CONSTANT NUMBER := 62;
266 TASK_IBUC_HISTORY CONSTANT NUMBER := 63;
267 TASK_SHORT_TEXT CONSTANT NUMBER:=64;
268 TASK_LONG_TEXT CONSTANT NUMBER:=65;
269 TASK_EAM_INFO CONSTANT NUMBER:=66;
270 TASK_EAM_FORECAST CONSTANT NUMBER:=67;
271 TASK_VISITS CONSTANT NUMBER:=68;
272 TASK_WO_ATTRIBUTES CONSTANT NUMBER:=69;
273 TASK_WO_TASK_HIERARCHY CONSTANT NUMBER:=70;
274 TASK_WO_OPERATION_REL CONSTANT NUMBER:=71;
275 TASK_WORK_BREAKDOWN_STRUCT CONSTANT NUMBER:=72;
276 TASK_WO_MILESTONES CONSTANT NUMBER:=73;
277 TASK_WO_SUB_COMP CONSTANT NUMBER:=74;
278 TASK_CMRO_FORECASTS CONSTANT NUMBER:=75;
279 TASK_CMRO_SUPP_RESRV CONSTANT NUMBER:=76;
280 TASK_CMRO_CLOSED_WO CONSTANT NUMBER:=77;
281 TASK_OSP_SUPPLY CONSTANT NUMBER := 78;
282
283 TOTAL_TASK_NUMBER CONSTANT NUMBER := 79;
284
285 -- Misc --
286 PIPE_TIME_OUT CONSTANT NUMBER := 30; -- 30 secs
287
288 ---------------------- Task Number ----------------------------
289
290 UNRESOLVABLE_ERROR CONSTANT NUMBER := -9999999;
291 START_ODS_LOAD CONSTANT NUMBER := -1111111; -- const to start the ODS monitor/worker
292
293 ----------------- Array Data Types -----------------------
294 TYPE NumTblTyp IS TABLE OF NUMBER;
295
296 ----------------- Added For Bug 6126698 and 6144734 ------------------
297 g_last_succ_iro_ref_time DATE;
298 g_LAST_SUCC_RES_REF_TIME DATE;
299
300
301 -- ================= Procedures ====================
302 PROCEDURE LAUNCH_WORKER(
303 ERRBUF OUT NOCOPY VARCHAR2,
304 RETCODE OUT NOCOPY NUMBER,
305 pMONITOR_REQUEST_ID IN NUMBER,
306 pINSTANCE_ID IN NUMBER,
307 pTIMEOUT IN NUMBER,
308 pLANG IN VARCHAR2 := NULL,
309 pRTYPE IN NUMBER,
313 pBOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
310 pREFRESH_ID IN NUMBER,
311 pAPPROV_SUPPLIER_CAP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
312 pATP_RULES_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
314 pBOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
315 pCALENDAR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
316 pDEMAND_CLASS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
317 pITEM_SUBST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
318 pFORECAST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
319 pITEM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
320 pKPI_BIS_ENABLED IN NUMBER := CASE WHEN v_apps_ver >= MSC_UTIL.G_APPS122 THEN MSC_UTIL.SYS_NO ELSE MSC_UTIL.SYS_YES END,
321 pMDS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
322 pMPS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
323 pOH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
324 pPARAMETER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
325 pPLANNER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
326 pPROJECT_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
327 pPUR_REQ_PO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
328 pRESERVES_HARD_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
329 pRESOURCE_NRA_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
330 pSafeStock_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
331 pSalesOrder_RTYPE IN NUMBER,
332 pSH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
333 pSOURCING_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
334 pSUB_INV_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
335 pTP_CUSTOMER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
336 pTP_VENDOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
337 pUNIT_NO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
338 pUOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
339 pUSER_SUPPLY_DEMAND IN NUMBER := MSC_UTIL.SYS_YES,
340 pWIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
341 pPO_RECEIPTS_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
342 pUSER_COMPANY_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
343 /* CP-ACK changes start */
344 pSUPPLIER_RESPONSE_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
345 /* CP-ACK changes end */
346 pTRIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES
347 );
348
349 PROCEDURE LAUNCH_MONITOR(
350 ERRBUF OUT NOCOPY VARCHAR2,
351 RETCODE OUT NOCOPY NUMBER,
352 pINSTANCE_ID IN NUMBER,
353 pORG_GROUP IN VARCHAR2,
354 pTotalWorkerNum IN NUMBER,
355 pTIMEOUT IN NUMBER, -- min
356 pLANG IN VARCHAR2 := NULL,
357 pOdsPURGEoption IN NUMBER := MSC_UTIL.SYS_NO,
358 pRTYPE IN NUMBER,
359 pANATBL_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
360 pAPPROV_SUPPLIER_CAP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
361 pATP_RULES_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
362 pBOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
363 pBOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
364 pCALENDAR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
365 pDEMAND_CLASS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
366 pITEM_SUBST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
367 pFORECAST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
368 pITEM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
369 pKPI_BIS_ENABLED IN NUMBER := CASE WHEN v_apps_ver >= MSC_UTIL.G_APPS122 THEN MSC_UTIL.SYS_NO ELSE MSC_UTIL.SYS_YES END,
370 pMDS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
371 pMPS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
372 pOH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
373 pPARAMETER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
374 pPLANNER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
375 pPO_RECEIPTS_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
376 pPROJECT_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
377 pPUR_REQ_PO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
378 pRESERVES_HARD_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
379 pRESOURCE_NRA_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
380 pSafeStock_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
381 pSalesOrder_RTYPE IN NUMBER,
382 pSH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
383 pSOURCING_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
384 pSUB_INV_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
385 /* CP-ACK starts */
386 pSUPPLIER_RESPONSE_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
387 /* CP-ACK ends */
391 pUNIT_NO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
388 pTP_CUSTOMER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
389 pTP_VENDOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
390 pTRIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
392 pUOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
393 pUSER_COMPANY_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
394 pUSER_SUPPLY_DEMAND IN NUMBER := MSC_UTIL.SYS_YES,
395 pWIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
396 pSALES_CHANNEL_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
397 pFISCAL_CALENDAR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
398 pINTERNAL_REPAIR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES, -- for bug 5909379
399 pEXTERNAL_REPAIR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES, -- for bug 5909379
400 pPAYBACK_DEMAND_SUPPLY_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
401 pCURRENCY_CONVERSION_ENABLED IN NUMBER := MSC_UTIL.SYS_NO, -- for bug 6469722
402 pDELIVERY_DETAILS_ENABLED IN NUMBER := MSC_UTIL.SYS_NO, -- for bug 6730983
403 PIBUC_HISTORY_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
404 pNOTES_ATTACH_ENABLED IN NUMBER := MSC_UTIL.SYS_NO
405 ,peAM_info IN NUMBER := MSC_UTIL.SYS_NO,
406 peAM_forecasts IN NUMBER := MSC_UTIL.SYS_NO,
407 peam_fc_st_date IN varchar2 := NULL ,
408 peam_fc_end_date IN VARCHAR2 := NULL ,
409 pCMRO_enabled IN NUMBER := MSC_UTIL.SYS_NO,
410 pCMRO_forecasts IN NUMBER := MSC_UTIL.SYS_NO,
411 pcmro_fc_st_date IN VARCHAR2 := NULL ,
412 pcmro_fc_end_date IN VARCHAR2 := NULL,
413 pcmro_closed_wo IN NUMBER := MSC_UTIL.SYS_NO,
414 posp_supply IN NUMBER := MSC_UTIL.SYS_NO
415 );
416
417 -- agmcont
418 -- Entry point for continuous collections
419
420 PROCEDURE LAUNCH_MONITOR_CONT(
421 ERRBUF OUT NOCOPY VARCHAR2,
422 RETCODE OUT NOCOPY NUMBER,
423 pINSTANCE_ID IN NUMBER,
424 pORG_GROUP IN VARCHAR2,
425 pTotalWorkerNum IN NUMBER,
426 pTIMEOUT IN NUMBER, -- min
427 pLANG IN VARCHAR2 := NULL,
428 -- pOdsPURGEoption IN NUMBER := SYS_NO,
429 -- pRTYPE IN NUMBER,
430 pTHRESH IN NUMBER,
431 pFREQ IN NUMBER,
432 pANATBL_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
433 pAPPROV_SUPPLIER_CAP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
434 pBOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
435 pBOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
436 pFORECAST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
437 pITEM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
438 pMDS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
439 pMPS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
440 pOH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
441 pPUR_REQ_PO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
442 pSalesOrder_RTYPE IN NUMBER,
443 pSUPPLIER_RESPONSE_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
444 pTRIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
445 pUSER_SUPPLY_DEMAND IN NUMBER := MSC_UTIL.SYS_YES,
446 pWIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
447 pATP_RULES_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
448 pCALENDAR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
449 pDEMAND_CLASS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
450 pITEM_SUBST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
451 pKPI_BIS_ENABLED IN NUMBER := CASE WHEN v_apps_ver >= MSC_UTIL.G_APPS122 THEN MSC_UTIL.SYS_NO ELSE MSC_UTIL.SYS_YES END,
452 pPARAMETER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
453 pPLANNER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
454 pPO_RECEIPTS_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
455 pPROJECT_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
456 pRESERVES_HARD_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
457 pRESOURCE_NRA_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
458 pSafeStock_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
459 pSH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
460 pSOURCING_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
461 pSUB_INV_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
462 pTP_CUSTOMER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
463 pTP_VENDOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
464 pUNIT_NO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
465 pUOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
466 pUSER_COMPANY_ENABLED IN NUMBER := MSC_UTIL.SYS_YES
467
468 );
472 RETCODE OUT NOCOPY NUMBER,
469
470 PROCEDURE LAUNCH_WORKER_CONT(
471 ERRBUF OUT NOCOPY VARCHAR2,
473 pMONITOR_REQUEST_ID IN NUMBER,
474 pINSTANCE_ID IN NUMBER,
475 pTIMEOUT IN NUMBER,
476 pLANG IN VARCHAR2 := NULL,
477 pRTYPE IN NUMBER,
478 pREFRESH_ID IN NUMBER,
479 pAPPROV_SUPPLIER_CAP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
480 pATP_RULES_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
481 pBOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
482 pBOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
483 pCALENDAR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
484 pDEMAND_CLASS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
485 pITEM_SUBST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
486 pFORECAST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
487 pITEM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
488 pKPI_BIS_ENABLED IN NUMBER := CASE WHEN v_apps_ver >= MSC_UTIL.G_APPS122 THEN MSC_UTIL.SYS_NO ELSE MSC_UTIL.SYS_YES END,
489 pMDS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
490 pMPS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
491 pOH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
492 pPARAMETER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
493 pPLANNER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
494 pPROJECT_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
495 pPUR_REQ_PO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
496 pRESERVES_HARD_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
497 pRESOURCE_NRA_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
498 pSafeStock_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
499 pSalesOrder_RTYPE IN NUMBER,
500 pSH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
501 pSOURCING_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
502 pSUB_INV_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
503 pTP_CUSTOMER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
504 pTP_VENDOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
505 pUNIT_NO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
506 pUOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
507 pUSER_SUPPLY_DEMAND IN NUMBER := MSC_UTIL.SYS_YES,
508 pWIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
509 pUSER_COMPANY_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
510 /* CP-ACK changes start */
511 pSUPPLIER_RESPONSE_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
512 /* CP-ACK changes end */
513 pTRIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
514 pPO_RECEIPTS_ENABLED IN NUMBER := MSC_UTIL.SYS_NO
515 );
516
517 PROCEDURE LAUNCH_MONITOR_CONT_DET_SCH(
518 ERRBUF OUT NOCOPY VARCHAR2,
519 RETCODE OUT NOCOPY NUMBER,
520 pINSTANCE_ID IN NUMBER,
521 pORG_GROUP IN VARCHAR2,
522 pTotalWorkerNum IN NUMBER,
523 pTIMEOUT IN NUMBER, -- min
524 pLANG IN VARCHAR2 := NULL,
525 pTHRESH IN NUMBER,
526 pFREQ IN NUMBER,
527 pANATBL_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
528 pAPPROV_SUPPLIER_CAP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
529 pBOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
530 --pBOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
531 --pFORECAST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
532 pITEM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
533 --pMDS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
534 pMPS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
535 pOH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
536 pPUR_REQ_PO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
537 pSalesOrder_RTYPE IN NUMBER := MSC_UTIL.SYS_YES,
538 pSUPPLIER_RESPONSE_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
539 --pTRIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
540 --pUSER_SUPPLY_DEMAND IN NUMBER := MSC_UTIL.SYS_YES,
541 pWIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
542 --pATP_RULES_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
543 pCALENDAR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
544 pDEMAND_CLASS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
545 --pITEM_SUBST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
546 pKPI_BIS_ENABLED IN NUMBER := CASE WHEN v_apps_ver >= MSC_UTIL.G_APPS122 THEN MSC_UTIL.SYS_NO ELSE MSC_UTIL.SYS_YES END,
547 pPARAMETER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
551 pRESOURCE_NRA_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
548 pPLANNER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
549 pPROJECT_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
550 pRESERVES_HARD_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
552 pSafeStock_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
553 --pSH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
554 --pSOURCING_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
555 --pSUB_INV_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
556 pTP_CUSTOMER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
557 pTP_VENDOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
558 pUNIT_NO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
559 pUOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES
560 --pUSER_COMPANY_ENABLED IN NUMBER := MSC_UTIL.SYS_YES
561 );
562
563
564
565 PROCEDURE DELETE_PROCESS
566 ( ERRBUF OUT NOCOPY VARCHAR2,
567 RETCODE OUT NOCOPY NUMBER,
568 pINSTANCE_ID IN NUMBER);
569
570 PROCEDURE SALES_ORDER_REFRESH_TYPE ( p_instance_id in NUMBER,
571 so_sn_flag out NOCOPY NUMBER );
572
573 FUNCTION GET_ORG_STR(p_instance_id IN NUMBER, p_type IN NUMBER DEFAULT 2)
574 RETURN VARCHAR2;
575
576
577 PROCEDURE GET_DEPOT_ORG_STRINGS(p_instance_id IN NUMBER);
578
579
580 FUNCTION GET_ORG_STR RETURN VARCHAR2;
581
582 /* ds_plan: change start */
583
584 PROCEDURE LAUNCH_MONITOR_DET_SCH(
585 ERRBUF OUT NOCOPY VARCHAR2,
586 RETCODE OUT NOCOPY NUMBER,
587 pINSTANCE_ID IN NUMBER,
588 pORG_GROUP IN VARCHAR2,
589 pTotalWorkerNum IN NUMBER,
590 pTIMEOUT IN NUMBER,
591 pLANG IN VARCHAR2 := NULL,
592 pOdsPURGEoption IN NUMBER := MSC_UTIL.SYS_NO,
593 pRTYPE IN NUMBER,
594 pANATBL_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
595 pAPPROV_SUPPLIER_CAP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
596 --pATP_RULES_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
597 pBOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
598 --pBOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
599 pCALENDAR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
600 pDEMAND_CLASS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
601 --pITEM_SUBST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
602 --pFORECAST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
603 pITEM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
604 pKPI_BIS_ENABLED IN NUMBER := CASE WHEN v_apps_ver >= MSC_UTIL.G_APPS122 THEN MSC_UTIL.SYS_NO ELSE MSC_UTIL.SYS_YES END,
605 --pMDS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
606 pMPS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
607 pOH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
608 pPARAMETER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
609 pPLANNER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
610 pPROJECT_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
611 pPUR_REQ_PO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
612 pRESERVES_HARD_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
613 pRESOURCE_NRA_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
614 pSafeStock_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
615 pSalesOrder_RTYPE IN NUMBER,
616 --pSH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
617 --pSOURCING_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
618 --pSUB_INV_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
619 pSUPPLIER_RESPONSE_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
620 pTP_CUSTOMER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
621 pTP_VENDOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
622 --pTRIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
623 pUNIT_NO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
624 pUOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
625 --pUSER_COMPANY_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
626 --pUSER_SUPPLY_DEMAND IN NUMBER := MSC_UTIL.SYS_YES,
627 pWIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES
628 );
629
630
631 /* ds_plan: change end */
632
633
634 -- ================= Procedures ====================
635
636 FUNCTION SET_ST_STATUS( ERRBUF OUT NOCOPY VARCHAR2,
637 RETCODE OUT NOCOPY NUMBER,
638 pINSTANCE_ID IN NUMBER,
639 pST_STATUS IN NUMBER,
640 pSO_RTYPE IN NUMBER:= NULL)
641 RETURN BOOLEAN;
642
643 PROCEDURE INITIALIZE( pREFRESH_ID OUT NOCOPY NUMBER,
647 PROCEDURE INITIALIZE_PULL_GLOBALS( pINSTANCE_ID IN NUMBER);
644 pTOTAL_TASK_NUMBER OUT NOCOPY NUMBER);
645
646
648
649 PROCEDURE FINAL( pINSTANCE_ID IN NUMBER,
650 pORG_GROUP IN VARCHAR2,
651 pRTYPE IN NUMBER,
652 pSO_RTYPE IN NUMBER,
653 pLRN IN NUMBER,
654 pSOURCING_ENABLED IN NUMBER,
655 prec IN MSC_UTIL.CollParamREC);
656
657 -- EXECUTE_TASK::pLRN if -1, then complete refresh.
658
659 PROCEDURE EXECUTE_TASK(
660 pSTATUS OUT NOCOPY NUMBER,
661 pTASKNUM IN NUMBER,
662 pIID IN NUMBER,
663 pLRN IN NUMBER,
664 pREFRESH_ID IN NUMBER,
665 ----- PROFILE OPTION --------------------------------------------------
666 pSO_SHIP_ARRIVE_VALUE IN NUMBER,
667 pMPS_CONSUME_PROFILE_VALUE IN NUMBER,
668 pSPREAD_LOAD IN NUMBER,
669 pHOUR_UOM IN VARCHAR2,
670 pLANG IN VARCHAR2,
671 pOE_INSTALL IN VARCHAR2,
672 ----- MSC PROFILE OPTION ----------------------------------------------
673 pMSO_ITEM_DMD_PENALTY IN NUMBER,
674 pMSO_ITEM_CAP_PENALTY IN NUMBER,
675 pMSO_ORG_DMD_PENALTY IN NUMBER,
676 pMSO_ORG_ITEM_PENALTY IN NUMBER,
677 pMSO_ORG_RES_PENALTY IN NUMBER,
678 pMSO_ORG_TRSP_PENALTY IN NUMBER,
679 pMSC_AGGREG_RES_NAME IN NUMBER,
680 pMSO_RES_PENALTY IN NUMBER,
681 pMSO_SUP_CAP_PENALTY IN NUMBER,
682 pMSC_BOM_SUBST_PRIORITY IN NUMBER,
683 pMSO_TRSP_PENALTY IN NUMBER,
684 pMSC_ALT_BOM_COST IN NUMBER,
685 pMSO_FCST_PENALTY IN NUMBER,
686 pMSO_SO_PENALTY IN NUMBER,
687 pMSC_ALT_OP_RES IN NUMBER,
688 pMSC_ALT_RES_PRIORITY IN NUMBER,
689 pMSC_BATCHABLE_FLAG IN NUMBER,
690 pMSC_BATCHING_WINDOW IN NUMBER,
691 pMSC_MIN_CAPACITY IN NUMBER,
692 pMSC_MAX_CAPACITY IN NUMBER,
693 pMSC_UNIT_OF_MEASURE IN NUMBER,
694 pMSC_SIMUL_RES_SEQ IN NUMBER,
695 pMRP_BIS_AV_DISCOUNT IN NUMBER,
696 pMRP_BIS_PRICE_LIST IN NUMBER,
697 pMSC_DMD_PRIORITY_FLEX_NUM IN NUMBER,
698 pMSC_FCST_PRIORITY_FLEX_NUM IN NUMBER,
699 -- pMSC_HUB_CURR_CODE IN VARCHAR2, -- bug # 6469722
700 -- pMSC_CURR_CONV_TYPE IN VARCHAR2,
701 -- pMSC_FUTURE_DAYS IN NUMBER,
702 -- pMSC_HISTORY_DAYS IN NUMBER,
703
704 ----- FLAGS -----------------------------------------------------------
705 pITEM_ENABLED IN NUMBER,
706 pVENDOR_ENABLED IN NUMBER,
707 pCUSTOMER_ENABLED IN NUMBER,
708 pBOM_ENABLED IN NUMBER,
709 pHARD_RESRVS_ENABLED IN NUMBER,
710 pSOURCING_ENABLED IN NUMBER,
711 pWIP_ENABLED IN NUMBER,
712 pPO_RECEIPTS_ENABLED IN NUMBER,
713 pSS_ENABLED IN NUMBER,
714 pPO_ENABLED IN NUMBER,
715 pITEM_SUBST_ENABLED IN NUMBER,
716 pOH_ENABLED IN NUMBER,
717 pSUPPLIER_CAP_ENABLED IN NUMBER,
718 pUOM_ENABLED IN NUMBER,
719 pMDS_ENABLED IN NUMBER,
720 pFORECAST_ENABLED IN NUMBER,
721 pMPS_ENABLED IN NUMBER,
722 pNRA_ENABLED IN NUMBER,
723 pSH_ENABLED IN NUMBER,
724 pUSER_COMPANY_ENABLED IN NUMBER,
725 /* CP-ACK starts */
726 pSUPPLIER_RESPONSE_ENABLED IN NUMBER,
727 /* CP-ACK ends */
728 pTRIP_ENABLED IN NUMBER,
729 prec in MSC_UTIL.CollParamREC
730 );
731
732 PROCEDURE ANALYZE_ALL_ST_TABLE;
733
734 PROCEDURE ANALYZE_ST_TABLE( pTASK_NUMBER IN NUMBER);
735
736 ------ NEW PROCEDURES for PARTIAL REPLACEMENT ----
737 FUNCTION Q_PARTIAL_TASK (p_instance_id NUMBER,
738 p_task_num NUMBER,
739 prec MSC_UTIL.CollParamREC,
740 p_collection_type NUMBER)
741 RETURN BOOLEAN;
742
743 -- agmcont
744
745
746
747
748 FUNCTION get_entity_lrn (p_instance_id in NUMBER,
749 p_task_num in NUMBER,
750 prec in MSC_UTIL.CollParamREC,
751 p_lrnn in number,
752 p_rtype in number,
753 p_org_group in varchar2,
754 p_entity_lrnn out NOCOPY number)
755 RETURN BOOLEAN;
756
757
758
759 END MSC_CL_PULL;
[Home] [Help]