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