[Home] [Help]
PACKAGE BODY: APPS.MSC_PHUB_BUDGET_PKG
Source
1 package body msc_phub_budget_pkg as
2 /* $Header: MSCHBBDB.pls 120.8 2011/02/10 19:06:05 wexia noship $ */
3
4 procedure populate_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
5 p_plan_id number, p_plan_run_id number)
6 is
7 l_owning_currency_code varchar2(20) := msc_phub_util.get_owning_currency_code(p_plan_run_id);
8 l_enforce_budget_constraints number;
9 l_budget_id number;
10 l_budget_value number;
11
12 l_plan_start_date date;
13 l_plan_cutoff_date date;
14 l_plan_type number;
15 l_sr_instance_id number;
16 l_organization_id number;
17
18 begin
19 msc_phub_util.log('msc_phub_budget_pkg.populate_details:');
20 retcode := 0;
21 errbuf := null;
22
23 select enforce_budget_constraints, budget_id, budget_value
24 into l_enforce_budget_constraints, l_budget_id, l_budget_value
25 from msc_plans
26 where plan_id=p_plan_id;
27
28 select plan_type, sr_instance_id, organization_id, plan_start_date, plan_cutoff_date
29 into l_plan_type, l_sr_instance_id, l_organization_id, l_plan_start_date, l_plan_cutoff_date
30 from msc_plan_runs
31 where plan_id=p_plan_id
32 and plan_run_id=p_plan_run_id;
33
34 msc_phub_util.log('msc_phub_budget_pkg.populate_details: '||
35 'l_enforce_budget_constraints='||l_enforce_budget_constraints||
36 ',l_budget_id='||l_budget_id||
37 ',l_budget_value='||l_budget_value);
38
39 if (nvl(l_enforce_budget_constraints,2) <> 1) then
40 msc_phub_util.log('msc_phub_budget_pkg.populate_details: l_enforce_budget_constraints<>1');
41 return;
42 end if;
43
44 if (l_budget_id is null and l_budget_value is null) then
45 msc_phub_util.log('msc_phub_budget_pkg.populate_details: l_budget_id is null and budget_value is null');
46 return;
47 end if;
48
49 if (l_budget_id > 0) then
50 insert into msc_budgets_f (
51 plan_id,
52 plan_run_id,
53 budget_level,
54 sr_instance_id,
55 organization_id,
56 category_set_id,
57 category_instance_id,
58 sr_category_id,
59 budget_value,
60 budget_value2,
61 created_by,
62 creation_date,
63 last_updated_by,
64 last_update_date,
65 last_update_login,
66 program_id,
67 program_login_id,
68 program_application_id,
69 request_id)
70 select
71 p_plan_id,
72 p_plan_run_id,
73 t.budget_level,
74 t.sr_instance_id,
75 t.organization_id,
76 t.category_set_id,
77 t.category_instance_id,
78 t.sr_category_id,
79 t.budget_value,
80 t.budget_value * decode(t.currency_code,
81 fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),
82 1, nvl(mcc.conv_rate,0)) budget_value2,
83 fnd_global.user_id,
84 sysdate,
85 fnd_global.user_id,
86 sysdate,
87 fnd_global.login_id,
88 fnd_global.conc_program_id,
89 fnd_global.conc_login_id,
90 fnd_global.prog_appl_id,
91 fnd_global.conc_request_id
92 from
93 (select
94 -- budget_level: 0:plan 1:org 2:cat 3:org-cat
95 decode(sign(b.organization_id), -1,
96 decode(sign(b.sr_category_id), -1, 0, 2),
97 decode(sign(b.sr_category_id), -1, 1, 3)) budget_level,
98 decode(b.sr_instance_id, -1, -23453, b.sr_instance_id) sr_instance_id,
99 decode(b.organization_id, -1, -23453, b.organization_id) organization_id,
100 nvl(mtp.currency_code, l_owning_currency_code) currency_code,
101 fnd_profile.value('MSR_BUDGET_CATEGORY_SET') category_set_id,
102 b.category_instance_id,
103 b.sr_category_id,
104 b.budget_value
105 from
106 msc_inventory_budget_values b,
107 msc_trading_partners mtp
108 where budget_id=l_budget_id
109 and b.sr_instance_id=mtp.sr_instance_id(+)
110 and b.organization_id=mtp.sr_tp_id(+)
111 and mtp.partner_type(+)=3
112 ) t,
113 msc_currency_conv_mv mcc
114 where mcc.from_currency(+)=t.currency_code
115 and mcc.calendar_date(+)=l_plan_start_date
116 and mcc.to_currency(+)=fnd_profile.value('MSC_HUB_CUR_CODE_RPT');
117 else
118 insert into msc_budgets_f (
119 plan_id,
120 plan_run_id,
121 budget_level,
122 sr_instance_id,
123 organization_id,
124 category_set_id,
125 category_instance_id,
126 sr_category_id,
127 budget_value,
128 budget_value2,
129 created_by,
130 creation_date,
131 last_updated_by,
132 last_update_date,
133 last_update_login,
134 program_id,
135 program_login_id,
136 program_application_id,
137 request_id)
138 select
139 p_plan_id,
140 p_plan_run_id,
141 0,
142 to_number(-23453),
143 to_number(-23453),
144 null,
145 -1,
146 -1,
147 budget_value,
148 budget_value * decode(currency_code,
149 fnd_profile.value('MSC_HUB_CUR_CODE_RPT'),
150 1, nvl(mcc.conv_rate,0)) budget_value2,
151 fnd_global.user_id,
152 sysdate,
153 fnd_global.user_id,
154 sysdate,
155 fnd_global.login_id,
156 fnd_global.conc_program_id,
157 fnd_global.conc_login_id,
158 fnd_global.prog_appl_id,
159 fnd_global.conc_request_id
160 from
161 (select
162 l_budget_value budget_value,
163 l_owning_currency_code currency_code
164 from dual), -- inline table for outer join
165 msc_currency_conv_mv mcc
166 where mcc.from_currency(+)=currency_code
167 and mcc.calendar_date(+)=l_plan_start_date
168 and mcc.to_currency(+)=fnd_profile.value('MSC_HUB_CUR_CODE_RPT');
169 end if;
170 msc_phub_util.log('msc_phub_budget_pkg.populate_details: msc_budgets_f, rowcount='||sql%rowcount);
171 commit;
172
173 exception
174 when others then
175 msc_phub_util.log('msc_phub_budget_pkg.populate_details.exception: '||sqlerrm);
176 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||':'||sqlerrm;
177 retcode := 2;
178 end populate_details;
179
180 procedure export_budgets_f (
181 errbuf out nocopy varchar2, retcode out nocopy varchar2,
182 p_st_transaction_id number, p_plan_run_id number,
183 p_dblink varchar2, p_source_version varchar2)
184 is
185 l_sql varchar2(5000);
186 l_suffix varchar2(32) := msc_phub_util.suffix(p_dblink);
187 l_apps_schema varchar2(30) := msc_phub_util.apps_schema;
188 begin
189 msc_phub_util.log('msc_phub_budget_pkg.export_budgets_f');
190 retcode := 0;
191 errbuf := null;
192
193 delete from msc_st_budgets_f where st_transaction_id=p_st_transaction_id;
194 commit;
195
196 l_sql :=
197 ' insert into msc_st_budgets_f('||
198 ' st_transaction_id,'||
199 ' error_code,'||
200 ' sr_instance_id,'||
201 ' organization_id,'||
202 ' category_set_id,'||
203 ' category_instance_id,'||
204 ' sr_category_id,'||
205 ' organization_code,'||
206 ' category_instance_code,'||
207 ' category_name,'||
208 ' budget_level,'||
209 ' budget_value,'||
210 ' budget_value2,'||
211 ' created_by, creation_date,'||
212 ' last_updated_by, last_update_date, last_update_login'||
213 ' )'||
214 ' select'||
215 ' :p_st_transaction_id,'||
216 ' 0,'||
217 ' f.sr_instance_id,'||
218 ' f.organization_id,'||
219 ' f.category_set_id,'||
220 ' f.category_instance_id,'||
221 ' f.sr_category_id,'||
222 ' mtp.organization_code,'||
223 ' mai.instance_code category_instance_code,'||
224 ' c.category_name,'||
225 ' f.budget_level,'||
226 ' f.budget_value,'||
227 ' f.budget_value2,'||
228 ' fnd_global.user_id, sysdate,'||
229 ' fnd_global.user_id, sysdate, fnd_global.login_id'||
230 ' from'||
231 ' '||l_apps_schema||'.msc_budgets_f'||l_suffix||' f,'||
232 ' '||l_apps_schema||'.msc_trading_partners'||l_suffix||' mtp,'||
233 ' '||l_apps_schema||'.msc_phub_categories_mv'||l_suffix||' c,'||
234 ' '||l_apps_schema||'.msc_apps_instances'||l_suffix||' mai'||
235 ' where f.plan_run_id=:p_plan_run_id'||
236 ' and mtp.partner_type(+)=3'||
237 ' and mtp.sr_instance_id(+)=f.sr_instance_id'||
238 ' and mtp.sr_tp_id(+)=f.organization_id'||
239 ' and c.sr_instance_id(+)=f.category_instance_id'||
240 ' and c.sr_category_id(+)=f.sr_category_id'||
241 ' and c.category_set_id(+)=f.category_set_id'||
242 ' and mai.instance_id(+)=f.category_instance_id';
243
244 execute immediate l_sql using p_st_transaction_id, p_plan_run_id;
245 commit;
246 msc_phub_util.log('msc_phub_budget_pkg.export_budgets_f: complete, retcode='||retcode);
247
248 exception
249 when others then
250 retcode := 2;
251 errbuf := 'msc_phub_budget_pkg.export_budgets_f: '||sqlerrm;
252 msc_phub_util.log(errbuf);
253 end export_budgets_f;
254
255 procedure import_budgets_f (
256 errbuf out nocopy varchar2, retcode out nocopy varchar2,
257 p_st_transaction_id number, p_plan_id number, p_plan_run_id number,
258 p_plan_type number, p_plan_start_date date, p_plan_cutoff_date date,
259 p_upload_mode number, p_overwrite_after_date date, p_def_instance_code varchar2)
260 is
261 l_staging_table varchar2(30) := 'msc_st_budgets_f';
262 l_fact_table varchar2(30) := 'msc_budgets_f';
263 l_result number := 0;
264 begin
265 msc_phub_util.log('msc_phub_budget_pkg.import_budgets_f');
266 retcode := 0;
267 errbuf := null;
268
269 l_result := l_result + msc_phub_util.prepare_fact_dates(
270 l_fact_table, 1, null, p_plan_id, p_plan_run_id,
271 p_upload_mode, p_overwrite_after_date);
272
273 l_result := l_result + msc_phub_util.decode_organization_key(
274 l_staging_table, p_st_transaction_id, p_def_instance_code,
275 'sr_instance_id', 'organization_id', 'organization_code');
276
277 l_result := l_result + msc_phub_util.decode_category_key(
278 l_staging_table, p_st_transaction_id);
279
280 if (p_upload_mode = msc_phub_util.upload_replace) then
281 msc_phub_util.log('msc_phub_budget_pkg.import_budgets_f: purge msc_budgets_f');
282
283 delete from msc_budgets_f
284 where plan_id=p_plan_id and plan_run_id=p_plan_run_id;
285 commit;
286 end if;
287
288 msc_phub_util.log('msc_phub_budget_pkg.import_budgets_f: insert into msc_budgets_f');
289 insert into msc_budgets_f (
290 plan_id,
291 plan_run_id,
292 sr_instance_id,
293 organization_id,
294 category_set_id,
295 category_instance_id,
296 sr_category_id,
297 budget_level,
298 budget_value,
299 budget_value2,
300 created_by, creation_date,
301 last_updated_by, last_update_date, last_update_login
302 )
303 select
304 p_plan_id,
305 p_plan_run_id,
306 nvl(sr_instance_id, -1),
307 nvl(organization_id, -1),
308 nvl(category_set_id, -23453),
309 nvl(category_instance_id, -1),
310 nvl(sr_category_id, -1),
311 budget_level,
312 budget_value,
313 budget_value2,
314 fnd_global.user_id, sysdate,
315 fnd_global.user_id, sysdate, fnd_global.login_id
316 from msc_st_budgets_f
317 where st_transaction_id=p_st_transaction_id and error_code=0;
318
319 msc_phub_util.log('msc_phub_budget_pkg.import_budgets_f: inserted='||sql%rowcount);
320 commit;
321
322 if (l_result > 0) then
323 retcode := -1;
324 end if;
325
326 msc_phub_util.log('msc_phub_budget_pkg.import_budgets_f: complete, retcode='||retcode);
327
328 exception
329 when others then
330 retcode := 2;
331 errbuf := 'msc_phub_budget_pkg.import_budgets_f: '||sqlerrm;
332 msc_phub_util.log(errbuf);
333 end import_budgets_f;
334
335 end msc_phub_budget_pkg;