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