[Home] [Help]
PACKAGE BODY: APPS.CST_RECEIPTACCRUALPEREND_PVT
Source
1 PACKAGE BODY CST_ReceiptAccrualPerEnd_PVT AS
2 /* $Header: CSTVRAPB.pls 120.18 2010/06/18 00:59:05 jkwac ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CST_ReceiptAccrualPerEnd_PVT';
5 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
6
7 G_GL_APPLICATION_ID CONSTANT NUMBER := 101;
8 G_PO_APPLICATION_ID CONSTANT NUMBER := 201;
9 G_CST_APPLICATION_ID CONSTANT NUMBER := 707;
10
11 -----------------------------------------------------------------------------
12 -- PROCEDURE : Start_Process
13 -- DESCRIPTION : Starting point for Receipt Accruals - Period End
14 -- Concurrent Program.
15 -----------------------------------------------------------------------------
16 PROCEDURE Start_Process
17 (
18 errbuf OUT NOCOPY VARCHAR2,
19 retcode OUT NOCOPY NUMBER,
20
21 p_min_accrual_amount IN NUMBER,
22 p_vendor_id IN NUMBER,
23 p_struct_num IN NUMBER,
24 p_category_id IN NUMBER,
25 p_period_name IN VARCHAR2
26 )
27
28 IS
29 l_api_name CONSTANT VARCHAR2(30) :='Start_process';
30 l_api_version CONSTANT NUMBER := 1.0;
31 l_return_status VARCHAR2(1);
32 l_msg_count NUMBER;
33 l_msg_data VARCHAR2(240);
34
35 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
36 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
37
38 /* Log Severities*/
39 /* 6- UNEXPECTED */
40 /* 5- ERROR */
41 /* 4- EXCEPTION */
42 /* 3- EVENT */
43 /* 2- PROCEDURE */
44 /* 1- STATEMENT */
45
46 /* In general, we should use the following:
47 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
48 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
49 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
50 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
51 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
52 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
53 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
54 */
55
56 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
57 l_exceptionLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
58 l_pLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
59 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
60
61 l_conc_status BOOLEAN;
62 l_return BOOLEAN;
63 l_status VARCHAR2(1);
64 l_industry VARCHAR2(1);
65 l_schema VARCHAR2(30);
66
67 l_stmt_num NUMBER;
68 l_sys_setup_rec CST_SYS_SETUP_REC_TYPE;
69
70 BEGIN
71 -- Standard Start of API savepoint
72 SAVEPOINT Start_Process_PVT;
73
74 g_counter := 0;
75 l_stmt_num := 0;
76 -- Procedure level log message for Entry point
77 IF (l_pLog) THEN
78 FND_LOG.STRING(
79 FND_LOG.LEVEL_PROCEDURE,
80 l_module || '.begin',
81 'Start_Process <<' ||
82 'p_min_accrual_amount = ' || p_min_accrual_amount ||','||
83 'p_vendor_id = ' || p_vendor_id ||','||
84 'p_struct_num = ' || p_struct_num ||','||
85 'p_category_id = ' || p_category_id ||','||
86 'p_period_name = ' || p_period_name
87 );
88 END IF;
89
90 -- Initialize message list.
91 FND_MSG_PUB.initialize;
92
93 -- Initialize API return status to success
94 l_return_status := FND_API.G_RET_STS_SUCCESS;
95
96 -------------------------------------------------------------------------
97 -- Get system set-up information e.g. set_of_books, chart_of_accounts
98 -- purchase_encumbrance_flag etc.
99 -------------------------------------------------------------------------
100 l_stmt_num := 20;
101 Get_SystemSetup (
102 p_api_version => 1.0,
103 p_init_msg_list => FND_API.G_FALSE,
104 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
105 x_return_status => l_return_status,
106 x_msg_count => l_msg_count,
107 x_msg_data => l_msg_data,
108 p_period_name => p_period_name,
109 x_sys_setup_rec => l_sys_setup_rec
110 );
111 -- If return status is not success, add message to the log
112 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
113 l_msg_data := 'Failed getting system setup' ;
114 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
115 END IF;
116
117 -------------------------------------------------------------------------
118 -- Call the common API CST_PerEndAccruals_PVT.Create_PerEndAccruals()
119 -- This API creates period end accrual entries in the temporary table
120 -- CST_PER_END_ACCRUALS_TEMP.
121 -------------------------------------------------------------------------
122 l_stmt_num := 40;
123 CST_PerEndAccruals_PVT.Create_PerEndAccruals (
124 p_api_version => 1.0,
125 p_init_msg_list => FND_API.G_FALSE,
126 p_commit => FND_API.G_FALSE,
127 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
128 x_return_status => l_return_status,
129 x_msg_count => l_msg_count,
130 x_msg_data => l_msg_data,
131 p_min_accrual_amount => p_min_accrual_amount,
132 p_vendor_id => p_vendor_id,
133 p_category_id => p_category_id,
134 p_end_date => l_sys_setup_rec.period_end_date,
135 p_accrued_receipt => 'N',
136 p_online_accruals => 'N',
137 p_closed_pos => 'N',
138 p_calling_api => CST_PerEndAccruals_PVT.G_RECEIPT_ACCRUAL_PER_END
139 );
140 -- If return status is not success, add message to the log
141 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
142 l_msg_data := 'Failed generating Period End Accrual information';
143 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
144 END IF;
145
146 -------------------------------------------------------------------------
147 -- Create events in RCV_ACCOUNTING_EVENTS
148 -------------------------------------------------------------------------
149 l_stmt_num := 50;
150 Seed_RcvAccountingEvents (
151 p_api_version => 1.0,
152 p_init_msg_list => FND_API.G_FALSE,
153 p_commit => FND_API.G_FALSE,
154 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
155 x_return_status => l_return_status,
156 x_msg_count => l_msg_count,
157 x_msg_data => l_msg_data,
158 p_sys_setup_rec => l_sys_setup_rec
159 );
160 -- If return status is not success, add message to the log
161 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
162 l_msg_data := 'Failed creating event in RCV_ACCOUNTING_EVENTS' ;
163 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
164 END IF;
165
166 -------------------------------------------------------------------------
167 -- Create accounting entries in RCV_RECEIVING_SUB_LEDGER
168 -------------------------------------------------------------------------
169 l_stmt_num := 60;
170 Create_AccrualAccount (
171 p_api_version => 1.0,
172 p_init_msg_list => FND_API.G_FALSE,
173 p_commit => FND_API.G_FALSE,
174 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
175 x_return_status => l_return_status,
176 x_msg_count => l_msg_count,
177 x_msg_data => l_msg_data,
178 p_sys_setup_rec => l_sys_setup_rec
179 );
180 -- If return status is not success, add message to the log
181 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
182 l_msg_data := 'Failed creating Period End Accrual entries ' ;
183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
184 END IF;
185
186 -------------------------------------------------------------------------
187 -- Update the Accrued PO distribution, and mark accrued_flag as 'Y'
188 -------------------------------------------------------------------------
189 l_stmt_num := 70;
190 FORALL l_ctr IN g_accrued_dist_id_tbl.FIRST..g_accrued_dist_id_tbl.LAST
191 UPDATE po_distributions_all pod
192 SET pod.accrued_flag = 'Y'
193 WHERE pod.po_distribution_id = g_accrued_dist_id_tbl(l_ctr);
194
195 -- Clear the PL/SQL table
196 g_accrued_dist_id_tbl.DELETE;
197
198 -- Write log messages to request log
199 l_stmt_num := 80;
200 CST_UTILITY_PUB.writelogmessages (
201 p_api_version => 1.0,
202 p_msg_count => l_msg_count,
203 p_msg_data => l_msg_data,
204 x_return_status => l_return_status
205 );
206
207 -- If return status is not success, add message to the log
208 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
209 l_msg_data := 'Failed writing log messages' ;
210 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
211 END IF;
212
213 -- Procedure level log message for exit point
214 IF (l_pLog) THEN
215 FND_LOG.STRING(
216 FND_LOG.LEVEL_PROCEDURE,
217 l_module || '.end',
218 'Start_Process >>'
219 );
220 END IF;
221
222 COMMIT WORK;
223
224 EXCEPTION
225 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
226 ROLLBACK TO Start_Process_PVT;
227
228 IF (l_exceptionLog) THEN
229 FND_LOG.STRING(
230 FND_LOG.LEVEL_EXCEPTION,
231 l_module || '.' || l_stmt_num,
232 l_msg_data
233 );
234 END IF;
235
236 -- Write log messages to request log
237 CST_UTILITY_PUB.writelogmessages (
238 p_api_version => 1.0,
239 p_msg_count => l_msg_count,
240 p_msg_data => l_msg_data,
241 x_return_status => l_return_status
242 );
243
244 -- Set concurrent program status to error
245 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',l_msg_data);
246
247 WHEN OTHERS THEN
248 ROLLBACK TO Start_Process_PVT;
249
250 -- Unexpected level log message for FND log
251 IF (l_uLog) THEN
252 FND_LOG.STRING(
253 FND_LOG.LEVEL_UNEXPECTED,
254 l_module || '.' || l_stmt_num,
255 SQLERRM
256 );
257 END IF;
258
259 IF FND_MSG_PUB.Check_Msg_Level
260 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
261 THEN
262 FND_MSG_PUB.Add_Exc_Msg
263 ( G_PKG_NAME,
264 l_api_name,
265 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
266 );
267 END IF;
268
269 -- Write log messages to request log
270 CST_UTILITY_PUB.writelogmessages (
271 p_api_version => 1.0,
272 p_msg_count => l_msg_count,
273 p_msg_data => l_msg_data,
274 x_return_status => l_return_status
275 );
276
277 -- Set concurrent program status to error
278 l_conc_status := FND_CONCURRENT.SET_COMPLETION_STATUS('ERROR',
279 'An unexpected error has occurred, please contact System Administrator. ');
280
281 END Start_Process;
282
283 -----------------------------------------------------------------------------
284 -- PROCEDURE : Seed_RcvAccountingEvents
285 -- DESCRIPTION : The procedure created events in RCV_ACCOUNTING_EVENTS table
286 --
287 -- The procedure generates data and creates PL/SQL table for
288 -- RAE, which will be used for bulk inserting the data in
289 -- RAE
290 -----------------------------------------------------------------------------
291 PROCEDURE Seed_RcvAccountingEvents
292 (
293 p_api_version IN NUMBER,
294 p_init_msg_list IN VARCHAR2,
295 p_commit IN VARCHAR2,
296 p_validation_level IN NUMBER,
297
298 x_return_status OUT NOCOPY VARCHAR2,
299 x_msg_count OUT NOCOPY NUMBER,
300 x_msg_data OUT NOCOPY VARCHAR2,
301
302 p_sys_setup_rec IN CST_SYS_SETUP_REC_TYPE
303 )
304
305 IS
306 l_api_name CONSTANT VARCHAR2(30) :='Seed_RcvAccountingEvents';
307 l_api_version CONSTANT NUMBER := 1.0;
308 l_return_status VARCHAR2(1);
309
310 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
311 l_module CONSTANT VARCHAR2(70) := 'cst.plsql.'||l_full_name;
312
313 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
314 l_exceptionLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
315 l_pLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
316 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
317
318 l_stmt_num NUMBER;
319 l_msg_data VARCHAR2(240);
320 l_ctr NUMBER;
321
322 l_org_id NUMBER;
323 l_user_id NUMBER;
324 l_login_id NUMBER;
325 l_conc_request_id NUMBER;
326 l_prog_appl_id NUMBER;
327 l_conc_program_id NUMBER;
328 l_inv_org_id NUMBER;
329 l_po_number VARCHAR2(20);
330
331 -- Cursor for fetching data from temp table CST_PER_END_ACCRUALS_TEMP
332 CURSOR l_accounting_events_csr IS
333 SELECT shipment_id,
334 distribution_id,
335 quantity_received,
336 quantity_billed,
337 accrual_quantity,
338 encum_quantity,
339 unit_price,
340 accrual_amount,
341 encum_amount,
342 currency_code,
343 currency_conversion_type,
344 currency_conversion_rate,
345 currency_conversion_date
346 FROM cst_per_end_accruals_temp;
347
348 BEGIN
349 -- Standard Start of API savepoint
350 SAVEPOINT Seed_RcvAccountingEvents_PVT;
351
352 l_stmt_num := 0;
353 -- Procedure level log message for Entry point
354 IF (l_pLog) THEN
355 FND_LOG.STRING(
356 FND_LOG.LEVEL_PROCEDURE,
357 l_module || '.begin',
358 'Insert_AccrualSubLedger <<');
359 END IF;
360
361 -- Standard call to check for call compatibility.
362 IF NOT FND_API.Compatible_API_Call ( l_api_version,
363 p_api_version,
364 l_api_name,
365 G_PKG_NAME )
366 THEN
367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
368 END IF;
369
370 -- Initialize message list if p_init_msg_list is set to TRUE.
371 IF FND_API.to_Boolean( p_init_msg_list ) THEN
372 FND_MSG_PUB.initialize;
373 END IF;
374
375 -- Initialize API return status to success
376 x_return_status := FND_API.G_RET_STS_SUCCESS;
377 l_return_status := FND_API.G_RET_STS_SUCCESS;
378
379 l_stmt_num := 10;
380 l_user_id := FND_GLOBAL.USER_ID;
381 l_login_id := FND_GLOBAL.LOGIN_ID;
382 l_conc_request_id := FND_GLOBAL.CONC_REQUEST_ID;
383 l_prog_appl_id := FND_GLOBAL.PROG_APPL_ID;
384 l_conc_program_id := FND_GLOBAL.CONC_PROGRAM_ID;
385 /*l_org_id := FND_GLOBAL.ORG_ID;
386 Getting org_id from purchasing option
387 Bug6987381*/
388 l_org_id := p_sys_setup_rec.org_id;
389
390 -- Loop for each record in tem table
391 l_stmt_num := 20;
392 FOR l_accounting_events_rec IN l_accounting_events_csr LOOP
393
394 l_stmt_num := 20;
395 SELECT poll.ship_to_organization_id,
396 poh.segment1
397 INTO l_inv_org_id,
398 l_po_number
399 FROM po_line_locations_all poll,
400 po_headers_all poh
401 WHERE poll.line_location_id = l_accounting_events_rec.shipment_id
402 AND poh.po_header_id = poll.po_header_id;
403
404 l_ctr := g_rae_event_id_tbl.COUNT + 1;
405 -- Get accounting_event_id and accounting_event_type_id
406 l_stmt_num := 30;
407 SELECT rcv_accounting_event_s.nextval,
408 raet.event_type_id
409 INTO g_rae_event_id_tbl(l_ctr),
410 g_rae_event_type_id_tbl(l_ctr)
411 FROM rcv_accounting_event_types raet
412 WHERE raet.event_type_name = 'PERIOD_END_ACCRUAL';
413
414 g_rae_distribution_id_tbl(l_ctr):= l_accounting_events_rec.distribution_id;
415 g_rae_inv_org_id_tbl(l_ctr) := l_inv_org_id;
416 g_rae_po_number_tbl(l_ctr) := l_po_number;
417 g_rae_qty_received_tbl(l_ctr) := l_accounting_events_rec.quantity_received;
418 g_rae_qty_invoiced_tbl(l_ctr) := l_accounting_events_rec.quantity_billed;
419 g_rae_unit_pice_tbl(l_ctr) := l_accounting_events_rec.unit_price;
420 g_rae_currency_code_tbl(l_ctr) := l_accounting_events_rec.currency_code;
421 g_rae_cur_conv_type_tbl(l_ctr) := l_accounting_events_rec.currency_conversion_type;
422 g_rae_cur_conv_rate_tbl(l_ctr) := l_accounting_events_rec.currency_conversion_rate;
423 g_rae_cur_conv_date_tbl(l_ctr) := l_accounting_events_rec.currency_conversion_date;
424
425 -- Transaction quantity and amount
426 g_rae_txn_qty_tbl(l_ctr) := l_accounting_events_rec.accrual_quantity;
427 g_rae_txn_amount_tbl(l_ctr) := l_accounting_events_rec.accrual_amount;
428
429 ---------------------------------------------------------------------
430 -- The PL/SQL table g_accrual_index_tbl works as a index table for
431 -- accrual event_id PL/SQL table g_rae_event_id_tbl.
432 -- e.g. g_accrual_index_tbl(po_distribution_id) stores position of
433 -- accounting_event_id stored in PL/SQL table g_rae_event_id_tbl,
434 -- corresponding to po_distribution_id.
435 --
436 -- This index table will be used to map the accounting_event_id and
437 -- po_distribution_id, while creating accounting entries
438 -- RCV_RECEIVING_SUB_LEDGER
439 ---------------------------------------------------------------------
440 g_accrual_index_tbl(l_accounting_events_rec.distribution_id) := l_ctr;
441 /*Bug6987381: g_rae_pnt_event_id_tbl to be used only for encumbrance
442 reversal events
443 */
444 g_rae_pnt_event_id_tbl(l_ctr) := null;
445
446 -- Generate events for encumbrance reversals
447 IF (p_sys_setup_rec.purch_encumbrance_flag = 'Y') THEN
448
449 l_ctr := g_rae_event_id_tbl.COUNT + 1;
450
451 -- Get accounting_event_id and accounting_event_type_id
452 l_stmt_num := 40;
453 SELECT rcv_accounting_event_s.nextval,
454 raet.event_type_id
455 INTO g_rae_event_id_tbl(l_ctr),
456 g_rae_event_type_id_tbl(l_ctr)
457 FROM rcv_accounting_event_types raet
458 WHERE raet.event_type_name = 'ENCUMBRANCE_REVERSAL';
459
460 g_rae_distribution_id_tbl(l_ctr):= l_accounting_events_rec.distribution_id;
461 g_rae_inv_org_id_tbl(l_ctr) := l_inv_org_id;
462 g_rae_qty_received_tbl(l_ctr) := l_accounting_events_rec.quantity_received;
463 g_rae_qty_invoiced_tbl(l_ctr) := l_accounting_events_rec.quantity_billed;
464 g_rae_unit_pice_tbl(l_ctr) := l_accounting_events_rec.unit_price;
465 g_rae_currency_code_tbl(l_ctr) := l_accounting_events_rec.currency_code;
466 g_rae_cur_conv_type_tbl(l_ctr) := l_accounting_events_rec.currency_conversion_type;
467 g_rae_cur_conv_rate_tbl(l_ctr) := l_accounting_events_rec.currency_conversion_rate;
468 g_rae_cur_conv_date_tbl(l_ctr) := l_accounting_events_rec.currency_conversion_date;
469 g_rae_txn_qty_tbl(l_ctr) := l_accounting_events_rec.encum_quantity;
470 g_rae_txn_amount_tbl(l_ctr) := l_accounting_events_rec.encum_amount;
471
472 -----------------------------------------------------------------
473 -- The PL/SQL table g_encum_index_tbl works as a index table for
474 -- PL/SQL table g_rae_event_id_tbl for encumbrance reversals.
475 -----------------------------------------------------------------
476 g_encum_index_tbl(l_accounting_events_rec.distribution_id) := l_ctr;
477 /*Bug6987381*/
478 g_rae_pnt_event_id_tbl(l_ctr) := g_rae_event_id_tbl(g_accrual_index_tbl(l_accounting_events_rec.distribution_id));
479 g_rae_po_number_tbl(l_ctr) := l_po_number;
480 END IF;
481
482 END LOOP;
483
484 -------------------------------------------------------------------------
485 -- Bulk insert the data in RCV_ACCOUNTING_EVENTS
486 -------------------------------------------------------------------------
487 l_stmt_num := 60;
488 FORALL l_ctr IN g_rae_event_id_tbl.FIRST..g_rae_event_id_tbl.LAST
489 INSERT into RCV_ACCOUNTING_EVENTS (
490 accounting_event_id,
491 last_update_date,
492 last_updated_by,
493 last_update_login,
494 creation_date,
495 created_by,
496 request_id,
497 program_application_id,
498 program_id,
499 program_udpate_date,
500 rcv_transaction_id,
501 event_type_id,
502 event_source,
503 event_source_id,
504 set_of_books_id,
505 org_id,
506 organization_id,
507 debit_account_id,
508 credit_account_id,
509 transaction_date,
510 source_doc_quantity,
511 transaction_quantity,
512 primary_quantity,
513 source_doc_unit_of_measure,
514 transaction_unit_of_measure,
515 primary_unit_of_measure,
516 po_header_id,
517 po_release_id,
518 po_line_id,
519 po_line_location_id,
520 po_distribution_id,
521 inventory_item_id,
522 unit_price,
523 intercompany_pricing_option,
524 transaction_amount,
525 quantity_received,
526 quantity_invoiced,
527 amount_received,
528 amount_invoiced,
529 nr_tax,
530 rec_tax,
531 nr_tax_amount,
532 rec_tax_amount,
533 currency_code,
534 currency_conversion_type,
535 currency_conversion_rate,
536 currency_conversion_date,
537 accounted_flag,
538 cross_ou_flag
539 )
540 SELECT
541 g_rae_event_id_tbl(l_ctr),
542 SYSDATE,
543 l_user_id,
544 l_login_id,
545 sysdate,
546 l_user_id,
547 l_conc_request_id,
548 l_prog_appl_id,
549 l_conc_program_id,
550 sysdate,
551 0,
552 g_rae_event_type_id_tbl(l_ctr),
553 'PERIOD_END_ACCRUAL',
554 g_rae_distribution_id_tbl(l_ctr),
555 p_sys_setup_rec.set_of_books_id,
556 l_org_id,
557 poll.ship_to_organization_id,
558 pod.code_combination_id,
559 pod.accrual_account_id,
560 p_sys_setup_rec.transaction_date,
561 DECODE (poll.matching_basis,
562 'QUANTITY', g_rae_txn_qty_tbl(l_ctr) ),
563 DECODE (poll.matching_basis,
564 'QUANTITY', g_rae_txn_qty_tbl(l_ctr) ),
565 DECODE (poll.matching_basis,
566 'QUANTITY', g_rae_txn_qty_tbl(l_ctr) *
567 inv_convert.inv_um_convert(
568 NVL(pol.item_id, 0),
569 10,
570 NULL,
571 NULL,
572 NULL,
573 poll.unit_meas_lookup_code,
574 NVL(msi.primary_unit_of_measure, puom.unit_of_measure))
575 ),
576 poll.unit_meas_lookup_code,
577 poll.unit_meas_lookup_code,
578 NVL(msi.primary_unit_of_measure, puom.unit_of_measure),
579 poh.po_header_id,
580 poll.po_release_id,
581 pol.po_line_id,
582 poll.line_location_id,
583 pod.po_distribution_id,
584 pol.item_id,
585 g_rae_unit_pice_tbl(l_ctr),
586 1,
587 DECODE (poll.matching_basis,
588 'AMOUNT', g_rae_txn_amount_tbl(l_ctr)),
589 DECODE (poll.matching_basis,
590 'QUANTITY', g_rae_qty_received_tbl(l_ctr)),
591 DECODE (poll.matching_basis,
592 'QUANTITY', g_rae_qty_invoiced_tbl(l_ctr)),
593 DECODE (poll.matching_basis,
594 'AMOUNT', g_rae_qty_received_tbl(l_ctr)),
595 DECODE (poll.matching_basis,
596 'AMOUNT', g_rae_qty_invoiced_tbl(l_ctr)),
597 DECODE (poll.matching_basis,
598 'QUANTITY', pod.recoverable_tax / pod.quantity_ordered),
599 DECODE (poll.matching_basis,
600 'QUANTITY', pod.nonrecoverable_tax / pod.quantity_ordered),
601 DECODE (poll.matching_basis,
602 'AMOUNT', pod.recoverable_tax * g_rae_txn_qty_tbl(l_ctr)
603 / pod.amount_ordered),
604 DECODE (poll.matching_basis,
605 'AMOUNT', pod.nonrecoverable_tax * g_rae_txn_qty_tbl(l_ctr)
606 / pod.amount_ordered),
607 g_rae_currency_code_tbl(l_ctr),
608 g_rae_cur_conv_type_tbl(l_ctr),
609 g_rae_cur_conv_rate_tbl(l_ctr),
610 g_rae_cur_conv_date_tbl(l_ctr),
611 'N',
612 DECODE( poh.org_id,
613 cod.operating_unit, 'N',
614 'Y')
615 FROM
616 po_headers_all poh,
617 po_lines_all pol,
618 po_line_locations_all poll,
619 po_distributions_all pod,
620 cst_organization_definitions cod,
621 mtl_system_items msi,
622 mtl_units_of_measure tuom,
623 mtl_units_of_measure puom
624 WHERE
625 pod.po_distribution_id = g_rae_distribution_id_tbl(l_ctr)
626 AND poh.po_header_id = pol.po_header_id
627 AND pol.po_line_id = poll.po_line_id
628 AND poll.line_location_id = pod.line_location_id
629 AND cod.organization_id = poll.ship_to_organization_id
630 AND msi.inventory_item_id (+) = pol.item_id
631 AND (msi.organization_id IS NULL
632 OR
633 (msi.organization_id = poll.ship_to_organization_id AND msi.organization_id IS NOT NULL))
634 AND tuom.unit_of_measure(+) = decode(poll.matching_basis, 'QUANTITY', poll.unit_meas_lookup_code, NULL)
635 AND puom.uom_class(+) = tuom.uom_class
636 AND puom.base_uom_flag(+) = 'Y';
637
638 ------------------------------------------------------------------------
639 -- Clear the PL/SQL tables,
640 -- Do not clear the table g_rae_event_id_tbl, since we need the
641 -- accounting_event_ids while creating accounting entries in
642 -- RCV_RECEIVING_SUB_LEDGER.
643 ------------------------------------------------------------------------
644 l_stmt_num := 70;
645 g_rae_distribution_id_tbl.DELETE;
646 g_rae_qty_received_tbl.DELETE;
647 g_rae_qty_invoiced_tbl.DELETE;
648 g_rae_unit_pice_tbl.DELETE;
649 g_rae_txn_qty_tbl.DELETE;
650 g_rae_txn_amount_tbl.DELETE;
651 g_rae_currency_code_tbl.DELETE;
652 g_rae_cur_conv_type_tbl.DELETE;
653 g_rae_cur_conv_rate_tbl.DELETE;
654 g_rae_cur_conv_date_tbl.DELETE;
655
656 -- Procedure level log message for exit point
657 IF (l_pLog) THEN
658 FND_LOG.STRING(
659 FND_LOG.LEVEL_PROCEDURE,
660 l_module || '.end',
661 'Insert_AccrualSubLedger >>'
662 );
663 END IF;
664
665 -- Get message count and if 1, return message data.
666 FND_MSG_PUB.Count_And_Get
667 ( p_count => x_msg_count,
668 p_data => x_msg_data
669 );
670
671 -- Standard check of p_commit.
672 IF FND_API.To_Boolean( p_commit ) THEN
673 COMMIT WORK;
674 END IF;
675
676 EXCEPTION
677
678 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
679 ROLLBACK TO Seed_RcvAccountingEvents_PVT;
680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
681
682 IF (l_exceptionLog) THEN
683 FND_LOG.STRING(
684 FND_LOG.LEVEL_EXCEPTION,
685 l_module || '.' || l_stmt_num,
686 l_msg_data
687 );
688 END IF;
689
690 FND_MSG_PUB.Count_And_Get
691 ( p_count => x_msg_count,
692 p_data => x_msg_data
693 );
694
695 WHEN OTHERS THEN
696 ROLLBACK TO Seed_RcvAccountingEvents_PVT;
697 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
698
699 -- Unexpected level log message
700 IF (l_uLog) THEN
701 FND_LOG.STRING(
702 FND_LOG.LEVEL_UNEXPECTED,
703 l_module || '.' || l_stmt_num,
704 SQLERRM
705 );
706 END IF;
707
708 IF FND_MSG_PUB.Check_Msg_Level
709 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
710 THEN
711 FND_MSG_PUB.Add_Exc_Msg
712 ( G_PKG_NAME,
713 l_api_name,
714 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
715 );
716 END IF;
717
718 FND_MSG_PUB.Count_And_Get
719 ( p_count => x_msg_count,
720 p_data => x_msg_data
721 );
722
723 END Seed_RcvAccountingEvents;
724
725 -----------------------------------------------------------------------------
726 -- PROCEDURE : Create_AccrualAccount
727 -- DESCRIPTION : The procedure fetches data from temp table
728 -- CST_PER_END_ACCRUALS_TEMP, and populates PL/SQL tables
729 -- with the corresponding accrual info for RCV_RECEIVING_SUB_LEDGER
730 -----------------------------------------------------------------------------
731 PROCEDURE Create_AccrualAccount
732 (
733 p_api_version IN NUMBER,
734 p_init_msg_list IN VARCHAR2,
735 p_commit IN VARCHAR2,
736 p_validation_level IN NUMBER,
737
738 x_return_status OUT NOCOPY VARCHAR2,
739 x_msg_count OUT NOCOPY NUMBER,
740 x_msg_data OUT NOCOPY VARCHAR2,
741
742 p_sys_setup_rec IN CST_SYS_SETUP_REC_TYPE
743 )
744 IS
745 l_api_name CONSTANT VARCHAR2(30) :='Create_AccrualAccount';
746 l_api_version CONSTANT NUMBER := 1.0;
747 l_return_status VARCHAR2(1);
748
749 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
750 l_module CONSTANT VARCHAR2(70) := 'cst.plsql.'||l_full_name;
751
752 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
753 l_exceptionLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
754 l_pLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
755 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
756
757 l_stmt_num NUMBER;
758 l_msg_data VARCHAR2(240);
759
760 l_accrual_info_rec CST_ACCRUAL_INFO_REC_TYPE;
761 l_trx_rec CST_XLA_PVT.T_XLA_RCV_TRX_INFO;
762 l_func_currency_code VARCHAR2(15);
763 l_accounted_dr NUMBER;
764 l_accounted_cr NUMBER;
765 l_accounted_encum_dr NUMBER;
766 l_accounted_encum_cr NUMBER;
767 l_accounted_nr_tax NUMBER;
768 l_accounted_rec_tax NUMBER;
769 l_user_curr_conv_type VARCHAR2(30);
770 l_ctr NUMBER;
771 /*Bug6987381 Start */
772 l_reference_date_1 DATE;
773 l_batch NUMBER;
774 l_errbuf VARCHAR2(1000);
775 l_retcode NUMBER;
776 l_bc_status VARCHAR2(2000);
777 l_packet_id NUMBER;
778 l_user_id NUMBER;
779 l_resp_id NUMBER;
780 l_resp_appl_id NUMBER;
781 /*Bug6987381 End */
782 -- Accrual cursor
783 CURSOR l_accrual_csr IS
784 SELECT cpea.shipment_id shipment_id,
785 cpea.distribution_id distribution_id,
786 cpea.accrual_quantity accrual_quantity,
787 cpea.encum_quantity encum_quantity,
788 cpea.accrual_amount entered_dr,
789 cpea.accrual_amount entered_cr,
790 cpea.encum_amount entered_encum_dr,
791 cpea.encum_amount entered_encum_cr,
792 poh.segment1 po_number,
793 NVL(fnc1.minimum_accountable_unit,0) min_func_acct_unit,
794 fnc1.precision func_currency_precision,
795 NVL(fnc2.minimum_accountable_unit,0) min_acct_unit,
796 fnc2.precision currency_precision,
797 poh.po_header_id po_header_id,
798 cpea.currency_code currency_code,
799 cpea.currency_conversion_rate currency_rate,
800 NVL(pod.rate,1) encum_currency_rate,
801 pod.rate_date encum_currency_conv_date,
802 glct.user_conversion_type user_curr_conv_type,
803 cpea.currency_conversion_date currency_conv_date,
804 pod.recoverable_tax * cpea.accrual_quantity
805 / DECODE(poll.matching_basis,
806 'AMOUNT', pod.amount_ordered,
807 pod.quantity_ordered ) entered_rec_tax,
808 pod.nonrecoverable_tax * cpea.accrual_quantity
809 / DECODE(poll.matching_basis,
810 'AMOUNT', pod.amount_ordered,
811 pod.quantity_ordered ) entered_nr_tax,
812 pod.code_combination_id act_debit_ccid,
813 pod.accrual_account_id act_credit_ccid,
814 pod.budget_account_id enc_credit_ccid
815 FROM cst_per_end_accruals_temp cpea,
816 po_headers_all poh,
817 po_line_locations_all poll,
818 po_distributions_all pod,
819 fnd_currencies fnc1,
820 fnd_currencies fnc2,
821 gl_daily_conversion_types glct
822 WHERE pod.po_distribution_id = cpea.distribution_id
823 AND pod.po_header_id = poh.po_header_id
824 AND pod.line_location_id = poll.line_location_id
825 AND fnc1.currency_code = l_func_currency_code
826 AND fnc2.currency_code = cpea.currency_code
827 AND cpea.currency_conversion_type = glct.conversion_type(+)
828 ;
829
830 BEGIN
831 -- Standard Start of API savepoint
832 SAVEPOINT Create_AccrualAccount_PVT;
833
834 l_stmt_num := 0;
835 -- Procedure level log message for Entry point
836 IF (l_pLog) THEN
837 FND_LOG.STRING(
838 FND_LOG.LEVEL_PROCEDURE,
839 l_module || '.begin',
840 'Create_AccrualAccount <<');
841 END IF;
842
843 -- Standard call to check for call compatibility.
844 IF NOT FND_API.Compatible_API_Call ( l_api_version,
845 p_api_version,
846 l_api_name,
847 G_PKG_NAME )
848 THEN
849 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
850 END IF;
851
852 -- Initialize message list if p_init_msg_list is set to TRUE.
853 IF FND_API.to_Boolean( p_init_msg_list ) THEN
854 FND_MSG_PUB.initialize;
855 END IF;
856
857 -- Initialize API return status to success
858 x_return_status := FND_API.G_RET_STS_SUCCESS;
859 l_return_status := FND_API.G_RET_STS_SUCCESS;
860
861 l_func_currency_code := p_sys_setup_rec.functional_currency_code;
862
863 -- Loop for each row of accrual cursor
864 l_stmt_num := 20;
865 FOR l_accrual_rec IN l_accrual_csr LOOP
866
867 -- Convert the accounting values in base currency
868 l_stmt_num := 30;
869 IF (l_accrual_rec.min_acct_unit = 0) THEN
870 l_accounted_dr := ROUND(l_accrual_rec.entered_dr,l_accrual_rec.currency_precision)
871 * l_accrual_rec.currency_rate ;
872 l_accounted_cr := ROUND(l_accrual_rec.entered_cr,l_accrual_rec.currency_precision)
873 * l_accrual_rec.currency_rate ;
874 l_accounted_encum_dr := ROUND(l_accrual_rec.entered_encum_dr,l_accrual_rec.currency_precision)
875 * l_accrual_rec.encum_currency_rate ;
876 l_accounted_encum_cr := ROUND(l_accrual_rec.entered_encum_cr,l_accrual_rec.currency_precision)
877 * l_accrual_rec.encum_currency_rate ;
878 l_accounted_nr_tax := ROUND(l_accrual_rec.entered_nr_tax , l_accrual_rec.currency_precision)
879 * l_accrual_rec.currency_rate ;
880 l_accounted_rec_tax := ROUND(l_accrual_rec.entered_rec_tax , l_accrual_rec.currency_precision)
881 * l_accrual_rec.currency_rate ;
882 ELSE
883 l_accounted_dr := ROUND(l_accrual_rec.entered_dr/l_accrual_rec.min_acct_unit)
884 * l_accrual_rec.min_acct_unit * l_accrual_rec.currency_rate;
885 l_accounted_cr := ROUND(l_accrual_rec.entered_cr/l_accrual_rec.min_acct_unit)
886 * l_accrual_rec.min_acct_unit * l_accrual_rec.currency_rate;
887 l_accounted_encum_dr := ROUND(l_accrual_rec.entered_encum_dr/l_accrual_rec.min_acct_unit)
888 * l_accrual_rec.min_acct_unit * l_accrual_rec.encum_currency_rate;
889 l_accounted_encum_cr := ROUND(l_accrual_rec.entered_encum_cr/l_accrual_rec.min_acct_unit)
890 * l_accrual_rec.min_acct_unit * l_accrual_rec.encum_currency_rate;
891 l_accounted_nr_tax := ROUND(l_accrual_rec.entered_nr_tax / l_accrual_rec.min_acct_unit)
892 * l_accrual_rec.min_acct_unit * l_accrual_rec.currency_rate;
893 l_accounted_rec_tax := ROUND(l_accrual_rec.entered_rec_tax / l_accrual_rec.min_acct_unit)
894 * l_accrual_rec.min_acct_unit * l_accrual_rec.currency_rate;
895 END IF;
896
897 ---------------------------------------------------------------------
898 -- g_accrual_index_tbl(po_distribution_id) stores position of
899 -- accounting_event_id stored in PL/SQL table g_rae_event_id_tbl,
900 -- corresponding to po_distribution_id.
901 ---------------------------------------------------------------------
902 l_stmt_num := 40;
903 IF (g_accrual_index_tbl.EXISTS(l_accrual_rec.distribution_id)) THEN
904 l_accrual_info_rec.rcv_acc_event_id :=
905 g_rae_event_id_tbl(g_accrual_index_tbl(l_accrual_rec.distribution_id));
906
907 -- This will be used only for encum reversal entries
908 l_accrual_info_rec.parent_rcv_acc_event_id := NULL;
909 ELSE
910 l_msg_data := 'Failed getting corresponding RCV_ACCOUNTING_EVENT_ID for distribution_id :'
911 || l_accrual_rec.distribution_id;
912 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
913 END IF;
914
915 -- Populate the accrual_info_rec record
916 l_stmt_num := 70;
917 l_accrual_info_rec.actual_flag := 'A';
918 l_accrual_info_rec.accrual_method_flag := 'P';
919
920 l_accrual_info_rec.currency_code := l_accrual_rec.currency_code;
921 l_accrual_info_rec.currency_conversion_date := l_accrual_rec.currency_conv_date;
922 l_accrual_info_rec.user_currency_conversion_type := l_accrual_rec.user_curr_conv_type;
923 l_accrual_info_rec.currency_conversion_rate := l_accrual_rec.currency_rate;
924 l_accrual_info_rec.po_header_id := l_accrual_rec.po_header_id;
925 l_accrual_info_rec.distribution_id := l_accrual_rec.distribution_id;
926 l_accrual_info_rec.po_number := l_accrual_rec.po_number;
927 l_accrual_info_rec.source_doc_quantity := l_accrual_rec.accrual_quantity;
928
929 l_accrual_info_rec.entered_rec_tax := l_accrual_rec.entered_rec_tax;
930 l_accrual_info_rec.entered_nr_tax := l_accrual_rec.entered_nr_tax;
931 l_accrual_info_rec.accounted_rec_tax := l_accounted_rec_tax;
932 l_accrual_info_rec.accounted_nr_tax := l_accounted_nr_tax;
933
934 ---------------------------------------------------------------------
935 -- Accrual information for debit entries
936 ---------------------------------------------------------------------
937 IF (l_accrual_rec.act_debit_ccid >= 0) THEN
938
939 l_accrual_info_rec.code_combination_id := l_accrual_rec.act_debit_ccid;
940 l_accrual_info_rec.accounting_line_type := 'Charge';
941
942 IF (l_accrual_rec.min_acct_unit <= 0) THEN
943 l_accrual_info_rec.entered_dr := ROUND(l_accrual_rec.entered_dr,l_accrual_rec.currency_precision);
944 ELSE
945 l_accrual_info_rec.entered_dr := ROUND( l_accrual_rec.entered_dr
946 / l_accrual_rec.min_acct_unit)
947 * l_accrual_rec.min_acct_unit;
948 END IF;
949
950 IF (l_accrual_rec.min_func_acct_unit <= 0) THEN
951 l_accrual_info_rec.accounted_dr := ROUND(l_accounted_dr,l_accrual_rec.func_currency_precision);
952 ELSE
953 l_accrual_info_rec.accounted_dr := ROUND( l_accounted_dr
954 / l_accrual_rec.min_func_acct_unit)
955 * l_accrual_rec.min_func_acct_unit;
956 END IF;
957
958 l_accrual_info_rec.accounted_cr := NULL;
959 l_accrual_info_rec.entered_cr := NULL;
960
961 -- Add a new row to the PL/SQL tables for the accrual_info_rec
962 l_stmt_num := 90;
963 Insert_Account (
964 p_api_version => 1.0,
965 p_init_msg_list => FND_API.G_FALSE,
966 p_commit => FND_API.G_FALSE,
967 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
968 x_return_status => l_return_status,
969 x_msg_count => x_msg_count,
970 x_msg_data => x_msg_data,
971 p_accrual_info_rec => l_accrual_info_rec,
972 p_sys_setup_rec => p_sys_setup_rec
973 );
974 -- If return status is not success, add message to the log
975 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
976 l_msg_data := 'Failed inserting data in Accrual table';
977 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
978 END IF;
979
980 END IF;
981
982 ---------------------------------------------------------------------
983 -- Accrual information for credit entries
984 ---------------------------------------------------------------------
985 IF( l_accrual_rec.act_credit_ccid >= 0) THEN
986
987 l_accrual_info_rec.code_combination_id := l_accrual_rec.act_credit_ccid;
988 l_accrual_info_rec.accounting_line_type := 'Accrual';
989
990 IF (l_accrual_rec.min_acct_unit <= 0 ) THEN
991 l_accrual_info_rec.entered_cr := ROUND(l_accrual_rec.entered_cr,l_accrual_rec.currency_precision);
992 ELSE
993 l_accrual_info_rec.entered_cr := ROUND( l_accrual_rec.entered_cr
994 / l_accrual_rec.min_acct_unit)
995 * l_accrual_rec.min_acct_unit;
996 END IF;
997
998 IF (l_accrual_rec.min_func_acct_unit <= 0 ) THEN
999 l_accrual_info_rec.accounted_cr := ROUND(l_accounted_cr,l_accrual_rec.func_currency_precision);
1000 ELSE
1001 l_accrual_info_rec.accounted_cr := ROUND( l_accounted_cr
1002 / l_accrual_rec.min_func_acct_unit)
1003 * l_accrual_rec.min_func_acct_unit;
1004 END IF;
1005
1006 l_accrual_info_rec.accounted_dr := NULL;
1007 l_accrual_info_rec.entered_dr := NULL;
1008
1009 -- Add a new row to the PL/SQL tables for the accrual_info_rec
1010 l_stmt_num := 110;
1011 Insert_Account (
1012 p_api_version => 1.0,
1013 p_init_msg_list => FND_API.G_FALSE,
1014 p_commit => FND_API.G_FALSE,
1015 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1016 x_return_status => l_return_status,
1017 x_msg_count => x_msg_count,
1018 x_msg_data => x_msg_data,
1019 p_accrual_info_rec => l_accrual_info_rec,
1020 p_sys_setup_rec => p_sys_setup_rec
1021 );
1022 -- If return status is not success, add message to the log
1023 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1024 l_msg_data := 'Failed inserting data in Accrual table';
1025 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1026 END IF;
1027
1028 END IF;
1029
1030
1031 ---------------------------------------------------------------------
1032 -- Accrual information for encumbrance reversals
1033 ---------------------------------------------------------------------
1034 IF (p_sys_setup_rec.purch_encumbrance_flag = 'Y') THEN
1035
1036 l_stmt_num := 120;
1037 IF (g_encum_index_tbl.EXISTS(l_accrual_rec.distribution_id)) THEN
1038
1039 l_accrual_info_rec.rcv_acc_event_id :=
1040 g_rae_event_id_tbl(g_encum_index_tbl(l_accrual_rec.distribution_id));
1041
1042 -- Get the accounting event id of correspoding accrual entry
1043 l_accrual_info_rec.parent_rcv_acc_event_id :=
1044 g_rae_event_id_tbl(g_accrual_index_tbl(l_accrual_rec.distribution_id));
1045 ELSE
1046
1047 l_msg_data := 'Failed getting corresponding RCV_ACCOUNTING_EVENT_ID for distribution_id :'
1048 || l_accrual_rec.distribution_id;
1049 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1050
1051 END IF;
1052
1053 l_accrual_info_rec.code_combination_id := l_accrual_rec.enc_credit_ccid;
1054 l_accrual_info_rec.actual_flag := 'E';
1055 l_accrual_info_rec.currency_code := l_accrual_rec.currency_code;
1056 l_accrual_info_rec.currency_conversion_date := l_accrual_rec.encum_currency_conv_date;
1057 l_accrual_info_rec.currency_conversion_rate := l_accrual_rec.encum_currency_rate;
1058 /*Bug 6987381 : Passing the Accounting Line Type as 'Encumbrance Reversal' */
1059 l_accrual_info_rec.accounting_line_type := 'Encumbrance Reversal';
1060 l_accrual_info_rec.entered_cr := l_accounted_encum_cr/l_accrual_rec.encum_currency_rate;
1061
1062 IF (l_accrual_rec.min_func_acct_unit <= 0 ) THEN
1063 l_accrual_info_rec.accounted_cr := ROUND(l_accounted_encum_cr,l_accrual_rec.func_currency_precision);
1064 ELSE
1065 l_accrual_info_rec.accounted_cr := ROUND( l_accounted_encum_cr
1066 / l_accrual_rec.min_func_acct_unit)
1067 * l_accrual_rec.min_func_acct_unit;
1068 END IF;
1069
1070 l_accrual_info_rec.accounted_dr := NULL;
1071 l_accrual_info_rec.entered_dr := NULL;
1072
1073 l_accrual_info_rec.entered_rec_tax := NULL;
1074 l_accrual_info_rec.entered_nr_tax := NULL;
1075 l_accrual_info_rec.accounted_rec_tax := NULL;
1076 l_accrual_info_rec.accounted_nr_tax := NULL;
1077
1078 l_accrual_info_rec.accrual_method_flag := NULL;
1079
1080 -- Add a new row to the PL/SQL tables for the accrual_info_rec
1081 -- corresponding to the encumbrance reversals
1082 l_stmt_num := 140;
1083 Insert_Account (
1084 p_api_version => 1.0,
1085 p_init_msg_list => FND_API.G_FALSE,
1086 p_commit => FND_API.G_FALSE,
1087 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1088 x_return_status => l_return_status,
1089 x_msg_count => x_msg_count,
1090 x_msg_data => x_msg_data,
1091 p_accrual_info_rec => l_accrual_info_rec,
1092 p_sys_setup_rec => p_sys_setup_rec
1093 );
1094 -- If return status is not success, add message to the log
1095 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1096 l_msg_data := 'Failed inserting data in Accrual table';
1097 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1098 END IF;
1099
1100 END IF;
1101
1102 END LOOP;
1103
1104 -------------------------------------------------------------------------
1105 -- Insert the data in RCV_RECEIVING_SUB_LEDGER table
1106 -------------------------------------------------------------------------
1107 l_stmt_num := 160;
1108 Insert_AccrualSubLedger (
1109 p_api_version => 1.0,
1110 p_init_msg_list => FND_API.G_FALSE,
1111 p_commit => FND_API.G_FALSE,
1112 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1113 x_return_status => l_return_status,
1114 x_msg_count => x_msg_count,
1115 x_msg_data => x_msg_data,
1116 p_sys_setup_rec => p_sys_setup_rec
1117 );
1118 -- If return status is not success, add message to the log
1119 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1120 l_msg_data := 'Failed inserting data in RCV_RECEIVING_SUB_LEDGER';
1121 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1122 END IF;
1123
1124 IF (g_counter = 0) THEN
1125 GOTO END_PROCEDURE;
1126 END IF;
1127
1128 -------------------------------------------------------------------------
1129 -- Raise SLA Event for Period End Accruals (EVENT_TYPE_ID = 14)
1130 -- In case of ENCUMBRANCE_REVERSAL, no need to Raise SLA Event
1131 -------------------------------------------------------------------------
1132 l_stmt_num := 180;
1133
1134 /*Bug6987381 Start */
1135 l_reference_date_1 := INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(
1136 p_sys_setup_rec.transaction_date,
1137 p_sys_setup_rec.org_id);
1138 l_stmt_num := 200;
1139 DELETE FROM XLA_EVENTS_INT_GT;
1140 l_stmt_num := 220;
1141
1142 FOR l_ctr IN g_rae_event_id_tbl.FIRST..g_rae_event_id_tbl.LAST LOOP
1143 IF (g_rae_event_type_id_tbl(l_ctr) = 14) THEN
1144 INSERT INTO XLA_EVENTS_INT_GT
1145 ( application_id,
1146 ledger_id,
1147 entity_code,
1148 source_id_int_1,
1149 source_id_int_2,
1150 source_id_int_3,
1151 event_class_code,
1152 event_type_code,
1153 event_date,
1154 event_status_code,
1155 security_id_int_1,
1156 security_id_int_2,
1157 transaction_date,
1158 reference_date_1,
1159 transaction_number,
1160 budgetary_control_flag
1161 )
1162 VALUES (
1163 707,
1164 p_sys_setup_rec.set_of_books_id,
1165 'RCV_ACCOUNTING_EVENTS',
1166 0,
1167 decode(g_rae_event_type_id_tbl(l_ctr),
1168 13,g_rae_pnt_event_id_tbl(l_ctr),
1169 g_rae_event_id_tbl(l_ctr)),
1170 g_rae_inv_org_id_tbl(l_ctr),
1171 'PERIOD_END_ACCRUAL',
1172 'PERIOD_END_ACCRUAL',
1173 p_sys_setup_rec.transaction_date,
1174 XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
1175 g_rae_inv_org_id_tbl(l_ctr),
1176 p_sys_setup_rec.org_id,
1177 p_sys_setup_rec.transaction_date,
1178 l_reference_date_1,
1179 g_rae_po_number_tbl(l_ctr),
1180 NULL
1181 );
1182 END IF;
1183 END LOOP;
1184
1185 -------------------------------------------------------------------------
1186 -- Clear the data in the PL/SQL tables corresponding to the
1187 -- accounting events
1188 -------------------------------------------------------------------------
1189 g_rae_event_id_tbl.DELETE;
1190 g_rae_event_type_id_tbl.DELETE;
1191 g_rae_inv_org_id_tbl.DELETE;
1192 g_rae_po_number_tbl.DELETE;
1193 g_accrual_index_tbl.DELETE;
1194 g_encum_index_tbl.DELETE;
1195 g_rae_pnt_event_id_tbl.DELETE;
1196 /* Call XLA API to create event in bulk mode */
1197 l_stmt_num := 240;
1198 xla_events_pub_pkg.create_bulk_events(p_application_id => 707,
1199 p_ledger_id => p_sys_setup_rec.set_of_books_id,
1200 p_entity_type_code => 'RCV_ACCOUNTING_EVENTS',
1201 p_source_application_id => 201);
1202
1203 /*Bug6987381 End */
1204 <<END_PROCEDURE>>
1205 -- Procedure level log message for exit point
1206 IF (l_pLog) THEN
1207 FND_LOG.STRING(
1208 FND_LOG.LEVEL_PROCEDURE,
1209 l_module || '.end',
1210 'Create_AccrualAccount >>'
1211 );
1212 END IF;
1213
1214 -- Get message count and if 1, return message data.
1215 FND_MSG_PUB.Count_And_Get
1216 ( p_count => x_msg_count,
1217 p_data => x_msg_data
1218 );
1219
1220 -- Standard check of p_commit.
1221 IF FND_API.To_Boolean( p_commit ) THEN
1222 COMMIT WORK;
1223 END IF;
1224
1225 EXCEPTION
1226
1227 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1228 ROLLBACK TO Create_AccrualAccount_PVT;
1229 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1230
1231 IF (l_exceptionLog) THEN
1232 FND_LOG.STRING(
1233 FND_LOG.LEVEL_EXCEPTION,
1234 l_module || '.' || l_stmt_num,
1235 l_msg_data
1236 );
1237 END IF;
1238
1239 FND_MSG_PUB.Count_And_Get
1240 ( p_count => x_msg_count,
1241 p_data => x_msg_data
1242 );
1243
1244 WHEN OTHERS THEN
1245 ROLLBACK TO Create_AccrualAccount_PVT;
1246 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1247
1248 -- Unexpected level log message
1249 IF (l_uLog) THEN
1250 FND_LOG.STRING(
1251 FND_LOG.LEVEL_UNEXPECTED,
1252 l_module || '.' || l_stmt_num,
1253 SQLERRM
1254 );
1255 END IF;
1256
1257 IF FND_MSG_PUB.Check_Msg_Level
1258 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1259 THEN
1260 FND_MSG_PUB.Add_Exc_Msg
1261 ( G_PKG_NAME,
1262 l_api_name,
1263 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
1264 );
1265 END IF;
1266
1267 FND_MSG_PUB.Count_And_Get
1268 ( p_count => x_msg_count,
1269 p_data => x_msg_data
1270 );
1271
1272 END Create_AccrualAccount;
1273
1274 -----------------------------------------------------------------------------
1275 -- PROCEDURE : Insert_Account
1276 -- DESCRIPTION : The procedure adds a new row to the PL/SQL tables for
1277 -- each accrual_info_rec record.
1278 -----------------------------------------------------------------------------
1279 PROCEDURE Insert_Account
1280 (
1281 p_api_version IN NUMBER,
1282 p_init_msg_list IN VARCHAR2,
1283 p_commit IN VARCHAR2,
1284 p_validation_level IN NUMBER,
1285
1286 x_return_status OUT NOCOPY VARCHAR2,
1287 x_msg_count OUT NOCOPY NUMBER,
1288 x_msg_data OUT NOCOPY VARCHAR2,
1289
1290 p_accrual_info_rec IN CST_ACCRUAL_INFO_REC_TYPE,
1291 p_sys_setup_rec IN CST_SYS_SETUP_REC_TYPE
1292 )
1293
1294 IS
1295 l_api_name CONSTANT VARCHAR2(30) :='Insert_Account';
1296 l_api_version CONSTANT NUMBER := 1.0;
1297 l_return_status VARCHAR2(1);
1298
1299 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1300 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
1301
1302 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
1303 l_exceptionLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1304 l_pLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1305 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1306
1307 l_stmt_num NUMBER;
1308 l_msg_data VARCHAR2(240);
1309
1310 BEGIN
1311 -- Standard Start of API savepoint
1312 SAVEPOINT Insert_Account_PVT;
1313
1314 l_stmt_num := 0;
1315 -- Procedure level log message for Entry point
1316 IF (l_pLog) THEN
1317 FND_LOG.STRING(
1318 FND_LOG.LEVEL_PROCEDURE,
1319 l_module || '.begin',
1320 'Insert_Account <<');
1321 END IF;
1322
1323 -- Standard call to check for call compatibility.
1324 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1325 p_api_version,
1326 l_api_name,
1327 G_PKG_NAME )
1328 THEN
1329 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1330 END IF;
1331
1332 -- Initialize message list if p_init_msg_list is set to TRUE.
1333 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1334 FND_MSG_PUB.initialize;
1335 END IF;
1336
1337 -- Initialize API return status to success
1338 x_return_status := FND_API.G_RET_STS_SUCCESS;
1339 l_return_status := FND_API.G_RET_STS_SUCCESS;
1340
1341 -- Get the position of the new row to be added
1342 g_counter := g_distribution_id_tbl.COUNT + 1;
1343
1344 -------------------------------------------------------------------------
1345 -- Add the record values to the PL/SQL tables
1346 -------------------------------------------------------------------------
1347 l_stmt_num := 20;
1348 g_rcv_acc_event_id_tbl(g_counter) := p_accrual_info_rec.rcv_acc_event_id;
1349 g_actual_flag_tbl(g_counter) := p_accrual_info_rec.actual_flag;
1350 g_currency_code_tbl(g_counter) := p_accrual_info_rec.currency_code;
1351 g_code_combination_id_tbl(g_counter) := p_accrual_info_rec.code_combination_id;
1352 g_entered_dr_tbl(g_counter) := p_accrual_info_rec.entered_dr;
1353 g_entered_cr_tbl(g_counter) := p_accrual_info_rec.entered_cr;
1354 g_accounted_dr_tbl(g_counter) := p_accrual_info_rec.accounted_dr;
1355 g_accounted_cr_tbl(g_counter) := p_accrual_info_rec.accounted_cr;
1356 g_curr_conversion_date_tbl(g_counter) := p_accrual_info_rec.currency_conversion_date;
1357 g_user_curr_conversion_tbl(g_counter) := p_accrual_info_rec.user_currency_conversion_type;
1358 g_curr_conversion_rate_tbl(g_counter) := p_accrual_info_rec.currency_conversion_rate;
1359 g_po_header_id_tbl(g_counter) := p_accrual_info_rec.po_header_id;
1360 g_distribution_id_tbl(g_counter) := p_accrual_info_rec.distribution_id;
1361 g_po_number_tbl(g_counter) := p_accrual_info_rec.po_number;
1362 g_source_doc_quantity_tbl(g_counter) := p_accrual_info_rec.source_doc_quantity;
1363 g_entered_rec_tax_tbl(g_counter) := p_accrual_info_rec.entered_rec_tax;
1364 g_entered_nr_tax_tbl(g_counter) := p_accrual_info_rec.entered_nr_tax;
1365 g_accounted_rec_tax_tbl(g_counter) := p_accrual_info_rec.accounted_rec_tax;
1366 g_accounted_nr_tax_tbl(g_counter) := p_accrual_info_rec.accounted_nr_tax;
1367 g_accrual_method_flag_tbl(g_counter) := p_accrual_info_rec.accrual_method_flag;
1368 g_accounting_line_type_tbl(g_counter) := p_accrual_info_rec.accounting_line_type;
1369
1370 -- To be used for to map the encum reversal entries with corresponding accrual entries
1371 g_pnt_rcv_acc_event_id_tbl(g_counter) := p_accrual_info_rec.parent_rcv_acc_event_id;
1372
1373 -------------------------------------------------------------------------
1374 -- Check for number of records in l_accrual_info_tbl
1375 -- If number of records is more then 1000, insert the data in database
1376 -- and clear the pl/sql tables, this will help in saving memory.
1377 -------------------------------------------------------------------------
1378 IF (g_counter >= 1000) THEN
1379
1380 -------------------------------------------------------------------------
1381 -- Insert the data in RCV_RECEIVING_SUB_LEDGER table
1382 -------------------------------------------------------------------------
1383 l_stmt_num := 40;
1384 Insert_AccrualSubLedger (
1385 p_api_version => 1.0,
1386 p_init_msg_list => FND_API.G_FALSE,
1387 p_commit => FND_API.G_FALSE,
1388 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1389 x_return_status => l_return_status,
1390 x_msg_count => x_msg_count,
1391 x_msg_data => x_msg_data,
1392 p_sys_setup_rec => p_sys_setup_rec
1393 );
1394 -- If return status is not success, add message to the log
1395 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1396 l_msg_data := 'Failed inserting data in RCV_RECEIVING_SUB_LEDGER';
1397 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1398 END IF;
1399
1400 END IF;
1401
1402 -- Procedure level log message for exit point
1403 IF (l_pLog) THEN
1404 FND_LOG.STRING(
1405 FND_LOG.LEVEL_PROCEDURE,
1406 l_module || '.end',
1407 'Insert_Account >>'
1408 );
1409 END IF;
1410
1411 -- Get message count and if 1, return message data.
1412 FND_MSG_PUB.Count_And_Get
1413 ( p_count => x_msg_count,
1414 p_data => x_msg_data
1415 );
1416
1417 -- Standard check of p_commit.
1418 IF FND_API.To_Boolean( p_commit ) THEN
1419 COMMIT WORK;
1420 END IF;
1421
1422 EXCEPTION
1423
1424 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1425 ROLLBACK TO Insert_Account_PVT;
1426 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1427
1428 IF (l_exceptionLog) THEN
1429 FND_LOG.STRING(
1430 FND_LOG.LEVEL_EXCEPTION,
1431 l_module || '.' || l_stmt_num,
1432 l_msg_data
1433 );
1434 END IF;
1435
1436 FND_MSG_PUB.Count_And_Get
1437 ( p_count => x_msg_count,
1438 p_data => x_msg_data
1439 );
1440
1441 WHEN OTHERS THEN
1442 ROLLBACK TO Insert_Account_PVT;
1443 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1444
1445 -- Unexpected level log message
1446 IF (l_uLog) THEN
1447 FND_LOG.STRING(
1448 FND_LOG.LEVEL_UNEXPECTED,
1449 l_module || '.' || l_stmt_num,
1450 SQLERRM
1451 );
1452 END IF;
1453
1454 IF FND_MSG_PUB.Check_Msg_Level
1455 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1456 THEN
1457 FND_MSG_PUB.Add_Exc_Msg
1458 ( G_PKG_NAME,
1459 l_api_name,
1460 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
1461 );
1462 END IF;
1463
1464 FND_MSG_PUB.Count_And_Get
1465 ( p_count => x_msg_count,
1466 p_data => x_msg_data
1467 );
1468
1469 END Insert_Account;
1470
1471 -----------------------------------------------------------------------------
1472 -- PROCEDURE : Insert_AccrualSubLedger
1473 -- DESCRIPTION : Insert accounting entries in RCV_RECEIVING_SUB_LEDGER
1474 ----------------------------------------------------------------------------
1475 PROCEDURE Insert_AccrualSubLedger
1476 (
1477 p_api_version IN NUMBER,
1478 p_init_msg_list IN VARCHAR2,
1479 p_commit IN VARCHAR2,
1480 p_validation_level IN NUMBER,
1481
1482 x_return_status OUT NOCOPY VARCHAR2,
1483 x_msg_count OUT NOCOPY NUMBER,
1484 x_msg_data OUT NOCOPY VARCHAR2,
1485
1486 p_sys_setup_rec IN CST_SYS_SETUP_REC_TYPE
1487 )
1488
1489 IS
1490 l_api_name CONSTANT VARCHAR2(30) :='Insert_AccrualSubLedger';
1491 l_api_version CONSTANT NUMBER := 1.0;
1492 l_return_status VARCHAR2(1);
1493
1494 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1495 l_module CONSTANT VARCHAR2(70) := 'cst.plsql.'||l_full_name;
1496
1497 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
1498 l_exceptionLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1499 l_pLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1500 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1501
1502 l_stmt_num NUMBER;
1503 l_msg_data VARCHAR2(240);
1504 l_ctr NUMBER;
1505
1506 l_user_id NUMBER;
1507 l_login_id NUMBER;
1508
1509 BEGIN
1510 -- Standard Start of API savepoint
1511 SAVEPOINT Insert_AccrualSubLedger_PVT;
1512
1513 l_stmt_num := 0;
1514 -- Procedure level log message for Entry point
1515 IF (l_pLog) THEN
1516 FND_LOG.STRING(
1517 FND_LOG.LEVEL_PROCEDURE,
1518 l_module || '.begin',
1519 'Insert_AccrualSubLedger <<');
1520 END IF;
1521
1522 -- Standard call to check for call compatibility.
1523 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1524 p_api_version,
1525 l_api_name,
1526 G_PKG_NAME )
1527 THEN
1528 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1529 END IF;
1530
1531 -- Initialize message list if p_init_msg_list is set to TRUE.
1532 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1533 FND_MSG_PUB.initialize;
1534 END IF;
1535
1536 -- Initialize API return status to success
1537 x_return_status := FND_API.G_RET_STS_SUCCESS;
1538 l_return_status := FND_API.G_RET_STS_SUCCESS;
1539
1540 l_stmt_num := 10;
1541 l_user_id := FND_GLOBAL.USER_ID;
1542 l_login_id := FND_GLOBAL.LOGIN_ID;
1543
1544 -------------------------------------------------------------------------
1545 -- Bulk insert the values in RCV_RECEIVING_SUB_LEDGER
1546 -------------------------------------------------------------------------
1547 l_stmt_num := 20;
1548 FORALL l_ctr IN g_distribution_id_tbl.FIRST..g_distribution_id_tbl.LAST
1549 INSERT INTO rcv_receiving_sub_ledger (
1550 rcv_sub_ledger_id,
1551 rcv_transaction_id,
1552 last_update_date,
1553 last_updated_by,
1554 creation_date,
1555 created_by,
1556 last_update_login,
1557 actual_flag,
1558 currency_code,
1559 je_source_name,
1560 je_category_name,
1561 set_of_books_id,
1562 accounting_date,
1563 code_combination_id,
1564 entered_dr,
1565 entered_cr,
1566 accounted_dr,
1567 accounted_cr,
1568 currency_conversion_date,
1569 user_currency_conversion_type,
1570 currency_conversion_rate,
1571 transaction_date,
1572 period_name,
1573 chart_of_accounts_id,
1574 functional_currency_code,
1575 reference1,
1576 reference2,
1577 reference3,
1578 reference4,
1579 reference9,
1580 source_doc_quantity,
1581 entered_rec_tax,
1582 entered_nr_tax,
1583 accounted_rec_tax,
1584 accounted_nr_tax,
1585 accrual_method_flag,
1586 accounting_event_id,
1587 accounting_line_type
1588 )
1589 VALUES (
1590 DECODE( g_actual_flag_tbl(l_ctr),'E',-1,1) * rcv_receiving_sub_ledger_s.nextval,
1591 0,
1592 SYSDATE,
1593 l_user_id,
1594 SYSDATE,
1595 l_user_id,
1596 l_login_id,
1597 g_actual_flag_tbl(l_ctr),
1598 g_currency_code_tbl(l_ctr),
1599 p_sys_setup_rec.user_je_source_name,
1600 p_sys_setup_rec.user_je_category_name,
1601 p_sys_setup_rec.set_of_books_id,
1602 p_sys_setup_rec.accrual_effect_date,
1603 g_code_combination_id_tbl(l_ctr),
1604 g_entered_dr_tbl(l_ctr),
1605 g_entered_cr_tbl(l_ctr),
1606 g_accounted_dr_tbl(l_ctr),
1607 g_accounted_cr_tbl(l_ctr),
1608 g_curr_conversion_date_tbl(l_ctr),
1609 g_user_curr_conversion_tbl(l_ctr),
1610 g_curr_conversion_rate_tbl(l_ctr),
1611 p_sys_setup_rec.transaction_date,
1612 p_sys_setup_rec.period_name,
1613 p_sys_setup_rec.chart_of_accounts_id,
1614 p_sys_setup_rec.functional_currency_code,
1615 'PO',
1616 TO_CHAR(g_po_header_id_tbl(l_ctr)),
1617 TO_CHAR(g_distribution_id_tbl(l_ctr)),
1618 g_po_number_tbl(l_ctr),
1619 g_pnt_rcv_acc_event_id_tbl(l_ctr),
1620 g_source_doc_quantity_tbl(l_ctr),
1621 g_entered_rec_tax_tbl(l_ctr),
1622 g_entered_nr_tax_tbl(l_ctr),
1623 g_accounted_rec_tax_tbl(l_ctr),
1624 g_accounted_nr_tax_tbl(l_ctr),
1625 g_accrual_method_flag_tbl(l_ctr),
1626 g_rcv_acc_event_id_tbl(l_ctr),
1627 g_accounting_line_type_tbl(l_ctr)
1628 );
1629
1630 -------------------------------------------------------------------------
1631 -- Clear the PL/SQL tables
1632 -------------------------------------------------------------------------
1633 l_stmt_num := 30;
1634 g_rcv_acc_event_id_tbl.DELETE;
1635 g_actual_flag_tbl.DELETE;
1636 g_currency_code_tbl.DELETE;
1637 g_code_combination_id_tbl.DELETE;
1638 g_entered_dr_tbl.DELETE;
1639 g_entered_cr_tbl.DELETE;
1640 g_accounted_dr_tbl.DELETE;
1641 g_accounted_cr_tbl.DELETE;
1642 g_curr_conversion_date_tbl.DELETE;
1643 g_user_curr_conversion_tbl.DELETE;
1644 g_curr_conversion_rate_tbl.DELETE;
1645 g_po_header_id_tbl.DELETE;
1646 g_distribution_id_tbl.DELETE;
1647 g_po_number_tbl.DELETE;
1648 g_source_doc_quantity_tbl.DELETE;
1649 g_entered_rec_tax_tbl.DELETE;
1650 g_entered_nr_tax_tbl.DELETE;
1651 g_accounted_rec_tax_tbl.DELETE;
1652 g_accounted_nr_tax_tbl.DELETE;
1653 g_accrual_method_flag_tbl.DELETE;
1654 g_accounting_line_type_tbl.DELETE;
1655 g_pnt_rcv_acc_event_id_tbl.DELETE;
1656
1657 -- Procedure level log message for exit point
1658 IF (l_pLog) THEN
1659 FND_LOG.STRING(
1660 FND_LOG.LEVEL_PROCEDURE,
1661 l_module || '.end',
1662 'Insert_AccrualSubLedger >>'
1663 );
1664 END IF;
1665
1666 -- Get message count and if 1, return message data.
1667 FND_MSG_PUB.Count_And_Get
1668 ( p_count => x_msg_count,
1669 p_data => x_msg_data
1670 );
1671
1672 -- Standard check of p_commit.
1673 IF FND_API.To_Boolean( p_commit ) THEN
1674 COMMIT WORK;
1675 END IF;
1676
1677 EXCEPTION
1678
1679 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1680 ROLLBACK TO Insert_AccrualSubLedger_PVT;
1681 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1682
1683 IF (l_exceptionLog) THEN
1684 FND_LOG.STRING(
1685 FND_LOG.LEVEL_EXCEPTION,
1686 l_module || '.' || l_stmt_num,
1687 l_msg_data
1688 );
1689 END IF;
1690
1691 FND_MSG_PUB.Count_And_Get
1692 ( p_count => x_msg_count,
1693 p_data => x_msg_data
1694 );
1695
1696 WHEN OTHERS THEN
1697 ROLLBACK TO Insert_AccrualSubLedger_PVT;
1698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1699
1700 -- Unexpected level log message
1701 IF (l_uLog) THEN
1702 FND_LOG.STRING(
1703 FND_LOG.LEVEL_UNEXPECTED,
1704 l_module || '.' || l_stmt_num,
1705 SQLERRM
1706 );
1707 END IF;
1708
1709 IF FND_MSG_PUB.Check_Msg_Level
1710 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1711 THEN
1712 FND_MSG_PUB.Add_Exc_Msg
1713 ( G_PKG_NAME,
1714 l_api_name,
1715 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
1716 );
1717 END IF;
1718
1719 FND_MSG_PUB.Count_And_Get
1720 ( p_count => x_msg_count,
1721 p_data => x_msg_data
1722 );
1723
1724 END Insert_AccrualSubLedger;
1725
1726 ----------------------------------------------------------------------------
1727 -- PROCEDURE : Get_SystemSetup
1728 -- DESCRIPTION : Get system set-up information e.g. set_of_books,
1729 -- functional_currency, chart_of_accounts,
1730 -- purchase_encumbrance_flag etc.
1731 -----------------------------------------------------------------------------
1732 PROCEDURE Get_SystemSetup
1733 (
1734 p_api_version IN NUMBER,
1735 p_init_msg_list IN VARCHAR2,
1736 p_validation_level IN NUMBER,
1737
1738 x_return_status OUT NOCOPY VARCHAR2,
1739 x_msg_count OUT NOCOPY NUMBER,
1740 x_msg_data OUT NOCOPY VARCHAR2,
1741
1742 p_period_name IN VARCHAR2,
1743 x_sys_setup_rec OUT NOCOPY CST_SYS_SETUP_REC_TYPE
1744 )
1745 IS
1746 l_api_name CONSTANT VARCHAR2(30) :='Get_SystemSetup';
1747 l_api_version CONSTANT NUMBER := 1.0;
1748 l_return_status VARCHAR2(1);
1749
1750 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1751 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
1752
1753 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
1754 l_exceptionLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1755 l_pLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1756 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1757 l_stmt_num NUMBER;
1758 l_msg_data VARCHAR2(240);
1759
1760 l_application_id NUMBER;
1761 l_gl_installed BOOLEAN;
1762 l_status VARCHAR2(1);
1763 l_industry VARCHAR2(1);
1764 l_schema VARCHAR2(30);
1765 l_legal_entity NUMBER;
1766 l_multi_org_flag VARCHAR2(1);
1767
1768 l_batch_no NUMBER;
1769
1770 BEGIN
1771 -- Standard Start of API savepoint
1772 SAVEPOINT Get_SystemSetup_PVT;
1773
1774 l_stmt_num := 0;
1775 -- Procedure level log message for Entry point
1776 IF (l_pLog) THEN
1777 FND_LOG.STRING(
1778 FND_LOG.LEVEL_PROCEDURE,
1779 l_module || '.begin',
1780 'Get_SystemSetup <<' ||
1781 'p_period_name =' || p_period_name);
1782 END IF;
1783
1784 -- Standard call to check for call compatibility.
1785 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1786 p_api_version,
1787 l_api_name,
1788 G_PKG_NAME )
1789 THEN
1790 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1791 END IF;
1792
1793 -- Initialize message list if p_init_msg_list is set to TRUE.
1794 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1795 FND_MSG_PUB.initialize;
1796 END IF;
1797
1798 -- Initialize API return status to success
1799 x_return_status := FND_API.G_RET_STS_SUCCESS;
1800 l_return_status := FND_API.G_RET_STS_SUCCESS;
1801
1802 -- Check whether GL is installed
1803 l_stmt_num := 20;
1804 l_gl_installed := FND_INSTALLATION.GET_APP_INFO ( 'SQLGL',
1805 l_status,
1806 l_industry,
1807 l_schema);
1808 IF (l_status = 'I') THEN
1809 l_application_id := G_GL_APPLICATION_ID;
1810 ELSE
1811 l_application_id := G_PO_APPLICATION_ID;
1812 END IF;
1813
1814 x_sys_setup_rec.period_name := p_period_name;
1815
1816 -------------------------------------------------------------------------
1817 -- Get system set-up information
1818 -------------------------------------------------------------------------
1819 l_stmt_num := 30;
1820 /*Bug6987381 : Added Org_id */
1821 SELECT NVL(fsp.set_of_books_id, 0),
1822 NVL(sob.chart_of_accounts_id, 0),
1823 sob.currency_code,
1824 NVL(fsp.purch_encumbrance_flag, 'N'),
1825 DECODE( SIGN(acr.start_date - SYSDATE),
1826 1, acr.start_date,
1827 DECODE( SIGN(SYSDATE - acr.end_date),
1828 1, acr.end_date,
1829 SYSDATE)),
1830 acr.end_date,
1831 fsp.org_id
1832 INTO x_sys_setup_rec.set_of_books_id,
1833 x_sys_setup_rec.chart_of_accounts_id,
1834 x_sys_setup_rec.functional_currency_code,
1835 x_sys_setup_rec.purch_encumbrance_flag,
1836 x_sys_setup_rec.accrual_effect_date,
1837 x_sys_setup_rec.accrual_cutoff_date,
1838 x_sys_setup_rec.org_id
1839 FROM gl_period_statuses acr,
1840 financials_system_parameters fsp,
1841 gl_sets_of_books sob
1842 WHERE acr.application_id = l_application_id
1843 AND acr.set_of_books_id = fsp.set_of_books_id
1844 AND acr.period_name = p_period_name
1845 AND fsp.set_of_books_id = sob.set_of_books_id
1846 AND acr.adjustment_period_flag = 'N';
1847
1848 -------------------------------------------------------------------------
1849 -- Convert Accrual Cutoff date from Legal entity timezone to
1850 -- Server timezone
1851 -------------------------------------------------------------------------
1852 l_stmt_num := 40;
1853 SELECT TO_NUMBER(org_information2)
1854 INTO l_legal_entity
1855 FROM hr_organization_information
1856 WHERE organization_id = MO_GLOBAL.GET_CURRENT_ORG_ID
1857 AND org_information_context = 'Operating Unit Information';
1858
1859 l_stmt_num := 50;
1860 x_sys_setup_rec.period_end_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
1861 x_sys_setup_rec.accrual_cutoff_date,
1862 l_legal_entity
1863 );
1864
1865 l_stmt_num := 60;
1866 x_sys_setup_rec.transaction_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
1867 x_sys_setup_rec.accrual_effect_date,
1868 l_legal_entity
1869 );
1870
1871 -- User GL Source Name and Category. These are mandatory columns in RCV_RECEIVING_SUB_LEGDER
1872 l_stmt_num := 70;
1873 SELECT user_je_category_name
1874 INTO x_sys_setup_rec.user_je_category_name
1875 FROM gl_je_categories
1876 WHERE je_category_name = 'Accrual';
1877
1878 l_stmt_num := 80;
1879 SELECT user_je_source_name
1880 INTO x_sys_setup_rec.user_je_source_name
1881 FROM gl_je_sources
1882 WHERE je_source_name = 'Purchasing';
1883
1884 -- Procedure level log message for exit point
1885 IF (l_pLog) THEN
1886 FND_LOG.STRING(
1887 FND_LOG.LEVEL_PROCEDURE,
1888 l_module || '.end',
1889 'Get_SystemSetup >> ' ||
1890 'set_of_books_id = ' || x_sys_setup_rec.set_of_books_id ||','||
1891 'chart_of_accounts_id = ' || x_sys_setup_rec.chart_of_accounts_id ||','||
1892 'functional_currency_code = ' || x_sys_setup_rec.functional_currency_code ||','||
1893 'purch_encumbrance_flag = ' || x_sys_setup_rec.purch_encumbrance_flag ||','||
1894 'period_name = ' || x_sys_setup_rec.period_name ||','||
1895 'accrual_effect_date = ' || x_sys_setup_rec.accrual_effect_date ||','||
1896 'accrual_cutoff_date = ' || x_sys_setup_rec.accrual_cutoff_date ||','||
1897 'period_end_date = ' || x_sys_setup_rec.period_end_date
1898 );
1899 END IF;
1900
1901 -- Get message count and if 1, return message data.
1902 FND_MSG_PUB.Count_And_Get
1903 ( p_count => x_msg_count,
1904 p_data => x_msg_data
1905 );
1906
1907 EXCEPTION
1908 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1909 ROLLBACK TO Get_SystemSetup_PVT;
1910 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1911
1912 IF (l_exceptionLog) THEN
1913 FND_LOG.STRING(
1914 FND_LOG.LEVEL_EXCEPTION,
1915 l_module || '.' || l_stmt_num,
1916 l_msg_data
1917 );
1918 END IF;
1919
1920 FND_MSG_PUB.Count_And_Get
1921 ( p_count => x_msg_count,
1922 p_data => x_msg_data
1923 );
1924
1925 WHEN OTHERS THEN
1926 ROLLBACK TO Get_SystemSetup_PVT;
1927 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1928
1929 -- Unexpected level log message
1930 IF (l_uLog) THEN
1931 FND_LOG.STRING(
1932 FND_LOG.LEVEL_UNEXPECTED,
1933 l_module || '.' || l_stmt_num,
1934 SQLERRM
1935 );
1936 END IF;
1937
1938 IF FND_MSG_PUB.Check_Msg_Level
1939 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1940 THEN
1941 FND_MSG_PUB.Add_Exc_Msg
1942 ( G_PKG_NAME,
1943 l_api_name,
1944 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
1945 );
1946 END IF;
1947
1948 FND_MSG_PUB.Count_And_Get
1949 ( p_count => x_msg_count,
1950 p_data => x_msg_data
1951 );
1952
1953 END Get_SystemSetup;
1954
1955 END CST_ReceiptAccrualPerEnd_PVT;