[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.52.12020000.10 2013/03/26 09:42:53 mbarrett 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 -- Bug 16529685 Start
56 gljh.je_from_sla_flag,
57 -- Bug 16529685 End
58 DECODE(g_account_segment,
59 'SEGMENT1', glcc.segment1, 'SEGMENT2', glcc.segment2,
60 'SEGMENT3', glcc.segment3, 'SEGMENT4', glcc.segment4,
61 'SEGMENT5', glcc.segment5, 'SEGMENT6', glcc.segment6,
62 'SEGMENT7', glcc.segment7, 'SEGMENT8', glcc.segment8,
63 'SEGMENT9', glcc.segment9, 'SEGMENT10',glcc.segment10,
64 'SEGMENT11',glcc.segment11, 'SEGMENT12',glcc.segment12,
65 'SEGMENT13',glcc.segment13, 'SEGMENT14',glcc.segment14,
66 'SEGMENT15',glcc.segment15, 'SEGMENT16',glcc.segment16,
67 'SEGMENT17',glcc.segment17, 'SEGMENT18',glcc.segment18,
68 'SEGMENT19',glcc.segment19, 'SEGMENT20',glcc.segment20,
69 'SEGMENT21',glcc.segment21, 'SEGMENT22',glcc.segment22,
70 'SEGMENT23',glcc.segment23, 'SEGMENT24',glcc.segment24,
71 'SEGMENT25',glcc.segment25, 'SEGMENT26',glcc.segment26,
72 'SEGMENT27',glcc.segment27, 'SEGMENT28',glcc.segment28,
73 'SEGMENT28',glcc.segment28, 'SEGMENT29',glcc.segment29,
74 'SEGMENT30',glcc.segment30, NULL) account_code
75 FROM gl_je_headers gljh,
76 gl_code_combinations glcc,
77 gl_je_lines gljl
78 WHERE gljl.status = 'P'
79 AND gljl.period_name = x_period
80 AND gljl.ledger_id = x_sobid
81 AND gljh.je_batch_id = NVL(x_batchid,gljh.je_batch_id)
82 AND gljl.code_combination_id = glcc.code_combination_id
83 AND EXISTS (SELECT '1'
84 FROM jl_co_gl_nit_accts jlcgna
85 WHERE DECODE(g_account_segment,
86 'SEGMENT1', glcc.segment1, 'SEGMENT2', glcc.segment2,
87 'SEGMENT3', glcc.segment3, 'SEGMENT4', glcc.segment4,
88 'SEGMENT5', glcc.segment5, 'SEGMENT6', glcc.segment6,
89 'SEGMENT7', glcc.segment7, 'SEGMENT8', glcc.segment8,
90 'SEGMENT9', glcc.segment9, 'SEGMENT10',glcc.segment10,
91 'SEGMENT11',glcc.segment11, 'SEGMENT12',glcc.segment12,
92 'SEGMENT13',glcc.segment13, 'SEGMENT14',glcc.segment14,
93 'SEGMENT15',glcc.segment15, 'SEGMENT16',glcc.segment16,
94 'SEGMENT17',glcc.segment17, 'SEGMENT18',glcc.segment18,
95 'SEGMENT19',glcc.segment19, 'SEGMENT20',glcc.segment20,
96 'SEGMENT21',glcc.segment21, 'SEGMENT22',glcc.segment22,
97 'SEGMENT23',glcc.segment23, 'SEGMENT24',glcc.segment24,
98 'SEGMENT25',glcc.segment25, 'SEGMENT26',glcc.segment26,
99 'SEGMENT27',glcc.segment27, 'SEGMENT28',glcc.segment28,
100 'SEGMENT28',glcc.segment28, 'SEGMENT29',glcc.segment29,
101 'SEGMENT30',glcc.segment30, NULL) = jlcgna.account_code
102 AND jlcgna.nit_required = 'Y'
103 AND jlcgna.chart_of_accounts_id = g_chart_of_accounts_id)
104 AND gljl.je_header_id = gljh.je_header_id
105 AND nvl(gljl.co_processed_flag, 'N') <> 'Y'
106 AND gljh.actual_flag = 'A'
107 AND gljh.currency_code <> 'STAT'
108 AND not exists (select 1 --filtering for intercompany lines created in GL -- Begin of bug10084267 fix
109 from gl_je_lines jl,
110 fnd_new_messages fnm
111 where fnm.message_name in ('PPOS0220', 'PPOS0221', 'PPOS0222', 'PPOS0243', 'PPOS0222_G','PPOSO275')
112 and fnm.application_id = 101 --bug 13355793
113 and language_code = USERENV('LANG')
114 and fnm.message_text = jl.description
115 and jl.je_header_id = gljl.je_header_id
116 and jl.je_line_num = gljl.je_line_num)
117 AND not exists (select 1 -- filtering for intercompany lines created in SLA
118 from xla_ae_lines ael,
119 gl_import_references gir
120 where gir.je_header_id = gljl.je_header_id
121 and gir.je_line_num = gljl.je_line_num
122 and gir.gl_sl_link_id = ael.gl_sl_link_id
123 and ael.accounting_class_code in ('INTER','INTRA')) --End of bug10084267 fix
124 ORDER BY gljh.je_header_id, nvl(gljh.reversed_je_header_id,0); --bug 8391172
125
126 TYPE t_parameters IS RECORD (
127 cid jl_co_gl_conc_ctrl.process_id%type,
128 set_of_books_id jl_co_gl_conc_ctrl.set_of_books_id%type,
129 user_id jl_co_gl_conc_ctrl.created_by%type,
130 rev_cid jl_co_gl_conc_ctrl.reversed_process_id%type);
131
132 TYPE t_nits IS RECORD (nit_id jl_co_gl_nits.nit_id%type,
133 nit jl_co_gl_nits.nit%type,
134 nit_name jl_co_gl_nits.name%type,
135 nit_type jl_co_gl_nits.type%type,
136 verifying_digit jl_co_gl_nits.verifying_digit%type);
137
138 TYPE t_gl_je IS RECORD (je_header_id jl_co_gl_trx.je_header_id%type,
139 je_line_num jl_co_gl_trx.je_line_num%type,
140 identifier jl_co_gl_conc_errs.identifier%type);
141
142 g_jl_trx jl_co_gl_trx%ROWTYPE;
143 g_journal_rec journals%ROWTYPE;
144 g_parameter_rec t_parameters;
145 g_nit_rec t_nits;
146 g_gl_je_rec t_gl_je;
147 g_error_exists VARCHAR2(5);
148 g_error_code NUMBER;
149 g_error_text VARCHAR2(240);
150
151
152 PROCEDURE Insert_Error_Rec
153 (p_message_text IN VARCHAR2 ) IS
154
155 BEGIN
156
157 INSERT INTO jl_co_gl_conc_errs (message_text,
158 process_id,
159 je_header_id,
160 je_line_num,
161 identifier,
162 creation_date,
163 created_by,
164 last_update_date,
165 last_updated_by,
166 last_update_login)
167 VALUES (p_message_text,
168 g_parameter_rec.cid,
169 g_gl_je_rec.je_header_id,
170 g_gl_je_rec.je_line_num,
171 g_gl_je_rec.identifier,
172 sysdate,
173 NVL(g_parameter_rec.user_id,-1),
174 sysdate,
175 NVL(g_parameter_rec.user_id,-1),
176 g_login_id );
177
178 EXCEPTION
179 WHEN others THEN
180 g_error_code := SQLCODE;
181 g_error_text := SUBSTR(SQLERRM,1,240);
182 FND_FILE.PUT_LINE(FND_FILE.log,'Insert_Error_Rec:'|| g_error_text);
183 RAISE;
184
185 END Insert_Error_Rec;
186
187
188 FUNCTION Validate_NIT
189 (p_nit_rec IN OUT NOCOPY t_nits,
190 p_identifier_type IN VARCHAR2 ) RETURN BOOLEAN IS
191
192 -- Validate nit information against jl_co_gl_nits.
193 -- If a corresponding record does not exist in jl_co_gl_nits,
194 -- insert a new nit record
195
196 l_master_nit_rec t_nits;
197 l_nit_valid BOOLEAN := TRUE;
198 l_message_text jl_co_gl_conc_errs.message_text%TYPE := NULL;
199 l_add_text VARCHAR2(30):= NULL;
200
201 BEGIN
202 FND_FILE.PUT_LINE(FND_FILE.log,'Inside Validate NIT');
203 -- add_text is populated only for external sources
204
205 IF p_identifier_type = 'JL_CO_GL_NIT' THEN
206 l_add_text := 'JL_CO_GL_0_NIT_TRX_CREATED';
207 END IF;
208
209 BEGIN -- check if nit exists by nit number
210 FND_FILE.PUT_LINE(FND_FILE.log,'p_nit_rec.nit : '||p_nit_rec.nit);
211
212 SELECT nit_id,
213 nit,
214 name,
215 type,
216 verifying_digit
217 INTO l_master_nit_rec
218 FROM jl_co_gl_nits
219 WHERE nit = p_nit_rec.nit;
220 -- Bug 14283808 Start
221 FND_FILE.PUT_LINE(FND_FILE.log,'l_master_nit_rec.nit_id : '||l_master_nit_rec.nit_id);
222 FND_FILE.PUT_LINE(FND_FILE.log,'l_master_nit_rec.nit : '||l_master_nit_rec.nit);
223 FND_FILE.PUT_LINE(FND_FILE.log,'l_master_nit_rec.nit_name : '||l_master_nit_rec.nit_name);
224 FND_FILE.PUT_LINE(FND_FILE.log,'l_master_nit_rec.nit_type : '||l_master_nit_rec.nit_type);
225 FND_FILE.PUT_LINE(FND_FILE.log,'l_master_nit_rec.verifying_digit : '||l_master_nit_rec.verifying_digit);
226 -- Bug 14283808 End
227
228 EXCEPTION
229 WHEN no_data_found THEN
230 NULL;
231 END;
232
233 BEGIN -- check if nit exists by name
234 IF l_master_nit_rec.nit IS NULL THEN
235
236 SELECT nit_id,
237 nit,name,
238 type,
239 verifying_digit
240 INTO l_master_nit_rec
241 FROM jl_co_gl_nits
242 WHERE name = p_nit_rec.nit_name; -- Bug 8589204 Removed the UPPER fn
243
244 FND_FILE.PUT_LINE(FND_FILE.log,'p_nit_rec.nit_name : '||p_nit_rec.nit_name);
245
246 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_NIT_NAME_EXISTS');
247 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
248 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
249 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
250 l_message_text := FND_MESSAGE.GET;
251 Insert_Error_Rec(l_message_text);
252 l_nit_valid := FALSE;
253
254 END IF;
255
256 EXCEPTION
257 WHEN no_data_found THEN
258 NULL;
259 END;
260
261 IF l_master_nit_rec.nit IS NULL THEN
262 FND_FILE.PUT_LINE(FND_FILE.log,'master : p_nit_rec.nit : '||p_nit_rec.nit);
263
264 -- master NIT does not exist
265 -- validate NIT and insert new NIT record
266 IF jg_taxid_val_pkg.check_length('CO',14,p_nit_rec.nit) = 'FALSE' THEN
267 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_NIT_MAX_DIGITS');
268 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
269 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
270 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
271 l_message_text := FND_MESSAGE.GET;
272 Insert_Error_Rec(l_message_text);
273 l_nit_valid := FALSE;
274 END IF; /*check_length*/
275
276 IF p_nit_rec.nit IS NULL THEN
277 FND_FILE.PUT_LINE(FND_FILE.log,'NIT Null Check : p_nit_rec.nit : '||p_nit_rec.nit);
278
279 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_NIT_REQUIRED');
280 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
281 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
282 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
283 l_message_text := FND_MESSAGE.GET;
284 Insert_Error_Rec(l_message_text);
285 l_nit_valid := FALSE;
286
287 ELSE
288 IF jg_taxid_val_pkg.check_numeric(p_nit_rec.nit) = 'FALSE' THEN
289 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_INVALID_NIT');
290 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
291 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
292 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
293 l_message_text := FND_MESSAGE.GET;
294
295 Insert_Error_Rec(l_message_text);
296 l_nit_valid := FALSE;
297
298 ELSE /* nit is numeric */
299 IF (p_nit_rec.verifying_digit IS NOT NULL) THEN
300 IF jg_taxid_val_pkg.check_algorithm(p_nit_rec.nit,'CO',
301 p_nit_rec.verifying_digit) = 'FALSE' THEN
302 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_VER_DIGIT_INVALID');
303 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',
304 p_identifier_type,TRUE);
305 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
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; /* validate_algorithm for verifying digit*/
311 END IF; /* v_digit not NULL */
312 END IF; /* check numeric */
313 END IF; /* nit is NULL */
314
315 IF (p_nit_rec.nit_type = 'LEGAL_ENTITY' AND
316 p_nit_rec.verifying_digit IS NULL) THEN
317 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_VER_DIGIT_REQUIRED');
318 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
319 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
320 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
321 l_message_text := FND_MESSAGE.GET;
322 Insert_Error_Rec(l_message_text);
323 l_nit_valid := FALSE;
324 END IF;
325
326 IF (p_nit_rec.nit_type IS NULL) OR (p_nit_rec.nit_type NOT IN
327 ('LEGAL_ENTITY','INDIVIDUAL','FOREIGN_ENTITY','MERCHANT')) THEN -- ER 9435028
328 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_NIT_TYPE_REQUIRED');
329 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
330 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
331 FND_MESSAGE.SET_TOKEN('TAXID_TYPE', 'LEGAL_ENTITY, INDIVIDUAL, MERCHANT and FOREIGN_ENTITY'); -- ER 9435028
332 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
333 l_message_text := FND_MESSAGE.GET;
334 Insert_Error_Rec(l_message_text);
335 l_nit_valid := FALSE;
336 END IF;
337
338 IF p_nit_rec.nit_name IS NULL THEN
339 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_NIT_NAME_REQUIRED');
340 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
341 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
342 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
343 l_message_text := FND_MESSAGE.GET;
344 Insert_Error_Rec(l_message_text);
345 l_nit_valid := FALSE;
346 END IF;
347
348 -- insert validated NIT record
349 IF l_nit_valid THEN
350
351 INSERT INTO jl_co_gl_nits (nit_id,
352 nit,
353 type,
354 verifying_digit,
355 name,
356 creation_date,
357 created_by,
358 last_update_date,
359 last_updated_by,
360 last_update_login)
361 VALUES (jl_co_gl_nits_s.nextval,
362 p_nit_rec.nit,
363 p_nit_rec.nit_type,
364 p_nit_rec.verifying_digit,
365 p_nit_rec.nit_name,
366 sysdate,
367 NVL(g_parameter_rec.user_id,-1),
368 sysdate,
369 NVL(g_parameter_rec.user_id,-1),
370 g_login_id);
371
372 SELECT jl_co_gl_nits_s.currval
373 INTO p_nit_rec.nit_id
374 FROM DUAL ;
375
376 END IF;
377
378 ELSIF l_nit_valid THEN
379 -- nit exists verify if the info matches master nit
380 -- Bug 14283808 Start
381 FND_FILE.PUT_LINE(FND_FILE.log,'p_nit_rec.nit : '||p_nit_rec.nit);
382 FND_FILE.PUT_LINE(FND_FILE.log,'l_master_nit_rec.nit : '||l_master_nit_rec.nit);
383 FND_FILE.PUT_LINE(FND_FILE.log,'p_nit_rec.nit_name : '||p_nit_rec.nit_name);
384 FND_FILE.PUT_LINE(FND_FILE.log,'l_master_nit_rec.nit_name : '||l_master_nit_rec.nit_name);
385 FND_FILE.PUT_LINE(FND_FILE.log,'p_nit_rec.nit_type : '||p_nit_rec.nit_type);
386 FND_FILE.PUT_LINE(FND_FILE.log,'l_master_nit_rec.nit_type : '||l_master_nit_rec.nit_type);
387 FND_FILE.PUT_LINE(FND_FILE.log,'p_nit_rec.verifying_digit : '|| p_nit_rec.verifying_digit);
388 FND_FILE.PUT_LINE(FND_FILE.log,'l_master_nit_rec.verifying_digit : '||l_master_nit_rec.verifying_digit);
389 -- Bug 14283808 End
390
391 IF (p_nit_rec.nit <> l_master_nit_rec.nit) OR
392 (UPPER(p_nit_rec.nit_name) <> UPPER(l_master_nit_rec.nit_name)) OR
393 (UPPER(p_nit_rec.nit_type) <> UPPER(l_master_nit_rec.nit_type)) OR
394 (p_nit_rec.verifying_digit <> l_master_nit_rec.verifying_digit) THEN
395
396 FND_FILE.PUT_LINE(FND_FILE.log,'in the conditoin l_nit valid and p_nit_rec.nit <> l_master_nit_rec.nit');
397
398
399 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_MASTER_NIT_MISMATCH');
400 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',p_identifier_type,TRUE);
401 FND_MESSAGE.SET_TOKEN('IDENTIFIER',g_gl_je_rec.identifier);
402 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
403 l_message_text := FND_MESSAGE.GET;
404 Insert_Error_Rec(l_message_text);
405 FND_FILE.PUT_LINE(FND_FILE.log,'inserted in insert_error_rec');
406 l_nit_valid := FALSE;
407 END IF;
408 END IF;
409
410 RETURN(l_nit_valid);
411 FND_FILE.PUT_LINE(FND_FILE.log,'Returning from Validate NIT');
412 EXCEPTION
413 WHEN no_data_found THEN
414 NULL;
415
416 WHEN others THEN
417 g_error_code := SQLCODE;
418 g_error_text := SUBSTR(SQLERRM,1,240);
419 FND_FILE.PUT_LINE(FND_FILE.log,'Validate_NIT:'|| g_error_text);
420 RAISE;
421
422 END Validate_NIT;
423
424
425 PROCEDURE Generate_GL_trx
426 (p_journal_rec IN journals%ROWTYPE ) IS
427
428 l_add_text VARCHAR2(30):= NULL;
429 l_message_text jl_co_gl_conc_errs.message_text%TYPE := NULL;
430 -- Bug 9710493 Start
431 l_flag Number;
432 l_entity_code Varchar2(30);
433 -- Bug 9710493 End
434
435 BEGIN
436 -- Bug 9710493 Start
437 l_flag := 0;
438 l_entity_code := NULL;
439 -- Bug 9710493 End
440 BEGIN
441 SELECT nit_id
442 INTO g_nit_rec.nit_id
443 FROM jl_co_gl_nits jlcgn
444 WHERE nit = DECODE(p_journal_rec.source,
445 'Payables','0',
446 'Purchasing','0',
447 'Receivables','0',
448 p_journal_rec.ext_nit);
449
450 EXCEPTION
451 WHEN no_data_found THEN
452 g_nit_rec.nit_id := NULL; --Fwd port of 11i bug 6155086
453 END;
454
455 SELECT p_journal_rec.je_header_id,
456 p_journal_rec.je_line_num,
457 null
458 INTO g_gl_je_rec
459 FROM DUAL;
460
461 -- Bug 9710493 Start
462 IF p_journal_rec.source in ('Purchasing','Receivables') THEN
463 -- Bug 9710493 End
464 -- Its here probably because import references dont exist or is in
465 -- summary because of which the AR, AP or PO routine couldn't process
466 -- it and passed the record on to the GL routine
467
468 l_add_text := 'JL_CO_GL_0_NIT_TRX_CREATED';
469 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_NO_IMPORT_REF');
470 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
471 l_message_text := FND_MESSAGE.GET;
472 Insert_Error_Rec(l_message_text);
473
474 -- Bug 9710493 Start
475 ELSIF p_journal_rec.source = 'Payables' Then
476 Begin
477 Select distinct ent.entity_code
478 Into l_entity_code
479 From xla_transaction_entities ent,
480 xla_ae_headers aeh,
481 xla_ae_lines ael,
482 gl_import_references imr
483 Where ent.application_id = 200
484 And ent.application_id =aeh.application_id
485 And aeh.application_id = ael.application_id
486 And ent.entity_id = aeh.entity_id
487 And (aeh.ledger_id = g_parameter_rec.set_of_books_id
488 OR
489 aeh.ledger_id In (Select from_ledger_id
490 From gl_consolidation
491 Where to_ledger_id = g_parameter_rec.set_of_books_id)
492 )
493 AND aeh.ae_header_id = ael.ae_header_id
494 And ael.application_id = 200
495 And imr.gl_sl_link_id = ael.gl_sl_link_id
496 And imr.je_header_id = p_journal_rec.je_header_id
497 And imr.je_line_num = p_journal_rec.je_line_num;
498 Exception
499 When Others Then
500 Fnd_file.Put_Line(Fnd_file.log, 'Exception occured in the Select Statement');
501 Null;
502 End;
503 If l_entity_code = 'MANUAL' Then
504 l_flag := 1;
505 Else
506 -- Its here probably because import references dont exist or is in
507 -- summary because of which the AP routine couldn't process
508 -- it and passed the record on to the GL routine
509
510 l_add_text := 'JL_CO_GL_0_NIT_TRX_CREATED';
511 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_NO_IMPORT_REF');
512 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
513 l_message_text := FND_MESSAGE.GET;
514 Insert_Error_Rec(l_message_text);
515 End If;
516 -- Bug 9710493 End
517 ELSIF g_nit_rec.nit_id IS NULL THEN /* Third party # is not valid */
518
519 l_add_text := 'JL_CO_GL_0_NIT_TRX_CREATED';
520 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_INVALID_NIT');
521 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',NULL);
522 FND_MESSAGE.SET_TOKEN('IDENTIFIER',NULL);
523 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
524 l_message_text := FND_MESSAGE.GET;
525 Insert_Error_Rec(l_message_text);
526
527 ELSIF p_journal_rec.ext_nit = '0' THEN
528 -- Not calling NIT_VALIDATION function because the only nit info
529 -- entered by the user in Enter Journals form is NIT# hence it
530 -- isn't necessary to do exhaustive NIT validation
531
532 l_add_text := 'JL_CO_GL_0_NIT_TRX_CREATED';
533 FND_MESSAGE.SET_NAME('JL','JL_CO_GL_NIT_REQUIRED');
534 FND_MESSAGE.SET_TOKEN('IDENTIFIER_TYPE',NULL);
535 FND_MESSAGE.SET_TOKEN('IDENTIFIER',NULL);
536 FND_MESSAGE.SET_TOKEN('ADD_TEXT',l_add_text,TRUE);
537 l_message_text := FND_MESSAGE.GET;
538 Insert_Error_Rec(l_message_text);
539
540 END IF;
541
542 -- Bug 9710493 Start
543 If l_flag = 0 Then
544 -- Bug 9710493 End
545 INSERT INTO jl_co_gl_trx (transaction_id,
546 process_id,
547 set_of_books_id,
548 code_combination_id,
549 account_code,
550 nit_id,
551 period_name,
552 period_year,
553 period_num,
554 je_batch_id,
555 je_header_id,
556 category,
557 subledger_doc_number,
558 je_line_num,
559 document_number,
560 accounting_date,
561 currency_code,
562 creation_date,
563 created_by,
564 last_update_date,
565 last_updated_by,
566 last_update_login,
567 accounted_dr,
568 accounted_cr,
569 entered_dr,
570 entered_cr)
571 VALUES (jl_co_gl_trx_s.nextval,
572 g_parameter_rec.cid,
573 g_parameter_rec.set_of_books_id,
574 p_journal_rec.code_combination_id,
575 p_journal_rec.account_code,
576 NVL(g_nit_rec.nit_id,g_default_nit_id),
577 p_journal_rec.period_name,
578 g_period_year,
579 g_period_num,
580 p_journal_rec.je_batch_id,
581 p_journal_rec.je_header_id,
582 p_journal_rec.category,
583 p_journal_rec.subl_doc_num,
584 p_journal_rec.je_line_num,
585 NULL,
586 p_journal_rec.accounting_date,
587 p_journal_rec.currency,
588 sysdate,
589 NVL(g_parameter_rec.user_id,-1),
590 sysdate,
591 NVL(g_parameter_rec.user_id,-1),
592 g_login_id,
593 DECODE(sign(p_journal_rec.accounted_cr), -1,
594 (abs(p_journal_rec.accounted_cr) +
595 NVL(DECODE(sign(p_journal_rec.accounted_dr),1,
596 p_journal_rec.accounted_dr,NULL),0)),
597 DECODE(sign(p_journal_rec.accounted_dr),-1,
598 NULL,p_journal_rec.accounted_dr)),
599 DECODE(sign(p_journal_rec.accounted_dr),-1,
600 (abs(p_journal_rec.accounted_dr) +
601 NVL(DECODE(sign(p_journal_rec.accounted_cr),1,
602 p_journal_rec.accounted_cr,NULL),0)),
603 DECODE(sign(p_journal_rec.accounted_cr),-1,
604 NULL,p_journal_rec.accounted_cr)),
605 DECODE(sign(p_journal_rec.entered_cr),-1,
606 (abs(p_journal_rec.entered_cr) +
607 NVL(DECODE(sign(p_journal_rec.entered_dr),1,
608 p_journal_rec.entered_dr,NULL),0)),
609 DECODE(sign(p_journal_rec.entered_dr),-1,
610 NULL,p_journal_rec.entered_dr)),
611 DECODE(sign(p_journal_rec.entered_dr),-1,
612 (abs(p_journal_rec.entered_dr) +
613 NVL(DECODE(sign(p_journal_rec.entered_cr),1,
614 p_journal_rec.entered_cr,NULL),0)),
615 DECODE(sign(p_journal_rec.entered_cr),-1,
616 NULL,p_journal_rec.entered_cr)) );
617 /* bug 7045429
618 BEGIN
619 SELECT 'TRUE'
620 INTO g_error_exists
621 FROM DUAL
622 WHERE EXISTS (SELECT '1'
623 FROM jl_co_gl_conc_errs jlcgce
624 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
625 AND jlcgce.je_line_num = p_journal_rec.je_line_num);
626
627 EXCEPTION
628 WHEN no_data_found THEN
629 NULL;
630 END;
631
632 IF NVL(g_error_exists,'FALSE') = 'TRUE' THEN
633 DELETE FROM jl_co_gl_trx jlcgt
634 WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
635 AND jlcgt.je_line_num = p_journal_rec.je_line_num;
636 ELSE */ -- Bug 8215616
637 UPDATE gl_je_lines gljl
638 SET co_processed_flag = 'Y'
639 WHERE gljl.je_header_id = p_journal_rec.je_header_id
640 AND gljl.je_line_num = p_journal_rec.je_line_num
641 AND EXISTS (SELECT 'Y'
642 FROM jl_co_gl_trx jlcgt
643 WHERE jlcgt.je_header_id = gljl.je_header_id
644 AND jlcgt.je_line_num = gljl.je_line_num); /*
645 END IF;
646 */
647 -- Bug 9710493 Start
648 End If;
649 -- Bug 9710493 End
650
651 COMMIT;
652
653 EXCEPTION
654 WHEN others THEN
655 g_error_code := SQLCODE;
656 g_error_text := SUBSTR(SQLERRM,1,240);
657 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_GL_trx:'|| g_error_text);
658 RAISE;
659
660 END Generate_GL_trx;
661
662
663 PROCEDURE Generate_AP_trx
664 (p_journal_rec IN journals%ROWTYPE) IS
665
666 -- R12 changes: replaced 11i sla tables with R12 xla tables and removed
667 -- the third sql in 11i for Payment history. This is handled via the 2nd sql here
668
669 --Bug 16036092 below cursor is modified to support 11i upgraded transactions
670 CURSOR ap_detail_lines IS
671 -- Bug 13805457 Start
672 SELECT DECODE(SIGN(lnk.unrounded_accounted_dr),-1,
673 (ABS(lnk.unrounded_accounted_dr) + NVL(DECODE(SIGN(lnk.unrounded_accounted_cr),1,lnk.unrounded_accounted_cr,null),0)),
674 DECODE(SIGN(lnk.unrounded_accounted_cr),-1,null, lnk.unrounded_accounted_cr)) ACCOUNTED_CR,
675 DECODE(SIGN(lnk.unrounded_accounted_cr),-1,
676 (ABS(lnk.unrounded_accounted_cr) + NVL(DECODE(SIGN(lnk.unrounded_accounted_dr),1,lnk.unrounded_accounted_dr,null),0)),
677 DECODE(SIGN(lnk.unrounded_accounted_dr),-1,null, lnk.unrounded_accounted_dr)) ACCOUNTED_DR,
678 DECODE(SIGN(lnk.unrounded_entered_cr),-1,
679 (ABS(lnk.unrounded_entered_cr) + NVL(DECODE(SIGN(lnk.unrounded_entered_dr),1,lnk.unrounded_entered_dr,null),0)),
680 DECODE(SIGN(lnk.unrounded_entered_dr),-1,null, lnk.unrounded_entered_dr)) ENTERED_DR,
681 DECODE(SIGN(lnk.unrounded_entered_dr),-1,
682 (ABS(lnk.unrounded_entered_dr) + NVL(DECODE(SIGN(lnk.unrounded_entered_cr),1,lnk.unrounded_entered_cr,null),0)),
683 DECODE(SIGN(lnk.unrounded_entered_cr),-1,null, lnk.unrounded_entered_cr)) ENTERED_CR,
684 -- Bug 13805457 End
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 I.invoice_type_lookup_code TRX_TYPE, -- ER 9435028
690 I.source TRX_SOURCE, -- ER 9435028
691 'INV' TRX_CLASS,
692 ael.accounting_class_code ACCT_LINE_TYPE,
693 D.invoice_distribution_id TRX_DIST_ID,
694 aeh.upg_batch_id UPG_BATCH_ID
695 FROM
696 ap_invoices_all I,
697 xla_transaction_entities ent,
698 xla_ae_headers AEH,
699 xla_ae_lines AEL,
700 ap_invoice_distributions_all D,
701 xla_distribution_links LNK,
702 gl_import_references R
703 WHERE
704 ent.application_id = 200
705 AND ent.application_id =aeh.application_id
706 AND aeh.application_id = ael.application_id
707 --bug8680825
708 /*and (ent.ledger_id = g_parameter_rec.set_of_books_id
709 or ent.ledger_id in (SELECT from_ledger_id
710 FROM gl_consolidation
711 WHERE to_ledger_id = g_parameter_rec.set_of_books_id))*/
712 AND ent.entity_code = 'AP_INVOICES'
713 AND i.invoice_id = ent.source_id_int_1
714 AND ent.entity_id = aeh.entity_id
715 --AND AEH.ledger_id = ent.ledger_id --bug8680825
716 AND (aeh.ledger_id = g_parameter_rec.set_of_books_id
717 OR
718 aeh.ledger_id IN (SELECT from_ledger_id
719 FROM gl_consolidation
720 WHERE to_ledger_id = g_parameter_rec.set_of_books_id)
721 )
722 AND aeh.ae_header_id = ael.ae_header_id
723 AND aeh.upg_batch_id is null
724 AND ael.ae_header_id = lnk.ae_header_id
725 AND ael.ae_line_num = lnk.ae_line_num
726 AND lnk.source_distribution_type <> 'AP_PREPAY' --- bug 7428486
727 AND ael.application_id = 200
728 AND lnk.application_id = 200
729 --AND D.invoice_distribution_id(+) = DECODE(lnk.source_distribution_type,
730 --'AP_INVOICE_DISTRIBUTIONS', lnk.source_distribution_id_num_1,null)
731 AND D.invoice_distribution_id = lnk.source_distribution_id_num_1
732 AND R.gl_sl_link_id = AEL.gl_sl_link_id
733 AND R.je_header_id = p_journal_rec.je_header_id
734 AND R.je_line_num = p_journal_rec.je_line_num
735 UNION ALL
736 -- Bug 13805457 Start
737 SELECT DECODE(SIGN(AEL.unrounded_accounted_dr),-1,
738 (ABS(AEL.unrounded_accounted_dr) + NVL(DECODE(SIGN(AEL.unrounded_accounted_cr),1,AEL.unrounded_accounted_cr,null),0)),
739 DECODE(SIGN(AEL.unrounded_accounted_cr),-1,null, AEL.unrounded_accounted_cr)) ACCOUNTED_CR,
740 DECODE(SIGN(AEL.unrounded_accounted_cr),-1,
741 (ABS(AEL.unrounded_accounted_cr) + NVL(DECODE(SIGN(AEL.unrounded_accounted_dr),1,AEL.unrounded_accounted_dr,null),0)),
742 DECODE(SIGN(AEL.unrounded_accounted_dr),-1,null, AEL.unrounded_accounted_dr)) ACCOUNTED_DR,
743 DECODE(SIGN(AEL.unrounded_entered_cr),-1,
744 (ABS(AEL.unrounded_entered_cr) + NVL(DECODE(SIGN(AEL.unrounded_entered_dr),1,AEL.unrounded_entered_dr,null),0)),
745 DECODE(SIGN(AEL.unrounded_entered_dr),-1,null, AEL.unrounded_entered_dr)) ENTERED_DR,
746 DECODE(SIGN(AEL.unrounded_entered_dr),-1,
747 (ABS(AEL.unrounded_entered_dr) + NVL(DECODE(SIGN(AEL.unrounded_entered_cr),1,AEL.unrounded_entered_cr,null),0)),
748 DECODE(SIGN(AEL.unrounded_entered_cr),-1,null, AEL.unrounded_entered_cr)) ENTERED_CR,
749 -- Bug 13805457 End
750 ent.entity_code SOURCE_TABLE,
751 ael.party_id PARTY_ID,
752 TO_CHAR(C.CHECK_NUMBER) TRX_NUMBER_C ,
753 C.CHECK_ID TRX_HDR_ID,
754 NULL TRX_TYPE, -- ER 9435028
755 NULL TRX_SOURCE, -- ER 9435028
756 'PAY' TRX_CLASS,
757 ael.accounting_class_code ACCT_LINE_TYPE,
758 NULL TRX_DIST_ID,
759 aeh.upg_batch_id UPG_BATCH_ID
760 FROM
761 ap_checks_all C,
762 xla_transaction_entities ent,
763 xla_ae_headers AEH,
764 xla_ae_lines AEL,
765 ap_payment_history_all D, -- bug 8673098
766 gl_import_references R
767 WHERE
768 ent.application_id = 200
769 --bug8680825
770 /*and (ent.ledger_id = g_parameter_rec.set_of_books_id
771 or ent.ledger_id in (SELECT from_ledger_id
772 FROM gl_consolidation
773 WHERE to_ledger_id = g_parameter_rec.set_of_books_id))*/
774
775 AND ent.entity_code = 'AP_PAYMENTS'
776 AND c.check_id = ent.source_id_int_1
777 AND c.check_id = D.check_id
778 AND ent.entity_id = aeh.entity_id
779 --AND AEH.ledger_id = ent.ledger_id --bug8680825
780 AND (aeh.ledger_id = g_parameter_rec.set_of_books_id
781 OR
782 aeh.ledger_id IN (SELECT from_ledger_id
783 FROM gl_consolidation
784 WHERE to_ledger_id = g_parameter_rec.set_of_books_id)
785 )
786 and aeh.ae_header_id = ael.ae_header_id
787 and ael.application_id = 200
788 AND D.accounting_event_id = aeh.event_id
789 AND R.gl_sl_link_id = AEL.gl_sl_link_id
790 AND R.je_header_id = p_journal_rec.je_header_id
791 AND R.je_line_num = p_journal_rec.je_line_num
792 UNION ALL
793 -- Bug 13805457 Start
794 SELECT DECODE(SIGN(lnk.unrounded_accounted_dr),-1,
795 (ABS(lnk.unrounded_accounted_dr) + NVL(DECODE(SIGN(lnk.unrounded_accounted_cr),1,lnk.unrounded_accounted_cr,null),0)),
796 DECODE(SIGN(lnk.unrounded_accounted_cr),-1,null, lnk.unrounded_accounted_cr)) ACCOUNTED_CR,
797 DECODE(SIGN(lnk.unrounded_accounted_cr),-1,
798 (ABS(lnk.unrounded_accounted_cr) + NVL(DECODE(SIGN(lnk.unrounded_accounted_dr),1,lnk.unrounded_accounted_dr,null),0)),
799 DECODE(SIGN(lnk.unrounded_accounted_dr),-1,null, lnk.unrounded_accounted_dr)) ACCOUNTED_DR,
800 DECODE(SIGN(lnk.unrounded_entered_cr),-1,
801 (ABS(lnk.unrounded_entered_cr) + NVL(DECODE(SIGN(lnk.unrounded_entered_dr),1,lnk.unrounded_entered_dr,null),0)),
802 DECODE(SIGN(lnk.unrounded_entered_dr),-1,null, lnk.unrounded_entered_dr)) ENTERED_DR,
803 DECODE(SIGN(lnk.unrounded_entered_dr),-1,
804 (ABS(lnk.unrounded_entered_dr) + NVL(DECODE(SIGN(lnk.unrounded_entered_cr),1,lnk.unrounded_entered_cr,null),0)),
805 DECODE(SIGN(lnk.unrounded_entered_cr),-1,null, lnk.unrounded_entered_cr)) ENTERED_CR,
806 -- Bug 13805457 End
807 lnk.source_distribution_type SOURCE_TABLE,
808 ael.party_id PARTY_ID,
809 ent.transaction_number TRX_NUMBER_C ,
810 I.invoice_id TRX_HDR_ID,
811 I.invoice_type_lookup_code TRX_TYPE, -- ER 9435028
812 I.source TRX_SOURCE, -- ER 9435028
813 'INV' TRX_CLASS,
814 ael.accounting_class_code ACCT_LINE_TYPE,
815 D.invoice_distribution_id TRX_DIST_ID,
816 aeh.upg_batch_id UPG_BATCH_ID
817 FROM
818 ap_invoices_all I,
819 xla_transaction_entities ent,
820 xla_ae_headers AEH,
821 xla_ae_lines AEL,
822 ap_prepay_app_dists D,
823 xla_distribution_links LNK,
824 gl_import_references R
825 WHERE
826 ent.application_id = 200
827 AND ent.application_id =aeh.application_id
828 AND aeh.application_id = ael.application_id
829 --bug8680825
830 /*and (ent.ledger_id = g_parameter_rec.set_of_books_id
831 ---AND lnk.source_distribution_type = 'AP_PREPAY' ---bug 7428486
832 or ent.ledger_id in (SELECT from_ledger_id
833 FROM gl_consolidation
834 WHERE to_ledger_id = g_parameter_rec.set_of_books_id))*/
835 AND ent.entity_code = 'AP_INVOICES'
836 AND i.invoice_id = ent.source_id_int_1
837 AND ent.entity_id = aeh.entity_id
838 --AND AEH.ledger_id = ent.ledger_id --bug8680825
839 AND (aeh.ledger_id = g_parameter_rec.set_of_books_id
840 OR
841 aeh.ledger_id IN (SELECT from_ledger_id
842 FROM gl_consolidation
843 WHERE to_ledger_id = g_parameter_rec.set_of_books_id)
844 )
845 AND aeh.ae_header_id = ael.ae_header_id
846 AND aeh.upg_batch_id is null
847 AND ael.ae_header_id = lnk.ae_header_id
848 AND ael.ae_line_num = lnk.ae_line_num
849 AND ael.application_id = 200
850 AND lnk.application_id = 200
851 AND lnk.source_distribution_type = 'AP_PREPAY'
852 --AND D.invoice_distribution_id(+) = DECODE(lnk.source_distribution_type,
853 -- 'AP_INVOICE_DISTRIBUTIONS', lnk.source_distribution_id_num_1,null)
854 AND D.prepay_app_dist_id = lnk.source_distribution_id_num_1
855 AND R.gl_sl_link_id = AEL.gl_sl_link_id
856 AND R.je_header_id = p_journal_rec.je_header_id
857 AND R.je_line_num = p_journal_rec.je_line_num
858
859 UNION ALL
860
861 SELECT DECODE(SIGN(AEL.unrounded_accounted_dr),-1,
862 (ABS(AEL.unrounded_accounted_dr) + NVL(DECODE(SIGN(AEL.unrounded_accounted_cr),1,AEL.unrounded_accounted_cr,null),0)),
863 DECODE(SIGN(AEL.unrounded_accounted_cr),-1,null, AEL.unrounded_accounted_cr)) ACCOUNTED_CR,
864 DECODE(SIGN(AEL.unrounded_accounted_cr),-1,
865 (ABS(AEL.unrounded_accounted_cr) + NVL(DECODE(SIGN(AEL.unrounded_accounted_dr),1,AEL.unrounded_accounted_dr,null),0)),
866 DECODE(SIGN(AEL.unrounded_accounted_dr),-1,null, AEL.unrounded_accounted_dr)) ACCOUNTED_DR,
867 DECODE(SIGN(AEL.unrounded_entered_cr),-1,
868 (ABS(AEL.unrounded_entered_cr) + NVL(DECODE(SIGN(AEL.unrounded_entered_dr),1,AEL.unrounded_entered_dr,null),0)),
869 DECODE(SIGN(AEL.unrounded_entered_dr),-1,null, AEL.unrounded_entered_dr)) ENTERED_DR,
870 DECODE(SIGN(AEL.unrounded_entered_dr),-1,
871 (ABS(AEL.unrounded_entered_dr) + NVL(DECODE(SIGN(AEL.unrounded_entered_cr),1,AEL.unrounded_entered_cr,null),0)),
872 DECODE(SIGN(AEL.unrounded_entered_cr),-1,null, AEL.unrounded_entered_cr)) ENTERED_CR,
873 ael.source_table SOURCE_TABLE,
874 ael.party_id PARTY_ID,
875 ent.transaction_number TRX_NUMBER_C ,
876 I.invoice_id TRX_HDR_ID,
877 I.invoice_type_lookup_code TRX_TYPE, -- ER 9435028
878 I.source TRX_SOURCE, -- ER 9435028
879 'INV' TRX_CLASS,
880 ael.accounting_class_code ACCT_LINE_TYPE,
881 decode(ael.source_table,'AP_INVOICE_DISTRIBUTIONS',ael.source_id, NULL) TRX_DIST_ID,
882 aeh.upg_batch_id UPG_BATCH_ID
883
884 FROM
885 ap_invoices_all I,
886 xla_transaction_entities ent,
887 xla_ae_headers AEH,
888 xla_ae_lines AEL,
889 gl_import_references R
890 WHERE
891 ent.application_id = 200
892 AND ent.application_id =aeh.application_id
893 AND aeh.application_id = ael.application_id
894 AND ent.entity_code = 'AP_INVOICES'
895 AND i.invoice_id = ent.source_id_int_1
896 AND ent.entity_id = aeh.entity_id
897 AND (aeh.ledger_id = g_parameter_rec.set_of_books_id
898 OR
899 aeh.ledger_id IN (SELECT from_ledger_id
900 FROM gl_consolidation
901 WHERE to_ledger_id = g_parameter_rec.set_of_books_id)
902 )
903 AND aeh.ae_header_id = ael.ae_header_id
904 AND aeh.upg_batch_id is not null
905 AND ael.application_id = 200
906 AND R.gl_sl_link_id = AEL.gl_sl_link_id
907 AND R.je_header_id = p_journal_rec.je_header_id
908 AND R.je_line_num = p_journal_rec.je_line_num;
909
910
911 l_supplier_num po_vendors.segment1%TYPE;
912 l_err_flag NUMBER;
913 l_invoice_line_number NUMBER; -- ER 9435028
914 l_nit ap_invoice_lines_all.merchant_taxpayer_id%TYPE; -- ER 9435028
915 l_nit_name ap_invoice_lines_all.merchant_name%TYPE; -- ER 9435028
916
917
918 BEGIN
919
920 g_error_exists := 'FALSE';
921 l_err_flag := 0;
922
923
924 SELECT p_journal_rec.je_header_id,
925 p_journal_rec.je_line_num,
926 p_journal_rec.ext_nit
927 INTO g_gl_je_rec
928 FROM DUAL;
929
930 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_ap_trx : Begin ');
931
932 FOR ap_trx IN ap_detail_lines LOOP
933 l_err_flag := l_err_flag + 1;
934 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_ap_trx : After ap_trx cursor call : ' ||ap_trx.source_table);
935 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_ap_trx :Dist ID' ||to_char(ap_trx.trx_dist_id));
936 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_ap_trx :i Trx ID' ||to_char(ap_trx.trx_hdr_id));
937 FND_FILE.PUT_LINE(FND_FILE.log,'Acc Credit Amount : ' ||ap_trx.ACCOUNTED_CR); --Bug 16036092
938 FND_FILE.PUT_LINE(FND_FILE.log,'Acc Debit Amount :' ||to_char(ap_trx.ACCOUNTED_DR)); --Bug 16036092
939 FND_FILE.PUT_LINE(FND_FILE.log,'Transaction Number :' ||to_char(ap_trx.TRX_NUMBER_C)); --Bug 16036092
940 FND_FILE.PUT_LINE(FND_FILE.log,'Upgrade Batch Id : ' ||to_char(ap_trx.UPG_BATCH_ID)); --Bug 16036092
941 FND_FILE.PUT_LINE(FND_FILE.log,'Credit Amount : ' ||ap_trx.ENTERED_CR); --Bug 16036092
942 FND_FILE.PUT_LINE(FND_FILE.log,'Debit Amount :' ||to_char(ap_trx.ENTERED_DR)); --Bug 16036092
943 FND_FILE.PUT_LINE(FND_FILE.log,'Acc Class Code :' ||ap_trx.ACCT_LINE_TYPE); --Bug 16036092
944
945 g_nit_rec := NULL;
946 l_supplier_num := NULL;
947
948 FND_FILE.PUT_LINE(FND_FILE.log,'Invoice Distribution ID '||ap_trx.trx_dist_id);
949 FND_FILE.PUT_LINE(FND_FILE.log,'Invoice ID '||ap_trx.trx_hdr_id);
950
951 IF (ap_trx.source_table = 'AP_INV_DIST' OR (ap_trx.source_table = 'AP_INVOICE_DISTRIBUTIONS' AND ap_trx.UPG_BATCH_ID IS NOT NULL )) THEN
952 FND_FILE.PUT_LINE(FND_FILE.log,'Source table is AP_INV_DIST');
953 BEGIN
954 SELECT DECODE(ap_trx.acct_line_type,'LIABILITY',NULL,NVL(global_attribute2,NULL)), invoice_line_number
955 INTO l_supplier_num, l_invoice_line_number
956 FROM ap_invoice_distributions_all apida
957 WHERE apida.invoice_id = ap_trx.trx_hdr_id
958 AND apida.invoice_distribution_id = ap_trx.trx_dist_id;
959 --bug8680825
960 /*AND (apida.set_of_books_id = g_parameter_rec.set_of_books_id
961 OR apida.set_of_books_id in (SELECT from_ledger_id
962 FROM gl_consolidation
963 WHERE to_ledger_id = g_parameter_rec.set_of_books_id));*/
964 EXCEPTION
965 WHEN OTHERS THEN
966 --l_supplier_num := NULL;
967 FND_FILE.PUT_LINE(FND_FILE.log,'INSIDE EXCEPTION '||SQLERRM);
968 END;
969 /* ER 9435028 changes start*/
970 IF(ap_trx.trx_source in('SelfService','XpenseXpress') AND ap_trx.trx_type in ('EXPENSE REPORT','PAYMENT REQUEST')) THEN
971 FND_FILE.PUT_LINE(FND_FILE.log,'Deriving Merchant NIT information');
972 BEGIN
973 SELECT trim(merchant_taxpayer_id), trim(merchant_name)
974 INTO l_nit, l_nit_name
975 FROM ap_invoice_lines_all lines
976 WHERE lines.invoice_id = ap_trx.trx_hdr_id
977 AND lines.line_number = l_invoice_line_number;
978
979 EXCEPTION
980 WHEN OTHERS THEN
981 FND_FILE.PUT_LINE(FND_FILE.log,'INSIDE EXCEPTION of Deriving Merchant NIT '||SQLERRM);
982 END;
983
984 END IF;
985 /* ER 9435028 changes end*/
986
987 END IF;
988
989 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_ap_trx : After ap_trx cursor call : ' ||l_supplier_num);
990 FND_FILE.PUT_LINE(FND_FILE.log,'After ap_trx cursor call : merchant_taxpayer_id - ' ||l_nit);
991 FND_FILE.PUT_LINE(FND_FILE.log,'After ap_trx cursor call : merchant_name - ' ||l_nit_name);
992
993 /* ER 9435028 changes start*/
994 IF(ap_trx.trx_source in('SelfService','XpenseXpress') AND ap_trx.trx_type in ('EXPENSE REPORT','PAYMENT REQUEST')) THEN
995 IF l_nit IS NOT NULL THEN
996 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_ap_trx : Merchant NIT IS NOT NULL ');
997
998 BEGIN
999 FND_FILE.PUT_LINE(FND_FILE.log,'Deriving Merchant NIT information ');
1000 IF INSTR(l_nit,'-') > 0 THEN
1001 BEGIN
1002 FND_FILE.PUT_LINE(FND_FILE.log,'Merchant NIT has validation digit ');
1003 SELECT jlcgn.nit_id
1004 INTO g_nit_rec.nit_id
1005 FROM jl_co_gl_nits jlcgn
1006 WHERE jlcgn.nit = substr(l_nit,1,instr(l_nit,'-')-1);
1007
1008 EXCEPTION
1009 WHEN OTHERS THEN
1010 FND_FILE.PUT_LINE(FND_FILE.log,'Exception while extracting NIT_ID from jl_co_gl_nits '||SQLERRM);
1011 END;
1012
1013 BEGIN
1014 SELECT substr(l_nit,1,instr(l_nit,'-')-1),
1015 l_nit_name,
1016 'MERCHANT',
1017 substr(l_nit,instr(l_nit,'-')+1,1)
1018 INTO g_nit_rec.nit,
1019 g_nit_rec.nit_name,
1020 g_nit_rec.nit_type,
1021 g_nit_rec.verifying_digit
1022 FROM dual;
1023
1024 EXCEPTION
1025 WHEN OTHERS THEN
1026 FND_FILE.PUT_LINE(FND_FILE.log,'Exception while inserting NIT information into g_nit_rec '||SQLERRM);
1027 END;
1028
1029 ELSE
1030 BEGIN
1031 FND_FILE.PUT_LINE(FND_FILE.log,'Merchant NIT do not have validation digit ');
1032 SELECT jlcgn.nit_id
1033 INTO g_nit_rec.nit_id
1034 FROM jl_co_gl_nits jlcgn
1035 WHERE jlcgn.nit = l_nit;
1036
1037 EXCEPTION
1038 WHEN OTHERS THEN
1039 FND_FILE.PUT_LINE(FND_FILE.log,'Exception while extracting NIT_ID from jl_co_gl_nits '||SQLERRM);
1040 END;
1041
1042 BEGIN
1043 SELECT l_nit,
1044 l_nit_name,
1045 'MERCHANT'
1046 INTO g_nit_rec.nit,
1047 g_nit_rec.nit_name,
1048 g_nit_rec.nit_type
1049 FROM dual;
1050 EXCEPTION
1051 WHEN OTHERS THEN
1052 FND_FILE.PUT_LINE(FND_FILE.log,'Exception while inserting NIT information into g_nit_rec '||SQLERRM);
1053 END;
1054
1055
1056 END IF;
1057
1058 EXCEPTION
1059 WHEN OTHERS THEN
1060 FND_FILE.PUT_LINE(FND_FILE.log,'INSIDE Deriving Verifying digit of Merchant NIT EXCEPTION '||SQLERRM);
1061 END;
1062 END IF;
1063 /* ER 9435028 changes end*/
1064
1065
1066 ELSE IF l_supplier_num IS NULL THEN
1067 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_ap_trx : l_supplier_num IS NULL ');
1068 SELECT jlcgn.nit_id,
1069 REPLACE(povapf.num_1099,'-'),
1070 povapf.vendor_name,
1071 SUBSTR(povapf.global_attribute10,1,30),
1072 povapf.global_attribute12,
1073 NVL(l_supplier_num,povapf.segment1)
1074 INTO g_nit_rec.nit_id,
1075 g_nit_rec.nit,
1076 g_nit_rec.nit_name,
1077 g_nit_rec.nit_type,
1078 g_nit_rec.verifying_digit,
1079 l_supplier_num
1080 FROM jl_co_gl_nits jlcgn, (SELECT nvl(papf.national_identifier,nvl(aps.individual_1099,aps.num_1099)) num_1099,
1081 aps.vendor_name,
1082 aps.global_attribute10,
1083 aps.global_attribute12,
1084 aps.segment1,
1085 aps.vendor_id
1086 FROM ap_suppliers aps,
1087 (select distinct person_id ,national_identifier from per_all_people_f
1088 WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
1089 WHERE nvl(aps.employee_id, -99) = papf.person_id (+)) povapf
1090 WHERE ap_trx.party_id = povapf.vendor_id
1091 AND jlcgn.nit(+) = REPLACE(povapf.num_1099,'-') ;
1092
1093 ELSE
1094
1095 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_ap_trx : l_supplier_num IS NOT NULL ');
1096 SELECT jlcgn.nit_id,
1097 REPLACE(povapf.num_1099,'-'),
1098 povapf.vendor_name,
1099 SUBSTR(povapf.global_attribute10,1,30),
1100 povapf.global_attribute12,
1101 NVL(l_supplier_num,povapf.segment1)
1102 INTO g_nit_rec.nit_id,
1103 g_nit_rec.nit,
1104 g_nit_rec.nit_name,
1105 g_nit_rec.nit_type,
1106 g_nit_rec.verifying_digit,
1107 l_supplier_num
1108 FROM jl_co_gl_nits jlcgn, (SELECT nvl(papf.national_identifier,nvl(aps.individual_1099,aps.num_1099)) num_1099,
1109 aps.vendor_name,
1110 aps.global_attribute10,
1111 aps.global_attribute12,
1112 aps.segment1,
1113 aps.vendor_id
1114 FROM ap_suppliers aps,
1115 (select distinct person_id ,national_identifier from per_all_people_f
1116 WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
1117 WHERE nvl(aps.employee_id, -99) = papf.person_id (+)) povapf
1118 WHERE l_supplier_num = povapf.segment1
1119 AND jlcgn.nit(+) = REPLACE(povapf.num_1099,'-') ;
1120
1121 END IF;
1122 END IF;
1123
1124 g_gl_je_rec.identifier := l_supplier_num;
1125
1126 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));
1127 IF Validate_NIT(g_nit_rec, 'JL_CO_GL_SUPPLIER') THEN
1128 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));
1129 FND_FILE.PUT_LINE(FND_FILE.log,'CCID, account code : '||to_char(p_journal_rec.code_combination_id)
1130 ||'-'||p_journal_rec.account_code);
1131 FND_FILE.PUT_LINE(FND_FILE.log,' : '||g_nit_rec.nit||'-'||g_nit_rec.nit_name);
1132 FND_FILE.PUT_LINE(FND_FILE.log,'AP : acc_dr, acc_cr,ent_dr, ent_dr : '
1133 ||to_char(ap_trx.accounted_dr)||'-'||to_char(ap_trx.accounted_cr)
1134 ||'-'||to_char(ap_trx.entered_dr)||'-'||to_char(ap_trx.entered_cr));
1135
1136 INSERT INTO jl_co_gl_trx (transaction_id,
1137 process_id,
1138 set_of_books_id,
1139 code_combination_id,
1140 account_code,
1141 nit_id,
1142 period_name,
1143 period_year,
1144 period_num,
1145 je_batch_id,
1146 je_header_id,
1147 category,
1148 subledger_doc_number,
1149 je_line_num,
1150 document_number,
1151 accounting_date,
1152 currency_code,
1153 creation_date,
1154 created_by,
1155 last_update_date,
1156 last_updated_by,
1157 last_update_login,
1158 accounted_dr,
1159 accounted_cr,
1160 entered_dr,
1161 entered_cr)
1162 VALUES (jl_co_gl_trx_s.nextval,
1163 g_parameter_rec.cid,
1164 g_parameter_rec.set_of_books_id,
1165 p_journal_rec.code_combination_id,
1166 p_journal_rec.account_code,
1167 g_nit_rec.nit_id,
1168 p_journal_rec.period_name,
1169 g_period_year,
1170 g_period_num,
1171 p_journal_rec.je_batch_id,
1172 p_journal_rec.je_header_id,
1173 p_journal_rec.category,
1174 p_journal_rec.subl_doc_num,
1175 p_journal_rec.je_line_num,
1176 ap_trx.trx_number_c,
1177 p_journal_rec.accounting_date,
1178 p_journal_rec.currency,
1179 sysdate,
1180 NVL(g_parameter_rec.user_id,-1),
1181 sysdate,
1182 NVL(g_parameter_rec.user_id,-1),
1183 g_login_id,
1184 ap_trx.accounted_dr,
1185 ap_trx.accounted_cr,
1186 ap_trx.entered_dr,
1187 ap_trx.entered_cr );
1188
1189 BEGIN
1190
1191 /* Commented for bug3840010
1192
1193 SELECT 'TRUE'
1194 INTO g_error_exists
1195 FROM DUAL
1196 WHERE EXISTS (SELECT '1'
1197 FROM jl_co_gl_conc_errs jlcgce
1198 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
1199 AND jlcgce.je_line_num = p_journal_rec.je_line_num); */
1200
1201 -- New statement for bug 3840010
1202 SELECT 'TRUE'
1203 INTO g_error_exists
1204 FROM jl_co_gl_conc_errs jlcgce
1205 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
1206 AND jlcgce.je_line_num = p_journal_rec.je_line_num and rownum = 1;
1207
1208 EXCEPTION
1209 WHEN no_data_found THEN
1210 NULL;
1211 END;
1212
1213 IF NVL(g_error_exists,'FALSE') = 'TRUE' THEN
1214
1215 UPDATE gl_je_lines gljl
1216 SET co_processed_flag = 'N'
1217 WHERE gljl.je_header_id = p_journal_rec.je_header_id
1218 AND gljl.je_line_num = p_journal_rec.je_line_num
1219 AND EXISTS (SELECT 'Y'
1220 FROM jl_co_gl_trx jlcgt
1221 WHERE jlcgt.je_header_id = gljl.je_header_id
1222 AND jlcgt.je_line_num = gljl.je_line_num);
1223 DELETE FROM jl_co_gl_trx jlcgt
1224 WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
1225 AND jlcgt.je_line_num = p_journal_rec.je_line_num;
1226
1227 ELSE
1228 UPDATE gl_je_lines gljl
1229 SET co_processed_flag = 'Y'
1230 WHERE gljl.je_header_id = p_journal_rec.je_header_id
1231 AND gljl.je_line_num = p_journal_rec.je_line_num
1232 AND EXISTS (SELECT 'Y'
1233 FROM jl_co_gl_trx jlcgt
1234 WHERE jlcgt.je_header_id = gljl.je_header_id
1235 AND jlcgt.je_line_num = gljl.je_line_num);
1236
1237 END IF;
1238
1239 END IF;
1240
1241 END LOOP;
1242
1243 BEGIN
1244
1245 SELECT 'TRUE'
1246 INTO g_error_exists
1247 FROM DUAL
1248 WHERE EXISTS (SELECT '1'
1249 FROM jl_co_gl_conc_errs jlcgce
1250 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
1251 AND jlcgce.je_line_num = p_journal_rec.je_line_num);
1252 EXCEPTION
1253 WHEN no_data_found THEN
1254 FND_FILE.PUT_LINE(FND_FILE.log,'no_data_found exception');
1255 NULL;
1256 END;
1257
1258 IF NVL(g_error_exists,'FALSE') = 'TRUE' THEN
1259 UPDATE gl_je_lines gljl
1260 SET co_processed_flag = 'N'
1261 WHERE gljl.je_header_id = p_journal_rec.je_header_id
1262 AND gljl.je_line_num = p_journal_rec.je_line_num
1263 AND EXISTS (SELECT 'Y'
1264 FROM jl_co_gl_trx jlcgt
1265 WHERE jlcgt.je_header_id = gljl.je_header_id
1266 AND jlcgt.je_line_num = gljl.je_line_num);
1267 FND_FILE.PUT_LINE(FND_FILE.log,'Delete jl_co_gl_trx');
1268 DELETE FROM jl_co_gl_trx jlcgt
1269 WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
1270 AND jlcgt.je_line_num = p_journal_rec.je_line_num;
1271 END IF;
1272
1273 COMMIT;
1274 --bug8499774
1275 IF l_err_flag = 0 THEN
1276 FND_FILE.PUT_LINE(FND_FILE.log,'Call Generate_GL_trx');
1277 Generate_GL_trx(g_journal_rec);
1278 END IF;
1279
1280 EXCEPTION
1281 WHEN no_data_found THEN
1282 -- import references don't exist or are summarized for journal
1283 FND_FILE.PUT_LINE(FND_FILE.log,'No data Call Generate_GL_trx');
1284 Generate_GL_trx(g_journal_rec); -- Create with NIT 0
1285
1286 WHEN others THEN
1287 g_error_code := SQLCODE;
1288 g_error_text := SUBSTR(SQLERRM,1,240);
1289 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_AP_trx:'|| g_error_text);
1290 RAISE;
1291
1292 END Generate_AP_trx;
1293
1294
1295 PROCEDURE Generate_PO_trx
1296 (p_journal_rec IN journals%ROWTYPE ) IS
1297
1298 /*BUG 9078068 : Cursor added to accomodate Cost Mgmt Trxs of type CST_WRITE_OFFS and
1299 RCV_RECEIVING_SUB_LEDGER*/
1300 CURSOR po_detail_lines IS
1301 SELECT jlcgn.nit_id,
1302 REPLACE(povapf.num_1099,'-'),
1303 povapf.vendor_name,
1304 SUBSTR(povapf.global_attribute10,1,30),
1305 povapf.global_attribute12,
1306 povapf.segment1,
1307 p_journal_rec.je_header_id,
1308 p_journal_rec.je_line_num
1309 FROM jl_co_gl_nits jlcgn,
1310 po_headers_all poha,
1311 PO_DISTRIBUTIONS_ALL podis,
1312 (SELECT nvl(papf.national_identifier,nvl(aps.individual_1099,aps.num_1099)) num_1099,
1313 aps.vendor_name,aps.global_attribute10,aps.global_attribute12,aps.segment1,
1314 aps.vendor_id
1315 FROM ap_suppliers aps,(select distinct person_id ,national_identifier from per_all_people_f
1316 WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
1317 WHERE nvl(aps.employee_id, -99) = papf.person_id (+)) povapf,
1318 xla_ae_lines AEL,
1319 gl_import_references R,
1320 RCV_RECEIVING_SUB_LEDGER RCVSD,
1321 xla_distribution_links LNK
1322 WHERE povapf.vendor_id = poha.vendor_id
1323 AND REPLACE(povapf.num_1099,'-') = jlcgn.nit(+)
1324 AND R.je_header_id = p_journal_rec.je_header_id
1325 AND R.je_line_num = p_journal_rec.je_line_num
1326 AND R.gl_sl_link_id = AEL.gl_sl_link_id
1327 AND AEL.application_id = 707
1328 AND AEL.ae_header_id = LNK.ae_header_id
1329 AND AEL.ae_line_num = LNK.ae_line_num
1330 AND LNK.application_id = 707
1331 AND LNK.SOURCE_DISTRIBUTION_TYPE = 'RCV_RECEIVING_SUB_LEDGER'
1332 AND LNK.SOURCE_DISTRIBUTION_ID_NUM_1 = RCVSD.RCV_SUB_LEDGER_ID
1333 AND RCVSD.reference3 = podis.PO_DISTRIBUTION_ID
1334 AND podis.po_header_id = poha.po_header_id
1335
1336 UNION
1337
1338 SELECT jlcgn.nit_id,
1339 REPLACE(povapf.num_1099,'-'),
1340 povapf.vendor_name,
1341 SUBSTR(povapf.global_attribute10,1,30),
1342 povapf.global_attribute12,
1343 povapf.segment1,
1344 p_journal_rec.je_header_id,
1345 p_journal_rec.je_line_num
1346 FROM jl_co_gl_nits jlcgn,
1347 po_headers_all poha,
1348 PO_DISTRIBUTIONS_ALL podis,
1349 cst_write_offs cwo,
1350 xla_ae_lines AEL, gl_import_references gir,
1351 xla_distribution_links LNK,(SELECT nvl(papf.national_identifier,nvl(aps.individual_1099,aps.num_1099)) num_1099, aps.vendor_name,
1352 aps.global_attribute10, aps.global_attribute12, aps.segment1, aps.vendor_id
1353 FROM ap_suppliers aps,(select distinct person_id , national_identifier
1354 from per_all_people_f
1355 WHERE trunc(sysdate) BETWEEN effective_start_date AND effective_end_date) papf
1356 WHERE nvl(aps.employee_id, -99) = papf.person_id (+)) povapf
1357 WHERE povapf.vendor_id = poha.vendor_id
1358 AND REPLACE(povapf.num_1099,'-') = jlcgn.nit(+)
1359 AND gir.je_header_id = p_journal_rec.je_header_id
1360 AND gir.je_line_num = p_journal_rec.je_line_num
1361 AND gir.gl_sl_link_id = AEL.gl_sl_link_id
1362 AND AEL.application_id = 707
1363 AND AEL.ae_header_id = LNK.ae_header_id
1364 AND AEL.ae_line_num = LNK.ae_line_num
1365 AND LNK.application_id = 707
1366 AND LNK.SOURCE_DISTRIBUTION_TYPE = 'CST_WRITE_OFFS'
1367 AND LNK.SOURCE_DISTRIBUTION_ID_NUM_1 = cwo.write_off_id
1368 AND cwo.po_distribution_id = podis.PO_DISTRIBUTION_ID
1369 AND podis.po_header_id = poha.po_header_id;
1370
1371 BEGIN
1372 FND_FILE.PUT_LINE(FND_FILE.log,'Inside Generate PO transaction');
1373 g_error_exists := 'FALSE';
1374 g_nit_rec := NULL;
1375 g_gl_je_rec := NULL;
1376
1377 IF p_journal_rec.category in ('Accrual','Receiving') THEN
1378 FND_FILE.PUT_LINE(FND_FILE.log,'Inside PO - 001');
1379
1380 --BUG 9078068
1381 --Begin
1382 OPEN po_detail_lines;
1383 FND_FILE.PUT_LINE(FND_FILE.log,'Opened PO Cursor');
1384 FETCH po_detail_lines
1385 INTO g_nit_rec.nit_id,
1386 g_nit_rec.nit,
1387 g_nit_rec.nit_name,
1388 g_nit_rec.nit_type,
1389 g_nit_rec.verifying_digit,
1390 g_gl_je_rec.identifier,
1391 g_gl_je_rec.je_header_id,
1392 g_gl_je_rec.je_line_num;
1393 FND_FILE.PUT_LINE(FND_FILE.log,'Fetched PO Cursor');
1394 CLOSE po_detail_lines;
1395 --End
1396
1397 FND_FILE.PUT_LINE(FND_FILE.log,'Closed PO Cursor');
1398 FND_FILE.PUT_LINE(FND_FILE.log,'Inside PO Before Validate - 002');
1399
1400 IF Validate_NIT(g_nit_rec, 'JL_CO_GL_SUPPLIER') THEN
1401 FND_FILE.PUT_LINE(FND_FILE.log,'Inside Validate');
1402 INSERT INTO jl_co_gl_trx (transaction_id,
1403 process_id,
1404 set_of_books_id,
1405 code_combination_id,
1406 account_code,
1407 nit_id,
1408 period_name,
1409 period_year,
1410 period_num,
1411 je_batch_id,
1412 je_header_id,
1413 category,
1414 subledger_doc_number,
1415 je_line_num,
1416 document_number,
1417 accounting_date,
1418 currency_code,
1419 creation_date,
1420 created_by,
1421 last_update_date,
1422 last_updated_by,
1423 last_update_login,
1424 accounted_dr,
1425 accounted_cr,
1426 entered_dr,
1427 entered_cr )
1428 VALUES (jl_co_gl_trx_s.nextval,
1429 g_parameter_rec.cid,
1430 g_parameter_rec.set_of_books_id,
1431 p_journal_rec.code_combination_id,
1432 p_journal_rec.account_code,
1433 g_nit_rec.nit_id,
1434 p_journal_rec.period_name,
1435 g_period_year,
1436 g_period_num,
1437 p_journal_rec.je_batch_id,
1438 p_journal_rec.je_header_id,
1439 p_journal_rec.category,
1440 p_journal_rec.subl_doc_num,
1441 p_journal_rec.je_line_num,
1442 p_journal_rec.ref_4,
1443 p_journal_rec.accounting_date,
1444 p_journal_rec.currency,
1445 sysdate,
1446 NVL(g_parameter_rec.user_id,-1),
1447 sysdate,
1448 NVL(g_parameter_rec.user_id,-1),
1449 g_login_id,
1450 DECODE(sign(p_journal_rec.accounted_cr),-1,
1451 (abs(p_journal_rec.accounted_cr) +
1452 NVL(DECODE(sign(p_journal_rec.accounted_dr),
1453 1,p_journal_rec.accounted_dr,NULL),0)),
1454 DECODE(sign(p_journal_rec.accounted_dr),-1,
1455 NULL,p_journal_rec.accounted_dr)),
1456 DECODE(sign(p_journal_rec.accounted_dr),-1,
1457 (abs(p_journal_rec.accounted_dr) +
1458 NVL(DECODE(sign(p_journal_rec.accounted_cr),
1459 1,p_journal_rec.accounted_cr,NULL),0)),
1460 DECODE(sign(p_journal_rec.accounted_cr),-1,
1461 NULL,p_journal_rec.accounted_cr)),
1462 DECODE(sign(p_journal_rec.entered_cr),-1,
1463 (abs(p_journal_rec.entered_cr) +
1464 NVL(DECODE(sign(p_journal_rec.entered_dr),
1465 1,p_journal_rec.entered_dr,NULL),0)),
1466 DECODE(sign(p_journal_rec.entered_dr),-1,
1467 NULL,p_journal_rec.entered_dr)),
1468 DECODE(sign(p_journal_rec.entered_dr),-1,
1469 (abs(p_journal_rec.entered_dr) +
1470 NVL(DECODE(sign(p_journal_rec.entered_cr),
1471 1,p_journal_rec.entered_cr,NULL),0)),
1472 DECODE(sign(p_journal_rec.entered_cr),-1,
1473 NULL,p_journal_rec.entered_cr)) );
1474
1475 BEGIN
1476 FND_FILE.PUT_LINE(FND_FILE.log,'Inside PO - 003');
1477 SELECT 'TRUE'
1478 INTO g_error_exists
1479 FROM DUAL
1480 WHERE EXISTS (SELECT '1'
1481 FROM jl_co_gl_conc_errs jlcgce
1482 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
1483 AND jlcgce.je_line_num = p_journal_rec.je_line_num);
1484
1485 EXCEPTION
1486 WHEN no_data_found THEN
1487 NULL;
1488 END;
1489
1490 IF NVL(g_error_exists,'FALSE') = 'TRUE' THEN
1491 FND_FILE.PUT_LINE(FND_FILE.log,'Inside PO - 004.1');
1492 UPDATE gl_je_lines gljl
1493 SET co_processed_flag = 'N'
1494 WHERE gljl.je_header_id = p_journal_rec.je_header_id
1495 AND gljl.je_line_num = p_journal_rec.je_line_num
1496 AND EXISTS (SELECT 'Y'
1497 FROM jl_co_gl_trx jlcgt
1498 WHERE jlcgt.je_header_id = gljl.je_header_id
1499 AND jlcgt.je_line_num = gljl.je_line_num);
1500
1501 DELETE FROM jl_co_gl_trx jlcgt
1502 WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
1503 AND jlcgt.je_line_num = p_journal_rec.je_line_num;
1504
1505 ELSE
1506 FND_FILE.PUT_LINE(FND_FILE.log,'Inside PO - 004.2');
1507 UPDATE gl_je_lines gljl
1508 SET co_processed_flag = 'Y'
1509 WHERE gljl.je_header_id = p_journal_rec.je_header_id
1510 AND gljl.je_line_num = p_journal_rec.je_line_num
1511 AND EXISTS (SELECT 'Y'
1512 FROM jl_co_gl_trx jlcgt
1513 WHERE jlcgt.je_header_id = gljl.je_header_id
1514 AND jlcgt.je_line_num = gljl.je_line_num);
1515
1516 END IF;
1517 END IF;
1518 END IF;
1519
1520 COMMIT;
1521 FND_FILE.PUT_LINE(FND_FILE.log,'Inside PO - 005');
1522
1523 EXCEPTION
1524 WHEN no_data_found THEN
1525 FND_FILE.PUT_LINE(FND_FILE.log,'Inside PO - EXCEPTION no data found');
1526 -- import references don't exist or are summarized for journal
1527 Generate_GL_trx(g_journal_rec); -- Create with NIT 0
1528
1529 WHEN others THEN
1530 FND_FILE.PUT_LINE(FND_FILE.log,'Inside PO - EXCEPTION others');
1531 g_error_code := SQLCODE;
1532 g_error_text := SUBSTR(SQLERRM,1,240);
1533 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_PO_trx:'|| g_error_text);
1534 RAISE;
1535
1536 END Generate_PO_trx;
1537
1538
1539 -- Bug 16097573 Start
1540
1541 PROCEDURE Generate_11i_PO_trx(p_journal_rec IN journals%ROWTYPE ) IS
1542 BEGIN
1543
1544 g_error_exists := 'FALSE';
1545 g_nit_rec := NULL;
1546 g_gl_je_rec := NULL;
1547
1548 FND_FILE.PUT_LINE(FND_FILE.log,'Executing Generate_11i_PO_trx procedure for category ' || p_journal_rec.category);
1549 IF p_journal_rec.category in ('Accrual','Receiving') THEN
1550
1551 SELECT jlcgn.nit_id,
1552 REPLACE(povpapf.num_1099,'-'),
1553 povpapf.vendor_name,
1554 SUBSTR(povpapf.global_attribute10,1,30),
1555 povpapf.global_attribute12,
1556 povpapf.segment1,
1557 p_journal_rec.je_header_id,
1558 p_journal_rec.je_line_num
1559 INTO g_nit_rec.nit_id,
1560 g_nit_rec.nit,
1561 g_nit_rec.nit_name,
1562 g_nit_rec.nit_type,
1563 g_nit_rec.verifying_digit,
1564 g_gl_je_rec.identifier,
1565 g_gl_je_rec.je_header_id,
1566 g_gl_je_rec.je_line_num
1567 FROM jl_co_gl_nits jlcgn,
1568 po_headers_all poha ,
1569 (SELECT nvl(papf.national_identifier,nvl(pov.individual_1099,pov.num_1099)) num_1099,
1570 pov.vendor_name,
1571 pov.global_attribute10,
1572 pov.global_attribute12,
1573 pov.segment1,
1574 pov.vendor_id
1575 FROM po_vendors pov,
1576 (select distinct person_id ,national_identifier from per_all_people_f
1577 WHERE nvl(EFFECTIVE_END_DATE,sysdate) >= sysdate) papf --bug 8664192
1578 WHERE nvl(pov.employee_id, -99) = papf.person_id (+))povpapf
1579 WHERE poha.po_header_id = TO_NUMBER(p_journal_rec.ref_2)
1580 AND povpapf.vendor_id = poha.vendor_id
1581 AND REPLACE(povpapf.num_1099,'-') = jlcgn.nit(+);
1582
1583 FND_FILE.PUT_LINE(FND_FILE.log,'Select successfull for 11i PO, executing Validate_NIT');
1584 IF Validate_NIT(g_nit_rec, 'JL_CO_GL_SUPPLIER') THEN
1585
1586 INSERT INTO jl_co_gl_trx (
1587 transaction_id,
1588 process_id,
1589 set_of_books_id,
1590 code_combination_id,
1591 account_code,
1592 nit_id,
1593 period_name,
1594 period_year,
1595 period_num,
1596 je_batch_id,
1597 je_header_id,
1598 category,
1599 subledger_doc_number,
1600 je_line_num,
1601 document_number,
1602 accounting_date,
1603 currency_code,
1604 creation_date,
1605 created_by,
1606 last_update_date,
1607 last_updated_by,
1608 last_update_login,
1609 accounted_dr,
1610 accounted_cr,
1611 entered_dr,
1612 entered_cr )
1613 VALUES ( jl_co_gl_trx_s.nextval,
1614 g_parameter_rec.cid,
1615 g_parameter_rec.set_of_books_id,
1616 p_journal_rec.code_combination_id,
1617 p_journal_rec.account_code,
1618 g_nit_rec.nit_id,
1619 p_journal_rec.period_name,
1620 g_period_year,
1621 g_period_num,
1622 p_journal_rec.je_batch_id,
1623 p_journal_rec.je_header_id,
1624 p_journal_rec.category,
1625 p_journal_rec.subl_doc_num,
1626 p_journal_rec.je_line_num,
1627 p_journal_rec.ref_4,
1628 p_journal_rec.accounting_date,
1629 p_journal_rec.currency,
1630 sysdate,
1631 NVL(g_parameter_rec.user_id,-1),
1632 sysdate,
1633 NVL(g_parameter_rec.user_id,-1),
1634 g_login_id,
1635 DECODE(sign(p_journal_rec.accounted_cr),-1,
1636 (abs(p_journal_rec.accounted_cr) +
1637 NVL(DECODE(sign(p_journal_rec.accounted_dr),
1638 1,p_journal_rec.accounted_dr,NULL),0)),
1639 DECODE(sign(p_journal_rec.accounted_dr),-1,
1640 NULL,p_journal_rec.accounted_dr)),
1641 DECODE(sign(p_journal_rec.accounted_dr),-1,
1642 (abs(p_journal_rec.accounted_dr) +
1643 NVL(DECODE(sign(p_journal_rec.accounted_cr),
1644 1,p_journal_rec.accounted_cr,NULL),0)),
1645 DECODE(sign(p_journal_rec.accounted_cr),-1,
1646 NULL,p_journal_rec.accounted_cr)),
1647 DECODE(sign(p_journal_rec.entered_cr),-1,
1648 (abs(p_journal_rec.entered_cr) +
1649 NVL(DECODE(sign(p_journal_rec.entered_dr),
1650 1,p_journal_rec.entered_dr,NULL),0)),
1651 DECODE(sign(p_journal_rec.entered_dr),-1,
1652 NULL,p_journal_rec.entered_dr)),
1653 DECODE(sign(p_journal_rec.entered_dr),-1,
1654 (abs(p_journal_rec.entered_dr) +
1655 NVL(DECODE(sign(p_journal_rec.entered_cr),
1656 1,p_journal_rec.entered_cr,NULL),0)),
1657 DECODE(sign(p_journal_rec.entered_cr),-1,
1658 NULL,p_journal_rec.entered_cr)) );
1659 FND_FILE.PUT_LINE(FND_FILE.log,'11i PO, insered record into jl_co_gl_trx');
1660
1661 END IF;
1662
1663 END IF;
1664
1665 COMMIT;
1666
1667 FND_FILE.PUT_LINE(FND_FILE.log,'Ended Generate_11i_PO_trx procedure');
1668 EXCEPTION
1669 WHEN no_data_found THEN
1670 -- import references don't exist or are summarized for journal
1671 Generate_GL_trx(g_journal_rec); -- Create with NIT 0
1672 WHEN others THEN
1673 g_error_code := SQLCODE;
1674 g_error_text := SUBSTR(SQLERRM,1,240);
1675 FND_FILE.PUT_LINE(FND_FILE.log,'Generate__11i_PO_trx:'|| g_error_text);
1676 RAISE;
1677
1678 END Generate_11i_PO_trx;
1679 -- Bug 16097573 End
1680
1681 -- Bug 16030118 Start
1682
1683 PROCEDURE Generate_11i_AR_trx(p_journal_rec IN journals%ROWTYPE ) IS
1684
1685 CURSOR ar_detail_lines IS
1686 SELECT reference_1,
1687 reference_2,
1688 reference_3,
1689 reference_4,
1690 reference_5,
1691 reference_6,
1692 reference_7,
1693 reference_8,
1694 reference_9,
1695 reference_10
1696 FROM gl_import_references glir
1697 WHERE glir.je_header_id = p_journal_rec.je_header_id
1698 AND glir.je_line_num = p_journal_rec.je_line_num;
1699
1700 l_acc_dr gl_je_lines.accounted_dr%TYPE;
1701 l_acc_cr gl_je_lines.accounted_cr%TYPE;
1702 l_ent_dr gl_je_lines.entered_dr%TYPE;
1703 l_ent_cr gl_je_lines.entered_cr%TYPE;
1704 l_customer_num po_vendors.segment1%TYPE;
1705 l_identifier_type VARCHAR2(30);
1706
1707 BEGIN
1708
1709 g_error_exists := 'FALSE';
1710
1711 SELECT p_journal_rec.je_header_id,
1712 p_journal_rec.je_line_num,
1713 p_journal_rec.ext_nit
1714 INTO g_gl_je_rec
1715 FROM DUAL;
1716
1717 FOR ar_trx IN ar_detail_lines LOOP
1718
1719 g_nit_rec := NULL;
1720 l_customer_num := NULL;
1721 l_identifier_type := 'JL_CO_GL_CUSTOMER';
1722
1723 IF ( ar_trx.reference_10 IN ('RA_CUST_TRX_LINE_GL_DIST',
1724 'AR_TRANSACTION_HISTORY', --bug 6868395
1725 'AR_ADJUSTMENTS',
1726 'AR_CASH_RECEIPT_HISTORY',
1727 'AR_MISC_CASH_DISTRIBUTIONS',
1728 'AR_RECEIVABLE_APPLICATIONS')) THEN
1729
1730 l_ent_dr := p_journal_rec.entered_dr;
1731 l_ent_cr := p_journal_rec.entered_cr;
1732 l_acc_dr := p_journal_rec.accounted_dr;
1733 l_acc_cr := p_journal_rec.accounted_cr;
1734
1735 END IF;
1736
1737 IF ( ar_trx.reference_8 = 'MISC' ) THEN
1738
1739 SELECT NVL(jlcgn1.nit_id, jlcgn.nit_id),
1740 REPLACE(NVL(apbb1.global_attribute11,
1741 apbb.global_attribute11),'-'),
1742 NVL(apbb1.bank_name,apbb.bank_name),
1743 SUBSTR(NVL(apbb1.global_attribute10,
1744 apbb.global_attribute10),1,30),
1745 NVL(apbb1.global_attribute12,apbb.global_attribute12),
1746 NVL(apbb1.bank_name,apbb.bank_name)
1747 INTO g_nit_rec.nit_id,
1748 g_nit_rec.nit,
1749 g_nit_rec.nit_name,
1750 g_nit_rec.nit_type,
1751 g_nit_rec.verifying_digit,
1752 g_gl_je_rec.identifier
1753 FROM jl_co_gl_nits jlcgn1,
1754 jl_co_gl_nits jlcgn,
1755 ap_bank_branches apbb1,
1756 ap_bank_branches apbb,
1757 ap_bank_accounts_all apbaa,
1758 ar_cash_receipts_all arcra
1759 WHERE arcra.cash_receipt_id = TO_NUMBER(ar_trx.reference_2)
1760 AND arcra.set_of_books_id = g_parameter_rec.set_of_books_id
1761 AND arcra.remittance_bank_account_id = apbaa.bank_account_id
1762 AND apbaa.bank_branch_id = apbb.bank_branch_id
1763 AND apbb1.bank_branch_id(+) =
1764 TO_NUMBER(NVL(apbb.global_attribute14,'0'))
1765 AND REPLACE(apbb.global_attribute11,'-') = jlcgn.nit(+)
1766 AND REPLACE(apbb1.global_attribute11,'-') = jlcgn1.nit(+);
1767
1768 l_identifier_type := 'JL_CO_GL_BANK';
1769
1770 ELSIF (( l_customer_num IS NULL ) AND
1771 ( ar_trx.reference_8 <> 'MISC' )) THEN
1772
1773 BEGIN
1774
1775 SELECT jlcgn.nit_id,
1776 REPLACE(rac.jgzz_fiscal_code,'-'),
1777 rac.party_name,
1778 SUBSTR(custacct.global_attribute10,1,30),
1779 custacct.global_attribute12,
1780 rac.party_number
1781 INTO g_nit_rec.nit_id,
1782 g_nit_rec.nit,
1783 g_nit_rec.nit_name,
1784 g_nit_rec.nit_type,
1785 g_nit_rec.verifying_digit,
1786 l_customer_num
1787 FROM jl_co_gl_nits jlcgn,
1788 hz_parties rac,
1789 hz_cust_accounts custacct
1790 WHERE custacct.cust_account_id = TO_NUMBER(ar_trx.reference_7)
1791 AND REPLACE(rac.jgzz_fiscal_code,'-') = jlcgn.nit(+)
1792 AND custacct.party_id=rac.party_id;
1793
1794 EXCEPTION
1795 WHEN no_data_found THEN
1796
1797 SELECT nit_id,
1798 nit,
1799 name,
1800 type,
1801 verifying_digit
1802 INTO g_nit_rec.nit_id,
1803 g_nit_rec.nit,
1804 g_nit_rec.nit_name,
1805 g_nit_rec.nit_type,
1806 g_nit_rec.verifying_digit
1807 FROM jl_co_gl_nits
1808 WHERE nit_id = g_default_nit_id;
1809
1810 END;
1811
1812 g_gl_je_rec.identifier := l_customer_num;
1813
1814 END IF;
1815
1816
1817 IF Validate_NIT(g_nit_rec,l_identifier_type) THEN
1818
1819 INSERT INTO jl_co_gl_trx (
1820 transaction_id,
1821 process_id,
1822 set_of_books_id,
1823 code_combination_id,
1824 account_code,
1825 nit_id,
1826 period_name,
1827 period_year,
1828 period_num,
1829 je_batch_id,
1830 je_header_id,
1831 category,
1832 subledger_doc_number,
1833 je_line_num,
1834 document_number,
1835 accounting_date,
1836 currency_code,
1837 creation_date,
1838 created_by,
1839 last_update_date,
1840 last_updated_by,
1841 last_update_login,
1842 accounted_dr,
1843 accounted_cr,
1844 entered_dr,
1845 entered_cr)
1846 VALUES ( jl_co_gl_trx_s.nextval,
1847 g_parameter_rec.cid,
1848 g_parameter_rec.set_of_books_id,
1849 p_journal_rec.code_combination_id,
1850 p_journal_rec.account_code,
1851 g_nit_rec.nit_id,
1852 p_journal_rec.period_name,
1853 g_period_year,
1854 g_period_num,
1855 p_journal_rec.je_batch_id,
1856 p_journal_rec.je_header_id,
1857 p_journal_rec.category,
1858 p_journal_rec.subl_doc_num,
1859 p_journal_rec.je_line_num,
1860 DECODE(ar_trx.reference_8,'ADJ',
1861 ar_trx.reference_5,ar_trx.reference_4),
1862 p_journal_rec.accounting_date,
1863 p_journal_rec.currency,
1864 sysdate,
1865 NVL(g_parameter_rec.user_id,-1),
1866 sysdate,
1867 NVL(g_parameter_rec.user_id,-1),
1868 g_login_id,
1869 l_acc_dr,
1870 l_acc_cr,
1871 l_ent_dr,
1872 l_ent_cr );
1873 END IF;
1874
1875 END LOOP;
1876
1877 -- since these are summary JE lines that are being processed,
1878 -- if any detail trx associated with the JE line fails then
1879 -- none of the trx should be processed
1880
1881 BEGIN
1882
1883 SELECT 'TRUE'
1884 INTO g_error_exists
1885 FROM DUAL
1886 WHERE EXISTS (SELECT '1'
1887 FROM jl_co_gl_conc_errs jlcgce
1888 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
1889 AND jlcgce.je_line_num = p_journal_rec.je_line_num);
1890
1891 EXCEPTION
1892 WHEN no_data_found THEN
1893 NULL;
1894 END;
1895
1896 IF NVL(g_error_exists,'FALSE') = 'TRUE' THEN
1897 DELETE FROM jl_co_gl_trx jlcgt
1898 WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
1899 AND jlcgt.je_line_num = p_journal_rec.je_line_num;
1900 END IF;
1901
1902 COMMIT;
1903
1904 EXCEPTION
1905 WHEN no_data_found THEN
1906 -- import references don't exist or are summarized for journal
1907 Generate_GL_trx(g_journal_rec); -- Create with NIT 0
1908 WHEN others THEN
1909 g_error_code := SQLCODE;
1910 g_error_text := SUBSTR(SQLERRM,1,240);
1911 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_AR_trx:'|| g_error_text);
1912 RAISE;
1913 END Generate_11i_AR_trx;
1914 -- Bug 16030118 End
1915
1916 PROCEDURE Generate_AR_trx
1917 (p_journal_rec IN journals%ROWTYPE ) IS
1918
1919 CURSOR ar_detail_lines IS
1920 -- Bug 13805457 Start
1921 -- Dalase Bug 16074336 Start
1922 SELECT DECODE(SIGN(AEL.accounted_dr),-1,
1923 (ABS(AEL.accounted_dr) + NVL(DECODE(SIGN(AEL.accounted_cr),1,AEL.accounted_cr,null),0)),
1924 DECODE(SIGN(AEL.accounted_cr),-1,null, AEL.accounted_cr)) ACCOUNTED_CR,
1925 DECODE(SIGN(AEL.accounted_cr),-1,
1926 (ABS(AEL.accounted_cr) + NVL(DECODE(SIGN(AEL.accounted_dr),1,AEL.accounted_dr,null),0)),
1927 DECODE(SIGN(AEL.accounted_dr),-1,null, AEL.accounted_dr)) ACCOUNTED_DR,
1928 DECODE(SIGN(AEL.entered_cr),-1,
1929 (ABS(AEL.entered_cr) + NVL(DECODE(SIGN(AEL.entered_dr),1,AEL.entered_dr,null),0)),
1930 DECODE(SIGN(AEL.entered_dr),-1,null, AEL.entered_dr)) ENTERED_DR,
1931 DECODE(SIGN(AEL.entered_dr),-1,
1932 (ABS(AEL.entered_dr) + NVL(DECODE(SIGN(AEL.entered_cr),1,AEL.entered_cr,null),0)),
1933 DECODE(SIGN(AEL.entered_cr),-1,null, AEL.entered_cr)) ENTERED_CR,
1934 -- Dalase Bug 16074336 End
1935 -- Bug 13805457 End
1936 ent.source_id_int_1,
1937 ent.transaction_number,
1938 ent.entity_code,
1939 ael.party_id,
1940 et.event_class_code
1941 FROM xla_transaction_entities ent,
1942 xla_ae_headers AEH,
1943 xla_ae_lines AEL,
1944 gl_import_references R,
1945 xla_event_types_b et
1946 WHERE
1947 ent.application_id = 222
1948 --bug8680825
1949 /*and (ent.ledger_id = g_parameter_rec.set_of_books_id
1950 or ent.ledger_id in (SELECT from_ledger_id
1951 FROM gl_consolidation
1952 WHERE to_ledger_id = g_parameter_rec.set_of_books_id))*/
1953
1954 -- and ent.entity_code = 'TRANSACTIONS'
1955 --and i.invoice_id = ent.source_id_int_1
1956 AND ent.entity_id = aeh.entity_id
1957 --AND AEH.ledger_id = ent.ledger_id --bug8680825
1958 AND aeh.ae_header_id = ael.ae_header_id
1959 AND (aeh.ledger_id = g_parameter_rec.set_of_books_id --bug8680825
1960 OR
1961 aeh.ledger_id IN (SELECT from_ledger_id
1962 FROM gl_consolidation
1963 WHERE to_ledger_id = g_parameter_rec.set_of_books_id)
1964 )
1965 AND ael.application_id = 222
1966 AND R.gl_sl_link_id = AEL.gl_sl_link_id
1967 AND R.je_header_id = p_journal_rec.je_header_id
1968 AND R.je_line_num = p_journal_rec.je_line_num
1969 AND et.event_type_code = aeh.event_type_code;
1970
1971
1972 l_acc_dr gl_je_lines.accounted_dr%TYPE;
1973 l_acc_cr gl_je_lines.accounted_cr%TYPE;
1974 l_ent_dr gl_je_lines.entered_dr%TYPE;
1975 l_ent_cr gl_je_lines.entered_cr%TYPE;
1976 l_customer_num po_vendors.segment1%TYPE;
1977 l_identifier_type VARCHAR2(30);
1978 l_country_code varchar2(30);
1979 l_branch_country_code varchar2(60);
1980 l_party_id number;
1981 l_receipt_id number;
1982 l_err_flag number;
1983
1984 BEGIN
1985
1986
1987 g_error_exists := 'FALSE';
1988 l_err_flag := 0;
1989
1990 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_AR_trx(+)');
1991
1992 SELECT p_journal_rec.je_header_id,
1993 p_journal_rec.je_line_num,
1994 p_journal_rec.ext_nit
1995 INTO g_gl_je_rec
1996 FROM DUAL;
1997 FND_FILE.PUT_LINE(FND_FILE.log,'je_header_id, je_line_num : '||to_char(p_journal_rec.je_header_id)
1998 ||'-'||to_char(p_journal_rec.je_line_num));
1999
2000 FND_FILE.PUT_LINE(FND_FILE.log,'Opening AR Cursor');
2001
2002 FOR ar_trx IN ar_detail_lines LOOP
2003 FND_FILE.PUT_LINE(FND_FILE.log,'Inside AR Cursor');
2004 -- Bug 14283808 Start
2005 FND_FILE.PUT_LINE(FND_FILE.log,'Accounted Dr ' || ar_trx.accounted_dr);
2006 FND_FILE.PUT_LINE(FND_FILE.log,'Accounted Cr ' || ar_trx.accounted_cr);
2007 FND_FILE.PUT_LINE(FND_FILE.log,'Entered Dr ' || ar_trx.accounted_dr);
2008 FND_FILE.PUT_LINE(FND_FILE.log,'Entered Cr ' || ar_trx.accounted_cr);
2009 FND_FILE.PUT_LINE(FND_FILE.log,'Source Id Int 1 ' || ar_trx.source_id_int_1);
2010 FND_FILE.PUT_LINE(FND_FILE.log,'Transaction Number ' || ar_trx.transaction_number);
2011 FND_FILE.PUT_LINE(FND_FILE.log,'Entity Code ' || ar_trx.entity_code);
2012 FND_FILE.PUT_LINE(FND_FILE.log,'Party ID ' || ar_trx.party_id);
2013 FND_FILE.PUT_LINE(FND_FILE.log,'Event Class code ' || ar_trx.event_class_code);
2014 -- Bug 14283808 End
2015 l_err_flag := l_err_flag + 1;
2016 g_nit_rec := NULL;
2017 l_customer_num := NULL;
2018 l_identifier_type := 'JL_CO_GL_CUSTOMER';
2019
2020 IF ar_trx.entity_code IN ('TRANSACTIONS',
2021 'RECEIPTS',
2022 -- Bug 13836017 Start
2023 'BILLS_RECEIVABLE',
2024 -- Bug 13836017 End
2025 'ADJUSTMENTS') THEN
2026 l_ent_dr := ar_trx.entered_dr; --bug 7169346
2027 l_ent_cr := ar_trx.entered_cr; --bug 7169346
2028 l_acc_dr := ar_trx.accounted_dr; --bug 7169346
2029 l_acc_cr := ar_trx.accounted_cr; --bug 7169346
2030 END IF;
2031
2032 IF ( ar_trx.event_class_code = 'MISC_RECEIPT' ) THEN
2033 FND_FILE.PUT_LINE(FND_FILE.log,'Event Class Code IS Misc Receipts');
2034 FND_FILE.PUT_LINE(FND_FILE.log,'Misc Receipts Query(+)');
2035 SELECT nit.nit_id,
2036 -- Bug 14283808 Start
2037 substr(party.jgzz_fiscal_code,1,decode(instr(party.jgzz_fiscal_code,'-'),0,length(party.jgzz_fiscal_code)-1,instr(party.jgzz_fiscal_code,'-')-1)), --bug9078068
2038 -- Bug 14283808 End
2039 br.bank_name,
2040 -- Bug 14283808 Start
2041 --party.country, -- nit type
2042 BankOrgProfile.home_country,
2043 substr(party.jgzz_fiscal_code,decode(instr(party.jgzz_fiscal_code,'-'),0,length(party.jgzz_fiscal_code),instr(party.jgzz_fiscal_code,'-')+1),1), --bug9078068
2044 -- Bug 14283808 End
2045 br.bank_name
2046 INTO g_nit_rec.nit_id,
2047 g_nit_rec.nit,
2048 g_nit_rec.nit_name,
2049 l_branch_country_code, --g_nit_rec.nit_type,
2050 g_nit_rec.verifying_digit,
2051 g_gl_je_rec.identifier
2052 FROM jl_co_gl_nits nit,
2053 hz_parties party,
2054 ce_bank_branches_v br,
2055 ce_bank_accounts ce_accts,
2056 ce_bank_acct_uses_all acctuse,
2057 -- Bug 14283808 Start
2058 ar_cash_receipts_all arcash,
2059 hz_organization_profiles BankOrgProfile
2060 -- Bug 14283808 End
2061 WHERE arcash.cash_receipt_id = ar_trx.source_id_int_1
2062 --AND arcash.set_of_books_id = g_parameter_rec.set_of_books_id --BUG 9078068
2063 AND acctuse.bank_acct_use_id = arcash.remit_bank_acct_use_id
2064 AND acctuse.bank_account_id = ce_accts.bank_account_id
2065 AND ce_accts.bank_branch_id = br.branch_party_id
2066 AND br.bank_party_id = party.party_id
2067 -- Bug 14283808 Start
2068 AND BankOrgProfile.party_id = party.party_id
2069 AND SYSDATE BETWEEN TRUNC(BankOrgProfile.effective_start_date) AND NVL(TRUNC(BankOrgProfile.effective_end_date), SYSDATE+1)
2070 AND nit.nit = substr(party.jgzz_fiscal_code,1,decode(instr(party.jgzz_fiscal_code,'-'),0,length(party.jgzz_fiscal_code)-1, instr(party.jgzz_fiscal_code,'-')-1));
2071 FND_FILE.PUT_LINE(FND_FILE.log,'g_nit_rec.nit_id ' || g_nit_rec.nit_id);
2072 FND_FILE.PUT_LINE(FND_FILE.log,'g_nit_rec.nit ' || g_nit_rec.nit);
2073 FND_FILE.PUT_LINE(FND_FILE.log,'g_nit_rec.nit_name ' || g_nit_rec.nit_name);
2074 FND_FILE.PUT_LINE(FND_FILE.log,'l_branch_country_code ' || l_branch_country_code);
2075 FND_FILE.PUT_LINE(FND_FILE.log,'g_nit_rec.verifying_digit ' || g_nit_rec.verifying_digit);
2076 FND_FILE.PUT_LINE(FND_FILE.log,'g_gl_je_rec.identifier ' || g_gl_je_rec.identifier);
2077 -- Bug 14283808 End
2078
2079 FND_FILE.PUT_LINE(FND_FILE.log,'Misc Receipts Query(-)');
2080
2081 l_country_code := fnd_profile.value ('JGZZ_COUNTRY_CODE');
2082
2083 FND_FILE.PUT_LINE(FND_FILE.log,'l_country_code - l_branch_country_code : '
2084 ||l_country_code||'--'||l_branch_country_code);
2085
2086 IF l_country_code = l_branch_country_code THEN
2087 g_nit_rec.nit_type := 'LEGAL_ENTITY';
2088 ELSE
2089 g_nit_rec.nit_type := 'FOREIGN_ENTITY';
2090 END IF;
2091
2092 FND_FILE.PUT_LINE(FND_FILE.log,'g_nit_rec.nit_type : '||g_nit_rec.nit_type);
2093 l_identifier_type := 'JL_CO_GL_BANK';
2094
2095 ELSIF (( l_customer_num IS NULL ) AND
2096 ( nvl(ar_trx.event_class_code,'$') <> 'MISC_RECEIPT' )) THEN
2097 FND_FILE.PUT_LINE(FND_FILE.log,'Event Class Code IS NOT Misc Receipts');
2098 BEGIN
2099 FND_FILE.PUT_LINE(FND_FILE.log,'Query(+)');
2100 SELECT jlcgn.nit_id,
2101 substr(jgzz_fiscal_code,1,decode(instr(jgzz_fiscal_code,'-'),0,14,instr(jgzz_fiscal_code,'-')-1)), --bug8685975
2102 rac.party_name,
2103 SUBSTR(custacct.global_attribute10,1,30),
2104 custacct.global_attribute12,
2105 rac.party_number
2106 INTO g_nit_rec.nit_id,
2107 g_nit_rec.nit,
2108 g_nit_rec.nit_name,
2109 g_nit_rec.nit_type,
2110 g_nit_rec.verifying_digit,
2111 l_customer_num
2112 FROM jl_co_gl_nits jlcgn,
2113 hz_parties rac,
2114 hz_cust_accounts custacct
2115 WHERE custacct.cust_account_id = ar_trx.party_id
2116 AND substr(jgzz_fiscal_code,1,decode(instr(jgzz_fiscal_code,'-'),0,14,instr(jgzz_fiscal_code,'-')-1)) = jlcgn.nit(+) --bug8685975
2117 AND custacct.party_id=rac.party_id;
2118 FND_FILE.PUT_LINE(FND_FILE.log,'Query(-)');
2119 FND_FILE.PUT_LINE(FND_FILE.log,'Non Misc : g_nit_rec : '||g_nit_rec.nit||'-'||g_nit_rec.nit_name);
2120 EXCEPTION
2121 WHEN no_data_found THEN
2122 FND_FILE.PUT_LINE(FND_FILE.log,'Exception no data found in NOT Misc receipts');
2123 SELECT nit_id,
2124 nit,
2125 name,
2126 type,
2127 verifying_digit
2128 INTO g_nit_rec.nit_id,
2129 g_nit_rec.nit,
2130 g_nit_rec.nit_name,
2131 g_nit_rec.nit_type,
2132 g_nit_rec.verifying_digit
2133 FROM jl_co_gl_nits
2134 WHERE nit_id = g_default_nit_id;
2135 FND_FILE.PUT_LINE(FND_FILE.log,'Exception Non Misc : g_nit_rec : '||g_nit_rec.nit||'-'||g_nit_rec.nit_name);
2136
2137 END;
2138
2139 g_gl_je_rec.identifier := l_customer_num;
2140 END IF;
2141
2142 FND_FILE.PUT_LINE(FND_FILE.log,'Call To Validate_NIT in Generate_AR :'||g_gl_je_rec.identifier);
2143
2144 IF Validate_NIT(g_nit_rec,l_identifier_type) THEN
2145 FND_FILE.PUT_LINE(FND_FILE.log,'After Validate :'||g_gl_je_rec.identifier);
2146
2147 INSERT INTO jl_co_gl_trx (transaction_id,
2148 process_id,
2149 set_of_books_id,
2150 code_combination_id,
2151 account_code,
2152 nit_id,
2153 period_name,
2154 period_year,
2155 period_num,
2156 je_batch_id,
2157 je_header_id,
2158 category,
2159 subledger_doc_number,
2160 je_line_num,
2161 document_number,
2162 accounting_date,
2163 currency_code,
2164 creation_date,
2165 created_by,
2166 last_update_date,
2167 last_updated_by,
2168 last_update_login,
2169 accounted_dr,
2170 accounted_cr,
2171 entered_dr,
2172 entered_cr)
2173 VALUES (jl_co_gl_trx_s.nextval,
2174 g_parameter_rec.cid,
2175 g_parameter_rec.set_of_books_id,
2176 p_journal_rec.code_combination_id,
2177 p_journal_rec.account_code,
2178 g_nit_rec.nit_id,
2179 p_journal_rec.period_name,
2180 g_period_year,
2181 g_period_num,
2182 p_journal_rec.je_batch_id,
2183 p_journal_rec.je_header_id,
2184 p_journal_rec.category,
2185 p_journal_rec.subl_doc_num,
2186 p_journal_rec.je_line_num,
2187 -- DECODE(ar_trx.reference_8,'ADJ',
2188 -- ar_trx.reference_5,ar_trx.reference_4),
2189 ar_trx.transaction_number,
2190 p_journal_rec.accounting_date,
2191 p_journal_rec.currency,
2192 sysdate,
2193 NVL(g_parameter_rec.user_id,-1),
2194 sysdate,
2195 NVL(g_parameter_rec.user_id,-1),
2196 g_login_id,
2197 l_acc_dr,
2198 l_acc_cr,
2199 l_ent_dr,
2200 l_ent_cr );
2201
2202 BEGIN
2203 FND_FILE.PUT_LINE(FND_FILE.log,'Stage 1');
2204 SELECT 'TRUE'
2205 INTO g_error_exists
2206 FROM DUAL
2207 WHERE EXISTS (SELECT '1'
2208 FROM jl_co_gl_conc_errs jlcgce
2209 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
2210 AND jlcgce.je_line_num = p_journal_rec.je_line_num);
2211
2212 EXCEPTION
2213 WHEN no_data_found THEN
2214 FND_FILE.PUT_LINE(FND_FILE.log,'Exception in Stage 1');
2215 NULL;
2216 END;
2217
2218 IF NVL(g_error_exists,'FALSE') = 'TRUE' THEN
2219 FND_FILE.PUT_LINE(FND_FILE.log,'Stage 2.1');
2220 UPDATE gl_je_lines gljl
2221 SET co_processed_flag = 'N'
2222 WHERE gljl.je_header_id = p_journal_rec.je_header_id
2223 AND gljl.je_line_num = p_journal_rec.je_line_num
2224 AND EXISTS (SELECT 'Y'
2225 FROM jl_co_gl_trx jlcgt
2226 WHERE jlcgt.je_header_id = gljl.je_header_id
2227 AND jlcgt.je_line_num = gljl.je_line_num);
2228
2229 DELETE FROM jl_co_gl_trx jlcgt
2230 WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
2231 AND jlcgt.je_line_num = p_journal_rec.je_line_num;
2232 ELSE
2233 FND_FILE.PUT_LINE(FND_FILE.log,'Stage2.2');
2234 UPDATE gl_je_lines gljl
2235 SET co_processed_flag = 'Y'
2236 WHERE gljl.je_header_id = p_journal_rec.je_header_id
2237 AND gljl.je_line_num = p_journal_rec.je_line_num
2238 AND EXISTS (SELECT 'Y'
2239 FROM jl_co_gl_trx jlcgt
2240 WHERE jlcgt.je_header_id = gljl.je_header_id
2241 AND jlcgt.je_line_num = gljl.je_line_num);
2242
2243 END IF;
2244
2245 END IF;
2246
2247 END LOOP;
2248 FND_FILE.PUT_LINE(FND_FILE.log,'After MAIN LOOP');
2249 -- since these are summary JE lines that are being processed,
2250 -- if any detail trx associated with the JE line fails then
2251 -- none of the trx should be processed
2252
2253 BEGIN
2254 FND_FILE.PUT_LINE(FND_FILE.log,'Stage 3');
2255 SELECT 'TRUE'
2256 INTO g_error_exists
2257 FROM DUAL
2258 WHERE EXISTS (SELECT '1'
2259 FROM jl_co_gl_conc_errs jlcgce
2260 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
2261 AND jlcgce.je_line_num = p_journal_rec.je_line_num);
2262
2263 EXCEPTION
2264 WHEN no_data_found THEN
2265 FND_FILE.PUT_LINE(FND_FILE.log,'Exception in Stage 3');
2266 NULL;
2267 END;
2268
2269 IF NVL(g_error_exists,'FALSE') = 'TRUE' THEN
2270 FND_FILE.PUT_LINE(FND_FILE.log,'Stage 4');
2271 UPDATE gl_je_lines gljl
2272 SET co_processed_flag = 'N'
2273 WHERE gljl.je_header_id = p_journal_rec.je_header_id
2274 AND gljl.je_line_num = p_journal_rec.je_line_num
2275 AND EXISTS (SELECT 'Y'
2276 FROM jl_co_gl_trx jlcgt
2277 WHERE jlcgt.je_header_id = gljl.je_header_id
2278 AND jlcgt.je_line_num = gljl.je_line_num);
2279
2280 DELETE FROM jl_co_gl_trx jlcgt
2281 WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
2282 AND jlcgt.je_line_num = p_journal_rec.je_line_num;
2283 END IF;
2284
2285 COMMIT;
2286 --bug8499774
2287 IF l_err_flag = 0 THEN
2288 Generate_GL_trx(g_journal_rec);
2289 END IF;
2290
2291 EXCEPTION
2292 WHEN no_data_found THEN
2293 FND_FILE.PUT_LINE(FND_FILE.log,'EXCEPTION no data found in Generate AR Transactions');
2294 -- import references don't exist or are summarized for journal
2295 Generate_GL_trx(g_journal_rec); -- Create with NIT 0
2296
2297 WHEN others THEN
2298 FND_FILE.PUT_LINE(FND_FILE.log,'EXCEPTION others in Generate AR Transactions');
2299 g_error_code := SQLCODE;
2300 g_error_text := SUBSTR(SQLERRM,1,240);
2301 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_AR_trx:'|| g_error_text);
2302 RAISE;
2303 END Generate_AR_trx;
2304
2305
2306 PROCEDURE Generate_Non_Oracle_trx(p_journal_rec IN journals%ROWTYPE) IS
2307
2308 BEGIN
2309 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_Non_Oracle_trx: start - Before select into g_nit_rec ');
2310 FND_FILE.PUT_LINE(FND_FILE.log,'p_journal_rec.extgl_nit_id '|| p_journal_rec.extgl_nit_id);
2311 FND_FILE.PUT_LINE(FND_FILE.log,'p_journal_rec.ext_nit '|| p_journal_rec.ext_nit);
2312 FND_FILE.PUT_LINE(FND_FILE.log,'p_journal_rec.ext_nit_name '|| p_journal_rec.ext_nit_name);
2313 FND_FILE.PUT_LINE(FND_FILE.log,'p_journal_rec.ext_nit_type '|| p_journal_rec.ext_nit_type);
2314 FND_FILE.PUT_LINE(FND_FILE.log,'p_journal_rec.ext_nit_v_digit '|| p_journal_rec.ext_nit_v_digit);
2315
2316 SELECT p_journal_rec.extgl_nit_id,
2317 p_journal_rec.ext_nit,
2318 p_journal_rec.ext_nit_name,
2319 p_journal_rec.ext_nit_type,
2320 p_journal_rec.ext_nit_v_digit
2321 INTO g_nit_rec
2322 FROM DUAL;
2323
2324
2325
2326 FND_FILE.PUT_LINE(FND_FILE.log,'Values after select into g_nit_rec ');
2327 FND_FILE.PUT_LINE(FND_FILE.log,'g_nit_rec.nit_id '|| g_nit_rec.nit_id);
2328 FND_FILE.PUT_LINE(FND_FILE.log,'g_nit_rec.nit '|| g_nit_rec.nit);
2329 FND_FILE.PUT_LINE(FND_FILE.log,'g_nit_rec.nit_name '|| g_nit_rec.nit_name);
2330 FND_FILE.PUT_LINE(FND_FILE.log,'g_nit_rec.nit_type '|| g_nit_rec.nit_type);
2331 FND_FILE.PUT_LINE(FND_FILE.log,'g_nit_rec.verifying_digit '|| g_nit_rec.verifying_digit);
2332
2333 BEGIN
2334 SELECT nit_id
2335 INTO g_nit_rec.nit_id
2336 FROM jl_co_gl_nits jlcgn
2337 WHERE nit = p_journal_rec.ext_nit;
2338
2339 FND_FILE.PUT_LINE(FND_FILE.log,'After select into g_nit_rec.nit_id from jl_co_gl_nits.nit_id');
2340 FND_FILE.PUT_LINE(FND_FILE.log,'g_nit_rec.nit_id '|| g_nit_rec.nit_id);
2341
2342
2343
2344 EXCEPTION
2345 WHEN no_data_found THEN
2346 NULL;
2347 END;
2348
2349 FND_FILE.PUT_LINE(FND_FILE.log,'Before select into g_gl_je_rec from p_journal_rec');
2350 FND_FILE.PUT_LINE(FND_FILE.log,'p_journal_rec.je_header_id '|| p_journal_rec.je_header_id);
2351 FND_FILE.PUT_LINE(FND_FILE.log,'p_journal_rec.je_line_num '|| p_journal_rec.je_line_num);
2352 FND_FILE.PUT_LINE(FND_FILE.log,'p_journal_rec.ext_nit '|| p_journal_rec.ext_nit);
2353
2354
2355 SELECT p_journal_rec.je_header_id,
2356 p_journal_rec.je_line_num,
2357 p_journal_rec.ext_nit
2358 INTO g_gl_je_rec FROM DUAL;
2359
2360 FND_FILE.PUT_LINE(FND_FILE.log,'After select into g_gl_je_rec from p_journal_rec');
2361 FND_FILE.PUT_LINE(FND_FILE.log,'g_gl_je_rec.je_header_id '|| g_gl_je_rec.je_header_id);
2362 FND_FILE.PUT_LINE(FND_FILE.log,'g_gl_je_rec.je_line_num '|| g_gl_je_rec.je_line_num);
2363 FND_FILE.PUT_LINE(FND_FILE.log,'g_gl_je_rec.identifier '|| g_gl_je_rec.identifier);
2364
2365
2366 IF Validate_NIT(g_nit_rec,'JL_CO_GL_NIT') THEN
2367 NULL;
2368 ELSE
2369 -- if NIT is not valid we are creating transactions with
2370 -- NIT 0 for Non Oracle source since the users are unable to
2371 -- correct the nit information via the JE form. But we call this
2372 -- function so that users can see the kind of errors associated
2373 -- with the interfaced records */
2374 g_nit_rec.nit_id := g_default_nit_id;
2375 END IF;
2376
2377 FND_FILE.PUT_LINE(FND_FILE.log,'Before insert into jl_co_gl_trx');
2378
2379
2380 FND_FILE.PUT_LINE(FND_FILE.log,'g_parameter_rec.cid '|| g_parameter_rec.cid);
2381 FND_FILE.PUT_LINE(FND_FILE.log,'p_journal_rec.je_batch_id '|| p_journal_rec.je_batch_id);
2382
2383 FND_FILE.PUT_LINE(FND_FILE.log,'p_journal_rec.je_header_id '|| p_journal_rec.je_header_id);
2384 FND_FILE.PUT_LINE(FND_FILE.log,'p_journal_rec.subl_doc_num '|| p_journal_rec.subl_doc_num);
2385 FND_FILE.PUT_LINE(FND_FILE.log,'p_journal_rec.ext_doc_num '|| p_journal_rec.ext_doc_num);
2386 FND_FILE.PUT_LINE(FND_FILE.log,'p_journal_rec.accounting_date '|| p_journal_rec.accounting_date);
2387 FND_FILE.PUT_LINE(FND_FILE.log,'p_journal_rec.subl_doc_num'|| p_journal_rec.category);
2388
2389 INSERT INTO jl_co_gl_trx (transaction_id,
2390 process_id,
2391 set_of_books_id,
2392 code_combination_id,
2393 account_code,
2394 nit_id,
2395 period_name,
2396 period_year,
2397 period_num,
2398 je_batch_id,
2399 je_header_id,
2400 category,
2401 subledger_doc_number,
2402 je_line_num,
2403 document_number,
2404 accounting_date,
2405 currency_code,
2406 creation_date,
2407 created_by,
2408 last_update_date,
2409 last_updated_by,
2410 last_update_login,
2411 accounted_dr,
2412 accounted_cr,
2413 entered_dr,
2414 entered_cr)
2415 VALUES (jl_co_gl_trx_s.nextval,
2416 g_parameter_rec.cid,
2417 g_parameter_rec.set_of_books_id,
2418 p_journal_rec.code_combination_id,
2419 p_journal_rec.account_code,
2420 NVL(g_nit_rec.nit_id,g_default_nit_id),
2421 p_journal_rec.period_name,
2422 g_period_year,
2423 g_period_num,
2424 p_journal_rec.je_batch_id,
2425 p_journal_rec.je_header_id,
2426 p_journal_rec.category,
2427 p_journal_rec.subl_doc_num,
2428 p_journal_rec.je_line_num,
2429 p_journal_rec.ext_doc_num,
2430 p_journal_rec.accounting_date,
2431 p_journal_rec.currency,
2432 sysdate,
2433 NVL(g_parameter_rec.user_id,-1),
2434 sysdate,
2435 NVL(g_parameter_rec.user_id,-1),
2436 g_login_id,
2437 DECODE(sign(p_journal_rec.accounted_cr),-1,
2438 (abs(p_journal_rec.accounted_cr) +
2439 NVL(DECODE(sign(p_journal_rec.accounted_dr),1,
2440 p_journal_rec.accounted_dr,NULL),0)),
2441 DECODE(sign(p_journal_rec.accounted_dr),-1,
2442 NULL,p_journal_rec.accounted_dr)),
2443 DECODE(sign(p_journal_rec.accounted_dr),-1,
2444 (abs(p_journal_rec.accounted_dr) +
2445 NVL(DECODE(sign(p_journal_rec.accounted_cr),1,
2446 p_journal_rec.accounted_cr,NULL),0)),
2447 DECODE(sign(p_journal_rec.accounted_cr),-1,
2448 NULL,p_journal_rec.accounted_cr)),
2449 DECODE(sign(p_journal_rec.entered_cr),-1,
2450 (abs(p_journal_rec.entered_cr) +
2451 NVL(DECODE(sign(p_journal_rec.entered_dr),1,
2452 p_journal_rec.entered_dr,NULL),0)),
2453 DECODE(sign(p_journal_rec.entered_dr),-1,
2454 NULL,p_journal_rec.entered_dr)),
2455 DECODE(sign(p_journal_rec.entered_dr),-1,
2456 (abs(p_journal_rec.entered_dr) +
2457 NVL(DECODE(sign(p_journal_rec.entered_cr),1,
2458 p_journal_rec.entered_cr,NULL),0)),
2459 DECODE(sign(p_journal_rec.entered_cr),-1,
2460 NULL,p_journal_rec.entered_cr)) );
2461
2462 /* bug 7045429
2463
2464 BEGIN
2465
2466 SELECT 'TRUE'
2467 INTO g_error_exists
2468 FROM DUAL
2469 WHERE EXISTS (SELECT '1'
2470 FROM jl_co_gl_conc_errs jlcgce
2471 WHERE jlcgce.je_header_id = p_journal_rec.je_header_id
2472 AND jlcgce.je_line_num = p_journal_rec.je_line_num);
2473
2474 EXCEPTION
2475 WHEN no_data_found THEN
2476 NULL;
2477 END;
2478
2479 IF NVL(g_error_exists,'FALSE') = 'TRUE' THEN
2480 DELETE FROM jl_co_gl_trx jlcgt
2481 WHERE jlcgt.je_header_id = p_journal_rec.je_header_id
2482 AND jlcgt.je_line_num = p_journal_rec.je_line_num;
2483 ELSE */ -- Bug 8215616
2484 UPDATE gl_je_lines gljl
2485 SET co_processed_flag = 'Y'
2486 WHERE gljl.je_header_id = p_journal_rec.je_header_id
2487 AND gljl.je_line_num = p_journal_rec.je_line_num
2488 AND EXISTS (SELECT 'Y'
2489 FROM jl_co_gl_trx jlcgt
2490 WHERE jlcgt.je_header_id = gljl.je_header_id
2491 AND jlcgt.je_line_num = gljl.je_line_num); /*
2492
2493 END IF;
2494 */
2495
2496 COMMIT;
2497
2498 EXCEPTION
2499 WHEN others THEN
2500 BEGIN
2501 g_error_code := SQLCODE;
2502 g_error_text := SUBSTR(SQLERRM,1,240);
2503 FND_FILE.PUT_LINE(FND_FILE.log,'Generate_Non_Oracle_trx:'
2504 || g_error_text);
2505 RAISE;
2506 END;
2507
2508 END Generate_Non_Oracle_trx;
2509
2510
2511
2512 PROCEDURE create_balances(
2513 p_period IN VARCHAR2,
2514 p_period_year IN NUMBER,
2515 p_period_num IN NUMBER,
2516 p_sobid IN NUMBER
2517 ) IS
2518 l_period_year number(15);
2519 l_pre_period_year number(15); --bug 10207969
2520 l_pre_period_num number(15);
2521 l_pre_period varchar2(15);
2522 l_bal_count number;
2523
2524 BEGIN
2525
2526
2527 SELECT count(*)
2528 INTO l_bal_count
2529 FROM jl_co_gl_balances bal
2530 WHERE bal.period_name = p_period
2531 AND bal.period_year = p_period_year
2532 AND bal.set_of_books_id = p_sobid
2533 AND rownum = 1;
2534
2535 IF l_bal_count = 0 THEN
2536 BEGIN
2537 SELECT max((bal.period_year * 100 + bal.period_num))
2538 , max(bal.period_year) --bug 10207969
2539 INTO l_pre_period_num
2540 , l_pre_period_year --bug 10207969
2541 FROM jl_co_gl_balances bal
2542 WHERE (bal.period_year * 100 + bal.period_num) < p_period_year * 100 + p_period_num
2543 AND bal.set_of_books_id = p_sobid;
2544
2545 INSERT INTO jl_co_gl_balances (
2546 balance_id,
2547 set_of_books_id,
2548 code_combination_id,
2549 account_code,
2550 nit_id,
2551 period_name,
2552 period_num,
2553 period_year,
2554 currency_code,
2555 begin_balance_cr,
2556 begin_balance_dr,
2557 period_net_cr,
2558 period_net_dr,
2559 creation_date,
2560 created_by,
2561 last_update_date,
2562 last_updated_by,
2563 last_update_login)
2564 (select jl_co_gl_balances_s.nextval,
2565 bal.set_of_books_id,
2566 bal.code_combination_id,
2567 bal.account_code,
2568 bal.nit_id,
2569 p_period,
2570 p_period_num,
2571 p_period_year,
2572 bal.currency_code,
2573 decode(p_period_year, l_pre_period_year,
2574 NVL(bal.begin_balance_cr,0)+NVL(bal.period_net_cr,0),
2575 decode(glcc.account_type, 'R', 0, 'E', 0,
2576 NVL(bal.begin_balance_cr,0)+NVL(bal.period_net_cr,0))), --bug 10207969
2577 decode(p_period_year, l_pre_period_year,
2578 NVL(bal.begin_balance_dr,0)+NVL(bal.period_net_dr,0),
2579 decode(glcc.account_type, 'R', 0, 'E', 0,
2580 NVL(bal.begin_balance_dr,0)+NVL(bal.period_net_dr,0))), --bug 10207969
2581 0,
2582 0,
2583 sysdate,
2584 bal.created_by,
2585 sysdate,
2586 bal.last_updated_by,
2587 bal.last_update_login
2588 FROM jl_co_gl_balances bal
2589 , gl_code_combinations glcc --bug 10207969
2590 WHERE (bal.period_year * 100 + bal.period_num) = l_pre_period_num
2591 AND bal.code_combination_id = glcc.code_combination_id --bug 10207969
2592 AND bal.set_of_books_id = p_sobid);
2593
2594 FND_FILE.PUT_LINE(FND_FILE.log,'p_sobid :'||to_char(p_sobid)||'-'||to_char(l_pre_period_num));
2595
2596
2597 EXCEPTION
2598 WHEN others THEN
2599 BEGIN
2600 g_error_code := SQLCODE;
2601 g_error_text := SUBSTR(SQLERRM,1,240);
2602 FND_FILE.PUT_LINE(FND_FILE.log,'create_balances:'
2603 || g_error_text);
2604 RAISE;
2605 END;
2606 END;
2607 END IF;
2608
2609 END create_balances;
2610
2611
2612 PROCEDURE Calculate_Balance(p_cid IN NUMBER,
2613 p_sobid IN NUMBER,
2614 p_userid IN NUMBER) IS
2615
2616 l_balance_id jl_co_gl_balances.balance_id%TYPE;
2617 l_begin_bal_dr_prior_period jl_co_gl_balances.begin_balance_dr%TYPE;
2618 l_begin_bal_cr_prior_period jl_co_gl_balances.begin_balance_cr%TYPE;
2619 l_period_net_dr_prior_period jl_co_gl_balances.period_net_dr%TYPE;
2620 l_period_net_cr_prior_period jl_co_gl_balances.period_net_cr%TYPE;
2621 l_begin_bal_dr jl_co_gl_balances.begin_balance_dr%TYPE;
2622 l_begin_bal_cr jl_co_gl_balances.begin_balance_cr%TYPE;
2623 l_period_set_name gl_periods.period_set_name%TYPE;
2624 l_max_period_num gl_periods.period_num%TYPE;
2625
2626 -- right now the currency field in jl_co_gl_balances only holds
2627 -- functional_currency_code. But in the future if the functionality
2628 -- is changed to hold balances for multiple currencies then curreny_code
2629 -- should be added to the BALANCE_TRX cursor and in other sql joins also
2630
2631 CURSOR balance_trx IS
2632 SELECT jlcgt.set_of_books_id sobid,
2633 jlcgt.nit_id nitid,
2634 jlcgt.period_name period_name,
2635 jlcgt.code_combination_id ccid,
2636 jlcgt.account_code acccode,
2637 jlcgt.period_year peryear,
2638 jlcgt.period_num pernum,
2639 glcc.account_type acctype,
2640 sum(nvl(jlcgt.accounted_dr,0)) acc_dr, --bug 10207969
2641 sum(nvl(jlcgt.accounted_cr,0)) acc_cr --bug 10207969
2642 FROM gl_code_combinations glcc, jl_co_gl_trx jlcgt
2643 WHERE jlcgt.process_id IN ( SELECT process_id
2644 FROM jl_co_gl_conc_ctrl
2645 WHERE NVL(balance_calculated,'N') <> 'Y'
2646 AND set_of_books_id
2647 = g_parameter_rec.set_of_books_id)
2648 AND jlcgt.code_combination_id = glcc.code_combination_id
2649 GROUP BY jlcgt.set_of_books_id,
2650 jlcgt.nit_id,
2651 jlcgt.period_name,
2652 jlcgt.code_combination_id,
2653 jlcgt.account_code,
2654 jlcgt.period_year,
2655 jlcgt.period_num,
2656 glcc.account_type ;
2657
2658 BEGIN -- Calculate balances
2659
2660
2661 FOR trx IN balance_trx LOOP
2662
2663 l_balance_id := 0;
2664 l_begin_bal_dr_prior_period := 0;
2665 l_begin_bal_cr_prior_period := 0;
2666 l_period_net_dr_prior_period := 0;
2667 l_period_net_cr_prior_period := 0;
2668 l_begin_bal_dr := 0;
2669 l_begin_bal_cr := 0;
2670
2671 SELECT period_set_name,
2672 currency_code
2673 INTO g_period_set_name,g_func_currency
2674 FROM gl_sets_of_books glsob
2675 WHERE glsob.set_of_books_id = trx.sobid;
2676
2677 BEGIN
2678
2679 SELECT balance_id
2680 INTO l_balance_id
2681 FROM jl_co_gl_balances jlcgb
2682 WHERE jlcgb.set_of_books_id = trx.sobid
2683 AND jlcgb.nit_id = trx.nitid
2684 AND jlcgb.code_combination_id = trx.ccid
2685 AND jlcgb.period_name = trx.period_name;
2686
2687 EXCEPTION
2688 WHEN no_data_found THEN
2689 NULL;
2690
2691 END;
2692
2693 IF l_balance_id = 0 THEN
2694 -- No balance record exists - insert new record
2695
2696 -- calculate the begin_bal for new record by adding the
2697 -- begin_bal AND period_net_activity FROM the prior period which
2698 -- could even be more than a year behind cause we dont create
2699 -- balance records IN a specific period unless there is activity
2700 -- IN that period. This decision was made to avoid creating
2701 -- too many records IN jl_co_balances (unlike gl_balances)
2702
2703 FND_FILE.PUT_LINE(FND_FILE.log,
2704 'If l_balance_id is 0 then insert ');
2705 BEGIN
2706
2707 SELECT begin_balance_dr,
2708 begin_balance_cr,
2709 period_net_dr,
2710 period_net_cr
2711 INTO l_begin_bal_dr_prior_period,
2712 l_begin_bal_cr_prior_period,
2713 l_period_net_dr_prior_period,
2714 l_period_net_cr_prior_period
2715 FROM jl_co_gl_balances jlcgb
2716 WHERE jlcgb.nit_id = trx.nitid
2717 AND jlcgb.set_of_books_id = trx.sobid
2718 AND jlcgb.code_combination_id = trx.ccid
2719 AND (jlcgb.period_year * 100 + jlcgb.period_num) =
2720 (SELECT max(jlcgb1.period_year * 100 +
2721 jlcgb1.period_num)
2722 FROM jl_co_gl_balances jlcgb1
2723 WHERE jlcgb1.nit_id = trx.nitid
2724 AND jlcgb1.set_of_books_id = trx.sobid
2725 AND jlcgb1.code_combination_id = trx.ccid
2726 AND (jlcgb1.period_year * 100 + jlcgb1.period_num) <
2727 (trx.peryear * 100 + trx.pernum)
2728 AND jlcgb1.period_year BETWEEN
2729 DECODE(trx.acctype,
2730 'R',trx.peryear, 'E',trx.peryear,
2731 trx.peryear - 200)
2732 AND trx.peryear );
2733
2734 -- if acctype IS O,A or L then prior period could be
2735 -- FROM prior year but if R or E then it would have to be
2736 -- FROM same fiscal year as the period being considered
2737
2738 EXCEPTION
2739 WHEN no_data_found THEN
2740 NULL;
2741 -- if this IS the 1st period for which balance record is
2742 -- being inserted then there wont be a prior period AND
2743 -- the previous sql statements will return 0 rows
2744 -- but you still want the following statements to be executed
2745 END;
2746
2747 l_begin_bal_dr := l_begin_bal_dr_prior_period +
2748 l_period_net_dr_prior_period ;
2749 l_begin_bal_cr := l_begin_bal_cr_prior_period +
2750 l_period_net_cr_prior_period ;
2751
2752 INSERT INTO jl_co_gl_balances (
2753 balance_id,
2754 set_of_books_id,
2755 code_combination_id,
2756 account_code,
2757 nit_id,
2758 period_name,
2759 period_num,
2760 period_year,
2761 currency_code,
2762 begin_balance_dr,
2763 begin_balance_cr,
2764 period_net_dr,
2765 period_net_cr,
2766 creation_date,
2767 created_by,
2768 last_update_date,
2769 last_updated_by,
2770 last_update_login)
2771 VALUES ( jl_co_gl_balances_s.nextval,
2772 trx.sobid,
2773 trx.ccid,
2774 trx.acccode,
2775 trx.nitid,
2776 trx.period_name,
2777 trx.pernum,
2778 trx.peryear,
2779 g_func_currency,
2780 l_begin_bal_dr,
2781 l_begin_bal_cr,
2782 trx.acc_dr,
2783 trx.acc_cr,
2784 sysdate,
2785 NVL(p_userid,-1),
2786 sysdate,
2787 NVL(p_userid,-1),
2788 g_login_id);
2789
2790 ELSE
2791 -- Balance record exists - Update period_net of current record
2792 FND_FILE.PUT_LINE(FND_FILE.log,
2793 'If l_balance_id is non 0 then update :' ||to_char(trx.acc_dr));
2794 UPDATE jl_co_gl_balances jlcgb
2795 SET period_net_dr = (period_net_dr + trx.acc_dr) ,
2796 period_net_cr = (period_net_cr + trx.acc_cr),
2797 last_update_date = sysdate,
2798 last_updated_by = p_userid,
2799 last_update_login = g_login_id
2800 WHERE jlcgb.set_of_books_id = trx.sobid
2801 AND jlcgb.nit_id = trx.nitid
2802 AND jlcgb.code_combination_id = trx.ccid
2803 AND jlcgb.period_name = trx.period_name;
2804
2805 END IF;
2806
2807 -- Update begin balances of all future balance records for
2808 -- each balance_trx.
2809 -- For income statement accounts only the records in the same year
2810 -- as the transaction needs to be updated
2811
2812 UPDATE jl_co_gl_balances jlcgb
2813 SET begin_balance_dr = (begin_balance_dr + trx.acc_dr),
2814 begin_balance_cr = (begin_balance_cr + trx.acc_cr),
2815 last_update_date = sysdate,
2816 last_updated_by = p_userid,
2817 last_update_login = g_login_id
2818 WHERE jlcgb.nit_id = trx.nitid
2819 AND jlcgb.set_of_books_id = trx.sobid
2820 AND jlcgb.code_combination_id = trx.ccid
2821 AND (jlcgb.period_year * 100 + jlcgb.period_num) >
2822 (trx.peryear * 100 + trx.pernum)
2823 AND period_year BETWEEN trx.peryear AND
2824 DECODE(trx.acctype, 'R',trx.peryear,
2825 'E',trx.peryear,
2826 trx.peryear * 100);
2827
2828 END LOOP;
2829
2830 UPDATE jl_co_gl_conc_ctrl
2831 SET status = DECODE(process_id,p_cid,'P',status),
2832 balance_calculated = 'Y',
2833 last_update_date = sysdate,
2834 last_updated_by = p_userid,
2835 last_update_login = g_login_id
2836 WHERE NVL(balance_calculated,'N') <> 'Y'
2837 AND set_of_books_id = g_parameter_rec.set_of_books_id;
2838
2839 COMMIT;
2840
2841 EXCEPTION
2842 WHEN others THEN
2843 g_error_code := SQLCODE;
2844 g_error_text := SUBSTR(SQLERRM,1,240);
2845 FND_FILE.PUT_LINE(FND_FILE.log,'Calculate_Balance:'
2846 || g_error_text);
2847 RAISE;
2848
2849 END Calculate_Balance;
2850
2851
2852 PROCEDURE Reverse_Balance(p_rcid IN NUMBER, p_cid IN NUMBER,p_sobid IN NUMBER,
2853 p_userid IN NUMBER) IS
2854
2855 CURSOR reversal_trx IS
2856 SELECT jlcgt.set_of_books_id sobid,
2857 jlcgt.nit_id nitid,
2858 jlcgt.period_name period_name,
2859 jlcgt.code_combination_id ccid,
2860 jlcgt.account_code acccode,
2861 jlcgt.period_year peryear,
2862 jlcgt.period_num pernum,
2863 glcc.account_type acctype,
2864 sum(nvl(jlcgt.accounted_dr,0)) acc_dr, --bug 10207969
2865 sum(nvl(jlcgt.accounted_cr,0)) acc_cr --bug 10207969
2866 FROM gl_code_combinations glcc,
2867 jl_co_gl_trx jlcgt
2868 WHERE process_id = p_rcid
2869 AND jlcgt.code_combination_id = glcc.code_combination_id
2870 GROUP BY jlcgt.set_of_books_id,
2871 jlcgt.nit_id,
2872 jlcgt.period_name,
2873 jlcgt.code_combination_id,
2874 jlcgt.account_code,
2875 jlcgt.period_year,
2876 jlcgt.period_num,
2877 glcc.account_type;
2878
2879 BEGIN
2880
2881 -- UPDATE balances
2882
2883 FOR trx IN reversal_trx
2884 LOOP
2885
2886 UPDATE jl_co_gl_balances jlcgb
2887 -- period_net should be updated only for the purge period
2888 SET period_net_dr =
2889 (period_net_dr - DECODE(jlcgb.period_name, trx.period_name,
2890 trx.acc_dr,0)),
2891 period_net_cr =
2892 (period_net_cr - DECODE(jlcgb.period_name, trx.period_name,
2893 trx.acc_cr,0)),
2894 -- begin_balance for all future periods in the current year
2895 -- only needs to be corrected for Income Statement accounts,
2896 -- and all future periods for balance sheet accounts
2897 begin_balance_dr =
2898 (begin_balance_dr - DECODE(jlcgb.period_name, trx.period_name,
2899 0,trx.acc_dr)) ,
2900 begin_balance_cr =
2901 (begin_balance_cr - DECODE(jlcgb.period_name, trx.period_name,
2902 0,trx.acc_cr)),
2903 last_update_date = sysdate,
2904 last_updated_by = p_userid ,
2905 last_update_login = g_login_id
2906 WHERE jlcgb.set_of_books_id = trx.sobid
2907 AND jlcgb.nit_id = trx.nitid
2908 AND jlcgb.code_combination_id = trx.ccid
2909 AND jlcgb.period_name IN (SELECT period_name
2910 FROM gl_periods
2911 WHERE period_set_name = g_period_set_name
2912 AND (period_year * 100 + period_num) >=
2913 (trx.peryear * 100 + trx.pernum)
2914 AND period_year BETWEEN trx.peryear AND
2915 DECODE(trx.acctype,
2916 'R',trx.peryear,'E',trx.peryear,
2917 trx.peryear * 100));
2918
2919 -- After the update if the period_net_dr and cr amounts are 0
2920 -- delete the balance record so that one would be able to delete
2921 -- an invalid NIT via the Define Third Party form.
2922 -- The form does not let you delete a NIT if there are records
2923 -- in jl_co_gl_trx and jl_co_gl_balances for that NIT
2924
2925 /* Bug 8339893: Irrespective of balances being ZERO or not, if there are
2926 * NO records in table JL_CO_GL_TRX for same SOB, NIT, CCID and PERIOD NEITHER
2927 * for OTHER PROCESS_ID, then we can safely delete balances for SAME SOB,
2928 * NIT, CCID and PERIOD since this cursor already grouped this info for
2929 * current PROCESS_ID, meaning there are NO other TRXs using such balance */
2930
2931 DELETE FROM jl_co_gl_balances jlcgb
2932 WHERE jlcgb.set_of_books_id = trx.sobid
2933 AND jlcgb.nit_id = trx.nitid
2934 AND jlcgb.code_combination_id = trx.ccid
2935 AND jlcgb.period_name = trx.period_name
2936 -- bug 8339893 AND jlcgb.period_net_dr = 0 AND jlcgb.period_net_cr = 0
2937 AND NOT EXISTS (SELECT 1 FROM jl_co_gl_trx jlcgt
2938 WHERE jlcgt.nit_id = trx.nitid
2939 AND jlcgt.code_combination_id = trx.ccid
2940 AND jlcgt.period_name = trx.period_name
2941 AND jlcgt.set_of_books_id = trx.sobid
2942 AND jlcgt.process_id <> p_rcid); --bug 8339893
2943
2944 END LOOP; -- UPDATE balances
2945
2946 -- delete transactions
2947
2948 UPDATE gl_je_lines gljl
2949 SET co_processed_flag = 'N'
2950 WHERE co_processed_flag = 'Y'
2951 AND status = 'P'
2952 AND EXISTS (SELECT 1
2953 FROM jl_co_gl_trx jlcgt
2954 WHERE jlcgt.process_id = p_rcid
2955 AND jlcgt.je_header_id = gljl.je_header_id
2956 AND jlcgt.je_line_num = gljl.je_line_num );
2957
2958 DELETE FROM jl_co_gl_trx
2959 WHERE process_id = p_rcid ;
2960
2961 UPDATE jl_co_gl_conc_ctrl
2962 SET status = 'P',
2963 reversed_process_id = p_rcid,
2964 last_update_date = sysdate,
2965 last_updated_by = p_userid,
2966 last_update_login = g_login_id
2967 WHERE process_id = p_cid;
2968
2969 UPDATE jl_co_gl_conc_ctrl
2970 SET status = 'R',
2971 last_update_date = sysdate,
2972 last_updated_by = p_userid,
2973 last_update_login = g_login_id
2974 WHERE process_id = p_rcid;
2975
2976 COMMIT; -- Reversal is complete
2977
2978 EXCEPTION
2979 WHEN others THEN
2980 g_error_code := SQLCODE;
2981 g_error_text := SUBSTR(SQLERRM,1,240);
2982 FND_FILE.PUT_LINE(FND_FILE.log,'Reverse_Balance:'|| g_error_text);
2983 RAISE;
2984
2985 END Reverse_Balance;
2986
2987
2988 PROCEDURE Create_Trx_Balance(errbuf OUT NOCOPY VARCHAR2,
2989 retcode OUT NOCOPY NUMBER,
2990 p_proc_type IN VARCHAR2,
2991 p_sobid IN NUMBER,
2992 p_period IN VARCHAR2,
2993 p_rcid IN NUMBER,
2994 p_batchid IN NUMBER) IS
2995
2996 l_request_id NUMBER := 0;
2997 i NUMBER := 1;
2998 l_rows NUMBER := 0;
2999 l_message_text jl_co_gl_conc_errs.message_text%TYPE := NULL;
3000
3001 BEGIN
3002 FND_FILE.PUT_LINE(FND_FILE.log,'Create_Trx_Balance: Start');
3003 FND_FILE.PUT_LINE(FND_FILE.log,'Ledger ID: '||p_sobid);
3004
3005 -- Bug 9078068, to access all sources data from xla_transaction_entities.
3006
3007 xla_security_pkg.set_security_context(602);
3008
3009
3010 DELETE FROM jl_co_gl_conc_errs;
3011
3012 -- Find out which segment IS the natural account segment
3013 SELECT application_column_name,
3014 id_flex_num
3015 INTO g_account_segment,
3016 g_chart_of_accounts_id
3017 FROM fnd_segment_attribute_values fndsav
3018 WHERE fndsav.id_flex_code = 'GL#'
3019 AND fndsav.segment_attribute_type = 'GL_ACCOUNT'
3020 AND fndsav.attribute_value = 'Y'
3021 AND application_id = 101
3022 AND fndsav.id_flex_num = (SELECT chart_of_accounts_id
3023 FROM gl_sets_of_books
3024 WHERE set_of_books_id = p_sobid);
3025
3026 -- Generate Process record
3027
3028
3029 SELECT TO_NUMBER(NVL(fnd_profile.value('LOGIN_ID'),-1))
3030 INTO g_login_id
3031 FROM DUAL;
3032
3033 SELECT jl_co_gl_conc_ctrl_s.nextval,
3034 p_sobid,
3035 TO_NUMBER(NVL(fnd_profile.value('USER_ID') ,-1)),
3036 p_rcid
3037 INTO g_parameter_rec
3038 FROM dual;
3039
3040 INSERT INTO jl_co_gl_conc_ctrl (
3041 process_id,
3042 set_of_books_id,
3043 period_name,
3044 reversed_process_id,
3045 status,
3046 creation_date,
3047 created_by,
3048 last_update_date,
3049 last_updated_by,
3050 last_update_login,
3051 balance_calculated)
3052 VALUES ( jl_co_gl_conc_ctrl_s.currval,
3053 p_sobid,
3054 DECODE(p_rcid,NULL,p_period,NULL),
3055 p_rcid,
3056 'E',
3057 sysdate,
3058 nvl(g_parameter_rec.user_id,-1),
3059 sysdate,
3060 nvl(g_parameter_rec.user_id,-1),
3061 g_login_id,
3062 NULL);
3063
3064 COMMIT;
3065
3066 BEGIN
3067 SELECT nit_id
3068 INTO g_default_nit_id
3069 FROM jl_co_gl_nits
3070 WHERE nit = '0';
3071
3072 EXCEPTION
3073 WHEN no_data_found THEN
3074 BEGIN
3075 INSERT INTO jl_co_gl_nits (
3076 nit_id,
3077 nit,
3078 type,
3079 verifying_digit,
3080 name,
3081 creation_date,
3082 created_by,
3083 last_update_date,
3084 last_updated_by,
3085 last_update_login)
3086 VALUES ( jl_co_gl_nits_s.nextval,
3087 0,
3088 'LEGAL_ENTITY',
3089 '0',
3090 'Default',
3091 sysdate,
3092 1,
3093 sysdate,
3094 1,
3095 null);
3096
3097 SELECT nit_id
3098 INTO g_default_nit_id
3099 FROM jl_co_gl_nits
3100 WHERE nit = '0';
3101 END;
3102 END;
3103
3104 SELECT period_set_name
3105 INTO g_period_set_name
3106 FROM gl_sets_of_books glsob
3107 WHERE glsob.set_of_books_id = p_sobid;
3108
3109 IF p_period is not null THEN
3110 SELECT period_num,
3111 period_year
3112 INTO g_period_num,
3113 g_period_year
3114 FROM gl_periods
3115 WHERE period_set_name = g_period_set_name
3116 AND period_name = p_period;
3117 END IF;
3118
3119 IF p_proc_type = 'GENERATE' THEN
3120
3121 -- Generate Transactions AND calculate balance
3122 FND_FILE.PUT_LINE(FND_FILE.log,'Create_Trx_Balance: Entering FOR LOOP');
3123
3124 FOR j_line IN journals(p_period,p_sobid,p_batchid) LOOP
3125 FND_FILE.PUT_LINE(FND_FILE.log,'p_period :'||p_period||'-'||to_char(p_sobid)||'-'||to_char(p_batchid)||j_line.source||'-'||j_line.je_header_id||'-'||j_line.je_line_num);
3126 FND_FILE.PUT_LINE(FND_FILE.log,'Journal Source :'||j_line.source || 'Category : ' || j_line.category || 'Reference 1 ' || j_line.ref_1);
3127
3128 g_journal_rec := j_line;
3129
3130 IF (j_line.source IN ('Payables') AND
3131 --Commented for bug8499774
3132 /*(j_line.ref_10 IS NOT NULL OR
3133 (j_line.ref_2 IS NULL AND
3134 j_line.ref_3 IS NULL AND
3135 j_line.ref_4 IS NULL AND
3136 j_line.ref_5 IS NULL )) AND*/
3137 j_line.reversed_je_header_id IS NULL) THEN
3138 FND_FILE.PUT_LINE(FND_FILE.log, 'Call to Generate_AP_trx :');
3139 Generate_AP_trx(g_journal_rec);
3140 ELSIF (j_line.source IN ('Cost Management') AND
3141 --(j_line.ref_1 = 'PO') AND
3142 -- Bug 16529685 Start
3143 (j_line.je_from_sla_flag is not null) AND
3144 -- Bug 16529685 End
3145 (j_line.reversed_je_header_id IS NULL)) THEN
3146 Generate_PO_trx(g_journal_rec);
3147 -- Bug 16097573 Start
3148 -- Added to process 11i transactions
3149 ELSIF (j_line.source IN ('Purchasing') AND (j_line.ref_1 = 'PO') AND
3150 (j_line.reversed_je_header_id IS NULL)) THEN
3151 Generate_11i_PO_trx(g_journal_rec);
3152 -- Bug 16097573 End
3153 ELSIF (j_line.source IN ('Receivables') AND
3154 --Commented for bug8499774
3155 /*(j_line.ref_10 IS NOT NULL OR
3156 (j_line.ref_2 IS NULL AND
3157 j_line.ref_3 IS NULL AND
3158 j_line.ref_4 IS NULL AND
3159 j_line.ref_5 IS NULL )) AND*/
3160 j_line.reversed_je_header_id IS NULL) THEN
3161 -- Bug 16030118 Start
3162 Declare
3163 Cursor C_Upg_Batch_ID Is
3164 Select aeh.upg_batch_id upg_batch_id
3165 FROM xla_transaction_entities ent,
3166 xla_ae_headers AEH,
3167 xla_ae_lines AEL,
3168 gl_import_references R
3169 WHERE ent.application_id = 222
3170 AND ent.entity_id = aeh.entity_id
3171 AND aeh.ae_header_id = ael.ae_header_id
3172 AND (aeh.ledger_id = g_parameter_rec.set_of_books_id --bug8680825
3173 OR
3174 aeh.ledger_id IN (SELECT from_ledger_id
3175 FROM gl_consolidation
3176 WHERE to_ledger_id = g_parameter_rec.set_of_books_id))
3177 AND ael.application_id = 222
3178 AND R.gl_sl_link_id = AEL.gl_sl_link_id
3179 AND R.je_header_id = j_line.je_header_id
3180 AND R.je_line_num = j_line.je_line_num;
3181 l_upg_batch_id xla_ae_headers.upg_batch_id%Type;
3182 Begin
3183 For Upg_Batch_ID_Rec in C_Upg_Batch_ID Loop
3184 l_upg_batch_id := Upg_Batch_ID_Rec.upg_batch_id;
3185 End Loop;
3186 If l_upg_batch_id is not null Then
3187 FND_FILE.PUT_LINE(FND_FILE.log, 'gl_sl_link_id is not null, Calling to R111i Generate_AR_trx :');
3188 Generate_11i_AR_trx(g_journal_rec);
3189 Else
3190 FND_FILE.PUT_LINE(FND_FILE.log, 'gl_sl_link_id is null, Calling to R111i Generate_AR_trx :');
3191 Generate_AR_trx(g_journal_rec);
3192 End If;
3193 Exception
3194 When Others Then
3195 FND_FILE.PUT_LINE(FND_FILE.log, 'Exception in Generate_AR_trx calling R12 Generate_AR_trx :');
3196 Generate_AR_trx(g_journal_rec);
3197 End;
3198 -- Bug 16030118 End
3199
3200 ELSIF (j_line.ref_10 IS NULL AND
3201 (j_line.ext_nit_type IS NOT NULL AND
3202 j_line.ext_nit_name IS NOT NULL AND
3203 j_line.ext_nit IS NOT NULL) AND
3204 (j_line.reversed_je_header_id IS NULL)) THEN
3205
3206 Generate_Non_Oracle_trx(g_journal_rec);
3207 ELSIF (j_line.reversed_je_header_id IS NOT NULL) THEN
3208 reverse_rec_tbl(i).code_combination_id :=
3209 g_journal_rec.code_combination_id;
3210 reverse_rec_tbl(i).account_code :=
3211 g_journal_rec.account_code;
3212 reverse_rec_tbl(i).period_name :=
3213 g_journal_rec.period_name;
3214 reverse_rec_tbl(i).je_batch_id :=
3215 g_journal_rec.je_batch_id;
3216 reverse_rec_tbl(i).je_header_id :=
3217 g_journal_rec.je_header_id;
3218 reverse_rec_tbl(i).category :=
3219 g_journal_rec.category;
3220 reverse_rec_tbl(i).subl_doc_num :=
3221 g_journal_rec.subl_doc_num;
3222 reverse_rec_tbl(i).je_line_num :=
3223 g_journal_rec.je_line_num;
3224 reverse_rec_tbl(i).accounting_date :=
3225 g_journal_rec.accounting_date;
3226 reverse_rec_tbl(i).currency :=
3227 g_journal_rec.currency;
3228 reverse_rec_tbl(i).reversed_je_header_id :=
3229 g_journal_rec.reversed_je_header_id;
3230 reverse_rec_tbl(i).je_line_num :=
3231 g_journal_rec.je_line_num;
3232 -- Bug 9441034 Start
3233 reverse_rec_tbl(i).entered_dr := g_journal_rec.entered_dr;
3234 reverse_rec_tbl(i).entered_cr := g_journal_rec.entered_cr;
3235 reverse_rec_tbl(i).accounted_dr := g_journal_rec.accounted_dr;
3236 reverse_rec_tbl(i).accounted_cr := g_journal_rec.accounted_cr;
3237 -- Bug 9441034 End
3238 i := i + 1;
3239 ELSE
3240 Generate_GL_trx(g_journal_rec);
3241 END IF;
3242 g_journal_rec := NULL;
3243
3244 END LOOP; -- journal CURSOR
3245
3246 -- Insert Reversed Journals
3247 l_rows := nvl(reverse_rec_tbl.last,0);
3248 FOR j_line IN 1..l_rows
3249 LOOP
3250 INSERT INTO jl_co_gl_trx(
3251 transaction_id,
3252 process_id,
3253 set_of_books_id,
3254 code_combination_id,
3255 account_code,
3256 nit_id,
3257 period_name,
3258 period_year,
3259 period_num,
3260 je_batch_id,
3261 je_header_id,
3262 category,
3263 subledger_doc_number,
3264 je_line_num,
3265 document_number,
3266 accounting_date,
3267 currency_code,
3268 creation_date,
3269 created_by,
3270 last_update_date,
3271 last_updated_by,
3272 last_update_login,
3273 accounted_dr,
3274 accounted_cr,
3275 entered_dr,
3276 entered_cr)
3277 (SELECT jl_co_gl_trx_s.nextval,
3278 g_parameter_rec.cid,
3279 g_parameter_rec.set_of_books_id,
3280 reverse_rec_tbl(j_line).code_combination_id,
3281 reverse_rec_tbl(j_line).account_code,
3282 jlcgt.nit_id,
3283 reverse_rec_tbl(j_line).period_name,
3284 g_period_year,
3285 g_period_num,
3286 reverse_rec_tbl(j_line).je_batch_id,
3287 reverse_rec_tbl(j_line).je_header_id,
3288 reverse_rec_tbl(j_line).category,
3289 reverse_rec_tbl(j_line).subl_doc_num,
3290 reverse_rec_tbl(j_line).je_line_num,
3291 jlcgt.document_number,
3292 reverse_rec_tbl(j_line).accounting_date,
3293 reverse_rec_tbl(j_line).currency,
3294 sysdate,
3295 NVL(g_parameter_rec.user_id,-1),
3296 sysdate,
3297 NVL(g_parameter_rec.user_id,-1),
3298 g_login_id,
3299 -- Bug 9441034 Start
3300 reverse_rec_tbl(j_line).accounted_dr,
3301 reverse_rec_tbl(j_line).accounted_cr,
3302 reverse_rec_tbl(j_line).entered_dr,
3303 reverse_rec_tbl(j_line).entered_cr
3304 -- Bug 9441034 Start
3305 FROM jl_co_gl_trx jlcgt
3306 WHERE jlcgt.je_header_id
3307 = reverse_rec_tbl(j_line).reversed_je_header_id
3308 AND jlcgt.je_line_num
3309 = reverse_rec_tbl(j_line).je_line_num );
3310
3311 --bug 8845393 Starts: Updating CO_PROCESSED_FLAG for Reversal Lines so that
3312 -- same record is NOT picked again if reversal process
3313 -- is run more than once for same period.
3314
3315 UPDATE gl_je_lines gljl
3316 SET co_processed_flag = 'Y'
3317 WHERE gljl.je_header_id = reverse_rec_tbl(j_line).je_header_id
3318 AND gljl.je_line_num = reverse_rec_tbl(j_line).je_line_num
3319 AND EXISTS (SELECT 'Y'
3320 FROM jl_co_gl_trx jlcgt
3321 WHERE jlcgt.je_header_id = gljl.je_header_id
3322 AND jlcgt.je_line_num = gljl.je_line_num);
3323 --bug 8845393 Ends
3324
3325 END LOOP;
3326
3327 COMMIT; --bug 8845393: Since all other kinds of trxs are
3328 -- committed after they are created, committing
3329 -- also Reversed trxs to be in synch.
3330
3331 -- Now that all the transactions are created - calculate balance
3332 FND_FILE.PUT_LINE(FND_FILE.log,
3333 'Create_Trx_Balance: Calling Calculate Balance');
3334
3335
3336 Create_Balances( p_period,
3337 g_period_year,
3338 g_period_num ,
3339 p_sobid);
3340
3341 Calculate_Balance(g_parameter_rec.cid,
3342 p_sobid,
3343 g_parameter_rec.user_id);
3344
3345 -- Submit the Third Party Balances Error Report
3346
3347 l_request_id := FND_REQUEST.SUBMIT_REQUEST(
3348 'JL',
3349 'JLCOGLNE',
3350 'Third Party Balances Error Report',
3351 '',
3352 FALSE,
3353 p_sobid,
3354 g_parameter_rec.cid);
3355 IF l_request_id = 0 THEN
3356 FND_FILE.PUT_LINE(FND_FILE.log,
3357 'CONC-REQUEST SUBMISSION FAILED');
3358 ELSE
3359 FND_MESSAGE.SET_NAME('SQLGL','GL_REQUEST_SUBMITTED');
3360 FND_MESSAGE.SET_TOKEN('REQUEST_ID',l_request_id,FALSE);
3361 l_message_text := FND_MESSAGE.GET;
3362 FND_FILE.PUT_LINE(FND_FILE.log,
3363 'Submitted Third Party Balances Error Report. '
3364 || l_message_text);
3365 END IF;
3366
3367 ELSIF p_proc_type = 'REVERSE' THEN
3368
3369 -- call Reverse Balances routine with the RCID
3370 FND_FILE.PUT_LINE(FND_FILE.log,'Create_Trx_Balance: Calling Reverse Balance');
3371
3372 Reverse_Balance(p_rcid,
3373 g_parameter_rec.cid,
3374 p_sobid,
3375 g_parameter_rec.user_id);
3376 END IF; -- if p_proc_type
3377
3378 FND_FILE.PUT_LINE(FND_FILE.log,'Create_Trx_Balance: Process completed successfully');
3379
3380 EXCEPTION
3381 WHEN others THEN
3382 g_error_code := SQLCODE;
3383 g_error_text := SUBSTR(SQLERRM,1,240);
3384 FND_FILE.PUT_LINE(FND_FILE.log,'Create_Trx_Balance:'|| g_error_text);
3385 RAISE;
3386
3387 END create_trx_balance;
3388
3389
3390 END jl_co_gl_nit_management;
3391