[Home] [Help]
PACKAGE BODY: APPS.PV_REFERRAL_STATUS_MAPS_PKG
Source
1 package body PV_REFERRAL_STATUS_MAPS_PKG as
2 /* $Header: pvxtrfmb.pls 120.0 2005/07/11 23:13:08 appldev noship $ */
3 procedure INSERT_ROW (
4 px_referral_status_map_id IN OUT NOCOPY NUMBER,
5 px_object_version_number IN OUT NOCOPY NUMBER,
6 p_benefit_type IN VARCHAR2,
7 p_status_code IN VARCHAR2,
8 p_map_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_referral_status_maps (
17 referral_status_map_id,
18 object_version_number,
19 benefit_type,
20 status_code,
21 map_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_referral_status_map_id,FND_API.g_miss_num,NULL,px_referral_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_status_code,FND_API.g_miss_char,NULL,p_status_code),
32 DECODE ( p_map_status_code,FND_API.g_miss_char,NULL,p_map_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 end INSERT_ROW;
41
42 procedure LOCK_ROW (
43 p_referral_status_map_id IN NUMBER,
44 p_object_version_number IN NUMBER,
45 p_benefit_type IN VARCHAR2,
46 p_status_code IN VARCHAR2,
47 p_map_status_code IN VARCHAR2
48 ) is
49 cursor c is select
50 OBJECT_VERSION_NUMBER,
51 BENEFIT_TYPE,
52 STATUS_CODE,
53 MAP_STATUS_CODE
54 from PV_REFERRAL_STATUS_MAPS
55 where REFERRAL_STATUS_MAP_ID = p_REFERRAL_STATUS_MAP_ID
56 for update of REFERRAL_STATUS_MAP_ID nowait;
57 recinfo c%rowtype;
58
59 begin
60 open c;
61 fetch c into recinfo;
62 if (c%notfound) then
63 close c;
64 fnd_message.set_name('FND', 'FORM_RECORD_DELETED');
65 app_exception.raise_exception;
66 end if;
67 close c;
68 if ( (recinfo.OBJECT_VERSION_NUMBER = p_OBJECT_VERSION_NUMBER)
69 AND ((recinfo.BENEFIT_TYPE = p_BENEFIT_TYPE)
70 OR ((recinfo.BENEFIT_TYPE is null) AND (p_BENEFIT_TYPE is null)))
71 AND ((recinfo.STATUS_CODE = p_STATUS_CODE)
72 OR ((recinfo.STATUS_CODE is null) AND (p_STATUS_CODE is null)))
73 AND ((recinfo.MAP_STATUS_CODE = p_MAP_STATUS_CODE)
74 OR ((recinfo.MAP_STATUS_CODE is null) AND (p_MAP_STATUS_CODE is null)))
75 ) then
76 null;
77 else
78 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
79 app_exception.raise_exception;
80 end if;
81
82 return;
83 end LOCK_ROW;
84
85 procedure UPDATE_ROW (
86 p_referral_status_map_id IN NUMBER,
87 p_object_version_number IN NUMBER,
88 p_benefit_type IN VARCHAR2,
89 p_status_code IN VARCHAR2,
90 p_map_status_code IN VARCHAR2,
91 p_last_update_date IN DATE,
92 p_last_updated_by IN NUMBER,
93 p_last_update_login IN NUMBER
94 ) is
95 begin
96 UPDATE pv_referral_status_maps SET
97 object_version_number = DECODE ( p_object_version_number,FND_API.g_miss_num,NULL,p_object_version_number+1),
98 benefit_type = DECODE ( p_benefit_type,FND_API.g_miss_char,NULL,p_benefit_type),
99 status_code = DECODE ( p_status_code,FND_API.g_miss_char,NULL,p_status_code),
100 map_status_code = DECODE ( p_map_status_code,FND_API.g_miss_char,NULL,p_map_status_code),
101 last_update_date = DECODE ( p_last_update_date,FND_API.g_miss_date,NULL,p_last_update_date),
102 last_updated_by = DECODE ( p_last_updated_by,FND_API.g_miss_num,NULL,p_last_updated_by),
103 last_update_login = DECODE ( p_last_update_login,FND_API.g_miss_num,NULL,p_last_update_login)
104 WHERE referral_status_map_id = p_referral_status_map_id;
105
106 IF (SQL%NOTFOUND) THEN
107 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
108 END IF;
109
110
111 end UPDATE_ROW;
112
113 procedure UPDATE_SEED_ROW (
114 p_referral_status_map_id IN NUMBER,
115 p_object_version_number IN NUMBER,
116 p_benefit_type IN VARCHAR2,
117 p_status_code IN VARCHAR2,
118 p_map_status_code IN VARCHAR2,
119 p_last_update_date IN DATE,
120 p_last_updated_by IN NUMBER,
121 p_last_update_login IN NUMBER
122 ) IS
123
124 CURSOR c_updated_by
125 IS
126 SELECT last_updated_by
127 FROM pv_referral_status_maps
128 WHERE referral_status_map_id = p_referral_status_map_id;
129
130 l_last_updated_by NUMBER;
131
132 BEGIN
133
134 FOR x IN c_updated_by
135 LOOP
136 l_last_updated_by := x.last_updated_by;
137 END LOOP;
138
139 IF ( l_last_updated_by = 1) THEN
140
141 UPDATE_ROW (
142 p_referral_status_map_id => p_referral_status_map_id,
143 p_object_version_number => p_object_version_number,
144 p_benefit_type => p_benefit_type,
145 p_status_code => p_status_code,
146 p_map_status_code => p_map_status_code,
147 p_last_update_date => p_last_update_date,
148 p_last_updated_by => p_last_updated_by,
149 p_last_update_login => p_last_update_login
150 );
151
152 ELSE
153 null;
154 END IF;
155
156
157 end UPDATE_SEED_ROW;
158
159 procedure DELETE_ROW (
160 p_REFERRAL_STATUS_MAP_ID in NUMBER
161 ) is
162 begin
163
164 delete from PV_REFERRAL_STATUS_MAPS
165 where REFERRAL_STATUS_MAP_ID = p_REFERRAL_STATUS_MAP_ID;
166
167 IF (SQL%NOTFOUND) THEN
168 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
169 END IF;
170 end DELETE_ROW;
171
172 procedure LOAD_ROW (
173 p_upload_mode IN VARCHAR2,
174 p_referral_status_map_id IN NUMBER,
175 p_object_version_number IN NUMBER,
176 p_benefit_type IN VARCHAR2,
177 p_status_code IN VARCHAR2,
178 p_map_status_code IN VARCHAR2,
179 p_owner IN VARCHAR2
180 )
181 IS
182
183 l_user_id number := 0;
184 l_obj_verno number;
185 l_dummy_char varchar2(1);
186 l_row_id varchar2(100);
187 l_referral_status_map_id number := p_referral_status_map_id;
188
189 cursor c_obj_verno is
190 SELECT object_version_number
191 FROM pv_referral_status_maps
192 WHERE referral_status_map_id = p_referral_status_map_id;
193
194 cursor c_chk_status_exists is
195 SELECT 'x'
196 FROM pv_referral_status_maps
197 WHERE referral_status_map_id = p_referral_status_map_id;
198
199 BEGIN
200
201 IF p_OWNER = 'SEED' then
202 l_user_id := 1;
203 ELSE
204 l_user_id := 0;
205 END IF;
206 IF p_upload_mode = 'NLS' THEN
207 null;
208 ELSE
209 OPEN c_chk_status_exists;
210 FETCH c_chk_status_exists INTO l_dummy_char;
211 IF c_chk_status_exists%NOTFOUND THEN
212 CLOSE c_chk_status_exists;
213 l_obj_verno := 1;
214
215 INSERT_ROW(
216 px_referral_status_map_id => l_referral_status_map_id,
217 px_object_version_number => l_obj_verno,
218 p_benefit_type => p_benefit_type,
219 p_status_code => p_status_code,
220 p_map_status_code => p_map_status_code,
221 p_creation_date => SYSDATE,
222 p_created_by => l_user_id,
223 p_last_update_date => SYSDATE,
224 p_last_updated_by => l_user_id,
225 p_last_update_login => 0);
226
227 ELSE
228 close c_chk_status_exists;
229 open c_obj_verno;
230 fetch c_obj_verno into l_obj_verno;
231 close c_obj_verno;
232
233 UPDATE_ROW (
234 p_referral_status_map_id => p_referral_status_map_id,
235 p_object_version_number => l_obj_verno,
236 p_benefit_type => p_benefit_type,
237 p_status_code => p_status_code,
238 p_map_status_code => p_map_status_code,
239 p_last_update_date => SYSDATE,
240 p_last_updated_by => l_user_id,
241 p_last_update_login => 0);
242
243
244 END IF;
245 END IF;
246
247 END LOAD_ROW;
248 end PV_REFERRAL_STATUS_MAPS_PKG;