[Home] [Help]
PACKAGE BODY: APPS.CST_ACCRUAL_REC_PVT
Source
1 package body CST_ACCRUAL_REC_PVT AS
2 /* $Header: CSTACRHB.pls 120.20.12010000.3 2008/12/30 15:21:56 smsasidh 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 ap_invoice_distributions_all aida
832 where aida.invoice_distribution_id = c_wo_rec.invoice_distribution_id
833 and apia.invoice_id = aida.invoice_id;
834 --}
835 end if; /* c_wo_rec.inventory_transaction_id is not null */
836
837 l_stmt_num := 20;
838
839 /* Insert necessary information into SLA events temp table */
840 insert into xla_events_int_gt
841 (
842 application_id,
843 ledger_id,
844 legal_entity_id,
845 entity_code,
846 source_id_int_1,
847 event_class_code,
848 event_type_code,
849 event_date,
850 event_status_code,
851 --BUG#7226250
852 security_id_int_2,
853 transaction_date,
854 reference_date_1,
855 transaction_number
856 )
857 values
858 (
859 707,
860 p_sob_id,
861 l_le_id,
862 'WO_ACCOUNTING_EVENTS',
863 c_wo_rec.l_wo_id,
864 'ACCRUAL_WRITE_OFF',
865 'ACCRUAL_WRITE_OFF',
866 p_wo_date,
867 XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
868 p_ou_id,
869 p_wo_date,
870 INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(p_wo_date,p_ou_id),
871 to_char(c_wo_rec.l_wo_id)
872 );
873
874 l_stmt_num := 25;
875
876 /*
877 Insert the selected miscellaneous transactions into
878 Costing's Write-Off tables
879 */
880 insert all
881 into cst_write_offs
882 (
883 write_off_id,
884 transaction_date,
885 accrual_account_id,
886 offset_account_id,
887 write_off_amount,
888 entered_amount,
889 currency_code,
890 currency_conversion_type,
891 currency_conversion_rate,
892 currency_conversion_date,
893 transaction_type_code,
894 invoice_distribution_id,
895 inventory_transaction_id,
896 po_distribution_id,
897 reason_id,
898 comments,
899 inventory_item_id,
900 vendor_id,
901 legal_entity_id,
902 operating_unit_id,
903 last_update_date,
904 last_updated_by,
905 last_update_login,
906 creation_date,
907 created_by,
908 request_id,
909 program_application_id,
910 program_id,
911 program_update_date
912 )
913 values
914 (
915 c_wo_rec.l_wo_id,
916 p_wo_date,
917 c_wo_rec.accrual_account_id,
918 p_off_id,
919 (-1) * c_wo_rec.amount,
920 (-1) * c_wo_rec.entered_amount,
921 c_wo_rec.currency_code,
922 c_wo_rec.currency_conversion_type,
923 c_wo_rec.currency_conversion_rate,
924 c_wo_rec.currency_conversion_date,
925 'WRITE OFF',
926 c_wo_rec.invoice_distribution_id,
927 c_wo_rec.inventory_transaction_id,
928 c_wo_rec.po_distribution_id,
929 p_rea_id,
930 p_comments,
931 c_wo_rec.inventory_item_id,
932 c_wo_rec.vendor_id,
933 l_le_id,
934 p_ou_id,
935 sysdate, --last_update_date,
936 FND_GLOBAL.USER_ID, --last_updated_by,
937 FND_GLOBAL.USER_ID, --last_update_login,
938 sysdate, --creation_date,
939 FND_GLOBAL.USER_ID, --created_by,
940 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
941 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
942 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
943 sysdate -- program_update_date
944 )
945 into cst_write_off_details
946 (
947 write_off_id,
948 transaction_date,
949 amount,
950 entered_amount,
951 quantity,
952 currency_code,
953 currency_conversion_type,
954 currency_conversion_rate,
955 currency_conversion_date,
956 transaction_type_code,
957 invoice_distribution_id,
958 inventory_transaction_id,
959 inventory_organization_id,
960 operating_unit_id,
961 last_update_date,
962 last_updated_by,
963 last_update_login,
964 creation_date,
965 created_by,
966 request_id,
967 program_application_id,
968 program_id,
969 program_update_date,
970 ae_header_id,
971 ae_line_num
972 )
973 values
974 (
975 c_wo_rec.l_wo_id,
976 c_wo_rec.transaction_date,
977 c_wo_rec.amount,
978 c_wo_rec.entered_amount,
979 c_wo_rec.quantity,
980 c_wo_rec.currency_code,
981 c_wo_rec.currency_conversion_type,
982 c_wo_rec.currency_conversion_rate,
983 c_wo_rec.currency_conversion_date,
984 c_wo_rec.transaction_type_code,
985 c_wo_rec.invoice_distribution_id,
986 c_wo_rec.inventory_transaction_id,
987 c_wo_rec.inventory_organization_id,
988 p_ou_id,
989 sysdate, --last_update_date,
990 FND_GLOBAL.USER_ID, --last_updated_by,
991 FND_GLOBAL.USER_ID, --last_update_login,
992 sysdate, --creation_date,
993 FND_GLOBAL.USER_ID, --created_by,
994 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
995 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
996 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
997 sysdate, --program_update_date,
998 c_wo_rec.ae_header_id,
999 c_wo_rec.ae_line_num
1000 )
1001 select c_wo_rec.l_wo_id,
1002 c_wo_rec.accrual_account_id,
1003 c_wo_rec.transaction_date,
1004 c_wo_rec.amount,
1005 c_wo_rec.entered_amount,
1006 c_wo_rec.quantity,
1007 c_wo_rec.currency_code,
1008 c_wo_rec.currency_conversion_type,
1009 c_wo_rec.currency_conversion_rate,
1010 c_wo_rec.currency_conversion_date,
1011 c_wo_rec.transaction_type_code,
1012 c_wo_rec.invoice_distribution_id,
1013 c_wo_rec.inventory_transaction_id,
1014 c_wo_rec.po_distribution_id,
1015 c_wo_rec.inventory_item_id,
1016 c_wo_rec.vendor_id,
1017 c_wo_rec.inventory_organization_id,
1018 c_wo_rec.operating_unit_id,
1019 c_wo_rec.ae_header_id,
1020 c_wo_rec.ae_line_num
1021 from cst_misc_reconciliation
1022 where rownum = 1;
1023 --}
1024 end loop; /* for c_wo_rec in c_wo(p_ou_id) */
1025
1026 l_stmt_num := 30;
1027
1028 /* Delete written-off transactions from Costing's Miscellaneous table */
1029 delete from cst_misc_reconciliation
1030 where operating_unit_id = p_ou_id
1031 and write_off_select_flag = 'Y';
1032
1033
1034 l_stmt_num := 35;
1035
1036 /*
1037 Call SLA's bulk events generator which uses the values previously
1038 inserted into SLA's event temp table
1039 */
1040 xla_events_pub_pkg.create_bulk_events(p_source_application_id => 201,
1041 p_application_id => 707,
1042 p_ledger_id => p_sob_id,
1043 p_entity_type_code => 'WO_ACCOUNTING_EVENTS');
1044
1045 commit;
1046 --}
1047 else
1048 --{
1049 x_count := -1;
1050 return;
1051 --}
1052 end if; /* l_rows > 0 */
1053
1054 x_count := l_rows;
1055 return;
1056
1057 exception
1058 when others then
1059 --{
1060 rollback;
1061 x_count := -1;
1062 x_err_num := SQLCODE;
1063 x_err_code := NULL;
1064 x_err_msg := 'CST_Accrual_Rec_PVT.insert_misc_data_all() ' || SQLERRM;
1065 fnd_message.set_name('BOM','CST_UNEXPECTED');
1066 fnd_message.set_token('TOKEN',SQLERRM);
1067 if(l_unLog) then
1068 fnd_log.message(fnd_log.level_unexpected, g_log_header || '.' || l_api_name
1069 || '(' || to_char(l_stmt_num) || ')', FALSE);
1070 end if;
1071 fnd_msg_pub.add;
1072 return;
1073 --}
1074 end insert_misc_data_all;
1075
1076 -- Start of comments
1077 -- API name : Insert_Appo_Data_All
1078 -- Type : Private
1079 -- Pre-reqs : None.
1080 -- Function : Write-off PO distributions selected in the AP and PO
1081 -- Accrual Write-Off Form in Costing tables. Proecedue will also generate
1082 -- Write-Off events in SLA. A single write-off event will be generated
1083 -- regardless of the number of transactions that make up the PO distribution.
1084 -- At the end, all the written-off PO distributions
1085 -- and individual AP and PO transactions are removed from
1086 -- cst_reconciliation_summary and cst_ap_po_reconciliation..
1087 -- Parameters :
1088 -- IN : p_wo_date IN DATE Required
1089 -- Write-Off Date
1090 -- : p_rea_id IN NUMBER Optional
1091 -- Write-Off Reason
1092 -- : p_comments IN VARCHAR2 Optional
1093 -- Write-Off Comments
1094 -- : p_sob_id IN NUMBER Required
1095 -- Ledger/Set of Books
1096 -- : p_ou_id IN NUMBER Required
1097 -- Operating Unit Identifier
1098 -- OUT : x_count OUT NOCOPY NUBMER Required
1099 -- Success Indicator
1100 -- {x > 0} => Success
1101 -- -1 => Failure
1102 -- : x_err_num OUT NOCOPY NUMBER Required
1103 -- Standard Error Parameter
1104 -- : x_err_code OUT NOCOPY VARCHAR2 Required
1105 -- Standard Error Parameter
1106 -- : x_err_msg OUT NOCOPY VARCHAR2 Required
1107 -- Standard Error Parameter
1108 -- Version : Current version 1.0
1109 -- Previous version 1.0
1110 -- Initial version 1.0
1111 -- End of comments
1112 procedure insert_appo_data_all(
1113 p_wo_date in date,
1114 p_rea_id in number,
1115 p_comments in varchar2,
1116 p_sob_id in number,
1117 p_ou_id in number,
1118 x_count out nocopy number,
1119 x_err_num out nocopy number,
1120 x_err_code out nocopy varchar2,
1121 x_err_msg out nocopy varchar2) is
1122
1123 l_api_version constant number := 1.0;
1124 l_api_name constant varchar2(30) := 'insert_appo_data_all';
1125 l_full_name constant varchar2(60) := g_pkg_name || '.' || l_api_name;
1126 l_module constant varchar2(60) := 'cst.plsql.' || l_full_name;
1127 l_uLog constant boolean := fnd_log.test(fnd_log.level_unexpected, l_module);
1128 l_unLog constant boolean := l_uLog and (fnd_log.level_unexpected >= g_log_level);
1129 l_errorLog constant boolean := l_uLog and (fnd_log.level_error >= g_log_level);
1130 l_exceptionLog constant boolean := l_errorLog and (fnd_log.level_exception >= g_log_level);
1131 l_pLog constant boolean := l_exceptionLog and (fnd_log.level_procedure >= g_log_level);
1132 l_sLog constant boolean := l_pLog and (fnd_log.level_statement >= g_log_level);
1133 l_stmt_num number;
1134 l_rows number;
1135 l_ent_sum number;
1136 l_off_id number;
1137 l_erv_id number;
1138 l_wo_cc varchar2(30);
1139 l_wo_ct varchar2(30);
1140 l_wo_cr number;
1141 l_wo_cd date;
1142
1143 /* Cusor to hold all the PO distributions selected in the AP and PO form*/
1144 cursor c_wo(l_ou_id number) is
1145 select po_accrual_write_offs_s.nextval l_wo_id,
1146 (po_balance + ap_balance + write_off_balance) l_tot_bal,
1147 po_distribution_id,
1148 accrual_account_id,
1149 destination_type_code,
1150 inventory_item_id,
1151 vendor_id,
1152 operating_unit_id,
1153 last_update_date,
1154 last_updated_by,
1155 last_update_login,
1156 creation_date,
1157 created_by,
1158 request_id,
1159 program_application_id,
1160 program_id,
1161 program_update_date
1162 from cst_reconciliation_summary
1163 where operating_unit_id = l_ou_id
1164 and write_off_select_flag = 'Y';
1165
1166 begin
1167
1168 l_stmt_num := 5;
1169
1170 if(l_pLog) then
1171 fnd_log.string(fnd_log.level_procedure, g_log_header || '.' || l_api_name ||
1172 '.begin', 'insert_appo_data_all << '
1173 || 'p_wo_date := ' || to_char(p_wo_date, 'YYYY/MM/DD HH24:MI:SS')
1174 || 'p_rea_id := ' || to_char(p_rea_id)
1175 || 'p_comments := ' || p_comments
1176 || 'p_sob_id := ' || to_char(p_sob_id)
1177 || 'p_ou_id := ' || to_char(p_ou_id));
1178 end if;
1179
1180 /* Print out the parameters to the Message Stack */
1181 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'));
1182 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Write-Off Reason: ' || to_char(p_rea_id));
1183 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Comments: ' || p_comments);
1184 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Set of Books: ' || to_char(p_sob_id));
1185 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Operating Unit: ' || to_char(p_ou_id));
1186
1187 l_stmt_num := 10;
1188
1189 /* Make sure user selected PO distributions to write-off */
1190 select count(*)
1191 into l_rows
1192 from cst_reconciliation_summary
1193 where operating_unit_id = p_ou_id
1194 and write_off_select_flag = 'Y';
1195
1196 if(l_rows > 0) then
1197 --{
1198 l_stmt_num := 15;
1199
1200 for c_wo_rec in c_wo(p_ou_id) loop
1201 --{
1202 /* Insert necessary information into SLA events temp table */
1203 insert into xla_events_int_gt
1204 (
1205 application_id,
1206 ledger_id,
1207 entity_code,
1208 source_id_int_1,
1209 event_class_code,
1210 event_type_code,
1211 event_date,
1212 event_status_code,
1213 --BUG#7226250
1214 security_id_int_2,
1215 transaction_date,
1216 reference_date_1,
1217 transaction_number
1218 )
1219 values
1220 (
1221 707,
1222 p_sob_id,
1223 'WO_ACCOUNTING_EVENTS',
1224 c_wo_rec.l_wo_id,
1225 'ACCRUAL_WRITE_OFF',
1226 'ACCRUAL_WRITE_OFF',
1227 p_wo_date,
1228 XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
1229 p_ou_id,
1230 p_wo_date,
1231 INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(p_wo_date,p_ou_id),
1232 to_char(c_wo_rec.l_wo_id)
1233 );
1234
1235 l_stmt_num := 20;
1236
1237 /*
1238 Insert the individual AP and/or PO transactions into
1239 the write-off details table
1240 */
1241 insert into cst_write_off_details
1242 (
1243 write_off_id,
1244 transaction_date,
1245 amount,
1246 entered_amount,
1247 quantity,
1248 currency_code,
1249 currency_conversion_type,
1250 currency_conversion_rate,
1251 currency_conversion_date,
1252 transaction_type_code,
1253 rcv_transaction_id,
1254 invoice_distribution_id,
1255 write_off_transaction_id,
1256 inventory_organization_id,
1257 operating_unit_id,
1258 last_update_date,
1259 last_updated_by,
1260 last_update_login,
1261 creation_date,
1262 created_by,
1263 request_id,
1264 program_application_id,
1265 program_id,
1266 program_update_date,
1267 ae_header_id,
1268 ae_line_num
1269 )
1270 select c_wo_rec.l_wo_id,
1271 capr.transaction_date,
1272 capr.amount,
1273 capr.entered_amount,
1274 capr.quantity,
1275 capr.currency_code,
1276 capr.currency_conversion_type,
1277 capr.currency_conversion_rate,
1278 capr.currency_conversion_date,
1279 capr.transaction_type_code,
1280 capr.rcv_transaction_id,
1281 capr.invoice_distribution_id,
1282 capr.write_off_id,
1283 capr.inventory_organization_id,
1284 capr.operating_unit_id,
1285 sysdate, --last_update_date,
1286 FND_GLOBAL.USER_ID, --last_updated_by,
1287 FND_GLOBAL.USER_ID, --last_update_login,
1288 sysdate, --creation_date,
1289 FND_GLOBAL.USER_ID, --created_by,
1290 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
1291 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
1292 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
1293 sysdate, --program_update_date,
1294 capr.ae_header_id,
1295 capr.ae_line_num
1296 from cst_ap_po_reconciliation capr
1297 where capr.po_distribution_id = c_wo_rec.po_distribution_id
1298 and capr.accrual_account_id = c_wo_rec.accrual_account_id
1299 and capr.operating_unit_id = c_wo_rec.operating_unit_id;
1300
1301 l_stmt_num := 25;
1302
1303 /* Get the sum of the entered amount */
1304 select sum(capr.entered_amount)
1305 into l_ent_sum
1306 from cst_ap_po_reconciliation capr
1307 where capr.po_distribution_id = c_wo_rec.po_distribution_id
1308 and capr.accrual_account_id = c_wo_rec.accrual_account_id
1309 and capr.operating_unit_id = c_wo_rec.operating_unit_id;
1310
1311 /* Get all the currency information and offset/erv accounts based on the PO match type */
1312 /* the offset account is selected as follows.If the destination type code is Expense, get the charge account
1313 else get the variance account from the po distribution */
1314
1315 select decode(pod.destination_type_code,'EXPENSE',pod.code_combination_id,
1316 pod.variance_account_id
1317 ),
1318 decode(poll.match_option, 'P', pod.variance_account_id,
1319 decode(pod.destination_type_code,'EXPENSE', pod.code_combination_id,-1)),
1320 poh.currency_code,
1321 poh.rate_type,
1322 decode(poll.match_option, 'P', pod.rate_date, trunc(p_wo_date))
1323 into l_off_id,
1324 l_erv_id,
1325 l_wo_cc,
1326 l_wo_ct,
1327 l_wo_cd
1328 from po_distributions_all pod,
1329 po_line_locations_all poll,
1330 po_headers_all poh
1331 where pod.po_distribution_id = c_wo_rec.po_distribution_id
1332 and pod.org_id = p_ou_id
1333 and poh.po_header_id = pod.po_header_id
1334 and poll.line_location_id = pod.line_location_id;
1335
1336 l_stmt_num := 26;
1337
1338 /* For the case of match to receipt, when NO rate is defined, use the rate and the currency conversion date from
1339 the po header */
1340
1341 BEGIN
1342
1343 select decode(poll.match_option, 'P',NVL(pod.rate,1),
1344 gl_currency_api.get_rate(poh.currency_code, gsb.currency_code,
1345 trunc(p_wo_date),poh.rate_type)
1346 )
1347 into l_wo_cr
1348 from po_distributions_all pod,
1349 po_line_locations_all poll,
1350 po_headers_all poh,
1351 gl_sets_of_books gsb
1352 where pod.po_distribution_id = c_wo_rec.po_distribution_id
1353 and pod.org_id = p_ou_id
1354 and poh.po_header_id = pod.po_header_id
1355 and poll.line_location_id = pod.line_location_id
1356 and gsb.set_of_books_id = pod.set_of_books_id ;
1357
1358 EXCEPTION
1359 WHEN gl_currency_api.NO_RATE THEN
1360
1361 Select NVL(pod.rate,1),
1362 pod.rate_date
1363 into l_wo_cr,
1364 l_wo_cd
1365 from po_distributions_all pod
1366 where pod.po_distribution_id = c_wo_rec.po_distribution_id
1367 and pod.org_id = p_ou_id ;
1368
1369 END;
1370
1371 l_stmt_num := 28;
1372
1373 /* Need to further determine ERV account if erv_id = -1 */
1374
1375 if(((l_wo_cr is null) or (l_ent_sum is null)) and (l_erv_id is not null)) then
1376 --{
1377 l_erv_id := null;
1378 --}
1379 elsif(l_erv_id = -1) then
1380 --{
1381 if(c_wo_rec.l_tot_bal > (l_wo_cr * l_ent_sum)) then
1382 --{
1383 select rate_var_gain_ccid
1384 into l_erv_id
1385 from financials_system_params_all
1386 where org_id = p_ou_id;
1387 --}
1388 else
1389 --{
1390 select rate_var_loss_ccid
1391 into l_erv_id
1392 from financials_system_params_all
1393 where org_id = p_ou_id;
1394 --}
1395 end if; /* c_wo_rec.l_tot_bal > (l_wo_cr + l_ent_sum) */
1396 --}
1397 end if; /* ((l_wo_cr is null) or (l_ent_sum is null)) and (l_erv_id is not null) */
1398
1399 l_stmt_num := 30;
1400
1401 /*
1402 Insert the PO distribution information, as well as the extra values
1403 recently calcuated into the write-off headers table.
1404 */
1405 insert into cst_write_offs
1406 (
1407 write_off_id,
1408 transaction_date,
1409 accrual_account_id,
1410 offset_account_id,
1411 erv_account_id,
1412 write_off_amount,
1413 entered_amount,
1414 currency_code,
1415 currency_conversion_type,
1416 currency_conversion_rate,
1417 currency_conversion_date,
1418 transaction_type_code,
1419 po_distribution_id,
1420 reason_id,
1421 comments,
1422 destination_type_code,
1423 inventory_item_id,
1424 vendor_id,
1425 operating_unit_id,
1426 last_update_date,
1427 last_updated_by,
1428 last_update_login,
1429 creation_date,
1430 created_by,
1431 request_id,
1432 program_application_id,
1433 program_id,
1434 program_update_date
1435 )
1436 values
1437 (
1438 c_wo_rec.l_wo_id,
1439 p_wo_date,
1440 c_wo_rec.accrual_account_id,
1441 l_off_id,
1442 l_erv_id,
1443 (-1) * c_wo_rec.l_tot_bal,
1444 (-1) * l_ent_sum,
1445 l_wo_cc,
1446 l_wo_ct,
1447 l_wo_cr,
1448 l_wo_cd,
1449 'WRITE OFF',
1450 c_wo_rec.po_distribution_id,
1451 p_rea_id,
1452 p_comments,
1453 c_wo_rec.destination_type_code,
1454 c_wo_rec.inventory_item_id,
1455 c_wo_rec.vendor_id,
1456 p_ou_id,
1457 sysdate, --last_update_date,
1458 FND_GLOBAL.USER_ID, --last_updated_by,
1459 FND_GLOBAL.USER_ID, --last_update_login,
1460 sysdate, --creation_date,
1461 FND_GLOBAL.USER_ID, --created_by,
1462 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
1463 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
1464 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
1465 sysdate --program_update_date
1466 );
1467 --}
1468 end loop; /* for c_wo_rec in c_wo(p_ou_id) */
1469
1470 l_stmt_num := 35;
1471 /*
1472 First delete the individual transactions from cst_ap_po_reconciliation
1473 as to maintain referential integretiy.
1474 */
1475 delete from cst_ap_po_reconciliation capr
1476 where exists (
1477 select 'X'
1478 from cst_reconciliation_summary crs
1479 where capr.operating_unit_id = crs.operating_unit_id
1480 and capr.po_distribution_id = crs.po_distribution_id
1481 and capr.accrual_account_id = crs.accrual_account_id
1482 and crs.write_off_select_flag = 'Y');
1483
1484 l_stmt_num := 40;
1485
1486 /*
1487 Once all the individual transaction have been deleted, removed the
1488 header information from cst_reconciliation_summary
1489 */
1490 delete from cst_reconciliation_summary
1491 where operating_unit_id = p_ou_id
1492 and write_off_select_flag = 'Y';
1493
1494 l_stmt_num := 45;
1495 /*
1496 Call SLA's bulk events generator which uses the values previously
1497 inserted into SLA's event temp table
1498 */
1499 xla_events_pub_pkg.create_bulk_events(p_source_application_id => 201,
1500 p_application_id => 707,
1501 p_ledger_id => p_sob_id,
1502 p_entity_type_code => 'WO_ACCOUNTING_EVENTS');
1503
1504 commit;
1505 --}
1506 else
1507 --{
1508 x_count := -1;
1509 return;
1510 --}
1511 end if; /* l_rows > 0 */
1512
1513 x_count := l_rows;
1514 return;
1515
1516 exception
1517 when others then
1518 --{
1519 rollback;
1520 x_count := -1;
1521 x_err_num := SQLCODE;
1522 x_err_code := NULL;
1523 x_err_msg := 'CST_Accrual_Rec_PVT.insert_appo_data_all() ' || SQLERRM;
1524 fnd_message.set_name('BOM','CST_UNEXPECTED');
1525 fnd_message.set_token('TOKEN',SQLERRM);
1526 if(l_unLog) then
1527 fnd_log.message(fnd_log.level_unexpected, g_log_header || '.' || l_api_name
1528 || '(' || to_char(l_stmt_num) || ')', FALSE);
1529 end if;
1530 fnd_msg_pub.add;
1531 return;
1532 --}
1533 end insert_appo_data_all;
1534
1535 -- Start of comments
1536 -- API name : Is_Reversible
1537 -- Type : Private
1538 -- Pre-reqs : None.
1539 -- Function : Checks whether a specific write-off distribution is reversible.
1540 -- A write-off is reversible if the write-off was performed in release 12 and later,
1541 -- has the transaction type code 'WRITE OFF', has not alredy been reversed and is
1542 -- not already part of another write-off distribution.
1543 -- Parameters :
1544 -- IN : p_wo_id IN NUMBER Required
1545 -- Write-Off Date
1546 -- : p_txn_c IN VARCHAR2 Required
1547 -- Transaction Type
1548 -- : p_off_id IN NUMBER Required
1549 -- Offset Accont
1550 -- : p_ou_id IN NUMBER Required
1551 -- Operating Unit Identifier
1552 -- OUT : x_count OUT NOCOPY NUBMER Required
1553 -- Reversible Indicator
1554 -- FND_API.G_TRUE => Reversible
1555 -- FND_API.G_FALSE => Not Reversible
1556 -- : x_err_num OUT NOCOPY NUMBER Required
1557 -- Standard Error Parameter
1558 -- : x_err_code OUT NOCOPY VARCHAR2 Required
1559 -- Standard Error Parameter
1560 -- : x_err_msg OUT NOCOPY VARCHAR2 Required
1561 -- Standard Error Parameter
1562 -- Version : Current version 1.0
1563 -- Previous version 1.0
1564 -- Initial version 1.0
1565 -- End of comments
1566 procedure is_reversible(
1567 p_wo_id in number,
1568 p_txn_c in varchar2,
1569 p_off_id in number,
1570 p_ou_id in number,
1571 x_count out nocopy varchar2,
1572 x_err_num out nocopy number,
1573 x_err_code out nocopy varchar2,
1574 x_err_msg out nocopy varchar2) is
1575
1576 l_api_version constant number := 1.0;
1577 l_api_name constant varchar2(30) := 'is_reversible';
1578 l_full_name constant varchar2(60) := g_pkg_name || '.' || l_api_name;
1579 l_module constant varchar2(60) := 'cst.plsql.' || l_full_name;
1580 l_uLog constant boolean := fnd_log.test(fnd_log.level_unexpected, l_module);
1581 l_unLog constant boolean := l_uLog and (fnd_log.level_unexpected >= g_log_level);
1582 l_errorLog constant boolean := l_uLog and (fnd_log.level_error >= g_log_level);
1583 l_exceptionLog constant boolean := l_errorLog and (fnd_log.level_exception >= g_log_level);
1584 l_pLog constant boolean := l_exceptionLog and (fnd_log.level_procedure >= g_log_level);
1585 l_sLog constant boolean := l_pLog and (fnd_log.level_statement >= g_log_level);
1586 l_stmt_num number;
1587 l_enabled number;
1588
1589 begin
1590
1591 l_stmt_num := 5;
1592
1593 if(l_pLog) then
1594 fnd_log.string(fnd_log.level_procedure, g_log_header || '.' || l_api_name ||
1595 '.begin', 'is_reversible << '
1596 || 'p_wo_id := ' || to_char(p_wo_id)
1597 || 'p_txn_c := ' || p_txn_c
1598 || 'p_off_id := ' || to_char(p_off_id)
1599 || 'p_ou_id := ' || to_char(p_ou_id));
1600 end if;
1601
1602 /* Print out the parameters to the Message Stack */
1603 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Write-Of ID: ' || to_char(p_wo_id));
1604 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Transaction Type: ' || p_txn_c);
1605 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Offset Account: ' || to_char(p_off_id));
1606 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Operating Unit: ' || to_char(p_ou_id));
1607
1608 l_stmt_num := 10;
1609
1610 /* Make sure distribution is not already a reversal */
1611 if(p_txn_c = 'WRITE OFF') then
1612 --{
1613 l_stmt_num := 15;
1614 /*
1615 Check whether the write-off was done in Release 12 or later
1616 Pre-Release 12 write-offs will not have an offset account
1617 */
1618 if(p_off_id is not null) then
1619 --{
1620 l_stmt_num := 20;
1621 /*
1622 Check whether current write-off has not already been reversed by
1623 searching the header table to see if the current distributions
1624 write-off ID is another distributions reversal ID
1625 */
1626 select count(*)
1627 into l_enabled
1628 from cst_write_offs
1629 where reversal_id = p_wo_id
1630 and operating_unit_id = p_ou_id;
1631 if(l_enabled = 0) then
1632 --{
1633 l_stmt_num := 25;
1634 /*
1635 Finally check whether the current distribution has been part
1636 of another write-off by taking the current distribution's
1637 write-off ID and seeing if it matches a row's
1638 write_off_transaction_id column in the details table
1639 */
1640 select count(*)
1641 into l_enabled
1642 from cst_write_off_details
1643 where write_off_transaction_id = p_wo_id
1644 and operating_unit_id = p_ou_id;
1645 if(l_enabled = 0) then
1646 --{
1647 l_stmt_num := 30;
1648 /* If all the tests pass, write-off is reversible */
1649 x_count := fnd_api.g_true;
1650 return;
1651 --}
1652 end if; /* l_enabled = 0 */
1653 --}
1654 end if; /* l_enabled = 0 */
1655 --}
1656 end if; /* p_off_id is not null */
1657 --}
1658 end if; /* p_txn_c = 'WRITE OFF' */
1659
1660 /* Indicates a test failed and therefore the write-off is not reversible */
1661 x_count := fnd_api.g_false;
1662 return;
1663
1664 exception
1665 when others then
1666 rollback;
1667 x_count := fnd_api.g_false;
1668 x_err_num := SQLCODE;
1669 x_err_code := NULL;
1670 x_err_msg := 'CST_Accrual_Rec_PVT.is_reversible() ' || SQLERRM;
1671 fnd_message.set_name('BOM','CST_UNEXPECTED');
1672 fnd_message.set_token('TOKEN',SQLERRM);
1673 if(l_unLog) then
1674 fnd_log.message(fnd_log.level_unexpected, g_log_header || '.' || l_api_name
1675 || '(' || to_char(l_stmt_num) || ')', FALSE);
1676 end if;
1677 fnd_msg_pub.add;
1678 return;
1679
1680 end is_reversible;
1681
1682 -- Start of comments
1683 -- API name : Reverse_Write_Offs
1684 -- Type : Private
1685 -- Pre-reqs : None.
1686 -- Function : Performs a write-off reversal and insert distributions and/or
1687 -- individual transactions back into the appropriate tables.
1688 -- If the reversing miscellaneous write-offs, then a write-off
1689 -- reversal is created and the individual miscellaneous transactions
1690 -- is inserted back into cst_misc_reconciliation. If reversing an
1691 -- AP and PO distribution, then a write-off reversal is created and all
1692 -- the individual AP and PO transactions in addition to all write-offs
1693 -- and reversals sharing the same PO distribution ID and accrual account
1694 -- are summed up and if they equal a non-zero value, they are inserted
1695 -- into the cst_reconciliation_summary and cst_ap_po_reconciliation
1696 -- as appropriate (see package body).
1697 -- Parameters :
1698 -- IN : p_wo_date IN DATE Required
1699 -- Write-Off Date
1700 -- : p_rea_id IN NUMBER Optional
1701 -- Write-Off Reason
1702 -- : p_comments IN VARCHAR2 Optional
1703 -- Write-Off Comments
1704 -- : p_sob_id IN NUMBER Required
1705 -- Ledger/Set of Books
1706 -- : p_ou_id IN NUMBER Required
1707 -- Operating Unit Identifier
1708 -- OUT : x_count OUT NOCOPY NUBMER Required
1709 -- Success Indicator
1710 -- {x > 0} => Success
1711 -- -1 => Failure
1712 -- : x_err_num OUT NOCOPY NUMBER Required
1713 -- Standard Error Parameter
1714 -- : x_err_code OUT NOCOPY VARCHAR2 Required
1715 -- Standard Error Parameter
1716 -- : x_err_msg OUT NOCOPY VARCHAR2 Required
1717 -- Standard Error Parameter
1718 -- Version : Current version 1.0
1719 -- Previous version 1.0
1720 -- Initial version 1.0
1721 -- End of comments
1722 procedure reverse_write_offs(
1723 p_wo_date in date,
1724 p_rea_id in number,
1725 p_comments in varchar2,
1726 p_sob_id in number,
1727 p_ou_id in number,
1728 x_count out nocopy number,
1729 x_err_num out nocopy number,
1730 x_err_code out nocopy varchar2,
1731 x_err_msg out nocopy varchar2) is
1732
1733 l_api_version constant number := 1.0;
1734 l_api_name constant varchar2(30) := 'reverse_write_offs';
1735 l_full_name constant varchar2(60) := g_pkg_name || '.' || l_api_name;
1736 l_module constant varchar2(60) := 'cst.plsql.' || l_full_name;
1737 l_uLog constant boolean := fnd_log.test(fnd_log.level_unexpected, l_module);
1738 l_unLog constant boolean := l_uLog and (fnd_log.level_unexpected >= g_log_level);
1739 l_errorLog constant boolean := l_uLog and (fnd_log.level_error >= g_log_level);
1740 l_exceptionLog constant boolean := l_errorLog and (fnd_log.level_exception >= g_log_level);
1741 l_pLog constant boolean := l_exceptionLog and (fnd_log.level_procedure >= g_log_level);
1742 l_sLog constant boolean := l_pLog and (fnd_log.level_statement >= g_log_level);
1743 l_stmt_num number;
1744 l_rows number;
1745 l_po_proc number;
1746 /* Cursor to hold all the distributions marked for reversal */
1747 cursor c_wo(l_ou_id number) is
1748 select po_accrual_write_offs_s.nextval l_wo_id,
1749 write_off_id l_rev_id,
1750 accrual_account_id,
1751 offset_account_id,
1752 erv_account_id,
1753 write_off_amount amount,
1754 entered_amount,
1755 currency_code,
1756 currency_conversion_type,
1757 currency_conversion_rate,
1758 currency_conversion_date,
1759 po_distribution_id,
1760 invoice_distribution_id,
1761 inventory_transaction_id,
1762 destination_type_code,
1763 inventory_item_id,
1764 vendor_id,
1765 legal_entity_id,
1766 operating_unit_id,
1767 last_update_date,
1768 last_updated_by,
1769 last_update_login,
1770 creation_date,
1771 created_by,
1772 request_id,
1773 program_application_id,
1774 program_id,
1775 program_update_date
1776 from cst_write_offs
1777 where operating_unit_id = l_ou_id
1778 and write_off_select_flag = 'Y';
1779
1780 begin
1781
1782 l_stmt_num := 5;
1783
1784 if(l_pLog) then
1785 fnd_log.string(fnd_log.level_procedure, g_log_header || '.' || l_api_name ||
1786 '.begin', 'reverse_write_offs << '
1787 || 'p_wo_date := ' || to_char(p_wo_date, 'YYYY/MM/DD HH24:MI:SS')
1788 || 'p_rea_id := ' || to_char(p_rea_id)
1789 || 'p_comments := ' || p_comments
1790 || 'p_sob_id := ' || to_char(p_sob_id)
1791 || 'p_ou_id := ' || to_char(p_ou_id));
1792 end if;
1793
1794 /* Print out the parameters to the Message Stack */
1795 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'));
1796 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Reversal Reason: ' || to_char(p_rea_id));
1797 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Comments: ' || p_comments);
1798 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Set of Books: ' || to_char(p_sob_id));
1799 fnd_msg_pub.add_exc_msg(g_pkg_name, l_api_name, 'Operating Unit: ' || to_char(p_ou_id));
1800
1801 l_stmt_num := 10;
1802
1803 /* Check that the user has selected distributions to reverse */
1804 select count(*)
1805 into l_rows
1806 from cst_write_offs
1807 where operating_unit_id = p_ou_id
1808 and write_off_select_flag = 'Y';
1809
1810 if(l_rows > 0) then
1811 --{
1812 l_stmt_num := 15;
1813
1814 for c_wo_rec in c_wo(p_ou_id) loop
1815 --{
1816 /* Insert the necessary information into SLA's event temp table */
1817 insert into xla_events_int_gt
1818 (
1819 application_id,
1820 ledger_id,
1821 legal_entity_id,
1822 entity_code,
1823 source_id_int_1,
1824 event_class_code,
1825 event_type_code,
1826 event_date,
1827 event_status_code,
1828 --BUG#7226250
1829 security_id_int_2,
1830 transaction_date,
1831 reference_date_1,
1832 transaction_number
1833 )
1834 values
1835 (
1836 707,
1837 p_sob_id,
1838 c_wo_rec.legal_entity_id,
1839 'WO_ACCOUNTING_EVENTS',
1840 c_wo_rec.l_wo_id,
1841 'ACCRUAL_WRITE_OFF',
1842 'ACCRUAL_WRITE_OFF',
1843 p_wo_date,
1844 XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
1845 p_ou_id,
1846 p_wo_date,
1847 INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(p_wo_date,p_ou_id),
1848 to_char(c_wo_rec.l_wo_id)
1849 );
1850
1851 l_stmt_num := 20;
1852
1853 /* Insert the reversal into the headers table */
1854 insert into cst_write_offs
1855 (
1856 write_off_id,
1857 transaction_date,
1858 accrual_account_id,
1859 offset_account_id,
1860 erv_account_id,
1861 write_off_amount,
1862 entered_amount,
1863 currency_code,
1864 currency_conversion_type,
1865 currency_conversion_rate,
1866 currency_conversion_date,
1867 transaction_type_code,
1868 po_distribution_id,
1869 invoice_distribution_id,
1870 inventory_transaction_id,
1871 reversal_id,
1872 reason_id,
1873 comments,
1874 destination_type_code,
1875 inventory_item_id,
1876 vendor_id,
1877 legal_entity_id,
1878 operating_unit_id,
1879 last_update_date,
1880 last_updated_by,
1881 last_update_login,
1882 creation_date,
1883 created_by,
1884 request_id,
1885 program_application_id,
1886 program_id,
1887 program_update_date
1888 )
1889 values
1890 (
1891 c_wo_rec.l_wo_id,
1892 p_wo_date,
1893 c_wo_rec.accrual_account_id,
1894 c_wo_rec.offset_account_id,
1895 c_wo_rec.erv_account_id,
1896 (-1) * c_wo_rec.amount,
1897 (-1) * c_wo_rec.entered_amount,
1898 c_wo_rec.currency_code,
1899 c_wo_rec.currency_conversion_type,
1900 c_wo_rec.currency_conversion_rate,
1901 c_wo_rec.currency_conversion_date,
1902 'REVERSE WRITE OFF',
1903 c_wo_rec.po_distribution_id,
1904 c_wo_rec.invoice_distribution_id,
1905 c_wo_rec.inventory_transaction_id,
1906 c_wo_rec.l_rev_id,
1907 p_rea_id,
1908 p_comments,
1909 c_wo_rec.destination_type_code,
1910 c_wo_rec.inventory_item_id,
1911 c_wo_rec.vendor_id,
1912 c_wo_rec.legal_entity_id,
1913 c_wo_rec.operating_unit_id,
1914 sysdate, --last_update_date,
1915 FND_GLOBAL.USER_ID, --last_updated_by,
1916 FND_GLOBAL.USER_ID, --last_update_login,
1917 sysdate, --creation_date,
1918 FND_GLOBAL.USER_ID, --created_by,
1919 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
1920 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
1921 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
1922 sysdate --program_update_date
1923 );
1924
1925 l_stmt_num := 25;
1926
1927 /*
1928 Insert the details from the previous write-off but with the new write-off ID
1929 into the write-off details table
1930 */
1931 insert into cst_write_off_details
1932 (
1933 write_off_id,
1934 transaction_date,
1935 amount,
1936 entered_amount,
1937 quantity,
1938 currency_code,
1939 currency_conversion_type,
1940 currency_conversion_rate,
1941 currency_conversion_date,
1942 transaction_type_code,
1943 rcv_transaction_id,
1944 invoice_distribution_id,
1945 inventory_transaction_id,
1946 write_off_transaction_id,
1947 inventory_organization_id,
1948 operating_unit_id,
1949 last_update_date,
1950 last_updated_by,
1951 last_update_login,
1952 creation_date,
1953 created_by,
1954 request_id,
1955 program_application_id,
1956 program_id,
1957 program_update_date,
1958 ae_header_id,
1959 ae_line_num
1960 )
1961 select c_wo_rec.l_wo_id,
1962 cwod.transaction_date,
1963 cwod.amount,
1964 cwod.entered_amount,
1965 cwod.quantity,
1966 cwod.currency_code,
1967 cwod.currency_conversion_type,
1968 cwod.currency_conversion_rate,
1969 cwod.currency_conversion_date,
1970 cwod.transaction_type_code,
1971 cwod.rcv_transaction_id,
1972 cwod.invoice_distribution_id,
1973 cwod.inventory_transaction_id,
1974 cwod.write_off_transaction_id,
1975 cwod.inventory_organization_id,
1976 cwod.operating_unit_id,
1977 sysdate, --last_update_date,
1978 FND_GLOBAL.USER_ID, --last_updated_by,
1979 FND_GLOBAL.USER_ID, --last_update_login,
1980 sysdate, --creation_date,
1981 FND_GLOBAL.USER_ID, --created_by,
1982 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
1983 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
1984 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
1985 sysdate, --program_update_date,
1986 cwod.ae_header_id,
1987 cwod.ae_line_num
1988 from cst_write_off_details cwod
1989 where cwod.write_off_id = c_wo_rec.l_rev_id
1990 and cwod.operating_unit_id = c_wo_rec.operating_unit_id;
1991
1992 l_stmt_num := 30;
1993
1994 /* Need to re-insert transations, either Miscellaneous or AP-PO*/
1995 /* Doing Miscellaneous */
1996 if((c_wo_rec.po_distribution_id is null) or
1997 (c_wo_rec.inventory_transaction_id is not null and c_wo_rec.po_distribution_id is not null) or
1998 (c_wo_rec.invoice_distribution_id is not null)) then
1999 --{
2000 l_stmt_num := 35;
2001
2002 insert into cst_misc_reconciliation
2003 (
2004 transaction_date,
2005 amount,
2006 entered_amount,
2007 quantity,
2008 currency_code,
2009 currency_conversion_type,
2010 currency_conversion_rate,
2011 currency_conversion_date,
2012 invoice_distribution_id,
2013 inventory_transaction_id,
2014 po_distribution_id,
2015 accrual_account_id,
2016 transaction_type_code,
2017 inventory_item_id,
2018 vendor_id,
2019 inventory_organization_id,
2020 operating_unit_id,
2021 last_update_date,
2022 last_updated_by,
2023 last_update_login,
2024 creation_date,
2025 created_by,
2026 request_id,
2027 program_application_id,
2028 program_id,
2029 program_update_date,
2030 ae_header_id,
2031 ae_line_num
2032 )
2033 select cwod.transaction_date,
2034 cwod.amount,
2035 cwod.entered_amount,
2036 cwod.quantity,
2037 cwod.currency_code,
2038 cwod.currency_conversion_type,
2039 cwod.currency_conversion_rate,
2040 cwod.currency_conversion_date,
2041 cwod.invoice_distribution_id,
2042 cwod.inventory_transaction_id,
2043 cwo.po_distribution_id,
2044 cwo.accrual_account_id,
2045 cwod.transaction_type_code,
2046 cwo.inventory_item_id,
2047 cwo.vendor_id,
2048 cwod.inventory_organization_id,
2049 cwod.operating_unit_id,
2050 sysdate, --last_update_date,
2051 FND_GLOBAL.USER_ID, --last_updated_by,
2052 FND_GLOBAL.USER_ID, --last_update_login,
2053 sysdate, --creation_date,
2054 FND_GLOBAL.USER_ID, --created_by,
2055 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
2056 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
2057 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
2058 sysdate, --program_update_date,
2059 cwod.ae_header_id,
2060 cwod.ae_line_num
2061 from cst_write_off_details cwod,
2062 cst_write_offs cwo
2063 where cwo.write_off_id = c_wo_rec.l_wo_id
2064 and cwo.operating_unit_id = c_wo_rec.operating_unit_id
2065 and cwod.write_off_id = cwo.write_off_id
2066 and cwod.operating_unit_id = cwo.operating_unit_id;
2067
2068 l_stmt_num := 40;
2069 --}
2070 /* AP PO */
2071 elsif((c_wo_rec.po_distribution_id is not null) and
2072 (c_wo_rec.inventory_transaction_id is null) and
2073 (c_wo_rec.invoice_distribution_id is null)) then
2074 --{
2075 l_stmt_num := 45;
2076
2077 /*
2078 Look whether a rebuild has occurred, meaning CRS will have rows for a given
2079 po_distribution_id/accrual_account_id pair
2080 */
2081 select count(*)
2082 into l_po_proc
2083 from cst_reconciliation_summary
2084 where po_distribution_id = c_wo_rec.po_distribution_id
2085 and accrual_account_id = c_wo_rec.accrual_account_id
2086 and operating_unit_id = c_wo_rec.operating_unit_id;
2087
2088 /* No records in CRS so insert relevant records from CWOD and CWO */
2089 if(l_po_proc = 0) then
2090 --{
2091 l_stmt_num := 50;
2092
2093 insert into cst_ap_po_reconciliation
2094 (
2095 transaction_date,
2096 amount,
2097 entered_amount,
2098 quantity,
2099 currency_code,
2100 currency_conversion_type,
2101 currency_conversion_rate,
2102 currency_conversion_date,
2103 po_distribution_id,
2104 rcv_transaction_id,
2105 invoice_distribution_id,
2106 accrual_account_id,
2107 transaction_type_code,
2108 write_off_id,
2109 inventory_organization_id,
2110 operating_unit_id,
2111 last_update_date,
2112 last_updated_by,
2113 last_update_login,
2114 creation_date,
2115 created_by,
2116 request_id,
2117 program_application_id,
2118 program_id,
2119 program_update_date,
2120 ae_header_id,
2121 ae_line_num
2122 )
2123 select cwod.transaction_date,
2124 cwod.amount,
2125 cwod.entered_amount,
2126 cwod.quantity,
2127 cwod.currency_code,
2128 cwod.currency_conversion_type,
2129 cwod.currency_conversion_rate,
2130 cwod.currency_conversion_date,
2131 cwo.po_distribution_id,
2132 cwod.rcv_transaction_id,
2133 cwod.invoice_distribution_id,
2134 cwo.accrual_account_id,
2135 cwod.transaction_type_code,
2136 cwod.write_off_transaction_id,
2137 cwod.inventory_organization_id,
2138 cwod.operating_unit_id,
2139 sysdate, --last_update_date,
2140 FND_GLOBAL.USER_ID, --last_updated_by,
2141 FND_GLOBAL.USER_ID, --last_update_login,
2142 sysdate, --creation_date,
2143 FND_GLOBAL.USER_ID, --created_by,
2144 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
2145 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
2146 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
2147 sysdate, --program_update_date,
2148 cwod.ae_header_id,
2149 cwod.ae_line_num
2150 from cst_write_offs cwo,
2151 cst_write_off_details cwod
2152 where cwo.write_off_id = c_wo_rec.l_wo_id
2153 and cwo.po_distribution_id = c_wo_rec.po_distribution_id
2154 and cwo.accrual_account_id = c_wo_rec.accrual_account_id
2155 and cwo.operating_unit_id = c_wo_rec.operating_unit_id
2156 and cwod.write_off_id = cwo.write_off_id
2157 and cwod.operating_unit_id = cwo.operating_unit_id;
2158
2159 l_stmt_num := 55;
2160
2161 /* Next insert the new write-off header and reversal header into CAPR */
2162 insert into cst_ap_po_reconciliation
2163 (
2164 transaction_date,
2165 amount,
2166 entered_amount,
2167 currency_code,
2168 currency_conversion_type,
2169 currency_conversion_rate,
2170 currency_conversion_date,
2171 po_distribution_id,
2172 accrual_account_id,
2173 transaction_type_code,
2174 write_off_id,
2175 operating_unit_id,
2176 last_update_date,
2177 last_updated_by,
2178 last_update_login,
2179 creation_date,
2180 created_by,
2181 request_id,
2182 program_application_id,
2183 program_id,
2184 program_update_date
2185 )
2186 select cwo.transaction_date,
2187 cwo.write_off_amount,
2188 cwo.entered_amount,
2189 cwo.currency_code,
2190 cwo.currency_conversion_type,
2191 cwo.currency_conversion_rate,
2192 cwo.currency_conversion_date,
2193 cwo.po_distribution_id,
2194 cwo.accrual_account_id,
2195 cwo.transaction_type_code,
2196 cwo.write_off_id,
2197 cwo.operating_unit_id,
2198 sysdate, --last_update_date,
2199 FND_GLOBAL.USER_ID, --last_updated_by,
2200 FND_GLOBAL.USER_ID, --last_update_login,
2201 sysdate, --creation_date,
2202 FND_GLOBAL.USER_ID, --created_by,
2203 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
2204 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
2205 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
2206 sysdate --program_update_date
2207 from cst_write_offs cwo
2208 where cwo.write_off_id in (c_wo_rec.l_wo_id, c_wo_rec.l_rev_id)
2209 and cwo.po_distribution_id = c_wo_rec.po_distribution_id
2210 and cwo.accrual_account_id = c_wo_rec.accrual_account_id
2211 and cwo.operating_unit_id = c_wo_rec.operating_unit_id;
2212
2213 l_stmt_num := 60;
2214
2215 /* Insert the data into the summary table */
2216 insert into cst_reconciliation_summary
2217 (
2218 po_distribution_id,
2219 accrual_account_id,
2220 po_balance,
2221 ap_balance,
2222 write_off_balance,
2223 last_receipt_date,
2224 last_invoice_dist_date,
2225 last_write_off_date,
2226 inventory_item_id,
2227 vendor_id,
2228 destination_type_code,
2229 operating_unit_id,
2230 last_update_date,
2231 last_updated_by,
2232 last_update_login,
2233 creation_date,
2234 created_by,
2235 request_id,
2236 program_application_id,
2237 program_id,
2238 program_update_date
2239 )
2240 select cwo.po_distribution_id,
2241 cwo.accrual_account_id,
2242 sum(decode(capr.write_off_id,NULL,
2243 decode(capr.invoice_distribution_id,NULL,
2244 capr.amount,0),0)),
2245 sum(decode(capr.invoice_distribution_id,NULL,0,capr.amount)),
2246 sum(decode(capr.write_off_id,NULL,0,capr.amount)),
2247 max(decode(capr.write_off_id,NULL,
2248 decode(capr.invoice_distribution_id,NULL,
2249 capr.transaction_date,NULL),NULL)),
2250 max(decode(capr.invoice_distribution_id,NULL,NULL,capr.transaction_date)),
2251 max(decode(capr.write_off_id,NULL,NULL,capr.transaction_date)),
2252 cwo.inventorY_item_id,
2253 cwo.vendor_id,
2254 cwo.destination_type_code,
2255 cwo.operating_unit_id,
2256 sysdate, --last_update_date,
2257 FND_GLOBAL.USER_ID, --last_updated_by,
2258 FND_GLOBAL.USER_ID, --last_update_login,
2259 sysdate, --creation_date,
2260 FND_GLOBAL.USER_ID, --created_by,
2261 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
2262 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
2263 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
2264 sysdate --program_update_date
2265 from cst_ap_po_reconciliation capr,
2266 cst_write_offs cwo
2267 where cwo.write_off_id = c_wo_rec.l_wo_id
2268 and cwo.operating_unit_id = c_wo_rec.operating_unit_id
2269 and capr.po_distribution_id = cwo.po_distribution_id
2270 and capr.accrual_account_id = cwo.accrual_account_id
2271 and capr.operating_unit_id = cwo.operating_unit_id
2272 group by cwo.po_distribution_id,
2273 cwo.accrual_account_id,
2274 cwo.inventory_item_id,
2275 cwo.vendor_id,
2276 cwo.destination_type_code,
2277 cwo.operating_unit_id,
2278 cwo.last_update_date,
2279 cwo.last_updated_by,
2280 cwo.last_update_login,
2281 cwo.creation_date,
2282 cwo.created_by,
2283 cwo.request_id,
2284 cwo.program_application_id,
2285 cwo.program_id,
2286 cwo.program_update_date;
2287
2288 l_stmt_num := 65;
2289 --}
2290 /*
2291 Rebuild has occured, decide if reversal would balance the account
2292 for the given po distribution ID
2293 */
2294 else
2295 --{
2296 l_stmt_num := 70;
2297
2298 select (po_balance + ap_balance + write_off_balance)
2299 into l_po_proc
2300 from cst_reconciliation_summary
2301 where po_distribution_id = c_wo_rec.po_distribution_id
2302 and accrual_account_id = c_wo_rec.accrual_account_id
2303 and operating_unit_id = c_wo_rec.operating_unit_id;
2304
2305 /* If it balances, remove entries from reconciliation tables */
2306 if(l_po_proc + (-1 * c_wo_rec.amount) = 0) then
2307 --{
2308 l_stmt_num := 75;
2309
2310 delete from cst_ap_po_reconciliation
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 l_stmt_num := 80;
2316
2317 delete from cst_reconciliation_summary
2318 where po_distribution_id = c_wo_rec.po_distribution_id
2319 and accrual_account_id = c_wo_rec.accrual_account_id
2320 and operating_unit_id = c_wo_rec.operating_unit_id;
2321 --}
2322 /* If it doesn't, update wo_balance in CRS and insert reversal only in CAPR */
2323 else
2324 --{
2325 l_stmt_num := 85;
2326
2327 update cst_reconciliation_summary crs
2328 set crs.write_off_balance = crs.write_off_balance + (-1 * c_wo_rec.amount)
2329 where po_distribution_id = c_wo_rec.po_distribution_id
2330 and accrual_account_id = c_wo_rec.accrual_account_id
2331 and operating_unit_id = c_wo_rec.operating_unit_id;
2332
2333 l_stmt_num := 90;
2334
2335 insert into cst_ap_po_reconciliation
2336 (
2337 transaction_date,
2338 amount,
2339 entered_amount,
2340 currency_code,
2341 currency_conversion_type,
2342 currency_conversion_rate,
2343 currency_conversion_date,
2344 po_distribution_id,
2345 accrual_account_id,
2346 transaction_type_code,
2347 write_off_id,
2348 operating_unit_id,
2349 last_update_date,
2350 last_updated_by,
2351 last_update_login,
2352 creation_date,
2353 created_by,
2354 request_id,
2355 program_application_id,
2356 program_id,
2357 program_update_date
2358 )
2359 select cwo.transaction_date,
2360 cwo.write_off_amount,
2361 cwo.entered_amount,
2362 cwo.currency_code,
2363 cwo.currency_conversion_type,
2364 cwo.currency_conversion_rate,
2365 cwo.currency_conversion_date,
2366 cwo.po_distribution_id,
2367 cwo.accrual_account_id,
2368 cwo.transaction_type_code,
2369 cwo.write_off_id,
2370 cwo.operating_unit_id,
2371 sysdate, --last_update_date,
2372 FND_GLOBAL.USER_ID, --last_updated_by,
2373 FND_GLOBAL.USER_ID, --last_update_login,
2374 sysdate, --creation_date,
2375 FND_GLOBAL.USER_ID, --created_by,
2376 FND_GLOBAL.CONC_REQUEST_ID, --request_id,
2377 FND_GLOBAL.PROG_APPL_ID, --program_application_id,
2378 FND_GLOBAL.CONC_PROGRAM_ID, --program_id,
2379 sysdate --program_update_date
2380 from cst_write_offs cwo
2381 where cwo.write_off_id = c_wo_rec.l_wo_id
2382 and cwo.po_distribution_id = c_wo_rec.po_distribution_id
2383 and cwo.accrual_account_id = c_wo_rec.accrual_account_id
2384 and cwo.operating_unit_id = c_wo_rec.operating_unit_id;
2385
2386 l_stmt_num := 95;
2387 --}
2388 end if; /* l_po_proc + (-1 * c_wo_rec.amount) = 0 */
2389 --}
2390 end if; /* l_po_proc = 0 */
2391 --}
2392 end if; /* (c_wo_rec.po_distribution_id is null) or
2393 (c_wo_rec.invoice_distribution_id is not null),
2394 (c_wo_rec.po_distribution_id is null) or
2395 (c_wo_rec.invoice_distribution_id is not null)
2396 */
2397 --}
2398 end loop; /* for c_wo_rec in c_wo(p_ou_id) */
2399
2400 l_stmt_num := 100;
2401 /*
2402 Call SLA's bulk events generator which uses the values previously
2403 inserted into SLA's event temp table
2404 */
2405 xla_events_pub_pkg.create_bulk_events(p_source_application_id => 201,
2406 p_application_id => 707,
2407 p_ledger_id => p_sob_id,
2408 p_entity_type_code => 'WO_ACCOUNTING_EVENTS');
2409
2410 /* need to reset the write_off_select_flag back CWO back to NULL */
2411
2412 Update cst_write_offs
2413 set write_off_select_flag = NULL
2414 where operating_unit_id = p_ou_id
2415 and write_off_select_flag = 'Y';
2416
2417 commit;
2418 --}
2419 else
2420 --{
2421 x_count := -1;
2422 return;
2423 --}
2424 end if; /* l_rows > 0 */
2425
2426 x_count := l_rows;
2427 return;
2428
2429 exception
2430 when others then
2431 --{
2432 rollback;
2433 x_count := -1;
2434 x_err_num := SQLCODE;
2435 x_err_code := NULL;
2436 x_err_msg := 'CST_Accrual_Rec_PVT.reverse_write_offs() ' || SQLERRM;
2437 fnd_message.set_name('BOM','CST_UNEXPECTED');
2438 fnd_message.set_token('TOKEN',SQLERRM);
2439 if(l_unLog) then
2440 fnd_log.message(fnd_log.level_unexpected, g_log_header || '.' || l_api_name
2441 || '(' || to_char(l_stmt_num) || ')', FALSE);
2442 end if;
2443 fnd_msg_pub.add;
2444 return;
2445 --}
2446 end reverse_write_offs;
2447
2448 end CST_ACCRUAL_REC_PVT;