1 PACKAGE BODY icx_store_batch_utils AS
2 /* $Header: ICXSTBUB.pls 115.1 99/07/17 03:26:02 porting shi $ */
3
4 -------------------------------------------------------------------
5 -- This procedure gets one line from the long string and trucates left the
6 -- the long string after retrieves the line.
7 -- The line is appended with the field separator.
8 -------------------------------------------------------------------
9 PROCEDURE get_line (p_text IN OUT LONG,
10 p_line IN OUT LONG) IS
11
12 l_position NUMBER := NULL;
13
14 BEGIN
15
16 IF p_text IS NULL THEN
17 p_line := NULL;
18 RETURN;
19 END IF;
20
21 l_position := INSTR(p_text, g_line_sep);
22
23 IF (l_position IS NULL) OR (l_position = 0 ) THEN
24 p_line := p_text;
25 p_text := NULL;
26 RETURN;
27 END IF;
28
29 p_line := substr(p_text, 1, l_position - 1);
30 p_text := substr(p_text, l_position + LENGTH(g_line_sep));
31
32 -- Append the field separator, so the get_field procedure can
33 -- retrieve the last filed
34 p_line := p_line || g_field_sep;
35
36 EXCEPTION
37 WHEN OTHERS THEN
38 htp.p('Error in get_line : ' || substr(SQLERRM, 1, 512));
39 -- icx_util.add_error(substr(SQLERRM, 12, 512));
40 -- icx_util.error_page_print;
41 END get_line;
42
43
44 -------------------------------------------------------------------
45 -- This procedure gets one field from the long string line and removes
46 -- the field from the line.
47 -------------------------------------------------------------------
48 PROCEDURE get_field (p_line IN OUT LONG,
49 p_field OUT LONG) IS
50
51 l_position NUMBER := NULL;
52
53 BEGIN
54
55 IF p_line IS NULL THEN
56 p_field := NULL;
57 RETURN;
58 END IF;
59
60 l_position := INSTR(p_line, g_field_sep);
61
62 IF (l_position IS NULL) OR (l_position = 0 ) THEN
63 p_field := p_line;
64 p_line := NULL;
65 RETURN;
66 END IF;
67
68 p_field := substr(p_line, 1, l_position - 1);
69 p_line := substr(p_line, l_position + LENGTH(g_field_sep));
70
71
72 EXCEPTION
73 WHEN OTHERS THEN
74 htp.p('Error in get_field : ' || substr(SQLERRM, 1, 512));
75 -- icx_util.add_error(substr(SQLERRM, 12, 512));
76 -- icx_util.error_page_print;
77 END get_field;
78
79 -----------------------------------------------------------
80 PROCEDURE get_prompts_table (p_region_code IN VARCHAR2,
81 p_prompts_table OUT item_prompts_table) IS
82
83 CURSOR item_prompts IS
84 SELECT ATTRIBUTE_LABEL_LONG,ATTRIBUTE_CODE, DISPLAY_VALUE_LENGTH
85 FROM AK_REGION_ITEMS_VL
86 WHERE REGION_CODE = p_region_code
87 AND (NODE_DISPLAY_FLAG = 'Y'
88 OR ATTRIBUTE_CODE = 'ICX_INVENTORY_ITEM_ID')
89 ORDER BY DISPLAY_SEQUENCE;
90
91 i NUMBER := NULL;
92 BEGIN
93
94 i := 1;
95 OPEN item_prompts;
96 LOOP
97 FETCH item_prompts INTO p_prompts_table(i).attribute_label,
98 p_prompts_table(i).attribute_code,
99 p_prompts_table(i).display_value_length;
100 EXIT WHEN item_prompts%NOTFOUND;
101 i := i + 1;
102 END LOOP;
103
104 EXCEPTION
105 WHEN OTHERS THEN
106 htp.p('Error in get_prompts_table : ' || substr(SQLERRM, 1, 512));
107 -- icx_util.add_error(substr(SQLERRM, 12, 512));
108 -- icx_util.error_page_print;
109 END get_prompts_table;
110
111 ----------------------------------------------------------
112 -- This procedure returns 'S' when the p_filed string is successfully
113 -- converted to the given date format or p_field is NULL.
114 -- Returns 'E' when the conversion fails
115 ----------------------------------------------------------
116 PROCEDURE convert_to_date (p_field IN VARCHAR2,
117 p_date_format IN VARCHAR2,
118 p_date OUT DATE,
119 p_error_flag OUT VARCHAR2)
120 IS
121
122 BEGIN
123
124 IF p_field IS NOT NULL THEN
125 p_date := TO_DATE(p_field, p_date_format);
126 p_error_flag := 'S';
127 ELSE
128 p_date := NULL;
129 p_error_flag := 'S';
130 END IF;
131 RETURN;
132
133 EXCEPTION
134 WHEN OTHERS THEN
135 p_date := NULL;
136 p_error_flag := 'E';
137 -- htp.p('Error in convert_to_date : ' || substr(SQLERRM, 1, 512));
138 -- icx_util.add_error(substr(SQLERRM, 12, 512));
139 -- icx_util.error_page_print;
140 END convert_to_date;
141
142
143 -----------------------------------------------------------
144 -- This procedure returns the attributes of a given prompt in the region.
145 -----------------------------------------------------------
146 PROCEDURE get_prompts_info (p_prompts_table IN item_prompts_table,
147 p_attribute_code IN VARCHAR2,
148 p_attribute_info_record OUT prompt_rec)
149 IS
150
151 BEGIN
152
153 IF p_prompts_table.COUNT = 0 THEN
154 p_attribute_info_record := NULL;
155 RETURN;
156 END IF;
157 FOR i IN p_prompts_table.FIRST..p_prompts_table.LAST LOOP
158
159 IF p_prompts_table(i).attribute_code = p_attribute_code THEN
160 p_attribute_info_record.attribute_label := p_prompts_table(i).attribute_label;
161 p_attribute_info_record.attribute_code := p_prompts_table(i).attribute_code;
162 p_attribute_info_record.display_value_length := p_prompts_table(i).display_value_length;
163
164 RETURN;
165 END IF; -- IF p_prompts_table(i)...
166
167 END LOOP; -- FOR i IN p_prompts_table
168 -- No match found
169 p_attribute_info_record := NULL;
170 RETURN;
174 htp.p('Error in get_prompts_info : ' || substr(SQLERRM, 1, 512));
171
172 EXCEPTION
173 WHEN OTHERS THEN
175 -- icx_util.add_error(substr(SQLERRM, 12, 512));
176 -- icx_util.error_page_print;
177 END get_prompts_info;
178
179
180 ---------------------------------------------------------------
181 -- Function replaces crlf with '\n' and returns the string
182 ---------------------------------------------------------------
183 FUNCTION replace_crlf (p_string IN VARCHAR2)
184 RETURN VARCHAR2 IS
185
186 l_string VARCHAR2(2000) := NULL;
187
188 BEGIN
189
190 -- Replace the CR/LF with \n
191 l_string := REPLACE(p_string, C_CR_LF, '\n');
192
193 RETURN l_string;
194
195 EXCEPTION
196 WHEN OTHERS THEN
197 htp.p('Error in replace_crlf: ' || substr(SQLERRM, 1, 512));
198 -- icx_util.add_error(substr(SQLERRM, 12, 512));
199 -- icx_util.error_page_print;
200 END replace_crlf;
201
202 END icx_store_batch_utils;
[Home] [Help]