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