[Home] [Help]
PACKAGE BODY: APPS.CSTPAPPR
Source
1 PACKAGE BODY CSTPAPPR AS
2 /* $Header: CSTAPPRB.pls 120.23.12020000.6 2013/03/04 09:03:04 penpan ship $ */
3
4 l_debug_flag constant VARCHAR2(1) := FND_PROFILE.VALUE('MRP_DEBUG');
5
6
7 /*============================================================================+
8 | This procedure is called by the Accounting Package for the Accounting Lib. |
9 | It first gets the details of the transaction from RCV_TRANSACTIONS |
10 | for the txn that is being processed. The appropriate procedure is then |
11 | called to create the accounting entry lines in the form of a PL/SQL table |
12 | |
13 | Logic : |
14 | |
15 | For the transaction passed in, get the txn details from RCV_TRANSACTIONS |
16 | If the current txn is period end accrual txn , call create_per_end_ae_lines |
17 | If the current txn is accrual txn, call create_rcv_ae_lines |
18 | Period End Accrual : |
19 | If current transaction is not receive or match, return |
20 | Get the acquisition cost, net quantity received |
21 | Get the document level (Shipment or Distribution) |
22 | If document level is Shipment, get all distributions against the shipmnt|
23 | If document level is Distribution, get the distribution |
24 | Loop for each distribution |
25 | Get the PO Price for the shipment |
26 | Find out the quantity Ordered at the document level |
27 | Find out the quantity received |
28 | Find out the quantity invoiced |
29 | Find out the quantity to accrue for the current transaction (based |
30 | on the uninvoiced quantity) |
31 | Find out the quantity to reverse encumber (based on the uninvoiced |
32 | quantity and excess received quantity)|
33 | Compute the amount to accrue and reverse encumber using the po price,|
34 | nonrecoverable tax and exchg rate |
35 | Debit the Expense account for the amount to accrue |
36 | Credit the Accrual account for the amount to accrue |
37 | If encumbrance is on, |
38 | Credit the Encumbrance account for the amount to reverse encumber |
39 | Return the accounting entries created in the form of PL/SQL table |
40 | On Receipt Accruals : |
41 | Find out the parent transaction for transactions which are not receive |
42 | or match |
43 | Find out the Acquisition Cost and the net quantity received |
44 | Find out if current transaction is stage 1 or stage 2 |
45 | The receiving process can be divided into 2 stages: |
46 | Stage 1 : Receiving from the supplier into the receiving dock|
47 | Stage 2 : Delivering to the final destination |
48 | Find out the net effect of the transaction |
49 | Depending on the quantity, the effective action |
50 | can either be a increase in the receive/deliver direction |
51 | or an increase in the return direction |
52 | Find out the document level of the transaction |
53 | If document level is Shipment, get all distributions against the shipmnt|
54 | If document level is Distribution, get the distribution |
55 | Loop for each distribution |
56 | Get the PO Price for the shipment |
57 | If the destination type is not expense |
58 | If stage 1 transaction |
59 | If net action is positive in receive direction |
60 | Dr. Receiving Inspection qty@Acq_cost |
61 | Cr. Accrual qty@po |
62 | Cr. IPV |
63 | Cr. ERV |
64 | Cr. Special Charges |
65 | else |
66 | Opposite of above |
67 | else |
68 | return |
69 | end if |
70 | else (if expense) |
71 | If stage 1 transaction |
72 | If net action is positive in receive direction |
73 | Dr. Receiving Inspection qty@po |
74 | Cr. Accrual qty@po |
75 | else |
76 | Opposite of above |
77 | else |
78 | If net action is positive in receive direction |
79 | Dr. Expense qty@po |
80 | Cr. Receiving Inspection qty@po |
81 | else |
82 | Opposite of above |
83 | end if |
84 | If encumbrance is on |
85 | Get quantity delivered upto the current transaction |
86 | Find out the quantity invoiced |
87 | Compute the quantity to reverse encumber |
88 | If net action is positive in receive direction |
89 | Cr. Encumbrance <amount> |
90 | else |
91 | Dr. Encumbrance <amount> |
92 | end if |
93 | end if |
94 | end if |
95 | end if |
96 | Return the accounting entries created in the form of PL/SQL table |
97 | Call the API to insert headers and lines |
98 |============================================================================*/
99
100 PROCEDURE create_acct_lines (
101 i_legal_entity IN NUMBER,
102 i_cost_type_id IN NUMBER,
103 i_cost_group_id IN NUMBER,
104 i_period_id IN NUMBER,
105 i_transaction_id IN NUMBER,
106 i_event_type_id IN VARCHAR2,
107 i_txn_type_flag IN VARCHAR2, --Bug 4586534
108 o_err_num OUT NOCOPY NUMBER,
109 o_err_code OUT NOCOPY VARCHAR2,
110 o_err_msg OUT NOCOPY VARCHAR2
111 )IS
112 l_ae_txn_rec CSTPALTY.CST_AE_TXN_REC_TYPE;
113 l_ae_line_rec_tbl CSTPALTY.CST_AE_LINE_TBL_TYPE := CSTPALTY.CST_AE_LINE_TBL_TYPE();
114 l_ae_err_rec CSTPALTY.CST_AE_ERR_REC_TYPE;
115 l_cost_type_name VARCHAR2(10);
116 l_cost_group_name VARCHAR2(10);
117 l_period_name VARCHAR2(15);
118 l_period_end_date DATE;
119 l_xfer_cg_exists NUMBER;
120 l_po_line_count NUMBER;
121 CST_TXN_TYPE_FAIL EXCEPTION;
122 CST_DIST_PKG_ERROR EXCEPTION;
123 CST_NO_RCV_LINE EXCEPTION;
124 CST_INSERT_ERROR EXCEPTION;
125
126 l_stmt_num NUMBER;
127 BEGIN
128
129 l_stmt_num := 10;
130 IF l_debug_flag = 'Y' THEN
131 fnd_file.put_line(fnd_file.log,'Create_Acct_Lines <<< ');
132 END IF;
133 ------------------------------------------------------------------------------
134 -- Determine the transaction type (Period end or on Receipt) based on the event type
135 -- the event type is a concatenated string of the form
136 -- Receipt transaction type
137 -----------------------------------------------------------------------------
138 l_stmt_num := 10;
139 l_ae_txn_rec.txn_type_flag := i_txn_type_flag ;
140 /*
141 SELECT
142 transaction_type_flag
143 INTO
144 l_ae_txn_rec.txn_type_flag
145 FROM
146 cst_accounting_event_types_v caet
147 WHERE
148 caet.event_type = i_event_type_id;
149 */
150
151 IF l_debug_flag = 'Y' THEN
152 fnd_file.put_line(fnd_file.log,'Event type: '||(l_ae_txn_rec.txn_type_flag));
153 END IF;
154
155 IF (l_ae_txn_rec.txn_type_flag in ('RCV','ACR')) THEN
156
157 l_ae_txn_rec.source_table := 'RT';
158 l_ae_txn_rec.source_id := i_transaction_id;
159
160 l_stmt_num := 20;
161
162 SELECT
163 count(rt.po_line_id) -- change for the bug 4968702
164 INTO
165 l_po_line_count
166 FROM
167 po_lines_all pol,
168 rcv_transactions rt
169 WHERE
170 pol.po_line_id = rt.po_line_id AND
171 rt.transaction_id = i_transaction_id;
172
173 IF (l_po_line_count = 0) THEN
174 RAISE CST_NO_RCV_LINE;
175 END IF;
176
177 l_stmt_num := 30;
178
179 SELECT
180 i_event_type_id,
181 null,
182 null,
183 null,
184 null,
185 rt.transaction_type,
186 rt.transaction_id,
187 pol.item_id,
188 i_legal_entity,
189 i_cost_type_id,
190 i_cost_group_id,
191 -- J Changes -------------------------------------------------------------------
192 DECODE(POLL.MATCHING_BASIS, 'AMOUNT', rt.amount, -- Changed for Complex work procurement
193 'QUANTITY', rt.primary_quantity),
194 --------------------------------------------------------------------------------
195 rt.subinventory,
196 null,
197 null,
198 null,
199 null,
200 rt.currency_code,
201 rt.currency_conversion_type,
202 nvl(rt.currency_conversion_date,transaction_date),
203 nvl(rt.currency_conversion_rate,1),
204 l_ae_txn_rec.txn_type_flag,
205 i_period_id,
206 rt.transaction_date,
207 rt.organization_id,
208 null,
209 null,
210 null,
211 null,
212 null,
213 null,
214 1, ----- inventory_asset_flag - is not used for this package
215 null,
216 nvl(poll.lcm_flag,'N')
217 INTO
218 l_ae_txn_rec.event_type_id,
219 l_ae_txn_rec.txn_action_id,
220 l_ae_txn_rec.txn_src_type_id,
221 l_ae_txn_rec.txn_src_id,
222 l_ae_txn_rec.txn_type_id,
223 l_ae_txn_rec.txn_type,
224 l_ae_txn_rec.transaction_id,
225 l_ae_txn_rec.inventory_item_id,
226 l_ae_txn_rec.legal_entity_id,
227 l_ae_txn_rec.cost_type_id,
228 l_ae_txn_rec.cost_group_id,
229 l_ae_txn_rec.primary_quantity,
230 l_ae_txn_rec.subinventory_code,
231 l_ae_txn_rec.xfer_organization_id,
232 l_ae_txn_rec.xfer_subinventory,
233 l_ae_txn_rec.xfer_transaction_id,
234 l_ae_txn_rec.dist_acct_id,
235 l_ae_txn_rec.currency_code,
236 l_ae_txn_rec.currency_conv_type,
237 l_ae_txn_rec.currency_conv_date,
238 l_ae_txn_rec.currency_conv_rate,
239 l_ae_txn_rec.ae_category,
240 l_ae_txn_rec.accounting_period_id,
241 l_ae_txn_rec.accounting_date,
242 l_ae_txn_rec.organization_id,
243 l_ae_txn_rec.mat_account,
244 l_ae_txn_rec.mat_ovhd_account,
245 l_ae_txn_rec.res_account,
246 l_ae_txn_rec.osp_account,
247 l_ae_txn_rec.ovhd_account,
248 l_ae_txn_rec.flow_schedule,
249 l_ae_txn_rec.exp_item ,
250 l_ae_txn_rec.line_id,
251 l_ae_txn_rec.lcm_flag
252 FROM
253 rcv_transactions rt,
254 po_lines_all pol,
255 po_line_locations_all poll -- Added for Complex work procurement
256 WHERE
257 rt.po_line_id = pol.po_line_id
258 AND rt.transaction_id = i_transaction_id
259 AND poll.line_location_id = rt.po_line_location_id; -- Added for Complex work procurement
260
261 l_stmt_num := 40;
262 select
263 displayed_field
264 into
265 l_ae_txn_rec.description
266 from
267 po_lookup_codes
268 where lookup_code = l_ae_txn_rec.txn_type AND
269 lookup_type = 'RCV TRANSACTION TYPE';
270
271 l_ae_txn_rec.wip_entity_type := NULL;
272
273 --- Retro Changes ---------------------------------------------------------------
274 ELSIF l_ae_txn_rec.txn_type_flag = 'ADJ' THEN
275 IF l_debug_flag = 'Y' THEN
276 FND_FILE.PUT_LINE(FND_FILE.LOG, 'RetroActive Price Adjust Event');
277 END IF;
278 -- Populate the transaction details from RCV_ACCOUNTING_EVENTS
279 l_ae_txn_rec.source_table := 'RAE';
280 l_ae_txn_rec.source_id := i_transaction_id;
281 l_ae_txn_rec.cost_group_id := i_cost_group_id;
282 l_ae_txn_rec.legal_entity_id := i_legal_entity;
283 l_ae_txn_rec.cost_type_id := i_cost_type_id;
284 l_ae_txn_rec.accounting_period_id := i_period_id;
285 l_ae_txn_rec.event_type_id := i_event_type_id;
286
287 -- Should selection be done from tables given that we are dealing
288 -- with only one transaction type (Adjust_Receive)? NO
289
290 l_ae_txn_rec.ae_category := 'ADJ'; -- Transaction_Type_Flag
291 l_ae_txn_rec.description := 'Adjust Receive';
292
293 l_stmt_num := 50;
294
295 SELECT
296 ACCOUNTING_EVENT_ID,
297 TRANSACTION_DATE,
298 UNIT_PRICE,
299 PRIOR_UNIT_PRICE,
300 PRIMARY_QUANTITY,
301 CURRENCY_CODE,
302 CURRENCY_CONVERSION_DATE,
303 CURRENCY_CONVERSION_RATE,
304 CURRENCY_CONVERSION_TYPE,
305 CREDIT_ACCOUNT_ID,
306 ORGANIZATION_ID,
307 PO_DISTRIBUTION_ID
308 INTO
309 l_ae_txn_rec.transaction_id,
310 l_ae_txn_rec.accounting_date,
311 l_ae_txn_rec.unit_price,
312 l_ae_txn_rec.prior_unit_price,
313 l_ae_txn_rec.primary_quantity,
314 l_ae_txn_rec.currency_code,
315 l_ae_txn_rec.currency_conv_date,
316 l_ae_txn_rec.currency_conv_rate,
317 l_ae_txn_rec.currency_conv_type,
318 l_ae_txn_rec.credit_account,
319 l_ae_txn_rec.organization_id,
320 l_ae_txn_rec.po_distribution_id
321 FROM
322 RCV_ACCOUNTING_EVENTS
323 WHERE
324 ACCOUNTING_EVENT_ID = i_transaction_id;
325
326 -- Rest of the fields in l_ae_txn_rec are NULL.. Is initialization to NULL
327 -- necessary
328
329 /* changes for Global Procurement */
330 /* changes for Global Procurement */
331 ELSIF (l_ae_txn_rec.txn_type_flag = 'RAE') THEN
332
333 l_ae_txn_rec.source_table := 'RAE';
334 l_ae_txn_rec.source_id := i_transaction_id;
335
336 l_stmt_num := 45;
337
338 SELECT
339 count(*)
340 INTO
341 l_po_line_count
342 FROM
343 po_lines_all pol,
344 rcv_transactions rt,
345 rcv_accounting_events rae
346 WHERE
347 pol.po_line_id = rt.po_line_id AND
348 rt.transaction_id = rae.rcv_transaction_id AND
349 rae.accounting_event_id = i_transaction_id;
350
351 IF (l_po_line_count = 0) THEN
352 RAISE CST_NO_RCV_LINE;
353 END IF;
354
355 l_stmt_num := 50;
356
357 SELECT
358 i_event_type_id,
359 null,
360 null,
361 null,
362 null,
363 decode(rae.event_type_id, 9, 'Logical Receive', 10, 'Logical Return to Vendor'),
364 rae.accounting_event_id,
365 RAE.INVENTORY_ITEM_ID,
366 i_legal_entity,
367 i_cost_type_id,
368 i_cost_group_id,
369 -- Service Line Type Changes ------------------------------------------------
370 -- For Services, Transaction_Value = TRANSACTION_AMOUNT
371 -- To fit into existing formula, Transaction_Value = Primary_Qty * Unit_Price,
372 -- we use: Primary_Quantity = TRANSACTION_AMOUNT, UNIT_PRICE = 1 in this case
373 DECODE (POLL.MATCHING_BASIS, 'AMOUNT', RAE.TRANSACTION_AMOUNT, -- Changed for Complex work procurement
374 'QUANTITY', RAE.source_doc_quantity),
375 -----------------------------------------------------------------------------
376 NULL, --subinventory code Verify if reqd especially for drop ship scenarios!!
377 null,
378 null,
379 null,
380 null,
381 rae.currency_code,
382 rae.currency_conversion_type,
383 nvl(rae.currency_conversion_date, rae.transaction_date),
384 nvl(rae.currency_conversion_rate,1),
385 l_ae_txn_rec.txn_type_flag,
386 i_period_id,
387 rae.transaction_date,
388 rae.organization_id,
389 null,
390 null,
391 null,
392 null,
393 null,
394 null,
395 1, ----- inventory_asset_flag - is not used for this package
396 null
397 INTO
398 l_ae_txn_rec.event_type_id,
399 l_ae_txn_rec.txn_action_id,
400 l_ae_txn_rec.txn_src_type_id,
401 l_ae_txn_rec.txn_src_id,
402 l_ae_txn_rec.txn_type_id,
403 l_ae_txn_rec.txn_type,
404 l_ae_txn_rec.transaction_id,
405 l_ae_txn_rec.inventory_item_id,
406 l_ae_txn_rec.legal_entity_id,
407 l_ae_txn_rec.cost_type_id,
408 l_ae_txn_rec.cost_group_id,
409 l_ae_txn_rec.primary_quantity,
410 l_ae_txn_rec.subinventory_code,
411 l_ae_txn_rec.xfer_organization_id,
412 l_ae_txn_rec.xfer_subinventory,
413 l_ae_txn_rec.xfer_transaction_id,
414 l_ae_txn_rec.dist_acct_id,
415 l_ae_txn_rec.currency_code,
416 l_ae_txn_rec.currency_conv_type,
417 l_ae_txn_rec.currency_conv_date,
418 l_ae_txn_rec.currency_conv_rate,
419 l_ae_txn_rec.ae_category,
420 l_ae_txn_rec.accounting_period_id,
421 l_ae_txn_rec.accounting_date,
422 l_ae_txn_rec.organization_id,
423 l_ae_txn_rec.mat_account,
424 l_ae_txn_rec.mat_ovhd_account,
425 l_ae_txn_rec.res_account,
426 l_ae_txn_rec.osp_account,
427 l_ae_txn_rec.ovhd_account,
428 l_ae_txn_rec.flow_schedule,
429 l_ae_txn_rec.exp_item ,
430 l_ae_txn_rec.line_id
431 FROM
432 rcv_accounting_events rae,
433 po_lines_all pol,
434 po_line_locations_all poll -- Added for Complex work procurement
435 WHERE
436 pol.po_line_id = RAE.PO_LINE_ID AND
437 poll.po_line_id = pol.po_line_id AND
438 poll.line_location_id = rae.po_line_location_id AND-- Added for Complex work procurement
439 rae.accounting_event_id = i_transaction_id;
440
441 l_stmt_num := 60;
442 l_ae_txn_rec.description := l_ae_txn_rec.txn_type;
443
444 ELSIF l_ae_txn_rec.txn_type_flag = 'LC ADJ' THEN
445 IF l_debug_flag = 'Y' THEN
446 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Landed Cost Adjust Event');
447 END IF;
448 -- Populate the transaction details from RCV_ACCOUNTING_EVENTS
449 l_ae_txn_rec.source_table := 'RAE';
450 l_ae_txn_rec.source_id := i_transaction_id;
451 l_ae_txn_rec.cost_group_id := i_cost_group_id;
452 l_ae_txn_rec.legal_entity_id := i_legal_entity;
453 l_ae_txn_rec.cost_type_id := i_cost_type_id;
454 l_ae_txn_rec.accounting_period_id := i_period_id;
455 l_ae_txn_rec.event_type_id := i_event_type_id;
456 l_ae_txn_rec.ae_category := 'LC ADJ'; -- Transaction_Type_Flag
457 l_ae_txn_rec.description := i_event_type_id;
458
459 l_stmt_num := 65;
460
461 SELECT
462 ACCOUNTING_EVENT_ID,
463 TRANSACTION_DATE,
464 UNIT_PRICE,
465 PRIOR_UNIT_PRICE,
466 PRIMARY_QUANTITY,
467 CURRENCY_CODE,
468 CURRENCY_CONVERSION_DATE,
469 CURRENCY_CONVERSION_RATE,
470 CURRENCY_CONVERSION_TYPE,
471 CREDIT_ACCOUNT_ID,
472 DEBIT_ACCOUNT_ID,
473 ORGANIZATION_ID,
474 PO_DISTRIBUTION_ID
475 INTO
476 l_ae_txn_rec.transaction_id,
477 l_ae_txn_rec.accounting_date,
478 l_ae_txn_rec.unit_price,
479 l_ae_txn_rec.prior_unit_price,
480 l_ae_txn_rec.primary_quantity,
481 l_ae_txn_rec.currency_code,
482 l_ae_txn_rec.currency_conv_date,
483 l_ae_txn_rec.currency_conv_rate,
484 l_ae_txn_rec.currency_conv_type,
485 l_ae_txn_rec.credit_account,
486 l_ae_txn_rec.debit_account,
487 l_ae_txn_rec.organization_id,
488 l_ae_txn_rec.po_distribution_id
489 FROM
490 RCV_ACCOUNTING_EVENTS
491 WHERE
492 ACCOUNTING_EVENT_ID = i_transaction_id;
493 ELSE
494 RAISE CST_TXN_TYPE_FAIL;
495 END IF;
496
497 l_stmt_num := 70;
498
499 SELECT
500 cost_type
501 INTO
502 l_cost_type_name
503 FROM
504 cst_cost_types
505 WHERE
506 cost_type_id = i_cost_type_id;
507
508 l_stmt_num := 75;
509
510 SELECT
511 cost_group
512 INTO
513 l_cost_group_name
514 FROM
515 cst_cost_groups
516 WHERE
517 cost_group_id = i_cost_group_id;
518
519 l_stmt_num := 80;
520
521 SELECT
522 period_name
523 INTO
524 l_period_name
525 FROM
526 cst_pac_periods
527 WHERE
528 pac_period_id = i_period_id;
529
530
531 l_ae_txn_rec.description := l_ae_txn_rec.description ||' : '||l_cost_type_name||' : '||l_cost_group_name||' : '||l_period_name;
532
533
534 -----------------------------------------------------------------------------
535 -- Get the period name for the period being processed
536 -----------------------------------------------------------------------------
537
538 /* Get the PERIOD_END_DATE for this period. This will
539 be used as the ACCOUNTING_DATE if the transaction is to be accrued at
540 period end */
541
542 l_stmt_num := 90;
543
544 SELECT
545 period_name,
546 period_end_date
547 INTO
548 l_ae_txn_rec.accounting_period_name,
549 l_period_end_date
550 FROM
551 cst_pac_periods
552 WHERE
553 pac_period_id = l_ae_txn_rec.accounting_period_id AND
554 cost_type_id = l_ae_txn_rec.cost_type_id AND
555 legal_entity = l_ae_txn_rec.legal_entity_id;
556
557
558 l_ae_txn_rec.category_id := NULL;
559
560
561 l_stmt_num := 100;
562
563 -----------------------------------------------------------------------------
564 -- Get the set of books id
565 -----------------------------------------------------------------------------
566
567 SELECT
568 set_of_books_id
569 INTO
570 l_ae_txn_rec.set_of_books_id
571 FROM
572 cst_le_cost_types clct
573 WHERE
574 clct.legal_entity = l_ae_txn_rec.legal_entity_id AND
575 clct.cost_type_id = l_ae_txn_rec.cost_type_id;
576
577 l_stmt_num := 110;
578
579 -----------------------------------------------------------------------------
580 -- Call the Receiving procedure if txn type is RCV
581 -- Call the Period End procedure if txn type is ACR
582 -----------------------------------------------------------------------------
583
584 IF (l_ae_txn_rec.txn_type_flag = 'RCV') THEN
585 create_rcv_ae_lines(
586 l_ae_txn_rec,
587 l_ae_line_rec_tbl,
588 l_ae_err_rec);
589 ELSIF (l_ae_txn_rec.txn_type_flag = 'ACR') THEN
590 l_ae_txn_rec.accounting_date := l_period_end_date;
591 create_per_end_ae_lines(
592 l_ae_txn_rec,
593 l_ae_line_rec_tbl,
594 l_ae_err_rec);
595 ELSIF (l_ae_txn_rec.txn_type_flag = 'ADJ') THEN
596 create_adj_ae_lines(
597 l_ae_txn_rec,
598 l_ae_line_rec_tbl,
599 l_ae_err_rec);
600 ELSIF (l_ae_txn_rec.txn_type_flag = 'RAE') THEN
601 create_rae_ae_lines(
602 l_ae_txn_rec,
603 l_ae_line_rec_tbl,
604 l_ae_err_rec);
605 ELSIF (l_ae_txn_rec.txn_type_flag = 'LC ADJ') THEN
606 create_lc_adj_ae_lines(
607 l_ae_txn_rec,
608 l_ae_line_rec_tbl,
609 l_ae_err_rec);
610 END IF;
611 IF (l_ae_err_rec.l_err_num <> 0) THEN
612 RAISE CST_DIST_PKG_ERROR;
613 END IF;
614
615
616 -----------------------------------------------------------------------------
617 -- If accounting entry lines were returned by the procedure , insert into the
618 -- accounting tables
619 -----------------------------------------------------------------------------
620
621 IF (l_ae_err_rec.l_err_num IS NULL OR l_ae_err_rec.l_err_num = 0) THEN
622 IF (l_ae_line_rec_tbl.EXISTS(1)) THEN
623 l_stmt_num := 120;
624 CSTPALPC.insert_ae_lines(
625 l_ae_txn_rec,
626 l_ae_line_rec_tbl,
627 l_ae_err_rec);
628 END IF;
629 IF (l_ae_err_rec.l_err_num <> 0) THEN
630 RAISE CST_INSERT_ERROR;
631 END IF;
632 END IF;
633
634 IF l_debug_flag = 'Y' THEN
635 fnd_file.put_line(fnd_file.log,'Create_Acct_Lines >>> ');
636 END IF;
637 EXCEPTION
638 WHEN CST_DIST_PKG_ERROR THEN
639 o_err_num := 30001;
640 o_err_code := SQLCODE;
641 o_err_msg := l_ae_err_rec.l_err_msg;
642
643 WHEN CST_TXN_TYPE_FAIL THEN
644 o_err_num := 30002;
645 o_err_code := SQLCODE;
646 o_err_msg := 'CSTPAPPR.create_acct_lines :Invalid Transaction Type Code';
647
648 WHEN CST_NO_RCV_LINE THEN
649 o_err_num := 30003;
650 o_err_code := SQLCODE;
651 FND_MESSAGE.set_name('BOM', 'CST_NO_RCV_LINE');
652 o_err_msg := FND_MESSAGE.Get;
653
654 WHEN CST_INSERT_ERROR THEN
655 o_err_num := 30004;
656 o_err_code := SQLCODE;
657 o_err_msg := 'CSTPAPPR.create_acct_lines : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
658
659 WHEN OTHERS THEN
660 o_err_num := SQLCODE;
661 o_err_code := '';
662 o_err_msg := 'CSTPAPPR.create_acct_lines : ' || to_char(l_stmt_num) || ':'||
663 substr(SQLERRM,1,180);
664
665 END create_acct_lines;
666
667
668 /*============================================================================+
669 | This procedure processes the transaction data and creates accounting entry |
670 | lines in the form of PL/SQL table and returns to the main procedure. |
671 | This procedure processes the logical accounting events |
672 | |
673 | 20-Jul-03 Anju Creation |
674 |============================================================================*/
675
676 PROCEDURE create_rae_ae_lines(
677 i_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
678 o_ae_line_rec_tbl OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
679 o_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
680 ) IS
681 l_ae_line_tbl CSTPALTY.CST_AE_LINE_TBL_TYPE;
682 l_ae_line_rec CSTPALTY.CST_AE_LINE_REC_TYPE;
683 l_curr_rec CSTPALTY.cst_ae_curr_rec_type;
684 l_err_rec CSTPALTY.cst_ae_err_rec_type;
685
686 l_dr_flag BOOLEAN;
687 l_hook NUMBER;
688 l_stmt_num NUMBER;
689 l_debit_acct_id NUMBER;
690 l_credit_acct_id NUMBER;
691 l_po_dist_id NUMBER;
692 l_unit_price NUMBER;
693 l_rec_tax NUMBER;
694 l_nr_tax NUMBER;
695 l_rae_event NUMBER;
696
697 process_error EXCEPTION;
698
699 BEGIN
700
701 if (l_debug_flag = 'Y') then
702 fnd_file.put_line(fnd_file.log,'Create_Rae_Ae_Lines <<< ');
703 end if;
704
705 l_ae_line_tbl := CSTPALTY.cst_ae_line_tbl_type();
706
707 -- Initialize local variables.
708 -- ---------------------------
709 l_err_rec.l_err_num := 0;
710 l_err_rec.l_err_code := '';
711 l_err_rec.l_err_msg := '';
712
713 -- Populate the Currency Record Type
714 -- ---------------------------------
715 l_stmt_num := 10;
716
717
718 select currency_code
719 into l_curr_rec.pri_currency
720 from gl_sets_of_books
721 where set_of_books_id = i_ae_txn_rec.set_of_books_id;
722 l_curr_rec.alt_currency := i_ae_txn_rec.currency_code;
723 l_curr_rec.currency_conv_date := i_ae_txn_rec.currency_conv_date;
724 l_curr_rec.currency_conv_type := i_ae_txn_rec.currency_conv_type;
725
726 l_stmt_num := 15;
727
728 -- J Changes --
729 -- For Service Line Types, UNIT_PRICE in RAE is NULL
730 -- Set L_UNIT_PRICE = 1, since PRIMARY_QTY above has been
731 -- set to TRANSACTION_AMOUNT (See Note)
732
733 select rae.debit_account_id, rae.credit_account_id,
734 rae.po_distribution_id,
735 DECODE(POLL.MATCHING_BASIS, 'AMOUNT', 1, -- Changed for Complex work procurement
736 'QUANTITY', rae.unit_price)
737 into l_debit_acct_id, l_credit_acct_id,
738 l_po_dist_id,
739 l_unit_price
740 from rcv_accounting_events rae,
741 PO_LINE_LOCATIONS_ALL POLL -- Changed for Complex work procurement
742 where rae.accounting_event_id = i_ae_txn_rec.transaction_id
743 and RAE.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID; -- Changed for Complex work procurement
744
745 l_ae_line_rec.actual_flag := NULL;
746 l_ae_line_rec.po_distribution_id := l_po_dist_id;
747
748 l_stmt_num := 20;
749
750 if(i_ae_txn_rec.txn_type = 'Logical Receive') then
751 l_rae_event := 9;
752 l_ae_line_rec.ae_line_type := 31;
753 elsif (i_ae_txn_rec.txn_type = 'Logical Return to Vendor') then
754 l_rae_event := 10;
755 l_ae_line_rec.ae_line_type := 16;
756 end if;
757
758 l_dr_flag := TRUE;
759 l_ae_line_rec.account := l_debit_acct_id;
760 l_ae_line_rec.transaction_value := l_unit_price * i_ae_txn_rec.primary_quantity;
761
762 l_curr_rec.currency_conv_rate := i_ae_txn_rec.currency_conv_rate;
763 l_curr_rec.currency_conv_date := i_ae_txn_rec.currency_conv_date;
764 l_curr_rec.currency_conv_type := i_ae_txn_rec.currency_conv_type;
765
766 if (l_debug_flag = 'Y') then
767 if (l_rae_event = 9) then
768 fnd_file.put_line(fnd_file.log, 'DR Clearing: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
769 elsif (l_rae_event = 10) then
770 fnd_file.put_line(fnd_file.log, 'DR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
771 end if;
772 end if;
773
774 IF ( i_ae_txn_rec.primary_quantity = 0 ) THEN
775 l_ae_line_rec.rate_or_amount := 0;
776 ELSE
777 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / i_ae_txn_rec.primary_quantity ;
778 END IF;
779
780 l_stmt_num := 30;
781
782 CSTPAPPR.insert_account (i_ae_txn_rec,
783 l_curr_rec,
784 l_dr_flag,
785 l_ae_line_rec,
786 l_ae_line_tbl,
787 l_err_rec);
788
789 if (l_err_rec.l_err_num <>0 and l_err_rec.l_err_num is not null) then
790 raise process_error;
791 end if;
792
793 -------------------------------------------
794 -- Toggle the debit flag
795 -------------------------------------------
796
797 l_dr_flag := not l_dr_flag;
798
799 l_stmt_num := 40;
800
801
802 l_ae_line_rec.account := l_credit_acct_id;
803
804 if(l_rae_event = 9) then
805 l_ae_line_rec.ae_line_type := 16;
806 elsif ( l_rae_event = 10) then
807 l_ae_line_rec.ae_line_type := 31;
808 end if;
809
810 l_ae_line_rec.transaction_value := l_unit_price * i_ae_txn_rec.primary_quantity;
811
812 l_curr_rec.currency_conv_rate := i_ae_txn_rec.currency_conv_rate;
813 l_curr_rec.currency_conv_date := i_ae_txn_rec.currency_conv_date;
814 l_curr_rec.currency_conv_type := i_ae_txn_rec.currency_conv_type;
815
816 l_stmt_num := 50;
817 if (l_debug_flag = 'Y') then
818 if (l_rae_event = 9) then
819 fnd_file.put_line(fnd_file.log,'CR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
820 ELSE
821 fnd_file.put_line(fnd_file.log,'CR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
822 END IF;
823 end if;
824
825 IF ( i_ae_txn_rec.primary_quantity = 0) THEN
826 l_ae_line_rec.rate_or_amount := 0;
827 ELSE
828 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / i_ae_txn_rec.primary_quantity ;
829 END IF;
830
831 CSTPAPPR.insert_account (i_ae_txn_rec,
832 l_curr_rec,
833 l_dr_flag,
834 l_ae_line_rec,
835 l_ae_line_tbl,
836 l_err_rec);
837 IF (l_err_rec.l_err_num <> 0 and l_err_rec.l_err_num is not null) then
838 raise process_error;
839 END IF;
840
841
842 -- Take care of rounding errors.
843 -- -----------------------------
844 l_stmt_num := 80;
845 balance_account (l_ae_line_tbl,
846 l_err_rec);
847
848 -- check error
849 if (l_err_rec.l_err_num <> 0) then
850 raise process_error;
851 end if;
852
853
854 -- Return the lines pl/sql table.
855 -- ------------------------------
856 l_stmt_num := 90;
857 o_ae_line_rec_tbl := l_ae_line_tbl;
858
859 if (l_debug_flag = 'Y') then
860 fnd_file.put_line(fnd_file.log,'Create_Rae_Ae_Lines >>>');
861 end if;
862
863 EXCEPTION
864
865 when process_error then
866 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
867 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
868 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
869
870 when others then
871 o_ae_err_rec.l_err_num := SQLCODE;
872 o_ae_err_rec.l_err_code := '';
873 o_ae_err_rec.l_err_msg := 'CSTPAPPR.create_rae_ae_lines : ' || to_char(l_stmt_num) || ':'||
874 substr(SQLERRM,1,180);
875
876 END create_rae_ae_lines;
877
878 /*============================================================================+
879 | This procedure processes the transaction data and creates accounting entry |
880 | lines in the form of PL/SQL table and returns to the main procedure. |
881 | This procedure processes the normal receiving transactions (not period end |
882 | accruals) |
883 |============================================================================*/
884
885 PROCEDURE create_rcv_ae_lines(
886 i_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
887 o_ae_line_rec_tbl OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
888 o_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
889 ) IS
890 l_ae_line_tbl CSTPALTY.CST_AE_LINE_TBL_TYPE;
891 l_ae_line_rec CSTPALTY.CST_AE_LINE_REC_TYPE;
892 l_curr_rec CSTPALTY.cst_ae_curr_rec_type;
893 l_err_rec CSTPALTY.cst_ae_err_rec_type;
894 l_par_rcv_txn NUMBER;
895 l_stage NUMBER;
896 l_par_txn_type VARCHAR2(20);
897 l_par_txn NUMBER;
898 l_net_receipt NUMBER;
899 l_doc_level VARCHAR2(1);
900 l_doc_id NUMBER;
901 l_dist_count NUMBER;
902 l_acq_exists NUMBER;
903 l_nqr NUMBER;
904 l_costed_quantity NUMBER;
905 l_acq_cost NUMBER;
906 l_acq_cost_ent_inv NUMBER;
907 l_acq_cost_ent_po NUMBER;
908 l_acq_cost_ent NUMBER;
909 l_quantity_invoiced NUMBER;
910 l_encum_amount NUMBER;
911 l_application_id NUMBER;
912 l_purch_encumbrance_type_id NUMBER;
913 l_purch_encumbrance_flag VARCHAR2(1);
914 l_match_option VARCHAR2(25);
915 l_quantity_ordered NUMBER;
916 l_tot_nqr NUMBER;
917 l_enc_flag VARCHAR2(1);
918 l_bud_enc_flag VARCHAR2(1);
919 l_dr_flag BOOLEAN;
920 l_po_rate NUMBER;
921 l_po_rate_date DATE;
922 l_po_price NUMBER;
923 l_hook NUMBER;
924 l_stmt_num NUMBER;
925
926 l_correct_ipv_amount NUMBER;
927 l_total_ipv NUMBER;
928
929 l_receive CONSTANT VARCHAR2(30) := 'RECEIVE';
930 l_correct CONSTANT VARCHAR2(30) := 'CORRECT';
931 l_rtv CONSTANT VARCHAR2(30) := 'RETURN TO VENDOR';
932 l_rtr CONSTANT VARCHAR2(30) := 'RETURN TO RECEIVING';
933 l_match CONSTANT VARCHAR2(30) := 'MATCH';
934 l_deliver CONSTANT VARCHAR2(30) := 'DELIVER';
935 l_quantity_delivered NUMBER;
936 l_dropship_type_code NUMBER := 0; -- FP Bug 5845861
937
938 l_debit_account number;
939 l_credit_account number;
940 process_error EXCEPTION;
941 CST_NO_PO_DIST EXCEPTION;
942
943 l_po_ou_id NUMBER; /* Bug 5555888 */
944 l_rcv_ou_id NUMBER; /* Bug 5555888 */
945 l_ledger_id NUMBER; /* BUG 9113487 */
946 l_uom_ratio NUMBER;
947 BEGIN
948
949 IF l_debug_flag = 'Y' THEN
950 fnd_file.put_line(fnd_file.log,'Create_Rcv_Ae_Lines <<< ');
951 END IF;
952 l_ae_line_tbl := CSTPALTY.cst_ae_line_tbl_type();
953
954 -- Initialize local variables.
955 -- ---------------------------
956 l_err_rec.l_err_num := 0;
957 l_err_rec.l_err_code := '';
958 l_err_rec.l_err_msg := '';
959
960 -- Populate the Currency Record Type
961 -- ---------------------------------
962 l_stmt_num := 10;
963
964
965 select currency_code
966 into l_curr_rec.pri_currency
967 from gl_sets_of_books
968 where set_of_books_id = i_ae_txn_rec.set_of_books_id;
969
970 /* BUG 9113487 */
971 l_stmt_num := 15;
972
973 SELECT ledger_id
974 into l_ledger_id
975 FROM cst_acct_info_v WHERE
976 organization_id=i_ae_txn_rec.organization_id;
977
978 l_curr_rec.alt_currency := i_ae_txn_rec.currency_code;
979 l_curr_rec.currency_conv_date := i_ae_txn_rec.currency_conv_date;
980 l_curr_rec.currency_conv_type := i_ae_txn_rec.currency_conv_type;
981
982 --------------------------------------------------------
983 -- First set the parent transaction id as the transaction id itself
984 --------------------------------------------------------
985
986 l_par_rcv_txn := i_ae_txn_rec.transaction_id;
987
988 ----------------------------------------------------------
989 -- If the transaction type is NOT receive or MATCH,
990 -- then it has a parent
991 ----------------------------------------------------------
992
993 IF (i_ae_txn_rec.txn_type <> l_receive) AND (i_ae_txn_rec.txn_type <> l_match) THEN
994
995 l_stmt_num := 20;
996
997 SELECT
998 rt6.transaction_id
999 INTO
1000 l_par_rcv_txn
1001 FROM
1002 rcv_transactions rt6
1003 WHERE
1004 rt6.transaction_type in (l_receive,l_match)
1005 START WITH
1006 rt6.transaction_id = i_ae_txn_rec.transaction_id
1007 CONNECT BY
1008 rt6.transaction_id = prior rt6.parent_transaction_id;
1009
1010
1011 IF l_debug_flag = 'Y' THEN
1012 fnd_file.put_line(fnd_file.log,'Parent Receive Txn: '||to_char(l_par_rcv_txn));
1013 END IF;
1014
1015 END IF; /*end if receipt or match*/
1016
1017 ------------------------------------------------------
1018 -- Get the acquisition cost for the transaction
1019 ------------------------------------------------------
1020
1021 l_stmt_num := 30;
1022
1023 SELECT
1024 count(rcv_transaction_id) -- change for the bug 4968702
1025 INTO
1026 l_acq_exists
1027 FROM
1028 cst_rcv_acq_costs crac
1029 WHERE
1030 crac.rcv_transaction_id = l_par_rcv_txn AND
1031 crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
1032 crac.cost_group_id = i_ae_txn_rec.cost_group_id;
1033
1034 IF (l_acq_exists > 0) THEN
1035
1036
1037 l_stmt_num := 32;
1038
1039 SELECT
1040 crac.net_quantity_received,
1041 crac.costed_quantity,
1042 crac.acquisition_cost
1043 INTO
1044 l_nqr,
1045 l_costed_quantity,
1046 l_acq_cost
1047 from cst_rcv_acq_costs crac
1048 where crac.rcv_transaction_id = l_par_rcv_txn AND
1049 crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
1050 crac.cost_group_id = i_ae_txn_rec.cost_group_id;
1051
1052 IF (l_nqr = 0 AND nvl(i_ae_txn_rec.lcm_flag,'N') = 'N' ) THEN
1053 IF l_debug_flag = 'Y' THEN
1054 fnd_file.put_line(fnd_file.log, 'The Net Quantity Received is Zero');
1055 END IF;
1056 return;
1057 END IF;
1058
1059 -------------------------------------------------
1060 -- Get the acquisition cost in the entered currency
1061 -- Bug # 1054868
1062 -------------------------------------------------
1063
1064 -------------------------------------------------
1065 -- Joining with ap_invoices_all in order to get the
1066 -- exchange rate. This is a result of AP's Invoice
1067 -- Lines Project
1068 -------------------------------------------------
1069
1070 l_stmt_num := 33;
1071
1072 select
1073 --Bug 15944346, use amount and rate is not accurate to get the entered account value,
1074 --it maybe different from receiving. so use price and quantity to calculate.
1075 --but if the price is null. then use amount.
1076 sum(decode(cracd.price, null, cracd.amount/NVL(aia.exchange_rate,1), cracd.price*cracd.quantity))
1077 into
1078 l_acq_cost_ent_inv
1079 from
1080 cst_rcv_acq_cost_details cracd,
1081 ap_invoice_distributions_all aida,
1082 ap_invoices_all aia
1083 where
1084 aida.invoice_distribution_id = cracd.invoice_distribution_id
1085 and aia.invoice_id = aida.invoice_id
1086 and aia.org_id = aida.org_id
1087 and cracd.header_id = (select header_id
1088 from cst_rcv_acq_costs crac
1089 where rcv_transaction_id = l_par_rcv_txn
1090 AND
1091 crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
1092 crac.cost_group_id = i_ae_txn_rec.cost_group_id)
1093 and cracd.source_type = 'INVOICE';
1094
1095 l_stmt_num := 34;
1096
1097 select
1098 sum(decode(cracd.price, null, cracd.amount/ decode(poll.match_option, --Bug 15944346
1099 'P',CSTPPACQ.get_po_rate(rt.transaction_id),
1100 'R',CSTPPACQ.get_rcv_rate(rt.transaction_id))
1101 ,cracd.price*cracd.quantity))
1102 into
1103 l_acq_cost_ent_po
1104 from
1105 cst_rcv_acq_cost_details cracd,
1106 po_line_locations_all poll,
1107 rcv_transactions rt
1108 where
1109 poll.line_location_id = cracd.po_line_location_id
1110 and cracd.header_id = (select header_id
1111 from cst_rcv_acq_costs crac
1112 where rcv_transaction_id = rt.transaction_id
1113 AND
1114 crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
1115 crac.cost_group_id = i_ae_txn_rec.cost_group_id)
1116 and rt.transaction_id = l_par_rcv_txn
1117 and rt.po_line_location_id = poll.line_location_id
1118 and cracd.source_type = 'PO';
1119 IF (l_acq_cost_ent_po < 0) THEN
1120 IF l_debug_flag = 'Y' THEN
1121 fnd_file.put_line(fnd_file.log,'Error: No Acquisition Rate');
1122 END IF;
1123 l_err_rec.l_err_num := 999;
1124 raise process_error;
1125 END IF;
1126 /* Added nvl as fix for bug 2265867 */
1127 IF (l_costed_quantity <> 0) THEN
1128 l_acq_cost_ent := (nvl(l_acq_cost_ent_inv,0) + nvl(l_acq_cost_ent_po,0))/l_costed_quantity;
1129 ELSE
1130 l_acq_cost_ent := 0;
1131 END IF;
1132 ELSE
1133 IF l_debug_flag = 'Y' THEN
1134 fnd_file.put_line(fnd_file.log,'No Acquisition Cost');
1135 END IF;
1136 return;
1137 END IF;
1138
1139
1140 ----------------------------------------------------------------
1141 -- The receiving process can be divided into 2 stages:
1142 -- Stage 1 : Receiving from the supplier into the receiving dock
1143 -- Stage 2 : Delivering to the final destination
1144 -- Stage 1 transactions are :
1145 -- RECEIVE
1146 -- CORRECTION TO RECEIVE
1147 -- MATCH
1148 -- RETURN TO VENDOR
1149 -- CORRECTION TO RETURN TO VENDOR
1150 -- Stage 2 transactions are :
1151 -- DELIVER
1152 -- CORRECTION TO DELIVER
1153 -- RETURN TO RECEIVING
1154 -- CORRECTION TO RETURN TO RECEIVING
1155 -----------------------------------------------------------------
1156
1157 -----------------------------------------------------------------
1158 -- First set the stage flag to 0
1159 -- Then determine the stage for the current transaction
1160 -----------------------------------------------------------------
1161
1162 l_stage := 0;
1163
1164 IF (i_ae_txn_rec.txn_type in (l_receive,l_match,l_rtv)) THEN
1165 l_stage := 1;
1166 ELSIF (i_ae_txn_rec.txn_type in (l_deliver,l_rtr)) THEN
1167 l_stage := 2;
1168 END IF;
1169
1170
1171 IF (i_ae_txn_rec.txn_type = l_correct) THEN
1172
1173 l_stmt_num := 50;
1174
1175 SELECT
1176 rt1.transaction_id,
1177 rt1.transaction_type
1178 INTO
1179 l_par_txn,
1180 l_par_txn_type
1181 FROM
1182 rcv_transactions rt1
1183 WHERE
1184 rt1.transaction_id = (select rt2.parent_transaction_id
1185 FROM
1186 rcv_transactions rt2
1187 WHERE
1188 rt2.transaction_id = i_ae_txn_rec.transaction_id);
1189
1190 END IF;
1191
1192 ------------------------------------------------------------------------------
1193 -- Depending on the quantity, the effective action of the current transaction
1194 -- can either be a increase in the receive/deliver direction
1195 -- or an increase in the return direction
1196 -- The following are increase in the receive/deliver direction :
1197 -- RECEIVE/MATCH
1198 -- DELIVER
1199 -- POSITIVE CORRECTION TO RECEIVE/DELIVER/MATCH
1200 -- NEGATIVE CORRECTION TO RTV/RTR
1201 -- The following are increase in the return direction :
1202 -- RETURN TO VENDOR
1203 -- RETURN TO RECEIVING
1204 -- POSITIVE CORRECTION TO RTV/RTR
1205 -- NEGATIVE CORRECTION TO RECEIVE/DELIVER/MATCH
1206 ------------------------------------------------------------------------------
1207
1208 IF (i_ae_txn_rec.txn_type = l_correct) THEN
1209 IF l_debug_flag = 'Y' THEN
1210 fnd_file.put_line(fnd_file.log,'Parent of Correct: '||l_par_txn_type);
1211 fnd_file.put_line(fnd_file.log,'Quantity: '||to_char(i_ae_txn_rec.primary_quantity));
1212 END IF;
1213
1214 IF (l_par_txn_type in (l_receive,l_match,l_rtv)) THEN
1215 l_stage := 1;
1216 ELSE
1217 l_stage := 2;
1218 END IF;
1219
1220 --BUG13994203
1221 --PAC now can has signed value, so it needn't to change the DR/CR side according to the correction quantity(negative or positive)
1222 --So now, l_net_receipt changs it's meaning, it stands for dr_flag.
1223
1224 IF (l_par_txn_type in (l_receive,l_match,l_deliver)) THEN
1225 l_net_receipt := 1;
1226 ELSE --(l_par_txn_type in (l_rtv,l_rtr))
1227 l_net_receipt := -1;
1228 END IF;
1229
1230 ELSIF (i_ae_txn_rec.txn_type in (l_rtv,l_rtr)) THEN
1231 l_net_receipt := -1;
1232 ELSE
1233 l_net_receipt := 1;
1234 END IF;
1235 IF l_debug_flag = 'Y' THEN
1236 fnd_file.put_line(fnd_file.log,'Net Receipt or Return: '||to_char(l_net_receipt));
1237 END IF;
1238 ------------------------------------------------------------
1239 -- A receipt can be at the shipment level or the distribution level:
1240 -- When receiving and delivering in one step, it is at the distribution level
1241 -- When receiving and delivering as two different steps, it is shipment level
1242 -- Get the document level as 'S' for shipment and 'D' for distribution
1243 ------------------------------------------------------------
1244
1245
1246 l_stmt_num := 60;
1247
1248 SELECT
1249 decode(rt.po_distribution_id, NULL, 'S', 'D'),
1250 nvl(rt.po_distribution_id, rt.po_line_location_id),
1251 nvl(dropship_type_code,3),
1252 decode(nvl(rt.source_doc_quantity,0),0,1,
1253 decode( nvl(rt.primary_quantity,0),0,1,
1254 nvl(rt.primary_quantity,0)/nvl(rt.source_doc_quantity,0)
1255 )
1256 )
1257 INTO
1258 l_doc_level,
1259 l_doc_id,
1260 l_dropship_type_code,
1261 l_uom_ratio
1262 FROM
1263 rcv_transactions rt
1264 WHERE
1265 rt.transaction_id = i_ae_txn_rec.transaction_id;
1266
1267 IF l_debug_flag = 'Y' THEN
1268 fnd_file.put_line(fnd_file.log,'Document Level: '||l_doc_level);
1269 fnd_file.put_line(fnd_file.log,'Document ID: '||to_char(l_doc_id));
1270 fnd_file.put_line(fnd_file.log,'Quantity: '||to_char(i_ae_txn_rec.primary_quantity));
1271 END IF;
1272
1273 SELECT
1274 count(*)
1275 into
1276 l_dist_count
1277 FROM
1278 po_distributions_all
1279 WHERE
1280 -- begin fix for perf bug 2581067
1281 (
1282 (l_doc_level = 'D' AND po_distribution_id = l_doc_id)
1283 OR (l_doc_level = 'S' AND line_location_id = l_doc_id)
1284 )
1285 AND rownum <= 1;
1286 -- end fix for perf bug 2581067. replaced the following:
1287
1288 IF (l_dist_count = 0) THEN
1289 IF l_debug_flag = 'Y' THEN
1290 fnd_file.put_line(fnd_file.log,'Error: No Distributions for Document: '||to_char(l_doc_id));
1291 END IF;
1292 RAISE CST_NO_PO_DIST;
1293 END IF;
1294
1295
1296 ------------------------------------------------------------------
1297 -- If the document level is Shipment, get all the distributions
1298 -- for the Shipment, against which the receipt occurred.
1299 -- If the document level is Distribution, get the distribution
1300 -- Loop for each distribution that is accrue at receipt
1301 ------------------------------------------------------------------
1302
1303 DECLARE
1304 CURSOR c_receive_dists IS
1305 SELECT
1306 decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_rate,nvl(pod.rate,decode(l_acq_cost_ent,0,1,l_acq_cost/l_acq_cost_ent))) "EXCHG_RATE",
1307 decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_date,pod.rate_date) "EXCHG_DATE",
1308 -- J Changes -------------------------------------------------------------------------
1309 DECODE(POLL.MATCHING_BASIS, 'AMOUNT', 1, -- Changed for Complex work procurement
1310 'QUANTITY', 0 ) "SERVICE_FLAG",
1311 --------------------------------------------------------------------------------------
1312 POD.po_distribution_id "PO_DISTRIBUTION_ID",
1313 POLL.line_location_id "PO_LINE_LOCATION_ID",
1314 POD.code_combination_id "EXPENSE_ACCOUNT_ID",
1315 POD.destination_type_code "DESTINATION_TYPE_CODE",
1316 decode(l_dropship_type_code, 2, RP.clearing_account_id, RP.receiving_account_id) "RECEIVING_ACCOUNT_ID", -- FP Bug 5845861 fix: pickup the clearing account for DS with old accounting
1317 POD.accrual_account_id "ACCRUAL_ACCOUNT_ID",
1318 nvl(POD.budget_account_id,-1) "ENCUMBRANCE_ACCOUNT_ID",
1319 decode(l_doc_level,'D', 1, DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work procurement
1320 'AMOUNT', POD.AMOUNT_ORDERED/POLL.AMOUNT,
1321 'QUANTITY',POD.QUANTITY_ORDERED/POLL.QUANTITY))
1322 * i_ae_txn_rec.primary_quantity "DIST_QUANTITY",
1323 -- J Changes ----------------------------------------------------------------------------
1324 (po_tax_sv.get_tax('PO',pod.po_distribution_id) /
1325 DECODE(POLL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_ORDERED, -- Changed for Complex work procurement
1326 'QUANTITY',POD.QUANTITY_ORDERED*l_uom_ratio) ) "TAX"
1327 -----------------------------------------------------------------------------------------
1328 FROM
1329 po_distributions_all pod,
1330 po_line_locations_all poll,
1331 -- J Changes ----------------------------------------------------------------------------
1332 PO_LINES_ALL POL,
1333 -----------------------------------------------------------------------------------------
1334 rcv_parameters rp
1335 WHERE
1336 -- begin fix for perf bug 2581067
1337 (
1338 (l_doc_level = 'D' AND pod.po_distribution_id = l_doc_id)
1339 OR (l_doc_level = 'S' AND poll.line_location_id = l_doc_id)
1340 )
1341 -- end fix for perf bug 2581067
1342 and pod.line_location_id = poll.line_location_id
1343 -- J Changes ----------------------------------------------------------------------------
1344 AND POLL.PO_LINE_ID = POL.PO_LINE_ID
1345 -----------------------------------------------------------------------------------------
1346 and rp.organization_id = pod.destination_organization_id
1347 and pod.destination_type_code in ('INVENTORY', 'SHOP FLOOR')
1348 and nvl(POLL.accrue_on_receipt_flag,'N') = 'Y'
1349 /* and nvl(POD.accrue_on_receipt_flag,'N') = 'Y' */;
1350
1351 l_lcm_adj_period NUMBER;
1352 l_landed_cost_abs_account NUMBER;
1353
1354 BEGIN
1355 SELECT nvl(max(LANDED_COST_ABS_ACCOUNT),-1)
1356 INTO l_landed_cost_abs_account
1357 FROM CST_ORG_COST_GROUP_ACCOUNTS coga
1358 WHERE coga.legal_entity_id = i_ae_txn_rec.legal_entity_id
1359 AND coga.cost_type_id = i_ae_txn_rec.cost_type_id
1360 AND coga.cost_group_id = i_ae_txn_rec.cost_group_id;
1361
1362 FOR c_receipts_rec IN c_receive_dists LOOP
1363 l_stmt_num := 62;
1364 l_ae_line_rec.actual_flag := NULL;
1365 l_ae_line_rec.po_distribution_id := c_receipts_rec.po_distribution_id;
1366
1367 ---------------------------------------------------------
1368 -- The PO Price is in terms of the PO UOM
1369 -- Convert it in terms of the primary UOM for the item
1370 ---------------------------------------------------------
1371
1372 SELECT
1373 -- J Changes ----------------------------------------------------------------------------
1374 DECODE(C_RECEIPTS_REC.SERVICE_FLAG, 1, 1,
1375 (poll.price_override * rt.source_doc_quantity / rt.primary_quantity))
1376 -----------------------------------------------------------------------------------------
1377 INTO
1378 l_po_price
1379 FROM
1380 rcv_transactions rt,
1381 po_line_locations_all poll
1382 WHERE
1383 rt.transaction_id = i_ae_txn_rec.transaction_id
1384 AND rt.po_line_location_id = poll.line_location_id;
1385
1386
1387
1388 --------------------------------------------------------------------
1389 -- For Expense destinations, both stage 1 and stage 2 transactions are processed
1390 -- For Inventory and Shop floor, only stage 1 transactions are processed
1391 -- the stage 2 transactions are populated in the MMT and WT tables
1392 -- and are processed by the INV and WIP processor
1393 --------------------------------------------------------------------
1394
1395 --IF (c_receipts_rec.destination_type_code <> 'EXPENSE') THEN
1396
1397 IF (l_stage = 1) THEN
1398 IF (nvl(i_ae_txn_rec.lcm_flag,'N') = 'Y') THEN
1399 /*LCM PO
1400 -- If net action is receive, the following accounting
1401 -- needs to be generated
1402 -- Dr. Receiving Inspection qty@Acq_cost
1403 -- Cr. Accrual qty@po
1404 -- Dr./Cr. Landed Cost Absorption
1405 -- If net action is returns, the following accounting
1406 -- needs to be generated
1407 -- Dr. Accrual qty@po
1408 -- Cr. Receiving Inspection qty@acq_cost
1409 -- Dr./Cr. Landed Cost Absorption
1410 */
1411 l_curr_rec.currency_conv_rate := c_receipts_rec.exchg_rate;
1412 l_curr_rec.currency_conv_date := c_receipts_rec.exchg_date;
1413 l_curr_rec.currency_conv_type := i_ae_txn_rec.currency_conv_type;
1414
1415 IF (l_net_receipt = 1) THEN
1416 l_dr_flag := FALSE;
1417 ELSE
1418 l_dr_flag := TRUE;
1419 END IF;
1420
1421 l_ae_line_rec.ae_line_type := 16;
1422 l_ae_line_rec.account := c_receipts_rec.accrual_account_id;
1423 l_ae_line_rec.transaction_value := c_receipts_rec.dist_quantity * (l_po_price + c_receipts_rec.tax);
1424 /* Accrual entries need to be rounded in receiving currency
1425 first to match the invoicing logic.
1426 */
1427 l_stmt_num := 1000;
1428 SELECT decode(c2.minimum_accountable_unit,
1429 NULL,round(l_ae_line_rec.transaction_value,c2.precision),
1430 round(l_ae_line_rec.transaction_value/c2.minimum_accountable_unit)
1431 * c2.minimum_accountable_unit )
1432 INTO l_ae_line_rec.transaction_value
1433 FROM fnd_currencies c2
1434 WHERE c2.currency_code = decode(l_curr_rec.alt_currency, NULL,
1435 l_curr_rec.pri_currency,
1436 l_curr_rec.alt_currency);
1437
1438
1439
1440 IF l_debug_flag = 'Y' THEN
1441 IF (l_dr_flag) THEN
1442 fnd_file.put_line(fnd_file.log,'DR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1443 ELSE
1444 fnd_file.put_line(fnd_file.log,'CR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1445 END IF;
1446 END IF;
1447
1448 IF ( c_receipts_rec.dist_quantity = 0 ) THEN
1449 l_ae_line_rec.rate_or_amount := 0;
1450 ELSE
1451 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity ;
1452 END IF;
1453
1454 l_stmt_num := 1010;
1455 CSTPAPPR.insert_account (i_ae_txn_rec,
1456 l_curr_rec,
1457 l_dr_flag,
1458 l_ae_line_rec,
1459 l_ae_line_tbl,
1460 l_err_rec);
1461 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) then
1462 raise process_error;
1463 END IF;
1464
1465 l_dr_flag := not l_dr_flag;
1466 l_stmt_num := 1020;
1467 SELECT nvl(max(craca.period_id),-1)
1468 INTO l_lcm_adj_period
1469 FROM cst_rcv_acq_costs_adj craca
1470 WHERE craca.rcv_transaction_id = l_par_rcv_txn
1471 AND craca.cost_type_id = i_ae_txn_rec.cost_type_id
1472 AND craca.cost_group_id = i_ae_txn_rec.cost_group_id;
1473
1474 IF (l_lcm_adj_period <> -1) THEN
1475 l_stmt_num := 1030;
1476 SELECT craca.acquisition_cost
1477 INTO l_acq_cost
1478 FROM cst_rcv_acq_costs_adj craca
1479 WHERE craca.rcv_transaction_id = l_par_rcv_txn
1480 AND craca.cost_type_id = i_ae_txn_rec.cost_type_id
1481 AND craca.cost_group_id = i_ae_txn_rec.cost_group_id
1482 AND craca.period_id = l_lcm_adj_period;
1483
1484 END IF;
1485 l_acq_cost_ent := l_acq_cost/c_receipts_rec.exchg_rate;
1486 l_ae_line_rec.account := c_receipts_rec.receiving_account_id;
1487 l_ae_line_rec.ae_line_type := 5;
1488 l_ae_line_rec.transaction_value := c_receipts_rec.dist_quantity * l_acq_cost_ent;
1489 l_stmt_num := 1040;
1490 SELECT decode(c2.minimum_accountable_unit,
1491 NULL,round(l_ae_line_rec.transaction_value,c2.precision),
1492 round(l_ae_line_rec.transaction_value/c2.minimum_accountable_unit)
1493 * c2.minimum_accountable_unit )
1494 INTO l_ae_line_rec.transaction_value
1495 FROM fnd_currencies c2
1496 WHERE c2.currency_code = decode(l_curr_rec.alt_currency, NULL,
1497 l_curr_rec.pri_currency,
1498 l_curr_rec.alt_currency);
1499
1500 IF l_debug_flag = 'Y' THEN
1501 IF (l_dr_flag) THEN
1502 fnd_file.put_line(fnd_file.log,'DR RI: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1503 ELSE
1504 fnd_file.put_line(fnd_file.log,'CR RI: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1505 END IF;
1506 END IF;
1507
1508 IF ( c_receipts_rec.dist_quantity = 0 ) THEN
1509 l_ae_line_rec.rate_or_amount := 0;
1510 ELSE
1511 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity ;
1512 END IF;
1513 l_stmt_num := 1050;
1514 CSTPAPPR.insert_account (i_ae_txn_rec,
1515 l_curr_rec,
1516 l_dr_flag,
1517 l_ae_line_rec,
1518 l_ae_line_tbl,
1519 l_err_rec);
1520 if (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) then
1521 raise process_error;
1522 end if;
1523
1524 IF ( l_acq_cost_ent<>(l_po_price + c_receipts_rec.tax)) THEN
1525 IF ((l_po_price + c_receipts_rec.tax)>l_acq_cost_ent) THEN
1526 IF (l_net_receipt = 1) THEN
1527 l_dr_flag := TRUE;
1528 ELSE
1529 l_dr_flag := FALSE;
1530 END IF;
1531 ELSE
1532 IF (l_net_receipt = 1) THEN
1533 l_dr_flag := FALSE;
1534 ELSE
1535 l_dr_flag := TRUE;
1536 END IF;
1537 END IF;
1538 l_ae_line_rec.account := l_landed_cost_abs_account;
1539 l_ae_line_rec.ae_line_type := 38;
1540 l_ae_line_rec.transaction_value := abs(c_receipts_rec.dist_quantity *
1541 ((l_po_price + c_receipts_rec.tax)-l_acq_cost_ent)) ;
1542 l_stmt_num := 1060;
1543 SELECT decode(c2.minimum_accountable_unit,
1544 NULL,round(l_ae_line_rec.transaction_value,c2.precision),
1545 round(l_ae_line_rec.transaction_value/c2.minimum_accountable_unit)
1546 * c2.minimum_accountable_unit )
1547 INTO l_ae_line_rec.transaction_value
1548 FROM fnd_currencies c2
1549 WHERE c2.currency_code = decode(l_curr_rec.alt_currency, NULL,
1550 l_curr_rec.pri_currency,
1551 l_curr_rec.alt_currency);
1552
1553 IF l_debug_flag = 'Y' THEN
1554 IF (l_dr_flag) THEN
1555 fnd_file.put_line(fnd_file.log,'DR Landed Cost Abs: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1556 ELSE
1557 fnd_file.put_line(fnd_file.log,'CR Landed Cost Abs: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1558 END IF;
1559 END IF;
1560
1561 IF ( c_receipts_rec.dist_quantity = 0 ) THEN
1562 l_ae_line_rec.rate_or_amount := 0;
1563 ELSE
1564 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity ;
1565 END IF;
1566 l_stmt_num := 1070;
1567 CSTPAPPR.insert_account (i_ae_txn_rec,
1568 l_curr_rec,
1569 l_dr_flag,
1570 l_ae_line_rec,
1571 l_ae_line_tbl,
1572 l_err_rec);
1573
1574 if (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) then
1575 raise process_error;
1576 end if;
1577
1578 END IF;
1579
1580 ELSE
1581 ----------------------------------------------------------
1582 -- If net action is receive, the following accounting needs to be generated
1583 -- Dr. Receiving Inspection qty@Acq_cost
1584 -- Cr. Accrual qty@po
1585 -- Dr./Cr. IPV
1586 -- Dr./Cr. Special charges
1587 -- Dr./Cr. ERV
1588 -- If net action is returns, the following accounting needs to be generated
1589 -- Dr. Accrual qty@po
1590 -- Cr. Receiving Inspection qty@acq_cost
1591 -- Dr./Cr. IPV
1592 -- Dr./Cr. Special charges
1593 -- Dr./Cr. ERV
1594 --
1595 -- Example :
1596 --
1597 -- PO Shipment qty = 100 po price $10 each
1598 -- PO Distributions :
1599 -- 40 tax = $40 p.u.tax = 40/40=1
1600 -- 60 tax = $30 p.u.tax = 30/60=0.5
1601 -- Receive 50 against shipment
1602 -- Invoice 10@12 against Receipt
1603 -- + Tax $10 p.u.tax=10/10=1
1604 -- + Freight $40
1605 -- + Tax on Freight $2
1606 -- POD #1
1607 -- Dr. Accrual 0.4*10*(10+1)=44
1608 -- Cr. Liability 0.4*(120+10+40+2)=68.8
1609 -- Dr. IPV 0.4*10*(12-10) = 8
1610 -- Dr. Tax IPV 0.4*10*(1-1)=0
1611 -- Dr. Freight Expense 0.4*40=16
1612 -- Dr. Tax Expense 0.4*2=0.8
1613 -- POD #2
1614 -- Dr. Accrual 0.6*10*(10+0.5)=63
1615 -- Cr. Liability 0.6*(120+10+40+5)=103.2
1616 -- Dr. IPV 0.6*10*(12-10) = 12
1617 -- Dr. Tax IPV 0.6*10*(1-0.5)=3
1618 -- Dr. Freight Expense 0.6*40=24
1619 -- Dr. Tax Expense 0.6*2=1.2
1620 -- Acquisition Cost = (10*12 + 10 + 40 + 2 + 40*11*0.4 + 40*10.5*0.6)/50 =
1621 -- 12
1622 --
1623 -- For each distribution :
1624 -- POD #1 :
1625 -- Dr. RI 50*40/100*12 = 240
1626 -- Cr. Accrual 50*40/100*(10 + 1) = 220
1627 -- Cr. IPV (8/50)*50=8
1628 -- Cr. Tax IPV (0/50)*50=0
1629 -- Cr. Freight Expense (16/50)*50=16
1630 -- Cr. Tax Expense (0.8/50)*50=0.8
1631 -- POD #2 :
1632 -- Dr. RI 50*60/100*12 = 360
1633 -- Cr. Accrual 50*60/100*(10 + 0.5) = 315
1634 -- Cr. IPV (12/50)*50*=12
1635 -- Cr. Tax IPV (3/50)*50=3
1636 -- Cr. Freight Expense (24/50)*50=24
1637 -- Cr. Tax Expense (1.2/50)*50=1.2
1638 -- Total Debits : 600
1639 -- Total Credits : 600
1640 ----------------------------------------------------------
1641
1642 IF (l_net_receipt = 1) THEN
1643 l_dr_flag := TRUE;
1644 ELSE
1645 l_dr_flag := FALSE;
1646 END IF;
1647
1648 l_ae_line_rec.account := c_receipts_rec.receiving_account_id;
1649 if (l_dropship_type_code = 2) then
1650 l_ae_line_rec.ae_line_type := 31;
1651 else
1652 l_ae_line_rec.ae_line_type := 5;
1653 end if;
1654 l_ae_line_rec.transaction_value := c_receipts_rec.dist_quantity * l_acq_cost_ent;
1655
1656 IF (l_acq_cost_ent = 0) THEN
1657 l_curr_rec.currency_conv_rate := 1;
1658 ELSE
1659 l_curr_rec.currency_conv_rate := l_acq_cost/l_acq_cost_ent;
1660 END IF;
1661
1662 l_curr_rec.currency_conv_date := null;
1663 l_curr_rec.currency_conv_type := null;
1664 --acq cost is a combination of po and invoice rate and is in functional currency
1665
1666 IF l_debug_flag = 'Y' THEN
1667 IF (l_dr_flag) THEN
1668 fnd_file.put_line(fnd_file.log,'DR RI: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1669 ELSE
1670 fnd_file.put_line(fnd_file.log,'CR RI: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1671 END IF;
1672 END IF;
1673
1674 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
1675 not the primary quantity on the transaction */
1676 IF ( c_receipts_rec.dist_quantity = 0 ) THEN
1677 l_ae_line_rec.rate_or_amount := 0;
1678 ELSE
1679 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity ;
1680 END IF;
1681
1682 CSTPAPPR.insert_account (i_ae_txn_rec,
1683 l_curr_rec,
1684 l_dr_flag,
1685 l_ae_line_rec,
1686 l_ae_line_tbl,
1687 l_err_rec);
1688 if (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) then
1689 raise process_error;
1690 end if;
1691
1692 -------------------------------------------
1693 -- Toggle the debit flag
1694 -------------------------------------------
1695
1696 l_dr_flag := not l_dr_flag;
1697
1698 l_stmt_num := 70;
1699
1700 /* Bug 3421141: Drop Shipment and Global Procureemnt Changes */
1701
1702 /* For Global Procurement receipts a credit needs to be made to the
1703 I/C Accrual account, rather than the supplier accrual account.
1704
1705 Instead of determining if the current transaction is in the
1706 context of global procureemnt by figuring out the PO OU and the
1707 receiving OU, I'm getting the account information from RAE, which
1708 will always store the correct account */
1709
1710 begin
1711 select credit_account_id, debit_account_id
1712 into l_credit_Account, l_debit_account
1713 from rcv_accounting_events
1714 where rcv_transaction_id = i_ae_txn_rec.transaction_id
1715 and organization_id = i_ae_txn_rec.organization_id
1716 and rownum = 1;
1717
1718 if(l_dr_flag) then
1719 l_ae_line_rec.account := l_debit_account;
1720 else
1721 l_ae_line_rec.account := l_credit_account;
1722 end if;
1723
1724 exception
1725 when others then
1726 l_ae_line_rec.account := c_receipts_rec.accrual_account_id;
1727 end;
1728
1729 l_stmt_num := 750;
1730
1731
1732 /* Bug 5555888 : For Global Procurement scenario (PO OU is diff from Rcv OU), the Accrual
1733 should also be hit at the acq cost (PO price or Transfer price as per setup). Also,
1734 in this case the tax/Invoice/IPV etc is also not there as acq price is binding price.
1735 For Normal receits do it at the PO price as before. */
1736
1737 select org_id
1738 into l_po_ou_id /* Get the OU where PO is created */
1739 from po_headers_all
1740 where po_header_id = (select po_header_id
1741 from rcv_transactions
1742 where transaction_id = i_ae_txn_rec.transaction_id);
1743
1744 l_stmt_num := 751;
1745
1746 select to_number(org_information3)
1747 into l_rcv_ou_id /* Get OU where Receiving is done */
1748 from hr_organization_information
1749 where org_information_context = 'Accounting Information'
1750 and organization_id = i_ae_txn_rec.organization_id;
1751
1752 l_stmt_num := 752;
1753
1754 IF l_debug_flag = 'Y' THEN
1755 fnd_file.put_line(fnd_file.log,'PO OU / Rcv OU '|| l_po_ou_id ||' / '|| l_rcv_ou_id);
1756 END IF;
1757
1758 l_stmt_num := 753;
1759
1760 l_ae_line_rec.ae_line_type := 16;
1761
1762 IF (l_po_ou_id <> l_rcv_ou_id) THEN /* Global Procurement Scenario */
1763
1764 l_stmt_num := 754;
1765
1766 IF l_debug_flag = 'Y' THEN
1767 fnd_file.put_line(fnd_file.log,'GP scenario. Trxn val: '||to_char(c_receipts_rec.dist_quantity * l_acq_cost_ent));
1768 END IF;
1769
1770 l_ae_line_rec.transaction_value := c_receipts_rec.dist_quantity * l_acq_cost_ent;
1771
1772 IF (l_acq_cost_ent = 0) THEN
1773 l_curr_rec.currency_conv_rate := 1;
1774 ELSE
1775 l_curr_rec.currency_conv_rate := l_acq_cost/l_acq_cost_ent;
1776 END IF;
1777
1778 l_curr_rec.currency_conv_date := null;
1779 l_curr_rec.currency_conv_type := null;
1780 --acq cost is a combination of po and invoice rate and is in functional currency
1781
1782 ELSE
1783
1784 l_stmt_num := 755;
1785
1786 IF l_debug_flag = 'Y' THEN
1787 fnd_file.put_line(fnd_file.log,'Normal Receipt. Trxn val: '||to_char(c_receipts_rec.dist_quantity * (l_po_price + c_receipts_rec.tax)));
1788 END IF;
1789
1790 l_ae_line_rec.transaction_value := c_receipts_rec.dist_quantity * (l_po_price + c_receipts_rec.tax);
1791
1792 l_curr_rec.currency_conv_rate := c_receipts_rec.exchg_rate;
1793 l_curr_rec.currency_conv_date := c_receipts_rec.exchg_date;
1794 l_curr_rec.currency_conv_type := i_ae_txn_rec.currency_conv_type;
1795
1796 END IF; /* (l_po_ou_id <> l_rcv_ou_id) */
1797
1798 l_stmt_num := 756;
1799
1800 IF l_debug_flag = 'Y' THEN
1801 IF (l_dr_flag) THEN
1802 fnd_file.put_line(fnd_file.log,'DR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1803 ELSE
1804 fnd_file.put_line(fnd_file.log,'CR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1805 END IF;
1806 END IF;
1807
1808 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
1809 not the primary quantity on the transaction */
1810 IF ( c_receipts_rec.dist_quantity = 0 ) THEN
1811 l_ae_line_rec.rate_or_amount := 0;
1812 ELSE
1813 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity ;
1814 END IF;
1815
1816 l_stmt_num := 757;
1817
1818 CSTPAPPR.insert_account (i_ae_txn_rec,
1819 l_curr_rec,
1820 l_dr_flag,
1821 l_ae_line_rec,
1822 l_ae_line_tbl,
1823 l_err_rec);
1824 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) then
1825 raise process_error;
1826 END IF;
1827
1828 l_stmt_num := 758;
1829
1830 ------------------------------------------------------
1831 -- All the invoice distribution lines of type TAX/FREIGHT/MISCELLANEOUS
1832 -- that do not have IPV should be charged to the Special charge account
1833 -- that is specified at the time of invoice creation
1834 ---------------------------------------------------------------
1835
1836 -------------------------------------------------
1837 -- Joining with ap_invoices_all in order to get the
1838 -- exchange rate. Also, the code needs to check for
1839 -- more than just ITEM when looking at the lookup code
1840 -- This is a result of AP's Invoice Lines Project
1841 -------------------------------------------------
1842
1843
1844
1845 /*-----------------------------------------------------------------+
1846 | BUG 9113487 |
1847 | In R12,final account used for Exchange Rate Variance, |
1848 | Invoice Price Variance and other charges is derived using |
1849 | Account derivation Rule defined in SLA.Hence if SLA data exists |
1850 | for invoice accounting derive account from SLA Accounting tables |
1851 | else derive it from AP tables. |
1852 +-----------------------------------------------------------------*/
1853
1854 DECLARE
1855
1856
1857 CURSOR xla_charges is
1858 SELECT
1859 cracd.amount/nvl(aia.exchange_rate,1) "AMOUNT",
1860 nvl(aia.exchange_rate,1) "EXCHANGE_RATE",
1861 aia.exchange_date "EXCHANGE_DATE",
1862 aia.exchange_rate_type "EXCHANGE_RATE_TYPE",
1863 xal.code_combination_id "CODE_COMBINATION_ID"
1864 FROM
1865 cst_rcv_acq_cost_details cracd,
1866 cst_rcv_acq_costs crac,
1867 xla_distribution_links xdl,
1868 xla_ae_lines xal,
1869 xla_ae_headers xae,
1870 ap_invoice_distributions_all aida ,
1871 ap_invoices_all aia
1872 WHERE
1873 cracd.line_type NOT IN ('ITEM','ACCRUAL','IPV','ERV','NONREC_TAX') AND
1874 crac.rcv_transaction_id = l_par_rcv_txn AND
1875 crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
1876 crac.cost_group_id = i_ae_txn_rec.cost_group_id AND
1877 cracd.header_id = crac.header_id AND
1878 cracd.source_type = 'INVOICE' AND
1879 cracd.invoice_distribution_id = aida.invoice_distribution_id
1880 AND xae.event_id=aida.accounting_event_id
1881 AND xae.ae_header_id=xdl.ae_header_id
1882 AND xae.ae_header_id=xal.ae_header_id
1883 AND xae.accounting_entry_status_code='F'
1884 AND xae.application_id=200
1885 AND xae.ledger_id=l_ledger_id
1886 AND xal.ae_header_id=xdl.ae_header_id
1887 AND xal.application_id=200
1888 AND xal.ledger_id=l_ledger_id
1889 AND xdl.application_id=200
1890 AND xdl.SOURCE_DISTRIBUTION_TYPE='AP_INV_DIST'
1891 AND xdl.ae_line_num=xal.ae_line_num
1892 AND xdl.source_distribution_id_num_1=aida.invoice_distribution_id
1893 AND xal.accounting_class_code not in ('LIABILITY','ITEM','ACCRUAL',
1894 'IPV','EXCHANGE_RATE_VARIANCE' ,'NONRECOVERABLE TAX')
1895 AND aia.invoice_id = aida.invoice_id
1896 AND aia.org_id = aida.org_id
1897 AND NOT EXISTS (
1898 SELECT '1' FROM ap_invoice_distributions_all aida2
1899 WHERE aida2.related_id = aida.invoice_distribution_id
1900 AND aida2.line_type_lookup_code = 'IPV');
1901
1902
1903 CURSOR c_charges IS
1904 SELECT
1905 cracd.amount/nvl(aia.exchange_rate,1) "AMOUNT",
1906 nvl(aia.exchange_rate,1) "EXCHANGE_RATE",
1907 aia.exchange_date "EXCHANGE_DATE",
1908 aia.exchange_rate_type "EXCHANGE_RATE_TYPE",
1909 aida.dist_code_combination_id "CODE_COMBINATION_ID"
1910 FROM
1911 cst_rcv_acq_cost_details cracd,
1912 cst_rcv_acq_costs crac,
1913 ap_invoice_distributions_all aida,
1914 ap_invoices_all aia
1915 WHERE
1916 cracd.line_type NOT IN ('ITEM','ACCRUAL','IPV','ERV','NONREC_TAX') AND
1917 crac.rcv_transaction_id = l_par_rcv_txn AND
1918 crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
1919 crac.cost_group_id = i_ae_txn_rec.cost_group_id AND
1920 cracd.header_id = crac.header_id AND
1921 cracd.source_type = 'INVOICE' AND
1922 cracd.invoice_distribution_id = aida.invoice_distribution_id AND
1923 aia.invoice_id = aida.invoice_id AND
1924 aia.org_id = aida.org_id AND
1925 NOT EXISTS (
1926 SELECT '1' FROM ap_invoice_distributions_all aida2
1927 WHERE aida2.related_id = aida.invoice_distribution_id
1928 AND aida2.line_type_lookup_code = 'IPV'
1929 );
1930
1931
1932
1933 xla_count NUMBER:=0;
1934 c_chg_rec c_charges%ROWTYPE;
1935
1936
1937 BEGIN
1938
1939 OPEN xla_charges;
1940 FETCH xla_charges INTO c_chg_rec;
1941
1942 IF(xla_charges%FOUND) THEN
1943 xla_count:=1;
1944 ELSE
1945 xla_count:=0;
1946 END IF;
1947
1948 CLOSE xla_charges;
1949
1950 IF(xla_count=1) THEN
1951 OPEN xla_charges;
1952 ELSE
1953 OPEN c_charges;
1954 END IF ;
1955
1956 LOOP
1957
1958 IF (xla_count=1) THEN
1959 FETCH xla_charges INTO c_chg_rec;
1960 EXIT WHEN xla_charges%notfound;
1961 ELSE
1962 FETCH c_charges INTO c_chg_rec;
1963 EXIT WHEN c_charges%notfound;
1964 END IF;
1965
1966 IF (l_net_receipt = 1 and c_chg_rec.amount > 0) OR (l_net_receipt = -1 and c_chg_rec.amount < 0) THEN
1967 l_dr_flag := FALSE;
1968 ELSE
1969 l_dr_flag := TRUE;
1970 END IF;
1971
1972 l_ae_line_rec.account := c_chg_rec.code_combination_id;
1973 l_ae_line_rec.ae_line_type := 19;
1974 --l_ae_line_rec.transaction_value := c_chg_rec.amount * (c_receipts_rec.dist_quantity / l_costed_quantity);
1975 l_ae_line_rec.transaction_value := c_chg_rec.amount * (i_ae_txn_rec.primary_quantity / l_costed_quantity);
1976
1977 -- also populate type date etc
1978 l_curr_rec.currency_conv_rate := c_chg_rec.exchange_rate;
1979 l_curr_rec.currency_conv_date := c_chg_rec.exchange_date;
1980 l_curr_rec.currency_conv_type := c_chg_rec.exchange_rate_type;
1981
1982 IF l_debug_flag = 'Y' THEN
1983 IF (l_dr_flag) THEN
1984 fnd_file.put_line(fnd_file.log,'DR Special Charges: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1985 ELSE
1986 fnd_file.put_line(fnd_file.log,'CR Special Charges: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1987 END IF;
1988 END IF;
1989
1990 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
1991 not the primary quantity on the transaction */
1992 IF (i_ae_txn_rec.primary_quantity = 0) THEN
1993 l_ae_line_rec.rate_or_amount := 0;
1994 ELSE
1995 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / i_ae_txn_rec.primary_quantity;
1996 END IF;
1997
1998 CSTPAPPR.insert_account (i_ae_txn_rec,
1999 l_curr_rec,
2000 l_dr_flag,
2001 l_ae_line_rec,
2002 l_ae_line_tbl,
2003 l_err_rec);
2004 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) THEN
2005 raise process_error;
2006 END IF;
2007
2008 END LOOP; /* c_charges loop */
2009 EXCEPTION
2010 when process_error then
2011 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
2012 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
2013 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
2014
2015 IF(xla_count=1) THEN
2016 CLOSE xla_charges;
2017 ELSE
2018 CLOSE c_charges;
2019 END IF ;
2020
2021
2022 END;
2023
2024 ---------------------------------------------------------------
2025 -- The invoice distribution lines that have IPV
2026 ---------------------------------------------------------------
2027
2028 ---------------------------------------------------------------
2029 -- Invoice Lines Project
2030 -- {base_}invoice_price_variance will be obsolete in 11.5.11
2031 -- also. Instead a separate distribution will be created for IPV
2032 -- Consult Invoice lines documentation (AP and/or Costing) for
2033 -- more information. In addition exchange rate needs to
2034 -- come from ap_invoice_all
2035 ---------------------------------------------------------------
2036
2037 DECLARE
2038
2039 CURSOR xla_ipv is
2040 SELECT aida.base_amount/nvl(aia.exchange_rate,1) "INVOICE_PRICE_VARIANCE",
2041 nvl(aia.exchange_rate,1) "EXCHANGE_RATE",
2042 aia.exchange_date "EXCHANGE_DATE",
2043 aia.exchange_rate_type "EXCHANGE_RATE_TYPE",
2044 xal.code_combination_id "CODE_COMBINATION_ID",
2045 aida.related_id "INVOICE_DISTRIBUTION_ID"
2046 FROM
2047 cst_rcv_acq_cost_details cracd,
2048 cst_rcv_acq_costs crac,
2049 xla_distribution_links xdl,
2050 xla_ae_lines xal,
2051 xla_ae_headers xae,
2052 ap_invoice_distributions_all aida,
2053 ap_invoices_all aia
2054 WHERE
2055 crac.rcv_transaction_id = l_par_rcv_txn AND
2056 crac.cost_type_id =i_ae_txn_rec.cost_type_id AND
2057 crac.cost_group_id = i_ae_txn_rec.cost_group_id AND
2058 cracd.header_id = crac.header_id AND
2059 cracd.source_type = 'INVOICE' AND
2060 cracd.invoice_distribution_id = nvl(aida.related_id,aida.CORRECTED_INVOICE_DIST_ID)
2061 AND xae.event_id=aida.accounting_event_id
2062 AND xae.ae_header_id=xdl.ae_header_id
2063 AND xae.ae_header_id=xal.ae_header_id
2064 AND xae.accounting_entry_status_code='F'
2065 AND xae.application_id=200
2066 AND xae.ledger_id=l_ledger_id
2067 AND xal.ae_header_id=xdl.ae_header_id
2068 AND xal.application_id=200
2069 AND xal.ledger_id=l_ledger_id
2070 AND xal.accounting_class_code ='IPV'
2071 AND xdl.application_id=200
2072 AND xdl.SOURCE_DISTRIBUTION_TYPE='AP_INV_DIST'
2073 AND xdl.source_distribution_id_num_1=aida.invoice_distribution_id
2074 AND xdl.ae_line_num=xal.ae_line_num
2075 AND aia.invoice_id = aida.invoice_id
2076 AND aia.org_id = aida.org_id
2077 AND aida.line_type_lookup_code = 'IPV'
2078 AND aida.amount <> 0
2079 AND aida.posted_flag = 'Y';
2080
2081 CURSOR c_ipv IS
2082 SELECT
2083 aida.base_amount/nvl(aia.exchange_rate,1) "INVOICE_PRICE_VARIANCE",
2084 nvl(aia.exchange_rate,1) "EXCHANGE_RATE",
2085 aia.exchange_date "EXCHANGE_DATE",
2086 aia.exchange_rate_type "EXCHANGE_RATE_TYPE",
2087 aida.dist_code_combination_id "CODE_COMBINATION_ID",
2088 -- Retroactive Pricing Enhancements
2089 -- Need Invoice Distribution to find correction invoices
2090 aida.related_id "INVOICE_DISTRIBUTION_ID"
2091 FROM
2092 cst_rcv_acq_cost_details cracd,
2093 cst_rcv_acq_costs crac,
2094 ap_invoice_distributions_all aida,
2095 ap_invoices_all aia
2096 WHERE
2097 crac.rcv_transaction_id = l_par_rcv_txn AND
2098 crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
2099 crac.cost_group_id = i_ae_txn_rec.cost_group_id AND
2100 cracd.header_id = crac.header_id AND
2101 cracd.source_type = 'INVOICE' AND
2102 cracd.invoice_distribution_id = nvl(aida.related_id,aida.CORRECTED_INVOICE_DIST_ID) AND
2103 aia.invoice_id = aida.invoice_id AND
2104 aia.org_id = aida.org_id AND
2105 aida.line_type_lookup_code = 'IPV' AND
2106 aida.amount <> 0 AND
2107 aida.posted_flag = 'Y'; --Added for bug 4773085
2108
2109
2110 xla_count NUMBER:=0;
2111 c_ipv_rec c_ipv%ROWTYPE;
2112
2113
2114 BEGIN
2115
2116 OPEN xla_ipv;
2117 FETCH xla_ipv INTO c_ipv_rec;
2118
2119 IF(xla_ipv%FOUND) THEN
2120 xla_count:=1;
2121 ELSE
2122 xla_count:=0;
2123 END IF;
2124
2125 CLOSE xla_ipv;
2126
2127 IF(xla_count=1) THEN
2128 OPEN xla_ipv;
2129 ELSE
2130 OPEN c_ipv;
2131 END IF ;
2132
2133 LOOP
2134
2135 IF (xla_count=1) THEN
2136 FETCH xla_ipv INTO c_ipv_rec;
2137 EXIT WHEN xla_ipv%notfound;
2138 ELSE
2139 FETCH c_ipv INTO c_ipv_rec;
2140 EXIT WHEN c_ipv%notfound;
2141 END IF;
2142
2143
2144 IF (l_net_receipt = 1 and c_ipv_rec.invoice_price_variance > 0) OR (l_net_receipt = -1 and c_ipv_rec.invoice_price_variance < 0) THEN
2145 l_dr_flag := FALSE;
2146 ELSE
2147 l_dr_flag := TRUE;
2148 END IF;
2149
2150 -- IPV account stamped on the Price correction Invoices is the same as the
2151 -- one on the original Invoice
2152 -- Get the sum of IPV amounts on the price correction invoices and add them to
2153 -- the IPV on the original invoice
2154 -- If there is a Price Correction Invoice, the IPV reverses and the net
2155 -- IPV SHOULD be 0.
2156
2157 -- As a result of Invoice Lines Project, IPV is a separate distribution
2158 -- and retropricing is handled through corrected_invoice_dist_id column
2159 l_stmt_num := 71;
2160 BEGIN
2161 SELECT
2162 NVL(AIDA.BASE_AMOUNT/NVL(AP_INV.EXCHANGE_RATE,1), 0)
2163 INTO
2164 l_correct_ipv_amount
2165 FROM
2166 AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
2167 AP_INVOICES_ALL AP_INV
2168 WHERE
2169 AIDA.CORRECTED_INVOICE_DIST_ID = c_ipv_rec.INVOICE_DISTRIBUTION_ID
2170 AND AIDA.LINE_TYPE_LOOKUP_CODE = 'IPV'
2171 AND AIDA.INVOICE_ID = AP_INV.INVOICE_ID
2172 AND AP_INV.INVOICE_TYPE_LOOKUP_CODE = 'PO PRICE ADJUST';
2173 EXCEPTION
2174 WHEN OTHERS THEN
2175 l_correct_ipv_amount := 0;
2176 END;
2177
2178 IF l_debug_flag = 'Y' THEN
2179 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Original IPV: '||to_char(c_ipv_rec.invoice_price_variance));
2180 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IPV - Price Correction Invoice: '||to_char(l_correct_ipv_amount));
2181 END IF;
2182
2183 l_total_ipv := c_ipv_rec.invoice_price_variance + l_correct_ipv_amount;
2184
2185 -- Create IPV line only if there is a net IPV
2186
2187 IF l_total_ipv <> 0 THEN
2188
2189 l_ae_line_rec.account := c_ipv_rec.code_combination_id;
2190 l_ae_line_rec.ae_line_type := 17;
2191 l_ae_line_rec.transaction_value := c_ipv_rec.invoice_price_variance * (i_ae_txn_rec.primary_quantity / l_costed_quantity);
2192
2193 l_curr_rec.currency_conv_rate := c_ipv_rec.exchange_rate;
2194 l_curr_rec.currency_conv_date := c_ipv_rec.exchange_date;
2195 l_curr_rec.currency_conv_type := c_ipv_rec.exchange_rate_type;
2196
2197 IF l_debug_flag = 'Y' THEN
2198 IF (l_dr_flag) THEN
2199 fnd_file.put_line(fnd_file.log,'DR IPV: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2200 ELSE
2201 fnd_file.put_line(fnd_file.log,'CR IPV: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2202 END IF;
2203 END IF;
2204
2205 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
2206 not the primary quantity on the transaction */
2207 IF (i_ae_txn_rec.primary_quantity = 0) THEN
2208 l_ae_line_rec.rate_or_amount := 0;
2209 ELSE
2210 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / i_ae_txn_rec.primary_quantity;
2211 END IF;
2212
2213 CSTPAPPR.insert_account (i_ae_txn_rec,
2214 l_curr_rec,
2215 l_dr_flag,
2216 l_ae_line_rec,
2217 l_ae_line_tbl,
2218 l_err_rec);
2219 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) THEN
2220 raise process_error;
2221 END IF;
2222
2223 END IF; -- l_total_ipv <> 0
2224
2225 END LOOP;
2226 EXCEPTION
2227 when process_error then
2228 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
2229 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
2230 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
2231
2232 IF(xla_count=1) THEN
2233 CLOSE xla_ipv;
2234 ELSE
2235 CLOSE c_ipv;
2236 END IF ;
2237
2238
2239 END;
2240
2241
2242 ----------------------------------------------------------------
2243 -- The invoice distribution lines that have ERV
2244 -- For the invoice lines project, ERV columns are obsolete and
2245 -- are separte distributions. This is previously described in a
2246 -- similar situation involving IPV as well as AP's documentation
2247 ----------------------------------------------------------------
2248
2249 DECLARE
2250
2251 CURSOR xla_erv is
2252 select
2253 aida.base_amount "EXCHANGE_RATE_VARIANCE",
2254 xal.code_combination_id "CODE_COMBINATION_ID"
2255 from
2256 xla_distribution_links xdl,
2257 xla_ae_lines xal,
2258 xla_ae_headers xae,
2259 cst_rcv_acq_cost_details cracd,
2260 cst_rcv_acq_costs crac,
2261 ap_invoice_distributions_all aida
2262 where crac.rcv_transaction_id =l_par_rcv_txn
2263 AND crac.cost_type_id=i_ae_txn_rec.cost_type_id
2264 AND crac.cost_group_id=i_ae_txn_rec.cost_group_id
2265 AND cracd.header_id = crac.header_id
2266 AND cracd.source_type = 'INVOICE'
2267 AND cracd.invoice_distribution_id =aida.related_id
2268 AND xae.event_id=aida.accounting_event_id
2269 AND xae.ae_header_id=xdl.ae_header_id
2270 AND xae.ae_header_id=xal.ae_header_id
2271 AND xae.accounting_entry_status_code='F'
2272 AND xae.application_id=200
2273 AND xae.ledger_id=l_ledger_id
2274 AND xal.ae_header_id=xdl.ae_header_id
2275 AND xal.application_id=200
2276 AND xal.ledger_id=l_ledger_id
2277 AND xal.accounting_class_code ='EXCHANGE_RATE_VARIANCE'
2278 AND xdl.application_id=200
2279 AND xdl.SOURCE_DISTRIBUTION_TYPE='AP_INV_DIST'
2280 AND xdl.ae_line_num=xal.ae_line_num
2281 AND xdl.source_distribution_id_num_1=aida.invoice_distribution_id
2282 AND aida.line_type_lookup_code = 'ERV'
2283 AND aida.posted_flag = 'Y'
2284 AND aida.base_amount<>0 ;
2285
2286 CURSOR c_erv IS
2287 SELECT
2288 aida.base_amount "EXCHANGE_RATE_VARIANCE",
2289 aida.dist_code_combination_id "CODE_COMBINATION_ID"
2290 FROM
2291 cst_rcv_acq_cost_details cracd,
2292 cst_rcv_acq_costs crac,
2293 ap_invoice_distributions_all aida
2294 WHERE
2295 crac.rcv_transaction_id = l_par_rcv_txn AND
2296 crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
2297 crac.cost_group_id = i_ae_txn_rec.cost_group_id AND
2298 cracd.header_id = crac.header_id AND
2299 cracd.source_type = 'INVOICE' AND
2300 cracd.invoice_distribution_id = aida.related_id AND
2301 aida.line_type_lookup_code = 'ERV' AND
2302 aida.base_amount <> 0 AND
2303 aida.posted_flag = 'Y';-- Added for bug 4773085
2304
2305 xla_count NUMBER:=0;
2306 c_erv_rec c_erv%ROWTYPE;
2307
2308
2309 BEGIN
2310
2311 OPEN xla_erv;
2312 FETCH xla_erv INTO c_erv_rec;
2313
2314 IF(xla_erv%FOUND) THEN
2315 xla_count:=1;
2316 ELSE
2317 xla_count:=0;
2318 END IF;
2319
2320 CLOSE xla_erv;
2321
2322 IF(xla_count=1) THEN
2323 OPEN xla_erv;
2324 ELSE
2325 OPEN c_erv;
2326 END IF ;
2327
2328 LOOP
2329
2330 IF (xla_count=1) THEN
2331 FETCH xla_erv INTO c_erv_rec;
2332 EXIT WHEN xla_erv%notfound;
2333 ELSE
2334 FETCH c_erv INTO c_erv_rec;
2335 EXIT WHEN c_erv%notfound;
2336 END IF;
2337
2338 IF (l_net_receipt = 1 and c_erv_rec.exchange_rate_variance > 0) OR (l_net_receipt = -1 and c_erv_rec.exchange_rate_variance < 0) THEN
2339 l_dr_flag := FALSE;
2340 ELSE
2341 l_dr_flag := TRUE;
2342 END IF;
2343
2344 l_ae_line_rec.account := c_erv_rec.code_combination_id;
2345 l_ae_line_rec.ae_line_type := 18;
2346 --l_ae_line_rec.transaction_value := c_erv_rec.exchange_rate_variance * (c_receipts_rec.dist_quantity / l_costed_quantity);
2347 l_ae_line_rec.transaction_value := c_erv_rec.exchange_rate_variance * (i_ae_txn_rec.primary_quantity / l_costed_quantity);
2348
2349 -- also populate type date etc
2350 l_curr_rec.currency_conv_rate := 1;
2351 l_curr_rec.currency_conv_date := null;
2352 l_curr_rec.currency_conv_type := null;
2353
2354 IF l_debug_flag = 'Y' THEN
2355 IF (l_dr_flag) THEN
2356 fnd_file.put_line(fnd_file.log,'DR ERV: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2357 ELSE
2358 fnd_file.put_line(fnd_file.log,'CR ERV: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2359 END IF;
2360 END IF;
2361
2362 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
2363 not the primary quantity on the transaction */
2364 IF (i_ae_txn_rec.primary_quantity = 0) THEN
2365 l_ae_line_rec.rate_or_amount := 0;
2366 ELSE
2367 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / i_ae_txn_rec.primary_quantity;
2368 END IF;
2369
2370 CSTPAPPR.insert_account (i_ae_txn_rec,
2371 l_curr_rec,
2372 l_dr_flag,
2373 l_ae_line_rec,
2374 l_ae_line_tbl,
2375 l_err_rec);
2376 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) THEN
2377 raise process_error;
2378 END IF;
2379
2380 END LOOP;
2381 EXCEPTION
2382 when process_error then
2383 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
2384 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
2385 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
2386
2387 IF(xla_count=1) THEN
2388 CLOSE xla_erv;
2389 ELSE
2390 CLOSE c_erv;
2391 END IF ;
2392
2393 END;
2394 END IF;
2395 END IF;
2396 END LOOP; /*not expense*/
2397 EXCEPTION
2398 when process_error then
2399 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
2400 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
2401 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
2402
2403 END;
2404
2405 -- Take care of rounding errors.
2406 -- -----------------------------
2407 l_stmt_num := 80;
2408 balance_account (l_ae_line_tbl,
2409 l_err_rec);
2410
2411 -- check error
2412 if (l_err_rec.l_err_num <> 0) then
2413 raise process_error;
2414 end if;
2415
2416
2417 DECLARE
2418 CURSOR c_receive_dists IS
2419 SELECT
2420 decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_rate,nvl(pod.rate,1)) "EXCHG_RATE",
2421 decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_date,pod.rate_date) "EXCHG_DATE",
2422 -- J Changes --------------------------------------------------------------------------------------
2423 DECODE (POLL.MATCHING_BASIS, 'AMOUNT', 1, -- Changed for Complex work procurement
2424 'QUANTITY', 0)"SERVICE_FLAG",
2425 ---------------------------------------------------------------------------------------------------
2426 POD.po_distribution_id "PO_DISTRIBUTION_ID",
2427 POLL.line_location_id "PO_LINE_LOCATION_ID",
2428 POD.code_combination_id "EXPENSE_ACCOUNT_ID",
2429 POD.destination_type_code "DESTINATION_TYPE_CODE",
2430 RP.receiving_account_id "RECEIVING_ACCOUNT_ID",
2431 POD.accrual_account_id "ACCRUAL_ACCOUNT_ID",
2432 nvl(POD.budget_account_id,-1) "ENCUMBRANCE_ACCOUNT_ID",
2433 -- J Changes --------------------------------------------------------------------------------------
2434 decode(l_doc_level,'D', 1, DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work procurement
2435 'AMOUNT', POD.AMOUNT_ORDERED/POLL.AMOUNT,
2436 'QUANTITY',pod.quantity_ordered/poll.quantity))
2437 * i_ae_txn_rec.primary_quantity "DIST_QUANTITY",
2438 po_tax_sv.get_tax('PO',pod.po_distribution_id) /
2439 DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work procurement
2440 'AMOUNT', POD.AMOUNT_ORDERED,
2441 'QUANTITY',POD.QUANTITY_ORDERED*l_uom_ratio) "TAX"
2442 ---------------------------------------------------------------------------------------------------
2443 FROM
2444 po_distributions_all pod,
2445 po_line_locations_all poll,
2446 -- J Changes --------------------------------------------------------------------------------------
2447 PO_LINES_ALL POL,
2448 ---------------------------------------------------------------------------------------------------
2449 rcv_parameters rp
2450 WHERE
2451 -- begin fix for perf bug 2581067
2452 (
2453 (l_doc_level = 'D' AND pod.po_distribution_id = l_doc_id)
2454 OR (l_doc_level = 'S' AND poll.line_location_id = l_doc_id)
2455 )
2456 -- end fix for perf bug 2581067.
2457 and pod.line_location_id = poll.line_location_id
2458 -- J Changes --------------------------------------------------------------------------------------
2459 AND POLL.PO_LINE_ID = POL.PO_LINE_ID
2460 ---------------------------------------------------------------------------------------------------
2461 and rp.organization_id = pod.destination_organization_id
2462 and pod.destination_type_code in ('EXPENSE')
2463 and nvl(POLL.accrue_on_receipt_flag,'N') = 'Y'
2464 /* and nvl(POD.accrue_on_receipt_flag,'N') = 'Y' */;
2465 BEGIN
2466 FOR c_receipts_rec IN c_receive_dists LOOP
2467 l_stmt_num := 62;
2468 l_ae_line_rec.actual_flag := NULL;
2469 l_ae_line_rec.po_distribution_id := c_receipts_rec.po_distribution_id;
2470
2471 ---------------------------------------------------------
2472 -- The PO Price is in terms of the PO UOM
2473 -- Convert it in terms of the primary UOM for the item
2474 ---------------------------------------------------------
2475
2476 SELECT
2477 DECODE(C_RECEIPTS_REC.SERVICE_FLAG, 1, 1,
2478 poll.price_override * rt.source_doc_quantity / rt.primary_quantity)
2479 INTO
2480 l_po_price
2481 FROM
2482 rcv_transactions rt,
2483 po_line_locations_all poll
2484 WHERE
2485 rt.transaction_id = i_ae_txn_rec.transaction_id
2486 AND rt.po_line_location_id = poll.line_location_id;
2487
2488
2489
2490 l_ae_line_rec.transaction_value := c_receipts_rec.dist_quantity * (l_po_price + c_receipts_rec.tax);
2491 /*Bug 14602236 No need to get from the cursor, they will get from RAE*/
2492 /*l_curr_rec.currency_conv_rate := c_receipts_rec.exchg_rate;
2493 l_curr_rec.currency_conv_date := c_receipts_rec.exchg_date;
2494 l_curr_rec.currency_conv_type := i_ae_txn_rec.currency_conv_type;*/
2495
2496 IF (l_stage = 1) THEN
2497
2498 ---------------------------------------------------------
2499 -- For expense destinations, stage 1 accounting is as follows:
2500 -- Dr. Receiving Inspection qty@po
2501 -- Cr. Accrual qty@po
2502 ---------------------------------------------------------
2503
2504
2505 /*Bug 14602236 both 5 and 16 type need to get the correct currency conversion rate and related information for global procurement scenario, so we get them directly from RAE
2506 , then prevent to get the conversion rate form the procurement organizations for phsical txn, it is not correct.*/
2507 select currency_conversion_rate, currency_conversion_date, currency_conversion_type
2508 into l_curr_rec.currency_conv_rate, l_curr_rec.currency_conv_date, l_curr_rec.currency_conv_type
2509 from rcv_accounting_events
2510 where rcv_transaction_id = i_ae_txn_rec.transaction_id
2511 and organization_id = i_ae_txn_rec.organization_id
2512 and accounting_event_id = (
2513 select min(accounting_event_id)
2514 from rcv_accounting_events
2515 where rcv_transaction_id = i_ae_txn_rec.transaction_id
2516 and organization_id = i_ae_txn_rec.organization_id
2517 );
2518
2519 IF (l_net_receipt = 1) THEN
2520 l_dr_flag := TRUE;
2521 ELSE
2522 l_dr_flag := FALSE;
2523 END IF;
2524
2525 l_ae_line_rec.account := c_receipts_rec.receiving_account_id;
2526 l_ae_line_rec.ae_line_type := 5;
2527
2528 IF l_debug_flag = 'Y' THEN
2529 IF (l_dr_flag) THEN
2530 fnd_file.put_line(fnd_file.log,'DR RI: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2531 ELSE
2532 fnd_file.put_line(fnd_file.log,'CR RI: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2533 END IF;
2534 END IF;
2535
2536 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
2537 not the primary quantity on the transaction */
2538 IF (c_receipts_rec.dist_quantity = 0) THEN
2539 l_ae_line_rec.rate_or_amount := 0;
2540 ELSE
2541 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity;
2542 END IF;
2543
2544 CSTPAPPR.insert_account (i_ae_txn_rec,
2545 l_curr_rec,
2546 l_dr_flag,
2547 l_ae_line_rec,
2548 l_ae_line_tbl,
2549 l_err_rec);
2550 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
2551 THEN
2552 raise process_error;
2553 END IF;
2554
2555
2556 l_dr_flag := not l_dr_flag;
2557
2558 /*Bug 14235739 like the bug in the inventory and shop floor part, we need to handle the global procurement scenario,
2559 And get the accounts and conversion rate from RAE directly, becuase whatever it is a normal PO or global procurement,
2560 it already get accounts ready, so we directly use it in PAC
2561
2562 RECEIVE*/
2563 begin
2564 select credit_account_id, debit_account_id
2565 into l_credit_Account, l_debit_account
2566 from rcv_accounting_events
2567 where rcv_transaction_id = i_ae_txn_rec.transaction_id
2568 and organization_id = i_ae_txn_rec.organization_id
2569 and accounting_event_id = (
2570 select min(accounting_event_id)
2571 from rcv_accounting_events
2572 where rcv_transaction_id = i_ae_txn_rec.transaction_id
2573 and organization_id = i_ae_txn_rec.organization_id
2574 );
2575
2576 if(l_dr_flag) then
2577 l_ae_line_rec.account := l_debit_account;
2578 else
2579 l_ae_line_rec.account := l_credit_account;
2580 end if;
2581
2582 exception
2583 when others then
2584 l_ae_line_rec.account := c_receipts_rec.accrual_account_id;
2585 end;
2586
2587 l_stmt_num := 759;
2588
2589 /*l_ae_line_rec.account := c_receipts_rec.accrual_account_id;*/
2590 l_ae_line_rec.ae_line_type := 16;
2591
2592 IF l_debug_flag = 'Y' THEN
2593 IF (l_dr_flag) THEN
2594 fnd_file.put_line(fnd_file.log,'DR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2595 ELSE
2596 fnd_file.put_line(fnd_file.log,'CR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2597 END IF;
2598 END IF;
2599
2600 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
2601 not the primary quantity on the transaction */
2602 IF (c_receipts_rec.dist_quantity = 0) THEN
2603 l_ae_line_rec.rate_or_amount := 0;
2604 ELSE
2605 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity;
2606 END IF;
2607
2608
2609
2610 CSTPAPPR.insert_account (i_ae_txn_rec,
2611 l_curr_rec,
2612 l_dr_flag,
2613 l_ae_line_rec,
2614 l_ae_line_tbl,
2615 l_err_rec);
2616
2617 -- Take care of rounding errors.
2618 -- -----------------------------
2619 l_stmt_num := 80;
2620 balance_account (l_ae_line_tbl,
2621 l_err_rec);
2622
2623 -- check error
2624 if (l_err_rec.l_err_num <> 0) then
2625 raise process_error;
2626 end if;
2627
2628
2629 ELSE /*if stage <> 1*/
2630
2631 ------------------------------------------------------------
2632 -- For Expense destinations, stage 2 accounting is as follows :
2633 -- Dr. Expense qty@po
2634 -- Cr. Receiving Inspection qty@po
2635 ------------------------------------------------------------
2636
2637 IF (l_net_receipt = 1) THEN
2638 l_dr_flag := TRUE;
2639 ELSE
2640 l_dr_flag := FALSE;
2641 END IF;
2642
2643 /*Bug 14235739/14602236 like the bug in the inventory and shop floor part, we need to handle the global procurement scenario,
2644 And get the accounts and conversion rate from RAE directly, becuase whatever it is a normal PO or global procurement,
2645 it already get accounts ready, so we directly use it in PAC
2646
2647 DELIVER txn*/
2648 begin
2649 select credit_account_id, debit_account_id, currency_conversion_rate, currency_conversion_date, currency_conversion_type
2650 into l_credit_Account, l_debit_account, l_curr_rec.currency_conv_rate, l_curr_rec.currency_conv_date, l_curr_rec.currency_conv_type
2651 from rcv_accounting_events
2652 where rcv_transaction_id = i_ae_txn_rec.transaction_id
2653 and organization_id = i_ae_txn_rec.organization_id
2654 and accounting_event_id = (
2655 select min(accounting_event_id)
2656 from rcv_accounting_events
2657 where rcv_transaction_id = i_ae_txn_rec.transaction_id
2658 and organization_id = i_ae_txn_rec.organization_id
2659 );
2660
2661 if(l_dr_flag) then
2662 l_ae_line_rec.account := l_debit_account;
2663 else
2664 l_ae_line_rec.account := l_credit_account;
2665 end if;
2666
2667 exception
2668 when others then
2669 l_ae_line_rec.account := c_receipts_rec.expense_account_id;
2670 end;
2671
2672 l_stmt_num := 761;
2673
2674
2675 /*l_ae_line_rec.account := c_receipts_rec.expense_account_id;*/
2676 l_ae_line_rec.ae_line_type := 20;
2677
2678 IF (c_receipts_rec.dist_quantity = 0) THEN
2679 l_ae_line_rec.rate_or_amount := 0;
2680 ELSE
2681 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity;
2682 END IF;
2683
2684 IF l_debug_flag = 'Y' THEN
2685 IF (l_dr_flag) THEN
2686 fnd_file.put_line(fnd_file.log,'DR Expense: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2687 ELSE
2688 fnd_file.put_line(fnd_file.log,'CR Expense: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2689 END IF;
2690 END IF;
2691
2692
2693 CSTPAPPR.insert_account (i_ae_txn_rec,
2694 l_curr_rec,
2695 l_dr_flag,
2696 l_ae_line_rec,
2697 l_ae_line_tbl,
2698 l_err_rec);
2699
2700 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
2701 THEN
2702 raise process_error;
2703 END IF;
2704
2705 l_dr_flag := not l_dr_flag;
2706 l_ae_line_rec.account := c_receipts_rec.receiving_account_id;
2707 l_ae_line_rec.ae_line_type := 5;
2708 IF (c_receipts_rec.dist_quantity = 0) THEN
2709 l_ae_line_rec.rate_or_amount := 0;
2710 ELSE
2711 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity;
2712 END IF;
2713
2714 IF l_debug_flag = 'Y' THEN
2715 IF (l_dr_flag) THEN
2716 fnd_file.put_line(fnd_file.log,'DR RI: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2717 ELSE
2718 fnd_file.put_line(fnd_file.log,'CR RI: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2719 END IF;
2720 END IF;
2721
2722 CSTPAPPR.insert_account (i_ae_txn_rec,
2723 l_curr_rec,
2724 l_dr_flag,
2725 l_ae_line_rec,
2726 l_ae_line_tbl,
2727 l_err_rec);
2728 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
2729 THEN
2730 raise process_error;
2731 END IF;
2732
2733 -- Take care of rounding errors.
2734 -- -----------------------------
2735 l_stmt_num := 80;
2736 balance_account (l_ae_line_tbl,
2737 l_err_rec);
2738
2739 -- check error
2740 if (l_err_rec.l_err_num <> 0) then
2741 raise process_error;
2742 end if;
2743
2744 l_stmt_num := 85;
2745 -----------------------------------------------------------
2746 -- Encumbrance entries
2747 -- First check if encumbrance is on
2748 -- Get the encumbrance type flag and budget account
2749 -----------------------------------------------------------
2750
2751 -- If the budget account was not specified at PO creation
2752 -- no encumbrance reversal is necessary
2753
2754 IF (c_receipts_rec.encumbrance_account_id = -1) THEN
2755 IF l_debug_flag = 'Y' THEN
2756 fnd_file.put_line(fnd_file.log,'No Encumbrance account at PO level');
2757 END IF;
2758 ELSE
2759
2760 CSTPAPPR.check_encumbrance(
2761 i_transaction_id => i_ae_txn_rec.transaction_id,
2762 i_set_of_books_id => i_ae_txn_rec.set_of_books_id,
2763 i_period_name => i_ae_txn_rec.accounting_period_name,
2764 i_encumbrance_account_id => c_receipts_rec.encumbrance_account_id,
2765 o_enc_flag => l_enc_flag,
2766 o_purch_encumbrance_type_id => l_purch_encumbrance_type_id,
2767 o_purch_encumbrance_flag => l_purch_encumbrance_flag,
2768 o_ae_err_rec => l_err_rec);
2769 if (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
2770 then
2771 raise process_error;
2772 end if;
2773
2774 /* Commented and replaced for forward port bug 5768550
2775 IF (l_enc_flag = 'Y' and l_purch_encumbrance_flag = 'Y') then*/
2776 IF l_purch_encumbrance_flag = 'Y' THEN
2777
2778 ------------------------------------------------------------------
2779 -- For encumbrance :
2780 -- At the time of PO creation, if encumbrance is turned on,
2781 -- encumbrance is created for the quantity ordered @po price
2782 -- At the time of delivery , if the reversal flag is turned on,
2783 -- reversing entries need to be generated for the quantity delivered
2784 -- but not exceeding the quantity encumbered.
2785 -------------------------------------------------------------------
2786
2787 -------------------------------------------------------------------
2788 -- Get the quantity delivered before the current transaction
2789 -- This quantity will be used to determine the amount that has
2790 -- been reversed and the quantity available to unencumber.
2791 -------------------------------------------------------------------
2792
2793 l_quantity_delivered := CSTPAPPR.get_net_del_qty(
2794 c_receipts_rec.po_distribution_id,
2795 i_ae_txn_rec.transaction_id);
2796 IF l_debug_flag = 'Y' THEN
2797 fnd_file.put_line(fnd_file.log,'Delivered Quantity: '||to_char(l_quantity_delivered));
2798 END IF;
2799
2800 l_stmt_num := 86;
2801
2802 ------------------------------------------------------------------
2803 -- cases possible:
2804 -- I. current transaction is a net deliver transaction
2805 -- a. quantity ordered > quantity delivered
2806 -- 1. quantity delivered after the current txn becomes > ordered
2807 -- qty to unencumber = (ordered - delivered)
2808 -- 2. quantity delivered after the current txn remains < ordered
2809 -- qty to unencumber = qty of current txn
2810 -- b. quantity ordered < quantity delivered
2811 -- 1. quantity delivered remains > ordered
2812 -- qty to unencumber = 0
2813 -- II. current transaction is a net return transaction
2814 -- a. quantity ordered > quantity delivered
2815 -- 1. quantity delivered remains < quantity delivered
2816 -- qty to unencumber = qty of current txn
2817 -- b. quantity ordered < quantity delivered
2818 -- 1. quantity delivered after current txn becomes < ordered
2819 -- qty to unencumber = qty of current txn - (qty del - qty ord)
2820 -- 2. quantity delivered after current txn remains > ordered
2821 -- qty to unencumber = 0
2822 -- Finally, Convert the quantity into Primary UOM
2823 ---------------------------------------------------------------------------------
2824 -- J Changes ---------------------------------------------------------------------------
2825 -- Compute Net Delivered Amount for Service Line Types
2826 IF C_RECEIPTS_REC.SERVICE_FLAG <> 1 THEN
2827 SELECT
2828 decode (l_net_receipt,
2829 1,
2830 least(
2831 abs(rt.source_doc_quantity),
2832 greatest(POD.quantity_ordered-l_quantity_delivered,0)
2833 ),
2834 -1,
2835 greatest(
2836 (least(POD.quantity_ordered-l_quantity_delivered,0) + abs(rt.source_doc_quantity)),
2837 0
2838 ),
2839 0
2840 ) * rt.primary_quantity/rt.source_doc_quantity * l_po_price,
2841 nvl(POD.rate, 1),
2842 pod.rate_date
2843 INTO
2844 l_encum_amount,
2845 l_po_rate,
2846 l_po_rate_date
2847 FROM
2848 po_headers_all POH,
2849 po_lines_all POL,
2850 po_line_locations_all POLL,
2851 po_distributions_all POD,
2852 rcv_transactions RT
2853 WHERE
2854 POH.po_header_id = POD.po_header_id AND
2855 POL.po_line_id = POD.po_line_id AND
2856 POLL.line_location_id = POD.line_location_id AND
2857 POD.po_distribution_id = c_receipts_rec.po_distribution_id AND
2858 nvl(POLL.accrue_on_receipt_flag,'N') = 'Y' AND
2859 /*nvl(POD.accrue_on_receipt_flag,'N') = 'Y' AND */
2860 RT.transaction_id = i_ae_txn_rec.transaction_id AND
2861 POD.destination_type_code = 'EXPENSE';
2862
2863 ELSE -- Service Line Types
2864 SELECT
2865 decode (l_net_receipt,
2866 1,
2867 least(
2868 abs(RT.AMOUNT),
2869 greatest(POD.AMOUNT_ORDERED - L_QUANTITY_DELIVERED, 0)
2870 ),
2871 -1,
2872 greatest(
2873 (least(POD.AMOUNT_ORDERED - L_QUANTITY_DELIVERED, 0) + abs(rt.AMOUNT)),
2874 0
2875 ),
2876 0
2877 ) * l_po_price,
2878 nvl(POD.rate, 1),
2879 pod.rate_date
2880 INTO
2881 l_encum_amount,
2882 l_po_rate,
2883 l_po_rate_date
2884 FROM
2885 po_headers_all POH,
2886 po_lines_all POL,
2887 po_line_locations_all POLL,
2888 po_distributions_all POD,
2889 rcv_transactions RT
2890 WHERE
2891 POH.po_header_id = POD.po_header_id
2892 AND POL.po_line_id = POD.po_line_id
2893 AND POLL.line_location_id = POD.line_location_id
2894 AND POD.po_distribution_id = c_receipts_rec.po_distribution_id
2895 AND nvl(POLL.accrue_on_receipt_flag,'N') = 'Y'
2896 /*AND nvl(POD.accrue_on_receipt_flag,'N') = 'Y' */
2897 AND RT.transaction_id = i_ae_txn_rec.transaction_id
2898 AND POD.destination_type_code = 'EXPENSE';
2899 END IF;
2900 ----------------------------------------------------------------------------------------------
2901
2902 IF (l_net_receipt = 1) then
2903 l_dr_flag := FALSE;
2904 ELSE
2905 l_dr_flag := TRUE;
2906 END IF;
2907 l_curr_rec.currency_conv_rate := l_po_rate;
2908 l_curr_rec.currency_conv_date := l_po_rate_date;
2909 l_ae_line_rec.transaction_value := l_encum_amount;
2910 l_ae_line_rec.account := c_receipts_rec.encumbrance_account_id;
2911 l_ae_line_rec.ae_line_type := 15;
2912 l_ae_line_rec.actual_flag := 'E';
2913 l_ae_line_rec.encum_type_id := l_purch_encumbrance_type_id;
2914
2915 IF l_debug_flag = 'Y' THEN
2916 IF (l_dr_flag) THEN
2917 fnd_file.put_line(fnd_file.log,'DR Enc: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2918 ELSE
2919 fnd_file.put_line(fnd_file.log,'CR Enc: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2920 END IF;
2921 END IF;
2922 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
2923 not the primary quantity on the transaction */
2924 IF (c_receipts_rec.dist_quantity = 0) THEN
2925 l_ae_line_rec.rate_or_amount := 0;
2926 ELSE
2927 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity;
2928 END IF;
2929
2930 CSTPAPPR.insert_account (i_ae_txn_rec,
2931 l_curr_rec,
2932 l_dr_flag,
2933 l_ae_line_rec,
2934 l_ae_line_tbl,
2935 l_err_rec);
2936 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
2937 THEN
2938 raise process_error;
2939 END IF;
2940
2941
2942 END IF; /* if enc on */
2943
2944 END IF; /* if po budget acct specified */
2945
2946 END IF; /*stage if*/
2947 --END IF; /* expense if*/
2948 END LOOP;
2949 EXCEPTION
2950 when process_error then
2951 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
2952 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
2953 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
2954
2955 END;
2956
2957
2958 -- Return the lines pl/sql table.
2959 -- ------------------------------
2960 l_stmt_num := 90;
2961 o_ae_line_rec_tbl := l_ae_line_tbl;
2962 IF l_debug_flag = 'Y' THEN
2963 fnd_file.put_line(fnd_file.log,'Create_Rcv_Ae_Lines >>> ');
2964 END IF;
2965
2966 EXCEPTION
2967
2968 when process_error then
2969 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
2970 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
2971 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
2972
2973 when cst_no_po_dist then
2974 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
2975 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
2976 FND_MESSAGE.set_name('BOM', 'CST_NO_RCV_LINE');
2977 o_ae_err_rec.l_err_msg := FND_MESSAGE.Get;
2978
2979 when others then
2980 o_ae_err_rec.l_err_num := SQLCODE;
2981 o_ae_err_rec.l_err_code := '';
2982 o_ae_err_rec.l_err_msg := 'CSTPAPPR.create_rcv_ae_lines : ' || to_char(l_stmt_num) || ':'||
2983 substr(SQLERRM,1,180);
2984
2985 END create_rcv_ae_lines;
2986
2987 -- Retro Changes--------------------------------------------------------------
2988 PROCEDURE create_adj_ae_lines(
2989 p_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
2990 x_ae_line_rec_tbl OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
2991 x_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
2992 ) IS
2993 l_ae_line_tbl CSTPALTY.CST_AE_LINE_TBL_TYPE;
2994 l_ae_line_rec CSTPALTY.CST_AE_LINE_REC_TYPE;
2995 l_curr_rec CSTPALTY.cst_ae_curr_rec_type;
2996 l_err_rec CSTPALTY.cst_ae_err_rec_type;
2997 l_dr_flag BOOLEAN;
2998
2999 -- Retroactive Pricing --
3000 l_current_transaction_value NUMBER;
3001 l_prior_transaction_value NUMBER;
3002
3003 l_current_entered_value NUMBER;
3004 l_prior_entered_value NUMBER;
3005
3006 l_current_accounted_value NUMBER;
3007 l_prior_accounted_value NUMBER;
3008 -------------------------
3009
3010
3011 l_stmt_num NUMBER := 0;
3012 l_debit_account NUMBER;
3013 INVALID_RETRO_ADJ_ACCOUNT EXCEPTION;
3014 PROCESS_ERROR EXCEPTION;
3015
3016 BEGIN
3017
3018 IF l_debug_flag = 'Y' THEN
3019 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Create_Adj_Ae_Lines <<<');
3020 END IF;
3021
3022 -- Initialize the collection
3023 l_ae_line_tbl := CSTPALTY.cst_ae_line_tbl_type();
3024
3025 -- Populate the Currency Structure
3026 l_stmt_num := 10;
3027
3028 select
3029 currency_code
3030 into
3031 l_curr_rec.pri_currency
3032 from
3033 gl_sets_of_books
3034 where
3035 set_of_books_id = p_ae_txn_rec.set_of_books_id;
3036
3037 l_stmt_num := 20;
3038
3039 l_curr_rec.alt_currency := p_ae_txn_rec.currency_code;
3040 l_curr_rec.currency_conv_date := p_ae_txn_rec.currency_conv_date;
3041 l_curr_rec.currency_conv_type := p_ae_txn_rec.currency_conv_type;
3042 l_curr_rec.currency_conv_rate := p_ae_txn_rec.currency_conv_rate;
3043
3044
3045 IF l_debug_flag = 'Y' THEN
3046 FND_FILE.PUT_LINE(FND_FILE.LOG, l_curr_rec.pri_currency || ' '||l_curr_rec.alt_currency);
3047 END IF;
3048
3049 -- Populate the Accounting Line Record
3050
3051 -- For ADJUST_RECEIVE events, the accounting is as follows:
3052 -- Dr Periodic Retroactive Adjustment Account
3053 -- Cr Accrual
3054
3055 l_stmt_num := 30;
3056
3057 -- Get the debit account
3058 SELECT
3059 nvl(RETRO_PRICE_ADJ_ACCOUNT, -1)
3060 INTO
3061 l_debit_account
3062 FROM
3063 CST_ORG_COST_GROUP_ACCOUNTS
3064 WHERE
3065 LEGAL_ENTITY_ID = P_AE_TXN_REC.LEGAL_ENTITY_ID
3066 AND COST_TYPE_ID = P_AE_TXN_REC.COST_TYPE_ID
3067 AND COST_GROUP_ID = P_AE_TXN_REC.COST_GROUP_ID;
3068
3069 IF l_debug_flag = 'Y' THEN
3070 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Debit Account: '||to_char(l_debit_account));
3071 END IF;
3072
3073 IF l_debit_account = -1 THEN
3074 RAISE INVALID_RETRO_ADJ_ACCOUNT;
3075 END IF;
3076
3077 l_stmt_num := 40;
3078 -- Debit
3079
3080 l_dr_flag := TRUE;
3081 -- The Line Type is seeded in MFG_LOOKUPS (CST_ACCOUNTING_LINE_TYPE)
3082 l_ae_line_rec.ae_line_type := 32;
3083
3084 l_ae_line_rec.account := l_debit_account;
3085
3086 l_current_transaction_value := p_ae_txn_rec.primary_quantity * p_ae_txn_rec.unit_price;
3087 l_prior_transaction_value := p_ae_txn_rec.primary_quantity * p_ae_txn_rec.prior_unit_price;
3088
3089 -- Adjustment Amount is computed as:
3090 -- Round(Round(unit_price * qty) * rate) - Round(Round(prior_unit_price*qty) * rate)
3091
3092 -- Rounding is done using the Minimum accounting unit or currency precision
3093 -- Rounded Current_Transaction Value
3094 select
3095 decode(l_curr_rec.alt_currency,NULL, NULL,
3096 l_curr_rec.pri_currency, NULL,
3097 decode(c2.minimum_accountable_unit,
3098 NULL,
3099 round(l_current_transaction_value, c2.precision),
3100 round(l_current_transaction_value /c2.minimum_accountable_unit)
3101 * c2.minimum_accountable_unit )),
3102 decode(c1.minimum_accountable_unit,
3103 NULL, round(l_current_transaction_value * l_curr_rec.currency_conv_rate, c1.precision),
3104 round(l_current_transaction_value * l_curr_rec.currency_conv_rate/c1.minimum_accountable_unit)
3105 * c1.minimum_accountable_unit ),
3106 decode(l_curr_rec.alt_currency,NULL, NULL,
3107 l_curr_rec.pri_currency, NULL,
3108 decode(c2.minimum_accountable_unit,
3109 NULL,
3110 round(l_prior_transaction_value, c2.precision),
3111 round(l_prior_transaction_value /c2.minimum_accountable_unit)
3112 * c2.minimum_accountable_unit )),
3113 decode(c1.minimum_accountable_unit,
3114 NULL, round(l_prior_transaction_value * l_curr_rec.currency_conv_rate, c1.precision),
3115 round(l_prior_transaction_value * l_curr_rec.currency_conv_rate/c1.minimum_accountable_unit)
3116 * c1.minimum_accountable_unit )
3117 into
3118 l_current_entered_value,
3119 l_current_accounted_value,
3120 l_prior_entered_value,
3121 l_prior_accounted_value
3122 from
3123 fnd_currencies c1,
3124 fnd_currencies c2
3125 where
3126 c1.currency_code = l_curr_rec.pri_currency
3127 and c2.currency_code = decode(l_curr_rec.alt_currency, NULL,
3128 l_curr_rec.pri_currency,
3129 l_curr_rec.alt_currency);
3130
3131 l_ae_line_rec.accounted_value := l_current_accounted_value - l_prior_accounted_value;
3132 l_ae_line_rec.entered_value := l_current_entered_value - l_prior_entered_value;
3133
3134 l_ae_line_rec.transaction_value := l_ae_line_rec.accounted_value;
3135
3136 IF l_debug_flag = 'Y' THEN
3137 FND_FILE.PUT_LINE(FND_FILE.log, 'l_ae_line_rec.accounted_value: '||to_char(l_ae_line_rec.accounted_value));
3138 END IF;
3139
3140 l_ae_line_rec.source_table := 'RAE';
3141 l_ae_line_rec.source_id := p_ae_txn_rec.transaction_id;
3142
3143 IF p_ae_txn_rec.primary_quantity <> 0 THEN
3144 l_ae_line_rec.rate_or_amount := p_ae_txn_rec.unit_price * l_curr_rec.currency_conv_rate;
3145 ELSE
3146 l_ae_line_rec.rate_or_amount := 0 ;
3147 END IF;
3148
3149 l_ae_line_rec.po_distribution_id := p_ae_txn_rec.po_distribution_id;
3150
3151 l_stmt_num := 50;
3152
3153 CSTPAPPR.insert_account (p_ae_txn_rec,
3154 l_curr_rec,
3155 l_dr_flag,
3156 l_ae_line_rec,
3157 l_ae_line_tbl,
3158 l_err_rec);
3159
3160 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) THEN
3161 raise process_error;
3162 END IF;
3163
3164 -- Credit
3165 -- Credit Account is the accrual account (stamped as Credit_Account on the
3166 -- transaction
3167 l_stmt_num := 60;
3168
3169 l_dr_flag := NOT l_dr_flag;
3170
3171 l_ae_line_rec.account := p_ae_txn_rec.credit_account;
3172 l_ae_line_rec.ae_line_type := 16;
3173 IF l_debug_flag = 'Y' THEN
3174 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Credit Account: '||to_char(l_ae_line_rec.account));
3175 END IF;
3176
3177 l_stmt_num := 70;
3178
3179 CSTPAPPR.insert_account (p_ae_txn_rec,
3180 l_curr_rec,
3181 l_dr_flag,
3182 l_ae_line_rec,
3183 l_ae_line_tbl,
3184 l_err_rec);
3185
3186 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) THEN
3187 raise process_error;
3188 END IF;
3189
3190 -- Copy the local structure to the Output
3191 x_ae_line_rec_tbl := l_ae_line_tbl;
3192
3193 IF l_debug_flag = 'Y' THEN
3194 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Create_Adj_Ae_Lines >>>');
3195 END IF;
3196
3197 EXCEPTION
3198 WHEN INVALID_RETRO_ADJ_ACCOUNT THEN
3199 l_err_rec.l_err_num := SQLCODE;
3200 l_err_rec.l_err_code := 'No Periodic Retroactive Adjustment Account Specified';
3201 l_err_rec.l_err_msg := 'CSTPAPPR:( '||to_char(l_stmt_num)||' ): ' || l_err_rec.l_err_code;
3202 x_ae_err_rec := l_err_rec;
3203 FND_FILE.PUT_LINE(FND_FILE.LOG, l_err_rec.l_err_msg);
3204
3205 WHEN PROCESS_ERROR THEN
3206 x_ae_err_rec := l_err_rec;
3207 FND_FILE.PUT_LINE(FND_FILE.LOG, l_err_rec.l_err_msg);
3208
3209 WHEN OTHERS THEN
3210 l_err_rec.l_err_num := SQLCODE;
3211 l_err_rec.l_err_code := '';
3212 l_err_rec.l_err_msg := 'CSTPAPPR:( '||to_char(l_stmt_num)||' ): ' || to_char(SQLCODE);
3213 x_ae_err_rec := l_err_rec;
3214 FND_FILE.PUT_LINE(FND_FILE.LOG, l_err_rec.l_err_msg);
3215
3216 END create_adj_ae_lines;
3217
3218 /*LCM CHANGES */
3219
3220 PROCEDURE create_lc_adj_ae_lines(
3221 p_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
3222 x_ae_line_rec_tbl OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
3223 x_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
3224 ) IS
3225 l_ae_line_tbl CSTPALTY.CST_AE_LINE_TBL_TYPE;
3226 l_ae_line_rec CSTPALTY.CST_AE_LINE_REC_TYPE;
3227 l_curr_rec CSTPALTY.cst_ae_curr_rec_type;
3228 l_err_rec CSTPALTY.cst_ae_err_rec_type;
3229 l_dr_flag BOOLEAN;
3230
3231
3232 l_current_transaction_value NUMBER;
3233 l_prior_transaction_value NUMBER;
3234
3235 l_stmt_num NUMBER := 0;
3236 PROCESS_ERROR EXCEPTION;
3237
3238 BEGIN
3239
3240 IF l_debug_flag = 'Y' THEN
3241 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Create_LC_Adj_Ae_Lines <<<');
3242 END IF;
3243
3244 -- Initialize the collection
3245 l_ae_line_tbl := CSTPALTY.cst_ae_line_tbl_type();
3246
3247 -- Populate the Currency Structure
3248 l_stmt_num := 10;
3249
3250 select
3251 currency_code
3252 into
3253 l_curr_rec.pri_currency
3254 from
3255 gl_sets_of_books
3256 where
3257 set_of_books_id = p_ae_txn_rec.set_of_books_id;
3258
3259 l_stmt_num := 20;
3260
3261 l_curr_rec.alt_currency := p_ae_txn_rec.currency_code;
3262 l_curr_rec.currency_conv_date := p_ae_txn_rec.currency_conv_date;
3263 l_curr_rec.currency_conv_type := p_ae_txn_rec.currency_conv_type;
3264 l_curr_rec.currency_conv_rate := p_ae_txn_rec.currency_conv_rate;
3265
3266
3267 IF l_debug_flag = 'Y' THEN
3268 FND_FILE.PUT_LINE(FND_FILE.LOG, l_curr_rec.pri_currency || ' '||l_curr_rec.alt_currency);
3269 END IF;
3270
3271 l_stmt_num := 30;
3272
3273 l_dr_flag := TRUE;
3274 l_ae_line_rec.account := p_ae_txn_rec.debit_account;
3275 l_current_transaction_value := p_ae_txn_rec.primary_quantity * p_ae_txn_rec.unit_price;
3276 l_prior_transaction_value := p_ae_txn_rec.primary_quantity * p_ae_txn_rec.prior_unit_price;
3277 l_ae_line_rec.transaction_value := l_current_transaction_value - l_prior_transaction_value;
3278 IF( p_ae_txn_rec.event_type_id = 'PAC LC ADJ REC') THEN
3279 IF (l_ae_line_rec.transaction_value>=0) THEN
3280 l_ae_line_rec.ae_line_type := 5;
3281 ELSE
3282 l_ae_line_rec.ae_line_type := 38;
3283 END IF;
3284 ELSIF( p_ae_txn_rec.event_type_id = 'PAC LC ADJ DEL ASSET') THEN
3285 IF (l_ae_line_rec.transaction_value>=0) THEN
3286 l_ae_line_rec.ae_line_type := 38;
3287 ELSE
3288 l_ae_line_rec.ae_line_type := 5;
3289 END IF;
3290 ELSIF( p_ae_txn_rec.event_type_id = 'PAC LC ADJ DEL EXP') THEN
3291 IF (l_ae_line_rec.transaction_value>=0) THEN
3292 l_ae_line_rec.ae_line_type := 2;
3293 ELSE
3294 l_ae_line_rec.ae_line_type := 5;
3295 END IF;
3296 END IF;
3297
3298
3299 IF l_debug_flag = 'Y' THEN
3300 FND_FILE.PUT_LINE(FND_FILE.log, 'l_ae_line_rec.accounted_value: '||to_char(l_ae_line_rec.accounted_value));
3301 END IF;
3302
3303 l_ae_line_rec.source_table := 'RAE';
3304 l_ae_line_rec.source_id := p_ae_txn_rec.transaction_id;
3305
3306 IF p_ae_txn_rec.primary_quantity <> 0 THEN
3307 l_ae_line_rec.rate_or_amount := p_ae_txn_rec.unit_price * l_curr_rec.currency_conv_rate;
3308 ELSE
3309 l_ae_line_rec.rate_or_amount := 0 ;
3310 END IF;
3311
3312 l_ae_line_rec.po_distribution_id := p_ae_txn_rec.po_distribution_id;
3313
3314 l_stmt_num := 50;
3315
3316 CSTPAPPR.insert_account (p_ae_txn_rec,
3317 l_curr_rec,
3318 l_dr_flag,
3319 l_ae_line_rec,
3320 l_ae_line_tbl,
3321 l_err_rec);
3322
3323 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) THEN
3324 raise process_error;
3325 END IF;
3326
3327 l_stmt_num := 60;
3328
3329 l_dr_flag := NOT l_dr_flag;
3330
3331 l_ae_line_rec.account := p_ae_txn_rec.credit_account;
3332 IF( p_ae_txn_rec.event_type_id = 'PAC LC ADJ REC') THEN
3333 IF (l_ae_line_rec.transaction_value>=0) THEN
3334 l_ae_line_rec.ae_line_type := 38;
3335 ELSE
3336 l_ae_line_rec.ae_line_type := 5;
3337 END IF;
3338 ELSIF( p_ae_txn_rec.event_type_id = 'PAC LC ADJ DEL ASSET') THEN
3339 IF (l_ae_line_rec.transaction_value>=0) THEN
3340 l_ae_line_rec.ae_line_type := 5;
3341 ELSE
3342 l_ae_line_rec.ae_line_type := 38;
3343 END IF;
3344 ELSIF( p_ae_txn_rec.event_type_id = 'PAC LC ADJ DEL EXP') THEN
3345 IF (l_ae_line_rec.transaction_value>=0) THEN
3346 l_ae_line_rec.ae_line_type := 5;
3347 ELSE
3348 l_ae_line_rec.ae_line_type := 2;
3349 END IF;
3350 END IF;
3351
3352 IF l_debug_flag = 'Y' THEN
3353 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Credit Account: '||to_char(l_ae_line_rec.account));
3354 END IF;
3355
3356 l_stmt_num := 70;
3357
3358 CSTPAPPR.insert_account (p_ae_txn_rec,
3359 l_curr_rec,
3360 l_dr_flag,
3361 l_ae_line_rec,
3362 l_ae_line_tbl,
3363 l_err_rec);
3364
3365 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) THEN
3366 raise process_error;
3367 END IF;
3368
3369 -- Copy the local structure to the Output
3370 x_ae_line_rec_tbl := l_ae_line_tbl;
3371
3372 IF l_debug_flag = 'Y' THEN
3373 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Create_LC_Adj_Ae_Lines >>>');
3374 END IF;
3375
3376 EXCEPTION
3377 WHEN PROCESS_ERROR THEN
3378 x_ae_err_rec := l_err_rec;
3379 FND_FILE.PUT_LINE(FND_FILE.LOG, l_err_rec.l_err_msg);
3380
3381 WHEN OTHERS THEN
3382 l_err_rec.l_err_num := SQLCODE;
3383 l_err_rec.l_err_code := '';
3384 l_err_rec.l_err_msg := 'CSTPAPPR:( '||to_char(l_stmt_num)||' ): ' || to_char(SQLCODE);
3385 x_ae_err_rec := l_err_rec;
3386 FND_FILE.PUT_LINE(FND_FILE.LOG, l_err_rec.l_err_msg);
3387
3388 END create_lc_adj_ae_lines;
3389 /*============================================================================+
3390 | This procedure processes the transaction data and creates accounting entry |
3391 | lines in the form of PL/SQL table and returns to the main procedure. |
3392 | This procedure processes the period end accruals |
3393 | This is called during the period close process |
3394 |============================================================================*/
3395
3396 PROCEDURE create_per_end_ae_lines(
3397 i_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
3398 o_ae_line_rec_tbl OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
3399 o_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
3400 ) IS
3401 l_ae_line_tbl CSTPALTY.CST_AE_LINE_TBL_TYPE;
3402 l_ae_line_rec CSTPALTY.CST_AE_LINE_REC_TYPE;
3403 l_curr_rec CSTPALTY.cst_ae_curr_rec_type;
3404 l_err_rec CSTPALTY.cst_ae_err_rec_type;
3405
3406 l_doc_level VARCHAR2(1);
3407 l_doc_id NUMBER;
3408 l_dist_count NUMBER;
3409 l_purch_encumbrance_type_id NUMBER;
3410 l_purch_encumbrance_flag VARCHAR2(1);
3411 l_enc_flag VARCHAR2(1);
3412 l_bud_enc_flag VARCHAR2(1);
3413 l_dr_flag BOOLEAN;
3414
3415 l_stmt_num NUMBER;
3416
3417 l_po_uom_factor NUMBER;
3418 l_rcv_uom_factor NUMBER;
3419 l_period_end_date DATE;
3420 l_accrual_qty NUMBER;
3421 l_encum_qty NUMBER;
3422
3423 l_return_status VARCHAR2(1);
3424 l_msg_count NUMBER;
3425 l_msg_data VARCHAR2(240);
3426
3427 process_error EXCEPTION;
3428 CST_NO_PO_DIST EXCEPTION;
3429
3430 BEGIN
3431
3432 IF l_debug_flag = 'Y' THEN
3433 fnd_file.put_line(fnd_file.log,'Create_Per_End_Ae_Lines <<<');
3434 END IF;
3435
3436 -- Initialize API return status to success
3437 l_return_status := FND_API.G_RET_STS_SUCCESS;
3438
3439 l_ae_line_tbl := CSTPALTY.cst_ae_line_tbl_type();
3440
3441 -- Initialize local variables.
3442 -- ---------------------------
3443 l_err_rec.l_err_num := 0;
3444 l_err_rec.l_err_code := '';
3445 l_err_rec.l_err_msg := '';
3446
3447 -- Populate the Currency Record Type
3448 -- ---------------------------------
3449 l_stmt_num := 10;
3450
3451 select currency_code
3452 into l_curr_rec.pri_currency
3453 from gl_sets_of_books
3454 where set_of_books_id = i_ae_txn_rec.set_of_books_id;
3455
3456 l_curr_rec.alt_currency := i_ae_txn_rec.currency_code;
3457 l_curr_rec.currency_conv_date := i_ae_txn_rec.currency_conv_date;
3458 l_curr_rec.currency_conv_type := i_ae_txn_rec.currency_conv_type;
3459
3460 l_stmt_num := 30;
3461
3462 ------------------------------------------------------------
3463 -- A receipt can be at the shipment level or the distribution level:
3464 -- When receiving and delivering in one step, it is at the distribution level
3465 -- When receiving and delivering as two different steps, it is shipment level
3466 -- Get the document level as 'S' for shipment and 'D' for distribution
3467 ------------------------------------------------------------
3468 -- Get the level
3469
3470 l_stmt_num := 60;
3471
3472 SELECT
3473 decode(rt.po_distribution_id, NULL, 'S', 'D'),
3474 nvl(rt.po_distribution_id, rt.po_line_location_id)
3475 INTO
3476 l_doc_level,
3477 l_doc_id
3478 FROM
3479 rcv_transactions rt
3480 WHERE
3481 rt.transaction_id = i_ae_txn_rec.transaction_id;
3482
3483 IF l_debug_flag = 'Y' THEN
3484 fnd_file.put_line(fnd_file.log,'Document Level: '||l_doc_level);
3485 fnd_file.put_line(fnd_file.log,'Document ID: '||to_char(l_doc_id));
3486 fnd_file.put_line(fnd_file.log,'Quantity: '||to_char(i_ae_txn_rec.primary_quantity));
3487 END IF;
3488
3489 SELECT
3490 count(*)
3491 into
3492 l_dist_count
3493 FROM
3494 po_distributions_all
3495 WHERE
3496 (
3497 (l_doc_level = 'D' AND po_distribution_id = l_doc_id)
3498 OR (l_doc_level = 'S' AND line_location_id = l_doc_id)
3499 )
3500 AND rownum <= 1;
3501
3502 IF (l_dist_count = 0) THEN
3503 IF l_debug_flag = 'Y' THEN
3504 fnd_file.put_line(fnd_file.log,'Error: No distributions for the Document: '||to_char(l_doc_id));
3505 END IF;
3506 RAISE CST_NO_PO_DIST;
3507 END IF;
3508
3509 ------------------------------------------------------------------
3510 -- If the document level is Shipment, get all the distributions
3511 -- for the Shipment, against which the receipt occurred.
3512 -- If the document level is Distribution, get the distribution
3513 -- Loop for each distribution that is NOT accrued at receipt
3514 ------------------------------------------------------------------
3515
3516 DECLARE
3517 ------------------------------------------------------------------
3518 -- Complex Work Procurement changes
3519 -- Whether the shipment is quantity based or amount based, is determined
3520 -- by poll.matching_basis = 'QUANTITY' or poll.matching_basis = 'AMOUNT'
3521 ------------------------------------------------------------------
3522 CURSOR c_receive_dists IS
3523 SELECT
3524 decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_rate,nvl(pod.rate,1)) "EXCHG_RATE",
3525 decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_date,pod.rate_date) "EXCHG_DATE",
3526 DECODE (poll.matching_basis, 'AMOUNT', 1, 0) "SERVICE_FLAG",
3527 POD.po_distribution_id "PO_DISTRIBUTION_ID",
3528 nvl(POD.rate,1) "PO_RATE",
3529 pod.rate_date "PO_DATE",
3530 POLL.line_location_id "PO_LINE_LOCATION_ID",
3531 POD.code_combination_id "EXPENSE_ACCOUNT_ID",
3532 POD.destination_type_code "DESTINATION_TYPE_CODE",
3533 RP.receiving_account_id "RECEIVING_ACCOUNT_ID",
3534 POD.accrual_account_id "ACCRUAL_ACCOUNT_ID",
3535 nvl(POD.budget_account_id,-1) "ENCUMBRANCE_ACCOUNT_ID",
3536 decode(poll.matching_basis,
3537 'AMOUNT', poll.amount - NVL(poll.amount_cancelled,0),
3538 poll.quantity - NVL(poll.quantity_cancelled,0)) "SHIPMENT_QUANTITY",
3539 decode(poll.matching_basis,
3540 'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
3541 pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) "DIST_QUANTITY",
3542 decode(poll.matching_basis,
3543 'AMOUNT', 1,
3544 NVL(poll.price_override, pol.unit_price)) +
3545 (po_tax_sv.get_tax( 'PO', pod.po_distribution_id) /
3546 decode(poll.matching_basis,
3547 'AMOUNT', pod.amount_ordered,
3548 pod.quantity_ordered)) "UNIT_PRICE",
3549 NVL(poll.match_option,'P') "MATCH_OPTION"
3550 FROM
3551 po_distributions_all pod,
3552 po_line_locations_all poll,
3553 po_lines_all pol,
3554 rcv_parameters rp
3555 WHERE
3556 (
3557 (l_doc_level = 'D' AND pod.po_distribution_id = l_doc_id)
3558 OR (l_doc_level = 'S' AND poll.line_location_id = l_doc_id)
3559 )
3560 and pod.line_location_id = poll.line_location_id
3561 and poll.po_line_id = pol.po_line_id
3562 and rp.organization_id = pod.destination_organization_id
3563 and pod.destination_type_code = 'EXPENSE'
3564 and nvl(POLL.accrue_on_receipt_flag,'N') = 'N'
3565 and nvl(POD.accrue_on_receipt_flag,'N') = 'N'
3566 /*BUG 8302671
3567 Only accrue if Ordered Quantity - Cancelled Quantity is > 0 */
3568 and DECODE (poll.matching_basis,
3569 'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
3570 pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) > 0
3571 AND DECODE(poll.matching_basis,
3572 'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
3573 poll.quantity - NVL(poll.quantity_cancelled,0)) > 0;
3574
3575 BEGIN
3576 l_stmt_num := 70;
3577
3578 FOR c_receipts_rec IN c_receive_dists LOOP
3579
3580 l_ae_line_rec.actual_flag := NULL;
3581 l_ae_line_rec.po_distribution_id := c_receipts_rec.po_distribution_id;
3582
3583 l_period_end_date := i_ae_txn_rec.accounting_date + 0.99999;
3584
3585 -------------------------------------------------------------------
3586 -- Period End Accrual rewrite changes
3587 -- The procedure CST_PerEndAccruals_PVT.Calculate_AccrualAmount
3588 -- returns the accrual_amount and encum_amount along with quantity_received
3589 -- and quantity_invoiced against the po_distribution_id.
3590 -------------------------------------------------------------------
3591 l_stmt_num := 80;
3592 CST_PerEndAccruals_PVT.Calculate_AccrualAmount(
3593 p_api_version => 1.0,
3594 p_init_msg_list => FND_API.G_FALSE,
3595 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3596 x_return_status => l_return_status,
3597 x_msg_count => l_msg_count,
3598 x_msg_data => l_msg_data,
3599 p_match_option => c_receipts_rec.match_option,
3600 p_distribution_id => c_receipts_rec.po_distribution_id,
3601 p_shipment_id => c_receipts_rec.po_line_location_id,
3602 p_transaction_id => i_ae_txn_rec.transaction_id,
3603 p_service_flag => c_receipts_rec.service_flag,
3604 p_dist_qty => c_receipts_rec.dist_quantity,
3605 p_shipment_qty => c_receipts_rec.shipment_quantity,
3606 p_end_date => l_period_end_date,
3607 x_accrual_qty => l_accrual_qty,
3608 x_encum_qty => l_encum_qty
3609 );
3610
3611 -- If return status is not success, raise exception
3612 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
3613 l_err_rec.l_err_num := 20001 ;
3614 l_err_rec.l_err_msg := l_msg_data;
3615 raise process_error;
3616 END IF;
3617
3618
3619 l_ae_line_rec.transaction_value := l_accrual_qty * c_receipts_rec.unit_price;
3620 l_ae_line_rec.rate_or_amount := c_receipts_rec.unit_price * c_receipts_rec.exchg_rate;
3621
3622 l_curr_rec.currency_conv_rate := c_receipts_rec.exchg_rate;
3623 l_curr_rec.currency_conv_date := c_receipts_rec.exchg_date;
3624 l_curr_rec.currency_conv_type := i_ae_txn_rec.currency_conv_type;
3625
3626 l_dr_flag := TRUE;
3627
3628 l_ae_line_rec.account := c_receipts_rec.expense_account_id;
3629 l_ae_line_rec.ae_line_type := 20;
3630 IF l_debug_flag = 'Y' THEN
3631 IF (l_dr_flag) THEN
3632 fnd_file.put_line(fnd_file.log,'DR Expense: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
3633 ELSE
3634 fnd_file.put_line(fnd_file.log,'CR Expense: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
3635 END IF;
3636 END IF;
3637
3638 CSTPAPPR.insert_account (i_ae_txn_rec,
3639 l_curr_rec,
3640 l_dr_flag,
3641 l_ae_line_rec,
3642 l_ae_line_tbl,
3643 l_err_rec);
3644 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
3645 THEN
3646 raise process_error;
3647 END IF;
3648
3649 l_dr_flag := not l_dr_flag;
3650
3651 l_ae_line_rec.account := c_receipts_rec.accrual_account_id;
3652 l_ae_line_rec.ae_line_type := 16;
3653
3654 IF l_debug_flag = 'Y' THEN
3655 IF (l_dr_flag) THEN
3656 fnd_file.put_line(fnd_file.log,'DR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
3657 ELSE
3658 fnd_file.put_line(fnd_file.log,'CR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
3659 END IF;
3660 END IF;
3661 CSTPAPPR.insert_account (i_ae_txn_rec,
3662 l_curr_rec,
3663 l_dr_flag,
3664 l_ae_line_rec,
3665 l_ae_line_tbl,
3666 l_err_rec);
3667 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
3668 THEN
3669 raise process_error;
3670 END IF;
3671
3672
3673 -- Take care of rounding errors.
3674 -- -----------------------------
3675 l_stmt_num := 80;
3676 balance_account (l_ae_line_tbl,
3677 l_err_rec);
3678
3679 -- check error
3680 if (l_err_rec.l_err_num <> 0) then
3681 raise process_error;
3682 end if;
3683
3684 -- encumbrance
3685 l_stmt_num := 110;
3686
3687 -- If the budget account was not specified at PO creation
3688 -- no encumbrance reversal is necessary
3689
3690 IF (c_receipts_rec.encumbrance_account_id = -1) THEN
3691 IF l_debug_flag = 'Y' THEN
3692 fnd_file.put_line(fnd_file.log,'No Encumbrance account at PO level');
3693 END IF;
3694 ELSE
3695
3696 CSTPAPPR.check_encumbrance(
3697 i_transaction_id => i_ae_txn_rec.transaction_id,
3698 i_set_of_books_id => i_ae_txn_rec.set_of_books_id,
3699 i_period_name => i_ae_txn_rec.accounting_period_name,
3700 i_encumbrance_account_id => c_receipts_rec.encumbrance_account_id,
3701 o_enc_flag => l_enc_flag,
3702 o_purch_encumbrance_type_id => l_purch_encumbrance_type_id,
3703 o_purch_encumbrance_flag => l_purch_encumbrance_flag,
3704 o_ae_err_rec => l_err_rec);
3705 if (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
3706 then
3707 raise process_error;
3708 end if;
3709
3710 /* Commented and replaced for forward port bug 5768550
3711 IF (l_enc_flag = 'Y' and l_purch_encumbrance_flag = 'Y') then*/
3712 IF l_purch_encumbrance_flag = 'Y' THEN
3713
3714 l_ae_line_rec.transaction_value := l_encum_qty * c_receipts_rec.unit_price;
3715 l_curr_rec.currency_conv_rate := c_receipts_rec.po_rate;
3716 l_curr_rec.currency_conv_date := c_receipts_rec.po_date;
3717
3718 l_dr_flag := FALSE;
3719
3720 l_ae_line_rec.account := c_receipts_rec.encumbrance_account_id;
3721 l_ae_line_rec.ae_line_type := 15;
3722 l_ae_line_rec.actual_flag := 'E';
3723 l_ae_line_rec.encum_type_id := l_purch_encumbrance_type_id;
3724
3725 IF l_debug_flag = 'Y' THEN
3726 IF (l_dr_flag) THEN
3727 fnd_file.put_line(fnd_file.log,'DR Encumbrance: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
3728 ELSE
3729 fnd_file.put_line(fnd_file.log,'CR Encumbrance: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
3730 END IF;
3731 END IF;
3732 CSTPAPPR.insert_account (i_ae_txn_rec,
3733 l_curr_rec,
3734 l_dr_flag,
3735 l_ae_line_rec,
3736 l_ae_line_tbl,
3737 l_err_rec);
3738 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
3739 THEN
3740 raise process_error;
3741 END IF;
3742 END IF; /*if enc on*/
3743 END IF; /*if budget acct specified */
3744 END LOOP;
3745 EXCEPTION
3746 when process_error then
3747 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
3748 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
3749 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
3750
3751 END;
3752
3753
3754 -- Return the lines pl/sql table.
3755 -- ------------------------------
3756 l_stmt_num := 90;
3757 o_ae_line_rec_tbl := l_ae_line_tbl;
3758 IF l_debug_flag = 'Y' THEN
3759 fnd_file.put_line(fnd_file.log,'Create_Per_End_Ae_Lines >>>');
3760 END IF;
3761
3762 EXCEPTION
3763
3764 when process_error then
3765 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
3766 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
3767 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
3768
3769 when cst_no_po_dist then
3770 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
3771 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
3772 FND_MESSAGE.set_name('BOM', 'CST_NO_RCV_LINE');
3773 o_ae_err_rec.l_err_msg := FND_MESSAGE.Get;
3774
3775 when others then
3776 o_ae_err_rec.l_err_num := SQLCODE;
3777 o_ae_err_rec.l_err_code := '';
3778 o_ae_err_rec.l_err_msg := 'CSTPAPPR.create_per_end_ae_lines : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
3779
3780 END create_per_end_ae_lines;
3781
3782 PROCEDURE check_encumbrance(
3783 i_transaction_id IN NUMBER,
3784 i_set_of_books_id IN NUMBER,
3785 i_period_name IN VARCHAR2, --???
3786 i_encumbrance_account_id IN NUMBER,
3787 o_enc_flag OUT NOCOPY VARCHAR2,
3788 o_purch_encumbrance_type_id OUT NOCOPY NUMBER,
3789 o_purch_encumbrance_flag OUT NOCOPY VARCHAR2,
3790 o_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
3791 )
3792 IS
3793 l_application_id NUMBER;
3794 l_functional_currency_code VARCHAR2(5);
3795 l_accrual_effect_date DATE;
3796 l_accrual_cutoff_date DATE;
3797 l_bud_enc_flag VARCHAR2(1);
3798 l_stmt_num NUMBER;
3799 l_operating_unit NUMBER;
3800 cst_no_enc_account exception;
3801
3802 BEGIN
3803 IF l_debug_flag = 'Y' THEN
3804 fnd_file.put_line(fnd_file.log,'Check_Encumbrance <<<');
3805 fnd_file.put_line(fnd_file.log,'Encumbrance Account: '||to_char(i_encumbrance_account_id));
3806 END IF;
3807
3808 l_stmt_num := 10;
3809 SELECT
3810 decode(status,'I',101,201)
3811 INTO
3812 l_application_id
3813 FROM
3814 fnd_product_installations
3815 WHERE
3816 application_id = 101;
3817
3818 l_stmt_num := 20;
3819
3820
3821 SELECT
3822 NVL(org_id,-1)
3823 into
3824 l_operating_unit
3825 FROM
3826 po_headers_all
3827 WHERE
3828 po_header_id = (select po_header_id from rcv_transactions
3829 where transaction_id = i_transaction_id);
3830
3831
3832 l_stmt_num := 30;
3833 SELECT
3834 SOB.currency_code,
3835 nvl(FSP.purch_encumbrance_flag, 'N'),
3836 nvl(GET.encumbrance_type_id, 0)
3837 INTO
3838 l_functional_currency_code,
3839 o_purch_encumbrance_flag,
3840 o_purch_encumbrance_type_id
3841 FROM
3842 GL_PERIOD_STATUSES ACR,
3843 GL_PERIOD_TYPES GLPT,
3844 FINANCIALS_SYSTEM_PARAMS_ALL FSP,
3845 GL_ENCUMBRANCE_TYPES GET,
3846 GL_SETS_OF_BOOKS SOB
3847 WHERE
3848 GLPT.period_type = ACR.period_type AND
3849 ACR.application_id = l_application_id AND
3850 ACR.set_of_books_id = i_set_of_books_id AND
3851 ACR.period_name = i_period_name AND
3852 FSP.set_of_books_id = SOB.set_of_books_id AND
3853 NVL(FSP.org_id,-1) = l_operating_unit AND
3854 GET.encumbrance_type_key = 'Obligation';
3855
3856 /*
3857 Bug 5768550(FP of bug 5722537):- The following code is commented.
3858 l_stmt_num := 40;
3859
3860 IF (o_purch_encumbrance_flag = 'Y') THEN
3861 -- Get profile CREATE_BUDGETARY_ENCUMBRANCES
3862 FND_PROFILE.get('CREATE_BUDGETARY_ENCUMBRANCES', l_bud_enc_flag);
3863 o_enc_flag := nvl(l_bud_enc_flag,'N');
3864 END IF;
3865
3866 IF (o_enc_flag = 'Y' and o_purch_encumbrance_flag = 'Y' and i_encumbrance_account_id = -1) THEN
3867 raise cst_no_enc_account;
3868 END IF;
3869 */
3870
3871 IF l_debug_flag = 'Y' THEN
3872 fnd_file.put_line(fnd_file.log,'Check_Encumbrance >>>');
3873 END IF;
3874 EXCEPTION
3875 WHEN CST_NO_ENC_ACCOUNT THEN
3876 o_ae_err_rec.l_err_num := 30010;
3877 o_ae_err_rec.l_err_code := 'CST_NO_ENC_ACCOUNT';
3878 FND_MESSAGE.set_name('BOM', 'CST_NO_ENC_ACCOUNT');
3879 o_ae_err_rec.l_err_msg := FND_MESSAGE.Get;
3880
3881 WHEN OTHERS THEN
3882 o_ae_err_rec.l_err_num := SQLCODE;
3883 o_ae_err_rec.l_err_code := '';
3884 o_ae_err_rec.l_err_msg := 'CSTPAPPR.check_encumbrance : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
3885
3886
3887 END check_encumbrance;
3888
3889 -- ===================================================================
3890 -- Insert Account.
3891 -- ===================================================================
3892 PROCEDURE insert_account(
3893 i_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
3894 i_ae_curr_rec IN CSTPALTY.cst_ae_curr_rec_type,
3895 i_dr_flag IN BOOLEAN,
3896 i_ae_line_rec IN CSTPALTY.cst_ae_line_rec_type,
3897 l_ae_line_tbl IN OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
3898 o_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type)
3899 IS
3900 l_err_rec CSTPALTY.cst_ae_err_rec_type;
3901 l_entered_value NUMBER;
3902 l_accounted_value NUMBER;
3903 l_stmt_num NUMBER;
3904 next_record_avail NUMBER;
3905 invalid_acct_error EXCEPTION;
3906
3907 BEGIN
3908
3909 IF l_debug_flag = 'Y' THEN
3910 fnd_file.put_line(fnd_file.log,'Insert_Account <<< ');
3911 END IF;
3912 -- Initialize variables.
3913 -- ---------------------
3914 l_err_rec.l_err_num := 0;
3915 l_err_rec.l_err_code := '';
3916 l_err_rec.l_err_msg := '';
3917
3918 if (i_ae_line_rec.account = -1) then
3919 raise invalid_acct_error;
3920 end if;
3921
3922 l_stmt_num := 10;
3923 next_record_avail := nvl(l_ae_line_tbl.LAST,0) ;
3924 l_ae_line_tbl.extend;
3925 next_record_avail := nvl(l_ae_line_tbl.LAST,0) ;
3926 l_ae_line_tbl(next_record_avail).ae_line_type :=
3927 i_ae_line_rec.ae_line_type;
3928
3929 l_stmt_num := 20;
3930
3931 select meaning
3932 into l_ae_line_tbl(next_record_avail).description
3933 from mfg_lookups
3934 where lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
3935 and lookup_code = l_ae_line_tbl(next_record_avail).ae_line_type;
3936
3937 l_ae_line_tbl(next_record_avail).account := i_ae_line_rec.account;
3938
3939 l_stmt_num := 30;
3940
3941 IF l_debug_flag = 'Y' THEN
3942 fnd_file.put_line(fnd_file.log,'Alt Currency : '||i_ae_curr_rec.alt_currency);
3943 fnd_file.put_line(fnd_file.log,'Pri Currency : '||i_ae_curr_rec.pri_currency);
3944 fnd_file.put_line(fnd_file.log,'Currency Rate: '||to_char(i_ae_curr_rec.currency_conv_rate));
3945 END IF;
3946
3947 select
3948 nvl(i_ae_curr_rec.alt_currency,i_ae_curr_rec.pri_currency)
3949 into l_ae_line_tbl(next_record_avail).currency_code
3950 from dual;
3951
3952 l_stmt_num := 40;
3953 select
3954 decode(i_ae_curr_rec.alt_currency,
3955 i_ae_curr_rec.pri_currency,NULL,
3956 i_ae_curr_rec.currency_conv_date)
3957 into l_ae_line_tbl(next_record_avail).currency_conv_date
3958 from dual;
3959
3960 l_stmt_num := 50;
3961 select
3962 decode(i_ae_curr_rec.alt_currency,
3963 i_ae_curr_rec.pri_currency,1,
3964 decode(i_ae_curr_rec.currency_conv_rate,-1,1,i_ae_curr_rec.currency_conv_rate))
3965 into l_ae_line_tbl(next_record_avail).currency_conv_rate
3966 from dual;
3967
3968 l_stmt_num := 60;
3969 select
3970 decode(i_ae_curr_rec.alt_currency,
3971 i_ae_curr_rec.pri_currency,NULL,
3972 i_ae_curr_rec.currency_conv_type)
3973 into l_ae_line_tbl(next_record_avail).currency_conv_type
3974 from dual;
3975
3976
3977 l_stmt_num := 70;
3978
3979 -- For Adjust Events, the accounted and entered values are computed by the calling routine,
3980 -- Create_Adj_Ae_Lines
3981
3982 IF (i_ae_txn_rec.txn_type_flag = 'ADJ') THEN
3983 l_entered_value := i_ae_line_rec.entered_value;
3984 l_accounted_value := i_ae_line_rec.accounted_value;
3985
3986 ELSE
3987 select decode(i_ae_curr_rec.alt_currency,NULL, NULL,
3988 i_ae_curr_rec.pri_currency, NULL,
3989 decode(c2.minimum_accountable_unit,
3990 NULL,
3991 round(i_ae_line_rec.transaction_value, c2.precision),
3992 round(i_ae_line_rec.transaction_value /c2.minimum_accountable_unit)
3993 * c2.minimum_accountable_unit )),
3994 decode(c1.minimum_accountable_unit,
3995 NULL, round(i_ae_line_rec.transaction_value * i_ae_curr_rec.currency_conv_rate, c1.precision),
3996 round(i_ae_line_rec.transaction_value * i_ae_curr_rec.currency_conv_rate/c1.minimum_accountable_unit)
3997 * c1.minimum_accountable_unit )
3998 into
3999 l_entered_value,
4000 l_accounted_value
4001 from
4002 fnd_currencies c1,
4003 fnd_currencies c2
4004 where
4005 c1.currency_code = i_ae_curr_rec.pri_currency
4006 and c2.currency_code = decode(i_ae_curr_rec.alt_currency, NULL,
4007 i_ae_curr_rec.pri_currency,
4008 i_ae_curr_rec.alt_currency);
4009
4010 END IF;
4011
4012 IF l_debug_flag = 'Y' THEN
4013 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Accounted_Value: '||to_char(l_accounted_value));
4014 END IF;
4015
4016 -- ERV does not have an entered amount.
4017 if (l_ae_line_tbl(next_record_avail).ae_line_type = 18) then
4018 l_entered_value := 0;
4019 end if;
4020
4021 /* 9166177: FP of Bug 9155316
4022 In case of retroprice adjustment in order to have minimum accountign accounting impact
4023 we follow TUMB rule that "Dr Retroprice account and CR accrual account".Hence
4024 there is possiblity that Accounted Dr can have -ive value.As a result while creating
4025 adjustmentent entries for Retroprice abs values should not be inserted.
4026
4027 13994203: Now PAC can have signed value, so no need to divide to ADJ or not ADJ. all can have signed value.
4028 */
4029 if (i_dr_flag) then
4030 l_ae_line_tbl(next_record_avail).entered_dr := nvl(l_entered_value,l_accounted_value);
4031 l_ae_line_tbl(next_record_avail).entered_cr := NULL;
4032 l_ae_line_tbl(next_record_avail).accounted_dr := l_accounted_value;
4033 l_ae_line_tbl(next_record_avail).accounted_cr := NULL;
4034 else
4035 l_ae_line_tbl(next_record_avail).entered_cr := nvl(l_entered_value,l_accounted_value);
4036 l_ae_line_tbl(next_record_avail).entered_dr := NULL;
4037 l_ae_line_tbl(next_record_avail).accounted_cr := l_accounted_value;
4038 l_ae_line_tbl(next_record_avail).accounted_dr := NULL;
4039 end if;
4040
4041 /*9166177 :Changes for bug 9155316 ends */
4042
4043 l_ae_line_tbl(next_record_avail).source_table := i_ae_txn_rec.source_table;
4044 l_ae_line_tbl(next_record_avail).source_id := i_ae_txn_rec.transaction_id;
4045
4046 /* Bug 2686598. Rate_or_amount now calculated in calling function */
4047 l_ae_line_tbl(next_record_avail).rate_or_amount := i_ae_line_rec.rate_or_amount;
4048
4049 l_ae_line_tbl(next_record_avail).basis_type := to_number(null);
4050 l_ae_line_tbl(next_record_avail).resource_id := null;
4051 l_ae_line_tbl(next_record_avail).cost_element_id := null;
4052 l_ae_line_tbl(next_record_avail).activity_id := NULL;
4053 l_ae_line_tbl(next_record_avail).repetitive_schedule_id := NULL;
4054 l_ae_line_tbl(next_record_avail).overhead_basis_factor := NULL;
4055 l_ae_line_tbl(next_record_avail).basis_resource_id := NULL;
4056 l_ae_line_tbl(next_record_avail).actual_flag := i_ae_line_rec.actual_flag;
4057 l_ae_line_tbl(next_record_avail).encum_type_id := i_ae_line_rec.encum_type_id;
4058 l_ae_line_tbl(next_record_avail).po_distribution_id := i_ae_line_rec.po_distribution_id;
4059
4060 l_ae_line_tbl(next_record_avail).reference1 := NULL;
4061 l_ae_line_tbl(next_record_avail).reference2 := i_ae_txn_rec.organization_id;
4062 l_ae_line_tbl(next_record_avail).reference3 := i_ae_txn_rec.transaction_id;
4063 l_ae_line_tbl(next_record_avail).reference4 := NULL;
4064 l_ae_line_tbl(next_record_avail).reference5 := NULL;
4065 l_ae_line_tbl(next_record_avail).reference6 := NULL;
4066 l_ae_line_tbl(next_record_avail).reference7 := NULL;
4067 l_ae_line_tbl(next_record_avail).reference8 := NULL;
4068 l_ae_line_tbl(next_record_avail).reference9 := NULL;
4069 l_ae_line_tbl(next_record_avail).reference10 := NULL;
4070
4071 IF l_debug_flag = 'Y' THEN
4072 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of Records: '||to_char(l_ae_line_tbl.COUNT));
4073 fnd_file.put_line(fnd_file.log,'Insert_Account >>> ');
4074 END IF;
4075
4076 EXCEPTION
4077
4078 when invalid_acct_error then
4079 o_ae_err_rec.l_err_num := 9999;
4080 o_ae_err_rec.l_err_code := 'CST_NO_TXN_INVALID_ACCOUNT';
4081 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_ACCOUNT');
4082 o_ae_err_rec.l_err_msg := FND_MESSAGE.Get;
4083
4084 when others then
4085 o_ae_err_rec.l_err_num := SQLCODE;
4086 o_ae_err_rec.l_err_code := '';
4087 o_ae_err_rec.l_err_msg := 'CSTPAPPR.insert_account' || to_char(l_stmt_num) ||
4088 substr(SQLERRM,1,180);
4089
4090 end insert_account;
4091
4092 FUNCTION get_net_del_qty(
4093 i_po_distribution_id IN NUMBER,
4094 i_transaction_id IN NUMBER)
4095 RETURN NUMBER
4096 IS
4097 ----------------------------------------------------
4098 -- Get all child transactions level by level
4099 -----------------------------------------------------
4100 CURSOR c_nqd is
4101 SELECT
4102 rt4.transaction_id,
4103 rt4.transaction_type,
4104 -- J Changes -------------------------------------------------------------
4105 -- Bug 3588765 --
4106 -- DECODE(POL.ORDER_TYPE_LOOKUP_CODE,
4107 -- 'RATE', rt4.AMOUNT,
4108 -- 'FIXED PRICE', rt4.AMOUNT,
4109 -- RT4.PRIMARY_QUANTITY) "PRIMARY_QUANTITY",
4110 -- End of Bug 3588765
4111 ----------------------------------------------------------------------------
4112 rt4.parent_transaction_id
4113 FROM
4114 rcv_transactions rt4
4115 -- J Changes -------------------------------------------------------------
4116 -- Bug 3588765 --
4117 -- PO_LINES_ALL POL
4118 -- End of Bug 3588765
4119 --------------------------------------------------------------------------
4120 WHERE
4121 rt4.transaction_id < i_transaction_id
4122 -- J Changes -------------------------------------------------------------
4123 -- Bug 3588765 --
4124 AND EXISTS (SELECT 1 FROM PO_LINES_ALL POL WHERE RT4.PO_LINE_ID= POL.PO_LINE_ID)
4125 -- AND RT4.PO_LINE_ID = POL.PO_LINE_ID
4126 -- End of Bug 3588765
4127 --------------------------------------------------------------------------
4128 START WITH
4129 rt4.po_distribution_id = i_po_distribution_id
4130 and transaction_type = 'DELIVER'
4131 CONNECT BY
4132 prior rt4.transaction_id = rt4.parent_transaction_id
4133 AND rt4.po_line_location_id = PRIOR rt4.po_line_location_id; -- Change for the bug 4968702
4134
4135 l_nqd NUMBER := 0;
4136 l_parent_type rcv_transactions.transaction_type%TYPE;
4137 l_stmt_num NUMBER := 0;
4138 -- Bug 3588765 --
4139 l_primary_quantity NUMBER := 0;
4140 -- End of Bug 3588765
4141 BEGIN
4142 ---------------------------------------------------------
4143 -- Initialize error variable
4144 ---------------------------------------------------------
4145 ---------------------------------------------------------
4146 -- For each child transaction loop
4147 --------------------------------------------------------
4148 FOR c_nqd_rec in c_nqd loop
4149
4150 -- Bug 3588765 -----------------------------
4151 l_stmt_num := 20;
4152 SELECT DECODE(POLL1.MATCHING_BASIS, -- Changed for Complex work procurement
4153 'AMOUNT', rt6.AMOUNT,
4154 'QUANTITY', RT6.PRIMARY_QUANTITY) "PRIMARY_QUANTITY"
4155 INTO l_primary_quantity
4156 FROM rcv_transactions rt6,
4157 PO_LINE_LOCATIONS_ALL POLL1 -- Changed for Complex work procurement
4158 WHERE rt6.transaction_id=c_nqd_rec.transaction_id
4159 AND RT6.PO_LINE_LOCATION_ID= POLL1.LINE_LOCATION_ID; -- Changed for Complex work procurement
4160 -- End of Bug 3588765 ----------------------
4161
4162 --------------------------------------------------------
4163 -- If it is not the parent (that was passed in) transaction itself
4164 --------------------------------------------------------
4165 IF c_nqd_rec.transaction_type <> 'DELIVER' THEN
4166 l_stmt_num := 10;
4167 ----------------------------------------------------------
4168 -- Get the parent transaction type
4169 ----------------------------------------------------------
4170 SELECT
4171 rt5.transaction_type
4172 INTO
4173 l_parent_type
4174 FROM
4175 rcv_transactions rt5
4176 WHERE
4177 rt5.transaction_id = c_nqd_rec.parent_transaction_id;
4178 END IF;
4179 ------------------------------------------------------------
4180 -- If it is the parent receive or match transaction
4181 -- then add the quantity to l_nqd
4182 ------------------------------------------------------------
4183 IF c_nqd_rec.transaction_type = 'DELIVER' THEN
4184 l_nqd := l_nqd + l_primary_quantity;
4185 -----------------------------------------------------------
4186 -- If the transaction is CORRECT,
4187 -- If parent is receive or match txn, then add the corrected qty
4188 -- If parent is return, then subtract the corrected qty
4189 -----------------------------------------------------------
4190 ELSIF c_nqd_rec.transaction_type = 'CORRECT' then
4191 IF l_parent_type = 'DELIVER' then
4192 l_nqd := l_nqd + l_primary_quantity;
4193 ELSIF l_parent_type = 'RETURN TO RECEIVING' then
4194 l_nqd := l_nqd - l_primary_quantity;
4195 END IF;
4196 ----------------------------------------------------------
4197 -- If transaction is return transaction, then subtract returned qty
4198 ----------------------------------------------------------
4199 ELSIF c_nqd_rec.transaction_type = 'RETURN TO RECEIVING' then
4200 l_nqd := l_nqd - l_primary_quantity;
4201 END IF;
4202
4203 END LOOP; -- child txns loop
4204 --------------------------------------------------------
4205 -- Return the net quantity received as calculated
4206 --------------------------------------------------------
4207 RETURN (l_nqd);
4208 EXCEPTION
4209 WHEN OTHERS THEN
4210 RETURN(NULL);
4211 END get_net_del_qty;
4212
4213
4214
4215
4216 -- ===================================================================
4217 -- Balance Account.
4218 -- ===================================================================
4219 procedure balance_account (
4220 l_ae_line_tbl IN OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
4221 o_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type)
4222 IS
4223 l_ent_value NUMBER := 0;
4224 l_acc_value NUMBER := 0;
4225 l_last_rec NUMBER;
4226 l_stmt_num NUMBER;
4227 l_ipv_line_flag NUMBER := 0; /*to find out if there is ipv line */
4228 BEGIN
4229 IF l_debug_flag = 'Y' THEN
4230 fnd_file.put_line(fnd_file.log,'Balance_Account <<<');
4231 END IF;
4232 if (l_ae_line_tbl.exists(1)) then
4233 l_stmt_num := 10;
4234 For i in l_ae_line_tbl.FIRST .. l_ae_line_tbl.LAST loop
4235 IF (l_ae_line_tbl(i).actual_flag = 'E') THEN
4236 null;
4237 else
4238 if (l_ae_line_tbl(i).ae_line_type = 17) then
4239 l_ipv_line_flag := 1; /* indicates that ipv line does exist */
4240 end if;
4241 l_ent_value := l_ent_value + nvl(l_ae_line_tbl(i).entered_dr,0) - nvl(l_ae_line_tbl(i).entered_cr,0);
4242 l_acc_value := l_acc_value + nvl(l_ae_line_tbl(i).accounted_dr,0) - nvl(l_ae_line_tbl(i).accounted_cr,0);
4243 END IF;
4244 end loop;
4245
4246 if (l_ent_value = 0 and l_acc_value = 0) then
4247 return;
4248 end if;
4249
4250 l_stmt_num := 20;
4251 l_last_rec := l_ae_line_tbl.LAST;
4252
4253
4254 -- Any rounding errors should be balanced out and put to the IPV account
4255 -- Bug 930582 workaround : any discrepancies with regard to accrual not including tax
4256 -- should be put into ipv.
4257
4258 while true
4259 loop
4260 if (l_ae_line_tbl(l_last_rec).actual_flag = 'E') OR
4261 (l_ipv_line_flag = 1 and l_ae_line_tbl(l_last_rec).ae_line_type <> 17) then
4262 l_last_rec := l_last_rec - 1;
4263 else
4264 exit;
4265 end if;
4266 end loop;
4267
4268
4269 if l_ae_line_tbl(l_last_rec).accounted_dr is not NULL then
4270 IF l_debug_flag = 'Y' THEN
4271 fnd_file.put_line(fnd_file.log,'Balancing Dr: '||to_char(l_ae_line_tbl(l_last_rec).entered_dr));
4272 END IF;
4273 l_ae_line_tbl(l_last_rec).accounted_dr :=
4274 l_ae_line_tbl(l_last_rec).accounted_dr - l_acc_value;
4275 l_ae_line_tbl(l_last_rec).entered_dr :=
4276 l_ae_line_tbl(l_last_rec).entered_dr - l_ent_value;
4277 elsif l_ae_line_tbl(l_last_rec).accounted_cr is not NULL then
4278 IF l_debug_flag = 'Y' THEN
4279 fnd_file.put_line(fnd_file.log,'Balancing Cr: '||to_char(l_ae_line_tbl(l_last_rec).entered_cr));
4280 END IF;
4281 l_ae_line_tbl(l_last_rec).accounted_cr :=
4282 l_ae_line_tbl(l_last_rec).accounted_cr + l_acc_value;
4283 l_ae_line_tbl(l_last_rec).entered_cr :=
4284 l_ae_line_tbl(l_last_rec).entered_cr + l_ent_value;
4285 end if;
4286
4287 end if;
4288 IF l_debug_flag = 'Y' THEN
4289 fnd_file.put_line(fnd_file.log,'Balance_Account >>>');
4290 END IF;
4291
4292 EXCEPTION
4293
4294 when others then
4295 o_ae_err_rec.l_err_num := SQLCODE;
4296 o_ae_err_rec.l_err_code := '';
4297 o_ae_err_rec.l_err_msg := 'CSTPAPPR.balance_account' || to_char(l_stmt_num) ||
4298 substr(SQLERRM,1,180);
4299
4300 END balance_account;
4301
4302 end CSTPAPPR;