[Home] [Help]
PACKAGE BODY: APPS.GMI_INVENTORY_CLOSE_CONC
Source
1 PACKAGE BODY GMI_INVENTORY_CLOSE_CONC AS
2 /* $Header: gmisubrb.pls 115.9 2004/07/08 20:58:31 acataldo noship $ */
3
4 /* Added local variable for debugging - Bug 3684980 */
5 x_prev_time DATE;
6 x_cur_time DATE;
7 PROCEDURE RUN(
8 errbuf OUT NOCOPY VARCHAR2,
9 retcode OUT NOCOPY VARCHAR2,
10 P_sequence IN VARCHAR2,
11 P_fiscal_year IN VARCHAR2,
12 P_period IN VARCHAR2,
13 P_period_id IN VARCHAR2,
14 P_start_date IN VARCHAR2,
15 P_end_date IN VARCHAR2,
16 P_op_code IN VARCHAR2,
17 P_orgn_code IN VARCHAR2,
18 P_close_ind IN VARCHAR2)
19
20 IS
21
22 Cursor Get_Whse IS
23 Select whse_code from
24 gmi_clos_warehouses
25 where inventory_close_id = P_sequence
26 order by whse_code;
27
28 x_whse_code IC_WHSE_MST.WHSE_CODE%TYPE;
29 l_iret NUMBER := -1;
30
31 x_period_id NUMBER;
32 x_period NUMBER;
33 x_op_code NUMBER;
34 x_start_date DATE;
35 x_end_date DATE;
36
37 /*BEGIN BUG#2589255 James Bernard */
38 /*Created a new local variable x_exception to handle exceptions */
39 x_exception EXCEPTION;
40 /*END BUG#2589255 */
41
42 BEGIN
43 errbuf := NULL;
44 retcode := '0';
45 x_close_err := 0;
46 /********************************************************************
47 Debugging Information.
48 *******************************************************************/
49 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'INPUT PARAMETERS');
50 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'SEQUENCE - '||P_sequence);
51 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'FISCAL YEAR- '||p_fiscal_year);
52 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'PERIOD - '||p_period);
53 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'PERIOD ID - '||p_period_id);
54 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'START DATE - '||P_start_date);
55 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'END DATE - '||P_end_date);
56 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'OP CODE - '||P_op_code);
57 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'ORGN CODE - '||P_orgn_code);
58 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'CLOSE IND - '||P_close_ind);
59
60 /* Debugging statements - Bug 3684980 */
61 x_cur_time := sysdate;
62 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Start of the Close process (ver 3)- '||
63 to_char(x_cur_time, 'hh24:mi:ss') );
64 x_prev_time := x_cur_time;
65
66
67 OPEN Get_Whse;
68 FETCH Get_Whse into x_whse_code;
69 IF (Get_Whse%NOTFOUND) THEN
70 CLOSE Get_Whse;
71 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'No Warehouse selected for Close');
72 /*BEGIN BUG#2589255 James Bernard */
73 /*Raising x_exception as GMI_CLOS_WAREHOUSES table has to be cleaned for */
74 /*whse row being processed */
75 RAISE x_exception;
76 /*END BUG#2589255 */
77 RETURN;
78 END IF;
79
80 LOOP
81 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' ');
82 /* Debugging statements - Bug 3684980 */
83 x_cur_time := sysdate;
84 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,'Processing Warehouse '||x_whse_code || ' - '||
85 to_char(x_cur_time, 'hh24:mi:ss') );
86 x_prev_time := x_cur_time;
87
88 /**********************************************************
89 Delete all rows from the
90 ic_perd_bal table for this warehouse.
91 **********************************************************/
92
93 FND_MESSAGE.set_name('GMI', 'ICCAL_DELETE_PERD_MSG');
94 FND_MESSAGE.set_token('WHSE', x_whse_code);
95 X_msg := FND_MESSAGE.GET;
96 x_cur_time := sysdate;
97 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' '||X_msg || ' - '|| to_char(x_cur_time, 'hh24:mi:ss') );
98 x_prev_time := x_cur_time;
99
100 l_iret := GMICCAL.delete_ic_perd_bal(P_fiscal_year, P_period, x_whse_code);
101 IF (l_iret < 0) THEN
102 FND_MESSAGE.SET_NAME('GMI','IC_PERD_BAL_DELETE_ERR');
103 FND_MESSAGE.set_token('ERRNO', TO_CHAR(l_iret));
104 X_msg := FND_MESSAGE.GET;
105 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,X_msg);
106 /*BEGIN BUG#2589255 James Bernard */
107 /*Raising x_exception as GMI_CLOS_WAREHOUSES table has to be cleaned for
108 whse row being processed */
109 RAISE x_exception;
110 /*END BUG#2589255 */
111 RETURN;
112 END IF;
113 /* Debugging statements - Bug 3684980 */
114 x_cur_time := sysdate;
115 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' Completed '||X_msg || ' - '||
116 to_char(x_cur_time, 'hh24:mi:ss') );
117 x_prev_time := x_cur_time;
118 /***********************************************************
119 Insert into ic_perd_bal for this warehouse.
120 *********************************************************/
121
122 FND_MESSAGE.set_name('GMI', 'ICCAL_CREATE_PERD_MSG');
123 FND_MESSAGE.set_token('WHSE', x_whse_code);
124 X_msg := FND_MESSAGE.GET;
125 /* Debugging statements - Bug 3684980 */
126 x_cur_time := sysdate;
127 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' '||X_msg || ' - '||
128 to_char(x_cur_time, 'hh24:mi:ss') );
129 x_prev_time := x_cur_time;
130
131
132 x_period_id := to_number(P_period_id);
133 x_period := to_number(P_period);
134 x_op_code := to_number(P_op_code);
135 x_start_date := to_date(P_start_date,'YYYY/MM/DD HH24:MI:SS');
136 x_end_date := to_date(P_end_date,'YYYY/MM/DD HH24:MI:SS');
137
138 inventory_close(P_fiscal_year,
139 x_period_id,
140 x_period,
141 x_whse_code,
142 x_op_code,
143 x_start_date,
144 x_end_date);
145 IF (x_close_err = 1) THEN
146 /*BEGIN BUG#2589255 James Bernard */
147 /*Raising x_exception as GMI_CLOS_WAREHOUSES table has to be cleaned for
148 whse row being processed */
149 RAISE x_exception;
150 /*END BUG#2589255 */
151 RETURN;
152 END IF;
153 /* Debugging statements - Bug 3684980 */
154 FND_MESSAGE.set_name('GMI', 'ICCAL_CREATE_PERD_MSG');
155 FND_MESSAGE.set_token('WHSE', x_whse_code);
156 X_msg := FND_MESSAGE.GET;
157 x_cur_time := sysdate;
158 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' Completed '||X_msg || ' - '||
159 to_char(x_cur_time, 'hh24:mi:ss') );
160 x_prev_time := x_cur_time;
161 /**********************************************************
162 Calculate Costs --BUG#2230683 - removed calculate cost logic.
163 *********************************************************/
164
165
166 /********************************************************
167 Update the status of the warehouse
168 ********************************************************/
169 /* Debugging statements - Bug 3684980 */
170 FND_MESSAGE.set_name('GMI', 'ICCAL_UPDATEWHSE_STS_MSG');
171 FND_MESSAGE.set_token('WHSE', x_whse_code);
172 X_msg := FND_MESSAGE.GET;
173 x_cur_time := sysdate;
174 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' '||X_msg || ' - '|| to_char(x_cur_time, 'hh24:mi:ss') );
175 x_prev_time := x_cur_time;
176
177 l_iret := -1;
178 l_iret := GMICCAL.whse_status_update(x_whse_code, P_fiscal_year,
179 P_period, P_close_ind);
180 IF (l_iret < 0) THEN
181 FND_MESSAGE.SET_NAME('GMI','IC_WHSE_STATUS_UPDATE_ERR');
182 FND_MESSAGE.set_token('ERRNO', TO_CHAR(l_iret));
183 X_msg := FND_MESSAGE.GET;
184 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,X_msg);
185 /*BEGIN BUG#2589255 James Bernard */
186 /*Raising x_exception as GMI_CLOS_WAREHOUSES table has to be cleaned for
187 whse row being processed */
188 RAISE x_exception;
189 /*END BUG#2589255 */
190 RETURN;
191 END IF;
192
193 /* Debugging statements - Bug 3684980 */
194 FND_MESSAGE.set_name('GMI', 'ICCAL_UPDATEWHSE_STS_MSG');
195 FND_MESSAGE.set_token('WHSE', x_whse_code);
196 X_msg := FND_MESSAGE.GET;
197 x_cur_time := sysdate;
198 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' Completed '||X_msg || ' - '||
199 to_char(x_cur_time, 'hh24:mi:ss') );
200 x_prev_time := x_cur_time;
201 /***********************************************
202 Clean up database for whse row processed.
203 **********************************************/
204 DELETE gmi_clos_warehouses
205 where inventory_close_id = P_sequence AND
206 whse_code = x_whse_code;
207 IF (SQLCODE <> 0) THEN
208 FND_MESSAGE.SET_NAME('GMI','IC_DELETE_CLOSE_ERROR');
209 FND_MESSAGE.set_token('ERRORCODE', TO_CHAR(SQLCODE));
210 X_msg := FND_MESSAGE.GET;
211 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,X_msg);
212 RETURN;
213 END IF;
214
215
216 COMMIT;
217 FETCH Get_Whse into x_whse_code;
218 IF (Get_Whse%NOTFOUND) THEN
219 EXIT;
220 END IF;
221 END LOOP;
222 CLOSE Get_Whse;
223
224 /*********************************************
225 Update the Status of the Period if required.
226 *********************************************/
227
228 /* Debugging statements - Bug 3684980 */
229 FND_MESSAGE.set_name('GMI', 'ICCAL_UPDATE_PERIOD_MSG');
230 FND_MESSAGE.set_token('WHSE', x_whse_code);
231 X_msg := FND_MESSAGE.GET;
232 x_cur_time := sysdate;
233 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' '||X_msg || ' - '|| to_char(x_cur_time, 'hh24:mi:ss') );
234 x_prev_time := x_cur_time;
235
236 l_iret := -1;
237 l_iret := GMICCAL.period_status_update(P_orgn_code, p_fiscal_year,
238 P_period);
239
240 IF(l_iret < 0) THEN
241 FND_MESSAGE.SET_NAME('GMI','IC_WHSE_STATUS_UPDATE_ERR');
242 FND_MESSAGE.set_token('ERRNO', TO_CHAR(l_iret));
243 X_msg := FND_MESSAGE.GET;
244 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,X_msg);
245 RETURN;
246 END IF;
247
248 FND_MESSAGE.set_name('GMI', 'ICCAL_UPDATE_PERIOD_MSG');
249 FND_MESSAGE.set_token('WHSE', x_whse_code);
250 /* Debugging statements - Bug 3684980 */
251 x_cur_time := sysdate;
252 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' Completed '||X_msg || ' - '|| to_char(x_cur_time, 'hh24:mi:ss') );
253 x_prev_time := x_cur_time;
254
255 /***********************************************
256 If all is well, commit and give final message.
257 **********************************************/
258
259 COMMIT;
260 FND_MESSAGE.SET_NAME('GMI','ICCAL_CLOSE_SUCCESS_MSG');
261 X_msg := FND_MESSAGE.GET;
262 /* Debugging statements - Bug 3684980 */
263 x_cur_time := sysdate;
264 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,X_msg || ' - '|| to_char(x_cur_time, 'hh24:mi:ss') );
265 x_prev_time := x_cur_time;
266 RETURN;
267
268 /*BEGIN BUG#2589255 James Bernard */
269 /*Handling the user defined exception */
270 EXCEPTION
271 WHEN x_exception then
272 /***********************************************
273 Clean up GMI_CLOS_WAREHOUSE for whse row processed.
274 **********************************************/
275 DELETE gmi_clos_warehouses
276 where inventory_close_id = P_sequence AND
277 whse_code = x_whse_code;
278 COMMIT;
279 IF (SQL%ROWCOUNT = 0) THEN
280 FND_MESSAGE.SET_NAME('GMI','IC_DELETE_CLOSE_ERROR');
281 FND_MESSAGE.set_token('ERRORCODE', TO_CHAR(SQLCODE));
282 X_msg := FND_MESSAGE.GET;
283 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,X_msg);
284 RETURN;
285 END IF;
286
287 WHEN fnd_file.utl_file_error THEN
288
289 /***********************************************
290 Clean up GMI_CLOS_WAREHOUSE for whse row processed.
291 **********************************************/
292 DELETE gmi_clos_warehouses
293 where inventory_close_id = P_sequence AND
294 whse_code = x_whse_code;
295 COMMIT;
296 IF (SQL%ROWCOUNT = 0) THEN
297 FND_MESSAGE.SET_NAME('GMI','IC_DELETE_CLOSE_ERROR');
298 FND_MESSAGE.set_token('ERRORCODE', TO_CHAR(SQLCODE));
299 X_msg := FND_MESSAGE.GET;
300 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,X_msg);
301 RETURN;
302 END IF;
303
304 WHEN others THEN
305
306 /***********************************************
307 Clean up GMI_CLOS_WAREHOUSE for whse row processed.
308 **********************************************/
309 DELETE gmi_clos_warehouses
310 where inventory_close_id = P_sequence AND
311 whse_code = x_whse_code;
312 COMMIT;
313 IF (SQL%ROWCOUNT = 0) THEN
314 FND_MESSAGE.SET_NAME('GMI','IC_DELETE_CLOSE_ERROR');
315 FND_MESSAGE.set_token('ERRORCODE', TO_CHAR(SQLCODE));
316 X_msg := FND_MESSAGE.GET;
317 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,X_msg);
318 RETURN;
319 END IF;
320
321 /*END BUG#2589255 */
322
323 END RUN;
324
325 /* =====================================================
326 PROCEDURE:
327 inventory_close
328
329 DESCRIPTION:
330 This PL/SQL procedure is responsible for
331 inserting rows from the ic_perd_bal in conjuction
332 with the running of a preliminary or Final close.
333 This is the initial seeding of this table.
334 Then it calculates the usage and yield
335
336 SYNOPSIS:
337 icprded1_process.inventory_close(pfiscal_year,
338 pprd_id,
339 pperiod,
340 pwhse_code,
341 pop_code,
342 pprd_start_date,
343 pprd_end_date);
344
345 pfiscal_year - Fiscal Year of Calendar.
346 pprd_id - Period ID surrogate of period within
347 calendar.
348 pperiod - Period within calendar.
349 pwhse_code - warehouse code
350 pop_code - Operators identifier number.
351 pprd_start_date - start date of the period
352 pprd_end_date - end date of the period
353
354 BUG#2355980 VRA Srinivas 24-Jun-2002.
355 Added the code for picking the correct lot_ststaus
356 from the corresponding period.
357 The lot_status is fetched from ic_adjs_jnl table in the
358 query because if the item's status control is of type 2
359 then no transactions will be inserted in ic_tran_cmp table.
360 It is not required to use doc_type in the where clause
361 because for TRNI and TRNR also based on the value of profile
362 option IC$MOVEDIFFSTAT the status might change.
363 ======================================================*/
364
365 PROCEDURE inventory_close(pfiscal_year VARCHAR2,
366 pprd_id NUMBER,
367 pperiod NUMBER,
368 pwhse_code VARCHAR2,
369 pop_code NUMBER,
370 pprd_start_date DATE,
371 pprd_end_date DATE) IS
372
373 /* ================================================
374 Local Variable definitions and initialization:
375 ===============================================*/
376 l_item_id item_srg_type := 0;
377 l_prev_item_id item_srg_type := 0;
378 l_lot_id lot_srg_type := 0;
379 l_prev_lot_id lot_srg_type := 0;
380 l_whse_code whse_type := NULL;
381 l_location location_type := NULL;
382 l_prev_location location_type := NULL;
383 l_doc_type doc_type := NULL;
384 l_line_type ln_type := NULL;
385 l_reason_code reasoncode_type := NULL;
386 l_reason reasoncode_type := NULL;
387 l_trans_date DATE := NULL;
388 l_trans_id trans_srg_type := 0;
389 l_trans_qty quantity_type := 0;
390 l_trans_qty2 quantity_type := 0;
391 l_yield_qty quantity_type := 0;
392 l_yield_qty2 quantity_type := 0;
393 l_usage_qty quantity_type := 0;
394 l_usage_qty2 quantity_type := 0;
395 l_delta_qty quantity_type := 0;
396 l_delta_qty2 quantity_type := 0;
397 l_log_end_date DATE := NULL;
398 uwhse_code VARCHAR2(4);
399 --BEGIN BUG#2355980 Srinivas
400 x_lot_status VARCHAR2(4);
401 --END BUG#2355980
402
403 /**********************************************
404 Cursor Definitions:
405 **********************************************/
406
407 CURSOR usage_reason(v_reason_code reasoncode_type) IS
408 SELECT reason_code
409 FROM sy_reas_cds
410 WHERE flow_type = 0
411 AND delete_mark = 0
412 AND reason_code = v_reason_code;
413
414 /* Bug 3684980 - changed to ic_item_mst_b for perf. */
415 /* Also only populated ic_perd_bal with non-zero rows */
416 Cursor get_loct_onhand is
417 SELECT v.item_id ,v.lot_id ,
418 whse_code ,location ,ROUND(loct_onhand,9) onhand,
419 ROUND(loct_onhand2,9) onhand2,
420 v.lot_status,v.qchold_res_code
421 from ic_loct_inv v, ic_item_mst_b m
422 WHERE whse_code = pwhse_code
423 AND noninv_ind = 0
424 AND v.item_id = m.item_id
425 AND v.delete_mark = 0
426 AND (nvl(v.loct_onhand,0) <> 0 OR nvl(v.loct_onhand2,0) <> 0);
427
428 get_loct_onhand_rec get_loct_onhand%ROWTYPE;
429
430 --BEGIN BUG#2355980 Srinivas
431
432 Cursor lot_status_cur (x_item_id Number,x_lot_id Number,x_location varchar2,x_whse_code varchar2)
433 IS
434 SELECT lot_status
435 FROM ic_adjs_jnl
436 WHERE line_id = (select max(line_id) from ic_adjs_jnl
437 WHERE item_id = x_item_id
438 AND lot_id = x_lot_id
439 AND location = x_location
440 AND whse_code = x_whse_code
441 AND completed_ind = 1
442 AND Trunc(doc_date) BETWEEN Trunc(pprd_start_date)
443 AND Trunc(pprd_end_date));
444
445 --END BUG#2355980
446
447 /**********************************************
448 Joe DiIorio 09/05/2001 BUG#1930560
449 Added check for retrieving only inventory
450 items.
451 *********************************************/
452
453
454 /* Bug 3684980 - changed to union all and ic_item_mst_b for perf. */
455 CURSOR get_trans IS
456 SELECT p.item_id, lot_id, whse_code,
457 location, doc_type, line_type,
458 reason_code, trans_date, trans_id,
462 AND trans_date >= pprd_start_date
459 trans_qty, trans_qty2
460 FROM ic_tran_pnd p, ic_item_mst_b m
461 WHERE whse_code = uwhse_code
463 AND p.creation_date <= l_log_end_date
464 AND trans_qty <> 0
465 AND completed_ind = 1
466 AND p.delete_mark = 0
467 AND p.item_id = m.item_id
468 AND noninv_ind = 0
469 UNION ALL
470 SELECT c.item_id, lot_id, whse_code,
471 location, doc_type, line_type,
472 reason_code, trans_date, trans_id,
473 trans_qty, trans_qty2
474 FROM ic_tran_cmp c, ic_item_mst_b m
475 WHERE whse_code = uwhse_code
476 AND trans_date >= pprd_start_date
477 AND c.creation_date <= l_log_end_date
478 AND trans_qty <> 0
479 AND c.item_id = m.item_id
480 AND noninv_ind = 0
481 AND doc_type NOT IN ('STSI', 'GRDI',
482 'STSR', 'GRDR')
483 ORDER BY 1,2,3,4;
484
485 BEGIN
486
487 COMMIT;
488 EXECUTE IMMEDIATE 'set transaction read only';
489 /* Debugging statements - Bug 3684980 */
490 x_cur_time := sysdate;
491 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' Before opening get_loct_onhand cursor - '||
492 to_char(x_cur_time, 'hh24:mi:ss') );
493 x_prev_time := x_cur_time;
494
495 open get_loct_onhand;
496
497 /* Debugging statements - Bug 3684980 */
498 x_cur_time := sysdate;
499 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' After opening get_loct_onhand cursor - '||
500 to_char(x_cur_time, 'hh24:mi:ss') );
501 x_prev_time := x_cur_time;
502
503 select sysdate into l_log_end_date from dual;
504 uwhse_code := UPPER(pwhse_code);
505
506 /* Debugging statements - Bug 3684980 */
507 x_cur_time := sysdate;
508 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' Before opening get_trans cursor - '||
509 to_char(x_cur_time, 'hh24:mi:ss') );
510 x_prev_time := x_cur_time;
511
512 open get_trans;
513
514 /* Debugging statements - Bug 3684980 */
515 x_cur_time := sysdate;
516 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' After opening get_trans cursor - '||
517 to_char(x_cur_time, 'hh24:mi:ss') );
518 x_prev_time := x_cur_time;
519 COMMIT;
520
521 /* Debugging statements - Bug 3684980 */
522 x_cur_time := sysdate;
523 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' Before insert into ic_perd_bal - '||
524 to_char(x_cur_time, 'hh24:mi:ss') );
525 x_prev_time := x_cur_time;
526
527 LOOP
528 fetch get_loct_onhand into get_loct_onhand_rec;
529 EXIT WHEN get_loct_onhand%NOTFOUND;
530
531 -- Bug 3684980 - removed update and executed single insert
532 --BEGIN BUG#2355980 Srinivas
533 x_lot_status := get_loct_onhand_rec.lot_status;
534 OPEN lot_status_cur(get_loct_onhand_rec.item_id,get_loct_onhand_rec.lot_id,
535 get_loct_onhand_rec.location,get_loct_onhand_rec.whse_code);
536 FETCH lot_status_cur into x_lot_status;
537 CLOSE lot_status_cur;
538
539
540 INSERT INTO ic_perd_bal
541 (perd_bal_id, gl_posted_ind, period_id, fiscal_year, --bug#2230683
542 period, item_id, lot_id,
543 whse_code, location, loct_onhand, loct_onhand2,
544 loct_usage, loct_usage2, loct_yield, loct_yield2,
545 loct_value, lot_status, qchold_res_code,
546 log_end_date, creation_date, created_by, last_update_date,
547 last_updated_by)
548 VALUES(gmi_perd_bal_id_s.nextval,0, pprd_id, pfiscal_year, pperiod, get_loct_onhand_rec.item_id, get_loct_onhand_rec.lot_id,
549 get_loct_onhand_rec.whse_code, get_loct_onhand_rec.location, get_loct_onhand_rec.onhand,
550 get_loct_onhand_rec.onhand2, 0,0,0,0,0,
551 x_lot_status, get_loct_onhand_rec.qchold_res_code, l_log_end_date,
552 SYSDATE, pop_code, SYSDATE, pop_code);
553
554
555 --END BUG#2355980
556
557
558 END LOOP;
559 CLOSE get_loct_onhand;
560 /* Debugging statements - Bug 3684980 */
561 x_cur_time := sysdate;
562 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' After insert into ic_perd_bal - '||
563 to_char(x_cur_time, 'hh24:mi:ss') );
564 x_prev_time := x_cur_time;
565
566 /***********************************************
567 calc_usage_yield
568
569 DESCRIPTION:
570 This PL/SQL block is responsible for
571 calculating an item's usage and yield for
572 a given period in the Inventory Calendar.
573 This function is called from both the
574 preliminary and final CLOSE process.
575 ***********************************************/
576
577 FND_MESSAGE.set_name('GMI','ICCAL_PERD_MSG');
578 FND_MESSAGE.set_token('WHSE',pwhse_code);
579 X_msg := FND_MESSAGE.GET;
580 -- FND_FILE.PUT_LINE (FND_FILE.OUTPUT,X_msg);
581
582 FETCH get_trans INTO
583 l_item_id, l_lot_id, l_whse_code,
584 l_location, l_doc_type, l_line_type,
585 l_reason_code, l_trans_date, l_trans_id,
586 l_trans_qty, l_trans_qty2;
587
588 IF (get_trans%NOTFOUND) THEN
589 CLOSE get_trans;
590 ELSE
591
592 l_prev_item_id := l_item_id;
593 l_prev_lot_id := l_lot_id;
594 l_prev_location := l_location;
595
599 to_char(x_cur_time, 'hh24:mi:ss') );
596 /* Debugging statements - Bug 3684980 */
597 x_cur_time := sysdate;
598 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' Before Updating ic_perd_bal with transaction quantities - '||
600 x_prev_time := x_cur_time;
601
602 LOOP
603 /*==========================================
604 This first condition checks to see if something
605 has changed or we do not have anymore rows. If
606 this condition is true, it is time to write our
607 results to the ic_perd_bal table.
608 ==========================================*/
609 IF (l_prev_item_id <> l_item_id OR
610 l_prev_lot_id <> l_lot_id OR
611 l_prev_location <> l_location OR
612 get_trans%NOTFOUND) THEN
613 /*==========================================
614 Item, lot or location has changed so
615 let's grab what we accumulated and update
616 the perpetual balances for this item, lot,
617 and location.
618 ==========================================*/
619 UPDATE ic_perd_bal
620 SET loct_onhand = loct_onhand - ROUND(l_delta_qty, 9),
621 loct_onhand2 = loct_onhand2 - ROUND(l_delta_qty2, 9),
622 loct_usage = ROUND(l_usage_qty, 9),
623 loct_usage2 = ROUND(l_usage_qty2, 9),
624 loct_yield = ROUND(l_yield_qty, 9),
625 loct_yield2 = ROUND(l_yield_qty2, 9),
626 last_update_date = SYSDATE,
627 last_updated_by = pop_code
628 WHERE period_id = pprd_id
629 AND lot_id = l_prev_lot_id
630 AND whse_code = pwhse_code
631 AND location = l_prev_location
632 AND item_id = l_prev_item_id
633 AND fiscal_year = pfiscal_year
634 AND period = pperiod;
635 IF(SQL%ROWCOUNT = 0) THEN
636 /*============================================
637 This could be because of a 'PURGE EMPTY BALANCES'
638 was run on this particular item. Therefore, the
639 row does not exist so we have to insert it!
640 ============================================*/
641 INSERT INTO ic_perd_bal
642 (perd_bal_id, gl_posted_ind, period_id, lot_id, --bug#2230683
643 whse_code, location, item_id,
644 fiscal_year, period, loct_onhand, loct_onhand2,
645 loct_usage, loct_usage2, loct_yield, loct_yield2,
646 loct_value, lot_status, qchold_res_code,
647 log_end_date, creation_date, created_by,
648 last_update_date, last_updated_by, last_update_login)
649 VALUES
650 (gmi_perd_bal_id_s.nextval, 0, pprd_id, l_prev_lot_id,
651 pwhse_code, l_prev_location,
652 l_prev_item_id, pfiscal_year, pperiod,
653 ROUND((0 - l_delta_qty), 9),
654 ROUND((0 - l_delta_qty2), 9),
655 ROUND(l_usage_qty, 9),
656 ROUND(l_usage_qty2, 9),
657 ROUND(l_yield_qty, 9),
658 ROUND(l_yield_qty2, 9),
659 0, NULL, NULL, l_log_end_date, SYSDATE, pop_code,
660 SYSDATE,pop_code, NULL);
661 END IF;
662
663 /*==========================================
664 Let's clear our accumulators!
665 ==========================================*/
666 l_delta_qty := 0;
667 l_delta_qty2 := 0;
668 l_usage_qty := 0;
669 l_usage_qty2 := 0;
670 l_yield_qty := 0;
671 l_yield_qty2 := 0;
672 END IF; -- item/lot/location change if
673
674 /*==================================
675 If this was the last valid fetch then
676 bail from loop!
677 ==================================*/
678 IF (get_trans%NOTFOUND) THEN
679 EXIT;
680 END IF;
681 /*================================
682 For the row we just fetched, determine if
683 it's greater than the period end date. If
684 it is, this is our delta quantity!
685 ================================*/
686 IF (l_trans_date > (pprd_end_date + .99999)) THEN
687 l_delta_qty := l_delta_qty + l_trans_qty;
688 l_delta_qty2 := l_delta_qty2 + l_trans_qty2;
689 END IF;
690
691 /*======================================
692 Next accumulate our yields
693 ======================================*/
694 IF (l_doc_type = 'PROD' AND l_line_type > 0
695 AND l_trans_date <= (pprd_end_date + .99999)) THEN
696 l_yield_qty := l_yield_qty + l_trans_qty;
697 l_yield_qty2 := l_yield_qty2 + l_trans_qty2;
698 END IF;
699
700 /*==== =======================
701 Next accumulate our usages
702 =============================*/
703 IF (l_doc_type = 'PROD' AND l_line_type < 0
704 AND l_trans_date <= (pprd_end_date + .99999)) THEN
705 l_usage_qty := l_usage_qty + l_trans_qty;
706 l_usage_qty2 := l_usage_qty2 + l_trans_qty2;
707 ELSIF (l_doc_type = 'ADJI' OR l_doc_type = 'ADJR'
708 AND l_trans_date <= (pprd_end_date + .99999)) THEN
712 IF(usage_reason%FOUND) THEN
709 OPEN usage_reason(l_reason_code);
710 FETCH usage_reason INTO
711 l_reason;
713 l_usage_qty := l_usage_qty + l_trans_qty;
714 l_usage_qty2 := l_usage_qty2 + l_trans_qty2;
715 END IF;
716 CLOSE usage_reason;
717 END IF;
718 /*==============================================
719 Let's prepare for next fetch so we can determine
720 if the item, lot, location has changed or not.
721 ==============================================*/
722 l_prev_item_id := l_item_id;
723 l_prev_lot_id := l_lot_id;
724 l_prev_location := l_location;
725
726 FETCH get_trans INTO
727 l_item_id, l_lot_id, l_whse_code,
728 l_location, l_doc_type, l_line_type,
729 l_reason_code, l_trans_date, l_trans_id,
730 l_trans_qty, l_trans_qty2;
731
732 END LOOP;
733
734 CLOSE get_trans;
735 /* Debugging statements - Bug 3684980 */
736 x_cur_time := sysdate;
737 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,' After Updating ic_perd_bal with transaction quantities - '||
738 to_char(x_cur_time, 'hh24:mi:ss') );
739 x_prev_time := x_cur_time;
740
741 END IF;
742
743 EXCEPTION
744 WHEN OTHERS THEN
745 FND_MESSAGE.SET_NAME('GMI','IC_CLOSE_GENERAL_ERROR');
746 FND_MESSAGE.SET_TOKEN('ERRNO',TO_CHAR(SQLCODE));
747 X_msg := FND_MESSAGE.GET;
748 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,X_msg);
749 X_errmsg := SUBSTR(SQLERRM,1,159);
750 FND_FILE.PUT_LINE (FND_FILE.OUTPUT,X_errmsg);
751 x_close_err := 1;
752 RETURN;
753
754 END inventory_close;
755
756 END;