[Home] [Help]
PACKAGE BODY: APPS.POA_REPORT_UTIL
Source
1 PACKAGE BODY POA_REPORT_UTIL AS
2 /* $Header: poarutlb.pls 115.12 2003/01/23 18:59:15 rvickrey ship $ */
3
4 PROCEDURE Build_OrderDates(
5 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
6 p_index IN NUMBER) IS
7 BEGIN
8 p_param(p_index).Label :=
9 FND_MESSAGE.get_string('PO', 'POA_ORDER_DATES');
10 p_param(p_index).Value := htf.formText('P_FDATE', NULL, NULL,
11 to_char(add_months(trunc(sysdate), -12) + 1, icx_sec.g_date_format));
12 p_param(p_index).Value := p_param(p_index).Value || ' - ' ||
13 htf.formText('P_TDATE', NULL, NULL, to_char(trunc(sysdate),
14 icx_sec.g_date_format));
15 END Build_OrderDates;
16
17 PROCEDURE Build_ReportingDates(
18 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
19 p_index IN NUMBER) IS
20 BEGIN
21 p_param(p_index).Label :=
22 FND_MESSAGE.get_string('PO', 'POA_REPORTING_DATES');
23 p_param(p_index).Value := htf.formText('P_FDATE', NULL, NULL,
24 to_char(add_months(trunc(sysdate), -12) + 1, icx_sec.g_date_format));
25 p_param(p_index).Value := p_param(p_index).Value || ' - ' ||
26 htf.formText('P_TDATE', NULL, NULL, to_char(trunc(sysdate),
27 icx_sec.g_date_format));
28 END Build_ReportingDates;
29
30 PROCEDURE Build_SupplierItem(
31 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
32 p_index IN NUMBER) IS
33 BEGIN
34 p_param(p_index).Label :=
35 FND_MESSAGE.get_string('PO', 'POA_ITEM');
36 p_param(p_index).Value := htf.formText('POA_BIS_ITEM_NAME');
37 p_param(p_index).Action :=
38 '<A HREF="javascript:LOV(''201'', ''POA_BIS_ITEM_NAME'', ''201'',
39 ''POA_BIS_SUPPERF_RPT'',''RPTPFORM'','''','''','''')">
40 <IMG SRC="/OA_MEDIA/FNDILOV.gif" ALIGN="ABSMIDDLE" BORDER=0 ALT
41 ="List of Values"></A>';
42 p_param(p_index).Action := p_param(p_index).Action ||
43 htf.formHidden('POA_BIS_ITEM_ID');
44 END Build_SupplierItem;
45
46 PROCEDURE Build_PrefSupplier(
47 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
48 p_index IN NUMBER) IS
49 BEGIN
50 p_param(p_index).Label :=
51 FND_MESSAGE.get_string('PO', 'POA_PREF_SUPPLIER');
52 p_param(p_index).Value := htf.formText('POA_BIS_PREF_SUPP_NAME');
53 p_param(p_index).Action :=
54 '<A HREF="javascript:LOV(''201'', ''POA_BIS_PREF_SUPP_NAME'', ''201'',
55 ''POA_BIS_SUPPERF_RPT'',''RPTPFORM'','''','''','''')">
56 <IMG SRC="/OA_MEDIA/FNDILOV.gif" ALIGN="ABSMIDDLE" BORDER=0 ALT
57 ="List of Values"></A>';
58 p_param(p_index).Action := p_param(p_index).Action ||
59 htf.formHidden('POA_BIS_PREF_SUPP_ID');
60 END Build_PrefSupplier;
61
62 PROCEDURE Build_ConsSupplier(
63 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
64 p_index IN NUMBER) IS
65 BEGIN
66 p_param(p_index).Label :=
67 FND_MESSAGE.get_string('PO', 'POA_CONS_SUPPLIER');
68 p_param(p_index).Value := htf.formText('POA_BIS_CONS_SUPP_NAME');
69 p_param(p_index).Action :=
70 '<A HREF="javascript:LOV(''201'', ''POA_BIS_CONS_SUPP_NAME'', ''201'',
71 ''POA_BIS_SUPPERF_RPT'',''RPTPFORM'','''','''','''')">
72 <IMG SRC="/OA_MEDIA/FNDILOV.gif" ALIGN="ABSMIDDLE" BORDER=0 ALT
73 ="List of Values"></A>';
74 p_param(p_index).Action := p_param(p_index).Action ||
75 htf.formHidden('POA_BIS_CONS_SUPP_ID');
76 END Build_ConsSupplier;
77
78 PROCEDURE Build_HiddenStartDate(p_start_date IN VARCHAR2,
79 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
80 p_index IN NUMBER) IS
81 BEGIN
82 -- p_param(p_index).Label := 'Reporting Start Date';
83 p_param(p_index).Action := p_param(p_index).Action ||
84 htf.formHidden('P_FDATE', p_start_date);
85 END Build_HiddenStartDate;
86
87 PROCEDURE Build_HiddenEndDate(p_end_date IN VARCHAR2,
88 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
89 p_index IN NUMBER) IS
90 BEGIN
91 -- p_param(p_index).Label := 'Reporting End Date';
92 p_param(p_index).Action := p_param(p_index).Action ||
93 htf.formHidden('P_TDATE', p_end_date);
94 END Build_HiddenEndDate;
95
96 PROCEDURE Build_HiddenCurrency(p_currency_code IN VARCHAR2,
97 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
98 p_index IN NUMBER) IS
99 BEGIN
100 -- p_param(p_index).Label := 'Currency';
101 p_param(p_index).Action := p_param(p_index).Action ||
102 htf.formHidden('POA_BIS_CURRENCY', p_currency_code);
103 END Build_HiddenCurrency;
104
105 PROCEDURE Build_HiddenItem(p_item_id IN VARCHAR2,
106 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
107 p_index IN NUMBER) IS
108 BEGIN
109 -- p_param(p_index).Label := 'Item';
110 p_param(p_index).Action := p_param(p_index).Action ||
111 htf.formHidden('POA_BIS_ITEM_ID', p_item_id);
112 END Build_HiddenItem;
113
114 PROCEDURE Build_SupplierNum(
115 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
116 p_index IN NUMBER) IS
117 BEGIN
118 p_param(p_index).Label :=
119 FND_MESSAGE.get_string('PO', 'POA_SUPPLIER_NUM');
120 p_param(p_index).Value := htf.formText('POA_BIS_SUPPLIER_NUM', NULL, NULL,
121 to_char(3));
122 END Build_SupplierNum;
123
124 PROCEDURE Build_QualityCost(
125 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
126 p_index IN NUMBER) IS
127 BEGIN
128 p_param(p_index).Label :=
129 FND_MESSAGE.get_string('PO', 'POA_QUALITY_COST');
130 p_param(p_index).Value := htf.formText('POA_BIS_QUALITY_COST', NULL, NULL,
131 to_char(25));
132 END Build_QualityCost;
133
134 PROCEDURE Build_DeliveryCost(
135 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
136 p_index IN NUMBER) IS
137 BEGIN
138 p_param(p_index).Label :=
139 FND_MESSAGE.get_string('PO', 'POA_DELIVERY_COST');
140 p_param(p_index).Value := htf.formText('POA_BIS_DELIVERY_COST', NULL, NULL,
141 to_char(25));
142 END Build_DeliveryCost;
143
144 PROCEDURE Build_SupplierOrderBy(
145 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
146 p_index IN NUMBER) IS
147 BEGIN
148 p_param(p_index).Label :=
149 FND_MESSAGE.get_string('PO', 'POA_ORDER_BY');
150 Build_Selection('POA_BIS_MEASURE',
151 'SELECT poc.lookup_code, poc.displayed_field
152 FROM po_lookup_codes poc
153 WHERE poc.lookup_type = ''POA BIS REPORT OPTION''
154 AND poc.lookup_code in (''DEFECTS'', ''EXCEPTIONS'', ''PRICE'', ''AMOUNT'', ''VOLUME'')
155 ORDER BY poc.displayed_field', p_param(p_index).Value);
156
157 p_param(p_index).Value := p_param(p_index).Value || ' ' ;
158
159 Build_Selection('POA_BIS_SORT_CRITERIA',
160 'SELECT poc.lookup_code, poc.displayed_field
161 FROM po_lookup_codes poc
162 WHERE poc.lookup_type = ''POA BIS REPORT OPTION''
163 AND poc.lookup_code in (''HIGHEST'', ''LOWEST'')
164 ORDER BY poc.displayed_field', p_param(p_index).Action);
165
166
167 END Build_SupplierOrderBy;
168
169 PROCEDURE Build_SavingsOperatingUnit(
170 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
171 p_index IN NUMBER) IS
172 l_where_clause VARCHAR2(1000);
173 l_return_status VARCHAR2(1000);
174 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
175 l_region_code VARCHAR2(30);
176 BEGIN
177
178 IF nvl(fnd_profile.value('POA_GLOBAL_SECURITY'), 'N') = 'N' THEN
179 -- Use region pointing to BIS lov restricted based on responsibilities
180 l_region_code := 'POA_BIS_SAVINGS_RPT';
181 BIS_UTILITIES_PUB.Retrieve_Where_Clause
182 (p_user_id => FND_GLOBAL.user_id,
183 p_region_code => 'BIS_OPERATING_UNIT',
184 x_where_clause => l_where_clause,
185 x_return_status => l_return_status,
186 x_error_Tbl => l_error_tbl);
187 ELSE
188 -- Use region pointing to unrestricted POA lov
189 l_region_code := 'POA_BIS_SAVINGS_RPT_G';
190 l_where_clause := NULL;
191 END IF;
192
193 p_param(p_index).Label :=
194 FND_MESSAGE.get_string('PO', 'POA_OPERATING_UNIT');
195 p_param(p_index).Value := htf.formText('POA_BIS_OPER_UNIT_NAME');
196 p_param(p_index).Action :=
197 '<A HREF="javascript:LOV(''201'', ''POA_BIS_OPER_UNIT_NAME'', ''201'',''' ||
198 l_region_code || ''',''RPTPFORM'','''', '''', '''||l_where_clause ||''')">
199 <IMG SRC="/OA_MEDIA/FNDILOV.gif" ALIGN="ABSMIDDLE" BORDER=0 ALT
200 ="List of Values"></A>';
201 p_param(p_index).Action := p_param(p_index).Action ||
202 htf.formHidden('POA_BIS_OPER_UNIT_ID');
203 END Build_SavingsOperatingUnit;
204
205 PROCEDURE Build_PPS_OperatingUnit(
206 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
207 p_index IN NUMBER) IS
208 l_where_clause VARCHAR2(1000);
209 l_return_status VARCHAR2(1000);
210 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
211 l_region_code VARCHAR2(30);
212 BEGIN
213
214 IF nvl(fnd_profile.value('POA_GLOBAL_SECURITY'), 'N') = 'N' THEN
215 -- Use region pointing to BIS lov restricted based on responsibilities
216 l_region_code := 'POA_BIS_PPS_RPT';
217 BIS_UTILITIES_PUB.Retrieve_Where_Clause
218 (p_user_id => FND_GLOBAL.user_id,
219 p_region_code => 'BIS_OPERATING_UNIT',
220 x_where_clause => l_where_clause,
221 x_return_status => l_return_status,
222 x_error_Tbl => l_error_tbl);
223 ELSE
224 -- Use unrestricted LOV
225 l_region_code := 'POA_BIS_PPS_RPT_G';
226 l_where_clause := NULL;
227 END IF;
228
229 p_param(p_index).Label :=
230 FND_MESSAGE.get_string('PO', 'POA_OPERATING_UNIT');
231 p_param(p_index).Value := htf.formText('POA_BIS_PPS_OPER_UNIT_NAME');
232 p_param(p_index).Action :=
233 '<A HREF="javascript:LOV(''201'', ''POA_BIS_PPS_OPER_UNIT_NAME'', ''201'',
234 ''' || l_region_code || ''',''RPTPFORM'','''','''',''' ||
235 l_where_clause || ''')">
236 <IMG SRC="/OA_MEDIA/FNDILOV.gif" ALIGN="ABSMIDDLE" BORDER=0 ALT
237 ="List of Values"></A>';
238 p_param(p_index).Action := p_param(p_index).Action ||
239 htf.formHidden('POA_BIS_PPS_OPER_UNIT_ID');
240 END Build_PPS_OperatingUnit;
241
242 PROCEDURE Build_SavingsBuyer(
243 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
244 p_index IN NUMBER) IS
245 BEGIN
246 p_param(p_index).Label :=
247 FND_MESSAGE.get_string('PO', 'POA_BUYER');
248 p_param(p_index).Value := htf.formText('POA_BIS_BUYER_NAME');
249 p_param(p_index).Action :=
250 '<A HREF="javascript:LOV(''201'', ''POA_BIS_BUYER_NAME'', ''201'',
251 ''POA_BIS_SAVINGS_RPT'',''RPTPFORM'','''','''','''')">
252 <IMG SRC="/OA_MEDIA/FNDILOV.gif" ALIGN="ABSMIDDLE" BORDER=0 ALT
253 ="List of Values"></A>';
254 p_param(p_index).Action := p_param(p_index).Action ||
255 htf.formHidden('POA_BIS_BUYER_ID');
256 END Build_SavingsBuyer;
257
258 PROCEDURE Build_SavingsCommodity(
259 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
260 p_index IN NUMBER) IS
261 BEGIN
262 p_param(p_index).Label :=
263 FND_MESSAGE.get_string('PO', 'POA_COMMODITY');
264 p_param(p_index).Value := htf.formText('POA_BIS_COMMODITY_NAME');
265 p_param(p_index).Action :=
266 '<A HREF="javascript:LOV(''201'', ''POA_BIS_COMMODITY_NAME'', ''201'',
267 ''POA_BIS_SAVINGS_RPT'',''RPTPFORM'','''','''','''')">
268 <IMG SRC="/OA_MEDIA/FNDILOV.gif" ALIGN="ABSMIDDLE" BORDER=0 ALT
269 ="List of Values"></A>';
270 p_param(p_index).Action := p_param(p_index).Action ||
271 htf.formHidden('POA_BIS_COMMODITY_ID');
272 END Build_SavingsCommodity;
273
274 PROCEDURE Build_SavingsItem(
275 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
276 p_index IN NUMBER) IS
277 BEGIN
278 p_param(p_index).Label :=
279 FND_MESSAGE.get_string('PO', 'POA_ITEM');
280 p_param(p_index).Value := htf.formText('POA_BIS_ITEM_NAME');
281 p_param(p_index).Action :=
282 '<A HREF="javascript:LOV(''201'', ''POA_BIS_ITEM_NAME'', ''201'',
283 ''POA_BIS_SAVINGS_RPT'',''RPTPFORM'','''','''','''')">
284 <IMG SRC="/OA_MEDIA/FNDILOV.gif" ALIGN="ABSMIDDLE" BORDER=0 ALT
285 ="List of Values"></A>';
286 p_param(p_index).Action := p_param(p_index).Action ||
287 htf.formHidden('POA_BIS_ITEM_ID');
288 END Build_SavingsItem;
289
290 PROCEDURE Build_SavingsSupplier(
291 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
292 p_index IN NUMBER) IS
293 BEGIN
294 p_param(p_index).Label :=
295 FND_MESSAGE.get_string('PO', 'POA_SUPPLIER');
296 p_param(p_index).Value := htf.formText('POA_BIS_SUPPLIER_NAME');
297 p_param(p_index).Action :=
298 '<A HREF="javascript:LOV(''201'', ''POA_BIS_SUPPLIER_NAME'', ''201'',
299 ''POA_BIS_SAVINGS_RPT'',''RPTPFORM'','''','''','''')">
300 <IMG SRC="/OA_MEDIA/FNDILOV.gif" ALIGN="ABSMIDDLE" BORDER=0 ALT
301 ="List of Values"></A>';
302 p_param(p_index).Action := p_param(p_index).Action ||
303 htf.formHidden('POA_BIS_SUPPLIER_ID');
304 END Build_SavingsSupplier;
305
306 PROCEDURE Build_KPIPeriodType(
307 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
308 p_index IN NUMBER) IS
309 BEGIN
310 p_param(p_index).Label :=
311 FND_MESSAGE.get_string('PO', 'POA_PERIOD_TYPE');
312 Build_Selection('POA_BIS_PERIOD_TYPE',
313 'SELECT distinct gpt.period_type, gpt.user_period_type
314 FROM gl_period_types gpt, gl_periods glp
315 WHERE glp.period_set_name = (SELECT sob.period_set_name
316 FROM gl_sets_of_books sob
317 WHERE sob.set_of_books_id =
318 to_number(fnd_profile.value_wnps(''GL_SET_OF_BKS_ID'')))
319 AND glp.period_type = gpt.period_type
320 AND glp.adjustment_period_flag = ''N''
321 order by gpt.user_period_type', p_param(p_index).Value);
322 END Build_KPIPeriodType;
323
324 PROCEDURE Build_KPI_2PeriodType(
325 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
326 p_index IN NUMBER) IS
327 BEGIN
328 p_param(p_index).Label := FND_MESSAGE.get_string('POA', 'POA_VIEW_BY');
329 Build_Selection('POA_BIS_PERIOD_TYPE2',
330 'SELECT FND_MESSAGE.get_string(''PO'',''POA_OPERATING_UNIT''),
331 FND_MESSAGE.get_string(''PO'',''POA_OPERATING_UNIT'')
332 FROM dual UNION ALL
333 SELECT FND_MESSAGE.get_string(''PO'',''POA_TIME''),
334 FND_MESSAGE.get_string(''PO'',''POA_TIME'') FROM dual',
335 p_param(p_index).Value);
336 END Build_KPI_2PeriodType;
337
338 PROCEDURE Build_CTL_ViewBy(
339 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
340 p_index IN NUMBER) IS
341 BEGIN
342 p_param(p_index).Label := FND_MESSAGE.get_string('POA', 'POA_VIEW_BY');
343 Build_Selection('POA_BIS_VIEW_BY',
344 'select FND_MESSAGE.get_string(''PO'', ''POA_LEAKAGE_TREND''),
345 FND_MESSAGE.get_string(''PO'', ''POA_LEAKAGE_TREND'')from sys.dual
346 union select FND_MESSAGE.get_string(''PO'', ''POA_EPS''),
347 FND_MESSAGE.get_string(''PO'', ''POA_EPS'')from sys.dual',
348 p_param(p_index).Value);
349 END Build_CTL_ViewBy;
350
351 PROCEDURE Build_LSS_ViewBy(
352 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
353 p_index IN NUMBER) IS
354 BEGIN
355 p_param(p_index).Label := FND_MESSAGE.get_string('POA', 'POA_VIEW_BY');
356 Build_Selection('POA_BIS_VIEW_BY',
357 'select FND_MESSAGE.get_string(''PO'', ''POA_SUPPLIER''),
358 FND_MESSAGE.get_string(''PO'', ''POA_SUPPLIER'')from sys.dual
359 union select FND_MESSAGE.get_string(''PO'', ''POA_ORGANIZATION''),
360 FND_MESSAGE.get_string(''PO'', ''POA_ORGANIZATION'')from sys.dual
361 union select FND_MESSAGE.get_string(''PO'', ''POA_COMMODITY''),
362 FND_MESSAGE.get_string(''PO'', ''POA_COMMODITY'')from sys.dual',
363 p_param(p_index).Value);
364 END Build_LSS_ViewBy;
365
366 PROCEDURE Build_SPA_ViewBy(
367 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
368 p_index IN NUMBER) IS
369 BEGIN
370 p_param(p_index).Label := FND_MESSAGE.get_string('POA', 'POA_VIEW_BY');
371 Build_Selection('POA_BIS_VIEW_BY',
372 'select FND_MESSAGE.get_string(''PO'', ''POA_SUPPLIER''),
373 FND_MESSAGE.get_string(''PO'', ''POA_SUPPLIER'')from sys.dual
374 union select FND_MESSAGE.get_string(''PO'', ''POA_COMMODITY''),
375 FND_MESSAGE.get_string(''PO'', ''POA_COMMODITY'')from sys.dual
376 union select FND_MESSAGE.get_string(''PO'', ''POA_BUYER''),
377 FND_MESSAGE.get_string(''PO'', ''POA_BUYER'')from sys.dual
378 union select FND_MESSAGE.get_string(''PO'', ''POA_OPERATING_UNIT''),
379 FND_MESSAGE.get_string(''PO'', ''POA_OPERATING_UNIT'')from sys.dual
380 union select FND_MESSAGE.get_string(''PO'', ''POA_ITEM''),
381 FND_MESSAGE.get_string(''PO'', ''POA_ITEM'')from sys.dual',
382 p_param(p_index).Value);
383 END Build_SPA_ViewBy;
384
385 PROCEDURE Build_SavingsShipToOrg(
386 p_param IN OUT NOCOPY BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type,
387 p_index IN NUMBER) IS
388 l_where_clause VARCHAR2(1000);
389 l_return_status VARCHAR2(1000);
390 l_error_tbl BIS_UTILITIES_PUB.Error_Tbl_Type;
391 l_region_code VARCHAR2(30);
392 BEGIN
393
394 IF nvl(fnd_profile.value('POA_GLOBAL_SECURITY'), 'N') = 'N' THEN
395 -- Use restricted LOV
396 l_region_code := 'POA_BIS_SAVINGS_RPT';
397 Retrieve_Org_Where_Clause
398 (p_user_id => FND_GLOBAL.user_id,
399 x_where_clause => l_where_clause,
400 x_return_status => l_return_status,
401 x_error_Tbl => l_error_tbl);
402 ELSE
403 l_region_code := 'POA_BIS_SAVINGS_RPT_G';
404 l_where_clause := NULL;
405 END IF;
406
407 p_param(p_index).Label :=
408 FND_MESSAGE.get_string('PO', 'POA_SHIP_TO_ORG');
409 p_param(p_index).Value := htf.formText('POA_BIS_ORG_NAME');
410 p_param(p_index).Action :=
411 '<A HREF="javascript:LOV(''201'', ''POA_BIS_ORG_NAME'', ''201'',''' ||
412 l_region_code || ''',''RPTPFORM'','''','''','''|| l_where_clause ||''')">
413 <IMG SRC="/OA_MEDIA/FNDILOV.gif" ALIGN="ABSMIDDLE" BORDER=0 ALT
414 ="List of Values"></A>';
415 p_param(p_index).Action := p_param(p_index).Action ||
416 htf.formHidden('POA_BIS_ORG_ID');
417 END Build_SavingsShipToOrg;
418
419 PROCEDURE Build_Selection(p_name IN VARCHAR2,
420 p_select IN VARCHAR2, p_output IN OUT NOCOPY VARCHAR2) IS
421 l_cursor NUMBER;
422 l_value VARCHAR2(254);
423 l_display VARCHAR2(254);
424 l_ret_code NUMBER;
425 l_count NUMBER;
426 BEGIN
427 l_cursor := dbms_sql.open_cursor;
428 dbms_sql.parse(l_cursor, p_select, DBMS_SQL.V7);
429 dbms_sql.define_column(l_cursor, 1, l_value, 254);
430 dbms_sql.define_column(l_cursor, 2, l_display, 254);
431 l_ret_code := dbms_sql.execute(l_cursor);
432 l_count := 0;
433 p_output := p_output || ' ' || htf.formSelectOpen(p_name);
434
435 LOOP
436
437 IF (dbms_sql.fetch_rows(l_cursor) > 0) THEN
438 l_count := l_count + 1;
439 dbms_sql.column_value(l_cursor, 1, l_value);
440 dbms_sql.column_value(l_cursor, 2, l_display);
441
442 IF l_count = 1 THEN
443 p_output := p_output || ' ' ||
444 htf.formSelectOption(l_display, 'YES', 'value="' || l_value || '"');
445 ELSE
446 p_output := p_output || ' ' ||
447 htf.formSelectOption(l_display, NULL, 'value="' || l_value || '"');
448 END IF;
449
450 ELSE
451 EXIT;
452 END IF;
453
454 END LOOP;
455
456 dbms_sql.close_cursor(l_cursor);
457 p_output := p_output || ' ' || htf.formSelectClose;
458
459 EXCEPTION
460 WHEN others THEN
461 POA_LOG.put_line('Error in Build_Selection procedure:');
462 POA_LOG.put_line(sqlcode);
463 RAISE;
464 END Build_Selection;
465
466 PROCEDURE Build_ErrorPage(
467 p_param IN BIS_UTILITIES_PUB.Report_Parameter_Tbl_Type) IS
468 l_count NUMBER;
469 BEGIN
470 IF p_param.count > 0 THEN
471 htp.p('<table align=center border=0 cellpadding=0 cellspacing=0
472 width=672> <tr><td><br></td></tr> <tr> <td align=center> <font face=arial>
473 <font size=+1>' ||
474 nvl(FND_MESSAGE.get_string('PO', 'POA_REPORT_PARAM'), 'Report Parameters?')
475 || '</font></td></tr><tr><td><br></td></tr>
476 <tr> <td align=left> <font face=arial>' ||
477 nvl(FND_MESSAGE.get_string('PO', 'POA_INVALID_PARAM'), 'The following parameters are invalid?')
478 || '</font></td></tr> <tr><td><br></td></tr> </table>');
479 htp.tableOpen (calign => 'CENTER', cattributes => ' BORDER=0 WIDTH=96%');
480
481 FOR l_count in 1..p_param.count LOOP
482 htp.tableRowOpen;
483 htp.tableData(cvalue => p_param(l_count).label,
484 calign => 'RIGHT',
485 cattributes => 'VALIGN=CENTER WIDTH=50%');
486 htp.tableData(cvalue => '<I>' || p_param(l_count).value || '</I>',
487 calign => 'LEFT',
488 cattributes => 'VALIGN=CENTER WIDTH=50%');
489 htp.tableRowClose;
490 END LOOP;
491
492 htp.tableRowOpen;
493 htp.tableData(cvalue => '<A Href="javascript:history.back()">Back to parameter page</A>', calign => 'CENTER', cattributes => 'VALIGN=CENTER COLSPAN=2');
494 htp.tableRowClose;
495 htp.tableClose;
496 END IF;
497
498 EXCEPTION
499 WHEN others THEN
500 POA_LOG.put_line('Error in Build_ErrorPage procedure:');
501 POA_LOG.put_line(sqlcode);
502 RAISE;
503 END Build_ErrorPage;
504
505 FUNCTION Validate_OrderDates(p_fdate IN OUT NOCOPY VARCHAR2,
506 p_tdate IN OUT NOCOPY VARCHAR2) RETURN BOOLEAN IS
507 BEGIN
508 IF (p_fdate IS NULL) THEN
509 SELECT to_char(add_months(sysdate, -12)+1, icx_sec.g_date_format)
510 INTO p_fdate
511 FROM SYS.DUAL;
512 END IF;
513
514 IF (p_tdate IS NULL) THEN
515 SELECT to_char(sysdate, icx_sec.g_date_format)
516 INTO p_tdate
517 FROM SYS.DUAL;
518 END IF;
519
520 -- Convert date to DD-MON-YYYY format as thats the format reports expect
521 SELECT to_char(to_date(p_fdate, icx_sec.g_date_format), 'DD-MON-YYYY')
522 INTO p_fdate
523 FROM SYS.DUAL;
524
525 SELECT to_char(to_date(p_tdate, icx_sec.g_date_format), 'DD-MON-YYYY')
526 INTO p_tdate
527 FROM SYS.DUAL;
528
529 RETURN TRUE;
530
531 EXCEPTION WHEN OTHERS THEN
532 RETURN FALSE;
533
534 END Validate_OrderDates;
535
536 FUNCTION Validate_SupplierNum(p_num_of_suppliers IN OUT NOCOPY NUMBER)
537 RETURN BOOLEAN IS
538 BEGIN
539 IF (p_num_of_suppliers is NULL) THEN
540 p_num_of_suppliers := 3;
541 END IF;
542
543 RETURN TRUE;
544
545 EXCEPTION
546 WHEN others THEN
547 POA_LOG.put_line('Error in Validate_SupplierNum procedure:');
548 POA_LOG.put_line(sqlcode);
549 RAISE;
550 END Validate_SupplierNum;
551
552 FUNCTION Validate_QualityCost(p_quality_cost IN OUT NOCOPY NUMBER)
553 RETURN BOOLEAN IS
554 BEGIN
555 IF (p_quality_cost is NULL) THEN
556 p_quality_cost := 25;
557 END IF;
558
559 RETURN TRUE;
560
561 EXCEPTION
562 WHEN others THEN
563 POA_LOG.put_line('Error in Validate_QualityCost procedure:');
564 POA_LOG.put_line(sqlcode);
565 RAISE;
566 END Validate_QualityCost;
567
568 FUNCTION Validate_DeliveryCost(p_delivery_cost IN OUT NOCOPY NUMBER)
569 RETURN BOOLEAN IS
570 BEGIN
571 IF (p_delivery_cost is NULL) THEN
572 p_delivery_cost := 25;
573 END IF;
574
575 RETURN TRUE;
576
577 EXCEPTION
578 WHEN others THEN
579 POA_LOG.put_line('Error in Validate_DeliveryCost procedure:');
580 POA_LOG.put_line(sqlcode);
581 RAISE;
582 END Validate_DeliveryCost;
583
584 FUNCTION Validate_SupplierItem(p_item_name IN VARCHAR2,
585 p_item_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
586 CURSOR c1 IS
587 SELECT msi.inventory_item_id
588 FROM mtl_system_items_kfv msi
589 where msi.concatenated_segments in (p_item_name) ;
590 BEGIN
591 IF (p_item_name IS NULL OR p_item_name = '') THEN
592 p_item_id := NULL;
593 return FALSE;
594 ELSE
595 OPEN c1;
596 FETCH c1 INTO p_item_id;
597
598 IF c1%NOTFOUND THEN
599 CLOSE c1;
600 RETURN FALSE;
601 ELSE
602 CLOSE c1;
603 RETURN TRUE;
604 END IF;
605
606 END IF;
607
608 EXCEPTION WHEN OTHERS THEN
609 IF c1%ISOPEN THEN
610 CLOSE c1;
611 END IF;
612 RETURN FALSE;
613 END Validate_SupplierItem;
614
615 FUNCTION Validate_PrefSupplier(p_pref_supp_name IN VARCHAR2,
616 p_pref_supp_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
617 CURSOR c1 IS
618 SELECT pov.vendor_id
619 FROM po_vendors pov
620 where pov.vendor_name = p_pref_supp_name;
621 BEGIN
622 IF (p_pref_supp_name IS NULL OR p_pref_supp_name = '') THEN
623 p_pref_supp_id := NULL;
624 return FALSE;
625 ELSE
626 OPEN c1;
627 FETCH c1 INTO p_pref_supp_id;
628
629 IF c1%NOTFOUND THEN
630 CLOSE c1;
631 RETURN FALSE;
632 ELSE
633 CLOSE c1;
634 RETURN TRUE;
635 END IF;
636
637 END IF;
638
639 EXCEPTION WHEN OTHERS THEN
640 IF c1%ISOPEN THEN
641 CLOSE c1;
642 END IF;
643 RETURN FALSE;
644 END Validate_PrefSupplier;
645
646 FUNCTION Validate_ConsSupplier(p_cons_supp_name IN VARCHAR2,
647 p_cons_supp_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
648 CURSOR c1 IS
649 SELECT pov.vendor_id
650 FROM po_vendors pov
651 where pov.vendor_name = p_cons_supp_name;
652 BEGIN
653 IF (p_cons_supp_name IS NULL OR p_cons_supp_name = '') THEN
654 p_cons_supp_id := -9999;
655 return TRUE;
656 ELSE
657 OPEN c1;
658 FETCH c1 INTO p_cons_supp_id;
659
660 IF c1%NOTFOUND THEN
661 CLOSE c1;
662 RETURN FALSE;
663 ELSE
664 CLOSE c1;
665 RETURN TRUE;
666 END IF;
667
668 END IF;
669
670 EXCEPTION WHEN OTHERS THEN
671 IF c1%ISOPEN THEN
672 CLOSE c1;
673 END IF;
674 RETURN FALSE;
675 END Validate_ConsSupplier;
676
677 FUNCTION Validate_SavingsBuyer(p_buyer_name IN VARCHAR2,
678 p_buyer_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
679 CURSOR c1 IS
680 SELECT b.person_id
681 FROM po_agents a, per_all_people_f b
682 WHERE a.agent_id = b.person_id
683 AND trunc(sysdate) between b.effective_start_date AND b.effective_end_date
684 AND b.full_name = p_buyer_name;
685 BEGIN
686 IF (p_buyer_name IS NULL OR p_buyer_name = '') THEN
687 p_buyer_id := -9999;
688 return TRUE;
689 ELSE
690 OPEN c1;
691 FETCH c1 INTO p_buyer_id;
692
693 IF c1%NOTFOUND THEN
694 CLOSE c1;
695 RETURN FALSE;
696 ELSE
697 CLOSE c1;
698 RETURN TRUE;
699 END IF;
700
701 END IF;
702
703 EXCEPTION WHEN OTHERS THEN
704 IF c1%ISOPEN THEN
705 CLOSE c1;
706 END IF;
707 RETURN FALSE;
708 END Validate_SavingsBuyer;
709
710 FUNCTION Validate_SavingsSupplier(p_supplier_name IN VARCHAR2,
711 p_supplier_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
712 CURSOR c1 IS
713 SELECT vendor_id
714 FROM po_vendors
715 WHERE vendor_name = p_supplier_name;
716 BEGIN
717 IF (p_supplier_name IS NULL OR p_supplier_name = '') THEN
718 p_supplier_id := -9999;
719 return TRUE;
720 ELSE
721 OPEN c1;
722 FETCH c1 INTO p_supplier_id;
723
724 IF c1%NOTFOUND THEN
725 CLOSE c1;
726 RETURN FALSE;
727 ELSE
728 CLOSE c1;
729 RETURN TRUE;
730 END IF;
731
732 END IF;
733
734 EXCEPTION WHEN OTHERS THEN
735 IF c1%ISOPEN THEN
736 CLOSE c1;
737 END IF;
738 RETURN FALSE;
739 END Validate_SavingsSupplier;
740
741 FUNCTION Validate_SavingsShipToOrg(p_org_name IN VARCHAR2,
742 p_org_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
743 l_global_check NUMBER;
744 CURSOR c0 IS
745 SELECT hr.organization_id
746 FROM hr_organization_units hr
747 WHERE hr.name = p_org_name;
748 CURSOR c1 IS
749 SELECT hr.organization_id
750 FROM hr_organization_units hr
751 WHERE hr.name = p_org_name
752 AND (hr.organization_id IN
753 (SELECT organization_id FROM org_organization_definitions
754 WHERE set_of_books_id IN
755 (SELECT id
756 FROM bis_sets_of_books_v
757 WHERE responsibility_id IN
758 (SELECT responsibility_id
759 FROM fnd_user_resp_groups
760 WHERE user_id = FND_GLOBAL.user_id
761 AND sysdate BETWEEN start_date and nvl(end_date, sysdate+1)))));
762
763 BEGIN
764 IF (p_org_name IS NULL OR p_org_name = '') THEN
765 p_org_id := -9999;
766 return TRUE;
767 ELSE
768
769 IF nvl(fnd_profile.value('POA_GLOBAL_SECURITY'), 'N') = 'N' THEN
770 -- Use restricted check
771 OPEN c1;
772 FETCH c1 INTO p_org_id;
773
774 IF c1%NOTFOUND THEN
775 CLOSE c1;
776 RETURN FALSE;
777 ELSE
778 CLOSE c1;
779 RETURN TRUE;
780 END IF;
781
782 ELSE
783 -- Use unrestricted check
784 OPEN c0;
785 FETCH c0 INTO p_org_id;
786
787 IF c0%NOTFOUND THEN
788 CLOSE c0;
789 RETURN FALSE;
790 ELSE
791 CLOSE c0;
792 RETURN TRUE;
793 END IF;
794
795 END IF;
796
797 END IF;
798
799 EXCEPTION WHEN OTHERS THEN
800 IF c1%ISOPEN THEN
801 CLOSE c1;
802 END IF;
803 IF c0%ISOPEN THEN
804 CLOSE c0;
805 END IF;
806 RETURN FALSE;
807 END Validate_SavingsShipToOrg;
808
809 FUNCTION Validate_SavingsCommodity(p_commodity_name IN VARCHAR2,
810 p_commodity_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
811 CURSOR c1 IS
812 SELECT category_id
813 FROM mtl_categories_kfv
814 WHERE concatenated_segments = p_commodity_name;
815 BEGIN
816 IF (p_commodity_name IS NULL OR p_commodity_name = '') THEN
817 p_commodity_id := -9999;
818 return TRUE;
819 ELSE
820 OPEN c1;
821 FETCH c1 INTO p_commodity_id;
822
823 IF c1%NOTFOUND THEN
824 CLOSE c1;
825 RETURN FALSE;
826 ELSE
827 CLOSE c1;
828 RETURN TRUE;
829 END IF;
830
831 END IF;
832
833 EXCEPTION WHEN OTHERS THEN
834 IF c1%ISOPEN THEN
835 CLOSE c1;
836 END IF;
837 RETURN FALSE;
838 END Validate_SavingsCommodity;
839
840 FUNCTION Validate_SavingsOperatingUnit(p_oper_unit_name IN VARCHAR2,
841 p_oper_unit_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
842 CURSOR c0 IS
843 SELECT organization_id
844 FROM hr_operating_units
845 WHERE name = p_oper_unit_name;
846 CURSOR c1 IS
847 SELECT organization_id
848 FROM hr_operating_units
849 WHERE name = p_oper_unit_name
850 and organization_id IN
851 (SELECT id from bis_operating_units_v
852 WHERE responsibility_id IN
853 (SELECT responsibility_id
854 FROM fnd_user_resp_groups
855 WHERE user_id = FND_GLOBAL.user_id
856 AND sysdate BETWEEN start_date and nvl(end_date, sysdate+1)));
857
858 BEGIN
859
860 IF (p_oper_unit_name IS NULL OR p_oper_unit_name = '') THEN
861 p_oper_unit_id := -9999;
862 return TRUE;
863 ELSE
864
865 IF nvl(fnd_profile.value('POA_GLOBAL_SECURITY'), 'N') = 'N' THEN
866 -- Use restricted check
867 OPEN c1;
868 FETCH c1 INTO p_oper_unit_id;
869
870 IF c1%NOTFOUND THEN
871 CLOSE c1;
872 RETURN FALSE;
873 ELSE
874 CLOSE c1;
875 RETURN TRUE;
876 END IF;
877
878 ELSE
879 -- Use unrestricted check
880 OPEN c0;
881 FETCH c0 INTO p_oper_unit_id;
882
883 IF c0%NOTFOUND THEN
884 CLOSE c0;
885 RETURN FALSE;
886 ELSE
887 CLOSE c0;
888 RETURN TRUE;
889 END IF;
890
891 END IF;
892
893 END IF;
894
895 EXCEPTION WHEN OTHERS THEN
896 IF c1%ISOPEN THEN
897 CLOSE c1;
898 END IF;
899 IF c0%ISOPEN THEN
900 CLOSE c0;
901 END IF;
902 RETURN FALSE;
903 END Validate_SavingsOperatingUnit;
904
905 FUNCTION Validate_PPS_OperatingUnit(p_oper_unit_name IN VARCHAR2,
906 p_oper_unit_id OUT NOCOPY NUMBER) RETURN BOOLEAN IS
907 CURSOR c0 IS
908 SELECT organization_id
909 FROM hr_operating_units
910 WHERE name = p_oper_unit_name;
911 CURSOR c1 IS
912 SELECT organization_id
913 FROM hr_operating_units
914 WHERE name = p_oper_unit_name
915 and organization_id IN
916 (SELECT id FROM bis_operating_units_v
917 WHERE responsibility_id IN
918 (SELECT responsibility_id
919 FROM fnd_user_resp_groups
920 WHERE user_id = FND_GLOBAL.user_id
921 AND sysdate between start_date and nvl(end_date, sysdate+1)));
922 BEGIN
923 IF (p_oper_unit_name IS NULL OR p_oper_unit_name = '') THEN
924 p_oper_unit_id := -9999;
925 return TRUE;
926 ELSE
927
928 IF nvl(fnd_profile.value('POA_GLOBAL_SECURITY'), 'N') = 'N' THEN
929 OPEN c1;
930 FETCH c1 INTO p_oper_unit_id;
931
932 IF c1%NOTFOUND THEN
933 CLOSE c1;
934 RETURN FALSE;
935 ELSE
936 CLOSE c1;
937 RETURN TRUE;
938 END IF;
939
940 ELSE
941 -- Use unrestricted check
942 OPEN c0;
943 FETCH c0 INTO p_oper_unit_id;
944
945 IF c0%NOTFOUND THEN
946 CLOSE c0;
947 RETURN FALSE;
948 ELSE
949 CLOSE c0;
950 RETURN TRUE;
951 END IF;
952
953 END IF;
954
955 END IF;
956
957 EXCEPTION WHEN OTHERS THEN
958 IF c1%ISOPEN THEN
959 CLOSE c1;
960 END IF;
961 IF c0%ISOPEN THEN
962 CLOSE c0;
963 END IF;
964 RETURN FALSE;
965 END Validate_PPS_OperatingUnit;
966
967 --Retrieve the where clause used to extract the set of
968 --organizations tied to a union of responsibilities.
969
970 PROCEDURE Retrieve_Org_Where_Clause
971 (p_user_id IN NUMBER := NULL
972 ,p_user_name IN VARCHAR2 := NULL
973 ,x_where_clause OUT NOCOPY VARCHAR2
974 ,x_return_status OUT NOCOPY VARCHAR2
975 ,x_error_Tbl OUT NOCOPY BIS_UTILITIES_PUB.Error_Tbl_Type
976 )
977 IS
978 l_user_id NUMBER;
979 l_Responsibility_tbl BIS_Responsibility_PVT.Responsibility_Tbl_Type;
980 l_SOB_tbl POA_REPORT_UTIL.SOB_Tbl_Type;
981 l_where_clause BIS_LEVELS.WHERE_CLAUSE%TYPE;
982 l_comma VARCHAR2(2) := ',';
983 l_database_object VARCHAR2(30);
984
985 BEGIN
986
987 IF p_user_id is null then
988 SELECT user_id
989 INTO l_user_id
990 FROM fnd_user
991 WHERE user_name = p_user_name;
992 ELSE
993 l_user_id := p_user_id;
994 END IF;
995
996 BIS_RESPONSIBILITY_PVT.Retrieve_User_Responsibilities
997 (p_api_version => 1.0
998 ,p_user_id => l_user_id
999 ,p_Responsibility_version => NULL
1000 ,x_Responsibility_Tbl => l_Responsibility_tbl
1001 ,x_return_status => x_return_status
1002 ,x_error_tbl => x_error_tbl
1003 );
1004
1005 --Ship-to Orgs are tied to a set of books id.
1006 --For each responsibility_id, find the corresponding set of books id
1007 --to determine which set of orgs to display in the lov.
1008
1009 Retrieve_Set_of_Books_Id
1010 (x_Responsibility_tbl => l_Responsibility_tbl
1011 ,x_SOB_tbl => l_SOB_tbl
1012 ,x_return_status => x_return_status
1013 );
1014
1015 SELECT database_object_name
1016 INTO l_database_object
1017 FROM ak_regions
1018 WHERE region_code = 'BIS_INV_ORGANIZATIONS';
1019
1020
1021 l_where_clause := ' 1 = 2 '
1022 || ' UNION SELECT DISTINCT VALUE, ID '
1023 || ' FROM ' || l_database_object
1024 || ' WHERE ID IN '
1025 || ' (SELECT organization_id FROM org_organization_definitions '
1026 || ' WHERE set_of_books_id IN ( ';
1027
1028
1029 FOR i IN 1..l_SOB_tbl.COUNT LOOP
1030 IF i = l_SOB_tbl.LAST THEN
1031 l_where_clause := l_where_clause || l_SOB_tbl(i).SOB_ID;
1032 ELSE
1033 l_where_clause := l_where_clause || l_SOB_tbl(i).SOB_ID || l_comma;
1034 END IF;
1035 END LOOP;
1036
1037 l_where_clause := l_where_clause || ' ))';
1038
1039 -- convert all of the ASCIII special characters
1040 l_where_clause := WFA_HTML.conv_special_url_chars(l_where_clause);
1041
1042 x_where_clause := l_where_clause;
1043
1044 EXCEPTION
1045 WHEN NO_DATA_FOUND THEN
1046 x_return_status := FND_API.G_RET_STS_ERROR;
1047 WHEN FND_API.G_EXC_ERROR THEN
1048 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1049 WHEN OTHERS THEN
1050 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1051 BIS_UTILITIES_PVT.Add_Error_Message
1052 (p_error_msg_id => SQLCODE
1053 ,p_error_description => SQLERRM
1054 ,p_error_proc_name => 'POA_REPORT_UTIL.Retrieve_Org_Where_Clause'
1055 ,p_error_table => x_error_tbl
1056 ,x_error_table => x_error_tbl
1057 );
1058
1059 END Retrieve_Org_Where_Clause;
1060
1061 --Retrieve the set of books id associated with each responsibility id
1062 PROCEDURE Retrieve_Set_of_Books_Id
1063 (x_Responsibility_tbl IN BIS_RESPONSIBILITY_PVT.Responsibility_Tbl_Type
1064 ,x_SOB_tbl OUT NOCOPY POA_REPORT_UTIL.SOB_Tbl_Type
1065 ,x_return_status OUT NOCOPY VARCHAR2
1066 )
1067 IS
1068 CURSOR sob_cur(p_resp_id NUMBER) IS
1069 SELECT id
1070 FROM bis_sets_of_books_v
1071 WHERE responsibility_id = p_resp_id;
1072
1073 l_rec POA_REPORT_UTIL.SOB_Rec_Type;
1074
1075 BEGIN
1076 x_return_status := FND_API.G_RET_STS_SUCCESS;
1077
1078
1079 FOR i IN 1..x_Responsibility_tbl.COUNT LOOP
1080 FOR cr in sob_cur(x_Responsibility_tbl(i).Responsibility_ID) LOOP
1081 l_rec.SOB_ID := cr.id;
1082 x_SOB_tbl(x_SOB_tbl.COUNT+1) := l_rec;
1083 END LOOP;
1084 END LOOP;
1085
1086 EXCEPTION
1087 WHEN NO_DATA_FOUND THEN
1088 x_return_status := FND_API.G_RET_STS_ERROR;
1089 RAISE FND_API.G_EXC_ERROR;
1090 WHEN FND_API.G_EXC_UNEXPECTED_ERROR then
1091 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1092 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1093 WHEN others THEN
1094 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
1095 BIS_UTILITIES_PVT.Add_Error_Message
1096 ( p_error_msg_id => SQLCODE
1097 ,p_error_description => SQLERRM
1098 ,p_error_proc_name => 'POA_REPORT_UTIL.Retrieve_Set_of_Books_Id'
1099 );
1100 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1101
1102 END Retrieve_Set_of_Books_Id;
1103
1104 END POA_REPORT_UTIL;