[Home] [Help]
PACKAGE BODY: APPS.PV_BENFT_STATUS_MAPS_PKG
Source
1 package body PV_BENFT_STATUS_MAPS_PKG as
2 /* $Header: pvxtbnmb.pls 120.1 2011/03/25 21:28:43 hekkiral ship $ */
3 procedure INSERT_ROW (
4 px_benft_status_map_id IN OUT NOCOPY NUMBER,
5 px_object_version_number IN OUT NOCOPY NUMBER,
6 p_benefit_type IN VARCHAR2,
7 p_vendor_status_code IN VARCHAR2,
8 p_partner_status_code IN VARCHAR2,
9 p_creation_date IN DATE,
10 p_created_by IN NUMBER,
11 p_last_update_date IN DATE,
12 p_last_updated_by IN NUMBER,
13 p_last_update_login IN NUMBER)
14 IS
15 begin
16 insert into PV_BENFT_STATUS_MAPS (
17 benft_status_map_id,
18 object_version_number,
19 benefit_type,
20 vendor_status_code,
21 partner_status_code,
22 creation_date,
23 created_by,
24 last_update_date,
25 last_updated_by,
26 last_update_login
27 ) values (
28 DECODE ( px_benft_status_map_id,FND_API.g_miss_num,NULL,px_benft_status_map_id),
29 DECODE ( px_object_version_number,FND_API.g_miss_num,NULL,px_object_version_number),
30 DECODE ( p_benefit_type,FND_API.g_miss_char,NULL,p_benefit_type),
31 DECODE ( p_vendor_status_code,FND_API.g_miss_char,NULL,p_vendor_status_code),
32 DECODE ( p_partner_status_code,FND_API.g_miss_char,NULL,p_partner_status_code),
33 DECODE ( p_creation_date,FND_API.g_miss_date,NULL,p_creation_date),
34 DECODE ( p_created_by,FND_API.g_miss_num,NULL,p_created_by),
35 DECODE ( p_last_update_date,FND_API.g_miss_date,NULL,p_last_update_date),
36 DECODE ( p_last_updated_by,FND_API.g_miss_num,NULL,p_last_updated_by),
37 DECODE ( p_last_update_login,FND_API.g_miss_num,NULL,p_last_update_login));
38
39
40
41 end INSERT_ROW;
42
43 procedure LOCK_ROW (
44 p_benft_status_map_id IN NUMBER,
45 p_object_version_number IN NUMBER,
46 p_benefit_type IN VARCHAR2,
47 p_vendor_status_code IN VARCHAR2,
48 p_partner_status_code IN VARCHAR2
49 ) is
50 cursor c is select
51 OBJECT_VERSION_NUMBER,
52 BENEFIT_TYPE,
53 VENDOR_STATUS_CODE,
54 PARTNER_STATUS_CODE
55 from PV_BENFT_STATUS_MAPS
56 where BENFT_STATUS_MAP_ID = p_BENFT_STATUS_MAP_ID
57 for update of BENFT_STATUS_MAP_ID nowait;
58 recinfo c%rowtype;
59
60
61 begin
62 open c;
63 fetch c into recinfo;
64 if (c%notfound) then
65 close c;
66 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
67 app_exception.raise_exception;
68 end if;
69 close c;
70 if ( (recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
71 AND ((recinfo.BENEFIT_TYPE = p_BENEFIT_TYPE)
72 OR ((recinfo.BENEFIT_TYPE is null) AND (p_BENEFIT_TYPE is null)))
73 AND ((recinfo.vendor_status_code = p_vendor_status_code)
74 OR ((recinfo.vendor_status_code is null) AND (p_vendor_status_code is null)))
75 AND ((recinfo.partner_status_code = p_partner_status_code)
76 OR ((recinfo.partner_status_code is null) AND (p_partner_status_code is null)))
77 ) then
78 null;
79 else
80 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
81 app_exception.raise_exception;
82 end if;
83
84 return;
85 end LOCK_ROW;
86
87 procedure UPDATE_ROW (
88 p_benft_status_map_id IN NUMBER,
89 p_object_version_number IN NUMBER,
90 p_benefit_type IN VARCHAR2,
91 p_vendor_status_code IN VARCHAR2,
92 p_partner_status_code IN VARCHAR2,
93 p_last_update_date IN DATE,
94 p_last_updated_by IN NUMBER,
95 p_last_update_login IN NUMBER
96 ) is
97 begin
98 UPDATE pv_benft_status_maps SET
99 object_version_number = DECODE ( p_object_version_number,FND_API.g_miss_num,NULL,p_object_version_number+1),
100 benefit_type = DECODE ( p_benefit_type,FND_API.g_miss_char,NULL,p_benefit_type),
101 vendor_status_code = DECODE ( p_vendor_status_code,FND_API.g_miss_char,NULL,p_vendor_status_code),
102 partner_status_code = DECODE ( p_partner_status_code,FND_API.g_miss_char,NULL,p_partner_status_code),
103 last_update_date = DECODE ( p_last_update_date,FND_API.g_miss_date,NULL,p_last_update_date),
104 last_updated_by = DECODE ( p_last_updated_by,FND_API.g_miss_num,NULL,p_last_updated_by),
105 last_update_login = DECODE ( p_last_update_login,FND_API.g_miss_num,NULL,p_last_update_login)
106 WHERE benft_status_map_id = p_benft_status_map_id;
107
108 IF (SQL%NOTFOUND) THEN
109 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
110 END IF;
111
112 end UPDATE_ROW;
113
114 procedure UPDATE_SEED_ROW (
115 p_benft_status_map_id IN NUMBER,
116 p_object_version_number IN NUMBER,
117 p_benefit_type IN VARCHAR2,
118 p_vendor_status_code IN VARCHAR2,
119 p_partner_status_code IN VARCHAR2,
120 p_last_update_date IN DATE,
121 p_last_updated_by IN NUMBER,
122 p_last_update_login IN NUMBER
123 ) IS
124
125 CURSOR c_updated_by
126 IS
127 SELECT last_updated_by
128 FROM pv_benft_status_maps
129 WHERE benft_status_map_id = p_benft_status_map_id;
130
131 l_last_updated_by NUMBER;
132
133 BEGIN
134
135 FOR x IN c_updated_by
136 LOOP
137 l_last_updated_by := x.last_updated_by;
138 END LOOP;
139
140 IF ( l_last_updated_by = 1) THEN
141
142 UPDATE_ROW (
143 p_benft_status_map_id => p_benft_status_map_id,
144 p_object_version_number => p_object_version_number,
145 p_benefit_type => p_benefit_type,
146 p_vendor_status_code => p_vendor_status_code,
147 p_partner_status_code => p_partner_status_code,
148 p_last_update_date => p_last_update_date,
149 p_last_updated_by => p_last_updated_by,
150 p_last_update_login => p_last_update_login
151 );
152
153 ELSE
154 null;
155 END IF;
156 END UPDATE_SEED_ROW;
157 procedure DELETE_ROW (
158 p_BENFT_STATUS_MAP_ID in NUMBER
159 ) is
160 begin
161 delete from PV_BENFT_STATUS_MAPS
162 where BENFT_STATUS_MAP_ID = p_BENFT_STATUS_MAP_ID;
163
164 if (sql%notfound) then
165 raise no_data_found;
166 end if;
167 end DELETE_ROW;
168
169 procedure LOAD_ROW (
170 p_upload_mode IN VARCHAR2,
171 p_benft_status_map_id IN NUMBER,
172 p_object_version_number IN NUMBER,
173 p_benefit_type IN VARCHAR2,
174 p_vendor_status_code IN VARCHAR2,
175 p_partner_status_code IN VARCHAR2,
176 p_owner IN VARCHAR2
177 )
178 IS
179
180 l_user_id number := 0;
181 l_obj_verno number;
182 l_dummy_char varchar2(1);
183 l_row_id varchar2(100);
184 l_benft_status_map_id number := p_benft_status_map_id;
185
186 cursor c_obj_verno is
187 SELECT object_version_number
188 FROM pv_benft_status_maps
189 WHERE benft_status_map_id = p_benft_status_map_id;
190
191 cursor c_chk_status_exists is
192 SELECT 'x'
193 FROM pv_benft_status_maps
194 WHERE benft_status_map_id = p_benft_status_map_id;
195
196 BEGIN
197
198 l_user_id := fnd_load_util.owner_id(p_owner);
199
200 IF p_upload_mode = 'NLS' THEN
201 null;
202 ELSE
203 OPEN c_chk_status_exists;
204 FETCH c_chk_status_exists INTO l_dummy_char;
205 IF c_chk_status_exists%NOTFOUND THEN
206 CLOSE c_chk_status_exists;
207 l_obj_verno := 1;
208
209 INSERT_ROW(
210 px_benft_status_map_id => l_benft_status_map_id,
211 px_object_version_number => l_obj_verno,
212 p_benefit_type => p_benefit_type,
213 p_vendor_status_code => p_vendor_status_code,
214 p_partner_status_code => p_partner_status_code,
215 p_creation_date => SYSDATE,
216 p_created_by => l_user_id,
217 p_last_update_date => SYSDATE,
218 p_last_updated_by => l_user_id,
219 p_last_update_login => 0);
220
221 ELSE
222 close c_chk_status_exists;
223 open c_obj_verno;
224 fetch c_obj_verno into l_obj_verno;
225 close c_obj_verno;
226
227 UPDATE_ROW (
228 p_benft_status_map_id => p_benft_status_map_id,
229 p_object_version_number => l_obj_verno,
230 p_benefit_type => p_benefit_type,
231 p_vendor_status_code => p_vendor_status_code,
232 p_partner_status_code => p_partner_status_code,
233 p_last_update_date => SYSDATE,
234 p_last_updated_by => l_user_id,
235 p_last_update_login => 0);
236
237
238 END IF;
239 END IF;
240
241 END LOAD_ROW;
242 end PV_BENFT_STATUS_MAPS_PKG;