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