[Home] [Help]
PACKAGE BODY: APPS.JL_BR_AR_BANK_ACCT_PKG
Source
1 PACKAGE BODY JL_BR_AR_BANK_ACCT_PKG AS
2 /* $Header: jlbrslab.pls 120.44.12010000.4 2008/08/04 12:50:25 vgadde ship $ */
3
4 /*========================================================================
5 | PRIVATE FUNCTION Create_SLA_Event
6 |
7 | DESCRIPTION
8 | Function to call SLA Create Event API for JLBR AR Bank Transfers
9 | It returns the EVENT_ID returned by SLA Create Event API
10 |
11 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
12 | a) Create_Event_Dists
13 |
14 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
15 |
16 | PARAMETERS
17 |
18 | KNOWN ISSUES
19 |
20 | NOTES
21 |
22 | MODIFICATION HISTORY
23 | Date Author Description of Changes
24 *=======================================================================*/
25
26 -- Define Package Level Debug Variable and Assign the Profile
27 DEBUG_Var varchar2(1) := NVL(FND_PROFILE.value('AFLOG_ENABLED'), 'N');
28 G_CURRENT_RUNTIME_LEVEL NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
29 G_LEVEL_STATEMENT CONSTANT NUMBER := FND_LOG.LEVEL_STATEMENT;
30 G_LEVEL_PROCEDURE CONSTANT NUMBER := FND_LOG.LEVEL_PROCEDURE;
31 G_LEVEL_EVENT CONSTANT NUMBER := FND_LOG.LEVEL_EVENT;
32 G_LEVEL_EXCEPTION CONSTANT NUMBER := FND_LOG.LEVEL_EXCEPTION;
33 G_LEVEL_ERROR CONSTANT NUMBER := FND_LOG.LEVEL_ERROR;
34 G_LEVEL_UNEXPECTED CONSTANT NUMBER := FND_LOG.LEVEL_UNEXPECTED;
35
36 FUNCTION Create_SLA_Event (p_sla_event_type IN VARCHAR2,
37 p_event_date IN DATE,
38 p_bordero_type IN VARCHAR2,
39 p_document_id IN NUMBER,
40 p_occurrence_id IN NUMBER,
41 p_ctrl_name IN VARCHAR2,
42 p_trx_number IN VARCHAR2,
43 p_bank_occurrence_type IN VARCHAR2,
44 p_std_occurrence_code IN VARCHAR2,
45 p_bordero_id IN NUMBER,
46 p_payment_schedule_id IN NUMBER)
47 RETURN NUMBER IS
48
49 l_event_source_info xla_events_pub_pkg.t_event_source_info;
50 l_event_id NUMBER;
51 l_org_id NUMBER;
52 l_security_context xla_events_pub_pkg.t_security;
53 l_reference_info xla_events_pub_pkg.t_event_reference_info;
54 l_category VARCHAR2(30) := NULL;
55 l_occurrence_id NUMBER;
56 l_event_number NUMBER;
57
58
59 BEGIN
60 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
61 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','Start FUNCTION Create_SLA_Event');
62 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','Parameters are :');
63 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event',' p_sla_event_type'||p_sla_event_type);
64 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event',' p_event_date'||p_event_date);
65 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event',' p_bordero_type='||p_bordero_type);
66 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event',' p_document_id='||p_document_id);
67 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event',' p_occurrence_id='||p_occurrence_id);
68 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event',' p_ctrl_name= '||p_ctrl_name);
69 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event',' p_trx_number='||p_trx_number);
70 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event',' p_bank_occurrence_type='||p_bank_occurrence_type);
71 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event',' p_std_occurrence_code='||p_std_occurrence_code);
72 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event',' p_bordero_id='||p_bordero_id);
73 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event',' p_payment_schedule_id='||p_payment_schedule_id);
74 END IF;
75
76 l_event_source_info.application_id := 222;
77
78 SELECT ract.legal_entity_id,
79 cd.org_id
80 INTO l_event_source_info.legal_entity_id ,
81 l_org_id
82 FROM ra_customer_trx_all ract,
83 jl_br_ar_collection_docs cd
84 WHERE ract.customer_trx_id = cd.customer_trx_id
85 AND ract.org_id = cd.org_id
86 AND cd.document_id = p_document_id;
87
88 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
89 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','Getting legal entity information '||l_event_source_info.legal_entity_id);
90 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','Getting org id information '||l_org_id);
91 END IF;
92
93 --
94 SELECT set_of_books_id
95 into l_event_source_info.ledger_id
96 FROM ar_system_parameters_all
97 WHERE org_id = l_org_id;
98
99 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
100 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','Getting set of books information '||l_event_source_info.ledger_id);
101 END IF;
102 --
103 BEGIN
104 SELECT occurrence_id
105 into l_occurrence_id
106 from
107 jl_br_ar_occurrence_docs_all oc,
108 jl_br_ar_bank_occurrences bo
109 WHERE
110 document_id = p_document_id
111 and bo.bank_occurrence_code = oc.bank_occurrence_code
112 and bo.bank_occurrence_type = oc.bank_occurrence_type
113 and bo.bank_party_id = oc.bank_party_id
114 and bo.std_occurrence_code = 'REMITTANCE'
115 and oc.occurrence_status = 'CONFIRMED';
116 EXCEPTION
117 WHEN OTHERS THEN
118 fnd_file.put_line(fnd_file.log,'exception occured'||sqlerrm);
119 END;
120
121 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
122 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','Getting occurence id information '||l_occurrence_id);
123 END IF;
124
125 IF p_std_occurrence_code = 'AUTOMATIC_WRITE_OFF' or p_std_occurrence_code = 'REJECTED_ENTRY' THEN
126
127 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
128 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','Inside condition where p_std_occurence code is AUTOMATIC_WRITE_OFF or REJECTED_ENTRY');
129 END IF;
130 SELECT rtrim(ract.trx_number||'-'||to_char(cd.terms_sequence_number)||':'||
131 to_char(cd.document_id)||':'||bo.description)
132 INTO
133 l_event_source_info.transaction_number
134 FROM jl_br_ar_collection_docs cd,
135 ra_customer_trx_all ract,
136 jl_br_ar_bank_occurrences bo,
137 jl_br_ar_occurrence_docs_all oc
138 WHERE ract.customer_trx_id = cd.customer_trx_id
139 AND ract.org_id = cd.org_id
140 AND cd.document_id = p_document_id
141 AND oc.document_id = cd.document_id
142 AND oc.occurrence_id = l_occurrence_id
143 And bo.bank_occurrence_code = oc.bank_occurrence_code
144 And bo.bank_occurrence_type = oc.bank_occurrence_type
145 And bo.bank_party_id = oc.bank_party_id;
146 l_event_source_info.entity_type_code := 'JL_BR_AR_COLL_DOC_OCCS' ;
147 l_event_source_info.source_id_int_1 := p_document_id;
148 l_event_source_info.source_id_int_2 := l_occurrence_id;
149 l_security_context.security_id_int_1 := l_org_id;
150
151 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
152 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','Getting transaction number information '||l_event_source_info.transaction_number);
153 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','l_event_source_info.entity_type_code = '||l_event_source_info.entity_type_code);
154 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','l_event_source_info.source_id_int_1 = '||l_event_source_info.source_id_int_1);
155 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','l_event_source_info.source_id_int_2 = '||l_event_source_info.source_id_int_2);
156 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','l_security_context.security_id_int_1 = '||l_security_context.security_id_int_1);
157 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','Calling procedure XLA_EVENTS_PUB_PKG.create_event ' );
158 END IF;
159 l_event_id := XLA_EVENTS_PUB_PKG.create_event(
160 p_event_source_info => l_event_source_info ,
161 p_event_type_code => p_sla_event_type ,
162 p_event_date => p_event_date ,
163 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED ,
164 p_event_number => 2 ,
165 p_reference_info => l_reference_info ,
166 p_valuation_method => '' ,
167 p_security_context => l_security_context );
168
169 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
170 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','Return l_event_id from XLA_EVENTS_PUB_PKG.create_event '||l_event_id );
171 END IF;
172 ELSE
173
174 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
175 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','Inside Else condition of p_std_occurence code ');
176 END IF;
177 SELECT rtrim(ract.trx_number||'-'||to_char(cd.terms_sequence_number)||':'||
178 to_char(cd.document_id)||':'||bo.description)
179 INTO
180 l_event_source_info.transaction_number
181 FROM jl_br_ar_collection_docs cd,
182 ra_customer_trx_all ract,
183 jl_br_ar_bank_occurrences bo,
184 jl_br_ar_occurrence_docs_all oc
185 WHERE ract.customer_trx_id = cd.customer_trx_id
186 AND ract.org_id = cd.org_id
187 AND cd.document_id = p_document_id
188 AND oc.document_id = cd.document_id
189 AND oc.occurrence_id = p_occurrence_id
190 And bo.bank_occurrence_code = oc.bank_occurrence_code
191 And bo.bank_occurrence_type = oc.bank_occurrence_type
192 And bo.bank_party_id = oc.bank_party_id;
193 l_event_source_info.entity_type_code := 'JL_BR_AR_COLL_DOC_OCCS' ;
194 l_event_source_info.source_id_int_1 := p_document_id;
195 l_event_source_info.source_id_int_2 := p_occurrence_id;
196 l_security_context.security_id_int_1 := l_org_id;
197 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
198 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','Getting transaction number information '||l_event_source_info.transaction_number);
199 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','l_event_source_info.entity_type_code = '||l_event_source_info.entity_type_code);
200 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','l_event_source_info.source_id_int_1 = '||l_event_source_info.source_id_int_1);
201 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','l_event_source_info.source_id_int_2 = '||l_event_source_info.source_id_int_2);
202 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','l_security_context.security_id_int_1 = '||l_security_context.security_id_int_1);
203 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','Calling procedure XLA_EVENTS_PUB_PKG.create_event ' );
204 END IF;
205
206 l_event_id := XLA_EVENTS_PUB_PKG.create_event(
207 p_event_source_info => l_event_source_info ,
208 p_event_type_code => p_sla_event_type ,
209 p_event_date => p_event_date ,
210 p_event_status_code => XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED ,
211 p_event_number => 1 ,
212 p_reference_info => l_reference_info ,
213 p_valuation_method => '' ,
214 p_security_context => l_security_context );
215 END IF;
216 --
217 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
218 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_SLA_Event','Return l_event_id from XLA_EVENTS_PUB_PKG.create_event '||l_event_id );
219 END IF;
220 return (l_event_id);
221 EXCEPTION
222 WHEN OTHERS THEN
223 fnd_file.put_line(fnd_file.log,'exception occured'||sqlerrm);
224
225 END Create_SLA_Event;
226
227 /*========================================================================
228 | PRIVATE PROCEDURE Create_Distribution
229 |
230 | DESCRIPTION
231 | Procedure to insert distribution in table JL_BR_AR_DISTRIBUTNS
232 |
233 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
234 | a) Create_Event_Dists
235 | b) Cancel_Reject_Distributions
236 |
237 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
238 |
239 | PARAMETERS
240 |
241 | KNOWN ISSUES
242 |
243 | NOTES
244 |
245 | MODIFICATION HISTORY
246 | Date Author Description of Changes
247 *=======================================================================*/
248
249 PROCEDURE Create_Distribution (p_event_id IN NUMBER,
250 p_event_date IN DATE,
251 p_document_id IN NUMBER,
252 p_distr_type IN VARCHAR2,
253 p_gl_date IN DATE,
254 p_entered_amt IN NUMBER,
255 p_occurrence_id IN NUMBER,
256 p_bank_occurrence_type IN VARCHAR2,
257 p_bank_occurrence_code IN VARCHAR2,
258 p_std_occurrence_code IN VARCHAR2,
259 p_bordero_type IN VARCHAR2,
260 p_org_id IN NUMBER,
261 p_entered_currency_code IN VARCHAR2,
262 p_conversion_rate IN NUMBER,
263 p_conversion_date IN DATE,
264 p_conversion_rate_type IN VARCHAR2,
265 p_acct_reversal_option IN VARCHAR2,
266 p_reversed_dist_id IN NUMBER,
267 p_reversed_dist_link_type IN VARCHAR2,
268 p_prior_dist_id IN NUMBER,
269 p_prior_dist_link_type IN VARCHAR2,
270 p_dist_line_number IN NUMBER) IS
271
272 l_count number;
273 BEGIN
274 l_count :=0;
275 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
276 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Distribution','Start of create distribution function');
277 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Distribution','Insertion into jl_br_ar_distributns table');
278 END IF;
279 Select count(*)
280 into l_count
281 from jl_br_ar_distributns
282 where DOCUMENT_ID = p_document_id
283 and DISTRIBUTION_TYPE = p_distr_type
284 and STD_OCCURRENCE_CODE = p_std_occurrence_code
285 and BORDERO_TYPE = p_bordero_type
286 and BANK_OCCURRENCE_TYPE = p_bank_occurrence_type ;
287
288 IF l_count > 0 then
289 fnd_file.put_line(fnd_file.log,'Record already exists');
290 return;
291 ELSE
292 insert into jl_br_ar_distributns
293 (ORG_ID,
294 DISTRIBUTION_ID,
295 DOCUMENT_ID,
296 DISTRIBUTION_TYPE,
297 GL_DATE,
298 ENTERED_AMT,
299 ENTERED_CURRENCY_CODE,
300 ACCTD_AMT,
301 CONVERSION_RATE,
302 CONVERSION_DATE,
303 CONVERSION_RATE_TYPE,
304 ACCOUNTING_REVERSAL_OPTION,
305 REVERSED_DIST_ID,
306 REVERSED_DIST_LINK_TYPE,
307 PRIOR_DIST_ID,
308 PRIOR_DIST_LINK_TYPE,
309 DISTRIBUTION_LINK_TYPE,
310 EVENT_ID,
311 EVENT_DATE,
312 OCCURRENCE_ID,
313 BANK_OCCURRENCE_TYPE,
314 BANK_OCCURRENCE_CODE,
315 STD_OCCURRENCE_CODE,
316 BORDERO_TYPE,
317 DIST_LINE_NUMBER
318 )
319 values (p_org_id, -- ORG_ID
320 jl_br_ar_distributns_s.NEXTVAL, -- DISTRIBUTION_ID
321 p_document_id, -- DOCUMENT_ID
322 p_distr_type, -- DISTRIBUTION_TYPE
323 p_gl_date, -- GL_DATE
324 p_entered_amt, -- ENTERED_AMT
325 p_entered_currency_code, -- ENTERED_CURRENCY_CODE
326 p_entered_amt * NVL(p_conversion_rate,1), -- ACCTD_AMT
327 p_conversion_rate, -- CONVERSION_RATE
328 p_conversion_date, -- CONVERSION_DATE
329 p_conversion_rate_type, -- CONVERSION_RATE_TYPE
330 p_acct_reversal_option, -- ACCOUNTING_REVERSAL_OPTION
331 p_reversed_dist_id, -- REVERSED_DIST_ID
332 p_reversed_dist_link_type, -- REVERSED_DIST_LINK_TYPE
333 p_prior_dist_id, -- PRIOR_DIST_ID
334 p_prior_dist_link_type, -- PRIOR_DIST_LINK_TYPE
335 'JLBR_AR_DIST', -- DISTRIBUTION_LINK_TYPE
336 p_event_id, -- EVENT_ID
337 p_event_date, -- EVENT_DATE
338 p_occurrence_id, -- OCCURRENCE_ID
339 p_bank_occurrence_type, -- BANK_OCCURRENCE_TYPE
340 p_bank_occurrence_code, -- BANK_OCCURRENCE_CODE
341 p_std_occurrence_code, -- STD_OCCURRENCE_CODE
342 p_bordero_type, -- BORDERO_TYPE
343 p_dist_line_number -- DIST_LINE_NUMBER
344 );
345 END IF;
346 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
347 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Distribution','Just after the Insertion into jl_br_ar_distributns table');
348 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Distribution','End of function');
349 END IF;
350
351 END Create_Distribution;
352
353 /*========================================================================
354 | PRIVATE PROCEDURE Cancel_Reject_Distributions
355 |
356 | DESCRIPTION
357 | Procedure to insert distributions for cancel , rejection and write off
358 |
359 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
360 | a) Create_Event_Dists
361 |
362 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
363 | a) Create_Distribution
364 |
365 | PARAMETERS
366 |
367 | KNOWN ISSUES
368 |
369 | NOTES
370 |
371 | MODIFICATION HISTORY
372 | Date Author Description of Changes
373 *=======================================================================*/
374
375 PROCEDURE Cancel_Reject_Distributions (p_event_id IN NUMBER,
376 p_event_date IN DATE,
377 p_gl_date IN DATE,
378 p_document_id IN NUMBER,
379 p_occurrence_id IN NUMBER,
380 p_bank_occurrence_type IN VARCHAR2,
381 p_bank_occurrence_code IN VARCHAR2,
382 p_std_occurrence_code IN VARCHAR2,
383 p_bordero_type IN VARCHAR2,
384 p_distribution_type IN VARCHAR2,
385 p_dist_line_number IN OUT NOCOPY NUMBER) IS
386 cursor c_dist is
387 SELECT ORG_ID,
388 DISTRIBUTION_ID,
389 DOCUMENT_ID,
390 DISTRIBUTION_TYPE,
391 GL_DATE,
392 ENTERED_AMT,
393 ENTERED_CURRENCY_CODE,
394 ACCTD_AMT,
395 CONVERSION_RATE,
396 CONVERSION_DATE,
397 CONVERSION_RATE_TYPE,
398 ACCOUNTING_REVERSAL_OPTION,
399 REVERSED_DIST_ID,
400 REVERSED_DIST_LINK_TYPE,
401 PRIOR_DIST_ID,
402 PRIOR_DIST_LINK_TYPE,
403 DISTRIBUTION_LINK_TYPE,
404 EVENT_ID,
405 EVENT_DATE,
406 OCCURRENCE_ID,
407 BANK_OCCURRENCE_TYPE,
408 BANK_OCCURRENCE_CODE,
409 STD_OCCURRENCE_CODE,
410 BORDERO_TYPE,
411 DIST_LINE_NUMBER
412 FROM jl_br_ar_distributns
413 WHERE document_id = p_document_id
414 AND std_occurrence_code = 'REMITTANCE'
415 AND accounting_reversal_option = 'N'
416 AND distribution_type = NVL(p_distribution_type,distribution_type);
417
418 r_d c_dist%ROWTYPE;
419
420 BEGIN
421 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
422 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Cancel_Reject_Distributions','Start of procedure');
423 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Cancel_Reject_Distributions','Open cursor c_dist');
424 END IF;
425 open c_dist;
426 LOOP
427 fetch c_dist into r_d;
428 EXIT WHEN c_dist%NOTFOUND;
429 p_dist_line_number := p_dist_line_number + 1;
430
431 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
432 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Cancel_Reject_Distributions','Calling create distribution function');
433 END IF;
434 create_distribution (
435 p_event_id => p_event_id,
436 p_event_date => p_event_date,
437 p_document_id => r_d.document_id,
438 p_distr_type => r_d.distribution_type,
439 p_gl_date => p_gl_date,
440 p_entered_amt => r_d.entered_amt * -1,
441 p_occurrence_id => p_occurrence_id,
442 p_bank_occurrence_type => p_bank_occurrence_type,
443 p_bank_occurrence_code => p_bank_occurrence_code,
444 p_std_occurrence_code => p_std_occurrence_code,
445 p_bordero_type => p_bordero_type,
446 p_org_id => r_d.org_id,
447 p_entered_currency_code => r_d.entered_currency_code,
448 p_conversion_rate => r_d.conversion_rate,
449 p_conversion_date => r_d.conversion_date,
450 p_conversion_rate_type => r_d.conversion_rate_type,
451 p_acct_reversal_option => 'Y',
452 p_reversed_dist_id => r_d.distribution_id,
453 p_reversed_dist_link_type => r_d.distribution_link_type,
454 p_prior_dist_id => NULL,
455 p_prior_dist_link_type => NULL,
456 p_dist_line_number => p_dist_line_number
457 );
458 END LOOP;
459 CLOSE c_dist;
460 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
461 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Cancel_Reject_Distributions','End of Cancel_Reject_Distributions procedure');
462 END IF;
463 END Cancel_Reject_Distributions;
464
465 /*========================================================================
466 | PUBLIC PROCEDURE Create_Event_Dists
467 |
468 | DESCRIPTION
469 | Main routine which creates SLA Event and distributions for
470 | JLBR AR Bank Transfer accounting. It returns EVENT_ID value
471 |
472 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
473 |
474 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
475 | a) Create_SLA_Event
476 | b) Create_Distribution
477 | c) Cancel_Reject_Distributions
478 |
479 | PARAMETERS
480 |
481 | KNOWN ISSUES
482 |
483 | NOTES
484 |
485 | MODIFICATION HISTORY
486 | Date Author Description of Changes
487 *=======================================================================*/
488
489 PROCEDURE Create_Event_Dists (p_event_type_code IN VARCHAR2,
490 p_event_date IN DATE,
491 p_document_id IN NUMBER,
492 p_gl_date IN DATE,
493 p_occurrence_id IN NUMBER,
494 p_bank_occurrence_type IN VARCHAR2,
495 p_bank_occurrence_code IN VARCHAR2,
496 p_std_occurrence_code IN VARCHAR2,
497 p_bordero_type IN VARCHAR2,
498 p_endorsement_amt IN NUMBER,
499 p_bank_charges_amt IN NUMBER,
500 p_factoring_charges_amt IN NUMBER,
501 p_event_id OUT NOCOPY NUMBER) IS
502
503 l_dist_exist NUMBER := 0;
504 l_event_id NUMBER;
505
506 l_org_id NUMBER;
507 l_entered_currency_code VARCHAR2 (15);
508 l_conversion_rate NUMBER;
509 l_conversion_date DATE;
510 l_conversion_rate_type VARCHAR2 (30);
511 l_dist_line_number NUMBER;
512 l_name VARCHAR2 (80);
513 l_trx_number VARCHAR2(30);
514 l_bordero_id NUMBER;
515 l_payment_schedule_id NUMBER;
516
517 l_prior_dist_id NUMBER;
518 l_prior_dist_link_type VARCHAR2 (30);
519
520 BEGIN
521
522 /*--------------------------------------*/
523 /* Ignore events that are not accounted */
524 /*--------------------------------------*/
525 IF (G_LEVEL_PROCEDURE >= G_CURRENT_RUNTIME_LEVEL ) THEN
526 FND_LOG.STRING(G_LEVEL_PROCEDURE, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Start of procedure');
527 END IF;
528
529 if (p_event_type_code = 'CONFIRM_COLL_DOC' or p_event_type_code = 'CONFIRM_FACT_DOC' or
530 p_event_type_code = 'APPLY_BANK_CHARGES_COLL_DOC' or p_event_type_code = 'APPLY_BANK_CHARGES_FACT_DOC' or
531 p_event_type_code = 'PAY_COLL_DOC_AFTER_WRITE_OFF') and NVL(p_bank_charges_amt,0) = 0 then
532
533 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
534 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Ignore events that are not accounted');
535 END IF;
536
537 p_event_id := NULL;
538 RETURN;
539 end if;
540
541 --
542 select cd.org_id,
543 ct.invoice_currency_code,
544 ps.exchange_rate,
545 ps.exchange_date,
546 ps.exchange_rate_type,
547 ct.trx_number,
548 sc.name,
549 bo.bordero_id,
550 ps.payment_schedule_id
551 into l_org_id,
552 l_entered_currency_code,
553 l_conversion_rate,
554 l_conversion_date,
555 l_conversion_rate_type,
556 l_trx_number,
557 l_name,
558 l_bordero_id,
559 l_payment_schedule_id
560 from ra_customer_trx_all ct,
561 ar_payment_schedules_all ps,
562 jl_br_ar_select_controls sc,
563 jl_br_ar_borderos bo,
564 jl_br_ar_collection_docs cd
565 where ct.customer_trx_id = cd.customer_trx_id
566 and ct.org_id = cd.org_id
567 and ps.payment_schedule_id = cd.payment_schedule_id
568 and ps.org_id = cd.org_id
569 and sc.selection_control_id = bo.selection_control_id
570 and bo.bordero_id = cd.bordero_id
571 and cd.document_id = p_document_id;
572 --
573 /*--------------------*/
574 /* Create SLA Event */
575 /*--------------------*/
576
577 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
578 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','l_org_id '||l_org_id);
579 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','l_entered_currency_code = '||l_entered_currency_code);
580 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','l_conversion_rate = '||l_conversion_rate);
581 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','l_conversion_date = '||l_conversion_date);
582 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','l_conversion_rate_type = '||l_conversion_rate_type);
583 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','l_trx_number = '||l_conversion_rate_type);
584 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','l_name = '||l_name);
585 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','l_bordero_id = '||l_bordero_id);
586 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','l_payment_schedule_id = '||l_payment_schedule_id);
587 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Calling procedure Create_SLA_Event ' );
588 END IF;
589
590
591 l_event_id := Create_SLA_Event (
592 p_sla_event_type => p_event_type_code,
593 p_event_date => p_event_date,
594 p_bordero_type => p_bordero_type,
595 p_document_id => p_document_id,
596 p_occurrence_id => p_occurrence_id,
597 p_ctrl_name => l_name,
598 p_trx_number => l_trx_number,
599 p_bank_occurrence_type => p_bank_occurrence_type,
600 p_std_occurrence_code => p_std_occurrence_code,
601 p_bordero_id => l_bordero_id,
602 p_payment_schedule_id => l_payment_schedule_id
603 );
604
605 /*-----------------------*/
606 /* Create Distributions */
607 /*-----------------------*/
608 --
609 select NVL(max(dist_line_number),0)
610 into l_dist_line_number
611 from jl_br_ar_distributns
612 where document_id = p_document_id;
613 --
614 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
615 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','l_dist_line_number = '||l_dist_line_number);
616 END IF;
617
618 if p_event_type_code = 'REMIT_COLL_DOC' or p_event_type_code = 'REMIT_FACT_DOC' then
619 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
620 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Inside if condition where p_event_type_code = REMIT_COLL_DOC or REMIT_FACT_DOC');
621 END IF; --- ENDORSEMENT
622
623 l_dist_line_number := l_dist_line_number + 1;
624
625 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
626 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Calling create_distribution');
627 END IF;
628 create_distribution (
629 p_event_id => l_event_id,
630 p_event_date => p_event_date,
631 p_document_id => p_document_id,
632 p_distr_type => 'JLBR_AR_ENDORSEMENT',
633 p_gl_date => p_gl_date,
634 p_entered_amt => p_endorsement_amt,
635 p_occurrence_id => p_occurrence_id,
636 p_bank_occurrence_type => p_bank_occurrence_type,
637 p_bank_occurrence_code => p_bank_occurrence_code,
638 p_std_occurrence_code => p_std_occurrence_code,
639 p_bordero_type => p_bordero_type,
640 p_org_id => l_org_id,
641 p_entered_currency_code => l_entered_currency_code,
642 p_conversion_rate => l_conversion_rate,
643 p_conversion_date => l_conversion_date,
644 p_conversion_rate_type => l_conversion_rate_type,
645 p_acct_reversal_option => 'N',
646 p_reversed_dist_id => NULL,
647 p_reversed_dist_link_type => NULL,
648 p_prior_dist_id => NULL,
649 p_prior_dist_link_type => NULL,
650 p_dist_line_number => l_dist_line_number
651 );
652
653 if NVL(p_bank_charges_amt,0) <> 0 then --- BANK CHARGES
654 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
655 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Calling create_distribution is p_bank_charges amount <>0');
656 END IF;
657
658 l_dist_line_number := l_dist_line_number + 1;
659 create_distribution (
660 p_event_id => l_event_id,
661 p_event_date => p_event_date,
662 p_document_id => p_document_id,
663 p_distr_type => 'JLBR_AR_BANK_CHARGES',
664 p_gl_date => p_gl_date,
665 p_entered_amt => p_bank_charges_amt,
666 p_occurrence_id => p_occurrence_id,
667 p_bank_occurrence_type => p_bank_occurrence_type,
668 p_bank_occurrence_code => p_bank_occurrence_code,
669 p_std_occurrence_code => p_std_occurrence_code,
670 p_bordero_type => p_bordero_type,
671 p_org_id => l_org_id,
672 p_entered_currency_code => l_entered_currency_code,
673 p_conversion_rate => l_conversion_rate,
674 p_conversion_date => l_conversion_date,
675 p_conversion_rate_type => l_conversion_rate_type,
676 p_acct_reversal_option => 'N',
677 p_reversed_dist_id => NULL,
678 p_reversed_dist_link_type => NULL,
679 p_prior_dist_id => NULL,
680 p_prior_dist_link_type => NULL,
681 p_dist_line_number => l_dist_line_number
682 );
683 end if; -- p_bank_charges_amt
684
685 if NVL(p_factoring_charges_amt,0) <> 0 then --- FACTORING CHARGES
686 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
687 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Calling create_distribution is FACTORING CHARGES amount <>0');
688 END IF;
689 l_dist_line_number := l_dist_line_number + 1;
690 create_distribution (
691 p_event_id => l_event_id,
692 p_event_date => p_event_date,
693 p_document_id => p_document_id,
694 p_distr_type => 'JLBR_AR_FACTORING_CHARGES',
695 p_gl_date => p_gl_date,
696 p_entered_amt => p_factoring_charges_amt,
697 p_occurrence_id => p_occurrence_id,
698 p_bank_occurrence_type => p_bank_occurrence_type,
699 p_bank_occurrence_code => p_bank_occurrence_code,
700 p_std_occurrence_code => p_std_occurrence_code,
701 p_bordero_type => p_bordero_type,
702 p_org_id => l_org_id,
703 p_entered_currency_code => l_entered_currency_code,
704 p_conversion_rate => l_conversion_rate,
705 p_conversion_date => l_conversion_date,
706 p_conversion_rate_type => l_conversion_rate_type,
707 p_acct_reversal_option => 'N',
708 p_reversed_dist_id => NULL,
709 p_reversed_dist_link_type => NULL,
710 p_prior_dist_id => NULL,
711 p_prior_dist_link_type => NULL,
712 p_dist_line_number => l_dist_line_number
713 );
714 end if; -- p_factoring_charges_amt
715
716 elsif p_event_type_code = 'CANCEL_COLL_DOC' or p_event_type_code = 'CANCEL_FACT_DOC' then
717 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
718 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Inside else if cond. p_event_type_code = CANCEL_COLL_DOC or p_event_type_code = CANCEL_FACT_DOC ');
719 END IF;
720
721 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
722 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Calling Cancel_Reject_Distributions function');
723 END IF;
724 Cancel_Reject_Distributions (
725 p_event_id => l_event_id,
726 p_event_date => p_event_date,
727 p_gl_date => p_gl_date,
728 p_document_id => p_document_id,
729 p_occurrence_id => p_occurrence_id,
730 p_bank_occurrence_type => p_bank_occurrence_type,
731 p_bank_occurrence_code => p_bank_occurrence_code,
732 p_std_occurrence_code => p_std_occurrence_code,
733 p_bordero_type => p_bordero_type,
734 p_distribution_type => NULL,
735 p_dist_line_number => l_dist_line_number
736 );
737
738 elsif p_event_type_code = 'WRITE_OFF_COLL_DOC' then
739 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
740 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Inside else if cond. p_event_type_code = WRITE_OFF_COLL_DOC ');
741 END IF;
742
743
744 if NVL(p_bank_charges_amt,0) <> 0 then --- BANK CHARGES
745 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
746 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Calling create_distribution function if p_bank_charges_amt <> 0');
747 END IF;
748 l_dist_line_number := l_dist_line_number + 1;
749 create_distribution (
750 p_event_id => l_event_id,
751 p_event_date => p_event_date,
752 p_document_id => p_document_id,
753 p_distr_type => 'JLBR_AR_BANK_CHARGES',
754 p_gl_date => p_gl_date,
755 p_entered_amt => p_bank_charges_amt,
756 p_occurrence_id => p_occurrence_id,
757 p_bank_occurrence_type => p_bank_occurrence_type,
758 p_bank_occurrence_code => p_bank_occurrence_code,
759 p_std_occurrence_code => p_std_occurrence_code,
760 p_bordero_type => p_bordero_type,
761 p_org_id => l_org_id,
762 p_entered_currency_code => l_entered_currency_code,
763 p_conversion_rate => l_conversion_rate,
764 p_conversion_date => l_conversion_date,
765 p_conversion_rate_type => l_conversion_rate_type,
766 p_acct_reversal_option => 'N',
767 p_reversed_dist_id => NULL,
768 p_reversed_dist_link_type => NULL,
769 p_prior_dist_id => NULL,
770 p_prior_dist_link_type => NULL,
771 p_dist_line_number => l_dist_line_number
772 );
773 end if; -- p_bank_charges_amt
774
775 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
776 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Calling Cancel_Reject_Distributions function');
777 END IF;
778 Cancel_Reject_Distributions (
779 p_event_id => l_event_id,
780 p_event_date => p_event_date,
781 p_gl_date => p_gl_date,
782 p_document_id => p_document_id,
783 p_occurrence_id => p_occurrence_id,
784 p_bank_occurrence_type => p_bank_occurrence_type,
785 p_bank_occurrence_code => p_bank_occurrence_code,
786 p_std_occurrence_code => p_std_occurrence_code,
787 p_bordero_type => p_bordero_type,
788 p_distribution_type => 'JLBR_AR_ENDORSEMENT',
789 p_dist_line_number => l_dist_line_number
790 );
791
792 elsif p_event_type_code = 'CONFIRM_COLL_DOC' or p_event_type_code = 'CONFIRM_FACT_DOC' or
793 p_event_type_code = 'APPLY_BANK_CHARGES_COLL_DOC' or p_event_type_code = 'APPLY_BANK_CHARGES_FACT_DOC' or
794 p_event_type_code = 'PAY_COLL_DOC_AFTER_WRITE_OFF' then
795 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
796 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Inside else if cond. p_event_type_code, big condition');
797 END IF;
798
799 if NVL(p_bank_charges_amt,0) <> 0 then --- BANK CHARGES
800 l_dist_line_number := l_dist_line_number + 1;
801 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
802 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Calling create_distribution function if p_bank_charges_amt <> 0');
803 END IF;
804 create_distribution (
805 p_event_id => l_event_id,
806 p_event_date => p_event_date,
807 p_document_id => p_document_id,
808 p_distr_type => 'JLBR_AR_BANK_CHARGES',
809 p_gl_date => p_gl_date,
810 p_entered_amt => p_bank_charges_amt,
811 p_occurrence_id => p_occurrence_id,
812 p_bank_occurrence_type => p_bank_occurrence_type,
813 p_bank_occurrence_code => p_bank_occurrence_code,
814 p_std_occurrence_code => p_std_occurrence_code,
815 p_bordero_type => p_bordero_type,
816 p_org_id => l_org_id,
817 p_entered_currency_code => l_entered_currency_code,
818 p_conversion_rate => l_conversion_rate,
819 p_conversion_date => l_conversion_date,
820 p_conversion_rate_type => l_conversion_rate_type,
821 p_acct_reversal_option => 'N',
822 p_reversed_dist_id => NULL,
823 p_reversed_dist_link_type => NULL,
824 p_prior_dist_id => NULL,
825 p_prior_dist_link_type => NULL,
826 p_dist_line_number => l_dist_line_number
827 );
828 end if; -- p_bank_charges_amt
829
830 elsif p_event_type_code = 'FULLY_SETTLE_COLL_DOC' or p_event_type_code = 'FULLY_SETTLE_FACT_DOC' or
831 p_event_type_code = 'PARTIALLY_SETTLE_FACT_DOC' OR p_event_type_code = 'PARTIALLY_SETTLE_COLL_DOC' then
832 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
833 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Inside else if cond. p_event_type_code = FULLY_SETTLE_COLL_DOC or FULLY_SETTLE_FACT_DOC or PARTIALLY_SETTLE_FACT_DOC or PARTIALLY_SETTLE_COLL_DOC');
834 END IF;
835
836 --- ENDORSEMENT
837 select distribution_id,
838 distribution_link_type
839 into l_prior_dist_id,
840 l_prior_dist_link_type
841 from jl_br_ar_distributns
842 where std_occurrence_code = 'REMITTANCE'
843 and document_id = p_document_id
844 and accounting_reversal_option = 'N'
845 and distribution_type = 'JLBR_AR_ENDORSEMENT';
846 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
847 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','distribution_id '||l_prior_dist_id);
848 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','distribution_link_type = '||l_prior_dist_link_type);
849 END IF;
850
851 l_dist_line_number := l_dist_line_number + 1;
852
853 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
854 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Calling create_distribution function if p_bank_charges_amt <> 0');
855 END IF;
856 create_distribution (
857 p_event_id => l_event_id,
858 p_event_date => p_event_date,
859 p_document_id => p_document_id,
860 p_distr_type => 'JLBR_AR_ENDORSEMENT',
861 p_gl_date => p_gl_date,
862 p_entered_amt => p_endorsement_amt,
863 p_occurrence_id => p_occurrence_id,
864 p_bank_occurrence_type => p_bank_occurrence_type,
865 p_bank_occurrence_code => p_bank_occurrence_code,
866 p_std_occurrence_code => p_std_occurrence_code,
867 p_bordero_type => p_bordero_type,
868 p_org_id => l_org_id,
869 p_entered_currency_code => l_entered_currency_code,
870 p_conversion_rate => l_conversion_rate,
871 p_conversion_date => l_conversion_date,
872 p_conversion_rate_type => l_conversion_rate_type,
873 p_acct_reversal_option => 'N',
874 p_reversed_dist_id => NULL,
875 p_reversed_dist_link_type => NULL,
876 p_prior_dist_id => l_prior_dist_id,
877 p_prior_dist_link_type => l_prior_dist_link_type,
878 p_dist_line_number => l_dist_line_number
879 );
880
881 if NVL(p_bank_charges_amt,0) <> 0 then --- BANK CHARGES
882 l_dist_line_number := l_dist_line_number + 1;
883 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
884 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Calling create_distribution function if p_bank_charges_amt <> 0');
885 END IF;
886
887 create_distribution (
888 p_event_id => l_event_id,
889 p_event_date => p_event_date,
890 p_document_id => p_document_id,
891 p_distr_type => 'JLBR_AR_BANK_CHARGES',
892 p_gl_date => p_gl_date,
893 p_entered_amt => p_bank_charges_amt,
894 p_occurrence_id => p_occurrence_id,
895 p_bank_occurrence_type => p_bank_occurrence_type,
896 p_bank_occurrence_code => p_bank_occurrence_code,
897 p_std_occurrence_code => p_std_occurrence_code,
898 p_bordero_type => p_bordero_type,
899 p_org_id => l_org_id,
900 p_entered_currency_code => l_entered_currency_code,
901 p_conversion_rate => l_conversion_rate,
902 p_conversion_date => l_conversion_date,
903 p_conversion_rate_type => l_conversion_rate_type,
904 p_acct_reversal_option => 'N',
905 p_reversed_dist_id => NULL,
906 p_reversed_dist_link_type => NULL,
907 p_prior_dist_id => NULL,
908 p_prior_dist_link_type => NULL,
909 p_dist_line_number => l_dist_line_number
910 );
911 end if; -- p_bank_charges_amt
912
913 elsif p_event_type_code = 'REJECT_COLL_DOC' or p_event_type_code = 'REJECT_FACT_DOC' then
914 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
915 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Inside else if cond. p_event_type_code = REJECT_COLL_DOC or REJECT_FACT_DOC');
916 END IF;
917 if NVL(p_bank_charges_amt,0) <> 0 then --- BANK CHARGES
918 l_dist_line_number := l_dist_line_number + 1;
919 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
920 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Calling create_distribution function if p_bank_charges_amt <> 0');
921 END IF;
922 create_distribution (
923 p_event_id => l_event_id,
924 p_event_date => p_event_date,
925 p_document_id => p_document_id,
926 p_distr_type => 'JLBR_AR_BANK_CHARGES',
927 p_gl_date => p_gl_date,
928 p_entered_amt => p_bank_charges_amt,
929 p_occurrence_id => p_occurrence_id,
930 p_bank_occurrence_type => p_bank_occurrence_type,
931 p_bank_occurrence_code => p_bank_occurrence_code,
932 p_std_occurrence_code => p_std_occurrence_code,
933 p_bordero_type => p_bordero_type,
934 p_org_id => l_org_id,
935 p_entered_currency_code => l_entered_currency_code,
936 p_conversion_rate => l_conversion_rate,
937 p_conversion_date => l_conversion_date,
938 p_conversion_rate_type => l_conversion_rate_type,
939 p_acct_reversal_option => 'N',
940 p_reversed_dist_id => NULL,
941 p_reversed_dist_link_type => NULL,
942 p_prior_dist_id => NULL,
943 p_prior_dist_link_type => NULL,
944 p_dist_line_number => l_dist_line_number
945 );
946 end if; -- p_bank_charges_amt
947
948 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
949 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','Calling Cancel_Reject_Distributions function');
950 END IF;
951 Cancel_Reject_Distributions (
952 p_event_id => l_event_id,
953 p_event_date => p_event_date,
954 p_gl_date => p_gl_date,
955 p_document_id => p_document_id,
956 p_occurrence_id => p_occurrence_id,
957 p_bank_occurrence_type => p_bank_occurrence_type,
958 p_bank_occurrence_code => p_bank_occurrence_code,
959 p_std_occurrence_code => p_std_occurrence_code,
960 p_bordero_type => p_bordero_type,
961 p_distribution_type => NULL,
962 p_dist_line_number => l_dist_line_number
963 );
964
965 end if;
966
967 p_event_id := l_event_id;
968
969 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
970 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Create_Event_Dists','End procedure Create_Event_Dists');
971 END IF;
972 END Create_Event_Dists;
973
974
975
976 /*========================================================================
977 | PUBLIC PROCEDURE Upgrade_Distributions
978 |
979 | DESCRIPTION
980 | Upgrades Distributions during downtime and on-demand upgrade
981 |
982 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
983 |
984 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
985 |
986 | PARAMETERS
987 |
988 | KNOWN ISSUES
989 |
990 | NOTES
991 |
992 | MODIFICATION HISTORY
993 | Date Author Description of Changes
994 *=======================================================================*/
995
996
997 PROCEDURE UPGRADE_DISTRIBUTIONS(
998 l_start_id IN number,
999 l_end_id IN number) IS
1000 Cursor c_dist is
1001 select tmp.distribution_id,
1002 tmp.document_id,
1003 decode(tmp.accounting_reversal_option, 'N', tmp.lagdist) prior_dist_id,
1004 decode(tmp.accounting_reversal_option, 'Y',
1005 decode(dist.std_occurrence_code, 'REMITTANCE', tmp.cancellagdist,tmp.lagdist)) rev_dist_id
1006 from (
1007 select distribution_id, document_id, ACCOUNTING_REVERSAL_OPTION,
1008 lag(distribution_id,1,to_number(NULL)) over (partition by document_id, distribution_type
1009 order by occurrence_id) lagdist,
1010 lag(distribution_id,1,to_number(NULL)) over (partition by document_id, occurrence_id, distribution_type
1011 order by accounting_reversal_option) cancellagdist
1012 from jl_rev_tmp) tmp,
1013 jl_br_ar_distributns_all dist
1014 where tmp.distribution_id = dist.distribution_id
1015 and (tmp.ACCOUNTING_REVERSAL_OPTION = 'Y'
1016 or dist.std_occurrence_code in ('FULL_SETTLEMENT', 'PARTIAL_SETTLEMENT')
1017 and dist.distribution_type = 'JLBR_AR_ENDORSEMENT');
1018
1019 TYPE prior_rev_dist_rec IS RECORD(
1020 dist_id NUMBER_TBL_TYPE,
1021 document_id NUMBER_TBL_TYPE,
1022 prior_dist_id NUMBER_TBL_TYPE,
1023 rev_dist_id NUMBER_TBL_TYPE
1024 );
1025
1026 pr_dist prior_rev_dist_rec;
1027
1028 l_doc_id NUMBER;
1029 l_prior_dist_id NUMBER;
1030
1031 BEGIN
1032 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1033 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPGRADE_DISTRIBUTIONS','Start procedure UPGRADE_DISTRIBUTIONS');
1034 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPGRADE_DISTRIBUTIONS','Insert into jl_br_ar_distributns_all ');
1035 END IF;
1036
1037 INSERT all
1038 WHEN 1=1 THEN
1039 INTO jl_br_ar_distributns_all
1040 (
1041 ORG_ID ,
1042 DISTRIBUTION_ID,
1043 DOCUMENT_ID ,
1044 DISTRIBUTION_LINK_TYPE,
1045 DISTRIBUTION_TYPE,
1046 GL_DATE,
1047 ENTERED_AMT ,
1048 ENTERED_CURRENCY_CODE,
1049 ACCTD_AMT ,
1050 CONVERSION_DATE ,
1051 CONVERSION_RATE ,
1052 CONVERSION_RATE_TYPE ,
1053 ACCOUNTING_REVERSAL_OPTION ,
1054 REVERSED_DIST_LINK_TYPE ,
1055 PRIOR_DIST_LINK_TYPE ,
1056 EVENT_DATE ,
1057 OCCURRENCE_ID ,
1058 BANK_OCCURRENCE_CODE ,
1059 STD_OCCURRENCE_CODE,
1060 BORDERO_TYPE ,
1061 BANK_OCCURRENCE_TYPE,
1062 DIST_LINE_NUMBER
1063 )
1064 values (
1065 ORG_ID ,
1066 jl_br_ar_distributns_s.NEXTVAL,
1067 DOCUMENT_ID ,
1068 'JLBR_AR_DIST',
1069 DISTRIBUTION_TYPE,
1070 GL_DATE,
1071 ENTERED_AMT ,
1072 ENTERED_CURRENCY_CODE,
1073 ENTERED_AMT ,
1074 CONVERSION_DATE ,
1075 CONVERSION_RATE ,
1076 CONVERSION_RATE_TYPE ,
1077 ACCOUNTING_REVERSAL_OPTION ,
1078 REVERSED_DIST_LINK_TYPE ,
1079 PRIOR_DIST_LINK_TYPE ,
1080 EVENT_DATE ,
1081 OCCURRENCE_ID ,
1082 BANK_OCCURRENCE_CODE ,
1083 STD_OCCURRENCE_CODE,
1084 BORDERO_TYPE ,
1085 BANK_OCCURRENCE_TYPE,
1086 DIST_LINE_NUMBER
1087 )
1088 WHEN STD_OCCURRENCE_CODE IN ('REMITTANCE', 'FULL_SETTLEMENT', 'PARTIAL_SETTLEMENT',
1089 'REJECTED_ENTRY', 'WRITE_OFF_REQUISITION', 'AUTOMATIC_WRITE_OFF')
1090 THEN
1091 into jl_rev_tmp (
1092 distribution_id, document_id, occurrence_id, distribution_type, ACCOUNTING_REVERSAL_OPTION
1093 )
1094 values (
1095 jl_br_ar_distributns_s.NEXTVAL, document_id, occurrence_id, distribution_type, ACCOUNTING_REVERSAL_OPTION
1096 )
1097 select
1098 X.ORG_ID ,
1099 X.DOCUMENT_ID ,
1100 DECODE(MULTIPLIER,1,'JLBR_AR_ENDORSEMENT',2,'JLBR_AR_ENDORSEMENT',3,'JLBR_AR_BANK_CHARGES',
1101 4,'JLBR_AR_BANK_CHARGES',5,'JLBR_AR_FACTORING_CHARGES',6,'JLBR_AR_FACTORING_CHARGES') distribution_type,
1102 nvl(DECODE(MULTIPLIER,2,X.OC_GL_DATE,4,X.OC_GL_DATE,6,X.OC_GL_DATE,
1103 DECODE(X.OCCURRENCE_STATUS, 'CANCELED', X.CSC_GL_DATE, X.OC_GL_DATE)),X.creation_date) gl_date,
1104 DECODE(MULTIPLIER,1,X.ENDORSEMENT_DEBIT_AMOUNT ,
1105 2, ( -1 * X.ENDORSEMENT_DEBIT_AMOUNT),
1106 3, X.BANK_CHARGES_DEBIT_AMOUNT,
1107 4, DECODE(X.STD_OCCURRENCE_CODE,'REJECTED_ENTRY',( -1 * X.REVERSE_CHARGES_DEB_AMOUNT),
1108 ( -1 * X.BANK_CHARGES_DEBIT_AMOUNT)),
1109 5, X.FACTOR_INTEREST_CREDIT_AMOUNT,
1110 6, ( -1 * X.FACTOR_INTEREST_CREDIT_AMOUNT)) entered_amt,
1111 X.ENTERED_CURRENCY_CODE,
1112 PS.EXCHANGE_DATE CONVERSION_DATE,
1113 PS.EXCHANGE_RATE CONVERSION_RATE,
1114 PS.EXCHANGE_RATE_TYPE CONVERSION_RATE_TYPE,
1115 DECODE(MULTIPLIER,1,'N',2,'Y',3,'N',4,'Y',5,'N',6,'Y') ACCOUNTING_REVERSAL_OPTION,
1116 DECODE(MULTIPLIER,1,NULL,2,'JLBR_AR_DIST',3,NULL,4,'JLBR_AR_DIST',
1117 5,NULL,6,'JLBR_AR_DIST') REVERSED_DIST_LINK_TYPE,
1118 DECODE(MULTIPLIER,1,DECODE(X.STD_OCCURRENCE_CODE, 'PARTIAL_SETTLEMENT', 'JLBR_AR_DIST',
1119 'FULL_SETTLEMENT', 'JLBR_AR_DIST', NULL),
1120 NULL) PRIOR_DIST_LINK_TYPE,
1121 DECODE(MULTIPLIER,1,X.CREATION_DATE,3,X.CREATION_DATE,5,X.CREATION_DATE,
1122 DECODE(X.OCCURRENCE_STATUS, 'CANCELED',X.LAST_UPDATE_DATE,X.CREATION_DATE)) EVENT_DATE,
1123 X.OCCURRENCE_ID ,
1124 X.BANK_OCCURRENCE_CODE ,
1125 X.STD_OCCURRENCE_CODE,
1126 X.BORDERO_TYPE ,
1127 X.BANK_OCCURRENCE_TYPE,
1128 ROW_NUMBER() OVER (PARTITION BY X.DOCUMENT_ID, X.OCCURRENCE_ID
1129 ORDER BY MULTIPLIER) DIST_LINE_NUMBER
1130 FROM
1131 (
1132 -- start
1133 select /*+ no_merge leading(oc) index(oc, jl_br_ar_occur_docs_n1) */
1134 oc.gl_date oc_gl_date, csc.gl_date csc_gl_date,
1135 nvl(oc.occurrence_status,'CONFIRMED') OCCURRENCE_STATUS,
1136 oc.endorsement_debit_amount, oc.bank_charges_debit_amount,
1137 oc.reverse_charges_deb_amount, oc.factor_interest_credit_amount,
1138 oc.endorsement_debit_ccid, oc.bank_charges_debit_ccid,
1139 oc.reverse_charges_deb_ccid, oc.factor_interest_credit_ccid,
1140 ract.invoice_currency_code entered_currency_code, oc.last_update_date,
1141 oc.creation_date, oc.occurrence_id, oc.bank_occurrence_code,
1142 bo.std_occurrence_code, b.bordero_type, bo.bank_occurrence_type,
1143 cd.document_id, oc.bank_party_id, oc.bordero_id, ract.customer_trx_id,
1144 csc.select_account_id, oc.flag_post_gl, oc.gl_date, cd.org_id,
1145 cd.payment_schedule_id
1146 from jl_br_ar_collection_docs_all cd,
1147 jl_br_ar_occurrence_docs_all oc,
1148 ra_customer_trx_all ract,
1149 jl_br_ar_bank_occurrences bo,
1150 jl_br_ar_select_accounts_all csc,
1151 jl_br_ar_borderos_all b
1152 where oc.document_id between l_start_id and l_end_id
1153 and nvl(oc.occurrence_status,'CONFIRMED') <> 'CREATED'
1154 and oc.document_id = cd.document_id
1155 and cd.customer_trx_id = ract.customer_trx_id
1156 and bo.bank_occurrence_code = oc.bank_occurrence_code
1157 and bo.bank_occurrence_type = oc.bank_occurrence_type
1158 and bo.bank_number = oc.bank_number
1159 and bo.bank_occurrence_type in ('REMITTANCE_OCCURRENCE', 'RETURN_OCCURRENCE')
1160 and bo.std_occurrence_code in ('REMITTANCE','WRITE_OFF_REQUISITION','CONFIRMED_ENTRY', 'REJECTED_ENTRY',
1161 'FULL_SETTLEMENT', 'PARTIAL_SETTLEMENT', 'PAYMENT_AFTER_WRITE_OFF',
1162 'AUTOMATIC_WRITE_OFF', 'BANK_CHARGES', 'REMITTANCE_CONFIRMATION',
1163 'REMITTANCE_REJECTION', 'OTHER_OCCURRENCES')
1164 and b.bordero_id = cd.bordero_id
1165 and b.bordero_type in ('COLLECTION', 'FACTORING')
1166 and csc.select_account_id = b.select_account_id
1167 and (nvl(oc.flag_post_gl, 'N') = 'N'
1168 or (oc.flag_post_gl = 'Y'
1169 and (exists (
1170 select 'Y'
1171 from xla_upgrade_dates xud
1172 where ract.set_of_books_id = xud.ledger_id
1173 and ((oc.gl_date >= xud.start_date and oc.gl_date < xud.end_date)
1174 or (oc.gl_date IS NULL and oc.creation_date between xud.start_date and xud.end_date)
1175 or (nvl(oc.occurrence_status,'CONFIRMED') <> 'CANCELED'
1176 and oc.gl_date < xud.start_date
1177 and bo.std_occurrence_code = 'REMITTANCE'
1178 and exists (
1179 select 'Y'
1180 from jl_br_ar_occurrence_docs o
1181 where o.document_id = oc.document_id
1182 and (
1183 (o.gl_date is NULL and oc.creation_date between xud.start_date and xud.end_date)
1184 or (o.gl_date >= xud.start_date
1185 and o.gl_date < xud.end_date))))))
1186 or ((nvl(oc.occurrence_status,'CONFIRMED') <> 'CANCELED'
1187 and bo.std_occurrence_code = 'REMITTANCE'
1188 and exists(select 'Y' from jl_br_ar_occurrence_docs_all o2 where o2.document_id = oc.document_id
1189 and nvl(o2.flag_post_gl,'N') = 'N'))))
1190 ))) X,
1191 gl_row_multipliers grm,
1192 ar_payment_schedules_all ps
1193 where grm.multiplier < 7
1194 and ps.payment_schedule_id = x.payment_schedule_id
1195 and not exists (
1196 select /*+ use_nl_with_index(rerun, JL_BR_AR_DISTRIBUTNS_U2) */ null
1197 from jl_br_ar_distributns_all rerun
1198 where rerun.occurrence_id = x.occurrence_id
1199 and rerun.distribution_type = decode (grm.multiplier, 1,
1200 'JLBR_AR_ENDORSEMENT', 2, 'JLBR_AR_ENDORSEMENT', 3,
1201 'JLBR_AR_BANK_CHARGES', 4, 'JLBR_AR_BANK_CHARGES', 5,
1202 'JLBR_AR_FACTORING_CHARGES', 6, 'JLBR_AR_FACTORING_CHARGES')
1203 and rerun.accounting_reversal_option = decode (grm.multiplier,
1204 2, 'Y', 4, 'Y', 6, 'Y', 'N'))
1205 AND
1206 (multiplier = 1
1207 and std_occurrence_code in ('REMITTANCE','FULL_SETTLEMENT', 'PARTIAL_SETTLEMENT')
1208 and endorsement_debit_amount is not null
1209 and endorsement_debit_ccid is not null
1210 or multiplier = 2
1211 and endorsement_debit_amount is not null
1212 and endorsement_debit_ccid is not null
1213 and (occurrence_status = 'CANCELED' and std_occurrence_code = 'REMITTANCE'
1214 or std_occurrence_code in ('WRITE_OFF_REQUISITION','REJECTED_ENTRY', 'AUTOMATIC_WRITE_OFF'))
1215 or multiplier = 3
1216 and bank_charges_debit_amount is not null
1217 and bank_charges_debit_ccid is not null
1218 or multiplier = 4
1219 and (occurrence_status = 'CANCELED' and std_occurrence_code = 'REMITTANCE'
1220 and bank_charges_debit_amount is not null
1221 and bank_charges_debit_ccid is not null
1222 or std_occurrence_code = 'REJECTED_ENTRY'
1223 and reverse_charges_deb_amount is not null
1224 and reverse_charges_deb_ccid is not null)
1225 or multiplier = 5
1226 and bordero_type = 'FACTORING'
1227 and std_occurrence_code = 'REMITTANCE'
1228 and factor_interest_credit_amount is not null
1229 and factor_interest_credit_ccid is not null
1230 or multiplier = 6
1231 and bordero_type = 'FACTORING'
1232 and factor_interest_credit_amount is not null
1233 and factor_interest_credit_ccid is not null
1234 and (std_occurrence_code = 'REMITTANCE' and occurrence_status = 'CANCELED'
1235 or std_occurrence_code = 'REJECTED_ENTRY'))
1236 ;
1237
1238 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1239 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPGRADE_DISTRIBUTIONS',' After Insert into jl_br_ar_distributns_all. Opening cursor c_dist ');
1240 END IF;
1241 open c_dist;
1242 LOOP
1243 FETCH c_dist BULK COLLECT INTO
1244 pr_dist.dist_id,
1245 pr_dist.document_id,
1246 pr_dist.prior_dist_id,
1247 pr_dist.rev_dist_id;
1248
1249 EXIT WHEN c_dist%NOTFOUND;
1250
1251 END LOOP;
1252 CLOSE c_dist;
1253
1254 -- Handle scenario where there can be more than one full settlement or partial settlement
1255 -- for the same collection document
1256 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1257 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPGRADE_DISTRIBUTIONS',' Handle scenario where there can be more than one full settlement or partial settlement for the same collection document');
1258 END IF;
1259 FOR i in nvl(pr_dist.dist_id.FIRST,0)..nvl(pr_dist.dist_id.LAST,-99)
1260 LOOP
1261 if pr_dist.prior_dist_id IS NOT NULL then
1262 if l_doc_id is NOT NULL and pr_dist.document_id(i) = l_doc_id then
1263 pr_dist.prior_dist_id(i) := l_prior_dist_id;
1264 end if;
1265 l_doc_id := pr_dist.document_id(i);
1266 l_prior_dist_id := pr_dist.prior_dist_id(i);
1267 end if;
1268 END LOOP;
1269
1270 FORALL i in 1..nvl(pr_dist.dist_id.LAST,-99)
1271
1272 update /*+ index(d, jl_br_ar_distributns_u1) */ jl_br_ar_distributns_all d
1273 set reversed_dist_id = pr_dist.rev_dist_id(i),
1274 prior_dist_id = pr_dist.prior_dist_id(i)
1275 where distribution_id = pr_dist.dist_id(i);
1276
1277 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1278 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPGRADE_DISTRIBUTIONS','END procedure UPGRADE_DISTRIBUTIONS');
1279 END IF;
1280 END UPGRADE_DISTRIBUTIONS;
1281
1282 /*========================================================================
1283 | PUBLIC PROCEDURE update_distributions
1284 |
1285 | DESCRIPTION
1286 | Updates Prior Distribution Id for the distribution records
1287 |
1288 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1289 |
1290 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1291 |
1292 | PARAMETERS
1293 |
1294 | KNOWN ISSUES
1295 |
1296 | NOTES
1297 |
1298 | MODIFICATION HISTORY
1299 | Date Author Description of Changes
1300 *=======================================================================*/
1301
1302 PROCEDURE UPDATE_DISTRIBUTIONS(
1303 l_start_rowid IN rowid,
1304 l_end_rowid IN rowid) IS
1305 BEGIN
1306
1307 ------------------------------------------------------------------
1308 /* Updating the prior and reversed distribution Ids */
1309 ------------------------------------------------------------------
1310 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1311 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPDATE_DISTRIBUTIONS','Start procedure UPDATE_DISTRIBUTIONS');
1312 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPDATE_DISTRIBUTIONS','Updating table jl_br_ar_distributns_all');
1313 END IF;
1314 UPDATE jl_br_ar_distributns_all d
1315 SET (prior_dist_id, reversed_dist_id) =
1316 ( SELECT decode(d.accounting_reversal_option, 'Y', NULL, d1.distribution_id),
1317 decode(d.accounting_reversal_option, 'Y', d1.distribution_id, NULL)
1318 FROM jl_br_ar_distributns_all d1,
1319 jl_br_ar_occurrence_docs_all occ1
1320 WHERE d1.distribution_type = d.distribution_type
1321 AND d1.std_occurrence_code = 'REMITTANCE'
1322 AND d1.document_id = d.document_id
1323 AND d1.occurrence_id = occ1.occurrence_id
1324 AND occ1.occurrence_status <> 'CANCELED')
1325 WHERE ((d.accounting_reversal_option = 'Y'
1326 AND d.reversed_dist_id IS NULL)
1327 OR
1328 (d.std_occurrence_code in ('FULL_SETTLEMENT','PARTIAL_SETTLEMENT')
1329 AND d.distribution_type = 'JLBR_AR_ENDORSEMENT'
1330 AND d.prior_dist_id IS NULL))
1331 AND EXISTS(
1332 SELECT 1 FROM jl_br_ar_occurrence_docs_all occ
1333 WHERE occ.occurrence_id = d.occurrence_id
1334 AND rowid >= l_start_rowid
1335 AND rowid <= l_end_rowid
1336 );
1337 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1338 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPDATE_DISTRIBUTIONS','END procedure UPDATE_DISTRIBUTIONS');
1339 END IF;
1340 END Update_Distributions;
1341
1342 /*========================================================================
1343 | PUBLIC PROCEDURE Upgrade_Occurrences
1344 |
1345 | DESCRIPTION
1346 | Upgrades Posted or Yet to be Posted Occurrences to SLA tables
1347 | Called from Downtime Upgrade Script
1348 |
1349 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1350 |
1351 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1352 |
1353 | PARAMETERS
1354 |
1355 | KNOWN ISSUES
1356 |
1357 | NOTES
1358 |
1359 | MODIFICATION HISTORY
1360 | Date Author Description of Changes
1361 *=======================================================================*/
1362
1363 PROCEDURE UPGRADE_OCCURRENCES(
1364 l_table_owner IN VARCHAR2,
1365 l_table_name IN VARCHAR2,
1366 l_script_name IN VARCHAR2,
1367 l_worker_id IN VARCHAR2,
1368 l_num_workers IN VARCHAR2,
1369 l_batch_size IN VARCHAR2,
1370 l_batch_id IN NUMBER,
1371 l_action_flag IN VARCHAR2) IS
1372
1373 l_return_status VARCHAR2(30);
1374
1375 BEGIN
1376 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1377 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPGRADE_OCCURRENCES','Start procedure UPGRADE_OCCURRENCES');
1378 END IF;
1379
1380 UPGRADE_OCCURRENCES(
1381 l_table_owner,
1382 l_table_name,
1383 l_script_name,
1384 l_worker_id,
1385 l_num_workers,
1386 l_batch_size,
1387 l_batch_id,
1388 l_action_flag,
1389 l_return_status);
1390
1391 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1392 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPGRADE_OCCURRENCES','END procedure UPGRADE_OCCURRENCES');
1393 END IF;
1394
1395 END Upgrade_Occurrences;
1396
1397 /*========================================================================
1398 | PUBLIC PROCEDURE Upgrade_Occurrences
1399 |
1400 | DESCRIPTION
1401 | Upgrades Posted or Yet to be Posted Occurrences to SLA tables
1402 | Called directly from on-demand upgrade concurrent program
1403 |
1404 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
1405 |
1406 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
1407 |
1408 | PARAMETERS
1409 |
1410 | KNOWN ISSUES
1411 |
1412 | NOTES
1413 |
1414 | MODIFICATION HISTORY
1415 | Date Author Description of Changes
1416 *=======================================================================*/
1417
1418 PROCEDURE UPGRADE_OCCURRENCES(
1419 l_table_owner IN VARCHAR2,
1420 l_table_name IN VARCHAR2,
1421 l_script_name IN VARCHAR2,
1422 l_worker_id IN VARCHAR2,
1423 l_num_workers IN VARCHAR2,
1424 l_batch_size IN VARCHAR2,
1425 l_batch_id IN NUMBER,
1426 l_action_flag IN VARCHAR2,
1427 x_return_status OUT NOCOPY VARCHAR2) IS
1428
1429 l_start_rowid NUMBER;
1430 l_end_rowid NUMBER;
1431 l_any_rows_to_process boolean;
1432 l_rows_processed number := 0;
1433 Cursor c_events is Select row_id, event_id, cancel_event_id from (
1434 Select row_id, event_id, lead(event_id)
1435 over (partition by row_id order by
1436 decode(event_type_code,'CANCEL_COLL_DOC',2,'CANCEL_FACT_DOC',2,1)) cancel_event_id,
1437 row_number()
1438 over (partition by row_id order by
1439 decode(event_type_code,'CANCEL_COLL_DOC',2,'CANCEL_FACT_DOC',2,1)) r
1440 from jl_remit3_gt) where r=1;
1441
1442 BEGIN
1443 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1444 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPGRADE_OCCURRENCES','Start procedure UPGRADE_OCCURRENCES');
1445 END IF;
1446 IF l_action_flag = 'R' THEN
1447 x_return_status := FND_API.G_RET_STS_SUCCESS;
1448 END IF;
1449
1450 /* ------ Initialize the rowid ranges ------ */
1451 ad_parallel_updates_pkg.initialize_id_range(
1452 ad_parallel_updates_pkg.ID_RANGE,
1453 l_table_owner,
1454 l_table_name,
1455 l_script_name,
1456 'document_id',
1457 l_worker_id,
1458 l_num_workers,
1459 l_batch_size, 0);
1460
1461 /* ------ Get rowid ranges ------ */
1462 ad_parallel_updates_pkg.get_id_range(
1463 l_start_rowid,
1464 l_end_rowid,
1465 l_any_rows_to_process,
1466 l_batch_size,
1467 TRUE);
1468
1469 WHILE ( l_any_rows_to_process = TRUE )
1470 LOOP
1471
1472 l_rows_processed := 0;
1473
1474 -------------------------------------------------------------------
1475 -- Create the distributions for on-demand upgrade only
1476 -------------------------------------------------------------------
1477 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1478 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Upgrade_Distributions','Calling procedure Upgrade_Distributions');
1479 END IF;
1480 IF l_script_name <> 'jl120occ.sql' THEN
1481
1482 Upgrade_Distributions(l_start_rowid,
1483 l_end_rowid);
1484
1485 /* Update_Distributions(l_start_rowid,
1486 l_end_rowid);
1487 */
1488 NULL;
1489 END IF;
1490
1491 -------------------------------------------------------------------
1492 -- Create the transaction entities
1493 -------------------------------------------------------------------
1494 /* The following code was modified as per the performance team recommendation to include global temporary tables.
1495 It is currently commented out because xla_upgrade_dates table is not available in xbuild2.
1496 The xla team is planning to release the table for general use post xbuild2. */
1497
1498 NULL;
1499
1500 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1501 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Upgrade_Distributions','Create the transaction entities, Insert into XLA_TRANSACTION_ENTITIES_UPG, jl_remit_gt,jl_cancel_gt');
1502 END IF;
1503
1504 INSERT all
1505 WHEN (1 = 1) THEN
1506 INTO XLA_TRANSACTION_ENTITIES_UPG
1507 (
1508 ENTITY_ID,
1509 APPLICATION_ID,
1510 LEGAL_ENTITY_ID,
1511 ENTITY_CODE,
1512 CREATION_DATE,
1513 CREATED_BY,
1514 LAST_UPDATE_DATE,
1515 LAST_UPDATED_BY,
1516 LAST_UPDATE_LOGIN,
1517 SOURCE_ID_INT_1,
1518 SECURITY_ID_INT_1,
1519 SOURCE_ID_INT_2,
1520 TRANSACTION_NUMBER,
1521 LEDGER_ID,
1522 SOURCE_APPLICATION_ID,
1523 UPG_BATCH_ID,
1524 UPG_SOURCE_APPLICATION_ID
1525 )
1526 values (
1527 xla_transaction_entities_s.nextval,
1528 222,
1529 legal_entity_id,
1530 'JL_BR_AR_COLL_DOC_OCCS',
1531 sysdate,
1532 2,
1533 sysdate,
1534 2,
1535 -2005,
1536 document_id,
1537 org_id,
1538 occurrence_id,
1539 transaction_number,
1540 set_of_books_id,
1541 '222',
1542 l_batch_id,
1543 222)
1544 WHEN (1 = 1) THEN
1545 INTO jl_remit_gt (
1546 entity_id,
1547 set_of_books_id,
1548 accounting_method,
1549 creation_date,
1550 gl_posted_date,
1551 last_update_date,
1552 flag_post_gl,
1553 std_occurrence_code,
1554 occurrence_status,
1555 oc_gl_date,
1556 event_type_code,
1557 event_date,
1558 event_status_code,
1559 process_status_code,
1560 trx_date,
1561 ACCOUNT_DATE,
1562 PERIOD_NAME,
1563 CATEGORY_NAME,
1564 JLBR_TRANSFER_TO_GL_FLAG,
1565 GL_TRANSFER_DATE,
1566 bordero_id,
1567 bordero_type,
1568 bank_occurrence_code,
1569 bank_occurrence_type,
1570 bank_party_id,
1571 customer_trx_id,
1572 document_id,
1573 bank_charges_credit_ccid,
1574 bank_charges_debit_ccid,
1575 endorsement_credit_ccid,
1576 endorsement_debit_ccid,
1577 factor_interest_credit_ccid,
1578 factor_interest_debit_ccid,
1579 occurrence_id,
1580 reverse_charges_cred_ccid,
1581 reverse_charges_deb_ccid,
1582 bill_to_customer_id,
1583 bill_to_site_use_id,
1584 trx_number,
1585 rid
1586 )
1587 VALUES (
1588 xla_transaction_entities_s.nextval,
1589 set_of_books_id,
1590 accounting_method,
1591 creation_date,
1592 gl_posted_date,
1593 last_update_date,
1594 flag_post_gl,
1595 std_occurrence_code,
1596 nvl(occurrence_status,'CONFIRMED'),
1597 oc_gl_date,
1598 (Decode(std_occurrence_code, 'REMITTANCE',
1599 decode(occurrence_status, 'CANCELED',decode(bordero_type,'COLLECTION', 'CANCEL_COLL_DOC', 'CANCEL_FACT_DOC'),
1600 decode(bordero_type,'COLLECTION', 'REMIT_COLL_DOC' ,'REMIT_FACT_DOC')), 'WRITE_OFF_REQUISITION', 'WRITE_OFF_COLL_DOC', 'CONFIRMED_ENTRY',
1601 decode(bordero_type,'COLLECTION', 'CONFIRM_COLL_DOC', 'CONFIRM_FACT_DOC'), 'REJECTED_ENTRY',
1602 decode(bordero_type,'COLLECTION', 'REJECT_COLL_DOC', 'REJECT_FACT_DOC'), 'FULL_SETTLEMENT',
1603 decode(bordero_type,'COLLECTION', 'FULLY_SETTLE_COLL_DOC', 'FULLY_SETTLE_FACT_DOC'), 'PARTIAL_SETTLEMENT',
1604 'PARTIALLY_SETTLE_COLL_DOC','PAYMENT_AFTER_WRITE_OFF', 'PAY_COLL_DOC_AFTER_WRITE_OFF',
1605 'AUTOMATIC_WRITE_OFF', 'WRITE_OFF_COLL_DOC', 'APPLY_BANK_CHARGES_COLL_DOC' )), -- event type code
1606 Decode(occurrence_status, 'CANCELED',last_update_date, creation_date), --event date
1607 decode(accounting_method, 'CASH', 'N', decode(nvl(flag_post_gl,'N'), 'N', 'U','P')) , -- event status
1608 decode(nvl(flag_post_gl,'N'), 'N', 'U','P') , -- processing status
1609 decode(occurrence_status,'CANCELED', nvl(cd_cancel_date,last_update_date),
1610 nvl(occurrence_date,creation_date)), -- trx_date
1611 oc_gl_date, --account_date
1612 PERIOD_NAME,
1613 Decode(std_occurrence_code, 'REMITTANCE','Remittance', 'WRITE_OFF_REQUISITION', 'Write-off',
1614 'CONFIRMED_ENTRY','Confirmation', 'REJECTED_ENTRY', 'Rejection', 'FULL_SETTLEMENT', 'Bank Receipts',
1615 'PARTIAL_SETTLEMENT', 'Bank Receipts','PAYMENT_AFTER_WRITE_OFF', 'Bank Receipts',
1616 'AUTOMATIC_WRITE_OFF', 'Write-off', 'Bank Charges'), -- Category Name
1617 nvl(flag_post_gl,'N'), -- jl_br_transfer_to_gl_flag
1618 gl_posted_date, -- gl_transfer_date
1619 bordero_id,
1620 bordero_type,
1621 bank_occurrence_code,
1622 bank_occurrence_type,
1623 bank_party_id,
1624 customer_trx_id,
1625 document_id,
1626 bank_charges_credit_ccid,
1627 bank_charges_debit_ccid,
1628 endorsement_credit_ccid,
1629 endorsement_debit_ccid,
1630 factor_interest_credit_ccid ,
1631 factor_interest_debit_ccid,
1632 occurrence_id,
1633 reverse_charges_cred_ccid ,
1634 reverse_charges_deb_ccid ,
1635 bill_to_customer_id,
1636 bill_to_site_use_id,
1637 trx_number,
1638 rid
1639 )
1640 WHEN (occurrence_status = 'CANCELED') THEN
1641 INTO jl_cancel_gt (
1642 entity_id,
1643 set_of_books_id,
1644 accounting_method,
1645 creation_date,
1646 gl_posted_date,
1647 last_update_date,
1648 flag_post_gl,
1649 std_occurrence_code,
1650 OCCURRENCE_STATUS,
1651 OC_gl_date,
1652 event_type_code,
1653 event_date,
1654 event_status_code,
1655 process_status_code,
1656 trx_date,
1657 ACCOUNT_DATE,
1658 PERIOD_NAME,
1659 CATEGORY_NAME,
1660 JLBR_TRANSFER_TO_GL_FLAG,
1661 GL_TRANSFER_DATE,
1662 bordero_id,
1663 bordero_type,
1664 bank_occurrence_code,
1665 bank_occurrence_type,
1666 bank_party_id,
1667 customer_trx_id,
1668 document_id,
1669 bank_charges_credit_ccid,
1670 bank_charges_debit_ccid,
1671 endorsement_credit_ccid,
1672 endorsement_debit_ccid,
1673 factor_interest_credit_ccid,
1674 factor_interest_debit_ccid,
1675 occurrence_id,
1676 reverse_charges_cred_ccid,
1677 reverse_charges_deb_ccid,
1678 bill_to_customer_id,
1679 bill_to_site_use_id,
1680 trx_number,
1681 rid
1682 )
1683 VALUES (
1684 xla_transaction_entities_s.nextval,
1685 set_of_books_id,
1686 accounting_method,
1687 creation_date,
1688 gl_posted_date,
1689 occurrence_date, -- last updated date
1690 nvl2(gl_posted_date,'Y','N'), -- flag_post_gl
1691 'REMITTANCE',
1692 'CANCELREMIT', -- Occ Status
1693 csc_gl_date, -- OC GL Date
1694 (Decode(bordero_type,'COLLECTION','REMIT_COLL_DOC' ,'REMIT_FACT_DOC')), --event type
1695 creation_date, -- event date
1696 decode(accounting_method, 'CASH', 'N', decode(decode(gl_posted_date,NULL,'N','Y'), 'N', 'U','P')) ,
1697 decode(decode(gl_posted_date,NULL,'N','Y'), 'N', 'U','P') ,
1698 nvl(occurrence_date,creation_date), --trx date
1699 csc_gl_date, -- account date
1700 csc_period_name,
1701 'Remittance', -- Category Name
1702 decode(gl_posted_date,NULL,'N','Y'), -- JL_BR_GL_TRANSFER_FLAG
1703 GL_POSTED_DATE, -- GL Transfer Date
1704 bordero_id,
1705 bordero_type,
1706 bank_occurrence_code,
1707 'REMITTANCE_OCCURRENCE',
1708 bank_party_id,
1709 customer_trx_id,
1710 document_id,
1711 bank_charges_credit_ccid,
1712 bank_charges_debit_ccid,
1713 endorsement_credit_ccid,
1714 endorsement_debit_ccid,
1715 factor_interest_credit_ccid,
1716 factor_interest_debit_ccid,
1717 occurrence_id,
1718 reverse_charges_cred_ccid,
1719 reverse_charges_deb_ccid,
1720 bill_to_customer_id,
1721 bill_to_site_use_id,
1722 trx_number ,
1723 RID
1724 )
1725 SELECT
1726 X.legal_entity_id, X.org_id, X.set_of_books_id,
1727 X.transaction_number,
1728 X.cd_cancel_date,
1729 X.accounting_method,
1730 X.creation_date ,
1731 X.gl_posted_date ,
1732 X.last_update_date ,
1733 X.flag_post_gl ,
1734 X.std_occurrence_code ,
1735 X.occurrence_status ,
1736 X.oc_gl_date,
1737 X.occurrence_date ,
1738 X.csc_gl_date,
1739 per.period_name PERIOD_NAME,
1740 X.CSC_PERIOD_NAME,
1741 X.bordero_id ,
1742 X.bordero_type ,
1743 X.bank_occurrence_code ,
1744 X.bank_occurrence_type ,
1745 X.bank_party_id ,
1746 X.customer_trx_id ,
1747 X.document_id ,
1748 X.bank_charges_credit_ccid ,
1749 X.bank_charges_debit_ccid ,
1750 X.endorsement_credit_ccid ,
1751 X.endorsement_debit_ccid ,
1752 X.factor_interest_credit_ccid ,
1753 X.factor_interest_debit_ccid ,
1754 X.occurrence_id ,
1755 X.reverse_charges_cred_ccid ,
1756 X.reverse_charges_deb_ccid ,
1757 X.bill_to_customer_id ,
1758 X.bill_to_site_use_id ,
1759 X.trx_number ,
1760 X.RID
1761 FROM (SELECT /*+ leading(oc,cd,ract,xud) swap_join_inputs(xud) swap_join_inputs(sys) swap_join_inputs(bo) use_nl(b,csc) */
1762 ract.legal_entity_id, cd.org_id, sys.set_of_books_id,
1763 rtrim(ract.trx_number||'-'||to_char(cd.terms_sequence_number)||':'||to_char(cd.document_id)||':'||bo.description)
1764 transaction_number,
1765 cd.cancellation_date cd_cancel_date,
1766 sys.accounting_method accounting_method,
1767 oc.creation_date creation_date,
1768 oc.gl_posted_date gl_posted_date,
1769 oc.last_update_date last_update_date,
1770 oc.flag_post_gl flag_post_gl,
1771 bo.std_occurrence_code std_occurrence_code,
1772 oc.occurrence_status occurrence_status,
1773 nvl(oc.gl_date,oc.creation_date) oc_gl_date,
1774 nvl(oc.gl_date,oc.creation_date) accounting_date,
1775 oc.occurrence_date occurrence_date,
1776 csc.gl_date csc_gl_date,
1777 gsb.period_set_name period_set_name,
1778 gsb.accounted_period_type period_type,
1779 per1.period_name CSC_PERIOD_NAME,
1780 b.bordero_id BORDERO_ID,
1781 b.bordero_type BORDERO_TYPE,
1782 bo.bank_occurrence_code BANK_OCCURRENCE_CODE,
1783 bo.bank_occurrence_type BANK_OCCURRENCE_TYPE,
1784 bo.bank_party_id BANK_PARTY_ID,
1785 cd.customer_trx_id CUSTOMER_TRX_ID,
1786 cd.document_id DOCUMENT_ID,
1787 oc.bank_charges_credit_ccid BANK_CHARGES_CREDIT_CCID,
1788 oc.bank_charges_debit_ccid BANK_CHARGES_DEBIT_CCID,
1789 oc.endorsement_credit_ccid ENDORSEMENT_CREDIT_CCID,
1790 oc.endorsement_debit_ccid ENDORSEMENT_DEBIT_CCID,
1791 oc.factor_interest_credit_ccid FACTOR_INTEREST_CREDIT_CCID,
1792 oc.factor_interest_debit_ccid FACTOR_INTEREST_DEBIT_CCID,
1793 oc.occurrence_id OCCURRENCE_ID,
1794 oc.reverse_charges_cred_ccid REVERSE_CHARGES_CRED_CCID,
1795 oc.reverse_charges_deb_ccid REVERSE_CHARGES_DEB_CCID,
1796 ract.bill_to_customer_id BILL_TO_CUSTOMER_ID,
1797 ract.bill_to_site_use_id BILL_TO_SITE_USE_ID,
1798 ract.trx_number TRX_NUMBER ,
1799 oc.ROWID RID
1800 FROM
1801 jl_br_ar_occurrence_docs_all oc,
1802 jl_br_ar_collection_docs_all cd,
1803 ra_customer_trx_all ract,
1804 jl_br_ar_bank_occurrences bo,
1805 jl_br_ar_select_accounts_all csc,
1806 jl_br_ar_borderos_all b,
1807 ar_system_parameters_all sys,
1808 gl_date_period_map per1,
1809 gl_sets_of_books gsb
1810 WHERE oc.document_id between l_start_rowid and l_end_rowid
1811 AND nvl(oc.occurrence_status,'CONFIRMED') <> 'CREATED'
1812 And bo.bank_occurrence_code = oc.bank_occurrence_code
1813 And bo.bank_occurrence_type = oc.bank_occurrence_type
1814 And bo.bank_number = oc.bank_number
1815 And oc.document_id = cd.document_id
1816 And cd.customer_trx_id = ract.customer_trx_id
1817 And cd.bordero_id = b.bordero_id
1818 And csc.select_account_id = b.select_account_id
1819 And sys.org_id = cd.org_id
1820 And gsb.set_of_books_id = ract.set_of_books_id
1821 And gsb.period_set_name = per1.period_set_name
1822 AND per1.period_type = gsb.accounted_period_type
1823 AND per1.accounting_date = csc.gl_date
1824 and bo.std_occurrence_code in ('REMITTANCE','WRITE_OFF_REQUISITION','CONFIRMED_ENTRY', 'REJECTED_ENTRY',
1825 'FULL_SETTLEMENT', 'PARTIAL_SETTLEMENT', 'PAYMENT_AFTER_WRITE_OFF',
1826 'AUTOMATIC_WRITE_OFF', 'BANK_CHARGES', 'REMITTANCE_CONFIRMATION',
1827 'REMITTANCE_REJECTION', 'OTHER_OCCURRENCES')
1828 AND ((oc.endorsement_debit_ccid is not null and oc.endorsement_debit_amount is not null
1829 and bo.std_occurrence_code in ('REMITTANCE','FULL_SETTLEMENT','PARTIAL_SETTLEMENT',
1830 'REJECTED_ENTRY','WRITE_OFF_REQUISITION','AUTOMATIC_WRITE_OFF'))
1831 OR (oc.bank_charges_debit_ccid is not null AND oc.bank_charges_Debit_amount is not null))
1832 and (nvl(oc.flag_post_gl, 'N') = 'N'
1833 or (oc.flag_post_gl = 'Y'
1834 and (exists (
1835 select 'Y'
1836 from xla_upgrade_dates xud
1837 where ract.set_of_books_id = xud.ledger_id
1838 and ((oc.gl_date >= xud.start_date and oc.gl_date < xud.end_date)
1839 or (oc.gl_date IS NULL and oc.creation_date between xud.start_date and xud.end_date)
1840 or (nvl(oc.occurrence_status,'CONFIRMED') <> 'CANCELED'
1841 and oc.gl_date < xud.start_date
1842 and bo.std_occurrence_code = 'REMITTANCE'
1843 and exists (
1844 select 'Y'
1845 from jl_br_ar_occurrence_docs o
1846 where o.document_id = oc.document_id
1847 and (
1848 (o.gl_date is NULL and oc.creation_date between xud.start_date and xud.end_date)
1849 or (o.gl_date >= xud.start_date
1850 and o.gl_date < xud.end_date))))))
1851 or ((nvl(oc.occurrence_status,'CONFIRMED') <> 'CANCELED'
1852 and bo.std_occurrence_code = 'REMITTANCE'
1853 and exists(select 'Y' from jl_br_ar_occurrence_docs_all o2 where o2.document_id = oc.document_id
1854 and nvl(o2.flag_post_gl,'N') = 'N'))))
1855 ))
1856 And not exists
1857 (SELECT 'Y' FROM
1858 xla_transaction_entities_upg xae
1859 WHERE xae.APPLICATION_ID = 222
1860 And xae.ENTITY_CODE = 'JL_BR_AR_COLL_DOC_OCCS'
1861 And xae.LEDGER_ID = ract.set_of_books_id
1862 And nvl(xae.SOURCE_ID_INT_1,-99) = cd.document_id
1863 And nvl(xae.SOURCE_ID_INT_2,-99) = oc.occurrence_id)) X
1864 LEFT OUTER JOIN gl_date_period_map per
1865 USING (period_set_name, period_type, accounting_date);
1866
1867 -------------------------------------------------------------------
1868 -- Create the Journal Entry Events and Headers
1869 -------------------------------------------------------------------
1870
1871 -- Rerunnability conditions (for checking if records already exist
1872 -- are not added for Events, JE Headers, JE Lines and Dist Links
1873 -- because it is assumed that if the data in transaction entities is not
1874 -- present, then data in rest of the tables is also not present.
1875 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
1876 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Upgrade_Distributions','Create the Journal Entry Events and Headers. Inserting into xla_events, jl_remit1_gt,xla_ae_headers,jl_remit3_gt');
1877 END IF;
1878
1879 INSERT ALL
1880 WHEN 1 = 1 THEN
1881 INTO xla_events
1882 (
1883 EVENT_ID,
1884 APPLICATION_ID,
1885 EVENT_TYPE_CODE,
1886 EVENT_DATE,
1887 ENTITY_ID,
1888 EVENT_STATUS_CODE,
1889 PROCESS_STATUS_CODE,
1890 EVENT_NUMBER,
1891 ON_HOLD_FLAG,
1892 CREATION_DATE,
1893 CREATED_BY,
1894 LAST_UPDATE_DATE,
1895 LAST_UPDATED_BY,
1896 LAST_UPDATE_LOGIN,
1897 PROGRAM_UPDATE_DATE,
1898 PROGRAM_APPLICATION_ID,
1899 PROGRAM_ID,
1900 UPG_BATCH_ID,
1901 UPG_SOURCE_APPLICATION_ID,
1902 TRANSACTION_DATE
1903 )
1904 VALUES
1905 (
1906 xla_events_s.nextval ,
1907 222,
1908 EVENT_TYPE_CODE,
1909 EVENT_DATE,
1910 ENTITY_ID,
1911 EVENT_STATUS_CODE,
1912 PROCESS_STATUS_CODE,
1913 EVENT_NUMBER,
1914 'N',
1915 sysdate,
1916 2,
1917 Sysdate,
1918 2,
1919 -2005,
1920 Sysdate,
1921 222,
1922 -2005,
1923 l_batch_id,
1924 222,
1925 TRX_DATE
1926 )
1927 WHEN (JLBR_TRANSFER_TO_GL_FLAG = 'Y') THEN
1928 INTO jl_remit1_gt
1929 (
1930 SET_OF_BOOKS_ID,
1931 ENTITY_ID,
1932 EVENT_TYPE_CODE,
1933 ACCOUNT_DATE,
1934 PERIOD_NAME,
1935 CATEGORY_NAME,
1936 JLBR_TRANSFER_TO_GL_FLAG,
1937 GL_TRANSFER_DATE,
1938 BORDERO_ID,
1939 BORDERO_TYPE,
1940 BANK_OCCURRENCE_CODE,
1941 BANK_OCCURRENCE_TYPE,
1942 BANK_PARTY_ID,
1943 STD_OCCURRENCE_CODE,
1944 CUSTOMER_TRX_ID,
1945 DOCUMENT_ID,
1946 BANK_CHARGES_CREDIT_CCID,
1947 BANK_CHARGES_DEBIT_CCID,
1948 ENDORSEMENT_CREDIT_CCID,
1949 ENDORSEMENT_DEBIT_CCID,
1950 FACTOR_INTEREST_CREDIT_CCID,
1951 FACTOR_INTEREST_DEBIT_CCID,
1952 OCCURRENCE_ID,
1953 OCCURRENCE_STATUS,
1954 BILL_TO_CUSTOMER_ID,
1955 BILL_TO_SITE_USE_ID,
1956 REVERSE_CHARGES_CRED_CCID,
1957 TRX_NUMBER,
1958 EVENT_ID,
1959 HEADER_ID
1960 )
1961 VALUES
1962 (
1963 SET_OF_BOOKS_ID,
1964 ENTITY_ID,
1965 EVENT_TYPE_CODE,
1966 ACCOUNT_DATE,
1967 PERIOD_NAME,
1968 CATEGORY_NAME,
1969 JLBR_TRANSFER_TO_GL_FLAG,
1970 GL_TRANSFER_DATE,
1971 BORDERO_ID,
1972 BORDERO_TYPE,
1973 BANK_OCCURRENCE_CODE,
1974 BANK_OCCURRENCE_TYPE,
1975 BANK_PARTY_ID,
1976 STD_OCCURRENCE_CODE,
1977 CUSTOMER_TRX_ID,
1978 DOCUMENT_ID,
1979 BANK_CHARGES_CREDIT_CCID,
1980 BANK_CHARGES_DEBIT_CCID,
1981 ENDORSEMENT_CREDIT_CCID,
1982 ENDORSEMENT_DEBIT_CCID,
1983 FACTOR_INTEREST_CREDIT_CCID,
1984 FACTOR_INTEREST_DEBIT_CCID,
1985 OCCURRENCE_ID,
1986 OCCURRENCE_STATUS,
1987 BILL_TO_CUSTOMER_ID,
1988 BILL_TO_SITE_USE_ID,
1989 REVERSE_CHARGES_CRED_CCID,
1990 TRX_NUMBER,
1991 xla_events_s.nextval,
1992 xla_ae_headers_s.nextval
1993 )
1994 WHEN JLBR_TRANSFER_TO_GL_FLAG = 'Y' THEN
1995 INTO xla_ae_headers
1996 (
1997 AE_HEADER_ID,
1998 APPLICATION_ID,
1999 LEDGER_ID,
2000 ENTITY_ID,
2001 EVENT_ID,
2002 EVENT_TYPE_CODE,
2003 ACCOUNTING_DATE,
2004 PERIOD_NAME,
2005 JE_CATEGORY_NAME,
2006 GL_TRANSFER_STATUS_CODE,
2007 GL_TRANSFER_DATE,
2008 ACCOUNTING_ENTRY_STATUS_CODE,
2009 ACCOUNTING_ENTRY_TYPE_CODE,
2010 AMB_CONTEXT_CODE,
2011 BALANCE_TYPE_CODE,
2012 CREATION_DATE,
2013 CREATED_BY,
2014 LAST_UPDATE_DATE,
2015 LAST_UPDATED_BY,
2016 LAST_UPDATE_LOGIN,
2017 PROGRAM_UPDATE_DATE,
2018 PROGRAM_APPLICATION_ID,
2019 PROGRAM_ID,
2020 UPG_BATCH_ID,
2021 UPG_SOURCE_APPLICATION_ID,
2022 ZERO_AMOUNT_FLAG
2023 )
2024 VALUES
2025 (
2026 xla_ae_headers_s.nextval,
2027 222,
2028 SET_OF_BOOKS_ID,
2029 ENTITY_ID,
2030 xla_events_s.currval ,
2031 EVENT_TYPE_CODE,
2032 ACCOUNT_DATE,
2033 PERIOD_NAME,
2034 CATEGORY_NAME,
2035 JLBR_TRANSFER_TO_GL_FLAG,
2036 GL_TRANSFER_DATE,
2037 'F',
2038 'STANDARD',
2039 'DEFAULT',
2040 'A',
2041 sysdate,
2042 2,
2043 sysdate,
2044 2,
2045 -2005,
2046 sysdate,
2047 222,
2048 -2005,
2049 l_batch_id,
2050 222,
2051 'N'
2052 )
2053 WHEN 1 = 1 THEN
2054 INTO jl_remit3_gt
2055 (
2056 ROW_ID,
2057 EVENT_TYPE_CODE,
2058 EVENT_ID
2059 )
2060 VALUES
2061 (
2062 RID,
2063 EVENT_TYPE_CODE,
2064 xla_events_s.nextval
2065 )
2066 SELECT
2067 SET_OF_BOOKS_ID,
2068 ENTITY_ID,
2069 EVENT_TYPE_CODE,
2070 ACCOUNT_DATE,
2071 PERIOD_NAME,
2072 CATEGORY_NAME,
2073 JLBR_TRANSFER_TO_GL_FLAG,
2074 GL_TRANSFER_DATE,
2075 BORDERO_ID,
2076 BORDERO_TYPE,
2077 BANK_OCCURRENCE_CODE,
2078 BANK_OCCURRENCE_TYPE,
2079 BANK_PARTY_ID,
2080 STD_OCCURRENCE_CODE,
2081 CUSTOMER_TRX_ID,
2082 DOCUMENT_ID,
2083 BANK_CHARGES_CREDIT_CCID,
2084 BANK_CHARGES_DEBIT_CCID,
2085 ENDORSEMENT_CREDIT_CCID,
2086 ENDORSEMENT_DEBIT_CCID,
2087 FACTOR_INTEREST_CREDIT_CCID,
2088 FACTOR_INTEREST_DEBIT_CCID,
2089 OCCURRENCE_ID,
2090 OCCURRENCE_STATUS,
2091 BILL_TO_CUSTOMER_ID,
2092 BILL_TO_SITE_USE_ID,
2093 REVERSE_CHARGES_CRED_CCID,
2094 TRX_NUMBER,
2095 TRX_DATE,
2096 PROCESS_STATUS_CODE,
2097 EVENT_STATUS_CODE,
2098 EVENT_DATE,
2099 1 EVENT_NUMBER,
2100 RID
2101 FROM
2102 jl_remit_gt
2103 UNION ALL
2104 SELECT
2105 SET_OF_BOOKS_ID,
2106 ENTITY_ID,
2107 EVENT_TYPE_CODE,
2108 ACCOUNT_DATE,
2109 PERIOD_NAME,
2110 CATEGORY_NAME,
2111 JLBR_TRANSFER_TO_GL_FLAG,
2112 GL_TRANSFER_DATE,
2113 BORDERO_ID,
2114 BORDERO_TYPE,
2115 BANK_OCCURRENCE_CODE,
2116 BANK_OCCURRENCE_TYPE,
2117 BANK_PARTY_ID,
2118 STD_OCCURRENCE_CODE,
2119 CUSTOMER_TRX_ID,
2120 DOCUMENT_ID,
2121 BANK_CHARGES_CREDIT_CCID,
2122 BANK_CHARGES_DEBIT_CCID,
2123 ENDORSEMENT_CREDIT_CCID,
2124 ENDORSEMENT_DEBIT_CCID,
2125 FACTOR_INTEREST_CREDIT_CCID,
2126 FACTOR_INTEREST_DEBIT_CCID,
2127 OCCURRENCE_ID,
2128 OCCURRENCE_STATUS,
2129 BILL_TO_CUSTOMER_ID,
2130 BILL_TO_SITE_USE_ID,
2131 REVERSE_CHARGES_CRED_CCID,
2132 TRX_NUMBER ,
2133 TRX_DATE,
2134 PROCESS_STATUS_CODE,
2135 EVENT_STATUS_CODE,
2136 EVENT_DATE,
2137 2 EVENT_NUMBER,
2138 RID
2139 FROM
2140 jl_cancel_gt;
2141
2142 ------------------------------------------------------------------
2143 -- Updating the event id and the cancel event id
2144 ------------------------------------------------------------------
2145 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2146 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Upgrade_Distributions','Updating the event id and the cancel event id of table jl_br_ar_occurrence_docs_all');
2147 END IF;
2148 OPEN c_events;
2149 FETCH c_events BULK COLLECT INTO
2150 JL_BR_AR_BANK_ACCT_PKG.trx_events.row_id,
2151 JL_BR_AR_BANK_ACCT_PKG.trx_events.event_id,
2152 JL_BR_AR_BANK_ACCT_PKG.trx_events.cancel_event_id
2153 ;
2154
2155 CLOSE c_events;
2156
2157 FORALL i in 1..nvl(JL_BR_AR_BANK_ACCT_PKG.trx_events.row_id.LAST,-99)
2158
2159 UPDATE jl_br_ar_occurrence_docs_all occ
2160 set event_id = JL_BR_AR_BANK_ACCT_PKG.trx_events.event_id(i)
2161 ,cancel_event_id = JL_BR_AR_BANK_ACCT_PKG.trx_events.cancel_event_id(i)
2162 where rowid = JL_BR_AR_BANK_ACCT_PKG.trx_events.row_id(i) ;
2163 -------------------------------------------------------------------
2164 -- Create the Journal Entry Lines
2165 -------------------------------------------------------------------
2166 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2167 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Upgrade_Distributions','Creating journal entries by inserting into xla_ae_lines, xla_distribution_links');
2168 END IF;
2169
2170 INSERT ALL
2171 WHEN 1 = 1 THEN
2172 INTO xla_ae_lines
2173 (
2174 AE_HEADER_ID,
2175 AE_LINE_NUM,
2176 DISPLAYED_LINE_NUMBER,
2177 APPLICATION_ID,
2178 CODE_COMBINATION_ID,
2179 LEDGER_ID,
2180 ACCOUNTING_DATE,
2181 GL_TRANSFER_MODE_CODE,
2182 ACCOUNTED_CR ,
2183 ACCOUNTED_DR,
2184 ENTERED_CR,
2185 ENTERED_DR ,
2186 ACCOUNTING_CLASS_CODE,
2187 CURRENCY_CODE,
2188 CURRENCY_CONVERSION_DATE ,
2189 CURRENCY_CONVERSION_RATE ,
2190 CURRENCY_CONVERSION_TYPE ,
2191 DESCRIPTION,
2192 PARTY_ID ,
2193 PARTY_SITE_ID,
2194 PARTY_TYPE_CODE ,
2195 CONTROL_BALANCE_FLAG ,
2196 CREATION_DATE,
2197 CREATED_BY,
2198 LAST_UPDATE_DATE,
2199 LAST_UPDATED_BY,
2200 LAST_UPDATE_LOGIN ,
2201 PROGRAM_UPDATE_DATE ,
2202 PROGRAM_APPLICATION_ID ,
2203 PROGRAM_ID ,
2204 UPG_BATCH_ID,
2205 UNROUNDED_ACCOUNTED_CR ,
2206 UNROUNDED_ACCOUNTED_DR,
2207 GAIN_OR_LOSS_FLAG,
2208 UNROUNDED_ENTERED_CR,
2209 UNROUNDED_ENTERED_DR
2210 )
2211 VALUES
2212 (
2213 HEADER_ID,
2214 LINE_NUM,
2215 LINE_NUM,
2216 222,
2217 CCID,
2218 SET_OF_BOOKS_ID,
2219 ACCOUNT_DATE,
2220 'D' ,
2221 ACCOUNTED_CR ,
2222 ACCOUNTED_DR,
2223 ENTERED_CR,
2224 ENTERED_DR ,
2225 ACCOUNTING_CLASS_CODE,
2226 CURRENCY_CODE,
2227 CONVERSION_DATE,
2228 CONVERSION_RATE,
2229 CONVERSION_RATE_TYPE,
2230 DESCRIPTION,
2231 PARTY_ID,
2232 PARTY_SITE_ID ,
2233 'C',
2234 CONTROL_BALANCE_FLAG,
2235 sysdate,
2236 2,
2237 sysdate,
2238 2,
2239 -2005,
2240 sysdate,
2241 222,
2242 -2005,
2243 100, --l_batch_id,
2244 UNROUNDED_ACCOUNTED_CR,
2245 UNROUNDED_ACCOUNTED_DR,
2246 'N' ,
2247 UNROUNDED_ENTERED_CR,
2248 UNROUNDED_ENTERED_DR
2249 )
2250 WHEN 1 = 1 THEN
2251 INTO xla_distribution_links
2252 (
2253 APPLICATION_ID,
2254 EVENT_ID,
2255 AE_HEADER_ID,
2256 AE_LINE_NUM,
2257 ACCOUNTING_LINE_CODE,
2258 ACCOUNTING_LINE_TYPE_CODE ,
2259 REF_AE_HEADER_ID,
2260 SOURCE_DISTRIBUTION_TYPE,
2261 SOURCE_DISTRIBUTION_ID_NUM_1 ,
2262 MERGE_DUPLICATE_CODE,
2263 TEMP_LINE_NUM,
2264 REF_EVENT_ID ,
2265 EVENT_CLASS_CODE,
2266 EVENT_TYPE_CODE,
2267 UPG_BATCH_ID ,
2268 UNROUNDED_ENTERED_DR ,
2269 UNROUNDED_ENTERED_CR ,
2270 UNROUNDED_ACCOUNTED_CR ,
2271 UNROUNDED_ACCOUNTED_DR
2272 )
2273 VALUES
2274 (
2275 222,
2276 EVENT_ID,
2277 HEADER_ID,
2278 LINE_NUM,
2279 ACCOUNTING_CLASS_CODE,
2280 'C',
2281 REF_HEADER_ID,
2282 'JLBR_AR_DIST',
2283 DISTRIBUTION_ID,
2284 'N',
2285 LINE_NUM,
2286 REF_EVENT_ID,
2287 EVENT_CLASS_CODE,
2288 EVENT_TYPE_CODE,
2289 100, --l_batch_id,
2290 ENTERED_AMOUNT,
2291 ENTERED_AMOUNT,
2292 ENTERED_AMOUNT,
2293 ENTERED_AMOUNT
2294 )
2295 SELECT
2296 jlje.HEADER_ID AS HEADER_ID,
2297 row_number() OVER (PARTITION BY HEADER_ID ORDER BY DISTRIBUTION_ID, MULTIPLIER) LINE_NUM,
2298 jlje.CCID AS CCID ,
2299 jlje.ACCOUNTING_CLASS_CODE AS ACCOUNTING_CLASS_CODE ,
2300 jlje.ACCOUNT_DATE AS ACCOUNT_DATE ,
2301 jlje.SET_OF_BOOKS_ID AS SET_OF_BOOKS_ID,
2302 jlje.CURRENCY_CODE AS CURRENCY_CODE ,
2303 jlje.CONVERSION_DATE AS CONVERSION_DATE ,
2304 jlje.CONVERSION_RATE AS CONVERSION_RATE ,
2305 jlje.CONVERSION_RATE_TYPE AS CONVERSION_RATE_TYPE ,
2306 jlje.PARTY_ID AS PARTY_ID ,
2307 jlje.PARTY_SITE_ID AS PARTY_SITE_ID ,
2308 jlje.EVENT_ID AS EVENT_ID ,
2309 jlje.DISTRIBUTION_ID AS DISTRIBUTION_ID ,
2310 jlje.EVENT_CLASS_CODE AS EVENT_CLASS_CODE ,
2311 jlje.EVENT_TYPE_CODE AS EVENT_TYPE_CODE ,
2312 jlje.ACCOUNTED_CR AS ACCOUNTED_CR ,
2313 jlje.ACCOUNTED_DR AS ACCOUNTED_DR ,
2314 jlje.ENTERED_CR AS ENTERED_CR ,
2315 jlje.ENTERED_DR AS ENTERED_DR ,
2316 jlje.UNROUNDED_ACCOUNTED_CR AS UNROUNDED_ACCOUNTED_CR ,
2317 jlje.UNROUNDED_ACCOUNTED_DR AS UNROUNDED_ACCOUNTED_DR ,
2318 jlje.UNROUNDED_ENTERED_CR AS UNROUNDED_ENTERED_CR ,
2319 jlje.UNROUNDED_ENTERED_DR AS UNROUNDED_ENTERED_DR ,
2320 jlje.ENTERED_AMOUNT AS ENTERED_AMOUNT,
2321 jlje.DESCRIPTION AS DESCRIPTION,
2322 decode(gcc.reference3, 'Y', 'P', NULL) AS CONTROL_BALANCE_FLAG,
2323 jlje.REF_HEADER_ID AS REF_HEADER_ID ,
2324 jlje.REF_EVENT_ID AS REF_EVENT_ID,
2325 jlje.multiplier AS MULTIPLIER
2326 FROM
2327 (SELECT /*+ ordered use_hash(reftr2) no_expand use_nl_with_index(dist) */
2328 tr2.header_id HEADER_ID,
2329 tr2.ACCOUNT_DATE ACCOUNT_DATE,
2330 tr2.SET_OF_BOOKS_ID SET_OF_BOOKS_ID,
2331 DECODE(grm.multiplier, 1 ,
2332 Decode(dist.distribution_type,
2333 'JLBR_AR_ENDORSEMENT', Decode( dist.accounting_reversal_option,'Y',
2334 TR2.endorsement_debit_ccid, TR2.endorsement_credit_ccid),
2335 'JLBR_AR_BANK_CHARGES', decode( dist.accounting_reversal_option,'Y',
2336 decode(dist.std_occurrence_code,'REJECTED_ENTRY',
2337 TR2.reverse_charges_cred_ccid, TR2.bank_charges_debit_ccid),
2338 TR2.bank_charges_credit_ccid),
2339 decode(dist.accounting_reversal_option,'Y', TR2.factor_interest_debit_ccid,
2340 TR2.factor_interest_credit_ccid)),
2341 2, Decode(dist.distribution_type,
2342 'JLBR_AR_ENDORSEMENT', decode( dist.accounting_reversal_option,'Y',
2343 TR2.endorsement_credit_ccid, TR2.ENDORSEMENT_DEBIT_CCID),
2344 'JLBR_AR_BANK_CHARGES', decode( dist.accounting_reversal_option,'Y',
2345 decode(dist.std_occurrence_code, 'REJECTED_ENTRY',
2346 TR2.reverse_charges_deb_ccid, TR2.bank_charges_credit_ccid),
2347 TR2.bank_charges_debit_ccid),
2348 decode(dist.accounting_reversal_option,'Y', TR2.factor_interest_credit_ccid,
2349 TR2.factor_interest_debit_ccid))) CCID,
2350 Decode(dist.distribution_type,
2351 'JLBR_AR_ENDORSEMENT', decode( TR2.bordero_type,'COLLECTION', 'REMITTANCE', 'FACTOR'),
2352 decode(grm.multiplier,
2353 1,decode(dist.accounting_reversal_option,'Y','BANK_CHARGES','CASH'),
2354 2,decode(dist.accounting_reversal_option,'Y','CASH','BANK_CHARGES'))) ACCOUNTING_CLASS_CODE,
2355 dist.entered_currency_code CURRENCY_CODE,
2356 dist.CONVERSION_DATE CONVERSION_DATE,
2357 dist.CONVERSION_RATE CONVERSION_RATE,
2358 dist.CONVERSION_RATE_TYPE CONVERSION_RATE_TYPE,
2359 TR2.bill_to_customer_id PARTY_ID,
2360 TR2.bill_to_site_use_id PARTY_SITE_ID ,
2361 TR2.event_id EVENT_ID,
2362 TR2.OCCURRENCE_STATUS OCCURRENCE_STATUS,
2363 dist.distribution_id DISTRIBUTION_ID,
2364 dist.prior_dist_id PRIOR_DIST_ID,
2365 dist.reversed_dist_id REVERSED_DIST_ID,
2366 refTR2.header_id REF_HEADER_ID,
2367 refTR2.event_id REF_EVENT_ID,
2368 decode(TR2.bordero_type,'COLLECTION','COLLECTION_OCC_DOCUMENT',
2369 'FACTORING_OCC_DOCUMENT') EVENT_CLASS_CODE,
2370 TR2.event_type_code EVENT_TYPE_CODE,
2371 decode(grm.multiplier,1,dist.acctd_amt,2,NULL) ACCOUNTED_CR ,
2372 decode(grm.multiplier,1,NULL,2,dist.acctd_amt) ACCOUNTED_DR,
2373 decode(grm.multiplier,1,dist.entered_amt,2,NULL) ENTERED_CR,
2374 decode(grm.multiplier,1,NULL,2,dist.entered_amt) ENTERED_DR ,
2375 decode(grm.multiplier,1,dist.acctd_amt,2,NULL) UNROUNDED_ACCOUNTED_CR ,
2376 decode(grm.multiplier,1,NULL,2,dist.acctd_amt) UNROUNDED_ACCOUNTED_DR,
2377 decode(grm.multiplier,1,dist.entered_amt,2,NULL) UNROUNDED_ENTERED_CR,
2378 decode(grm.multiplier,1,NULL,2,dist.entered_amt) UNROUNDED_ENTERED_DR,
2379 dist.entered_amt ENTERED_AMOUNT,
2380 decode(grm.multiplier,1,'Credito '||decode(dist.distribution_type,'JLBR_AR_ENDORSEMENT',
2381 decode(TR2.std_occurrence_code,'REMITTANCE','Endosso para ','OTHER_DATA_CHANGING','Endosso para ','Titulo em '),'Banco Conta Movimento ') || decode(dist.distribution_type,'JLBR_AR_ENDORSEMENT',
2382 decode(TR2.bordero_type,'COLLECTION','COBRANCA ','DESCONTO '), '')||
2383 decode(TR2.occurrence_status,'CANCELED','- Cobranca Bancaria - Cancelamento','- Cobranca Bancaria - ')||
2384 decode(TR2.occurrence_status,'CANCELED','Remittance',
2385 decode(TR2.std_occurrence_code, 'REMITTANCE','Remittance',
2386 'WRITE_OFF_REQUISITION', 'Write-off', 'CONFIRMED_ENTRY','Confirmation', 'REJECTED_ENTRY', 'Rejection', 'FULL_SETTLEMENT', 'Bank Receipts', 'PARTIAL_SETTLEMENT', 'Bank Receipts',
2387 'PAYMENT_AFTER_WRITE_OFF', 'Bank Receipts', 'AUTOMATIC_WRITE_OFF', 'Write-off', 'Bank Charges' )) ||
2388 ' - Invoice ' ||substr(TR2.trx_number,1,15),
2389 2,
2390 'Debito '||decode(dist.distribution_type,'JLBR_AR_ENDORSEMENT',
2391 decode(TR2.std_occurrence_code,'REMITTANCE','Titulo em ','OTHER_DATA_CHANGING','Titulo em ','Endosso para '),' Despesas Financeiras e Bancarias') ||
2392 decode(dist.distribution_type,'JLBR_AR_ENDORSEMENT',
2393 decode(TR2.bordero_type,'COLLECTION','COBRANCA ','DESCONTO'),'')||
2394 decode(TR2.occurrence_status,'CANCELED','- Cobranca Bancaria - Cancelamento','- Cobranca Bancaria - ')||
2395 decode(TR2.occurrence_status,'CANCELED','Remittance',
2396 decode(TR2.std_occurrence_code, 'REMITTANCE','Remittance',
2397 'WRITE_OFF_REQUISITION', 'Write-off', 'CONFIRMED_ENTRY','Confirmation', 'REJECTED_ENTRY', 'Rejection', 'FULL_SETTLEMENT', 'Bank Receipts', 'PARTIAL_SETTLEMENT', 'Bank Receipts',
2398 'PAYMENT_AFTER_WRITE_OFF', 'Bank Receipts', 'AUTOMATIC_WRITE_OFF', 'Write-off', 'Bank Charges' )) ||
2399 ' - Invoice ' ||substr(TR2.trx_number,1,15)) DESCRIPTION,
2400 grm.multiplier MULTIPLIER
2401 FROM
2402 jl_remit1_gt TR2,
2403 jl_remit1_gt refTR2,
2404 jl_br_ar_distributns_all dist,
2405 gl_row_multipliers grm
2406 WHERE
2407 dist.occurrence_id = TR2.occurrence_id
2408 And dist.document_id = TR2.document_id
2409 AND refTR2.document_id = TR2.document_id
2410 AND grm.multiplier < 3
2411 And (refTR2.event_type_code IN('REMIT_COLL_DOC' ,'REMIT_FACT_DOC')
2412 AND refTR2.occurrence_status = 'CONFIRMED'
2413 AND TR2.std_occurrence_code IN ('WRITE_OFF_REQUISITION','REJECTED_ENTRY',
2414 'FULL_SETTLEMENT','PARTIAL_SETTLEMENT','AUTOMATIC_WRITE_OFF')
2415 AND EXISTS(SELECT 'Y' FROM jl_br_ar_distributns_all dist2
2416 WHERE dist2.occurrence_id = refTR2.occurrence_id
2417 AND (dist2.distribution_id = dist.prior_dist_id and dist.prior_dist_id is not null
2418 or dist2.distribution_id = dist.reversed_dist_id and dist.reversed_dist_id is not null))
2419 OR refTR2.occurrence_id = TR2.occurrence_id
2420 AND dist.prior_dist_id is null
2421 and dist.reversed_dist_id is null
2422 OR refTR2.occurrence_id = TR2.occurrence_id
2423 and refTR2.event_type_code IN('REMIT_COLL_DOC' ,'REMIT_FACT_DOC')
2424 and refTR2.occurrence_status ='CANCELREMIT'
2425 )) jlje ,
2426 gl_code_combinations gcc
2427 WHERE gcc.code_combination_id = jlje.ccid;
2428
2429
2430 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
2431
2432 ad_parallel_updates_pkg.processed_id_range(
2433 l_rows_processed,
2434 l_end_rowid);
2435
2436 commit;
2437
2438 ad_parallel_updates_pkg.get_id_range(
2439 l_start_rowid,
2440 l_end_rowid,
2441 l_any_rows_to_process,
2442 l_batch_size,
2443 FALSE);
2444
2445 l_rows_processed := 0 ;
2446
2447 END LOOP ; /* end of WHILE loop */
2448
2449 EXCEPTION
2450 WHEN NO_DATA_FOUND THEN
2451 /*IF l_action_flag = 'R' THEN
2452 FND_MESSAGE.SET_NAME('FND', 'FND_GENERIC_MESSAGE');
2453 FND_MESSAGE.SET_TOKEN('MASSAGE' ,'Exception NO_DATA_FOUND in UPGRADE_OCCURRENCES ');
2454 FND_MSG_PUB.ADD;
2455 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2456 ELSE
2457 RAISE;
2458 END IF;
2459 */
2460 NULL;
2461
2462 WHEN OTHERS THEN
2463 IF l_action_flag = 'R' THEN
2464 FND_MESSAGE.SET_NAME('FND', 'FND_GENERIC_MESSAGE');
2465 FND_MESSAGE.SET_TOKEN('MASSAGE' ,'Exception OTHER in UPGRADE_OCCURRENCES '||SQLERRM);
2466 FND_MSG_PUB.ADD;
2467 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2468 ELSE
2469 RAISE;
2470 END IF;
2471
2472 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2473 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.Upgrade_Distributions','End procedure UPGRADE_OCCURRENCES');
2474 END IF;
2475 END UPGRADE_OCCURRENCES;
2476
2477
2478 /*========================================================================
2479 | PUBLIC PROCEDURE Update_Occurrences
2480 |
2481 | DESCRIPTION
2482 | Upgrades Posted or Yet to be Posted Occurrences to SLA tables
2483 | Called directly from on-demand upgrade concurrent program
2484 |
2485 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2486 |
2487 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2488 |
2489 | PARAMETERS
2490 |
2491 | KNOWN ISSUES
2492 |
2493 | NOTES
2494 |
2495 | MODIFICATION HISTORY
2496 | Date Author Description of Changes
2497 *=======================================================================*/
2498
2499 PROCEDURE UPDATE_OCCURRENCES(
2500 l_table_owner IN VARCHAR2,
2501 l_table_name IN VARCHAR2,
2502 l_script_name IN VARCHAR2,
2503 l_worker_id IN VARCHAR2,
2504 l_num_workers IN VARCHAR2,
2505 l_batch_size IN VARCHAR2,
2506 l_batch_id IN NUMBER,
2507 l_action_flag IN VARCHAR2,
2508 x_return_status OUT NOCOPY VARCHAR2) IS
2509
2510 l_start_rowid rowid;
2511 l_end_rowid rowid;
2512 l_any_rows_to_process boolean;
2513 l_rows_processed number := 0;
2514
2515 BEGIN
2516 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2517 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPDATE_OCCURRENCES','Start procedure UPDATE_OCCURRENCES');
2518 END IF;
2519 IF l_action_flag = 'R' THEN
2520 x_return_status := FND_API.G_RET_STS_SUCCESS;
2521 END IF;
2522
2523 /* ------ Initialize the rowid ranges ------ */
2524 ad_parallel_updates_pkg.initialize_rowid_range(
2525 ad_parallel_updates_pkg.ROWID_RANGE,
2526 l_table_owner,
2527 l_table_name,
2528 l_script_name,
2529 l_worker_id,
2530 l_num_workers,
2531 l_batch_size, 0);
2532
2533 /* ------ Get rowid ranges ------ */
2534 ad_parallel_updates_pkg.get_rowid_range(
2535 l_start_rowid,
2536 l_end_rowid,
2537 l_any_rows_to_process,
2538 l_batch_size,
2539 TRUE);
2540
2541 WHILE ( l_any_rows_to_process = TRUE )
2542 LOOP
2543
2544 l_rows_processed := 0;
2545
2546 -------------------------------------------------------------------
2547 -- Update event_id for unposted occurrences and distributions
2548 -- This will be used by Extract objects when Create Accounting
2549 -- program of SLA is run for the non posted data
2550 -------------------------------------------------------------------
2551 /* UPDATE jl_br_ar_occurrence_docs_all oc
2552 SET event_id = decode(oc.gl_posted_date, NULL, (SELECT a.event_id
2553 FROM xla_events a, xla_transaction_entities_upg c, ar_system_parameters_all sys
2554 WHERE sys.org_id = oc.org_id
2555 AND a.entity_id = c.entity_id
2556 AND a.event_type_code NOT IN ('CANCEL_COLL_DOC', 'CANCEL_FACT_DOC')
2557 AND c.APPLICATION_ID = 222
2558 AND c.ENTITY_CODE = 'JL_BR_AR_COLL_DOC_OCCS'
2559 AND c.LEDGER_ID = sys.set_of_books_id
2560 AND c.SOURCE_ID_INT_1 = oc.document_id
2561 AND c.source_id_int_2 = oc.occurrence_id),NULL) ,
2562 cancel_event_id = decode(oc.occurrence_status, 'CANCELED',(SELECT a1.event_id
2563 FROM xla_events a1, xla_transaction_entities_upg c1, ar_system_parameters_all sys1
2564 WHERE sys1.org_id = oc.org_id
2565 AND a1.entity_id = c1.entity_id
2566 AND a1.event_type_code IN ('CANCEL_COLL_DOC', 'CANCEL_FACT_DOC')
2567 AND c1.APPLICATION_ID = 222
2568 AND c1.ENTITY_CODE = 'JL_BR_AR_COLL_DOC_OCCS'
2569 AND c1.LEDGER_ID = sys1.set_of_books_id
2570 AND c1.SOURCE_ID_INT_1 = oc.document_id
2571 AND c1.source_id_int_2 = oc.occurrence_id),NULL)
2572 WHERE oc.rowid >= l_start_rowid
2573 AND oc.rowid <= l_end_rowid
2574 AND (oc.flag_post_gl = 'N' OR oc.flag_post_gl IS NULL)
2575 AND oc.event_id IS NULL;
2576
2577 */
2578
2579 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2580 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPDATE_OCCURRENCES','Update table jl_br_ar_distributns_all');
2581 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPDATE_OCCURRENCES','Update event_id for unposted occurrences and distributions');
2582 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPDATE_OCCURRENCES','This will be used by Extract objects when Create Accounting program of SLA is run for the non posted data');
2583 END IF;
2584 UPDATE /*+ rowid(jlbr) */ jl_br_ar_distributns_all jlbr
2585 SET event_id = decode(jlbr.accounting_reversal_option, 'Y',
2586 (SELECT a1.event_id
2587 FROM xla_events a1, xla_transaction_entities_upg c1, ar_system_parameters_all sys1,
2588 jl_br_ar_occurrence_docs_all oc1
2589 WHERE jlbr.occurrence_id = oc1.occurrence_id
2590 AND sys1.org_id = oc1.org_id
2591 AND a1.entity_id = c1.entity_id
2592 AND ((a1.event_type_code IN ('CANCEL_COLL_DOC', 'CANCEL_FACT_DOC')
2593 AND oc1.occurrence_status = 'CANCELED')
2594 OR (oc1.occurrence_status <> 'CANCELED'))
2595 AND c1.APPLICATION_ID = 222
2596 AND c1.ENTITY_CODE = 'JL_BR_AR_COLL_DOC_OCCS'
2597 AND c1.LEDGER_ID = sys1.set_of_books_id
2598 AND nvl(c1.SOURCE_ID_INT_1,-99) = oc1.document_id
2599 AND nvl(c1.source_id_int_2,-99) = oc1.occurrence_id),
2600 (SELECT a.event_id
2601 FROM xla_events a, xla_transaction_entities_upg c, ar_system_parameters_all sys
2602 WHERE sys.org_id = jlbr.org_id
2603 AND a.entity_id = c.entity_id
2604 AND a.event_type_code NOT IN ('CANCEL_COLL_DOC', 'CANCEL_FACT_DOC')
2605 AND c.APPLICATION_ID = 222
2606 AND c.ENTITY_CODE = 'JL_BR_AR_COLL_DOC_OCCS'
2607 AND c.LEDGER_ID = sys.set_of_books_id
2608 AND nvl(c.SOURCE_ID_INT_1,-99) = jlbr.document_id
2609 AND nvl(c.source_id_int_2,-99) = jlbr.occurrence_id))
2610 WHERE jlbr.event_id IS NULL
2611 AND jlbr.rowid >= l_start_rowid
2612 AND jlbr.rowid <= l_end_rowid
2613 AND EXISTS (SELECT 'Y' from jl_br_ar_occurrence_docs_all occ
2614 WHERE occ.occurrence_id = jlbr.occurrence_id
2615 AND (occ.flag_post_gl = 'N' OR occ.flag_post_gl IS NULL));
2616
2617 l_rows_processed := l_rows_processed + SQL%ROWCOUNT;
2618
2619 ad_parallel_updates_pkg.processed_rowid_range(
2620 l_rows_processed,
2621 l_end_rowid);
2622
2623 commit;
2624
2625 ad_parallel_updates_pkg.get_rowid_range(
2626 l_start_rowid,
2627 l_end_rowid,
2628 l_any_rows_to_process,
2629 l_batch_size,
2630 FALSE);
2631
2632 l_rows_processed := 0 ;
2633
2634 END LOOP ; /* end of WHILE loop */
2635
2636 EXCEPTION
2637 WHEN NO_DATA_FOUND THEN
2638 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2639 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPDATE_OCCURRENCES','No data found in FUNCTION UPDATE_OCCURRENCES');
2640 NULL;
2641 END IF;
2642 IF l_action_flag = 'R' THEN
2643 FND_MESSAGE.SET_NAME('FND', 'FND_GENERIC_MESSAGE');
2644 FND_MESSAGE.SET_TOKEN('MASSAGE' ,'Exception NO_DATA_FOUND in UPGRADE_OCCURRENCES ');
2645 FND_MSG_PUB.ADD;
2646 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2647 ELSE
2648 RAISE;
2649 END IF;
2650
2651 WHEN OTHERS THEN
2652 IF l_action_flag = 'R' THEN
2653 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2654 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPDATE_OCCURRENCES','Exception in FUNCTION UPDATE_OCCURRENCES');
2655 NULL;
2656 END IF;
2657 FND_MESSAGE.SET_NAME('FND', 'FND_GENERIC_MESSAGE');
2658 FND_MESSAGE.SET_TOKEN('MASSAGE' ,'Exception OTHER in UPGRADE_OCCURRENCES '||SQLERRM);
2659 FND_MSG_PUB.ADD;
2660 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2661
2662 ELSE
2663 RAISE;
2664 END IF;
2665
2666 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2667 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPDATE_OCCURRENCES','End procedure UPDATE_OCCURRENCES');
2668 END IF;
2669 END UPDATE_OCCURRENCES;
2670
2671
2672 /*========================================================================
2673 | PUBLIC PROCEDURE Load_Occurrences_Header_Data
2674 |
2675 | DESCRIPTION
2676 | Upgrades Posted or Yet to be Posted Occurrences to SLA tables
2677 | Called directly from on-demand upgrade concurrent program
2678 |
2679 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2680 |
2681 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2682 |
2683 | PARAMETERS
2684 |
2685 | KNOWN ISSUES
2686 |
2687 | NOTES
2688 |
2689 | MODIFICATION HISTORY
2690 | Date Author Description of Changes
2691 *=======================================================================*/
2692
2693 PROCEDURE load_occurrences_header_data(p_application_id IN NUMBER) IS
2694
2695 l_application_id NUMBER;
2696
2697 BEGIN
2698 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2699 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.load_occurrences_header_data','Start procedure load_occurrences_header_data');
2700 END IF;
2701 IF p_application_id IS NULL THEN
2702 l_application_id := 222;
2703 ELSE
2704 l_application_id := p_application_id;
2705 END IF;
2706 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2707 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.load_occurrences_header_data','l_application_id ='||l_application_id);
2708 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.load_occurrences_header_data','Insert into AR_XLA_LINES_EXTRACT ');
2709 END IF;
2710 INSERT INTO AR_XLA_LINES_EXTRACT(
2711 EVENT_ID
2712 ,LEDGER_ID
2713 ,SET_OF_BOOKS_ID
2714 ,ORG_ID
2715 ,CUSTOMER_TRX_ID
2716 ,REMITTANCE_BANK_ACCT_ID
2717 ,PAYMENT_SCHEDULE_ID
2718 ,RECEIPT_METHOD_ID
2719 ,SALESREP_ID
2720 ,BILL_SITE_USE_ID
2721 ,PAYING_SITE_USE_ID
2722 ,SOLD_SITE_USE_ID
2723 ,SHIP_SITE_USE_ID
2724 ,BILL_CUSTOMER_ID
2725 ,PAYING_CUSTOMER_ID
2726 ,SOLD_CUSTOMER_ID
2727 ,SHIP_CUSTOMER_ID
2728 ,REMIT_ADDRESS_ID
2729 ,SELECT_FLAG
2730 ,LEVEL_FLAG
2731 ,PAIRED_CCID
2732 ,EVENT_CLASS_CODE
2733 )
2734 SELECT /*+INDEX (gt XLA_EVENTS_GT_U1)*/
2735 gt.event_id -- EVENT_ID
2736 ,trx.set_of_books_id -- LEDGER_ID
2737 ,trx.set_of_books_id -- SET_OF_BOOKS_ID
2738 ,trx.org_id -- ORG_ID
2739 ,cd.customer_trx_id -- CUSTOMER_TRX_ID
2740 ,jlh.jlbr_bank_acct_use_id -- REMITTANCE_BANK_ACCT_ID
2741 ,cd.payment_schedule_id -- PAYMENT_SCHEDULE_ID
2742 ,jlh.jlbr_receipt_method_id -- RECEIPT_METHOD_ID
2743 ,trx.primary_salesrep_id -- SALESREP_ID
2744 ,trx.bill_to_site_use_id -- BILL_SITE_USE_ID
2745 ,trx.paying_site_use_id -- PAYING_SITE_USE_ID
2746 ,trx.sold_to_site_use_id -- SOLD_SITE_USE_ID
2747 ,trx.ship_to_site_use_id -- SHIP_SITE_USE_ID
2748 ,trx.bill_to_customer_id -- BILL_CUSTOMER_ID
2749 ,trx.paying_customer_id -- PAYING_CUSTOMER_ID
2750 ,trx.sold_to_customer_id -- SOLD_CUSTOMER_ID
2751 ,trx.ship_to_customer_id -- SHIP_CUSTOMER_ID
2752 ,trx.remit_to_address_id -- REMIT_ADDRESS_ID
2753 ,'Y' -- SELECT_FLAG
2754 ,'H' -- LEVEL_FLAG
2755 ,ctlgd.code_combination_id -- PAIRED_CCID
2756 ,'INVOICE' -- EVENT_CLASS_CODE
2757 FROM ra_customer_trx_all trx,
2758 xla_events_gt gt,
2759 ra_cust_trx_line_gl_dist_all ctlgd,
2760 jl_br_ar_collection_docs_all cd,
2761 jl_br_ar_coll_occ_docs_h_v jlh
2762 WHERE gt.entity_code = 'JL_BR_AR_COLL_DOC_OCCS'
2763 AND gt.application_id = l_application_id
2764 AND jlh.jlbr_document_id = gt.source_id_int_1
2765 AND jlh.jlbr_occurrence_id = gt.source_id_int_2
2766 AND cd.document_id = jlh.jlbr_document_id
2767 AND trx.customer_trx_id = cd.customer_trx_id
2768 AND trx.customer_trx_id = ctlgd.customer_trx_id
2769 AND ctlgd.account_class = 'REC'
2770 AND ctlgd.account_set_flag = 'N';
2771
2772 EXCEPTION
2773 WHEN NO_DATA_FOUND THEN
2774 NULL;
2775 WHEN OTHERS THEN
2776 IF (G_LEVEL_EXCEPTION >= G_CURRENT_RUNTIME_LEVEL ) THEN
2777 FND_LOG.STRING(G_LEVEL_EXCEPTION, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.load_occurrences_header_data','Exception in FUNCTION load_occurrences_header_data');
2778 NULL;
2779 END IF;
2780 FND_MESSAGE.SET_NAME('FND', 'FND_GENERIC_MESSAGE');
2781 FND_MESSAGE.SET_TOKEN('MESSAGE' ,
2782 'Procedure :jl_br_ar_bank_acct_pkg.load_occurrences_header_data'||
2783 'Error :'||SQLERRM);
2784 FND_MSG_PUB.ADD;
2785
2786 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2787 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.load_occurrences_header_data','End of procedure');
2788 END IF;
2789 END load_occurrences_header_data;
2790
2791
2792 /*========================================================================
2793 | PUBLIC FUNCTION Check_If_Upgrade_Occs
2794 |
2795 | DESCRIPTION
2796 | To be used only by the on-demand SLA upgrade program of AR to check
2797 | if Brazilian Occurrences Upgrade is to be executed or not
2798 |
2799 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2800 |
2801 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2802 |
2803 | PARAMETERS
2804 |
2805 | KNOWN ISSUES
2806 |
2807 | NOTES
2808 |
2809 | MODIFICATION HISTORY
2810 | Date Author Description of Changes
2811 *=======================================================================*/
2812
2813 FUNCTION check_if_upgrade_occs RETURN BOOLEAN IS
2814
2815 dummy NUMBER;
2816
2817 BEGIN
2818 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2819 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.check_if_upgrade_occs','Start of procedure check_if_upgrade_occs');
2820 END IF;
2821 SELECT 1 INTO dummy
2822 FROM ar_system_parameters_all
2823 WHERE global_attribute_category IS NOT NULL
2824 AND global_attribute_category = 'JL.BR.ARXSYSPA.Additional Info'
2825 AND rownum = 1;
2826
2827 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2828 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.check_if_upgrade_occs','dummy='||dummy);
2829 END IF;
2830 RETURN TRUE;
2831
2832 EXCEPTION
2833 WHEN NO_DATA_FOUND THEN
2834 RETURN FALSE;
2835
2836 END check_if_upgrade_occs;
2837
2838 /*========================================================================
2839 | PUBLIC PROCEDURE Upgrade_Mc_Occurrences
2840 |
2841 | DESCRIPTION
2842 | Upgrades JL MRC records to SLA Archetecture
2843 |
2844 | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
2845 |
2846 | CALLS PROCEDURES/FUNCTIONS (local to this package body)
2847 |
2848 | PARAMETERS
2849 |
2850 | KNOWN ISSUES
2851 |
2852 | NOTES
2853 |
2854 | MODIFICATION HISTORY
2855 | Date Author Description of Changes
2856 | 21-SEP-2005 JVARKEY Created
2857 | 29-SEP-2005 SSAKAMUR Added overloaded version for
2858 | on-demand upgrade
2859 *=======================================================================*/
2860
2861 -- Called directly from downtime upgrade script
2862
2863 PROCEDURE UPGRADE_MC_OCCURRENCES(
2864 l_table_owner IN VARCHAR2,
2865 l_table_name IN VARCHAR2,
2866 l_script_name IN VARCHAR2,
2867 l_worker_id IN VARCHAR2,
2868 l_num_workers IN VARCHAR2,
2869 l_batch_size IN VARCHAR2,
2870 l_batch_id IN NUMBER,
2871 l_action_flag IN VARCHAR2) IS
2872
2873 l_return_status VARCHAR2(30);
2874
2875 BEGIN
2876 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2877 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPGRADE_MC_OCCURRENCES','Start of procedure UPGRADE_MC_OCCURRENCES');
2878 END IF;
2879 UPGRADE_MC_OCCURRENCES(
2880 l_table_owner,
2881 l_table_name,
2882 l_script_name,
2883 l_worker_id,
2884 l_num_workers,
2885 l_batch_size,
2886 l_batch_id,
2887 l_action_flag,
2888 l_return_status);
2889
2890 END Upgrade_Mc_Occurrences;
2891
2892
2893 -- Called Directly from on-demand upgrade program
2894
2895 PROCEDURE UPGRADE_MC_OCCURRENCES(
2896 l_table_owner IN VARCHAR2,
2897 l_table_name IN VARCHAR2,
2898 l_script_name IN VARCHAR2,
2899 l_worker_id IN VARCHAR2,
2900 l_num_workers IN VARCHAR2,
2901 l_batch_size IN VARCHAR2,
2902 l_batch_id IN NUMBER,
2903 l_action_flag IN VARCHAR2,
2904 x_return_status OUT NOCOPY VARCHAR2) IS
2905
2906 l_start_rowid rowid;
2907 l_end_rowid rowid;
2908 l_any_rows_to_process boolean;
2909 l_rows_processed number := 0;
2910
2911 BEGIN
2912 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2913 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPGRADE_MC_OCCURRENCES','Start of procedure UPGRADE_MC_OCCURRENCES');
2914 END IF;
2915 IF l_action_flag = 'R' THEN
2916 x_return_status := FND_API.G_RET_STS_SUCCESS;
2917 END IF;
2918
2919 /* ------ Initialize the rowid ranges ------ */
2920 ad_parallel_updates_pkg.initialize_rowid_range(
2921 ad_parallel_updates_pkg.ROWID_RANGE,
2922 l_table_owner,
2923 l_table_name,
2924 l_script_name,
2925 l_worker_id,
2926 l_num_workers,
2927 l_batch_size, 0);
2928
2929 /* ------ Get rowid ranges ------ */
2930 ad_parallel_updates_pkg.get_rowid_range(
2931 l_start_rowid,
2932 l_end_rowid,
2933 l_any_rows_to_process,
2934 l_batch_size,
2935 TRUE);
2936
2937 WHILE ( l_any_rows_to_process = TRUE )
2938 LOOP
2939
2940 l_rows_processed := 0;
2941
2942 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
2943 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPGRADE_MC_OCCURRENCES','Create the Journal Entry Headers, insert into XLA_AE_HEADERS, ');
2944 END IF;
2945 --------------------------------------
2946 -- Create the Journal Entry Headers --
2947 --------------------------------------
2948 INSERT ALL
2949 WHEN 1 = 1 THEN
2950 INTO XLA_AE_HEADERS
2951 (
2952 AE_HEADER_ID,
2953 APPLICATION_ID,
2954 LEDGER_ID,
2955 ENTITY_ID,
2956 EVENT_ID,
2957 EVENT_TYPE_CODE,
2958 ACCOUNTING_DATE,
2959 PERIOD_NAME,
2960 JE_CATEGORY_NAME,
2961 GL_TRANSFER_STATUS_CODE,
2962 GL_TRANSFER_DATE,
2963 GROUP_ID,
2964 ACCOUNTING_ENTRY_STATUS_CODE,
2965 ACCOUNTING_ENTRY_TYPE_CODE,
2966 AMB_CONTEXT_CODE,
2967 PRODUCT_RULE_TYPE_CODE,
2968 PRODUCT_RULE_CODE,
2969 PRODUCT_RULE_VERSION,
2970 DESCRIPTION,
2971 BUDGET_VERSION_ID,
2972 FUNDS_STATUS_CODE,
2973 ENCUMBRANCE_TYPE_ID,
2974 BALANCE_TYPE_CODE,
2975 REFERENCE_DATE,
2976 COMPLETED_DATE,
2977 PACKET_ID,
2978 ACCOUNTING_BATCH_ID,
2979 DOC_SEQUENCE_ID,
2980 DOC_SEQUENCE_VALUE,
2981 DOC_CATEGORY_CODE,
2982 CLOSE_ACCT_SEQ_ASSIGN_ID,
2983 CLOSE_ACCT_SEQ_VERSION_ID,
2984 CLOSE_ACCT_SEQ_VALUE,
2985 COMPLETION_ACCT_SEQ_ASSIGN_ID,
2986 COMPLETION_ACCT_SEQ_VERSION_ID,
2987 COMPLETION_ACCT_SEQ_VALUE,
2988 ATTRIBUTE_CATEGORY,
2989 ATTRIBUTE1,
2990 ATTRIBUTE2,
2991 ATTRIBUTE3,
2992 ATTRIBUTE4,
2993 ATTRIBUTE5,
2994 ATTRIBUTE6,
2995 ATTRIBUTE7,
2996 ATTRIBUTE8,
2997 ATTRIBUTE9,
2998 ATTRIBUTE10,
2999 ATTRIBUTE11,
3000 ATTRIBUTE12,
3001 ATTRIBUTE13,
3002 ATTRIBUTE14,
3003 ATTRIBUTE15,
3004 CREATION_DATE,
3005 CREATED_BY,
3006 LAST_UPDATE_DATE,
3007 LAST_UPDATED_BY,
3008 LAST_UPDATE_LOGIN,
3009 PROGRAM_UPDATE_DATE,
3010 PROGRAM_APPLICATION_ID,
3011 PROGRAM_ID,
3012 REQUEST_ID,
3013 DOC_SEQUENCE_VERSION_ID,
3014 DOC_SEQUENCE_ASSIGN_ID,
3015 UPG_BATCH_ID,
3016 UPG_SOURCE_APPLICATION_ID,
3017 UPG_VALID_FLAG,
3018 ZERO_AMOUNT_FLAG,
3019 PARENT_AE_HEADER_ID,
3020 PARENT_AE_LINE_NUM
3021 )
3022 VALUES
3023 (
3024 xla_ae_headers_s.nextval,
3025 222,
3026 sob_id,
3027 entity_id,
3028 event_id,
3029 event_type_code,
3030 account_date,
3031 period_name,
3032 category_name,
3033 'Y',
3034 gl_transfer_date,
3035 null,
3036 'F',
3037 'STANDARD',
3038 'DEFAULT',
3039 null,
3040 null,
3041 null,
3042 null,
3043 null,
3044 null,
3045 null,
3046 'A',
3047 null,
3048 null,
3049 null,
3050 null,
3051 null,
3052 null,
3053 null,
3054 null,
3055 null,
3056 null,
3057 null,
3058 null,
3059 null,
3060 null,
3061 null,
3062 null,
3063 null,
3064 null,
3065 null,
3066 null,
3067 null,
3068 null,
3069 null,
3070 null,
3071 null,
3072 null,
3073 null,
3074 null,
3075 null,
3076 sysdate,
3077 2,
3078 sysdate,
3079 2,
3080 -2005,
3081 sysdate,
3082 222,
3083 -2005,
3084 null,
3085 null,
3086 null,
3087 batch_id,
3088 222,
3089 null,
3090 'N' ,
3091 null,
3092 null)
3093 SELECT
3094 l_batch_id AS batch_id,
3095 sob_id AS sob_id,
3096 entity_id AS entity_id,
3097 event_id AS event_id,
3098 event_type_code AS event_type_code,
3099 account_date AS account_date,
3100 period_name AS period_name,
3101 category_name AS category_name,
3102 gl_transfer_date AS gl_transfer_date
3103 FROM
3104 (select /*+ ordered use_nl(mcod,cd,ct,lgr,map,gps,dist,ps,dl,hdr)
3105 index(DL,XLA_DISTRIBUTION_LINKS_N1) index(HDR,XLA_AE_HEADERS_U1) */
3106 hdr.ae_header_id ae_header_id,
3107 hdr.entity_id entity_id,
3108 hdr.event_id event_id,
3109 hdr.event_type_code event_type_code,
3110 hdr.accounting_date account_date,
3111 hdr.period_name period_name,
3112 hdr.je_category_name category_name,
3113 hdr.gl_transfer_date gl_transfer_date,
3114 mcod.set_of_books_id sob_id
3115 --
3116 from
3117 jl_br_ar_occurrence_docs_all od,
3118 jl_br_ar_mc_occ_docs mcod,
3119 jl_br_ar_collection_docs_all cd,
3120 ra_customer_trx_all ct,
3121 gl_ledgers lgr,
3122 gl_date_period_map map,
3123 gl_period_statuses gps,
3124 jl_br_ar_distributns_all dist,
3125 ar_mc_payment_schedules ps,
3126 xla_distribution_links dl,
3127 xla_ae_headers hdr
3128 --
3129 where od.rowid >= l_start_rowid
3130 and od.rowid <= l_end_rowid
3131 --
3132 and mcod.occurrence_id = od.occurrence_id
3133 and mcod.gl_posted_date is not null
3134 --
3135 and cd.document_id = od.document_id
3136 --
3137 and ct.customer_trx_id = cd.customer_trx_id
3138 --
3139 and lgr.ledger_id = ct.set_of_books_id
3140 --
3141 and map.period_set_name = lgr.period_set_name
3142 and map.period_type = lgr.accounted_period_type
3143 and (map.accounting_date = hdr.accounting_date
3144 OR (cd.document_status NOT IN ('CANCELED','PARTIALLY_RECEIVED','REFUSED','TOTALLY_RECEIVED','WRITTEN_OFF')
3145 AND hdr.event_type_code IN ('REMIT_COLL_DOC' ,'REMIT_FACT_DOC')
3146 AND od.occurrence_status <> 'CANCELED'))
3147
3148 --
3149 and gps.application_id = 222
3150 and gps.period_name = map.period_name
3151 and gps.set_of_books_id = ct.set_of_books_id
3152 and gps.migration_status_code = 'P'
3153 --
3154 and dist.occurrence_id = od.occurrence_id
3155 --
3156 and ps.payment_schedule_id = cd.payment_schedule_id
3157 and ps.set_of_books_id = mcod.set_of_books_id
3158 --
3159 and dl.source_distribution_id_num_1 = dist.distribution_id
3160 and dl.source_distribution_type = 'JLBR_AR_DIST'
3161 and dl.application_id = 222
3162 --
3163 and hdr.ae_header_id = dl.ae_header_id
3164 and hdr.application_id = 222
3165 and hdr.ledger_id = ct.set_of_books_id
3166 --
3167 and NOT EXISTS(select 'Y' from xla_ae_headers hdr1
3168 where hdr1.application_id = 222
3169 and hdr1.ledger_id = mcod.set_of_books_id
3170 and hdr1.entity_id = hdr.entity_id
3171 and hdr1.event_id = hdr.event_id
3172 and hdr1.event_type_code = hdr.event_type_code)
3173 --
3174 group by
3175 hdr.ae_header_id,
3176 hdr.entity_id,
3177 hdr.event_id,
3178 hdr.event_type_code,
3179 hdr.accounting_date,
3180 hdr.period_name,
3181 hdr.je_category_name,
3182 hdr.gl_transfer_date,
3183 mcod.set_of_books_id);
3184
3185 -----------------------------------------------------------
3186 -- Create the Journal Entry Lines and Distribution Links --
3187 -----------------------------------------------------------
3188 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3189 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPGRADE_MC_OCCURRENCES','Create the Journal Entry Lines and Distribution Links, insert into XLA_AE_LINES');
3190 END IF;
3191 INSERT ALL
3192 WHEN 1 = 1 THEN
3193 INTO XLA_AE_LINES
3194 (
3195 AE_HEADER_ID,
3196 AE_LINE_NUM,
3197 DISPLAYED_LINE_NUMBER,
3198 APPLICATION_ID,
3199 CODE_COMBINATION_ID,
3200 GL_TRANSFER_MODE_CODE,
3201 ACCOUNTED_CR ,
3202 ACCOUNTED_DR,
3203 ENTERED_CR,
3204 ENTERED_DR ,
3205 ACCOUNTING_CLASS_CODE,
3206 CURRENCY_CODE,
3207 CURRENCY_CONVERSION_DATE ,
3208 CURRENCY_CONVERSION_RATE ,
3209 CURRENCY_CONVERSION_TYPE ,
3210 DESCRIPTION,
3211 GL_SL_LINK_TABLE,
3212 GL_SL_LINK_ID ,
3213 PARTY_ID ,
3214 PARTY_SITE_ID,
3215 PARTY_TYPE_CODE ,
3216 STATISTICAL_AMOUNT,
3217 USSGL_TRANSACTION_CODE,
3218 JGZZ_RECON_REF ,
3219 CONTROL_BALANCE_FLAG ,
3220 ANALYTICAL_BALANCE_FLAG ,
3221 ATTRIBUTE_CATEGORY,
3222 ATTRIBUTE1,
3223 ATTRIBUTE2,
3224 ATTRIBUTE3 ,
3225 ATTRIBUTE4 ,
3226 ATTRIBUTE5,
3227 ATTRIBUTE6,
3228 ATTRIBUTE7,
3229 ATTRIBUTE8 ,
3230 ATTRIBUTE9 ,
3231 ATTRIBUTE10 ,
3232 ATTRIBUTE11 ,
3233 ATTRIBUTE12,
3234 ATTRIBUTE13 ,
3235 ATTRIBUTE14,
3236 ATTRIBUTE15 ,
3237 CREATION_DATE,
3238 CREATED_BY,
3239 LAST_UPDATE_DATE,
3240 LAST_UPDATED_BY,
3241 LAST_UPDATE_LOGIN ,
3242 PROGRAM_UPDATE_DATE ,
3243 PROGRAM_APPLICATION_ID ,
3244 PROGRAM_ID ,
3245 REQUEST_ID ,
3246 UPG_BATCH_ID,
3247 UPG_TAX_REFERENCE_ID1 ,
3248 UPG_TAX_REFERENCE_ID2 ,
3249 UPG_TAX_REFERENCE_ID3,
3250 UNROUNDED_ACCOUNTED_CR ,
3251 UNROUNDED_ACCOUNTED_DR,
3252 GAIN_OR_LOSS_FLAG,
3253 UNROUNDED_ENTERED_CR,
3254 UNROUNDED_ENTERED_DR ,
3255 SUBSTITUTED_CCID ,
3256 BUSINESS_CLASS_CODE)
3257 VALUES
3258 (
3259 header_id,
3260 line_num,
3261 line_num,
3262 222,
3263 ccid,
3264 'D' ,
3265 acctd_amount_cr ,
3266 acctd_amount_dr,
3267 amount_cr,
3268 amount_dr ,
3269 accounting_class_code,
3270 currency_code,
3271 conversion_date,
3272 conversion_rate,
3273 conversion_type,
3274 description,
3275 null,
3276 null,
3277 party_id,
3278 party_site_id ,
3279 'C',
3280 NULL ,
3281 NULL,
3282 NULL,
3283 control_balance_flag,
3284 NULL,
3285 NULL,
3286 NULL,
3287 NULL,
3288 NULL,
3289 NULL,
3290 NULL,
3291 NULL,
3292 NULL,
3293 NULL,
3294 NULL,
3295 NULL,
3296 NULL,
3297 NULL,
3298 NULL,
3299 NULL,
3300 NULL,
3301 sysdate,
3302 2,
3303 sysdate,
3304 2,
3305 -2005,
3306 sysdate,
3307 222,
3308 -2005,
3309 null,
3310 batch_id,
3311 null,
3312 null,
3313 null,
3314 acctd_amount_cr,
3315 acctd_amount_dr,
3316 'N' ,
3317 amount_cr,
3318 amount_dr,
3319 null,
3320 null
3321 )
3322 WHEN 1 = 1 THEN
3323 INTO XLA_DISTRIBUTION_LINKS
3324 (
3325 APPLICATION_ID,
3326 EVENT_ID,
3327 AE_HEADER_ID,
3328 AE_LINE_NUM,
3329 ACCOUNTING_LINE_CODE,
3330 ACCOUNTING_LINE_TYPE_CODE ,
3331 REF_AE_HEADER_ID,
3332 REF_TEMP_LINE_NUM,
3333 SOURCE_DISTRIBUTION_TYPE,
3334 SOURCE_DISTRIBUTION_ID_CHAR_1 ,
3335 SOURCE_DISTRIBUTION_ID_CHAR_2 ,
3336 SOURCE_DISTRIBUTION_ID_CHAR_3 ,
3337 SOURCE_DISTRIBUTION_ID_CHAR_4 ,
3338 SOURCE_DISTRIBUTION_ID_CHAR_5 ,
3339 SOURCE_DISTRIBUTION_ID_NUM_1 ,
3340 SOURCE_DISTRIBUTION_ID_NUM_2 ,
3341 SOURCE_DISTRIBUTION_ID_NUM_3 ,
3342 SOURCE_DISTRIBUTION_ID_NUM_4 ,
3343 SOURCE_DISTRIBUTION_ID_NUM_5 ,
3344 MERGE_DUPLICATE_CODE,
3345 TAX_LINE_REF_ID,
3346 TAX_SUMMARY_LINE_REF_ID ,
3347 TAX_REC_NREC_DIST_REF_ID,
3348 STATISTICAL_AMOUNT,
3349 TEMP_LINE_NUM,
3350 REF_EVENT_ID ,
3351 LINE_DEFINITION_OWNER_CODE,
3352 LINE_DEFINITION_CODE ,
3353 EVENT_CLASS_CODE,
3354 EVENT_TYPE_CODE,
3355 UPG_BATCH_ID ,
3356 CALCULATE_ACCTD_AMTS_FLAG,
3357 CALCULATE_G_L_AMTS_FLAG ,
3358 ROUNDING_CLASS_CODE ,
3359 DOCUMENT_ROUNDING_LEVEL ,
3360 UNROUNDED_ENTERED_DR ,
3361 UNROUNDED_ENTERED_CR ,
3362 DOC_ROUNDING_ENTERED_AMT ,
3363 DOC_ROUNDING_ACCTD_AMT ,
3364 UNROUNDED_ACCOUNTED_DR ,
3365 UNROUNDED_ACCOUNTED_CR
3366 )
3367 VALUES
3368 (
3369 222,
3370 event_id,
3371 header_id,
3372 line_num,
3373 accounting_class_code,
3374 'C',
3375 ref_header_id,
3376 null,
3377 'JLBR_AR_DIST',
3378 null,
3379 null,
3380 null ,
3381 null,
3382 null,
3383 distribution_id,
3384 null,
3385 null,
3386 null,
3387 null,
3388 'N',
3389 null,
3390 null,
3391 null,
3392 null,
3393 line_num,
3394 ref_event_id,
3395 null,
3396 null,
3397 event_class_code,
3398 event_type_code,
3399 batch_id,
3400 null,
3401 null,
3402 null,
3403 null,
3404 amount_dr,
3405 amount_cr,
3406 null,
3407 null,
3408 acctd_amount_dr,
3409 acctd_amount_cr
3410 )
3411 SELECT
3412 l_batch_id AS batch_id,
3413 header_id AS header_id,
3414 ref_header_id AS ref_header_id,
3415 distribution_id AS distribution_id,
3416 event_id AS event_id,
3417 ref_event_id AS ref_event_id,
3418 ccid AS ccid,
3419 amount_dr AS amount_dr,
3420 amount_cr AS amount_cr,
3421 acctd_amount_dr AS acctd_amount_dr,
3422 acctd_amount_cr AS acctd_amount_cr,
3423 accounting_class_code AS accounting_class_code,
3424 currency_code AS currency_code,
3425 conversion_rate AS conversion_rate,
3426 conversion_date AS conversion_date,
3427 conversion_type AS conversion_type,
3428 description AS description,
3429 party_id AS party_id,
3430 party_site_id AS party_site_id,
3431 control_balance_flag AS control_balance_flag,
3432 event_type_code AS event_type_code,
3433 event_class_code AS event_class_code,
3434 sob_id AS sob_id,
3435 RANK() OVER (PARTITION BY event_id, ref_header_id,sob_id
3436 ORDER BY distribution_id, amount_dr) AS line_num
3437 FROM
3438 (select /*+ ordered use_nl(mcod,cd,ct,lgr,map,gps,dist,ps,dl,lin,hdr,hdr1,lgr1)
3439 index(DL,XLA_DISTRIBUTION_LINKS_N1) index(LIN,XLA_AE_LINES_U1) index(HDR,XLA_AE_HEADERS_U1) index(HDR1,XLA_AE_HEADERS_N2) */
3440 hdr1.ae_header_id header_id,
3441 ref.ae_header_id ref_header_id,
3442 ref.event_id ref_event_id,
3443 dist.distribution_id distribution_id,
3444 dl.event_id event_id,
3445 lin.code_combination_id ccid,
3446 dl.unrounded_entered_dr amount_dr,
3447 dl.unrounded_entered_cr amount_cr,
3448 decode(dl.unrounded_entered_dr,null,null,
3449 gl_mc_currency_pkg.CurrRound(
3450 dl.unrounded_entered_dr*NVL(ps.exchange_rate,1),
3451 lgr1.currency_code)) acctd_amount_dr,
3452 decode(dl.unrounded_entered_cr,null,null,
3453 gl_mc_currency_pkg.CurrRound(
3454 dl.unrounded_entered_cr*NVL(ps.exchange_rate,1),
3455 lgr1.currency_code)) acctd_amount_cr,
3456 lin.accounting_class_code accounting_class_code,
3457 dist.entered_currency_code currency_code,
3458 ps.exchange_rate conversion_rate,
3459 ps.exchange_date conversion_date,
3460 ps.exchange_rate_type conversion_type,
3461 lin.description description,
3462 lin.party_id party_id,
3463 lin.party_site_id party_site_id,
3464 lin.control_balance_flag control_balance_flag,
3465 dl.event_type_code event_type_code,
3466 dl.event_class_code event_class_code,
3467 mcod.set_of_books_id sob_id
3468 --
3469 from
3470 jl_br_ar_occurrence_docs_all od,
3471 jl_br_ar_mc_occ_docs mcod,
3472 jl_br_ar_collection_docs_all cd,
3473 ra_customer_trx_all ct,
3474 gl_ledgers lgr,
3475 gl_date_period_map map,
3476 gl_period_statuses gps,
3477 jl_br_ar_distributns_all dist,
3478 ar_mc_payment_schedules ps,
3479 xla_distribution_links dl,
3480 xla_ae_lines lin,
3481 xla_ae_headers hdr,
3482 xla_ae_headers hdr1,
3483 xla_ae_headers ref,
3484 xla_ae_headers ref1,
3485 gl_ledgers lgr1
3486 --
3487 where od.rowid >= l_start_rowid
3488 and od.rowid <= l_end_rowid
3489 --
3490 and mcod.occurrence_id = od.occurrence_id
3491 and mcod.gl_posted_date is not null
3492 --
3493 and cd.document_id = od.document_id
3494 --
3495 and ct.customer_trx_id = cd.customer_trx_id
3496 --
3497 and lgr.ledger_id = ct.set_of_books_id
3498 --
3499 and map.period_set_name = lgr.period_set_name
3500 and map.period_type = lgr.accounted_period_type
3501 and (map.accounting_date = hdr.accounting_date
3502 OR (cd.document_status NOT IN ('CANCELED','PARTIALLY_RECEIVED','REFUSED','TOTALLY_RECEIVED','WRITTEN_OFF')
3503 AND hdr.event_type_code IN ('REMIT_COLL_DOC' ,'REMIT_FACT_DOC')
3504 AND od.occurrence_status <> 'CANCELED'))
3505 --
3506 and gps.application_id = 222
3507 and gps.period_name = map.period_name
3508 and gps.set_of_books_id = ct.set_of_books_id
3509 and gps.migration_status_code = 'P'
3510 --
3511 and dist.occurrence_id = od.occurrence_id
3512 --
3513 and ps.payment_schedule_id = cd.payment_schedule_id
3514 and ps.set_of_books_id = mcod.set_of_books_id
3515 --
3516 and dl.source_distribution_id_num_1 = dist.distribution_id
3517 and dl.source_distribution_type = 'JLBR_AR_DIST'
3518 and dl.application_id = 222
3519 --
3520 and lin.application_id = 222
3521 and lin.ae_header_id = dl.ae_header_id
3522 and lin.ae_line_num = dl.ae_line_num
3523 --
3524 and hdr.ae_header_id = lin.ae_header_id
3525 and hdr.application_id = 222
3526 and hdr.ledger_id = ct.set_of_books_id
3527 --
3528 and hdr1.application_id = 222
3529 and hdr1.ledger_id = mcod.set_of_books_id
3530 and hdr1.entity_id = hdr.entity_id
3531 and hdr1.event_id = hdr.event_id
3532 and hdr1.event_type_code = hdr.event_type_code
3533 --
3534 and lgr1.ledger_id = mcod.set_of_books_id
3535 --
3536 and ref.application_id = 222
3537 and ref.ledger_id = mcod.set_of_books_id
3538 and ref.entity_id = ref1.entity_id
3539 and ref.event_id = ref1.event_id
3540 and ref.event_type_code = ref1.event_type_code
3541 --
3542 and ref1.ae_header_id = dl.ref_ae_header_id
3543 --
3544 and NOT EXISTS(select 'Y' from xla_distribution_links dl2, xla_ae_headers hdr2
3545 where dl2.application_id = 222
3546 and dl2.source_distribution_id_num_1 = dist.distribution_id
3547 and dl2.source_distribution_type = 'JLBR_AR_DIST'
3548 and hdr2.ae_header_id = dl2.ae_header_id
3549 and hdr2.application_id = 222
3550 and hdr2.ledger_id = mcod.set_of_books_id)
3551 );
3552
3553 l_rows_processed := SQL%ROWCOUNT;
3554
3555 ad_parallel_updates_pkg.processed_rowid_range(
3556 l_rows_processed,
3557 l_end_rowid);
3558
3559 commit;
3560
3561 ad_parallel_updates_pkg.get_rowid_range(
3562 l_start_rowid,
3563 l_end_rowid,
3564 l_any_rows_to_process,
3565 l_batch_size,
3566 FALSE);
3567
3568 l_rows_processed := 0 ;
3569
3570 END LOOP ; /* end of WHILE loop */
3571
3572 EXCEPTION
3573 WHEN NO_DATA_FOUND THEN
3574 /* IF l_action_flag = 'R' THEN
3575 FND_MESSAGE.SET_NAME('FND', 'FND_GENERIC_MESSAGE');
3576 FND_MESSAGE.SET_TOKEN('MASSAGE' ,'Exception NO_DATA_FOUND in UPGRADE_MC_OCCURRENCES ');
3577 FND_MSG_PUB.ADD;
3578 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3579 ELSE
3580 RAISE;
3581 END IF;
3582 */
3583 NULL;
3584
3585 WHEN OTHERS THEN
3586 IF l_action_flag = 'R' THEN
3587 FND_MESSAGE.SET_NAME('FND', 'FND_GENERIC_MESSAGE');
3588 FND_MESSAGE.SET_TOKEN('MASSAGE' ,'Exception OTHER in UPGRADE_MC_OCCURRENCES '||SQLERRM);
3589 FND_MSG_PUB.ADD;
3590 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
3591 ELSE
3592 RAISE;
3593 END IF;
3594 IF (G_LEVEL_STATEMENT >= G_CURRENT_RUNTIME_LEVEL ) THEN
3595 FND_LOG.STRING(G_LEVEL_STATEMENT, 'JL.plsql.JL_BR_AR_BANK_ACCT_PKG.UPGRADE_MC_OCCURRENCES','End UPGRADE_MC_OCCURRENCES ');
3596 END IF;
3597 END UPGRADE_MC_OCCURRENCES;
3598
3599 END JL_BR_AR_BANK_ACCT_PKG;