[Home] [Help]
PACKAGE BODY: APPS.EGO_DEMO_PUB
Source
1 package body EGO_DEMO_PUB AS
2 /*$Header: EGODEMOB.pls 120.1 2007/05/09 15:03:40 dsakalle noship $ */
3 ----------------------------------------------------------------------------
4 -- A.Calculate_Grade
5 ----------------------------------------------------------------------------
6 FUNCTION Calculate_Grade (
7 p_component_risk IN NUMBER
8 , p_lead_time IN NUMBER
9 , p_cost IN NUMBER
10 , p_supplier_risk IN NUMBER
11 )
12 RETURN NUMBER
13 IS
14
15 p_grade NUMBER :=10;
16
17 BEGIN
18
19 if(p_cost > 0) then
20 if (p_cost > 10 and p_cost <= 20) then
21 p_grade := p_grade - 1.15;
22 elsif (p_cost > 20 and p_cost <= 50) then
23 p_grade := p_grade - 2.38;
24 else
25 p_grade := p_grade - 3.01;
26 end if;
27 end if;
28
29 if (p_component_risk > 0) then
30 if (p_component_risk > 1 AND p_component_risk <= 3) then
31 p_grade := p_grade - .95;
32 elsif (p_component_risk > 3 and p_component_risk <= 5) then
33 p_grade := p_grade - 1.07;
34 else
35 p_grade := p_grade - 1.93;
36 end if;
37 end if;
38
39 if (p_lead_time > 0 ) then
40 if (p_lead_time > 10 AND p_lead_time <= 20) then
41 p_grade := p_grade - .55;
42 elsif (p_lead_time > 20 and p_lead_time <= 45) then
43 p_grade := p_grade - 1.35;
44 else
45 p_grade := p_grade - 2.06;
46 end if;
47 end if;
48
49 if (p_supplier_risk > 0) then
50 if (p_supplier_risk > 1 AND p_supplier_risk <= 2) then
51 p_grade := p_grade - .70;
52 elsif (p_supplier_risk > 2 and p_supplier_risk <= 5) then
53 p_grade := p_grade - 1.14;
54 else
55 p_grade := p_grade - 2.01;
56 end if;
57 end if;
58 p_grade := round(p_grade);
59 return p_grade;
60 EXCEPTION
61 WHEN OTHERS THEN
62 NULL;
63
64 END Calculate_Grade;
65
66 ----------------------------------------------------------------------------
67
68 ---Generate_Item_Number
69
70 ----------------------------------------------------------------------------
71
72 FUNCTION Generate_Item_Number (
73 p_Section_Code IN VARCHAR2
74 , p_Model_Code IN VARCHAR2
75 , p_Prototype_Code IN VARCHAR2
76 )
77 RETURN VARCHAR2
78 IS
79
80 l_item_number VARCHAR2(30) := '';
81 l_item_squence VARCHAR2(30) := '';
82
83 BEGIN
84
85 SELECT EGO_DEMO_ITEM_NUMBER_S.NEXTVAL
86 INTO l_item_squence
87 FROM DUAL;
88
89 l_item_number := p_Section_Code || l_item_squence || ' ' || p_Model_Code || p_Prototype_Code || ' ' || '0000';
90
91 return l_item_number;
92
93 EXCEPTION
94 WHEN OTHERS THEN
95 NULL;
96
97 END Generate_Item_Number;
98
99 ----------------------------------------------------------------------------
100
101 ---Generate_Item_Desc
102
103 ----------------------------------------------------------------------------
104
105 FUNCTION Generate_Item_Desc (
106 p_Section_Code IN VARCHAR2
107 , p_Model_Code IN VARCHAR2
108 , p_Product_Line IN VARCHAR2
109 )
110 RETURN VARCHAR2
111 IS
112
113 l_item_desc VARCHAR2(30) := '';
114
115
116 BEGIN
117
118 l_item_desc := p_Product_Line || '.' || p_Model_Code || '.' || p_Section_Code;
119
120 return l_item_desc;
121
122 EXCEPTION
123 WHEN OTHERS THEN
124 NULL;
125
126 END Generate_Item_Desc;
127
128
129 ----------------------------------------------------------------------------
130
131 PROCEDURE Calculate_Weightage (
132 p_param1 IN VARCHAR2
133 , p_param2 IN VARCHAR2
134 , p_param3 IN VARCHAR2
135 , p_result1 IN OUT NOCOPY NUMBER
136 , p_result2 IN OUT NOCOPY NUMBER
137 , p_result3 IN OUT NOCOPY NUMBER
138 )
139 IS
140 p_param1_num NUMBER :=0;
141 p_param2_num NUMBER :=0;
142 p_param3_num NUMBER :=0;
143
144 BEGIN
145
146 if(p_param1 = 'High') then
147 p_param1_num := 3;
148 elsif (p_param1 = 'Medium') then
149 p_param1_num := 2;
150 elsif(p_param1 = 'Low') then
151 p_param1_num := 1;
152 end if;
153
154 if(p_param2 = 'High') then
155 p_param2_num := 3;
156 elsif (p_param2 = 'Medium') then
157 p_param2_num := 2;
158 elsif(p_param2 = 'Low') then
159 p_param2_num := 1;
160 end if;
161
162 if(p_param3 = 'High') then
163 p_param3_num := 3;
164 elsif (p_param3 = 'Medium') then
165 p_param3_num := 2;
166 elsif(p_param3 = 'Low') then
167 p_param3_num := 1;
168 end if;
169
170 p_result1 := round((0.5*p_param1_num + 0.25*p_param2_num + 0.25*p_param3_num),2);
171 p_result2 := round((0.25*p_param1_num + 0.5*p_param2_num + 0.25*p_param3_num),2);
172 p_result3 := round((0.25*p_param1_num + 0.25*p_param2_num + 0.5*p_param3_num),2);
173
174
175 EXCEPTION
176 WHEN OTHERS THEN
177 NULL;
178
179 END Calculate_Weightage;
180
181 ----------------------------------------------------------------------------------
182 -- GenCapacitorItemDesc
183 ----------------------------------------------------------------------------------
184 FUNCTION GenCapacitorItemDesc (
185 p1 IN VARCHAR2
186 , p2 IN NUMBER
187 , p3 IN NUMBER
188 , p4 IN NUMBER
189 , p5 IN VARCHAR2
190 , p6 IN VARCHAR2
191 , p7 IN VARCHAR2
192 )
193 RETURN VARCHAR2
194 IS
195
196 l_ItemDesc VARCHAR2(300) := '' ; -- Description
197 l_Del CONSTANT VARCHAR2(2) := ','; -- Deliminator
198
199 BEGIN
200
201 l_ItemDesc := 'Capacitor' || l_Del || p1 || l_Del || p2 ||'uF' || l_Del || p3 || 'V' || l_Del || p4 ||'%' || l_Del || p5 || l_Del || p6 || l_Del || p7;
202
203 RETURN l_ItemDesc ;
204
205 EXCEPTION
206 WHEN OTHERS THEN
207 NULL;
208
209 END GenCapacitorItemDesc;
210
211 -----------------------------------------------------------------------------------
212 -- ClassifyECO
213 -----------------------------------------------------------------------------------
214
215 PROCEDURE ClassifyECO (
216 pA1 IN VARCHAR2
217 , pB1 IN VARCHAR2
218 , pB2 IN VARCHAR2
219 , pB3 IN VARCHAR2
220 , pChangeId IN NUMBER
221 )
222 IS
223 l_class_code VARCHAR2(80);
224 l_class_code_id NUMBER;
225 BEGIN
226
227 IF(pA1 = 'N') THEN
228 l_class_code := 'Class A';
229 ELSIF (pB1 = 'Y' OR pB2 = 'Y' OR pB3 = 'Y') THEN
230 l_class_code := 'Class B';
231 ELSE
232 l_class_code := 'Class C';
233 END IF;
234
235 SELECT classification_id
236 INTO l_class_code_id
237 FROM eng_change_classifications_vl
238 WHERE classification_name = l_class_code;
239
240 IF l_class_code_id is not null
241 THEN
242
243 UPDATE eng_engineering_changes
244 SET classification_id = l_class_code_id
245 where change_id = pChangeId;
246
247 commit;
248
249 END IF;
250
251 EXCEPTION
252 WHEN OTHERS THEN
253 NULL;
254
255 END ClassifyECO;
256
257 ------------------------------------------------------------------------------
258
259 FUNCTION Gen_Item_Num_With_Key_Attrs( p_Section_Code IN VARCHAR2
260 ,p_Model_Code IN VARCHAR2
261 ,p_Prototype_Code IN VARCHAR2
262 ,p_col_name_value_array IN EGO_COL_NAME_VALUE_PAIR_ARRAY
263 )
264 RETURN VARCHAR2
265 IS
266 l_item_number VARCHAR2(300);
267 l_item_squence VARCHAR2(300);
268 l_style_item_id NUMBER;
269 l_icc_id NUMBER;
270 l_item_type VARCHAR2(1000);
271 l_ego_col_name_value_pair_obj EGO_COL_NAME_VALUE_PAIR_OBJ;
272 l_style_item_num MTL_SYSTEM_ITEMS_KFV.CONCATENATED_SEGMENTS%TYPE;
273 BEGIN
274 SELECT EGO_DEMO_ITEM_NUMBER_S.NEXTVAL
275 INTO l_item_squence
276 FROM DUAL;
277
278 IF p_col_name_value_array IS NOT NULL THEN
279 FOR l_row_table_index IN p_col_name_value_array.first .. p_col_name_value_array.last
280 LOOP
281 l_ego_col_name_value_pair_obj := p_col_name_value_array(l_row_table_index);
282 IF l_ego_col_name_value_pair_obj.NAME = 'STYLE_ITEM_ID' THEN
283 l_style_item_id := l_ego_col_name_value_pair_obj.VALUE;
284 ELSIF l_ego_col_name_value_pair_obj.NAME = 'ITEM_TYPE' THEN
285 l_item_type := l_ego_col_name_value_pair_obj.VALUE;
286 ELSIF l_ego_col_name_value_pair_obj.NAME = 'ITEM_CATALOG_GROUP_ID' THEN
287 l_icc_id := l_ego_col_name_value_pair_obj.VALUE;
288 END IF;
289 END LOOP;
290 END IF;
291
292 IF l_style_item_id IS NOT NULL THEN
293 BEGIN
294 SELECT SUBSTR(CONCATENATED_SEGMENTS, 1, 20) INTO l_style_item_num
295 FROM MTL_SYSTEM_ITEMS_KFV
296 WHERE INVENTORY_ITEM_ID = l_style_item_id
297 AND ROWNUM = 1;
298 EXCEPTION WHEN OTHERS THEN
299 l_style_item_num := 'EXCEPTION';
300 END;
301 l_item_number := l_style_item_num || '-' || l_item_squence;
302 ELSE
303 l_item_number := 'ICC-' || l_icc_id || '-ITYPE-' || l_item_type || '-' || l_item_squence;
304 END IF;
305
306 RETURN l_item_number;
307 EXCEPTION WHEN OTHERS THEN
308 RETURN l_item_number;
309 END Gen_Item_Num_With_Key_Attrs;
310
311
312 END EGO_DEMO_PUB;