[Home] [Help]
PACKAGE BODY: APPS.CSTPPACQ
Source
1 PACKAGE BODY CSTPPACQ AS
2 /* $Header: CSTPACQB.pls 120.22.12010000.7 2008/11/29 02:34:22 anjha ship $ */
3
4 /* define the Global variable for Debug */
5
6 G_DEBUG CONSTANT VARCHAR2(1) := NVL(fnd_profile.value('MRP_DEBUG'),'N');
7
8 PROCEDURE acq_cost_processor(
9 i_period IN NUMBER,
10 i_start_date IN DATE,
11 i_end_date IN DATE,
12 i_cost_type_id IN NUMBER,
13 i_cost_group_id IN NUMBER,
14 i_user_id IN NUMBER,
15 i_login_id IN NUMBER,
16 i_req_id IN NUMBER,
17 i_prog_id IN NUMBER,
18 i_prog_appl_id IN NUMBER,
19 o_err_num OUT NOCOPY NUMBER,
20 o_err_code OUT NOCOPY VARCHAR2,
21 o_err_msg OUT NOCOPY VARCHAR2,
22 i_source_flag IN NUMBER, --DEFAULT 1
23 i_receipt_no IN NUMBER, --DEFAULT NULL
24 i_invoice_no IN NUMBER, --DEFAULT NULL
25 i_adj_account IN NUMBER ) --DEFAULT NULL
26 IS
27
28 ---------------------------------------------------------------
29 -- 1.0 Get all receipts that
30 -- took place in the period and cost type
31 -- belong to org of the cost group passed in
32 -- transaction_type = 'RECEIVE' and parent_transaction_id = -1
33 -- indicates a RECEIVE transaction
34 -- transaction_type = 'MATCH' indicates a match to UNORDERED receipt
35 -- and is equivalent to a RECEIVE transaction
36 --
37 -- FP BUG 5845861 fix: dropship type_code means
38 -- 1 External Drop Shpmnt, Shpmnt Txn flow has new accounting flag checked.
39 -- 2 External Drop Shpmnt, Shpmnt Txn flow does not have new accounting flag check
40 -- 3 Not a Drop Shpmnt
41 -- So exclude only 1 while picking receipts for dropshipment scenarios
42 -- BUG 6748898 FP:11I10-12.0 c_receipts cursor split into separate cursors for
43 -- c_receipts_source_flag_1 for periodic acquisition cost processor
44 -- c_receipts_source_flag_2 for periodic acquisition cost adjustment processor
45 ---------------------------------------------------------------------------------
46 /* this select has to be executed only when i_source_flag=1,
47 periodic acquisition cost processor */
48 CURSOR c_receipts_source_flag_1 (l_start_date IN DATE,
49 l_end_date IN DATE,
50 i_receipt_no IN NUMBER,
51 i_invoice_no IN NUMBER) IS
52 (SELECT
53 distinct rt.transaction_id ,
54 nvl(poll.lcm_flag,'N') lcm_flag
55 FROM
56 rcv_transactions rt,
57 po_line_locations_all poll, -- Added for Complex work Procurement
58 cst_cost_group_assignments ccga1
59 WHERE rt.transaction_date BETWEEN i_start_date and i_end_date AND
60 -- Added for Complex work Procurement
61 rt.po_line_id = poll.po_line_id AND
62 rt.po_line_location_id = poll.line_location_id AND
63 poll.shipment_type <> 'PREPAYMENT' AND
64 ccga1.cost_group_id = i_cost_group_id AND
65 rt.organization_id = ccga1.organization_id AND
66 rt.source_document_code = 'PO' AND
67 NVL(rt.consigned_flag,'N') = 'N' AND
68 NVL(rt.dropship_type_code,3) <> 1 AND -- FP bug 5845861 fix
69 ( ( rt.parent_transaction_id = -1 AND
70 rt.transaction_type = 'RECEIVE'
71 )
72 OR
73 ( transaction_type = 'MATCH')
74 )
75 );
76
77 -----------------------------------------------------------------------------
78 -- 1.0 Get all receipts that
79 -- took place in the period and cost type
80 -- belong to org of the cost group passed in
81 -- transaction_type = 'RECEIVE' and parent_transaction_id = -1
82 -- indicates a RECEIVE transaction
83 -- transaction_type = 'MATCH' indicates a match to UNORDERED receipt
84 -- and is equivalent to a RECEIVE transaction
85 --
86 -- Bug 5563311: The dropship_type code means
87 -- 1 External Drop Shpmnt, Shpmnt Trxn Flow has new accounting flag checked.
88 -- 2 External Drop Shpmnt, Shpmnt Trxn Flow does not have new accounting flag checked.
89 -- 3 Not a Drop Shpmnt.
90 -- So exclude only 1 while picking the rcpts for dropshipment scenarios
91 -- Bug 6748898 fix: c_receipts cursor split into separate cursors for
92 -- c_receipts_source_flag_1 for periodic acquisition cost processor
93 -- c_receipts_source_flag_2 for periodic acquisition cost adjustment processor
94 --
95 -- FP Bug 7336698 fix: Hint OPTIMIZER_FEATURES_ENABLE('9.0.1') added
96 -------------------------------------------------------------------------------
97 /* This cursor has to be executed only when i_source_flag is 2 for
98 periodic acquisition cost adjustment processor
99 */
100 CURSOR c_receipts_source_flag_2 (l_start_date IN DATE,
101 l_end_date IN DATE,
102 i_receipt_no IN NUMBER,
103 i_invoice_no IN NUMBER) IS
104 (Select /*+ OPTIMIZER_FEATURES_ENABLE('9.0.1') */
105 distinct aida.rcv_transaction_id transaction_id
106 from ap_invoice_distributions_all aida
107 WHERE aida.rcv_transaction_id is not null
108 and i_invoice_no IS NULL
109 and i_receipt_no is NULL
110 and exists (select 1 from rcv_transactions rt,
111 po_line_locations_all poll, -- Added for Complex work Procurement
112 cst_cost_group_assignments ccga
113 where rt.transaction_id = aida.rcv_transaction_id
114 -- Added for Complex work Procurement
115 and rt.po_line_id = poll.po_line_id
116 and rt.po_line_location_id = poll.line_location_id
117 and nvl(poll.lcm_flag,'N') = 'N'
118 and poll.shipment_type <> 'PREPAYMENT'
119 and rt.transaction_date < l_start_date
120 AND ccga.cost_group_id = i_cost_group_id
121 AND rt.organization_id = ccga.organization_id
122 AND rt.source_document_code = 'PO'
123 AND NVL(rt.consigned_flag,'N') = 'N'
124 AND NVL(rt.dropship_type_code,3) = 3 --dropshipement project
125 AND ( ( rt.parent_transaction_id = -1
126 AND rt.transaction_type = 'RECEIVE')
127 OR
128 ( rt.transaction_type = 'MATCH'))
129 )
130 AND NOT EXISTS ( SELECT 1
131 FROM RCV_TRANSACTIONS RT,
132 PO_DISTRIBUTIONS_ALL POD
133 WHERE RT.TRANSACTION_ID = AIDA.RCV_TRANSACTION_ID
134 AND POD.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID
135 AND POD.DESTINATION_TYPE_CODE = 'EXPENSE'
136 )
137 /*bug 5044215/5264793.Only pick up receipts that have delivers */
138 AND EXISTS (Select 1
139 from rcv_transactions rt2
140 where rt2.transaction_type in ('DELIVER')
141 START WITH rt2.transaction_id = aida.rcv_transaction_id
142 CONNECT BY
143 prior rt2.transaction_id = rt2.parent_transaction_id
144 )
145 /* Invoice Lines Project
146 Removing reference to ap_chrg_allocations_all
147 */
148 and EXISTS(
149 SELECT 1 FROM ap_invoice_distributions_all aida2
150 WHERE aida.invoice_distribution_id = nvl(aida2.charge_applicable_to_dist_id, aida.invoice_distribution_id)
151 AND (aida2.accounting_date between l_start_date and l_end_date)
152 AND aida2.posted_flag = 'Y'
153 AND aida2.line_type_lookup_code <> 'REC_TAX'
154 )
155 and NOT EXISTS (SELECT 1 FROM rcv_transactions rt,rcv_accounting_events rae --add for dropshipment
156 WHERE rt.transaction_id = aida.rcv_transaction_id
157 AND rae.rcv_transaction_id = rt.transaction_id
158 AND rae.event_type_id = 1 -- RECEIVE
159 AND rae.trx_flow_header_id is not NULL)
160 UNION
161 select distinct rcv_transaction_id from ap_invoice_distributions_all aida
162 where ((aida.accounting_date between l_start_date and l_end_date))
163 and aida.posted_flag = 'Y'
164 /* Invoice Lines Project, TAX is now REC_TAX and NONREC_TAX */
165 and aida.line_type_lookup_code <> 'REC_TAX'
166 and aida.rcv_transaction_id is NOT NULL
167 and i_receipt_no IS NULL
168 and i_invoice_no IS NULL
169 and exists (select 1 from rcv_transactions rt,
170 po_line_locations_all poll, -- Added for Complex work Procurement
171 cst_cost_group_assignments ccga
172 where rt.transaction_id = aida.rcv_transaction_id
173 -- Added for Complex work Procurement
174 and rt.po_line_id = poll.line_location_id
175 and rt.po_line_location_id = poll.line_location_id
176 and nvl(poll.lcm_flag,'N') = 'N'
177 and poll.shipment_type <> 'PREPAYMENT'
178 and rt.transaction_date < l_start_date
179 AND ccga.cost_group_id = i_cost_group_id
180 AND rt.organization_id = ccga.organization_id
181 AND rt.source_document_code = 'PO'
182 AND NVL(rt.consigned_flag,'N') = 'N'
183 AND NVL(rt.dropship_type_code,3) = 3 --dropshipment project
184 AND ( ( rt.parent_transaction_id = -1
185 AND rt.transaction_type = 'RECEIVE')
186 OR
187 ( rt.transaction_type = 'MATCH'))
188 )
189 AND NOT EXISTS ( SELECT 1
190 FROM RCV_TRANSACTIONS RT,
191 PO_DISTRIBUTIONS_ALL POD
192 WHERE RT.TRANSACTION_ID = AIDA.RCV_TRANSACTION_ID
193 AND POD.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID
194 AND POD.DESTINATION_TYPE_CODE = 'EXPENSE' )
195 /*bug 5044215/5264793.Only pick up receipts that have delivers */
196 AND EXISTS (Select 1
197 from rcv_transactions rt2
198 where rt2.transaction_type in ('DELIVER')
199 START WITH rt2.transaction_id = aida.rcv_transaction_id
200 CONNECT BY
201 prior rt2.transaction_id = rt2.parent_transaction_id
202 )
203 and NOT EXISTS (SELECT 1 FROM rcv_transactions rt,rcv_accounting_events rae --add for dropshipment
204 WHERE rt.transaction_id = aida.rcv_transaction_id
205 AND rae.rcv_transaction_id = rt.transaction_id
206 AND rae.event_type_id = 1 -- RECEIVE
207 AND rae.trx_flow_header_id is not NULL)
208 )
209 UNION
210 ( select i_receipt_no from dual
211 where i_receipt_no IS NOT NULL
212 /*bug 5044215/5264793.Only pick up receipts that have delivers */
213 AND EXISTS (Select 1
214 from rcv_transactions rt2
215 where rt2.transaction_type in ('DELIVER')
216 START WITH rt2.transaction_id = i_receipt_no
217 CONNECT BY
218 prior rt2.transaction_id = rt2.parent_transaction_id
219 )
220 AND EXISTS ( Select 'not lcm enabled'
221 FROM rcv_transactions rt,
222 po_line_locations_all poll
223 WHERE rt.transaction_id = i_receipt_no
224 AND rt.po_line_location_id = poll.line_location_id
225 AND nvl(poll.lcm_flag,'N') = 'N'
226
227 )
228 )
229
230 UNION
231 ( select distinct rcv_transaction_id from ap_invoice_distributions_all aida
232 where aida.invoice_id = i_invoice_no
233 and i_invoice_no IS NOT NULL
234 and aida.rcv_transaction_id IS NOT NULL
235 and aida.line_type_lookup_code = 'ITEM'
236 and NOT EXISTS (SELECT 1 FROM rcv_transactions rt,rcv_accounting_events rae --add for dropshipment
237 WHERE rt.transaction_id = aida.rcv_transaction_id
238 AND rae.rcv_transaction_id = rt.transaction_id
239 AND rae.event_type_id = 1 -- RECEIVE
240 AND rae.trx_flow_header_id is not NULL)
241 AND NOT EXISTS ( SELECT 1
242 FROM rcv_transactions rt,
243 po_distributions_all pod,
244 po_line_locations_all poll
245 WHERE rt.transaction_id = aida.rcv_transaction_id
246 AND pod.line_location_id = rt.po_line_location_id
247 AND poll.line_location_id = rt.po_line_location_id
248 AND nvl(poll.lcm_flag,'N') = 'Y'
249 AND pod.destination_type_code = 'EXPENSE' )
250 /*bug 5044215/5264793.Only pick up receipts that have delivers */
251 AND EXISTS (Select 1
252 from rcv_transactions rt2
253 where rt2.transaction_type in ('DELIVER')
254 START WITH rt2.transaction_id = aida.rcv_transaction_id
255 CONNECT BY
256 prior rt2.transaction_id = rt2.parent_transaction_id
257 )
258 );
259
260 CURSOR c_lcm_adj (l_start_date IN DATE,
261 l_end_date IN DATE)
262 IS SELECT clat.rcv_transaction_id,
263 clat.inventory_item_id,
264 clat.organization_id,
265 rp.receiving_account_id,
266 nvl(msi.inventory_asset_flag,'N') inventory_asset_flag,
267 rt.po_header_id,
268 rt.po_line_location_id line_location_id,
269 rt.po_line_id,
270 rt.unit_landed_cost,
271 msi.primary_uom_code,
272 (nvl(poll.price_override,0) + get_rcv_tax(rt.transaction_id)) po_unit_price,
273 decode(nvl(poll.match_option,'P'),
274 'P',get_po_rate(rt.transaction_id),
275 'R',rt.currency_conversion_rate) rate,
276 poll.org_id,
277 rt.po_release_id,
278 nvl(rt.po_distribution_id,-1) po_distribution_id,
279 poll.quantity poll_quantity,
280 muom.unit_of_measure,
281 max(clat.transaction_id) transaction_id
282 FROM cst_lc_adj_transactions clat,
283 cst_cost_group_assignments ccga1,
284 mtl_parameters mp,
285 rcv_transactions rt,
286 rcv_parameters rp,
287 mtl_system_items msi,
288 po_line_locations_all poll,
289 mtl_units_of_measure muom
290 WHERE rt.transaction_date < l_start_date
291 AND clat.transaction_date BETWEEN l_start_date and l_end_date
292 AND clat.rcv_transaction_id = rt.transaction_id
293 AND ccga1.cost_group_id = i_cost_group_id
294 AND rt.organization_id = ccga1.organization_id
295 AND mp.organization_id = ccga1.organization_id
296 AND msi.organization_id = clat.organization_id
297 AND clat.organization_id = rt.organization_id
298 AND msi.inventory_item_id = clat.inventory_item_id
299 AND mp.lcm_enabled_flag = 'Y'
300 AND rp.organization_id = ccga1.organization_id
301 AND poll.line_location_id = rt.po_line_location_id
302 AND poll.lcm_flag = 'Y'
303 AND muom.uom_code = msi.primary_uom_code
304 GROUP BY clat.rcv_transaction_id,
305 clat.inventory_item_id,
306 clat.organization_id,
307 rp.receiving_account_id,
308 nvl(msi.inventory_asset_flag,'N'),
309 rt.po_header_id,
310 rt.po_line_location_id,
311 rt.po_line_id,
312 rt.unit_landed_cost,
313 msi.primary_uom_code,
314 (nvl(poll.price_override,0) + get_rcv_tax(rt.transaction_id)),
315 decode(nvl(poll.match_option,'P'),
316 'P',get_po_rate(rt.transaction_id),
317 'R',rt.currency_conversion_rate),
318 poll.org_id,
319 rt.po_release_id,
320 nvl(rt.po_distribution_id,-1),
321 poll.quantity,
322 muom.unit_of_measure;
323
324 CURSOR c_lcm_del(p_rcv_transaction_id IN NUMBER,
325 p_valuation_date IN DATE,
326 p_organization_id IN NUMBER) IS
327 SELECT mmt.subinventory_code,
328 nvl(mse.asset_inventory,2) asset_inventory,
329 rt.po_distribution_id,
330 sum(mmt.primary_quantity) primary_quantity
331 FROM ( SELECT po_distribution_id,
332 transaction_id,
333 organization_id
334 FROM rcv_transactions
335 WHERE transaction_type IN ('DELIVER','RETURN TO RECEIVING','CORRECT')
336 AND transaction_date < p_valuation_date
337 AND organization_id = p_organization_id
338 START WITH transaction_id = p_rcv_transaction_id
339 CONNECT BY parent_transaction_id = PRIOR transaction_id
340 ) rt,
341 mtl_material_transactions mmt,
342 mtl_secondary_inventories mse
343 WHERE rt.transaction_id = mmt.rcv_transaction_id
344 AND mse.secondary_inventory_name = mmt.subinventory_code
345 AND mse.organization_id = mmt.organization_id
346 AND mmt.organization_id = rt.organization_id
347 GROUP BY mmt.subinventory_code,
348 nvl(mse.asset_inventory,2),
349 rt.po_distribution_id;
350
351 l_err_num NUMBER;
352 l_err_code VARCHAR2(240);
353 l_err_msg VARCHAR2(240);
354 l_stmt_num NUMBER := 0;
355 l_hook INTEGER;
356 l_first_time_flag NUMBER := 0;
357 l_legal_entity NUMBER := 0;
358 l_start_date DATE;
359 l_end_date DATE;
360 l_res_flag NUMBER := 0;
361 l_details_nextvalue NUMBER;
362 l_priuom_cost NUMBER;
363 l_res_invoices NUMBER;
364 l_prev_period_id NUMBER;
365
366 l_chrg_present NUMBER :=0;
367
368 l_accounting_event_id NUMBER;
369 l_rae_unit_price NUMBER;
370 l_sob_id NUMBER;
371 l_rae_trf_price_flag rcv_accounting_events.INTERCOMPANY_PRICING_OPTION%TYPE;
372
373 l_order_type_lookup_code VARCHAR2(20);
374
375 CST_FAIL_GET_NQR EXCEPTION;
376 CST_FAIL_ACQ_HOOK EXCEPTION;
377 CST_FAIL_LCM_HOOK EXCEPTION;
378 CST_FAIL_GET_CHARGE_ALLOCS EXCEPTION;
379 CST_FAIL_COMPUTE_ACQ_COST EXCEPTION;
380 CST_ACQ_NULL_RATE EXCEPTION;
381 CST_ACQ_NULL_TAX EXCEPTION;
382 PROCESS_ERROR EXCEPTION;
383 -- Added for Perf Bug# 5214447
384 l_recs_processed NUMBER; --counter
385 l_commit_records_count NUMBER := 500; -- COMMIT to be issued every 500 records. Can be changed, if reqd.
386
387 BEGIN
388 IF g_debug = 'Y' THEN
389 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Acq_Cost_Processor <<<');
390 END IF;
391 l_recs_processed := 0;
392 -----------------------------------------------------
393 -- Initialize error variables
394 -----------------------------------------------------
395
396 l_err_num := 0;
397 l_err_code := '';
398 l_err_msg := '';
399 o_err_num := 0;
400 o_err_code := '';
401 o_err_msg := '';
402
403 /* Initialize the first time flag to 0 */
404
405 l_first_time_flag := 0;
406
407 ---------------------------------------------
408 -- Call Hooks
409 ---------------------------------------------
410
411 l_stmt_num := 10;
412 l_hook := CSTPPAHK.acq_cost_hook(
413 i_period,
414 i_start_date,
415 i_end_date,
416 i_cost_type_id,
417 i_cost_group_id,
418 i_user_id,
419 i_login_id,
420 i_req_id,
421 i_prog_id,
422 i_prog_appl_id,
423 l_err_num,
424 l_err_code,
425 l_err_msg);
426
427 IF (l_err_num <> 0) THEN
428 RAISE CST_FAIL_ACQ_HOOK;
429 END IF;
430
431 l_stmt_num := 15;
432 IF l_hook = 0 THEN
433 l_hook := CST_LandedCostHook_PUB.landed_cost_hook (
434 i_period,
435 i_start_date,
436 i_end_date,
437 i_cost_type_id,
438 i_cost_group_id,
439 i_user_id,
440 i_login_id,
441 i_req_id,
442 i_prog_id,
443 i_prog_appl_id,
444 l_err_num,
445 l_err_code,
446 l_err_msg );
447
448 IF (l_err_num <> 0) THEN
449 RAISE CST_FAIL_LCM_HOOK;
450 END IF;
451 END IF;
452
453 l_stmt_num := 20;
454
455 select legal_entity
456 into l_legal_entity
457 from cst_cost_groups
458 where cost_group_id = i_cost_group_id ;
459
460 -- If this package is called from the acquisition adjustment process, then
461 -- we will have to get the period start date
462
463
464 -- Check for restriction flag on Invoices. If it has been turned off,
465 -- then there is a chance that invoices in the next period may have been
466 -- picked up by the acquisition cost processor, in which case,the adjustment
467 -- processor must not pick them up(for the all receipts case)
468
469 l_stmt_num := 30;
470
471 select NVL(restrict_doc_flag, 2),
472 set_of_books_id
473 into l_res_invoices,
474 l_sob_id
475 from cst_le_cost_types
476 where legal_entity = l_legal_entity
477 and cost_type_id = i_cost_type_id;
478
479 If i_source_flag = 2 then
480
481 If l_res_invoices = 2 then /* not set */
482
483 l_stmt_num := 40;
484 BEGIN /* to handle the case of no prev pac periods */
485 select NVL(MAX(pac_period_id), -1)
486 into l_prev_period_id
487 from cst_pac_periods
488 where legal_entity = l_legal_entity
489 and open_flag = 'N'
490 and cost_type_id = i_cost_type_id ;
491
492 select period_close_date,
493 i_end_date
494 into l_start_date,
495 l_end_date
496 from cst_pac_periods
497 where pac_period_id = l_prev_period_id
498 and legal_entity = l_legal_entity
499 and cost_type_id = i_cost_type_id;
500
501 EXCEPTION
502 when others then
503 l_start_date := NULL;
504 l_end_date := i_end_date;
505 END;
506
507 end If; -- l_res_invoices = 2
508
509 If l_res_invoices = 1 OR l_start_date is NULL then
510
511 l_stmt_num := 50;
512 select period_start_date,
513 i_end_date
514 INTO l_start_date,
515 l_end_date
516 FROM cst_pac_periods cpp
517 where cpp.pac_period_id = i_period
518 and cpp.legal_entity = l_legal_entity
519 and cpp.cost_type_id = i_cost_type_id;
520
521 end If; -- l_res_invoices = 1
522
523 else -- l_source_flag <> 2
524 l_start_date := i_start_date;
525 l_end_date := i_end_date;
526 end if;
527
528 IF g_debug = 'Y' THEN
529 fnd_file.put_line(fnd_file.log,'Start date: ' || to_char(l_start_date,'DD-MON-RR'));
530 fnd_file.put_line(fnd_file.log,'End Date: ' || to_char(l_end_date,'DD-MON-RR'));
531 END IF;
532
533
534 -- ==================================================================================
535 -- FP BUG 6748898 fix
536 -- Execute the cursor c_receipts_source_flag_1 when i_source_flag is 1
537 -- Periodic Acquisition Cost Processor
538 -- Execute the cursor c_receipts_source_flag_2 when i_source_flag is 2
539 -- Periodic Acquisition Cost Adjustment Processor
540 -- ==================================================================================
541 IF i_source_flag = 1 THEN
542
543 -------------------------------------------------------------------------------------
544 -- 2.0 Loop for each receipt when source flag = 1 Periodic Acquisition Cost Processor
545 -------------------------------------------------------------------------------------
546 FOR c_rec IN c_receipts_source_flag_1(l_start_date,l_end_date,i_receipt_no,i_invoice_no) LOOP
547
548 l_recs_processed := l_recs_processed + 1;
549 IF (c_rec.lcm_flag <> 'Y') THEN
550 DECLARE
551 l_rec_ct NUMBER := 0;
552 l_nqr rcv_transactions.quantity%TYPE := 0;
553 l_inv_count number;
554 l_header cst_rcv_acq_costs.header_id%TYPE;
555 l_primary_uom mtl_system_items.primary_uom_code%TYPE;
556 l_po_uom mtl_units_of_measure.uom_code%TYPE;
557 l_po_uom_code po_lines_all.unit_meas_lookup_code%TYPE;
558 l_po_price po_lines_all.unit_price%TYPE;
559 l_rate rcv_transactions.CURRENCY_CONVERSION_RATE%TYPE;
560 l_po_line_loc po_lines_all.po_line_id%TYPE;
561 l_item_id mtl_system_items.inventory_item_id%TYPE;
562 l_org_id rcv_transactions.organization_id%TYPE;
563 l_poll_quantity po_line_locations_all.quantity%TYPE;
564 l_pri_poll_quantity po_line_locations_all.quantity%TYPE;
565 l_po_count NUMBER; -- remove this later
566 l_nr_tax_rate NUMBER;
567 l_match_option po_line_locations_all.match_option%TYPE;
568 l_rec_uom_code rcv_transactions.unit_of_measure%TYPE;
569 l_rec_uom mtl_units_of_measure.uom_code%TYPE;
570
571 BEGIN
572 If G_DEBUG ='Y' then
573 fnd_file.put_line(fnd_file.log, 'Transaction: ' ||to_char(c_rec.transaction_id));
574 end If;
575
576 l_stmt_num := 60;
577
578 SELECT count(rcv_transaction_id)
579 INTO l_rec_ct
580 FROM cst_rcv_acq_costs crac
581 WHERE crac.rcv_transaction_id = c_rec.transaction_id
582 AND crac.period_id = i_period
583 AND crac.cost_type_id = i_cost_type_id
584 AND crac.cost_group_id = i_cost_group_id
585 AND ROWNUM < 2;
586
587
588 IF l_rec_ct = 0 THEN
589
590 -------------------------------------------------------------
591 -- 2.1 Get net quantity received in primary uom
592 -------------------------------------------------------------
593 l_nqr := get_nqr(c_rec.transaction_id,i_source_flag,
594 l_start_date,l_end_date,l_res_invoices,l_err_num); -- in pri uom
595
596 IF (l_err_num <> 0) THEN
597 RAISE CST_FAIL_GET_NQR;
598 END IF;
599
600 l_stmt_num := 110;
601
602 -----------------------------------------------------------
603 -- Get next header id from sequence
604 -----------------------------------------------------------
605 SELECT cst_rcv_acq_costs_s.nextval
606 INTO l_header
607 FROM dual;
608
609 /* begin changes for dropshipment project */
610 /* Includes Changes for Service Line Types */
611 l_stmt_num := 120;
612 Begin
613 Select rae.accounting_event_id,
614 DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work Procurement
615 'AMOUNT', RAE.TRANSACTION_AMOUNT,
616 'QUANTITY',rae.unit_price),
617 INTERCOMPANY_PRICING_OPTION
618 Into l_accounting_event_id,
619 l_rae_unit_price,
620 l_rae_trf_price_flag
621 From rcv_accounting_events rae,
622 po_lines_all POL,
623 po_line_locations_all POLL, -- Added for Complex work Procurement
624 po_distributions_all POD
625 Where rae.rcv_transaction_id = c_rec.transaction_id
626 And rae.event_type_id = 1 -- RECEIVE
627 And rae.trx_flow_header_id is not null
628 AND RAE.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
629 AND POD.PO_LINE_ID = POL.PO_LINE_ID
630 AND POLL.PO_LINE_ID = POL.PO_LINE_ID; -- Added for Complex work Procurement
631 Exception
632 When others then
633 l_accounting_event_id:= 0;
634 l_rae_unit_price := 0;
635 l_rae_trf_price_flag := 1;
636 End;
637
638 If (l_rae_trf_price_flag <> 2) then
639 l_nr_tax_rate := get_rcv_tax(c_rec.transaction_id);
640
641 IF (l_nr_tax_rate is null) THEN
642 RAISE CST_ACQ_NULL_TAX;
643 END IF;
644 Else
645 l_nr_tax_rate := 0;
646 End if;
647 /* dropshipment end */
648
649 -------------------------------------------------------------
650 -- Get the match_option from po_line_locations_all
651 -- If match_option is P then exch rate has to be the rate at the time of PO
652 -- If match_option is R then exch rate has to be the rate at the time of Receipt
653 -------------------------------------------------------------
654
655 l_stmt_num := 130;
656
657 SELECT nvl(poll.match_option,'P')
658 INTO l_match_option
659 FROM po_line_locations_all poll,
660 rcv_transactions rt7
661 WHERE
662 poll.line_location_id = rt7.po_line_location_id
663 AND rt7.transaction_id = c_rec.transaction_id;
664
665
666 -------------------------------------------------------------
667 -- if po_line_id in POLL does not exist in POL !!
668 -- this is due to corrupted data of a line_id in POLL not being in POL
669 -------------------------------------------------------------
670
671 l_stmt_num := 140;
672
673 SELECT count(rt2.transaction_id)
674 INTO l_po_count
675 FROM rcv_transactions rt2,
676 po_lines_all pol1,
677 po_line_locations_all poll1
678 WHERE rt2.transaction_id = c_rec.transaction_id
679 AND rt2.po_line_location_id = poll1.line_location_id
680 AND pol1.po_line_id = poll1.po_line_id
681 AND ROWNUM < 2;
682
683 IF l_po_count = 0 THEN
684 l_stmt_num := 150;
685
686 SELECT
687 decode(l_rae_trf_price_flag, 2, l_rae_unit_price, (nvl(poll2.price_override,0) + l_nr_tax_rate)),
688 rt3.po_line_location_id,
689 nvl(rt3.CURRENCY_CONVERSION_RATE,1) ,
690 rsl.item_id,
691 nvl(poll2.unit_meas_lookup_code,rsl.unit_of_measure),
692 poll2.quantity,
693 rt3.organization_id,
694 nvl(poll2.matching_basis,'QUANTITY') /* Bug4762808 */
695 INTO
696 l_po_price,
697 l_po_line_loc,
698 l_rate,
699 l_item_id,
700 l_po_uom_code,
701 l_poll_quantity,
702 l_org_id,
703 l_order_type_lookup_code
704 FROM
705 rcv_transactions rt3,
706 rcv_shipment_lines rsl,
707 po_line_locations_all poll2
708 WHERE
709 rt3.transaction_id = c_rec.transaction_id
710 AND rt3.po_line_location_id = poll2.line_location_id
711 AND rsl.shipment_line_id = rt3.shipment_line_id;
712
713 ELSE -- l_po_count
714 ------------------------------------------------------------
715 -- Get Per Unit PO Price in terms of PO UOM
716 -- Get PO Line Location Id, Item id, PO UOM, PO Quantity, org
717 ------------------------------------------------------------
718
719 -- price_override is based on PO UOM
720 -- non_recoverable_tax is based on PO UOM so divide by PO quantity
721 -- price_override in po currency
722 -- non_recoverable_tax in po currency
723 -- po_price will not be converted into functional currency now
724 -- because we want to use the exch rate at time of receipt
725
726 l_stmt_num := 65;
727
728 SELECT
729 -- J Changes ---------------------------------------------------------------
730 DECODE(POLL3.MATCHING_BASIS,
731 'AMOUNT', 1 + l_nr_tax_rate,
732 'QUANTITY',decode(l_rae_trf_price_flag, 2, l_rae_unit_price, (nvl(poll3.price_override,0) + l_nr_tax_rate))),
733 ----------------------------------------------------------------------------
734 rt33.po_line_location_id,
735 rt33.unit_of_measure ,
736 nvl(pol2.item_id,-1),
737 nvl(poll3.unit_meas_lookup_code,pol2.unit_meas_lookup_code),
738 poll3.quantity,
739 rt33.organization_id,
740 decode(nvl(poll3.match_option,'P'),
741 'P',get_po_rate(rt33.transaction_id),
742 'R',rt33.currency_conversion_rate),
743 nvl(poll3.matching_basis,'QUANTITY') /* Bug4762808 */
744 INTO
745 l_po_price,
746 l_po_line_loc,
747 l_rec_uom_code,
748 l_item_id,
749 l_po_uom_code,
750 l_poll_quantity,
751 l_org_id,
752 l_rate,
753 l_order_type_lookup_code
754 FROM
755 po_lines_all pol2,
756 po_line_locations_all poll3,
757 rcv_transactions rt33
758 WHERE
759 rt33.transaction_id = c_rec.transaction_id
760 AND rt33.po_line_location_id = poll3.line_location_id
761 AND pol2.po_line_id = poll3.po_line_id;
762
763 END IF; -- l_po_count
764
765 IF (l_rate is null OR l_rate = -1) THEN
766 RAISE CST_ACQ_NULL_RATE;
767 END IF;
768
769 l_stmt_num := 67;
770
771
772 /* Bug 4762808 - Service Line Type POs do not have UOM and quantity populated.*/
773
774 If l_order_type_lookup_code <> 'AMOUNT' then
775 ------------------------------------------------------
776 -- Get UOM code for PO UOM and REC UOM
777 ------------------------------------------------------
778
779 SELECT
780 mum1.uom_code
781 INTO
782 l_po_uom
783 FROM
784 mtl_units_of_measure mum1
785 WHERE
786 MUM1.UNIT_OF_measure = l_po_uom_code;
787
788 l_stmt_num := 70;
789
790 SELECT
791 mum1.uom_code
792 INTO
793 l_rec_uom
794 FROM
795 mtl_units_of_measure mum1
796 WHERE
797 mum1.unit_of_measure = l_rec_uom_code;
798
799 l_stmt_num := 30;
800
801 ---------------------------------------------------------
802 -- Get Primary UOM for the Item for the org
803 ---------------------------------------------------------
804
805 IF l_item_id = -1 THEN
806 l_primary_uom := l_po_uom;
807 ELSE
808
809 l_stmt_num := 75;
810
811 SELECT
812 msi.primary_uom_code
813 INTO
814 l_primary_uom
815 FROM
816 mtl_system_items msi
817 WHERE
818 msi.inventory_item_id = l_item_id AND
819 msi.organization_id = l_org_id;
820 END IF;
821
822 ---------------------------------------------------------
823 -- Convert PO Quantity into Primary Quantity
824 ---------------------------------------------------------
825
826 l_stmt_num := 78;
827
828 l_pri_poll_quantity := inv_convert.inv_um_convert(
829 l_item_id,
830 NULL,
831 l_poll_quantity, -- PO quantity
832 l_po_uom, -- PO UOM
833 l_primary_uom, -- pri uom
834 NULL,
835 NULL);
836
837 ---------------------------------------------------------
838 -- PO per unit price in POLL is based on PO UOM
839 -- Convert the price based on Primary UOM
840 ---------------------------------------------------------
841
842 l_po_price := l_po_price * l_poll_quantity / l_pri_poll_quantity;
843 End if;
844
845 --------------------------------------------------------
846 -- 2.2 Insert inTO cst_rcV_ACQ_COSTS a row for the receipt
847 -- for cost type, period, cost group
848 -- setting quantity_invoiced, quantity_at_po_price,
849 -- total_invoice_amount, amount_at_po_price, total_amount,
850 -- costed_quantity, acqcuisition_cost to NULL for now
851 -- These values will be updated later with the right values.
852 ----------------------------------------------------------
853
854 l_stmt_num := 80;
855
856 Insert_into_acqhdr_tables(
857 l_header,
858 i_cost_group_id,
859 i_cost_type_id,
860 i_period,
861 c_rec.transaction_id,
862 l_nqr, -- in pri uom
863 NULL,
864 NULL,
865 NULL,
866 NULL,
867 NULL,
868 NULL,
869 NULL,
870 l_po_line_loc,
871 l_po_price, -- in po currency based on pri uom
872 l_primary_uom,
873 l_rate, -- rate at time of receipt
874 SYSDATE,
875 i_user_id,
876 SYSDATE,
877 i_user_id,
878 i_req_id,
879 i_prog_appl_id,
880 i_prog_id,
881 SYSDATE,
882 i_login_id,
883 i_source_flag,
884 l_err_num,
885 l_err_msg);
886
887
888 if (l_accounting_event_id = 0) then --added for dropshipment project
889 -------------------------------------------------------------
890 -- 2.3 Get all posted Invoice lines from AP_INVOICE_DISTRIBUTIONS_ALL
891 -- which are matched to the receipt
892 --------------------------------------------------------------
893
894 l_stmt_num := 85;
895
896 Select NVL(restrict_doc_flag,2) into l_res_flag
897 from CST_LE_COST_TYPES
898 where legal_entity = l_legal_entity
899 and cost_type_id = i_cost_type_id;
900
901 l_stmt_num := 90;
902
903 SELECT count(rcv_transaction_id)
904 INTO l_inv_count
905 FROM ap_invoice_distributions_all ad1
906 WHERE ad1.rcv_transaction_id = c_rec.transaction_id AND
907 ((l_res_flag =1) AND (ad1.accounting_date between i_start_date and i_end_date)) OR (l_res_flag = 2)
908 AND ad1.posted_flag = 'Y' AND
909 /* Invoice Lines Project TAX is now REC_TAX and NONREC_TAX */
910 ad1.line_type_lookup_code <> 'REC_TAX' AND
911 ROWNUM < 2;
912
913 else
914 l_inv_count := 0;
915 end if;
916 --------------------------------------------------------------
917 -- 2.4 If there are invoices
918 -- 2.4.1 loop for each invoice dist line
919 ---------------------------------------------------------------
920
921 IF l_inv_count > 0 THEN
922 DECLARE
923
924 CURSOR c_invoices IS
925 SELECT
926 ad2.invoice_distribution_id,
927 ad2.invoice_id,
928 -- J Changes ------------------------------------------------------------------
929 nvl(DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work Procurement
930 'AMOUNT', AD2.AMOUNT,
931 'QUANTITY',ad2.quantity_invoiced), 0 ) "QUANTITY_INVOICED", -- Invoice UOM
932 -------------------------------------------------------------------------------
933 ad2.distribution_line_number,
934 ad2.line_type_lookup_code,
935 -- J Changes ------------------------------------------------------------------
936 nvl(DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work Procurement
937 'AMOUNT', 1,
938 'QUANTITY', ad2.unit_price), 0 ) unit_price, -- Invoice Currency
939 --------------------------------------------------------------------------------
940 nvl(ad2.base_amount, nvl(ad2.amount, 0)) base_amount
941 FROM
942 ap_invoice_distributions_all ad2,
943 -- J Changes -----------------------------------------------------------
944 RCV_TRANSACTIONS RT,
945 PO_LINES_ALL POL,
946 PO_LINE_LOCATIONS_ALL POLL, -- Added for Complex work Procurement
947 ap_invoices_all aia /* bug 4352624 Added to ignore invoices of type prepayment */
948 ------------------------------------------------------------------------
949 WHERE
950 ad2.rcv_transaction_id = c_rec.transaction_id
951 AND ad2.posted_flag = 'Y'
952 /* bug 4352624 Added to ignore invoices of type prepayment */
953 AND ad2.line_type_lookup_code <>'PREPAY'
954 AND aia.invoice_id = ad2.invoice_id
955 AND aia.invoice_type_lookup_code <>'PREPAYMENT'
956
957 -- J Changes -----------------------------------------------------------
958 AND RT.TRANSACTION_ID = AD2.RCV_TRANSACTION_ID
959 AND POL.PO_LINE_ID = RT.PO_LINE_ID
960 AND RT.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
961 AND POLL.PO_LINE_ID = POL.PO_LINE_ID ---- Added for Complex work Procurement
962 ------------------------------------------------------------------------
963 AND ( ( l_res_flag =1 ) AND ( ad2.accounting_date between i_start_date and i_end_date) )
964 OR (l_res_flag = 2)
965 /* Invoice Lines Project TAX is now REC_TAX AND NONREC_TAX */
966 AND ad2.line_type_lookup_code <> 'REC_TAX'
967 -- J Changes -------------------------------------------------------------
968 -- Ensure that Price corrections are not picked --
969 /* Invoice Lines Project root_distribution_id ->
970 corrected_invoice_dist_id */
971 AND ad2.corrected_invoice_dist_id is null;
972 --------------------------------------------------------------------------
973 l_pri_quantity_invoiced NUMBER;
974
975 l_correction_amount NUMBER;
976 l_corr_inv NUMBER;
977 l_correction_tax_amount NUMBER; /*Bug3891984*/
978 l_corr_invoice_id NUMBER; /*Bug3891984*/
979
980 BEGIN
981 FOR c_inv IN c_invoices LOOP
982 ---------------------------------------------------
983 -- Check if there are any Price Correction Invoices
984 -- And if so, get the correction amount
985 ---------------------------------------------------
986 IF G_DEBUG = 'Y' THEN
987 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoice: ' ||to_char(c_inv.INVOICE_DISTRIBUTION_ID));
988 END IF;
989 BEGIN
990 -----------------------------------------------------
991 -- The latest price correction invoice does not
992 -- have an Invoice of type 'ADJUSTMENT' that reverses
993 -- it out.
994 -- Refer AP HLD for Retroactive Pricing
995 -- Make sure that there are no distributions in AIDA
996 -- with xinv_parent_distribution_id = inv_dist_id of
997 -- of the price correction invoice
998 -----------------------------------------------------
999 -------------------------------------------------------------------
1000 -- Bug 3891984 : Added the column invoice_id in the following select
1001 -- statement. This invoice id will be required to pick up the
1002 -- PO Price Adjustment invoices having LINE_TYPE_LOOKUP_CODE
1003 -- as 'TAX' with TAX_RECOVERABLE_FLAG set to 'N'
1004 -------------------------------------------------------------------
1005
1006 SELECT AIDA.INVOICE_ID, /*Bug3891984*/
1007 AIDA.INVOICE_DISTRIBUTION_ID, -- Only for debugging
1008 NVL(AIDA.BASE_AMOUNT, NVL(AIDA.AMOUNT, 0))
1009 INTO l_corr_invoice_id,
1010 l_corr_inv,
1011 l_correction_amount
1012 FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
1013 AP_INVOICES_ALL AP_INV
1014 /* Invoice Lines Project
1015 No root_distribution_id or xinv_parent_reversal_id
1016 now it'll just be represented by corrected_invoice_dist_id
1017 */
1018 WHERE AIDA.CORRECTED_INVOICE_DIST_ID = c_inv.INVOICE_DISTRIBUTION_ID
1019 AND AIDA.INVOICE_ID = AP_INV.INVOICE_ID
1020 AND AP_INV.INVOICE_TYPE_LOOKUP_CODE = 'PO PRICE ADJUST';
1021
1022 BEGIN
1023 /* Bug3891984 changes starts here */
1024 SELECT NVL(AIDA.BASE_AMOUNT, NVL(AIDA.AMOUNT, 0))
1025 INTO l_correction_tax_amount
1026 FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
1027 WHERE AIDA.INVOICE_ID = l_corr_invoice_id
1028 /* Invoice Lines Project non-recoverable tax now is just NONREC_TAX */
1029 AND AIDA.LINE_TYPE_LOOKUP_CODE = 'NONREC_TAX';
1030
1031 EXCEPTION
1032 WHEN OTHERS THEN
1033 l_correction_tax_amount := 0;
1034 l_corr_invoice_id := -1;
1035
1036 END;
1037
1038 l_correction_amount:= l_correction_amount + l_correction_tax_amount;
1039
1040 /* Bug3891984 ends here */
1041
1042 EXCEPTION
1043 WHEN OTHERS THEN
1044 l_correction_amount := 0;
1045 l_corr_inv := -1;
1046 END;
1047
1048 IF G_DEBUG = 'Y' THEN
1049 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Latest Price Correction Invoice: '||to_char(l_corr_inv));
1050 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Correction Amount (from Price Correction Invoice): '||to_char(l_correction_amount));
1051 END IF;
1052
1053 BEGIN
1054
1055 --------------------------------------------------------------
1056 -- Convert Invoice Quantity into Primary Units
1057 --------------------------------------------------------------
1058
1059 l_stmt_num := 95;
1060
1061 l_pri_quantity_invoiced := inv_convert.inv_um_convert(
1062 l_item_id,
1063 NULL,
1064 c_inv.quantity_invoiced,
1065 l_rec_uom, -- inv uom same as rec when matched to receipt
1066 l_primary_uom,
1067 NULL,
1068 NULL);
1069
1070 ---------------------------------------------------------------
1071 -- 2.4.1.1 Insert into CST_RCV_ACQ_COST_DETAILS table
1072 ---------------------------------------------------------------
1073
1074 l_stmt_num := 100;
1075
1076 /* bug fix for bug 3411774. The acquisition cost considers the TAX twice if there is a
1077 rcv_transaction_id against it and also if it is allocated to the ITEM lines.
1078 So we should prevent insertion into the details table from the c_reciepts cursor as it will be
1079 inserted into the details table later from the chrg_allocations cursor */
1080
1081 l_chrg_present := 0;
1082
1083 BEGIN
1084
1085 /* Invoice Lines Project no more ap_chrg_allocations_all table */
1086 Select count(*) into l_chrg_present
1087 from ap_invoice_distributions_all
1088 where invoice_distribution_id = c_inv.invoice_distribution_id
1089 and charge_applicable_to_dist_id is not null;
1090
1091 EXCEPTION
1092 WHEN OTHERS THEN
1093 l_chrg_present := 0;
1094
1095 END;
1096
1097 If l_chrg_present = 0 then /* means that this has not been allocated */
1098
1099 select cst_rcv_acq_cost_details_s.nextval into l_details_nextvalue
1100 from dual;
1101
1102 l_stmt_num := 105;
1103
1104 select decode(l_pri_quantity_invoiced,0,0,(c_inv.unit_price * c_inv.quantity_invoiced / l_pri_quantity_invoiced)) into l_priuom_cost
1105 from dual;
1106
1107 l_stmt_num := 110;
1108
1109 Insert_into_acqdtls_tables (
1110 l_header,
1111 l_details_nextvalue,
1112 'INVOICE',
1113 NULL,
1114 c_inv.invoice_distribution_id,
1115 1,
1116 0,
1117 c_inv.invoice_distribution_id,
1118 NULL,
1119 NULL,
1120 NULL,
1121 c_inv.base_amount + l_correction_amount, -- in func currency
1122 l_pri_quantity_invoiced, -- in pri uom
1123 l_priuom_cost, -- convert to price based on pri uom
1124 c_inv.line_type_lookup_code,
1125 SYSDATE,
1126 i_user_id,
1127 SYSDATE,
1128 i_user_id,
1129 i_req_id,
1130 i_prog_appl_id,
1131 i_prog_id,
1132 SYSDATE,
1133 i_login_id,
1134 i_source_flag,
1135 l_err_num,
1136 l_err_msg);
1137
1138 End If; /* end of check for rows to be present in chrg allocations table */
1139 ------------------------------------------------------------
1140 -- 2.4.1.2 Get all special charge lines that are directly
1141 -- or indirectly allocated to the invoice lines
1142 -- (that are matched to the receipt)
1143 ------------------------------------------------------------
1144 l_stmt_num := 115;
1145
1146 get_charge_allocs(
1147 l_header,
1148 c_inv.invoice_distribution_id,
1149 i_start_date,
1150 i_end_date,
1151 l_res_flag,
1152 i_user_id,
1153 i_login_id,
1154 i_req_id,
1155 i_prog_id,
1156 i_prog_appl_id,
1157 l_err_num,
1158 l_err_code,
1159 l_err_msg);
1160
1161 IF (l_err_num <> 0) THEN
1162 RAISE CST_FAIL_GET_CHARGE_ALLOCS;
1163 END IF;
1164 END;
1165 END LOOP; -- Invoice loop
1166 END;
1167 END IF; -- If Invoice count > 0
1168
1169 --------------------------------------------------------
1170 -- 2.5 Compute the Acquisition Cost based on the info in CRACD
1171 --------------------------------------------------------
1172 l_stmt_num := 125;
1173
1174 compute_acq_cost(
1175 l_header,
1176 l_nqr,
1177 l_po_line_loc,
1178 l_po_price,
1179 l_primary_uom,
1180 l_rate,
1181 l_po_uom,
1182 l_item_id,
1183 i_user_id,
1184 i_login_id,
1185 i_req_id,
1186 i_prog_id,
1187 i_prog_appl_id,
1188 l_err_num,
1189 l_err_code,
1190 l_err_msg);
1191
1192 IF (l_err_num <> 0) THEN
1193 RAISE CST_FAIL_COMPUTE_ACQ_COST;
1194 END IF;
1195 END IF; -- if hook was used
1196 END;
1197 ELSE /*LCM enabled*/
1198 DECLARE
1199 l_rct_landed_cost NUMBER;
1200 l_rec_ct NUMBER;
1201 l_rct_adj_landed_cost NUMBER;
1202 l_lcm_acq_cost NUMBER;
1203 l_net_qty_rec rcv_transactions.quantity%TYPE := 0;
1204 l_header_id cst_rcv_acq_costs.header_id%TYPE;
1205 l_nr_tax_rate NUMBER;
1206 l_primary_uom mtl_system_items.primary_uom_code%TYPE;
1207 l_po_uom mtl_units_of_measure.uom_code%TYPE;
1208 l_po_uom_code po_lines_all.unit_meas_lookup_code%TYPE;
1209 l_po_price po_lines_all.unit_price%TYPE;
1210 l_rate rcv_transactions.CURRENCY_CONVERSION_RATE%TYPE;
1211 l_po_line_loc po_line_locations_all.line_location_id%TYPE;
1212 l_item_id mtl_system_items.inventory_item_id%TYPE;
1213 l_org_id rcv_transactions.organization_id%TYPE;
1214 BEGIN
1215 l_stmt_num := 1000;
1216 SELECT count(rcv_transaction_id)
1217 INTO l_rec_ct
1218 FROM cst_rcv_acq_costs crac
1219 WHERE crac.rcv_transaction_id = c_rec.transaction_id
1220 AND crac.period_id = i_period
1221 AND crac.cost_type_id = i_cost_type_id
1222 AND crac.cost_group_id = i_cost_group_id
1223 AND ROWNUM < 2;
1224
1225
1226 IF l_rec_ct = 0 THEN /*Hook has not been used*/
1227 l_stmt_num := 1010;
1228 SELECT unit_landed_cost
1229 INTO l_rct_landed_cost
1230 FROM rcv_transactions
1231 WHERE transaction_id = c_rec.transaction_id;
1232 l_stmt_num := 1020;
1233
1234 SELECT nvl(Max(new_landed_cost),-1)
1235 INTO l_rct_adj_landed_cost
1236 FROM
1237 (
1238 SELECT new_landed_cost,transaction_id,
1239 max(transaction_id) OVER ( PARTITION BY transaction_date)
1240 max_transaction_id
1241 FROM
1242 (SELECT new_landed_cost,transaction_id,transaction_date,
1243 max(transaction_date) OVER (PARTITION BY rcv_transaction_id)
1244 max_transaction_date
1245 FROM cst_lc_adj_transactions
1246 WHERE rcv_transaction_id = c_rec.transaction_id
1247 AND transaction_date BETWEEN l_start_date
1248 AND l_end_date)
1249 WHERE transaction_date = max_transaction_date
1250 )
1251 WHERE transaction_id = max_transaction_id;
1252 IF ( l_rct_adj_landed_cost = -1) THEN
1253 l_lcm_acq_cost := l_rct_landed_cost;
1254 ELSE
1255 l_lcm_acq_cost := l_rct_adj_landed_cost;
1256 END IF;
1257 l_stmt_num := 1030;
1258 l_net_qty_rec := get_nqr(i_transaction_id => c_rec.transaction_id,
1259 i_source_flag => i_source_flag,
1260 i_start_date => l_start_date,
1261 i_end_date => l_end_date,
1262 i_res_flag => 1,
1263 o_err_num =>l_err_num);
1264
1265 IF (l_err_num <> 0) THEN
1266 RAISE CST_FAIL_GET_NQR;
1267 END IF;
1268
1269 l_stmt_num := 1040;
1270 SELECT cst_rcv_acq_costs_s.nextval
1271 INTO l_header_id
1272 FROM dual;
1273
1274 l_stmt_num := 1050;
1275
1276 l_nr_tax_rate := get_rcv_tax(c_rec.transaction_id);
1277
1278 l_stmt_num := 1060;
1279
1280 SELECT
1281 (nvl(poll3.price_override,0) + l_nr_tax_rate),
1282 rt33.po_line_location_id,
1283 nvl(pol2.item_id,-1),
1284 nvl(poll3.unit_meas_lookup_code,
1285 pol2.unit_meas_lookup_code),
1286 rt33.organization_id,
1287 decode(nvl(poll3.match_option,'P'),
1288 'P',get_po_rate(rt33.transaction_id),
1289 'R',rt33.currency_conversion_rate)
1290 INTO
1291 l_po_price,
1292 l_po_line_loc,
1293 l_item_id,
1294 l_po_uom_code,
1295 l_org_id,
1296 l_rate
1297 FROM
1298 po_lines_all pol2,
1299 po_line_locations_all poll3,
1300 rcv_transactions rt33
1301 WHERE rt33.transaction_id = c_rec.transaction_id
1302 AND rt33.po_line_location_id = poll3.line_location_id
1303 AND pol2.po_line_id = poll3.po_line_id;
1304
1305 l_stmt_num := 1060;
1306
1307 SELECT mum1.uom_code
1308 INTO l_po_uom
1309 FROM mtl_units_of_measure mum1
1310 WHERE MUM1.UNIT_OF_measure = l_po_uom_code;
1311
1312 IF l_item_id = -1 THEN
1313 l_primary_uom := l_po_uom;
1314 ELSE
1315 l_stmt_num := 1070;
1316 SELECT msi.primary_uom_code
1317 INTO l_primary_uom
1318 FROM mtl_system_items msi
1319 WHERE msi.inventory_item_id = l_item_id
1320 AND msi.organization_id = l_org_id;
1321 END IF;
1322
1323 l_stmt_num := 1080;
1324 Insert_into_acqhdr_tables(
1325 i_header_id => l_header_id,
1326 i_cost_group_id => i_cost_group_id,
1327 i_cost_type_id => i_cost_type_id,
1328 i_period_id => i_period,
1329 i_rcv_transaction_id => c_rec.transaction_id,
1330 i_net_quantity_received => l_net_qty_rec,
1331 i_total_quantity_invoiced => NULL,
1332 i_quantity_at_po_price => 0,
1333 i_total_invoice_amount => NULL,
1334 i_amount_at_po_price => 0,
1335 i_total_amount => l_net_qty_rec*l_lcm_acq_cost,
1336 i_costed_quantity => l_net_qty_rec,
1337 i_acquisition_cost => l_lcm_acq_cost,
1338 i_po_line_location_id => l_po_line_loc,
1339 i_po_unit_price => l_po_price,
1340 i_primary_uom => l_primary_uom,
1341 i_rec_exchg_rate => l_rate,
1342 i_last_update_date => SYSDATE,
1343 i_last_updated_by => i_user_id,
1344 i_creation_date => SYSDATE,
1345 i_created_by => i_user_id,
1346 i_request_id => i_req_id,
1347 i_program_application_id => i_prog_appl_id,
1348 i_program_id => i_prog_id,
1349 i_program_update_date => SYSDATE,
1350 i_last_update_login => i_login_id,
1351 i_source_flag => i_source_flag,
1352 o_err_num => l_err_num,
1353 o_err_msg => l_err_msg );
1354
1355 l_stmt_num := 1090;
1356 INSERT INTO cst_rcv_acq_cost_details (
1357 HEADER_ID,
1358 DETAIL_ID,
1359 SOURCE_TYPE,
1360 PO_LINE_LOCATION_ID,
1361 PARENT_DISTRIBUTION_ID,
1362 DISTRIBUTION_NUM,
1363 LEVEL_NUM,
1364 INVOICE_DISTRIBUTION_ID,
1365 PARENT_INVOICE_DIST_ID,
1366 ALLOCATED_AMOUNT,
1367 PARENT_AMOUNT,
1368 AMOUNT,
1369 QUANTITY,
1370 PRICE,
1371 LINE_TYPE,
1372 LAST_UPDATE_DATE,
1373 LAST_UPDATED_BY,
1374 CREATION_DATE,
1375 CREATED_BY,
1376 REQUEST_ID,
1377 PROGRAM_APPLICATION_ID,
1378 PROGRAM_ID,
1379 PROGRAM_UPDATE_DATE,
1380 LAST_UPDATE_LOGIN
1381 )
1382 VALUES (
1383 l_header_id,
1384 cst_rcv_acq_cost_details_s.nextval,
1385 'LCM',
1386 l_po_line_loc,
1387 NULL,
1388 -1,
1389 0,
1390 NULL,
1391 NULL,
1392 NULL,
1393 NULL,
1394 l_net_qty_rec*l_lcm_acq_cost,
1395 l_net_qty_rec,
1396 l_lcm_acq_cost,
1397 NULL,
1398 SYSDATE,
1399 i_user_id,
1400 SYSDATE,
1401 i_user_id,
1402 i_req_id,
1403 i_prog_appl_id,
1404 i_prog_id,
1405 SYSDATE,
1406 i_login_id);
1407
1408 END IF;
1409 END;
1410
1411 END IF;/*LCM enabled*/
1412 -- Added Perf bug# 5214447. Issuing intermediate commits after processing preset No. of rows.
1413
1414 IF l_recs_processed >= l_commit_records_count THEN
1415 IF g_debug = 'Y' THEN
1416 fnd_file.put_line(fnd_file.LOG, ' 500 txns processed.... Issuing Commit ');
1417 END IF;
1418 l_recs_processed := 0;
1419 COMMIT;
1420 END IF;
1421
1422 END LOOP; -- Receipts loop for acquisition cost processor
1423 /*LCM Adjustment*/
1424 DECLARE
1425 l_qty_del_exp_sub NUMBER;
1426 l_qty_del_asset_sub NUMBER;
1427 l_tot_qty_received NUMBER;
1428 l_lcm_abs_acct_id NUMBER;
1429 l_lcm_var_acct_id NUMBER;
1430 l_rcv_insp_acct_id NUMBER;
1431 l_exp_acct_id NUMBER;
1432 l_new_landed_cost NUMBER;
1433 l_prior_landed_cost NUMBER;
1434 l_prior_period NUMBER;
1435 l_header_id cst_rcv_acq_costs.header_id%TYPE;
1436 l_exp_account_id NUMBER;
1437 l_transaction_id NUMBER;
1438 l_rcv_accounting_event_id NUMBER;
1439 l_dr_flag BOOLEAN;
1440 l_uom_control NUMBER;
1441 l_master_org_id NUMBER;
1442 l_avcu_txn_date DATE;
1443 l_um_rate NUMBER;
1444 l_master_uom_code mtl_system_items.Primary_UOM_CODE%TYPE;
1445 BEGIN
1446 l_stmt_num := 1095;
1447 SELECT nvl(max(LANDED_COST_VAR_ACCOUNT),-1),
1448 nvl(max(LANDED_COST_ABS_ACCOUNT),-1)
1449 INTO l_lcm_var_acct_id,
1450 l_lcm_abs_acct_id
1451 FROM CST_ORG_COST_GROUP_ACCOUNTS
1452 WHERE legal_entity_id = l_legal_entity
1453 AND cost_type_id = i_cost_type_id
1454 AND cost_group_id = i_cost_group_id;
1455
1456 l_stmt_num := 1097;
1457
1458 SELECT mia.control_level,
1459 ccg.organization_id
1460 INTO l_uom_control,l_master_org_id
1461 FROM mtl_item_attributes mia,
1462 cst_cost_groups ccg
1463 WHERE mia.attribute_name = 'MTL_SYSTEM_ITEMS.PRIMARY_UNIT_OF_MEASURE'
1464 AND ccg.cost_group_id = i_cost_group_id;
1465
1466 l_avcu_txn_date := least(l_end_date,sysdate);
1467
1468 FOR c_rec IN c_lcm_adj(l_start_date,l_end_date) LOOP
1469 l_recs_processed := l_recs_processed + 1;
1470 l_stmt_num := 1100;
1471 Delete from mtl_pac_txn_cost_details mptcd
1472 where mptcd.transaction_id IN ( SELECT mmt.transaction_id
1473 FROM mtl_material_transactions mmt,
1474 cst_rcv_acq_costs_adj craca
1475 WHERE mmt.rcv_transaction_id
1476 = c_rec.rcv_transaction_id
1477 AND mmt.transaction_source_id
1478 = craca.header_id
1479 AND craca.rcv_transaction_id
1480 = c_rec.rcv_transaction_id
1481 AND craca.cost_group_id
1482 = i_cost_group_id
1483 AND craca.period_id = i_period
1484 AND craca.cost_type_id
1485 = i_cost_type_id
1486 AND mmt.transaction_action_id = 24
1487 AND mmt.transaction_type_id = 26
1488 AND mmt.transaction_source_type_id = 14);
1489
1490 l_stmt_num := 1110;
1491 Delete from mtl_material_transactions mmt
1492 where mmt.rcv_transaction_id = c_rec.rcv_transaction_id
1493 AND mmt.transaction_action_id = 24
1494 AND mmt.transaction_type_id = 26
1495 AND mmt.transaction_source_type_id = 14
1496 AND mmt.transaction_source_id IN ( select craca.header_id
1497 from cst_rcv_acq_costs_adj craca
1498 where craca.period_id = i_period
1499 and craca.cost_group_id = i_cost_group_id
1500 and craca.rcv_transaction_id = c_rec.rcv_transaction_id
1501 and craca.cost_type_id = i_cost_type_id );
1502
1503 l_stmt_num := 1120;
1504 Delete from rcv_accounting_events rae
1505 WHERE rae.event_type_id IN (18,19,20)
1506 AND rae.rcv_transaction_id = c_rec.rcv_transaction_id
1507 AND rae.event_source_id IN ( select header_id from cst_rcv_acq_costs_adj craca
1508 where craca.rcv_transaction_id = c_rec.rcv_transaction_id
1509 and cost_group_id = i_cost_group_id
1510 and period_id = i_period
1511 and cost_type_id = i_cost_type_id
1512 );
1513 l_stmt_num := 1125;
1514 Delete from cst_rcv_acq_cost_details_adj cracda
1515 where cracda.header_id = (select header_id from cst_rcv_acq_costs_adj craca
1516 where craca.rcv_transaction_id = c_rec.rcv_transaction_id
1517 and cost_group_id = i_cost_group_id
1518 and period_id = i_period
1519 and cost_type_id = i_cost_type_id);
1520
1521 l_stmt_num := 1130;
1522
1523 Delete from cst_rcv_acq_costs_adj crac
1524 where crac.rcv_transaction_id = c_rec.rcv_transaction_id
1525 and cost_group_id = i_cost_group_id
1526 and period_id = i_period
1527 and cost_type_id = i_cost_type_id;
1528
1529 l_stmt_num := 1140;
1530
1531 l_tot_qty_received := get_nqr(i_transaction_id => c_rec.rcv_transaction_id,
1532 i_source_flag => 2,
1533 i_start_date => l_start_date,
1534 /*passing this as start date as we want total received prior to this period*/
1535 i_end_date => l_start_date,
1536 i_res_flag => l_res_invoices,
1537 o_err_num => l_err_num); -- in pri uom
1538 IF (l_err_num <> 0) THEN
1539 RAISE CST_FAIL_GET_NQR;
1540 END IF;
1541
1542 /* GET NEW LANDED COST*/
1543 l_stmt_num := 1160;
1544 /*SELECT new_landed_cost
1545 INTO l_new_landed_cost
1546 FROM cst_lc_adj_transactions
1547 WHERE transaction_id = c_rec.transaction_id;*/
1548 SELECT Max(new_landed_cost)
1549 INTO l_new_landed_cost
1550 FROM
1551 (
1552 SELECT new_landed_cost,transaction_id,
1553 max(transaction_id) OVER ( PARTITION BY transaction_date)
1554 max_transaction_id
1555 FROM
1556 (SELECT new_landed_cost,transaction_id,transaction_date,
1557 max(transaction_date) OVER (PARTITION BY rcv_transaction_id)
1558 max_transaction_date
1559 FROM cst_lc_adj_transactions
1560 WHERE rcv_transaction_id = c_rec.rcv_transaction_id
1561 AND organization_id = c_rec.organization_id
1562 AND transaction_date BETWEEN l_start_date
1563 AND l_end_date)
1564 WHERE transaction_date = max_transaction_date
1565 )
1566 WHERE transaction_id = max_transaction_id;
1567 /* GET PRIOR LANDED COST*/
1568 l_stmt_num := 1170;
1569 /*SELECT nvl(max(period_id),-1)
1570 INTO l_prior_period
1571 FROM cst_rcv_acq_costs_adj
1572 WHERE rcv_transaction_id = c_rec.rcv_transaction_id
1573 AND cost_group_id = i_cost_group_id
1574 AND cost_type_id = i_cost_type_id;
1575 IF (l_prior_period <> -1) THEN
1576 l_stmt_num := 1180;
1577 SELECT craca.acquisition_cost
1578 INTO l_prior_landed_cost
1579 FROM cst_rcv_acq_costs_adj craca
1580 WHERE craca.rcv_transaction_id = c_rec.rcv_transaction_id
1581 AND craca.cost_type_id = i_cost_type_id
1582 AND craca.cost_group_id = i_cost_group_id
1583 AND craca.period_id = l_prior_period;
1584 */
1585 select nvl(max(acquisition_cost),-1)
1586 into l_prior_landed_cost
1587 from (
1588 select craca.acquisition_cost,
1589 craca.period_id,
1590 max(craca.period_id) OVER (PARTITION BY craca.rcv_transaction_id)
1591 max_period_id
1592 from cst_rcv_acq_costs_adj craca
1593 WHERE craca.rcv_transaction_id = c_rec.rcv_transaction_id
1594 AND craca.cost_type_id = i_cost_type_id
1595 AND craca.cost_group_id = i_cost_group_id)
1596 where period_id = max_period_id;
1597 IF (l_prior_landed_cost = -1) THEN
1598 l_stmt_num := 1190;
1599 SELECT nvl(max(crac3.acquisition_cost),-1)
1600 INTO l_prior_landed_cost
1601 FROM cst_rcv_acq_costs crac3
1602 WHERE crac3.cost_type_id = i_cost_type_id
1603 AND crac3.cost_group_id = i_cost_group_id
1604 AND crac3.rcv_transaction_id = c_rec.rcv_transaction_id;
1605
1606 IF (l_prior_landed_cost = -1) THEN
1607 l_stmt_num := 1200;
1608 /* SELECT nvl(max(clat1.new_landed_cost),-1)
1609 INTO l_prior_landed_cost
1610 FROM cst_lc_adj_transactions clat1
1611 WHERE clat1.rcv_transaction_id = c_rec.rcv_transaction_id
1612 AND clat1.transaction_date < l_start_date
1613 AND clat1.transaction_id = ( SELECT max(clat2.transaction_id)
1614 FROM cst_lc_adj_transactions clat2
1615 WHERE clat2.rcv_transaction_id =
1616 c_rec.rcv_transaction_id
1617 AND clat2.transaction_date <
1618 l_start_date
1619 );*/
1620 SELECT nvl(Max(new_landed_cost),-1)
1621 INTO l_prior_landed_cost
1622 FROM
1623 (
1624 SELECT new_landed_cost,transaction_id,
1625 max(transaction_id) OVER ( PARTITION BY transaction_date)
1626 max_transaction_id
1627 FROM
1628 (SELECT new_landed_cost,transaction_id,transaction_date,
1629 max(transaction_date) OVER (PARTITION BY rcv_transaction_id)
1630 max_transaction_date
1631 FROM cst_lc_adj_transactions
1632 WHERE rcv_transaction_id = c_rec.rcv_transaction_id
1633 AND transaction_date < l_start_date
1634 )
1635 WHERE transaction_date = max_transaction_date
1636 )
1637 WHERE transaction_id = max_transaction_id;
1638 IF l_prior_landed_cost = -1 THEN
1639 l_prior_landed_cost := c_rec.unit_landed_cost;
1640 END IF;
1641 END IF;
1642 END IF;
1643 IF (l_prior_landed_cost <> l_new_landed_cost) THEN
1644 l_stmt_num := 1210;
1645 SELECT cst_rcv_acq_costs_s.nextval
1646 INTO l_header_id
1647 FROM dual;
1648
1649 l_stmt_num := 1220;
1650
1651 Insert_into_acqhdr_tables(
1652 i_header_id => l_header_id,
1653 i_cost_group_id => i_cost_group_id,
1654 i_cost_type_id => i_cost_type_id,
1655 i_period_id => i_period,
1656 i_rcv_transaction_id => c_rec.rcv_transaction_id,
1657 i_net_quantity_received => l_tot_qty_received,
1658 i_total_quantity_invoiced => NULL,
1659 i_quantity_at_po_price => 0,
1660 i_total_invoice_amount => NULL,
1661 i_amount_at_po_price => 0,
1662 i_total_amount => l_tot_qty_received*l_new_landed_cost,
1663 i_costed_quantity => l_tot_qty_received,
1664 i_acquisition_cost => l_new_landed_cost,
1665 i_po_line_location_id => c_rec.line_location_id,
1666 i_po_unit_price => c_rec.po_unit_price,
1667 i_primary_uom => c_rec.primary_uom_code,
1668 i_rec_exchg_rate => c_rec.rate,
1669 i_last_update_date => SYSDATE,
1670 i_last_updated_by => i_user_id,
1671 i_creation_date => SYSDATE,
1672 i_created_by => i_user_id,
1673 i_request_id => i_req_id,
1674 i_program_application_id => i_prog_appl_id,
1675 i_program_id => i_prog_id,
1676 i_program_update_date => SYSDATE,
1677 i_last_update_login => i_login_id,
1678 i_source_flag => 2,
1679 o_err_num => l_err_num,
1680 o_err_msg => l_err_msg );
1681
1682 l_stmt_num := 1230;
1683 INSERT INTO cst_rcv_acq_cost_details_adj (
1684 HEADER_ID,
1685 DETAIL_ID,
1686 SOURCE_TYPE,
1687 PO_LINE_LOCATION_ID,
1688 PARENT_DISTRIBUTION_ID,
1689 DISTRIBUTION_NUM,
1690 LEVEL_NUM,
1691 INVOICE_DISTRIBUTION_ID,
1692 PARENT_INVOICE_DIST_ID,
1693 ALLOCATED_AMOUNT,
1694 PARENT_AMOUNT,
1695 AMOUNT,
1696 QUANTITY,
1697 PRICE,
1698 LINE_TYPE,
1699 LAST_UPDATE_DATE,
1700 LAST_UPDATED_BY,
1701 CREATION_DATE,
1702 CREATED_BY,
1703 REQUEST_ID,
1704 PROGRAM_APPLICATION_ID,
1705 PROGRAM_ID,
1706 PROGRAM_UPDATE_DATE,
1707 LAST_UPDATE_LOGIN
1708 )
1709 VALUES (
1710 l_header_id,
1711 cst_rcv_acq_cost_details_s.nextval,
1712 'LCM',
1713 c_rec.line_location_id,
1714 NULL,
1715 -1,
1716 0,
1717 NULL,
1718 NULL,
1719 NULL,
1720 NULL,
1721 l_tot_qty_received*l_new_landed_cost,
1722 l_tot_qty_received,
1723 l_new_landed_cost,
1724 NULL,
1725 SYSDATE,
1726 i_user_id,
1727 SYSDATE,
1728 i_user_id,
1729 i_req_id,
1730 i_prog_appl_id,
1731 i_prog_id,
1732 SYSDATE,
1733 i_login_id);
1734 l_stmt_num := 1240;
1735 /* Insert PAC LCM ADJUST RECEIVE INTO RAE */
1736 IF (c_rec.po_distribution_id <> -1) THEN
1737 INSERT into RCV_ACCOUNTING_EVENTS(
1738 accounting_event_id,
1739 last_update_date,
1740 last_updated_by,
1741 last_update_login,
1742 creation_date,
1743 created_by,
1744 request_id,
1745 program_application_id,
1746 program_id,
1747 program_udpate_date,
1748 rcv_transaction_id,
1749 event_type_id,
1750 event_source,
1751 event_source_id,
1752 set_of_books_id,
1753 org_id,
1754 organization_id,
1755 debit_account_id,
1756 credit_account_id,
1757 transaction_date,
1758 source_doc_quantity,
1759 transaction_quantity,
1760 primary_quantity,
1761 source_doc_unit_of_measure,
1762 transaction_unit_of_measure,
1763 primary_unit_of_measure,
1764 po_header_id,
1765 po_release_id,
1766 po_line_id,
1767 po_line_location_id,
1768 po_distribution_id,
1769 inventory_item_id,
1770 unit_price,
1771 prior_unit_price,
1772 currency_conversion_rate)
1773 (SELECT
1774 rcv_accounting_event_s.NEXTVAL,
1775 sysdate,
1776 i_user_id,
1777 i_login_id,
1778 sysdate,
1779 i_user_id,
1780 i_req_id,
1781 i_prog_appl_id,
1782 i_prog_id,
1783 sysdate,
1784 c_rec.rcv_transaction_id,
1785 18,
1786 'PAC_LCM_ADJ_REC' ,
1787 l_header_id,
1788 l_sob_id,
1789 c_rec.org_id,
1790 c_rec.organization_id,
1791 decode(sign(l_tot_qty_received*
1792 (l_new_landed_cost-l_prior_landed_cost)),-1,
1793 l_lcm_abs_acct_id,
1794 c_rec.receiving_account_id),
1795 decode(sign(l_tot_qty_received*
1796 (l_new_landed_cost-l_prior_landed_cost)),-1,
1797 c_rec.receiving_account_id,
1798 l_lcm_abs_acct_id),
1799 l_avcu_txn_date,
1800 l_tot_qty_received ,
1801 l_tot_qty_received ,
1802 l_tot_qty_received ,
1803 c_rec.unit_of_measure,
1804 c_rec.unit_of_measure,
1805 c_rec.unit_of_measure,
1806 c_rec.po_header_id,
1807 c_rec.po_release_id,
1808 c_rec.po_line_id,
1809 c_rec.line_location_id,
1810 c_rec.po_distribution_id,
1811 c_rec.inventory_item_id,
1812 l_new_landed_cost unit_price,
1813 l_prior_landed_cost,
1814 1
1815 FROM DUAL);
1816 ELSE
1817 l_stmt_num := 1245;
1818 INSERT into RCV_ACCOUNTING_EVENTS(
1819 accounting_event_id,
1820 last_update_date,
1821 last_updated_by,
1822 last_update_login,
1823 creation_date,
1824 created_by,
1825 request_id,
1826 program_application_id,
1827 program_id,
1828 program_udpate_date,
1829 rcv_transaction_id,
1830 event_type_id,
1831 event_source,
1832 event_source_id,
1833 set_of_books_id,
1834 org_id,
1835 organization_id,
1836 debit_account_id,
1837 credit_account_id,
1838 transaction_date,
1839 source_doc_quantity,
1840 transaction_quantity,
1841 primary_quantity,
1842 source_doc_unit_of_measure,
1843 transaction_unit_of_measure,
1844 primary_unit_of_measure,
1845 po_header_id,
1846 po_release_id,
1847 po_line_id,
1848 po_line_location_id,
1849 po_distribution_id,
1850 inventory_item_id,
1851 unit_price,
1852 prior_unit_price,
1853 currency_conversion_rate)
1854 (SELECT
1855 rcv_accounting_event_s.NEXTVAL,
1856 sysdate,
1857 i_user_id,
1858 i_login_id,
1859 sysdate,
1860 i_user_id,
1861 i_req_id,
1862 i_prog_appl_id,
1863 i_prog_id,
1864 sysdate,
1865 c_rec.rcv_transaction_id,
1866 18,
1867 'PAC_LCM_ADJ_REC' ,
1868 l_header_id,
1869 l_sob_id,
1870 c_rec.org_id,
1871 c_rec.organization_id,
1872 decode(sign(l_tot_qty_received*
1873 (l_new_landed_cost-l_prior_landed_cost)),-1,
1874 l_lcm_abs_acct_id,
1875 c_rec.receiving_account_id),
1876 decode(sign(l_tot_qty_received*
1877 (l_new_landed_cost-l_prior_landed_cost)),-1,
1878 c_rec.receiving_account_id,
1879 l_lcm_abs_acct_id),
1880 l_avcu_txn_date,
1881 l_tot_qty_received*POD.quantity_ordered/c_rec.poll_quantity source_doc_quantity,
1882 l_tot_qty_received*POD.quantity_ordered/c_rec.poll_quantity transaction_quantity,
1883 l_tot_qty_received*POD.quantity_ordered/c_rec.poll_quantity primary_quantity,
1884 c_rec.unit_of_measure,
1885 c_rec.unit_of_measure,
1886 c_rec.unit_of_measure,
1887 c_rec.po_header_id,
1888 c_rec.po_release_id,
1889 c_rec.po_line_id,
1890 c_rec.line_location_id,
1891 pod.po_distribution_id,
1892 c_rec.inventory_item_id,
1893 l_new_landed_cost unit_price,
1894 l_prior_landed_cost,
1895 1
1896 FROM po_distributions_all pod
1897 WHERE pod.line_location_id = c_rec.line_location_id);
1898 END IF;
1899 /* NOW INSERT THE RAE FOR DELIVERY */
1900 FOR C_REC2 IN c_lcm_del(c_rec.rcv_transaction_id,
1901 l_start_date,
1902 c_rec.organization_id ) LOOP
1903 IF (C_REC2.asset_inventory = 1
1904 AND C_REC.inventory_asset_flag ='Y' ) THEN
1905 l_stmt_num := 1250;
1906 INSERT into RCV_ACCOUNTING_EVENTS(
1907 accounting_event_id,
1908 last_update_date,
1909 last_updated_by,
1910 last_update_login,
1911 creation_date,
1912 created_by,
1913 request_id,
1914 program_application_id,
1915 program_id,
1916 program_udpate_date,
1917 rcv_transaction_id,
1918 event_type_id,
1919 event_source,
1920 event_source_id,
1921 set_of_books_id,
1922 org_id,
1923 organization_id,
1924 debit_account_id,
1925 credit_account_id,
1926 transaction_date,
1927 source_doc_quantity,
1928 transaction_quantity,
1929 primary_quantity,
1930 source_doc_unit_of_measure,
1931 transaction_unit_of_measure,
1932 primary_unit_of_measure,
1933 po_header_id,
1934 po_release_id,
1935 po_line_id,
1936 po_line_location_id,
1937 po_distribution_id,
1938 inventory_item_id,
1939 unit_price,
1940 prior_unit_price,
1941 currency_conversion_rate)
1942 VALUES
1943 (
1944 rcv_accounting_event_s.NEXTVAL,
1945 sysdate,
1946 i_user_id,
1947 i_login_id,
1948 sysdate,
1949 i_user_id,
1950 i_req_id,
1951 i_prog_appl_id,
1952 i_prog_id,
1953 sysdate,
1954 c_rec.rcv_transaction_id,
1955 19,
1956 'PAC_LCM_ADJ_DEL_ASSET' ,
1957 l_header_id,
1958 l_sob_id,
1959 c_rec.org_id,
1960 c_rec.organization_id,
1961 decode(sign(c_rec2.primary_quantity*
1962 (l_new_landed_cost-l_prior_landed_cost)),-1,
1963 c_rec.receiving_account_id,
1964 l_lcm_abs_acct_id),
1965 decode(sign(c_rec2.primary_quantity*
1966 (l_new_landed_cost-l_prior_landed_cost)),-1,
1967 l_lcm_abs_acct_id,
1968 c_rec.receiving_account_id),
1969 l_avcu_txn_date,
1970 c_rec2.primary_quantity ,
1971 c_rec2.primary_quantity ,
1972 c_rec2.primary_quantity ,
1973 c_rec.unit_of_measure,
1974 c_rec.unit_of_measure,
1975 c_rec.unit_of_measure,
1976 c_rec.po_header_id,
1977 c_rec.po_release_id,
1978 c_rec.po_line_id,
1979 c_rec.line_location_id,
1980 c_rec2.po_distribution_id,
1981 c_rec.inventory_item_id,
1982 l_new_landed_cost ,
1983 l_prior_landed_cost,
1984 1
1985 )
1986 Returning accounting_event_id INTO l_rcv_accounting_event_id;
1987 l_stmt_num := 1260;
1988 select MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL
1989 into l_transaction_id
1990 from dual;
1991 l_stmt_num := 1265;
1992 l_um_rate := 1;
1993 CSTPPINV.get_um_rate(
1994 i_txn_org_id => c_rec.organization_id,
1995 i_master_org_id => l_master_org_id,
1996 i_txn_cost_group_id => i_cost_group_id,
1997 i_txfr_cost_group_id => -1,
1998 i_txn_action_id => 24,
1999 i_item_id => c_rec.inventory_item_id,
2000 i_uom_control => l_uom_control,
2001 i_user_id => i_user_id,
2002 i_login_id => i_login_id,
2003 i_request_id => i_req_id,
2004 i_prog_id => i_prog_id,
2005 i_prog_appl_id => i_prog_appl_id,
2006 o_um_rate => l_um_rate,
2007 o_err_num => l_err_num,
2008 o_err_code => l_err_code,
2009 o_err_msg => l_err_msg
2010 );
2011 IF (l_err_num <> 0) THEN
2012 RAISE PROCESS_ERROR;
2013 END IF;
2014 l_stmt_num := 1268;
2015 SELECT msi.primary_uom_code
2016 INTO l_master_uom_code
2017 FROM mtl_system_items msi
2018 WHERE msi.organization_id = l_master_org_id
2019 AND msi.inventory_item_id = c_rec.inventory_item_id;
2020 l_stmt_num := 1270;
2021 INSERT INTO MTL_MATERIAL_TRANSACTIONS
2022 (transaction_id,
2023 last_update_date,
2024 last_updated_by,
2025 creation_date,
2026 created_by,
2027 inventory_item_id,
2028 organization_id,
2029 transaction_type_id,
2030 transaction_action_id,
2031 transaction_source_type_id,
2032 transaction_quantity,
2033 transaction_uom,
2034 primary_quantity,
2035 transaction_date,
2036 value_change,
2037 material_account,
2038 material_overhead_account,
2039 resource_account,
2040 outside_processing_account,
2041 overhead_account,
2042 costed_flag,
2043 org_cost_group_id,
2044 cost_type_id,
2045 source_code,
2046 source_line_id,
2047 expense_account_id,
2048 rcv_transaction_id,
2049 transaction_source_id,
2050 subinventory_code)
2051 VALUES (
2052 l_transaction_id,
2053 sysdate,
2054 i_user_id,
2055 sysdate,
2056 i_user_id,
2057 c_rec.inventory_item_id,
2058 l_master_org_id,
2059 26,
2060 24,
2061 14,
2062 c_rec2.primary_quantity* l_um_rate,
2063 l_master_uom_code,
2064 c_rec2.primary_quantity* l_um_rate,
2065 l_avcu_txn_date,
2066 (c_rec2.primary_quantity*
2067 (l_new_landed_cost-l_prior_landed_cost)),
2068 l_lcm_abs_acct_id,
2069 l_lcm_abs_acct_id,
2070 l_lcm_abs_acct_id,
2071 l_lcm_abs_acct_id,
2072 l_lcm_abs_acct_id,
2073 NULL,
2074 i_cost_group_id,
2075 i_cost_type_id,
2076 'PACLCMADJ',
2077 l_rcv_accounting_event_id,
2078 l_lcm_var_acct_id,
2079 c_rec.rcv_transaction_id,
2080 l_header_id,
2081 c_rec2.subinventory_code
2082 );
2083
2084 /* insert into MPTCD */
2085 l_stmt_num := 1280;
2086 Insert into mtl_pac_txn_cost_details
2087 (cost_group_id,
2088 transaction_id,
2089 pac_period_id,
2090 cost_type_id,
2091 cost_element_id,
2092 level_type,
2093 inventory_item_id,
2094 value_change,
2095 transaction_cost,
2096 last_update_date,
2097 last_updated_by,
2098 creation_date,
2099 created_by )
2100 Values (i_cost_group_id,
2101 l_transaction_id,
2102 i_period,
2103 i_cost_type_id,
2104 1, -- cost element ID
2105 1, -- THis level
2106 c_rec.inventory_item_id,
2107 (c_rec2.primary_quantity*
2108 (l_new_landed_cost-l_prior_landed_cost)),
2109 l_prior_landed_cost/l_um_rate,
2110 sysdate,
2111 i_user_id,
2112 sysdate,
2113 i_user_id);
2114
2115 ELSE
2116 l_stmt_num := 1300;
2117 IF (sign(c_rec2.primary_quantity*(l_new_landed_cost-l_prior_landed_cost)) = -1 ) THEN
2118 l_dr_flag := "FALSE";
2119 ELSE
2120 l_dr_flag := "TRUE";
2121 END IF;
2122
2123 l_exp_account_id := CSTPAPHK.get_account_id (
2124 c_rec.rcv_transaction_id,
2125 l_legal_entity,
2126 i_cost_type_id,
2127 i_cost_group_id,
2128 l_dr_flag,
2129 2,
2130 1,
2131 NULL,
2132 c_rec2.subinventory_code,
2133 "TRUE",
2134 l_err_num,
2135 l_err_code,
2136 l_err_msg);
2137 IF (l_err_num<>0 AND
2138 l_err_num is not null) then
2139 RAISE process_error;
2140 END IF;
2141 IF (l_exp_account_id = -1) THEN
2142 l_stmt_num := 1310;
2143 SELECT nvl(expense_account, -1)
2144 INTO l_exp_account_id
2145 FROM mtl_fiscal_cat_accounts
2146 WHERE legal_entity_id = l_legal_entity
2147 AND cost_type_id = i_cost_type_id
2148 AND cost_group_id = i_cost_group_id
2149 AND category_id = (SELECT mic.category_id
2150 FROM mtl_item_categories mic
2151 WHERE mic.inventory_item_id =
2152 c_rec.inventory_item_id
2153 AND mic.organization_id =
2154 c_rec.organization_id
2155 AND mic.category_set_id =
2156 (SELECT category_set_id
2157 FROM mtl_default_category_sets
2158 WHERE functional_area_id = 5)
2159 );
2160 END IF;
2161 l_stmt_num := 1320;
2162 INSERT into RCV_ACCOUNTING_EVENTS(
2163 accounting_event_id,
2164 last_update_date,
2165 last_updated_by,
2166 last_update_login,
2167 creation_date,
2168 created_by,
2169 request_id,
2170 program_application_id,
2171 program_id,
2172 program_udpate_date,
2173 rcv_transaction_id,
2174 event_type_id,
2175 event_source,
2176 event_source_id,
2177 set_of_books_id,
2178 org_id,
2179 organization_id,
2180 debit_account_id,
2181 credit_account_id,
2182 transaction_date,
2183 source_doc_quantity,
2184 transaction_quantity,
2185 primary_quantity,
2186 source_doc_unit_of_measure,
2187 transaction_unit_of_measure,
2188 primary_unit_of_measure,
2189 po_header_id,
2190 po_release_id,
2191 po_line_id,
2192 po_line_location_id,
2193 po_distribution_id,
2194 inventory_item_id,
2195 unit_price,
2196 prior_unit_price,
2197 currency_conversion_rate)
2198 ( SELECT
2199 rcv_accounting_event_s.NEXTVAL,
2200 sysdate,
2201 i_user_id,
2202 i_login_id,
2203 sysdate,
2204 i_user_id,
2205 i_req_id,
2206 i_prog_appl_id,
2207 i_prog_id,
2208 sysdate,
2209 c_rec.rcv_transaction_id,
2210 20,
2211 'PAC_LCM_ADJ_DEL_EXP' ,
2212 l_header_id,
2213 l_sob_id,
2214 c_rec.org_id,
2215 c_rec.organization_id,
2216 decode(sign(c_rec2.primary_quantity*
2217 (l_new_landed_cost-l_prior_landed_cost)),-1,
2218 c_rec.receiving_account_id,
2219 l_exp_account_id),
2220 decode(sign(c_rec2.primary_quantity*
2221 (l_new_landed_cost-l_prior_landed_cost)),-1,
2222 l_exp_account_id,
2223 c_rec.receiving_account_id),
2224 l_avcu_txn_date,
2225 c_rec2.primary_quantity source_doc_quantity,
2226 c_rec2.primary_quantity transaction_quantity,
2227 c_rec2.primary_quantity primary_quantity,
2228 c_rec.unit_of_measure,
2229 c_rec.unit_of_measure,
2230 c_rec.unit_of_measure,
2231 c_rec.po_header_id,
2232 c_rec.po_release_id,
2233 c_rec.po_line_id,
2234 c_rec.line_location_id,
2235 c_rec2.po_distribution_id,
2236 c_rec.inventory_item_id,
2237 l_new_landed_cost unit_price,
2238 l_prior_landed_cost,
2239 1
2240 FROM DUAL);
2241 END IF;
2242 END LOOP;
2243 IF l_recs_processed >= l_commit_records_count THEN
2244 IF g_debug = 'Y' THEN
2245 fnd_file.put_line(fnd_file.LOG, ' 500 txns processed.... Issuing Commit ');
2246 END IF;
2247 l_recs_processed := 0;
2248 COMMIT;
2249 END IF;
2250 END IF; /*prior landed cost <> new landed cost*/
2251 END LOOP;
2252 /* Update the primary_quantity of the MMT with total adjusted QTY */
2253 l_stmt_num := 1330;
2254 UPDATE mtl_material_transactions mmt
2255 SET (primary_quantity,
2256 transaction_quantity)
2257 = ( SELECT sum(mmt2.primary_quantity),
2258 sum(mmt2.transaction_quantity)
2259 FROM mtl_material_transactions mmt2
2260 WHERE mmt2.inventory_item_id =
2261 mmt.inventory_item_id
2262 AND mmt2.transaction_action_id = 24
2263 AND mmt2.transaction_type_id = 26
2264 AND mmt2.transaction_source_type_id = 14
2265 AND mmt2.transaction_date = l_avcu_txn_date
2266 AND mmt2.source_code = 'PACLCMADJ'
2267 AND mmt2.org_cost_group_id = i_cost_group_id
2268 AND mmt2.cost_type_id = i_cost_type_id
2269 AND mmt2.organization_id = l_master_org_id
2270 )
2271 WHERE mmt.transaction_action_id = 24
2272 AND mmt.transaction_type_id = 26
2273 AND mmt.transaction_source_type_id = 14
2274 AND mmt.transaction_date = l_avcu_txn_date
2275 AND mmt.source_code = 'PACLCMADJ'
2276 AND mmt.org_cost_group_id = i_cost_group_id
2277 AND mmt.cost_type_id = i_cost_type_id
2278 AND mmt.organization_id = l_master_org_id;
2279
2280 END;
2281 /*LCM Adjustment*/
2282 IF g_debug = 'Y' THEN
2283 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Acq_Cost_Processor >>>');
2284 END IF;
2285
2286 ELSIF i_source_flag = 2 THEN
2287 -------------------------------------------------------------------------------------
2288 -- 2.0 Loop for each receipt when source flag = 2
2289 -- Periodic Acquisition Cost Adjustment Processor
2290 -------------------------------------------------------------------------------------
2291
2292 FOR c_rec IN c_receipts_source_flag_2(l_start_date,l_end_date,i_receipt_no,i_invoice_no) LOOP
2293
2294 l_recs_processed := l_recs_processed + 1;
2295 DECLARE
2296 l_rec_ct NUMBER := 0;
2297 l_nqr rcv_transactions.quantity%TYPE := 0;
2298 l_inv_count number;
2299 l_header cst_rcv_acq_costs.header_id%TYPE;
2300 l_primary_uom mtl_system_items.primary_uom_code%TYPE;
2301 l_po_uom mtl_units_of_measure.uom_code%TYPE;
2302 l_po_uom_code po_lines_all.unit_meas_lookup_code%TYPE;
2303 l_po_price po_lines_all.unit_price%TYPE;
2304 l_rate rcv_transactions.CURRENCY_CONVERSION_RATE%TYPE;
2305 l_po_line_loc po_lines_all.po_line_id%TYPE;
2306 l_item_id mtl_system_items.inventory_item_id%TYPE;
2307 l_org_id rcv_transactions.organization_id%TYPE;
2308 l_poll_quantity po_line_locations_all.quantity%TYPE;
2309 l_pri_poll_quantity po_line_locations_all.quantity%TYPE;
2310 l_po_count NUMBER; -- remove this later
2311 l_nr_tax_rate NUMBER;
2312 l_match_option po_line_locations_all.match_option%TYPE;
2313 l_rec_uom_code rcv_transactions.unit_of_measure%TYPE;
2314 l_rec_uom mtl_units_of_measure.uom_code%TYPE;
2315
2316 BEGIN
2317 If G_DEBUG ='Y' then
2318 fnd_file.put_line(fnd_file.log, 'Transaction: ' ||to_char(c_rec.transaction_id));
2319 end If;
2320
2321 -- Delete from MMT and MPTCD transactions that were created for the
2322 -- previous run for this receipt and then delete from craca and cracda*/
2323
2324 l_stmt_num := 71;
2325 Delete from mtl_pac_txn_cost_details mptcd
2326 where mptcd.transaction_id IN ( select craca.mmt_transaction_id
2327 from cst_rcv_acq_costs_adj craca
2328 where craca.mmt_transaction_id is not null
2329 and craca.period_id = i_period
2330 and craca.cost_group_id = i_cost_group_id
2331 and craca.rcv_transaction_id = c_rec.transaction_id
2332 and craca.cost_type_id = i_cost_type_id );
2333
2334 Delete from mtl_material_transactions mmt
2335 where mmt.transaction_id IN ( select craca.mmt_transaction_id
2336 from cst_rcv_acq_costs_adj craca
2337 where craca.mmt_transaction_id is not null
2338 and craca.period_id = i_period
2339 and craca.cost_group_id = i_cost_group_id
2340 and craca.rcv_transaction_id = c_rec.transaction_id
2341 and craca.cost_type_id = i_cost_type_id );
2342
2343 l_stmt_num := 81;
2344
2345 Delete from cst_rcv_acq_cost_details_adj cracda
2346 where cracda.header_id = (select header_id from cst_rcv_acq_costs_adj craca
2347 where craca.rcv_transaction_id = c_rec.transaction_id
2348 and cost_group_id = i_cost_group_id
2349 and period_id = i_period
2350 and cost_type_id = i_cost_type_id);
2351
2352 l_stmt_num := 91;
2353
2354 Delete from cst_rcv_acq_costs_adj crac
2355 where crac.rcv_transaction_id = c_rec.transaction_id
2356 and cost_group_id = i_cost_group_id
2357 and period_id = i_period
2358 and cost_type_id = i_cost_type_id;
2359
2360 If l_first_time_flag = 0 then
2361 l_first_time_flag := 1;
2362 l_stmt_num := 101;
2363
2364 CSTPPPUR.purge_period_data(i_period,
2365 l_legal_entity,
2366 i_cost_group_id,
2367 0,
2368 i_user_id,
2369 i_login_id,
2370 i_req_id,
2371 i_prog_id,
2372 i_prog_appl_id,
2373 l_err_num,
2374 l_err_code,
2375 l_err_msg);
2376 end if; -- l_first_time_flag
2377
2378 l_rec_ct := 0;
2379
2380
2381 IF l_rec_ct = 0 THEN
2382
2383 -------------------------------------------------------------
2384 -- 2.1 Get net quantity received in primary uom
2385 -------------------------------------------------------------
2386 l_nqr := get_nqr(c_rec.transaction_id,i_source_flag,
2387 l_start_date,l_end_date,l_res_invoices,l_err_num); -- in pri uom
2388
2389 IF (l_err_num <> 0) THEN
2390 RAISE CST_FAIL_GET_NQR;
2391 END IF;
2392
2393 l_stmt_num := 111;
2394
2395 -----------------------------------------------------------
2396 -- Get next header id from sequence
2397 -----------------------------------------------------------
2398 SELECT cst_rcv_acq_costs_s.nextval
2399 INTO l_header
2400 FROM dual;
2401
2402 /* begin changes for dropshipment project */
2403 /* Includes Changes for Service Line Types */
2404 l_stmt_num := 121;
2405 Begin
2406 Select rae.accounting_event_id,
2407 DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work Procurement
2408 'AMOUNT', RAE.TRANSACTION_AMOUNT,
2409 'QUANTITY',rae.unit_price),
2410 INTERCOMPANY_PRICING_OPTION
2411 Into l_accounting_event_id,
2412 l_rae_unit_price,
2413 l_rae_trf_price_flag
2414 From rcv_accounting_events rae,
2415 po_lines_all POL,
2416 po_line_locations_all POLL, -- Added for Complex work Procurement
2417 po_distributions_all POD
2418 Where rae.rcv_transaction_id = c_rec.transaction_id
2419 And rae.event_type_id = 1 -- RECEIVE
2420 And rae.trx_flow_header_id is not null
2421 AND RAE.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
2422 AND POD.PO_LINE_ID = POL.PO_LINE_ID
2423 AND POLL.PO_LINE_ID = POL.PO_LINE_ID; -- Added for Complex work Procurement
2424 Exception
2425 When others then
2426 l_accounting_event_id:= 0;
2427 l_rae_unit_price := 0;
2428 l_rae_trf_price_flag := 1;
2429 End;
2430
2431 If (l_rae_trf_price_flag <> 2) then
2432 l_nr_tax_rate := get_rcv_tax(c_rec.transaction_id);
2433
2434 IF (l_nr_tax_rate is null) THEN
2435 RAISE CST_ACQ_NULL_TAX;
2436 END IF;
2437 Else
2438 l_nr_tax_rate := 0;
2439 End if;
2440 /* dropshipment end */
2441
2442 -------------------------------------------------------------
2443 -- Get the match_option from po_line_locations_all
2444 -- If match_option is P then exch rate has to be the rate at the time of PO
2445 -- If match_option is R then exch rate has to be the rate at the time of Receipt
2446 -------------------------------------------------------------
2447
2448 l_stmt_num := 131;
2449
2450 SELECT nvl(poll.match_option,'P')
2451 INTO l_match_option
2452 FROM po_line_locations_all poll,
2453 rcv_transactions rt7
2454 WHERE
2455 poll.line_location_id = rt7.po_line_location_id
2456 AND rt7.transaction_id = c_rec.transaction_id;
2457
2458
2459 -------------------------------------------------------------
2460 -- if po_line_id in POLL does not exist in POL !!
2461 -- this is due to corrupted data of a line_id in POLL not being in POL
2462 -------------------------------------------------------------
2463
2464 l_stmt_num := 141;
2465
2466 SELECT count(rt2.transaction_id)
2467 INTO l_po_count
2468 FROM rcv_transactions rt2,
2469 po_lines_all pol1,
2470 po_line_locations_all poll1
2471 WHERE rt2.transaction_id = c_rec.transaction_id
2472 AND rt2.po_line_location_id = poll1.line_location_id
2473 AND pol1.po_line_id = poll1.po_line_id
2474 AND ROWNUM < 2;
2475
2476 IF l_po_count = 0 THEN
2477 l_stmt_num := 151;
2478
2479 SELECT
2480 decode(l_rae_trf_price_flag, 2, l_rae_unit_price, (nvl(poll2.price_override,0) + l_nr_tax_rate)),
2481 rt3.po_line_location_id,
2482 nvl(rt3.CURRENCY_CONVERSION_RATE,1) ,
2483 rsl.item_id,
2484 nvl(poll2.unit_meas_lookup_code,rsl.unit_of_measure),
2485 poll2.quantity,
2486 rt3.organization_id,
2487 nvl(poll2.matching_basis,'QUANTITY') /* Bug4762808 */
2488 INTO
2489 l_po_price,
2490 l_po_line_loc,
2491 l_rate,
2492 l_item_id,
2493 l_po_uom_code,
2494 l_poll_quantity,
2495 l_org_id,
2496 l_order_type_lookup_code
2497 FROM
2498 rcv_transactions rt3,
2499 rcv_shipment_lines rsl,
2500 po_line_locations_all poll2
2501 WHERE
2502 rt3.transaction_id = c_rec.transaction_id
2503 AND rt3.po_line_location_id = poll2.line_location_id
2504 AND rsl.shipment_line_id = rt3.shipment_line_id;
2505
2506 ELSE -- l_po_count
2507 ------------------------------------------------------------
2508 -- Get Per Unit PO Price in terms of PO UOM
2509 -- Get PO Line Location Id, Item id, PO UOM, PO Quantity, org
2510 ------------------------------------------------------------
2511
2512 -- price_override is based on PO UOM
2513 -- non_recoverable_tax is based on PO UOM so divide by PO quantity
2514 -- price_override in po currency
2515 -- non_recoverable_tax in po currency
2516 -- po_price will not be converted into functional currency now
2517 -- because we want to use the exch rate at time of receipt
2518
2519 l_stmt_num := 66;
2520
2521 SELECT
2522 -- J Changes ---------------------------------------------------------------
2523 DECODE(POLL3.MATCHING_BASIS,
2524 'AMOUNT', 1 + l_nr_tax_rate,
2525 'QUANTITY',decode(l_rae_trf_price_flag, 2, l_rae_unit_price, (nvl(poll3.price_override,0) + l_nr_tax_rate))),
2526 ----------------------------------------------------------------------------
2527 rt33.po_line_location_id,
2528 rt33.unit_of_measure ,
2529 nvl(pol2.item_id,-1),
2530 nvl(poll3.unit_meas_lookup_code,pol2.unit_meas_lookup_code),
2531 poll3.quantity,
2532 rt33.organization_id,
2533 decode(nvl(poll3.match_option,'P'),
2534 'P',get_po_rate(rt33.transaction_id),
2535 'R',rt33.currency_conversion_rate),
2536 nvl(poll3.matching_basis,'QUANTITY') /* Bug4762808 */
2537 INTO
2538 l_po_price,
2539 l_po_line_loc,
2540 l_rec_uom_code,
2541 l_item_id,
2542 l_po_uom_code,
2543 l_poll_quantity,
2544 l_org_id,
2545 l_rate,
2546 l_order_type_lookup_code
2547 FROM
2548 po_lines_all pol2,
2549 po_line_locations_all poll3,
2550 rcv_transactions rt33
2551 WHERE
2552 rt33.transaction_id = c_rec.transaction_id
2553 AND rt33.po_line_location_id = poll3.line_location_id
2554 AND pol2.po_line_id = poll3.po_line_id;
2555
2556 END IF; -- l_po_count
2557
2558 IF (l_rate is null OR l_rate = -1) THEN
2559 RAISE CST_ACQ_NULL_RATE;
2560 END IF;
2561
2562 l_stmt_num := 68;
2563
2564
2565 /* Bug 4762808 - Service Line Type POs do not have UOM and quantity populated.*/
2566
2567 If l_order_type_lookup_code <> 'AMOUNT' then
2568 ------------------------------------------------------
2569 -- Get UOM code for PO UOM and REC UOM
2570 ------------------------------------------------------
2571
2572 SELECT
2573 mum1.uom_code
2574 INTO
2575 l_po_uom
2576 FROM
2577 mtl_units_of_measure mum1
2578 WHERE
2579 MUM1.UNIT_OF_measure = l_po_uom_code;
2580
2581 l_stmt_num := 71;
2582
2583 SELECT
2584 mum1.uom_code
2585 INTO
2586 l_rec_uom
2587 FROM
2588 mtl_units_of_measure mum1
2589 WHERE
2590 mum1.unit_of_measure = l_rec_uom_code;
2591
2592 l_stmt_num := 31;
2593
2594 ---------------------------------------------------------
2595 -- Get Primary UOM for the Item for the org
2596 ---------------------------------------------------------
2597
2598 IF l_item_id = -1 THEN
2599 l_primary_uom := l_po_uom;
2600 ELSE
2601
2602 l_stmt_num := 76;
2603
2604 SELECT
2605 msi.primary_uom_code
2606 INTO
2607 l_primary_uom
2608 FROM
2609 mtl_system_items msi
2610 WHERE
2611 msi.inventory_item_id = l_item_id AND
2612 msi.organization_id = l_org_id;
2613 END IF;
2614
2615 ---------------------------------------------------------
2616 -- Convert PO Quantity into Primary Quantity
2617 ---------------------------------------------------------
2618
2619 l_stmt_num := 79;
2620
2621 l_pri_poll_quantity := inv_convert.inv_um_convert(
2622 l_item_id,
2623 NULL,
2624 l_poll_quantity, -- PO quantity
2625 l_po_uom, -- PO UOM
2626 l_primary_uom, -- pri uom
2627 NULL,
2628 NULL);
2629
2630 ---------------------------------------------------------
2631 -- PO per unit price in POLL is based on PO UOM
2632 -- Convert the price based on Primary UOM
2633 ---------------------------------------------------------
2634
2635 l_po_price := l_po_price * l_poll_quantity / l_pri_poll_quantity;
2636 End if;
2637
2638 --------------------------------------------------------
2639 -- 2.2 Insert inTO cst_rcV_ACQ_COSTS a row for the receipt
2640 -- for cost type, period, cost group
2641 -- setting quantity_invoiced, quantity_at_po_price,
2642 -- total_invoice_amount, amount_at_po_price, total_amount,
2643 -- costed_quantity, acqcuisition_cost to NULL for now
2644 -- These values will be updated later with the right values.
2645 ----------------------------------------------------------
2646
2647 l_stmt_num := 81;
2648
2649 Insert_into_acqhdr_tables(
2650 l_header,
2651 i_cost_group_id,
2652 i_cost_type_id,
2653 i_period,
2654 c_rec.transaction_id,
2655 l_nqr, -- in pri uom
2656 NULL,
2657 NULL,
2658 NULL,
2659 NULL,
2660 NULL,
2661 NULL,
2662 NULL,
2663 l_po_line_loc,
2664 l_po_price, -- in po currency based on pri uom
2665 l_primary_uom,
2666 l_rate, -- rate at time of receipt
2667 SYSDATE,
2668 i_user_id,
2669 SYSDATE,
2670 i_user_id,
2671 i_req_id,
2672 i_prog_appl_id,
2673 i_prog_id,
2674 SYSDATE,
2675 i_login_id,
2676 i_source_flag,
2677 l_err_num,
2678 l_err_msg);
2679
2680
2681 if (l_accounting_event_id = 0) then --added for dropshipment project
2682 -------------------------------------------------------------
2683 -- 2.3 Get all posted Invoice lines from AP_INVOICE_DISTRIBUTIONS_ALL
2684 -- which are matched to the receipt
2685 --------------------------------------------------------------
2686
2687 l_stmt_num := 86;
2688
2689 Select NVL(restrict_doc_flag,2) into l_res_flag
2690 from CST_LE_COST_TYPES
2691 where legal_entity = l_legal_entity
2692 and cost_type_id = i_cost_type_id;
2693
2694 l_stmt_num := 91;
2695
2696 SELECT count(rcv_transaction_id)
2697 INTO l_inv_count
2698 FROM ap_invoice_distributions_all ad1
2699 WHERE ad1.rcv_transaction_id = c_rec.transaction_id
2700 AND ad1.accounting_date <= l_end_date
2701 AND ad1.posted_flag = 'Y' AND
2702 /* Invoice Lines Project TAX is now REC_TAX and NONREC_TAX */
2703 ad1.line_type_lookup_code <> 'REC_TAX' AND
2704 ROWNUM < 2;
2705
2706 else
2707 l_inv_count := 0;
2708 end if;
2709 --------------------------------------------------------------
2710 -- 2.4 If there are invoices
2711 -- 2.4.1 loop for each invoice dist line
2712 ---------------------------------------------------------------
2713
2714 IF l_inv_count > 0 THEN
2715 DECLARE
2716
2717 CURSOR c_invoices IS
2718 SELECT
2719 ad2.invoice_distribution_id,
2720 ad2.invoice_id,
2721 -- J Changes ------------------------------------------------------------------
2722 nvl(DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work Procurement
2723 'AMOUNT', AD2.AMOUNT,
2724 'QUANTITY',ad2.quantity_invoiced), 0 ) "QUANTITY_INVOICED", -- Invoice UOM
2725 -------------------------------------------------------------------------------
2726 ad2.distribution_line_number,
2727 ad2.line_type_lookup_code,
2728 -- J Changes ------------------------------------------------------------------
2729 nvl(DECODE(POLL.MATCHING_BASIS, -- Changed for Complex work Procurement
2730 'AMOUNT', 1,
2731 'QUANTITY', ad2.unit_price), 0 ) unit_price, -- Invoice Currency
2732 --------------------------------------------------------------------------------
2733 nvl(ad2.base_amount, nvl(ad2.amount, 0)) base_amount
2734 FROM
2735 ap_invoice_distributions_all ad2,
2736 -- J Changes -----------------------------------------------------------
2737 RCV_TRANSACTIONS RT,
2738 PO_LINES_ALL POL,
2739 PO_LINE_LOCATIONS_ALL POLL, -- Added for Complex work Procurement
2740 ap_invoices_all aia /* bug 4352624 Added to ignore invoices of type prepayment */
2741 ------------------------------------------------------------------------
2742 WHERE
2743 ad2.rcv_transaction_id = c_rec.transaction_id
2744 AND ad2.posted_flag = 'Y'
2745 /* bug 4352624 Added to ignore invoices of type prepayment */
2746 AND ad2.line_type_lookup_code <>'PREPAY'
2747 AND aia.invoice_id = ad2.invoice_id
2748 AND aia.invoice_type_lookup_code <>'PREPAYMENT'
2749
2750 -- J Changes -----------------------------------------------------------
2751 AND RT.TRANSACTION_ID = AD2.RCV_TRANSACTION_ID
2752 AND POL.PO_LINE_ID = RT.PO_LINE_ID
2753 AND RT.PO_LINE_LOCATION_ID = POLL.LINE_LOCATION_ID
2754 AND POLL.PO_LINE_ID = POL.PO_LINE_ID ---- Added for Complex work Procurement
2755 ------------------------------------------------------------------------
2756 AND ad2.accounting_date <= l_end_date
2757 /* Invoice Lines Project TAX is now REC_TAX AND NONREC_TAX */
2758 AND ad2.line_type_lookup_code <> 'REC_TAX'
2759 -- J Changes -------------------------------------------------------------
2760 -- Ensure that Price corrections are not picked --
2761 /* Invoice Lines Project root_distribution_id ->
2762 corrected_invoice_dist_id */
2763 AND ad2.corrected_invoice_dist_id is null;
2764 --------------------------------------------------------------------------
2765 l_pri_quantity_invoiced NUMBER;
2766 l_correction_amount NUMBER;
2767 l_corr_inv NUMBER;
2768 l_correction_tax_amount NUMBER; /*Bug3891984*/
2769 l_corr_invoice_id NUMBER; /*Bug3891984*/
2770
2771 BEGIN
2772 FOR c_inv IN c_invoices LOOP
2773 ---------------------------------------------------
2774 -- Check if there are any Price Correction Invoices
2775 -- And if so, get the correction amount
2776 ---------------------------------------------------
2777 IF G_DEBUG = 'Y' THEN
2778 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Invoice: ' ||to_char(c_inv.INVOICE_DISTRIBUTION_ID));
2779 END IF;
2780 BEGIN
2781 -----------------------------------------------------
2782 -- The latest price correction invoice does not
2783 -- have an Invoice of type 'ADJUSTMENT' that reverses
2784 -- it out.
2785 -- Refer AP HLD for Retroactive Pricing
2786 -- Make sure that there are no distributions in AIDA
2787 -- with xinv_parent_distribution_id = inv_dist_id of
2788 -- of the price correction invoice
2789 -----------------------------------------------------
2790 -------------------------------------------------------------------
2791 -- Bug 3891984 : Added the column invoice_id in the following select
2792 -- statement. This invoice id will be required to pick up the
2793 -- PO Price Adjustment invoices having LINE_TYPE_LOOKUP_CODE
2794 -- as 'TAX' with TAX_RECOVERABLE_FLAG set to 'N'
2795 -------------------------------------------------------------------
2796
2797 SELECT AIDA.INVOICE_ID, /*Bug3891984*/
2798 AIDA.INVOICE_DISTRIBUTION_ID, -- Only for debugging
2799 NVL(AIDA.BASE_AMOUNT, NVL(AIDA.AMOUNT, 0))
2800 INTO l_corr_invoice_id,
2801 l_corr_inv,
2802 l_correction_amount
2803 FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA,
2804 AP_INVOICES_ALL AP_INV
2805 /* Invoice Lines Project
2806 No root_distribution_id or xinv_parent_reversal_id
2807 now it'll just be represented by corrected_invoice_dist_id
2808 */
2809 WHERE AIDA.CORRECTED_INVOICE_DIST_ID = c_inv.INVOICE_DISTRIBUTION_ID
2810 AND AIDA.INVOICE_ID = AP_INV.INVOICE_ID
2811 AND AP_INV.INVOICE_TYPE_LOOKUP_CODE = 'PO PRICE ADJUST';
2812
2813 BEGIN
2814 /* Bug3891984 changes starts here */
2815 SELECT NVL(AIDA.BASE_AMOUNT, NVL(AIDA.AMOUNT, 0))
2816 INTO l_correction_tax_amount
2817 FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA
2818 WHERE AIDA.INVOICE_ID = l_corr_invoice_id
2819 /* Invoice Lines Project non-recoverable tax now is just NONREC_TAX */
2820 AND AIDA.LINE_TYPE_LOOKUP_CODE = 'NONREC_TAX';
2821
2822 EXCEPTION
2823 WHEN OTHERS THEN
2824 l_correction_tax_amount := 0;
2825 l_corr_invoice_id := -1;
2826
2827 END;
2828
2829 l_correction_amount:= l_correction_amount + l_correction_tax_amount;
2830
2831 /* Bug3891984 ends here */
2832
2833 EXCEPTION
2834 WHEN OTHERS THEN
2835 l_correction_amount := 0;
2836 l_corr_inv := -1;
2837 END;
2838
2839 IF G_DEBUG = 'Y' THEN
2840 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Latest Price Correction Invoice: '||to_char(l_corr_inv));
2841 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Correction Amount (from Price Correction Invoice): '||to_char(l_correction_amount));
2842 END IF;
2843
2844 BEGIN
2845
2846 --------------------------------------------------------------
2847 -- Convert Invoice Quantity into Primary Units
2848 --------------------------------------------------------------
2849
2850 l_stmt_num := 96;
2851
2852 l_pri_quantity_invoiced := inv_convert.inv_um_convert(
2853 l_item_id,
2854 NULL,
2855 c_inv.quantity_invoiced,
2856 l_rec_uom, -- inv uom same as rec when matched to receipt
2857 l_primary_uom,
2858 NULL,
2859 NULL);
2860
2861 ---------------------------------------------------------------
2862 -- 2.4.1.1 Insert into CST_RCV_ACQ_COST_DETAILS table
2863 ---------------------------------------------------------------
2864
2865 l_stmt_num := 101;
2866
2867 /* bug fix for bug 3411774. The acquisition cost considers the TAX twice if there is a
2868 rcv_transaction_id against it and also if it is allocated to the ITEM lines.
2869 So we should prevent insertion into the details table from the c_reciepts cursor as it will be
2870 inserted into the details table later from the chrg_allocations cursor */
2871
2872 l_chrg_present := 0;
2873
2874 BEGIN
2875
2876 /* Invoice Lines Project no more ap_chrg_allocations_all table */
2877 Select count(*) into l_chrg_present
2878 from ap_invoice_distributions_all
2879 where invoice_distribution_id = c_inv.invoice_distribution_id
2880 and charge_applicable_to_dist_id is not null;
2881
2882 EXCEPTION
2883 WHEN OTHERS THEN
2884 l_chrg_present := 0;
2885
2886 END;
2887
2888 If l_chrg_present = 0 then /* means that this has not been allocated */
2889
2890 select cst_rcv_acq_cost_details_s.nextval into l_details_nextvalue
2891 from dual;
2892
2893 l_stmt_num := 106;
2894
2895 select decode(l_pri_quantity_invoiced,0,0,(c_inv.unit_price * c_inv.quantity_invoiced / l_pri_quantity_invoiced)) into l_priuom_cost
2896 from dual;
2897
2898 l_stmt_num := 111;
2899
2900 Insert_into_acqdtls_tables (
2901 l_header,
2902 l_details_nextvalue,
2903 'INVOICE',
2904 NULL,
2905 c_inv.invoice_distribution_id,
2906 1,
2907 0,
2908 c_inv.invoice_distribution_id,
2909 NULL,
2910 NULL,
2911 NULL,
2912 c_inv.base_amount + l_correction_amount, -- in func currency
2913 l_pri_quantity_invoiced, -- in pri uom
2914 l_priuom_cost, -- convert to price based on pri uom
2915 c_inv.line_type_lookup_code,
2916 SYSDATE,
2917 i_user_id,
2918 SYSDATE,
2919 i_user_id,
2920 i_req_id,
2921 i_prog_appl_id,
2922 i_prog_id,
2923 SYSDATE,
2924 i_login_id,
2925 i_source_flag,
2926 l_err_num,
2927 l_err_msg);
2928
2929 End If; /* end of check for rows to be present in chrg allocations table */
2930 ------------------------------------------------------------
2931 -- 2.4.1.2 Get all special charge lines that are directly
2932 -- or indirectly allocated to the invoice lines
2933 -- (that are matched to the receipt)
2934 ------------------------------------------------------------
2935
2936 l_stmt_num := 121;
2937
2938 get_charge_allocs_for_acqadj(
2939 l_header,
2940 c_inv.invoice_distribution_id,
2941 l_start_date,
2942 l_end_date,
2943 i_user_id,
2944 i_login_id,
2945 i_req_id,
2946 i_prog_id,
2947 i_prog_appl_id,
2948 l_err_num,
2949 l_err_code,
2950 l_err_msg);
2951
2952 IF (l_err_num <> 0) THEN
2953 RAISE CST_FAIL_GET_CHARGE_ALLOCS;
2954 END IF;
2955 END;
2956 END LOOP; -- Invoice loop
2957 END;
2958 END IF; -- If Invoice count > 0
2959
2960 --------------------------------------------------------
2961 -- 2.5 Compute the Acquisition Cost based on the info in CRACD
2962 --------------------------------------------------------
2963
2964 l_stmt_num := 131;
2965
2966 compute_acq_cost_acqadj(
2967 l_header,
2968 l_nqr,
2969 l_po_line_loc,
2970 l_po_price,
2971 l_primary_uom,
2972 l_rate,
2973 l_po_uom,
2974 l_item_id,
2975 i_period,
2976 i_cost_group_id,
2977 l_org_id,
2978 i_cost_type_id,
2979 i_adj_account,
2980 i_user_id,
2981 i_login_id,
2982 i_req_id,
2983 i_prog_id,
2984 i_prog_appl_id,
2985 l_err_num,
2986 l_err_code,
2987 l_err_msg);
2988
2989
2990 IF (l_err_num <> 0) THEN
2991 RAISE CST_FAIL_COMPUTE_ACQ_COST;
2992 END IF;
2993
2994 END IF; -- if hook was used
2995 END;
2996
2997
2998 -- Added Perf bug# 5214447. Issuing intermediate commits after processing preset No. of rows.
2999
3000 IF l_recs_processed >= l_commit_records_count THEN
3001 IF g_debug = 'Y' THEN
3002 fnd_file.put_line(fnd_file.LOG, ' 500 txns processed.... Issuing Commit ');
3003 END IF;
3004 l_recs_processed := 0;
3005 COMMIT;
3006 END IF;
3007
3008 END LOOP; -- Receipts loop for acquisition cost adjustment processor
3009
3010
3011 IF g_debug = 'Y' THEN
3012 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Acq_Cost_Adjustment_Processor >>>');
3013 END IF;
3014
3015 END IF; -- i_source_flag check
3016
3017 EXCEPTION
3018 WHEN CST_FAIL_GET_NQR THEN
3019 o_err_num := 30005;
3020 o_err_code := SQLCODE;
3021 FND_MESSAGE.set_name('BOM', 'CST_FAIL_GET_NQR');
3022 o_err_msg := FND_MESSAGE.Get;
3023 WHEN CST_FAIL_GET_CHARGE_ALLOCS THEN
3024 o_err_num := 30007;
3025 o_err_code := SQLCODE;
3026 FND_MESSAGE.set_name('BOM', 'CST_FAIL_GET_CHARGE_ALLOCS');
3027 o_err_msg := FND_MESSAGE.Get;
3028 o_err_msg := l_err_msg||' : ' ||o_err_msg;
3029 WHEN CST_FAIL_COMPUTE_ACQ_COST THEN
3030 o_err_num := 30008;
3031 o_err_code := SQLCODE;
3032 FND_MESSAGE.set_name('BOM', 'CST_FAIL_COMPUTE_ACQ_COST');
3033 o_err_msg := FND_MESSAGE.Get;
3034 o_err_msg := l_err_msg||' : ' ||o_err_msg;
3035 WHEN CST_FAIL_ACQ_HOOK THEN
3036 o_err_num := 30004;
3037 o_err_code := SQLCODE;
3038 FND_MESSAGE.set_name('BOM', 'CST_FAIL_ACQ_HOOK');
3039 o_err_msg := FND_MESSAGE.Get;
3040 WHEN CST_FAIL_LCM_HOOK THEN
3041 o_err_num := 30015;
3042 o_err_code := SQLCODE;
3043 FND_MESSAGE.set_name('BOM', 'CST_FAIL_LCM_HOOK');
3044 o_err_msg := FND_MESSAGE.Get;
3045 WHEN CST_ACQ_NULL_RATE THEN
3046 o_err_num := 30010;
3047 o_err_code := SQLCODE;
3048 FND_MESSAGE.set_name('BOM', 'CST_ACQ_NULL_RATE');
3049 o_err_msg := FND_MESSAGE.Get;
3050 WHEN CST_ACQ_NULL_TAX THEN
3051 o_err_num := 30011;
3052 o_err_code := SQLCODE;
3053 FND_MESSAGE.set_name('BOM', 'CST_ACQ_NULL_TAX');
3054 o_err_msg := FND_MESSAGE.Get;
3055 WHEN PROCESS_ERROR THEN
3056 o_err_num := l_err_num;
3057 o_err_code := l_err_code;
3058 o_err_msg := l_err_msg;
3059 WHEN OTHERS THEN
3060 o_err_num := 30009;
3061 o_err_code := SQLCODE;
3062 o_err_msg := SUBSTR('CSTPPACQ.acq_cost_processor('
3063 ||to_char(l_stmt_num)
3064 ||'):'
3065 ||SQLERRM,1,240);
3066 END acq_cost_processor;
3067
3068 ------------------------------------------------------------
3069 -- FUNCTION
3070 -- Get_Nqr
3071 -- DESCRIPTION
3072 -- Function returns the Net Quantity Received taking into
3073 -- account returns, corrections etc for a parent receipt.
3074 --
3075 -- Modified 11i.10 to support Service Line Types.
3076 -- Function now returns net Quantity or Amount for a given
3077 -- transaction depending on the PO line type associated
3078 -- with the transaction.
3079 -------------------------------------------------------------
3080
3081 FUNCTION get_nqr(
3082 i_transaction_id IN NUMBER,
3083 i_source_flag IN NUMBER,
3084 i_start_date IN DATE,
3085 i_end_date IN DATE,
3086 i_res_flag IN NUMBER,
3087 o_err_num OUT NOCOPY NUMBER)
3088 RETURN NUMBER
3089 IS
3090 ----------------------------------------------------
3091 -- Get all child transactions level by level
3092 -----------------------------------------------------
3093
3094 CURSOR c_net_amount IS
3095 SELECT RT.TRANSACTION_ID,
3096 RT.TRANSACTION_TYPE,
3097 RT.AMOUNT,
3098 RT.PARENT_TRANSACTION_ID
3099 FROM RCV_TRANSACTIONS RT
3100 WHERE ( ( (i_source_flag = 1)
3101 AND ( ( (i_res_flag =1)
3102 AND (rt.transaction_date between i_start_date and i_end_date))
3103 OR (i_res_flag = 2)))
3104 OR ( ( i_source_flag = 2 ) AND (rt.transaction_date <= i_end_date ) ) )
3105 START WITH
3106 RT.transaction_id = i_transaction_id
3107 CONNECT BY
3108 PRIOR RT.transaction_id = RT.parent_transaction_id;
3109
3110 CURSOR c_nqr is
3111 SELECT
3112 rt4.transaction_id,
3113 rt4.transaction_type,
3114 rt4.primary_quantity,
3115 rt4.quantity,/* ADDED FOR #BUG6697382*/
3116 rt4.parent_transaction_id
3117 FROM
3118 rcv_transactions rt4
3119 WHERE
3120 (((i_source_flag = 1) AND (((i_res_flag =1) AND (rt4.transaction_date between i_start_date and i_end_date)) OR (i_res_flag = 2))) OR ((i_source_flag = 2 ) AND (rt4.transaction_date <= i_end_date)))
3121 START WITH
3122 rt4.transaction_id = i_transaction_id
3123 CONNECT BY
3124 prior rt4.transaction_id = rt4.parent_transaction_id;
3125
3126 l_nqr NUMBER := 0;
3127 l_actual_nqr NUMBER:=0;/* ADDED FOR #BUG6679382*/
3128 l_po_line_type_code VARCHAR2(25);
3129 l_parent_type rcv_transactions.transaction_type%TYPE;
3130 l_stmt_num NUMBER := 0;
3131 BEGIN
3132
3133 ---------------------------------------------------------
3134 -- Initialize error variable
3135 ---------------------------------------------------------
3136
3137 o_err_num := 0;
3138
3139 IF g_debug = 'Y' THEN
3140 FND_FILE.PUT_LINE(FND_FILE.LOG, 'GET_NQR <<< ');
3141 END IF;
3142 -------------------------------------------
3143 -- Determine if PO is for Service Line Type
3144 -------------------------------------------
3145
3146 l_stmt_num := 10;
3147
3148 SELECT NVL(POLL.MATCHING_BASIS, POL.MATCHING_BASIS) -- Changed for Complex work Procurement
3149 INTO L_PO_LINE_TYPE_CODE
3150 FROM PO_LINES_ALL POL,
3151 PO_LINE_LOCATIONS_ALL POLL, -- Added for Complex work Procurement
3152 RCV_TRANSACTIONS RT
3153 WHERE POL.PO_LINE_ID = RT.PO_LINE_ID
3154 AND POLL.LINE_LOCATION_ID = RT.PO_LINE_LOCATION_ID-- Added for Complex work Procurement
3155 AND RT.TRANSACTION_ID = I_TRANSACTION_ID;
3156
3157 IF L_PO_LINE_TYPE_CODE = 'AMOUNT' THEN
3158 -- Service Line Types
3159 FOR c_amount_rec in c_net_amount loop
3160 IF c_amount_rec.transaction_id <> i_transaction_id THEN
3161
3162 l_stmt_num := 20;
3163
3164 SELECT transaction_type
3165 INTO l_parent_type
3166 FROM rcv_transactions
3167 WHERE transaction_id = c_amount_rec.parent_transaction_id;
3168 END IF;
3169
3170 IF c_amount_rec.transaction_id = i_transaction_id THEN
3171 l_nqr := l_nqr + c_amount_rec.amount;
3172 ELSIF c_amount_rec.transaction_type = 'CORRECT' then
3173 IF l_parent_type = 'RECEIVE' OR
3174 l_parent_type = 'MATCH' THEN
3175 l_nqr := l_nqr + c_amount_rec.amount;
3176 ELSIF l_parent_type = 'RETURN TO VENDOR' then
3177 l_nqr := l_nqr - c_amount_rec.amount;
3178 END IF;
3179 ELSIF c_amount_rec.transaction_type = 'RETURN TO VENDOR' then
3180 l_nqr := l_nqr - c_amount_rec.amount;
3181 END IF;
3182 END LOOP; -- child txns loop
3183
3184 ELSE -- Other Line Types (Not Service)
3185 --------------------------------------------------------
3186 -- For each child transaction loop
3187 --------------------------------------------------------
3188 FOR c_nqr_rec in c_nqr loop
3189 --------------------------------------------------------
3190 -- If it is not the parent (that was passed in) transaction itself
3191 --------------------------------------------------------
3192 IF c_nqr_rec.transaction_id <> i_transaction_id THEN
3193 ----------------------------------------------------------
3194 -- Get the parent transaction type
3195 ----------------------------------------------------------
3196 l_stmt_num := 30;
3197 SELECT
3198 rt5.transaction_type
3199 INTO
3200 l_parent_type
3201 FROM
3202 rcv_transactions rt5
3203 WHERE
3204 rt5.transaction_id = c_nqr_rec.parent_transaction_id;
3205 END IF;
3206
3207 ------------------------------------------------------------
3208 -- If it is the parent receive or match transaction
3209 -- then add the quantity to l_nqr
3210 ------------------------------------------------------------
3211 l_stmt_num := 30;
3212
3213 IF c_nqr_rec.transaction_id = i_transaction_id THEN
3214 l_nqr := l_nqr + c_nqr_rec.primary_quantity;
3215 l_actual_nqr := l_actual_nqr + c_nqr_rec.quantity;/* ADDED FOR #BUG6697382*/
3216 -----------------------------------------------------------
3217 -- If the transaction is CORRECT,
3218 -- If parent is receive or match txn, then add the corrected qty
3219 -- If parent is return, then subtract the corrected qty
3220 -----------------------------------------------------------
3221 ELSIF c_nqr_rec.transaction_type = 'CORRECT' then
3222 IF l_parent_type = 'RECEIVE' OR
3223 l_parent_type = 'MATCH' THEN
3224 l_nqr := l_nqr + c_nqr_rec.primary_quantity;
3225 l_actual_nqr := l_actual_nqr + c_nqr_rec.quantity;/* ADDED FOR #BUG6697382*/
3226 ELSIF l_parent_type = 'RETURN TO VENDOR' then
3227 l_nqr := l_nqr - c_nqr_rec.primary_quantity;
3228 l_actual_nqr := l_actual_nqr - c_nqr_rec.quantity;/* ADDED FOR #BUG6697382*/
3229 END IF;
3230 ----------------------------------------------------------
3231 -- If transaction is return transaction, then subtract returned qty
3232 ----------------------------------------------------------
3233 ELSIF c_nqr_rec.transaction_type = 'RETURN TO VENDOR' then
3234 l_nqr := l_nqr - c_nqr_rec.primary_quantity;
3235 l_actual_nqr := l_actual_nqr - c_nqr_rec.quantity;/* ADDED FOR #BUG6697382*/
3236
3237 END IF;
3238 END LOOP; -- child txns loop
3239
3240 /* ADDED If condition for #BUG6697382*/
3241 IF(l_actual_nqr=0) THEN
3242 l_nqr:=0;
3243 END IF;
3244 END IF; -- Line Types
3245 --------------------------------------------------------
3246 -- Return the net quantity received as calculated
3247 --------------------------------------------------------
3248
3249 IF g_debug = 'Y' THEN
3250 FND_FILE.PUT_LINE(FND_FILE.LOG, 'Net Quantity/Amount: '||to_char(l_nqr));
3251 FND_FILE.PUT_LINE(FND_FILE.LOG, 'GET_NQR >>> ');
3252 END IF;
3253 RETURN (l_nqr);
3254 EXCEPTION
3255 WHEN OTHERS THEN
3256 o_err_num := 30006;
3257 END get_nqr;
3258
3259 PROCEDURE get_charge_allocs (
3260 i_hdr IN NUMBER,
3261 i_item_dist IN NUMBER,
3262 i_start_date IN DATE,
3263 i_end_date IN DATE,
3264 i_res_flag IN NUMBER,
3265 i_user_id IN NUMBER,
3266 i_login_id IN NUMBER,
3267 i_req_id IN NUMBER,
3268 i_prog_id IN NUMBER,
3269 i_prog_appl_id IN NUMBER,
3270 o_err_num OUT NOCOPY NUMBER,
3271 o_err_code OUT NOCOPY VARCHAR2,
3272 o_err_msg OUT NOCOPY VARCHAR2)
3273 IS
3274 l_imm_parent NUMBER;
3275 l_factor NUMBER := 1;
3276 l_prev_weight NUMBER;
3277 l_chg_count NUMBER;
3278 l_stmt_num NUMBER := 0;
3279 BEGIN
3280
3281 -----------------------------------------------------
3282 -- Initialize error variables
3283 ----------------------------------------------------
3284
3285 o_err_num := 0;
3286 o_err_code := '';
3287 o_err_msg := '';
3288
3289 l_stmt_num := 10;
3290
3291 -------------------------------------------------------
3292 -- Check if any allocations (both parent and child should be posted)
3293 -------------------------------------------------------
3294 /* Invoice Lines Project
3295 No more ap_chrg_allocations_all table. Now need to get all information
3296 through ap_invoice_distributions_all. To determine if a distribution is a
3297 charge, just examine whether the charge_applicable_to_dist_id is not null
3298 */
3299 SELECT count(1)
3300 INTO l_chg_count
3301 FROM ap_invoice_distributions_all aida
3302 WHERE aida.posted_flag = 'Y'
3303 AND (((i_res_flag = 1)
3304 AND (aida.accounting_date BETWEEN i_start_date AND i_end_Date))
3305 OR (i_res_flag = 2))
3306 AND aida.line_type_lookup_code <> 'REC_TAX'
3307 AND EXISTS (
3308 SELECT 'X'
3309 FROM ap_invoice_distributions_all aida2
3310 WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id
3311 AND aida2.posted_flag = 'Y'
3312 AND (((i_res_flag = 1)
3313 AND (aida2.accounting_date BETWEEN i_start_date AND i_end_Date))
3314 OR (i_res_flag = 2))
3315 AND aida2.line_type_lookup_code <> 'REC_TAX'
3316 )
3317 START WITH
3318 aida.charge_applicable_to_dist_id = i_item_dist
3319 CONNECT BY
3320 prior aida.invoice_distribution_id = aida.charge_applicable_to_dist_id;
3321
3322 ----------------------------------------------------------
3323 -- If any, then process
3324 ---------------------------------------------------------
3325
3326 IF l_chg_count > 0 THEN
3327
3328 l_stmt_num := 20;
3329
3330 -------------------------------------------------------------
3331 -- Insert into CRACD all allocations level by level
3332 -------------------------------------------------------------
3333 /* Invoice Lines Project
3334 No more ap_chrg_allocations_all table. Now need to get all information
3335 through ap_invoice_distributions_all. To determine if a distribution is a
3336 charge, just examine whether the charge_applicable_to_dist_id is not null
3337 */
3338
3339 INSERT INTO
3340 cst_rcv_acq_cost_details ( -- cracd2
3341 HEADER_ID,
3342 DETAIL_ID,
3343 SOURCE_TYPE,
3344 PO_LINE_LOCATION_ID,
3345 PARENT_DISTRIBUTION_ID,
3346 DISTRIBUTION_NUM,
3347 LEVEL_NUM,
3348 INVOICE_DISTRIBUTION_ID,
3349 PARENT_INVOICE_DIST_ID,
3350 ALLOCATED_AMOUNT,
3351 PARENT_AMOUNT,
3352 AMOUNT,
3353 QUANTITY,
3354 PRICE,
3355 LINE_TYPE,
3356 LAST_UPDATE_DATE,
3357 LAST_UPDATED_BY,
3358 CREATION_DATE,
3359 CREATED_BY,
3360 REQUEST_ID,
3361 PROGRAM_APPLICATION_ID,
3362 PROGRAM_ID,
3363 PROGRAM_UPDATE_DATE,
3364 LAST_UPDATE_LOGIN
3365 )
3366 SELECT
3367 i_hdr,
3368 cst_rcv_acq_cost_details_s.nextval,
3369 'INVOICE',
3370 NULL,
3371 i_item_dist,
3372 rownum + 1,
3373 LEVEL,
3374 aida.invoice_distribution_id,
3375 aida.charge_applicable_to_dist_id,
3376 nvl(aida.base_amount,nvl(aida.amount,0)) base_amount,
3377 NULL,
3378 NULL,
3379 NULL,
3380 NULL,
3381 NULL,
3382 SYSDATE,
3383 i_user_id,
3384 SYSDATE,
3385 i_user_id,
3386 i_req_id,
3387 i_prog_appl_id,
3388 i_prog_id,
3389 SYSDATE,
3390 i_login_id
3391 FROM
3392 ap_invoice_distributions_all aida
3393 WHERE aida.posted_flag = 'Y'
3394 AND (((i_res_flag = 1) AND (aida.accounting_date BETWEEN i_start_date AND i_end_Date))
3395 OR (i_res_flag = 2))
3396 AND aida.line_type_lookup_code <> 'REC_TAX'
3397 AND EXISTS (
3398 SELECT 'X'
3399 FROM ap_invoice_distributions_all aida2
3400 WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id
3401 AND aida2.posted_flag = 'Y'
3402 AND (((i_res_flag = 1) AND (aida2.accounting_date BETWEEN i_start_date AND i_end_Date))
3403 OR (i_res_flag = 2))
3404 AND aida2.line_type_lookup_code <> 'REC_TAX'
3405 )
3406 START WITH
3407 aida.charge_applicable_to_dist_id = i_item_dist
3408 CONNECT BY
3409 prior aida.invoice_distribution_id = aida.charge_applicable_to_dist_id;
3410
3411 l_stmt_num := 30;
3412
3413 ----------------------------------------------------------
3414 -- Get the parent amount from the AP_INVOICE_DISTRIBUTIONS_ALL table
3415 -- and also the LINE TYPE
3416 -- and update the CRACD rows just created
3417 ----------------------------------------------------------
3418
3419 UPDATE
3420 cst_rcv_acq_cost_details cracd3
3421 SET
3422 cracd3.parent_amount = (
3423 SELECT
3424 nvl(ad7.base_amount,nvl(ad7.amount,0))
3425 FROM
3426 ap_invoice_distributions_all ad7
3427 WHERE
3428 ad7.invoice_distribution_id = cracd3.parent_invoice_dist_id) ,
3429 cracd3.line_type = (
3430 SELECT
3431 ad8.line_type_lookup_code
3432 FROM
3433 ap_invoice_distributions_all ad8
3434 WHERE
3435 ad8.invoice_distribution_id = cracd3.invoice_distribution_id)
3436 WHERE
3437 cracd3.parent_invoice_dist_id IS NOT NULL AND
3438 cracd3.invoice_distribution_id IS NOT NULL AND
3439 cracd3.parent_distribution_id = i_item_dist AND
3440 cracd3.header_id = i_hdr;
3441
3442 l_stmt_num := 40;
3443
3444 -----------------------------------------------------------
3445 -- Set amount as allocated amount for the level 1 lines
3446 -- since the whole allocated amount goes to level 0
3447 -- for other levels, the portion that goes to previous level
3448 -- is determined by the number of parents it has
3449 -----------------------------------------------------------
3450 /* Invoice Lines Project
3451 In the new model, all charges are 100% allocated to its parent so
3452 the amount and allocated amount columns are identical
3453 */
3454
3455 /* bug 4965847 changed query to join with parent_invoice_dist_id rather than parent_distribution_id*/
3456
3457 UPDATE
3458 cst_rcv_acq_cost_details cracd4
3459 SET
3460 cracd4.amount = cracd4.allocated_amount -- amount in func curr
3461 WHERE
3462 cracd4.header_id = i_hdr AND
3463 cracd4.PARENT_INVOICE_DIST_ID = i_item_dist;
3464
3465 -------------------------------------------------------
3466 -- Loop for all the rows inserted
3467 ------------------------------------------------------
3468 /* Invoice Lines Project
3469 The rest of this code tries to figure out the allocation percentages of charges
3470 to their parents. In the new model, charges are 100% allocated to their parent so
3471 there is no need to perform these calculations */
3472 END IF; -- If charge allocations exist
3473
3474 EXCEPTION
3475 WHEN OTHERS THEN
3476 o_err_num := 30001;
3477 o_err_code := SQLCODE;
3478 o_err_msg := SUBSTR('CSTPPACQ.get_charge_allocs('
3479 ||to_char(l_stmt_num)
3480 ||'):'
3481 ||SQLERRM,1,240);
3482 END get_charge_allocs;
3483
3484
3485
3486 Procedure get_charge_allocs_for_acqadj(
3487 i_hdr IN NUMBER,
3488 i_item_dist IN NUMBER,
3489 l_start_date IN DATE,
3490 l_end_date IN DATE,
3491 i_user_id IN NUMBER,
3492 i_login_id IN NUMBER,
3493 i_req_id IN NUMBER,
3494 i_prog_id IN NUMBER,
3495 i_prog_appl_id IN NUMBER,
3496 o_err_num OUT NOCOPY NUMBER,
3497 o_err_code OUT NOCOPY VARCHAR2,
3498 o_err_msg OUT NOCOPY VARCHAR2)
3499 IS
3500 l_imm_parent NUMBER;
3501 l_factor NUMBER := 1;
3502 l_prev_weight NUMBER;
3503 l_chg_count NUMBER;
3504 l_stmt_num NUMBER := 0;
3505 BEGIN
3506
3507 -----------------------------------------------------
3508 -- Initialize error variables
3509 ----------------------------------------------------
3510
3511 o_err_num := 0;
3512 o_err_code := '';
3513 o_err_msg := '';
3514
3515 l_stmt_num := 10;
3516
3517 -------------------------------------------------------
3518 -- Check if any allocations (both parent and child should be posted)
3519 -------------------------------------------------------
3520 /* Invoice Lines Project
3521 No more ap_chrg_allocations_all table. Now need to get all information
3522 through ap_invoice_distributions_all. To determine if a distribution is a
3523 charge, just examine whether the charge_applicable_to_dist_id is not null
3524 */
3525
3526 SELECT
3527 count(1)
3528 INTO
3529 l_chg_count
3530 FROM
3531 ap_invoice_distributions_all aida
3532 WHERE aida.posted_flag = 'Y'
3533 AND aida.accounting_date <= l_end_date
3534 AND aida.line_type_lookup_code <> 'REC_TAX'
3535 AND EXISTS (
3536 SELECT 'X'
3537 FROM ap_invoice_distributions_all aida2
3538 WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id
3539 AND aida2.posted_flag = 'Y'
3540 AND aida2.accounting_date <= l_end_date
3541 AND aida2.line_type_lookup_code <> 'REC_TAX'
3542 )
3543 START WITH
3544 aida.charge_applicable_to_dist_id = i_item_dist
3545 CONNECT BY
3546 prior aida.invoice_distribution_id = aida.charge_applicable_to_dist_id;
3547
3548 ----------------------------------------------------------
3549 -- If any, then process
3550 ---------------------------------------------------------
3551
3552
3553 IF l_chg_count > 0 THEN
3554
3555 l_stmt_num := 20;
3556
3557 -------------------------------------------------------------
3558 -- Insert into CRACD all allocations level by level
3559 -------------------------------------------------------------
3560 /* Invoice Lines Project
3561 No more ap_chrg_allocations_all table. Now need to get all information
3562 through ap_invoice_distributions_all. To determine if a distribution is a
3563 charge, just examine whether the charge_applicable_to_dist_id is not null
3564 */
3565
3566 INSERT INTO
3567 cst_rcv_acq_cost_details_adj ( -- cracd2
3568 HEADER_ID,
3569 DETAIL_ID,
3570 SOURCE_TYPE,
3571 PO_LINE_LOCATION_ID,
3572 PARENT_DISTRIBUTION_ID,
3573 DISTRIBUTION_NUM,
3574 LEVEL_NUM,
3575 INVOICE_DISTRIBUTION_ID,
3576 PARENT_INVOICE_DIST_ID,
3577 ALLOCATED_AMOUNT,
3578 PARENT_AMOUNT,
3579 AMOUNT,
3580 QUANTITY,
3581 PRICE,
3582 LINE_TYPE,
3583 LAST_UPDATE_DATE,
3584 LAST_UPDATED_BY,
3585 CREATION_DATE,
3586 CREATED_BY,
3587 REQUEST_ID,
3588 PROGRAM_APPLICATION_ID,
3589 PROGRAM_ID,
3590 PROGRAM_UPDATE_DATE,
3591 LAST_UPDATE_LOGIN
3592 )
3593 SELECT
3594 i_hdr,
3595 cst_rcv_acq_cost_details_s.nextval,
3596 'INVOICE',
3597 NULL,
3598 i_item_dist,
3599 rownum + 1,
3600 LEVEL,
3601 aida.invoice_distribution_id,
3602 aida.charge_applicable_to_dist_id,
3603 nvl(aida.base_amount,nvl(aida.amount,0)) base_amount,
3604 NULL,
3605 NULL,
3606 NULL,
3607 NULL,
3608 NULL,
3609 SYSDATE,
3610 i_user_id,
3611 SYSDATE,
3612 i_user_id,
3613 i_req_id,
3614 i_prog_appl_id,
3615 i_prog_id,
3616 SYSDATE,
3617 i_login_id
3618 FROM
3619 ap_invoice_distributions_all aida
3620 WHERE aida.posted_flag = 'Y'
3621 AND aida.accounting_date <= l_end_date
3622 AND aida.line_type_lookup_code <> 'REC_TAX'
3623 AND EXISTS (
3624 SELECT 'X'
3625 FROM ap_invoice_distributions_all aida2
3626 WHERE aida2.invoice_distribution_id = aida.charge_applicable_to_dist_id
3627 AND aida2.posted_flag = 'Y'
3628 AND aida2.accounting_date <= l_end_date
3629 AND aida2.line_type_lookup_code <> 'REC_TAX'
3630 )
3631 START WITH
3632 aida.charge_applicable_to_dist_id = i_item_dist
3633 CONNECT BY
3634 prior aida.invoice_distribution_id = aida.charge_applicable_to_dist_id;
3635
3636 l_stmt_num := 30;
3637
3638 ----------------------------------------------------------
3639 -- Get the parent amount from the AP_INVOICE_DISTRIBUTIONS_ALL table
3640 -- and also the LINE TYPE
3641 -- and update the CRACD rows just created
3642 ----------------------------------------------------------
3643
3644
3645 UPDATE
3646 cst_rcv_acq_cost_details_adj cracd3
3647 SET
3648 cracd3.parent_amount = (
3649 SELECT
3650 nvl(ad7.base_amount,nvl(ad7.amount,0))
3651 FROM
3652 ap_invoice_distributions_all ad7
3653 WHERE
3654 ad7.invoice_distribution_id = cracd3.parent_invoice_dist_id) ,
3655 cracd3.line_type = (
3656 SELECT
3657 ad8.line_type_lookup_code
3658 FROM
3659 ap_invoice_distributions_all ad8
3660 WHERE
3661 ad8.invoice_distribution_id = cracd3.invoice_distribution_id)
3662 WHERE
3663 cracd3.parent_invoice_dist_id IS NOT NULL AND
3664 cracd3.invoice_distribution_id IS NOT NULL AND
3665 cracd3.parent_distribution_id = i_item_dist AND
3666 cracd3.header_id = i_hdr;
3667
3668 l_stmt_num := 40;
3669
3670 -----------------------------------------------------------
3671 -- Set amount as allocated amount for the level 1 lines
3672 -- since the whole allocated amount goes to level 0
3673 -- for other levels, the portion that goes to previous level
3674 -- is determined by the number of parents it has
3675 -----------------------------------------------------------
3676 /* Invoice Lines Project
3677 In the new model, all charges are 100% allocated to its parent so
3678 the amount and allocated amount columns are identical
3679 */
3680
3681 /* bug 4965847 changed query to join with parent_invoice_dist_id rather than parent_distribution_id*/
3682
3683 UPDATE
3684 cst_rcv_acq_cost_details_adj cracd4
3685 SET
3686 cracd4.amount = cracd4.allocated_amount -- amount in func curr
3687 WHERE
3688 cracd4.header_id = i_hdr AND
3689 cracd4.PARENT_INVOICE_DIST_ID = i_item_dist;
3690
3691 -------------------------------------------------------
3692 -- Loop for all the rows inserted
3693 ------------------------------------------------------
3694 /* Invoice Lines Project
3695 The rest of this code tries to figure out the allocation percentages of charges
3696 to their parents. In the new model, charges are 100% allocated to their parent so
3697 there is no need to perform these calculations */
3698 END IF; -- If charge allocations exist
3699
3700 EXCEPTION
3701 WHEN OTHERS THEN
3702 o_err_num := 30001;
3703 o_err_code := SQLCODE;
3704 o_err_msg := SUBSTR('CSTPPACQ.get_charge_allocs_for_acqadj('
3705 ||to_char(l_stmt_num)
3706 ||'):'
3707 ||SQLERRM,1,240);
3708 END get_charge_allocs_for_acqadj;
3709
3710
3711
3712 PROCEDURE compute_acq_cost (
3713 i_header IN NUMBER,
3714 i_nqr IN NUMBER,
3715 i_po_line_loc IN NUMBER,
3716 i_po_price IN NUMBER,
3717 i_primary_uom IN VARCHAR2,
3718 i_rate IN NUMBER,
3719 i_po_uom IN VARCHAR2,
3720 i_item IN NUMBER,
3721 i_user_id IN NUMBER,
3722 i_login_id IN NUMBER,
3723 i_req_id IN NUMBER,
3724 i_prog_id IN NUMBER,
3725 i_prog_appl_id IN NUMBER,
3726 o_err_num OUT NOCOPY NUMBER,
3727 o_err_code OUT NOCOPY VARCHAR2,
3728 o_err_msg OUT NOCOPY VARCHAR2)
3729 IS
3730 l_total_invoice_amount cst_rcv_acq_cost_details.amount%TYPE;
3731 l_qty_invoiced cst_rcv_acq_cost_details.quantity%TYPE;
3732 l_qty_at_po cst_rcv_acq_cost_details.quantity%TYPE;
3733 l_costed_quantity cst_rcv_acq_cost_details.quantity%TYPE;
3734 l_amount_at_po cst_rcv_acq_cost_details.amount%TYPE;
3735 l_total_amount cst_rcv_acq_cost_details.amount%TYPE;
3736 l_acq_cost cst_rcv_acq_costs.acquisition_cost%TYPE;
3737 l_cracd_count NUMBER := 0;
3738 l_stmt_num NUMBER := 0;
3739 CST_NULL_ACQ_COST EXCEPTION;
3740 BEGIN
3741
3742 --------------------------------------------------------
3743 -- Initialize error variables
3744 ---------------------------------------------------------
3745
3746 o_err_num := 0;
3747 o_err_code := '';
3748 o_err_msg := '';
3749
3750 l_stmt_num := 10;
3751
3752 ---------------------------------------------------------
3753 -- Check if any rows in CRACD for the header
3754 -- If there are none, that means no invoices were matched to receipt
3755 -- acq cost will be PO price
3756 -- If there are some, then invoices were matched and acq cost will
3757 -- be a combination of PO and invoice price
3758 -----------------------------------------------------------
3759
3760 SELECT count(header_id)
3761 INTO l_cracd_count
3762 FROM cst_rcv_acq_cost_details cracd9
3763 WHERE cracd9.header_id = i_header
3764 AND ROWNUM < 2;
3765
3766 -------------------------------------------------------------
3767 -- If invoices were matched
3768 ------------------------------------------------------------
3769
3770 IF l_cracd_count > 0 THEN
3771
3772 l_stmt_num := 20;
3773
3774 ----------------------------------------------------------
3775 -- Get total invoice amount
3776 ---------------------------------------------------------
3777
3778 SELECT
3779 SUM(cracd10.amount)
3780 INTO
3781 l_total_invoice_amount
3782 FROM
3783 cst_rcv_acq_cost_details cracd10
3784 WHERE
3785 cracd10.header_id = i_header;
3786
3787 ----------------------------------------------------------
3788 -- Get total invoice quantity
3789 ---------------------------------------------------------
3790
3791 l_stmt_num := 25;
3792
3793 SELECT
3794 SUM(nvl(cracd11.quantity,0))
3795 INTO
3796 l_qty_invoiced
3797 FROM
3798 cst_rcv_acq_cost_details cracd11
3799 WHERE
3800 cracd11.header_id = i_header;
3801
3802 ELSE
3803
3804 --------------------------------------------------------------
3805 -- Set Total Invoice amount and quantity to zero
3806 --------------------------------------------------------------
3807
3808 l_total_invoice_amount := 0;
3809 l_qty_invoiced := 0;
3810
3811 END IF;
3812
3813 -------------------------------------------------------------
3814 -- If total invoice quantity is greater than the net qty recd,
3815 -- then acq cost is to be calculated based on invoice quantity
3816 -- else acq cost is to be calculated based on net qty recd
3817 -------------------------------------------------------------
3818 IF l_qty_invoiced > i_nqr THEN
3819 l_qty_at_po := 0;
3820 l_costed_quantity := l_qty_invoiced;
3821 ELSE
3822 l_qty_at_po := i_nqr - l_qty_invoiced;
3823 l_costed_quantity := i_nqr;
3824 END IF;
3825
3826 --------------------------------------------------------------
3827 -- Calculate amount at po, total amount and acq cost
3828 --------------------------------------------------------------
3829
3830 l_amount_at_po := l_qty_at_po * i_po_price * i_rate; -- po price in pri
3831 l_total_amount := l_total_invoice_amount + l_amount_at_po;
3832 IF l_costed_quantity = 0 THEN
3833 l_acq_cost := i_po_price * i_rate; /* changed for bug 3090599 */
3834 ELSE
3835 l_acq_cost := l_total_amount / l_costed_quantity;
3836 END IF;
3837
3838 l_stmt_num := 30;
3839
3840
3841 ----------------------------------------------------------------
3842 -- Check if acquisition cost is null
3843 ---------------------------------------------------------------
3844
3845 IF (l_acq_cost is null) THEN
3846 RAISE CST_NULL_ACQ_COST;
3847 END IF;
3848 -----------------------------------------------------------------
3849 -- update CRACD with the calculated values
3850 ----------------------------------------------------------------
3851
3852 l_stmt_num := 28;
3853
3854 UPDATE
3855 cst_rcv_acq_costs crac2
3856 SET
3857 crac2.total_invoice_amount = l_total_invoice_amount,
3858 crac2.total_quantity_invoiced = l_qty_invoiced,
3859 crac2.quantity_at_po_price = l_qty_at_po,
3860 crac2.amount_at_po_price = l_amount_at_po,
3861 crac2.total_amount = l_total_amount,
3862 crac2.costed_quantity = l_costed_quantity,
3863 crac2.acquisition_cost = l_acq_cost
3864 WHERE
3865 crac2.header_id = i_header;
3866
3867 --------------------------------------------------------------
3868 -- If the qty at po was not zero, then insert a row for source type PO
3869 --------------------------------------------------------------
3870
3871 IF l_qty_at_po <> 0 THEN
3872
3873 l_stmt_num := 40;
3874
3875 INSERT INTO
3876 cst_rcv_acq_cost_details ( --cracd12
3877 HEADER_ID,
3878 DETAIL_ID,
3879 SOURCE_TYPE,
3880 PO_LINE_LOCATION_ID,
3881 PARENT_DISTRIBUTION_ID,
3882 DISTRIBUTION_NUM,
3883 LEVEL_NUM,
3884 INVOICE_DISTRIBUTION_ID,
3885 PARENT_INVOICE_DIST_ID,
3886 ALLOCATED_AMOUNT,
3887 PARENT_AMOUNT,
3888 AMOUNT,
3889 QUANTITY,
3890 PRICE,
3891 LINE_TYPE,
3892 LAST_UPDATE_DATE,
3893 LAST_UPDATED_BY,
3894 CREATION_DATE,
3895 CREATED_BY,
3896 REQUEST_ID,
3897 PROGRAM_APPLICATION_ID,
3898 PROGRAM_ID,
3899 PROGRAM_UPDATE_DATE,
3900 LAST_UPDATE_LOGIN
3901 )
3902 VALUES (
3903 i_header,
3904 cst_rcv_acq_cost_details_s.nextval,
3905 'PO',
3906 i_po_line_loc,
3907 NULL,
3908 -1,
3909 0,
3910 NULL,
3911 NULL,
3912 NULL,
3913 NULL,
3914 l_amount_at_po,
3915 l_qty_at_po,
3916 i_po_price,
3917 NULL,
3918 SYSDATE,
3919 i_user_id,
3920 SYSDATE,
3921 i_user_id,
3922 i_req_id,
3923 i_prog_appl_id,
3924 i_prog_id,
3925 SYSDATE,
3926 i_login_id);
3927 END IF;
3928
3929 EXCEPTION
3930 WHEN CST_NULL_ACQ_COST THEN
3931 o_err_num := 30014;
3932 o_err_code := SQLCODE;
3933 FND_MESSAGE.set_name('BOM', 'CST_NULL_ACQ_COST');
3934 o_err_msg := FND_MESSAGE.Get;
3935 WHEN OTHERS THEN
3936 o_err_num := 30002;
3937 o_err_code := SQLCODE;
3938 o_err_msg := SUBSTR('CSTPPACQ.compute_acq_cost('
3939 ||to_char(l_stmt_num)
3940 ||'):'
3941 ||SQLERRM,1,240);
3942
3943 END compute_acq_cost;
3944
3945
3946 Procedure compute_acq_cost_acqadj(
3947 i_header IN NUMBER,
3948 i_nqr IN NUMBER,
3949 i_po_line_loc IN NUMBER,
3950 i_po_price IN NUMBER,
3951 i_primary_uom IN VARCHAR2,
3952 i_rate IN NUMBER,
3953 i_po_uom IN VARCHAR2,
3954 i_item IN NUMBER,
3955 i_pac_period_id IN NUMBER,
3956 i_cost_group_id IN NUMBER,
3957 i_org_id IN NUMBER,
3958 i_cost_type_id IN NUMBER,
3959 i_adj_account IN NUMBER,
3960 i_user_id IN NUMBER,
3961 i_login_id IN NUMBER,
3962 i_req_id IN NUMBER,
3963 i_prog_id IN NUMBER,
3964 i_prog_appl_id IN NUMBER,
3965 o_err_num OUT NOCOPY NUMBER,
3966 o_err_code OUT NOCOPY VARCHAR2,
3967 o_err_msg OUT NOCOPY VARCHAR2)
3968 IS
3969 l_total_invoice_amount cst_rcv_acq_cost_details_adj.amount%TYPE;
3970 l_qty_invoiced cst_rcv_acq_cost_details_adj.quantity%TYPE;
3971 l_qty_at_po cst_rcv_acq_cost_details_adj.quantity%TYPE;
3972 l_costed_quantity cst_rcv_acq_cost_details_adj.quantity%TYPE;
3973 l_amount_at_po cst_rcv_acq_cost_details_adj.amount%TYPE;
3974 l_total_amount cst_rcv_acq_cost_details_adj.amount%TYPE;
3975 l_acq_cost cst_rcv_acq_costs_adj.acquisition_cost%TYPE;
3976 l_cracd_count NUMBER := 0;
3977 l_stmt_num NUMBER := 0;
3978 l_acq_adjustment_amount NUMBER := 0;
3979 l_old_increments NUMBER :=0;
3980 l_legal_entity NUMBER;
3981 l_prev_period_id NUMBER;
3982 l_ori_acq_amount NUMBER;
3983 l_prior_period_quantity NUMBER;
3984 l_prior_period_cost NUMBER;
3985 l_legal_entity_id NUMBER;
3986 l_transaction_id NUMBER;
3987 l_period_close_date DATE;
3988 l_least_date DATE;
3989 l_material_account NUMBER(15);
3990 l_material_overhead_account NUMBER(15);
3991 l_outside_processing_account NUMBER(15);
3992 l_resource_account NUMBER(15);
3993 l_overhead_account NUMBER(15);
3994 l_rcv_txn_id NUMBER;
3995 l_original_acq_cost NUMBER :=0;
3996 l_rcv_txn_date DATE;
3997 l_rtv_qty NUMBER :=0;
3998 l_rtv_adj_amount NUMBER :=0;
3999 l_net_qty_received NUMBER :=0;
4000 l_original_qty_received NUMBER :=0;
4001 l_item NUMBER;
4002 l_wip_entity_id NUMBER;
4003
4004
4005
4006
4007 CST_NULL_ACQ_COST EXCEPTION;
4008 CONC_STATUS BOOLEAN;
4009 BEGIN
4010
4011 --------------------------------------------------------
4012 -- Initialize error variables
4013 ---------------------------------------------------------
4014 o_err_num := 0;
4015 o_err_code := '';
4016 o_err_msg := '';
4017
4018 l_stmt_num := 10;
4019
4020 ---------------------------------------------------------
4021 -- Check if any rows in CRACD for the header
4022 -- If there are none, that means no invoices were matched to receipt
4023 -- acq cost will be PO price
4024 -- If there are some, then invoices were matched and acq cost will
4025 -- be a combination of PO and invoice price
4026 -----------------------------------------------------------
4027
4028 SELECT count(header_id)
4029 INTO l_cracd_count
4030 FROM cst_rcv_acq_cost_details_adj cracd9
4031 WHERE cracd9.header_id = i_header
4032 AND ROWNUM < 2;
4033
4034 -------------------------------------------------------------
4035 -- If invoices were matched
4036 ------------------------------------------------------------
4037
4038 IF l_cracd_count > 0 THEN
4039
4040 l_stmt_num := 20;
4041
4042 ----------------------------------------------------------
4043 -- Get total invoice amount
4044 ---------------------------------------------------------
4045 SELECT
4046 SUM(cracd10.amount)
4047 INTO
4048 l_total_invoice_amount
4049 FROM
4050 cst_rcv_acq_cost_details_adj cracd10
4051 WHERE
4052 cracd10.header_id = i_header;
4053
4054 ----------------------------------------------------------
4055 -- Get total invoice quantity
4056 ---------------------------------------------------------
4057
4058 l_stmt_num := 25;
4059
4060 SELECT
4061 SUM(nvl(cracd11.quantity,0))
4062 INTO
4063 l_qty_invoiced
4064 FROM
4065 cst_rcv_acq_cost_details_adj cracd11
4066 WHERE
4067 cracd11.header_id = i_header;
4068
4069 ELSE
4070
4071 --------------------------------------------------------------
4072 -- Set Total Invoice amount and quantity to zero
4073 --------------------------------------------------------------
4074
4075 l_total_invoice_amount := 0;
4076 l_qty_invoiced := 0;
4077
4078 END IF;
4079
4080 -------------------------------------------------------------
4081 -- If total invoice quantity is greater than the net qty recd,
4082 -- then acq cost is to be calculated based on invoice quantity
4083 -- else acq cost is to be calculated based on net qty recd
4084 -------------------------------------------------------------
4085 IF l_qty_invoiced > i_nqr THEN
4086 l_qty_at_po := 0;
4087 l_costed_quantity := l_qty_invoiced;
4088 ELSE
4089 l_qty_at_po := i_nqr - l_qty_invoiced;
4090 l_costed_quantity := i_nqr;
4091 END IF;
4092
4093 --------------------------------------------------------------
4094 -- Calculate amount at po, total amount and acq cost
4095 --------------------------------------------------------------
4096
4097 l_amount_at_po := l_qty_at_po * i_po_price * i_rate; -- po price in pri
4098 l_total_amount := l_total_invoice_amount + l_amount_at_po;
4099 IF l_costed_quantity = 0 THEN
4100 l_acq_cost := i_po_price * i_rate; /* changed for bug 3090599 */
4101 ELSE
4102 l_acq_cost := l_total_amount / l_costed_quantity;
4103 END IF;
4104
4105 l_stmt_num := 30;
4106
4107
4108 ----------------------------------------------------------------
4109 -- Check if acquisition cost is null
4110 ---------------------------------------------------------------
4111
4112 IF (l_acq_cost is null) THEN
4113 RAISE CST_NULL_ACQ_COST;
4114 END IF;
4115 -----------------------------------------------------------------
4116 -- update CRACD with the calculated values
4117 ----------------------------------------------------------------
4118 UPDATE
4119 cst_rcv_acq_costs_adj crac2
4120 SET
4121 crac2.total_invoice_amount = l_total_invoice_amount,
4122 crac2.total_quantity_invoiced = l_qty_invoiced,
4123 crac2.quantity_at_po_price = l_qty_at_po,
4124 crac2.amount_at_po_price = l_amount_at_po,
4125 crac2.total_amount = l_total_amount,
4126 crac2.costed_quantity = l_costed_quantity,
4127 crac2.acquisition_cost = l_acq_cost
4128 WHERE
4129 crac2.header_id = i_header;
4130
4131 --------------------------------------------------------------
4132 -- If the qty at po was not zero, then insert a row for source type PO
4133 --------------------------------------------------------------
4134
4135 IF l_qty_at_po <> 0 THEN
4136
4137 l_stmt_num := 40;
4138
4139 INSERT INTO
4140 cst_rcv_acq_cost_details_adj ( --cracd12
4141 HEADER_ID,
4142 DETAIL_ID,
4143 SOURCE_TYPE,
4144 PO_LINE_LOCATION_ID,
4145 PARENT_DISTRIBUTION_ID,
4146 DISTRIBUTION_NUM,
4147 LEVEL_NUM,
4148 INVOICE_DISTRIBUTION_ID,
4149 PARENT_INVOICE_DIST_ID,
4150 ALLOCATED_AMOUNT,
4151 PARENT_AMOUNT,
4152 AMOUNT,
4153 QUANTITY,
4154 PRICE,
4155 LINE_TYPE,
4156 LAST_UPDATE_DATE,
4157 LAST_UPDATED_BY,
4158 CREATION_DATE,
4159 CREATED_BY,
4160 REQUEST_ID,
4161 PROGRAM_APPLICATION_ID,
4162 PROGRAM_ID,
4163 PROGRAM_UPDATE_DATE,
4164 LAST_UPDATE_LOGIN
4165 )
4166 VALUES (
4167 i_header,
4168 cst_rcv_acq_cost_details_s.nextval,
4169 'PO',
4170 i_po_line_loc,
4171 NULL,
4172 -1,
4173 0,
4174 NULL,
4175 NULL,
4176 NULL,
4177 NULL,
4178 l_amount_at_po,
4179 l_qty_at_po,
4180 i_po_price,
4181 NULL,
4182 SYSDATE,
4183 i_user_id,
4184 SYSDATE,
4185 i_user_id,
4186 i_req_id,
4187 i_prog_appl_id,
4188 i_prog_id,
4189 SYSDATE,
4190 i_login_id);
4191 END IF;
4192
4193 /* now calculate the amount to be posted as the adjustment amount */
4194
4195 If G_DEBUG = 'Y' then
4196
4197 fnd_file.put_line(fnd_file.log,'Calculating the Adjustment amount');
4198 fnd_file.put_line(fnd_file.log,'Header ID : ' || to_char(i_header));
4199
4200 End If;
4201
4202 l_stmt_num := 45;
4203
4204 /* bug fix for bug 3439082.Added joins on the cost group and cost type */
4205
4206
4207 BEGIN /* bail out exception that occurs coz there is no row in crac */
4208
4209 select NVL((crac.net_quantity_received * crac.acquisition_cost),0),
4210 crac.rcv_transaction_id, nvl(crac.acquisition_cost,0),
4211 nvl(crac.net_quantity_received,0)
4212 INTO l_ori_acq_amount, l_rcv_txn_id, l_original_acq_cost,
4213 l_original_qty_received
4214 FROM cst_rcv_acq_costs crac, cst_rcv_acq_costs_adj craca
4215 WHERE craca.header_id = i_header
4216 AND crac.rcv_transaction_id = craca.rcv_transaction_id
4217 AND crac.cost_type_id = i_cost_type_id
4218 AND crac.cost_group_id = i_cost_group_id;
4219
4220 EXCEPTION
4221 WHEN NO_DATA_FOUND then
4222 l_ori_acq_amount := 0;
4223 l_rcv_txn_id := -99;
4224
4225 END;
4226
4227 If G_DEBUG = 'Y' then
4228
4229 fnd_file.put_line(fnd_file.log,'Original Acq cost:'|| l_ori_acq_amount);
4230 End If;
4231
4232 /* now get the SUM of all the incremental amounts posted to MMT so far in the previous periods */
4233
4234 l_stmt_num := 50;
4235
4236 /* bug fix for bug 3439082.Added joins on the cost group and cost type */
4237
4238 select NVL(SUM(NVL(value_change,0)),0) into l_old_increments
4239 from mtl_material_transactions mmt, cst_rcv_acq_costs_adj craca
4240 where mmt.transaction_id = craca.mmt_transaction_id
4241 and craca.mmt_transaction_id is NOT NULL
4242 and craca.cost_group_id = i_cost_group_id
4243 and craca.cost_type_id = i_cost_type_id
4244 and craca.rcv_transaction_id = (select rcv_transaction_id
4245 from cst_rcv_acq_costs_adj craca2
4246 where craca2.header_id = i_header);
4247
4248
4249 fnd_file.put_line(fnd_file.log,'old increment :' || to_char(l_old_increments));
4250
4251 select nvl(net_quantity_received,0)
4252 into l_net_qty_received
4253 from cst_rcv_acq_costs_adj
4254 where header_id = i_header;
4255
4256 /* Bug 2741945 */
4257
4258 /* Get the RTV/Correction qty and amount to be adjusted */
4259
4260 l_rtv_qty := nvl(abs(l_net_qty_received - l_original_qty_received),0);
4261
4262 l_rtv_adj_amount := l_rtv_qty * l_original_acq_cost;
4263
4264 fnd_file.put_line(fnd_file.log,'RTV Adjustment amount : ' || to_char(l_rtv_adj_amount));
4265
4266 fnd_file.put_line(fnd_file.log,'Current acq cost : ' || to_char(l_acq_cost));
4267
4268
4269
4270 l_acq_adjustment_amount := (NVL(l_acq_cost,0) * l_net_qty_received)
4271 - l_ori_acq_amount - l_old_increments + l_rtv_adj_amount;
4272
4273 fnd_file.put_line(fnd_file.log,'Adjustment amount : ' || to_char(l_acq_adjustment_amount));
4274
4275 /* Now check if the amount to be posted is greater than 0.If it is then post an entry into MMT.Otherwise dont */
4276
4277 IF l_acq_adjustment_amount <> 0 then
4278
4279 /* now start geting the details that are required to insert into MMT */
4280
4281 /* first get the legal entity for the cost group */
4282
4283 l_stmt_num := 55;
4284
4285 select legal_entity into l_legal_entity
4286 from cst_cost_groups
4287 where cost_group_id = i_cost_group_id ;
4288
4289 /* Now get the prior period quantity */
4290
4291 l_stmt_num := 60;
4292
4293 select NVL(MAX(pac_period_id), -1) into l_prev_period_id
4294 from cst_pac_periods
4295 where legal_entity = l_legal_entity
4296 and open_flag = 'N'
4297 and cost_type_id = i_cost_type_id;
4298
4299 /* bug 5044215/5264793.Check if the deliveries against this receipt is to shopfloor.If it is then the
4300 adjustment amount needs to be posted against the assembly item on the job */
4301
4302 select wip_entity_id
4303 into l_wip_entity_id
4304 from
4305 (
4306 Select distinct wip_entity_id
4307 from rcv_transactions rt2
4308 where rt2.transaction_type in ('DELIVER')
4309 START WITH
4310 rt2.transaction_id = (select rcv_transaction_id
4311 from cst_rcv_acq_costs_adj craca2
4312 where craca2.header_id = i_header)
4313 CONNECT BY
4314 prior rt2.transaction_id = rt2.parent_transaction_id
4315 )
4316 where rownum = 1;
4317
4318 If l_wip_entity_id is not NULL then
4319 Select primary_item_id
4320 into l_item
4321 from wip_entities
4322 where wip_entity_id = l_wip_entity_id ;
4323 else
4324 l_item := i_item ;
4325 end if;
4326
4327 If l_prev_period_id = -1 then
4328 l_prior_period_quantity := 0;
4329 l_prior_period_cost := 0;
4330 else
4331
4332 l_stmt_num := 65;
4333
4334 BEGIN
4335 select NVL(total_layer_quantity,0),NVL(item_cost,0)
4336 into l_prior_period_quantity,l_prior_period_cost
4337 from cst_pac_item_costs
4338 where pac_period_id = l_prev_period_id
4339 and cost_group_id = i_cost_group_id
4340 and inventory_item_id = l_item ;
4341
4342 EXCEPTION
4343 WHEN OTHERS THEN
4344 l_prior_period_quantity := 0;
4345 l_prior_period_cost := 0;
4346 END;
4347
4348 end if;
4349
4350 /* Now get the accounts for all the cost elements */
4351
4352 l_stmt_num := 70;
4353
4354 fnd_file.put_line(fnd_file.log,'item id : ' || to_char(l_item));
4355 fnd_file.put_line(fnd_file.log,'org_id : ' || to_char(i_org_id));
4356
4357
4358 select MTL_MATERIAL_TRANSACTIONS_S.NEXTVAL into l_transaction_id
4359 from dual;
4360
4361 /* get the period close date for the current open period */
4362
4363 l_stmt_num := 80;
4364
4365 select NVL(period_end_date,sysdate) into l_period_close_date
4366 from CST_PAC_PERIODS
4367 where pac_period_id = i_pac_period_id
4368 and legal_entity = l_legal_entity
4369 and cost_type_id = i_cost_type_id;
4370
4371 /* Now insert stuff into MMT */
4372
4373 select LEAST(l_period_close_date,sysdate) into l_least_date
4374 from dual;
4375
4376 l_stmt_num := 90;
4377
4378 /* bug 4322574. CHanged the Costed_flag to NULL from 'N' so that the perpetual cost worker does not try and
4379 pick it up for costing */
4380
4381
4382 INSERT INTO MTL_MATERIAL_TRANSACTIONS
4383 (transaction_id,
4384 last_update_date,
4385 last_updated_by,
4386 creation_date,
4387 created_by,
4388 inventory_item_id,
4389 organization_id,
4390 transaction_type_id,
4391 transaction_action_id,
4392 transaction_source_type_id,
4393 transaction_quantity,
4394 transaction_uom,
4395 primary_quantity,
4396 transaction_date,
4397 value_change,
4398 material_account,
4399 material_overhead_account,
4400 resource_account,
4401 outside_processing_account,
4402 overhead_account,
4403 costed_flag,
4404 org_cost_group_id,
4405 cost_type_id,
4406 source_code,
4407 source_line_id)
4408 VALUES (
4409 l_transaction_id,
4410 sysdate,
4411 i_user_id,
4412 sysdate,
4413 i_user_id,
4414 l_item,
4415 i_org_id,
4416 26,
4417 24,
4418 14,
4419 --l_prior_period_quantity,
4420 0,
4421 i_primary_uom,
4422 --l_prior_period_quantity,
4423 0,
4424 l_least_date, --- transaction_date is sysdate
4425 l_acq_adjustment_amount,
4426 i_adj_account,
4427 i_adj_account,
4428 i_adj_account,
4429 i_adj_account,
4430 i_adj_account,
4431 NULL,
4432 i_cost_group_id,
4433 i_cost_type_id,
4434 'ACQADJ',
4435 l_rcv_txn_id
4436 );
4437
4438 /* insert into MPTCD */
4439
4440 Insert into mtl_pac_txn_cost_details
4441 (cost_group_id,
4442 transaction_id,
4443 pac_period_id,
4444 cost_type_id,
4445 cost_element_id,
4446 level_type,
4447 inventory_item_id,
4448 value_change,
4449 transaction_cost,
4450 last_update_date,
4451 last_updated_by,
4452 creation_date,
4453 created_by )
4454 Values (i_cost_group_id,
4455 l_transaction_id,
4456 i_pac_period_id,
4457 i_cost_type_id,
4458 1, -- cost element ID
4459 1, -- THis level
4460 l_item,
4461 l_acq_adjustment_amount,
4462 l_prior_period_cost,
4463 sysdate,
4464 i_user_id,
4465 sysdate,
4466 i_user_id);
4467
4468
4469
4470 /* Now update the entry in CRACA with the new transaction ID of MMT */
4471
4472 l_stmt_num := 100;
4473
4474 Update cst_rcv_acq_costs_adj set
4475 mmt_transaction_id = l_transaction_id
4476 where header_id = i_header;
4477
4478
4479 END IF; -- there is something to post into MMT
4480
4481
4482
4483 EXCEPTION
4484 WHEN CST_NULL_ACQ_COST THEN
4485 o_err_num := 30014;
4486 o_err_code := SQLCODE;
4487 FND_MESSAGE.set_name('BOM', 'CST_NULL_ACQ_COST');
4488 o_err_msg := FND_MESSAGE.Get;
4489 WHEN OTHERS THEN
4490 o_err_num := 30002;
4491 o_err_code := SQLCODE;
4492 o_err_msg := SUBSTR('CSTPPACQ.compute_acq_cost_adj('
4493 ||to_char(l_stmt_num)
4494 ||'):'
4495 ||SQLERRM,1,240);
4496
4497 END compute_acq_cost_acqadj;
4498
4499
4500
4501 PROCEDURE get_acq_cost (
4502 i_cost_group_id IN NUMBER,
4503 i_txn_id IN NUMBER,
4504 i_cost_type_id IN NUMBER,
4505 i_wip_inv_flag IN VARCHAR2,
4506 o_acq_cost OUT NOCOPY NUMBER,
4507 o_err_num OUT NOCOPY NUMBER,
4508 o_err_code OUT NOCOPY VARCHAR2,
4509 o_err_msg OUT NOCOPY VARCHAR2)
4510 IS
4511 l_rcv_txn NUMBER;
4512 l_rec_cost NUMBER;
4513 l_par_txn NUMBER;
4514 l_stmt_num NUMBER := 0;
4515 l_err_msg VARCHAR2(240);
4516 l_lcm_adj_period NUMBER;
4517 l_lcm_flag VARCHAR2(1);
4518
4519 CST_FAIL_PAR_ERROR EXCEPTION;
4520 CST_NO_ACQ_COST EXCEPTION;
4521 CST_NULL_ACQ_COST EXCEPTION;
4522 CST_FAIL_MMT_TXN EXCEPTION;
4523 CST_FAIL_WIP_TXN EXCEPTION;
4524 BEGIN
4525
4526 ------------------------------------------------------------
4527 -- Initialize variables
4528 ------------------------------------------------------------
4529
4530 o_err_num := 0;
4531 o_err_code := '';
4532 o_err_msg := '';
4533
4534 l_err_msg := NULL;
4535 ---------------------------------------------------------------
4536 -- If the function is called from Inventory part of PAC processor,
4537 -- the flag will be 'I' and it is a MMT transaction
4538 -- If the function is called from WIP part of PAC processor,
4539 -- the flag will be 'W' and it is a WT transaction
4540 ---------------------------------------------------------------
4541
4542 IF i_wip_inv_flag = 'I' THEN
4543
4544 l_stmt_num := 10;
4545
4546 ---------------------------------------------------------------
4547 -- Get correspoding rcv_txn from MMT
4548 ---------------------------------------------------------------
4549
4550 SELECT
4551 rcv_transaction_id
4552 INTO
4553 l_rcv_txn
4554 FROM
4555 mtl_material_transactions mmt
4556 WHERE
4557 mmt.transaction_id = i_txn_id AND
4558 mmt.organization_id in (
4559 SELECT
4560 ccga2.organization_id
4561 FROM
4562 cst_cost_group_assignments ccga2
4563 WHERE
4564 ccga2.cost_group_id = i_cost_group_id);
4565
4566 ELSIF i_wip_inv_flag = 'W' THEN
4567
4568 l_stmt_num := 20;
4569
4570 -----------------------------------------------------------------
4571 -- Get correspoding rcv_txn from WT
4572 -----------------------------------------------------------------
4573
4574 -----------------------------------------------------------------
4575 -- Fix for bug 1758901
4576 -- source_line_id of wip_transactions corresponds to
4577 -- transaction_id of rcv_transactions;
4578 -- rcv_transaction_id of wip_transactions corresponds to
4579 -- interface_transaction_id of rcv_transactions
4580 -----------------------------------------------------------------
4581 /* Reversed above fix
4582 Source_line_id in WT corresponds to interface_transaction_id
4583 in rcv_transactions and rcv_transaction_id corresponds to the
4584 transaction_id in rcv_transactions.
4585 Bugfix 2541821
4586 */
4587
4588 SELECT
4589 rcv_transaction_id
4590 INTO
4591 l_rcv_txn
4592 FROM
4593 wip_transactions wt
4594 WHERE
4595 wt.transaction_id = i_txn_id AND
4596 wt.organization_id in (
4597 SELECT
4598 ccga3.organization_id
4599 FROM
4600 cst_cost_group_assignments ccga3
4601 WHERE
4602 ccga3.cost_group_id = i_cost_group_id);
4603 ELSE
4604 RAISE CST_FAIL_PAR_ERROR;
4605 END IF;
4606
4607
4608 -- if no data found, then either the rcv txn is incorrect or the org is incorrect
4609
4610 l_stmt_num := 30;
4611
4612 ------------------------------------------------------------
4613 -- Get the parent receive or match txn from RCV_TRANSACTIONS
4614 ------------------------------------------------------------
4615
4616 SELECT
4617 rt6.transaction_id,
4618 nvl(poll.lcm_flag,'N')
4619 INTO
4620 l_par_txn,
4621 l_lcm_flag
4622 FROM
4623 rcv_transactions rt6,
4624 po_line_locations_all poll
4625 WHERE
4626 rt6.transaction_type in ('RECEIVE','MATCH')
4627 AND poll.line_location_id = rt6.po_line_location_id
4628 START WITH
4629 rt6.transaction_id = l_rcv_txn
4630 CONNECT BY
4631 rt6.transaction_id = prior rt6.parent_transaction_id;
4632
4633 l_stmt_num := 40;
4634
4635 Declare
4636 l_hook_cost number;
4637 l_hook_err number;
4638 Begin
4639 l_hook_cost :=0;
4640 l_hook_err :=0;
4641
4642 l_stmt_num := 45;
4643 IF (l_lcm_flag = 'N') THEN
4644 SELECT
4645 nvl(crac3.acquisition_cost,-1)
4646 INTO
4647 l_rec_cost
4648 FROM
4649 cst_rcv_acq_costs crac3
4650 WHERE
4651 crac3.cost_type_id = i_cost_type_id AND
4652 crac3.cost_group_id = i_cost_group_id AND
4653 crac3.rcv_transaction_id = l_par_txn;
4654
4655 IF SQL%ROWCOUNT = 0 THEN
4656 RAISE CST_NO_ACQ_COST;
4657 END IF;
4658
4659 IF (l_rec_cost = -1) then
4660 RAISE CST_NULL_ACQ_COST;
4661 END IF;
4662 ELSE /*LCM enabled*/
4663 l_stmt_num := 50;
4664 SELECT
4665 nvl(max(crac3.acquisition_cost),-1)
4666 INTO
4667 l_rec_cost
4668 FROM
4669 cst_rcv_acq_costs crac3
4670 WHERE
4671 crac3.cost_type_id = i_cost_type_id AND
4672 crac3.cost_group_id = i_cost_group_id AND
4673 crac3.rcv_transaction_id = l_par_txn;
4674 l_stmt_num := 60;
4675 SELECT nvl(max(craca.period_id),-1)
4676 INTO l_lcm_adj_period
4677 FROM cst_rcv_acq_costs_adj craca
4678 WHERE craca.rcv_transaction_id = l_par_txn
4679 AND craca.cost_type_id = i_cost_type_id
4680 AND craca.cost_group_id = i_cost_group_id;
4681
4682 IF (l_lcm_adj_period <> -1) THEN
4683 l_stmt_num := 70;
4684 SELECT craca.acquisition_cost
4685 INTO l_rec_cost
4686 FROM cst_rcv_acq_costs_adj craca
4687 WHERE craca.rcv_transaction_id = l_par_txn
4688 AND craca.cost_type_id = i_cost_type_id
4689 AND craca.cost_group_id = i_cost_group_id
4690 AND craca.period_id = l_lcm_adj_period;
4691 ELSIF ( l_rec_cost = -1 AND l_lcm_adj_period = -1) THEN
4692 RAISE no_data_found;
4693 END IF;
4694 END IF;
4695 Exception
4696 WHEN no_data_found then
4697 l_err_msg := '';
4698 CSTPPAHK.acq_receipt_cost_hook(
4699 i_cost_type_id,
4700 i_cost_group_id,
4701 l_par_txn,
4702 l_hook_cost,
4703 l_hook_err,
4704 l_err_msg);
4705 if l_hook_err < 0 then
4706 raise;
4707 else
4708 l_rec_cost := l_hook_cost;
4709 end if;
4710
4711 WHEN others then
4712 Raise;
4713 End;
4714
4715 -------------------------------------------------------------
4716 -- set output parameter to acq cost
4717 -------------------------------------------------------------
4718
4719 o_acq_cost := l_rec_cost;
4720 --until AP objects are built, whole code needs to be commented out.
4721 --return a dummy cost of 1 for now.
4722
4723 --o_acq_cost := 1;
4724
4725 -- if no data, then acq cost does not exist for the CT, period
4726 EXCEPTION
4727 WHEN CST_FAIL_PAR_ERROR THEN
4728 o_err_num := 30010;
4729 o_err_code := SQLCODE;
4730 o_err_msg := 'CSTPPACQ.get_acq_cost : Wrong Parameter Value';
4731
4732 WHEN CST_FAIL_MMT_TXN THEN
4733 o_err_num := 30011;
4734 o_err_code := SQLCODE;
4735 FND_MESSAGE.set_name('BOM', 'CST_FAIL_MMT_TXN');
4736 o_err_msg := FND_MESSAGE.Get;
4737
4738 WHEN CST_FAIL_WIP_TXN THEN
4739 o_err_num := 30012;
4740 o_err_code := SQLCODE;
4741 FND_MESSAGE.set_name('BOM', 'CST_FAIL_WIP_TXN');
4742 o_err_msg := FND_MESSAGE.Get;
4743
4744 WHEN CST_NO_ACQ_COST THEN
4745 o_err_num := 30013;
4746 o_err_code := SQLCODE;
4747 FND_MESSAGE.set_name('BOM', 'CST_NO_ACQ_COST');
4748 o_err_msg := FND_MESSAGE.Get;
4749
4750 WHEN CST_NULL_ACQ_COST THEN
4751 o_err_num := 30014;
4752 o_err_code := SQLCODE;
4753 FND_MESSAGE.set_name('BOM', 'CST_NULL_ACQ_COST');
4754 o_err_msg := FND_MESSAGE.Get;
4755
4756 WHEN OTHERS THEN
4757 o_err_num := 30003;
4758 o_err_code := SQLCODE;
4759 o_err_msg := SUBSTR(l_err_msg||'CSTPPACQ.get_acq_cost('
4760 ||to_char(l_stmt_num)
4761 ||'):'
4762 ||SQLERRM,1,240);
4763 END get_acq_cost;
4764
4765 FUNCTION get_rcv_tax (
4766 i_rcv_txn_id IN NUMBER)
4767 RETURN NUMBER
4768 IS
4769 l_tot_tax NUMBER;
4770 l_stmt_num NUMBER;
4771 BEGIN
4772
4773 /* This function is also called from the CSTRAIVR.rdf main query. */
4774
4775 l_stmt_num := 10;
4776
4777 SELECT
4778 nvl((SUM(NVL(nonrecoverable_tax,0))
4779 /SUM(DECODE(PLL.MATCHING_BASIS,
4780 'AMOUNT', POD.AMOUNT_ORDERED,
4781 'QUANTITY', POD.quantity_ordered ) ) ), 0 )
4782 INTO
4783 l_tot_tax
4784 FROM
4785 po_distributions_all pod,
4786 rcv_transactions rcv,
4787 po_line_locations_all pll
4788 WHERE RCV.TRANSACTION_ID = i_rcv_txn_id
4789 AND POD.LINE_LOCATION_ID = RCV.PO_LINE_LOCATION_ID
4790 AND PLL.LINE_LOCATION_ID = RCV.PO_LINE_LOCATION_ID
4791 AND (
4792 ( RCV.PO_DISTRIBUTION_ID IS NOT NULL
4793 AND RCV.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
4794 )
4795 OR
4796 ( RCV.PO_DISTRIBUTION_ID IS NULL
4797 AND RCV.PO_LINE_LOCATION_ID = POD.LINE_LOCATION_ID
4798 )
4799 );
4800
4801 return l_tot_tax;
4802
4803
4804 EXCEPTION
4805 WHEN OTHERS THEN
4806 return -1;
4807
4808 END get_rcv_tax;
4809
4810 FUNCTION get_po_rate (
4811 i_rcv_txn_id IN NUMBER)
4812 RETURN NUMBER
4813 IS
4814 l_po_rate NUMBER;
4815 l_stmt_num NUMBER;
4816 l_rsl_exists NUMBER;
4817 l_trx_flow NUMBER := 1;
4818 l_org_id NUMBER;
4819 BEGIN
4820
4821 /* This function is also called from the CSTRAIVR.rdf main query. */
4822
4823 l_stmt_num := 10;
4824
4825 SELECT count(rcv_transaction_id)
4826 INTO l_rsl_exists
4827 FROM rcv_receiving_sub_ledger rsl
4828 WHERE rsl.rcv_transaction_id = i_rcv_txn_id
4829 AND rsl.accounted_cr IS NOT NULL
4830 AND rsl.accounted_cr <> 0
4831 AND ROWNUM < 2;
4832
4833 l_stmt_num := 15;
4834 IF (l_rsl_exists <> 0) THEN
4835
4836 /* Bug 3427884: eliminate any adjust event accounting if this is a
4837 Global Procurement scenario using transfer pricing option */
4838
4839 Begin
4840 Select
4841 TRX_FLOW_HEADER_ID,
4842 organization_id
4843 Into
4844 l_trx_flow,
4845 l_org_id
4846 From rcv_accounting_events rae,
4847 po_lines_all POL,
4848 po_distributions_all POD
4849 Where rae.rcv_transaction_id = i_rcv_txn_id
4850 And rae.event_type_id = 1 -- RECEIVE
4851 And rae.trx_flow_header_id is not null
4852 AND RAE.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
4853 AND POD.PO_LINE_ID = POL.PO_LINE_ID;
4854 Exception
4855 When others then
4856 l_trx_flow := -1;
4857 l_org_id := -1;
4858 End;
4859
4860 if (l_trx_flow > 0) then
4861 /* bug 3421589 - If this is a global procurement with a transaction flow
4862 * then it's possible that there are rsl entries for each organization
4863 * in the flow for the same rcv_txn_id. In that case, we need to join on
4864 * RAE to pick up the proper org.
4865 */
4866
4867 SELECT
4868 SUM(rsl.accounted_cr) / SUM(rsl.entered_cr)
4869 INTO
4870 l_po_rate
4871 FROM
4872 rcv_receiving_sub_ledger rsl,
4873 rcv_accounting_events rae
4874 WHERE
4875 rsl.rcv_transaction_id = i_rcv_txn_id AND
4876 rsl.accounted_cr is not null AND
4877 rsl.accounted_cr <> 0 and
4878 rsl.accounting_event_id = rae.accounting_event_id and
4879 rae.event_type_id <> 7
4880 and rae.organization_id = l_org_id;
4881
4882 else
4883
4884 SELECT
4885 SUM(rsl.accounted_cr) / SUM(rsl.entered_cr)
4886 INTO
4887 l_po_rate
4888 FROM
4889 rcv_receiving_sub_ledger rsl
4890 WHERE
4891 rsl.rcv_transaction_id = i_rcv_txn_id AND
4892 rsl.accounted_cr is not null AND
4893 rsl.accounted_cr <> 0;
4894 end if;
4895
4896 ELSE
4897
4898 l_stmt_num := 17;
4899
4900 SELECT
4901 SUM(DECODE(POLL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_ORDERED, POD.QUANTITY_ORDERED)*nvl(pod.rate,1))
4902 /SUM(DECODE(POLL.MATCHING_BASIS, 'AMOUNT', POD.AMOUNT_ORDERED, POD.QUANTITY_ORDERED))
4903 INTO
4904 l_po_rate
4905 FROM
4906 PO_DISTRIBUTIONS_ALL POD,
4907 RCV_TRANSACTIONS RT,
4908 PO_LINE_LOCATIONS_ALL POLL
4909 WHERE
4910 RT.TRANSACTION_ID = i_rcv_txn_id
4911 AND (
4912 ( RT.PO_DISTRIBUTION_ID IS NOT NULL
4913 AND RT.PO_DISTRIBUTION_ID = POD.PO_DISTRIBUTION_ID
4914 )
4915 OR
4916 ( RT.PO_DISTRIBUTION_ID IS NULL
4917 AND RT.PO_LINE_LOCATION_ID = POD.LINE_LOCATION_ID
4918 )
4919 )
4920 AND POLL.LINE_LOCATION_ID = POD.LINE_LOCATION_ID;
4921
4922 END IF;
4923
4924
4925 return l_po_rate;
4926
4927 EXCEPTION
4928 WHEN OTHERS THEN
4929 return -1;
4930
4931 END get_po_rate;
4932
4933 FUNCTION get_net_undel_qty(
4934 i_transaction_id IN NUMBER,
4935 i_end_date IN DATE)
4936 RETURN NUMBER
4937 IS
4938 ----------------------------------------------------
4939 -- Get all child transactions level by level
4940 -----------------------------------------------------
4941 CURSOR c_nqud is
4942 SELECT
4943 rt4.transaction_id,
4944 rt4.transaction_type,
4945 rt4.primary_quantity,
4946 rt4.parent_transaction_id
4947 FROM
4948 rcv_transactions rt4
4949 WHERE
4950 rt4.transaction_date < i_end_date
4951 START WITH
4952 rt4.transaction_id = i_transaction_id
4953 CONNECT BY
4954 prior rt4.transaction_id = rt4.parent_transaction_id;
4955 l_nqud NUMBER := 0;
4956 l_parent_type rcv_transactions.transaction_type%TYPE;
4957 l_stmt_num NUMBER := 0;
4958 BEGIN
4959 ---------------------------------------------------------
4960 -- Initialize error variable
4961 ---------------------------------------------------------
4962 ---------------------------------------------------------
4963 -- For each child transaction loop
4964 --------------------------------------------------------
4965 FOR c_nqud_rec in c_nqud loop
4966 --------------------------------------------------------
4967 -- If it is not the parent (that was passed in) transaction itself
4968 --------------------------------------------------------
4969 IF c_nqud_rec.transaction_id <> i_transaction_id THEN
4970 l_stmt_num := 10;
4971 ----------------------------------------------------------
4972 -- Get the parent transaction type
4973 ----------------------------------------------------------
4974 SELECT
4975 rt5.transaction_type
4976 INTO
4977 l_parent_type
4978 FROM
4979 rcv_transactions rt5
4980 WHERE
4981 rt5.transaction_id = c_nqud_rec.parent_transaction_id;
4982 END IF;
4983 ------------------------------------------------------------
4984 -- If it is the parent receive or match transaction
4985 -- then add the quantity to l_nqud
4986 ------------------------------------------------------------
4987 IF c_nqud_rec.transaction_id = i_transaction_id THEN
4988 l_nqud := l_nqud + c_nqud_rec.primary_quantity;
4989 -----------------------------------------------------------
4990 -- If the transaction is CORRECT,
4991 -- If parent is receive or match txn, then add the corrected qty
4992 -- If parent is return, then subtract the corrected qty
4993 -----------------------------------------------------------
4994 ELSIF c_nqud_rec.transaction_type = 'CORRECT' then
4995 IF l_parent_type = 'RECEIVE' OR
4996 l_parent_type = 'MATCH' THEN
4997 l_nqud := l_nqud + c_nqud_rec.primary_quantity;
4998 ELSIF l_parent_type = 'RETURN TO VENDOR' then
4999 l_nqud := l_nqud - c_nqud_rec.primary_quantity;
5000 ELSIF l_parent_type = 'DELIVER' then
5001 l_nqud := l_nqud - c_nqud_rec.primary_quantity;
5002 ELSIF l_parent_type = 'RETURN TO RECEIVING' then
5003 l_nqud := l_nqud + c_nqud_rec.primary_quantity;
5004 END IF;
5005 ----------------------------------------------------------
5006 -- If transaction is return transaction, then subtract returned qty
5007 ----------------------------------------------------------
5008 ELSIF c_nqud_rec.transaction_type = 'RETURN TO VENDOR' then
5009 l_nqud := l_nqud - c_nqud_rec.primary_quantity;
5010 ELSIF c_nqud_rec.transaction_type = 'DELIVER' then
5011 l_nqud := l_nqud - c_nqud_rec.primary_quantity;
5012 ELSIF c_nqud_rec.transaction_type = 'RETURN TO RECEIVING' then
5013 l_nqud := l_nqud + c_nqud_rec.primary_quantity;
5014 END IF;
5015 END LOOP; -- child txns loop
5016 --------------------------------------------------------
5017 -- Return the net quantity received as calculated
5018 --------------------------------------------------------
5019 RETURN (l_nqud);
5020 EXCEPTION
5021 WHEN OTHERS THEN
5022 RETURN(NULL);
5023 END get_net_undel_qty;
5024
5025
5026 Procedure Insert_into_acqhdr_tables(
5027 i_header_id IN NUMBER,
5028 i_cost_group_id IN NUMBER,
5029 i_cost_type_id IN NUMBER,
5030 i_period_id IN NUMBER,
5031 i_rcv_transaction_id IN NUMBER,
5032 i_net_quantity_received IN NUMBER,
5033 i_total_quantity_invoiced IN NUMBER,
5034 i_quantity_at_po_price IN NUMBER,
5035 i_total_invoice_amount IN NUMBER,
5036 i_amount_at_po_price IN NUMBER,
5037 i_total_amount IN NUMBER,
5038 i_costed_quantity IN NUMBER,
5039 i_acquisition_cost IN NUMBER,
5040 i_po_line_location_id IN NUMBER,
5041 i_po_unit_price IN NUMBER,
5042 i_primary_uom IN VARCHAR2,
5043 i_rec_exchg_rate IN NUMBER,
5044 i_last_update_date IN DATE,
5045 i_last_updated_by IN NUMBER,
5046 i_creation_date IN DATE,
5047 i_created_by IN NUMBER,
5048 i_request_id IN NUMBER,
5049 i_program_application_id IN NUMBER,
5050 i_program_id IN NUMBER,
5051 i_program_update_date IN DATE,
5052 i_last_update_login IN NUMBER,
5053 i_source_flag IN NUMBER,
5054 o_err_num OUT NOCOPY NUMBER,
5055 o_err_msg OUT NOCOPY VARCHAR2 ) IS
5056
5057 l_stmt_no NUMBER := 10;
5058
5059 BEGIN
5060
5061 If i_source_flag = 1 then
5062
5063 INSERT INTO cst_rcv_acq_costs (
5064 HEADER_ID,
5065 COST_GROUP_ID,
5066 COST_TYPE_ID,
5067 PERIOD_ID,
5068 RCV_TRANSACTION_ID,
5069 NET_QUANTITY_RECEIVED,
5070 TOTAL_QUANTITY_INVOICED,
5071 QUANTITY_AT_PO_PRICE,
5072 TOTAL_INVOICE_AMOUNT,
5073 AMOUNT_AT_PO_PRICE,
5074 TOTAL_AMOUNT,
5075 COSTED_QUANTITY,
5076 ACQUISITION_COST,
5077 PO_LINE_LOCATION_ID,
5078 PO_UNIT_PRICE,
5079 PRIMARY_UOM,
5080 REC_EXCHG_RATE,
5081 LAST_UPDATE_DATE,
5082 LAST_UPDATED_BY,
5083 CREATION_DATE,
5084 CREATED_BY,
5085 REQUEST_ID,
5086 PROGRAM_APPLICATION_ID,
5087 PROGRAM_ID,
5088 PROGRAM_UPDATE_DATE,
5089 LAST_UPDATE_LOGIN
5090 )
5091 values(i_header_id,
5092 i_cost_group_id,
5093 i_cost_type_id,
5094 i_period_id,
5095 i_rcv_transaction_id,
5096 i_net_quantity_received,
5097 i_total_quantity_invoiced,
5098 i_quantity_at_po_price,
5099 i_total_invoice_amount,
5100 i_amount_at_po_price,
5101 i_total_amount,
5102 i_costed_quantity,
5103 i_acquisition_cost,
5104 i_po_line_location_id,
5105 i_po_unit_price,
5106 i_primary_uom,
5107 i_rec_exchg_rate,
5108 i_last_update_date,
5109 i_last_updated_by,
5110 i_creation_date,
5111 i_created_by,
5112 i_request_id,
5113 i_program_application_id,
5114 i_program_id,
5115 i_program_update_date,
5116 i_last_update_login );
5117
5118 elsif i_source_flag = 2 then
5119
5120 l_stmt_no := 20;
5121
5122 INSERT INTO cst_rcv_acq_costs_adj (
5123 HEADER_ID,
5124 COST_GROUP_ID,
5125 COST_TYPE_ID,
5126 PERIOD_ID,
5127 RCV_TRANSACTION_ID,
5128 NET_QUANTITY_RECEIVED,
5129 TOTAL_QUANTITY_INVOICED,
5130 QUANTITY_AT_PO_PRICE,
5131 TOTAL_INVOICE_AMOUNT,
5132 AMOUNT_AT_PO_PRICE,
5133 TOTAL_AMOUNT,
5134 COSTED_QUANTITY,
5135 ACQUISITION_COST,
5136 PO_LINE_LOCATION_ID,
5137 PO_UNIT_PRICE,
5138 PRIMARY_UOM,
5139 REC_EXCHG_RATE,
5140 LAST_UPDATE_DATE,
5141 LAST_UPDATED_BY,
5142 CREATION_DATE,
5143 CREATED_BY,
5144 REQUEST_ID,
5145 PROGRAM_APPLICATION_ID,
5146 PROGRAM_ID,
5147 PROGRAM_UPDATE_DATE,
5148 LAST_UPDATE_LOGIN
5149 )
5150 values(i_header_id,
5151 i_cost_group_id,
5152 i_cost_type_id,
5153 i_period_id,
5154 i_rcv_transaction_id,
5155 i_net_quantity_received,
5156 i_total_quantity_invoiced,
5157 i_quantity_at_po_price,
5158 i_total_invoice_amount,
5159 i_amount_at_po_price,
5160 i_total_amount,
5161 i_costed_quantity,
5162 i_acquisition_cost,
5163 i_po_line_location_id,
5164 i_po_unit_price,
5165 i_primary_uom,
5166 i_rec_exchg_rate,
5167 i_last_update_date,
5168 i_last_updated_by,
5169 i_creation_date,
5170 i_created_by,
5171 i_request_id,
5172 i_program_application_id,
5173 i_program_id,
5174 i_program_update_date,
5175 i_last_update_login );
5176 END IF;
5177
5178 EXCEPTION
5179 when others then
5180 o_err_msg := SUBSTR('CSTPPACQ.Insert_into_acqhdr_tables('
5181 ||to_char(l_stmt_no)
5182 ||'):'
5183 ||SQLERRM,1,240);
5184 o_err_num := -1;
5185
5186 end Insert_into_acqhdr_tables;
5187
5188
5189 Procedure Insert_into_acqdtls_tables (
5190 i_header_id IN NUMBER,
5191 i_detail_id IN NUMBER,
5192 i_source_type IN VARCHAR2,
5193 i_po_line_location_id IN NUMBER,
5194 i_parent_distribution_id IN NUMBER,
5195 i_distribution_num IN NUMBER,
5196 i_level_num IN NUMBER,
5197 i_invoice_distribution_id IN NUMBER,
5198 i_parent_inv_distribution_id IN NUMBER,
5199 i_allocated_amount IN NUMBER,
5200 i_parent_amount IN NUMBER,
5201 i_amount IN NUMBER,
5202 i_quantity IN NUMBER,
5203 i_price IN NUMBER,
5204 i_line_type IN VARCHAR2,
5205 i_last_update_date IN DATE,
5206 i_last_updated_by IN NUMBER,
5207 i_creation_date IN DATE,
5208 i_created_by IN NUMBER,
5209 i_request_id IN NUMBER,
5210 i_program_application_id IN NUMBER,
5211 i_program_id IN NUMBER,
5212 i_program_update_date IN DATE,
5213 i_last_update_login IN NUMBER,
5214 i_source_flag IN NUMBER,
5215 o_err_num OUT NOCOPY NUMBER,
5216 o_err_msg OUT NOCOPY VARCHAR2) IS
5217
5218
5219 l_stmt_no NUMBER := 10;
5220
5221 BEGIN
5222
5223 IF i_source_flag = 1 then
5224
5225 Insert into cst_rcv_acq_cost_details(
5226 HEADER_ID,
5227 DETAIL_ID,
5228 SOURCE_TYPE,
5229 PO_LINE_LOCATION_ID,
5230 PARENT_DISTRIBUTION_ID,
5231 DISTRIBUTION_NUM,
5232 LEVEL_NUM,
5233 INVOICE_DISTRIBUTION_ID,
5234 PARENT_INVOICE_DIST_ID,
5235 ALLOCATED_AMOUNT,
5236 PARENT_AMOUNT,
5237 AMOUNT,
5238 QUANTITY,
5239 PRICE,
5240 LINE_TYPE,
5241 LAST_UPDATE_DATE,
5242 LAST_UPDATED_BY,
5243 CREATION_DATE,
5244 CREATED_BY,
5245 REQUEST_ID,
5246 PROGRAM_APPLICATION_ID,
5247 PROGRAM_ID,
5248 PROGRAM_UPDATE_DATE,
5249 LAST_UPDATE_LOGIN
5250 )
5251 values(
5252 i_header_id,
5253 i_detail_id,
5254 i_source_type,
5255 i_po_line_location_id,
5256 i_parent_distribution_id,
5257 i_distribution_num,
5258 i_level_num,
5259 i_invoice_distribution_id,
5260 i_parent_inv_distribution_id,
5261 i_allocated_amount,
5262 i_parent_amount,
5263 i_amount,
5264 i_quantity,
5265 i_price,
5266 i_line_type,
5267 i_last_update_date,
5268 i_last_updated_by,
5269 i_creation_date,
5270 i_created_by,
5271 i_request_id,
5272 i_program_application_id,
5273 i_program_id,
5274 i_program_update_date,
5275 i_last_update_login
5276 );
5277
5278 ELSIF i_source_flag = 2 then
5279
5280 l_stmt_no := 20;
5281
5282 Insert into cst_rcv_acq_cost_details_adj(
5283 HEADER_ID,
5284 DETAIL_ID,
5285 SOURCE_TYPE,
5286 PO_LINE_LOCATION_ID,
5287 PARENT_DISTRIBUTION_ID,
5288 DISTRIBUTION_NUM,
5289 LEVEL_NUM,
5290 INVOICE_DISTRIBUTION_ID,
5291 PARENT_INVOICE_DIST_ID,
5292 ALLOCATED_AMOUNT,
5293 PARENT_AMOUNT,
5294 AMOUNT,
5295 QUANTITY,
5296 PRICE,
5297 LINE_TYPE,
5298 LAST_UPDATE_DATE,
5299 LAST_UPDATED_BY,
5300 CREATION_DATE,
5301 CREATED_BY,
5302 REQUEST_ID,
5303 PROGRAM_APPLICATION_ID,
5304 PROGRAM_ID,
5305 PROGRAM_UPDATE_DATE,
5306 LAST_UPDATE_LOGIN
5307 )
5308 values(
5309 i_header_id,
5310 i_detail_id,
5311 i_source_type,
5312 i_po_line_location_id,
5313 i_parent_distribution_id,
5314 i_distribution_num,
5315 i_level_num,
5316 i_invoice_distribution_id,
5317 i_parent_inv_distribution_id,
5318 i_allocated_amount,
5319 i_parent_amount,
5320 i_amount,
5321 i_quantity,
5322 i_price,
5323 i_line_type,
5324 i_last_update_date,
5325 i_last_updated_by,
5326 i_creation_date,
5327 i_created_by,
5328 i_request_id,
5329 i_program_application_id,
5330 i_program_id,
5331 i_program_update_date,
5332 i_last_update_login
5333 );
5334
5335 END IF;
5336
5337 EXCEPTION
5338 when others then
5339 o_err_msg := SUBSTR('CSTPPACQ.Insert_into_acqdtls_tables('
5340 ||to_char(l_stmt_no)
5341 ||'):'
5342 ||SQLERRM,1,240);
5343 o_err_num := -1;
5344
5345 END Insert_into_acqdtls_tables;
5346
5347 Procedure Acquisition_cost_adj_processor(
5348 ERRBUF OUT NOCOPY VARCHAR2,
5349 RETCODE OUT NOCOPY NUMBER,
5350 i_legal_entity IN NUMBER,
5351 i_cost_type_id IN NUMBER,
5352 i_period IN NUMBER,
5353 i_end_date IN VARCHAR2,
5354 i_cost_group_id IN NUMBER,
5355 i_source_flag IN NUMBER,
5356 i_run_option IN NUMBER,
5357 i_receipt_dummy IN VARCHAR2,
5358 i_receipt_no IN NUMBER,
5359 i_invoice_dummy IN VARCHAR2,
5360 i_invoice_no IN NUMBER,
5361 i_chart_of_ac_id IN NUMBER,
5362 i_adj_account_dummy IN NUMBER,
5363 i_adj_account IN NUMBER
5364 ) IS
5365
5366 CST_INVALID_EXCEPTION EXCEPTION;
5367 CONC_STATUS BOOLEAN;
5368 l_err_num NUMBER;
5369 l_err_code VARCHAR2(2000);
5370 l_err_msg VARCHAR2(2000);
5371 l_receipt_no NUMBER;
5372 l_invoice_no NUMBER;
5373 l_end_date DATE;
5374 l_stmt_num NUMBER;
5375
5376
5377 BEGIN
5378
5379 l_err_code := '';
5380 l_err_msg := '';
5381
5382 If i_run_option = 1 then
5383 l_receipt_no := NULL;
5384 l_invoice_no := NULL;
5385 elsif i_run_option = 3 then
5386 l_receipt_no := i_receipt_no;
5387 l_invoice_no := NULL;
5388 elsif i_run_option = 2 then
5389 l_receipt_no := NULL;
5390 l_invoice_no := i_invoice_no;
5391 end if;
5392
5393 l_end_date := to_date(i_end_date,'RR/MM/DD HH24:MI:SS');
5394
5395 If G_DEBUG = 'Y' then
5396
5397 fnd_file.put_line(fnd_file.log,'date is : '|| to_char(l_end_date,'DD-MON-RR'));
5398
5399 End If;
5400
5401 l_stmt_num := 10;
5402
5403
5404 /* start printing out the Parameters */
5405
5406 fnd_file.put_line(fnd_file.log,'Legal Entity : '|| to_char(i_legal_entity));
5407 fnd_file.put_line(fnd_file.log,'Cost Type : '|| to_char(i_cost_type_id));
5408 fnd_file.put_line(fnd_file.log,'Period : '|| to_char(i_period));
5409 fnd_file.put_line(fnd_file.log,'Process Upto date : '|| to_char(to_date(i_end_date,'RR/MM/DD HH24:MI:SS'),'DD-MON-RR'));
5410 fnd_file.put_line(fnd_file.log,'Cost Group : '|| to_char(i_cost_group_id));
5411 fnd_file.put_line(fnd_file.log,'Source : '|| to_char(i_source_flag));
5412 fnd_file.put_line(fnd_file.log,'Run Option : '|| to_char(i_run_option));
5413 fnd_file.put_line(fnd_file.log,'Receipt No : '|| to_char(i_receipt_no));
5414 fnd_file.put_line(fnd_file.log,'Invoice No : '|| to_char(i_invoice_no));
5415
5416 /* call the same Acquisition Cost Processor code with the new modified parameters */
5417
5418 l_stmt_num := 20;
5419
5420 Acq_cost_processor(
5421 i_period,
5422 NULL, --i_start_date will be computed as the period start date
5423 l_end_date,
5424 i_cost_type_id,
5425 i_cost_group_id,
5426 FND_GLOBAL.USER_ID,
5427 FND_GLOBAL.LOGIN_ID,
5428 FND_GLOBAL.CONC_REQUEST_ID,
5429 FND_GLOBAL.CONC_PROGRAM_ID,
5430 FND_GLOBAL.PROG_APPL_ID,
5431 l_err_num,
5432 l_err_code,
5433 l_err_msg,
5434 2, -- source_flag
5435 l_receipt_no,
5436 l_invoice_no,
5437 i_adj_account);
5438
5439 IF l_err_code is NOT NULL then
5440 RAISE CST_INVALID_EXCEPTION;
5441
5442 END IF;
5443
5444 COMMIT;
5445
5446 EXCEPTION
5447
5448 WHEN others then
5449
5450 fnd_file.put_line(fnd_file.log,'Exception occured in Acquisition_cost_adj_processor : ' || l_err_code || ' ' || l_err_msg);
5451
5452 ROLLBACK;
5453
5454 CONC_STATUS := fnd_concurrent.set_completion_status ('ERROR','CST_INVALID_EXCEPTION');
5455 return;
5456
5457 END Acquisition_cost_adj_processor;
5458
5459 END CSTPPACQ;