[Home] [Help]
PACKAGE BODY: APPS.CN_SRP_QUOTA_RATES_PKG
Source
1 PACKAGE body cn_srp_quota_rates_pkg AS
2 /*$Header: cntsrqrb.pls 115.3 2002/01/28 20:06:13 pkm ship $*/
3
4 -------------------------------------------------------------------------------+
5 -- Procedure Name : Insert_Row --+
6 -- Purpose : Insert given fields into the cn_srp_quota_rates table; --+
7 -- also do a cascade insert into cn_srp_quota_rates if --+
8 -- required --+
9 -- History --+
10 -- 06-JUN-2000 mblum Created --+
11 -------------------------------------------------------------------------------+
12 PROCEDURE insert_row
13 (SRP_QUOTA_CATE_ID IN NUMBER,
14 ROLE_QUOTA_RATE_ID IN NUMBER,
15 SRP_ID IN NUMBER,
16 QUOTA_CATEGORY_ID IN NUMBER,
17 RATE_TIER_ID IN NUMBER,
18 COMM_RATE IN NUMBER,
19 ATTRIBUTE_CATEGORY IN VARCHAR2 := NULL,
20 ATTRIBUTE1 IN VARCHAR2 := NULL,
21 ATTRIBUTE2 IN VARCHAR2 := NULL,
22 ATTRIBUTE3 IN VARCHAR2 := NULL,
23 ATTRIBUTE4 IN VARCHAR2 := NULL,
24 ATTRIBUTE5 IN VARCHAR2 := NULL,
25 ATTRIBUTE6 IN VARCHAR2 := NULL,
26 ATTRIBUTE7 IN VARCHAR2 := NULL,
27 ATTRIBUTE8 IN VARCHAR2 := NULL,
28 ATTRIBUTE9 IN VARCHAR2 := NULL,
29 ATTRIBUTE10 IN VARCHAR2 := NULL,
30 ATTRIBUTE11 IN VARCHAR2 := NULL,
31 ATTRIBUTE12 IN VARCHAR2 := NULL,
32 ATTRIBUTE13 IN VARCHAR2 := NULL,
33 ATTRIBUTE14 IN VARCHAR2 := NULL,
34 ATTRIBUTE15 IN VARCHAR2 := NULL) IS
35
36 l_srp_quota_rate_id number;
37
38 BEGIN
39 -- Get the next value in sequence
40 SELECT cn_srp_quota_rates_s.nextval
41 INTO l_srp_quota_rate_id
42 FROM dual;
43
44 -- Now insert row
45 INSERT INTO cn_srp_quota_rates
46 (srp_quota_rate_id, srp_quota_cate_id, role_quota_rate_id, srp_id,
47 quota_category_id, rate_tier_id, comm_rate, attribute_category,
48 attribute1, attribute2, attribute3, attribute4, attribute5,
49 attribute6, attribute7, attribute8, attribute9, attribute10,
50 attribute11, attribute12, attribute13, attribute14, attribute15,
51 last_update_date, last_updated_by, last_update_login, creation_date,
52 created_by, object_version_number) VALUES
53 (l_srp_quota_rate_id, srp_quota_cate_id, role_quota_rate_id, srp_id,
54 quota_category_id, rate_tier_id, comm_rate, attribute_category,
55 attribute1, attribute2, attribute3, attribute4, attribute5,
56 attribute6, attribute7, attribute8, attribute9, attribute10,
57 attribute11, attribute12, attribute13, attribute14, attribute15,
58 sysdate, fnd_global.user_id, fnd_global.login_id,
59 sysdate, fnd_global.user_id, 1);
60 END Insert_Row;
61
62 -------------------------------------------------------------------------------+
63 -- Procedure Name : Update_Row --+
64 -- Purpose : Update given fields in the cn_srp_quota_rates table; --+
65 -- also do a cascade update into cn_srp_quota_rates if --+
66 -- required --+
67 -- History --+
68 -- 06-JUN-2000 mblum Created --+
69 -------------------------------------------------------------------------------+
70 PROCEDURE update_row
71 (SRP_QUOTA_RATE_ID IN NUMBER,
72 SRP_QUOTA_CATE_ID IN NUMBER := FND_API.G_MISS_NUM,
73 ROLE_QUOTA_RATE_ID IN NUMBER := FND_API.G_MISS_NUM,
74 SRP_ID IN NUMBER := FND_API.G_MISS_NUM,
75 QUOTA_CATEGORY_ID IN NUMBER := FND_API.G_MISS_NUM,
76 RATE_TIER_ID IN NUMBER := FND_API.G_MISS_NUM,
77 COMM_RATE IN NUMBER := FND_API.G_MISS_NUM,
78 ATTRIBUTE_CATEGORY IN VARCHAR2 := FND_API.G_MISS_CHAR,
79 ATTRIBUTE1 IN VARCHAR2 := FND_API.G_MISS_CHAR,
80 ATTRIBUTE2 IN VARCHAR2 := FND_API.G_MISS_CHAR,
81 ATTRIBUTE3 IN VARCHAR2 := FND_API.G_MISS_CHAR,
82 ATTRIBUTE4 IN VARCHAR2 := FND_API.G_MISS_CHAR,
83 ATTRIBUTE5 IN VARCHAR2 := FND_API.G_MISS_CHAR,
84 ATTRIBUTE6 IN VARCHAR2 := FND_API.G_MISS_CHAR,
85 ATTRIBUTE7 IN VARCHAR2 := FND_API.G_MISS_CHAR,
86 ATTRIBUTE8 IN VARCHAR2 := FND_API.G_MISS_CHAR,
87 ATTRIBUTE9 IN VARCHAR2 := FND_API.G_MISS_CHAR,
88 ATTRIBUTE10 IN VARCHAR2 := FND_API.G_MISS_CHAR,
89 ATTRIBUTE11 IN VARCHAR2 := FND_API.G_MISS_CHAR,
90 ATTRIBUTE12 IN VARCHAR2 := FND_API.G_MISS_CHAR,
91 ATTRIBUTE13 IN VARCHAR2 := FND_API.G_MISS_CHAR,
92 ATTRIBUTE14 IN VARCHAR2 := FND_API.G_MISS_CHAR,
93 ATTRIBUTE15 IN VARCHAR2 := FND_API.G_MISS_CHAR) IS
94
95 l_srp_quota_rate_id number := srp_quota_rate_id;
96 l_srp_quota_cate_id number;
97 l_role_quota_rate_id number;
98 l_quota_category_id number;
99 l_srp_id number;
100 l_rate_tier_id number;
101 l_comm_rate number;
102 l_attribute_category varchar2(150);
103 l_attribute1 varchar2(150);
104 l_attribute2 varchar2(150);
105 l_attribute3 varchar2(150);
106 l_attribute4 varchar2(150);
107 l_attribute5 varchar2(150);
108 l_attribute6 varchar2(150);
109 l_attribute7 varchar2(150);
110 l_attribute8 varchar2(150);
111 l_attribute9 varchar2(150);
112 l_attribute10 varchar2(150);
113 l_attribute11 varchar2(150);
114 l_attribute12 varchar2(150);
115 l_attribute13 varchar2(150);
116 l_attribute14 varchar2(150);
117 l_attribute15 varchar2(150);
118 l_object_version_number number;
119
120 CURSOR l_update_csr IS
121 SELECT srp_quota_cate_id, role_quota_rate_id, quota_category_id, srp_id,
122 rate_tier_id, comm_rate, attribute_category, attribute1, attribute2,
123 attribute3, attribute4, attribute5, attribute6, attribute7,
124 attribute8, attribute9, attribute10, attribute11, attribute12,
125 attribute13, attribute14, attribute15, object_version_number
126 FROM cn_srp_quota_rates r
127 WHERE r.srp_quota_rate_id = l_srp_quota_rate_id;
128
129 MN NUMBER := FND_API.G_MISS_NUM;
130 MC VARCHAR2(150) := FND_API.G_MISS_CHAR;
131
132 BEGIN
133 OPEN l_update_csr;
134 FETCH l_update_csr INTO l_srp_quota_cate_id, l_quota_category_id,
135 l_role_quota_rate_id, l_srp_id, l_rate_tier_id, l_comm_rate,
136 l_attribute_category, l_attribute1, l_attribute2, l_attribute3,
137 l_attribute4, l_attribute5 , l_attribute6, l_attribute7, l_attribute8,
138 l_attribute9, l_attribute10, l_attribute11, l_attribute12, l_attribute13,
139 l_attribute14, l_attribute15, l_object_version_number;
140 CLOSE l_update_csr;
141
142 -- copy over and handle g-misses
143 IF (srp_quota_cate_id <> MN) THEN l_srp_quota_cate_id := srp_quota_cate_id; END IF;
144 IF (quota_category_id <> MN) THEN l_quota_category_id := quota_category_id; END IF;
145 IF (role_quota_rate_id <> MN) THEN l_role_quota_rate_id := role_quota_rate_id; END IF;
146 IF (srp_id <> MN) THEN l_srp_id := srp_id; END IF;
147 IF (rate_tier_id <> MN) THEN l_rate_tier_id := rate_tier_id; END IF;
148 IF (comm_rate <> MN) THEN l_comm_rate := comm_rate; END IF;
149 IF (attribute_category <> MC) THEN l_attribute_category := attribute_category; END IF;
150 IF (attribute1 <> MC) THEN l_attribute1 := attribute1; END IF;
151 IF (attribute2 <> MC) THEN l_attribute2 := attribute2; END IF;
152 IF (attribute3 <> MC) THEN l_attribute3 := attribute3; END IF;
153 IF (attribute4 <> MC) THEN l_attribute4 := attribute4; END IF;
154 IF (attribute5 <> MC) THEN l_attribute5 := attribute5; END IF;
155 IF (attribute6 <> MC) THEN l_attribute6 := attribute6; END IF;
156 IF (attribute7 <> MC) THEN l_attribute7 := attribute7; END IF;
157 IF (attribute8 <> MC) THEN l_attribute8 := attribute8; END IF;
158 IF (attribute9 <> MC) THEN l_attribute9 := attribute9; END IF;
159 IF (attribute10 <> MC) THEN l_attribute10 := attribute10; END IF;
160 IF (attribute11 <> MC) THEN l_attribute11 := attribute11; END IF;
161 IF (attribute12 <> MC) THEN l_attribute12 := attribute12; END IF;
162 IF (attribute13 <> MC) THEN l_attribute13 := attribute13; END IF;
163 IF (attribute14 <> MC) THEN l_attribute14 := attribute14; END IF;
164 IF (attribute15 <> MC) THEN l_attribute15 := attribute15; END IF;
165
166 UPDATE cn_srp_quota_rates r SET
167 srp_quota_cate_id = l_srp_quota_cate_id,
168 role_quota_rate_id = l_role_quota_rate_id,
169 quota_category_id = l_quota_category_id, srp_id = l_srp_id,
170 rate_tier_id = l_rate_tier_id, comm_rate = l_comm_rate,
171 attribute_category = l_attribute_category,
172 attribute1 = l_attribute1, attribute2 = l_attribute2,
173 attribute3 = l_attribute3, attribute4 = l_attribute4,
174 attribute5 = l_attribute5, attribute6 = l_attribute6,
175 attribute7 = l_attribute7, attribute8 = l_attribute8,
176 attribute9 = l_attribute9, attribute10 = l_attribute10,
177 attribute11 = l_attribute11, attribute12 = l_attribute12,
178 attribute13 = l_attribute13, attribute14 = l_attribute14,
179 attribute15 = l_attribute15,
180 object_version_number = l_object_version_number + 1,
181 last_update_date = sysdate,
182 last_updated_by = fnd_global.user_id,
183 last_update_login = fnd_global.login_id
184 WHERE r.srp_quota_rate_id = l_srp_quota_rate_id;
185 END Update_Row;
186
187 -------------------------------------------------------------------------------+
188 -- Procedure Name : Delete_Row --+
189 -- Purpose : Delete the specified row in the cn_srp_quota_rates table;--+
190 -- also cascade delete in cn_srp_quota_rates if required --+
191 -- History --+
192 -- 06-JUN-2000 mblum Created --+
193 -------------------------------------------------------------------------------+
194 PROCEDURE delete_row
195 (SRP_QUOTA_RATE_ID IN NUMBER) IS
196 l_srp_quota_rate_id number := srp_quota_rate_id;
197 BEGIN
198 DELETE FROM cn_srp_quota_rates r
199 WHERE r.srp_quota_rate_id = l_srp_quota_rate_id;
200 END Delete_Row;
201
202 END cn_srp_quota_rates_pkg;