1 PACKAGE BODY pmi_common_pkg as
2 /* $Header: PMICOMMB.pls 120.0 2005/05/24 16:54:43 appldev noship $ */
3
4 FUNCTION PMICO_GET_COST( p_Item_id IN ic_item_mst.item_id%TYPE,
5 p_Whse_code IN ic_whse_mst.whse_code%TYPE,
6 p_Cost_mthd IN cm_cmpt_dtl.cost_mthd_code%TYPE,
7 p_Transaction_date IN DATE)
8 RETURN NUMBER
9 IS
10 /* Cursor to get Organization code for the Warehouse passed */
11 CURSOR cur_whse_orgn_code(l_whse_code IN ic_whse_mst.whse_code%TYPE)
12 IS
13 SELECT orgn_code
14 FROM ic_whse_mst
15 WHERE whse_code = l_whse_code;
16 CURSOR CUR_COST_MTHD(l_whse_code IN ic_whse_mst.whse_code%TYPE)
17 IS
18 SELECT b.orgn_code,c.GL_COST_MTHD
19 FROM ic_whse_mst a, sy_orgn_mst b, GL_PLCY_MST c
20 WHERE a.whse_code = l_whse_code
21 AND a.orgn_code = b.orgn_code
22 AND b.co_code = c.co_code;
23
24 /* Local Variable Declaration */
25 l_whse_code ic_whse_mst.whse_code%TYPE;
26 l_orgn_code sy_orgn_mst.orgn_code%TYPE;
27 l_cmpntcls_ind CM_CMPT_DTL.COST_CMPNTCLS_ID%TYPE;
28 l_analysis_code CM_CMPT_DTL.COST_ANALYSIS_CODE%TYPE;
29 l_total_cost NUMBER;
30 l_no_of_rows NUMBER;
31 l_cost_mthd cm_cmpt_dtl.cost_mthd_code%TYPE;
32
33 BEGIN
34 IF p_cost_mthd IS NOT NULL THEN
35 OPEN cur_whse_orgn_code(p_Whse_code);
36 FETCH cur_whse_orgn_code into l_orgn_code;
37 CLOSE cur_whse_orgn_code;
38 /* Assigning Parameter Cost Method to Local Cost method as the CMCOMMON_GET_COST
39 will accept variable of type IN/OUT */
40 l_cost_mthd:=p_cost_mthd;
41 ELSE
42 OPEN CUR_COST_MTHD(p_Whse_code);
43 FETCH CUR_COST_MTHD into l_orgn_code,l_cost_mthd;
44 CLOSE CUR_COST_MTHD;
45 END IF;
46 IF GMF_CMCOMMON.CMCOMMON_GET_COST(p_Item_id,p_Whse_code,l_orgn_code,p_Transaction_date,
47 l_Cost_mthd,l_cmpntcls_ind,l_analysis_code,1,
48 l_total_cost,l_no_of_rows)= 1 THEN
49 /* if Cost Found Return total Cost */
50 Return l_total_cost;
51 ELSE
52 Return 0;
53 END IF;
54 END PMICO_GET_COST;
55
56 /*Function to calculate amount in to_currency */
57
58 FUNCTION PMICO_GET_MULCURR_AMT( p_From_Curr IN gl_curr_mst.currency_code%TYPE,
59 p_To_Curr IN gl_curr_mst.currency_code%TYPE,
60 p_Rate_date IN DATE,
61 p_amount IN NUMBER)
62 RETURN NUMBER IS
63 /* Cursor to get Rate type Code for Sales orders
64 Trans Source type is Hardcoded with 5 as we need rate type for sales orders.
65 The data in gl_srce_mst is seeded by GMFSEED.sql at the time of customer installation
66 */
67 CURSOR cur_get_rate_type
68 IS
69 SELECT rate_type_code
70 FROM gl_srce_mst
71 WHERE TRANS_SOURCE_TYPE=5;
72 /*local Variables */
73 l_rate_type_code gl_srce_mst.rate_type_code%TYPE;
74 l_mul_div_sign op_ordr_dtl.mul_div_sign%TYPE;
75 l_error_code number:=0;
76 l_exchange_rate number;
77 l_converted_amount number;
78 BEGIN
79 IF(PV_PMICO_GMA_F_CURR =p_from_curr AND PV_PMICO_GMA_T_CURR =p_to_curr AND
80 PV_PMICO_GMA_RATE_DT = p_rate_date) THEN
81 IF PV_PMICO_GMA_SIGN = 0 THEN
82 l_converted_amount:= P_amount*PV_PMICO_GMA_XCNG_RATE;
83 ELSE
84 l_converted_amount:= P_amount/PV_PMICO_GMA_XCNG_RATE;
85 END IF;
86 return l_converted_amount;
87 ELSE
88 /* Open the cursor to get rate type */
89 OPEN cur_get_rate_type;
90 FETCH cur_get_rate_type into l_rate_type_code;
91 CLOSE cur_get_rate_type;
92 l_exchange_rate := gmf_glcommon_db.get_closest_rate(p_from_curr,p_to_curr,
93 p_rate_date,l_rate_type_code,
94 l_mul_div_sign,
95 l_error_code);
96 /* check if function returned any error
97 get_closest_rate return 100 as error*/
98 IF l_error_code = 100 THEN
99 RETURN 0;
100 ELSE
101 IF l_mul_div_sign = 0 THEN
102 /* l_mul_div_sign = 0 indicates to perform multiplication between conversion */
103 l_converted_amount:= P_amount*l_exchange_rate;
104 ELSE
105 l_converted_amount:= P_amount/l_exchange_rate;
106 END IF;
107 PV_PMICO_GMA_F_CURR:=p_from_curr;
108 PV_PMICO_GMA_T_CURR:=p_to_curr;
109 PV_PMICO_GMA_RATE_DT:=p_rate_date;
110 PV_PMICO_GMA_SIGN:=l_mul_div_sign;
111 PV_PMICO_GMA_XCNG_RATE:=l_exchange_rate;
112 return l_converted_amount;
113 END IF;
114 END IF;
115 END PMICO_GET_MULCURR_AMT;
116
117 /*Function to find if conversion factor exist for currency */
118
119 FUNCTION PMICO_CURRCONV_ERROR( p_From_Curr IN gl_curr_mst.currency_code%TYPE,
120 p_To_Curr IN gl_curr_mst.currency_code%TYPE,
121 p_Rate_date IN DATE)
122 RETURN NUMBER IS
123 /* Cursor to get Rate type Code for Sales orders
124 Trans Source type is Hardcoded with 5 as we need rate type for sales orders.
125 The data in gl_srce_mst is seeded by GMFSEED.sql at the time of customer installation
126 */
127 CURSOR cur_get_rate_type
128 IS
129 SELECT rate_type_code
130 FROM gl_srce_mst
131 WHERE TRANS_SOURCE_TYPE=5;
132 /*local Variables */
133 l_rate_type_code gl_srce_mst.rate_type_code%TYPE;
134 l_mul_div_sign op_ordr_dtl.mul_div_sign%TYPE;
135 l_error_code number:=0;
136 l_exchange_rate NUMBER;
137 BEGIN
138 IF(PV_PMICO_CE_F_CURR =p_from_curr AND PV_PMICO_CE_T_CURR =p_to_curr AND
139 PV_PMICO_CE_RATE_DT = p_rate_date) THEN
140 return PV_PMICO_CE_XCNG_ERROR;
141 ELSE
142 /* Open the cursor to get rate type */
143 OPEN cur_get_rate_type;
144 FETCH cur_get_rate_type into l_rate_type_code;
145 CLOSE cur_get_rate_type;
146 l_exchange_rate := gmf_glcommon_db.get_closest_rate(p_from_curr,p_to_curr,
147 p_rate_date,l_rate_type_code,
148 l_mul_div_sign,
149 l_error_code);
150 PV_PMICO_CE_F_CURR:=p_from_curr;
151 PV_PMICO_CE_T_CURR:=p_to_curr;
152 PV_PMICO_CE_RATE_DT:=p_rate_date;
153 /* check if function returned any error get_closest_rate return 100 as error*/
154 IF l_error_code = 100 THEN
155 PV_PMICO_CE_XCNG_ERROR:=1;
156 ELSE
157 PV_PMICO_CE_XCNG_ERROR:=0;
158 END IF;
159 RETURN PV_PMICO_CE_XCNG_ERROR;
160 END IF;
161 END PMICO_CURRCONV_ERROR;
162
163
164
165 FUNCTION PMICO_GET_TARGET(p_target_shortname VARCHAR2,
166 p_ORG_LVL_ID VARCHAR2,
167 p_DIM1_LVL_ID VARCHAR2,
168 p_period_type VARCHAR2,
169 P_period_set_name VARCHAR2,
170 p_plan_id NUMBER,
171 p_from_date DATE,
172 p_to_date DATE,
173 p_param_view_by NUMBER,
174 p_period_num NUMBER)
175 RETURN NUMBER IS
176
177 CURSOR cur_target_lvl IS
178 SELECT TARGET_LEVEL_ID
179 FROM bisbv_target_levels
180 WHERE lower(target_level_short_name) = lower(p_target_shortname);
181 l_target bisbv_targets.TARGET%TYPE;
182 l_period_type gl_periods.period_type%type;
183 target_lvl_id bisbv_target_levels.TARGET_LEVEL_ID%TYPE;
184 time_lvl_id bisbv_targets.TIME_LEVEL_VALUE_ID%TYPE;
185 BEGIN
186 IF p_param_view_by = 1 THEN
187 IF p_period_num IS NOT NULL AND p_period_num <> 0 THEN
188 BEGIN
189 SELECT p_period_set_name || '+' || period_name INTO time_lvl_id
190 FROM gl_periods
191 WHERE period_type = p_period_type AND
192 period_set_name = p_period_set_name AND
193 start_Date BETWEEN p_from_date AND p_to_date AND
194 period_num = p_period_num AND
195 ADJUSTMENT_PERIOD_FLAG <> 'Y' ;
196 EXCEPTION WHEN OTHERS THEN
197 RETURN(NULL);
198 END;
199 ELSE
200 RETURN (NULL);
201 END IF;
202 ELSE
203 BEGIN
204 SELECT p_period_set_name || '+' || period_name INTO time_lvl_id
205 FROM gl_periods
206 WHERE period_type = p_period_type AND
207 period_set_name = p_period_set_name AND
208 start_Date BETWEEN p_from_date AND p_to_date AND
209 ADJUSTMENT_PERIOD_FLAG <> 'Y' ;
210 EXCEPTION WHEN TOO_MANY_ROWS THEN
211 RETURN (NULL);
212 WHEN OTHERS THEN
213 RETURN (NULL);
214 END;
215 END IF;
216 OPEN cur_target_lvl;
217 FETCH cur_target_lvl INTO target_lvl_id;
218 IF cur_target_lvl%NOTFOUND THEN
219 CLOSE cur_target_lvl;
220 RETURN (NULL);
221 END IF;
222 CLOSE cur_target_lvl;
223
224 BEGIN
225 SELECT TARGET INTO l_target
226 FROM bisbv_targets
227 WHERE target_level_id = target_lvl_id and
228 ORG_LEVEL_VALUE_ID = p_ORG_LVL_ID AND
229 PLAN_ID = p_plan_id AND
230 DIM1_LEVEL_VALUE_ID = p_DIM1_LVL_ID AND
231 TIME_LEVEL_VALUE_ID = time_lvl_id;
232
233 EXCEPTION WHEN NO_DATA_FOUND THEN
234 RETURN (NULL);
235 WHEN OTHERS THEN
236 RETURN (NULL);
237 END;
238 return(l_target);
239
240 END PMICO_GET_TARGET;
241
242
243 END pmi_common_pkg;
[Home] [Help]