[Home] [Help]
PACKAGE BODY: APPS.GMPMRRP
Source
1 PACKAGE BODY GMPMRRP AS
2 /* $Header: GMPMRRPB.pls 120.0 2005/05/26 14:32:16 appldev noship $ */
3
4 --Package Declarations
5
6 PROCEDURE mr_insert_header ;
7
8 PROCEDURE mr_bucket_report ;
9
10 FUNCTION mr_bucket_details
11 (V_item_id IN NUMBER,
12 V_planning_class IN VARCHAR2) RETURN NUMBER;
13
14 PROCEDURE mr_whse_list(V_item_id NUMBER) ;
15
16 PROCEDURE mr_get_balance(V_item_id NUMBER) ;
17
18 PROCEDURE mr_get_safety_stock(V_item_id NUMBER) ;
19
20
21 G_no_of_reports NUMBER := 0;
22 G_matl_rep_id NUMBER;
23 G_Buyer_plnr_id NUMBER;
24 G_Buyer_plnr VARCHAR2(100);
25 G_whse_list VARCHAR2(2000);
26 G_num_whses NUMBER;
27 G_schedule_id NUMBER;
28 G_mrp_id NUMBER;
29 G_fwhse_code VARCHAR2(4);
30 G_twhse_code VARCHAR2(4);
31 G_forgn_code VARCHAR2(4);
32 G_torgn_code VARCHAR2(4);
33 G_whse_security VARCHAR2(1);
34 G_on_hand1 NUMBER;
35 G_total_ss NUMBER;
36 G_no_safetystock NUMBER;
37 G_unit_ss NUMBER;
38 G_fplanning_class VARCHAR2(8);
39 G_tplanning_class VARCHAR2(8);
40 G_fitem_no VARCHAR2(32);
41 G_titem_no VARCHAR2(32);
42 G_log_text VARCHAR2(1000);
43
44 TYPE planning_rec_typ is RECORD(planning_class VARCHAR2(8),
45 item_id NUMBER);
46 TYPE planning_tab_typ IS TABLE OF planning_rec_typ
47 INDEX BY BINARY_INTEGER;
48 G_planning_tab planning_tab_typ;
49
50
51 /*============================================================================+
52 | |
53 | PROCEDURE NAME gmp_print_mrp |
54 | |
55 | DESCRIPTION Procedure to submit the request for report |
56 | |
57 | MODIFICATION HISTORY |
58 | 07/14/01 Praveen Reddy ----- created |
59 | |
60 | 14-MAR-03 BUG#2740325 V. Ajay Kumar -- Replaced the TO_CHAR |
61 | function with FND_DATE.DATE_TO_CANONICAL function. |
62 | 03-SEP-03 BUG#3125285 V. Ajay Kumar -- Changed the datatype of |
63 | V_run_date and V_run_date1 parameters from Date to |
64 | Varchar2 such that the date conversion takes place |
65 | properly across MLS environments in the procedure |
66 | gmp_print_mrp. Reverted the usage of the function |
67 | function FND_DATE.DATE_TO_CANONICAL for the V_run_date1 |
68 | and V_run_date parameters, in the gmp_print_mrp procedure. |
69 +============================================================================*/
70
71 PROCEDURE gmp_print_mrp
72 (errbuf OUT NOCOPY VARCHAR2,
73 retcode OUT NOCOPY VARCHAR2,
74 V_schedule_id IN NUMBER,
75 V_mrp_id IN NUMBER,
76 V_fplanning_class IN VARCHAR2,
77 V_tplanning_class IN VARCHAR2,
78 V_fwhse_code IN VARCHAR2,
79 V_twhse_code IN VARCHAR2,
80 V_forgn_code IN VARCHAR2,
81 V_torgn_code IN VARCHAR2,
82 V_fitem_no IN VARCHAR2,
83 V_titem_no IN VARCHAR2,
84 V_fBuyer_Plnr IN VARCHAR2,
85 V_tBuyer_Plnr IN VARCHAR2,
86 V_whse_security IN VARCHAR2,
87 V_printer IN VARCHAR2,
88 V_number_of_copies IN NUMBER,
89 V_user_print_style IN VARCHAR2,
90 V_run_date IN VARCHAR2, --VAK Changed Datatype from DATE to VARCHAR2.
91 V_run_date1 IN VARCHAR2, --VAK Changed Datatype from DATE to VARCHAR2.
92 V_schedule IN VARCHAR2,
93 V_usr_orgn_code IN VARCHAR2 ) IS
94
95 X_conc_id NUMBER;
96 X_status BOOLEAN;
97 X_ri_where VARCHAR2(3000); /* Changed to 3000 instead of 1000 - B3351464 */
98 X_fBuyer_Plnr VARCHAR2(100);
99 X_tBuyer_Plnr VARCHAR2(100);
100
101 -- B2502197 Rajesh Patangya Splited queries into 2 parts
102 CURSOR Cur_Buyer_plnr(C_fBuyer_Plnr VARCHAR2 , C_tBuyer_Plnr VARCHAR2)IS
103 SELECT user_name
104 FROM fnd_user
105 WHERE user_name BETWEEN C_fBuyer_Plnr AND C_tBuyer_Plnr;
106
107 CURSOR Cur_Buyer_plnr_id(C_Buyer_Plnr VARCHAR2)IS
108 SELECT user_id
109 FROM fnd_user
110 WHERE user_name = C_Buyer_Plnr ;
111
112 BEGIN
113 retcode := 0;
114 G_fwhse_code := V_fwhse_code;
115 G_twhse_code := V_twhse_code;
116 G_forgn_code := V_forgn_code;
117 G_torgn_code := V_torgn_code;
118 G_whse_security := V_whse_security;
119 G_mrp_id := V_mrp_id;
120 G_schedule_id := V_schedule_id;
121 G_fplanning_class := V_fplanning_class;
122 G_tplanning_class := V_tplanning_class;
123 G_fitem_no := V_fitem_no;
124 G_titem_no := V_titem_no;
125
126 IF V_fBuyer_Plnr IS NULL THEN
127 select min(user_name) INTO X_fBuyer_Plnr from fnd_user;
128 ELSE
129 X_fBuyer_Plnr := V_fBuyer_plnr;
130 END IF;
131 IF V_tBuyer_plnr IS NULL THEN
132 select max(user_name) INTO X_tBuyer_Plnr from fnd_user;
133 ELSE
134 X_tBuyer_Plnr := V_tBuyer_plnr;
135 END IF;
136
137 OPEN Cur_Buyer_plnr(X_fBuyer_Plnr, X_tBuyer_Plnr );
138 LOOP
139 FETCH Cur_Buyer_plnr INTO G_Buyer_plnr;
140 IF Cur_Buyer_plnr%NOTFOUND THEN
141 EXIT;
142 END IF;
143
144 -- B2502197 Rajesh Patangya Splited queries into 2 parts
145 OPEN Cur_Buyer_plnr_id(G_Buyer_Plnr);
146 FETCH Cur_Buyer_plnr_id INTO G_Buyer_plnr_id;
147 IF Cur_Buyer_plnr_id%NOTFOUND THEN
148 G_Buyer_plnr_id := -1;
149 END IF;
150 CLOSE Cur_Buyer_plnr_id;
151
152 IF G_Buyer_plnr_id > 0 THEN /* B2861091 - Added IF condition */
153
154 G_planning_tab.delete;
155 mr_insert_header;
156
157 IF G_planning_tab.count > 0 then
158 G_no_of_reports := to_char(to_number(G_no_of_reports) + 1);
159 mr_bucket_report;
160 -- Invoke the concurrent manager from here
161 IF V_number_of_copies > 0 THEN
162 X_status := FND_REQUEST.SET_PRINT_OPTIONS(V_printer,
163 UPPER(V_user_print_style),
164 V_number_of_copies, TRUE, 'N');
165 END IF;
166 -- request is submitted to the concurrent manager
167 --BEGIN BUG#3125285 V. Ajay Kumar
168 --BEGIN BUG#2740325 V. Ajay Kumar
169 X_conc_id := FND_REQUEST.SUBMIT_REQUEST('GMP','RIMR2USR','',
170 V_run_date1, FALSE,
171 TO_CHAR(G_matl_rep_id),TO_CHAR(G_Buyer_plnr_id),
172 X_ri_where,
173 V_run_date,
174 V_schedule, V_usr_orgn_code,chr(0),'','','',
175 '','','','','','','','','','',
176 '','','','','','','','','','',
177 '','','','','','','','','','',
178 '','','','','','','','','','',
179 '','','','','','','','','','',
180 '','','','','','','','','','',
181 '','','','','','','','','','',
182 '','','','','','','','','','',
183 '','','','','','','','','','');
184
185 --END BUG#2740325
186 --END BUG#3125285
187
188 IF X_conc_id = 0 THEN
189 G_log_text := FND_MESSAGE.GET;
190 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
191 retcode:=2;
192 exit;
193 ELSE
194 COMMIT ;
195 END IF;
196 END IF; /* End if for G_planning_tab.COUNT */
197 END IF ; /* END IF for G_Buyer_plnr_id - B2861091*/
198 END LOOP;
199
200 CLOSE Cur_Buyer_plnr; /* Bug# 2794837 - Cursor Already Open Error */
201
202 -- Print into the log file the information about the Reports are submitted
203 IF G_no_of_reports = 0 THEN
204 FND_MESSAGE.SET_NAME('GMP','PS_NO_TRANS');
205 G_log_text := FND_MESSAGE.GET;
206 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
207 /* Setting the Concurrent Status to Warning instead of giving Error */
208 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL)) THEN
209 NULL;
210 END IF;
211 retcode :=3;
212 ELSIF G_no_of_reports = 1 THEN
213 FND_MESSAGE.SET_NAME('GMP','GMP_REPORT_SUBMITTED');
214 G_log_text := FND_MESSAGE.GET;
215 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
216
217 ELSE
218 FND_MESSAGE.SET_NAME('GMP','GMP_MULTIPLE_REPORTS_SUBMITTED');
219 G_log_text := FND_MESSAGE.GET;
220 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
221
222 END IF;
223
224 EXCEPTION /* B2861091 - Added Exception Handler */
225 WHEN OTHERS THEN
226 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in Print MRP Bucket '||sqlerrm);
227
228 END gmp_print_mrp; /***** END PROCEDURE ***************************/
229
230
231 /*============================================================================+
232 | |
233 | PROCEDURE NAME mr_insert_header |
234 | |
235 | DESCRIPTION Procedure to insert data into ps_matl_hdr |
236 | |
237 | MODIFICATION HISTORY |
238 | 07/14/01 Praveen Reddy ----- created |
239 | |
240 +============================================================================*/
241
242 PROCEDURE mr_insert_header IS
243
244 X_where VARCHAR2(4000) := NULL;
245 X_select1 VARCHAR2(4000) := NULL;
246 X_row_count NUMBER;
247 X_rep_id NUMBER;
248 X_i NUMBER;
249 X_planning_class VARCHAR2(8) := NULL;
250 X_item_id NUMBER;
251 cur_planning NUMBER;
252 BEGIN
253 -- Building of the Where clause.
254 X_where := 'SELECT i.planning_class,i.item_id '
255 ||' FROM ps_oper_pcl c, ps_plng_cls p, ic_item_mst i, '
256 ||' fnd_user f '
257 ||' WHERE c.delete_mark=0 ' ;
258
259 X_where := X_where
260 ||' AND f.user_id = :1 '
261 ||' AND f.user_id = c.user_id '
262 ||' AND c.planning_class=i.planning_class '
263 ||' AND i.planning_class=p.planning_class ';
264
265 IF (G_fplanning_class IS NOT NULL) THEN
266 X_where := X_where||' AND c.planning_class >= :2 ' ;
267 END IF;
268 IF (G_tplanning_class IS NOT NULL) THEN
269 X_where := X_where||' AND c.planning_class <= :3 ' ;
270 END IF;
271 IF (G_fitem_no IS NOT NULL) THEN
272 X_where := X_where||' AND i.item_no >= :4 ' ;
273 END IF;
274 IF (G_titem_no IS NOT NULL) THEN
275 X_where := X_where||' AND i.item_no <= :5 ' ;
276 END IF;
277 IF (G_mrp_id IS NOT NULL) THEN
278 X_where:= X_where||' AND i.item_id in (select distinct item_id '
279 ||' FROM mr_tran_tbl WHERE mrp_id= to_char(:6) )' ;
280 END IF;
281
282 cur_planning := dbms_sql.open_cursor;
283 dbms_sql.parse (cur_planning, X_where,dbms_sql.NATIVE);
284
285 dbms_sql.bind_variable(cur_planning, ':1', G_Buyer_plnr_id);
286
287 IF (G_fplanning_class IS NOT NULL) THEN
288 dbms_sql.bind_variable(cur_planning, ':2', G_fplanning_class);
289 END IF;
290 IF (G_tplanning_class IS NOT NULL) THEN
291 dbms_sql.bind_variable(cur_planning, ':3', G_tplanning_class);
292 END IF;
293 IF (G_fitem_no IS NOT NULL) THEN
294 dbms_sql.bind_variable(cur_planning, ':4', G_fitem_no);
295 END IF;
296 IF (G_titem_no IS NOT NULL) THEN
297 dbms_sql.bind_variable(cur_planning, ':5', G_titem_no);
298 END IF;
299 IF (G_mrp_id IS NOT NULL) THEN
300 dbms_sql.bind_variable(cur_planning, ':6', G_mrp_id);
301 END IF;
302
303 dbms_sql.define_column (cur_planning, 1, X_planning_class, 8);
304 dbms_sql.define_column (cur_planning, 2, X_item_id);
305 X_row_count := dbms_sql.execute_and_fetch (cur_planning);
306 IF X_row_count > 0 THEN
307 SELECT gem5_matl_rep_id_s.nextval INTO X_rep_id FROM dual;
308 G_matl_rep_id := X_rep_id;
309 X_i := 0;
310 LOOP
311 dbms_sql.column_value (cur_planning, 1, X_Planning_class);
312 dbms_sql.column_value (cur_planning, 2, X_Item_id);
313 X_i := X_i + 1;
314 G_planning_tab(X_i).planning_class := X_planning_class;
315 G_planning_tab(X_i).item_id := X_item_id;
316 -- Inserts the data into Header table.
317 INSERT INTO ps_matl_hdr (matl_rep_id,planning_class,item_id)
318 VALUES(X_rep_id,X_planning_class,X_item_id);
319 IF dbms_sql.fetch_rows (cur_planning) <= 0 then
320 EXIT;
321 END IF;
322 END LOOP;
323
324 END IF;
325 dbms_sql.close_cursor (cur_planning);
326 EXCEPTION
327 WHEN OTHERS THEN
328 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Error in mr insert header'|| sqlerrm);
329
330
331 END mr_insert_header; /***** END PROCEDURE********************/
332
333 /*============================================================================+
334 | |
335 | PROCEDURE NAME mr_bucket_report |
336 | |
337 | DESCRIPTION Procedure to call mr_bucket_details for items. |
338 | |
339 | MODIFICATION HISTORY |
340 | 07/14/01 Praveen Reddy ----- created |
341 | |
342 +============================================================================*/
343
344 PROCEDURE mr_bucket_report IS
345
346 X_ret NUMBER;
347 X_i NUMBER := 0;
348 X_planning_class VARCHAR2(8);
349 X_item_id NUMBER;
350
351 BEGIN
352
353 IF G_planning_tab.count > 0 then
354 LOOP
355 X_i := X_i + 1;
356 EXIT WHEN X_i > G_planning_tab.count;
357 X_planning_class := G_planning_tab(X_i).planning_class;
358 X_item_id := G_planning_tab(X_i).item_id;
359 IF X_item_id IS NOT NULL THEN
360 X_ret := mr_bucket_details(X_item_id, X_planning_class);
361 END IF;
362 END LOOP;
363 END IF;
364
365 EXCEPTION
366 WHEN OTHERS THEN
367 FND_FILE.PUT_LINE ( FND_FILE.LOG, sqlerrm);
368
369 END mr_bucket_report; /***** END PROCEDURE ********************/
370
371 /*============================================================================+
372 | |
373 | PROCEDURE NAME mr_bucket_details |
374 | |
375 | DESCRIPTION Procedure to make a call to the stored procedure |
376 | to populate ps_matl_dtl table |
377 | |
378 | MODIFICATION HISTORY |
379 | 07/14/01 Praveen Reddy ----- created |
380 | |
381 +============================================================================*/
382
383 FUNCTION mr_bucket_details
384 (V_item_id IN NUMBER,
385 V_planning_class IN VARCHAR2) RETURN NUMBER IS
386 X_row_count NUMBER := 0;
387 BEGIN
388 -- to get warehouse list.
389 mr_whse_list(V_item_id) ;
390
391 IF G_whse_list IS NULL THEN
392 RETURN(-1);
393 END IF;
394 -- to get balance
395 mr_get_balance(V_item_id) ;
396 -- to get safety_stock
397 mr_get_safety_stock(V_item_id) ;
398 --
399 /*
400 FND_FILE.PUT_LINE ( FND_FILE.LOG,'After Get Safety stock ');
401 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Schedule id '||G_schedule_id);
402 FND_FILE.PUT_LINE ( FND_FILE.LOG,'mrp id '||G_mrp_id);
403 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Item id '||V_item_id);
404 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Whse List '||G_whse_list);
405 FND_FILE.PUT_LINE ( FND_FILE.LOG,' On hand '||G_on_hand1);
406 FND_FILE.PUT_LINE ( FND_FILE.LOG,' total ss '||G_total_ss);
407 FND_FILE.PUT_LINE ( FND_FILE.LOG,' Matl rep id '||G_matl_rep_id);
408 */
409 --
410 X_row_count := pkg_gmp_bucket_data.mr_bucket_data(G_schedule_id,
411 G_mrp_id,
412 V_item_id,
413 G_whse_list,
414 nvl(G_on_hand1,0),
415 nvl(G_total_ss,0),
416 G_matl_rep_id);
417
418 -- if there are no transactions then that item row is deleted from header table.
419 IF X_row_count = 0 THEN
420 DELETE FROM ps_matl_hdr
421 WHERE item_id = V_item_id
422 AND matl_rep_id = G_matl_rep_id;
423 RETURN(-1);
424 END IF;
425 RETURN(0);
426 --
427 EXCEPTION
428 WHEN OTHERS THEN
429 FND_FILE.PUT_LINE (FND_FILE.LOG,'Error in Bucket details '|| sqlerrm);
430 RETURN(0);
431 --
432 END mr_bucket_details; /***** END FUNCTION ********************/
433
434 /*============================================================================+
435 | |
436 | PROCEDURE NAME mr_whse_list |
437 | |
438 | DESCRIPTION Procedure to create the list of valid warehouses |
439 | |
440 | MODIFICATION HISTORY |
441 | 07/14/01 Praveen Reddy ----- created
442 | 04/05/04 M. Anil Kumar Bug#3519744 |
443 | Assigned variable X_count with zero so that the warehouse |
444 | list is created properly with the commasin between. |
445 +============================================================================*/
446
447 PROCEDURE mr_whse_list(V_item_id NUMBER) IS
448
449 TYPE ref_cursor_typ is REF CURSOR;
450 Cur_matl_act ref_cursor_typ;
451
452 --Bug 3168907 Kalyani Manda Added the cursor.
453 Cursor Cur_get_whse_item_id ( V_item_id NUMBER) IS
454 SELECT whse_item_id
455 FROM ic_item_mst
456 WHERE item_id = V_item_id;
457
458 X_sel_whse_list VARCHAR2(2000) := '';
459 X_matl_whse VARCHAR2(4);
460 old_matl_whse VARCHAR2(4);
461 X_count NUMBER(5) :=0; --Bug#3519744
462 X_forgn_code VARCHAR2(4);
463 X_torgn_code VARCHAR2(4);
464 X_fwhse_code VARCHAR2(4);
465 X_twhse_code VARCHAR2(4);
466 --Bug 3168907 Added the variables
467 X_whse_item_id NUMBER;
468 X_whse_eff_item_id NUMBER;
469
470
471 CURSOR Cur_min_max_orgn(p_schedule_id NUMBER) IS
472 SELECT min(orgn_code), max(orgn_code)
473 FROM ps_schd_dtl
474 WHERE schedule_id = p_schedule_id;
475 BEGIN
476 IF G_fwhse_code IS NULL THEN
477 SELECT MIN(whse_code) INTO X_fwhse_code FROM ic_whse_mst;
478 ELSE
479 X_fwhse_code := G_fwhse_code;
480 END IF;
481 IF G_twhse_code IS NULL THEN
482 SELECT MAX(whse_code) INTO X_twhse_code FROM ic_whse_mst;
483 ELSE
484 X_twhse_code := G_twhse_code;
485 END IF;
486 IF G_mrp_id IS NOT NULL AND G_schedule_id IS NOT NULL THEN
487
488 --Bug 3168907 Fetch the whse_item_id for the item.
489 OPEN Cur_get_whse_item_id(V_item_id);
490 FETCH Cur_get_whse_item_id INTO X_whse_item_id;
491 CLOSE Cur_get_whse_item_id;
492
493 OPEN Cur_min_max_orgn(G_schedule_id);
494 FETCH Cur_min_max_orgn INTO X_forgn_code, X_torgn_code;
495 CLOSE Cur_min_max_orgn;
496 IF G_forgn_code IS NOT NULL THEN
497 X_forgn_code := G_forgn_code;
498 END IF;
499 IF G_torgn_code IS NOT NULL THEN
500 X_torgn_code := G_torgn_code;
501 END IF;
502
503 IF nvl(G_whse_security,'N') = 'N' THEN
504 OPEN Cur_matl_act for
505 SELECT distinct trn.whse_code, whs.whse_item_id
506 FROM mr_tran_tbl trn, ps_whse_eff whs
507 WHERE mrp_id = G_mrp_id
508 AND item_id = V_item_id
509 AND trn.whse_code >= X_fwhse_code
510 AND trn.whse_code <= X_twhse_code
511 AND trn.whse_code = whs.whse_code
512 AND whs.plant_code in (select orgn_code from ps_schd_dtl
513 where schedule_id = G_schedule_id
514 AND orgn_code between X_forgn_code and X_torgn_code )
515 ORDER BY 1;
516 ELSE
517 OPEN Cur_matl_act for
518 SELECT distinct trn.whse_code, whs.whse_item_id --Bug 3168907 Added ps.whse_item_id
519 FROM mr_tran_tbl trn, ps_whse_eff whs, sy_orgn_usr org
520 WHERE mrp_id =G_mrp_id
521 AND item_id = V_item_id
522 AND trn.whse_code >= X_fwhse_code
523 AND trn.whse_code <= X_twhse_code
524 AND trn.whse_code = whs.whse_code
525 AND whs.plant_code in (select orgn_code from ps_schd_dtl
526 where schedule_id = G_schedule_id
527 AND orgn_code between X_forgn_code and X_torgn_code )
528 and whs.plant_code = org.orgn_code
529 and org.user_id = G_Buyer_plnr_id
530 ORDER BY 1;
531 END IF;
532 FETCH Cur_matl_act INTO X_matl_whse, X_whse_eff_item_id; --Bug 3168907 Added X_whse_eff_item_id
533 IF Cur_matl_act%NOTFOUND THEN
534 X_count:=0;
535 ELSE
536 LOOP
537 --Begin Bug 3168907
538 IF X_whse_eff_item_id IS NULL OR X_whse_item_id = X_whse_eff_item_id THEN
539
540 IF ( X_count > 0 ) AND ( nvl(X_matl_whse,'*') <> nvl(old_matl_whse,'*')) Then
541 X_sel_whse_list := X_sel_whse_list||',';
542 END IF;
543 /*B3659238 - Sowmya- MRP AND MPS BUCKETED REPORT SHOW ALL WAREHOUSES, MRP BUCKETED SHOWS 0 QTY
544 Donot allow to Append if the Whse is same*/
545
546 /* B3351464 - Donot allow to Append if the Whse is same */
547 IF nvl(X_matl_whse,'*') <> nvl(old_matl_whse,'*')
548 THEN
549 X_sel_whse_list := X_sel_whse_list||''''||X_matl_whse||'''';
550 old_matl_whse := X_matl_whse;
551 X_count := X_count + 1;
552 END IF;
553 /* End of changes B3351464 */
554 END IF;
555 FETCH Cur_matl_act INTO X_matl_whse, X_whse_eff_item_id;
556 IF Cur_matl_act%NOTFOUND THEN
557 EXIT;
558 END IF;
559 --End Bug 3168907
560 END LOOP;
561 END IF;
562 CLOSE Cur_matl_act;
563 G_num_whses := X_count;
564 G_whse_list := X_sel_whse_list;
565
566 END IF;
567 --
568 EXCEPTION
569 WHEN OTHERS THEN
570 FND_FILE.PUT_LINE (FND_FILE.LOG,'Error in Whse List '|| sqlerrm);
571 --
572 END mr_whse_list; /******** END PROCEDURE*************/
573
574 /*============================================================================+
575 | |
576 | PROCEDURE NAME mr_get_balance |
577 | |
578 | DESCRIPTION Procedure to get the on hand quantity |
579 | |
580 | MODIFICATION HISTORY |
581 | 07/14/01 Praveen Reddy ----- created |
582 | |
583 +============================================================================*/
584
585 PROCEDURE mr_get_balance(V_item_id NUMBER) IS
586
587 X_select1 VARCHAR2(4000) := NULL;
588 cur_balance NUMBER;
589 X_row_count NUMBER(5);
590 BEGIN
591 X_select1 :='SELECT sum(trans_qty) total'||
592 ' FROM mr_tran_tbl mr'||
593 ' WHERE mrp_id= to_char(:1) AND item_id = to_char(:2) ' ||
594 ' AND whse_code in ( ' || G_whse_list || ' ) ' ||
595 ' and doc_type='||''''||'BAL'||''''||
596 ' group by mr.doc_type';
597
598 /* G_whse_list is a list of warehouses, which are already validated
599 and hence not used as a bind variable */
600
601 cur_balance := dbms_sql.open_cursor;
602 dbms_sql.parse (cur_balance, X_select1,dbms_sql.NATIVE);
603
604 dbms_sql.bind_variable(cur_balance,':1',G_mrp_id);
605 dbms_sql.bind_variable(cur_balance,':2',V_item_id);
606
607 dbms_sql.define_column (cur_balance, 1, G_on_hand1);
608 X_row_count := dbms_sql.execute(cur_balance);
609 IF dbms_sql.fetch_rows (cur_balance) > 0 then
610 dbms_sql.column_value (cur_balance, 1, G_on_hand1);
611 END IF;
612 dbms_sql.close_cursor (cur_balance);
613 EXCEPTION
614 WHEN OTHERS THEN
615 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Error in mr get balance '|| sqlerrm);
616 IF dbms_sql.is_open (cur_balance) then
617 dbms_sql.close_cursor (cur_balance);
618 END IF;
619
620 END mr_get_balance; /******** END PROCEDURE*************/
621
622 /*============================================================================+
623 | |
624 | PROCEDURE NAME mr_get_safety_stock |
625 | |
626 | DESCRIPTION Procedure to get the safety stock details |
627 | |
628 | MODIFICATION HISTORY |
629 | 07/14/01 Praveen Reddy ----- created |
630 | |
631 +============================================================================*/
632
633 PROCEDURE mr_get_safety_stock(V_item_id NUMBER) IS
634
635 CURSOR Cur_unit_safety_stock(C_item_id NUMBER) IS
636 SELECT safety_stock
637 FROM ic_whse_inv
638 WHERE item_id= C_item_id
639 AND whse_code is NULL and delete_mark=0;
640
641
642 X_whse_cnt NUMBER(5);
643 X_select1 VARCHAR2(4000) := NULL ;
644 X_row_count NUMBER(5);
645 cur_sstock NUMBER;
646 BEGIN
647
648 X_select1 :='SELECT sum(safety_stock) total_ss,count(*) no_ss'||
649 ' FROM ic_whse_inv'||
650 ' WHERE item_id = to_char(:1) ' ||
651 ' AND whse_code in ( ' || G_whse_list || ' ) ' ||
652 ' AND delete_mark=0 ';
653
654 IF G_whse_list IS NOT NULL THEN
655 cur_sstock := dbms_sql.open_cursor;
656 dbms_sql.parse (cur_sstock, X_select1,dbms_sql.NATIVE);
657
658 dbms_sql.bind_variable(cur_sstock,':1',V_item_id);
659
660 dbms_sql.define_column (cur_sstock, 1, G_total_ss);
661 dbms_sql.define_column (cur_sstock, 2, G_no_safetystock);
662 X_row_count := dbms_sql.execute (cur_sstock);
663 IF dbms_sql.fetch_rows (cur_sstock) > 0 then
664 dbms_sql.column_value (cur_sstock, 1, G_total_ss);
665 dbms_sql.column_value (cur_sstock, 2, G_no_safetystock);
666 END IF;
667 ELSE
668 G_total_ss :=0;
669 G_no_safetystock :=0;
670 END IF;
671 dbms_sql.close_cursor (cur_sstock);
672
673 IF ((NVL(G_no_safetystock,0) < NVL(G_num_whses,0))) THEN
674 G_unit_ss := 0;
675 OPEN Cur_unit_safety_stock(V_item_id);
676 FETCH Cur_unit_safety_stock INTO G_unit_ss;
677 CLOSE Cur_unit_safety_stock;
678 X_whse_cnt := G_num_whses;
679 G_total_ss := NVL(G_total_ss,0) + (X_whse_cnt - NVL(G_no_safetystock,0)) * G_unit_ss;
680 END IF;
681
682 EXCEPTION
683 WHEN OTHERS THEN
684 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Error in get Safety ctock'|| sqlerrm);
685 IF dbms_sql.is_open (cur_sstock) then
686 dbms_sql.close_cursor (cur_sstock);
687 END IF;
688
689 END mr_get_safety_stock; /******** END PROCEDURE*************/
690
691
692 END GMPMRRP; /***** END PACKAGE BODY ***************************/