[Home] [Help]
PACKAGE BODY: APPS.GMPPSRP
Source
1 PACKAGE BODY GMPPSRP as
2 /* $Header: GMPPSRPB.pls 120.4 2008/01/17 06:07:56 vpedarla ship $ */
3
4 --Package Declarations
5
6 PROCEDURE ps_insert_header ;
7
8 PROCEDURE ps_bucket_report ;
9
10 FUNCTION ps_bucket_details (V_item_id IN NUMBER, V_organization_id IN NUMBER) RETURN NUMBER;
11
12 PROCEDURE ps_get_safety_stock(V_item_id NUMBER, V_organization_id NUMBER) ;
13
14 PROCEDURE get_onhand_qty(V_item_id NUMBER, V_organization_id NUMBER) ;
15
16 G_matl_rep_id NUMBER := 0;
17 G_orgnanization_id NUMBER;
18 -- G_schedule VARCHAR2(16);
19 G_schedule_id NUMBER(10);
20 G_category_set NUMBER(30);
21 G_structure_id NUMBER;
22 -- G_category_set_id NUMBER;
23 G_fcategory VARCHAR2(240);
24 G_tcategory VARCHAR2(240);
25 G_fbuyer VARCHAR2(240);
26 G_tbuyer VARCHAR2(240);
27 G_fplanner VARCHAR2(10);
28 G_tplanner VARCHAR2(10);
29 G_forg VARCHAR2(3);
30 G_torg VARCHAR2(3);
31 G_fitem VARCHAR2(240);
32 G_titem VARCHAR2(240);
33 G_on_hand1 NUMBER := 0; /* B3009969 */
34 G_on_hand2 NUMBER := 0; /* B3009969 */
35 G_total_ss NUMBER := 0;
36 G_nonnet_ind NUMBER := 0;
37 G_log_text VARCHAR2(1000);
38 G_template VARCHAR2(100);
39 G_template_locale VARCHAR2(6);
40
41
42 TYPE planning_rec_typ IS RECORD(planning_class VARCHAR2(8),item_id NUMBER);
43 TYPE planning_tab_typ IS TABLE OF planning_rec_typ INDEX BY BINARY_INTEGER;
44 G_planning_tab planning_tab_typ;
45
46 TYPE item_rec_typ IS RECORD
47 (
48 inventory_item_id NUMBER,
49 organization_id NUMBER,
50 category_id NUMBER
51 );
52 TYPE item_tab_typ IS TABLE OF item_rec_typ INDEX BY BINARY_INTEGER;
53 G_item_tab item_tab_typ;
54
55 /*============================================================================+
56 | |
57 | PROCEDURE NAME gmp_print_mps |
58 | |
59 | DESCRIPTION Procedure to submit the request for report |
60 | |
61 | MODIFICATION HISTORY |
62 | 07/14/01 Praveen Reddy ----- created |
63 | |
64 +============================================================================*/
65
66 PROCEDURE gmp_print_mps
67 ( errbuf OUT NOCOPY VARCHAR2,
68 retcode OUT NOCOPY VARCHAR2,
69 V_organization_id IN NUMBER,
70 V_schedule IN NUMBER,
71 -- V_schedule_id IN NUMBER,
72 V_Category_Set IN NUMBER,
73 V_Structure_Id IN NUMBER,
74 -- V_Category_set_id IN NUMBER,
75 V_fcategory IN VARCHAR2,
76 V_tcategory IN VARCHAR2,
77 V_fbuyer IN VARCHAR2,
78 V_tbuyer IN VARCHAR2,
79 V_fplanner IN VARCHAR2,
80 V_tplanner IN VARCHAR2,
81 V_forg IN VARCHAR2,
82 V_torg IN VARCHAR2,
83 V_fitem IN VARCHAR2,
84 V_titem IN VARCHAR2,
85 V_template IN VARCHAR2,
86 V_template_locale IN VARCHAR2
87 )IS
88
89 X_conc_id NUMBER;
90 X_status BOOLEAN;
91 X_ri_where VARCHAR2(1000);
92
93 BEGIN
94
95 retcode := 0;
96 G_orgnanization_id := V_organization_id;
97 -- G_schedule := V_schedule;
98 G_schedule_id := V_schedule;
99 G_category_set := V_Category_Set;
100 G_structure_id := V_Structure_Id;
101 -- G_category_set_id := V_category_set_id;
102 G_fcategory := V_fcategory;
103 G_tcategory := V_tcategory;
104 G_fbuyer := V_fbuyer;
105 G_tbuyer := V_tbuyer;
106 G_fplanner := V_fplanner;
107 G_tplanner := V_tplanner;
108 G_forg := V_forg;
109 G_torg := V_torg;
110 G_fitem := V_fitem;
111 G_titem := V_titem;
112 G_template := V_template;
113 G_template_locale := V_template_locale;
114
115 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Calling Modified gmp_print_mps with values ');
116 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_orgnanization_id '||to_char(G_orgnanization_id));
117 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_schedule '||to_char(G_schedule));
118 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_schedule_id '||to_char(G_schedule_id));
119 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_category_set '||G_category_set);
120 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_structure_id '||to_char(G_structure_id));
121 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_category_set_id '||to_char(G_category_set_id));
122 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_fcategory '||G_fcategory);
123 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_tcategory '||G_tcategory);
124 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_fbuyer '||G_fbuyer);
125 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_tbuyer '||G_tbuyer);
126 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_fplanner '||G_fplanner);
127 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_tplanner '||G_tplanner);
128 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_forg '||G_forg);
129 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_torg '||G_torg);
130 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_fitem '||G_fitem);
131 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_titem '||G_titem);
132 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_template '||G_template);
133 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_template_locale '||G_template_locale);
134
135 ps_insert_header;
136 IF G_item_tab.COUNT > 0 THEN
137 ps_bucket_report;
138 END IF;
139 -- G_no_of_reports := to_char(to_number(G_no_of_reports) + 1);
140 IF (G_matl_rep_id IS NOT NULL) THEN NULL;
141 DELETE
142 FROM ps_matl_hdr pmh
143 WHERE pmh.matl_rep_id = G_matl_rep_id
144 AND
145 ((pmh.inventory_item_id NOT IN (SELECT pmd1.inventory_item_id
146 FROM ps_matl_dtl pmd1
147 WHERE pmd1.matl_rep_id = G_matl_rep_id))
148 OR
149 (pmh.organization_id NOT IN (SELECT organization_id
150 FROM ps_matl_dtl pmd2
151 WHERE pmd2.inventory_item_id = pmh.inventory_item_id
152 AND pmd2.matl_rep_id = G_matl_rep_id)));
153 END IF;
154
155 ps_generate_xml;
156
157 -- Invoke the concurrent manager from here
158 /*
159 IF V_number_of_copies > 0 THEN
160 X_status := FND_REQUEST.SET_PRINT_OPTIONS(V_printer, UPPER(V_user_print_style),
161 V_number_of_copies, TRUE, 'N');
162 END IF;
163 */
164 -- request is submitted to the concurrent manager
165 /*
166 X_conc_id := FND_REQUEST.SUBMIT_REQUEST('GMP','RIPS2USR','',
167 TO_CHAR(V_run_date,'YYYY/MM/DD HH24:MI:SS'), FALSE, TO_CHAR(G_matl_rep_id),
168 TO_CHAR(G_Buyer_plnr_id), X_ri_where,chr(0),'',
169 '', '','','','',
170 '','','','','','','','','','',
171 '','','','','','','','','','',
172 '','','','','','','','','','',
173 '','','','','','','','','','',
174 '','','','','','','','','','',
175 '','','','','','','','','','',
176 '','','','','','','','','','',
177 '','','','','','','','','','',
178 '','','','','','','','','','');
179 IF X_conc_id = 0 THEN
180 G_log_text := FND_MESSAGE.GET;
181 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
182 retcode:=2;
183 EXIT;
184 ELSE
185 COMMIT ;
186 END IF;
187 */
188
189 END gmp_print_mps; /***** END PROCEDURE ***************************/
190
191 /*============================================================================+
192 | |
193 | PROCEDURE NAME ps_insert_header |
194 | |
195 | DESCRIPTION Procedure to insert data into ps_matl_hdr |
196 | |
197 | MODIFICATION HISTORY |
198 | 07/14/01 Praveen Reddy ----- created |
199 | |
200 +============================================================================*/
201
202 PROCEDURE ps_insert_header IS
203
204 x_select VARCHAR2(2000);
205 cur_item NUMBER;
206 X_item_id NUMBER;
207 X_i NUMBER;
208 X_org_id NUMBER;
209 X_category_id NUMBER;
210 X_rep_id NUMBER;
211 X_row_count NUMBER;
212
213 BEGIN
214
215 x_select := ' SELECT DISTINCT '||
216 ' msi.inventory_item_id, '||
217 ' msi.organization_id, ';
218
219 IF G_fcategory IS NOT NULL OR G_tcategory IS NOT NULL THEN
220 x_select := x_select || ' mca.category_id category_id';
221 ELSE
222 x_select := x_select || ' -999 category_id ';
223 END IF;
224 x_select := x_select || ' FROM ';
225 -- ' po_agents pag, '||
226 -- ' ps_schd_dtl psd, '||
227
228 IF G_fplanner IS NOT NULL OR G_tplanner IS NOT NULL THEN
229 x_select := x_select || ' mtl_planners mpl, ';
230 END IF;
231 IF G_fbuyer IS NOT NULL OR G_tbuyer IS NOT NULL THEN
232 x_select := x_select || ' hr_employees hem, ';
233 END IF;
234 IF G_forg IS NOT NULL OR G_torg IS NOT NULL THEN
235 x_select := x_select || ' mtl_parameters mpa, ';
236 END IF;
237 IF G_fcategory IS NOT NULL OR G_tcategory IS NOT NULL THEN
238 x_select := x_select || ' mtl_categories_kfv mca, ';
239 END IF;
240
241 x_select := x_select ||
242 ' mtl_system_items_kfv msi, '||
243 ' mtl_item_categories mic, '||
244 ' ps_schd_dtl psd, '||
245 ' mtl_category_sets mcs '||
246 ' WHERE '||
247 ' mcs.category_set_id = to_char(:category_set_id) '||
248 ' AND mcs.structure_id = to_char(:structure_id) '||
249 ' AND mic.category_set_id = mcs.category_set_id '||
250 ' AND psd.schedule_id = to_char(:schedule_id) '||
251 ' AND psd.organization_id = msi.organization_id '||
252 ' AND mic.inventory_item_id = msi.inventory_item_id '||
253 ' AND mic.organization_id = msi.organization_id ';
254
255 IF G_fcategory IS NOT NULL OR G_tcategory IS NOT NULL THEN
256 x_select := x_select || ' AND mcs.structure_id = mca.structure_id '||
257 ' AND mic.category_id = mca.category_id ';
258 IF G_fcategory IS NOT NULL THEN
259 x_select := x_select || ' AND mca.concatenated_segments >= :f_category ';
260 END IF;
261 IF G_tcategory IS NOT NULL THEN
262 x_select := x_select || ' AND mca.concatenated_segments <= :t_category ';
263 END IF;
264 END IF;
265
266 IF G_fplanner IS NOT NULL OR G_tplanner IS NOT NULL THEN
267 x_select := x_select || ' AND mpl.planner_code = msi.planner_code '||
268 ' AND mpl.organization_id = msi.organization_id ';
269 IF G_fplanner IS NOT NULL THEN
270 x_select := x_select || ' AND msi.planner_code >= :f_planner ';
271 END IF;
272 IF G_tplanner IS NOT NULL THEN
273 x_select := x_select || ' AND msi.planner_code <= :t_planner ';
274 END IF;
275 END IF;
276
277 IF G_fbuyer IS NOT NULL OR G_tbuyer IS NOT NULL THEN
278 x_select := x_select || ' AND hem.employee_id = msi.buyer_id ';
279 IF G_fbuyer IS NOT NULL THEN
280 x_select := x_select || ' AND hem.full_name >= :f_buyer ';
281 END IF;
282 IF G_tbuyer IS NOT NULL THEN
283 x_select := x_select || ' AND hem.full_name <= :t_buyer ';
284 END IF;
285 END IF;
286
287 IF G_forg IS NOT NULL OR G_torg IS NOT NULL THEN
288 x_select := x_select || ' AND mpa.organization_id = msi.organization_id ';
289 IF G_forg IS NOT NULL THEN
290 x_select := x_select || ' AND mpa.organization_code >= :f_org ';
291 END IF;
292 IF G_torg IS NOT NULL THEN
293 x_select := x_select || ' AND mpa.organization_code <= :t_org ';
294 END IF;
295 END IF;
296
297 IF G_fitem IS NOT NULL THEN
298 x_select := x_select || ' AND msi.concatenated_segments >= :f_item ';
299 END IF;
300 IF G_titem IS NOT NULL THEN
301 x_select := x_select || ' AND msi.concatenated_segments <= :t_item ';
302 END IF;
303
304 cur_item := dbms_sql.open_cursor;
305 dbms_sql.parse (cur_item, x_select,dbms_sql.NATIVE);
306
307 dbms_sql.bind_variable(cur_item, ':category_set_id', G_category_set);
308 dbms_sql.bind_variable(cur_item, ':structure_id', G_structure_id);
309 dbms_sql.bind_variable(cur_item, ':schedule_id', G_schedule_id);
310
311 IF G_fcategory IS NOT NULL THEN
312 dbms_sql.bind_variable(cur_item, ':f_category', G_fcategory);
313 END IF;
314 IF G_tcategory IS NOT NULL THEN
315 dbms_sql.bind_variable(cur_item, ':t_category', G_tcategory);
316 END IF;
317
318 IF G_fplanner IS NOT NULL THEN
319 dbms_sql.bind_variable(cur_item, ':f_planner', G_fplanner);
320 END IF;
321 IF G_tplanner IS NOT NULL THEN
322 dbms_sql.bind_variable(cur_item, ':t_planner', G_tplanner);
323 END IF;
324
325 IF G_fbuyer IS NOT NULL THEN
326 dbms_sql.bind_variable(cur_item, ':f_buyer', G_fbuyer);
327 END IF;
328 IF G_tbuyer IS NOT NULL THEN
329 dbms_sql.bind_variable(cur_item, ':t_buyer', G_tbuyer);
330 END IF;
331
332 IF G_forg IS NOT NULL THEN
333 dbms_sql.bind_variable(cur_item, ':f_org', G_forg);
334 END IF;
335 IF G_torg IS NOT NULL THEN
336 dbms_sql.bind_variable(cur_item, ':t_org', G_torg);
337 END IF;
338
339 IF G_fitem IS NOT NULL THEN
340 dbms_sql.bind_variable(cur_item, ':f_item', G_fitem);
341 END IF;
342 IF G_titem IS NOT NULL THEN
343 dbms_sql.bind_variable(cur_item, ':t_item', G_titem);
344 END IF;
345
346 dbms_sql.define_column (cur_item, 1, X_item_id);
347 dbms_sql.define_column (cur_item, 2, X_org_id);
348 dbms_sql.define_column (cur_item, 3, X_category_id);
349
350 X_row_count := dbms_sql.execute_and_fetch (cur_item);
351 IF X_row_count > 0 THEN
352 SELECT gmp_matl_rep_id_s.NEXTVAL INTO X_rep_id FROM dual;
353 G_matl_rep_id := X_rep_id;
354 X_i := 0;
355 LOOP
356 dbms_sql.column_value (cur_item, 1, X_item_id);
360 G_item_tab(X_i).inventory_item_id := X_item_id;
357 dbms_sql.column_value (cur_item, 2, X_org_id);
358 dbms_sql.column_value (cur_item, 3, X_category_id);
359 X_i := X_i + 1;
361 G_item_tab(X_i).organization_id := X_org_id;
362 G_item_tab(X_i).category_id := X_category_id;
363 -- Inserts the data into Header table.
364 INSERT INTO ps_matl_hdr (matl_rep_id,inventory_item_id,organization_id,category_id)
365 VALUES(X_rep_id,X_item_id,X_org_id,X_category_id);
366 IF dbms_sql.fetch_rows (cur_item) <= 0 THEN
367 EXIT;
368 END IF;
369 END LOOP;
370 END IF;
371 dbms_sql.close_cursor (cur_item);
372
373 EXCEPTION
374 WHEN OTHERS THEN
375 FND_FILE.PUT_LINE ( FND_FILE.LOG, sqlerrm);
376
377 END ps_insert_header; /***** END PROCEDURE********************/
378
379 /*============================================================================+
380 | |
381 | PROCEDURE NAME ps_bucket_report |
382 | |
383 | DESCRIPTION Procedure to call ps_bucket_details for items. |
384 | |
385 | MODIFICATION HISTORY |
386 | 07/14/01 Praveen Reddy ----- created |
387 | |
388 +============================================================================*/
389
390 PROCEDURE ps_bucket_report IS
391
392 X_ret NUMBER;
393 /*
394 X_i NUMBER := 0;
395 X_planning_class VARCHAR2(8);
396 X_item_id NUMBER;
397 */
398
399 BEGIN
400 IF G_item_tab.COUNT > 0 THEN
401 FOR cnt IN G_item_tab.FIRST..G_item_tab.LAST
402 LOOP
403 X_ret := ps_bucket_details(G_item_tab(cnt).inventory_item_id,
404 G_item_tab(cnt).organization_id);
405 END LOOP;
406 END IF;
407
408 /* X_ret := ps_fcst_list;
409 IF G_planning_tab.count > 0 then
410 LOOP
411 X_i := X_i + 1;
412 EXIT WHEN X_i > G_planning_tab.count;
413 X_planning_class := G_planning_tab(X_i).planning_class;
414 X_item_id := G_planning_tab(X_i).item_id;
415 IF X_item_id IS NOT NULL THEN
416 X_ret := ps_bucket_details(X_item_id, X_planning_class);
417 END IF;
418 END LOOP;
419 END IF;
420 */
421 EXCEPTION
422 WHEN OTHERS THEN
423 FND_FILE.PUT_LINE ( FND_FILE.LOG, sqlerrm);
424
425 END ps_bucket_report; /***** END PROCEDURE ********************/
426
427 /*============================================================================+
428 | |
429 | FUNCTION NAME ps_bucket_details |
430 | |
431 | DESCRIPTION Function to make a call to the stored procedure |
432 | to populate ps_matl_dtl table |
433 | |
434 | MODIFICATION HISTORY |
435 | 07/14/01 Praveen Reddy ----- created |
436 | |
437 +============================================================================*/
438
439 FUNCTION ps_bucket_details
440 (V_item_id IN NUMBER,
441 V_organization_id IN NUMBER) RETURN NUMBER IS
442
443 X_row_count NUMBER;
444 X_uom_code VARCHAR2(3);
445
446 CURSOR cur_get_item_uom IS
447 SELECT primary_uom_code FROM mtl_system_items
448 WHERE inventory_item_id = V_item_id
449 AND organization_id = V_organization_id;
450
451 BEGIN
452 -- to get warehouse list.
453 /* Call to this procedure will not be required as the item cursor itself will
454 return the valid items and organizations. */
455 /*
456 ps_whse_list(V_item_id) ;
457
458 IF G_whse_list IS NULL THEN
459 RETURN(-1);
460 END IF;
461 */
462 -- to get balance
463 -- ps_get_balance(V_item_id) ;
464 get_onhand_qty(V_item_id, V_organization_id);
465
466 -- to get safety_stock
467 ps_get_safety_stock(V_item_id, V_organization_id) ;
468
469 OPEN cur_get_item_uom;
470 FETCH cur_get_item_uom INTO X_uom_code;
471 CLOSE cur_get_item_uom;
472
473 X_row_count := pkg_gmp_bucket_data.ps_bucket_data(G_schedule_id,
474 V_item_id,
475 to_char(V_organization_id),
476 -- G_fcst_list,
477 G_on_hand1,
478 G_total_ss,
479 X_uom_code,
480 -- 1,
481 G_matl_rep_id);
482
486 WHERE inventory_item_id = V_item_id
483 -- if there are no transactions then that item row is deleted from header table.
484 IF X_row_count = 0 THEN
485 DELETE FROM ps_matl_hdr
487 AND organization_id = V_organization_id
488 AND matl_rep_id = G_matl_rep_id;
489 RETURN(-1);
490 END IF;
491 RETURN(0);
492 END ps_bucket_details; /***** END FUNCTION ********************/
493
494 /* Added this new procedure to get the primary and secondary onhand
495 qty when a list of organization_ids are mentioned. */
496
497 /****************************************************************
498 * NAME
499 * get_onhand_qty
500 * SYNOPSIS
501 * proc get_onhand_qty
502 * PARAMETERS
503 * V_item_id - Inventory_Item_Id of Item
504 * V_organization_id - Organization_id
505 * DESCRIPTION
506 * Procedure used to Retrieve onhand qtys
507 * HISTORY
508 * Namit 01Mar05 - Initial Version
509 ****************************************************************/
510
511 PROCEDURE get_onhand_qty(
512 V_item_id NUMBER,
513 V_organization_id NUMBER
514 ) IS
515
516 l_onhand1 NUMBER;
517 l_onhand2 NUMBER;
518 l_non_nettable NUMBER;
519
520 Cursor Cur_nettable_ind ( V_schedule_id NUMBER) IS
521 SELECT NVL(nonnet_ind,0)
522 FROM ps_schd_hdr
523 WHERE schedule_id = V_schedule_id;
524
525 BEGIN
526
527 OPEN Cur_nettable_ind(G_schedule_id);
528 FETCH Cur_nettable_ind INTO l_non_nettable;
529 CLOSE Cur_nettable_ind;
530
531 IF l_non_nettable = 0 THEN
532 l_non_nettable := 2;
533 END IF;
534
535 G_nonnet_ind := l_non_nettable;
536
537 inv_consigned_validations.get_planning_quantity(
538 P_INCLUDE_NONNET => l_non_nettable
539 , P_LEVEL => 1
540 , P_ORG_ID => V_organization_id
541 , P_SUBINV => NULL
542 , P_ITEM_ID => V_item_id
543 , P_GRADE_CODE => NULL
544 , X_QOH => l_onhand1
545 , X_SQOH => l_onhand2);
546
547 IF l_onhand1 IS NOT NULL THEN
548 G_on_hand1 := l_onhand1;
549 END IF;
550 IF l_onhand2 IS NOT NULL THEN
551 G_on_hand2 := l_onhand2;
552 END IF;
553
554 END get_onhand_qty;
555
556 /*============================================================================+
557 | |
558 | PROCEDURE NAME ps_get_safety_stock |
559 | |
560 | DESCRIPTION Procedure to get the safety stock details |
561 | |
562 | MODIFICATION HISTORY |
563 | 07/14/01 Praveen Reddy ----- created |
564 | |
565 +============================================================================*/
566
567 PROCEDURE ps_get_safety_stock(V_item_id NUMBER, V_organization_id NUMBER) IS
568
569 /*
570 CURSOR Cur_unit_safety_stock(C_item_id NUMBER) IS
571 SELECT safety_stock
572 FROM ic_whse_inv
573 WHERE item_id= C_item_id
574 AND whse_code is NULL and delete_mark=0;
575 */
576 X_whse_cnt NUMBER(5);
577 X_select1 VARCHAR2(2000) := NULL;
578 X_row_count NUMBER(5);
579 cur_sstock NUMBER;
580 BEGIN
581 /*
582 X_select1 :='SELECT sum(safety_stock) total_ss,count(*) no_ss'||
583 ' FROM ic_whse_inv'||
584 ' WHERE item_id = to_char(:1) ' ||
585 ' AND whse_code in ( ' || G_whse_list || ' ) ' ||
586 ' AND delete_mark=0 '||
587 ' GROUP BY item_id';
588 */
589 X_select1 :=
590 ' SELECT NVL(SUM(s1.safety_stock_quantity), 0) total_ss'||
591 ' FROM mtl_safety_stocks s1 '||
592 ' WHERE s1.organization_id = to_char(:org_id)'||
593 ' AND s1.inventory_item_id = to_char(:item_id)'||
594 ' AND (s1.effectivity_date <= SYSDATE '||
595 ' AND s1.effectivity_date >= ( '||
596 ' SELECT NVL(MAX(s2.effectivity_date), SYSDATE) '||
597 ' FROM mtl_safety_stocks s2 '||
598 ' WHERE s2.organization_id = s1.organization_id'||
599 ' AND s2.inventory_item_id = to_char(:item_id)'||
600 ' AND s2.effectivity_date <= SYSDATE)) ';
601
602
603 -- IF G_whse_list IS NOT NULL THEN
604 cur_sstock := dbms_sql.open_cursor;
605 dbms_sql.parse (cur_sstock, X_select1,dbms_sql.NATIVE);
606
607 dbms_sql.bind_variable(cur_sstock,':item_id',V_item_id);
608 dbms_sql.bind_variable(cur_sstock,':org_id',V_organization_id);
609
610 dbms_sql.define_column (cur_sstock, 1, G_total_ss);
611 -- dbms_sql.define_column (cur_sstock, 2, G_no_safetystock);
612 X_row_count := dbms_sql.EXECUTE(cur_sstock);
613 IF dbms_sql.fetch_rows (cur_sstock) > 0 THEN
614 dbms_sql.column_value (cur_sstock, 1, G_total_ss);
618 dbms_sql.close_cursor (cur_sstock);
615 -- dbms_sql.column_value (cur_sstock, 2, G_no_safetystock);
616 END IF;
617
619 EXCEPTION
620 WHEN OTHERS THEN
621 FND_FILE.PUT_LINE ( FND_FILE.LOG, sqlerrm);
622 IF dbms_sql.is_open (cur_sstock) then
623 dbms_sql.close_cursor (cur_sstock);
624 END IF;
625
626 END ps_get_safety_stock; /******** END PROCEDURE*************/
627
628 /* ***************************************************************
629 * NAME
630 * FUNCTION - ps_generate_xml
631 * PARAMETERS
632 *
633 * DESCRIPTION
634 * Procedure used to Generate XML for Bucketed Data.
635 * HISTORY
636 * Namit 31Mar05 - Initial Version
637 *************************************************************** */
638
639 PROCEDURE ps_generate_xml IS
640
641 qryCtx DBMS_XMLGEN.ctxHandle;
642 result CLOB;
643 x_stmt VARCHAR2(2000);
644 seq_stmt VARCHAR2(100);
645 x_seq_num NUMBER;
646
647 BEGIN
648 seq_stmt := NULL;
649 x_seq_num := 0;
650
651 x_stmt := ' SELECT ' ||
652 ' gmppsrp.organization_code( '||G_orgnanization_id||') master_org, ' ||
653 ' gmppsrp.schedule( '||G_schedule_id||') schedule, ' ||
654 ' gmppsrp.category_set( '||G_category_set||') category_set, ' ||
655 ''''||G_fcategory||''''||' fcategory, ' ||
656 ''''||G_tcategory||''''||' tcategory, ' ||
657 ''''||G_fbuyer||''''||' fbuyer, ' ||
658 ''''||G_tbuyer||''''||' tbuyer, ' ||
659 ''''||G_fplanner||''''||' fplanner, ' ||
660 ''''||G_tplanner||''''||' tplanner, ' ||
661 ''''||G_forg||''''||' forg, ' ||
662 ''''||G_torg||''''||' torg, ' ||
663 ''''||G_fitem||''''||' fitem, ' ||
664 ''''||G_titem||''''||' titem, ' ||
665 ' CURSOR( ' ||
666 ' SELECT ' ||
667 ' gmppsrp.item_name(pmh.inventory_item_id, pmh.organization_id) item_name, ' ||
668 ' gmppsrp.organization_code (pmh.organization_id) organization_code, ' ||
669 ' gmppsrp.planner_code (pmh.inventory_item_id, pmh.organization_id) planner_code, ' ||
670 ' gmppsrp.buyer_name (pmh.inventory_item_id, pmh.organization_id) buyer_name, ' ||
671 ' gmppsrp.onhand_qty (pmh.inventory_item_id, pmh.organization_id) onhand_qty, ' ||
672 ' gmppsrp.unit_of_measure(pmh.inventory_item_id, pmh.organization_id) primary_uom_code, ' ||
673 ' gmppsrp.category(pmh.category_id) category, ' ||
674 ' CURSOR( ' ||
675 ' SELECT pmd.* ' ||
676 ' FROM ps_matl_dtl pmd ' ||
677 ' WHERE pmd.inventory_item_id = pmh.inventory_item_id ' ||
678 ' AND pmd.organization_id = pmh.organization_id ' ||
679 ' AND pmd.matl_rep_id = pmh.matl_rep_id ' ||
680 ' ORDER BY pmd.inventory_item_id, pmd.organization_id, pmd.perd_end_date ' ||
681 ' ) DETAIL ' ||
682 ' FROM ps_matl_hdr pmh ' ||
683 ' WHERE pmh.matl_rep_id = ' ||G_matl_rep_id||
684 ' ORDER BY pmh.inventory_item_id, pmh.organization_id ' ||
685 ' ) HEADER ' ||
686 ' FROM DUAL ';
687
688 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
689
690 EXECUTE IMMEDIATE seq_stmt INTO x_seq_num ;
691
692 --DELETE FROM GMP_BUCKETED_XML_GTMP;
693 qryctx := dbms_xmlgen.newcontext(x_stmt);
694 result := DBMS_XMLGEN.getXML(qryCtx);
695 INSERT INTO gmp_bucketed_xml_temp(bckt_matl_xml_id, xml_file) VALUES(x_seq_num, result);
696 DBMS_XMLGEN.closeContext(qryCtx);
697 ps_generate_output(x_seq_num);
698
699 EXCEPTION
700 WHEN OTHERS THEN
701 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Exception in procedure ps_generate_xml :'||SQLERRM);
702
703 END ps_generate_xml;
704
705 /* ***************************************************************
706 * NAME
707 * FUNCTION - schedule
708 * PARAMETERS
709 * p_schedule_id - Schedule Id
710 * DESCRIPTION
711 * Function used to Schedule Name
712 * HISTORY
713 * Namit 31Mar05 - Initial Version
714 *************************************************************** */
715
716 FUNCTION schedule (p_schedule_id NUMBER)
717 RETURN VARCHAR2 IS
718 v_schedule_name VARCHAR2(16);
719 BEGIN
720
721 SELECT schedule INTO v_schedule_name
722 FROM ps_schd_hdr
723 WHERE schedule_id = p_schedule_id;
724
725 RETURN v_schedule_name;
726
727 EXCEPTION
728 WHEN OTHERS THEN
729 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function schedule '||SQLERRM);
730 END schedule;
731
732 /* ***************************************************************
733 * NAME
734 * FUNCTION - category_set
735 * PARAMETERS
736 * p_category_set_id - Category Set Id
737 * DESCRIPTION
738 * Function used to Retrieve Category Name
739 * HISTORY
740 * Namit 31Mar05 - Initial Version
741 *************************************************************** */
742
743 FUNCTION category_set (p_category_set_id NUMBER)
744 RETURN VARCHAR2 IS
745 v_category_set_name VARCHAR2(30);
746 BEGIN
747
748 SELECT category_set_name INTO v_category_set_name
749 FROM mtl_category_sets
750 WHERE category_set_id = p_category_set_id;
751
752 RETURN v_category_set_name;
753
754 EXCEPTION
755 WHEN OTHERS THEN
759 /* ***************************************************************
756 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function category_set '||SQLERRM);
757 END category_set;
758
760 * NAME
761 * FUNCTION - item_name
762 * PARAMETERS
763 * V_item_id - Inventory_Item_Id of Item
764 * V_organization_id - Organization_id
765 * DESCRIPTION
766 * Function used to Retrieve Item Name
767 * HISTORY
768 * Namit 31Mar05 - Initial Version
769 *************************************************************** */
770
771 FUNCTION item_name (p_inventory_item_id NUMBER, p_organization_id NUMBER)
772 RETURN VARCHAR2 IS
773 v_item_name VARCHAR2(240);
774 BEGIN
775
776 SELECT concatenated_segments INTO v_item_name
777 FROM mtl_system_items_kfv
778 WHERE inventory_item_id = p_inventory_item_id
779 AND organization_id = p_organization_id;
780
781 RETURN v_item_name;
782
783 EXCEPTION
784 WHEN OTHERS THEN
785 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function item_name '||SQLERRM);
786 END item_name;
787
788 /* ***************************************************************
789 * NAME
790 * FUNCTION - organization_code
791 * PARAMETERS
792 * V_organization_id - Organization_id
793 * DESCRIPTION
794 * Function used to Retrieve Organization Code
795 * HISTORY
796 * Namit 31Mar05 - Initial Version
797 *************************************************************** */
798
799 FUNCTION organization_code (p_organization_id NUMBER)
800 RETURN VARCHAR2 IS
801 v_org_code VARCHAR2(3);
802 BEGIN
803
804 SELECT organization_code INTO v_org_code
805 FROM mtl_parameters
806 WHERE organization_id = p_organization_id;
807
808 RETURN v_org_code;
809
810 EXCEPTION
811 WHEN OTHERS THEN
812 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function organization_code '||SQLERRM);
813 END organization_code;
814
815 /* ***************************************************************
816 * NAME
817 * FUNCTION - planner_code
818 * PARAMETERS
819 * V_item_id - Inventory_Item_Id of Item
820 * V_organization_id - Organization_id
821 * DESCRIPTION
822 * Function used to Retrieve Planner Code
823 * HISTORY
824 * Namit 31Mar05 - Initial Version
825 *************************************************************** */
826
827 FUNCTION planner_code (p_inventory_item_id NUMBER, p_organization_id NUMBER)
828 RETURN VARCHAR2 IS
829 v_planner_code VARCHAR2(10);
830 BEGIN
831
832 SELECT planner_code INTO v_planner_code
833 FROM mtl_system_items
834 WHERE inventory_item_id = p_inventory_item_id
835 AND organization_id = p_organization_id;
836
837 RETURN v_planner_code;
838
839 EXCEPTION
840 WHEN NO_DATA_FOUND THEN RETURN NULL;
841 WHEN OTHERS THEN
842 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function planner_code '||SQLERRM);
843 END planner_code;
844
845 /* ***************************************************************
846 * NAME
847 * FUNCTION - buyer_name
848 * PARAMETERS
849 * V_item_id - Inventory_Item_Id of Item
850 * V_organization_id - Organization_id
851 * DESCRIPTION
852 * Function used to Retrieve Item Buyer Name
853 * HISTORY
854 * Rajesh Patangya - Modified the SELECT B4905328
855 *************************************************************** */
856
857 FUNCTION buyer_name (p_inventory_item_id NUMBER, p_organization_id NUMBER)
858 RETURN VARCHAR2 IS
859 v_buyer_name VARCHAR2(240);
860 BEGIN
861
862 SELECT he.full_name INTO v_buyer_name
863 FROM mtl_system_items_b msi, per_people_f he
864 WHERE msi.organization_id = p_organization_id
865 AND msi.inventory_item_id = p_inventory_item_id
866 AND msi.buyer_id = he.person_id ;
867
868 RETURN v_buyer_name;
869
870 EXCEPTION
871 WHEN NO_DATA_FOUND THEN RETURN NULL;
872 WHEN OTHERS THEN
873 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function buyer_name '||SQLERRM);
874 END buyer_name;
875
876 /* ***************************************************************
877 * NAME
878 * FUNCTION - onhand_qty
879 * PARAMETERS
880 * V_item_id - Inventory_Item_Id of Item
881 * V_organization_id - Organization_id
882 * DESCRIPTION
883 * Function used to Retrieve Item onhand qty in Primary UOM
884 * HISTORY
885 * Namit 31Mar05 - Initial Version
886 *************************************************************** */
887
888 FUNCTION onhand_qty (p_inventory_item_id NUMBER, p_organization_id NUMBER)
889 RETURN NUMBER IS
890 v_onhand_qty NUMBER := 0;
891 l_onhand1 NUMBER;
892 l_onhand2 NUMBER;
893
894 BEGIN
895
896 inv_consigned_validations.get_planning_quantity(
897 P_INCLUDE_NONNET => G_nonnet_ind
898 , P_LEVEL => 1
899 , P_ORG_ID => p_organization_id
900 , P_SUBINV => NULL
901 , P_ITEM_ID => p_inventory_item_id
902 , P_GRADE_CODE => NULL
903 , X_QOH => l_onhand1
904 , X_SQOH => l_onhand2);
905
906 IF l_onhand1 IS NOT NULL THEN
907 v_onhand_qty := l_onhand1;
908 END IF;
909
910 RETURN v_onhand_qty;
911
915 END onhand_qty;
912 EXCEPTION
913 WHEN OTHERS THEN
914 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function onhand_qty '||SQLERRM);
916
917 /* ***************************************************************
918 * NAME
919 * FUNCTION - unit_of_measure
920 * PARAMETERS
921 * V_item_id - Inventory_Item_Id of Item
922 * V_organization_id - Organization_id
923 * DESCRIPTION
924 * Function used to Retrieve Primary UOM Code.
925 * HISTORY
926 * Namit 31Mar05 - Initial Version
927 *************************************************************** */
928
929 FUNCTION unit_of_measure (p_inventory_item_id NUMBER, p_organization_id NUMBER)
930 RETURN VARCHAR2 IS
931 v_uom_code VARCHAR2(3);
932 BEGIN
933
934 SELECT primary_uom_code INTO v_uom_code
935 FROM mtl_system_items
936 WHERE inventory_item_id = p_inventory_item_id
937 AND organization_id = p_organization_id;
938
939 RETURN v_uom_code;
940
941 EXCEPTION
942 WHEN OTHERS THEN
943 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function unit_of_measure '||SQLERRM);
944 END unit_of_measure;
945
946 /* ***************************************************************
947 * NAME
948 * FUNCTION - category
949 * PARAMETERS
950 * p_category_id - Category Id
951 * DESCRIPTION
952 * Function used to Retrieve Category Name
956
953 * HISTORY
954 * Namit 31Mar05 - Initial Version
955 *************************************************************** */
957 FUNCTION category (p_category_id NUMBER)
958 RETURN VARCHAR2 IS
959 v_category VARCHAR2(240);
960 BEGIN
961
962 SELECT concatenated_segments INTO v_category
963 FROM mtl_categories_kfv
964 WHERE category_id = p_category_id;
965
966 RETURN v_category;
967
968 EXCEPTION
969 WHEN NO_DATA_FOUND THEN RETURN NULL;
970 WHEN OTHERS THEN
971 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function category '||SQLERRM);
972 END category;
973
974 /* ***************************************************************
975 * NAME
976 * PROCEDURE - ps_generate_output
977 * PARAMETERS
978 * DESCRIPTION
979 * Procedure used generate the final output.
980 * HISTORY
981 * Namit 31Mar05 - Initial Version
982 *************************************************************** */
983
984 PROCEDURE ps_generate_output (
985 p_sequence_num IN NUMBER
986 )
987 IS
988
989 l_conc_id NUMBER;
990 l_req_id NUMBER;
991 l_phase VARCHAR2(20);
992 l_status_code VARCHAR2(20);
993 l_dev_phase VARCHAR2(20);
994 l_dev_status VARCHAR2(20);
995 l_message VARCHAR2(20);
996 l_status BOOLEAN;
997
998
999 BEGIN
1000
1001 l_conc_id := FND_REQUEST.SUBMIT_REQUEST('GMP','GMPBCKT','', '',FALSE,
1002 p_sequence_num, chr(0),'','','','','','','','','','','',
1003 '','','','','','','','','','','','','','','',
1004 '','','','','','','','','','',
1005 '','','','','','','','','','',
1006 '','','','','','','','','','',
1007 '','','','','','','','','','',
1008 '','','','','','','','','','',
1009 '','','','','','','','','','',
1010 '','','','','','','','','','');
1011
1012 IF l_conc_id = 0 THEN
1013 G_log_text := FND_MESSAGE.GET;
1014 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
1015 ELSE
1016 COMMIT ;
1017 END IF;
1018
1019 IF l_conc_id <> 0 THEN
1020
1021 l_status := fnd_concurrent.WAIT_FOR_REQUEST
1022 (
1023 REQUEST_ID => l_conc_id,
1024 INTERVAL => 30,
1025 MAX_WAIT => 900,
1026 PHASE => l_phase,
1027 STATUS => l_status_code,
1028 DEV_PHASE => l_dev_phase,
1029 DEV_STATUS => l_dev_status,
1030 MESSAGE => l_message
1031 );
1032
1033 DELETE FROM gmp_bucketed_xml_temp WHERE bckt_matl_xml_id = p_sequence_num;
1034
1035 /* Bug: 6609251 Vpedarla added a NULL parameters for the submition of the FND request for XDOREPPB */
1036 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
1037 l_conc_id,554,G_template,
1038 G_template_locale,'N','','','','','','','','',
1039 '','','','','','','','','','','','','','','',
1040 '','','','','','','','','','',
1041 '','','','','','','','','','',
1042 '','','','','','','','','','',
1043 '','','','','','','','','','',
1044 '','','','','','','','','','',
1045 '','','','','','','','','','',
1046 '','','','','','','','','','');
1047 END IF;
1048
1049
1050 EXCEPTION
1051 WHEN OTHERS THEN
1052 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Exception in procedure ps_generate_output '||SQLERRM);
1053 END ps_generate_output;
1054
1055 /* ***************************************************************
1056 * NAME
1057 * PROCEDURE - xml_transfer
1058 * PARAMETERS
1059 * DESCRIPTION
1060 * Procedure used provide the XML as output of the concurrent program.
1061 * HISTORY
1062 * Namit 31Mar05 - Initial Version
1063 *************************************************************** */
1064
1065 PROCEDURE xml_transfer (
1066 errbuf OUT NOCOPY VARCHAR2,
1067 retcode OUT NOCOPY VARCHAR2,
1068 p_sequence_num IN NUMBER
1069 )IS
1070
1071 l_file CLOB;
1072 file_varchar2 VARCHAR2(10);
1073 l_len NUMBER;
1074 l_limit NUMBER;
1075
1076 BEGIN
1077
1078 SELECT xml_file INTO l_file
1079 FROM gmp_bucketed_xml_temp
1080 WHERE bckt_matl_xml_id = p_sequence_num;
1081 l_limit:= 1;
1082
1083 l_len := DBMS_LOB.GETLENGTH (l_file);
1084 LOOP
1085 IF l_len > l_limit THEN
1086 file_varchar2 := DBMS_LOB.SUBSTR (l_file,10,l_limit);
1087 FND_FILE.PUT(FND_FILE.OUTPUT,file_varchar2);
1088 FND_FILE.PUT(FND_FILE.LOG, file_varchar2);
1089 file_varchar2 := NULL;
1090 l_limit:= l_limit + 10;
1091 ELSE
1092 file_varchar2 := DBMS_LOB.SUBSTR (l_file,10,l_limit);
1093 FND_FILE.PUT(FND_FILE.OUTPUT, file_varchar2);
1094 FND_FILE.PUT(FND_FILE.LOG,file_varchar2);
1095 file_varchar2 := NULL;
1096 EXIT;
1097 END IF;
1098 END LOOP;
1099 EXCEPTION
1100 WHEN OTHERS THEN
1101 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Exception in procedure xml_transfer '||SQLERRM);
1102 END xml_transfer;
1103
1104 END GMPPSRP; /***** END PACKAGE BODY ***************************/