1 PACKAGE BODY GMF_CMCOMMON AS
2 /* $Header: gmfcmcob.pls 120.19.12020000.3 2012/09/13 13:35:31 smukalla 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 * Manish Gupta 02-MAR-99 Bug 841019
74 * Commented Package DBMS_OUTPUT.
75 * 14-Dec-1999 Rajesh Seshadri Bug 1111582 - Get cost returns cost even if cost
76 * does not exist for the date passed in. Apparently the query did not
77 * use the trans_date or whse_code. Modified the query.
78 * Also removed the usage_ind condition from the queries when retrieve_ind
79 * is 4 or 5 [PPV calculations use retrieve_ind of 4 or 5]. Refer B1019295
80 * Introduced cmcommon_log for printing trace messages
81 * 20-Feb-2002 Uday Moogala Bug# 2231928
82 * Added a new function to get_sort_sequence to return sort_sequence for
83 * the cost_cmpntcls_id from cm_cmpt_mst table. Used in CMCSDED forms
84 * This Level and Lower Level detail blocks ORDER BY property.
85 * 30/Oct/2002 R.Sharath Kumar Bug# 2641405
86 * Added NOCOPY hint
87 * 28-Sep-2001 Venkat Chukkapalli Bug 1926529 - Modified code to use cost
88 * warehouse org in retrieving cost when cost warehouse association exists.
89 ************************************************************************************** */
90
91 /*********************************************************
92 * Added by Anand Thiyagarajan ANTHIYAG 15-DEC-2004 Start *
93 *********************************************************/
94
95 G_PKG_NAME CONSTANT VARCHAR2(30):= 'GMF_CMCOMMON';
96 G_DEBUG_LEVEL NUMBER := FND_MSG_PUB.G_Msg_Level_Threshold;
97
98 /*******************************************************
99 * Added by Anand Thiyagarajan ANTHIYAG 15-DEC-2004 End *
100 *******************************************************/
101
102 PROCEDURE cmcommon_log( pmsg IN VARCHAR2 );
103
104 FUNCTION cmcommon_get_cost(item_id IN NUMBER,
105 whse_code IN VARCHAR2,
106 orgn_code IN VARCHAR2,
107 trans_date IN DATE,
108 cost_mthd IN OUT NOCOPY VARCHAR2,
109 cmpntcls_id IN OUT NOCOPY NUMBER,
110 analysis_code IN OUT NOCOPY VARCHAR2,
111 retreive_ind IN NUMBER,
112 total_cost OUT NOCOPY NUMBER,
113 no_of_rows OUT NOCOPY NUMBER)
114 RETURN NUMBER IS
115 -- PK Eliminated obsolete code Get_Process_Item_Cost is proper 12.0 procedure.
116
117 BEGIN
118 RETURN(1);
119 END cmcommon_get_cost;
120
121
122 /***************************************************************************************************************
123 * PROCEDURE
124 * get_multiple_cmpts_cost
125 *
126 * DESCRIPTION
127 * Helps in retrieving the values of cost_cmpntcls id and analysis_code
128 * cmpnt_amt when retrieve_ind is 4 and retrieves the cmpnt_amt when retrieved
129 * ind is 2 or 3. The retrieve ind is introduced in this procedure for the reason
130 * that the arrays such as P_cost_cmpntcls_id and P_cost_analysis_code and P_cmpntcost_amt
131 * gets populated only when retrieve ind is 4 and would not get populated when retrieve ind
132 * is 2 or 3 except for P_cmpntcost_amt.As a result we end up accessing junk memory array
133 * cells which does not hold any data. Ensure that you pass same indicator value used for
134 * get_cost routine before invoking this routine.
135 *
136 *
137 * AUTHOR
138 * sukarna Reddy 09/26/98
139 *
140 * ASSUMPTION
141 * Before calling this procedure it is assumed that cmcommon_get_cost routine is called.
142 *
143 * INPUT PARAMETERS
144 * v_index = Index of the array cell for P_cost_analysis_code() or
145 * or P_cost_cmpntcls_id(),P_cmpntcost_amt().
146 * v_retrieve_ind =
147 * 2 Retreive acctg_cost all cmptcost_amts for itemcost
148 * 3 Retreive acctg_cost cmptcost_amt for itemcost_id,
149 * cost_cmpntcls_id cost_analysis
150 * 4 retrieve array of PPV/Matl CC costs, Cls Id/AnCd
151 * in P_cmpntcost_amt,P_cost_cmpntcls_id, P_cost_analysis_code
152 * OUTPUT PARAMETERS
153 * v_cost_cmpntcls_id = refer to this value only when retrieve_ind is 4
154 * v_cost_analysis_code = refer to this value only when retrieve_ind is 4
155 * v_cmpnt_amt = refer to this value only when retrieve_ind is 2,3,4
156 * v_status = 0 sucessfull
157 * -1 no cost compoments exist or if v_index value
158 * is 0 or if it exceeds the value more than
159 * P_no_of_rows variable.
160 *
161 *
162 * USAGE
163 * The procedure below should be used only after calling cmcommon_get_cost
164 * function to retrieve the multiple cost.Use this procedure in a LOOP
165 * to retrive the values row by row by specifying the v_index.
166 *
167 * HISTORY
168 *
169 *
170 ****************************************************************************************************************/
171
172 PROCEDURE get_multiple_cmpts_cost(v_index IN NUMBER,
173 v_cost_cmpntcls_id OUT NOCOPY NUMBER,
174 v_cost_analysis_code OUT NOCOPY VARCHAR2,
175 v_cmpnt_amt OUT NOCOPY NUMBER,
176 v_retrieve_ind IN NUMBER,
177 v_status OUT NOCOPY NUMBER) IS
178 BEGIN
179 /*The variable P_no_of_rows is populated by get_cost routine when retrieve_ind is 2,3,4
180 to notify how many array cell rows are populated with values. */
181
182 IF ( v_index <= P_no_of_rows AND v_index > 0) THEN
183 IF (v_retrieve_ind IN (2,3)) THEN
184 v_cmpnt_amt := P_cmpntcost_amt(v_index);
185 v_status := 0;
186 ELSIF(v_retrieve_ind = 4) THEN
187 v_cost_cmpntcls_id := P_cost_cmpntcls_id(v_index);
188 v_cost_analysis_code := P_cost_analysis_code1(v_index);
189 v_cmpnt_amt := P_cmpntcost_amt(v_index);
190 v_status := 0;
191 END IF;
192 ELSE
193 v_status := -1;
194 END IF;
195 END get_multiple_cmpts_cost;
196
197 /**
198 * Output log messages
199 */
200
201 PROCEDURE cmcommon_log( pmsg IN VARCHAR2 )
202 IS
203 l_dt VARCHAR2(64);
204
205 BEGIN
206 l_dt := TO_CHAR(SYSDATE, 'yyyy-mm-dd hh24:mi:ss');
207 fnd_file.put_line(fnd_file.log,pmsg||' '||l_dt);
208 END cmcommon_log;
209
210 /************************************************************************************
211 * PROCEDURE
212 * get_sort_sequence
213 *
214 * DESCRIPTION
215 * This function will return sort_sequence for incomming
216 * the cost_cmpntcls_id from cm_cmpt_mst table. Used in CMCSDED forms
217 * This Level and Lower Level detail blocks ORDER BY property.
218 *
219 * AUTHOR
220 * Uday Moogala 20-Feb-2002 Bug 2231928
221 *
222 * INPUT PARAMETERS
223 * v_cost_cmpntcls_id Cost Component Class Id comming from form CMCSDED.fmb
224 *
225 * OUTPUT PARAMETERS
226 * Returns sort sequence
227 *
228 * HISTORY
229 *
230 **************************************************************************************/
231
232 FUNCTION get_sort_sequence(v_cost_cmpntcls_id IN NUMBER) RETURN NUMBER
233 IS
234 CURSOR sort_seq(p_ccc_id NUMBER)
235 IS
236 SELECT DECODE(sort_sequence, 0, NULL, sort_sequence) sort_sequence
237 FROM cm_cmpt_mst
238 WHERE cost_cmpntcls_id = p_ccc_id ;
239
240 l_sort_sequence cm_cmpt_mst.sort_sequence%TYPE;
241
242 BEGIN
243
244 OPEN sort_seq(v_cost_cmpntcls_id) ;
245 FETCH sort_seq INTO l_sort_sequence;
246 CLOSE sort_seq ;
247
248 RETURN l_sort_sequence;
249
250 EXCEPTION
251 WHEN OTHERS THEN
252 RETURN 0 ;
253 END get_sort_sequence;
254
255 /************************************************************************************
256 * FUNCTION
257 * unit_cost
258 *
259 * DESCRIPTION
260 * This is a wrapper function which calls cmcommon_get_cost to return
261 * the cost.This function is called from PPV report(POXRCPPV.rdf)
262 *
263 * AUTHOR
264 * Mahesh Chandak 6-MAR-2002 Bug 2245477
265 *
266 * INPUT PARAMETERS
267 * v_item_id
268 * v_whse_code
269 * v_orgn_code
270 * v_trans_date
271 *
272 * OUTPUT PARAMETERS
273 * Returns cost of an item.
274 *
275 * HISTORY
276 *
277 **************************************************************************************/
278
279 FUNCTION unit_cost(v_item_id IN NUMBER,
280 v_whse_code IN VARCHAR2,
281 v_orgn_code IN VARCHAR2,
282 v_trans_date IN DATE ) RETURN NUMBER IS
283 x_unitcost NUMBER;
284 x_ret NUMBER;
285 x_cmpntclsid NUMBER;
286 x_analysiscode VARCHAR2(100);
287 x_costmthd VARCHAR2(100);
288 x_norows NUMBER;
289
290 -- PK Eliminated obsolete code
291
292 BEGIN
293
294 RETURN(null);
295
296
297 EXCEPTION
298 WHEN OTHERS THEN
299 RETURN(null) ;
300 END unit_cost;
301
302
303
304 /* **************************************************************************************************
305 * FUNCTION
306 * cmcommon_get_cost
307 * DESCRIPTION
308 *
309 * This function is an overloaded function of get cost routine. This function
310 * can be used to get costs for non lot cost method or lot cost method.
311 *
312 * For non lot cost method this function retrieves item cost, cost type
313 * and fmeff_id from gl_item_cst and cmptcost_amt from gl_item_dtl
314 * get_cost should return the cost of the item for the cost warehouse
315 * if there is a cost warehouse associated with the given warehouse else
316 * it should return the cost of the item for the given warehouse
317 *
318 * AUTHOR
319 * Sukarna Reddy Dt 20-OCT-2003 Bug 3196846
320 *
321 * INPUT PARAMETERS
322 * item_id = Item id
323 * whse_code = Warehouse code
324 * orgn_code = Organization Code
325 * trans_date = Date of item cost
326 * cost_mthd = cost method used
327 * cmpntcls_id = component class id
328 * analysis_code = analysis code
329 * retreive_ind = 1 or retreive just acctg_cost
330 * 2 retreive acctg_cost all cmptcost_amts for itemcost
331 * 3 retreive acctg_cost cmptcost_amt for itemcost_id,
332 * cost_cmpntcls_id cost_analysis
333 * 4 retrieve array of PPV/Matl CC costs, Cls Id/AnCd
334 * in P_cmpntcost_amt,P_cost_cmpntcls_id, P_cost_analysis_code
335 * 5 retrieve total of PPV/Matl CC costs in total_cost
336 lot_id = Lot id. Should be passed to retrieve specific lot cost.
337 trans_id = Lot cost transaction id.
338 *
339 * OUTPUT PARAMETERS
340 * P_acctg_cost = used to receive acctg_cost
341 * P_cost_type = used to receive cost_type
342 * P_fmeff_id = used to receive fmeff_id
343 * P_cmntcost_amt() = used to receive cmptcost_amt from gl_item_dtl
344 * P_cost_cmpntcls_id() = for retrieve mode 4 Component Class Ids
345 * P_cost_analysis_code = for retrieve mode 4 Analysis Codes
346 *
347 * total_cost = This out parameter should be reffered only in the
348 * case when retrieve_ind value is passed as 1 or 5.
349 * cost_mthd = used to return gl_cost_mthd.
350 *
351 * declare Global cached orgn_code and cost_mthd_code vars.
352 * If repeated calls are made to this routine for same organization
353 * and no cost method is passed as a parameter than it will make
354 * use of these global cached orgn code and cached cost method and
355 * will not try to get cost method from the GL Fiscal policy. The
356 * Package variable P_cached_cost_whse_code is used to hold the cost
357 * warehouse value if retrieved otherwise it will hold the value of
358 * the warehouse passed to this routine. The package variable
359 * P_cached_cost_basis is used to hold cost_basis retrieved from
360 * fiscal Policy.
361 *
362 * P_cached_orgn_code = Caches orgn_Code
363 * P_cached_gl_cost_mthd = Caches cost_mthd_code
364 * P_cached_cost_basis = Caches cost_basis
365 * P_cached_cost_whse_code = Caches cost_whse_code
366 *
367 * The variable P_no_of_rows is populated by get_cost routine when retrieve_ind is 2,3,4
368 * to notify how many array cell rows are populated with values.
369 *
370 * P_no_of rows = Caches the no of rows, retrieved and stored in array.
371 *
372 * RETURNS (choose one set)
373 * 1 success
374 * -1 No cost found.
375 * -2 Error in parameters passed
376 * -3 No GL Fiscal policy ( Unable to get the cost method from Fiscal plcy)
377 * HISTORY
378 ************************************************************************************** */
379
380
381 FUNCTION cmcommon_get_cost ( p_item_id IN NUMBER
382 ,p_whse_code IN VARCHAR2
383 ,p_orgn_code IN VARCHAR2
384 ,p_trans_date IN DATE
385 ,p_cost_mthd IN OUT NOCOPY VARCHAR2
386 ,p_cmpntcls_id IN OUT NOCOPY NUMBER
387 ,p_analysis_code IN OUT NOCOPY VARCHAR2
388 ,p_retrieve_ind IN NUMBER
389 ,x_total_cost OUT NOCOPY NUMBER
390 ,x_no_of_rows OUT NOCOPY NUMBER
391 ,p_lot_id IN NUMBER
392 ,p_trans_id IN NUMBER)
393 RETURN NUMBER IS
394
395 -- PK Eliminated obsolete code Get_Process_Item_Cost is proper 12.0 procedure.
396
397
398 BEGIN
399
400 RETURN(1);
401
402 END;
403
404 /*********************************************************
405 * Added by Anand Thiyagarajan ANTHIYAG 15-DEC-2004 Start *
406 *********************************************************/
407
408 /***********************************************************************************************
409 * FUNCTION *
410 * Get_Process_Item_Cost *
411 * *
412 * DESCRIPTION *
413 * This function is an overloaded function of get cost routine. This function *
414 * can be used to get costs for non lot cost method or lot cost method. *
415 * *
416 * For non lot cost method this function retrieves item cost, cost type *
417 * and fmeff_id from gl_item_cst and cmptcost_amt from gl_item_dtl *
418 * get_cost should return the cost of the item for the cost warehouse *
419 * if there is a cost warehouse associated with the given warehouse else *
420 * it should return the cost of the item for the given warehouse *
421 * *
422 * AUTHOR *
423 * Anand Thiyagarajan 01-JUN-2005 *
424 * *
425 * INPUT PARAMETERS *
426 * inventory_item_id = Item id *
427 * Organization_id = Organization *
428 * transaction_date = Date of item cost *
429 * cost_mthd = cost method used *
430 * cost_component_class_id = component class id *
431 * analysis_code = analysis code *
432 * detail_flag = 1 => retreive just acctg_cost *
433 * 2 => retreive acctg_cost all cmptcost_amts for itemcost *
434 * 3 => retreive acctg_cost cmptcost_amt for itemcost_id, *
435 * cost_cmpntcls_id cost_analysis Code *
436 * *
437 * OUTPUT PARAMETERS *
438 * total_cost = This out parameter should be reffered only in the *
439 * case when retrieve_ind value is passed as 1 *
440 * *
441 * RETURNS (choose one set) *
442 * 0 - success *
443 * 1 - could not get transfer_price *
444 * 2 - could not get item unit cost *
445 * 3 - uom conversion error *
446 * *
447 * HISTORY *
448 * Parag Kanetkar 28-Jan-2011 Bug 10370554 - added p_from_where, p_fiscal_year and *
449 * p_period for inventory valuation report. *
450 ***********************************************************************************************/
451 FUNCTION Get_Process_Item_Cost
452 (
453 p_api_version IN NUMBER
454 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
455 , x_return_status OUT NOCOPY VARCHAR2
456 , x_msg_count OUT NOCOPY NUMBER
457 , x_msg_data OUT NOCOPY VARCHAR2
458 , p_inventory_item_id IN NUMBER /* Item_Id */
459 , p_organization_id IN NUMBER /* Inventory Organization Id */
460 , p_transaction_date IN DATE /* Cost as on date */
461 , p_detail_flag IN NUMBER /* same as retrieve indicator: */ /* 1 = total cost, 2 = details; */ /* 3 = cost for a specific component class/analysis code, etc. */
462 , p_cost_method IN OUT NOCOPY VARCHAR2 /* OPM Cost Method */
463 , p_cost_component_class_id IN OUT NOCOPY NUMBER
464 , p_cost_analysis_code IN OUT NOCOPY VARCHAR2
465 , x_total_cost OUT NOCOPY NUMBER /* total cost */
466 , x_no_of_rows OUT NOCOPY NUMBER /* number of detail rows retrieved */
467 , p_from_where IN VARCHAR2 DEFAULT 'NOT_LOT_COSTS'
468 , p_fiscal_year IN NUMBER DEFAULT NULL
469 , p_period IN NUMBER DEFAULT NULL
470 )
471 RETURN NUMBER
472 IS
473
474 /******************
475 * Local Variables *
476 ******************/
477
478 l_api_name CONSTANT VARCHAR2(30) := 'Get_Process_Item_Cost' ;
479 l_api_version CONSTANT NUMBER := 1.0 ;
480
481 X_itemcost_id gl_item_cst.itemcost_id%TYPE;
482 X_cost_organization_id cm_whse_asc.cost_organization_id%TYPE;
483 X_no_recs NUMBER(10);
484 i INTEGER;
485 p_cost_type_id cm_mthd_mst.cost_type_id%type;
486 l_cost_type cm_mthd_mst.cost_type%type;
487
488 /********************************************
489 * Change this only when absolutely required *
490 ********************************************/
491
492 l_debug_flag NUMBER := 0;
493
494 /**********
495 * Cursors *
496 **********/
497
498 /*******************************************************************
499 * Retrieves the cost Organizations for the specified Organizations *
500 *******************************************************************/
501
502 CURSOR Cur_cmwhse_asc
503 (
504 V_ORGANIZATION_ID IN NUMBER,
505 v_trans_date IN DATE
506 )
507 IS
508 SELECT cost_ORGANIZATION_ID
509 FROM cm_whse_asc
510 WHERE ORGANIZATION_ID = V_ORGANIZATION_ID
511 AND eff_start_date <= v_trans_date
512 AND eff_end_date >= v_trans_date
513 AND delete_mark = 0;
514
515 /**********************************************************************
516 * Retrieves the fiscal policy for warehouse organization's company *
517 **********************************************************************/
518
519 CURSOR Cur_whse_orgn_plcy_mst
520 (
521 v_ORGANIZATION_ID IN NUMBER
522 )
523 IS
524 SELECT o.organization_id,
525 f.legal_entity_id,
526 f.cost_type_id,
527 f.cost_basis
528 FROM hr_organization_information o,
529 gmf_fiscal_policies f
530 WHERE o.organization_id = v_organization_id
531 AND o.org_information_context = 'Accounting Information'
532 AND o.org_information2 = f.LEGAL_ENTITY_ID
533 AND f.delete_mark = 0;
534
535 Cur_whse_orgn_plcy_temp Cur_whse_orgn_plcy_mst%ROWTYPE;
536
537 /***********************************************************************************
538 * Retrieves the previous calendar,period and end_date for the specified trans_date *
539 ***********************************************************************************/
540
541 CURSOR Cur_get_calprd
542 (
543 v_legal_entity_id IN NUMBER,
544 v_trans_date IN DATE,
545 v_cost_type_id IN NUMBER
546 )
547 IS
548 SELECT mst.calendar_code,
549 mst.period_code,
550 mst.end_date,
551 mst.period_id
552 FROM cm_cldr_mst_v mst
553 WHERE mst.delete_mark = 0
554 AND mst.end_date < v_trans_date
555 AND mst.cost_type_id = v_cost_type_id
556 AND mst.legal_entity_id = v_legal_entity_id
557 ORDER BY 3 desc;
558
559 Cur_get_calprd_tmp Cur_get_calprd%ROWTYPE;
560
561 /********************************************************************
562 * Retrieves cost for a cost warehouse, organization and cost method *
563 ********************************************************************/
564
565 CURSOR Cur_get_pr_cost
566 (
567 v_ORGANIZATION_ID IN NUMBER,
568 v_item_id IN NUMBER,
569 v_cost_type_id IN NUMBER,
570 v_period_id IN NUMBER
571 )
572 IS
573 SELECT acctg_cost,
574 cost_type,
575 fmeff_id,
576 itemcost_id
577 FROM gl_item_cst
578 WHERE organization_id = v_organization_id
579 AND inventory_item_id = v_item_id
580 AND cost_type_id = v_cost_type_id
581 AND period_id = v_period_id;
582
583 Cur_get_pr_cost_tmp Cur_get_pr_cost%ROWTYPE;
584
585 /**************************************************
586 * Retrieves the cost directly if cost basis is 1. *
587 **************************************************/
588
589 CURSOR Cur_get_cost_direct
590 (
591 v_organization_id IN NUMBER,
592 v_item_id IN NUMBER,
593 v_cost_type_id IN VARCHAR2,
594 v_trans_date IN DATE
595 )
596 IS
597 SELECT acctg_cost,
598 cost_type,
599 fmeff_id,
600 itemcost_id
601 FROM gl_item_cst
602 WHERE organization_id = v_organization_id
603 AND inventory_item_id = v_item_id
604 AND cost_type_id = v_cost_type_id
605 AND end_date >= v_trans_date
606 AND start_date <= v_trans_date;
607
608 Cur_get_cost_direct_tmp Cur_get_cost_direct%ROWTYPE;
609
610 /**********************************************************************************
611 * Retrieves the component cost for a pariticular cost component and analysis code *
612 **********************************************************************************/
613
614 CURSOR Cur_item_dtl
615 (
616 v_itemcost_id IN NUMBER,
617 v_cmpntcls_id IN NUMBER,
618 v_analysis_code IN VARCHAR2
619 )
620 IS
621 SELECT cmptcost_amt
622 FROM gl_item_dtl
623 WHERE itemcost_id = v_itemcost_id
624 AND cost_cmpntcls_id = v_cmpntcls_id
625 AND cost_analysis_code = v_analysis_code;
626
627 Cur_item_dtl_tmp Cur_item_dtl%ROWTYPE;
628
629 /**************************************************************************
630 * Retrieves all the cost components for a component class and itemcost_id *
631 * There could be many analysis codes for a given component class *
632 **************************************************************************/
633
634 CURSOR Cur_item_cost
635 (
636 v_itemcost_id IN NUMBER
637 )
638 IS
639 SELECT cmptcost_amt,
640 i.cost_cmpntcls_id,
641 i.cost_analysis_code
642 FROM gl_item_dtl i,
643 cm_cmpt_mst c
644 WHERE i.itemcost_id = v_itemcost_id
645 AND i.cost_cmpntcls_id = c.cost_cmpntcls_id
646 AND c.ppv_ind = 1;
647
648 /*****************************************************************************
649 * Retrieves Total cost of the specified cost componentcls id and itemcost_id *
650 *****************************************************************************/
651
652 CURSOR Cur_glitmdtl
653 (
654 v_itemcost_id IN NUMBER
655 )
656 IS
657 SELECT SUM(cmptcost_amt)
658 FROM gl_item_dtl i,
659 cm_cmpt_mst c
660 WHERE i.itemcost_id = v_itemcost_id
661 AND i.cost_cmpntcls_id = c.cost_cmpntcls_id
662 AND c.ppv_ind = 1;
663
664 /***************************************************
665 * Retrieves Cost Tpe FOR the Cost TYPE Id Selected *
666 ***************************************************/
667
668 CURSOR Cur_Get_mthd_type
669 (
670 v_cost_type_id IN NUMBER
671 )
672 IS
673 SELECT cost_type
674 FROM cm_mthd_mst
675 WHERE cost_type_id = v_cost_type_id;
676
677 -- PK Bug 10370554 INV Valuation report
678 -- ?? What about negative on hands in both the cursors ??
679
680 CURSOR cur_moqd (v_inventory_item_id NUMBER, v_organization_id NUMBER) IS
681 SELECT nvl(sum(moqd.primary_transaction_quantity), 0) qty, lot_number
682 FROM mtl_onhand_quantities_detail moqd, mtl_secondary_inventories msi
683 WHERE moqd.organization_id = v_organization_id
684 AND moqd.inventory_item_id = v_inventory_item_id
685 AND moqd.is_consigned = 2
686 AND msi.secondary_inventory_name = moqd.subinventory_code
687 AND moqd.organization_id = msi.organization_id
688 AND msi.asset_inventory = 1
689 GROUP BY inventory_item_id, lot_number;
690
691
692 CURSOR cur_gmf_perd_bal (v_inventory_item_id NUMBER, v_organization_id NUMBER, v_fiscal_year NUMBER, v_period NUMBER) IS
693 SELECT nvl(sum(gpb.primary_quantity), 0) qty, lot_number
694 FROM gmf_period_balances gpb, org_acct_periods oap, mtl_secondary_inventories msi
695 WHERE gpb.organization_id = v_organization_id
696 AND gpb.inventory_item_id = v_inventory_item_id
697 AND gpb.acct_period_id = oap.acct_period_id
698 AND oap.organization_id = gpb.organization_id
699 AND oap.period_year = v_fiscal_year
700 AND oap.period_num = v_period
701 AND gpb.subinventory_code (+)= msi.secondary_inventory_name
702 AND gpb.organization_id = msi.organization_id
703 AND msi.asset_inventory = 1
704 GROUP BY inventory_item_id, lot_number;
705
706 l_lot_cost NUMBER := 0;
707 l_lot_qty NUMBER := 0;
708 l_cum_qty NUMBER := 0;
709 l_cum_amt NUMBER := 0;
710 l_avg_cost NUMBER := 0;
711 l_cnt NUMBER := 0;
712 l_lot_cum NUMBER := 0;
713
714 -- PK Bug 10370554 INV Valuation report
715
716 BEGIN
717
718
719
720 l_debug_flag := G_DEBUG_LEVEL;
721
722 /*************************************************************
723 * Initialize message list if p_init_msg_list is set to TRUE. *
724 *************************************************************/
725 IF FND_API.to_Boolean( p_init_msg_list ) THEN
726 FND_MSG_PUB.initialize;
727 END IF;
728
729 /*************************************************
730 * Standard call to check for call compatibility. *
731 *************************************************/
732
733 IF NOT FND_API.Compatible_API_Call
734 (
735 l_api_version,
736 p_api_version,
737 l_api_name,
738 G_PKG_NAME
739 ) THEN
740 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
741 END IF;
742
743 /******************************************
744 * Initialize API return status to success *
745 ******************************************/
746 x_return_status := FND_API.G_RET_STS_SUCCESS;
747 x_msg_count := 0;
748 x_msg_data := NULL;
749
750 IF (l_debug_flag > 0) THEN
751 cmcommon_log( 'Input parameters: Inventory Item Id: ' || p_inventory_item_id || ' Org: ' || p_Organization_id ||' Cost Date: ' || to_char(p_transaction_date, 'yyyy-mm-dd hh24:mi:ss') );
752 cmcommon_log( 'Input cost type: ' || nvl(to_char(p_cost_type_id),'null-cost-type') );
753 END IF;
754
755 IF (p_inventory_item_id IS NULL OR p_organization_id IS NULL OR p_transaction_date IS NULL) THEN
756 IF( l_debug_flag > 0 ) THEN
757 cmcommon_log( 'Insufficient input parameters; exit status: -2' );
758 END IF;
759 RETURN(-2);
760 END IF;
761
762 /********************************************************************************
763 * The loop below is used to initialize the array if populated by previous call. *
764 ********************************************************************************/
765 IF (P_no_of_rows > 0) THEN
766 FOR i IN 1..P_no_of_rows LOOP
767 P_cmpntcost_amt(i) := NULL;
768 P_cost_cmpntcls_id(i) := NULL;
769 P_cost_analysis_code1(i) := NULL;
770 END LOOP;
771 END IF;
772
773 P_no_of_rows := 0;
774 P_acctg_cost := NULL;
775
776 /***********************************
777 * Get the Costing Whse Association *
778 ***********************************/
779 OPEN Cur_cmwhse_asc (
780 p_organization_id,
781 p_transaction_date
782 );
783 FETCH Cur_cmwhse_asc INTO X_cost_organization_id;
784
785 /*************************************************************************
786 * In Case there is no Organizations association for a given Organization *
787 * it should go ahead and return the cost for the given organization. *
788 *************************************************************************/
789 IF Cur_cmwhse_asc%NOTFOUND THEN
790 X_cost_organization_id := p_organization_id;
791 IF l_debug_flag > 0 THEN
792 cmcommon_log('Using Inv org ' || X_cost_organization_id || ' to retrieve cost' );
793 END IF;
794 ELSE
795 NULL;
796 IF l_debug_flag > 0 THEN
797 cmcommon_log('Cost organization retrieved '||X_cost_organization_id);
798 END IF;
799 END IF;
800 CLOSE Cur_cmwhse_asc;
801
802 /***********************************************************************
803 * Cache Cost_Type_Id, cost_basis and co_code for whse's orgn's company *
804 ***********************************************************************/
805 IF (X_cost_organization_id <> P_cached_cost_organization_id OR P_cached_cost_organization_id IS NULL) THEN
806 OPEN Cur_whse_orgn_plcy_mst (
807 X_cost_organization_id
808 );
809 FETCH Cur_whse_orgn_plcy_mst INTO Cur_whse_orgn_plcy_temp;
810 IF (Cur_whse_orgn_plcy_mst%FOUND) THEN
811 CLOSE Cur_whse_orgn_plcy_mst;
812 P_cached_cost_organization_id := Cur_whse_orgn_plcy_temp.organization_id;
813 P_cached_cost_type_id := Cur_whse_orgn_plcy_temp.cost_type_id;
814 P_cached_cost_basis := Cur_whse_orgn_plcy_temp.cost_basis;
815 P_cached_legal_entity_id := Cur_whse_orgn_plcy_temp.legal_entity_id;
816 ELSE
817 /*************************
818 * No fiscal plcy defined *
819 *************************/
820 CLOSE Cur_whse_orgn_plcy_mst;
821 IF l_debug_flag > 0 THEN
822 cmcommon_log( 'No fiscal policy defined for the Legal Entity of Org: ' || p_organization_id || '; exit status: -3 ' );
823 END IF;
824 RETURN(-3);
825 END IF;
826 END IF;
827
828 IF l_debug_flag > 0 THEN
829 cmcommon_log( ' Legal Entity: ' || P_cached_legal_entity_id || ' GL Cost Type: ' || P_cached_cost_type_id ||' Cost Basis: ' || P_cached_cost_basis );
830 END IF;
831
832 /**********************************************************
833 * If this variable hold NULL assign Cached variable to it *
834 **********************************************************/
835 IF (p_cost_method IS NULL) THEN
836 p_cost_type_id := P_cached_cost_type_id;
837 SELECT cost_mthd_code
838 INTO p_cost_method
839 FROM cm_mthd_mst
840 WHERE cost_type_id = P_cached_cost_type_id;
841 ELSE
842 SELECT cost_type_id
843 INTO p_cost_type_id
844 FROM cm_mthd_mst
845 WHERE cost_mthd_code = p_cost_method;
846 END IF;
847
848 IF l_debug_flag > 0 THEN
849 cmcommon_log( ' Cost_Type_Id: ' || P_cost_type_id||' Cost Method: '||p_cost_method);
850 END IF;
851
852 /************************************************************************
853 * Check if cost method is lot cost method if it is then return an error *
854 ************************************************************************/
855 IF p_cost_type_id IS NOT NULL THEN
856 OPEN Cur_Get_mthd_type(p_cost_type_id);
857 FETCH Cur_Get_mthd_type INTO l_cost_type;
858 CLOSE Cur_get_mthd_type;
859
860 IF l_debug_flag > 0 THEN
861 cmcommon_log( ' Cost Type: ' || l_cost_type);
862 END IF;
863
864 IF l_cost_type = 6 THEN
865
866 -- PK Bug 10370554 Code changes Begin
867
868 -- Add parameter validation code and return -4 for error.
869 IF (p_from_where = 'GPB' AND (p_fiscal_year IS NULL OR p_period IS NULL)) THEN
870 IF( l_debug_flag > 0 ) THEN
871 cmcommon_log( 'No fiscal year '||p_fiscal_year ||' OR Period '||p_period || ' Passed to API ' ||
872 'Report Submission Error exit status: -4 ' );
873 END IF;
874 RETURN(-4);
875 END IF;
876
877 IF (p_from_where = 'GPB') then -- add item lot costed check as well??
878
879 -- get the lots and lot qty from gmf_period_balances;
880
881 FOR cur_gmf_perd_bal_rec IN cur_gmf_perd_bal (p_inventory_item_id, p_organization_id, p_fiscal_year, p_period)
882 LOOP
883 l_cum_qty := l_cum_qty + cur_gmf_perd_bal_rec.qty;
884 l_cnt := l_cnt + 1;
885
886 x_return_status := Get_Process_Item_Cost ( p_api_version => p_api_version
887 ,p_init_msg_list => p_init_msg_list
888 ,x_return_status => x_return_status
889 ,x_msg_count => x_msg_count
890 ,x_msg_data => x_msg_data
891 ,p_inventory_item_id => p_inventory_item_id
892 ,p_organization_id => p_organization_id
893 ,p_transaction_date => p_transaction_date
894 ,p_detail_flag => p_detail_flag
895 ,p_cost_method => p_cost_method
896 ,p_cost_component_class_id => p_cost_component_class_id
897 ,p_cost_analysis_code => p_cost_analysis_code
898 ,X_total_cost => l_lot_cost
899 ,X_no_of_rows => x_no_of_rows
900 ,p_lot_number => cur_gmf_perd_bal_rec.lot_number
901 ,p_transaction_id => null
902 );
903
904
905 l_cum_amt := l_cum_amt + (NVL(l_lot_cost, 0) * cur_gmf_perd_bal_rec.qty);
906 l_lot_cum := l_lot_cum + NVL(l_lot_cost, 0);
907
908 IF( l_debug_flag > 0 ) THEN
909 cmcommon_log( 'Inv Item id '||p_inventory_item_id||' Lot Number '||cur_gmf_perd_bal_rec.lot_number||' Lot Cost '||
910 l_lot_cost||' qty '||cur_gmf_perd_bal_rec.qty||' Count '||l_cnt );
911 END IF;
912
913 END LOOP;
914 IF nvl(l_cum_qty, 0) <> 0 THEN
915 l_avg_cost := l_cum_amt / l_cum_qty;
916 x_total_cost := l_avg_cost;
917 IF( l_debug_flag > 0 ) THEN
918 cmcommon_log( '1 Inv Item id '||p_inventory_item_id ||' l_cum_qty '||l_cum_qty||' total_cost '||l_avg_cost);
919 END IF;
920 RETURN(x_return_status);
921 ELSIF (l_cnt > 0) THEN -- no balance exists for any lot
922 l_avg_cost := l_lot_cum / l_cnt;
923 x_total_cost := l_avg_cost;
924 IF( l_debug_flag > 0 ) THEN
925 cmcommon_log( '2 Inv Item id '||p_inventory_item_id ||' l_cnt '||l_cnt||' total_cost '||l_avg_cost);
926 END IF;
927 RETURN(x_return_status);
928 ELSE -- Loop never entered
929 l_avg_cost := 0;
930 x_total_cost := l_avg_cost;
931 IF( l_debug_flag > 0 ) THEN
932 cmcommon_log( '3 Inv Item id '||p_inventory_item_id || ' l_cnt '||l_cnt||' total_cost '||l_avg_cost);
933 END IF;
934 RETURN(x_return_status);
935 END IF;
936
937 ELSIF (p_from_where = 'MOQD') THEN -- add item lot costed check as well??
938
939 -- get the lots and lot qty from mtl_onhand_quantities_detail;
940
941 FOR cur_moqd_rec IN cur_moqd (p_inventory_item_id, p_organization_id)
942 LOOP
943 l_cum_qty := l_cum_qty + cur_moqd_rec.qty;
944 l_cnt := l_cnt + 1;
945
946 x_return_status := Get_Process_Item_Cost ( p_api_version => p_api_version
947 ,p_init_msg_list => p_init_msg_list
948 ,x_return_status => x_return_status
949 ,x_msg_count => x_msg_count
950 ,x_msg_data => x_msg_data
951 ,p_inventory_item_id => p_inventory_item_id
952 ,p_organization_id => p_organization_id
953 ,p_transaction_date => p_transaction_date
954 ,p_detail_flag => p_detail_flag
955 ,p_cost_method => p_cost_method
956 ,p_cost_component_class_id => p_cost_component_class_id
957 ,p_cost_analysis_code => p_cost_analysis_code
958 ,X_total_cost => l_lot_cost
959 ,X_no_of_rows => x_no_of_rows
960 ,p_lot_number => cur_moqd_rec.lot_number
961 ,p_transaction_id => null
962 );
963
964 l_cum_amt := l_cum_amt+ (NVL(l_lot_cost, 0) * cur_moqd_rec.qty);
965 l_lot_cum := l_lot_cum + NVL(l_lot_cost, 0);
966 IF( l_debug_flag > 0 ) THEN
967 cmcommon_log( 'Inv Item id '||p_inventory_item_id||' Lot Number '||cur_moqd_rec.lot_number||' Lot Cost '||
968 l_lot_cost||' qty '||cur_moqd_rec.qty||' Count '||l_cnt );
969 END IF;
970
971 END LOOP;
972 IF nvl(l_cum_qty, 0) <> 0 THEN
973 l_avg_cost := l_cum_amt / l_cum_qty;
974 x_total_cost := l_avg_cost;
975 IF( l_debug_flag > 0 ) THEN
976 cmcommon_log( '1 Inv Item id '||p_inventory_item_id ||' l_cum_qty '||l_cum_qty||' total_cost '||l_avg_cost);
977 END IF;
978 RETURN(x_return_status);
979 ELSIF (l_cnt > 0) THEN -- no balance exists for any lot
980 l_avg_cost := l_lot_cum / l_cnt;
981 x_total_cost := l_avg_cost;
982 IF( l_debug_flag > 0 ) THEN
983 cmcommon_log( '2 Inv Item id '||p_inventory_item_id ||' l_cnt '||l_cnt||' total_cost '||l_avg_cost);
984 END IF;
985 RETURN(x_return_status);
986 ELSE -- Loop never entered
987 l_avg_cost := 0;
988 x_total_cost := l_avg_cost;
989 IF( l_debug_flag > 0 ) THEN
990 cmcommon_log( '3 Inv Item id '||p_inventory_item_id || ' l_cnt '||l_cnt||' total_cost '||l_avg_cost);
991 END IF;
992 RETURN(x_return_status);
993 END IF;
994 ELSE -- p_from_where neither 'MOQD' or 'GPB'. Thus call did not come from INV valuation report. prior existing logic
995 x_return_status := Get_Process_Item_Cost (
996 p_api_version => p_api_version
997 ,p_init_msg_list => p_init_msg_list
998 ,x_return_status => x_return_status
999 ,x_msg_count => x_msg_count
1000 ,x_msg_data => x_msg_data
1001 ,p_inventory_item_id => p_inventory_item_id
1002 ,p_organization_id => p_organization_id
1003 ,p_transaction_date => p_transaction_date
1004 ,p_detail_flag => p_detail_flag
1005 ,p_cost_method => p_cost_method
1006 ,p_cost_component_class_id => p_cost_component_class_id
1007 ,p_cost_analysis_code => p_cost_analysis_code
1008 ,X_total_cost => x_total_cost
1009 ,X_no_of_rows => x_no_of_rows
1010 ,p_lot_number => null
1011 ,p_transaction_id => null
1012 );
1013 RETURN(x_return_status);
1014 END IF; -- p_from_where
1015 END IF; -- l_cost_type = 6
1016 END IF; -- p_cost_type_id IS NOT NULL
1017 P_acctg_cost := 0;
1018 P_fmeff_id := 0;
1019 P_cost_type := NULL;
1020
1021 /***************************************************************************************************
1022 * The variable below is initialized to 0 instead of null to avoid NULL comaprison IN where clause. *
1023 ***************************************************************************************************/
1024 x_itemcost_id := 0;
1025
1026 /****************************************************************************************
1027 * Try to get the cost using the item_id, whse_code and orgn_code from calling PROCEDURE *
1028 ****************************************************************************************/
1029 IF (P_cached_cost_basis = 0) THEN
1030 /****************************************************
1031 * get prior period cost. *
1032 * first get calendar, period codes for prior period *
1033 ****************************************************/
1034 OPEN Cur_get_calprd (
1035 P_cached_legal_entity_id,
1036 p_transaction_date,
1037 p_cost_type_id
1038 );
1039 FETCH Cur_get_calprd INTO Cur_get_calprd_tmp;
1040 IF Cur_get_calprd%NOTFOUND THEN
1041 CLOSE Cur_get_calprd;
1042 /***********************************************************************
1043 * No prior period and calendar found for specified p_transaction_date *
1044 ***********************************************************************/
1045 RETURN(-1);
1046 END IF;
1047 CLOSE Cur_get_calprd;
1048
1049 IF( l_debug_flag > 0 ) THEN
1050 cmcommon_log( ' Calendar Details:Calendar Code: ' || Cur_get_calprd_tmp.calendar_code ||' Period Code: '||Cur_get_calprd_tmp.period_code||' End Date: '||to_char(Cur_get_calprd_tmp.end_date)||' Period_id: '||Cur_get_calprd_tmp.period_id);
1051 END IF;
1052
1053 /****************************************************************************
1054 * Now select the cost based on the prior calendar and period selected above *
1055 ****************************************************************************/
1056 OPEN Cur_get_pr_cost (
1057 P_cached_cost_organization_id,
1058 p_inventory_item_id,
1059 p_cost_type_id,
1060 Cur_get_calprd_tmp.period_id
1061 );
1062 FETCH Cur_get_pr_cost INTO Cur_get_pr_cost_tmp;
1063 IF Cur_get_pr_cost%FOUND THEN
1064 x_no_recs := 1;
1065 P_acctg_cost := cur_get_pr_cost_tmp.acctg_cost;
1066 P_cost_type := cur_get_pr_cost_tmp.cost_type;
1067 P_fmeff_id := cur_get_pr_cost_tmp.fmeff_id;
1068 x_itemcost_id := cur_get_pr_cost_tmp.itemcost_id;
1069 ELSE
1070 x_no_recs := 0;
1071 END IF;
1072 CLOSE Cur_get_pr_cost;
1073 ELSE
1074
1075 IF l_debug_flag > 0 THEN
1076 cmcommon_log( ' Cost Organization: ' || P_cached_cost_organization_id || ' Inventory Item Id: ' || p_inventory_item_id ||' Cost Type Id: ' || p_cost_type_id ||' Transaction Date: '||p_transaction_date);
1077 END IF;
1078
1079 /******************************************
1080 * cost_basis = 1. get current period cost *
1081 ******************************************/
1082 OPEN Cur_get_cost_direct (
1083 P_cached_cost_organization_id,
1084 p_inventory_item_id,
1085 p_cost_type_id,
1086 p_transaction_date
1087 );
1088 FETCH Cur_get_cost_direct INTO Cur_get_cost_direct_tmp;
1089 IF Cur_get_cost_direct%FOUND THEN
1090 x_no_recs := 1;
1091 P_acctg_cost := Cur_get_cost_direct_tmp.acctg_cost;
1092 P_cost_type := Cur_get_cost_direct_tmp.cost_type;
1093 P_fmeff_id := Cur_get_cost_direct_tmp.fmeff_id;
1094 x_itemcost_id := Cur_get_cost_direct_tmp.itemcost_id;
1095 ELSE
1096 x_no_recs := 0;
1097 END IF;
1098 CLOSE Cur_get_cost_direct;
1099 END IF;
1100
1101 IF( l_debug_flag > 0 ) THEN
1102 cmcommon_log( ' Number of cost rows: ' || x_no_recs );
1103 END IF;
1104
1105 IF (x_no_recs = 0) THEN
1106 RETURN(-1);
1107 END IF;
1108
1109 IF( l_debug_flag > 0 ) THEN
1110 cmcommon_log( ' Acctg Cost: ' || P_acctg_cost || ' Cost Type: ' || P_cost_type ||' Fmeff_id: ' || P_fmeff_id || ' Itemcost_id: ' || x_itemcost_id );
1111 END IF;
1112
1113 /****************************************************************
1114 * The variable below is initialized to 0 in order to avoid NULL *
1115 * comparison in the where clause *
1116 ****************************************************************/
1117
1118 IF p_cost_component_class_id IS NULL THEN
1119 p_cost_component_class_id := 0;
1120 END IF;
1121
1122 /******************************************
1123 * select co cmptcost_amt from gl_item_dtl *
1124 ******************************************/
1125 x_no_recs := 0;
1126 P_no_of_rows := 0;
1127
1128 IF p_detail_flag = 1 THEN
1129 x_no_of_rows := 1; -- PK B 7213143
1130 x_total_cost := P_acctg_cost;
1131 x_no_recs := 1;
1132 ELSIF(p_detail_flag = 2 OR p_detail_flag = 3) THEN
1133 i:= 0;
1134 FOR cur_item_dtl_tmp IN cur_item_dtl (
1135 X_itemcost_id,
1136 p_cost_component_class_id,
1137 p_cost_analysis_code
1138 ) LOOP
1139 i:= i + 1;
1140 P_cmpntcost_amt(i) := cur_item_dtl_tmp.cmptcost_amt;
1141 x_no_recs := i;
1142 END LOOP;
1143 P_no_of_rows := i;
1144 x_no_of_rows := i;
1145
1146 /****************************************************************
1147 * Get item detail cmponent class ids/analysis codes *
1148 * This loop retrieves the multiple compnt_cost,cost_cmpntcls_id *
1149 * and analysis code and populates the array P_cmpntcost_amt, *
1150 * P_cost_cmpntcls_id,P_cost_analysis_code *
1151 ****************************************************************/
1152
1153 ELSIF p_detail_flag = 4 THEN
1154 i:= 0;
1155 FOR cur_item_cost_tmp IN cur_item_cost(X_itemcost_id) LOOP
1156 i:= i + 1;
1157 P_cmpntcost_amt(i) := cur_item_cost_tmp.cmptcost_amt;
1158 P_cost_cmpntcls_id(i) := cur_item_cost_tmp.cost_cmpntcls_id;
1159 P_cost_analysis_code1(i) := cur_item_cost_tmp.cost_analysis_code;
1160 x_no_recs := i;
1161 END LOOP;
1162 P_no_of_rows := i;
1163
1164 /*****************************************************************************
1165 * This out parameter is introduced in order to access the value of it *
1166 * in the forms. The reason behind is, one cannot access directly the package *
1167 * spec variables such as P_no_of_rows *
1168 *****************************************************************************/
1169 x_no_of_rows := i;
1170
1171 /****************************************************************
1172 * get total cost of item ppv/matl comp class ids/analysis codes *
1173 ****************************************************************/
1174 ELSIF p_detail_flag = 5 THEN
1175 OPEN Cur_glitmdtl(X_itemcost_id);
1176 FETCH Cur_glitmdtl INTO x_total_cost;
1177 IF Cur_glitmdtl%NOTFOUND THEN
1178 x_total_cost := 0;
1179 x_no_recs := 0;
1180 ELSE
1181 x_no_recs := 1;
1182 END IF;
1183 CLOSE Cur_glitmdtl;
1184 END IF;
1185 IF (x_no_recs = 0) THEN
1186 RETURN (-1);
1187 ELSE
1188 RETURN(1);
1189 END IF;
1190 RETURN(1);
1191
1192 /**************************************************************************
1193 * Standard call to get message count and if count is 1, get message info. *
1194 **************************************************************************/
1195 FND_MSG_PUB.Count_And_Get (
1196 p_count => x_msg_count,
1197 p_data => x_msg_data
1198 );
1199 EXCEPTION
1200 WHEN FND_API.G_EXC_ERROR THEN
1201 x_return_status := FND_API.G_RET_STS_ERROR ;
1202 FND_MSG_PUB.Count_And_Get (
1203 p_count => x_msg_count,
1204 p_data => x_msg_data
1205 );
1206 RETURN -1;
1207 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1208 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1209 FND_MSG_PUB.Count_And_Get (
1210 p_count => x_msg_count,
1211 p_data => x_msg_data
1212 );
1213 RETURN -1;
1214 WHEN OTHERS THEN
1215 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1216 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1217 FND_MSG_PUB.Add_Exc_Msg (
1218 G_PKG_NAME,
1219 l_api_name
1220 );
1221 END IF;
1222 FND_MSG_PUB.Count_And_Get (
1223 p_count => x_msg_count,
1224 p_data => x_msg_data
1225 );
1226 RETURN -1;
1227 END Get_Process_Item_Cost;
1228
1229 /***********************************************************************************************
1230 * FUNCTION *
1231 * Get_Process_Item_Cost *
1232 * *
1233 * DESCRIPTION *
1234 * This function is an overloaded function of get cost routine. This function *
1235 * can be used to get costs for non lot cost method or lot cost method. *
1236 * *
1237 * For non lot cost method this function retrieves item cost, cost type *
1238 * and fmeff_id from gl_item_cst and cmptcost_amt from gl_item_dtl *
1239 * get_cost should return the cost of the item for the cost warehouse *
1240 * if there is a cost warehouse associated with the given warehouse else *
1241 * it should return the cost of the item for the given warehouse *
1242 * *
1243 * AUTHOR *
1244 * Anand Thiyagarajan 01-JUN-2005 *
1245 * *
1246 * INPUT PARAMETERS *
1247 * inventory_item_id = Item id *
1248 * Organization_id = Organization *
1249 * transaction_date = Date of item cost *
1250 * cost_mthd = cost method used *
1251 * cost_component_class_id = component class id *
1252 * analysis_code = analysis code *
1253 * detail_flag = 1 => retreive just acctg_cost *
1254 * 2 => retreive acctg_cost all cmptcost_amts for itemcost *
1255 * 3 => retreive acctg_cost cmptcost_amt for itemcost_id, *
1256 * cost_cmpntcls_id cost_analysis Code *
1257 * lot_number = Lot Number *
1258 * Transaction_id = Transaction Identifier *
1259 * *
1260 * OUTPUT PARAMETERS *
1261 * total_cost = This out parameter should be reffered only in the *
1262 * case when retrieve_ind value is passed as 1 *
1263 * *
1264 * RETURNS (choose one set) *
1265 * 0 - success *
1266 * 1 - could not get transfer_price *
1267 * 2 - could not get item unit cost *
1268 * 3 - uom conversion error *
1269 * *
1270 * HISTORY
1271 * Sukarna Reddy Dt 26-Oct-2005 removed lot_id and replaced it with lot number in *
1272 * PARTITION BY CLAUSE. *
1273 * Added delete mark in the where clause while fetching default cost method *
1274 * Added RAISE exception to avoid circular calls to get_process_item_cost. if alternate *
1275 * cost method is marked for purge. *
1276 ***********************************************************************************************/
1277 FUNCTION Get_Process_Item_Cost
1278 (
1279 p_api_version IN NUMBER
1280 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1281 , x_return_status OUT NOCOPY VARCHAR2
1282 , x_msg_count OUT NOCOPY NUMBER
1283 , x_msg_data OUT NOCOPY VARCHAR2
1284 , p_inventory_item_id IN NUMBER /* Item_Id */
1285 , p_organization_id IN NUMBER /* Inventory Organization Id */
1286 , p_transaction_date IN DATE /* Cost as on date */
1287 , p_detail_flag IN NUMBER /* same as retrieve indicator: */ /* 1 = total cost, 2 = details; */ /* 3 = cost for a specific component class/analysis code, etc. */
1288 , p_cost_method IN OUT NOCOPY VARCHAR2 /* OPM Cost Method */
1289 , p_cost_component_class_id IN OUT NOCOPY NUMBER
1290 , p_cost_analysis_code IN OUT NOCOPY VARCHAR2
1291 , x_total_cost OUT NOCOPY NUMBER /* total cost */
1292 , x_no_of_rows OUT NOCOPY NUMBER /* number of detail rows retrieved */
1293 , p_lot_number IN VARCHAR2 /* Lot Number for the Item/Lot */
1294 , p_transaction_id IN NUMBER /* Transaction_id from MMT */
1295 )
1296 RETURN NUMBER
1297 IS
1298
1299 /******************
1300 * Local Variables *
1301 ******************/
1302 l_api_name CONSTANT VARCHAR2(30) := 'Get_Process_Item_Cost' ;
1303 l_api_version CONSTANT NUMBER := 1.0 ;
1304 l_cost_type_id NUMBER;
1305 l_status NUMBER;
1306 i NUMBER;
1307 l_header_id NUMBER;
1308 l_lot_actual_cost NUMBER;
1309 l_avg_cost NUMBER;
1310 l_cost_type_id1 cm_mthd_mst.cost_type_id%type;
1311 l_cost_type cm_mthd_mst.cost_type%type;
1312 l_default_lot_cost_type_id cm_mthd_mst.default_lot_cost_type_id%type;
1313 l_default_cost_mthd_code cm_mthd_mst.cost_mthd_code%TYPE;
1314
1315 ERROR_RETURN_STATUS EXCEPTION;
1316
1317 /**********
1318 * Cursors *
1319 **********/
1320
1321 CURSOR Cur_get_cmthd_type
1322 (
1323 v_cost_type_id IN NUMBER
1324 )
1325 IS
1326 SELECT
1327 cost_type,
1328 default_lot_cost_type_id
1329 FROM cm_mthd_mst
1330 WHERE cost_type_id = v_cost_type_id;
1331
1332 CURSOR Cur_get_cost_mthd ( v_organization_id IN NUMBER ) IS
1333 SELECT m.cost_type_id,
1334 m.default_lot_cost_type_id
1335 FROM cm_mthd_mst m,
1336 gmf_fiscal_policies plc,
1337 hr_organization_information o
1338 WHERE o.organization_id = v_organization_id
1339 AND o.org_information_context = 'Accounting Information'
1340 AND plc.legal_entity_id = o.org_information2
1341 AND plc.cost_type_id = m.cost_type_id
1342 AND m.delete_mark = 0
1343 AND plc.delete_mark = 0 ;
1344
1345 CURSOR Cur_get_cmpnts
1346 (
1347 v_header_id IN NUMBER,
1348 v_cost_cmpntcls_id IN NUMBER,
1349 v_cost_analysis_code IN VARCHAR2
1350 )
1351 IS
1352 SELECT cost_cmpntcls_id,
1353 cost_analysis_code,
1354 component_cost
1355 FROM gmf_lot_cost_details
1356 WHERE header_id = v_header_id
1357 AND cost_cmpntcls_id = NVL(v_cost_cmpntcls_id,cost_cmpntcls_id)
1358 AND cost_analysis_code = NVL(v_cost_analysis_code,cost_analysis_code);
1359
1360 CURSOR Cur_Get_total_cost
1361 (
1362 v_header_id IN NUMBER,
1363 v_lot_number IN VARCHAR2
1364 )
1365 IS
1366 SELECT sum(component_cost)
1367 FROM gmf_lot_cost_details d,
1368 gmf_lot_costs h
1369 WHERE h.header_id = v_header_id
1370 AND h.header_id = d.header_id
1371 AND h.lot_number = nvl(v_lot_number ,h.lot_number);
1372
1373 CURSOR Cur_Get_header
1374 (
1375 v_trans_id IN NUMBER,
1376 v_cost_type_id IN NUMBER
1377 )
1378 IS
1379 SELECT cost_header_id
1380 FROM gmf_material_lot_cost_txns
1381 WHERE transaction_id = v_trans_id
1382 AND cost_type_id = v_cost_type_id;
1383
1384 CURSOR Cur_Get_recent_hdr
1385 (
1386 v_item_id IN NUMBER,
1387 v_lot_number IN VARCHAR2,
1388 v_cost_type_id IN NUMBER,
1389 v_trans_date IN DATE,
1390 v_organization_id IN VARCHAR2
1391 )
1392 IS
1393 SELECT MAX(header_id)
1394 FROM gmf_lot_costs
1395 WHERE inventory_item_id = p_inventory_item_id
1396 AND cost_type_id = v_cost_type_id
1397 AND lot_number = v_lot_number
1398 AND cost_date <= v_trans_date
1399 AND organization_id = v_organization_id;
1400
1401 CURSOR Cur_get_cost
1402 (
1403 v_item_id IN NUMBER,
1404 v_organization_id IN NUMBER,
1405 v_cost_type_id IN NUMBER,
1406 v_trans_date IN DATE
1407 )
1408 IS
1409 SELECT SUM(onhand_qty*unit_cost)/SUM(onhand_qty)
1410 FROM (
1411 SELECT onhand_qty,
1412 unit_cost,
1413 RANK() OVER (
1414 PARTITION BY lot_number
1415 ORDER BY cost_date desc,
1416 header_id desc
1417 ) as rank
1418 FROM gmf_lot_costs
1419 WHERE cost_date <= v_trans_date
1420 AND inventory_item_id = v_item_id
1421 AND organization_id = v_organization_id
1422 AND cost_type_id = v_cost_type_id
1423 )
1424 WHERE rank = 1;
1425
1426 BEGIN
1427
1428
1429
1430 IF (p_cost_method IS NOT NULL) THEN
1431 SELECT cost_type_id
1432 INTO l_cost_type_id
1433 FROM cm_mthd_mst
1434 WHERE cost_mthd_code = p_cost_method
1435 AND delete_mark = 0;
1436 END IF;
1437
1438 /*************************************************************
1439 * Initialize message list if p_init_msg_list is set to TRUE. *
1440 *************************************************************/
1441 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1442 FND_MSG_PUB.initialize;
1443 END IF;
1444
1445 /*************************************************
1446 * Standard call to check for call compatibility. *
1447 *************************************************/
1448 IF NOT FND_API.Compatible_API_Call (
1449 l_api_version,
1450 p_api_version,
1451 l_api_name,
1452 G_PKG_NAME
1453 ) THEN
1454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1455 END IF;
1456
1457 /******************************************
1458 * Initialize API return status to success *
1459 ******************************************/
1460 x_return_status := FND_API.G_RET_STS_SUCCESS;
1461 x_msg_count := 0;
1462 x_msg_data := NULL;
1463
1464 IF (p_inventory_item_id IS NULL OR p_organization_id IS NULL OR p_transaction_date IS NULL) THEN
1465 /******************************************
1466 * No required input parameters specified. *
1467 ******************************************/
1468 cmcommon_log( 'Insufficient input parameters; exit status: -2' );
1469 RETURN(-2);
1470 END IF;
1471
1472 /*******************************************************
1473 * If cost method is not passed get gl cost method from *
1474 * fiscal policy *
1475 *******************************************************/
1476 IF (l_cost_type_id IS NULL) THEN
1477 OPEN Cur_get_cost_mthd (
1478 p_organization_id
1479 );
1480 FETCH Cur_get_cost_mthd INTO l_cost_type_id1,
1481 l_default_lot_cost_type_id;
1482 CLOSE Cur_get_cost_mthd;
1483 IF l_cost_type_id1 IS NULL THEN
1484 cmcommon_log( 'No Cost type defined in fiscal policy or specify a cost type; exit status: -2' );
1485 RETURN(-2);
1486 ELSE
1487 l_cost_type_id := l_cost_type_id1;
1488 END IF;
1489 END IF;
1490
1491 /***************************************************************************
1492 * if cost method is passed or retrieved from previous step check to see if *
1493 * its a lot cost method or not lot cost method *
1494 ***************************************************************************/
1495 IF l_cost_type_id IS NOT NULL THEN
1496 OPEN Cur_get_cmthd_type (
1497 l_cost_type_id
1498 );
1499 FETCH Cur_get_cmthd_type INTO l_cost_type,
1500 l_default_lot_cost_type_id;
1501 CLOSE Cur_get_cmthd_type;
1502 END IF;
1503
1504 /**************************************************************
1505 * If not lot cost method then call original get cost FUNCTION *
1506 **************************************************************/
1507
1508 IF l_cost_type <> 6 THEN
1509 l_status := Get_Process_Item_Cost (
1510 p_api_version => p_api_version,
1511 p_init_msg_list => p_init_msg_list,
1512 x_return_status => x_return_status,
1513 x_msg_count => x_msg_count,
1514 x_msg_data => x_msg_data,
1515 p_inventory_item_id => p_inventory_item_id,
1516 p_organization_id => p_organization_id,
1517 p_transaction_date => p_transaction_date,
1518 p_cost_method => p_cost_method,
1519 p_cost_component_class_id => p_cost_component_class_id,
1520 p_cost_analysis_code => p_cost_analysis_code,
1521 p_detail_flag => p_detail_flag,
1522 x_total_cost => x_total_cost,
1523 x_no_of_rows => x_no_of_rows
1524 );
1525 RETURN(l_status);
1526 ELSE
1527 /*******************************************************
1528 * Give trans id an highest priority compared to lot id *
1529 *******************************************************/
1530 IF nvl(p_transaction_id,0) > 0 THEN
1531 OPEN Cur_Get_header (
1532 p_transaction_id,
1533 l_cost_type_id
1534 );
1535 FETCH Cur_Get_header INTO l_header_id;
1536 CLOSE Cur_Get_header;
1537
1538 OPEN Cur_get_total_cost(l_header_id,p_lot_number);
1539 FETCH Cur_Get_total_cost INTO x_total_cost;
1540 CLOSE Cur_get_total_cost;
1541
1542 /************************************************************
1543 * if no trans id is passed then get cost header id based on *
1544 * recent transaction. *
1545 ************************************************************/
1546 ELSIF (nvl(p_transaction_id,0) = 0 OR p_transaction_id < 0) THEN
1547 IF (p_lot_number IS NOT NULL) THEN
1548 OPEN Cur_get_recent_hdr (
1549 p_inventory_item_id,
1550 p_lot_number,
1551 l_cost_type_id,
1552 p_transaction_date,
1553 p_organization_id
1554 );
1555 FETCH Cur_get_recent_hdr INTO l_header_id;
1556 CLOSE Cur_get_recent_hdr;
1557
1558 IF (nvl(l_header_id,0) > 0) THEN
1559 OPEN Cur_get_total_cost (
1560 l_header_id,
1561 p_lot_number
1562 );
1563 FETCH Cur_Get_total_cost INTO x_total_cost;
1564 CLOSE Cur_get_total_cost;
1565 END IF;
1566
1567 /*****************************************************************
1568 * If there is not lot or transaction ID then get cost *
1569 * by applying weighted average of all most recent lots belonging *
1570 * to an item,whse,cost method,cost date *
1571 *****************************************************************/
1572
1573 ELSIF (p_lot_number IS NULL) THEN
1574 OPEN Cur_get_cost (
1575 p_inventory_item_id,
1576 p_organization_id,
1577 l_cost_type_id,
1578 p_transaction_date
1579 );
1580 FETCH Cur_Get_cost INTO l_avg_cost;
1581 CLOSE Cur_Get_cost;
1582 END IF;
1583 IF l_avg_cost IS NOT NULL THEN
1584 x_no_of_rows := 1;
1585 -- Bug 13066587
1586 IF l_avg_cost < 0 THEN
1587 l_avg_cost := 0;
1588 END IF;
1589 -- End Bug 13066587
1590 x_total_cost := l_avg_cost;
1591 END IF;
1592 END IF;
1593
1594 -- PK code fix NVL B7213143
1595 /* IF (l_cost_type = 6 AND l_default_lot_cost_type_id IS NOT NULL AND NVL(x_total_cost, 0) = 0 AND NVL(l_avg_cost, 0) = 0) THEN Bug13890277 Commented */
1596 IF (l_cost_type = 6 AND l_default_lot_cost_type_id IS NOT NULL AND x_total_cost IS NULL AND l_avg_cost IS NULL) THEN
1597 BEGIN
1598 SELECT cost_mthd_code INTO l_default_cost_mthd_code
1599 FROM cm_mthd_mst
1600 WHERE cost_type_id = l_default_lot_cost_type_id AND
1601 delete_mark = 0;
1602 EXCEPTION
1603 WHEN OTHERS THEN
1604 l_default_cost_mthd_code := NULL;
1605 /* INVCONV sschinch */
1606 -- if we are here then we should return from here to avoid circular calls to get_process_item_cost.
1607 RAISE error_return_status;
1608 END;
1609 l_status := Get_Process_Item_Cost (
1610 p_api_version => p_api_version,
1611 p_init_msg_list => p_init_msg_list,
1612 x_return_status => x_return_status,
1613 x_msg_count => x_msg_count,
1614 x_msg_data => x_msg_data,
1615 p_inventory_item_id => p_inventory_item_id,
1616 p_organization_id => p_organization_id,
1617 p_transaction_date => p_transaction_date,
1618 p_cost_method => l_default_cost_mthd_code,
1619 p_cost_component_class_id => p_cost_component_class_id,
1620 p_cost_analysis_code => p_cost_analysis_code,
1621 p_detail_flag => p_detail_flag,
1622 x_total_cost => x_total_cost,
1623 x_no_of_rows => x_no_of_rows
1624 );
1625 IF l_status < 0 THEN
1626 RAISE error_return_status;
1627 END IF;
1628 END IF;
1629
1630 /***************************************************
1631 * Honor retrieve ind to get some additional info *
1632 * for lot cost method only when lot id or trans id *
1633 * is passed. *
1634 ***************************************************/
1635 IF (p_lot_number IS NOT NULL OR NVL(p_transaction_id,0) > 0) THEN
1636 IF (p_detail_flag = 2 OR p_detail_flag = 3) THEN
1637 i:= 0;
1638 IF (l_header_id > 0 AND p_cost_component_class_id > 0 AND p_cost_analysis_code IS NOT NULL) THEN
1639 FOR Cur_temp IN Cur_Get_cmpnts (
1640 l_header_id,
1641 p_cost_component_class_id,
1642 p_cost_analysis_code
1643 ) LOOP
1644 i:= i + 1;
1645 P_cmpntcost_amt(i) := cur_temp.component_cost;
1646 END LOOP;
1647 x_no_of_rows := i;
1648 p_no_of_rows := i;
1649 END IF;
1650 ELSIF (p_detail_flag = 4) THEN
1651 IF (l_header_id > 0) THEN
1652 i := 0;
1653 FOR Cur_temp IN Cur_Get_cmpnts (
1654 l_header_id,
1655 NULL,
1656 NULL
1657 ) LOOP
1658 i:= i + 1;
1659 P_cmpntcost_amt(i) := cur_temp.component_cost;
1660 P_cost_cmpntcls_id(i) := cur_temp.cost_cmpntcls_id;
1661 P_cost_analysis_code1(i) := cur_temp.cost_analysis_code;
1662 END LOOP;
1663 x_no_of_rows := i;
1664 p_no_of_rows := i;
1665 END IF;
1666 END IF;
1667 END IF;
1668
1669 IF (x_no_of_rows = 0) THEN
1670 RAISE ERROR_RETURN_STATUS;
1671 ELSE
1672 RETURN(1);
1673 END IF;
1674 END IF;
1675
1676 /**************************************************************************
1677 * Standard call to get message count and if count is 1, get message info. *
1678 **************************************************************************/
1679 FND_MSG_PUB.Count_And_Get (
1680 p_count => x_msg_count,
1681 p_data => x_msg_data
1682 );
1683
1684 EXCEPTION
1685 WHEN ERROR_RETURN_STATUS THEN
1686 x_no_of_rows := 0;
1687 x_total_cost := 0;
1688 p_no_of_rows := 0;
1689 RETURN(-1);
1690 WHEN FND_API.G_EXC_ERROR THEN
1691 x_return_status := FND_API.G_RET_STS_ERROR ;
1692 FND_MSG_PUB.Count_And_Get (
1693 p_count => x_msg_count,
1694 p_data => x_msg_data
1695 );
1696 RETURN -1;
1697 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1698 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1699 FND_MSG_PUB.Count_And_Get (
1700 p_count => x_msg_count,
1701 p_data => x_msg_data
1702 );
1703 RETURN -1;
1704 WHEN OTHERS THEN
1705 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1706 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1707 FND_MSG_PUB.Add_Exc_Msg (
1708 G_PKG_NAME,
1709 l_api_name
1710 );
1711 END IF;
1712 FND_MSG_PUB.Count_And_Get (
1713 p_count => x_msg_count,
1714 p_data => x_msg_data
1715 );
1716 RETURN -1;
1717 END Get_Process_Item_Cost;
1718
1719
1720 /***************************************************************************************
1721 * FUNCTION *
1722 * Is_Batch_Cost_Frozen *
1723 * *
1724 * DESCRIPTION *
1725 * This function is used to get the status whether the costs Fora particular batch *
1726 * has been closed ore not *
1727 * *
1728 * AUTHOR *
1729 * Anand Thiyagarajan 01-JUN-2005 *
1730 * *
1731 * INPUT PARAMETERS *
1732 * batch_id = Batch id *
1733 * *
1734 * OUTPUT PARAMETERS *
1735 * *
1736 * RETURNS *
1737 * TRUE => Period Costs are Frozen *
1738 * FALSE => Period Costs are Opn *
1739 * *
1740 * HISTORY *
1741 * *
1742 ***************************************************************************************/
1743 FUNCTION is_batch_cost_frozen
1744 (
1745 p_api_version IN NUMBER
1746 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1747 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1748 , x_return_status OUT NOCOPY VARCHAR2
1749 , x_msg_count OUT NOCOPY NUMBER
1750 , x_msg_data OUT NOCOPY VARCHAR2
1751 , p_batch_id IN gme_batch_header.batch_id%TYPE
1752 )
1753 RETURN BOOLEAN
1754 IS
1755
1756 /******************
1757 * Local Variables *
1758 ******************/
1759 l_api_name CONSTANT VARCHAR2(30) := 'Is_Batch_Cost_Frozen' ;
1760 l_api_version CONSTANT NUMBER := 1.0 ;
1761 l_cnt_frozen_matls NUMBER := 0;
1762
1763 /**********
1764 * Cursors *
1765 **********/
1766
1767 CURSOR c_get_period_info
1768 (
1769 l_batch_id IN gme_batch_header.batch_id%TYPE
1770 )
1771 IS
1772 SELECT count(1)
1773 FROM cm_cmpt_dtl cst,
1774 cm_acst_led aled,
1775 gme_material_details md,
1776 gme_batch_header bh
1777 WHERE bh.batch_id = l_batch_id
1778 AND bh.batch_id = md.batch_id
1779 AND md.material_detail_id = aled.transline_id
1780 AND aled.source_ind = 0
1781 AND aled.cmpntcost_id = cst.cmpntcost_id
1782 AND cst.rollover_ind = 1;
1783
1784 BEGIN
1785
1786
1787
1788 /*************************************************************
1789 * Initialize message list if p_init_msg_list is set to TRUE. *
1790 *************************************************************/
1791 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1792
1793 FND_MSG_PUB.initialize;
1794
1795 END IF;
1796
1797 /*************************************************
1798 * Standard call to check for call compatibility. *
1799 *************************************************/
1800 IF NOT FND_API.Compatible_API_Call
1801 (
1802 l_api_version,
1803 p_api_version,
1804 l_api_name,
1805 G_PKG_NAME
1806 ) THEN
1807
1808 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1809
1810 END IF;
1811
1812 /******************************************
1813 * Initialize API return status to success *
1814 ******************************************/
1815 x_return_status := FND_API.G_RET_STS_SUCCESS;
1816 x_msg_count := 0;
1817 x_msg_data := NULL;
1818
1819 OPEN c_get_period_info( p_batch_id);
1820 FETCH c_get_period_info INTO l_cnt_frozen_matls;
1821 IF c_get_period_info%NOTFOUND THEN
1822
1823 CLOSE c_get_period_info;
1824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1825
1826 END IF;
1827 CLOSE c_get_period_info;
1828
1829 /**************************************************************************
1830 * Standard call to get message count and if count is 1, get message info. *
1831 **************************************************************************/
1832 FND_MSG_PUB.Count_And_Get (
1833 p_count => x_msg_count,
1834 p_data => x_msg_data
1835 );
1836 /*************************************************************
1837 * Initialize message list if p_init_msg_list is set to TRUE. *
1838 *************************************************************/
1839 IF FND_API.to_Boolean( p_commit ) THEN
1840 COMMIT;
1841 END IF;
1842
1843 IF nvl(l_cnt_frozen_matls,0) > 0 THEN
1844 RETURN TRUE;
1845 ELSE
1846 RETURN FALSE;
1847 END IF;
1848
1849 EXCEPTION
1850 WHEN FND_API.G_EXC_ERROR THEN
1851 x_return_status := FND_API.G_RET_STS_ERROR ;
1852 FND_MSG_PUB.Count_And_Get (
1853 p_count => x_msg_count,
1854 p_data => x_msg_data
1855 );
1856 RETURN NULL;
1857 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1858 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1859 FND_MSG_PUB.Count_And_Get (
1860 p_count => x_msg_count,
1861 p_data => x_msg_data
1862 );
1863 RETURN NULL;
1864 WHEN OTHERS THEN
1865 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1866 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1867 FND_MSG_PUB.Add_Exc_Msg (
1868 G_PKG_NAME,
1869 l_api_name
1870 );
1871 END IF;
1872 FND_MSG_PUB.Count_And_Get (
1873 p_count => x_msg_count,
1874 p_data => x_msg_data
1875 );
1876 RETURN NULL;
1877 END Is_Batch_Cost_Frozen;
1878
1879 /************************************************************************************
1880 * FUNCTION
1881 * get_process_item_price
1882 *
1883 * DESCRIPTION
1884 * This is a wrapper function which calls get_process_item_unit_price
1885 * or transfer price API to get the cost.
1886 *
1887 * - For process/discrete transfers, get transfer_price.
1888 * - For process/process transfers, get item unit cost.
1889 *
1890 * This function is called from PO Req's POXRQLNS.pld
1891 *
1892 * AUTHOR
1893 * umoogala 10-Feb-2005 genesis
1894 * OPM INVCONV
1895 *
1896 * INPUT PARAMETERS
1897 * p_inventory_item_id IN NUMBER
1898 * p_src_organization_id IN NUMBER
1899 * p_src_process_enabled_flag IN VARCHAR2
1900 * p_dest_organization_id IN NUMBER
1901 * p_dest_process_enabled_flag IN VARCHAR2
1902 * p_trans_uom IN VARCHAR2
1903 * p_trans_date IN DATE
1904 *
1905 * x_unit_price OUT NOCOPY NUMBER
1906 * x_currency_code OUT NOCOPY VARCHAR2
1907 * x_incr_transfer_price OUT NOCOPY NUMBER
1908 * x_incr_currency_code OUT NOCOPY VARCHAR2
1909 *
1910 * OUTPUT PARAMETERS
1911 * Returns cost of an item.
1912 * Return Status
1913 * 0 - success
1914 * -1 - could not get transfer_price
1915 * -2 - could not get item unit cost
1916 * -3 - uom conversion error
1917 *
1918 * HISTORY
1919 *
1920 **************************************************************************************/
1921
1922 PROCEDURE get_process_item_price (
1923 p_inventory_item_id IN NUMBER
1924 , p_trans_qty IN NUMBER
1925 , p_trans_uom IN VARCHAR2
1926 , p_trans_date IN DATE
1927
1928 , p_src_organization_id IN NUMBER
1929 , p_src_process_enabled_flag IN VARCHAR2
1930
1931 , p_dest_organization_id IN NUMBER
1932 , p_dest_process_enabled_flag IN VARCHAR2
1933
1934 , p_source IN VARCHAR2
1935
1936 , x_unit_price OUT NOCOPY NUMBER
1937 , x_unit_price_priuom OUT NOCOPY NUMBER
1938 , x_currency_code OUT NOCOPY VARCHAR2
1939 , x_incr_transfer_price OUT NOCOPY NUMBER
1940 , x_incr_currency_code OUT NOCOPY VARCHAR2
1941 , x_return_status OUT NOCOPY NUMBER
1942 )
1943 IS
1944
1945 l_return_status VARCHAR2(1);
1946 x_msg_count NUMBER;
1947 x_msg_data VARCHAR2(2000);
1948 x_no_of_rows NUMBER;
1949
1950 l_transfer_type VARCHAR2(6);
1951 l_from_ou NUMBER;
1952 l_to_ou NUMBER;
1953
1954 l_conversion_rate NUMBER;
1955 l_primary_uom_code mtl_units_of_measure.uom_code%TYPE;
1956 l_trans_uom_code mtl_units_of_measure.uom_code%TYPE;
1957 l_primary_uom mtl_units_of_measure.unit_of_measure%TYPE;
1958
1959 BEGIN
1960
1961 l_return_status := FND_API.G_RET_STS_SUCCESS;
1962 l_transfer_type := 'INTORG';
1963
1964 IF (p_src_process_enabled_flag <> p_dest_process_enabled_flag)
1965 THEN
1966 -- process/discrete internal orders. Get transfer_price
1967 -- For INTCOM xfers, get transfer_price using INV API.
1968 -- For INTORG xfers, get transfer_price using Pricelist
1969
1970 /*
1971 IF l_from_ou <> l_to_ou
1972 THEN
1973 IF fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER') = 1
1974 THEN
1975 l_transfer_type := 'INTCOM';
1976 ELSE
1977 l_transfer_type := 'INTORD';
1978 END IF;
1979 ELSE
1980 l_transfer_type := 'INTORD';
1981 END IF;
1982 */
1983
1984 SELECT to_number(src.org_information2) src_ou, to_number(dest.org_information2) dest_ou
1985 INTO l_from_ou, l_to_ou
1986 FROM hr_organization_information src, hr_organization_information dest
1987 WHERE src.organization_id = p_src_organization_id
1988 AND src.org_information_context = 'Accounting Information'
1989 AND dest.organization_id = p_dest_organization_id
1990 AND dest.org_information_context = 'Accounting Information'
1991 ;
1992
1993 GMF_get_transfer_price_PUB.get_transfer_price (
1994 p_api_version => 1.0
1995 , p_init_msg_list => 'F'
1996
1997 , p_inventory_item_id => p_inventory_item_id
1998 , p_transaction_qty => p_trans_qty
1999 , p_transaction_uom => p_trans_uom
2000
2001 , p_transaction_id => NULL
2002 , p_global_procurement_flag => 'N'
2003 , p_drop_ship_flag => 'N'
2004
2005 , p_from_organization_id => p_src_organization_id
2006 , p_from_ou => l_from_ou
2007 , p_to_organization_id => p_dest_organization_id
2008 , p_to_ou => l_to_ou
2009
2010 , p_transfer_type => l_transfer_type
2011 , p_transfer_source => p_source
2012
2013 , x_return_status => l_return_status
2014 , x_msg_data => x_msg_data
2015 , x_msg_count => x_msg_count
2016
2017 , x_transfer_price => x_unit_price
2018 , x_transfer_price_priuom => x_unit_price_priuom
2019 , x_currency_code => x_currency_code
2020 , x_incr_transfer_price => x_incr_transfer_price /* not used */
2021 , x_incr_currency_code => x_incr_currency_code /* not used */
2022 );
2023
2024 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR
2025 x_unit_price IS NULL
2026 THEN
2027 x_unit_price := 0;
2028 x_return_status := -1; -- since pld cannot read db package variables
2029 END IF;
2030
2031 ELSIF p_src_process_enabled_flag = 'Y' AND p_dest_process_enabled_flag = 'Y'
2032 THEN
2033 -- process to process orders
2034
2035 GMF_cmcommon.get_process_item_unit_price (
2036 p_inventory_item_id => p_inventory_item_id
2037 , p_organization_id => p_src_organization_id
2038 , p_trans_date => p_trans_date
2039 , x_unit_price => x_unit_price
2040 , x_return_status => l_return_status
2041 );
2042
2043 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
2044 THEN
2045 x_unit_price := 0;
2046 x_return_status := -2; -- since pld cannot read db package variables
2047 END IF;
2048
2049
2050 IF l_return_status = FND_API.G_RET_STS_SUCCESS
2051 THEN
2052 ------------------------------------------------------------
2053 -- If the trans UOM and the item's primary UOM are same then
2054 -- the unit price is same else we need to calculate (derive)
2055 -- the unit price for the requisiton uom
2056 ------------------------------------------------------------
2057
2058 SELECT primary_unit_of_measure
2059 INTO l_primary_uom
2060 FROM mtl_system_items
2061 WHERE inventory_item_id = p_inventory_item_id
2062 AND organization_id = p_src_organization_id;
2063
2064
2065 IF l_primary_uom <> p_trans_uom
2066 THEN
2067
2068 SELECT uom_code
2069 INTO l_primary_uom_code
2070 FROM mtl_units_of_measure_vl
2071 WHERE unit_of_measure_tl = l_primary_uom;
2072
2073 SELECT uom_code
2074 INTO l_trans_uom_code
2075 FROM mtl_units_of_measure_vl
2076 WHERE unit_of_measure_tl = p_trans_uom;
2077
2078 inv_convert.inv_um_conversion(
2079 from_unit => l_primary_uom_code
2080 , to_unit => l_trans_uom_code
2081 , item_id => p_inventory_item_id
2082 , lot_number => NULL
2083 , organization_id => p_src_organization_id
2084 , uom_rate => l_conversion_rate
2085 );
2086
2087 IF l_conversion_rate = -99999
2088 THEN
2089 x_unit_price := 0;
2090 x_return_status := -3;
2091 ELSE
2092 x_unit_price := round((x_unit_price/l_conversion_rate), 5);
2093 END IF;
2094
2095 END IF; -- IF l_primary_uom <> p_trans_uom
2096
2097 END IF; -- IF x_return_status = FND_API.G_RET_STS_SUCCESS
2098
2099 END IF; -- ELSIF p_src_process_enabled_flag = 'Y' AND p_dest_process_enabled_flag = 'Y'
2100
2101 x_return_status := 0;
2102
2103 END get_process_item_price;
2104
2105
2106 /************************************************************************************
2107 * FUNCTION
2108 * get_process_item_unit_price
2109 *
2110 * DESCRIPTION
2111 * This is a wrapper function which calls Get_Process_Item_Cost to return
2112 * the cost. This function is called from PO Req's POXRQLNS.pld
2113 * -- PPV report(POXRCPPV.rdf)
2114 *
2115 * AUTHOR
2116 * umoogala 10-Feb-2005 genesis
2117 * OPM INVCONV
2118 *
2119 * INPUT PARAMETERS
2120 * p_inventory_item_id
2121 * p_organization_id
2122 * p_trans_uom
2123 * p_trans_date
2124 *
2125 * OUTPUT PARAMETERS
2126 * Returns cost of an item.
2127 * Status -1 or 0
2128 *
2129 * HISTORY
2130 *
2131 **************************************************************************************/
2132
2133 PROCEDURE get_process_item_unit_price (
2134 p_inventory_item_id IN NUMBER
2135 , p_organization_id IN NUMBER
2136 , p_trans_date IN DATE
2137 , x_unit_price OUT NOCOPY NUMBER
2138 , x_return_status OUT NOCOPY VARCHAR2
2139 )
2140 IS
2141
2142 l_return_status VARCHAR2(1);
2143 l_ret_val NUMBER;
2144 x_msg_count NUMBER;
2145 x_msg_data VARCHAR2(2000);
2146 x_no_of_rows NUMBER;
2147
2148 x_cost_method cm_mthd_mst.cost_mthd_code%TYPE;
2149 x_cost_component_class_id cm_cmpt_mst.cost_cmpntcls_id%TYPE;
2150 x_cost_analysis_code cm_alys_mst.cost_analysis_code%TYPE;
2151
2152 BEGIN
2153
2154 l_return_status := FND_API.G_RET_STS_SUCCESS;
2155
2156 l_ret_val := GMF_CMCOMMON.Get_Process_Item_Cost (
2157 1.0
2158 , fnd_api.g_true
2159 , l_return_status
2160 , x_msg_count
2161 , x_msg_data
2162 , p_inventory_item_id
2163 , p_organization_id
2164 , p_trans_date
2165 , 1 -- return unit_price
2166 , x_cost_method
2167 , x_cost_component_class_id
2168 , x_cost_analysis_code
2169 , x_unit_price
2170 , x_no_of_rows
2171 );
2172
2173 IF l_ret_val <> 1 OR l_return_status <> FND_API.G_RET_STS_SUCCESS
2174 THEN
2175 x_return_status := FND_API.G_RET_STS_ERROR;
2176 END IF;
2177
2178 x_return_status := FND_API.G_RET_STS_SUCCESS;
2179 x_unit_price := 1;
2180
2181 END get_process_item_unit_price;
2182
2183
2184 /*************************************************************************
2185 * FUNCTION *
2186 * process_item_unit_cost *
2187 * *
2188 * DESCRIPTION *
2189 * Unit cost of a process item (R12 version of unit cost function) *
2190 * *
2191 * HISTORY *
2192 * 11-Jul-2005 Rajesh Seshadri created stub version *
2193 * 23-AUG-2005 Anand Thiyagarajan Implementation Details *
2194 * *
2195 *************************************************************************/
2196 FUNCTION process_item_unit_cost
2197 (
2198 p_inventory_item_id IN NUMBER,
2199 p_organization_id IN NUMBER,
2200 p_transaction_date IN DATE
2201 ) RETURN NUMBER
2202 IS
2203
2204 /******************
2205 * Local Variables *
2206 ******************/
2207
2208 l_ret_val NUMBER;
2209 l_return_status VARCHAR2(5);
2210 l_msg_count NUMBER(10);
2211 l_msg_data VARCHAR2(2000);
2212 l_total_cost NUMBER;
2213 l_no_of_rows NUMBER(10);
2214 l_cost_method CM_MTHD_MST.COST_MTHD_CODE%TYPE;
2215 l_cost_component_class_id CM_CMPT_DTL.COST_CMPNTCLS_ID%TYPE;
2216 l_cost_analysis_code CM_CMPT_DTL.COST_ANALYSIS_CODE%TYPE;
2217 l_api_name CONSTANT VARCHAR2(30) := 'Process_Item_Unit_Cost' ;
2218 l_api_version CONSTANT NUMBER := 1.0 ;
2219
2220 /********************************************
2221 * Change this only when absolutely required *
2222 ********************************************/
2223
2224 l_debug_flag NUMBER := 0;
2225
2226 BEGIN
2227
2228 l_debug_flag := G_DEBUG_LEVEL;
2229
2230 /******************************************
2231 * Initialize API return status to success *
2232 ******************************************/
2233 l_return_status := FND_API.G_RET_STS_SUCCESS;
2234 l_msg_count := 0;
2235 l_msg_data := NULL;
2236
2237 IF (l_debug_flag > 0) THEN
2238 cmcommon_log( 'Input parameters: Inventory Item Id: ' || p_inventory_item_id || ' Org: ' || p_Organization_id ||' Cost Date: ' || to_char(p_transaction_date, 'yyyy-mm-dd hh24:mi:ss') );
2239 END IF;
2240
2241 IF (p_inventory_item_id IS NULL OR p_organization_id IS NULL OR p_transaction_date IS NULL) THEN
2242 IF( l_debug_flag > 0 ) THEN
2243 cmcommon_log( 'Insufficient input parameters; exit status: -2' );
2244 END IF;
2245 RETURN(0);
2246 END IF;
2247
2248 l_ret_val := Get_Process_Item_Cost
2249 (
2250 p_api_version => l_api_version
2251 , p_init_msg_list => FND_API.G_TRUE
2252 , x_return_status => l_return_status
2253 , x_msg_count => l_msg_count
2254 , x_msg_data => l_msg_data
2255 , p_inventory_item_id => p_inventory_item_id
2256 , p_organization_id => p_organization_id
2257 , p_transaction_date => p_transaction_date
2258 , p_detail_flag => 1
2259 , p_cost_method => l_cost_method
2260 , p_cost_component_class_id => l_cost_component_class_id
2261 , p_cost_analysis_code => l_cost_analysis_code
2262 , x_total_cost => l_total_cost
2263 , x_no_of_rows => l_no_of_rows
2264 );
2265 IF (l_debug_flag > 0) THEN
2266 cmcommon_log( 'Return Status => '|| l_return_status || ' Message => '|| l_msg_data ||' Cost => '||l_total_cost);
2267 END IF;
2268
2269 /**************************************************************************
2270 * Standard call to get message count and if count is 1, get message info. *
2271 **************************************************************************/
2272 FND_MSG_PUB.Count_And_Get (
2273 p_count => l_msg_count,
2274 p_data => l_msg_data
2275 );
2276
2277 RETURN (nvl(l_total_cost,0));
2278
2279 EXCEPTION
2280 WHEN FND_API.G_EXC_ERROR THEN
2281 l_return_status := FND_API.G_RET_STS_ERROR ;
2282 FND_MSG_PUB.Count_And_Get (
2283 p_count => l_msg_count,
2284 p_data => l_msg_data
2285 );
2286 RETURN 0;
2287 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2288 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2289 FND_MSG_PUB.Count_And_Get (
2290 p_count => l_msg_count,
2291 p_data => l_msg_data
2292 );
2293 RETURN 0;
2294 WHEN OTHERS THEN
2295 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2296 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2297 FND_MSG_PUB.Add_Exc_Msg (
2298 G_PKG_NAME,
2299 l_api_name
2300 );
2301 END IF;
2302 FND_MSG_PUB.Count_And_Get (
2303 p_count => l_msg_count,
2304 p_data => l_msg_data
2305 );
2306 RETURN 0;
2307
2308 END process_item_unit_cost;
2309
2310 /*************************************************************************
2311 * FUNCTION *
2312 * process_item_unit_cost *
2313 * *
2314 * DESCRIPTION *
2315 * Unit cost of a process item (R12 version of unit cost function) *
2316 * Overloaded for Lot Specific Costs *
2317 * *
2318 * HISTORY *
2319 * 11-Jul-2005 Rajesh Seshadri created stub version *
2320 * 23-AUG-2005 Anand Thiyagarajan Implementation Details *
2321 * *
2322 *************************************************************************/
2323 FUNCTION process_item_unit_cost
2324 (
2325 p_inventory_item_id IN NUMBER,
2326 p_organization_id IN NUMBER,
2327 p_transaction_date IN DATE,
2328 p_lot_number IN VARCHAR2,
2329 p_transaction_id IN NUMBER
2330 )
2331 RETURN NUMBER
2332 IS
2333 /******************
2334 * Local Variables *
2335 ******************/
2336
2337 l_ret_val NUMBER;
2338 l_return_status VARCHAR2(5);
2339 l_msg_count NUMBER(10);
2340 l_msg_data VARCHAR2(2000);
2341 l_total_cost NUMBER;
2342 l_no_of_rows NUMBER(10);
2343 l_cost_method CM_MTHD_MST.COST_MTHD_CODE%TYPE;
2344 l_cost_component_class_id CM_CMPT_DTL.COST_CMPNTCLS_ID%TYPE;
2345 l_cost_analysis_code CM_CMPT_DTL.COST_ANALYSIS_CODE%TYPE;
2346 l_api_name CONSTANT VARCHAR2(30) := 'Process_Item_Unit_Cost' ;
2347 l_api_version CONSTANT NUMBER := 1.0 ;
2348
2349 /********************************************
2350 * Change this only when absolutely required *
2351 ********************************************/
2352
2353 l_debug_flag NUMBER := 0;
2354
2355 BEGIN
2356
2357
2358 l_debug_flag := G_DEBUG_LEVEL;
2359
2360 /******************************************
2361 * Initialize API return status to success *
2362 ******************************************/
2363 l_return_status := FND_API.G_RET_STS_SUCCESS;
2364 l_msg_count := 0;
2365 l_msg_data := NULL;
2366
2367 IF (l_debug_flag > 0) THEN
2368 cmcommon_log( 'Input parameters: Inventory Item Id: ' || p_inventory_item_id || ' Org: ' || p_Organization_id);
2369 cmcommon_log( 'Input Parameters: Cost Date: ' || to_char(p_transaction_date, 'yyyy-mm-dd hh24:mi:ss'));
2370 cmcommon_log( 'Input Parameters: Lot Number: '|| p_lot_number || ' Transaction Id: '|| p_transaction_id);
2371 END IF;
2372
2373 IF (p_inventory_item_id IS NULL OR p_organization_id IS NULL OR p_transaction_date IS NULL OR p_organization_id IS NULL) THEN
2374 IF( l_debug_flag > 0 ) THEN
2375 cmcommon_log( 'Insufficient input parameters; exit status: -2' );
2376 END IF;
2377 RETURN(0);
2378 END IF;
2379
2380 l_ret_val := Get_Process_Item_Cost
2381 (
2382 p_api_version => l_api_version
2383 , p_init_msg_list => FND_API.G_TRUE
2384 , x_return_status => l_return_status
2385 , x_msg_count => l_msg_count
2386 , x_msg_data => l_msg_data
2387 , p_inventory_item_id => p_inventory_item_id
2388 , p_organization_id => p_organization_id
2389 , p_transaction_date => p_transaction_date
2390 , p_detail_flag => 1
2391 , p_cost_method => l_cost_method
2392 , p_cost_component_class_id => l_cost_component_class_id
2393 , p_cost_analysis_code => l_cost_analysis_code
2394 , x_total_cost => l_total_cost
2395 , x_no_of_rows => l_no_of_rows
2396 , p_lot_number => p_lot_number
2397 , p_transaction_id => p_transaction_id
2398 );
2399
2400 IF (l_debug_flag > 0) THEN
2401 cmcommon_log( 'Return Status => '|| l_return_status || ' Message => '|| l_msg_data ||' Cost => '||l_total_cost);
2402 END IF;
2403
2404 /**************************************************************************
2405 * Standard call to get message count and if count is 1, get message info. *
2406 **************************************************************************/
2407 FND_MSG_PUB.Count_And_Get (
2408 p_count => l_msg_count,
2409 p_data => l_msg_data
2410 );
2411
2412 RETURN (nvl(l_total_cost,0));
2413
2414 EXCEPTION
2415 WHEN FND_API.G_EXC_ERROR THEN
2416 l_return_status := FND_API.G_RET_STS_ERROR ;
2417 FND_MSG_PUB.Count_And_Get (
2418 p_count => l_msg_count,
2419 p_data => l_msg_data
2420 );
2421 RETURN 0;
2422 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2423 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2424 FND_MSG_PUB.Count_And_Get (
2425 p_count => l_msg_count,
2426 p_data => l_msg_data
2427 );
2428 RETURN 0;
2429 WHEN OTHERS THEN
2430 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2431 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2432 FND_MSG_PUB.Add_Exc_Msg (
2433 G_PKG_NAME,
2434 l_api_name
2435 );
2436 END IF;
2437 FND_MSG_PUB.Count_And_Get (
2438 p_count => l_msg_count,
2439 p_data => l_msg_data
2440 );
2441 RETURN 0;
2442
2443 END process_item_unit_cost;
2444
2445 /* ***********************************************************************************
2446 * FUNCTION
2447 * get_cmpt_cost
2448 * DESCRIPTION
2449 *
2450 * This function gets item unit cost from cm_cmpt_dtl, NOT from gl_item_cst/dtl.
2451 * This is being use in OPM Batch Cost Detail Report.
2452 *
2453 * AUTHOR
2454 * Anand Thiyagarajan 20-Feb-2007 Bug#5436964 Batches Across Periods FP
2455 *
2456 * INPUT PARAMETERS
2457 * inventory_item_id = Item id
2458 * Organization_id = Organization Id
2459 * transaction_date = Date of item cost
2460 * cost_type_id = Cost Type Used
2461 * Prior Ind = Prior period cost indicator
2462 *
2463 * OUTPUT PARAMETERS
2464 * None.
2465 *
2466 * RETURNS
2467 * total unit cost
2468 *
2469 * HISTORY
2470 * Anand Thiyagarajan 20-Feb-2007 Bug#5436964: Batches Across Periods FP
2471 ***************************************************************************************/
2472 FUNCTION get_cmpt_cost
2473 (
2474 p_inventory_item_id IN NUMBER,
2475 p_organization_id IN NUMBER,
2476 p_transaction_date IN DATE,
2477 p_cost_type_id IN NUMBER,
2478 p_prior_period_cost IN NUMBER
2479 )
2480 RETURN NUMBER
2481 IS
2482 CURSOR get_item_cost
2483 (
2484 v_inventory_item_id NUMBER,
2485 v_organization_id NUMBER,
2486 v_transaction_date DATE,
2487 v_cost_type_id NUMBER
2488 )
2489 IS
2490 SELECT nvl(sum(cst.cmpnt_cost), 0)
2491 FROM cm_cmpt_dtl cst,
2492 gmf_organization_definitions god,
2493 gmf_fiscal_policies f,
2494 gmf_period_statuses gps,
2495 (
2496 select nvl (
2497 (
2498 SELECT x.cost_organization_id
2499 FROM cm_whse_asc x
2500 WHERE x.organization_id = v_organization_id
2501 AND x.eff_start_date <= v_transaction_date
2502 AND x.eff_end_date >= v_transaction_date
2503 AND x.delete_mark = 0
2504 ), v_organization_id) organization_id
2505 from dual
2506 ) oasc
2507 WHERE god.organization_id = nvl(oasc.organization_id, v_organization_id)
2508 AND f.legal_entity_id = god.legal_entity_id
2509 AND f.delete_mark = 0
2510 AND gps.delete_mark = 0
2511 AND gps.legal_entity_id = f.legal_entity_id
2512 AND gps.cost_type_id = nvl(v_cost_type_id, f.cost_type_id)
2513 AND v_transaction_date BETWEEN gps.START_DATE AND gps.end_date
2514 AND cst.inventory_item_id = v_inventory_item_id
2515 AND cst.organization_id = NVL(oasc.organization_id, v_organization_id)
2516 AND cst.period_id = gps.period_id
2517 AND cst.cost_type_id = nvl(v_cost_type_id, f.cost_type_id);
2518
2519 CURSOR get_prior_period_end_date
2520 (
2521 v_organization_id NUMBER,
2522 v_transaction_date DATE,
2523 v_cost_type_id NUMBER
2524 )
2525 IS
2526 SELECT gps.end_date
2527 FROM gmf_organization_definitions god,
2528 gmf_fiscal_policies f,
2529 gmf_period_statuses gps,
2530 (
2531 select nvl (
2532 (
2533 SELECT x.cost_organization_id
2534 FROM cm_whse_asc x
2535 WHERE x.organization_id = v_organization_id
2536 AND x.eff_start_date <= v_transaction_date
2537 AND x.eff_end_date >= v_transaction_date
2538 AND x.delete_mark = 0
2539 ), v_organization_id) organization_id
2540 from dual
2541 ) oasc
2542 WHERE god.organization_id = nvl(oasc.organization_id, v_organization_id)
2543 AND f.legal_entity_id = god.legal_entity_id
2544 AND f.delete_mark = 0
2545 AND gps.delete_mark = 0
2546 AND gps.legal_entity_id = f.legal_entity_id
2547 AND gps.cost_type_id = nvl(v_cost_type_id, f.cost_type_id)
2548 AND gps.end_date < v_transaction_date
2549 ORDER BY gps.end_date desc;
2550
2551 l_cost NUMBER;
2552 l_transaction_date DATE;
2553 BEGIN
2554 IF p_prior_period_cost = 1 THEN
2555 OPEN get_prior_period_end_date(p_organization_id, p_transaction_date, p_cost_type_id);
2556 FETCH get_prior_period_end_date INTO l_transaction_date;
2557 CLOSE get_prior_period_end_date ;
2558 ELSE
2559 l_transaction_date := p_transaction_date;
2560 END IF;
2561
2562 OPEN get_item_cost(p_inventory_item_id, p_organization_id, l_transaction_date, p_cost_type_id);
2563 FETCH get_item_cost INTO l_cost;
2564 CLOSE get_item_cost;
2565
2566 RETURN l_cost;
2567
2568 END get_cmpt_cost;
2569
2570 /* ***********************************************************************************
2571 * FUNCTION
2572 * get_rsrc_cost
2573 * DESCRIPTION
2574 *
2575 * This function gets resource cost from cm_rsrc_dtl.
2576 * This is being use in OPM Batch Cost Detail Report.
2577 *
2578 * AUTHOR
2579 * Anand Thiyagarajan 20-Feb-2007 Bug#5436964 Batches Across Periods FP
2580 *
2581 * INPUT PARAMETERS
2582 * Resources = Resource
2583 * Organization_id = Organization Id
2584 * transaction_date = Date of item cost
2585 * cost_type_id = Cost Type Used
2586 * Prior Ind = Prior period cost indicator
2587 *
2588 * OUTPUT PARAMETERS
2589 * None.
2590 *
2591 * RETURNS
2592 * Resource cost
2593 *
2594 * HISTORY
2595 * Anand Thiyagarajan 20-Feb-2007 Bug#5436964: Batches Across Periods FP
2596 ***************************************************************************************/
2597 FUNCTION get_rsrc_cost
2598 (
2599 p_resources IN VARCHAR2,
2600 p_organization_id IN NUMBER,
2601 p_transaction_date IN DATE,
2602 p_cost_type_id IN NUMBER,
2603 p_prior_period_cost IN NUMBER
2604 )
2605 RETURN NUMBER
2606 IS
2607 CURSOR get_prior_period_end_date
2608 (
2609 v_organization_id NUMBER,
2610 v_transaction_date DATE,
2611 v_cost_type_id NUMBER
2612 )
2613 IS
2614 SELECT gps.end_date
2615 FROM gmf_organization_definitions god,
2616 gmf_fiscal_policies f,
2617 gmf_period_statuses gps
2618 WHERE god.organization_id = v_organization_id
2619 AND f.legal_entity_id = god.legal_entity_id
2620 AND f.delete_mark = 0
2621 AND gps.delete_mark = 0
2622 AND gps.legal_entity_id = f.legal_entity_id
2623 AND gps.cost_type_id = nvl(v_cost_type_id, f.cost_type_id)
2624 AND gps.end_date < v_transaction_date
2625 ORDER BY gps.end_date desc;
2626
2627 CURSOR get_rsrc_cost
2628 (
2629 v_resources VARCHAR2,
2630 v_organization_id NUMBER,
2631 v_transaction_date DATE,
2632 v_cost_type_id NUMBER
2633 )
2634 IS
2635 SELECT nvl(sum(cst.nominal_cost), 0)
2636 FROM cm_rsrc_dtl cst,
2637 gmf_organization_definitions god,
2638 gmf_fiscal_policies f,
2639 gmf_period_statuses gps
2640 WHERE god.organization_id = v_organization_id
2641 AND f.legal_entity_id = god.legal_entity_id
2642 AND f.delete_mark = 0
2643 AND gps.delete_mark = 0
2644 AND gps.legal_entity_id = f.legal_entity_id
2645 AND gps.cost_type_id = nvl(v_cost_type_id, f.cost_type_id)
2646 AND v_transaction_date BETWEEN gps.START_DATE AND gps.end_date
2647 AND cst.resources = v_resources
2648 AND (cst.organization_id = v_organization_id OR cst.organization_id IS NULL)
2649 AND cst.period_id = gps.period_id
2650 AND cst.cost_type_id = nvl(v_cost_type_id, f.cost_type_id);
2651
2652 l_cost NUMBER;
2653 l_transaction_date DATE;
2654
2655 BEGIN
2656 IF p_prior_period_cost = 1 THEN
2657 OPEN get_prior_period_end_date(p_organization_id, p_transaction_date, p_cost_type_id);
2658 FETCH get_prior_period_end_date INTO l_transaction_date;
2659 CLOSE get_prior_period_end_date ;
2660 ELSE
2661 l_transaction_date := p_transaction_date;
2662 END IF;
2663
2664 OPEN get_rsrc_cost(p_resources, p_organization_id, l_transaction_date, p_cost_type_id);
2665 FETCH get_rsrc_cost INTO l_cost;
2666 CLOSE get_rsrc_cost;
2667
2668 RETURN l_cost;
2669
2670 END get_rsrc_cost;
2671
2672 END GMF_CMCOMMON ;
[Home] [Help]