eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.
PACKAGE BODY: APPS.OKC_CDRL_DT_PKG

Source


1 PACKAGE BODY OKC_CDRL_DT_PKG AS
2 /* $Header: OKCCDRLDTB.pls 120.0.12020000.2 2013/04/12 06:41:53 nbingi noship $ */
3 
4     function get_uda_attr_desc_sql(p_delId number,
5                                 p_data_type varchar2,
6                                 p_appl_col_name varchar2,
7                                 p_end_user_col_name varchar2,
8                                 p_attr_group_type varchar2,
9                                 p_attr_group varchar2,
10                                 p_attr_id number)
11                                 return varchar2 is
12 
13   p_application_id  number := 510;
14   p_object_name varchar2(200) := 'OKC_DELIVERABLE';
15   p_pk1_column_name   varchar2(200) := 'DELIVERABLE_ID';
16   l_sql varchar2(1000);
17   begin
18 
19   if (p_data_type = 'C' or p_data_type = 'A' ) then
20     l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
21                  p_application_id || ',' || ' null,  ' ||
22                  p_appl_col_name|| ', null , ''' ||
23                  p_end_user_col_name|| ''',''' ||
24                  p_attr_group_type || ''',''' ||
25                  P_ATTR_GROUP|| ''',' ||
26                  p_attr_id || ',''' ||
27                  p_object_name || ''',''' ||
28                  p_pk1_column_name || ''',' ||
29                  p_delID || ') as ' || p_end_user_col_name || '_DESC' ;
30   elsif  p_data_type = 'N' then
31     l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
32                  p_application_id || ',' || ' null, null, ' ||
33                  p_appl_col_name|| ', ''' ||
34                  p_end_user_col_name|| ''',''' ||
35                  p_attr_group_type || ''',''' ||
36                  p_attr_group || ''',' ||
37                  p_attr_id || ',''' ||
38                  p_object_name || ''',''' ||
39                  p_pk1_column_name || ''',' ||
40                  p_delID || ') as ' || p_end_user_col_name || '_DESC' ;
41   elsif (p_data_type = 'X' or p_data_type = 'Y') then
42      l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
43                  p_application_id || ',' || p_appl_col_name ||
44                  ' , null, null, ''' ||
45                  p_end_user_col_name|| ''',''' ||
46                  p_attr_group_type || ''',''' ||
47                  p_attr_group || ''',' ||
48                  p_attr_id || ',''' ||
49                  p_object_name || ''',''' ||
50                  p_pk1_column_name || ''',' ||
51                  p_delID || ') as ' || p_end_user_col_name || '_DESC ' ;
52   end if;
53   return(l_sql);
54   end get_uda_attr_desc_sql;
55 
56   FUNCTION GET_UDA_XML(p_delId number, p_uda_template_id number)
57   RETURN XMLType IS
58   CURSOR C_ATTR_GRP IS
59     SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP,
60           AG.ATTR_GROUP_ID ATTR_GROUP_ID,
61           AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME,
62           AG.MULTI_ROW MULTI_ROW
63     FROM EGO_FND_DSC_FLX_CTX_EXT AG,
64          okc_uda_deliverable_usages usg
65     WHERE usg.uda_template_id = p_uda_template_id
66     and usg.attr_group_id = ag.attr_group_id;
67 
68   CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2)  IS
69     SELECT   EFDFCE.ATTR_ID                ,
70     EFDFCE.APPLICATION_COLUMN_NAME      ,
71     FCU.END_USER_COLUMN_NAME,
72     fcu.flex_value_set_id,
73     EFDFCE.data_type,
74     EFDFCE.descriptive_flexfield_name attr_group_type
75     FROM
76       EGO_FND_DF_COL_USGS_EXT EFDFCE,
77       FND_DESCR_FLEX_COLUMN_USAGES FCU
78     WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
79     AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE  = P_ATTR_GROUP
80     AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
81     AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
82     AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
83     AND FCU.DISPLAY_FLAG <> 'H';
84 
85   L_SQL VARCHAR2(32767);
86   L_OP VARCHAR2(32767);
87   L_ATTR_GRP NUMBER;
88   L_CTR NUMBER;
89   L_O_CTR NUMBER;
90   l_uda_xml XMLTYPE;
91   BEGIN
92 
93   L_O_CTR := 0;
94   FOR REC IN C_ATTR_GRP LOOP
95     IF REC.MULTI_ROW = 'Y' THEN
96       IF L_O_CTR > 0 THEN
97         L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
98                  || '", XMLAgg(XMLForest(' ;
99       ELSE
100         L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLAgg(XMLForest(';
101       END IF;
102     ELSE
103       IF L_O_CTR > 0 THEN
104         L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
105                   || '", XMLForest(' ;
106       ELSE
107         L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
108       END IF;
109     END IF;
110     L_CTR := 0 ;
111 
112     FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
113         IF L_CTR = 0 THEN
114           L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as ' || R.END_USER_COLUMN_NAME;
115         ELSE
116           L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as ' || R.END_USER_COLUMN_NAME;
117         END IF;
118         if r.flex_value_set_id is not null then
119           if (r.data_type = 'C' or r.data_type = 'A' ) then
120               l_sql := l_sql || ',' || get_uda_attr_desc_sql(
121                                      p_delId,
122                                      r.data_type,
123                                      r.application_column_name,
124                                      r.end_user_column_name,
125                                      r.attr_group_type,
126                                      rec.attr_group,
127                                      r.ATTR_ID );
128           end if;
129         end if;
130         L_CTR := L_CTR + 1;
131     END LOOP;
132     IF REC.MULTI_ROW = 'Y' THEN
133       L_SQL := L_SQL || ')))';
134     ELSE
135       L_SQL := L_SQL || ' ))';
136     END IF;
137     L_SQL := L_SQL || ' from okc_deliverables_ext_b where deliverable_id = ' ||
138              p_delId || ' and attr_group_id = ' || REC.ATTR_GROUP_ID || ')' ;
139     L_O_CTR := L_O_CTR + 1;
140   END LOOP;
141 
142   l_op := ' select XMLConcat(' || l_sql || ') from dual';
143   execute immediate l_op into l_uda_xml;
144 
145   RETURN (l_uda_xml);
146   END GET_UDA_XML;
147 
148   FUNCTION GET_ADDR_UDA_XML(p_delId number, p_uda_template_id number)
149   RETURN XMLType is
150   cursor c_attr is
151 	select attr_name, database_column
152 	from ego_attrs_v
153 	where attr_group_name = 'CDRL_DIST_ADDR';
154 
155   cursor c_del is
156 	select extension_id , rownum num
157 	from okc_deliverables_ext_b where attr_group_id in (
158 	select  attr_group_id from EGO_FND_DSC_FLX_CTX_EXT
159 	where descriptive_flexfield_name = 'OKC_DELIVERABLES_EXT_ATTRS'
160 	and descriptive_flex_context_code = 'CDRL_DIST_ADDR')
161 	and deliverable_id = p_delId
162 	and rownum < 39;
163 
164   type tbl_var is table of varchar2(50) index by pls_integer;
165   t_attr tbl_var;
166   t_dbcol tbl_var;
167   l_ctr number := 0;
168   l_delctr number :=0;
169   l_sql CLOB;
170   l_addr_xml XMLType;
171 
172   begin
173 	open c_attr;
174 	 fetch c_attr bulk collect into t_attr, t_dbcol;
175 	close c_attr;
176 
177 	if t_attr.count > 0 then
178   	   l_sql := 'select XMLconcat( ' ;
179 
180 	   for rec in c_del loop
181 	  	l_ctr := 0;
182 	  	if l_delctr = 0 then
183 	    		l_sql := l_sql || '(select XMLConcat(';
184 	  	else
185 	    		l_sql := l_sql || ', (select XMLConcat(';
186 	  	end if;
187 
188 	  	for i in t_attr.first..t_attr.last loop
189 	    		l_sql := l_sql || 'XMLElement("' || t_attr(i) || '_'
190 	    				|| rec.num || '",' || t_dbcol(i) || ')' ;
191 	    		if l_ctr < t_attr.count-1 then
192 		 		l_sql := l_sql || ', ';
193 	    		end if;
194 	    		l_ctr := l_ctr + 1;
195 	  	end loop;
196 
197 	  	l_sql := l_sql || ') from okc_deliverables_ext_b where extension_id = '
198 					|| rec.extension_id || ')' ;
199 	  	l_delctr := l_delctr + 1;
200 	    end loop;
201 
202 		l_sql := l_sql || ') from dual' ;
203 		execute immediate l_sql into l_addr_xml;
204 	end if;
205 
206 	return(l_addr_xml);
207   end GET_ADDR_UDA_XML;
208 
209 FUNCTION GET_SINGLE_ATTR_VALUE(p_attr_group VARCHAR2, p_attr_name VARCHAR2,
210                               p_delID NUMBER) RETURN VARCHAR2 IS
211 
212 l_result VARCHAR2(4000) := '';
213 l_res_code VARCHAR2(2000);
214 l_sql         VARCHAR2(4000);
215 l_db_col        VARCHAR2(40);
216 l_attr_group_id NUMBER;
217 l_application_id  number := 510;
218 l_object_name varchar2(200) := 'OKC_DELIVERABLE';
219 l_pk1_column_name   varchar2(200) := 'DELIVERABLE_ID';
220 l_data_type varchar2(40);
221 l_attr_id number;
222 BEGIN
223 	SELECT attr.database_column , grp.attr_group_id, attr.data_type_code, attr.attr_id
224 	INTO l_db_col, l_attr_group_id, l_data_type, l_attr_id
225 	FROM ego_attrs_v attr, ego_attr_groups_v grp
226 	WHERE attr.attr_group_name = p_attr_group
227 	AND attr.attr_group_type     = 'OKC_DELIVERABLES_EXT_ATTRS'
228 	AND attr.attr_name           = p_attr_name
229 	AND attr.attr_group_type     = grp.attr_group_type
230 	AND attr.attr_group_name     = grp.attr_group_name;
231 
232 	l_sql := 'select ' || l_db_col
233 		|| ' from okc_deliverables_ext_b where deliverable_id = :1 and attr_group_id = :2';
234 
235 	EXECUTE immediate l_sql INTO l_res_code USING p_delId, l_attr_group_id ;
236 
237 
238 	if (l_data_type = 'C' or l_data_type = 'A') then
239 		l_result := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
240 					p_application_id => l_application_id,
241 					p_attr_internal_date_value => null,
242 					p_attr_internal_str_value => l_res_code,
243 					p_attr_internal_num_value => null,
244 					p_attr_internal_name => p_attr_name,
245 					p_attr_group_type => 'OKC_DELIVERABLES_EXT_ATTRS',
246 					p_attr_group_int_name => p_attr_group,
247 					p_attr_id => l_attr_id,
248 					p_object_name => l_object_name,
249 					p_pk1_column_name => l_pk1_column_name,
250 					p_pk1_value => p_delId
251 					);
252 	elsif (l_data_type = 'N') then
253 		l_result := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
254 					p_application_id => l_application_id,
255 					p_attr_internal_date_value => null,
256 					p_attr_internal_str_value => null,
257 					p_attr_internal_num_value => l_res_code,
258 					p_attr_internal_name => p_attr_name,
259 					p_attr_group_type => 'OKC_DELIVERABLES_EXT_ATTRS',
260 					p_attr_group_int_name => p_attr_group,
261 					p_attr_id => l_attr_id,
262 					p_object_name => l_object_name,
263 					p_pk1_column_name => l_pk1_column_name,
264 					p_pk1_value => p_delId
265 					);
266 	elsif (l_data_type = 'X' or l_data_type ='Y') then
267 		l_result := EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet (
268 					p_application_id => l_application_id,
269 					p_attr_internal_date_value => l_res_code,
270 					p_attr_internal_str_value => null,
271 					p_attr_internal_num_value => null,
272 					p_attr_internal_name => p_attr_name,
273 					p_attr_group_type => 'OKC_DELIVERABLES_EXT_ATTRS',
274 					p_attr_group_int_name => p_attr_group,
275 					p_attr_id => l_attr_id,
276 					p_object_name => l_object_name,
277 					p_pk1_column_name => l_pk1_column_name,
278 					p_pk1_value => p_delId
279 					);
280 	end if;
281 
282 	RETURN(l_result);
283 
284 END GET_SINGLE_ATTR_VALUE;
285 
286 FUNCTION GET_CDRL_CAT_OTH(p_delID number) return varchar2
287 IS
288 l_cdrl_cat varchar2(200);
289 BEGIN
290 
291 	l_cdrl_cat := GET_SINGLE_ATTR_VALUE('CDRL_DETAILS', 'CDRL_CATEGORY',
292 				p_delID);
293 
294 	if ((l_cdrl_cat = 'TDP') or (l_cdrl_cat = 'TM')) then
295 		return null;
296 	else
297 		return l_cdrl_cat;
298 	end if;
299 END GET_CDRL_CAT_OTH;
300 END;