1 PACKAGE BODY BOM_CALENDAR_API_BK AS
2 -- $Header: BOMCALAB.pls 120.1 2005/06/21 05:29:12 appldev ship $
3 -- =========================================================================+
4 -- Copyright (c) 1993 Oracle Corporation Belmont, California, USA |
5 -- All rights reserved. |
6 -- =========================================================================+
7 -- |
8 -- File Name : BOMCALAB.pls |
9 -- Description : This API will validate the input date against input |
10 -- calendar as a valid working day. |
11 -- Parameters: x_calendar_code the calendar user wants to use |
12 -- x_date input date user wants to verify |
13 -- x_working_day show is this date a working date |
14 -- err_code error code, if any error happens |
15 -- err_meg error message, if any error happens |
16 -- Revision |
17 -- Jen-Ya Ku Creation |
18 -- Rahul Chitko Fixed bug 1607927. Trucated the date in |
19 -- checking the work day.
20 -- =========================================================================
21 PROCEDURE CHECK_WORKING_DAY (
22 x_calendar_code IN VARCHAR2,
23 x_date IN DATE,
24 x_working_day IN OUT NOCOPY /* file.sql.39 change */ BOOLEAN,
25 err_code IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
26 err_meg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR)
27 AS
28 l_date_seq_num NUMBER := NULL;
29
30 BEGIN
31 select CA.seq_num
32 into l_date_seq_num
33 from BOM_CALENDAR_DATES CA
34 where CA.calendar_code = x_calendar_code
35 and CA.calendar_date = trunc(x_date)
36 and CA.exception_set_id = -1;
37
38 if (l_date_seq_num is NULL) then
39 x_working_day := FALSE;
40 else
41 x_working_day := TRUE;
42 end if;
43
44 err_code := 0;
45 EXCEPTION
46 WHEN NO_DATA_FOUND then
47 err_code := -1;
48 err_meg := 'BOMCALAB: Invalid Calendar code or invalid input date is found';
49 WHEN others then
50 err_code := SQLCODE;
51 err_meg := substrb(SQLERRM,1,80);
52 END CHECK_WORKING_DAY;
53
54 -- =========================================================================+
55 -- |
56 -- |
57 -- Description : Given a calendar, a date and a time, this function will |
58 -- check to see if there is any shift working on that date at|
59 -- that time. Returns TRUE if there is a working shift and |
60 -- FALSE otherwise. |
61 -- Parameters: x_calendar_code the calendar user wants to use |
62 -- x_date input date user wants to verify |
63 ---- err_code error code, if any error happens |
64 -- err_meg error message, if any error happens |
65 -- Revision |
66 -- Punit Jain Creation |
67 -- =========================================================================
68
69 FUNCTION CHECK_WORKING_SHIFT (
70 x_calendar_code IN VARCHAR2,
71 x_date IN DATE,
72 err_code IN OUT NOCOPY /* file.sql.39 change */ NUMBER,
73 err_meg IN OUT NOCOPY /* file.sql.39 change */ VARCHAR)
74 return BOOLEAN
75 is
76
77 CURSOR shift_cursor IS
78 SELECT CA.shift_num, SH.from_time, SH.to_time, DT.seq_num
79 FROM BOM_CALENDAR_SHIFTS CA, BOM_SHIFT_TIMES SH,
80 BOM_SHIFT_DATES DT
81 WHERE CA.CALENDAR_CODE=x_calendar_code and
82 SH.CALENDAR_CODE=x_calendar_code and
83 CA.SHIFT_NUM = SH.SHIFT_NUM and
84 DT.CALENDAR_CODE = x_calendar_code and
85 DT.SHIFT_NUM = CA.SHIFT_NUM and
86 DT.EXCEPTION_SET_ID = -1 and
87 to_char(DT.SHIFT_DATE, 'YYYY/MM/DD')
88 = to_char(x_date, 'YYYY/MM/DD');
89
90 dummy NUMBER;
91 x_time NUMBER;
92 working_shift BOOLEAN:= FALSE;
93
94 BEGIN
95
96 SELECT 1
97 INTO dummy
98 FROM BOM_CALENDARS
99 WHERE CALENDAR_CODE = x_calendar_code;
100
101 x_time := to_number(to_char(x_date, 'SSSSS'));
102
103 FOR shift_var IN shift_cursor LOOP
104 -- check if the input date is a valid working date for this shift
105
106 if (shift_var.seq_num is not NULL) then
107
108 /* This is a working day for this shift and so check if the working
109 times for this shift contain the input time
110 It is possible that a shift extends past midnight. In this case the
111 shift_to_time will be less than the shift_from_time.
112 */
113
114 if shift_var.from_time < shift_var.to_time then
115 if (x_time between shift_var.from_time and shift_var.to_time) then
116 working_shift:= TRUE;
117 end if;
118 elsif (x_time between shift_var.from_time and 86400 or
119 x_time between 0 and shift_var.to_time) then
120 working_shift:=TRUE;
121 end if;
122
123
124 end if;
125 end loop;
126
127 return (working_shift);
128
129 EXCEPTION
130 WHEN NO_DATA_FOUND then
131 err_code := -1;
132 err_meg := 'BOMCALAB: Invalid Calendar code or invalid input date is found';
133 return (FALSE);
134
135 WHEN others then
136 err_code := SQLCODE;
137 err_meg := substrb(SQLERRM,1,80);
138 return FALSE;
139
140 END check_working_shift;
141
142
143 END BOM_CALENDAR_API_BK;
[Home] [Help]