[Home] [Help]
PACKAGE BODY: APPS.ZX_ACCT_TAX_CLS_DEFS_PKG
Source
1 PACKAGE BODY zx_acct_tax_cls_defs_pkg AS
2 /* $Header: zxgltcdb.pls 120.1 2005/07/06 12:04:11 mparihar noship $ */
3
4 ---
5 --- PRIVATE FUNCTIONS
6 ---
7
8 --
9 -- Procedure
10 -- select_row
11 -- Purpose
12 -- Gets the row from zx_acct_tax_cls_defs_all associated with
13 -- the given ledger id and organization.
14 -- History
15 --
16 -- Arguments
17 -- recinfo A row from zx_acct_tx_cls_defs_all
18 -- Example
19 -- zx_acct_tax_cls_defs_pkg.select_row(recinfo);
20 -- Notes
21 --
22 PROCEDURE select_row( recinfo IN OUT NOCOPY zx_acct_tx_cls_defs_all%ROWTYPE ) IS
23 BEGIN
24 SELECT *
25 INTO recinfo
26 FROM zx_acct_tx_cls_defs_all
27 WHERE ledger_id = recinfo.ledger_id
28 AND org_id = recinfo.org_id;
29 EXCEPTION
30 WHEN app_exceptions.application_exception THEN
31 RAISE;
32 WHEN OTHERS THEN
33 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
34 fnd_message.set_token('PROCEDURE',
35 'zx_acct_tax_cls_defs_all.select_row');
36 RAISE;
37 END select_row;
38
39
40 --
41 -- PUBLIC FUNCTIONS
42 --
43
44 PROCEDURE select_columns(
45 x_ledger_id NUMBER,
46 x_org_id NUMBER,
47 x_tax_class IN OUT NOCOPY VARCHAr2,
48 x_tax_classification_code IN OUT NOCOPY VARCHAr2) IS
49
50 recinfo zx_acct_tx_cls_defs_all%ROWTYPE;
51
52 BEGIN
53 recinfo.ledger_id := x_ledger_id;
54 recinfo.org_id := x_org_id;
55 select_row( recinfo );
56 x_tax_class := recinfo.tax_class;
57 x_tax_classification_code := recinfo.tax_classification_code;
58
59 EXCEPTION
60 WHEN app_exceptions.application_exception THEN
61 RAISE;
62 WHEN OTHERS THEN
63 fnd_message.set_name('SQLGL', 'GL_UNHANDLED_EXCEPTION');
64 fnd_message.set_token('PROCEDURE',
65 'zx_acct_tax_cls_defs_all.select_columns');
66 RAISE;
67 END select_columns;
68
69
70 PROCEDURE duplicate_tax_class_code ( x_ledger_id NUMBER,
71 x_org_id NUMBER,
72 x_rowid VARCHAR2 ) IS
73 x_total NUMBER;
74 BEGIN
75 SELECT count(*)
76 INTO x_total
77 FROM zx_acct_tx_cls_defs_all
78 WHERE ledger_id = x_ledger_id
79 AND org_id = x_org_id
80 AND ( x_rowid is null OR rowid <> x_rowid );
81
82 IF (x_total <> 0) THEN
83 -- A already record exists for this ledger id and org id
84 fnd_message.set_name('SQLGL', 'GL_STAX_DUPLICATE_RECORD');
85 app_exception.raise_exception;
86 END IF;
87
88 END duplicate_tax_class_code;
89
90
91 PROCEDURE org_name ( x_org_id NUMBER,
92 x_org_name IN OUT NOCOPY VARCHAR2 ) IS
93 org_cursor NUMBER;
94 row_count NUMBER;
95 BEGIN
96
97 org_cursor := dbms_sql.open_cursor;
98 dbms_sql.parse(org_cursor,
99 'SELECT name ' ||
100 'FROM hr_operating_units ' ||
101 'WHERE organization_id = :org_id ',
102 dbms_sql.v7);
103 dbms_sql.define_column(org_cursor, 1, x_org_name, 240);
104 dbms_sql.bind_variable(org_cursor, ':org_id', x_org_id);
105
106 row_count := dbms_sql.execute_and_fetch(org_cursor);
107 IF (row_count = 0) THEN
108 RAISE NO_DATA_FOUND;
109 END IF;
110
111 dbms_sql.column_value(org_cursor, 1, x_org_name);
112 dbms_sql.close_cursor(org_cursor);
113 END org_name;
114
115
116 PROCEDURE insert_row(
117 X_LEDGER_ID NUMBER,
118 X_ORG_ID NUMBER,
119 X_ACCOUNT_SEGMENT_VALUE VARCHAR2,
120 X_TAX_CLASS VARCHAR2,
121 X_TAX_CLASSIFICATION_CODE VARCHAR2,
122 X_ALLOW_TAX_CODE_OVERRIDE_FLAG VARCHAR2,
123 X_RECORD_TYPE_CODE VARCHAR2,
124 X_CREATION_DATE DATE,
125 X_CREATED_BY NUMBER,
126 X_LAST_UPDATED_BY NUMBER,
127 X_LAST_UPDATE_DATE DATE,
128 X_LAST_UPDATE_LOGIN NUMBER,
129 X_ATTRIBUTE_CATEGORY VARCHAR2,
130 X_ATTRIBUTE1 VARCHAR2,
131 X_ATTRIBUTE2 VARCHAR2,
132 X_ATTRIBUTE3 VARCHAR2,
133 X_ATTRIBUTE4 VARCHAR2,
134 X_ATTRIBUTE5 VARCHAR2,
135 X_ATTRIBUTE6 VARCHAR2,
136 X_ATTRIBUTE7 VARCHAR2,
137 X_ATTRIBUTE8 VARCHAR2,
138 X_ATTRIBUTE9 VARCHAR2,
139 X_ATTRIBUTE10 VARCHAR2,
140 X_ATTRIBUTE11 VARCHAR2,
141 X_ATTRIBUTE12 VARCHAR2,
142 X_ATTRIBUTE13 VARCHAR2,
143 X_ATTRIBUTE14 VARCHAR2,
144 X_ATTRIBUTE15 VARCHAR2) IS
145 CURSOR C IS SELECT ROWID FROM ZX_ACCT_TX_CLS_DEFS_ALL
146 WHERE LEDGER_ID = X_LEDGER_ID
147 AND ORG_ID = X_ORG_ID;
148 dummy VARCHAR2(30);
149 BEGIN
150 INSERT INTO ZX_ACCT_TX_CLS_DEFS_ALL
151 (ledger_id,
152 org_id,
153 account_segment_value,
154 tax_class,
155 tax_classification_code,
156 allow_tax_code_override_flag,
157 record_type_code,
158 creation_date,
159 created_by,
160 last_updated_by,
161 last_update_date,
162 last_update_login,
163 attribute_category,
164 attribute1,
165 attribute2,
166 attribute3,
167 attribute4,
168 attribute5,
169 attribute6,
170 attribute7,
171 attribute8,
172 attribute9,
173 attribute10,
174 attribute11,
175 attribute12,
176 attribute13,
177 attribute14,
178 attribute15)
179 VALUES
180 (X_LEDGER_ID,
181 X_ORG_ID,
182 X_ACCOUNT_SEGMENT_VALUE,
183 X_TAX_CLASS,
184 X_TAX_CLASSIFICATION_CODE,
185 X_ALLOW_TAX_CODE_OVERRIDE_FLAG,
186 X_RECORD_TYPE_CODE,
187 X_CREATION_DATE,
188 X_CREATED_BY,
189 X_LAST_UPDATED_BY,
190 X_LAST_UPDATE_DATE,
191 X_LAST_UPDATE_LOGIN,
192 X_ATTRIBUTE_CATEGORY,
193 X_ATTRIBUTE1,
194 X_ATTRIBUTE2,
195 X_ATTRIBUTE3,
196 X_ATTRIBUTE4,
197 X_ATTRIBUTE5,
198 X_ATTRIBUTE6,
199 X_ATTRIBUTE7,
200 X_ATTRIBUTE8,
201 X_ATTRIBUTE9,
202 X_ATTRIBUTE10,
203 X_ATTRIBUTE11,
204 X_ATTRIBUTE12,
205 X_ATTRIBUTE13,
206 X_ATTRIBUTE14,
207 X_ATTRIBUTE15);
208
209 OPEN C;
210 FETCH C INTO dummy;
211 IF (C%NOTFOUND) THEN
212 CLOSE C;
213 RAISE NO_DATA_FOUND;
214 END IF;
215 CLOSE C;
216
217 END insert_row;
218
219
220 PROCEDURE update_row(
221 X_LEDGER_ID NUMBER,
222 X_ORG_ID NUMBER,
223 X_ACCOUNT_SEGMENT_VALUE VARCHAR2,
224 X_TAX_CLASS VARCHAR2,
225 X_TAX_CLASSIFICATION_CODE VARCHAR2,
226 X_ALLOW_TAX_CODE_OVERRIDE_FLAG VARCHAR2,
227 X_RECORD_TYPE_CODE VARCHAR2,
228 X_CREATION_DATE DATE,
229 X_CREATED_BY NUMBER,
230 X_LAST_UPDATED_BY NUMBER,
231 X_LAST_UPDATE_DATE DATE,
232 X_LAST_UPDATE_LOGIN NUMBER,
233 X_ATTRIBUTE_CATEGORY VARCHAR2,
234 X_ATTRIBUTE1 VARCHAR2,
235 X_ATTRIBUTE2 VARCHAR2,
236 X_ATTRIBUTE3 VARCHAR2,
237 X_ATTRIBUTE4 VARCHAR2,
238 X_ATTRIBUTE5 VARCHAR2,
239 X_ATTRIBUTE6 VARCHAR2,
240 X_ATTRIBUTE7 VARCHAR2,
241 X_ATTRIBUTE8 VARCHAR2,
242 X_ATTRIBUTE9 VARCHAR2,
243 X_ATTRIBUTE10 VARCHAR2,
244 X_ATTRIBUTE11 VARCHAR2,
245 X_ATTRIBUTE12 VARCHAR2,
246 X_ATTRIBUTE13 VARCHAR2,
247 X_ATTRIBUTE14 VARCHAR2,
248 X_ATTRIBUTE15 VARCHAR2,
249 X_ACCOUNT_SEGMENT_VALUE_ORIG VARCHAR2,
250 X_TAX_CLASS_ORIG VARCHAR2) IS
251 BEGIN
252 UPDATE ZX_ACCT_TX_CLS_DEFS_ALL
253 SET ledger_id = X_LEDGER_ID,
254 org_id = X_ORG_ID,
255 account_segment_value = X_ACCOUNT_SEGMENT_VALUE,
256 tax_class = X_TAX_CLASS,
257 tax_classification_code = X_TAX_CLASSIFICATION_CODE,
258 allow_tax_code_override_flag = X_ALLOW_TAX_CODE_OVERRIDE_FLAG,
259 record_type_code = X_RECORD_TYPE_CODE,
260 creation_date = X_CREATION_DATE,
261 created_by = X_CREATED_BY,
262 last_updated_by = X_LAST_UPDATED_BY,
263 last_update_date = X_LAST_UPDATE_DATE,
264 last_update_login = X_LAST_UPDATE_LOGIN,
265 attribute_category = X_ATTRIBUTE_CATEGORY,
266 attribute1 = X_ATTRIBUTE1,
267 attribute2 = X_ATTRIBUTE2,
268 attribute3 = X_ATTRIBUTE3,
269 attribute4 = X_ATTRIBUTE4,
270 attribute5 = X_ATTRIBUTE5,
271 attribute6 = X_ATTRIBUTE6,
272 attribute7 = X_ATTRIBUTE7,
273 attribute8 = X_ATTRIBUTE8,
274 attribute9 = X_ATTRIBUTE9,
275 attribute10 = X_ATTRIBUTE10,
276 attribute11 = X_ATTRIBUTE11,
277 attribute12 = X_ATTRIBUTE12,
278 attribute13 = X_ATTRIBUTE13,
279 attribute14 = X_ATTRIBUTE14,
280 attribute15 = X_ATTRIBUTE15
281 WHERE ledger_id = X_LEDGER_ID
282 AND org_id = X_ORG_ID
283 AND account_segment_value = X_ACCOUNT_SEGMENT_VALUE_ORIG
284 AND tax_class = X_TAX_CLASS_ORIG;
285
286 IF (SQL%NOTFOUND) THEN
287 RAISE NO_DATA_FOUND;
288 END IF;
289
290 END update_row;
291
292
293 PROCEDURE lock_row(
294 X_LEDGER_ID NUMBER,
295 X_ORG_ID NUMBER,
296 X_ACCOUNT_SEGMENT_VALUE VARCHAR2,
297 X_TAX_CLASS VARCHAR2,
298 X_TAX_CLASSIFICATION_CODE VARCHAR2,
299 X_ALLOW_TAX_CODE_OVERRIDE_FLAG VARCHAR2,
300 X_RECORD_TYPE_CODE VARCHAR2,
301 X_CREATION_DATE DATE,
302 X_CREATED_BY NUMBER,
303 X_LAST_UPDATED_BY NUMBER,
304 X_LAST_UPDATE_DATE DATE,
305 X_LAST_UPDATE_LOGIN NUMBER,
306 X_ATTRIBUTE_CATEGORY VARCHAR2,
307 X_ATTRIBUTE1 VARCHAR2,
308 X_ATTRIBUTE2 VARCHAR2,
309 X_ATTRIBUTE3 VARCHAR2,
310 X_ATTRIBUTE4 VARCHAR2,
311 X_ATTRIBUTE5 VARCHAR2,
312 X_ATTRIBUTE6 VARCHAR2,
313 X_ATTRIBUTE7 VARCHAR2,
314 X_ATTRIBUTE8 VARCHAR2,
315 X_ATTRIBUTE9 VARCHAR2,
316 X_ATTRIBUTE10 VARCHAR2,
317 X_ATTRIBUTE11 VARCHAR2,
318 X_ATTRIBUTE12 VARCHAR2,
319 X_ATTRIBUTE13 VARCHAR2,
320 X_ATTRIBUTE14 VARCHAR2,
321 X_ATTRIBUTE15 VARCHAR2) IS
322 CURSOR C IS
323 SELECT *
324 FROM ZX_ACCT_TX_CLS_DEFS_ALL
325 WHERE ledger_id = X_LEDGER_ID
326 AND org_id = X_ORG_ID
327 AND account_segment_value = X_ACCOUNT_SEGMENT_VALUE
328 AND tax_class = X_TAX_CLASS
329 FOR UPDATE OF tax_classification_code NOWAIT;
333 FETCH C INTO Recinfo;
330 Recinfo C%ROWTYPE;
331 BEGIN
332 OPEN C;
334 IF (C%NOTFOUND) THEN
335 CLOSE C;
336 FND_MESSAGE.set_name('FND', 'FORM_RECORD_DELETED');
337 APP_EXCEPTION.raise_exception;
338 END IF;
339 CLOSE C;
340
341 IF ( (Recinfo.ledger_id = X_LEDGER_ID)
342 AND ( (Recinfo.org_id = X_ORG_ID)
343 OR ( (Recinfo.org_id IS NULL)
344 AND (X_ORG_ID IS NULL)))
345 AND (Recinfo.account_segment_value = X_ACCOUNT_SEGMENT_VALUE)
346 AND ( (Recinfo.tax_class = X_TAX_CLASS)
347 OR ( (Recinfo.tax_class IS NULL)
348 AND (X_TAX_CLASS IS NULL)))
349 AND ( (Recinfo.tax_classification_code = X_TAX_CLASSIFICATION_CODE)
350 OR ( (Recinfo.tax_classification_code IS NULL)
351 AND (X_TAX_CLASSIFICATION_CODE IS NULL)))
352 AND ( (Recinfo.allow_tax_code_override_flag = X_ALLOW_TAX_CODE_OVERRIDE_FLAG)
353 OR ( (Recinfo.allow_tax_code_override_flag IS NULL)
354 AND (X_ALLOW_TAX_CODE_OVERRIDE_FLAG IS NULL)))
355 AND ( (Recinfo.record_type_code = X_RECORD_TYPE_CODE)
356 OR ( (Recinfo.record_type_code IS NULL)
357 AND (X_RECORD_TYPE_CODE IS NULL)))
358 AND ( (Recinfo.attribute_category = X_ATTRIBUTE_CATEGORY)
359 OR ( (Recinfo.attribute_category IS NULL)
360 AND (X_ATTRIBUTE_CATEGORY IS NULL)))
361 AND ( (Recinfo.attribute1 = X_ATTRIBUTE1)
362 OR ( (Recinfo.attribute1 IS NULL)
363 AND (X_ATTRIBUTE1 IS NULL)))
364 AND ( (Recinfo.attribute2 = X_ATTRIBUTE2)
365 OR ( (Recinfo.attribute2 IS NULL)
366 AND (X_ATTRIBUTE2 IS NULL)))
367 AND ( (Recinfo.attribute3 = X_ATTRIBUTE3)
368 OR ( (Recinfo.attribute3 IS NULL)
369 AND (X_ATTRIBUTE3 IS NULL)))
370 AND ( (Recinfo.attribute4 = X_ATTRIBUTE4)
371 OR ( (Recinfo.attribute4 IS NULL)
372 AND (X_ATTRIBUTE4 IS NULL)))
373 AND ( (Recinfo.attribute5 = X_ATTRIBUTE5)
374 OR ( (Recinfo.attribute5 IS NULL)
375 AND (X_ATTRIBUTE5 IS NULL)))
376 AND ( (Recinfo.attribute6 = X_ATTRIBUTE6)
377 OR ( (Recinfo.attribute6 IS NULL)
378 AND (X_ATTRIBUTE6 IS NULL)))
379 AND ( (Recinfo.attribute7 = X_ATTRIBUTE7)
380 OR ( (Recinfo.attribute7 IS NULL)
381 AND (X_ATTRIBUTE7 IS NULL)))
382 AND ( (Recinfo.attribute8 = X_ATTRIBUTE8)
383 OR ( (Recinfo.attribute8 IS NULL)
384 AND (X_ATTRIBUTE8 IS NULL)))
385 AND ( (Recinfo.attribute9 = X_ATTRIBUTE9)
386 OR ( (Recinfo.attribute9 IS NULL)
387 AND (X_ATTRIBUTE9 IS NULL)))
388 AND ( (Recinfo.attribute10 = X_ATTRIBUTE10)
389 OR ( (Recinfo.attribute10 IS NULL)
390 AND (X_ATTRIBUTE10 IS NULL)))
391 AND ( (Recinfo.attribute11 = X_ATTRIBUTE11)
392 OR ( (Recinfo.attribute11 IS NULL)
393 AND (X_ATTRIBUTE11 IS NULL)))
394 AND ( (Recinfo.attribute12 = X_ATTRIBUTE12)
395 OR ( (Recinfo.attribute12 IS NULL)
399 AND (X_ATTRIBUTE13 IS NULL)))
396 AND (X_ATTRIBUTE12 IS NULL)))
397 AND ( (Recinfo.attribute13 = X_ATTRIBUTE13)
398 OR ( (Recinfo.attribute13 IS NULL)
400 AND ( (Recinfo.attribute14 = X_ATTRIBUTE14)
401 OR ( (Recinfo.attribute14 IS NULL)
402 AND (X_ATTRIBUTE14 IS NULL)))
403 AND ( (Recinfo.attribute15 = X_ATTRIBUTE15)
404 OR ( (Recinfo.attribute15 IS NULL)
405 AND (X_ATTRIBUTE15 IS NULL)))
406 ) THEN
407 RETURN;
408 ELSE
409 FND_MESSAGE.set_name('FND', 'FORM_RECORD_CHANGED');
410 APP_EXCEPTION.raise_exception;
411 END IF;
412
413 END lock_row;
414
415
416 END zx_acct_tax_cls_defs_pkg;