1 PACKAGE MSC_CL_PULL AS -- specification
2 /* $Header: MSCCLFAS.pls 120.14 2008/01/07 18:56:59 vpalla 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
267 TOTAL_TASK_NUMBER CONSTANT NUMBER := 63;
268
269 -- Misc --
270 PIPE_TIME_OUT CONSTANT NUMBER := 30; -- 30 secs
271
272 ---------------------- Task Number ----------------------------
273
274 UNRESOLVABLE_ERROR CONSTANT NUMBER := -9999999;
275 START_ODS_LOAD CONSTANT NUMBER := -1111111; -- const to start the ODS monitor/worker
276
277 ----------------- Array Data Types -----------------------
278 TYPE NumTblTyp IS TABLE OF NUMBER;
279
280 ----------------- Added For Bug 6126698 and 6144734 ------------------
281 g_last_succ_iro_ref_time DATE;
282 g_LAST_SUCC_RES_REF_TIME DATE;
283
284
285 -- ================= Procedures ====================
286 PROCEDURE LAUNCH_WORKER(
287 ERRBUF OUT NOCOPY VARCHAR2,
288 RETCODE OUT NOCOPY NUMBER,
289 pMONITOR_REQUEST_ID IN NUMBER,
290 pINSTANCE_ID IN NUMBER,
291 pTIMEOUT IN NUMBER,
292 pLANG IN VARCHAR2 := NULL,
293 pRTYPE IN NUMBER,
294 pREFRESH_ID IN NUMBER,
295 pAPPROV_SUPPLIER_CAP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
296 pATP_RULES_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
297 pBOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
298 pBOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
299 pCALENDAR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
300 pDEMAND_CLASS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
301 pITEM_SUBST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
302 pFORECAST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
303 pITEM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
304 pKPI_BIS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
305 pMDS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
306 pMPS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
307 pOH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
308 pPARAMETER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
309 pPLANNER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
310 pPROJECT_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
311 pPUR_REQ_PO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
312 pRESERVES_HARD_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
313 pRESOURCE_NRA_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
314 pSafeStock_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
315 pSalesOrder_RTYPE IN NUMBER,
316 pSH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
317 pSOURCING_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
318 pSUB_INV_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
319 pTP_CUSTOMER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
320 pTP_VENDOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
321 pUNIT_NO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
322 pUOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
323 pUSER_SUPPLY_DEMAND IN NUMBER := MSC_UTIL.SYS_YES,
324 pWIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
325 pPO_RECEIPTS_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
326 pUSER_COMPANY_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
327 /* CP-ACK changes start */
328 pSUPPLIER_RESPONSE_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
329 /* CP-ACK changes end */
330 pTRIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES
331 );
332
333 PROCEDURE LAUNCH_MONITOR(
334 ERRBUF OUT NOCOPY VARCHAR2,
335 RETCODE OUT NOCOPY NUMBER,
336 pINSTANCE_ID IN NUMBER,
337 pORG_GROUP IN VARCHAR2,
338 pTotalWorkerNum IN NUMBER,
339 pTIMEOUT IN NUMBER, -- min
340 pLANG IN VARCHAR2 := NULL,
341 pOdsPURGEoption IN NUMBER := MSC_UTIL.SYS_NO,
342 pRTYPE IN NUMBER,
343 pANATBL_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
347 pBOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
344 pAPPROV_SUPPLIER_CAP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
345 pATP_RULES_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
346 pBOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
348 pCALENDAR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
349 pDEMAND_CLASS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
350 pITEM_SUBST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
351 pFORECAST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
352 pITEM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
353 pKPI_BIS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
354 pMDS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
355 pMPS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
356 pOH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
357 pPARAMETER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
358 pPLANNER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
359 pPO_RECEIPTS_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
360 pPROJECT_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
361 pPUR_REQ_PO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
362 pRESERVES_HARD_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
363 pRESOURCE_NRA_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
364 pSafeStock_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
365 pSalesOrder_RTYPE IN NUMBER,
366 pSH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
367 pSOURCING_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
368 pSUB_INV_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
369 /* CP-ACK starts */
370 pSUPPLIER_RESPONSE_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
371 /* CP-ACK ends */
372 pTP_CUSTOMER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
373 pTP_VENDOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
374 pTRIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
375 pUNIT_NO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
376 pUOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
377 pUSER_COMPANY_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
378 pUSER_SUPPLY_DEMAND IN NUMBER := MSC_UTIL.SYS_YES,
379 pWIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
380 pSALES_CHANNEL_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
381 pFISCAL_CALENDAR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
382 pINTERNAL_REPAIR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES, -- for bug 5909379
383 pEXTERNAL_REPAIR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES, -- for bug 5909379
384 pPAYBACK_DEMAND_SUPPLY_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
385 pCURRENCY_CONVERSION_ENABLED IN NUMBER := MSC_UTIL.SYS_NO, -- for bug 6469722
386 pDELIVERY_DETAILS_ENABLED IN NUMBER := MSC_UTIL.SYS_NO -- for bug 6730983
387 );
388
389 -- agmcont
390 -- Entry point for continuous collections
391
392 PROCEDURE LAUNCH_MONITOR_CONT(
393 ERRBUF OUT NOCOPY VARCHAR2,
394 RETCODE OUT NOCOPY NUMBER,
395 pINSTANCE_ID IN NUMBER,
396 pORG_GROUP IN VARCHAR2,
397 pTotalWorkerNum IN NUMBER,
398 pTIMEOUT IN NUMBER, -- min
399 pLANG IN VARCHAR2 := NULL,
400 -- pOdsPURGEoption IN NUMBER := SYS_NO,
401 -- pRTYPE IN NUMBER,
402 pTHRESH IN NUMBER,
403 pFREQ IN NUMBER,
404 pANATBL_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
405 pAPPROV_SUPPLIER_CAP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
406 pBOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
407 pBOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
408 pFORECAST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
409 pITEM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
410 pMDS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
411 pMPS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
412 pOH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
413 pPUR_REQ_PO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
414 pSalesOrder_RTYPE IN NUMBER,
415 pSUPPLIER_RESPONSE_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
416 pTRIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
417 pUSER_SUPPLY_DEMAND IN NUMBER := MSC_UTIL.SYS_YES,
418 pWIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
419 pATP_RULES_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
420 pCALENDAR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
421 pDEMAND_CLASS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
422 pITEM_SUBST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
423 pKPI_BIS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
424 pPARAMETER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
425 pPLANNER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
426 pPO_RECEIPTS_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
427 pPROJECT_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
428 pRESERVES_HARD_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
429 pRESOURCE_NRA_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
430 pSafeStock_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
431 pSH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
432 pSOURCING_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
433 pSUB_INV_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
434 pTP_CUSTOMER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
435 pTP_VENDOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
436 pUNIT_NO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
437 pUOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
438 pUSER_COMPANY_ENABLED IN NUMBER := MSC_UTIL.SYS_YES
439
440 );
441
442 PROCEDURE LAUNCH_WORKER_CONT(
443 ERRBUF OUT NOCOPY VARCHAR2,
444 RETCODE OUT NOCOPY NUMBER,
445 pMONITOR_REQUEST_ID IN NUMBER,
446 pINSTANCE_ID IN NUMBER,
447 pTIMEOUT IN NUMBER,
448 pLANG IN VARCHAR2 := NULL,
449 pRTYPE IN NUMBER,
450 pREFRESH_ID IN NUMBER,
451 pAPPROV_SUPPLIER_CAP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
452 pATP_RULES_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
453 pBOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
454 pBOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
455 pCALENDAR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
456 pDEMAND_CLASS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
457 pITEM_SUBST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
458 pFORECAST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
459 pITEM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
460 pKPI_BIS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
461 pMDS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
462 pMPS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
463 pOH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
464 pPARAMETER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
465 pPLANNER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
466 pPROJECT_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
467 pPUR_REQ_PO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
468 pRESERVES_HARD_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
469 pRESOURCE_NRA_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
470 pSafeStock_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
471 pSalesOrder_RTYPE IN NUMBER,
472 pSH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
473 pSOURCING_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
474 pSUB_INV_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
475 pTP_CUSTOMER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
476 pTP_VENDOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
477 pUNIT_NO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
478 pUOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
479 pUSER_SUPPLY_DEMAND IN NUMBER := MSC_UTIL.SYS_YES,
480 pWIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
481 pUSER_COMPANY_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
482 /* CP-ACK changes start */
483 pSUPPLIER_RESPONSE_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
484 /* CP-ACK changes end */
485 pTRIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
486 pPO_RECEIPTS_ENABLED IN NUMBER := MSC_UTIL.SYS_NO
487 );
488
489 PROCEDURE LAUNCH_MONITOR_CONT_DET_SCH(
490 ERRBUF OUT NOCOPY VARCHAR2,
491 RETCODE OUT NOCOPY NUMBER,
492 pINSTANCE_ID IN NUMBER,
493 pORG_GROUP IN VARCHAR2,
494 pTotalWorkerNum IN NUMBER,
495 pTIMEOUT IN NUMBER, -- min
496 pLANG IN VARCHAR2 := NULL,
497 pTHRESH IN NUMBER,
498 pFREQ IN NUMBER,
499 pANATBL_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
500 pAPPROV_SUPPLIER_CAP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
501 pBOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
502 --pBOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
503 --pFORECAST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
504 pITEM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
505 --pMDS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
506 pMPS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
507 pOH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
508 pPUR_REQ_PO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
509 pSalesOrder_RTYPE IN NUMBER := MSC_UTIL.SYS_YES,
510 pSUPPLIER_RESPONSE_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
511 --pTRIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
512 --pUSER_SUPPLY_DEMAND IN NUMBER := MSC_UTIL.SYS_YES,
513 pWIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
514 --pATP_RULES_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
518 pKPI_BIS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
515 pCALENDAR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
516 pDEMAND_CLASS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
517 --pITEM_SUBST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
519 pPARAMETER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
520 pPLANNER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
521 pPROJECT_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
522 pRESERVES_HARD_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
523 pRESOURCE_NRA_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
524 pSafeStock_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
525 --pSH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
526 --pSOURCING_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
527 --pSUB_INV_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
528 pTP_CUSTOMER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
529 pTP_VENDOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
530 pUNIT_NO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
531 pUOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES
532 --pUSER_COMPANY_ENABLED IN NUMBER := MSC_UTIL.SYS_YES
533 );
534
535
536
537 PROCEDURE DELETE_PROCESS
538 ( ERRBUF OUT NOCOPY VARCHAR2,
539 RETCODE OUT NOCOPY NUMBER,
540 pINSTANCE_ID IN NUMBER);
541
542 PROCEDURE SALES_ORDER_REFRESH_TYPE ( p_instance_id in NUMBER,
543 so_sn_flag out NOCOPY NUMBER );
544
545 FUNCTION GET_ORG_STR(p_instance_id IN NUMBER, p_type IN NUMBER DEFAULT 2)
546 RETURN VARCHAR2;
547
548
549 PROCEDURE GET_DEPOT_ORG_STRINGS(p_instance_id IN NUMBER);
550
551
552 FUNCTION GET_ORG_STR RETURN VARCHAR2;
553
554 /* ds_plan: change start */
555
556 PROCEDURE LAUNCH_MONITOR_DET_SCH(
557 ERRBUF OUT NOCOPY VARCHAR2,
558 RETCODE OUT NOCOPY NUMBER,
559 pINSTANCE_ID IN NUMBER,
560 pORG_GROUP IN VARCHAR2,
561 pTotalWorkerNum IN NUMBER,
562 pTIMEOUT IN NUMBER,
563 pLANG IN VARCHAR2 := NULL,
567 pAPPROV_SUPPLIER_CAP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
564 pOdsPURGEoption IN NUMBER := MSC_UTIL.SYS_NO,
565 pRTYPE IN NUMBER,
566 pANATBL_ENABLED IN NUMBER := MSC_UTIL.SYS_NO,
568 --pATP_RULES_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
569 pBOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
570 --pBOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
571 pCALENDAR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
572 pDEMAND_CLASS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
573 --pITEM_SUBST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
574 --pFORECAST_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
575 pITEM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
576 pKPI_BIS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
577 --pMDS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
578 pMPS_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
579 pOH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
580 pPARAMETER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
581 pPLANNER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
582 pPROJECT_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
583 pPUR_REQ_PO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
584 pRESERVES_HARD_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
585 pRESOURCE_NRA_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
586 pSafeStock_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
587 pSalesOrder_RTYPE IN NUMBER,
588 --pSH_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
589 --pSOURCING_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
590 --pSUB_INV_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
591 pSUPPLIER_RESPONSE_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
592 pTP_CUSTOMER_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
593 pTP_VENDOR_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
594 --pTRIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
595 pUNIT_NO_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
596 pUOM_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
597 --pUSER_COMPANY_ENABLED IN NUMBER := MSC_UTIL.SYS_YES,
598 --pUSER_SUPPLY_DEMAND IN NUMBER := MSC_UTIL.SYS_YES,
599 pWIP_ENABLED IN NUMBER := MSC_UTIL.SYS_YES
600 );
601
602
603 /* ds_plan: change end */
604
605
606 -- ================= Procedures ====================
607
608 FUNCTION SET_ST_STATUS( ERRBUF OUT NOCOPY VARCHAR2,
609 RETCODE OUT NOCOPY NUMBER,
610 pINSTANCE_ID IN NUMBER,
611 pST_STATUS IN NUMBER,
612 pSO_RTYPE IN NUMBER:= NULL)
613 RETURN BOOLEAN;
614
615 PROCEDURE INITIALIZE( pREFRESH_ID OUT NOCOPY NUMBER,
616 pTOTAL_TASK_NUMBER OUT NOCOPY NUMBER);
617
618
619 PROCEDURE INITIALIZE_PULL_GLOBALS( pINSTANCE_ID IN NUMBER);
620
621 PROCEDURE FINAL( pINSTANCE_ID IN NUMBER,
622 pORG_GROUP IN VARCHAR2,
623 pRTYPE IN NUMBER,
624 pSO_RTYPE IN NUMBER,
625 pLRN IN NUMBER,
626 pSOURCING_ENABLED IN NUMBER,
627 prec IN MSC_UTIL.CollParamREC);
628
629 -- EXECUTE_TASK::pLRN if -1, then complete refresh.
630
631 PROCEDURE EXECUTE_TASK(
632 pSTATUS OUT NOCOPY NUMBER,
633 pTASKNUM IN NUMBER,
634 pIID IN NUMBER,
635 pLRN IN NUMBER,
636 pREFRESH_ID IN NUMBER,
637 ----- PROFILE OPTION --------------------------------------------------
638 pSO_SHIP_ARRIVE_VALUE IN NUMBER,
639 pMPS_CONSUME_PROFILE_VALUE IN NUMBER,
640 pSPREAD_LOAD IN NUMBER,
641 pHOUR_UOM IN VARCHAR2,
642 pLANG IN VARCHAR2,
643 pOE_INSTALL IN VARCHAR2,
644 ----- MSC PROFILE OPTION ----------------------------------------------
645 pMSO_ITEM_DMD_PENALTY IN NUMBER,
646 pMSO_ITEM_CAP_PENALTY IN NUMBER,
647 pMSO_ORG_DMD_PENALTY IN NUMBER,
648 pMSO_ORG_ITEM_PENALTY IN NUMBER,
649 pMSO_ORG_RES_PENALTY IN NUMBER,
650 pMSO_ORG_TRSP_PENALTY IN NUMBER,
651 pMSC_AGGREG_RES_NAME IN NUMBER,
652 pMSO_RES_PENALTY IN NUMBER,
653 pMSO_SUP_CAP_PENALTY IN NUMBER,
654 pMSC_BOM_SUBST_PRIORITY IN NUMBER,
658 pMSO_SO_PENALTY IN NUMBER,
655 pMSO_TRSP_PENALTY IN NUMBER,
656 pMSC_ALT_BOM_COST IN NUMBER,
657 pMSO_FCST_PENALTY IN NUMBER,
659 pMSC_ALT_OP_RES IN NUMBER,
660 pMSC_ALT_RES_PRIORITY IN NUMBER,
661 pMSC_BATCHABLE_FLAG IN NUMBER,
662 pMSC_BATCHING_WINDOW IN NUMBER,
663 pMSC_MIN_CAPACITY IN NUMBER,
664 pMSC_MAX_CAPACITY IN NUMBER,
665 pMSC_UNIT_OF_MEASURE IN NUMBER,
666 pMSC_SIMUL_RES_SEQ IN NUMBER,
667 pMRP_BIS_AV_DISCOUNT IN NUMBER,
668 pMRP_BIS_PRICE_LIST IN NUMBER,
669 pMSC_DMD_PRIORITY_FLEX_NUM IN NUMBER,
670 pMSC_FCST_PRIORITY_FLEX_NUM IN NUMBER,
671 -- pMSC_HUB_CURR_CODE IN VARCHAR2, -- bug # 6469722
672 -- pMSC_CURR_CONV_TYPE IN VARCHAR2,
673 -- pMSC_FUTURE_DAYS IN NUMBER,
674 -- pMSC_HISTORY_DAYS IN NUMBER,
675
676 ----- FLAGS -----------------------------------------------------------
677 pITEM_ENABLED IN NUMBER,
678 pVENDOR_ENABLED IN NUMBER,
679 pCUSTOMER_ENABLED IN NUMBER,
680 pBOM_ENABLED IN NUMBER,
681 pHARD_RESRVS_ENABLED IN NUMBER,
682 pSOURCING_ENABLED IN NUMBER,
683 pWIP_ENABLED IN NUMBER,
684 pPO_RECEIPTS_ENABLED IN NUMBER,
685 pSS_ENABLED IN NUMBER,
686 pPO_ENABLED IN NUMBER,
687 pITEM_SUBST_ENABLED IN NUMBER,
688 pOH_ENABLED IN NUMBER,
689 pSUPPLIER_CAP_ENABLED IN NUMBER,
690 pUOM_ENABLED IN NUMBER,
691 pMDS_ENABLED IN NUMBER,
692 pFORECAST_ENABLED IN NUMBER,
693 pMPS_ENABLED IN NUMBER,
694 pNRA_ENABLED IN NUMBER,
695 pSH_ENABLED IN NUMBER,
696 pUSER_COMPANY_ENABLED IN NUMBER,
697 /* CP-ACK starts */
698 pSUPPLIER_RESPONSE_ENABLED IN NUMBER,
699 /* CP-ACK ends */
700 pTRIP_ENABLED IN NUMBER,
701 prec in MSC_UTIL.CollParamREC
702 );
703
704 PROCEDURE ANALYZE_ALL_ST_TABLE;
705
706 PROCEDURE ANALYZE_ST_TABLE( pTASK_NUMBER IN NUMBER);
707
708 ------ NEW PROCEDURES for PARTIAL REPLACEMENT ----
709 FUNCTION Q_PARTIAL_TASK (p_instance_id NUMBER,
710 p_task_num NUMBER,
711 prec MSC_UTIL.CollParamREC,
712 p_collection_type NUMBER)
713 RETURN BOOLEAN;
714
715 -- agmcont
716
717
718
719
720 FUNCTION get_entity_lrn (p_instance_id in NUMBER,
721 p_task_num in NUMBER,
722 prec in MSC_UTIL.CollParamREC,
723 p_lrnn in number,
724 p_rtype in number,
725 p_org_group in varchar2,
726 p_entity_lrnn out NOCOPY number)
727 RETURN BOOLEAN;
728
729
730
731 END MSC_CL_PULL;