1 PACKAGE BODY RCV_AccrualUtilities_GRP AS
2 /* $Header: RCVGUTLB.pls 120.10.12010000.2 2008/11/10 13:43:14 anjha ship $ */
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'RCV_AccrualUtilities_GRP';
5 --G_DEBUG CONSTANT VARCHAR2(1) := NVL(FND_PROFILE.VALUE('AFLOG_ENABLED'),'N');
6 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
7 G_LOG_HEAD CONSTANT VARCHAR2(40) := 'po.plsql.'||G_PKG_NAME;
8 G_MSG_LEVEL_THRESHOLD CONSTANT VARCHAR2(1):= FND_PROFILE.Value('FND_API_MSG_LEVEL_THRESHOLD');
9
10 -----------------------------------------------------------------------------
11 -- Start of comments --
12 -- --
13 -- FUNCTION --
14 -- get_ret_sts_success returns constant G_RET_STS_SUCCESS from--
15 -- fnd_api package --
16 -----------------------------------------------------------------------------
17 FUNCTION get_ret_sts_success return varchar2
18 IS
19 BEGIN
20 return fnd_api.g_ret_sts_success;
21 END get_ret_sts_success;
22
23 -----------------------------------------------------------------------------
24 -- Start of comments --
25 -- --
26 -- FUNCTION --
27 -- get_ret_sts_error returns constant G_RET_STS_ERROR from --
28 -- fnd_api package --
29 -----------------------------------------------------------------------------
30 FUNCTION get_ret_sts_error return varchar2
31 IS
32 BEGIN
33 return fnd_api.g_ret_sts_error;
34 END get_ret_sts_error;
35
36 -----------------------------------------------------------------------------
37 -- Start of comments --
38 -- --
39 -- FUNCTION --
40 -- get_ret_sts_unexp_error returns constant G_RET_STS_UNEXP_ERROR --
41 -- from fnd_api package --
42 -----------------------------------------------------------------------------
43 FUNCTION get_ret_sts_unexp_error return varchar2
44 IS
45 BEGIN
46 return fnd_api.g_ret_sts_unexp_error;
47 END get_ret_sts_unexp_error;
48
49 -----------------------------------------------------------------------------
50 -- Start of comments --
51 -- --
52 -- FUNCTION --
53 -- get_true returns constant G_TRUE from fnd_api package --
54 -----------------------------------------------------------------------------
55 FUNCTION get_true return varchar2
56 IS
57 BEGIN
58 return fnd_api.g_true;
59 END get_true;
60
61 -----------------------------------------------------------------------------
62 -- Start of comments --
63 -- --
64 -- FUNCTION --
65 -- get_false returns constant G_FALSE from fnd_api package--
66 -----------------------------------------------------------------------------
67 FUNCTION get_false return varchar2
68 IS
69 BEGIN
70 return fnd_api.g_false;
71 END get_false;
72
73 -----------------------------------------------------------------------------
74 -- Start of comments --
75 -- --
76 -- FUNCTION --
77 -- get_valid_level_none returns constant G_VALID_LEVEL_NONE from --
78 -- fnd_api package --
79 -----------------------------------------------------------------------------
80 FUNCTION get_valid_level_none return NUMBER
81 IS
82 BEGIN
83 return fnd_api.g_valid_level_none;
84 END get_valid_level_none;
85
86 -----------------------------------------------------------------------------
87 -- Start of comments --
88 -- --
89 -- FUNCTION --
90 -- get_valid_level_full returns constant G_VALID_LEVEL_FULL from --
91 -- fnd_api package --
92 -----------------------------------------------------------------------------
93 FUNCTION get_valid_level_full return NUMBER
94 IS
95 BEGIN
96 return fnd_api.g_valid_level_full;
97 END get_valid_level_full;
98
99 -----------------------------------------------------------------------------
100 -- Start of comments
101 -- API name : Get_ReceivingUnitPrice
102 -- Type : Group
103 -- Function : To get the average unit price of quantity in Receiving
104 -- Inspection given a parent receive/match transaction. If a date
105 -- is specified, the average unit price is for the quantity in Receiving
106 -- as of that date. Otherwise, it is for the current date.
107 -- Pre-reqs :
108 -- Parameters :
109 -- IN : p_api_version IN NUMBER Required
110 -- p_init_msg_list IN VARCHAR2 Optional
111 -- Default = FND_API.G_FALSE
112 -- p_commit IN VARCHAR2 Optional
113 -- Default = FND_API.G_FALSE
114 -- p_validation_level IN NUMBER Optional
115 -- Default = FND_API.G_VALID_LEVEL_FULL
116 -- p_rcv_transaction_id IN NUMBER
117 -- p_valuation_date IN DATE Optional
118 -- Default = NULL
119 --
120 -- OUT : x_unit_price OUT NUMBER
121 -- x_return_status OUT VARCHAR2(1)
122 -- x_msg_count OUT NUMBER
123 -- x_msg_data OUT VARCHAR2(2000)
124 -- Version :
125 -- Initial version 1.0
126 --
127 -- Notes : This procedure is used by the Receving Value Report and the All inventory
128 -- value report to display the value in receiving inspection.
129 -- Earlier, this value was simply calculated as (mtl_supply.primary_quantity
130 -- However, with the introduction of global procurement and drop shipments
131 -- the accounting could be done at transfer price instead of PO price.
132 -- Furthermore, the transfer price itself can change between transactions.
133 -- Mtl_supply contains a summary amount : quantity_recieved + quantity corrected
134 -- - quantity returned. Hence the unit price that should be used by the view
135 -- should be the average of the unit price across these transactions.
136 --
137 -- End of comments
138 -------------------------------------------------------------------------------
139 PROCEDURE Get_ReceivingUnitPrice(
140 p_api_version IN NUMBER,
141 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
142 p_commit IN VARCHAR2 := FND_API.G_FALSE,
143 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
144 x_return_status OUT NOCOPY VARCHAR2,
145 x_msg_count OUT NOCOPY NUMBER,
146 x_msg_data OUT NOCOPY VARCHAR2,
147
148 p_rcv_transaction_id IN NUMBER,
149 p_valuation_date IN DATE := NULL,
150 x_unit_price OUT NOCOPY NUMBER
151 )
152 IS
153 l_api_name CONSTANT VARCHAR2(30) := 'Get_ReceivingUnitPrice';
154 l_api_version CONSTANT NUMBER := 1.0;
155
156 l_return_status VARCHAR2(1);
157 l_msg_count NUMBER := 0;
158 l_msg_data VARCHAR2(8000);
159 l_stmt_num NUMBER := 0;
160 l_api_message VARCHAR2(1000);
161
162 l_rcv_transaction_id NUMBER;
163
164 l_dist_flag NUMBER;
165 l_txn_price NUMBER;
166 l_tax NUMBER;
167
168 l_total_price NUMBER;
169 l_total_quantity NUMBER;
170 l_rae_count NUMBER;
171 l_parent_txn_type RCV_TRANSACTIONS.TRANSACTION_TYPE%TYPE;
172 l_source_doc_code RCV_TRANSACTIONS.SOURCE_DOCUMENT_CODE%TYPE;
173
174 l_rcv_organization_id RCV_TRANSACTIONS.organization_id%TYPE;
175 l_po_header_id RCV_TRANSACTIONS.po_header_id%TYPE;
176 l_po_org_id PO_HEADERS_ALL.org_id%TYPE;
177 l_po_sob_id CST_ORGANIZATION_DEFINITIONS.set_of_books_id%TYPE;
178 l_rcv_org_id CST_ORGANIZATION_DEFINITIONS.operating_unit%TYPE;
179 l_rcv_sob_id CST_ORGANIZATION_DEFINITIONS.set_of_books_id%TYPE;
180 l_destination_type_code PO_DISTRIBUTIONS_ALL.destination_type_code%TYPE;
181 l_lcm_flag PO_LINE_LOCATIONS_ALL.lcm_flag%TYPE;
182
183 l_full_name CONSTANT VARCHAR2(60) := g_pkg_name || '.' || l_api_name;
184 l_module constant varchar2(60) := 'po.plsql.'||l_full_name;
185
186 l_uLog CONSTANT BOOLEAN := fnd_log.level_unexpected >= g_log_level AND
187 fnd_log.TEST(fnd_log.level_unexpected, l_module);
188 l_errorLog constant boolean := l_uLog and (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
189 l_pLog constant boolean := l_errorLog and (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
190 l_sLog constant boolean := l_pLog and (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
191 l_rae_price NUMBER;
192
193
194 CURSOR c_txn_history_csr (c_transaction_id NUMBER, c_valuation_date DATE) IS
195 SELECT RT.transaction_id transaction_id,
196 RT.transaction_type transaction_type,
197 RT.source_doc_quantity source_doc_quantity,
198 RT.organization_id organization_id,
199 RT.primary_quantity primary_quantity,
200 nvl(RT.unit_landed_cost,0) unit_landed_cost
201 FROM rcv_transactions RT
202 WHERE ((c_valuation_date is not null and transaction_date <= c_valuation_date)
203 OR c_valuation_date is null)
204 START WITH transaction_id = c_transaction_id
205 CONNECT BY parent_transaction_id = PRIOR transaction_id;
206
207
208 BEGIN
209 l_return_status := fnd_api.g_ret_sts_success;
210 -- Standard start of API savepoint
211 SAVEPOINT Get_ReceivingUnitPrice_GRP;
212
213 l_stmt_num := 0;
214
215 IF l_pLog THEN
216 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
217 ,'Get_ReceivingUnitPrice <<');
218 END IF;
219
220 -- Standard call to check for call compatibility
221 IF NOT FND_API.Compatible_API_Call (
222 l_api_version,
223 p_api_version,
224 l_api_name,
225 G_PKG_NAME ) THEN
226 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
227 END IF;
228
229 -- Initialize message list if p_init_msg_list is set to TRUE
230 IF FND_API.to_Boolean(p_init_msg_list) THEN
231 FND_MSG_PUB.initialize;
232 END IF;
233
234 -- Initialize API return status to success
235 x_return_status := FND_API.G_RET_STS_SUCCESS;
236
237 IF l_sLog THEN
238 l_api_message := 'p_rcv_transaction_id : '||p_rcv_transaction_id;
239 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.begin'
240 ,l_api_message);
241 END IF;
242
243 l_stmt_num := 10;
244
245 SELECT rt.source_document_code, rt.po_header_id, rt.organization_id,
246 nvl(poll.lcm_flag,'N')
247 INTO l_source_doc_code, l_po_header_id, l_rcv_organization_id,
248 l_lcm_flag
249 FROM rcv_transactions rt,
250 po_line_locations_all poll
251 WHERE rt.transaction_id = p_rcv_transaction_id
252 AND rt.po_line_location_id = poll.line_location_id;
253
254 IF l_source_doc_code <> 'PO' THEN
255 FND_MESSAGE.set_name('PO','INVALID_SOURCE_DOCUMENT');
256 FND_MSG_pub.add;
257 IF l_errorLog THEN
258 FND_LOG.message(FND_LOG.LEVEL_ERROR,G_LOG_HEAD || '.'||l_api_name||l_stmt_num,FALSE);
259 END IF;
260 RAISE FND_API.g_exc_error;
261 END IF;
262
263
264 /* Get the parameters to determine whether this is a global procurement scenario. */
265
266 /* Get PO Operating Unit and Set of Books */
267 l_stmt_num := 20;
268 SELECT POH.org_id, HOU.set_of_books_id
269 INTO l_po_org_id, l_po_sob_id
270 FROM po_headers_all POH, hr_operating_units HOU
271 WHERE POH.org_id = HOU.organization_id
272 AND POH.po_header_id = l_po_header_id;
273
274 IF l_sLog THEN
275 l_api_message := 'l_po_org_id : '|| l_po_org_id || '; l_po_sob_id : ' || l_po_sob_id;
276 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
277 ,l_api_message);
278 END IF;
279
280 /* Get Receiving Operating Unit and Set of Books */
281 l_stmt_num := 30;
282 SELECT operating_unit, set_of_books_id
283 INTO l_rcv_org_id, l_rcv_sob_id
284 FROM cst_organization_definitions cod
285 WHERE organization_id = l_rcv_organization_id;
286
287 IF l_sLog THEN
288 l_api_message := 'l_rcv_org_id : '|| l_rcv_org_id || '; l_rcv_sob_id : ' || l_rcv_sob_id;
289 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
290 ,l_api_message);
291 END IF;
292
293
294 /* 12i: Since we are modifying the calling procedures to pass in parent receive/match txn,
295 we can just set l_rcv_transaction_id := p_rcv_transaction_id.
296
297 -- Bug #3094999. For Accept/Reject/Transfer transactions, mtl_supply gets updated with the
298 -- transaction_id of the Accept/Reject/Transfer transaction in place of the parent receipt.
299 -- However, accounting is only done against the parent receive transaction. Therefore, we
300 -- have to identify the parent receipt to find the actual unit price.
301 SELECT transaction_id
302 INTO l_rcv_transaction_id
303 FROM (
304 SELECT RT.transaction_id transaction_id,
305 RT.parent_transaction_id parent_transaction_id,
306 RT.transaction_type
307 FROM rcv_transactions RT
308 START WITH transaction_id = p_rcv_transaction_id
309 CONNECT BY transaction_id = PRIOR parent_transaction_id)
310 WHERE ((transaction_type = 'RECEIVE' and parent_transaction_id=-1)
311 OR transaction_type = 'MATCH');
312 */
313 l_rcv_transaction_id := p_rcv_transaction_id;
314
315 IF l_sLog THEN
316 l_api_message := 'l_rcv_transaction_id : '||l_rcv_transaction_id;
317 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.begin'
318 ,l_api_message);
319 END IF;
320
321 l_total_price := 0;
322 l_total_quantity := 0;
323 IF (l_lcm_flag = 'N') THEN
324 FOR rec_txn in c_txn_history_csr(l_rcv_transaction_id, p_valuation_date) LOOP
325
326 -- The situation where the unit_price on an event will vary from the PO
327 -- price occurs only in Global Procurement scenarios. In all other cases
328 -- including adjust events we use the latest PO price.
329 -- So for all global procurement related transactions, we compute the
330 -- average unit price from RAE and for non global procurement events,
331 -- we use the latest PO price.
332 l_stmt_num := 40;
333 IF l_sLog THEN
334 l_api_message := 'Processing Transaction_ID : '||rec_txn.transaction_id||
335 ' Transaction_Type : '||rec_txn.transaction_type||
336 ' Source_Doc_Qty : '||rec_txn.source_doc_quantity||
337 ' Organization_ID : '||rec_txn.organization_id;
338 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
339 ,l_api_message);
340 END IF;
341
342
343 IF rec_txn.transaction_type NOT IN ('ACCEPT','REJECT','TRANSFER') THEN
344
345 IF(rec_txn.transaction_type = 'CORRECT') THEN
346 l_stmt_num := 50;
347
348 SELECT PARENT.transaction_type
349 INTO l_parent_txn_type
350 FROM rcv_transactions RT, rcv_transactions PARENT
351 WHERE RT.transaction_id = rec_txn.transaction_id
352 AND PARENT.transaction_id = RT.parent_transaction_id;
353
354 IF l_sLog THEN
355 l_api_message := 'l_parent_txn_type : '||l_parent_txn_type;
356 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
357 ,l_api_message);
358 END IF;
359
360 END IF;
361
362 l_stmt_num := 60;
363
364 SELECT count(*)
365 INTO l_rae_count
366 FROM rcv_accounting_events RAE
367 WHERE RAE.rcv_transaction_id = rec_txn.transaction_id
368 AND RAE.organization_id = rec_txn.organization_id
369 AND RAE.event_type_id IN (1,2,3,4,5,6)
370 AND RAE.TRX_FLOW_HEADER_ID IS NOT NULL;
371 /* This condition RAE.trx_flow_header_id IS NOT NULL limits this to
372 events in RAE for global procurement scenarios only */
373
374
375 IF (l_rae_count = 0) then
376 -- Transaction was done prior to Patchset J or
377 -- It is a non-global procurement scenario
378 -- Default to POLL.price_override in this case.
379 -- Or, it could be a Deliver, RTR, or Correct to Deliver or RTR
380 -- for Inventory or WIP destination types in a global procurement scenario.
381
382 IF (l_po_org_id <> l_rcv_org_id) THEN
383 /*
384 The only valid scenario that brings us to this code branch satisfies
385 the following conditions:
386 1. This is a global procurement scenario
387 2. If (1) is true, since the transaction had no events in RAE (l_rae_count = 0),
388 the transaction must be a Deliver, RTR, or Correct to Deliver or RTR
389 for Inventory or WIP destinations only.
390 (For other transaction types in a global procurement scenario and
391 for Expense destinations, l_rae_count > 0)
392 */
393
394 l_stmt_num := 70;
395
396 SELECT POD.destination_type_code
397 INTO l_destination_type_code
398 FROM po_distributions_all POD, rcv_transactions RT
399 WHERE POD.po_distribution_id = RT.po_distribution_id
400 AND RT.transaction_id = rec_txn.transaction_id;
401
402 IF l_sLog THEN
403 l_api_message := 'l_destination_type_code : '|| l_destination_type_code;
404 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
405 ,l_api_message);
406 END IF;
407
408 /*
409 * The formula to get the actual transaction value from MMT or WT would be:
410 * (MMT.transaction_cost * primary_quantity) or
411 * (WT.actual_resource_rate * primary_quantity)
412 *
413 * However, to yield the correct unit price in global procurement
414 * scenarios with UOM conversion (where primary_quantity <>
415 * source_doc_quantity), l_txn_price must be equal to
416 * unit price * RT.source_doc_quantity, since in the end the API
417 * divides the total transaction value by RT.source_doc_quantity
418 * to get the unit price.
419 * Failing to convert to RT.source_doc_quantity will cause the API
420 * to calculate the incorrect unit price in global procurement scenarios
421 * with UOM conversion.
422 */
423
424 IF (l_destination_type_code = 'INVENTORY') THEN
425
426 /* For Inventory destinations, use MMT.transaction_cost. */
427
428 l_stmt_num := 80;
429 SELECT (MMT.transaction_cost * rec_txn.source_doc_quantity)
430 INTO l_txn_price
431 FROM mtl_material_transactions MMT
432 WHERE MMT.rcv_transaction_id = rec_txn.transaction_id
433 AND MMT.organization_id = rec_txn.organization_id;
434
435
436 ELSIF (l_destination_type_code = 'SHOP FLOOR') THEN
437
438 /* For Shop Floor destinations, use WT.actual_resource_rate.
439 * Select from WT if the transaction is costed, and WCTI otherwise.
440 */
441
442 BEGIN
443 l_stmt_num := 90;
444 SELECT (WT.actual_resource_rate * rec_txn.source_doc_quantity)
445 INTO l_txn_price
446 FROM wip_transactions WT
447 WHERE WT.rcv_transaction_id = rec_txn.transaction_id
448 AND WT.organization_id = rec_txn.organization_id;
449 EXCEPTION
450 WHEN no_data_found THEN
451 SELECT (WCTI.actual_resource_rate * rec_txn.source_doc_quantity)
452 INTO l_txn_price
453 FROM wip_cost_txn_interface WCTI
454 WHERE WCTI.rcv_transaction_id = rec_txn.transaction_id
455 AND WCTI.organization_id = rec_txn.organization_id;
456 END;
457
458 END IF; /* IF (l_destination_type_code = ) */
459
460 IF l_sLog THEN
461 l_api_message := 'l_txn_price : '||l_txn_price;
462 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
463 ,l_api_message);
464 END IF;
465
466 ELSE
467 /* This is a non-global procurement scenario or a pre-Patchset J transaction */
468 l_stmt_num := 100;
469 SELECT DECODE (PO_DISTRIBUTION_ID, NULL, 0, 1)
470 INTO l_dist_flag
471 FROM RCV_TRANSACTIONS
472 WHERE TRANSACTION_ID = rec_txn.transaction_id;
473
474 IF l_sLog THEN
475 l_api_message := 'l_dist_flag : '||l_dist_flag;
476 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
477 ,l_api_message);
478 END IF;
479
480
481 l_stmt_num := 110;
482 IF l_dist_flag = 1 THEN
483 /* Bug 6266340: Modified the Select clause to check for 0 primary quantity */
484 SELECT decode(RT.primary_quantity, 0, 0,
485 ((PLL.price_override * RT.source_doc_quantity +
486 PO_TAX_SV.GET_TAX('PO', POD.PO_DISTRIBUTION_ID) *
487 RT.SOURCE_DOC_QUANTITY/POD.QUANTITY_ORDERED) *
488 decode (nvl(PLL.match_option,'P'),
489 'R',NVL(RT.currency_conversion_rate,1),
490 'P',NVL(NVL(POD.rate,POH.rate),1)) *
491 (RT.source_doc_quantity/RT.primary_quantity)))
492 INTO l_txn_price
493 FROM rcv_transactions RT,
494 po_distributions_all POD,
495 po_line_locations_all PLL,
496 po_headers_all POH
497 WHERE RT.transaction_id = rec_txn.transaction_id
498 AND POD.po_distribution_id = RT.po_distribution_id
499 AND PLL.line_location_id = RT.po_line_location_id
500 AND POH.po_header_id = RT.po_header_id;
501
502 IF l_sLog THEN
503 l_api_message := 'l_txn_price : '||l_txn_price;
504 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
505 ,l_api_message);
506 END IF;
507
508 ELSE
509 l_stmt_num := 120;
510 SELECT
511 NVL(SUM(PO_TAX_SV.get_tax('PO', POD.PO_DISTRIBUTION_ID)), 0)
512 INTO
513 l_tax
514 from
515 po_distributions_all pod,
516 po_line_locations_all pol,
517 rcv_transactions rt
518 where
519 rt.transaction_id = rec_txn.transaction_id
520 and rt.po_line_location_id = pol.line_location_id
521 and pod.line_location_id = pol.line_location_id;
522
523 IF l_sLog THEN
524 l_api_message := 'l_tax : '||l_tax;
525 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
526 ,l_api_message);
527 END IF;
528
529 l_stmt_num := 130;
530
531 /* Bug 6266340: Modified the Select clause to check for 0 primary quantity */
532 SELECT decode(RT.primary_quantity, 0, 0,
533 ((PLL.price_override * RT.source_doc_quantity +
534 RT.SOURCE_DOC_QUANTITY/PLL.QUANTITY * l_tax) *
535 decode (nvl(PLL.match_option,'P'),
536 'R',NVL(RT.currency_conversion_rate, 1),
537 'P',NVL(POH.rate, 1)) *
538 (RT.source_doc_quantity/RT.primary_quantity)))
539 INTO l_txn_price
540 FROM rcv_transactions RT,
541 po_line_locations_all PLL,
542 po_headers_all POH
543 WHERE RT.transaction_id = rec_txn.transaction_id
544 AND PLL.line_location_id = RT.po_line_location_id
545 AND POH.po_header_id = RT.po_header_id;
546
547 IF l_sLog THEN
548 l_api_message := 'l_txn_price : '||l_txn_price;
549 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
550 ,l_api_message);
551 END IF;
552
553 END IF; -- l_dist_flag = 1
554
555 END IF; -- l_po_org_id <> l_rcv_org_id
556 ELSE
557 -- Transaction was done after Patchset J and it relates to global procurement
558 -- RAE.unit_price can be used.
559 -- A sum is necessary here since in the case of a standard receipt,
560 -- you could have multiple events - one for each distribution. */
561 -- Added organization check so adjust events in global procurement
562 -- scenarios would get excluded.
563
564 l_stmt_num := 140;
565
566 /* Bug 6266340: Modified the Select clause to check for 0 primary quantity */
567 SELECT sum(decode(RAE.primary_quantity, 0, 0,
568 (RAE.unit_price * nvl(RAE.currency_conversion_rate,1)*
569 RAE.source_doc_quantity/RAE.primary_quantity * RAE.source_doc_quantity)))
570 INTO l_txn_price
571 FROM rcv_accounting_events RAE
572 WHERE RAE.rcv_transaction_id = rec_txn.transaction_id
573 AND RAE.organization_id = rec_txn.organization_id
574 AND RAE.event_type_id IN (1,2,3,4,5,6);
575
576 IF l_sLog THEN
577 l_api_message := 'l_txn_price : '||l_txn_price;
578 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
579 ,l_api_message);
580 END IF;
581
582 END IF;
583
584
585 /* for each transaction, increment or decrement the running total price and quantity. */
586 l_stmt_num := 150;
587 -- Receive, Match and RTR transactions increase value in Receiving Inspection.
588 -- Deliver and RTV transactions decrease value in Receiving Inspection.
589 -- Corrections follow the behavior of the parent.
590 IF((rec_txn.transaction_type IN ('RECEIVE','MATCH')) OR
591 (rec_txn.transaction_type = 'CORRECT' AND l_parent_txn_type IN ('RECEIVE','MATCH'))) THEN
592 l_total_price := l_total_price + l_txn_price;
593 l_total_quantity := l_total_quantity + rec_txn.source_doc_quantity;
594 ELSIF ((rec_txn.transaction_type = 'RETURN TO VENDOR') OR
595 (rec_txn.transaction_type = 'CORRECT' AND l_parent_txn_type = 'RETURN TO VENDOR')) THEN
596 l_total_price := l_total_price - l_txn_price;
597 l_total_quantity := l_total_quantity - rec_txn.source_doc_quantity;
598 ELSIF ((rec_txn.transaction_type = 'RETURN TO RECEIVING') OR
599 (rec_txn.transaction_type = 'CORRECT' AND l_parent_txn_type = 'RETURN TO RECEIVING')) THEN
600 l_total_price := l_total_price + l_txn_price;
601 l_total_quantity := l_total_quantity + rec_txn.source_doc_quantity;
602 ELSIF ((rec_txn.transaction_type = 'DELIVER') OR
603 (rec_txn.transaction_type = 'CORRECT' AND l_parent_txn_type = 'DELIVER')) THEN
604 l_total_price := l_total_price - l_txn_price;
605 l_total_quantity := l_total_quantity - rec_txn.source_doc_quantity;
606 END IF;
607
608 IF l_sLog THEN
609 l_api_message := 'l_total_price : '||l_total_price ||
610 ' l_total_quantity : '||l_total_quantity;
611 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
612 ,l_api_message);
613 END IF;
614
615 END IF; /* IF rec_txn.transaction_type... */
616 END LOOP;
617 ELSE /*LCM ENABLED*/
618 l_stmt_num := 150;
619 l_rae_price := 0;
620 SELECT nvl(sum(decode(rae.event_type_id,
621 15,rae.primary_quantity,
622 -1*rae.primary_quantity)*
623 (rae.unit_price-rae.prior_unit_price)),0)
624 INTO l_rae_price
625 FROM rcv_accounting_events rae
626 WHERE rae.event_type_id IN (15,16,17)
627 AND rae.rcv_transaction_id = l_rcv_transaction_id
628 AND((p_valuation_date is not null
629 and rae.transaction_date <= p_valuation_date)
630 OR p_valuation_date is null);
631 IF l_sLog THEN
632 l_api_message := 'l_rae_price : '||l_rae_price ;
633 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
634 ,l_api_message);
635 END IF;
636
637 FOR rec_txn in c_txn_history_csr(l_rcv_transaction_id, p_valuation_date) LOOP
638 IF l_sLog THEN
639 l_api_message := 'Processing Transaction_ID : '||rec_txn.transaction_id||
640 ' Transaction_Type : '||rec_txn.transaction_type||
641 ' Source_Doc_Qty : '||rec_txn.source_doc_quantity||
642 ' Organization_ID : '||rec_txn.organization_id;
643 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
644 ,l_api_message);
645 END IF;
646
647
648 IF rec_txn.transaction_type NOT IN ('ACCEPT','REJECT','TRANSFER') THEN
649
650 IF(rec_txn.transaction_type = 'CORRECT') THEN
651 l_stmt_num := 160;
652
653 SELECT PARENT.transaction_type
654 INTO l_parent_txn_type
655 FROM rcv_transactions RT,
656 rcv_transactions PARENT
657 WHERE RT.transaction_id = rec_txn.transaction_id
658 AND PARENT.transaction_id = RT.parent_transaction_id;
659
660 IF l_sLog THEN
661 l_api_message := 'l_parent_txn_type : '||l_parent_txn_type;
662 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
663 ,l_api_message);
664 END IF;
665
666 END IF;
667
668 IF((rec_txn.transaction_type IN ('RECEIVE','MATCH')) OR
669 (rec_txn.transaction_type = 'CORRECT' AND
670 l_parent_txn_type IN ('RECEIVE','MATCH'))) THEN
671 l_total_price := l_total_price +
672 rec_txn.primary_quantity*rec_txn.unit_landed_cost;
673 l_total_quantity := l_total_quantity + rec_txn.primary_quantity;
674 ELSIF ((rec_txn.transaction_type = 'RETURN TO VENDOR') OR
675 (rec_txn.transaction_type = 'CORRECT' AND
676 l_parent_txn_type = 'RETURN TO VENDOR')) THEN
677 l_total_price := l_total_price -
678 rec_txn.primary_quantity*rec_txn.unit_landed_cost;
679 l_total_quantity := l_total_quantity - rec_txn.primary_quantity;
680 ELSIF ((rec_txn.transaction_type = 'RETURN TO RECEIVING') OR
681 (rec_txn.transaction_type = 'CORRECT' AND
682 l_parent_txn_type = 'RETURN TO RECEIVING')) THEN
683 l_total_price := l_total_price +
684 rec_txn.primary_quantity*rec_txn.unit_landed_cost;
685 l_total_quantity := l_total_quantity + rec_txn.primary_quantity;
686 ELSIF ((rec_txn.transaction_type = 'DELIVER') OR
687 (rec_txn.transaction_type = 'CORRECT' AND l_parent_txn_type = 'DELIVER')) THEN
688 l_total_price := l_total_price -
689 rec_txn.primary_quantity*rec_txn.unit_landed_cost;
690 l_total_quantity := l_total_quantity - rec_txn.primary_quantity;
691 END IF;
692
693 IF l_sLog THEN
694 l_api_message := 'l_total_price : '||l_total_price ||
695 ' l_total_quantity : '||l_total_quantity;
696 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
697 ,l_api_message);
698 END IF;
699
700 END IF;
701 END LOOP;
702 l_total_price := l_total_price+l_rae_price;
703
704 END IF;
705
706 IF l_total_quantity = 0 THEN
707 x_unit_price := 0;
708 ELSE
709 x_unit_price := l_total_price/l_total_quantity;
710 END IF;
711
712 IF l_sLog THEN
713 l_api_message := ' l_total_price : '||l_total_price ||
714 ' l_total_quantity : '||l_total_quantity ||
715 ' x_unit_price : '||x_unit_price;
716 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
717 ,l_api_message);
718 END IF;
719
720
721 --- Standard check of p_commit
722 IF FND_API.to_Boolean(p_commit) THEN
723 COMMIT WORK;
724 END IF;
725
726
727 -- Standard Call to get message count and if count = 1, get message info
728 FND_MSG_PUB.Count_And_Get (
729 p_count => x_msg_count,
730 p_data => x_msg_data );
731
732
733 IF l_pLog THEN
734 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
735 ,'Get_ReceivingUnitPrice >>');
736 END IF;
737
738
739 EXCEPTION
740 WHEN FND_API.g_exc_error THEN
741 ROLLBACK TO Get_ReceivingUnitPrice_GRP;
742 x_return_status := FND_API.g_ret_sts_error;
743 FND_MSG_PUB.count_and_get
744 ( p_count => x_msg_count
745 , p_data => x_msg_data
746 );
747
748 WHEN FND_API.g_exc_unexpected_error THEN
749 ROLLBACK TO Get_ReceivingUnitPrice_GRP;
750 x_return_status := FND_API.g_ret_sts_unexp_error ;
751 FND_MSG_PUB.count_and_get
752 ( p_count => x_msg_count
753 , p_data => x_msg_data
754 );
755
756 WHEN OTHERS THEN
757 ROLLBACK TO Get_ReceivingUnitPrice_GRP;
758 x_return_status := fnd_api.g_ret_sts_unexp_error ;
759
760 /*IF l_uLog THEN*/ -- replaced this to work around GSCC false positives File.Sql.45 (bug #4480504)
761 IF fnd_log.level_unexpected >= g_log_level THEN
762 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
763 ,'Get_ReceivingUnitPrice : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
764 END IF;
765
766 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
767 THEN
768 FND_MSG_PUB.add_exc_msg
769 ( G_PKG_NAME,
770 l_api_name || 'Statement -'||to_char(l_stmt_num)
771 );
772 END IF;
773 FND_MSG_PUB.count_and_get
774 ( p_count => x_msg_count
775 , p_data => x_msg_data
776 );
777
778 END Get_ReceivingUnitPrice;
779
780 -----------------------------------------------------------------------------------------------
781 -- Start of comments
782 -- API name : Validate_PO_Purge
783 -- Type : Private
784 -- Function : To Validate if records in RAE and RRS can be
785 -- deleted for a list of PO_HEADER_ID's
786 -- Pre-reqs :
787 -- Parameters :
788 -- p_purge_entity_type IN VARCHAR2
789 -- The table of which the entity is the primary identifier
790 -- Values: PO_HEADERS_ALL, RCV_TRANSACTIONS
791 -- p_purge_in_rec IN RCV_AccrualUtilities_GRP.purge_in_rectype
792 -- Contains the List of PO_HEADER_ID's to be evaluated
793 -- x_purge_out_rec OUT NOCOPY RCV_AccrualUtilities_GRP.purge_out_rectype
794 -- Contains c character ('Y'/'N') indicating whether records
795 -- for corresponding header_id's can be deleted or not
796 ----------------------------------------------------------------------------------------------
797
798 PROCEDURE Validate_PO_Purge (
799 p_api_version IN NUMBER,
800 p_init_msg_list IN VARCHAR2,
801 p_commit IN VARCHAR2,
802 x_return_status OUT NOCOPY VARCHAR2,
803 x_msg_count OUT NOCOPY NUMBER,
804 x_msg_data OUT NOCOPY VARCHAR2,
805 p_purge_entity_type IN VARCHAR2,
806 p_purge_in_rec IN RCV_AccrualUtilities_GRP.purge_in_rectype,
807 x_purge_out_rec OUT NOCOPY RCV_AccrualUtilities_GRP.purge_out_rectype
808 ) IS
809
810 l_api_name constant varchar2(30) := 'Validate_PO_Purge';
811 l_api_version number := 1.0;
812
813 l_stmt_num number;
814 l_index binary_integer;
815
816 BEGIN
817 -- Establish API Savepoint
818 SAVEPOINT Validate_PO_Purge;
819
820 -- Standard call to check for call compatibility
821 l_stmt_num := 10;
822 IF NOT FND_API.Compatible_API_Call ( l_api_version,
823 p_api_version,
824 l_api_name,
825 G_PKG_NAME ) THEN
826 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
827 END IF;
828
829 -- Initialize message list
830 l_stmt_num := 20;
831 IF FND_API.to_boolean ( p_init_msg_list ) THEN
832 FND_MSG_PUB.initialize;
833 END IF;
834
835
836 -- Initialize API return status to success
837 x_return_status := FND_API.G_RET_STS_SUCCESS;
838
839 -- Set each header_id in p_purge_in_rec as Validated
840 -- This is marked as 'Y' in x_purge_out_rec in the corresponding
841 -- index.
842
843 l_index := p_purge_in_rec.entity_ids.FIRST;
844
845 LOOP
846 x_purge_out_rec.purge_allowed(l_index) := 'Y';
847 EXIT WHEN l_index = p_purge_in_rec.entity_ids.LAST;
848 l_index := p_purge_in_rec.entity_ids.NEXT(l_index);
849 END LOOP;
850
851 --- Standard check of p_commit
852 IF FND_API.to_Boolean(p_commit) THEN
853 COMMIT WORK;
854 END IF;
855
856
857 EXCEPTION
858 WHEN OTHERS THEN
859 ROLLBACK TO Validate_PO_Purge;
860 IF G_MSG_LEVEL_THRESHOLD <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH THEN
861 FND_MSG_PUB.Add_Exc_Msg(
862 p_pkg_name => G_PKG_NAME,
863 p_procedure_name => l_api_name,
864 p_error_text => 'Error at: '||
865 to_char(l_stmt_num) || ' '||
866 SQLERRM
867 );
868
869 END IF;
870 x_return_status := FND_API.G_RET_STS_ERROR;
871
872 END Validate_PO_Purge;
873
874 -----------------------------------------------------------------------------------------------
875 -- Start of comments
876 -- API name : Purge
877 -- Type : Private
878 -- Function : To delete the records in RAE and RRS corresponding to po_header_id's
879 -- specified.
880 -- Pre-reqs :
881 -- Parameters :
882 -- p_purge_entity_type IN VARCHAR2
883 -- The table of which the entity is the primary identifier
884 -- Values: PO_HEADERS_ALL, RCV_TRANSACTIONS
885 -- p_purge_in_rec IN RCV_AccrualUtilities_GRP.purge_in_rectype
886 -- Contains the List of PO_HEADER_ID's for which corresponding
887 -- records need to be deleted from RAE and RRS
888 ----------------------------------------------------------------------------------------------
889
890 PROCEDURE Purge (
891 p_api_version IN NUMBER,
892 p_init_msg_list IN VARCHAR2,
893 p_commit IN VARCHAR2,
894 x_return_status OUT NOCOPY VARCHAR2,
895 x_msg_count OUT NOCOPY NUMBER,
896 x_msg_data OUT NOCOPY VARCHAR2,
897 p_purge_entity_type IN VARCHAR2,
898 p_purge_in_rec IN RCV_AccrualUtilities_GRP.purge_in_rectype
899 ) IS
900
901 l_api_name constant varchar2(30) := 'Purge';
902 l_api_version number := 1.0;
903
904 l_stmt_num number;
905 l_index binary_integer;
906
907
908 l_acct_events RCV_AccrualUtilities_GRP.TBL_NUM;
909
910 INCORRECT_ENTITY EXCEPTION;
911 DELETE_FAILED EXCEPTION;
912
913 BEGIN
914 -- Establish API Savepoint
915 SAVEPOINT Purge;
916
917 -- Standard call to check for call compatibility
918 l_stmt_num := 10;
919 IF NOT FND_API.Compatible_API_Call ( l_api_version,
920 p_api_version,
921 l_api_name,
922 G_PKG_NAME ) THEN
923 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
924 END IF;
925
926 -- Initialize message list
927 l_stmt_num := 20;
928 IF FND_API.to_boolean ( p_init_msg_list ) THEN
929 FND_MSG_PUB.initialize;
930 END IF;
931
932
933 -- Initialize API return status to success
934 x_return_status := FND_API.G_RET_STS_SUCCESS;
935
936 l_stmt_num := 25;
937
938 IF p_purge_entity_type <> 'RCV_TRANSACTIONS' THEN
939 RAISE INCORRECT_ENTITY;
940 END IF;
941
942 BEGIN
943 l_stmt_num := 30;
944
945 FORALL l_index in p_purge_in_rec.entity_ids.FIRST..p_purge_in_rec.entity_ids.LAST
946 DELETE FROM RCV_ACCOUNTING_EVENTS
947 WHERE RCV_TRANSACTION_ID = p_purge_in_rec.entity_ids(l_index);
948 FORALL l_index in p_purge_in_rec.entity_ids.FIRST..p_purge_in_rec.entity_ids.LAST
949 DELETE FROM RCV_RECEIVING_SUB_LEDGER
950 WHERE RCV_TRANSACTION_ID = p_purge_in_rec.entity_ids(l_index);
951 EXCEPTION
952 WHEN OTHERS THEN
953 RAISE DELETE_FAILED;
954 END;
955 --- Standard check of p_commit
956 IF FND_API.to_Boolean(p_commit) THEN
957 COMMIT WORK;
958 END IF;
959
960
961 EXCEPTION
962 WHEN INCORRECT_ENTITY THEN
963 ROLLBACK TO Purge;
964 IF G_MSG_LEVEL_THRESHOLD <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH THEN
965 FND_MSG_PUB.Add_Exc_Msg(
966 p_pkg_name => G_PKG_NAME,
967 p_procedure_name => l_api_name,
968 p_error_text => 'Incorrect Entity Passed to API, RCV_TRANSACTION_ID expected'||
969 to_char(l_stmt_num) || ' '||
970 SQLERRM
971 );
972
973 END IF;
974 x_return_status := FND_API.G_RET_STS_ERROR;
975
976
977 WHEN DELETE_FAILED THEN
978 ROLLBACK TO Purge;
979 IF G_MSG_LEVEL_THRESHOLD <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH THEN
980 FND_MSG_PUB.Add_Exc_Msg(
981 p_pkg_name => G_PKG_NAME,
982 p_procedure_name => l_api_name,
983 p_error_text => 'Purge of RCV_Accounting_Events/RCV_Receiving_Sub_Ledger Failed'||
984 to_char(l_stmt_num) || ' '||
985 SQLERRM
986 );
987
988 END IF;
989 x_return_status := FND_API.G_RET_STS_ERROR;
990
991 WHEN OTHERS THEN
992 ROLLBACK To Purge;
993 IF G_MSG_LEVEL_THRESHOLD <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH THEN
994 FND_MSG_PUB.Add_Exc_Msg(
995 p_pkg_name => G_PKG_NAME,
996 p_procedure_name => l_api_name,
997 p_error_text => 'Error at: '||
998 to_char(l_stmt_num) || ' '||
999 SQLERRM
1000 );
1001
1002 END IF;
1003 x_return_status := FND_API.G_RET_STS_ERROR;
1004
1005 END Purge;
1006
1007 -----------------------------------------------------------------------------
1008 -- Start of comments
1009 -- API name : Get_encumReversalAmt
1010 -- Type : Group
1011 -- Function : To obtain total encumbrance reversal by PO distribution ID
1012 -- Pre-reqs :
1013 -- Parameters :
1014 -- IN : p_po_distribution_id IN NUMBER
1015 -- p_start_gl_date IN DATE Optional
1016 -- p_end_gl_date IN DATE Optional
1017 --
1018 -- RETURN : Encumbrance Reversal Amount
1019 -- This amount is always a positive number
1020 -- Version : Initial version 1.0
1021 -- Notes : This function will be used in the Encumbrance Detail Report
1022 -- and active encumbrance summary screen.
1023 -- The function will be called only if accrue on receipt is set to Yes
1024 --
1025 -- For inventory destinations,
1026 -- sum(MTA.base_transaction_value) for deliveries
1027 -- against the PO distribution
1028 -- that have been transferred to GL
1029 -- For expense destinations,
1030 -- sum(RRS.accounted_dr/cr for E rows) for
1031 -- deliveries against the PO distribution
1032 --
1033 -- Encumbrance is not supported currently for Shop Floor
1034 -- For Time Zone changes
1035 -- Assume that date sent in is server timezone,
1036 -- and validate with TxnDate
1037 -- End of comments
1038 -------------------------------------------------------------------------------
1039
1040 FUNCTION Get_encumReversalAmt(
1041 p_po_distribution_id IN NUMBER,
1042 p_start_txn_date IN VARCHAR2,
1043 p_end_txn_date IN VARCHAR2
1044 )
1045
1046 RETURN NUMBER
1047 IS
1048 l_encReversalAmt NUMBER := 0;
1049 l_accrueOnRcptFlg VARCHAR(1);
1050 l_destTypeCode VARCHAR2(25);
1051 l_stmt_num NUMBER := 0;
1052
1053 BEGIN
1054 l_accrueOnRcptFlg := 'Y';
1055 -- Obtain Accrue on Receipt flag and destination type from POD
1056
1057 /* MOAC Uptake - Replaced po_distributions with po_distributions_all as the view
1058 would be obsoleted in R12 */
1059
1060 l_stmt_num := 10;
1061 select nvl(accrue_on_receipt_flag,'N'),
1062 destination_type_code
1063 into l_accrueOnRcptFlg,
1064 l_destTypeCode
1065 from po_distributions_all
1066 where po_distribution_id = p_po_distribution_id;
1067
1068 -- Check if accrue on receipt, else return 0
1069 l_stmt_num := 20;
1070 if (l_accrueOnRcptFlg <> 'Y') then
1071 return l_encReversalAmt;
1072 end if;
1073
1074 -- Obtain Encumbrance Reversal Amount
1075 l_stmt_num := 30;
1076
1077 if (l_destTypeCode = 'INVENTORY') then
1078 l_stmt_num := 40;
1079 select sum(nvl(mta.base_transaction_value, 0))
1080 into l_encReversalAmt
1081 from mtl_material_transactions mmt,
1082 mtl_transaction_accounts mta,
1083 rcv_transactions rt
1084 where rt.po_distribution_id = p_po_distribution_id
1085 and fnd_date.date_to_canonical(rt.transaction_date)
1086 between nvl(p_start_txn_date,fnd_date.date_to_canonical(rt.transaction_date))
1087 and nvl(p_end_txn_date,fnd_date.date_to_canonical(sysdate))
1088 and mmt.rcv_transaction_id = rt.transaction_id
1089 and mta.transaction_id = mmt.transaction_id
1090 and mta.accounting_line_type = 15
1091 and mta.gl_batch_id <> -1;
1092
1093 elsif (l_destTypeCode = 'EXPENSE') then
1094 l_stmt_num := 50;
1095 select sum(nvl(rrs.accounted_dr,0)-nvl(rrs.accounted_cr,0))
1096 into l_encReversalAmt
1097 from rcv_receiving_sub_ledger rrs,
1098 rcv_transactions rt
1099 where rt.po_distribution_id = p_po_distribution_id
1100 and fnd_date.date_to_canonical(rt.transaction_date)
1101 between nvl(p_start_txn_date,fnd_date.date_to_canonical(rt.transaction_date))
1102 and nvl(p_end_txn_date,fnd_date.date_to_canonical(sysdate))
1103 and rrs.rcv_transaction_id = rt.transaction_id
1104 and rrs.actual_flag = 'E';
1105 else
1106 l_stmt_num := 60;
1107 l_encReversalAmt := 0;
1108 end if;
1109
1110 l_stmt_num := 70;
1111 return (abs(l_encReversalAmt));
1112
1113 EXCEPTION
1114 when no_data_found then
1115 l_encReversalAmt := 0;
1116 return (l_encReversalAmt);
1117
1118 when others then
1119 l_encReversalAmt := 0;
1120 return (l_encReversalAmt);
1121 END;
1122
1123 END RCV_AccrualUtilities_GRP;
[Home] [Help]