[Home] [Help]
PACKAGE BODY: APPS.MSC_DEMANTRA_PKG
Source
1 package body msc_demantra_pkg as
2 /* $Header: MSCHBDMB.pls 120.14.12010000.7 2008/09/03 14:40:01 wexia ship $ */
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_api_name varchar2(100) := 'msc_demand_f_pkg.populate_details';
8
9 l_owning_currency_code varchar2(20) := 'XXX';
10 begin
11 retcode := 0;
12 errbuf := '';
13
14 -- save owning organization's functional currency
15 begin
16 select o.currency_code
17 into l_owning_currency_code
18 from msc_trading_partners o,
19 msd_dem_app_instance_orgs daio,
20 msd_dp_ascp_scenarios_v p
21 where o.partner_type=3
22 and o.sr_instance_id=decode(sign(p.sr_instance_id), -1, o.sr_instance_id, p.sr_instance_id)
23 and o.organization_code=daio.organization_code
24 and daio.organization_id=fnd_profile.value('MSD_DEM_MASTER_ORG')
25 and p.scenario_id=p_plan_id;
26 exception
27 when others then
28 null;
29 end;
30
31
32 --dbms_output.put_line(l_api_name||'('||p_plan_id||', '||p_plan_run_id||')');
33 insert into msc_demantra_f (
34 ods_plan_id,
35 plan_id,
36 plan_run_id,
37 sr_instance_id,
38 organization_id,
39 customer_id,
40 customer_site_id,
41 region_id,
42 inventory_item_id,
43 demand_class,
44 owning_org_id,
45 owning_inst_id,
46 start_date,
47 aggr_type, category_set_id, sr_category_id,
48 consensus_fcst,
49 consensus_fcst_value,
50 consensus_fcst_value2,
51 consensus_fcst_cum,
52 priority,
53 created_by, creation_date,
54 last_update_date, last_updated_by, last_update_login,
55 program_id, program_login_id,
56 program_application_id, request_id)
57 select
58 to_number(-1) ods_plan_id,
59 t.scenario_id plan_id,
60 p_plan_run_id,
61 decode(t.organization_id, -1, -23453, t.sr_instance_id) sr_instance_id, --wei: sync sr_instance_id with organization_id
62 decode(t.organization_id, -1, -23453, t.organization_id) organization_id,
63 t.customer_id,
64 t.customer_site_id,
65 mpc.region_id,
66 t.inventory_item_id,
67 t.demand_class,
68
69 decode(t.organization_id,
70 -1, msc_hub_calendar.get_item_org(-1, t.inventory_item_id, t.sr_instance_id),
71 t.organization_id) owning_org_id,
72
73 t.sr_instance_id owning_inst_id,
74
75 t.start_date,
76 to_number(0) aggr_type,
77 to_number(-23453) category_set_id,
78 to_number(-23453) sr_category_id,
79 t.consensus_fcst,
80 t.consensus_fcst*t.price consensus_fcst_value,
81 t.consensus_fcst*t.price * decode(t.currency_code,
82 fnd_profile.value('MSC_HUB_CUR_CODE_RPT'), 1, nvl(mcc.conv_rate,0))
83 consensus_fcst_value2,
84 t.consensus_fcst_cum,
85 t.priority,
86 fnd_global.user_id, sysdate,
87 sysdate, fnd_global.user_id, fnd_global.login_id,
88 fnd_global.conc_program_id, fnd_global.conc_login_id,
89 fnd_global.prog_appl_id, fnd_global.conc_request_id
90 from (
91 select
92 k.scenario_id,
93 k.sr_instance_id,
94 k.organization_id,
95 k.customer_id,
96 k.customer_site_id,
97 k.zone_id,
98 k.inventory_item_id,
99 k.demand_class,
100 k.start_date,
101 nvl(f.currency_code, l_owning_currency_code) currency_code,
102 f.price,
103 f.consensus_fcst,
104 sum(nvl(f.consensus_fcst, 0)) over(partition by
105 k.scenario_id, k.sr_instance_id, k.organization_id,
106 k.customer_id, k.customer_site_id, k.zone_id,
107 k.inventory_item_id, k.demand_class
108 order by k.start_date) consensus_fcst_cum,
109 f.priority
110 from
111 (select distinct
112 k1.scenario_id,
113 k1.sr_instance_id,
114 k1.organization_id,
115 k1.customer_id,
116 k1.customer_site_id,
117 k1.zone_id,
118 k1.inventory_item_id,
119 k1.demand_class,
120 k2.start_date
121 from msd_dem_scn_entries_v k1,
122 (select distinct start_date
123 from msd_dem_scn_entries_v
124 where scenario_id=p_plan_id) k2
125 where k1.scenario_id=p_plan_id) k,
126 msd_dem_scn_entries_v f
127 where k.scenario_id = f.scenario_id(+)
128 and k.sr_instance_id = f.sr_instance_id(+)
129 and k.organization_id = f.organization_id(+)
130 and k.customer_id = f.customer_id(+)
131 and k.customer_site_id = f.customer_site_id(+)
132 and k.zone_id = f.zone_id(+)
133 and k.inventory_item_id = f.inventory_item_id(+)
134 and k.demand_class = f.demand_class(+)
135 and k.start_date = f.start_date(+)) t,
136 msc_currency_conv_mv mcc,
137 msc_phub_customers_mv mpc
138 where mcc.to_currency(+) = fnd_profile.value('MSC_HUB_CUR_CODE_RPT')
139 and mcc.from_currency(+) = t.currency_code
140 and mcc.calendar_date(+) = t.start_date
141 and mpc.customer_id = t.customer_id
142 and mpc.customer_site_id = t.customer_site_id
143 and mpc.region_id = decode(t.customer_id, -23453, t.zone_id, mpc.region_id);
144
145 commit;
146 --dbms_output.put_line('rowcount='||SQL%ROWCOUNT);
147
148 populate_summary(errbuf, retcode, p_plan_id, p_plan_run_id);
149
150 exception
151 when dup_val_on_index then
152 --dbms_output.put_line(to_char(SQLCODE) || ':' || SQLERRM);
153 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||
154 SQLCODE||' -ERROR- '||SQLERRM;
155 retcode := 2;
156
157 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
158 fnd_log.string(fnd_log.level_unexpected, l_api_name, to_char(SQLCODE)||':'||SQLERRM);
159 end if;
160
161
162 when others then
163 --dbms_output.put_line(to_char(SQLCODE) || ':' || SQLERRM);
164 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||
165 SQLCODE||' -ERROR- '||SQLERRM;
166 retcode := 2;
167
168 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
169 fnd_log.string(fnd_log.level_unexpected, l_api_name, to_char(SQLCODE)||':'||SQLERRM);
170 end if;
171
172 end populate_details;
173
174
175 procedure populate_summary(errbuf out nocopy varchar2, retcode out nocopy varchar2,
176 p_plan_id number, p_plan_run_id number)
177 is
178 l_category_set_id1 number := fnd_profile.value('MSC_HUB_CAT_SET_ID_1');
179 begin
180 -- level 1
181 insert into msc_demantra_f (
182 ods_plan_id, plan_id, plan_run_id,
183 sr_instance_id, organization_id, owning_org_id, owning_inst_id,
184 inventory_item_id,
185 customer_id, customer_site_id, region_id,
186 demand_class, start_date,
187 aggr_type, category_set_id, sr_category_id,
188 consensus_fcst,
189 consensus_fcst_value,
190 consensus_fcst_value2,
191 consensus_fcst_cum,
192 priority,
193 created_by, creation_date,
194 last_update_date, last_updated_by, last_update_login,
195 program_id, program_login_id,
196 program_application_id, request_id)
197 -- category (42, 43, 44)
198 select
199 f.ods_plan_id, f.plan_id, f.plan_run_id,
200 f.sr_instance_id, f.organization_id, f.owning_org_id, f.owning_inst_id,
201 to_number(-23453) inventory_item_id,
202 f.customer_id, f.customer_site_id, f.region_id,
203 f.demand_class, f.start_date,
204 to_number(42) aggr_type,
205 l_category_set_id1 category_set_id,
206 nvl(q.sr_category_id, -23453),
207 sum(f.consensus_fcst),
208 sum(f.consensus_fcst_value),
209 sum(f.consensus_fcst_value2),
210 sum(f.consensus_fcst_cum),
211 avg(f.priority),
212 fnd_global.user_id, sysdate,
213 sysdate, fnd_global.user_id, fnd_global.login_id,
214 fnd_global.conc_program_id, fnd_global.conc_login_id,
215 fnd_global.prog_appl_id, fnd_global.conc_request_id
216 from
217 msc_demantra_f f,
218 msc_phub_item_categories_mv q
219 where f.plan_id = p_plan_id and f.plan_run_id = p_plan_run_id
220 and f.aggr_type=0
221 and f.owning_inst_id=q.sr_instance_id(+)
222 and f.owning_org_id=q.organization_id(+)
223 and f.inventory_item_id=q.inventory_item_id(+)
224 and q.category_set_id(+)=l_category_set_id1
225 group by
226 f.ods_plan_id, f.plan_id, f.plan_run_id,
227 f.sr_instance_id, f.organization_id, f.owning_org_id, f.owning_inst_id,
228 f.customer_id, f.customer_site_id, f.region_id,
229 f.demand_class, f.start_date,
230 nvl(q.sr_category_id, -23453);
231
232 commit;
233
234 exception
235 when dup_val_on_index then
236 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_DUPLICATE_DATA')||
237 SQLCODE||' -ERROR- '||SQLERRM;
238 retcode := 2;
239 dbms_output.put_line(errbuf);
240 when others then
241 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||
242 SQLCODE||' -ERROR- '||SQLERRM;
243 retcode := 2;
244 dbms_output.put_line(errbuf);
245
246 end populate_summary;
247
248 procedure purge_details(errbuf out nocopy varchar2, retcode out nocopy varchar2,
249 p_plan_id number, p_plan_run_id number)
250 is
251 l_api_name varchar2(100) := 'msc_demand_f_pkg.purge_details';
252 begin
253 retcode := 0;
254 errbuf := '';
255
256 delete from msc_demantra_f
257 where plan_id = p_plan_id
258 and plan_run_id = nvl(p_plan_run_id, plan_run_id);
259
260 commit;
261
262 exception
263 when others then
264 errbuf := msc_phub_util.get_planning_hub_message('MSC_HUB_POPULATE_ERROR')||
265 SQLCODE||' -ERROR- '||SQLERRM;
266 retcode := 2;
267
268 if (fnd_log.level_unexpected >= fnd_log.g_current_runtime_level) then
269 fnd_log.string(fnd_log.level_unexpected, l_api_name, to_char(SQLCODE)||':'||SQLERRM);
270 end if;
271
272 end purge_details;
273
274 end msc_demantra_pkg;