1 package body PA_CC_TP_RULES_BG_PKG AS
2 /* $Header: PAXTPRTB.pls 120.2 2005/08/05 03:28:35 rgandhi noship $ */
3 procedure INSERT_ROW (
4 X_ROWID in out NOCOPY VARCHAR2,/*File.sql.39*/
5 X_TP_RULE_ID in NUMBER,
6 X_BUSINESS_GROUP_ID in NUMBER,
7 X_NAME in VARCHAR2,
8 X_RULE_TYPE in VARCHAR2,
9 X_MARKUP_CALC_BASE_CODE in VARCHAR2,
10 X_CALC_METHOD_CODE in VARCHAR2,
11 X_PERCENTAGE_APPLIED in NUMBER,
12 X_START_DATE_ACTIVE in DATE,
13 X_END_DATE_ACTIVE in DATE,
14 X_SCHEDULE_ID in NUMBER,
15 X_ATTRIBUTE_CATEGORY in VARCHAR2,
16 X_ATTRIBUTE1 in VARCHAR2,
17 X_ATTRIBUTE2 in VARCHAR2,
18 X_ATTRIBUTE3 in VARCHAR2,
19 X_ATTRIBUTE4 in VARCHAR2,
20 X_ATTRIBUTE5 in VARCHAR2,
21 X_ATTRIBUTE6 in VARCHAR2,
22 X_ATTRIBUTE7 in VARCHAR2,
23 X_ATTRIBUTE8 in VARCHAR2,
24 X_ATTRIBUTE9 in VARCHAR2,
25 X_ATTRIBUTE10 in VARCHAR2,
26 X_ATTRIBUTE11 in VARCHAR2,
27 X_ATTRIBUTE12 in VARCHAR2,
28 X_ATTRIBUTE13 in VARCHAR2,
29 X_ATTRIBUTE14 in VARCHAR2,
30 X_ATTRIBUTE15 in VARCHAR2,
31 X_DESCRIPTION in VARCHAR2,
32 X_CREATION_DATE in DATE,
33 X_CREATED_BY in NUMBER,
34 X_LAST_UPDATE_DATE in DATE,
35 X_LAST_UPDATED_BY in NUMBER,
36 X_LAST_UPDATE_LOGIN in NUMBER
37 ) is
38 cursor C is select ROWID from PA_CC_TP_RULES_BG
39 where TP_RULE_ID = X_TP_RULE_ID;
40 begin
41 insert into PA_CC_TP_RULES_BG (
42 TP_RULE_ID,
43 BUSINESS_GROUP_ID,
44 NAME,
45 RULE_TYPE,
46 MARKUP_CALC_BASE_CODE,
47 CALC_METHOD_CODE,
48 PERCENTAGE_APPLIED,
49 START_DATE_ACTIVE,
50 LAST_UPDATE_DATE,
51 LAST_UPDATED_BY,
52 CREATION_DATE,
53 CREATED_BY,
54 LAST_UPDATE_LOGIN,
55 END_DATE_ACTIVE,
56 DESCRIPTION,
57 SCHEDULE_ID,
58 ATTRIBUTE_CATEGORY,
59 ATTRIBUTE1,
60 ATTRIBUTE2,
61 ATTRIBUTE3,
62 ATTRIBUTE4,
63 ATTRIBUTE5,
64 ATTRIBUTE6,
65 ATTRIBUTE7,
66 ATTRIBUTE8,
67 ATTRIBUTE9,
68 ATTRIBUTE10,
69 ATTRIBUTE11,
70 ATTRIBUTE12,
71 ATTRIBUTE13,
72 ATTRIBUTE14,
73 ATTRIBUTE15)
74 values(
75 X_TP_RULE_ID,
76 X_BUSINESS_GROUP_ID,
77 X_NAME,
78 X_RULE_TYPE,
79 X_MARKUP_CALC_BASE_CODE,
80 X_CALC_METHOD_CODE,
81 X_PERCENTAGE_APPLIED,
82 X_START_DATE_ACTIVE,
83 X_LAST_UPDATE_DATE,
84 X_LAST_UPDATED_BY,
85 X_CREATION_DATE,
86 X_CREATED_BY,
87 X_LAST_UPDATE_LOGIN,
88 X_END_DATE_ACTIVE,
89 X_DESCRIPTION,
90 X_SCHEDULE_ID,
91 X_ATTRIBUTE_CATEGORY,
92 X_ATTRIBUTE1,
93 X_ATTRIBUTE2,
94 X_ATTRIBUTE3,
95 X_ATTRIBUTE4,
96 X_ATTRIBUTE5,
97 X_ATTRIBUTE6,
98 X_ATTRIBUTE7,
99 X_ATTRIBUTE8,
100 X_ATTRIBUTE9,
101 X_ATTRIBUTE10,
102 X_ATTRIBUTE11,
103 X_ATTRIBUTE12,
104 X_ATTRIBUTE13,
105 X_ATTRIBUTE14,
106 X_ATTRIBUTE15);
107 open c;
108 fetch c into X_ROWID;
109 if (c%notfound) then
110 close c;
111 raise no_data_found;
112 end if;
113 close c;
114 end INSERT_ROW;
115
116 procedure LOCK_ROW (
117 X_TP_RULE_ID in NUMBER,
118 X_BUSINESS_GROUP_ID in NUMBER,
119 X_NAME in VARCHAR2,
120 X_RULE_TYPE in VARCHAR2,
121 X_MARKUP_CALC_BASE_CODE in VARCHAR2,
122 X_CALC_METHOD_CODE in VARCHAR2,
123 X_PERCENTAGE_APPLIED in NUMBER,
124 X_START_DATE_ACTIVE in DATE,
125 X_END_DATE_ACTIVE in DATE,
126 X_SCHEDULE_ID in NUMBER,
127 X_ATTRIBUTE_CATEGORY in VARCHAR2,
128 X_ATTRIBUTE1 in VARCHAR2,
129 X_ATTRIBUTE2 in VARCHAR2,
130 X_ATTRIBUTE3 in VARCHAR2,
131 X_ATTRIBUTE4 in VARCHAR2,
132 X_ATTRIBUTE5 in VARCHAR2,
133 X_ATTRIBUTE6 in VARCHAR2,
134 X_ATTRIBUTE7 in VARCHAR2,
135 X_ATTRIBUTE8 in VARCHAR2,
136 X_ATTRIBUTE9 in VARCHAR2,
137 X_ATTRIBUTE10 in VARCHAR2,
138 X_ATTRIBUTE11 in VARCHAR2,
139 X_ATTRIBUTE12 in VARCHAR2,
140 X_ATTRIBUTE13 in VARCHAR2,
141 X_ATTRIBUTE14 in VARCHAR2,
142 X_ATTRIBUTE15 in VARCHAR2,
143 X_DESCRIPTION in VARCHAR2
144 ) is
145 cursor c1 is select
146 BUSINESS_GROUP_ID,
147 NAME,
148 RULE_TYPE,
149 MARKUP_CALC_BASE_CODE,
150 CALC_METHOD_CODE,
151 PERCENTAGE_APPLIED,
152 START_DATE_ACTIVE,
153 END_DATE_ACTIVE,
154 SCHEDULE_ID,
155 ATTRIBUTE_CATEGORY,
156 ATTRIBUTE1,
157 ATTRIBUTE2,
158 ATTRIBUTE3,
159 ATTRIBUTE4,
160 ATTRIBUTE5,
161 ATTRIBUTE6,
162 ATTRIBUTE7,
163 ATTRIBUTE8,
164 ATTRIBUTE9,
165 ATTRIBUTE10,
166 ATTRIBUTE11,
167 ATTRIBUTE12,
168 ATTRIBUTE13,
169 ATTRIBUTE14,
170 ATTRIBUTE15,
171 DESCRIPTION
172 from PA_CC_TP_RULES_BG
173 where TP_RULE_ID = X_TP_RULE_ID
174 for update of TP_RULE_ID nowait;
175 begin
176 for tlinfo in c1 loop
177 if ( ((tlinfo.DESCRIPTION = X_DESCRIPTION)
178 OR ((tlinfo.DESCRIPTION is null) AND (X_DESCRIPTION is null)))
179 AND (tlinfo.BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID)
180 AND (tlinfo.NAME = X_NAME)
181 AND (tlinfo.RULE_TYPE = X_RULE_TYPE)
182 AND (tlinfo.MARKUP_CALC_BASE_CODE = X_MARKUP_CALC_BASE_CODE)
183 AND (tlinfo.CALC_METHOD_CODE = X_CALC_METHOD_CODE)
184 AND (tlinfo.PERCENTAGE_APPLIED = X_PERCENTAGE_APPLIED)
185 AND (tlinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
186 AND ((tlinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
187 OR ((tlinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
188 AND ((tlinfo.SCHEDULE_ID = X_SCHEDULE_ID)
189 OR ((tlinfo.SCHEDULE_ID is null) AND (X_SCHEDULE_ID is null)))
190 AND ((tlinfo.ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY)
191 OR ((tlinfo.ATTRIBUTE_CATEGORY is null) AND (X_ATTRIBUTE_CATEGORY is null)))
192 AND ((tlinfo.ATTRIBUTE1 = X_ATTRIBUTE1)
193 OR ((tlinfo.ATTRIBUTE1 is null) AND (X_ATTRIBUTE1 is null)))
194 AND ((tlinfo.ATTRIBUTE2 = X_ATTRIBUTE2)
195 OR ((tlinfo.ATTRIBUTE2 is null) AND (X_ATTRIBUTE2 is null)))
196 AND ((tlinfo.ATTRIBUTE3 = X_ATTRIBUTE3)
197 OR ((tlinfo.ATTRIBUTE3 is null) AND (X_ATTRIBUTE3 is null)))
198 AND ((tlinfo.ATTRIBUTE4 = X_ATTRIBUTE4)
199 OR ((tlinfo.ATTRIBUTE4 is null) AND (X_ATTRIBUTE4 is null)))
200 AND ((tlinfo.ATTRIBUTE5 = X_ATTRIBUTE5)
201 OR ((tlinfo.ATTRIBUTE5 is null) AND (X_ATTRIBUTE5 is null)))
202 AND ((tlinfo.ATTRIBUTE6 = X_ATTRIBUTE6)
203 OR ((tlinfo.ATTRIBUTE6 is null) AND (X_ATTRIBUTE6 is null)))
204 AND ((tlinfo.ATTRIBUTE7 = X_ATTRIBUTE7)
205 OR ((tlinfo.ATTRIBUTE7 is null) AND (X_ATTRIBUTE7 is null)))
206 AND ((tlinfo.ATTRIBUTE8 = X_ATTRIBUTE8)
207 OR ((tlinfo.ATTRIBUTE8 is null) AND (X_ATTRIBUTE8 is null)))
208 AND ((tlinfo.ATTRIBUTE9 = X_ATTRIBUTE9)
209 OR ((tlinfo.ATTRIBUTE9 is null) AND (X_ATTRIBUTE9 is null)))
210 AND ((tlinfo.ATTRIBUTE10 = X_ATTRIBUTE10)
211 OR ((tlinfo.ATTRIBUTE10 is null) AND (X_ATTRIBUTE10 is null)))
212 AND ((tlinfo.ATTRIBUTE11 = X_ATTRIBUTE11)
213 OR ((tlinfo.ATTRIBUTE11 is null) AND (X_ATTRIBUTE11 is null)))
214 AND ((tlinfo.ATTRIBUTE12 = X_ATTRIBUTE12)
215 OR ((tlinfo.ATTRIBUTE12 is null) AND (X_ATTRIBUTE12 is null)))
216 AND ((tlinfo.ATTRIBUTE13 = X_ATTRIBUTE13)
217 OR ((tlinfo.ATTRIBUTE13 is null) AND (X_ATTRIBUTE13 is null)))
218 AND ((tlinfo.ATTRIBUTE14 = X_ATTRIBUTE14)
219 OR ((tlinfo.ATTRIBUTE14 is null) AND (X_ATTRIBUTE14 is null)))
220 AND ((tlinfo.ATTRIBUTE15 = X_ATTRIBUTE15)
221 OR ((tlinfo.ATTRIBUTE15 is null) AND (X_ATTRIBUTE15 is null)))
222 ) then
223 null;
224 else
225 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
226 app_exception.raise_exception;
227 end if;
228 end loop;
229 return;
230 end LOCK_ROW;
231
232 procedure UPDATE_ROW (
233 X_ROWID in varchar2,
234 X_TP_RULE_ID in NUMBER,
235 X_BUSINESS_GROUP_ID in NUMBER,
236 X_NAME in VARCHAR2,
237 X_RULE_TYPE in VARCHAR2,
238 X_MARKUP_CALC_BASE_CODE in VARCHAR2,
239 X_CALC_METHOD_CODE in VARCHAR2,
240 X_PERCENTAGE_APPLIED in NUMBER,
241 X_START_DATE_ACTIVE in DATE,
242 X_END_DATE_ACTIVE in DATE,
243 X_SCHEDULE_ID in NUMBER,
244 X_ATTRIBUTE_CATEGORY in VARCHAR2,
245 X_ATTRIBUTE1 in VARCHAR2,
246 X_ATTRIBUTE2 in VARCHAR2,
247 X_ATTRIBUTE3 in VARCHAR2,
248 X_ATTRIBUTE4 in VARCHAR2,
249 X_ATTRIBUTE5 in VARCHAR2,
250 X_ATTRIBUTE6 in VARCHAR2,
251 X_ATTRIBUTE7 in VARCHAR2,
252 X_ATTRIBUTE8 in VARCHAR2,
253 X_ATTRIBUTE9 in VARCHAR2,
254 X_ATTRIBUTE10 in VARCHAR2,
255 X_ATTRIBUTE11 in VARCHAR2,
256 X_ATTRIBUTE12 in VARCHAR2,
257 X_ATTRIBUTE13 in VARCHAR2,
258 X_ATTRIBUTE14 in VARCHAR2,
259 X_ATTRIBUTE15 in VARCHAR2,
260 X_DESCRIPTION in VARCHAR2,
261 X_LAST_UPDATE_DATE in DATE,
262 X_LAST_UPDATED_BY in NUMBER,
263 X_LAST_UPDATE_LOGIN in NUMBER
264 ) is
265 begin
266 update PA_CC_TP_RULES_BG set
267 BUSINESS_GROUP_ID = X_BUSINESS_GROUP_ID,
268 tp_rule_id = x_tp_rule_id,
269 NAME = X_NAME,
270 RULE_TYPE = X_RULE_TYPE,
271 MARKUP_CALC_BASE_CODE = X_MARKUP_CALC_BASE_CODE,
272 CALC_METHOD_CODE = X_CALC_METHOD_CODE,
273 PERCENTAGE_APPLIED = X_PERCENTAGE_APPLIED,
274 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
275 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
276 SCHEDULE_ID = X_SCHEDULE_ID,
277 ATTRIBUTE_CATEGORY = X_ATTRIBUTE_CATEGORY,
278 ATTRIBUTE1 = X_ATTRIBUTE1,
279 ATTRIBUTE2 = X_ATTRIBUTE2,
280 ATTRIBUTE3 = X_ATTRIBUTE3,
281 ATTRIBUTE4 = X_ATTRIBUTE4,
282 ATTRIBUTE5 = X_ATTRIBUTE5,
283 ATTRIBUTE6 = X_ATTRIBUTE6,
284 ATTRIBUTE7 = X_ATTRIBUTE7,
285 ATTRIBUTE8 = X_ATTRIBUTE8,
286 ATTRIBUTE9 = X_ATTRIBUTE9,
287 ATTRIBUTE10 = X_ATTRIBUTE10,
288 ATTRIBUTE11 = X_ATTRIBUTE11,
289 ATTRIBUTE12 = X_ATTRIBUTE12,
290 ATTRIBUTE13 = X_ATTRIBUTE13,
291 ATTRIBUTE14 = X_ATTRIBUTE14,
292 ATTRIBUTE15 = X_ATTRIBUTE15,
293 DESCRIPTION = X_DESCRIPTION,
294 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
295 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
296 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
297 where rowid=x_rowid;
298
299 if (sql%notfound) then
300 raise no_data_found;
301 end if;
302 end UPDATE_ROW;
303
304 procedure DELETE_ROW (
305 X_TP_RULE_ID in NUMBER
306 ) is
307 begin
308 delete from PA_CC_TP_RULES_BG
309 where TP_RULE_ID = X_TP_RULE_ID;
310
311 if (sql%notfound) then
312 raise no_data_found;
313 end if;
314
315 end DELETE_ROW;
316
317 end PA_CC_TP_RULES_BG_PKG;
[Home] [Help]