1 package body msc_hub_calendar as
2 /* $Header: MSCHBCAB.pls 120.9 2008/03/17 22:20:42 hulu noship $ */
3
4 function first_work_date(p_calendar_code in varchar2,
5 p_sr_instance_id in number,
6 p_bkt_type in number,
7 p_bkt_start_date date) return date is
8 x_date date;
9 l_seq_num number;
10 begin
11 if (p_bkt_type =1 ) then
12 -- this is the day bucket
13 -- check whether this day is the working date
14 select mcd.calendar_date into x_date
15 from msc_calendar_dates mcd,msc_calendars mc
16 where mc.calendar_code = p_calendar_code
17 and mc.sr_instance_id = p_sr_instance_id
18 and mc.calendar_code=mcd.calendar_code
19 and mc.sr_instance_id=mcd.sr_instance_id
20 -- and mc.exception_set_id = mcd.exception_set_id
21 and mcd.calendar_date = p_bkt_start_date
22 and mcd.seq_num is not null;
23
24 return x_date;
25
26 elsif (p_bkt_type = 2) then
27
28 select min(mcd.calendar_date) into x_date
29 from msc_calendar_dates mcd, msc_calendars mc,msc_cal_week_start_dates mcwsd
30 where mc.calendar_code = p_calendar_code
31 and mc.sr_instance_id = p_sr_instance_id
32 and mc.calendar_code = mcd.calendar_code
33 and mc.sr_instance_id = mcd.sr_instance_id
34 -- and mc.exception_set_id = mcd.exception_set_id
35 and mcd.seq_num is not null
36 and mcd.calendar_code = mcwsd.calendar_code
37 and mcd.sr_instance_id = mcwsd.sr_instance_id
38 and mcd.exception_set_id =mcwsd.exception_set_id
39 and mcwsd.WEEK_START_DATE = p_bkt_start_date
40 and mcd.calendar_date >=mcwsd.WEEK_START_DATE
41 and mcd.calendar_date<mcwsd.next_date;
42
43 return x_date;
44 elsif (p_bkt_type=3) then
45
46 select min(mcd.calendar_date) into x_date
47 from msc_calendar_dates mcd, msc_calendars mc,msc_period_start_dates mpsd
48 where mc.calendar_code = p_calendar_code
49 and mc.sr_instance_id = p_sr_instance_id
50 and mc.calendar_code = mcd.calendar_code
51 and mc.sr_instance_id = mcd.sr_instance_id
52 -- and mc.exception_set_id = mcd.exception_set_id
53 and mcd.seq_num is not null
54 and mcd.calendar_code = mpsd.calendar_code
55 and mcd.sr_instance_id = mpsd.sr_instance_id
56 and mcd.exception_set_id =mpsd.exception_set_id
57 and mpsd.PERIOD_START_DATE = p_bkt_start_date
58 and mcd.calendar_date >=mpsd.PERIOD_START_DATE
59 and mcd.calendar_date<mpsd.next_date;
60
61 return x_date;
62 end if;
63 exception
64
65 when no_data_found then
66 return null;
67 end first_work_date;
68
69
70
71
72 function last_work_date(p_plan_id in number,
73 p_sr_instance_id in number,
74 p_bkt_type in number,
75 p_bkt_start_date in date,
76 p_bkt_end_date in date) return date is
77
78
79 x_date date;
80 l_seq_num number;
81 l_calendar_code varchar2(20);
82 l_plan_type number;
83 begin
84
85 select plan_type into l_plan_type
86 from msc_plans where plan_id=p_plan_id;
87
88 if (l_plan_type = 6) then -- SNO
89 return trunc(p_bkt_end_date);
90 end if;
91
92 -- here, we follow the same logic used in mbp
93 -- first check whether profile MSC_BKT_REFERENCE_CALENDAR is set
94 -- if it set, l_calendar_code = MSC_BKT_REFERENCE_CALENDAR
95 -- otherwise, l_calendar_code = owning org's calendar
96
97
98 l_calendar_Code := fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR');
99 if (l_calendar_code is null) then
100 select mtp.calendar_code into l_calendar_code
101 from msc_trading_partners mtp, msc_plans mp
102 where mtp.sr_tp_id = mp.organization_id
103 and mtp.sr_instance_id = mp.sr_instance_id
104 and mp.plan_id =p_plan_id and mtp.PARTNER_TYPE =3;
105
106 end if;
107
108
109 -- dbms_output.put_line('calendar_code=' || l_calendar_code);
110
111 if (p_bkt_type =1 ) then
112 -- this is the day bucket
113 -- check whether this day is the working date
114 --- for daily bucket, it is the work day
115 --- engine sometime put s/d on day bucket even if
116 --- it is not working day
117 select mcd.calendar_date into x_date
118 from msc_calendar_dates mcd,msc_calendars mc
119 where mc.calendar_code = l_calendar_code
120 and mc.sr_instance_id = p_sr_instance_id
121 and mc.calendar_code=mcd.calendar_code
122 and mc.sr_instance_id=mcd.sr_instance_id
123
124 and mcd.calendar_date = p_bkt_start_date
125 and mcd.seq_num is not null;
126
127 return p_bkt_start_date;
128
129 elsif (p_bkt_type = 2) then
130
131 select max(mcd.calendar_date) into x_date
132 from msc_calendar_dates mcd, msc_calendars mc,msc_cal_week_start_dates mcwsd
133 where mc.calendar_code = l_calendar_code
134 and mc.sr_instance_id = p_sr_instance_id
135 and mc.calendar_code = mcd.calendar_code
136 and mc.sr_instance_id = mcd.sr_instance_id
137
138 and mcd.seq_num is not null
139 and mcd.calendar_code = mcwsd.calendar_code
140 and mcd.sr_instance_id = mcwsd.sr_instance_id
141 and mcd.exception_set_id =mcwsd.exception_set_id
142 and mcwsd.WEEK_START_DATE >= p_bkt_start_date
143 and mcwsd.WEEK_START_DATE <= p_bkt_end_date
144 and mcd.calendar_date >=mcwsd.WEEK_START_DATE
145 and mcd.calendar_date<mcwsd.next_date;
146
147 return x_date;
148 elsif (p_bkt_type=3) then
149
150 select max(mcd.calendar_date) into x_date
151 from msc_calendar_dates mcd, msc_calendars mc,msc_period_start_dates mpsd
152 where mc.calendar_code = l_calendar_code
153 and mc.sr_instance_id = p_sr_instance_id
154 and mc.calendar_code = mcd.calendar_code
155 and mc.sr_instance_id = mcd.sr_instance_id
156
157 and mcd.seq_num is not null
158 and mcd.calendar_code = mpsd.calendar_code
159 and mcd.sr_instance_id = mpsd.sr_instance_id
160 and mcd.exception_set_id =mpsd.exception_set_id
161 and mpsd.PERIOD_START_DATE >= p_bkt_start_date
162 and mpsd.PERIOD_START_DATE <= p_bkt_end_date
163 and mcd.calendar_date >=mpsd.PERIOD_START_DATE
164 and mcd.calendar_date<mpsd.next_date;
165
166 return x_date;
167 end if;
168 exception
169
170 when no_data_found then
171 -- dbms_output.put_line('no data:calendar_code=' || l_calendar_code);
172 if (p_bkt_type =1) then return p_bkt_start_date;
173 else return null;
174 end if;
175 end last_work_date;
176
177
178
179 function last_work_date(p_calendar_code in varchar2,
180 p_sr_instance_id in number,
181 p_bkt_type in number,
182 p_bkt_start_date date) return date is
183 x_date date;
184 l_seq_num number;
185 begin
186 if (p_bkt_type =1 ) then
187 -- this is the day bucket
188 -- check whether this day is the working date
189 select mcd.calendar_date into x_date
190 from msc_calendar_dates mcd,msc_calendars mc
191 where mc.calendar_code = p_calendar_code
192 and mc.sr_instance_id = p_sr_instance_id
193 and mc.calendar_code=mcd.calendar_code
194 and mc.sr_instance_id=mcd.sr_instance_id
195
196 and mcd.calendar_date = p_bkt_start_date
197 and mcd.seq_num is not null;
198
199 return x_date;
200
201 elsif (p_bkt_type = 2) then
202
203 select max(mcd.calendar_date) into x_date
204 from msc_calendar_dates mcd, msc_calendars mc,msc_cal_week_start_dates mcwsd
205 where mc.calendar_code = p_calendar_code
206 and mc.sr_instance_id = p_sr_instance_id
207 and mc.calendar_code = mcd.calendar_code
208 and mc.sr_instance_id = mcd.sr_instance_id
209
210 and mcd.seq_num is not null
211 and mcd.calendar_code = mcwsd.calendar_code
212 and mcd.sr_instance_id = mcwsd.sr_instance_id
213 and mcd.exception_set_id =mcwsd.exception_set_id
214 and mcwsd.WEEK_START_DATE = p_bkt_start_date
215 and mcd.calendar_date >=mcwsd.WEEK_START_DATE
216 and mcd.calendar_date<mcwsd.next_date;
217
218 return x_date;
219 elsif (p_bkt_type=3) then
220
221 select max(mcd.calendar_date) into x_date
222 from msc_calendar_dates mcd, msc_calendars mc,msc_period_start_dates mpsd
223 where mc.calendar_code = p_calendar_code
224 and mc.sr_instance_id = p_sr_instance_id
225 and mc.calendar_code = mcd.calendar_code
226 and mc.sr_instance_id = mcd.sr_instance_id
227
228 and mcd.seq_num is not null
229 and mcd.calendar_code = mpsd.calendar_code
230 and mcd.sr_instance_id = mpsd.sr_instance_id
231 and mcd.exception_set_id =mpsd.exception_set_id
232 and mpsd.PERIOD_START_DATE = p_bkt_start_date
233 and mcd.calendar_date >=mpsd.PERIOD_START_DATE
234 and mcd.calendar_date<mpsd.next_date;
235
236 return x_date;
237 end if;
238 exception
239
240 when no_data_found then
241 return null;
242 end last_work_date;
243
244
245 -----------------------------------------------------------------
246 --- this function is called in msc_demand_pkg and msc_supply_pkg
247 --- to put the supply/demands in them last working day of the first
248 --- bucket
249 ------------------------------------------------------------------
250
251 function last_work_date(p_plan_id in number,
252 p_date in date ) return date is
253 x_date date;
254 l_seq_num number;
255 l_bkt_start_date date;
256 l_bkt_end_date date;
257 l_bkt_type number;
258 l_sr_instance_id number;
259 begin
260
261 select bkt_start_date,bkt_end_date,bucket_type
262 into l_bkt_start_date,l_bkt_end_date,l_bkt_type
263 from msc_plan_buckets
264 where plan_id = p_plan_id
265 and p_date between bkt_start_date and bkt_end_date;
266
267
268 select sr_instance_id into l_sr_instance_id
269 from msc_plans where plan_id=p_plan_id;
270
271
272 return msc_hub_calendar.last_work_date(p_plan_id,l_sr_instance_id,
273 l_bkt_type,l_bkt_start_date,
274 l_bkt_end_date);
275 exception
276
277 when no_data_found then -- if the date is not in any bucket, return the plan start date
278 select curr_start_date into x_date
279 from msc_plans where plan_id = p_plan_id;
280
281 return x_date;
282 end last_work_date;
283
284
285 function ss_date(p_plan_id in number,p_bkt_start_date in date,p_bkt_end_date in date) return date is
286 x_date date;
287
288
289 begin
290 select max(period_start_date) into x_date
291 from msc_safety_Stocks
292 where period_start_date <=p_bkt_end_date
293 and plan_id=p_plan_id;
294
295 if (x_date is null) then
296 x_date :=p_bkt_start_date;
297 end if;
298
299 return x_date;
300
301
302
303
304 end ss_date;
305
306
307 function working_day_bkt_start_date(p_plan_id in number,
308 p_sr_instance_id in number,
309 p_bkt_type in number,
310 p_bkt_start_date in date,
311 p_bkt_end_date in date) return date is
312
313
314 x_date date;
315 l_seq_num number;
316 l_calendar_code varchar2(20);
317
318 begin
319 -- here, we follow the same logic used in mbp
320 -- first check whether profile MSC_BKT_REFERENCE_CALENDAR is set
321 -- if it set, l_calendar_code = MSC_BKT_REFERENCE_CALENDAR
322 -- otherwise, l_calendar_code = owning org's calendar
323
324
325 l_calendar_Code := fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR');
326 if (l_calendar_code is null) then
327 select mtp.calendar_code into l_calendar_code
328 from msc_trading_partners mtp, msc_plans mp
329 where mtp.sr_tp_id = mp.organization_id
330 and mtp.sr_instance_id = mp.sr_instance_id
331 and mp.plan_id =p_plan_id and mtp.PARTNER_TYPE =3;
332
333 end if;
334
335
336 -- dbms_output.put_line('calendar_code=' || l_calendar_code);
337
338 if (p_bkt_type =1 ) then
339
340 select max(mcd.calendar_date) into x_date
341 from msc_calendar_dates mcd,msc_calendars mc
342 where mc.calendar_code = l_calendar_code
343 and mc.sr_instance_id = p_sr_instance_id
344 and mc.calendar_code=mcd.calendar_code
345 and mc.sr_instance_id=mcd.sr_instance_id
346
347 and mcd.calendar_date <= p_bkt_start_date
348 and mcd.calendar_date >= p_bkt_start_date -7
349 and mcd.seq_num is not null;
350
351 return x_date;
352 else
353 return p_bkt_start_date;
354 end if;
355
356 exception
357
358 when no_data_found then
359 return null;
360
361 end working_day_bkt_start_date;
362
363 function get_item_org(p_plan_id in number,p_item_id in number,
364 p_sr_inst_id in number) return number is
365 x_org_id number;
366 begin
367 select min(organization_id) into x_org_id
368 from msc_system_items
369 where plan_id=p_plan_id
370 and inventory_item_id = p_item_id
371 and sr_instance_id = p_sr_inst_id;
372 return x_org_id;
373 end get_item_org;
374
375 end msc_hub_calendar;
[Home] [Help]