1 PACKAGE BODY msc_phub_util AS
2 /* $Header: MSCHBUTB.pls 120.3.12010000.7 2008/09/03 14:44:22 wexia ship $ */
3 SYS_YES CONSTANT INTEGER := 1;
4 SYS_NO CONSTANT INTEGER := 2;
5
6 function get_conversion_rate(p_func_currency varchar2,p_sr_instance_id number, p_date date) return number is
7 l_currency_rate number;
8 l_reporting_currency varchar2(20) := get_reporting_currency_code;
9 begin
10 /*select CONV_RATE
11 into l_currency_rate
12 from MSC_CURRENCY_CONVERSIONS
13 where FROM_CURRENCY = p_func_currency
14 and TO_CURRENCY = l_reporting_currency
15 and SR_INSTANCE_ID = p_sr_instance_id
16 and CONV_DATE = p_date;*/
17 return 1;--l_currency_rate;
18 end get_conversion_rate;
19
20 function get_conversion_rate(p_sr_instance_id number, p_organization_id number, p_date date) return number is
21 l_func_currency varchar2(20);
22 l_rate number;
23 begin
24 /*select currency_code
25 into l_func_currency
26 from msc_trading_partners
27 where sr_instance_id = p_sr_instance_id
28 and organization_id = p_organization_id
29 and partner_type = 3;*/
30 l_rate := msc_phub_util.get_conversion_rate(l_func_currency, p_sr_instance_id, p_date);
31 return l_rate;
32 end get_conversion_rate;
33
34 function get_planning_hub_message(p_mesg_code varchar2) return varchar2 is
35 l_message varchar2(100);
36 begin
37 if p_mesg_code = 'MSC_HUB_UNASSIGNED' then
38 l_message := 'Unassigned';
39 else
40 FND_MESSAGE.SET_NAME('MSC', p_mesg_code);
41 l_message :=FND_MESSAGE.GET;
42 end if;
43 return l_message;
44 end get_planning_hub_message;
45
46 function get_reporting_currency_code return varchar2 is
47 begin
48 if g_rpt_curr_code is null then
49 g_rpt_curr_code := nvl(FND_PROFILE.VALUE('MSC_HUB_CUR_CODE_RPT'),'USD');
50 end if;
51
52 return g_rpt_curr_code;
53 end get_reporting_currency_code;
54
55 FUNCTION get_exception_group(p_exception_type_id in number) return varchar2 is
56 l_exception_group varchar2(300);
57 l_exception_group_id number;
58
59 CURSOR exception_group_meaning(p_exception_group_id NUMBER) IS
60 select meaning
61 from mfg_lookups
62 where lookup_type = 'MSC_EXCEPTION_GROUP'
63 and lookup_code = p_exception_group_id;
64 BEGIN
65 l_exception_group_id:= case
66 when p_exception_type_id in (11,5,12,105,30,48,84,29) then 1
67 when p_exception_type_id in (31,32,33,34,43,44,49,114) then 2
68 when p_exception_type_id in (2,3,20,115) then 3
69 when p_exception_type_id in (6,7,8,10,9,47,62,63,64,65,66,70,71) then 4
70 when p_exception_type_id in (13,14,113,23,24,25,26,27,35,41,42,15,16,69,52) then 5
71 when p_exception_type_id in (28,112,21,22,36,37,45,46,90,91)then 6
72 when p_exception_type_id in (40,38,39,50,51,61)then 7
73 when p_exception_type_id in (17,18,19)then 8
74 when p_exception_type_id in (53,54,55,56,57,58,67,59,60,72,77)then 11
75 when p_exception_type_id in (85,86)then 12
76 when p_exception_type_id in (92,93)then 13
77 when p_exception_type_id in (87,88)then 14
78 when p_exception_type_id in (150,151,152) then 15
79 when p_exception_type_id in (160,161,162) then 16
80 when p_exception_type_id in (170,171,172,173) then 17
81 when p_exception_type_id in (180,181) then 18
82 when p_exception_type_id in (190,191) then 19
83 when p_exception_type_id in (200,201)then 20
84 else 1
85 end;
86
87 open exception_group_meaning(l_exception_group_id);
88 fetch exception_group_meaning into l_exception_group;
89 close exception_group_meaning;
90
91 return l_exception_group;
92 END get_exception_group;
93
94 function get_list_price(p_plan_id number,p_inst_id number,p_org_id number, p_item_id number) return number is
95
96 l_list_price number;
97 begin
98 select nvl(msi.list_price,0) * (1-(nvl(msi.average_discount,0)/100))
99 into l_list_price
100 from msc_system_items msi
101 where
102 msi.plan_id =p_plan_id
103 and msi.sr_instance_id = p_inst_id
104 and msi.organization_id = p_org_id
105 and msi.inventory_item_id = p_item_id;
106 return l_list_price;
107 end get_list_price;
108
109 function is_plan_constrained (l_daily number,
110 l_weekly number,
111 l_monthly number,
112 l_dailym number,
113 l_weeklym number,
114 l_monthlym number) return number is
115 begin
116
117 if l_daily = 1 or l_weekly =1 or l_monthly =1 or l_dailym = 1 or l_weeklym =1 or l_monthlym =1 then
118 return SYS_YES;
119 else
120 return SYS_NO;
121 end if;
122
123 end is_plan_constrained;
124
125 FUNCTION is_plan_constrained(p_plan_id number) return number is
126 l_plan_constrained number;
127 begin
128 select count(1) into l_plan_constrained
129 from
130 msc_plans mp
131 where
132 mp.plan_id = p_plan_id
133 and( nvl(mp.daily_resource_constraints,0 ) = 1
134 or nvl(mp.weekly_resource_constraints,0) = 1
135 or nvl(mp.period_resource_constraints,0) = 1
136 or nvl(mp.daily_material_constraints,0 ) = 1
137 or nvl(mp.weekly_material_constraints,0) = 1
138 or nvl(mp.period_material_constraints,0) = 1);
139
140 if l_plan_constrained = 0 then
141 l_plan_constrained := 2;
142 end if;
143
144 return l_plan_constrained;
145 end is_plan_constrained;
146
147 FUNCTION get_plan_type(p_plan_id number) return number is
148 l_plan_type number;
149 begin
150 select CURR_PLAN_TYPE
151 into l_plan_type
152 from
153 msc_plans
154 where
155 plan_id = p_plan_id;
156 return l_plan_type;
157 end get_plan_type;
158
159 FUNCTION get_default_plan_id(p_scenario_id number, p_plan_type number, p_plan_run_name varchar2) return number is
160 cursor c_default_plan_id(p_scenario_id varchar2) is
161 select distinct p.plan_id
162 from msc_scenario_plans sp, msc_scenarios s,
163 (select distinct plan_id, plan_type from msc_plans
164 union all
165 select distinct scenario_id, to_number(10)
166 from msd_dp_ascp_scenarios_v
167 where demand_plan_id=5555555) p
168 where sp.scenario_id=s.scenario_id
169 and sp.plan_id=p.plan_id
170 and p.plan_type=decode(sign(nvl(p_plan_type, -1)), 1, p_plan_type, p.plan_type)
171 and s.scenario_id=p_scenario_id;
172
173 l_plan_id number := null;
174 begin
175 if (p_plan_run_name is not null) then
176 select plan_id
177 into l_plan_id
178 from msc_plan_runs
179 where plan_run_name=p_plan_run_name;
180 return l_plan_id;
181 end if;
182
183 if (p_scenario_id is not null) then
184 open c_default_plan_id(p_scenario_id);
185 fetch c_default_plan_id into l_plan_id;
186 close c_default_plan_id;
187 return l_plan_id;
188 end if;
189
190 return null;
191
192 exception
193 when others then
194 return null;
195
196 end get_default_plan_id;
197
198 FUNCTION get_user_name(p_user_id number) return varchar2 is
199 l_user_name varchar2(80);
200 begin
201 select distinct u.user_name
202 into l_user_name
203 from fnd_user u, fnd_user_resp_groups g
204 where u.user_id=g.user_id
205 and g.responsibility_application_id=724
206 and sysdate between u.start_date and nvl(u.end_date, sysdate)
207 and u.user_id=p_user_id;
208
209 return l_user_name;
210
211 exception
212 when others then
213 return msc_phub_util.get_planning_hub_message('MSC_HUB_UNASSIGNED');
214 end get_user_name;
215
216 procedure populate_item_categories(p_query_id number, p_plan_id number, p_plan_run_id number,
217 p_plan_type number, p_aggr_type number, p_category_set_id number) is
218 begin
219 if p_category_set_id is not null then
220 if p_plan_type = 10 then
221 insert into msc_hub_query(
222 query_id,
223 number1, -- plan_id
224 number2, -- plan_run_id
225 number3, -- owning_inst_id
226 number4, -- owning_org_id
227 number5, -- inventory_item_id
228 number6, -- aggr_type
229 number7, -- category_set_id
230 number8, -- sr_category_id
231 last_update_date, last_updated_by, creation_date, created_by, last_update_login
232 )
233 select distinct p_query_id, p_plan_id, p_plan_run_id,
234 f.owning_inst_id, f.owning_org_id, f.inventory_item_id,
235 p_aggr_type,
236 p_category_set_id,
237 nvl(ic.sr_category_id, -23453),
238 sysdate, 1, sysdate, 1, 1
239 from msc_demantra_f f, msc_phub_item_categories_mv ic
240 where f.plan_id=p_plan_id and f.plan_run_id=p_plan_run_id
241 and f.owning_inst_id = ic.sr_instance_id(+)
242 and f.owning_org_id = ic.organization_id(+)
243 and f.inventory_item_id = ic.inventory_item_id(+)
244 and ic.category_set_id(+) = p_category_set_id;
245
246 else
247 insert into msc_hub_query(
248 query_id,
249 number1, -- plan_id
250 number2, -- plan_run_id
251 number3, -- sr_instance_id
252 number4, -- organization_id
253 number5, -- inventory_item_id
254 number6, -- aggr_type
255 number7, -- category_set_id
256 number8, -- sr_category_id
257 last_update_date, last_updated_by, creation_date, created_by, last_update_login
258 )
259 select distinct p_query_id, p_plan_id, p_plan_run_id,
260 i.sr_instance_id, i.organization_id, i.inventory_item_id,
261 p_aggr_type,
262 p_category_set_id,
263 nvl(ic.sr_category_id, -23453),
264 sysdate, 1, sysdate, 1, 1
265 from msc_system_items i, msc_phub_item_categories_mv ic
266 where i.plan_id=p_plan_id
267 and i.sr_instance_id = ic.sr_instance_id(+)
268 and i.organization_id = ic.organization_id(+)
269 and i.inventory_item_id = ic.inventory_item_id(+)
270 and ic.category_set_id(+) = p_category_set_id
271 union all
272 select distinct p_query_id, p_plan_id, p_plan_run_id,
273 i.sr_instance_id, i.organization_id, to_number(-23453),
274 p_aggr_type,
275 p_category_set_id,
276 to_number(-23453),
277 sysdate, 1, sysdate, 1, 1
278 from msc_system_items i
279 where i.plan_id=p_plan_id;
280
281 end if;
282
283 --dbms_output.put_line('populate_item_categories '||p_aggr_type||':'||sql%rowcount);
284 commit;
285 end if;
286
287 exception
288 when others then
289 --dbms_output.put_line(sqlerrm);
290 null;
291
292 end populate_item_categories;
293
294 procedure populate_item_categories(p_query_id number, p_plan_id number, p_plan_run_id number,
295 p_plan_type number default null) is
296 begin
297 populate_item_categories(p_query_id, p_plan_id, p_plan_run_id, p_plan_type,
298 42, fnd_profile.value('MSC_HUB_CAT_SET_ID_1'));
299 end populate_item_categories;
300
301 procedure validate_icx_session(p_icx_cookie varchar2, p_user varchar2, p_pwd varchar2) is
302 l_retval varchar2(1);
303 SECURITY_CONTEXT_INVALID exception;
304
305 cursor c_user_info is
306 select furg.user_id, furg.responsibility_id, responsibility_application_id
307 from fnd_user_resp_groups furg,
308 fnd_user fu,
309 fnd_responsibility fr
310 where furg.user_id = fu.user_id
311 and furg.responsibility_id = fr.responsibility_id
312 and furg.responsibility_application_id = fr.application_id
313 and fu.user_name = 'APCC_ADMIN'
314 and fr.responsibility_key = 'APS_SCN_PLN';
315
316 l_user_id number;
317 l_resp_id number;
318 l_resp_app_id number;
319 procedure println (p_msg varchar2) is
320 begin
321 null;
322 --dbms_output.put_line(p_msg);
323 end println;
324 begin
325 println('icx cookie value is cookie user pwd - '|| p_icx_cookie ||' - '||p_user||' - '||p_pwd); commit;
326
327 l_retval := fnd_web_sec.validate_login(p_user, p_pwd);
328 println('icx cookie value is valid_login - '||l_retval); commit;
329
330 if p_icx_cookie <> '-1' then
331 app_session.validate_icx_session(p_icx_cookie);
332 elsif (l_retval ='Y' and p_user = 'APCC_ADMIN') or (p_user = 'Administrator') then
333 open c_user_info;
334 fetch c_user_info into l_user_id, l_resp_id, l_resp_app_id;
335 close c_user_info;
336 if (l_user_id is null or l_resp_id is null or l_resp_app_id is null) then
337 raise SECURITY_CONTEXT_INVALID;
338 end if;
339 fnd_global.apps_initialize ( user_id => l_user_id, resp_id => l_resp_id, resp_appl_id => l_resp_app_id);
340 else
341 raise SECURITY_CONTEXT_INVALID;
342 end if;
343 end validate_icx_session;
344
345 END msc_phub_util;
[Home] [Help]