[Home] [Help]
PACKAGE BODY: APPS.JA_AU_CCID_PKG
Source
1 PACKAGE BODY JA_AU_CCID_PKG as
2 /* $Header: jaaupccb.pls 115.0 2003/01/08 23:35:36 thwon ship $ */
3
4
5 -- ** Declare global variables
6 l_error_msg po_interface_errors.error_message%TYPE ;
7 l_error_flag number;
8 l_index NATURAL;
9
10
11 -- ** Declare exceptions
12 l_incorrect_table EXCEPTION;
13 l_null_segment EXCEPTION;
14 l_invalid_segment EXCEPTION;
15 l_no_rows_updated EXCEPTION;
16 l_null_exp_acct EXCEPTION;
17 l_update_failure EXCEPTION;
18 l_no_data_found EXCEPTION;
19
20
21 PROCEDURE JA_AU_AUTOACCOUNTING
22 (x_org_id IN
23 mtl_material_transactions.organization_id%TYPE,
24 x_subinv IN
25 mtl_material_transactions.subinventory_code%TYPE,
26 x_item_id IN
27 mtl_material_transactions.inventory_item_id%TYPE,
28 l_transaction_id IN
29 po_requisitions_interface.transaction_id%TYPE)
30
31 IS
32
33 l_chart_of_accts_id org_organization_definitions.organization_id%TYPE;
34 l_set_of_books_id org_organization_definitions.set_of_books_id%TYPE;
35 l_subinv_ccid mtl_secondary_inventories.expense_account%TYPE;
36 l_item_ccid mtl_system_items.expense_account%TYPE;
37 l_table_name JA_AU_ACCT_DEFAULT_SEGS.table_name%TYPE;
38 l_constant JA_AU_ACCT_DEFAULT_SEGS.constant%TYPE;
39 l_segment JA_AU_ACCT_DEFAULT_SEGS.segment%TYPE;
40 l_segvalues fnd_flex_ext.segmentarray;
41 l_seglength natural := 0;
42 l_segnumber natural := 0;
43 l_ccid gl_code_combinations.code_combination_id%TYPE;
44 l_num_segs number;
45 l_test_ccid boolean;
46
47
48 CURSOR l_autoaccount_defns IS
49 SELECT nvl(upper(s.table_name), '!~') TABLE_NAME,
50 nvl(s.constant, '!~') CONSTANT,
51 s.segment
52 FROM JA_AU_ACCT_DEFAULT_SEGS s, ja_au_account_defaults d
53 WHERE s.gl_default_id = d.gl_default_id
54 AND d.set_of_books_id = l_set_of_books_id
55 ORDER BY d.type,s.segment_num ;
56
57 BEGIN
58
59 l_error_flag := 0;
60 l_num_segs := 0;
61 /* Initialise l_segvalues and l_ccid */
62 FOR l_index IN 1..30 LOOP
63 l_segvalues(l_index) := NULL;
64 END LOOP;
65 l_ccid := 0;
66 l_chart_of_accts_id := 0;
67 l_set_of_books_id := 0;
68 l_subinv_ccid := 0;
69 l_item_ccid := 0;
70
71
72 /* Obtain chart_of_accounts_id and set_of_books from
73 org_organization_definitions*/
74 JA_AU_get_coa_sob( x_org_id,
75 l_chart_of_accts_id,
76 l_set_of_books_id);
77
78 IF l_error_flag = -1 THEN
79 GOTO end_processing;
80 END IF;
81 /* Get the subinventory and item expense accounts */
82 JA_AU_get_repln_exp_accts(x_org_id,
83 x_subinv,
84 x_item_id,
85 l_subinv_ccid,
86 l_item_ccid);
87
88 IF l_error_flag = -1 THEN
89 GOTO end_processing;
90 END IF;
91
92 /* Fetch the AutoAccounting definitions a row at a time and retrieve
93 the segment value from GL_CODE_COMBINATIONS for the specified
94 segment */
95 OPEN l_autoaccount_defns;
96
97 LOOP
98 FETCH l_autoaccount_defns
99 INTO l_table_name,
100 l_constant,
101 l_segment ;
102
103
104 EXIT WHEN l_autoaccount_defns%NOTFOUND;
105
106 l_num_segs := l_num_segs + 1;
107
108 l_seglength := LENGTH(l_segment);
109 l_segnumber := TO_NUMBER(SUBSTR(l_segment,8,l_seglength-7));
110
111 l_segvalues(l_segnumber) := JA_AU_get_segment_value(l_table_name,
112 l_constant,
113 l_segment,
114 l_subinv_ccid,
115 l_item_ccid);
116
117 IF l_error_flag = -1 THEN
118 GOTO end_processing;
119 END IF;
120
121 END LOOP;
122
123 CLOSE l_autoaccount_defns;
124
125 l_test_ccid := fnd_flex_ext.get_combination_id('SQLGL',
126 'GL#',
127 l_chart_of_accts_id,
128 sysdate,
129 l_num_segs,
130 l_segvalues,
131 l_ccid);
132
133 IF (l_test_ccid) THEN
134 NULL;
135 ELSE
136 l_error_msg := 'AUTOGL ERROR - Could not obtain or create CODE_COMBINATION_ID.';
137 goto end_processing;
138 END IF;
139
140 JA_AU_update_mtltrxacct( l_transaction_id,
141 l_ccid );
142 <<end_processing>>
143 commit;
144
145 EXCEPTION
146 WHEN OTHERS THEN
147 null;
148 END JA_AU_AUTOACCOUNTING ;
149
150
151 -- *
152 -- ** Create JA_AU_GET_COA_SOB procedure
153 -- *
154
155 PROCEDURE JA_AU_GET_COA_SOB
156 (x_org_id IN
157 org_organization_definitions.organization_id%TYPE,
158 x_chart_of_accts_id OUT
159 org_organization_definitions.chart_of_accounts_id%TYPE,
160 x_set_of_books_id OUT
161 org_organization_definitions.set_of_books_id%TYPE)
162 IS
166 INTO x_chart_of_accts_id, x_set_of_books_id
163 BEGIN
164
165 SELECT chart_of_accounts_id, set_of_books_id
167 FROM org_organization_definitions
168 WHERE organization_id = x_org_id
169 AND nvl(disable_date, sysdate+1) > sysdate ;
170
171 EXCEPTION
172 WHEN NO_DATA_FOUND THEN
173 l_error_msg := 'AUTOGL ERROR - Could not retrieve chart_of_accounts_id and/or set_of_books_id.';
174 l_error_flag := -1;
175 WHEN OTHERS THEN
176 l_error_flag := -1;
177
178 END JA_AU_GET_COA_SOB;
179
180
181 -- *
182 -- ** Create JA_AU_GET_REPLN_EXP_ACCTS procedure
183 -- *
184
185 PROCEDURE JA_AU_GET_REPLN_EXP_ACCTS
186 (x_org_id IN
187 org_organization_definitions.organization_id%TYPE,
188 x_subinv IN
189 mtl_secondary_inventories.secondary_inventory_name%TYPE,
190 x_item_id IN
191 mtl_system_items.inventory_item_id%TYPE,
192 x_subinv_ccid IN OUT
193 mtl_secondary_inventories.expense_account%TYPE,
194 x_item_ccid IN OUT
195 mtl_system_items.expense_account%TYPE)
196 IS
197 BEGIN
198
199 l_error_msg := 'AUTOGL ERROR - Could not retrieve subinventory expense_account';
200
201 SELECT nvl(expense_account, -1)
202 INTO x_subinv_ccid
203 FROM mtl_secondary_inventories
204 WHERE organization_id = x_org_id
205 AND secondary_inventory_name = x_subinv ;
206
207 IF x_subinv_ccid = -1 THEN
208 l_error_msg := 'AUTOGL ERROR - Subinventory expense_account was NULL';
209 RAISE l_null_exp_acct;
210 END IF;
211
212 l_error_msg := 'AUTOGL ERROR - Could not retrieve item expense_account';
213
214 SELECT nvl(expense_account, -1)
215 INTO x_item_ccid
216 FROM mtl_system_items
217 WHERE organization_id = x_org_id
218 AND inventory_item_id = x_item_id ;
219
220 IF x_item_ccid = -1 THEN
221 l_error_msg := 'AUTOGL ERROR - Item expense_account was NULL';
222 RAISE l_null_exp_acct;
223 END IF;
224
225 l_error_msg := null;
226
227 EXCEPTION
228 WHEN NO_DATA_FOUND THEN
229 l_error_flag := -1;
230 WHEN l_null_exp_acct THEN
231 l_error_flag := -1;
232 WHEN OTHERS THEN
233 l_error_flag := -1;
234
235 END JA_AU_GET_REPLN_EXP_ACCTS;
236
237
238 -- *
239 -- ** Create JA_AU_GET_SEGMENT_VALUE function
240 -- *
241
242 FUNCTION JA_AU_GET_SEGMENT_VALUE
243 (
244 x_table_name IN
245 JA_AU_ACCT_DEFAULT_SEGS.table_name%TYPE,
246 x_constant IN
247 JA_AU_ACCT_DEFAULT_SEGS.constant%TYPE,
248 x_segment IN
249 JA_AU_ACCT_DEFAULT_SEGS.segment%TYPE,
250 x_subinv_ccid IN
251 mtl_secondary_inventories.expense_account%TYPE,
252 x_item_ccid IN
253 mtl_system_items.expense_account%TYPE)
254 RETURN gl_code_combinations.segment1%TYPE IS
255
256 l_value gl_code_combinations.segment1%TYPE;
257
258 BEGIN
259
260 IF SUBSTR(x_constant,1,2) = '!~' THEN /* Not a constant */
261 IF x_table_name = 'MTL_SECONDARY_INVENTORIES' THEN
262 l_value := JA_AU_get_value(x_subinv_ccid,
263 x_segment);
264 ELSIF x_table_name = 'MTL_SYSTEM_ITEMS' THEN
265 l_value := JA_AU_get_value(x_item_ccid,
269 RAISE l_incorrect_table;
266 x_segment);
267 ELSE
268 l_value := '0';
270 END IF;
271 ELSE
272 l_value := RTRIM(SUBSTR(x_constant,1,25));
273 END IF;
274
275 return(l_value);
276
277 EXCEPTION
278 WHEN l_incorrect_table THEN
279 l_error_msg := 'AUTOGL ERROR - Incorrect AutoAccounting setup - Invalid Table.';
280 l_error_flag := -1;
281 return(l_value);
282 WHEN OTHERS THEN
283 l_error_flag := -1;
284 return(l_value);
285
286 END JA_AU_GET_SEGMENT_VALUE;
287
288
289 -- *
290 -- ** Create JA_AU_GET_VALUE function
291 -- *
292
293 FUNCTION JA_AU_GET_VALUE
294 (x_ccid IN
295 gl_code_combinations.code_combination_id%TYPE,
296 x_segment IN
297 gl_code_combinations.segment1%TYPE)
298 RETURN gl_code_combinations.segment1%TYPE IS
299
300 l_value gl_code_combinations.segment1%TYPE;
301
302 BEGIN
303
304 IF SUBSTR(x_segment,1,9) = 'SEGMENT30' THEN
305 SELECT nvl(segment30,'!@')
306 INTO l_value
307 FROM gl_code_combinations
308 WHERE code_combination_id = x_ccid ;
309 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT29' THEN
310 SELECT nvl(segment29,'!@')
311 INTO l_value
312 FROM gl_code_combinations
313 WHERE code_combination_id = x_ccid ;
314 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT28' THEN
315 SELECT nvl(segment28,'!@')
316 INTO l_value
317 FROM gl_code_combinations
318 WHERE code_combination_id = x_ccid ;
319 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT27' THEN
320 SELECT nvl(segment27,'!@')
321 INTO l_value
322 FROM gl_code_combinations
323 WHERE code_combination_id = x_ccid ;
324 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT26' THEN
325 SELECT nvl(segment26,'!@')
326 INTO l_value
327 FROM gl_code_combinations
328 WHERE code_combination_id = x_ccid ;
332 FROM gl_code_combinations
329 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT25' THEN
330 SELECT nvl(segment25,'!@')
331 INTO l_value
333 WHERE code_combination_id = x_ccid ;
334 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT24' THEN
335 SELECT nvl(segment24,'!@')
336 INTO l_value
337 FROM gl_code_combinations
338 WHERE code_combination_id = x_ccid ;
339 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT23' THEN
340 SELECT nvl(segment23,'!@')
341 INTO l_value
342 FROM gl_code_combinations
343 WHERE code_combination_id = x_ccid ;
344 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT22' THEN
345 SELECT nvl(segment22,'!@')
346 INTO l_value
350 SELECT nvl(segment21,'!@')
347 FROM gl_code_combinations
348 WHERE code_combination_id = x_ccid ;
349 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT21' THEN
351 INTO l_value
352 FROM gl_code_combinations
353 WHERE code_combination_id = x_ccid ;
354 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT20' THEN
355 SELECT nvl(segment20,'!@')
356 INTO l_value
357 FROM gl_code_combinations
358 WHERE code_combination_id = x_ccid ;
359 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT19' THEN
360 SELECT nvl(segment19,'!@')
361 INTO l_value
362 FROM gl_code_combinations
363 WHERE code_combination_id = x_ccid ;
364 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT18' THEN
365 SELECT nvl(segment18,'!@')
366 INTO l_value
367 FROM gl_code_combinations
368 WHERE code_combination_id = x_ccid ;
369 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT17' THEN
370 SELECT nvl(segment17,'!@')
371 INTO l_value
372 FROM gl_code_combinations
373 WHERE code_combination_id = x_ccid ;
374 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT16' THEN
375 SELECT nvl(segment16,'!@')
376 INTO l_value
377 FROM gl_code_combinations
378 WHERE code_combination_id = x_ccid ;
379 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT15' THEN
380 SELECT nvl(segment15,'!@')
381 INTO l_value
382 FROM gl_code_combinations
383 WHERE code_combination_id = x_ccid ;
384 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT14' THEN
385 SELECT nvl(segment14,'!@')
386 INTO l_value
387 FROM gl_code_combinations
388 WHERE code_combination_id = x_ccid ;
389 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT13' THEN
390 SELECT nvl(segment13,'!@')
391 INTO l_value
392 FROM gl_code_combinations
393 WHERE code_combination_id = x_ccid ;
394 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT12' THEN
395 SELECT nvl(segment12,'!@')
396 INTO l_value
397 FROM gl_code_combinations
398 WHERE code_combination_id = x_ccid ;
399 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT11' THEN
400 SELECT nvl(segment11,'!@')
401 INTO l_value
402 FROM gl_code_combinations
403 WHERE code_combination_id = x_ccid ;
404 ELSIF SUBSTR(x_segment,1,9) = 'SEGMENT10' THEN
405 SELECT nvl(segment10,'!@')
406 INTO l_value
407 FROM gl_code_combinations
408 WHERE code_combination_id = x_ccid ;
409 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT9' THEN
410 SELECT nvl(segment9,'!@')
411 INTO l_value
412 FROM gl_code_combinations
413 WHERE code_combination_id = x_ccid ;
414 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT8' THEN
415 SELECT nvl(segment8,'!@')
416 INTO l_value
417 FROM gl_code_combinations
418 WHERE code_combination_id = x_ccid ;
419 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT7' THEN
420 SELECT nvl(segment7,'!@')
421 INTO l_value
422 FROM gl_code_combinations
423 WHERE code_combination_id = x_ccid ;
424 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT6' THEN
425 SELECT nvl(segment6,'!@')
426 INTO l_value
427 FROM gl_code_combinations
428 WHERE code_combination_id = x_ccid ;
429 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT5' THEN
430 SELECT nvl(segment5,'!@')
431 INTO l_value
432 FROM gl_code_combinations
433 WHERE code_combination_id = x_ccid ;
434 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT4' THEN
435 SELECT nvl(segment4,'!@')
436 INTO l_value
437 FROM gl_code_combinations
438 WHERE code_combination_id = x_ccid ;
439 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT3' THEN
440 SELECT nvl(segment3,'!@')
441 INTO l_value
442 FROM gl_code_combinations
443 WHERE code_combination_id = x_ccid ;
444 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT2' THEN
445 SELECT nvl(segment2,'!@')
446 INTO l_value
447 FROM gl_code_combinations
448 WHERE code_combination_id = x_ccid ;
449 ELSIF SUBSTR(x_segment,1,8) = 'SEGMENT1' THEN
450 SELECT nvl(segment1,'!@')
451 INTO l_value
452 FROM gl_code_combinations
453 WHERE code_combination_id = x_ccid ;
454 ELSE
455 RAISE l_invalid_segment;
456 END IF;
457
458 IF l_value = '!@' THEN
459 RAISE l_null_segment;
460 END IF;
461
462 return(l_value);
463
464 EXCEPTION
465 WHEN l_invalid_segment THEN
466 l_error_msg := 'AUTOGL ERROR - Incorrect AutoAccounting setup - Invalid Segment';
467 WHEN l_null_segment THEN
468 l_error_msg := 'AUTOGL ERROR - Segment value in GL_CODE_COMBINATIONS is null';
469 WHEN NO_DATA_FOUND THEN
470 l_error_msg := 'AUTOGL ERROR - Could not retrieve segment value from GL_CODE_COMBINATIONS.';
471 WHEN OTHERS THEN
472 null;
473
474 END JA_AU_GET_VALUE;
475
476
477 PROCEDURE JA_AU_UPDATE_MTLTRXACCT
478 (x_transaction_id IN
479 mtl_transaction_accounts.transaction_id%TYPE,
480 x_ccid IN
481 gl_code_combinations.code_combination_id%TYPE)
482 IS
483 BEGIN
484
485 UPDATE mtl_transaction_accounts
486 SET reference_account = x_ccid
487 WHERE transaction_id = x_transaction_id
488 AND accounting_line_type = 2;
489
490 IF SQL%NOTFOUND THEN
491 RAISE l_no_rows_updated;
492 END IF;
493
494 COMMIT WORK;
495
496 EXCEPTION
497 WHEN l_no_rows_updated THEN
498 l_error_msg := 'AUTOGL ERROR - Update of charge_account_id in po_requisitions_interface
499 failed.';
500 WHEN OTHERS THEN
501 null;
502 END JA_AU_UPDATE_MTLTRXACCT;
503
504
505 END JA_AU_CCID_PKG;