[Home] [Help]
PACKAGE BODY: APPS.GMICDEP
Source
1 PACKAGE BODY GMICDEP AS
2 /* $Header: gmicdepb.pls 120.1 2005/10/03 12:09:14 jsrivast noship $ */
3 /* =============================================
4 FUNCTION:
5 calc_costs
6
7 DESCRIPTION:
8 This PL/SQL function is responsible for
9 calculating an item's cost.
10
11 This function is dependent on the CMCOMMON
12 package and the function cmcommon_get_cost
13 contained therein.
14
15 This function is used as part of the INVENTORY
16 PRELIMINARY and FINAL CLOSE process.
17
18 SYNOPSIS:
19 iret := GMICDEP.calc_costs(pwhse_code,,
20 pprd_end_date, pperiod,
21 pfiscal_year, pop_code);
22
23 pwhse_code - warehouse code
24 pprd_end_date - the date for the end of the
25 period.
26 pperiod - Period within calendar.
27 pfiscal_year - Fiscal Year of Calendar.
28 pop_code - Operators identifier number.
29 RETURNS:
30 0 Success.
31 -251 Fiscal Policy Error.
32 -252 Cost update to ic_perd_bal error.
33 <-252 Oracle RDBMS Error.
34 ============================================= */
35 FUNCTION calc_costs(pwhse_code VARCHAR2,
36 pprd_end_date DATE,
37 pperiod NUMBER,
38 pfiscal_year VARCHAR2,
39 pop_code NUMBER)
40 RETURN NUMBER IS
41
42 /* Local Variable definitions and initialization:
43 ============================================== */
44 l_iret NUMBER := 0;
45 l_item_id item_srg_type := NULL;
46 l_orgn_code orgn_type := NULL;
47 l_cost_method VARCHAR2(5) := NULL;
48 l_cmpntcls NUMBER := NULL;
49 l_analysis_code VARCHAR2(5) := NULL;
50 l_cost NUMBER := 0;
51 l_indicator NUMBER := 1;
52 l_rows NUMBER := 0;
53
54 /* Cursor Definitions:
55 =================== */
56 CURSOR item_selection IS
57 SELECT DISTINCT(item_id)
58 FROM ic_perd_bal
59 WHERE fiscal_year = UPPER(pfiscal_year)
60 AND period = pperiod
61 AND whse_code = UPPER(pwhse_code)
62 AND loct_usage <> 0;
63
64 CURSOR determine_orgn IS
65 SELECT orgn_code
66 FROM ic_whse_mst
67 where whse_code = UPPER(pwhse_code)
68 AND delete_mark = 0;
69
70 /* ======================================== */
71 BEGIN
72
73 OPEN determine_orgn;
74 FETCH determine_orgn INTO
75 l_orgn_code;
76
77 IF(determine_orgn%NOTFOUND) THEN
78 CLOSE determine_orgn;
79 RETURN 0;
80 END IF;
81 CLOSE determine_orgn;
82
83 OPEN item_selection;
84 FETCH item_selection INTO
85 l_item_id;
86
87 IF(item_selection%NOTFOUND) THEN
88 CLOSE item_selection;
89 RETURN 0;
90 END IF;
91
92 /* ===================================================*/
93 WHILE (item_selection%FOUND) LOOP
94
95 /* Let's call the costing package
96 and determine the items cost.
97 ============================== */
98 l_iret := 0;
99 l_iret := GMF_CMCOMMON.cmcommon_get_cost(l_item_id,
100 pwhse_code, l_orgn_code, pprd_end_date,
101 l_cost_method, l_cmpntcls, l_analysis_code,
102 l_indicator, l_cost, l_rows);
103
104 IF(l_iret = -1) THEN
105 /* The function didn't find a cost!
106 ===============================*/
107 l_cost := 0;
108 ELSIF(l_iret = -3) THEN
109 /* ===============================*/
110 RETURN DEP_COST_FISCAL_POLICY_ERR;
111 ELSIF(l_iret < -3) THEN
112 /* ===============================*/
113 RETURN l_iret;
114 END IF;
115
116 /* Now let's update the perpetual balance
117 table for all occurances of the item.
118 ====================================== */
119 UPDATE ic_perd_bal
120 SET loct_value = (loct_usage * l_cost),
121 last_update_date = SYSDATE,
122 last_updated_by = pop_code
123 WHERE fiscal_year = pfiscal_year
124 AND period = pperiod
125 AND whse_code = pwhse_code
126 AND item_id = l_item_id
127 AND loct_usage <> 0;
128
129 IF(SQL%ROWCOUNT = 0) THEN
130 /* ===============================*/
131 RETURN DEP_COST_UPDATE_ERR;
132 END IF;
133
134 FETCH item_selection INTO
135 l_item_id;
136
137 END LOOP;
138 /* ========================================================*/
139 CLOSE item_selection;
140 RETURN 0;
141
142
143 EXCEPTION
144 WHEN OTHERS THEN
145 RETURN SQLCODE;
146 END calc_costs;
147
148 END;