1 package body msc_hp_util as
2 /* $Header: MSCPCUTB.pls 120.0.12020000.3 2012/11/02 17:38:37 wexia noship $ */
3 g_page_size number := 10;
4 g_max_fetch_size number := 100;
5 g_fetch_size number := g_max_fetch_size;
6
7 function get_calendar_code(p_plan_id number) return varchar2
8 is
9 l_calendar_code varchar2(20);
10 begin
11 l_calendar_code := fnd_profile.value('MSC_BKT_REFERENCE_CALENDAR'); -- l_calendar_code
12 if (l_calendar_code is null) then
13 select mtp.calendar_code
14 into l_calendar_code
15 from msc_trading_partners mtp, msc_plans mp
16 where mtp.sr_tp_id=mp.organization_id
17 and mtp.sr_instance_id=mp.sr_instance_id
18 and mp.plan_id=p_plan_id
19 and mtp.partner_type=3;
20 end if;
21 return l_calendar_code;
22 end get_calendar_code;
23
24 procedure prepare_col_dtls(p_plan_id number, p_refresh number)
25 is
26 l_calendar_code varchar2(20);
27 l_n number;
28 begin
29 msc_phub_util.log('msc_hp_util.prepare_col_dtls('||p_plan_id||')');
30 l_calendar_code := get_calendar_code(p_plan_id);
31 if (l_calendar_code is null) then
32 return;
33 end if;
34
35 if (p_refresh = 1) then
36 delete from msc_hp_col_dtls where plan_id=p_plan_id;
37 msc_phub_util.log('delete from msc_hp_col_dtls: '||sql%rowcount);
38 commit;
39 else
40 select count(*) into l_n
41 from msc_hp_col_dtls
42 where plan_id=p_plan_id;
43
44 if (l_n > 0) then
45 return;
46 end if;
47 end if;
48
49 insert into msc_hp_col_dtls(
50 plan_id,
51 bkt_start_date,
52 bkt_end_date,
53 seq_num,
54 bucket_type,
55 bucket_index,
56 week_start_date,
57 week_end_date,
58 week_last_work_date,
59 period_start_date,
60 period_end_date,
61 period_last_work_date,
62 created_by, creation_date,
63 last_update_date, last_updated_by, last_update_login)
64 with t as (
65 select
66 b.bkt_start_date,
67 b.bkt_end_date,
68 b.seq_num,
69 b.bucket_type,
70 b.bucket_index,
71 w.week_start_date week_start_date,
72 w.week_end_date,
73 w.week_last_work_date,
74 p.period_start_date period_start_date,
75 p.period_end_date,
76 p.period_last_work_date
77 from
78 (select
79 b.bkt_start_date,
80 b.bkt_end_date,
81 md.seq_num,
82 b.bucket_type,
83 b.bucket_index
84 from
85 msc_plan_buckets b,
86 msc_calendar_dates md
87 where b.plan_id=p_plan_id
88 and b.curr_flag=1
89 and md.calendar_code=l_calendar_code
90 and md.exception_set_id=-1
91 and b.bkt_start_date=md.calendar_date
92 ) b,
93 (select
94 mw.week_start_date,
95 mw.next_date-1 week_end_date,
96 max(decode(md.seq_num, null, null, md.calendar_date)) week_last_work_date
97 from
98 msc_plan_buckets b,
99 msc_calendar_dates md,
100 msc_cal_week_start_dates mw
101 where b.plan_id=p_plan_id
102 and b.curr_flag=1
103 and md.calendar_date between b.bkt_start_date and b.bkt_end_date
104 and md.calendar_code=l_calendar_code
105 and md.exception_set_id=-1
106 and md.calendar_code=mw.calendar_code
107 and md.sr_instance_id=mw.sr_instance_id
108 and md.exception_set_id=mw.exception_set_id
109 and md.calendar_date between mw.week_start_date and mw.next_date-1
110 --and md.seq_num is not null
111 group by
112 mw.week_start_date,
113 mw.next_date
114 ) w,
115 (select
116 mp.period_start_date,
117 mp.next_date-1 period_end_date,
118 max(decode(md.seq_num, null, null, md.calendar_date)) period_last_work_date
119 from
120 msc_plan_buckets b,
121 msc_calendar_dates md,
122 msc_period_start_dates mp
123 where b.plan_id=p_plan_id
124 and b.curr_flag=1
125 and md.calendar_date between b.bkt_start_date and b.bkt_end_date
126 and md.calendar_code=l_calendar_code
127 and md.exception_set_id=-1
128 and md.calendar_code=mp.calendar_code
129 and md.sr_instance_id=mp.sr_instance_id
130 and md.exception_set_id=mp.exception_set_id
131 and md.calendar_date between mp.period_start_date and mp.next_date-1
132 --and md.seq_num is not null
133 group by
134 mp.period_start_date,
135 mp.next_date
136 ) p
137 where b.bkt_start_date between w.week_start_date and w.week_end_date
138 and w.week_start_date between p.period_start_date and p.period_end_date
139 ),
140 m as (
141 select bkt_start_date plan_start_date
142 from msc_plan_buckets
143 where plan_id=p_plan_id
144 and curr_flag=1
145 and bucket_index=1
146 )
147 select
148 p_plan_id,
149 t.bkt_start_date,
150 t.bkt_end_date,
151 t.seq_num,
152 t.bucket_type,
153 t.bucket_index,
154 decode(tw.week_index, 1, m.plan_start_date, t.week_start_date) week_start_date,
155 t.week_end_date,
156 greatest(decode(tw.week_index, 1, m.plan_start_date, t.week_start_date), t.week_last_work_date) week_last_work_date,
157 decode(tp.period_index, 1, m.plan_start_date, t.period_start_date) period_start_date,
158 t.period_end_date,
159 greatest(decode(tp.period_index, 1, m.plan_start_date, t.period_start_date), t.period_last_work_date) period_last_work_date,
160 fnd_global.user_id, sysdate,
161 sysdate, fnd_global.user_id, fnd_global.login_id
162 from t, m,
163 (select rownum week_index, week_start_date from (select distinct week_start_date from t order by 1)) tw,
164 (select rownum period_index, period_start_date from (select distinct period_start_date from t order by 1)) tp
165 where t.week_start_date=tw.week_start_date
166 and t.period_start_date=tp.period_start_date
167 union all
168 select
169 p_plan_id,
170 m.plan_start_date - 1 bkt_start_date,
171 m.plan_start_date - 1 bkt_end_date,
172 0 seq_num,
173 0 bucket_type,
174 0 bucket_index,
175 m.plan_start_date - 1 week_start_date,
176 m.plan_start_date - 1 week_end_date,
177 m.plan_start_date - 1 week_last_work_date,
178 m.plan_start_date - 1 period_start_date,
179 m.plan_start_date - 1 period_end_date,
180 m.plan_start_date - 1 period_last_work_date,
181 fnd_global.user_id, sysdate,
182 sysdate, fnd_global.user_id, fnd_global.login_id
183 from m
184 order by bucket_index;
185 msc_phub_util.log('insert into msc_hp_col_dtls: '||sql%rowcount);
186 commit;
187
188 end prepare_col_dtls;
189
190 procedure prepare_filters(p_query_id number, p_filter_type number, p_filters varchar2)
191 is
192 i number;
193 s varchar2(5000) := p_filters||',';
194 t varchar2(5000);
195 begin
196 msc_phub_util.log('msc_hp_util.prepare_filters('||p_query_id||', '||p_filter_type||')');
197 i := instr(s, ',');
198 while nvl(i, 0) > 0 loop
199 t := trim(substr(s, 1, i - 1));
200 if (length(t) > 0) then
201 if (p_filter_type in (ft_organization, ft_category, ft_department, ft_resource)) then
202 insert into msc_form_query(query_id, number1, char1,
203 created_by, creation_date, last_update_date, last_updated_by, last_update_login)
204 values (p_query_id, p_filter_type, trim(substr(s, 1, i - 1)),
205 fnd_global.user_id, sysdate, sysdate, fnd_global.user_id, fnd_global.login_id);
206 end if;
207
208 if (p_filter_type in (ft_exception, ft_item)) then
209 insert into msc_form_query(query_id, number1, number2,
210 created_by, creation_date, last_update_date, last_updated_by, last_update_login)
211 values (p_query_id, p_filter_type, trim(substr(s, 1, i - 1)),
212 fnd_global.user_id, sysdate, sysdate, fnd_global.user_id, fnd_global.login_id);
213 end if;
214 end if;
215 s := substr(s, i + 1);
216 i := instr(s, ',');
217 end loop;
218 commit;
219 end prepare_filters;
220
221 function create_query_id return number
222 is
223 l_query_id number;
224 begin
225 select msc_hp_query_s.nextval into l_query_id from dual;
226 return l_query_id;
227 end create_query_id;
228
229 function get_plan_id(p_query_id number) return number
230 is
231 l_plan_id number;
232 begin
233 select plan_id into l_plan_id
234 from msc_hp_row_dtls
235 where query_id=p_query_id and rownum=1;
236
237 return l_plan_id;
238 exception
239 when no_data_found then
240 return null;
241 end get_plan_id;
242
243 procedure sync_ui_data(p_plan_id number, p_mode number)
244 is
245 begin
246 msc_phub_util.log('msc_hp_util.sync_ui_data('||p_plan_id||','||p_mode||')');
247
248 delete from msc_matl_plan_data where query_id in (select query_id from msc_hp_row_dtls where plan_id=p_plan_id);
249 msc_phub_util.log('delete from msc_matl_plan_data: '||sql%rowcount);
250 commit;
251
252 delete from msc_res_plan_data where query_id in (select query_id from msc_hp_row_dtls where plan_id=p_plan_id);
253 msc_phub_util.log('delete from msc_res_plan_data: '||sql%rowcount);
254 commit;
255
256 delete from msc_hp_updates where query_id in (select query_id from msc_hp_row_dtls where plan_id=p_plan_id);
257 msc_phub_util.log('delete from msc_hp_updates: '||sql%rowcount);
258 commit;
259
260 delete from msc_res_plan_updates where query_id in (select query_id from msc_hp_row_dtls where plan_id=p_plan_id);
261 msc_phub_util.log('delete from msc_res_plan_updates: '||sql%rowcount);
262 commit;
263
264 delete from msc_hp_row_dtls where plan_id=p_plan_id;
265 msc_phub_util.log('delete from msc_hp_row_dtls: '||sql%rowcount);
266 commit;
267
268 delete from msc_hp_col_dtls where plan_id=p_plan_id;
269 msc_phub_util.log('delete from msc_hp_col_dtls: '||sql%rowcount);
270 commit;
271
272 if (p_mode = sync_launch) then
273 prepare_col_dtls(p_plan_id, 1);
274 end if;
275
276 end sync_ui_data;
277 end msc_hp_util;
[Home] [Help]