[Home] [Help]
PACKAGE BODY: APPS.CST_PERENDACCRUALS_PVT
Source
1 PACKAGE BODY CST_PerEndAccruals_PVT AS
2 /* $Header: CSTVPEAB.pls 120.21.12020000.3 2012/07/27 10:45:41 pbasrani ship $ */
3
4 -------------------------------------------------------------------------------
5 -- Period end accrual algorithm:
6 -------------------------------------------------------------------------------
7 -- For each eligible distribution, repeat steps 1 to 11
8 -- 1. Shipment_qty : quantity ordered - quantity cancelled
9 -- 2. Shipment_net_qty_received : Net quantity received against the shipment
10 -- 3. Shipment_net_qty_delivered: Net quantity delivered against all the
11 -- distributions for the shipment
12 -- 4. Shipment_remaining_qty : Net quantity to be delivered against all
13 -- the distributions in the shipment
14 -- = p_shipment_qty - l_shipment_net_qty_delivered
15 -- 5. Distribution_qty : Quantity ordered - quantity cancelled
16 -- 6. Dist_net_qty_delivered : Net quantity delivered against the distribution
17 -- 7. Dist_remaining_qty : Net quantity to be delivered against the
18 -- distribution
19 -- 8. Unit_price : po_price + nr_tax
20 --
21 -- 9. If (Match to PO)
22 -- a. qty_in_receiving : Remaining quantity to deliver
23 -- = shipment_net_qty_received - shipment_net_qty_delivered
24 -- b. Prorate this remaining quantity (which has been received but not
25 -- been delivered) among the distribution
26 -- If distribution_remaining_qty <= 0
27 -- allocated_qty = 0
28 -- Else
29 -- dist_remaining_qty
30 -- allocated_qty = qty_in_receiving * ----------------------
31 -- shipment_remaining_qty
32 -- c. Prorate the over receipt quantity based on quantity ordered
33 -- d. Quantity_received = dist_net_qty_delivered + allocated_qty
34 -- + over_receipt
35 -- e. Quantity invoiced = Net qty invoiced against the Distribution
36 -- f. Accrual_amount
37 -- If qty_received <= quantity_invoiced
38 -- accrual_amount = 0
39 -- else
40 -- accrual_amount = (qty_received - quantity_invoiced) * unit_price
41 --
42 -- 10. If (Match to Receipt)
43 -- For each RECEIVE or MATCH tarnsaction created against the Shipment,
44 -- repeat following :
45 -- a. Qty_received = Net quantity received against the receipt.
46 -- b. Qty_invoiced = Net quantity invoiced against the receipt *
47 -- distribution_qty
48 -- -------------------
49 -- shipment_qty
50 -- c. Qty_delivered = Net quantity delivered against the distribution in
51 -- the receive transaction.
52 -- d. Qty_in_receiving (Remaining quantity to deliver)
53 -- = qty_received - qty_delivered
54 -- e. Prorate this remaining quantity (which has been received but not yet
55 -- delivered) among the distribution.
56 -- If distribution_remaining_qty <= 0
57 -- allocated_qty = 0
58 -- Else
59 -- distribution_remaining_qty
60 -- allocated_qty = --------------------------- * qty_in_receiving
61 -- shipment_remaining_qty
62 -- f. Prorate the over receipt quantity based on quantity ordered
63 -- g. Quantity_received = dist_net_qty_delivered + allocated_qty
64 -- + over_receipt
65 -- h. Accrual_amount
66 -- If qty_received <= quantity_invoiced
67 -- accrual_amount = 0
68 -- Else
69 -- accrual_amount = (qty_received - quantity_invoiced) * unit_price
70 --
71 -- 11. Create accrual entries in CST_PER_END_ACCRUALS_TEMP
72 -------------------------------------------------------------------------------
73
74 G_PKG_NAME CONSTANT VARCHAR2(30):='CST_PerEndAccruals_PVT';
75 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
76
77
78
79 -----------------------------------------------------------------------------
80 -- PROCEDURE : Create_Per_End_Accruals
81 -- DESCRIPTION : Starting point for Period End Accrual program.
82 -- The API creates period end accrual entries in the
83 -- temporary table CST_PER_END_ACCRUALS_TEMP.
84 -----------------------------------------------------------------------------
85 PROCEDURE Create_PerEndAccruals
86 (
87 p_api_version IN NUMBER,
88 p_init_msg_list IN VARCHAR2,
89 p_commit IN VARCHAR2,
90 p_validation_level IN NUMBER,
91 x_return_status OUT NOCOPY VARCHAR2,
92 x_msg_count OUT NOCOPY NUMBER,
93 x_msg_data OUT NOCOPY VARCHAR2,
94
95 p_min_accrual_amount IN NUMBER,
96 p_vendor_id IN NUMBER,
97 p_vendor_from IN VARCHAR2,
98 p_vendor_to IN VARCHAR2,
99 p_category_id IN NUMBER,
100 p_category_from IN VARCHAR2,
101 p_category_to IN VARCHAR2,
102 p_end_date IN DATE,
103 p_accrued_receipt IN VARCHAR2,
104 p_online_accruals IN VARCHAR2,
105 p_closed_pos IN VARCHAR2,
106 p_calling_api IN NUMBER
107 )
108
109 IS
110 l_api_name CONSTANT VARCHAR2(30) :='Create_PerEndAccruals';
111 l_api_version CONSTANT NUMBER := 1.0;
112 l_return_status VARCHAR2(1);
113
114 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
115 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
116
117 /* Log Severities*/
118 /* 6- UNEXPECTED */
119 /* 5- ERROR */
120 /* 4- EXCEPTION */
121 /* 3- EVENT */
122 /* 2- PROCEDURE */
123 /* 1- STATEMENT */
124
125 /* In general, we should use the following:
126 G_LOG_LEVEL CONSTANT NUMBER := FND_LOG.G_CURRENT_RUNTIME_LEVEL;
127 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
128 l_errorLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_ERROR >= G_LOG_LEVEL);
129 l_exceptionLog CONSTANT BOOLEAN := l_errorLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
130 l_eventLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_EVENT >= G_LOG_LEVEL);
131 l_pLog CONSTANT BOOLEAN := l_eventLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
132 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
133 */
134
135 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
136 l_exceptionLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
137 l_pLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
138 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
139
140 l_accrual_rec CST_PER_END_ACCRUALS_TEMP%ROWTYPE;
141 l_end_date DATE;
142 l_stmt_num NUMBER;
143 l_msg_data VARCHAR2(240);
144
145 ------------------------------------------------------------------------
146 -- Distribution Level Cursor
147 -- The following conditions must be satisfied:
148 -- 1. Shipment must be on a STANDARD, BLANKET, or PLANNED PO header.
149 -- 2. If a vendor is specified, then the purchase order must be for
150 -- that vendor.
151 -- 3. If an item category has been specified, then the PO line must be
152 -- for that item category.
153 -- 4. The shipment type must not be of PREPAYMENT type
154 -- 5. Accrued flag should be Y or N depending upon the value of Accrued
155 -- Receipt parameter.
156 -- 6. For Period End Accruals, the destination type for the distribution
157 -- should be EXPENSE and should be either not closed or closed after
158 -- the cut-off date.
159 -- 8. For online accruals, filter out the shipments where quantity_billed
160 -- is equal to quantity_received. This will help in improving the
161 -- performance of the process.
162 -- 9. If 'Include Closed POs' is yes, include all the closed POs. Else
163 -- exclude the POs which are closed before the cut-oof date.
164 -- This parameter is used by Uninvoiced Receipts Report.
165 -- 10.Must have a receipt against it with a transaction date less than
166 -- the cutoff date.
167 -------------------------------------------------------------------------
168 CURSOR l_distribution_csr IS
169 SELECT /*+ LEADING (POLL) PUSH_SUBQ */
170 pod.po_distribution_id po_distribution_id,
171 poll.line_location_id line_location_id,
172 DECODE(poll.matching_basis,
173 'AMOUNT', 1,
174 0) service_flag,
175 DECODE (poll.matching_basis,
176 'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
177 pod.quantity_ordered - NVL(pod.quantity_cancelled, 0))
178 distribution_quantity,
179 DECODE (poll.matching_basis,
180 'AMOUNT', pod.amount_ordered,
181 pod.quantity_ordered) quantity_ordered,
182 DECODE(poll.matching_basis,
183 'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
184 poll.quantity - NVL(poll.quantity_cancelled,0))
185 shipment_quantity,
186 DECODE(poll.matching_basis,
187 'AMOUNT', 1,
188 NVL(poll.price_override, pol.unit_price))
189 po_price,
190 NVL(poll.match_option,'P') match_option,
191 pol.category_id category_id,
192 poh.currency_code currency_code,
193 NVL(NVL(pod.rate,poh.rate),1) currency_rate,
194 poh.rate_type curr_conv_type,
195 pod.rate_date currency_conv_date
196 FROM po_distributions pod, -- Using single org view PO_DISTRIBUTIONS to support MOAC
197 po_line_locations_all poll,
198 po_lines_all pol,
199 po_headers_all poh,
200 po_vendors pov,
201 mtl_categories_kfv mca,
202 mtl_default_sets_view mds
203 WHERE pol.po_header_id = poh.po_header_id
204 AND poh.pcard_id IS NULL
205 AND poll.po_line_id = pol.po_line_id
206 AND pod.line_location_id = poll.line_location_id
207 AND poh.type_lookup_code IN ('STANDARD', 'BLANKET', 'PLANNED')
208 AND poh.vendor_id = pov.vendor_id
209 AND ((p_vendor_id IS NOT NULL AND pov.vendor_id = p_vendor_id)
210 OR
211 p_vendor_id IS NULL)
212 AND ((p_vendor_from IS NOT NULL AND pov.vendor_name >= p_vendor_from)
213 OR
214 p_vendor_from IS NULL)
215 AND ((p_vendor_to IS NOT NULL AND pov.vendor_name <= p_vendor_to)
216 OR
217 p_vendor_to IS NULL)
218 AND pol.category_id = mca.category_id
219 AND ((p_category_id IS NOT NULL AND mca.category_id = p_category_id)
220 OR
221 p_category_id IS NULL)
222 AND (p_category_from IS NULL
223 OR
224 (mca.concatenated_segments >= p_category_from AND p_category_from IS NOT NULL))
225 AND (p_category_to IS NULL
226 OR
227 (mca.concatenated_segments <= p_category_to AND p_category_to IS NOT NULL))
228 AND mds.structure_id = mca.structure_id
229 AND mds.functional_area_id = 2
230 AND poll.shipment_type <> 'PREPAYMENT'
231 AND (p_closed_pos = 'Y'
232 OR
233 (poll.shipment_closed_date IS NULL OR poll.shipment_closed_date > l_end_date))
234 AND ((NVL(poll.accrue_on_receipt_flag,'N') = 'N'
235 AND pod.destination_type_code = 'EXPENSE'
236 AND NVL(pod.accrued_flag, 'N') = NVL(p_accrued_receipt, 'N')
237 )
238 OR
239 (p_online_accruals = 'Y'
240 AND poll.accrue_on_receipt_flag = 'Y'
241 AND (poll.shipment_closed_date is NULL or (poll.shipment_closed_date > l_end_date) OR
242 DECODE(poll.matching_basis,
243 'AMOUNT', poll.amount_billed - poll.amount_received,
244 poll.quantity_billed - poll.quantity_received) <> 0)
245 ))
246 --{BUG#6366287: Only accrue if Ordered Quantity - Cancelled Quantity is > 0
247 AND DECODE (poll.matching_basis,
248 'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
249 pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) > 0
250 AND DECODE(poll.matching_basis,
251 'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
252 poll.quantity - NVL(poll.quantity_cancelled,0)) > 0
253 --}
254 AND EXISTS
255 (SELECT /*+ PUSH_SUBQ NO_UNNEST */
256 'Get a receipt/match for this shipment'
257 FROM rcv_transactions rvt
258 WHERE rvt.po_line_location_id = poll.line_location_id
259 AND rvt.transaction_type IN ('RECEIVE','MATCH')
260 AND rvt.transaction_date <= l_end_date
261 )
262 ORDER by poll.line_location_id; /*Order by Clause added for bug 8675502*/
263
264
265 --{Specialization of the cursor
266 --
267 -- No vendor provided and no category provided
268 --
269 CURSOR c_no_vendor_no_category
270 IS
271 SELECT /*+ LEADING (POLL) PUSH_SUBQ */
272 pod.po_distribution_id po_distribution_id,
273 poll.line_location_id line_location_id,
274 DECODE(poll.matching_basis,
275 'AMOUNT', 1,
276 0) service_flag,
277 DECODE (poll.matching_basis,
278 'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
279 pod.quantity_ordered - NVL(pod.quantity_cancelled, 0))
280 distribution_quantity,
281 DECODE (poll.matching_basis,
282 'AMOUNT', pod.amount_ordered,
283 pod.quantity_ordered) quantity_ordered,
284 DECODE(poll.matching_basis,
285 'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
286 poll.quantity - NVL(poll.quantity_cancelled,0))
287 shipment_quantity,
288 DECODE(poll.matching_basis,
289 'AMOUNT', 1,
290 NVL(poll.price_override, pol.unit_price))
291 po_price,
292 NVL(poll.match_option,'P') match_option,
293 pol.category_id category_id,
294 poh.currency_code currency_code,
295 NVL(NVL(pod.rate,poh.rate),1) currency_rate,
296 poh.rate_type curr_conv_type,
297 pod.rate_date currency_conv_date
298 FROM po_distributions pod -- Using single org view PO_DISTRIBUTIONS to support MOAC
299 ,po_line_locations_all poll
300 ,po_lines_all pol
301 ,po_headers_all poh
302 WHERE pol.po_header_id = poh.po_header_id
303 AND poh.pcard_id IS NULL
304 AND poll.po_line_id = pol.po_line_id
305 AND pod.line_location_id = poll.line_location_id
306 AND poh.type_lookup_code IN ('STANDARD', 'BLANKET', 'PLANNED')
307 AND poll.shipment_type <> 'PREPAYMENT'
308 AND (p_closed_pos = 'Y'
309 OR
310 (poll.shipment_closed_date IS NULL OR poll.shipment_closed_date > l_end_date))
311 AND ((NVL(poll.accrue_on_receipt_flag,'N') = 'N'
312 AND pod.destination_type_code = 'EXPENSE'
313 AND NVL(pod.accrued_flag, 'N') = NVL(p_accrued_receipt, 'N')
314 )
315 OR
316 (p_online_accruals = 'Y'
317 AND poll.accrue_on_receipt_flag = 'Y'
318 AND (poll.shipment_closed_date is NULL or (poll.shipment_closed_date > l_end_date) OR
319 DECODE(poll.matching_basis,
320 'AMOUNT', poll.amount_billed - poll.amount_received,
321 poll.quantity_billed - poll.quantity_received) <> 0)
322 ))
323 AND DECODE (poll.matching_basis,
324 'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
325 pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) > 0
326 AND DECODE(poll.matching_basis,
327 'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
328 poll.quantity - NVL(poll.quantity_cancelled,0)) > 0
329 AND EXISTS
330 (SELECT /*+ PUSH_SUBQ NO_UNNEST */
331 'Get a receipt/match for this shipment'
332 FROM rcv_transactions rvt
333 WHERE rvt.po_line_location_id = poll.line_location_id
334 AND rvt.transaction_type IN ('RECEIVE','MATCH')
335 AND rvt.transaction_date <= l_end_date
336 )
337 ORDER by poll.line_location_id; /*Order by Clause added for bug 8675502*/
338
339
340
341
342
343
344 --
345 -- Vendor ID provided and no category provided
346 --
347 CURSOR c_vendor_id_no_category
348 IS
349 SELECT /*+ LEADING (POLL) PUSH_SUBQ */
350 pod.po_distribution_id po_distribution_id,
351 poll.line_location_id line_location_id,
352 DECODE(poll.matching_basis,
353 'AMOUNT', 1,
354 0) service_flag,
355 DECODE (poll.matching_basis,
356 'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
357 pod.quantity_ordered - NVL(pod.quantity_cancelled, 0))
358 distribution_quantity,
359 DECODE (poll.matching_basis,
360 'AMOUNT', pod.amount_ordered,
361 pod.quantity_ordered) quantity_ordered,
362 DECODE(poll.matching_basis,
363 'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
364 poll.quantity - NVL(poll.quantity_cancelled,0))
365 shipment_quantity,
366 DECODE(poll.matching_basis,
367 'AMOUNT', 1,
368 NVL(poll.price_override, pol.unit_price))
369 po_price,
370 NVL(poll.match_option,'P') match_option,
371 pol.category_id category_id,
372 poh.currency_code currency_code,
373 NVL(NVL(pod.rate,poh.rate),1) currency_rate,
374 poh.rate_type curr_conv_type,
375 pod.rate_date currency_conv_date
376 FROM po_distributions pod -- Using single org view PO_DISTRIBUTIONS to support MOAC
377 ,po_line_locations_all poll
378 ,po_lines_all pol
379 ,po_headers_all poh
380 ,po_vendors pov
381 WHERE pol.po_header_id = poh.po_header_id
382 AND poh.pcard_id IS NULL
383 AND poll.po_line_id = pol.po_line_id
384 AND pod.line_location_id = poll.line_location_id
385 AND poh.type_lookup_code IN ('STANDARD', 'BLANKET', 'PLANNED')
386 AND poh.vendor_id = pov.vendor_id
387 AND pov.vendor_id = p_vendor_id
388 AND poll.shipment_type <> 'PREPAYMENT'
389 AND (p_closed_pos = 'Y'
390 OR
391 (poll.shipment_closed_date IS NULL OR poll.shipment_closed_date > l_end_date))
392 AND ((NVL(poll.accrue_on_receipt_flag,'N') = 'N'
393 AND pod.destination_type_code = 'EXPENSE'
394 AND NVL(pod.accrued_flag, 'N') = NVL(p_accrued_receipt, 'N')
395 )
396 OR
397 (p_online_accruals = 'Y'
398 AND poll.accrue_on_receipt_flag = 'Y'
399 AND (poll.shipment_closed_date is NULL or (poll.shipment_closed_date > l_end_date) OR
400 DECODE(poll.matching_basis,
401 'AMOUNT', poll.amount_billed - poll.amount_received,
402 poll.quantity_billed - poll.quantity_received) <> 0)
403 ))
404 AND DECODE (poll.matching_basis,
405 'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
406 pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) > 0
407 AND DECODE(poll.matching_basis,
408 'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
409 poll.quantity - NVL(poll.quantity_cancelled,0)) > 0
410 AND EXISTS
411 (SELECT /*+ PUSH_SUBQ NO_UNNEST */
412 'Get a receipt/match for this shipment'
413 FROM rcv_transactions rvt
414 WHERE rvt.po_line_location_id = poll.line_location_id
415 AND rvt.transaction_type IN ('RECEIVE','MATCH')
416 AND rvt.transaction_date <= l_end_date
417 )
418 ORDER by poll.line_location_id; /*Order by Clause added for bug 8675502*/
419
420
421
422
423 --
424 -- Vendor range provided and no category provided
425 --
426 CURSOR c_vendor_range_no_category
427 IS
428 SELECT /*+ LEADING (POLL) PUSH_SUBQ */
429 pod.po_distribution_id po_distribution_id,
430 poll.line_location_id line_location_id,
431 DECODE(poll.matching_basis,
432 'AMOUNT', 1,
433 0) service_flag,
434 DECODE (poll.matching_basis,
435 'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
436 pod.quantity_ordered - NVL(pod.quantity_cancelled, 0))
437 distribution_quantity,
438 DECODE (poll.matching_basis,
439 'AMOUNT', pod.amount_ordered,
440 pod.quantity_ordered) quantity_ordered,
441 DECODE(poll.matching_basis,
442 'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
443 poll.quantity - NVL(poll.quantity_cancelled,0))
444 shipment_quantity,
445 DECODE(poll.matching_basis,
446 'AMOUNT', 1,
447 NVL(poll.price_override, pol.unit_price))
448 po_price,
449 NVL(poll.match_option,'P') match_option,
450 pol.category_id category_id,
451 poh.currency_code currency_code,
452 NVL(NVL(pod.rate,poh.rate),1) currency_rate,
453 poh.rate_type curr_conv_type,
454 pod.rate_date currency_conv_date
455 FROM po_distributions pod -- Using single org view PO_DISTRIBUTIONS to support MOAC
456 ,po_line_locations_all poll
457 ,po_lines_all pol
458 ,po_headers_all poh
459 ,po_vendors pov
460 WHERE pol.po_header_id = poh.po_header_id
461 AND poh.pcard_id IS NULL
462 AND poll.po_line_id = pol.po_line_id
463 AND pod.line_location_id = poll.line_location_id
464 AND poh.type_lookup_code IN ('STANDARD', 'BLANKET', 'PLANNED')
465 AND poh.vendor_id = pov.vendor_id
466 AND ((p_vendor_from IS NOT NULL AND pov.vendor_name >= p_vendor_from)
467 OR
468 p_vendor_from IS NULL)
469 AND ((p_vendor_to IS NOT NULL AND pov.vendor_name <= p_vendor_to)
470 OR
471 p_vendor_to IS NULL)
472 AND poll.shipment_type <> 'PREPAYMENT'
473 AND (p_closed_pos = 'Y'
474 OR
475 (poll.shipment_closed_date IS NULL OR poll.shipment_closed_date > l_end_date))
476 AND ((NVL(poll.accrue_on_receipt_flag,'N') = 'N'
477 AND pod.destination_type_code = 'EXPENSE'
478 AND NVL(pod.accrued_flag, 'N') = NVL(p_accrued_receipt, 'N')
479 )
480 OR
481 (p_online_accruals = 'Y'
482 AND poll.accrue_on_receipt_flag = 'Y'
483 AND (poll.shipment_closed_date is NULL or (poll.shipment_closed_date > l_end_date) OR
484 DECODE(poll.matching_basis,
485 'AMOUNT', poll.amount_billed - poll.amount_received,
486 poll.quantity_billed - poll.quantity_received) <> 0)
487 ))
488 AND DECODE (poll.matching_basis,
489 'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
490 pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) > 0
491 AND DECODE(poll.matching_basis,
492 'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
493 poll.quantity - NVL(poll.quantity_cancelled,0)) > 0
494 AND EXISTS
495 (SELECT /*+ PUSH_SUBQ NO_UNNEST */
496 'Get a receipt/match for this shipment'
497 FROM rcv_transactions rvt
498 WHERE rvt.po_line_location_id = poll.line_location_id
499 AND rvt.transaction_type IN ('RECEIVE','MATCH')
500 AND rvt.transaction_date <= l_end_date
501 )
502 ORDER by poll.line_location_id; /*Order by Clause added for bug 8675502*/
503
504
505
506 --
507 -- No Vendor provided and category ID provided
508 --
509 CURSOR c_no_vendor_category_id
510 IS
511 SELECT /*+ LEADING (POLL) PUSH_SUBQ */
512 pod.po_distribution_id po_distribution_id,
513 poll.line_location_id line_location_id,
514 DECODE(poll.matching_basis,
515 'AMOUNT', 1,
516 0) service_flag,
517 DECODE (poll.matching_basis,
518 'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
519 pod.quantity_ordered - NVL(pod.quantity_cancelled, 0))
520 distribution_quantity,
521 DECODE (poll.matching_basis,
522 'AMOUNT', pod.amount_ordered,
523 pod.quantity_ordered) quantity_ordered,
524 DECODE(poll.matching_basis,
525 'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
526 poll.quantity - NVL(poll.quantity_cancelled,0))
527 shipment_quantity,
528 DECODE(poll.matching_basis,
529 'AMOUNT', 1,
530 NVL(poll.price_override, pol.unit_price))
531 po_price,
532 NVL(poll.match_option,'P') match_option,
533 pol.category_id category_id,
534 poh.currency_code currency_code,
535 NVL(NVL(pod.rate,poh.rate),1) currency_rate,
536 poh.rate_type curr_conv_type,
537 pod.rate_date currency_conv_date
538 FROM po_distributions pod -- Using single org view PO_DISTRIBUTIONS to support MOAC
539 ,po_line_locations_all poll
540 ,po_lines_all pol
541 ,po_headers_all poh
542 ,mtl_categories_kfv mca
543 ,mtl_default_sets_view mds
544 WHERE pol.po_header_id = poh.po_header_id
545 AND poh.pcard_id IS NULL
546 AND poll.po_line_id = pol.po_line_id
547 AND pod.line_location_id = poll.line_location_id
548 AND poh.type_lookup_code IN ('STANDARD', 'BLANKET', 'PLANNED')
549 AND pol.category_id = mca.category_id
550 AND mca.category_id = p_category_id
551 AND mds.structure_id = mca.structure_id
552 AND mds.functional_area_id = 2
553 AND poll.shipment_type <> 'PREPAYMENT'
554 AND (p_closed_pos = 'Y'
555 OR
556 (poll.shipment_closed_date IS NULL OR poll.shipment_closed_date > l_end_date))
557 AND ((NVL(poll.accrue_on_receipt_flag,'N') = 'N'
558 AND pod.destination_type_code = 'EXPENSE'
559 AND NVL(pod.accrued_flag, 'N') = NVL(p_accrued_receipt, 'N')
560 )
561 OR
562 (p_online_accruals = 'Y'
563 AND poll.accrue_on_receipt_flag = 'Y'
564 AND (poll.shipment_closed_date is NULL or (poll.shipment_closed_date > l_end_date) OR
565 DECODE(poll.matching_basis,
566 'AMOUNT', poll.amount_billed - poll.amount_received,
567 poll.quantity_billed - poll.quantity_received) <> 0)
568 ))
569 AND DECODE (poll.matching_basis,
570 'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
571 pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) > 0
572 AND DECODE(poll.matching_basis,
573 'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
574 poll.quantity - NVL(poll.quantity_cancelled,0)) > 0
575 AND EXISTS
576 (SELECT /*+ PUSH_SUBQ NO_UNNEST */
577 'Get a receipt/match for this shipment'
578 FROM rcv_transactions rvt
579 WHERE rvt.po_line_location_id = poll.line_location_id
580 AND rvt.transaction_type IN ('RECEIVE','MATCH')
581 AND rvt.transaction_date <= l_end_date
582 )
583 ORDER by poll.line_location_id; /*Order by Clause added for bug 8675502*/
584
585
586
587
588 --
589 -- No Vendor provided and category range provided
590 --
591 CURSOR c_no_vendor_category_range
592 IS
593 SELECT /*+ LEADING (POLL) PUSH_SUBQ */
594 pod.po_distribution_id po_distribution_id,
595 poll.line_location_id line_location_id,
596 DECODE(poll.matching_basis,
597 'AMOUNT', 1,
598 0) service_flag,
599 DECODE (poll.matching_basis,
600 'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
601 pod.quantity_ordered - NVL(pod.quantity_cancelled, 0))
602 distribution_quantity,
603 DECODE (poll.matching_basis,
604 'AMOUNT', pod.amount_ordered,
605 pod.quantity_ordered) quantity_ordered,
606 DECODE(poll.matching_basis,
607 'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
608 poll.quantity - NVL(poll.quantity_cancelled,0))
609 shipment_quantity,
610 DECODE(poll.matching_basis,
611 'AMOUNT', 1,
612 NVL(poll.price_override, pol.unit_price))
613 po_price,
614 NVL(poll.match_option,'P') match_option,
615 pol.category_id category_id,
616 poh.currency_code currency_code,
617 NVL(NVL(pod.rate,poh.rate),1) currency_rate,
618 poh.rate_type curr_conv_type,
619 pod.rate_date currency_conv_date
620 FROM po_distributions pod -- Using single org view PO_DISTRIBUTIONS to support MOAC
621 ,po_line_locations_all poll
622 ,po_lines_all pol
623 ,po_headers_all poh
624 ,mtl_categories_kfv mca
625 ,mtl_default_sets_view mds
626 WHERE pol.po_header_id = poh.po_header_id
627 AND poh.pcard_id IS NULL
628 AND poll.po_line_id = pol.po_line_id
629 AND pod.line_location_id = poll.line_location_id
630 AND poh.type_lookup_code IN ('STANDARD', 'BLANKET', 'PLANNED')
631 AND pol.category_id = mca.category_id
632 AND (p_category_from IS NULL
633 OR
634 (mca.concatenated_segments >= p_category_from AND p_category_from IS NOT NULL))
635 AND (p_category_to IS NULL
636 OR
637 (mca.concatenated_segments <= p_category_to AND p_category_to IS NOT NULL))
638 AND mds.structure_id = mca.structure_id
639 AND mds.functional_area_id = 2
640 AND poll.shipment_type <> 'PREPAYMENT'
641 AND (p_closed_pos = 'Y'
642 OR
643 (poll.shipment_closed_date IS NULL OR poll.shipment_closed_date > l_end_date))
644 AND ((NVL(poll.accrue_on_receipt_flag,'N') = 'N'
645 AND pod.destination_type_code = 'EXPENSE'
646 AND NVL(pod.accrued_flag, 'N') = NVL(p_accrued_receipt, 'N')
647 )
648 OR
649 (p_online_accruals = 'Y'
650 AND poll.accrue_on_receipt_flag = 'Y'
651 AND (poll.shipment_closed_date is NULL or (poll.shipment_closed_date > l_end_date) OR
652 DECODE(poll.matching_basis,
653 'AMOUNT', poll.amount_billed - poll.amount_received,
654 poll.quantity_billed - poll.quantity_received) <> 0)
655 ))
656 AND DECODE (poll.matching_basis,
657 'AMOUNT', pod.amount_ordered - NVL(pod.amount_cancelled, 0),
658 pod.quantity_ordered - NVL(pod.quantity_cancelled, 0)) > 0
659 AND DECODE(poll.matching_basis,
660 'AMOUNT', poll.amount - NVL(poll.amount_cancelled, 0),
661 poll.quantity - NVL(poll.quantity_cancelled,0)) > 0
662 AND EXISTS
663 (SELECT /*+ PUSH_SUBQ NO_UNNEST */
664 'Get a receipt/match for this shipment'
665 FROM rcv_transactions rvt
666 WHERE rvt.po_line_location_id = poll.line_location_id
667 AND rvt.transaction_type IN ('RECEIVE','MATCH')
668 AND rvt.transaction_date <= l_end_date
669 )
670 ORDER by poll.line_location_id ; /* Order by Clause added for bug 8675502*/
671
672
673
674
675
676
677
678
679
680
681
682
683 -------------------------------------------------------------------------
684 -- PL/SQL tables of accrual info to be inserted in
685 -- CST_PER_END_ACCRUALS_TEMP table
686 -------------------------------------------------------------------------
687 l_acr_dist_id_tbl ACR_DIST_ID_TBL_TYPE;
688 l_acr_shipment_id_tbl ACR_SHIPMENT_ID_TBL_TYPE;
689 l_acr_category_id_tbl ACR_CATEGORY_ID_TBL_TYPE;
690 l_acr_match_option_tbl ACR_MATCH_OPTION_TBL_TYPE;
691 l_acr_qty_received_tbl ACR_QTY_RECEIVED_TBL_TYPE;
692 l_acr_qty_billed_tbl ACR_QTY_BILLED_TBL_TYPE;
693 l_acr_accrual_qty_tbl ACR_ACCRUAL_QTY_TBL_TYPE;
694 l_acr_encum_qty_tbl ACR_ENCUM_QTY_TBL_TYPE;
695 l_acr_unit_price_tbl ACR_UNIT_PRICE_TBL_TYPE;
696 l_acr_accrual_amount_tbl ACR_ACCRUAL_AMOUNT_TBL_TYPE;
697 l_acr_encum_amount_tbl ACR_ENCUM_AMOUNT_TBL_TYPE;
698 l_acr_cur_code_tbl ACR_CUR_CODE_TBL_TYPE;
699 l_acr_cur_conv_type_tbl ACR_CUR_CONV_TYPE_TBL_TYPE;
700 l_acr_cur_conv_rate_tbl ACR_CUR_CONV_RATE_TBL_TYPE;
701 l_acr_cur_conv_date_tbl ACR_CUR_CONV_DATE_TBL_TYPE;
702 l_ctr NUMBER;
703 l_use VARCHAR2(30);
704 BEGIN
705 -- Standard Start of API savepoint
706 SAVEPOINT Create_PerEndAccruals_PVT;
707
708 l_stmt_num := 0;
709
710 -- Procedure level log message for Entry point
711 IF (l_pLog) THEN
712 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_module,'Create_PerEndAccruals << ');
713 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_module,' p_min_accrual_amount = '|| p_min_accrual_amount);
714 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_module,' p_vendor_id = '|| p_vendor_id);
715 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_module,' p_vendor_to = '|| p_vendor_to);
716 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_module,' p_category_id = '|| p_category_id);
717 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_module,' p_category_from = '|| p_category_from);
718 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_module,' p_category_to = '|| p_category_to);
719 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_module,' p_end_date = '|| p_end_date);
720 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_module,' p_accrued_receipt = '|| p_accrued_receipt);
721 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_module,' p_online_accruals = '|| p_online_accruals);
722 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_module,' p_calling_api = '|| p_calling_api);
723 END IF;
724
725 -- Standard call to check for call compatibility
726 IF NOT FND_API.Compatible_API_Call ( l_api_version,
727 p_api_version,
728 l_api_name,
729 G_PKG_NAME )
730 THEN
731 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
732 END IF;
733
734 -- Initialize message list if p_init_msg_list is set to TRUE
735 IF FND_API.to_Boolean( p_init_msg_list ) THEN
736 FND_MSG_PUB.initialize;
737 END IF;
738
739 -- Initialize API return status to success
740 x_return_status := FND_API.G_RET_STS_SUCCESS;
741 l_return_status := FND_API.G_RET_STS_SUCCESS;
742
743 -- Initialize the period end date
744 l_end_date := p_end_date + 0.99999;
745
746 -- Loop for each distribution
747 l_stmt_num := 50;
748
749 --
750 --{Specialization BUG#7296737
751 --
752 IF (l_pLog) THEN
753 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_module,'Determining the cursor');
754 END IF;
755
756 IF (p_vendor_id IS NULL AND
757 p_vendor_from IS NULL AND
758 p_vendor_to IS NULL AND
759 p_category_id IS NULL AND
760 p_category_from IS NULL AND
761 p_category_to IS NULL)
762 THEN
763 l_use := 'c_no_vendor_no_category';
764
765 ELSIF ( (p_vendor_id IS NOT NULL OR
766 p_vendor_from IS NOT NULL OR
767 p_vendor_to IS NOT NULL ) AND
768 p_category_id IS NULL AND
769 p_category_from IS NULL AND
770 p_category_to IS NULL)
771 THEN
772 IF p_vendor_id IS NOT NULL THEN
773 l_use := 'c_vendor_id_no_category';
774 ELSE
775 l_use := 'c_vendor_range_no_category';
776 END IF;
777
778 ELSIF ( p_vendor_id IS NULL AND
779 p_vendor_from IS NULL AND
780 p_vendor_to IS NULL AND
781 ( p_category_id IS NOT NULL OR
782 p_category_from IS NOT NULL OR
783 p_category_to IS NOT NULL))
784 THEN
785 IF p_category_id IS NOT NULL THEN
786 l_use := 'c_no_vendor_category_id';
787 ELSE
788 l_use := 'c_no_vendor_category_range';
789 END IF;
790
791 ELSE
792 -- General case
793 l_use := 'l_distribution_csr';
794 END IF;
795
796 IF (l_pLog) THEN
797 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_module,'l_use :'||l_use);
798 END IF;
799
800 IF l_use = 'c_no_vendor_no_category' THEN
801
802 FOR l_distribution_rec IN c_no_vendor_no_category LOOP
803 l_accrual_rec.shipment_id := l_distribution_rec.line_location_id;
804 l_accrual_rec.distribution_id := l_distribution_rec.po_distribution_id;
805 l_accrual_rec.category_id := l_distribution_rec.category_id;
806 l_accrual_rec.match_option := l_distribution_rec.match_option;
807 l_accrual_rec.currency_code := l_distribution_rec.currency_code;
808 l_accrual_rec.currency_conversion_type := l_distribution_rec.curr_conv_type;
809 l_accrual_rec.currency_conversion_rate := l_distribution_rec.currency_rate;
810 l_accrual_rec.currency_conversion_date := l_distribution_rec.currency_conv_date;
811
812 l_stmt_num := 110;
813 l_accrual_rec.unit_price := l_distribution_rec.po_price
814 + po_tax_sv.get_tax( 'PO', l_distribution_rec.po_distribution_id)
815 / l_distribution_rec.quantity_ordered;
816 l_stmt_num := 120;
817 Calculate_AccrualAmount(
818 p_api_version => 1.0,
819 p_init_msg_list => FND_API.G_FALSE,
820 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
821 x_return_status => l_return_status,
822 x_msg_count => x_msg_count,
823 x_msg_data => x_msg_data,
824 p_service_flag => l_distribution_rec.service_flag,
825 p_dist_qty => l_distribution_rec.distribution_quantity,
826 p_shipment_qty => l_distribution_rec.shipment_quantity,
827 p_end_date => l_end_date,
828 x_accrual_rec => l_accrual_rec
829 );
830 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
831 l_msg_data := 'Error Occured while calculating acrual amount against the Shipment id :' ||
832 TO_CHAR(l_distribution_rec.line_location_id) ||
833 ' ,Distribution Id :' || TO_CHAR(l_distribution_rec.po_distribution_id);
834 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
835 END IF;
836
837 l_stmt_num := 130;
838 IF ((l_accrual_rec.quantity_received > l_accrual_rec.quantity_billed)
839 OR
840 (l_accrual_rec.quantity_received < l_accrual_rec.quantity_billed AND
841 p_calling_api = G_UNINVOICED_RECEIPT_REPORT))
842 THEN
843 -- Get the position of the new row to be added
844 l_ctr := l_acr_dist_id_tbl.COUNT + 1;
845
846 l_acr_dist_id_tbl(l_ctr) := l_accrual_rec.distribution_id;
847 l_acr_shipment_id_tbl(l_ctr) := l_accrual_rec.shipment_id;
848 l_acr_category_id_tbl(l_ctr) := l_accrual_rec.category_id;
849 l_acr_match_option_tbl(l_ctr) := l_accrual_rec.match_option;
850 l_acr_qty_received_tbl(l_ctr) := l_accrual_rec.quantity_received;
851 l_acr_qty_billed_tbl(l_ctr) := l_accrual_rec.quantity_billed;
852 l_acr_accrual_qty_tbl(l_ctr) := l_accrual_rec.accrual_quantity;
853 l_acr_encum_qty_tbl(l_ctr) := l_accrual_rec.encum_quantity;
854 l_acr_unit_price_tbl(l_ctr) := l_accrual_rec.unit_price;
855 l_acr_accrual_amount_tbl(l_ctr):= l_accrual_rec.accrual_amount;
856 l_acr_encum_amount_tbl(l_ctr) := l_accrual_rec.encum_amount;
857 l_acr_cur_code_tbl(l_ctr) := l_accrual_rec.currency_code;
858 l_acr_cur_conv_type_tbl(l_ctr) := l_accrual_rec.currency_conversion_type;
859 l_acr_cur_conv_rate_tbl(l_ctr) := l_accrual_rec.currency_conversion_rate;
860 l_acr_cur_conv_date_tbl(l_ctr) := l_accrual_rec.currency_conversion_date;
861
862 END IF;
863
864 IF (p_calling_api = G_RECEIPT_ACCRUAL_PER_END) THEN
865 CST_ReceiptAccrualPerEnd_PVT.g_accrued_dist_id_tbl(CST_ReceiptAccrualPerEnd_PVT.g_accrued_dist_id_tbl.COUNT + 1)
866 := l_distribution_rec.po_distribution_id;
867 END IF;
868
869 END LOOP; --end of c_no_vendor_no_category loop
870
871
872 ELSIF l_use = 'c_vendor_id_no_category' THEN
873
874 FOR l_distribution_rec IN c_vendor_id_no_category LOOP
875
876 l_accrual_rec.shipment_id := l_distribution_rec.line_location_id;
877 l_accrual_rec.distribution_id := l_distribution_rec.po_distribution_id;
878 l_accrual_rec.category_id := l_distribution_rec.category_id;
879 l_accrual_rec.match_option := l_distribution_rec.match_option;
880 l_accrual_rec.currency_code := l_distribution_rec.currency_code;
881 l_accrual_rec.currency_conversion_type := l_distribution_rec.curr_conv_type;
882 l_accrual_rec.currency_conversion_rate := l_distribution_rec.currency_rate;
883 l_accrual_rec.currency_conversion_date := l_distribution_rec.currency_conv_date;
884
885 l_stmt_num := 110;
886 l_accrual_rec.unit_price := l_distribution_rec.po_price
887 + po_tax_sv.get_tax( 'PO', l_distribution_rec.po_distribution_id)
888 / l_distribution_rec.quantity_ordered;
889
890 l_stmt_num := 120;
891 Calculate_AccrualAmount(
892 p_api_version => 1.0,
893 p_init_msg_list => FND_API.G_FALSE,
894 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
895 x_return_status => l_return_status,
896 x_msg_count => x_msg_count,
897 x_msg_data => x_msg_data,
898 p_service_flag => l_distribution_rec.service_flag,
899 p_dist_qty => l_distribution_rec.distribution_quantity,
900 p_shipment_qty => l_distribution_rec.shipment_quantity,
901 p_end_date => l_end_date,
902 x_accrual_rec => l_accrual_rec
903 );
904
905 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
906 l_msg_data := 'Error Occured while calculating acrual amount against the Shipment id :' ||
907 TO_CHAR(l_distribution_rec.line_location_id) ||
908 ' ,Distribution Id :' || TO_CHAR(l_distribution_rec.po_distribution_id);
909 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
910 END IF;
911
912 l_stmt_num := 130;
913 IF ((l_accrual_rec.quantity_received > l_accrual_rec.quantity_billed)
914 OR
915 (l_accrual_rec.quantity_received < l_accrual_rec.quantity_billed AND
916 p_calling_api = G_UNINVOICED_RECEIPT_REPORT))
917 THEN
918
919 -- Get the position of the new row to be added
920 l_ctr := l_acr_dist_id_tbl.COUNT + 1;
921
922 l_acr_dist_id_tbl(l_ctr) := l_accrual_rec.distribution_id;
923 l_acr_shipment_id_tbl(l_ctr) := l_accrual_rec.shipment_id;
924 l_acr_category_id_tbl(l_ctr) := l_accrual_rec.category_id;
925 l_acr_match_option_tbl(l_ctr) := l_accrual_rec.match_option;
926 l_acr_qty_received_tbl(l_ctr) := l_accrual_rec.quantity_received;
927 l_acr_qty_billed_tbl(l_ctr) := l_accrual_rec.quantity_billed;
928 l_acr_accrual_qty_tbl(l_ctr) := l_accrual_rec.accrual_quantity;
929 l_acr_encum_qty_tbl(l_ctr) := l_accrual_rec.encum_quantity;
930 l_acr_unit_price_tbl(l_ctr) := l_accrual_rec.unit_price;
931 l_acr_accrual_amount_tbl(l_ctr):= l_accrual_rec.accrual_amount;
932 l_acr_encum_amount_tbl(l_ctr) := l_accrual_rec.encum_amount;
933 l_acr_cur_code_tbl(l_ctr) := l_accrual_rec.currency_code;
934 l_acr_cur_conv_type_tbl(l_ctr) := l_accrual_rec.currency_conversion_type;
935 l_acr_cur_conv_rate_tbl(l_ctr) := l_accrual_rec.currency_conversion_rate;
936 l_acr_cur_conv_date_tbl(l_ctr) := l_accrual_rec.currency_conversion_date;
937
938 END IF;
939
940 IF (p_calling_api = G_RECEIPT_ACCRUAL_PER_END) THEN
941 CST_ReceiptAccrualPerEnd_PVT.g_accrued_dist_id_tbl(CST_ReceiptAccrualPerEnd_PVT.g_accrued_dist_id_tbl.COUNT + 1)
942 := l_distribution_rec.po_distribution_id;
943 END IF;
944
945 END LOOP; --end of c_vendor_id_no_category loop
946
947 ELSIF l_use = 'c_vendor_range_no_category' THEN
948
949 FOR l_distribution_rec IN c_vendor_range_no_category LOOP
950
951 l_accrual_rec.shipment_id := l_distribution_rec.line_location_id;
952 l_accrual_rec.distribution_id := l_distribution_rec.po_distribution_id;
953 l_accrual_rec.category_id := l_distribution_rec.category_id;
954 l_accrual_rec.match_option := l_distribution_rec.match_option;
955 l_accrual_rec.currency_code := l_distribution_rec.currency_code;
956 l_accrual_rec.currency_conversion_type := l_distribution_rec.curr_conv_type;
957 l_accrual_rec.currency_conversion_rate := l_distribution_rec.currency_rate;
958 l_accrual_rec.currency_conversion_date := l_distribution_rec.currency_conv_date;
959
960 l_stmt_num := 110;
961 l_accrual_rec.unit_price := l_distribution_rec.po_price
962 + po_tax_sv.get_tax( 'PO', l_distribution_rec.po_distribution_id)
963 / l_distribution_rec.quantity_ordered;
964
965 l_stmt_num := 120;
966 Calculate_AccrualAmount(
967 p_api_version => 1.0,
968 p_init_msg_list => FND_API.G_FALSE,
969 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
970 x_return_status => l_return_status,
971 x_msg_count => x_msg_count,
972 x_msg_data => x_msg_data,
973 p_service_flag => l_distribution_rec.service_flag,
974 p_dist_qty => l_distribution_rec.distribution_quantity,
975 p_shipment_qty => l_distribution_rec.shipment_quantity,
976 p_end_date => l_end_date,
977 x_accrual_rec => l_accrual_rec
978 );
979
980 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
981 l_msg_data := 'Error Occured while calculating acrual amount against the Shipment id :' ||
982 TO_CHAR(l_distribution_rec.line_location_id) ||
983 ' ,Distribution Id :' || TO_CHAR(l_distribution_rec.po_distribution_id);
984 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
985 END IF;
986
987 l_stmt_num := 130;
988 IF ((l_accrual_rec.quantity_received > l_accrual_rec.quantity_billed)
989 OR
990 (l_accrual_rec.quantity_received < l_accrual_rec.quantity_billed AND
991 p_calling_api = G_UNINVOICED_RECEIPT_REPORT))
992 THEN
993 l_ctr := l_acr_dist_id_tbl.COUNT + 1;
994
995 l_acr_dist_id_tbl(l_ctr) := l_accrual_rec.distribution_id;
996 l_acr_shipment_id_tbl(l_ctr) := l_accrual_rec.shipment_id;
997 l_acr_category_id_tbl(l_ctr) := l_accrual_rec.category_id;
998 l_acr_match_option_tbl(l_ctr) := l_accrual_rec.match_option;
999 l_acr_qty_received_tbl(l_ctr) := l_accrual_rec.quantity_received;
1000 l_acr_qty_billed_tbl(l_ctr) := l_accrual_rec.quantity_billed;
1001 l_acr_accrual_qty_tbl(l_ctr) := l_accrual_rec.accrual_quantity;
1002 l_acr_encum_qty_tbl(l_ctr) := l_accrual_rec.encum_quantity;
1003 l_acr_unit_price_tbl(l_ctr) := l_accrual_rec.unit_price;
1004 l_acr_accrual_amount_tbl(l_ctr):= l_accrual_rec.accrual_amount;
1005 l_acr_encum_amount_tbl(l_ctr) := l_accrual_rec.encum_amount;
1006 l_acr_cur_code_tbl(l_ctr) := l_accrual_rec.currency_code;
1007 l_acr_cur_conv_type_tbl(l_ctr) := l_accrual_rec.currency_conversion_type;
1008 l_acr_cur_conv_rate_tbl(l_ctr) := l_accrual_rec.currency_conversion_rate;
1009 l_acr_cur_conv_date_tbl(l_ctr) := l_accrual_rec.currency_conversion_date;
1010
1011 END IF;
1012
1013 IF (p_calling_api = G_RECEIPT_ACCRUAL_PER_END) THEN
1014 CST_ReceiptAccrualPerEnd_PVT.g_accrued_dist_id_tbl(CST_ReceiptAccrualPerEnd_PVT.g_accrued_dist_id_tbl.COUNT + 1)
1015 := l_distribution_rec.po_distribution_id;
1016 END IF;
1017
1018 END LOOP; --end of c_vendor_range_no_category loop
1019
1020 ELSIF l_use = 'c_no_vendor_category_range' THEN
1021
1022 FOR l_distribution_rec IN c_no_vendor_category_range LOOP
1023
1024 l_accrual_rec.shipment_id := l_distribution_rec.line_location_id;
1025 l_accrual_rec.distribution_id := l_distribution_rec.po_distribution_id;
1026 l_accrual_rec.category_id := l_distribution_rec.category_id;
1027 l_accrual_rec.match_option := l_distribution_rec.match_option;
1028 l_accrual_rec.currency_code := l_distribution_rec.currency_code;
1029 l_accrual_rec.currency_conversion_type := l_distribution_rec.curr_conv_type;
1030 l_accrual_rec.currency_conversion_rate := l_distribution_rec.currency_rate;
1031 l_accrual_rec.currency_conversion_date := l_distribution_rec.currency_conv_date;
1032
1033 l_stmt_num := 110;
1034 l_accrual_rec.unit_price := l_distribution_rec.po_price
1035 + po_tax_sv.get_tax( 'PO', l_distribution_rec.po_distribution_id)
1036 / l_distribution_rec.quantity_ordered;
1037 l_stmt_num := 120;
1038 Calculate_AccrualAmount(
1039 p_api_version => 1.0,
1040 p_init_msg_list => FND_API.G_FALSE,
1041 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1042 x_return_status => l_return_status,
1043 x_msg_count => x_msg_count,
1044 x_msg_data => x_msg_data,
1045 p_service_flag => l_distribution_rec.service_flag,
1046 p_dist_qty => l_distribution_rec.distribution_quantity,
1047 p_shipment_qty => l_distribution_rec.shipment_quantity,
1048 p_end_date => l_end_date,
1049 x_accrual_rec => l_accrual_rec
1050 );
1051
1052 -- If return status is not success, raise unexpected exception
1053 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1054 l_msg_data := 'Error Occured while calculating acrual amount against the Shipment id :' ||
1055 TO_CHAR(l_distribution_rec.line_location_id) ||
1056 ' ,Distribution Id :' || TO_CHAR(l_distribution_rec.po_distribution_id);
1057 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1058 END IF;
1059
1060 l_stmt_num := 130;
1061 IF ((l_accrual_rec.quantity_received > l_accrual_rec.quantity_billed)
1062 OR
1063 (l_accrual_rec.quantity_received < l_accrual_rec.quantity_billed AND
1064 p_calling_api = G_UNINVOICED_RECEIPT_REPORT))
1065 THEN
1066 l_ctr := l_acr_dist_id_tbl.COUNT + 1;
1067
1068 l_acr_dist_id_tbl(l_ctr) := l_accrual_rec.distribution_id;
1069 l_acr_shipment_id_tbl(l_ctr) := l_accrual_rec.shipment_id;
1070 l_acr_category_id_tbl(l_ctr) := l_accrual_rec.category_id;
1071 l_acr_match_option_tbl(l_ctr) := l_accrual_rec.match_option;
1072 l_acr_qty_received_tbl(l_ctr) := l_accrual_rec.quantity_received;
1073 l_acr_qty_billed_tbl(l_ctr) := l_accrual_rec.quantity_billed;
1074 l_acr_accrual_qty_tbl(l_ctr) := l_accrual_rec.accrual_quantity;
1075 l_acr_encum_qty_tbl(l_ctr) := l_accrual_rec.encum_quantity;
1076 l_acr_unit_price_tbl(l_ctr) := l_accrual_rec.unit_price;
1077 l_acr_accrual_amount_tbl(l_ctr):= l_accrual_rec.accrual_amount;
1078 l_acr_encum_amount_tbl(l_ctr) := l_accrual_rec.encum_amount;
1079 l_acr_cur_code_tbl(l_ctr) := l_accrual_rec.currency_code;
1080 l_acr_cur_conv_type_tbl(l_ctr) := l_accrual_rec.currency_conversion_type;
1081 l_acr_cur_conv_rate_tbl(l_ctr) := l_accrual_rec.currency_conversion_rate;
1082 l_acr_cur_conv_date_tbl(l_ctr) := l_accrual_rec.currency_conversion_date;
1083
1084 END IF;
1085
1086 IF (p_calling_api = G_RECEIPT_ACCRUAL_PER_END) THEN
1087 CST_ReceiptAccrualPerEnd_PVT.g_accrued_dist_id_tbl(CST_ReceiptAccrualPerEnd_PVT.g_accrued_dist_id_tbl.COUNT + 1)
1088 := l_distribution_rec.po_distribution_id;
1089 END IF;
1090
1091 END LOOP; --end of c_no_vendor_category_range loop
1092
1093 ELSIF l_use = 'c_no_vendor_category_id' THEN
1094
1095 FOR l_distribution_rec IN c_no_vendor_category_id LOOP
1096
1097 l_accrual_rec.shipment_id := l_distribution_rec.line_location_id;
1098 l_accrual_rec.distribution_id := l_distribution_rec.po_distribution_id;
1099 l_accrual_rec.category_id := l_distribution_rec.category_id;
1100 l_accrual_rec.match_option := l_distribution_rec.match_option;
1101 l_accrual_rec.currency_code := l_distribution_rec.currency_code;
1102 l_accrual_rec.currency_conversion_type := l_distribution_rec.curr_conv_type;
1103 l_accrual_rec.currency_conversion_rate := l_distribution_rec.currency_rate;
1104 l_accrual_rec.currency_conversion_date := l_distribution_rec.currency_conv_date;
1105
1106 l_stmt_num := 110;
1107 l_accrual_rec.unit_price := l_distribution_rec.po_price
1108 + po_tax_sv.get_tax( 'PO', l_distribution_rec.po_distribution_id)
1109 / l_distribution_rec.quantity_ordered;
1110 l_stmt_num := 120;
1111 Calculate_AccrualAmount(
1112 p_api_version => 1.0,
1113 p_init_msg_list => FND_API.G_FALSE,
1114 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1115 x_return_status => l_return_status,
1116 x_msg_count => x_msg_count,
1117 x_msg_data => x_msg_data,
1118 p_service_flag => l_distribution_rec.service_flag,
1119 p_dist_qty => l_distribution_rec.distribution_quantity,
1120 p_shipment_qty => l_distribution_rec.shipment_quantity,
1121 p_end_date => l_end_date,
1122 x_accrual_rec => l_accrual_rec
1123 );
1124
1125 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1126 l_msg_data := 'Error Occured while calculating acrual amount against the Shipment id :' ||
1127 TO_CHAR(l_distribution_rec.line_location_id) ||
1128 ' ,Distribution Id :' || TO_CHAR(l_distribution_rec.po_distribution_id);
1129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1130 END IF;
1131
1132 l_stmt_num := 130;
1133 IF ((l_accrual_rec.quantity_received > l_accrual_rec.quantity_billed)
1134 OR
1135 (l_accrual_rec.quantity_received < l_accrual_rec.quantity_billed AND
1136 p_calling_api = G_UNINVOICED_RECEIPT_REPORT))
1137 THEN
1138 l_ctr := l_acr_dist_id_tbl.COUNT + 1;
1139
1140 l_acr_dist_id_tbl(l_ctr) := l_accrual_rec.distribution_id;
1141 l_acr_shipment_id_tbl(l_ctr) := l_accrual_rec.shipment_id;
1142 l_acr_category_id_tbl(l_ctr) := l_accrual_rec.category_id;
1143 l_acr_match_option_tbl(l_ctr) := l_accrual_rec.match_option;
1144 l_acr_qty_received_tbl(l_ctr) := l_accrual_rec.quantity_received;
1145 l_acr_qty_billed_tbl(l_ctr) := l_accrual_rec.quantity_billed;
1146 l_acr_accrual_qty_tbl(l_ctr) := l_accrual_rec.accrual_quantity;
1147 l_acr_encum_qty_tbl(l_ctr) := l_accrual_rec.encum_quantity;
1148 l_acr_unit_price_tbl(l_ctr) := l_accrual_rec.unit_price;
1149 l_acr_accrual_amount_tbl(l_ctr):= l_accrual_rec.accrual_amount;
1150 l_acr_encum_amount_tbl(l_ctr) := l_accrual_rec.encum_amount;
1151 l_acr_cur_code_tbl(l_ctr) := l_accrual_rec.currency_code;
1152 l_acr_cur_conv_type_tbl(l_ctr) := l_accrual_rec.currency_conversion_type;
1153 l_acr_cur_conv_rate_tbl(l_ctr) := l_accrual_rec.currency_conversion_rate;
1154 l_acr_cur_conv_date_tbl(l_ctr) := l_accrual_rec.currency_conversion_date;
1155
1156 END IF;
1157
1158 IF (p_calling_api = G_RECEIPT_ACCRUAL_PER_END) THEN
1159 CST_ReceiptAccrualPerEnd_PVT.g_accrued_dist_id_tbl(CST_ReceiptAccrualPerEnd_PVT.g_accrued_dist_id_tbl.COUNT + 1)
1160 := l_distribution_rec.po_distribution_id;
1161 END IF;
1162
1163 END LOOP; --end of c_no_vendor_category_id loop
1164
1165 ELSE
1166
1167 FOR l_distribution_rec IN l_distribution_csr LOOP
1168
1169 l_accrual_rec.shipment_id := l_distribution_rec.line_location_id;
1170 l_accrual_rec.distribution_id := l_distribution_rec.po_distribution_id;
1171 l_accrual_rec.category_id := l_distribution_rec.category_id;
1172 l_accrual_rec.match_option := l_distribution_rec.match_option;
1173 l_accrual_rec.currency_code := l_distribution_rec.currency_code;
1174 l_accrual_rec.currency_conversion_type := l_distribution_rec.curr_conv_type;
1175 l_accrual_rec.currency_conversion_rate := l_distribution_rec.currency_rate;
1176 l_accrual_rec.currency_conversion_date := l_distribution_rec.currency_conv_date;
1177
1178 ---------------------------------------------------------------------
1179 -- Unit Price = po_price + tax
1180 -- Tax amount in pod is not recalculated in case of cancellation,
1181 -- so we do not consider cancelled qty while prorating dist tax.
1182 ---------------------------------------------------------------------
1183 l_stmt_num := 110;
1184 l_accrual_rec.unit_price := l_distribution_rec.po_price
1185 + po_tax_sv.get_tax( 'PO', l_distribution_rec.po_distribution_id)
1186 / l_distribution_rec.quantity_ordered;
1187
1188 ---------------------------------------------------------------------
1189 -- The procedure Calculate_AccrualAmount calculates the
1190 -- accrual_amount and encum_amount
1191 ---------------------------------------------------------------------
1192 l_stmt_num := 120;
1193 Calculate_AccrualAmount(
1194 p_api_version => 1.0,
1195 p_init_msg_list => FND_API.G_FALSE,
1196 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1197 x_return_status => l_return_status,
1198 x_msg_count => x_msg_count,
1199 x_msg_data => x_msg_data,
1200 p_service_flag => l_distribution_rec.service_flag,
1201 p_dist_qty => l_distribution_rec.distribution_quantity,
1202 p_shipment_qty => l_distribution_rec.shipment_quantity,
1203 p_end_date => l_end_date,
1204 x_accrual_rec => l_accrual_rec
1205 );
1206
1207 -- If return status is not success, raise unexpected exception
1208 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1209 l_msg_data := 'Error Occured while calculating acrual amount against the Shipment id :' ||
1210 TO_CHAR(l_distribution_rec.line_location_id) ||
1211 ' ,Distribution Id :' || TO_CHAR(l_distribution_rec.po_distribution_id);
1212 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1213 END IF;
1214
1215 ---------------------------------------------------------------------
1216 -- We need only those accrual entries, for which:
1217 -- EITHER
1218 -- qty_received > qty_billed
1219 -- OR
1220 -- if qty_received < qty_billed and the process has been called by
1221 -- Uninvoiced Receipt Report (This case is used only for reporting
1222 -- over-invoicing)
1223 --
1224 -- Note: No need to consider the cases when qty_received = qty_billed
1225 ---------------------------------------------------------------------
1226 l_stmt_num := 130;
1227 IF ((l_accrual_rec.quantity_received > l_accrual_rec.quantity_billed)
1228 OR
1229 (l_accrual_rec.quantity_received < l_accrual_rec.quantity_billed AND
1230 p_calling_api = G_UNINVOICED_RECEIPT_REPORT))
1231 THEN
1232
1233 -- Get the position of the new row to be added
1234 l_ctr := l_acr_dist_id_tbl.COUNT + 1;
1235
1236 -----------------------------------------------------------------
1237 -- Add the record values to the PL/SQL tables
1238 -----------------------------------------------------------------
1239 l_acr_dist_id_tbl(l_ctr) := l_accrual_rec.distribution_id;
1240 l_acr_shipment_id_tbl(l_ctr) := l_accrual_rec.shipment_id;
1241 l_acr_category_id_tbl(l_ctr) := l_accrual_rec.category_id;
1242 l_acr_match_option_tbl(l_ctr) := l_accrual_rec.match_option;
1243 l_acr_qty_received_tbl(l_ctr) := l_accrual_rec.quantity_received;
1244 l_acr_qty_billed_tbl(l_ctr) := l_accrual_rec.quantity_billed;
1245 l_acr_accrual_qty_tbl(l_ctr) := l_accrual_rec.accrual_quantity;
1246 l_acr_encum_qty_tbl(l_ctr) := l_accrual_rec.encum_quantity;
1247 l_acr_unit_price_tbl(l_ctr) := l_accrual_rec.unit_price;
1248 l_acr_accrual_amount_tbl(l_ctr):= l_accrual_rec.accrual_amount;
1249 l_acr_encum_amount_tbl(l_ctr) := l_accrual_rec.encum_amount;
1250 l_acr_cur_code_tbl(l_ctr) := l_accrual_rec.currency_code;
1251 l_acr_cur_conv_type_tbl(l_ctr) := l_accrual_rec.currency_conversion_type;
1252 l_acr_cur_conv_rate_tbl(l_ctr) := l_accrual_rec.currency_conversion_rate;
1253 l_acr_cur_conv_date_tbl(l_ctr) := l_accrual_rec.currency_conversion_date;
1254
1255 END IF;
1256
1257 ---------------------------------------------------------------------
1258 -- Add the distribution_id to the list of accrued distribution_id for
1259 -- Receipt Accrual-Period End Process
1260 ---------------------------------------------------------------------
1261 IF (p_calling_api = G_RECEIPT_ACCRUAL_PER_END) THEN
1262 CST_ReceiptAccrualPerEnd_PVT.g_accrued_dist_id_tbl(CST_ReceiptAccrualPerEnd_PVT.g_accrued_dist_id_tbl.COUNT + 1)
1263 := l_distribution_rec.po_distribution_id;
1264 END IF;
1265
1266 END LOOP; --end of l_distribution_rec loop
1267
1268 END IF;
1269
1270 -------------------------------------------------------------------------
1271 -- Bulk insert the data from PL/SQL tables to temp table
1272 -------------------------------------------------------------------------
1273 l_stmt_num := 140;
1274 FORALL l_ctr IN l_acr_dist_id_tbl.FIRST..l_acr_dist_id_tbl.LAST
1275 INSERT INTO cst_per_end_accruals_temp (
1276 shipment_id,
1277 distribution_id,
1278 category_id,
1279 match_option,
1280 quantity_received,
1281 quantity_billed,
1282 accrual_quantity,
1283 encum_quantity,
1284 unit_price,
1285 accrual_amount,
1286 encum_amount,
1287 currency_code,
1288 currency_conversion_type,
1289 currency_conversion_rate,
1290 currency_conversion_date
1291 )
1292 VALUES (
1293 l_acr_shipment_id_tbl(l_ctr),
1294 l_acr_dist_id_tbl(l_ctr),
1295 l_acr_category_id_tbl(l_ctr),
1296 l_acr_match_option_tbl(l_ctr),
1297 l_acr_qty_received_tbl(l_ctr),
1298 l_acr_qty_billed_tbl(l_ctr),
1299 l_acr_accrual_qty_tbl(l_ctr),
1300 l_acr_encum_qty_tbl(l_ctr),
1301 l_acr_unit_price_tbl(l_ctr),
1302 l_acr_accrual_amount_tbl(l_ctr),
1303 l_acr_encum_amount_tbl(l_ctr),
1304 l_acr_cur_code_tbl(l_ctr),
1305 l_acr_cur_conv_type_tbl(l_ctr),
1306 l_acr_cur_conv_rate_tbl(l_ctr),
1307 l_acr_cur_conv_date_tbl(l_ctr)
1308 );
1309
1310 -------------------------------------------------------------------------
1311 -- Check for min_accrual_amount at shipment level.
1312 -- If accrual_amount for a shipment is less then min_accrual_amount, then
1313 -- delete the rows related to that shipment from the temporary table.
1314 -------------------------------------------------------------------------
1315 l_stmt_num := 150;
1316 DELETE FROM cst_per_end_accruals_temp
1317 WHERE shipment_id IN (SELECT shipment_id
1318 FROM cst_per_end_accruals_temp
1319 GROUP BY shipment_id
1320 HAVING SUM(accrual_amount) < NVL(p_min_accrual_amount, 0)
1321 );
1322
1323 -- Procedure level log message for exit point
1324 IF (l_pLog) THEN
1325 FND_LOG.STRING(FND_LOG.LEVEL_PROCEDURE,l_module,'Create_PerEndAccruals >>');
1326 END IF;
1327
1328 -- Get message count and if 1, return message data.
1329 FND_MSG_PUB.Count_And_Get
1330 ( p_count => x_msg_count,
1331 p_data => x_msg_data
1332 );
1333
1334 -- Standard check of p_commit.
1335 IF FND_API.To_Boolean( p_commit ) THEN
1336 COMMIT WORK;
1337 END IF;
1338
1339 EXCEPTION
1340
1341 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1342 ROLLBACK TO Create_PerEndAccruals_PVT;
1343 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1344
1345 IF (l_exceptionLog) THEN
1346 FND_LOG.STRING(
1347 FND_LOG.LEVEL_EXCEPTION,
1348 l_module || '.' || l_stmt_num,
1349 l_msg_data
1350 );
1351 END IF;
1352
1353 FND_MSG_PUB.Count_And_Get
1354 ( p_count => x_msg_count,
1355 p_data => x_msg_data
1356 );
1357
1358 WHEN OTHERS THEN
1359 ROLLBACK TO Create_PerEndAccruals_PVT;
1360 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1361
1362 -- Unexpected level log message
1363 IF (l_uLog) THEN
1364 FND_LOG.STRING(
1365 FND_LOG.LEVEL_UNEXPECTED,
1366 l_module || '.' || l_stmt_num,
1367 SQLERRM
1368 );
1369 END IF;
1370
1371 IF FND_MSG_PUB.Check_Msg_Level
1372 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
1373 THEN
1374 FND_MSG_PUB.Add_Exc_Msg
1375 ( G_PKG_NAME,
1376 l_api_name,
1377 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
1378 );
1379 END IF;
1380
1381 FND_MSG_PUB.Count_And_Get
1382 ( p_count => x_msg_count,
1383 p_data => x_msg_data
1384 );
1385
1386 END Create_PerEndAccruals;
1387
1388 -----------------------------------------------------------------------------
1389 -- PROCEDURE : Calculate_AccrualAmount
1390 -- DESCRIPTION : The procedure calculates and returns the record for the
1391 -- CST_PER_END_ACCRUALS_TEMP
1392 -----------------------------------------------------------------------------
1393 PROCEDURE Calculate_AccrualAmount
1394 (
1395 p_api_version IN NUMBER,
1396 p_init_msg_list IN VARCHAR2,
1397 p_validation_level IN NUMBER,
1398 x_return_status OUT NOCOPY VARCHAR2,
1399 x_msg_count OUT NOCOPY NUMBER,
1400 x_msg_data OUT NOCOPY VARCHAR2,
1401
1402 p_service_flag IN NUMBER,
1403 p_dist_qty IN NUMBER,
1404 p_shipment_qty IN NUMBER,
1405 p_end_date IN DATE,
1406 p_transaction_id IN NUMBER,
1407
1408 x_accrual_rec IN OUT NOCOPY CST_PER_END_ACCRUALS_TEMP%ROWTYPE
1409 )
1410
1411 IS
1412 l_api_name CONSTANT VARCHAR2(30) :='Calculate_AccrualAmount';
1413 l_api_version CONSTANT NUMBER := 1.0;
1414 l_return_status VARCHAR2(1);
1415
1416 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
1417 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
1418
1419 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
1420 l_exceptionLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
1421 l_pLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
1422 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
1423
1424 l_stmt_num NUMBER;
1425 l_msg_data VARCHAR2(240);
1426
1427 l_last_rcv_shipment_id NUMBER;
1428 l_shipment_remaining_qty NUMBER;
1429 l_dist_remaining_qty NUMBER;
1430 l_allocated_qty NUMBER;
1431 l_txn_net_qty_received NUMBER;
1432 l_txn_net_qty_delivered NUMBER;
1433 l_qty_in_receiving NUMBER;
1434 l_over_receipt_qty NUMBER;
1435 l_dist_qty_delivered NUMBER;
1436 l_sum_func_amount NUMBER;
1437 l_sum_allocate_amount NUMBER;
1438
1439 l_net_qty_received NUMBER;
1440 l_qty_received NUMBER;
1441 l_qty_invoiced NUMBER;
1442
1443 -------------------------------------------------------------------------
1444 -- For match to Receipt cases, need to traverse all the receipts created
1445 -- against the po_shipment.
1446 -------------------------------------------------------------------------
1447 CURSOR l_transaction_csr IS
1448 SELECT transaction_id,
1449 shipment_header_id,
1450 currency_code,
1451 NVL(currency_conversion_rate, 1) currency_conversion_rate,
1452 currency_conversion_date,
1453 currency_conversion_type
1454 FROM rcv_transactions
1455 WHERE po_line_location_id = x_accrual_rec.shipment_id
1456 AND ((transaction_type = 'RECEIVE' AND parent_transaction_id = -1)
1457 OR
1458 (transaction_type = 'MATCH'))
1459 AND transaction_date <= p_end_date
1460 AND (p_transaction_id IS NULL
1461 OR
1462 (transaction_id = p_transaction_id AND p_transaction_id IS NOT NULL))
1463 ORDER BY shipment_header_id, transaction_date;
1464
1465 CURSOR l_po_distributions_csr IS
1466 SELECT po_distribution_id,
1467 DECODE (p_service_flag,
1468 1, amount_ordered - NVL(amount_cancelled, 0),
1469 quantity_ordered - NVL(quantity_cancelled, 0)) distribution_quantity
1470 FROM po_distributions_all
1471 WHERE line_location_id = x_accrual_rec.shipment_id;
1472
1473 BEGIN
1474
1475 l_stmt_num := 0;
1476
1477 -- Procedure level log message for Entry point
1478 IF (l_pLog) THEN
1479 FND_LOG.STRING(
1480 FND_LOG.LEVEL_PROCEDURE,
1481 l_module || '.begin',
1482 'Calculate_AccrualAmount <<' ||
1483 'p_service_flag = ' || p_service_flag ||','||
1484 'p_dist_qty = ' || p_dist_qty ||','||
1485 'p_shipment_qty = ' || p_shipment_qty ||','||
1486 'p_end_date = ' || p_end_date ||','||
1487 'p_transaction_id = ' || p_transaction_id ||','||
1488 'Shipment Id = ' || x_accrual_rec.shipment_id || ', ' ||
1489 'Distribution Id = ' || x_accrual_rec.distribution_id || ', ' ||
1490 'Match Option = ' || x_accrual_rec.match_option
1491 );
1492 END IF;
1493
1494 -- Standard call to check for call compatibility.
1495 IF NOT FND_API.Compatible_API_Call ( l_api_version,
1496 p_api_version,
1497 l_api_name,
1498 G_PKG_NAME )
1499 THEN
1500 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1501 END IF;
1502
1503 -- Initialize message list if p_init_msg_list is set to TRUE.
1504 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1505 FND_MSG_PUB.initialize;
1506 END IF;
1507
1508 -- Initialize API return status to success
1509 x_return_status := FND_API.G_RET_STS_SUCCESS;
1510 l_return_status := FND_API.G_RET_STS_SUCCESS;
1511
1512 -- Inintialize the variables
1513 x_accrual_rec.quantity_received:= 0;
1514 x_accrual_rec.quantity_billed := 0;
1515 x_accrual_rec.accrual_quantity := 0;
1516 x_accrual_rec.accrual_amount := 0;
1517
1518 l_stmt_num := 60;
1519 -- Checking values so that there will not be any division by zero cases later
1520 IF (p_shipment_qty <= 0 OR p_dist_qty <= 0) THEN
1521 FND_MSG_PUB.Add_Exc_Msg (
1522 G_PKG_NAME,
1523 l_api_name,
1524 'Shipment Id :' || x_accrual_rec.shipment_id || ',' ||
1525 'Distribution Id :' || x_accrual_rec.distribution_id || ',' ||
1526 'Ordered quantity less than or equal to zero'
1527 );
1528 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1529 END IF;
1530
1531 -------------------------------------------------------------------------
1532 -- If Match to PO cases
1533 -------------------------------------------------------------------------
1534 IF (x_accrual_rec.match_option = 'P') THEN
1535 IF (l_pLog) THEN
1536 FND_LOG.STRING(
1537 FND_LOG.LEVEL_PROCEDURE,
1538 l_module||',',
1539 ' g_shipment_id is '||g_shipment_id
1540 );
1541 END IF;
1542
1543 -------------------------------------------------------------------------
1544 -- Get net_qty_received and net_qty_delivered against the PO shipment
1545 -- and net_qty_delivered against the distribution.
1546 -------------------------------------------------------------------------
1547 IF (x_accrual_rec.shipment_id <> NVL(g_shipment_id, -999)) THEN
1548 g_shipment_id := x_accrual_rec.shipment_id;
1549
1550 IF (l_pLog) THEN
1551 FND_LOG.STRING(
1552 FND_LOG.LEVEL_PROCEDURE,
1553 l_module||',',
1554 ' g_shipment_id is '||g_shipment_id
1555 );
1556 END IF;
1557
1558 -- clear and initialize the pl/sql table for dist_delivered_quantity
1559 g_dist_nqd_tbl.DELETE;
1560 FOR l_po_distributions IN l_po_distributions_csr LOOP
1561 g_dist_nqd_tbl(l_po_distributions.po_distribution_id) := 0;
1562 END LOOP;
1563
1564 l_stmt_num := 100;
1565 Get_RcvQuantity (
1566 p_api_version => 1.0,
1567 p_init_msg_list => FND_API.G_FALSE,
1568 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1569 x_return_status => l_return_status,
1570 x_msg_count => x_msg_count,
1571 x_msg_data => x_msg_data,
1572 p_line_location_id => x_accrual_rec.shipment_id,
1573 p_service_flag => p_service_flag,
1574 p_end_date => p_end_date
1575 );
1576 -- If return status is not success, raise unexpected exception
1577 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1578 l_msg_data := 'Failed calculating quantity_received and quantity_delivered against the Shipment id :' ||
1579 TO_CHAR(x_accrual_rec.shipment_id);
1580 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1581 END IF;
1582 END IF;
1583
1584 g_shipment_net_qty_received := g_nqr;
1585
1586 IF (l_pLog) THEN
1587 FND_LOG.STRING(
1588 FND_LOG.LEVEL_PROCEDURE,
1589 l_module||',',
1590 ' g_shipment_net_qty_received is '||g_nqr
1591 );
1592 END IF;
1593
1594 g_shipment_net_qty_delivered:= g_nqd;
1595
1596 IF (l_pLog) THEN
1597 FND_LOG.STRING(
1598 FND_LOG.LEVEL_PROCEDURE,
1599 l_module||',',
1600 ' g_shipment_net_qty_delivered is '||g_nqd
1601 );
1602 END IF;
1603
1604 g_dist_net_qty_delivered := g_dist_nqd_tbl(x_accrual_rec.distribution_id);
1605 IF (l_pLog) THEN
1606 FND_LOG.STRING(
1607 FND_LOG.LEVEL_PROCEDURE,
1608 l_module||',',
1609 ' g_dist_net_qty_delivered is '||g_dist_nqd_tbl(x_accrual_rec.distribution_id)
1610
1611 );
1612 END IF;
1613
1614
1615 -- Expected quantity yet to delivered at shipment level,
1616 -- i.e., sum(dist_quantity_ordered - dist_qty_delivered)
1617 l_shipment_remaining_qty := p_shipment_qty;
1618 FOR l_po_distributions IN l_po_distributions_csr LOOP
1619 IF (l_pLog) THEN
1620 FND_LOG.STRING(
1621 FND_LOG.LEVEL_PROCEDURE,
1622 l_module||',',
1623 ' l_po_distributions.po_distribution_id is '||l_po_distributions.po_distribution_id ||','||
1624 'g_dist_nqd_tbl(l_po_distributions.po_distribution_id) qty is '|| g_dist_nqd_tbl(l_po_distributions.po_distribution_id)
1625 );
1626 END IF;
1627
1628 IF (l_po_distributions.distribution_quantity > g_dist_nqd_tbl(l_po_distributions.po_distribution_id)) THEN
1629 IF (l_pLog) THEN
1630 FND_LOG.STRING(
1631 FND_LOG.LEVEL_PROCEDURE,
1632 l_module||',',
1633 ' Inside if'
1634 );
1635 END IF;
1636
1637 l_shipment_remaining_qty := l_shipment_remaining_qty - g_dist_nqd_tbl(l_po_distributions.po_distribution_id);
1638
1639 ELSE
1640 IF (l_pLog) THEN
1641 FND_LOG.STRING(
1642 FND_LOG.LEVEL_PROCEDURE,
1643 l_module||',',
1644 ' Inside else'
1645 );
1646 END IF;
1647 l_shipment_remaining_qty := l_shipment_remaining_qty - l_po_distributions.distribution_quantity;
1648 END IF;
1649 END LOOP;
1650
1651 -- Remaining (or expected) quantity to be delivered against this distributions
1652 -- dist_quantity_ordered - dist_quantity_delivered
1653 l_dist_remaining_qty := p_dist_qty - g_dist_net_qty_delivered;
1654
1655 IF (l_pLog) THEN
1656 FND_LOG.STRING(
1657 FND_LOG.LEVEL_PROCEDURE,
1658 l_module||',',
1659 ' l_dist_remaining_qty is '|| l_dist_remaining_qty
1660 );
1661 END IF;
1662 IF (l_dist_remaining_qty < 0) THEN
1663 l_dist_remaining_qty := 0;
1664 END IF;
1665
1666 -- Quantity received, but not yet delivered
1667 l_qty_in_receiving := g_shipment_net_qty_received - g_shipment_net_qty_delivered;
1668
1669 ---------------------------------------------------------------------
1670 -- Check for over receipts
1671 ---------------------------------------------------------------------
1672 IF (g_shipment_net_qty_received > p_shipment_qty) THEN
1673 l_over_receipt_qty := g_shipment_net_qty_received
1674 - (l_shipment_remaining_qty + g_shipment_net_qty_delivered);
1675 IF (l_over_receipt_qty < 0) THEN
1676 l_over_receipt_qty := 0;
1677 END IF;
1678 -- If over receipt quantity has been fully or partially delivered
1679 IF (l_qty_in_receiving <= l_over_receipt_qty) THEN
1680 l_over_receipt_qty := l_qty_in_receiving;
1681 l_qty_in_receiving := 0;
1682
1683 -- If over receipt quantity has not yet been delivered
1684 ELSE
1685 l_qty_in_receiving := l_qty_in_receiving - l_over_receipt_qty;
1686 END IF;
1687 ELSE
1688 l_over_receipt_qty := 0;
1689 END IF;
1690
1691 ---------------------------------------------------------------------
1692 -- Prorate the remaining quantity (which has been received but not
1693 -- been delivered) among the distribution
1694 ---------------------------------------------------------------------
1695 l_stmt_num := 110;
1696 IF (l_shipment_remaining_qty <= 0) THEN
1697 l_allocated_qty := 0;
1698 ELSE
1699 l_allocated_qty := l_qty_in_receiving *
1700 l_dist_remaining_qty / l_shipment_remaining_qty;
1701 END IF;
1702
1703 ---------------------------------------------------------------------
1704 -- Prorate the over receipt quantity among the distributions
1705 -- based on the quantity ordered
1706 ---------------------------------------------------------------------
1707 l_stmt_num := 120;
1708 IF (l_over_receipt_qty > 0) THEN
1709 l_allocated_qty := l_allocated_qty + l_over_receipt_qty *
1710 p_dist_qty / p_shipment_qty;
1711 END IF;
1712
1713 ---------------------------------------------------------------------
1714 -- Total received quantity against the distribution
1715 -- = Quantity delivered + allocated quantity
1716 ---------------------------------------------------------------------
1717 l_net_qty_received := g_dist_net_qty_delivered + l_allocated_qty;
1718
1719 ---------------------------------------------------------------------
1720 -- Get quantity invoiced against the distribution
1721 ---------------------------------------------------------------------
1722 l_stmt_num := 130;
1723 Get_InvoiceQuantity (
1724 p_api_version => 1.0,
1725 p_init_msg_list => FND_API.G_FALSE,
1726 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1727 x_return_status => l_return_status,
1728 x_msg_count => x_msg_count,
1729 x_msg_data => x_msg_data,
1730 p_match_option => x_accrual_rec.match_option,
1731 p_dist_id => x_accrual_rec.distribution_id,
1732 p_service_flag => p_service_flag,
1733 p_end_date => p_end_date,
1734 x_quantity_invoiced => l_qty_invoiced
1735 );
1736 -- If return status is not success, raise unexpected exception
1737 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1738 l_msg_data := 'Failed calculating Net quantity Invoiced against the Distribution id :'
1739 || TO_CHAR(x_accrual_rec.distribution_id);
1740 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1741 END IF;
1742
1743 x_accrual_rec.quantity_received := l_net_qty_received;
1744 x_accrual_rec.quantity_billed := l_qty_invoiced;
1745
1746 END IF;
1747
1748 -------------------------------------------------------------------------
1749 -- Match to Receipt cases
1750 -------------------------------------------------------------------------
1751 -- For PAC Period end accrual calculation
1752 -- If rcv_transaction_id is not null, calculate the qty_received and
1753 -- qty_delivered against the rcv_transaction_id for both Match to PO and
1754 -- match to Receipt cases.
1755 -- In this case the l_transaction_csr loop will run only once for the
1756 -- given rcv_transaction_id.
1757 -------------------------------------------------------------------------
1758 IF (x_accrual_rec.match_option = 'R' OR p_transaction_id IS NOT NULL) THEN
1759
1760 -- Inintialize the variables
1761 IF (x_accrual_rec.distribution_id <> NVL(g_distribution_id, -999)) THEN
1762 g_shipment_net_qty_received := 0;
1763 g_shipment_net_qty_delivered := 0;
1764 g_dist_net_qty_delivered := 0;
1765 g_distribution_id := x_accrual_rec.distribution_id;
1766
1767 END IF;
1768 l_qty_received := 0;
1769 l_sum_func_amount := 0;
1770 l_sum_allocate_amount := 0;
1771 l_last_rcv_shipment_id := -999;
1772
1773 /* Bug 8675502
1774 Re-Initializing global variable g_shipment_id
1775 */
1776 g_shipment_id := x_accrual_rec.shipment_id;
1777
1778 l_stmt_num := 150;
1779
1780 l_shipment_remaining_qty := p_shipment_qty;
1781 -- Loop for each transactions
1782 FOR l_transaction_rec IN l_transaction_csr LOOP
1783
1784 -- Check if the receipt has already been traversed.
1785 IF (l_transaction_rec.shipment_header_id <> l_last_rcv_shipment_id) THEN
1786
1787 l_last_rcv_shipment_id := l_transaction_rec.shipment_header_id ;
1788
1789 -- clear the pl/sql table for distribution_delivered_quantity
1790 g_dist_nqd_tbl.DELETE;
1791 FOR l_po_distributions IN l_po_distributions_csr LOOP
1792 g_dist_nqd_tbl(l_po_distributions.po_distribution_id) := 0;
1793 END LOOP;
1794
1795 -----------------------------------------------------------------
1796 -- get net_quantity_received and net_quantity_delivered against
1797 -- the receipt
1798 -----------------------------------------------------------------
1799 l_stmt_num := 160;
1800 Get_RcvQuantity (
1801 p_api_version => 1.0,
1802 p_init_msg_list => FND_API.G_FALSE,
1803 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1804 x_return_status => l_return_status,
1805 x_msg_count => x_msg_count,
1806 x_msg_data => x_msg_data,
1807 p_line_location_id => x_accrual_rec.shipment_id,
1808 p_rcv_shipment_id => l_transaction_rec.shipment_header_id,
1809 p_rcv_txn_id => p_transaction_id,
1810 p_service_flag => p_service_flag,
1811 p_end_date => p_end_date
1812 );
1813 -- If return status is not success, raise unexpected exception
1814 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1815 l_msg_data := 'Failed calculating Net quantity received against the Transaction id :'
1816 || TO_CHAR(l_transaction_rec.transaction_id);
1817 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1818 END IF;
1819
1820 l_txn_net_qty_received := g_nqr;
1821 l_txn_net_qty_delivered:= g_nqd;
1822 l_dist_qty_delivered := g_dist_nqd_tbl(x_accrual_rec.distribution_id);
1823
1824 g_shipment_net_qty_received := g_shipment_net_qty_received + l_txn_net_qty_received;
1825 g_shipment_net_qty_delivered:= g_shipment_net_qty_delivered + l_txn_net_qty_delivered;
1826 g_dist_net_qty_delivered := g_dist_net_qty_delivered + l_dist_qty_delivered;
1827
1828 FOR l_po_distributions IN l_po_distributions_csr LOOP
1829 IF (l_po_distributions.distribution_quantity > g_dist_nqd_tbl(l_po_distributions.po_distribution_id)) THEN
1830 l_shipment_remaining_qty := l_shipment_remaining_qty - g_dist_nqd_tbl(l_po_distributions.po_distribution_id);
1831 ELSE
1832 l_shipment_remaining_qty := l_shipment_remaining_qty - l_po_distributions.distribution_quantity;
1833 END IF;
1834 END LOOP;
1835
1836 -----------------------------------------------------------------
1837 -- Total received quantity against the distribution
1838 -- = Quantity delivered + allocated quantity
1839 -----------------------------------------------------------------
1840 l_txn_net_qty_received := l_dist_qty_delivered;
1841 l_qty_received := l_qty_received + l_txn_net_qty_received;
1842
1843 x_accrual_rec.currency_code := l_transaction_rec.currency_code;
1844 x_accrual_rec.currency_conversion_type := l_transaction_rec.currency_conversion_type;
1845 x_accrual_rec.currency_conversion_rate := l_transaction_rec.currency_conversion_rate;
1846
1847 -----------------------------------------------------------------
1848 -- Calculate sum of accrual amount in functional currency, this would
1849 -- be used to calculate Currency_conversion_rate for match to receipt cases
1850 -----------------------------------------------------------------
1851 l_sum_func_amount := l_sum_func_amount
1852 + l_txn_net_qty_received * l_transaction_rec.currency_conversion_rate;
1853 l_sum_allocate_amount := l_sum_allocate_amount +
1854 (g_nqr - g_nqd) * l_transaction_rec.currency_conversion_rate;
1855
1856 -- Get the latest conversion_date
1857 IF (x_accrual_rec.currency_conversion_date IS NULL
1858 OR x_accrual_rec.currency_conversion_date < l_transaction_rec.currency_conversion_date) THEN
1859 x_accrual_rec.currency_conversion_date := l_transaction_rec.currency_conversion_date;
1860 END IF;
1861
1862 END IF;
1863 END LOOP;
1864
1865 -----------------------------------------------------------------
1866 -- Get quantity invoiced against the rcv transaction
1867 -----------------------------------------------------------------
1868 l_stmt_num := 200;
1869 Get_InvoiceQuantity (
1870 p_api_version => 1.0,
1871 p_init_msg_list => FND_API.G_FALSE,
1872 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
1873 x_return_status => l_return_status,
1874 x_msg_count => x_msg_count,
1875 x_msg_data => x_msg_data,
1876 p_match_option => x_accrual_rec.match_option,
1877 p_dist_id => x_accrual_rec.distribution_id,
1878 p_rcv_txn_id => p_transaction_id,
1879 p_service_flag => p_service_flag,
1880 p_end_date => p_end_date,
1881 x_quantity_invoiced => l_qty_invoiced
1882 );
1883 -- If return status is not success, raise unexpected exception
1884 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
1885 l_msg_data := 'Failed calculating quantity invoiced against the distribution id :'
1886 || TO_CHAR(x_accrual_rec.distribution_id);
1887 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1888 END IF;
1889
1890 x_accrual_rec.quantity_billed := l_qty_invoiced;
1891
1892
1893 -- Remaining (or expected) quantity to be delivered against this distributions
1894 l_dist_remaining_qty := p_dist_qty - g_dist_net_qty_delivered;
1895 IF (l_dist_remaining_qty < 0) THEN
1896 l_dist_remaining_qty := 0;
1897 END IF;
1898
1899 -- Quantity received, but not yet delivered
1900 l_qty_in_receiving := g_shipment_net_qty_received - g_shipment_net_qty_delivered;
1901
1902 ---------------------------------------------------------------------
1903 -- Check for over receipts
1904 ---------------------------------------------------------------------
1905 IF (g_shipment_net_qty_received > p_shipment_qty) THEN
1906 l_over_receipt_qty := g_shipment_net_qty_received
1907 - (l_shipment_remaining_qty + g_shipment_net_qty_delivered);
1908 IF (l_over_receipt_qty < 0) THEN
1909 l_over_receipt_qty := 0;
1910 END IF;
1911 -- If over receipt quantity has been fully or partially delivered
1912 IF (l_qty_in_receiving <= l_over_receipt_qty) THEN
1913 l_over_receipt_qty := l_qty_in_receiving;
1914 l_qty_in_receiving := 0;
1915
1916 -- If over receipt quantity has not yet been delivered
1917 ELSE
1918 l_qty_in_receiving := l_qty_in_receiving - l_over_receipt_qty;
1919 END IF;
1920 ELSE
1921 l_over_receipt_qty := 0;
1922 END IF;
1923
1924 ---------------------------------------------------------------------
1925 -- Prorate the remaining quantity (which has been received but not
1926 -- been delivered) among the distribution
1927 ---------------------------------------------------------------------
1928 l_stmt_num := 221;
1929 IF (l_shipment_remaining_qty <= 0) THEN
1930 l_allocated_qty := 0;
1931 ELSE
1932 l_allocated_qty := l_qty_in_receiving *
1933 l_dist_remaining_qty / l_shipment_remaining_qty;
1934 END IF;
1935
1936 ---------------------------------------------------------------------
1937 -- Prorate the over receipt quantity among the distributions
1938 -- based on the quantity ordered
1939 ---------------------------------------------------------------------
1940 l_stmt_num := 222;
1941 IF (l_over_receipt_qty > 0) THEN
1942 l_allocated_qty := l_allocated_qty + l_over_receipt_qty *
1943 p_dist_qty / p_shipment_qty;
1944 END IF;
1945
1946 ---------------------------------------------------------------------
1947 -- Total received quantity against the distribution
1948 -- = Quantity delivered + allocated quantity
1949 ---------------------------------------------------------------------
1950 l_qty_received := l_qty_received + l_allocated_qty;
1951
1952
1953 x_accrual_rec.quantity_received := l_qty_received;
1954
1955 ---------------------------------------------------------------------
1956 -- Currency_conversion_rate for match to receipt cases is weighted
1957 -- average of the currency_conversion_rate for all the txns
1958 ---------------------------------------------------------------------
1959 l_stmt_num := 223;
1960 IF (l_allocated_qty > 0) THEN
1961 x_accrual_rec.currency_conversion_rate := (l_sum_func_amount +
1962 (l_sum_allocate_amount / (g_shipment_net_qty_received - g_shipment_net_qty_delivered) * l_allocated_qty) ) / l_qty_received;
1963 ELSIF (l_qty_received > 0) THEN
1964 x_accrual_rec.currency_conversion_rate := l_sum_func_amount / l_qty_received;
1965 END IF;
1966
1967 IF (x_accrual_rec.currency_conversion_rate IS NULL
1968 OR x_accrual_rec.currency_conversion_rate <= 0) THEN
1969 x_accrual_rec.currency_conversion_rate := 1;
1970 END IF;
1971
1972 END IF;
1973
1974 l_stmt_num := 230;
1975 -----------------------------------------------------------------
1976 -- Calculate the accrual amount
1977 -----------------------------------------------------------------
1978 IF (x_accrual_rec.quantity_received <= x_accrual_rec.quantity_billed) THEN
1979 x_accrual_rec.accrual_quantity := 0;
1980 ELSE
1981 x_accrual_rec.accrual_quantity := x_accrual_rec.quantity_received - x_accrual_rec.quantity_billed;
1982 END IF;
1983
1984 x_accrual_rec.accrual_amount := x_accrual_rec.accrual_quantity * x_accrual_rec.unit_price;
1985
1986 -------------------------------------------------------------------------
1987 -- Calculate the encumbrance amount
1988 -- We should only encumber upto quantity ordered. If quantity received is
1989 -- greater than quantity ordered, we should not encumber for the excess.
1990 -------------------------------------------------------------------------
1991 IF (x_accrual_rec.quantity_received <= p_dist_qty) THEN
1992 x_accrual_rec.encum_quantity := x_accrual_rec.accrual_quantity;
1993 ELSE
1994 x_accrual_rec.encum_quantity := x_accrual_rec.accrual_quantity
1995 - (x_accrual_rec.quantity_received - p_dist_qty);
1996 END IF;
1997 x_accrual_rec.encum_amount := x_accrual_rec.encum_quantity * x_accrual_rec.unit_price;
1998
1999 -- Procedure level log message for exit point
2000 IF (l_pLog) THEN
2001 FND_LOG.STRING(
2002 FND_LOG.LEVEL_PROCEDURE,
2003 l_module || '.end',
2004 'Calculate_AccrualAmount >> ' ||
2005 'quantity_received = ' || x_accrual_rec.quantity_received ||','||
2006 'quantity_billed = ' || x_accrual_rec.quantity_billed ||','||
2007 'accrual_quantity = ' || x_accrual_rec.accrual_quantity ||','||
2008 'encum_quantity = ' || x_accrual_rec.encum_quantity ||','||
2009 'accrual_amount = ' || x_accrual_rec.accrual_amount ||','||
2010 'encum_amount = ' || x_accrual_rec.encum_amount
2011 );
2012 END IF;
2013
2014 -- Get message count and if 1, return message data.
2015 FND_MSG_PUB.Count_And_Get
2016 ( p_count => x_msg_count,
2017 p_data => x_msg_data
2018 );
2019
2020 EXCEPTION
2021
2022 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2023
2024 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2025
2026 IF (l_exceptionLog) THEN
2027 FND_LOG.STRING(
2028 FND_LOG.LEVEL_EXCEPTION,
2029 l_module || '.' || l_stmt_num,
2030 l_msg_data
2031 );
2032 END IF;
2033
2034 FND_MSG_PUB.Count_And_Get
2035 ( p_count => x_msg_count,
2036 p_data => x_msg_data
2037 );
2038
2039 WHEN OTHERS THEN
2040
2041 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2042
2043 IF (l_uLog) THEN
2044 FND_LOG.STRING(
2045 FND_LOG.LEVEL_UNEXPECTED,
2046 l_module || '.' || l_stmt_num,
2047 SQLERRM
2048 );
2049 END IF;
2050
2051 IF FND_MSG_PUB.Check_Msg_Level
2052 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2053 THEN
2054 FND_MSG_PUB.Add_Exc_Msg
2055 ( G_PKG_NAME,
2056 l_api_name,
2057 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
2058 );
2059 END IF;
2060
2061 FND_MSG_PUB.Count_And_Get
2062 ( p_count => x_msg_count,
2063 p_data => x_msg_data
2064 );
2065
2066 END Calculate_AccrualAmount;
2067
2068 -----------------------------------------------------------------------------
2069 -- PROCEDURE : Calculate_AccrualAmount
2070 -- DESCRIPTION : Procedure for PAC period end accrual process.
2071 -- The procedure will return accrual and encum quantities only
2072 -----------------------------------------------------------------------------
2073 PROCEDURE Calculate_AccrualAmount
2074 (
2075 p_api_version IN NUMBER,
2076 p_init_msg_list IN VARCHAR2,
2077 p_validation_level IN NUMBER,
2078 x_return_status OUT NOCOPY VARCHAR2,
2079 x_msg_count OUT NOCOPY NUMBER,
2080 x_msg_data OUT NOCOPY VARCHAR2,
2081
2082 p_match_option IN VARCHAR2,
2083 p_distribution_id IN NUMBER,
2084 p_shipment_id IN NUMBER,
2085 p_transaction_id IN NUMBER,
2086 p_service_flag IN NUMBER,
2087 p_dist_qty IN NUMBER,
2088 p_shipment_qty IN NUMBER,
2089 p_end_date IN DATE,
2090
2091 x_accrual_qty OUT NOCOPY NUMBER,
2092 x_encum_qty OUT NOCOPY NUMBER
2093 )
2094
2095 IS
2096 l_api_name CONSTANT VARCHAR2(30) :='Calculate_AccrualAmount';
2097 l_api_version CONSTANT NUMBER := 1.0;
2098 l_return_status VARCHAR2(1);
2099
2100 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2101 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
2102
2103 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
2104 l_exceptionLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_EXCEPTION >= G_LOG_LEVEL);
2105 l_pLog CONSTANT BOOLEAN := l_exceptionLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
2106 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
2107
2108 l_stmt_num NUMBER;
2109 l_msg_data VARCHAR2(240);
2110 l_accrual_rec CST_PER_END_ACCRUALS_TEMP%ROWTYPE;
2111
2112 BEGIN
2113
2114 l_stmt_num := 0;
2115
2116 -- Procedure level log message for Entry point
2117 IF (l_pLog) THEN
2118 FND_LOG.STRING(
2119 FND_LOG.LEVEL_PROCEDURE,
2120 l_module || '.begin',
2121 'Calculate_AccrualAmount <<' ||
2122 'p_match_option = ' || p_match_option ||','||
2123 'p_distribution_id = ' || p_distribution_id ||','||
2124 'p_shipment_id = ' || p_shipment_id ||','||
2125 'p_transaction_id = ' || p_transaction_id ||','||
2126 'p_service_flag = ' || p_service_flag ||','||
2127 'p_dist_qty = ' || p_dist_qty ||','||
2128 'p_shipment_qty = ' || p_shipment_qty ||','||
2129 'p_end_date = ' || p_end_date
2130 );
2131 END IF;
2132
2133 -- Standard call to check for call compatibility.
2134 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2135 p_api_version,
2136 l_api_name,
2137 G_PKG_NAME )
2138 THEN
2139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2140 END IF;
2141
2142 -- Initialize message list if p_init_msg_list is set to TRUE.
2143 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2144 FND_MSG_PUB.initialize;
2145 END IF;
2146
2147 -- Initialize API return status to success
2148 x_return_status := FND_API.G_RET_STS_SUCCESS;
2149 l_return_status := FND_API.G_RET_STS_SUCCESS;
2150
2151 -- Prepairing accrual record to call the Calculate_AccrualAmount procedure
2152 l_accrual_rec.shipment_id := p_shipment_id;
2153 l_accrual_rec.distribution_id := p_distribution_id;
2154 l_accrual_rec.match_option := p_match_option;
2155
2156 -- For PAC only accrual quantity will be returned, accrual amount will be
2157 -- calculated in the calling API itself
2158 l_accrual_rec.unit_price := 1;
2159
2160 -----------------------------------------------------------------
2161 -- The procedure Calculate_AccrualAmount calculates the
2162 -- accrual_amount and encum_amount
2163 -----------------------------------------------------------------
2164 l_stmt_num := 10;
2165 Calculate_AccrualAmount(
2166 p_api_version => 1.0,
2167 p_init_msg_list => FND_API.G_FALSE,
2168 p_validation_level => FND_API.G_VALID_LEVEL_FULL,
2169 x_return_status => l_return_status,
2170 x_msg_count => x_msg_count,
2171 x_msg_data => x_msg_data,
2172 p_service_flag => p_service_flag,
2173 p_dist_qty => p_dist_qty,
2174 p_shipment_qty => p_shipment_qty,
2175 p_end_date => p_end_date,
2176 p_transaction_id => p_transaction_id,
2177 x_accrual_rec => l_accrual_rec
2178 );
2179 -- If return status is not success, raise unexpected exception
2180 IF (l_return_status <> FND_API.G_RET_STS_SUCCESS ) THEN
2181 l_msg_data := 'Failed calculating aacrual amount against the Shipment id :' ||
2182 TO_CHAR(p_shipment_id);
2183 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2184 END IF;
2185
2186 -- Assign the values returned
2187 x_accrual_qty := l_accrual_rec.accrual_quantity;
2188 x_encum_qty := l_accrual_rec.encum_quantity;
2189
2190 -- Procedure level log message for exit point
2191 IF (l_pLog) THEN
2192 FND_LOG.STRING(
2193 FND_LOG.LEVEL_PROCEDURE,
2194 l_module || '.end',
2195 'Calculate_AccrualAmount >>' ||
2196 'x_accrual_qty = ' || x_accrual_qty ||','||
2197 'x_encum_qty = ' || x_encum_qty
2198 );
2199 END IF;
2200
2201 -- Get message count and if 1, return message data.
2202 FND_MSG_PUB.Count_And_Get
2203 ( p_count => x_msg_count,
2204 p_data => x_msg_data
2205 );
2206
2207 EXCEPTION
2208
2209 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2210
2211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2212
2213 IF (l_exceptionLog) THEN
2214 FND_LOG.STRING(
2215 FND_LOG.LEVEL_EXCEPTION,
2216 l_module || '.' || l_stmt_num,
2217 l_msg_data
2218 );
2219 END IF;
2220
2221 FND_MSG_PUB.Count_And_Get
2222 ( p_count => x_msg_count,
2223 p_data => x_msg_data
2224 );
2225
2226 WHEN OTHERS THEN
2227
2228 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2229
2230 IF (l_uLog) THEN
2231 FND_LOG.STRING(
2232 FND_LOG.LEVEL_UNEXPECTED,
2233 l_module || '.' || l_stmt_num,
2234 SQLERRM
2235 );
2236 END IF;
2237
2238 IF FND_MSG_PUB.Check_Msg_Level
2239 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2240 THEN
2241 FND_MSG_PUB.Add_Exc_Msg
2242 ( G_PKG_NAME,
2243 l_api_name,
2244 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
2245 );
2246 END IF;
2247
2248 FND_MSG_PUB.Count_And_Get
2249 ( p_count => x_msg_count,
2250 p_data => x_msg_data
2251 );
2252
2253 END Calculate_AccrualAmount;
2254
2255 -----------------------------------------------------------------------------
2256 -- PROCEDURE : Get_RcvQuantity
2257 -- DESCRIPTION : Returns the Net Quantity Received and net quantity
2258 -- delivered against a Shipment and distribution
2259 -- or against a Receipt
2260 --
2261 -- net_qty_received = Quantity received
2262 -- - return to vendor + corrections
2263 --
2264 -- net_qty_delivered = Quantity delivered
2265 -- - return to receiving + corrections
2266 --
2267 -- The returned value will be in PO's UOM.
2268 -----------------------------------------------------------------------------
2269 PROCEDURE Get_RcvQuantity
2270 (
2271 p_api_version IN NUMBER,
2272 p_init_msg_list IN VARCHAR2,
2273 p_validation_level IN NUMBER,
2274 x_return_status OUT NOCOPY VARCHAR2,
2275 x_msg_count OUT NOCOPY NUMBER,
2276 x_msg_data OUT NOCOPY VARCHAR2,
2277
2278 p_line_location_id IN NUMBER,
2279 p_rcv_shipment_id IN NUMBER,
2280 p_rcv_txn_id IN NUMBER,
2281 p_service_flag IN NUMBER,
2282 p_end_date IN DATE
2283 )
2284
2285 IS
2286 l_api_name CONSTANT VARCHAR2(30) :='Get_RcvQuantity';
2287 l_api_version CONSTANT NUMBER := 1.0;
2288 l_return_status VARCHAR2(1);
2289
2290 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2291 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
2292
2293 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
2294 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
2295 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
2296
2297 l_stmt_num NUMBER;
2298 l_parent_type VARCHAR2(25);
2299 qty_received NUMBER;
2300 qty_delivered NUMBER;
2301
2302 -------------------------------------------------------------------------
2303 -- Cursor for calculating net quantity received and net quantity delivered
2304 -- against shipment
2305 -------------------------------------------------------------------------
2306 CURSOR l_shipment_csr IS
2307 SELECT rt.transaction_id,
2308 rt.po_line_location_id,
2309 rt.po_distribution_id,
2310 rt.transaction_type,
2311 DECODE(p_service_flag,
2312 1, rt.amount,
2313 rt.source_doc_quantity) quantity,
2314 rt.parent_transaction_id
2315 FROM rcv_transactions rt
2316 WHERE rt.transaction_date <= p_end_date
2317 START WITH rt.po_line_location_id = p_line_location_id
2318 AND ((rt.transaction_type = 'RECEIVE' AND rt.parent_transaction_id = -1)
2319 OR
2320 (rt.transaction_type = 'MATCH'))
2321 CONNECT BY rt.parent_transaction_id = PRIOR rt.transaction_id;
2322 /*AND rt.po_line_location_id = PRIOR rt.po_line_location_id;*/
2323 /*Removed the above condition for performance bug 13574635 as its not required and causes N6
2324 index on RT to be picked up which is not efficient,By removing this we can ensure that N1 index is used.*/
2325
2326 -------------------------------------------------------------------------
2327 -- Cursor for calculating net quantity received and net quantity delivered
2328 -- against rcv transaction
2329 -------------------------------------------------------------------------
2330 CURSOR l_rcv_txn_csr IS
2331 SELECT rt.transaction_id,
2332 rt.po_line_location_id,
2333 rt.po_distribution_id,
2334 rt.transaction_type,
2335 DECODE(p_service_flag,
2336 1, rt.amount,
2337 rt.source_doc_quantity) quantity,
2338 rt.parent_transaction_id
2339 FROM rcv_transactions rt
2340 WHERE rt.transaction_date <= p_end_date
2341 START WITH rt.shipment_header_id = p_rcv_shipment_id
2342 AND rt.po_line_location_id = p_line_location_id
2343 AND ((rt.transaction_type = 'RECEIVE' AND rt.parent_transaction_id = -1)
2344 OR
2345 (rt.transaction_type = 'MATCH'))
2346 AND (p_rcv_txn_id IS NULL
2347 OR
2348 (rt.transaction_id = p_rcv_txn_id AND p_rcv_txn_id IS NOT NULL))
2349 CONNECT BY rt.parent_transaction_id = PRIOR rt.transaction_id;
2350 /*AND rt.po_line_location_id = PRIOR rt.po_line_location_id;*/
2351 /*Removed the above condition for performance bug 13574635 as its not required and causes N6
2352 index on RT to be picked up which is not efficient,By removing this we can ensure that N1 index is used.*/
2353
2354
2355 BEGIN
2356
2357 l_stmt_num := 0;
2358 -- Procedure level log message for Entry point
2359 IF (l_pLog) THEN
2360 FND_LOG.STRING(
2361 FND_LOG.LEVEL_PROCEDURE,
2362 l_module || '.begin',
2363 'Get_RcvQuantity << ' ||
2364 'p_line_location_id = ' || p_line_location_id ||','||
2365 'p_rcv_txn_id = ' || p_rcv_txn_id ||','||
2366 'p_rcv_shipment_id = ' || p_rcv_shipment_id ||','||
2367 'p_service_flag = ' || p_service_flag ||','||
2368 'p_end_date = ' || p_end_date
2369 );
2370 END IF;
2371
2372 -- Standard call to check for call compatibility.
2373 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2374 p_api_version,
2375 l_api_name,
2376 G_PKG_NAME )
2377 THEN
2378 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2379 END IF;
2380
2381 -- Initialize message list if p_init_msg_list is set to TRUE.
2382 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2383 FND_MSG_PUB.initialize;
2384 END IF;
2385
2386 -- Initialize API return status to success
2387 x_return_status := FND_API.G_RET_STS_SUCCESS;
2388 l_return_status := FND_API.G_RET_STS_SUCCESS;
2389
2390 -- initialize the variables
2391 g_nqr := 0;
2392 g_nqd := 0;
2393
2394 IF (p_rcv_shipment_id IS NULL) THEN
2395
2396 -- For each child transaction loop
2397 l_stmt_num := 10;
2398 FOR l_shipment_rec IN l_shipment_csr LOOP
2399
2400 qty_received := 0;
2401 qty_delivered := 0;
2402
2403 -- If it is not RECEIVE or MATCH transaction
2404 IF (l_shipment_rec.transaction_type <> 'RECEIVE')
2405 AND (l_shipment_rec.transaction_type <> 'MATCH') THEN
2406
2407 -- Get the parent transaction type
2408 l_stmt_num := 20;
2409 SELECT rt.transaction_type
2410 INTO l_parent_type
2411 FROM rcv_transactions rt
2412 WHERE rt.transaction_id = l_shipment_rec.parent_transaction_id;
2413
2414 END IF;
2415
2416 -----------------------------------------------------------------
2417 -- If it is the parent RECEIVE or MATCH transaction then add the
2418 -- quantity to x_nqr (net_quantity_received)
2419 -----------------------------------------------------------------
2420 l_stmt_num := 30;
2421 IF (l_shipment_rec.transaction_type = 'RECEIVE')
2422 OR (l_shipment_rec.transaction_type = 'MATCH') THEN
2423
2424 qty_received := l_shipment_rec.quantity;
2425
2426 ---------------------------------------------------------------------
2427 -- If it is the parent DELIVER transaction then add the quantity
2428 -- to x_nqd (net_quantity_delivered)
2429 ---------------------------------------------------------------------
2430 ELSIF (l_shipment_rec.transaction_type = 'DELIVER') THEN
2431
2432 qty_delivered := l_shipment_rec.quantity;
2433
2434 -----------------------------------------------------------------
2435 -- If the transaction is CORRECT :
2436 -- If parent is RECEIVE or MATCH transaction then add the corrected qty to x_nqr,
2437 -- If parent is RETURN TO VENDOR then subtract the corrected qty from x_nqr,
2438 -- If parent is DELIVER then add corrected qty to x_nqd,
2439 -- If parent is RETURN TO RECEIVING then subtract the corrected qty from x_nqd
2440 -----------------------------------------------------------------
2441 ELSIF (l_shipment_rec.transaction_type = 'CORRECT') THEN
2442
2443 IF (l_parent_type = 'RECEIVE' OR l_parent_type = 'MATCH') THEN
2444 qty_received := l_shipment_rec.quantity;
2445
2446 ELSIF (l_parent_type = 'RETURN TO VENDOR') THEN
2447 qty_received := -1 * l_shipment_rec.quantity;
2448
2449 ELSIF (l_parent_type = 'DELIVER') THEN
2450 qty_delivered := l_shipment_rec.quantity;
2451
2452 ELSIF (l_parent_type = 'RETURN TO RECEIVING') THEN
2453 qty_delivered := -1 * l_shipment_rec.quantity;
2454
2455 END IF;
2456
2457 -----------------------------------------------------------------
2458 -- If transaction is RETURN TO VENDOR transaction, then subtract
2459 -- returned qty from net_quantity_received
2460 -----------------------------------------------------------------
2461 ELSIF (l_shipment_rec.transaction_type = 'RETURN TO VENDOR') THEN
2462 qty_received := -1 * l_shipment_rec.quantity;
2463
2464 -----------------------------------------------------------------
2465 -- If transaction is RETURN TO RECEIVING transaction, then subtract
2466 -- returned qty from net_quantity_delivered
2467 -----------------------------------------------------------------
2468 ELSIF (l_shipment_rec.transaction_type = 'RETURN TO RECEIVING') THEN
2469 qty_delivered := -1 * l_shipment_rec.quantity;
2470
2471 END IF;
2472
2473 -- Sum of net_quantity_received
2474 g_nqr := g_nqr + qty_received;
2475
2476 -- Sum of net_quantity_delivered
2477 g_nqd := g_nqd + qty_delivered;
2478
2479 -----------------------------------------------------------------
2480 -- Get net_quantity_delivered against each po_distributions
2481 -----------------------------------------------------------------
2482 IF (l_shipment_rec.po_distribution_id IS NOT NULL) THEN
2483 g_dist_nqd_tbl(l_shipment_rec.po_distribution_id)
2484 := g_dist_nqd_tbl(l_shipment_rec.po_distribution_id) + qty_delivered;
2485 END IF;
2486 END LOOP;
2487
2488 ELSE
2489
2490 -- For each child transaction loop
2491 l_stmt_num := 40;
2492 FOR l_txn_rec IN l_rcv_txn_csr LOOP
2493
2494 qty_received := 0;
2495 qty_delivered := 0;
2496
2497 -- If it is not RECEIVE or MATCH transaction
2498 IF (l_txn_rec.transaction_type <> 'RECEIVE')
2499 AND (l_txn_rec.transaction_type <> 'MATCH') THEN
2500
2501 -- Get the parent transaction type
2502 l_stmt_num := 50;
2503 SELECT rt.transaction_type
2504 INTO l_parent_type
2505 FROM rcv_transactions rt
2506 WHERE rt.transaction_id = l_txn_rec.parent_transaction_id;
2507
2508 END IF;
2509
2510 -----------------------------------------------------------------
2511 -- If it is the parent RECEIVE or MATCH transaction then add the
2512 -- quantity to x_nqr (net_quantity_received)
2513 -----------------------------------------------------------------
2514 l_stmt_num := 60;
2515 IF (l_txn_rec.transaction_type = 'RECEIVE')
2516 OR (l_txn_rec.transaction_type = 'MATCH') THEN
2517
2518 qty_received := l_txn_rec.quantity;
2519
2520 ---------------------------------------------------------------------
2521 -- If it is the parent DELIVER transaction then add the quantity
2522 -- to x_nqd (net_quantity_delivered)
2523 ---------------------------------------------------------------------
2524 ELSIF (l_txn_rec.transaction_type = 'DELIVER') THEN
2525
2526 qty_delivered := l_txn_rec.quantity;
2527
2528 -----------------------------------------------------------------
2529 -- If the transaction is CORRECT :
2530 -- If parent is RECEIVE or MATCH transaction then add the corrected qty to x_nqr,
2531 -- If parent is RETURN TO VENDOR then subtract the corrected qty from x_nqr,
2532 -- If parent is DELIVER then add corrected qty to x_nqd,
2533 -- If parent is RETURN TO RECEIVING then subtract the corrected qty from x_nqd
2534 -----------------------------------------------------------------
2535 ELSIF (l_txn_rec.transaction_type = 'CORRECT') THEN
2536
2537 IF (l_parent_type = 'RECEIVE' OR l_parent_type = 'MATCH') THEN
2538 qty_received := l_txn_rec.quantity;
2539
2540 ELSIF (l_parent_type = 'RETURN TO VENDOR') THEN
2541 qty_received := -1 * l_txn_rec.quantity;
2542
2543 ELSIF (l_parent_type = 'DELIVER') THEN
2544 qty_delivered := l_txn_rec.quantity;
2545
2546 ELSIF (l_parent_type = 'RETURN TO RECEIVING') THEN
2547 qty_delivered := -1 * l_txn_rec.quantity;
2548
2549 END IF;
2550
2551 -----------------------------------------------------------------
2552 -- If transaction is RETURN TO VENDOR transaction, then subtract
2553 -- returned qty from net_quantity_received
2554 -----------------------------------------------------------------
2555 ELSIF (l_txn_rec.transaction_type = 'RETURN TO VENDOR') THEN
2556 qty_received := -1 * l_txn_rec.quantity;
2557
2558 -----------------------------------------------------------------
2559 -- If transaction is RETURN TO RECEIVING transaction, then subtract
2560 -- returned qty from net_quantity_delivered
2561 -----------------------------------------------------------------
2562 ELSIF (l_txn_rec.transaction_type = 'RETURN TO RECEIVING') THEN
2563 qty_delivered := -1 * l_txn_rec.quantity;
2564
2565 END IF;
2566
2567 g_nqr := g_nqr + qty_received;
2568 g_nqd := g_nqd + qty_delivered;
2569
2570 -----------------------------------------------------------------
2571 -- Get net_quantity_delivered against each po_distributions
2572 -----------------------------------------------------------------
2573 IF (l_txn_rec.po_distribution_id IS NOT NULL) THEN
2574 g_dist_nqd_tbl(l_txn_rec.po_distribution_id)
2575 := g_dist_nqd_tbl(l_txn_rec.po_distribution_id) + qty_delivered;
2576
2577 END IF;
2578
2579 END LOOP;
2580
2581 END IF;
2582
2583 -- Procedure level log message for exit point
2584 IF (l_pLog) THEN
2585 FND_LOG.STRING(
2586 FND_LOG.LEVEL_PROCEDURE,
2587 l_module || '.end',
2588 'Get_RcvQuantity >> ' ||
2589 'g_nqr = ' || g_nqr ||','||
2590 'g_nqd = ' || g_nqd
2591 );
2592 END IF;
2593
2594 -- Get message count and if 1, return message data.
2595 FND_MSG_PUB.Count_And_Get
2596 ( p_count => x_msg_count,
2597 p_data => x_msg_data
2598 );
2599
2600 EXCEPTION
2601
2602 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2603
2604 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2605
2606 FND_MSG_PUB.Count_And_Get
2607 ( p_count => x_msg_count,
2608 p_data => x_msg_data
2609 );
2610
2611 WHEN OTHERS THEN
2612
2613 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2614
2615 IF (l_uLog) THEN
2616 FND_LOG.STRING(
2617 FND_LOG.LEVEL_UNEXPECTED,
2618 l_module || '.' || l_stmt_num,
2619 SQLERRM
2620 );
2621 END IF;
2622
2623 IF FND_MSG_PUB.Check_Msg_Level
2624 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2625 THEN
2626 FND_MSG_PUB.Add_Exc_Msg
2627 ( G_PKG_NAME,
2628 l_api_name,
2629 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
2630 );
2631 END IF;
2632
2633 FND_MSG_PUB.Count_And_Get
2634 ( p_count => x_msg_count,
2635 p_data => x_msg_data
2636 );
2637
2638 END Get_RcvQuantity;
2639
2640 -----------------------------------------------------------------------------
2641 -- PROCEDURE : Get_InvoiceQuantity
2642 -- DESCRIPTION : Returns quantity invoiced against the distribution or
2643 -- the receipt.
2644 -----------------------------------------------------------------------------
2645 PROCEDURE Get_InvoiceQuantity
2646 (
2647 p_api_version IN NUMBER,
2648 p_init_msg_list IN VARCHAR2,
2649 p_validation_level IN NUMBER,
2650 x_return_status OUT NOCOPY VARCHAR2,
2651 x_msg_count OUT NOCOPY NUMBER,
2652 x_msg_data OUT NOCOPY VARCHAR2,
2653
2654 p_match_option IN VARCHAR2,
2655 p_dist_id IN NUMBER,
2656 p_rcv_txn_id IN NUMBER,
2657 p_service_flag IN NUMBER,
2658 p_end_date IN DATE,
2659 x_quantity_invoiced OUT NOCOPY NUMBER
2660 )
2661
2662 IS
2663 l_api_name CONSTANT VARCHAR2(30) :='Get_InvoiceQuantity';
2664 l_api_version CONSTANT NUMBER := 1.0;
2665 l_return_status VARCHAR2(1);
2666
2667 l_full_name CONSTANT VARCHAR2(60) := G_PKG_NAME || '.' || l_api_name;
2668 l_module CONSTANT VARCHAR2(60) := 'cst.plsql.'||l_full_name;
2669
2670 l_uLog CONSTANT BOOLEAN := FND_LOG.TEST(FND_LOG.LEVEL_UNEXPECTED, l_module) AND (FND_LOG.LEVEL_UNEXPECTED >= G_LOG_LEVEL);
2671 l_pLog CONSTANT BOOLEAN := l_uLog AND (FND_LOG.LEVEL_PROCEDURE >= G_LOG_LEVEL);
2672 l_sLog CONSTANT BOOLEAN := l_pLog AND (FND_LOG.LEVEL_STATEMENT >= G_LOG_LEVEL);
2673
2674 l_quantity_invoiced NUMBER;
2675 l_txn_to_po_rate NUMBER;
2676 l_stmt_num NUMBER;
2677
2678 BEGIN
2679
2680 l_stmt_num := 0;
2681 -- Procedure level log message for Entry point
2682 IF (l_pLog) THEN
2683 FND_LOG.STRING(
2684 FND_LOG.LEVEL_PROCEDURE,
2685 l_module || '.begin',
2686 'Get_InvoiceQuantity <<' ||
2687 'p_match_option = ' || p_match_option ||','||
2688 'p_dist_id = ' || p_dist_id ||','||
2689 'p_rcv_txn_id = ' || p_rcv_txn_id ||','||
2690 'p_service_flag = ' || p_service_flag ||','||
2691 'p_end_date = ' || p_end_date
2692 );
2693 END IF;
2694
2695 -- Standard call to check for call compatibility.
2696 IF NOT FND_API.Compatible_API_Call ( l_api_version,
2697 p_api_version,
2698 l_api_name,
2699 G_PKG_NAME )
2700 THEN
2701 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
2702 END IF;
2703
2704 -- Initialize message list if p_init_msg_list is set to TRUE.
2705 IF FND_API.to_Boolean( p_init_msg_list ) THEN
2706 FND_MSG_PUB.initialize;
2707 END IF;
2708
2709 -- Initialize API return status to success
2710 x_return_status := FND_API.G_RET_STS_SUCCESS;
2711 l_return_status := FND_API.G_RET_STS_SUCCESS;
2712
2713 -------------------------------------------------------------------------
2714 -- If Match option is Match to PO
2715 -------------------------------------------------------------------------
2716 l_stmt_num := 10;
2717 IF (p_match_option = 'P') THEN
2718
2719 SELECT NVL(DECODE(p_service_flag,
2720 1, SUM(aida.amount),
2721 SUM(decode(aida.corrected_invoice_dist_id,
2722 null,aida.quantity_invoiced,
2723 nvl(aida.corrected_quantity,aida.quantity_invoiced))
2724 )),
2725 0)
2726 INTO l_quantity_invoiced
2727 FROM ap_invoice_distributions_all aida,
2728 ap_invoices_all aia
2729 WHERE aida.po_distribution_id = p_dist_id
2730 AND aida.line_type_lookup_code NOT IN ('IPV', 'ERV', 'PREPAY','REC_TAX')
2731 AND aida.accounting_date <= p_end_date
2732 AND aida.posted_flag = 'Y'
2733 AND aia.invoice_id = aida.invoice_id
2734 AND aia.invoice_type_lookup_code <> 'PREPAYMENT';
2735
2736 -------------------------------------------------------------------------
2737 -- If Match option is Match to Receipt
2738 -------------------------------------------------------------------------
2739 l_stmt_num := 20;
2740 ELSIF (p_match_option = 'R') THEN
2741
2742 SELECT NVL(DECODE(p_service_flag,
2743 1, SUM(aida.amount),
2744 SUM(decode(aida.corrected_invoice_dist_id,
2745 null,aida.quantity_invoiced,
2746 nvl(aida.corrected_quantity,aida.quantity_invoiced))
2747 * DECODE(pol.item_id,null,1,inv_convert.inv_um_convert(pol.item_id,
2748 10,
2749 NULL,
2750 NULL,
2751 NULL,
2752 aida.matched_uom_lookup_code,
2753 NVL(pol.unit_meas_lookup_code, poll.unit_meas_lookup_code))))),
2754 0)
2755 INTO l_quantity_invoiced
2756 FROM ap_invoice_distributions_all aida,
2757 ap_invoices_all aia,
2758 po_lines_all pol,
2759 po_line_locations_all poll,
2760 po_distributions_all pod
2761 WHERE aida.po_distribution_id = pod.po_distribution_id
2762 AND (p_rcv_txn_id IS NULL OR aida.rcv_transaction_id = p_rcv_txn_id)
2763 AND aida.line_type_lookup_code NOT IN ('IPV', 'ERV', 'PREPAY','REC_TAX')
2764 AND aida.accounting_date <= p_end_date
2765 AND aia.invoice_id = aida.invoice_id
2766 AND aia.invoice_type_lookup_code <> 'PREPAYMENT'
2767 AND aida.posted_flag = 'Y'
2768 AND pod.po_distribution_id = p_dist_id
2769 AND pol.po_line_id = poll.po_line_id
2770 AND poll.line_location_id = pod.line_location_id;
2771
2772 END IF;
2773
2774 -------------------------------------------------------------------------
2775 -- If the user is weird in his invoice reversals (dating them before the
2776 -- invoice itself and then accruing between them) it is possible for the
2777 -- quantity invoiced to be negative. This would improperly increase the
2778 -- accrual amount.
2779 -------------------------------------------------------------------------
2780 IF (l_quantity_invoiced < 0) THEN
2781 l_quantity_invoiced := 0;
2782 END IF;
2783
2784 x_quantity_invoiced := l_quantity_invoiced;
2785
2786 -- Procedure level log message for exit point
2787 IF (l_pLog) THEN
2788 FND_LOG.STRING(
2789 FND_LOG.LEVEL_PROCEDURE,
2790 l_module || '.end',
2791 'Get_InvoiceQuantity >> ' ||
2792 'x_quantity_invoiced = ' || x_quantity_invoiced
2793 );
2794 END IF;
2795
2796 -- Get message count and if 1, return message data.
2797 FND_MSG_PUB.Count_And_Get
2798 ( p_count => x_msg_count,
2799 p_data => x_msg_data
2800 );
2801
2802 EXCEPTION
2803
2804 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2805
2806 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2807
2808 FND_MSG_PUB.Count_And_Get
2809 ( p_count => x_msg_count,
2810 p_data => x_msg_data
2811 );
2812
2813 WHEN OTHERS THEN
2814 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
2815
2816 IF (l_uLog) THEN
2817 FND_LOG.STRING(
2818 FND_LOG.LEVEL_UNEXPECTED,
2819 l_module || '.' || l_stmt_num,
2820 SQLERRM
2821 );
2822 END IF;
2823
2824 IF FND_MSG_PUB.Check_Msg_Level
2825 (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
2826 THEN
2827 FND_MSG_PUB.Add_Exc_Msg
2828 ( G_PKG_NAME,
2829 l_api_name,
2830 '(' || TO_CHAR(l_stmt_num) || ') : ' || SUBSTRB (SQLERRM , 1 , 230)
2831 );
2832 END IF;
2833
2834 FND_MSG_PUB.Count_And_Get
2835 ( p_count => x_msg_count,
2836 p_data => x_msg_data
2837 );
2838
2839 END Get_InvoiceQuantity;
2840
2841 END CST_PerEndAccruals_PVT; -- end package body