1 PACKAGE BODY opi_opm_common_pkg as
2 /* $Header: OPICOMMB.pls 115.4 2002/05/07 13:28:59 pkm ship $ */
3
4 FUNCTION OPMCO_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 DEFAULT NULL,
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 OPMCO_GET_COST;
55
56 /*Function to calculate amount in to_currency */
57
58 FUNCTION OPMCO_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_OPMCO_GMA_F_CURR =p_from_curr AND PV_OPMCO_GMA_T_CURR =p_to_curr AND
80 PV_OPMCO_GMA_RATE_DT = p_rate_date) THEN
81 IF PV_OPMCO_GMA_SIGN = 0 THEN
82 l_converted_amount:= P_amount*PV_OPMCO_GMA_XCNG_RATE;
83 ELSE
84 l_converted_amount:= P_amount/PV_OPMCO_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_OPMCO_GMA_F_CURR:=p_from_curr;
108 PV_OPMCO_GMA_T_CURR:=p_to_curr;
109 PV_OPMCO_GMA_RATE_DT:=p_rate_date;
110 PV_OPMCO_GMA_SIGN:=l_mul_div_sign;
111 PV_OPMCO_GMA_XCNG_RATE:=l_exchange_rate;
112 return l_converted_amount;
113 END IF;
114 END IF;
115 END OPMCO_GET_MULCURR_AMT;
116
117 /*Function to find if conversion factor exist for currency */
118
119 FUNCTION OPMCO_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_OPMCO_CE_F_CURR =p_from_curr AND PV_OPMCO_CE_T_CURR =p_to_curr AND
139 PV_OPMCO_CE_RATE_DT = p_rate_date) THEN
140 return PV_OPMCO_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_OPMCO_CE_F_CURR:=p_from_curr;
151 PV_OPMCO_CE_T_CURR:=p_to_curr;
152 PV_OPMCO_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_OPMCO_CE_XCNG_ERROR:=1;
156 ELSE
157 PV_OPMCO_CE_XCNG_ERROR:=0;
158 END IF;
159 RETURN PV_OPMCO_CE_XCNG_ERROR;
160 END IF;
161 END OPMCO_CURRCONV_ERROR;
162
163
164 FUNCTION OPI_OPM_GET_CHARGE ( p_Order_id IN op_ordr_dtl.order_id%TYPE,
165 p_charge_amount IN NUMBER,
166 p_extended_price IN op_ordr_dtl.extended_price%TYPE,
167 p_Billing_Currency IN op_ordr_dtl.billing_currency%TYPE,
168 p_Base_Currency IN op_ordr_dtl.BASE_CURRENCY%TYPE,
169 p_exchange_Rate IN op_ordr_dtl.EXCHANGE_RATE%TYPE,
170 p_mul_div_sign IN op_ordr_dtl.mul_div_sign%TYPE
171 )
172 RETURN NUMBER IS
173
174 /*Cursor to find total value for order*/
175
176 CURSOR cur_order_value(P_order_id op_ordr_dtl.order_id%TYPE)
177 IS
178 SELECT SUM(DECODE(Base_Currency,Billing_Currency,
179 Extended_price,
180 Decode(mul_div_sign,0,Extended_price*Exchange_Rate,
181 Extended_price/Exchange_Rate)))
182 FROM op_ordr_dtl
183 WHERE order_id = P_order_id
184 AND line_status >= 20;
185
186 l_line_charge NUMBER;
187 l_order_value NUMBER;
188 l_order_charge NUMBER;
189 l_total_charge NUMBER;
190 BEGIN
191 /* Get order value to calculate order discount */
192
193 OPEN cur_order_value(P_Order_id);
194 FETCH cur_order_value into l_order_value;
195 IF l_order_value = 0 THEN
196 l_order_value:=1;
197 END IF;
198 CLOSE cur_order_value;
199 /* Calculate total Charges */
200 l_total_charge:=(p_extended_price/(l_order_value)*p_charge_amount);
201 return nvl(l_total_charge,0);
202 END OPI_OPM_GET_CHARGE;
203
204 /* Function to Find Resource Cost */
205
206 FUNCTION OPMCO_GET_RSRC_COST( p_ORGN_CODE IN SY_ORGN_MST.ORGN_CODE%TYPE,
207 p_RESOURCE IN CR_RSRC_MST.RESOURCES%TYPE,
208 p_Cost_mthd IN cm_rsrc_dtl.cost_mthd_code%TYPE DEFAULT NULL,
209 p_usage_uom IN cm_rsrc_dtl.USAGE_UM%TYPE,
210 p_Transaction_date IN DATE)
211 RETURN NUMBER
212 IS
213 CURSOR CUR_COST_MTHD(l_orgn_code IN sy_orgn_mst.orgn_code%TYPE)
214 IS
215 SELECT b.CO_CODE,c.GL_COST_MTHD
216 FROM sy_orgn_mst b, GL_PLCY_MST c
217 WHERE b.orgn_code = l_orgn_code
218 AND b.co_code = c.co_code;
219
220 /* Local Variable Declaration */
221 l_orgn_code sy_orgn_mst.orgn_code%TYPE;
222 l_cost_mthd cm_cmpt_dtl.cost_mthd_code%TYPE;
223 l_co_code sy_orgn_mst.orgn_code%TYPE;
224 l_calendar_code cm_rsrc_dtl.CALENDAR_CODE%TYPE;
225 l_period_code cm_rsrc_dtl.PERIOD_CODE%TYPE;
226 l_rsrc_cost NUMBER;
227 BEGIN
228 l_cost_mthd:=p_cost_mthd;
229 IF p_cost_mthd IS NULL THEN
230 OPEN CUR_COST_MTHD(p_orgn_code);
231 FETCH CUR_COST_MTHD into l_co_code,l_cost_mthd;
232
233 CLOSE CUR_COST_MTHD;
234 ELSE
235 SELECT CO_CODE into l_co_code
236 FROM SY_ORGN_MST
237 WHERE ORGN_CODE=p_ORGN_CODE;
238 END IF;
239 SELECT NOMINAL_COST into l_rsrc_cost
240 FROM CM_RSRC_DTL a,CM_CLDR_DTL b,CM_CLDR_HDR c
241 WHERE a.ORGN_CODE=p_ORGN_CODE
242 AND a.CALENDAR_CODE=b.CALENDAR_CODE
243 AND a.PERIOD_CODE=b.PERIOD_CODE
244 AND a.COST_MTHD_CODE=l_cost_mthd
245 AND a.USAGE_UM=p_usage_uom
246 AND a.RESOURCES=p_resource
247 AND p_transaction_date between b.start_date and b.end_date
248 AND b.CALENDAR_CODE=c.CALENDAR_CODE
249 AND c.cost_mthd_code= l_cost_mthd
250 AND c.co_code=l_co_code;
251 return nvl(l_rsrc_cost,0);
252
253 END OPMCO_GET_RSRC_COST;
254
255
256 END opi_opm_common_pkg;
[Home] [Help]