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