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