[Home] [Help]
PACKAGE BODY: APPS.MSC_SUPPLIER_PWB
Source
1 package body msc_supplier_pwb as
2 /* $Header: MSCFNSCB.pls 120.0 2005/05/25 19:09:21 appldev noship $ */
3
4
5 -- 1 valid <= 0 invalid input
6 function valid_supplier_type(p_unique_id in varchar2,
7 p_user_id in number,
8 p_supplier_id in number,
9 p_supplier_site_id in number)
10 return number is
11
12 cursor c_user_resp (p_user number, p_resp number, p_app number) is
13 select count(*)
14 from fnd_user_resp_groups
15 where user_id = p_user
16 and responsibility_id= p_resp
17 and responsibility_application_id = p_app ;
18
19 l_supplier_id number;
20 l_count number := 0;
21 l_retval boolean := false;
22 begin
23 --check whether this user has supplier resp
24 open c_user_resp(fnd_global.user_id, 23815, 724);
25 fetch c_user_resp into l_count;
26 close c_user_resp ;
27
28 if ( l_count = 0 ) then
29 return -3;
30 end if;
31
32 --check whether supplier org mode
33 l_retval := modelled_as_org;
34 if (not l_retval) then
35 --check whether supplier mode
36 l_supplier_id := modelled_as_supplier;
37 if ( l_supplier_id is null) then
38 return -1; --invalid supplier
39 else
40 --check supplier has any plans
41 l_retval :=check_supplier_plans(l_supplier_id);
42 if (not l_retval) then
43 return -2; --supplier does not have plans
44 end if;
45 end if;
46 end if;
47 return 1;
48 end valid_supplier_type ;
49
50 procedure enable_fn_sec is
51 l_item_grp_name constant varchar2(30) := 'MSC_ITEMS_FN';
52 l_res_grp_name constant varchar2(30) := 'MSC_RES_FN';
53 l_sup_grp_name constant varchar2(30) := 'MSC_SUP_FN';
54 l_schain_grp_name constant varchar2(30) := 'MSC_SCHAIN_FN';
55 l_plan_grp_name constant varchar2(30) := 'MSC_PLAN_FN';
56 l_gc_grp_name constant varchar2(30) := 'MSC_GC_FN';
57 l_kpi_grp_name constant varchar2(30) := 'MSC_KPI_FN';
58 l_peg_grp_name constant varchar2(30) := 'MSC_PEG_FN';
59 l_exepln_grp_name constant varchar2(30) := 'MSC_EXEPLN_FN';
60 l_replan_grp_name constant varchar2(30) := 'MSCREPLAN';
61 l_cp_grp_name constant varchar2(30) := 'MSC_CP_FN';
62 l_opt_grp_name constant varchar2(30) := 'MSC_OPT_FN';
63 l_undo_grp_name constant varchar2(30) := 'MSC_UNDO_FN';
64 l_bom_grp_name constant varchar2(30) := 'MSC_BOM_FN';
65 l_invlv_grp_name constant varchar2(30) := 'MSCINVLV';
66 l_res_batch_name constant varchar2(30) := 'MSC_RES_BATCH_FN';
67 /*---------------------Enhancement for LA by :Satyagi---------*/
68 l_liability_name constant varchar2(30) := 'MSC_LIABILITY_FN';
69 /*----------------------Enhancement for LA by :Satyagi---------*/
70
71 begin
72
73 g_item_grp := fnd_function.test(l_item_grp_name);
74 if ( g_item_grp ) then
75 g_item_fn := 1;
76 --utham 2777366 always disable for cp user..wrong
77 g_psub_fn := 1;
78 end if;
79
80 g_res_grp := fnd_function.test(l_res_grp_name);
81 if ( g_res_grp ) then
82 g_res_fn := 1;
83 end if;
84
85 g_opt_grp := fnd_function.test(l_opt_grp_name);
86 if ( g_opt_grp ) then
87 g_option_fn := 1;
88 g_pref_fn := 1;
89 end if;
90
91 g_bom_grp := fnd_function.test(l_bom_grp_name);
92 if ( g_bom_grp ) then
93 g_oper_fn := 1;
94 g_comp_fn := 1;
95 g_used_fn := 1;
96 g_peff_fn := 1;
97 g_coprod_fn := 1;
98 end if;
99 g_cp_grp := fnd_function.test(l_cp_grp_name);
100 if ( g_cp_grp ) then
101 g_23b_fn := 1;
102 end if;
103 g_undo_grp := fnd_function.test(l_undo_grp_name);
104 if ( g_undo_grp ) then
105 g_undo_fn := 1;
106 end if;
107 g_sup_grp := fnd_function.test(l_sup_grp_name);
108 if ( g_sup_grp ) then
109 g_supp_fn := 1;
110 end if;
111 g_schain_grp := fnd_function.test(l_schain_grp_name);
112 if ( g_schain_grp ) then
113 g_bill_fn := 1;
114 g_sour_fn := 1;
115 g_dest_fn := 1;
116 end if;
117 g_plan_grp := fnd_function.test(l_plan_grp_name);
118 if ( g_plan_grp ) then
119 g_excp_fn := 1;
120 g_hz_fn := 1;
121 g_vert_fn := 1;
122 g_28_fn := 1;
123 g_pcr_fn := 1;
124 --utham 2777366 always disable for cp user ..wrong
125 g_ss_fn := 1;
126 g_21a_fn := 1;
127 end if;
128 g_gc_grp := fnd_function.test(l_gc_grp_name);
129 if ( g_gc_grp ) then
130 g_gc_fn := 1;
131 end if;
132 g_kpi_grp := fnd_function.test(l_kpi_grp_name);
133 if ( g_kpi_grp ) then
134 g_kpi_fn := 1;
135 end if;
136 g_peg_grp := fnd_function.test(l_peg_grp_name);
137 if ( g_peg_grp ) then
138 g_peg_fn := 1;
139 end if;
140 g_exepln_grp := fnd_function.test(l_exepln_grp_name);
141 if ( g_exepln_grp ) then
142 g_23a_fn := 1;
143 g_21b_fn := 1;
144 end if;
145 g_replan_grp := fnd_function.test(l_replan_grp_name);
146 if ( g_replan_grp ) then
147 g_replan_fn := 1;
148 end if;
149 g_invlv_grp := fnd_function.test(l_invlv_grp_name);
150 if ( g_invlv_grp ) then
151 g_invlv_fn := 1;
152 end if;
153 g_res_batch_grp := fnd_function.test(l_res_batch_name);
154 if ( g_res_batch_grp ) then
155 g_res_batch_fn := 1;
156 end if;
157 /*---------------------Enhancement for LA by :Satyagi---------*/
158 g_liability_grp := fnd_function.test(l_liability_name);
159 if ( g_liability_grp ) then
160 g_liability_fn := 1;
161 end if;
162 /*----------------------Enhancement for LA by :Satyagi---------*/
163 end enable_fn_sec ;
164
165 function get_fn_status(p_fn_name in varchar2) return NUMBER IS
166 l_excp_fn_name constant varchar2(30) := 'MSC_EXCP_FN';
167 l_res_fn_name constant varchar2(30) := 'MSC_RES_FN';
168 l_supp_fn_name constant varchar2(30) := 'MSC_SUPPLIER_FN';
169 l_oper_fn_name constant varchar2(30) := 'MSC_OPER_FN';
170 l_kpi_fn_name constant varchar2(30) := 'MSC_KPI_FN';
171 l_peg_fn_name constant varchar2(30) := 'MSC_PEG_FN';
172 l_hz_fn_name constant varchar2(30) := 'MSC_HZ_FN';
173 l_vert_fn_name constant varchar2(30) := 'MSC_VERT_FN';
174 l_gc_fn_name constant varchar2(30) := 'MSC_GC_FN';
175 l_bill_fn_name constant varchar2(30) := 'MSC_BILL_FN';
176 l_item_fn_name constant varchar2(30) := 'MSC_ITEM_FN';
177 l_comp_fn_name constant varchar2(30) := 'MSC_COMP_FN';
178 l_used_fn_name constant varchar2(30) := 'MSC_USED_FN';
179 l_sour_fn_name constant varchar2(30) := 'MSC_SOUR_FN';
180 l_dest_fn_name constant varchar2(30) := 'MSC_DEST_FN';
181 l_peff_fn_name constant varchar2(30) := 'MSC_PEFF_FN';
182 l_coprod_fn_name constant varchar2(30) := 'MSC_COPROD_FN';
183 l_ss_fn_name constant varchar2(30) := 'MSC_SS_FN';
184 l_psub_fn_name constant varchar2(30) := 'MSC_PSUB_FN';
185 l_replan_fn_name constant varchar2(30) := 'MSC_REPLAN_FN';
186 l_21a_fn_name constant varchar2(30) := 'MSC_21A_FN';
187 l_21b_fn_name constant varchar2(30) := 'MSC_21B_FN';
188 l_option_fn_name constant varchar2(30) := 'MSC_OPTION_FN';
189 l_23a_fn_name constant varchar2(30) := 'MSC_23A_FN';
190 l_23b_fn_name constant varchar2(30) := 'MSC_23B_FN';
191 l_pref_fn_name constant varchar2(30) := 'MSC_PREF_FN';
192 l_undo_fn_name constant varchar2(30) := 'MSC_UNDO_FN';
193 l_pcr_fn_name constant varchar2(30) := 'MSC_PCR_FN';
194 l_28_fn_name constant varchar2(30) := 'MSC_28_FN';
195 l_cal_fn_name constant varchar2(30) := 'MSC_CAL_FN';
196 l_invlv_fn_name constant varchar2(30) := 'MSC_MSCINVLV';
197 l_res_batch_name constant varchar2(30) := 'MSC_RES_BATCH_FN';
198 /*---------------------Enhancement for LA by :Satyagi---------*/
199 l_liability_name constant varchar2(30) := 'MSC_LIABILITY_FN';
200 /* ----------------------Enhancement for LA by :Satyagi---------*/
201 begin
202 if (p_fn_name = l_excp_fn_name ) then
203 return g_excp_fn;
204 elsif (p_fn_name = l_res_batch_name ) then
205 return g_res_batch_fn;
206 /*---------------------Enhancement for LA by :Satyagi---------*/
207 elsif (p_fn_name = l_liability_name ) then
208 return g_liability_fn;
209 /*----------------------Enhancement for LA by :Satyagi---------*/
210 elsif (p_fn_name = l_res_fn_name ) then
211 return g_res_fn;
212 elsif (p_fn_name = l_supp_fn_name ) then
213 return g_supp_fn;
214 elsif (p_fn_name = l_oper_fn_name ) then
215 return g_oper_fn;
216 elsif (p_fn_name = l_kpi_fn_name ) then
217 return g_kpi_fn;
218 elsif (p_fn_name = l_peg_fn_name ) then
219 return g_peg_fn;
220 elsif (p_fn_name = l_hz_fn_name ) then
221 return g_hz_fn;
222 elsif (p_fn_name = l_vert_fn_name ) then
223 return g_vert_fn;
224 elsif (p_fn_name = l_gc_fn_name ) then
225 return g_gc_fn;
226 elsif (p_fn_name = l_bill_fn_name ) then
227 return g_bill_fn;
228 elsif (p_fn_name = l_item_fn_name ) then
229 return g_item_fn;
230 elsif (p_fn_name = l_comp_fn_name ) then
231 return g_comp_fn;
232 elsif (p_fn_name = l_used_fn_name ) then
233 return g_used_fn;
234 elsif (p_fn_name = l_sour_fn_name ) then
235 return g_sour_fn;
236 elsif (p_fn_name = l_dest_fn_name ) then
237 return g_dest_fn;
238 elsif (p_fn_name = l_peff_fn_name ) then
239 return g_peff_fn;
240 elsif (p_fn_name = l_coprod_fn_name ) then
241 return g_coprod_fn;
242 elsif (p_fn_name = l_ss_fn_name ) then
243 return g_ss_fn;
244 elsif (p_fn_name = l_psub_fn_name ) then
245 return g_psub_fn;
246 elsif (p_fn_name = l_replan_fn_name ) then
247 return g_replan_fn;
248 elsif (p_fn_name = l_21a_fn_name ) then
249 return g_21a_fn;
250 elsif (p_fn_name = l_21b_fn_name ) then
251 return g_21b_fn;
252 elsif (p_fn_name = l_option_fn_name ) then
253 return g_option_fn;
254 elsif (p_fn_name = l_23a_fn_name ) then
255 return g_23a_fn;
256 elsif (p_fn_name = l_23b_fn_name ) then
257 return g_23b_fn;
258 elsif (p_fn_name = l_pref_fn_name ) then
259 return g_pref_fn;
260 elsif (p_fn_name = l_undo_fn_name ) then
261 return g_undo_fn;
262 elsif (p_fn_name = l_pcr_fn_name ) then
263 return g_pcr_fn;
264 elsif (p_fn_name = l_28_fn_name ) then
265 return g_pcr_fn;
266 elsif (p_fn_name = l_cal_fn_name ) then
267 return g_cal_fn;
268 elsif (p_fn_name = l_invlv_fn_name ) then
269 return g_invlv_fn;
270 else
271 --Acutally invalid input from the caller
272 return -1;
273 end if;
274 end get_fn_status ;
275
276 procedure invoke_mscfnscwb( p_resp_app in varchar2,
277 p_resp_key in varchar2,
278 p_secgrp_key in varchar2,
279 p_err_msg out nocopy varchar2,
280 p_url out nocopy varchar2) is
281
282 l_apps_web_agent varchar2(250);
283 l_plsql_proc varchar2(250);
284 l_actual_params varchar2(250);
285 l_temp varchar2(250);
286 l_length number ;
287 l_amp varchar2(2) := fnd_global.local_chr(38);
288 l_found number;
289
290 begin
291 p_err_msg := null; --initialize to null
292 p_url := null;
293
294 l_apps_web_agent := fnd_profile.value('APPS_WEB_AGENT');
295
296 --check if the last char is '/', else append
297 l_length := length(l_apps_web_agent);
298 l_temp := substr(l_apps_web_agent,l_length,1);
299 l_found := instr(l_temp,'/');
300 if ( l_found <= 0 ) then
301 l_apps_web_agent := l_apps_web_agent||'/';
302 --dbms_output.put_line(' l_apps_web_agent appended '||l_apps_web_agent );
303 end if;
304
305 l_plsql_proc := 'fnd_icx_launch.runforms?';
306 l_actual_params := 'ICX_TICKET='||l_amp||
307 'RESP_APP='||p_resp_app||l_amp||
308 'RESP_KEY='||p_resp_key||l_amp||
309 'SECGRP_KEY='||p_secgrp_key;
310
311 p_url := l_apps_web_agent||l_plsql_proc||l_actual_params;
312 --dbms_output.put_line(' p_url '||p_url );
313 exception
314 when others then
315 p_err_msg := sqlerrm;
316 p_url := null;
317 end invoke_mscfnscwb;
318
319 procedure launch_supplier_pwb ( p_unique_id in varchar2,
320 p_user_id in number,
321 p_supplier_id in number,
322 p_supplier_site_id in number,
323 p_err_msg out nocopy varchar2,
324 p_url out nocopy varchar2 ) is
325 l_valid_input number;
326 begin
327 p_err_msg := null;
328 p_url := null;
329
330 --validate the input
331 l_valid_input := msc_supplier_pwb.valid_supplier_type(p_unique_id,
332 p_user_id, p_supplier_id, p_supplier_site_id);
333 if ( l_valid_input = -1 ) then
334 p_err_msg := 'MSC_FSEC_INVALID_SUPPLIER';
335 return;
336 elsif ( l_valid_input = -2 ) then
337 p_err_msg := 'MSC_FSEC_SUPPLIER_NO_PLANS';
338 return;
339 elsif ( l_valid_input = -3 ) then
340 p_err_msg := 'MSC_FSEC_NO_RESPONSIBILITY';
341 return;
342 elsif ( l_valid_input < 0 ) then
343 p_err_msg := 'MSC_FSEC_INVALID_INPUT';
344 return;
345 end if;
346 --validate the input ends
347
348 --store the context values in global vars
349 g_supplier_id := p_supplier_id;
350 g_supp_site_id := p_supplier_site_id;
351 --g_inst_id, g_org_id, g_partner_type are set in valid_supplier_type fn
352
353
354 --set the global parameters if required ..hardcoded.
355 --g_resp_app := 'MSC';
356 --g_resp_key := 'MSC_TEST_RESP';
357 --g_secgrp_key := 'STANDARD';
358 --g_func := 'MSCFNSCW-VSCP';
359
360 --build the url, based on parameters
361 msc_supplier_pwb.invoke_mscfnscwb( g_resp_app,
362 g_resp_key,
363 g_secgrp_key,
364 p_err_msg,
365 p_url);
366 exception
367 when others then
368 p_err_msg := sqlerrm;
369 p_url := null;
370 end launch_supplier_pwb;
371
372 FUNCTION check_supplier_plans (p_supplier_id number) return boolean IS
373 CURSOR c_supp (l_supplier_id in number) IS
374 SELECT count(*)
375 FROM msc_designators d,
376 msc_plans p,
377 msc_item_suppliers mis
378 WHERE p.organization_id = d.organization_id
379 AND p.sr_instance_id = d.sr_instance_id
380 AND p.compile_designator = d.designator
381 AND p.plan_completion_date IS NOT NULL
382 AND p.data_completion_date IS NOT NULL
383 AND p.plan_id <> -1
384 AND nvl(d.production,2) = 1
385 AND p.curr_plan_type in (1,2,3)
386 AND p.plan_id = mis.plan_id
387 AND p.sr_instance_id = mis.sr_instance_id
388 AND p.organization_id = mis.organization_id
389 AND mis.supplier_id = l_supplier_id;
390
391 l_temp number;
392 BEGIN
393 open c_supp (p_supplier_id);
394 fetch c_supp into l_temp;
395 close c_supp;
396 if (l_temp = 0 ) then
397 return FALSE;
398 else
399 return TRUE;
400 end if;
401
402 END check_supplier_plans ;
403
404 FUNCTION modelled_as_org return boolean IS
405
406 CURSOR c_org IS
407 select count(*)
408 from fnd_user fu,
409 msc_company_users mcu,
410 msc_trading_partner_maps map,
411 msc_company_relationships mcrs,
412 msc_trading_partners mtp
413 where fu.user_id = mcu.user_id
414 and mcu.company_id = mcrs.object_id
415 and mcrs.subject_id = 1
416 and mcrs.relationship_type = 2
417 and mcrs.relationship_id = map.company_key
418 and map.map_type = 1
419 and map.tp_key = mtp.modeled_supplier_id
420 and mtp.partner_type = 3
421 and fu.user_id = fnd_global.user_id;
422 l_temp number;
423 BEGIN
424 open c_org;
425 fetch c_org into l_temp;
426 close c_org;
427
428 if (l_temp = 0 ) then
429 return FALSE;
430 else
431 return TRUE;
432 end if;
433 END modelled_as_org ;
434
435 FUNCTION modelled_as_supplier return number IS
436
437 CURSOR c_sup IS
438 select mtp.sr_instance_id, mtp.partner_id
439 from fnd_user fu,
440 msc_company_users mcu,
441 msc_trading_partner_maps map,
442 msc_company_relationships mcrs,
443 msc_trading_partners mtp
444 where fu.user_id = mcu.user_id
445 and mcu.company_id = mcrs.object_id
446 and mcrs.subject_id = 1
447 and mcrs.relationship_type = 2
448 and mcrs.relationship_id = map.company_key
449 and map.map_type = 1
450 and map.tp_key = mtp.partner_id
451 and fu.user_id = fnd_global.user_id ;
452
453 l_sup number;
454 l_inst number;
455 BEGIN
456 open c_sup;
457 fetch c_sup into l_inst, l_sup;
458 close c_sup;
459
460 return l_sup;
461
462 END modelled_as_supplier;
463
464 end msc_supplier_pwb ;