[Home] [Help]
PACKAGE BODY: APPS.QA_RESULTS_INTERFACE_PKG
Source
1 PACKAGE BODY QA_RESULTS_INTERFACE_PKG as
2 /* $Header: qltimptb.plb 120.4 2011/02/11 11:36:54 ntungare ship $ */
3
4
5 FUNCTION dequote(s1 in varchar2) RETURN varchar2 IS
6 --
7 -- The string s1 may be used in a dynamically constructed SQL
8 -- statement. If s1 contains a single quote, there will be syntax
9 -- error. This function returns a string s2 that is same as s1
10 -- except each single quote is replaced with two single quotes.
11 -- Put in for NLS fix. Previously if plan name or element name
12 -- contains a single quote, that will cause problem when creating
13 -- views.
14 -- bso
15 --
16 BEGIN
17 RETURN replace(s1, '''', '''''');
18 END dequote;
19
20
21 FUNCTION BUILD_VQR_SQL ( given_plan_id IN NUMBER,
22 ss_where_clause in varchar2 default null)
23 return VARCHAR2 as
24
25 begin
26
27 DECLARE
28
29 cursor CES is select result_column_name, qpc.char_id
30 from qa_plan_chars qpc, qa_chars qc
31 where plan_id = given_plan_id
32 and qpc.char_id = qc.char_id
33 and qpc.enabled_flag = 1
34 order by prompt_sequence;
35
36 first NUMBER;
37 column_name VARCHAR2(240);
38 key VARCHAR2(240);
39 select_clause VARCHAR2(32000);
40 from_clause VARCHAR2(1000);
41 where_clause VARCHAR2(1000);
42 table_name VARCHAR2(240);
43 org_id NUMBER;
44 begin
45
46
47 select_clause := 'SELECT ';
48 from_clause := ' FROM qa_results_v' ;
49 where_clause := ' WHERE plan_id = ' || given_plan_id ||
50 ' AND' || ' (status = 2 or status is null) ' ;
51 if (ss_where_clause is not null) then
52 where_clause := where_clause || ' AND ' || ss_where_clause;
53 end if;
54
55 where_clause := where_clause || ' ORDER BY ' || 'occurrence desc';
56
57
58 first := 1;
59
60 -- select min(organization_id) into org_id
61 -- from qa_results_v
62 -- where plan_id = given_plan_id;
63
64 FOR element in CES LOOP
65
66
67 if ((element.char_id = 10) or element.char_id = 60) then
68
69 column_name := 'qa_flex_util.item(organization_id , item_id) ITEM';
70
71
72 elsif ((element.char_id = 15) or (element.char_id = 65)) then
73 column_name := 'qa_flex_util.locator(organization_id, locator_id) LOCATOR';
74
75 else
76 column_name := upper(QA_CORE_PKG.get_result_column_name(element.char_id, given_plan_id));
77
78 end if;
79
80 if (first = 1) then
81 select_clause := select_clause || column_name;
82 first := 0;
83 else
84 select_clause := select_clause || ', ' || column_name;
85 end if;
86
87
88 END LOOP;
89
90 -- This sql script should return QA_CREATED_BY_NAME instead of CREATED_BY
91 -- please see bug # 1054779.
92
93 select_clause := select_clause || ', ' || 'QA_CREATED_BY_NAME'
94 || ', ' || 'COLLECTION_ID'
95 || ', ' || 'fnd_date.date_to_chardt(LAST_UPDATE_DATE,2) LAST_UPDATE_DATE';
96
97 select_clause := select_clause || from_clause || where_clause;
98 return select_clause;
99 end;
100
101 EXCEPTION when others then
102 raise;
103
104 End BUILD_VQR_SQL;
105
106
107 FUNCTION build_select_clause (p_plan_id IN NUMBER)
108 RETURN VARCHAR2 IS
109
110 cursor CES is
111 SELECT result_column_name, qpc.char_id, qc.datatype
112 FROM qa_plan_chars qpc, qa_chars qc
113 WHERE plan_id = p_plan_id
114 AND qpc.char_id = qc.char_id
115 AND qpc.enabled_flag = 1
116 ORDER BY prompt_sequence;
117
118 first NUMBER;
119 column_name VARCHAR2(240);
120 select_clause VARCHAR2(32000);
121
122 BEGIN
123
124 first := 1;
125
126 FOR element in CES LOOP
127
128 IF element.char_id = qa_ss_const.item THEN
129 column_name := 'qa_flex_util.item(organization_id , item_id) ITEM';
130 ELSIF element.char_id = qa_ss_const.comp_item THEN
131 column_name := 'qa_flex_util.item(organization_id , comp_item_id) COMP_ITEM';
132
133 -- anagarwa Fri Feb 7 14:38:21 PST 2003
134 -- Bug 2792353
135 -- even though following 2 statements are for bill reference and
136 -- routing reference, we still use qa_flex_util.item to get the values
137 -- because foreign keys for both bill reference and routing reference
138 -- point to mtl_system_items_kfv.inventory_item_id and the actual
139 -- value comes from concatenated segments.
140 -- Therefore there is no need to write a separate function for these
141 -- 2 elements.
142
143 ELSIF element.char_id = qa_ss_const.bill_reference THEN
144 column_name := 'qa_flex_util.item(organization_id, bill_reference_id) BILL_REFERENCE';
145
146 ELSIF element.char_id = qa_ss_const.routing_reference THEN
147 column_name := 'qa_flex_util.item(organization_id, routing_reference_id) ROUTING_REFERENCE';
148
149 ELSIF element.char_id = qa_ss_const.locator THEN
150 column_name := 'qa_flex_util.locator(organization_id, locator_id)
151 LOCATOR';
152
153 ELSIF element.char_id = qa_ss_const.comp_locator THEN
154 column_name := 'qa_flex_util.locator(organization_id, comp_locator_id)
155 COMP_LOCATOR';
156
157 ELSIF element.char_id = qa_ss_const.to_locator THEN
158 column_name := 'qa_flex_util.locator(organization_id, to_locator_id)
159 TO_LOCATOR';
160
161 -- end of changes by anagarwa
162
163 /*
164 Bug 4261053.suramasw.
165
166 Appending ASSET_GROUP_ID to be assigned to column_name when assigning
167 ASSET_GROUP. This was done so that view quality results in EAM Asset
168 Query transaction shows the quality result(s) collected only for the
169 asset number, which the user tries to view. Plz see the method
170 setupStandAloneQuery in QaVqrDataTopCO.java to know the details on
171 why ASSET_GROUP_ID should also be assigned to column_name.
172 */
173 --dgupta: Start R12 EAM Integration. Bug 4345492
174 ELSIF element.char_id = qa_ss_const.asset_group THEN
175 column_name := 'qa_flex_util.get_asset_group_name(organization_id,
176 asset_group_id) ASSET_GROUP, ASSET_GROUP_ID';
177 --dgupta: End R12 EAM Integration. Bug 4345492
178 ELSIF element.char_id = qa_ss_const.asset_activity THEN
179 column_name := 'qa_flex_util.item(organization_id,
180 asset_activity_id) ASSET_ACTIVITY';
181
182 -- added the following to include new hardcoded element followup activity
183 -- saugupta
184
185 ELSIF element.char_id = qa_ss_const.followup_activity THEN
186 column_name := 'qa_flex_util.item(organization_id,
187 followup_activity_id) FOLLOWUP_ACTIVITY';
188
189 ELSIF element.char_id = qa_ss_const.work_order THEN
190 column_name := 'qa_flex_util.work_order(organization_id, work_order_id) WORK_ORDER';
191
192 ELSIF not qa_plan_element_api.hardcoded(element.char_id) and
193 element.datatype in (qa_ss_const.date_datatype ,
194 qa_ss_const.datetime_datatype) then
195 column_name := upper(QA_CORE_PKG.get_result_column_name(element.char_id, p_plan_id));
196 column_name := 'fnd_date.canonical_to_date(' || column_name || ') ' || column_name;
197 ELSE
198 column_name := upper(QA_CORE_PKG.get_result_column_name(
199 element.char_id, p_plan_id));
200 END IF;
201
202 IF (first = 1) THEN
203 select_clause := select_clause || column_name;
204 first := 0;
205 ELSE
206 select_clause := select_clause || ', ' || column_name;
207 END IF;
208
209 END LOOP;
210
211 -- rkaza. bug 3265506. 11/18/2003. Adding status column
212 select_clause := select_clause || ', OCCURRENCE, PLAN_ID, STATUS ';
213 RETURN select_clause;
214
215 END build_select_clause;
216
217
218 FUNCTION get_txn_specific_where_clause(p_txn_number IN NUMBER, p_plan_id IN
219 NUMBER, p_values_table IN qa_ss_const.ctx_table)
220 RETURN VARCHAR2 IS
221
222 CURSOR c1 IS
223 SELECT collection_trigger_id
224 FROM qa_txn_collection_triggers
225 WHERE transaction_number = p_txn_number
226 AND search_flag = 1;
227
228 element_id NUMBER;
229 column_name VARCHAR2(240);
230 column_value VARCHAR2(150);
231 where_clause VARCHAR2(2000) DEFAULT NULL;
232
233 BEGIN
234
235 OPEN c1;
236 LOOP
237
238 FETCH c1 INTO element_id;
239 EXIT WHEN c1%NOTFOUND;
240
241 IF qa_plan_element_api.element_in_plan(p_plan_id, element_id) THEN
242
243 IF element_id IN (qa_ss_const.item, qa_ss_const.comp_item) then
244 column_name := 'qa_flex_util.item(organization_id , item_id)';
245
246 ELSIF element_id IN (qa_ss_const.locator,
247 qa_ss_const.comp_locator) THEN
248 column_name := 'qa_flex_util.locator(organization_id,
249 locator_id)';
250 --dgupta: Start R12 EAM Integration. Bug 4345492
251 ELSIF element_id IN (qa_ss_const.asset_group) then
252 column_name := 'qa_flex_util.get_asset_group_name(organization_id,
253 asset_group_id)';
254 --dgupta: End R12 EAM Integration. Bug 4345492
255 ELSIF element_id = qa_ss_const.asset_activity THEN
256 column_name := 'qa_flex_util.item(organization_id,
257 asset_activity_id)';
258
259 -- added the following to include new hardcoded element followup activity
260 -- saugupta
261
262 ELSIF element_id = qa_ss_const.followup_activity THEN
263 column_name := 'qa_flex_util.item(organization_id,
264 followup_activity_id)';
265
266
267 ELSIF element_id = qa_ss_const.work_order THEN
268 column_name := 'qa_flex_util.work_order(organization_id, work_order_id)';
269
270 ELSE
271 column_name := upper(QA_CORE_PKG.get_result_column_name(
272 element_id, p_plan_id));
273 END IF;
274
275 where_clause := where_clause || ' and ' || column_name;
276 column_value := p_values_table(element_id);
277
278 IF (column_value IS NOT NULL) THEN
279 where_clause := where_clause || ' = '
280 || '''' || column_value || '''';
281 ELSE
282 where_clause := where_clause || ' IS NULL';
283 END IF;
284
285 END IF;
286
287 END LOOP;
288 CLOSE c1;
289
290 RETURN where_clause;
291
292 END get_txn_specific_where_clause;
293
294
295 FUNCTION BUILD_OSP_VQR_SQL ( p_plan_id IN NUMBER,
296 p_item IN VARCHAR2 DEFAULT NULL,
297 p_revision IN VARCHAR2 DEFAULT NULL,
298 p_job_name IN VARCHAR2 DEFAULT NULL,
299 p_from_op_seq_num IN VARCHAR2 DEFAULT NULL,
300 p_vendor_name IN VARCHAR2 DEFAULT NULL,
301 p_po_number IN VARCHAR2 DEFAULT NULL,
302 p_ordered_quantity IN VARCHAR2 DEFAULT NULL,
303 p_vendor_item_number IN VARCHAR2 DEFAULT NULL,
304 p_po_release_num IN VARCHAR2 DEFAULT NULL,
305 p_uom_name IN VARCHAR2 DEFAULT NULL,
306 p_production_line IN VARCHAR2 DEFAULT NULL,
307 p_po_header_id IN NUMBER DEFAULT NULL)
308 RETURN VARCHAR2 IS
309
310 -- po_header_id is being added for security reasons
311
312 select_clause VARCHAR2(32000);
313 from_clause VARCHAR2(1000);
314 where_clause VARCHAR2(1000);
315 order_by_clause VARCHAR2(100);
316 values_table qa_ss_const.ctx_table;
317
318 BEGIN
319
320 values_table.delete();
321
322 values_table(qa_ss_const.item) := p_item;
323 values_table(qa_ss_const.revision) := p_revision;
324 values_table(qa_ss_const.job_name) := p_job_name;
325 values_table(qa_ss_const.from_op_seq_num) := p_from_op_seq_num;
326 values_table(qa_ss_const.vendor_name) := p_vendor_name;
327 values_table(qa_ss_const.po_number) := p_po_number;
328 values_table(qa_ss_const.ordered_quantity) := p_ordered_quantity;
329 values_table(qa_ss_const.vendor_item_number) := p_vendor_item_number;
330 values_table(qa_ss_const.po_release_num) := p_po_release_num;
331 values_table(qa_ss_const.uom_name) := p_uom_name;
332 values_table(qa_ss_const.production_line) := p_production_line;
333
334 select_clause := 'SELECT ';
335 from_clause := ' FROM qa_results_v' ;
336 where_clause := ' WHERE plan_id = ' || p_plan_id ||
337 ' AND' || ' (status = 2 or status is null) ' ;
338
339 select_clause := select_clause || build_select_clause(p_plan_id);
340
341 -- bug 3178307. rkaza. 10/07/2003. Timezone Support.
342 select_clause := select_clause || ', '
343 || 'QA_CREATED_BY_NAME'
344 || ', ' || 'COLLECTION_ID' || ', '
345 || 'LAST_UPDATE_DATE';
346
347
348 -- If po_header_id is null then we should not return any quality results
349 -- The above is for security reasons.
350
351 IF (p_po_header_id IS NULL) THEN
352 where_clause := ' WHERE 1 = 2 ';
353 ELSE
354 where_clause := where_clause || get_txn_specific_where_clause(
355 qa_ss_const.ss_outside_processing_txn, p_plan_id, values_table);
356 where_clause := where_clause ||
357 ' AND PO_HEADER_ID = ' || p_po_header_id;
358 END IF;
359
360 order_by_clause := ' ORDER BY ' || 'occurrence desc';
361
362 select_clause := select_clause || from_clause || where_clause
363 || order_by_clause;
364
365 RETURN select_clause;
366
367 End BUILD_OSP_VQR_SQL;
368
369
370 FUNCTION BUILD_SHIPMENT_VQR_SQL ( p_plan_id IN NUMBER,
371 p_item IN VARCHAR2 DEFAULT NULL,
372 p_item_category IN VARCHAR2 DEFAULT NULL,
373 p_revision IN VARCHAR2 DEFAULT NULL,
374 p_supplier IN VARCHAR2 DEFAULT NULL,
375 p_po_number IN VARCHAR2 DEFAULT NULL,
376 p_po_line_num IN VARCHAR2 DEFAULT NULL,
377 p_po_shipment_num IN VARCHAR2 DEFAULT NULL,
378 p_ship_to IN VARCHAR2 DEFAULT NULL,
379 p_ordered_quantity IN VARCHAR2 DEFAULT NULL,
380 p_vendor_item_number IN VARCHAR2 DEFAULT NULL,
381 p_po_release_num IN VARCHAR2 DEFAULT NULL,
382 p_uom_name IN VARCHAR2 DEFAULT NULL,
383 p_supplier_site IN VARCHAR2 DEFAULT NULL,
384 p_ship_to_location IN VARCHAR2 DEFAULT NULL,
385 p_po_header_id IN NUMBER DEFAULT NULL)
386 RETURN VARCHAR2 IS
387
388 -- po_header_id is being added for security reasons
389
390 select_clause VARCHAR2(32000);
391 from_clause VARCHAR2(1000);
392 where_clause VARCHAR2(1000);
393 order_by_clause VARCHAR2(100);
394 table_name VARCHAR2(240);
395 values_table qa_ss_const.ctx_table;
396
397 BEGIN
398
399 values_table.delete();
400
401 values_table(qa_ss_const.item) := p_item;
402 values_table(qa_ss_const.item_category) := p_item_category;
403 values_table(qa_ss_const.revision) := p_revision;
404 values_table(qa_ss_const.vendor_name) := p_supplier;
405 values_table(qa_ss_const.po_number) := p_po_number;
406 values_table(qa_ss_const.po_line_num) := p_po_line_num;
407 values_table(qa_ss_const.po_shipment_num) := p_po_shipment_num;
408 values_table(qa_ss_const.ship_to) := p_ship_to;
409 values_table(qa_ss_const.ordered_quantity) := p_ordered_quantity;
410 values_table(qa_ss_const.vendor_item_number) := p_vendor_item_number;
411 values_table(qa_ss_const.po_release_num) := p_po_release_num;
412 values_table(qa_ss_const.uom_name) := p_uom_name;
413 values_table(qa_ss_const.vendor_site_code) := p_supplier_site;
414 values_table(qa_ss_const.ship_to_location) := p_ship_to_location;
415
416 select_clause := 'SELECT ';
417 from_clause := ' FROM qa_results_v' ;
418 where_clause := ' WHERE plan_id = ' || p_plan_id ||
419 ' AND' || ' (status = 2 or status is null) ' ;
420
421 select_clause := select_clause || build_select_clause(p_plan_id);
422
423 -- bug 3178307. rkaza. 10/07/2003. Timezone Support.
424 select_clause := select_clause || ', '
425 || 'QA_CREATED_BY_NAME'
426 || ', ' || 'COLLECTION_ID' || ', '
427 || 'LAST_UPDATE_DATE';
428
429 -- If po_header_id is null then we should not return any quality results
430 -- The above is for security reasons.
431
432 IF (p_po_header_id IS NULL) THEN
433 where_clause := ' WHERE 1 = 2 ';
434 ELSE
435 where_clause := where_clause || get_txn_specific_where_clause(
436 qa_ss_const.ss_shipments_txn, p_plan_id, values_table);
437 where_clause := where_clause ||
438 ' AND PO_HEADER_ID = ' || p_po_header_id;
439 END IF;
440
441 order_by_clause := ' ORDER BY ' || 'occurrence desc';
442
443 select_clause := select_clause || from_clause || where_clause
444 || order_by_clause;
445
446 RETURN select_clause;
447
448 End BUILD_SHIPMENT_VQR_SQL;
449
450
451 FUNCTION BUILD_OM_VQR_SQL ( p_plan_id IN NUMBER,
452 p_so_header_id IN VARCHAR2,
453 p_so_line_id IN VARCHAR2 DEFAULT NULL,
454 p_item_id IN VARCHAR2 DEFAULT NULL)
455 RETURN VARCHAR2 IS
456
457 select_clause VARCHAR2(32000);
458 from_clause VARCHAR2(1000);
459 where_clause VARCHAR2(1000);
460 order_by_clause VARCHAR2(100);
461
462 l_sales_order_id NUMBER := -99;
463
464 BEGIN
465
466 select_clause := 'SELECT ';
467 from_clause := ' FROM qa_results_v' ;
468 where_clause := ' WHERE plan_id = ' || p_plan_id ||
469 ' AND' || ' (status = 2 or status is null) ' ;
470
471 select_clause := select_clause || build_select_clause(p_plan_id);
472
473 -- bug 3178307. rkaza. 10/07/2003. Timezone Support.
474 select_clause := select_clause || ', '
475 || 'QA_CREATED_BY_NAME'
476 || ', ' || 'COLLECTION_ID' || ', '
477 || 'LAST_UPDATE_DATE';
478
479 l_sales_order_id :=
480 qa_results_interface_pkg.OEHeader_to_MTLSales
481 ( p_so_header_id );
482
483 where_clause := where_clause || 'AND so_header_id = ' || l_sales_order_id;
484
485 IF (p_item_id IS NOT NULL) THEN
486 where_clause := where_clause || 'AND item_id = ' || p_item_id;
487 END IF;
488
489 order_by_clause := ' ORDER BY ' || 'occurrence desc';
490
491 select_clause := select_clause || from_clause || where_clause
492 || order_by_clause;
493
494 RETURN select_clause;
495
496 End BUILD_OM_VQR_SQL;
497
498 --parent-child modifications
499 FUNCTION get_plan_vqr_sql (p_plan_id IN NUMBER)
500 RETURN VARCHAR2 IS
501
502 select_clause VARCHAR2(32000);
503 from_clause VARCHAR2(1000);
504 where_clause VARCHAR2(1000);
505 BEGIN
506
507 select_clause := 'SELECT ';
508 from_clause := ' FROM qa_results_v' ;
509 -- where_clause := ' WHERE plan_id = ' || p_plan_id ;
510 select_clause := select_clause || build_select_clause(p_plan_id);
511 select_clause := select_clause || ' , name ';
512 --Ilam removed extra collection id from above
513 --it was redundant collection_id was mentioned twice earlier
514
515 select_clause := select_clause || ', ' || 'QA_CREATED_BY_NAME' || ', '
516 || 'COLLECTION_ID' || ', ' || 'LAST_UPDATE_DATE';
517
518 -- || 'fnd_date.date_to_chardt(LAST_UPDATE_DATE) LAST_UPDATE_DATE';
519
520 RETURN select_clause || from_clause;
521
522 END get_plan_vqr_sql;
523
524
525 FUNCTION build_asset_vqr_sql ( p_plan_id IN NUMBER DEFAULT NULL,
526 p_asset_group IN VARCHAR2 DEFAULT NULL,
527 p_asset_number IN VARCHAR2 DEFAULT NULL)
528 RETURN VARCHAR2 IS
529
530 select_clause VARCHAR2(32000);
531 from_clause VARCHAR2(1000);
532 where_clause VARCHAR2(1000);
533 order_by_clause VARCHAR2(100);
534
535 BEGIN
536
537 select_clause := 'SELECT ';
538 from_clause := ' FROM qa_results_v' ;
539 where_clause := ' WHERE plan_id = ' || p_plan_id ||
540 ' AND' || ' (status = 2 or status is null) ' ;
541
542 select_clause := select_clause || build_select_clause(p_plan_id);
543
544 select_clause := select_clause || ', '
545 || 'QA_CREATED_BY_NAME'
546 || ', ' || 'COLLECTION_ID' || ', '
547 || 'fnd_date.date_to_chardt(LAST_UPDATE_DATE,2) LAST_UPDATE_DATE';
548
549 order_by_clause := ' ORDER BY ' || 'occurrence desc';
550
551 select_clause := select_clause || from_clause;
552
553 -- || where_clause
554 -- || order_by_clause;
555
556 RETURN select_clause;
557
558 END build_asset_vqr_sql;
559
560
561 PROCEDURE START_IMPORT_ROW ( proc_status IN NUMBER,
562 org_id IN NUMBER,
563 given_plan_id IN NUMBER,
564 script OUT NOCOPY VARCHAR2,
565 tail_script OUT NOCOPY VARCHAR2,
566 source_code IN VARCHAR2 default null,
567 source_line_id IN NUMBER default null,
568 po_agent_id IN NUMBER default null ) IS
569
570 plan_name qa_plans.name%TYPE;
571 org_code mtl_parameters.organization_code%TYPE;
572
573
574
575 BEGIN
576
577 script := null;
578 tail_script := null;
579
580 select organization_code into org_code
581 from mtl_parameters
582 where organization_id = org_id;
583
584
585 select name into plan_name
586 from qa_plans
587 where plan_id = given_plan_id;
588
589 -- plan_name := ''''||upper(plan_name)|| '''';
590 -- CHANGED on Aug 20,1999 to fix single quotes problem
591 plan_name := upper(plan_name);
592
593
594 script := 'Insert into qa_results_interface ' ||
595 '(process_status, organization_code, plan_name, source_code, source_line_id, po_agent_id';
596
597 tail_script := ' values ( ' || proc_status || ', '
598 || '''' || dequote(org_code) || '''' || ', '|| '''' ||
599 dequote(plan_name) || '''';
600 -- CHANGED on Aug 20,1999 to fix single quotes problem
601 -- Because dequote for plan_name was causing problem above
602
603 if (source_code is not null) then
604 tail_script := tail_script || ', ' || '''' ||source_code || '''';
605 else
606 tail_script := tail_script || ', null ';
607 end if;
608
609
610 if (source_line_id is not null) then
611 tail_script := tail_script || ', ' || '''' || to_char(source_line_id)|| '''';
612 else
613 tail_script := tail_script || ', null ';
614 end if;
615
616 if (po_agent_id is not null) then
617 tail_script := tail_script || ', ' || '''' || to_char(po_agent_id)|| '''';
618 else
619 tail_script := tail_script || ', null ';
620 end if;
621
622
623
624 EXCEPTION when others then
625 raise;
626
627 END START_IMPORT_ROW;
628
629
630
631 FUNCTION ADD_ELEMENT_VALUE ( GIVEN_PLAN_ID IN NUMBER,
632 ELEMENT_ID IN NUMBER,
633 ELEMENT_VALUE IN VARCHAR2,
634 SCRIPT IN OUT NOCOPY VARCHAR2,
635 TAIL_SCRIPT IN OUT NOCOPY VARCHAR2) return NUMBER IS
636
637 BEGIN
638
639 DECLARE
640 DATE_FORMAT_ERROR EXCEPTION;
641 INVALID_DATE EXCEPTION;
642
643 PRAGMA EXCEPTION_INIT (DATE_FORMAT_ERROR, -1861);
644 PRAGMA EXCEPTION_INIT (INVALID_DATE, -1858);
645
646 element_name VARCHAR2(240);
647 temp_date DATE;
648 temp VARCHAR2(240);
649 temp_num NUMBER;
650 date_flag NUMBER default 0;
651 BEGIN
652
653
654 if (ELEMENT_ID is not null) then
655
656
657 if (ELEMENT_VALUE is null) then
658 if (QA_CORE_PKG.is_mandatory (GIVEN_PLAN_ID, ELEMENT_ID) = True) then
659 return 4;
660 end if;
661 end if;
662
663 element_name := QA_CORE_PKG.get_result_column_name (ELEMENT_ID, given_plan_id);
664
665 script := script || ', ' || element_name;
666
667 if (QA_CORE_PKG.get_element_data_type(Element_ID) = 3 ) then
668 if (ELEMENT_ID = 1 ) then
669 date_flag := -1;
670 temp_date := qltdate.any_to_date(ELEMENT_VALUE);
671 date_flag := 0;
672 tail_script := tail_script || ', ' || ''''|| temp_date || '''';
673 else
674 temp :=qltdate.any_to_canon(ELEMENT_VALUE);
675 tail_script := tail_script || ', ' || ''''|| temp || '''';
676 end if;
677
678 elsif (QA_CORE_PKG.get_element_data_type(ELEMENT_ID) = 2 ) then
679 temp_num := to_number(ELEMENT_VALUE);
680 tail_script := tail_script || ', ' || ''''|| temp_num || '''';
681 else
682 tail_script := tail_script || ', ' || ''''|| dequote(ELEMENT_VALUE) || '''';
683 end if;
684 return 0;
685
686 else
687 return -1;
688
689 end if;
690
691
692 EXCEPTION
693
694 when INVALID_NUMBER or VALUE_ERROR then
695 return 2;
696
697 when DATE_FORMAT_ERROR OR INVALID_DATE then
698 return 3;
699
700 when OTHERS then
701 if (date_flag = -1) then
702 return 3;
703 else
704 return 1;
705 end if;
706 END;
707
708 END ADD_ELEMENT_VALUE;
709
710
711
712 PROCEDURE END_IMPORT_ROW ( script IN VARCHAR2,
713 tail_script IN VARCHAR2, no_error IN BOOLEAN) IS
714
715 final_script VARCHAR2(32000);
716
717 BEGIN
718
719 if (no_error) then
720
721 final_script := script || ') ' || taiL_script || ')';
722 -- htp.p(final_script); htp.nl;
723 QA_CORE_PKG.EXEC_SQL(final_script);
724
725 end if;
726
727
728 EXCEPTION when others then
729 raise;
730
731 END END_IMPORT_ROW;
732
733
734
735 FUNCTION COMMIT_ROWS RETURN NUMBER IS
736
737 BEGIN
738
739 commit;
740 return 0;
741
742 EXCEPTION when others then
743 raise;
744 END;
745
746
747 FUNCTION ROLLBACK_ROWS RETURN NUMBER IS
748
749 BEGIN
750
751 rollback;
752 return 0;
753
754 EXCEPTION when others then
755 raise;
756
757 END;
758
759
760
761 --Bug 3140760
762 --A sales order has a representation in two tables
763 --OE_HEADERS_ALL.HEADER_ID and MTL_SALES_ORDERS.SALES_ORDER_ID
764 --Given a header id, finding the equivalent sales_order_id is a little tricky
765 --Similar logic is done in the view QA_SALES_ORDERS_LOV_V
766 --Function below is built for Convenience purpose
767 --This function takes a SO Header id (OE_HEADERS_ALL.HEADER_ID)
768 --Computes the equivalent Sales_order_id in Mtl_sales_orders and return it
769 --
770 FUNCTION OEHEADER_TO_MTLSALES ( p_oe_header_id IN NUMBER )
771 RETURN NUMBER
772 IS
773
774 l_sales_order_id NUMBER := -99;
775
776 --
777 -- bug 4328665
778 -- Added the function to_char() to the
779 -- filtering condition
780 -- mso.segment1 = oe.order_number
781 -- so that the index MTL_SALES_ORDERS_N1 is
782 -- refrerred to; while selecting the data from
783 -- MTL_SALES_ORDERS table, instead of doing
784 -- a FTS.
785 -- ntungare Wed May 2 03:50:09 PDT 2007
786 --
787 CURSOR mtl_so_cur IS
788 SELECT mso.sales_order_id
789 FROM mtl_sales_orders mso,
790 oe_order_headers_all oe,
791 qa_customers_lov_v rc,
792 oe_transaction_types_tl ot,
793 fnd_languages fl
794 WHERE mso.segment1 = to_char(oe.order_number)
795 AND oe.order_type_id = ot.transaction_type_id
796 AND ot.language = fl.language_code
797 AND fl.installed_flag = 'B'
798 AND oe.sold_to_org_id = rc.customer_id (+)
799 AND mso.segment2 = ot.name
800 AND mso.segment3 = fnd_profile.value('ONT_SOURCE_CODE')
801 AND oe.header_id = p_oe_header_id;
802
803 BEGIN
804
805 open mtl_so_cur;
806 fetch mtl_so_cur into l_sales_order_id;
807 close mtl_so_cur;
808
809 RETURN l_sales_order_id; -- Negative 99 returned if not found
810
811 END OEHEADER_TO_MTLSALES;
812
813
814
815 end QA_RESULTS_INTERFACE_PKG;
816