[Home] [Help]
PACKAGE BODY: APPS.GMPMPACT
Source
1 PACKAGE BODY GMPMPACT AS
2 /* $Header: GMPMPACB.pls 120.10 2012/01/02 13:32:39 vkinduri ship $ */
3
4 --Package Declarations
5
6 Procedure insert_header_data;
7
8 --Procedure set_where_clause;
9
10
11 Procedure rips1usr_unbucket_report;
12
13 --Procedure ps_whse_list(V_item_id NUMBER) ;
14
15 Procedure ps_data_retrieval (V_item_id IN NUMBER, V_organization_id IN NUMBER);
16
17 Procedure pscommon_safety_stock (V_item_id IN NUMBER, V_organization_id IN NUMBER);
18
19 Procedure cleanup_details;
20
21 Procedure get_onhand_qty(V_item_id IN NUMBER, V_organization_id IN NUMBER);
22
23
24 G_matl_rep_id NUMBER := 0;
25 G_orgnanization_id NUMBER;
26 -- G_schedule VARCHAR2(16);
27 G_schedule_id NUMBER(10);
28 G_structure_id NUMBER;
29 G_category_set_id NUMBER;
30 G_fcategory VARCHAR2(240);
31 G_tcategory VARCHAR2(240);
32 G_fbuyer VARCHAR2(240);
33 G_tbuyer VARCHAR2(240);
34 G_fplanner VARCHAR2(10);
35 G_tplanner VARCHAR2(10);
36 G_forg VARCHAR2(3);
37 G_torg VARCHAR2(3);
38 G_fitem VARCHAR2(240);
39 G_titem VARCHAR2(240);
40 G_on_hand1 NUMBER := 0; /* B3009969 */
41 G_on_hand2 NUMBER := 0; /* B3009969 */
42 G_total_ss NUMBER := 0;
43 G_nonnet_ind NUMBER := 0;
44 G_ftrans_date DATE;
45 G_ttrans_date DATE;
46 G_critical_indicator NUMBER := 0;
47 G_start_balance NUMBER;
48 G_log_text VARCHAR2(1000);
49 G_doc_id NUMBER;
50 G_doc_type VARCHAR2(4);
51 G_tranline_id NUMBER;
52 G_c_ind VARCHAR2(5);
53 G_cust_vend VARCHAR2(32);
54 G_template VARCHAR2(100);
55 G_template_locale VARCHAR2(6);
56
57 TYPE item_rec_typ IS RECORD
58 (
59 inventory_item_id NUMBER,
60 organization_id NUMBER,
61 category_id NUMBER
62 );
63 TYPE item_tab_typ IS TABLE OF item_rec_typ INDEX BY BINARY_INTEGER;
64 G_item_tab item_tab_typ;
65
66
67 TYPE doc_typ is RECORD( trans_date Date,
68 doc_type VARCHAR2(4),
69 doc_id NUMBER(10),
70 trans_qty NUMBER,
71 trans_qty2 NUMBER,
72 line_id NUMBER,
73 org_code VARCHAR2(3)
74 );
75
76 TYPE doc_tab_typ IS TABLE OF doc_typ INDEX BY BINARY_INTEGER;
77 G_doc_tab doc_tab_typ;
78
79 /*============================================================================+
80 | |
81 | PROCEDURE NAME print_mps_activity |
82 | |
83 | DESCRIPTION Procedure to submit the request for report |
84 | |
85 | MODIFICATION HISTORY |
86 | 05/03/04 Rameshwar Bug#3543259 |
87 | Moved the code from form to Package for performance |
88 | issues. |
89 +============================================================================*/
90
91 PROCEDURE print_mps_activity
92 (errbuf OUT NOCOPY VARCHAR2,
93 retcode OUT NOCOPY VARCHAR2,
94 V_organization_id IN NUMBER,
95 V_schedule IN NUMBER,
96 -- V_schedule_id IN NUMBER,
97 V_Category_Set_id IN NUMBER,
98 V_Structure_Id IN NUMBER,
99 V_fcategory IN VARCHAR2,
100 V_tcategory IN VARCHAR2,
101 V_fbuyer IN VARCHAR2,
102 V_tbuyer IN VARCHAR2,
103 V_fplanner IN VARCHAR2,
104 V_tplanner IN VARCHAR2,
105 V_forg IN VARCHAR2,
106 V_torg IN VARCHAR2,
107 V_fitem IN VARCHAR2,
108 V_titem IN VARCHAR2,
109 V_ftrans_date IN VARCHAR2,
110 V_ttrans_date IN VARCHAR2,
111 /* V_ftrans_date IN DATE,
112 V_ttrans_date IN DATE, */
113 V_critical_indicator IN NUMBER,
114 V_template IN VARCHAR2,
115 V_template_locale IN VARCHAR2
116 ) IS
117
118 --local Variable declaration
119 X_status BOOLEAN;
120 X_conc_id NUMBER;
121 X_fBuyer_Plnr VARCHAR2(100);
122 X_tBuyer_Plnr VARCHAR2(100);
123 X_Buyer_plnr VARCHAR2(100);
124 X_no_of_reports NUMBER;
125 X_ri_where VARCHAR2(1000);
126
127 BEGIN
128
129 retcode := 0;
130 G_orgnanization_id := V_organization_id;
131 -- G_schedule := V_schedule;
132 G_schedule_id := V_schedule;
133 G_category_set_id := V_Category_Set_id;
134 G_structure_id := V_Structure_Id;
135 G_fcategory := V_fcategory;
136 G_tcategory := V_tcategory;
137 G_fbuyer := V_fbuyer;
138 G_tbuyer := V_tbuyer;
139 G_fplanner := V_fplanner;
140 G_tplanner := V_tplanner;
141 G_forg := V_forg;
142 G_torg := V_torg;
143 G_fitem := V_fitem;
144 G_titem := V_titem;
145 G_ftrans_date := TO_DATE(V_ftrans_date, 'YYYY/MM/DD HH24:MI:SS') ; -- B9094377 ;
146 G_ttrans_date := TO_DATE(V_ttrans_date, 'YYYY/MM/DD HH24:MI:SS') ; -- B9094377;
147 G_critical_indicator := V_critical_indicator;
148 G_template := V_template;
149 G_template_locale := V_template_locale;
150
151 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Calling gmp_print_mps with values ');
152 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_orgnanization_id '||to_char(G_orgnanization_id));
153 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_schedule '||to_char(G_schedule));
154 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_schedule_id '||to_char(G_schedule_id));
155 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_category_set '||G_category_set);
156 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_structure_id '||to_char(G_structure_id));
157 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_category_set_id '||to_char(G_category_set_id));
158 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_fcategory '||G_fcategory);
159 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_tcategory '||G_tcategory);
160 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_fbuyer '||G_fbuyer);
161 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_tbuyer '||G_tbuyer);
162 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_fplanner '||G_fplanner);
163 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_tplanner '||G_tplanner);
164 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_forg '||G_forg);
165 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_torg '||G_torg);
166 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_fitem '||G_fitem);
167 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_titem '||G_titem);
168 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_ftrans_date '||TO_CHAR(G_ftrans_date,'DD-MON-YYYY'));
169 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_ttrans_date '||TO_CHAR(G_ttrans_date,'DD-MON-YYYY'));
170 FND_FILE.PUT_LINE ( FND_FILE.LOG, ' G_critical_indicator '||G_critical_indicator);
171
172 --Insert data in PS_MATL_HDR.
173 insert_header_data;
174
175 IF G_item_tab.count > 0 then
176 rips1usr_unbucket_report;
177
178 DELETE
179 FROM ps_matl_hdr pmh
180 WHERE pmh.matl_rep_id = G_matl_rep_id
181 AND
182 ((pmh.inventory_item_id NOT IN (SELECT pud1.inventory_item_id
183 FROM ps_ubkt_dtl pud1
184 WHERE pud1.matl_rep_id = G_matl_rep_id))
185 OR
186 (pmh.organization_id NOT IN (SELECT organization_id
187 FROM ps_ubkt_dtl pud2
188 WHERE pud2.inventory_item_id = pmh.inventory_item_id
189 AND pud2.matl_rep_id = G_matl_rep_id)));
190 END IF;
191
192 ps_generate_xml;
193
194 /*
195 -- Invoke the concurrent manager from here
196 IF V_number_of_copies > 0 THEN
197 X_status := FND_REQUEST.SET_PRINT_OPTIONS(V_printer,
198 UPPER(V_user_print_style),
199 V_number_of_copies, TRUE, 'N');
200 END IF;
201 -- request is submitted to the concurrent manager
202 FND_FILE.PUT_LINE ( FND_FILE.LOG,' Submitting the Req '||sqlerrm);
203
204 -- B3679023 niyadav changed 'TO_CHAR(G_user_id)' to 'TO_CHAR(G_Buyer_plnr_id)' .
205 X_conc_id := FND_REQUEST.SUBMIT_REQUEST('GMP','RIPS1USR','',
206 TO_CHAR(V_run_date,'YYYY/MM/DD HH24:MI:SS'),
207 FALSE, TO_CHAR(G_matl_rep_id),
208 TO_CHAR(G_Buyer_plnr_id), X_ri_where,
209 G_fwhse_code||','||G_twhse_code,'','','','','','',
210 '','','','','','','','','','',
211 '','','','','','','','','','',
212 '','','','','','','','','','',
213 '','','','','','','','','','',
214 '','','','','','','','','','',
215 '','','','','','','','','','',
216 '','','','','','','','','','',
217 '','','','','','','','','','',
218 '','','','','','','','','','');
219
220 FND_FILE.PUT_LINE ( FND_FILE.LOG,' Submitted the Req '||sqlerrm);
221
222 IF X_conc_id = 0 THEN
223 G_log_text := FND_MESSAGE.GET;
224 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
225 retcode:=2;
226 exit;
227 ELSE
228 COMMIT ;
229 END IF;
230
231 END IF;
232 END IF;
233 END LOOP;
234
235 CLOSE Cur_buyer_plnr;
236 */
237 EXCEPTION
238 WHEN OTHERS THEN
239 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in Print mps Activity'||sqlerrm);
240
241 END print_mps_activity;
242
243 /*************** END OF PROCEDURE **********************************/
244
245
246 /*============================================================================+
247 | |
248 | PROCEDURE NAME INSERT_HEADER_DATA |
249 | |
250 | DESCRIPTION Procedure to insert data into ps_matl_hdr |
251 | This Procedure fetches data for the Header Table by |
252 | building the Where condition based on the User and the|
253 | Planning Classes and then inserts into the Header |
254 | Table by creating a record group |
255 | |
256 | MODIFICATION HISTORY |
257 | 05/04/04 Rameshwar ----- created |
258 | |
259 +============================================================================*/
260
261 Procedure insert_header_data IS
262
263 x_select VARCHAR2(2000);
264 cur_item NUMBER;
265 X_item_id NUMBER;
266 X_i NUMBER;
267 X_org_id NUMBER;
268 X_category_id NUMBER;
269 X_rep_id NUMBER;
270 X_row_count NUMBER;
271
272 BEGIN
273 -- Building of the Where clause.
274
275 x_select := ' SELECT DISTINCT '||
276 ' msi.inventory_item_id, '||
277 ' msi.organization_id, ';
278
279 IF G_fcategory IS NOT NULL OR G_tcategory IS NOT NULL THEN
280 x_select := x_select || ' mca.category_id category_id';
281 ELSE
282 x_select := x_select || ' -999 category_id ';
283 END IF;
284 x_select := x_select || ' FROM ';
285 -- ' po_agents pag, '||
286 -- ' ps_schd_dtl psd, '||
287
288 IF G_fplanner IS NOT NULL OR G_tplanner IS NOT NULL THEN
289 x_select := x_select || ' mtl_planners mpl, ';
290 END IF;
291 IF G_fbuyer IS NOT NULL OR G_tbuyer IS NOT NULL THEN
292 x_select := x_select || ' hr_employees hem, ';
293 END IF;
294 IF G_forg IS NOT NULL OR G_torg IS NOT NULL THEN
295 x_select := x_select || ' mtl_parameters mpa, ';
296 END IF;
297 IF G_fcategory IS NOT NULL OR G_tcategory IS NOT NULL THEN
298 x_select := x_select || ' mtl_categories_kfv mca, ';
299 END IF;
300
301 x_select := x_select ||
302 ' mtl_system_items_kfv msi, '||
303 ' mtl_item_categories mic, '||
304 ' ps_schd_dtl psd, '||
305 ' mtl_category_sets mcs '||
306 ' WHERE '||
307 ' mcs.category_set_id = to_char(:category_set_id) '||
308 ' AND mcs.structure_id = to_char(:structure_id) '||
309 ' AND mic.category_set_id = mcs.category_set_id '||
310 ' AND psd.schedule_id = to_char(:schedule_id) '||
311 ' AND psd.organization_id = msi.organization_id '||
312 ' AND mic.inventory_item_id = msi.inventory_item_id '||
313 ' AND mic.organization_id = msi.organization_id ';
314
315 IF G_fcategory IS NOT NULL OR G_tcategory IS NOT NULL THEN
316 x_select := x_select || ' AND mcs.structure_id = mca.structure_id '||
317 ' AND mic.category_id = mca.category_id ';
318 IF G_fcategory IS NOT NULL THEN
319 x_select := x_select || ' AND mca.concatenated_segments >= :f_category ';
320 END IF;
321 IF G_tcategory IS NOT NULL THEN
322 x_select := x_select || ' AND mca.concatenated_segments <= :t_category ';
323 END IF;
324 END IF;
325
326 IF G_fplanner IS NOT NULL OR G_tplanner IS NOT NULL THEN
327 x_select := x_select || ' AND mpl.planner_code = msi.planner_code '||
328 ' AND mpl.organization_id = msi.organization_id ';
329 IF G_fplanner IS NOT NULL THEN
330 x_select := x_select || ' AND msi.planner_code >= :f_planner ';
331 END IF;
332 IF G_tplanner IS NOT NULL THEN
333 x_select := x_select || ' AND msi.planner_code <= :t_planner ';
334 END IF;
335 END IF;
336
337 IF G_fbuyer IS NOT NULL OR G_tbuyer IS NOT NULL THEN
338 x_select := x_select || ' AND hem.employee_id = msi.buyer_id ';
339 IF G_fbuyer IS NOT NULL THEN
340 x_select := x_select || ' AND hem.full_name >= :f_buyer ';
341 END IF;
342 IF G_tbuyer IS NOT NULL THEN
343 x_select := x_select || ' AND hem.full_name <= :t_buyer ';
344 END IF;
345 END IF;
346
347 IF G_forg IS NOT NULL OR G_torg IS NOT NULL THEN
348 x_select := x_select || ' AND mpa.organization_id = msi.organization_id ';
349 IF G_forg IS NOT NULL THEN
350 x_select := x_select || ' AND mpa.organization_code >= :f_org ';
351 END IF;
352 IF G_torg IS NOT NULL THEN
353 x_select := x_select || ' AND mpa.organization_code <= :t_org ';
354 END IF;
355 END IF;
356
357 IF G_fitem IS NOT NULL THEN
358 x_select := x_select || ' AND msi.concatenated_segments >= :f_item ';
359 END IF;
360 IF G_titem IS NOT NULL THEN
361 x_select := x_select || ' AND msi.concatenated_segments <= :t_item ';
362 END IF;
363
364 cur_item := dbms_sql.open_cursor;
365 dbms_sql.parse (cur_item, x_select,dbms_sql.NATIVE);
366
367 dbms_sql.bind_variable(cur_item, ':category_set_id', G_category_set_id);
368 dbms_sql.bind_variable(cur_item, ':structure_id', G_structure_id);
369 dbms_sql.bind_variable(cur_item, ':schedule_id', G_schedule_id);
370
371 IF G_fcategory IS NOT NULL THEN
372 dbms_sql.bind_variable(cur_item, ':f_category', G_fcategory);
373 END IF;
374 IF G_tcategory IS NOT NULL THEN
375 dbms_sql.bind_variable(cur_item, ':t_category', G_tcategory);
376 END IF;
377
378 IF G_fplanner IS NOT NULL THEN
379 dbms_sql.bind_variable(cur_item, ':f_planner', G_fplanner);
380 END IF;
381 IF G_tplanner IS NOT NULL THEN
382 dbms_sql.bind_variable(cur_item, ':t_planner', G_tplanner);
383 END IF;
384
385 IF G_fbuyer IS NOT NULL THEN
386 dbms_sql.bind_variable(cur_item, ':f_buyer', G_fbuyer);
387 END IF;
388 IF G_tbuyer IS NOT NULL THEN
389 dbms_sql.bind_variable(cur_item, ':t_buyer', G_tbuyer);
390 END IF;
391
392 IF G_forg IS NOT NULL THEN
393 dbms_sql.bind_variable(cur_item, ':f_org', G_forg);
394 END IF;
395 IF G_torg IS NOT NULL THEN
396 dbms_sql.bind_variable(cur_item, ':t_org', G_torg);
397 END IF;
398
399 IF G_fitem IS NOT NULL THEN
400 dbms_sql.bind_variable(cur_item, ':f_item', G_fitem);
401 END IF;
402 IF G_titem IS NOT NULL THEN
403 dbms_sql.bind_variable(cur_item, ':t_item', G_titem);
404 END IF;
405
406 dbms_sql.define_column (cur_item, 1, X_item_id);
407 dbms_sql.define_column (cur_item, 2, X_org_id);
408 dbms_sql.define_column (cur_item, 3, X_category_id);
409
410 X_row_count := dbms_sql.execute_and_fetch (cur_item);
411 IF X_row_count > 0 THEN
412 SELECT gmp_matl_rep_id_s.NEXTVAL INTO X_rep_id FROM dual;
413 G_matl_rep_id := X_rep_id;
414 X_i := 0;
415 LOOP
416 dbms_sql.column_value (cur_item, 1, X_item_id);
417 dbms_sql.column_value (cur_item, 2, X_org_id);
418 dbms_sql.column_value (cur_item, 3, X_category_id);
419 X_i := X_i + 1;
420 G_item_tab(X_i).inventory_item_id := X_item_id;
421 G_item_tab(X_i).organization_id := X_org_id;
422 G_item_tab(X_i).category_id := X_category_id;
423 -- Inserts the data into Header table.
424 INSERT INTO ps_matl_hdr (matl_rep_id,inventory_item_id,organization_id,category_id)
425 VALUES(X_rep_id,X_item_id,X_org_id,X_category_id);
426 IF dbms_sql.fetch_rows (cur_item) <= 0 THEN
427 EXIT;
428 END IF;
429 END LOOP;
430 END IF;
431 dbms_sql.close_cursor (cur_item);
432
433 EXCEPTION
434 WHEN OTHERS THEN
435 FND_FILE.PUT_LINE ( FND_FILE.LOG, sqlerrm);
436 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error writing to Header'||sqlerrm);
437 /* b3668927 nsinghi : Closing cursors in exception block. */
438 IF dbms_sql.is_open (cur_item) THEN
439 FND_FILE.PUT_LINE(FND_FILE.LOG,'EXCEPTION cur_item is Open');
440 dbms_sql.close_cursor (cur_item);
441 END IF;
442 END insert_header_data;
443
444 /*************** END OF PROCEDURE **********************************/
445
446 /*============================================================================+
447 | |
448 | PROCEDURE NAME RIPS1USR_UNBUCKET_REPORT |
449 | |
450 | DESCRIPTION Procedure to call mps_unbucket_details for items. |
451 | |
452 | MODIFICATION HISTORY |
453 | 05/04/04 Rameshwar ----- created |
454 | |
455 +============================================================================*/
456
457 Procedure rips1usr_unbucket_report IS
458
459 CURSOR Cur_check_dtl IS
460 SELECT 1
461 FROM FND_DUAL
462 WHERE EXISTS (SELECT matl_rep_id
463 FROM ps_ubkt_dtl
464 WHERE matl_rep_id = G_matl_rep_id) ;
465
466 X_ret NUMBER;
467 X_i NUMBER := 0;
468 X_planning_class VARCHAR2(8);
469 X_item_id NUMBER;
470 BEGIN
471
472 IF G_item_tab.COUNT > 0 THEN
473 FOR cnt IN G_item_tab.FIRST..G_item_tab.LAST
474 LOOP
475 ps_data_retrieval(G_item_tab(cnt).inventory_item_id,
476 G_item_tab(cnt).organization_id);
477 END LOOP;
478 END IF;
479
480 OPEN Cur_check_dtl;
481 FETCH Cur_check_dtl INTO X_ret;
482 CLOSE Cur_check_dtl;
483
484 IF (X_ret = 0) THEN
485 FND_MESSAGE.SET_NAME('GMP','PS_NO_TRANS');
486 G_log_text := FND_MESSAGE.GET;
487 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
488 ROLLBACK;
489 END IF;
490
491 EXCEPTION
492 WHEN OTHERS THEN
493 FND_FILE.PUT_LINE (FND_FILE.LOG, sqlerrm);
494 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error writing to Unbucket'||sqlerrm);
495 /* b3668927 nsinghi : Closing cursors in exception block. */
496 IF Cur_check_dtl%ISOPEN THEN
497 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cur_check_dtl Is Open');
498 CLOSE Cur_check_dtl;
499 END IF;
500
501 End rips1usr_unbucket_report;
502
503
504
505 /*******************End Of Procedure rips1usr_unbucket_report ******************************/
506
507
508 /*============================================================================+
509 | |
510 | PROCEDURE NAME PS_DATA_RETRIEVAL |
511 | |
512 | DESCRIPTION Procedure to call mps_unbucket_details for items. |
513 | |
514 | MODIFICATION HISTORY |
515 | 05/04/04 Rameshwar ----- created |
516 | 09/15/04 Teresa Wong B3865101 Added code to support profile to |
517 | exclude Internal Sales Orders. |
518 | |
519 +============================================================================*/
520
521
522 Procedure ps_data_retrieval (V_item_id IN NUMBER, V_organization_id IN NUMBER) IS
523
524 CURSOR get_order_ind_cur IS
525 SELECT order_ind
526 FROM ps_schd_hdr
527 WHERE schedule_id = G_schedule_id;
528
529 CURSOR Cur_fpo_doc_no IS
530 SELECT batch_no
531 FROM gme_batch_header
532 WHERE batch_type = 10
533 AND batch_id = G_doc_id
534 AND organization_id = V_organization_id
535 AND delete_mark = 0;
536
537 CURSOR Cur_prod_doc_no IS
538 SELECT batch_no
539 FROM gme_batch_header
540 WHERE batch_type = 0
541 AND batch_id = G_doc_id
542 AND organization_id = V_organization_id
543 AND delete_mark = 0;
544
545 /* nsinghi MPSCONV Start */
546 /* OPSO txns will no longer supported. So commenting the code. */
547 /*
548
549 CURSOR Cur_opso_doc_no IS
550 SELECT order_no
551 FROM op_ordr_hdr
552 WHERE order_id = G_doc_id
553 AND orgn_code = G_orgn_code;
554 */
555 /* nsinghi MPSCONV End */
556
557 /*B4905079 - Changed the cursor to improve the performance*/
558 CURSOR Cur_omso_doc_no IS
559 SELECT DISTINCT oh.order_number
560 FROM oe_order_headers_all oh,
561 oe_order_lines_all ol
562 WHERE oh.header_id = ol.header_id
563 AND inv_salesorder.get_salesorder_for_oeheader(ol.header_id) = G_doc_id
564 AND ol.open_flag = 'Y'
565 AND ol.visible_demand_flag = 'Y' /*B4905079 - Flag to ensure that available_to_mrp = 1 */
566 AND decode(ol.source_document_type_id, 10, 8, decode(ol.line_category_code, 'ORDER',2,12)) IN (2,8);
567
568 -- TKW B3865101 9/15/04 Added cursor for the case where Exclude
569 -- Internal Sales Orders profile was set to Y.
570 /*B4905079 - Changed the cursor to improve the performance*/
571 CURSOR Cur_excl_internal_omso_doc_no IS
572 SELECT DISTINCT oh.order_number
573 FROM oe_order_headers_all oh,
574 oe_order_lines_all ol
575 WHERE oh.header_id = ol.header_id
576 AND inv_salesorder.get_salesorder_for_oeheader(ol.header_id) = G_doc_id
577 AND ol.open_flag = 'Y'
578 AND ol.visible_demand_flag = 'Y' /*B4905079 - Flag to ensure that available_to_mrp = 1 */
579 AND decode(ol.source_document_type_id, 10, 8, decode(ol.line_category_code, 'ORDER',2,12)) IN (2,8)
580 AND nvl(ol.source_document_type_id, 0) <> 10 ;
581
582 CURSOR Cur_po_doc_no IS
583 SELECT po.po_number
584 FROM MTL_PARAMETERS mtl,
585 MTL_SYSTEM_ITEMS mitem,
586 -- IC_ITEM_MST ic,
587 PO_PO_SUPPLY_VIEW po
588 WHERE po.item_id = mitem.inventory_item_id
589 AND po.to_organization_id = mitem.organization_id
590 -- AND mitem.segment1 = ic.item_no
591 AND mtl.organization_id = po.to_organization_id
592 AND mtl.process_enabled_flag = 'Y'
593 AND mitem.inventory_item_flag = 'Y'
594 -- AND ic.noninv_ind = 0
595 -- AND ic.experimental_ind = 0
596 -- AND ic.delete_mark = 0
597 AND NOT EXISTS
598 ( SELECT 1 FROM oe_drop_ship_sources odss
599 WHERE po.PO_HEADER_ID = odss.PO_HEADER_ID
600 AND po.PO_LINE_ID = odss.PO_LINE_ID )
601 AND po.po_header_id = G_doc_id ;
602
603
604 CURSOR Cur_requisition_details IS
605 SELECT po.requisition_number
606 FROM MTL_PARAMETERS mtl,
607 MTL_SYSTEM_ITEMS mitem,
608 -- IC_ITEM_MST ic,
609 PO_REQ_SUPPLY_VIEW po
610 WHERE po.item_id = mitem.inventory_item_id
611 AND po.to_organization_id = mitem.organization_id
612 -- AND mitem.segment1 = ic.item_no
613 AND mtl.organization_id = po.to_organization_id
614 AND mtl.process_enabled_flag = 'Y'
615 AND mitem.inventory_item_flag = 'Y'
616 -- AND ic.noninv_ind = 0
617 -- AND ic.experimental_ind = 0
618 -- AND ic.delete_mark = 0
619 AND NOT EXISTS
620 ( SELECT 1 FROM oe_drop_ship_sources odss
621 WHERE po.REQUISITION_HEADER_ID = odss.REQUISITION_HEADER_ID
622 AND po.REQ_LINE_ID = odss.REQUISITION_LINE_ID )
623 AND po.requisition_header_id = G_doc_id ;
624
625 CURSOR Cur_receiving_details IS
626 SELECT ph.segment1
627 FROM MTL_PARAMETERS mtl,
628 MTL_SYSTEM_ITEMS mitem,
629 -- IC_ITEM_MST ic,
630 PO_HEADERS_ALL ph,
631 PO_RCV_SUPPLY_VIEW po
632 WHERE po.item_id = mitem.inventory_item_id
633 AND po.to_organization_id = mitem.organization_id
634 -- AND mitem.segment1 = ic.item_no
635 AND mtl.organization_id = po.to_organization_id
636 AND mtl.process_enabled_flag = 'Y'
637 AND mitem.inventory_item_flag = 'Y'
638 -- AND ic.noninv_ind = 0
639 -- AND ic.experimental_ind = 0
640 -- AND ic.delete_mark = 0
641 AND po.po_header_id = ph.po_header_id
642 AND NOT EXISTS
643 ( SELECT 1 FROM oe_drop_ship_sources odss
644 WHERE po.PO_HEADER_ID = odss.PO_HEADER_ID
645 AND po.PO_LINE_ID = odss.PO_LINE_ID )
646 AND po.po_header_id = G_doc_id
647 AND G_doc_type = 'PRCV'
648 UNION ALL
649 SELECT rsh.receipt_num
650 FROM MTL_PARAMETERS mtl,
651 MTL_SYSTEM_ITEMS mitem,
652 -- IC_ITEM_MST ic,
653 RCV_SHIPMENT_HEADERS rsh,
654 PO_SHIP_RCV_SUPPLY_VIEW po
655 WHERE po.item_id = mitem.inventory_item_id
656 AND po.shipment_header_id = rsh.shipment_header_id
657 AND po.to_organization_id = mitem.organization_id
658 -- AND mitem.segment1 = ic.item_no
659 AND mtl.organization_id = po.to_organization_id
660 AND mtl.process_enabled_flag = 'Y'
661 AND mitem.inventory_item_flag = 'Y'
662 -- AND ic.noninv_ind = 0
663 -- AND ic.experimental_ind = 0
664 -- AND ic.delete_mark = 0
665 AND po.shipment_header_id = G_doc_id ;
666
667 CURSOR Cur_shipment_details IS
668 SELECT rsh.receipt_num
669 FROM MTL_PARAMETERS mtl,
670 MTL_SYSTEM_ITEMS mitem,
671 -- IC_ITEM_MST ic,
672 RCV_SHIPMENT_HEADERS rsh,
673 PO_SHIP_SUPPLY_VIEW po
674 WHERE po.item_id = mitem.inventory_item_id
675 AND po.shipment_header_id = rsh.shipment_header_id
676 AND po.to_organization_id = mitem.organization_id
677 -- AND mitem.segment1 = ic.item_no
678 AND mtl.organization_id = po.to_organization_id
679 AND mtl.process_enabled_flag = 'Y'
680 AND mitem.inventory_item_flag = 'Y'
681 -- AND ic.noninv_ind = 0
682 -- AND ic.experimental_ind = 0
683 -- AND ic.delete_mark = 0
684 AND po.shipment_header_id = G_doc_id ;
685
686 /* nsinghi MPSCONV Start */
687 /* Since the transfer txns will no longer exist, so commenting the code. */
688 /*
689 CURSOR Cur_transfer_doc_no IS
690 SELECT transfer_no
691 FROM ic_xfer_mst
692 WHERE transfer_id = G_doc_id ;
693 */
694 /* nsinghi MPSCONV End */
695
696
697 X_row_count NUMBER;
698 X_row_count1 NUMBER;
699 X_select1 VARCHAR2(4000);
700 X_select VARCHAR2(25000);
701 gs_temp VARCHAR2(3000);
702 X_status NUMBER(5);
703 X_first_flag NUMBER(5);
704 X_doc1 NUMBER;
705 X_doc NUMBER;
706 X_doc_type VARCHAR2(4);
707 X_trans_date DATE;
708 X_orgn_code VARCHAR2(4);
709 X_doc_id NUMBER(10);
710 X_line_id NUMBER;
711 X_qty NUMBER;
712 X_trans_qty NUMBER;
713 X_trans_qty2 NUMBER;
714 X_cust_vend Varchar2(32);
715 X_line_no NUMBER(10);
716 X_org_code VARCHAR2(3);
717 X_i NUMBER(5) := 0 ;
718 X_date DATE;
719 X_pastdue CHAR(1);
720 X_ret NUMBER;
721 X_on_hand1 NUMBER;
722 X_on_hand2 NUMBER;
723 Balance1 NUMBER;
724 Balance2 NUMBER;
725 Start_balance NUMBER;
726 l_ord_ind NUMBER;
727 X_doc_line NUMBER;
728 x_doc_no VARCHAR2(32);
729 X_qty_i NUMBER;
730 X_qty2_i NUMBER;
731 X_qty_k NUMBER;
732 X_j NUMBER;
733 x_whse_list VARCHAR2(40);
734 exclude_internal_omso NUMBER := 0; -- TKW Added for B3865101
735
736 BEGIN
737
738 X_pastdue := ' ' ;
739
740 --Retrieve the value whether or not the sales order has been included
741 OPEN get_order_ind_cur;
742 FETCH get_order_ind_cur INTO l_ord_ind;
743 CLOSE get_order_ind_cur;
744
745 exclude_internal_omso := TO_NUMBER(FND_PROFILE.VALUE('GMP_EXCLUDE_INTERNAL_OMSO')); /* B3865101 */
746
747 get_onhand_qty(V_item_id, V_organization_id);
748
749 -- G_start_balance := balance1;
750 balance1 := G_on_hand1;
751 balance2 := G_on_hand2;
752
753 --Get safety stock values.
754
755 pscommon_safety_stock(v_item_id, V_organization_id);
756
757 x_select := x_select || ' SELECT gmd.material_requirement_date trans_date, '||
758 ' DECODE(gbh.batch_type, 10,'||''''||'FPO'||''''||','||''''||'PROD'||''''||') doc_type, gbh.batch_id doc_id,'||
759 ' DECODE(gmd.line_type, -1,-1,1) * '||
760 ' DECODE(gmd.dtl_um, '||
761 ' msi.primary_uom_code, '||
762 ' NVL((NVL(gmd.wip_plan_qty, gmd.plan_qty) - gmd.actual_qty), 0), '||
763 ' inv_convert.inv_um_convert(gmd.inventory_item_id, '||
764 ' NULL, '||
765 ' gmd.organization_id, '||
766 ' NULL, '||
767 ' NVL((NVL(gmd.wip_plan_qty, gmd.plan_qty) - gmd.actual_qty), 0), '||
768 ' gmd.dtl_um, '||
769 ' msi.primary_uom_code, '||
770 ' NULL, '||
771 ' NULL '||
772 ' )) trans_qty, '||
773 ' DECODE(msi.dual_uom_control,0,0, '||
774 ' DECODE(gmd.line_type, -1,-1,1) * DECODE(gmd.dtl_um, '||
775 ' msi.secondary_uom_code, '||
776 ' NVL((NVL(gmd.wip_plan_qty, gmd.plan_qty) - gmd.actual_qty), 0), '||
777 ' inv_convert.inv_um_convert(gmd.inventory_item_id, '||
778 ' NULL, '||
779 ' gmd.organization_id, '||
780 ' NULL, '||
781 ' NVL((NVL(gmd.wip_plan_qty, gmd.plan_qty) - gmd.actual_qty), 0), '||
782 ' gmd.dtl_um, '||
783 ' msi.secondary_uom_code, '||
784 ' NULL, '||
785 ' NULL '||
786 ' ))) trans_qty2, '||
787 ' gmd.material_detail_id line_id, mp.organization_code inv_org_code'||
788 ' FROM '||
789 ' gme_batch_header gbh, '||
790 ' gme_material_details gmd, '||
791 ' mtl_parameters mp, '||
792 ' hr_organization_units hou, '||
793 ' mtl_system_items msi '||
794 ' WHERE '||
795 ' Gbh.batch_id = gmd.batch_id '||
796 ' AND msi.inventory_item_id = gmd.inventory_item_id '||
797 ' AND msi.organization_id = gmd.organization_id '||
798 ' AND gmd.organization_id = mp.organization_id '||
799 ' AND mp.process_enabled_flag = '||''''||'Y'||''''||
800 ' AND gbh.batch_status IN (1,2) '||
801 ' AND gmd.actual_qty < NVL(gmd.wip_plan_qty, gmd.plan_qty) '||
802 ' AND msi.inventory_item_id = TO_CHAR(:item_id) '||
803 ' AND hou.organization_id = mp.organization_id '||
804 ' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
805 ' AND gmd.material_requirement_date >= nvl(:start_date, gmd.material_requirement_date - 1) '||
806 ' AND gmd.material_requirement_date <= nvl(:end_date, gmd.material_requirement_date + 1) '||
807 ' AND gbh.organization_id = TO_CHAR(:organization_id) ';
808 IF l_ord_ind = 1 THEN
809 x_select := x_select ||
810 ' UNION ALL '||
811 ' SELECT ' ||
812 ' mtl.requirement_date trans_date, ' ||
813 ''''||'OMSO'||''''||' doc_type, mtl.demand_source_header_id doc_id, '||
814 ' mtl.primary_uom_quantity * (-1) trans_qty, '||
815 ' DECODE(items.dual_uom_control,0,0, '||
816 ' (-1) * inv_convert.inv_um_convert(mtl.inventory_item_id, '||
817 ' NULL, '||
818 ' org.organization_id, '||
819 ' NULL, '||
820 ' mtl.primary_uom_quantity , '||
821 ' items.primary_uom_code, '||
822 ' items.secondary_uom_code, '||
823 ' NULL, '||
824 ' NULL '||
825 ' )) trans_qty2, '||
826 ' dtl.line_id line_id, org.organization_code inv_org_code '||
827 'FROM '||
828 ' mtl_demand_omoe mtl, '||
829 ' mtl_system_items items, '||
830 ' oe_order_headers_all hdr, '||
831 ' oe_order_lines_all dtl, '||
832 ' hr_organization_units hou, '||
833 ' mtl_parameters org '||
834 ' WHERE '||
835 ' mtl.inventory_item_id = TO_CHAR(:item_id) '||
836 ' AND hou.organization_id = org.organization_id '||
837 ' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
838 ' AND mtl.organization_id = TO_CHAR(:organization_id) '||
839 ' and items.organization_id = mtl.organization_id '||
840 ' and items.inventory_item_id = mtl.inventory_item_id '||
841 ' and NVL(mtl.completed_quantity,0) = 0 '||
842 ' and mtl.open_flag = '||''''||'Y'||''''||
843 ' and mtl.available_to_mrp = 1 '||
844 ' and mtl.parent_demand_id is NULL'||
845 ' and mtl.demand_source_type IN (2,8)'||
846 ' and mtl.demand_id = dtl.line_id '||
847 ' and dtl.header_id = hdr.header_id '||
848 ' and dtl.ship_from_org_id = org.organization_id '||
849 ' AND mtl.requirement_date >= nvl(:start_date, mtl.requirement_date - 1) '||
850 ' AND mtl.requirement_date <= nvl(:end_date, mtl.requirement_date + 1) '||
851 ' and org.process_enabled_flag = '||''''||'Y'||''''||
852 ' and ((TO_NUMBER(FND_PROFILE.VALUE('||''''||'GMP_EXCLUDE_INTERNAL_OMSO'||''''||')) = 1 ' ||
853 ' and nvl(dtl.source_document_type_id, 0) <> 10 ' ||
854 ' ) ' ||
855 ' or TO_NUMBER(FND_PROFILE.VALUE('||''''||'GMP_EXCLUDE_INTERNAL_OMSO'||''''||')) = 0 ' ||
856 ' ) ' ||
857 ' and NOT EXISTS '||
858 ' (SELECT 1 '||
859 ' FROM so_lines_all sl, '||
860 ' so_lines_all slp, '||
861 ' mtl_demand_omoe dem'||
862 ' WHERE '||
863 ' slp.line_id(+) = nvl(sl.parent_line_id,sl.line_id)'||
864 ' and to_number(dem.demand_source_line) = sl.line_id(+) '||
865 ' and dem.demand_source_type in (2,8)'||
866 ' and sl.end_item_unit_number IS NULL'||
867 ' and slp.end_item_unit_number IS NULL'||
868 ' and dem.demand_id = mtl.demand_id '||
869 ' and items.effectivity_control = 2) ';
870 END IF ;
871 x_select := x_select ||' UNION ALL '||
872 ' SELECT '||
873 ' dtl.forecast_date trans_date, '||
874 ' '||''''||'FCST'||''''||' doc_type, NULL doc_id, '||
875 ' (-1) * dtl.current_forecast_quantity trans_qty, '||
876 ' DECODE(msi.dual_uom_control,0,0, '||
877 ' (-1) * inv_convert.inv_um_convert(dtl.inventory_item_id, '||
878 ' NULL, '||
879 ' dtl.organization_id, '||
880 ' NULL, '||
881 ' dtl.current_forecast_quantity, '||
882 ' msi.primary_uom_code, '||
883 ' msi.secondary_uom_code, '||
884 ' NULL, '||
885 ' NULL '||
886 ' )) trans_qty2, '||
887 ' 0 line_id, mp.organization_code inv_org_code '||
888 ' FROM '||
889 ' ps_schd_for psf, '||
890 ' mrp_forecast_designators mff, '||
891 ' mrp_forecast_dates dtl, '||
892 ' mtl_system_items msi, '||
893 ' hr_organization_units hou, '||
894 ' mtl_parameters mp '||
895 ' WHERE dtl.inventory_item_id = TO_CHAR(:item_id) '||
896 ' AND psf.schedule_id = TO_CHAR(:schedule_id) '||
897 ' AND psf.organization_id = TO_CHAR(:organization_id) '||
898 ' AND hou.organization_id = mp.organization_id '||
899 ' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
900 ' AND psf.organization_id = mp.organization_id '||
901 ' AND mp.process_enabled_flag = '||''''||'Y'||''''||
902 ' AND psf.organization_id = msi.organization_id '||
903 ' AND dtl.inventory_item_id = msi.inventory_item_id '||
904 ' AND psf.organization_id = mff.organization_id '||
905 ' AND psf.forecast_designator = mff.forecast_set '||
906 ' AND mff.forecast_designator = dtl.forecast_designator '||
907 ' AND mff.organization_id = dtl.organization_id '||
908 ' AND dtl.forecast_date >= nvl(:start_date, dtl.forecast_date - 1) '||
909 ' AND dtl.forecast_date <= nvl(:end_date, dtl.forecast_date + 1) '||
910 -- Bug # 13542871 vkinduri, truncated sysdate to see forecast entires entered on sysdate
911 --' AND dtl.forecast_date >= fnd_date.canonical_to_date(fnd_date.date_to_canonical(sysdate)) '||
912 ' AND dtl.forecast_date >= trunc(fnd_date.canonical_to_date(fnd_date.date_to_canonical(sysdate))) '||
913 ' UNION ALL '||
914 ' SELECT po.expected_delivery_date trans_date, '||''''||'PORD'||''''||' doc_type, '||
915 ' po.po_header_id doc_id, '||
916 ' po.to_org_primary_quantity trans_qty,'||
917 ' DECODE(mitem.dual_uom_control,0,0, '||
918 ' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
919 ' NULL, '||
920 ' mitem.organization_id, '||
921 ' NULL, '||
922 ' po.to_org_primary_quantity, '||
923 ' mitem.primary_uom_code, '||
924 ' mitem.secondary_uom_code, '||
925 ' NULL, '||
926 ' NULL)) trans_qty2, '||
927 -- Modified line_id from po.line_location_id to po.po_line_id as per bug # 11068148
928 --' po.po_line_location_id line_id, mtl.organization_code inv_org_code '||
929 ' po.po_line_id line_id, mtl.organization_code inv_org_code '||
930 ' FROM MTL_PARAMETERS mtl, '||
931 ' hr_organization_units hou, '||
932 ' po_po_supply_view po, mtl_system_items mitem '||
933 ' WHERE po.item_id = TO_CHAR(:item_id) '||
934 ' AND po.item_id = mitem.inventory_item_id '||
935 ' AND po.to_organization_id = mitem.organization_id '||
936 ' AND mtl.organization_id = po.to_organization_id '||
937 ' AND mtl.process_enabled_flag = ' ||''''||'Y'||''''||
938 ' AND po.to_organization_id = TO_CHAR(:organization_id) '||
939 ' AND hou.organization_id = mtl.organization_id '||
940 ' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
941 ' AND po.expected_delivery_date >= nvl(:start_date, po.expected_delivery_date - 1) '||
942 ' AND po.expected_delivery_date <= nvl(:end_date, po.expected_delivery_date + 1) '||
943 ' AND NOT EXISTS '||
944 ' ( SELECT 1 FROM oe_drop_ship_sources odss '||
945 ' WHERE po.po_header_id = odss.po_header_id '||
946 ' AND po.po_line_id = odss.po_line_id ) '||
947 ' UNION ALL '||
948 ' SELECT po.expected_delivery_date, '||''''||'PREQ'||''''||' , '||
949 ' po.requisition_header_id, '||
950 ' po.to_org_primary_quantity,'||
951 ' DECODE(mitem.dual_uom_control,0,0, '||
952 ' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
953 ' NULL, '||
954 ' mitem.organization_id, '||
955 ' NULL, '||
956 ' po.to_org_primary_quantity, '||
957 ' mitem.primary_uom_code, '||
958 ' mitem.secondary_uom_code, '||
959 ' NULL, '||
960 ' NULL )) trans_qty2, '||
961 ' po.req_line_id, mtl.organization_code '||
962 ' FROM MTL_PARAMETERS mtl, '||
963 ' hr_organization_units hou, '||
964 ' po_req_supply_view po, mtl_system_items mitem '||
965 ' WHERE po.item_id = TO_CHAR(:item_id) '||
966 ' AND po.item_id = mitem.inventory_item_id '||
967 ' AND po.to_organization_id = mitem.organization_id '||
968 ' AND mtl.organization_id = po.to_organization_id '||
969 ' AND mtl.process_enabled_flag = ' ||''''||'Y'||''''||
970 ' AND po.to_organization_id = TO_CHAR(:organization_id) '||
971 ' AND hou.organization_id = mtl.organization_id '||
972 ' AND po.expected_delivery_date >= nvl(:start_date, po.expected_delivery_date - 1) '||
973 ' AND po.expected_delivery_date <= nvl(:end_date, po.expected_delivery_date + 1) '||
974 ' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
975 ' AND NOT EXISTS '||
976 ' ( SELECT 1 FROM oe_drop_ship_sources odss '||
977 ' WHERE po.REQUISITION_HEADER_ID = odss.REQUISITION_HEADER_ID '||
978 ' AND po.REQ_LINE_ID = odss.REQUISITION_LINE_ID ) '||
979 ' UNION ALL'||
980 ' SELECT po.expected_delivery_date trans_date, '||''''||'PRCV'||''''||' doc_type, '||
981 ' po.po_header_id doc_id, '||
982 ' po.to_org_primary_quantity trans_qty,'||
983 ' DECODE(mitem.dual_uom_control,0,0, '||
984 ' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
985 ' NULL, '||
986 ' mitem.organization_id, '||
987 ' NULL, '||
988 ' po.to_org_primary_quantity, '||
989 ' mitem.primary_uom_code, '||
990 ' mitem.secondary_uom_code, '||
991 ' NULL, '||
992 ' NULL)) trans_qty2, '||
993 ' po.po_line_id line_id, mtl.organization_code inv_org_code '||
994 ' FROM MTL_PARAMETERS mtl, '||
995 ' hr_organization_units hou, '||
996 ' po_rcv_supply_view po, mtl_system_items mitem '||
997 ' WHERE po.item_id = TO_CHAR(:item_id) '||
998 ' AND po.item_id = mitem.inventory_item_id '||
999 ' AND po.to_organization_id = mitem.organization_id '||
1000 ' AND mtl.organization_id = po.to_organization_id '||
1001 ' AND mtl.process_enabled_flag = ' ||''''||'Y'||''''||
1002 ' AND po.to_organization_id = TO_CHAR(:organization_id) '||
1003 ' AND hou.organization_id = mtl.organization_id '||
1004 ' AND po.expected_delivery_date >= nvl(:start_date, po.expected_delivery_date - 1) '||
1005 ' AND po.expected_delivery_date <= nvl(:end_date, po.expected_delivery_date + 1) '||
1006 ' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
1007 ' AND NOT EXISTS '||
1008 ' ( SELECT 1 FROM oe_drop_ship_sources odss '||
1009 ' WHERE po.po_header_id = odss.po_header_id '||
1010 ' AND po.po_line_id = odss.po_line_id ) '||
1011 ' UNION ALL'||
1012 ' SELECT po.expected_delivery_date trans_date, '||''''||'PRCV'||''''||' doc_type ,'||
1013 ' po.shipment_header_id doc_id, '||
1014 ' po.to_org_primary_quantity trans_qty,'||
1015 ' DECODE(mitem.dual_uom_control,0,0, '||
1016 ' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
1017 ' NULL, '||
1018 ' mitem.organization_id, '||
1019 ' NULL, '||
1020 ' po.to_org_primary_quantity, '||
1021 ' mitem.primary_uom_code, '||
1022 ' mitem.secondary_uom_code, '||
1023 ' NULL, '||
1024 ' NULL)) trans_qty2, '||
1025 ' po.shipment_line_id line_id, mtl.organization_code inv_org_code '||
1026 ' FROM MTL_PARAMETERS mtl, '||
1027 ' hr_organization_units hou, '||
1028 ' po_ship_rcv_supply_view po, mtl_system_items mitem '||
1029 ' WHERE po.item_id = TO_CHAR(:item_id) '||
1030 ' AND po.item_id = mitem.inventory_item_id '||
1031 ' AND po.to_organization_id = mitem.organization_id '||
1032 ' AND mtl.organization_id = po.to_organization_id '||
1033 ' AND mtl.process_enabled_flag = ' ||''''||'Y'||''''||
1034 ' AND po.to_organization_id = TO_CHAR(:organization_id) '||
1035 ' AND hou.organization_id = mtl.organization_id '||
1036 ' AND po.expected_delivery_date >= nvl(:start_date, po.expected_delivery_date - 1) '||
1037 ' AND po.expected_delivery_date <= nvl(:end_date, po.expected_delivery_date + 1) '||
1038 ' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
1039 ' UNION ALL'||
1040 ' SELECT po.expected_delivery_date trans_date, '||''''||'SHMT'||''''||' doc_type,'||
1041 ' po.shipment_header_id doc_id, '||
1042 ' po.to_org_primary_quantity trans_qty,'||
1043 ' DECODE(mitem.dual_uom_control,0,0, '||
1044 ' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
1045 ' NULL, '||
1046 ' mitem.organization_id, '||
1047 ' NULL, '||
1048 ' po.to_org_primary_quantity, '||
1049 ' mitem.primary_uom_code, '||
1050 ' mitem.secondary_uom_code, '||
1051 ' NULL, '||
1052 ' NULL)) trans_qty2, '||
1053 ' po.shipment_line_id line_id, mtl.organization_code inv_org_code '||
1054 ' FROM MTL_PARAMETERS mtl, '||
1055 ' hr_organization_units hou, '||
1056 ' po_ship_supply_view po, mtl_system_items mitem '||
1057 ' WHERE po.item_id = TO_CHAR(:item_id) '||
1058 ' AND po.item_id = mitem.inventory_item_id '||
1059 ' AND po.to_organization_id = mitem.organization_id '||
1060 ' AND mtl.organization_id = po.to_organization_id '||
1061 ' AND mtl.process_enabled_flag = ' ||''''||'Y'||''''||
1062 ' AND mtl.organization_id = TO_CHAR(:organization_id) '||
1063 ' AND hou.organization_id = mtl.organization_id '||
1064 ' AND po.expected_delivery_date >= nvl(:start_date, po.expected_delivery_date - 1) '||
1065 ' AND po.expected_delivery_date <= nvl(:end_date, po.expected_delivery_date + 1) '||
1066 ' AND nvl(hou.date_to,SYSDATE) >= SYSDATE '||
1067 ' ORDER BY 1 ASC, 4 DESC';
1068
1069 X_doc := dbms_sql.open_cursor;
1070
1071 dbms_sql.parse(X_doc,X_select,dbms_sql.NATIVE);
1072
1073 dbms_sql.bind_variable(X_doc,':item_id',V_item_id);
1074 dbms_sql.bind_variable(X_doc,':schedule_id',G_schedule_id);
1075 dbms_sql.bind_variable(X_doc,':organization_id',V_organization_id);
1076 dbms_sql.bind_variable(X_doc,':start_date',G_ftrans_date);
1077 dbms_sql.bind_variable(X_doc,':end_date',G_ttrans_date);
1078
1079 dbms_sql.define_column(X_doc, 1, X_trans_date);
1080 dbms_sql.define_column(X_doc, 2, X_doc_type, 4);
1081 dbms_sql.define_column(X_doc, 3, X_doc_id);
1082 dbms_sql.define_column(X_doc, 4, X_trans_qty);
1083 dbms_sql.define_column(X_doc, 5, X_trans_qty2);
1084 dbms_sql.define_column(X_doc, 6, X_line_id);
1085 dbms_sql.define_column(X_doc, 7, X_org_code,3);
1086
1087 X_row_count := dbms_sql.EXECUTE(X_doc);
1088
1089 LOOP
1090
1091 X_row_count := dbms_sql.fetch_rows (X_doc);
1092 IF X_row_count = 0 THEN
1093 EXIT;
1094 END IF;
1095
1096 X_i := X_i + 1;
1097 dbms_sql.column_value(X_doc, 1, X_trans_date);
1098 dbms_sql.column_value(X_doc, 2, X_doc_type);
1099 dbms_sql.column_value(X_doc, 3, X_doc_id);
1100 dbms_sql.column_value(X_doc, 4, X_trans_qty);
1101 dbms_sql.column_value(X_doc, 5, X_trans_qty2);
1102 dbms_sql.column_value(X_doc, 6, X_line_id);
1103 dbms_sql.column_value(X_doc, 7, X_org_code);
1104
1105 G_doc_tab(X_i).trans_date := X_trans_date;
1106 G_doc_tab(X_i).doc_type := X_doc_type;
1107 G_doc_tab(X_i).doc_id := X_doc_id;
1108 G_doc_tab(X_i).trans_qty := X_trans_qty;
1109 G_doc_tab(X_i).trans_qty2 := X_trans_qty2;
1110 G_doc_tab(X_i).line_id := X_line_id;
1111 G_doc_tab(X_i).org_code := X_org_code;
1112
1113
1114 END LOOP;
1115 x_row_count1 := X_i;
1116
1117 FOR X_i IN 1..X_row_count1
1118 LOOP
1119 G_doc_type := G_doc_tab(X_i).doc_type;
1120 G_doc_id := G_doc_tab(X_i).doc_id;
1121 G_tranline_id := G_doc_tab(X_i).line_id;
1122 -- G_orgn_code := G_doc_tab(X_i).orgn_code;
1123
1124 IF G_doc_tab(X_i).doc_type = 'FPO' THEN
1125 OPEN Cur_fpo_doc_no;
1126 FETCH Cur_fpo_doc_no INTO x_doc_no;
1127 CLOSE Cur_fpo_doc_no;
1128 ELSIF G_doc_tab(X_i).doc_type = 'PROD' THEN
1129 OPEN Cur_prod_doc_no;
1130 FETCH Cur_prod_doc_no INTO x_doc_no;
1131 CLOSE Cur_prod_doc_no;
1132 ELSIF G_doc_tab(X_i).doc_type = 'PREQ' THEN
1133 OPEN Cur_requisition_details;
1134 FETCH Cur_requisition_details INTO x_doc_no;
1135 CLOSE Cur_requisition_details;
1136 ELSIF G_doc_tab(X_i).doc_type = 'PRCV' THEN
1137 OPEN Cur_receiving_details;
1138 FETCH Cur_receiving_details INTO x_doc_no;
1139 CLOSE Cur_receiving_details;
1140 ELSIF G_doc_tab(X_i).doc_type = 'SHMT' THEN
1141 OPEN Cur_shipment_details;
1142 FETCH Cur_shipment_details INTO x_doc_no;
1143 CLOSE Cur_shipment_details;
1144 ELSIF G_doc_tab(X_i).doc_type = 'PORD' THEN
1145 OPEN Cur_po_doc_no;
1146 FETCH Cur_po_doc_no INTO x_doc_no;
1147 CLOSE Cur_po_doc_no;
1148 /* nsinghi MPSCONV Start */
1149 /* OPSO txns will no longer supported. So commenting the code. */
1150 /*
1151 ELSIF G_doc_tab(X_i).doc_type = 'OPSO' THEN
1152 OPEN Cur_opso_doc_no;
1153 FETCH Cur_opso_doc_no INTO x_doc_no;
1154 CLOSE Cur_opso_doc_no;
1155 */
1156 /* nsinghi MPSCONV End */
1157 ELSIF G_doc_tab(X_i).doc_type = 'OMSO' THEN
1158 -- TKW B3865101 Check profile value before getting doc no
1159 IF (exclude_internal_omso = 0) THEN
1160 OPEN Cur_omso_doc_no;
1161 FETCH Cur_omso_doc_no INTO x_doc_no;
1162 CLOSE Cur_omso_doc_no;
1163 ELSE
1164 OPEN Cur_excl_internal_omso_doc_no;
1165 FETCH Cur_excl_internal_omso_doc_no INTO x_doc_no;
1166 CLOSE Cur_excl_internal_omso_doc_no;
1167 END IF;
1168
1169 /* nsinghi MPSCONV Start */
1170 /* Since the transfer txns will no longer exist, so commenting the code. */
1171 /*
1172 ELSIF G_doc_tab(X_i).doc_type= 'XFER' THEN
1173 OPEN Cur_transfer_doc_no;
1174 FETCH Cur_transfer_doc_no INTO x_doc_no;
1175 CLOSE Cur_transfer_doc_no;
1176 */
1177 /* nsinghi MPSCONV End */
1178 END IF;
1179
1180
1181
1182 balance1 := balance1 + G_doc_tab(X_i).trans_qty ;
1183
1184 --If balance is less than the safety stock then set critical indicator accordingly.
1185 IF (nvl(balance1,0) < nvl(G_total_ss,0)) THEN
1186 G_c_ind := '**';
1187 cleanup_details;
1188 SELECT SYSDATE INTO X_date FROM dual;
1189 X_ret := G_doc_tab(X_i).trans_date - X_date;
1190 IF (X_ret < 0) THEN
1191 X_pastdue := '*';
1192 ELSE
1193 X_pastdue := NULL;
1194 END IF;
1195 --Insert the record into the detail table.
1196
1197 INSERT INTO ps_ubkt_dtl(matl_rep_id,
1198 -- item_id,
1199 inventory_item_id,
1200 -- planning_class,
1201 -- whse_code,
1202 organization_id,
1203 start_balance,
1204 past_due,
1205 trans_date,
1206 doc_type,
1207 -- orgn_code,
1208 doc_no,
1209 line_id,
1210 trans_qty,
1211 balance,
1212 critical_ind,
1213 cust_vend)
1214 Values( G_matl_rep_id,
1215 V_item_id,
1216 -- V_planning_class,
1217 -- G_doc_tab(X_i).whse_code,
1218 V_organization_id,
1219 G_start_balance,
1220 X_pastdue,
1221 G_doc_tab(X_i).trans_date,
1222 G_doc_type,
1223 -- G_doc_tab(X_i).orgn_code,
1224 X_doc_no,
1225 G_doc_tab(X_i).line_id,
1226 G_doc_tab(X_i).trans_qty,
1227 balance1,
1228 G_c_ind,
1229 G_cust_vend);
1230
1231 G_start_balance := 0.00;
1232 ELSIF G_critical_indicator = 2 THEN
1233
1234 cleanup_details;
1235
1236
1237 SELECT SYSDATE INTO X_date FROM dual;
1238 X_ret := G_doc_tab(X_i).trans_date - X_date;
1239 IF (X_ret < 0) THEN
1240 X_pastdue := '*';
1241 ELSE
1242 X_pastdue := NULL;
1243 END IF;
1244
1245
1246 INSERT INTO ps_ubkt_dtl(matl_rep_id,
1247 -- item_id,
1248 inventory_item_id,
1249 -- planning_class,
1250 -- whse_code,
1251 organization_id,
1252 start_balance,
1253 past_due,
1254 trans_date,
1255 doc_type,
1256 --- orgn_code,
1257 doc_no,
1258 line_id,
1259 trans_qty,
1260 balance,
1261 critical_ind,
1262 cust_vend)
1263 Values( G_matl_rep_id,
1264 V_item_id,
1265 -- V_planning_class,
1266 -- G_doc_tab(X_i).whse_code,
1267 V_organization_id,
1268 G_start_balance,
1269 X_pastdue,
1270 G_doc_tab(X_i).trans_date,
1271 G_doc_type,
1272 -- G_doc_tab(X_i).orgn_code,
1273 X_doc_no,
1274 G_doc_tab(X_i).line_id,
1275 G_doc_tab(X_i).trans_qty,
1276 balance1,
1277 G_c_ind,
1278 G_cust_vend);
1279
1280 G_start_balance := 0.00;
1281
1282 END IF;
1283
1284 G_c_ind := '';
1285 END LOOP;
1286 G_doc_tab.delete;
1287 dbms_sql.close_cursor(X_doc);
1288
1289 IF X_i = 0 THEN
1290
1291 DELETE FROM ps_matl_hdr
1292 WHERE inventory_item_id = V_item_id
1293 AND matl_rep_id = G_matl_rep_id;
1294 END IF;
1295
1296 -- END IF; /* End if for G_whse_list NOT NULL */
1297
1298 EXCEPTION
1299 WHEN OTHERS THEN
1300 -- X_WHSE_LIST:=SQLERRM;
1301 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error writing to ps_ubkt_dtl'||sqlerrm);
1302
1303 /* b3668927 nsinghi : Closing cursors in exception block. */
1304 IF dbms_sql.is_open(X_doc) THEN
1305 FND_FILE.PUT_LINE(FND_FILE.LOG,'cur_select Is Open');
1306 dbms_sql.close_cursor(X_doc);
1307 END IF;
1308 IF dbms_sql.is_open(X_doc1) THEN
1309 FND_FILE.PUT_LINE(FND_FILE.LOG,'X_doc1 Is Open');
1310 dbms_sql.close_cursor(X_doc1);
1311 END IF;
1312 IF Cur_fpo_doc_no%ISOPEN THEN
1313 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cur_fpo_doc_no Is Open');
1314 CLOSE Cur_fpo_doc_no;
1315 END IF;
1316 IF Cur_prod_doc_no%ISOPEN THEN
1317 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cur_prod_doc_no Is Open');
1318 CLOSE Cur_prod_doc_no;
1319 END IF;
1320 IF Cur_requisition_details%ISOPEN THEN
1321 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cur_requisition_details Is Open');
1322 CLOSE Cur_requisition_details;
1323 END IF;
1324 IF Cur_receiving_details%ISOPEN THEN
1325 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cur_receiving_details Is Open');
1326 CLOSE Cur_receiving_details;
1327 END IF;
1328 IF Cur_shipment_details%ISOPEN THEN
1329 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cur_shipment_details Is Open');
1330 CLOSE Cur_shipment_details;
1331 END IF;
1332 IF Cur_po_doc_no%ISOPEN THEN
1333 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cur_po_doc_no Is Open');
1334 CLOSE Cur_po_doc_no;
1335 END IF;
1336 /*
1337 IF Cur_opso_doc_no%ISOPEN THEN
1338 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cur_opso_doc_no Is Open');
1339 CLOSE Cur_opso_doc_no;
1340 END IF;
1341 */
1342 IF Cur_omso_doc_no%ISOPEN THEN
1343 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cur_omso_doc_no Is Open');
1344 CLOSE Cur_omso_doc_no;
1345 END IF;
1346 /* IF Cur_transfer_doc_no%ISOPEN THEN
1347 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cur_transfer_doc_no Is Open');
1348 CLOSE Cur_transfer_doc_no;
1349 END IF; */
1350 IF get_order_ind_cur%ISOPEN THEN
1351 FND_FILE.PUT_LINE(FND_FILE.LOG,'get_order_ind_cur Is Open');
1352 CLOSE get_order_ind_cur;
1353 END IF;
1354
1355 END ps_data_retrieval;
1356
1357
1358 /*******************End Of Procedure ps_data_retrieval ****************************/
1359
1360 /* Added this new procedure to get the primary and secondary onhand
1361 qty when a list of organization_ids are mentioned. */
1362
1363 /****************************************************************
1364 * NAME
1365 * get_onhand_qty
1366 * SYNOPSIS
1367 * proc get_onhand_qty
1368 * PARAMETERS
1369 * V_item_id - Inventory_Item_Id of Item
1370 * V_organization_id - Organization_id
1371 * DESCRIPTION
1372 * Procedure used to Retrieve onhand qtys
1373 * HISTORY
1374 * Namit 01Mar05 - Initial Version
1375 ****************************************************************/
1376
1377 PROCEDURE get_onhand_qty(
1378 V_item_id NUMBER,
1379 V_organization_id NUMBER
1380 ) IS
1381
1382 l_onhand1 NUMBER;
1383 l_onhand2 NUMBER;
1384 l_non_nettable NUMBER;
1385
1386 Cursor Cur_nettable_ind ( V_schedule_id NUMBER) IS
1387 SELECT NVL(nonnet_ind,0)
1388 FROM ps_schd_hdr
1389 WHERE schedule_id = V_schedule_id;
1390
1391 BEGIN
1392
1393 OPEN Cur_nettable_ind(G_schedule_id);
1394 FETCH Cur_nettable_ind INTO l_non_nettable;
1395 CLOSE Cur_nettable_ind;
1396
1397 IF l_non_nettable = 0 THEN
1398 l_non_nettable := 2;
1399 END IF;
1400
1401 G_nonnet_ind := l_non_nettable;
1402
1403 inv_consigned_validations.get_planning_quantity(
1404 P_INCLUDE_NONNET => l_non_nettable
1405 , P_LEVEL => 1
1406 , P_ORG_ID => V_organization_id
1407 , P_SUBINV => NULL
1408 , P_ITEM_ID => V_item_id
1409 , P_GRADE_CODE => NULL
1410 , X_QOH => l_onhand1
1411 , X_SQOH => l_onhand2);
1412
1413 IF l_onhand1 IS NOT NULL THEN
1414 G_on_hand1 := l_onhand1;
1415 END IF;
1416 IF l_onhand2 IS NOT NULL THEN
1417 G_on_hand2 := l_onhand2;
1418 END IF;
1419
1420 END get_onhand_qty;
1421
1422
1423 /*============================================================================+
1424 | |
1425 | PROCEDURE NAME PSCOMMON_SAFETY_STOCK |
1426 | |
1427 | DESCRIPTION Procedure used to retrieve safety stock information |
1428 | for item for the Bucketed Activity. |
1429 | |
1430 | MODIFICATION HISTORY |
1431 | 05/04/04 Rameshwar ----- created |
1432 | |
1433 +============================================================================*/
1434 PROCEDURE pscommon_safety_stock (V_item_id NUMBER, V_organization_id NUMBER) IS
1435
1436 X_tot_ss NUMBER DEFAULT 0 ;
1437 X_safety_stock NUMBER DEFAULT 0 ;
1438 X_no_safety_stock NUMBER DEFAULT 0 ;
1439
1440 X_unit_ss NUMBER DEFAULT 0;
1441 X_whse_cnt NUMBER ;
1442 X_select1 VARCHAR2(2000);
1443 X_status NUMBER(5);
1444 X_doc NUMBER;
1445 X_row_count NUMBER;
1446
1447
1448 BEGIN
1449
1450 X_select1 :=
1451 ' SELECT NVL(SUM(s1.safety_stock_quantity), 0) total_ss'||
1452 ' FROM mtl_safety_stocks s1 '||
1453 ' WHERE s1.organization_id = to_char(:org_id)'||
1454 ' AND s1.inventory_item_id = to_char(:item_id)'||
1455 ' AND (s1.effectivity_date <= SYSDATE '||
1456 ' AND s1.effectivity_date >= ( '||
1457 ' SELECT NVL(MAX(s2.effectivity_date), SYSDATE) '||
1458 ' FROM mtl_safety_stocks s2 '||
1459 ' WHERE s2.organization_id = s1.organization_id'||
1460 ' AND s2.inventory_item_id = to_char(:item_id)'||
1461 ' AND s2.effectivity_date <= SYSDATE)) ';
1462
1463 X_doc := dbms_sql.open_cursor;
1464 dbms_sql.parse(X_doc, X_select1,dbms_sql.NATIVE);
1465 dbms_sql.bind_variable(X_doc,':item_id',V_item_id);
1466 dbms_sql.bind_variable(X_doc,':org_id',V_organization_id);
1467
1468 dbms_sql.define_column(X_doc, 1, X_tot_ss);
1469 -- dbms_sql.define_column(X_doc, 2, X_no_safety_stock);
1470
1471 X_row_count := dbms_sql.execute(X_doc);
1472
1473
1474 IF dbms_sql.fetch_rows (X_DOC)>0 THEN
1475 dbms_sql.column_value(X_doc, 1, X_tot_ss);
1476 -- dbms_sql.column_value(X_doc, 2, X_no_safety_stock);
1477 G_total_ss := X_tot_ss;
1478 -- G_no_safety_stock:= X_no_safety_stock;
1479 ELSE
1480 G_total_ss := 0;
1481 -- G_no_safety_stock:= 0;
1482 END IF;
1483 dbms_sql.close_cursor (x_doc);
1484
1485 EXCEPTION
1486 WHEN OTHERS THEN
1487 FND_FILE.PUT_LINE ( FND_FILE.LOG, sqlerrm||'pscommon_safety_stock');
1488 IF dbms_sql.is_open(X_doc) THEN
1489 FND_FILE.PUT_LINE(FND_FILE.LOG,'cur_select Is Open');
1490 dbms_sql.close_cursor(X_doc);
1491 END IF;
1492
1493 END pscommon_safety_stock;
1494
1495 /******** End of Procedure pscommon_safety_stock ********************/
1496
1497
1498
1499
1500
1501 /*============================================================================+
1502 | |
1503 | PROCEDURE NAME CLEANUP_DETAILS |
1504 | |
1505 | DESCRIPTION This procedure cleans up details, such as customer |
1506 | vendor information. |
1507 | |
1508 | MODIFICATION HISTORY |
1509 | 05/10/04 Rameshwar ----- created |
1510 | 09/15/04 Teresa Wong B3865101 Added code to support profile to |
1511 | exclude Internal Sales Orders. |
1512 +============================================================================*/
1513 PROCEDURE cleanup_details IS
1514
1515 /* nsinghi MPSCONV Start */
1516 /* OPSO txns will no longer supported. So commenting the code. */
1517 /*
1518 CURSOR Cur_order_details IS
1519 SELECT distinct cs.cust_no
1520 FROM op_ordr_hdr op, op_ordr_dtl od, op_cust_mst cs
1521 WHERE op.order_id = G_doc_id
1522 AND op.order_id = od.order_id
1523 AND od.line_id = G_tranline_id
1524 AND od.shipcust_id = cs.cust_id;
1525 */
1526 /* nsinghi MPSCONV End */
1527
1528 CURSOR Cur_purchase_details IS
1529 --Modified to vendor_name from segment1 as per bug # 11068148
1530 SELECT UNIQUE substr(pv.vendor_name,1,32)
1531 -- UNIQUE pv.segment1
1532 FROM MTL_PARAMETERS mtl,
1533 PO_VENDORS pv,
1534 MTL_SYSTEM_ITEMS mitem,
1535 -- IC_ITEM_MST ic,
1536 PO_PO_SUPPLY_VIEW po
1537 WHERE po.item_id = mitem.inventory_item_id
1538 AND pv.vendor_id = po.vendor_id
1539 AND po.to_organization_id = mitem.organization_id
1540 -- AND mitem.segment1 = ic.item_no
1541 AND mtl.organization_id = po.to_organization_id
1542 AND mtl.process_enabled_flag = 'Y'
1543 AND mitem.inventory_item_flag = 'Y'
1544 -- AND ic.noninv_ind = 0
1545 -- AND ic.experimental_ind = 0
1546 -- AND ic.delete_mark = 0
1547 AND NOT EXISTS
1548 ( SELECT 1 FROM oe_drop_ship_sources odss
1549 WHERE po.PO_HEADER_ID = odss.PO_HEADER_ID
1550 AND po.PO_LINE_ID = odss.PO_LINE_ID )
1551 AND po.po_line_id = G_tranline_id ;
1552
1553 CURSOR Cur_receiving_details IS
1554 --Modified to vendor_name from segment1 as per bug # 11068148
1555 SELECT UNIQUE substr(pv.vendor_name,1,32)
1556 -- UNIQUE pv.segment1
1557 FROM MTL_PARAMETERS mtl,
1558 PO_VENDORS pv,
1559 MTL_SYSTEM_ITEMS mitem,
1560 -- IC_ITEM_MST ic,
1561 PO_RCV_SUPPLY_VIEW po
1562 WHERE po.item_id = mitem.inventory_item_id
1563 AND pv.vendor_id = po.vendor_id
1564 AND po.to_organization_id = mitem.organization_id
1565 -- AND mitem.segment1 = ic.item_no
1566 AND mtl.organization_id = po.to_organization_id
1567 AND mtl.process_enabled_flag = 'Y'
1568 AND mitem.inventory_item_flag = 'Y'
1569 -- AND ic.noninv_ind = 0
1570 -- AND ic.experimental_ind = 0
1571 -- AND ic.delete_mark = 0
1572 AND NOT EXISTS
1573 ( SELECT 1 FROM oe_drop_ship_sources odss
1574 WHERE po.PO_HEADER_ID = odss.PO_HEADER_ID
1575 AND po.PO_LINE_ID = odss.PO_LINE_ID )
1576 AND po.po_line_id = G_tranline_id
1577 AND G_doc_type = 'PRCV'
1578 UNION ALL
1579 --Modified to vendor_name from segment1 as per bug # 11068148
1580 SELECT UNIQUE substr(pv.vendor_name,1,32)
1581 -- UNIQUE pv.segment1
1582 FROM MTL_PARAMETERS mtl,
1583 PO_VENDORS pv,
1584 MTL_SYSTEM_ITEMS mitem,
1585 -- IC_ITEM_MST ic,
1586 RCV_SHIPMENT_HEADERS rsh,
1587 PO_SHIP_RCV_SUPPLY_VIEW po
1588 WHERE po.item_id = mitem.inventory_item_id
1589 AND pv.vendor_id = rsh.vendor_id
1590 AND po.shipment_header_id = rsh.shipment_header_id
1591 AND po.to_organization_id = mitem.organization_id
1592 -- AND mitem.segment1 = ic.item_no
1593 AND mtl.organization_id = po.to_organization_id
1594 AND mtl.process_enabled_flag = 'Y'
1595 AND mitem.inventory_item_flag = 'Y'
1596 -- AND ic.noninv_ind = 0
1597 -- AND ic.experimental_ind = 0
1598 -- AND ic.delete_mark = 0
1599 AND po.shipment_line_id = G_tranline_id ;
1600
1601 CURSOR Cur_shipment_details IS
1602 --Modified to vendor_name from segment1 as per bug # 11068148
1603 SELECT UNIQUE substr(pv.vendor_name,1,32)
1604 -- UNIQUE pv.segment1
1605 FROM MTL_PARAMETERS mtl,
1606 MTL_SYSTEM_ITEMS mitem,
1607 -- IC_ITEM_MST ic,
1608 PO_VENDORS pv,
1609 RCV_SHIPMENT_HEADERS rsh,
1610 PO_SHIP_SUPPLY_VIEW po
1611 WHERE po.item_id = mitem.inventory_item_id
1612 AND pv.vendor_id(+) = rsh.vendor_id
1613 AND po.shipment_header_id = rsh.shipment_header_id
1614 AND po.to_organization_id = mitem.organization_id
1615 -- AND mitem.segment1 = ic.item_no
1616 AND mtl.organization_id = po.to_organization_id
1617 AND mtl.process_enabled_flag = 'Y'
1618 AND mitem.inventory_item_flag = 'Y'
1619 -- AND ic.noninv_ind = 0
1620 -- AND ic.experimental_ind = 0
1621 -- AND ic.delete_mark = 0
1622 AND po.shipment_line_id = G_tranline_id ;
1623
1624
1625 CURSOR Cur_requisition_details IS
1626 SELECT SUBSTRB(prl.suggested_vendor_name,1,40)
1627 FROM MTL_PARAMETERS mtl,
1628 MTL_SYSTEM_ITEMS mitem,
1629 -- IC_ITEM_MST ic,
1630 -- IC_WHSE_MST iwm,
1631 PO_REQUISITION_LINES_ALL prl,
1632 PO_REQ_SUPPLY_VIEW po
1633 WHERE po.item_id = mitem.inventory_item_id
1634 AND po.req_line_id = prl.requisition_line_id
1635 AND po.to_organization_id = mitem.organization_id
1636 -- AND mitem.segment1 = ic.item_no
1637 -- AND po.to_organization_id = iwm.mtl_organization_id
1638 AND mtl.organization_id = po.to_organization_id
1639 AND mtl.process_enabled_flag = 'Y'
1640 AND mitem.inventory_item_flag = 'Y'
1641 -- AND iwm.delete_mark = 0
1642 -- AND ic.noninv_ind = 0
1643 -- AND ic.experimental_ind = 0
1644 -- AND ic.delete_mark = 0
1645 -- AND iwm.orgn_code = G_orgn_code
1646 AND NOT EXISTS
1647 ( SELECT 1 FROM oe_drop_ship_sources odss
1648 WHERE po.REQUISITION_HEADER_ID = odss.REQUISITION_HEADER_ID
1649 AND po.REQ_LINE_ID = odss.REQUISITION_LINE_ID )
1650 AND po.req_line_id = G_tranline_id ;
1651
1652 CURSOR Cur_om_order_details IS
1653 --Modified to name from customer_number as per bug # 11068148
1654 SELECT DISTINCT substr(sold_to_org.name,1,40)
1655 --DISTINCT sold_to_org.customer_number
1656 FROM oe_order_headers_all oh,
1657 oe_order_lines_all ol,
1658 oe_sold_to_orgs_v sold_to_org,
1659 mtl_demand_omoe mtl
1660 WHERE oh.header_id = ol.header_id
1661 AND ol.line_id = mtl.demand_id
1662 AND oh.sold_to_org_id = sold_to_org.organization_id(+)
1663 AND mtl.demand_source_header_id = G_doc_id
1664 AND mtl.open_flag = 'Y'
1665 AND mtl.available_to_mrp = 1
1666 AND mtl.parent_demand_id is NULL
1667 AND mtl.demand_source_type IN (2,8) ;
1668
1669 -- TKW B3865101 9/15/04 Added cursor for the case where Exclude
1670 -- Internal Sales Orders profile was set to Y.
1671 CURSOR Cur_excl_internal_omso_dtl IS
1672 --Modified to name from customer_number as per bug # 11068148
1673 SELECT DISTINCT substr(sold_to_org.name,1,40)
1674 -- DISTINCT sold_to_org.customer_number
1675 FROM oe_order_headers_all oh,
1676 oe_order_lines_all ol,
1677 oe_sold_to_orgs_v sold_to_org,
1678 mtl_demand_omoe mtl
1679 WHERE oh.header_id = ol.header_id
1680 AND ol.line_id = mtl.demand_id
1681 AND oh.sold_to_org_id = sold_to_org.organization_id(+)
1682 AND mtl.demand_source_header_id = G_doc_id
1683 AND mtl.open_flag = 'Y'
1684 AND mtl.available_to_mrp = 1
1685 AND mtl.parent_demand_id is NULL
1686 AND mtl.demand_source_type IN (2,8)
1687 AND nvl(ol.source_document_type_id, 0) <> 10 ;
1688
1689 X_workfield3 VARCHAR2(40);
1690 exclude_internal_omso NUMBER := 0; -- TKW Added for B3865101
1691
1692 BEGIN
1693 exclude_internal_omso := TO_NUMBER(FND_PROFILE.VALUE('GMP_EXCLUDE_INTERNAL_OMSO')); /* B3865101 */
1694
1695 --Retrieve sales order details.
1696 /* nsinghi MPSCONV Start */
1697 /* OPSO txns will no longer supported. So commenting the code. */
1698 /*
1699 IF (G_doc_type = 'OPSO') THEN
1700 OPEN Cur_order_details;
1701 FETCH Cur_order_details INTO X_workfield3;
1702 CLOSE Cur_order_details;
1703 --Retrieve OM sales order details.
1704 */
1705 /* nsinghi MPSCONV End */
1706
1707 IF (G_doc_type = 'OMSO') THEN
1708 -- TKW B3865101 Check profile before getting the details.
1709 IF (exclude_internal_omso = 0) THEN
1710 OPEN Cur_om_order_details;
1711 FETCH Cur_om_order_details INTO X_workfield3;
1712 CLOSE Cur_om_order_details;
1713 ELSE
1714 OPEN Cur_excl_internal_omso_dtl;
1715 FETCH Cur_excl_internal_omso_dtl INTO X_workfield3;
1716 CLOSE Cur_excl_internal_omso_dtl;
1717 END IF;
1718
1719 --Retrieve purchase order details.
1720 ELSIF (G_doc_type = 'PORD') THEN
1721 OPEN Cur_purchase_details;
1722 FETCH Cur_purchase_details INTO X_workfield3;
1723 CLOSE Cur_purchase_details;
1724 ELSIF (G_doc_type = 'PRCV') THEN
1725 OPEN Cur_receiving_details;
1726 FETCH Cur_receiving_details INTO X_workfield3;
1727 CLOSE Cur_receiving_details;
1728 --Retrieve PO/REQ shipment details.
1729 ELSIF (G_doc_type = 'SHMT') THEN
1730 OPEN Cur_shipment_details;
1731 FETCH Cur_shipment_details INTO X_workfield3;
1732 CLOSE Cur_shipment_details;
1733 --Retrieve requisition details.
1734 ELSIF (G_doc_type = 'PREQ') THEN
1735 OPEN Cur_requisition_details;
1736 FETCH Cur_requisition_details INTO X_workfield3;
1737 CLOSE Cur_requisition_details;
1738 --Retrieve production details.
1739 ELSIF (G_doc_type = 'PROD' OR G_doc_type = 'FPO') THEN
1740 X_workfield3 := NULL;
1741 END IF;
1742 G_cust_vend := X_workfield3;
1743
1744 EXCEPTION
1745 WHEN OTHERS THEN
1746 FND_FILE.PUT_LINE(FND_FILE.LOG,'Error in Cleanup details '||sqlerrm);
1747 /* b3668927 nsinghi : Closing cursors in exception block. */
1748 /* IF Cur_order_details%ISOPEN THEN
1749 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cursor Cur_order_details Is Open');
1750 CLOSE Cur_order_details;
1751 END IF;
1752 */
1753 IF Cur_om_order_details%ISOPEN THEN
1754 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cursor Cur_om_order_details Is Open');
1755 CLOSE Cur_om_order_details;
1756 END IF;
1757 IF Cur_purchase_details%ISOPEN THEN
1758 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cursor Cur_purchase_details Is Open');
1759 CLOSE Cur_purchase_details;
1760 END IF;
1761 IF Cur_receiving_details%ISOPEN THEN
1762 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cursor Cur_receiving_details Is Open');
1763 CLOSE Cur_receiving_details;
1764 END IF;
1765 IF Cur_shipment_details%ISOPEN THEN
1766 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cursor Cur_shipment_details Is Open');
1767 CLOSE Cur_shipment_details;
1768 END IF;
1769 IF Cur_requisition_details%ISOPEN THEN
1770 FND_FILE.PUT_LINE(FND_FILE.LOG,'Cursor Cur_requisition_details Is Open');
1771 CLOSE Cur_requisition_details;
1772 END IF;
1773
1774 END cleanup_details;
1775
1776 /******** End of Cleanup_details ********************/
1777
1778 /* ***************************************************************
1779 * NAME
1780 * FUNCTION - ps_generate_xml
1781 * PARAMETERS
1782 *
1783 * DESCRIPTION
1784 * Procedure used to Generate XML for Bucketed Data.
1785 * HISTORY
1786 * Namit 31Mar05 - Initial Version
1787 *************************************************************** */
1788
1789 PROCEDURE ps_generate_xml IS
1790
1791 qryCtx DBMS_XMLGEN.ctxHandle;
1792 result CLOB;
1793 x_stmt VARCHAR2(25000);
1794 seq_stmt VARCHAR2(200);
1795 x_seq_num NUMBER;
1796 l_encoding VARCHAR2(20); /* B7481907 */
1797 l_xml_header VARCHAR2(100); /* B7481907 */
1798 l_offset PLS_INTEGER; /* B7481907 */
1799 temp_clob CLOB; /* B7481907 */
1800 len PLS_INTEGER; /* B7481907 */
1801
1802 BEGIN
1803
1804 -- B7481907 Rajesh Patangya starts
1805 -- The following line of code ensures that XML data
1806 -- generated here uses the right encoding
1807 l_encoding := fnd_profile.value('ICX_CLIENT_IANA_ENCODING');
1808 l_xml_header := '<?xml version="1.0" encoding="'|| l_encoding ||'"?>';
1809 FND_FILE.PUT_LINE ( FND_FILE.LOG,'l_xml_header - '||l_xml_header);
1810 -- B7481907 Rajesh Patangya starts
1811
1812
1813 seq_stmt := NULL;
1814 x_seq_num := 0;
1815
1816 x_stmt := ' SELECT ' ||
1817 ' gmpmpact.organization_code( '||G_orgnanization_id||') master_org, ' ||
1818 ' gmpmpact.schedule( '||G_schedule_id||') schedule, ' ||
1819 ' gmpmpact.category_set( '||G_category_set_id||') category_set, ' ||
1820 ''''||G_fcategory||''''||' fcategory, ' ||
1821 ''''||G_tcategory||''''||' tcategory, ' ||
1822 ''''||G_fbuyer||''''||' fbuyer, ' ||
1823 ''''||G_tbuyer||''''||' tbuyer, ' ||
1824 ''''||G_fplanner||''''||' fplanner, ' ||
1825 ''''||G_tplanner||''''||' tplanner, ' ||
1826 ''''||G_forg||''''||' forg, ' ||
1827 ''''||G_torg||''''||' torg, ' ||
1828 ''''||G_fitem||''''||' fitem, ' ||
1829 ''''||G_titem||''''||' titem, ' ||
1830 ''''||G_ftrans_date||''''||' fdate, ' ||
1831 ''''||G_ttrans_date||''''||' tdate, ' ||
1832 ' CURSOR( ' ||
1833 ' SELECT ' ||
1834 ' gmpmpact.item_name(pmh.inventory_item_id, pmh.organization_id) item_name, ' ||
1835 ' gmpmpact.organization_code (pmh.organization_id) organization_code, ' ||
1836 ' gmpmpact.planner_code (pmh.inventory_item_id, pmh.organization_id) planner_code, ' ||
1837 ' gmpmpact.buyer_name (pmh.inventory_item_id, pmh.organization_id) buyer_name, ' ||
1838 ' gmpmpact.onhand_qty (pmh.inventory_item_id, pmh.organization_id) onhand_qty, ' ||
1839 ' gmpmpact.unit_of_measure(pmh.inventory_item_id, pmh.organization_id) primary_uom_code, ' ||
1840 ' gmpmpact.category(pmh.category_id) category, ' ||
1841 ' CURSOR( ' ||
1842 ' SELECT pud.line_id line_id, ' ||
1843 ' pud.matl_rep_id matl_rep_id, ' ||
1844 ' pud.doc_type doc_type, ' ||
1845 ' pud.doc_no doc_no, ' ||
1846 ' pud.start_balance start_balance, ' ||
1847 ' pud.past_due past_due, ' ||
1848 ' pud.trans_date trans_date, ' ||
1849 ' pud.trans_qty trans_qty, ' ||
1850 ' pud.balance balance, ' ||
1851 ' pud.critical_ind critical_ind, ' ||
1852 ' pud.cust_vend cust_vend, ' ||
1853 ' pud.inventory_item_id inventory_item_id, ' ||
1854 ' gmpmpact.organization_code (pud.organization_id) organization_code ' ||
1855 ' FROM ps_ubkt_dtl pud ' ||
1856 ' WHERE pud.inventory_item_id = pmh.inventory_item_id ' ||
1857 ' AND pud.organization_id = pmh.organization_id ' ||
1858 ' AND pud.matl_rep_id = pmh.matl_rep_id ' ||
1859 --Modified order by clause as per bug # 11072275
1860 --' ORDER BY pud.inventory_item_id, pud.organization_id, pud.trans_date, pud.doc_type ' ||
1861 ' ORDER BY pud.inventory_item_id, pud.organization_id, pud.trans_date, rownum ' ||
1862 ' ) DETAIL ' ||
1863 ' FROM ps_matl_hdr pmh ' ||
1864 ' WHERE pmh.matl_rep_id = ' ||G_matl_rep_id||
1865 ' ORDER BY pmh.inventory_item_id, pmh.organization_id ' ||
1866 ' ) HEADER ' ||
1867 ' FROM DUAL ';
1868
1869 -- DELETE FROM GMP_UNBUCKETED_XML_GTMP;
1870 -- FND_FILE.PUT_LINE ( FND_FILE.LOG, x_stmt);
1871
1872 -- B7481907 Rajesh Patangya starts
1873 DBMS_LOB.createtemporary(temp_clob, TRUE);
1874 DBMS_LOB.createtemporary(result, TRUE);
1875
1876 qryctx := dbms_xmlgen.newcontext(x_stmt);
1877
1878 -- generate XML data
1879 DBMS_XMLGEN.getXML (qryctx, temp_clob, DBMS_XMLGEN.none);
1880 l_offset := DBMS_LOB.INSTR (lob_loc => temp_clob,
1881 pattern => '>',
1882 offset => 1,
1883 nth => 1);
1884 FND_FILE.PUT_LINE ( FND_FILE.LOG,'l_offset - '||l_offset);
1885
1886 -- Remove the header
1887 DBMS_LOB.erase (temp_clob, l_offset,1);
1888
1889 -- The following line of code ensures that XML data
1890 -- generated here uses the right encoding
1891 DBMS_LOB.writeappend (result, length(l_xml_header), l_xml_header);
1892
1893 -- Append the rest to xml output
1894 DBMS_LOB.append (result, temp_clob);
1895
1896 -- close context and free memory
1897 DBMS_XMLGEN.closeContext(qryctx);
1898 DBMS_LOB.FREETEMPORARY (temp_clob);
1899 -- B7481907 Rajesh Patangya Ends
1900
1901 seq_stmt := 'select gmp_matl_rep_id_s.nextval from dual ';
1902 EXECUTE IMMEDIATE seq_stmt INTO x_seq_num ;
1903
1904 INSERT INTO gmp_unbucketed_xml_temp(ubckt_matl_xml_id, xml_file) VALUES(x_seq_num, result);
1905 ps_generate_output(x_seq_num);
1906
1907 EXCEPTION
1908 WHEN OTHERS THEN
1909 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Exception in procedure ps_generate_xml :'||SQLERRM);
1910
1911 END ps_generate_xml;
1912
1913 /* ***************************************************************
1914 * NAME
1915 * FUNCTION - schedule
1916 * PARAMETERS
1917 * p_schedule_id - Schedule Id
1918 * DESCRIPTION
1919 * Function used to Schedule Name
1920 * HISTORY
1921 * Namit 31Mar05 - Initial Version
1922 *************************************************************** */
1923
1924 FUNCTION schedule (p_schedule_id NUMBER)
1925 RETURN VARCHAR2 IS
1926 v_schedule_name VARCHAR2(16);
1927 BEGIN
1928
1929 SELECT schedule INTO v_schedule_name
1930 FROM ps_schd_hdr
1931 WHERE schedule_id = p_schedule_id;
1932
1933 RETURN v_schedule_name;
1934
1935 EXCEPTION
1936 WHEN OTHERS THEN
1937 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function schedule '||SQLERRM);
1938 END schedule;
1939
1940 /* ***************************************************************
1941 * NAME
1942 * FUNCTION - category_set
1943 * PARAMETERS
1944 * p_category_set_id - Category Set Id
1945 * DESCRIPTION
1946 * Function used to Retrieve Category Name
1947 * HISTORY
1948 * Namit 31Mar05 - Initial Version
1949 *************************************************************** */
1950
1951 FUNCTION category_set (p_category_set_id NUMBER)
1952 RETURN VARCHAR2 IS
1953 v_category_set_name VARCHAR2(30);
1954 BEGIN
1955
1956 SELECT category_set_name INTO v_category_set_name
1957 FROM mtl_category_sets
1958 WHERE category_set_id = p_category_set_id;
1959
1960 RETURN v_category_set_name;
1961
1962 EXCEPTION
1963 WHEN OTHERS THEN
1964 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function category_set '||SQLERRM);
1965 END category_set;
1966
1967 /* ***************************************************************
1968 * NAME
1969 * FUNCTION - item_name
1970 * PARAMETERS
1971 * V_item_id - Inventory_Item_Id of Item
1972 * V_organization_id - Organization_id
1973 * DESCRIPTION
1974 * Function used to Retrieve Item Name
1975 * HISTORY
1976 * Namit 31Mar05 - Initial Version
1977 *************************************************************** */
1978
1979 FUNCTION item_name (p_inventory_item_id NUMBER, p_organization_id NUMBER)
1980 RETURN VARCHAR2 IS
1981 v_item_name VARCHAR2(240);
1982 BEGIN
1983
1984 SELECT concatenated_segments INTO v_item_name
1985 FROM mtl_system_items_kfv
1986 WHERE inventory_item_id = p_inventory_item_id
1987 AND organization_id = p_organization_id;
1988
1989 RETURN v_item_name;
1990
1991 EXCEPTION
1992 WHEN OTHERS THEN
1993 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function item_name '||SQLERRM);
1994 END item_name;
1995
1996 /* ***************************************************************
1997 * NAME
1998 * FUNCTION - organization_code
1999 * PARAMETERS
2000 * V_organization_id - Organization_id
2001 * DESCRIPTION
2002 * Function used to Retrieve Organization Code
2003 * HISTORY
2004 * Namit 31Mar05 - Initial Version
2005 *************************************************************** */
2006
2007 FUNCTION organization_code (p_organization_id NUMBER)
2008 RETURN VARCHAR2 IS
2009 v_org_code VARCHAR2(3);
2010 BEGIN
2011
2012 SELECT organization_code INTO v_org_code
2013 FROM mtl_parameters
2014 WHERE organization_id = p_organization_id;
2015
2016 RETURN v_org_code;
2017
2018 EXCEPTION
2019 WHEN OTHERS THEN
2020 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function organization_code '||SQLERRM);
2021 END organization_code;
2022
2023 /* ***************************************************************
2024 * NAME
2025 * FUNCTION - planner_code
2026 * PARAMETERS
2027 * V_item_id - Inventory_Item_Id of Item
2028 * V_organization_id - Organization_id
2029 * DESCRIPTION
2030 * Function used to Retrieve Planner Code
2031 * HISTORY
2032 * Namit 31Mar05 - Initial Version
2033 *************************************************************** */
2034
2035 FUNCTION planner_code (p_inventory_item_id NUMBER, p_organization_id NUMBER)
2036 RETURN VARCHAR2 IS
2037 v_planner_code VARCHAR2(10);
2038 BEGIN
2039
2040 SELECT planner_code INTO v_planner_code
2041 FROM mtl_system_items
2042 WHERE inventory_item_id = p_inventory_item_id
2043 AND organization_id = p_organization_id;
2044
2045 RETURN v_planner_code;
2046
2047 EXCEPTION
2048 WHEN NO_DATA_FOUND THEN RETURN NULL;
2049 WHEN OTHERS THEN
2050 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function planner_code '||SQLERRM);
2051 END planner_code;
2052
2053 /* ***************************************************************
2054 * NAME
2055 * FUNCTION - buyer_name
2056 * PARAMETERS
2057 * V_item_id - Inventory_Item_Id of Item
2058 * V_organization_id - Organization_id
2059 * DESCRIPTION
2060 * Function used to Retrieve Item Buyer Name
2061 * HISTORY
2062 * Namit 31Mar05 - Initial Version
2063 *************************************************************** */
2064
2065 FUNCTION buyer_name (p_inventory_item_id NUMBER, p_organization_id NUMBER)
2066 RETURN VARCHAR2 IS
2067 v_buyer_name VARCHAR2(240);
2068 BEGIN
2069
2070 SELECT he.full_name INTO v_buyer_name
2071 FROM mtl_system_items msi, hr_employees he
2072 WHERE inventory_item_id = p_inventory_item_id
2073 AND organization_id = p_organization_id
2074 AND msi.buyer_id = he.employee_id;
2075
2076 RETURN v_buyer_name;
2077
2078 EXCEPTION
2079 WHEN NO_DATA_FOUND THEN RETURN NULL;
2080 WHEN OTHERS THEN
2081 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function buyer_name '||SQLERRM);
2082 END buyer_name;
2083
2084 /* ***************************************************************
2085 * NAME
2086 * FUNCTION - onhand_qty
2087 * PARAMETERS
2088 * V_item_id - Inventory_Item_Id of Item
2089 * V_organization_id - Organization_id
2090 * DESCRIPTION
2091 * Function used to Retrieve Item onhand qty in Primary UOM
2092 * HISTORY
2093 * Namit 31Mar05 - Initial Version
2094 *************************************************************** */
2095
2096 FUNCTION onhand_qty (p_inventory_item_id NUMBER, p_organization_id NUMBER)
2097 RETURN NUMBER IS
2098 v_onhand_qty NUMBER := 0;
2099 l_onhand1 NUMBER;
2100 l_onhand2 NUMBER;
2101
2102 BEGIN
2103
2104 inv_consigned_validations.get_planning_quantity(
2105 P_INCLUDE_NONNET => G_nonnet_ind
2106 , P_LEVEL => 1
2107 , P_ORG_ID => p_organization_id
2108 , P_SUBINV => NULL
2109 , P_ITEM_ID => p_inventory_item_id
2110 , P_GRADE_CODE => NULL
2111 , X_QOH => l_onhand1
2112 , X_SQOH => l_onhand2);
2113
2114 IF l_onhand1 IS NOT NULL THEN
2115 v_onhand_qty := l_onhand1;
2116 END IF;
2117
2118 RETURN v_onhand_qty;
2119
2120 EXCEPTION
2121 WHEN OTHERS THEN
2122 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function onhand_qty '||SQLERRM);
2123 END onhand_qty;
2124
2125 /* ***************************************************************
2126 * NAME
2127 * FUNCTION - unit_of_measure
2128 * PARAMETERS
2129 * V_item_id - Inventory_Item_Id of Item
2130 * V_organization_id - Organization_id
2131 * DESCRIPTION
2132 * Function used to Retrieve Primary UOM Code.
2133 * HISTORY
2134 * Namit 31Mar05 - Initial Version
2135 *************************************************************** */
2136
2137 FUNCTION unit_of_measure (p_inventory_item_id NUMBER, p_organization_id NUMBER)
2138 RETURN VARCHAR2 IS
2139 v_uom_code VARCHAR2(3);
2140 BEGIN
2141
2142 SELECT primary_uom_code INTO v_uom_code
2143 FROM mtl_system_items
2144 WHERE inventory_item_id = p_inventory_item_id
2145 AND organization_id = p_organization_id;
2146
2147 RETURN v_uom_code;
2148
2149 EXCEPTION
2150 WHEN OTHERS THEN
2151 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function unit_of_measure '||SQLERRM);
2152 END unit_of_measure;
2153
2154 /* ***************************************************************
2155 * NAME
2156 * FUNCTION - category
2157 * PARAMETERS
2158 * p_category_id - Category Id
2159 * DESCRIPTION
2160 * Function used to Retrieve Category Name
2161 * HISTORY
2162 * Namit 31Mar05 - Initial Version
2163 *************************************************************** */
2164
2165 FUNCTION category (p_category_id NUMBER)
2166 RETURN VARCHAR2 IS
2167 v_category VARCHAR2(240);
2168 BEGIN
2169
2170 SELECT concatenated_segments INTO v_category
2171 FROM mtl_categories_kfv
2172 WHERE category_id = p_category_id;
2173
2174 RETURN v_category;
2175
2176 EXCEPTION
2177 WHEN NO_DATA_FOUND THEN RETURN NULL;
2178 WHEN OTHERS THEN
2179 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Error in function category '||SQLERRM);
2180 END category;
2181
2182 /* ***************************************************************
2183 * NAME
2184 * PROCEDURE - ps_generate_output
2185 * PARAMETERS
2186 * DESCRIPTION
2187 * Procedure used generate the final output.
2188 * HISTORY
2189 * Namit 31Mar05 - Initial Version
2190 *************************************************************** */
2191
2192 PROCEDURE ps_generate_output (
2193 p_sequence_num IN NUMBER
2194 )
2195 IS
2196
2197 l_conc_id NUMBER;
2198 l_req_id NUMBER;
2199 l_phase VARCHAR2(20);
2200 l_status_code VARCHAR2(20);
2201 l_dev_phase VARCHAR2(20);
2202 l_dev_status VARCHAR2(20);
2203 l_message VARCHAR2(20);
2204 l_status BOOLEAN;
2205
2206
2207 BEGIN
2208
2209 l_conc_id := FND_REQUEST.SUBMIT_REQUEST('GMP','GMPUBCKT','', '',FALSE,
2210 p_sequence_num, chr(0),'','','','','','','','','','','',
2211 '','','','','','','','','','','','','','','',
2212 '','','','','','','','','','',
2213 '','','','','','','','','','',
2214 '','','','','','','','','','',
2215 '','','','','','','','','','',
2216 '','','','','','','','','','',
2217 '','','','','','','','','','',
2218 '','','','','','','','','','');
2219
2220 IF l_conc_id = 0 THEN
2221 G_log_text := FND_MESSAGE.GET;
2222 FND_FILE.PUT_LINE ( FND_FILE.LOG,G_log_text);
2223 ELSE
2224 COMMIT ;
2225 END IF;
2226
2227 IF l_conc_id <> 0 THEN
2228
2229 l_status := fnd_concurrent.WAIT_FOR_REQUEST
2230 (
2231 REQUEST_ID => l_conc_id,
2232 INTERVAL => 30,
2233 MAX_WAIT => 900,
2234 PHASE => l_phase,
2235 STATUS => l_status_code,
2236 DEV_PHASE => l_dev_phase,
2237 DEV_STATUS => l_dev_status,
2238 MESSAGE => l_message
2239 );
2240
2241 DELETE FROM gmp_unbucketed_xml_temp WHERE ubckt_matl_xml_id = p_sequence_num;
2242
2243 /* Bug: 6609251 Vpedarla added a NULL parameters for the submition of the FND request for XDOREPPB */
2244 l_req_id := FND_REQUEST.SUBMIT_REQUEST('XDO','XDOREPPB','', '',FALSE,'',
2245 l_conc_id,554,G_template,
2246 G_template_locale,'N','','','','','','','','',
2247 '','','','','','','','','','','','','','','',
2248 '','','','','','','','','','',
2249 '','','','','','','','','','',
2250 '','','','','','','','','','',
2251 '','','','','','','','','','',
2252 '','','','','','','','','','',
2253 '','','','','','','','','','',
2254 '','','','','','','','','','');
2255 END IF;
2256
2257 EXCEPTION
2258 WHEN OTHERS THEN
2259 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Exception in procedure ps_generate_output '||SQLERRM);
2260 END ps_generate_output;
2261
2262 /* ***************************************************************
2263 * NAME
2264 * PROCEDURE - xml_transfer
2265 * PARAMETERS
2266 * DESCRIPTION
2267 * Procedure used provide the XML as output of the concurrent program.
2268 * HISTORY
2269 * Namit 31Mar05 - Initial Version
2270 * Bug 9094869 Vpedarla Increased size of file_varchar2 to 1000.
2271 *************************************************************** */
2272
2273 PROCEDURE xml_transfer (
2274 errbuf OUT NOCOPY VARCHAR2,
2275 retcode OUT NOCOPY VARCHAR2,
2276 p_sequence_num IN NUMBER
2277 )IS
2278
2279 l_file CLOB;
2280 file_varchar2 VARCHAR2(1000);
2281 l_len NUMBER;
2282 l_limit NUMBER;
2283
2284 BEGIN
2285
2286 SELECT xml_file INTO l_file
2287 FROM gmp_unbucketed_xml_temp
2288 WHERE ubckt_matl_xml_id = p_sequence_num;
2289 l_limit:= 1;
2290
2291 l_len := DBMS_LOB.GETLENGTH (l_file);
2292 LOOP
2293 IF l_len > l_limit THEN
2294 file_varchar2 := DBMS_LOB.SUBSTR (l_file,10,l_limit);
2295 FND_FILE.PUT(FND_FILE.OUTPUT,file_varchar2);
2296 FND_FILE.PUT(FND_FILE.LOG, file_varchar2);
2297 file_varchar2 := NULL;
2298 l_limit:= l_limit + 10;
2299 ELSE
2300 file_varchar2 := DBMS_LOB.SUBSTR (l_file,10,l_limit);
2301 FND_FILE.PUT(FND_FILE.OUTPUT, file_varchar2);
2302 FND_FILE.PUT(FND_FILE.LOG,file_varchar2);
2303 file_varchar2 := NULL;
2304 EXIT;
2305 END IF;
2306 END LOOP;
2307 EXCEPTION
2308 WHEN OTHERS THEN
2309 FND_FILE.PUT_LINE ( FND_FILE.LOG, 'Exception in procedure xml_transfer '||SQLERRM);
2310 END;
2311
2312 END GMPMPACT;