1 PACKAGE GMF_CMCOMMON AS
2 /* $Header: gmfcmcos.pls 120.6.12010000.1 2008/07/30 05:37:06 appldev 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
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,
27 * cost_cmpntcls_id cost_analysis
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 *
136 * when retrive_ind value is 2,3 or 4. *
137 * *
138 * The procedure below returns a value in v_status *
139 * -1 - no cost compoments exist or if v_index value *
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 FUNCTION Get_Process_Item_Cost
203 (
204 p_api_version IN NUMBER
205 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
206 , x_return_status OUT NOCOPY VARCHAR2
207 , x_msg_count OUT NOCOPY NUMBER
208 , x_msg_data OUT NOCOPY VARCHAR2
209 , p_inventory_item_id IN NUMBER /* Item_Id */
210 , p_organization_id IN NUMBER /* Inventory Organization Id */
211 , p_transaction_date IN DATE /* Cost as on date */
212 , p_detail_flag IN NUMBER /* same as retrieve indicator: */ /* 1 = total cost, 2 = details; */ /* 3 = cost for a specific component class/analysis code, etc. */
213 , p_cost_method IN OUT NOCOPY VARCHAR2 /* OPM Cost Method */
214 , p_cost_component_class_id IN OUT NOCOPY NUMBER
215 , p_cost_analysis_code IN OUT NOCOPY VARCHAR2
216 , x_total_cost OUT NOCOPY NUMBER /* total cost */
217 , x_no_of_rows OUT NOCOPY NUMBER /* number of detail rows retrieved */
218 )
219 RETURN NUMBER ;
220
221 FUNCTION Get_Process_Item_Cost
222 (
223 p_api_version IN NUMBER
224 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
225 , x_return_status OUT NOCOPY VARCHAR2
226 , x_msg_count OUT NOCOPY NUMBER
227 , x_msg_data OUT NOCOPY VARCHAR2
228 , p_inventory_item_id IN NUMBER /* Item_Id */
229 , p_organization_id IN NUMBER /* Inventory Organization Id */
230 , p_transaction_date IN DATE /* Cost as on date */
231 , p_detail_flag IN NUMBER /* same as retrieve indicator: */ /* 1 = total cost, 2 = details; */ /* 3 = cost for a specific component class/analysis code, etc. */
232 , p_cost_method IN OUT NOCOPY VARCHAR2 /* OPM Cost Method */
233 , p_cost_component_class_id IN OUT NOCOPY NUMBER
234 , p_cost_analysis_code IN OUT NOCOPY VARCHAR2
235 , x_total_cost OUT NOCOPY NUMBER /* total cost */
236 , x_no_of_rows OUT NOCOPY NUMBER /* number of detail rows retrieved */
237 , p_lot_number IN VARCHAR2 /* Lot Number for the Item/Lot */
238 , p_transaction_id IN NUMBER /* Transaction_id from MMT */
239 )
240 RETURN NUMBER ;
241
242 FUNCTION is_batch_cost_frozen
243 (
244 p_api_version IN NUMBER
245 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
246 , p_commit IN VARCHAR2 := FND_API.G_FALSE
247 , x_return_status OUT NOCOPY VARCHAR2
248 , x_msg_count OUT NOCOPY NUMBER
249 , x_msg_data OUT NOCOPY VARCHAR2
250 , p_batch_id IN gme_batch_header.batch_id%TYPE
251 )
252 RETURN BOOLEAN;
253
254 /*******************************************************
255 * Added by Anand Thiyagarajan ANTHIYAG 15-DEC-2004 End *
256 *******************************************************/
257
258 /**************************************
259 * OPM INVCONV umoogala 10-Feb-2004 *
260 **************************************/
261
262 PROCEDURE get_process_item_unit_price
263 (
264 p_inventory_item_id IN NUMBER
265 , p_organization_id IN NUMBER
266 , p_trans_date IN DATE
267 , x_unit_price OUT NOCOPY NUMBER
268 , x_return_status OUT NOCOPY VARCHAR2
269 );
270
271 PROCEDURE get_process_item_price
272 (
273 p_inventory_item_id IN NUMBER
274 , p_trans_qty IN NUMBER
275 , p_trans_uom IN VARCHAR2
276 , p_trans_date IN DATE
277 , p_src_organization_id IN NUMBER
278 , p_src_process_enabled_flag IN VARCHAR2
279 , p_dest_organization_id IN NUMBER
280 , p_dest_process_enabled_flag IN VARCHAR2
281 , p_source IN VARCHAR2
282 , x_unit_price OUT NOCOPY NUMBER
283 , x_unit_price_priuom OUT NOCOPY NUMBER
284 , x_currency_code OUT NOCOPY VARCHAR2
285 , x_incr_transfer_price OUT NOCOPY NUMBER
286 , x_incr_currency_code OUT NOCOPY VARCHAR2
287 , x_return_status OUT NOCOPY NUMBER
288 );
289
290 /******************************************
291 * End OPM INVCONV umoogala 10-Feb-2004 *
292 ******************************************/
293
294 FUNCTION process_item_unit_cost
295 (
296 p_inventory_item_id IN NUMBER,
297 p_organization_id IN NUMBER,
298 p_transaction_date IN DATE
299 )
300 RETURN NUMBER;
301
302 FUNCTION process_item_unit_cost
303 (
304 p_inventory_item_id IN NUMBER,
305 p_organization_id IN NUMBER,
306 p_transaction_date IN DATE,
307 p_lot_number IN VARCHAR2,
308 p_transaction_id IN NUMBER
309 )
310 RETURN NUMBER ;
311
312 /***************************************************
313 * Bug#5436964 ANTHIYAG BatchesXPeriods 20-Feb-2007 *
314 ***************************************************/
315
316 FUNCTION get_cmpt_cost
317 (
318 p_inventory_item_id IN NUMBER,
319 p_organization_id IN NUMBER,
320 p_transaction_date IN DATE,
321 p_cost_type_id IN NUMBER,
322 p_prior_period_cost IN NUMBER
323 )
324 RETURN NUMBER;
325
326 FUNCTION get_rsrc_cost
327 (
328 p_resources IN VARCHAR2,
329 p_organization_id IN NUMBER,
330 p_transaction_date IN DATE,
331 p_cost_type_id IN NUMBER,
332 p_prior_period_cost IN NUMBER
333 )
334 RETURN NUMBER;
335
336 /***************************************************
337 * Bug#5436964 ANTHIYAG BatchesXPeriods 20-Feb-2007 *
338 ***************************************************/
339
340 END GMF_CMCOMMON ;