[Home] [Help]
PACKAGE BODY: APPS.JL_CO_GL_NIT_MANAGEMENT
Source
1 PACKAGE BODY jl_co_gl_nit_management AS
2 /* $Header: jlcoglbb.pls 120.23.12010000.7 2008/08/29 06:02:43 vspuli ship $ */
3
4 g_period_set_name gl_periods.period_set_name%TYPE;
5 g_account_segment fnd_segment_attribute_values.application_column_name%TYPE;
6 g_chart_of_accounts_id fnd_segment_attribute_values.id_flex_num%TYPE;
7 g_period_num gl_periods.period_num%TYPE;
8 g_period_year gl_periods.period_year%TYPE;
9 g_func_currency gl_sets_of_books.currency_code%TYPE;
10 g_default_nit_id jl_co_gl_nits.nit_id%TYPE;
11 g_login_id jl_co_gl_trx.last_update_login%TYPE;
12
13 PROCEDURE Create_Balances(
14 p_period IN VARCHAR2,
15 p_period_year IN NUMBER,
16 p_period_num IN NUMBER,
17 p_sobid IN NUMBER
18 );
19
20 -- R12 Changes: Changed gljl.global_attribute1 to gjjl.co_third_party
21
22
23 CURSOR journals (x_period VARCHAR2, x_sobid NUMBER, x_batchid NUMBER) IS
24 SELECT gljh.je_source source,
25 gljh.je_batch_id,
26 gljl.je_header_id,
27 gljl.je_line_num ,
28 gljh.je_category category,
29 gljh.reversed_je_header_id,
30 gljl.code_combination_id,
31 gljl.period_name,
32 gljl.effective_date accounting_date,
33 gljl.reference_1 ext_doc_num,
34 0 extgl_nit_id,
35 NVL(NVL(gljl.reference_2,NVL(gljl.CO_THIRD_PARTY,gljl.global_attribute1)),'0') ext_nit,
36 SUBSTR(gljl.reference_4,1,30) ext_nit_type,
37 gljl.reference_5 ext_nit_name,
38 gljl.reference_3 ext_nit_v_digit,
39 gljl.subledger_doc_sequence_value subl_doc_num,
40 gljh.currency_code currency,
41 gljl.entered_dr,
42 gljl.entered_cr,
43 gljl.accounted_cr,
44 gljl.accounted_dr,
45 gljl.reference_1 ref_1,
46 gljl.reference_2 ref_2,
47 gljl.reference_3 ref_3,
48 gljl.reference_4 ref_4,
49 gljl.reference_5 ref_5,
50 gljl.reference_6 ref_6,
51 gljl.reference_7 ref_7,
52 gljl.reference_8 ref_8,
53 gljl.reference_9 ref_9,
54 gljl.reference_10 ref_10,
55 DECODE(g_account_segment,
56 'SEGMENT1', glcc.segment1, 'SEGMENT2', glcc.segment2,
57 'SEGMENT3', glcc.segment3, 'SEGMENT4', glcc.segment4,
58 'SEGMENT5', glcc.segment5, 'SEGMENT6', glcc.segment6,
59 'SEGMENT7', glcc.segment7, 'SEGMENT8', glcc.segment8,
60 'SEGMENT9', glcc.segment9, 'SEGMENT10',glcc.segment10,
61 'SEGMENT11',glcc.segment11, 'SEGMENT12',glcc.segment12,
62 'SEGMENT13',glcc.segment13, 'SEGMENT14',glcc.segment14,
63 'SEGMENT15',glcc.segment15, 'SEGMENT16',glcc.segment16,
64 'SEGMENT17',glcc.segment17, 'SEGMENT18',glcc.segment18,
65 'SEGMENT19',glcc.segment19, 'SEGMENT20',glcc.segment20,
66 'SEGMENT21',glcc.segment21, 'SEGMENT22',glcc.segment22,
67 'SEGMENT23',glcc.segment23, 'SEGMENT24',glcc.segment24,
68 'SEGMENT25',glcc.segment25, 'SEGMENT26',glcc.segment26,
69 'SEGMENT27',glcc.segment27, 'SEGMENT28',glcc.segment28,
70 'SEGMENT28',glcc.segment28, 'SEGMENT29',glcc.segment29,
71 'SEGMENT30',glcc.segment30, NULL) account_code
72 FROM gl_je_headers gljh,
73 gl_code_combinations glcc,
74 gl_je_lines gljl
75 WHERE gljl.status = 'P'
76 AND gljl.period_name = x_period
77 AND gljl.ledger_id = x_sobid
78 AND gljh.je_batch_id = NVL(x_batchid,gljh.je_batch_id)
79 AND gljl.code_combination_id = glcc.code_combination_id
80 AND EXISTS (SELECT '1'
81 FROM jl_co_gl_nit_accts jlcgna
82 WHERE DECODE(g_account_segment,
83 'SEGMENT1', glcc.segment1, 'SEGMENT2', glcc.segment2,
84 'SEGMENT3', glcc.segment3, 'SEGMENT4', glcc.segment4,
85 'SEGMENT5', glcc.segment5, 'SEGMENT6', glcc.segment6,
86 'SEGMENT7', glcc.segment7, 'SEGMENT8', glcc.segment8,
87 'SEGMENT9', glcc.segment9, 'SEGMENT10',glcc.segment10,
88 'SEGMENT11',glcc.segment11, 'SEGMENT12',glcc.segment12,
89 'SEGMENT13',glcc.segment13, 'SEGMENT14',glcc.segment14,
90 'SEGMENT15',glcc.segment15, 'SEGMENT16',glcc.segment16,
91 'SEGMENT17',glcc.segment17, 'SEGMENT18',glcc.segment18,
92 'SEGMENT19',glcc.segment19, 'SEGMENT20',glcc.segment20,
93 'SEGMENT21',glcc.segment21, 'SEGMENT22',glcc.segment22,
94 'SEGMENT23',glcc.segment23, 'SEGMENT24',glcc.segment24,
95 'SEGMENT25',glcc.segment25, 'SEGMENT26',glcc.segment26,
96 'SEGMENT27',glcc.segment27, 'SEGMENT28',glcc.segment28,
97 'SEGMENT28',glcc.segment28, 'SEGMENT29',glcc.segment29,
98 'SEGMENT30',glcc.segment30, NULL) = jlcgna.account_code
99 AND jlcgna.nit_required = 'Y'
100 AND jlcgna.chart_of_accounts_id = g_chart_of_accounts_id)
101 AND gljl.je_header_id = gljh.je_header_id
102 AND nvl(gljl.co_processed_flag, 'N') <> 'Y'
103 AND gljh.actual_flag = 'A'
104 AND gljh.currency_code <> 'STAT';
105
106 TYPE t_parameters IS RECORD (
107 cid jl_co_gl_conc_ctrl.process_id%type,
108 set_of_books_id jl_co_gl_conc_ctrl.set_of_books_id%type,
109 user_id jl_co_gl_conc_ctrl.created_by%type,
110 rev_cid jl_co_gl_conc_ctrl.reversed_process_id%type);
111
112 TYPE t_nits IS RECORD (nit_id jl_co_gl_nits.nit_id%type,
113 nit jl_co_gl_nits.nit%type,
114 nit_name jl_co_gl_nits.name%type,
115 nit_type jl_co_gl_nits.type%type,
116 verifying_digit jl_co_gl_nits.verifying_digit%type);
117
118 TYPE t_gl_je IS RECORD (je_header_id jl_co_gl_trx.je_header_id%type,
119 je_line_num jl_co_gl_trx.je_line_num%type,
120 identifier jl_co_gl_conc_errs.identifier%type);
121
122 g_jl_trx jl_co_gl_trx%ROWTYPE;
123 g_journal_rec journals%ROWTYPE;
124 g_parameter_rec t_parameters;
125 g_nit_rec t_nits;
126 g_gl_je_rec t_gl_je;
127 g_error_exists VARCHAR2(5);
128 g_error_code NUMBER;
129 g_error_text VARCHAR2(240);
130
131
132 PROCEDURE Insert_Error_Rec
133 (p_message_text IN VARCHAR2 ) IS
134
135 BEGIN
136
137 INSERT INTO jl_co_gl_conc_errs (message_text,
138 process_id,
139 je_header_id,
140 je_line_num,
141 identifier,
142 creation_date,
143 created_by,
144 last_update_date,
145 last_updated_by,
146 last_update_login)
147 VALUES (p_message_text,
148 g_parameter_rec.cid,
149 g_gl_je_rec.je_header_id,
150 g_gl_je_rec.je_line_num,
151 g_gl_je_rec.identifier,
152 sysdate,
153 NVL(g_parameter_rec.user_id,-1),
154 sysdate,
155 NVL(g_parameter_rec.user_id,-1),
156 g_login_id );
157
158 EXCEPTION
159 WHEN others THEN
160 g_error_code := SQLCODE;
161 g_error_text := SUBSTR(SQLERRM,1,240);
162 FND_FILE.PUT_LINE(FND_FILE.log,'Insert_Error_Rec:'|| g_error_text);
163 RAISE;
164
165 END Insert_Error_Rec;
166
167
168 FUNCTION Validate_NIT
169 (p_nit_rec IN OUT NOCOPY t_nits,
170 p_identifier_type IN VARCHAR2 ) RETURN BOOLEAN IS
171
172 -- Validate nit information against jl_co_gl_nits.
173 -- If a corresponding record does not exist in jl_co_gl_nits,
174 -- insert a new nit record
175
176 l_master_nit_rec t_nits;
177 l_nit_valid BOOLEAN := TRUE;
178 l_message_text jl_co_gl_conc_errs.message_text%TYPE := NULL;
179 l_add_text VARCHAR2(30):= NULL;
180
181 BEGIN
182
183 -- add_text is populated only for external sources
184
185 IF p_identifier_type = 'JL_CO_GL_NIT' THEN
186 l_add_text := 'JL_CO_GL_0_NIT_TRX_CREATED';
187 END IF;
188
189 BEGIN -- check if nit exists by nit number
190 FND_FILE.PUT_LINE(FND_FILE.log,'p_nit_rec.nit : '||p_nit_rec.nit);
191
192 SELECT nit_id,
193 nit,
194 name,
195 type,
196 verifying_digit
197 INTO l_master_nit_rec
198 FROM jl_co_gl_nits
199 WHERE nit = p_nit_rec.nit;
200
201
202 EXCEPTION
203 WHEN no_data_found THEN
204 NULL;
205 END;
206
207 BEGIN -- check if nit exists by name
208 IF l_master_nit_rec.nit IS NULL THEN
209
210 SELECT nit_id,
211 nit,name,
212 type,
213 verifying_digit
214 INTO l_master_nit_rec
215 FROM jl_co_gl_nits
216 WHERE UPPER(name) = UPPER(p_nit_rec.nit_name);
217
218 FND_FILE.PUT_LINE(FND_FILE.log,'p_nit_rec.nit_name : '||p_nit_rec.nit_name);
219
220 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_NIT_NAME_EXISTS');
221 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
222 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
223 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
224 l_message_text := FND_MESSAGE.GET;
225 Insert_Error_Rec(l_message_text);
226 l_nit_valid := FALSE;
227
228 END IF;
229
230 EXCEPTION
231 WHEN no_data_found THEN
232 NULL;
233 END;
234
235 IF l_master_nit_rec.nit IS NULL THEN
236 FND_FILE.PUT_LINE(FND_FILE.log,'master : p_nit_rec.nit : '||p_nit_rec.nit);
237
238 -- master NIT does not exist
239 -- validate NIT and insert new NIT record
240 IF jg_taxid_val_pkg.check_length('CO',14,p_nit_rec.nit) = 'FALSE' THEN
241 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_NIT_MAX_DIGITS');
242 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
243 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
244 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
245 l_message_text := FND_MESSAGE.GET;
246 Insert_Error_Rec(l_message_text);
247 l_nit_valid := FALSE;
248 END IF; /*check_length*/
249
250 IF p_nit_rec.nit IS NULL THEN
251 FND_FILE.PUT_LINE(FND_FILE.log,'NIT Null Check : p_nit_rec.nit : '||p_nit_rec.nit);
252
253 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_NIT_REQUIRED');
254 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
255 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
256 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
257 l_message_text := FND_MESSAGE.GET;
258 Insert_Error_Rec(l_message_text);
259 l_nit_valid := FALSE;
260
261 ELSE
262 IF jg_taxid_val_pkg.check_numeric(p_nit_rec.nit) = 'FALSE' THEN
263 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_INVALID_NIT');
264 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
265 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
266 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
267 l_message_text := FND_MESSAGE.GET;
268
269 Insert_Error_Rec(l_message_text);
270 l_nit_valid := FALSE;
271
272 ELSE /* nit is numeric */
273 IF (p_nit_rec.verifying_digit IS NOT NULL) THEN
274 IF jg_taxid_val_pkg.check_algorithm(p_nit_rec.nit,'CO',
275 p_nit_rec.verifying_digit) = 'FALSE' THEN
276 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_VER_DIGIT_INVALID');
277 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',
278 p_identifier_type,TRUE);
279 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
280 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
281 l_message_text := FND_MESSAGE.GET;
282 Insert_Error_Rec(l_message_text);
283 l_nit_valid := FALSE;
284 END IF; /* validate_algorithm for verifying digit*/
285 END IF; /* v_digit not NULL */
286 END IF; /* check numeric */
287 END IF; /* nit is NULL */
288
289 IF (p_nit_rec.nit_type = 'LEGAL_ENTITY' AND
290 p_nit_rec.verifying_digit IS NULL) THEN
291 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_VER_DIGIT_REQUIRED');
292 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
293 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
294 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
295 l_message_text := FND_MESSAGE.GET;
296 Insert_Error_Rec(l_message_text);
297 l_nit_valid := FALSE;
298 END IF;
299
300 IF (p_nit_rec.nit_type IS NULL) OR (p_nit_rec.nit_type NOT IN
301 ('LEGAL_ENTITY','INDIVIDUAL','FOREIGN_ENTITY')) THEN
302 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_NIT_TYPE_REQUIRED');
303 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
304 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
305 FND_MESSAGE.SET_TOKEN('TAXID_TYPE', 'LEGAL_ENTITY, INDIVIDUAL and FOREIGN_ENTITY');
306 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
307 l_message_text := FND_MESSAGE.GET;
308 Insert_Error_Rec(l_message_text);
309 l_nit_valid := FALSE;
310 END IF;
311
312 IF p_nit_rec.nit_name IS NULL THEN
313 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_NIT_NAME_REQUIRED');
314 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
315 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
316 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
317 l_message_text := FND_MESSAGE.GET;
318 Insert_Error_Rec(l_message_text);
319 l_nit_valid := FALSE;
320 END IF;
321
322 -- insert validated NIT record
323 IF l_nit_valid THEN
324
325 INSERT INTO jl_co_gl_nits (nit_id,
326 nit,
327 type,
328 verifying_digit,
329 name,
330 creation_date,
331 created_by,
332 last_update_date,
333 last_updated_by,
334 last_update_login)
335 VALUES (jl_co_gl_nits_s.nextval,
336 p_nit_rec.nit,
337 p_nit_rec.nit_type,
338 p_nit_rec.verifying_digit,
339 p_nit_rec.nit_name,
340 sysdate,
341 NVL(g_parameter_rec.user_id,-1),
342 sysdate,
343 NVL(g_parameter_rec.user_id,-1),
344 g_login_id);
345
346 SELECT jl_co_gl_nits_s.currval
347 INTO p_nit_rec.nit_id
348 FROM DUAL ;
349
350 END IF;
351
352 ELSIF l_nit_valid THEN
353 -- nit exists verify if the info matches master nit
354
355 IF (p_nit_rec.nit <> l_master_nit_rec.nit) OR
356 (UPPER(p_nit_rec.nit_name) <> UPPER(l_master_nit_rec.nit_name)) OR
357 (UPPER(p_nit_rec.nit_type) <> UPPER(l_master_nit_rec.nit_type)) OR
358 (p_nit_rec.verifying_digit <> l_master_nit_rec.verifying_digit) THEN
359
360 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_MASTER_NIT_MISMATCH');
361 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
362 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
363 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
364 l_message_text := FND_MESSAGE.GET;
365 Insert_Error_Rec(l_message_text);
366 l_nit_valid := FALSE;
367 END IF;
368 END IF;
369
370 RETURN(l_nit_valid);
371
372 EXCEPTION
373 WHEN no_data_found THEN
374 NULL;
375
376 WHEN others THEN
377 g_error_code := SQLCODE;
378 g_error_text := SUBSTR(SQLERRM,1,240);
379 FND_FILE.PUT_LINE(FND_FILE.log,'Validate_NIT:'|| g_error_text);
380 RAISE;
381
382 END Validate_NIT;
383
384
385 PROCEDURE Generate_GL_trx
386 (p_journal_rec IN journals%ROWTYPE ) IS
387
388 l_add_text VARCHAR2(30):= NULL;
389 l_message_text jl_co_gl_conc_errs.message_text%TYPE := NULL;
390
391 BEGIN
392
393 BEGIN
394 SELECT nit_id
395 INTO g_nit_rec.nit_id
396 FROM jl_co_gl_nits jlcgn
397 WHERE nit = DECODE(p_journal_rec.source,
398 'Payables','0',
399 'Purchasing','0',
400 'Receivables','0',
401 p_journal_rec.ext_nit);
402
403 EXCEPTION
404 WHEN no_data_found THEN
405 g_nit_rec.nit_id := NULL; --Fwd port of 11i bug 6155086
406 END;
407
408 SELECT p_journal_rec.je_header_id,
409 p_journal_rec.je_line_num,
410 null
411 INTO g_gl_je_rec
412 FROM DUAL;
413
414 IF p_journal_rec.source in ('Payables','Purchasing','Receivables') THEN
415 -- Its here probably because import references dont exist or is in
416 -- summary because of which the AR, AP or PO routine couldn't process
417 -- it and passed the record on to the GL routine
418
419 l_add_text := 'JL_CO_GL_0_NIT_TRX_CREATED';
420 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_NO_IMPORT_REF');
421 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
422 l_message_text := FND_MESSAGE.GET;
423 Insert_Error_Rec(l_message_text);
424
425 ELSIF g_nit_rec.nit_id IS NULL THEN /* Third party # is not valid */
426
427 l_add_text := 'JL_CO_GL_0_NIT_TRX_CREATED';
428 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_INVALID_NIT');
429 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',NULL);
430 FND_MESSAGE.SET_TOKEN('IDENTIFIER',NULL);
431 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
432 l_message_text := FND_MESSAGE.GET;
433 Insert_Error_Rec(l_message_text);
434
435 ELSIF p_journal_rec.ext_nit = '0' THEN
436 -- Not calling NIT_VALIDATION function because the only nit info
437 -- entered by the user in Enter Journals form is NIT# hence it
438 -- isn't necessary to do exhaustive NIT validation
439
440 l_add_text := 'JL_CO_GL_0_NIT_TRX_CREATED';
441 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_NIT_REQUIRED');
442 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',NULL);
443 FND_MESSAGE.SET_TOKEN('IDENTIFIER',NULL);
444 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
445 l_message_text := FND_MESSAGE.GET;
446 Insert_Error_Rec(l_message_text);
447
448 END IF;
449
450 INSERT INTO jl_co_gl_trx (transaction_id,
451 process_id,
452 set_of_books_id,
453 code_combination_id,
454 account_code,
455 nit_id,
456 period_name,
457 period_year,
458 period_num,
459 je_batch_id,
460 je_header_id,
461 category,
462 subledger_doc_number,
463 je_line_num,
464 document_number,
465 accounting_date,
466 currency_code,
467 creation_date,
468 created_by,
469 last_update_date,
470 last_updated_by,
471 last_update_login,
472 accounted_dr,
473 accounted_cr,
474 entered_dr,
475 entered_cr)
476 VALUES (jl_co_gl_trx_s.nextval,
477 g_parameter_rec.cid,
478 g_parameter_rec.set_of_books_id,
479 p_journal_rec.code_combination_id,
480 p_journal_rec.account_code,
481 NVL(g_nit_rec.nit_id,g_default_nit_id),
482 p_journal_rec.period_name,
483 g_period_year,
484 g_period_num,
485 p_journal_rec.je_batch_id,
486 p_journal_rec.je_header_id,
487 p_journal_rec.category,
488 p_journal_rec.subl_doc_num,
489 p_journal_rec.je_line_num,
490 NULL,
491 p_journal_rec.accounting_date,
492 p_journal_rec.currency,
493 sysdate,
494 NVL(g_parameter_rec.user_id,-1),
495 sysdate,
496 NVL(g_parameter_rec.user_id,-1),
497 g_login_id,
498 DECODE(sign(p_journal_rec.accounted_cr), -1,
499 (abs(p_journal_rec.accounted_cr) +
500 NVL(DECODE(sign(p_journal_rec.accounted_dr),1,
501 p_journal_rec.accounted_dr,NULL),0)),
502 DECODE(sign(p_journal_rec.accounted_dr),-1,
503 NULL,p_journal_rec.accounted_dr)),
504 DECODE(sign(p_journal_rec.accounted_dr),-1,
505 (abs(p_journal_rec.accounted_dr) +
506 NVL(DECODE(sign(p_journal_rec.accounted_cr),1,
507 p_journal_rec.accounted_cr,NULL),0)),
508 DECODE(sign(p_journal_rec.accounted_cr),-1,
509 NULL,p_journal_rec.accounted_cr)),
510 DECODE(sign(p_journal_rec.entered_cr),-1,
511 (abs(p_journal_rec.entered_cr) +
512 NVL(DECODE(sign(p_journal_rec.entered_dr),1,
513 p_journal_rec.entered_dr,NULL),0)),
514 DECODE(sign(p_journal_rec.entered_dr),-1,
515 NULL,p_journal_rec.entered_dr)),
516 DECODE(sign(p_journal_rec.entered_dr),-1,
517 (abs(p_journal_rec.entered_dr) +
518 NVL(DECODE(sign(p_journal_rec.entered_cr),1,
519 p_journal_rec.entered_cr,NULL),0)),
520 DECODE(sign(p_journal_rec.entered_cr),-1,
521 NULL,p_journal_rec.entered_cr)) );
522 /* bug 7045429
523 BEGIN
524 SELECT 'TRUE'
525 INTO g_error_exists
526 FROM DUAL
527 WHERE EXISTS (SELECT '1'
528 FROM jl_co_gl_conc_errs jlcgce
529 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
530 AND jlcgce.je_line_num = p_journal_rec.je_line_num);
531
532 EXCEPTION
533 WHEN no_data_found THEN
534 NULL;
535 END;
536
537 IF NVL(g_error_exists,'FALSE') = 'TRUE' THEN
538 DELETE FROM jl_co_gl_trx jlcgt
539 WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
540 AND jlcgt.je_line_num = p_journal_rec.je_line_num;
541 ELSE
542 UPDATE gl_je_lines gljl
543 SET co_processed_flag = 'Y'
544 WHERE gljl.je_header_id = p_journal_rec.je_header_id
545 AND gljl.je_line_num = p_journal_rec.je_line_num
546 AND EXISTS (SELECT 'Y'
547 FROM jl_co_gl_trx jlcgt
548 WHERE jlcgt.je_header_id = gljl.je_header_id
549 AND jlcgt.je_line_num = gljl.je_line_num);
550 END IF;
551 */
552
553 COMMIT;
554
555 EXCEPTION
556 WHEN others THEN
557 g_error_code := SQLCODE;
558 g_error_text := SUBSTR(SQLERRM,1,240);
559 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_GL_trx:'|| g_error_text);
560 RAISE;
561
562 END Generate_GL_trx;
563
564
565 PROCEDURE Generate_AP_trx
566 (p_journal_rec IN journals%ROWTYPE ) IS
567
568 -- R12 changes: replaced 11i sla tables with R12 xla tables and removed
569 -- the third sql in 11i for Payment history. This is handled via the 2nd sql here
570
571 CURSOR ap_detail_lines IS
572 SELECT DECODE(SIGN(lnk.unrounded_accounted_cr),-1,
573 DECODE(SIGN(lnk.unrounded_accounted_dr),-1,ABS(lnk.unrounded_accounted_dr),null),
574 lnk.unrounded_accounted_cr) ACCOUNTED_CR,
575 DECODE(SIGN(lnk.unrounded_accounted_dr),-1,
576 DECODE(SIGN(lnk.unrounded_accounted_cr),-1,ABS(lnk.unrounded_accounted_cr),null),
577 lnk.unrounded_accounted_dr) ACCOUNTED_DR,
578 DECODE(SIGN(lnk.unrounded_entered_dr),-1,
579 DECODE(SIGN(lnk.unrounded_entered_cr),-1, ABS(lnk.unrounded_entered_cr), null),
580 lnk.unrounded_entered_dr) ENTERED_DR,
581 DECODE(SIGN(lnk.unrounded_entered_cr),-1,
582 DECODE(SIGN(lnk.unrounded_entered_dr),-1, ABS(lnk.unrounded_entered_dr), null),
583 lnk.unrounded_entered_cr) ENTERED_CR,
584 lnk.source_distribution_type SOURCE_TABLE,
585 ael.party_id PARTY_ID,
586 ent.transaction_number TRX_NUMBER_C ,
587 I.invoice_id TRX_HDR_ID,
588 'INV' TRX_CLASS,
589 ael.accounting_class_code ACCT_LINE_TYPE,
590 D.invoice_distribution_id TRX_DIST_ID
591 FROM
592 ap_invoices_all I,
593 xla_transaction_entities ent,
594 xla_ae_headers AEH,
595 xla_ae_lines AEL,
596 ap_invoice_distributions_all D,
597 xla_distribution_links LNK,
598 gl_import_references R
599 WHERE
600 ent.application_id = 200
601 and ent.application_id =aeh.application_id
602 and aeh.application_id = ael.application_id
603 and (ent.ledger_id = g_parameter_rec.set_of_books_id
604 or ent.ledger_id in (SELECT from_ledger_id
605 FROM gl_consolidation
606 WHERE to_ledger_id = g_parameter_rec.set_of_books_id))
607 and ent.entity_code = 'AP_INVOICES'
608 and i.invoice_id = ent.source_id_int_1
609 and ent.entity_id = aeh.entity_id
610 AND AEH.ledger_id = ent.ledger_id
611 and aeh.ae_header_id = ael.ae_header_id
612 and ael.ae_header_id = lnk.ae_header_id
613 and ael.ae_line_num = lnk.ae_line_num
614 and ael.application_id = 200
615 and lnk.application_id = 200
616 --AND D.invoice_distribution_id(+) = DECODE(lnk.source_distribution_type,
617 -- 'AP_INVOICE_DISTRIBUTIONS', lnk.source_distribution_id_num_1,null)
618 AND D.invoice_distribution_id = lnk.source_distribution_id_num_1
619 AND R.gl_sl_link_id = AEL.gl_sl_link_id
620 AND R.je_header_id = p_journal_rec.je_header_id
621 AND R.je_line_num = p_journal_rec.je_line_num
622 UNION ALL
623 SELECT DECODE(SIGN(lnk.unrounded_accounted_cr),-1,
624 DECODE(SIGN(lnk.unrounded_accounted_dr),-1,ABS(lnk.unrounded_accounted_dr),null),
625 lnk.unrounded_accounted_cr) ACCOUNTED_CR,
626 DECODE(SIGN(lnk.unrounded_accounted_dr),-1,
627 DECODE(SIGN(lnk.unrounded_accounted_cr),-1,ABS(lnk.unrounded_accounted_cr),null),
628 lnk.unrounded_accounted_dr) ACCOUNTED_DR,
629 DECODE(SIGN(lnk.unrounded_entered_dr),-1,
630 DECODE(SIGN(lnk.unrounded_entered_cr),-1, ABS(lnk.unrounded_entered_cr), null),
631 lnk.unrounded_entered_dr) ENTERED_DR,
632 DECODE(SIGN(lnk.unrounded_entered_cr),-1,
633 DECODE(SIGN(lnk.unrounded_entered_dr),-1, ABS(lnk.unrounded_entered_dr), null),
634 lnk.unrounded_entered_cr) ENTERED_CR,
635 ent.entity_code SOURCE_TABLE,
636 ael.party_id PARTY_ID,
637 TO_CHAR(C.CHECK_NUMBER) TRX_NUMBER_C ,
638 C.CHECK_ID TRX_HDR_ID,
639 'PAY' TRX_CLASS,
640 ael.accounting_class_code ACCT_LINE_TYPE,
641 D.invoice_distribution_id TRX_DIST_ID
642 FROM
643 ap_checks_all C,
644 xla_transaction_entities ent,
645 xla_ae_headers AEH,
646 xla_ae_lines AEL,
647 ap_payment_hist_dists D, -- bug 6956097
648 xla_distribution_links LNK,
649 gl_import_references R
650 WHERE
651 ent.application_id = 200
652 and (ent.ledger_id = g_parameter_rec.set_of_books_id
653 or ent.ledger_id in (SELECT from_ledger_id
654 FROM gl_consolidation
655 WHERE to_ledger_id = g_parameter_rec.set_of_books_id))
656
657 and ent.entity_code = 'AP_PAYMENTS'
658 and c.check_id = ent.source_id_int_1
659 and ent.entity_id = aeh.entity_id
660 AND AEH.ledger_id = ent.ledger_id
661 and aeh.ae_header_id = ael.ae_header_id
662 and ael.ae_header_id = lnk.ae_header_id
663 and ael.ae_line_num = lnk.ae_line_num
664 and ael.application_id = 200
665 and lnk.application_id = 200
666 --AND D.invoice_distribution_id(+) = DECODE(lnk.source_distribution_type,
667 -- 'AP_INVOICE_DISTRIBUTIONS', lnk.source_distribution_id_num_1,null)
668 AND D.payment_hist_dist_id = lnk.source_distribution_id_num_1 -- bug 6956097
669 AND R.gl_sl_link_id = AEL.gl_sl_link_id
670 AND R.je_header_id = p_journal_rec.je_header_id
671 AND R.je_line_num = p_journal_rec.je_line_num
672 UNION ALL
673 SELECT DECODE(SIGN(lnk.unrounded_accounted_cr),-1,
674 DECODE(SIGN(lnk.unrounded_accounted_dr),-1,ABS(lnk.unrounded_accounted_dr),null),
675 lnk.unrounded_accounted_cr) ACCOUNTED_CR,
676 DECODE(SIGN(lnk.unrounded_accounted_dr),-1,
677 DECODE(SIGN(lnk.unrounded_accounted_cr),-1,ABS(lnk.unrounded_accounted_cr),null),
678 lnk.unrounded_accounted_dr) ACCOUNTED_DR,
679 DECODE(SIGN(lnk.unrounded_entered_dr),-1,
680 DECODE(SIGN(lnk.unrounded_entered_cr),-1, ABS(lnk.unrounded_entered_cr), null),
681 lnk.unrounded_entered_dr) ENTERED_DR,
682 DECODE(SIGN(lnk.unrounded_entered_cr),-1,
683 DECODE(SIGN(lnk.unrounded_entered_dr),-1, ABS(lnk.unrounded_entered_dr), null),
684 lnk.unrounded_entered_cr) ENTERED_CR,
685 lnk.source_distribution_type SOURCE_TABLE,
686 ael.party_id PARTY_ID,
687 ent.transaction_number TRX_NUMBER_C ,
688 I.invoice_id TRX_HDR_ID,
689 'INV' TRX_CLASS,
690 ael.accounting_class_code ACCT_LINE_TYPE,
691 D.invoice_distribution_id TRX_DIST_ID
692 FROM
693 ap_invoices_all I,
694 xla_transaction_entities ent,
695 xla_ae_headers AEH,
696 xla_ae_lines AEL,
697 ap_prepay_app_dists D,
698 xla_distribution_links LNK,
699 gl_import_references R
700 WHERE
701 ent.application_id = 200
702 and ent.application_id =aeh.application_id
703 and aeh.application_id = ael.application_id
704 and (ent.ledger_id = g_parameter_rec.set_of_books_id
705 AND lnk.source_distribution_type = 'AP_PREPAY'
706 or ent.ledger_id in (SELECT from_ledger_id
707 FROM gl_consolidation
708 WHERE to_ledger_id = g_parameter_rec.set_of_books_id))
709 and ent.entity_code = 'AP_INVOICES'
710 and i.invoice_id = ent.source_id_int_1
711 and ent.entity_id = aeh.entity_id
712 AND AEH.ledger_id = ent.ledger_id
713 and aeh.ae_header_id = ael.ae_header_id
714 and ael.ae_header_id = lnk.ae_header_id
715 and ael.ae_line_num = lnk.ae_line_num
716 and ael.application_id = 200
717 and lnk.application_id = 200
718 --AND D.invoice_distribution_id(+) = DECODE(lnk.source_distribution_type,
719 -- 'AP_INVOICE_DISTRIBUTIONS', lnk.source_distribution_id_num_1,null)
720 AND D.prepay_app_dist_id = lnk.source_distribution_id_num_1
721 AND R.gl_sl_link_id = AEL.gl_sl_link_id
722 AND R.je_header_id = p_journal_rec.je_header_id
723 AND R.je_line_num = p_journal_rec.je_line_num;
724
725
726 l_supplier_num po_vendors.segment1%TYPE;
727
728 BEGIN
729
730 g_error_exists := 'FALSE';
731
732 SELECT p_journal_rec.je_header_id,
733 p_journal_rec.je_line_num,
734 p_journal_rec.ext_nit
735 INTO g_gl_je_rec
736 FROM DUAL;
737
738 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_ap_trx : Begin ');
739
740 FOR ap_trx IN ap_detail_lines LOOP
741
742 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_ap_trx : After ap_trx cursor call : ' ||ap_trx.source_table);
743 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_ap_trx :Dist ID' ||to_char(ap_trx.trx_dist_id));
744 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_ap_trx :i Trx ID' ||to_char(ap_trx.trx_hdr_id));
745 g_nit_rec := NULL;
746 l_supplier_num := NULL;
747
748 IF (ap_trx.source_table = 'AP_INV_DIST') THEN
749
750 SELECT DECODE(ap_trx.acct_line_type,'LIABILITY',NULL,NVL(global_attribute2,NULL))
751 INTO l_supplier_num
752 FROM ap_invoice_distributions_all apida
753 WHERE apida.invoice_id = ap_trx.trx_hdr_id
754 --AND apida.distribution_line_number = ap_trx.trx_dist_id
755 AND apida.invoice_distribution_id = ap_trx.trx_dist_id
756 and (apida.set_of_books_id = g_parameter_rec.set_of_books_id
757 or apida.set_of_books_id in (SELECT from_ledger_id
758 FROM gl_consolidation
759 WHERE to_ledger_id = g_parameter_rec.set_of_books_id));
760
761 END IF;
762
763 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_ap_trx : After ap_trx cursor call : ' ||l_supplier_num);
764 /* Commented out for Bug3840010
765 SELECT jlcgn.nit_id,
766 REPLACE(pov.num_1099,'-'),
767 pov.vendor_name,
768 SUBSTR(pov.global_attribute10,1,30),
769 pov.global_attribute12,
770 NVL(l_supplier_num,pov.segment1)
771 INTO g_nit_rec.nit_id,
772 g_nit_rec.nit,
773 g_nit_rec.nit_name,
774 g_nit_rec.nit_type,
775 g_nit_rec.verifying_digit,
776 l_supplier_num
777 FROM jl_co_gl_nits jlcgn, po_vendors pov
778 WHERE NVL(l_supplier_num,TO_CHAR(ap_trx.party_id)) =
779 DECODE(l_supplier_num, NULL,TO_CHAR(pov.vendor_id),pov.segment1)
780 AND jlcgn.nit(+) = REPLACE(pov.num_1099,'-') ; */
781
782 -- Replaced the above logic with 2 different statements executed conditionally
783 IF l_supplier_num IS NULL THEN
784 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_ap_trx : l_supplier_num IS NULL ');
785 SELECT jlcgn.nit_id,
786 REPLACE(povapf.num_1099,'-'),
787 povapf.vendor_name,
788 SUBSTR(povapf.global_attribute10,1,30),
789 povapf.global_attribute12,
790 NVL(l_supplier_num,povapf.segment1)
791 INTO g_nit_rec.nit_id,
792 g_nit_rec.nit,
793 g_nit_rec.nit_name,
794 g_nit_rec.nit_type,
795 g_nit_rec.verifying_digit,
796 l_supplier_num
797 FROM jl_co_gl_nits jlcgn, (SELECT nvl(papf.national_identifier,nvl(aps.individual_1099,aps.num_1099)) num_1099,
798 aps.vendor_name,
799 aps.global_attribute10,
800 aps.global_attribute12,
801 aps.segment1,
802 aps.vendor_id
803 FROM ap_suppliers aps,
804 (select distinct person_id ,national_identifier from per_all_people_f
805 where nvl(EFFECTIVE_END_DATE,sysdate)>=sysdate) papf
806 WHERE nvl(aps.employee_id, -99) = papf.person_id (+)) povapf
807 WHERE ap_trx.party_id = povapf.vendor_id
808 AND jlcgn.nit(+) = REPLACE(povapf.num_1099,'-') ;
809
810 ELSE
811
812 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_ap_trx : l_supplier_num IS NOT NULL ');
813 SELECT jlcgn.nit_id,
814 REPLACE(povapf.num_1099,'-'),
815 povapf.vendor_name,
816 SUBSTR(povapf.global_attribute10,1,30),
817 povapf.global_attribute12,
818 NVL(l_supplier_num,povapf.segment1)
819 INTO g_nit_rec.nit_id,
820 g_nit_rec.nit,
821 g_nit_rec.nit_name,
822 g_nit_rec.nit_type,
823 g_nit_rec.verifying_digit,
824 l_supplier_num
825 FROM jl_co_gl_nits jlcgn, (SELECT nvl(papf.national_identifier,nvl(aps.individual_1099,aps.num_1099)) num_1099,
826 aps.vendor_name,
827 aps.global_attribute10,
828 aps.global_attribute12,
829 aps.segment1,
830 aps.vendor_id
831 FROM ap_suppliers aps,
832 (select distinct person_id ,national_identifier from per_all_people_f
833 where nvl(EFFECTIVE_END_DATE,sysdate)>=sysdate) papf
834 WHERE nvl(aps.employee_id, -99) = papf.person_id (+)) povapf
835 WHERE l_supplier_num = povapf.segment1
836 AND jlcgn.nit(+) = REPLACE(povapf.num_1099,'-') ;
837
838 END IF;
839
840 g_gl_je_rec.identifier := l_supplier_num;
841
842 FND_FILE.PUT_LINE(FND_FILE.log,'Before Validate Call for AP : JE Line NUM, Header_id : '||to_char(p_journal_rec.je_line_num)||'-'||to_char(p_journal_rec.je_header_id));
843 IF Validate_NIT(g_nit_rec, 'JL_CO_GL_SUPPLIER') THEN
844 FND_FILE.PUT_LINE(FND_FILE.log,'After AP NIT Validate : JE Line NUM, Header_id : '||to_char(p_journal_rec.je_line_num)||'-'||to_char(p_journal_rec.je_header_id));
845 FND_FILE.PUT_LINE(FND_FILE.log,'CCID, account code : '||to_char(p_journal_rec.code_combination_id)
846 ||'-'||p_journal_rec.account_code);
847 FND_FILE.PUT_LINE(FND_FILE.log,' : '||g_nit_rec.nit||'-'||g_nit_rec.nit_name);
848 FND_FILE.PUT_LINE(FND_FILE.log,'AP : acc_dr, acc_cr,ent_dr, ent_dr : '
849 ||to_char(ap_trx.accounted_dr)||'-'||to_char(ap_trx.accounted_cr)
850 ||'-'||to_char(ap_trx.entered_dr)||'-'||to_char(ap_trx.entered_cr));
851
852 INSERT INTO jl_co_gl_trx (transaction_id,
853 process_id,
854 set_of_books_id,
855 code_combination_id,
856 account_code,
857 nit_id,
858 period_name,
859 period_year,
860 period_num,
861 je_batch_id,
862 je_header_id,
863 category,
864 subledger_doc_number,
865 je_line_num,
866 document_number,
867 accounting_date,
868 currency_code,
869 creation_date,
870 created_by,
871 last_update_date,
872 last_updated_by,
873 last_update_login,
874 accounted_dr,
875 accounted_cr,
876 entered_dr,
877 entered_cr)
878 VALUES (jl_co_gl_trx_s.nextval,
879 g_parameter_rec.cid,
880 g_parameter_rec.set_of_books_id,
881 p_journal_rec.code_combination_id,
882 p_journal_rec.account_code,
883 g_nit_rec.nit_id,
884 p_journal_rec.period_name,
885 g_period_year,
886 g_period_num,
887 p_journal_rec.je_batch_id,
888 p_journal_rec.je_header_id,
889 p_journal_rec.category,
890 p_journal_rec.subl_doc_num,
891 p_journal_rec.je_line_num,
892 ap_trx.trx_number_c,
893 p_journal_rec.accounting_date,
894 p_journal_rec.currency,
895 sysdate,
896 NVL(g_parameter_rec.user_id,-1),
897 sysdate,
898 NVL(g_parameter_rec.user_id,-1),
899 g_login_id,
900 ap_trx.accounted_dr,
901 ap_trx.accounted_cr,
902 ap_trx.entered_dr,
903 ap_trx.entered_cr );
904
905 BEGIN
906
907 /* Commented for bug3840010
908
909 SELECT 'TRUE'
910 INTO g_error_exists
911 FROM DUAL
912 WHERE EXISTS (SELECT '1'
913 FROM jl_co_gl_conc_errs jlcgce
914 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
915 AND jlcgce.je_line_num = p_journal_rec.je_line_num); */
916
917 -- New statement for bug 3840010
918 SELECT 'TRUE'
919 INTO g_error_exists
920 FROM jl_co_gl_conc_errs jlcgce
921 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
922 AND jlcgce.je_line_num = p_journal_rec.je_line_num and rownum = 1;
923
924 EXCEPTION
925 WHEN no_data_found THEN
926 NULL;
927 END;
928
929 IF NVL(g_error_exists,'FALSE') = 'TRUE' THEN
930 DELETE FROM jl_co_gl_trx jlcgt
931 WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
932 AND jlcgt.je_line_num = p_journal_rec.je_line_num;
933
934 ELSE
935 UPDATE gl_je_lines gljl
936 SET co_processed_flag = 'Y'
937 WHERE gljl.je_header_id = p_journal_rec.je_header_id
938 AND gljl.je_line_num = p_journal_rec.je_line_num
939 AND EXISTS (SELECT 'Y'
940 FROM jl_co_gl_trx jlcgt
941 WHERE jlcgt.je_header_id = gljl.je_header_id
942 AND jlcgt.je_line_num = gljl.je_line_num);
943
944 END IF;
945
946 END IF;
947
948 END LOOP;
949
950 BEGIN
951
952 SELECT 'TRUE'
953 INTO g_error_exists
954 FROM DUAL
955 WHERE EXISTS (SELECT '1'
956 FROM jl_co_gl_conc_errs jlcgce
957 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
958 AND jlcgce.je_line_num = p_journal_rec.je_line_num);
959 EXCEPTION
960 WHEN no_data_found THEN
961 NULL;
962 END;
963
964 IF NVL(g_error_exists,'FALSE') = 'TRUE' THEN
965 UPDATE gl_je_lines gljl
966 SET co_processed_flag = NULL
967 WHERE gljl.je_header_id = p_journal_rec.je_header_id
968 AND gljl.je_line_num = p_journal_rec.je_line_num
969 AND EXISTS (SELECT 'Y'
970 FROM jl_co_gl_trx jlcgt
971 WHERE jlcgt.je_header_id = gljl.je_header_id
972 AND jlcgt.je_line_num = gljl.je_line_num);
973
974 DELETE FROM jl_co_gl_trx jlcgt
975 WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
976 AND jlcgt.je_line_num = p_journal_rec.je_line_num;
977 END IF;
978
979 COMMIT;
980
981 EXCEPTION
982 WHEN no_data_found THEN
983 -- import references don't exist or are summarized for journal
984 Generate_GL_trx(g_journal_rec); -- Create with NIT 0
985
986 WHEN others THEN
987 g_error_code := SQLCODE;
988 g_error_text := SUBSTR(SQLERRM,1,240);
989 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_AP_trx:'|| g_error_text);
990 RAISE;
991
992 END Generate_AP_trx;
993
994
995 PROCEDURE Generate_PO_trx
996 (p_journal_rec IN journals%ROWTYPE ) IS
997
998 BEGIN
999
1000 g_error_exists := 'FALSE';
1001 g_nit_rec := NULL;
1002 g_gl_je_rec := NULL;
1003
1004 IF p_journal_rec.category in ('Accrual','Receiving') THEN
1005
1006 SELECT jlcgn.nit_id,
1007 REPLACE(povapf.num_1099,'-'),
1008 povapf.vendor_name,
1009 SUBSTR(povapf.global_attribute10,1,30),
1010 povapf.global_attribute12,
1011 povapf.segment1,
1012 p_journal_rec.je_header_id,
1013 p_journal_rec.je_line_num
1014 INTO g_nit_rec.nit_id,
1015 g_nit_rec.nit,
1016 g_nit_rec.nit_name,
1017 g_nit_rec.nit_type,
1018 g_nit_rec.verifying_digit,
1019 g_gl_je_rec.identifier,
1020 g_gl_je_rec.je_header_id,
1021 g_gl_je_rec.je_line_num
1022 FROM jl_co_gl_nits jlcgn,
1023 po_headers_all poha,
1024 (SELECT nvl(papf.national_identifier,nvl(aps.individual_1099,aps.num_1099)) num_1099,
1025 aps.vendor_name,
1026 aps.global_attribute10,
1027 aps.global_attribute12,
1028 aps.segment1,
1029 aps.vendor_id
1030 FROM ap_suppliers aps,
1031 (select distinct person_id ,national_identifier from per_all_people_f
1032 where nvl(EFFECTIVE_END_DATE,sysdate)>=sysdate) papf
1033 WHERE nvl(aps.employee_id, -99) = papf.person_id (+)) povapf
1034 WHERE poha.po_header_id = TO_NUMBER(p_journal_rec.ref_2)
1035 AND povapf.vendor_id = poha.vendor_id
1036 AND REPLACE(povapf.num_1099,'-') = jlcgn.nit(+);
1037
1038 IF Validate_NIT(g_nit_rec, 'JL_CO_GL_SUPPLIER') THEN
1039
1040 INSERT INTO jl_co_gl_trx (transaction_id,
1041 process_id,
1042 set_of_books_id,
1043 code_combination_id,
1044 account_code,
1045 nit_id,
1046 period_name,
1047 period_year,
1048 period_num,
1049 je_batch_id,
1050 je_header_id,
1051 category,
1052 subledger_doc_number,
1053 je_line_num,
1054 document_number,
1055 accounting_date,
1056 currency_code,
1057 creation_date,
1058 created_by,
1059 last_update_date,
1060 last_updated_by,
1061 last_update_login,
1062 accounted_dr,
1063 accounted_cr,
1064 entered_dr,
1065 entered_cr )
1066 VALUES (jl_co_gl_trx_s.nextval,
1067 g_parameter_rec.cid,
1068 g_parameter_rec.set_of_books_id,
1069 p_journal_rec.code_combination_id,
1070 p_journal_rec.account_code,
1071 g_nit_rec.nit_id,
1072 p_journal_rec.period_name,
1073 g_period_year,
1074 g_period_num,
1075 p_journal_rec.je_batch_id,
1076 p_journal_rec.je_header_id,
1077 p_journal_rec.category,
1078 p_journal_rec.subl_doc_num,
1079 p_journal_rec.je_line_num,
1080 p_journal_rec.ref_4,
1081 p_journal_rec.accounting_date,
1082 p_journal_rec.currency,
1083 sysdate,
1084 NVL(g_parameter_rec.user_id,-1),
1085 sysdate,
1086 NVL(g_parameter_rec.user_id,-1),
1087 g_login_id,
1088 DECODE(sign(p_journal_rec.accounted_cr),-1,
1089 (abs(p_journal_rec.accounted_cr) +
1090 NVL(DECODE(sign(p_journal_rec.accounted_dr),
1091 1,p_journal_rec.accounted_dr,NULL),0)),
1092 DECODE(sign(p_journal_rec.accounted_dr),-1,
1093 NULL,p_journal_rec.accounted_dr)),
1094 DECODE(sign(p_journal_rec.accounted_dr),-1,
1095 (abs(p_journal_rec.accounted_dr) +
1096 NVL(DECODE(sign(p_journal_rec.accounted_cr),
1097 1,p_journal_rec.accounted_cr,NULL),0)),
1098 DECODE(sign(p_journal_rec.accounted_cr),-1,
1099 NULL,p_journal_rec.accounted_cr)),
1100 DECODE(sign(p_journal_rec.entered_cr),-1,
1101 (abs(p_journal_rec.entered_cr) +
1102 NVL(DECODE(sign(p_journal_rec.entered_dr),
1103 1,p_journal_rec.entered_dr,NULL),0)),
1104 DECODE(sign(p_journal_rec.entered_dr),-1,
1105 NULL,p_journal_rec.entered_dr)),
1106 DECODE(sign(p_journal_rec.entered_dr),-1,
1107 (abs(p_journal_rec.entered_dr) +
1108 NVL(DECODE(sign(p_journal_rec.entered_cr),
1109 1,p_journal_rec.entered_cr,NULL),0)),
1110 DECODE(sign(p_journal_rec.entered_cr),-1,
1111 NULL,p_journal_rec.entered_cr)) );
1112
1113 BEGIN
1114
1115 SELECT 'TRUE'
1116 INTO g_error_exists
1117 FROM DUAL
1118 WHERE EXISTS (SELECT '1'
1119 FROM jl_co_gl_conc_errs jlcgce
1120 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
1121 AND jlcgce.je_line_num = p_journal_rec.je_line_num);
1122
1123 EXCEPTION
1124 WHEN no_data_found THEN
1125 NULL;
1126 END;
1127
1128 IF NVL(g_error_exists,'FALSE') = 'TRUE' THEN
1129 DELETE FROM jl_co_gl_trx jlcgt
1130 WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
1131 AND jlcgt.je_line_num = p_journal_rec.je_line_num;
1132 ELSE
1133 UPDATE gl_je_lines gljl
1134 SET co_processed_flag = 'Y'
1135 WHERE gljl.je_header_id = p_journal_rec.je_header_id
1136 AND gljl.je_line_num = p_journal_rec.je_line_num
1137 AND EXISTS (SELECT 'Y'
1138 FROM jl_co_gl_trx jlcgt
1139 WHERE jlcgt.je_header_id = gljl.je_header_id
1140 AND jlcgt.je_line_num = gljl.je_line_num);
1141
1142 END IF;
1143 END IF;
1144
1145 END IF;
1146
1147 COMMIT;
1148
1149 EXCEPTION
1150 WHEN no_data_found THEN
1151 -- import references don't exist or are summarized for journal
1152 Generate_GL_trx(g_journal_rec); -- Create with NIT 0
1153
1154 WHEN others THEN
1155 g_error_code := SQLCODE;
1156 g_error_text := SUBSTR(SQLERRM,1,240);
1157 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_PO_trx:'|| g_error_text);
1158 RAISE;
1159
1160 END Generate_PO_trx;
1161
1162
1163 PROCEDURE Generate_AR_trx
1164 (p_journal_rec IN journals%ROWTYPE ) IS
1165
1166 CURSOR ar_detail_lines IS
1167 SELECT DECODE(SIGN(lnk.unrounded_accounted_cr),-1,
1168 DECODE(SIGN(lnk.unrounded_accounted_dr),-1,ABS(lnk.unrounded_accounted_dr),null),
1169 lnk.unrounded_accounted_cr) ACCOUNTED_CR,
1170 DECODE(SIGN(lnk.unrounded_accounted_dr),-1,
1171 DECODE(SIGN(lnk.unrounded_accounted_cr),-1,ABS(lnk.unrounded_accounted_cr),null),
1172 lnk.unrounded_accounted_dr) ACCOUNTED_DR,
1173 DECODE(SIGN(lnk.unrounded_entered_dr),-1,
1174 DECODE(SIGN(lnk.unrounded_entered_cr),-1, ABS(lnk.unrounded_entered_cr), null),
1175 lnk.unrounded_entered_dr) ENTERED_DR,
1176 DECODE(SIGN(lnk.unrounded_entered_cr),-1,
1177 DECODE(SIGN(lnk.unrounded_entered_dr),-1, ABS(lnk.unrounded_entered_dr), null),
1178 lnk.unrounded_entered_cr) ENTERED_CR,
1179 ent.source_id_int_1,
1180 ent.transaction_number,
1181 ent.entity_code,
1182 ael.party_id,
1183 et.event_class_code
1184 FROM xla_transaction_entities ent,
1185 xla_ae_headers AEH,
1186 xla_ae_lines AEL,
1187 xla_distribution_links LNK, --bug 7169346
1188 gl_import_references R,
1189 xla_event_types_b et
1190 WHERE
1191 ent.application_id = 222
1192 and (ent.ledger_id = g_parameter_rec.set_of_books_id
1193 or ent.ledger_id in (SELECT from_ledger_id
1194 FROM gl_consolidation
1195 WHERE to_ledger_id = g_parameter_rec.set_of_books_id))
1196
1197 -- and ent.entity_code = 'TRANSACTIONS'
1198 --and i.invoice_id = ent.source_id_int_1
1199 and ent.entity_id = aeh.entity_id
1200 AND AEH.ledger_id = ent.ledger_id
1201 and aeh.ae_header_id = ael.ae_header_id
1202 and ael.application_id = 222
1203 AND R.gl_sl_link_id = AEL.gl_sl_link_id
1204 AND R.je_header_id = p_journal_rec.je_header_id
1205 AND R.je_line_num = p_journal_rec.je_line_num
1206 AND ael.ae_header_id = lnk.ae_header_id --bug 7169346
1207 AND ael.ae_line_num = lnk.ae_line_num --bug 7169346
1208 AND lnk.application_id = 222 --bug 7169346
1209 AND et.event_type_code = aeh.event_type_code;
1210
1211 /*
1212 CURSOR ar_detail_lines IS
1213 SELECT reference_1,
1214 reference_2,
1215 reference_3,
1216 reference_4,
1217 reference_5,
1218 reference_6,
1219 reference_7,
1220 reference_8,
1221 reference_9,
1222 reference_10
1223 FROM gl_import_references glir
1224 WHERE glir.je_header_id = p_journal_rec.je_header_id
1225 AND glir.je_line_num = p_journal_rec.je_line_num;
1226 */
1227 l_acc_dr gl_je_lines.accounted_dr%TYPE;
1228 l_acc_cr gl_je_lines.accounted_cr%TYPE;
1229 l_ent_dr gl_je_lines.entered_dr%TYPE;
1230 l_ent_cr gl_je_lines.entered_cr%TYPE;
1231 l_customer_num po_vendors.segment1%TYPE;
1232 l_identifier_type VARCHAR2(30);
1233 l_country_code varchar2(30);
1234 l_branch_country_code varchar2(60);
1235 l_party_id number;
1236 l_receipt_id number;
1237
1238 BEGIN
1239
1240 g_error_exists := 'FALSE';
1241
1242 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_AR_trx(+)');
1243
1244 SELECT p_journal_rec.je_header_id,
1245 p_journal_rec.je_line_num,
1246 p_journal_rec.ext_nit
1247 INTO g_gl_je_rec
1248 FROM DUAL;
1249 FND_FILE.PUT_LINE(FND_FILE.log,'je_header_id, je_line_num : '||to_char(p_journal_rec.je_header_id)
1250 ||'-'||to_char(p_journal_rec.je_line_num));
1251
1252 FOR ar_trx IN ar_detail_lines LOOP
1253
1254 g_nit_rec := NULL;
1255 l_customer_num := NULL;
1256 l_identifier_type := 'JL_CO_GL_CUSTOMER';
1257
1258 IF ar_trx.entity_code IN ('TRANSACTIONS',
1259 'RECEIPTS',
1260 'ADJUSTMENTS') THEN
1261 l_ent_dr := ar_trx.entered_dr; --bug 7169346
1262 l_ent_cr := ar_trx.entered_cr; --bug 7169346
1263 l_acc_dr := ar_trx.accounted_dr; --bug 7169346
1264 l_acc_cr := ar_trx.accounted_cr; --bug 7169346
1265
1266 END IF;
1267
1268 IF ( ar_trx.event_class_code = 'MISC_RECEIPT' ) THEN
1269 SELECT nit.nit_id,
1270 substr(party.jgzz_fiscal_code,1,14),
1271 br.bank_name,
1272 party.country, -- nit type
1273 substr(party.jgzz_fiscal_code,15,1),
1274 br.bank_name
1275 INTO g_nit_rec.nit_id,
1276 g_nit_rec.nit,
1277 g_nit_rec.nit_name,
1278 l_branch_country_code, --g_nit_rec.nit_type,
1279 g_nit_rec.verifying_digit,
1280 g_gl_je_rec.identifier
1281 FROM jl_co_gl_nits nit,
1282 hz_parties party,
1283 ce_bank_branches_v br,
1284 ce_bank_accounts ce_accts,
1285 ce_bank_acct_uses_all acctuse,
1286 ar_cash_receipts_all arcash
1287 WHERE arcash.cash_receipt_id = ar_trx.source_id_int_1
1288 AND arcash.set_of_books_id = g_parameter_rec.set_of_books_id
1289 AND acctuse.bank_acct_use_id = arcash.remit_bank_acct_use_id
1290 AND acctuse.bank_account_id = ce_accts.bank_account_id
1291 AND ce_accts.bank_branch_id = br.branch_party_id
1292 AND br.bank_party_id = party.party_id
1293 AND nit.nit = substr(party.jgzz_fiscal_code,1,14);
1294
1295 l_country_code := fnd_profile.value ('JGZZ_COUNTRY_CODE');
1296
1297 FND_FILE.PUT_LINE(FND_FILE.log,'l_country_code - l_branch_country_code : '
1298 ||l_country_code||'--'||l_branch_country_code);
1299
1300 IF l_country_code = l_branch_country_code THEN
1301 g_nit_rec.nit_type := 'LEGAL_ENTITY';
1302 ELSE
1303 g_nit_rec.nit_type := 'FOREIGN_ENTITY';
1304 END IF;
1305
1306 FND_FILE.PUT_LINE(FND_FILE.log,'g_nit_rec.nit_type : '||g_nit_rec.nit_type);
1307 l_identifier_type := 'JL_CO_GL_BANK';
1308
1309 ELSIF (( l_customer_num IS NULL ) AND
1310 ( nvl(ar_trx.event_class_code,'$') <> 'MISC_RECEIPT' )) THEN
1311
1312 BEGIN
1313
1314 SELECT jlcgn.nit_id,
1315 REPLACE(rac.jgzz_fiscal_code,'-'),
1316 rac.party_name,
1317 SUBSTR(custacct.global_attribute10,1,30),
1318 custacct.global_attribute12,
1319 rac.party_number
1320 INTO g_nit_rec.nit_id,
1321 g_nit_rec.nit,
1322 g_nit_rec.nit_name,
1323 g_nit_rec.nit_type,
1324 g_nit_rec.verifying_digit,
1325 l_customer_num
1326 FROM jl_co_gl_nits jlcgn,
1327 hz_parties rac,
1328 hz_cust_accounts custacct
1329 WHERE custacct.cust_account_id = ar_trx.party_id
1330 AND REPLACE(rac.jgzz_fiscal_code,'-') = jlcgn.nit(+)
1331 AND custacct.party_id=rac.party_id;
1332
1333 FND_FILE.PUT_LINE(FND_FILE.log,'Non Misc : g_nit_rec : '||g_nit_rec.nit||'-'||g_nit_rec.nit_name);
1334 EXCEPTION
1335 WHEN no_data_found THEN
1336
1337 SELECT nit_id,
1338 nit,
1339 name,
1340 type,
1341 verifying_digit
1342 INTO g_nit_rec.nit_id,
1343 g_nit_rec.nit,
1344 g_nit_rec.nit_name,
1345 g_nit_rec.nit_type,
1346 g_nit_rec.verifying_digit
1347 FROM jl_co_gl_nits
1348 WHERE nit_id = g_default_nit_id;
1349 FND_FILE.PUT_LINE(FND_FILE.log,'Exception Non Misc : g_nit_rec : '||g_nit_rec.nit||'-'||g_nit_rec.nit_name);
1350
1351 END;
1352
1353 g_gl_je_rec.identifier := l_customer_num;
1354
1355 END IF;
1356 FND_FILE.PUT_LINE(FND_FILE.log,'Call To Validate_NIT in Generate_AR :'||g_gl_je_rec.identifier);
1357
1358 IF Validate_NIT(g_nit_rec,l_identifier_type) THEN
1359 FND_FILE.PUT_LINE(FND_FILE.log,'After Validate :'||g_gl_je_rec.identifier);
1360
1361 INSERT INTO jl_co_gl_trx (transaction_id,
1362 process_id,
1363 set_of_books_id,
1364 code_combination_id,
1365 account_code,
1366 nit_id,
1367 period_name,
1368 period_year,
1369 period_num,
1370 je_batch_id,
1371 je_header_id,
1372 category,
1373 subledger_doc_number,
1374 je_line_num,
1375 document_number,
1376 accounting_date,
1377 currency_code,
1378 creation_date,
1379 created_by,
1380 last_update_date,
1381 last_updated_by,
1382 last_update_login,
1383 accounted_dr,
1384 accounted_cr,
1385 entered_dr,
1386 entered_cr)
1387 VALUES (jl_co_gl_trx_s.nextval,
1388 g_parameter_rec.cid,
1389 g_parameter_rec.set_of_books_id,
1390 p_journal_rec.code_combination_id,
1391 p_journal_rec.account_code,
1392 g_nit_rec.nit_id,
1393 p_journal_rec.period_name,
1394 g_period_year,
1395 g_period_num,
1396 p_journal_rec.je_batch_id,
1397 p_journal_rec.je_header_id,
1398 p_journal_rec.category,
1399 p_journal_rec.subl_doc_num,
1400 p_journal_rec.je_line_num,
1401 -- DECODE(ar_trx.reference_8,'ADJ',
1402 -- ar_trx.reference_5,ar_trx.reference_4),
1403 ar_trx.transaction_number,
1404 p_journal_rec.accounting_date,
1405 p_journal_rec.currency,
1406 sysdate,
1407 NVL(g_parameter_rec.user_id,-1),
1408 sysdate,
1409 NVL(g_parameter_rec.user_id,-1),
1410 g_login_id,
1411 l_acc_dr,
1412 l_acc_cr,
1413 l_ent_dr,
1414 l_ent_cr );
1415
1416 BEGIN
1417
1418 SELECT 'TRUE'
1419 INTO g_error_exists
1420 FROM DUAL
1421 WHERE EXISTS (SELECT '1'
1422 FROM jl_co_gl_conc_errs jlcgce
1423 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
1424 AND jlcgce.je_line_num = p_journal_rec.je_line_num);
1425
1426 EXCEPTION
1427 WHEN no_data_found THEN
1428 NULL;
1429 END;
1430
1431 IF NVL(g_error_exists,'FALSE') = 'TRUE' THEN
1432 DELETE FROM jl_co_gl_trx jlcgt
1433 WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
1434 AND jlcgt.je_line_num = p_journal_rec.je_line_num;
1435 ELSE
1436 UPDATE gl_je_lines gljl
1437 SET co_processed_flag = 'Y'
1438 WHERE gljl.je_header_id = p_journal_rec.je_header_id
1439 AND gljl.je_line_num = p_journal_rec.je_line_num
1440 AND EXISTS (SELECT 'Y'
1441 FROM jl_co_gl_trx jlcgt
1442 WHERE jlcgt.je_header_id = gljl.je_header_id
1443 AND jlcgt.je_line_num = gljl.je_line_num);
1444
1445 END IF;
1446
1447 END IF;
1448
1449 END LOOP;
1450
1451 -- since these are summary JE lines that are being processed,
1452 -- if any detail trx associated with the JE line fails then
1453 -- none of the trx should be processed
1454
1455 BEGIN
1456
1457 SELECT 'TRUE'
1458 INTO g_error_exists
1459 FROM DUAL
1460 WHERE EXISTS (SELECT '1'
1461 FROM jl_co_gl_conc_errs jlcgce
1462 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
1463 AND jlcgce.je_line_num = p_journal_rec.je_line_num);
1464
1465 EXCEPTION
1466 WHEN no_data_found THEN
1467 NULL;
1468 END;
1469
1470 IF NVL(g_error_exists,'FALSE') = 'TRUE' THEN
1471 UPDATE gl_je_lines gljl
1472 SET co_processed_flag = NULL
1473 WHERE gljl.je_header_id = p_journal_rec.je_header_id
1474 AND gljl.je_line_num = p_journal_rec.je_line_num
1475 AND EXISTS (SELECT 'Y'
1476 FROM jl_co_gl_trx jlcgt
1477 WHERE jlcgt.je_header_id = gljl.je_header_id
1478 AND jlcgt.je_line_num = gljl.je_line_num);
1479
1480 DELETE FROM jl_co_gl_trx jlcgt
1481 WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
1482 AND jlcgt.je_line_num = p_journal_rec.je_line_num;
1483 END IF;
1484
1485 COMMIT;
1486
1487 EXCEPTION
1488 WHEN no_data_found THEN
1489 -- import references don't exist or are summarized for journal
1490 Generate_GL_trx(g_journal_rec); -- Create with NIT 0
1491
1492 WHEN others THEN
1493 g_error_code := SQLCODE;
1494 g_error_text := SUBSTR(SQLERRM,1,240);
1495 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_AR_trx:'|| g_error_text);
1496 RAISE;
1497 END Generate_AR_trx;
1498
1499
1500 PROCEDURE Generate_Non_Oracle_trx(p_journal_rec IN journals%ROWTYPE) IS
1501
1502 BEGIN
1503
1504 SELECT p_journal_rec.extgl_nit_id,
1505 p_journal_rec.ext_nit,
1506 p_journal_rec.ext_nit_name,
1507 p_journal_rec.ext_nit_type,
1508 p_journal_rec.ext_nit_v_digit
1509 INTO g_nit_rec
1510 FROM DUAL;
1511
1512 BEGIN
1513 SELECT nit_id
1514 INTO g_nit_rec.nit_id
1515 FROM jl_co_gl_nits jlcgn
1516 WHERE nit = p_journal_rec.ext_nit;
1517
1518 EXCEPTION
1519 WHEN no_data_found THEN
1520 NULL;
1521 END;
1522
1523 SELECT p_journal_rec.je_header_id,
1524 p_journal_rec.je_line_num,
1525 p_journal_rec.ext_nit
1526 INTO g_gl_je_rec FROM DUAL;
1527
1528 IF Validate_NIT(g_nit_rec,'JL_CO_GL_NIT') THEN
1529 NULL;
1530 ELSE
1531 -- if NIT is not valid we are creating transactions with
1532 -- NIT 0 for Non Oracle source since the users are unable to
1533 -- correct the nit information via the JE form. But we call this
1534 -- function so that users can see the kind of errors associated
1535 -- with the interfaced records */
1536 g_nit_rec.nit_id := g_default_nit_id;
1537 END IF;
1538
1539 INSERT INTO jl_co_gl_trx (transaction_id,
1540 process_id,
1541 set_of_books_id,
1542 code_combination_id,
1543 account_code,
1544 nit_id,
1545 period_name,
1546 period_year,
1547 period_num,
1548 je_batch_id,
1549 je_header_id,
1550 category,
1551 subledger_doc_number,
1552 je_line_num,
1553 document_number,
1554 accounting_date,
1555 currency_code,
1556 creation_date,
1557 created_by,
1558 last_update_date,
1559 last_updated_by,
1560 last_update_login,
1561 accounted_dr,
1562 accounted_cr,
1563 entered_dr,
1564 entered_cr)
1565 VALUES (jl_co_gl_trx_s.nextval,
1566 g_parameter_rec.cid,
1567 g_parameter_rec.set_of_books_id,
1568 p_journal_rec.code_combination_id,
1569 p_journal_rec.account_code,
1570 NVL(g_nit_rec.nit_id,g_default_nit_id),
1571 p_journal_rec.period_name,
1572 g_period_year,
1573 g_period_num,
1574 p_journal_rec.je_batch_id,
1575 p_journal_rec.je_header_id,
1576 p_journal_rec.category,
1577 p_journal_rec.subl_doc_num,
1578 p_journal_rec.je_line_num,
1579 p_journal_rec.ext_doc_num,
1580 p_journal_rec.accounting_date,
1581 p_journal_rec.currency,
1582 sysdate,
1583 NVL(g_parameter_rec.user_id,-1),
1584 sysdate,
1585 NVL(g_parameter_rec.user_id,-1),
1586 g_login_id,
1587 DECODE(sign(p_journal_rec.accounted_cr),-1,
1588 (abs(p_journal_rec.accounted_cr) +
1589 NVL(DECODE(sign(p_journal_rec.accounted_dr),1,
1590 p_journal_rec.accounted_dr,NULL),0)),
1591 DECODE(sign(p_journal_rec.accounted_dr),-1,
1592 NULL,p_journal_rec.accounted_dr)),
1593 DECODE(sign(p_journal_rec.accounted_dr),-1,
1594 (abs(p_journal_rec.accounted_dr) +
1595 NVL(DECODE(sign(p_journal_rec.accounted_cr),1,
1596 p_journal_rec.accounted_cr,NULL),0)),
1597 DECODE(sign(p_journal_rec.accounted_cr),-1,
1598 NULL,p_journal_rec.accounted_cr)),
1599 DECODE(sign(p_journal_rec.entered_cr),-1,
1600 (abs(p_journal_rec.entered_cr) +
1601 NVL(DECODE(sign(p_journal_rec.entered_dr),1,
1602 p_journal_rec.entered_dr,NULL),0)),
1603 DECODE(sign(p_journal_rec.entered_dr),-1,
1604 NULL,p_journal_rec.entered_dr)),
1605 DECODE(sign(p_journal_rec.entered_dr),-1,
1606 (abs(p_journal_rec.entered_dr) +
1607 NVL(DECODE(sign(p_journal_rec.entered_cr),1,
1608 p_journal_rec.entered_cr,NULL),0)),
1609 DECODE(sign(p_journal_rec.entered_cr),-1,
1610 NULL,p_journal_rec.entered_cr)) );
1611
1612 /* bug 7045429
1613
1614 BEGIN
1615
1616 SELECT 'TRUE'
1617 INTO g_error_exists
1618 FROM DUAL
1619 WHERE EXISTS (SELECT '1'
1620 FROM jl_co_gl_conc_errs jlcgce
1621 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
1622 AND jlcgce.je_line_num = p_journal_rec.je_line_num);
1623
1624 EXCEPTION
1625 WHEN no_data_found THEN
1626 NULL;
1627 END;
1628
1629 IF NVL(g_error_exists,'FALSE') = 'TRUE' THEN
1630 DELETE FROM jl_co_gl_trx jlcgt
1631 WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
1632 AND jlcgt.je_line_num = p_journal_rec.je_line_num;
1633 ELSE
1634 UPDATE gl_je_lines gljl
1635 SET co_processed_flag = 'Y'
1636 WHERE gljl.je_header_id = p_journal_rec.je_header_id
1637 AND gljl.je_line_num = p_journal_rec.je_line_num
1638 AND EXISTS (SELECT 'Y'
1639 FROM jl_co_gl_trx jlcgt
1640 WHERE jlcgt.je_header_id = gljl.je_header_id
1641 AND jlcgt.je_line_num = gljl.je_line_num);
1642
1643 END IF;
1644 */
1645
1646 COMMIT;
1647
1648 EXCEPTION
1649 WHEN others THEN
1650 BEGIN
1651 g_error_code := SQLCODE;
1652 g_error_text := SUBSTR(SQLERRM,1,240);
1653 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_Non_Oracle_trx:'
1654 || g_error_text);
1655 RAISE;
1656 END;
1657
1658 END Generate_Non_Oracle_trx;
1659
1660
1661
1662 PROCEDURE create_balances(
1663 p_period IN VARCHAR2,
1664 p_period_year IN NUMBER,
1665 p_period_num IN NUMBER,
1666 p_sobid IN NUMBER
1667 ) IS
1668 l_period_year number(15);
1669 l_pre_period_num number(15);
1670 l_pre_period varchar2(15);
1671 l_bal_count number;
1672
1673 BEGIN
1674
1675
1676 SELECT count(*)
1677 INTO l_bal_count
1678 FROM jl_co_gl_balances bal
1679 WHERE bal.period_name = p_period
1680 AND bal.period_year = p_period_year
1681 AND bal.set_of_books_id = p_sobid
1682 AND rownum = 1;
1683
1684 IF l_bal_count = 0 THEN
1685 BEGIN
1686 SELECT max((bal.period_year * 100 + bal.period_num))
1687 INTO l_pre_period_num
1688 FROM jl_co_gl_balances bal
1689 WHERE (bal.period_year * 100 + bal.period_num) < p_period_year * 100 + p_period_num
1690 AND bal.set_of_books_id = p_sobid;
1691
1692 INSERT INTO jl_co_gl_balances (
1693 balance_id,
1694 set_of_books_id,
1695 code_combination_id,
1696 account_code,
1697 nit_id,
1698 period_name,
1699 period_num,
1700 period_year,
1701 currency_code,
1702 begin_balance_cr,
1703 begin_balance_dr,
1704 period_net_cr,
1705 period_net_dr,
1706 creation_date,
1707 created_by,
1708 last_update_date,
1709 last_updated_by,
1710 last_update_login)
1711 (select jl_co_gl_balances_s.nextval,
1712 bal.set_of_books_id,
1713 bal.code_combination_id,
1714 bal.account_code,
1715 bal.nit_id,
1716 p_period,
1717 p_period_num,
1718 p_period_year,
1719 bal.currency_code,
1720 NVL(bal.begin_balance_cr,0)+NVL(bal.period_net_cr,0),
1721 NVL(bal.begin_balance_dr,0)+NVL(bal.period_net_dr,0),
1722 0,
1723 0,
1724 sysdate,
1725 bal.created_by,
1726 sysdate,
1727 bal.last_updated_by,
1728 bal.last_update_login
1729 FROM jl_co_gl_balances bal
1730 WHERE (bal.period_year * 100 + bal.period_num) = l_pre_period_num
1731 AND bal.set_of_books_id = p_sobid);
1732
1733 FND_FILE.PUT_LINE(FND_FILE.log,'p_sobid :'||to_char(p_sobid)||'-'||to_char(l_pre_period_num));
1734
1735
1736 EXCEPTION
1737 WHEN others THEN
1738 BEGIN
1739 g_error_code := SQLCODE;
1740 g_error_text := SUBSTR(SQLERRM,1,240);
1741 FND_FILE.PUT_LINE(FND_FILE.log,'create_balances:'
1742 || g_error_text);
1743 RAISE;
1744 END;
1745 END;
1746 END IF;
1747
1748 END create_balances;
1749
1750
1751 PROCEDURE Calculate_Balance(p_cid IN NUMBER,
1752 p_sobid IN NUMBER,
1753 p_userid IN NUMBER) IS
1754
1755 l_balance_id jl_co_gl_balances.balance_id%TYPE;
1756 l_begin_bal_dr_prior_period jl_co_gl_balances.begin_balance_dr%TYPE;
1757 l_begin_bal_cr_prior_period jl_co_gl_balances.begin_balance_cr%TYPE;
1758 l_period_net_dr_prior_period jl_co_gl_balances.period_net_dr%TYPE;
1759 l_period_net_cr_prior_period jl_co_gl_balances.period_net_cr%TYPE;
1760 l_begin_bal_dr jl_co_gl_balances.begin_balance_dr%TYPE;
1761 l_begin_bal_cr jl_co_gl_balances.begin_balance_cr%TYPE;
1762 l_period_set_name gl_periods.period_set_name%TYPE;
1763 l_max_period_num gl_periods.period_num%TYPE;
1764
1765 -- right now the currency field in jl_co_gl_balances only holds
1766 -- functional_currency_code. But in the future if the functionality
1767 -- is changed to hold balances for multiple currencies then curreny_code
1768 -- should be added to the BALANCE_TRX cursor and in other sql joins also
1769
1770 CURSOR balance_trx IS
1771 SELECT jlcgt.set_of_books_id sobid,
1772 jlcgt.nit_id nitid,
1773 jlcgt.period_name period_name,
1774 jlcgt.code_combination_id ccid,
1775 jlcgt.account_code acccode,
1776 jlcgt.period_year peryear,
1777 jlcgt.period_num pernum,
1778 glcc.account_type acctype,
1779 NVL(sum(jlcgt.accounted_dr),0) acc_dr,
1780 NVL(sum(jlcgt.accounted_cr),0) acc_cr
1781 FROM gl_code_combinations glcc, jl_co_gl_trx jlcgt
1782 WHERE jlcgt.process_id IN ( SELECT process_id
1783 FROM jl_co_gl_conc_ctrl
1784 WHERE NVL(balance_calculated,'N') <> 'Y'
1785 AND set_of_books_id
1786 = g_parameter_rec.set_of_books_id)
1787 AND jlcgt.code_combination_id = glcc.code_combination_id
1788 GROUP BY jlcgt.set_of_books_id,
1789 jlcgt.nit_id,
1790 jlcgt.period_name,
1791 jlcgt.code_combination_id,
1792 jlcgt.account_code,
1793 jlcgt.period_year,
1794 jlcgt.period_num,
1795 glcc.account_type ;
1796
1797 BEGIN -- Calculate balances
1798
1799
1800 FOR trx IN balance_trx LOOP
1801
1802 l_balance_id := 0;
1803 l_begin_bal_dr_prior_period := 0;
1804 l_begin_bal_cr_prior_period := 0;
1805 l_period_net_dr_prior_period := 0;
1806 l_period_net_cr_prior_period := 0;
1807 l_begin_bal_dr := 0;
1808 l_begin_bal_cr := 0;
1809
1810 SELECT period_set_name,
1811 currency_code
1812 INTO g_period_set_name,g_func_currency
1813 FROM gl_sets_of_books glsob
1814 WHERE glsob.set_of_books_id = trx.sobid;
1815
1816 BEGIN
1817
1818 SELECT balance_id
1819 INTO l_balance_id
1820 FROM jl_co_gl_balances jlcgb
1821 WHERE jlcgb.set_of_books_id = trx.sobid
1822 AND jlcgb.nit_id = trx.nitid
1823 AND jlcgb.code_combination_id = trx.ccid
1824 AND jlcgb.period_name = trx.period_name;
1825
1826 EXCEPTION
1827 WHEN no_data_found THEN
1828 NULL;
1829
1830 END;
1831
1832 IF l_balance_id = 0 THEN
1833 -- No balance record exists - insert new record
1834
1835 -- calculate the begin_bal for new record by adding the
1836 -- begin_bal AND period_net_activity FROM the prior period which
1837 -- could even be more than a year behind cause we dont create
1838 -- balance records IN a specific period unless there is activity
1839 -- IN that period. This decision was made to avoid creating
1840 -- too many records IN jl_co_balances (unlike gl_balances)
1841
1842 FND_FILE.PUT_LINE(FND_FILE.log,
1843 'If l_balance_id is 0 then insert ');
1844
1845 BEGIN
1846
1847 SELECT begin_balance_dr,
1848 begin_balance_cr,
1849 period_net_dr,
1850 period_net_cr
1851 INTO l_begin_bal_dr_prior_period,
1852 l_begin_bal_cr_prior_period,
1853 l_period_net_dr_prior_period,
1854 l_period_net_cr_prior_period
1855 FROM jl_co_gl_balances jlcgb
1856 WHERE jlcgb.nit_id = trx.nitid
1857 AND jlcgb.set_of_books_id = trx.sobid
1858 AND jlcgb.code_combination_id = trx.ccid
1859 AND (jlcgb.period_year * 100 + jlcgb.period_num) =
1860 (SELECT max(jlcgb1.period_year * 100 +
1861 jlcgb1.period_num)
1862 FROM jl_co_gl_balances jlcgb1
1863 WHERE jlcgb1.nit_id = trx.nitid
1864 AND jlcgb1.set_of_books_id = trx.sobid
1865 AND jlcgb1.code_combination_id = trx.ccid
1866 AND (jlcgb1.period_year * 100 + jlcgb1.period_num) <
1867 (trx.peryear * 100 + trx.pernum)
1868 AND jlcgb1.period_year BETWEEN
1869 DECODE(trx.acctype,
1870 'R',trx.peryear, 'E',trx.peryear,
1871 trx.peryear - 200)
1872 AND trx.peryear );
1873
1874 -- if acctype IS O,A or L then prior period could be
1875 -- FROM prior year but if R or E then it would have to be
1876 -- FROM same fiscal year as the period being considered
1877
1878 EXCEPTION
1879 WHEN no_data_found THEN
1880 NULL;
1881 -- if this IS the 1st period for which balance record is
1882 -- being inserted then there wont be a prior period AND
1883 -- the previous sql statements will return 0 rows
1884 -- but you still want the following statements to be executed
1885 END;
1886
1887 l_begin_bal_dr := l_begin_bal_dr_prior_period +
1888 l_period_net_dr_prior_period ;
1889 l_begin_bal_cr := l_begin_bal_cr_prior_period +
1890 l_period_net_cr_prior_period ;
1891
1892 INSERT INTO jl_co_gl_balances (
1893 balance_id,
1894 set_of_books_id,
1895 code_combination_id,
1896 account_code,
1897 nit_id,
1898 period_name,
1899 period_num,
1900 period_year,
1901 currency_code,
1902 begin_balance_dr,
1903 begin_balance_cr,
1904 period_net_dr,
1905 period_net_cr,
1906 creation_date,
1907 created_by,
1908 last_update_date,
1909 last_updated_by,
1910 last_update_login)
1911 VALUES ( jl_co_gl_balances_s.nextval,
1912 trx.sobid,
1913 trx.ccid,
1914 trx.acccode,
1915 trx.nitid,
1916 trx.period_name,
1917 trx.pernum,
1918 trx.peryear,
1919 g_func_currency,
1920 l_begin_bal_dr,
1921 l_begin_bal_cr,
1922 trx.acc_dr,
1923 trx.acc_cr,
1924 sysdate,
1925 NVL(p_userid,-1),
1926 sysdate,
1927 NVL(p_userid,-1),
1928 g_login_id);
1929
1930 ELSE
1931 -- Balance record exists - Update period_net of current record
1932 FND_FILE.PUT_LINE(FND_FILE.log,
1933 'If l_balance_id is non 0 then update :' ||to_char(trx.acc_dr));
1934 UPDATE jl_co_gl_balances jlcgb
1935 SET period_net_dr = (period_net_dr + trx.acc_dr) ,
1936 period_net_cr = (period_net_cr + trx.acc_cr),
1937 last_update_date = sysdate,
1938 last_updated_by = p_userid,
1939 last_update_login = g_login_id
1940 WHERE jlcgb.set_of_books_id = trx.sobid
1941 AND jlcgb.nit_id = trx.nitid
1942 AND jlcgb.code_combination_id = trx.ccid
1943 AND jlcgb.period_name = trx.period_name;
1944
1945 END IF;
1946
1947 -- Update begin balances of all future balance records for
1948 -- each balance_trx.
1949 -- For income statement accounts only the records in the same year
1950 -- as the transaction needs to be updated
1951
1952 UPDATE jl_co_gl_balances jlcgb
1953 SET begin_balance_dr = (begin_balance_dr + trx.acc_dr),
1954 begin_balance_cr = (begin_balance_cr + trx.acc_cr),
1955 last_update_date = sysdate,
1956 last_updated_by = p_userid,
1957 last_update_login = g_login_id
1958 WHERE jlcgb.nit_id = trx.nitid
1959 AND jlcgb.set_of_books_id = trx.sobid
1960 AND jlcgb.code_combination_id = trx.ccid
1961 AND (jlcgb.period_year * 100 + jlcgb.period_num) >
1962 (trx.peryear * 100 + trx.pernum)
1963 AND period_year BETWEEN trx.peryear AND
1964 DECODE(trx.acctype, 'R',trx.peryear,
1965 'E',trx.peryear,
1966 trx.peryear * 100);
1967
1968 END LOOP;
1969
1970 UPDATE jl_co_gl_conc_ctrl
1971 SET status = DECODE(process_id,p_cid,'P',status),
1972 balance_calculated = 'Y',
1973 last_update_date = sysdate,
1974 last_updated_by = p_userid,
1975 last_update_login = g_login_id
1976 WHERE NVL(balance_calculated,'N') <> 'Y'
1977 AND set_of_books_id = g_parameter_rec.set_of_books_id;
1978
1979 COMMIT;
1980
1981 EXCEPTION
1982 WHEN others THEN
1983 g_error_code := SQLCODE;
1984 g_error_text := SUBSTR(SQLERRM,1,240);
1985 FND_FILE.PUT_LINE(FND_FILE.log,'Calculate_Balance:'
1986 || g_error_text);
1987 RAISE;
1988
1989 END Calculate_Balance;
1990
1991
1992 PROCEDURE Reverse_Balance(p_rcid IN NUMBER, p_cid IN NUMBER,p_sobid IN NUMBER,
1993 p_userid IN NUMBER) IS
1994
1995 CURSOR reversal_trx IS
1996 SELECT jlcgt.set_of_books_id sobid,
1997 jlcgt.nit_id nitid,
1998 jlcgt.period_name period_name,
1999 jlcgt.code_combination_id ccid,
2000 jlcgt.account_code acccode,
2001 jlcgt.period_year peryear,
2002 jlcgt.period_num pernum,
2003 glcc.account_type acctype,
2004 NVL(sum(jlcgt.accounted_dr),0) acc_dr,
2005 NVL(sum(jlcgt.accounted_cr),0) acc_cr
2006 FROM gl_code_combinations glcc,
2007 jl_co_gl_trx jlcgt
2008 WHERE process_id = p_rcid
2009 AND jlcgt.code_combination_id = glcc.code_combination_id
2010 GROUP BY jlcgt.set_of_books_id,
2011 jlcgt.nit_id,
2012 jlcgt.period_name,
2013 jlcgt.code_combination_id,
2014 jlcgt.account_code,
2015 jlcgt.period_year,
2016 jlcgt.period_num,
2017 glcc.account_type;
2018
2019 BEGIN
2020
2021 -- UPDATE balances
2022
2023 FOR trx IN reversal_trx
2024 LOOP
2025
2026 UPDATE jl_co_gl_balances jlcgb
2027 -- period_net should be updated only for the purge period
2028 SET period_net_dr =
2029 (period_net_dr - DECODE(jlcgb.period_name, trx.period_name,
2030 trx.acc_dr,0)),
2031 period_net_cr =
2032 (period_net_cr - DECODE(jlcgb.period_name, trx.period_name,
2033 trx.acc_cr,0)),
2034 -- begin_balance for all future periods in the current year
2035 -- only needs to be corrected for Income Statement accounts,
2036 -- and all future periods for balance sheet accounts
2037 begin_balance_dr =
2038 (begin_balance_dr - DECODE(jlcgb.period_name, trx.period_name,
2039 0,trx.acc_dr)) ,
2040 begin_balance_cr =
2041 (begin_balance_cr - DECODE(jlcgb.period_name, trx.period_name,
2042 0,trx.acc_cr)),
2043 last_update_date = sysdate,
2044 last_updated_by = p_userid ,
2045 last_update_login = g_login_id
2046 WHERE jlcgb.set_of_books_id = trx.sobid
2047 AND jlcgb.nit_id = trx.nitid
2048 AND jlcgb.code_combination_id = trx.ccid
2049 AND jlcgb.period_name IN (SELECT period_name
2050 FROM gl_periods
2051 WHERE period_set_name = g_period_set_name
2052 AND (period_year * 100 + period_num) >=
2053 (trx.peryear * 100 + trx.pernum)
2054 AND period_year BETWEEN trx.peryear AND
2055 DECODE(trx.acctype,
2056 'R',trx.peryear,'E',trx.peryear,
2057 trx.peryear * 100));
2058
2059 -- After the update if the period_net_dr and cr amounts are 0
2060 -- delete the balance record so that one would be able to delete
2061 -- an invalid NIT via the Define Third Party form.
2062 -- The form does not let you delete a NIT if there are records
2063 -- in jl_co_gl_trx and jl_co_gl_balances for that NIT
2064
2065 DELETE FROM jl_co_gl_balances jlcgb
2066 WHERE jlcgb.set_of_books_id = trx.sobid
2067 AND jlcgb.nit_id = trx.nitid
2068 AND jlcgb.code_combination_id = trx.ccid
2069 AND jlcgb.period_name = trx.period_name
2070 AND jlcgb.period_net_dr = 0 AND jlcgb.period_net_cr = 0
2071 AND NOT EXISTS (SELECT 1 FROM jl_co_gl_trx jlcgt
2072 WHERE jlcgt.nit_id = trx.nitid
2073 AND jlcgt.code_combination_id = trx.ccid
2074 AND jlcgt.period_name = trx.period_name
2075 AND jlcgt.set_of_books_id = trx.sobid);
2076
2077 END LOOP; -- UPDATE balances
2078
2079 -- delete transactions
2080
2081 UPDATE gl_je_lines gljl
2082 SET co_processed_flag = 'N'
2083 WHERE co_processed_flag = 'Y'
2084 AND status = 'P'
2085 AND EXISTS (SELECT 1
2086 FROM jl_co_gl_trx jlcgt
2087 WHERE jlcgt.process_id = p_rcid
2088 AND jlcgt.je_header_id = gljl.je_header_id
2089 AND jlcgt.je_line_num = gljl.je_line_num );
2090
2091 DELETE FROM jl_co_gl_trx
2092 WHERE process_id = p_rcid ;
2093
2094 UPDATE jl_co_gl_conc_ctrl
2095 SET status = 'P',
2096 reversed_process_id = p_rcid,
2097 last_update_date = sysdate,
2098 last_updated_by = p_userid,
2099 last_update_login = g_login_id
2100 WHERE process_id = p_cid;
2101
2102 UPDATE jl_co_gl_conc_ctrl
2103 SET status = 'R',
2104 last_update_date = sysdate,
2105 last_updated_by = p_userid,
2106 last_update_login = g_login_id
2107 WHERE process_id = p_rcid;
2108
2109 COMMIT; -- Reversal is complete
2110
2111 EXCEPTION
2112 WHEN others THEN
2113 g_error_code := SQLCODE;
2114 g_error_text := SUBSTR(SQLERRM,1,240);
2115 FND_FILE.PUT_LINE(FND_FILE.log,'Reverse_Balance:'|| g_error_text);
2116 RAISE;
2117
2118 END Reverse_Balance;
2119
2120
2121 PROCEDURE Create_Trx_Balance(errbuf OUT NOCOPY VARCHAR2,
2122 retcode OUT NOCOPY NUMBER,
2123 p_proc_type IN VARCHAR2,
2124 p_sobid IN NUMBER,
2125 p_period IN VARCHAR2,
2126 p_rcid IN NUMBER,
2127 p_batchid IN NUMBER) IS
2128
2129 l_request_id NUMBER := 0;
2130 i NUMBER := 1;
2131 l_rows NUMBER := 0;
2132 l_message_text jl_co_gl_conc_errs.message_text%TYPE := NULL;
2133
2134 BEGIN
2135
2136 FND_FILE.PUT_LINE(FND_FILE.log,'Create_Trx_Balance: Start');
2137
2138 DELETE FROM jl_co_gl_conc_errs;
2139
2140 -- Find out which segment IS the natural account segment
2141 SELECT application_column_name,
2142 id_flex_num
2143 INTO g_account_segment,
2144 g_chart_of_accounts_id
2145 FROM fnd_segment_attribute_values fndsav
2146 WHERE fndsav.id_flex_code = 'GL#'
2147 AND fndsav.segment_attribute_type = 'GL_ACCOUNT'
2148 AND fndsav.attribute_value = 'Y'
2149 AND application_id = 101
2150 AND fndsav.id_flex_num = (SELECT chart_of_accounts_id
2151 FROM gl_sets_of_books
2152 WHERE set_of_books_id = p_sobid);
2153
2154 -- Generate Process record
2155
2156
2157 SELECT TO_NUMBER(NVL(fnd_profile.value('LOGIN_ID'),-1))
2158 INTO g_login_id
2159 FROM DUAL;
2160
2161 SELECT jl_co_gl_conc_ctrl_s.nextval,
2162 p_sobid,
2163 TO_NUMBER(NVL(fnd_profile.value('USER_ID') ,-1)),
2164 p_rcid
2165 INTO g_parameter_rec
2166 FROM dual;
2167
2168 INSERT INTO jl_co_gl_conc_ctrl (
2169 process_id,
2170 set_of_books_id,
2171 period_name,
2172 reversed_process_id,
2173 status,
2174 creation_date,
2175 created_by,
2176 last_update_date,
2177 last_updated_by,
2178 last_update_login,
2179 balance_calculated)
2180 VALUES ( jl_co_gl_conc_ctrl_s.currval,
2181 p_sobid,
2182 DECODE(p_rcid,NULL,p_period,NULL),
2183 p_rcid,
2184 'E',
2185 sysdate,
2186 nvl(g_parameter_rec.user_id,-1),
2187 sysdate,
2188 nvl(g_parameter_rec.user_id,-1),
2189 g_login_id,
2190 NULL);
2191
2192 COMMIT;
2193
2194 BEGIN
2195
2196 SELECT nit_id
2197 INTO g_default_nit_id
2198 FROM jl_co_gl_nits
2199 WHERE nit = '0';
2200
2201 EXCEPTION
2202 WHEN no_data_found THEN
2203 BEGIN
2204 INSERT INTO jl_co_gl_nits (
2205 nit_id,
2206 nit,
2207 type,
2208 verifying_digit,
2209 name,
2210 creation_date,
2211 created_by,
2212 last_update_date,
2213 last_updated_by,
2214 last_update_login)
2215 VALUES ( jl_co_gl_nits_s.nextval,
2216 0,
2217 'LEGAL_ENTITY',
2218 '0',
2219 'Default',
2220 sysdate,
2221 1,
2222 sysdate,
2223 1,
2224 null);
2225
2226 SELECT nit_id
2227 INTO g_default_nit_id
2228 FROM jl_co_gl_nits
2229 WHERE nit = '0';
2230 END;
2231 END;
2232
2233 SELECT period_set_name
2234 INTO g_period_set_name
2235 FROM gl_sets_of_books glsob
2236 WHERE glsob.set_of_books_id = p_sobid;
2237
2238 IF p_period is not null THEN
2239 SELECT period_num,
2240 period_year
2241 INTO g_period_num,
2242 g_period_year
2243 FROM gl_periods
2244 WHERE period_set_name = g_period_set_name
2245 AND period_name = p_period;
2246 END IF;
2247
2248 IF p_proc_type = 'GENERATE' THEN
2249
2250 -- Generate Transactions AND calculate balance
2251 FND_FILE.PUT_LINE(FND_FILE.log,
2252 'Create_Trx_Balance: Entering FOR LOOP');
2253
2254 FOR j_line IN journals(p_period,p_sobid,p_batchid) LOOP
2255 FND_FILE.PUT_LINE(FND_FILE.log,
2256 'p_period :'||p_period||'-'||to_char(p_sobid)||'-'||to_char(p_batchid)||j_line.source);
2257
2258 g_journal_rec := j_line;
2259
2260 IF (j_line.source IN ('Payables') AND
2261 (j_line.ref_10 IS NOT NULL OR
2262 (j_line.ref_2 IS NULL AND
2263 j_line.ref_3 IS NULL AND
2264 j_line.ref_4 IS NULL AND
2265 j_line.ref_5 IS NULL )) AND
2266 (j_line.reversed_je_header_id IS NULL)) THEN
2267 FND_FILE.PUT_LINE(FND_FILE.log, 'Call to Generate_AP_trx :');
2268
2269 Generate_AP_trx(g_journal_rec);
2270 ELSIF (j_line.source IN ('Purchasing') AND
2271 (j_line.ref_1 = 'PO') AND
2272 (j_line.reversed_je_header_id IS NULL)) THEN
2273
2274 Generate_PO_trx(g_journal_rec);
2275 ELSIF (j_line.source IN ('Receivables') AND
2276 (j_line.ref_10 IS NOT NULL OR
2277 (j_line.ref_2 IS NULL AND
2278 j_line.ref_3 IS NULL AND
2279 j_line.ref_4 IS NULL AND
2280 j_line.ref_5 IS NULL )) AND
2281 (j_line.reversed_je_header_id IS NULL)) THEN
2282 FND_FILE.PUT_LINE(FND_FILE.log, 'Call to Generate_AR_trx :');
2283 Generate_AR_trx(g_journal_rec);
2284 ELSIF (j_line.ref_10 IS NULL AND
2285 (j_line.ext_nit_type IS NOT NULL AND
2286 j_line.ext_nit_name IS NOT NULL AND
2287 j_line.ext_nit IS NOT NULL) AND
2288 (j_line.reversed_je_header_id IS NULL)) THEN
2289
2290 Generate_Non_Oracle_trx(g_journal_rec);
2291 ELSIF (j_line.reversed_je_header_id IS NOT NULL) THEN
2292
2293 reverse_rec_tbl(i).code_combination_id :=
2294 g_journal_rec.code_combination_id;
2295 reverse_rec_tbl(i).account_code :=
2296 g_journal_rec.account_code;
2297 reverse_rec_tbl(i).period_name :=
2298 g_journal_rec.period_name;
2299 reverse_rec_tbl(i).je_batch_id :=
2300 g_journal_rec.je_batch_id;
2301 reverse_rec_tbl(i).je_header_id :=
2302 g_journal_rec.je_header_id;
2303 reverse_rec_tbl(i).category :=
2304 g_journal_rec.category;
2305 reverse_rec_tbl(i).subl_doc_num :=
2306 g_journal_rec.subl_doc_num;
2307 reverse_rec_tbl(i).je_line_num :=
2308 g_journal_rec.je_line_num;
2309 reverse_rec_tbl(i).accounting_date :=
2310 g_journal_rec.accounting_date;
2311 reverse_rec_tbl(i).currency :=
2312 g_journal_rec.currency;
2313 reverse_rec_tbl(i).reversed_je_header_id :=
2314 g_journal_rec.reversed_je_header_id;
2315 reverse_rec_tbl(i).je_line_num :=
2316 g_journal_rec.je_line_num;
2317 i := i + 1;
2318 ELSE
2319 Generate_GL_trx(g_journal_rec);
2320 END IF;
2321 g_journal_rec := NULL;
2322
2323 END LOOP; -- journal CURSOR
2324
2325 -- Insert Reversed Journals
2326 l_rows := nvl(reverse_rec_tbl.last,0);
2327 FOR j_line IN 1..l_rows
2328 LOOP
2329 INSERT INTO jl_co_gl_trx(
2330 transaction_id,
2331 process_id,
2332 set_of_books_id,
2333 code_combination_id,
2334 account_code,
2335 nit_id,
2336 period_name,
2337 period_year,
2338 period_num,
2339 je_batch_id,
2340 je_header_id,
2341 category,
2342 subledger_doc_number,
2343 je_line_num,
2344 document_number,
2345 accounting_date,
2346 currency_code,
2347 creation_date,
2348 created_by,
2349 last_update_date,
2350 last_updated_by,
2351 last_update_login,
2352 accounted_dr,
2353 accounted_cr,
2354 entered_dr,
2355 entered_cr)
2356 (SELECT jl_co_gl_trx_s.nextval,
2357 g_parameter_rec.cid,
2358 g_parameter_rec.set_of_books_id,
2359 reverse_rec_tbl(j_line).code_combination_id,
2360 reverse_rec_tbl(j_line).account_code,
2361 jlcgt.nit_id,
2362 reverse_rec_tbl(j_line).period_name,
2363 g_period_year,
2364 g_period_num,
2365 reverse_rec_tbl(j_line).je_batch_id,
2366 reverse_rec_tbl(j_line).je_header_id,
2367 reverse_rec_tbl(j_line).category,
2368 reverse_rec_tbl(j_line).subl_doc_num,
2369 reverse_rec_tbl(j_line).je_line_num,
2370 jlcgt.document_number,
2371 reverse_rec_tbl(j_line).accounting_date,
2372 reverse_rec_tbl(j_line).currency,
2373 sysdate,
2374 NVL(g_parameter_rec.user_id,-1),
2375 sysdate,
2376 NVL(g_parameter_rec.user_id,-1),
2377 g_login_id,
2378 jlcgt.accounted_cr,
2379 jlcgt.accounted_dr,
2380 jlcgt.entered_cr,
2381 jlcgt.entered_dr
2382 FROM jl_co_gl_trx jlcgt
2383 WHERE jlcgt.je_header_id
2384 = reverse_rec_tbl(j_line).reversed_je_header_id
2385 AND jlcgt.je_line_num
2386 = reverse_rec_tbl(j_line).je_line_num );
2387 END LOOP;
2388
2389 -- Now that all the transactions are created - calculate balance
2390 FND_FILE.PUT_LINE(FND_FILE.log,
2391 'Create_Trx_Balance: Calling Calculate Balance');
2392
2393
2394 Create_Balances( p_period,
2395 g_period_year,
2396 g_period_num ,
2397 p_sobid);
2398
2399 Calculate_Balance(g_parameter_rec.cid,
2400 p_sobid,
2401 g_parameter_rec.user_id);
2402
2403 -- Submit the Third Party Balances Error Report
2404
2405 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
2406 'JL',
2407 'JLCOGLNE',
2408 'Third Party Balances Error Report',
2409 '',
2410 FALSE,
2411 p_sobid,
2412 g_parameter_rec.cid);
2413 IF l_request_id = 0 THEN
2414 FND_FILE.PUT_LINE(FND_FILE.log,
2415 'CONC-REQUEST SUBMISSION FAILED');
2416 ELSE
2417 FND_MESSAGE.SET_NAME('SQLGL','GL_REQUEST_SUBMITTED');
2418 FND_MESSAGE.SET_TOKEN('REQUEST_ID',l_request_id,FALSE);
2419 l_message_text := FND_MESSAGE.GET;
2420 FND_FILE.PUT_LINE(FND_FILE.log,
2421 'Submitted Third Party Balances Error Report. '
2422 || l_message_text);
2423 END IF;
2424
2425 ELSIF p_proc_type = 'REVERSE' THEN
2426
2427 -- call Reverse Balances routine with the RCID
2428
2429 FND_FILE.PUT_LINE(FND_FILE.log,
2430 'Create_Trx_Balance: Calling Reverse Balance');
2431
2432 Reverse_Balance(p_rcid,
2433 g_parameter_rec.cid,
2434 p_sobid,
2435 g_parameter_rec.user_id);
2436 END IF; -- if p_proc_type
2437
2438 FND_FILE.PUT_LINE(FND_FILE.log,
2439 'Create_Trx_Balance: Process completed successfully');
2440
2441 EXCEPTION
2442 WHEN others THEN
2443 g_error_code := SQLCODE;
2444 g_error_text := SUBSTR(SQLERRM,1,240);
2445 FND_FILE.PUT_LINE(FND_FILE.log,
2446 'Create_Trx_Balance:'|| g_error_text);
2447 RAISE;
2448
2449 END create_trx_balance;
2450
2451 END jl_co_gl_nit_management;