1 package body qa_ss_core as
2 /* $Header: qltsscob.plb 115.28 2002/11/27 19:31:45 jezheng ship $ */
3
4 /*
5
6 -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
7 -- CHANGE RECORD
8 -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
9 1. Sep 13, 99 Mandatory field validation for context elements removed
10 Change done because, context element could be null, and then
11 the user cannot save the data to the plan
12 Talked to Manish about this, and felt this may be better to do
13 PROCEDURE enter_results altered (BUG 998445)
14
15 2. Bug 977968
16 Function Evaluate_triggers removed unnecessary to_char
17
18 3. Added the HiddenRSMDF form to the eqr webpage (procedure draw_table)
19
20 4. Fixing Bug 995239. Modify procedure VQR_Frames and draw_frames
21 Add a local variable l_plan_name. Use a cursor to fetch this
22 and use it in the call to pos_upper_banner_sv.PaintUpperBanner
23 (Minor performance issue: dont query fnd_messages in pos pkg if not needed.
24 Ayeung is the PO contact for the above).
25
26 5. Bug 999521 fix: Procedure list_plans modified
27 Private func Is_Job_Valid added
28 6. Bug 998381: to make send notification work without clicking back button
29 I am going to add arguments to the procedure enter_results, and also
30 have a hidden html form in that procedure.
31 Changes to the procedure draw_table is the elements of the html
32 form WORKFLOWDOC are also going to be made hidden elements of
33 html form RSMDF. This is done so they will now be transmitted
34 to the enter_results procedure.
35 7. Removing hardcoded US and using language variable
36 8. Also remove US from OA_MEDIA/US and make it OA_MEDIA
37 -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
38 -- END CHANGE RECORD
39 -- ^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^^
40 */
41
42 -- PRIVATE FUNCTIONS AND PROCEDURES BELOW --
43 function get_char_prompt(X_char_id IN NUMBER)
44 return VARCHAR2
45 IS
46 l_prompt VARCHAR2(50);
47
48 CURSOR c IS
49 Select prompt
50 From QA_CHARS
51 WHERE char_id = X_char_id;
52
53 BEGIN
54 OPEN c;
55 FETCH c INTO l_prompt;
56 CLOSE c;
57
58 RETURN l_prompt;
59 EXCEPTION
60 WHEN OTHERS THEN
61 IF c%ISOPEN THEN
62 CLOSE c;
63 END IF;
64 htp.p('Exception in private function qa_ss_core.get_char_prompt');
65 htp.p(SQLERRM);
66
67 END get_char_prompt;
68 -----------------------------------------------------------------
69 function Is_Job_Valid (X_Po_Dist_Id IN NUMBER,
70 X_Wip_Entity_Id IN NUMBER)
71 Return BOOLEAN
72 IS
73 l_ent_type NUMBER;
74 l_dummy_var NUMBER;
75 valid_ok BOOLEAN := TRUE;
76 CURSOR we_type_cur IS
77 SELECT entity_type
78 FROM WIP_ENTITIES
79 WHERE WIP_ENTITY_ID = X_Wip_Entity_Id;
80
81 -- #2382432
82 -- Changed the view to WIP_DISCRETE_JOBS_ALL_V instead of
83 -- earlier wip_open_discrete_jobs_val_v
84 -- rkunchal Sun Jun 30 22:59:11 PDT 2002
85
86 CURSOR wdj_open_val_cur IS
87 SELECT wip_entity_id
88 FROM WIP_DISCRETE_JOBS_ALL_V
89 WHERE WIP_ENTITY_ID = X_Wip_Entity_Id;
90
91 BEGIN
92 OPEN we_type_cur;
93 FETCH we_type_cur INTO l_ent_type;
94 CLOSE we_type_cur;
95 IF (l_ent_type = 3) THEN -- closed discrete job
96 valid_ok := FALSE; -- not valid for eqr
97 END IF;
98 IF (l_ent_type = 1) THEN -- open disc job;need more eval
99 OPEN wdj_open_val_cur;
100 FETCH wdj_open_val_cur INTO l_dummy_var;
101 IF wdj_open_val_cur%FOUND THEN
102 valid_ok := TRUE;
103 ELSE
104 valid_ok := FALSE;
105 -- set to FALSE only if l_ent_type = 1
106 -- AND not in WIP_OPEN_DISCRETE_JOBS_VAL_V
107 END IF;
108 CLOSE wdj_open_val_cur;
109 END IF;
110 RETURN valid_ok; -- variable initial value is TRUE
111
112 EXCEPTION
113 WHEN OTHERS THEN
114 IF we_type_cur%ISOPEN THEN
115 CLOSE we_type_cur;
116 END IF;
117 IF wdj_open_val_cur%ISOPEN THEN
118 CLOSE wdj_open_val_cur;
119 END IF;
120 htp.p('Exception in private function qa_ss_core.Is_Job_Valid');
121 htp.p(SQLERRM);
122
123 END Is_Job_Valid;
124
125
126 FUNCTION get_item_id (x_org_id number, x_item VARCHAR2) return NUMBER is
127
128 id NUMBER;
129
130 BEGIN
131
132 --
133 -- Bug 2672398. The original SQL here is too costly.
134 -- We should simply use the qa_flex_util package. It
135 -- has an identical function
136 -- bso Mon Nov 25 19:06:05 PST 2002
137 --
138 return qa_flex_util.get_item_id(x_org_id, x_item);
139 END;
140
141
142 --Following copied from qltutlfb.plb and added as private here
143 --to avoid dependency (by Ilam)
144 -- The following function is added to make item_category a collection trigger
145 -- It can be called from mobile or self service.
146 -- In mobile, the user will have item_value and not the id but in self service
147 -- item_id is available. To ensure the scalability, this function takes in
148 -- both, item_value as well as item_id. It calls get_item_id to get item_id
149 -- based upon item_value and then uses this to get item_category.
150 -- anagarwa Tue Sep 18 16:19:08 PDT 2001
151
152 PROCEDURE get_item_category_val (p_org_id NUMBER,
153 p_item_val VARCHAR2 default null,
154 p_item_id NUMBER default null,
155 x_category_val OUT NOCOPY VARCHAR2,
156 x_category_id OUT NOCOPY NUMBER) IS
157
158 l_item_id NUMBER;
159 l_category_set_id NUMBER;
160 l_category_val VARCHAR2(1000) := NULL;
161 l_category_id NUMBER := NULL;
162
163
164 CURSOR category_cur(p_org_id NUMBER, p_item_id NUMBER, p_category_set_id NUMBER) IS
165 select mck.concatenated_segments,
166 mck.category_id
167 from mtl_item_categories mic, mtl_categories_kfv mck
168 where mic.organization_id = p_org_id
169 and mic.category_id = mck.category_id
170 and mic.inventory_item_id =p_item_id
171 and mic.category_set_id = p_category_set_id;
172
173 BEGIN
174 -- org_id should never be null
175 IF (p_org_id IS NULL) THEN
176 RETURN ;
177 END IF;
178
179 -- if calling from mobile, then get_item_id
180 IF ((p_item_id <0 OR p_item_id IS NULL) AND (p_item_val IS NOT NULL))
181 THEN
182 l_item_id := get_item_id(p_org_id, p_item_val);
183 ELSE
184 l_item_id := p_item_id;
185 END IF;
186
187 l_category_set_id := FND_PROFILE.VALUE('QA_CATEGORY_SET');
188
189 OPEN category_cur( p_org_id, l_item_id, l_category_set_id);
190
191 FETCH category_cur INTO l_category_val, l_category_id;
192
193 CLOSE category_cur;
194
195 --if cursor did not fetch any rows
196 --then l_category_val and l_category_id will have
197 --initialized NULL values
198 --Do Not Raise Exception Here
199 --
200 x_category_val := l_category_val;
201 x_category_id := l_category_id;
202
203 RETURN;
204
205 END;
206
207
208 --------------------------------------------------------------------------------
209 --
210 -- PUBLIC PROCEDURES AND FUNCTIONS BELOW
211 --
212 --------------------------------------------------------------------------------
213 procedure enter_results(
214 selectbox IN qa_ss_const.var30_table DEFAULT qa_ss_const.def30_tab,
215 p_col1 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
216 p_col2 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
217 p_col3 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
218 p_col4 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
219 p_col5 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
220 p_col6 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
221 p_col7 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
222 p_col8 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
223 p_col9 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
224 p_col10 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
225 p_col11 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
226 p_col12 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
227 p_col13 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
228 p_col14 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
229 p_col15 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
230 p_col16 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
231 p_col17 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
232 p_col18 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
233 p_col19 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
234 p_col20 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
235 p_col21 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
236 p_col22 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
237 p_col23 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
238 p_col24 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
239 p_col25 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
240 p_col26 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
241 p_col27 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
242 p_col28 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
243 p_col29 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
244 p_col30 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
245 p_col31 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
246 p_col32 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
247 p_col33 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
248 p_col34 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
249 p_col35 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
250 p_col36 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
251 p_col37 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
252 p_col38 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
253 p_col39 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
254 p_col40 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
255 p_col41 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
256 p_col42 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
257 p_col43 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
258 p_col44 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
259 p_col45 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
260 p_col46 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
261 p_col47 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
262 p_col48 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
263 p_col49 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
264 p_col50 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
265 p_col51 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
266 p_col52 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
267 p_col53 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
268 p_col54 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
269 p_col55 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
270 p_col56 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
271 p_col57 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
272 p_col58 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
273 p_col59 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
274 p_col60 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
275 p_col61 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
276 p_col62 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
277 p_col63 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
278 p_col64 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
279 p_col65 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
280 p_col66 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
281 p_col67 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
282 p_col68 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
283 p_col69 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
284 p_col70 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
285 p_col71 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
286 p_col72 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
287 p_col73 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
288 p_col74 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
289 p_col75 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
290 p_col76 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
291 p_col77 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
292 p_col78 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
293 p_col79 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
294 p_col80 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
295 p_col81 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
296 p_col82 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
297 p_col83 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
298 p_col84 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
299 p_col85 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
300 p_col86 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
301 p_col87 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
302 p_col88 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
303 p_col89 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
304 p_col90 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
305 p_col91 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
306 p_col92 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
307 p_col93 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
308 p_col94 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
309 p_col95 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
310 p_col96 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
311 p_col97 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
312 p_col98 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
313 p_col99 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
314 p_col100 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
315 p_col101 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
316 p_col102 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
317 p_col103 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
318 p_col104 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
319 p_col105 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
320 p_col106 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
321 p_col107 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
322 p_col108 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
323 p_col109 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
324 p_col110 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
325 p_col111 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
326 p_col112 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
327 p_col113 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
328 p_col114 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
329 p_col115 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
330 p_col116 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
331 p_col117 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
332 p_col118 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
333 p_col119 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
334 p_col120 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
335 p_col121 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
336 p_col122 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
337 p_col123 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
338 p_col124 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
339 p_col125 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
340 p_col126 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
341 p_col127 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
342 p_col128 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
343 p_col129 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
344 p_col130 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
345 p_col131 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
346 p_col132 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
347 p_col133 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
348 p_col134 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
349 p_col135 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
350 p_col136 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
351 p_col137 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
352 p_col138 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
353 p_col139 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
354 p_col140 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
355 p_col141 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
356 p_col142 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
357 p_col143 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
358 p_col144 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
359 p_col145 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
360 p_col146 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
361 p_col147 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
362 p_col148 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
363 p_col149 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
364 p_col150 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
365 p_col151 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
366 p_col152 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
367 p_col153 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
368 p_col154 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
369 p_col155 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
370 p_col156 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
371 p_col157 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
372 p_col158 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
373 p_col159 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
374 p_col160 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
375 hid_planid IN NUMBER DEFAULT NULL,
376 orgz_id IN NUMBER DEFAULT NULL,
377 txn_num IN NUMBER DEFAULT NULL,
378 po_agent_id IN NUMBER DEFAULT NULL,
379 x_source_id IN NUMBER DEFAULT NULL,
380 x_item_id IN NUMBER DEFAULT NULL,
381 x_po_header_id IN NUMBER DEFAULT NULL)
382 IS
383 orgz_ex EXCEPTION;
384 plan_ex EXCEPTION;
385 col_count NUMBER;
386 No_problem BOOLEAN;
387 l_date_format varchar2(100);
388 charid_tab qa_ss_const.num_table;
389 values_tab qa_ss_const.var150_table;
390 rows_selected BOOLEAN := FALSE;
391
392 script VARCHAR2(32000); -- out parameter to qltimptb pkg
393 tailscript VARCHAR2(32000); -- out parameter to qltimptb pkg
394 flg1 NUMBER;
395 r NUMBER;
396
397 qri_source_code VARCHAR2(30) := NULL;
398 qri_source_line_id NUMBER := NULL;
399
400 l_prompt VARCHAR2(50):=NULL; -- this is qa_chars.prompt column to show
401 -- collection element prompt for error messages
402
403 cursor chars_cur IS
404 SELECT qpc.char_id
405 FROM qa_plan_chars qpc
406 where qpc.plan_id = hid_planid
407 AND qpc.enabled_flag = 1
408 ORDER BY qpc.prompt_sequence;
409
410 BEGIN
411 if (icx_sec.validatesession) then
412
413 htp.p('<HTML>');
414
415 htp.p('<HEAD>');
416 js.ScriptOpen;
417 htp.p('function set_clear_var(inval)
418 {
419 if (inval = "N")
420 {
421 parent.buttonsFrame.document.buttonHiddenF.clear_var.value = "N";
422 // alert("value set to " + parent.buttonsFrame.document.buttonHiddenF.clear_var.value);
423 }
424 }');
425 js.ScriptClose;
426 htp.p('</HEAD>');
427
428 htp.p('<BODY bgcolor="#cccccc">');
429
430 -- BELOW WORKFLOWDOC added for Bug 998381
431 htp.p('<FORM ACTION="qa_ss_core.call_workflow" NAME="WORKFLOWDOC" METHOD="POST">');
432 htp.formHidden('x_buyer_id', po_agent_id);
433 htp.formHidden('x_source_id', x_source_id);
434 htp.formHidden('x_plan_id', hid_planid);
435 htp.formHidden('x_item_id', x_item_id);
436 htp.formHidden('x_po_header_id', x_po_header_id);
437 htp.p('</FORM>');
438
439
440 -- if orgz_id is null, then raise an exception here
441 if (orgz_id is NULL) Then
442 RAISE orgz_ex;
443 end if; -- end orgz_id check
444
445 if (hid_planid is NULL) Then
446 RAISE plan_ex;
447 end if; -- end hid_planid check
448
449 -- The below is useful for collection import and QLTCORE.pld
450 -- uses these values to display contact info etc in CIMDF
451 -- Bso has done those changes
452 -- source line id has the fnd_user userid got thro ICX call
453 qri_source_line_id := icx_sec.getID(icx_sec.PV_USER_ID);
454
455 if (txn_num = 100) THEN
456 qri_source_code := 'QA_SS_OSP';
457 elsif (txn_num = 110) THEN
458 qri_source_code := 'QA_SS_SHIP';
459 end if;
460
461
462
463 l_date_format := icx_sec.getID(icx_sec.PV_DATE_FORMAT);
464 if (l_date_format is NULL) then
465 l_date_format := 'DD/MON/RRRR';
466 end if;
467
468 col_count := 0;
469 FOR char_rec IN chars_cur
470 LOOP
471 col_count := col_count + 1;
472 charid_tab(col_count) := char_rec.char_id;
473 END LOOP;
474 -- col_count has the no.of cols in the plan
475
476 fnd_date.initialize(l_date_format); -- to set user mask
477 -- fnd_number.initialize( );
478 fnd_message.clear;
479
480 No_problem := TRUE; -- initialize this to true
481
482 SAVEPOINT start_inserting;
483 FOR K in 1..qa_ss_const.no_of_rows
484 LOOP
485 -- Reinitialization of No_problem to TRUE removed here
486 -- for usability fix after Aug 11
487
488 -- New code line below
489 -- If selectbox.Exists(K) Then
490 -- htp.p('value of selectbox('||selectbox(K)||') is: ' || selectbox(K));
491 -- htp.nl;
492 -- End If;
493
494 if ( NOT selectbox.Exists(K) ) Then
495
496 -- if ( selectbox(K) = 'N') THEN
497 -- htp.p('row ' || to_char(K)|| 'top level filtered'); htp.nl;
498 NULL; -- definitely this is an empty row
499 -- else still have to check for empty row?? I dont think so
500 /*
501 elsif ( is_empty_row ( K,
502 charid_tab,
503 txn_num,
504 p_col1,
505 p_col2,
506 p_col3,
507 p_col4,
508 p_col5,
509 p_col6,
510 p_col7,
511 p_col8,
512 p_col9,
513 p_col10,
514 p_col11,
515 p_col12,
516 p_col13,
517 p_col14,
518 p_col15,
519 p_col16,
520 p_col17,
521 p_col18,
522 p_col19,
523 p_col20,
524 p_col21,
525 p_col22,
526 p_col23,
527 p_col24,
528 p_col25,
529 p_col26,
530 p_col27,
531 p_col28,
532 p_col29,
533 p_col30,
534 p_col31,
535 p_col32,
536 p_col33,
537 p_col34,
538 p_col35,
539 p_col36,
540 p_col37,
541 p_col38,
542 p_col39,
543 p_col40,
544 p_col41,
545 p_col42,
546 p_col43,
547 p_col44,
548 p_col45,
549 p_col46,
550 p_col47,
551 p_col48,
552 p_col49,
553 p_col50,
554 p_col51,
555 p_col52,
556 p_col53,
557 p_col54,
558 p_col55,
559 p_col56,
560 p_col57,
561 p_col58,
562 p_col59,
563 p_col60,
564 p_col61,
565 p_col62,
566 p_col63,
567 p_col64,
568 p_col65,
569 p_col66,
570 p_col67,
571 p_col68,
572 p_col69,
573 p_col70,
574 p_col71,
575 p_col72,
576 p_col73,
577 p_col74,
578 p_col75,
579 p_col76,
580 p_col77,
581 p_col78,
582 p_col79,
583 p_col80,
584 p_col81,
585 p_col82,
586 p_col83,
587 p_col84,
588 p_col85,
589 p_col86,
590 p_col87,
591 p_col88,
592 p_col89,
593 p_col90,
594 p_col91,
595 p_col92,
596 p_col93,
597 p_col94,
598 p_col95,
599 p_col96,
600 p_col97,
601 p_col98,
602 p_col99,
603 p_col100,
604 p_col101,
605 p_col102,
606 p_col103,
607 p_col104,
608 p_col105,
609 p_col106,
610 p_col107,
611 p_col108,
612 p_col109,
613 p_col110,
614 p_col111,
615 p_col112,
616 p_col113,
617 p_col114,
618 p_col115,
619 p_col116,
620 p_col117,
621 p_col118,
622 p_col119,
623 p_col120,
624 p_col121,
625 p_col122,
626 p_col123,
627 p_col124,
628 p_col125,
629 p_col126,
630 p_col127,
631 p_col128,
632 p_col129,
633 p_col130,
634 p_col131,
635 p_col132,
636 p_col133,
637 p_col134,
638 p_col135,
639 p_col136,
640 p_col137,
641 p_col138,
642 p_col139,
643 p_col140,
644 p_col141,
645 p_col142,
646 p_col143,
647 p_col144,
648 p_col145,
649 p_col146,
650 p_col147,
651 p_col148,
652 p_col149,
653 p_col150,
654 p_col151,
655 p_col152,
656 p_col153,
657 p_col154,
658 p_col155,
659 p_col156,
660 p_col157,
661 p_col158,
662 p_col159,
663 p_col160,
664 hid_planid ) )
665 THEN
666 htp.p('row ' || to_char(K)|| 'second level filtered'); htp.nl;
667 NULL; -- empty row
668 */
669 ELSE
670 -- proceed with your calls
671
672 -- New code being added here
673 -- htp.p('===============================================================');
674 -- htp.nl;
675 r := to_number(selectbox(K));
676 -- r := K;
677 rows_selected := TRUE;
678 -- New code above
679
680 values_tab(1) := p_col1(r);
681 values_tab(2) := p_col2(r);
682 values_tab(3) := p_col3(r);
683 values_tab(4) := p_col4(r);
684 values_tab(5) := p_col5(r);
685 values_tab(6) := p_col6(r);
686 values_tab(7) := p_col7(r);
687 values_tab(8) := p_col8(r);
688 values_tab(9) := p_col9(r);
689 values_tab(10) := p_col10(r);
690 values_tab(11) := p_col11(r);
691 values_tab(12) := p_col12(r);
692 values_tab(13) := p_col13(r);
693 values_tab(14) := p_col14(r);
694 values_tab(15) := p_col15(r);
695 values_tab(16) := p_col16(r);
696 values_tab(17) := p_col17(r);
697 values_tab(18) := p_col18(r);
698 values_tab(19) := p_col19(r);
699 values_tab(20) := p_col20(r);
700 values_tab(21) := p_col21(r);
701 values_tab(22) := p_col22(r);
702 values_tab(23) := p_col23(r);
703 values_tab(24) := p_col24(r);
704 values_tab(25) := p_col25(r);
705 values_tab(26) := p_col26(r);
706 values_tab(27) := p_col27(r);
707 values_tab(28) := p_col28(r);
708 values_tab(29) := p_col29(r);
709 values_tab(30) := p_col30(r);
710 values_tab(31) := p_col31(r);
711 values_tab(32) := p_col32(r);
712 values_tab(33) := p_col33(r);
713 values_tab(34) := p_col34(r);
714 values_tab(35) := p_col35(r);
715 values_tab(36) := p_col36(r);
716 values_tab(37) := p_col37(r);
717 values_tab(38) := p_col38(r);
718 values_tab(39) := p_col39(r);
719 values_tab(40) := p_col40(r);
720 values_tab(41) := p_col41(r);
721 values_tab(42) := p_col42(r);
722 values_tab(43) := p_col43(r);
723 values_tab(44) := p_col44(r);
724 values_tab(45) := p_col45(r);
725 values_tab(46) := p_col46(r);
726 values_tab(47) := p_col47(r);
727 values_tab(48) := p_col48(r);
728 values_tab(49) := p_col49(r);
729 values_tab(50) := p_col50(r);
730 values_tab(51) := p_col51(r);
731 values_tab(52) := p_col52(r);
732 values_tab(53) := p_col53(r);
733 values_tab(54) := p_col54(r);
734 values_tab(55) := p_col55(r);
735 values_tab(56) := p_col56(r);
736 values_tab(57) := p_col57(r);
737 values_tab(58) := p_col58(r);
738 values_tab(59) := p_col59(r);
739 values_tab(60) := p_col60(r);
740 values_tab(61) := p_col61(r);
741 values_tab(62) := p_col62(r);
742 values_tab(63) := p_col63(r);
743 values_tab(64) := p_col64(r);
744 values_tab(65) := p_col65(r);
745 values_tab(66) := p_col66(r);
746 values_tab(67) := p_col67(r);
747 values_tab(68) := p_col68(r);
748 values_tab(69) := p_col69(r);
749 values_tab(70) := p_col70(r);
750 values_tab(71) := p_col71(r);
751 values_tab(72) := p_col72(r);
752 values_tab(73) := p_col73(r);
753 values_tab(74) := p_col74(r);
754 values_tab(75) := p_col75(r);
755 values_tab(76) := p_col76(r);
756 values_tab(77) := p_col77(r);
757 values_tab(78) := p_col78(r);
758 values_tab(79) := p_col79(r);
759 values_tab(80) := p_col80(r);
760 values_tab(81) := p_col81(r);
761 values_tab(82) := p_col82(r);
762 values_tab(83) := p_col83(r);
763 values_tab(84) := p_col84(r);
764 values_tab(85) := p_col85(r);
765 values_tab(86) := p_col86(r);
766 values_tab(87) := p_col87(r);
767 values_tab(88) := p_col88(r);
768 values_tab(89) := p_col89(r);
769 values_tab(90) := p_col90(r);
770 values_tab(91) := p_col91(r);
771 values_tab(92) := p_col92(r);
772 values_tab(93) := p_col93(r);
773 values_tab(94) := p_col94(r);
774 values_tab(95) := p_col95(r);
775 values_tab(96) := p_col96(r);
776 values_tab(97) := p_col97(r);
777 values_tab(98) := p_col98(r);
778 values_tab(99) := p_col99(r);
779 values_tab(100) := p_col100(r);
780 values_tab(101) := p_col101(r);
781 values_tab(102) := p_col102(r);
782 values_tab(103) := p_col103(r);
783 values_tab(104) := p_col104(r);
784 values_tab(105) := p_col105(r);
785 values_tab(106) := p_col106(r);
786 values_tab(107) := p_col107(r);
787 values_tab(108) := p_col108(r);
788 values_tab(109) := p_col109(r);
789 values_tab(110) := p_col110(r);
790 values_tab(111) := p_col111(r);
791 values_tab(112) := p_col112(r);
792 values_tab(113) := p_col113(r);
793 values_tab(114) := p_col114(r);
794 values_tab(115) := p_col115(r);
795 values_tab(116) := p_col116(r);
796 values_tab(117) := p_col117(r);
797 values_tab(118) := p_col118(r);
798 values_tab(119) := p_col119(r);
799 values_tab(120) := p_col120(r);
800 values_tab(121) := p_col121(r);
801 values_tab(122) := p_col122(r);
802 values_tab(123) := p_col123(r);
803 values_tab(124) := p_col124(r);
804 values_tab(125) := p_col125(r);
805 values_tab(126) := p_col126(r);
806 values_tab(127) := p_col127(r);
807 values_tab(128) := p_col128(r);
808 values_tab(129) := p_col129(r);
809 values_tab(130) := p_col130(r);
810 values_tab(131) := p_col131(r);
811 values_tab(132) := p_col132(r);
812 values_tab(133) := p_col133(r);
813 values_tab(134) := p_col134(r);
814 values_tab(135) := p_col135(r);
815 values_tab(136) := p_col136(r);
816 values_tab(137) := p_col137(r);
817 values_tab(138) := p_col138(r);
818 values_tab(139) := p_col139(r);
819 values_tab(140) := p_col140(r);
820 values_tab(141) := p_col141(r);
821 values_tab(142) := p_col142(r);
822 values_tab(143) := p_col143(r);
823 values_tab(144) := p_col144(r);
824 values_tab(145) := p_col145(r);
825 values_tab(146) := p_col146(r);
826 values_tab(147) := p_col147(r);
827 values_tab(148) := p_col148(r);
828 values_tab(149) := p_col149(r);
829 values_tab(150) := p_col150(r);
830 values_tab(151) := p_col151(r);
831 values_tab(152) := p_col152(r);
832 values_tab(153) := p_col153(r);
833 values_tab(154) := p_col154(r);
834 values_tab(155) := p_col155(r);
835 values_tab(156) := p_col156(r);
836 values_tab(157) := p_col157(r);
837 values_tab(158) := p_col158(r);
838 values_tab(159) := p_col159(r);
839 values_tab(160) := p_col160(r);
840
841 -- htp.p('Row:'|| r || ' Calling ob start import row');
842 qa_results_interface_pkg.start_import_row(1, orgz_id, hid_planid,
843 script, tailscript, qri_source_code,
844 qri_source_line_id, po_agent_id);
845
846 for c in 1..col_count
847 loop
848 flg1 := qa_results_interface_pkg.add_element_value(hid_planid, charid_tab(c),
849 values_tab(c), script, tailscript);
850
851 if (flg1 = 2) then
852 l_prompt := qa_ss_core.get_char_prompt(charid_tab(c));
853 fnd_message.set_name('QA', 'QA_SS_INVALID_NUM');
854 fnd_message.set_token('ROWNUMBER', r);
855 fnd_message.set_token('COLNUMBER', c);
856 fnd_message.set_token('ELEMENT', l_prompt);
857 -- icx_util.add_error(fnd_message.get);
858 htp.p('<STRONG><FONT COLOR="#FF0000">'||fnd_message.get||'</FONT></STRONG>');
859 htp.nl;
860 No_problem := FALSE; -- this means, there is some error
861 elsif (flg1 = 3) then
862 l_prompt := qa_ss_core.get_char_prompt(charid_tab(c));
863 fnd_message.set_name('QA', 'QA_SS_INVALID_DATE');
864 fnd_message.set_token('ROWNUMBER', r);
865 fnd_message.set_token('COLNUMBER', c);
866 fnd_message.set_token('ELEMENT', l_prompt);
867 -- icx_util.add_error(fnd_message.get);
868 htp.p('<STRONG><FONT COLOR="#FF0000">'||fnd_message.get||'</FONT></STRONG>');
869 htp.nl;
870 No_problem := FALSE; -- this means, there is some error
871 elsif (flg1 = 4) then
872 -- Adding if statement, Sep 13, 1999 -- talked to mmpatel
873 -- accept null context elems even if they r mandatory BUG 998445
874 if (NOT CxE(charid_tab(c), txn_num)) -- if not a ctx elem, then process
875 then
876 l_prompt := qa_ss_core.get_char_prompt(charid_tab(c));
877 fnd_message.set_name('QA', 'QA_SS_MAND_VALUE');
878 fnd_message.set_token('ROWNUMBER', r);
879 fnd_message.set_token('COLNUMBER', c);
880 fnd_message.set_token('ELEMENT', l_prompt);
881 -- icx_util.add_error(fnd_message.get);
882 htp.p('<STRONG><FONT COLOR="#FF0000">'||fnd_message.get||'</FONT></STRONG>');
883 htp.nl;
884 No_problem := FALSE; -- this means, there is some error
885 end if;
886 elsif (flg1 = 1) then
887 l_prompt := qa_ss_core.get_char_prompt(charid_tab(c));
888 fnd_message.set_name('QA', 'QA_SS_GENERIC_INVALID');
889 fnd_message.set_token('ROWNUMBER', r);
890 fnd_message.set_token('COLNUMBER', c);
891 fnd_message.set_token('ELEMENT', l_prompt);
892 -- icx_util.add_error(fnd_message.get);
893 htp.p('<STRONG><FONT COLOR="#FF0000">'||fnd_message.get||'</FONT></STRONG>');
894 htp.nl;
895 No_problem := FALSE; -- this means, there is some error
896 end if; -- end flg1 check
897 end loop; -- columns loop
898
899 /* -- Introduced to report values entered for errored rows
900 -- Commented out after aug 11 frz for usability fix
901 if (no_problem = FALSE) then
902 htp.p('You entered the following values for this Row: ');
903 htp.nl;
904 FOR c in 1..col_count
905 LOOP
906 htp.p('column '||to_char(c)||' : '|| values_tab(c));
907 htp.nl;
908 END LOOP;
909 htp.nl;
910 else
911 NULL;
912 end if;
913 */
914 -- htp.p('Calling end import row for row : ' || to_char(r)); htp.nl;
915 qa_results_interface_pkg.end_import_row(script, tailscript, No_problem);
916
917 /* -- commented out after aug 11 frz for usability fix
918 if (no_problem = TRUE) then
919 htp.p('Row '||to_char(r) || ': Successfully submitted'); htp.nl;
920 -- Need new fnd seed data july 28, 1999
921 -- Commit done at end of all rows below
922 end if;
923 */
924 END IF; -- end check for empty row
925 END LOOP; --End Rows Loop
926
927 -- COMMIT; -- commented out after aug 11 frz for usability fix
928
929 If (rows_selected = FALSE) Then
930 htp.p(fnd_message.get_string('QA', 'QA_SS_NO_CHANGES')); -- New code
931 -- Need new fnd seed data july 28, 1999
932
933 Elsif (No_problem = TRUE) Then
934 fnd_message.set_name('QA', 'QA_SS_INSERT_OK');
935 htp.p(fnd_message.get);
936 Commit; -- there was no problem so we can commit
937
938 Else
939 -- icx_admin_sig.error_screen(fnd_message.get);
940 -- commented out after aug 11 frz for usability fix
941 htp.p(fnd_message.get_string('QA', 'QA_SS_INSERT_NOT_OK'));
942 Rollback to start_inserting; -- rollback any inserts issued
943
944
945 End If; -- end checking value of No_problem
946 fnd_message.clear;
947 js.ScriptOpen;
948 if (No_problem=FALSE) THEN
949 htp.p('javascript:set_clear_var("N")');
950 end if;
951 js.ScriptClose;
952 htp.p('</BODY>');
953 htp.p('</HTML>');
954
955 end if; -- end icx validate session
956 EXCEPTION
957 WHEN orgz_ex THEN
958 htp.p('<STRONG><FONT COLOR="#FF0000">Organization Id is Null which raised exception</FONT></STRONG>');
959
960 WHEN plan_ex THEN
961 htp.p('<STRONG><FONT COLOR="#FF0000">Plan Id is Null which raised exception</FONT></STRONG>');
962 WHEN OTHERS THEN
963 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
964 END enter_results;
965 ------------------------------------------------------------------------
966
967
968 procedure draw_table(plan_id_i IN qa_plans.plan_id%TYPE DEFAULT NULL,
969 txn_num IN NUMBER DEFAULT NULL,
970 orgz_id IN NUMBER DEFAULT NULL,
971 pk1 IN VARCHAR2 DEFAULT NULL,
972 pk2 IN VARCHAR2 DEFAULT NULL,
973 pk3 IN VARCHAR2 DEFAULT NULL,
974 pk4 IN VARCHAR2 DEFAULT NULL,
975 pk5 IN VARCHAR2 DEFAULT NULL,
976 pk6 IN VARCHAR2 DEFAULT NULL,
977 pk7 IN VARCHAR2 DEFAULT NULL,
978 pk8 IN VARCHAR2 DEFAULT NULL,
979 pk9 IN VARCHAR2 DEFAULT NULL,
980 pk10 IN VARCHAR2 DEFAULT NULL)
981 IS
982 Ctx qa_ss_const.Ctx_Table;
983 -- Ctx_Table type is declared in pkg spec
984 Prompt_Arr qa_ss_const.var30_table;
985 lov_arr qa_ss_const.bool_table;
986 disp_len_arr qa_ss_const.num_table;
987 dv_arr qa_ss_const.var150_table; -- for Default Value
988 charid_array qa_ss_const.num_table;
989 names_array qa_ss_const.var30_table;
990
991 no_of_cols NUMBER := 0;
992 plan_name_i qa_plans.name%TYPE := NULL;
993 l_language_code varchar2(30);
994 it_name VARCHAR2(20);
995 de_name VARCHAR2(20); -- dependent element name
996 Name VARCHAR2(2000);
997 char_name_i qa_chars.name%TYPE;
998 item_name VARCHAR2(30) := NULL;
999 l_Po_Agent_Id NUMBER := NULL;
1000 l_User_Id NUMBER := NULL;
1001 l_Item_Id NUMBER := NULL;
1002 l_Po_Header_Id NUMBER := NULL;
1003 l_Wip_Entity_Type NUMBER := NULL;
1004 l_Wip_Rep_Sch_Id NUMBER := NULL;
1005 l_Po_Release_Id NUMBER := NULL;
1006 l_Po_Line_Id NUMBER := NULL;
1007 l_Line_Location_Id NUMBER := NULL;
1008 l_Po_Distribution_Id NUMBER := NULL;
1009 l_Wip_Entity_Id NUMBER := NULL;
1010 l_Wip_Line_Id NUMBER := NULL;
1011 l_Po_Shipment_Id NUMBER := NULL;
1012 l_Organization_Id NUMBER := NULL;
1013
1014 row_color VARCHAR2(20) := 'BLUE';
1015
1016 -- sql_st VARCHAR2(32000) := NULL; /* Remove this later, only for debug */
1017 -- iv_string VARCHAR2(30) := NULL; /* remove this too */
1018
1019 job_pline_ex EXCEPTION;
1020 flag NUMBER:=0;
1021
1022 -- Aug 03, 1999 adding data entry hint to this cursor
1023
1024 dhint_tab qa_ss_const.var150_table;
1025
1026 CURSOR char_cur IS
1027 select qpc.prompt_sequence, qc.char_id, qc.name, NVL(qc.developer_name, qc.name) AS cname, qc.sql_validation_string, qpc.prompt, qc.display_length, qpc.default_value,
1028 qpc.enabled_flag, qc.data_entry_hint
1029 from qa_plan_chars qpc, qa_chars qc
1030 where qpc.plan_id = plan_id_i
1031 AND qpc.char_id = qc.char_id
1032 AND qpc.enabled_flag = 1
1033 ORDER BY qpc.prompt_sequence;
1034 -- changed above cursor qpc.prompt, qpc.default_value
1035 -- to fix bug 1276799
1036 BEGIN
1037 if (icx_sec.validatesession) then
1038
1039 fnd_message.clear;
1040
1041 Default_In_Values(Ctx, Txn_Num, PK1, PK2, PK3, PK4,
1042 PK5, PK6, PK7, PK8, PK9, PK10, l_Po_Agent_Id, l_Item_Id, l_Po_Header_Id,
1043 l_Wip_Entity_Type, l_Wip_Rep_Sch_Id, l_Po_Release_Id, l_Po_Line_Id,
1044 l_Line_Location_Id, l_Po_Distribution_Id, l_Wip_Entity_Id,
1045 l_Wip_Line_Id, l_Po_Shipment_Id, l_Organization_Id );
1046 -- Remember Ctx is InOut and l_Po_agent_id is Out
1047 -- The l_po_agent_id is stored in a formhidden below
1048 -- Also above function will alter some global package variables
1049 -- Keep in mind it will set global variables starting with G_etc.
1050
1051
1052 l_language_code := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
1053 l_user_id := icx_sec.getID(icx_sec.PV_USER_ID);
1054
1055 Name := owa_util.get_cgi_env('SCRIPT_NAME');
1056
1057 select qp.name into plan_name_i
1058 from qa_plans qp
1059 where qp.plan_id = plan_id_i;
1060
1061 -- htp.p('Debug: Job = ' || job_i);
1062
1063
1064 htp.p('<HTML>');
1065 htp.p('<HEAD>');
1066 htp.linkRel('STYLESHEET', '/OA_HTML/'||
1067 l_language_code||'/POSSTYLE.css');
1068
1069 generate_eqr_javascript; -- this is a pl/sql procedure call. procedure is in this pkg
1070
1071 htp.p('<BODY bgcolor="#cccccc" onLoad="javascript:parent.clear_if_needed()">');
1072 -- htp.p('<BODY bgcolor="#cccccc" onLoad="if (parent.buttonsFrame.document.buttonHiddenF.clear_var.value = ''N'') {parent.clr_form()}">');
1073 -- htp.p('<BODY bgcolor="#cccccc" onLoad="x=parent.buttonsFrame.document.buttonHiddenF.clear_var.value; alert(x)">');
1074 -- icx_admin_sig.toolbar(language_code => c_language_code, disp_help => 'N');
1075
1076 fnd_message.set_name('QA', 'QA_SS_EQR');
1077 /*
1078 icx_plug_utilities.toolbar(substr(fnd_message.get,1,40), p_language_code=>l_language_code,
1079 p_disp_help=>'Y',p_disp_exit=>'Y');
1080 icx_plug_utilities.plugbanner(plan_name_i);
1081 */
1082 -- htp.p('Org id is ' || fnd_profile.value('ORG_ID') );
1083 -- htp.p('User id is ' || fnd_profile.value('USER_ID') );
1084 -- htp.p('pvwebuserid: ' || icx_sec.getID(icx_sec.PV_WEB_USER_ID));
1085
1086 --hidden_elems; -- CALL TO HIDDEN_ELEMS to set hidden fields
1087 htp.p('<FORM ACTION="" NAME="HiddenRSMDF" METHOD="">');
1088 htp.formHidden('x_txn_num', txn_num);
1089 htp.formHidden('x_wip_entity_type', l_wip_entity_type);
1090 htp.formHidden('x_wip_rep_sch_id', l_wip_rep_sch_id);
1091 htp.formHidden('x_po_header_id', l_po_header_id);
1092 htp.formHidden('x_po_release_id', l_po_release_id);
1093 htp.formHidden('x_po_line_id', l_po_line_id);
1094 htp.formHidden('x_line_location_id', l_line_location_id);
1095 htp.formHidden('x_po_distribution_id', l_po_distribution_id);
1096 htp.formHidden('x_item_id', l_item_id);
1097 htp.formHidden('x_wip_entity_id', l_wip_entity_id);
1098 htp.formHidden('x_wip_line_id', l_wip_line_id);
1099 htp.formHidden('x_po_shipment_id', l_po_shipment_id);
1100 htp.p('</FORM>');
1101
1102 htp.p('<FORM ACTION="qa_ss_core.call_workflow" NAME="WORKFLOWDOC" METHOD="POST">');
1103 htp.formHidden('x_buyer_id', l_po_agent_id);
1104 htp.formHidden('x_source_id', l_user_id);
1105 htp.formHidden('x_plan_id', plan_id_i);
1106 htp.formHidden('x_item_id', l_Item_Id);
1107 htp.formHidden('x_po_header_id', l_po_header_id);
1108 htp.p('</FORM>');
1109
1110 htp.p('<FORM ACTION="qa_ss_core.enter_results" NAME="RSMDF" METHOD="POST">');
1111 -- htp.formHidden('No_Dependency','No_Dependency'); --This is no longer needed
1112
1113 htp.tableOpen(cborder=>'BORDER=2',calign=>'CENTER', cattributes=>'CELLPADDING=2 cellspacing=1');
1114
1115 -- htp.p('Language Code = ' || l_language_code);
1116 -- htp.br;
1117
1118 -- htp.tableCaption(htf.bold(plan_name_i));
1119 -- htp.tableRowOpen(cattributes => 'BGCOLOR="#336699"');
1120 htp.tableRowOpen;
1121 -- htp.p('Got here 1');
1122
1123 htp.tableData(cvalue=>'<font class=promptwhite>'||'Select'||'</font>',
1124 calign=>'LEFT', cattributes=>'valign=bottom bgcolor="#336699"');
1125
1126 FOR char_rec IN char_cur
1127 LOOP
1128 -- htp.p(' Got here 2');
1129 no_of_cols := no_of_cols + 1;
1130
1131
1132 -- htp.p('enable_arr('||no_of_cols||') = ' || enable_arr(no_of_cols));
1133
1134 IF (is_lov_needed(char_rec.char_id, Txn_Num) = FALSE)
1135 then
1136 -- if no lov is needed or if ctxt elmt
1137 htp.tableData(cvalue=>'<font class=promptwhite>'||char_rec.prompt||'</font>',
1138 calign=>'LEFT', cattributes=>'valign=bottom bgcolor="#336699"');
1139 lov_arr(no_of_cols) := FALSE;
1140 --span one column
1141 ELSE --span 2 cols
1142 htp.tableData(cvalue=>'<font class=promptwhite>'||char_rec.prompt||'</font>',
1143 calign=>'LEFT',
1144 ccolspan=>'2', cattributes=>'valign=bottom bgcolor="#336699"' );
1145 lov_arr(no_of_cols) := TRUE;
1146 -- this is an lov field
1147 END IF;
1148 dv_arr(no_of_cols) := char_rec.default_value;
1149 disp_len_arr(no_of_cols) := char_rec.display_length;
1150 Prompt_Arr(no_of_cols) := char_rec.prompt; --needed to draw end of table
1151 names_array(no_of_cols) := char_rec.cname;
1152 charid_array(no_of_cols) := char_rec.char_id;
1153 IF (char_rec.data_entry_hint IS NOT NULL) Then
1154 dhint_tab(no_of_cols) :=
1155 ICX_UTIL.replace_onMouseOver_quotes(
1156 SUBSTRB(char_rec.data_entry_hint,1,90));
1157 ELSE
1158 dhint_tab(no_of_cols) := NULL;
1159 END IF; -- end if for data entry hint
1160
1161 -- htp.p('column'||no_of_cols||' : char_name= '||char_rec.name);
1162 END LOOP;
1163 -- htp.p('Got here 3');
1164
1165 htp.tableRowClose;
1166 -- Below Line for UI Standards
1167 htp.p('<TR></TR><TR></TR>'); -- DO NOT COMMENT THIS UI Standard
1168
1169 -- htp.p('Got here 4'); htp.nl;
1170 -- htp.p('Number of cols = ' || no_of_cols); htp.nl;
1171 FOR rows IN 1..qa_ss_const.no_of_rows
1172 LOOP
1173 if (row_color = 'BLUE') THEN
1174 htp.tableRowOpen(cattributes => 'BGCOLOR="#99CCFF"');
1175 row_color := 'WHITE';
1176 else
1177 htp.tableRowOpen(cattributes => 'BGCOLOR="#FFFFFF"');
1178 row_color := 'BLUE';
1179 end if; -- end if for row_color
1180 -- New code line for field below. Indicates if a row was modified
1181 -- Dont make this a table data hiddenfield <TD> It screws up the UI
1182 -- Consequently htp used, NOT htf
1183 -- htp.formHidden('selectbox', cvalue=>'N') ;
1184 htp.tableData( htf.formCheckbox(cname=>'selectbox',
1185 cvalue=>to_char(rows)));
1186
1187
1188 FOR cols IN 1..no_of_cols
1189 LOOP
1190 -- htp.p('Got here 5');
1191 -- htp.p('col = ' || cols); htp.nl;
1192 item_name := 'p_col' || TO_CHAR(cols);
1193 -- htp.p('charid= ' || charid_array(cols) || ' txnnum= ' || Txn_Num);
1194 IF ( CxE(charid_array(cols), Txn_Num) ) Then
1195 -- this is a context element for this txn
1196 -- so create a textual data and corresponding hidden field value
1197
1198 -- htp.p('Is a context element'); htp.nl;
1199 htp.tableData( NVL(Ctx(charid_array(cols)), ' '), calign=>'LEFT',
1200 crowspan=>'1',
1201 cattributes=>'VALIGN="CENTER"');
1202 htp.formHidden(item_name, Ctx(charid_array(cols)));
1203
1204 else -- Not a default context element
1205 -- htp.p('Not context element'); htp.nl;
1206 -- New code, the whole onchange piece is new july28,1999
1207 -- Onfocus added on Aug 03, 1999
1208
1209 /*
1210 htp.tableData(htf.formText(item_name,disp_len_arr(cols),150,
1211 dv_arr(cols),'onChange="document.RSMDF.selectbox['
1212 || to_char(rows-1) || '].value=''Y''" onFocus="window.status='''||dhint_tab(cols)||'''"' ),
1213 calign=>'CENTER',
1214 crowspan=>'1',
1215 cattributes=>'VALIGN="CENTER"');
1216 */
1217 -- Above commented out on Aug 13, 1999 to try the below checkbox instead of hidden field
1218 -- changing .value=''Y'' to .checked=true
1219 htp.tableData(htf.formText(item_name,disp_len_arr(cols),150,
1220 dv_arr(cols),'onChange="document.RSMDF.selectbox['
1221 || to_char(rows-1) || '].checked=true" onFocus="window.status='''||dhint_tab(cols)||'''"' ),
1222 calign=>'CENTER',
1223 crowspan=>'1',
1224 cattributes=>'VALIGN="CENTER"');
1225
1226 IF (lov_arr(cols)) THEN
1227 it_name := 'p_col' ||to_char(cols)||'['||to_char(rows-1) || ']';
1228
1229 -- FNDILOV.gif is older FNDLSTOV.gif is newer
1230 htp.p('<TD><A HREF="javascript:LOV(' || To_Char(rows-1) || ','
1231 || charid_array(cols) || ', '||cols || ', '|| 'document.RSMDF.'
1232 ||it_name || ' )">
1233 <img src="/OA_MEDIA/FNDILOV.gif" ALIGN="CENTER" ALT="Lov"
1234 BORDER=0 WIDTH=23 HEIGHT=21></A></TD>');
1235 -- Use rows-1 for javascript rows are from Zero unlike plsql
1236 END IF; --end if lov icon needed or not
1237 END IF; -- end whether context elemnt or not
1238
1239
1240 item_name := NULL;
1241
1242 -- htp.p('Got here 8'); htp.nl;
1243
1244 END LOOP; -- end col loop
1245 -- htp.p('got here 9');
1246
1247 -- Hidden columns
1248 FOR cols IN no_of_cols+1..qa_ss_const.max_cols
1249 LOOP
1250 -- htp.p('got here 10');
1251 item_name := 'p_col' || TO_CHAR(cols);
1252 htp.formHidden(item_name);
1253 item_name := NULL;
1254 END LOOP; -- end hidden column loop
1255
1256
1257 htp.tableRowClose;
1258 -- htp.p('got here 11'||htf.br);
1259 END LOOP; /* end rows loop */
1260 -- htp.p('got here 12');
1261
1262 /* -- commenting out becos no longer need bottom table header acc to new UI
1263 htp.p('<TR></TR><TR></TR>');
1264 htp.tableRowOpen(cattributes => 'BGCOLOR="#83C1C1"');
1265 FOR i in 1..no_of_cols
1266 LOOP
1267 -- htp.p('got here 13');
1268
1269 IF ( lov_arr(i) = FALSE)
1270 then
1271 htp.tableHeader(cvalue=>Prompt_Arr(i),
1272 calign=>'CENTER');
1273 else
1274 htp.tableHeader(cvalue=>Prompt_Arr(i),
1275 calign=>'CENTER',
1276 ccolspan=>'2');
1277 END IF;
1278 END LOOP;
1279 -- where is tablerowclose? is this omitted by mistake?
1280 */
1281 -- htp.p('got here 14');
1282 htp.tableClose;
1283 htp.formHidden('hid_planid', plan_id_i);
1284 htp.formHidden('orgz_id', orgz_id);
1285 htp.formHidden('txn_num', Txn_Num);
1286 htp.formHidden('po_agent_id', l_Po_Agent_Id);
1287 -- Just like ur having the above hiddenfield for planid
1288 -- have a couple of hidden fields called orgz_id and txn_num
1289 -- so they can also be passed to the enter_results procedure
1290 htp.formHidden('x_source_id', l_user_id);
1291 htp.formHidden('x_item_id', l_Item_Id);
1292 htp.formHidden('x_po_header_id', l_po_header_id);
1293 -- The above hidden fields added for BUG 998381
1294 -- so they are part of RSMDF in addition to WORKFLOWDOC
1295 -- htp.formSubmit(NULL, 'Enter Results');
1296
1297 htp.br;
1298
1299 htp.p('</FORM>');
1300
1301 htp.p('</BODY>');
1302 htp.p('</HTML>');
1303
1304
1305 end if; -- end icx validatesession
1306
1307 EXCEPTION
1308 WHEN job_pline_ex THEN
1309 htp.p('Job and Production Line Exception thrown in draw_table');
1310
1311 WHEN OTHERS THEN
1312 htp.p('Exception in procedure draw_table');
1313 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
1314 END draw_table;
1315 ------------------------------------------------------------------------------------
1316
1317 function CxE(cid IN Number, txnumber IN NUMBER DEFAULT NULL)
1318 RETURN BOOLEAN
1319
1320 IS
1321 coll_trigg NUMBER;
1322 coll_seed_ex EXCEPTION;
1323 BEGIN
1324
1325 Select count(1) INTO coll_trigg
1326 From qa_txn_collection_triggers qtct
1327 WHERE qtct.transaction_number = txnumber
1328 AND qtct.collection_trigger_id = cid;
1329
1330 IF (coll_trigg = 1) Then
1331 RETURN TRUE; -- it is a context element for this txn
1332 Elsif (coll_trigg = 0) THEN
1333 RETURN FALSE; -- not a context element for this txn
1334 Else
1335 Raise coll_seed_ex; -- seed data problem in coll. triggers
1336 END IF; -- endif for coll_trigg comparison
1337
1338 EXCEPTION
1339 WHEN coll_seed_ex THEN
1340 htp.p('Exception in function CxE - Collection Trigger');
1341 WHEN OTHERS THEN
1342 htp.p('Exception in function CxE');
1343 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
1344 END CxE;
1345 --------------------------------------------------------------------------------------
1346
1347
1348 function is_lov_needed(cid IN Number,
1349 Txn_Num IN NUMBER DEFAULT NULL)
1350 RETURN BOOLEAN
1351
1352 IS
1353 cx Boolean;
1354 inlist_exists NUMBER:=0;
1355 sql_val_st VARCHAR2(3000) := NULL;
1356
1357 BEGIN
1358 if (CxE(cid, Txn_Num)) then
1359 return FALSE;
1360 end if;
1361 -- If it is a context element for the given txn, then we
1362 -- return above. Below stmts are not executed
1363
1364 SELECT values_exist_flag, sql_validation_string INTO inlist_exists,sql_val_st
1365 FROM qa_chars
1366 WHERE char_id = cid;
1367
1368 if (inlist_exists = 1) then
1369 return TRUE;
1370 end if;
1371 if (sql_val_st IS NOT NULL) then
1372 return TRUE;
1373 end if;
1374
1375 -- Hardcoded collection elements are taken care of below
1376 -- if hardcoded lov record group is available in regular
1377 -- Quality Application in QRES block of QLTRSMDF form
1378 if (cid IN (qa_ss_const.Item,
1379 qa_ss_const.Locator,
1380 qa_ss_const.Comp_Revision,
1381 qa_ss_const.Comp_Subinventory,
1382 qa_ss_const.Comp_UOM,
1383 qa_ss_const.Customer_Name,
1384 qa_ss_const.Department,
1385 qa_ss_const.From_Op_Seq_Num,
1386 qa_ss_const.Production_Line,
1387 qa_ss_const.PO_Number,
1388 qa_ss_const.PO_Release_Num,
1389 qa_ss_const.PO_Shipment_Num,
1390 qa_ss_const.Project_Number,
1391 qa_ss_const.Receipt_Num,
1392 qa_ss_const.Resource_Code,
1393 qa_ss_const.Revision,
1394 qa_ss_const.RMA_Number,
1395 qa_ss_const.Sales_Order,
1396 qa_ss_const.Subinventory,
1397 qa_ss_const.Task_Number,
1398 qa_ss_const.To_Department,
1399 qa_ss_const.To_Op_Seq_Num,
1400 qa_ss_const.UOM,
1401 qa_ss_const.Vendor_Name,
1402 qa_ss_const.Job_Name)
1403 )
1404
1405 then
1406 return TRUE;
1407 else
1408 return FALSE;
1409 end if;
1410
1411 EXCEPTION
1412 WHEN OTHERS THEN
1413 htp.p('Exception in function is_lov_needed');
1414 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
1415 END is_lov_needed;
1416
1417 ------------------------------------------------------------------------------------------
1418
1419 procedure draw_frames(plan_id_i IN qa_plans.plan_id%TYPE DEFAULT NULL,
1420 txn_num IN NUMBER DEFAULT NULL,
1421 orgz_id IN NUMBER DEFAULT NULL,
1422 pk1 IN VARCHAR2 DEFAULT NULL,
1423 pk2 IN VARCHAR2 DEFAULT NULL,
1424 pk3 IN VARCHAR2 DEFAULT NULL,
1425 pk4 IN VARCHAR2 DEFAULT NULL,
1426 pk5 IN VARCHAR2 DEFAULT NULL,
1427 pk6 IN VARCHAR2 DEFAULT NULL,
1428 pk7 IN VARCHAR2 DEFAULT NULL,
1429 pk8 IN VARCHAR2 DEFAULT NULL,
1430 pk9 IN VARCHAR2 DEFAULT NULL,
1431 pk10 IN VARCHAR2 DEFAULT NULL)
1432
1433 IS
1434 xyz varchar2(20000);
1435 -- In this code, xyz is used for a URL string construction
1436 toolbar_heading VARCHAR2(500);
1437 l_plan_name VARCHAR2(50);
1438 l_language_code VARCHAR2(30);
1439
1440 CURSOR plan_name_cur IS
1441 SELECT name
1442 FROM QA_PLANS
1443 WHERE plan_id = plan_id_i ;
1444
1445 BEGIN
1446 if (icx_sec.validatesession) then
1447 l_language_code := icx_sec.getid(icx_sec.PV_LANGUAGE_CODE);
1448
1449 OPEN plan_name_cur;
1450 FETCH plan_name_cur INTO l_plan_name;
1451 CLOSE plan_name_cur;
1452
1453 xyz := 'qa_ss_core.draw_table?plan_id_i=' || plan_id_i
1454 || '&' || 'txn_num=' || Txn_Num
1455 || '&' || 'orgz_id=' || orgz_id
1456 || '&' || 'pk1=' || pk1
1457 || '&' || 'pk2=' || pk2
1458 || '&' || 'pk3=' || pk3
1459 || '&' || 'pk4=' || pk4
1460 || '&' || 'pk5=' || pk5
1461 || '&' || 'pk6=' || pk6
1462 || '&' || 'pk7=' || pk7
1463 || '&' || 'pk8=' || pk8
1464 || '&' || 'pk9=' || pk9
1465 || '&' || 'pk10=' || pk10;
1466 htp.p('<HTML>');
1467 htp.p('<HEAD>');
1468 htp.p('<LINK REL="STYLESHEET" HREF="/OA_HTML/'
1469 ||l_language_code||'/POSSTYLE.css">');
1470
1471
1472 htp.p('<script src="/OA_HTML/POSCUTIL.js" language="JavaScript">');
1473 htp.p('</script>');
1474 htp.p('<script src="/OA_HTML/POSWUTIL.js" language="JavaScript">');
1475 htp.p('</script>');
1476 htp.p('<script src="/OA_HTML/POSEVENT.js" language="JavaScript">');
1477 htp.p('</script>');
1478
1479 js.scriptOpen;
1480 pos_global_vars_sv.InitializeMessageArray;
1481 js.scriptClose;
1482
1483 htp.p('<SCRIPT LANGUAGE="JavaScript">');
1484 htp.p('function eqr_submit()
1485 {
1486 eqrFrame.document.RSMDF.submit();
1487
1488 }');
1489 htp.p('function clr_form()
1490 {
1491 eqrFrame.document.RSMDF.reset();
1492 }');
1493 htp.p('function go_bk()
1494 {
1495 eqrFrame.history.go(-1);
1496 }');
1497
1498 -- New code trial below
1499 htp.p('function notify_buyer(buyer_msg)
1500 {
1501 if (confirm(buyer_msg))
1502 {
1503 // buttonsFrame.location = "http://www.oracle.com";
1504 eqrFrame.document.WORKFLOWDOC.submit();
1505 }
1506 }');
1507
1508 -- Newly added below after Aug 11 frz for usability fix
1509 htp.p('function clear_if_needed()
1510 {
1511 if (buttonsFrame.document.buttonHiddenF.clear_var.value == "Y")
1512 {
1513 clr_form();
1514 }
1515 else
1516 {
1517 buttonsFrame.document.buttonHiddenF.clear_var.value = "Y" ;
1518 // reset the value so as to submit again
1519 }
1520 }');
1521
1522 htp.p('</SCRIPT>');
1523 htp.p('</HEAD>');
1524 -- NEW made the border=0 in the frameset. This prevents accidental reloading
1525 -- when frame is resized. Now that can be prevented
1526 htp.p('<frameset rows="50,*,40" border=0>');
1527
1528 toolbar_heading := fnd_message.get_string('QA', 'QA_SS_EQR');
1529 toolbar_heading := wfa_html.conv_special_url_chars(toolbar_heading);
1530 htp.p('<frame src="pos_toolbar_sv.PaintToolbar?p_title='||toolbar_heading||'"
1531 name=toolbar
1532 marginwidth=6
1533 marginheight=2
1534 scrolling=no>');
1535 htp.p('<frameset cols="3,*,3" border=0>');
1536 htp.p('<frame src="/OA_HTML/'||l_language_code||'/POSBLBOR.htm"
1537 name=borderLeft
1538 marginwidth=0
1539 marginheight=0
1540 scrolling=no>');
1541
1542 l_plan_name := wfa_html.conv_special_url_chars(l_plan_name);
1543 htp.p('<frameset rows="30,*,5" border=0>');
1544 htp.p('<frame src="pos_upper_banner_sv.PaintUpperBanner?p_product=QA'||'&'||'p_title='
1545 || l_plan_name || '"
1546 name=upperbanner
1547 marginwidth=0
1548 marginheight=0
1549 scrolling=no>');
1550
1551
1552 htp.p('<FRAME NAME="eqrFrame" SRC="' || xyz || '">');
1553
1554 htp.p('<frame src="/OA_HTML/'||l_language_code||'/POSLWBAN.htm"
1555 name=lowerbanner
1556 marginwidth=0
1557 marginheight=0
1558 scrolling=no>');
1559
1560 htp.p('</frameset>');
1561 htp.p('<frame src="/OA_HTML/'||l_language_code||'/POSBLBOR.htm"
1562 name=borderRight
1563 marginwidth=0
1564 marginheight=0
1565 scrolling=no>');
1566 htp.p('</frameset>');
1567 htp.p('<frame src="qa_ss_core.qlt_buttons"
1568 name=buttonsFrame
1569 marginwidth=0
1570 marginheight=0
1571 scrolling=no>');
1572
1573
1574 htp.p('</FRAMESET>');
1575 htp.p('</HTML>');
1576
1577 end if; -- end icx session
1578 EXCEPTION
1579 WHEN OTHERS THEN
1580 IF plan_name_cur%ISOPEN THEN
1581 CLOSE plan_name_cur;
1582 END IF;
1583 htp.p('Exception in procedure draw_frames');
1584 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
1585 END draw_frames;
1586 -------------------------------------------------------------------------------
1587
1588 procedure qlt_buttons
1589
1590 IS
1591
1592 l_language_code varchar2(30);
1593 msg varchar2(2000);
1594 l_profile_val NUMBER;
1595
1596 BEGIN
1597 if (icx_sec.validatesession) then
1598 l_language_code := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
1599 if (l_language_code is Null) then
1600 l_language_code := 'US';
1601 end if;
1602
1603 fnd_message.clear;
1604 fnd_message.set_name('QA','QA_SS_SUBMIT_DATA');
1605 msg := fnd_message.get;
1606 msg := substr(msg, 1, 20);
1607
1608 l_profile_val := FND_PROFILE.VALUE('QA_SS_RECEIVE_NOTIFICATIONS');
1609 -- htp.p('l_profile_val = ' || l_profile_val); htp.nl;
1610
1611 htp.p('<HTML>');
1612 htp.p('<BODY bgcolor=#336699>');
1613
1614 htp.p('<FORM NAME="buttonHiddenF" ACTION="" METHOD="POST">');
1615 htp.formHidden('clear_var', 'Y');
1616 htp.p('</FORM>');
1617
1618 /*
1619 icx_util.DynamicButton(P_ButtonText => msg,
1620 P_ImageFileName => 'FNDBSBMT',
1621 P_OnMouseOverText => msg,
1622 P_HyperTextCall => 'javascript:parent.eqr_submit()',
1623 P_LanguageCode => l_language_code,
1624 P_JavaScriptFlag => FALSE);
1625 */
1626 htp.p('<TABLE WIDTH=60% CELLPADDING=0 CELLSPACING=0 BORDER=0>');
1627 htp.p('<TR>');
1628 htp.p('<TD VALIGN=MIDDLE ALIGN=LEFT WIDTH=100>');
1629
1630 qa_ss_core.draw_html_button('javascript:parent.eqr_submit()', msg);
1631
1632 fnd_message.set_name('QA', 'QA_SS_CLEAR_FORM');
1633 msg := fnd_message.get;
1634 msg := substr(msg, 1, 20);
1635 /*
1636 icx_util.DynamicButton(P_ButtonText => msg,
1637 P_ImageFileName => 'FNDBCLR',
1638 P_OnMouseOverText => msg,
1639 P_HyperTextCall => 'javascript:parent.clr_form()',
1640 P_LanguageCode => l_language_code,
1641 P_JavaScriptFlag => FALSE);
1642 */
1643 htp.p('<TD VALIGN=MIDDLE ALIGN=LEFT WIDTH=100>');
1644 qa_ss_core.draw_html_button('javascript:parent.clr_form()', msg);
1645 htp.p('</TD>');
1646
1647 fnd_message.clear;
1648
1649 -- New code trial below
1650 msg := fnd_message.get_string('QA', 'QA_SS_NOTIFY_DIALOG');
1651 msg := substr(msg, 1, 180);
1652
1653 /*
1654 icx_util.DynamicButton(P_ButtonText=>substr(fnd_message.get_string('QA','QA_SS_NOTIFY'),1,23),
1655 P_ImageFileName => 'FNDBSBMT',
1656 P_OnMouseOverText => 'Notify Buyer',
1657 P_HyperTextCall => 'javascript:parent.notify_buyer('''||msg||''')',
1658 P_LanguageCode => l_language_code,
1659 P_JavaScriptFlag => FALSE);
1660 */
1661 IF ( (l_profile_val = 1) OR (l_profile_val IS NULL) ) THEN
1662 htp.p('<TD VALIGN=MIDDLE ALIGN=LEFT WIDTH=100>');
1663 qa_ss_core.draw_html_button('javascript:parent.notify_buyer('''||msg||''')',
1664 substr(fnd_message.get_string('QA','QA_SS_NOTIFY'),1,23) );
1665 htp.p('</TD>');
1666 END IF; -- end check for l_profile_val
1667
1668 htp.p('</TR>');
1669 htp.p('</TABLE>');
1670
1671
1672 htp.p('</BODY>');
1673 htp.p('</HTML>');
1674
1675 end if; -- end icx session
1676
1677 EXCEPTION
1678 WHEN OTHERS THEN
1679 htp.p('Exception in procedure qlt_buttons');
1680 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
1681 END qlt_buttons;
1682 ---------------------------------------------------------------------------------
1683
1684 procedure Default_In_Values (Ctx IN OUT NOCOPY qa_ss_const.Ctx_Table,
1685 Txn_Num IN NUMBER DEFAULT NULL,
1686 PK1 IN VARCHAR2 DEFAULT NULL,
1687 PK2 IN VARCHAR2 DEFAULT NULL,
1688 PK3 IN VARCHAR2 DEFAULT NULL,
1689 PK4 IN VARCHAR2 DEFAULT NULL,
1690 PK5 IN VARCHAR2 DEFAULT NULL,
1691 PK6 IN VARCHAR2 DEFAULT NULL,
1692 PK7 IN VARCHAR2 DEFAULT NULL,
1693 PK8 IN VARCHAR2 DEFAULT NULL,
1694 PK9 IN VARCHAR2 DEFAULT NULL,
1695 PK10 IN VARCHAR2 DEFAULT NULL,
1696 X_Po_Agent_Id OUT NOCOPY NUMBER,
1697 X_Item_Id OUT NOCOPY NUMBER,
1698 X_Po_Header_Id OUT NOCOPY NUMBER,
1699 X_Wip_Entity_Type OUT NOCOPY NUMBER,
1700 X_Wip_Rep_Sch_Id OUT NOCOPY NUMBER,
1701 X_Po_Release_Id OUT NOCOPY NUMBER,
1702 X_Po_Line_Id OUT NOCOPY NUMBER,
1703 X_Line_Location_Id OUT NOCOPY NUMBER,
1704 X_Po_Distribution_Id OUT NOCOPY NUMBER,
1705 X_Wip_Entity_Id OUT NOCOPY NUMBER,
1706 X_Wip_Line_Id OUT NOCOPY NUMBER,
1707 X_Po_Shipment_Id OUT NOCOPY NUMBER,
1708 X_Organization_Id OUT NOCOPY NUMBER
1709 )
1710
1711 IS
1712 not_selfserve EXCEPTION;
1713 BEGIN
1714
1715 If (Txn_Num = 100) Then -- OSP Txn
1716 qa_ss_osp.default_osp_values(Ctx, Txn_Num, PK1, PK2, PK3,
1717 PK4, PK5, PK6, PK7, PK8, PK9, PK10, X_Po_Agent_Id,
1718 X_Item_Id, X_Po_Header_Id, X_Wip_Entity_Type,
1719 X_Wip_Rep_Sch_Id, X_Po_Release_Id, X_Po_Line_Id,
1720 X_Line_Location_Id, X_Po_Distribution_Id, X_Wip_Entity_Id,
1721 X_Wip_Line_Id, X_Po_Shipment_Id, X_Organization_Id);
1722 Elsif (Txn_Num = 110) Then -- Shipments Txn
1723 qa_ss_ship.default_ship_values(Ctx, Txn_Num, PK1, PK2, PK3,
1724 PK4, PK5, PK6, PK7, PK8, PK9, PK10, X_Po_Agent_Id,
1725 X_Item_Id, X_Po_Header_Id,
1726 X_Wip_Entity_Type, X_Wip_Rep_Sch_Id,
1727 X_Po_Release_Id, X_Po_Line_Id,
1728 X_Line_Location_Id, X_Po_Distribution_Id,
1729 X_Wip_Entity_Id, X_Wip_Line_Id, X_Po_Shipment_Id,
1730 X_Organization_Id);
1731 ELSE
1732 Raise not_selfserve;
1733
1734 END IF; -- end if osp txn
1735
1736
1737
1738 EXCEPTION
1739 WHEN not_selfserve Then
1740 htp.p('Exception in procedure Default_In_Values');
1741 htp.p('This is NOT A self-service Transaction! Alert!!!');
1742 WHEN OTHERS THEN
1743 htp.p('Exception in procedure Default_In_Values');
1744 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
1745
1746 END Default_In_Values;
1747
1748 ------------------------------------------------------------------------------------
1749
1750 procedure VQR(plan_id_i IN qa_plans.plan_id%TYPE,
1751 start_row IN NUMBER DEFAULT 1,
1752 end_row IN NUMBER DEFAULT 20,
1753 ss_where_clause in varchar2 default null)
1754
1755 IS
1756 l_language_code varchar2(30);
1757 num_of_col NUMBER;
1758 vname VARCHAR2(30) := NULL;
1759 dyn_cur INTEGER;
1760 ignore INTEGER;
1761 tempstr VARCHAR2(150);
1762 ind NUMBER := 0;
1763 msg VARCHAR2(2000);
1764
1765 Prompt_Arr qa_ss_const.var30_table;
1766
1767 charname qa_ss_const.var30_table;
1768
1769 charid qa_ss_const.num_table;
1770 sql_st VARCHAR2(32000) := NULL;
1771 plan_name_i qa_plans.name%TYPE := NULL;
1772
1773 row_color VARCHAR2(10) := 'BLUE';
1774
1775 l_query_size NUMBER := 20;
1776 r_cnt NUMBER;
1777 srow_st varchar2(1000);
1778 erow_st varchar2(1000);
1779 more_records BOOLEAN;
1780 ss_w_c varchar2(5000) := NULL;
1781
1782 CURSOR char_cur IS
1783 select qpc.prompt_sequence, qc.char_id, qc.name, qc.prompt, qpc.enabled_flag
1784 from qa_plan_chars qpc, qa_chars qc
1785 where qpc.plan_id = plan_id_i
1786 AND qpc.char_id = qc.char_id
1787 AND qpc.enabled_flag = 1
1788 ORDER BY qpc.prompt_sequence;
1789
1790 BEGIN
1791 -- htp.p('vqr procedure entered');
1792 if (icx_sec.validatesession) then
1793
1794 fnd_message.clear;
1795 l_language_code := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
1796
1797
1798 -- htp.p('after lang code: ' || l_language_code);
1799 -- htp.p('ss_where='||ss_where_clause); htp.nl;
1800 if (ss_where_clause is not null) then
1801 ss_w_c := wfa_html.conv_special_url_chars(ss_where_clause);
1802 end if;
1803 num_of_col := 0;
1804
1805 select qp.name into plan_name_i
1806 from qa_plans qp
1807 where qp.plan_id = plan_id_i;
1808
1809 vname := 'Q_' || replace(substr(plan_name_i,1,25),' ','_') || '_V';
1810 vname := UPPER(vname);
1811
1812 htp.p('<HTML>');
1813 htp.p('<HEAD>');
1814 htp.p('</HEAD>');
1815 htp.p('<BODY bgcolor="#cccccc">');
1816
1817 fnd_message.set_name('QA', 'QA_SS_VQR');
1818 msg := fnd_message.get;
1819 msg := substr(msg, 1, 40);
1820 /*
1821 icx_plug_utilities.toolbar(msg, p_language_code=>l_language_code,
1822 p_disp_help=>'Y',p_disp_exit=>'Y');
1823 icx_plug_utilities.plugbanner(plan_name_i);
1824 */
1825
1826 htp.p('Records ' || to_char(start_row) || ' to ' || to_char(end_row));
1827
1828 htp.tableOpen(calign=>'CENTER', cborder=>'BORDER=2', cattributes=>'CELLPADDING=2');
1829 htp.tableRowOpen(cattributes=>'BGCOLOR="#336699"');
1830
1831 -- htp.p('Before cursor loop. msg = '||msg);
1832
1833
1834 FOR char_rec IN char_cur
1835 LOOP
1836 num_of_col := num_of_col + 1;
1837 htp.tableHeader(cvalue=>'<font color=#ffffff>'||char_rec.prompt||'</font>', calign=>'CENTER');
1838 Prompt_Arr(num_of_col) := char_rec.prompt;
1839 charname(num_of_col) := replace(UPPER(char_rec.name),' ','_');
1840 charid(num_of_col) := char_rec.char_id;
1841 END LOOP; -- end char_rec loop
1842 -- htp.p('after cur loop');
1843
1844 htp.tableHeader(cvalue=>'<font color=#ffffff>'||fnd_message.get_string('QA','QA_SS_CREATED_BY')||'</font>', calign=>'CENTER');
1845 htp.tableHeader(cvalue=>'<font color=#ffffff>'||fnd_message.get_string('QA','QA_SS_COLLECTION')||'</font>', calign=>'CENTER');
1846 htp.tableHeader(cvalue=>'<font color=#ffffff>'||fnd_message.get_string('QA','QA_SS_ENTRY_DATE')||'</font>', calign=>'CENTER');
1847 htp.tableRowClose;
1848 htp.p('<TR></TR><TR></TR>'); -- This is for UI Standard
1849
1850 -- htp.p('Before call to build vqr sql');
1851
1852
1853 -- Call OB's pkg
1854 sql_st := qa_results_interface_pkg.BUILD_VQR_SQL(plan_id_i,
1855 ss_where_clause);
1856 -- htp.p('Sql stmt returned is: ' || sql_st);
1857
1858
1859 dyn_cur := dbms_sql.open_cursor;
1860 dbms_sql.parse(dyn_cur, sql_st, dbms_sql.v7);
1861
1862 FOR i IN 1..num_of_col+3 -- +3 is for createdby,collectionid and entry date
1863 -- standard columns
1864 Loop
1865 Dbms_sql.define_column(dyn_cur, i, tempstr, 150);
1866 End Loop; --end for loop for define columns
1867
1868 ignore := dbms_sql.execute(dyn_cur);
1869
1870 r_cnt := 0;
1871 more_records := TRUE;
1872 LOOP
1873 If dbms_sql.fetch_rows(dyn_cur) > 0 Then
1874 r_cnt := r_cnt+1;
1875 if (r_cnt > end_row) then
1876 exit;
1877 end if;
1878
1879 if (r_cnt >= start_row) then
1880 if (row_color = 'BLUE') Then
1881 htp.tableRowOpen(cattributes=>'BGCOLOR="#99ccff"');
1882 row_color := 'WHITE';
1883 else
1884 htp.tableRowOpen(cattributes=>'BGCOLOR="#ffffff"');
1885 row_color := 'BLUE';
1886 end if; -- end if for row_color
1887 FOR i IN 1..num_of_col+3
1888 Loop
1889 Dbms_Sql.column_value(dyn_cur, i, tempstr);
1890 htp.tableData(NVL(tempstr, ' '));
1891 End Loop; -- End for loop inside of dyn cursor loop
1892 htp.tableRowClose;
1893
1894 end if; -- end r_cnt check
1895 ELSE
1896 -- no more row to process
1897
1898 more_records := FALSE;
1899 Exit;
1900 END IF; -- if fetch rows
1901
1902 END LOOP;
1903
1904 /* -- commenting out becos bottom table header not part of new UI
1905 htp.p('<TR></TR><TR></TR>');
1906 htp.tableRowOpen(cattributes=>'BGCOLOR="#83C1C1"');
1907 FOR i IN 1..num_of_col
1908 LOOP
1909 htp.tableHeader(cvalue=>Prompt_Arr(i), calign=>'CENTER');
1910 END LOOP;
1911 htp.tableHeader(cvalue=>'Created By', calign=>'CENTER');
1912 htp.tableHeader(cvalue=>'Collection', calign=>'CENTER');
1913 htp.tableHeader(cvalue=>'Entry Date', calign=>'CENTER');
1914 htp.tableRowClose;
1915 */
1916
1917 htp.tableClose;
1918
1919 srow_st := 'qa_ss_core.VQR?plan_id_i='|| plan_id_i
1920 || '&' || 'start_row=' || to_char(start_row-l_query_size)
1921 || '&' || 'end_row=' || to_char(start_row-1)
1922 || '&' || 'ss_where_clause=' || ss_w_c;
1923
1924
1925 erow_st := 'qa_ss_core.VQR?plan_id_i='|| plan_id_i
1926 || '&' || 'start_row=' || to_char(end_row+1)
1927 || '&' || 'end_row=' || to_char(end_row+l_query_size)
1928 || '&' || 'ss_where_clause=' || ss_w_c;
1929
1930
1931
1932 if (start_row > 1) then
1933 htp.anchor(srow_st, fnd_message.get_string('QA','QA_SS_PREVIOUS'));
1934 end if;
1935
1936 htp.p(' -------- ');
1937
1938 if (more_records = TRUE) then
1939 htp.anchor(erow_st, fnd_message.get_string('QA','QA_SS_NEXT'));
1940 end if;
1941
1942 htp.p('</BODY>');
1943 htp.p('</HTML>');
1944
1945 DBMS_SQL.Close_Cursor(dyn_cur);
1946
1947
1948
1949 end if; -- end icx session
1950
1951
1952 EXCEPTION
1953 WHEN OTHERS THEN
1954 IF DBMS_SQL.IS_OPEN(dyn_cur) Then
1955 DBMS_SQL.CLOSE_CURSOR(dyn_cur);
1956 END IF;
1957 Raise;
1958 htp.p('Exception in procedure VQR');
1959 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
1960
1961 END VQR;
1962 -------------------------------------------------------------------------------------
1963
1964 procedure draw_display_field ( disp_text IN VARCHAR2 )
1965 IS
1966
1967 BEGIN
1968 NULL;
1969
1970 EXCEPTION
1971 WHEN OTHERS THEN
1972 htp.p('Exception in procedure draw_display_field');
1973 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
1974
1975 END draw_display_field;
1976 ---------------------------------------------------------------
1977
1978 procedure draw_input_field (itemname IN VARCHAR2, def_value IN VARCHAR2, size_i IN VARCHAR2 )
1979 IS
1980
1981 BEGIN
1982
1983 NULL;
1984
1985 EXCEPTION
1986 WHEN OTHERS THEN
1987 htp.p('Exception in procedure draw_input_field');
1988 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
1989
1990 END draw_input_field;
1991 -------------------------------------------------------------------------------
1992
1993 /*
1994 procedure draw_lov_button ( ............ )
1995 IS
1996
1997 BEGIN
1998
1999 EXCEPTION
2000 WHEN OTHERS THEN
2001 htp.p('Exception in procedure draw_lov_button');
2002 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
2003 END draw_lov_button;
2004 */
2005 ------------------------------------------------------------------------------
2006
2007 procedure draw_prompt ( prompt IN VARCHAR2, cspan IN NUMBER )
2008 IS
2009
2010 BEGIN
2011 NULL;
2012
2013 EXCEPTION
2014 WHEN OTHERS THEN
2015 htp.p('Exception in procedure draw_prompt');
2016 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
2017
2018 END draw_prompt;
2019 ---------------------------------------------------------------------------------
2020
2021 function is_empty_row (
2022 r IN NUMBER,
2023 charid_tab IN qa_ss_const.num_table,
2024 Txn_Num IN NUMBER DEFAULT NULL,
2025 p_col1 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2026 p_col2 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2027 p_col3 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2028 p_col4 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2029 p_col5 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2030 p_col6 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2031 p_col7 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2032 p_col8 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2033 p_col9 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2034 p_col10 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2035 p_col11 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2036 p_col12 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2037 p_col13 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2038 p_col14 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2039 p_col15 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2040 p_col16 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2041 p_col17 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2042 p_col18 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2043 p_col19 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2044 p_col20 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2045 p_col21 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2046 p_col22 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2047 p_col23 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2048 p_col24 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2049 p_col25 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2050 p_col26 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2051 p_col27 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2052 p_col28 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2053 p_col29 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2054 p_col30 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2055 p_col31 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2056 p_col32 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2057 p_col33 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2058 p_col34 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2059 p_col35 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2060 p_col36 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2061 p_col37 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2062 p_col38 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2063 p_col39 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2064 p_col40 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2065 p_col41 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2066 p_col42 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2067 p_col43 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2068 p_col44 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2069 p_col45 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2070 p_col46 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2071 p_col47 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2072 p_col48 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2073 p_col49 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2074 p_col50 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2075 p_col51 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2076 p_col52 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2077 p_col53 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2078 p_col54 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2079 p_col55 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2080 p_col56 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2081 p_col57 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2082 p_col58 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2083 p_col59 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2084 p_col60 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2085 p_col61 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2086 p_col62 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2087 p_col63 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2088 p_col64 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2089 p_col65 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2090 p_col66 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2091 p_col67 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2092 p_col68 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2093 p_col69 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2094 p_col70 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2095 p_col71 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2096 p_col72 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2097 p_col73 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2098 p_col74 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2099 p_col75 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2100 p_col76 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2101 p_col77 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2102 p_col78 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2103 p_col79 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2104 p_col80 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2105 p_col81 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2106 p_col82 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2107 p_col83 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2108 p_col84 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2109 p_col85 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2110 p_col86 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2111 p_col87 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2112 p_col88 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2113 p_col89 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2114 p_col90 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2115 p_col91 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2116 p_col92 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2117 p_col93 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2118 p_col94 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2119 p_col95 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2120 p_col96 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2121 p_col97 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2122 p_col98 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2123 p_col99 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2124 p_col100 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2125 p_col101 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2126 p_col102 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2127 p_col103 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2128 p_col104 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2129 p_col105 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2130 p_col106 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2131 p_col107 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2132 p_col108 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2133 p_col109 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2134 p_col110 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2135 p_col111 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2136 p_col112 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2137 p_col113 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2138 p_col114 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2139 p_col115 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2140 p_col116 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2141 p_col117 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2142 p_col118 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2143 p_col119 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2144 p_col120 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2145 p_col121 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2146 p_col122 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2147 p_col123 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2148 p_col124 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2149 p_col125 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2150 p_col126 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2151 p_col127 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2152 p_col128 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2153 p_col129 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2154 p_col130 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2155 p_col131 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2156 p_col132 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2157 p_col133 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2158 p_col134 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2159 p_col135 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2160 p_col136 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2161 p_col137 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2162 p_col138 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2163 p_col139 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2164 p_col140 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2165 p_col141 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2166 p_col142 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2167 p_col143 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2168 p_col144 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2169 p_col145 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2170 p_col146 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2171 p_col147 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2172 p_col148 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2173 p_col149 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2174 p_col150 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2175 p_col151 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2176 p_col152 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2177 p_col153 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2178 p_col154 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2179 p_col155 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2180 p_col156 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2181 p_col157 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2182 p_col158 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2183 p_col159 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2184 p_col160 IN qa_ss_const.Eqr_Array DEFAULT qa_ss_const.def_array,
2185 planid IN NUMBER DEFAULT NULL )
2186 Return Boolean
2187
2188 IS
2189
2190 BEGIN
2191
2192 If charid_tab.Exists(1) Then
2193 If Not(CxE(charid_tab(1),Txn_Num)) Then
2194 If (p_col1(r) is Not Null) Then
2195 return FALSE;
2196 End If;
2197 End If;
2198 End If;
2199 -------------------------------------
2200 If charid_tab.Exists(2) Then
2201 If Not(CxE(charid_tab(2),Txn_Num)) Then
2202 If (p_col2(r) is Not Null) Then
2203 return FALSE;
2204 End If;
2205 End If;
2206 End If;
2207 -------------------------------------------
2208 If charid_tab.Exists(3) Then
2209 If Not(CxE(charid_tab(3),Txn_Num)) Then
2210 If (p_col3(r) is Not Null) Then
2211 return FALSE;
2212 End If;
2213 End If;
2214 End If;
2215 If charid_tab.Exists(4) Then
2216 If Not(CxE(charid_tab(4),Txn_Num)) Then
2217 If (p_col4(r) is Not Null) Then
2218 return FALSE;
2219 End If;
2220 End If;
2221 End If;
2222 If charid_tab.Exists(5) Then
2223 If Not(CxE(charid_tab(5),Txn_Num)) Then
2224 If (p_col5(r) is Not Null) Then
2225 return FALSE;
2226 End If;
2227 End If;
2228 End If;
2229 If charid_tab.Exists(6) Then
2230 If Not(CxE(charid_tab(6),Txn_Num)) Then
2231 If (p_col6(r) is Not Null) Then
2232 return FALSE;
2233 End If;
2234 End If;
2235 End If;
2236 If charid_tab.Exists(7) Then
2237 If Not(CxE(charid_tab(7),Txn_Num)) Then
2238 If (p_col7(r) is Not Null) Then
2239 return FALSE;
2240 End If;
2241 End If;
2242 End If;
2243 If charid_tab.Exists(8) Then
2244 If Not(CxE(charid_tab(8),Txn_Num)) Then
2245 If (p_col8(r) is Not Null) Then
2246 return FALSE;
2247 End If;
2248 End If;
2249 End If;
2250 If charid_tab.Exists(9) Then
2251 If Not(CxE(charid_tab(9),Txn_Num)) Then
2252 If (p_col9(r) is Not Null) Then
2253 return FALSE;
2254 End If;
2255 End If;
2256 End If;
2257 If charid_tab.Exists(10) Then
2258 If Not(CxE(charid_tab(10),Txn_Num)) Then
2259 If (p_col10(r) is Not Null) Then
2260 return FALSE;
2261 End If;
2262 End If;
2263 End If;
2264 If charid_tab.Exists(11) Then
2265 If Not(CxE(charid_tab(11),Txn_Num)) Then
2266 If (p_col11(r) is Not Null) Then
2267 return FALSE;
2268 End If;
2269 End If;
2270 End If;
2271 If charid_tab.Exists(12) Then
2272 If Not(CxE(charid_tab(12),Txn_Num)) Then
2273 If (p_col12(r) is Not Null) Then
2274 return FALSE;
2275 End If;
2276 End If;
2277 End If;
2278 If charid_tab.Exists(13) Then
2279 If Not(CxE(charid_tab(13),Txn_Num)) Then
2280 If (p_col13(r) is Not Null) Then
2281 return FALSE;
2282 End If;
2283 End If;
2284 End If;
2285 If charid_tab.Exists(14) Then
2286 If Not(CxE(charid_tab(14),Txn_Num)) Then
2287 If (p_col14(r) is Not Null) Then
2288 return FALSE;
2289 End If;
2290 End If;
2291 End If;
2292 If charid_tab.Exists(15) Then
2293 If Not(CxE(charid_tab(15),Txn_Num)) Then
2294 If (p_col15(r) is Not Null) Then
2295 return FALSE;
2296 End If;
2297 End If;
2298 End If;
2299 If charid_tab.Exists(16) Then
2300 If Not(CxE(charid_tab(16),Txn_Num)) Then
2301 If (p_col16(r) is Not Null) Then
2302 return FALSE;
2303 End If;
2304 End If;
2305 End If;
2306 If charid_tab.Exists(17) Then
2307 If Not(CxE(charid_tab(17),Txn_Num)) Then
2308 If (p_col17(r) is Not Null) Then
2309 return FALSE;
2310 End If;
2311 End If;
2312 End If;
2313 If charid_tab.Exists(18) Then
2314 If Not(CxE(charid_tab(18),Txn_Num)) Then
2315 If (p_col18(r) is Not Null) Then
2316 return FALSE;
2317 End If;
2318 End If;
2319 End If;
2320 If charid_tab.Exists(19) Then
2321 If Not(CxE(charid_tab(19),Txn_Num)) Then
2322 If (p_col19(r) is Not Null) Then
2323 return FALSE;
2324 End If;
2325 End If;
2326 End If;
2327 If charid_tab.Exists(20) Then
2328 If Not(CxE(charid_tab(20),Txn_Num)) Then
2329 If (p_col20(r) is Not Null) Then
2330 return FALSE;
2331 End If;
2332 End If;
2333 End If;
2334 If charid_tab.Exists(21) Then
2335 If Not(CxE(charid_tab(21),Txn_Num)) Then
2336 If (p_col21(r) is Not Null) Then
2337 return FALSE;
2338 End If;
2339 End If;
2340 End If;
2341 If charid_tab.Exists(22) Then
2342 If Not(CxE(charid_tab(22),Txn_Num)) Then
2343 If (p_col22(r) is Not Null) Then
2344 return FALSE;
2345 End If;
2346 End If;
2347 End If;
2348 If charid_tab.Exists(23) Then
2349 If Not(CxE(charid_tab(23),Txn_Num)) Then
2350 If (p_col23(r) is Not Null) Then
2351 return FALSE;
2352 End If;
2353 End If;
2354 End If;
2355 If charid_tab.Exists(24) Then
2356 If Not(CxE(charid_tab(24),Txn_Num)) Then
2357 If (p_col24(r) is Not Null) Then
2358 return FALSE;
2359 End If;
2360 End If;
2361 End If;
2362 If charid_tab.Exists(25) Then
2363 If Not(CxE(charid_tab(25),Txn_Num)) Then
2364 If (p_col25(r) is Not Null) Then
2365 return FALSE;
2366 End If;
2367 End If;
2368 End If;
2369 If charid_tab.Exists(26) Then
2370 If Not(CxE(charid_tab(26),Txn_Num)) Then
2371 If (p_col26(r) is Not Null) Then
2372 return FALSE;
2373 End If;
2374 End If;
2375 End If;
2376 If charid_tab.Exists(27) Then
2377 If Not(CxE(charid_tab(27),Txn_Num)) Then
2378 If (p_col27(r) is Not Null) Then
2379 return FALSE;
2380 End If;
2381 End If;
2382 End If;
2383 If charid_tab.Exists(28) Then
2384 If Not(CxE(charid_tab(28),Txn_Num)) Then
2385 If (p_col28(r) is Not Null) Then
2386 return FALSE;
2387 End If;
2388 End If;
2389 End If;
2390 If charid_tab.Exists(29) Then
2391 If Not(CxE(charid_tab(29),Txn_Num)) Then
2392 If (p_col29(r) is Not Null) Then
2393 return FALSE;
2394 End If;
2395 End If;
2396 End If;
2397 If charid_tab.Exists(30) Then
2398 If Not(CxE(charid_tab(30),Txn_Num)) Then
2399 If (p_col30(r) is Not Null) Then
2400 return FALSE;
2401 End If;
2402 End If;
2403 End If;
2404 If charid_tab.Exists(31) Then
2405 If Not(CxE(charid_tab(31),Txn_Num)) Then
2406 If (p_col31(r) is Not Null) Then
2407 return FALSE;
2408 End If;
2409 End If;
2410 End If;
2411 If charid_tab.Exists(32) Then
2412 If Not(CxE(charid_tab(32),Txn_Num)) Then
2413 If (p_col32(r) is Not Null) Then
2414 return FALSE;
2415 End If;
2416 End If;
2417 End If;
2418 If charid_tab.Exists(33) Then
2419 If Not(CxE(charid_tab(33),Txn_Num)) Then
2420 If (p_col33(r) is Not Null) Then
2421 return FALSE;
2422 End If;
2423 End If;
2424 End If;
2425 If charid_tab.Exists(34) Then
2426 If Not(CxE(charid_tab(34),Txn_Num)) Then
2427 If (p_col34(r) is Not Null) Then
2428 return FALSE;
2429 End If;
2430 End If;
2431 End If;
2432 If charid_tab.Exists(35) Then
2433 If Not(CxE(charid_tab(35),Txn_Num)) Then
2434 If (p_col35(r) is Not Null) Then
2435 return FALSE;
2436 End If;
2437 End If;
2438 End If;
2439 If charid_tab.Exists(36) Then
2440 If Not(CxE(charid_tab(36),Txn_Num)) Then
2441 If (p_col36(r) is Not Null) Then
2442 return FALSE;
2443 End If;
2444 End If;
2445 End If;
2446 If charid_tab.Exists(37) Then
2447 If Not(CxE(charid_tab(37),Txn_Num)) Then
2448 If (p_col37(r) is Not Null) Then
2449 return FALSE;
2450 End If;
2451 End If;
2452 End If;
2453 If charid_tab.Exists(38) Then
2454 If Not(CxE(charid_tab(38),Txn_Num)) Then
2455 If (p_col38(r) is Not Null) Then
2456 return FALSE;
2457 End If;
2458 End If;
2459 End If;
2460 If charid_tab.Exists(39) Then
2461 If Not(CxE(charid_tab(39),Txn_Num)) Then
2462 If (p_col39(r) is Not Null) Then
2463 return FALSE;
2464 End If;
2465 End If;
2466 End If;
2467 If charid_tab.Exists(40) Then
2468 If Not(CxE(charid_tab(40),Txn_Num)) Then
2469 If (p_col40(r) is Not Null) Then
2470 return FALSE;
2471 End If;
2472 End If;
2473 End If;
2474 If charid_tab.Exists(41) Then
2475 If Not(CxE(charid_tab(41),Txn_Num)) Then
2476 If (p_col41(r) is Not Null) Then
2477 return FALSE;
2478 End If;
2479 End If;
2480 End If;
2481 If charid_tab.Exists(42) Then
2482 If Not(CxE(charid_tab(42),Txn_Num)) Then
2483 If (p_col42(r) is Not Null) Then
2484 return FALSE;
2485 End If;
2486 End If;
2487 End If;
2488 If charid_tab.Exists(43) Then
2489 If Not(CxE(charid_tab(43),Txn_Num)) Then
2490 If (p_col43(r) is Not Null) Then
2491 return FALSE;
2492 End If;
2493 End If;
2494 End If;
2495 If charid_tab.Exists(44) Then
2496 If Not(CxE(charid_tab(44),Txn_Num)) Then
2497 If (p_col44(r) is Not Null) Then
2498 return FALSE;
2499 End If;
2500 End If;
2501 End If;
2502 If charid_tab.Exists(45) Then
2503 If Not(CxE(charid_tab(45),Txn_Num)) Then
2504 If (p_col45(r) is Not Null) Then
2505 return FALSE;
2506 End If;
2507 End If;
2508 End If;
2509 If charid_tab.Exists(46) Then
2510 If Not(CxE(charid_tab(46),Txn_Num)) Then
2511 If (p_col46(r) is Not Null) Then
2512 return FALSE;
2513 End If;
2514 End If;
2515 End If;
2516 If charid_tab.Exists(47) Then
2517 If Not(CxE(charid_tab(47),Txn_Num)) Then
2518 If (p_col47(r) is Not Null) Then
2519 return FALSE;
2520 End If;
2521 End If;
2522 End If;
2523 If charid_tab.Exists(48) Then
2524 If Not(CxE(charid_tab(48),Txn_Num)) Then
2525 If (p_col48(r) is Not Null) Then
2526 return FALSE;
2527 End If;
2528 End If;
2529 End If;
2530 If charid_tab.Exists(49) Then
2531 If Not(CxE(charid_tab(49),Txn_Num)) Then
2532 If (p_col49(r) is Not Null) Then
2533 return FALSE;
2534 End If;
2535 End If;
2536 End If;
2537 If charid_tab.Exists(50) Then
2538 If Not(CxE(charid_tab(50),Txn_Num)) Then
2539 If (p_col50(r) is Not Null) Then
2540 return FALSE;
2541 End If;
2542 End If;
2543 End If;
2544 If charid_tab.Exists(51) Then
2545 If Not(CxE(charid_tab(51),Txn_Num)) Then
2546 If (p_col51(r) is Not Null) Then
2547 return FALSE;
2548 End If;
2549 End If;
2550 End If;
2551 If charid_tab.Exists(52) Then
2552 If Not(CxE(charid_tab(52),Txn_Num)) Then
2553 If (p_col52(r) is Not Null) Then
2554 return FALSE;
2555 End If;
2556 End If;
2557 End If;
2558 If charid_tab.Exists(53) Then
2559 If Not(CxE(charid_tab(53),Txn_Num)) Then
2560 If (p_col53(r) is Not Null) Then
2561 return FALSE;
2562 End If;
2563 End If;
2564 End If;
2565 If charid_tab.Exists(54) Then
2566 If Not(CxE(charid_tab(54),Txn_Num)) Then
2567 If (p_col54(r) is Not Null) Then
2568 return FALSE;
2569 End If;
2570 End If;
2571 End If;
2572 If charid_tab.Exists(55) Then
2573 If Not(CxE(charid_tab(55),Txn_Num)) Then
2574 If (p_col55(r) is Not Null) Then
2575 return FALSE;
2576 End If;
2577 End If;
2578 End If;
2579 If charid_tab.Exists(56) Then
2580 If Not(CxE(charid_tab(56),Txn_Num)) Then
2581 If (p_col56(r) is Not Null) Then
2582 return FALSE;
2583 End If;
2584 End If;
2585 End If;
2586 If charid_tab.Exists(57) Then
2587 If Not(CxE(charid_tab(57),Txn_Num)) Then
2588 If (p_col57(r) is Not Null) Then
2589 return FALSE;
2590 End If;
2591 End If;
2592 End If;
2593 If charid_tab.Exists(58) Then
2594 If Not(CxE(charid_tab(58),Txn_Num)) Then
2595 If (p_col58(r) is Not Null) Then
2596 return FALSE;
2597 End If;
2598 End If;
2599 End If;
2600 If charid_tab.Exists(59) Then
2601 If Not(CxE(charid_tab(59),Txn_Num)) Then
2602 If (p_col59(r) is Not Null) Then
2603 return FALSE;
2604 End If;
2605 End If;
2606 End If;
2607 If charid_tab.Exists(60) Then
2608 If Not(CxE(charid_tab(60),Txn_Num)) Then
2609 If (p_col60(r) is Not Null) Then
2610 return FALSE;
2611 End If;
2612 End If;
2613 End If;
2614 If charid_tab.Exists(61) Then
2615 If Not(CxE(charid_tab(61),Txn_Num)) Then
2616 If (p_col61(r) is Not Null) Then
2617 return FALSE;
2618 End If;
2619 End If;
2620 End If;
2621 If charid_tab.Exists(62) Then
2622 If Not(CxE(charid_tab(62),Txn_Num)) Then
2623 If (p_col62(r) is Not Null) Then
2624 return FALSE;
2625 End If;
2626 End If;
2627 End If;
2628 If charid_tab.Exists(63) Then
2629 If Not(CxE(charid_tab(63),Txn_Num)) Then
2630 If (p_col63(r) is Not Null) Then
2631 return FALSE;
2632 End If;
2633 End If;
2634 End If;
2635 If charid_tab.Exists(64) Then
2636 If Not(CxE(charid_tab(64),Txn_Num)) Then
2637 If (p_col64(r) is Not Null) Then
2638 return FALSE;
2639 End If;
2640 End If;
2641 End If;
2642 If charid_tab.Exists(65) Then
2643 If Not(CxE(charid_tab(65),Txn_Num)) Then
2644 If (p_col65(r) is Not Null) Then
2645 return FALSE;
2646 End If;
2647 End If;
2648 End If;
2649 If charid_tab.Exists(66) Then
2650 If Not(CxE(charid_tab(66),Txn_Num)) Then
2651 If (p_col66(r) is Not Null) Then
2652 return FALSE;
2653 End If;
2654 End If;
2655 End If;
2656 If charid_tab.Exists(67) Then
2657 If Not(CxE(charid_tab(67),Txn_Num)) Then
2658 If (p_col67(r) is Not Null) Then
2659 return FALSE;
2660 End If;
2661 End If;
2662 End If;
2663 If charid_tab.Exists(68) Then
2664 If Not(CxE(charid_tab(68),Txn_Num)) Then
2665 If (p_col68(r) is Not Null) Then
2666 return FALSE;
2667 End If;
2668 End If;
2669 End If;
2670 If charid_tab.Exists(69) Then
2671 If Not(CxE(charid_tab(69),Txn_Num)) Then
2672 If (p_col69(r) is Not Null) Then
2673 return FALSE;
2674 End If;
2675 End If;
2676 End If;
2677 If charid_tab.Exists(70) Then
2678 If Not(CxE(charid_tab(70),Txn_Num)) Then
2679 If (p_col70(r) is Not Null) Then
2680 return FALSE;
2681 End If;
2682 End If;
2683 End If;
2684 If charid_tab.Exists(71) Then
2685 If Not(CxE(charid_tab(71),Txn_Num)) Then
2686 If (p_col71(r) is Not Null) Then
2687 return FALSE;
2688 End If;
2689 End If;
2690 End If;
2691 If charid_tab.Exists(72) Then
2692 If Not(CxE(charid_tab(72),Txn_Num)) Then
2693 If (p_col72(r) is Not Null) Then
2694 return FALSE;
2695 End If;
2696 End If;
2697 End If;
2698 If charid_tab.Exists(73) Then
2699 If Not(CxE(charid_tab(73),Txn_Num)) Then
2700 If (p_col73(r) is Not Null) Then
2701 return FALSE;
2702 End If;
2703 End If;
2704 End If;
2705 If charid_tab.Exists(74) Then
2706 If Not(CxE(charid_tab(74),Txn_Num)) Then
2707 If (p_col74(r) is Not Null) Then
2708 return FALSE;
2709 End If;
2710 End If;
2711 End If;
2712 If charid_tab.Exists(75) Then
2713 If Not(CxE(charid_tab(75),Txn_Num)) Then
2714 If (p_col75(r) is Not Null) Then
2715 return FALSE;
2716 End If;
2717 End If;
2718 End If;
2719 If charid_tab.Exists(76) Then
2720 If Not(CxE(charid_tab(76),Txn_Num)) Then
2721 If (p_col76(r) is Not Null) Then
2722 return FALSE;
2723 End If;
2724 End If;
2725 End If;
2726 If charid_tab.Exists(77) Then
2727 If Not(CxE(charid_tab(77),Txn_Num)) Then
2728 If (p_col77(r) is Not Null) Then
2729 return FALSE;
2730 End If;
2731 End If;
2732 End If;
2733 If charid_tab.Exists(78) Then
2734 If Not(CxE(charid_tab(78),Txn_Num)) Then
2735 If (p_col78(r) is Not Null) Then
2736 return FALSE;
2737 End If;
2738 End If;
2739 End If;
2740 If charid_tab.Exists(79) Then
2741 If Not(CxE(charid_tab(79),Txn_Num)) Then
2742 If (p_col79(r) is Not Null) Then
2743 return FALSE;
2744 End If;
2745 End If;
2746 End If;
2747 If charid_tab.Exists(80) Then
2748 If Not(CxE(charid_tab(80),Txn_Num)) Then
2749 If (p_col80(r) is Not Null) Then
2750 return FALSE;
2751 End If;
2752 End If;
2753 End If;
2754 If charid_tab.Exists(81) Then
2755 If Not(CxE(charid_tab(81),Txn_Num)) Then
2756 If (p_col81(r) is Not Null) Then
2757 return FALSE;
2758 End If;
2759 End If;
2760 End If;
2761 If charid_tab.Exists(82) Then
2762 If Not(CxE(charid_tab(82),Txn_Num)) Then
2763 If (p_col82(r) is Not Null) Then
2764 return FALSE;
2765 End If;
2766 End If;
2767 End If;
2768 If charid_tab.Exists(83) Then
2769 If Not(CxE(charid_tab(83),Txn_Num)) Then
2770 If (p_col83(r) is Not Null) Then
2771 return FALSE;
2772 End If;
2773 End If;
2774 End If;
2775 If charid_tab.Exists(84) Then
2776 If Not(CxE(charid_tab(84),Txn_Num)) Then
2777 If (p_col84(r) is Not Null) Then
2778 return FALSE;
2779 End If;
2780 End If;
2781 End If;
2782 If charid_tab.Exists(85) Then
2783 If Not(CxE(charid_tab(85),Txn_Num)) Then
2784 If (p_col85(r) is Not Null) Then
2785 return FALSE;
2786 End If;
2787 End If;
2788 End If;
2789 If charid_tab.Exists(86) Then
2790 If Not(CxE(charid_tab(86),Txn_Num)) Then
2791 If (p_col86(r) is Not Null) Then
2792 return FALSE;
2793 End If;
2794 End If;
2795 End If;
2796 If charid_tab.Exists(87) Then
2797 If Not(CxE(charid_tab(87),Txn_Num)) Then
2798 If (p_col87(r) is Not Null) Then
2799 return FALSE;
2800 End If;
2801 End If;
2802 End If;
2803 If charid_tab.Exists(88) Then
2804 If Not(CxE(charid_tab(88),Txn_Num)) Then
2805 If (p_col88(r) is Not Null) Then
2806 return FALSE;
2807 End If;
2808 End If;
2809 End If;
2810 If charid_tab.Exists(89) Then
2811 If Not(CxE(charid_tab(89),Txn_Num)) Then
2812 If (p_col89(r) is Not Null) Then
2813 return FALSE;
2814 End If;
2815 End If;
2816 End If;
2817 If charid_tab.Exists(90) Then
2818 If Not(CxE(charid_tab(90),Txn_Num)) Then
2819 If (p_col90(r) is Not Null) Then
2820 return FALSE;
2821 End If;
2822 End If;
2823 End If;
2824 If charid_tab.Exists(91) Then
2825 If Not(CxE(charid_tab(91),Txn_Num)) Then
2826 If (p_col91(r) is Not Null) Then
2827 return FALSE;
2828 End If;
2829 End If;
2830 End If;
2831 If charid_tab.Exists(92) Then
2832 If Not(CxE(charid_tab(92),Txn_Num)) Then
2833 If (p_col92(r) is Not Null) Then
2834 return FALSE;
2835 End If;
2836 End If;
2837 End If;
2838 If charid_tab.Exists(93) Then
2839 If Not(CxE(charid_tab(93),Txn_Num)) Then
2840 If (p_col93(r) is Not Null) Then
2841 return FALSE;
2842 End If;
2843 End If;
2844 End If;
2845 If charid_tab.Exists(94) Then
2846 If Not(CxE(charid_tab(94),Txn_Num)) Then
2847 If (p_col94(r) is Not Null) Then
2848 return FALSE;
2849 End If;
2850 End If;
2851 End If;
2852 If charid_tab.Exists(95) Then
2853 If Not(CxE(charid_tab(95),Txn_Num)) Then
2854 If (p_col95(r) is Not Null) Then
2855 return FALSE;
2856 End If;
2857 End If;
2858 End If;
2859 If charid_tab.Exists(96) Then
2860 If Not(CxE(charid_tab(96),Txn_Num)) Then
2861 If (p_col96(r) is Not Null) Then
2862 return FALSE;
2863 End If;
2864 End If;
2865 End If;
2866 If charid_tab.Exists(97) Then
2867 If Not(CxE(charid_tab(97),Txn_Num)) Then
2868 If (p_col97(r) is Not Null) Then
2869 return FALSE;
2870 End If;
2871 End If;
2872 End If;
2873 If charid_tab.Exists(98) Then
2874 If Not(CxE(charid_tab(98),Txn_Num)) Then
2875 If (p_col98(r) is Not Null) Then
2876 return FALSE;
2877 End If;
2878 End If;
2879 End If;
2880 If charid_tab.Exists(99) Then
2881 If Not(CxE(charid_tab(99),Txn_Num)) Then
2882 If (p_col99(r) is Not Null) Then
2883 return FALSE;
2884 End If;
2885 End If;
2886 End If;
2887 If charid_tab.Exists(100) Then
2888 If Not(CxE(charid_tab(100),Txn_Num)) Then
2889 If (p_col100(r) is Not Null) Then
2890 return FALSE;
2891 End If;
2892 End If;
2893 End If;
2894 If charid_tab.Exists(101) Then
2895 If Not(CxE(charid_tab(101),Txn_Num)) Then
2896 If (p_col101(r) is Not Null) Then
2897 return FALSE;
2898 End If;
2899 End If;
2900 End If;
2901 If charid_tab.Exists(102) Then
2902 If Not(CxE(charid_tab(102),Txn_Num)) Then
2903 If (p_col102(r) is Not Null) Then
2904 return FALSE;
2905 End If;
2906 End If;
2907 End If;
2908 If charid_tab.Exists(103) Then
2909 If Not(CxE(charid_tab(103),Txn_Num)) Then
2910 If (p_col103(r) is Not Null) Then
2911 return FALSE;
2912 End If;
2913 End If;
2914 End If;
2915 If charid_tab.Exists(104) Then
2916 If Not(CxE(charid_tab(104),Txn_Num)) Then
2917 If (p_col104(r) is Not Null) Then
2918 return FALSE;
2919 End If;
2920 End If;
2921 End If;
2922 If charid_tab.Exists(105) Then
2923 If Not(CxE(charid_tab(105),Txn_Num)) Then
2924 If (p_col105(r) is Not Null) Then
2925 return FALSE;
2926 End If;
2927 End If;
2928 End If;
2929 If charid_tab.Exists(106) Then
2930 If Not(CxE(charid_tab(106),Txn_Num)) Then
2931 If (p_col106(r) is Not Null) Then
2932 return FALSE;
2933 End If;
2934 End If;
2935 End If;
2936 If charid_tab.Exists(107) Then
2937 If Not(CxE(charid_tab(107),Txn_Num)) Then
2938 If (p_col107(r) is Not Null) Then
2939 return FALSE;
2940 End If;
2941 End If;
2942 End If;
2943 If charid_tab.Exists(108) Then
2944 If Not(CxE(charid_tab(108),Txn_Num)) Then
2945 If (p_col108(r) is Not Null) Then
2946 return FALSE;
2947 End If;
2948 End If;
2949 End If;
2950 If charid_tab.Exists(109) Then
2951 If Not(CxE(charid_tab(109),Txn_Num)) Then
2952 If (p_col109(r) is Not Null) Then
2953 return FALSE;
2954 End If;
2955 End If;
2956 End If;
2957 If charid_tab.Exists(110) Then
2958 If Not(CxE(charid_tab(110),Txn_Num)) Then
2959 If (p_col110(r) is Not Null) Then
2960 return FALSE;
2961 End If;
2962 End If;
2963 End If;
2964 If charid_tab.Exists(111) Then
2965 If Not(CxE(charid_tab(111),Txn_Num)) Then
2966 If (p_col111(r) is Not Null) Then
2967 return FALSE;
2968 End If;
2969 End If;
2970 End If;
2971 If charid_tab.Exists(112) Then
2972 If Not(CxE(charid_tab(112),Txn_Num)) Then
2973 If (p_col112(r) is Not Null) Then
2974 return FALSE;
2975 End If;
2976 End If;
2977 End If;
2978 If charid_tab.Exists(113) Then
2979 If Not(CxE(charid_tab(113),Txn_Num)) Then
2980 If (p_col113(r) is Not Null) Then
2981 return FALSE;
2982 End If;
2983 End If;
2984 End If;
2985 If charid_tab.Exists(114) Then
2986 If Not(CxE(charid_tab(114),Txn_Num)) Then
2987 If (p_col114(r) is Not Null) Then
2988 return FALSE;
2989 End If;
2990 End If;
2991 End If;
2992 If charid_tab.Exists(115) Then
2993 If Not(CxE(charid_tab(115),Txn_Num)) Then
2994 If (p_col115(r) is Not Null) Then
2995 return FALSE;
2996 End If;
2997 End If;
2998 End If;
2999 If charid_tab.Exists(116) Then
3000 If Not(CxE(charid_tab(116),Txn_Num)) Then
3001 If (p_col116(r) is Not Null) Then
3002 return FALSE;
3003 End If;
3004 End If;
3005 End If;
3006 If charid_tab.Exists(117) Then
3007 If Not(CxE(charid_tab(117),Txn_Num)) Then
3008 If (p_col117(r) is Not Null) Then
3009 return FALSE;
3010 End If;
3011 End If;
3012 End If;
3013 If charid_tab.Exists(118) Then
3014 If Not(CxE(charid_tab(118),Txn_Num)) Then
3015 If (p_col118(r) is Not Null) Then
3016 return FALSE;
3017 End If;
3018 End If;
3019 End If;
3020 If charid_tab.Exists(119) Then
3021 If Not(CxE(charid_tab(119),Txn_Num)) Then
3022 If (p_col119(r) is Not Null) Then
3023 return FALSE;
3024 End If;
3025 End If;
3026 End If;
3027 If charid_tab.Exists(120) Then
3028 If Not(CxE(charid_tab(120),Txn_Num)) Then
3029 If (p_col120(r) is Not Null) Then
3030 return FALSE;
3031 End If;
3032 End If;
3033 End If;
3034 If charid_tab.Exists(121) Then
3035 If Not(CxE(charid_tab(121),Txn_Num)) Then
3036 If (p_col121(r) is Not Null) Then
3037 return FALSE;
3038 End If;
3039 End If;
3040 End If;
3041 If charid_tab.Exists(122) Then
3042 If Not(CxE(charid_tab(122),Txn_Num)) Then
3043 If (p_col122(r) is Not Null) Then
3044 return FALSE;
3045 End If;
3046 End If;
3047 End If;
3048 If charid_tab.Exists(123) Then
3049 If Not(CxE(charid_tab(123),Txn_Num)) Then
3050 If (p_col123(r) is Not Null) Then
3051 return FALSE;
3052 End If;
3053 End If;
3054 End If;
3055 If charid_tab.Exists(124) Then
3056 If Not(CxE(charid_tab(124),Txn_Num)) Then
3057 If (p_col124(r) is Not Null) Then
3058 return FALSE;
3059 End If;
3060 End If;
3061 End If;
3062 If charid_tab.Exists(125) Then
3063 If Not(CxE(charid_tab(125),Txn_Num)) Then
3064 If (p_col125(r) is Not Null) Then
3065 return FALSE;
3066 End If;
3067 End If;
3068 End If;
3069 If charid_tab.Exists(126) Then
3070 If Not(CxE(charid_tab(126),Txn_Num)) Then
3071 If (p_col126(r) is Not Null) Then
3072 return FALSE;
3073 End If;
3074 End If;
3075 End If;
3076 If charid_tab.Exists(127) Then
3077 If Not(CxE(charid_tab(127),Txn_Num)) Then
3078 If (p_col127(r) is Not Null) Then
3079 return FALSE;
3080 End If;
3081 End If;
3082 End If;
3083 If charid_tab.Exists(128) Then
3084 If Not(CxE(charid_tab(128),Txn_Num)) Then
3085 If (p_col128(r) is Not Null) Then
3086 return FALSE;
3087 End If;
3088 End If;
3089 End If;
3090 If charid_tab.Exists(129) Then
3091 If Not(CxE(charid_tab(129),Txn_Num)) Then
3092 If (p_col129(r) is Not Null) Then
3093 return FALSE;
3094 End If;
3095 End If;
3096 End If;
3097 If charid_tab.Exists(130) Then
3098 If Not(CxE(charid_tab(130),Txn_Num)) Then
3099 If (p_col130(r) is Not Null) Then
3100 return FALSE;
3101 End If;
3102 End If;
3103 End If;
3104 If charid_tab.Exists(131) Then
3105 If Not(CxE(charid_tab(131),Txn_Num)) Then
3106 If (p_col131(r) is Not Null) Then
3107 return FALSE;
3108 End If;
3109 End If;
3110 End If;
3111 If charid_tab.Exists(132) Then
3112 If Not(CxE(charid_tab(132),Txn_Num)) Then
3113 If (p_col132(r) is Not Null) Then
3114 return FALSE;
3115 End If;
3116 End If;
3117 End If;
3118 If charid_tab.Exists(133) Then
3119 If Not(CxE(charid_tab(133),Txn_Num)) Then
3120 If (p_col133(r) is Not Null) Then
3121 return FALSE;
3122 End If;
3123 End If;
3124 End If;
3125 If charid_tab.Exists(134) Then
3126 If Not(CxE(charid_tab(134),Txn_Num)) Then
3127 If (p_col134(r) is Not Null) Then
3128 return FALSE;
3129 End If;
3130 End If;
3131 End If;
3132 If charid_tab.Exists(135) Then
3133 If Not(CxE(charid_tab(135),Txn_Num)) Then
3134 If (p_col135(r) is Not Null) Then
3135 return FALSE;
3136 End If;
3137 End If;
3138 End If;
3139 If charid_tab.Exists(136) Then
3140 If Not(CxE(charid_tab(136),Txn_Num)) Then
3141 If (p_col136(r) is Not Null) Then
3142 return FALSE;
3143 End If;
3144 End If;
3145 End If;
3146 If charid_tab.Exists(137) Then
3147 If Not(CxE(charid_tab(137),Txn_Num)) Then
3148 If (p_col137(r) is Not Null) Then
3149 return FALSE;
3150 End If;
3151 End If;
3152 End If;
3153 If charid_tab.Exists(138) Then
3154 If Not(CxE(charid_tab(138),Txn_Num)) Then
3155 If (p_col138(r) is Not Null) Then
3156 return FALSE;
3157 End If;
3158 End If;
3159 End If;
3160 If charid_tab.Exists(139) Then
3161 If Not(CxE(charid_tab(139),Txn_Num)) Then
3162 If (p_col139(r) is Not Null) Then
3163 return FALSE;
3164 End If;
3165 End If;
3166 End If;
3167 If charid_tab.Exists(140) Then
3168 If Not(CxE(charid_tab(140),Txn_Num)) Then
3169 If (p_col140(r) is Not Null) Then
3170 return FALSE;
3171 End If;
3172 End If;
3173 End If;
3174 If charid_tab.Exists(141) Then
3175 If Not(CxE(charid_tab(141),Txn_Num)) Then
3176 If (p_col141(r) is Not Null) Then
3177 return FALSE;
3178 End If;
3179 End If;
3180 End If;
3181 If charid_tab.Exists(142) Then
3182 If Not(CxE(charid_tab(142),Txn_Num)) Then
3183 If (p_col142(r) is Not Null) Then
3184 return FALSE;
3185 End If;
3186 End If;
3187 End If;
3188 If charid_tab.Exists(143) Then
3189 If Not(CxE(charid_tab(143),Txn_Num)) Then
3190 If (p_col143(r) is Not Null) Then
3191 return FALSE;
3192 End If;
3193 End If;
3194 End If;
3195 If charid_tab.Exists(144) Then
3196 If Not(CxE(charid_tab(144),Txn_Num)) Then
3197 If (p_col144(r) is Not Null) Then
3198 return FALSE;
3199 End If;
3200 End If;
3201 End If;
3202 If charid_tab.Exists(145) Then
3203 If Not(CxE(charid_tab(145),Txn_Num)) Then
3204 If (p_col145(r) is Not Null) Then
3205 return FALSE;
3206 End If;
3207 End If;
3208 End If;
3209 If charid_tab.Exists(146) Then
3210 If Not(CxE(charid_tab(146),Txn_Num)) Then
3211 If (p_col146(r) is Not Null) Then
3212 return FALSE;
3213 End If;
3214 End If;
3215 End If;
3216 If charid_tab.Exists(147) Then
3217 If Not(CxE(charid_tab(147),Txn_Num)) Then
3218 If (p_col147(r) is Not Null) Then
3219 return FALSE;
3220 End If;
3221 End If;
3222 End If;
3223 If charid_tab.Exists(148) Then
3224 If Not(CxE(charid_tab(148),Txn_Num)) Then
3225 If (p_col148(r) is Not Null) Then
3226 return FALSE;
3227 End If;
3228 End If;
3229 End If;
3230 If charid_tab.Exists(149) Then
3231 If Not(CxE(charid_tab(149),Txn_Num)) Then
3232 If (p_col149(r) is Not Null) Then
3233 return FALSE;
3234 End If;
3235 End If;
3236 End If;
3237 If charid_tab.Exists(150) Then
3238 If Not(CxE(charid_tab(150),Txn_Num)) Then
3239 If (p_col150(r) is Not Null) Then
3240 return FALSE;
3241 End If;
3242 End If;
3243 End If;
3244 If charid_tab.Exists(151) Then
3245 If Not(CxE(charid_tab(151),Txn_Num)) Then
3246 If (p_col151(r) is Not Null) Then
3247 return FALSE;
3248 End If;
3249 End If;
3250 End If;
3251 If charid_tab.Exists(152) Then
3252 If Not(CxE(charid_tab(152),Txn_Num)) Then
3253 If (p_col152(r) is Not Null) Then
3254 return FALSE;
3255 End If;
3256 End If;
3257 End If;
3258 If charid_tab.Exists(153) Then
3259 If Not(CxE(charid_tab(153),Txn_Num)) Then
3260 If (p_col153(r) is Not Null) Then
3261 return FALSE;
3262 End If;
3263 End If;
3264 End If;
3265 If charid_tab.Exists(154) Then
3266 If Not(CxE(charid_tab(154),Txn_Num)) Then
3267 If (p_col154(r) is Not Null) Then
3268 return FALSE;
3269 End If;
3270 End If;
3271 End If;
3272 If charid_tab.Exists(155) Then
3273 If Not(CxE(charid_tab(155),Txn_Num)) Then
3274 If (p_col155(r) is Not Null) Then
3275 return FALSE;
3276 End If;
3277 End If;
3278 End If;
3279 If charid_tab.Exists(156) Then
3280 If Not(CxE(charid_tab(156),Txn_Num)) Then
3281 If (p_col156(r) is Not Null) Then
3282 return FALSE;
3283 End If;
3284 End If;
3285 End If;
3286 If charid_tab.Exists(157) Then
3287 If Not(CxE(charid_tab(157),Txn_Num)) Then
3288 If (p_col157(r) is Not Null) Then
3289 return FALSE;
3290 End If;
3291 End If;
3292 End If;
3293 If charid_tab.Exists(158) Then
3294 If Not(CxE(charid_tab(158),Txn_Num)) Then
3295 If (p_col158(r) is Not Null) Then
3296 return FALSE;
3297 End If;
3298 End If;
3299 End If;
3300 If charid_tab.Exists(159) Then
3301 If Not(CxE(charid_tab(159),Txn_Num)) Then
3302 If (p_col159(r) is Not Null) Then
3303 return FALSE;
3304 End If;
3305 End If;
3306 End If;
3307 If charid_tab.Exists(160) Then
3308 If Not(CxE(charid_tab(160),Txn_Num)) Then
3309 If (p_col160(r) is Not Null) Then
3310 return FALSE;
3311 End If;
3312 End If;
3313 End If;
3314
3315 Return TRUE; -- If all failed, return TRUE to signal empty row
3316 EXCEPTION
3317 WHEN OTHERS THEN
3318 htp.p('Exception in function is_empty_row');
3319 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
3320 END is_empty_row;
3321 ------------------------------------------------------------------------------------
3322
3323
3324 function any_applicable_plans ( Ctx IN qa_ss_const.Ctx_Table,
3325 Txn_Number IN NUMBER,
3326 organization_id IN NUMBER )
3327 Return BOOLEAN
3328
3329 IS
3330 Dummy_tab qa_ss_const.num_table;
3331
3332
3333 BEGIN
3334 -- sanity check: dont think icx_sec.validatesession is needed here
3335 -- becos this function is part of wip's osp view
3336 -- but mentioning this comment as a check
3337
3338
3339 NULL;
3340
3341 Return Evaluate_Triggers(Ctx, Txn_Number, organization_id,
3342 Dummy_tab, 2);
3343
3344 -- The 2 above is an optimization hint to my Evaluate_triggers
3345 -- function which may be used in the future. Signifies, return
3346 -- after the first applicable plan is found. Dont search for more
3347
3348
3349 EXCEPTION
3350 WHEN OTHERS THEN
3351 htp.p('Exception in function any_applicable_plans');
3352 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
3353 -- anagarwa Thu Nov 8 11:24:34 PST 2001
3354 -- Following return added to handle following problem (Bug #2101177) :
3355 -- ORA-06503: PL/SQL: Function returned without value
3356 -- ORA-06512: at "APPS.QA_SS_CORE", line 3327
3357 -- ORA-06512: at "APPS.QA_SS_OSP", line 48
3358 -- ORA-06512: at line 1
3359 RETURN FALSE;
3360 END any_applicable_plans;
3361 ----------------------------------------------------------------------------------------
3362
3363 procedure all_applicable_plans ( Ctx IN qa_ss_const.Ctx_Table,
3364 Txn_Number IN NUMBER,
3365 organization_id IN NUMBER,
3366 PK1 IN VARCHAR2 DEFAULT NULL,
3367 PK2 IN VARCHAR2 DEFAULT NULL,
3368 PK3 IN VARCHAR2 DEFAULT NULL,
3369 PK4 IN VARCHAR2 DEFAULT NULL,
3370 PK5 IN VARCHAR2 DEFAULT NULL,
3371 PK6 IN VARCHAR2 DEFAULT NULL,
3372 PK7 IN VARCHAR2 DEFAULT NULL,
3373 PK8 IN VARCHAR2 DEFAULT NULL,
3374 PK9 IN VARCHAR2 DEFAULT NULL,
3375 PK10 IN VARCHAR2 DEFAULT NULL)
3376
3377 IS
3378 Pid_table qa_ss_const.num_table;
3379 ret_val boolean;
3380 BEGIN
3381 if (icx_sec.validatesession) then
3382
3383 NULL;
3384
3385 ret_val := Evaluate_Triggers(Ctx, Txn_Number, organization_id,
3386 Pid_table, 1);
3387 -- The 1 above is Flag value to the procedure
3388 -- ignore the ret_val in this case
3389
3390 List_Plans (Pid_table, Txn_Number, organization_id,
3391 pk1, pk2, pk3, pk4, pk5, pk6, pk7, pk8, pk9, pk10);
3392
3393
3394 end if; -- end icx session
3395
3396 EXCEPTION
3397 WHEN OTHERS THEN
3398 htp.p('Exception in procedure all_applicable_plans');
3399 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
3400 END all_applicable_plans;
3401 -------------------------------------------------------------------------------------------
3402
3403 function Evaluate_Triggers ( Ctx IN qa_ss_const.Ctx_Table,
3404 x_Txn_Number IN NUMBER,
3405 x_organization_id IN NUMBER,
3406 Pid_tab IN OUT NOCOPY qa_ss_const.num_Table,
3407 Flag IN NUMBER )
3408 RETURN BOOLEAN
3409 -- The Flag above is an optimization hint to my Evaluate_triggers
3410 -- function which may be used in the future. Signifies, return
3411 -- after the first applicable plan is found. Dont search for more
3412 -- if the Flag value is 2. Otherwise, find all the applic. plans
3413
3414 -- When the Flag is 2, the return value of the Function is what
3415 -- is significant. If the return value is TRUE, that means plans
3416 -- are applicable. In this case, the Pid_tab can be IGNORED
3417 -- When Flag is Not 2, then Pid_Tab will contain list of
3418 -- all applicable plan ids
3419 IS
3420
3421 Cursor coll_trigg_cur is
3422 SELECT qpt.Plan_transaction_id,
3423 qpt.Plan_id,
3424 qc.char_id,
3425 qc.dependent_char_id,
3426 qc.datatype,
3427 qpct.Operator,
3428 qpct.Low_Value,
3429 qpct.High_Value
3430 FROM qa_plan_collection_triggers qpct,
3431 qa_plan_transactions qpt,
3432 qa_plans_val_v qp,
3433 qa_chars qc,
3434 qa_txn_collection_triggers qtct
3435 WHERE qpt.Plan_ID = qp.Plan_ID
3436 AND qpct.Plan_Transaction_ID(+) = qpt.Plan_Transaction_ID
3437 AND qpct.Collection_Trigger_ID = qtct.Collection_Trigger_ID(+)
3438 AND qpct.Collection_Trigger_ID = qc.char_id(+)
3439 AND qpt.TRANSACTION_NUMBER = x_txn_number
3440 AND qtct.TRANSACTION_NUMBER(+) = x_txn_number
3441 AND qp.ORGANIZATION_ID = x_organization_id
3442 AND qpt.enabled_flag = 1
3443 ORDER BY qpt.plan_transaction_id;
3444
3445 Type Coll_Trigg_Type is TABLE of coll_trigg_cur%ROWTYPE INDEX BY BINARY_INTEGER;
3446 Coll_Trigg_Tab Coll_Trigg_Type;
3447
3448 plan_is_applicable BOOLEAN;
3449 counter INTEGER;
3450 i INTEGER := 1;
3451
3452 l_rowcount INTEGER;
3453
3454 l_datatype NUMBER;
3455 l_operator NUMBER;
3456 l_low_char VARCHAR2(150);
3457 l_high_char VARCHAR2(150);
3458 l_low_number NUMBER;
3459 l_high_number NUMBER;
3460 l_low_date DATE;
3461 l_high_date DATE;
3462 l_value_char VARCHAR2(150);
3463 l_value_number NUMBER;
3464 l_value_date DATE;
3465
3466 l_plan_id NUMBER;
3467 l_old_plan_id NUMBER;
3468
3469 l_plan_txn_id NUMBER ;
3470 l_old_plan_txn_id NUMBER ;
3471
3472
3473 l_char_id NUMBER;
3474 l_dep_char_id NUMBER;
3475 pid_count NUMBER := 0;
3476 atleast_one BOOLEAN;
3477 -- All variables beginning with l_ are local variables
3478 BEGIN
3479 atleast_one := FALSE;
3480 counter := 1;
3481 For ct_rec in coll_trigg_cur
3482 loop
3483 coll_trigg_tab(counter) := ct_rec;
3484 counter := counter + 1;
3485 end loop;
3486
3487 l_rowcount := coll_trigg_tab.count;
3488
3489 if (l_rowcount < 1) Then
3490 return FALSE; -- no plans applicable
3491 end if;
3492
3493 l_plan_txn_id := coll_trigg_tab(1).plan_transaction_id;
3494
3495 -- The variable i has been initialized to 1
3496
3497 WHILE ( i <= l_rowcount)
3498 LOOP
3499 l_old_plan_txn_id := l_plan_txn_id;
3500 plan_is_applicable := TRUE; -- start with this assumption
3501
3502 WHILE (l_plan_txn_id = l_old_plan_txn_id) AND (i <= l_rowcount)
3503 LOOP
3504 IF (plan_is_applicable = TRUE)
3505 THEN
3506 l_operator := coll_trigg_tab(i).Operator;
3507 l_datatype := coll_trigg_tab(i).Datatype;
3508 l_char_id := coll_trigg_tab(i).char_id;
3509 IF (l_operator is NULL) AND (l_datatype is NULL)
3510 THEN
3511 null;
3512 -- null collection trigger. Plan applies
3513 ELSE
3514 -- WATCH OUT FOR EXCEPTIONS while
3515 -- accessing Ctx table below
3516 IF (qltcompb.compare( Ctx(l_char_id),
3517 l_operator,
3518 coll_trigg_tab(i).Low_value,
3519 coll_trigg_tab(i).High_Value,
3520 l_datatype) )
3521 -- above is a overloaded call
3522 THEN
3523 plan_is_applicable := TRUE;
3524 ELSE
3525 plan_is_applicable := FALSE;
3526 END IF; --end qltcompb
3527 END IF; -- end l_operator and l_datatype null
3528 END IF; -- end Check plan applicable is true
3529
3530 i := i+1;
3531 IF (i <= l_rowcount) THEN
3532 l_plan_txn_id := coll_trigg_tab(i).plan_transaction_id;
3533 END IF;
3534 END LOOP; -- end inner while loop
3535 IF (plan_is_applicable = TRUE) THEN
3536 atleast_one := TRUE;
3537 -- if flag is 2, stop here itself and return True
3538 IF (Flag = 2) THEN
3539 RETURN TRUE;
3540 END IF;
3541 -- if flag is not 2, then keep continuing
3542 pid_count := pid_count + 1;
3543 -- at very beginning pid_count is ZERO
3544 Pid_tab(pid_count) := coll_trigg_tab(i-1).plan_id;
3545 END IF;
3546 END LOOP; -- end outer while loop
3547
3548 RETURN atleast_one;
3549
3550 EXCEPTION
3551 WHEN OTHERS THEN
3552 htp.p('Exception in function Evaluate_Triggers');
3553 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
3554 -- anagarwa Thu Nov 8 11:24:34 PST 2001
3555 -- Following return added to handle following problem (Bug #2101177) :
3556 -- ORA-06503: PL/SQL: Function returned without value
3557 -- ORA-06512: at "APPS.QA_SS_CORE", line 3327
3558 -- ORA-06512: at "APPS.QA_SS_OSP", line 48
3559 -- ORA-06512: at line 1
3560 RETURN FALSE;
3561 END Evaluate_Triggers;
3562 ----------------------------------------------------------------------------------------------
3563
3564 procedure generate_eqr_javascript
3565
3566 IS
3567
3568 BEGIN
3569
3570 htp.p('<SCRIPT LANGUAGE="JavaScript">');
3571 htp.p('var lov_win;');
3572 htp.p('function sayhello()
3573 {
3574 alert("Hello Quality User");
3575 }');
3576
3577 htp.p('function LOV(rowno,charid, colno, itm)
3578 {
3579 var fldval;
3580
3581 fldval = itm.value; //remember itm is a reference
3582 // to the field and not a string
3583 // so itm.value can be said
3584
3585 fldval = escape(fldval);
3586 lov_win = window.open("qa_ss_lov.gen_list?vchar_id="+charid+"&rnumb="+rowno+
3587 "&cnumb="+colno+"&find1="+fldval, "LOV",
3588 "resizable=yes,menubar=yes,scrollbar=yes,width=780,height=300");
3589
3590 lov_win.opener = self;
3591 }');
3592
3593 htp.p('</SCRIPT>');
3594
3595 EXCEPTION
3596
3597 WHEN OTHERS THEN
3598 htp.p('Exception in procedure generate_eqr_javascript');
3599 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
3600 END generate_eqr_javascript;
3601 -------------------------------------------------------------------------------------------------
3602 procedure List_Plans ( Pid_tab IN qa_ss_const.num_table,
3603 txn_num IN NUMBER DEFAULT NULL,
3604 orgz_id IN NUMBER DEFAULT NULL,
3605 PK1 IN VARCHAR2 DEFAULT NULL,
3606 PK2 IN VARCHAR2 DEFAULT NULL,
3607 PK3 IN VARCHAR2 DEFAULT NULL,
3608 PK4 IN VARCHAR2 DEFAULT NULL,
3609 PK5 IN VARCHAR2 DEFAULT NULL,
3610 PK6 IN VARCHAR2 DEFAULT NULL,
3611 PK7 IN VARCHAR2 DEFAULT NULL,
3612 PK8 IN VARCHAR2 DEFAULT NULL,
3613 PK9 IN VARCHAR2 DEFAULT NULL,
3614 PK10 IN VARCHAR2 DEFAULT NULL )
3615
3616 IS
3617 l_language_code varchar2(30);
3618 no_of_plans NUMBER;
3619 pid_i NUMBER;
3620 pname varchar2(40);
3621 pdesc varchar2(30);
3622 ptype varchar2(30);
3623 enterurl varchar2(10000);
3624 viewurl varchar2(2000);
3625 attachurl varchar2(200);
3626 atchmt varchar2(50);
3627 show_eqr BOOLEAN := TRUE;
3628 row_color VARCHAR2(10) := 'BLUE';
3629 osp_job_ok BOOLEAN := TRUE;
3630
3631 CURSOR plan_cur (X_Pid NUMBER)
3632 IS
3633 Select qp.name, qp.description, fcl.meaning
3634 from qa_plans qp, fnd_common_lookups fcl
3635 where qp.plan_id = X_Pid
3636 and qp.plan_type_code = fcl.lookup_code
3637 and fcl.lookup_type = 'COLLECTION_PLAN_TYPE'
3638 Order By qp.name;
3639
3640 plan_rec plan_cur%ROWTYPE;
3641
3642 BEGIN
3643 if (icx_sec.validatesession) then
3644
3645 l_language_code := icx_sec.getID(icx_sec.PV_LANGUAGE_CODE);
3646
3647 -- Added new function security July 30, 1999
3648 show_eqr := FND_FUNCTION.TEST('ICX_QA_ENTER_QUALITY');
3649
3650 -- Below IF stmts for Bug 999521
3651 IF (show_eqr) THEN
3652 IF (txn_num=100) THEN -- osp transaction
3653 osp_job_ok := Is_Job_Valid(to_number(PK1),
3654 to_number(PK2));
3655 show_eqr := osp_job_ok;
3656 -- private function in beginning of this pkg
3657 -- NOTE: For OSP, pk1 is po_distribution_id
3658 -- and pk2 is wip_entity_id
3659 END IF;
3660 END IF;
3661
3662 htp.p('<HTML>');
3663 htp.p('<HEAD>');
3664
3665 /* -- Javascript COMMENTED OUT
3666 htp.p('<SCRIPT LANGUAGE="JavaScript">');
3667 htp.p('function view_results(plan_id)
3668 {
3669 window.location = "qa_ss_core.VQR?plan_id_i="+plan_id;
3670 }');
3671 htp.p('function enter_results(plan_id, txn, oid, p1, p2, p3, p4, p5, p6, p7,p8, p9, p10)
3672 {
3673 window.location = "qa_ss_core.draw_frames?plan_id_i="+plan_id+"'
3674 || '&' || 'txn_num="+txn+"'
3675 || '&' || 'orgz_id="+oid+"'
3676 || '&' || 'pk1="+p1+"'
3677 || '&' || 'pk2="+p2+"'
3678 || '&' || 'pk3="+p3+"'
3679 || '&' || 'pk4="+p4+"'
3680 || '&' || 'pk5="+p5+"'
3681 || '&' || 'pk6="+p6+"'
3682 || '&' || 'pk7="+p7+"'
3683 || '&' || 'pk8="+p8+"'
3684 || '&' || 'pk9="+p9+"'
3685 || '&' || 'pk10="+p10;
3686 }');
3687
3688 htp.p('</SCRIPT>');
3689 */ -- COMMENTED OUT ABOVE
3690
3691 htp.p('</HEAD>');
3692 htp.p('<BODY bgcolor=#cccccc>');
3693
3694 -- icx_admin_sig.toolbar(language_code => l_language_code);
3695 -- icx_plug_utilities.plugbanner(fnd_message.get_string('QA', 'QA_SS_COLL_PLANS'));
3696 if (NOT osp_job_ok) THEN
3697 htp.p(fnd_message.get_string('QA', 'QA_SS_INVALID_JOB'));
3698 end if;
3699 htp.formOpen('');
3700 htp.br;
3701 htp.tableOpen(cborder=>'BORDER=2', cattributes=>'CELLPADDING=2');
3702 htp.tableRowOpen (cattributes=>'BGCOLOR="#336699"');
3703 htp.tableHeader(cvalue=>'<font color=#ffffff>'||
3704 fnd_message.get_string('QA', 'QA_SS_CP_HEADING')
3705 || '</font>', calign=>'CENTER');
3706 htp.tableHeader(cvalue=>'<font color=#ffffff>'||
3707 fnd_message.get_string('QA', 'QA_SS_DESC'), calign=>'CENTER');
3708 htp.tableHeader(cvalue=>'<font color=#ffffff>'||
3709 'Type'|| '</font>', calign=>'CENTER');
3710 if (show_eqr) THEN
3711 htp.tableHeader(cvalue=>'<font color=#ffffff>'||
3712 fnd_message.get_string('QA', 'QA_SS_ENTER_BUTTON')|| '</font>', calign=>'CENTER');
3713 END IF;
3714 htp.tableHeader(cvalue=>'<font color=#ffffff>'||
3715 fnd_message.get_string('QA', 'QA_SS_VIEW_BUTTON')|| '</font>', calign=>'CENTER');
3716 htp.tableHeader(cvalue=>'<font color=#ffffff>'||
3717 fnd_message.get_string('QA', 'QA_SS_ATTACH_BUTTON')|| '</font>', calign=>'CENTER');
3718 htp.tableRowClose;
3719 htp.p('<TR></TR><TR></TR>');
3720
3721 -- Loop goes in here
3722 no_of_plans := Pid_tab.count;
3723 For i in 1..no_of_plans
3724 Loop
3725 pid_i := Pid_tab(i);
3726
3727 enterurl := 'qa_ss_core.draw_frames?plan_id_i=' || pid_i
3728 || '&' || 'txn_num=' || Txn_Num
3729 || '&' || 'orgz_id=' || orgz_id
3730 || '&' || 'pk1=' || pk1
3731 || '&' || 'pk2=' || pk2
3732 || '&' || 'pk3=' || pk3
3733 || '&' || 'pk4=' || pk4
3734 || '&' || 'pk5=' || pk5
3735 || '&' || 'pk6=' || pk6
3736 || '&' || 'pk7=' || pk7
3737 || '&' || 'pk8=' || pk8
3738 || '&' || 'pk9=' || pk9
3739 || '&' || 'pk10=' || pk10;
3740
3741 viewurl := 'qa_ss_core.VQR_Frames?plan_id_i=' || pid_i
3742 || '&'
3743 || 'ss_where_clause=';
3744 attachurl := 'qa_ss_attachment.qa_plans_view_attachment?plan_id=' || pid_i;
3745 -- viewurl := 'qa_results_interface_pkg.Proc1?x=100';
3746
3747 atchmt := qa_ss_attachment.qa_plans_attachment_status(pid_i);
3748
3749 OPEN plan_cur(pid_i);
3750 FETCH plan_cur INTO plan_rec;
3751 pname := substr(plan_rec.name,1,30);
3752 pdesc := NVL(substr(plan_rec.description,1,20), ' ');
3753 ptype := NVL(substr(plan_rec.meaning,1,20), ' ');
3754
3755 IF (row_color = 'BLUE') THEN
3756 htp.tableRowOpen(cattributes=>'BGCOLOR="#99CCFF"');
3757 row_color := 'WHITE';
3758 ELSE
3759 htp.tableRowOpen(cattributes=>'BGCOLOR="#FFFFFF"');
3760 row_color := 'BLUE';
3761 END IF; -- end if for row color
3762
3763 htp.tableData(pname);
3764 htp.tableData(pdesc);
3765 htp.tableData(ptype);
3766 IF (show_eqr) THEN
3767 htp.tableData(htf.anchor(enterurl, fnd_message.get_string('QA','QA_SS_ENTER_BUTTON'), cattributes=>'TARGET="enterwin"'));
3768 END IF;
3769 htp.tableData(htf.anchor(viewurl, fnd_message.get_string('QA','QA_SS_VIEW_BUTTON'), cattributes=>'TARGET="viewwin"'));
3770 if (atchmt = 'FULL') then
3771 htp.tableData(htf.anchor(attachurl,
3772 fnd_message.get_string('QA','QA_SS_AVAILABLE'),
3773 cattributes=>'TARGET="attwin"'));
3774 else
3775 htp.tableData('&'||'nbsp');
3776 end if;
3777
3778 CLOSE plan_cur;
3779 End Loop; -- end of forloop for all rows in list of plans
3780
3781 -- Bottom header is below
3782 /*
3783 htp.p('<TR></TR><TR></TR>');
3784 htp.tableRowOpen (cattributes=>'BGCOLOR="#83C1C1"');
3785 htp.tableHeader(cvalue=>fnd_message.get_string('QA', 'QA_SS_CP_HEADING'), calign=>'CENTER');
3786 htp.tableHeader(cvalue=>fnd_message.get_string('QA', 'QA_SS_DESC'), calign=>'CENTER');
3787 htp.tableHeader(cvalue=>'Type', calign=>'CENTER');
3788 IF (show_eqr) THEN
3789 htp.tableHeader(cvalue=>fnd_message.get_string('QA', 'QA_SS_ENTER_BUTTON'), calign=>'CENTER');
3790 END IF;
3791 htp.tableHeader(cvalue=>fnd_message.get_string('QA', 'QA_SS_VIEW_BUTTON'), calign=>'CENTER');
3792 htp.tableHeader(cvalue=>fnd_message.get_string('QA', 'QA_SS_ATTACH_BUTTON'), calign=>'CENTER');
3793 htp.tableRowClose;
3794 */
3795 htp.tableClose;
3796 htp.formClose;
3797
3798
3799 htp.p('</BODY>');
3800
3801 htp.p('</HTML>');
3802
3803 NULL;
3804 end if; -- end icx session
3805
3806
3807 EXCEPTION
3808 WHEN OTHERS THEN
3809 If plan_cur%ISOPEN Then
3810 CLOSE plan_cur;
3811 End if;
3812
3813 htp.p('Exception in QA_SS_CORE.List_Plans');
3814 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
3815 END List_Plans;
3816
3817 ---------------------------------------------------------------------------------------------------
3818 procedure Plan_List_Frames (
3819 txn_num IN NUMBER DEFAULT NULL,
3820 PK1 IN VARCHAR2 DEFAULT NULL,
3821 PK2 IN VARCHAR2 DEFAULT NULL,
3822 PK3 IN VARCHAR2 DEFAULT NULL,
3823 PK4 IN VARCHAR2 DEFAULT NULL,
3824 PK5 IN VARCHAR2 DEFAULT NULL,
3825 PK6 IN VARCHAR2 DEFAULT NULL,
3826 PK7 IN VARCHAR2 DEFAULT NULL,
3827 PK8 IN VARCHAR2 DEFAULT NULL,
3828 PK9 IN VARCHAR2 DEFAULT NULL,
3829 PK10 IN VARCHAR2 DEFAULT NULL )
3830 IS
3831 url_str VARCHAR2(1000);
3832 toolbar_heading VARCHAR2(500);
3833 l_language_code VARCHAR2(30);
3834 BEGIN
3835
3836 l_language_code := ICX_Sec.GetId(ICX_SEC.PV_LANGUAGE_CODE);
3837
3838 IF (txn_num=100) then
3839 url_str :='qa_ss_osp.osp_plans';
3840 ELSIF (txn_num=110) then
3841 url_str := 'qa_ss_ship.shipping_plans';
3842 END IF;
3843
3844 htp.p('<HTML>');
3845 htp.p('<TITLE>'||fnd_message.get_string('QA', 'QA_SS_COLL_PLANS')||'</TITLE>');
3846 htp.p('<HEAD>');
3847 htp.p('<LINK REL="STYLESHEET" HREF="/OA_HTML/'
3848 ||l_language_code||'/POSSTYLE.css">');
3849
3850
3851 htp.p('<script src="/OA_HTML/POSCUTIL.js" language="JavaScript">');
3852 htp.p('</script>');
3853 htp.p('<script src="/OA_HTML/POSWUTIL.js" language="JavaScript">');
3854 htp.p('</script>');
3855 htp.p('<script src="/OA_HTML/POSEVENT.js" language="JavaScript">');
3856 htp.p('</script>');
3857
3858 js.scriptOpen;
3859 pos_global_vars_sv.InitializeMessageArray;
3860 js.scriptClose;
3861 htp.p('</HEAD>');
3862
3863 toolbar_heading := fnd_message.get_string('QA', 'QA_SS_COLL_PLANS');
3864 toolbar_heading := wfa_html.conv_special_url_chars(toolbar_heading);
3865 htp.p('<frameset rows="50,*,40" border=0>');
3866 htp.p('<frame src="pos_toolbar_sv.PaintToolbar?p_title='||toolbar_heading||'"
3867 name=toolbar
3868 marginwidth=6
3869 marginheight=2
3870 scrolling=no>');
3871 htp.p('<frameset cols="3,*,3" border=0>');
3872 htp.p('<frame src="/OA_HTML/'||l_language_code||'/POSBLBOR.htm"
3873 name=borderLeft
3874 marginwidth=0
3875 marginheight=0
3876 scrolling=no>');
3877 htp.p('<frameset rows="30,*,5" border=0>');
3878 htp.p('<frame src="pos_upper_banner_sv.PaintUpperBanner?p_product=QA'||'&'||'p_title=QA_SS_COLL_PLANS"
3879 name=upperbanner
3880 marginwidth=0
3881 marginheight=0
3882 scrolling=no>');
3883
3884
3885
3886
3887 htp.p('<frame src="'||url_str||'?pk1='||pk1
3888 ||'&'||'pk2='||pk2
3889 ||'&'||'pk3='||pk3
3890 ||'&'||'pk4='||pk4
3891 ||'&'||'pk5='||pk5
3892 ||'&'||'pk6='||pk6
3893 ||'&'||'pk7='||pk7
3894 ||'&'||'pk8='||pk8
3895 ||'&'||'pk9='||pk9
3896 ||'&'||'pk10='||pk10||'"
3897 name=content
3898 marginwidth=0
3899 marginheight=0
3900 scrolling=yes>');
3901
3902
3903
3904 /* -- BELOW ONLY FOR DEBUG
3905 htp.p('<frame src="qa_ss_core.draw_table?plan_id_i=1941&txn_num=100&orgz_id=207&pk1=4891"
3906 name=content
3907 marginwidth=0
3908 marginheight=0
3909 scrolling=yes>');
3910 */
3911 htp.p('<frame src="/OA_HTML/'||l_language_code||'/POSLWBAN.htm"
3912 name=lowerbanner
3913 marginwidth=0
3914 marginheight=0
3915 scrolling=no>');
3916
3917 htp.p('</frameset>');
3918 htp.p('<frame src="/OA_HTML/'||l_language_code||'/POSBLBOR.htm"
3919 name=borderRight
3920 marginwidth=0
3921 marginheight=0
3922 scrolling=no>');
3923 htp.p('</frameset>');
3924 htp.p('<frame src="/OA_HTML/'||l_language_code||'/POSBLBOR.htm"
3925 name=bottomregion
3926 marginwidth=0
3927 marginheight=0
3928 scrolling=no>');
3929 htp.p('</frameset>');
3930 htp.p('</HTML>');
3931
3932 EXCEPTION
3933 WHEN OTHERS THEN
3934 htp.p('Exception in QA_SS_CORE.Plan_List_Frames');
3935 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
3936 END Plan_List_Frames;
3937 ----------------------------------------------------------------------------------------------------
3938 procedure VQR_Frames(plan_id_i IN qa_plans.plan_id%TYPE,
3939 ss_where_clause in varchar2 default null)
3940 IS
3941 toolbar_heading VARCHAR2(500);
3942 l_plan_name VARCHAR2(50);
3943 l_language_code VARCHAR2(30);
3944 ss_w_c varchar2(5000) := NULL;
3945
3946 CURSOR plan_name_cur IS
3947 SELECT name
3948 FROM QA_PLANS
3949 WHERE plan_id = plan_id_i ;
3950
3951 BEGIN
3952 if (icx_sec.validateSession) THEN
3953
3954 l_language_code := ICX_Sec.GetId(icx_sec.PV_LANGUAGE_CODE);
3955
3956 OPEN plan_name_cur;
3957 FETCH plan_name_cur INTO l_plan_name;
3958 CLOSE plan_name_cur;
3959
3960 htp.p('<HTML>');
3961 htp.p('<TITLE>'||fnd_message.get_string('QA', 'QA_SS_VQR')||'</TITLE>');
3962 htp.p('<HEAD>');
3963 htp.p('<LINK REL="STYLESHEET" HREF="/OA_HTML/'
3964 ||l_language_code||'/POSSTYLE.css">');
3965
3966
3967 htp.p('<script src="/OA_HTML/POSCUTIL.js" language="JavaScript">');
3968 htp.p('</script>');
3969 htp.p('<script src="/OA_HTML/POSWUTIL.js" language="JavaScript">');
3970 htp.p('</script>');
3971 htp.p('<script src="/OA_HTML/POSEVENT.js" language="JavaScript">');
3972 htp.p('</script>');
3973
3974 js.scriptOpen;
3975 pos_global_vars_sv.InitializeMessageArray;
3976 js.scriptClose;
3977 htp.p('</HEAD>');
3978
3979 toolbar_heading := fnd_message.get_string('QA', 'QA_SS_VQR');
3980 toolbar_heading := wfa_html.conv_special_url_chars(toolbar_heading);
3981
3982 htp.p('<frameset rows="50,*,40" border=0>');
3983 htp.p('<frame src="pos_toolbar_sv.PaintToolbar?p_title='||toolbar_heading||'"
3984 name=toolbar
3985 marginwidth=6
3986 marginheight=2
3987 scrolling=no>');
3988 htp.p('<frameset cols="3,*,3" border=0>');
3989 htp.p('<frame src="/OA_HTML/'||l_language_code||'/POSBLBOR.htm"
3990 name=borderLeft
3991 marginwidth=0
3992 marginheight=0
3993 scrolling=no>');
3994
3995 l_plan_name := wfa_html.conv_special_url_chars(l_plan_name);
3996 htp.p('<frameset rows="30,*,5" border=0>');
3997 htp.p('<frame src="pos_upper_banner_sv.PaintUpperBanner?p_product=QA'||'&'||'p_title='
3998 || l_plan_name || '"
3999 name=upperbanner
4000 marginwidth=0
4001 marginheight=0
4002 scrolling=no>');
4003
4004 if (ss_where_clause is not null) then
4005 ss_w_c := wfa_html.conv_special_url_chars(ss_where_clause);
4006 end if;
4007
4008 htp.p('<frame src="qa_ss_core.VQR?plan_id_i='||plan_id_i
4009 ||'&'
4010 ||'ss_where_clause='||ss_w_c||'"
4011 name=content
4012 marginwidth=0
4013 marginheight=0
4014 scrolling=yes>');
4015
4016 htp.p('<frame src="/OA_HTML/'||l_language_code||'/POSLWBAN.htm"
4017 name=lowerbanner
4018 marginwidth=0
4019 marginheight=0
4020 scrolling=no>');
4021
4022 htp.p('</frameset>');
4023 htp.p('<frame src="/OA_HTML/'||l_language_code||'/POSBLBOR.htm"
4024 name=borderRight
4025 marginwidth=0
4026 marginheight=0
4027 scrolling=no>');
4028 htp.p('</frameset>');
4029 htp.p('<frame src="/OA_HTML/'||l_language_code||'/POSBLBOR.htm"
4030 name=bottomregion
4031 marginwidth=0
4032 marginheight=0
4033 scrolling=no>');
4034 htp.p('</frameset>');
4035 htp.p('</HTML>');
4036
4037
4038
4039 end if; -- end icx validate session
4040
4041 EXCEPTION
4042 WHEN OTHERS THEN
4043 IF plan_name_cur%ISOPEN THEN
4044 CLOSE plan_name_cur;
4045 END IF;
4046 htp.p('Exception in QA_SS_CORE.VQR_Frames');
4047 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
4048 END Vqr_Frames;
4049 -----------------------------------------------------------------------------------------------------
4050 procedure draw_html_button(src IN VARCHAR2 DEFAULT NULL,
4051 txt IN VARCHAR2 DEFAULT NULL)
4052
4053 -- in the above, src is the javascript and txt is the button label
4054 -- Also, look at pos_asn_search_pkg.button (file POSASNSB.pls)
4055 -- written by david chan (dfkchan) of PO team
4056 IS
4057
4058 BEGIN
4059 htp.p('
4060 <table cellpadding=0 cellspacing=0 border=0>
4061 <tr>
4062 <td rowspan=5><img src=/OA_MEDIA/FNDBRNDL.gif ></td>
4063 <td bgcolor=#333333><img src=/OA_MEDIA/FNDPX3.gif ></td>
4064 <td rowspan=5><img src=/OA_MEDIA/FNDBRNDR.gif ></td>
4065 </tr>
4066 <tr>
4067 <td bgcolor=#ffffff><img src=/OA_MEDIA/FNDPX6.gif></td>
4068 </tr>
4069 <tr>
4070 <td bgcolor=#cccccc height=20 nowrap><a
4071 href="' || src || '"><font class=button>'|| txt || '</font></a></td>
4072 </tr>
4073 <tr>
4074 <td bgcolor=#666666><img src=/OA_MEDIA/FNDPX3.gif></td>
4075 </tr>
4076 <tr>
4077 <td bgcolor=#333333><img src=/OA_MEDIA/FNDPX3.gif></td>
4078 </tr>
4079 </table>
4080 ');
4081
4082
4083 EXCEPTION
4084 WHEN OTHERS THEN
4085 htp.p('Exception in QA_SS_CORE.Draw_html_button');
4086 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
4087
4088 END draw_html_button;
4089 -----------------------------------------------------------------------------------------------------
4090 procedure call_workflow(x_buyer_id IN NUMBER DEFAULT NULL,
4091 x_source_id IN NUMBER DEFAULT NULL,
4092 x_plan_id IN NUMBER DEFAULT NULL,
4093 x_item_id IN NUMBER DEFAULT NULL,
4094 x_po_header_id IN NUMBER DEFAULT NULL)
4095 IS
4096
4097 BEGIN
4098 htp.p(fnd_message.get_string('QA', 'QA_SS_NOTIFY_SENT'));
4099 -- Call Revathy's package below
4100 qa_ss_import_wf.start_buyer_notification(x_buyer_id ,
4101 x_source_id ,
4102 x_plan_id ,
4103 x_item_id ,
4104 x_po_header_id );
4105
4106 EXCEPTION
4107 WHEN OTHERS THEN
4108 htp.p('Exception in QA_SS_CORE.Call_Workflow');
4109 htp.p('<STRONG><FONT COLOR="#FF0000">'||SQLERRM||'</FONT></STRONG>');
4110 END call_workflow;
4111 -----------------------------------------------------------------------------------------------------
4112 function is_plan_applicable ( Pid IN NUMBER,
4113 Txn_Num IN NUMBER default null,
4114 PK1 IN VARCHAR2 default null,
4115 PK2 IN VARCHAR2 default null,
4116 PK3 IN VARCHAR2 default null,
4117 PK4 IN VARCHAR2 default null,
4118 PK5 IN VARCHAR2 default null,
4119 PK6 IN VARCHAR2 default null,
4120 PK7 IN VARCHAR2 default null,
4121 PK8 IN VARCHAR2 default null,
4122 PK9 IN VARCHAR2 default null,
4123 PK10 IN VARCHAR2 default null,
4124 Txn_Name IN VARCHAR2 default null)
4125 Return VARCHAR2
4126 IS
4127 Ctx qa_ss_const.Ctx_Table;
4128 -- Ctx_Table type is declared in pkg spec
4129 Prompt_Arr qa_ss_const.var30_table;
4130 lov_arr qa_ss_const.bool_table;
4131 disp_len_arr qa_ss_const.num_table;
4132 dv_arr qa_ss_const.var150_table; -- for Default Value
4133 charid_array qa_ss_const.num_table;
4134 names_array qa_ss_const.var30_table;
4135
4136 no_of_cols NUMBER := 0;
4137 plan_name_i qa_plans.name%TYPE := NULL;
4138 l_language_code varchar2(30);
4139 it_name VARCHAR2(20);
4140 de_name VARCHAR2(20); -- dependent element name
4141 Name VARCHAR2(2000);
4142 char_name_i qa_chars.name%TYPE;
4143 item_name VARCHAR2(30) := NULL;
4144 l_Po_Agent_Id NUMBER := NULL;
4145 l_User_Id NUMBER := NULL;
4146 l_Item_Id NUMBER := NULL;
4147 l_Po_Header_Id NUMBER := NULL;
4148 l_Wip_Entity_Type NUMBER := NULL;
4149 l_Wip_Rep_Sch_Id NUMBER := NULL;
4150 l_Po_Release_Id NUMBER := NULL;
4151 l_Po_Line_Id NUMBER := NULL;
4152 l_Line_Location_Id NUMBER := NULL;
4153 l_Po_Distribution_Id NUMBER := NULL;
4154 l_Wip_Entity_Id NUMBER := NULL;
4155 l_Wip_Line_Id NUMBER := NULL;
4156 l_Po_Shipment_Id NUMBER := NULL;
4157 l_Organization_Id NUMBER := NULL;
4158
4159 out_category_value VARCHAR2(1000) := NULL;
4160 out_category_id NUMBER := NULL;
4161
4162 temp_pid NUMBER := NULL;
4163 temp_plan_txn_id NUMBER := NULL;
4164 chk VARCHAR2(1);
4165 CURSOR plan_tx_cur IS
4166 SELECT qpt.plan_id, qpt.plan_transaction_id
4167 FROM QA_PLAN_TRANSACTIONS QPT
4168 Where qpt.plan_id = Pid
4169 and qpt.transaction_number = Txn_Num;
4170
4171 --
4172 --this cursor finds if item category is used as
4173 --a collection trigger for this plan txn
4174 --
4175 --CURSOR is_item_cat_cur (c_plan_txn_id IN NUMBER)
4176 --IS
4177 --SELECT 1
4178 --from qa_plan_collection_triggers qpct
4179 --where qpct.plan_transaction_id = c_plan_txn_id
4180 --and qpct.collection_trigger_id = 11;
4181 --collection trigger id 11 is item category
4182
4183 BEGIN
4184 -- SS OM transaction is not a regular txn
4185 -- Hence some special logic
4186 IF (Txn_Name = 'OMHEADER') THEN
4187 chk:= qa_ss_om.is_om_header_plan_applicable(Pid, PK1);
4188 RETURN chk;
4189 ELSIF (Txn_Name = 'OMLINES') THEN
4190 chk:= qa_ss_om.is_om_lines_plan_applicable(Pid,PK1,PK3);
4191 RETURN chk;
4192 END IF;
4193
4194 -- Below stmts executed only if txn Not OM related
4195 OPEN plan_tx_cur;
4196 FETCH plan_tx_cur INTO temp_pid, temp_plan_txn_id;
4197 IF plan_tx_cur%NOTFOUND
4198 Then
4199 Return 'N';
4200 END IF;
4201 CLOSE plan_tx_cur;
4202
4203 qa_ss_core.Default_In_Values
4204 (Ctx, Txn_Num, PK1, PK2, PK3, PK4,
4205 PK5, PK6, PK7, PK8, PK9, PK10,
4206 l_Po_Agent_Id, l_Item_Id, l_Po_Header_Id,
4207 l_Wip_Entity_Type, l_Wip_Rep_Sch_Id, l_Po_Release_Id,
4208 l_Po_Line_Id, l_Line_Location_Id, l_Po_Distribution_Id,
4209 l_Wip_Entity_Id, l_Wip_Line_Id, l_Po_Shipment_Id,
4210 l_Organization_Id );
4211
4212 --enhancement 2004914 Item Category
4213 --for family pack H Sep 21, 2001
4214 if (Txn_Num = 100 or Txn_Num = 110 ) then
4215
4216 get_item_category_val (
4217 p_org_id => l_Organization_Id,
4218 p_item_id => l_Item_Id,
4219 x_category_val => out_category_value,
4220 x_category_id => out_category_id);
4221
4222 --if for some reason, the procedure
4223 --cannot find any category for the given item
4224 --due to QA:Item Category Set Profile being
4225 --incorrectly set, or any other reason, then
4226 --out_category_value will have NULL value
4227
4228 Ctx(qa_ss_const.item_category) := out_category_value;
4229
4230 end if;
4231
4232 chk:= check_plan_for_applicability(Ctx, Txn_Num, l_Organization_Id, pid);
4233 RETURN chk;
4234
4235 END is_plan_applicable;
4236
4237 -------------------------------------------------------------
4238
4239 function is_plan_applicable_for_osp (
4240 Pid IN NUMBER,
4241 p_item IN VARCHAR2 DEFAULT NULL,
4242 p_revision IN VARCHAR2 DEFAULT NULL,
4243 p_job_name IN VARCHAR2 DEFAULT NULL,
4244 p_from_op_seq_num IN VARCHAR2 DEFAULT NULL,
4245 p_vendor_name IN VARCHAR2 DEFAULT NULL,
4246 p_po_number IN VARCHAR2 DEFAULT NULL,
4247 p_ordered_quantity IN VARCHAR2 DEFAULT NULL,
4248 p_vendor_item_number IN VARCHAR2 DEFAULT NULL,
4249 p_po_release_num IN VARCHAR2 DEFAULT NULL,
4250 p_uom_name IN VARCHAR2 DEFAULT NULL,
4251 p_production_line IN VARCHAR2 DEFAULT NULL,
4252 p_organization_id IN NUMBER DEFAULT NULL)
4253 Return VARCHAR2
4254 IS
4255 Ctx qa_ss_const.Ctx_Table;
4256 -- Ctx_Table type is declared in pkg spec
4257 Prompt_Arr qa_ss_const.var30_table;
4258 lov_arr qa_ss_const.bool_table;
4259 disp_len_arr qa_ss_const.num_table;
4260 dv_arr qa_ss_const.var150_table; -- for Default Value
4261 charid_array qa_ss_const.num_table;
4262 names_array qa_ss_const.var30_table;
4263
4264 no_of_cols NUMBER := 0;
4265 plan_name_i qa_plans.name%TYPE := NULL;
4266 l_language_code varchar2(30);
4267 it_name VARCHAR2(20);
4268 de_name VARCHAR2(20); -- dependent element name
4269 Name VARCHAR2(2000);
4270 char_name_i qa_chars.name%TYPE;
4271 item_name VARCHAR2(30) := NULL;
4272 l_Po_Agent_Id NUMBER := NULL;
4273 l_User_Id NUMBER := NULL;
4274 l_Item_Id NUMBER := NULL;
4275 l_Po_Header_Id NUMBER := NULL;
4276 l_Wip_Entity_Type NUMBER := NULL;
4277 l_Wip_Rep_Sch_Id NUMBER := NULL;
4278 l_Po_Release_Id NUMBER := NULL;
4279 l_Po_Line_Id NUMBER := NULL;
4280 l_Line_Location_Id NUMBER := NULL;
4281 l_Po_Distribution_Id NUMBER := NULL;
4282 l_Wip_Entity_Id NUMBER := NULL;
4283 l_Wip_Line_Id NUMBER := NULL;
4284 l_Po_Shipment_Id NUMBER := NULL;
4285 l_Organization_Id NUMBER := NULL;
4286
4287 temp_pid NUMBER := NULL;
4288 chk VARCHAR2(1);
4289 CURSOR plan_tx_cur IS
4290 SELECT qpt.plan_id
4291 FROM QA_PLAN_TRANSACTIONS QPT
4292 Where qpt.plan_id = Pid
4293 and qpt.transaction_number = 100;
4294
4295 BEGIN
4296 OPEN plan_tx_cur;
4297 FETCH plan_tx_cur INTO temp_pid;
4298 IF plan_tx_cur%NOTFOUND
4299 Then
4300 Return 'N';
4301 END IF;
4302 CLOSE plan_tx_cur;
4303
4304 Ctx.delete();
4305
4306 Ctx(qa_ss_const.item) := p_item;
4307 Ctx(qa_ss_const.revision) := p_revision;
4308 Ctx(qa_ss_const.job_name) := p_job_name;
4309 Ctx(qa_ss_const.from_op_seq_num) := p_from_op_seq_num;
4310 Ctx(qa_ss_const.vendor_name) := p_vendor_name;
4311 Ctx(qa_ss_const.po_number) := p_po_number;
4312 Ctx(qa_ss_const.ordered_quantity) := p_ordered_quantity;
4313 Ctx(qa_ss_const.vendor_item_number) := p_vendor_item_number;
4314 Ctx(qa_ss_const.vendor_item_number) := p_po_release_num;
4315 Ctx(qa_ss_const.po_release_num) := p_po_release_num;
4316 Ctx(qa_ss_const.uom_name) := p_uom_name;
4317 Ctx(qa_ss_const.production_line) := p_production_line;
4318
4319 l_organization_id := p_organization_id;
4320
4321 chk:= check_plan_for_applicability(Ctx, 100, l_Organization_Id, pid);
4322 RETURN chk;
4323
4324 END is_plan_applicable_for_osp;
4325
4326 -------------------------------------------------------------
4327 FUNCTION is_plan_applicable_for_ship (
4328 Pid IN NUMBER,
4329 p_item IN VARCHAR2 DEFAULT NULL,
4330 p_item_category IN VARCHAR2 DEFAULT NULL,
4331 p_revision IN VARCHAR2 DEFAULT NULL,
4332 p_supplier IN VARCHAR2 DEFAULT NULL,
4333 p_po_number IN VARCHAR2 DEFAULT NULL,
4334 p_po_line_num IN VARCHAR2 DEFAULT NULL,
4335 p_po_shipment_num IN VARCHAR2 DEFAULT NULL,
4336 p_ship_to IN VARCHAR2 DEFAULT NULL,
4337 p_ordered_quantity IN VARCHAR2 DEFAULT NULL,
4338 p_vendor_item_number IN VARCHAR2 DEFAULT NULL,
4339 p_po_release_num IN VARCHAR2 DEFAULT NULL,
4340 p_uom_name IN VARCHAR2 DEFAULT NULL,
4341 p_supplier_site IN VARCHAR2 DEFAULT NULL,
4342 p_ship_to_location IN VARCHAR2 DEFAULT NULL,
4343 p_organization_id IN NUMBER DEFAULT NULL)
4344 RETURN VARCHAR2
4345 IS
4346 Ctx qa_ss_const.Ctx_Table;
4347 -- Ctx_Table type is declared in pkg spec
4348 Prompt_Arr qa_ss_const.var30_table;
4349 lov_arr qa_ss_const.bool_table;
4350 disp_len_arr qa_ss_const.num_table;
4351 dv_arr qa_ss_const.var150_table; -- for Default Value
4352 charid_array qa_ss_const.num_table;
4353 names_array qa_ss_const.var30_table;
4354
4355 no_of_cols NUMBER := 0;
4356 plan_name_i qa_plans.name%TYPE := NULL;
4357 l_language_code varchar2(30);
4358 it_name VARCHAR2(20);
4359 de_name VARCHAR2(20); -- dependent element name
4360 Name VARCHAR2(2000);
4361 char_name_i qa_chars.name%TYPE;
4362 item_name VARCHAR2(30) := NULL;
4363 l_Po_Agent_Id NUMBER := NULL;
4364 l_User_Id NUMBER := NULL;
4365 l_Item_Id NUMBER := NULL;
4366 l_Po_Header_Id NUMBER := NULL;
4367 l_Wip_Entity_Type NUMBER := NULL;
4368 l_Wip_Rep_Sch_Id NUMBER := NULL;
4369 l_Po_Release_Id NUMBER := NULL;
4370 l_Po_Line_Id NUMBER := NULL;
4371 l_Line_Location_Id NUMBER := NULL;
4372 l_Po_Distribution_Id NUMBER := NULL;
4373 l_Wip_Entity_Id NUMBER := NULL;
4374 l_Wip_Line_Id NUMBER := NULL;
4375 l_Po_Shipment_Id NUMBER := NULL;
4376 l_Organization_Id NUMBER := NULL;
4377
4378 temp_pid NUMBER := NULL;
4379 chk VARCHAR2(1);
4380 CURSOR plan_tx_cur IS
4381 SELECT qpt.plan_id
4382 FROM QA_PLAN_TRANSACTIONS QPT
4383 Where qpt.plan_id = Pid
4384 and qpt.transaction_number = 110;
4385
4386 BEGIN
4387 OPEN plan_tx_cur;
4388 FETCH plan_tx_cur INTO temp_pid;
4389 IF plan_tx_cur%NOTFOUND
4390 Then
4391 Return 'N';
4392 END IF;
4393 CLOSE plan_tx_cur;
4394
4395 Ctx.delete();
4396
4397 Ctx(qa_ss_const.item) := p_item;
4398 Ctx(qa_ss_const.item_category) := p_item_category;
4399 Ctx(qa_ss_const.revision) := p_revision;
4400 Ctx(qa_ss_const.vendor_name) := p_supplier;
4401 Ctx(qa_ss_const.po_number) := p_po_number;
4402 Ctx(qa_ss_const.po_line_num) := p_po_line_num;
4403 Ctx(qa_ss_const.po_shipment_num) := p_po_shipment_num;
4404 Ctx(qa_ss_const.ship_to) := p_ship_to;
4405 Ctx(qa_ss_const.ordered_quantity) := p_ordered_quantity;
4406 Ctx(qa_ss_const.vendor_item_number) := p_vendor_item_number;
4407 Ctx(qa_ss_const.po_release_num) := p_po_release_num;
4408 Ctx(qa_ss_const.uom_name) := p_uom_name;
4409 Ctx(qa_ss_const.vendor_site_code) := p_supplier_site;
4410 Ctx(qa_ss_const.ship_to_location) := p_ship_to_location;
4411
4412
4413 l_organization_id := p_organization_id;
4414
4415 chk:= check_plan_for_applicability(Ctx, 110, l_Organization_Id, pid);
4416 RETURN chk;
4417
4418 END is_plan_applicable_for_ship;
4419
4420 -------------------------------------------------------------
4421
4422
4423 function check_plan_for_applicability ( Ctx IN qa_ss_const.Ctx_Table,
4424 x_Txn_Number IN NUMBER,
4425 x_organization_id IN NUMBER,
4426 x_Pid IN NUMBER)
4427 RETURN VARCHAR2
4428
4429 IS
4430
4431 Cursor coll_trigg_cur is
4432 SELECT qpt.Plan_transaction_id,
4433 qpt.Plan_id,
4434 qc.char_id,
4435 qc.dependent_char_id,
4436 qc.datatype,
4437 qpct.Operator,
4438 qpct.Low_Value,
4439 qpct.High_Value
4440 FROM qa_plan_collection_triggers qpct,
4441 qa_plan_transactions qpt,
4442 qa_plans qp,
4443 qa_chars qc,
4444 qa_txn_collection_triggers qtct
4445 WHERE qp.Plan_ID = x_Pid
4446 AND qpt.Plan_ID = qp.Plan_ID
4447 AND qpct.Plan_Transaction_ID(+) = qpt.Plan_Transaction_ID
4448 AND qpct.Collection_Trigger_ID = qtct.Collection_Trigger_ID(+)
4449 AND qpct.Collection_Trigger_ID = qc.char_id(+)
4450 AND qpt.TRANSACTION_NUMBER = x_txn_number
4451 AND qtct.TRANSACTION_NUMBER(+) = x_txn_number
4452 AND qp.ORGANIZATION_ID = x_organization_id
4453 AND qpt.enabled_flag = 1
4454 ORDER BY qpt.plan_transaction_id;
4455
4456 Type Coll_Trigg_Type is TABLE of coll_trigg_cur%ROWTYPE INDEX BY BINARY_INTEGER;
4457 Coll_Trigg_Tab Coll_Trigg_Type;
4458
4459 plan_is_applicable BOOLEAN;
4460 counter INTEGER;
4461 i INTEGER := 1;
4462
4463 l_rowcount INTEGER;
4464
4465 l_datatype NUMBER;
4466 l_operator NUMBER;
4467 l_low_char VARCHAR2(150);
4468 l_high_char VARCHAR2(150);
4469 l_low_number NUMBER;
4470 l_high_number NUMBER;
4471 l_low_date DATE;
4472 l_high_date DATE;
4473 l_value_char VARCHAR2(150);
4474 l_value_number NUMBER;
4475 l_value_date DATE;
4476
4477 l_plan_id NUMBER;
4478 l_old_plan_id NUMBER;
4479
4480 l_plan_txn_id NUMBER ;
4481 l_old_plan_txn_id NUMBER ;
4482
4483
4484 l_char_id NUMBER;
4485 l_dep_char_id NUMBER;
4486 pid_count NUMBER := 0;
4487 atleast_one BOOLEAN;
4488 -- All variables beginning with l_ are local variables
4489 BEGIN
4490
4491 atleast_one := FALSE;
4492 counter := 1;
4493 For ct_rec in coll_trigg_cur
4494 loop
4495 coll_trigg_tab(counter) := ct_rec;
4496 counter := counter + 1;
4497 end loop;
4498
4499 l_rowcount := coll_trigg_tab.count;
4500
4501 if (l_rowcount < 1) Then
4502 return 'N'; -- no plans applicable
4503 end if;
4504
4505 l_plan_txn_id := coll_trigg_tab(1).plan_transaction_id;
4506
4507 -- The variable i has been initialized to 1
4508
4509 WHILE ( i <= l_rowcount)
4510 LOOP
4511 l_old_plan_txn_id := l_plan_txn_id;
4512 plan_is_applicable := TRUE; -- start with this assumption
4513
4514 WHILE (l_plan_txn_id = l_old_plan_txn_id) AND (i <= l_rowcount)
4515 LOOP
4516 IF (plan_is_applicable = TRUE)
4517 THEN
4518 l_operator := coll_trigg_tab(i).Operator;
4519 l_datatype := coll_trigg_tab(i).Datatype;
4520 l_char_id := coll_trigg_tab(i).char_id;
4521 IF (l_operator is NULL) AND (l_datatype is NULL)
4522 THEN
4523 null;
4524 -- null collection trigger. Plan applies
4525 ELSE
4526 -- WATCH OUT FOR EXCEPTIONS while
4527 -- accessing Ctx table below
4528 IF (qltcompb.compare( Ctx(l_char_id),
4529 l_operator,
4530 coll_trigg_tab(i).Low_value,
4531 coll_trigg_tab(i).High_Value,
4532 l_datatype) )
4533 -- above is a overloaded call
4534 THEN
4535 plan_is_applicable := TRUE;
4536 ELSE
4537 plan_is_applicable := FALSE;
4538 END IF; --end qltcompb
4539 END IF; -- end l_operator and l_datatype null
4540 END IF; -- end Check plan applicable is true
4541
4542 i := i+1;
4543 IF (i <= l_rowcount) THEN
4544 l_plan_txn_id := coll_trigg_tab(i).plan_transaction_id;
4545 END IF;
4546 END LOOP; -- end inner while loop
4547 IF (plan_is_applicable = TRUE) THEN
4548 RETURN 'Y';
4549 END IF;
4550 -- if flag is not 2, then keep continuing
4551
4552 END LOOP; -- end outer while loop
4553
4554 RETURN 'N';
4555
4556 END;
4557
4558 function get_rel_num (po_rel_id IN NUMBER)
4559 Return NUMBER
4560 IS
4561 l_rel_num NUMBER := NULL;
4562
4563 CURSOR rel_num_cur IS
4564 SELECT Release_Num
4565 FROM Po_Releases_All
4566 WHERE Po_Release_Id = po_rel_id;
4567
4568 BEGIN
4569 IF ( po_rel_id is NOT Null) Then
4570 Open rel_num_cur;
4571 Fetch rel_num_cur INTO l_rel_num;
4572 Close rel_num_cur;
4573 END IF;
4574 Return l_rel_num;
4575 END;
4576
4577
4578 function get_buyer_id (po_hdr_id IN Number,
4579 po_rel_id IN Number)
4580 Return NUMBER
4581 IS
4582 l_buyer_id NUMBER := NULL;
4583
4584 CURSOR buyer1_cur IS
4585 Select AGENT_ID
4586 From PO_HEADERS_ALL
4587 Where PO_HEADER_ID = po_hdr_id;
4588
4589 CURSOR buyer2_cur IS
4590 Select AGENT_ID
4591 From PO_RELEASES_ALL
4592 Where PO_RELEASE_ID = po_rel_id;
4593
4594 BEGIN
4595 IF (po_rel_id is NOT NULL) THEN
4596 OPEN buyer2_cur;
4597 FETCH buyer2_cur INTO l_buyer_id;
4598 CLOSE buyer2_cur;
4599 ELSIF (po_hdr_id is NOT NULL) THEN
4600 OPEN buyer1_cur;
4601 FETCH buyer1_cur INTO l_buyer_id;
4602 CLOSE buyer1_cur;
4603 END IF;
4604
4605 Return l_buyer_id;
4606 END;
4607
4608 function is_context_element (element_id IN NUMBER, txn_number IN NUMBER)
4609 RETURN VARCHAR2
4610 -- This is a wrapper to return varchar2 so it can be made
4611 -- use of in sql statement. Boolean wont work.
4612 -- 'Y' or 'N' returned
4613 -- look also at qltakmpb.plb function context_element
4614 IS
4615 result BOOLEAN;
4616 dummy NUMBER;
4617
4618 CURSOR c IS
4619 SELECT 1
4620 FROM qa_txn_collection_triggers qtct
4621 WHERE qtct.transaction_number = txn_number
4622 AND qtct.collection_trigger_id = element_id;
4623
4624 BEGIN
4625 -- This function determines if collection element is a context element
4626 -- given a transaction number.
4627
4628 OPEN c;
4629 FETCH c INTO dummy;
4630 result := c%FOUND;
4631 CLOSE c;
4632
4633 if (result)
4634 then
4635 Return 'Y';
4636 else
4637 Return 'N';
4638 end if;
4639 end; -- end function is_context_element
4640 ----------------------------------------------------------
4641 function Check_Valid_Job (X_Wip_Entity_Id IN NUMBER)
4642 Return VARCHAR2
4643 IS
4644 l_ent_type NUMBER;
4645 l_dummy_var NUMBER;
4646 valid_ok BOOLEAN := TRUE;
4647 CURSOR we_type_cur IS
4648 SELECT entity_type
4649 FROM WIP_ENTITIES
4650 WHERE WIP_ENTITY_ID = X_Wip_Entity_Id;
4651
4652 -- #2382432
4653 -- Changed the view to WIP_DISCRETE_JOBS_ALL_V instead of
4654 -- earlier wip_open_discrete_jobs_val_v
4655 -- rkunchal Sun Jun 30 22:59:11 PDT 2002
4656
4657 CURSOR wdj_open_val_cur IS
4658 SELECT wip_entity_id
4659 FROM WIP_DISCRETE_JOBS_ALL_V
4660 WHERE WIP_ENTITY_ID = X_Wip_Entity_Id;
4661
4662 BEGIN
4663 OPEN we_type_cur;
4664 FETCH we_type_cur INTO l_ent_type;
4665 CLOSE we_type_cur;
4666 IF (l_ent_type = 3) THEN -- closed discrete job
4667 valid_ok := FALSE; -- not valid for eqr
4668 END IF;
4669 IF (l_ent_type = 1) THEN -- open disc job;need more eval
4670 OPEN wdj_open_val_cur;
4671 FETCH wdj_open_val_cur INTO l_dummy_var;
4672 IF wdj_open_val_cur%FOUND THEN
4673 valid_ok := TRUE;
4674 ELSE
4675 valid_ok := FALSE;
4676 -- set to FALSE only if l_ent_type = 1
4677 -- AND not in WIP_OPEN_DISCRETE_JOBS_VAL_V
4678 END IF;
4679 CLOSE wdj_open_val_cur;
4680 END IF;
4681 IF (valid_ok) THEN
4682 RETURN 'Y';
4683 ELSE
4684 RETURN 'N';
4685 END IF;
4686
4687 END Check_Valid_Job;
4688
4689
4690 end qa_ss_core;
4691