[Home] [Help]
PACKAGE BODY: APPS.GMPMRACT
Source
1 PACKAGE BODY GMPMRACT AS
2 /* $Header: GMPMRACB.pls 115.10 2004/04/05 07:55:55 mkalyani noship $ */
3
4 --Package Declarations
5
6 PROCEDURE mr_insert_header ;
7
8 PROCEDURE set_where_clause;
9
10 PROCEDURE mr_unbucket_report ;
11
12 PROCEDURE mr_data_retrieval
13 (V_item_id IN NUMBER,
14 V_planning_class IN VARCHAR2) ;
15
16 PROCEDURE mr_cleanup_details;
17
18 PROCEDURE mr_whse_list(V_item_id NUMBER) ;
19
20 PROCEDURE mr_get_balance(V_item_id NUMBER) ;
21
22 PROCEDURE mr_get_safety_stock(V_item_id NUMBER) ;
23
24
25 G_no_of_reports NUMBER := 0;
26 G_matl_rep_id NUMBER;
27 G_Buyer_plnr_id NUMBER;
28 G_Buyer_plnr VARCHAR2(100);
29 G_whse_list VARCHAR2(2000);
30 G_num_whses NUMBER;
31 G_schedule_id NUMBER;
32 G_mrp_id NUMBER;
33 G_whse_code VARCHAR2(4);
34 G_fwhse_code VARCHAR2(4);
35 G_twhse_code VARCHAR2(4);
36 G_ftrans_date DATE;
37 G_ttrans_date DATE;
38 G_whse_security VARCHAR2(1);
39 G_on_hand1 NUMBER;
40 G_total_ss NUMBER;
41 G_no_safetystock NUMBER;
42 G_unit_ss NUMBER;
43 G_fplanning_class VARCHAR2(8);
44 G_tplanning_class VARCHAR2(8);
45 G_fitem_no VARCHAR2(32);
46 G_titem_no VARCHAR2(32);
47 G_balance NUMBER(25);
48 G_balance1 NUMBER(25);
49 G_balance2 NUMBER(25);
50 G_start_balance NUMBER(19);
51 G_c_ind VARCHAR2(2);
52 G_critical_indicator VARCHAR2(1);
53 G_log_text VARCHAR2(1000);
54 G_sy_all VARCHAR2(1000);
55 G_cust_vend VARCHAR2(32);
56 G_doc_id NUMBER(10);
57 G_doc_no VARCHAR2(32);
58 G_orgn_code VARCHAR2(4);
59 G_line_no NUMBER(10);
60 G_doc_type VARCHAR2(4);
61 G_where VARCHAR2(2000);
62 --
63 TYPE planning_rec_typ is RECORD(planning_class VARCHAR2(8),item_id NUMBER);
64
65 TYPE planning_tab_typ IS TABLE OF planning_rec_typ INDEX BY BINARY_INTEGER;
66
67 G_planning_tab planning_tab_typ;
68
69 --
70 TYPE doc_typ is RECORD( doc_type Varchar2(4),
71 trans_date Date,
72 orgn_code Varchar2(4),
73 doc_id NUMBER(10),
74 trans_qty NUMBER,
75 cust_vend Varchar2(32),
76 line_no NUMBER(10),
77 whse_code VARCHAR2(4)
78 );
79 --
80 TYPE doc_tab_typ IS TABLE OF doc_typ INDEX BY BINARY_INTEGER;
81 G_doc_tab doc_tab_typ;
82
83
84 /*============================================================================+
85 | |
86 | PROCEDURE NAME print_mrp_activity |
87 | |
88 | DESCRIPTION Procedure to submit the request for report |
89 | |
90 | MODIFICATION HISTORY |
91 | 12/31/02 Sridhar Gidugu ----- created |
92 | 04/22/03 Sastry BUG#2889706 Moved the call to set_where_clause before|
93 | loop as where clause should be built only once. |
94 +============================================================================*/
95
96 PROCEDURE print_mrp_activity
97 (errbuf OUT NOCOPY VARCHAR2,
98 retcode OUT NOCOPY VARCHAR2,
99 V_schedule_id IN NUMBER,
100 V_mrp_id IN NUMBER,
101 V_fplanning_class IN VARCHAR2,
102 V_tplanning_class IN VARCHAR2,
103 V_fwhse_code IN VARCHAR2,
104 V_twhse_code IN VARCHAR2,
105 V_fitem_no IN VARCHAR2,
106 V_titem_no IN VARCHAR2,
107 V_fBuyer_Plnr IN VARCHAR2,
108 V_tBuyer_Plnr IN VARCHAR2,
109 V_ftrans_date IN DATE,
110 V_ttrans_date IN DATE,
111 V_whse_security IN VARCHAR2,
112 V_critical_indicator IN NUMBER,
113 V_printer IN VARCHAR2,
114 V_number_of_copies IN NUMBER,
115 V_user_print_style IN VARCHAR2,
116 V_run_date IN DATE,
117 V_run_date1 IN DATE,
118 V_schedule IN VARCHAR2,
119 V_usr_orgn_code IN VARCHAR2 ) IS
120
121 X_conc_id NUMBER;
122 X_status BOOLEAN;
123 X_ri_where VARCHAR2(1000);
124 X_fBuyer_Plnr VARCHAR2(100);
125 X_tBuyer_Plnr VARCHAR2(100);
126
127 CURSOR Cur_Buyer_plnr(C_fBuyer_Plnr VARCHAR2 , C_tBuyer_Plnr VARCHAR2)IS
128 SELECT user_name
129 FROM fnd_user
130 WHERE (C_fBuyer_Plnr is NULL OR user_name >= C_fBuyer_Plnr)
131 AND (C_tBuyer_Plnr is NULL OR user_name <= C_tBuyer_Plnr);
132
133 CURSOR Cur_Buyer_plnr_id(C_Buyer_Plnr VARCHAR2)IS
134 SELECT user_id
135 FROM fnd_user
136 WHERE user_name = C_Buyer_Plnr ;
137
138 BEGIN
139 retcode := 0;
140 G_fwhse_code := V_fwhse_code;
141 G_twhse_code := V_twhse_code;
142 G_whse_security := V_whse_security;
143 G_mrp_id := V_mrp_id;
144 G_schedule_id := V_schedule_id;
145 G_fplanning_class := V_fplanning_class;
146 G_tplanning_class := V_tplanning_class;
147 G_ftrans_date := V_ftrans_date;
148 G_ttrans_date := V_ttrans_date;
149 G_fitem_no := V_fitem_no;
150 G_titem_no := V_titem_no;
151 G_critical_indicator := V_critical_indicator;
152
153 IF V_fBuyer_plnr IS NULL THEN
154 select min(user_name) INTO X_fBuyer_Plnr from fnd_user;
155 ELSE
156 X_fBuyer_Plnr := V_fBuyer_plnr;
157 END IF;
158
159 IF V_tBuyer_plnr IS NULL THEN
160 select max(user_name) INTO X_tBuyer_Plnr from fnd_user;
161 ELSE
162 X_tBuyer_Plnr := V_tBuyer_plnr;
163 END IF;
164
165 --BEGIN BUG#2889706 Sastry
166 --Moved the following call from below.
167 set_where_clause;
168 --END BUG#2889706
169 OPEN Cur_Buyer_plnr(X_fBuyer_Plnr, X_tBuyer_Plnr );
170 LOOP
171 FETCH Cur_Buyer_plnr INTO G_Buyer_plnr;
172 IF Cur_Buyer_plnr%NOTFOUND THEN
173 -- CLOSE Cur_Buyer_plnr;
174 EXIT;
175 END IF;
176
177 OPEN Cur_Buyer_plnr_id(G_Buyer_Plnr);
178 FETCH Cur_Buyer_plnr_id INTO G_Buyer_plnr_id;
179 IF Cur_Buyer_plnr_id%NOTFOUND THEN
180 FND_FILE.PUT_LINE ( FND_FILE.LOG,'Bad User Code '||G_Buyer_plnr_id);
181 G_Buyer_plnr_id := -1;
182 END IF;
183 CLOSE Cur_Buyer_plnr_id;
184
185 IF G_Buyer_plnr_id > 0 THEN
186
187 G_planning_tab.delete;
188 mr_insert_header;
189
190 IF G_planning_tab.count > 0 then
191 G_no_of_reports := to_char(to_number(G_no_of_reports) + 1);
192 --BEGIN BUG#2889706 Sastry
193 --Commented the following call as it is moved above.
194 --set_where_clause;
195 --END BUG#2889706
196 X_ri_where := G_where;
197 mr_unbucket_report;
198 -- Invoke the concurrent manager from here
199 IF V_number_of_copies > 0 THEN
200 X_status := FND_REQUEST.SET_PRINT_OPTIONS(V_printer,
201 UPPER(V_user_print_style),
202 V_number_of_copies, TRUE, 'N');
203 END IF;
204 -- request is submitted to the concurrent manager
205 FND_FILE.PUT_LINE ( FND_FILE.LOG,' Submitting the Req '||sqlerrm);
206
207 X_conc_id := FND_REQUEST.SUBMIT_REQUEST('GMP','RIMR1USR','',
208 TO_CHAR(V_run_date1,'YYYY/MM/DD HH24:MI:SS'),
209 FALSE, TO_CHAR(G_matl_rep_id),
210 TO_CHAR(G_Buyer_plnr_id), X_ri_where,
211 TO_CHAR(V_run_date,'YYYY/MM/DD HH24:MI:SS'),
212 V_schedule,V_usr_orgn_code,CHR(0),'','','',
213 '','','','','','','','','','',
214 '','','','','','','','','','',
215 '','','','','','','','','','',
216 '','','','','','','','','','',
217 '','','','','','','','','','',
218 '','','','','','','','','','',
219 '','','','','','','','','','',
220 '','','','','','','','','','',
221 '','','','','','','','','','');
222
223 FND_FILE.PUT_LINE ( FND_FILE.LOG,' Submitted the Req '||sqlerrm);
224 --
225 IF X_conc_id = 0 THEN
226 G_log_text := FND_MESSAGE.GET;
227 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
228 retcode:=2;
229 exit;
230 ELSE
231 COMMIT ;
232 END IF;
233
234 END IF; /* End if Planning Tab Count */
235 END IF; /* END IF for G_Buyer_plnr_id */
236 END LOOP;
237 --
238 CLOSE Cur_buyer_plnr;
239 --
240 -- Print into the log file the information about the Reports are submitted
241 IF G_no_of_reports = 0 THEN
242 FND_MESSAGE.SET_NAME('GMP','PS_NO_TRANS');
243 G_log_text := FND_MESSAGE.GET;
244 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
245 /* Setting the Concurrent Status to Warning instead of giving Error */
246 IF (FND_CONCURRENT.SET_COMPLETION_STATUS('WARNING',NULL)) THEN
247 NULL;
248 END IF;
249 retcode :=3;
250 ELSIF G_no_of_reports = 1 THEN
251 FND_MESSAGE.SET_NAME('GMP','GMP_REPORT_SUBMITTED');
252 G_log_text := FND_MESSAGE.GET;
253 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
254
255 ELSE
256 FND_MESSAGE.SET_NAME('GMP','GMP_MULTIPLE_REPORTS_SUBMITTED');
257 G_log_text := FND_MESSAGE.GET;
258 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
259
260 END IF;
261 EXCEPTION
262 WHEN OTHERS THEN
263 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in Print mrp Activity'||sqlerrm);
264
265
266
267 END print_mrp_activity; /***** END PROCEDURE ***************************/
268
269 /*============================================================================+
270 | |
271 | PROCEDURE NAME mr_insert_header |
272 | |
273 | DESCRIPTION Procedure to insert data into ps_matl_hdr |
274 | This Procedure fetches data for the Header Table by |
275 | building the Where condition based on the User and the|
276 | Planning Classes and then inserts into the Header |
277 | Table by creating a record group |
278 | |
279 | MODIFICATION HISTORY |
280 | 12/31/02 Sridhar Gidugu ----- created |
281 | |
282 +============================================================================*/
283
284 PROCEDURE mr_insert_header IS
285
286 X_where VARCHAR2(5000) := NULL ;
287 X_row_count NUMBER;
288 X_rep_id NUMBER;
289 X_i NUMBER;
290 X_planning_class VARCHAR2(8) := NULL ;
291 X_item_id NUMBER;
292 cur_planning NUMBER;
293 BEGIN
294 -- Building of the Where clause.
295 X_where := 'SELECT i.planning_class,i.item_id '
296 ||' FROM ps_oper_pcl c, ps_plng_cls p, ic_item_mst i, '
297 ||' fnd_user f '
298 ||' WHERE c.delete_mark=0 ' ;
299
300 X_where := X_where
301 ||' AND f.user_id = :1 '
302 ||' AND f.user_id = c.user_id '
303 ||' AND c.planning_class=i.planning_class '
304 ||' AND i.planning_class=p.planning_class ';
305
306 IF (G_fplanning_class IS NOT NULL) THEN
307 X_where := X_where||' AND c.planning_class >= :2 ' ;
308 END IF;
309
310 IF (G_tplanning_class IS NOT NULL) THEN
311 X_where := X_where||' AND c.planning_class <= :3 ' ;
312 END IF;
313
314 IF (G_fitem_no IS NOT NULL) THEN
315 X_where := X_where||' AND i.item_no >= :4 ' ;
316 END IF;
317
318 IF (G_titem_no IS NOT NULL) THEN
319 X_where := X_where||' AND i.item_no <= :5 ' ;
320 END IF;
321
322 IF (G_mrp_id IS NOT NULL) THEN
323 X_where:= X_where||' AND i.item_id in (select distinct item_id '
324 ||' FROM mr_tran_tbl WHERE mrp_id= to_char(:6) )' ;
325 END IF;
326
327 cur_planning := dbms_sql.open_cursor;
328 dbms_sql.parse (cur_planning, X_where,dbms_sql.NATIVE);
329 dbms_sql.bind_variable(cur_planning, ':1', G_Buyer_plnr_id);
330
331 IF (G_fplanning_class IS NOT NULL) THEN
332 dbms_sql.bind_variable(cur_planning, ':2', G_fplanning_class);
333 END IF;
334 IF (G_tplanning_class IS NOT NULL) THEN
335 dbms_sql.bind_variable(cur_planning, ':3', G_tplanning_class);
336 END IF;
337 IF (G_fitem_no IS NOT NULL) THEN
338 dbms_sql.bind_variable(cur_planning, ':4', G_fitem_no);
339 END IF;
340 IF (G_titem_no IS NOT NULL) THEN
341 dbms_sql.bind_variable(cur_planning, ':5', G_titem_no);
342 END IF;
343 IF (G_mrp_id IS NOT NULL) THEN
344 dbms_sql.bind_variable(cur_planning, ':6', G_mrp_id);
345 END IF;
346
347 dbms_sql.define_column (cur_planning, 1, X_planning_class, 8);
348 dbms_sql.define_column (cur_planning, 2, X_item_id);
349
350 X_row_count := dbms_sql.execute_and_fetch (cur_planning);
351 IF X_row_count > 0 THEN
352 SELECT gem5_matl_rep_id_s.nextval INTO X_rep_id FROM dual;
353
354 G_matl_rep_id := X_rep_id;
355 X_i := 0;
356 LOOP
357 dbms_sql.column_value (cur_planning, 1, X_Planning_class);
358 dbms_sql.column_value (cur_planning, 2, X_Item_id);
359 X_i := X_i + 1;
360 G_planning_tab(X_i).planning_class := X_planning_class;
361 G_planning_tab(X_i).item_id := X_item_id;
362 -- Inserts the data into Header table.
363 /* this information is used for Report Header Purposes */
364
365 INSERT INTO ps_matl_hdr (matl_rep_id,planning_class,item_id)
366 VALUES(X_rep_id,X_planning_class,X_item_id);
367
368 IF dbms_sql.fetch_rows (cur_planning) <= 0 then
369 EXIT;
370 END IF;
371
372 END LOOP;
373
374 END IF;
375 dbms_sql.close_cursor (cur_planning);
376 EXCEPTION
377 WHEN OTHERS THEN
378 FND_FILE.PUT_LINE ( FND_FILE.LOG, sqlerrm);
379 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error writing to Header'||sqlerrm);
380
381 END mr_insert_header; /***** END PROCEDURE********************/
382
383 /*============================================================================+
384 | |
385 | PROCEDURE NAME set_where_clause |
386 | |
387 | DESCRIPTION Procedure to set the Where Clause for the given from |
388 | warehouse and to warehouse and from trans date and to |
389 | trans date parameters |
390 | |
391 | MODIFICATION HISTORY |
392 | 12/31/02 Sridhar Gidugu ----- created |
393 | 04/22/03 Sastry BUG#2889706 Modified the G_where by replacing to_char|
394 | with fnd_date.date_to_canonical. |
395 +============================================================================*/
396 PROCEDURE set_where_clause IS
397 BEGIN
398 IF G_fwhse_code IS NOT NULL THEN
399 G_where := G_where||' and whse_code >= '||''''||G_fwhse_code||'''';
403 G_where := G_where||' and whse_code <= '||''''||G_twhse_code||'''';
400 END IF;
401 --
402 IF G_twhse_code IS NOT NULL THEN
404 END IF;
405 IF G_ftrans_date IS NOT NULL THEN
406 --BEGIN BUG#2889706 Sastry
407 --Modified the G_where by replacing to_char with fnd_date.date_to_canonical
408 G_where := G_where||' and trans_date >= fnd_date.canonical_to_date('''||
409 fnd_date.date_to_canonical(G_ftrans_date)||''')';
410 END IF;
411 IF G_ttrans_date IS NOT NULL THEN
412 G_where := G_where||' and trans_date <= fnd_date.canonical_to_date('''||
413 fnd_date.date_to_canonical(G_ttrans_date)||''')';
414 --END BUG#2889706
415 END IF;
416 END set_where_clause; /* End of Procedure set where Clause */
417 /*============================================================================+
418 | |
419 | PROCEDURE NAME mr_unbucket_report |
420 | |
421 | DESCRIPTION Procedure to call mr_unbucket_details for items. |
422 | |
423 | MODIFICATION HISTORY |
424 | 12/31/02 Sridhar Gidugu ----- created |
425 | |
426 +============================================================================*/
427
428 PROCEDURE mr_unbucket_report IS
429
430 X_ret NUMBER;
431 X_i NUMBER := 0;
432 X_planning_class VARCHAR2(8);
433 X_item_id NUMBER;
434
435 BEGIN
436
437 IF G_planning_tab.count > 0 then
438 LOOP
439 X_i := X_i + 1;
440 EXIT WHEN X_i > G_planning_tab.count;
441 X_planning_class := G_planning_tab(X_i).planning_class;
442 X_item_id := G_planning_tab(X_i).item_id;
443 IF X_item_id IS NOT NULL THEN
444 G_doc_tab.delete;
445 mr_data_retrieval(X_item_id, X_planning_class);
446 END IF;
447 END LOOP;
448 END IF;
449
450 EXCEPTION
451 WHEN OTHERS THEN
452 FND_FILE.PUT_LINE (FND_FILE.LOG, sqlerrm);
453 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error writing to Unbucket'||sqlerrm);
454
455 END mr_unbucket_report; /***** END PROCEDURE ********************/
456
457 /*============================================================================+
458 | |
459 | PROCEDURE NAME mr_data_retrieval |
460 | |
461 | DESCRIPTION Procedure to Retrieve the Data for Unbucketed material|
462 | activity and for each item from mr_tran_tbl based on |
463 | mrp_id and warehouse list |
464 | |
465 | MODIFICATION HISTORY |
466 | 12/31/02 Sridhar Gidugu ----- created |
467 | |
468 +============================================================================*/
469
470 PROCEDURE mr_data_retrieval(V_item_id IN NUMBER,
471 V_planning_class IN VARCHAR2) IS
472 X_row_count NUMBER;
473 X_select VARCHAR2(3000);
474 gs_temp VARCHAR2(3000);
475 X_status NUMBER(5);
476 X_first_flag NUMBER(5);
477 X_doc NUMBER;
478 X_doc_type Varchar2(4);
479 X_trans_date Date;
480 X_orgn_code Varchar2(4);
481 X_doc_id NUMBER(10);
482 X_trans_qty NUMBER;
483 X_cust_vend Varchar2(32);
484 X_line_no NUMBER(10);
485 X_whse_code VARCHAR2(4);
486 X_i NUMBER(5) := 0;
487 X_date DATE;
488 X_pastdue CHAR(1) := '';
489 X_ret Number;
490
491 BEGIN
492 -- to get warehouse list.
493 IF V_item_id is NOT NULL
494 THEN
495 mr_whse_list(V_item_id) ; /* Procedure to create list valid Whses */
496 END IF;
497
498 -- to get balance
499 IF G_whse_list IS NOT NULL THEN
500 mr_get_balance(V_item_id) ; /* Procedure to get the On Hand Qty */
501 -- to get safety_stock from ic_whse_inv
502 mr_get_safety_stock(V_item_id) ; /* Get Safety Stock Details */
503 -- mr_schedule_parms;
504
505 -- Added LEXP for Lot expiry project B3219257 Rajesh Patangya 10/28/2003
506
507 X_select := ' SELECT mr.doc_type doc_type,mr.trans_date trans_date, '||
508 ' mr.orgn_code orgn_code,mr.doc_id doc_id, '||
509 ' mr.trans_qty trans_qty, '||
510 ' null cust_vend, mr.line_no line_no, mr.whse_code whse_code'||
511 ' FROM mr_tran_tbl mr'||
512 ' WHERE mrp_id = TO_CHAR(:1) ' ||
513 ' AND mr.item_id = TO_CHAR(:2) ' ||
514 ' AND mr.whse_code in ('|| G_whse_list ||')'||
515 ' AND mr.doc_type in ('||''''||'PROD'||''''||','||''''||'FPO'||''''||',
516 '||''''||'PORD'||''''||','||''''||'PREQ'||''''||',
517 '||''''||'OMSO'||''''||','||''''||'OPSO'||''''||',
518 '||''''||'PPUR'||''''||','||''''||'PPRD'||''''||',
519 '||''''||'FCST'||''''||','||''''||'PTRN'||''''||',
520 '||''''||'LEXP'||''''||','||''''||'PRCV'||''''||','||''''||'SHMT'||''''||',
521 '||''''||'PBPR'||''''||','||''''||'XFER'||''''||','||''''||'PBPO'||''''||')'||
522 ' ORDER BY 2 ASC, 5 DESC';
523
524 X_doc := dbms_sql.open_cursor;
528 dbms_sql.bind_variable(X_doc,':2',V_item_id);
525 dbms_sql.parse(X_doc, X_select,dbms_sql.NATIVE);
526
527 dbms_sql.bind_variable(X_doc,':1',G_mrp_id);
529
530 dbms_sql.define_column(X_doc, 1, X_doc_type,4);
531 dbms_sql.define_column(X_doc, 2, X_trans_date);
532 dbms_sql.define_column(X_doc, 3, X_orgn_code,4);
533 dbms_sql.define_column(X_doc, 4, X_doc_id);
534 dbms_sql.define_column(X_doc, 5, X_trans_qty);
535 dbms_sql.define_column(X_doc, 6, X_cust_vend,32);
536 dbms_sql.define_column(X_doc, 7, X_line_no);
537 dbms_sql.define_column(X_doc, 8, X_whse_code,4);
538
539
540 X_row_count := dbms_sql.execute(X_doc);
541
542 X_first_flag := 1;
543
544 --IF X_row_count > 0
545 --THEN
546 LOOP
547 X_row_count := dbms_sql.fetch_rows (X_doc);
548 IF X_row_count = 0 THEN
549 EXIT;
550 END IF;
551 X_i := X_i + 1;
552 dbms_sql.column_value(X_doc, 1, X_doc_type);
553 dbms_sql.column_value(X_doc, 2, X_trans_date);
554 dbms_sql.column_value(X_doc, 3, X_orgn_code);
555 dbms_sql.column_value(X_doc, 4, X_doc_id);
556 dbms_sql.column_value(X_doc, 5, X_trans_qty);
557 dbms_sql.column_value(X_doc, 6, X_cust_vend);
558 dbms_sql.column_value(X_doc, 7, X_line_no);
559 dbms_sql.column_value(X_doc, 8, X_whse_code);
560 --
561 G_doc_tab(X_i).doc_type := X_doc_type;
562 G_doc_type := G_doc_tab(X_i).doc_type;
563 G_doc_tab(X_i).trans_date := X_trans_date;
564 G_doc_tab(X_i).orgn_code := X_orgn_code;
565
566 -- Assigning the orgn code values for OMSO doc type
567 -- B2992073 10/28/2003 Rajesh Patangya
568
569 G_orgn_code := X_orgn_code;
570
571 G_doc_tab(X_i).doc_id := X_doc_id;
572 G_doc_id := G_doc_tab(X_i).doc_id;
573 G_doc_tab(X_i).trans_qty := X_trans_qty;
574 G_doc_tab(X_i).cust_vend := X_cust_vend; /* NULL */
575 G_doc_tab(X_i).line_no := X_line_no;
576 G_line_no := G_doc_tab(X_i).line_no;
577 G_doc_tab(X_i).whse_code := X_whse_code;
578 --
579 /*
580 dbms_output.put_line(' Doc Type '||G_doc_tab(X_i).doc_type);
581 dbms_output.put_line(' Trans Date '||G_doc_tab(X_i).trans_date);
582 dbms_output.put_line(' Orgn Code '||G_doc_tab(X_i).orgn_code);
583 dbms_output.put_line(' Doc Id '||G_doc_tab(X_i).doc_id);
584 dbms_output.put_line(' Trans Qty '||G_doc_tab(X_i).trans_qty);
585 dbms_output.put_line(' Line No '||G_doc_tab(X_i).line_no);
586 dbms_output.put_line(' Whse Code '||G_doc_tab(X_i).whse_code);
587 */
588 --
589 G_balance1 := G_balance1 + G_doc_tab(X_i).trans_qty;
590 IF X_first_flag = 1
591 THEN
592 G_balance := G_balance1 - G_doc_tab(X_i).trans_qty;
593 END IF;
594 --
595 X_first_flag := 0;
596
597 IF (nvl(G_balance1,0) < nvl(G_total_ss,0))
598 THEN
599 G_c_ind := '**';
600 -- mr_insert_details(V_item_id,V_planning_class);
601 mr_cleanup_details;
602 SELECT sysdate into X_date from dual;
603 X_ret := G_doc_tab(X_i).trans_date - X_date;
604 IF (X_ret < 0) THEN
605 X_pastdue := '*';
606 ELSE
607 X_pastdue := NULL;
608 END IF;
609 /* Insert the Transaction Data into mr_ubkt_dtl table
610 for Report to Process and show the data on the screen */
611 INSERT INTO mr_ubkt_dtl(matl_rep_id,
612 item_id,
613 planning_class,
614 whse_code,
615 start_balance,
616 past_due,
617 trans_date,
618 doc_type,
619 orgn_code,
620 doc_id,
621 doc_no,
622 trans_qty,
623 balance,
624 critical_ind,
625 cust_vend
626 )
627 VALUES(G_matl_rep_id,
628 V_item_id,
629 V_planning_class,
630 G_doc_tab(X_i).whse_code,
631 G_start_balance,
632 X_pastdue,
633 G_doc_tab(X_i).trans_date,
634 G_doc_tab(X_i).doc_type,
635 G_doc_tab(X_i).orgn_code,
636 -- nvl(G_doc_tab(i).doc_id,0),
637 nvl(G_doc_id,0),
638 G_doc_no,
639 G_doc_tab(X_i).trans_qty,
640 G_balance1,
641 G_c_ind,
642 --Begin Bug#2131275 P.Raghu
643 --G_cust_vend value is inserted instead of NULL.
647 );
644 --G_doc_tab(X_i).cust_vend
645 G_cust_vend
646 --End Bug#2131275
648 /* Insert data complete */
649 G_start_balance := 0.00;
650
651 ELSIF G_critical_indicator = 0
652 THEN
653 -- mr_insert_details(V_item_id,V_planning_class);
654
655 mr_cleanup_details;
656 SELECT sysdate into X_date from dual;
657 X_ret := G_doc_tab(X_i).trans_date - X_date;
658 IF (X_ret < 0) THEN
659 X_pastdue := '*';
660 ELSE
661 X_pastdue := NULL;
662 END IF;
663 /* Insert the Transaction Data into mr_ubkt_dtl table
664 for Report to Process and show the data on the screen */
665 INSERT INTO mr_ubkt_dtl(matl_rep_id,
666 item_id,
667 planning_class,
668 whse_code,
669 start_balance,
670 past_due,
671 trans_date,
672 doc_type,
673 orgn_code,
674 doc_id,
675 doc_no,
676 trans_qty,
677 balance,
678 critical_ind,
679 cust_vend
680 )
681 VALUES(G_matl_rep_id,
682 V_item_id,
683 V_planning_class,
684 G_doc_tab(X_i).whse_code,
685 G_start_balance,
686 X_pastdue,
687 G_doc_tab(X_i).trans_date,
688 G_doc_tab(X_i).doc_type,
689 G_doc_tab(X_i).orgn_code,
690 -- nvl(G_doc_tab(i).doc_id,0),
691 nvl(G_doc_id,0),
692 G_doc_no,
693 G_doc_tab(X_i).trans_qty,
694 G_balance1,
695 G_c_ind,
696 --Begin Bug#2131275 P.Raghu
697 --G_cust_vend value is inserted instead of NULL.
698 --G_doc_tab(X_i).cust_vend
699 G_cust_vend
700 --End Bug#2131275
701 );
702 /* Insert data complete */
703 G_start_balance := 0.00;
704 --
705 END IF;
706
707 G_c_ind := '';
708 END LOOP;
709 G_doc_tab.delete;
710 dbms_sql.close_cursor(X_doc);
711 --END IF;
712 --
713 /* if there are no transactions then that item row is deleted from
714 header table. */
715 IF X_i = 0 THEN
716 DELETE FROM ps_matl_hdr
717 WHERE item_id = V_item_id
718 AND matl_rep_id = G_matl_rep_id;
719 END IF;
720 --
721
722 END IF; /* End if for G_whse_list NOT NULL */
723
724 EXCEPTION
725 WHEN OTHERS THEN
726 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error writing to mr_ubkt_dtl'||sqlerrm);
727 END mr_data_retrieval; /***** END FUNCTION ********************/
728
729 /*============================================================================+
730 | |
731 | PROCEDURE NAME mr_whse_list |
732 | |
733 | DESCRIPTION Procedure to create the list of valid warehouses |
734 | for the given Schedule and Item, and build warehouses |
735 | list depending on number of warehouses the item is |
736 | present |
737 | |
738 | MODIFICATION HISTORY |
739 | 01/01/03 Sridhar Gidugu ----- created |
740 | 12/19/03 Kalyani Manda Bug3168907 |
741 | Modified code to look for PS_WHSE_EFF |
742 | for item/whse_item_id combination for |
743 | considering the material activity |
744
745 +============================================================================*/
746
747 PROCEDURE mr_whse_list(V_item_id NUMBER) IS
748
749 TYPE ref_cursor_typ is REF CURSOR;
750 Cur_matl_act ref_cursor_typ;
751
752 --Bug 3168907 Kalyani Manda Added the cursor.
753 Cursor Cur_get_whse_item_id ( V_item_id NUMBER) IS
754 SELECT whse_item_id
755 FROM ic_item_mst
756 WHERE item_id = V_item_id;
757
758
759 X_sel_whse_list VARCHAR2(2000);
760 X_matl_whse VARCHAR2(4);
761 X_count NUMBER(5) := 0; --3168907 assigned the default value.
762 X_fwhse_code VARCHAR2(4);
766 X_whse_item_id NUMBER;
763 X_twhse_code VARCHAR2(4);
764 --Bug 3168907 Added the variables
765 X_whse VARCHAR2(4);
767 X_whse_eff_item_id NUMBER;
768
769 BEGIN
770 G_sy_all := fnd_profile.value('SY$ALL');
771
772 IF G_fwhse_code IS NULL THEN
773 SELECT MIN(whse_code) INTO X_fwhse_code FROM ic_whse_mst;
774 ELSE
775 X_fwhse_code := G_fwhse_code;
776 END IF;
777 IF G_twhse_code IS NULL THEN
778 SELECT MAX(whse_code) INTO X_twhse_code FROM ic_whse_mst;
779 ELSE
780 X_twhse_code := G_twhse_code;
781 END IF;
782 --
783 IF G_mrp_id IS NOT NULL AND G_schedule_id IS NOT NULL THEN
784
785 --Bug 3168907 Fetch the whse_item_id for the item.
786 OPEN Cur_get_whse_item_id(V_item_id);
787 FETCH Cur_get_whse_item_id INTO X_whse_item_id;
788 CLOSE Cur_get_whse_item_id;
789
790 /* Define Cursor per whse security to select the whse code */
791 IF nvl(G_whse_security,'N') = 'N' THEN
792 --Bug 3168907 Added ps_whse_eff whs to fetch whse_item_id
793 OPEN Cur_matl_act for
794 SELECT distinct trn.whse_code, whs.whse_item_id
795 FROM mr_tran_tbl trn, ps_schd_dtl sch, ps_whse_eff whs
796 WHERE sch.schedule_id = G_schedule_id
797 AND trn.mrp_id = G_mrp_id
798 AND item_id = V_item_id
799 AND whs.whse_code = trn.whse_code
800 AND whs.plant_code = sch.orgn_code
801 AND (whs.whse_code >= X_fwhse_code
802 OR X_fwhse_code IS NULL)
803 AND (whs.whse_code <= X_twhse_code
804 OR X_twhse_code IS NULL)
805 ORDER BY 1;
806
807 ELSE
808 --Bug 3168907 Added ps_whse_eff whs to fetch whse_item_id
809 OPEN Cur_matl_act for
810 SELECT distinct trn.whse_code, whs.whse_item_id
811 FROM mr_tran_tbl trn, ps_schd_dtl sch, sy_orgn_usr org, ps_whse_eff whs
812 WHERE sch.orgn_code = org.orgn_code
813 and sch.schedule_id = G_schedule_id
814 and mrp_id = G_mrp_id
815 and item_id = V_item_id
816 and org.user_id = G_Buyer_plnr_id
817 and whs.plant_code = sch.orgn_code
818 and whs.whse_code = trn.whse_code
819 and (whs.whse_code >= X_fwhse_code
820 or X_fwhse_code IS NULL)
821 and (whs.whse_code <= X_twhse_code
822 or X_twhse_code IS NULL)
823 ORDER BY 1 ;
824 END IF;
825 --
826 FETCH Cur_matl_act INTO X_matl_whse, X_whse_eff_item_id; --Bug 3168907 Added X_whse_eff_item_id
827 IF Cur_matl_act%NOTFOUND THEN
828 X_count:=0;
829 ELSE
830 LOOP
831 --Begin Bug 3168907
832 IF X_whse_eff_item_id IS NULL OR X_whse_item_id = x_whse_eff_item_id THEN
833 IF X_count > 0 Then
834 X_sel_whse_list := X_sel_whse_list||',';
835 END IF;
836 X_sel_whse_list := X_sel_whse_list||''''||X_matl_whse||'''';
837 X_whse := X_matl_whse;
838 X_count := X_count + 1;
839 END IF;
840 FETCH Cur_matl_act INTO X_matl_whse, X_whse_eff_item_id;
841 IF Cur_matl_act%NOTFOUND THEN
842 EXIT;
843 END IF;
844 --End Bug 3168907
845 END LOOP;
846 END IF;
847 CLOSE Cur_matl_act;
848 --
849 IF X_count = 1
850 THEN
851 G_whse_code := X_whse; --3168907 Modified assignment from x_matl_whse to X_whse.
852 ELSIF X_count > 1
853 THEN
854 IF G_sy_all = 'SY$ALL'
855 THEN
856 G_whse_code := NULL;
857 ELSE
858 G_whse_code := G_sy_all;
859 END IF;
860 END IF;
861 --
862 G_num_whses := X_count;
863 G_whse_list := X_sel_whse_list;
864
865 END IF;
866 EXCEPTION
867 WHEN OTHERS THEN
868 FND_FILE.PUT_LINE ( FND_FILE.LOG, sqlerrm||'mr_whse_list');
869 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error writing to Whse List'||sqlerrm);
870 END mr_whse_list; /******** END PROCEDURE*************/
871
872 /*============================================================================+
873 | |
874 | PROCEDURE NAME mr_get_balance |
875 | |
876 | DESCRIPTION Procedure to get the Initial balance from mr_tran_tbl |
877 | for that particular Item |
878 | |
879 | MODIFICATION HISTORY |
880 | 01/01/03 Sridhar Gidugu ----- created |
881 | |
882 +============================================================================*/
883
884 PROCEDURE mr_get_balance(V_item_id NUMBER) IS
885
886 X_select1 VARCHAR2(2000) := NULL;
887 cur_balance NUMBER;
888 X_row_count NUMBER(5);
889 BEGIN
890 X_select1 :='SELECT sum(trans_qty) total'||
891 ' FROM mr_tran_tbl mr'||
892 ' WHERE mrp_id= to_char(:1) AND item_id = to_char(:2) ' ||
893 ' AND whse_code in (' || G_whse_list || ') ' ||
894 ' AND doc_type='||''''||'BAL'||''''||
895 ' group by mr.doc_type';
896
897 cur_balance := dbms_sql.open_cursor;
901 dbms_sql.bind_variable(cur_balance,':2',V_item_id);
898 dbms_sql.parse (cur_balance, X_select1,dbms_sql.NATIVE);
899
900 dbms_sql.bind_variable(cur_balance,':1',G_mrp_id);
902
903 dbms_sql.define_column (cur_balance, 1, G_on_hand1);
904 X_row_count := dbms_sql.execute(cur_balance);
905 IF dbms_sql.fetch_rows (cur_balance) > 0 then
906 dbms_sql.column_value (cur_balance, 1, G_on_hand1);
907 ELSE
908 G_on_hand1 := 0;
909 END IF;
910 dbms_sql.close_cursor (cur_balance);
911 --
912 G_balance1 := G_on_hand1;
913 G_start_balance := G_balance1;
914
915 EXCEPTION
916 WHEN OTHERS THEN
917 FND_FILE.PUT_LINE ( FND_FILE.LOG, sqlerrm);
918 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error Get Balance '||sqlerrm);
919 IF dbms_sql.is_open (cur_balance) then
920 dbms_sql.close_cursor (cur_balance);
921 END IF;
922
923 END mr_get_balance; /******** END PROCEDURE*************/
924
925 /*============================================================================+
926 | |
927 | PROCEDURE NAME mr_get_safety_stock |
928 | |
929 | DESCRIPTION Procedure to get the safety stock Information for the |
930 | Item |
931 | |
932 | MODIFICATION HISTORY |
933 | 12/31/02 Sridhar Gidugu ----- created |
934 | |
935 +============================================================================*/
936
937 PROCEDURE mr_get_safety_stock(V_item_id NUMBER) IS
938
939 CURSOR Cur_unit_safety_stock(C_item_id NUMBER) IS
940 SELECT safety_stock
941 FROM ic_whse_inv
942 WHERE item_id= C_item_id
943 AND whse_code is NULL and delete_mark=0;
944
945 X_whse_cnt NUMBER(5);
946 X_select1 VARCHAR2(2000) := NULL ;
947 X_row_count NUMBER(5);
948 cur_sstock NUMBER;
949 BEGIN
950
951 X_select1 :='SELECT sum(safety_stock) total_ss,count(*) no_ss'||
952 ' FROM ic_whse_inv'||
953 ' WHERE item_id= to_char(:1) ' ||
954 ' AND whse_code in ('|| G_whse_list ||') and delete_mark=0';
955
956 IF G_whse_list IS NOT NULL THEN
957 cur_sstock := dbms_sql.open_cursor;
958 dbms_sql.parse (cur_sstock, X_select1,dbms_sql.NATIVE);
959
960 dbms_sql.bind_variable(cur_sstock,':1',V_item_id);
961
962 dbms_sql.define_column (cur_sstock, 1, G_total_ss);
963 dbms_sql.define_column (cur_sstock, 2, G_no_safetystock);
964 X_row_count := dbms_sql.execute(cur_sstock);
965 IF dbms_sql.fetch_rows (cur_sstock) > 0 then
966 dbms_sql.column_value (cur_sstock, 1, G_total_ss);
967 dbms_sql.column_value (cur_sstock, 2, G_no_safetystock);
968 ELSE
969 G_total_ss := 0;
970 G_no_safetystock := 0;
971 END IF;
972 dbms_sql.close_cursor (cur_sstock);
973 END IF;
974
975 IF ((NVL(G_no_safetystock,0) < NVL(G_num_whses,0))) THEN
976 G_unit_ss := 0;
977 OPEN Cur_unit_safety_stock(V_item_id);
978 FETCH Cur_unit_safety_stock INTO G_unit_ss;
979 CLOSE Cur_unit_safety_stock;
980 X_whse_cnt := G_num_whses;
981
982 IF (G_whse_code <> G_sy_all)
983 THEN
984 X_whse_cnt := 1;
985 END IF;
986
987 G_total_ss := NVL(G_total_ss,0) +
988 (X_whse_cnt - NVL(G_no_safetystock,0)) * G_unit_ss;
989 END IF;
990
991 EXCEPTION
992 WHEN OTHERS THEN
993 FND_FILE.PUT_LINE ( FND_FILE.LOG, sqlerrm);
994 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error Get Safety Stock '||sqlerrm);
995 IF dbms_sql.is_open (cur_sstock) then
996 dbms_sql.close_cursor (cur_sstock);
997 END IF;
998
999 END mr_get_safety_stock; /******** END PROCEDURE*************/
1000
1001
1002
1003
1004 /*############################################################################
1005 # NAME
1006 # mr_cleanup_details
1007 # SYNOPSIS
1008 # Proc mr_cleanup_details
1009 # DESCRIPTION
1010 # This procedure adds details such as orgn_code,doc_no,cust_vend which are
1011 # retrieved from the corresponding master tables based on the
1012 # doc_type,to the item transaction rows before they are displayed
1013 # to the end user.If the transaction doc id is not order id then
1014 # the following code extracts the bol id and displays it against
1015 # the transaction for material activity inquiry. For distinguishing
1016 # the order id from bol id, the doc type will be displayed as 'OPSP'
1017 # instead of transaction doc type of 'OPSO'. Since there can be
1018 # multiple sales orders in one shipment, the customer no in such cases
1019 # will be displayed as 'MULTIPLE'.
1020 #
1021 # Sridhar Gidugu 02/01/2003 - Created
1022 # P.Raghu 12/26/03 - B2131275 - Modified Cur_vend_dtl and Cur_req_dtl cursors
1023 # to select vendor_name and suggested_vendor_name instead of
1024 # segment1 value for Customer/Vendor respectively.
1025 # Sastry 04/01/2004 - B3482123 - Replaced po_headers_all table with
1026 # po_po_supply_view so that releases are shown for 'PORD' doc_type.
1027 #############################################################################*/
1028 PROCEDURE mr_cleanup_details IS
1029 CURSOR Cur_custno IS
1030 SELECT cs.cust_no cust_no
1034
1031 FROM op_cust_mst cs, op_ordr_dtl dt
1032 WHERE dt.bol_id = G_doc_id
1033 AND dt.shipcust_id = cs.cust_id;
1035 --Bug#2131275 P.Raghu
1036 --Selecting pv.vendor_name instead of pv.segment1 value for Customer/Vendor.
1037 --BUG#3482123 Sastry Replaced po_headers_all table with po_po_supply_view.
1038 CURSOR Cur_vend_dtl IS
1039 SELECT unique mtt.orgn_code, po.po_number, SUBSTRB(vn.vendor_name,1,32)
1040 FROM po_po_supply_view po,
1041 po_vendors vn,
1042 mr_tran_tbl mtt
1043 WHERE po.po_line_location_id = mtt.doc_id
1044 AND mtt.mrp_id = G_mrp_id
1045 AND mtt.doc_id = G_doc_id
1046 AND mtt.line_no = G_line_no
1047 AND mtt.doc_type = 'PORD'
1048 AND vn.vendor_id (+) = po.vendor_id ;
1049
1050 -- PRCV, SHMT
1051 --Bug#2131275 P.Raghu
1052 --Selecting pv.vendor_name instead of pv.segment1 value for Customer/Vendor.
1053 CURSOR Cur_prcv_dtl IS
1054 SELECT unique mtt.orgn_code, prh.receipt_num, SUBSTRB(vn.vendor_name,1,32)
1055 FROM rcv_shipment_headers prh,
1056 po_vendors vn,
1057 mr_tran_tbl mtt
1058 WHERE prh.shipment_header_id = G_doc_id
1059 AND mtt.mrp_id = G_mrp_id
1060 AND mtt.line_no = G_line_no
1061 AND mtt.doc_id = G_doc_id
1062 AND mtt.doc_type in ('PRCV','SHMT')
1063 AND vn.vendor_id (+) = prh.vendor_id
1064 UNION ALL
1065 SELECT unique mtt.orgn_code, prh.segment1, SUBSTRB(vn.vendor_name,1,32)
1066 FROM po_headers_all prh,
1067 po_vendors vn,
1068 mr_tran_tbl mtt
1069 WHERE prh.po_header_id = G_doc_id
1070 AND mtt.mrp_id = G_mrp_id
1071 AND mtt.line_no = G_line_no
1072 AND mtt.doc_id = G_doc_id
1073 AND mtt.doc_type = 'PRCV'
1074 AND vn.vendor_id (+) = prh.vendor_id ;
1075
1076 CURSOR Cur_cust_dtl IS
1077 SELECT op.orgn_code, op.order_no,cs.cust_no
1078 FROM op_ordr_hdr op, op_cust_mst cs
1079 WHERE op.order_id = G_doc_id
1080 AND op.shipcust_id = cs.cust_id;
1081
1082 CURSOR Cur_order_dtl IS
1083 SELECT op.orgn_code, op.bol_no
1084 FROM op_bill_lad op
1085 WHERE op.bol_id = G_doc_id;
1086
1087 CURSOR Cur_batch_dtl is
1088 SELECT gbh.plant_code, gbh.batch_no
1089 FROM gme_batch_header gbh
1090 WHERE gbh.batch_id = G_doc_id
1091 AND gbh.delete_mark = 0;
1092
1093 -- B1159495 Rajesh Patangya
1094 CURSOR Cur_transfer_dtl is
1095 SELECT ic.orgn_code,ic.transfer_no
1096 FROM ic_xfer_mst ic
1097 WHERE ic.transfer_id = G_doc_id;
1098
1099 -- Added OMSO doc type
1100 -- B2992073 10/28/2003 Rajesh Patangya
1101 CURSOR Cur_om_order_details IS
1102 SELECT oh.order_number, sold_to_org.customer_number
1103 FROM oe_order_headers_all oh,
1104 oe_sold_to_orgs_v sold_to_org
1105 WHERE oh.header_id = G_doc_id
1106 AND oh.sold_to_org_id = sold_to_org.organization_id(+) ;
1107
1108 CURSOR Cur_purchase_dtl IS
1109 SELECT bh.orgn_code, bh.bpo_no,bd.line_no
1110 FROM po_bpos_dtl bd, po_bpos_hdr bh
1111 WHERE bd.line_id = G_doc_id
1112 AND bd.bpo_id = bh.bpo_id;
1113
1114 --Bug#2131275 P.Raghu
1115 --Selecting prl.Suggested_vendor_name instead of pv.segment1 value for Customer/Vendor.
1116 CURSOR Cur_req_dtl IS
1117 SELECT unique mtt.orgn_code, prh.segment1, SUBSTRB(prl.suggested_vendor_name,1,32)
1118 FROM po_requisition_headers prh,
1119 po_requisition_lines prl,
1120 po_vendors vn,
1121 mr_tran_tbl mtt
1122 WHERE prl.requisition_header_id = G_doc_id
1123 AND prh.requisition_header_id = prl.requisition_header_id
1124 AND mtt.mrp_id = G_mrp_id
1125 AND mtt.line_no = G_line_no
1126 AND mtt.doc_id = G_doc_id
1127 AND mtt.doc_type = 'PREQ'
1128 AND vn.vendor_id (+) = prl.vendor_id ;
1129
1130
1131 X_custno VARCHAR2(32);
1132 X_orgn_code VARCHAR2(4);
1133 X_doc_no VARCHAR2(32);
1134 X_cust_vend VARCHAR2(32);
1135 NO_MATCH_DOC EXCEPTION;
1136
1137 BEGIN
1138 X_orgn_code:=NULL;
1139 X_doc_no:=NULL;
1140 X_cust_vend:=NULL;
1141
1142 IF G_doc_type= 'PORD' THEN
1143 OPEN Cur_vend_dtl;
1144 FETCH Cur_vend_dtl INTO X_orgn_code,X_doc_no,X_cust_vend;
1145 CLOSE Cur_vend_dtl;
1146 ELSIF G_doc_type in ('PRCV','SHMT') THEN
1147 OPEN Cur_prcv_dtl;
1148 FETCH Cur_prcv_dtl INTO X_orgn_code,X_doc_no,X_cust_vend;
1149 CLOSE Cur_prcv_dtl;
1150 ELSIF G_doc_type= 'XFER' THEN
1151 OPEN Cur_transfer_dtl;
1152 FETCH Cur_transfer_dtl INTO X_orgn_code,X_doc_no;
1153 CLOSE Cur_transfer_dtl;
1154 ELSIF G_doc_type='OPSO' THEN
1155 OPEN Cur_cust_dtl;
1156 FETCH Cur_cust_dtl INTO X_orgn_code,X_doc_no,X_cust_vend;
1157 IF Cur_cust_dtl%NOTFOUND THEN
1158 CLOSE Cur_cust_dtl;
1159
1160 OPEN Cur_order_dtl;
1161 FETCH Cur_order_dtl INTO X_orgn_code,X_doc_no;
1162 CLOSE Cur_order_dtl;
1163 --
1164 OPEN Cur_custno;
1165 FETCH Cur_custno INTO X_custno;
1166 CLOSE Cur_custno;
1167 --
1168 IF Cur_order_dtl%ROWCOUNT > 1 THEN
1169 X_cust_vend := 'MULTIPLE';
1170 ELSE
1171 X_cust_vend := X_custno;
1172 END IF;
1173 --
1174 G_doc_type := 'OPSP';
1175 END IF;
1176
1177 CLOSE Cur_cust_dtl;
1178
1179 -- B2992073 10/28/2003 Rajesh Patangya
1180 ELSIF (G_doc_type = 'OMSO') THEN
1181 OPEN Cur_om_order_details;
1185 OPEN Cur_batch_dtl;
1182 FETCH Cur_om_order_details INTO X_doc_no, X_cust_vend;
1183 CLOSE Cur_om_order_details;
1184 ELSIF G_doc_type= 'PROD' OR G_doc_type = 'FPO' THEN
1186 FETCH Cur_batch_dtl INTO X_orgn_code,X_doc_no;
1187 CLOSE Cur_batch_dtl;
1188 ELSIF G_doc_type = 'PBPR' OR G_doc_type = 'PBPO' THEN
1189 OPEN Cur_purchase_dtl;
1190 FETCH Cur_purchase_dtl INTO X_orgn_code,X_doc_no,X_cust_vend;
1191 CLOSE Cur_purchase_dtl;
1192 ELSIF G_doc_type = 'PREQ' THEN
1193 OPEN Cur_req_dtl;
1194 FETCH Cur_req_dtl INTO X_orgn_code,X_doc_no, X_cust_vend;
1195 CLOSE Cur_req_dtl;
1196 ELSE
1197 G_doc_no:= NULL;
1198 G_cust_vend:=NULL;
1199 select gem5_mrp_doc_id_s.nextval into G_doc_id
1200 from dual;
1201 Raise NO_MATCH_DOC;
1202 END IF;
1203
1204 IF G_doc_type <> 'OMSO' THEN
1205 G_orgn_code:= X_orgn_code;
1206 END IF;
1207
1208 G_doc_no:= X_doc_no;
1209 G_cust_vend:= X_cust_vend;
1210 EXCEPTION
1211 WHEN NO_MATCH_DOC THEN
1212 Null;
1213 WHEN OTHERS THEN
1214 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in Cleanup details '||sqlerrm);
1215 END mr_cleanup_details;
1216
1217
1218 END GMPMRACT; /***** END PACKAGE BODY ***************************/