1 PACKAGE BODY QLTSMENB as
2 /* $Header: qltsmenb.plb 115.7 2002/11/27 19:30:12 jezheng ship $ */
3
4 -- 8/2/95 - CREATED
5 -- Kevin Wiggen
6
7 -- This package does the join to other tables for qa results
8 -- It needs the char_id and value from results, and it will perform the lookup
9 -- It is not necessary to check if there is a lookup first, but its suggested
10
11 FUNCTION LOOKUP(x_char_id IN NUMBER,
12 x_value IN VARCHAR2)
13 RETURN VARCHAR2 IS
14
15 return_value_char VARCHAR2(1500);
16 return_value_num NUMBER;
17 return_value_date DATE;
18
19 used_where BOOLEAN := FALSE;
20 V_DATATYPE NUMBER;
21 V_FK_LOOKUP_TYPE NUMBER;
22 V_FK_TABLE_NAME VARCHAR2(30);
23 V_FK_TABLE_SHORT_NAME VARCHAR2(5);
24 V_PK_ID VARCHAR2(30);
25 V_PK_ID2 VARCHAR2(30);
26 V_PK_ID3 VARCHAR2(30);
27 V_FK_ID VARCHAR2(30);
28 V_FK_ID2 VARCHAR2(30);
29 V_FK_ID3 VARCHAR2(30);
30 V_FK_MEANING VARCHAR2(30);
31 V_FK_ADD_WHERE VARCHAR2(2000);
32
33 source_cursor integer;
34 ignore integer;
35
36 V_CATEGORY_SET_ID VARCHAR2(1000);
37
38 v_sql_statement VARCHAR2(20000) := null;
39
40 cursor info is
41 select DATATYPE, FK_LOOKUP_TYPE, FK_TABLE_NAME, FK_TABLE_SHORT_NAME,
42 PK_ID, PK_ID2, PK_ID3, FK_ID, FK_ID2, FK_ID3, FK_MEANING,
43 FK_ADD_WHERE
44 from qa_chars
45 where char_id = x_char_id;
46
47 BEGIN
48 IF x_value IS NULL THEN
49 RETURN(x_value);
50 END IF;
51
52 open info;
53 fetch info into V_DATATYPE, V_FK_LOOKUP_TYPE, V_FK_TABLE_NAME,
54 V_FK_TABLE_SHORT_NAME, V_PK_ID, V_PK_ID2, V_PK_ID3,
55 V_FK_ID, V_FK_ID2, V_FK_ID3, V_FK_MEANING, V_FK_ADD_WHERE;
56
57 close info;
58
59 if (V_FK_LOOKUP_TYPE is null) or (V_FK_LOOKUP_TYPE = 2) then
60 -- no lookup return original value
61 RETURN(x_value);
62 /*
63 elsif x_char_id = 33 then -- sales order number
64 --
65 -- Need special processing for sales order number for now because
66 -- its data reside on two tables instead of one. Will try to fix
67 -- this in the future.
68 -- bso Mon May 31 19:42:28 PDT 1999
69 --
70 v_sql_statement :=
71 'select order_number from (' ||
72 'select to_number(segment1) order_number from mtl_sales_orders ' ||
73 'where sales_order_id=' || x_value || ' union all '||
74 'select order_number from oe_order_headers '||
75 'where header_id=' || x_value || ')' ||
76 'where rownum = 1';
77 */
78 else
79 v_sql_statement := 'SELECT ' || V_FK_TABLE_SHORT_NAME || '.' ||
80 V_FK_MEANING || ' FROM ' ||
81 V_FK_TABLE_NAME || ' ' || V_FK_TABLE_SHORT_NAME ||
82 ' WHERE ' || x_value || ' = ' ||
83 V_FK_TABLE_SHORT_NAME || '.' || V_PK_ID;
84
85 if V_PK_ID2 is not null then
86 v_sql_statement := v_sql_statement || ' and ' || QLTNINRB.NAME_IN(V_FK_ID2) ||
87 ' = ' || V_FK_TABLE_SHORT_NAME || '.' || V_PK_ID2;
88 if V_PK_ID3 is not null then
89 v_sql_statement := v_sql_statement || ' and ' || QLTNINRB.NAME_IN(V_FK_ID3) ||
90 ' = ' || V_FK_TABLE_SHORT_NAME || '.' || V_PK_ID3;
91 end if;
92 end if;
93 if V_FK_ADD_WHERE is not null then
94 v_sql_statement := v_sql_statement || ' and ' || V_FK_ADD_WHERE;
95 end if;
96
97 -- check for type 3s
98 if (V_FK_LOOKUP_TYPE = 3) then
99 -- This must be a Item Category, its our only three right now
100 -- simply need to add a where clause that includes the cat_set_id profile
101 V_CATEGORY_SET_ID := FND_PROFILE.VALUE('QA_CATEGORY_SET');
102 -- check to see if the profile is set
103 if V_CATEGORY_SET_ID is null then
104 FND_MESSAGE.SET_NAME('QA','QA_PROFILE_NOT_SET');
105 QLTSTORB.KILL_REC_GROUP;
106 APP_EXCEPTION.RAISE_EXCEPTION;
107 end if;
108 -- add the category Set
109 v_sql_statement := v_sql_statement || ' CATEGORY_SET_ID = ' ||
110 V_CATEGORY_SET_ID || ' AND ';
111 end if;
112 end if;
113
114 -- run the statement
115 source_cursor := dbms_sql.open_cursor;
116 dbms_sql.parse(source_cursor, v_sql_statement, dbms_sql.v7);
117 if V_DATATYPE = 1 then
118 dbms_sql.define_column(source_cursor, 1, return_value_char, 1500);
119 elsif V_DATATYPE = 2 then
120 dbms_sql.define_column(source_cursor, 1, return_value_num);
121 else
122 dbms_sql.define_column(source_cursor, 1, return_value_date);
123 end if;
124 ignore := dbms_sql.execute(source_cursor);
125
126 -- now get the value and return it
127
128 -- For Bug2243760. Added Close_cursor statement in
129 -- below three cases.
130
131 if dbms_sql.fetch_rows(source_cursor)>0 then
132 if V_DATATYPE = 1 then
133 dbms_sql.column_value(source_cursor,1, return_value_char);
134 dbms_sql.close_cursor(source_cursor);
135 RETURN(return_value_char);
136 elsif V_DATATYPE = 2 then
137 dbms_sql.column_value(source_cursor,1, return_value_num);
138 dbms_sql.close_cursor(source_cursor);
139 RETURN(to_char(return_value_num));
140 else
141 dbms_sql.column_value(source_cursor,1, return_value_date);
142 dbms_sql.close_cursor(source_cursor);
143 RETURN(to_char(return_value_date));
144 end if;
145 end if;
146
147 END LOOKUP;
148
149 END QLTSMENB;
150
[Home] [Help]