[Home] [Help]
PACKAGE BODY: APPS.QPR_REPORT_TYPE_LINES_PKG
Source
1 PACKAGE Body QPR_REPORT_TYPE_LINES_PKG as
2 /* $Header: qpr_reptyp_linb.pls 120.0 2007/10/11 13:14:52 agbennet noship $ */
3 procedure load_row(P_REPORT_TYPE_LINE_ID VARCHAR2,
4 P_REPORT_TYPE_LINE_NAME VARCHAR2,
5 P_ASPECT_RATIO VARCHAR2,
6 P_REPORT_TYPE_LINE_CODE VARCHAR2,
7 P_WIDTH VARCHAR2,
8 P_OWNER VARCHAR2,
9 P_REPORT_OPTIONS VARCHAR2,
10 P_REPORT_OPTIONS_II VARCHAR2,
11 P_REPORT_METADATA VARCHAR2,
12 P_REPORT_METADATA_II VARCHAR2,
13 P_REPORT_METADATA_III VARCHAR2,
14 P_REPORT_METADATA_IV VARCHAR2,
15 P_REPORT_MODIFIERS VARCHAR2,
16 P_REPORT_MODIFIERS_II VARCHAR2) as
17 l_met_clob1 clob;
18 l_met_clob2 clob;
19 l_met_clob3 clob;
20 l_met_clob4 clob;
21 l_met_insert_clob clob;
22 l_opt_clob1 clob;
23 l_opt_clob2 clob;
24 l_opt_insert_clob clob;
25 l_mod_clob1 clob;
26 l_mod_clob2 clob;
27 l_mod_insert_clob clob;
28 l_user_id NUMBER;
29 begin
30
31 l_user_id := FND_LOAD_UTIL.OWNER_ID(P_OWNER);
32 dbms_lob.createtemporary(l_met_clob1,true);
33 dbms_lob.open(l_met_clob1,dbms_lob.lob_readwrite);
34
35 dbms_lob.createtemporary(l_met_clob2,true);
36 dbms_lob.open(l_met_clob2,dbms_lob.lob_readwrite);
37
38 dbms_lob.createtemporary(l_met_clob3,true);
39 dbms_lob.open(l_met_clob3,dbms_lob.lob_readwrite);
40
41 dbms_lob.createtemporary(l_met_clob4,true);
42 dbms_lob.open(l_met_clob4,dbms_lob.lob_readwrite);
43
44 --dbms_lob.createtemporary(l_met_insert_clob,true);
45 --dbms_lob.open(l_met_insert_clob,dbms_lob.lob_readwrite);
46
47 dbms_lob.createtemporary(l_opt_clob1,true);
48 dbms_lob.open(l_opt_clob1,dbms_lob.lob_readwrite);
49
50 dbms_lob.createtemporary(l_opt_clob2,true);
51 dbms_lob.open(l_opt_clob2,dbms_lob.lob_readwrite);
52
53 --dbms_lob.createtemporary(l_opt_insert_clob,true);
54 --dbms_lob.open(l_opt_insert_clob,dbms_lob.lob_readwrite);
55
56 dbms_lob.createtemporary(l_mod_clob1,true);
57 dbms_lob.open(l_mod_clob1,dbms_lob.lob_readwrite);
58
59 dbms_lob.createtemporary(l_mod_clob2,true);
60 dbms_lob.open(l_mod_clob2,dbms_lob.lob_readwrite);
61
62 --dbms_lob.createtemporary(l_mod_insert_clob,true);
63 --dbms_lob.open(l_mod_insert_clob,dbms_lob.lob_readwrite);
64
65 l_met_insert_clob := null;
66 l_opt_insert_clob := null;
67 l_mod_insert_clob := null;
68
69 if ( P_REPORT_METADATA <> fnd_load_util.null_value ) then --is not null ) then
70 dbms_lob.createtemporary(l_met_insert_clob,true);
71 dbms_lob.open(l_met_insert_clob,dbms_lob.lob_readwrite);
72
73 dbms_lob.write(l_met_clob1,length(P_REPORT_METADATA),1,P_REPORT_METADATA);
74 l_met_insert_clob := l_met_clob1;
75 end if;
76
77 if ( P_REPORT_METADATA_II <> fnd_load_util.null_value) then --is not null ) then
78 dbms_lob.write(l_met_clob2,length(P_REPORT_METADATA_II),1,P_REPORT_METADATA_II);
79 dbms_lob.append(l_met_insert_clob,l_met_clob2);
80 end if;
81
82 if ( P_REPORT_METADATA_III <> fnd_load_util.null_value) then --is not null ) then
83 dbms_lob.write(l_met_clob3,length(P_REPORT_METADATA_III),1,P_REPORT_METADATA_III);
84 dbms_lob.append(l_met_insert_clob,l_met_clob3);
85 end if;
86
87 if ( P_REPORT_METADATA_IV <> fnd_load_util.null_value) then --is not null ) then
88 dbms_lob.write(l_met_clob4,length(P_REPORT_METADATA_IV),1,P_REPORT_METADATA_IV);
89 dbms_lob.append(l_met_insert_clob,l_met_clob4);
90 end if;
91
92 if ( P_REPORT_OPTIONS <> fnd_load_util.null_value) then --is not null ) then
93 dbms_lob.createtemporary(l_opt_insert_clob,true);
94 dbms_lob.open(l_opt_insert_clob,dbms_lob.lob_readwrite);
95
96 dbms_lob.write(l_opt_clob1,length(P_REPORT_OPTIONS),1,P_REPORT_OPTIONS);
97 l_opt_insert_clob := l_opt_clob1;
98 end if;
99
100 if ( P_REPORT_OPTIONS_II <> fnd_load_util.null_value) then --is not null ) then
101 dbms_lob.write(l_opt_clob2,length(P_REPORT_OPTIONS_II),1,P_REPORT_OPTIONS_II);
102 dbms_lob.append(l_opt_insert_clob,l_opt_clob2);
103 end if;
104
105 if ( P_REPORT_MODIFIERS <> fnd_load_util.null_value) then --is not null ) then
106 dbms_lob.createtemporary(l_mod_insert_clob,true);
107 dbms_lob.open(l_mod_insert_clob,dbms_lob.lob_readwrite);
108
109 dbms_lob.write(l_mod_clob1,length(P_REPORT_MODIFIERS),1,P_REPORT_MODIFIERS);
110 l_mod_insert_clob := l_mod_clob1;
111 end if;
112
113 if ( P_REPORT_MODIFIERS_II <> fnd_load_util.null_value) then --is not null ) then
114 dbms_lob.write(l_mod_clob2,length(P_REPORT_MODIFIERS_II),1,P_REPORT_MODIFIERS_II);
115 dbms_lob.append(l_mod_insert_clob,l_mod_clob2);
116 end if;
117
118 update qpr_report_type_lines
119 set
120 report_type_line_name = P_REPORT_TYPE_LINE_NAME,
121 report_metadata = l_met_insert_clob,
122 report_modifiers = l_mod_insert_clob,
123 report_options = l_opt_insert_clob,
124 aspect_ratio = to_number(P_ASPECT_RATIO),
125 report_type_line_code = P_REPORT_TYPE_LINE_CODE,
126 width = to_number(P_WIDTH),
127 last_update_date =sysdate,
128 last_updated_by = l_user_id,
129 last_update_login = 0
130 where report_type_line_id = to_number(P_REPORT_TYPE_LINE_ID);
131
132 if SQL%NOTFOUND then
133 insert into qpr_report_type_lines
134 (REPORT_TYPE_LINE_ID,
135 REPORT_TYPE_LINE_NAME,
136 REPORT_TYPE_LINE_CODE,
137 ASPECT_RATIO,
138 WIDTH,
139 CREATION_DATE,
140 CREATED_BY,
141 LAST_UPDATE_DATE,
142 LAST_UPDATED_BY,
143 LAST_UPDATE_LOGIN,
144 REPORT_OPTIONS,
145 REPORT_METADATA,
146 REPORT_MODIFIERS
147 )
148 values
149 (to_number(P_REPORT_TYPE_LINE_ID),
150 P_REPORT_TYPE_LINE_NAME,
151 P_REPORT_TYPE_LINE_CODE,
152 to_number(P_ASPECT_RATIO),
153 to_number(P_WIDTH),
154 sysdate,
155 l_user_id,
156 sysdate,
157 l_user_id,
158 0,
159 l_opt_insert_clob,
160 l_met_insert_clob,
161 l_mod_insert_clob
162 );
163 end if;
164 dbms_lob.close(l_met_clob1);
165 dbms_lob.close(l_met_clob2);
166 dbms_lob.close(l_met_clob3);
167 dbms_lob.close(l_met_clob4);
168 dbms_lob.close(l_met_insert_clob);
169 dbms_lob.close(l_opt_clob1);
170 dbms_lob.close(l_opt_clob2);
171 dbms_lob.close(l_opt_insert_clob);
172 dbms_lob.close(l_mod_clob1);
173 dbms_lob.close(l_mod_clob2);
174 dbms_lob.close(l_mod_insert_clob);
175
176 EXCEPTION
177 WHEN OTHERS then
178 NULL;
179 end load_row;
180 end QPR_REPORT_TYPE_LINES_PKG;