[Home] [Help]
PACKAGE BODY: APPS.CST_ACCRUAL_REC_PVT
Source
1 package body CST_ACCRUAL_REC_PVT AS
2 /* $Header: CSTACRHB.pls 120.24 2010/07/28 16:33:39 hyu ship $ */
3
4 G_PKG_NAME constant varchar2(30) := 'CST_Accrual_Rec_PVT';
5 G_LOG_HEADER constant varchar2(40) := 'cst.plsql.CST_Accrual_Rec_PVT';
6 G_LOG_LEVEL constant number := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7
8 -- Start of comments
9 -- API name : Get_Accounts
10 -- Type : Private
11 -- Pre-reqs : None.
12 -- Function : Get all the "default accounts" for a given operating unit.
13 -- Only distint accrual account IDs are return.
14 -- Parameters :
15 -- IN : p_ou_id IN NUMBER Required
16 -- Operating Unit Identifier.
17 -- OUT : x_count OUT NOCOPY NUBMER Required
18 -- Succes Indicator
19 -- 1 => Success
20 -- -1 => Failure
21 -- : x_err_num OUT NOCOPY NUMBER Required
22 -- Standard Error Parameter
23 -- : x_err_code OUT NOCOPY VARCHAR2 Required
24 -- Standard Error Parameter
25 -- : x_err_msg OUT NOCOPY VARCHAR2 Required
26 -- Standard Error Parameter
27 -- Version : Current version 1.0
28 -- Previous version 1.0
29 -- Initial version 1.0
30 -- End of comments
31 procedure get_accounts( p_ou_id in number,
32 x_count out nocopy number,
33 x_err_num out nocopy number,
34 x_err_code out nocopy varchar2,
35 x_err_msg out nocopy varchar2) is
36
37 l_api_version constant number := 1.0;
38 l_api_name constant varchar2(30) := 'get_accounts';
39 l_full_name constant varchar2(60) := g_pkg_name || '.' || l_api_name;
40 l_module constant varchar2(60) := 'cst.plsql.' || l_full_name;
41 l_uLog constant boolean := fnd_log.test(fnd_log.level_unexpected, l_module);
42 l_unLog constant boolean := l_uLog and (fnd_log.level_unexpected >= g_log_level);
43 l_errorLog constant boolean := l_uLog and (fnd_log.level_error >= g_log_level);
44 l_exceptionLog constant boolean := l_errorLog and (fnd_log.level_exception >= g_log_level);
45 l_pLog constant boolean := l_exceptionLog and (fnd_log.level_procedure >= g_log_level);
46 l_sLog constant boolean := l_pLog and (fnd_log.level_statement >= g_log_level);
47 l_stmt_num number;
48
49 begin
50
51 l_stmt_num := 5;
52
53 if(l_pLog) then
54 fnd_log.string(fnd_log.level_procedure, g_log_header || '.' || l_api_name ||
55 '.begin', 'get_accounts << ' || 'p_ou_id := ' || to_char(p_ou_id));
56 end if;
57
58 /* Print out the parameters to the Message Stack */
59 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Operating Unit: ' || to_char(p_ou_id));
60
61 l_stmt_num := 10;
62
63 INSERT into cst_accrual_accounts(
64 operating_unit_id,
65 accrual_account_id,
66 last_update_date,
67 last_updated_by,
68 last_update_login,
69 creation_date,
70 created_by,
71 request_id,
72 program_application_id,
73 program_id,
74 program_update_date
75 )
76 /* Grabs accrual accounts that have been part of a purchase order */
77 SELECT
78 t.org_id,
79 t.accrual_account_id,
80 sysdate, --last_update_date,
81 FND_GLOBAL.USER_ID, --last_updated_by,
82 FND_GLOBAL.USER_ID, --last_update_login,
83 sysdate, --creation_date,
84 FND_GLOBAL.USER_ID, --created_by,
85 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
86 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
87 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
88 sysdate
89 FROM
90 (select distinct p_ou_id org_id, paat.accrual_account_id accrual_account_id
91 from po_accrual_accounts_temp_all paat
92 where paat.org_id = p_ou_id
93 and not exists (
94 select 1
95 from cst_accrual_accounts caa
96 where caa.accrual_account_id = paat.accrual_account_id
97 and caa.operating_unit_id = p_ou_id)
98 and exists ( select 1
99 from financials_system_params_all fsp,
100 gl_sets_of_books gsb,
101 gl_code_combinations gcc
102 where gsb.set_of_books_id = fsp.set_of_books_id
103 and fsp.org_id = p_ou_id
104 and gcc.code_combination_id = paat.accrual_account_id
105 and gcc.chart_of_accounts_id = gsb.chart_of_accounts_id
106 )
107
108 union
109 /* Grabs the default Purchasing accrual account */
110 select distinct p_ou_id org_id, psp.accrued_code_combination_id accrual_account_id
111 from po_system_parameters_all psp
112 where psp.accrued_code_combination_id is not null
113 and psp.org_id = p_ou_id
114 and not exists (
115 select 1
116 from cst_accrual_accounts caa
117 where caa.accrual_account_id = psp.accrued_code_combination_id
118 and caa.operating_unit_id = p_ou_id)
119 and exists ( select 1
120 from financials_system_params_all fsp,
121 gl_sets_of_books gsb,
122 gl_code_combinations gcc
123 where gsb.set_of_books_id = fsp.set_of_books_id
124 and fsp.org_id = p_ou_id
125 and gcc.code_combination_id = psp.accrued_code_combination_id
126 and gcc.chart_of_accounts_id = gsb.chart_of_accounts_id
127 )
128
129 union
130 /* Grabs the accrual account for each inventory organization*/
131 select distinct p_ou_id org_id, mp.ap_accrual_account accrual_account_id
132 from mtl_parameters mp
133 where mp.ap_accrual_account is not null
134 and exists (
135 select 1
136 from hr_organization_information hoi
137 where hoi.organization_id = mp.organization_id
138 and hoi.org_information_context = 'Accounting Information'
139 and hoi.org_information3 = to_char(p_ou_id))
140 and not exists (
141 select 1
142 from cst_accrual_accounts caa
143 where caa.accrual_account_id = mp.ap_accrual_account
144 and caa.operating_unit_id = p_ou_id)
145 and exists ( select 1
146 from financials_system_params_all fsp,
147 gl_sets_of_books gsb,
148 gl_code_combinations gcc
149 where gsb.set_of_books_id = fsp.set_of_books_id
150 and fsp.org_id = p_ou_id
151 and gcc.code_combination_id = mp.ap_accrual_account
152 and gcc.chart_of_accounts_id = gsb.chart_of_accounts_id
153 )
154 ) t ;
155
156
157 x_count := sql%rowcount;
158
159 commit;
160
161 return;
162
163 exception
164 when others then
165 rollback;
166 x_count := -1;
167 x_err_num := SQLCODE;
168 x_err_code := NULL;
169 x_err_msg := 'CST_Accrual_Rec_PVT.get_accounts() ' || SQLERRM;
170 fnd_message.set_name('BOM','CST_UNEXPECTED');
171 fnd_message.set_token('TOKEN',SQLERRM);
172 if(l_unLog) then
173 fnd_log.message(fnd_log.level_unexpected, g_log_header || '.' || l_api_name
174 || '(' || to_char(l_stmt_num) || ')', FALSE);
175 end if;
176 fnd_msg_pub.add;
177 return;
178
179 end get_accounts;
180
181 -- Start of comments
182 -- API name : Flip_Flag
183 -- Type : Private
184 -- Pre-reqs : None.
185 -- Function : Sets the write_off_select_flag column in the appropriate
186 -- database tables to 'Y' or NULL.
187 -- Parameters :
188 -- IN : p_row_id IN VARCHAR2 Required
189 -- Row Identifier
190 -- : p_bit IN VARCHAR2 Required
191 -- Determines whether to set the column to 'Y' or NULL
192 -- FND_API.G_TRUE => 'Y'
193 -- FND_API.G_FALSE => NULL
194 -- : p_prog IN NUMBER Required
195 -- Codes which tables's write_off_select_flag column will be altered
196 -- 0 => cst_reconciliation_summary (AP and PO Form)
197 -- 1 => cst_misc_reconciliation (Miscellaneous Form)
198 -- 2 => cst_write_offs (View Write-Offs Form)
199 -- OUT : x_count OUT NOCOPY VARCHAR2 Required
200 -- Succes Indicator
201 -- FND_API.G_TRUE => Success
202 -- FND_API.G_FALSE => Failure
203 -- : x_err_num OUT NOCOPY NUMBER Required
204 -- Standard Error Parameter
205 -- : x_err_code OUT NOCOPY VARCHAR2 Required
206 -- Standard Error Parameter
207 -- : x_err_msg OUT NOCOPY VARCHAR2 Required
208 -- Standard Error Parameter
209 -- Version : Current version 1.0
210 -- Previous version 1.0
211 -- Initial version 1.0
212 -- End of comments
213 procedure flip_flag ( p_row_id in varchar2,
214 p_bit in varchar2,
215 p_prog in number,
216 x_count out nocopy varchar2,
217 x_err_num out nocopy number,
218 x_err_code out nocopy varchar2,
219 x_err_msg out nocopy varchar2) is
220
221 l_api_version constant number := 1.0;
222 l_api_name constant varchar2(30) := 'flip_flag';
223 l_full_name constant varchar2(60) := g_pkg_name || '.' || l_api_name;
224 l_module constant varchar2(60) := 'cst.plsql.' || l_full_name;
225 l_uLog constant boolean := fnd_log.test(fnd_log.level_unexpected, l_module);
226 l_unLog constant boolean := l_uLog and (fnd_log.level_unexpected >= g_log_level);
227 l_errorLog constant boolean := l_uLog and (fnd_log.level_error >= g_log_level);
228 l_exceptionLog constant boolean := l_errorLog and (fnd_log.level_exception >= g_log_level);
229 l_pLog constant boolean := l_exceptionLog and (fnd_log.level_procedure >= g_log_level);
230 l_sLog constant boolean := l_pLog and (fnd_log.level_statement >= g_log_level);
231 l_stmt_num number;
232
233 begin
234
235 l_stmt_num := 5;
236
237 if(l_pLog) then
238 fnd_log.string(fnd_log.level_procedure, g_log_header || '.' || l_api_name ||
239 '.begin', 'flip_flag << '
240 || 'p_row_id := ' || p_row_id
241 || 'p_bit := ' || p_bit
242 || 'p_prog := ' || to_char(p_prog));
243 end if;
244
245 /* Print out the parameters to the Message Stack */
246 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Row ID: ' || p_row_id);
247 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Flag Checked: ' || p_bit);
248 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Table Select: ' || to_char(p_prog));
249
250 l_stmt_num := 10;
251
252 if(fnd_api.to_boolean(p_bit)) then
253 --{
254 if(p_prog = 0) then
255 --{
256 update cst_reconciliation_summary
257 set write_off_select_flag = 'Y'
258 where rowid = p_row_id;
259 --}
260 elsif(p_prog = 1) then
261 --{
262 update cst_misc_reconciliation
263 set write_off_select_flag = 'Y'
264 where rowid = p_row_id;
265 --}
266 elsif(p_prog = 2) then
267 --{
268 update cst_write_offs
269 set write_off_select_flag = 'Y'
270 where rowid = p_row_id;
271 --}
272 end if; /* p_prog=0, p_prog=1, p_prog=2 */
273 --}
274 elsif(not fnd_api.to_boolean(p_bit)) then
275 --{
276 if(p_prog = 0) then
277 --{
278 update cst_reconciliation_summary
279 set write_off_select_flag = null
280 where rowid = p_row_id;
281 --}
282 elsif(p_prog = 1) then
283 --{
284 update cst_misc_reconciliation
285 set write_off_select_flag = null
286 where rowid = p_row_id;
287 --}
288 elsif(p_prog = 2) then
289 --{
290 update cst_write_offs
291 set write_off_select_flag = null
292 where rowid = p_row_id;
293 --}
294 end if; /* p_prog=0, p_prog=1, p_prog=2 */
295 --}
296 end if; /* fnd_api.to_boolean(p_bit) */
297
298 x_count := fnd_api.g_true;
299 return;
300
301 exception
302 when others then
303 --{
304 rollback;
305 x_count := fnd_api.g_false;
306 x_err_num := SQLCODE;
307 x_err_code := NULL;
308 x_err_msg := 'CST_Accrual_Rec_PVT.flip_flag() ' || SQLERRM;
309 fnd_message.set_name('BOM','CST_UNEXPECTED');
310 fnd_message.set_token('TOKEN',SQLERRM);
311 if(l_unLog) then
312 fnd_log.message(fnd_log.level_unexpected, g_log_header || '.' || l_api_name
313 || '(' || to_char(l_stmt_num) || ')', FALSE);
314 end if;
315 fnd_msg_pub.add;
316 return;
317 --}
318
319 end flip_flag;
320
321 -- Start of comments
322 -- API name : Calc_Age_In_Days
323 -- Type : Private
324 -- Pre-reqs : None.
325 -- Function : Calculates age in days using the profile option CST_ACCRUAL_AGE_IN_DAYS
326 -- Parameters :
327 -- IN : p_lrd IN DATE Required
328 -- Last Receipt Date
329 -- : p_lid IN DATE Required
330 -- Last Invoice Date
331 -- OUT : x_count OUT NOCOPY NUBMER Required
332 -- Age In Days Value
333 -- : x_err_num OUT NOCOPY NUMBER Required
334 -- Standard Error Parameter
335 -- : x_err_code OUT NOCOPY VARCHAR2 Required
336 -- Standard Error Parameter
337 -- : x_err_msg OUT NOCOPY VARCHAR2 Required
338 -- Standard Error Parameter
339 -- Version : Current version 1.0
340 -- Previous version 1.0
341 -- Initial version 1.0
342 -- End of comments
343 procedure calc_age_in_days ( p_lrd in date,
344 p_lid in date,
345 x_count out nocopy number,
346 x_err_num out nocopy number,
347 x_err_code out nocopy varchar2,
348 x_err_msg out nocopy varchar2) is
349
350 l_api_version constant number := 1.0;
351 l_api_name constant varchar2(30) := 'calc_age_in_days';
352 l_full_name constant varchar2(60) := g_pkg_name || '.' || l_api_name;
353 l_module constant varchar2(60) := 'cst.plsql.' || l_full_name;
354 l_uLog constant boolean := fnd_log.test(fnd_log.level_unexpected, l_module);
355 l_unLog constant boolean := l_uLog and (fnd_log.level_unexpected >= g_log_level);
356 l_errorLog constant boolean := l_uLog and (fnd_log.level_error >= g_log_level);
357 l_exceptionLog constant boolean := l_errorLog and (fnd_log.level_exception >= g_log_level);
358 l_pLog constant boolean := l_exceptionLog and (fnd_log.level_procedure >= g_log_level);
359 l_sLog constant boolean := l_pLog and (fnd_log.level_statement >= g_log_level);
360 l_stmt_num number;
361
362 begin
363
364 l_stmt_num := 5;
365
366 if(l_pLog) then
367 fnd_log.string(fnd_log.level_procedure, g_log_header || '.' || l_api_name ||
368 '.begin', 'procedure cal_age_in_days << '
369 || 'p_lrd := ' || to_char(p_lrd, 'YYYY/MM/DD HH24:MI:SS')
370 || 'p_lid := ' || to_char(p_lid, 'YYYY/MM/DD HH24:MI:SS'));
371 end if;
372
373 /* Print out the parameters to the Message Stack */
374 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Last Receipt Date: ' || to_char(p_lrd, 'YYYY/MM/DD HH24:MI:SS'));
375 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Last Invoice Date: ' || to_char(p_lid, 'YYYY/MM/DD HH24:MI:SS'));
376
377 l_stmt_num := 10;
378
379 /*
380 If profile option set to last receipt date, then use the last receipt date
381 to calculate Age in Days unless it is null, then use last invoice date
382 */
383 if(fnd_profile.value('CST_ACCRUAL_AGE_IN_DAYS') = '1') then
384 --{
385 if(p_lrd is not null) then
386 --{
387 x_count := trunc(sysdate - p_lrd);
388 return;
389 --}
390 else
391 --{
392 x_count := trunc(sysdate - p_lid);
393 return;
394 --}
395 end if; /* p_lrd is not null */
396 --}
397 /*
398 If profile option set to use the last activity date, the use the later of the
399 last receipt date or last invoice date to calculate Age in Days. If one of the
400 date values is null, use the non-null value.
401 */
402 else
403 --{
404 if(p_lid is null) then
405 --{
406 x_count := trunc(sysdate - p_lrd);
407 return;
408 --}
409 elsif(p_lrd is null) then
410 --{
411 x_count := trunc(sysdate - p_lid);
412 return;
413 --}
414 else
415 --{
416 if(p_lrd >= p_lid) then
417 --{
418 x_count := trunc(sysdate - p_lrd);
419 return;
420 --}
421 else
422 --{
423 x_count := trunc(sysdate - p_lid);
424 return;
425 --}
426 end if; /* p_lrd >= p_lid */
427 --}
428 end if; /* p_lid is null, p_lrd is null */
429 --}
430 end if; /* fnd_profile.value('CST_ACCRUAL_AGE_IN_DAYS') = 1 */
431
432 exception
433 when others then
434 --{
435 rollback;
436 x_count := -1;
437 x_err_num := SQLCODE;
438 x_err_code := NULL;
439 x_err_msg := 'CST_Accrual_Rec_PVT.calc_age_in_days() ' || SQLERRM;
440 fnd_message.set_name('BOM','CST_UNEXPECTED');
441 fnd_message.set_token('TOKEN',SQLERRM);
442 if(l_unLog) then
443 fnd_log.message(fnd_log.level_unexpected, g_log_header || '.' || l_api_name
444 || '(' || to_char(l_stmt_num) || ')', FALSE);
445 end if;
446 fnd_msg_pub.add;
447 return;
448 --}
449
450 end calc_age_in_days;
451
452 -- Start of comments
453 -- API name : Calc_Age_In_Days
454 -- Type : Private
455 -- Pre-reqs : None.
456 -- Function : Calculates age in days using the profile option CST_ACCRUAL_AGE_IN_DAYS
457 -- Parameters :
458 -- IN : p_lrd IN DATE Required
459 -- Last Receipt Date
460 -- : p_lid IN DATE Required
461 -- Last Invoice Date
462 -- RETURN : NUMBER
463 -- Age In Days Value
464 -- {x > -1} => Normal Completion
465 -- -1 => Error
466 -- Version : Current version 1.0
467 -- Previous version 1.0
468 -- Initial version 1.0
469 -- End of comments
470 function calc_age_in_days ( p_lrd in date,
471 p_lid in date) return number is
472
473 l_api_version constant number := 1.0;
474 l_api_name constant varchar2(30) := 'calc_age_in_days';
475 l_full_name constant varchar2(60) := g_pkg_name || '.' || l_api_name;
476 l_module constant varchar2(60) := 'cst.plsql.' || l_full_name;
477 l_uLog constant boolean := fnd_log.test(fnd_log.level_unexpected, l_module);
478 l_unLog constant boolean := l_uLog and (fnd_log.level_unexpected >= g_log_level);
479 l_errorLog constant boolean := l_uLog and (fnd_log.level_error >= g_log_level);
480 l_exceptionLog constant boolean := l_errorLog and (fnd_log.level_exception >= g_log_level);
481 l_pLog constant boolean := l_exceptionLog and (fnd_log.level_procedure >= g_log_level);
482 l_sLog constant boolean := l_pLog and (fnd_log.level_statement >= g_log_level);
483 l_stmt_num number;
484
485 begin
486
487 l_stmt_num := 5;
488
489 if(l_pLog) then
490 fnd_log.string(fnd_log.level_procedure, g_log_header || '.' || l_api_name ||
491 '.begin', 'function calc_age_in_days << '
492 || 'p_lrd := ' || to_char(p_lrd, 'YYYY/MM/DD HH24:MI:SS')
493 || 'p_lid := ' || to_char(p_lid, 'YYYY/MM/DD HH24:MI:SS'));
494 end if;
495
496 /* Print out the parameters to the Message Stack */
497 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Last Receipt Date: ' || to_char(p_lrd, 'YYYY/MM/DD HH24:MI:SS'));
498 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Last Invoice Date: ' || to_char(p_lid, 'YYYY/MM/DD HH24:MI:SS'));
499
500 l_stmt_num := 10;
501
502 /*
503 If profile option set to last receipt date, then use the last receipt date
504 to calculate Age in Days unless it is null, then use last invoice date
505 */
506 if(fnd_profile.value('CST_ACCRUAL_AGE_IN_DAYS') = '1') then
507 --{
508 if(p_lrd is not null) then
509 --{
510 return trunc(sysdate - p_lrd);
511 --}
512 else
513 --{
514 return trunc(sysdate - p_lid);
515 --}
516 end if; /* p_lrd is not null */
517 --}
518 /*
519 If profile option set to use the last activity date, the use the later of the
520 last receipt date or last invoice date to calculate Age in Days. If one of the
521 date values is null, use the non-null value.
522 */
523 else
524 --{
525 if(p_lid is null) then
526 --{
527 return trunc(sysdate - p_lrd);
528 --}
529 elsif(p_lrd is null) then
530 --{
531 return trunc(sysdate - p_lid);
532 --}
533 else
534 --{
535 if(p_lrd >= p_lid) then
536 --{
537 return trunc(sysdate - p_lrd);
538 --}
539 else
540 --{
541 return trunc(sysdate - p_lid);
542 --}
543 end if; /* p_lrd >= p_lid */
544 --}
545 end if; /* p_lid is null, p_lrd is null */
546 --}
547 end if; /* fnd_profile.value('CST_ACCRUAL_AGE_IN_DAYS') = 1 */
548
549 exception
550 when others then
551 --{
552 rollback;
553 fnd_message.set_name('BOM','CST_UNEXPECTED');
554 fnd_message.set_token('TOKEN',SQLERRM);
555 if(l_unLog) then
556 fnd_log.message(fnd_log.level_unexpected, g_log_header || '.' || l_api_name
557 || '(' || to_char(l_stmt_num) || ')', FALSE);
558 end if;
559 fnd_msg_pub.add;
560 return -1;
561 --}
562
563 end calc_age_in_days;
564
565 -- Start of comments
566 -- API name : Update_All
567 -- Type : Private
568 -- Pre-reqs : None.
569 -- Function : Sets all the write_off_select_flags to 'Y' in the appropriate
570 -- table whose rows are returned by the where clause
571 -- Parameters :
572 -- IN : p_where IN VARCHAR2 Required
573 -- Where Clause
574 -- : p_prog IN NUMBER Required
575 -- Codes which table's write_off_select_flag column will be altered
576 -- 0 => cst_reconciliation_summary (AP and PO Form)
577 -- 1 => cst_misc_reconciliation (Miscellaneous Form)
578 -- 2 => cst_write_offs (View Write-Offs Form)
579 -- : p_ou_id IN NUMBER Required
580 -- Operating Unit Identifier
581 -- OUT : x_out OUT NOCOPY NUBMER Required
582 -- Sum of distributions/transactions selected
583 -- : x_tot OUT NOCOPY NUMBER Required
584 -- Number of rows selected for update
585 -- : x_err_num OUT NOCOPY NUMBER Required
586 -- Standard Error Parameter
587 -- : x_err_code OUT NOCOPY VARCHAR2 Required
588 -- Standard Error Parameter
589 -- : x_err_msg OUT NOCOPY VARCHAR2 Required
590 -- Standard Error Parameter
591 -- Version : Current version 1.0
592 -- Previous version 1.0
593 -- Initial version 1.0
594 -- End of comments
595 procedure update_all ( p_where in varchar2,
596 p_prog in number,
597 p_ou_id in number,
598 x_out out nocopy number,
599 x_tot out nocopy number,
600 x_err_num out nocopy number,
601 x_err_code out nocopy varchar2,
602 x_err_msg out nocopy varchar2) is
603
604 l_api_version constant number := 1.0;
605 l_api_name constant varchar2(30) := 'update_all';
606 l_full_name constant varchar2(60) := g_pkg_name || '.' || l_api_name;
607 l_module constant varchar2(60) := 'cst.plsql.' || l_full_name;
608 l_uLog constant boolean := fnd_log.test(fnd_log.level_unexpected, l_module);
609 l_unLog constant boolean := l_uLog and (fnd_log.level_unexpected >= g_log_level);
610 l_errorLog constant boolean := l_uLog and (fnd_log.level_error >= g_log_level);
611 l_exceptionLog constant boolean := l_errorLog and (fnd_log.level_exception >= g_log_level);
612 l_pLog constant boolean := l_exceptionLog and (fnd_log.level_procedure >= g_log_level);
613 l_sLog constant boolean := l_pLog and (fnd_log.level_statement >= g_log_level);
614 l_stmt_num number;
615
616 begin
617
618 l_stmt_num := 5;
619
620 if(l_pLog) then
621 fnd_log.string(fnd_log.level_procedure, g_log_header || '.' || l_api_name ||
622 '.begin', 'update_all << '
623 || 'p_where := ' || p_where
624 || 'p_prog := ' || to_char(p_prog)
625 || 'p_ou := ' || to_char(p_ou_id));
626 end if;
627
628 /* Print out the parameters to the Message Stack */
629 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Where Clause: ' || SUBSTRB(p_where,10000));
630 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Table Select: ' || to_char(p_prog));
631 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Operating Unit: ' || to_char(p_ou_id));
632
633 l_stmt_num := 10;
634
635 execute immediate p_where;
636
637 l_stmt_num := 15;
638
639 if(p_prog = 0) then
640 --{
641 select count(*), sum(po_balance + ap_balance + write_off_balance)
642 into x_tot, x_out
643 from cst_reconciliation_summary
644 where operating_unit_id = p_ou_id
645 and write_off_select_flag = 'Y';
646 --}
647 elsif(p_prog = 1) then
648 --{
649 select count(*), sum(amount)
650 into x_tot, x_out
651 from cst_misc_reconciliation
652 where operating_unit_id = p_ou_id
653 and write_off_select_flag = 'Y';
654 --}
655 end if; /* p_prog = 0, p_prog = 1 */
656
657 return;
658
659 exception
660 when others then
661 --{
662 rollback;
663 x_tot := -1;
664 x_out := -1;
665 x_err_num := SQLCODE;
666 x_err_code := NULL;
667 x_err_msg := 'CST_Accrual_Rec_PVT.update_all() ' || SQLERRM;
668 fnd_message.set_name('BOM','CST_UNEXPECTED');
669 fnd_message.set_token('TOKEN',SQLERRM);
670 if(l_unLog) then
671 fnd_log.message(fnd_log.level_unexpected, g_log_header || '.' || l_api_name
672 || '(' || to_char(l_stmt_num) || ')', FALSE);
673 end if;
674 fnd_msg_pub.add;
675 return;
676 --}
677
678 end update_all;
679
680 -- Start of comments
681 -- API name : Insert_Misc_Data_All
682 -- Type : Private
683 -- Pre-reqs : None.
684 -- Function : Write-off transactions selected in the Miscellaneous
685 -- Accrual Write-Off Form in Costing tables. Proecedue will also generate
686 -- Write-Off events in SLA. At the end, all the written-off transactions are
687 -- removed from cst_misc_reconciliation.
688 -- Parameters :
689 -- IN : p_wo_date IN DATE Required
690 -- Write-Off Date
691 -- : p_off_id IN NUMBER Required
692 -- Offset Account
693 -- : p_rea_id IN NUMBER Optional
694 -- Write-Off Reason
695 -- : p_comments IN VARCHAR2 Optional
696 -- Write-Off Comments
697 -- : p_sob_id IN NUMBER Required
698 -- Ledger/Set of Books
699 -- : p_ou_id IN NUMBER Required
700 -- Operating Unit Identifier
701 -- OUT : x_count OUT NOCOPY NUBMER Required
702 -- Success Indicator
703 -- {x > 0} => Success
704 -- -1 => Failure
705 -- : x_err_num OUT NOCOPY NUMBER Required
706 -- Standard Error Parameter
707 -- : x_err_code OUT NOCOPY VARCHAR2 Required
708 -- Standard Error Parameter
709 -- : x_err_msg OUT NOCOPY VARCHAR2 Required
710 -- Standard Error Parameter
711 -- Version : Current version 1.0
712 -- Previous version 1.0
713 -- Initial version 1.0
714 -- End of comments
715 procedure insert_misc_data_all(
716 p_wo_date in date,
717 p_off_id in number,
718 p_rea_id in number,
719 p_comments in varchar2,
720 p_sob_id in number,
721 p_ou_id in number,
722 x_count out nocopy number,
723 x_err_num out nocopy number,
724 x_err_code out nocopy varchar2,
725 x_err_msg out nocopy varchar2) is
726
727 l_api_version constant number := 1.0;
728 l_api_name constant varchar2(30) := 'insert_misc_data_all';
729 l_full_name constant varchar2(60) := g_pkg_name || '.' || l_api_name;
730 l_module constant varchar2(60) := 'cst.plsql.' || l_full_name;
731 l_uLog constant boolean := fnd_log.test(fnd_log.level_unexpected, l_module);
732 l_unLog constant boolean := l_uLog and (fnd_log.level_unexpected >= g_log_level);
733 l_errorLog constant boolean := l_uLog and (fnd_log.level_error >= g_log_level);
734 l_exceptionLog constant boolean := l_errorLog and (fnd_log.level_exception >= g_log_level);
735 l_pLog constant boolean := l_exceptionLog and (fnd_log.level_procedure >= g_log_level);
736 l_sLog constant boolean := l_pLog and (fnd_log.level_statement >= g_log_level);
737 l_stmt_num number;
738 l_rows number;
739 l_le_id number;
740 /* Cursor to hold all select miscellaneous transactions*/
741 cursor c_wo(l_ou_id number) is
742 select po_accrual_write_offs_s.nextval l_wo_id,
743 accrual_account_id,
744 transaction_date,
745 amount,
746 entered_amount,
747 quantity,
748 currency_code,
749 currency_conversion_type,
750 currency_conversion_rate,
751 currency_conversion_date,
752 transaction_type_code,
753 invoice_distribution_id,
754 inventory_transaction_id,
755 po_distribution_id,
756 inventory_item_id,
757 vendor_id,
758 inventory_organization_id,
759 operating_unit_id,
760 last_update_date,
761 last_updated_by,
762 last_update_login,
763 creation_date,
764 created_by,
765 request_id,
766 program_application_id,
767 program_id,
768 program_update_date,
769 ae_header_id,
770 ae_line_num
771 from cst_misc_reconciliation
772 where operating_unit_id = l_ou_id
773 and write_off_select_flag = 'Y';
774
775 begin
776
777 l_stmt_num := 5;
778
779 if(l_pLog) then
780 fnd_log.string(fnd_log.level_procedure, g_log_header || '.' || l_api_name ||
781 '.begin', 'insert_misc_data_all << '
782 || 'p_wo_date := ' || to_char(p_wo_date, 'YYYY/MM/DD HH24:MI:SS')
783 || 'p_off_id := ' || to_char(p_off_id)
784 || 'p_rea_id := ' || to_char(p_rea_id)
785 || 'p_comments := ' || p_comments
786 || 'p_sob_id := ' || to_char(p_sob_id)
787 || 'p_ou_id := ' || to_char(p_ou_id));
788 end if;
789
790 /* Print out the parameters to the Message Stack */
791 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Write-Off Date: ' || to_char(p_wo_date, 'YYYY/MM/DD HH24:MI:SS'));
792 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Offset Account: ' || to_char(p_off_id));
793 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Write-Off Reason: ' || to_char(p_rea_id));
794 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Comments: ' || p_comments);
795 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Set of Books: ' || to_char(p_sob_id));
796 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Operating Unit: ' || to_char(p_ou_id));
797
798 l_stmt_num := 10;
799
800 /* Check whether any transactions have been selected for write-off */
801 select count(*)
802 into l_rows
803 from cst_misc_reconciliation
804 where operating_unit_id = p_ou_id
805 and write_off_select_flag = 'Y';
806
807 if(l_rows > 0) then
808 --{
809 l_stmt_num := 15;
810
811 for c_wo_rec in c_wo(p_ou_id) loop
812 --{
813 /*
814 If it is an inventory transaction, the Legal Entity will be derived
815 by the inventory organization ID, For miscellaneous invoices, the
816 Legal Entity will be derived from the AP Invoices table.
817 */
818 if(c_wo_rec.inventory_transaction_id is not null) then
819 --{
820 select org_information2
821 into l_le_id
822 from hr_organization_information
823 where organization_id = c_wo_rec.inventory_organization_id
824 and org_information_context = 'Accounting Information';
825 --}
826 else
827 --{
828 select apia.legal_entity_id
829 into l_le_id
830 from ap_invoices_all apia
831 ,(SELECT invoice_id
832 ,invoice_distribution_id
833 FROM ap_invoice_distributions_all
834 UNION
835 SELECT invoice_id
836 ,invoice_distribution_id
837 FROM ap_self_assessed_tax_dist_all ) aida
838 where aida.invoice_distribution_id = c_wo_rec.invoice_distribution_id
839 and apia.invoice_id = aida.invoice_id;
840 --}
841 end if; /* c_wo_rec.inventory_transaction_id is not null */
842
843 l_stmt_num := 20;
844
845 /* Insert necessary information into SLA events temp table */
846 insert into xla_events_int_gt
847 (
848 application_id,
849 ledger_id,
850 legal_entity_id,
851 entity_code,
852 source_id_int_1,
853 event_class_code,
854 event_type_code,
855 event_date,
856 event_status_code,
857 --BUG#7226250
858 security_id_int_2,
859 transaction_date,
860 reference_date_1,
861 transaction_number
862 )
863 values
864 (
865 707,
866 p_sob_id,
867 l_le_id,
868 'WO_ACCOUNTING_EVENTS',
869 c_wo_rec.l_wo_id,
870 'ACCRUAL_WRITE_OFF',
871 'ACCRUAL_WRITE_OFF',
872 p_wo_date,
873 XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
874 p_ou_id,
875 p_wo_date,
876 INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(p_wo_date,p_ou_id),
877 to_char(c_wo_rec.l_wo_id)
878 );
879
880 l_stmt_num := 25;
881
882 /*
883 Insert the selected miscellaneous transactions into
884 Costing's Write-Off tables
885 */
886 insert all
887 into cst_write_offs
888 (
889 write_off_id,
890 transaction_date,
891 accrual_account_id,
892 offset_account_id,
893 write_off_amount,
894 entered_amount,
895 currency_code,
896 currency_conversion_type,
897 currency_conversion_rate,
898 currency_conversion_date,
899 transaction_type_code,
900 invoice_distribution_id,
901 inventory_transaction_id,
902 po_distribution_id,
903 reason_id,
904 comments,
905 inventory_item_id,
906 vendor_id,
907 legal_entity_id,
908 operating_unit_id,
909 last_update_date,
910 last_updated_by,
911 last_update_login,
912 creation_date,
913 created_by,
914 request_id,
915 program_application_id,
916 program_id,
917 program_update_date
918 )
919 values
920 (
921 c_wo_rec.l_wo_id,
922 p_wo_date,
923 c_wo_rec.accrual_account_id,
924 p_off_id,
925 (-1) * c_wo_rec.amount,
926 (-1) * c_wo_rec.entered_amount,
927 c_wo_rec.currency_code,
928 c_wo_rec.currency_conversion_type,
929 c_wo_rec.currency_conversion_rate,
930 c_wo_rec.currency_conversion_date,
931 'WRITE OFF',
932 c_wo_rec.invoice_distribution_id,
933 c_wo_rec.inventory_transaction_id,
934 c_wo_rec.po_distribution_id,
935 p_rea_id,
936 p_comments,
937 c_wo_rec.inventory_item_id,
938 c_wo_rec.vendor_id,
939 l_le_id,
940 p_ou_id,
941 sysdate, --last_update_date,
942 FND_GLOBAL.USER_ID, --last_updated_by,
943 FND_GLOBAL.USER_ID, --last_update_login,
944 sysdate, --creation_date,
945 FND_GLOBAL.USER_ID, --created_by,
946 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
947 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
948 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
949 sysdate -- program_update_date
950 )
951 into cst_write_off_details
952 (
953 write_off_id,
954 transaction_date,
955 amount,
956 entered_amount,
957 quantity,
958 currency_code,
959 currency_conversion_type,
960 currency_conversion_rate,
961 currency_conversion_date,
962 transaction_type_code,
963 invoice_distribution_id,
964 inventory_transaction_id,
965 inventory_organization_id,
966 operating_unit_id,
967 last_update_date,
968 last_updated_by,
969 last_update_login,
970 creation_date,
971 created_by,
972 request_id,
973 program_application_id,
974 program_id,
975 program_update_date,
976 ae_header_id,
977 ae_line_num
978 )
979 values
980 (
981 c_wo_rec.l_wo_id,
982 c_wo_rec.transaction_date,
983 c_wo_rec.amount,
984 c_wo_rec.entered_amount,
985 c_wo_rec.quantity,
986 c_wo_rec.currency_code,
987 c_wo_rec.currency_conversion_type,
988 c_wo_rec.currency_conversion_rate,
989 c_wo_rec.currency_conversion_date,
990 c_wo_rec.transaction_type_code,
991 c_wo_rec.invoice_distribution_id,
992 c_wo_rec.inventory_transaction_id,
993 c_wo_rec.inventory_organization_id,
994 p_ou_id,
995 sysdate, --last_update_date,
996 FND_GLOBAL.USER_ID, --last_updated_by,
997 FND_GLOBAL.USER_ID, --last_update_login,
998 sysdate, --creation_date,
999 FND_GLOBAL.USER_ID, --created_by,
1000 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
1001 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
1002 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
1003 sysdate, --program_update_date,
1004 c_wo_rec.ae_header_id,
1005 c_wo_rec.ae_line_num
1006 )
1007 select c_wo_rec.l_wo_id,
1008 c_wo_rec.accrual_account_id,
1009 c_wo_rec.transaction_date,
1010 c_wo_rec.amount,
1011 c_wo_rec.entered_amount,
1012 c_wo_rec.quantity,
1013 c_wo_rec.currency_code,
1014 c_wo_rec.currency_conversion_type,
1015 c_wo_rec.currency_conversion_rate,
1016 c_wo_rec.currency_conversion_date,
1017 c_wo_rec.transaction_type_code,
1018 c_wo_rec.invoice_distribution_id,
1019 c_wo_rec.inventory_transaction_id,
1020 c_wo_rec.po_distribution_id,
1021 c_wo_rec.inventory_item_id,
1022 c_wo_rec.vendor_id,
1023 c_wo_rec.inventory_organization_id,
1024 c_wo_rec.operating_unit_id,
1025 c_wo_rec.ae_header_id,
1026 c_wo_rec.ae_line_num
1027 from cst_misc_reconciliation
1028 where rownum = 1;
1029 --}
1030 end loop; /* for c_wo_rec in c_wo(p_ou_id) */
1031
1032 l_stmt_num := 30;
1033
1034 /* Delete written-off transactions from Costing's Miscellaneous table */
1035 delete from cst_misc_reconciliation
1036 where operating_unit_id = p_ou_id
1037 and write_off_select_flag = 'Y';
1038
1039
1040 l_stmt_num := 35;
1041
1042 /*
1043 Call SLA's bulk events generator which uses the values previously
1044 inserted into SLA's event temp table
1045 */
1046 xla_events_pub_pkg.create_bulk_events(p_source_application_id => 201,
1047 p_application_id => 707,
1048 p_ledger_id => p_sob_id,
1049 p_entity_type_code => 'WO_ACCOUNTING_EVENTS');
1050
1051 commit;
1052 --}
1053 else
1054 --{
1055 x_count := -1;
1056 return;
1057 --}
1058 end if; /* l_rows > 0 */
1059
1060 x_count := l_rows;
1061 return;
1062
1063 exception
1064 when others then
1065 --{
1066 rollback;
1067 x_count := -1;
1068 x_err_num := SQLCODE;
1069 x_err_code := NULL;
1070 x_err_msg := 'CST_Accrual_Rec_PVT.insert_misc_data_all() ' || SQLERRM;
1071 fnd_message.set_name('BOM','CST_UNEXPECTED');
1072 fnd_message.set_token('TOKEN',SQLERRM);
1073 if(l_unLog) then
1074 fnd_log.message(fnd_log.level_unexpected, g_log_header || '.' || l_api_name
1075 || '(' || to_char(l_stmt_num) || ')', FALSE);
1076 end if;
1077 fnd_msg_pub.add;
1078 return;
1079 --}
1080 end insert_misc_data_all;
1081
1082 -- Start of comments
1083 -- API name : Insert_Appo_Data_All
1084 -- Type : Private
1085 -- Pre-reqs : None.
1086 -- Function : Write-off PO distributions selected in the AP and PO
1087 -- Accrual Write-Off Form in Costing tables. Proecedue will also generate
1088 -- Write-Off events in SLA. A single write-off event will be generated
1089 -- regardless of the number of transactions that make up the PO distribution.
1090 -- At the end, all the written-off PO distributions
1091 -- and individual AP and PO transactions are removed from
1092 -- cst_reconciliation_summary and cst_ap_po_reconciliation..
1093 -- Parameters :
1094 -- IN : p_wo_date IN DATE Required
1095 -- Write-Off Date
1096 -- : p_rea_id IN NUMBER Optional
1097 -- Write-Off Reason
1098 -- : p_comments IN VARCHAR2 Optional
1099 -- Write-Off Comments
1100 -- : p_sob_id IN NUMBER Required
1101 -- Ledger/Set of Books
1102 -- : p_ou_id IN NUMBER Required
1103 -- Operating Unit Identifier
1104 -- OUT : x_count OUT NOCOPY NUBMER Required
1105 -- Success Indicator
1106 -- {x > 0} => Success
1107 -- -1 => Failure
1108 -- : x_err_num OUT NOCOPY NUMBER Required
1109 -- Standard Error Parameter
1110 -- : x_err_code OUT NOCOPY VARCHAR2 Required
1111 -- Standard Error Parameter
1112 -- : x_err_msg OUT NOCOPY VARCHAR2 Required
1113 -- Standard Error Parameter
1114 -- Version : Current version 1.0
1115 -- Previous version 1.0
1116 -- Initial version 1.0
1117 -- End of comments
1118 procedure insert_appo_data_all(
1119 p_wo_date in date,
1120 p_rea_id in number,
1121 p_comments in varchar2,
1122 p_sob_id in number,
1123 p_ou_id in number,
1124 x_count out nocopy number,
1125 x_err_num out nocopy number,
1126 x_err_code out nocopy varchar2,
1127 x_err_msg out nocopy varchar2) is
1128
1129 l_api_version constant number := 1.0;
1130 l_api_name constant varchar2(30) := 'insert_appo_data_all';
1131 l_full_name constant varchar2(60) := g_pkg_name || '.' || l_api_name;
1132 l_module constant varchar2(60) := 'cst.plsql.' || l_full_name;
1133 l_uLog constant boolean := fnd_log.test(fnd_log.level_unexpected, l_module);
1134 l_unLog constant boolean := l_uLog and (fnd_log.level_unexpected >= g_log_level);
1135 l_errorLog constant boolean := l_uLog and (fnd_log.level_error >= g_log_level);
1136 l_exceptionLog constant boolean := l_errorLog and (fnd_log.level_exception >= g_log_level);
1137 l_pLog constant boolean := l_exceptionLog and (fnd_log.level_procedure >= g_log_level);
1138 l_sLog constant boolean := l_pLog and (fnd_log.level_statement >= g_log_level);
1139 l_stmt_num number;
1140 l_rows number;
1141 l_ent_sum number;
1142 l_off_id number;
1143 l_erv_id number;
1144 l_wo_cc varchar2(30);
1145 l_wo_ct varchar2(30);
1146 l_wo_cr number;
1147 l_wo_cd date;
1148
1149 /* Cusor to hold all the PO distributions selected in the AP and PO form*/
1150 cursor c_wo(l_ou_id number) is
1151 select po_accrual_write_offs_s.nextval l_wo_id,
1152 (po_balance + ap_balance + write_off_balance) l_tot_bal,
1153 po_distribution_id,
1154 accrual_account_id,
1155 destination_type_code,
1156 inventory_item_id,
1157 vendor_id,
1158 operating_unit_id,
1159 last_update_date,
1160 last_updated_by,
1161 last_update_login,
1162 creation_date,
1163 created_by,
1164 request_id,
1165 program_application_id,
1166 program_id,
1167 program_update_date
1168 from cst_reconciliation_summary
1169 where operating_unit_id = l_ou_id
1170 and write_off_select_flag = 'Y';
1171
1172 begin
1173
1174 l_stmt_num := 5;
1175
1176 if(l_pLog) then
1177 fnd_log.string(fnd_log.level_procedure, g_log_header || '.' || l_api_name ||
1178 '.begin', 'insert_appo_data_all << '
1179 || 'p_wo_date := ' || to_char(p_wo_date, 'YYYY/MM/DD HH24:MI:SS')
1180 || 'p_rea_id := ' || to_char(p_rea_id)
1181 || 'p_comments := ' || p_comments
1182 || 'p_sob_id := ' || to_char(p_sob_id)
1183 || 'p_ou_id := ' || to_char(p_ou_id));
1184 end if;
1185
1186 /* Print out the parameters to the Message Stack */
1187 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Write-Off Date: ' || to_char(p_wo_date, 'YYYY/MM/DD HH24:MI:SS'));
1188 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Write-Off Reason: ' || to_char(p_rea_id));
1189 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Comments: ' || p_comments);
1190 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Set of Books: ' || to_char(p_sob_id));
1191 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Operating Unit: ' || to_char(p_ou_id));
1192
1193 l_stmt_num := 10;
1194
1195 /* Make sure user selected PO distributions to write-off */
1196 select count(*)
1197 into l_rows
1198 from cst_reconciliation_summary
1199 where operating_unit_id = p_ou_id
1200 and write_off_select_flag = 'Y';
1201
1202 if(l_rows > 0) then
1203 --{
1204 l_stmt_num := 15;
1205
1206 for c_wo_rec in c_wo(p_ou_id) loop
1207 --{
1208 /* Insert necessary information into SLA events temp table */
1209 insert into xla_events_int_gt
1210 (
1211 application_id,
1212 ledger_id,
1213 entity_code,
1214 source_id_int_1,
1215 event_class_code,
1216 event_type_code,
1217 event_date,
1218 event_status_code,
1219 --BUG#7226250
1220 security_id_int_2,
1221 transaction_date,
1222 reference_date_1,
1223 transaction_number
1224 )
1225 values
1226 (
1227 707,
1228 p_sob_id,
1229 'WO_ACCOUNTING_EVENTS',
1230 c_wo_rec.l_wo_id,
1231 'ACCRUAL_WRITE_OFF',
1232 'ACCRUAL_WRITE_OFF',
1233 p_wo_date,
1234 XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
1235 p_ou_id,
1236 p_wo_date,
1237 INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(p_wo_date,p_ou_id),
1238 to_char(c_wo_rec.l_wo_id)
1239 );
1240
1241 l_stmt_num := 20;
1242
1243 /*
1244 Insert the individual AP and/or PO transactions into
1245 the write-off details table
1246 */
1247 insert into cst_write_off_details
1248 (
1249 write_off_id,
1250 transaction_date,
1251 amount,
1252 entered_amount,
1253 quantity,
1254 currency_code,
1255 currency_conversion_type,
1256 currency_conversion_rate,
1257 currency_conversion_date,
1258 transaction_type_code,
1259 rcv_transaction_id,
1260 invoice_distribution_id,
1261 write_off_transaction_id,
1262 inventory_organization_id,
1263 operating_unit_id,
1264 last_update_date,
1265 last_updated_by,
1266 last_update_login,
1267 creation_date,
1268 created_by,
1269 request_id,
1270 program_application_id,
1271 program_id,
1272 program_update_date,
1273 ae_header_id,
1274 ae_line_num
1275 )
1276 select c_wo_rec.l_wo_id,
1277 capr.transaction_date,
1278 capr.amount,
1279 capr.entered_amount,
1280 capr.quantity,
1281 capr.currency_code,
1282 capr.currency_conversion_type,
1283 capr.currency_conversion_rate,
1284 capr.currency_conversion_date,
1285 capr.transaction_type_code,
1286 capr.rcv_transaction_id,
1287 capr.invoice_distribution_id,
1288 capr.write_off_id,
1289 capr.inventory_organization_id,
1290 capr.operating_unit_id,
1291 sysdate, --last_update_date,
1292 FND_GLOBAL.USER_ID, --last_updated_by,
1293 FND_GLOBAL.USER_ID, --last_update_login,
1294 sysdate, --creation_date,
1295 FND_GLOBAL.USER_ID, --created_by,
1296 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
1297 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
1298 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
1299 sysdate, --program_update_date,
1300 capr.ae_header_id,
1301 capr.ae_line_num
1302 from cst_ap_po_reconciliation capr
1303 where capr.po_distribution_id = c_wo_rec.po_distribution_id
1304 and capr.accrual_account_id = c_wo_rec.accrual_account_id
1305 and capr.operating_unit_id = c_wo_rec.operating_unit_id;
1306
1307 l_stmt_num := 25;
1308
1309 /* Get the sum of the entered amount */
1310 select sum(capr.entered_amount)
1311 into l_ent_sum
1312 from cst_ap_po_reconciliation capr
1313 where capr.po_distribution_id = c_wo_rec.po_distribution_id
1314 and capr.accrual_account_id = c_wo_rec.accrual_account_id
1315 and capr.operating_unit_id = c_wo_rec.operating_unit_id;
1316
1317 /* Get all the currency information and offset/erv accounts based on the PO match type */
1318 /* the offset account is selected as follows.If the destination type code is Expense, get the charge account
1319 else get the variance account from the po distribution */
1320
1321 select decode(pod.destination_type_code,'EXPENSE',pod.code_combination_id,
1322 pod.variance_account_id
1323 ),
1324 decode(poll.match_option, 'P', pod.variance_account_id,
1325 decode(pod.destination_type_code,'EXPENSE', pod.code_combination_id,-1)),
1326 poh.currency_code,
1327 poh.rate_type,
1328 --
1329 --BUG#9191539: The exchange rate date for PO:PO_RATE_DATE - For Receipt:Write Off Date
1330 --
1331 DECODE(poll.match_option, 'P',NVL(pod.rate_date,TRUNC(pod.creation_date))
1332 ,NVL(p_wo_date,TRUNC(SYSDATE)))
1333 into l_off_id,
1334 l_erv_id,
1335 l_wo_cc,
1336 l_wo_ct,
1337 l_wo_cd
1338 from po_distributions_all pod,
1339 po_line_locations_all poll,
1340 po_headers_all poh
1341 where pod.po_distribution_id = c_wo_rec.po_distribution_id
1342 and pod.org_id = p_ou_id
1343 and poh.po_header_id = pod.po_header_id
1344 and poll.line_location_id = pod.line_location_id;
1345
1346 l_stmt_num := 26;
1347
1348 /* For the case of match to receipt, when NO rate is defined, use the rate and the currency conversion date from
1349 the po header */
1350
1351 BEGIN
1352
1353 select decode(poll.match_option, 'P',NVL(pod.rate,1),
1354 gl_currency_api.get_rate(poh.currency_code, gsb.currency_code,
1355 trunc(p_wo_date),poh.rate_type)
1356 )
1357 into l_wo_cr
1358 from po_distributions_all pod,
1359 po_line_locations_all poll,
1360 po_headers_all poh,
1361 gl_sets_of_books gsb
1362 where pod.po_distribution_id = c_wo_rec.po_distribution_id
1363 and pod.org_id = p_ou_id
1364 and poh.po_header_id = pod.po_header_id
1365 and poll.line_location_id = pod.line_location_id
1366 and gsb.set_of_books_id = pod.set_of_books_id ;
1367
1368 EXCEPTION
1369 WHEN gl_currency_api.NO_RATE THEN
1370
1371 Select NVL(pod.rate,1),
1372 NVL(pod.rate_date,TRUNC(pod.creation_date))
1373 into l_wo_cr,
1374 l_wo_cd
1375 from po_distributions_all pod
1376 where pod.po_distribution_id = c_wo_rec.po_distribution_id
1377 and pod.org_id = p_ou_id ;
1378
1379 END;
1380
1381 l_stmt_num := 28;
1382
1383 /* Need to further determine ERV account if erv_id = -1 */
1384
1385 if(((l_wo_cr is null) or (l_ent_sum is null)) and (l_erv_id is not null)) then
1386 --{
1387 l_erv_id := null;
1388 --}
1389 elsif(l_erv_id = -1) then
1390 --{
1391 if(c_wo_rec.l_tot_bal > (l_wo_cr * l_ent_sum)) then
1392 --{
1393 select rate_var_gain_ccid
1394 into l_erv_id
1395 from financials_system_params_all
1396 where org_id = p_ou_id;
1397 --}
1398 else
1399 --{
1400 select rate_var_loss_ccid
1401 into l_erv_id
1402 from financials_system_params_all
1403 where org_id = p_ou_id;
1404 --}
1405 end if; /* c_wo_rec.l_tot_bal > (l_wo_cr + l_ent_sum) */
1406 --}
1407 end if; /* ((l_wo_cr is null) or (l_ent_sum is null)) and (l_erv_id is not null) */
1408
1409 l_stmt_num := 30;
1410
1411 /*
1412 Insert the PO distribution information, as well as the extra values
1413 recently calcuated into the write-off headers table.
1414 */
1415 insert into cst_write_offs
1416 (
1417 write_off_id,
1418 transaction_date,
1419 accrual_account_id,
1420 offset_account_id,
1421 erv_account_id,
1422 write_off_amount,
1423 entered_amount,
1424 currency_code,
1425 currency_conversion_type,
1426 currency_conversion_rate,
1427 currency_conversion_date,
1428 transaction_type_code,
1429 po_distribution_id,
1430 reason_id,
1431 comments,
1432 destination_type_code,
1433 inventory_item_id,
1434 vendor_id,
1435 operating_unit_id,
1436 last_update_date,
1437 last_updated_by,
1438 last_update_login,
1439 creation_date,
1440 created_by,
1441 request_id,
1442 program_application_id,
1443 program_id,
1444 program_update_date
1445 )
1446 values
1447 (
1448 c_wo_rec.l_wo_id,
1449 p_wo_date,
1450 c_wo_rec.accrual_account_id,
1451 l_off_id,
1452 l_erv_id,
1453 (-1) * c_wo_rec.l_tot_bal,
1454 (-1) * l_ent_sum,
1455 l_wo_cc,
1456 l_wo_ct,
1457 l_wo_cr,
1458 l_wo_cd,
1459 'WRITE OFF',
1460 c_wo_rec.po_distribution_id,
1461 p_rea_id,
1462 p_comments,
1463 c_wo_rec.destination_type_code,
1464 c_wo_rec.inventory_item_id,
1465 c_wo_rec.vendor_id,
1466 p_ou_id,
1467 sysdate, --last_update_date,
1468 FND_GLOBAL.USER_ID, --last_updated_by,
1469 FND_GLOBAL.USER_ID, --last_update_login,
1470 sysdate, --creation_date,
1471 FND_GLOBAL.USER_ID, --created_by,
1472 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
1473 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
1474 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
1475 sysdate --program_update_date
1476 );
1477 --}
1478 end loop; /* for c_wo_rec in c_wo(p_ou_id) */
1479
1480 l_stmt_num := 35;
1481 /*
1482 First delete the individual transactions from cst_ap_po_reconciliation
1483 as to maintain referential integretiy.
1484 */
1485 delete from cst_ap_po_reconciliation capr
1486 where exists (
1487 select 'X'
1488 from cst_reconciliation_summary crs
1489 where capr.operating_unit_id = crs.operating_unit_id
1490 and capr.po_distribution_id = crs.po_distribution_id
1491 and capr.accrual_account_id = crs.accrual_account_id
1492 and crs.write_off_select_flag = 'Y');
1493
1494 l_stmt_num := 40;
1495
1496 /*
1497 Once all the individual transaction have been deleted, removed the
1498 header information from cst_reconciliation_summary
1499 */
1500 delete from cst_reconciliation_summary
1501 where operating_unit_id = p_ou_id
1502 and write_off_select_flag = 'Y';
1503
1504 l_stmt_num := 45;
1505 /*
1506 Call SLA's bulk events generator which uses the values previously
1507 inserted into SLA's event temp table
1508 */
1509 xla_events_pub_pkg.create_bulk_events(p_source_application_id => 201,
1510 p_application_id => 707,
1511 p_ledger_id => p_sob_id,
1512 p_entity_type_code => 'WO_ACCOUNTING_EVENTS');
1513
1514 commit;
1515 --}
1516 else
1517 --{
1518 x_count := -1;
1519 return;
1520 --}
1521 end if; /* l_rows > 0 */
1522
1523 x_count := l_rows;
1524 return;
1525
1526 exception
1527 when others then
1528 --{
1529 rollback;
1530 x_count := -1;
1531 x_err_num := SQLCODE;
1532 x_err_code := NULL;
1533 x_err_msg := 'CST_Accrual_Rec_PVT.insert_appo_data_all() ' || SQLERRM;
1534 fnd_message.set_name('BOM','CST_UNEXPECTED');
1535 fnd_message.set_token('TOKEN',SQLERRM);
1536 if(l_unLog) then
1537 fnd_log.message(fnd_log.level_unexpected, g_log_header || '.' || l_api_name
1538 || '(' || to_char(l_stmt_num) || ')', FALSE);
1539 end if;
1540 fnd_msg_pub.add;
1541 return;
1542 --}
1543 end insert_appo_data_all;
1544
1545 -- Start of comments
1546 -- API name : Is_Reversible
1547 -- Type : Private
1548 -- Pre-reqs : None.
1549 -- Function : Checks whether a specific write-off distribution is reversible.
1550 -- A write-off is reversible if the write-off was performed in release 12 and later,
1551 -- has the transaction type code 'WRITE OFF', has not alredy been reversed and is
1552 -- not already part of another write-off distribution.
1553 -- Parameters :
1554 -- IN : p_wo_id IN NUMBER Required
1555 -- Write-Off Date
1556 -- : p_txn_c IN VARCHAR2 Required
1557 -- Transaction Type
1558 -- : p_off_id IN NUMBER Required
1559 -- Offset Accont
1560 -- : p_ou_id IN NUMBER Required
1561 -- Operating Unit Identifier
1562 -- OUT : x_count OUT NOCOPY NUBMER Required
1563 -- Reversible Indicator
1564 -- FND_API.G_TRUE => Reversible
1565 -- FND_API.G_FALSE => Not Reversible
1566 -- : x_err_num OUT NOCOPY NUMBER Required
1567 -- Standard Error Parameter
1568 -- : x_err_code OUT NOCOPY VARCHAR2 Required
1569 -- Standard Error Parameter
1570 -- : x_err_msg OUT NOCOPY VARCHAR2 Required
1571 -- Standard Error Parameter
1572 -- Version : Current version 1.0
1573 -- Previous version 1.0
1574 -- Initial version 1.0
1575 -- End of comments
1576 procedure is_reversible(
1577 p_wo_id in number,
1578 p_txn_c in varchar2,
1579 p_off_id in number,
1580 p_ou_id in number,
1581 x_count out nocopy varchar2,
1582 x_err_num out nocopy number,
1583 x_err_code out nocopy varchar2,
1584 x_err_msg out nocopy varchar2) is
1585
1586 l_api_version constant number := 1.0;
1587 l_api_name constant varchar2(30) := 'is_reversible';
1588 l_full_name constant varchar2(60) := g_pkg_name || '.' || l_api_name;
1589 l_module constant varchar2(60) := 'cst.plsql.' || l_full_name;
1590 l_uLog constant boolean := fnd_log.test(fnd_log.level_unexpected, l_module);
1591 l_unLog constant boolean := l_uLog and (fnd_log.level_unexpected >= g_log_level);
1592 l_errorLog constant boolean := l_uLog and (fnd_log.level_error >= g_log_level);
1593 l_exceptionLog constant boolean := l_errorLog and (fnd_log.level_exception >= g_log_level);
1594 l_pLog constant boolean := l_exceptionLog and (fnd_log.level_procedure >= g_log_level);
1595 l_sLog constant boolean := l_pLog and (fnd_log.level_statement >= g_log_level);
1596 l_stmt_num number;
1597 l_enabled number;
1598
1599 begin
1600
1601 l_stmt_num := 5;
1602
1603 if(l_pLog) then
1604 fnd_log.string(fnd_log.level_procedure, g_log_header || '.' || l_api_name ||
1605 '.begin', 'is_reversible << '
1606 || 'p_wo_id := ' || to_char(p_wo_id)
1607 || 'p_txn_c := ' || p_txn_c
1608 || 'p_off_id := ' || to_char(p_off_id)
1609 || 'p_ou_id := ' || to_char(p_ou_id));
1610 end if;
1611
1612 /* Print out the parameters to the Message Stack */
1613 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Write-Of ID: ' || to_char(p_wo_id));
1614 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Transaction Type: ' || p_txn_c);
1615 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Offset Account: ' || to_char(p_off_id));
1616 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Operating Unit: ' || to_char(p_ou_id));
1617
1618 l_stmt_num := 10;
1619
1620 /* Make sure distribution is not already a reversal */
1621 if(p_txn_c = 'WRITE OFF') then
1622 --{
1623 l_stmt_num := 15;
1624 /*
1625 Check whether the write-off was done in Release 12 or later
1626 Pre-Release 12 write-offs will not have an offset account
1627 */
1628 if(p_off_id is not null) then
1629 --{
1630 l_stmt_num := 20;
1631 /*
1632 Check whether current write-off has not already been reversed by
1633 searching the header table to see if the current distributions
1634 write-off ID is another distributions reversal ID
1635 */
1636 select count(*)
1637 into l_enabled
1638 from cst_write_offs
1639 where reversal_id = p_wo_id
1640 and operating_unit_id = p_ou_id;
1641 if(l_enabled = 0) then
1642 --{
1643 l_stmt_num := 25;
1644 /*
1645 Finally check whether the current distribution has been part
1646 of another write-off by taking the current distribution's
1647 write-off ID and seeing if it matches a row's
1648 write_off_transaction_id column in the details table
1649 */
1650 select count(*)
1651 into l_enabled
1652 from cst_write_off_details
1653 where write_off_transaction_id = p_wo_id
1654 and operating_unit_id = p_ou_id;
1655 if(l_enabled = 0) then
1656 --{
1657 l_stmt_num := 30;
1658 /* If all the tests pass, write-off is reversible */
1659 x_count := fnd_api.g_true;
1660 return;
1661 --}
1662 end if; /* l_enabled = 0 */
1663 --}
1664 end if; /* l_enabled = 0 */
1665 --}
1666 end if; /* p_off_id is not null */
1667 --}
1668 end if; /* p_txn_c = 'WRITE OFF' */
1669
1670 /* Indicates a test failed and therefore the write-off is not reversible */
1671 x_count := fnd_api.g_false;
1672 return;
1673
1674 exception
1675 when others then
1676 rollback;
1677 x_count := fnd_api.g_false;
1678 x_err_num := SQLCODE;
1679 x_err_code := NULL;
1680 x_err_msg := 'CST_Accrual_Rec_PVT.is_reversible() ' || SQLERRM;
1681 fnd_message.set_name('BOM','CST_UNEXPECTED');
1682 fnd_message.set_token('TOKEN',SQLERRM);
1683 if(l_unLog) then
1684 fnd_log.message(fnd_log.level_unexpected, g_log_header || '.' || l_api_name
1685 || '(' || to_char(l_stmt_num) || ')', FALSE);
1686 end if;
1687 fnd_msg_pub.add;
1688 return;
1689
1690 end is_reversible;
1691
1692 -- Start of comments
1693 -- API name : Reverse_Write_Offs
1694 -- Type : Private
1695 -- Pre-reqs : None.
1696 -- Function : Performs a write-off reversal and insert distributions and/or
1697 -- individual transactions back into the appropriate tables.
1698 -- If the reversing miscellaneous write-offs, then a write-off
1699 -- reversal is created and the individual miscellaneous transactions
1700 -- is inserted back into cst_misc_reconciliation. If reversing an
1701 -- AP and PO distribution, then a write-off reversal is created and all
1702 -- the individual AP and PO transactions in addition to all write-offs
1703 -- and reversals sharing the same PO distribution ID and accrual account
1704 -- are summed up and if they equal a non-zero value, they are inserted
1705 -- into the cst_reconciliation_summary and cst_ap_po_reconciliation
1706 -- as appropriate (see package body).
1707 -- Parameters :
1708 -- IN : p_wo_date IN DATE Required
1709 -- Write-Off Date
1710 -- : p_rea_id IN NUMBER Optional
1711 -- Write-Off Reason
1712 -- : p_comments IN VARCHAR2 Optional
1713 -- Write-Off Comments
1714 -- : p_sob_id IN NUMBER Required
1715 -- Ledger/Set of Books
1716 -- : p_ou_id IN NUMBER Required
1717 -- Operating Unit Identifier
1718 -- OUT : x_count OUT NOCOPY NUBMER Required
1719 -- Success Indicator
1720 -- {x > 0} => Success
1721 -- -1 => Failure
1722 -- : x_err_num OUT NOCOPY NUMBER Required
1723 -- Standard Error Parameter
1724 -- : x_err_code OUT NOCOPY VARCHAR2 Required
1725 -- Standard Error Parameter
1726 -- : x_err_msg OUT NOCOPY VARCHAR2 Required
1727 -- Standard Error Parameter
1728 -- Version : Current version 1.0
1729 -- Previous version 1.0
1730 -- Initial version 1.0
1731 -- End of comments
1732 procedure reverse_write_offs(
1733 p_wo_date in date,
1734 p_rea_id in number,
1735 p_comments in varchar2,
1736 p_sob_id in number,
1737 p_ou_id in number,
1738 x_count out nocopy number,
1739 x_err_num out nocopy number,
1740 x_err_code out nocopy varchar2,
1741 x_err_msg out nocopy varchar2) is
1742
1743 l_api_version constant number := 1.0;
1744 l_api_name constant varchar2(30) := 'reverse_write_offs';
1745 l_full_name constant varchar2(60) := g_pkg_name || '.' || l_api_name;
1746 l_module constant varchar2(60) := 'cst.plsql.' || l_full_name;
1747 l_uLog constant boolean := fnd_log.test(fnd_log.level_unexpected, l_module);
1748 l_unLog constant boolean := l_uLog and (fnd_log.level_unexpected >= g_log_level);
1749 l_errorLog constant boolean := l_uLog and (fnd_log.level_error >= g_log_level);
1750 l_exceptionLog constant boolean := l_errorLog and (fnd_log.level_exception >= g_log_level);
1751 l_pLog constant boolean := l_exceptionLog and (fnd_log.level_procedure >= g_log_level);
1752 l_sLog constant boolean := l_pLog and (fnd_log.level_statement >= g_log_level);
1753 l_stmt_num number;
1754 l_rows number;
1755 l_po_proc number;
1756 /* Cursor to hold all the distributions marked for reversal */
1757 cursor c_wo(l_ou_id number) is
1758 select po_accrual_write_offs_s.nextval l_wo_id,
1759 write_off_id l_rev_id,
1760 accrual_account_id,
1761 offset_account_id,
1762 erv_account_id,
1763 write_off_amount amount,
1764 entered_amount,
1765 currency_code,
1766 currency_conversion_type,
1767 currency_conversion_rate,
1768 currency_conversion_date,
1769 po_distribution_id,
1770 invoice_distribution_id,
1771 inventory_transaction_id,
1772 destination_type_code,
1773 inventory_item_id,
1774 vendor_id,
1775 legal_entity_id,
1776 operating_unit_id,
1777 last_update_date,
1778 last_updated_by,
1779 last_update_login,
1780 creation_date,
1781 created_by,
1782 request_id,
1783 program_application_id,
1784 program_id,
1785 program_update_date
1786 from cst_write_offs
1787 where operating_unit_id = l_ou_id
1788 and write_off_select_flag = 'Y';
1789
1790 begin
1791
1792 l_stmt_num := 5;
1793
1794 if(l_pLog) then
1795 fnd_log.string(fnd_log.level_procedure, g_log_header || '.' || l_api_name ||
1796 '.begin', 'reverse_write_offs << '
1797 || 'p_wo_date := ' || to_char(p_wo_date, 'YYYY/MM/DD HH24:MI:SS')
1798 || 'p_rea_id := ' || to_char(p_rea_id)
1799 || 'p_comments := ' || p_comments
1800 || 'p_sob_id := ' || to_char(p_sob_id)
1801 || 'p_ou_id := ' || to_char(p_ou_id));
1802 end if;
1803
1804 /* Print out the parameters to the Message Stack */
1805 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Reversal Date: ' || to_char(p_wo_date, 'YYYY/MM/DD HH24:MI:SS'));
1806 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Reversal Reason: ' || to_char(p_rea_id));
1807 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Comments: ' || p_comments);
1808 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Set of Books: ' || to_char(p_sob_id));
1809 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Operating Unit: ' || to_char(p_ou_id));
1810
1811 l_stmt_num := 10;
1812
1813 /* Check that the user has selected distributions to reverse */
1814 select count(*)
1815 into l_rows
1816 from cst_write_offs
1817 where operating_unit_id = p_ou_id
1818 and write_off_select_flag = 'Y';
1819
1820 if(l_rows > 0) then
1821 --{
1822 l_stmt_num := 15;
1823
1824 for c_wo_rec in c_wo(p_ou_id) loop
1825 --{
1826 /* Insert the necessary information into SLA's event temp table */
1827 insert into xla_events_int_gt
1828 (
1829 application_id,
1830 ledger_id,
1831 legal_entity_id,
1832 entity_code,
1833 source_id_int_1,
1834 event_class_code,
1835 event_type_code,
1836 event_date,
1837 event_status_code,
1838 --BUG#7226250
1839 security_id_int_2,
1840 transaction_date,
1841 reference_date_1,
1842 transaction_number
1843 )
1844 values
1845 (
1846 707,
1847 p_sob_id,
1848 c_wo_rec.legal_entity_id,
1849 'WO_ACCOUNTING_EVENTS',
1850 c_wo_rec.l_wo_id,
1851 'ACCRUAL_WRITE_OFF',
1852 'ACCRUAL_WRITE_OFF',
1853 p_wo_date,
1854 XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
1855 p_ou_id,
1856 p_wo_date,
1857 INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(p_wo_date,p_ou_id),
1858 to_char(c_wo_rec.l_wo_id)
1859 );
1860
1861 l_stmt_num := 20;
1862
1863 /* Insert the reversal into the headers table */
1864 insert into cst_write_offs
1865 (
1866 write_off_id,
1867 transaction_date,
1868 accrual_account_id,
1869 offset_account_id,
1870 erv_account_id,
1871 write_off_amount,
1872 entered_amount,
1873 currency_code,
1874 currency_conversion_type,
1875 currency_conversion_rate,
1876 currency_conversion_date,
1877 transaction_type_code,
1878 po_distribution_id,
1879 invoice_distribution_id,
1880 inventory_transaction_id,
1881 reversal_id,
1882 reason_id,
1883 comments,
1884 destination_type_code,
1885 inventory_item_id,
1886 vendor_id,
1887 legal_entity_id,
1888 operating_unit_id,
1889 last_update_date,
1890 last_updated_by,
1891 last_update_login,
1892 creation_date,
1893 created_by,
1894 request_id,
1895 program_application_id,
1896 program_id,
1897 program_update_date
1898 )
1899 values
1900 (
1901 c_wo_rec.l_wo_id,
1902 p_wo_date,
1903 c_wo_rec.accrual_account_id,
1904 c_wo_rec.offset_account_id,
1905 c_wo_rec.erv_account_id,
1906 (-1) * c_wo_rec.amount,
1907 (-1) * c_wo_rec.entered_amount,
1908 c_wo_rec.currency_code,
1909 c_wo_rec.currency_conversion_type,
1910 c_wo_rec.currency_conversion_rate,
1911 c_wo_rec.currency_conversion_date,
1912 'REVERSE WRITE OFF',
1913 c_wo_rec.po_distribution_id,
1914 c_wo_rec.invoice_distribution_id,
1915 c_wo_rec.inventory_transaction_id,
1916 c_wo_rec.l_rev_id,
1917 p_rea_id,
1918 p_comments,
1919 c_wo_rec.destination_type_code,
1920 c_wo_rec.inventory_item_id,
1921 c_wo_rec.vendor_id,
1922 c_wo_rec.legal_entity_id,
1923 c_wo_rec.operating_unit_id,
1924 sysdate, --last_update_date,
1925 FND_GLOBAL.USER_ID, --last_updated_by,
1926 FND_GLOBAL.USER_ID, --last_update_login,
1927 sysdate, --creation_date,
1928 FND_GLOBAL.USER_ID, --created_by,
1929 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
1930 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
1931 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
1932 sysdate --program_update_date
1933 );
1934
1935 l_stmt_num := 25;
1936
1937 /*
1938 Insert the details from the previous write-off but with the new write-off ID
1939 into the write-off details table
1940 */
1941 insert into cst_write_off_details
1942 (
1943 write_off_id,
1944 transaction_date,
1945 amount,
1946 entered_amount,
1947 quantity,
1948 currency_code,
1949 currency_conversion_type,
1950 currency_conversion_rate,
1951 currency_conversion_date,
1952 transaction_type_code,
1953 rcv_transaction_id,
1954 invoice_distribution_id,
1955 inventory_transaction_id,
1956 write_off_transaction_id,
1957 inventory_organization_id,
1958 operating_unit_id,
1959 last_update_date,
1960 last_updated_by,
1961 last_update_login,
1962 creation_date,
1963 created_by,
1964 request_id,
1965 program_application_id,
1966 program_id,
1967 program_update_date,
1968 ae_header_id,
1969 ae_line_num
1970 )
1971 select c_wo_rec.l_wo_id,
1972 cwod.transaction_date,
1973 cwod.amount,
1974 cwod.entered_amount,
1975 cwod.quantity,
1976 cwod.currency_code,
1977 cwod.currency_conversion_type,
1978 cwod.currency_conversion_rate,
1979 cwod.currency_conversion_date,
1980 cwod.transaction_type_code,
1981 cwod.rcv_transaction_id,
1982 cwod.invoice_distribution_id,
1983 cwod.inventory_transaction_id,
1984 cwod.write_off_transaction_id,
1985 cwod.inventory_organization_id,
1986 cwod.operating_unit_id,
1987 sysdate, --last_update_date,
1988 FND_GLOBAL.USER_ID, --last_updated_by,
1989 FND_GLOBAL.USER_ID, --last_update_login,
1990 sysdate, --creation_date,
1991 FND_GLOBAL.USER_ID, --created_by,
1992 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
1993 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
1994 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
1995 sysdate, --program_update_date,
1996 cwod.ae_header_id,
1997 cwod.ae_line_num
1998 from cst_write_off_details cwod
1999 where cwod.write_off_id = c_wo_rec.l_rev_id
2000 and cwod.operating_unit_id = c_wo_rec.operating_unit_id;
2001
2002 l_stmt_num := 30;
2003
2004 /* Need to re-insert transations, either Miscellaneous or AP-PO*/
2005 /* Doing Miscellaneous */
2006 if((c_wo_rec.po_distribution_id is null) or
2007 (c_wo_rec.inventory_transaction_id is not null and c_wo_rec.po_distribution_id is not null) or
2008 (c_wo_rec.invoice_distribution_id is not null)) then
2009 --{
2010 l_stmt_num := 35;
2011
2012 insert into cst_misc_reconciliation
2013 (
2014 transaction_date,
2015 amount,
2016 entered_amount,
2017 quantity,
2018 currency_code,
2019 currency_conversion_type,
2020 currency_conversion_rate,
2021 currency_conversion_date,
2022 invoice_distribution_id,
2023 inventory_transaction_id,
2024 po_distribution_id,
2025 accrual_account_id,
2026 transaction_type_code,
2027 inventory_item_id,
2028 vendor_id,
2029 inventory_organization_id,
2030 operating_unit_id,
2031 last_update_date,
2032 last_updated_by,
2033 last_update_login,
2034 creation_date,
2035 created_by,
2036 request_id,
2037 program_application_id,
2038 program_id,
2039 program_update_date,
2040 ae_header_id,
2041 ae_line_num
2042 )
2043 select cwod.transaction_date,
2044 cwod.amount,
2045 cwod.entered_amount,
2046 cwod.quantity,
2047 cwod.currency_code,
2048 cwod.currency_conversion_type,
2049 cwod.currency_conversion_rate,
2050 cwod.currency_conversion_date,
2051 cwod.invoice_distribution_id,
2052 cwod.inventory_transaction_id,
2053 cwo.po_distribution_id,
2054 cwo.accrual_account_id,
2055 cwod.transaction_type_code,
2056 cwo.inventory_item_id,
2057 cwo.vendor_id,
2058 cwod.inventory_organization_id,
2059 cwod.operating_unit_id,
2060 sysdate, --last_update_date,
2061 FND_GLOBAL.USER_ID, --last_updated_by,
2062 FND_GLOBAL.USER_ID, --last_update_login,
2063 sysdate, --creation_date,
2064 FND_GLOBAL.USER_ID, --created_by,
2065 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
2066 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
2067 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
2068 sysdate, --program_update_date,
2069 cwod.ae_header_id,
2070 cwod.ae_line_num
2071 from cst_write_off_details cwod,
2072 cst_write_offs cwo
2073 where cwo.write_off_id = c_wo_rec.l_wo_id
2074 and cwo.operating_unit_id = c_wo_rec.operating_unit_id
2075 and cwod.write_off_id = cwo.write_off_id
2076 and cwod.operating_unit_id = cwo.operating_unit_id;
2077
2078 l_stmt_num := 40;
2079 --}
2080 /* AP PO */
2081 elsif((c_wo_rec.po_distribution_id is not null) and
2082 (c_wo_rec.inventory_transaction_id is null) and
2083 (c_wo_rec.invoice_distribution_id is null)) then
2084 --{
2085 l_stmt_num := 45;
2086
2087 /*
2088 Look whether a rebuild has occurred, meaning CRS will have rows for a given
2089 po_distribution_id/accrual_account_id pair
2090 */
2091 select count(*)
2092 into l_po_proc
2093 from cst_reconciliation_summary
2094 where po_distribution_id = c_wo_rec.po_distribution_id
2095 and accrual_account_id = c_wo_rec.accrual_account_id
2096 and operating_unit_id = c_wo_rec.operating_unit_id;
2097
2098 /* No records in CRS so insert relevant records from CWOD and CWO */
2099 if(l_po_proc = 0) then
2100 --{
2101 l_stmt_num := 50;
2102
2103 insert into cst_ap_po_reconciliation
2104 (
2105 transaction_date,
2106 amount,
2107 entered_amount,
2108 quantity,
2109 currency_code,
2110 currency_conversion_type,
2111 currency_conversion_rate,
2112 currency_conversion_date,
2113 po_distribution_id,
2114 rcv_transaction_id,
2115 invoice_distribution_id,
2116 accrual_account_id,
2117 transaction_type_code,
2118 write_off_id,
2119 inventory_organization_id,
2120 operating_unit_id,
2121 last_update_date,
2122 last_updated_by,
2123 last_update_login,
2124 creation_date,
2125 created_by,
2126 request_id,
2127 program_application_id,
2128 program_id,
2129 program_update_date,
2130 ae_header_id,
2131 ae_line_num
2132 )
2133 select cwod.transaction_date,
2134 cwod.amount,
2135 cwod.entered_amount,
2136 cwod.quantity,
2137 cwod.currency_code,
2138 cwod.currency_conversion_type,
2139 cwod.currency_conversion_rate,
2140 cwod.currency_conversion_date,
2141 cwo.po_distribution_id,
2142 cwod.rcv_transaction_id,
2143 cwod.invoice_distribution_id,
2144 cwo.accrual_account_id,
2145 cwod.transaction_type_code,
2146 cwod.write_off_transaction_id,
2147 cwod.inventory_organization_id,
2148 cwod.operating_unit_id,
2149 sysdate, --last_update_date,
2150 FND_GLOBAL.USER_ID, --last_updated_by,
2151 FND_GLOBAL.USER_ID, --last_update_login,
2152 sysdate, --creation_date,
2153 FND_GLOBAL.USER_ID, --created_by,
2154 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
2155 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
2156 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
2157 sysdate, --program_update_date,
2158 cwod.ae_header_id,
2159 cwod.ae_line_num
2160 from cst_write_offs cwo,
2161 cst_write_off_details cwod
2162 where cwo.write_off_id = c_wo_rec.l_wo_id
2163 and cwo.po_distribution_id = c_wo_rec.po_distribution_id
2164 and cwo.accrual_account_id = c_wo_rec.accrual_account_id
2165 and cwo.operating_unit_id = c_wo_rec.operating_unit_id
2166 and cwod.write_off_id = cwo.write_off_id
2167 and cwod.operating_unit_id = cwo.operating_unit_id;
2168
2169 l_stmt_num := 55;
2170
2171 /* Next insert the new write-off header and reversal header into CAPR */
2172 insert into cst_ap_po_reconciliation
2173 (
2174 transaction_date,
2175 amount,
2176 entered_amount,
2177 currency_code,
2178 currency_conversion_type,
2179 currency_conversion_rate,
2180 currency_conversion_date,
2181 po_distribution_id,
2182 accrual_account_id,
2183 transaction_type_code,
2184 write_off_id,
2185 operating_unit_id,
2186 last_update_date,
2187 last_updated_by,
2188 last_update_login,
2189 creation_date,
2190 created_by,
2191 request_id,
2192 program_application_id,
2193 program_id,
2194 program_update_date
2195 )
2196 select cwo.transaction_date,
2197 cwo.write_off_amount,
2198 cwo.entered_amount,
2199 cwo.currency_code,
2200 cwo.currency_conversion_type,
2201 cwo.currency_conversion_rate,
2202 cwo.currency_conversion_date,
2203 cwo.po_distribution_id,
2204 cwo.accrual_account_id,
2205 cwo.transaction_type_code,
2206 cwo.write_off_id,
2207 cwo.operating_unit_id,
2208 sysdate, --last_update_date,
2209 FND_GLOBAL.USER_ID, --last_updated_by,
2210 FND_GLOBAL.USER_ID, --last_update_login,
2211 sysdate, --creation_date,
2212 FND_GLOBAL.USER_ID, --created_by,
2213 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
2214 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
2215 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
2216 sysdate --program_update_date
2217 from cst_write_offs cwo
2218 where cwo.write_off_id in (c_wo_rec.l_wo_id, c_wo_rec.l_rev_id)
2219 and cwo.po_distribution_id = c_wo_rec.po_distribution_id
2220 and cwo.accrual_account_id = c_wo_rec.accrual_account_id
2221 and cwo.operating_unit_id = c_wo_rec.operating_unit_id;
2222
2223 l_stmt_num := 60;
2224
2225 /* Insert the data into the summary table */
2226 insert into cst_reconciliation_summary
2227 (
2228 po_distribution_id,
2229 accrual_account_id,
2230 po_balance,
2231 ap_balance,
2232 write_off_balance,
2233 last_receipt_date,
2234 last_invoice_dist_date,
2235 last_write_off_date,
2236 inventory_item_id,
2237 vendor_id,
2238 destination_type_code,
2239 operating_unit_id,
2240 last_update_date,
2241 last_updated_by,
2242 last_update_login,
2243 creation_date,
2244 created_by,
2245 request_id,
2246 program_application_id,
2247 program_id,
2248 program_update_date
2249 )
2250 select cwo.po_distribution_id,
2251 cwo.accrual_account_id,
2252 sum(decode(capr.write_off_id,NULL,
2253 decode(capr.invoice_distribution_id,NULL,
2254 capr.amount,0),0)),
2255 sum(decode(capr.invoice_distribution_id,NULL,0,capr.amount)),
2256 sum(decode(capr.write_off_id,NULL,0,capr.amount)),
2257 max(decode(capr.write_off_id,NULL,
2258 decode(capr.invoice_distribution_id,NULL,
2259 capr.transaction_date,NULL),NULL)),
2260 max(decode(capr.invoice_distribution_id,NULL,NULL,capr.transaction_date)),
2261 max(decode(capr.write_off_id,NULL,NULL,capr.transaction_date)),
2262 cwo.inventorY_item_id,
2263 cwo.vendor_id,
2264 cwo.destination_type_code,
2265 cwo.operating_unit_id,
2266 sysdate, --last_update_date,
2267 FND_GLOBAL.USER_ID, --last_updated_by,
2268 FND_GLOBAL.USER_ID, --last_update_login,
2269 sysdate, --creation_date,
2270 FND_GLOBAL.USER_ID, --created_by,
2271 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
2272 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
2273 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
2274 sysdate --program_update_date
2275 from cst_ap_po_reconciliation capr,
2276 cst_write_offs cwo
2277 where cwo.write_off_id = c_wo_rec.l_wo_id
2278 and cwo.operating_unit_id = c_wo_rec.operating_unit_id
2279 and capr.po_distribution_id = cwo.po_distribution_id
2280 and capr.accrual_account_id = cwo.accrual_account_id
2281 and capr.operating_unit_id = cwo.operating_unit_id
2282 group by cwo.po_distribution_id,
2283 cwo.accrual_account_id,
2284 cwo.inventory_item_id,
2285 cwo.vendor_id,
2286 cwo.destination_type_code,
2287 cwo.operating_unit_id,
2288 cwo.last_update_date,
2289 cwo.last_updated_by,
2290 cwo.last_update_login,
2291 cwo.creation_date,
2292 cwo.created_by,
2293 cwo.request_id,
2294 cwo.program_application_id,
2295 cwo.program_id,
2296 cwo.program_update_date;
2297
2298 l_stmt_num := 65;
2299 --}
2300 /*
2301 Rebuild has occured, decide if reversal would balance the account
2302 for the given po distribution ID
2303 */
2304 else
2305 --{
2306 l_stmt_num := 70;
2307
2308 select (po_balance + ap_balance + write_off_balance)
2309 into l_po_proc
2310 from cst_reconciliation_summary
2311 where po_distribution_id = c_wo_rec.po_distribution_id
2312 and accrual_account_id = c_wo_rec.accrual_account_id
2313 and operating_unit_id = c_wo_rec.operating_unit_id;
2314
2315 /* If it balances, remove entries from reconciliation tables */
2316 if(l_po_proc + (-1 * c_wo_rec.amount) = 0) then
2317 --{
2318 l_stmt_num := 75;
2319
2320 delete from cst_ap_po_reconciliation
2321 where po_distribution_id = c_wo_rec.po_distribution_id
2322 and accrual_account_id = c_wo_rec.accrual_account_id
2323 and operating_unit_id = c_wo_rec.operating_unit_id;
2324
2325 l_stmt_num := 80;
2326
2327 delete from cst_reconciliation_summary
2328 where po_distribution_id = c_wo_rec.po_distribution_id
2329 and accrual_account_id = c_wo_rec.accrual_account_id
2330 and operating_unit_id = c_wo_rec.operating_unit_id;
2331 --}
2332 /* If it doesn't, update wo_balance in CRS and insert reversal only in CAPR */
2333 else
2334 --{
2335 l_stmt_num := 85;
2336
2337 update cst_reconciliation_summary crs
2338 set crs.write_off_balance = crs.write_off_balance + (-1 * c_wo_rec.amount)
2339 where po_distribution_id = c_wo_rec.po_distribution_id
2340 and accrual_account_id = c_wo_rec.accrual_account_id
2341 and operating_unit_id = c_wo_rec.operating_unit_id;
2342
2343 l_stmt_num := 90;
2344
2345 insert into cst_ap_po_reconciliation
2346 (
2347 transaction_date,
2348 amount,
2349 entered_amount,
2350 currency_code,
2351 currency_conversion_type,
2352 currency_conversion_rate,
2353 currency_conversion_date,
2354 po_distribution_id,
2355 accrual_account_id,
2356 transaction_type_code,
2357 write_off_id,
2358 operating_unit_id,
2359 last_update_date,
2360 last_updated_by,
2361 last_update_login,
2362 creation_date,
2363 created_by,
2364 request_id,
2365 program_application_id,
2366 program_id,
2367 program_update_date
2368 )
2369 select cwo.transaction_date,
2370 cwo.write_off_amount,
2371 cwo.entered_amount,
2372 cwo.currency_code,
2373 cwo.currency_conversion_type,
2374 cwo.currency_conversion_rate,
2375 cwo.currency_conversion_date,
2376 cwo.po_distribution_id,
2377 cwo.accrual_account_id,
2378 cwo.transaction_type_code,
2379 cwo.write_off_id,
2380 cwo.operating_unit_id,
2381 sysdate, --last_update_date,
2382 FND_GLOBAL.USER_ID, --last_updated_by,
2383 FND_GLOBAL.USER_ID, --last_update_login,
2384 sysdate, --creation_date,
2385 FND_GLOBAL.USER_ID, --created_by,
2386 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
2387 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
2388 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
2389 sysdate --program_update_date
2390 from cst_write_offs cwo
2391 where cwo.write_off_id = c_wo_rec.l_wo_id
2392 and cwo.po_distribution_id = c_wo_rec.po_distribution_id
2393 and cwo.accrual_account_id = c_wo_rec.accrual_account_id
2394 and cwo.operating_unit_id = c_wo_rec.operating_unit_id;
2395
2396 l_stmt_num := 95;
2397 --}
2398 end if; /* l_po_proc + (-1 * c_wo_rec.amount) = 0 */
2399 --}
2400 end if; /* l_po_proc = 0 */
2401 --}
2402 end if; /* (c_wo_rec.po_distribution_id is null) or
2403 (c_wo_rec.invoice_distribution_id is not null),
2404 (c_wo_rec.po_distribution_id is null) or
2405 (c_wo_rec.invoice_distribution_id is not null)
2406 */
2407 --}
2408 end loop; /* for c_wo_rec in c_wo(p_ou_id) */
2409
2410 l_stmt_num := 100;
2411 /*
2412 Call SLA's bulk events generator which uses the values previously
2413 inserted into SLA's event temp table
2414 */
2415 xla_events_pub_pkg.create_bulk_events(p_source_application_id => 201,
2416 p_application_id => 707,
2417 p_ledger_id => p_sob_id,
2418 p_entity_type_code => 'WO_ACCOUNTING_EVENTS');
2419
2420 /* need to reset the write_off_select_flag back CWO back to NULL */
2421
2422 Update cst_write_offs
2423 set write_off_select_flag = NULL
2424 where operating_unit_id = p_ou_id
2425 and write_off_select_flag = 'Y';
2426
2427 commit;
2428 --}
2429 else
2430 --{
2431 x_count := -1;
2432 return;
2433 --}
2434 end if; /* l_rows > 0 */
2435
2436 x_count := l_rows;
2437 return;
2438
2439 exception
2440 when others then
2441 --{
2442 rollback;
2443 x_count := -1;
2444 x_err_num := SQLCODE;
2445 x_err_code := NULL;
2446 x_err_msg := 'CST_Accrual_Rec_PVT.reverse_write_offs() ' || SQLERRM;
2447 fnd_message.set_name('BOM','CST_UNEXPECTED');
2448 fnd_message.set_token('TOKEN',SQLERRM);
2449 if(l_unLog) then
2450 fnd_log.message(fnd_log.level_unexpected, g_log_header || '.' || l_api_name
2451 || '(' || to_char(l_stmt_num) || ')', FALSE);
2452 end if;
2453 fnd_msg_pub.add;
2454 return;
2455 --}
2456 end reverse_write_offs;
2457
2458 end CST_ACCRUAL_REC_PVT;