[Home] [Help]
PACKAGE BODY: APPS.CSTPAPPR
Source
1 PACKAGE BODY CSTPAPPR AS
2 /* $Header: CSTAPPRB.pls 120.15.12010000.3 2008/11/30 11:43:57 anjha 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 BEGIN
946
947 IF l_debug_flag = 'Y' THEN
948 fnd_file.put_line(fnd_file.log,'Create_Rcv_Ae_Lines <<< ');
949 END IF;
950 l_ae_line_tbl := CSTPALTY.cst_ae_line_tbl_type();
951
952 -- Initialize local variables.
953 -- ---------------------------
954 l_err_rec.l_err_num := 0;
955 l_err_rec.l_err_code := '';
956 l_err_rec.l_err_msg := '';
957
958 -- Populate the Currency Record Type
959 -- ---------------------------------
960 l_stmt_num := 10;
961
962
963 select currency_code
964 into l_curr_rec.pri_currency
965 from gl_sets_of_books
966 where set_of_books_id = i_ae_txn_rec.set_of_books_id;
967
968 l_curr_rec.alt_currency := i_ae_txn_rec.currency_code;
969 l_curr_rec.currency_conv_date := i_ae_txn_rec.currency_conv_date;
970 l_curr_rec.currency_conv_type := i_ae_txn_rec.currency_conv_type;
971
972 --------------------------------------------------------
973 -- First set the parent transaction id as the transaction id itself
974 --------------------------------------------------------
975
976 l_par_rcv_txn := i_ae_txn_rec.transaction_id;
977
978 ----------------------------------------------------------
979 -- If the transaction type is NOT receive or MATCH,
980 -- then it has a parent
981 ----------------------------------------------------------
982
983 IF (i_ae_txn_rec.txn_type <> l_receive) AND (i_ae_txn_rec.txn_type <> l_match) THEN
984
985 l_stmt_num := 20;
986
987 SELECT
988 rt6.transaction_id
989 INTO
990 l_par_rcv_txn
991 FROM
992 rcv_transactions rt6
993 WHERE
994 rt6.transaction_type in (l_receive,l_match)
995 START WITH
996 rt6.transaction_id = i_ae_txn_rec.transaction_id
997 CONNECT BY
998 rt6.transaction_id = prior rt6.parent_transaction_id;
999
1000
1001 IF l_debug_flag = 'Y' THEN
1002 fnd_file.put_line(fnd_file.log,'Parent Receive Txn: '||to_char(l_par_rcv_txn));
1003 END IF;
1004
1005 END IF; /*end if receipt or match*/
1006
1007 ------------------------------------------------------
1008 -- Get the acquisition cost for the transaction
1009 ------------------------------------------------------
1010
1011 l_stmt_num := 30;
1012
1013 SELECT
1014 count(rcv_transaction_id) -- change for the bug 4968702
1015 INTO
1016 l_acq_exists
1017 FROM
1018 cst_rcv_acq_costs crac
1019 WHERE
1020 crac.rcv_transaction_id = l_par_rcv_txn AND
1021 crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
1022 crac.cost_group_id = i_ae_txn_rec.cost_group_id;
1023
1024 IF (l_acq_exists > 0) THEN
1025
1026
1027 l_stmt_num := 32;
1028
1029 SELECT
1030 crac.net_quantity_received,
1031 crac.costed_quantity,
1032 crac.acquisition_cost
1033 INTO
1034 l_nqr,
1035 l_costed_quantity,
1036 l_acq_cost
1037 from cst_rcv_acq_costs crac
1038 where crac.rcv_transaction_id = l_par_rcv_txn AND
1039 crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
1040 crac.cost_group_id = i_ae_txn_rec.cost_group_id;
1041
1042 IF (l_nqr = 0 AND nvl(i_ae_txn_rec.lcm_flag,'N') = 'N' ) THEN
1043 IF l_debug_flag = 'Y' THEN
1044 fnd_file.put_line(fnd_file.log, 'The Net Quantity Received is Zero');
1045 END IF;
1046 return;
1047 END IF;
1048
1049 -------------------------------------------------
1050 -- Get the acquisition cost in the entered currency
1051 -- Bug # 1054868
1052 -------------------------------------------------
1053
1054 -------------------------------------------------
1055 -- Joining with ap_invoices_all in order to get the
1056 -- exchange rate. This is a result of AP's Invoice
1057 -- Lines Project
1058 -------------------------------------------------
1059
1060 l_stmt_num := 33;
1061
1062 select
1063 sum(cracd.amount/NVL(aia.exchange_rate,1))
1064 into
1065 l_acq_cost_ent_inv
1066 from
1067 cst_rcv_acq_cost_details cracd,
1068 ap_invoice_distributions_all aida,
1069 ap_invoices_all aia
1070 where
1071 aida.invoice_distribution_id = cracd.invoice_distribution_id
1072 and aia.invoice_id = aida.invoice_id
1073 and aia.org_id = aida.org_id
1074 and cracd.header_id = (select header_id
1075 from cst_rcv_acq_costs crac
1076 where rcv_transaction_id = l_par_rcv_txn
1077 AND
1078 crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
1079 crac.cost_group_id = i_ae_txn_rec.cost_group_id)
1080 and cracd.source_type = 'INVOICE';
1081
1082 l_stmt_num := 34;
1083
1084 select
1085 sum(cracd.amount/ decode(poll.match_option,
1086 'P',CSTPPACQ.get_po_rate(rt.transaction_id),
1087 'R',rt.currency_conversion_rate))
1088 into
1089 l_acq_cost_ent_po
1090 from
1091 cst_rcv_acq_cost_details cracd,
1092 po_line_locations_all poll,
1093 rcv_transactions rt
1094 where
1095 poll.line_location_id = cracd.po_line_location_id
1096 and cracd.header_id = (select header_id
1097 from cst_rcv_acq_costs crac
1098 where rcv_transaction_id = rt.transaction_id
1099 AND
1100 crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
1101 crac.cost_group_id = i_ae_txn_rec.cost_group_id)
1102 and rt.transaction_id = l_par_rcv_txn
1103 and rt.po_line_location_id = poll.line_location_id
1104 and cracd.source_type = 'PO';
1105 IF (l_acq_cost_ent_po < 0) THEN
1106 IF l_debug_flag = 'Y' THEN
1107 fnd_file.put_line(fnd_file.log,'Error: No Acquisition Rate');
1108 END IF;
1109 l_err_rec.l_err_num := 999;
1110 raise process_error;
1111 END IF;
1112 /* Added nvl as fix for bug 2265867 */
1113 IF (l_costed_quantity <> 0) THEN
1114 l_acq_cost_ent := (nvl(l_acq_cost_ent_inv,0) + nvl(l_acq_cost_ent_po,0))/l_costed_quantity;
1115 ELSE
1116 l_acq_cost_ent := 0;
1117 END IF;
1118 ELSE
1119 IF l_debug_flag = 'Y' THEN
1120 fnd_file.put_line(fnd_file.log,'No Acquisition Cost');
1121 END IF;
1122 return;
1123 END IF;
1124
1125
1126 ----------------------------------------------------------------
1127 -- The receiving process can be divided into 2 stages:
1128 -- Stage 1 : Receiving from the supplier into the receiving dock
1129 -- Stage 2 : Delivering to the final destination
1130 -- Stage 1 transactions are :
1131 -- RECEIVE
1132 -- CORRECTION TO RECEIVE
1133 -- MATCH
1134 -- RETURN TO VENDOR
1135 -- CORRECTION TO RETURN TO VENDOR
1136 -- Stage 2 transactions are :
1137 -- DELIVER
1138 -- CORRECTION TO DELIVER
1139 -- RETURN TO RECEIVING
1140 -- CORRECTION TO RETURN TO RECEIVING
1141 -----------------------------------------------------------------
1142
1143 -----------------------------------------------------------------
1144 -- First set the stage flag to 0
1145 -- Then determine the stage for the current transaction
1146 -----------------------------------------------------------------
1147
1148 l_stage := 0;
1149
1150 IF (i_ae_txn_rec.txn_type in (l_receive,l_match,l_rtv)) THEN
1151 l_stage := 1;
1152 ELSIF (i_ae_txn_rec.txn_type in (l_deliver,l_rtr)) THEN
1153 l_stage := 2;
1154 END IF;
1155
1156
1157 IF (i_ae_txn_rec.txn_type = l_correct) THEN
1158
1159 l_stmt_num := 50;
1160
1161 SELECT
1162 rt1.transaction_id,
1163 rt1.transaction_type
1164 INTO
1165 l_par_txn,
1166 l_par_txn_type
1167 FROM
1168 rcv_transactions rt1
1169 WHERE
1170 rt1.transaction_id = (select rt2.parent_transaction_id
1171 FROM
1172 rcv_transactions rt2
1173 WHERE
1174 rt2.transaction_id = i_ae_txn_rec.transaction_id);
1175
1176 END IF;
1177
1178 ------------------------------------------------------------------------------
1179 -- Depending on the quantity, the effective action of the current transaction
1180 -- can either be a increase in the receive/deliver direction
1181 -- or an increase in the return direction
1182 -- The following are increase in the receive/deliver direction :
1183 -- RECEIVE/MATCH
1184 -- DELIVER
1185 -- POSITIVE CORRECTION TO RECEIVE/DELIVER/MATCH
1186 -- NEGATIVE CORRECTION TO RTV/RTR
1187 -- The following are increase in the return direction :
1188 -- RETURN TO VENDOR
1189 -- RETURN TO RECEIVING
1190 -- POSITIVE CORRECTION TO RTV/RTR
1191 -- NEGATIVE CORRECTION TO RECEIVE/DELIVER/MATCH
1192 ------------------------------------------------------------------------------
1193
1194 IF (i_ae_txn_rec.txn_type = l_correct) THEN
1195 IF l_debug_flag = 'Y' THEN
1196 fnd_file.put_line(fnd_file.log,'Parent of Correct: '||l_par_txn_type);
1197 fnd_file.put_line(fnd_file.log,'Quantity: '||to_char(i_ae_txn_rec.primary_quantity));
1198 END IF;
1199
1200 IF (l_par_txn_type in (l_receive,l_match,l_rtv)) THEN
1201 l_stage := 1;
1202 ELSE
1203 l_stage := 2;
1204 END IF;
1205 IF (l_par_txn_type in (l_receive,l_match,l_deliver) and i_ae_txn_rec.primary_quantity > 0) OR (l_par_txn_type in (l_rtv,l_rtr) and i_ae_txn_rec.primary_quantity < 0) THEN
1206 l_net_receipt := 1;
1207 ELSE
1208 l_net_receipt := -1;
1209 END IF;
1210
1211 ELSIF (i_ae_txn_rec.txn_type in (l_rtv,l_rtr)) THEN
1212 l_net_receipt := -1;
1213 ELSE
1214 l_net_receipt := 1;
1215 END IF;
1216 IF l_debug_flag = 'Y' THEN
1217 fnd_file.put_line(fnd_file.log,'Net Receipt or Return: '||to_char(l_net_receipt));
1218 END IF;
1219 ------------------------------------------------------------
1220 -- A receipt can be at the shipment level or the distribution level:
1221 -- When receiving and delivering in one step, it is at the distribution level
1222 -- When receiving and delivering as two different steps, it is shipment level
1223 -- Get the document level as 'S' for shipment and 'D' for distribution
1224 ------------------------------------------------------------
1225
1226
1227 l_stmt_num := 60;
1228
1229 SELECT
1230 decode(rt.po_distribution_id, NULL, 'S', 'D'),
1231 nvl(rt.po_distribution_id, rt.po_line_location_id)
1232 INTO
1233 l_doc_level,
1234 l_doc_id
1235 FROM
1236 rcv_transactions rt
1237 WHERE
1238 rt.transaction_id = i_ae_txn_rec.transaction_id;
1239
1240 IF l_debug_flag = 'Y' THEN
1241 fnd_file.put_line(fnd_file.log,'Document Level: '||l_doc_level);
1242 fnd_file.put_line(fnd_file.log,'Document ID: '||to_char(l_doc_id));
1243 fnd_file.put_line(fnd_file.log,'Quantity: '||to_char(i_ae_txn_rec.primary_quantity));
1244 END IF;
1245
1246 SELECT
1247 count(*)
1248 into
1249 l_dist_count
1250 FROM
1251 po_distributions_all
1252 WHERE
1253 -- begin fix for perf bug 2581067
1254 (
1255 (l_doc_level = 'D' AND po_distribution_id = l_doc_id)
1256 OR (l_doc_level = 'S' AND line_location_id = l_doc_id)
1257 )
1258 AND rownum <= 1;
1259 -- end fix for perf bug 2581067. replaced the following:
1260
1261 IF (l_dist_count = 0) THEN
1262 IF l_debug_flag = 'Y' THEN
1263 fnd_file.put_line(fnd_file.log,'Error: No Distributions for Document: '||to_char(l_doc_id));
1264 END IF;
1265 RAISE CST_NO_PO_DIST;
1266 END IF;
1267
1268
1269 ------------------------------------------------------------------
1270 -- If the document level is Shipment, get all the distributions
1271 -- for the Shipment, against which the receipt occurred.
1272 -- If the document level is Distribution, get the distribution
1273 -- Loop for each distribution that is accrue at receipt
1274 ------------------------------------------------------------------
1275
1276 DECLARE
1277 CURSOR c_receive_dists IS
1278 SELECT
1279 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",
1280 decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_date,pod.rate_date) "EXCHG_DATE",
1281 -- J Changes -------------------------------------------------------------------------
1282 DECODE(POLL.MATCHING_BASIS, 'AMOUNT', 1, -- Changed for Complex work procurement
1283 'QUANTITY', 0 ) "SERVICE_FLAG",
1284 --------------------------------------------------------------------------------------
1285 POD.po_distribution_id "PO_DISTRIBUTION_ID",
1286 POLL.line_location_id "PO_LINE_LOCATION_ID",
1287 POD.code_combination_id "EXPENSE_ACCOUNT_ID",
1288 POD.destination_type_code "DESTINATION_TYPE_CODE",
1289 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
1290 POD.accrual_account_id "ACCRUAL_ACCOUNT_ID",
1291 nvl(POD.budget_account_id,-1) "ENCUMBRANCE_ACCOUNT_ID",
1292 decode(l_doc_level,'D', 1, DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work procurement
1293 'AMOUNT', POD.AMOUNT_ORDERED/POLL.AMOUNT,
1294 'QUANTITY',POD.QUANTITY_ORDERED/POLL.QUANTITY))
1295 * i_ae_txn_rec.primary_quantity "DIST_QUANTITY",
1296 -- J Changes ----------------------------------------------------------------------------
1297 (po_tax_sv.get_tax('PO',pod.po_distribution_id) /
1298 DECODE(POLL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_ORDERED, -- Changed for Complex work procurement
1299 'QUANTITY',POD.QUANTITY_ORDERED) ) "TAX"
1300 -----------------------------------------------------------------------------------------
1301 FROM
1302 po_distributions_all pod,
1303 po_line_locations_all poll,
1304 -- J Changes ----------------------------------------------------------------------------
1305 PO_LINES_ALL POL,
1306 -----------------------------------------------------------------------------------------
1307 rcv_parameters rp
1308 WHERE
1309 -- begin fix for perf bug 2581067
1310 (
1311 (l_doc_level = 'D' AND pod.po_distribution_id = l_doc_id)
1312 OR (l_doc_level = 'S' AND poll.line_location_id = l_doc_id)
1313 )
1314 -- end fix for perf bug 2581067
1315 and pod.line_location_id = poll.line_location_id
1316 -- J Changes ----------------------------------------------------------------------------
1317 AND POLL.PO_LINE_ID = POL.PO_LINE_ID
1318 -----------------------------------------------------------------------------------------
1319 and rp.organization_id = pod.destination_organization_id
1320 and pod.destination_type_code in ('INVENTORY', 'SHOP FLOOR')
1321 and nvl(POLL.accrue_on_receipt_flag,'N') = 'Y'
1322 /* and nvl(POD.accrue_on_receipt_flag,'N') = 'Y' */;
1323
1324 l_lcm_adj_period NUMBER;
1325 l_landed_cost_abs_account NUMBER;
1326
1327 BEGIN
1328 SELECT nvl(max(LANDED_COST_ABS_ACCOUNT),-1)
1329 INTO l_landed_cost_abs_account
1330 FROM CST_ORG_COST_GROUP_ACCOUNTS coga
1331 WHERE coga.legal_entity_id = i_ae_txn_rec.legal_entity_id
1332 AND coga.cost_type_id = i_ae_txn_rec.cost_type_id
1333 AND coga.cost_group_id = i_ae_txn_rec.cost_group_id;
1334
1335 FOR c_receipts_rec IN c_receive_dists LOOP
1336 l_stmt_num := 62;
1337 l_ae_line_rec.actual_flag := NULL;
1338 l_ae_line_rec.po_distribution_id := c_receipts_rec.po_distribution_id;
1339
1340 ---------------------------------------------------------
1341 -- The PO Price is in terms of the PO UOM
1342 -- Convert it in terms of the primary UOM for the item
1343 ---------------------------------------------------------
1344
1345 SELECT
1346 -- J Changes ----------------------------------------------------------------------------
1347 DECODE(C_RECEIPTS_REC.SERVICE_FLAG, 1, 1,
1348 (poll.price_override * rt.source_doc_quantity / rt.primary_quantity))
1349 -----------------------------------------------------------------------------------------
1350 INTO
1351 l_po_price
1352 FROM
1353 rcv_transactions rt,
1354 po_line_locations_all poll
1355 WHERE
1356 rt.transaction_id = i_ae_txn_rec.transaction_id
1357 AND rt.po_line_location_id = poll.line_location_id;
1358
1359
1360
1361 --------------------------------------------------------------------
1362 -- For Expense destinations, both stage 1 and stage 2 transactions are processed
1363 -- For Inventory and Shop floor, only stage 1 transactions are processed
1364 -- the stage 2 transactions are populated in the MMT and WT tables
1365 -- and are processed by the INV and WIP processor
1366 --------------------------------------------------------------------
1367
1368 --IF (c_receipts_rec.destination_type_code <> 'EXPENSE') THEN
1369
1370 IF (l_stage = 1) THEN
1371 IF (nvl(i_ae_txn_rec.lcm_flag,'N') = 'Y') THEN
1372 /*LCM PO
1373 -- If net action is receive, the following accounting
1374 -- needs to be generated
1375 -- Dr. Receiving Inspection qty@Acq_cost
1376 -- Cr. Accrual qty@po
1377 -- Dr./Cr. Landed Cost Absorption
1378 -- If net action is returns, the following accounting
1379 -- needs to be generated
1380 -- Dr. Accrual qty@po
1381 -- Cr. Receiving Inspection qty@acq_cost
1382 -- Dr./Cr. Landed Cost Absorption
1383 */
1384 l_curr_rec.currency_conv_rate := c_receipts_rec.exchg_rate;
1385 l_curr_rec.currency_conv_date := c_receipts_rec.exchg_date;
1386 l_curr_rec.currency_conv_type := i_ae_txn_rec.currency_conv_type;
1387
1388 IF (l_net_receipt = 1) THEN
1389 l_dr_flag := FALSE;
1390 ELSE
1391 l_dr_flag := TRUE;
1392 END IF;
1393
1394 l_ae_line_rec.ae_line_type := 16;
1395 l_ae_line_rec.account := c_receipts_rec.accrual_account_id;
1396 l_ae_line_rec.transaction_value := c_receipts_rec.dist_quantity * (l_po_price + c_receipts_rec.tax);
1397 /* Accrual entries need to be rounded in receiving currency
1398 first to match the invoicing logic.
1399 */
1400 l_stmt_num := 1000;
1401 SELECT decode(c2.minimum_accountable_unit,
1402 NULL,round(l_ae_line_rec.transaction_value,c2.precision),
1403 round(l_ae_line_rec.transaction_value/c2.minimum_accountable_unit)
1404 * c2.minimum_accountable_unit )
1405 INTO l_ae_line_rec.transaction_value
1406 FROM fnd_currencies c2
1407 WHERE c2.currency_code = decode(l_curr_rec.alt_currency, NULL,
1408 l_curr_rec.pri_currency,
1409 l_curr_rec.alt_currency);
1410
1411
1412
1413 IF l_debug_flag = 'Y' THEN
1414 IF (l_dr_flag) THEN
1415 fnd_file.put_line(fnd_file.log,'DR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1416 ELSE
1417 fnd_file.put_line(fnd_file.log,'CR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1418 END IF;
1419 END IF;
1420
1421 IF ( c_receipts_rec.dist_quantity = 0 ) THEN
1422 l_ae_line_rec.rate_or_amount := 0;
1423 ELSE
1424 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity ;
1425 END IF;
1426
1427 l_stmt_num := 1010;
1428 CSTPAPPR.insert_account (i_ae_txn_rec,
1429 l_curr_rec,
1430 l_dr_flag,
1431 l_ae_line_rec,
1432 l_ae_line_tbl,
1433 l_err_rec);
1434 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) then
1435 raise process_error;
1436 END IF;
1437
1438 l_dr_flag := not l_dr_flag;
1439 l_stmt_num := 1020;
1440 SELECT nvl(max(craca.period_id),-1)
1441 INTO l_lcm_adj_period
1442 FROM cst_rcv_acq_costs_adj craca
1443 WHERE craca.rcv_transaction_id = l_par_rcv_txn
1444 AND craca.cost_type_id = i_ae_txn_rec.cost_type_id
1445 AND craca.cost_group_id = i_ae_txn_rec.cost_group_id;
1446
1447 IF (l_lcm_adj_period <> -1) THEN
1448 l_stmt_num := 1030;
1449 SELECT craca.acquisition_cost
1450 INTO l_acq_cost
1451 FROM cst_rcv_acq_costs_adj craca
1452 WHERE craca.rcv_transaction_id = l_par_rcv_txn
1453 AND craca.cost_type_id = i_ae_txn_rec.cost_type_id
1454 AND craca.cost_group_id = i_ae_txn_rec.cost_group_id
1455 AND craca.period_id = l_lcm_adj_period;
1456
1457 END IF;
1458 l_acq_cost_ent := l_acq_cost/c_receipts_rec.exchg_rate;
1459 l_ae_line_rec.account := c_receipts_rec.receiving_account_id;
1460 l_ae_line_rec.ae_line_type := 5;
1461 l_ae_line_rec.transaction_value := c_receipts_rec.dist_quantity * l_acq_cost_ent;
1462 l_stmt_num := 1040;
1463 SELECT decode(c2.minimum_accountable_unit,
1464 NULL,round(l_ae_line_rec.transaction_value,c2.precision),
1465 round(l_ae_line_rec.transaction_value/c2.minimum_accountable_unit)
1466 * c2.minimum_accountable_unit )
1467 INTO l_ae_line_rec.transaction_value
1468 FROM fnd_currencies c2
1469 WHERE c2.currency_code = decode(l_curr_rec.alt_currency, NULL,
1470 l_curr_rec.pri_currency,
1471 l_curr_rec.alt_currency);
1472
1473 IF l_debug_flag = 'Y' THEN
1474 IF (l_dr_flag) THEN
1475 fnd_file.put_line(fnd_file.log,'DR RI: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1476 ELSE
1477 fnd_file.put_line(fnd_file.log,'CR RI: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1478 END IF;
1479 END IF;
1480
1481 IF ( c_receipts_rec.dist_quantity = 0 ) THEN
1482 l_ae_line_rec.rate_or_amount := 0;
1483 ELSE
1484 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity ;
1485 END IF;
1486 l_stmt_num := 1050;
1487 CSTPAPPR.insert_account (i_ae_txn_rec,
1488 l_curr_rec,
1489 l_dr_flag,
1490 l_ae_line_rec,
1491 l_ae_line_tbl,
1492 l_err_rec);
1493 if (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) then
1494 raise process_error;
1495 end if;
1496
1497 IF ( l_acq_cost_ent<>(l_po_price + c_receipts_rec.tax)) THEN
1498 IF ((l_po_price + c_receipts_rec.tax)>l_acq_cost_ent) THEN
1499 IF (l_net_receipt = 1) THEN
1500 l_dr_flag := TRUE;
1501 ELSE
1502 l_dr_flag := FALSE;
1503 END IF;
1504 ELSE
1505 IF (l_net_receipt = 1) THEN
1506 l_dr_flag := FALSE;
1507 ELSE
1508 l_dr_flag := TRUE;
1509 END IF;
1510 END IF;
1511 l_ae_line_rec.account := l_landed_cost_abs_account;
1512 l_ae_line_rec.ae_line_type := 38;
1513 l_ae_line_rec.transaction_value := abs(c_receipts_rec.dist_quantity *
1514 ((l_po_price + c_receipts_rec.tax)-l_acq_cost_ent)) ;
1515 l_stmt_num := 1060;
1516 SELECT decode(c2.minimum_accountable_unit,
1517 NULL,round(l_ae_line_rec.transaction_value,c2.precision),
1518 round(l_ae_line_rec.transaction_value/c2.minimum_accountable_unit)
1519 * c2.minimum_accountable_unit )
1520 INTO l_ae_line_rec.transaction_value
1521 FROM fnd_currencies c2
1522 WHERE c2.currency_code = decode(l_curr_rec.alt_currency, NULL,
1523 l_curr_rec.pri_currency,
1524 l_curr_rec.alt_currency);
1525
1526 IF l_debug_flag = 'Y' THEN
1527 IF (l_dr_flag) THEN
1528 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 ));
1529 ELSE
1530 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 ));
1531 END IF;
1532 END IF;
1533
1534 IF ( c_receipts_rec.dist_quantity = 0 ) THEN
1535 l_ae_line_rec.rate_or_amount := 0;
1536 ELSE
1537 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity ;
1538 END IF;
1539 l_stmt_num := 1070;
1540 CSTPAPPR.insert_account (i_ae_txn_rec,
1541 l_curr_rec,
1542 l_dr_flag,
1543 l_ae_line_rec,
1544 l_ae_line_tbl,
1545 l_err_rec);
1546
1547 if (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) then
1548 raise process_error;
1549 end if;
1550
1551 END IF;
1552
1553 ELSE
1554 ----------------------------------------------------------
1555 -- If net action is receive, the following accounting needs to be generated
1556 -- Dr. Receiving Inspection qty@Acq_cost
1557 -- Cr. Accrual qty@po
1558 -- Dr./Cr. IPV
1559 -- Dr./Cr. Special charges
1560 -- Dr./Cr. ERV
1561 -- If net action is returns, the following accounting needs to be generated
1562 -- Dr. Accrual qty@po
1563 -- Cr. Receiving Inspection qty@acq_cost
1564 -- Dr./Cr. IPV
1565 -- Dr./Cr. Special charges
1566 -- Dr./Cr. ERV
1567 --
1568 -- Example :
1569 --
1570 -- PO Shipment qty = 100 po price $10 each
1571 -- PO Distributions :
1572 -- 40 tax = $40 p.u.tax = 40/40=1
1573 -- 60 tax = $30 p.u.tax = 30/60=0.5
1574 -- Receive 50 against shipment
1575 -- Invoice 10@12 against Receipt
1576 -- + Tax $10 p.u.tax=10/10=1
1577 -- + Freight $40
1578 -- + Tax on Freight $2
1579 -- POD #1
1580 -- Dr. Accrual 0.4*10*(10+1)=44
1581 -- Cr. Liability 0.4*(120+10+40+2)=68.8
1582 -- Dr. IPV 0.4*10*(12-10) = 8
1583 -- Dr. Tax IPV 0.4*10*(1-1)=0
1584 -- Dr. Freight Expense 0.4*40=16
1585 -- Dr. Tax Expense 0.4*2=0.8
1586 -- POD #2
1587 -- Dr. Accrual 0.6*10*(10+0.5)=63
1588 -- Cr. Liability 0.6*(120+10+40+5)=103.2
1589 -- Dr. IPV 0.6*10*(12-10) = 12
1590 -- Dr. Tax IPV 0.6*10*(1-0.5)=3
1591 -- Dr. Freight Expense 0.6*40=24
1592 -- Dr. Tax Expense 0.6*2=1.2
1593 -- Acquisition Cost = (10*12 + 10 + 40 + 2 + 40*11*0.4 + 40*10.5*0.6)/50 =
1594 -- 12
1595 --
1596 -- For each distribution :
1597 -- POD #1 :
1598 -- Dr. RI 50*40/100*12 = 240
1599 -- Cr. Accrual 50*40/100*(10 + 1) = 220
1600 -- Cr. IPV (8/50)*50=8
1601 -- Cr. Tax IPV (0/50)*50=0
1602 -- Cr. Freight Expense (16/50)*50=16
1603 -- Cr. Tax Expense (0.8/50)*50=0.8
1604 -- POD #2 :
1605 -- Dr. RI 50*60/100*12 = 360
1606 -- Cr. Accrual 50*60/100*(10 + 0.5) = 315
1607 -- Cr. IPV (12/50)*50*=12
1608 -- Cr. Tax IPV (3/50)*50=3
1609 -- Cr. Freight Expense (24/50)*50=24
1610 -- Cr. Tax Expense (1.2/50)*50=1.2
1611 -- Total Debits : 600
1612 -- Total Credits : 600
1613 ----------------------------------------------------------
1614
1615 IF (l_net_receipt = 1) THEN
1616 l_dr_flag := TRUE;
1617 ELSE
1618 l_dr_flag := FALSE;
1619 END IF;
1620
1621 l_ae_line_rec.account := c_receipts_rec.receiving_account_id;
1622 l_ae_line_rec.ae_line_type := 5;
1623 l_ae_line_rec.transaction_value := c_receipts_rec.dist_quantity * l_acq_cost_ent;
1624
1625 IF (l_acq_cost_ent = 0) THEN
1626 l_curr_rec.currency_conv_rate := 1;
1627 ELSE
1628 l_curr_rec.currency_conv_rate := l_acq_cost/l_acq_cost_ent;
1629 END IF;
1630
1631 l_curr_rec.currency_conv_date := null;
1632 l_curr_rec.currency_conv_type := null;
1633 --acq cost is a combination of po and invoice rate and is in functional currency
1634
1635 IF l_debug_flag = 'Y' THEN
1636 IF (l_dr_flag) THEN
1637 fnd_file.put_line(fnd_file.log,'DR RI: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1638 ELSE
1639 fnd_file.put_line(fnd_file.log,'CR RI: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1640 END IF;
1641 END IF;
1642
1643 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
1644 not the primary quantity on the transaction */
1645 IF ( c_receipts_rec.dist_quantity = 0 ) THEN
1646 l_ae_line_rec.rate_or_amount := 0;
1647 ELSE
1648 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity ;
1649 END IF;
1650
1651 CSTPAPPR.insert_account (i_ae_txn_rec,
1652 l_curr_rec,
1653 l_dr_flag,
1654 l_ae_line_rec,
1655 l_ae_line_tbl,
1656 l_err_rec);
1657 if (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) then
1658 raise process_error;
1659 end if;
1660
1661 -------------------------------------------
1662 -- Toggle the debit flag
1663 -------------------------------------------
1664
1665 l_dr_flag := not l_dr_flag;
1666
1667 l_stmt_num := 70;
1668
1669 /* Bug 3421141: Drop Shipment and Global Procureemnt Changes */
1670
1671 /* For Global Procurement receipts a credit needs to be made to the
1672 I/C Accrual account, rather than the supplier accrual account.
1673
1674 Instead of determining if the current transaction is in the
1675 context of global procureemnt by figuring out the PO OU and the
1676 receiving OU, I'm getting the account information from RAE, which
1677 will always store the correct account */
1678
1679 begin
1680 select credit_account_id, debit_account_id
1681 into l_credit_Account, l_debit_account
1682 from rcv_accounting_events
1683 where rcv_transaction_id = i_ae_txn_rec.transaction_id
1684 and organization_id = i_ae_txn_rec.organization_id
1685 and event_type_id = 1
1686 and rownum = 1;
1687
1688 if(l_dr_flag) then
1689 l_ae_line_rec.account := l_debit_account;
1690 else
1691 l_ae_line_rec.account := l_credit_account;
1692 end if;
1693
1694 exception
1695 when others then
1696 l_ae_line_rec.account := c_receipts_rec.accrual_account_id;
1697 end;
1698
1699 l_stmt_num := 750;
1700
1701
1702 /* Bug 5555888 : For Global Procurement scenario (PO OU is diff from Rcv OU), the Accrual
1703 should also be hit at the acq cost (PO price or Transfer price as per setup). Also,
1704 in this case the tax/Invoice/IPV etc is also not there as acq price is binding price.
1705 For Normal receits do it at the PO price as before. */
1706
1707 select org_id
1708 into l_po_ou_id /* Get the OU where PO is created */
1709 from po_headers_all
1710 where po_header_id = (select po_header_id
1711 from rcv_transactions
1712 where transaction_id = i_ae_txn_rec.transaction_id);
1713
1714 l_stmt_num := 751;
1715
1716 select to_number(org_information3)
1717 into l_rcv_ou_id /* Get OU where Receiving is done */
1718 from hr_organization_information
1719 where org_information_context = 'Accounting Information'
1720 and organization_id = i_ae_txn_rec.organization_id;
1721
1722 l_stmt_num := 752;
1723
1724 IF l_debug_flag = 'Y' THEN
1725 fnd_file.put_line(fnd_file.log,'PO OU / Rcv OU '|| l_po_ou_id ||' / '|| l_rcv_ou_id);
1726 END IF;
1727
1728 l_stmt_num := 753;
1729
1730 l_ae_line_rec.ae_line_type := 16;
1731
1732 IF (l_po_ou_id <> l_rcv_ou_id) THEN /* Global Procurement Scenario */
1733
1734 l_stmt_num := 754;
1735
1736 IF l_debug_flag = 'Y' THEN
1737 fnd_file.put_line(fnd_file.log,'GP scenario. Trxn val: '||to_char(c_receipts_rec.dist_quantity * l_acq_cost_ent));
1738 END IF;
1739
1740 l_ae_line_rec.transaction_value := c_receipts_rec.dist_quantity * l_acq_cost_ent;
1741
1742 IF (l_acq_cost_ent = 0) THEN
1743 l_curr_rec.currency_conv_rate := 1;
1744 ELSE
1745 l_curr_rec.currency_conv_rate := l_acq_cost/l_acq_cost_ent;
1746 END IF;
1747
1748 l_curr_rec.currency_conv_date := null;
1749 l_curr_rec.currency_conv_type := null;
1750 --acq cost is a combination of po and invoice rate and is in functional currency
1751
1752 ELSE
1753
1754 l_stmt_num := 755;
1755
1756 IF l_debug_flag = 'Y' THEN
1757 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)));
1758 END IF;
1759
1760 l_ae_line_rec.transaction_value := c_receipts_rec.dist_quantity * (l_po_price + c_receipts_rec.tax);
1761
1762 l_curr_rec.currency_conv_rate := c_receipts_rec.exchg_rate;
1763 l_curr_rec.currency_conv_date := c_receipts_rec.exchg_date;
1764 l_curr_rec.currency_conv_type := i_ae_txn_rec.currency_conv_type;
1765
1766 END IF; /* (l_po_ou_id <> l_rcv_ou_id) */
1767
1768 l_stmt_num := 756;
1769
1770 IF l_debug_flag = 'Y' THEN
1771 IF (l_dr_flag) THEN
1772 fnd_file.put_line(fnd_file.log,'DR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1773 ELSE
1774 fnd_file.put_line(fnd_file.log,'CR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1775 END IF;
1776 END IF;
1777
1778 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
1779 not the primary quantity on the transaction */
1780 IF ( c_receipts_rec.dist_quantity = 0 ) THEN
1781 l_ae_line_rec.rate_or_amount := 0;
1782 ELSE
1783 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity ;
1784 END IF;
1785
1786 l_stmt_num := 757;
1787
1788 CSTPAPPR.insert_account (i_ae_txn_rec,
1789 l_curr_rec,
1790 l_dr_flag,
1791 l_ae_line_rec,
1792 l_ae_line_tbl,
1793 l_err_rec);
1794 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) then
1795 raise process_error;
1796 END IF;
1797
1798 l_stmt_num := 758;
1799
1800 ------------------------------------------------------
1801 -- All the invoice distribution lines of type TAX/FREIGHT/MISCELLANEOUS
1802 -- that do not have IPV should be charged to the Special charge account
1803 -- that is specified at the time of invoice creation
1804 ---------------------------------------------------------------
1805
1806 -------------------------------------------------
1807 -- Joining with ap_invoices_all in order to get the
1808 -- exchange rate. Also, the code needs to check for
1809 -- more than just ITEM when looking at the lookup code
1810 -- This is a result of AP's Invoice Lines Project
1811 -------------------------------------------------
1812
1813
1814 DECLARE
1815 CURSOR c_charges IS
1816 SELECT
1817 cracd.amount/nvl(aia.exchange_rate,1) "AMOUNT",
1818 nvl(aia.exchange_rate,1) "EXCHANGE_RATE",
1819 aia.exchange_date "EXCHANGE_DATE",
1820 aia.exchange_rate_type "EXCHANGE_RATE_TYPE",
1821 aida.dist_code_combination_id "CODE_COMBINATION_ID"
1822 FROM
1823 cst_rcv_acq_cost_details cracd,
1824 cst_rcv_acq_costs crac,
1825 ap_invoice_distributions_all aida,
1826 ap_invoices_all aia
1827 WHERE
1828 cracd.line_type NOT IN ('ITEM','ACCRUAL','IPV','ERV','NONREC_TAX') AND
1829 crac.rcv_transaction_id = l_par_rcv_txn AND
1830 crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
1831 crac.cost_group_id = i_ae_txn_rec.cost_group_id AND
1832 cracd.header_id = crac.header_id AND
1833 cracd.source_type = 'INVOICE' AND
1834 cracd.invoice_distribution_id = aida.invoice_distribution_id AND
1835 aia.invoice_id = aida.invoice_id AND
1836 aia.org_id = aida.org_id AND
1837 NOT EXISTS (
1838 SELECT '1' FROM ap_invoice_distributions_all aida2
1839 WHERE aida2.related_id = aida.invoice_distribution_id
1840 AND aida2.line_type_lookup_code = 'IPV'
1841 );
1842 BEGIN
1843
1844 FOR c_chg_rec IN c_charges LOOP
1845
1846 IF (l_net_receipt = 1 and c_chg_rec.amount > 0) OR (l_net_receipt = -1 and c_chg_rec.amount < 0) THEN
1847 l_dr_flag := FALSE;
1848 ELSE
1849 l_dr_flag := TRUE;
1850 END IF;
1851
1852 l_ae_line_rec.account := c_chg_rec.code_combination_id;
1853 l_ae_line_rec.ae_line_type := 19;
1854 --l_ae_line_rec.transaction_value := c_chg_rec.amount * (c_receipts_rec.dist_quantity / l_costed_quantity);
1855 l_ae_line_rec.transaction_value := c_chg_rec.amount * (i_ae_txn_rec.primary_quantity / l_costed_quantity);
1856
1857 -- also populate type date etc
1858 l_curr_rec.currency_conv_rate := c_chg_rec.exchange_rate;
1859 l_curr_rec.currency_conv_date := c_chg_rec.exchange_date;
1860 l_curr_rec.currency_conv_type := c_chg_rec.exchange_rate_type;
1861
1862 IF l_debug_flag = 'Y' THEN
1863 IF (l_dr_flag) THEN
1864 fnd_file.put_line(fnd_file.log,'DR Special Charges: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1865 ELSE
1866 fnd_file.put_line(fnd_file.log,'CR Special Charges: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1867 END IF;
1868 END IF;
1869
1870 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
1871 not the primary quantity on the transaction */
1872 IF (i_ae_txn_rec.primary_quantity = 0) THEN
1873 l_ae_line_rec.rate_or_amount := 0;
1874 ELSE
1875 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;
1876 END IF;
1877
1878 CSTPAPPR.insert_account (i_ae_txn_rec,
1879 l_curr_rec,
1880 l_dr_flag,
1881 l_ae_line_rec,
1882 l_ae_line_tbl,
1883 l_err_rec);
1884 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) THEN
1885 raise process_error;
1886 END IF;
1887
1888 END LOOP; /* c_charges loop */
1889 EXCEPTION
1890 when process_error then
1891 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
1892 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
1893 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
1894
1895 END;
1896
1897 ---------------------------------------------------------------
1898 -- The invoice distribution lines that have IPV
1899 ---------------------------------------------------------------
1900
1901 ---------------------------------------------------------------
1902 -- Invoice Lines Project
1903 -- {base_}invoice_price_variance will be obsolete in 11.5.11
1904 -- also. Instead a separate distribution will be created for IPV
1905 -- Consult Invoice lines documentation (AP and/or Costing) for
1906 -- more information. In addition exchange rate needs to
1907 -- come from ap_invoice_all
1908 ---------------------------------------------------------------
1909
1910 DECLARE
1911 CURSOR c_ipv IS
1912 SELECT
1913 aida.base_amount/nvl(aia.exchange_rate,1) "INVOICE_PRICE_VARIANCE",
1914 nvl(aia.exchange_rate,1) "EXCHANGE_RATE",
1915 aia.exchange_date "EXCHANGE_DATE",
1916 aia.exchange_rate_type "EXCHANGE_RATE_TYPE",
1917 aida.dist_code_combination_id "CODE_COMBINATION_ID",
1918 -- Retroactive Pricing Enhancements
1919 -- Need Invoice Distribution to find correction invoices
1920 aida.related_id "INVOICE_DISTRIBUTION_ID"
1921 FROM
1922 cst_rcv_acq_cost_details cracd,
1923 cst_rcv_acq_costs crac,
1924 ap_invoice_distributions_all aida,
1925 ap_invoices_all aia
1926 WHERE
1927 crac.rcv_transaction_id = l_par_rcv_txn AND
1928 crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
1929 crac.cost_group_id = i_ae_txn_rec.cost_group_id AND
1930 cracd.header_id = crac.header_id AND
1931 cracd.source_type = 'INVOICE' AND
1932 cracd.invoice_distribution_id = aida.related_id AND
1933 aia.invoice_id = aida.invoice_id AND
1934 aia.org_id = aida.org_id AND
1935 aida.line_type_lookup_code = 'IPV' AND
1936 aida.amount <> 0 AND
1937 aida.posted_flag = 'Y'; --Added for bug 4773085
1938 BEGIN
1939
1940 FOR c_ipv_rec IN c_ipv LOOP
1941
1942 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
1943 l_dr_flag := FALSE;
1944 ELSE
1945 l_dr_flag := TRUE;
1946 END IF;
1947
1948 -- IPV account stamped on the Price correction Invoices is the same as the
1949 -- one on the original Invoice
1950 -- Get the sum of IPV amounts on the price correction invoices and add them to
1951 -- the IPV on the original invoice
1952 -- If there is a Price Correction Invoice, the IPV reverses and the net
1953 -- IPV SHOULD be 0.
1954
1955 -- As a result of Invoice Lines Project, IPV is a separate distribution
1956 -- and retropricing is handled through corrected_invoice_dist_id column
1957 l_stmt_num := 71;
1958 BEGIN
1959 SELECT
1960 NVL(AIDA.BASE_AMOUNT/NVL(AP_INV.EXCHANGE_RATE,1), 0)
1961 INTO
1962 l_correct_ipv_amount
1963 FROM
1964 AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
1965 AP_INVOICES_ALL AP_INV
1966 WHERE
1967 AIDA.CORRECTED_INVOICE_DIST_ID = c_ipv_rec.INVOICE_DISTRIBUTION_ID
1968 AND AIDA.LINE_TYPE_LOOKUP_CODE = 'IPV'
1969 AND AIDA.INVOICE_ID = AP_INV.INVOICE_ID
1970 AND AP_INV.INVOICE_TYPE_LOOKUP_CODE = 'PO PRICE ADJUST';
1971 EXCEPTION
1972 WHEN OTHERS THEN
1973 l_correct_ipv_amount := 0;
1974 END;
1975
1976 IF l_debug_flag = 'Y' THEN
1977 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Original IPV: '||to_char(c_ipv_rec.invoice_price_variance));
1978 FND_FILE.PUT_LINE(FND_FILE.LOG, 'IPV - Price Correction Invoice: '||to_char(l_correct_ipv_amount));
1979 END IF;
1980
1981 l_total_ipv := c_ipv_rec.invoice_price_variance + l_correct_ipv_amount;
1982
1983 -- Create IPV line only if there is a net IPV
1984
1985 IF l_total_ipv <> 0 THEN
1986
1987 l_ae_line_rec.account := c_ipv_rec.code_combination_id;
1988 l_ae_line_rec.ae_line_type := 17;
1989 l_ae_line_rec.transaction_value := c_ipv_rec.invoice_price_variance * (i_ae_txn_rec.primary_quantity / l_costed_quantity);
1990
1991 l_curr_rec.currency_conv_rate := c_ipv_rec.exchange_rate;
1992 l_curr_rec.currency_conv_date := c_ipv_rec.exchange_date;
1993 l_curr_rec.currency_conv_type := c_ipv_rec.exchange_rate_type;
1994
1995 IF l_debug_flag = 'Y' THEN
1996 IF (l_dr_flag) THEN
1997 fnd_file.put_line(fnd_file.log,'DR IPV: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
1998 ELSE
1999 fnd_file.put_line(fnd_file.log,'CR IPV: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2000 END IF;
2001 END IF;
2002
2003 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
2004 not the primary quantity on the transaction */
2005 IF (i_ae_txn_rec.primary_quantity = 0) THEN
2006 l_ae_line_rec.rate_or_amount := 0;
2007 ELSE
2008 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;
2009 END IF;
2010
2011 CSTPAPPR.insert_account (i_ae_txn_rec,
2012 l_curr_rec,
2013 l_dr_flag,
2014 l_ae_line_rec,
2015 l_ae_line_tbl,
2016 l_err_rec);
2017 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) THEN
2018 raise process_error;
2019 END IF;
2020
2021 END IF; -- l_total_ipv <> 0
2022
2023 END LOOP;
2024 EXCEPTION
2025 when process_error then
2026 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
2027 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
2028 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
2029
2030 END;
2031
2032
2033 ----------------------------------------------------------------
2034 -- The invoice distribution lines that have ERV
2035 -- For the invoice lines project, ERV columns are obsolete and
2036 -- are separte distributions. This is previously described in a
2037 -- similar situation involving IPV as well as AP's documentation
2038 ----------------------------------------------------------------
2039
2040 DECLARE
2041 CURSOR c_erv IS
2042 SELECT
2043 aida.amount "EXCHANGE_RATE_VARIANCE",
2044 aida.dist_code_combination_id "CODE_COMBINATION_ID"
2045 FROM
2046 cst_rcv_acq_cost_details cracd,
2047 cst_rcv_acq_costs crac,
2048 ap_invoice_distributions_all aida
2049 WHERE
2050 crac.rcv_transaction_id = l_par_rcv_txn AND
2051 crac.cost_type_id = i_ae_txn_rec.cost_type_id AND
2052 crac.cost_group_id = i_ae_txn_rec.cost_group_id AND
2053 cracd.header_id = crac.header_id AND
2054 cracd.source_type = 'INVOICE' AND
2055 cracd.invoice_distribution_id = aida.related_id AND
2056 aida.line_type_lookup_code = 'ERV' AND
2057 aida.amount <> 0 AND
2058 aida.posted_flag = 'Y';-- Added for bug 4773085
2059
2060 BEGIN
2061
2062 FOR c_erv_rec IN c_erv LOOP
2063
2064 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
2065 l_dr_flag := FALSE;
2066 ELSE
2067 l_dr_flag := TRUE;
2068 END IF;
2069
2070 l_ae_line_rec.account := c_erv_rec.code_combination_id;
2071 l_ae_line_rec.ae_line_type := 18;
2072 --l_ae_line_rec.transaction_value := c_erv_rec.exchange_rate_variance * (c_receipts_rec.dist_quantity / l_costed_quantity);
2073 l_ae_line_rec.transaction_value := c_erv_rec.exchange_rate_variance * (i_ae_txn_rec.primary_quantity / l_costed_quantity);
2074
2075 -- also populate type date etc
2076 l_curr_rec.currency_conv_rate := 1;
2077 l_curr_rec.currency_conv_date := null;
2078 l_curr_rec.currency_conv_type := null;
2079
2080 IF l_debug_flag = 'Y' THEN
2081 IF (l_dr_flag) THEN
2082 fnd_file.put_line(fnd_file.log,'DR ERV: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2083 ELSE
2084 fnd_file.put_line(fnd_file.log,'CR ERV: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2085 END IF;
2086 END IF;
2087
2088 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
2089 not the primary quantity on the transaction */
2090 IF (i_ae_txn_rec.primary_quantity = 0) THEN
2091 l_ae_line_rec.rate_or_amount := 0;
2092 ELSE
2093 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;
2094 END IF;
2095
2096 CSTPAPPR.insert_account (i_ae_txn_rec,
2097 l_curr_rec,
2098 l_dr_flag,
2099 l_ae_line_rec,
2100 l_ae_line_tbl,
2101 l_err_rec);
2102 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) THEN
2103 raise process_error;
2104 END IF;
2105
2106 END LOOP;
2107 EXCEPTION
2108 when process_error then
2109 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
2110 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
2111 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
2112
2113 END;
2114 END IF;
2115 END IF;
2116 END LOOP; /*not expense*/
2117 EXCEPTION
2118 when process_error then
2119 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
2120 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
2121 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
2122
2123 END;
2124
2125 -- Take care of rounding errors.
2126 -- -----------------------------
2127 l_stmt_num := 80;
2128 balance_account (l_ae_line_tbl,
2129 l_err_rec);
2130
2131 -- check error
2132 if (l_err_rec.l_err_num <> 0) then
2133 raise process_error;
2134 end if;
2135
2136
2137 DECLARE
2138 CURSOR c_receive_dists IS
2139 SELECT
2140 decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_rate,nvl(pod.rate,1)) "EXCHG_RATE",
2141 decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_date,pod.rate_date) "EXCHG_DATE",
2142 -- J Changes --------------------------------------------------------------------------------------
2143 DECODE (POLL.MATCHING_BASIS, 'AMOUNT', 1, -- Changed for Complex work procurement
2144 'QUANTITY', 0)"SERVICE_FLAG",
2145 ---------------------------------------------------------------------------------------------------
2146 POD.po_distribution_id "PO_DISTRIBUTION_ID",
2147 POLL.line_location_id "PO_LINE_LOCATION_ID",
2148 POD.code_combination_id "EXPENSE_ACCOUNT_ID",
2149 POD.destination_type_code "DESTINATION_TYPE_CODE",
2150 RP.receiving_account_id "RECEIVING_ACCOUNT_ID",
2151 POD.accrual_account_id "ACCRUAL_ACCOUNT_ID",
2152 nvl(POD.budget_account_id,-1) "ENCUMBRANCE_ACCOUNT_ID",
2153 -- J Changes --------------------------------------------------------------------------------------
2154 decode(l_doc_level,'D', 1, DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work procurement
2155 'AMOUNT', POD.AMOUNT_ORDERED/POLL.AMOUNT,
2156 'QUANTITY',pod.quantity_ordered/poll.quantity))
2157 * i_ae_txn_rec.primary_quantity "DIST_QUANTITY",
2158 po_tax_sv.get_tax('PO',pod.po_distribution_id) /
2159 DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work procurement
2160 'AMOUNT', POD.AMOUNT_ORDERED,
2161 'QUANTITY',POD.QUANTITY_ORDERED) "TAX"
2162 ---------------------------------------------------------------------------------------------------
2163 FROM
2164 po_distributions_all pod,
2165 po_line_locations_all poll,
2166 -- J Changes --------------------------------------------------------------------------------------
2167 PO_LINES_ALL POL,
2168 ---------------------------------------------------------------------------------------------------
2169 rcv_parameters rp
2170 WHERE
2171 -- begin fix for perf bug 2581067
2172 (
2173 (l_doc_level = 'D' AND pod.po_distribution_id = l_doc_id)
2174 OR (l_doc_level = 'S' AND poll.line_location_id = l_doc_id)
2175 )
2176 -- end fix for perf bug 2581067.
2177 and pod.line_location_id = poll.line_location_id
2178 -- J Changes --------------------------------------------------------------------------------------
2179 AND POLL.PO_LINE_ID = POL.PO_LINE_ID
2180 ---------------------------------------------------------------------------------------------------
2181 and rp.organization_id = pod.destination_organization_id
2182 and pod.destination_type_code in ('EXPENSE')
2183 and nvl(POLL.accrue_on_receipt_flag,'N') = 'Y'
2184 /* and nvl(POD.accrue_on_receipt_flag,'N') = 'Y' */;
2185 BEGIN
2186 FOR c_receipts_rec IN c_receive_dists LOOP
2187 l_stmt_num := 62;
2188 l_ae_line_rec.actual_flag := NULL;
2189 l_ae_line_rec.po_distribution_id := c_receipts_rec.po_distribution_id;
2190
2191 ---------------------------------------------------------
2192 -- The PO Price is in terms of the PO UOM
2193 -- Convert it in terms of the primary UOM for the item
2194 ---------------------------------------------------------
2195
2196 SELECT
2197 DECODE(C_RECEIPTS_REC.SERVICE_FLAG, 1, 1,
2198 poll.price_override * rt.source_doc_quantity / rt.primary_quantity)
2199 INTO
2200 l_po_price
2201 FROM
2202 rcv_transactions rt,
2203 po_line_locations_all poll
2204 WHERE
2205 rt.transaction_id = i_ae_txn_rec.transaction_id
2206 AND rt.po_line_location_id = poll.line_location_id;
2207
2208
2209
2210 l_ae_line_rec.transaction_value := c_receipts_rec.dist_quantity * (l_po_price + c_receipts_rec.tax);
2211 l_curr_rec.currency_conv_rate := c_receipts_rec.exchg_rate;
2212 l_curr_rec.currency_conv_date := c_receipts_rec.exchg_date;
2213 l_curr_rec.currency_conv_type := i_ae_txn_rec.currency_conv_type;
2214
2215 IF (l_stage = 1) THEN
2216
2217 ---------------------------------------------------------
2218 -- For expense destinations, stage 1 accounting is as follows:
2219 -- Dr. Receiving Inspection qty@po
2220 -- Cr. Accrual qty@po
2221 ---------------------------------------------------------
2222
2223 IF (l_net_receipt = 1) THEN
2224 l_dr_flag := TRUE;
2225 ELSE
2226 l_dr_flag := FALSE;
2227 END IF;
2228
2229 l_ae_line_rec.account := c_receipts_rec.receiving_account_id;
2230 l_ae_line_rec.ae_line_type := 5;
2231
2232 IF l_debug_flag = 'Y' THEN
2233 IF (l_dr_flag) THEN
2234 fnd_file.put_line(fnd_file.log,'DR RI: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2235 ELSE
2236 fnd_file.put_line(fnd_file.log,'CR RI: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2237 END IF;
2238 END IF;
2239
2240 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
2241 not the primary quantity on the transaction */
2242 IF (c_receipts_rec.dist_quantity = 0) THEN
2243 l_ae_line_rec.rate_or_amount := 0;
2244 ELSE
2245 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity;
2246 END IF;
2247
2248 CSTPAPPR.insert_account (i_ae_txn_rec,
2249 l_curr_rec,
2250 l_dr_flag,
2251 l_ae_line_rec,
2252 l_ae_line_tbl,
2253 l_err_rec);
2254 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
2255 THEN
2256 raise process_error;
2257 END IF;
2258
2259
2260 l_dr_flag := not l_dr_flag;
2261 l_ae_line_rec.account := c_receipts_rec.accrual_account_id;
2262 l_ae_line_rec.ae_line_type := 16;
2263
2264 IF l_debug_flag = 'Y' THEN
2265 IF (l_dr_flag) THEN
2266 fnd_file.put_line(fnd_file.log,'DR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2267 ELSE
2268 fnd_file.put_line(fnd_file.log,'CR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2269 END IF;
2270 END IF;
2271
2272 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
2273 not the primary quantity on the transaction */
2274 IF (c_receipts_rec.dist_quantity = 0) THEN
2275 l_ae_line_rec.rate_or_amount := 0;
2276 ELSE
2277 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity;
2278 END IF;
2279
2280
2281
2282 CSTPAPPR.insert_account (i_ae_txn_rec,
2283 l_curr_rec,
2284 l_dr_flag,
2285 l_ae_line_rec,
2286 l_ae_line_tbl,
2287 l_err_rec);
2288
2289 -- Take care of rounding errors.
2290 -- -----------------------------
2291 l_stmt_num := 80;
2292 balance_account (l_ae_line_tbl,
2293 l_err_rec);
2294
2295 -- check error
2296 if (l_err_rec.l_err_num <> 0) then
2297 raise process_error;
2298 end if;
2299
2300
2301 ELSE /*if stage <> 1*/
2302
2303 ------------------------------------------------------------
2304 -- For Expense destinations, stage 2 accounting is as follows :
2305 -- Dr. Expense qty@po
2306 -- Cr. Receiving Inspection qty@po
2307 ------------------------------------------------------------
2308
2309 IF (l_net_receipt = 1) THEN
2310 l_dr_flag := TRUE;
2311 ELSE
2312 l_dr_flag := FALSE;
2313 END IF;
2314
2315 l_ae_line_rec.account := c_receipts_rec.expense_account_id;
2316 l_ae_line_rec.ae_line_type := 20;
2317
2318 IF (c_receipts_rec.dist_quantity = 0) THEN
2319 l_ae_line_rec.rate_or_amount := 0;
2320 ELSE
2321 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity;
2322 END IF;
2323
2324 IF l_debug_flag = 'Y' THEN
2325 IF (l_dr_flag) THEN
2326 fnd_file.put_line(fnd_file.log,'DR Expense: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2327 ELSE
2328 fnd_file.put_line(fnd_file.log,'CR Expense: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2329 END IF;
2330 END IF;
2331
2332
2333 CSTPAPPR.insert_account (i_ae_txn_rec,
2334 l_curr_rec,
2335 l_dr_flag,
2336 l_ae_line_rec,
2337 l_ae_line_tbl,
2338 l_err_rec);
2339
2340 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
2341 THEN
2342 raise process_error;
2343 END IF;
2344
2345 l_dr_flag := not l_dr_flag;
2346 l_ae_line_rec.account := c_receipts_rec.receiving_account_id;
2347 l_ae_line_rec.ae_line_type := 5;
2348 IF (c_receipts_rec.dist_quantity = 0) THEN
2349 l_ae_line_rec.rate_or_amount := 0;
2350 ELSE
2351 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity;
2352 END IF;
2353
2354 IF l_debug_flag = 'Y' THEN
2355 IF (l_dr_flag) THEN
2356 fnd_file.put_line(fnd_file.log,'DR RI: '||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 RI: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2359 END IF;
2360 END IF;
2361
2362 CSTPAPPR.insert_account (i_ae_txn_rec,
2363 l_curr_rec,
2364 l_dr_flag,
2365 l_ae_line_rec,
2366 l_ae_line_tbl,
2367 l_err_rec);
2368 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
2369 THEN
2370 raise process_error;
2371 END IF;
2372
2373 -- Take care of rounding errors.
2374 -- -----------------------------
2375 l_stmt_num := 80;
2376 balance_account (l_ae_line_tbl,
2377 l_err_rec);
2378
2379 -- check error
2380 if (l_err_rec.l_err_num <> 0) then
2381 raise process_error;
2382 end if;
2383
2384 l_stmt_num := 85;
2385 -----------------------------------------------------------
2386 -- Encumbrance entries
2387 -- First check if encumbrance is on
2388 -- Get the encumbrance type flag and budget account
2389 -----------------------------------------------------------
2390
2391 -- If the budget account was not specified at PO creation
2392 -- no encumbrance reversal is necessary
2393
2394 IF (c_receipts_rec.encumbrance_account_id = -1) THEN
2395 IF l_debug_flag = 'Y' THEN
2396 fnd_file.put_line(fnd_file.log,'No Encumbrance account at PO level');
2397 END IF;
2398 ELSE
2399
2400 CSTPAPPR.check_encumbrance(
2401 i_transaction_id => i_ae_txn_rec.transaction_id,
2402 i_set_of_books_id => i_ae_txn_rec.set_of_books_id,
2403 i_period_name => i_ae_txn_rec.accounting_period_name,
2404 i_encumbrance_account_id => c_receipts_rec.encumbrance_account_id,
2405 o_enc_flag => l_enc_flag,
2406 o_purch_encumbrance_type_id => l_purch_encumbrance_type_id,
2407 o_purch_encumbrance_flag => l_purch_encumbrance_flag,
2408 o_ae_err_rec => l_err_rec);
2409 if (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
2410 then
2411 raise process_error;
2412 end if;
2413
2414 /* Commented and replaced for forward port bug 5768550
2415 IF (l_enc_flag = 'Y' and l_purch_encumbrance_flag = 'Y') then*/
2416 IF l_purch_encumbrance_flag = 'Y' THEN
2417
2418 ------------------------------------------------------------------
2419 -- For encumbrance :
2420 -- At the time of PO creation, if encumbrance is turned on,
2421 -- encumbrance is created for the quantity ordered @po price
2422 -- At the time of delivery , if the reversal flag is turned on,
2423 -- reversing entries need to be generated for the quantity delivered
2424 -- but not exceeding the quantity encumbered.
2425 -------------------------------------------------------------------
2426
2427 -------------------------------------------------------------------
2428 -- Get the quantity delivered before the current transaction
2429 -- This quantity will be used to determine the amount that has
2430 -- been reversed and the quantity available to unencumber.
2431 -------------------------------------------------------------------
2432
2433 l_quantity_delivered := CSTPAPPR.get_net_del_qty(
2434 c_receipts_rec.po_distribution_id,
2435 i_ae_txn_rec.transaction_id);
2436 IF l_debug_flag = 'Y' THEN
2437 fnd_file.put_line(fnd_file.log,'Delivered Quantity: '||to_char(l_quantity_delivered));
2438 END IF;
2439
2440 l_stmt_num := 86;
2441
2442 ------------------------------------------------------------------
2443 -- cases possible:
2444 -- I. current transaction is a net deliver transaction
2445 -- a. quantity ordered > quantity delivered
2446 -- 1. quantity delivered after the current txn becomes > ordered
2447 -- qty to unencumber = (ordered - delivered)
2448 -- 2. quantity delivered after the current txn remains < ordered
2449 -- qty to unencumber = qty of current txn
2450 -- b. quantity ordered < quantity delivered
2451 -- 1. quantity delivered remains > ordered
2452 -- qty to unencumber = 0
2453 -- II. current transaction is a net return transaction
2454 -- a. quantity ordered > quantity delivered
2455 -- 1. quantity delivered remains < quantity delivered
2456 -- qty to unencumber = qty of current txn
2457 -- b. quantity ordered < quantity delivered
2458 -- 1. quantity delivered after current txn becomes < ordered
2459 -- qty to unencumber = qty of current txn - (qty del - qty ord)
2460 -- 2. quantity delivered after current txn remains > ordered
2461 -- qty to unencumber = 0
2462 -- Finally, Convert the quantity into Primary UOM
2463 ---------------------------------------------------------------------------------
2464 -- J Changes ---------------------------------------------------------------------------
2465 -- Compute Net Delivered Amount for Service Line Types
2466 IF C_RECEIPTS_REC.SERVICE_FLAG <> 1 THEN
2467 SELECT
2468 decode (l_net_receipt,
2469 1,
2470 least(
2471 abs(rt.source_doc_quantity),
2472 greatest(POD.quantity_ordered-l_quantity_delivered,0)
2473 ),
2474 -1,
2475 greatest(
2476 (least(POD.quantity_ordered-l_quantity_delivered,0) + abs(rt.source_doc_quantity)),
2477 0
2478 ),
2479 0
2480 ) * rt.primary_quantity/rt.source_doc_quantity * l_po_price,
2481 nvl(POD.rate, 1),
2482 pod.rate_date
2483 INTO
2484 l_encum_amount,
2485 l_po_rate,
2486 l_po_rate_date
2487 FROM
2488 po_headers_all POH,
2489 po_lines_all POL,
2490 po_line_locations_all POLL,
2491 po_distributions_all POD,
2492 rcv_transactions RT
2493 WHERE
2494 POH.po_header_id = POD.po_header_id AND
2495 POL.po_line_id = POD.po_line_id AND
2496 POLL.line_location_id = POD.line_location_id AND
2497 POD.po_distribution_id = c_receipts_rec.po_distribution_id AND
2498 nvl(POLL.accrue_on_receipt_flag,'N') = 'Y' AND
2499 /*nvl(POD.accrue_on_receipt_flag,'N') = 'Y' AND */
2500 RT.transaction_id = i_ae_txn_rec.transaction_id AND
2501 POD.destination_type_code = 'EXPENSE';
2502
2503 ELSE -- Service Line Types
2504 SELECT
2505 decode (l_net_receipt,
2506 1,
2507 least(
2508 abs(RT.AMOUNT),
2509 greatest(POD.AMOUNT_ORDERED - L_QUANTITY_DELIVERED, 0)
2510 ),
2511 -1,
2512 greatest(
2513 (least(POD.AMOUNT_ORDERED - L_QUANTITY_DELIVERED, 0) + abs(rt.AMOUNT)),
2514 0
2515 ),
2516 0
2517 ) * l_po_price,
2518 nvl(POD.rate, 1),
2519 pod.rate_date
2520 INTO
2521 l_encum_amount,
2522 l_po_rate,
2523 l_po_rate_date
2524 FROM
2525 po_headers_all POH,
2526 po_lines_all POL,
2527 po_line_locations_all POLL,
2528 po_distributions_all POD,
2529 rcv_transactions RT
2530 WHERE
2531 POH.po_header_id = POD.po_header_id
2532 AND POL.po_line_id = POD.po_line_id
2533 AND POLL.line_location_id = POD.line_location_id
2534 AND POD.po_distribution_id = c_receipts_rec.po_distribution_id
2535 AND nvl(POLL.accrue_on_receipt_flag,'N') = 'Y'
2536 /*AND nvl(POD.accrue_on_receipt_flag,'N') = 'Y' */
2537 AND RT.transaction_id = i_ae_txn_rec.transaction_id
2538 AND POD.destination_type_code = 'EXPENSE';
2539 END IF;
2540 ----------------------------------------------------------------------------------------------
2541
2542 IF (l_net_receipt = 1) then
2543 l_dr_flag := FALSE;
2544 ELSE
2545 l_dr_flag := TRUE;
2546 END IF;
2547 l_curr_rec.currency_conv_rate := l_po_rate;
2548 l_curr_rec.currency_conv_date := l_po_rate_date;
2549 l_ae_line_rec.transaction_value := l_encum_amount;
2550 l_ae_line_rec.account := c_receipts_rec.encumbrance_account_id;
2551 l_ae_line_rec.ae_line_type := 15;
2552 l_ae_line_rec.actual_flag := 'E';
2553 l_ae_line_rec.encum_type_id := l_purch_encumbrance_type_id;
2554
2555 IF l_debug_flag = 'Y' THEN
2556 IF (l_dr_flag) THEN
2557 fnd_file.put_line(fnd_file.log,'DR Enc: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2558 ELSE
2559 fnd_file.put_line(fnd_file.log,'CR Enc: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
2560 END IF;
2561 END IF;
2562 /* Bug 2686598. Use the quantity in the po_distribution to calcualte rate_or_amount
2563 not the primary quantity on the transaction */
2564 IF (c_receipts_rec.dist_quantity = 0) THEN
2565 l_ae_line_rec.rate_or_amount := 0;
2566 ELSE
2567 l_ae_line_rec.rate_or_amount := l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate / c_receipts_rec.dist_quantity;
2568 END IF;
2569
2570 CSTPAPPR.insert_account (i_ae_txn_rec,
2571 l_curr_rec,
2572 l_dr_flag,
2573 l_ae_line_rec,
2574 l_ae_line_tbl,
2575 l_err_rec);
2576 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
2577 THEN
2578 raise process_error;
2579 END IF;
2580
2581
2582 END IF; /* if enc on */
2583
2584 END IF; /* if po budget acct specified */
2585
2586 END IF; /*stage if*/
2587 --END IF; /* expense if*/
2588 END LOOP;
2589 EXCEPTION
2590 when process_error then
2591 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
2592 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
2593 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
2594
2595 END;
2596
2597
2598 -- Return the lines pl/sql table.
2599 -- ------------------------------
2600 l_stmt_num := 90;
2601 o_ae_line_rec_tbl := l_ae_line_tbl;
2602 IF l_debug_flag = 'Y' THEN
2603 fnd_file.put_line(fnd_file.log,'Create_Rcv_Ae_Lines >>> ');
2604 END IF;
2605
2606 EXCEPTION
2607
2608 when process_error then
2609 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
2610 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
2611 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
2612
2613 when cst_no_po_dist then
2614 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
2615 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
2616 FND_MESSAGE.set_name('BOM', 'CST_NO_RCV_LINE');
2617 o_ae_err_rec.l_err_msg := FND_MESSAGE.Get;
2618
2619 when others then
2620 o_ae_err_rec.l_err_num := SQLCODE;
2621 o_ae_err_rec.l_err_code := '';
2622 o_ae_err_rec.l_err_msg := 'CSTPAPPR.create_rcv_ae_lines : ' || to_char(l_stmt_num) || ':'||
2623 substr(SQLERRM,1,180);
2624
2625 END create_rcv_ae_lines;
2626
2627 -- Retro Changes--------------------------------------------------------------
2628 PROCEDURE create_adj_ae_lines(
2629 p_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
2630 x_ae_line_rec_tbl OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
2631 x_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
2632 ) IS
2633 l_ae_line_tbl CSTPALTY.CST_AE_LINE_TBL_TYPE;
2634 l_ae_line_rec CSTPALTY.CST_AE_LINE_REC_TYPE;
2635 l_curr_rec CSTPALTY.cst_ae_curr_rec_type;
2636 l_err_rec CSTPALTY.cst_ae_err_rec_type;
2637 l_dr_flag BOOLEAN;
2638
2639 -- Retroactive Pricing --
2640 l_current_transaction_value NUMBER;
2641 l_prior_transaction_value NUMBER;
2642
2643 l_current_entered_value NUMBER;
2644 l_prior_entered_value NUMBER;
2645
2646 l_current_accounted_value NUMBER;
2647 l_prior_accounted_value NUMBER;
2648 -------------------------
2649
2650
2651 l_stmt_num NUMBER := 0;
2652 l_debit_account NUMBER;
2653 INVALID_RETRO_ADJ_ACCOUNT EXCEPTION;
2654 PROCESS_ERROR EXCEPTION;
2655
2656 BEGIN
2657
2658 IF l_debug_flag = 'Y' THEN
2659 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Create_Adj_Ae_Lines <<<');
2660 END IF;
2661
2662 -- Initialize the collection
2663 l_ae_line_tbl := CSTPALTY.cst_ae_line_tbl_type();
2664
2665 -- Populate the Currency Structure
2666 l_stmt_num := 10;
2667
2668 select
2669 currency_code
2670 into
2671 l_curr_rec.pri_currency
2672 from
2673 gl_sets_of_books
2674 where
2675 set_of_books_id = p_ae_txn_rec.set_of_books_id;
2676
2677 l_stmt_num := 20;
2678
2679 l_curr_rec.alt_currency := p_ae_txn_rec.currency_code;
2680 l_curr_rec.currency_conv_date := p_ae_txn_rec.currency_conv_date;
2681 l_curr_rec.currency_conv_type := p_ae_txn_rec.currency_conv_type;
2682 l_curr_rec.currency_conv_rate := p_ae_txn_rec.currency_conv_rate;
2683
2684
2685 IF l_debug_flag = 'Y' THEN
2686 FND_FILE.PUT_LINE(FND_FILE.LOG, l_curr_rec.pri_currency || ' '||l_curr_rec.alt_currency);
2687 END IF;
2688
2689 -- Populate the Accounting Line Record
2690
2691 -- For ADJUST_RECEIVE events, the accounting is as follows:
2692 -- Dr Periodic Retroactive Adjustment Account
2693 -- Cr Accrual
2694
2695 l_stmt_num := 30;
2696
2697 -- Get the debit account
2698 SELECT
2699 nvl(RETRO_PRICE_ADJ_ACCOUNT, -1)
2700 INTO
2701 l_debit_account
2702 FROM
2703 CST_ORG_COST_GROUP_ACCOUNTS
2704 WHERE
2705 LEGAL_ENTITY_ID = P_AE_TXN_REC.LEGAL_ENTITY_ID
2706 AND COST_TYPE_ID = P_AE_TXN_REC.COST_TYPE_ID
2707 AND COST_GROUP_ID = P_AE_TXN_REC.COST_GROUP_ID;
2708
2709 IF l_debug_flag = 'Y' THEN
2710 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Debit Account: '||to_char(l_debit_account));
2711 END IF;
2712
2713 IF l_debit_account = -1 THEN
2714 RAISE INVALID_RETRO_ADJ_ACCOUNT;
2715 END IF;
2716
2717 l_stmt_num := 40;
2718 -- Debit
2719
2720 l_dr_flag := TRUE;
2721 -- The Line Type is seeded in MFG_LOOKUPS (CST_ACCOUNTING_LINE_TYPE)
2722 l_ae_line_rec.ae_line_type := 32;
2723
2724 l_ae_line_rec.account := l_debit_account;
2725
2726 l_current_transaction_value := p_ae_txn_rec.primary_quantity * p_ae_txn_rec.unit_price;
2727 l_prior_transaction_value := p_ae_txn_rec.primary_quantity * p_ae_txn_rec.prior_unit_price;
2728
2729 -- Adjustment Amount is computed as:
2730 -- Round(Round(unit_price * qty) * rate) - Round(Round(prior_unit_price*qty) * rate)
2731
2732 -- Rounding is done using the Minimum accounting unit or currency precision
2733 -- Rounded Current_Transaction Value
2734 select
2735 decode(l_curr_rec.alt_currency,NULL, NULL,
2736 l_curr_rec.pri_currency, NULL,
2737 decode(c2.minimum_accountable_unit,
2738 NULL,
2739 round(l_current_transaction_value, c2.precision),
2740 round(l_current_transaction_value /c2.minimum_accountable_unit)
2741 * c2.minimum_accountable_unit )),
2742 decode(c1.minimum_accountable_unit,
2743 NULL, round(l_current_transaction_value * l_curr_rec.currency_conv_rate, c1.precision),
2744 round(l_current_transaction_value * l_curr_rec.currency_conv_rate/c1.minimum_accountable_unit)
2745 * c1.minimum_accountable_unit ),
2746 decode(l_curr_rec.alt_currency,NULL, NULL,
2747 l_curr_rec.pri_currency, NULL,
2748 decode(c2.minimum_accountable_unit,
2749 NULL,
2750 round(l_prior_transaction_value, c2.precision),
2751 round(l_prior_transaction_value /c2.minimum_accountable_unit)
2752 * c2.minimum_accountable_unit )),
2753 decode(c1.minimum_accountable_unit,
2754 NULL, round(l_prior_transaction_value * l_curr_rec.currency_conv_rate, c1.precision),
2755 round(l_prior_transaction_value * l_curr_rec.currency_conv_rate/c1.minimum_accountable_unit)
2756 * c1.minimum_accountable_unit )
2757 into
2758 l_current_entered_value,
2759 l_current_accounted_value,
2760 l_prior_entered_value,
2761 l_prior_accounted_value
2762 from
2763 fnd_currencies c1,
2764 fnd_currencies c2
2765 where
2766 c1.currency_code = l_curr_rec.pri_currency
2767 and c2.currency_code = decode(l_curr_rec.alt_currency, NULL,
2768 l_curr_rec.pri_currency,
2769 l_curr_rec.alt_currency);
2770
2771 l_ae_line_rec.accounted_value := l_current_accounted_value - l_prior_accounted_value;
2772 l_ae_line_rec.entered_value := l_current_entered_value - l_prior_entered_value;
2773
2774 l_ae_line_rec.transaction_value := l_ae_line_rec.accounted_value;
2775
2776 IF l_debug_flag = 'Y' THEN
2777 FND_FILE.PUT_LINE(FND_FILE.log, 'l_ae_line_rec.accounted_value: '||to_char(l_ae_line_rec.accounted_value));
2778 END IF;
2779
2780 l_ae_line_rec.source_table := 'RAE';
2781 l_ae_line_rec.source_id := p_ae_txn_rec.transaction_id;
2782
2783 IF p_ae_txn_rec.primary_quantity <> 0 THEN
2784 l_ae_line_rec.rate_or_amount := p_ae_txn_rec.unit_price * l_curr_rec.currency_conv_rate;
2785 ELSE
2786 l_ae_line_rec.rate_or_amount := 0 ;
2787 END IF;
2788
2789 l_ae_line_rec.po_distribution_id := p_ae_txn_rec.po_distribution_id;
2790
2791 l_stmt_num := 50;
2792
2793 CSTPAPPR.insert_account (p_ae_txn_rec,
2794 l_curr_rec,
2795 l_dr_flag,
2796 l_ae_line_rec,
2797 l_ae_line_tbl,
2798 l_err_rec);
2799
2800 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) THEN
2801 raise process_error;
2802 END IF;
2803
2804 -- Credit
2805 -- Credit Account is the accrual account (stamped as Credit_Account on the
2806 -- transaction
2807 l_stmt_num := 60;
2808
2809 l_dr_flag := NOT l_dr_flag;
2810
2811 l_ae_line_rec.account := p_ae_txn_rec.credit_account;
2812 l_ae_line_rec.ae_line_type := 16;
2813 IF l_debug_flag = 'Y' THEN
2814 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Credit Account: '||to_char(l_ae_line_rec.account));
2815 END IF;
2816
2817 l_stmt_num := 70;
2818
2819 CSTPAPPR.insert_account (p_ae_txn_rec,
2820 l_curr_rec,
2821 l_dr_flag,
2822 l_ae_line_rec,
2823 l_ae_line_tbl,
2824 l_err_rec);
2825
2826 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) THEN
2827 raise process_error;
2828 END IF;
2829
2830 -- Copy the local structure to the Output
2831 x_ae_line_rec_tbl := l_ae_line_tbl;
2832
2833 IF l_debug_flag = 'Y' THEN
2834 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Create_Adj_Ae_Lines >>>');
2835 END IF;
2836
2837 EXCEPTION
2838 WHEN INVALID_RETRO_ADJ_ACCOUNT THEN
2839 l_err_rec.l_err_num := SQLCODE;
2840 l_err_rec.l_err_code := 'No Periodic Retroactive Adjustment Account Specified';
2841 l_err_rec.l_err_msg := 'CSTPAPPR:( '||to_char(l_stmt_num)||' ): ' || l_err_rec.l_err_code;
2842 x_ae_err_rec := l_err_rec;
2843 FND_FILE.PUT_LINE(FND_FILE.LOG, l_err_rec.l_err_msg);
2844
2845 WHEN PROCESS_ERROR THEN
2846 x_ae_err_rec := l_err_rec;
2847 FND_FILE.PUT_LINE(FND_FILE.LOG, l_err_rec.l_err_msg);
2848
2849 WHEN OTHERS THEN
2850 l_err_rec.l_err_num := SQLCODE;
2851 l_err_rec.l_err_code := '';
2852 l_err_rec.l_err_msg := 'CSTPAPPR:( '||to_char(l_stmt_num)||' ): ' || to_char(SQLCODE);
2853 x_ae_err_rec := l_err_rec;
2854 FND_FILE.PUT_LINE(FND_FILE.LOG, l_err_rec.l_err_msg);
2855
2856 END create_adj_ae_lines;
2857
2858 /*LCM CHANGES */
2859
2860 PROCEDURE create_lc_adj_ae_lines(
2861 p_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
2862 x_ae_line_rec_tbl OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
2863 x_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
2864 ) IS
2865 l_ae_line_tbl CSTPALTY.CST_AE_LINE_TBL_TYPE;
2866 l_ae_line_rec CSTPALTY.CST_AE_LINE_REC_TYPE;
2867 l_curr_rec CSTPALTY.cst_ae_curr_rec_type;
2868 l_err_rec CSTPALTY.cst_ae_err_rec_type;
2869 l_dr_flag BOOLEAN;
2870
2871
2872 l_current_transaction_value NUMBER;
2873 l_prior_transaction_value NUMBER;
2874
2875 l_stmt_num NUMBER := 0;
2876 PROCESS_ERROR EXCEPTION;
2877
2878 BEGIN
2879
2880 IF l_debug_flag = 'Y' THEN
2881 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Create_LC_Adj_Ae_Lines <<<');
2882 END IF;
2883
2884 -- Initialize the collection
2885 l_ae_line_tbl := CSTPALTY.cst_ae_line_tbl_type();
2886
2887 -- Populate the Currency Structure
2888 l_stmt_num := 10;
2889
2890 select
2891 currency_code
2892 into
2893 l_curr_rec.pri_currency
2894 from
2895 gl_sets_of_books
2896 where
2897 set_of_books_id = p_ae_txn_rec.set_of_books_id;
2898
2899 l_stmt_num := 20;
2900
2901 l_curr_rec.alt_currency := p_ae_txn_rec.currency_code;
2902 l_curr_rec.currency_conv_date := p_ae_txn_rec.currency_conv_date;
2903 l_curr_rec.currency_conv_type := p_ae_txn_rec.currency_conv_type;
2904 l_curr_rec.currency_conv_rate := p_ae_txn_rec.currency_conv_rate;
2905
2906
2907 IF l_debug_flag = 'Y' THEN
2908 FND_FILE.PUT_LINE(FND_FILE.LOG, l_curr_rec.pri_currency || ' '||l_curr_rec.alt_currency);
2909 END IF;
2910
2911 l_stmt_num := 30;
2912
2913 l_dr_flag := TRUE;
2914 l_ae_line_rec.account := p_ae_txn_rec.debit_account;
2915 l_current_transaction_value := p_ae_txn_rec.primary_quantity * p_ae_txn_rec.unit_price;
2916 l_prior_transaction_value := p_ae_txn_rec.primary_quantity * p_ae_txn_rec.prior_unit_price;
2917 l_ae_line_rec.transaction_value := l_current_transaction_value - l_prior_transaction_value;
2918 IF( p_ae_txn_rec.event_type_id = 'PAC LC ADJ REC') THEN
2919 IF (l_ae_line_rec.transaction_value>=0) THEN
2920 l_ae_line_rec.ae_line_type := 5;
2921 ELSE
2922 l_ae_line_rec.ae_line_type := 38;
2923 END IF;
2924 ELSIF( p_ae_txn_rec.event_type_id = 'PAC LC ADJ DEL ASSET') THEN
2925 IF (l_ae_line_rec.transaction_value>=0) THEN
2926 l_ae_line_rec.ae_line_type := 38;
2927 ELSE
2928 l_ae_line_rec.ae_line_type := 5;
2929 END IF;
2930 ELSIF( p_ae_txn_rec.event_type_id = 'PAC LC ADJ DEL EXP') THEN
2931 IF (l_ae_line_rec.transaction_value>=0) THEN
2932 l_ae_line_rec.ae_line_type := 2;
2933 ELSE
2934 l_ae_line_rec.ae_line_type := 5;
2935 END IF;
2936 END IF;
2937
2938
2939 IF l_debug_flag = 'Y' THEN
2940 FND_FILE.PUT_LINE(FND_FILE.log, 'l_ae_line_rec.accounted_value: '||to_char(l_ae_line_rec.accounted_value));
2941 END IF;
2942
2943 l_ae_line_rec.source_table := 'RAE';
2944 l_ae_line_rec.source_id := p_ae_txn_rec.transaction_id;
2945
2946 IF p_ae_txn_rec.primary_quantity <> 0 THEN
2947 l_ae_line_rec.rate_or_amount := p_ae_txn_rec.unit_price * l_curr_rec.currency_conv_rate;
2948 ELSE
2949 l_ae_line_rec.rate_or_amount := 0 ;
2950 END IF;
2951
2952 l_ae_line_rec.po_distribution_id := p_ae_txn_rec.po_distribution_id;
2953
2954 l_stmt_num := 50;
2955
2956 CSTPAPPR.insert_account (p_ae_txn_rec,
2957 l_curr_rec,
2958 l_dr_flag,
2959 l_ae_line_rec,
2960 l_ae_line_tbl,
2961 l_err_rec);
2962
2963 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) THEN
2964 raise process_error;
2965 END IF;
2966
2967 l_stmt_num := 60;
2968
2969 l_dr_flag := NOT l_dr_flag;
2970
2971 l_ae_line_rec.account := p_ae_txn_rec.credit_account;
2972 IF( p_ae_txn_rec.event_type_id = 'PAC LC ADJ REC') THEN
2973 IF (l_ae_line_rec.transaction_value>=0) THEN
2974 l_ae_line_rec.ae_line_type := 38;
2975 ELSE
2976 l_ae_line_rec.ae_line_type := 5;
2977 END IF;
2978 ELSIF( p_ae_txn_rec.event_type_id = 'PAC LC ADJ DEL ASSET') THEN
2979 IF (l_ae_line_rec.transaction_value>=0) THEN
2980 l_ae_line_rec.ae_line_type := 5;
2981 ELSE
2982 l_ae_line_rec.ae_line_type := 38;
2983 END IF;
2984 ELSIF( p_ae_txn_rec.event_type_id = 'PAC LC ADJ DEL EXP') THEN
2985 IF (l_ae_line_rec.transaction_value>=0) THEN
2986 l_ae_line_rec.ae_line_type := 5;
2987 ELSE
2988 l_ae_line_rec.ae_line_type := 2;
2989 END IF;
2990 END IF;
2991
2992 IF l_debug_flag = 'Y' THEN
2993 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Credit Account: '||to_char(l_ae_line_rec.account));
2994 END IF;
2995
2996 l_stmt_num := 70;
2997
2998 CSTPAPPR.insert_account (p_ae_txn_rec,
2999 l_curr_rec,
3000 l_dr_flag,
3001 l_ae_line_rec,
3002 l_ae_line_tbl,
3003 l_err_rec);
3004
3005 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null) THEN
3006 raise process_error;
3007 END IF;
3008
3009 -- Copy the local structure to the Output
3010 x_ae_line_rec_tbl := l_ae_line_tbl;
3011
3012 IF l_debug_flag = 'Y' THEN
3013 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Create_LC_Adj_Ae_Lines >>>');
3014 END IF;
3015
3016 EXCEPTION
3017 WHEN PROCESS_ERROR THEN
3018 x_ae_err_rec := l_err_rec;
3019 FND_FILE.PUT_LINE(FND_FILE.LOG, l_err_rec.l_err_msg);
3020
3021 WHEN OTHERS THEN
3022 l_err_rec.l_err_num := SQLCODE;
3023 l_err_rec.l_err_code := '';
3024 l_err_rec.l_err_msg := 'CSTPAPPR:( '||to_char(l_stmt_num)||' ): ' || to_char(SQLCODE);
3025 x_ae_err_rec := l_err_rec;
3026 FND_FILE.PUT_LINE(FND_FILE.LOG, l_err_rec.l_err_msg);
3027
3028 END create_lc_adj_ae_lines;
3029 /*============================================================================+
3030 | This procedure processes the transaction data and creates accounting entry |
3031 | lines in the form of PL/SQL table and returns to the main procedure. |
3032 | This procedure processes the period end accruals |
3033 | This is called during the period close process |
3034 |============================================================================*/
3035
3036 PROCEDURE create_per_end_ae_lines(
3037 i_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
3038 o_ae_line_rec_tbl OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
3039 o_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
3040 ) IS
3041 l_ae_line_tbl CSTPALTY.CST_AE_LINE_TBL_TYPE;
3042 l_ae_line_rec CSTPALTY.CST_AE_LINE_REC_TYPE;
3043 l_curr_rec CSTPALTY.cst_ae_curr_rec_type;
3044 l_err_rec CSTPALTY.cst_ae_err_rec_type;
3045
3046 l_doc_level VARCHAR2(1);
3047 l_doc_id NUMBER;
3048 l_dist_count NUMBER;
3049 l_purch_encumbrance_type_id NUMBER;
3050 l_purch_encumbrance_flag VARCHAR2(1);
3051 l_enc_flag VARCHAR2(1);
3052 l_bud_enc_flag VARCHAR2(1);
3053 l_dr_flag BOOLEAN;
3054
3055 l_stmt_num NUMBER;
3056
3057 l_po_uom_factor NUMBER;
3058 l_rcv_uom_factor NUMBER;
3059 l_period_end_date DATE;
3060 l_accrual_qty NUMBER;
3061 l_encum_qty NUMBER;
3062
3063 l_return_status VARCHAR2(1);
3064 l_msg_count NUMBER;
3065 l_msg_data VARCHAR2(240);
3066
3067 process_error EXCEPTION;
3068 CST_NO_PO_DIST EXCEPTION;
3069
3070 BEGIN
3071
3072 IF l_debug_flag = 'Y' THEN
3073 fnd_file.put_line(fnd_file.log,'Create_Per_End_Ae_Lines <<<');
3074 END IF;
3075
3076 -- Initialize API return status to success
3077 l_return_status := FND_API.G_RET_STS_SUCCESS;
3078
3079 l_ae_line_tbl := CSTPALTY.cst_ae_line_tbl_type();
3080
3081 -- Initialize local variables.
3082 -- ---------------------------
3083 l_err_rec.l_err_num := 0;
3084 l_err_rec.l_err_code := '';
3085 l_err_rec.l_err_msg := '';
3086
3087 -- Populate the Currency Record Type
3088 -- ---------------------------------
3089 l_stmt_num := 10;
3090
3091 select currency_code
3092 into l_curr_rec.pri_currency
3093 from gl_sets_of_books
3094 where set_of_books_id = i_ae_txn_rec.set_of_books_id;
3095
3096 l_curr_rec.alt_currency := i_ae_txn_rec.currency_code;
3097 l_curr_rec.currency_conv_date := i_ae_txn_rec.currency_conv_date;
3098 l_curr_rec.currency_conv_type := i_ae_txn_rec.currency_conv_type;
3099
3100 l_stmt_num := 30;
3101
3102 ------------------------------------------------------------
3103 -- A receipt can be at the shipment level or the distribution level:
3104 -- When receiving and delivering in one step, it is at the distribution level
3105 -- When receiving and delivering as two different steps, it is shipment level
3106 -- Get the document level as 'S' for shipment and 'D' for distribution
3107 ------------------------------------------------------------
3108 -- Get the level
3109
3110 l_stmt_num := 60;
3111
3112 SELECT
3113 decode(rt.po_distribution_id, NULL, 'S', 'D'),
3114 nvl(rt.po_distribution_id, rt.po_line_location_id)
3115 INTO
3116 l_doc_level,
3117 l_doc_id
3118 FROM
3119 rcv_transactions rt
3120 WHERE
3121 rt.transaction_id = i_ae_txn_rec.transaction_id;
3122
3123 IF l_debug_flag = 'Y' THEN
3124 fnd_file.put_line(fnd_file.log,'Document Level: '||l_doc_level);
3125 fnd_file.put_line(fnd_file.log,'Document ID: '||to_char(l_doc_id));
3126 fnd_file.put_line(fnd_file.log,'Quantity: '||to_char(i_ae_txn_rec.primary_quantity));
3127 END IF;
3128
3129 SELECT
3130 count(*)
3131 into
3132 l_dist_count
3133 FROM
3134 po_distributions_all
3135 WHERE
3136 (
3137 (l_doc_level = 'D' AND po_distribution_id = l_doc_id)
3138 OR (l_doc_level = 'S' AND line_location_id = l_doc_id)
3139 )
3140 AND rownum <= 1;
3141
3142 IF (l_dist_count = 0) THEN
3143 IF l_debug_flag = 'Y' THEN
3144 fnd_file.put_line(fnd_file.log,'Error: No distributions for the Document: '||to_char(l_doc_id));
3145 END IF;
3146 RAISE CST_NO_PO_DIST;
3147 END IF;
3148
3149 ------------------------------------------------------------------
3150 -- If the document level is Shipment, get all the distributions
3151 -- for the Shipment, against which the receipt occurred.
3152 -- If the document level is Distribution, get the distribution
3153 -- Loop for each distribution that is NOT accrued at receipt
3154 ------------------------------------------------------------------
3155
3156 DECLARE
3157 ------------------------------------------------------------------
3158 -- Complex Work Procurement changes
3159 -- Whether the shipment is quantity based or amount based, is determined
3160 -- by poll.matching_basis = 'QUANTITY' or poll.matching_basis = 'AMOUNT'
3161 ------------------------------------------------------------------
3162 CURSOR c_receive_dists IS
3163 SELECT
3164 decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_rate,nvl(pod.rate,1)) "EXCHG_RATE",
3165 decode (poll.match_option,'R',i_ae_txn_rec.currency_conv_date,pod.rate_date) "EXCHG_DATE",
3166 DECODE (poll.matching_basis, 'AMOUNT', 1, 0) "SERVICE_FLAG",
3167 POD.po_distribution_id "PO_DISTRIBUTION_ID",
3168 nvl(POD.rate,1) "PO_RATE",
3169 pod.rate_date "PO_DATE",
3170 POLL.line_location_id "PO_LINE_LOCATION_ID",
3171 POD.code_combination_id "EXPENSE_ACCOUNT_ID",
3172 POD.destination_type_code "DESTINATION_TYPE_CODE",
3173 RP.receiving_account_id "RECEIVING_ACCOUNT_ID",
3174 POD.accrual_account_id "ACCRUAL_ACCOUNT_ID",
3175 nvl(POD.budget_account_id,-1) "ENCUMBRANCE_ACCOUNT_ID",
3176 decode(poll.matching_basis,
3177 'AMOUNT', poll.amount - NVL(poll.amount_cancelled,0),
3178 poll.quantity - NVL(poll.quantity_cancelled,0)) "SHIPMENT_QUANTITY",
3179 decode(poll.matching_basis,
3180 'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
3181 pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) "DIST_QUANTITY",
3182 decode(poll.matching_basis,
3183 'AMOUNT', 1,
3184 NVL(poll.price_override, pol.unit_price)) +
3185 (po_tax_sv.get_tax( 'PO', pod.po_distribution_id) /
3186 decode(poll.matching_basis,
3187 'AMOUNT', pod.amount_ordered,
3188 pod.quantity_ordered)) "UNIT_PRICE",
3189 NVL(poll.match_option,'P') "MATCH_OPTION"
3190 FROM
3191 po_distributions_all pod,
3192 po_line_locations_all poll,
3193 po_lines_all pol,
3194 rcv_parameters rp
3195 WHERE
3196 (
3197 (l_doc_level = 'D' AND pod.po_distribution_id = l_doc_id)
3198 OR (l_doc_level = 'S' AND poll.line_location_id = l_doc_id)
3199 )
3200 and pod.line_location_id = poll.line_location_id
3201 and poll.po_line_id = pol.po_line_id
3202 and rp.organization_id = pod.destination_organization_id
3203 and pod.destination_type_code = 'EXPENSE'
3204 and nvl(POLL.accrue_on_receipt_flag,'N') = 'N'
3205 and nvl(POD.accrue_on_receipt_flag,'N') = 'N';
3206
3207 BEGIN
3208 l_stmt_num := 70;
3209
3210 FOR c_receipts_rec IN c_receive_dists LOOP
3211
3212 l_ae_line_rec.actual_flag := NULL;
3213 l_ae_line_rec.po_distribution_id := c_receipts_rec.po_distribution_id;
3214
3215 l_period_end_date := i_ae_txn_rec.accounting_date + 0.99999;
3216
3217 -------------------------------------------------------------------
3218 -- Period End Accrual rewrite changes
3219 -- The procedure CST_PerEndAccruals_PVT.Calculate_AccrualAmount
3220 -- returns the accrual_amount and encum_amount along with quantity_received
3221 -- and quantity_invoiced against the po_distribution_id.
3222 -------------------------------------------------------------------
3223 l_stmt_num := 80;
3224 CST_PerEndAccruals_PVT.Calculate_AccrualAmount(
3225 p_api_version => 1.0,
3226 p_init_msg_list => FND_API.G_FALSE,
3227 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
3228 x_return_status => l_return_status,
3229 x_msg_count => l_msg_count,
3230 x_msg_data => l_msg_data,
3231 p_match_option => c_receipts_rec.match_option,
3232 p_distribution_id => c_receipts_rec.po_distribution_id,
3233 p_shipment_id => c_receipts_rec.po_line_location_id,
3234 p_transaction_id => i_ae_txn_rec.transaction_id,
3235 p_service_flag => c_receipts_rec.service_flag,
3236 p_dist_qty => c_receipts_rec.dist_quantity,
3237 p_shipment_qty => c_receipts_rec.shipment_quantity,
3238 p_end_date => l_period_end_date,
3239 x_accrual_qty => l_accrual_qty,
3240 x_encum_qty => l_encum_qty
3241 );
3242
3243 -- If return status is not success, raise exception
3244 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
3245 l_err_rec.l_err_num := 20001 ;
3246 l_err_rec.l_err_msg := l_msg_data;
3247 raise process_error;
3248 END IF;
3249
3250
3251 l_ae_line_rec.transaction_value := l_accrual_qty * c_receipts_rec.unit_price;
3252 l_ae_line_rec.rate_or_amount := c_receipts_rec.unit_price * c_receipts_rec.exchg_rate;
3253
3254 l_curr_rec.currency_conv_rate := c_receipts_rec.exchg_rate;
3255 l_curr_rec.currency_conv_date := c_receipts_rec.exchg_date;
3256 l_curr_rec.currency_conv_type := i_ae_txn_rec.currency_conv_type;
3257
3258 l_dr_flag := TRUE;
3259
3260 l_ae_line_rec.account := c_receipts_rec.expense_account_id;
3261 l_ae_line_rec.ae_line_type := 20;
3262 IF l_debug_flag = 'Y' THEN
3263 IF (l_dr_flag) THEN
3264 fnd_file.put_line(fnd_file.log,'DR Expense: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
3265 ELSE
3266 fnd_file.put_line(fnd_file.log,'CR Expense: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
3267 END IF;
3268 END IF;
3269
3270 CSTPAPPR.insert_account (i_ae_txn_rec,
3271 l_curr_rec,
3272 l_dr_flag,
3273 l_ae_line_rec,
3274 l_ae_line_tbl,
3275 l_err_rec);
3276 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
3277 THEN
3278 raise process_error;
3279 END IF;
3280
3281 l_dr_flag := not l_dr_flag;
3282
3283 l_ae_line_rec.account := c_receipts_rec.accrual_account_id;
3284 l_ae_line_rec.ae_line_type := 16;
3285
3286 IF l_debug_flag = 'Y' THEN
3287 IF (l_dr_flag) THEN
3288 fnd_file.put_line(fnd_file.log,'DR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
3289 ELSE
3290 fnd_file.put_line(fnd_file.log,'CR Accrual: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
3291 END IF;
3292 END IF;
3293 CSTPAPPR.insert_account (i_ae_txn_rec,
3294 l_curr_rec,
3295 l_dr_flag,
3296 l_ae_line_rec,
3297 l_ae_line_tbl,
3298 l_err_rec);
3299 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
3300 THEN
3301 raise process_error;
3302 END IF;
3303
3304
3305 -- Take care of rounding errors.
3306 -- -----------------------------
3307 l_stmt_num := 80;
3308 balance_account (l_ae_line_tbl,
3309 l_err_rec);
3310
3311 -- check error
3312 if (l_err_rec.l_err_num <> 0) then
3313 raise process_error;
3314 end if;
3315
3316 -- encumbrance
3317 l_stmt_num := 110;
3318
3319 -- If the budget account was not specified at PO creation
3320 -- no encumbrance reversal is necessary
3321
3322 IF (c_receipts_rec.encumbrance_account_id = -1) THEN
3323 IF l_debug_flag = 'Y' THEN
3324 fnd_file.put_line(fnd_file.log,'No Encumbrance account at PO level');
3325 END IF;
3326 ELSE
3327
3328 CSTPAPPR.check_encumbrance(
3329 i_transaction_id => i_ae_txn_rec.transaction_id,
3330 i_set_of_books_id => i_ae_txn_rec.set_of_books_id,
3331 i_period_name => i_ae_txn_rec.accounting_period_name,
3332 i_encumbrance_account_id => c_receipts_rec.encumbrance_account_id,
3333 o_enc_flag => l_enc_flag,
3334 o_purch_encumbrance_type_id => l_purch_encumbrance_type_id,
3335 o_purch_encumbrance_flag => l_purch_encumbrance_flag,
3336 o_ae_err_rec => l_err_rec);
3337 if (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
3338 then
3339 raise process_error;
3340 end if;
3341
3342 /* Commented and replaced for forward port bug 5768550
3343 IF (l_enc_flag = 'Y' and l_purch_encumbrance_flag = 'Y') then*/
3344 IF l_purch_encumbrance_flag = 'Y' THEN
3345
3346 l_ae_line_rec.transaction_value := l_encum_qty * c_receipts_rec.unit_price;
3347 l_curr_rec.currency_conv_rate := c_receipts_rec.po_rate;
3348 l_curr_rec.currency_conv_date := c_receipts_rec.po_date;
3349
3350 l_dr_flag := FALSE;
3351
3352 l_ae_line_rec.account := c_receipts_rec.encumbrance_account_id;
3353 l_ae_line_rec.ae_line_type := 15;
3354 l_ae_line_rec.actual_flag := 'E';
3355 l_ae_line_rec.encum_type_id := l_purch_encumbrance_type_id;
3356
3357 IF l_debug_flag = 'Y' THEN
3358 IF (l_dr_flag) THEN
3359 fnd_file.put_line(fnd_file.log,'DR Encumbrance: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
3360 ELSE
3361 fnd_file.put_line(fnd_file.log,'CR Encumbrance: '||to_char( l_ae_line_rec.transaction_value * l_curr_rec.currency_conv_rate ));
3362 END IF;
3363 END IF;
3364 CSTPAPPR.insert_account (i_ae_txn_rec,
3365 l_curr_rec,
3366 l_dr_flag,
3367 l_ae_line_rec,
3368 l_ae_line_tbl,
3369 l_err_rec);
3370 IF (l_err_rec.l_err_num<>0 and l_err_rec.l_err_num is not null)
3371 THEN
3372 raise process_error;
3373 END IF;
3374 END IF; /*if enc on*/
3375 END IF; /*if budget acct specified */
3376 END LOOP;
3377 EXCEPTION
3378 when process_error then
3379 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
3380 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
3381 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
3382
3383 END;
3384
3385
3386 -- Return the lines pl/sql table.
3387 -- ------------------------------
3388 l_stmt_num := 90;
3389 o_ae_line_rec_tbl := l_ae_line_tbl;
3390 IF l_debug_flag = 'Y' THEN
3391 fnd_file.put_line(fnd_file.log,'Create_Per_End_Ae_Lines >>>');
3392 END IF;
3393
3394 EXCEPTION
3395
3396 when process_error then
3397 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
3398 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
3399 o_ae_err_rec.l_err_msg := l_err_rec.l_err_msg;
3400
3401 when cst_no_po_dist then
3402 o_ae_err_rec.l_err_num := l_err_rec.l_err_num;
3403 o_ae_err_rec.l_err_code := l_err_rec.l_err_code;
3404 FND_MESSAGE.set_name('BOM', 'CST_NO_RCV_LINE');
3405 o_ae_err_rec.l_err_msg := FND_MESSAGE.Get;
3406
3407 when others then
3408 o_ae_err_rec.l_err_num := SQLCODE;
3409 o_ae_err_rec.l_err_code := '';
3410 o_ae_err_rec.l_err_msg := 'CSTPAPPR.create_per_end_ae_lines : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
3411
3412 END create_per_end_ae_lines;
3413
3414 PROCEDURE check_encumbrance(
3415 i_transaction_id IN NUMBER,
3416 i_set_of_books_id IN NUMBER,
3417 i_period_name IN VARCHAR2, --???
3418 i_encumbrance_account_id IN NUMBER,
3419 o_enc_flag OUT NOCOPY VARCHAR2,
3420 o_purch_encumbrance_type_id OUT NOCOPY NUMBER,
3421 o_purch_encumbrance_flag OUT NOCOPY VARCHAR2,
3422 o_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type
3423 )
3424 IS
3425 l_application_id NUMBER;
3426 l_functional_currency_code VARCHAR2(5);
3427 l_accrual_effect_date DATE;
3428 l_accrual_cutoff_date DATE;
3429 l_bud_enc_flag VARCHAR2(1);
3430 l_stmt_num NUMBER;
3431 l_operating_unit NUMBER;
3432 cst_no_enc_account exception;
3433
3434 BEGIN
3435 IF l_debug_flag = 'Y' THEN
3436 fnd_file.put_line(fnd_file.log,'Check_Encumbrance <<<');
3437 fnd_file.put_line(fnd_file.log,'Encumbrance Account: '||to_char(i_encumbrance_account_id));
3438 END IF;
3439
3440 l_stmt_num := 10;
3441 SELECT
3442 decode(status,'I',101,201)
3443 INTO
3444 l_application_id
3445 FROM
3446 fnd_product_installations
3447 WHERE
3448 application_id = 101;
3449
3450 l_stmt_num := 20;
3451
3452
3453 SELECT
3454 NVL(org_id,-1)
3455 into
3456 l_operating_unit
3457 FROM
3458 po_headers_all
3459 WHERE
3460 po_header_id = (select po_header_id from rcv_transactions
3461 where transaction_id = i_transaction_id);
3462
3463
3464 l_stmt_num := 30;
3465 SELECT
3466 SOB.currency_code,
3467 nvl(FSP.purch_encumbrance_flag, 'N'),
3468 nvl(FSP.purch_encumbrance_type_id, 0)
3469 INTO
3470 l_functional_currency_code,
3471 o_purch_encumbrance_flag,
3472 o_purch_encumbrance_type_id
3473 FROM
3474 GL_PERIOD_STATUSES ACR,
3475 GL_PERIOD_TYPES GLPT,
3476 FINANCIALS_SYSTEM_PARAMS_ALL FSP,
3477 GL_SETS_OF_BOOKS SOB
3478 WHERE
3479 GLPT.period_type = ACR.period_type AND
3480 ACR.application_id = l_application_id AND
3481 ACR.set_of_books_id = i_set_of_books_id AND
3482 ACR.period_name = i_period_name AND
3483 FSP.set_of_books_id = SOB.set_of_books_id AND
3484 NVL(FSP.org_id,-1) = l_operating_unit;
3485
3486 /*
3487 Bug 5768550(FP of bug 5722537):- The following code is commented.
3488 l_stmt_num := 40;
3489
3490 IF (o_purch_encumbrance_flag = 'Y') THEN
3491 -- Get profile CREATE_BUDGETARY_ENCUMBRANCES
3492 FND_PROFILE.get('CREATE_BUDGETARY_ENCUMBRANCES', l_bud_enc_flag);
3493 o_enc_flag := nvl(l_bud_enc_flag,'N');
3494 END IF;
3495
3496 IF (o_enc_flag = 'Y' and o_purch_encumbrance_flag = 'Y' and i_encumbrance_account_id = -1) THEN
3497 raise cst_no_enc_account;
3498 END IF;
3499 */
3500
3501 IF l_debug_flag = 'Y' THEN
3502 fnd_file.put_line(fnd_file.log,'Check_Encumbrance >>>');
3503 END IF;
3504 EXCEPTION
3505 WHEN CST_NO_ENC_ACCOUNT THEN
3506 o_ae_err_rec.l_err_num := 30010;
3507 o_ae_err_rec.l_err_code := 'CST_NO_ENC_ACCOUNT';
3508 FND_MESSAGE.set_name('BOM', 'CST_NO_ENC_ACCOUNT');
3509 o_ae_err_rec.l_err_msg := FND_MESSAGE.Get;
3510
3511 WHEN OTHERS THEN
3512 o_ae_err_rec.l_err_num := SQLCODE;
3513 o_ae_err_rec.l_err_code := '';
3514 o_ae_err_rec.l_err_msg := 'CSTPAPPR.check_encumbrance : ' || to_char(l_stmt_num) || ':'|| substr(SQLERRM,1,180);
3515
3516
3517 END check_encumbrance;
3518
3519 -- ===================================================================
3520 -- Insert Account.
3521 -- ===================================================================
3522 PROCEDURE insert_account(
3523 i_ae_txn_rec IN CSTPALTY.cst_ae_txn_rec_type,
3524 i_ae_curr_rec IN CSTPALTY.cst_ae_curr_rec_type,
3525 i_dr_flag IN BOOLEAN,
3526 i_ae_line_rec IN CSTPALTY.cst_ae_line_rec_type,
3527 l_ae_line_tbl IN OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
3528 o_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type)
3529 IS
3530 l_err_rec CSTPALTY.cst_ae_err_rec_type;
3531 l_entered_value NUMBER;
3532 l_accounted_value NUMBER;
3533 l_stmt_num NUMBER;
3534 next_record_avail NUMBER;
3535 invalid_acct_error EXCEPTION;
3536
3537 BEGIN
3538
3539 IF l_debug_flag = 'Y' THEN
3540 fnd_file.put_line(fnd_file.log,'Insert_Account <<< ');
3541 END IF;
3542 -- Initialize variables.
3543 -- ---------------------
3544 l_err_rec.l_err_num := 0;
3545 l_err_rec.l_err_code := '';
3546 l_err_rec.l_err_msg := '';
3547
3548 if (i_ae_line_rec.account = -1) then
3549 raise invalid_acct_error;
3550 end if;
3551
3552 l_stmt_num := 10;
3553 next_record_avail := nvl(l_ae_line_tbl.LAST,0) ;
3554 l_ae_line_tbl.extend;
3555 next_record_avail := nvl(l_ae_line_tbl.LAST,0) ;
3556 l_ae_line_tbl(next_record_avail).ae_line_type :=
3557 i_ae_line_rec.ae_line_type;
3558
3559 l_stmt_num := 20;
3560
3561 select meaning
3562 into l_ae_line_tbl(next_record_avail).description
3563 from mfg_lookups
3564 where lookup_type = 'CST_ACCOUNTING_LINE_TYPE'
3565 and lookup_code = l_ae_line_tbl(next_record_avail).ae_line_type;
3566
3567 l_ae_line_tbl(next_record_avail).account := i_ae_line_rec.account;
3568
3569 l_stmt_num := 30;
3570
3571 IF l_debug_flag = 'Y' THEN
3572 fnd_file.put_line(fnd_file.log,'Alt Currency : '||i_ae_curr_rec.alt_currency);
3573 fnd_file.put_line(fnd_file.log,'Pri Currency : '||i_ae_curr_rec.pri_currency);
3574 fnd_file.put_line(fnd_file.log,'Currency Rate: '||to_char(i_ae_curr_rec.currency_conv_rate));
3575 END IF;
3576
3577 select
3578 nvl(i_ae_curr_rec.alt_currency,i_ae_curr_rec.pri_currency)
3579 into l_ae_line_tbl(next_record_avail).currency_code
3580 from dual;
3581
3582 l_stmt_num := 40;
3583 select
3584 decode(i_ae_curr_rec.alt_currency,
3585 i_ae_curr_rec.pri_currency,NULL,
3586 i_ae_curr_rec.currency_conv_date)
3587 into l_ae_line_tbl(next_record_avail).currency_conv_date
3588 from dual;
3589
3590 l_stmt_num := 50;
3591 select
3592 decode(i_ae_curr_rec.alt_currency,
3593 i_ae_curr_rec.pri_currency,1,
3594 decode(i_ae_curr_rec.currency_conv_rate,-1,1,i_ae_curr_rec.currency_conv_rate))
3595 into l_ae_line_tbl(next_record_avail).currency_conv_rate
3596 from dual;
3597
3598 l_stmt_num := 60;
3599 select
3600 decode(i_ae_curr_rec.alt_currency,
3601 i_ae_curr_rec.pri_currency,NULL,
3602 i_ae_curr_rec.currency_conv_type)
3603 into l_ae_line_tbl(next_record_avail).currency_conv_type
3604 from dual;
3605
3606
3607 l_stmt_num := 70;
3608
3609 -- For Adjust Events, the accounted and entered values are computed by the calling routine,
3610 -- Create_Adj_Ae_Lines
3611
3612 IF (i_ae_txn_rec.txn_type_flag = 'ADJ') THEN
3613 l_entered_value := i_ae_line_rec.entered_value;
3614 l_accounted_value := i_ae_line_rec.accounted_value;
3615
3616 ELSE
3617 select decode(i_ae_curr_rec.alt_currency,NULL, NULL,
3618 i_ae_curr_rec.pri_currency, NULL,
3619 decode(c2.minimum_accountable_unit,
3620 NULL,
3621 round(i_ae_line_rec.transaction_value, c2.precision),
3622 round(i_ae_line_rec.transaction_value /c2.minimum_accountable_unit)
3623 * c2.minimum_accountable_unit )),
3624 decode(c1.minimum_accountable_unit,
3625 NULL, round(i_ae_line_rec.transaction_value * i_ae_curr_rec.currency_conv_rate, c1.precision),
3626 round(i_ae_line_rec.transaction_value * i_ae_curr_rec.currency_conv_rate/c1.minimum_accountable_unit)
3627 * c1.minimum_accountable_unit )
3628 into
3629 l_entered_value,
3630 l_accounted_value
3631 from
3632 fnd_currencies c1,
3633 fnd_currencies c2
3634 where
3635 c1.currency_code = i_ae_curr_rec.pri_currency
3636 and c2.currency_code = decode(i_ae_curr_rec.alt_currency, NULL,
3637 i_ae_curr_rec.pri_currency,
3638 i_ae_curr_rec.alt_currency);
3639
3640 END IF;
3641
3642 IF l_debug_flag = 'Y' THEN
3643 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Accounted_Value: '||to_char(l_accounted_value));
3644 END IF;
3645
3646 -- ERV does not have an entered amount.
3647 if (l_ae_line_tbl(next_record_avail).ae_line_type = 18) then
3648 l_entered_value := 0;
3649 end if;
3650 if (i_dr_flag) then
3651 l_ae_line_tbl(next_record_avail).entered_dr := nvl(abs(l_entered_value),abs(l_accounted_value));
3652 l_ae_line_tbl(next_record_avail).entered_cr := NULL;
3653 l_ae_line_tbl(next_record_avail).accounted_dr := abs(l_accounted_value);
3654 l_ae_line_tbl(next_record_avail).accounted_cr := NULL;
3655 else
3656 l_ae_line_tbl(next_record_avail).entered_cr := nvl(abs(l_entered_value),abs(l_accounted_value));
3657 l_ae_line_tbl(next_record_avail).entered_dr := NULL;
3658 l_ae_line_tbl(next_record_avail).accounted_cr := abs(l_accounted_value);
3659 l_ae_line_tbl(next_record_avail).accounted_dr := NULL;
3660 end if;
3661 l_ae_line_tbl(next_record_avail).source_table := i_ae_txn_rec.source_table;
3662 l_ae_line_tbl(next_record_avail).source_id := i_ae_txn_rec.transaction_id;
3663
3664 /* Bug 2686598. Rate_or_amount now calculated in calling function */
3665 l_ae_line_tbl(next_record_avail).rate_or_amount := i_ae_line_rec.rate_or_amount;
3666
3667 l_ae_line_tbl(next_record_avail).basis_type := to_number(null);
3668 l_ae_line_tbl(next_record_avail).resource_id := null;
3669 l_ae_line_tbl(next_record_avail).cost_element_id := null;
3670 l_ae_line_tbl(next_record_avail).activity_id := NULL;
3671 l_ae_line_tbl(next_record_avail).repetitive_schedule_id := NULL;
3672 l_ae_line_tbl(next_record_avail).overhead_basis_factor := NULL;
3673 l_ae_line_tbl(next_record_avail).basis_resource_id := NULL;
3674 l_ae_line_tbl(next_record_avail).actual_flag := i_ae_line_rec.actual_flag;
3675 l_ae_line_tbl(next_record_avail).encum_type_id := i_ae_line_rec.encum_type_id;
3676 l_ae_line_tbl(next_record_avail).po_distribution_id := i_ae_line_rec.po_distribution_id;
3677
3678 l_ae_line_tbl(next_record_avail).reference1 := NULL;
3679 l_ae_line_tbl(next_record_avail).reference2 := i_ae_txn_rec.organization_id;
3680 l_ae_line_tbl(next_record_avail).reference3 := i_ae_txn_rec.transaction_id;
3681 l_ae_line_tbl(next_record_avail).reference4 := NULL;
3682 l_ae_line_tbl(next_record_avail).reference5 := NULL;
3683 l_ae_line_tbl(next_record_avail).reference6 := NULL;
3684 l_ae_line_tbl(next_record_avail).reference7 := NULL;
3685 l_ae_line_tbl(next_record_avail).reference8 := NULL;
3686 l_ae_line_tbl(next_record_avail).reference9 := NULL;
3687 l_ae_line_tbl(next_record_avail).reference10 := NULL;
3688
3689 IF l_debug_flag = 'Y' THEN
3690 FND_FILE.PUT_LINE(FND_FILE.LOG, 'No. of Records: '||to_char(l_ae_line_tbl.COUNT));
3691 fnd_file.put_line(fnd_file.log,'Insert_Account >>> ');
3692 END IF;
3693
3694 EXCEPTION
3695
3696 when invalid_acct_error then
3697 o_ae_err_rec.l_err_num := 9999;
3698 o_ae_err_rec.l_err_code := 'CST_NO_TXN_INVALID_ACCOUNT';
3699 FND_MESSAGE.set_name('BOM', 'CST_NO_TXN_INVALID_ACCOUNT');
3700 o_ae_err_rec.l_err_msg := FND_MESSAGE.Get;
3701
3702 when others then
3703 o_ae_err_rec.l_err_num := SQLCODE;
3704 o_ae_err_rec.l_err_code := '';
3705 o_ae_err_rec.l_err_msg := 'CSTPAPPR.insert_account' || to_char(l_stmt_num) ||
3706 substr(SQLERRM,1,180);
3707
3708 end insert_account;
3709
3710 FUNCTION get_net_del_qty(
3711 i_po_distribution_id IN NUMBER,
3712 i_transaction_id IN NUMBER)
3713 RETURN NUMBER
3714 IS
3715 ----------------------------------------------------
3716 -- Get all child transactions level by level
3717 -----------------------------------------------------
3718 CURSOR c_nqd is
3719 SELECT
3720 rt4.transaction_id,
3721 rt4.transaction_type,
3722 -- J Changes -------------------------------------------------------------
3723 -- Bug 3588765 --
3724 -- DECODE(POL.ORDER_TYPE_LOOKUP_CODE,
3725 -- 'RATE', rt4.AMOUNT,
3726 -- 'FIXED PRICE', rt4.AMOUNT,
3727 -- RT4.PRIMARY_QUANTITY) "PRIMARY_QUANTITY",
3728 -- End of Bug 3588765
3729 ----------------------------------------------------------------------------
3730 rt4.parent_transaction_id
3731 FROM
3732 rcv_transactions rt4
3733 -- J Changes -------------------------------------------------------------
3734 -- Bug 3588765 --
3735 -- PO_LINES_ALL POL
3736 -- End of Bug 3588765
3737 --------------------------------------------------------------------------
3738 WHERE
3739 rt4.transaction_id < i_transaction_id
3740 -- J Changes -------------------------------------------------------------
3741 -- Bug 3588765 --
3742 AND EXISTS (SELECT 1 FROM PO_LINES_ALL POL WHERE RT4.PO_LINE_ID= POL.PO_LINE_ID)
3743 -- AND RT4.PO_LINE_ID = POL.PO_LINE_ID
3744 -- End of Bug 3588765
3745 --------------------------------------------------------------------------
3746 START WITH
3747 rt4.po_distribution_id = i_po_distribution_id
3748 and transaction_type = 'DELIVER'
3749 CONNECT BY
3750 prior rt4.transaction_id = rt4.parent_transaction_id
3751 AND rt4.po_line_location_id = PRIOR rt4.po_line_location_id; -- Change for the bug 4968702
3752
3753 l_nqd NUMBER := 0;
3754 l_parent_type rcv_transactions.transaction_type%TYPE;
3755 l_stmt_num NUMBER := 0;
3756 -- Bug 3588765 --
3757 l_primary_quantity NUMBER := 0;
3758 -- End of Bug 3588765
3759 BEGIN
3760 ---------------------------------------------------------
3761 -- Initialize error variable
3762 ---------------------------------------------------------
3763 ---------------------------------------------------------
3764 -- For each child transaction loop
3765 --------------------------------------------------------
3766 FOR c_nqd_rec in c_nqd loop
3767
3768 -- Bug 3588765 -----------------------------
3769 l_stmt_num := 20;
3770 SELECT DECODE(POLL1.MATCHING_BASIS, -- Changed for Complex work procurement
3771 'AMOUNT', rt6.AMOUNT,
3772 'QUANTITY', RT6.PRIMARY_QUANTITY) "PRIMARY_QUANTITY"
3773 INTO l_primary_quantity
3774 FROM rcv_transactions rt6,
3775 PO_LINE_LOCATIONS_ALL POLL1 -- Changed for Complex work procurement
3776 WHERE rt6.transaction_id=c_nqd_rec.transaction_id
3777 AND RT6.PO_LINE_LOCATION_ID= POLL1.LINE_LOCATION_ID; -- Changed for Complex work procurement
3778 -- End of Bug 3588765 ----------------------
3779
3780 --------------------------------------------------------
3781 -- If it is not the parent (that was passed in) transaction itself
3782 --------------------------------------------------------
3783 IF c_nqd_rec.transaction_type <> 'DELIVER' THEN
3784 l_stmt_num := 10;
3785 ----------------------------------------------------------
3786 -- Get the parent transaction type
3787 ----------------------------------------------------------
3788 SELECT
3789 rt5.transaction_type
3790 INTO
3791 l_parent_type
3792 FROM
3793 rcv_transactions rt5
3794 WHERE
3795 rt5.transaction_id = c_nqd_rec.parent_transaction_id;
3796 END IF;
3797 ------------------------------------------------------------
3798 -- If it is the parent receive or match transaction
3799 -- then add the quantity to l_nqd
3800 ------------------------------------------------------------
3801 IF c_nqd_rec.transaction_type = 'DELIVER' THEN
3802 l_nqd := l_nqd + l_primary_quantity;
3803 -----------------------------------------------------------
3804 -- If the transaction is CORRECT,
3805 -- If parent is receive or match txn, then add the corrected qty
3806 -- If parent is return, then subtract the corrected qty
3807 -----------------------------------------------------------
3808 ELSIF c_nqd_rec.transaction_type = 'CORRECT' then
3809 IF l_parent_type = 'DELIVER' then
3810 l_nqd := l_nqd + l_primary_quantity;
3811 ELSIF l_parent_type = 'RETURN TO RECEIVING' then
3812 l_nqd := l_nqd - l_primary_quantity;
3813 END IF;
3814 ----------------------------------------------------------
3815 -- If transaction is return transaction, then subtract returned qty
3816 ----------------------------------------------------------
3817 ELSIF c_nqd_rec.transaction_type = 'RETURN TO RECEIVING' then
3818 l_nqd := l_nqd - l_primary_quantity;
3819 END IF;
3820
3821 END LOOP; -- child txns loop
3822 --------------------------------------------------------
3823 -- Return the net quantity received as calculated
3824 --------------------------------------------------------
3825 RETURN (l_nqd);
3826 EXCEPTION
3827 WHEN OTHERS THEN
3828 RETURN(NULL);
3829 END get_net_del_qty;
3830
3831
3832
3833
3834 -- ===================================================================
3835 -- Balance Account.
3836 -- ===================================================================
3837 procedure balance_account (
3838 l_ae_line_tbl IN OUT NOCOPY CSTPALTY.cst_ae_line_tbl_type,
3839 o_ae_err_rec OUT NOCOPY CSTPALTY.cst_ae_err_rec_type)
3840 IS
3841 l_ent_value NUMBER := 0;
3842 l_acc_value NUMBER := 0;
3843 l_last_rec NUMBER;
3844 l_stmt_num NUMBER;
3845 l_ipv_line_flag NUMBER := 0; /*to find out if there is ipv line */
3846 BEGIN
3847 IF l_debug_flag = 'Y' THEN
3848 fnd_file.put_line(fnd_file.log,'Balance_Account <<<');
3849 END IF;
3850 if (l_ae_line_tbl.exists(1)) then
3851 l_stmt_num := 10;
3852 For i in l_ae_line_tbl.FIRST .. l_ae_line_tbl.LAST loop
3853 IF (l_ae_line_tbl(i).actual_flag = 'E') THEN
3854 null;
3855 else
3856 if (l_ae_line_tbl(i).ae_line_type = 17) then
3857 l_ipv_line_flag := 1; /* indicates that ipv line does exist */
3858 end if;
3859 l_ent_value := l_ent_value + nvl(l_ae_line_tbl(i).entered_dr,0) - nvl(l_ae_line_tbl(i).entered_cr,0);
3860 l_acc_value := l_acc_value + nvl(l_ae_line_tbl(i).accounted_dr,0) - nvl(l_ae_line_tbl(i).accounted_cr,0);
3861 END IF;
3862 end loop;
3863
3864 if (l_ent_value = 0 and l_acc_value = 0) then
3865 return;
3866 end if;
3867
3868 l_stmt_num := 20;
3869 l_last_rec := l_ae_line_tbl.LAST;
3870
3871
3872 -- Any rounding errors should be balanced out and put to the IPV account
3873 -- Bug 930582 workaround : any discrepancies with regard to accrual not including tax
3874 -- should be put into ipv.
3875
3876 while true
3877 loop
3878 if (l_ae_line_tbl(l_last_rec).actual_flag = 'E') OR
3879 (l_ipv_line_flag = 1 and l_ae_line_tbl(l_last_rec).ae_line_type <> 17) then
3880 l_last_rec := l_last_rec - 1;
3881 else
3882 exit;
3883 end if;
3884 end loop;
3885
3886
3887 if l_ae_line_tbl(l_last_rec).accounted_dr is not NULL then
3888 IF l_debug_flag = 'Y' THEN
3889 fnd_file.put_line(fnd_file.log,'Balancing Dr: '||to_char(l_ae_line_tbl(l_last_rec).entered_dr));
3890 END IF;
3891 l_ae_line_tbl(l_last_rec).accounted_dr :=
3892 l_ae_line_tbl(l_last_rec).accounted_dr - l_acc_value;
3893 l_ae_line_tbl(l_last_rec).entered_dr :=
3894 l_ae_line_tbl(l_last_rec).entered_dr - l_ent_value;
3895 elsif l_ae_line_tbl(l_last_rec).accounted_cr is not NULL then
3896 IF l_debug_flag = 'Y' THEN
3897 fnd_file.put_line(fnd_file.log,'Balancing Cr: '||to_char(l_ae_line_tbl(l_last_rec).entered_cr));
3898 END IF;
3899 l_ae_line_tbl(l_last_rec).accounted_cr :=
3900 l_ae_line_tbl(l_last_rec).accounted_cr + l_acc_value;
3901 l_ae_line_tbl(l_last_rec).entered_cr :=
3902 l_ae_line_tbl(l_last_rec).entered_cr + l_ent_value;
3903 end if;
3904
3905 end if;
3906 IF l_debug_flag = 'Y' THEN
3907 fnd_file.put_line(fnd_file.log,'Balance_Account >>>');
3908 END IF;
3909
3910 EXCEPTION
3911
3912 when others then
3913 o_ae_err_rec.l_err_num := SQLCODE;
3914 o_ae_err_rec.l_err_code := '';
3915 o_ae_err_rec.l_err_msg := 'CSTPAPPR.balance_account' || to_char(l_stmt_num) ||
3916 substr(SQLERRM,1,180);
3917
3918 END balance_account;
3919
3920 end CSTPAPPR;