[Home] [Help]
Skip to content
PACKAGE BODY: APPS.JL_BR_WORKDAY_CALENDAR
Source
1 PACKAGE BODY JL_BR_WORKDAY_CALENDAR AS
2 /* $Header: jlbrscdb.pls 120.3.12010000.4 2009/09/24 12:47:15 mkandula ship $ */
3
4 PROCEDURE JL_BR_CHECK_DATE (
5 p_date IN varchar2,
6 p_calendar IN varchar2,
7 p_city IN varchar2,
8 p_action IN varchar2,
9 p_new_date IN OUT NOCOPY varchar2,
10 p_status IN OUT NOCOPY number)
11 IS
12 x_disable_date date;
13 x_date date;
14 x_seq_num number;
15 x_prior_date date;
16 x_next_date date;
17 x_type number;
18 flag_exit number(38);
19 local_flag_exit number(38); -- Bug 801009
20 BEGIN
21 --
22 -- Bug 1176138
23 -- When payment action is KEEP (= 3)do not validate the date; simply return
24 --
25 if nvl(p_action,'3') = '3' then
26 p_status := 0;
30
27 p_new_date := p_date;
28 return;
29 end if;
31 select to_date(p_date,'DD-MM-YYYY')
32 into x_date
33 from dual;
34
35 p_status:= 0;
36 flag_exit := 0;
37
38 if x_date IS NOT NULL then
39 WHILE flag_exit = 0 LOOP
40 ----
41 -- Bug 1052225
42 -- Carry out Local Holiday processing ONLY when city is NOT NULL
43 --
44 if p_city IS NOT NULL then
45 BEGIN
46 local_flag_exit := 0;
47 WHILE local_flag_exit = 0 LOOP
48 BEGIN
49 SELECT a.local_holiday_type, b.disable_date
50 INTO x_type, x_disable_date
51 FROM jl_br_local_holiday_dates a,
52 jl_br_local_holiday_sets b
53 WHERE b.local_holiday_set_name = p_city -- Bug 801019
54 AND a.local_holiday_set_id = b.local_holiday_set_id
55 AND a.local_holiday_date = x_date
56 AND nvl(b.disable_date,sysdate + 1) > sysdate;
57
58 /* type = 1 -> Working-day */
59 /* type = 2 -> Non-working-day */
60 /* action = 1 -> Anticipate */
61 /* action = 2 -> Postpone */
62 /* action = 3 -> Keep */
63
64 if x_type = 2 then
65 if p_action = '1' then
66 x_date := x_date - 1;
67 elsif p_action = '2' then
68 x_date := x_date + 1;
69 end if;
70 end if;
71
72 EXCEPTION
73 WHEN NO_DATA_FOUND THEN
74 flag_exit := 1;
75 local_flag_exit := 1; -- Bug 801009
76 END;
77 END LOOP;
78 END;
79 end if;
80 ----
81
82 BEGIN
83 SELECT seq_num, prior_date, next_date
84 INTO x_seq_num, x_prior_date, x_next_date
85 FROM BOM_CALENDAR_DATES
86 WHERE calendar_code = p_calendar
87 AND calendar_date = x_date;
88
89 /* 2=OFF -> non-working-day */
90 /* NULL -> non-working-day */
91 if x_seq_num IS NULL then
92 flag_exit := 0;
93 if p_action = '1' then
94 x_date := x_prior_date;
95 elsif p_action = '2' then
96 x_date := x_next_date;
97 end if;
98 else
99 flag_exit := 1;
100 select to_char(x_date,'DD-MM-YYYY')
101 into p_new_date
102 from dual;
103 end if;
104
105 EXCEPTION
106 WHEN NO_DATA_FOUND THEN
107 flag_exit := 1; -- bug: 8618972
108 p_status := -1;
109 END;
110 END LOOP;
111 END IF;
112 END JL_BR_CHECK_DATE;
113
114 PROCEDURE JL_BR_CHECK_DATE (
115 p_date IN varchar2,
116 p_calendar IN varchar2,
117 p_city IN varchar2,
118 p_action IN varchar2,
119 p_new_date IN OUT NOCOPY varchar2,
120 p_status IN OUT NOCOPY number,
121 p_state IN varchar2) -- Bug 2319552
122 IS
123 x_disable_date date;
124 x_date date;
125 x_seq_num number;
126 x_prior_date date;
127 x_next_date date;
128 x_type number;
129 flag_exit number(38);
130 local_flag_exit number(38); -- Bug 801009
131 BEGIN
132 --
133 -- Bug 1176138
134 -- When payment action is KEEP (= 3)do not validate the date; simply return
135 --
136 if nvl(p_action,'3') = '3' then
137 p_status := 0;
138 p_new_date := p_date;
139 return;
140 end if;
141
142 select to_date(p_date,'DD-MM-YYYY')
143 into x_date
144 from dual;
145
146 p_status:= 0;
147 flag_exit := 0;
148
149 if x_date IS NOT NULL then
150 WHILE flag_exit = 0 LOOP
151 ----
152 -- Bug 1052225
153 -- Carry out Local Holiday processing ONLY when city is NOT NULL
154 --
155 if p_city IS NOT NULL then
156 BEGIN
157 local_flag_exit := 0;
158 WHILE local_flag_exit = 0 LOOP
159 BEGIN
160 SELECT a.local_holiday_type, b.disable_date
161 INTO x_type, x_disable_date
162 FROM jl_br_local_holiday_dates a,
163 jl_br_local_holiday_sets b
164 WHERE b.local_holiday_set_name = p_city -- Bug 801019
165 AND nvl(b.state,'$') = nvl(p_state,'$') -- Bug 2319552
166 AND a.local_holiday_set_id = b.local_holiday_set_id
167 AND a.local_holiday_date = x_date
168 AND nvl(b.disable_date,sysdate + 1) > sysdate;
169
170 /* type = 1 -> Working-day */
171 /* type = 2 -> Non-working-day */
172 /* action = 1 -> Anticipate */
173 /* action = 2 -> Postpone */
174 /* action = 3 -> Keep */
175
176 if x_type = 2 then
177 if p_action = '1' then
178 x_date := x_date - 1;
179 elsif p_action = '2' then
180 x_date := x_date + 1;
181 end if;
182 end if;
183
184 EXCEPTION
185 WHEN NO_DATA_FOUND THEN
186 flag_exit := 1;
187 local_flag_exit := 1; -- Bug 801009
188 END;
189 END LOOP;
190 END;
191 end if;
192 ----
193
194 BEGIN
195 SELECT seq_num, prior_date, next_date
196 INTO x_seq_num, x_prior_date, x_next_date
197 FROM BOM_CALENDAR_DATES
198 WHERE calendar_code = p_calendar
199 AND calendar_date = x_date;
200
201 /* 2=OFF -> non-working-day */
202 /* NULL -> non-working-day */
203 if x_seq_num IS NULL then
204 flag_exit := 0;
205 if p_action = '1' then
206 x_date := x_prior_date;
207 elsif p_action = '2' then
208 x_date := x_next_date;
209 end if;
210 else
211 flag_exit := 1;
212 select to_char(x_date,'DD-MM-YYYY')
213 into p_new_date
214 from dual;
215 end if;
216
217 EXCEPTION
218 WHEN NO_DATA_FOUND THEN
219 flag_exit := 1; -- bug: 8618972
220 p_status := -1;
221 END;
222 END LOOP;
223 END IF;
224 END JL_BR_CHECK_DATE;
225
226 PROCEDURE JL_BR_PAY_DATE_BDC (
227 p_date IN date,
228 p_new_date IN OUT NOCOPY date,
229 p_status IN OUT NOCOPY number)
230 IS
231
232 l_calendar VARCHAR2(10);
233 l_payment_action VARCHAR2(1);
234 l_payment_location VARCHAR2(80);
235 l_city VARCHAR2(30);
236 l_state VARCHAR2(30);
237 returned_date VARCHAR2(11);
238 errcode1 NUMBER;
239 l_org_id NUMBER(38);
240
241 BEGIN
242
243 l_org_id := mo_global.get_current_org_id;
244
245 if l_org_id is null then
246 return;
247 end if;
248
249 l_payment_location := jl_zz_sys_options_pkg.get_payment_location(l_org_id);
250
251 IF NVL(l_payment_location,'$') = '1' THEN -- 1 COMPANY
252 --Replicating JL_ZZ_AP_LIBRARY_1_PKG.get_city_frm_sys; here with ORG_ID
253 --as the above api is not yet accepting org_id. As is it might work for
254 --calls from forms where context is set to single org, but same wont
255 --work from pages that do not set this context (e.g. this current case
256 --where payment date is now in OA page : more info on bug 5437175)
257
258 Declare
259
260 l_ledger_id NUMBER;
261 l_BSV VARCHAR2(30);
262
263
264 Cursor CityTReg Is
265 Select etb.town_or_city, etb.region_2
266 From
267 xle_establishment_v etb
268 ,xle_bsv_associations bsv
269 ,gl_ledger_le_v gl
270 Where
271 etb.legal_entity_id = gl.legal_entity_id
272 And bsv.legal_parent_id = etb.legal_entity_id
273 And etb.establishment_id = bsv.legal_construct_id
274 And bsv.entity_name = l_BSV
275 And gl.ledger_id = l_ledger_id;
276
277 Begin
278 Begin
279 select set_of_books_id,substr(global_attribute4,1,25)
280 into l_ledger_id,l_BSV
281 from ap_system_parameters_all
282 where nvl(org_id,-99) = nvl(l_org_id,-99);
283 Exception
284 when others THEN
285 NULL;
286 End;
287 For CityReg2 IN CityTReg Loop
288 l_city := CityReg2.town_or_city;
289 l_state := CityReg2.region_2;
290 End Loop;
291
292
293 End;
294 /* OA page VO doesnt have vendor_site_id, so its not possible
295 to have this functionality in R12
296 ...
297 ELSIF NVL(l_payment_location,'$') = '2' THEN -- 2 SUPPLIER
298 JL_ZZ_AP_LIBRARY_1_PKG.get_city_frm_povend(p_vendor_site_id,
299 l_city, 1, errcode1,
300 l_state);
301 */
302 END IF;
303
304 l_payment_action := jl_zz_sys_options_pkg.get_payment_action(l_org_id);
305 l_calendar := jl_zz_sys_options_pkg.get_calendar;
306
307 if (l_payment_location is null or
308 l_payment_action is null or
309 l_calendar is null or
310 l_city is null or
311 l_state is null
312 ) then
313 return;
314 end if;
315
316 jl_br_workday_calendar.jl_br_check_date(to_char(p_date,'DD-MM-YYYY'),
317 l_calendar,
318 l_city,
319 l_payment_action,
320 returned_date,
321 p_status,
322 l_state);
323 if p_status = 0 then /* procedure successfull */
324 p_new_date := to_date(returned_date,'DD-MM-YYYY');
325 end if;
326 END JL_BR_PAY_DATE_BDC;
327
328 END JL_BR_WORKDAY_CALENDAR;