[Home] [Help]
PACKAGE BODY: APPS.JL_ZZ_AR_TX_CUS_CLS_PKG
Source
1 PACKAGE BODY JL_ZZ_AR_TX_CUS_CLS_PKG AS
2 /* $Header: jlzztacb.pls 120.2 2003/03/03 19:32:04 opedrega ship $ */
3
4
5 PROCEDURE Populate_Cus_Cls_Rows
6 (X_address_id NUMBER,
7 X_class_code VARCHAR2,
8 X_org_id NUMBER) IS
9 BEGIN
10 Insert into JL_ZZ_AR_TX_CUS_CLS(cus_class_id,
11 address_id,
12 tax_attr_class_code,
13 tax_category_id,
14 tax_attribute_name,
15 tax_attribute_value,
16 enabled_flag,
17 org_id,
18 last_update_date,
19 last_updated_by,
20 last_update_login,
21 creation_date,
22 created_by)
23 SELECT JL_ZZ_AR_TX_CUS_CLS_S.nextval,
24 X_address_id,
25 X_class_code,
26 tax_category_id,
27 tax_attribute_name,
28 tax_attribute_value,
29 'Y',
30 X_org_id,
31 SYSDATE,
32 TO_NUMBER(FND_PROFILE.VALUE('USER_ID')),
33 TO_NUMBER(FND_PROFILE.VALUE('LOGIN_ID')),
34 SYSDATE,
35 TO_NUMBER(FND_PROFILE.VALUE('USER_ID'))
36 FROM JL_ZZ_AR_TX_ATT_CLS
37 WHERE tax_attr_class_type = 'CONTRIBUTOR_CLASS'
38 AND tax_attribute_type = 'CONTRIBUTOR_ATTRIBUTE'
39 AND tax_attr_class_code = X_class_code
40 AND org_id = X_org_id;
41 --commit;
42
43 EXCEPTION
44 WHEN NO_DATA_FOUND THEN
45 NULL;
46 WHEN OTHERS THEN
47 NULL;
48 END Populate_Cus_Cls_Rows;
49
50 PROCEDURE Insert_Row
51 (X_rowid IN OUT NOCOPY VARCHAR2,
52 X_cus_class_id NUMBER,
53 X_address_id NUMBER,
54 X_tax_attr_class_code VARCHAR2,
55 X_tax_category_id NUMBER,
56 X_tax_attribute_name VARCHAR2,
57 X_tax_attribute_value VARCHAR2,
58 X_enabled_flag VARCHAR2,
59 X_org_id NUMBER,
60 X_last_updated_by NUMBER,
61 X_last_update_date DATE,
62 X_last_update_login NUMBER,
63 X_creation_date DATE,
64 X_created_by NUMBER,
65 X_calling_sequence IN VARCHAR2) IS
66
67 CURSOR C IS
68 SELECT rowid
69 FROM JL_ZZ_AR_TX_CUS_CLS
70 WHERE tax_attr_class_code = X_tax_attr_class_code
71 AND tax_category_id = X_tax_category_id
72 AND tax_attribute_name = X_tax_attribute_name
73 AND org_id = X_org_id;
74
75 current_calling_sequence VARCHAR2(2000);
76 debug_info VARCHAR2(100);
77
78 BEGIN
79
80 -- Update the calling sequence
81
82 current_calling_sequence := 'JL_ZZ_AR_TX_CUS_CLS_PKG.INSERT_ROW<-' ||
83 X_calling_sequence;
84
85 debug_info := 'Insert into JL_ZZ_AR_TX_CUS_CLS ';
86
87 INSERT INTO JL_ZZ_AR_TX_CUS_CLS(cus_class_id,
88 address_id,
89 tax_attr_class_code,
90 tax_category_id,
91 tax_attribute_name,
92 tax_attribute_value,
93 enabled_flag,
94 org_id,
95 last_updated_by,
96 last_update_date,
97 last_update_login,
98 creation_date,
99 created_by)
100 VALUES(X_cus_class_id,
101 X_address_id,
102 X_tax_attr_class_code,
103 X_tax_category_id,
104 X_tax_attribute_name,
105 X_tax_attribute_value,
106 X_enabled_flag,
107 X_org_id,
108 X_last_updated_by,
109 X_last_update_date,
110 X_last_update_login,
111 X_creation_date,
112 X_created_by);
113
114 debug_info := 'Open cursor C';
115 OPEN C;
116 debug_info := 'Fetch cursor C';
117 FETCH C INTO X_rowid;
118 IF (C%NOTFOUND) THEN
119 debug_info := 'Close cursor C - DATA NOTFOUND';
120 CLOSE C;
121 Raise NO_DATA_FOUND;
122 END IF;
123 debug_info := 'Close cursor C';
124 CLOSE C;
125
126 EXCEPTION
127 WHEN OTHERS THEN
128 IF (SQLCODE <> -20001) THEN
129 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
130 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
131 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
132 FND_MESSAGE.SET_TOKEN('PARAMETERS',
133 ' tax_attr_class_code = ' || X_tax_attr_class_code ||
134 ' tax_category_id = ' || X_tax_category_id ||
135 ' tax_attribute_name = ' || X_tax_attribute_name );
136 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
137 END IF;
138 APP_EXCEPTION.RAISE_EXCEPTION;
139 END Insert_Row;
140
141 PROCEDURE Update_Row
142 (X_rowid VARCHAR2,
143 X_cus_class_id NUMBER,
144 X_address_id NUMBER,
145 X_tax_attr_class_code VARCHAR2,
146 X_tax_category_id NUMBER,
147 X_tax_attribute_name VARCHAR2,
148 X_tax_attribute_value VARCHAR2,
149 X_enabled_flag VARCHAR2,
150 X_org_id NUMBER,
151 X_last_updated_by NUMBER,
152 X_last_update_date DATE,
153 X_last_update_login NUMBER,
154 X_creation_date DATE,
155 X_created_by NUMBER,
156 X_calling_sequence IN VARCHAR2) IS
157
158 BEGIN
159 UPDATE JL_ZZ_AR_TX_CUS_CLS
160 SET cus_class_id = X_cus_class_id,
161 address_id = X_address_id,
162 tax_attr_class_code = X_tax_attr_class_code,
163 tax_category_id = X_tax_category_id,
164 tax_attribute_name = X_tax_attribute_name,
165 tax_attribute_value = X_tax_attribute_value,
166 enabled_flag = X_enabled_flag,
167 org_id = X_org_id,
168 last_updated_by = X_last_updated_by,
169 last_update_date = X_last_update_date,
170 last_update_login = X_last_update_login,
171 creation_date = X_creation_date,
172 created_by = X_created_by
173 WHERE rowid = X_rowid;
174
175 IF (SQL%NOTFOUND) THEN
176 raise NO_DATA_FOUND;
177 END IF;
178
179 END Update_Row;
180
181 PROCEDURE Delete_Row
182 (x_Rowid VARCHAR2) IS
183
184 BEGIN
185
186 DELETE
187 FROM JL_ZZ_AR_TX_CUS_CLS
188 WHERE rowid = X_rowid;
189
190 IF (SQL%NOTFOUND) THEN
191 raise NO_DATA_FOUND;
192 END IF;
193
194 END Delete_Row;
195
196 PROCEDURE Lock_Row
197 (X_rowid VARCHAR2,
198 X_cus_class_id NUMBER,
199 X_address_id NUMBER,
200 X_tax_attr_class_code VARCHAR2,
201 X_tax_category_id NUMBER,
202 X_tax_attribute_name VARCHAR2,
203 X_tax_attribute_value VARCHAR2,
204 X_enabled_flag VARCHAR2,
205 X_org_id NUMBER,
206 X_last_updated_by NUMBER,
207 X_last_update_date DATE,
208 X_last_update_login NUMBER,
209 X_creation_date DATE,
210 X_created_by NUMBER,
211 X_calling_sequence IN VARCHAR2) IS
212
213 CURSOR C IS
214 SELECT CUS_CLASS_ID,
215 ADDRESS_ID,
216 TAX_ATTR_CLASS_CODE,
217 TAX_CATEGORY_ID,
218 TAX_ATTRIBUTE_NAME,
219 TAX_ATTRIBUTE_VALUE,
220 ENABLED_FLAG,
221 ORG_ID,
222 LAST_UPDATE_DATE,
223 LAST_UPDATED_BY,
224 LAST_UPDATE_LOGIN,
225 CREATION_DATE,
226 CREATED_BY
227 FROM JL_ZZ_AR_TX_CUS_CLS
228 WHERE cus_class_id = X_cus_class_id
229 FOR UPDATE of tax_attr_class_code,
230 tax_category_id,
231 tax_attribute_name,
232 ORG_ID
233 NOWAIT;
234
235 Recinfo C%ROWTYPE;
236
237 current_calling_sequence VARCHAR2(2000);
238 debug_info VARCHAR2(100);
239
240 BEGIN
241
242 current_calling_sequence := 'JL_ZZ_AR_TX_CUS_CLS_PKG.LOCK_ROW<-' ||
243 X_calling_sequence;
244 debug_info := 'Open cursor C';
245 OPEN C;
246 debug_info := 'Fetch cursor C';
247 FETCH C INTO Recinfo;
248
249 IF (C%NOTFOUND) THEN
250 debug_info := 'Close cursor C - DATA NOTFOUND';
251 CLOSE C;
252 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_DELETED');
253 APP_EXCEPTION.Raise_Exception;
254 END IF;
255
256 debug_info := 'Close cursor C';
257 CLOSE C;
258 IF ((Recinfo.cus_class_id = X_cus_class_id) AND
259 (Recinfo.tax_attr_class_code = X_tax_attr_class_code) AND
260 (Recinfo.tax_category_id = X_tax_category_id) AND
261 (Recinfo.tax_attribute_name = X_tax_attribute_name) AND
262 (Recinfo.tax_attribute_value = X_tax_attribute_value) AND
263 (Recinfo.enabled_flag = X_enabled_flag) AND
264 ((Recinfo.org_id = X_org_id) OR
265 ((Recinfo.org_id IS NULL) AND
266 (X_org_id IS NULL))) AND
267 (Recinfo.last_updated_by = X_last_updated_by) AND
268 (Recinfo.last_update_date = X_last_update_date) AND
269 ((Recinfo.last_update_login = X_last_update_login) OR
270 ((Recinfo.last_update_login IS NULL) AND
271 (X_last_update_login IS NULL))) AND
272 ((Recinfo.creation_date = X_creation_date) OR
273 ((Recinfo.creation_date IS NULL) AND
274 (X_creation_date IS NULL))) AND
275 ((Recinfo.created_by = X_created_by) OR
276 ((Recinfo.created_by IS NULL) AND
277 (X_created_by IS NULL)))) THEN
278 return;
279 ELSE
280 FND_MESSAGE.Set_Name('FND', 'FORM_RECORD_CHANGED');
281 APP_EXCEPTION.Raise_Exception;
282 END IF;
283 EXCEPTION
284 WHEN OTHERS THEN
285 IF (SQLCODE <> -20001) THEN
286 IF (SQLCODE = -54) THEN
287 FND_MESSAGE.SET_NAME('SQLAP','AP_RESOURCE_BUSY');
288 ELSE
289 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
290 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
291 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
292 FND_MESSAGE.SET_TOKEN('PARAMETERS',
293 ' tax_attr_class_code = ' || X_tax_attr_class_code ||
294 ' tax_category_id = ' || X_tax_category_id ||
295 ' tax_attribute_name = ' || X_tax_attribute_name );
296 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
297 END IF;
298 END IF;
299 APP_EXCEPTION.RAISE_EXCEPTION;
300 END Lock_Row;
301
302 PROCEDURE Check_Unique
303 (X_rowid VARCHAR2,
304 X_address_id NUMBER,
305 X_tax_attr_class_code VARCHAR2,
306 X_tax_category_id NUMBER,
307 X_tax_attribute_name VARCHAR2,
308 X_org_id NUMBER,
309 X_calling_sequence IN VARCHAR2) IS
310
311 l_dummy NUMBER;
312 current_calling_sequence VARCHAR2(2000);
313 debug_info VARCHAR2(100);
314
315 BEGIN
316 -- Update the calling sequence
317 --
318 current_calling_sequence := 'JL_ZZ_AR_TX_CUS_CLS_PKG.CHECK_UNIQUE<-' ||
319 X_calling_sequence;
320 SELECT COUNT(1)
321 INTO l_dummy
322 FROM JL_ZZ_AR_TX_CUS_CLS
323 WHERE address_id = X_address_id
324 AND tax_attr_class_code = X_tax_attr_class_code
325 AND tax_category_id = X_tax_category_id
326 AND tax_attribute_name = X_tax_attribute_name
327 AND org_id = X_org_id
328 AND ((X_rowid IS NULL) OR (rowid <> X_rowid));
329
330 IF (l_dummy >=1) THEN
331 FND_MESSAGE.SET_NAME('SQLAP','AP_DEBUG');
332 FND_MESSAGE.SET_TOKEN('ERROR',SQLERRM);
333 FND_MESSAGE.SET_TOKEN('CALLING_SEQUENCE',current_calling_sequence);
334 FND_MESSAGE.SET_TOKEN('PARAMETERS',
335 ' tax_attr_class_code = ' || X_tax_attr_class_code ||
336 ' tax_category_id = ' || X_tax_category_id ||
337 ' tax_attribute_name = ' || X_tax_attribute_name );
338 FND_MESSAGE.SET_TOKEN('DEBUG_INFO',debug_info);
339 APP_EXCEPTION.RAISE_EXCEPTION;
340 END IF;
341 END Check_Unique;
342
343 END JL_ZZ_AR_TX_CUS_CLS_PKG;