[Home] [Help]
PACKAGE BODY: APPS.CSTPMECS
Source
1 PACKAGE BODY CSTPMECS AS
2 /* $Header: CSTMECSB.pls 120.11.12020000.3 2013/02/19 15:27:01 yunfchen ship $ */
3
4 PROCEDURE CSTPALAR (
5
6 I_LIST_ID IN NUMBER,
7 I_ORG_ID IN NUMBER,
8 I_ACTIVITY_ID IN NUMBER,
9 I_FROM_DATE IN DATE,
10 I_TO_DATE IN DATE,
11 I_ACCOUNT_ID IN NUMBER,
12 I_BASIS_TYPE IN NUMBER,
13 I_FIXED_RATE IN NUMBER,
14 I_PER_INC IN NUMBER,
15 I_AMT_INC IN NUMBER,
16 I_COST_TYPE_ID IN NUMBER,
17 I_COPY_COST_TYPE IN NUMBER,
18 I_RESOURCE_ID IN NUMBER,
19
20 I_USER_ID IN NUMBER,
21 I_REQUEST_ID IN NUMBER,
22 I_PROGRAM_ID IN NUMBER,
23 I_PROGRAM_APPL_ID IN NUMBER,
24 O_RETURN_CODE OUT NOCOPY NUMBER) IS
25
26 ROUND_UNIT NUMBER;
27 PRECISION NUMBER;
28 EXT_PREC NUMBER;
29
30 BEGIN
31
32 O_RETURN_CODE := 9999;
33
34 CSTPUTIL.CSTPUGCI(I_ORG_ID, ROUND_UNIT, PRECISION, EXT_PREC);
35
36 UPDATE cst_item_cost_details CICD
37 SET (
38 usage_rate_or_amount,
39 item_cost,
40 last_update_date,
41 last_updated_by,
42 request_id,
43 program_application_id,
44 program_id,
45 program_update_date
46 ) = (
47 SELECT unit_cost,
48 ROUND((unit_cost * CICD.basis_factor *
49 CICD.net_yield_or_shrinkage_factor), EXT_PREC),
50 SYSDATE,
51 I_USER_ID,
52 I_REQUEST_ID,
53 I_PROGRAM_ID,
54 I_PROGRAM_APPL_ID,
55 SYSDATE
56 FROM cst_activity_costs
57 WHERE activity_id = CICD.activity_id
58 AND organization_id = CICD.organization_id
59 AND cost_type_id = CICD.cost_type_id
60 )
61 WHERE organization_id = I_ORG_ID
62 AND cost_type_id = I_COST_TYPE_ID
63 AND activity_id = DECODE(I_ACTIVITY_ID, 0, activity_id, I_ACTIVITY_ID)
64 AND cost_element_id = 2 /* Material Overhead */
65 AND basis_type = 6 /* Activity Units */
66 AND level_type = 1 /* This Level */
67 AND rollup_source_type = 1 /* User Defined */
68 AND EXISTS
69 (
70 SELECT 'x'
71 FROM cst_lists CL,
72 cst_activity_costs CAC
73 WHERE CL.list_id = I_LIST_ID
74 AND CL.entity_id = CICD.inventory_item_id
75 AND CAC.organization_id = CICD.organization_id
76 AND CAC.cost_type_id = CICD.cost_type_id
77 AND CAC.activity_id = CICD.activity_id
78 AND CAC.unit_cost IS NOT NULL
79 )
80 ;
81
82 O_RETURN_CODE := 0;
83
84 EXCEPTION
85 WHEN NO_DATA_FOUND THEN
86 O_RETURN_CODE := SQLCODE;
87 WHEN OTHERS THEN
88 O_RETURN_CODE := SQLCODE;
89 raise_application_error(-20001, SQLERRM);
90
91 END CSTPALAR;
92
93
94 PROCEDURE CSTPAPOR (
95
96 I_LIST_ID IN NUMBER,
97 I_ORG_ID IN NUMBER,
98 I_ACTIVITY_ID IN NUMBER,
99 I_FROM_DATE IN DATE,
100 I_TO_DATE IN DATE,
101 I_ACCOUNT_ID IN NUMBER,
102 I_BASIS_TYPE IN NUMBER,
103 I_FIXED_RATE IN NUMBER,
104 I_PER_INC IN NUMBER,
105 I_AMT_INC IN NUMBER,
106 I_COST_TYPE_ID IN NUMBER,
107 I_COPY_COST_TYPE IN NUMBER,
108 I_RESOURCE_ID IN NUMBER,
109
110 I_USER_ID IN NUMBER,
111 I_REQUEST_ID IN NUMBER,
112 I_PROGRAM_ID IN NUMBER,
113 I_PROGRAM_APPL_ID IN NUMBER,
114 O_RETURN_CODE IN OUT NOCOPY NUMBER) IS
115
116 ROUND_UNIT NUMBER;
117 PRECISION NUMBER;
118 EXT_PREC NUMBER;
119
120 BEGIN
121
122 O_RETURN_CODE := 9999;
123
124 CSTPUTIL.CSTPUGCI(I_ORG_ID, ROUND_UNIT, PRECISION, EXT_PREC);
125
126 /*===============================================================*/
127 /* Removed any item from the list where there is no PO receipt */
128 /* Bug 13497932 - use exists, count, and sum to exclude zero quantity items */
129 /*===============================================================*/
130 DELETE FROM cst_lists L
131 WHERE L.list_id = I_LIST_ID
132 AND EXISTS (
133 SELECT /*+ NO_UNNEST */ 'X'
134 FROM mtl_material_transactions M
135 , mtl_parameters MP
136 WHERE MP.cost_organization_id = I_ORG_ID
137 AND M.organization_id = MP.organization_id
138 AND M.inventory_item_id = L.ENTITY_ID
139 AND (
140 ( M.transaction_source_type_id = 1
141 AND M.transaction_action_id = 27
142 AND M.transaction_type_id = 18
143 )
144 /* Bug 10116576: need to include PO adjustment and RTV */
145 OR
146 ( M.transaction_source_type_id = 1
147 AND M.transaction_action_id = 29
148 AND M.transaction_type_id = 71
149 )
150 OR
151 ( M.transaction_source_type_id = 1
152 AND M.transaction_action_id = 1
153 AND M.transaction_type_id = 36
154 )
155 OR
156 ( M.transaction_source_type_id = 1
157 AND M.transaction_action_id = 6
158 AND M.transaction_type_id = 74
159 )
160 OR
161 ( M.transaction_source_type_id = 13
162 AND M.transaction_action_id = 6
163 AND M.transaction_type_id = 75
164 )
165 )
166 AND M.owning_tp_type = 2
167 AND M.transaction_date BETWEEN NVL(I_FROM_DATE, M.transaction_date-1) AND NVL(I_TO_DATE, M.transaction_date+1)
168 AND M.costed_flag IS NULL
169 HAVING (count(*)=0 or sum(M.primary_quantity)=0)
170 );
171
172 /*==================================================================*/
173 /* insert item cost where there is PO receipt but no pending cost */
174 /* information */
175 /*==================================================================*/
176 CSTPUMEC.CSTPEIIC(I_ORG_ID,
177 I_COST_TYPE_ID,
178 I_LIST_ID,
179 I_RESOURCE_ID,
180 I_USER_ID,
181 I_REQUEST_ID,
182 I_PROGRAM_ID,
183 I_PROGRAM_APPL_ID,
184 O_RETURN_CODE);
185
186 IF O_RETURN_CODE <> 0 THEN
187 raise_application_error(-20001, 'CSTPAPOR->CSTPEIIC: '||SQLERRM);
188 END IF;
189
190 UPDATE cst_item_cost_details CICD
191 SET (
192 usage_rate_or_amount
193 , item_cost
194 , last_update_date
195 , last_updated_by
196 , request_id
197 , program_application_id
198 , program_id
199 , program_update_date
200 ) = (
201 SELECT /*+ NO_UNNEST */
202 ((SUM(MMT.primary_quantity * NVL(MMT.transaction_cost,0) ) *
203 (1 + I_PER_INC/100)) /
204 SUM(MMT.primary_quantity)) + I_AMT_INC
205 , ROUND(((((SUM(MMT.primary_quantity * nvl(MMT.transaction_cost,0) ) *
206 (1 + I_PER_INC/100)) /
207 SUM(MMT.primary_quantity)) + I_AMT_INC) *
208 NVL(CICD.basis_factor,0) *
209 NVL(CICD.net_yield_or_shrinkage_factor,0) *
210 NVL(CICD.resource_rate,1)
211 ), EXT_PREC)
212 , SYSDATE
213 , I_USER_ID
214 , I_REQUEST_ID
215 , I_PROGRAM_APPL_ID
216 , I_PROGRAM_ID
217 , SYSDATE
218 FROM mtl_material_transactions MMT
219 , mtl_parameters MP
220 WHERE MP.cost_organization_id = I_ORG_ID
221 AND MMT.organization_id = MP.organization_id
222 AND MMT.OWNING_TP_TYPE = 2
223 AND MMT.inventory_item_id = CICD.inventory_item_id
224 AND (
225 ( MMT.transaction_source_type_id = 1
226 AND MMT.transaction_action_id = 27
227 AND MMT.transaction_type_id = 18
228 )
229 /* Bug 10116576: need to include PO adjustment and RTV */
230 OR
231 ( MMT.transaction_source_type_id = 1
232 AND MMT.transaction_action_id = 29
233 AND MMT.transaction_type_id = 71
234 )
235 OR
236 ( MMT.transaction_source_type_id = 1
237 AND MMT.transaction_action_id = 1
238 AND MMT.transaction_type_id = 36
239 )
240 OR
241 ( MMT.transaction_source_type_id = 1
242 AND MMT.transaction_action_id = 6
243 AND MMT.transaction_type_id = 74
244 )
245 OR
246 ( MMT.transaction_source_type_id = 13
247 AND MMT.transaction_action_id = 6
248 AND MMT.transaction_type_id = 75
249 )
250 )
251 AND MMT.transaction_date BETWEEN NVL(I_FROM_DATE, MMT.transaction_date-1) AND NVL(I_TO_DATE, MMT.transaction_date+1)
252 AND MMT.costed_flag IS NULL
253 )
254 WHERE organization_id = I_ORG_ID
255 AND cost_type_id = I_COST_TYPE_ID
256 AND level_type = 1
257 AND cost_element_id = 1
258 AND resource_id = I_RESOURCE_ID
259 AND NVL(activity_id,-1) = DECODE(I_ACTIVITY_ID,
260 0, NVL(activity_id,-1),
261 I_ACTIVITY_ID)
262 AND NVL(basis_type,-1) = DECODE(I_BASIS_TYPE,
263 0, NVL(basis_type,-1),
264 I_BASIS_TYPE)
265 AND rollup_source_type = 1 /* User Defined */
266 AND EXISTS
267 (
268 SELECT 'X'
269 FROM cst_lists L
270 WHERE L.list_id = I_LIST_ID
271 AND L.entity_id = CICD.inventory_item_id
272 )
273 ;
274 O_RETURN_CODE := 0;
275
276 EXCEPTION
277 WHEN NO_DATA_FOUND THEN
278 O_RETURN_CODE := SQLCODE;
279 WHEN OTHERS THEN
280 O_RETURN_CODE := SQLCODE;
281 raise_application_error(-20001, 'CSTPAPOR: '||SQLERRM);
282
283 END CSTPAPOR;
284
285 PROCEDURE CSTPAWAC (
286
287 I_LIST_ID IN NUMBER,
288 I_ORG_ID IN NUMBER,
289 I_ACTIVITY_ID IN NUMBER,
290 I_FROM_DATE IN DATE,
291 I_TO_DATE IN DATE,
292 I_ACCOUNT_ID IN NUMBER,
293 I_BASIS_TYPE IN NUMBER,
294 I_FIXED_RATE IN NUMBER,
295 I_PER_INC IN NUMBER,
296 I_AMT_INC IN NUMBER,
297 I_COST_TYPE_ID IN NUMBER,
298 I_COPY_COST_TYPE IN NUMBER,
299 I_RESOURCE_ID IN NUMBER,
300
301 I_USER_ID IN NUMBER,
302 I_REQUEST_ID IN NUMBER,
303 I_PROGRAM_ID IN NUMBER,
304 I_PROGRAM_APPL_ID IN NUMBER,
305 O_RETURN_CODE IN OUT NOCOPY NUMBER) IS
306
307 ROUND_UNIT NUMBER;
308 PRECISION NUMBER;
309 EXT_PREC NUMBER;
310
311 BEGIN
312
313 O_RETURN_CODE := 9999;
314
315 CSTPUTIL.CSTPUGCI(I_ORG_ID, ROUND_UNIT, PRECISION, EXT_PREC);
316
317 /*===============================================================*/
318 /* Removed any item from the list where there is no PO receipt */
319 /*===============================================================*/
320 DELETE FROM cst_lists L
321 WHERE L.list_id = I_LIST_ID
322 AND NOT EXISTS (
323 SELECT 'X'
324 FROM mtl_material_transactions M
325 , mtl_parameters MP
326 WHERE MP.cost_organization_id = I_ORG_ID
327 AND M.organization_id = MP.organization_id
328 AND M.inventory_item_id = L.ENTITY_ID
329 AND M.transaction_source_type_id = 1
330 AND M.transaction_action_id = 27
331 AND M.transaction_type_id = 18
332 AND (i_from_date IS NULL OR
333 (i_from_date IS NOT NULL AND m.transaction_date > i_from_date ))
334 AND (i_to_date IS NULL OR
335 (i_to_date IS NOT NULL AND m.transaction_date < i_to_date ))
336 AND M.costed_flag = 'N'
337 )
338 ;
339
340 /*==================================================================*/
341 /* insert item cost where there is PO receipt but no pending cost */
342 /* information */
343 /*==================================================================*/
344 CSTPUMEC.CSTPEIIC(I_ORG_ID,
345 I_COST_TYPE_ID,
346 I_LIST_ID,
347 I_RESOURCE_ID,
348 I_USER_ID,
349 I_REQUEST_ID,
350 I_PROGRAM_ID,
351 I_PROGRAM_APPL_ID,
352 O_RETURN_CODE);
353
354 IF O_RETURN_CODE <> 0 THEN
355 raise_application_error(-20001, 'CSTPAWAC->CSTPEIIC: '||SQLERRM);
356 END IF;
357
358
359
360
361
362
363
364
365
366 /*==================================================================*/
367 /* update item cost details */
368 /*==================================================================*/
369 UPDATE cst_item_cost_details CICD
370 SET
371 (
372 item_cost
373 , usage_rate_or_amount
374 , last_update_date
375 , last_updated_by
376 , request_id
377 , program_application_id
378 , program_id
379 , program_update_date
380 )
381 =
382 ( /* item_cost calculation */
383 SELECT /* applied daily received value */
384 (((
385 SUM (
386 DECODE (MMT.costed_flag,NULL,0,MMT.primary_quantity)
387 * MMT.actual_cost
388 )
389 * (1 + I_PER_INC/100)
390 )
391 + /* previous day on-hand value */
392 ((
393 SUM (MOH.transaction_quantity)
394 - SUM (
395 DECODE (MMT.costed_flag,NULL,0,MMT.primary_quantity)
396 )
397 )
398 * CICD.item_cost
399 )
400 )
401 / (SUM (MOH.transaction_quantity) + I_AMT_INC)
402 )
403 , ROUND /* usage_rate_or_amount calcul. */
404 (((((
405 SUM (
406 DECODE (MMT.costed_flag,NULL,0,MMT.primary_quantity)
407 * MMT.actual_cost
408 )
409 * (1 + I_PER_INC/100)
410 )
411 +
412 ((
413 SUM (MOH.transaction_quantity)
414 - SUM (
415 DECODE (MMT.costed_flag,NULL,0,MMT.primary_quantity)
416 )
417 )
418 * CICD.item_cost
419 )
420 )
421 / (SUM (MOH.transaction_quantity)
422 + I_AMT_INC
423 )
424 )
425 / (NVL (CICD.basis_factor,0)
426 * NVL (CICD.net_yield_or_shrinkage_factor,0)
427 * NVL (CICD.resource_rate,1)
428 )
429 )
430 , EXT_PREC
431 )
432 , SYSDATE
433 , I_USER_ID
434 , I_REQUEST_ID
435 , I_PROGRAM_APPL_ID
436 , I_PROGRAM_ID
437 , SYSDATE
438
439 FROM /* Select and Round FROM clause */
440 mtl_parameters MP
441 , mtl_secondary_inventories MSI
442 , mtl_onhand_quantities MOH
443 , mtl_material_transactions MMT
444
445 WHERE /* Select and Round WHERE clause */
446 MP.cost_organization_id = I_ORG_ID
447
448 AND MSI.organization_id = MP.organization_id
449 AND MSI.asset_inventory = 1
450
451 AND MOH.organization_id = MSI.organization_id
452 AND MOH.subinventory_code = MSI.secondary_inventory_name
453 AND MOH.inventory_item_id = CICD.inventory_item_id
454
455 AND MMT.transaction_id = MOH.create_transaction_id
456 AND MMT.transaction_type_id = 18
457 AND MMT.transaction_source_type_id = 1
458 AND MMT.transaction_date BETWEEN
459 NVL(I_FROM_DATE, MMT.transaction_date-1)
460 AND
461 NVL(I_TO_DATE, MMT.transaction_date+1)
462
463 ) /* End of SET */
464
465 WHERE /* Update WHERE clause */
466 organization_id = I_ORG_ID
467 AND cost_type_id = I_COST_TYPE_ID
468 AND level_type = 1
469 AND cost_element_id = 1
470 AND resource_id = I_RESOURCE_ID
471 AND NVL(activity_id,-1) = DECODE(I_ACTIVITY_ID,
472 0, NVL(activity_id,-1),
473 I_ACTIVITY_ID)
474 AND NVL(basis_type,-1) = DECODE(I_BASIS_TYPE,
475 0, NVL(basis_type,-1),
476 I_BASIS_TYPE)
477 AND rollup_source_type = 1 /* User Defined */
478
479 AND EXISTS
480 (
481 SELECT 'X'
482 FROM cst_lists L
483 WHERE L.list_id = I_LIST_ID
484 AND L.entity_id = CICD.inventory_item_id
485 )
486 ;
487
488 O_RETURN_CODE := 0;
489
490 EXCEPTION
491 WHEN NO_DATA_FOUND THEN
492 O_RETURN_CODE := SQLCODE;
493 WHEN OTHERS THEN
494 O_RETURN_CODE := SQLCODE;
495 raise_application_error(-20001, 'CSTPAWAC: '||SQLERRM);
496
497 END CSTPAWAC;
498
499 PROCEDURE CSTPOPOA (
500
501 I_LIST_ID IN NUMBER,
502 I_ORG_ID IN NUMBER,
503 I_ACTIVITY_ID IN NUMBER,
504 I_FROM_DATE IN DATE,
505 I_TO_DATE IN DATE,
506 I_ACCOUNT_ID IN NUMBER,
507 I_BASIS_TYPE IN NUMBER,
508 I_FIXED_RATE IN NUMBER,
509 I_PER_INC IN NUMBER,
510 I_AMT_INC IN NUMBER,
511 I_COST_TYPE_ID IN NUMBER,
512 I_COPY_COST_TYPE IN NUMBER,
513 I_RESOURCE_ID IN NUMBER,
514
515 I_USER_ID IN NUMBER,
516 I_REQUEST_ID IN NUMBER,
517 I_PROGRAM_ID IN NUMBER,
518 I_PROGRAM_APPL_ID IN NUMBER,
519 O_RETURN_CODE IN OUT NOCOPY NUMBER) IS
520
521 ROUND_UNIT NUMBER;
522 PRECISION NUMBER;
523 EXT_PREC NUMBER;
524
525 BEGIN
526
527 O_RETURN_CODE := 9999;
528
529 CSTPUTIL.CSTPUGCI(I_ORG_ID, ROUND_UNIT, PRECISION, EXT_PREC);
530
531 /*=============================================================================*/
532 /* Populate the global temporary table cst_open_pos_temp with all of the items */
533 /* in cst_lists that have open POs associated with them. This data will be */
534 /* used below to update the item cost and usage rate in CICD. */
535 /*=============================================================================*/
536
537 /* Bug 3589032 - Instead of inserting the open POs directly into the temp
538 * table, I split it into an insert, update, and delete. The reason is that
539 * the explain plan for the single insert has a cost in the 10,000 order of
540 * magnitude, vs. a 10 order of magnitude cost for the update statement.
541 */
542
543 INSERT INTO cst_open_pos_temp (
544 usage_rate_or_amount,
545 inventory_item_id)
546 SELECT
547 NULL
548 , entity_id
549 FROM cst_lists
550 WHERE list_id = I_LIST_ID;
551
552
553 UPDATE cst_open_pos_temp COPT
554 set usage_rate_or_amount =
555 (SELECT (SUM(
556 (POD.quantity_ordered - NVL(POD.quantity_delivered,0)) *
557 (
558 (PLL.price_override + nvl(po_tax_sv.get_tax('PO',pod.po_distribution_id),0)/pod.quantity_ordered) *
559 --modified for GP currency conversion
560 DECODE(po_cur.po_currency,rcvcur.currency_code, NVL(POD.rate,1),
561 NVL(gl_currency_api.get_rate(po_cur.po_currency, rcvcur.currency_code, NVL(pod.rate_Date,TRUNC(SYSDATE)), psp.default_rate_type),1))
562 )
563 ) /
564 decode(SUM((POD.quantity_ordered - NVL(POD.quantity_delivered,0)) *
565 UOM.conversion_rate), 0, 1,
566 SUM((POD.quantity_ordered - NVL(POD.quantity_delivered,0)) *
567 UOM.conversion_rate))) *
568 (1 + I_PER_INC/100) + I_AMT_INC
569 FROM mtl_parameters MP
570 , po_headers_all POH
571 , po_lines_all POL
572 , po_line_locations_all PLL
573 , po_distributions_all POD
574 , mtl_uom_conversions_view UOM
575 , cst_organization_definitions rcvcur
576 , po_system_parameters_all psp
577 , (SELECT DISTINCT operating_unit ou_id, currency_code po_currency
578 FROM cst_organization_definitions ) po_cur
579 WHERE MP.cost_organization_id = I_ORG_ID
580 AND POD.destination_organization_id = MP.organization_id
581 AND POD.destination_type_code = 'INVENTORY'
582 AND NVL(PLL.closed_code, 'OPEN') not in ('FINALLY CLOSED', 'CLOSED',
583 'CLOSED FOR RECEIVING' )
584 AND POD.quantity_ordered <> NVL(POD.quantity_delivered,0)
585 AND NVL(PLL.cancel_flag, 'N') = 'N'
586 AND POL.item_id = COPT.inventory_item_id
587 AND POH.po_header_id = POL.po_header_id
588 --added for bug 13581867
589 AND PLL.po_header_id = POH.po_header_id
590 AND POL.po_line_id = PLL.po_line_id
591 AND POD.po_line_id = POL.po_line_id
592 AND PLL.line_location_id = POD.line_location_id
593 AND PLL.approved_flag = 'Y'
594 AND NVL(PLL.promised_date,NVL(PLL.need_by_date,POH.approved_date))
595 BETWEEN
596 NVL(I_FROM_DATE, NVL(PLL.promised_date,NVL(PLL.need_by_date,POH.approved_date))-1)
597 AND
598 NVL(I_TO_DATE, NVL(PLL.promised_date,NVL(PLL.need_by_date,POH.approved_date))+1)
599 AND UOM.organization_id = I_ORG_ID
600 AND UOM.inventory_item_id = POL.item_id
601 AND UOM.unit_of_measure = POL.unit_meas_lookup_code
602 --added for GP currency conversion
603 AND psp.org_id = poh.org_id
604 AND po_cur.ou_id = poh.org_id
605 AND pod.destination_organization_id = rcvcur.organization_id
606 GROUP BY POL.item_id);
607
608
609 fnd_file.put_line(fnd_file.log,'Inserted '||to_char(SQL%ROWCOUNT)||' rows into temp table.');
610
611 /* Now delete all items from the temp tables where there were no OPEN POs found */
612 DELETE FROM cst_open_pos_temp
613 WHERE usage_rate_or_amount IS NULL;
614
615 DELETE FROM cst_lists
616 WHERE list_id = I_LIST_ID
617 AND entity_id NOT IN (SELECT inventory_item_id FROM cst_open_pos_temp);
618
619 /*===============================================================*/
620 /* insert item cost where there is open PO but no pending cost */
621 /* information */
622 /*===============================================================*/
623 CSTPUMEC.CSTPEIIC(I_ORG_ID,
624 I_COST_TYPE_ID,
625 I_LIST_ID,
626 I_RESOURCE_ID,
627 I_USER_ID,
628 I_REQUEST_ID,
629 I_PROGRAM_ID,
630 I_PROGRAM_APPL_ID,
631 O_RETURN_CODE);
632
633 IF O_RETURN_CODE <> 0 THEN
634 raise_application_error(-20001, 'CSTPOPOA: '||SQLERRM);
635 END IF;
636
637 /*=============================================================================*/
638 /* Update CICD using the data in the global temporary table cst_open_pos_temp. */
639 /*=============================================================================*/
640 UPDATE cst_item_cost_details A
641 SET (
642 usage_rate_or_amount
643 , item_cost
644 , last_update_date
645 , last_updated_by
646 , request_id
647 , program_application_id
648 , program_id
649 , program_update_date
650 ) = (
651 SELECT COPT.usage_rate_or_amount
652 , ROUND((COPT.usage_rate_or_amount) *
653 NVL((A.basis_factor * A.net_yield_or_shrinkage_factor *
654 A.resource_rate),1), EXT_PREC)
655 , SYSDATE
656 , I_USER_ID
657 , I_REQUEST_ID
658 , I_PROGRAM_APPL_ID
659 , I_PROGRAM_ID
660 , SYSDATE
661 FROM cst_open_pos_temp COPT
662 WHERE COPT.inventory_item_id = A.inventory_item_id
663 )
664 WHERE organization_id = I_ORG_ID
665 AND cost_type_id = I_COST_TYPE_ID
666 AND level_type = 1
667 AND cost_element_id = 1
668 AND resource_id = I_RESOURCE_ID
669 AND NVL(activity_id,-1) = DECODE(I_ACTIVITY_ID,
670 0, NVL(activity_id,-1),
671 I_ACTIVITY_ID)
672 AND NVL(basis_type,-1) = DECODE(I_BASIS_TYPE,
673 0, NVL(basis_type,-1),
674 I_BASIS_TYPE)
675 AND rollup_source_type = 1 /* User Defined */
676 AND A.inventory_item_id in
677 (
678 SELECT inventory_item_id
679 FROM cst_open_pos_temp
680 )
681 ;
682
683 fnd_file.put_line(fnd_file.log,'Updated '||to_char(SQL%ROWCOUNT)||' rows in cst_item_cost_details.');
684
685 O_RETURN_CODE := 0;
686
687 EXCEPTION
688 WHEN NO_DATA_FOUND THEN
689 O_RETURN_CODE := SQLCODE;
690 WHEN OTHERS THEN
691 O_RETURN_CODE := SQLCODE;
692 raise_application_error(-20001, SQLERRM);
693
694 END CSTPOPOA;
695
696 PROCEDURE CSTPSHRK (
697
698 I_LIST_ID IN NUMBER,
699 I_ORG_ID IN NUMBER,
700 I_ACTIVITY_ID IN NUMBER,
701 I_FROM_DATE IN DATE,
702 I_TO_DATE IN DATE,
703 I_ACCOUNT_ID IN NUMBER,
704 I_BASIS_TYPE IN NUMBER,
705 I_FIXED_RATE IN NUMBER,
706 I_PER_INC IN NUMBER,
707 I_AMT_INC IN NUMBER,
708 I_COST_TYPE_ID IN NUMBER,
709 I_COPY_COST_TYPE IN NUMBER,
710 I_RESOURCE_ID IN NUMBER,
711
712 I_LAST_UPDATED_BY IN NUMBER,
713 I_REQUEST_ID IN NUMBER,
714 I_PROGRAM_ID IN NUMBER,
715 I_PROGRAM_APPL_ID IN NUMBER,
716 O_RETURN_CODE OUT NOCOPY NUMBER) IS
717
718 ROUND_UNIT NUMBER;
719 PRECISION NUMBER;
720 EXT_PREC NUMBER;
721 L_LOCATION NUMBER;
722
723 BEGIN
724
725 O_RETURN_CODE := 0;
726
727 L_LOCATION := 0;
728
729 CSTPUTIL.CSTPUGCI(I_ORG_ID, ROUND_UNIT, PRECISION, EXT_PREC);
730
731 L_LOCATION := 1;
732
733 UPDATE CST_ITEM_COSTS A
734 SET SHRINKAGE_RATE = DECODE(I_FIXED_RATE,-1,0,I_FIXED_RATE)
735 WHERE ORGANIZATION_ID = I_ORG_ID
736 AND COST_TYPE_ID = I_COST_TYPE_ID
737 AND EXISTS
738 (
739 SELECT 'X'
740 FROM CST_LISTS L
741 WHERE L.LIST_ID = I_LIST_ID
742 AND L.ENTITY_ID = A.INVENTORY_ITEM_ID
743 )
744 ;
745
746 L_LOCATION := 2;
747
748 UPDATE CST_ITEM_COST_DETAILS A
749 SET NET_YIELD_OR_SHRINKAGE_FACTOR = 1/(1 - DECODE(I_FIXED_RATE,-1,0,I_FIXED_RATE))
750 , ITEM_COST = ROUND(((USAGE_RATE_OR_AMOUNT * BASIS_FACTOR *
751 NVL(RESOURCE_RATE,1)) / (1 - DECODE(I_FIXED_RATE,-1,0,I_FIXED_RATE))), EXT_PREC)
752 , LAST_UPDATE_DATE = SYSDATE
753 , LAST_UPDATED_BY = I_LAST_UPDATED_BY
754 , REQUEST_ID = I_REQUEST_ID
755 , PROGRAM_APPLICATION_ID = I_PROGRAM_APPL_ID
756 , PROGRAM_ID = I_PROGRAM_ID
757 , PROGRAM_UPDATE_DATE = SYSDATE
758 WHERE ORGANIZATION_ID = I_ORG_ID
759 AND COST_TYPE_ID = I_COST_TYPE_ID
760 AND EXISTS
761 (
762 SELECT 'X'
763 FROM CST_LISTS L
764 WHERE L.LIST_ID = I_LIST_ID
765 AND L.ENTITY_ID = A.INVENTORY_ITEM_ID
766 )
767 ;
768
769 O_RETURN_CODE := 0;
770
771 EXCEPTION
772 WHEN NO_DATA_FOUND THEN
773 O_RETURN_CODE := SQLCODE;
774 WHEN OTHERS THEN
775 O_RETURN_CODE := SQLCODE;
776 raise_application_error(-20001, 'CSTPSHRK(' || L_LOCATION || '): '
777 || SQLERRM);
778
779 END CSTPSHRK;
780
781 PROCEDURE CSTPSMOH (
782
783 I_LIST_ID IN NUMBER,
784 I_ORG_ID IN NUMBER,
785 I_ACTIVITY_ID IN NUMBER,
786 I_FROM_DATE IN DATE,
787 I_TO_DATE IN DATE,
788 I_ACCOUNT_ID IN NUMBER,
789 I_BASIS_TYPE IN NUMBER,
790 I_FIXED_RATE IN NUMBER,
791 I_PER_INC IN NUMBER,
792 I_AMT_INC IN NUMBER,
793 I_COST_TYPE_ID IN NUMBER,
794 I_COPY_COST_TYPE IN NUMBER,
795 I_RESOURCE_ID IN NUMBER,
796
797 I_LAST_UPDATED_BY IN NUMBER,
798 I_REQUEST_ID IN NUMBER,
799 I_PROGRAM_ID IN NUMBER,
800 I_PROGRAM_APPL_ID IN NUMBER,
801 O_RETURN_CODE OUT NOCOPY NUMBER) IS
802
803 ROUND_UNIT NUMBER;
804 PRECISION NUMBER;
805 EXT_PREC NUMBER;
806
807 BEGIN
808
809 CSTPUTIL.CSTPUGCI(I_ORG_ID, ROUND_UNIT, PRECISION, EXT_PREC);
810
811 O_RETURN_CODE := 9999;
812
813 UPDATE CST_ITEM_COST_DETAILS A
814 SET USAGE_RATE_OR_AMOUNT =
815 (DECODE(I_FIXED_RATE,-1,USAGE_RATE_OR_AMOUNT,I_FIXED_RATE) *
816 (1 + I_PER_INC/100) + I_AMT_INC),
817 ITEM_COST =
818 ROUND((DECODE(I_FIXED_RATE,-1,USAGE_RATE_OR_AMOUNT,I_FIXED_RATE) *
819 (1 + I_PER_INC/100) + I_AMT_INC) *
820 NVL(BASIS_FACTOR,1) * NVL(NET_YIELD_OR_SHRINKAGE_FACTOR,1),
821 EXT_PREC),
822 LAST_UPDATE_DATE = SYSDATE,
823 LAST_UPDATED_BY = I_LAST_UPDATED_BY,
824 REQUEST_ID = I_REQUEST_ID,
825 PROGRAM_APPLICATION_ID = I_PROGRAM_APPL_ID,
826 PROGRAM_ID = I_PROGRAM_ID,
827 PROGRAM_UPDATE_DATE = SYSDATE
828 WHERE ORGANIZATION_ID = I_ORG_ID
829 AND COST_TYPE_ID = I_COST_TYPE_ID
830 AND LEVEL_TYPE = 1
831 AND COST_ELEMENT_ID = 2
832 AND NVL(ACTIVITY_ID,-1) = DECODE(I_ACTIVITY_ID,
833 0, NVL(ACTIVITY_ID,-1),
834 I_ACTIVITY_ID)
835 AND NVL(BASIS_TYPE,-1) = DECODE(I_BASIS_TYPE,
836 0, NVL(BASIS_TYPE,-1),
837 I_BASIS_TYPE)
838 AND NVL(RESOURCE_ID,-1) = DECODE(I_RESOURCE_ID,
839 0, NVL(RESOURCE_ID,-1),
840 I_RESOURCE_ID)
841 AND A.INVENTORY_ITEM_ID IN
842 (
843 SELECT ENTITY_ID
844 FROM CST_LISTS L
845 WHERE LIST_ID = I_LIST_ID
846 )
847 ;
848
849 O_RETURN_CODE := 0;
850
851 EXCEPTION
852 WHEN NO_DATA_FOUND THEN
853 O_RETURN_CODE := SQLCODE;
854 WHEN OTHERS THEN
855 O_RETURN_CODE := SQLCODE;
856 raise_application_error(-20001, SQLERRM);
857
858 END CSTPSMOH;
859
860 PROCEDURE CSTPSMTL (
861
862 I_LIST_ID IN NUMBER,
863 I_ORG_ID IN NUMBER,
864 I_ACTIVITY_ID IN NUMBER,
865 I_FROM_DATE IN DATE,
866 I_TO_DATE IN DATE,
867 I_ACCOUNT_ID IN NUMBER,
868 I_BASIS_TYPE IN NUMBER,
869 I_FIXED_RATE IN NUMBER,
870 I_PER_INC IN NUMBER,
871 I_AMT_INC IN NUMBER,
872 I_COST_TYPE_ID IN NUMBER,
873 I_COPY_COST_TYPE IN NUMBER,
874 I_RESOURCE_ID IN NUMBER,
875
876 I_LAST_UPDATED_BY IN NUMBER,
877 I_REQUEST_ID IN NUMBER,
878 I_PROGRAM_ID IN NUMBER,
879 I_PROGRAM_APPL_ID IN NUMBER,
880 O_RETURN_CODE OUT NOCOPY NUMBER) IS
881
882 ROUND_UNIT NUMBER;
883 PRECISION NUMBER;
884 EXT_PREC NUMBER;
885
886 BEGIN
887
888 O_RETURN_CODE := 9999;
889
890 CSTPUTIL.CSTPUGCI(I_ORG_ID, ROUND_UNIT, PRECISION, EXT_PREC);
891
892 UPDATE CST_ITEM_COST_DETAILS A
893 SET USAGE_RATE_OR_AMOUNT =
894 (DECODE(I_FIXED_RATE,-1,USAGE_RATE_OR_AMOUNT,I_FIXED_RATE) *
895 (1 + I_PER_INC/100) + I_AMT_INC),
896 ITEM_COST =
897 ROUND((DECODE(I_FIXED_RATE,-1,USAGE_RATE_OR_AMOUNT,I_FIXED_RATE) *
898 (1 + I_PER_INC/100) + I_AMT_INC) *
899 NVL(BASIS_FACTOR,1) * NVL(NET_YIELD_OR_SHRINKAGE_FACTOR,1),
900 EXT_PREC),
901 LAST_UPDATE_DATE = SYSDATE,
902 LAST_UPDATED_BY = I_LAST_UPDATED_BY,
903 REQUEST_ID = I_REQUEST_ID,
904 PROGRAM_APPLICATION_ID = I_PROGRAM_APPL_ID,
905 PROGRAM_ID = I_PROGRAM_ID,
906 PROGRAM_UPDATE_DATE = SYSDATE
907 WHERE ORGANIZATION_ID = I_ORG_ID
908 AND COST_TYPE_ID = I_COST_TYPE_ID
909 AND LEVEL_TYPE = 1
910 AND COST_ELEMENT_ID = 1
911 AND NVL(ACTIVITY_ID,-1) = DECODE(I_ACTIVITY_ID,
912 0, NVL(ACTIVITY_ID,-1),
913 I_ACTIVITY_ID)
914 AND NVL(BASIS_TYPE,-1) = DECODE(I_BASIS_TYPE,
915 0, NVL(BASIS_TYPE,-1),
916 I_BASIS_TYPE)
917 AND NVL(RESOURCE_ID,-1) = DECODE(I_RESOURCE_ID,
918 0, NVL(RESOURCE_ID,-1),
919 I_RESOURCE_ID)
920 AND EXISTS
921 (
922 SELECT 'X'
923 FROM CST_LISTS L
924 WHERE LIST_ID = I_LIST_ID
925 AND ENTITY_ID = A.INVENTORY_ITEM_ID
926 )
927 ;
928
929 O_RETURN_CODE := 0;
930
931 EXCEPTION
932 WHEN NO_DATA_FOUND THEN
933 O_RETURN_CODE := SQLCODE;
934 WHEN OTHERS THEN
935 O_RETURN_CODE := SQLCODE;
936 raise_application_error(-20001, SQLERRM);
937
938 END CSTPSMTL;
939
940
941 PROCEDURE CSTPSPSR (
942
943 I_LIST_ID IN NUMBER,
944 I_ORG_ID IN NUMBER,
945 I_ACTIVITY_ID IN NUMBER,
946 I_FROM_DATE IN DATE,
947 I_TO_DATE IN DATE,
948 I_ACCOUNT_ID IN NUMBER,
949 I_BASIS_TYPE IN NUMBER,
950 I_FIXED_RATE IN NUMBER,
951 I_PER_INC IN NUMBER,
952 I_AMT_INC IN NUMBER,
953 I_COST_TYPE_ID IN NUMBER,
954 I_COPY_COST_TYPE IN NUMBER,
955 I_RESOURCE_ID IN NUMBER,
956
957 I_LAST_UPDATED_BY IN NUMBER,
958 I_REQUEST_ID IN NUMBER,
959 I_PROGRAM_ID IN NUMBER,
960 I_PROGRAM_APPL_ID IN NUMBER,
961 O_RETURN_CODE OUT NOCOPY NUMBER) IS
962
963 ROUND_UNIT NUMBER;
964 PRECISION NUMBER;
965 EXT_PREC NUMBER;
966
967 BEGIN
968
969 O_RETURN_CODE := 0;
970
971 CSTPUTIL.CSTPUGCI(I_ORG_ID, ROUND_UNIT, PRECISION, EXT_PREC);
972
973 /* Changes for Bug #1768987. Setting shrinkage rate to 0 if shrinkage
974 rate is NULL in MSI. */
975 UPDATE CST_ITEM_COSTS A
976 SET SHRINKAGE_RATE = (
977 SELECT NVL(SHRINKAGE_RATE,0)
978 FROM MTL_SYSTEM_ITEMS
979 WHERE ORGANIZATION_ID = I_ORG_ID
980 AND INVENTORY_ITEM_ID = A.INVENTORY_ITEM_ID
981 )
982 WHERE ORGANIZATION_ID = I_ORG_ID
983 AND COST_TYPE_ID = I_COST_TYPE_ID
984 AND EXISTS
985 (
986 SELECT 'X'
987 FROM CST_LISTS L
988 WHERE L.LIST_ID = I_LIST_ID
989 AND L.ENTITY_ID = A.INVENTORY_ITEM_ID
990 )
991 ;
992
993 UPDATE CST_ITEM_COST_DETAILS A
994 SET (NET_YIELD_OR_SHRINKAGE_FACTOR,
995 ITEM_COST,
996 LAST_UPDATE_DATE,
997 LAST_UPDATED_BY,
998 REQUEST_ID,
999 PROGRAM_APPLICATION_ID,
1000 PROGRAM_ID,
1001 PROGRAM_UPDATE_DATE) = (
1002 SELECT 1 / (1 - NVL(SHRINKAGE_RATE, 0)),
1003 ROUND(((A.USAGE_RATE_OR_AMOUNT * A.BASIS_FACTOR *
1004 NVL(A.RESOURCE_RATE, 1)) / (1 - NVL(SHRINKAGE_RATE, 0))), EXT_PREC),
1005 SYSDATE,
1006 I_LAST_UPDATED_BY,
1007 I_REQUEST_ID,
1008 I_PROGRAM_APPL_ID,
1009 I_PROGRAM_ID,
1010 SYSDATE
1011 FROM MTL_SYSTEM_ITEMS
1012 WHERE ORGANIZATION_ID = I_ORG_ID
1013 AND INVENTORY_ITEM_ID = A.INVENTORY_ITEM_ID
1014 )
1015 WHERE ORGANIZATION_ID = I_ORG_ID
1016 AND COST_TYPE_ID = I_COST_TYPE_ID
1017 AND EXISTS
1018 (
1019 SELECT 'X'
1020 FROM CST_LISTS L
1021 WHERE L.LIST_ID = I_LIST_ID
1022 AND L.ENTITY_ID = A.INVENTORY_ITEM_ID
1023 )
1024 ;
1025
1026 O_RETURN_CODE := 0;
1027
1028 EXCEPTION
1029 WHEN NO_DATA_FOUND THEN
1030 O_RETURN_CODE := SQLCODE;
1031 WHEN OTHERS THEN
1032 O_RETURN_CODE := SQLCODE;
1033 raise_application_error(-20001, SQLERRM);
1034
1035 END CSTPSPSR;
1036 PROCEDURE CSTPULMC (
1037
1038 I_LIST_ID IN NUMBER,
1039 I_ORG_ID IN NUMBER,
1040 I_ACTIVITY_ID IN NUMBER,
1041 I_FROM_DATE IN DATE,
1042 I_TO_DATE IN DATE,
1043 I_ACCOUNT_ID IN NUMBER,
1044 I_BASIS_TYPE IN NUMBER,
1045 I_FIXED_RATE IN NUMBER,
1046 I_PER_INC IN NUMBER,
1047 I_AMT_INC IN NUMBER,
1048 I_COST_TYPE_ID IN NUMBER,
1049 I_COPY_COST_TYPE IN NUMBER,
1050 I_RESOURCE_ID IN NUMBER,
1051
1052 I_USER_ID IN NUMBER,
1053 I_REQUEST_ID IN NUMBER,
1054 I_PROGRAM_ID IN NUMBER,
1055 I_PROGRAM_APPL_ID IN NUMBER,
1056 O_RETURN_CODE IN OUT NOCOPY NUMBER) IS
1057
1058 ROUND_UNIT NUMBER;
1059 PRECISION NUMBER;
1060 EXT_PREC NUMBER;
1061
1062 BEGIN
1063
1064 O_RETURN_CODE := 9999;
1065
1066 CSTPUTIL.CSTPUGCI(I_ORG_ID, ROUND_UNIT, PRECISION, EXT_PREC);
1067
1068 /*===============================================================*/
1069 /* Removed any item FROM the list WHERE there is no AP invoice */
1070 /*===============================================================*/
1071
1072 /* =============================================================+
1073 The next delete is to fix bug number 302747. We will not
1074 update the cost of any item where the sum of the quantities
1075 invoiced is equal less than or equal to zero. After having done this
1076 the above statement is redundant. After having done this
1077 the above statement is redundant
1078 +==============================================================*/
1079
1080 --{BUG 5890227 -FPBUG 5705600
1081 /* =============================================================+
1082 This delete is to fix bug number 5705600. We will not
1083 update the cost of any item where the sum of the Invoiced Value or
1084 Quantity is equal less than or equal to zero. Since this results in
1085 Zero or negative item Costs.After having done this the above statement
1086 is redundant
1087 +==============================================================*/
1088
1089 DELETE FROM cst_lists L
1090 WHERE L.list_id = I_LIST_ID
1091 AND EXISTS
1092 (SELECT 1
1093 FROM mtl_uom_conversions_view UCR
1094 , po_lines_all PL
1095 , po_distributions_all PD
1096 , mtl_parameters MP
1097 , ap_invoice_distributions_all AIP
1098 WHERE NVL(AIP.posted_flag,'N') = 'Y'
1099 AND AIP.accounting_date
1100 BETWEEN NVL(I_FROM_DATE, AIP.accounting_date)
1101 AND NVL(I_TO_DATE, AIP.accounting_date)
1102 AND PD.po_distribution_id = AIP.po_distribution_id
1103 AND PD.destination_organization_id = MP.organization_id
1104 AND MP.cost_organization_id = I_ORG_ID
1105 AND PL.po_line_id = PD.po_line_id
1106 AND PL.item_id = L.entity_id
1107 AND UCR.inventory_item_id = PL.item_id
1108 AND UCR.organization_id = I_ORG_ID
1109 AND UCR.unit_of_measure = AIP.MATCHED_UOM_LOOKUP_CODE
1110 HAVING ((NVL(sum(quantity_invoiced * UCR.conversion_rate),0) <=0)
1111 OR (sum(nvl(AIP.base_amount, AIP.amount)) <0)));
1112
1113 fnd_file.put_line(fnd_file.log,to_char(SQL%ROWCOUNT)|| ' Items were deleted from list');
1114 fnd_file.put_line(fnd_file.log,'Since they had negative or zero Invoiced quantity or negative Invoiced value ');
1115
1116 /*==================================================================*/
1117 /* insert item cost WHERE there is AP invoice but no pending cost */
1118 /* information */
1119 /*==================================================================*/
1120 CSTPUMEC.CSTPEIIC(I_ORG_ID,
1121 I_COST_TYPE_ID,
1122 I_LIST_ID,
1123 I_RESOURCE_ID,
1124 I_USER_ID,
1125 I_REQUEST_ID,
1126 I_PROGRAM_ID,
1127 I_PROGRAM_APPL_ID,
1128 O_RETURN_CODE);
1129
1130 IF O_RETURN_CODE <> 0 THEN
1131 raise_application_error(-20001, 'CSTPULMC: '||SQLERRM);
1132 END IF;
1133
1134 UPDATE cst_item_cost_details CICD
1135 SET (
1136 usage_rate_or_amount
1137 , item_cost
1138 , last_update_date
1139 , last_updated_by
1140 , request_id
1141 , program_application_id
1142 , program_id
1143 , program_update_date
1144 ) = (
1145 SELECT sum(nvl(AIP.base_amount, AIP.amount)) /
1146 (sum(quantity_invoiced * UCR.conversion_rate))
1147 * (1 + i_per_inc/100) + i_amt_inc
1148 , round((sum(nvl(AIP.base_amount, AIP.amount)) /
1149 (sum(quantity_invoiced * UCR.conversion_rate))
1150 * (1 + i_per_inc/100) + i_amt_inc) *
1151 nvl((CICD.basis_factor * CICD.net_yield_or_shrinkage_factor *
1152 CICD.resource_rate),1), ext_prec)
1153 , SYSDATE
1154 , I_USER_ID
1155 , I_REQUEST_ID
1156 , I_PROGRAM_APPL_ID
1157 , I_PROGRAM_ID
1158 , SYSDATE
1159 FROM mtl_uom_conversions_view UCR
1160 , po_lines_all PL
1161 , po_distributions_all PD
1162 , mtl_parameters MP
1163 /* BUG#8876268, BUG#12696671, code changes for IPV and NONREC_TAX */
1164 , (SELECT base_amount
1165 , quantity_invoiced
1166 , amount
1167 , posted_flag
1168 , accounting_date
1169 , line_type_lookup_code
1170 , matched_uom_lookup_code
1171 , po_distribution_id
1172 FROM ap_invoice_distributions_all
1173 WHERE line_type_lookup_code IN ('ITEM','ACCRUAL')
1174 UNION ALL
1175 SELECT i.base_amount
1176 , 0 --l.quantity_invoiced because tax line quantity is included in accrual or item line quantity
1177 , i.amount
1178 , i.posted_flag
1179 , i.accounting_date
1180 , i.line_type_lookup_code
1181 , l.matched_uom_lookup_code
1182 , i.po_distribution_id
1183 FROM ap_invoice_distributions_all i
1184 , ap_invoice_distributions_all l
1185 WHERE i.line_type_lookup_code = 'NONREC_TAX'
1186 AND i.CHARGE_APPLICABLE_TO_DIST_ID = l.invoice_distribution_id
1187 AND l.line_type_lookup_code IN ('ITEM', 'ACCRUAL')
1188 UNION ALL
1189 SELECT i.base_amount
1190 , 0 --l.quantity_invoiced because IPV line quantity is included in accrual or item line quantity
1191 , i.amount
1192 , i.posted_flag
1193 , i.accounting_date
1194 , i.line_type_lookup_code
1195 , l.matched_uom_lookup_code
1196 , i.po_distribution_id
1197 FROM ap_invoice_distributions_all i
1198 , ap_invoice_distributions_all l
1199 WHERE i.line_type_lookup_code = 'IPV'
1200 AND i.related_id = l.related_id
1201 AND i.invoice_id = l.invoice_id
1202 AND l.line_type_lookup_code <> 'IPV') AIP
1203 WHERE AIP.posted_flag = 'Y'
1204 AND (I_FROM_DATE IS NULL OR (I_FROM_DATE IS NOT NULL and AIP.accounting_date >= I_FROM_DATE))
1205 AND (I_TO_DATE IS NULL OR (I_TO_DATE IS NOT NULL and AIP.accounting_date <= I_TO_DATE))
1206 /* Invoice Lines Project, as part of eTAX, TAX line type code is
1207 split into REC_TAX and NONREC_TAX. In addition, the ITEM
1208 could now be ITEM or ACCRUAL. These modifications incorporate
1209 these changes.
1210 */
1211 AND PD.po_distribution_id = AIP.po_distribution_id
1212 AND PD.destination_organization_id = MP.organization_id
1213 AND MP.cost_organization_id = I_ORG_ID
1214 AND PL.po_line_id = PD.po_line_id
1215 AND PL.item_id = CICD.inventory_item_id
1216 AND UCR.inventory_item_id = CICD.inventory_item_id
1217 AND UCR.organization_id = I_ORG_ID
1218 AND UCR.unit_of_measure = AIP.MATCHED_UOM_LOOKUP_CODE --BUG#5881736 PL.unit_meas_lookup_code
1219 )
1220 WHERE organization_id = I_ORG_ID
1221 AND cost_type_id = I_COST_TYPE_ID
1222 AND cost_element_id = 1 /* Material */
1223 AND resource_id = I_RESOURCE_ID
1224 AND rollup_source_type = 1 /* User Defined */
1225 AND nvl(activity_id,-1) = DECODE(I_ACTIVITY_ID,
1226 0, NVL(activity_id,-1),
1227 I_ACTIVITY_ID)
1228 AND NVL(basis_type,-1) = DECODE(I_BASIS_TYPE,
1229 0, NVL(basis_type,-1),
1230 I_BASIS_TYPE)
1231 AND CICD.inventory_item_id IN
1232 (SELECT entity_id
1233 FROM cst_lists
1234 WHERE list_id = I_LIST_ID);
1235
1236
1237 EXCEPTION
1238 WHEN NO_DATA_FOUND THEN
1239 O_RETURN_CODE := SQLCODE;
1240 WHEN OTHERS THEN
1241 O_RETURN_CODE := SQLCODE;
1242 raise_application_error(-20001, SQLERRM);
1243
1244 END CSTPULMC;
1245
1246 END CSTPMECS;