DBA Data[Home] [Help]

PACKAGE BODY: APPS.JL_CO_GL_NIT_MANAGEMENT

Source


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