1 PACKAGE GMF_CMCOMMON AUTHID CURRENT_USER AS
2 /* $Header: gmfcmcos.pls 120.7 2011/02/01 21:18:13 pkanetka ship $ */
3
4 /* **************************************************************************************************
5 * FUNCTION
6 * cmcommon_get_cost
7 * DESCRIPTION
8 * Retrieves item cost, cost type and fmeff_id from gl_item_cst
9 * and cmptcost_amt from gl_item_dtl get_cost should return the cost of the item
10 * for the cost warehouse if there is a cost warehouse associated with the given
11 * warehouse else it should return the cost of the item for the given warehouse
12 *
13 * AUTHOR
14 * Tapas Banerjee 04/30/1992
15 *
16 * INPUT PARAMETERS
17 * item_id = Item id
18 * whse_code = Warehouse code
19 * orgn_code = Organization Code
20 * trans_date = Date of item cost
21 * cost_mthd = cost method used
22 * cmpntcls_id = component class id
23 * analysis_code = analysis code
27 * cost_cmpntcls_id cost_analysis
24 * retreive_ind = 1 or retreive just acctg_cost
25 * 2 retreive acctg_cost all cmptcost_amts for itemcost
26 * 3 retreive acctg_cost cmptcost_amt for itemcost_id,
28 * 4 retrieve array of PPV/Matl CC costs, Cls Id/AnCd
29 * in P_cmpntcost_amt,P_cost_cmpntcls_id, P_cost_analysis_code
30 * 5 retrieve total of PPV/Matl CC costs in total_cost
31 *
32 * OUTPUT PARAMETERS
33 * P_acctg_cost = used to receive acctg_cost
34 * P_cost_type = used to receive cost_type
35 * P_fmeff_id = used to receive fmeff_id
36 * P_cmntcost_amt() = used to receive cmptcost_amt from gl_item_dtl
37 * P_cost_cmpntcls_id() = for retrieve mode 4 Component Class Ids
38 * P_cost_analysis_code = for retrieve mode 4 Analysis Codes
39 *
40 * total_cost = This out parameter should be reffered only in the
41 * case when retrieve_ind value is passed as 1 or 5.
42 * cost_mthd = used to return gl_cost_mthd.
43 *
44 * declare Global cached orgn_code and cost_mthd_code vars.
45 * If repeated calls are made to this routine for same organization
46 * and no cost method is passed as a parameter than it will make
47 * use of these global cached orgn code and cached cost method and
48 * will not try to get cost method from the GL Fiscal policy. The
49 * Package variable P_cached_cost_whse_code is used to hold the cost
50 * warehouse value if retrieved otherwise it will hold the value of
51 * the warehouse passed to this routine. The package variable
52 * P_cached_cost_basis is used to hold cost_basis retrieved from
53 * fiscal Policy.
54 *
55 * P_cached_orgn_code = Caches orgn_Code
56 * P_cached_gl_cost_mthd = Caches cost_mthd_code
57 * P_cached_cost_basis = Caches cost_basis
58 * P_cached_cost_whse_code = Caches cost_whse_code
59 *
60 * The variable P_no_of_rows is populated by get_cost routine when retrieve_ind is 2,3,4
61 * to notify how many array cell rows are populated with values.
62 *
63 * P_no_of rows = Caches the no of rows, retrieved and stored in array.
64 *
65 * RETURNS (choose one set)
66 * 1 success
67 * -1 No cost found.
68 * -2 Error in parameters passed
69 * -3 No GL Fiscal policy ( Unable to get the cost method from Fiscal plcy)
70 * HISTORY
71 * Sukarna Reddy Programmer 09/26/98 Converted JPL to PLSQL.
72 *
73 * 20-Feb-2002 Uday Moogala Bug# 2231928
74 * Added a new function to get_sort_sequence to return sort_sequence for
75 * the cost_cmpntcls_id from cm_cmpt_mst table. Used in CMCSDED forms
76 * This Level and Lower Level detail blocks ORDER BY property.
77 * 30/Oct/2002 R.Sharath Kumar Bug# 2641405
78 * Added NOCOPY hint
79 ************************************************************************************** */
80
81 TYPE cmpnt_cost IS TABLE OF gl_item_dtl.cmptcost_amt%TYPE
82 INDEX BY BINARY_INTEGER;
83
84 TYPE cmpnt_id IS TABLE OF cm_cmpt_mst.cost_cmpntcls_id%TYPE
85 INDEX BY BINARY_INTEGER;
86
87 TYPE analysis_code IS TABLE OF cm_alys_mst.cost_analysis_code%TYPE
88 INDEX BY BINARY_INTEGER;
89
90 TYPE gmf_lot_cost_cmpnts IS TABLE OF GMF_LOT_COST_DETAILS%ROWTYPE
91 INDEX BY BINARY_INTEGER;
92
93
94 P_cmpntcost_amt cmpnt_cost;
95 P_cost_cmpntcls_id cmpnt_id;
96 P_cost_analysis_code1 analysis_code;
97
98 P_acctg_cost gl_item_cst.acctg_cost%TYPE; /* used to receive acctg_cost*/
99 P_cost_type gl_item_cst.cost_type%TYPE; /* used to receive cost_type*/
100 P_fmeff_id gl_item_cst.fmeff_id%TYPE; /* used to receive fmeff_id*/
101 P_cached_gl_cost_mthd gmf_fiscal_policies.cost_type_id%TYPE;
102 P_cached_cost_whse_code cm_whse_asc.organization_id%TYPE;
103 P_cached_orgn_code sy_orgn_mst.orgn_code%TYPE;
104 P_cached_co_code sy_orgn_mst.co_code%TYPE;
105
106 P_cached_legal_entity_id gmf_fiscal_policies.legal_entity_id%TYPE;
107 P_cached_cost_organization_id cm_whse_asc.cost_organization_id%TYPE;
108 P_cached_cost_basis gmf_fiscal_policies.cost_basis%TYPE;
109 P_cached_cost_type_id gmf_fiscal_policies.cost_type_id%TYPE;
110
111 /****************************************************************************************
112 * The variable P_no_of_rows is populated by get_cost routine when retrieve_ind is 2,3,4 *
113 * to notify how many array cell rows are populated with values. *
114 ****************************************************************************************/
115
116 P_no_of_rows NUMBER DEFAULT 0;
117
118 FUNCTION cmcommon_get_cost
119 (
120 item_id IN NUMBER,
121 whse_code IN VARCHAR2,
122 orgn_code IN VARCHAR2,
123 trans_date IN DATE,
124 cost_mthd IN OUT NOCOPY VARCHAR2,
125 cmpntcls_id IN OUT NOCOPY NUMBER,
126 analysis_code IN OUT NOCOPY VARCHAR2,
127 retreive_ind IN NUMBER,
128 total_cost OUT NOCOPY NUMBER,
129 no_of_rows OUT NOCOPY NUMBER
130 )
131 RETURN NUMBER;
132
133 /**********************************************************************************************
134 * The procedure below should be used only after calling cmcommon_get_cost *
135 * function to retrieve the multiple cost . This procedure should be invoked *
139 * -1 - no cost compoments exist or if v_index value *
136 * when retrive_ind value is 2,3 or 4. *
137 * *
138 * The procedure below returns a value in v_status *
140 * is 0 or if it exceeds the value more than P_no_of_rows variable. *
141 * 0 - successful *
142 * USAGE *
143 * Use this procedure in a LOOP to retrive the values row by row by specifying *
144 * the v_index *
145 * DESCRIPTION *
146 * Helps in retrieving the values of cost_cmpntcls id and analysis_code *
147 * cmpnt_amt when retrieve_ind is 4 and retrieves the cmpnt_amt when retrieved *
148 * ind is 2 or 3. The retrieve ind is introduced in this procedure for the reason *
149 * that the arrays such as P_cost_cmpntcls_id and P_cost_analysis_code and P_cmpntcost_amt *
150 * gets populated only when retrieve ind is 4 and would not get populated when retrieve ind *
151 * is 2 or 3 except for P_cmpntcost_amt as a result we end up accessing junk memory array *
152 * cells which does not hold any data. Ensure that you pass same indicator value used for *
153 * get_cost routine before invoking this routine *
154 **********************************************************************************************/
155
156 PROCEDURE get_multiple_cmpts_cost
157 (
158 v_index IN NUMBER,
159 v_cost_cmpntcls_id OUT NOCOPY NUMBER,
160 v_cost_analysis_code OUT NOCOPY VARCHAR2,
161 v_cmpnt_amt OUT NOCOPY NUMBER,
162 v_retrieve_ind IN NUMBER,
163 v_status OUT NOCOPY NUMBER
164 );
165
166 FUNCTION get_sort_sequence
167 (
168 v_cost_cmpntcls_id IN NUMBER
169 )
170 RETURN NUMBER;
171
172 FUNCTION unit_cost
173 (
174 v_item_id IN NUMBER,
175 v_whse_code IN VARCHAR2,
176 v_orgn_code IN VARCHAR2,
177 v_trans_date IN DATE
178 )
179 RETURN NUMBER;
180
181 FUNCTION cmcommon_get_cost
182 (
183 p_item_id IN NUMBER,
184 p_whse_code IN VARCHAR2,
185 p_orgn_code IN VARCHAR2,
186 p_trans_date IN DATE,
187 p_cost_mthd IN OUT NOCOPY VARCHAR2,
188 p_cmpntcls_id IN OUT NOCOPY NUMBER,
189 p_analysis_code IN OUT NOCOPY VARCHAR2,
190 p_retrieve_ind IN NUMBER,
191 x_total_cost OUT NOCOPY NUMBER,
192 x_no_of_rows OUT NOCOPY NUMBER,
193 p_lot_id IN NUMBER,
194 p_trans_id IN NUMBER
195 )
196 RETURN NUMBER;
197
198 /*********************************************************
199 * Added by Anand Thiyagarajan ANTHIYAG 15-DEC-2004 start *
200 *********************************************************/
201
202 /* PK Bug 10370554 added p_from_where, p_fiscal_year, p_period for inventory valuation report */
203
204 FUNCTION Get_Process_Item_Cost
205 (
206 p_api_version IN NUMBER
207 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
208 , x_return_status OUT NOCOPY VARCHAR2
209 , x_msg_count OUT NOCOPY NUMBER
210 , x_msg_data OUT NOCOPY VARCHAR2
211 , p_inventory_item_id IN NUMBER /* Item_Id */
212 , p_organization_id IN NUMBER /* Inventory Organization Id */
213 , p_transaction_date IN DATE /* Cost as on date */
214 , p_detail_flag IN NUMBER /* same as retrieve indicator: */ /* 1 = total cost, 2 = details; */ /* 3 = cost for a specific component class/analysis code, etc. */
215 , p_cost_method IN OUT NOCOPY VARCHAR2 /* OPM Cost Method */
216 , p_cost_component_class_id IN OUT NOCOPY NUMBER
217 , p_cost_analysis_code IN OUT NOCOPY VARCHAR2
218 , x_total_cost OUT NOCOPY NUMBER /* total cost */
219 , x_no_of_rows OUT NOCOPY NUMBER /* number of detail rows retrieved */
220 , p_from_where IN VARCHAR2 DEFAULT 'NOT_LOT_COSTS'
221 , p_fiscal_year IN NUMBER DEFAULT NULL
222 , p_period IN NUMBER DEFAULT NULL
223 )
224 RETURN NUMBER ;
225
226 FUNCTION Get_Process_Item_Cost
227 (
228 p_api_version IN NUMBER
229 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
230 , x_return_status OUT NOCOPY VARCHAR2
231 , x_msg_count OUT NOCOPY NUMBER
232 , x_msg_data OUT NOCOPY VARCHAR2
233 , p_inventory_item_id IN NUMBER /* Item_Id */
234 , p_organization_id IN NUMBER /* Inventory Organization Id */
235 , p_transaction_date IN DATE /* Cost as on date */
236 , p_detail_flag IN NUMBER /* same as retrieve indicator: */ /* 1 = total cost, 2 = details; */ /* 3 = cost for a specific component class/analysis code, etc. */
237 , p_cost_method IN OUT NOCOPY VARCHAR2 /* OPM Cost Method */
238 , p_cost_component_class_id IN OUT NOCOPY NUMBER
239 , p_cost_analysis_code IN OUT NOCOPY VARCHAR2
240 , x_total_cost OUT NOCOPY NUMBER /* total cost */
241 , x_no_of_rows OUT NOCOPY NUMBER /* number of detail rows retrieved */
242 , p_lot_number IN VARCHAR2 /* Lot Number for the Item/Lot */
243 , p_transaction_id IN NUMBER /* Transaction_id from MMT */
244 )
245 RETURN NUMBER ;
246
247 FUNCTION is_batch_cost_frozen
248 (
249 p_api_version IN NUMBER
250 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
251 , p_commit IN VARCHAR2 := FND_API.G_FALSE
252 , x_return_status OUT NOCOPY VARCHAR2
253 , x_msg_count OUT NOCOPY NUMBER
254 , x_msg_data OUT NOCOPY VARCHAR2
255 , p_batch_id IN gme_batch_header.batch_id%TYPE
256 )
257 RETURN BOOLEAN;
258
259 /*******************************************************
260 * Added by Anand Thiyagarajan ANTHIYAG 15-DEC-2004 End *
261 *******************************************************/
262
263 /**************************************
264 * OPM INVCONV umoogala 10-Feb-2004 *
265 **************************************/
266
267 PROCEDURE get_process_item_unit_price
268 (
269 p_inventory_item_id IN NUMBER
270 , p_organization_id IN NUMBER
271 , p_trans_date IN DATE
272 , x_unit_price OUT NOCOPY NUMBER
273 , x_return_status OUT NOCOPY VARCHAR2
274 );
275
276 PROCEDURE get_process_item_price
277 (
278 p_inventory_item_id IN NUMBER
279 , p_trans_qty IN NUMBER
280 , p_trans_uom IN VARCHAR2
281 , p_trans_date IN DATE
282 , p_src_organization_id IN NUMBER
283 , p_src_process_enabled_flag IN VARCHAR2
284 , p_dest_organization_id IN NUMBER
285 , p_dest_process_enabled_flag IN VARCHAR2
286 , p_source IN VARCHAR2
287 , x_unit_price OUT NOCOPY NUMBER
288 , x_unit_price_priuom OUT NOCOPY NUMBER
289 , x_currency_code OUT NOCOPY VARCHAR2
290 , x_incr_transfer_price OUT NOCOPY NUMBER
291 , x_incr_currency_code OUT NOCOPY VARCHAR2
292 , x_return_status OUT NOCOPY NUMBER
293 );
294
295 /******************************************
296 * End OPM INVCONV umoogala 10-Feb-2004 *
297 ******************************************/
298
299 FUNCTION process_item_unit_cost
300 (
301 p_inventory_item_id IN NUMBER,
302 p_organization_id IN NUMBER,
303 p_transaction_date IN DATE
304 )
305 RETURN NUMBER;
306
307 FUNCTION process_item_unit_cost
308 (
309 p_inventory_item_id IN NUMBER,
310 p_organization_id IN NUMBER,
311 p_transaction_date IN DATE,
312 p_lot_number IN VARCHAR2,
313 p_transaction_id IN NUMBER
314 )
315 RETURN NUMBER ;
316
317 /***************************************************
318 * Bug#5436964 ANTHIYAG BatchesXPeriods 20-Feb-2007 *
319 ***************************************************/
320
321 FUNCTION get_cmpt_cost
322 (
323 p_inventory_item_id IN NUMBER,
324 p_organization_id IN NUMBER,
325 p_transaction_date IN DATE,
326 p_cost_type_id IN NUMBER,
327 p_prior_period_cost IN NUMBER
328 )
329 RETURN NUMBER;
330
331 FUNCTION get_rsrc_cost
332 (
333 p_resources IN VARCHAR2,
334 p_organization_id IN NUMBER,
335 p_transaction_date IN DATE,
336 p_cost_type_id IN NUMBER,
337 p_prior_period_cost IN NUMBER
338 )
339 RETURN NUMBER;
340
341 /***************************************************
342 * Bug#5436964 ANTHIYAG BatchesXPeriods 20-Feb-2007 *
343 ***************************************************/
344
345 END GMF_CMCOMMON ;