[Home] [Help]
PACKAGE BODY: APPS.CST_RECEIPTACCRUALPEREND_PVT
Source
1 PACKAGE BODY CST_ReceiptAccrualPerEnd_PVT AS
2 /* $Header: CSTVRAPB.pls 120.12.12010000.3 2008/10/29 23:12:56 anjha 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,
368 END IF;
365 G_PKG_NAME )
366 THEN
367 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
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;
472 -----------------------------------------------------------------
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
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)),
598 'QUANTITY', pod.recoverable_tax / pod.quantity_ordered),
595 DECODE (poll.matching_basis,
596 'AMOUNT', g_rae_qty_invoiced_tbl(l_ctr)),
597 DECODE (poll.matching_basis,
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,
723 END Seed_RcvAccountingEvents;
720 p_data => x_msg_data
721 );
722
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 glct.user_conversion_type user_curr_conv_type,
801 cpea.currency_conversion_date currency_conv_date,
802 pod.recoverable_tax * cpea.accrual_quantity
803 / DECODE(poll.matching_basis,
804 'AMOUNT', pod.amount_ordered,
805 pod.quantity_ordered ) entered_rec_tax,
806 pod.nonrecoverable_tax * cpea.accrual_quantity
807 / DECODE(poll.matching_basis,
808 'AMOUNT', pod.amount_ordered,
809 pod.quantity_ordered ) entered_nr_tax,
810 pod.code_combination_id act_debit_ccid,
811 pod.accrual_account_id act_credit_ccid,
812 pod.budget_account_id enc_credit_ccid
813 FROM cst_per_end_accruals_temp cpea,
814 po_headers_all poh,
815 po_line_locations_all poll,
816 po_distributions_all pod,
817 fnd_currencies fnc1,
818 fnd_currencies fnc2,
819 gl_daily_conversion_types glct
823 AND fnc1.currency_code = l_func_currency_code
820 WHERE pod.po_distribution_id = cpea.distribution_id
821 AND pod.po_header_id = poh.po_header_id
822 AND pod.line_location_id = poll.line_location_id
824 AND fnc2.currency_code = cpea.currency_code
825 AND cpea.currency_conversion_type = glct.conversion_type(+)
826 ;
827
828 BEGIN
829 -- Standard Start of API savepoint
830 SAVEPOINT Create_AccrualAccount_PVT;
831
832 l_stmt_num := 0;
833 -- Procedure level log message for Entry point
834 IF (l_pLog) THEN
835 FND_LOG.STRING(
836 FND_LOG.LEVEL_PROCEDURE,
837 l_module || '.begin',
838 'Create_AccrualAccount <<');
839 END IF;
840
841 -- Standard call to check for call compatibility.
842 IF NOT FND_API.Compatible_API_Call ( l_api_version,
843 p_api_version,
844 l_api_name,
845 G_PKG_NAME )
846 THEN
847 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
848 END IF;
849
850 -- Initialize message list if p_init_msg_list is set to TRUE.
851 IF FND_API.to_Boolean( p_init_msg_list ) THEN
852 FND_MSG_PUB.initialize;
853 END IF;
854
855 -- Initialize API return status to success
856 x_return_status := FND_API.G_RET_STS_SUCCESS;
857 l_return_status := FND_API.G_RET_STS_SUCCESS;
858
859 l_func_currency_code := p_sys_setup_rec.functional_currency_code;
860
861 -- Loop for each row of accrual cursor
862 l_stmt_num := 20;
863 FOR l_accrual_rec IN l_accrual_csr LOOP
864
865 -- Convert the accounting values in base currency
866 l_stmt_num := 30;
867 IF (l_accrual_rec.min_acct_unit = 0) THEN
868 l_accounted_dr := ROUND(l_accrual_rec.entered_dr,l_accrual_rec.currency_precision)
869 * l_accrual_rec.currency_rate ;
870 l_accounted_cr := ROUND(l_accrual_rec.entered_cr,l_accrual_rec.currency_precision)
871 * l_accrual_rec.currency_rate ;
872 l_accounted_encum_dr := ROUND(l_accrual_rec.entered_encum_dr,l_accrual_rec.currency_precision)
873 * l_accrual_rec.currency_rate ;
874 l_accounted_encum_cr := ROUND(l_accrual_rec.entered_encum_cr,l_accrual_rec.currency_precision)
875 * l_accrual_rec.currency_rate ;
876 l_accounted_nr_tax := ROUND(l_accrual_rec.entered_nr_tax , l_accrual_rec.currency_precision)
877 * l_accrual_rec.currency_rate ;
878 l_accounted_rec_tax := ROUND(l_accrual_rec.entered_rec_tax , l_accrual_rec.currency_precision)
879 * l_accrual_rec.currency_rate ;
880 ELSE
881 l_accounted_dr := ROUND(l_accrual_rec.entered_dr/l_accrual_rec.min_acct_unit)
882 * l_accrual_rec.min_acct_unit * l_accrual_rec.currency_rate;
883 l_accounted_cr := ROUND(l_accrual_rec.entered_cr/l_accrual_rec.min_acct_unit)
884 * l_accrual_rec.min_acct_unit * l_accrual_rec.currency_rate;
885 l_accounted_encum_dr := ROUND(l_accrual_rec.entered_encum_dr/l_accrual_rec.min_acct_unit)
886 * l_accrual_rec.min_acct_unit * l_accrual_rec.currency_rate;
887 l_accounted_encum_cr := ROUND(l_accrual_rec.entered_encum_cr/l_accrual_rec.min_acct_unit)
888 * l_accrual_rec.min_acct_unit * l_accrual_rec.currency_rate;
889 l_accounted_nr_tax := ROUND(l_accrual_rec.entered_nr_tax / l_accrual_rec.min_acct_unit)
890 * l_accrual_rec.min_acct_unit * l_accrual_rec.currency_rate;
891 l_accounted_rec_tax := ROUND(l_accrual_rec.entered_rec_tax / l_accrual_rec.min_acct_unit)
892 * l_accrual_rec.min_acct_unit * l_accrual_rec.currency_rate;
893 END IF;
894
895 ---------------------------------------------------------------------
896 -- g_accrual_index_tbl(po_distribution_id) stores position of
897 -- accounting_event_id stored in PL/SQL table g_rae_event_id_tbl,
898 -- corresponding to po_distribution_id.
899 ---------------------------------------------------------------------
900 l_stmt_num := 40;
901 IF (g_accrual_index_tbl.EXISTS(l_accrual_rec.distribution_id)) THEN
902 l_accrual_info_rec.rcv_acc_event_id :=
903 g_rae_event_id_tbl(g_accrual_index_tbl(l_accrual_rec.distribution_id));
904
905 -- This will be used only for encum reversal entries
906 l_accrual_info_rec.parent_rcv_acc_event_id := NULL;
907 ELSE
908 l_msg_data := 'Failed getting corresponding RCV_ACCOUNTING_EVENT_ID for distribution_id :'
909 || l_accrual_rec.distribution_id;
910 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
911 END IF;
912
913 -- Populate the accrual_info_rec record
914 l_stmt_num := 70;
915 l_accrual_info_rec.actual_flag := 'A';
916 l_accrual_info_rec.accrual_method_flag := 'P';
917
921 l_accrual_info_rec.currency_conversion_rate := l_accrual_rec.currency_rate;
918 l_accrual_info_rec.currency_code := l_accrual_rec.currency_code;
919 l_accrual_info_rec.currency_conversion_date := l_accrual_rec.currency_conv_date;
920 l_accrual_info_rec.user_currency_conversion_type := l_accrual_rec.user_curr_conv_type;
922 l_accrual_info_rec.po_header_id := l_accrual_rec.po_header_id;
923 l_accrual_info_rec.distribution_id := l_accrual_rec.distribution_id;
924 l_accrual_info_rec.po_number := l_accrual_rec.po_number;
925 l_accrual_info_rec.source_doc_quantity := l_accrual_rec.accrual_quantity;
926
927 l_accrual_info_rec.entered_rec_tax := l_accrual_rec.entered_rec_tax;
928 l_accrual_info_rec.entered_nr_tax := l_accrual_rec.entered_nr_tax;
929 l_accrual_info_rec.accounted_rec_tax := l_accounted_rec_tax;
930 l_accrual_info_rec.accounted_nr_tax := l_accounted_nr_tax;
931
932 ---------------------------------------------------------------------
933 -- Accrual information for debit entries
934 ---------------------------------------------------------------------
935 IF (l_accrual_rec.act_debit_ccid >= 0) THEN
936
937 l_accrual_info_rec.code_combination_id := l_accrual_rec.act_debit_ccid;
938 l_accrual_info_rec.accounting_line_type := 'Charge';
939
940 IF (l_accrual_rec.min_acct_unit <= 0) THEN
941 l_accrual_info_rec.entered_dr := l_accrual_rec.entered_dr;
942 ELSE
943 l_accrual_info_rec.entered_dr := ROUND( l_accrual_rec.entered_dr
944 / l_accrual_rec.min_acct_unit)
945 * l_accrual_rec.min_acct_unit;
946 END IF;
947
948 IF (l_accrual_rec.min_func_acct_unit <= 0) THEN
949 l_accrual_info_rec.accounted_dr := l_accounted_dr;
950 ELSE
951 l_accrual_info_rec.accounted_dr := ROUND( l_accounted_dr
952 / l_accrual_rec.min_func_acct_unit)
953 * l_accrual_rec.min_func_acct_unit;
954 END IF;
955
956 l_accrual_info_rec.accounted_cr := NULL;
957 l_accrual_info_rec.entered_cr := NULL;
958
959 -- Add a new row to the PL/SQL tables for the accrual_info_rec
960 l_stmt_num := 90;
961 Insert_Account (
962 p_api_version => 1.0,
963 p_init_msg_list => FND_API.G_FALSE,
964 p_commit => FND_API.G_FALSE,
965 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
966 x_return_status => l_return_status,
967 x_msg_count => x_msg_count,
968 x_msg_data => x_msg_data,
969 p_accrual_info_rec => l_accrual_info_rec,
970 p_sys_setup_rec => p_sys_setup_rec
971 );
972 -- If return status is not success, add message to the log
973 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
974 l_msg_data := 'Failed inserting data in Accrual table';
975 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
976 END IF;
977
978 END IF;
979
980 ---------------------------------------------------------------------
981 -- Accrual information for credit entries
982 ---------------------------------------------------------------------
983 IF( l_accrual_rec.act_credit_ccid >= 0) THEN
984
985 l_accrual_info_rec.code_combination_id := l_accrual_rec.act_credit_ccid;
986 l_accrual_info_rec.accounting_line_type := 'Accrual';
987
988 IF (l_accrual_rec.min_acct_unit <= 0 ) THEN
989 l_accrual_info_rec.entered_cr := l_accrual_rec.entered_cr;
990 ELSE
991 l_accrual_info_rec.entered_cr := ROUND( l_accrual_rec.entered_cr
992 / l_accrual_rec.min_acct_unit)
993 * l_accrual_rec.min_acct_unit;
994 END IF;
995
996 IF (l_accrual_rec.min_func_acct_unit <= 0 ) THEN
997 l_accrual_info_rec.accounted_cr := l_accounted_cr;
998 ELSE
999 l_accrual_info_rec.accounted_cr := ROUND( l_accounted_cr
1000 / l_accrual_rec.min_func_acct_unit)
1001 * l_accrual_rec.min_func_acct_unit;
1002 END IF;
1003
1004 l_accrual_info_rec.accounted_dr := NULL;
1005 l_accrual_info_rec.entered_dr := NULL;
1006
1007 -- Add a new row to the PL/SQL tables for the accrual_info_rec
1008 l_stmt_num := 110;
1009 Insert_Account (
1010 p_api_version => 1.0,
1011 p_init_msg_list => FND_API.G_FALSE,
1012 p_commit => FND_API.G_FALSE,
1013 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1014 x_return_status => l_return_status,
1015 x_msg_count => x_msg_count,
1016 x_msg_data => x_msg_data,
1017 p_accrual_info_rec => l_accrual_info_rec,
1018 p_sys_setup_rec => p_sys_setup_rec
1019 );
1023 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1020 -- If return status is not success, add message to the log
1021 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1022 l_msg_data := 'Failed inserting data in Accrual table';
1024 END IF;
1025
1026 END IF;
1027
1028
1029 ---------------------------------------------------------------------
1030 -- Accrual information for encumbrance reversals
1031 ---------------------------------------------------------------------
1032 IF (p_sys_setup_rec.purch_encumbrance_flag = 'Y') THEN
1033
1034 l_stmt_num := 120;
1035 IF (g_encum_index_tbl.EXISTS(l_accrual_rec.distribution_id)) THEN
1036
1037 l_accrual_info_rec.rcv_acc_event_id :=
1038 g_rae_event_id_tbl(g_encum_index_tbl(l_accrual_rec.distribution_id));
1039
1040 -- Get the accounting event id of correspoding accrual entry
1041 l_accrual_info_rec.parent_rcv_acc_event_id :=
1042 g_rae_event_id_tbl(g_accrual_index_tbl(l_accrual_rec.distribution_id));
1043 ELSE
1044
1045 l_msg_data := 'Failed getting corresponding RCV_ACCOUNTING_EVENT_ID for distribution_id :'
1046 || l_accrual_rec.distribution_id;
1047 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1048
1049 END IF;
1050
1051 l_accrual_info_rec.code_combination_id := l_accrual_rec.enc_credit_ccid;
1052 l_accrual_info_rec.actual_flag := 'E';
1053 l_accrual_info_rec.currency_code := p_sys_setup_rec.functional_currency_code;
1054 /*Bug 6987381 : Passing the Accounting Line Type as 'Encumbrance Reversal' */
1055 l_accrual_info_rec.accounting_line_type := 'Encumbrance Reversal';
1056 IF (l_accrual_rec.min_func_acct_unit <= 0 ) THEN
1057 l_accrual_info_rec.entered_cr := l_accounted_encum_cr;
1058 ELSE
1059 l_accrual_info_rec.entered_cr := ROUND( l_accounted_encum_cr
1060 / l_accrual_rec.min_func_acct_unit)
1061 * l_accrual_rec.min_func_acct_unit;
1062 END IF;
1063
1064 l_accrual_info_rec.accounted_cr := l_accrual_info_rec.entered_cr;
1065 l_accrual_info_rec.accounted_dr := NULL;
1066 l_accrual_info_rec.entered_dr := NULL;
1067
1068 l_accrual_info_rec.entered_rec_tax := NULL;
1069 l_accrual_info_rec.entered_nr_tax := NULL;
1070 l_accrual_info_rec.accounted_rec_tax := NULL;
1071 l_accrual_info_rec.accounted_nr_tax := NULL;
1072
1073 l_accrual_info_rec.accrual_method_flag := NULL;
1074
1075 -- Add a new row to the PL/SQL tables for the accrual_info_rec
1076 -- corresponding to the encumbrance reversals
1077 l_stmt_num := 140;
1078 Insert_Account (
1079 p_api_version => 1.0,
1080 p_init_msg_list => FND_API.G_FALSE,
1081 p_commit => FND_API.G_FALSE,
1082 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1083 x_return_status => l_return_status,
1084 x_msg_count => x_msg_count,
1085 x_msg_data => x_msg_data,
1086 p_accrual_info_rec => l_accrual_info_rec,
1087 p_sys_setup_rec => p_sys_setup_rec
1088 );
1089 -- If return status is not success, add message to the log
1090 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1091 l_msg_data := 'Failed inserting data in Accrual table';
1092 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1093 END IF;
1094
1095 END IF;
1096
1097 END LOOP;
1098
1099 -------------------------------------------------------------------------
1100 -- Insert the data in RCV_RECEIVING_SUB_LEDGER table
1101 -------------------------------------------------------------------------
1102 l_stmt_num := 160;
1103 Insert_AccrualSubLedger (
1104 p_api_version => 1.0,
1105 p_init_msg_list => FND_API.G_FALSE,
1106 p_commit => FND_API.G_FALSE,
1107 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1108 x_return_status => l_return_status,
1109 x_msg_count => x_msg_count,
1110 x_msg_data => x_msg_data,
1111 p_sys_setup_rec => p_sys_setup_rec
1112 );
1113 -- If return status is not success, add message to the log
1114 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1115 l_msg_data := 'Failed inserting data in RCV_RECEIVING_SUB_LEDGER';
1116 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1117 END IF;
1118
1119 IF (g_counter = 0) THEN
1120 GOTO END_PROCEDURE;
1121 END IF;
1122
1123 -------------------------------------------------------------------------
1124 -- Raise SLA Event for Period End Accruals (EVENT_TYPE_ID = 14)
1125 -- In case of ENCUMBRANCE_REVERSAL, no need to Raise SLA Event
1126 -------------------------------------------------------------------------
1127 l_stmt_num := 180;
1128 /* Commented for bug6987381
1129 FOR l_ctr IN g_rae_event_id_tbl.FIRST..g_rae_event_id_tbl.LAST LOOP
1130
1131 IF (g_rae_event_type_id_tbl(l_ctr) = 14) THEN
1135 l_trx_rec.acct_event_type_id := g_rae_event_type_id_tbl(l_ctr);
1132 l_trx_rec.transaction_id := 0;
1133 l_trx_rec.transaction_number := g_rae_po_number_tbl(l_ctr);
1134 l_trx_rec.acct_event_id := g_rae_event_id_tbl(l_ctr);
1136 l_trx_rec.transaction_date := p_sys_setup_rec.transaction_date;
1137 l_trx_rec.inv_organization_id := g_rae_inv_org_id_tbl(l_ctr);
1138 l_trx_rec.ledger_id := p_sys_setup_rec.set_of_books_id;
1139 l_trx_rec.ENCUMBRANCE_FLAG := p_sys_setup_rec.purch_encumbrance_flag;
1140
1141 CST_XLA_PVT.Create_RCVXLAEvent(
1142 p_api_version => 1.0,
1143 p_init_msg_list => FND_API.G_FALSE,
1144 p_commit => FND_API.G_FALSE,
1145 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1146 x_return_status => l_return_status,
1147 x_msg_count => x_msg_count,
1148 x_msg_data => x_msg_data,
1149 p_trx_info => l_trx_rec
1150 );
1151 -- If return status is not success, add message to the log
1152 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1153 l_msg_data := 'Error raising SLA Event';
1154 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1155 END IF;
1156 END IF;
1157
1158 END LOOP;*/
1159 /*Bug6987381 Start */
1160 l_reference_date_1 := INV_LE_TIMEZONE_PUB.get_le_day_time_for_ou(
1161 p_sys_setup_rec.transaction_date,
1162 p_sys_setup_rec.org_id);
1163 l_stmt_num := 200;
1164 DELETE FROM XLA_EVENTS_INT_GT;
1165 l_stmt_num := 220;
1166 FORALL l_ctr IN g_rae_event_id_tbl.FIRST..g_rae_event_id_tbl.LAST
1167 /* Populate XLA Event GT */
1168 INSERT INTO XLA_EVENTS_INT_GT
1169 ( application_id,
1170 ledger_id,
1171 entity_code,
1172 source_id_int_1,
1173 source_id_int_2,
1174 source_id_int_3,
1175 event_class_code,
1176 event_type_code,
1177 event_date,
1178 event_status_code,
1179 security_id_int_1,
1180 security_id_int_2,
1181 transaction_date,
1182 reference_date_1,
1183 transaction_number,
1184 budgetary_control_flag
1185 )
1186 VALUES (
1187 707,
1188 p_sys_setup_rec.set_of_books_id,
1189 'RCV_ACCOUNTING_EVENTS',
1190 0,
1191 decode(g_rae_event_type_id_tbl(l_ctr),
1192 13,g_rae_pnt_event_id_tbl(l_ctr),
1193 g_rae_event_id_tbl(l_ctr)),
1194 g_rae_inv_org_id_tbl(l_ctr),
1195 'PERIOD_END_ACCRUAL',
1196 'PERIOD_END_ACCRUAL',
1197 p_sys_setup_rec.transaction_date,
1198 XLA_EVENTS_PUB_PKG.C_EVENT_UNPROCESSED,
1199 g_rae_inv_org_id_tbl(l_ctr),
1200 p_sys_setup_rec.org_id,
1201 p_sys_setup_rec.transaction_date,
1202 l_reference_date_1,
1203 g_rae_po_number_tbl(l_ctr),
1204 decode(g_rae_event_type_id_tbl(l_ctr),13,'Y',NULL)
1205 );
1206
1207 -------------------------------------------------------------------------
1208 -- Clear the data in the PL/SQL tables corresponding to the
1209 -- accounting events
1210 -------------------------------------------------------------------------
1211 g_rae_event_id_tbl.DELETE;
1212 g_rae_event_type_id_tbl.DELETE;
1213 g_rae_inv_org_id_tbl.DELETE;
1214 g_rae_po_number_tbl.DELETE;
1215 g_accrual_index_tbl.DELETE;
1216 g_encum_index_tbl.DELETE;
1217 g_rae_pnt_event_id_tbl.DELETE;
1218 /* Call XLA API to create event in bulk mode */
1219 l_stmt_num := 240;
1220 xla_events_pub_pkg.create_bulk_events(p_application_id => 707,
1221 p_ledger_id => p_sys_setup_rec.set_of_books_id,
1222 p_entity_type_code => 'RCV_ACCOUNTING_EVENTS',
1223 p_source_application_id => 201);
1224 l_stmt_num := 260;
1225 /* Check to see if Encumbrance is enabled */
1226
1227 IF (nvl(p_sys_setup_rec.purch_encumbrance_flag,'N') = 'Y') THEN
1228 /* Populate XLA_ACCT_PROG_EVENTS_GT for Actual entries for
1229 bulk accounting */
1230 DELETE FROM XLA_ACCT_PROG_EVENTS_GT;
1231 l_stmt_num := 280;
1232 INSERT into XLA_ACCT_PROG_EVENTS_GT (Event_Id)
1233 SELECT xe.event_id
1234 FROM xla_events xe,
1235 xla_events_int_gt xegt,
1236 xla_transaction_entities xte
1237 WHERE xe.entity_id = xte.entity_id
1238 AND xe.application_id = 707
1239 AND nvl(xegt.budgetary_control_flag,'N') = 'N'
1240 AND nvl(xe.budgetary_control_flag,'N') = 'N'
1241 AND xte.ledger_id = p_sys_setup_rec.set_of_books_id
1242 AND xte.application_id = 707
1243 AND xte.entity_code = 'RCV_ACCOUNTING_EVENTS'
1244 AND nvl(xte.source_id_int_1,-99) = xegt.source_id_int_1
1245 AND nvl(xte.source_id_int_2,-99) = xegt.source_id_int_2
1249 xla_accounting_pub_pkg.accounting_program_events
1246 AND nvl(xte.source_id_int_3,-99) = xegt.source_id_int_3;
1247 /* Call create accounting in Bulk mode */
1248 l_stmt_num := 300;
1250 ( p_application_id => 707
1251 ,p_accounting_mode => 'FINAL'
1252 ,p_gl_posting_flag => 'N'
1253 ,p_accounting_batch_id => l_batch
1254 ,p_errbuf => l_errbuf
1255 ,p_retcode => l_retcode
1256 );
1257 IF ( l_retcode <> 0) THEN
1258 l_msg_data := 'Error in Creating SLA Accounting For Actuals';
1259 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1260 END IF;
1261 /*Populate PSA_BC_XLA_EVENTS_GT for Encumbrance Event*/
1262 l_stmt_num := 320;
1263 DELETE FROM PSA_BC_XLA_EVENTS_GT;
1264 l_stmt_num := 340;
1265 INSERT INTO PSA_BC_XLA_EVENTS_GT (
1266 EVENT_ID,
1267 RESULT_CODE )
1268 SELECT xe.event_id,
1269 'UNPROCESSED'
1270 FROM xla_events xe,
1271 xla_events_int_gt xegt,
1272 xla_transaction_entities xte
1273 WHERE xe.entity_id = xte.entity_id
1274 AND xe.application_id = 707
1275 AND nvl(xegt.budgetary_control_flag,'N') = 'Y'
1276 AND nvl(xe.budgetary_control_flag,'N') = 'Y'
1277 AND xte.ledger_id = p_sys_setup_rec.set_of_books_id
1278 AND xte.application_id = 707
1279 AND xte.entity_code = 'RCV_ACCOUNTING_EVENTS'
1280 AND nvl(xte.source_id_int_1,-99) = xegt.source_id_int_1
1281 AND nvl(xte.source_id_int_2,-99) = xegt.source_id_int_2
1282 AND nvl(xte.source_id_int_3,-99) = xegt.source_id_int_3;
1283 l_stmt_num := 360;
1284 /* Delete from the GT tables for XLA_EVENTS_INT_GT and
1285 XLA_ACCT_PROG_EVENTS_GT before calling PSA API as
1286 PSA API also uses the same GTT */
1287 DELETE FROM XLA_ACCT_PROG_EVENTS_GT;
1288 DELETE FROM XLA_EVENTS_INT_GT;
1289 l_stmt_num := 380;
1290 FND_PROFILE.get('USER_ID', l_user_id);
1291 FND_PROFILE.get('RESP_ID', l_resp_id);
1292 FND_PROFILE.get('RESP_APPL_ID', l_resp_appl_id);
1293
1294 PSA_BC_XLA_PUB.Budgetary_Control (
1295 p_api_version => 1.0,
1296 p_init_msg_list => FND_API.G_FALSE,
1297 x_return_status => l_return_status,
1298 x_msg_count => x_msg_count,
1299 x_msg_data => x_msg_data,
1300 p_application_id => G_CST_APPLICATION_ID,
1301 p_bc_mode => 'F', /* Force Mode */
1302 p_override_flag => NULL,
1303 p_user_id => l_user_id,
1304 p_user_resp_id => l_resp_id,
1305 x_status_code => l_bc_status,
1306 x_packet_id => l_packet_id );
1307 IF ( l_bc_status in ('XLA_ERROR', 'FATAL') OR
1308 l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1309 l_msg_data := 'Error in Encumbrance Accounting/Budgetory Control';
1310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1311 END IF;
1312 END IF;/*Encumbrance Flag*/
1313
1314 /*Bug6987381 End */
1315 <<END_PROCEDURE>>
1316 -- Procedure level log message for exit point
1317 IF (l_pLog) THEN
1318 FND_LOG.STRING(
1319 FND_LOG.LEVEL_PROCEDURE,
1320 l_module || '.end',
1321 'Create_AccrualAccount >>'
1322 );
1323 END IF;
1324
1325 -- Get message count and if 1, return message data.
1326 FND_MSG_PUB.Count_And_Get
1327 ( p_count => x_msg_count,
1328 p_data => x_msg_data
1329 );
1330
1331 -- Standard check of p_commit.
1332 IF FND_API.To_Boolean( p_commit ) THEN
1333 COMMIT WORK;
1334 END IF;
1335
1336 EXCEPTION
1337
1338 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1339 ROLLBACK TO Create_AccrualAccount_PVT;
1340 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1341
1342 IF (l_exceptionLog) THEN
1343 FND_LOG.STRING(
1344 FND_LOG.LEVEL_EXCEPTION,
1345 l_module || '.' || l_stmt_num,
1346 l_msg_data
1347 );
1348 END IF;
1349
1350 FND_MSG_PUB.Count_And_Get
1351 ( p_count => x_msg_count,
1352 p_data => x_msg_data
1353 );
1354
1355 WHEN OTHERS THEN
1356 ROLLBACK TO Create_AccrualAccount_PVT;
1357 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1358
1359 -- Unexpected level log message
1360 IF (l_uLog) THEN
1361 FND_LOG.STRING(
1362 FND_LOG.LEVEL_UNEXPECTED,
1363 l_module || '.' || l_stmt_num,
1364 SQLERRM
1365 );
1366 END IF;
1367
1368 IF FND_MSG_PUB.Check_Msg_Level
1369 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1370 THEN
1371 FND_MSG_PUB.Add_Exc_Msg
1372 ( G_PKG_NAME,
1373 l_api_name,
1377
1374 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
1375 );
1376 END IF;
1378 FND_MSG_PUB.Count_And_Get
1379 ( p_count => x_msg_count,
1380 p_data => x_msg_data
1381 );
1382
1383 END Create_AccrualAccount;
1384
1385 -----------------------------------------------------------------------------
1386 -- PROCEDURE : Insert_Account
1387 -- DESCRIPTION : The procedure adds a new row to the PL/SQL tables for
1388 -- each accrual_info_rec record.
1389 -----------------------------------------------------------------------------
1390 PROCEDURE Insert_Account
1391 (
1392 p_api_version IN NUMBER,
1393 p_init_msg_list IN VARCHAR2,
1394 p_commit IN VARCHAR2,
1395 p_validation_level IN NUMBER,
1396
1397 x_return_status OUT NOCOPY VARCHAR2,
1398 x_msg_count OUT NOCOPY NUMBER,
1399 x_msg_data OUT NOCOPY VARCHAR2,
1400
1401 p_accrual_info_rec IN CST_ACCRUAL_INFO_REC_TYPE,
1402 p_sys_setup_rec IN CST_SYS_SETUP_REC_TYPE
1403 )
1404
1405 IS
1406 l_api_name CONSTANT VARCHAR2(30) :='Insert_Account';
1407 l_api_version CONSTANT NUMBER := 1.0;
1408 l_return_status VARCHAR2(1);
1409
1410 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1411 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
1412
1413 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
1414 l_exceptionLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1415 l_pLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1416 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1417
1418 l_stmt_num NUMBER;
1419 l_msg_data VARCHAR2(240);
1420
1421 BEGIN
1422 -- Standard Start of API savepoint
1423 SAVEPOINT Insert_Account_PVT;
1424
1425 l_stmt_num := 0;
1426 -- Procedure level log message for Entry point
1427 IF (l_pLog) THEN
1428 FND_LOG.STRING(
1429 FND_LOG.LEVEL_PROCEDURE,
1430 l_module || '.begin',
1431 'Insert_Account <<');
1432 END IF;
1433
1434 -- Standard call to check for call compatibility.
1435 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1436 p_api_version,
1437 l_api_name,
1438 G_PKG_NAME )
1439 THEN
1440 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1441 END IF;
1442
1443 -- Initialize message list if p_init_msg_list is set to TRUE.
1444 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1445 FND_MSG_PUB.initialize;
1446 END IF;
1447
1448 -- Initialize API return status to success
1449 x_return_status := FND_API.G_RET_STS_SUCCESS;
1450 l_return_status := FND_API.G_RET_STS_SUCCESS;
1451
1452 -- Get the position of the new row to be added
1453 g_counter := g_distribution_id_tbl.COUNT + 1;
1454
1455 -------------------------------------------------------------------------
1456 -- Add the record values to the PL/SQL tables
1457 -------------------------------------------------------------------------
1458 l_stmt_num := 20;
1459 g_rcv_acc_event_id_tbl(g_counter) := p_accrual_info_rec.rcv_acc_event_id;
1460 g_actual_flag_tbl(g_counter) := p_accrual_info_rec.actual_flag;
1461 g_currency_code_tbl(g_counter) := p_accrual_info_rec.currency_code;
1462 g_code_combination_id_tbl(g_counter) := p_accrual_info_rec.code_combination_id;
1463 g_entered_dr_tbl(g_counter) := p_accrual_info_rec.entered_dr;
1464 g_entered_cr_tbl(g_counter) := p_accrual_info_rec.entered_cr;
1465 g_accounted_dr_tbl(g_counter) := p_accrual_info_rec.accounted_dr;
1466 g_accounted_cr_tbl(g_counter) := p_accrual_info_rec.accounted_cr;
1467 g_curr_conversion_date_tbl(g_counter) := p_accrual_info_rec.currency_conversion_date;
1468 g_user_curr_conversion_tbl(g_counter) := p_accrual_info_rec.user_currency_conversion_type;
1469 g_curr_conversion_rate_tbl(g_counter) := p_accrual_info_rec.currency_conversion_rate;
1470 g_po_header_id_tbl(g_counter) := p_accrual_info_rec.po_header_id;
1471 g_distribution_id_tbl(g_counter) := p_accrual_info_rec.distribution_id;
1472 g_po_number_tbl(g_counter) := p_accrual_info_rec.po_number;
1473 g_source_doc_quantity_tbl(g_counter) := p_accrual_info_rec.source_doc_quantity;
1474 g_entered_rec_tax_tbl(g_counter) := p_accrual_info_rec.entered_rec_tax;
1475 g_entered_nr_tax_tbl(g_counter) := p_accrual_info_rec.entered_nr_tax;
1476 g_accounted_rec_tax_tbl(g_counter) := p_accrual_info_rec.accounted_rec_tax;
1477 g_accounted_nr_tax_tbl(g_counter) := p_accrual_info_rec.accounted_nr_tax;
1478 g_accrual_method_flag_tbl(g_counter) := p_accrual_info_rec.accrual_method_flag;
1479 g_accounting_line_type_tbl(g_counter) := p_accrual_info_rec.accounting_line_type;
1480
1484 -------------------------------------------------------------------------
1481 -- To be used for to map the encum reversal entries with corresponding accrual entries
1482 g_pnt_rcv_acc_event_id_tbl(g_counter) := p_accrual_info_rec.parent_rcv_acc_event_id;
1483
1485 -- Check for number of records in l_accrual_info_tbl
1486 -- If number of records is more then 1000, insert the data in database
1487 -- and clear the pl/sql tables, this will help in saving memory.
1488 -------------------------------------------------------------------------
1489 IF (g_counter >= 1000) THEN
1490
1491 -------------------------------------------------------------------------
1492 -- Insert the data in RCV_RECEIVING_SUB_LEDGER table
1493 -------------------------------------------------------------------------
1494 l_stmt_num := 40;
1495 Insert_AccrualSubLedger (
1496 p_api_version => 1.0,
1497 p_init_msg_list => FND_API.G_FALSE,
1498 p_commit => FND_API.G_FALSE,
1499 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1500 x_return_status => l_return_status,
1501 x_msg_count => x_msg_count,
1502 x_msg_data => x_msg_data,
1503 p_sys_setup_rec => p_sys_setup_rec
1504 );
1505 -- If return status is not success, add message to the log
1506 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1507 l_msg_data := 'Failed inserting data in RCV_RECEIVING_SUB_LEDGER';
1508 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1509 END IF;
1510
1511 END IF;
1512
1513 -- Procedure level log message for exit point
1514 IF (l_pLog) THEN
1515 FND_LOG.STRING(
1516 FND_LOG.LEVEL_PROCEDURE,
1517 l_module || '.end',
1518 'Insert_Account >>'
1519 );
1520 END IF;
1521
1522 -- Get message count and if 1, return message data.
1523 FND_MSG_PUB.Count_And_Get
1524 ( p_count => x_msg_count,
1525 p_data => x_msg_data
1526 );
1527
1528 -- Standard check of p_commit.
1529 IF FND_API.To_Boolean( p_commit ) THEN
1530 COMMIT WORK;
1531 END IF;
1532
1533 EXCEPTION
1534
1535 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1536 ROLLBACK TO Insert_Account_PVT;
1537 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1538
1539 IF (l_exceptionLog) THEN
1540 FND_LOG.STRING(
1541 FND_LOG.LEVEL_EXCEPTION,
1542 l_module || '.' || l_stmt_num,
1543 l_msg_data
1544 );
1545 END IF;
1546
1547 FND_MSG_PUB.Count_And_Get
1548 ( p_count => x_msg_count,
1549 p_data => x_msg_data
1550 );
1551
1552 WHEN OTHERS THEN
1553 ROLLBACK TO Insert_Account_PVT;
1554 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1555
1556 -- Unexpected level log message
1557 IF (l_uLog) THEN
1558 FND_LOG.STRING(
1559 FND_LOG.LEVEL_UNEXPECTED,
1560 l_module || '.' || l_stmt_num,
1561 SQLERRM
1562 );
1563 END IF;
1564
1565 IF FND_MSG_PUB.Check_Msg_Level
1566 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1567 THEN
1568 FND_MSG_PUB.Add_Exc_Msg
1569 ( G_PKG_NAME,
1570 l_api_name,
1571 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
1572 );
1573 END IF;
1574
1575 FND_MSG_PUB.Count_And_Get
1576 ( p_count => x_msg_count,
1577 p_data => x_msg_data
1578 );
1579
1580 END Insert_Account;
1581
1582 -----------------------------------------------------------------------------
1583 -- PROCEDURE : Insert_AccrualSubLedger
1584 -- DESCRIPTION : Insert accounting entries in RCV_RECEIVING_SUB_LEDGER
1585 ----------------------------------------------------------------------------
1586 PROCEDURE Insert_AccrualSubLedger
1587 (
1588 p_api_version IN NUMBER,
1589 p_init_msg_list IN VARCHAR2,
1590 p_commit IN VARCHAR2,
1591 p_validation_level IN NUMBER,
1592
1593 x_return_status OUT NOCOPY VARCHAR2,
1594 x_msg_count OUT NOCOPY NUMBER,
1595 x_msg_data OUT NOCOPY VARCHAR2,
1596
1597 p_sys_setup_rec IN CST_SYS_SETUP_REC_TYPE
1598 )
1599
1600 IS
1601 l_api_name CONSTANT VARCHAR2(30) :='Insert_AccrualSubLedger';
1602 l_api_version CONSTANT NUMBER := 1.0;
1603 l_return_status VARCHAR2(1);
1604
1605 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1606 l_module CONSTANT VARCHAR2(70) := 'cst.plsql.'||l_full_name;
1607
1611 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1608 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
1609 l_exceptionLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1610 l_pLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1612
1613 l_stmt_num NUMBER;
1614 l_msg_data VARCHAR2(240);
1615 l_ctr NUMBER;
1616
1617 l_user_id NUMBER;
1618 l_login_id NUMBER;
1619
1620 BEGIN
1621 -- Standard Start of API savepoint
1622 SAVEPOINT Insert_AccrualSubLedger_PVT;
1623
1624 l_stmt_num := 0;
1625 -- Procedure level log message for Entry point
1626 IF (l_pLog) THEN
1627 FND_LOG.STRING(
1628 FND_LOG.LEVEL_PROCEDURE,
1629 l_module || '.begin',
1630 'Insert_AccrualSubLedger <<');
1631 END IF;
1632
1633 -- Standard call to check for call compatibility.
1634 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1635 p_api_version,
1636 l_api_name,
1637 G_PKG_NAME )
1638 THEN
1639 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1640 END IF;
1641
1642 -- Initialize message list if p_init_msg_list is set to TRUE.
1643 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1644 FND_MSG_PUB.initialize;
1645 END IF;
1646
1647 -- Initialize API return status to success
1648 x_return_status := FND_API.G_RET_STS_SUCCESS;
1649 l_return_status := FND_API.G_RET_STS_SUCCESS;
1650
1651 l_stmt_num := 10;
1652 l_user_id := FND_GLOBAL.USER_ID;
1653 l_login_id := FND_GLOBAL.LOGIN_ID;
1654
1655 -------------------------------------------------------------------------
1656 -- Bulk insert the values in RCV_RECEIVING_SUB_LEDGER
1657 -------------------------------------------------------------------------
1658 l_stmt_num := 20;
1659 FORALL l_ctr IN g_distribution_id_tbl.FIRST..g_distribution_id_tbl.LAST
1660 INSERT INTO rcv_receiving_sub_ledger (
1661 rcv_sub_ledger_id,
1662 rcv_transaction_id,
1663 last_update_date,
1664 last_updated_by,
1665 creation_date,
1666 created_by,
1667 last_update_login,
1668 actual_flag,
1669 currency_code,
1670 je_source_name,
1671 je_category_name,
1672 set_of_books_id,
1673 accounting_date,
1674 code_combination_id,
1675 entered_dr,
1676 entered_cr,
1677 accounted_dr,
1678 accounted_cr,
1679 currency_conversion_date,
1680 user_currency_conversion_type,
1681 currency_conversion_rate,
1682 transaction_date,
1683 period_name,
1684 chart_of_accounts_id,
1685 functional_currency_code,
1686 reference1,
1687 reference2,
1688 reference3,
1689 reference4,
1690 reference9,
1691 source_doc_quantity,
1692 entered_rec_tax,
1693 entered_nr_tax,
1694 accounted_rec_tax,
1695 accounted_nr_tax,
1696 accrual_method_flag,
1697 accounting_event_id,
1698 accounting_line_type
1699 )
1700 VALUES (
1701 rcv_receiving_sub_ledger_s.nextval,
1702 0,
1703 SYSDATE,
1704 l_user_id,
1705 SYSDATE,
1706 l_user_id,
1707 l_login_id,
1708 g_actual_flag_tbl(l_ctr),
1709 g_currency_code_tbl(l_ctr),
1710 p_sys_setup_rec.user_je_source_name,
1711 p_sys_setup_rec.user_je_category_name,
1712 p_sys_setup_rec.set_of_books_id,
1713 p_sys_setup_rec.accrual_effect_date,
1714 g_code_combination_id_tbl(l_ctr),
1715 g_entered_dr_tbl(l_ctr),
1716 g_entered_cr_tbl(l_ctr),
1717 g_accounted_dr_tbl(l_ctr),
1718 g_accounted_cr_tbl(l_ctr),
1719 g_curr_conversion_date_tbl(l_ctr),
1720 g_user_curr_conversion_tbl(l_ctr),
1721 g_curr_conversion_rate_tbl(l_ctr),
1722 p_sys_setup_rec.transaction_date,
1723 p_sys_setup_rec.period_name,
1724 p_sys_setup_rec.chart_of_accounts_id,
1725 p_sys_setup_rec.functional_currency_code,
1726 'PO',
1727 TO_CHAR(g_po_header_id_tbl(l_ctr)),
1728 TO_CHAR(g_distribution_id_tbl(l_ctr)),
1729 g_po_number_tbl(l_ctr),
1730 g_pnt_rcv_acc_event_id_tbl(l_ctr),
1731 g_source_doc_quantity_tbl(l_ctr),
1732 g_entered_rec_tax_tbl(l_ctr),
1733 g_entered_nr_tax_tbl(l_ctr),
1734 g_accounted_rec_tax_tbl(l_ctr),
1735 g_accounted_nr_tax_tbl(l_ctr),
1736 g_accrual_method_flag_tbl(l_ctr),
1737 g_rcv_acc_event_id_tbl(l_ctr),
1738 g_accounting_line_type_tbl(l_ctr)
1739 );
1740
1744 l_stmt_num := 30;
1741 -------------------------------------------------------------------------
1742 -- Clear the PL/SQL tables
1743 -------------------------------------------------------------------------
1745 g_rcv_acc_event_id_tbl.DELETE;
1746 g_actual_flag_tbl.DELETE;
1747 g_currency_code_tbl.DELETE;
1748 g_code_combination_id_tbl.DELETE;
1749 g_entered_dr_tbl.DELETE;
1750 g_entered_cr_tbl.DELETE;
1751 g_accounted_dr_tbl.DELETE;
1752 g_accounted_cr_tbl.DELETE;
1753 g_curr_conversion_date_tbl.DELETE;
1754 g_user_curr_conversion_tbl.DELETE;
1755 g_curr_conversion_rate_tbl.DELETE;
1756 g_po_header_id_tbl.DELETE;
1757 g_distribution_id_tbl.DELETE;
1758 g_po_number_tbl.DELETE;
1759 g_source_doc_quantity_tbl.DELETE;
1760 g_entered_rec_tax_tbl.DELETE;
1761 g_entered_nr_tax_tbl.DELETE;
1762 g_accounted_rec_tax_tbl.DELETE;
1763 g_accounted_nr_tax_tbl.DELETE;
1764 g_accrual_method_flag_tbl.DELETE;
1765 g_accounting_line_type_tbl.DELETE;
1766 g_pnt_rcv_acc_event_id_tbl.DELETE;
1767
1768 -- Procedure level log message for exit point
1769 IF (l_pLog) THEN
1770 FND_LOG.STRING(
1771 FND_LOG.LEVEL_PROCEDURE,
1772 l_module || '.end',
1773 'Insert_AccrualSubLedger >>'
1774 );
1775 END IF;
1776
1777 -- Get message count and if 1, return message data.
1778 FND_MSG_PUB.Count_And_Get
1779 ( p_count => x_msg_count,
1780 p_data => x_msg_data
1781 );
1782
1783 -- Standard check of p_commit.
1784 IF FND_API.To_Boolean( p_commit ) THEN
1785 COMMIT WORK;
1786 END IF;
1787
1788 EXCEPTION
1789
1790 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1791 ROLLBACK TO Insert_AccrualSubLedger_PVT;
1792 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1793
1794 IF (l_exceptionLog) THEN
1795 FND_LOG.STRING(
1796 FND_LOG.LEVEL_EXCEPTION,
1797 l_module || '.' || l_stmt_num,
1798 l_msg_data
1799 );
1800 END IF;
1801
1802 FND_MSG_PUB.Count_And_Get
1803 ( p_count => x_msg_count,
1804 p_data => x_msg_data
1805 );
1806
1807 WHEN OTHERS THEN
1808 ROLLBACK TO Insert_AccrualSubLedger_PVT;
1809 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1810
1811 -- Unexpected level log message
1812 IF (l_uLog) THEN
1813 FND_LOG.STRING(
1814 FND_LOG.LEVEL_UNEXPECTED,
1815 l_module || '.' || l_stmt_num,
1816 SQLERRM
1817 );
1818 END IF;
1819
1820 IF FND_MSG_PUB.Check_Msg_Level
1821 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1822 THEN
1823 FND_MSG_PUB.Add_Exc_Msg
1824 ( G_PKG_NAME,
1825 l_api_name,
1826 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
1827 );
1828 END IF;
1829
1830 FND_MSG_PUB.Count_And_Get
1834
1831 ( p_count => x_msg_count,
1832 p_data => x_msg_data
1833 );
1835 END Insert_AccrualSubLedger;
1836
1837 ----------------------------------------------------------------------------
1838 -- PROCEDURE : Get_SystemSetup
1839 -- DESCRIPTION : Get system set-up information e.g. set_of_books,
1840 -- functional_currency, chart_of_accounts,
1841 -- purchase_encumbrance_flag etc.
1842 -----------------------------------------------------------------------------
1843 PROCEDURE Get_SystemSetup
1844 (
1845 p_api_version IN NUMBER,
1846 p_init_msg_list IN VARCHAR2,
1847 p_validation_level IN NUMBER,
1848
1849 x_return_status OUT NOCOPY VARCHAR2,
1850 x_msg_count OUT NOCOPY NUMBER,
1851 x_msg_data OUT NOCOPY VARCHAR2,
1852
1853 p_period_name IN VARCHAR2,
1854 x_sys_setup_rec OUT NOCOPY CST_SYS_SETUP_REC_TYPE
1855 )
1856 IS
1857 l_api_name CONSTANT VARCHAR2(30) :='Get_SystemSetup';
1858 l_api_version CONSTANT NUMBER := 1.0;
1859 l_return_status VARCHAR2(1);
1860
1861 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1862 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
1863
1864 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
1865 l_exceptionLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1866 l_pLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1867 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1868 l_stmt_num NUMBER;
1869 l_msg_data VARCHAR2(240);
1870
1871 l_application_id NUMBER;
1872 l_gl_installed BOOLEAN;
1873 l_status VARCHAR2(1);
1874 l_industry VARCHAR2(1);
1875 l_schema VARCHAR2(30);
1876 l_legal_entity NUMBER;
1877 l_multi_org_flag VARCHAR2(1);
1878
1879 l_batch_no NUMBER;
1880
1881 BEGIN
1882 -- Standard Start of API savepoint
1883 SAVEPOINT Get_SystemSetup_PVT;
1884
1885 l_stmt_num := 0;
1886 -- Procedure level log message for Entry point
1887 IF (l_pLog) THEN
1888 FND_LOG.STRING(
1889 FND_LOG.LEVEL_PROCEDURE,
1890 l_module || '.begin',
1891 'Get_SystemSetup <<' ||
1892 'p_period_name =' || p_period_name);
1893 END IF;
1894
1895 -- Standard call to check for call compatibility.
1896 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1897 p_api_version,
1898 l_api_name,
1899 G_PKG_NAME )
1900 THEN
1901 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1902 END IF;
1903
1904 -- Initialize message list if p_init_msg_list is set to TRUE.
1905 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1906 FND_MSG_PUB.initialize;
1907 END IF;
1908
1909 -- Initialize API return status to success
1910 x_return_status := FND_API.G_RET_STS_SUCCESS;
1911 l_return_status := FND_API.G_RET_STS_SUCCESS;
1912
1913 -- Check whether GL is installed
1914 l_stmt_num := 20;
1915 l_gl_installed := FND_INSTALLATION.GET_APP_INFO ( 'SQLGL',
1916 l_status,
1917 l_industry,
1918 l_schema);
1919 IF (l_status = 'I') THEN
1920 l_application_id := G_GL_APPLICATION_ID;
1921 ELSE
1922 l_application_id := G_PO_APPLICATION_ID;
1923 END IF;
1924
1925 x_sys_setup_rec.period_name := p_period_name;
1926
1927 -------------------------------------------------------------------------
1928 -- Get system set-up information
1929 -------------------------------------------------------------------------
1930 l_stmt_num := 30;
1931 /*Bug6987381 : Added Org_id */
1932 SELECT NVL(fsp.set_of_books_id, 0),
1933 NVL(sob.chart_of_accounts_id, 0),
1934 sob.currency_code,
1935 NVL(fsp.purch_encumbrance_flag, 'N'),
1936 DECODE( SIGN(acr.start_date - SYSDATE),
1937 1, acr.start_date,
1938 DECODE( SIGN(SYSDATE - acr.end_date),
1939 1, acr.end_date,
1940 SYSDATE)),
1941 acr.end_date,
1942 fsp.org_id
1943 INTO x_sys_setup_rec.set_of_books_id,
1944 x_sys_setup_rec.chart_of_accounts_id,
1945 x_sys_setup_rec.functional_currency_code,
1946 x_sys_setup_rec.purch_encumbrance_flag,
1947 x_sys_setup_rec.accrual_effect_date,
1948 x_sys_setup_rec.accrual_cutoff_date,
1949 x_sys_setup_rec.org_id
1950 FROM gl_period_statuses acr,
1951 financials_system_parameters fsp,
1952 gl_sets_of_books sob
1953 WHERE acr.application_id = l_application_id
1954 AND acr.set_of_books_id = fsp.set_of_books_id
1955 AND acr.period_name = p_period_name
1956 AND fsp.set_of_books_id = sob.set_of_books_id
1957 AND acr.adjustment_period_flag = 'N';
1958
1959 -------------------------------------------------------------------------
1960 -- Convert Accrual Cutoff date from Legal entity timezone to
1961 -- Server timezone
1962 -------------------------------------------------------------------------
1963 l_stmt_num := 40;
1964 SELECT TO_NUMBER(org_information2)
1965 INTO l_legal_entity
1966 FROM hr_organization_information
1967 WHERE organization_id = MO_GLOBAL.GET_CURRENT_ORG_ID
1968 AND org_information_context = 'Operating Unit Information';
1969
1970 l_stmt_num := 50;
1971 x_sys_setup_rec.period_end_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
1972 x_sys_setup_rec.accrual_cutoff_date,
1973 l_legal_entity
1974 );
1975
1976 l_stmt_num := 60;
1977 x_sys_setup_rec.transaction_date := INV_LE_TIMEZONE_PUB.GET_SERVER_DAY_TIME_FOR_LE(
1978 x_sys_setup_rec.accrual_effect_date,
1979 l_legal_entity
1980 );
1981
1982 -- User GL Source Name and Category. These are mandatory columns in RCV_RECEIVING_SUB_LEGDER
1983 l_stmt_num := 70;
1984 SELECT user_je_category_name
1985 INTO x_sys_setup_rec.user_je_category_name
1986 FROM gl_je_categories
1987 WHERE je_category_name = 'Accrual';
1988
1989 l_stmt_num := 80;
1990 SELECT user_je_source_name
1991 INTO x_sys_setup_rec.user_je_source_name
1992 FROM gl_je_sources
1993 WHERE je_source_name = 'Purchasing';
1994
1995 -- Procedure level log message for exit point
1996 IF (l_pLog) THEN
1997 FND_LOG.STRING(
1998 FND_LOG.LEVEL_PROCEDURE,
1999 l_module || '.end',
2000 'Get_SystemSetup >> ' ||
2001 'set_of_books_id = ' || x_sys_setup_rec.set_of_books_id ||','||
2002 'chart_of_accounts_id = ' || x_sys_setup_rec.chart_of_accounts_id ||','||
2003 'functional_currency_code = ' || x_sys_setup_rec.functional_currency_code ||','||
2004 'purch_encumbrance_flag = ' || x_sys_setup_rec.purch_encumbrance_flag ||','||
2005 'period_name = ' || x_sys_setup_rec.period_name ||','||
2006 'accrual_effect_date = ' || x_sys_setup_rec.accrual_effect_date ||','||
2007 'accrual_cutoff_date = ' || x_sys_setup_rec.accrual_cutoff_date ||','||
2008 'period_end_date = ' || x_sys_setup_rec.period_end_date
2009 );
2010 END IF;
2011
2012 -- Get message count and if 1, return message data.
2013 FND_MSG_PUB.Count_And_Get
2014 ( p_count => x_msg_count,
2015 p_data => x_msg_data
2016 );
2017
2018 EXCEPTION
2019 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2020 ROLLBACK TO Get_SystemSetup_PVT;
2021 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2022
2023 IF (l_exceptionLog) THEN
2024 FND_LOG.STRING(
2025 FND_LOG.LEVEL_EXCEPTION,
2026 l_module || '.' || l_stmt_num,
2027 l_msg_data
2028 );
2029 END IF;
2030
2031 FND_MSG_PUB.Count_And_Get
2032 ( p_count => x_msg_count,
2033 p_data => x_msg_data
2034 );
2035
2036 WHEN OTHERS THEN
2037 ROLLBACK TO Get_SystemSetup_PVT;
2038 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2039
2040 -- Unexpected level log message
2041 IF (l_uLog) THEN
2042 FND_LOG.STRING(
2043 FND_LOG.LEVEL_UNEXPECTED,
2044 l_module || '.' || l_stmt_num,
2045 SQLERRM
2046 );
2047 END IF;
2048
2049 IF FND_MSG_PUB.Check_Msg_Level
2050 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2051 THEN
2052 FND_MSG_PUB.Add_Exc_Msg
2053 ( G_PKG_NAME,
2054 l_api_name,
2055 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
2056 );
2057 END IF;
2058
2059 FND_MSG_PUB.Count_And_Get
2060 ( p_count => x_msg_count,
2061 p_data => x_msg_data
2062 );
2063
2064 END Get_SystemSetup;
2065
2066 END CST_ReceiptAccrualPerEnd_PVT;