1 PACKAGE BODY RCV_ACCRUAL_SV AS
2 /* $Header: RCVACCRB.pls 120.1 2005/09/21 02:35:56 bigoyal noship $*/
3
4
5 /* ======================================================================= *\
6 * PROCEDURE NAME: get_received_quantity() *
7 * *
8 * DESCRIPTION: *
9 * This function will accept a RECEIVE or MATCH transaction id and *
10 * return the quantity received in the PO's unit of measure. This is *
11 * mainly a function wrapper that calls the get_quantities API. *
12 * *
13 * USAGE: *
14 * This function is called from the period end accruals concurrent *
15 * program when the match option is set to match to receipt. *
16 * It is also used by the accounting events API to get received *
17 * quantity while accounting for retroactive price adjustments. *
18 \* ======================================================================= */
19
20 FUNCTION get_received_quantity (p_transaction_id IN NUMBER,
21 p_accrual_cutoff_date IN DATE) RETURN NUMBER
22 IS
23
24 X_progress VARCHAR2(3);
25
26 v_ordered_po_qty NUMBER;
27 v_received_po_qty NUMBER;
28 v_corrected_po_qty NUMBER;
29 v_delivered_po_qty NUMBER;
30 v_rtv_po_qty NUMBER;
31 v_billed_po_qty NUMBER;
32 v_accepted_po_qty NUMBER;
33 v_rejected_po_qty NUMBER;
34 v_ordered_txn_qty NUMBER;
35 v_received_txn_qty NUMBER;
36 v_corrected_txn_qty NUMBER;
37 v_delivered_txn_qty NUMBER;
38 v_rtv_txn_qty NUMBER;
39 v_billed_txn_qty NUMBER;
40 v_accepted_txn_qty NUMBER;
41 v_rejected_txn_qty NUMBER;
42 l_debug VARCHAR2(80);
43
44 BEGIN
45
46 X_progress := '001';
47
48 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
49
50 get_quantities(p_transaction_id,
51 v_ordered_po_qty,
52 v_received_po_qty,
53 v_corrected_po_qty,
54 v_delivered_po_qty,
55 v_rtv_po_qty,
56 v_billed_po_qty,
57 v_accepted_po_qty,
58 v_rejected_po_qty,
59 v_ordered_txn_qty,
60 v_received_txn_qty,
61 v_corrected_txn_qty,
62 v_delivered_txn_qty,
63 v_rtv_txn_qty,
64 v_billed_txn_qty,
65 v_accepted_txn_qty,
66 v_rejected_txn_qty,
67 p_accrual_cutoff_date);
68
69 X_progress := '002';
70
71 if (l_debug = 'Y') then
72 FND_FILE.PUT_LINE(FND_FILE.LOG,'Received Qty : '||TO_CHAR(v_received_po_qty));
73 FND_FILE.PUT_LINE(FND_FILE.LOG,'RTV Qty : ' || to_char(v_rtv_po_qty));
74 FND_FILE.PUT_LINE(FND_FILE.LOG,'Corrected Qty : ' || to_char(v_corrected_po_qty));
75 end if;
76 RETURN(v_received_po_qty-v_rtv_po_qty+v_corrected_po_qty);
77
78 EXCEPTION
79
80 WHEN OTHERS THEN
81 RAISE;
82
83 END get_received_quantity;
84
85 /* ======================================================================= *\
86 * PROCEDURE NAME: get_delivered_quantity() *
87 * *
88 * DESCRIPTION: *
89 * This function will accept a DELIVER transaction id and *
90 * return the quantity delivered in the PO's unit of measure. This is *
91 * mainly a function wrapper that calls the get_quantities API. *
92 * *
93 * USAGE: *
94 * This function is called from the accounting events API *
95 * while accounting for retroactive price changes. *
96 \* ======================================================================= */
97
98 FUNCTION get_delivered_quantity (p_transaction_id IN NUMBER,
99 p_accrual_cutoff_date IN DATE) RETURN NUMBER
100 IS
101
102 X_progress VARCHAR2(3);
103
104 v_ordered_po_qty NUMBER;
105 v_received_po_qty NUMBER;
106 v_corrected_po_qty NUMBER;
107 v_delivered_po_qty NUMBER;
108 v_rtv_po_qty NUMBER;
109 v_billed_po_qty NUMBER;
110 v_accepted_po_qty NUMBER;
111 v_rejected_po_qty NUMBER;
112 v_ordered_txn_qty NUMBER;
113 v_received_txn_qty NUMBER;
114 v_corrected_txn_qty NUMBER;
115 v_delivered_txn_qty NUMBER;
116 v_rtv_txn_qty NUMBER;
117 v_billed_txn_qty NUMBER;
118 v_accepted_txn_qty NUMBER;
119 v_rejected_txn_qty NUMBER;
120 l_debug VARCHAR2(80);
121
122 BEGIN
123
124 X_progress := '001';
125
126 l_debug := FND_PROFILE.VALUE('MRP_DEBUG');
127
128 get_quantities(p_transaction_id,
129 v_ordered_po_qty,
130 v_received_po_qty,
131 v_corrected_po_qty,
132 v_delivered_po_qty,
133 v_rtv_po_qty,
134 v_billed_po_qty,
135 v_accepted_po_qty,
136 v_rejected_po_qty,
137 v_ordered_txn_qty,
138 v_received_txn_qty,
139 v_corrected_txn_qty,
140 v_delivered_txn_qty,
141 v_rtv_txn_qty,
142 v_billed_txn_qty,
143 v_accepted_txn_qty,
144 v_rejected_txn_qty,
145 p_accrual_cutoff_date);
146
147 X_progress := '002';
148
149 if (l_debug = 'Y') then
150 FND_FILE.PUT_LINE(FND_FILE.LOG,'Delivered Qty : '||TO_CHAR(v_delivered_po_qty));
151 end if;
152 RETURN(v_delivered_po_qty);
153
154 EXCEPTION
155
156 WHEN OTHERS THEN
157 RAISE;
158 END get_delivered_quantity;
159
160
161
162 /* ======================================================================= *\
163 * PROCEDURE NAME: get_quantities() *
164 * *
165 * DESCRIPTION: *
166 * Given a transaction_id, this procedure will return the following *
167 * transaction quantities: *
168 * Ordered *
169 * Received *
170 * Corrected *
171 * Delivered *
172 * Returned To Vendor *
173 * Billed *
174 * Accepted *
175 * Rejected *
176 * The quantities are returned in both the PO and Transaction's unit *
177 * of measure. For period end accruals, we are only concerned with *
178 * the received transaction quantity in the PO's UOM. *
179 * This procedure contains the same logic as in the *
180 * RCV_INVOICE_MATCHING_SV package. However, we are using inventory's *
181 * UOM conversion routine here so that we do not violate the *
182 * associated WNDS pragma restriction associated with this procedure *
183 * *
184 * USAGE: *
185 * This function is called from the period end accruals concurrent *
186 * program when the match option is set to match to receipt. *
187 \* ======================================================================= */
188
189 PROCEDURE get_quantities( top_transaction_id IN NUMBER,
190 ordered_po_qty IN OUT NOCOPY NUMBER,
191 received_po_qty IN OUT NOCOPY NUMBER,
192 corrected_po_qty IN OUT NOCOPY NUMBER,
193 delivered_po_qty IN OUT NOCOPY NUMBER,
194 rtv_po_qty IN OUT NOCOPY NUMBER,
195 billed_po_qty IN OUT NOCOPY NUMBER,
196 accepted_po_qty IN OUT NOCOPY NUMBER,
197 rejected_po_qty IN OUT NOCOPY NUMBER,
198 ordered_txn_qty IN OUT NOCOPY NUMBER,
199 received_txn_qty IN OUT NOCOPY NUMBER,
200 corrected_txn_qty IN OUT NOCOPY NUMBER,
201 delivered_txn_qty IN OUT NOCOPY NUMBER,
202 rtv_txn_qty IN OUT NOCOPY NUMBER,
203 billed_txn_qty IN OUT NOCOPY NUMBER,
204 accepted_txn_qty IN OUT NOCOPY NUMBER,
205 rejected_txn_qty IN OUT NOCOPY NUMBER,
206 p_accrual_cutoff_date IN DATE) IS
207
208 X_progress VARCHAR2(3);
209
210 X_primary_uom VARCHAR2(25);
211 X_txn_uom VARCHAR2(25);
212 X_po_uom VARCHAR2(25);
213 X_pr_to_txn_rate NUMBER := 1;
214 X_pr_to_po_rate NUMBER := 1;
215 X_po_to_txn_rate NUMBER := 1;
216 X_item_id NUMBER := 0;
217 X_line_location_id NUMBER := 0;
218 X_received_quantity NUMBER := 0;
219 X_corrected_quantity NUMBER := 0;
220 X_delivered_quantity NUMBER := 0;
221 X_rtv_quantity NUMBER := 0;
222 X_accepted_quantity NUMBER := 0;
223 X_rejected_quantity NUMBER := 0;
224
225 v_primary_uom VARCHAR2(25);
226 v_po_uom VARCHAR2(25);
227 v_txn_uom VARCHAR2(25);
228 v_txn_id NUMBER := 0;
229 v_primary_quantity NUMBER := 0;
230 v_transaction_type VARCHAR2(25);
231 v_parent_id NUMBER := 0;
232 v_parent_type VARCHAR2(25);
233 v_shipment_line_id NUMBER := 0;
234 v_line_location_id NUMBER := 0;
235 /* Bug 2033579 Added two variables to store grand parent type and id */
236 grand_parent_type VARCHAR2(25);
237 grand_parent_id NUMBER := 0;
238
239
240
241 /* This cursor recursively query up all the children of the
242 ** top transaction (RECEIVE or MATCH)
243 */
244
245 /* Moved trunc() off the database columns */
246
247 CURSOR c_txn_history (c_transaction_id NUMBER) IS
248 SELECT
249 transaction_id,
250 primary_quantity,
251 primary_unit_of_measure,
252 unit_of_measure,
253 source_doc_unit_of_measure,
254 transaction_type,
255 shipment_line_id,
256 po_line_location_id,
257 parent_transaction_id
258 FROM
259 rcv_transactions
260 WHERE transaction_date <= trunc(p_accrual_cutoff_date)+1
261 START WITH transaction_id = c_transaction_id
262 CONNECT BY parent_transaction_id = PRIOR transaction_id;
263
264 BEGIN
265
266 OPEN c_txn_history(top_transaction_id);
267
268 X_progress := '001';
269
270 LOOP
271 FETCH c_txn_history INTO v_txn_id,
272 v_primary_quantity,
273 v_primary_uom,
274 v_txn_uom,
275 v_po_uom,
276 v_transaction_type,
277 v_shipment_line_id,
278 v_line_location_id,
279 v_parent_id;
280
281 EXIT WHEN c_txn_history%NOTFOUND;
282
283 X_progress := '002';
284
285 /* This was done for the receive transaction in the old code. Moving it
286 out and getting the UOM information based on the top transaction_id,
287 so it will work when called for a Receive and a Deliver transaction. */
291 SELECT
288 IF(v_txn_id = top_transaction_id) THEN
289 /* Find out the item_id for UOM conversion */
290
292 item_id
293 INTO
294 X_item_id
295 FROM
296 rcv_shipment_lines
297 WHERE
298 shipment_line_id = v_shipment_line_id;
299
300 X_line_location_id := v_line_location_id;
301 X_primary_uom := v_primary_uom;
302 X_txn_uom := v_txn_uom;
303 X_po_uom := v_po_uom;
304
305 END IF;
306
307 IF v_transaction_type = 'RECEIVE' OR v_transaction_type = 'MATCH' THEN
308
309 X_received_quantity := v_primary_quantity;
310
311 ELSIF v_transaction_type = 'RETURN TO VENDOR' THEN
312
313 /*Bug2033579 When Performing 'Return to vendor' on Accept or Reject Transaction
314 accepted quantity and Rejected quantity are not calculated correctly
315 */
316
317 SELECT
318 transaction_type
319 INTO
320 v_parent_type
321 FROM
322 rcv_transactions
323 WHERE
324 transaction_id = v_parent_id;
325
326 if v_parent_type = 'ACCEPT' THEN
327 X_accepted_quantity := X_accepted_quantity - v_primary_quantity;
328 end if;
329
330 if v_parent_type = 'REJECT' THEN
331 X_rejected_quantity := X_rejected_quantity - v_primary_quantity;
332 end if;
333
334 X_rtv_quantity := X_rtv_quantity + v_primary_quantity;
335
336 ELSIF v_transaction_type = 'DELIVER' THEN
337
338 X_delivered_quantity := X_delivered_quantity + v_primary_quantity;
339
340 ELSIF v_transaction_type = 'ACCEPT' THEN
341 /*Bug 2033579 Accept quantity is not determined correctly when received goods
342 are inspected more than once by pressing Inspection button and quantity is
343 accepted.
344 */
345 SELECT
346 transaction_type
347 INTO
348 v_parent_type
349 FROM
350 rcv_transactions
351 WHERE
352 transaction_id = v_parent_id;
353
354 if v_parent_type <> 'ACCEPT' THEN
355 X_accepted_quantity := X_accepted_quantity + v_primary_quantity;
356 end if;
357
358 if v_parent_type = 'REJECT' THEN
359 X_rejected_quantity := X_rejected_quantity - v_primary_quantity;
360 end if;
361
362 ELSIF v_transaction_type = 'REJECT' THEN
363 /*Bug 2033579 Reject quantity is not determined correctly when received goods
364 are inspected more than once by pressing Inspection button and quantity is
365 Rejected.
366 */
367 SELECT
368 transaction_type
369 INTO
370 v_parent_type
371 FROM
372 rcv_transactions
373 WHERE
374 transaction_id = v_parent_id;
375
376 if v_parent_type <> 'REJECT' then
377 X_rejected_quantity := X_rejected_quantity + v_primary_quantity;
378 end if;
379 if v_parent_type = 'ACCEPT' then
380 X_accepted_quantity := X_accepted_quantity - v_primary_quantity;
381 end if;
382
383 ELSIF v_transaction_type = 'RETURN TO RECEIVING' THEN
384
385 X_delivered_quantity := X_delivered_quantity - v_primary_quantity;
386
387 ELSIF v_transaction_type = 'CORRECT' THEN
388
389 /* The correction function is based on parent transaction type */
390
391 SELECT
392 transaction_type,parent_transaction_id
393 INTO
394 v_parent_type,grand_parent_id
395 FROM
396 rcv_transactions
397 WHERE
398 transaction_id = v_parent_id;
399
400 /*Bug 2272666 Added the no data found exception for the select statement itself
401 Earlier the no data found exception was handled for the entire function which
402 is removed because for this sql case it is not getting excuted sometimes.
403 If correction is done just after doing a receipt the grand parent id will be
404 -1 in that case the sql below won't fetch any records.
405 */
406 BEGIN
407
408 SELECT
409 transaction_type
410 INTO
411 grand_parent_type
412 FROM
413 rcv_transactions
414 WHERE
415 transaction_id = grand_parent_id;
416 EXCEPTION
417
418 WHEN NO_DATA_FOUND THEN
419 NULL;
420
421 END;
422
423 IF v_parent_type = 'RECEIVE' OR v_parent_type = 'MATCH' THEN
424
425 X_corrected_quantity := X_corrected_quantity +
426 v_primary_quantity;
427
428 ELSIF v_parent_type = 'RETURN TO VENDOR' THEN
429 /*Bug 2033579 When performing a correction on Return to Vendor and if its grand
430 parent is accept or Reject Transaction then the accepted or rejected quantity
431 will be adjusted accordingly.
432 */
433
434 if grand_parent_type = 'ACCEPT' THEN
435 X_accepted_quantity := X_accepted_quantity - v_primary_quantity;
436 end if;
437
438 if grand_parent_type = 'REJECT' THEN
439 X_rejected_quantity := X_rejected_quantity - v_primary_quantity;
440 end if;
441
442 X_rtv_quantity := X_rtv_quantity + v_primary_quantity;
443
444 ELSIF v_parent_type = 'DELIVER' THEN
445
446 X_delivered_quantity := X_delivered_quantity +
450 /*Bug 2033579 When performing a correction on Accept transaction and if its
447 v_primary_quantity;
448
449 ELSIF v_parent_type = 'ACCEPT' THEN
451 grand parent is Reject transaction then the Rejected quantity will be adjusted*/
452
453 if grand_parent_type = 'REJECT' THEN
454 X_rejected_quantity := X_rejected_quantity - v_primary_quantity;
455 end if;
456
457 X_accepted_quantity := X_accepted_quantity + v_primary_quantity;
458
459 ELSIF v_parent_type = 'REJECT' THEN
460 /*Bug 2033579 When performing a correction on Reject Transaction and if its
461 grand parent is Accept transaction then the accepted quantity will be
462 adjusted.
463 */
464 if grand_parent_type = 'ACCEPT' THEN
465 X_accepted_quantity := X_accepted_quantity - v_primary_quantity;
466 end if;
467
468 X_rejected_quantity := X_rejected_quantity + v_primary_quantity;
469
470 ELSIF v_parent_type = 'RETURN TO RECEIVING' THEN
471
472 X_delivered_quantity := X_delivered_quantity -
473 v_primary_quantity;
474
475 END IF;
476
477 END IF;
478
479 END LOOP;
480
481 CLOSE c_txn_history;
482
483 X_progress := '003';
484
485 /* Get the orderd, billed quantity from PO */
486
487 IF X_line_location_id IS NOT NULL THEN
488 SELECT
489 ps.quantity,
490 rt.quantity_billed -- This quantity is in transaction UOM
491 INTO
492 ordered_po_qty,
493 billed_txn_qty
494 FROM
495 po_line_locations ps,
496 rcv_transactions rt
497 WHERE
498 rt.transaction_id = top_transaction_id and
499 rt.po_line_location_id = ps.line_location_id;
500 ELSE
501 ordered_po_qty := 0;
502 billed_po_qty := 0;
503 END IF;
504
505 X_progress := '004';
506
507
508 /* Get UOM conversion rates using INV apis */
509
510 X_pr_to_po_rate := inv_convert.inv_um_convert(X_item_id, 10,
511 NULL, NULL, NULL,
512 X_primary_uom, X_po_uom);
513
514 X_pr_to_txn_rate := inv_convert.inv_um_convert(X_item_id, 10,
515 NULL, NULL, NULL,
516 X_primary_uom, X_txn_uom);
517
518 X_po_to_txn_rate := inv_convert.inv_um_convert(X_item_id, 10,
519 NULL, NULL, NULL,
520 X_po_uom, X_txn_uom);
521
522 X_progress := '005';
523
524
525 /* Calculate the quantity with uom info */
526
527 received_po_qty := X_pr_to_po_rate * X_received_quantity;
528 corrected_po_qty := X_pr_to_po_rate * X_corrected_quantity;
529 delivered_po_qty := X_pr_to_po_rate * X_delivered_quantity;
530 rtv_po_qty := X_pr_to_po_rate * X_rtv_quantity;
531 accepted_po_qty := X_pr_to_po_rate * X_accepted_quantity;
532 rejected_po_qty := X_pr_to_po_rate * X_rejected_quantity;
533 billed_po_qty := billed_po_qty / X_po_to_txn_rate; -- txn to po rate is
534 -- inverse of
535 -- X_po_to_txn_rate
536
537 ordered_txn_qty := X_po_to_txn_rate * ordered_po_qty;
538 received_txn_qty := X_pr_to_txn_rate * X_received_quantity;
539 corrected_txn_qty := X_pr_to_txn_rate * X_corrected_quantity;
540 delivered_txn_qty := X_pr_to_txn_rate * X_delivered_quantity;
541 rtv_txn_qty := X_pr_to_txn_rate * X_rtv_quantity;
542 accepted_txn_qty := X_pr_to_txn_rate * X_accepted_quantity;
543 rejected_txn_qty := X_pr_to_txn_rate * X_rejected_quantity;
544
545 X_progress := '006';
546
547 EXCEPTION
548
549 WHEN OTHERS THEN
550 RAISE;
551
552 END get_quantities;
553
554
555 END RCV_ACCRUAL_SV;
[Home] [Help]