[Home] [Help]
PACKAGE BODY: APPS.PKG_GMP_BUCKET_DATA
Source
1 package body PKG_GMP_BUCKET_DATA as
2 /* $Header: GMPBCKTB.pls 120.3 2011/09/23 13:22:52 vkinduri ship $ */
3
4 FUNCTION mr_bucket_data (V_schedule NUMBER,
5 V_mrp_id NUMBER,
6 V_item_id NUMBER,
7 V_whse_list VARCHAR2, /* List with seperat */
8 V_on_hand NUMBER,
9 V_total_ss NUMBER,
10 V_matl_rep_id NUMBER) RETURN NUMBER IS
11 TYPE trans_date_type IS TABLE OF mr_tran_tbl.trans_date%TYPE
12 INDEX BY BINARY_INTEGER;
13 TYPE document_type IS TABLE OF sy_docs_mst.doc_type%TYPE
14 INDEX BY BINARY_INTEGER;
15 TYPE trans_qty_type IS TABLE OF mr_tran_tbl.trans_qty%TYPE
16 INDEX BY BINARY_INTEGER;
17 TYPE orgn_code_type IS TABLE OF sy_orgn_mst.orgn_code%TYPE
18 INDEX BY BINARY_INTEGER;
19 TYPE period_start_date_type IS TABLE OF ps_matl_dtl.perd_end_date%TYPE
20 INDEX BY BINARY_INTEGER;
21 TYPE period_end_date_type IS TABLE OF ps_matl_dtl.perd_end_date%TYPE
22 INDEX BY BINARY_INTEGER;
23 TYPE period_name_type IS TABLE OF ps_matl_dtl.perd_name%TYPE
24 INDEX BY BINARY_INTEGER;
25 trans_date_tab TRANS_DATE_TYPE;
26 doc_type_tab DOCUMENT_TYPE;
27 trans_qty_tab TRANS_QTY_TYPE;
28 orgn_code_tab ORGN_CODE_TYPE;
29 period_start_date_tab PERIOD_START_DATE_TYPE;
30 period_end_date_tab PERIOD_END_DATE_TYPE;
31 period_name_tab PERIOD_NAME_TYPE;
32
33 CURSOR Cur_trans_dtl IS
34 SELECT doc_type,trans_date,orgn_code,trans_qty
35 FROM mr_tran_tbl
36 WHERE mrp_id =V_mrp_id
37 AND item_id =V_item_id
38 AND INSTR(V_whse_list, whse_code) <> 0
39 ORDER BY trans_date asc, trans_qty desc;
40 CURSOR Cur_schedule IS
41 SELECT no_days, no_weeks, no_4weeks, no_13weeks
42 FROM ps_schd_hdr
43 WHERE schedule_id = V_schedule
44 AND delete_mark = 0;
45 CURSOR Cur_check_hdr IS
46 SELECT matl_rep_id
47 FROM ps_matl_hdr
48 WHERE matl_rep_id = V_matl_rep_id;
49 CURSOR Cur_matl_rep_id IS
50 SELECT gem5_matl_rep_id_s.NEXTVAL
51 FROM dual;
52 X_rows NUMBER DEFAULT 0;
53 prior_to_one NUMBER DEFAULT 0;
54 X_trans_date DATE;
55 X_qty NUMBER DEFAULT 0;
56 X_whse_code VARCHAR2(4);
57 X_retvar NUMBER(5);
58 X_workdate1 DATE;
59 X_workdate2 DATE;
60 X_period VARCHAR2(40);
61 X_no_days NUMBER(5);
62 X_no_weeks NUMBER(5);
63 X_no_4weeks NUMBER(5);
64 X_no_13weeks NUMBER(5);
65 X_date1 DATE;
66 X_date2 DATE;
67 X_day1 VARCHAR2(30);
68 X_n NUMBER(5);
69 X_i NUMBER(5);
70 X_j NUMBER(5);
71 X_dcount NUMBER;
72 X_wcount NUMBER;
73 X_mcount NUMBER;
74 X_qcount NUMBER;
75 X_matl_rep_id NUMBER;
76 X_tot_periods NUMBER(5);
77 X_sales_orders NUMBER DEFAULT 0;
78 X_forecast NUMBER DEFAULT 0;
79 X_dep_demand NUMBER DEFAULT 0;
80 X_plnd_ingred NUMBER DEFAULT 0;
81 X_po_receipts NUMBER DEFAULT 0;
82 X_sched_prod NUMBER DEFAULT 0;
83 X_plnd_prod NUMBER DEFAULT 0;
84 X_total_demand NUMBER DEFAULT 0;
85 X_total_supply NUMBER DEFAULT 0;
86 X_net_ss_reqmt NUMBER DEFAULT 0;
87 X_ending_bal NUMBER DEFAULT 0;
88 X_prev_balance NUMBER DEFAULT 0;
89 /* B1159495 Inventory Transfers */
90 X_plnd_transfer_out NUMBER DEFAULT 0;
91 X_plnd_transfer_in NUMBER DEFAULT 0;
92 /* B1781498 */
93 X_sched_transfer_out NUMBER DEFAULT 0;
94 X_sched_transfer_in NUMBER DEFAULT 0;
95 X_planned_purch NUMBER DEFAULT 0;
96 X_preq_supply NUMBER DEFAULT 0;
97 X_prcv_supply NUMBER DEFAULT 0;
98 X_shmt_supply NUMBER DEFAULT 0;
99 X_other_demand NUMBER DEFAULT 0;
100 period_name VARCHAR2(40); /*B3021669 - Sowmya */
101
102 trans_rec Cur_trans_dtl%ROWTYPE;
103 BEGIN
104 OPEN Cur_trans_dtl;
105 LOOP
106 FETCH Cur_trans_dtl INTO trans_rec;
107 EXIT WHEN Cur_trans_dtl%NOTFOUND;
108 X_rows := X_rows + 1;
109 doc_type_tab(X_rows) := trans_rec.doc_type;
110 trans_date_tab(X_rows) := trans_rec.trans_date;
111 orgn_code_tab(X_rows) := trans_rec.orgn_code;
112 trans_qty_tab(X_rows) := trans_rec.trans_qty;
113 END LOOP;
114 CLOSE Cur_trans_dtl;
115
116 -- TKW B3034938 The changes made in bugs 2348778 and 2626977 were commented
117 -- since the date returned was null. The min date was set as a constant
118 -- date in gma_global_grp.
119 /*
120 --BEGIN BUG#2348778 RajaSekhar
121 X_workdate1 := FND_DATE.string_to_date(GMA_CORE_PKG.get_date_constant('SY$MIN_DATE'), 'YYYY/MM/DD HH24:MI:SS');
122 -- Bug #2626977 (JKB) Removed reference to date profile above.
123 --END BUG#2348778
124 */
125
126 -- X_workdate1 := GMA_GLOBAL_GRP.SY$MIN_DATE;
127 /* Use the hard coded value for bug 12399670 Vijay Induri */
128 X_workdate1 := TO_DATE ( (SYSDATE-(17*365)), 'DD/MM/YYYY HH24:MI:SS') ;
129
130 X_workdate2 := TO_DATE(TO_CHAR(SYSDATE,'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
131 FND_MESSAGE.SET_NAME('GMP','PS_PASTDUE');
132 X_period := FND_MESSAGE.GET;
133 IF (X_rows = 0) THEN
134 RETURN(X_rows);
135 END IF;
136
137 OPEN Cur_schedule;
138 FETCH Cur_schedule INTO X_no_days, X_no_weeks, X_no_4weeks, X_no_13weeks;
139 CLOSE Cur_schedule;
140 X_date1 := SYSDATE;
141 X_date2 := X_date1 - 1;
142 X_date1 := X_date2 + X_no_days;
143 X_day1 := INITCAP(TO_CHAR(X_date1,'DAY')); /* Day */
144 X_n := TO_CHAR(X_date1,'D'); /* Whay Day of a week */
145 X_no_days := X_no_days + 7 - X_n + 1;
146 X_tot_periods := X_no_days + X_no_weeks + X_no_4weeks + X_no_13weeks;
147 period_start_date_tab(1) := X_workdate1;
148 period_end_date_tab(1) := TO_DATE(TO_CHAR(X_date2,'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS'); /* yesterday */
149 FND_MESSAGE.SET_NAME('GMP','PS_PASTDUE');
150 period_name_tab(1) := FND_MESSAGE.GET;
151
152 -- TKW 12/26/2003 B3337215 - Port B3306526 to 11.5.10L.
153 -- Modified following condition for the case X_no_days = 1 to work
154 IF (X_no_days > 1) THEN
155 -- IF (X_no_days <> 0) THEN
156 FOR X_i IN 2..X_no_days
157 LOOP
158 X_j := X_i - 1;
159 X_dcount := X_i + 1;
160 X_date1 := period_end_date_tab(X_j);
161 period_start_date_tab(X_i) := X_date1;
162 X_date2 := period_start_date_tab(X_i) + 1;
163 period_end_date_tab(X_i) := TO_DATE(TO_CHAR(X_date2,'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
164 /*period_name_tab(X_i) := INITCAP(TO_CHAR(X_date2,'DAY'));*/
165
166 /*B3732658 - Added a new parameter 'NLS_DATE_LANGUAGE=ENGLISH' to the period name below, so that whatever might be the
167 language to which the database is set to the period name is fetched in ENGLISH. This period name is used further for
168 fetching the days from the message dictionary*/
169 period_name := trim(UPPER(TO_CHAR(X_date2,'DAY','NLS_DATE_LANGUAGE=ENGLISH'))); /*B3732658*/
170
171 /*B3021669 - Sowmya - GMP:GMP:DAYS OF THE WEEK UNTRANSLATED IN MPS MATERIAL ACTIVITY INQUIRY*/
172 /* Based on the period name the day will be picked from the message dictionary.
173 This change has been done to facilitate the translation of messages*/
174 IF ( period_name = 'SUNDAY') THEN
175 FND_MESSAGE.SET_NAME('GMP','PS_SUNDAY');
176 period_name_tab(X_i) := FND_MESSAGE.GET;
177 ELSIF ( period_name = 'MONDAY') THEN
178 FND_MESSAGE.SET_NAME('GMP','PS_MONDAY');
179 period_name_tab(X_i) := FND_MESSAGE.GET;
180 ELSIF ( period_name = 'TUESDAY') THEN
181 FND_MESSAGE.SET_NAME('GMP','PS_TUESDAY');
182 period_name_tab(X_i) := FND_MESSAGE.GET;
183 ELSIF ( period_name = 'WEDNESDAY') THEN
184 FND_MESSAGE.SET_NAME('GMP','PS_WEDNESDAY');
185 period_name_tab(X_i) := FND_MESSAGE.GET;
186 ELSIF ( period_name = 'THURSDAY') THEN
187 FND_MESSAGE.SET_NAME('GMP','PS_THURSDAY');
188 period_name_tab(X_i) := FND_MESSAGE.GET;
189 ELSIF ( period_name = 'FRIDAY') THEN
190 FND_MESSAGE.SET_NAME('GMP','PS_FRIDAY');
191 period_name_tab(X_i) := FND_MESSAGE.GET;
192 ELSIF ( period_name = 'SATURDAY') THEN
193 FND_MESSAGE.SET_NAME('GMP','PS_SATURDAY');
194 period_name_tab(X_i) := FND_MESSAGE.GET;
195 END IF;
196
197 END LOOP;
198 ELSE
199 X_dcount := 2;
200 END IF;
201 IF (X_no_weeks <> 0) THEN
202 FOR X_i IN X_dcount..(X_no_days + X_no_weeks)
203 LOOP
204 X_j := X_i - 1;
205 X_wcount := X_i + 1;
206 X_date1 := period_end_date_tab(X_j);
207 period_start_date_tab(X_i) := X_date1;
208 X_date2 := period_start_date_tab(X_i) + 7;
209 period_end_date_tab(X_i) := TO_DATE(TO_CHAR(X_date2,'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
210 FND_MESSAGE.SET_NAME('GMP','PS_WEEK');
211 period_name_tab(X_i) := FND_MESSAGE.GET||' '||TO_CHAR(X_date2,'WW');
212 END LOOP;
213 ELSE
214 IF (X_no_days = 0) THEN
215 X_wcount := 2;
216 ELSE
217 X_wcount := X_dcount;
218 END IF;
219 END IF;
220
221 IF (X_no_4weeks <> 0) THEN
222 FOR X_i IN X_wcount..(X_no_days + X_no_weeks + X_no_4weeks)
223 LOOP
224 X_j := X_i - 1;
225 X_mcount := X_i + 1;
226 X_date1 := period_end_date_tab(X_j);
227 period_start_date_tab(X_i) := X_date1;
228 X_date2 := period_start_date_tab(X_i) + 28;
229 period_end_date_tab(X_i) := TO_DATE(TO_CHAR(X_date2,'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
230 FND_MESSAGE.SET_NAME('GMP','PS_WEEK');
231 period_name_tab(X_i) := FND_MESSAGE.GET||' '||LPAD(TO_CHAR(TO_NUMBER(TO_CHAR(X_date1,'WW'))+1),2,'0')||'-'||TO_CHAR(X_date2,'WW');
232 END LOOP;
233 ELSE
234 IF (X_no_days = 0 AND X_no_weeks = 0) THEN
235 X_mcount := 2;
236 ELSIF (X_no_weeks = 0) THEN
237 X_mcount := X_dcount;
238 ELSE
239 X_mcount := X_wcount;
240 END IF;
241 END IF;
242
243 IF (X_no_13weeks <> 0) THEN
244 FOR X_i IN X_mcount..(X_no_days + X_no_weeks + X_no_4weeks + X_no_13weeks)
245 LOOP
246 X_j := X_i - 1;
247 X_qcount := X_i + 1;
248 X_date1 := period_end_date_tab(X_j);
249 period_start_date_tab(X_i) := X_date1;
250 X_date2 := period_start_date_tab(X_i) + 91;
251 period_end_date_tab(X_i) := TO_DATE(TO_CHAR(X_date2,'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
252 FND_MESSAGE.SET_NAME('GMP','PS_WEEK');
253 period_name_tab(X_i) := FND_MESSAGE.GET||' '||LPAD(TO_CHAR(TO_NUMBER(TO_CHAR(X_date1,'WW'))+1),2,'0')||'-'||TO_CHAR(X_date2,'WW');
254 END LOOP;
255 END IF;
256
257 OPEN Cur_check_hdr;
258 FETCH Cur_check_hdr INTO X_matl_rep_id;
259 IF (Cur_check_hdr%NOTFOUND) THEN
260 OPEN Cur_matl_rep_id;
261 FETCH Cur_matl_rep_id INTO X_matl_rep_id;
262 CLOSE Cur_matl_rep_id;
263 INSERT INTO ps_matl_hdr (matl_rep_id, item_id)
264 VALUES (X_matl_rep_id, V_item_id);
265 ELSE
266 X_matl_rep_id := V_matl_rep_id;
267 END IF;
268 CLOSE Cur_check_hdr;
269
270 IF (V_matl_rep_id IS NOT NULL) THEN
271 DELETE
272 FROM ps_matl_dtl
273 WHERE matl_rep_id = V_matl_rep_id
274 AND item_id = V_item_id;
275 END IF;
276
277 IF (INSTR(V_whse_list,',') <> 0) THEN
278 X_whse_code := FND_PROFILE.VALUE('SY$ALL');
279 ELSE
280 X_whse_code := REPLACE(V_whse_list, '''', '');
281 END IF;
282
283 prior_to_one := 1 ;
284 FOR X_j IN 1..X_tot_periods
285 LOOP
286 X_sales_orders := 0;
287 X_forecast := 0;
288 X_dep_demand := 0;
289 X_plnd_ingred := 0;
290 X_total_demand := 0;
291 X_total_supply := 0;
292 X_po_receipts := 0;
293 X_sched_prod := 0;
297 X_net_ss_reqmt := 0;
294 X_plnd_prod := 0;
295 X_planned_purch := 0;
296 X_ending_bal := 0;
298 /* B1159495 Inventory Transfers */
299 X_plnd_transfer_out := 0;
300 X_plnd_transfer_in := 0;
301 /* B1781498 */
302 X_sched_transfer_out := 0;
303 X_sched_transfer_in := 0;
304 X_planned_purch := 0;
305 X_preq_supply := 0;
306 X_prcv_supply := 0;
307 X_shmt_supply := 0;
308 X_other_demand := 0;
309
310
311 -- FOR X_i IN 1..X_rows /* transaction table */
312 FOR X_i IN prior_to_one..X_rows /* transaction table */
313 LOOP
314 /* 22-Jan-04 Namit Singhi B3340572. Removed equality condition for period start date as
315 Sales Orders appeared twice in MPS Bucketed Material Inquiry Screen*/
316 IF (trans_date_tab(X_i) > period_start_date_tab(X_j)) AND (trans_date_tab(X_i) <= period_end_date_tab(X_j)) THEN
317 /* RDP 08/24/2000 Bug 1371700 addition of OMSO */
318 IF (doc_type_tab(X_i) = 'OPSO' OR doc_type_tab(X_i) = 'OMSO') THEN
319 X_sales_orders := X_sales_orders + trans_qty_tab(X_i);
320 ELSIF (doc_type_tab(X_i) = 'FCST') THEN
321 X_forecast := X_forecast + trans_qty_tab(X_i);
322 ELSIF (doc_type_tab(X_i) = 'PROD' OR doc_type_tab(X_i) = 'FPO') THEN
323 IF (trans_qty_tab(X_i) < 0) THEN
324 X_dep_demand := X_dep_demand + trans_qty_tab(X_i);
325 ELSE
326 X_sched_prod := X_sched_prod + trans_qty_tab(X_i);
327 END IF;
328 ELSIF (doc_type_tab(X_i) = 'PPRD') THEN
329 IF (trans_qty_tab(X_i) < 0) THEN
330 X_plnd_ingred := X_plnd_ingred + trans_qty_tab(X_i);
331 ELSE
332 X_plnd_prod := X_plnd_prod + trans_qty_tab(X_i);
333 END IF;
334 /* B1159495 Inventory Transfers */
335 ELSIF (doc_type_tab(X_i) = 'XFER') THEN
336 IF (trans_qty_tab(X_i) < 0) THEN
337 X_sched_transfer_out := X_sched_transfer_out + NVL(trans_qty_tab(X_i),0);
338 ELSE
339 X_sched_transfer_in := X_sched_transfer_in + NVL(trans_qty_tab(X_i), 0);
340 END IF;
341 ELSIF (doc_type_tab(X_i) = 'PTRN') THEN
342 IF (trans_qty_tab(X_i) < 0) THEN
343 X_plnd_transfer_out := X_plnd_transfer_out + NVL(trans_qty_tab(X_i),0);
344 ELSE
345 X_plnd_transfer_in := X_plnd_transfer_in + NVL(trans_qty_tab(X_i), 0);
346 END IF;
347
348 /* B1553919, RDP */
349 /* 28-Aug-01 RDP B1781498 For PREQ,PROD,PORD,PRCV,SHMT */
350 ELSIF (doc_type_tab(X_i) = 'PORD' OR doc_type_tab(X_i) = 'PBPO') THEN
351 X_po_receipts := X_po_receipts + trans_qty_tab(X_i);
352 ELSIF (doc_type_tab(X_i) = 'PRCV' ) THEN
353 X_prcv_supply := X_prcv_supply + trans_qty_tab(X_i);
354 ELSIF (doc_type_tab(X_i) = 'SHMT' ) THEN
355 X_shmt_supply := X_shmt_supply + trans_qty_tab(X_i);
356 ELSIF (doc_type_tab(X_i) = 'PREQ') THEN
357 X_preq_supply := X_preq_supply + trans_qty_tab(X_i);
358 ELSIF (doc_type_tab(X_i) = 'PPUR' OR doc_type_tab(X_i) = 'PBPR') THEN
359 X_planned_purch := X_planned_purch + trans_qty_tab(X_i);
360 ELSIF (doc_type_tab(X_i) = 'LEXP') THEN
361 X_other_demand := X_other_demand + trans_qty_tab(X_i);
362 END IF;
363 END IF;
364 prior_to_one := X_i ;
365 EXIT WHEN trans_date_tab(X_i) > period_end_date_tab(X_j) ;
366 /* if trans_date > period_end_date then break ; */
367 END LOOP;
368 X_sales_orders := ROUND((-1) * X_sales_orders,9);
369 X_forecast := ROUND((-1) * X_forecast,9);
370 X_dep_demand := ROUND((-1) * X_dep_demand,9);
371 X_plnd_ingred := ROUND((-1) * X_plnd_ingred,9);
372 X_other_demand := ROUND((-1) * X_other_demand,9);
373
374 X_po_receipts := ROUND(X_po_receipts,9);
375 X_preq_supply := ROUND(X_preq_supply,9);
376 X_prcv_supply := ROUND(X_prcv_supply,9);
377 X_shmt_supply := ROUND(X_shmt_supply,9);
378 X_sched_prod := ROUND(X_sched_prod,9);
379 X_planned_purch := ROUND(X_planned_purch,9);
380 X_plnd_prod := ROUND(X_plnd_prod,9);
381
382 /* B1159495 Inventory Transfers */
383 X_sched_transfer_in := ROUND(X_sched_transfer_in,9);
384 X_sched_transfer_out := ROUND((-1) * X_sched_transfer_out,9);
385
386 /* B1159495 Inventory Transfers */
387 X_plnd_transfer_in := ROUND(X_plnd_transfer_in,9);
388 X_plnd_transfer_out := ROUND((-1) * X_plnd_transfer_out,9);
389
390 X_total_demand := ROUND(X_sales_orders + X_dep_demand + X_plnd_ingred
391 + X_forecast + X_sched_transfer_out + X_plnd_transfer_out
392 +X_other_demand,9);
393
394 X_total_supply := ROUND(X_po_receipts + X_preq_supply
395 + X_prcv_supply + X_shmt_supply
396 + X_sched_prod + X_planned_purch + X_plnd_prod
397 + X_sched_transfer_in + X_plnd_transfer_in,9);
398
399 IF (X_j = 1) THEN
400 X_ending_bal := ROUND(V_on_hand - X_total_demand + X_total_supply,9);
401 ELSE
402 X_ending_bal := ROUND(X_prev_balance - X_total_demand + X_total_supply,9);
403 END IF;
404 IF (X_ending_bal <= NVL(V_total_ss,0)) THEN
405 X_net_ss_reqmt := ROUND(NVL(V_total_ss,0) - X_ending_bal,9);
406 END IF;
407 X_prev_balance := X_ending_bal;
408
409 INSERT INTO ps_matl_dtl
410 (MATL_REP_ID,
411 ITEM_ID,
412 WHSE_CODE,
413 QTY_ON_HAND,
414 PERD_NAME,
415 PERD_END_DATE,
416 SALES_ORDERS,
417 FORE_CAST,
418 PLND_INGRED,
419 OTHER_DEMAND,
420 TOTAL_DEMAND,
421 PO_RECEIPTS,
422 PREQ_SUPPLY,
423 PRCV_SUPPLY,
424 SHMT_SUPPLY,
425 SCHED_PROD,
426 SCHED_INGRED,
427 PLND_PURCHASE,
428 PLND_PROD,
429 ENDING_BAL,
430 NET_SS_REQMT,
431 SCHED_TRANSFER_OUT,
432 SCHED_TRANSFER_IN,
433 PLND_TRANSFER_OUT,
434 PLND_TRANSFER_IN)
435 VALUES
436 (X_matl_rep_id,
437 V_item_id,
438 X_whse_code,
439 V_on_hand,
440 period_name_tab(X_j),
441 period_end_date_tab(X_j),
442 X_sales_orders,
443 X_forecast,
444 X_plnd_ingred,
445 X_other_demand,
446 X_total_demand,
447 X_po_receipts,
448 X_preq_supply ,
449 X_prcv_supply ,
450 X_shmt_supply ,
451 X_sched_prod,
452 X_dep_demand,
453 X_planned_purch,
454 X_plnd_prod,
455 X_ending_bal,
456 X_net_ss_reqmt,
457 X_sched_transfer_out,
458 X_sched_transfer_in,
459 X_plnd_transfer_out,
460 X_plnd_transfer_in);
461
462 END LOOP;
463 RETURN(X_matl_rep_id);
464
465 END MR_BUCKET_DATA;
466
467 /* =========== PS_BUCKET_DATA ==================== */
468
469 FUNCTION ps_bucket_data (V_schedule NUMBER,
470 V_item_id NUMBER,
471 V_org_list VARCHAR2, -- akaruppa previously V_whse_list VARCHAR2
472 -- V_fcst_list VARCHAR2,
473 V_on_hand NUMBER,
474 V_total_ss NUMBER,
475 V_uom VARCHAR2,
476 -- V_um_ind NUMBER,
477 V_matl_rep_id NUMBER) RETURN NUMBER IS
478 TYPE trans_date_type IS TABLE OF ic_tran_pnd.trans_date%TYPE
479 INDEX BY BINARY_INTEGER;
480 TYPE document_type IS TABLE OF sy_docs_mst.doc_type%TYPE
481 INDEX BY BINARY_INTEGER;
482 TYPE trans_qty_type IS TABLE OF ic_tran_pnd.trans_qty%TYPE
483 INDEX BY BINARY_INTEGER;
484 TYPE trans_qty2_type IS TABLE OF ic_tran_pnd.trans_qty2%TYPE
485 INDEX BY BINARY_INTEGER;
486 TYPE whse_code_type IS TABLE OF ic_whse_mst.whse_code%TYPE
487 INDEX BY BINARY_INTEGER;
488 TYPE organization_id_type IS TABLE OF hr_organization_units.organization_id%TYPE -- akaruppa added **
489 INDEX BY BINARY_INTEGER;
490 TYPE period_start_date_type IS TABLE OF ps_matl_dtl.perd_end_date%TYPE
491 INDEX BY BINARY_INTEGER;
492 TYPE period_end_date_type IS TABLE OF ps_matl_dtl.perd_end_date%TYPE
493 INDEX BY BINARY_INTEGER;
494 TYPE period_name_type IS TABLE OF ps_matl_dtl.perd_name%TYPE
495 INDEX BY BINARY_INTEGER;
496 trans_date_tab TRANS_DATE_TYPE;
497 doc_type_tab DOCUMENT_TYPE;
498 trans_qty_tab TRANS_QTY_TYPE;
499 trans_qty2_tab TRANS_QTY2_TYPE;
500 whse_code_tab WHSE_CODE_TYPE;
501 organization_id_tab ORGANIZATION_ID_TYPE; -- akaruppa added **
502 period_start_date_tab PERIOD_START_DATE_TYPE;
503 period_end_date_tab PERIOD_END_DATE_TYPE;
504 period_name_tab PERIOD_NAME_TYPE;
505 period_name VARCHAR2(40); /*B3021669 - Sowmya */
506
507 /* 29-Jan-04 - B3394924 - Sowmya - Record definition for the transaction information */
508 TYPE trans_typ IS RECORD(
509 trans_date DATE,
510 doc_type VARCHAR2(4),
511 trans_qty NUMBER,
512 -- trans_qty2 NUMBER,
513 whse_code VARCHAR2(4)
514 );
515
516 trans_rec trans_typ;
517
518 /* 29-Jan-04 - B3394924 - Sowmya - The cursor for fetching the details of all the document types has been made dynamic*/
519 TYPE gmp_fet_cursor_typ IS REF CURSOR;
520 Cur_trans_dtl gmp_fet_cursor_typ;
521
522 /* 29-Jan-04 - B3394924 - Sowmya - SALES ORDERS BEING SEEN ON MPS INQUIRY SCREEN EVEN WHEN EXCLUDED IN SCHEDULE */
523 CURSOR Get_ord_ind_cur IS
524 SELECT order_ind
525 FROM ps_schd_hdr
526 WHERE schedule_id = V_schedule;
527 /* akaruppa changed the cursor to obtain the UOM from MTL_SYSTEM_ITEMS
528 CURSOR Cur_item_um IS
529 SELECT item_um, item_um2
530 FROM ic_item_mst
531 WHERE item_id = V_item_id; */
532
533 CURSOR Cur_item_um IS
534 SELECT DISTINCT primary_uom_code,secondary_uom_code
535 FROM mtl_system_items
536 WHERE inventory_item_id = V_item_id;
537
538 CURSOR Cur_schedule IS
539 SELECT no_days, no_weeks, no_4weeks, no_13weeks
540 FROM ps_schd_hdr
541 WHERE schedule_id = V_schedule
542 AND delete_mark = 0;
543 CURSOR Cur_check_hdr IS
544 SELECT matl_rep_id
545 FROM ps_matl_hdr
546 WHERE matl_rep_id = V_matl_rep_id;
547 CURSOR Cur_matl_rep_id IS
548 SELECT gem5_matl_rep_id_s.NEXTVAL
549 FROM dual;
550 X_rows NUMBER DEFAULT 0;
551 X_trans_date DATE;
552 X_qty NUMBER DEFAULT 0;
553 X_qty2 NUMBER DEFAULT 0;
554 -- X_whse_code VARCHAR2(10);
555 X_organization_id NUMBER;
556 X_item_uom VARCHAR2(3); -- akaruppa previously X_item_um
557 X_item_uom2 VARCHAR2(3); -- akaruppa previously X_item_um2
558 X_trans_qty2 NUMBER;
559 X_retvar NUMBER(5);
560 X_workdate1 DATE;
561 X_workdate2 DATE;
562 X_period VARCHAR2(40);
563 X_no_days NUMBER(5);
564 X_no_weeks NUMBER(5);
565 X_no_4weeks NUMBER(5);
566 X_no_13weeks NUMBER(5);
567 X_date1 DATE;
568 X_date2 DATE;
569 X_day1 VARCHAR2(30);
570 X_n NUMBER(5);
571 X_i NUMBER(5);
572 X_j NUMBER(5);
573 X_k NUMBER(5);
574 X_l NUMBER(5);
575 X_m NUMBER(5);
576 X_dcount NUMBER;
577 X_wcount NUMBER;
578 X_mcount NUMBER;
579 X_qcount NUMBER;
580 X_matl_rep_id NUMBER;
581 X_tot_periods NUMBER(5);
582 X_sales_orders NUMBER DEFAULT 0;
583 X_forecast NUMBER DEFAULT 0;
584 X_sched_ingred NUMBER DEFAULT 0;
585 X_firm_ingred NUMBER DEFAULT 0;
586 X_po_receipts NUMBER DEFAULT 0;
587 X_sched_prod NUMBER DEFAULT 0;
588 X_firm_prod NUMBER DEFAULT 0;
589 X_total_demand NUMBER DEFAULT 0;
590 X_total_supply NUMBER DEFAULT 0;
591 X_net_ss_reqmt NUMBER DEFAULT 0;
592 X_ending_bal NUMBER DEFAULT 0;
593 X_prev_balance NUMBER DEFAULT 0;
594 /* B1159495 Inventory Transfers */
595 X_plnd_transfer_out NUMBER DEFAULT 0;
596 X_plnd_transfer_in NUMBER DEFAULT 0;
597 /* B1781498 */
598 -- X_sched_transfer_out NUMBER DEFAULT 0;
599 -- X_sched_transfer_in NUMBER DEFAULT 0;
600 X_planned_purch NUMBER DEFAULT 0;
601 X_preq_supply NUMBER DEFAULT 0;
602 X_prcv_supply NUMBER DEFAULT 0;
603 X_shmt_supply NUMBER DEFAULT 0;
604 l_order_ind NUMBER := 1 ;/* B3394924 sowmya */
605 x_select VARCHAR2(32600);/* B3394924 sowmya */
606
607 BEGIN
608 -- Retrive the order_ind /* B3394924 sowmya */
609 OPEN Get_ord_ind_cur ;
610 FETCH Get_ord_ind_cur INTO l_order_ind ;
611 CLOSE Get_ord_ind_cur;
612
613 /*B3394924 - sowmya - The cursor for fetching the document type made dynamic */
614 /* 28-Aug-01 Rajesh Patangya B1781498 For PREQ,PORD,PRCV,SHMT */
615 -- akaruppa changed query to fetch data from gme_material_details for production data
616 x_select := ' SELECT gmd.material_requirement_date trans_date, '||
617 ' DECODE(gbh.batch_type, 10,''FPO'',''PROD'') doc_type, '||
618 ' DECODE(gmd.line_type, -1,-1,1) * DECODE(gmd.dtl_um, '||
619 ' :p1, '||
620 ' NVL((NVL(gmd.wip_plan_qty, gmd.plan_qty) - gmd.actual_qty), 0), '||
621 ' inv_convert.inv_um_convert(gmd.inventory_item_id, '||
622 ' NULL, '||
623 ' gmd.organization_id, '||
624 ' NULL, '||
625 ' NVL((NVL(gmd.wip_plan_qty, gmd.plan_qty) - gmd.actual_qty), 0), '||
626 ' gmd.dtl_um, '||
627 ' :p2, '||
628 ' NULL, '||
629 ' NULL '||
630 ' ) '||
631 ' ) trans_qty, '||
632 /* ' DECODE(msi.dual_uom_control,0,0, '||
633 ' DECODE(gmd.line_type, -1,-1,1) * DECODE(gmd.dtl_um, '||
634 ' msi.secondary_uom_code, '||
635 ' (gmd.wip_plan_qty - gmd.actual_qty), '||
636 ' inv_convert.inv_um_convert(gmd.inventory_item_id, '||
637 ' NULL, '||
638 ' gmd.organization_id, '||
639 ' 38, '||
640 ' (gmd.wip_plan_qty-gmd.actual_qty), '||
641 ' gmd.dtl_um, '||
642 ' msi.secondary_uom_code, '||
643 ' NULL, '||
644 ' NULL '||
645 ' ) '||
646 ' ) '||
647 ' ) trans_qty2, '||
648 */
649 ' mp.organization_code '||
650 ' FROM '||
651 ' gme_batch_header gbh, '||
652 ' gme_material_details gmd, '||
653 ' mtl_parameters mp, '||
654 ' mtl_system_items msi '||
655 ' WHERE '||
656 ' Gbh.batch_id = gmd.batch_id '||
657 ' AND msi.inventory_item_id = gmd.inventory_item_id '||
658 ' AND msi.organization_id = gmd.organization_id '||
659 ' AND gmd.organization_id = mp.organization_id '||
660 ' AND mp.process_enabled_flag = '|| '''Y''' ||
661 ' AND gbh.batch_status IN (1,2) '||
662 ' AND gmd.actual_qty < NVL(gmd.wip_plan_qty, gmd.plan_qty) '||
663 ' AND msi.inventory_item_id = :p3 '||
664 ' AND INSTR(:p4, TO_CHAR(gbh.organization_id)) <> 0' ;
665
666 IF l_order_ind = 1 THEN /* B3394924 - Fetch sales order data only when included in the schedule*/
667 x_select := x_select ||' UNION ALL ' ||
668 ' SELECT '||
669 ' mtl.requirement_date, '||
670 ' '''||'OMSO'||''''||', '||
671 -- ' mtl.primary_uom_quantity * (-1) , '||
672 ' DECODE(items.primary_uom_code,:p5,mtl.primary_uom_quantity * (-1), '|| -- akaruppa added
673 ' (-1) * inv_convert.inv_um_convert(mtl.inventory_item_id, '||
674 ' NULL, '||
675 ' org.organization_id, '||
676 ' NULL, '||
677 ' mtl.primary_uom_quantity , '||
678 ' items.primary_uom_code, '||
679 ' :p6, '||
680 ' NULL, '||
681 ' NULL '||
682 ' ) '||
683 ' ) trans_qty, '||
684 ' org.organization_code '|| -- akaruppa previously iwm.whse_code
685 ' FROM '||
686 ' mtl_demand_omoe mtl,'||
687 ' mtl_system_items items,'||
688 ' oe_order_headers_all hdr, '||
689 ' oe_order_lines_all dtl, '||
690 ' mtl_parameters org '||
691 ' WHERE '||
692 ' mtl.inventory_item_id = :p7 '|| -- akaruppa previously im.item_id
693 ' AND INSTR(:p8, TO_CHAR(mtl.organization_id)) <> 0'|| -- akaruppa previously iwm.whse_code
694 ' and items.organization_id = mtl.organization_id '||
695 ' and items.inventory_item_id = mtl.inventory_item_id '||
696 ' and NVL(mtl.completed_quantity,0) = 0 '||
697 ' and mtl.open_flag = ' || '''Y''' ||
698 ' and mtl.available_to_mrp = 1 '||
699 ' and mtl.parent_demand_id is NULL '||
700 ' and mtl.demand_source_type IN (2,8) '||
701 ' and mtl.demand_id = dtl.line_id '||
702 ' and dtl.header_id = hdr.header_id '||
703 ' and dtl.ship_from_org_id = org.organization_id '||
704 ' and org.process_enabled_flag = '|| '''Y''' ||
705 /* ' and ((TO_NUMBER(FND_PROFILE.VALUE(''GMP_EXCLUDE_INTERNAL_OMSO'')) = 1 ' ||
706 ' and nvl(dtl.source_document_type_id, 0) <> 10 ' ||
707 ' ) ' ||
708 ' or TO_NUMBER(FND_PROFILE.VALUE(''GMP_EXCLUDE_INTERNAL_OMSO'')) = 0 ' ||
709 ' ) ' ||
710 */
711 ' and NOT EXISTS '||
712 ' (SELECT 1 '||
713 ' FROM so_lines_all sl, '||
714 ' so_lines_all slp, '||
715 ' mtl_demand_omoe dem '||
716 ' WHERE '||
717 ' slp.line_id(+) = nvl(sl.parent_line_id,sl.line_id) '||
718 ' and to_number(dem.demand_source_line) = sl.line_id(+) '||
719 ' and dem.demand_source_type in (2,8) '||
720 ' and sl.end_item_unit_number IS NULL '||
721 ' and slp.end_item_unit_number IS NULL '||
722 ' and dem.demand_id = mtl.demand_id '||
723 ' and items.effectivity_control = 2) ' ;
724 END IF;
725 x_select := x_select ||' UNION ALL ' ||
726 -- akaruppa changed query to obtain forecast data from Oracle Forecast
727 ' SELECT '||
728 ' dtl.forecast_date, '||
729 ' '''||'FCST'||''''||', '||
730 -- ' dtl.current_forecast_quantity trans_qty, '||
731 ' DECODE(msi.primary_uom_code,:p9, (-1) * dtl.current_forecast_quantity, '||
732 ' (-1) * inv_convert.inv_um_convert(dtl.inventory_item_id, '||
733 ' NULL, '||
734 ' dtl.organization_id, '||
735 ' NULL, '||
736 ' dtl.current_forecast_quantity, '||
737 ' msi.primary_uom_code, '||
738 ' :p10, '||
739 ' NULL, '||
740 ' NULL '||
741 ' ) '||
742 ' ) trans_qty, '||
743 ' mp.organization_code '||
744 ' FROM '||
745 ' ps_schd_for psf, '||
746 ' mrp_forecast_designators mff, '||
747 ' mrp_forecast_dates dtl, '||
748 ' mtl_system_items msi, '||
749 ' mtl_parameters mp '||
750 ' WHERE dtl.inventory_item_id = :p11 '||
751 ' AND psf.schedule_id = :p12 '||
752 ' AND INSTR(:p13, TO_CHAR(psf.organization_id)) <> 0 '||
753 ' AND psf.organization_id = mp.organization_id '||
754 ' AND mp.process_enabled_flag = '|| '''Y''' ||
755 ' AND psf.organization_id = msi.organization_id '||
756 ' AND dtl.inventory_item_id = msi.inventory_item_id '||
757 ' AND psf.organization_id = mff.organization_id '||
758 ' AND psf.forecast_designator = mff.forecast_set '||
759 ' AND mff.forecast_designator = dtl.forecast_designator '||
760 ' AND mff.organization_id = dtl.organization_id '||
761 -- Bug # 12685140 vkinduri, truncated sysdate to see forecast entires entered on sysdate in Bucketed Material Inquiry from
762 --' AND dtl.forecast_date >= fnd_date.canonical_to_date(fnd_date.date_to_canonical(sysdate)) '||
763 ' AND dtl.forecast_date >= trunc(fnd_date.canonical_to_date(fnd_date.date_to_canonical(sysdate))) '||
764 ' UNION ALL ' ||
765 ' SELECT po.expected_delivery_date, '||
766 ' '''||'PORD'||''''||', '||
767 -- ' po.to_org_primary_quantity, '||
768 ' DECODE(mitem.primary_uom_code,:p14,po.to_org_primary_quantity, '|| -- akaruppa added
769 ' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
770 ' NULL, '||
771 ' mitem.organization_id, '||
772 ' NULL, '||
773 ' po.to_org_primary_quantity, '||
774 ' mitem.primary_uom_code, '||
775 ' :p15, '||
776 ' NULL, '||
777 ' NULL '||
778 ' ) '||
779 ' ) trans_qty, '||
780 ' mtl.organization_code '|| -- akaruppa previously iwm.whse_code
781 ' FROM MTL_PARAMETERS mtl, '||
782 ' PO_PO_SUPPLY_VIEW po, '||
783 ' MTL_SYSTEM_ITEMS mitem '||
784 ' WHERE po.item_id = :p16 '|| -- akaruppa previously ic.item_id
785 ' AND po.item_id = mitem.inventory_item_id '||
786 ' AND po.to_organization_id = mitem.organization_id '||
787 ' AND mtl.organization_id = po.to_organization_id '||
788 ' AND mtl.process_enabled_flag = '|| '''Y''' ||
789 ' AND NOT EXISTS '||
790 ' ( SELECT 1 FROM oe_drop_ship_sources odss '||
791 ' WHERE po.po_header_id = odss.po_header_id '||
792 ' AND po.po_line_id = odss.po_line_id ) '||
793 ' AND INSTR(:p17, TO_CHAR(po.to_organization_id)) <> 0 '|| -- akaruppa previously iwm.whse_code
794 ' UNION ALL ' ||
795 ' SELECT po.expected_delivery_date, '||
796 ' '''||'PREQ'||''''||', '||
797 -- ' po.to_org_primary_quantity,'||
798 ' DECODE(mitem.primary_uom_code,:p18,po.to_org_primary_quantity, '|| -- akaruppa added
799 ' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
800 ' NULL, '||
801 ' mitem.organization_id, '||
802 ' NULL, '||
803 ' po.to_org_primary_quantity, '||
804 ' mitem.primary_uom_code, '||
805 ' :p19, '||
806 ' NULL, '||
807 ' NULL '||
808 ' ) '||
809 ' ) trans_qty, '||
810 ' mtl.organization_code '|| -- akaruppa previously iwm.whse_code
811 ' FROM MTL_PARAMETERS mtl,'||
812 ' PO_REQ_SUPPLY_VIEW po,'||
813 ' MTL_SYSTEM_ITEMS mitem '||
814 ' WHERE po.item_id = :p20'|| -- akaruppa previously ic.item_id
815 ' AND po.item_id = mitem.inventory_item_id '||
816 ' AND po.to_organization_id = mitem.organization_id '||
817 ' AND mtl.organization_id = po.to_organization_id '||
818 ' AND mtl.process_enabled_flag = '|| '''Y''' ||
819 ' AND NOT EXISTS '||
820 ' ( SELECT 1 FROM oe_drop_ship_sources odss '||
821 ' WHERE po.requisition_header_id = odss.requisition_header_id '||
822 ' AND po.req_line_id = odss.requisition_line_id ) '||
823 ' AND INSTR(:p21, TO_CHAR(po.to_organization_id)) <> 0 ' ; -- akaruppa previously iwm.whse_code
824
825 x_select := x_select || ' UNION ALL '||
826 ' SELECT po.expected_delivery_date,'||
827 ' '''||'PRCV'||''''||', '||
828 -- ' po.to_org_primary_quantity,'||
829 ' DECODE(mitem.primary_uom_code,:p22,po.to_org_primary_quantity, '|| -- akaruppa added
830 ' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
831 ' NULL, '||
832 ' mitem.organization_id, '||
833 ' NULL, '||
834 ' po.to_org_primary_quantity, '||
835 ' mitem.primary_uom_code, '||
836 ' :p23, '||
837 ' NULL, '||
838 ' NULL '||
839 ' ) '||
840 ' ) trans_qty, '||
841 ' mtl.organization_code '|| -- akaruppa previously iwm.whse_code
842 ' FROM MTL_PARAMETERS mtl,'||
843 ' PO_RCV_SUPPLY_VIEW po,'||
844 ' MTL_SYSTEM_ITEMS mitem '||
845 ' WHERE po.item_id = :p24'|| -- akaruppa previously ic.item_id
846 ' AND po.item_id = mitem.inventory_item_id '||
847 ' AND po.to_organization_id = mitem.organization_id '||
848 ' AND mtl.organization_id = po.to_organization_id '||
849 ' AND mtl.process_enabled_flag = ' || '''Y''' || --||''''||'Y'||''' '||
850 ' AND NOT EXISTS '||
851 ' ( SELECT 1 FROM oe_drop_ship_sources odss '||
852 ' WHERE po.po_header_id = odss.po_header_id '||
853 ' AND po.po_line_id = odss.po_line_id ) '||
854 ' AND INSTR(:p25, TO_CHAR(po.to_organization_id)) <> 0' || -- akaruppa previously iwm.whse_code
855 ' UNION ALL '||
856 ' SELECT po.expected_delivery_date,'||
857 ' '''||'PRCV'||''''||', '||
858 -- ' po.to_org_primary_quantity,'||
859 ' DECODE(mitem.primary_uom_code,:p26,po.to_org_primary_quantity, '|| -- akaruppa added
860 ' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
861 ' NULL, '||
862 ' mitem.organization_id, '||
863 ' NULL, '||
864 ' po.to_org_primary_quantity, '||
865 ' mitem.primary_uom_code, '||
866 ' :p27, '||
867 ' NULL, '||
868 ' NULL '||
869 ' ) '||
870 ' ) trans_qty, '||
871 ' mtl.organization_code '|| -- akaruppa previously iwm.whse_code
872 ' FROM MTL_PARAMETERS mtl,'||
873 ' PO_SHIP_RCV_SUPPLY_VIEW po, '||
874 ' MTL_SYSTEM_ITEMS mitem '||
875 ' WHERE po.item_id = :p28'|| -- akaruppa previously ic.item_id
876 ' AND po.item_id = mitem.inventory_item_id '||
877 ' AND po.to_organization_id = mitem.organization_id '||
878 ' AND mtl.organization_id = po.to_organization_id '||
879 ' AND mtl.process_enabled_flag = '|| '''Y''' ||
880 ' AND INSTR(:p29, TO_CHAR(po.to_organization_id)) <> 0'; -- akaruppa previously iwm.whse_code
881
882 /* BUG#3404056 - Port Bug 3264766 to 11.5.10L
883 D. Sailaja - Added ordered clause and commented the
884 existing order of the FROM clause to incorporate the new order */
885
886 x_select := x_select || ' UNION ALL '||
887 ' SELECT '|| /* + ordered */
888 ' po.expected_delivery_date, '||
889 ' '''||'SHMT'||''''||', '||
890 -- ' po.to_org_primary_quantity,'||
891 ' DECODE(mitem.primary_uom_code,:p30,po.to_org_primary_quantity, '|| -- akaruppa added
892 ' inv_convert.inv_um_convert(mitem.inventory_item_id, '||
893 ' NULL, '||
894 ' mitem.organization_id, '||
895 ' NULL, '||
896 ' po.to_org_primary_quantity, '||
897 ' mitem.primary_uom_code, '||
898 ' :p31, '||
899 ' NULL, '||
900 ' NULL '||
901 ' ) '||
902 ' ) trans_qty, '||
903 ' mtl.organization_code '|| -- akaruppa previously iwm.whse_code
904 ' FROM MTL_SYSTEM_ITEMS mitem,'||
905 ' PO_SHIP_SUPPLY_VIEW po,'||
906 ' MTL_PARAMETERS mtl '||
907 ' WHERE po.item_id = :p32'|| -- akaruppa previously ic.item_id
908 ' AND po.item_id = mitem.inventory_item_id '||
909 ' AND po.to_organization_id = mitem.organization_id '||
910 ' AND mtl.organization_id = po.to_organization_id '||
911 ' AND mtl.process_enabled_flag = '|| '''Y''' ||
912 ' AND INSTR(:p33, TO_CHAR(mtl.organization_id)) <> 0 ' || -- akaruppa previously iwm.whse_code
913 ' ORDER BY 1 asc, 3 desc ';
914
915 /*Open the cursor and pass the item_id , warehouse_id and the forecast list*/
916
917 /* B3394924 - Sowmya - Added to handle the bind variables passed to the cursor when
918 sales order are included and excluded from the schedule*/
919 -- akaruppa changed the bind variables based on the query
920 IF l_order_ind = 1 THEN
921
922 OPEN Cur_trans_dtl FOR x_select USING
923 V_uom, V_uom, V_item_id,V_org_list,
924 V_uom, V_uom, V_item_id,V_org_list,
925 V_uom, V_uom, V_item_id, V_schedule,V_org_list,
926 V_uom, V_uom, V_item_id,V_org_list,
927 V_uom, V_uom, V_item_id,V_org_list,
928 V_uom, V_uom, V_item_id,V_org_list,
929 V_uom, V_uom, V_item_id,V_org_list,
930 V_uom, V_uom, V_item_id,V_org_list;
931
932
933 ELSE
934 OPEN Cur_trans_dtl FOR x_select USING
935 V_uom, V_uom, V_item_id,V_org_list,
936 V_uom, V_uom, V_item_id,V_schedule,V_org_list,
937 V_uom, V_uom, V_item_id,V_org_list,
938 V_uom, V_uom, V_item_id,V_org_list,
939 V_uom, V_uom, V_item_id,V_org_list,
940 V_uom, V_uom, V_item_id,V_org_list,
941 V_uom, V_uom, V_item_id,V_org_list;
942 END IF;
943
944
945 LOOP
946 FETCH Cur_trans_dtl INTO trans_rec;
947 EXIT WHEN Cur_trans_dtl%NOTFOUND;
948 X_rows := X_rows + 1;
949
950 /* nsinghi MPSCONV Start */
951 /* Following code was written mainly for forecast transactions that did not have
952 the secondary UOM qty. Now the secondary qty for all txns are being retrieved in the
953 query itself. So commented following code. */
954 /*
955 OPEN Cur_item_um;
956 FETCH Cur_item_um INTO X_item_uom, X_item_uom2;
957 CLOSE Cur_item_um;
958 X_trans_qty2 := trans_rec.trans_qty2;
959 IF (X_item_uom2 IS NOT NULL) THEN
960 IF (NVL(trans_rec.trans_qty2,0) = 0) AND (trans_rec.trans_qty <> 0) THEN
961 gmicuom.icuomcv(V_item_id,0,trans_rec.trans_qty,X_item_uom,X_item_uom2,X_trans_qty2); -- akaruppa TO DO Change GMICUOM to Inv_Convert
962 END IF;
963 END IF;
964 */
965 /* nsinghi MPSCONV End */
966
967 trans_date_tab(X_rows) := trans_rec.trans_date;
968 doc_type_tab(X_rows) := trans_rec.doc_type;
969 trans_qty_tab(X_rows) := NVL(trans_rec.trans_qty,0);
970 -- trans_qty2_tab(X_rows) := NVL(X_trans_qty2,0);
971 whse_code_tab(X_rows) := trans_rec.whse_code;
972 END LOOP;
973 CLOSE Cur_trans_dtl;
974
978 So commented following code. */
975 /* nsinghi MPSCONV Start */
976 /* Following code was written mainly for forecast consumption.
977 Now the consumption will be done in Discrete forcasting module.
979 /*
980
981 FOR X_k IN 1..X_rows LOOP
982 IF (doc_type_tab(X_k) = 'FCST') THEN
983 X_l := X_k + 1;
984 FOR X_m IN X_l..X_rows LOOP
985
986 IF (doc_type_tab(X_m) = 'OPSO' OR
987 doc_type_tab(X_m) = 'OMSO') THEN
988 IF (whse_code_tab(X_m) = whse_code_tab(X_k)) THEN
989 trans_qty_tab(X_k) := trans_qty_tab(X_k) - trans_qty_tab(X_m);
990 trans_qty2_tab(X_k) := trans_qty2_tab(X_k) - trans_qty2_tab(X_m);
991 IF (trans_qty_tab(X_k) > 0) THEN
992 trans_qty_tab(X_k) := 0;
993 END IF;
994 IF (trans_qty2_tab(X_k) > 0) THEN
995 trans_qty2_tab(X_k) := 0;
996 END IF;
997 END IF;
998 ELSIF (doc_type_tab(X_m) = 'FCST') THEN
999 EXIT;
1000 END IF;
1001 END LOOP;
1002 END IF;
1003 END LOOP;
1004 */
1005 /* nsinghi MPSCONV End */
1006
1007 -- TKW B3034938 The changes made in bugs 2348778 and 2626977 were commented
1008 -- since the date returned was null. The min date was set as a constant
1009 -- date in gma_global_grp.
1010 /*
1011 --BEGIN BUG#2348778 RajaSekhar
1012 X_workdate1 := FND_DATE.string_to_date(GMA_CORE_PKG.get_date_constant('SY$MIN_DATE'), 'YYYY/MM/DD HH24:MI:SS');
1013 -- Bug #2626977 (JKB) Removed reference to date profile above.
1014 --END BUG#2348778
1015 */
1016
1017 -- X_workdate1 := GMA_GLOBAL_GRP.SY$MIN_DATE;
1018 /* Use the hard coded value for bug 12399670 Vijay Induri */
1019 X_workdate1 := TO_DATE ( (SYSDATE-(17*365)), 'DD/MM/YYYY HH24:MI:SS') ;
1020
1021 X_workdate2 := TO_DATE(TO_CHAR(SYSDATE,'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
1022 FND_MESSAGE.SET_NAME('GMP','PS_PASTDUE');
1023 X_period := FND_MESSAGE.GET;
1024 IF (X_rows = 0) THEN
1025 RETURN(X_rows);
1026 END IF;
1027
1028 OPEN Cur_schedule;
1029 FETCH Cur_schedule INTO X_no_days, X_no_weeks, X_no_4weeks, X_no_13weeks;
1030 CLOSE Cur_schedule;
1031
1032 X_date1 := SYSDATE;
1033 X_date2 := X_date1 - 1;
1034 X_date1 := X_date2 + X_no_days;
1035 X_day1 := INITCAP(TO_CHAR(X_date1,'DAY'));
1036 X_n := TO_CHAR(X_date1,'D');
1037 X_no_days := X_no_days + 7 - X_n + 1;
1038 X_tot_periods := X_no_days + X_no_weeks + X_no_4weeks + X_no_13weeks;
1039 period_start_date_tab(1) := X_workdate1;
1040 period_end_date_tab(1) := TO_DATE(TO_CHAR(X_date2,'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
1041 FND_MESSAGE.SET_NAME('GMP','PS_PASTDUE');
1042 period_name_tab(1) := FND_MESSAGE.GET;
1043
1044 -- TKW 12/26/2003 B3337215 - Port B3306526 to 11.5.10L.
1045 -- Modified following condition for the case X_no_days = 1 to work
1046 IF (X_no_days > 1) THEN
1047 -- IF (X_no_days <> 0) THEN
1048 FOR X_i IN 2..X_no_days
1049 LOOP
1050 X_j := X_i - 1;
1051 X_dcount := X_i + 1;
1052 X_date1 := period_end_date_tab(X_j);
1053 period_start_date_tab(X_i) := X_date1;
1054 X_date2 := period_start_date_tab(X_i) + 1;
1055 period_end_date_tab(X_i) := TO_DATE(TO_CHAR(X_date2,'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
1056 /*period_name_tab(X_i) := INITCAP(TO_CHAR(X_date2,'DAY'));*/
1057
1058 /*B3732658 - Added a new parameter 'NLS_DATE_LANGUAGE=ENGLISH' to the period name below, so that whatever might be the
1059 language to which the database is set to the period name is fetched in ENGLISH. This period name is used further for
1060 fetching the days from the message dictionary*/
1061 period_name := trim(UPPER(TO_CHAR(X_date2,'DAY','NLS_DATE_LANGUAGE=ENGLISH'))); /*B3732658*/
1062
1063 /*B3021669 - Sowmya - GMP:GMP:DAYS OF THE WEEK UNTRANSLATED IN MPS MATERIAL ACTIVITY INQUIRY*/
1064 /* Based on the period name the day will be picked from the message dictionary.
1065 This change has been done to facilitate the translation of messages*/
1066
1067 IF ( period_name = 'SUNDAY') THEN
1068 FND_MESSAGE.SET_NAME('GMP','PS_SUNDAY');
1069 period_name_tab(X_i) := FND_MESSAGE.GET;
1070 ELSIF ( period_name = 'MONDAY') THEN
1071 FND_MESSAGE.SET_NAME('GMP','PS_MONDAY');
1072 period_name_tab(X_i) := FND_MESSAGE.GET;
1073 ELSIF ( period_name = 'TUESDAY') THEN
1074 FND_MESSAGE.SET_NAME('GMP','PS_TUESDAY');
1075 period_name_tab(X_i) := FND_MESSAGE.GET;
1076 ELSIF ( period_name = 'WEDNESDAY') THEN
1077 FND_MESSAGE.SET_NAME('GMP','PS_WEDNESDAY');
1078 period_name_tab(X_i) := FND_MESSAGE.GET;
1079 ELSIF ( period_name = 'THURSDAY') THEN
1080 FND_MESSAGE.SET_NAME('GMP','PS_THURSDAY');
1081 period_name_tab(X_i) := FND_MESSAGE.GET;
1082 ELSIF ( period_name = 'FRIDAY') THEN
1083 FND_MESSAGE.SET_NAME('GMP','PS_FRIDAY');
1084 period_name_tab(X_i) := FND_MESSAGE.GET;
1085 ELSIF ( period_name = 'SATURDAY') THEN
1089
1086 FND_MESSAGE.SET_NAME('GMP','PS_SATURDAY');
1087 period_name_tab(X_i) := FND_MESSAGE.GET;
1088 END IF;
1090 END LOOP;
1091 ELSE
1092 X_dcount := 2;
1093 END IF;
1094 IF (X_no_weeks <> 0) THEN
1095 FOR X_i IN X_dcount..(X_no_days + X_no_weeks)
1096 LOOP
1097 X_j := X_i - 1;
1098 X_wcount := X_i + 1;
1099 X_date1 := period_end_date_tab(X_j);
1100 period_start_date_tab(X_i) := X_date1;
1101 X_date2 := period_start_date_tab(X_i) + 7;
1102 period_end_date_tab(X_i) := TO_DATE(TO_CHAR(X_date2,'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
1103 FND_MESSAGE.SET_NAME('GMP','PS_WEEK');
1104 period_name_tab(X_i) := FND_MESSAGE.GET||' '||TO_CHAR(X_date2,'WW');
1105 END LOOP;
1106 ELSE
1107 IF (X_no_days = 0) THEN
1108 X_wcount := 2;
1109 ELSE
1110 X_wcount := X_dcount;
1111 END IF;
1112 END IF;
1113
1114 IF (X_no_4weeks <> 0) THEN
1115 FOR X_i IN X_wcount..(X_no_days + X_no_weeks + X_no_4weeks)
1116 LOOP
1117 X_j := X_i - 1;
1118 X_mcount := X_i + 1;
1119 X_date1 := period_end_date_tab(X_j);
1120 period_start_date_tab(X_i) := X_date1;
1121 X_date2 := period_start_date_tab(X_i) + 28;
1122 period_end_date_tab(X_i) := TO_DATE(TO_CHAR(X_date2,'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
1123 FND_MESSAGE.SET_NAME('GMP','PS_WEEK');
1124 period_name_tab(X_i) := FND_MESSAGE.GET||' '||LPAD(TO_CHAR(TO_NUMBER(TO_CHAR(X_date1,'WW'))+1),2,'0')||'-'||TO_CHAR(X_date2,'WW');
1125 END LOOP;
1126 ELSE
1127 IF (X_no_days = 0 AND X_no_weeks = 0) THEN
1128 X_mcount := 2;
1129 ELSIF (X_no_weeks = 0) THEN
1130 X_mcount := X_dcount;
1131 ELSE
1132 X_mcount := X_wcount;
1133 END IF;
1134 END IF;
1135
1136 IF (X_no_13weeks <> 0) THEN
1137 FOR X_i IN X_mcount..(X_no_days + X_no_weeks + X_no_4weeks + X_no_13weeks)
1138 LOOP
1139 X_j := X_i - 1;
1140 X_qcount := X_i + 1;
1141 X_date1 := period_end_date_tab(X_j);
1142 period_start_date_tab(X_i) := X_date1;
1143 X_date2 := period_start_date_tab(X_i) + 91;
1144 period_end_date_tab(X_i) := TO_DATE(TO_CHAR(X_date2,'DD/MM/YYYY')||' 23:59:59','DD/MM/YYYY HH24:MI:SS');
1145 FND_MESSAGE.SET_NAME('GMP','PS_WEEK');
1146 period_name_tab(X_i) := FND_MESSAGE.GET||' '||LPAD(TO_CHAR(TO_NUMBER(TO_CHAR(X_date1,'WW'))+1),2,'0')||'-'||TO_CHAR(X_date2,'WW');
1147 END LOOP;
1148 END IF;
1149
1150 OPEN Cur_check_hdr;
1151 FETCH Cur_check_hdr INTO X_matl_rep_id;
1152 IF (Cur_check_hdr%NOTFOUND) THEN
1153 OPEN Cur_matl_rep_id;
1154 FETCH Cur_matl_rep_id INTO X_matl_rep_id;
1155 CLOSE Cur_matl_rep_id;
1156
1157 /* nsinghi MPSCONV Start */
1158 /* ToDo : Need to ensure if we need to insert organization_id too?
1159 V_matl_rep_id will be null when call to this procedure is made from
1160 Bucketed Material Form and NOT Report. I think this part of code
1161 will require to be removed. */
1162 /* nsinghi MPSCONV End */
1163 /*
1164 INSERT INTO ps_matl_hdr (matl_rep_id, inventory_item_id)
1165 VALUES (X_matl_rep_id, V_item_id);
1166 */
1167 ELSE
1168 X_matl_rep_id := V_matl_rep_id;
1169 END IF;
1170 CLOSE Cur_check_hdr;
1171
1172 IF (V_matl_rep_id IS NOT NULL) THEN
1173 DELETE
1174 FROM ps_matl_dtl
1175 WHERE matl_rep_id = V_matl_rep_id
1176 AND item_id = V_item_id;
1177 END IF;
1178 IF (INSTR(V_org_list,',') <> 0) THEN
1179 -- X_whse_code := FND_PROFILE.VALUE('SY$ALL');
1180 X_organization_id := NULL;
1181 ELSE /* For reports the V_org_list will be single org */
1182 X_organization_id := TO_NUMBER(V_org_list);
1183 -- X_whse_code := REPLACE(V_org_list, '''', '');
1184 END IF;
1185 FOR X_j IN 1..X_tot_periods
1186 LOOP
1187 X_sales_orders := 0;
1188 X_forecast := 0;
1189 X_sched_ingred := 0;
1190 X_firm_ingred := 0;
1191 X_total_demand := 0;
1192 X_total_supply := 0;
1193 X_po_receipts := 0;
1194 X_sched_prod := 0;
1195 X_firm_prod := 0;
1196 X_ending_bal := 0;
1197 X_net_ss_reqmt := 0;
1198 /* B1159495 Inventory Transfers */
1199 X_plnd_transfer_out := 0;
1200 X_plnd_transfer_in := 0;
1201 /* B1781498 */
1202 -- X_sched_transfer_out := 0;
1203 -- X_sched_transfer_in := 0;
1204 X_preq_supply := 0;
1205 X_prcv_supply := 0;
1206 X_shmt_supply := 0;
1207
1208 FOR X_i IN 1..X_rows
1209 LOOP
1213 -- IF (V_um_ind = 1) THEN
1210 /* 22-Jan-04 Namit Singhi B3340572. Removed equality condition for period start date as
1211 Sales Orders appeared twice in MPS Bucketed Material Inquiry Screen*/
1212 IF (trans_date_tab(X_i) > period_start_date_tab(X_j)) AND (trans_date_tab(X_i) <= period_end_date_tab(X_j)) THEN
1214 /* RDP 08/24/2000 Bug 1371700 addition of OMSO */
1215 IF (doc_type_tab(X_i) = 'OPSO' OR doc_type_tab(X_i) = 'SHIP' OR
1216 doc_type_tab(X_i) = 'OMSO' ) THEN
1217 X_sales_orders := X_sales_orders + NVL(trans_qty_tab(X_i),0);
1218 ELSIF (doc_type_tab(X_i) = 'PROD') THEN
1219 IF (trans_qty_tab(X_i) < 0) THEN
1220 X_sched_ingred := X_sched_ingred + NVL(trans_qty_tab(X_i),0);
1221 ELSE
1222 X_sched_prod := X_sched_prod + NVL(trans_qty_tab(X_i),0);
1223 END IF;
1224 ELSIF (doc_type_tab(X_i) = 'FPO') THEN
1225 IF (trans_qty_tab(X_i) < 0) THEN
1226 X_firm_ingred := X_firm_ingred + NVL(trans_qty_tab(X_i),0);
1227 ELSE
1228 X_firm_prod := X_firm_prod + NVL(trans_qty_tab(X_i),0);
1229 END IF;
1230
1231 /* B1159495 Inventory Transfers */
1232 /* nsinghi MPSCONV Start */
1233 /* ToDo: This code will be commented as we cannot have schedule transfers. */
1234 /* ELSIF (doc_type_tab(X_i) = 'XFER') THEN
1235 IF (trans_qty_tab(X_i) < 0) THEN
1236 X_sched_transfer_out := X_sched_transfer_out + NVL(trans_qty_tab(X_i),0);
1237 ELSE
1238 X_sched_transfer_in := X_sched_transfer_in + NVL(trans_qty_tab(X_i), 0);
1239 END IF;
1240 */
1241 /* nsinghi MPSCONV Start */
1242
1243 ELSIF (doc_type_tab(X_i) = 'PORD') THEN
1244 X_po_receipts := X_po_receipts + NVL(trans_qty_tab(X_i),0);
1245 /* 28-Aug-01 Rajesh Patangya B1781498 For PREQ,PORD,PRCV,SHMT */
1246 ELSIF (doc_type_tab(X_i) = 'PREQ') THEN
1247 X_preq_supply := X_preq_supply + NVL(trans_qty_tab(X_i),0);
1248 ELSIF (doc_type_tab(X_i) = 'PRCV') THEN
1249 X_prcv_supply := X_prcv_supply + NVL(trans_qty_tab(X_i),0);
1250 ELSIF (doc_type_tab(X_i) = 'SHMT') THEN
1251 X_shmt_supply := X_shmt_supply + NVL(trans_qty_tab(X_i),0);
1252 ELSIF (doc_type_tab(X_i) = 'FCST') THEN
1253 X_forecast := X_forecast + NVL(trans_qty_tab(X_i),0);
1254 END IF;
1255 -- ELSIF (V_um_ind = 2) THEN
1256 /* IF (doc_type_tab(X_i) = 'OPSO' OR doc_type_tab(X_i) = 'SHIP' OR
1257 doc_type_tab(X_i) = 'OMSO' ) THEN
1258 X_sales_orders := X_sales_orders + NVL(trans_qty2_tab(X_i),0);
1259 ELSIF (doc_type_tab(X_i) = 'PROD') THEN
1260 IF (trans_qty2_tab(X_i) < 0) THEN
1261 X_sched_ingred := X_sched_ingred + NVL(trans_qty2_tab(X_i),0);
1262 ELSE
1263 X_sched_prod := X_sched_prod + NVL(trans_qty2_tab(X_i),0);
1264 END IF;
1265 ELSIF (doc_type_tab(X_i) = 'FPO') THEN
1266 IF (trans_qty2_tab(X_i) < 0) THEN
1267 X_firm_ingred := X_firm_ingred + NVL(trans_qty2_tab(X_i),0);
1268 ELSE
1269 X_firm_prod := X_firm_prod + NVL(trans_qty2_tab(X_i),0);
1270 END IF;
1271 ELSIF (doc_type_tab(X_i) = 'PORD') THEN
1272 X_po_receipts := X_po_receipts + NVL(trans_qty2_tab(X_i),0);
1273 ELSIF (doc_type_tab(X_i) = 'PREQ') THEN
1274 X_preq_supply := X_preq_supply + NVL(trans_qty2_tab(X_i),0);
1275 ELSIF (doc_type_tab(X_i) = 'PRCV') THEN
1276 X_prcv_supply := X_prcv_supply + NVL(trans_qty2_tab(X_i),0);
1277 ELSIF (doc_type_tab(X_i) = 'SHMT') THEN
1278 X_shmt_supply := X_shmt_supply + NVL(trans_qty2_tab(X_i),0);
1279 ELSIF (doc_type_tab(X_i) = 'FCST') THEN
1280 X_forecast := X_forecast + NVL(trans_qty2_tab(X_i),0);
1281 END IF;
1282 END IF;
1283 */
1284 END IF;
1285 END LOOP;
1286 X_sales_orders := ROUND((-1) * X_sales_orders,9);
1287 X_forecast := ROUND((-1) * X_forecast,9);
1288 X_sched_ingred := ROUND((-1) * X_sched_ingred,9);
1289 X_firm_ingred := ROUND((-1) * X_firm_ingred,9);
1290
1291 /* nsinghi MPSCONV Start */
1292 /* ToDo: This code will be commented as we cannot have schedule transfers. */
1293 /* X_sched_transfer_out := ROUND((-1) * X_sched_transfer_out,9);
1294
1295 X_total_demand := ROUND(X_sales_orders + X_sched_ingred + X_firm_ingred +
1296 X_forecast+X_sched_transfer_out,9);
1297
1298 X_total_supply := ROUND(X_po_receipts + X_preq_supply + X_shmt_supply +
1299 X_prcv_supply + X_sched_prod +
1300 X_firm_prod + X_sched_transfer_in,9);
1301 */
1302 /* nsinghi MPSCONV End */
1303
1304 X_total_demand := ROUND(X_sales_orders + X_sched_ingred + X_firm_ingred +
1305 X_forecast,9);
1306
1307 X_total_supply := ROUND(X_po_receipts + X_preq_supply + X_shmt_supply +
1308 X_prcv_supply + X_sched_prod +
1309 X_firm_prod ,9);
1310
1311 X_po_receipts := ROUND(X_po_receipts,9);
1312 X_sched_prod := ROUND(X_sched_prod,9);
1313 X_firm_prod := ROUND(X_firm_prod,9);
1314
1315 X_preq_supply := ROUND(X_preq_supply,9);
1316 X_prcv_supply := ROUND(X_prcv_supply,9);
1317 X_shmt_supply := ROUND(X_shmt_supply,9);
1318
1319 -- X_sched_transfer_in := ROUND(X_sched_transfer_in,9);
1320
1321 IF (X_j = 1) THEN
1322 X_ending_bal := ROUND(V_on_hand - X_total_demand + X_total_supply,9);
1323 ELSE
1324 X_ending_bal := ROUND(X_prev_balance - X_total_demand + X_total_supply,9);
1325 END IF;
1326 IF (X_ending_bal <= NVL(V_total_ss,0)) THEN
1327 X_net_ss_reqmt := ROUND(NVL(V_total_ss,0) - X_ending_bal,9);
1328 END IF;
1329 X_prev_balance := X_ending_bal;
1330
1331 INSERT INTO ps_matl_dtl
1332 (MATL_REP_ID,
1333 /* nsinghi MPSCONV Start */
1334 -- ITEM_ID,
1335 INVENTORY_ITEM_ID,
1336 -- WHSE_CODE,
1337 ORGANIZATION_ID,
1338 /* nsinghi MPSCONV End */
1339 QTY_ON_HAND,
1340 PERD_NAME,
1341 PERD_END_DATE,
1342 SALES_ORDERS,
1343 FORE_CAST,
1344 SCHED_INGRED,
1345 FIRM_INGRED,
1346 TOTAL_DEMAND,
1347 PO_RECEIPTS,
1348 PREQ_SUPPLY,
1349 PRCV_SUPPLY,
1350 SHMT_SUPPLY,
1351 SCHED_PROD,
1352 FIRM_PROD,
1353 ENDING_BAL,
1354 NET_SS_REQMT)
1355 /* SCHED_TRANSFER_OUT,
1356 SCHED_TRANSFER_IN ) */
1357 VALUES
1358 (X_matl_rep_id,
1359 V_item_id,
1360 /* nsinghi MPSCONV Start */
1361 -- X_whse_code,
1362 X_organization_id,
1363 /* nsinghi MPSCONV End */
1364 V_on_hand,
1365 period_name_tab(X_j),
1366 period_end_date_tab(X_j),
1367 X_sales_orders,
1368 X_forecast,
1369 X_sched_ingred,
1370 X_firm_ingred,
1371 X_total_demand,
1372 X_po_receipts,
1373 X_preq_supply ,
1374 X_prcv_supply ,
1375 X_shmt_supply ,
1376 X_sched_prod,
1377 X_firm_prod,
1378 X_ending_bal,
1379 X_net_ss_reqmt);
1380 /* X_sched_transfer_out,
1381 X_sched_transfer_in ); */
1382
1383 END LOOP;
1384 RETURN(X_matl_rep_id);
1385
1386 END PS_BUCKET_DATA ;
1387
1388 END PKG_GMP_BUCKET_DATA;