1 PACKAGE BODY CST_COST_API AS
2 /* $Header: CSTCAPIB.pls 120.2 2007/11/07 00:13:51 ipineda ship $ */
3
4 -------------------------------------------------------------------------
5 -- get_item_cost()
6 --
7 -- Synopsis:
8 --
9 -- This function returns the item cost for a given item and organization.
10 -- This function accepts only IN parameters, and returns a single value.
11 -- This makes the API simple to call as part of SQL statements.
12 --
13 -- You may also pass either the cost group ID or the cost type ID,
14 -- if you desire more specific cost information. Do not pass both the
15 -- cost group and the cost type at the same time.
16 --
17 -- For now, pass p_api_version = 1.
18 --
19 -- This API currently does not support the retrieval of specific layer
20 -- costs within a FIFO/LIFO organization.
21 --
22 --
23 -- Code Details:
24 --
25 -- The output of this function is dependent on the primary cost method
26 -- of the organization, and which of the parameters were specified.
27 -- Note that you should never specify both the cost_type_id and
28 -- the cost_group_id together; to not pass a parameter, use NULL.
29 --
30 -- For FIFO/LIFO, the cost group average from CQL is returned.
31 --
32 -- Parameters | Standard | Average/FIFO/LIFO
33 -- -----------------------+-----------------------+----------------------
34 -- item, org | Frozen from CIC | Default CG from CQL
35 -- item, org, cost_group | Frozen from CIC | Specified CG from CQL
36 -- item, org, cost_type | Specified CT from CIC | Specified CT from CIC
37 --
38 -- The precision parameter is used to determinate what type of rounding
39 -- should be applied to the item cost value that is returned. The number
40 -- specified for this parameter represents the number of decimal digits
41 -- that the output will be rounded to and its defaulted as NULL.
42 --
43 -- Error Conditions:
44 --
45 -- For all errors, the returned value is NULL.
46 --
47 -- The possible error conditions are:
48 -- Invalid item/organization combination.
49 -- Item is not cost enabled.
50 -- Item has no cost in the specified cost group or cost type.
51 ----------------------------------------------------------------------
52
53 function get_item_cost (
54 p_api_version in number,
55 p_inventory_item_id in number,
56 p_organization_id in number,
57 p_cost_group_id in number default NULL,
58 p_cost_type_id in number default NULL,
59 p_precision in number default NULL
60 )
61 return number
62 is
63
64 l_stmt_num number := 0;
65 l_item_cost number := NULL;
66 l_cost_method_id number := NULL;
67 l_default_cost_group_id number := NULL;
68
69 begin
70
71
72 if p_cost_group_id is not NULL AND p_cost_type_id is not NULL then
73 return NULL;
74 end if;
75
76
77 l_stmt_num := 10;
78 select MP.primary_cost_method,
79 MP.default_cost_group_id
80 into l_cost_method_id,
81 l_default_cost_group_id
82 from mtl_parameters MP
83 where MP.organization_id = p_organization_id;
84
85 -- Changes introduced in if statement to select the item cost from the
86 -- Cost Organization please refer to bug 6431253 for further
87 -- information. 09/19/2007
88 if p_cost_type_id is not NULL OR l_cost_method_id = 1 then
89
90 l_stmt_num := 20;
91 select CIC.item_cost
92 into l_item_cost
93 from cst_item_costs CIC,
94 mtl_parameters mp
95 where CIC.inventory_item_id = p_inventory_item_id AND
96 CIC.organization_id = mp.cost_organization_id AND
97 CIC.cost_type_id = NVL( p_cost_type_id, 1 ) AND
98 mp.organization_id = p_organization_id ;
99
100 else
101
102 l_stmt_num := 30;
103 select CQL.item_cost
104 into l_item_cost
105 from cst_quantity_layers CQL,
106 mtl_parameters mp
107 where CQL.inventory_item_id = p_inventory_item_id AND
108 CQL.organization_id = mp.cost_organization_id AND
109 CQL.cost_group_id = NVL( p_cost_group_id,
110 l_default_cost_group_id ) AND
111 mp.organization_id = p_organization_id ;
112
113 end if;
114
115 /*Bug6514166: Added rounding functionality to the returned value*/
116 if p_precision is NULL then
117 return l_item_cost;
118 else
119 return round(l_item_cost,p_precision);
120 end if;
121
122
123 exception
124 when others then
125 return NULL;
126
127 end get_item_cost;
128
129
130 END CST_COST_API;
[Home] [Help]