1 PACKAGE BODY RCV_AccrualUtilities_GRP AS
2 /* $Header: RCVGUTLB.pls 120.12.12020000.5 2012/12/21 10:43:10 yuyun 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 l_mmt_count NUMBER;
193 l_WT_COUNT NUMBER;
194 l_wcti_COUNT NUMBER;
195 l_retro_rae_price NUMBER;
196 CURSOR c_txn_history_csr (c_transaction_id NUMBER, c_valuation_date DATE) IS
197 SELECT RT.transaction_id transaction_id,
198 RT.transaction_type transaction_type,
199 RT.source_doc_quantity source_doc_quantity,
200 RT.organization_id organization_id,
201 RT.primary_quantity primary_quantity,
202 nvl(RT.unit_landed_cost,0) unit_landed_cost,
203 RT.po_unit_price
204 FROM rcv_transactions RT
205 WHERE ((c_valuation_date is not null and transaction_date <= c_valuation_date)
206 OR c_valuation_date is null)
207 START WITH transaction_id = c_transaction_id
208 CONNECT BY parent_transaction_id = PRIOR transaction_id;
209
210
211 BEGIN
212 l_return_status := fnd_api.g_ret_sts_success;
213 -- Standard start of API savepoint
214 SAVEPOINT Get_ReceivingUnitPrice_GRP;
215
216 l_stmt_num := 0;
217
218 IF l_pLog THEN
219 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.begin'
220 ,'Get_ReceivingUnitPrice <<');
221 END IF;
222
223 -- Standard call to check for call compatibility
224 IF NOT FND_API.Compatible_API_Call (
225 l_api_version,
226 p_api_version,
227 l_api_name,
228 G_PKG_NAME ) THEN
229 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
230 END IF;
231
232 -- Initialize message list if p_init_msg_list is set to TRUE
233 IF FND_API.to_Boolean(p_init_msg_list) THEN
234 FND_MSG_PUB.initialize;
235 END IF;
236
237 -- Initialize API return status to success
238 x_return_status := FND_API.G_RET_STS_SUCCESS;
239
240 IF l_sLog THEN
241 l_api_message := 'p_rcv_transaction_id : '||p_rcv_transaction_id;
242 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.begin'
243 ,l_api_message);
244 END IF;
245
246 l_stmt_num := 10;
247
248 SELECT rt.source_document_code, rt.po_header_id, rt.organization_id,
249 nvl(poll.lcm_flag,'N')
250 INTO l_source_doc_code, l_po_header_id, l_rcv_organization_id,
251 l_lcm_flag
252 FROM rcv_transactions rt,
253 po_line_locations_all poll
254 WHERE rt.transaction_id = p_rcv_transaction_id
255 AND rt.po_line_location_id = poll.line_location_id;
256
257 IF l_source_doc_code <> 'PO' THEN
258 FND_MESSAGE.set_name('PO','INVALID_SOURCE_DOCUMENT');
259 FND_MSG_pub.add;
260 IF l_errorLog THEN
261 FND_LOG.message(FND_LOG.LEVEL_ERROR,G_LOG_HEAD || '.'||l_api_name||l_stmt_num,FALSE);
262 END IF;
263 RAISE FND_API.g_exc_error;
264 END IF;
265
266
267 /* Get the parameters to determine whether this is a global procurement scenario. */
268
269 /* Get PO Operating Unit and Set of Books */
270 l_stmt_num := 20;
271 SELECT POH.org_id, HOU.set_of_books_id
272 INTO l_po_org_id, l_po_sob_id
273 FROM po_headers_all POH, hr_operating_units HOU
274 WHERE POH.org_id = HOU.organization_id
275 AND POH.po_header_id = l_po_header_id;
276
277 IF l_sLog THEN
278 l_api_message := 'l_po_org_id : '|| l_po_org_id || '; l_po_sob_id : ' || l_po_sob_id;
279 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
280 ,l_api_message);
281 END IF;
282
283 /* Get Receiving Operating Unit and Set of Books */
284 l_stmt_num := 30;
285 SELECT operating_unit, set_of_books_id
286 INTO l_rcv_org_id, l_rcv_sob_id
287 FROM cst_organization_definitions cod
288 WHERE organization_id = l_rcv_organization_id;
289
290 IF l_sLog THEN
291 l_api_message := 'l_rcv_org_id : '|| l_rcv_org_id || '; l_rcv_sob_id : ' || l_rcv_sob_id;
292 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
293 ,l_api_message);
294 END IF;
295
296
297 /* 12i: Since we are modifying the calling procedures to pass in parent receive/match txn,
298 we can just set l_rcv_transaction_id := p_rcv_transaction_id.
299
300 -- Bug #3094999. For Accept/Reject/Transfer transactions, mtl_supply gets updated with the
301 -- transaction_id of the Accept/Reject/Transfer transaction in place of the parent receipt.
302 -- However, accounting is only done against the parent receive transaction. Therefore, we
303 -- have to identify the parent receipt to find the actual unit price.
304 SELECT transaction_id
305 INTO l_rcv_transaction_id
306 FROM (
307 SELECT RT.transaction_id transaction_id,
308 RT.parent_transaction_id parent_transaction_id,
309 RT.transaction_type
310 FROM rcv_transactions RT
311 START WITH transaction_id = p_rcv_transaction_id
312 CONNECT BY transaction_id = PRIOR parent_transaction_id)
313 WHERE ((transaction_type = 'RECEIVE' and parent_transaction_id=-1)
314 OR transaction_type = 'MATCH');
315 */
316 l_rcv_transaction_id := p_rcv_transaction_id;
317
318 IF l_sLog THEN
319 l_api_message := 'l_rcv_transaction_id : '||l_rcv_transaction_id;
320 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.begin'
321 ,l_api_message);
322 END IF;
323
324 l_total_price := 0;
325 l_total_quantity := 0;
326 IF (l_lcm_flag = 'N') THEN
327 FOR rec_txn in c_txn_history_csr(l_rcv_transaction_id, p_valuation_date) LOOP
328
329 -- The situation where the unit_price on an event will vary from the PO
330 -- price occurs only in Global Procurement scenarios. In all other cases
331 -- including adjust events we use the latest PO price.
332 -- So for all global procurement related transactions, we compute the
333 -- average unit price from RAE and for non global procurement events,
334 -- we use the latest PO price.
335 l_stmt_num := 40;
336 IF l_sLog THEN
337 l_api_message := 'Processing Transaction_ID : '||rec_txn.transaction_id||
338 ' Transaction_Type : '||rec_txn.transaction_type||
339 ' Source_Doc_Qty : '||rec_txn.source_doc_quantity||
340 ' Organization_ID : '||rec_txn.organization_id;
341 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
342 ,l_api_message);
343 END IF;
344
345
346 IF rec_txn.transaction_type NOT IN ('ACCEPT','REJECT','TRANSFER') THEN
347
348 IF(rec_txn.transaction_type = 'CORRECT') THEN
349 l_stmt_num := 50;
350
351 SELECT PARENT.transaction_type
352 INTO l_parent_txn_type
353 FROM rcv_transactions RT, rcv_transactions PARENT
354 WHERE RT.transaction_id = rec_txn.transaction_id
355 AND PARENT.transaction_id = RT.parent_transaction_id;
356
357 IF l_sLog THEN
358 l_api_message := 'l_parent_txn_type : '||l_parent_txn_type;
359 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
360 ,l_api_message);
361 END IF;
362
363 END IF;
364
365 l_stmt_num := 60;
366
367 SELECT count(*)
368 INTO l_rae_count
369 FROM rcv_accounting_events RAE
370 WHERE RAE.rcv_transaction_id = rec_txn.transaction_id
371 AND RAE.organization_id = rec_txn.organization_id
372 AND RAE.event_type_id IN (1,2,3,4,5,6)
373 AND RAE.TRX_FLOW_HEADER_ID IS NOT NULL;
374 /* This condition RAE.trx_flow_header_id IS NOT NULL limits this to
375 events in RAE for global procurement scenarios only */
376
377
378 IF (l_rae_count = 0) then
379 -- Transaction was done prior to Patchset J or
380 -- It is a non-global procurement scenario
381 -- Default to POLL.price_override in this case.
382 -- Or, it could be a Deliver, RTR, or Correct to Deliver or RTR
383 -- for Inventory or WIP destination types in a global procurement scenario.
384
385 /* Bug#14737903 :
386 add condition AND l_po_sob_id <> l_rcv_sob_id ,
387 when l_po_sob_id = l_rcv_sob_id, it is not considerred as global procurement, and in this case, we can Procure in one OU,
388 and receive in another OU without transaction flow set between them, hence add AND l_po_sob_id <> l_rcv_sob_id below
389 */
390 IF (l_po_org_id <> l_rcv_org_id AND l_po_sob_id <> l_rcv_sob_id) THEN
391 /*
392 The only valid scenario that brings us to this code branch satisfies
393 the following conditions:
394 1. This is a global procurement scenario
395 2. If (1) is true, since the transaction had no events in RAE (l_rae_count = 0),
396 the transaction must be a Deliver, RTR, or Correct to Deliver or RTR
397 for Inventory or WIP destinations only.
398 (For other transaction types in a global procurement scenario and
399 for Expense destinations, l_rae_count > 0)
400 */
401
402 l_stmt_num := 70;
403
404 SELECT POD.destination_type_code
405 INTO l_destination_type_code
406 FROM po_distributions_all POD, rcv_transactions RT
407 WHERE POD.po_distribution_id = RT.po_distribution_id
408 AND RT.transaction_id = rec_txn.transaction_id;
409
410 IF l_sLog THEN
411 l_api_message := 'l_destination_type_code : '|| l_destination_type_code;
412 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
413 ,l_api_message);
414 END IF;
415
416 /*
417 * The formula to get the actual transaction value from MMT or WT would be:
418 * (MMT.transaction_cost * primary_quantity) or
419 * (WT.actual_resource_rate * primary_quantity)
420 *
421 * However, to yield the correct unit price in global procurement
422 * scenarios with UOM conversion (where primary_quantity <>
423 * source_doc_quantity), l_txn_price must be equal to
424 * unit price * RT.source_doc_quantity, since in the end the API
425 * divides the total transaction value by RT.source_doc_quantity
426 * to get the unit price.
427 * Failing to convert to RT.source_doc_quantity will cause the API
428 * to calculate the incorrect unit price in global procurement scenarios
429 * with UOM conversion.
430 */
431
432 IF (l_destination_type_code = 'INVENTORY') THEN
433
434 /* For Inventory destinations, use MMT.transaction_cost. */
435
436 l_stmt_num := 80;
437 SELECT (MMT.transaction_cost * rec_txn.source_doc_quantity)
438 INTO l_txn_price
439 FROM mtl_material_transactions MMT
440 WHERE MMT.rcv_transaction_id = rec_txn.transaction_id
441 AND MMT.organization_id = rec_txn.organization_id;
442
443
444 ELSIF (l_destination_type_code = 'SHOP FLOOR') THEN
445
446 /* For Shop Floor destinations, use WT.actual_resource_rate.
447 * Select from WT if the transaction is costed, and WCTI otherwise.
448 */
449
450 BEGIN
451 l_stmt_num := 90; /*Bug#14584624: Get absolute value to fix the value of Return to Receiving */
452 SELECT (((nvl(WT.actual_resource_rate,0) *
453 decode(nvl(WT.primary_quantity,0),
454 0,decode(nvl(WT.actual_resource_rate,0),0,0,1),
455 abs(nvl(WT.primary_quantity,0))
456 )
457 )/rec_txn.primary_quantity)*
458 rec_txn.source_doc_quantity)
459 INTO l_txn_price
460 FROM wip_transactions WT
461 WHERE WT.rcv_transaction_id = rec_txn.transaction_id
462 AND WT.organization_id = rec_txn.organization_id;
463 EXCEPTION
464 WHEN no_data_found THEN
465 SELECT (((nvl(WCTI.actual_resource_rate,0) *
466 decode(nvl(WCTI.primary_quantity,0),
467 0,decode(nvl(WCTI.actual_resource_rate,0),0,0,1),
468 abs(nvl(WCTI.primary_quantity,0))
469 )
470 )/rec_txn.primary_quantity)*
471 rec_txn.source_doc_quantity)
472 INTO l_txn_price
473 FROM wip_cost_txn_interface WCTI
474 WHERE WCTI.rcv_transaction_id = rec_txn.transaction_id
475 AND WCTI.organization_id = rec_txn.organization_id;
476 END;
477
478 END IF; /* IF (l_destination_type_code = ) */
479
480 IF l_sLog THEN
481 l_api_message := 'l_txn_price : '||l_txn_price;
482 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
483 ,l_api_message);
484 END IF;
485
486 ELSE --l_po_org_id <> l_rcv_org_id
487 /* This is a non-global procurement scenario or a pre-Patchset J transaction */
488 /* Check to see if RAE exists If it exists then take the price from RAE.
489 Also make sure that RETRO PRICE Change Events are handled correctly */
490 /* Get the RETRO PRICE change first. As it could happen that a delivery transaction
491 has RAE for retro and MMT for delivery we need to consider both */
492 l_stmt_num := 92 ;
493 select nvl(sum((RAE.unit_price-RAE.prior_unit_price) * nvl(RAE.currency_conversion_rate,1)*
494 RAE.source_doc_quantity/RAE.primary_quantity * RAE.source_doc_quantity),0)
495 into l_retro_rae_price
496 from RCV_ACCOUNTING_EVENTS RAE
497 WHERE RAE.rcv_transaction_id = rec_txn.transaction_id
498 AND RAE.organization_id = rec_txn.organization_id
499 AND RAE.event_type_id IN (7,8)
500 AND ((p_valuation_date is not null
501 and RAE.transaction_date <= p_valuation_date)
502 OR p_valuation_date is null );
503
504 l_stmt_num := 94 ;
505 SELECT count(*),sum(decode(RAE.primary_quantity, 0, 0,
506 (RAE.unit_price * nvl(RAE.currency_conversion_rate,1)*
507 RAE.source_doc_quantity/RAE.primary_quantity * RAE.source_doc_quantity)
508 )
509 )
510 INTO l_rae_count,l_txn_price
511 FROM rcv_accounting_events RAE
512 WHERE RAE.rcv_transaction_id = rec_txn.transaction_id
513 AND RAE.organization_id = rec_txn.organization_id
514 AND RAE.event_type_id IN (1,2,3,4,5,6)
515 AND ((p_valuation_date is not null
516 and RAE.transaction_date <= p_valuation_date)
517 OR p_valuation_date is null );
518 IF (l_rae_count = 0) then
519 /* In Non GLobal Procurement after patchet J delivery might be in MMT,WT,WCTI and not in RAE*/
520 l_stmt_num := 96 ;
521 Select count(*),sum((MMT.transaction_cost * rec_txn.source_doc_quantity))
522 into l_mmt_count,l_txn_price
523 from mtl_material_transactions MMT
524 where MMT.rcv_transaction_id = rec_txn.transaction_id
525 AND MMT.organization_id = rec_txn.organization_id;
526
527 IF(l_mmt_count = 0) then
528 l_stmt_num := 98 ; /*Bug#14584624: Get absolute value to fix the value of Return to Receiving */
529 SELECT count(*),
530 sum ( ( ( nvl(WT.actual_resource_rate,0) *
531 decode(nvl(WT.primary_quantity,0),
532 0,decode(nvl(WT.actual_resource_rate,0),0,0,1),
533 abs(nvl(WT.primary_quantity,0))
534 )
535 )/rec_txn.primary_quantity
536 )*rec_txn.source_doc_quantity
537 )
538 INTO l_WT_COUNT,
539 l_txn_price
540 FROM wip_transactions WT
541 WHERE WT.rcv_transaction_id = rec_txn.transaction_id
542 AND WT.organization_id = rec_txn.organization_id;
543 /* For Bug 13547638 making sure that if WT has actual_resource_rate as 0 even if po_unit_price <> 0
544 then calculate from RT as GL is wrong */
545 IF(l_wt_count <> 0 and l_txn_price = 0 and nvl(rec_txn.po_unit_price,0) <> 0 and rec_txn.primary_quantity <> 0 ) then
546 l_wt_count := 0 ;
547 END IF;
548 IF (l_WT_COUNT = 0) then
549 l_stmt_num := 99 ; /*Bug#14584624: Get absolute value to fix the value of Return to Receiving */
550 SELECT count(*),
551 sum ( ( (nvl(WCTI.actual_resource_rate,0) *
552 decode(nvl(WCTI.primary_quantity,0),
553 0,decode(nvl(WCTI.actual_resource_rate,0),0,0,1),
554 abs(nvl(WCTI.primary_quantity,0))
555 )
556 )/rec_txn.primary_quantity
557 )*rec_txn.source_doc_quantity
558 )
559 INTO l_wcti_count,
560 l_txn_price
561 FROM wip_cost_txn_interface WCTI
562 WHERE WCTI.rcv_transaction_id = rec_txn.transaction_id
563 AND WCTI.organization_id = rec_txn.organization_id;
564 /* For Bug 13547638 making sure that if WCTI has actual_resource_rate as 0 even if po_unit_price <> 0
565 then calculate from RT as GL is wrong */
566 IF(l_wcti_count <> 0 and l_txn_price = 0 and nvl(rec_txn.po_unit_price,0) <> 0 and rec_txn.primary_quantity <> 0) then
567 l_wcti_count := 0 ;
568 END IF;
569 IF (l_wcti_count = 0) then /*prior patchset j */
570 l_stmt_num := 100;
571 SELECT DECODE (PO_DISTRIBUTION_ID, NULL, 0, 1)
572 INTO l_dist_flag
573 FROM RCV_TRANSACTIONS
574 WHERE TRANSACTION_ID = rec_txn.transaction_id;
575
576 IF l_sLog THEN
577 l_api_message := 'l_dist_flag : '||l_dist_flag;
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
583 l_stmt_num := 110;
584 IF l_dist_flag = 1 THEN
585 /* Bug 6266340: Modified the Select clause to check for 0 primary quantity */
586 SELECT decode(RT.primary_quantity, 0, 0,
587 ((nvl(RT.po_unit_price,PLL.price_override) * RT.source_doc_quantity +
588 PO_TAX_SV.GET_TAX('PO', POD.PO_DISTRIBUTION_ID) *
589 RT.SOURCE_DOC_QUANTITY/POD.QUANTITY_ORDERED) *
590 decode (nvl(PLL.match_option,'P'),
591 'R',NVL(RT.currency_conversion_rate,1),
592 'P',NVL(NVL(POD.rate,POH.rate),1)) *
593 (RT.source_doc_quantity/RT.primary_quantity)))
594 INTO l_txn_price
595 FROM rcv_transactions RT,
596 po_distributions_all POD,
597 po_line_locations_all PLL,
598 po_headers_all POH
599 WHERE RT.transaction_id = rec_txn.transaction_id
600 AND POD.po_distribution_id = RT.po_distribution_id
601 AND PLL.line_location_id = RT.po_line_location_id
602 AND POH.po_header_id = RT.po_header_id;
603
604 IF l_sLog THEN
605 l_api_message := 'l_txn_price : '||l_txn_price;
606 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
607 ,l_api_message);
608 END IF;
609
610 ELSE
611 l_stmt_num := 120;
612 SELECT
613 NVL(SUM(PO_TAX_SV.get_tax('PO', POD.PO_DISTRIBUTION_ID)), 0)
614 INTO
615 l_tax
616 from
617 po_distributions_all pod,
618 po_line_locations_all pol,
619 rcv_transactions rt
620 where
621 rt.transaction_id = rec_txn.transaction_id
622 and rt.po_line_location_id = pol.line_location_id
623 and pod.line_location_id = pol.line_location_id;
624
625 IF l_sLog THEN
626 l_api_message := 'l_tax : '||l_tax;
627 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
628 ,l_api_message);
629 END IF;
630
631 l_stmt_num := 130;
632
633 /* Bug 6266340: Modified the Select clause to check for 0 primary quantity */
634 SELECT decode(RT.primary_quantity, 0, 0,
635 ((nvl(RT.po_unit_price,PLL.price_override) * RT.source_doc_quantity +
636 RT.SOURCE_DOC_QUANTITY/PLL.QUANTITY * l_tax) *
637 decode (nvl(PLL.match_option,'P'),
638 'R',NVL(RT.currency_conversion_rate, 1),
639 'P',NVL(POH.rate, 1)) *
640 (RT.source_doc_quantity/RT.primary_quantity)))
641 INTO l_txn_price
642 FROM rcv_transactions RT,
643 po_line_locations_all PLL,
644 po_headers_all POH
645 WHERE RT.transaction_id = rec_txn.transaction_id
646 AND PLL.line_location_id = RT.po_line_location_id
647 AND POH.po_header_id = RT.po_header_id;
648
649 IF l_sLog THEN
650 l_api_message := 'l_txn_price : '||l_txn_price;
651 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
652 ,l_api_message);
653 END IF;
654
655 END IF; -- l_dist_flag = 1
656 END IF;/*WCTI COUNT */
657 END IF; /*WT COUNT*/
658 END IF; /* MMT_COUNT*/
659 END IF; /* RAE COUNT */
660 END IF; -- l_po_org_id <> l_rcv_org_id
661 ELSE
662 -- Transaction was done after Patchset J and it relates to global procurement
663 -- RAE.unit_price can be used.
664 -- A sum is necessary here since in the case of a standard receipt,
665 -- you could have multiple events - one for each distribution. */
666 -- Added organization check so adjust events in global procurement
667 -- scenarios would get excluded.
668
669 l_stmt_num := 140;
670
671 /* Bug 6266340: Modified the Select clause to check for 0 primary quantity */
672 SELECT sum(decode(RAE.primary_quantity, 0, 0,
673 (RAE.unit_price * nvl(RAE.currency_conversion_rate,1)*
674 RAE.source_doc_quantity/RAE.primary_quantity * RAE.source_doc_quantity)))
675 INTO l_txn_price
676 FROM rcv_accounting_events RAE
677 WHERE RAE.rcv_transaction_id = rec_txn.transaction_id
678 AND RAE.organization_id = rec_txn.organization_id
679 AND RAE.event_type_id IN (1,2,3,4,5,6);
680
681 IF l_sLog THEN
682 l_api_message := 'l_txn_price : '||l_txn_price;
683 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
684 ,l_api_message);
685 END IF;
686
687 END IF;
688
689
690 /* for each transaction, increment or decrement the running total price and quantity. */
691 l_stmt_num := 150;
692 -- Receive, Match and RTR transactions increase value in Receiving Inspection.
693 -- Deliver and RTV transactions decrease value in Receiving Inspection.
694 -- Corrections follow the behavior of the parent.
695 IF((rec_txn.transaction_type IN ('RECEIVE','MATCH')) OR
696 (rec_txn.transaction_type = 'CORRECT' AND l_parent_txn_type IN ('RECEIVE','MATCH'))) THEN
697 l_total_price := l_total_price + l_txn_price + nvl(l_retro_rae_price,0);
698 l_total_quantity := l_total_quantity + rec_txn.source_doc_quantity;
699 ELSIF ((rec_txn.transaction_type = 'RETURN TO VENDOR') OR
700 (rec_txn.transaction_type = 'CORRECT' AND l_parent_txn_type = 'RETURN TO VENDOR')) THEN
701 l_total_price := l_total_price - l_txn_price - nvl(l_retro_rae_price,0);
702 l_total_quantity := l_total_quantity - rec_txn.source_doc_quantity;
703 ELSIF ((rec_txn.transaction_type = 'RETURN TO RECEIVING') OR
704 (rec_txn.transaction_type = 'CORRECT' AND l_parent_txn_type = 'RETURN TO RECEIVING')) THEN
705 l_total_price := l_total_price + l_txn_price + nvl(l_retro_rae_price,0);
706 l_total_quantity := l_total_quantity + rec_txn.source_doc_quantity;
707 ELSIF ((rec_txn.transaction_type = 'DELIVER') OR
708 (rec_txn.transaction_type = 'CORRECT' AND l_parent_txn_type = 'DELIVER')) THEN
709 l_total_price := l_total_price - l_txn_price - nvl(l_retro_rae_price,0);
710 l_total_quantity := l_total_quantity - rec_txn.source_doc_quantity;
711 END IF;
712
713 IF l_sLog THEN
714 l_api_message := 'l_total_price : '||l_total_price ||
715 ' l_total_quantity : '||l_total_quantity;
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 END IF; /* IF rec_txn.transaction_type... */
721 END LOOP;
722 ELSE /*LCM ENABLED*/
723 l_stmt_num := 150;
724 l_rae_price := 0;
725 SELECT nvl(sum(decode(rae.event_type_id,
726 15,rae.primary_quantity,
727 -1*rae.primary_quantity)*
728 (rae.unit_price-rae.prior_unit_price)),0)
729 INTO l_rae_price
730 FROM rcv_accounting_events rae
731 WHERE rae.event_type_id IN (15,16,17)
732 AND rae.rcv_transaction_id = l_rcv_transaction_id
733 AND((p_valuation_date is not null
734 and rae.transaction_date <= p_valuation_date)
735 OR p_valuation_date is null);
736 IF l_sLog THEN
737 l_api_message := 'l_rae_price : '||l_rae_price ;
738 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
739 ,l_api_message);
740 END IF;
741
742 FOR rec_txn in c_txn_history_csr(l_rcv_transaction_id, p_valuation_date) LOOP
743 IF l_sLog THEN
744 l_api_message := 'Processing Transaction_ID : '||rec_txn.transaction_id||
745 ' Transaction_Type : '||rec_txn.transaction_type||
746 ' Source_Doc_Qty : '||rec_txn.source_doc_quantity||
747 ' Organization_ID : '||rec_txn.organization_id;
748 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
749 ,l_api_message);
750 END IF;
751
752
753 IF rec_txn.transaction_type NOT IN ('ACCEPT','REJECT','TRANSFER') THEN
754
755 IF(rec_txn.transaction_type = 'CORRECT') THEN
756 l_stmt_num := 160;
757
758 SELECT PARENT.transaction_type
759 INTO l_parent_txn_type
760 FROM rcv_transactions RT,
761 rcv_transactions PARENT
762 WHERE RT.transaction_id = rec_txn.transaction_id
763 AND PARENT.transaction_id = RT.parent_transaction_id;
764
765 IF l_sLog THEN
766 l_api_message := 'l_parent_txn_type : '||l_parent_txn_type;
767 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
768 ,l_api_message);
769 END IF;
770
771 END IF;
772
773 IF((rec_txn.transaction_type IN ('RECEIVE','MATCH')) OR
774 (rec_txn.transaction_type = 'CORRECT' AND
775 l_parent_txn_type IN ('RECEIVE','MATCH'))) THEN
776 l_total_price := l_total_price +
777 rec_txn.primary_quantity*rec_txn.unit_landed_cost;
778 l_total_quantity := l_total_quantity + rec_txn.primary_quantity;
779 ELSIF ((rec_txn.transaction_type = 'RETURN TO VENDOR') OR
780 (rec_txn.transaction_type = 'CORRECT' AND
781 l_parent_txn_type = 'RETURN TO VENDOR')) THEN
782 l_total_price := l_total_price -
783 rec_txn.primary_quantity*rec_txn.unit_landed_cost;
784 l_total_quantity := l_total_quantity - rec_txn.primary_quantity;
785 ELSIF ((rec_txn.transaction_type = 'RETURN TO RECEIVING') OR
786 (rec_txn.transaction_type = 'CORRECT' AND
787 l_parent_txn_type = 'RETURN TO RECEIVING')) THEN
788 l_total_price := l_total_price +
789 rec_txn.primary_quantity*rec_txn.unit_landed_cost;
790 l_total_quantity := l_total_quantity + rec_txn.primary_quantity;
791 ELSIF ((rec_txn.transaction_type = 'DELIVER') OR
792 (rec_txn.transaction_type = 'CORRECT' AND l_parent_txn_type = 'DELIVER')) THEN
793 l_total_price := l_total_price -
794 rec_txn.primary_quantity*rec_txn.unit_landed_cost;
795 l_total_quantity := l_total_quantity - rec_txn.primary_quantity;
796 END IF;
797
798 IF l_sLog THEN
799 l_api_message := 'l_total_price : '||l_total_price ||
800 ' l_total_quantity : '||l_total_quantity;
801 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
802 ,l_api_message);
803 END IF;
804
805 END IF;
806 END LOOP;
807 l_total_price := l_total_price+l_rae_price;
808
809 END IF;
810
811 IF l_total_quantity = 0 THEN
812 x_unit_price := 0;
813 ELSE
814 x_unit_price := l_total_price/l_total_quantity;
815 END IF;
816
817 IF l_sLog THEN
818 l_api_message := ' l_total_price : '||l_total_price ||
819 ' l_total_quantity : '||l_total_quantity ||
820 ' x_unit_price : '||x_unit_price;
821 FND_LOG.string(FND_LOG.LEVEL_STATEMENT,G_LOG_HEAD || '.'||l_api_name||'.'||l_stmt_num
822 ,l_api_message);
823 END IF;
824
825
826 --- Standard check of p_commit
827 IF FND_API.to_Boolean(p_commit) THEN
828 COMMIT WORK;
829 END IF;
830
831
832 -- Standard Call to get message count and if count = 1, get message info
833 FND_MSG_PUB.Count_And_Get (
834 p_count => x_msg_count,
835 p_data => x_msg_data );
836
837
838 IF l_pLog THEN
839 FND_LOG.string(FND_LOG.LEVEL_PROCEDURE,G_LOG_HEAD || '.'||l_api_name||'.end'
840 ,'Get_ReceivingUnitPrice >>');
841 END IF;
842
843
844 EXCEPTION
845 WHEN FND_API.g_exc_error THEN
846 ROLLBACK TO Get_ReceivingUnitPrice_GRP;
847 x_return_status := FND_API.g_ret_sts_error;
848 FND_MSG_PUB.count_and_get
849 ( p_count => x_msg_count
850 , p_data => x_msg_data
851 );
852
853 WHEN FND_API.g_exc_unexpected_error THEN
854 ROLLBACK TO Get_ReceivingUnitPrice_GRP;
855 x_return_status := FND_API.g_ret_sts_unexp_error ;
856 FND_MSG_PUB.count_and_get
857 ( p_count => x_msg_count
858 , p_data => x_msg_data
859 );
860
861 WHEN OTHERS THEN
862 ROLLBACK TO Get_ReceivingUnitPrice_GRP;
863 x_return_status := fnd_api.g_ret_sts_unexp_error ;
864
865 /*IF l_uLog THEN*/ -- replaced this to work around GSCC false positives File.Sql.45 (bug #4480504)
866 IF fnd_log.level_unexpected >= g_log_level THEN
867 FND_LOG.string(FND_LOG.LEVEL_UNEXPECTED,G_LOG_HEAD || '.'||l_api_name||l_stmt_num
868 ,'Get_ReceivingUnitPrice : '||l_stmt_num||' : '||substr(SQLERRM,1,200));
869 END IF;
870
871 IF fnd_msg_pub.check_msg_level(fnd_msg_pub.g_msg_lvl_unexp_error)
872 THEN
873 FND_MSG_PUB.add_exc_msg
874 ( G_PKG_NAME,
875 l_api_name || 'Statement -'||to_char(l_stmt_num)
876 );
877 END IF;
878 FND_MSG_PUB.count_and_get
879 ( p_count => x_msg_count
880 , p_data => x_msg_data
881 );
882
883 END Get_ReceivingUnitPrice;
884
885 -----------------------------------------------------------------------------------------------
886 -- Start of comments
887 -- API name : Validate_PO_Purge
888 -- Type : Private
889 -- Function : To Validate if records in RAE and RRS can be
890 -- deleted for a list of PO_HEADER_ID's
891 -- Pre-reqs :
892 -- Parameters :
893 -- p_purge_entity_type IN VARCHAR2
894 -- The table of which the entity is the primary identifier
895 -- Values: PO_HEADERS_ALL, RCV_TRANSACTIONS
896 -- p_purge_in_rec IN RCV_AccrualUtilities_GRP.purge_in_rectype
897 -- Contains the List of PO_HEADER_ID's to be evaluated
898 -- x_purge_out_rec OUT NOCOPY RCV_AccrualUtilities_GRP.purge_out_rectype
899 -- Contains c character ('Y'/'N') indicating whether records
900 -- for corresponding header_id's can be deleted or not
901 ----------------------------------------------------------------------------------------------
902
903 PROCEDURE Validate_PO_Purge (
904 p_api_version IN NUMBER,
905 p_init_msg_list IN VARCHAR2,
906 p_commit IN VARCHAR2,
907 x_return_status OUT NOCOPY VARCHAR2,
908 x_msg_count OUT NOCOPY NUMBER,
909 x_msg_data OUT NOCOPY VARCHAR2,
910 p_purge_entity_type IN VARCHAR2,
911 p_purge_in_rec IN RCV_AccrualUtilities_GRP.purge_in_rectype,
912 x_purge_out_rec OUT NOCOPY RCV_AccrualUtilities_GRP.purge_out_rectype
913 ) IS
914
915 l_api_name constant varchar2(30) := 'Validate_PO_Purge';
916 l_api_version number := 1.0;
917
918 l_stmt_num number;
919 l_index binary_integer;
920
921 BEGIN
922 -- Establish API Savepoint
923 SAVEPOINT Validate_PO_Purge;
924
925 -- Standard call to check for call compatibility
926 l_stmt_num := 10;
927 IF NOT FND_API.Compatible_API_Call ( l_api_version,
928 p_api_version,
929 l_api_name,
930 G_PKG_NAME ) THEN
931 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
932 END IF;
933
934 -- Initialize message list
935 l_stmt_num := 20;
936 IF FND_API.to_boolean ( p_init_msg_list ) THEN
937 FND_MSG_PUB.initialize;
938 END IF;
939
940
941 -- Initialize API return status to success
942 x_return_status := FND_API.G_RET_STS_SUCCESS;
943
944 -- Set each header_id in p_purge_in_rec as Validated
945 -- This is marked as 'Y' in x_purge_out_rec in the corresponding
946 -- index.
947
948 l_index := p_purge_in_rec.entity_ids.FIRST;
949
950 LOOP
951 x_purge_out_rec.purge_allowed(l_index) := 'Y';
952 EXIT WHEN l_index = p_purge_in_rec.entity_ids.LAST;
953 l_index := p_purge_in_rec.entity_ids.NEXT(l_index);
954 END LOOP;
955
956 --- Standard check of p_commit
957 IF FND_API.to_Boolean(p_commit) THEN
958 COMMIT WORK;
959 END IF;
960
961
962 EXCEPTION
963 WHEN OTHERS THEN
964 ROLLBACK TO Validate_PO_Purge;
965 IF G_MSG_LEVEL_THRESHOLD <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH THEN
966 FND_MSG_PUB.Add_Exc_Msg(
967 p_pkg_name => G_PKG_NAME,
968 p_procedure_name => l_api_name,
969 p_error_text => 'Error at: '||
970 to_char(l_stmt_num) || ' '||
971 SQLERRM
972 );
973
974 END IF;
975 x_return_status := FND_API.G_RET_STS_ERROR;
976
977 END Validate_PO_Purge;
978
979 -----------------------------------------------------------------------------------------------
980 -- Start of comments
981 -- API name : Purge
982 -- Type : Private
983 -- Function : To delete the records in RAE and RRS corresponding to po_header_id's
984 -- specified.
985 -- Pre-reqs :
986 -- Parameters :
987 -- p_purge_entity_type IN VARCHAR2
988 -- The table of which the entity is the primary identifier
989 -- Values: PO_HEADERS_ALL, RCV_TRANSACTIONS
990 -- p_purge_in_rec IN RCV_AccrualUtilities_GRP.purge_in_rectype
991 -- Contains the List of PO_HEADER_ID's for which corresponding
992 -- records need to be deleted from RAE and RRS
993 ----------------------------------------------------------------------------------------------
994
995 PROCEDURE Purge (
996 p_api_version IN NUMBER,
997 p_init_msg_list IN VARCHAR2,
998 p_commit IN VARCHAR2,
999 x_return_status OUT NOCOPY VARCHAR2,
1000 x_msg_count OUT NOCOPY NUMBER,
1001 x_msg_data OUT NOCOPY VARCHAR2,
1002 p_purge_entity_type IN VARCHAR2,
1003 p_purge_in_rec IN RCV_AccrualUtilities_GRP.purge_in_rectype
1004 ) IS
1005
1006 l_api_name constant varchar2(30) := 'Purge';
1007 l_api_version number := 1.0;
1008
1009 l_stmt_num number;
1010 l_index binary_integer;
1011
1012
1013 l_acct_events RCV_AccrualUtilities_GRP.TBL_NUM;
1014
1015 INCORRECT_ENTITY EXCEPTION;
1016 DELETE_FAILED EXCEPTION;
1017
1018 BEGIN
1019 -- Establish API Savepoint
1020 SAVEPOINT Purge;
1021
1022 -- Standard call to check for call compatibility
1023 l_stmt_num := 10;
1024 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1025 p_api_version,
1026 l_api_name,
1027 G_PKG_NAME ) THEN
1028 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1029 END IF;
1030
1031 -- Initialize message list
1032 l_stmt_num := 20;
1033 IF FND_API.to_boolean ( p_init_msg_list ) THEN
1034 FND_MSG_PUB.initialize;
1035 END IF;
1036
1037
1038 -- Initialize API return status to success
1039 x_return_status := FND_API.G_RET_STS_SUCCESS;
1040
1041 l_stmt_num := 25;
1042
1043 IF p_purge_entity_type <> 'RCV_TRANSACTIONS' THEN
1044 RAISE INCORRECT_ENTITY;
1045 END IF;
1046
1047 BEGIN
1048 l_stmt_num := 30;
1049
1050 FORALL l_index in p_purge_in_rec.entity_ids.FIRST..p_purge_in_rec.entity_ids.LAST
1051 DELETE FROM RCV_ACCOUNTING_EVENTS
1052 WHERE RCV_TRANSACTION_ID = p_purge_in_rec.entity_ids(l_index);
1053 FORALL l_index in p_purge_in_rec.entity_ids.FIRST..p_purge_in_rec.entity_ids.LAST
1054 DELETE FROM RCV_RECEIVING_SUB_LEDGER
1055 WHERE RCV_TRANSACTION_ID = p_purge_in_rec.entity_ids(l_index);
1056 EXCEPTION
1057 WHEN OTHERS THEN
1058 RAISE DELETE_FAILED;
1059 END;
1060 --- Standard check of p_commit
1061 IF FND_API.to_Boolean(p_commit) THEN
1062 COMMIT WORK;
1063 END IF;
1064
1065
1066 EXCEPTION
1067 WHEN INCORRECT_ENTITY THEN
1068 ROLLBACK TO Purge;
1069 IF G_MSG_LEVEL_THRESHOLD <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH THEN
1070 FND_MSG_PUB.Add_Exc_Msg(
1071 p_pkg_name => G_PKG_NAME,
1072 p_procedure_name => l_api_name,
1073 p_error_text => 'Incorrect Entity Passed to API, RCV_TRANSACTION_ID expected'||
1074 to_char(l_stmt_num) || ' '||
1075 SQLERRM
1076 );
1077
1078 END IF;
1079 x_return_status := FND_API.G_RET_STS_ERROR;
1080
1081
1082 WHEN DELETE_FAILED THEN
1083 ROLLBACK TO Purge;
1084 IF G_MSG_LEVEL_THRESHOLD <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH THEN
1085 FND_MSG_PUB.Add_Exc_Msg(
1086 p_pkg_name => G_PKG_NAME,
1087 p_procedure_name => l_api_name,
1088 p_error_text => 'Purge of RCV_Accounting_Events/RCV_Receiving_Sub_Ledger Failed'||
1089 to_char(l_stmt_num) || ' '||
1090 SQLERRM
1091 );
1092
1093 END IF;
1094 x_return_status := FND_API.G_RET_STS_ERROR;
1095
1096 WHEN OTHERS THEN
1097 ROLLBACK To Purge;
1098 IF G_MSG_LEVEL_THRESHOLD <= FND_MSG_PUB.G_MSG_LVL_DEBUG_HIGH THEN
1099 FND_MSG_PUB.Add_Exc_Msg(
1100 p_pkg_name => G_PKG_NAME,
1101 p_procedure_name => l_api_name,
1102 p_error_text => 'Error at: '||
1103 to_char(l_stmt_num) || ' '||
1104 SQLERRM
1105 );
1106
1107 END IF;
1108 x_return_status := FND_API.G_RET_STS_ERROR;
1109
1110 END Purge;
1111
1112 -----------------------------------------------------------------------------
1113 -- Start of comments
1114 -- API name : Get_encumReversalAmt
1115 -- Type : Group
1116 -- Function : To obtain total encumbrance reversal by PO distribution ID
1117 -- Pre-reqs :
1118 -- Parameters :
1119 -- IN : p_po_distribution_id IN NUMBER
1120 -- p_start_gl_date IN DATE Optional
1121 -- p_end_gl_date IN DATE Optional
1122 --
1123 -- RETURN : Encumbrance Reversal Amount
1124 -- This amount is always a positive number
1125 -- Version : Initial version 1.0
1126 -- Notes : This function will be used in the Encumbrance Detail Report
1127 -- and active encumbrance summary screen.
1128 -- The function will be called only if accrue on receipt is set to Yes
1129 --
1130 -- For inventory destinations,
1131 -- sum(MTA.base_transaction_value) for deliveries
1132 -- against the PO distribution
1133 -- that have been transferred to GL
1134 -- For expense destinations,
1135 -- sum(RRS.accounted_dr/cr for E rows) for
1136 -- deliveries against the PO distribution
1137 --
1138 -- Encumbrance is not supported currently for Shop Floor
1139 -- For Time Zone changes
1140 -- Assume that date sent in is server timezone,
1141 -- and validate with TxnDate
1142 -- End of comments
1143 -------------------------------------------------------------------------------
1144
1145 FUNCTION Get_encumReversalAmt(
1146 p_po_distribution_id IN NUMBER,
1147 p_start_txn_date IN VARCHAR2,
1148 p_end_txn_date IN VARCHAR2
1149 )
1150
1151 RETURN NUMBER
1152 IS
1153 l_encReversalAmt NUMBER := 0;
1154 l_accrueOnRcptFlg VARCHAR(1);
1155 l_destTypeCode VARCHAR2(25);
1156 l_stmt_num NUMBER := 0;
1157
1158 BEGIN
1159 l_accrueOnRcptFlg := 'Y';
1160 -- Obtain Accrue on Receipt flag and destination type from POD
1161
1162 /* MOAC Uptake - Replaced po_distributions with po_distributions_all as the view
1163 would be obsoleted in R12 */
1164
1165 l_stmt_num := 10;
1166 select nvl(accrue_on_receipt_flag,'N'),
1167 destination_type_code
1168 into l_accrueOnRcptFlg,
1169 l_destTypeCode
1170 from po_distributions_all
1171 where po_distribution_id = p_po_distribution_id;
1172
1173 -- Check if accrue on receipt, else return 0
1174 l_stmt_num := 20;
1175 if (l_accrueOnRcptFlg <> 'Y') then
1176 return l_encReversalAmt;
1177 end if;
1178
1179 -- Obtain Encumbrance Reversal Amount
1180 l_stmt_num := 30;
1181
1182 if (l_destTypeCode = 'INVENTORY') then
1183 l_stmt_num := 40;
1184 select sum(nvl(mta.base_transaction_value, 0))
1185 into l_encReversalAmt
1186 from mtl_material_transactions mmt,
1187 mtl_transaction_accounts mta,
1188 rcv_transactions rt
1189 where rt.po_distribution_id = p_po_distribution_id
1190 and fnd_date.date_to_canonical(rt.transaction_date)
1191 between nvl(p_start_txn_date,fnd_date.date_to_canonical(rt.transaction_date))
1192 and nvl(p_end_txn_date,fnd_date.date_to_canonical(sysdate))
1193 and mmt.rcv_transaction_id = rt.transaction_id
1194 and mta.transaction_id = mmt.transaction_id
1195 and mta.accounting_line_type = 15
1196 and NVL(mta.gl_batch_id, 0) <> -1;
1197
1198 elsif (l_destTypeCode = 'EXPENSE') then
1199 l_stmt_num := 50;
1200 select sum(nvl(rrs.accounted_dr,0)-nvl(rrs.accounted_cr,0))
1201 into l_encReversalAmt
1202 from rcv_receiving_sub_ledger rrs,
1203 rcv_transactions rt
1204 where rt.po_distribution_id = p_po_distribution_id
1205 and fnd_date.date_to_canonical(rt.transaction_date)
1206 between nvl(p_start_txn_date,fnd_date.date_to_canonical(rt.transaction_date))
1207 and nvl(p_end_txn_date,fnd_date.date_to_canonical(sysdate))
1208 and rrs.rcv_transaction_id = rt.transaction_id
1209 and rrs.actual_flag = 'E';
1210 else
1211 l_stmt_num := 60;
1212 l_encReversalAmt := 0;
1213 end if;
1214
1215 l_stmt_num := 70;
1216 return (abs(l_encReversalAmt));
1217
1218 EXCEPTION
1219 when no_data_found then
1220 l_encReversalAmt := 0;
1221 return (l_encReversalAmt);
1222
1223 when others then
1224 l_encReversalAmt := 0;
1225 return (l_encReversalAmt);
1226 END;
1227
1228 END RCV_AccrualUtilities_GRP;
[Home] [Help]