[Home] [Help]
PACKAGE BODY: APPS.MSC_SECRULE_PKG
Source
4
1 PACKAGE BODY MSC_SECRULE_PKG AS
2 -- $Header: MSCXVSPB.pls 120.1 2005/06/20 04:26:27 appldev ship $
3
5
6 Procedure INSERT_SEC_RULE
7 ( p_order_type IN Number,
8 p_item_name IN Varchar2,
9 p_customer_name IN Varchar2,
10 p_supplier_name IN Varchar2,
11 p_customer_site_name IN Varchar2,
12 p_supplier_site_name IN Varchar2,
13 p_org_name IN Varchar2,
14 p_grantee_type IN Varchar2,
15 p_grantee_key IN Varchar2,
16 p_start_date IN Date,
17 p_end_date IN Date,
18 p_privilege IN Varchar2,
19 p_order_number IN Varchar2,
20 p_company_name IN Varchar2,
21 p_return_code OUT NOCOPY Number,
22 p_err_msg OUT NOCOPY Varchar2)
23 IS
24
25
26 l_return_code Number :=0;
27 l_err_msg Varchar2(1000);
28
29 -- local variables for given inputs
30
31 l_lookup_code Number;
32 l_item_id Number;
33 l_customer_id Number;
34 l_supplier_id Number;
35 l_customer_site_id Number;
36 l_supplier_site_id Number;
37 l_org_id Number;
38 l_grantee_key Number;
39 l_grantee_type Varchar2(100);
40 l_start_date Date;
41 l_end_date Date;
42 l_company_id Number;
43 l_customer_flag boolean := false;
44 l_supplier_flag boolean :=false;
45 l_customer_item_flag boolean := true;
46 l_supplier_item_flag boolean :=true;
47 l_responsibility_key varchar2(30);
48 l_duplicate_rule_id Number;
49
50 CURSOR c_old_security_rule
51 IS
52 SELECT sr.rule_id
53 FROM msc_x_security_rules sr
54 WHERE decode(p_order_type,null,-1,sr.order_type) = decode(p_order_type,null,-1,p_order_type)
55 AND decode(l_item_id,null,-1,sr.item_id) = decode(l_item_id,null,-1,l_item_id)
56 AND decode(l_customer_id,null,-1,sr.customer_id) = decode(l_customer_id,null,-1,l_customer_id)
57 AND decode(l_supplier_id,null,-1,sr.supplier_id) = decode(l_supplier_id,null,-1,l_supplier_id)
58 AND decode(l_customer_site_id,null,-1,sr.customer_site_id)= decode(l_customer_site_id,null,-1,l_customer_site_id)
59 AND decode(l_supplier_site_id,null,-1,sr.supplier_site_id)= decode(l_supplier_site_id,null,-1,l_supplier_site_id)
60 AND decode(l_org_id,null,-1,sr.org_id) = decode(l_org_id,null,-1,l_org_id)
61 AND decode(p_order_number,null,'xx',sr.order_number) = decode(p_order_number,null,'xx',p_order_number)
62 AND sr.grantee_type = p_grantee_type
63 AND sr.grantee_key = l_grantee_key
64 AND sr.privilege = p_privilege
65 AND sr.company_id = l_company_id;
66 --AND sr.effective_from_date= l_start_date
67 --AND sr.effective_to_date = l_end_date
68
69
70
71 BEGIN
72 p_return_code:=0;
73 l_responsibility_key:=mscx_ui_utilities.get_responsibility_key;
74 --dbms_output.put_line('initial resp id is ' ||l_responsibility_key);
75
76 -- company validation
77
78 if (l_responsibility_key is not null ) and (l_responsibility_key='MSCX_SC_ADMIN_FULL') then
79
80 validate_company_name(p_company_name,l_company_id,p_return_code, p_err_msg );
81 --dbms_output.put_line('after company name ' );
82
83 else
84 l_company_id:=sys_context('msc','company_id');
85 --dbms_output.put_line('initial comp id is ' ||l_company_id);
86 end if;
87
88
89
90
91
92 -- order type validation
93
94
95 -- validate_order_type(p_order_type,l_lookup_code,p_return_code, p_err_msg );
96
97 l_lookup_code:=p_order_type;
98
99
100
101
102 -- item_name validation
103
107
104 validate_item_name(p_item_name,p_company_name,l_item_id,p_return_code,p_err_msg );
105
106 -- customer validation
108 validate_customer_name(p_customer_name,l_company_id,l_customer_id,l_customer_flag,p_return_code, p_err_msg );
109
110
111 -- supplier validation
112
113 validate_supplier_name(p_supplier_name,l_company_id,l_supplier_id,l_supplier_flag,p_return_code, p_err_msg );
114 --dbms_output.put_line('supplier name is ' ||l_supplier_id ||' '|| p_supplier_name||'return code' ||p_return_code);
115
116
117
118 -- customer site validation
119
120 validate_customer_site_name(p_customer_site_name,l_company_id,l_customer_id,l_customer_site_id,l_customer_flag,p_return_code, p_err_msg );
121 --dbms_output.put_line('customer name name is ' ||l_customer_id ||' '|| p_customer_name||'return code' ||p_return_code);
122
123
124
125 -- supplier site validation
126
127 validate_supplier_site_name(p_supplier_site_name,l_company_id,l_supplier_id,l_supplier_site_id,l_supplier_flag,p_return_code, p_err_msg );
128 --dbms_output.put_line('supplier site name is ' ||p_return_code);
129
130 -- org validation
131
132 validate_org_name(p_org_name,l_company_id,l_org_id,p_return_code, p_err_msg );
133
134 -- grantee key validation
135
136 validate_grantee_key(p_grantee_type,p_grantee_key,l_grantee_key,p_return_code, p_err_msg );
137 --dbms_output.put_line('grantee key ' ||p_return_code );
138
139
140 --- Date validations
141 if p_start_date is not null then
142 BEGIN
143 --l_start_date:=trunc(to_date(p_start_date));
144 l_start_date:=p_start_date;
145 exception
146 when others then
147 p_return_code := -1;
148 p_err_msg := p_err_msg ||' '||'Invalid Start date';
149 -- --dbms_output.put_line('l_err_msg:=' ||p_err_msg);
150 END;
151 else
152 l_start_date:=sysdate;
153 end if ;
154
155
156 --Check if end date > start date
157
158 if p_end_date is not NULL then
159 BEGIN
160 --l_end_date:=trunc(to_date(p_end_date));
161 l_end_date:=p_end_date;
162 if l_end_date<l_start_date then
163 p_return_code := -1;
164 p_err_msg := p_err_msg ||' '||'Invalid End date';
165 -- --dbms_output.put_line('err message is ' ||p_err_msg );
166 end if;
167
168 exception
169 when others then
170 p_return_code := -1;
171 p_err_msg := p_err_msg ||' '||'Invalid End date';
172 END;
173 else
174 l_end_date := null; /* reset the value if it is null */
175 end if;
176 --dbms_output.put_line('return code before insert is ' ||p_return_code);
177
178 if p_return_code=0 then
179
180
181 -- open the cursor to ceck for duplicacy of the records OK.
182
183 OPEN c_old_security_rule ;
184 FETCH c_old_security_rule INTO l_duplicate_rule_id;
185 if c_old_security_rule%FOUND then
186 p_return_code := -1;
187 p_err_msg := p_err_msg ||' '||'This rule already exists';
188 --dbms_output.put_line('agoel error message for DR' ||p_err_msg);
189 end if ;
190 --dbms_output.put_line('agoel return code is ' ||p_return_code);
191 CLOSE c_old_security_rule;
192 end if ;
193
194 if p_return_code=0 then
195 if l_lookup_code>ORDER_TYPE_ZERO then
196 -- if l_lookup_code IS NOT NULL then
197
198 insert into msc_x_security_rules(rule_id, order_type,item_id,customer_id,customer_site_id,
199 supplier_id,supplier_site_id,org_id,order_number,grantee_type,
200 grantee_key,privilege,company_id,effective_from_date,effective_to_date,
201 item_name,creation_date,created_by,last_update_date,last_updated_by)
202 values
203 (msc_security_rules_s.nextval,l_lookup_code,l_item_id,l_customer_id,l_customer_site_id,
204 l_supplier_id,l_supplier_site_id,l_org_id,p_order_number,p_grantee_type,
205 l_grantee_key,p_privilege,l_company_id,l_start_date,l_end_date,
206 p_item_name,sysdate,fnd_global.user_id,sysdate,fnd_global.user_id);
207 else
208 insert into msc_x_security_rules(rule_id, order_type,item_id,customer_id,customer_site_id,
209 supplier_id,supplier_site_id,org_id,order_number,grantee_type,
210 grantee_key,privilege,company_id,effective_from_date,effective_to_date,
211 item_name,creation_date,created_by,last_update_date,last_updated_by)
212 values
213 (msc_security_rules_s.nextval,NULL,l_item_id,l_customer_id,l_customer_site_id,
214 l_supplier_id,l_supplier_site_id,l_org_id,p_order_number,p_grantee_type,
215 l_grantee_key,p_privilege,l_company_id,l_start_date,l_end_date,
216 p_item_name,sysdate,fnd_global.user_id,sysdate,fnd_global.user_id);
217 end if;
218
219
220
221
222 end if;
223 exception
224 when no_data_found then
228
225 p_return_code := -1;
226 p_err_msg := p_err_msg ||' '||'Invalid company name';
227 --dbms_output.put_line('l_err_msg:=' ||SQLERRM);
229 when others then
230 p_return_code := -1;
231 p_err_msg := p_err_msg ||' '||'error while inserting the record';
232 --dbms_output.put_line('l_err_msg:=' ||SQLERRM);
233
234
235
236
237
238 END INSERT_SEC_RULE;
239
240 -- edit rule
241
242
243 Procedure EDIT_SEC_RULE
244 ( p_order_type IN Number,
245 p_item_name IN Varchar2,
246 p_customer_name IN Varchar2,
247 p_supplier_name IN Varchar2,
248 p_customer_site_name IN Varchar2,
249 p_supplier_site_name IN Varchar2,
250 p_org_name IN Varchar2,
251 p_grantee_type IN Varchar2,
252 p_grantee_key IN Varchar2,
253 p_start_date IN Date,
254 p_end_date IN Date,
255 p_privilege IN Varchar2,
256 p_order_number IN Varchar2,
257 p_company_name IN Varchar2,
258 p_rule_id IN Number,
259 p_return_code OUT NOCOPY Number,
260 p_err_msg OUT NOCOPY Varchar2)
261 IS
262
263
264
265 l_return_code Number :=0;
266 l_err_msg Varchar2(1000);
267
268 -- local variables for given inputs
269
270 l_lookup_code Number;
271 l_item_id Number;
272 l_category_id Number;
273 l_customer_id Number;
274 l_supplier_id Number;
275 l_customer_site_id Number;
276 l_supplier_site_id Number;
277 l_org_id Number;
278 l_grantee_key Number; -- earlier it was varchar30
279 l_grantee_type Varchar2(100);
280 l_start_date Date;
281 l_end_date Date;
282 l_company_id Number;
283 --l_rule_id Number;
284 l_customer_flag boolean := false;
285 l_supplier_flag boolean :=false;
286 l_customer_item_flag boolean := true;
287 l_supplier_item_flag boolean :=true;
288 l_responsibility_key Varchar2(30);
289 l_duplicate_rule_id Number;
290
291
292
293 CURSOR c_old_security_rule
294 IS
295 SELECT sr.rule_id
296 FROM msc_x_security_rules sr
297 WHERE decode(l_lookup_code,null,-1,sr.order_type) = decode(l_lookup_code,null,-1,l_lookup_code)
298 AND decode(l_item_id,null,-1,sr.item_id) = decode(l_item_id,null,-1,l_item_id)
299 AND decode(l_customer_id,null,-1,sr.customer_id) = decode(l_customer_id,null,-1,l_customer_id)
300 AND decode(l_supplier_id,null,-1,sr.supplier_id) = decode(l_supplier_id,null,-1,l_supplier_id)
301 AND decode(l_customer_site_id,null,-1,sr.customer_site_id)= decode(l_customer_site_id,null,-1,l_customer_site_id)
302 AND decode(l_supplier_site_id,null,-1,sr.supplier_site_id)= decode(l_supplier_site_id,null,-1,l_supplier_site_id)
303 AND decode(l_org_id,null,-1,sr.org_id) = decode(l_org_id,null,-1,l_org_id)
304 AND decode(p_order_number,null,-1,sr.order_number) = decode(p_order_number,null,-1,p_order_number)
305 AND sr.grantee_type = p_grantee_type
306 AND sr.grantee_key = l_grantee_key
307 AND sr.privilege = p_privilege
308 AND sr.company_id = l_company_id
309 AND sr.rule_id <> p_rule_id;
310
311
312 -- ordertype
313
314
315 BEGIN
316 p_return_code:=0;
317 l_responsibility_key:=mscx_ui_utilities.get_responsibility_key;
318
319 --dbms_output.put_line('initial resp id is ' ||l_responsibility_key);
320
321 -- company validation
322
323 if (l_responsibility_key is not null ) and (l_responsibility_key='MSCX_SC_ADMIN_FULL') then
324
325 validate_company_name(p_company_name,l_company_id,p_return_code, p_err_msg );
326 --dbms_output.put_line('after company name ' );
327
328 else
329 l_company_id:=sys_context('msc','company_id');
330 --dbms_output.put_line('initial comp id is ' ||l_company_id);
331 end if;
332
333
334
335
336
337 -- order type validation
338
339
340 -- validate_order_type(p_order_type,l_lookup_code,p_return_code, p_err_msg );
341
342 l_lookup_code:=p_order_type;
343
344
345
346 -- item_name validation
347
348 validate_item_name(p_item_name,p_company_name,l_item_id,p_return_code,p_err_msg );
349
350 -- customer validation
351
352 validate_customer_name(p_customer_name,l_company_id,l_customer_id,l_customer_flag,p_return_code, p_err_msg );
353
354
355 -- supplier validation
356
357 validate_supplier_name(p_supplier_name,l_company_id,l_supplier_id,l_supplier_flag,p_return_code, p_err_msg );
358 --dbms_output.put_line('supplier name is ' ||l_supplier_id ||' '|| p_supplier_name||'return code' ||p_return_code);
359
360
361
362 -- customer site validation
363
364 validate_customer_site_name(p_customer_site_name,l_company_id,l_customer_id,l_customer_site_id,l_customer_flag,p_return_code, p_err_msg );
368
365 --dbms_output.put_line('customer name name is ' ||l_customer_id ||' '|| p_customer_name||'return code' ||p_return_code);
366
367
369 -- supplier site validation
370
371 validate_supplier_site_name(p_supplier_site_name,l_company_id,l_supplier_id,l_supplier_site_id,l_supplier_flag,p_return_code, p_err_msg );
372 --dbms_output.put_line('supplier site name is ' ||p_return_code);
373
374 -- org validation
375
376 validate_org_name(p_org_name,l_company_id,l_org_id,p_return_code, p_err_msg );
377
378 -- grantee key validation
379
380 validate_grantee_key(p_grantee_type,p_grantee_key,l_grantee_key,p_return_code, p_err_msg );
381 --dbms_output.put_line('grantee key ' ||p_return_code );
382
383
384 --- Date validations
385 if p_start_date is not null then
386 BEGIN
387 --l_start_date:=trunc(to_date(p_start_date));
388 l_start_date:=p_start_date;
389 exception
390 when others then
391 p_return_code := -1;
392 p_err_msg := p_err_msg ||' '||'Invalid Start date';
393 -- --dbms_output.put_line('l_err_msg:=' ||p_err_msg);
394 END;
395 else
396 l_start_date:=sysdate;
397 end if ;
398
399
400 --Check if end date > start date
401 if p_end_date is not NULL then
402 BEGIN
403 --l_end_date:=trunc(to_date(p_end_date));
404 l_end_date:=p_end_date;
405 if l_end_date<l_start_date then
406 p_return_code := -1;
407 p_err_msg := p_err_msg ||' '||'Invalid End date';
408 -- --dbms_output.put_line('err message is ' ||p_err_msg );
409 end if;
410
411 exception
412 when others then
413 p_return_code := -1;
414 p_err_msg := p_err_msg ||' '||'Invalid End date';
415 END;
416 else
417 l_end_date := null; /* reset the value if it is null */
418 end if;
419 --dbms_output.put_line('return code before insert is ' ||p_return_code);
420
421 if p_return_code=0 then
422
423
424 -- open the cursor to ceck for duplicacy of the records OK.
425
426 OPEN c_old_security_rule ;
427 FETCH c_old_security_rule INTO l_duplicate_rule_id;
428 if c_old_security_rule%FOUND then
429 p_return_code := -1;
430 p_err_msg := p_err_msg ||' '||'This rule already exists';
431 --dbms_output.put_line('agoel error message for DR' ||p_err_msg);
432 end if ;
433 CLOSE c_old_security_rule;
434 end if ;
435
436 if p_return_code=0 then
437 if l_lookup_code>ORDER_TYPE_ZERO then
438
439
440 update msc_x_security_rules set
441 order_type = l_lookup_code,
442 item_id = l_item_id,
443 customer_id = l_customer_id,
444 customer_site_id = l_customer_site_id,
445 supplier_id = l_supplier_id,
446 supplier_site_id = l_supplier_site_id,
447 org_id = l_org_id,
448 order_number = p_order_number,
449 grantee_type = p_grantee_type,
450 grantee_key = l_grantee_key,
451 privilege = p_privilege,
452 company_id = l_company_id,
453 effective_from_date = l_start_date,
454 effective_to_date = l_end_date,
455 item_name = p_item_name,
456 -- order_type_meaning = p_order_type,
457 last_update_date = sysdate,
458 last_updated_by = fnd_global.user_id
459 where rule_id = p_rule_id;
460
461 else
462
463 update msc_x_security_rules set
464 order_type = null,
465 item_id = l_item_id,
466 customer_id = l_customer_id,
467 customer_site_id = l_customer_site_id,
468 supplier_id = l_supplier_id,
469 supplier_site_id = l_supplier_site_id,
470 org_id = l_org_id,
471 order_number = p_order_number,
472 grantee_type = p_grantee_type,
473 grantee_key = l_grantee_key,
474 privilege = p_privilege,
475 company_id = l_company_id,
476 effective_from_date = l_start_date,
477 effective_to_date = l_end_date,
478 item_name = p_item_name,
479 -- order_type_meaning = p_order_type,
480 last_update_date = sysdate,
481 last_updated_by = fnd_global.user_id
482 where rule_id = p_rule_id;
483
484 end if;
485
486
487 end if;
488 exception
489 when no_data_found then
490 p_return_code := -1;
494 when others then
491 p_err_msg := p_err_msg ||' '||'Invalid company name';
492 --dbms_output.put_line('l_err_msg:=' ||p_err_msg);
493
495 p_return_code := 1;
496 p_err_msg := p_err_msg ||' '||'error during updating the record';
497 -- --dbms_output.put_line('l_err_msg:=' ||p_err_msg);
498
499
500
501
502
503 END EDIT_SEC_RULE;
504
505
506
507 /* Procedure VALIDATE_ORDER_TYPE
508 ( p_order_type IN Varchar2,
509 l_lookup_code OUT Number,
510 p_return_code IN OUT Number,
511 p_err_msg IN OUT Varchar2)
512 IS
513 BEGIN
514 if p_order_type is not null then
515 BEGIN
516 select lookup_code into l_lookup_code
517 from fnd_lookup_values
518 where lookup_type='MSC_X_ORDER_TYPE'
519 and meaning=p_order_type
520 and language=userenv('lang');
521
522
523 -- --dbms_output.put_line('order type exists' ||l_lookup_code);
524
525 exception when no_data_found then
526 p_return_code:=-1;
527 p_err_msg:= p_err_msg ||' '|| 'Invalid Order Type';
528 -- --dbms_output.put_line('l_err_msg:=' ||p_err_msg);
529 END;
530 else
531 l_lookup_code:=null;
532 end if;
533 END VALIDATE_ORDER_TYPE;*/
534
535
536 -- company validation Binding done to validate name containing ( company's ) to be implemented in all cursors
537 -- due to time constarint implemented only in one
538
539 Procedure VALIDATE_COMPANY_NAME
540 ( p_company_name IN Varchar2,
541 l_company_id OUT NOCOPY Number,
542 p_return_code IN OUT NOCOPY Number,
543 p_err_msg IN OUT NOCOPY Varchar2)
544 IS
545
546 TYPE SECCurTyp IS REF CURSOR;
547 sec_cursor SECCurTyp;
548 sql_statement varchar2(500);
549 BEGIN
550 if p_company_name is not null then
551 BEGIN
552 --dbms_output.put_line('before the company id select'||p_company_name );
553 sql_statement :='Select mc.company_id
554 from msc_companies mc
555 where mc.company_name=:1';
556
557 OPEN sec_cursor FOR sql_statement USING
558 p_company_name;
559
560
561 FETCH sec_cursor INTO l_company_id;
562 CLOSE sec_cursor;
563
564 exception when no_data_found then
565 p_return_code:=-1;
566 p_err_msg:= p_err_msg ||' '|| 'Invalid Company Name'||sqlcode||sqlerrm;
567 --dbms_output.put_line('Company Name Exists' ||p_err_msg);
568 raise;
569 END;
570 end if;
571 END VALIDATE_COMPANY_NAME;
572
573
574
575
576 -- item name validation
577
578 Procedure VALIDATE_ITEM_NAME
579 ( p_item_name IN Varchar2,
580 p_company_name IN Varchar2,
581 l_item_id OUT NOCOPY Number,
582 p_return_code IN OUT NOCOPY Number,
583 p_err_msg IN OUT NOCOPY Varchar2)
584 IS
585
586 l_customer_item_flag boolean := true;
587 l_supplier_item_flag boolean :=true;
588
589 BEGIN
590 if p_item_name is not null then
591 BEGIN
592 select s.inventory_item_id into l_item_id
593 from
594 msc_item_suppliers s
595 , msc_trading_partners tp
596 where
597 s.supplier_id = tp.partner_id
598 and tp.partner_type = 1
599 and tp.partner_name = nvl(p_company_name,sys_context('msc','company_name'))
600 and s.supplier_item_name=p_item_name
601 and s.plan_id=-1
602 and rownum<2;
603
604 -- --dbms_output.put_line('item name exists' ||l_item_id);
605
606 exception when others then
607 -- p_return_code:=1;
608 -- p_err_msg:= p_err_msg ||' '|| 'Invalid Item Name';
609 --dbms_output.put_line('l_err_msg:= supplier item does not exist' );
610 l_supplier_item_flag:=false;
611 END;
612
613 if (p_item_name is not null ) and ( l_supplier_item_flag=false ) then
614 BEGIN
615 select s.inventory_item_id into l_item_id
616 from
617 msc_item_customers s
618 , msc_trading_partners tp
619 where
620 s.customer_id = tp.partner_id
621 and tp.partner_type = 2
622 and tp.partner_name = nvl(p_company_name,sys_context('msc','company_name'))
623 and s.customer_item_name=p_item_name
624 and rownum<2;
625
626 -- --dbms_output.put_line('item name exists' ||l_item_id);
627
628 exception when others then
629 -- p_return_code:=1;
630 -- p_err_msg:= p_err_msg ||' '|| 'Invalid Item Name';
631 --dbms_output.put_line('l_err_msg:= customer item does not exist' );
632 l_customer_item_flag:=false;
633 END;
634
635 end if;
636
637 if (p_item_name is not null ) and ( l_supplier_item_flag=false ) and ( l_customer_item_flag=false ) then
638 BEGIN
639 select cm.inventory_item_id into l_item_id
640 from msc_items cm
641 where
645
642 item_name=p_item_name;
643
644 -- --dbms_output.put_line('item name exists' ||l_item_id);
646 exception when no_data_found then
647 p_return_code:=-1;
648 p_err_msg:= p_err_msg ||' '|| 'Invalid Item Name';
649 --dbms_output.put_line('l_err_msg:= Invalid item name' );
650
651 END;
652 end if;
653
654 else
655 l_item_id:=null;
656 end if;
657 END VALIDATE_ITEM_NAME;
658
659 -- customer name validation
660
661 Procedure VALIDATE_CUSTOMER_NAME
662 ( p_customer_name IN Varchar2,
663 l_company_id IN Number,
664 l_customer_id OUT NOCOPY Number,
665 l_customer_flag IN OUT NOCOPY boolean,
666 p_return_code IN OUT NOCOPY Number,
667 p_err_msg IN OUT NOCOPY Varchar2)
668 IS
669
670 l_cust_company_id Number:=-999 ;
671
672 BEGIN
673
674 if p_customer_name is not null then
675 BEGIN
676 select company_id into l_cust_company_id
677 from msc_companies where
678 company_name=p_customer_name;
679 --dbms_output.put_line('Customer company id exists'|| l_cust_company_id );
680 exception when no_data_found then
681 p_return_code:=-1;
682 -- p_err_msg:= p_err_msg ||' '|| 'Invalid Customer Name ';
683 --dbms_output.put_line('l_err_msg:='||p_err_msg);
684 END;
685 if l_company_id=l_cust_company_id then
686 l_customer_id:=l_cust_company_id;
687 l_customer_flag:=true;
688 else
689 BEGIN
690 Select mcr.subject_id into l_customer_id
691 from msc_company_relationships mcr,
692 msc_companies mc
693 where
694 mcr.subject_id = mc.company_id
695 and mcr.relationship_type = 2
696 and mcr.object_id =l_company_id
697 and mc.company_name=p_customer_name ;
698 l_customer_flag:=true;
699
700 --dbms_output.put_line('Customer Name Exists' ||l_item_id);
701
702 exception when no_data_found then
703 p_return_code:=-1;
704 p_err_msg:= p_err_msg ||' '|| 'Invalid Customer Name';
705 ----dbms_output.put_line('l_err_msg:=' ||p_err_msg);
706 END;
707 end if ;
708 else
709 l_customer_id:=null;
710 end if;
711
712 END VALIDATE_CUSTOMER_NAME;
713
714
715 -- supplier name validation
716
717 Procedure VALIDATE_SUPPLIER_NAME
718 ( p_supplier_name IN Varchar2,
719 l_company_id IN Number,
720 l_supplier_id OUT NOCOPY Number,
721 l_supplier_flag IN OUT NOCOPY boolean,
722 p_return_code IN OUT NOCOPY Number,
723 p_err_msg IN OUT NOCOPY Varchar2)
724 IS
725
726 l_supp_company_id Number:=-999 ;
727
728 BEGIN
729 if p_supplier_name is not null then
730 BEGIN
731 Select company_id into l_supp_company_id
732 from msc_companies
733 where company_name=p_supplier_name;
734 --dbms_output.put_line('Suppliercompany id exists'|| l_supp_company_id );
735 exception when no_data_found then
736 p_return_code:=-1;
737 -- p_err_msg:= p_err_msg ||' '|| 'Invalid Supplier Name';
738 ----dbms_output.put_line('l_err_msg:=' ||p_err_msg);
739 END;
740 if l_company_id=l_supp_company_id then
741 l_supplier_id := l_supp_company_id;
742 l_supplier_flag:=true;
743
744 else
745 BEGIN
746 Select mcr.subject_id into l_supplier_id
747 from msc_company_relationships mcr,
748 msc_companies mc
749 where
750 mcr.subject_id = mc.company_id
751 and mcr.relationship_type = 1
752 and mcr.object_id =l_company_id
756 --dbms_output.put_line('Supplier Name Exists' ||l_supplier_id);
753 and mc.company_name=p_supplier_name ;
754 l_supplier_flag:=true;
755
757
758 exception when no_data_found then
759 p_return_code:=-1;
760 p_err_msg:= p_err_msg ||' '|| 'Invalid Supplier Name';
761 ----dbms_output.put_line('l_err_msg:=' ||p_err_msg);
762 END;
763 end if;
764 else
765 l_supplier_id:=null ;
766 end if;
767
768 END VALIDATE_SUPPLIER_NAME;
769
770 Procedure VALIDATE_CUSTOMER_SITE_NAME
771 ( p_customer_site_name IN Varchar2,
772 l_company_id IN Number,
773 l_customer_id IN Number,
774 l_customer_site_id OUT NOCOPY Number,
775 l_customer_flag IN OUT NOCOPY boolean,
776 p_return_code IN OUT NOCOPY Number,
777 p_err_msg IN OUT NOCOPY Varchar2)
778 IS
779
780 l_cust_site_company_id Number:=-999 ;
781 l_cust_site_company_id_flag boolean :=true;
782
783
784 BEGIN
785 if (p_customer_site_name is not null ) and (l_customer_flag= true) then
786
787 BEGIN
788 select company_site_id into l_cust_site_company_id
789 from msc_company_sites
790 where company_id=l_company_id
791 and company_site_name=p_customer_site_name;
792 --dbms_output.put_line('Customer company id exists'|| l_cust_company_id );
793 exception when no_data_found then
794 l_cust_site_company_id_flag :=false;
795
796 END;
797
798 if (l_cust_site_company_id_flag = true) then
799 l_customer_site_id:=l_cust_site_company_id;
800 else
801 BEGIN
802 select cs.company_site_id into l_customer_site_id
803 from msc_company_relationships mcr,
804 msc_company_sites cs
805 where mcr.subject_id = cs.company_id
806 and mcr.relationship_type = 2
807 and mcr.object_id=l_company_id
808 and cs.company_site_name=p_customer_site_name
809 and mcr.subject_id=l_customer_id ; -- added to validate if customer site belongs to a customer
810
811 ----dbms_output.put_line('Customer Site Name Exists' ||l_item_id);
812
813 exception when no_data_found then
814 p_return_code:=-1;
815 p_err_msg:= p_err_msg ||' '|| 'Invalid Customer Site for Customer ';
816 -- --dbms_output.put_line('l_err_msg:=' ||p_err_msg);
817 END;
818 end if;
819
820 elsif (p_customer_site_name is not null ) then
821 p_return_code:=-1;
822 p_err_msg:= p_err_msg ||' '|| 'Enter Valid Customer for Customer Site';
823 else
824 l_customer_site_id:=null;
825 end if;
826 END VALIDATE_CUSTOMER_SITE_NAME;
827
828 -- supplier_site validation
829
830 Procedure VALIDATE_SUPPLIER_SITE_NAME
831 ( p_supplier_site_name IN Varchar2,
832 l_company_id IN Number,
833 l_supplier_id IN Number,
834 l_supplier_site_id OUT NOCOPY Number,
835 l_supplier_flag IN OUT NOCOPY boolean,
836 p_return_code IN OUT NOCOPY Number,
837 p_err_msg IN OUT NOCOPY Varchar2)
838 IS
839
840 l_supp_site_company_id Number:=-999 ;
841 l_supp_site_company_id_flag boolean :=true;
842
843 BEGIN
844 if (p_supplier_site_name is not null) and (l_supplier_flag=true) then
845
846 BEGIN
847 select company_site_id into l_supp_site_company_id
848 from msc_company_sites
849 where company_id=l_company_id
850 and company_site_name=p_supplier_site_name;
851 --dbms_output.put_line('Supplier company id exists'|| l_cust_company_id );
852 exception when no_data_found then
853 l_supp_site_company_id_flag :=false;
854
855 END;
856
857 if (l_supp_site_company_id_flag = true) then
858 l_supplier_site_id:=l_supp_site_company_id;
859 else
860
861 BEGIN
862 select cs.company_site_id into l_supplier_site_id
863 from msc_company_relationships mcr,
864 msc_company_sites cs
865 where mcr.subject_id = cs.company_id
866 and mcr.relationship_type = 1--( 'supplier of ' )
867 and mcr.object_id=l_company_id
868 and cs.company_site_name=p_supplier_site_name
869 and mcr.subject_id=l_supplier_id ; -- added to validate if supplier site belongs to a supplier
870
871 --dbms_output.put_line('Supplier Site Name 1' );
872
873 exception when no_data_found then
874 p_return_code:=-1;
875 p_err_msg:= p_err_msg ||' '|| 'Invalid Supplier Site Name for Supplier ';
876 --dbms_output.put_line('supplier site exception 1 :=' ||p_err_msg);
877 END;
878 end if ;
879
880 elsif (p_supplier_site_name is not null ) then
881
882 p_return_code:=-1;
883 p_err_msg:= p_err_msg ||' '|| 'Enter Valid Supplier for Supplier Site';
884
885 else
886 l_supplier_site_id:=null;
887 end if;
888
889 END VALIDATE_SUPPLIER_SITE_NAME;
890
891 -- validate org name
892
896 l_org_id OUT NOCOPY Number,
893 Procedure VALIDATE_ORG_NAME
894 ( p_org_name IN Varchar2,
895 l_company_id IN Number,
897 p_return_code IN OUT NOCOPY Number,
898 p_err_msg IN OUT NOCOPY Varchar2)
899 IS
900 BEGIN
901 if p_org_name is not null then
902 BEGIN
903 select company_site_id into l_org_id
904 from msc_company_sites
905 where company_id=l_company_id
906 and company_site_name=p_org_name;
907
908 -- --dbms_output.put_line('Org Name Exists' ||l_item_id);
909
910 exception when no_data_found then
911 p_return_code:=-1;
912 p_err_msg:= p_err_msg ||' '|| 'Invalid Org Name';
913 --dbms_output.put_line('l_err_msg:=' ||p_err_msg);
914 END;
915 else
916 l_org_id:=null;
917 end if;
918 END VALIDATE_ORG_NAME;
919
920
921 Procedure VALIDATE_GRANTEE_KEY
922 ( p_grantee_type IN Varchar2,
923 p_grantee_key IN Varchar2,
924 l_grantee_key OUT NOCOPY Number,
925 p_return_code IN OUT NOCOPY Number,
926 p_err_msg IN OUT NOCOPY Varchar2)
927 IS
928 BEGIN
929 if p_grantee_type = 'COMPANY' then
930
931 if p_grantee_key is not null then
932 BEGIN
933 select company_id into l_grantee_key from msc_companies
934 where company_name=p_grantee_key;
935
936 -- --dbms_output.put_line('grantee key Exists' ||l_item_id);
937
938 exception when no_data_found then
939 p_return_code:=-1;
940 p_err_msg:= p_err_msg ||' '|| 'Invalid Assigned To';
941 -- --dbms_output.put_line('l_err_msg:=' ||p_err_msg);
942 END;
943 else
944 --l_grantee_key:=null;
945 -- code for grantee key as null
946 p_return_code:=-1;
947 p_err_msg:= p_err_msg ||' '|| 'Assigned To cannot be null';
948 end if;
949
950 elsif p_grantee_type='USER' then
951
952 if p_grantee_key is not null then
953 BEGIN
954 select user_id into l_grantee_key from fnd_user
955 where user_name=p_grantee_key;
956
957 -- --dbms_output.put_line('Grantee key Exists' ||l_item_id);
958
959 exception when no_data_found then
960 p_return_code:=-1;
961 p_err_msg:= p_err_msg ||' '|| 'Invalid Assigned To';
962 -- --dbms_output.put_line('l_err_msg:=' ||p_err_msg);
963 END;
964 else
965 -- l_grantee_key:=null;
966 -- code for grantee key as null
967 p_return_code:=-1;
968 p_err_msg:= p_err_msg ||' '|| 'Assigned To cannot be null';
969 end if;
970
971 elsif p_grantee_type='RESPONSIBILITY' then
972 if p_grantee_key is not null then
973 BEGIN
974 select responsibility_id into l_grantee_key from fnd_responsibility_vl
975 where responsibility_name=p_grantee_key;
976
977 -- --dbms_output.put_line('Grantee key Exists' ||l_item_id);
978
979 exception when no_data_found then
980 p_return_code:=-1;
981 p_err_msg:= p_err_msg ||' '|| 'Invalid Assigned To';
982 -- --dbms_output.put_line('l_err_msg:=' ||p_err_msg);
983 END;
984 else
985 -- l_grantee_key:=null;
986 -- code for grantee key as null
987 p_return_code:=-1;
988 p_err_msg:= p_err_msg ||' '|| 'Assigned To cannot be null';
989 end if;
990
991 elsif p_grantee_type='GROUP' then
992 if p_grantee_key is not null then
993 BEGIN
994 select group_id into l_grantee_key from msc_groups
995 where group_name =p_grantee_key;
996
997 exception when no_data_found then
998 p_return_code:=-1;
999 p_err_msg:= p_err_msg ||' '|| 'Invalid Assigned To';
1000 END;
1001 else
1002 -- l_grantee_key:=null;
1003 -- code for grantee key as null
1004 p_return_code:=-1;
1005 p_err_msg:= p_err_msg ||' '|| 'Assigned To cannot be null';
1006 end if;
1007
1008 elsif (p_grantee_type='GLOBAL' ) then
1009 if p_grantee_key is not null then
1010 p_err_msg:= p_err_msg ||' '|| 'Assigned To should be null';
1011 p_return_code:=-1;
1012 -- --dbms_output.put_line('l_err_msg:=' ||p_err_msg);
1013 else
1014 l_grantee_key:=null;
1015 end if;
1016
1017 end if;
1018
1019 END VALIDATE_GRANTEE_KEY;
1020
1021
1022
1023
1024
1025 END MSC_SECRULE_PKG;
1026
1027
1028
1029
1030
1031
1032