[Home] [Help]
PACKAGE: APPS.MSC_CL_GMP_UTILITY
Source
1 PACKAGE MSC_CL_GMP_UTILITY AS -- specification
2 /* $Header: MSCCLGMS.pls 120.0.12010000.3 2008/08/18 07:05:48 sbyerram ship $ */
3
4
5
6 /* Global value variables */
7 v_sql_stmt VARCHAR2(32000) := NULL;
8 v_item_sql_stmt VARCHAR2(32000) := NULL;
9 v_sales_sql_stmt VARCHAR2(32000) := NULL;
10 v_forecast_sql_stmt VARCHAR2(32000) := NULL;
11 v_association_sql_stmt VARCHAR2(32000) := NULL;
12 v_null_date DATE := TO_DATE('01/01/1970','DD/MM/YYYY');
13 null_value VARCHAR2(2) := NULL;
14 V_YES NUMBER := 1;
15 V_WPS CONSTANT VARCHAR2(4) := 'WPS';
16 V_APS CONSTANT VARCHAR2(4) := 'APS';
17 V_BASED CONSTANT VARCHAR2(5) := 'BASED';
18 no_of_secs CONSTANT REAL := 86400;
19
20 /* Variables for document types */
21 v_doc_prod VARCHAR2(4) := 'PROD';
22 v_doc_fpo VARCHAR2(4) := 'FPO';
23 v_doc_opso VARCHAR2(4) := 'OPSO';
24 v_cp_enabled BOOLEAN := FALSE;
25
26 g_in_str_org VARCHAR2(4000) := NULL;
27 G_ALL_ORG CONSTANT VARCHAR2(10000) := '-999' ;
28
29 PROCEDURE extract_effectivities
30 (
31 at_apps_link IN VARCHAR2,
32 delimiter_char IN VARCHAR2,
33 instance IN INTEGER,
34 run_date IN DATE,
35 return_status IN OUT NOCOPY BOOLEAN
36 );
37 PROCEDURE extract_items
38 (
39 at_apps_link IN VARCHAR2,
40 instance IN INTEGER,
41 run_date IN DATE,
42 return_status IN OUT NOCOPY BOOLEAN
43 );
44 PROCEDURE extract_sub_inventory
45 (
46 at_apps_link IN VARCHAR2,
47 instance IN INTEGER,
48 run_date IN DATE,
49 return_status IN OUT NOCOPY BOOLEAN
50 );
51
52 PROCEDURE time_stamp ;
53
54 FUNCTION check_formula (pplant_code IN VARCHAR2,
55 porganization_id IN NUMBER,
56 pformula_id IN NUMBER) return BOOLEAN ;
57
58 FUNCTION check_formula_for_organization (
59 pplant_code IN VARCHAR2,
60 porganization_id IN NUMBER,
61 pformula_id IN NUMBER) return BOOLEAN ;
62
63 PROCEDURE validate_formula_for_orgn ;
64
65 PROCEDURE validate_formula ;
66
67 PROCEDURE invalidate_rtg_all_org (p_routing_id IN NUMBER) ;
68
69 PROCEDURE validate_routing (prouting_id IN NUMBER ,
70 porgn_code IN VARCHAR2,
71 pheader_loc IN NUMBER,
72 prout_valid OUT NOCOPY BOOLEAN) ;
73
74 PROCEDURE link_routing ;
75
76 PROCEDURE link_override_routing ;
77
78 PROCEDURE export_effectivities
79 (
80 return_status OUT NOCOPY BOOLEAN
81 ) ;
82
83 FUNCTION bsearch_routing (p_routing_id IN NUMBER ,
84 p_plant_code IN VARCHAR2)
85 RETURN INTEGER ;
86
87 /* Added New Function for Sequence Dependencies - SGIDUGU */
88 FUNCTION bsearch_setupid (p_oprn_id IN NUMBER ,
89 p_category_id IN NUMBER)
90 RETURN INTEGER ;
91
92 PROCEDURE write_process_effectivity
93 (
94 p_x_aps_fmeff_id IN NUMBER,
95 p_aps_fmeff_id IN NUMBER,
96 return_status OUT NOCOPY BOOLEAN
97 ) ;
98
99 PROCEDURE write_bom_components
100 (
101 p_x_aps_fmeff_id IN NUMBER,
102 return_status OUT NOCOPY BOOLEAN
103 ) ;
104
105 PROCEDURE write_routing
106 (
107 p_x_aps_fmeff_id IN NUMBER,
108 return_status OUT NOCOPY BOOLEAN
109 ) ;
110
111 PROCEDURE write_routing_operations
112 (
113 p_x_aps_fmeff_id IN NUMBER,
114 return_status OUT NOCOPY BOOLEAN
115 ) ;
116
117 PROCEDURE retrieve_effectivities
118 (
119 return_status OUT NOCOPY BOOLEAN
120 ) ;
121
122 PROCEDURE write_operation_components
123 (
124 p_x_aps_fmeff_id IN NUMBER,
125 precipe_id IN NUMBER,
126 return_status OUT NOCOPY BOOLEAN
127 ) ;
128
129 PROCEDURE setup
130 (
131 apps_link_name IN VARCHAR2,
132 delimiter_char IN VARCHAR2,
133 instance IN INTEGER,
134 run_date IN DATE,
135 return_status OUT NOCOPY BOOLEAN
136 ) ;
137
138 PROCEDURE gmp_putline (
139 v_text IN VARCHAR2,
140 v_mode IN VARCHAR2 ) ;
141
142 FUNCTION find_routing_header ( prouting_id IN NUMBER,
143 pplant_code IN VARCHAR2)
144 RETURN BOOLEAN ;
145 FUNCTION find_routing_offsets (p_formula_id IN NUMBER,
146 p_plant_code IN VARCHAR2)
147 RETURN NUMBER ;
148 FUNCTION get_offsets( p_formula_id IN NUMBER,
149 p_plant_code IN VARCHAR2,
150 p_formulaline_id IN NUMBER )
151 RETURN NUMBER ;
152
153 PROCEDURE msc_inserts
154 (
155 return_status OUT NOCOPY BOOLEAN
156 ) ;
157
158 /* Added new procedure to write the Resource Setups and Transitions - SGIDUGU */
159
160 PROCEDURE write_setups_and_transitions
161 (
162 return_status OUT NOCOPY BOOLEAN
163 ) ;
164
165 PROCEDURE write_step_dependency(
166 p_x_aps_fmeff_id IN NUMBER
167 );
168
169 FUNCTION enh_bsearch_stpno ( l_formula_id IN NUMBER,
170 l_recipe_id IN NUMBER,
171 l_item_id IN NUMBER
172 ) RETURN INTEGER ;
173 PROCEDURE bsearch_unique (p_resource_id IN NUMBER ,
174 p_category_id IN NUMBER ,
175 p_setup_id OUT NOCOPY NUMBER
176 ) ;
177
178 -- for future use
179 FUNCTION GMP_BOM_UTILITY1_R10
180 (
181 p_dblink IN VARCHAR2,
182 p_delimiter IN VARCHAR2,
183 p_instance IN INTEGER,
184 p_run_date IN DATE,
185 p_num1 IN NUMBER,
186 p_num2 IN NUMBER,
187 p_num3 IN NUMBER,
188 p_num4 IN NUMBER,
189 p_varchar1 IN VARCHAR2,
190 p_varchar2 IN VARCHAR2,
191 p_varchar3 IN VARCHAR2,
192 p_varchar4 IN VARCHAR2
193 ) RETURN INTEGER ;
194
195 FUNCTION GMP_BOM_UTILITY2_R10
196 (
197 p_dblink IN VARCHAR2,
198 p_delimiter IN VARCHAR2,
199 p_instance IN INTEGER,
200 p_run_date IN DATE,
201 p_num1 IN NUMBER,
202 p_num2 IN NUMBER,
203 p_num3 IN NUMBER,
204 p_num4 IN NUMBER,
205 p_varchar1 IN VARCHAR2,
206 p_varchar2 IN VARCHAR2,
207 p_varchar3 IN VARCHAR2,
208 p_varchar4 IN VARCHAR2
209 ) RETURN INTEGER ;
210
211 PROCEDURE GMP_BOM_PROC1_R10
212 (
213 p_dblink IN VARCHAR2,
214 p_delimiter IN VARCHAR2,
215 p_instance IN INTEGER,
216 p_run_date IN DATE,
217 p_num1 IN NUMBER,
218 p_num2 IN NUMBER,
219 p_num3 IN NUMBER,
220 p_num4 IN NUMBER,
221 p_varchar1 IN VARCHAR2,
222 p_varchar2 IN VARCHAR2,
223 p_varchar3 IN VARCHAR2,
224 p_varchar4 IN VARCHAR2,
225 return_status OUT NOCOPY BOOLEAN
226 ) ;
227
228 PROCEDURE GMP_BOM_PROC2_R10
229 (
230 p_dblink IN VARCHAR2,
231 p_delimiter IN VARCHAR2,
232 p_instance IN INTEGER,
233 p_run_date IN DATE,
234 p_num1 IN NUMBER,
235 p_num2 IN NUMBER,
236 p_num3 IN NUMBER,
237 p_num4 IN NUMBER,
238 p_varchar1 IN VARCHAR2,
239 p_varchar2 IN VARCHAR2,
240 p_varchar3 IN VARCHAR2,
241 p_varchar4 IN VARCHAR2,
242 return_status OUT NOCOPY BOOLEAN
243 ) ;
244
245 /*--------------------OPM PLD Specifications starts -------------------------*/
246
247 /* Procedure to extract production order to the for demands and supplies */
248 PROCEDURE production_orders(
249 pdblink IN VARCHAR2,
250 pinstance_id IN NUMBER,
251 prun_date IN DATE,
252 pdelimiter IN VARCHAR2,
253 return_status IN OUT NOCOPY BOOLEAN);
254
255 /* Universal routine to write to msc_st_supplies table */
256 PROCEDURE insert_supplies(
257 pitem_id NUMBER,
258 porganization_id NUMBER,
259 pinstance_id NUMBER,
260 pdate DATE,
261 pstart_date DATE,
262 pend_date DATE,
263 pbatch_id NUMBER,
264 pqty NUMBER,
265 pfirmed_ind NUMBER,
266 pbatchstep_no NUMBER, /* B2919303 */
267 porder_no VARCHAR2,
268 plot_number VARCHAR2,
269 pexpire_date DATE,
270 psupply_type NUMBER,
271 pproduct_item_id NUMBER); /* B2953953 - CoProduct changes */
272
273 /* Universal routine to write to msc_st_demands table */
274 PROCEDURE insert_demands(
275 pitem_id NUMBER,
276 porganization_id NUMBER,
277 pinstance_id NUMBER,
278 pbatch_id NUMBER,
279 pproduct_item_id NUMBER,
280 pdate DATE,
281 pqty NUMBER,
282 pbatchstep_no NUMBER, /* B2919303 */
283 porder_no VARCHAR2,
284 pdesignator VARCHAR2,
285 pnet_price NUMBER, /* B1200400 */
286 porigination_type NUMBER,
287 api_mode BOOLEAN DEFAULT FALSE,
288 pschedule_id NUMBER DEFAULT NULL);
289
290 /* routine to write to msc_st_resource_requirements */
291 PROCEDURE insert_resource_requirements(
292 porganization_id NUMBER,
293 pinstance_id NUMBER,
294 pseq_num NUMBER,
295 presource_id NUMBER,
296 pstart_date DATE,
297 pend_date DATE,
298 presource_usage NUMBER,
299 prsrc_cnt NUMBER,
300 pbatchstep_no NUMBER, /* B1224660 added new parameter */
301 pbatch_id NUMBER,
302 pstep_status NUMBER,
303 pschedule_flag NUMBER,
304 pparent_seq_num NUMBER,
305 pmin_xfer_qty NUMBER);
306
307 /* Procedure to extract onhand balances */
308 PROCEDURE extract_onhand_balances(
309 pdblink IN VARCHAR2,
310 pinstance_id IN NUMBER,
311 prun_date IN DATE,
312 pdelimiter IN VARCHAR2,
313 return_status IN OUT NOCOPY BOOLEAN);
314
315 /* Procedure to extract Inventory Transfers Demands B2756431 */
316 PROCEDURE extract_inv_transfer_demands(
317 pdblink IN VARCHAR2,
318 pinstance_id IN NUMBER,
319 prun_date IN DATE,
320 pdelimiter IN VARCHAR2,
321 pwhse_code IN VARCHAR2,
322 pdesignator IN VARCHAR2,
323 return_status IN OUT NOCOPY BOOLEAN);
324
325 /* Procedure to extract Inventory Transfers Supplies B2756431 */
326 PROCEDURE extract_inv_transfer_supplies(
327 pdblink IN VARCHAR2,
328 pinstance_id IN NUMBER,
329 prun_date IN DATE,
330 pdelimiter IN VARCHAR2,
331 return_status IN OUT NOCOPY BOOLEAN);
332
333
334 PROCEDURE onhand_inventory(
335 pdblink IN VARCHAR2,
336 pinstance_id IN NUMBER,
337 prun_date IN DATE,
338 pdelimiter IN VARCHAR2,
339 return_status IN OUT NOCOPY BOOLEAN);
340
341 /* Procedure to develop designator names */
342 PROCEDURE build_designator(
343 poccur IN NUMBER,
344 pdelimiter IN VARCHAR2,
345 pdesignator OUT NOCOPY VARCHAR2);
346
347 PROCEDURE sales_forecast_api(
348 errbuf OUT NOCOPY VARCHAR2,
349 retcode OUT NOCOPY VARCHAR2,
350 p_cp_enabled IN BOOLEAN DEFAULT TRUE,
351 p_run_date IN DATE DEFAULT SYSDATE);
352
353 /* Procedure to extract the sales and forecast demands */
354 PROCEDURE sales_forecast(
355 pdblink IN VARCHAR2,
356 pinstance_id IN NUMBER,
357 prun_date IN DATE,
358 pdelimiter IN VARCHAR2,
359 return_status IN OUT NOCOPY BOOLEAN,
360 api_mode IN BOOLEAN DEFAULT FALSE);
361
362 PROCEDURE write_this_so(
363 pcounter IN NUMBER,
364 sapi_mode IN BOOLEAN DEFAULT FALSE) ;
365
366 PROCEDURE write_this_fcst(
367 pcounter IN NUMBER,
368 fapi_mode IN BOOLEAN DEFAULT FALSE) ;
369
370 FUNCTION associate_forecasts(
371 pschd_fcst_cnt IN NUMBER,
372 pschd_id IN NUMBER ) return BOOLEAN ;
373
374 FUNCTION check_so(
375 pso_counter IN NUMBER,
376 pinventory_item_id IN NUMBER,
377 porganization_id IN NUMBER) return BOOLEAN ;
378
379 FUNCTION check_forecast(
380 pfcst_counter IN NUMBER,
381 pinventory_item_id IN NUMBER,
382 porganization_id IN NUMBER) return BOOLEAN ;
383
384 PROCEDURE consume_forecast(
385 pinventory_item_id IN NUMBER,
386 porganization_id IN NUMBER,
387 papi_mode IN BOOLEAN DEFAULT FALSE) ;
388
389 PROCEDURE write_forecast(
390 pfcst_counter IN NUMBER,
391 pinventory_item_id IN NUMBER,
395 PROCEDURE write_so(
392 porganization_id IN NUMBER,
393 papi_mode IN BOOLEAN DEFAULT FALSE) ;
394
396 pso_counter IN NUMBER,
397 pinventory_item_id IN NUMBER,
398 porganization_id IN NUMBER,
399 papi_mode IN BOOLEAN DEFAULT FALSE) ;
400
401 PROCEDURE insert_designator ;
402
403 PROCEDURE process_resource_rows(
404 pfirst_row IN NUMBER,
405 plast_row IN NUMBER,
406 pfound_mtl IN NUMBER,
407 porgn_id IN NUMBER,
408 pinstance_id IN NUMBER,
409 pinflate_wip IN NUMBER,
410 pmin_xfer_qty IN NUMBER);
411
412 PROCEDURE extract_forecasts(
413 pdblink IN VARCHAR2,
414 pinstance_id IN NUMBER,
415 prun_date IN DATE,
416 pdelimiter IN VARCHAR2,
417 return_status IN OUT NOCOPY BOOLEAN);
418
419
420 /*Sowmya - As Per latest FDD changes - Start*/
421 PROCEDURE production_reservations (
422 pdblink IN VARCHAR2,
423 pinstance_id IN NUMBER,
424 prun_date IN DATE,
425 pdelimiter IN VARCHAR2,
426 return_status IN OUT NOCOPY BOOLEAN);
427 /*Sowmya - As Per latest FDD changes - End*/
428
429 /*Sowmya */
430 PROCEDURE update_last_setup_id (
431 effbuf OUT NOCOPY VARCHAR2,
432 retcode OUT NOCOPY NUMBER,
433 f_orgn_code IN VARCHAR2,
434 t_orgn_code IN VARCHAR2
435 );
436 /*Sowmya */
437
438 FUNCTION GMP_APSDS_UTILITY1_R10
439 (
440 p_dblink IN VARCHAR2,
441 p_delimiter IN VARCHAR2,
442 p_instance IN INTEGER,
443 p_run_date IN DATE,
444 p_num1 IN NUMBER,
445 p_num2 IN NUMBER,
446 p_num3 IN NUMBER,
447 p_num4 IN NUMBER,
448 p_varchar1 IN VARCHAR2,
449 p_varchar2 IN VARCHAR2,
450 p_varchar3 IN VARCHAR2,
451 p_varchar4 IN VARCHAR2
452 ) RETURN INTEGER ;
453
454 PROCEDURE GMP_APSDS_PROC1_R10
455 (
456 p_dblink IN VARCHAR2,
457 p_delimiter IN VARCHAR2,
458 p_instance IN INTEGER,
459 p_run_date IN DATE,
460 p_num1 IN NUMBER,
461 p_num2 IN NUMBER,
462 p_num3 IN NUMBER,
463 p_num4 IN NUMBER,
464 p_varchar1 IN VARCHAR2,
465 p_varchar2 IN VARCHAR2,
466 p_varchar3 IN VARCHAR2,
467 p_varchar4 IN VARCHAR2,
468 return_status OUT NOCOPY BOOLEAN
469 ) ;
470
471 PROCEDURE sales_order_allocation (
472 pdblink IN VARCHAR2,
473 pinstance_id IN NUMBER,
474 pentity IN NUMBER,
475 return_status IN OUT NOCOPY BOOLEAN);
476
477 /*--------------------OPM Calendar Specification Starts ---------------*/
478
479 PROCEDURE log_message(pBUFF IN VARCHAR2) ;
480
481 /* Procedure to Insert department resources */
482 PROCEDURE rsrc_extract(p_instance_id IN NUMBER,
483 p_db_link IN VARCHAR2,
484 return_status OUT NOCOPY BOOLEAN);
485
486 /* Bug# 1494939 - Initial changes for Resource Calendar */
487 PROCEDURE update_trading_partners(p_org_id IN NUMBER,
488 p_cal_code IN VARCHAR2,
489 return_status OUT NOCOPY BOOLEAN);
490
491 PROCEDURE get_cal_no( p_cal_id IN NUMBER,
492 p_cal_no IN VARCHAR2,
493 p_icode IN VARCHAR2,
494 p_out_cal OUT NOCOPY VARCHAR2,
495 p_already_prefixed OUT NOCOPY VARCHAR2 );
496
497 PROCEDURE retrieve_calendar_detail( p_cal_id IN NUMBER,
498 p_calendar_no IN VARCHAR2,
499 p_cal_desc IN VARCHAR2,
500 p_run_date IN DATE,
501 p_db_link IN VARCHAR2,
502 p_instance_id IN NUMBER,
503 p_usage IN VARCHAR2,
504 return_status OUT NOCOPY BOOLEAN) ;
505
506 PROCEDURE insert_simulation_sets(p_org_id IN NUMBER,
507 p_instance_id IN NUMBER,
508 p_simulation_set IN VARCHAR2,
509 return_status OUT NOCOPY BOOLEAN);
510
511 PROCEDURE net_rsrc_insert(p_org_id IN NUMBER,
512 p_orgn_code IN VARCHAR2,
513 p_simulation_set IN VARCHAR2,
514 p_db_link IN VARCHAR2,
515 p_instance_id IN NUMBER,
516 p_run_date IN DATE ,
517 p_calendar_id IN NUMBER,
518 p_usage IN VARCHAR2,
519 return_status OUT NOCOPY BOOLEAN);
520
521 PROCEDURE populate_rsrc_cal(p_run_date IN DATE,
522 p_instance_id IN NUMBER,
523 p_delimiter IN VARCHAR2,
524 p_db_link IN VARCHAR2,
525 p_nra_enabled IN NUMBER,
526 return_status OUT NOCOPY BOOLEAN);
527
528
529 PROCEDURE insert_gmp_resource_avail( errbuf OUT NOCOPY VARCHAR2,
530 retcode OUT NOCOPY NUMBER ,
531 p_orgn_code IN VARCHAR2 ,
532 p_from_rsrc IN VARCHAR2 ,
533 p_to_rsrc IN VARCHAR2 ,
534 p_calendar_id IN NUMBER ) ;
535
536 PROCEDURE net_rsrc_avail_calculate(
537 p_org_id IN NUMBER,
538 p_orgn_code IN VARCHAR2,
539 p_calendar_id IN NUMBER,
540 p_instance_id IN NUMBER,
541 p_db_link IN VARCHAR2,
542 p_usage IN VARCHAR2, /* OPM-PS */
543 return_status OUT NOCOPY BOOLEAN);
544
545 PROCEDURE net_rsrc_avail_insert(p_instance_id IN NUMBER,
546 p_orgn_code IN VARCHAR2,
547 p_resource_instance_id IN NUMBER,
548 p_calendar_id IN NUMBER,
552 p_calendar_date IN DATE,
549 p_resource_id IN NUMBER,
550 p_assigned_qty IN NUMBER,
551 p_shift_num IN NUMBER,
553 p_from_time IN NUMBER,
554 p_to_time IN NUMBER ) ;
555
556 PROCEDURE net_rsrc(p_instance_id IN NUMBER,
557 p_org_id IN NUMBER,
558 p_simulation_set IN VARCHAR2,
559 p_resource_id IN NUMBER,
560 p_assigned_qty IN NUMBER,
561 p_shift_num IN NUMBER,
562 p_calendar_date IN DATE,
563 p_from_time IN NUMBER,
564 p_to_time IN NUMBER ) ;
565
566 FUNCTION ORG_STRING(instance_id IN NUMBER) return BOOLEAN ;
567
568 FUNCTION GMP_CAL_UTILITY1_R10
569 (
570 p_dblink IN VARCHAR2,
571 p_delimiter IN VARCHAR2,
572 p_instance IN INTEGER,
573 p_run_date IN DATE,
574 p_num1 IN NUMBER,
575 p_num2 IN NUMBER,
576 p_num3 IN NUMBER,
577 p_num4 IN NUMBER,
578 p_varchar1 IN VARCHAR2,
579 p_varchar2 IN VARCHAR2,
580 p_varchar3 IN VARCHAR2,
581 p_varchar4 IN VARCHAR2
582 ) RETURN INTEGER ;
583
584 PROCEDURE GMP_CAL_PROC1_R10
585 (
586 p_dblink IN VARCHAR2,
587 p_delimiter IN VARCHAR2,
588 p_instance IN INTEGER,
589 p_run_date IN DATE,
590 p_num1 IN NUMBER,
591 p_num2 IN NUMBER,
592 p_num3 IN NUMBER,
593 p_num4 IN NUMBER,
594 p_varchar1 IN VARCHAR2,
595 p_varchar2 IN VARCHAR2,
596 p_varchar3 IN VARCHAR2,
597 p_varchar4 IN VARCHAR2,
598 return_status OUT NOCOPY BOOLEAN
599 ) ;
600
601 PROCEDURE rsrcal_based_availability(p_run_date IN date,
602 p_instance_id IN number,
603 p_db_link IN varchar2,
604 return_status OUT NOCOPY BOOLEAN) ;
605
606
607 FUNCTION is_aps_compatible RETURN NUMBER;
608
609 END MSC_CL_GMP_UTILITY;