PACKAGE BODY: APPS.FA_RX_LOV_PKG

Source

1 PACKAGE BODY FA_RX_LOV_PKG as
2 /* $Header: faxrxlvb.pls 120.6 2012/02/05 22:06:20 glchen ship $ */
3 
4   procedure unparse_lov_select (
5 	X_lovid		number,
6 	X_params	fa_rx_lov_pkg.param_array,
7 	X_param_types	fa_rx_lov_pkg.param_array,
8 	X_num_params	number,
9 	X_select out nocopy clob,
10 	X_missing_param out nocopy number) is
11 
12   h_sqlstmt	clob;
13   ii		integer;
14 
15   h_date_str	varchar2(25);
16   h_date_format varchar2(25);
17 
18   begin
19 
20     select select_statement
21     into h_sqlstmt
22     from fa_rx_lov
23     where lov_id = X_lovid;
24 
25     ii := X_num_params;
26 
27     loop
28       if X_params(ii) is not null then
29 
30 	if X_param_types(ii) = 'VARCHAR2' then
31 
32 	  h_sqlstmt :=  replace(h_sqlstmt, ':P'||to_char(ii),
33 			''''||X_params(ii)||'''');
34 
35 	else if X_param_types(ii) = 'NUMBER' then
36 	  h_sqlstmt :=  replace(h_sqlstmt, ':P'||to_char(ii),X_params(ii));
37 
38 	else  -- DATE
39 
40 	  h_date_str := X_params(ii);
41 	  h_date_format := 'DD/MM/YYYY';
42 
43 	  h_sqlstmt := replace(h_sqlstmt, ':P'||to_char(ii),
44 		     'to_date('||''''||h_date_str||''''||','||
45 		     ''''||h_date_format||''''||')');
46 
47 	end if;
48 	end if;
49 
50       else	-- param is null; check if it's needed.
51 		-- If so, then exit right away, indicating missing param #.
52 
53 	if instr(h_sqlstmt,':P'||to_char(ii)) <> 0 then
54 	  X_missing_param := ii;
55 	  return;
56 	end if;
57 
58       end if;
59       ii := ii - 1;
60       exit when ii < 1;
61     end loop;
62 
63     X_missing_param := 0;
64     X_select := h_sqlstmt;
65 
66   end unparse_lov_select;
67 end FA_RX_LOV_PKG;