1 PACKAGE BODY CN_SFP_ROLE_UTIL_PVT AS
2 -- $Header: cnsfrolb.pls 115.2 2003/01/09 03:03:09 sbadami noship $
3 -- declare global variables...
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_SFP_ROLE_UTIL_PVT';
5
6
7 FUNCTION is_org_valid_role
8 (
9 p_role_id IN NUMBER
10 ) RETURN VARCHAR2
11 IS
12 l_rate_sch_id NUMBER;
13 l_seas_id NUMBER;
14 l_calc_formula_id NUMBER;
15 l_org_id NUMBER;
16 l_rate_sch_pass VARCHAR2(1) := 'Y';
17 l_formula_pass VARCHAR2(1) := 'Y';
18 l_seas_pass VARCHAR2(1) := 'Y';
19
20 l_total_traversed VARCHAR2(1) := 'Y';
21
22 l_return VARCHAR2(1) := 'Y';
23 l_temp_org NUMBER;
24
25 CURSOR role_quota_cate_cur IS
26 SELECT rqc.ROLE_ID,rqc.QUOTA_CATEGORY_ID,rqc.RATE_SCHEDULE_ID,rqc.CALC_FORMULA_ID,
27 rqc.SEAS_SCHEDULE_ID FROM CN_ROLE_QUOTA_CATES rqc WHERE ROLE_ID = p_role_id;
28 BEGIN
29
30 l_org_id := fnd_profile.value('ORG_ID');
31
32 FOR eachq IN role_quota_cate_cur LOOP
33 l_rate_sch_id :=eachq.RATE_SCHEDULE_ID;
34 l_calc_formula_id := eachq.calc_formula_id;
35 l_seas_id := eachq.SEAS_SCHEDULE_ID;
36
37 -- Start doing the cheque for Rate Schedules
38 IF l_rate_sch_id IS NOT NULL THEN
39 SELECT NVL(ORG_ID,-99) INTO l_temp_org FROM CN_RATE_SCHEDULES_ALL WHERE rate_schedule_id = l_rate_sch_id;
40 IF l_temp_org <> l_org_id THEN
41 l_rate_sch_pass := 'N';
42 EXIT;
43 END IF;
44 END IF;
45
46 IF l_calc_formula_id IS NOT NULL THEN
47 SELECT NVL(ORG_ID,-99) INTO l_temp_org FROM CN_CALC_FORMULAS_ALL WHERE CALC_FORMULA_ID = l_calc_formula_id and org_id = l_org_id;
48 IF l_temp_org <> l_org_id THEN
49 l_formula_pass := 'N';
50 EXIT;
51 END IF;
52 END IF;
53
54 IF l_seas_id IS NOT NULL THEN
55 SELECT NVL(ORG_ID,-99) INTO l_temp_org FROM CN_SEAS_SCHEDULES_ALL WHERE SEAS_SCHEDULE_ID = l_seas_id;
56 IF l_temp_org <> l_org_id THEN
57 l_seas_pass := 'N';
58 EXIT;
59 END IF;
60 END IF;
61
62 END LOOP;
63
64 IF l_total_traversed = 'N' THEN
65 NULL;
66 END IF;
67
68 IF (l_rate_sch_pass = 'Y' AND l_formula_pass = 'Y' AND l_seas_pass = 'Y') THEN
69 l_return := 'Y';
70 ELSE
71 l_return := 'N';
72 END IF;
73
74 return l_return;
75 EXCEPTION
76 WHEN OTHERS THEN
77 RAISE;
78 END;
79
80 -- Validating a give Role Quota Cate for the rates from ADMIN Tables.
81
82 FUNCTION validate_roleqc_for_rates
83 (
84 p_role_quota_cate_id IN NUMBER
85 ) RETURN VARCHAR2
86 IS
87 l_return VARCHAR2(1) := 'Y';
88 l_rate_schedule_id NUMBER;
89
90 CURSOR role_quota_rate_cur IS
91 select nvl(attribute1,-1) rate_tier_id ,rate_tier_id rate_sequence from cn_role_quota_rates
92 where role_quota_cate_id = p_role_quota_cate_id;
93
94
95 l_role_qc_cur role_quota_rate_cur%ROWTYPE;
96 l_count_rate_tiers NUMBER := 0;
97 BEGIN
98 -- Get Rate Schedule ID for the given role quota cate
99 BEGIN
100 select NVL(rate_schedule_id,-1) into l_rate_schedule_id
101 from cn_role_quota_cates where role_quota_cate_id = p_role_quota_cate_id;
102 EXCEPTION
103 WHEN OTHERS THEN
104 l_rate_schedule_id := -1;
105 END;
106
107 -- If there is no rate schedule id for the given role quota
108 -- cate you can return success.
109 IF (l_rate_schedule_id > 0) THEN
110 -- Identify the rate_tier_id and sequence and compare against
111 -- CN_RATE_TIERS for the changes
112 FOR eachq IN role_quota_rate_cur LOOP
113 l_role_qc_cur.rate_tier_id := eachq.rate_tier_id;
114 l_role_qc_cur.rate_sequence := eachq.rate_sequence;
115
116 IF (l_role_qc_cur.rate_tier_id > 0) THEN
117 select count(*) INTO l_count_rate_tiers from cn_rate_tiers
118 where rate_tier_id = l_role_qc_cur.rate_tier_id
119 and rate_sequence = l_role_qc_cur.rate_sequence;
120
121 -- If the tiers don't match to 1 no need to check further.
122 IF ((l_count_rate_tiers < 1) OR (l_count_rate_tiers > 1))THEN
123 l_return := 'N';
124 EXIT;
125 END IF;
126 END IF;
127 l_count_rate_tiers := 0;
128 END LOOP;
129 END IF;
130
131 return l_return;
132 EXCEPTION
133 WHEN OTHERS THEN
134 RAISE;
135 END;
136
137 END CN_SFP_ROLE_UTIL_PVT;
[Home] [Help]