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