1 PACKAGE BODY GMICCAL AS
2 /* $Header: gmiccalb.pls 115.10 2003/03/21 14:32:30 jdiiorio ship $ */
3 /* =============================================
4 FUNCTION:
5 trans_date_validate
6
7 DESCRIPTION:
8 This PL/SQL function is responsible for
9 determining if the date passed in is in a
10 valid inventory calendar period based on
11 the organization code passed in.
12
13 SYNOPSIS:
14 iret := GMICCAL.trans_date_validate(trans_date, porgn_code,
15 pwhse_code);
16
17 trans_date - transaction date in format
18 dd-mmm-yyyy hh24:mi:ss
19 porgn_code - organization code of type
20 ic_cldr_dtl.orgn_code%TYPE;
21 pwhse_code - warehouse code of type
22 ic_whse_mst.whse_code%TYPE;
23
24 RETURNS:
25 <-29 RDBMS Oracle Error.
26 -29 Warehouse code is not found.
27 -28 Warehouse code not passed as a parameter.
28 -27 Organization code not passed as a parameter.
29 -26 Transaction date not passed as a parameter.
30 -25 Warehouse has been closed for the period.
31 -24 Company Code not found.
32 -23 Date is within a closed inventory calendar period.
33 -22 Period end date and close indicator not found.
34 -21 Fiscal Yr and Fiscal Yr beginning date not found.
35 0 Success
36 HISTORY:
37 WJ Harris III 03-DEC-98 Resynch r11.0
38 Break validation of warehouse code in this
39 function only to accomodate defect found in
40 plant/warehouse effectivities and production.
41 Warehouse validation will be done ONLY on the
42 warehouse code.
43 Jalaj Srivastava Bug 1579270
44 The orgn code passed in as parameter is the document
45 orgn code which should not be used for determining
46 the fiscal year and the period for the company.
47 We need the orgn code of the organization which owns
48 the whse code.
49 ============================================= */
50 FUNCTION trans_date_validate(trans_date DATE,
51 porgn_code VARCHAR2,
52 pwhse_code VARCHAR2)
53 RETURN NUMBER IS
54 /* Variable Declarations
55 ===================== */
56 l_period_date enddate_type;
57 l_begin_date enddate_type;
58 l_fiscal_yr ic_cldr_dtl.fiscal_year%TYPE;
59 l_period_ind ic_cldr_dtl.closed_period_ind%TYPE;
60 l_period ic_cldr_dtl.period%TYPE;
61 lp_co_code orgn_type;
62 lp_orgn_code orgn_type;
63 l_whse_code whse_type;
64 iret NUMBER;
65
66 /* Cursor Definitions
67 ================== */
68 CURSOR get_yr_begin_date IS
69 SELECT max(begin_date)
70 FROM ic_cldr_hdr
71 WHERE begin_date <= trans_date
72 AND delete_mark = 0
73 AND UPPER(orgn_code) = UPPER(lp_co_code);
74
75 CURSOR get_fiscal_yr IS
76 SELECT fiscal_year
77 FROM ic_cldr_hdr
78 WHERE begin_date = l_begin_date
79 AND delete_mark = 0
80 AND orgn_code = UPPER(lp_co_code);
81
82 CURSOR get_period_date IS
83 SELECT MIN(Period_end_date)
84 FROM ic_cldr_dtl
85 WHERE TRUNC(period_end_date, 'DD') >=
86 TRUNC(trans_date, 'DD')
87 AND fiscal_year = l_fiscal_yr
88 AND UPPER(orgn_code) = UPPER(lp_co_code);
89
90 CURSOR get_period_info IS
91 SELECT closed_period_ind, period
92 FROM ic_cldr_dtl
93 WHERE fiscal_year = l_fiscal_yr
94 AND period_end_date = l_period_date
95 AND UPPER(orgn_code) = UPPER(lp_co_code);
96
97 CURSOR is_whse_closed IS
98 SELECT whse_code
99 FROM ic_whse_sts
100 WHERE whse_code = UPPER(pwhse_code)
101 AND fiscal_year = l_fiscal_yr
102 AND period = l_period
103 AND close_whse_ind <> 3;
104
105 CURSOR is_whse_there IS
106 SELECT whse_code
107 FROM ic_whse_sts
108 WHERE whse_code = UPPER(pwhse_code)
109 AND fiscal_year = l_fiscal_yr
110 AND period = l_period;
111
112 CURSOR validate_whse IS
113 SELECT whse_code
114 FROM ic_whse_mst
115 where whse_code = UPPER(pwhse_code)
116 AND delete_mark = 0;
117
118 CURSOR get_whse_orgn_code IS
119 SELECT orgn_code
120 FROM ic_whse_mst
121 where whse_code = UPPER(pwhse_code);
122 --no need for checking the delete mark here
123 /* ================================================ */
124 BEGIN
125
126 l_period_date := NULL;
127 l_fiscal_yr := NULL;
128 lp_co_code := NULL;
129 l_whse_code := NULL;
130 l_period_ind := 0;
131
132 /* ======================================
133 OK .. Let's validate our parameter
134 list shall we.
135 ====================================== */
136
137 IF(trans_date IS NULL) THEN
138 RETURN INVCAL_DATE_PARM_ERR;
139 END IF;
140
141 IF(porgn_code IS NULL) THEN
142 RETURN INVCAL_ORGN_PARM_ERR;
143 END IF;
144
145 IF(pwhse_code IS NULL) THEN
146 RETURN INVCAL_WHSE_PARM_ERR;
147 END IF;
148
149 /* =======================================
150 Determine organization which owns
151 the whse.
152 ======================================= */
153 OPEN get_whse_orgn_code;
154 FETCH get_whse_orgn_code INTO
155 lp_orgn_code;
156 IF(get_whse_orgn_code%NOTFOUND) THEN
157 CLOSE get_whse_orgn_code;
158 RETURN INVCAL_WHSE_ERR;
159 END IF;
160 CLOSE get_whse_orgn_code;
161
162
163 /* =======================================
164 Step One - determine company
165 code of organization which owns the whse.
166 This calls the determine_company() function
167 ======================================= */
168
169 iret := GMICCAL.determine_company(lp_orgn_code, lp_co_code);
170 IF(iret <> 0) THEN
171 RETURN INVCAL_CO_ERR;
172
173 END IF;
174
175 /* ========================================
176 Step Two - Validate the warehouse passed
177 ======================================== */
178 OPEN validate_whse;
179 FETCH validate_whse INTO
180 l_whse_code;
181 IF(validate_whse%NOTFOUND) THEN
182
183 CLOSE validate_whse;
184 RETURN INVCAL_WHSE_ERR;
185 END IF;
186 CLOSE validate_whse;
187
188 /* ==================================
189 Step Three - determine the
190 Fiscal Yr. and the begining date
191 of the transaction date passed in.
192 ================================== */
193 OPEN get_yr_begin_date;
194 FETCH get_yr_begin_date INTO l_begin_date;
195
196 IF(get_yr_begin_date%NOTFOUND) THEN
197
198 CLOSE get_yr_begin_date;
199 RETURN INVCAL_FISCALYR_ERR;
200 END IF;
201
202 IF(l_begin_date IS NULL) THEN
203 CLOSE get_yr_begin_date;
204 RETURN INVCAL_FISCALYR_ERR;
205 END IF;
206
207 CLOSE get_yr_begin_date;
208 /* ============================================
209 STEP 4
210 Get the Fiscal Year associated to the begin
211 date fetched.
212 ============================================ */
213 OPEN get_fiscal_yr;
214 FETCH get_fiscal_yr INTO l_fiscal_yr;
215
216 IF(get_fiscal_yr%NOTFOUND) THEN
217
218 CLOSE get_fiscal_yr;
219 RETURN INVCAL_FISCALYR_ERR;
220 END IF;
221 CLOSE get_fiscal_yr;
222 /*============================================
223 STEP 5
224 Fetch the period end date based on Fiscal
225 Year, company, and transaction date.
226 ============================================ */
227 OPEN get_period_date;
228 FETCH get_period_date INTO l_period_date;
229
230 IF(get_period_date%NOTFOUND) THEN
231
232 CLOSE get_period_date;
233 RETURN INVCAL_PERIOD_ERR;
234
235 ELSIF(l_period_date IS NULL) THEN
236 CLOSE get_period_date;
237 RETURN INVCAL_PERIOD_ERR;
238 END IF;
239 CLOSE get_period_date;
240
241 /* ===========================================
242 STEP 6
243 Fetch the period and closed indicator.
244 Determine if the period is opened or closed.
245 1 = Open period
246 2 = Preliminary Close (This is still opened)
247 3 = The bad boy is closed. No Transactions
248 allowed.
249 ============================================ */
250 OPEN get_period_info;
251 FETCH get_period_info INTO l_period_ind, l_period;
252
253 IF(get_period_info%NOTFOUND) THEN
254
255 CLOSE get_period_info;
256 RETURN INVCAL_PERIOD_ERR;
257
258 ELSIF(l_period_ind < 3) THEN
259
260 /* ===========================================
261 If the calendar period is open for business
262 we must also ensure that the warehouse has
263 not been final closed and is also open for
264 business!
265 =========================================== */
266 OPEN is_whse_there;
267 FETCH is_whse_there INTO l_whse_code;
268 IF(is_whse_there%NOTFOUND) THEN
269 /* Never entered Inventory Close Form
270 ===================================== */
271 CLOSE is_whse_there;
272 CLOSE get_period_info;
273 RETURN 0;
274 END IF;
275 CLOSE is_whse_there;
276
277 /* ============================================
278 Warehouse exists in the warehouse Status table
279 so let's determine if it is closed.
280 ============================================== */
281 OPEN is_whse_closed;
282 FETCH is_whse_closed INTO l_whse_code;
283 IF(is_whse_closed%NOTFOUND) THEN
284
285 CLOSE is_whse_closed;
286 CLOSE get_period_info;
287 RETURN INVCAL_WHSE_CLOSED;
288 ELSE
289 CLOSE is_whse_closed;
290 CLOSE get_period_info;
291 RETURN 0;
292 END IF;
293
294 /* ===========================================
295 We have a date in a closed Inventory
296 Calendar Period. Notify calling program.
297 =========================================== */
298 ELSIF(l_period_ind = 3) THEN
299 CLOSE get_period_info;
300 RETURN INVCAL_PERIOD_CLOSED;
301 END IF;
302 CLOSE get_period_info;
303
304 EXCEPTION
305 WHEN OTHERS THEN
306
307 RETURN SQLCODE;
308 END trans_date_validate;
309 /* =============================================
310 FUNCTION:
311 delete_ic_perd_bal
312
313 DESCRIPTION:
314 This PL/SQL function is responsible for
315 deleting rows from the ic_perd_bal in conjuction
316 with the rerunning of a preliminary close.
317
318 SYNOPSIS:
319 iret := GMICCAL.delete_ic_perd_bal(pfiscal_yr, pperiod,
320 pwhse_code);
321
322 pfiscal_yr - The fiscal year for the Calendar.
323 pperiod - The period within the Fiscal year.
324 pwhse_code - warehouse code
325
326 RETURNS:
327 0 Success
328
329 HISTORY:
330 M Petrosino 25-Mar-1999 B859062
331 delete_ic_perd_bal was missing a return value.
332 added return 0 to function.
333 ============================================= */
334 FUNCTION delete_ic_perd_bal(pfiscal_year VARCHAR2,
335 pperiod NUMBER,
336 pwhse_code VARCHAR2)
337 RETURN NUMBER IS
338
339 /* ========================================*/
340 BEGIN
341
342 DELETE from ic_perd_bal
343 WHERE fiscal_year = UPPER(pfiscal_year)
344 AND period = pperiod
345 AND whse_code = UPPER(pwhse_code);
346
347 IF (SQL%ROWCOUNT = 0 ) THEN
348 /* This is not an error ..... it means
349 there were no rows to delete dude!
350 =================================== */
351 RETURN 0;
352 END IF;
353
354 RETURN 0;
355
356 EXCEPTION
357 WHEN OTHERS THEN
358 RETURN SQLCODE;
359 END delete_ic_perd_bal;
360 /* =============================================
361 FUNCTION:
362 insert_ic_perd_bal
363
364 DESCRIPTION:
365 This PL/SQL function is responsible for
366 inserting rows from the ic_perd_bal in conjuction
367 with the running of a preliminary or Final close.
368 This is the initial seeding of this table.
369
370 SYNOPSIS:
371 iret := GMICCAL.insert_ic_perd_bal(pwhse_code);
372
373 pfiscal_year - Fiscal Year of Calendar.
374 pper_id - Period ID surrogate of period within
375 calendar.
379
376 pperiod - Period within calendar.
377 pwhse_code - warehouse code
378 pop_code - Operators identifier number.
380 RETURNS:
381 < 0 Oracle RDBMS error.
382 >= 0 The number of rows inserted.
383 ============================================= */
384 FUNCTION insert_ic_perd_bal(pfiscal_year VARCHAR2,
385 pper_id NUMBER,
386 pperiod NUMBER,
387 pwhse_code VARCHAR2,
388 pop_code NUMBER)
389 RETURN NUMBER IS
390
391 /* ========================================*/
392 BEGIN
393
394 INSERT INTO ic_perd_bal
395 (perd_bal_id, gl_posted_ind, period_id, fiscal_year, --bug#2230683
396 period, item_id, lot_id,
397 whse_code, location, loct_onhand, loct_onhand2,
398 loct_usage, loct_usage2, loct_yield, loct_yield2,
399 loct_value, lot_status, qchold_res_code,
400 log_end_date, creation_date, created_by, last_update_date,
401 last_updated_by)
402 SELECT gmi_perd_bal_id_s.nextval, 0, pper_id, pfiscal_year,
403 pperiod, item_id, lot_id,
404 whse_code, location, ROUND(loct_onhand, 9),
405 ROUND(loct_onhand2, 9), 0,0,0,0,0,
406 lot_status, qchold_res_code, SYSDATE,
407 SYSDATE, pop_code, SYSDATE, pop_code
408 FROM ic_loct_inv
409 WHERE whse_code = pwhse_code
410 AND delete_mark = 0 ;
411
412 RETURN SQL%ROWCOUNT;
413
414 EXCEPTION
415 WHEN OTHERS THEN
416
417 RETURN SQLCODE;
418 END insert_ic_perd_bal;
419 /* =============================================
420 FUNCTION:
421 calc_usage_yield
422
423 DESCRIPTION:
424 This PL/SQL function is responsible for
425 calculating an item's usage and yield for
426 a given period in the Inventory Calendar.
427 This function is called from both the
428 preliminary and final CLOSE process.
429
430 SYNOPSIS:
431 iret := GMICCAL.calc_usage_yield(pwhse_code, pprd_start_date,
432 pprd_end_date, plog_end_date, pperiod,
433 pfiscal_year, pop_code);
434
435 pwhse_code - warehouse code
436 pprd_start_date - start date of the period
437 pprd_end_date - end date of the period
438 plog_end_date - current date.
439 pperiod - Period within calendar.
440 pfiscal_year - Fiscal Year of Calendar.
441 pop_code - Operators identifier number.
442
443 RETURNS:
444 < 0 Oracle RDBMS error.
445 0 Success.
446 ============================================= */
447 FUNCTION calc_usage_yield(pwhse_code VARCHAR2,
448 pprd_start_date DATE,
449 pprd_end_date DATE,
450 plog_end_date DATE,
451 pperiod NUMBER,
452 pprd_id NUMBER,
453 pfiscal_year VARCHAR2,
454 pop_code NUMBER)
455 RETURN NUMBER IS
456
457 /* Local Variable definitions and initialization:
458 ================================================= */
459 l_item_id item_srg_type := 0;
460 l_prev_item_id item_srg_type := 0;
461 l_lot_id lot_srg_type := 0;
462 l_prev_lot_id lot_srg_type := 0;
463 l_whse_code whse_type := NULL;
464 l_location location_type := NULL;
465 l_prev_location location_type := NULL;
466 l_doc_type doc_type := NULL;
467 l_line_type ln_type := NULL;
468 l_reason_code reasoncode_type := NULL;
469 l_reason reasoncode_type := NULL;
470 l_trans_date DATE := NULL;
471 l_trans_id trans_srg_type := 0;
472 l_trans_qty quantity_type := 0;
473 l_trans_qty2 quantity_type := 0;
474 l_yield_qty quantity_type := 0;
475 l_yield_qty2 quantity_type := 0;
476 l_usage_qty quantity_type := 0;
477 l_usage_qty2 quantity_type := 0;
478 l_delta_qty quantity_type := 0;
479 l_delta_qty2 quantity_type := 0;
480
481 /* Cursor Definitions:
482 =================== */
483 CURSOR usage_reason(v_reason_code reasoncode_type) IS
484 SELECT reason_code
485 FROM sy_reas_cds
486 WHERE flow_type = 0
487 AND delete_mark = 0
488 AND reason_code = v_reason_code;
489
490 CURSOR get_trans IS
491 SELECT item_id, lot_id, whse_code,
492 location, doc_type, line_type,
493 reason_code, trans_date, trans_id,
494 trans_qty, trans_qty2
495 FROM ic_tran_pnd
496 WHERE whse_code = UPPER(pwhse_code)
497 AND trans_date >= pprd_start_date
498 AND creation_date <= plog_end_date
499 AND trans_qty <> 0
500 AND completed_ind = 1
501 AND delete_mark = 0
502 UNION
506 trans_qty, trans_qty2
503 SELECT item_id, lot_id, whse_code,
504 location, doc_type, line_type,
505 reason_code, trans_date, trans_id,
507 FROM ic_tran_cmp
508 WHERE whse_code = UPPER(pwhse_code)
509 AND trans_date >= pprd_start_date
510 AND creation_date <= plog_end_date
511 AND trans_qty <> 0
512 AND doc_type NOT IN ('STSI', 'GRDI',
513 'STSR', 'GRDR')
514 ORDER BY 1,2,3,4;
515
516 /* ======================================== */
517 BEGIN
518
519 OPEN get_trans;
520 FETCH get_trans INTO
521 l_item_id, l_lot_id, l_whse_code,
522 l_location, l_doc_type, l_line_type,
523 l_reason_code, l_trans_date, l_trans_id,
524 l_trans_qty, l_trans_qty2;
525
526 IF(get_trans%NOTFOUND) THEN
527 CLOSE get_trans;
528 RETURN 0;
529 END IF;
530
531 l_prev_item_id := l_item_id;
532 l_prev_lot_id := l_lot_id;
533 l_prev_location := l_location;
534
535 /* =================================================== */
536 LOOP
537 /* This first condition checks to see if something
538 has changed or we do not have anymore rows. If
539 this condition is true, it is time to write our
540 results to the ic_perd_bal table.
541 =============================================== */
542 IF (l_prev_item_id <> l_item_id OR
543 l_prev_lot_id <> l_lot_id OR
544 l_prev_location <> l_location OR
545 get_trans%NOTFOUND) THEN
546
547
548 /* Item, lot or location has changed so
549 let's grab what we accumulated and update
550 the perpetual balances for this item, lot,
551 and location.
552 =========================================== */
553 UPDATE ic_perd_bal
554 SET loct_onhand = loct_onhand - ROUND(l_delta_qty, 9),
555 loct_onhand2 = loct_onhand2 - ROUND(l_delta_qty2, 9),
556 loct_usage = ROUND(l_usage_qty, 9),
557 loct_usage2 = ROUND(l_usage_qty2, 9),
558 loct_yield = ROUND(l_yield_qty, 9),
559 loct_yield2 = ROUND(l_yield_qty2, 9),
560 last_update_date = SYSDATE,
561 last_updated_by = pop_code
562 WHERE period_id = pprd_id
563 AND lot_id = l_prev_lot_id
564 AND whse_code = pwhse_code
565 AND location = l_prev_location
566 AND item_id = l_prev_item_id
567 AND fiscal_year = pfiscal_year
568 AND period = pperiod;
569
570 IF(SQL%ROWCOUNT = 0) THEN
571 /* This could be because of a 'PURGE EMPTY BALANCES'
572 was run on this particular item. Therefore, the
573 row does not exist so we have to insert it!
574 ================================================*/
575 INSERT INTO ic_perd_bal
576 (perd_bal_id, gl_posted_ind, period_id, lot_id, --bug#2230683
577 whse_code, location, item_id,
578 fiscal_year, period, loct_onhand, loct_onhand2,
579 loct_usage, loct_usage2, loct_yield, loct_yield2,
580 loct_value, lot_status, qchold_res_code,
581 log_end_date, creation_date, created_by,
582 last_update_date, last_updated_by, last_update_login)
583 VALUES
584 (gmi_perd_bal_id_s.nextval, 0, pprd_id, l_prev_lot_id, --bug#2230683
585 pwhse_code, l_prev_location,
586 l_prev_item_id, pfiscal_year, pperiod,
587 ROUND((0 - l_delta_qty), 9),
588 ROUND((0 - l_delta_qty2), 9),
589 ROUND(l_usage_qty, 9),
590 ROUND(l_usage_qty2, 9),
591 ROUND(l_yield_qty, 9),
592 ROUND(l_yield_qty2, 9),
593 0, NULL, NULL, SYSDATE, SYSDATE, pop_code,
594 SYSDATE,pop_code, NULL);
595 END IF;
596
597 /* Let's clear our accumulators!
598 ================================ */
599 l_delta_qty := 0;
600 l_delta_qty2 := 0;
601 l_usage_qty := 0;
602 l_usage_qty2 := 0;
603 l_yield_qty := 0;
604 l_yield_qty2 := 0;
605
606 END IF;
607
608 /* If this was the last valid fetch then
609 bail from loop!
610 ===================================== */
611 IF(get_trans%NOTFOUND) THEN
612 EXIT;
613 END IF;
614
615 /* For the row we just fetched, determine if
616 it's greater than the period end date. If
617 it is, this is our delta quantity!
618 =========================================
619 Joe DiIorio 06-JAN-1999 Bug#655581 Changed period
620 end date check from '+1' to '+.99999 to correct
621 problem where transactions from next day in period
622 are included in close balances.
623 ========================================= */
624 IF(l_trans_date > (pprd_end_date + .99999)) THEN
625 l_delta_qty := l_delta_qty + l_trans_qty;
629 /* Next accumulate our yields
626 l_delta_qty2 := l_delta_qty2 + l_trans_qty2;
627 END IF;
628
630 ==========================
631 Joe DiIorio 06-JAN-1999 Bug#655581 Changed period
632 end date check from '+1' to '+.99999 to correct
633 problem where transactions from next day in period
634 are included in close balances.
635 ========================== */
636 IF (l_doc_type = 'PROD' AND l_line_type > 0
637 AND l_trans_date <= (pprd_end_date + .99999)) THEN
638 l_yield_qty := l_yield_qty + l_trans_qty;
639 l_yield_qty2 := l_yield_qty2 + l_trans_qty2;
640 END IF;
641
642 /* Next accumulate our usages
643 ==========================
644 Joe DiIorio 06-JAN-1999 Bug#655581 Changed period
645 end date check from '+1' to '+.99999 to correct
646 problem where transactions from next day in period
647 are included in close balances.
648 ========================== */
649 IF (l_doc_type = 'PROD' AND l_line_type < 0
650 AND l_trans_date <= (pprd_end_date + .99999)) THEN
651 l_usage_qty := l_usage_qty + l_trans_qty;
652 l_usage_qty2 := l_usage_qty2 + l_trans_qty2;
653 ELSIF (l_doc_type = 'ADJI' OR l_doc_type = 'ADJR'
654 AND l_trans_date <= (pprd_end_date + .99999)) THEN
655 OPEN usage_reason(l_reason_code);
656 FETCH usage_reason INTO
657 l_reason;
658 IF(usage_reason%FOUND) THEN
659 l_usage_qty := l_usage_qty + l_trans_qty;
660 l_usage_qty2 := l_usage_qty2 + l_trans_qty2;
661 END IF;
662 CLOSE usage_reason;
663
664 END IF;
665
666 /* Let's prepare for next fetch so we can determine
667 if the item, lot, location has changed or not.
668 ================================================ */
669 l_prev_item_id := l_item_id;
670 l_prev_lot_id := l_lot_id;
671 l_prev_location := l_location;
672
673 FETCH get_trans INTO
674 l_item_id, l_lot_id, l_whse_code,
675 l_location, l_doc_type, l_line_type,
676 l_reason_code, l_trans_date, l_trans_id,
677 l_trans_qty, l_trans_qty2;
678 END LOOP;
679 /* ======================================================== */
680 CLOSE get_trans;
681 RETURN 0;
682
683
684 EXCEPTION
685 WHEN OTHERS THEN
686
687 RETURN SQLCODE;
688 END calc_usage_yield;
689 /* =============================================
690 FUNCTION:
691 whse_status_update
692
693 DESCRIPTION:
694 This PL/SQL function is responsible for
695 updating the warehouse status
696 as the result of either an inventory calendar
697 preliminary or final close of a warehouse.
698
699 SYNOPSIS:
700 iret := GMICCAL.whse_status_update(pwhse_code,
701 pperiod, pclose_type);
702
703 pwhse_code - warehouse which has been preliminary
704 or final closed.
705 pfiscal_year- The company fiscal Year for the
706 inventory calendar.
707 pperiod - The inventory calendar period.
708 pclose_type - 2 denotes preliminary close of warehouse.
709 3 denotes Final Close of warehouse.
710
711 RETURNS:
712 0 Success
713 -30 Update warehouse status error.
714 HISTORY:
715 Sastry 05/17/2002 BUG#2356476
716 Modified the Update statement to update the columns
717 last_updated_by,last_update_date and last_update_login.
718 ============================================= */
719 FUNCTION whse_status_update(pwhse_code VARCHAR2,
720 pfiscal_year VARCHAR2,
721 pperiod NUMBER,
722 pclose_type NUMBER) RETURN NUMBER IS
723
724 /* ================================================ */
725 BEGIN
726 -- BEGIN BUG#2356476 Sastry
727 -- Also update last_updated_by,last_update_date and last_update_login.
728 UPDATE ic_whse_sts
729 SET log_end_date = SYSDATE,
730 close_whse_ind = pclose_type,
731 last_updated_by = FND_GLOBAL.USER_ID,
732 last_update_date = SYSDATE,
733 last_update_login = FND_GLOBAL.LOGIN_ID
734 WHERE fiscal_year = pfiscal_year
735 AND period = pperiod
736 AND whse_code = UPPER(pwhse_code);
737 -- END BUG#2356476
738
739 IF(SQL%ROWCOUNT = 0) THEN
740 RETURN INVCAL_WHSESTS_UPDATE_ERR;
741 END IF;
742
743 RETURN 0;
744
745 EXCEPTION
746 WHEN OTHERS THEN
747
748 RETURN SQLCODE;
749
750 END whse_status_update;
751 /* =============================================
752 FUNCTION:
753 period_status_update
754
755 DESCRIPTION:
756 This PL/SQL function is responsible for
757 updating the Inventory Calendar Period status
761 SYNOPSIS:
758 as the result of either an inventory calendar
759 preliminary or final close of a warehouse.
760
762 iret := GMICCAL.period_status_update(pco_code,
763 pfiscal_year, pperiod, pclose_type);
764
765 pco_code - The company for the Inventory Calendar.
766 pfiscal_year- The fiscal year of the Calendar.
767 pperiod - The inventory calendar period.
768
769 RETURNS:
770 0 Success
771 -31 Update period status error.
772 ============================================= */
773 FUNCTION period_status_update(pco_code VARCHAR2,
774 pfiscal_year VARCHAR2,
775 pperiod NUMBER) RETURN NUMBER IS
776 /* Local Variables:
777 ================ */
778 l_whse_code whse_type := NULL;
779 l_close_type NUMBER := NULL;
780
781 /* Cursor Definitions
782 ================== */
783 CURSOR determine_type IS
784 SELECT s.whse_code
785 FROM ic_whse_sts s, ic_whse_mst w,
786 sy_orgn_mst o
787 WHERE o.co_code = UPPER(pco_code)
788 AND w.orgn_code = o.orgn_code
789 AND s.whse_code = w.whse_code
790 AND s.fiscal_year = pfiscal_year
791 AND s.period = pperiod
792 AND s.close_whse_ind <> 3;
793
794 /* ================================================ */
795 BEGIN
796
797 OPEN determine_type;
798 FETCH determine_type INTO
799 l_whse_code;
800
801 IF(l_whse_code IS NULL) THEN
802 /* All the warehouses are final closed
803 ====================================== */
804 l_close_type := 3;
805 ELSE
806 /* Some of the warehouses are still opened
807 ========================================== */
808 l_close_type := 2;
809 END IF;
810 CLOSE determine_type;
811
812
813 UPDATE ic_cldr_dtl
814 SET closed_period_ind = l_close_type,
815 last_update_date = SYSDATE
816 WHERE orgn_code = pco_code
817 AND fiscal_year = pfiscal_year
818 AND period = pperiod;
819
820 IF(SQL%ROWCOUNT = 0) THEN
821 RETURN INVCAL_PRDSTS_UPDATE_ERR;
822 END IF;
823 RETURN 0;
824
825 EXCEPTION
826 WHEN OTHERS THEN
827
828 RETURN SQLCODE;
829
830
831 END period_status_update;
832 /* =============================================
833 FUNCTION:
834 determine_company
835
836 DESCRIPTION:
837 This PL/SQL function is responsible for
838 determining the company associated to the
839 passed in organization code.
840
841 SYNOPSIS:
842 iret := GMICCAL.determine_company(porgn_code,
843 pout_orgn_code);
844
845 porgn_code - organization code of type
846 ic_cldr_dtl.orgn_code%TYPE;
847 porgn_code - will hold the company code of type
848 ic_cldr_dtl.orgn_code%TYPE;
849
850
851 RETURNS:
852 0 Success
853 -41 Company not found.
854 -42 Organization not valid.
855 <-42 RDBMS Oracle Error.
856 ============================================= */
857 FUNCTION determine_company(porgn_code VARCHAR2,
858 pout_co_code IN OUT NOCOPY VARCHAR2) RETURN NUMBER IS
859
860 /* Local Variables
861 =============== */
862 l_orgn_code orgn_type;
863 l_company orgn_type;
864
865 /* Cursor Definitions
866 ================== */
867 CURSOR validate_orgn_code IS
868 SELECT orgn_code
869 FROM sy_orgn_mst
870 WHERE orgn_code = UPPER(porgn_code)
871 AND delete_mark = 0;
872
873 CURSOR get_company IS
874 SELECT UPPER(co_code)
875 FROM sy_orgn_mst
876 WHERE orgn_code = UPPER(porgn_code)
877 AND delete_mark = 0;
878
879 /* ================================================ */
880 BEGIN
881
882 l_orgn_code := NULL;
883 l_company := NULL;
884
885
886 /* Step One - determine if we have a
887 valid organization being passed.
888 If we do not inform the caller and
889 bail!
890 ================================== */
891 OPEN validate_orgn_code;
892 FETCH validate_orgn_code INTO l_orgn_code;
893
894 IF(validate_orgn_code%NOTFOUND) THEN
895
896 CLOSE validate_orgn_code;
897 RETURN ORGN_VAL_ERR;
898
899 END IF;
900 CLOSE validate_orgn_code;
901 /* ===================================
902 Step Two - Determine the company
903 associated to the organization
904 =================================== */
905 OPEN get_company;
906 FETCH get_company INTO l_company;
907
908 IF(get_company%NOTFOUND) THEN
909 CLOSE get_company;
910 RETURN ORGN_CO_ERR;
911
912 END IF;
916 We have been successful! Return
913 CLOSE get_company;
914
915 /* ==================================
917 the company code to the caller
918 and a status of zero.
919 ================================== */
920 pout_co_code := l_company;
921
922
923 RETURN 0;
924
925
926 EXCEPTION
927 WHEN OTHERS THEN
928
929 RETURN SQLCODE;
930 END determine_company;
931
932 END;
[Home] [Help]