[Home] [Help]
PACKAGE BODY: APPS.GMPPSRP
Source
1 PACKAGE BODY GMPPSRP as
2 /* $Header: GMPPSRPB.pls 120.6.12020000.1 2012/06/27 15:08:03 appldev 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);
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;
360 G_item_tab(X_i).inventory_item_id := X_item_id;
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
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
486 WHERE inventory_item_id = V_item_id
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);
615 -- dbms_sql.column_value (cur_sstock, 2, G_no_safetystock);
616 END IF;
617
618 dbms_sql.close_cursor (cur_sstock);
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(25000);
644 seq_stmt VARCHAR2(200);
645 x_seq_num NUMBER;
646 l_encoding VARCHAR2(20); /* B7481907 */
647 l_xml_header VARCHAR2(100); /* B7481907 */
648 l_offset PLS_INTEGER; /* B7481907 */
649 temp_clob CLOB; /* B7481907 */
650 len PLS_INTEGER; /* B7481907 */
651
652 BEGIN
653
654 -- B7481907 Rajesh Patangya starts
655 -- The following line of code ensures that XML data
656 -- generated here uses the right encoding
657 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
658 l_xml_header := '<?xml version="1.0" encoding="'|| l_encoding ||'"?>';
659 FND_FILE.PUT_LINE ( FND_FILE.LOG,'l_xml_header - '||l_xml_header);
660 -- B7481907 Rajesh Patangya starts
661
662 seq_stmt := NULL;
663 x_seq_num := 0;
664
665 x_stmt := ' SELECT ' ||
666 ' gmppsrp.organization_code( '||G_orgnanization_id||') master_org, ' ||
667 ' gmppsrp.schedule( '||G_schedule_id||') schedule, ' ||
668 ' gmppsrp.category_set( '||G_category_set||') category_set, ' ||
669 ''''||G_fcategory||''''||' fcategory, ' ||
670 ''''||G_tcategory||''''||' tcategory, ' ||
671 ''''||G_fbuyer||''''||' fbuyer, ' ||
672 ''''||G_tbuyer||''''||' tbuyer, ' ||
673 ''''||G_fplanner||''''||' fplanner, ' ||
674 ''''||G_tplanner||''''||' tplanner, ' ||
675 ''''||G_forg||''''||' forg, ' ||
676 ''''||G_torg||''''||' torg, ' ||
677 ''''||G_fitem||''''||' fitem, ' ||
678 ''''||G_titem||''''||' titem, ' ||
679 ' CURSOR( ' ||
680 ' SELECT ' ||
681 ' gmppsrp.item_name(pmh.inventory_item_id, pmh.organization_id) item_name, ' ||
682 ' gmppsrp.organization_code (pmh.organization_id) organization_code, ' ||
683 ' gmppsrp.planner_code (pmh.inventory_item_id, pmh.organization_id) planner_code, ' ||
684 ' gmppsrp.buyer_name (pmh.inventory_item_id, pmh.organization_id) buyer_name, ' ||
685 ' gmppsrp.onhand_qty (pmh.inventory_item_id, pmh.organization_id) onhand_qty, ' ||
686 ' gmppsrp.unit_of_measure(pmh.inventory_item_id, pmh.organization_id) primary_uom_code, ' ||
687 ' gmppsrp.category(pmh.category_id) category, ' ||
688 ' CURSOR( ' ||
689 ' SELECT pmd.* ' ||
690 ' FROM ps_matl_dtl pmd ' ||
691 ' WHERE pmd.inventory_item_id = pmh.inventory_item_id ' ||
692 ' AND pmd.organization_id = pmh.organization_id ' ||
693 ' AND pmd.matl_rep_id = pmh.matl_rep_id ' ||
694 ' ORDER BY pmd.inventory_item_id, pmd.organization_id, pmd.perd_end_date ' ||
695 ' ) DETAIL ' ||
696 ' FROM ps_matl_hdr pmh ' ||
697 ' WHERE pmh.matl_rep_id = ' ||G_matl_rep_id||
698 ' ORDER BY pmh.inventory_item_id, pmh.organization_id ' ||
699 ' ) HEADER ' ||
700 ' FROM DUAL ';
701
702 -- DELETE FROM GMP_BUCKETED_XML_GTMP;
703 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, x_stmt);
704
705 -- B7481907 Rajesh Patangya starts
706 DBMS_LOB.createtemporary(temp_clob, TRUE);
707 DBMS_LOB.createtemporary(result, TRUE);
708
709 qryctx := dbms_xmlgen.newcontext(x_stmt);
710
711 -- generate XML data
712 DBMS_XMLGEN.getXML (qryctx, temp_clob, DBMS_XMLGEN.none);
713 l_offset := DBMS_LOB.INSTR (lob_loc => temp_clob,
714 pattern => '>',
715 offset => 1,
716 nth => 1);
717 FND_FILE.PUT_LINE ( FND_FILE.LOG,'l_offset - '||l_offset);
718
719 -- Remove the header
720 DBMS_LOB.erase (temp_clob, l_offset,1);
721
722 -- The following line of code ensures that XML data
723 -- generated here uses the right encoding
724 DBMS_LOB.writeappend (result, length(l_xml_header), l_xml_header);
725
726 -- Append the rest to xml output
727 DBMS_LOB.append (result, temp_clob);
728
729 -- close context and free memory
730 DBMS_XMLGEN.closeContext(qryctx);
731 DBMS_LOB.FREETEMPORARY (temp_clob);
732 -- B7481907 Rajesh Patangya Ends
733
734 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
735 EXECUTE IMMEDIATE seq_stmt INTO x_seq_num ;
736 INSERT INTO gmp_bucketed_xml_temp(bckt_matl_xml_id, xml_file) VALUES(x_seq_num, result);
737 ps_generate_output(x_seq_num);
738
739 EXCEPTION
740 WHEN OTHERS THEN
741 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Exception in procedure ps_generate_xml :'||SQLERRM);
742
743 END ps_generate_xml;
744
745 /* ***************************************************************
746 * NAME
747 * FUNCTION - schedule
748 * PARAMETERS
749 * p_schedule_id - Schedule Id
750 * DESCRIPTION
751 * Function used to Schedule Name
752 * HISTORY
753 * Namit 31Mar05 - Initial Version
754 *************************************************************** */
755
756 FUNCTION schedule (p_schedule_id NUMBER)
757 RETURN VARCHAR2 IS
758 v_schedule_name VARCHAR2(16);
759 BEGIN
760
761 SELECT schedule INTO v_schedule_name
762 FROM ps_schd_hdr
763 WHERE schedule_id = p_schedule_id;
764
765 RETURN v_schedule_name;
766
767 EXCEPTION
768 WHEN OTHERS THEN
769 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function schedule '||SQLERRM);
770 END schedule;
771
772 /* ***************************************************************
773 * NAME
774 * FUNCTION - category_set
775 * PARAMETERS
776 * p_category_set_id - Category Set Id
777 * DESCRIPTION
778 * Function used to Retrieve Category Name
779 * HISTORY
780 * Namit 31Mar05 - Initial Version
781 *************************************************************** */
782
783 FUNCTION category_set (p_category_set_id NUMBER)
784 RETURN VARCHAR2 IS
785 v_category_set_name VARCHAR2(30);
786 BEGIN
787
788 SELECT category_set_name INTO v_category_set_name
789 FROM mtl_category_sets
790 WHERE category_set_id = p_category_set_id;
791
792 RETURN v_category_set_name;
793
794 EXCEPTION
795 WHEN OTHERS THEN
796 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function category_set '||SQLERRM);
797 END category_set;
798
799 /* ***************************************************************
800 * NAME
801 * FUNCTION - item_name
802 * PARAMETERS
803 * V_item_id - Inventory_Item_Id of Item
804 * V_organization_id - Organization_id
805 * DESCRIPTION
806 * Function used to Retrieve Item Name
807 * HISTORY
808 * Namit 31Mar05 - Initial Version
809 *************************************************************** */
810
811 FUNCTION item_name (p_inventory_item_id NUMBER, p_organization_id NUMBER)
812 RETURN VARCHAR2 IS
813 v_item_name VARCHAR2(240);
814 BEGIN
815
816 SELECT concatenated_segments INTO v_item_name
817 FROM mtl_system_items_kfv
818 WHERE inventory_item_id = p_inventory_item_id
819 AND organization_id = p_organization_id;
820
821 RETURN v_item_name;
822
823 EXCEPTION
824 WHEN OTHERS THEN
825 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function item_name '||SQLERRM);
826 END item_name;
827
828 /* ***************************************************************
829 * NAME
830 * FUNCTION - organization_code
831 * PARAMETERS
832 * V_organization_id - Organization_id
833 * DESCRIPTION
834 * Function used to Retrieve Organization Code
835 * HISTORY
836 * Namit 31Mar05 - Initial Version
837 *************************************************************** */
838
839 FUNCTION organization_code (p_organization_id NUMBER)
840 RETURN VARCHAR2 IS
841 v_org_code VARCHAR2(3);
842 BEGIN
843
844 SELECT organization_code INTO v_org_code
845 FROM mtl_parameters
846 WHERE organization_id = p_organization_id;
847
848 RETURN v_org_code;
849
850 EXCEPTION
851 WHEN OTHERS THEN
852 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function organization_code '||SQLERRM);
853 END organization_code;
854
855 /* ***************************************************************
856 * NAME
857 * FUNCTION - planner_code
858 * PARAMETERS
859 * V_item_id - Inventory_Item_Id of Item
860 * V_organization_id - Organization_id
861 * DESCRIPTION
862 * Function used to Retrieve Planner Code
863 * HISTORY
864 * Namit 31Mar05 - Initial Version
865 *************************************************************** */
866
867 FUNCTION planner_code (p_inventory_item_id NUMBER, p_organization_id NUMBER)
868 RETURN VARCHAR2 IS
869 v_planner_code VARCHAR2(10);
870 BEGIN
871
872 SELECT planner_code INTO v_planner_code
873 FROM mtl_system_items
874 WHERE inventory_item_id = p_inventory_item_id
875 AND organization_id = p_organization_id;
876
877 RETURN v_planner_code;
878
879 EXCEPTION
880 WHEN NO_DATA_FOUND THEN RETURN NULL;
881 WHEN OTHERS THEN
882 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function planner_code '||SQLERRM);
883 END planner_code;
884
885 /* ***************************************************************
886 * NAME
887 * FUNCTION - buyer_name
888 * PARAMETERS
889 * V_item_id - Inventory_Item_Id of Item
890 * V_organization_id - Organization_id
891 * DESCRIPTION
892 * Function used to Retrieve Item Buyer Name
893 * HISTORY
894 * Rajesh Patangya - Modified the SELECT B4905328
895 *************************************************************** */
896
897 FUNCTION buyer_name (p_inventory_item_id NUMBER, p_organization_id NUMBER)
898 RETURN VARCHAR2 IS
899 v_buyer_name VARCHAR2(240);
900 BEGIN
901
902 SELECT he.full_name INTO v_buyer_name
903 FROM mtl_system_items_b msi, per_people_f he
904 WHERE msi.organization_id = p_organization_id
905 AND msi.inventory_item_id = p_inventory_item_id
906 AND msi.buyer_id = he.person_id ;
907
908 RETURN v_buyer_name;
909
910 EXCEPTION
911 WHEN NO_DATA_FOUND THEN RETURN NULL;
912 WHEN OTHERS THEN
913 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function buyer_name '||SQLERRM);
914 END buyer_name;
915
916 /* ***************************************************************
917 * NAME
918 * FUNCTION - onhand_qty
919 * PARAMETERS
920 * V_item_id - Inventory_Item_Id of Item
921 * V_organization_id - Organization_id
922 * DESCRIPTION
923 * Function used to Retrieve Item onhand qty in Primary UOM
924 * HISTORY
925 * Namit 31Mar05 - Initial Version
926 *************************************************************** */
927
928 FUNCTION onhand_qty (p_inventory_item_id NUMBER, p_organization_id NUMBER)
929 RETURN NUMBER IS
930 v_onhand_qty NUMBER := 0;
931 l_onhand1 NUMBER;
932 l_onhand2 NUMBER;
933
934 BEGIN
935
936 inv_consigned_validations.get_planning_quantity(
937 P_INCLUDE_NONNET => G_nonnet_ind
938 , P_LEVEL => 1
939 , P_ORG_ID => p_organization_id
940 , P_SUBINV => NULL
941 , P_ITEM_ID => p_inventory_item_id
942 , P_GRADE_CODE => NULL
943 , X_QOH => l_onhand1
944 , X_SQOH => l_onhand2);
945
946 IF l_onhand1 IS NOT NULL THEN
947 v_onhand_qty := l_onhand1;
948 END IF;
949
950 RETURN v_onhand_qty;
951
952 EXCEPTION
953 WHEN OTHERS THEN
954 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function onhand_qty '||SQLERRM);
955 END onhand_qty;
956
957 /* ***************************************************************
958 * NAME
959 * FUNCTION - unit_of_measure
960 * PARAMETERS
961 * V_item_id - Inventory_Item_Id of Item
962 * V_organization_id - Organization_id
963 * DESCRIPTION
964 * Function used to Retrieve Primary UOM Code.
965 * HISTORY
966 * Namit 31Mar05 - Initial Version
967 *************************************************************** */
968
969 FUNCTION unit_of_measure (p_inventory_item_id NUMBER, p_organization_id NUMBER)
970 RETURN VARCHAR2 IS
971 v_uom_code VARCHAR2(3);
972 BEGIN
973
974 SELECT primary_uom_code INTO v_uom_code
975 FROM mtl_system_items
976 WHERE inventory_item_id = p_inventory_item_id
977 AND organization_id = p_organization_id;
978
979 RETURN v_uom_code;
980
981 EXCEPTION
982 WHEN OTHERS THEN
983 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function unit_of_measure '||SQLERRM);
984 END unit_of_measure;
985
986 /* ***************************************************************
987 * NAME
988 * FUNCTION - category
989 * PARAMETERS
990 * p_category_id - Category Id
991 * DESCRIPTION
992 * Function used to Retrieve Category Name
993 * HISTORY
994 * Namit 31Mar05 - Initial Version
995 *************************************************************** */
996
997 FUNCTION category (p_category_id NUMBER)
998 RETURN VARCHAR2 IS
999 v_category VARCHAR2(240);
1000 BEGIN
1001
1002 SELECT concatenated_segments INTO v_category
1003 FROM mtl_categories_kfv
1004 WHERE category_id = p_category_id;
1005
1006 RETURN v_category;
1007
1008 EXCEPTION
1009 WHEN NO_DATA_FOUND THEN RETURN NULL;
1010 WHEN OTHERS THEN
1011 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function category '||SQLERRM);
1012 END category;
1013
1014 /* ***************************************************************
1015 * NAME
1016 * PROCEDURE - ps_generate_output
1017 * PARAMETERS
1018 * DESCRIPTION
1019 * Procedure used generate the final output.
1020 * HISTORY
1021 * Namit 31Mar05 - Initial Version
1022 *************************************************************** */
1023
1024 PROCEDURE ps_generate_output (
1025 p_sequence_num IN NUMBER
1026 )
1027 IS
1028
1029 l_conc_id NUMBER;
1030 l_req_id NUMBER;
1031 l_phase VARCHAR2(20);
1032 l_status_code VARCHAR2(20);
1033 l_dev_phase VARCHAR2(20);
1034 l_dev_status VARCHAR2(20);
1035 l_message VARCHAR2(20);
1036 l_status BOOLEAN;
1037
1038
1039 BEGIN
1040
1041 l_conc_id := FND_REQUEST.SUBMIT_REQUEST('GMP','GMPBCKT','', '',FALSE,
1042 p_sequence_num, chr(0),'','','','','','','','','','','',
1043 '','','','','','','','','','','','','','','',
1044 '','','','','','','','','','',
1045 '','','','','','','','','','',
1046 '','','','','','','','','','',
1047 '','','','','','','','','','',
1048 '','','','','','','','','','',
1049 '','','','','','','','','','',
1050 '','','','','','','','','','');
1051
1052 IF l_conc_id = 0 THEN
1053 G_log_text := FND_MESSAGE.GET;
1054 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
1055 ELSE
1056 COMMIT ;
1057 END IF;
1058
1059 IF l_conc_id <> 0 THEN
1060
1061 l_status := fnd_concurrent.WAIT_FOR_REQUEST
1062 (
1063 REQUEST_ID => l_conc_id,
1064 INTERVAL => 30,
1065 MAX_WAIT => 900,
1066 PHASE => l_phase,
1067 STATUS => l_status_code,
1068 DEV_PHASE => l_dev_phase,
1069 DEV_STATUS => l_dev_status,
1070 MESSAGE => l_message
1071 );
1072
1073 DELETE FROM gmp_bucketed_xml_temp WHERE bckt_matl_xml_id = p_sequence_num;
1074
1075 /* Bug: 6609251 Vpedarla added a NULL parameters for the submition of the FND request for XDOREPPB */
1076 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
1077 l_conc_id,554,G_template,
1078 G_template_locale,'N','','','','','','','','',
1079 '','','','','','','','','','','','','','','',
1080 '','','','','','','','','','',
1081 '','','','','','','','','','',
1082 '','','','','','','','','','',
1083 '','','','','','','','','','',
1084 '','','','','','','','','','',
1085 '','','','','','','','','','',
1086 '','','','','','','','','','');
1087 END IF;
1088
1089
1090 EXCEPTION
1091 WHEN OTHERS THEN
1092 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Exception in procedure ps_generate_output '||SQLERRM);
1093 END ps_generate_output;
1094
1095 /* ***************************************************************
1096 * NAME
1097 * PROCEDURE - xml_transfer
1098 * PARAMETERS
1099 * DESCRIPTION
1100 * Procedure used provide the XML as output of the concurrent program.
1101 * HISTORY
1102 * Namit 31Mar05 - Initial Version
1103 * Bug 9094869 Vpedarla Increased size of file_varchar2 to 1000.
1104 *************************************************************** */
1105
1106 PROCEDURE xml_transfer (
1107 errbuf OUT NOCOPY VARCHAR2,
1108 retcode OUT NOCOPY VARCHAR2,
1109 p_sequence_num IN NUMBER
1110 )IS
1111
1112 l_file CLOB;
1113 file_varchar2 VARCHAR2(1000);
1114 l_len NUMBER;
1115 l_limit NUMBER;
1116
1117 BEGIN
1118
1119 SELECT xml_file INTO l_file
1120 FROM gmp_bucketed_xml_temp
1121 WHERE bckt_matl_xml_id = p_sequence_num;
1122 l_limit:= 1;
1123
1124 l_len := DBMS_LOB.GETLENGTH (l_file);
1125 LOOP
1126 IF l_len > l_limit THEN
1127 file_varchar2 := DBMS_LOB.SUBSTR (l_file,10,l_limit);
1128 FND_FILE.PUT(FND_FILE.OUTPUT,file_varchar2);
1129 FND_FILE.PUT(FND_FILE.LOG, file_varchar2);
1130 file_varchar2 := NULL;
1131 l_limit:= l_limit + 10;
1132 ELSE
1133 file_varchar2 := DBMS_LOB.SUBSTR (l_file,10,l_limit);
1134 FND_FILE.PUT(FND_FILE.OUTPUT, file_varchar2);
1135 FND_FILE.PUT(FND_FILE.LOG,file_varchar2);
1136 file_varchar2 := NULL;
1137 EXIT;
1138 END IF;
1139 END LOOP;
1140 EXCEPTION
1141 WHEN OTHERS THEN
1142 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Exception in procedure xml_transfer '||SQLERRM);
1143 END xml_transfer;
1144
1145 END GMPPSRP; /***** END PACKAGE BODY ***************************/