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.ICX_DATATEMPLATE_PKG

Source


4 FUNCTION AfterPForm RETURN Boolean AS
1 PACKAGE BODY ICX_DATATEMPLATE_PKG AS
2 /* $Header: ICX_DATATEMPLATE_PKG.plb 120.4.12020000.3 2013/04/18 12:38:26 rparise noship $ */
3 
5   l_return Boolean;
6 BEGIN
7   RETURN (TRUE);
8 END AfterPForm;
9 
10 FUNCTION get_uda_attr_desc_sql(p_doc_type varchar2, p_pk1_value number,
11                                 p_data_type varchar2,
12                                 p_appl_col_name varchar2,
13                                 p_end_user_col_name varchar2,
14                                 p_attr_group varchar2, p_attr_id number)
15                                 return varchar2 is
16 
17   p_application_id  number := 201;
18   p_attr_group_type varchar2(200);
19   p_object_name varchar2(200);
20   p_pk1_column_name   varchar2(200);
21   l_sql varchar2(1000);
22   begin
23   if p_doc_type = 'LINES' then
24     p_attr_group_type :=  'PO_REQ_LINE_EXT_ATTRS';
25     p_object_name := 'PO_REQ_LINES_EXT_B';
26     p_pk1_column_name := 'REQUISITION_LINE_ID';
27   elsif p_doc_type = 'HEADER' then
28     p_attr_group_type :=   'PO_REQ_HEADER_EXT_ATTRS';
29     p_object_name := 'PO_REQ_HEADERS_EXT_B';
30     p_pk1_column_name := 'REQUISITION_HEADER_ID';
31   end if;
32 
33   if (p_data_type = 'C' or p_data_type = 'A' ) then
34     l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
35                  p_application_id || ',' || ' null,  ' ||
36                  p_appl_col_name|| ', null , ''' ||
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 = 'N' then
45     l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
46                  p_application_id || ',' || ' null, null, ' ||
47                  p_appl_col_name|| ', ''' ||
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   elsif (p_data_type = 'X' or p_data_type = 'Y') then
56      l_sql := ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
57                  p_application_id || ',' || p_appl_col_name ||
58                  ' , null, null, ''' ||
59                  p_end_user_col_name|| ''',''' ||
60                  p_attr_group_type || ''',''' ||
61                  p_attr_group || ''',' ||
62                  p_attr_id || ',''' ||
63                  p_object_name || ''',''' ||
67   return(l_sql);
64                  p_pk1_column_name || ''',' ||
65                  p_pk1_value || ') as ' || p_end_user_col_name || '_DESC ' ;
66   end if;
68   end get_uda_attr_desc_sql;
69 
70 
71 FUNCTION GET_UDA_HEADER_XML RETURN XMLType IS
72   CURSOR C_ATTR_GRP IS
73     SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP,
74           AG.ATTR_GROUP_ID ATTR_GROUP_ID,
75           AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME,
76           AG.MULTI_ROW MULTI_ROW
77     FROM EGO_FND_DSC_FLX_CTX_EXT AG,
78          PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
79     WHERE POT.TEMPLATE_ID = POTU.TEMPLATE_ID
80     AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
81     AND AG.ATTR_GROUP_ID  = POTU.ATTRIBUTE_GROUP_ID
82     AND potu.attribute_group_id in (select distinct attr_group_id
83                                     from po_req_headers_ext_b
84                                     where requisition_header_id = reqHeaderId
85                                     and uda_template_id = potu.template_id
86                                     and attr_group_id = potu.attribute_group_id)
87     ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
88 
89   CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2)  IS
90     SELECT   EFDFCE.ATTR_ID                ,
91     EFDFCE.APPLICATION_COLUMN_NAME      ,
92     FCU.END_USER_COLUMN_NAME,
93     fcu.flex_value_set_id,
94     EFDFCE.data_type
95     FROM
96       EGO_FND_DF_COL_USGS_EXT EFDFCE,
97       FND_DESCR_FLEX_COLUMN_USAGES FCU
98     WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
99     AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE  = P_ATTR_GROUP
100     AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE = EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
101     AND FCU.DESCRIPTIVE_FLEXFIELD_NAME = EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
102     AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
103     AND FCU.DISPLAY_FLAG <> 'H';
104 
105   L_SQL VARCHAR2(32767);
106   L_OP VARCHAR2(32767);
107   L_ATTR_GRP NUMBER;
108   L_CTR NUMBER;
109   L_O_CTR NUMBER;
110   l_uda_xml XMLTYPE;
111   BEGIN
112 
113   L_O_CTR := 0;
114   FOR REC IN C_ATTR_GRP LOOP
115     IF REC.MULTI_ROW = 'Y' THEN
116       IF L_O_CTR > 0 THEN
117         L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
118                  || '", XMLAgg(XMLForest(' ;
119       ELSE
120         L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLAgg(XMLForest(';
121       END IF;
122     ELSE
123       IF L_O_CTR > 0 THEN
124         L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
125                   || '", XMLForest(' ;
126       ELSE
127         L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
128       END IF;
129     END IF;
130     L_CTR := 0 ;
131     FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
132         IF L_CTR = 0 THEN
133           L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as ' || R.END_USER_COLUMN_NAME;
134         ELSE
135           L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as ' || R.END_USER_COLUMN_NAME;
136         END IF;
137         if r.flex_value_set_id is not null then
138           if (r.data_type = 'C' or r.data_type = 'A' ) then
139               l_sql := l_sql || ',' || get_uda_attr_desc_sql('HEADER',
140                                      reqHeaderId,
141                                      r.data_type,
142                                      r.application_column_name,
143                                      r.end_user_column_name,
144                                      rec.attr_group,
145                                      r.ATTR_ID );
146           end if;
147         end if;
148         L_CTR := L_CTR + 1;
149     END LOOP;
150     IF REC.MULTI_ROW = 'Y' THEN
151       L_SQL := L_SQL || ')))';
152     ELSE
153       L_SQL := L_SQL || ' ))';
154     END IF;
155     L_SQL := L_SQL || ' from po_req_headers_ext_vl where requisition_header_id = ' ||
156              reqHeaderId ||
157              ' and attr_group_id = ' || REC.ATTR_GROUP_ID || ')' ;
158     L_O_CTR := L_O_CTR + 1;
159   END LOOP;
160 
161   l_op := ' select XMLConcat(' || l_sql || ') from dual';
162   execute immediate l_op into l_uda_xml;
163   RETURN (l_uda_xml);
164 END GET_UDA_HEADER_XML;
165 
166 FUNCTION GET_ADDRESSXML RETURN CLOB AS
167   l_where_sql varchar2(1000);
168   l_addr_sql varchar2(1000);
169   l_sql varchar2(4000);
170   l_col_name varchar2(50);
171   l_attr_grp_id number;
172   clob_addr CLOB;
173   c_addr SYS_REFCURSOR;
174   l_address varchar2(4000);
175 begin
176   l_where_sql := ' from po_req_headers_ext_vl where requisition_header_id = :1 ';
177 
178   begin
179     SELECT 	ag.attr_group_id,
180             efdfce.application_column_name
181     into   l_attr_grp_id, l_col_name
182     FROM ego_fnd_dsc_flx_ctx_ext ag,
183          ego_fnd_df_col_usgs_ext efdfce,
184          fnd_descr_flex_column_usages fcu
185     WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
186     and efdfce.descriptive_flex_context_code  = ag.descriptive_flex_context_code
187     and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
188     and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
189     and fcu.application_column_name = efdfce.application_column_name
190     and  ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_REQ_HEADER_EXT_ATTRS'
191     and upper(ag.descriptive_flex_context_code) = upper('addresses')
192     and upper(fcu.end_user_column_name) = upper('addressdtlsxml');
193 
194     l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
195 
196     l_addr_sql := 'select ' || l_col_name || ' as address ' ;
197 
198   exception
199   when no_data_found then
203   if l_addr_sql is not null then
200     l_addr_sql := '';
201   end;
202 
204     l_sql := l_addr_sql || l_where_sql;
205 
206     dbms_lob.createtemporary(clob_addr, true);
207     dbms_lob.open(clob_addr, dbms_lob.lob_readwrite);
208     open c_addr for l_sql using reqHeaderId, l_attr_grp_id;
209     loop
210       fetch c_addr into l_address;
211       exit when c_addr%notfound;
212       if l_address is not null then
213       dbms_lob.writeappend(clob_addr, length(l_address), l_address);
214       end if;
215     end loop;
216     dbms_lob.close(clob_addr);
217   end if;
218   return(clob_addr);
219 END GET_ADDRESSXML;
220 
221 
222 FUNCTION GET_CONTACTSXML RETURN CLOB AS
223   l_cnt_sql varchar2(1000);
224   l_where_sql varchar2(1000);
225   l_sql varchar2(4000);
226   l_cnt varchar2(4000);
227   l_col_name varchar2(50);
228   l_attr_grp_id number;
229   c_cnt SYS_REFCURSOR;
230   clob_cnt CLOB;
231 begin
232   l_where_sql := ' from po_req_headers_ext_vl where requisition_header_id = :1 ';
233 
234   begin
235     SELECT 	ag.attr_group_id,
236             efdfce.application_column_name
237     into   l_attr_grp_id, l_col_name
238     FROM ego_fnd_dsc_flx_ctx_ext ag,
239          ego_fnd_df_col_usgs_ext efdfce,
240          fnd_descr_flex_column_usages fcu
241     WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
242     and efdfce.descriptive_flex_context_code  = ag.descriptive_flex_context_code
243     and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
244     and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
245     and fcu.application_column_name = efdfce.application_column_name
246     and  ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_REQ_HEADER_EXT_ATTRS'
247     and upper(ag.descriptive_flex_context_code) = upper('addresses')
248     and upper(fcu.end_user_column_name) = upper('contactdtlsxml');
249 
250     l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
251 
252     l_cnt_sql := 'select ' || l_col_name || ' as contacts ' ;
253 
254   exception
255   when no_data_found then
256     l_cnt_sql := '';
257   end;
258 
259   if l_cnt_sql is not null then
260     l_sql := l_cnt_sql || l_where_sql;
261 
262     dbms_lob.createtemporary(clob_cnt, true);
263     dbms_lob.open(clob_cnt , dbms_lob.lob_readwrite);
264     open c_cnt for l_sql using reqHeaderId, l_attr_grp_id;
265     loop
266       fetch c_cnt into l_cnt;
267       exit when c_cnt%notfound;
268       if l_cnt is not null then
269       dbms_lob.writeappend(clob_cnt, length(l_cnt), l_cnt);
270       end if;
271     end loop;
272     dbms_lob.close(clob_cnt);
273   end if;
274   return(clob_cnt);
275 END GET_CONTACTSXML;
276 
277 FUNCTION GET_AUTH_OFFICER_XML RETURN CLOB AS
278   l_where_sql varchar2(1000);
279   l_auth_sql varchar2(1000);
280   l_auth_sql_1 varchar2(4000);
281   l_sql varchar2(4000);
282   l_col_name varchar2(50);
283   l_attr_grp_id number;
284   clob_auth CLOB;
285   clob_auth_1 CLOB;
286   c_auth SYS_REFCURSOR;
287   l_auth varchar2(4000);
288   c_auth_1 SYS_REFCURSOR;
289 l_auth_id number;
290 
291 begin
292   l_where_sql := ' from po_req_headers_ext_vl where requisition_header_id = :1 ';
293 
294   begin
295     SELECT 	ag.attr_group_id,
296             efdfce.application_column_name
297     into   l_attr_grp_id, l_col_name
298     FROM ego_fnd_dsc_flx_ctx_ext ag,
299          ego_fnd_df_col_usgs_ext efdfce,
300          fnd_descr_flex_column_usages fcu
301     WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
302     and efdfce.descriptive_flex_context_code  = ag.descriptive_flex_context_code
303     and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
304     and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
305     and fcu.application_column_name = efdfce.application_column_name
306     and  ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_REQ_HEADER_EXT_ATTRS'
307     and upper(ag.descriptive_flex_context_code) = upper('MIPR_ACC_INFO')
308     and upper(fcu.end_user_column_name) IN ('AUTH_OFFICER') ;
309 
310     l_where_sql := l_where_sql || ' and attr_group_id = :3 ' ;
311 
312     l_auth_sql := 'select ' || l_col_name || ' as AUTH_OFFICER_ID ' ;
313 
314   exception
315   when no_data_found then
316     l_auth_sql := '';
317   end;
318 
319   if l_auth_sql is not null then
320     l_sql := l_auth_sql || l_where_sql;
321     open c_auth for l_sql using reqHeaderId, l_attr_grp_id;
322     fetch c_auth into l_auth_id;
323     close c_auth;
324 
325     l_auth_sql_1 := 'select nvl(hr_general.decode_position_latest_name (pa.position_id),jbt.name)
326 from per_all_assignments_f pa, per_jobs_tl jbt
327 where jbt.job_id(+) =pa.job_id and  jbt.language=userenv(''LANG'')
328 and sysdate between nvl(pa.effective_start_date,sysdate) and  nvl(pa.effective_end_date,sysdate)
329 and pa.person_id =:1';
330 
331   dbms_lob.createtemporary(clob_auth_1, true);
332     dbms_lob.open(clob_auth_1, dbms_lob.lob_readwrite);
333     open c_auth_1 for l_auth_sql_1 using l_auth_id;
334     loop
335       fetch c_auth_1 into l_auth;
336       exit when c_auth_1%notfound;
337       if l_auth is not null then
338       dbms_lob.writeappend(clob_auth_1, length(l_auth), l_auth);
339       end if;
340     end loop;
341     dbms_lob.close(clob_auth_1);
342   end if;
343 
344 
345   return(clob_auth_1);
346 END GET_AUTH_OFFICER_XML;
347 
348 FUNCTION GET_APPROPRIATION(CHARGE_ACCOUNT IN VARCHAR2) RETURN VARCHAR2 AS
349 BEGIN
350   -- Code need to be written for getting Appropriation from Charge account
354 FUNCTION GET_LIMITSH(CHARGE_ACCOUNT IN VARCHAR2) RETURN VARCHAR2 AS
351   return(CHARGE_ACCOUNT);
352 END GET_APPROPRIATION;
353 
355 BEGIN
356   -- Code need to be written for getting Appropriation from Charge account
357   return(CHARGE_ACCOUNT);
358 END GET_LIMITSH;
359 
360 FUNCTION GET_ADDRESS_FROM_LOCATION (p_location_id IN NUMBER)
361 RETURN VARCHAR2 IS
362 
363 l_address_line_1       VARCHAR2(240);
364 l_address_line_2       VARCHAR2(240);
365 l_address_line_3       VARCHAR2(240);
366 l_town_or_city         VARCHAR2(80);
367 l_state_or_province    VARCHAR2(80);
368 l_postal_code          VARCHAR2(80);
369 l_territory_short_name VARCHAR2(80);
370 l_iso_territory_code   VARCHAR2(80);
371 address_string         VARCHAR2(1000):= '';
372 
373 BEGIN
374 
375   PO_HR_LOCATION.get_address (
376               p_location_id          => p_location_id
377             , x_address_line_1       => l_address_line_1
378             , x_address_line_2       => l_address_line_2
379             , x_address_line_3       => l_address_line_3
380             , x_town_or_city         => l_town_or_city
381             , x_state_or_province    => l_state_or_province
382             , x_postal_code          => l_postal_code
383             , x_territory_short_name => l_territory_short_name
384             , x_iso_territory_code   => l_iso_territory_code );
385 
386 
387   select  decode(l_address_line_1,null,'',l_address_line_1||' ')||
388           decode(l_address_line_2,null,'',l_address_line_2||' ')||
389           decode(l_address_line_3,null,'',l_address_line_3||' ')||
390           decode(l_town_or_city,null,'',l_town_or_city||',')||
391           decode(l_state_or_province,null,'',l_state_or_province||',')||
392           decode(l_territory_short_name, null,'',l_territory_short_name||',')||
393           l_postal_code
394   into address_string
395   from dual;
396 
397   RETURN address_string;
398 
399 END GET_ADDRESS_FROM_LOCATION;
400 
401 FUNCTION GET_FUNDSDATAXML RETURN CLOB AS
402 
403 CURSOR c_funds_info IS
404 SELECT  ag.attr_group_id as attr_grp_id,
405 	 	efdfce.application_column_name as appl_col_name,
406 	 	fcu.end_user_column_name as end_user_col_name
407 FROM ego_fnd_dsc_flx_ctx_ext ag,
408      ego_fnd_df_col_usgs_ext efdfce,
409      fnd_descr_flex_column_usages fcu
410 WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
411 and efdfce.descriptive_flex_context_code  = ag.descriptive_flex_context_code
412 and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
413 and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
414 and fcu.application_column_name = efdfce.application_column_name
415 and  ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_REQ_HEADER_EXT_ATTRS'
416 and upper(ag.descriptive_flex_context_code) = upper('MIPR_ACC_INFO')
417 and upper(fcu.end_user_column_name) IN ('FUNDS_INFO', 'AMOUNT') ;
418 
419   l_where_sql varchar2(1000);
420   l_auth_sql varchar2(1000) := '';
421   l_sql varchar2(4000);
422   l_attr_grp_id number;
423   l_amount number;
424   l_op VARCHAR2(1000);
425   l_xml_sql varchar2(500) := '';
426   l_uda_xml XMLTYPE;
427   cnt NUMBER;
428   c_auth SYS_REFCURSOR;
429   l_funds_info VARCHAR2(20);
430 
431 begin
432   cnt := 0;
433   for rec in c_funds_info loop
434   	if cnt = 0 then
435   		l_auth_sql :=  'select ' || rec.appl_col_name || ' as ' || rec.end_user_col_name;
436   	else
437   		l_auth_sql :=  l_auth_sql || ' ,' || rec.appl_col_name || ' as ' || rec.end_user_col_name;
438   	end if;
439   	l_attr_grp_id := rec.attr_grp_id;
440   	cnt := cnt + 1;
441   end loop;
442 
443   l_where_sql := ' from po_req_headers_ext_vl where requisition_header_id = :1 and attr_group_id = :2 ' ;
444 
445   if l_auth_sql is not null then
446     l_sql := l_auth_sql || l_where_sql;
447     open c_auth for l_sql using reqHeaderId, l_attr_grp_id;
448     fetch c_auth into l_funds_info, l_amount;
449     close c_auth;
450 
451     if l_funds_info = 'REQUIRED' then
452 	l_xml_sql := 'XMLELEMENT(ADDITIONAL_FUNDS_REQD_FLAG, ''Y''), XMLELEMENT(ADDITIONAL_FUNDS_REQD_AMOUNT,' || l_amount || ')';
453     elsif l_funds_info = 'WITHDRAW' then
454 	l_xml_sql := 'XMLELEMENT(WITHDRAW_FUNDS_FLAG, ''Y''), XMLELEMENT(WITHDRAW_FUNDS_AMOUNT,' || l_amount || ')';
455     else
456       l_xml_sql := 'XMLELEMENT(ADDITIONAL_FUNDS_REQD_FLAG, ''N''), XMLELEMENT(ADDITIONAL_FUNDS_REQD_AMOUNT, ' || 0.00 || '),' ||
457                     ' XMLELEMENT(WITHDRAW_FUNDS_FLAG, ''N''), XMLELEMENT(WITHDRAW_FUNDS_AMOUNT, ' || 0.00 || ')';
458     end if;
459 
460   end if;
461 
462   l_op := ' select XMLConcat( ' || l_xml_sql || ') from dual';
463   execute immediate l_op into l_uda_xml;
464 
465   RETURN l_uda_xml.GETCLOBVAL();
466 END GET_FUNDSDATAXML;
467 
468 FUNCTION GET_MIPR_ACC_ATTRIBUTES RETURN CLOB AS
469 
470   cursor c_obligation is
471   SELECT count(*), clm_mipr_obligation_type
472   FROM po_requisition_lines_all
473   WHERE requisition_header_id = reqHeaderId
474   GROUP BY clm_mipr_obligation_type
475   HAVING clm_mipr_obligation_type is not null;
476 
477   l_reimbursement_flag varchar2(1) := 'N';
478   l_direct_citation_flag varchar2(1) := 'N';
479   l_both_flag varchar2(1):= 'N';
480   l_acc_xml XMLTYPE;
481   l_count number;
482   l_obligation_type varchar2(20);
483   l_sql VARCHAR2(1000);
484 
485 begin
486   open c_obligation;
487 
488   loop
489   	fetch c_obligation into l_count, l_obligation_type;
490   	exit when c_obligation%NOTFOUND;
491   	if l_obligation_type = 'REIMBURSEMENT' then
492   		if l_direct_citation_flag = 'N' then
493   			l_reimbursement_flag := 'Y';
494   		else
495   			l_both_flag := 'Y';
499 		if l_reimbursement_flag = 'N' then
496 		end if;
497 	end if;
498 	if l_obligation_type = 'DIRECT_CITE' then
500 			l_direct_citation_flag := 'Y';
501 		else
502 			l_both_flag := 'Y';
503 		end if;
504 	end if;
505   end loop;
506 
507   l_sql := 'SELECT XMLCONCAT(XMLELEMENT(REIMBURSEMENT_FLAG, '''
508    					|| l_reimbursement_flag || '''),
509   					XMLELEMENT(DIRECT_CITATION_FLAG, '''
510   					|| l_direct_citation_flag||'''), XMLELEMENT(OBLG_BOTH_FLAG,'''
511   					|| l_both_flag ||''')) FROM DUAL';
512 
513   EXECUTE IMMEDIATE l_sql INTO l_acc_xml;
514 
515   return l_acc_xml.getclobval();
516 END GET_MIPR_ACC_ATTRIBUTES;
517 
518 PROCEDURE GET_TEMPLATES_FOR_DD448(x_facePageTemplate OUT NOCOPY VARCHAR2,
519                                   x_contPagesTemplate OUT NOCOPY VARCHAR2,
520                                   x_datadef OUT NOCOPY VARCHAR2) IS
521 BEGIN
522   -- Code need to be written for getting PDF template code for DD448 report.
523   x_facePageTemplate := 'ICXMIPRDD448';
524   x_contPagesTemplate := 'ICXDD448SUB';
525 
526   SELECT data_source_code INTO x_datadef
527   FROM XDO_TEMPLATES_VL
528   WHERE template_code = 'ICXMIPRDD448';
529 
530 END GET_TEMPLATES_FOR_DD448;
531 
532 PROCEDURE GET_TEMPLATES_FOR_DD448_2(x_facePageTemplate OUT NOCOPY VARCHAR2,
533                                     x_contPagesTemplate OUT NOCOPY VARCHAR2,
534                                     x_datadef OUT NOCOPY VARCHAR2) IS
535 BEGIN
536   -- Code need to be written for getting PDF template code for DD448-2 report.
537   x_facePageTemplate := 'ICXMIPRDD4482';
538   x_contPagesTemplate := 'ICXDD4482SUB';
539 
540   SELECT data_source_code INTO x_datadef
541   FROM XDO_TEMPLATES_VL
542   WHERE template_code = 'ICXMIPRDD4482';
543 
544 END GET_TEMPLATES_FOR_DD448_2;
545 
546 FUNCTION GET_ADDRESS_FROM_UDA_ATTR(P_REQ_HEADER_ID NUMBER,
547                                     P_ADDRESS_TYPE IN VARCHAR2,
548                                     P_COL_NAME IN VARCHAR2) RETURN VARCHAR2 IS
549 
550 l_sql VARCHAR2(4000);
551 l_address varchar2(1000);
552 c_addr SYS_REFCURSOR;
553 l_columns VARCHAR2(50);
554 l_col_names varchar2(50);
555 l_attr_group_id number;
556 l_addresstype VARCHAR2(25);
557 l_address_det VARCHAR2(25);
558 l_address_type VARCHAR2(50);
559 
560 BEGIN
561   BEGIN
562 
563     SELECT 	ag.attr_group_id,
564           LISTAGG (efdfce.application_column_name, ',')
565           WITHIN GROUP
566           (ORDER BY efdfce.application_column_name) cols
567     INTO l_attr_group_id, l_col_names
568     FROM ego_fnd_dsc_flx_ctx_ext ag,
569           ego_fnd_df_col_usgs_ext efdfce,
570           fnd_descr_flex_column_usages fcu
571     WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
572     and efdfce.descriptive_flex_context_code  = ag.descriptive_flex_context_code
573     and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
574     and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
575     and fcu.application_column_name = efdfce.application_column_name
576     and  ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_REQ_HEADER_EXT_ATTRS'
577     and upper(ag.descriptive_flex_context_code) = upper('addresses')
578     AND upper(fcu.end_user_column_name) IN (upper(p_col_name), upper('addresstype'))
579     GROUP BY ag.attr_group_id;
580 
581     SELECT substr(l_col_names, 1, InStr(l_col_names, ',')-1), substr(l_col_names, InStr(l_col_names, ',')+1) INTO  l_addresstype, l_address_det FROM dual;
582 
583     l_sql := 'select ' || l_col_names || ' from po_req_headers_ext_vl where requisition_header_id = :1  and attr_group_id = :2 and ' ||
584               l_addresstype || ' = :3';
585 
586   EXCEPTION
587   WHEN NO_DATA_FOUND THEN
588     l_sql := '';
589   END;
590 
591   if l_sql is not null then
592     open c_addr for l_sql using p_req_header_id, l_attr_group_id, p_address_type;
593     LOOP
594       fetch c_addr into l_address_type, l_address;
595       exit when c_addr%notfound;
596     end loop;
597   end if;
598 
599   RETURN (l_address);
600 
601 END GET_ADDRESS_FROM_UDA_ATTR;
602 
603 FUNCTION GET_MIPR_ACCEPTANCE_STATUS(P_REQ_HEADER_ID IN NUMBER) RETURN VARCHAR2 AS
604 
605   l_where_sql varchar2(1000);
606   l_acc_sql varchar2(1000) := '';
607   l_sql varchar2(4000);
608   l_attr_grp_id number;
609   l_col_name varchar2(50);
610   l_acceptance_status VARCHAR2(15);
611   c_accept SYS_REFCURSOR;
612 
613 BEGIN
614 
615   SELECT  ag.attr_group_id,
616 	 	      efdfce.application_column_name
617   INTO    l_attr_grp_id, l_col_name
618   FROM ego_fnd_dsc_flx_ctx_ext ag,
619       ego_fnd_df_col_usgs_ext efdfce,
620       fnd_descr_flex_column_usages fcu
621   WHERE efdfce.descriptive_flexfield_name = ag.descriptive_flexfield_name
622   and efdfce.descriptive_flex_context_code  = ag.descriptive_flex_context_code
623   and fcu.descriptive_flex_context_code = efdfce.descriptive_flex_context_code
624   and fcu.descriptive_flexfield_name = efdfce.descriptive_flexfield_name
625   and fcu.application_column_name = efdfce.application_column_name
626   and  ag.DESCRIPTIVE_FLEXFIELD_NAME = 'PO_REQ_HEADER_EXT_ATTRS'
627   and upper(ag.descriptive_flex_context_code) = upper('MIPR_ACC_INFO')
628   and upper(fcu.end_user_column_name) = 'MIPR_ACC_STAT';
629 
630   l_acc_sql := 'select ' || l_col_name || ' as CLM_MIPR_ACCEPTANCE_STATUS ' ;
631   l_where_sql := ' from po_req_headers_ext_vl where requisition_header_id = :1 and attr_group_id = :2 ' ;
632 
633   if l_acc_sql is not null then
634     l_sql := l_acc_sql || l_where_sql;
635     open c_accept for l_sql using p_req_header_id, l_attr_grp_id;
636     fetch c_accept into l_acceptance_status;
637     close c_accept;
638   end if;
639 
640   RETURN l_acceptance_status;
641 END GET_MIPR_ACCEPTANCE_STATUS;
642 
643 END;