[Home] [Help]
PACKAGE BODY: APPS.GMD_CREATE_FORMULA
Source
1 PACKAGE BODY GMD_CREATE_FORMULA AS
2 /* $Header: GMDPCFMB.pls 120.2 2005/09/23 06:49:11 txdaniel noship $ */
3 /* =============================================
4 Procedure:
5 Create_Formula
6
7 DESCRIPTION:
8 This PL/SQL procedure is responsible for
9 calling the formula API.
10
11 =============================================
12 */
13
14 PROCEDURE CREATE_FORMULA IS
15 formula_insert_table GMD_FORMULA_PUB.formula_insert_hdr_tbl_type;
16 form_count BINARY_INTEGER := 0;
17 form_handle UTL_FILE.FILE_TYPE;
18 line_out varchar2(2000);
19 rec_type varchar2(2000);
20 start_position NUMBER := 0;
21 end_position NUMBER := 1;
22 l_return_status varchar2(1);
23 x_msg_count NUMBER;
24 x_msg_data varchar2(240);
25 my_text varchar2(2000);
26 time_start BINARY_INTEGER;
27 time_load BINARY_INTEGER;
28 time_end BINARY_INTEGER;
29
30 l_end_of_line VARCHAR2(30);
31
32 BEGIN
33 time_start := DBMS_UTILITY.GET_TIME;
34
35 /* OPEN A TEXT FILE
36 PLEASE NOTE: my file exists in /sqlcom/log/opm115m
37 This needs to be changed !!!!!!
38 */
39
40 form_handle := UTL_FILE.FOPEN('/sqlcom/log/opm115g','MPGFM.csv','R');
41
42 LOOP
43 UTL_FILE.GET_LINE(form_handle,line_out);
44
45 form_count := form_count + 1;
46 start_position := 0;
47 end_position := 1;
48
49 IF (end_position <> 0) THEN
50 end_position := instr(line_out,',',start_position + 1,1);
51 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
52 formula_insert_table(form_count).record_type := rec_type;
53 start_position := end_position;
54 END IF;
55
56 IF (end_position <> 0) THEN
57 end_position := instr(line_out,',',start_position + 1,1);
58 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
59 formula_insert_table(form_count).formula_no := UPPER(rec_type);
60 start_position := end_position;
61 END IF;
62
63 IF (end_position <> 0) THEN
64 end_position := instr(line_out,',',start_position + 1,1);
65 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
66 formula_insert_table(form_count).formula_vers := to_number(rec_type);
67 start_position := end_position;
68 END IF;
69
70 IF (end_position <> 0) THEN
71 end_position := instr(line_out,',',start_position + 1,1);
72 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
73 formula_insert_table(form_count).formula_type := to_number(rec_type);
74 start_position := end_position;
75 END IF;
76
77 IF (end_position <> 0) THEN
78 end_position := instr(line_out,',',start_position + 1,1);
79 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
80 formula_insert_table(form_count).formula_desc1 := rec_type;
81 start_position := end_position;
82 END IF;
83
84 IF (end_position <> 0) THEN
85 end_position := instr(line_out,',',start_position + 1,1);
86 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
87 formula_insert_table(form_count).formula_desc2 := (rec_type);
88 start_position := end_position;
89 END IF;
90
91 IF (end_position <> 0) THEN
92 end_position := instr(line_out,',',start_position + 1,1);
93 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
94 formula_insert_table(form_count).formula_class := (rec_type);
95 start_position := end_position;
96 END IF;
97
98 IF (end_position <> 0) THEN
99 end_position := instr(line_out,',',start_position + 1,1);
100 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
101 formula_insert_table(form_count).fmcontrol_class := rec_type;
102 start_position := end_position;
103 END IF;
104
105 IF (end_position <> 0) THEN
106 end_position := instr(line_out,',',start_position + 1,1);
107 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
108 formula_insert_table(form_count).inactive_ind := to_number(rec_type);
109 start_position := end_position;
110 END IF;
111
112 IF (end_position <> 0) THEN
113 end_position := instr(line_out,',',start_position + 1,1);
114 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
115 formula_insert_table(form_count).formula_id := to_number(rec_type);
116 start_position := end_position;
117 END IF;
118
119 IF (end_position <> 0) THEN
120 end_position := instr(line_out,',',start_position + 1,1);
121 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
122 formula_insert_table(form_count).formulaline_id := to_number(rec_type);
123 start_position := end_position;
124 END IF;
125
126 IF (end_position <> 0) THEN
127 end_position := instr(line_out,',',start_position + 1,1);
128 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
129 formula_insert_table(form_count).line_type := to_number(rec_type);
130 start_position := end_position;
131 END IF;
132
133 IF (end_position <> 0) THEN
134 end_position := instr(line_out,',',start_position + 1,1);
135 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
136 formula_insert_table(form_count).line_no := to_number(rec_type);
137 start_position := end_position;
138 END IF;
139
140 IF (end_position <> 0) THEN
141 end_position := instr(line_out,',',start_position + 1,1);
142 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
143 formula_insert_table(form_count).item_no := UPPER(rec_type);
144 start_position := end_position;
145 END IF;
146
147 IF (end_position <> 0) THEN
148 end_position := instr(line_out,',',start_position + 1,1);
149 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
150 formula_insert_table(form_count).qty := to_number(rec_type);
151 start_position := end_position;
152 END IF;
153
154 IF (end_position <> 0) THEN
155 end_position := instr(line_out,',',start_position + 1,1);
156 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
157 formula_insert_table(form_count).detail_uom := (rec_type);
158 start_position := end_position;
159 END IF;
160
161 IF (end_position <> 0) THEN
162 end_position := instr(line_out,',',start_position + 1,1);
163 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
164 formula_insert_table(form_count).release_type := to_number(rec_type);
165 start_position := end_position;
166 END IF;
167
168 IF (end_position <> 0) THEN
169 end_position := instr(line_out,',',start_position + 1,1);
170 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
171 formula_insert_table(form_count).scrap_factor := to_number(rec_type);
172 start_position := end_position;
173 END IF;
174
175 IF (end_position <> 0) THEN
176 end_position := instr(line_out,',',start_position + 1,1);
177 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
178 formula_insert_table(form_count).scale_type_hdr := to_number(rec_type);
179 start_position := end_position;
180 END IF;
181 IF (end_position <> 0) THEN
182 end_position := instr(line_out,',',start_position + 1,1);
183 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
184 formula_insert_table(form_count).scale_type_dtl := to_number(rec_type);
185 start_position := end_position;
186 END IF;
187
188 IF (end_position <> 0) THEN
189 end_position := instr(line_out,',',start_position + 1,1);
190 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
191 formula_insert_table(form_count).cost_alloc := to_number(rec_type);
192 start_position := end_position;
193 END IF;
194
195 IF (end_position <> 0) THEN
196 end_position := instr(line_out,',',start_position + 1,1);
197 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
198 formula_insert_table(form_count).phantom_type := to_number(rec_type);
199 start_position := end_position;
200 END IF;
201
202 IF (end_position <> 0) THEN
203 end_position := instr(line_out,',',start_position + 1,1);
204 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
205 formula_insert_table(form_count).rework_type := to_number(rec_type);
206 start_position := end_position;
207 END IF;
208
209 IF (end_position <> 0) THEN
210 end_position := instr(line_out,',',start_position + 1,1);
211 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
212 formula_insert_table(form_count).owner_organization_id := (rec_type);
213 start_position := end_position;
214 END IF;
215
216 /* New fields added for Mini pack G - for fm_form_mst_b table*/
217 IF (end_position <> 0) THEN
218 end_position := instr(line_out,',',start_position + 1,1);
219 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
220 formula_insert_table(form_count).formula_status := (rec_type);
221 start_position := end_position;
222 END IF;
223
224 IF (end_position <> 0) THEN
225 end_position := instr(line_out,',',start_position + 1,1);
226 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
227 formula_insert_table(form_count).owner_id := to_number(rec_type);
228 start_position := end_position;
229 END IF;
230
231 /* New fields added for Mini pack G - for fm_matl_dtl */
232 IF (end_position <> 0) THEN
233 end_position := instr(line_out,',',start_position + 1,1);
234 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
235 formula_insert_table(form_count).TPFORMULA_ID := to_number(rec_type);
236 start_position := end_position;
237 END IF;
238
239 IF (end_position <> 0) THEN
240 end_position := instr(line_out,',',start_position + 1,1);
241 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
242 formula_insert_table(form_count).IAFORMULA_ID := to_number(rec_type);
243 start_position := end_position;
244 END IF;
245
246 IF (end_position <> 0) THEN
247 end_position := instr(line_out,',',start_position + 1,1);
248 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
249 formula_insert_table(form_count).CONTRIBUTE_STEP_QTY_IND := (rec_type);
250 start_position := end_position;
251 END IF;
252
253 IF (end_position <> 0) THEN
254 end_position := instr(line_out,',',start_position + 1,1);
255 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
256 formula_insert_table(form_count).user_name := rec_type;
257 start_position := end_position;
258 END IF;
259
260 IF (end_position <> 0) THEN
261 end_position := instr(line_out,',',start_position + 1,1);
262 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
263 formula_insert_table(form_count).created_by := to_number(rec_type);
264 start_position := end_position;
265 END IF;
266
267 IF (end_position <> 0) THEN
268 end_position := instr(line_out,',',start_position + 1,1);
269 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
270 formula_insert_table(form_count).last_updated_by := to_number(rec_type);
271 start_position := end_position;
272 END IF;
273
274 IF (end_position <> 0) THEN
275 end_position := instr(line_out,',',start_position + 1,1);
276 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
277 formula_insert_table(form_count).creation_date := (rec_type);
278 start_position := end_position;
279 END IF;
280
281 IF (end_position <> 0) THEN
282 end_position := instr(line_out,',',start_position + 1,1);
283 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
284 formula_insert_table(form_count).last_update_date := (rec_type);
285 start_position := end_position;
286 END IF;
287
288 IF (end_position <> 0) THEN
289 end_position := instr(line_out,',',start_position + 1,1);
290 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
291 formula_insert_table(form_count).text_code_hdr := (rec_type);
292 start_position := end_position;
293 END IF;
294
295 IF (end_position <> 0) THEN
296 end_position := instr(line_out,',',start_position + 1,1);
297 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
298 formula_insert_table(form_count).text_code_dtl := (rec_type);
299 start_position := end_position;
300 END IF;
301
302
303 /* Flexfields for formula header and detail */
304
305 IF (end_position <> 0) THEN
306 end_position := instr(line_out,',',start_position + 1,1);
307 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
308 formula_insert_table(form_count).attribute1 := rec_type;
309 start_position := end_position;
310 END IF;
311 IF (end_position <> 0) THEN
312 end_position := instr(line_out,',',start_position + 1,1);
313 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
314 formula_insert_table(form_count).attribute2 := rec_type;
315 start_position := end_position;
316 END IF;
317 IF (end_position <> 0) THEN
318 end_position := instr(line_out,',',start_position + 1,1);
319 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
320 formula_insert_table(form_count).attribute3 := rec_type;
321 start_position := end_position;
322 END IF;
323 IF (end_position <> 0) THEN
324 end_position := instr(line_out,',',start_position + 1,1);
325 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
326 formula_insert_table(form_count).attribute4 := rec_type;
327 start_position := end_position;
328 END IF;
329 IF (end_position <> 0) THEN
330 end_position := instr(line_out,',',start_position + 1,1);
331 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
332 formula_insert_table(form_count).attribute5 := rec_type;
333 start_position := end_position;
334 END IF;
338 formula_insert_table(form_count).attribute6 := rec_type;
335 IF (end_position <> 0) THEN
336 end_position := instr(line_out,',',start_position + 1,1);
337 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
339 start_position := end_position;
340 END IF;
341 IF (end_position <> 0) THEN
342 end_position := instr(line_out,',',start_position + 1,1);
343 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
344 formula_insert_table(form_count).attribute7 := rec_type;
345 start_position := end_position;
346 END IF;
347 IF (end_position <> 0) THEN
348 end_position := instr(line_out,',',start_position + 1,1);
349 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
350 formula_insert_table(form_count).attribute8 := rec_type;
351 start_position := end_position;
352 END IF;
353 IF (end_position <> 0) THEN
354 end_position := instr(line_out,',',start_position + 1,1);
355 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
356 formula_insert_table(form_count).attribute9 := rec_type;
357 start_position := end_position;
358 END IF;
359 IF (end_position <> 0) THEN
360 end_position := instr(line_out,',',start_position + 1,1);
361 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
362 formula_insert_table(form_count).attribute10 := rec_type;
363 start_position := end_position;
364 END IF;
365 IF (end_position <> 0) THEN
366 end_position := instr(line_out,',',start_position + 1,1);
367 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
368 formula_insert_table(form_count).attribute11 := rec_type;
369 start_position := end_position;
370 END IF;
371 IF (end_position <> 0) THEN
372 end_position := instr(line_out,',',start_position + 1,1);
373 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
374 formula_insert_table(form_count).attribute12 := rec_type;
375 start_position := end_position;
376 END IF;
377 IF (end_position <> 0) THEN
378 end_position := instr(line_out,',',start_position + 1,1);
379 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
380 formula_insert_table(form_count).attribute13 := rec_type;
381 start_position := end_position;
382 END IF;
383 IF (end_position <> 0) THEN
384 end_position := instr(line_out,',',start_position + 1,1);
385 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
386 formula_insert_table(form_count).attribute14 := rec_type;
387 start_position := end_position;
388 END IF;
389 IF (end_position <> 0) THEN
390 end_position := instr(line_out,',',start_position + 1,1);
391 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
392 formula_insert_table(form_count).attribute15 := rec_type;
393 start_position := end_position;
394 END IF;
395 IF (end_position <> 0) THEN
396 end_position := instr(line_out,',',start_position + 1,1);
397 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
398 formula_insert_table(form_count).attribute16 := rec_type;
399 start_position := end_position;
400 END IF;
401 IF (end_position <> 0) THEN
402 end_position := instr(line_out,',',start_position + 1,1);
403 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
404 formula_insert_table(form_count).attribute17 := rec_type;
405 start_position := end_position;
406 END IF;
407 IF (end_position <> 0) THEN
408 end_position := instr(line_out,',',start_position + 1,1);
409 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
410 formula_insert_table(form_count).attribute18 := rec_type;
411 start_position := end_position;
412 END IF;
413 IF (end_position <> 0) THEN
414 end_position := instr(line_out,',',start_position + 1,1);
415 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
416 formula_insert_table(form_count).attribute19 := rec_type;
417 start_position := end_position;
418 END IF;
419 IF (end_position <> 0) THEN
420 end_position := instr(line_out,',',start_position + 1,1);
421 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
422 formula_insert_table(form_count).attribute20 := rec_type;
423 start_position := end_position;
424 END IF;
425 IF (end_position <> 0) THEN
426 end_position := instr(line_out,',',start_position + 1,1);
427 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
428 formula_insert_table(form_count).attribute21 := rec_type;
429 start_position := end_position;
430 END IF;
431 IF (end_position <> 0) THEN
432 end_position := instr(line_out,',',start_position + 1,1);
433 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
434 formula_insert_table(form_count).attribute22 := rec_type;
435 start_position := end_position;
436 END IF;
437 IF (end_position <> 0) THEN
438 end_position := instr(line_out,',',start_position + 1,1);
439 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
440 formula_insert_table(form_count).attribute23 := rec_type;
441 start_position := end_position;
442 END IF;
443 IF (end_position <> 0) THEN
444 end_position := instr(line_out,',',start_position + 1,1);
445 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
446 formula_insert_table(form_count).attribute24 := rec_type;
447 start_position := end_position;
448 END IF;
449 IF (end_position <> 0) THEN
450 end_position := instr(line_out,',',start_position + 1,1);
451 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
452 formula_insert_table(form_count).attribute25 := rec_type;
453 start_position := end_position;
454 END IF;
455 IF (end_position <> 0) THEN
456 end_position := instr(line_out,',',start_position + 1,1);
457 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
458 formula_insert_table(form_count).attribute26 := rec_type;
459 start_position := end_position;
460 END IF;
461 IF (end_position <> 0) THEN
462 end_position := instr(line_out,',',start_position + 1,1);
463 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
464 formula_insert_table(form_count).attribute27 := rec_type;
465 start_position := end_position;
466 END IF;
467 IF (end_position <> 0) THEN
468 end_position := instr(line_out,',',start_position + 1,1);
469 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
470 formula_insert_table(form_count).attribute28 := rec_type;
471 start_position := end_position;
472 END IF;
473 IF (end_position <> 0) THEN
474 end_position := instr(line_out,',',start_position + 1,1);
475 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
476 formula_insert_table(form_count).attribute29 := rec_type;
477 start_position := end_position;
478 END IF;
479 IF (end_position <> 0) THEN
480 end_position := instr(line_out,',',start_position + 1,1);
481 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
482 formula_insert_table(form_count).attribute30 := rec_type;
483 start_position := end_position;
484 END IF;
485
486
487 IF (end_position <> 0) THEN
488 end_position := instr(line_out,',',start_position + 1,1);
489 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
490 formula_insert_table(form_count).dtl_attribute1 := rec_type;
491 start_position := end_position;
492 END IF;
493 IF (end_position <> 0) THEN
494 end_position := instr(line_out,',',start_position + 1,1);
495 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
496 formula_insert_table(form_count).dtl_attribute2 := rec_type;
497 start_position := end_position;
498 END IF;
499 IF (end_position <> 0) THEN
500 end_position := instr(line_out,',',start_position + 1,1);
501 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
502 formula_insert_table(form_count).dtl_attribute3 := rec_type;
503 start_position := end_position;
504 END IF;
505 IF (end_position <> 0) THEN
506 end_position := instr(line_out,',',start_position + 1,1);
507 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
508 formula_insert_table(form_count).dtl_attribute4 := rec_type;
509 start_position := end_position;
510 END IF;
511 IF (end_position <> 0) THEN
512 end_position := instr(line_out,',',start_position + 1,1);
513 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
514 formula_insert_table(form_count).dtl_attribute5 := rec_type;
515 start_position := end_position;
516 END IF;
517 IF (end_position <> 0) THEN
518 end_position := instr(line_out,',',start_position + 1,1);
519 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
520 formula_insert_table(form_count).dtl_attribute6 := rec_type;
521 start_position := end_position;
522 END IF;
523 IF (end_position <> 0) THEN
524 end_position := instr(line_out,',',start_position + 1,1);
525 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
526 formula_insert_table(form_count).dtl_attribute7 := rec_type;
527 start_position := end_position;
528 END IF;
529 IF (end_position <> 0) THEN
530 end_position := instr(line_out,',',start_position + 1,1);
531 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
532 formula_insert_table(form_count).dtl_attribute8 := rec_type;
533 start_position := end_position;
534 END IF;
535 IF (end_position <> 0) THEN
536 end_position := instr(line_out,',',start_position + 1,1);
537 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
538 formula_insert_table(form_count).dtl_attribute9 := rec_type;
539 start_position := end_position;
540 END IF;
541 IF (end_position <> 0) THEN
542 end_position := instr(line_out,',',start_position + 1,1);
543 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
544 formula_insert_table(form_count).dtl_attribute10 := rec_type;
545 start_position := end_position;
546 END IF;
547 IF (end_position <> 0) THEN
548 end_position := instr(line_out,',',start_position + 1,1);
549 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
550 formula_insert_table(form_count).dtl_attribute11 := rec_type;
551 start_position := end_position;
552 END IF;
553 IF (end_position <> 0) THEN
554 end_position := instr(line_out,',',start_position + 1,1);
555 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
556 formula_insert_table(form_count).dtl_attribute12 := rec_type;
557 start_position := end_position;
558 END IF;
559 IF (end_position <> 0) THEN
560 end_position := instr(line_out,',',start_position + 1,1);
561 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
562 formula_insert_table(form_count).dtl_attribute13 := rec_type;
563 start_position := end_position;
564 END IF;
565 IF (end_position <> 0) THEN
566 end_position := instr(line_out,',',start_position + 1,1);
570 END IF;
567 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
568 formula_insert_table(form_count).dtl_attribute14 := rec_type;
569 start_position := end_position;
571 IF (end_position <> 0) THEN
572 end_position := instr(line_out,',',start_position + 1,1);
573 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
574 formula_insert_table(form_count).dtl_attribute15 := rec_type;
575 start_position := end_position;
576 END IF;
577 IF (end_position <> 0) THEN
578 end_position := instr(line_out,',',start_position + 1,1);
579 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
580 formula_insert_table(form_count).dtl_attribute16 := rec_type;
581 start_position := end_position;
582 END IF;
583 IF (end_position <> 0) THEN
584 end_position := instr(line_out,',',start_position + 1,1);
585 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
586 formula_insert_table(form_count).dtl_attribute17 := rec_type;
587 start_position := end_position;
588 END IF;
589 IF (end_position <> 0) THEN
590 end_position := instr(line_out,',',start_position + 1,1);
591 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
592 formula_insert_table(form_count).dtl_attribute18 := rec_type;
593 start_position := end_position;
594 END IF;
595 IF (end_position <> 0) THEN
596 end_position := instr(line_out,',',start_position + 1,1);
597 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
598 formula_insert_table(form_count).dtl_attribute19 := rec_type;
599 start_position := end_position;
600 END IF;
601 IF (end_position <> 0) THEN
602 end_position := instr(line_out,',',start_position + 1,1);
603 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
604 formula_insert_table(form_count).dtl_attribute20 := rec_type;
605 start_position := end_position;
606 END IF;
607 IF (end_position <> 0) THEN
608 end_position := instr(line_out,',',start_position + 1,1);
609 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
610 formula_insert_table(form_count).dtl_attribute21 := rec_type;
611 start_position := end_position;
612 END IF;
613 IF (end_position <> 0) THEN
614 end_position := instr(line_out,',',start_position + 1,1);
615 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
616 formula_insert_table(form_count).dtl_attribute22 := rec_type;
617 start_position := end_position;
618 END IF;
619 IF (end_position <> 0) THEN
620 end_position := instr(line_out,',',start_position + 1,1);
621 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
622 formula_insert_table(form_count).dtl_attribute23 := rec_type;
623 start_position := end_position;
624 END IF;
625 IF (end_position <> 0) THEN
626 end_position := instr(line_out,',',start_position + 1,1);
627 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
628 formula_insert_table(form_count).dtl_attribute24 := rec_type;
629 start_position := end_position;
630 END IF;
631 IF (end_position <> 0) THEN
632 end_position := instr(line_out,',',start_position + 1,1);
633 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
634 formula_insert_table(form_count).dtl_attribute25 := rec_type;
635 start_position := end_position;
636 END IF;
637 IF (end_position <> 0) THEN
638 end_position := instr(line_out,',',start_position + 1,1);
639 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
640 formula_insert_table(form_count).dtl_attribute26 := rec_type;
641 start_position := end_position;
642 END IF;
643 IF (end_position <> 0) THEN
644 end_position := instr(line_out,',',start_position + 1,1);
645 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
646 formula_insert_table(form_count).dtl_attribute27 := rec_type;
647 start_position := end_position;
648 END IF;
649 IF (end_position <> 0) THEN
650 end_position := instr(line_out,',',start_position + 1,1);
651 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
652 formula_insert_table(form_count).dtl_attribute28 := rec_type;
653 start_position := end_position;
654 END IF;
655 IF (end_position <> 0) THEN
656 end_position := instr(line_out,',',start_position + 1,1);
657 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
658 formula_insert_table(form_count).dtl_attribute29 := rec_type;
659 start_position := end_position;
660 END IF;
661 IF (end_position <> 0) THEN
662 end_position := instr(line_out,',',start_position + 1,1);
663 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
664 formula_insert_table(form_count).dtl_attribute30 := rec_type;
665 start_position := end_position;
666 END IF;
667
668 IF (end_position <> 0) THEN
669 end_position := instr(line_out,',',start_position + 1,1);
670 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
671 formula_insert_table(form_count).attribute_category := rec_type;
672 start_position := end_position;
673 END IF;
674
675 IF (end_position <> 0) THEN
676 end_position := instr(line_out,',',start_position + 1,1);
677 rec_type := substr(line_out,start_position + 1,(end_position - start_position) - 1);
678 l_end_of_line := rec_type;
679 start_position := end_position;
680 END IF;
681
682
683 END LOOP;
684
685
686 EXCEPTION
687 WHEN no_data_found THEN
688 UTL_FILE.FCLOSE(form_handle);
689 time_load := DBMS_UTILITY.GET_TIME;
690
691 GMD_FORMULA_PUB.Insert_Formula
692 ( 1.0 ,
693 FND_API.G_FALSE ,
694 FND_API.G_TRUE ,
695 'YES' ,
696 l_return_status ,
697 x_msg_count ,
698 x_msg_data ,
699 formula_insert_table
700 );
701
702
703 time_end := DBMS_UTILITY.GET_TIME;
704
705 IF (l_return_status <> 'S') THEN
706 for i IN 1 .. x_msg_count LOOP
707 my_text := FND_MSG_PUB.get(i,x_msg_data);
708 /* dbms_output.put_line('The text is '||my_text); */
709 END LOOP;
710 END IF;
711
712
713 WHEN OTHERS
714 THEN
715 UTL_FILE.FCLOSE(form_handle);
716
717 END Create_Formula;
718 End GMD_CREATE_FORMULA;