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

Source


1 PACKAGE BODY PO_POXPRCLM_PKG AS
2 /* $Header: POXPRCLMB.pls 120.1 2011/01/03 04:55:30 bhuchand noship $ */
3 
4 FUNCTION AfterPForm return boolean is
5 begin
6 
7 SEGMENT_WHERE :='1=1 ';
8 
9   if (p_req_num_from is not null and p_req_num_to is null) then
10      SEGMENT_WHERE := 'prh.segment1 >= :p_req_num_from ';
11   elsif (p_req_num_to is not null and p_req_num_from is null) then
12      SEGMENT_WHERE := 'prh.segment1 <= :p_req_num_to ';
13   elsif (p_req_num_to is not null and p_req_num_from is not null) then
14      SEGMENT_WHERE := '(prh.segment1 between :p_req_num_from and :p_req_num_to) ';
15   end if;
16 
17   return (TRUE);
18 end;
19 
20 FUNCTION GET_UDA_HEADER_XML( p_req_header_id NUMBER) RETURN XMLType IS
21 CURSOR C_ATTR_GRP IS
22 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP, AG.ATTR_GROUP_ID
23 ATTR_GROUP_ID,
24       AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME, AG.MULTI_ROW
25 MULTI_ROW
26 FROM EGO_FND_DSC_FLX_CTX_EXT AG, po_requisition_headers_all H,
27 PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
28 WHERE H.requisition_header_id = p_req_header_id
29 AND H.UDA_TEMPLATE_ID = POT.TEMPLATE_ID
30 AND POT.TEMPLATE_ID = POTU.TEMPLATE_ID
31 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
32 AND AG.ATTR_GROUP_ID  = POTU.ATTRIBUTE_GROUP_ID
33 and POTU.attribute_group_id in (select distinct attr_group_id from PO_REQ_HEADERS_EXT_B
34                                   where REQUISITION_HEADER_ID = H.requisition_header_id and attr_group_id = POTU.attribute_group_id)
35 
36 ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
37 
38 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2)  IS
39 SELECT   EFDFCE.ATTR_ID                ,
40   EFDFCE.APPLICATION_COLUMN_NAME      ,
41   FCU.END_USER_COLUMN_NAME,
42   fcu.flex_value_set_id,
43      EFDFCE.data_type
44    FROM
45   EGO_FND_DF_COL_USGS_EXT EFDFCE
46        , FND_DESCR_FLEX_COLUMN_USAGES FCU
47   WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
48 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE  = P_ATTR_GROUP
49        AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE =
50 EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
51        AND FCU.DESCRIPTIVE_FLEXFIELD_NAME =
52 EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
53        AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
54        AND FCU.DISPLAY_FLAG <> 'H';
55 
56 L_SQL VARCHAR2(32767);
57 L_OP VARCHAR2(32767);
58 L_ATTR_GRP NUMBER;
59 L_CTR NUMBER;
60 L_O_CTR NUMBER;
61 l_uda_xml XMLTYPE;
62 
63 p_application_id  number := 201;
64   p_attr_internal_str_value varchar2(100);
65 
66   p_attr_internal_name  varchar2(100);
67   p_attr_group_type varchar2(200) :=  'PO_REQ_HEADER_EXT_ATTRS';
68   p_attr_group_int_name varchar2(200);
69 
70   p_object_name varchar2(200) := 'PO_REQ_HEADERS_EXT_B';
71 
72   p_pk1_column_name   varchar2(200) := 'REQUISITION_HEADER_ID';
73   p_pk1_value  number := p_req_header_id;
74 
75 
76 BEGIN
77 
78 L_O_CTR := 0;
79 FOR REC IN C_ATTR_GRP LOOP
80   IF REC.MULTI_ROW = 'Y' THEN
81     IF L_O_CTR > 0 THEN
82       L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
83 || '", XMLAgg(XMLForest(' ;
84     ELSE
85       L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '",
86 XMLAgg(XMLForest(';
87     END IF;
88   ELSE
89     IF L_O_CTR > 0 THEN
90       L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
91 || '", XMLForest(' ;
92     ELSE
93       L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
94     END IF;
95   END IF;
96   L_CTR := 0 ;
97   FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
98       IF L_CTR = 0 THEN
99         L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as ' ||
100 R.END_USER_COLUMN_NAME;
101       ELSE
102         L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as ' ||
103 R.END_USER_COLUMN_NAME;
104       END IF;
105 
106      if r.flex_value_set_id is not null then
107           if (r.data_type = 'C' or r.data_type = 'A' ) then
108             l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
109                          p_application_id || ',' || ' null,  ' ||
110                          R.APPLICATION_COLUMN_NAME || ', null , ''' ||
111                          R.END_USER_COLUMN_NAME || ''',''' ||
112                          p_attr_group_type || ''',''' ||
113                          REC.ATTR_GROUP || ''',' ||
114                          r.attr_id || ',''' ||
115                          p_object_name || ''',''' ||
116                          p_pk1_column_name || ''',' ||
117                          p_pk1_value || ') as ' || R.end_user_column_name || '_DESC' ;
118           elsif  r.data_type = 'N' then
119             l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
120                          p_application_id || ',' || ' null, null, ' ||
121                          R.APPLICATION_COLUMN_NAME || ', ''' ||
122                          R.END_USER_COLUMN_NAME || ''',''' ||
123                          p_attr_group_type || ''',''' ||
124                          REC.ATTR_GROUP || ''',' ||
125                          r.attr_id || ',''' ||
126                          p_object_name || ''',''' ||
127                          p_pk1_column_name || ''',' ||
128                          p_pk1_value || ') as ' || R.end_user_column_name || '_DESC' ;
129           elsif (r.data_type = 'X' or r.data_type = 'Y') then
130              l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
131                          p_application_id || ',' ||  R.APPLICATION_COLUMN_NAME ||
132                          ' , null, null, ''' ||
133                          R.END_USER_COLUMN_NAME || ''',''' ||
134                          p_attr_group_type || ''',''' ||
135                          REC.ATTR_GROUP || ''',' ||
136                          r.attr_id || ',''' ||
137                          p_object_name || ''',''' ||
138                          p_pk1_column_name || ''',' ||
139                          p_pk1_value || ') as ' || R.end_user_column_name || '_DESC' ;
140           end if;
141         end if;
142 
143 
144 
145       L_CTR := L_CTR + 1;
146   END LOOP;
147   IF REC.MULTI_ROW = 'Y' THEN
148     L_SQL := L_SQL || ')))';
149   ELSE
150     L_SQL := L_SQL || ' ))';
151   END IF;
152   L_SQL := L_SQL || ' from PO_REQ_HEADERS_EXT_VL where
153 REQUISITION_HEADER_ID = ' || p_req_header_id || ' and attr_group_id = '
154 || REC.ATTR_GROUP_ID || ')' ;
155 
156   L_O_CTR := L_O_CTR + 1;
157 END LOOP;
158 
159 l_op := ' select XMLConcat(' || l_sql || ') from dual';
160 execute immediate l_op into l_uda_xml;
161 
162 RETURN (l_uda_xml);
163 END;
164 
165 
166 FUNCTION GET_UDA_LINES_XML(p_req_line_id NUMBER) RETURN XMLTYPE IS
167 CURSOR C_ATTR_GRP IS
168 SELECT AG.DESCRIPTIVE_FLEX_CONTEXT_CODE ATTR_GROUP, AG.ATTR_GROUP_ID
169 ATTR_GROUP_ID,
170       AG.DESCRIPTIVE_FLEXFIELD_NAME DESC_FLEXFLD_NAME, AG.MULTI_ROW
171 MULTI_ROW
172 FROM EGO_FND_DSC_FLX_CTX_EXT AG, po_requisition_lines_all L,
173 PO_UDA_AG_TEMPLATES POT, PO_UDA_AG_TEMPLATE_USAGES POTU
174 WHERE L.requisition_line_id = p_req_line_id
175 AND L.UDA_TEMPLATE_ID = POT.TEMPLATE_ID
176 AND POT.TEMPLATE_ID = POTU.TEMPLATE_ID
177 AND AG.DESCRIPTIVE_FLEXFIELD_NAME = POT.ENTITY_CODE
178 AND AG.ATTR_GROUP_ID  = POTU.ATTRIBUTE_GROUP_ID
179 and potu.attribute_group_id in (select distinct attr_group_id from PO_REQ_LINES_EXT_B
180                                   where requisition_line_id=p_req_line_id and  attr_group_id = potu.attribute_group_id)
181 ORDER BY POTU.ATTRIBUTE_GROUP_SEQUENCE;
182 
183 CURSOR C_ATTR_MD(P_ATTR_GROUP VARCHAR2, P_DESC_FLEXFLD_NAME VARCHAR2)  IS
184 SELECT   EFDFCE.ATTR_ID                ,
185   EFDFCE.APPLICATION_COLUMN_NAME      ,
186   FCU.END_USER_COLUMN_NAME ,
187      fcu.flex_value_set_id,
188      EFDFCE.data_type
189 
190    FROM
191   EGO_FND_DF_COL_USGS_EXT EFDFCE
192        , FND_DESCR_FLEX_COLUMN_USAGES FCU
193   WHERE EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME = P_DESC_FLEXFLD_NAME
194 AND EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE  = P_ATTR_GROUP
195        AND FCU.DESCRIPTIVE_FLEX_CONTEXT_CODE =
196 EFDFCE.DESCRIPTIVE_FLEX_CONTEXT_CODE
197        AND FCU.DESCRIPTIVE_FLEXFIELD_NAME =
198 EFDFCE.DESCRIPTIVE_FLEXFIELD_NAME
199        AND FCU.APPLICATION_COLUMN_NAME = EFDFCE.APPLICATION_COLUMN_NAME
200        AND FCU.DISPLAY_FLAG <> 'H';
201 
202 L_SQL VARCHAR2(32767);
203 L_OP VARCHAR2(32767);
204 L_ATTR_GRP NUMBER;
205 L_CTR NUMBER;
206 L_O_CTR NUMBER;
207 l_uda_xml XMLTYPE;
208 
209 p_application_id  number := 201;
210   p_attr_internal_str_value varchar2(100);
211 
212   p_attr_internal_name  varchar2(100);
213   p_attr_group_type varchar2(200) :=  'PO_REQ_LINE_EXT_ATTRS';
214   p_attr_group_int_name varchar2(200);
215 
216   p_object_name varchar2(200) := 'PO_REQ_LINES_EXT_B';
217 
218   p_pk1_column_name   varchar2(200) := 'REQUISITION_LINE_ID';
219   p_pk1_value  number := p_req_line_id;
220 
221 BEGIN
222 
223 L_O_CTR := 0;
224 FOR REC IN C_ATTR_GRP LOOP
225   IF REC.MULTI_ROW = 'Y' THEN
226     IF L_O_CTR > 0 THEN
227       L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
228 || '", XMLAgg(XMLForest(' ;
229     ELSE
230       L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '",
231 XMLAgg(XMLForest(';
232     END IF;
233   ELSE
234     IF L_O_CTR > 0 THEN
235       L_SQL := L_SQL || ',' || '(select XMLElement("' || REC.ATTR_GROUP
236 || '", XMLForest(' ;
237     ELSE
238       L_SQL := '(select XMLElement("' || REC.ATTR_GROUP || '", XMLForest(';
239     END IF;
240   END IF;
241   L_CTR := 0 ;
242   FOR R IN C_ATTR_MD(REC.ATTR_GROUP, REC.DESC_FLEXFLD_NAME) LOOP
243       IF L_CTR = 0 THEN
244         L_SQL := L_SQL || R.APPLICATION_COLUMN_NAME || ' as ' ||
245 R.END_USER_COLUMN_NAME;
246       ELSE
247         L_SQL := L_SQL || ',' || R.APPLICATION_COLUMN_NAME || ' as ' ||
248 R.END_USER_COLUMN_NAME;
249       END IF;
250 
251       if r.flex_value_set_id is not null then
252           if (r.data_type = 'C' or r.data_type = 'A' ) then
253             l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
254                          p_application_id || ',' || ' null,  ' ||
255                          R.APPLICATION_COLUMN_NAME || ', null , ''' ||
256                          R.END_USER_COLUMN_NAME || ''',''' ||
257                          p_attr_group_type || ''',''' ||
258                          REC.ATTR_GROUP || ''',' ||
259                          r.attr_id || ',''' ||
260                          p_object_name || ''',''' ||
261                          p_pk1_column_name || ''',' ||
262                          p_pk1_value || ') as ' || R.end_user_column_name || '_DESC' ;
263           elsif  r.data_type = 'N' then
264             l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
265                          p_application_id || ',' || ' null, null, ' ||
266                          R.APPLICATION_COLUMN_NAME || ', ''' ||
267                          R.END_USER_COLUMN_NAME || ''',''' ||
268                          p_attr_group_type || ''',''' ||
269                          REC.ATTR_GROUP || ''',' ||
270                          r.attr_id || ',''' ||
271                          p_object_name || ''',''' ||
272                          p_pk1_column_name || ''',' ||
273                          p_pk1_value || ') as ' || R.end_user_column_name || '_DESC' ;
274           elsif (r.data_type = 'X' or r.data_type = 'Y') then
275              l_sql := l_sql || ',' || ' EGO_USER_ATTRS_DATA_PVT.Get_Attr_Disp_Val_From_VSet ( ' ||
276                          p_application_id || ',' ||  R.APPLICATION_COLUMN_NAME ||
277                          ' , null, null, ''' ||
278                          R.END_USER_COLUMN_NAME || ''',''' ||
279                          p_attr_group_type || ''',''' ||
280                          REC.ATTR_GROUP || ''',' ||
281                          r.attr_id || ',''' ||
282                          p_object_name || ''',''' ||
283                          p_pk1_column_name || ''',' ||
284                          p_pk1_value || ') as ' || R.end_user_column_name || '_DESC' ;
285           end if;
286         end if;
287 
288 
289       L_CTR := L_CTR + 1;
290   END LOOP;
291   IF REC.MULTI_ROW = 'Y' THEN
292     L_SQL := L_SQL || ')))';
293   ELSE
294     L_SQL := L_SQL || ' ))';
295   END IF;
296   L_SQL := L_SQL || ' from PO_REQ_LINES_EXT_VL where
297 REQUISITION_LINE_ID = '||p_req_line_id||' and attr_group_id = ' || REC.ATTR_GROUP_ID || ')' ;
298 
299   L_O_CTR := L_O_CTR + 1;
300 END LOOP;
301 
302 l_op := ' select XMLConcat(' || l_sql || ') from dual';
303 execute immediate l_op into l_uda_xml;
304 
305 RETURN (l_uda_xml);
306 END;
307 
308 
309 END;
310