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