[Home] [Help]
PACKAGE BODY: APPS.PAY_JP_SWOT_NUMBERS_PKG
Source
1 PACKAGE BODY PAY_JP_SWOT_NUMBERS_PKG AS
2 /* $Header: pyjppswn.pkb 120.1.12010000.3 2009/06/24 06:49:56 keyazawa ship $ */
3 --
4 -------------------------------------------------------------------------------
5 -- INSERT ROW
6 --
7 -- To insert a row into PAY_JP_SWOT_NUMBERS from PAYJPSWN.fmx
8 --
9 -------------------------------------------------------------------------------
10 PROCEDURE insert_row
11 (
12 x_rowid in out nocopy varchar2,
13 x_organization_id in number,
14 x_district_code in varchar2,
15 x_report_district_code in varchar2,
16 x_swot_number in varchar2,
17 x_efile_exclusive_flag in varchar2,
18 x_output_file_name in varchar2,
19 x_import_exclusive_flag in varchar2,
20 x_input_file_name in varchar2,
21 x_last_update_date in date,
22 x_last_updated_by in number,
23 x_last_update_login in number,
24 x_created_by in number,
25 x_creation_date in date
26 )IS
27 --
28 cursor cur_row is
29 select rowidtochar(rowid)
30 from pay_jp_swot_numbers
31 where organization_id = x_organization_id
32 and district_code = x_district_code;
33 --
34 BEGIN
35 --
36 insert into pay_jp_swot_numbers
37 (
38 organization_id,
39 district_code,
40 report_district_code,
41 swot_number,
42 efile_exclusive_flag,
43 output_file_name,
44 import_exclusive_flag,
45 input_file_name,
46 last_update_date,
47 last_updated_by,
48 last_update_login,
49 created_by,
50 creation_date
51 )
52 values
53 (
54 x_organization_id,
55 x_district_code,
56 x_report_district_code,
57 x_swot_number,
58 x_efile_exclusive_flag,
59 x_output_file_name,
60 x_import_exclusive_flag,
61 x_input_file_name,
62 x_last_update_date,
63 x_last_updated_by,
64 x_last_update_login,
65 x_created_by,
66 x_creation_date
67 );
68 --
69 open cur_row;
70 fetch cur_row into x_rowid;
71 --
72 if (cur_row%NOTFOUND) then
73 close cur_row;
74 raise NO_DATA_FOUND;
75 end if;
76 --
77 close cur_row;
78 --
79 END insert_row;
80 --
81 -------------------------------------------------------------------------------
82 -- LOCK ROW
83 --
84 -- To lock row of PAY_JP_SWOT_NUMBERS from PAYJPSWN.fmx
85 --
86 -------------------------------------------------------------------------------
87 PROCEDURE lock_row
88 (
89 x_rowid in varchar2,
90 x_organization_id in number,
91 x_district_code in varchar2,
92 x_report_district_code in varchar2,
93 x_swot_number in varchar2,
94 x_efile_exclusive_flag in varchar2,
95 x_output_file_name in varchar2,
96 x_import_exclusive_flag in varchar2,
97 x_input_file_name in varchar2,
98 x_last_update_date in date,
99 x_last_updated_by in number,
100 x_last_update_login in number,
101 x_created_by in number,
102 x_creation_date in date
103 )IS
104 --
105 cursor cur_row is
106 select
107 organization_id,
108 district_code,
109 report_district_code,
110 swot_number,
111 efile_exclusive_flag,
112 output_file_name,
113 import_exclusive_flag,
114 input_file_name,
115 last_update_date,
116 last_updated_by,
117 last_update_login,
118 created_by,
119 creation_date
120 from
121 pay_jp_swot_numbers
122 where
123 rowid = chartorowid(x_rowid)
124 for update of
125 organization_id,
126 district_code,
127 report_district_code,
128 swot_number,
129 efile_exclusive_flag,
130 output_file_name,
131 import_exclusive_flag,
132 input_file_name
133 nowait;
134 --
135 l_cur_row cur_row%ROWTYPE;
136 --
137 BEGIN
138 --
139 open cur_row;
140 fetch cur_row into l_cur_row;
141 --
142 if (cur_row%NOTFOUND) then
143 close cur_row;
144 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
145 app_exception.raise_exception;
146 end if;
147 --
148 if
149 l_cur_row.organization_id = x_organization_id
150 and
151 l_cur_row.district_code = x_district_code
152 and
153 (
154 l_cur_row.report_district_code = x_report_district_code
155 or
156 (
157 l_cur_row.report_district_code is null
158 and
159 x_report_district_code is null
160 )
161 )
162 and
163 (
164 l_cur_row.swot_number = x_swot_number
165 or
166 (
167 l_cur_row.swot_number is null
168 and
169 x_swot_number is null
170 )
171 )
172 and
173 l_cur_row.last_update_date = x_last_update_date
174 and
175 l_cur_row.last_updated_by = x_last_updated_by
176 and
177 (
178 l_cur_row.last_update_login = x_last_update_login
179 or
180 (
181 l_cur_row.last_update_login is null
182 and
183 x_last_update_login is null
184 )
185 )
186 and
187 l_cur_row.created_by = x_created_by
188 and
189 l_cur_row.creation_date = x_creation_date
190 then
191 return;
192 else
193 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
194 app_exception.raise_exception;
195 end if;
196 --
197 END lock_row;
198 --
199 -------------------------------------------------------------------------------
200 -- UPDATE ROW
201 --
202 -- To update row of PAY_JP_SWOT_NUMBERS from PAYJPSWN.fmx
203 --
204 -------------------------------------------------------------------------------
205 PROCEDURE update_row
206 (
207 x_rowid in varchar2,
208 x_organization_id in number,
209 x_district_code in varchar2,
210 x_report_district_code in varchar2,
211 x_swot_number in varchar2,
212 x_efile_exclusive_flag in varchar2,
213 x_output_file_name in varchar2,
214 x_import_exclusive_flag in varchar2,
215 x_input_file_name in varchar2,
216 x_last_update_date in date,
217 x_last_updated_by in number,
218 x_last_update_login in number,
219 x_created_by in number,
220 x_creation_date in date
221 )IS
222 BEGIN
223 --
224 update
225 pay_jp_swot_numbers
226 set
227 organization_id = x_organization_id,
228 district_code = x_district_code,
229 report_district_code = x_report_district_code,
230 swot_number = x_swot_number,
231 efile_exclusive_flag = x_efile_exclusive_flag,
232 output_file_name = x_output_file_name,
233 import_exclusive_flag = x_import_exclusive_flag,
234 input_file_name = x_input_file_name,
235 last_update_date = x_last_update_date,
236 last_updated_by = x_last_updated_by,
237 last_update_login = x_last_update_login,
238 created_by = x_created_by,
239 creation_date = x_creation_date
240 where
241 rowid = chartorowid(x_rowid);
242 --
243 if (SQL%NOTFOUND) then
244 raise NO_DATA_FOUND;
245 end if;
246 --
247 END update_row;
248 --
249 --------------------------------------------------------------------------------
250 -- DELETE ROW
251 --
252 -- To delete row of PAY_JP_SWOT_NUMBERS from PAYJPSWN.fmx
253 --
254 -------------------------------------------------------------------------------
255 PROCEDURE delete_row(x_rowid in varchar2)IS
256 BEGIN
257 --
258 delete from pay_jp_swot_numbers
259 where rowid = chartorowid(x_rowid);
260 --
261 if (SQL%NOTFOUND) then
262 raise NO_DATA_FOUND;
263 end if;
264 --
265 END delete_row;
266 --
267 --------------------------------------------------------------------------------
268 -- CHK SWOT NUMBER
269 --
270 -- To check swot number column length. If the column is over 15 byte length,
271 -- return False.
272 --
273 -------------------------------------------------------------------------------
274 FUNCTION chk_swot_number(p_value in VARCHAR2) return VARCHAR2 IS
275 --
276 l_length_swot_num number;
277 --
278 BEGIN
279 --
280 l_length_swot_num := lengthb(p_value);
281 --
282 if l_length_swot_num > 15 then
283 return('FALSE');
284 elsif l_length_swot_num is null then
285 return('FALSE');
286 elsif l_length_swot_num < 1 then
287 return('FALSE');
288 else
289 return('TRUE');
290 end if;
291 --
292 END chk_swot_number;
293 --
294 -- -------------------------------------------------------------------------
295 -- chk_output_file_name
296 -- -------------------------------------------------------------------------
297 function chk_output_file_name(
298 p_output_file_name in varchar2)
299 return boolean
300 is
301 --
302 l_val_symbol varchar2(255) := '._-';
303 l_inv_symbol varchar2(255) := ' !"#$%&''()*+,/:;<=>?@[\]^`{|}~';
304 l_inv_tra_symbol varchar2(255) := '@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@';
305 l_result boolean := true;
306 --
307 begin
308 --
309 if p_output_file_name is not null then
310 --
311 if lengthb(p_output_file_name) <> length(p_output_file_name) then
312 l_result := false;
313 elsif instr(translate(p_output_file_name,l_inv_symbol,l_inv_tra_symbol),'@') <> 0 then
314 l_result := false;
315 elsif translate(p_output_file_name,'@'||l_val_symbol,'@') is null then
316 l_result := false;
317 end if;
318 --
319 end if;
320 --
321 return l_result;
322 end chk_output_file_name;
323 --
324 END PAY_JP_SWOT_NUMBERS_PKG;