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_SBCR_DT_PKG

Source


1 PACKAGE BODY OKC_SBCR_DT_PKG AS
2 /* $Header: OKCSBCRDTB.pls 120.0.12020000.3 2013/03/01 08:06:03 nbingi noship $ */
3 
4 FUNCTION get_uda_attr_desc_sql(p_pk1_value NUMBER,
5                                p_data_type VARCHAR2,
6                                p_appl_col_name VARCHAR2,
7                                p_end_user_col_name VARCHAR2,
8                                p_attr_group VARCHAR2,
9                                p_attr_id NUMBER) RETURN VARCHAR2 IS
10 
11   p_application_id  number := 510;
12   p_attr_group_type varchar2(200);
13   p_object_name varchar2(200);
14   p_pk1_column_name   varchar2(200);
15   l_sql varchar2(1000);
16 
17 BEGIN
18   p_attr_group_type :=  'OKC_REP_CONTRACT_HDR_EXT_ATTRS';
19   p_object_name := 'OKC_REP_CONTRACTS_ALL_EXT_B';
20   p_pk1_column_name := 'CONTRACT_ID';
21 
22   IF (p_data_type = 'C' or p_data_type = 'A' ) then
23     l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
24                  p_application_id || ',' || ' null,  ' ||
25                  p_appl_col_name|| ', null , ''' ||
26                  p_end_user_col_name|| ''',''' ||
27                  p_attr_group_type || ''',''' ||
28                  p_attr_group|| ''',' ||
29                  p_attr_id || ',''' ||
30                  p_object_name || ''',''' ||
31                  p_pk1_column_name || ''',' ||
32                  p_pk1_value || ') as ' || p_end_user_col_name || '_DESC' ;
33   ELSIF p_data_type = 'N' then
34     l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
35                  p_application_id || ',' || ' null, null, ' ||
36                  p_appl_col_name|| ', ''' ||
37                  p_end_user_col_name|| ''',''' ||
38                  p_attr_group_type || ''',''' ||
39                  p_attr_group || ''',' ||
40                  p_attr_id || ',''' ||
41                  p_object_name || ''',''' ||
42                  p_pk1_column_name || ''',' ||
43                  p_pk1_value || ') as ' || p_end_user_col_name || '_DESC' ;
44   ELSIF (p_data_type = 'X' or p_data_type = 'Y') then
45     l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
46                  p_application_id || ',' || p_appl_col_name ||
47                  ' , null, null, ''' ||
48                  p_end_user_col_name|| ''',''' ||
49                  p_attr_group_type || ''',''' ||
50                  p_attr_group || ''',' ||
51                  p_attr_id || ',''' ||
52                  p_object_name || ''',''' ||
53                  p_pk1_column_name || ''',' ||
54                  p_pk1_value || ') as ' || p_end_user_col_name || '_DESC ' ;
55   END IF;
56 
57   RETURN(l_sql);
58 END get_uda_attr_desc_sql;
59 
60 FUNCTION get_uda_single_attr_xml(p_contract_id NUMBER) RETURN XMLType IS
61 
62   CURSOR C_ATTR_GRP IS
63   SELECT ag.descriptive_flex_context_code attr_group,
64          ag.attr_group_id attr_group_id,
65          ag.descriptive_flexfield_name desc_flexfld_name
66   FROM ego_fnd_dsc_flx_ctx_ext ag
67   WHERE ag.attr_group_id IN (SELECT DISTINCT ext.attr_group_id FROM okc_rep_contracts_all_ext_b ext WHERE ext.contract_id = p_contract_id)
68         AND ag.multi_row = 'N';
69 
70   CURSOR C_ATTR(p_attr_group VARCHAR2, p_desc_flexfld_name VARCHAR2) IS
71   SELECT efdfce.attr_id,
72          efdfce.application_column_name,
73          fcu.end_user_column_name,
74          fcu.flex_value_set_id,
75          efdfce.data_type
76   FROM 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_ctr NUMBER;
88   l_uda_xml XMLTYPE;
89 
90 BEGIN
91 
92   FOR rec IN C_ATTR_GRP LOOP
93     IF l_sql IS NOT NULL THEN
94       l_sql := l_sql || ',' || '(SELECT XMLELEMENT("' || rec.attr_group || '", XMLFOREST(' ;
95     ELSE
96       l_sql := '(SELECT XMLELEMENT("' || rec.attr_group || '", XMLFOREST(';
97     END if;
98 
99     l_ctr := 0 ;
100     FOR attr IN C_ATTR(rec.attr_group, rec.desc_flexfld_name) LOOP
101       IF l_ctr = 0 then
102         l_sql := l_sql || attr.application_column_name || ' as ' || attr.end_user_column_name;
103       ELSE
104         l_sql := l_sql || ',' || attr.application_column_name || ' as ' || attr.end_user_column_name;
105       END if;
106 
107       IF attr.flex_value_set_id IS NOT NULL THEN
108         l_sql := l_sql || ',' || get_uda_attr_desc_sql(p_contract_id,
109                                                        attr.data_type,
110                                                        attr.application_column_name,
111                                                        attr.end_user_column_name,
112                                                        rec.attr_group,
113                                                        attr.attr_id );
114       END if;
115       l_ctr := l_ctr + 1;
116     END LOOP;
117 
118     l_sql := l_sql || ' ))';
119     l_sql := l_sql || ' FROM okc_rep_contracts_all_ext_vl WHERE contract_id = ' || p_contract_id || ' and attr_group_id = ' || rec.attr_group_id || ')' ;
120   END LOOP;
121 
122   l_op := 'SELECT XMLCONCAT(' || l_sql || ') FROM dual';
123 
124   EXECUTE IMMEDIATE l_op INTO l_uda_xml;
125   RETURN (l_uda_xml);
126 
127 END get_uda_single_attr_xml;
128 
129 FUNCTION get_setaside_recommendations(p_contract_id NUMBER) RETURN XMLTYPE IS
130 
131 l_rc SYS_REFCURSOR;
132 l_rec_type_col_name VARCHAR2(30);
133 l_rec_percent_col_name VARCHAR2(30);
134 l_rec_type VARCHAR2(150);
135 l_is_rec VARCHAR2(1);
136 l_rec_percent NUMBER;
137 l_attr_group_id NUMBER;
138 l_sql VARCHAR2(32567);
139 l_xml_sql VARCHAR2(32567);
140 l_final_sql VARCHAR2(32567);
141 l_xml XMLTYPE;
142 
143 BEGIN
144 
145   SELECT fcu.application_column_name INTO l_rec_type_col_name
146   FROM fnd_descr_flex_column_usages fcu
147   WHERE fcu.descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
148         AND fcu.descriptive_flex_context_code = 'SBCR_REC_AG'
149         AND fcu.end_user_column_name = 'RECOMMENDATION_TYPE';
150 
151   SELECT fcu.application_column_name INTO l_rec_percent_col_name
152   FROM fnd_descr_flex_column_usages fcu
153   WHERE fcu.descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
154         AND fcu.descriptive_flex_context_code = 'SBCR_REC_AG'
155         AND fcu.end_user_column_name = 'RECOMMEND_PERCENT';
156 
157   SELECT attr_group_id INTO l_attr_group_id
158   FROM ego_fnd_dsc_flx_ctx_ext
159   WHERE descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
160         AND descriptive_flex_context_code = 'SBCR_REC_AG';
161 
162   l_sql := 'SELECT lkup.lookup_code, nvl2(ext.' || l_rec_type_col_name || ', ''Y'', ''N''), ext.' || l_rec_percent_col_name || ' FROM okc_rep_contracts_all_ext_b ext, fnd_lookups lkup' ||
163            ' WHERE ext.contract_id(+) = ' || p_contract_id || ' AND ext.attr_group_id (+) = ' || l_attr_group_id ||
164            ' AND lkup.lookup_type = ''OKC_REP_SBCR_REC_SET_ASIDE'' AND lkup.enabled_flag = ''Y''' ||
165            ' AND TRUNC(SYSDATE) BETWEEN lkup.start_date_active AND nvl(lkup.end_date_active, SYSDATE)' ||
166            ' AND lkup.lookup_code = ext.' || l_rec_type_col_name || '(+)';
167 
168   OPEN l_rc FOR l_sql;
169   LOOP
170 	  FETCH l_rc INTO l_rec_type, l_is_rec, l_rec_percent ;
171 	  EXIT WHEN l_rc%NOTFOUND;
172       IF l_xml_sql IS NULL THEN
173         l_xml_sql := 'XMLForest(''' || l_is_rec || ''' as ' || l_rec_type  || '_TYPE, ''' || l_rec_percent || ''' as ' || l_rec_type || '_PER)';
174       ELSE
175         l_xml_sql := l_xml_sql || ', XMLForest(''' || l_is_rec || ''' as ' || l_rec_type  || '_TYPE, ''' || l_rec_percent || ''' as ' || l_rec_type || '_PER)';
176       END IF;
177   END LOOP;
178 
179   l_final_sql := ' SELECT XMLConcat(' || l_xml_sql || ') FROM dual';
180   EXECUTE IMMEDIATE l_final_sql INTO l_xml;
181   RETURN (l_xml);
182 
183 EXCEPTION
184   WHEN No_Data_Found THEN
185     RETURN l_xml;
186   WHEN OTHERS THEN
187     RAISE;
188 END get_setaside_recommendations;
189 
190 FUNCTION get_prev_acqs_history(p_contract_id NUMBER) RETURN XMLTYPE IS
191 
192 l_rc SYS_REFCURSOR;
193 l_prev_acq_type_col_name VARCHAR2(30);
194 l_prev_acq_cmt_col_name VARCHAR2(30);
195 l_prev_acq_type VARCHAR2(150);
196 l_prev_acq_cmt VARCHAR2(150);
197 l_attr_group_id NUMBER;
198 l_sql VARCHAR2(32567);
199 l_xml_sql VARCHAR2(32567);
200 l_final_sql VARCHAR2(32567);
201 l_xml XMLTYPE;
202 
203 BEGIN
204 
205   SELECT fcu.application_column_name INTO l_prev_acq_type_col_name
206   FROM fnd_descr_flex_column_usages fcu
207   WHERE fcu.descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
208         AND fcu.descriptive_flex_context_code = 'SBCR_PRE_ACQ'
209         AND fcu.end_user_column_name = 'ACQ_PREV';
210 
211   SELECT fcu.application_column_name INTO l_prev_acq_cmt_col_name
212   FROM fnd_descr_flex_column_usages fcu
213   WHERE fcu.descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
214         AND fcu.descriptive_flex_context_code = 'SBCR_PRE_ACQ'
215         AND fcu.end_user_column_name = 'ACQ_COMMENTS';
216 
217   SELECT attr_group_id INTO l_attr_group_id
218   FROM ego_fnd_dsc_flx_ctx_ext
219   WHERE descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
220         AND descriptive_flex_context_code = 'SBCR_PRE_ACQ';
221 
222   l_sql := 'SELECT ' || l_prev_acq_type_col_name || ', ' || l_prev_acq_cmt_col_name ||
223            ' FROM okc_rep_contracts_all_ext_b' ||
224            ' WHERE contract_id = ' || p_contract_id ||
225            ' AND attr_group_id = ' || l_attr_group_id ||
226            ' AND ' || l_prev_acq_type_col_name || ' IS NOT NULL';
227 
228   OPEN l_rc FOR l_sql;
229   LOOP
230 	  FETCH l_rc INTO l_prev_acq_type, l_prev_acq_cmt ;
231 	  EXIT WHEN l_rc%NOTFOUND;
232       IF l_xml_sql IS NULL THEN
233         l_xml_sql := 'XMLForest(''Y'' as ' || l_prev_acq_type  || '_TYPE, ''' || l_prev_acq_cmt || ''' as ' || l_prev_acq_type || '_CMT)';
234       ELSE
235         l_xml_sql := l_xml_sql || ', XMLForest(''Y'' as ' || l_prev_acq_type  || '_TYPE, ''' || l_prev_acq_cmt || ''' as ' || l_prev_acq_type || '_CMT)';
236       END IF;
237   END LOOP;
238 
239   l_final_sql := ' SELECT XMLConcat(' || l_xml_sql || ') FROM dual';
240   EXECUTE IMMEDIATE l_final_sql INTO l_xml;
241   RETURN (l_xml);
242 EXCEPTION
243   WHEN No_Data_Found THEN
244     RETURN l_xml;
245   WHEN OTHERS THEN
246     RAISE;
247 END get_prev_acqs_history;
248 
249 FUNCTION get_sbss_employees_size(p_contract_id NUMBER) RETURN VARCHAR2 IS
250 
251 l_sbcr_size_standard_col_name VARCHAR2(30);
252 l_sbcr_size_standard VARCHAR2(150);
253 l_sbcr_size_standard_desc VARCHAR2(32567) := NULL;
254 l_attr_group_id NUMBER;
255 l_attr_id NUMBER;
256 l_attr_data_type VARCHAR2(30);
257 l_sql VARCHAR2(1000);
258 l_sql_desc VARCHAR2(1000);
259 
260 BEGIN
261 
262   SELECT fcu.application_column_name, efdfce.attr_id, efdfce.data_type
263   INTO l_sbcr_size_standard_col_name, l_attr_id, l_attr_data_type
264   FROM fnd_descr_flex_column_usages fcu,
265        ego_fnd_df_col_usgs_ext efdfce
266   WHERE fcu.descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
267         AND fcu.descriptive_flex_context_code = 'SBCR_PROC_INFO_AG'
268         AND fcu.end_user_column_name = 'SBCR_SBSS_SIZE_STANDARD'
269         AND efdfce.descriptive_flexfield_name = fcu.descriptive_flexfield_name
270         AND efdfce.descriptive_flex_context_code = fcu.descriptive_flex_context_code
271         AND efdfce.application_column_name = fcu.application_column_name;
272 
273   SELECT attr_group_id INTO l_attr_group_id
274   FROM ego_fnd_dsc_flx_ctx_ext
275   WHERE descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
276         AND descriptive_flex_context_code = 'SBCR_PROC_INFO_AG';
277 
278   l_sql := 'SELECT ' || l_sbcr_size_standard_col_name ||
279            ' FROM okc_rep_contracts_all_ext_b' ||
280            ' WHERE contract_id = ' || p_contract_id ||
281            ' AND attr_group_id = ' || l_attr_group_id ||
282            ' AND ' || l_sbcr_size_standard_col_name || ' like ''%EMP%''';
283 
284   EXECUTE IMMEDIATE l_sql INTO l_sbcr_size_standard;
285 
286   l_sql_desc := get_uda_attr_desc_sql(p_contract_id,
287                                       l_attr_data_type,
288                                       l_sbcr_size_standard_col_name,   --application_column_name
289                                       'SBCR_SBSS_SIZE_STANDARD',       --end_user_column_name
290                                       'SBCR_PROC_INFO_AG',             --attr_group_name
291                                       l_attr_id );
292 
293   l_sql_desc := 'SELECT ' || l_sql_desc ||
294                 ' FROM okc_rep_contracts_all_ext_b' ||
295 	           ' WHERE contract_id = ' || p_contract_id ||
296 	           ' AND attr_group_id = ' || l_attr_group_id;
297 
298   EXECUTE IMMEDIATE l_sql_desc INTO l_sbcr_size_standard_desc;
299 
300   RETURN (l_sbcr_size_standard_desc);
301 EXCEPTION
302   WHEN No_Data_Found THEN
303     RETURN l_sbcr_size_standard_desc;
304   WHEN OTHERS THEN
305     RAISE;
306 END get_sbss_employees_size;
307 
308 FUNCTION get_sbss_dollars_size(p_contract_id NUMBER) RETURN VARCHAR2 IS
309 
310 l_sbcr_size_standard_col_name VARCHAR2(30);
311 l_sbcr_size_standard VARCHAR2(150);
312 l_sbcr_size_standard_desc VARCHAR2(32567) := NULL;
313 l_attr_group_id NUMBER;
314 l_attr_id NUMBER;
315 l_attr_data_type VARCHAR2(30);
316 l_sql VARCHAR2(1000);
317 l_sql_desc VARCHAR2(1000);
318 
319 BEGIN
320 
321   SELECT fcu.application_column_name, efdfce.attr_id, efdfce.data_type
322   INTO l_sbcr_size_standard_col_name, l_attr_id, l_attr_data_type
323   FROM fnd_descr_flex_column_usages fcu,
324        ego_fnd_df_col_usgs_ext efdfce
325   WHERE fcu.descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
326         AND fcu.descriptive_flex_context_code = 'SBCR_PROC_INFO_AG'
327         AND fcu.end_user_column_name = 'SBCR_SBSS_SIZE_STANDARD'
328         AND efdfce.descriptive_flexfield_name = fcu.descriptive_flexfield_name
329         AND efdfce.descriptive_flex_context_code = fcu.descriptive_flex_context_code
330         AND efdfce.application_column_name = fcu.application_column_name;
331 
332   SELECT attr_group_id INTO l_attr_group_id
333   FROM ego_fnd_dsc_flx_ctx_ext
334   WHERE descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
335         AND descriptive_flex_context_code = 'SBCR_PROC_INFO_AG';
336 
337   l_sql := 'SELECT ' || l_sbcr_size_standard_col_name ||
338            ' FROM okc_rep_contracts_all_ext_b' ||
339            ' WHERE contract_id = ' || p_contract_id ||
340            ' AND attr_group_id = ' || l_attr_group_id ||
341            ' AND ' || l_sbcr_size_standard_col_name || ' like ''%$%''';
342 
343   EXECUTE IMMEDIATE l_sql INTO l_sbcr_size_standard;
344 
345   l_sql_desc := get_uda_attr_desc_sql(p_contract_id,
346                                       l_attr_data_type,
347                                       l_sbcr_size_standard_col_name,   --application_column_name
348                                       'SBCR_SBSS_SIZE_STANDARD',       --end_user_column_name
349                                       'SBCR_PROC_INFO_AG',             --attr_group_name
350                                       l_attr_id );
351 
352   l_sql_desc := 'SELECT ' || l_sql_desc ||
353                 ' FROM okc_rep_contracts_all_ext_b' ||
354 	           ' WHERE contract_id = ' || p_contract_id ||
355 	           ' AND attr_group_id = ' || l_attr_group_id;
356 
357   EXECUTE IMMEDIATE l_sql_desc INTO l_sbcr_size_standard_desc;
358 
359   RETURN (l_sbcr_size_standard_desc);
360 EXCEPTION
361   WHEN No_Data_Found THEN
362     RETURN l_sbcr_size_standard_desc;
363   WHEN OTHERS THEN
364     RAISE;
365 END get_sbss_dollars_size;
366 
367 FUNCTION is_sec8A_setaside_recommended(p_contract_id NUMBER) RETURN VARCHAR2 IS
368 
369 l_rec_type_col_name VARCHAR2(30);
370 l_attr_group_id NUMBER;
371 l_sql VARCHAR2(1000);
372 sec8A_recommended VARCHAR2(1) := 'N';
373 
374 BEGIN
375 
376   SELECT fcu.application_column_name INTO l_rec_type_col_name
377   FROM fnd_descr_flex_column_usages fcu
378   WHERE fcu.descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
379         AND fcu.descriptive_flex_context_code = 'SBCR_REC_AG'
380         AND fcu.end_user_column_name = 'RECOMMENDATION_TYPE';
381 
382   SELECT attr_group_id INTO l_attr_group_id
383   FROM ego_fnd_dsc_flx_ctx_ext
384   WHERE descriptive_flexfield_name = 'OKC_REP_CONTRACT_HDR_EXT_ATTRS'
385         AND descriptive_flex_context_code = 'SBCR_REC_AG';
386 
387   l_sql := 'SELECT ''Y''' ||
388            ' FROM okc_rep_contracts_all_ext_b' ||
389            ' WHERE contract_id = ' || p_contract_id ||
390            ' AND attr_group_id = ' || l_attr_group_id ||
391            ' AND ' || l_rec_type_col_name || ' like ''SEC8A%''';
392 
393   EXECUTE IMMEDIATE l_sql INTO sec8A_recommended;
394 
395   RETURN (sec8A_recommended);
396 EXCEPTION
397   WHEN No_Data_Found THEN
398     RETURN sec8A_recommended;
399   WHEN OTHERS THEN
400     RAISE;
401 END is_sec8A_setaside_recommended;
402 
403 END OKC_SBCR_DT_PKG;