1 PACKAGE MSC_X_NETTING_PKG AS
2 /* $Header: MSCXNETS.pls 120.1 2008/01/07 09:28:28 dejoshi ship $ */
3
4 --===========================================================
5 -- Constants
6 --===========================================================
7 G_PLAN_ID Number := -1;
8 G_SR_INSTANCE_ID Number := -1;
9 SALES_FORECAST Number := 1;
10 ORDER_FORECAST Number := 2;
11 SUPPLY_COMMIT Number := 3;
12 HISTORICAL_SALES Number := 4;
13 ALLOCATED_ONHAND Number := 9;
14 UNALLOCATED_ONHAND Number := 10;
15 PURCHASE_ORDER Number := 13;
16 SALES_ORDER Number := 14;
17 ASN Number := 15;
18 SHIPMENT_RECEIPT Number := 16;
19 ASCP_SALES_ORDER_MDS Number := 6;
20 ASCP_SALES_ORDER Number := 30;
21 ASCP_PURCHASE_ORDER Number := 1;
22
23 G_ZERO Number := 0;
24
25 G_MAGIC_NUMBER Number := -99;
26
27 SUPPLY_PLANNING CONSTANT INTEGER := 1;
28 DEMAND_PLANNING CONSTANT INTEGER := 2;
29 VMI CONSTANT INTEGER := 3;
30 EXECUTION_ORDER CONSTANT INTEGER := 4;
31
32 BUYER CONSTANT INTEGER := 1;
33 SELLER CONSTANT INTEGER := 2;
34
35 G_PROCESSING_TIME Number := 9999;
36 G_REPLENISH_TIME_FENCE Number := 100;
37 G_AUTO_RELEASE_FLAG Varchar2(3) := 'No';
38
39 -----------------------------------------------------------------
40 -- the srs exception group constants
41 -----------------------------------------------------------------
42 G_GROUP1 CONSTANT Number := -1001; -- late order
43 G_GROUP2 CONSTANT Number := -1002; -- material shortage
44 G_GROUP3 CONSTANT Number := -1003; -- response required
45 G_GROUP4 CONSTANT Number := -1004; -- potential late order
46 G_GROUP5 CONSTANT Number := -1005; -- forecast mismatch
47 G_GROUP6 CONSTANT Number := -1006; -- early order
48 G_GROUP7 CONSTANT Number := -1007; -- material excess
49 G_GROUP8 CONSTANT Number := -1008; -- changed order
50 G_GROUP9 CONSTANT Number := -1009; -- forecast accuracy
51 G_GROUP10 CONSTANT Number := -1010; -- performance
52
53 -----------------------------------------------------------------
54 -- exception group constants
55 -----------------------------------------------------------------
56 G_LATE_ORDER CONSTANT Number := 1;
57 G_MATERIAL_SHORTAGE CONSTANT Number := 2;
58 G_RESPONSE_REQUIRED CONSTANT Number := 3;
59 G_POTENTIAL_LATE_ORDER CONSTANT Number := 4;
60 G_FORECAST_MISMATCH CONSTANT Number := 5;
61 G_EARLY_ORDER CONSTANT Number := 6;
62 G_MATERIAL_EXCESS CONSTANT Number := 7;
63 G_CHANGED_ORDER CONSTANT Number := 8;
64 G_FORECAST_ACCURACY CONSTANT Number := 9;
65 G_PERFORMANCE CONSTANT Number := 10;
66 ------------------------------------------------------------------
67 -- exception type constants
68 -----------------------------------------------------------------
69 G_EXCEP1 Number := 1;
70 G_EXCEP2 Number := 2;
71 G_EXCEP3 Number := 3;
72 G_EXCEP4 Number := 4;
73 G_EXCEP5 Number := 5;
74 G_EXCEP6 Number := 6;
75 G_EXCEP7 Number := 7;
76 G_EXCEP8 Number := 8;
77 G_EXCEP9 Number := 9;
78 G_EXCEP10 Number := 10;
79 G_EXCEP11 Number := 11;
80 G_EXCEP12 Number := 12;
81 G_EXCEP13 Number := 13;
82 G_EXCEP14 Number := 14;
83 G_EXCEP15 Number := 15;
84 G_EXCEP16 Number := 16;
85 G_EXCEP17 Number := 17;
86 G_EXCEP18 Number := 18;
87 G_EXCEP19 Number := 19;
88 G_EXCEP20 Number := 20;
89 G_EXCEP21 Number := 21;
90 G_EXCEP22 Number := 22;
91 G_EXCEP23 Number := 23;
92 G_EXCEP24 Number := 24;
93 G_EXCEP25 Number := 25;
94 G_EXCEP26 Number := 26;
95 G_EXCEP27 Number := 27;
96 G_EXCEP28 Number := 28;
97 G_EXCEP29 Number := 29;
98 G_EXCEP30 Number := 30;
99 G_EXCEP31 Number := 31;
100 G_EXCEP32 Number := 32;
101 G_EXCEP33 Number := 33;
102 G_EXCEP34 Number := 34;
103 G_EXCEP35 Number := 35;
104 G_EXCEP36 Number := 36;
105 G_EXCEP37 Number := 37;
106 G_EXCEP38 Number := 38;
107 G_EXCEP39 Number := 39;
108 G_EXCEP40 Number := 40;
109 G_EXCEP41 Number := 41;
110 G_EXCEP42 Number := 42;
111 G_EXCEP43 Number := 43;
112 G_EXCEP44 Number := 44;
113 G_EXCEP45 Number := 45;
114 G_EXCEP46 Number := 46;
115 G_EXCEP47 Number := 47;
116 G_EXCEP48 Number := 48;
117 G_EXCEP49 Number := 49; --bug# 2761469
118 G_EXCEP50 Number := 50;
119 G_EXCEP51 Number := 51;
120
121 ---------------------------------------------------------------
122 -- PL/SQL table types
123 ---------------------------------------------------------------
124 TYPE number_arr IS TABLE of NUMBER;
125 TYPE date_arr IS TABLE of DATE;
126
127 TYPE publisherList IS TABLE OF msc_companies.company_name%TYPE;
128 TYPE pubsiteList IS TABLE OF msc_company_sites.company_site_name%TYPE;
129 TYPE customerList IS TABLE OF msc_companies.company_name%TYPE;
130 TYPE custsiteList IS TABLE OF msc_company_sites.company_site_name%TYPE;
131 TYPE supplierList IS TABLE OF msc_companies.company_name%TYPE;
132 TYPE suppsiteList IS TABLE OF msc_company_sites.company_site_name%TYPE;
133 TYPE exceptypeList IS TABLE OF msc_x_exception_details.exception_type_name%TYPE;
134 TYPE excepgroupList IS TABLE OF msc_x_exception_details.exception_group_name%TYPE;
135 TYPE itemnameList IS TABLE OF msc_x_exception_details.item_name%TYPE;
136 TYPE itemdescList IS TABLE OF msc_x_exception_details.item_description%TYPE;
137 TYPE ordernumberList IS TABLE OF msc_x_exception_details.order_number%TYPE;
138 TYPE releasenumList IS TABLE OF msc_x_exception_details.release_number%TYPE;
139 TYPE linenumList IS TABLE OF msc_x_exception_details.line_number%TYPE;
140 TYPE tpitemnameList IS TABLE OF msc_x_exception_details.trading_partner_item_name%TYPE;
141 TYPE exceptbasisList IS TABLE OF msc_x_exception_details.exception_basis%TYPE;
142
143 --===========================================================
144
145
146 PROCEDURE Launch_Engine(p_errbuf OUT NOCOPY VARCHAR2,
147 p_retcode OUT NOCOPY VARCHAR2,
148 p_early_order IN VARCHAR2,
149 p_changed_order IN VARCHAR2,
150 p_forecast_accuracy IN VARCHAR2,
151 p_forecast_mismatch IN VARCHAR2,
152 p_late_order IN VARCHAR2,
153 p_material_excess IN VARCHAR2,
154 p_material_shortage IN VARCHAR2,
155 p_performance IN VARCHAR2,
156 p_potential_late_order IN VARCHAR2,
157 p_response_required IN VARCHAR2,
158 p_custom_exception IN VARCHAR2);
159
160 PROCEDURE Start_Netting (p_early_order IN VARCHAR2,
161 p_changed_order IN VARCHAR2,
162 p_forecast_accuracy IN VARCHAR2,
163 p_forecast_mismatch IN VARCHAR2,
164 p_late_order IN VARCHAR2,
165 p_material_excess IN VARCHAR2,
166 p_material_shortage IN VARCHAR2,
167 p_performance IN VARCHAR2,
168 p_potential_late_order IN VARCHAR2,
169 p_response_required IN VARCHAR2,
170 p_custom_exception IN VARCHAR2);
171
172
173 PROCEDURE POTENTIAL_LO_NETTING (p_max_refresh_number in Number,
174 p_potential_late_order in VARCHAR2);
175
176
177 FUNCTION DOES_EXCEPTION_ORG_EXIST (p_org_id IN Number) RETURN Number;
178
179 PROCEDURE POPULATE_EXCEPTION_ORG;
180
181
182 FUNCTION GENERATE_COMPLEMENT_EXCEPTION(p_company_id IN Number,
183 p_company_site_id In Number,
184 p_item_id IN Number,
185 p_refresh_number IN Number,
186 p_type IN Number,
187 p_role IN NUMBER default null)
188 RETURN Boolean;
189
190 PROCEDURE Delete_Item(l_type in varchar2,
191 l_key in varchar2);
192
193
194 PROCEDURE DELETE_WF_NOTIFICATION(p_type in varchar2,
195 p_key in varchar2);
196
197 FUNCTION GET_MESSAGE_TYPE(p_exception_code in Number) RETURN Varchar2;
198
199 FUNCTION GET_MESSAGE_GROUP(p_exception_group in Number) RETURN Varchar2;
200
201 PROCEDURE UPDATE_EXCEPTIONS_SUMMARY(p_company_id IN Number,
202 p_company_site_id IN Number,
203 p_item_id IN Number,
204 p_exception_type IN Number,
205 p_exception_group IN Number);
206
207
208 PROCEDURE ADD_EXCEPTION_DETAILS (p_company_id IN Number,
209 p_company_name IN Varchar2,
210 p_company_site_id IN Number,
211 p_company_site_name In Varchar2,
212 p_item_id In Number,
213 p_item_name In Varchar2,
214 p_item_description In Varchar2,
215 p_exception_type IN Number,
216 p_exception_type_name In Varchar2,
217 p_exception_group In Number,
218 p_exception_group_name IN Varchar2,
219 p_trx_id1 IN Number,
220 p_trx_id2 IN Number,
221 p_customer_id IN Number,
222 p_customer_name IN Varchar2,
223 p_customer_site_id IN Number,
224 p_customer_site_name in varchar2,
225 p_customer_item_name In Varchar2,
226 p_supplier_id IN Number,
227 p_supplier_name In Varchar2,
228 p_supplier_site_id IN Number ,
229 p_supplier_site_name In Varchar2,
230 p_supplier_item_name In Varchar2,
231 p_quantity3 IN Number ,
232 p_quantity1 In Number,
233 p_quantity2 In Number,
234 p_threshold In Number,
235 p_lead_time In Number,
236 p_item_min_qty In Number,
237 p_item_max_qty In Number,
238 p_order_number IN Varchar2 ,
239 p_release_number IN Varchar2,
240 p_line_number IN Varchar2,
241 p_end_order_number IN Varchar2 default null,
242 p_end_order_rel_number In Varchar2 default null,
243 p_end_order_line_number IN Varchar2 default null,
244 p_actual_date IN Date default null,
245 p_tp_actual_date IN Date default null,
246 p_creation_date IN Date default null,
247 p_tp_creation_date IN Date default null,
248 p_other_date IN Date default null
249 , p_replenishment_method IN NUMBER default null
250 );
251
252 PROCEDURE UPDATE_ITEM_EXCEPTION( p_company_id IN Number,
253 p_company_site_id IN Number,
254 p_item_id IN Number,
255 p_exception_type IN Number,
256 p_exception_group IN Number);
257
258 PROCEDURE ADD_ITEM_EXCEPTION( p_company_id IN Number,
259 p_company_site_id In Number,
260 p_item_id IN Number,
261 p_exception_type IN Number,
262 p_exception_group IN Number);
263
264 FUNCTION DOES_EXCEPTION_EXIST(p_company_id IN Number,
265 p_company_site_id IN Number,
266 p_item_id IN Number,
267 p_exception_type IN Number,
268 p_exception_group In Number) RETURN Number;
269
270
271 FUNCTION Get_Total_Qty( p_order_number IN VARCHAR2,
272 p_release_number IN VARCHAR2,
273 p_line_number IN VARCHAR2,
274 p_company_id IN Number,
275 p_company_site_id IN NUMBER,
276 p_tp_id IN Number,
277 p_tp_site_id IN NUMBER,
278 p_item_id IN NUMBER) RETURN Number;
279
280 FUNCTION Does_So_Exist( p_order_number IN VARCHAR2,
281 p_release_number IN VARCHAR2,
282 p_line_number IN VARCHAR2,
283 p_company_id IN Number,
284 p_company_site_id IN Number,
285 p_tp_id IN NUMBER,
286 p_tp_site_id IN NUMBER,
287 p_item_id IN NUMBER) RETURN Number;
288
289 FUNCTION Does_Po_Exist( p_end_order_number IN VARCHAR2,
290 p_end_order_rel_number IN VARCHAR2,
291 p_end_order_line_number IN VARCHAR2,
292 p_company_id IN NUMBER,
293 p_company_site_id IN NUMBER,
294 p_tp_id IN NUMBER,
295 p_tp_site_id IN NUMBER,
296 p_item_id IN NUMBER) RETURN NUMBER;
297
298 FUNCTION Does_ShipRcpt_Exist( p_order_number IN VARCHAR2,
299 p_release_number IN VARCHAR2,
300 p_line_number IN VARCHAR2,
301 p_company_id IN NUMBER,
302 p_company_site_id IN NUMBER,
303 p_tp_id IN NUMBER,
304 p_tp_site_id IN NUMBER,
305 p_item_id IN NUMBER) RETURN NUMBER;
306
307 FUNCTION Does_Detail_Excep_Exist(p_company_id IN Number,
308 p_company_site_id IN Number,
309 p_item_id IN Number,
310 p_exception_type IN Number,
311 p_trx_id1 IN Number,
312 p_trx_id2 IN Number default null) RETURN NUMBER;
313
314 FUNCTION DOES_LO_EXIST (p_company_id IN Number,
315 p_company_site_id IN Number,
316 p_item_id In Number,
317 p_exception_type In number,
318 p_trx_id In number) RETURN NUMBER ;
319
320 PROCEDURE DELETE_EXEC_ORDER_DEPENDENCY(p_refresh_number IN Number);
321
322 PROCEDURE PURGE_ZQTY_EXEC_ORDER (p_refresh_number IN Number);
323
324
325 PROCEDURE DELETE_OBSOLETE_EXCEPTIONS( p_company_id IN Number,
326 p_company_site_id in Number,
327 p_customer_id in Number,
328 p_customer_site_id In Number,
329 p_supplier_id In Number,
330 p_supplier_site_id IN Number,
331 p_exception_group IN Number,
332 p_curr_exc_type in Number,
333 p_obs_exc_type in Number,
334 p_item_id in Number,
335 p_bkt_start_date in Date,
336 p_bkt_end_date in Date,
337 p_type in Number default null,
338 p_transaction_id1 In Number default null,
339 p_transaction_id2 IN Number default null
340 );
341
342 PROCEDURE CLEAN_UP_PROCESS;
343
344
345 PROCEDURE ADD_TO_DELETE_TBL (p_company_id in number,
346 p_company_site_id in number,
347 p_customer_id in number,
348 p_customer_site_id in number,
349 p_supplier_id in number,
350 p_supplier_site_id in number,
351 p_item_id in number,
352 p_group in number,
353 p_type in number,
354 p_trxid1 in number,
355 p_trxid2 in number,
356 p_date1 in date,
357 p_date2 in date,
358 t_company_list IN OUT NOCOPY number_arr,
359 t_company_site_list IN OUT NOCOPY number_arr,
360 t_customer_list IN OUT NOCOPY number_arr,
361 t_customer_site_list IN OUT NOCOPY number_arr,
362 t_supplier_list IN OUT NOCOPY number_arr,
363 t_supplier_site_list IN OUT NOCOPY number_arr,
364 t_item_list IN OUT NOCOPY number_arr,
365 t_group_list IN OUT NOCOPY number_arr,
366 t_type_list IN OUT NOCOPY number_arr,
367 t_trxid1_list IN OUT NOCOPY number_arr,
368 t_trxid2_list IN OUT NOCOPY number_arr,
369 t_date1_list IN OUT NOCOPY date_arr,
370 t_date2_list IN OUT NOCOPY date_arr) ;
371
372 PROCEDURE archive_exception (t_company_list In number_arr,
373 t_company_site_list in number_arr,
374 t_customer_list in number_arr,
375 t_customer_site_list in number_arr,
376 t_supplier_list in number_arr,
377 t_supplier_site_list in number_arr,
378 t_item_list In number_arr,
379 t_group_list in number_arr,
380 t_type_list in number_arr,
381 t_trxid1_list in number_arr,
382 t_trxid2_list in number_arr,
383 t_date1_list in date_arr,
384 t_date2_list in date_arr);
385
386
387
388 --================================================================
389 -- with bulk insert
390 --================================================================
391 PROCEDURE ADD_TO_EXCEPTION_TBL (
392 p_company_id IN Number,
393 p_company_name IN Varchar2,
394 p_company_site_id IN Number,
395 p_company_site_name IN Varchar2,
396 p_item_id IN Number,
397 p_item_name IN Varchar2,
398 p_item_description IN Varchar2,
399 p_exception_type IN Number,
400 p_exception_type_name IN Varchar2,
401 p_exception_group IN Number,
402 p_exception_group_name IN Varchar2,
403 p_trx_id1 IN Number,
404 p_trx_id2 IN Number,
405 p_customer_id IN Number,
406 p_customer_name IN Varchar2,
407 p_customer_site_id IN Number,
408 p_customer_site_name IN varchar2,
409 p_customer_item_name IN Varchar2,
410 p_supplier_id IN Number,
411 p_supplier_name IN Varchar2,
412 p_supplier_site_id IN Number,
413 p_supplier_site_name IN Varchar2,
414 p_supplier_item_name IN Varchar2,
415 p_number1 IN Number,
416 p_number2 IN Number,
417 p_number3 IN Number,
418 p_threshold IN Number,
419 p_lead_time IN Number,
420 p_item_min_qty IN Number,
421 p_item_max_qty IN Number,
422 p_order_number IN Varchar2 ,
423 p_release_number IN Varchar2,
424 p_line_number IN Varchar2,
425 p_end_order_number IN Varchar2,
426 p_end_order_rel_number IN Varchar2,
427 p_end_order_line_number IN Varchar2,
428 p_creation_date IN Date,
429 p_tp_creation_date IN Date,
430 p_date1 IN Date,
431 p_date2 IN Date,
432 p_date3 IN Date,
433 p_date4 IN Date,
434 p_date5 IN Date,
435 p_exception_basis IN Varchar2,
436 a_company_id IN OUT NOCOPY number_arr,
437 a_company_name IN OUT NOCOPY publisherList,
438 a_company_site_id IN OUT NOCOPY number_arr,
439 a_company_site_name IN OUT NOCOPY pubsiteList,
440 a_item_id IN OUT NOCOPY number_arr,
441 a_item_name IN OUT NOCOPY itemnameList,
442 a_item_desc IN OUT NOCOPY itemdescList,
443 a_exception_type IN OUT NOCOPY number_arr,
444 a_exception_type_name IN OUT NOCOPY exceptypeList,
445 a_exception_group IN OUT NOCOPY number_arr,
446 a_exception_group_name IN OUT NOCOPY excepgroupList,
450 a_customer_name IN OUT NOCOPY customerList,
447 a_trx_id1 IN OUT NOCOPY number_arr,
448 a_trx_id2 IN OUT NOCOPY number_arr,
449 a_customer_id IN OUT NOCOPY number_arr,
451 a_customer_site_id IN OUT NOCOPY number_arr,
452 a_customer_site_name IN OUT NOCOPY custsiteList,
453 a_customer_item_name IN OUT NOCOPY itemnameList,
454 a_supplier_id IN OUT NOCOPY number_arr,
455 a_supplier_name IN OUT NOCOPY supplierList,
456 a_supplier_site_id IN OUT NOCOPY number_arr,
457 a_supplier_site_name IN OUT NOCOPY suppsiteList,
458 a_supplier_item_name IN OUT NOCOPY itemnameList,
459 a_number1 IN OUT NOCOPY number_arr,
460 a_number2 IN OUT NOCOPY number_arr,
461 a_number3 IN OUT NOCOPY number_arr,
462 a_threshold IN OUT NOCOPY number_arr,
463 a_lead_time IN OUT NOCOPY number_arr,
464 a_item_min_qty IN OUT NOCOPY number_arr,
465 a_item_max_qty IN OUT NOCOPY number_arr,
466 a_order_number IN OUT NOCOPY ordernumberList,
467 a_release_number IN OUT NOCOPY releasenumList,
468 a_line_number IN OUT NOCOPY linenumList,
469 a_end_order_number IN OUT NOCOPY ordernumberList,
470 a_end_order_rel_number IN OUT NOCOPY releasenumList,
471 a_end_order_line_number IN OUT NOCOPY linenumList,
472 a_creation_date IN OUT NOCOPY date_arr,
473 a_tp_creation_date IN OUT NOCOPY date_arr,
474 a_date1 IN OUT NOCOPY date_arr,
475 a_date2 IN OUT NOCOPY date_arr,
476 a_date3 IN OUT NOCOPY date_arr,
477 a_date4 IN OUT NOCOPY date_arr,
478 a_date5 IN OUT NOCOPY date_arr,
479 a_exception_basis IN OUT NOCOPY exceptbasisList);
480
481 --===================================================================
482 -- PROCEDURE POPUATE_EXCEPTION_DATA
483 --===================================================================
484 PROCEDURE POPULATE_EXCEPTION_DATA(
485 a_company_id IN number_arr,
486 a_company_name IN publisherList,
487 a_company_site_id IN number_arr,
488 a_company_site_name IN pubsiteList,
489 a_item_id IN number_arr,
490 a_item_name IN itemnameList,
491 a_item_desc IN itemdescList,
492 a_exception_type IN number_arr,
493 a_exception_type_name IN exceptypeList,
494 a_exception_group IN number_arr,
495 a_exception_group_name IN excepgroupList,
496 a_trx_id1 IN number_arr,
497 a_trx_id2 IN number_arr,
498 a_customer_id IN number_arr,
499 a_customer_name IN customerList,
500 a_customer_site_id IN number_arr,
501 a_customer_site_name IN custsiteList,
502 a_customer_item_name IN itemnameList,
503 a_supplier_id IN number_arr,
504 a_supplier_name IN supplierList,
505 a_supplier_site_id IN number_arr,
506 a_supplier_site_name IN suppsiteList,
507 a_supplier_item_name IN itemnameList,
508 a_number1 IN number_arr,
509 a_number2 IN number_arr,
510 a_number3 IN number_arr,
511 a_threshold IN number_arr,
512 a_lead_time IN number_arr,
513 a_item_min_qty IN number_arr,
514 a_item_max_qty IN number_arr,
515 a_order_number IN ordernumberList,
516 a_release_number IN releasenumList,
517 a_line_number IN linenumList,
518 a_end_order_number IN ordernumberList,
519 a_end_order_rel_number IN releasenumList,
520 a_end_order_line_number IN linenumList,
521 a_creation_date IN date_arr,
522 a_tp_creation_date IN date_arr,
523 a_date1 IN date_arr,
524 a_date2 IN date_arr,
525 a_date3 IN date_arr,
526 a_date4 IN date_arr,
527 a_date5 IN date_arr,
528 a_exception_basis IN exceptbasisList);
529
530 PROCEDURE update_item (p_refresh_number in Number);
531
532 PROCEDURE DELETE_EXCEP; --added for bug#6729356
533
534 END MSC_X_NETTING_PKG;
535
[Home] [Help]