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