1 PACKAGE BODY GMF_CMCOMMON AS
2 /* $Header: gmfcmcob.pls 120.15.12010000.2 2008/10/21 20:52:33 rpatangy 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 * *
449 ***********************************************************************************************/
450 FUNCTION Get_Process_Item_Cost
451 (
452 p_api_version IN NUMBER
453 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
454 , x_return_status OUT NOCOPY VARCHAR2
455 , x_msg_count OUT NOCOPY NUMBER
456 , x_msg_data OUT NOCOPY VARCHAR2
457 , p_inventory_item_id IN NUMBER /* Item_Id */
458 , p_organization_id IN NUMBER /* Inventory Organization Id */
459 , p_transaction_date IN DATE /* Cost as on date */
460 , p_detail_flag IN NUMBER /* same as retrieve indicator: */ /* 1 = total cost, 2 = details; */ /* 3 = cost for a specific component class/analysis code, etc. */
461 , p_cost_method IN OUT NOCOPY VARCHAR2 /* OPM Cost Method */
462 , p_cost_component_class_id IN OUT NOCOPY NUMBER
463 , p_cost_analysis_code IN OUT NOCOPY VARCHAR2
464 , x_total_cost OUT NOCOPY NUMBER /* total cost */
465 , x_no_of_rows OUT NOCOPY NUMBER /* number of detail rows retrieved */
466 )
467 RETURN NUMBER
468 IS
469
470 /******************
471 * Local Variables *
472 ******************/
473
474 l_api_name CONSTANT VARCHAR2(30) := 'Get_Process_Item_Cost' ;
475 l_api_version CONSTANT NUMBER := 1.0 ;
476
477 X_itemcost_id gl_item_cst.itemcost_id%TYPE;
478 X_cost_organization_id cm_whse_asc.cost_organization_id%TYPE;
479 X_no_recs NUMBER(10);
480 i INTEGER;
481 p_cost_type_id cm_mthd_mst.cost_type_id%type;
482 l_cost_type cm_mthd_mst.cost_type%type;
483
484 /********************************************
485 * Change this only when absolutely required *
486 ********************************************/
487
488 l_debug_flag NUMBER := 0;
489
490 /**********
491 * Cursors *
492 **********/
493
494 /*******************************************************************
495 * Retrieves the cost Organizations for the specified Organizations *
496 *******************************************************************/
497
498 CURSOR Cur_cmwhse_asc
499 (
500 V_ORGANIZATION_ID IN NUMBER,
501 v_trans_date IN DATE
502 )
503 IS
504 SELECT cost_ORGANIZATION_ID
505 FROM cm_whse_asc
506 WHERE ORGANIZATION_ID = V_ORGANIZATION_ID
507 AND eff_start_date <= v_trans_date
508 AND eff_end_date >= v_trans_date
509 AND delete_mark = 0;
510
511 /**********************************************************************
512 * Retrieves the fiscal policy for warehouse organization's company *
513 **********************************************************************/
514
515 CURSOR Cur_whse_orgn_plcy_mst
516 (
517 v_ORGANIZATION_ID IN NUMBER
518 )
519 IS
520 SELECT o.organization_id,
521 f.legal_entity_id,
522 f.cost_type_id,
523 f.cost_basis
524 FROM hr_organization_information o,
525 gmf_fiscal_policies f
526 WHERE o.organization_id = v_organization_id
527 AND o.org_information_context = 'Accounting Information'
528 AND o.org_information2 = f.LEGAL_ENTITY_ID
529 AND f.delete_mark = 0;
530
531 Cur_whse_orgn_plcy_temp Cur_whse_orgn_plcy_mst%ROWTYPE;
532
533 /***********************************************************************************
534 * Retrieves the previous calendar,period and end_date for the specified trans_date *
535 ***********************************************************************************/
536
537 CURSOR Cur_get_calprd
538 (
539 v_legal_entity_id IN NUMBER,
540 v_trans_date IN DATE,
541 v_cost_type_id IN NUMBER
542 )
543 IS
544 SELECT mst.calendar_code,
545 mst.period_code,
546 mst.end_date,
547 mst.period_id
548 FROM cm_cldr_mst_v mst
549 WHERE mst.delete_mark = 0
550 AND mst.end_date < v_trans_date
551 AND mst.cost_type_id = v_cost_type_id
552 AND mst.legal_entity_id = v_legal_entity_id
553 ORDER BY 3 desc;
554
555 Cur_get_calprd_tmp Cur_get_calprd%ROWTYPE;
556
557 /********************************************************************
558 * Retrieves cost for a cost warehouse, organization and cost method *
559 ********************************************************************/
560
561 CURSOR Cur_get_pr_cost
562 (
563 v_ORGANIZATION_ID IN NUMBER,
564 v_item_id IN NUMBER,
565 v_cost_type_id IN NUMBER,
566 v_period_id IN NUMBER
567 )
568 IS
569 SELECT acctg_cost,
570 cost_type,
571 fmeff_id,
572 itemcost_id
573 FROM gl_item_cst
574 WHERE organization_id = v_organization_id
575 AND inventory_item_id = v_item_id
576 AND cost_type_id = v_cost_type_id
577 AND period_id = v_period_id;
578
579 Cur_get_pr_cost_tmp Cur_get_pr_cost%ROWTYPE;
580
581 /**************************************************
582 * Retrieves the cost directly if cost basis is 1. *
583 **************************************************/
584
585 CURSOR Cur_get_cost_direct
586 (
587 v_organization_id IN NUMBER,
588 v_item_id IN NUMBER,
589 v_cost_type_id IN VARCHAR2,
590 v_trans_date IN DATE
591 )
592 IS
593 SELECT acctg_cost,
594 cost_type,
595 fmeff_id,
596 itemcost_id
597 FROM gl_item_cst
598 WHERE organization_id = v_organization_id
599 AND inventory_item_id = v_item_id
600 AND cost_type_id = v_cost_type_id
601 AND end_date >= v_trans_date
602 AND start_date <= v_trans_date;
603
604 Cur_get_cost_direct_tmp Cur_get_cost_direct%ROWTYPE;
605
606 /**********************************************************************************
607 * Retrieves the component cost for a pariticular cost component and analysis code *
608 **********************************************************************************/
609
610 CURSOR Cur_item_dtl
611 (
612 v_itemcost_id IN NUMBER,
613 v_cmpntcls_id IN NUMBER,
614 v_analysis_code IN VARCHAR2
615 )
616 IS
617 SELECT cmptcost_amt
618 FROM gl_item_dtl
619 WHERE itemcost_id = v_itemcost_id
620 AND cost_cmpntcls_id = v_cmpntcls_id
621 AND cost_analysis_code = v_analysis_code;
622
623 Cur_item_dtl_tmp Cur_item_dtl%ROWTYPE;
624
625 /**************************************************************************
626 * Retrieves all the cost components for a component class and itemcost_id *
627 * There could be many analysis codes for a given component class *
628 **************************************************************************/
629
630 CURSOR Cur_item_cost
631 (
632 v_itemcost_id IN NUMBER
633 )
634 IS
635 SELECT cmptcost_amt,
636 i.cost_cmpntcls_id,
637 i.cost_analysis_code
638 FROM gl_item_dtl i,
639 cm_cmpt_mst c
640 WHERE i.itemcost_id = v_itemcost_id
641 AND i.cost_cmpntcls_id = c.cost_cmpntcls_id
642 AND c.ppv_ind = 1;
643
644 /*****************************************************************************
645 * Retrieves Total cost of the specified cost componentcls id and itemcost_id *
646 *****************************************************************************/
647
648 CURSOR Cur_glitmdtl
649 (
650 v_itemcost_id IN NUMBER
651 )
652 IS
653 SELECT SUM(cmptcost_amt)
654 FROM gl_item_dtl i,
655 cm_cmpt_mst c
656 WHERE i.itemcost_id = v_itemcost_id
657 AND i.cost_cmpntcls_id = c.cost_cmpntcls_id
658 AND c.ppv_ind = 1;
659
660 /***************************************************
661 * Retrieves Cost Tpe FOR the Cost TYPE Id Selected *
662 ***************************************************/
663
664 CURSOR Cur_Get_mthd_type
665 (
666 v_cost_type_id IN NUMBER
667 )
668 IS
669 SELECT cost_type
670 FROM cm_mthd_mst
671 WHERE cost_type_id = v_cost_type_id;
672
673
674
675 BEGIN
676
677
678
679 l_debug_flag := G_DEBUG_LEVEL;
680
681 /*************************************************************
682 * Initialize message list if p_init_msg_list is set to TRUE. *
683 *************************************************************/
684 IF FND_API.to_Boolean( p_init_msg_list ) THEN
685 FND_MSG_PUB.initialize;
686 END IF;
687
688 /*************************************************
689 * Standard call to check for call compatibility. *
690 *************************************************/
691
692 IF NOT FND_API.Compatible_API_Call
693 (
694 l_api_version,
695 p_api_version,
696 l_api_name,
697 G_PKG_NAME
698 ) THEN
699 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
700 END IF;
701
702 /******************************************
703 * Initialize API return status to success *
704 ******************************************/
705 x_return_status := FND_API.G_RET_STS_SUCCESS;
706 x_msg_count := 0;
707 x_msg_data := NULL;
708
709 IF (l_debug_flag > 0) THEN
710 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') );
711 cmcommon_log( 'Input cost type: ' || nvl(to_char(p_cost_type_id),'null-cost-type') );
712 END IF;
713
714 IF (p_inventory_item_id IS NULL OR p_organization_id IS NULL OR p_transaction_date IS NULL) THEN
715 IF( l_debug_flag > 0 ) THEN
716 cmcommon_log( 'Insufficient input parameters; exit status: -2' );
717 END IF;
718 RETURN(-2);
719 END IF;
720
721 /********************************************************************************
722 * The loop below is used to initialize the array if populated by previous call. *
723 ********************************************************************************/
724 IF (P_no_of_rows > 0) THEN
725 FOR i IN 1..P_no_of_rows LOOP
726 P_cmpntcost_amt(i) := NULL;
727 P_cost_cmpntcls_id(i) := NULL;
728 P_cost_analysis_code1(i) := NULL;
729 END LOOP;
730 END IF;
731
732 P_no_of_rows := 0;
733 P_acctg_cost := NULL;
734
735 /***********************************
736 * Get the Costing Whse Association *
737 ***********************************/
738 OPEN Cur_cmwhse_asc (
739 p_organization_id,
740 p_transaction_date
741 );
742 FETCH Cur_cmwhse_asc INTO X_cost_organization_id;
743
744 /*************************************************************************
745 * In Case there is no Organizations association for a given Organization *
746 * it should go ahead and return the cost for the given organization. *
747 *************************************************************************/
748 IF Cur_cmwhse_asc%NOTFOUND THEN
749 X_cost_organization_id := p_organization_id;
750 IF l_debug_flag > 0 THEN
751 cmcommon_log('Using Inv org ' || X_cost_organization_id || ' to retrieve cost' );
752 END IF;
753 ELSE
754 NULL;
755 IF l_debug_flag > 0 THEN
756 cmcommon_log('Cost organization retrieved '||X_cost_organization_id);
757 END IF;
758 END IF;
759 CLOSE Cur_cmwhse_asc;
760
761 /***********************************************************************
762 * Cache Cost_Type_Id, cost_basis and co_code for whse's orgn's company *
763 ***********************************************************************/
764 IF (X_cost_organization_id <> P_cached_cost_organization_id OR P_cached_cost_organization_id IS NULL) THEN
765 OPEN Cur_whse_orgn_plcy_mst (
766 X_cost_organization_id
767 );
768 FETCH Cur_whse_orgn_plcy_mst INTO Cur_whse_orgn_plcy_temp;
769 IF (Cur_whse_orgn_plcy_mst%FOUND) THEN
770 CLOSE Cur_whse_orgn_plcy_mst;
771 P_cached_cost_organization_id := Cur_whse_orgn_plcy_temp.organization_id;
772 P_cached_cost_type_id := Cur_whse_orgn_plcy_temp.cost_type_id;
773 P_cached_cost_basis := Cur_whse_orgn_plcy_temp.cost_basis;
774 P_cached_legal_entity_id := Cur_whse_orgn_plcy_temp.legal_entity_id;
775 ELSE
776 /*************************
777 * No fiscal plcy defined *
778 *************************/
779 CLOSE Cur_whse_orgn_plcy_mst;
780 IF l_debug_flag > 0 THEN
781 cmcommon_log( 'No fiscal policy defined for the Legal Entity of Org: ' || p_organization_id || '; exit status: -3 ' );
782 END IF;
783 RETURN(-3);
784 END IF;
785 END IF;
786
787 IF l_debug_flag > 0 THEN
788 cmcommon_log( ' Legal Entity: ' || P_cached_legal_entity_id || ' GL Cost Type: ' || P_cached_cost_type_id ||' Cost Basis: ' || P_cached_cost_basis );
789 END IF;
790
791 /**********************************************************
792 * If this variable hold NULL assign Cached variable to it *
793 **********************************************************/
794 IF (p_cost_method IS NULL) THEN
795 p_cost_type_id := P_cached_cost_type_id;
796 SELECT cost_mthd_code
797 INTO p_cost_method
798 FROM cm_mthd_mst
799 WHERE cost_type_id = P_cached_cost_type_id;
800 ELSE
801 SELECT cost_type_id
802 INTO p_cost_type_id
803 FROM cm_mthd_mst
804 WHERE cost_mthd_code = p_cost_method;
805 END IF;
806
807 IF l_debug_flag > 0 THEN
808 cmcommon_log( ' Cost_Type_Id: ' || P_cost_type_id||' Cost Method: '||p_cost_method);
809 END IF;
810
811 /************************************************************************
812 * Check if cost method is lot cost method if it is then return an error *
813 ************************************************************************/
814 IF p_cost_type_id IS NOT NULL THEN
815 OPEN Cur_Get_mthd_type(p_cost_type_id);
816 FETCH Cur_Get_mthd_type INTO l_cost_type;
817 CLOSE Cur_get_mthd_type;
818
819 IF l_debug_flag > 0 THEN
820 cmcommon_log( ' Cost Type: ' || l_cost_type);
821 END IF;
822
823 IF l_cost_type = 6 THEN
824 x_return_status := Get_Process_Item_Cost (
825 p_api_version => p_api_version
826 ,p_init_msg_list => p_init_msg_list
827 ,x_return_status => x_return_status
828 ,x_msg_count => x_msg_count
829 ,x_msg_data => x_msg_data
830 ,p_inventory_item_id => p_inventory_item_id
831 ,p_organization_id => p_organization_id
832 ,p_transaction_date => p_transaction_date
833 ,p_detail_flag => p_detail_flag
834 ,p_cost_method => p_cost_method
835 ,p_cost_component_class_id => p_cost_component_class_id
836 ,p_cost_analysis_code => p_cost_analysis_code
837 ,X_total_cost => x_total_cost
838 ,X_no_of_rows => x_no_of_rows
839 ,p_lot_number => null
840 ,p_transaction_id => null
841 );
842 RETURN(x_return_status);
843 END IF;
844 END IF;
845 P_acctg_cost := 0;
846 P_fmeff_id := 0;
847 P_cost_type := NULL;
848
849 /***************************************************************************************************
850 * The variable below is initialized to 0 instead of null to avoid NULL comaprison IN where clause. *
851 ***************************************************************************************************/
852 x_itemcost_id := 0;
853
854 /****************************************************************************************
855 * Try to get the cost using the item_id, whse_code and orgn_code from calling PROCEDURE *
856 ****************************************************************************************/
857 IF (P_cached_cost_basis = 0) THEN
858 /****************************************************
859 * get prior period cost. *
860 * first get calendar, period codes for prior period *
861 ****************************************************/
862 OPEN Cur_get_calprd (
863 P_cached_legal_entity_id,
864 p_transaction_date,
865 p_cost_type_id
866 );
867 FETCH Cur_get_calprd INTO Cur_get_calprd_tmp;
868 IF Cur_get_calprd%NOTFOUND THEN
869 CLOSE Cur_get_calprd;
870 /***********************************************************************
871 * No prior period and calendar found for specified p_transaction_date *
872 ***********************************************************************/
873 RETURN(-1);
874 END IF;
875 CLOSE Cur_get_calprd;
876
877 IF( l_debug_flag > 0 ) THEN
878 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);
879 END IF;
880
881 /****************************************************************************
882 * Now select the cost based on the prior calendar and period selected above *
883 ****************************************************************************/
884 OPEN Cur_get_pr_cost (
885 P_cached_cost_organization_id,
886 p_inventory_item_id,
887 p_cost_type_id,
888 Cur_get_calprd_tmp.period_id
889 );
890 FETCH Cur_get_pr_cost INTO Cur_get_pr_cost_tmp;
891 IF Cur_get_pr_cost%FOUND THEN
892 x_no_recs := 1;
893 P_acctg_cost := cur_get_pr_cost_tmp.acctg_cost;
894 P_cost_type := cur_get_pr_cost_tmp.cost_type;
895 P_fmeff_id := cur_get_pr_cost_tmp.fmeff_id;
896 x_itemcost_id := cur_get_pr_cost_tmp.itemcost_id;
897 ELSE
898 x_no_recs := 0;
899 END IF;
900 CLOSE Cur_get_pr_cost;
901 ELSE
902
903 IF l_debug_flag > 0 THEN
904 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);
905 END IF;
906
907 /******************************************
908 * cost_basis = 1. get current period cost *
909 ******************************************/
910 OPEN Cur_get_cost_direct (
911 P_cached_cost_organization_id,
912 p_inventory_item_id,
913 p_cost_type_id,
914 p_transaction_date
915 );
916 FETCH Cur_get_cost_direct INTO Cur_get_cost_direct_tmp;
917 IF Cur_get_cost_direct%FOUND THEN
918 x_no_recs := 1;
919 P_acctg_cost := Cur_get_cost_direct_tmp.acctg_cost;
920 P_cost_type := Cur_get_cost_direct_tmp.cost_type;
921 P_fmeff_id := Cur_get_cost_direct_tmp.fmeff_id;
922 x_itemcost_id := Cur_get_cost_direct_tmp.itemcost_id;
923 ELSE
924 x_no_recs := 0;
925 END IF;
926 CLOSE Cur_get_cost_direct;
927 END IF;
928
929 IF( l_debug_flag > 0 ) THEN
930 cmcommon_log( ' Number of cost rows: ' || x_no_recs );
931 END IF;
932
933 IF (x_no_recs = 0) THEN
934 RETURN(-1);
935 END IF;
936
937 IF( l_debug_flag > 0 ) THEN
938 cmcommon_log( ' Acctg Cost: ' || P_acctg_cost || ' Cost Type: ' || P_cost_type ||' Fmeff_id: ' || P_fmeff_id || ' Itemcost_id: ' || x_itemcost_id );
939 END IF;
940
941 /****************************************************************
942 * The variable below is initialized to 0 in order to avoid NULL *
943 * comparison in the where clause *
944 ****************************************************************/
945
946 IF p_cost_component_class_id IS NULL THEN
947 p_cost_component_class_id := 0;
948 END IF;
949
950 /******************************************
951 * select co cmptcost_amt from gl_item_dtl *
952 ******************************************/
953 x_no_recs := 0;
954 P_no_of_rows := 0;
955
956 IF p_detail_flag = 1 THEN
957 x_no_of_rows := 1; -- PK B 7213143
958 x_total_cost := P_acctg_cost;
959 x_no_recs := 1;
960 ELSIF(p_detail_flag = 2 OR p_detail_flag = 3) THEN
961 i:= 0;
962 FOR cur_item_dtl_tmp IN cur_item_dtl (
963 X_itemcost_id,
964 p_cost_component_class_id,
965 p_cost_analysis_code
966 ) LOOP
967 i:= i + 1;
968 P_cmpntcost_amt(i) := cur_item_dtl_tmp.cmptcost_amt;
969 x_no_recs := i;
970 END LOOP;
971 P_no_of_rows := i;
972 x_no_of_rows := i;
973
974 /****************************************************************
975 * Get item detail cmponent class ids/analysis codes *
976 * This loop retrieves the multiple compnt_cost,cost_cmpntcls_id *
977 * and analysis code and populates the array P_cmpntcost_amt, *
978 * P_cost_cmpntcls_id,P_cost_analysis_code *
979 ****************************************************************/
980
981 ELSIF p_detail_flag = 4 THEN
982 i:= 0;
983 FOR cur_item_cost_tmp IN cur_item_cost(X_itemcost_id) LOOP
984 i:= i + 1;
985 P_cmpntcost_amt(i) := cur_item_cost_tmp.cmptcost_amt;
986 P_cost_cmpntcls_id(i) := cur_item_cost_tmp.cost_cmpntcls_id;
987 P_cost_analysis_code1(i) := cur_item_cost_tmp.cost_analysis_code;
988 x_no_recs := i;
989 END LOOP;
990 P_no_of_rows := i;
991
992 /*****************************************************************************
993 * This out parameter is introduced in order to access the value of it *
994 * in the forms. The reason behind is, one cannot access directly the package *
995 * spec variables such as P_no_of_rows *
996 *****************************************************************************/
997 x_no_of_rows := i;
998
999 /****************************************************************
1000 * get total cost of item ppv/matl comp class ids/analysis codes *
1001 ****************************************************************/
1002 ELSIF p_detail_flag = 5 THEN
1003 OPEN Cur_glitmdtl(X_itemcost_id);
1004 FETCH Cur_glitmdtl INTO x_total_cost;
1005 IF Cur_glitmdtl%NOTFOUND THEN
1006 x_total_cost := 0;
1007 x_no_recs := 0;
1008 ELSE
1009 x_no_recs := 1;
1010 END IF;
1011 CLOSE Cur_glitmdtl;
1012 END IF;
1013 IF (x_no_recs = 0) THEN
1014 RETURN (-1);
1015 ELSE
1016 RETURN(1);
1017 END IF;
1018 RETURN(1);
1019
1020 /**************************************************************************
1021 * Standard call to get message count and if count is 1, get message info. *
1022 **************************************************************************/
1023 FND_MSG_PUB.Count_And_Get (
1024 p_count => x_msg_count,
1025 p_data => x_msg_data
1026 );
1027 EXCEPTION
1028 WHEN FND_API.G_EXC_ERROR THEN
1029 x_return_status := FND_API.G_RET_STS_ERROR ;
1030 FND_MSG_PUB.Count_And_Get (
1031 p_count => x_msg_count,
1032 p_data => x_msg_data
1033 );
1034 RETURN -1;
1035 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1036 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1037 FND_MSG_PUB.Count_And_Get (
1038 p_count => x_msg_count,
1039 p_data => x_msg_data
1040 );
1041 RETURN -1;
1042 WHEN OTHERS THEN
1043 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1044 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1045 FND_MSG_PUB.Add_Exc_Msg (
1046 G_PKG_NAME,
1047 l_api_name
1048 );
1049 END IF;
1050 FND_MSG_PUB.Count_And_Get (
1051 p_count => x_msg_count,
1052 p_data => x_msg_data
1053 );
1054 RETURN -1;
1055 END Get_Process_Item_Cost;
1056
1057 /***********************************************************************************************
1058 * FUNCTION *
1059 * Get_Process_Item_Cost *
1060 * *
1061 * DESCRIPTION *
1062 * This function is an overloaded function of get cost routine. This function *
1063 * can be used to get costs for non lot cost method or lot cost method. *
1064 * *
1065 * For non lot cost method this function retrieves item cost, cost type *
1066 * and fmeff_id from gl_item_cst and cmptcost_amt from gl_item_dtl *
1067 * get_cost should return the cost of the item for the cost warehouse *
1068 * if there is a cost warehouse associated with the given warehouse else *
1069 * it should return the cost of the item for the given warehouse *
1070 * *
1071 * AUTHOR *
1072 * Anand Thiyagarajan 01-JUN-2005 *
1073 * *
1074 * INPUT PARAMETERS *
1075 * inventory_item_id = Item id *
1076 * Organization_id = Organization *
1077 * transaction_date = Date of item cost *
1078 * cost_mthd = cost method used *
1079 * cost_component_class_id = component class id *
1080 * analysis_code = analysis code *
1081 * detail_flag = 1 => retreive just acctg_cost *
1082 * 2 => retreive acctg_cost all cmptcost_amts for itemcost *
1083 * 3 => retreive acctg_cost cmptcost_amt for itemcost_id, *
1084 * cost_cmpntcls_id cost_analysis Code *
1085 * lot_number = Lot Number *
1086 * Transaction_id = Transaction Identifier *
1087 * *
1088 * OUTPUT PARAMETERS *
1089 * total_cost = This out parameter should be reffered only in the *
1090 * case when retrieve_ind value is passed as 1 *
1091 * *
1092 * RETURNS (choose one set) *
1093 * 0 - success *
1094 * 1 - could not get transfer_price *
1095 * 2 - could not get item unit cost *
1096 * 3 - uom conversion error *
1097 * *
1098 * HISTORY
1099 * Sukarna Reddy Dt 26-Oct-2005 removed lot_id and replaced it with lot number in *
1100 * PARTITION BY CLAUSE. *
1101 * Added delete mark in the where clause while fetching default cost method *
1102 * Added RAISE exception to avoid circular calls to get_process_item_cost. if alternate *
1103 * cost method is marked for purge. *
1104 ***********************************************************************************************/
1105 FUNCTION Get_Process_Item_Cost
1106 (
1107 p_api_version IN NUMBER
1108 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1109 , x_return_status OUT NOCOPY VARCHAR2
1110 , x_msg_count OUT NOCOPY NUMBER
1111 , x_msg_data OUT NOCOPY VARCHAR2
1112 , p_inventory_item_id IN NUMBER /* Item_Id */
1113 , p_organization_id IN NUMBER /* Inventory Organization Id */
1114 , p_transaction_date IN DATE /* Cost as on date */
1115 , p_detail_flag IN NUMBER /* same as retrieve indicator: */ /* 1 = total cost, 2 = details; */ /* 3 = cost for a specific component class/analysis code, etc. */
1116 , p_cost_method IN OUT NOCOPY VARCHAR2 /* OPM Cost Method */
1117 , p_cost_component_class_id IN OUT NOCOPY NUMBER
1118 , p_cost_analysis_code IN OUT NOCOPY VARCHAR2
1119 , x_total_cost OUT NOCOPY NUMBER /* total cost */
1120 , x_no_of_rows OUT NOCOPY NUMBER /* number of detail rows retrieved */
1121 , p_lot_number IN VARCHAR2 /* Lot Number for the Item/Lot */
1122 , p_transaction_id IN NUMBER /* Transaction_id from MMT */
1123 )
1124 RETURN NUMBER
1125 IS
1126
1127 /******************
1128 * Local Variables *
1129 ******************/
1130 l_api_name CONSTANT VARCHAR2(30) := 'Get_Process_Item_Cost' ;
1131 l_api_version CONSTANT NUMBER := 1.0 ;
1132 l_cost_type_id NUMBER;
1133 l_status NUMBER;
1134 i NUMBER;
1135 l_header_id NUMBER;
1136 l_lot_actual_cost NUMBER;
1137 l_avg_cost NUMBER;
1138 l_cost_type_id1 cm_mthd_mst.cost_type_id%type;
1139 l_cost_type cm_mthd_mst.cost_type%type;
1140 l_default_lot_cost_type_id cm_mthd_mst.default_lot_cost_type_id%type;
1141 l_default_cost_mthd_code cm_mthd_mst.cost_mthd_code%TYPE;
1142
1143 ERROR_RETURN_STATUS EXCEPTION;
1144
1145 /**********
1146 * Cursors *
1147 **********/
1148
1149 CURSOR Cur_get_cmthd_type
1150 (
1151 v_cost_type_id IN NUMBER
1152 )
1153 IS
1154 SELECT
1155 cost_type,
1156 default_lot_cost_type_id
1157 FROM cm_mthd_mst
1158 WHERE cost_type_id = v_cost_type_id;
1159
1160 CURSOR Cur_get_cost_mthd ( v_organization_id IN NUMBER ) IS
1161 SELECT m.cost_type_id,
1162 m.default_lot_cost_type_id
1163 FROM cm_mthd_mst m,
1164 gmf_fiscal_policies plc,
1165 hr_organization_information o
1166 WHERE o.organization_id = v_organization_id
1167 AND o.org_information_context = 'Accounting Information'
1168 AND plc.legal_entity_id = o.org_information2
1169 AND plc.cost_type_id = m.cost_type_id
1170 AND m.delete_mark = 0
1171 AND plc.delete_mark = 0 ;
1172
1173 CURSOR Cur_get_cmpnts
1174 (
1175 v_header_id IN NUMBER,
1176 v_cost_cmpntcls_id IN NUMBER,
1177 v_cost_analysis_code IN VARCHAR2
1178 )
1179 IS
1180 SELECT cost_cmpntcls_id,
1181 cost_analysis_code,
1182 component_cost
1183 FROM gmf_lot_cost_details
1184 WHERE header_id = v_header_id
1185 AND cost_cmpntcls_id = NVL(v_cost_cmpntcls_id,cost_cmpntcls_id)
1186 AND cost_analysis_code = NVL(v_cost_analysis_code,cost_analysis_code);
1187
1188 CURSOR Cur_Get_total_cost
1189 (
1190 v_header_id IN NUMBER,
1191 v_lot_number IN VARCHAR2
1192 )
1193 IS
1194 SELECT sum(component_cost)
1195 FROM gmf_lot_cost_details d,
1196 gmf_lot_costs h
1197 WHERE h.header_id = v_header_id
1198 AND h.header_id = d.header_id
1199 AND h.lot_number = nvl(v_lot_number ,h.lot_number);
1200
1201 CURSOR Cur_Get_header
1202 (
1203 v_trans_id IN NUMBER,
1204 v_cost_type_id IN NUMBER
1205 )
1206 IS
1207 SELECT cost_header_id
1208 FROM gmf_material_lot_cost_txns
1209 WHERE transaction_id = v_trans_id
1210 AND cost_type_id = v_cost_type_id;
1211
1212 CURSOR Cur_Get_recent_hdr
1213 (
1214 v_item_id IN NUMBER,
1215 v_lot_number IN VARCHAR2,
1216 v_cost_type_id IN NUMBER,
1217 v_trans_date IN DATE,
1218 v_organization_id IN VARCHAR2
1219 )
1220 IS
1221 SELECT MAX(header_id)
1222 FROM gmf_lot_costs
1223 WHERE inventory_item_id = p_inventory_item_id
1224 AND cost_type_id = v_cost_type_id
1225 AND lot_number = v_lot_number
1226 AND cost_date <= v_trans_date
1227 AND organization_id = v_organization_id;
1228
1229 CURSOR Cur_get_cost
1230 (
1231 v_item_id IN NUMBER,
1232 v_organization_id IN NUMBER,
1233 v_cost_type_id IN NUMBER,
1234 v_trans_date IN DATE
1235 )
1236 IS
1237 SELECT SUM(onhand_qty*unit_cost)/SUM(onhand_qty)
1238 FROM (
1239 SELECT onhand_qty,
1240 unit_cost,
1241 RANK() OVER (
1242 PARTITION BY lot_number
1243 ORDER BY cost_date desc,
1244 header_id desc
1245 ) as rank
1246 FROM gmf_lot_costs
1247 WHERE cost_date <= v_trans_date
1248 AND inventory_item_id = v_item_id
1249 AND organization_id = v_organization_id
1250 AND cost_type_id = v_cost_type_id
1251 )
1252 WHERE rank = 1;
1253
1254 BEGIN
1255
1256
1257
1258 IF (p_cost_method IS NOT NULL) THEN
1259 SELECT cost_type_id
1260 INTO l_cost_type_id
1261 FROM cm_mthd_mst
1262 WHERE cost_mthd_code = p_cost_method
1263 AND delete_mark = 0;
1264 END IF;
1265
1266 /*************************************************************
1267 * Initialize message list if p_init_msg_list is set to TRUE. *
1268 *************************************************************/
1269 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1270 FND_MSG_PUB.initialize;
1271 END IF;
1272
1273 /*************************************************
1274 * Standard call to check for call compatibility. *
1275 *************************************************/
1276 IF NOT FND_API.Compatible_API_Call (
1277 l_api_version,
1278 p_api_version,
1279 l_api_name,
1280 G_PKG_NAME
1281 ) THEN
1282 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1283 END IF;
1284
1285 /******************************************
1286 * Initialize API return status to success *
1287 ******************************************/
1288 x_return_status := FND_API.G_RET_STS_SUCCESS;
1289 x_msg_count := 0;
1290 x_msg_data := NULL;
1291
1292 IF (p_inventory_item_id IS NULL OR p_organization_id IS NULL OR p_transaction_date IS NULL) THEN
1293 /******************************************
1294 * No required input parameters specified. *
1295 ******************************************/
1296 cmcommon_log( 'Insufficient input parameters; exit status: -2' );
1297 RETURN(-2);
1298 END IF;
1299
1300 /*******************************************************
1301 * If cost method is not passed get gl cost method from *
1302 * fiscal policy *
1303 *******************************************************/
1304 IF (l_cost_type_id IS NULL) THEN
1305 OPEN Cur_get_cost_mthd (
1306 p_organization_id
1307 );
1308 FETCH Cur_get_cost_mthd INTO l_cost_type_id1,
1309 l_default_lot_cost_type_id;
1310 CLOSE Cur_get_cost_mthd;
1311 IF l_cost_type_id1 IS NULL THEN
1312 cmcommon_log( 'No Cost type defined in fiscal policy or specify a cost type; exit status: -2' );
1313 RETURN(-2);
1314 ELSE
1315 l_cost_type_id := l_cost_type_id1;
1316 END IF;
1317 END IF;
1318
1319 /***************************************************************************
1320 * if cost method is passed or retrieved from previous step check to see if *
1321 * its a lot cost method or not lot cost method *
1322 ***************************************************************************/
1323 IF l_cost_type_id IS NOT NULL THEN
1324 OPEN Cur_get_cmthd_type (
1325 l_cost_type_id
1326 );
1327 FETCH Cur_get_cmthd_type INTO l_cost_type,
1328 l_default_lot_cost_type_id;
1329 CLOSE Cur_get_cmthd_type;
1330 END IF;
1331
1332 /**************************************************************
1333 * If not lot cost method then call original get cost FUNCTION *
1334 **************************************************************/
1335
1336 IF l_cost_type <> 6 THEN
1337 l_status := Get_Process_Item_Cost (
1338 p_api_version => p_api_version,
1339 p_init_msg_list => p_init_msg_list,
1340 x_return_status => x_return_status,
1341 x_msg_count => x_msg_count,
1342 x_msg_data => x_msg_data,
1343 p_inventory_item_id => p_inventory_item_id,
1344 p_organization_id => p_organization_id,
1345 p_transaction_date => p_transaction_date,
1346 p_cost_method => p_cost_method,
1347 p_cost_component_class_id => p_cost_component_class_id,
1348 p_cost_analysis_code => p_cost_analysis_code,
1349 p_detail_flag => p_detail_flag,
1350 x_total_cost => x_total_cost,
1351 x_no_of_rows => x_no_of_rows
1352 );
1353 RETURN(l_status);
1354 ELSE
1355 /*******************************************************
1356 * Give trans id an highest priority compared to lot id *
1357 *******************************************************/
1358 IF nvl(p_transaction_id,0) > 0 THEN
1359 OPEN Cur_Get_header (
1360 p_transaction_id,
1361 l_cost_type_id
1362 );
1363 FETCH Cur_Get_header INTO l_header_id;
1364 CLOSE Cur_Get_header;
1365
1366 OPEN Cur_get_total_cost(l_header_id,p_lot_number);
1367 FETCH Cur_Get_total_cost INTO x_total_cost;
1368 CLOSE Cur_get_total_cost;
1369
1370 /************************************************************
1371 * if no trans id is passed then get cost header id based on *
1372 * recent transaction. *
1373 ************************************************************/
1374 ELSIF (nvl(p_transaction_id,0) = 0 OR p_transaction_id < 0) THEN
1375 IF (p_lot_number IS NOT NULL) THEN
1376 OPEN Cur_get_recent_hdr (
1377 p_inventory_item_id,
1378 p_lot_number,
1379 l_cost_type_id,
1380 p_transaction_date,
1381 p_organization_id
1382 );
1383 FETCH Cur_get_recent_hdr INTO l_header_id;
1384 CLOSE Cur_get_recent_hdr;
1385
1386 IF (nvl(l_header_id,0) > 0) THEN
1387 OPEN Cur_get_total_cost (
1388 l_header_id,
1389 p_lot_number
1390 );
1391 FETCH Cur_Get_total_cost INTO x_total_cost;
1392 CLOSE Cur_get_total_cost;
1393 END IF;
1394
1395 /*****************************************************************
1396 * If there is not lot or transaction ID then get cost *
1397 * by applying weighted average of all most recent lots belonging *
1398 * to an item,whse,cost method,cost date *
1399 *****************************************************************/
1400
1401 ELSIF (p_lot_number IS NULL) THEN
1402 OPEN Cur_get_cost (
1403 p_inventory_item_id,
1404 p_organization_id,
1405 l_cost_type_id,
1406 p_transaction_date
1407 );
1408 FETCH Cur_Get_cost INTO l_avg_cost;
1409 CLOSE Cur_Get_cost;
1410 END IF;
1411 IF l_avg_cost IS NOT NULL THEN
1412 x_no_of_rows := 1;
1413 x_total_cost := l_avg_cost;
1414 END IF;
1415 END IF;
1416
1417 -- PK code fix NVL B7213143
1418 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
1419 BEGIN
1420 SELECT cost_mthd_code INTO l_default_cost_mthd_code
1421 FROM cm_mthd_mst
1422 WHERE cost_type_id = l_default_lot_cost_type_id AND
1423 delete_mark = 0;
1424 EXCEPTION
1425 WHEN OTHERS THEN
1426 l_default_cost_mthd_code := NULL;
1427 /* INVCONV sschinch */
1428 -- if we are here then we should return from here to avoid circular calls to get_process_item_cost.
1429 RAISE error_return_status;
1430 END;
1431 l_status := Get_Process_Item_Cost (
1432 p_api_version => p_api_version,
1433 p_init_msg_list => p_init_msg_list,
1434 x_return_status => x_return_status,
1435 x_msg_count => x_msg_count,
1436 x_msg_data => x_msg_data,
1437 p_inventory_item_id => p_inventory_item_id,
1438 p_organization_id => p_organization_id,
1439 p_transaction_date => p_transaction_date,
1440 p_cost_method => l_default_cost_mthd_code,
1441 p_cost_component_class_id => p_cost_component_class_id,
1442 p_cost_analysis_code => p_cost_analysis_code,
1443 p_detail_flag => p_detail_flag,
1444 x_total_cost => x_total_cost,
1445 x_no_of_rows => x_no_of_rows
1446 );
1447 IF l_status < 0 THEN
1448 RAISE error_return_status;
1449 END IF;
1450 END IF;
1451
1452 /***************************************************
1453 * Honor retrieve ind to get some additional info *
1454 * for lot cost method only when lot id or trans id *
1455 * is passed. *
1456 ***************************************************/
1457 IF (p_lot_number IS NOT NULL OR NVL(p_transaction_id,0) > 0) THEN
1458 IF (p_detail_flag = 2 OR p_detail_flag = 3) THEN
1459 i:= 0;
1460 IF (l_header_id > 0 AND p_cost_component_class_id > 0 AND p_cost_analysis_code IS NOT NULL) THEN
1461 FOR Cur_temp IN Cur_Get_cmpnts (
1462 l_header_id,
1463 p_cost_component_class_id,
1464 p_cost_analysis_code
1465 ) LOOP
1466 i:= i + 1;
1467 P_cmpntcost_amt(i) := cur_temp.component_cost;
1468 END LOOP;
1469 x_no_of_rows := i;
1470 p_no_of_rows := i;
1471 END IF;
1472 ELSIF (p_detail_flag = 4) THEN
1473 IF (l_header_id > 0) THEN
1474 i := 0;
1475 FOR Cur_temp IN Cur_Get_cmpnts (
1476 l_header_id,
1477 NULL,
1478 NULL
1479 ) LOOP
1480 i:= i + 1;
1481 P_cmpntcost_amt(i) := cur_temp.component_cost;
1482 P_cost_cmpntcls_id(i) := cur_temp.cost_cmpntcls_id;
1483 P_cost_analysis_code1(i) := cur_temp.cost_analysis_code;
1484 END LOOP;
1485 x_no_of_rows := i;
1486 p_no_of_rows := i;
1487 END IF;
1488 END IF;
1489 END IF;
1490
1491 IF (x_no_of_rows = 0) THEN
1492 RAISE ERROR_RETURN_STATUS;
1493 ELSE
1494 RETURN(1);
1495 END IF;
1496 END IF;
1497
1498 /**************************************************************************
1499 * Standard call to get message count and if count is 1, get message info. *
1500 **************************************************************************/
1501 FND_MSG_PUB.Count_And_Get (
1502 p_count => x_msg_count,
1503 p_data => x_msg_data
1504 );
1505
1506 EXCEPTION
1507 WHEN ERROR_RETURN_STATUS THEN
1508 x_no_of_rows := 0;
1509 x_total_cost := 0;
1510 p_no_of_rows := 0;
1511 RETURN(-1);
1512 WHEN FND_API.G_EXC_ERROR THEN
1513 x_return_status := FND_API.G_RET_STS_ERROR ;
1514 FND_MSG_PUB.Count_And_Get (
1515 p_count => x_msg_count,
1516 p_data => x_msg_data
1517 );
1518 RETURN -1;
1519 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1520 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1521 FND_MSG_PUB.Count_And_Get (
1522 p_count => x_msg_count,
1523 p_data => x_msg_data
1524 );
1525 RETURN -1;
1526 WHEN OTHERS THEN
1527 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1528 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1529 FND_MSG_PUB.Add_Exc_Msg (
1530 G_PKG_NAME,
1531 l_api_name
1532 );
1533 END IF;
1534 FND_MSG_PUB.Count_And_Get (
1535 p_count => x_msg_count,
1536 p_data => x_msg_data
1537 );
1538 RETURN -1;
1539 END Get_Process_Item_Cost;
1540
1541
1542 /***************************************************************************************
1543 * FUNCTION *
1544 * Is_Batch_Cost_Frozen *
1545 * *
1546 * DESCRIPTION *
1547 * This function is used to get the status whether the costs Fora particular batch *
1548 * has been closed ore not *
1549 * *
1550 * AUTHOR *
1551 * Anand Thiyagarajan 01-JUN-2005 *
1552 * *
1553 * INPUT PARAMETERS *
1554 * batch_id = Batch id *
1555 * *
1556 * OUTPUT PARAMETERS *
1557 * *
1558 * RETURNS *
1559 * TRUE => Period Costs are Frozen *
1560 * FALSE => Period Costs are Opn *
1561 * *
1562 * HISTORY *
1563 * *
1564 ***************************************************************************************/
1565 FUNCTION is_batch_cost_frozen
1566 (
1567 p_api_version IN NUMBER
1568 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
1569 , p_commit IN VARCHAR2 := FND_API.G_FALSE
1570 , x_return_status OUT NOCOPY VARCHAR2
1571 , x_msg_count OUT NOCOPY NUMBER
1572 , x_msg_data OUT NOCOPY VARCHAR2
1573 , p_batch_id IN gme_batch_header.batch_id%TYPE
1574 )
1575 RETURN BOOLEAN
1576 IS
1577
1578 /******************
1579 * Local Variables *
1580 ******************/
1581 l_api_name CONSTANT VARCHAR2(30) := 'Is_Batch_Cost_Frozen' ;
1582 l_api_version CONSTANT NUMBER := 1.0 ;
1583 l_cnt_frozen_matls NUMBER := 0;
1584
1585 /**********
1586 * Cursors *
1587 **********/
1588
1589 CURSOR c_get_period_info
1590 (
1591 l_batch_id IN gme_batch_header.batch_id%TYPE
1592 )
1593 IS
1594 SELECT count(1)
1595 FROM cm_cmpt_dtl cst,
1596 cm_acst_led aled,
1597 gme_material_details md,
1598 gme_batch_header bh
1599 WHERE bh.batch_id = l_batch_id
1600 AND bh.batch_id = md.batch_id
1601 AND md.material_detail_id = aled.transline_id
1602 AND aled.source_ind = 0
1603 AND aled.cmpntcost_id = cst.cmpntcost_id
1604 AND cst.rollover_ind = 1;
1605
1606 BEGIN
1607
1608
1609
1610 /*************************************************************
1611 * Initialize message list if p_init_msg_list is set to TRUE. *
1612 *************************************************************/
1613 IF FND_API.to_Boolean( p_init_msg_list ) THEN
1614
1615 FND_MSG_PUB.initialize;
1616
1617 END IF;
1618
1619 /*************************************************
1620 * Standard call to check for call compatibility. *
1621 *************************************************/
1622 IF NOT FND_API.Compatible_API_Call
1623 (
1624 l_api_version,
1625 p_api_version,
1626 l_api_name,
1627 G_PKG_NAME
1628 ) THEN
1629
1630 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1631
1632 END IF;
1633
1634 /******************************************
1635 * Initialize API return status to success *
1636 ******************************************/
1637 x_return_status := FND_API.G_RET_STS_SUCCESS;
1638 x_msg_count := 0;
1639 x_msg_data := NULL;
1640
1641 OPEN c_get_period_info( p_batch_id);
1642 FETCH c_get_period_info INTO l_cnt_frozen_matls;
1643 IF c_get_period_info%NOTFOUND THEN
1644
1645 CLOSE c_get_period_info;
1646 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1647
1648 END IF;
1649 CLOSE c_get_period_info;
1650
1651 /**************************************************************************
1652 * Standard call to get message count and if count is 1, get message info. *
1653 **************************************************************************/
1654 FND_MSG_PUB.Count_And_Get (
1655 p_count => x_msg_count,
1656 p_data => x_msg_data
1657 );
1658 /*************************************************************
1659 * Initialize message list if p_init_msg_list is set to TRUE. *
1660 *************************************************************/
1661 IF FND_API.to_Boolean( p_commit ) THEN
1662 COMMIT;
1663 END IF;
1664
1665 IF nvl(l_cnt_frozen_matls,0) > 0 THEN
1666 RETURN TRUE;
1667 ELSE
1668 RETURN FALSE;
1669 END IF;
1670
1671 EXCEPTION
1672 WHEN FND_API.G_EXC_ERROR THEN
1673 x_return_status := FND_API.G_RET_STS_ERROR ;
1674 FND_MSG_PUB.Count_And_Get (
1675 p_count => x_msg_count,
1676 p_data => x_msg_data
1677 );
1678 RETURN NULL;
1679 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1680 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1681 FND_MSG_PUB.Count_And_Get (
1682 p_count => x_msg_count,
1683 p_data => x_msg_data
1684 );
1685 RETURN NULL;
1686 WHEN OTHERS THEN
1687 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
1688 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1689 FND_MSG_PUB.Add_Exc_Msg (
1690 G_PKG_NAME,
1691 l_api_name
1692 );
1693 END IF;
1694 FND_MSG_PUB.Count_And_Get (
1695 p_count => x_msg_count,
1696 p_data => x_msg_data
1697 );
1698 RETURN NULL;
1699 END Is_Batch_Cost_Frozen;
1700
1701 /************************************************************************************
1702 * FUNCTION
1703 * get_process_item_price
1704 *
1705 * DESCRIPTION
1706 * This is a wrapper function which calls get_process_item_unit_price
1707 * or transfer price API to get the cost.
1708 *
1709 * - For process/discrete transfers, get transfer_price.
1710 * - For process/process transfers, get item unit cost.
1711 *
1712 * This function is called from PO Req's POXRQLNS.pld
1713 *
1714 * AUTHOR
1715 * umoogala 10-Feb-2005 genesis
1716 * OPM INVCONV
1717 *
1718 * INPUT PARAMETERS
1719 * p_inventory_item_id IN NUMBER
1720 * p_src_organization_id IN NUMBER
1721 * p_src_process_enabled_flag IN VARCHAR2
1722 * p_dest_organization_id IN NUMBER
1723 * p_dest_process_enabled_flag IN VARCHAR2
1724 * p_trans_uom IN VARCHAR2
1725 * p_trans_date IN DATE
1726 *
1727 * x_unit_price OUT NOCOPY NUMBER
1728 * x_currency_code OUT NOCOPY VARCHAR2
1729 * x_incr_transfer_price OUT NOCOPY NUMBER
1730 * x_incr_currency_code OUT NOCOPY VARCHAR2
1731 *
1732 * OUTPUT PARAMETERS
1733 * Returns cost of an item.
1734 * Return Status
1735 * 0 - success
1736 * -1 - could not get transfer_price
1737 * -2 - could not get item unit cost
1738 * -3 - uom conversion error
1739 *
1740 * HISTORY
1741 *
1742 **************************************************************************************/
1743
1744 PROCEDURE get_process_item_price (
1745 p_inventory_item_id IN NUMBER
1746 , p_trans_qty IN NUMBER
1747 , p_trans_uom IN VARCHAR2
1748 , p_trans_date IN DATE
1749
1750 , p_src_organization_id IN NUMBER
1751 , p_src_process_enabled_flag IN VARCHAR2
1752
1753 , p_dest_organization_id IN NUMBER
1754 , p_dest_process_enabled_flag IN VARCHAR2
1755
1756 , p_source IN VARCHAR2
1757
1758 , x_unit_price OUT NOCOPY NUMBER
1759 , x_unit_price_priuom OUT NOCOPY NUMBER
1760 , x_currency_code OUT NOCOPY VARCHAR2
1761 , x_incr_transfer_price OUT NOCOPY NUMBER
1762 , x_incr_currency_code OUT NOCOPY VARCHAR2
1763 , x_return_status OUT NOCOPY NUMBER
1764 )
1765 IS
1766
1767 l_return_status VARCHAR2(1);
1768 x_msg_count NUMBER;
1769 x_msg_data VARCHAR2(2000);
1770 x_no_of_rows NUMBER;
1771
1772 l_transfer_type VARCHAR2(6);
1773 l_from_ou NUMBER;
1774 l_to_ou NUMBER;
1775
1776 l_conversion_rate NUMBER;
1777 l_primary_uom_code mtl_units_of_measure.uom_code%TYPE;
1778 l_trans_uom_code mtl_units_of_measure.uom_code%TYPE;
1779 l_primary_uom mtl_units_of_measure.unit_of_measure%TYPE;
1780
1781 BEGIN
1782
1783 l_return_status := FND_API.G_RET_STS_SUCCESS;
1784 l_transfer_type := 'INTORG';
1785
1786 IF (p_src_process_enabled_flag <> p_dest_process_enabled_flag)
1787 THEN
1788 -- process/discrete internal orders. Get transfer_price
1789 -- For INTCOM xfers, get transfer_price using INV API.
1790 -- For INTORG xfers, get transfer_price using Pricelist
1791
1792 /*
1793 IF l_from_ou <> l_to_ou
1794 THEN
1795 IF fnd_profile.value('INV_INTERCOMPANY_INVOICE_INTERNAL_ORDER') = 1
1796 THEN
1797 l_transfer_type := 'INTCOM';
1798 ELSE
1799 l_transfer_type := 'INTORD';
1800 END IF;
1801 ELSE
1802 l_transfer_type := 'INTORD';
1803 END IF;
1804 */
1805
1806 SELECT to_number(src.org_information2) src_ou, to_number(dest.org_information2) dest_ou
1807 INTO l_from_ou, l_to_ou
1808 FROM hr_organization_information src, hr_organization_information dest
1809 WHERE src.organization_id = p_src_organization_id
1810 AND src.org_information_context = 'Accounting Information'
1811 AND dest.organization_id = p_dest_organization_id
1812 AND dest.org_information_context = 'Accounting Information'
1813 ;
1814
1815 GMF_get_transfer_price_PUB.get_transfer_price (
1816 p_api_version => 1.0
1817 , p_init_msg_list => 'F'
1818
1819 , p_inventory_item_id => p_inventory_item_id
1820 , p_transaction_qty => p_trans_qty
1821 , p_transaction_uom => p_trans_uom
1822
1823 , p_transaction_id => NULL
1824 , p_global_procurement_flag => 'N'
1825 , p_drop_ship_flag => 'N'
1826
1827 , p_from_organization_id => p_src_organization_id
1828 , p_from_ou => l_from_ou
1829 , p_to_organization_id => p_dest_organization_id
1830 , p_to_ou => l_to_ou
1831
1832 , p_transfer_type => l_transfer_type
1833 , p_transfer_source => p_source
1834
1835 , x_return_status => l_return_status
1836 , x_msg_data => x_msg_data
1837 , x_msg_count => x_msg_count
1838
1839 , x_transfer_price => x_unit_price
1840 , x_transfer_price_priuom => x_unit_price_priuom
1841 , x_currency_code => x_currency_code
1842 , x_incr_transfer_price => x_incr_transfer_price /* not used */
1843 , x_incr_currency_code => x_incr_currency_code /* not used */
1844 );
1845
1846 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR
1847 x_unit_price IS NULL
1848 THEN
1849 x_unit_price := 0;
1850 x_return_status := -1; -- since pld cannot read db package variables
1851 END IF;
1852
1853 ELSIF p_src_process_enabled_flag = 'Y' AND p_dest_process_enabled_flag = 'Y'
1854 THEN
1855 -- process to process orders
1856
1857 GMF_cmcommon.get_process_item_unit_price (
1858 p_inventory_item_id => p_inventory_item_id
1859 , p_organization_id => p_src_organization_id
1860 , p_trans_date => p_trans_date
1861 , x_unit_price => x_unit_price
1862 , x_return_status => l_return_status
1863 );
1864
1865 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
1866 THEN
1867 x_unit_price := 0;
1868 x_return_status := -2; -- since pld cannot read db package variables
1869 END IF;
1870
1871
1872 IF l_return_status = FND_API.G_RET_STS_SUCCESS
1873 THEN
1874 ------------------------------------------------------------
1875 -- If the trans UOM and the item's primary UOM are same then
1876 -- the unit price is same else we need to calculate (derive)
1877 -- the unit price for the requisiton uom
1878 ------------------------------------------------------------
1879
1880 SELECT primary_unit_of_measure
1881 INTO l_primary_uom
1882 FROM mtl_system_items
1883 WHERE inventory_item_id = p_inventory_item_id
1884 AND organization_id = p_src_organization_id;
1885
1886
1887 IF l_primary_uom <> p_trans_uom
1888 THEN
1889
1890 SELECT uom_code
1891 INTO l_primary_uom_code
1892 FROM mtl_units_of_measure_vl
1893 WHERE unit_of_measure_tl = l_primary_uom;
1894
1895 SELECT uom_code
1896 INTO l_trans_uom_code
1897 FROM mtl_units_of_measure_vl
1898 WHERE unit_of_measure_tl = p_trans_uom;
1899
1900 inv_convert.inv_um_conversion(
1901 from_unit => l_primary_uom_code
1902 , to_unit => l_trans_uom_code
1903 , item_id => p_inventory_item_id
1904 , lot_number => NULL
1905 , organization_id => p_src_organization_id
1906 , uom_rate => l_conversion_rate
1907 );
1908
1909 IF l_conversion_rate = -99999
1910 THEN
1911 x_unit_price := 0;
1912 x_return_status := -3;
1913 ELSE
1914 x_unit_price := round((x_unit_price/l_conversion_rate), 5);
1915 END IF;
1916
1917 END IF; -- IF l_primary_uom <> p_trans_uom
1918
1919 END IF; -- IF x_return_status = FND_API.G_RET_STS_SUCCESS
1920
1921 END IF; -- ELSIF p_src_process_enabled_flag = 'Y' AND p_dest_process_enabled_flag = 'Y'
1922
1923 x_return_status := 0;
1924
1925 END get_process_item_price;
1926
1927
1928 /************************************************************************************
1929 * FUNCTION
1930 * get_process_item_unit_price
1931 *
1932 * DESCRIPTION
1933 * This is a wrapper function which calls Get_Process_Item_Cost to return
1934 * the cost. This function is called from PO Req's POXRQLNS.pld
1935 * -- PPV report(POXRCPPV.rdf)
1936 *
1937 * AUTHOR
1938 * umoogala 10-Feb-2005 genesis
1939 * OPM INVCONV
1940 *
1941 * INPUT PARAMETERS
1942 * p_inventory_item_id
1943 * p_organization_id
1944 * p_trans_uom
1945 * p_trans_date
1946 *
1947 * OUTPUT PARAMETERS
1948 * Returns cost of an item.
1949 * Status -1 or 0
1950 *
1951 * HISTORY
1952 *
1953 **************************************************************************************/
1954
1955 PROCEDURE get_process_item_unit_price (
1956 p_inventory_item_id IN NUMBER
1957 , p_organization_id IN NUMBER
1958 , p_trans_date IN DATE
1959 , x_unit_price OUT NOCOPY NUMBER
1960 , x_return_status OUT NOCOPY VARCHAR2
1961 )
1962 IS
1963
1964 l_return_status VARCHAR2(1);
1965 l_ret_val NUMBER;
1966 x_msg_count NUMBER;
1967 x_msg_data VARCHAR2(2000);
1968 x_no_of_rows NUMBER;
1969
1970 x_cost_method cm_mthd_mst.cost_mthd_code%TYPE;
1971 x_cost_component_class_id cm_cmpt_mst.cost_cmpntcls_id%TYPE;
1972 x_cost_analysis_code cm_alys_mst.cost_analysis_code%TYPE;
1973
1974 BEGIN
1975
1976 l_return_status := FND_API.G_RET_STS_SUCCESS;
1977
1978 l_ret_val := GMF_CMCOMMON.Get_Process_Item_Cost (
1979 1.0
1980 , fnd_api.g_true
1981 , l_return_status
1982 , x_msg_count
1983 , x_msg_data
1984 , p_inventory_item_id
1985 , p_organization_id
1986 , p_trans_date
1987 , 1 -- return unit_price
1988 , x_cost_method
1989 , x_cost_component_class_id
1990 , x_cost_analysis_code
1991 , x_unit_price
1992 , x_no_of_rows
1993 );
1994
1995 IF l_ret_val <> 1 OR l_return_status <> FND_API.G_RET_STS_SUCCESS
1996 THEN
1997 x_return_status := FND_API.G_RET_STS_ERROR;
1998 END IF;
1999
2000 x_return_status := FND_API.G_RET_STS_SUCCESS;
2001 x_unit_price := 1;
2002
2003 END get_process_item_unit_price;
2004
2005
2006 /*************************************************************************
2007 * FUNCTION *
2008 * process_item_unit_cost *
2009 * *
2010 * DESCRIPTION *
2011 * Unit cost of a process item (R12 version of unit cost function) *
2012 * *
2013 * HISTORY *
2014 * 11-Jul-2005 Rajesh Seshadri created stub version *
2015 * 23-AUG-2005 Anand Thiyagarajan Implementation Details *
2016 * *
2017 *************************************************************************/
2018 FUNCTION process_item_unit_cost
2019 (
2020 p_inventory_item_id IN NUMBER,
2021 p_organization_id IN NUMBER,
2022 p_transaction_date IN DATE
2023 ) RETURN NUMBER
2024 IS
2025
2026 /******************
2027 * Local Variables *
2028 ******************/
2029
2030 l_ret_val NUMBER;
2031 l_return_status VARCHAR2(5);
2032 l_msg_count NUMBER(10);
2033 l_msg_data VARCHAR2(2000);
2034 l_total_cost NUMBER;
2035 l_no_of_rows NUMBER(10);
2036 l_cost_method CM_MTHD_MST.COST_MTHD_CODE%TYPE;
2037 l_cost_component_class_id CM_CMPT_DTL.COST_CMPNTCLS_ID%TYPE;
2038 l_cost_analysis_code CM_CMPT_DTL.COST_ANALYSIS_CODE%TYPE;
2039 l_api_name CONSTANT VARCHAR2(30) := 'Process_Item_Unit_Cost' ;
2040 l_api_version CONSTANT NUMBER := 1.0 ;
2041
2042 /********************************************
2043 * Change this only when absolutely required *
2044 ********************************************/
2045
2046 l_debug_flag NUMBER := 0;
2047
2048 BEGIN
2049
2050 l_debug_flag := G_DEBUG_LEVEL;
2051
2052 /******************************************
2053 * Initialize API return status to success *
2054 ******************************************/
2055 l_return_status := FND_API.G_RET_STS_SUCCESS;
2056 l_msg_count := 0;
2057 l_msg_data := NULL;
2058
2059 IF (l_debug_flag > 0) THEN
2060 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') );
2061 END IF;
2062
2063 IF (p_inventory_item_id IS NULL OR p_organization_id IS NULL OR p_transaction_date IS NULL) THEN
2064 IF( l_debug_flag > 0 ) THEN
2065 cmcommon_log( 'Insufficient input parameters; exit status: -2' );
2066 END IF;
2067 RETURN(0);
2068 END IF;
2069
2070 l_ret_val := Get_Process_Item_Cost
2071 (
2072 p_api_version => l_api_version
2073 , p_init_msg_list => FND_API.G_TRUE
2074 , x_return_status => l_return_status
2075 , x_msg_count => l_msg_count
2076 , x_msg_data => l_msg_data
2077 , p_inventory_item_id => p_inventory_item_id
2078 , p_organization_id => p_organization_id
2079 , p_transaction_date => p_transaction_date
2080 , p_detail_flag => 1
2081 , p_cost_method => l_cost_method
2082 , p_cost_component_class_id => l_cost_component_class_id
2083 , p_cost_analysis_code => l_cost_analysis_code
2084 , x_total_cost => l_total_cost
2085 , x_no_of_rows => l_no_of_rows
2086 );
2087 IF (l_debug_flag > 0) THEN
2088 cmcommon_log( 'Return Status => '|| l_return_status || ' Message => '|| l_msg_data ||' Cost => '||l_total_cost);
2089 END IF;
2090
2091 /**************************************************************************
2092 * Standard call to get message count and if count is 1, get message info. *
2093 **************************************************************************/
2094 FND_MSG_PUB.Count_And_Get (
2095 p_count => l_msg_count,
2096 p_data => l_msg_data
2097 );
2098
2099 RETURN (nvl(l_total_cost,0));
2100
2101 EXCEPTION
2102 WHEN FND_API.G_EXC_ERROR THEN
2103 l_return_status := FND_API.G_RET_STS_ERROR ;
2104 FND_MSG_PUB.Count_And_Get (
2105 p_count => l_msg_count,
2106 p_data => l_msg_data
2107 );
2108 RETURN 0;
2109 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2110 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2111 FND_MSG_PUB.Count_And_Get (
2112 p_count => l_msg_count,
2113 p_data => l_msg_data
2114 );
2115 RETURN 0;
2116 WHEN OTHERS THEN
2117 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2118 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2119 FND_MSG_PUB.Add_Exc_Msg (
2120 G_PKG_NAME,
2121 l_api_name
2122 );
2123 END IF;
2124 FND_MSG_PUB.Count_And_Get (
2125 p_count => l_msg_count,
2126 p_data => l_msg_data
2127 );
2128 RETURN 0;
2129
2130 END process_item_unit_cost;
2131
2132 /*************************************************************************
2133 * FUNCTION *
2134 * process_item_unit_cost *
2135 * *
2136 * DESCRIPTION *
2137 * Unit cost of a process item (R12 version of unit cost function) *
2138 * Overloaded for Lot Specific Costs *
2139 * *
2140 * HISTORY *
2141 * 11-Jul-2005 Rajesh Seshadri created stub version *
2142 * 23-AUG-2005 Anand Thiyagarajan Implementation Details *
2143 * *
2144 *************************************************************************/
2145 FUNCTION process_item_unit_cost
2146 (
2147 p_inventory_item_id IN NUMBER,
2148 p_organization_id IN NUMBER,
2149 p_transaction_date IN DATE,
2150 p_lot_number IN VARCHAR2,
2151 p_transaction_id IN NUMBER
2152 )
2153 RETURN NUMBER
2154 IS
2155 /******************
2156 * Local Variables *
2157 ******************/
2158
2159 l_ret_val NUMBER;
2160 l_return_status VARCHAR2(5);
2161 l_msg_count NUMBER(10);
2162 l_msg_data VARCHAR2(2000);
2163 l_total_cost NUMBER;
2164 l_no_of_rows NUMBER(10);
2165 l_cost_method CM_MTHD_MST.COST_MTHD_CODE%TYPE;
2166 l_cost_component_class_id CM_CMPT_DTL.COST_CMPNTCLS_ID%TYPE;
2167 l_cost_analysis_code CM_CMPT_DTL.COST_ANALYSIS_CODE%TYPE;
2168 l_api_name CONSTANT VARCHAR2(30) := 'Process_Item_Unit_Cost' ;
2169 l_api_version CONSTANT NUMBER := 1.0 ;
2170
2171 /********************************************
2172 * Change this only when absolutely required *
2173 ********************************************/
2174
2175 l_debug_flag NUMBER := 0;
2176
2177 BEGIN
2178
2179
2180 l_debug_flag := G_DEBUG_LEVEL;
2181
2182 /******************************************
2183 * Initialize API return status to success *
2184 ******************************************/
2185 l_return_status := FND_API.G_RET_STS_SUCCESS;
2186 l_msg_count := 0;
2187 l_msg_data := NULL;
2188
2189 IF (l_debug_flag > 0) THEN
2190 cmcommon_log( 'Input parameters: Inventory Item Id: ' || p_inventory_item_id || ' Org: ' || p_Organization_id);
2191 cmcommon_log( 'Input Parameters: Cost Date: ' || to_char(p_transaction_date, 'yyyy-mm-dd hh24:mi:ss'));
2192 cmcommon_log( 'Input Parameters: Lot Number: '|| p_lot_number || ' Transaction Id: '|| p_transaction_id);
2193 END IF;
2194
2195 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
2196 IF( l_debug_flag > 0 ) THEN
2197 cmcommon_log( 'Insufficient input parameters; exit status: -2' );
2198 END IF;
2199 RETURN(0);
2200 END IF;
2201
2202 l_ret_val := Get_Process_Item_Cost
2203 (
2204 p_api_version => l_api_version
2205 , p_init_msg_list => FND_API.G_TRUE
2206 , x_return_status => l_return_status
2207 , x_msg_count => l_msg_count
2208 , x_msg_data => l_msg_data
2209 , p_inventory_item_id => p_inventory_item_id
2210 , p_organization_id => p_organization_id
2211 , p_transaction_date => p_transaction_date
2212 , p_detail_flag => 1
2213 , p_cost_method => l_cost_method
2214 , p_cost_component_class_id => l_cost_component_class_id
2215 , p_cost_analysis_code => l_cost_analysis_code
2216 , x_total_cost => l_total_cost
2217 , x_no_of_rows => l_no_of_rows
2218 , p_lot_number => p_lot_number
2219 , p_transaction_id => p_transaction_id
2220 );
2221
2222 IF (l_debug_flag > 0) THEN
2223 cmcommon_log( 'Return Status => '|| l_return_status || ' Message => '|| l_msg_data ||' Cost => '||l_total_cost);
2224 END IF;
2225
2226 /**************************************************************************
2227 * Standard call to get message count and if count is 1, get message info. *
2228 **************************************************************************/
2229 FND_MSG_PUB.Count_And_Get (
2230 p_count => l_msg_count,
2231 p_data => l_msg_data
2232 );
2233
2234 RETURN (nvl(l_total_cost,0));
2235
2236 EXCEPTION
2237 WHEN FND_API.G_EXC_ERROR THEN
2238 l_return_status := FND_API.G_RET_STS_ERROR ;
2239 FND_MSG_PUB.Count_And_Get (
2240 p_count => l_msg_count,
2241 p_data => l_msg_data
2242 );
2243 RETURN 0;
2244 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
2245 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2246 FND_MSG_PUB.Count_And_Get (
2247 p_count => l_msg_count,
2248 p_data => l_msg_data
2249 );
2250 RETURN 0;
2251 WHEN OTHERS THEN
2252 l_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
2253 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
2254 FND_MSG_PUB.Add_Exc_Msg (
2255 G_PKG_NAME,
2256 l_api_name
2257 );
2258 END IF;
2259 FND_MSG_PUB.Count_And_Get (
2260 p_count => l_msg_count,
2261 p_data => l_msg_data
2262 );
2263 RETURN 0;
2264
2265 END process_item_unit_cost;
2266
2267 /* ***********************************************************************************
2268 * FUNCTION
2269 * get_cmpt_cost
2270 * DESCRIPTION
2271 *
2272 * This function gets item unit cost from cm_cmpt_dtl, NOT from gl_item_cst/dtl.
2273 * This is being use in OPM Batch Cost Detail Report.
2274 *
2275 * AUTHOR
2276 * Anand Thiyagarajan 20-Feb-2007 Bug#5436964 Batches Across Periods FP
2277 *
2278 * INPUT PARAMETERS
2279 * inventory_item_id = Item id
2280 * Organization_id = Organization Id
2281 * transaction_date = Date of item cost
2282 * cost_type_id = Cost Type Used
2283 * Prior Ind = Prior period cost indicator
2284 *
2285 * OUTPUT PARAMETERS
2286 * None.
2287 *
2288 * RETURNS
2289 * total unit cost
2290 *
2291 * HISTORY
2292 * Anand Thiyagarajan 20-Feb-2007 Bug#5436964: Batches Across Periods FP
2293 ***************************************************************************************/
2294 FUNCTION get_cmpt_cost
2295 (
2296 p_inventory_item_id IN NUMBER,
2297 p_organization_id IN NUMBER,
2298 p_transaction_date IN DATE,
2299 p_cost_type_id IN NUMBER,
2300 p_prior_period_cost IN NUMBER
2301 )
2302 RETURN NUMBER
2303 IS
2304 CURSOR get_item_cost
2305 (
2306 v_inventory_item_id NUMBER,
2307 v_organization_id NUMBER,
2308 v_transaction_date DATE,
2309 v_cost_type_id NUMBER
2310 )
2311 IS
2312 SELECT nvl(sum(cst.cmpnt_cost), 0)
2313 FROM cm_cmpt_dtl cst,
2314 gmf_organization_definitions god,
2315 gmf_fiscal_policies f,
2316 gmf_period_statuses gps,
2317 (
2318 select nvl (
2319 (
2320 SELECT x.cost_organization_id
2321 FROM cm_whse_asc x
2322 WHERE x.organization_id = v_organization_id
2323 AND x.eff_start_date <= v_transaction_date
2324 AND x.eff_end_date >= v_transaction_date
2325 AND x.delete_mark = 0
2326 ), v_organization_id) organization_id
2327 from dual
2328 ) oasc
2329 WHERE god.organization_id = nvl(oasc.organization_id, v_organization_id)
2330 AND f.legal_entity_id = god.legal_entity_id
2331 AND f.delete_mark = 0
2332 AND gps.delete_mark = 0
2333 AND gps.legal_entity_id = f.legal_entity_id
2334 AND gps.cost_type_id = nvl(v_cost_type_id, f.cost_type_id)
2335 AND v_transaction_date BETWEEN gps.START_DATE AND gps.end_date
2336 AND cst.inventory_item_id = v_inventory_item_id
2337 AND cst.organization_id = NVL(oasc.organization_id, v_organization_id)
2338 AND cst.period_id = gps.period_id
2339 AND cst.cost_type_id = nvl(v_cost_type_id, f.cost_type_id);
2340
2341 CURSOR get_prior_period_end_date
2342 (
2343 v_organization_id NUMBER,
2344 v_transaction_date DATE,
2345 v_cost_type_id NUMBER
2346 )
2347 IS
2348 SELECT gps.end_date
2349 FROM gmf_organization_definitions god,
2350 gmf_fiscal_policies f,
2351 gmf_period_statuses gps,
2352 (
2353 select nvl (
2354 (
2355 SELECT x.cost_organization_id
2356 FROM cm_whse_asc x
2357 WHERE x.organization_id = v_organization_id
2358 AND x.eff_start_date <= v_transaction_date
2359 AND x.eff_end_date >= v_transaction_date
2360 AND x.delete_mark = 0
2361 ), v_organization_id) organization_id
2362 from dual
2363 ) oasc
2364 WHERE god.organization_id = nvl(oasc.organization_id, v_organization_id)
2365 AND f.legal_entity_id = god.legal_entity_id
2366 AND f.delete_mark = 0
2367 AND gps.delete_mark = 0
2368 AND gps.legal_entity_id = f.legal_entity_id
2369 AND gps.cost_type_id = nvl(v_cost_type_id, f.cost_type_id)
2370 AND gps.end_date < v_transaction_date
2371 ORDER BY gps.end_date desc;
2372
2373 l_cost NUMBER;
2374 l_transaction_date DATE;
2375 BEGIN
2376 IF p_prior_period_cost = 1 THEN
2377 OPEN get_prior_period_end_date(p_organization_id, p_transaction_date, p_cost_type_id);
2378 FETCH get_prior_period_end_date INTO l_transaction_date;
2379 CLOSE get_prior_period_end_date ;
2380 ELSE
2381 l_transaction_date := p_transaction_date;
2382 END IF;
2383
2384 OPEN get_item_cost(p_inventory_item_id, p_organization_id, l_transaction_date, p_cost_type_id);
2385 FETCH get_item_cost INTO l_cost;
2386 CLOSE get_item_cost;
2387
2388 RETURN l_cost;
2389
2390 END get_cmpt_cost;
2391
2392 /* ***********************************************************************************
2393 * FUNCTION
2394 * get_rsrc_cost
2395 * DESCRIPTION
2396 *
2397 * This function gets resource cost from cm_rsrc_dtl.
2398 * This is being use in OPM Batch Cost Detail Report.
2399 *
2400 * AUTHOR
2401 * Anand Thiyagarajan 20-Feb-2007 Bug#5436964 Batches Across Periods FP
2402 *
2403 * INPUT PARAMETERS
2404 * Resources = Resource
2405 * Organization_id = Organization Id
2406 * transaction_date = Date of item cost
2407 * cost_type_id = Cost Type Used
2408 * Prior Ind = Prior period cost indicator
2409 *
2410 * OUTPUT PARAMETERS
2411 * None.
2412 *
2413 * RETURNS
2414 * Resource cost
2415 *
2416 * HISTORY
2417 * Anand Thiyagarajan 20-Feb-2007 Bug#5436964: Batches Across Periods FP
2418 ***************************************************************************************/
2419 FUNCTION get_rsrc_cost
2420 (
2421 p_resources IN VARCHAR2,
2422 p_organization_id IN NUMBER,
2423 p_transaction_date IN DATE,
2424 p_cost_type_id IN NUMBER,
2425 p_prior_period_cost IN NUMBER
2426 )
2427 RETURN NUMBER
2428 IS
2429 CURSOR get_prior_period_end_date
2430 (
2431 v_organization_id NUMBER,
2432 v_transaction_date DATE,
2433 v_cost_type_id NUMBER
2434 )
2435 IS
2436 SELECT gps.end_date
2437 FROM gmf_organization_definitions god,
2438 gmf_fiscal_policies f,
2439 gmf_period_statuses gps
2440 WHERE god.organization_id = v_organization_id
2441 AND f.legal_entity_id = god.legal_entity_id
2442 AND f.delete_mark = 0
2443 AND gps.delete_mark = 0
2444 AND gps.legal_entity_id = f.legal_entity_id
2445 AND gps.cost_type_id = nvl(v_cost_type_id, f.cost_type_id)
2446 AND gps.end_date < v_transaction_date
2447 ORDER BY gps.end_date desc;
2448
2449 CURSOR get_rsrc_cost
2450 (
2451 v_resources VARCHAR2,
2452 v_organization_id NUMBER,
2453 v_transaction_date DATE,
2454 v_cost_type_id NUMBER
2455 )
2456 IS
2457 SELECT nvl(sum(cst.nominal_cost), 0)
2458 FROM cm_rsrc_dtl cst,
2459 gmf_organization_definitions god,
2460 gmf_fiscal_policies f,
2461 gmf_period_statuses gps
2462 WHERE god.organization_id = v_organization_id
2463 AND f.legal_entity_id = god.legal_entity_id
2464 AND f.delete_mark = 0
2465 AND gps.delete_mark = 0
2466 AND gps.legal_entity_id = f.legal_entity_id
2467 AND gps.cost_type_id = nvl(v_cost_type_id, f.cost_type_id)
2468 AND v_transaction_date BETWEEN gps.START_DATE AND gps.end_date
2469 AND cst.resources = v_resources
2470 AND (cst.organization_id = v_organization_id OR cst.organization_id IS NULL)
2471 AND cst.period_id = gps.period_id
2472 AND cst.cost_type_id = nvl(v_cost_type_id, f.cost_type_id);
2473
2474 l_cost NUMBER;
2475 l_transaction_date DATE;
2476
2477 BEGIN
2478 IF p_prior_period_cost = 1 THEN
2479 OPEN get_prior_period_end_date(p_organization_id, p_transaction_date, p_cost_type_id);
2480 FETCH get_prior_period_end_date INTO l_transaction_date;
2481 CLOSE get_prior_period_end_date ;
2482 ELSE
2483 l_transaction_date := p_transaction_date;
2484 END IF;
2485
2486 OPEN get_rsrc_cost(p_resources, p_organization_id, l_transaction_date, p_cost_type_id);
2487 FETCH get_rsrc_cost INTO l_cost;
2488 CLOSE get_rsrc_cost;
2489
2490 RETURN l_cost;
2491
2492 END get_rsrc_cost;
2493
2494 END GMF_CMCOMMON ;
[Home] [Help]