[Home] [Help]
PACKAGE BODY: APPS.FWK_PORTLET_GEN_UTIL
Source
1 PACKAGE BODY FWK_PORTLET_GEN_UTIL as
2 /* $Header: fwkportgenutlb.pls 120.4 2011/12/29 20:20:25 mmuhanna ship $ */
3 -----------------------------------------------------------------------------
4 ---------------------------- PUBLIC METHODS ---------------------------------
5 -----------------------------------------------------------------------------
6 /* $Header: fwkportgenutlb.pls 120.4 2011/12/29 20:20:25 mmuhanna ship $ */
7 -- Return back the full Page Metadata.
8 --
9 -- Parameters:
10 -- p_document - the fully qualified document name, which can represent
11 -- either a document or package file.
12 -- (i.e. '/oracle/apps/ak/attributeSets')
13 --
14 FUNCTION getPageMetaData(p_document in VARCHAR2) RETURN CLOB
15 IS
16 p_chunk VARCHAR2(32000) DEFAULT '';
17 full_document CLOB DEFAULT '';
18 BEGIN
19 p_chunk := jdr_mds_internal.exportDocumentAsXML(p_document);
20 full_document := p_chunk;
21 IF p_chunk IS NULL THEN
22 return full_document;
23 ELSE
24 LOOP
25 p_chunk := jdr_mds_internal.exportDocumentAsXML(NULL);
26 EXIT WHEN p_chunk IS NULL;
27 full_document := full_document || p_chunk;
28 END LOOP;
29 END IF;
30 return full_document;
31 END;
32
33
34 -----------------------------------------------------------------------------
35 ---------------------------- PUBLIC METHODS ---------------------------------
36 -----------------------------------------------------------------------------
37
38 -- Return Path Name for specific document ID.
39 --
40 -- Parameters:
41 -- p_docid - the fully qualified document id
42
43 FUNCTION getPathName(p_docid in NUMBER) RETURN VARCHAR
44 IS
45 path_name JDR_PATHS.PATH_NAME%TYPE;
46
47 CURSOR c_pathName(docID JDR_PATHS.PATH_OWNER_DOCID%TYPE) IS
48 SELECT path_name
49 FROM JDR_PATHS
50 WHERE path_owner_docid = 2
51 CONNECT BY PRIOR PATH_OWNER_DOCID = PATH_DOCID
52 START WITH PATH_DOCID = docID;
53
54
55 BEGIN
56
57 path_name := null;
58 for x in c_pathName(p_docid) loop
59 path_name := x.path_name;
60 end loop;
61 return path_name;
62
63 END;
64
65
66 -----------------------------------------------------------------------------
67 ---------------------------- PUBLIC METHODS ---------------------------------
68 -----------------------------------------------------------------------------
69
70 -- This is the concurrent program that will refresh the Materialized view.
71 -- This method is not used anymore.
72 -- Parameters:
73 -- none
74
75 PROCEDURE refresh_mview(errbuf out nocopy varchar2,
76 retcode out nocopy number)
77 IS
78 begin
79
80 fnd_file.put_line(fnd_file.log, 'Refreshing materialized view');
81 fnd_file.put_line(fnd_file.log, 'Start time : ' || to_char(sysdate, 'DD-MON-RR HH24:MI:SS'));
82
83 dbms_mview.refresh('FWK_PORTLET_GEN_MV');
84
85 fnd_file.put_line(fnd_file.log, 'Refresh successful.');
86 fnd_file.put_line(fnd_file.log, 'End time : ' || to_char(sysdate, 'DD-MON-RR HH24:MI:SS'));
87
88
89
90 errbuf := 'Portlet MV Refresh successful.';
91 retcode := 0;
92
93 exception
94 when others then
95 retcode := 2;
96 errbuf := 'ERROR: ' || sqlerrm;
97
98 end;
99
100 -----------------------------------------------------------------------------
101 ---------------------------- PUBLIC METHODS ---------------------------------
102 -----------------------------------------------------------------------------
103
104 -- This is the concurrent program that will refresh the FWK_PORTLET_GEN table.
105 --
106 -- Parameters:
107 -- none
108
109 PROCEDURE refreshPortletTable(errbuf out nocopy varchar2,
110 retcode out nocopy number)
111 IS
112
113 TYPE docIdTAB is TABLE of NUMBER index by binary_integer;
114 TYPE docNameTAB is TABLE of VARCHAR2(4000) index by binary_integer;
115 TYPE regionNameTAB is TABLE of VARCHAR2(60) index by binary_integer;
116 TYPE regionTypeTAB is TABLE of VARCHAR2(40) index by binary_integer;
117 TYPE appNameTAB is TABLE of VARCHAR2(4000) index by binary_integer;
118
119 docId docIdTAB;
120 docName docNameTAB;
121 regionName regionNameTAB;
122 regionType regionTypeTAB;
123 appName appNameTAB;
124
125 BEGIN
126
127 fnd_file.put_line(fnd_file.log, 'Refreshing Portlet Generator Table');
128 fnd_file.put_line(fnd_file.log, 'Start time : ' || to_char(sysdate, 'DD-MON-RR HH24:MI:SS'));
129
130 SELECT x.DOCUMENT_ID,
131 x.DOCUMENT_NAME,
132 x.REGION_NAME,
133 x.REGION_TYPE,
134 x.APP_SHORT_NAME
135 BULK COLLECT INTO docId, docName, regionName, regionType, appName
136 FROM (
137 SELECT COMP.comp_docid AS document_id,
138 jdr_mds_internal.getdocumentname(COMP.comp_docid) AS document_name,
139 PATH.path_name AS region_name,
140 COMP.comp_element AS region_type,
141 FWK_PORTLET_GEN_UTIL.getPathName(COMP.comp_docid)AS app_short_name
142 FROM jdr_components COMP, jdr_attributes ATTR, jdr_paths PATH
143 WHERE COMP.comp_docid = ATTR.att_comp_docid
144 AND PATH.path_docid = COMP.comp_docid
145 AND ATTR.att_name = 'amDefName'
146 AND ATTR.att_value IS NOT NULL
147 AND ATTR.att_comp_seq = 0
148 AND COMP.comp_seq = 0
149 AND COMP.comp_element IN ('oa:advancedTable', 'oa:bulletedList',
150 'oa:defaultDoubleColumn', 'oa:defaultFormStack',
151 'oa:defaultSingleColumn', 'oa:defaultStack', 'oa:flowLayout',
152 'oa:footer', 'oa:gantt', 'oa:graphTable', 'oa:hgrid',
153 'oa:header', 'oa:hideShow', 'oa:hideShowHeader',
154 'labeledFieldLayout', 'oa:messageComponentLayout', 'oa:query',
155 'oa:shuttle', 'oa:stackLayout', 'oa:subTabLayout',
156 'oa:table', 'oa:tableLayout', 'oa:tree')
157 AND COMP.comp_docid
158 IN ( SELECT att_comp_docid FROM jdr_attributes
159 WHERE att_name = 'standalone'
160 AND att_value = 'true'
161 AND att_comp_seq = 0 )
162 UNION ALL
163 SELECT COMP.comp_docid AS document_id,
164 jdr_mds_internal.getdocumentname(COMP.comp_docid)||'.'||COMP.comp_id AS document_name,
165 COMP.comp_id AS region_name,
166 COMP.comp_element AS region_type ,
167 FWK_PORTLET_GEN_UTIL.getPathName(COMP.comp_docid)AS app_short_name
168 FROM jdr_components COMP, jdr_attributes ATTR
169 WHERE COMP.comp_docid = ATTR.att_comp_docid
170 AND ATTR.att_name = 'amDefName'
171 AND attr.att_value IS NOT NULL
172 AND ATTR.att_comp_seq = COMP.comp_seq
173 AND COMP.comp_seq <> 0
174 AND COMP.comp_element IN ('oa:advancedTable', 'oa:bulletedList',
175 'oa:defaultDoubleColumn', 'oa:defaultFormStack',
176 'oa:defaultSingleColumn', 'oa:defaultStack', 'oa:flowLayout',
177 'oa:footer', 'oa:gantt', 'oa:graphTable', 'oa:hgrid',
178 'oa:header', 'oa:hideShow', 'oa:hideShowHeader',
179 'labeledFieldLayout', 'oa:messageComponentLayout', 'oa:query',
180 'oa:shuttle', 'oa:stackLayout', 'oa:subTabLayout',
181 'oa:table', 'oa:tableLayout', 'oa:tree')
182 AND COMP.comp_docid
183 IN ( SELECT DISTINCT att_comp_docid FROM jdr_attributes
184 WHERE att_name = 'standalone'
185 AND att_value = 'true'
186 AND att_comp_seq = COMP.comp_seq )
187 UNION ALL
188 SELECT COMP.comp_docid AS document_id,
189 jdr_mds_internal.getdocumentname(COMP.comp_docid) AS document_name,
190 PATH.path_name AS region_name,
191 COMP.comp_element AS region_type,
192 FWK_PORTLET_GEN_UTIL.getPathName(COMP.comp_docid)AS app_short_name
193 FROM jdr_components COMP, jdr_paths PATH
194 WHERE COMP.comp_element = 'oa:contentContainer'
195 AND PATH.path_docid = COMP.comp_docid
196 AND COMP.COMP_SEQ = 0
197 UNION ALL
198 SELECT COMP.comp_docid AS document_id,
199 jdr_mds_internal.getdocumentname(COMP.comp_docid)||'.'||COMP.comp_id AS document_name,
200 COMP.comp_id AS region_name,
201 COMP.comp_element AS region_type,
202 FWK_PORTLET_GEN_UTIL.getPathName(COMP.comp_docid)AS app_short_name
203 FROM jdr_components COMP
204 WHERE COMP.comp_element = 'oa:contentContainer'
205 AND COMP.COMP_SEQ <> 0 )x;
206
207 FORALL i IN docId.FIRST..docId.LAST
208 INSERT INTO FWK_PORTLET_GEN(DOCUMENT_ID ,
209 DOCUMENT_NAME ,
210 REGION_NAME ,
211 REGION_TYPE ,
212 APP_SHORT_NAME)
213 VALUES( docId(i),
214 docName(i),
215 regionName(i),
216 regionType(i),
217 appName(i));
218
219 fnd_file.put_line(fnd_file.log, 'Refresh successful.');
220 fnd_file.put_line(fnd_file.log, 'End time : ' || to_char(sysdate, 'DD-MON-RR HH24:MI:SS'));
221
222 errbuf := 'Portlet Table Refresh successful.';
223 retcode := 0;
224
225 exception
226 when others then
227 retcode := 2;
228 errbuf := 'ERROR: ' || sqlerrm;
229
230 END;
231
232
233
234
235 END FWK_PORTLET_GEN_UTIL;