1 PACKAGE BODY QA_ERES_PKG as
2 /* $Header: qaeresb.pls 115.2 2004/05/11 01:32:38 ilawler noship $ */
3
4 /* Cache of the last parsed transactionId */
5 x_last_transaction_id VARCHAR2(2000);
6 x_last_plan_id NUMBER;
7 x_last_collection_id NUMBER;
8 x_last_occurrence NUMBER;
9
10 /*
11 Private utility function for Collapse_Msg_Tokens. Locates the next
12 token name so we can expand it.
13 */
14 FUNCTION find_long_token(p_msg VARCHAR2) RETURN VARCHAR2 IS
15 i INTEGER;
16 j INTEGER;
17 BEGIN
18 i := instr(p_msg, SUFFIXSTRING);
19 j := instr(p_msg, '&', i-LENGTH(p_msg));
20 RETURN substr(p_msg, j, i-j);
21 END find_long_token;
22
23 /*
24 Private utility function for Collapse_Msg_Tokens. Expands a token
25 name to the long form for simple search-replace matching.
26 */
27 FUNCTION expanded_token_string (l_token VARCHAR2) RETURN VARCHAR2 IS
28 BEGIN
29 RETURN l_token || SUFFIXSTRING || '1' ||
30 l_token || SUFFIXSTRING || '2' ||
31 l_token || SUFFIXSTRING || '3' ||
32 l_token || SUFFIXSTRING || '4' ||
33 l_token || SUFFIXSTRING || '5' ||
34 l_token || SUFFIXSTRING || '6' ||
35 l_token || SUFFIXSTRING || '7' ||
36 l_token || SUFFIXSTRING || '8' ||
37 l_token || SUFFIXSTRING || '9' ||
38 l_token || SUFFIXSTRING || '10';
39 END expanded_token_string;
40
41 FUNCTION Collapse_Msg_Tokens (p_msg VARCHAR2) RETURN VARCHAR2 IS
42 l_msg VARCHAR2(4000);
43 tok VARCHAR2(200);
44 BEGIN
45 l_msg := p_msg;
46 LOOP
47 tok := find_long_token(l_msg);
48 EXIT WHEN tok IS NULL;
49 l_msg := replace(l_msg, expanded_token_string(tok), tok);
50 END LOOP;
51 RETURN l_msg;
52 END Collapse_Msg_Tokens;
53
54 FUNCTION get_category_name(p_category_id IN NUMBER, p_category_set_id IN NUMBER)
55 RETURN VARCHAR2
56 IS
57 x_name varchar2(240) := null;
58 CURSOR c IS
59 SELECT MCK.concatenated_segments
60 FROM mtl_categories_kfv MCK, MTL_CATEGORY_SETS_B CSET
61 WHERE CSET.CATEGORY_SET_ID = p_category_set_id AND
62 MCK.STRUCTURE_ID = CSET.STRUCTURE_ID AND
63 MCK.CATEGORY_ID = p_category_id;
64 BEGIN
65 OPEN c;
66 FETCH c INTO x_name;
67 CLOSE c;
68
69 RETURN x_name;
70 END;
71
72 FUNCTION get_category_desc(p_category_id IN NUMBER, p_category_set_id IN NUMBER)
73 RETURN VARCHAR2
74 IS
75 l_desc varchar2(240) := null;
76 CURSOR c IS
77 SELECT MCK.description
78 FROM mtl_categories_vl MCK, MTL_CATEGORY_SETS_B CSET
79 WHERE CSET.CATEGORY_SET_ID = p_category_set_id AND
80 MCK.STRUCTURE_ID = CSET.STRUCTURE_ID AND
81 MCK.CATEGORY_ID = p_category_id;
82 BEGIN
83 OPEN c;
84 FETCH c INTO l_desc;
85 CLOSE c;
86
87 RETURN l_desc;
88 END;
89
90 /*
91 Mon May 10 17:43:58 2004 - ilawler - bug #3599451
92
93 Private utility function for get_result_column_value. This is similar to
94 qa_core_pkg.get_result_column_name except it collapses the 3 cursors into one.
95
96 p_plan_id NUMBER => collection plan id
97 p_char_id NUMBER => collection element id
98
99 RETURNS VARCHAR2 name of the column in qa_results_full_v or NULL if char_id not
100 in the plan_id's definition.
101 */
102 FUNCTION get_result_column_name(p_plan_id IN NUMBER,
103 p_char_id IN NUMBER)
104 RETURN VARCHAR2
105 IS
106 CURSOR C1 (c_plan_id NUMBER, c_char_id NUMBER) IS
107 SELECT DECODE(qc.hardcoded_column, NULL, qpc.result_column_name, qc.developer_name) rescol
108 FROM QA_PLAN_CHARS qpc,
109 QA_CHARS qc
110 WHERE qc.char_id = qpc.char_id AND
111 qpc.plan_id = c_plan_id AND
112 qpc.char_id = c_char_id;
113
114 l_rescol VARCHAR2(2400);
115 BEGIN
116 OPEN C1(p_plan_id, p_char_id);
117 FETCH C1 INTO l_rescol;
118 IF C1%NOTFOUND THEN
119 CLOSE C1;
120 RETURN '';
121 ELSE
122 CLOSE C1;
123 END IF;
124
125 return l_rescol;
126 END;
127
128 FUNCTION get_result_column_value(p_plan_id IN NUMBER,
129 p_collection_id IN NUMBER,
130 p_occurrence IN NUMBER,
131 p_char_id IN NUMBER)
132 RETURN VARCHAR2
133 IS
134 l_rescol VARCHAR2(2400);
135 l_rescol_value VARCHAR2(4000);
136 l_stmt VARCHAR2(4000);
137 BEGIN
138 l_rescol := get_result_column_name(p_plan_id, p_char_id);
139
140 --now perform an execute immediate using this column
141 l_stmt := 'SELECT '||l_rescol||' FROM QA_RESULTS_FULL_V WHERE plan_id = :2 and collection_id = :3 and occurrence = :4';
142 EXECUTE IMMEDIATE l_stmt INTO l_rescol_value USING p_plan_id, p_collection_id, p_occurrence;
143 RETURN l_rescol_value;
144 END;
145
146
147 FUNCTION decode_wsh_released_status(p_source_code IN VARCHAR2,
148 p_released_status IN VARCHAR2,
149 p_released_status_name IN VARCHAR2,
150 p_inv_interfaced_flag IN VARCHAR2,
151 p_oe_interfaced_flag IN VARCHAR2)
152 RETURN VARCHAR2
153 IS
154 l_return_str WSH_DLVY_DELIVERABLES_V.RELEASED_STATUS_NAME%TYPE;
155 BEGIN
156 l_return_str := p_released_status_name;
157
158 IF (p_source_code = 'OE'
159 AND p_released_status = 'C'
160 AND p_oe_interfaced_flag = 'Y'
161 AND p_inv_interfaced_flag IN ('X','Y'))
162 OR
163 (p_source_code <> 'OE'
164 AND p_released_status = 'C'
165 AND p_inv_interfaced_flag = 'Y') THEN
166 BEGIN
167 SELECT meaning
168 INTO l_return_str
169 FROM wsh_lookups
170 WHERE lookup_type = 'PICK_STATUS'
171 AND lookup_code = 'I';
172 EXCEPTION
173 WHEN OTHERS THEN
174 l_return_str := NULL;
175 END;
176 END IF;
177
178 return l_return_str;
179 END;
180
181 FUNCTION decode_po_hazard_class(p_interface_transaction_id IN NUMBER)
182 RETURN VARCHAR2
183 IS
184 CURSOR C1 IS
185 SELECT DECODE(rt.source_document_code, 'RMA', MSI.HAZARD_CLASS_ID, NVL(POL.HAZARD_CLASS_ID, MSI.HAZARD_CLASS_ID)) HAZARD_CLASS_ID
186 FROM RCV_TRANSACTIONS_INTERFACE RT, MTL_SYSTEM_ITEMS MSI, PO_LINES_ALL POL
187 WHERE rt.interface_transaction_id = p_interface_transaction_id AND
188 rt.to_organization_id = MSI.ORGANIZATION_ID(+) AND
189 rt.item_id = MSI.INVENTORY_ITEM_ID(+) AND
190 rt.po_line_id = POL.PO_LINE_ID(+);
191
192 CURSOR C2 (p_hazard_class_id NUMBER) IS
193 SELECT HAZARD_CLASS
194 FROM PO_HAZARD_CLASSES_VL
195 WHERE HAZARD_CLASS_ID = p_hazard_class_id;
196
197 l_hazard_class_id NUMBER;
198 l_hazard_class PO_HAZARD_CLASSES_VL.HAZARD_CLASS%TYPE;
199 BEGIN
200 OPEN C1;
201 FETCH C1 INTO l_hazard_class_id;
202 IF C1%NOTFOUND OR l_hazard_class_id IS NULL THEN
203 CLOSE C1;
204 RETURN NULL;
205 ELSE
206 CLOSE C1;
207 END IF;
208
209 OPEN C2(l_hazard_class_id);
210 FETCH C2 INTO l_hazard_class;
211 IF C2%NOTFOUND THEN
212 CLOSE C2;
213 RETURN NULL;
214 ELSE
215 CLOSE C2;
216 END IF;
217
218 return l_hazard_class;
219
220 EXCEPTION
221 WHEN OTHERS THEN
222 RETURN NULL;
223 END;
224
225 FUNCTION decode_po_un_number(p_interface_transaction_id IN NUMBER)
226 RETURN VARCHAR2
227 IS
228 CURSOR C1 IS
229 SELECT DECODE(rt.source_document_code, 'RMA', MSI.UN_NUMBER_ID, NVL(POL.UN_NUMBER_ID, MSI.UN_NUMBER_ID)) UN_NUMBER_ID
230 FROM RCV_TRANSACTIONS_INTERFACE RT, MTL_SYSTEM_ITEMS MSI, PO_LINES_ALL POL
231 WHERE rt.interface_transaction_id = p_interface_transaction_id AND
232 rt.to_organization_id = MSI.ORGANIZATION_ID(+) AND
233 rt.item_id = MSI.INVENTORY_ITEM_ID(+) AND
234 rt.po_line_id = POL.PO_LINE_ID(+);
235
236 CURSOR C2 (p_un_number_id NUMBER) IS
237 SELECT UN_NUMBER
238 FROM PO_UN_NUMBERS_VL
239 WHERE UN_NUMBER_ID = p_un_number_id;
240
241 l_un_number_id NUMBER;
242 l_un_number PO_UN_NUMBERS_VL.UN_NUMBER%TYPE;
243 BEGIN
244 OPEN C1;
245 FETCH C1 INTO l_un_number_id;
246 IF C1%NOTFOUND OR l_un_number_id IS NULL THEN
247 CLOSE C1;
248 RETURN NULL;
249 ELSE
250 CLOSE C1;
251 END IF;
252
253 OPEN C2(l_un_number_id);
254 FETCH C2 INTO l_un_number;
255 IF C2%NOTFOUND THEN
256 CLOSE C2;
257 RETURN NULL;
258 ELSE
259 CLOSE C2;
260 END IF;
261
262 return l_un_number;
263
264 EXCEPTION
265 WHEN OTHERS THEN
266 RETURN NULL;
267 END;
268
269 FUNCTION get_result_column_value(p_transaction_id IN VARCHAR2,
270 p_char_id IN NUMBER)
271 RETURN VARCHAR2
272 IS
273 l_d_pos1 NUMBER;
274 l_d_pos2 NUMBER;
275 l_plan_id NUMBER;
276 l_collection_id NUMBER;
277 l_occurrence NUMBER;
278 l_rescol VARCHAR2(2400);
279 l_rescol_value VARCHAR2(4000);
280 l_stmt VARCHAR2(4000);
281 l_char_prompt VARCHAR2(200);
282 BEGIN
283 --see if the transaction_id's the same as the last one we parsed
284 IF p_transaction_id = x_last_transaction_id THEN
285 l_plan_id := x_last_plan_id;
286 l_collection_id := x_last_collection_id;
287 l_occurrence := x_last_occurrence;
288 ELSE
289 --parse the new transaction id
290 l_d_pos1 := instr(p_transaction_id, '-');
291 l_d_pos2 := instr(p_transaction_id, '-', l_d_pos1+1);
292 IF (l_d_pos1 > 0 AND l_d_pos2 > 0) THEN
293 l_plan_id := to_number(substr(p_transaction_id, 1, l_d_pos1-1));
294 l_collection_id := to_number(substr(p_transaction_id, l_d_pos1+1, l_d_pos2-l_d_pos1-1));
295 l_occurrence := to_number(substr(p_transaction_id, l_d_pos2+1));
296
297 --cache it for future calls - useful for multiple AME conditions
298 x_last_plan_id := l_plan_id;
299 x_last_collection_id := l_collection_id;
300 x_last_occurrence := l_occurrence;
301 x_last_transaction_id := p_transaction_id;
302 END IF;
303 END IF;
304
305 --obtain the name of the qa_results_full_v column
306 l_rescol := get_result_column_name(l_plan_id, p_char_id);
307
308 --now perform an execute immediate using this column if a column was found
309 IF l_rescol IS NOT NULL THEN
310 l_stmt := 'SELECT DISTINCT '||l_rescol||' FROM QA_RESULTS_FULL_V WHERE plan_id = :1 and collection_id = :2 and occurrence = NVL(:3, occurrence)';
311 EXECUTE IMMEDIATE l_stmt INTO l_rescol_value USING l_plan_id, l_collection_id, l_occurrence;
312 ELSE
313 l_rescol_value := '';
314 END IF;
315
316 RETURN l_rescol_value;
317
318 EXCEPTION
319 WHEN TOO_MANY_ROWS THEN
320 --lookup the char's prompt for the error message
321 l_char_prompt := qa_plan_element_api.get_prompt(l_plan_id, p_char_id);
322
323 fnd_message.set_name('QA', 'QA_ERES_AME_TOO_MANY_ROWS');
324 fnd_message.set_token('CHAR_PROMPT', l_char_prompt);
325 raise_application_error(-20001, fnd_message.get());
326 WHEN OTHERS THEN
327 RAISE;
328 --DEBUG:raise_application_error(-20001, 'GRCV, when others, char('||p_char_id||'), plan('||l_plan_id||'), col('||l_collection_id||'), occ('||l_occurrence||'), stmt:"'||l_stmt||'", sqlcode('||SQLCODE||'), sqlerrm:"'||SQLERRM||'"');
329 END;
330
331
332 END QA_ERES_PKG;
[Home] [Help]