[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_PERSONS_TBH
Source
1 PACKAGE BODY Igw_Prop_Persons_Tbh AS
2 /* $Header: igwtperb.pls 115.6 2002/11/15 00:40:41 ashkumar ship $ */
3
4 ---------------------------------------------------------------------------
5
6 G_PKG_NAME VARCHAR2(30) := 'IGW_PROP_PERSONS_TBH';
7
8 ---------------------------------------------------------------------------
9
10 PROCEDURE Insert_Row
11 (
12 x_rowid OUT NOCOPY VARCHAR2,
13 p_proposal_id IN NUMBER,
14 p_person_id IN NUMBER,
15 p_person_party_id IN NUMBER,
16 p_proposal_role_code IN VARCHAR2,
17 p_pi_flag IN VARCHAR2,
18 p_key_person_flag IN VARCHAR2,
19 p_percent_effort IN NUMBER,
20 p_person_organization_id IN NUMBER,
21 p_org_party_id IN NUMBER,
22 x_return_status OUT NOCOPY VARCHAR2,
23 p_mode IN VARCHAR2
24 ) IS
25
26 l_api_name CONSTANT VARCHAR2(30) := 'Insert_Row';
27
28 l_last_update_date DATE := SYSDATE;
29 l_last_updated_by NUMBER := Nvl(Fnd_Global.User_Id,-1);
30 l_last_update_login NUMBER := Nvl(Fnd_Global.Login_Id,-1);
31
32 l_person_sequence NUMBER;
33
34 CURSOR c IS
35 SELECT rowid
36 FROM igw_prop_persons
37 WHERE proposal_id = p_proposal_id AND
38 person_party_id = p_person_party_id;
39
40 BEGIN
41
42 x_return_status := Fnd_Api.G_Ret_Sts_Success;
43
44 IF p_mode = 'I' THEN
45
46 l_last_updated_by := 1;
47 l_last_update_login := 0;
48
49 ELSIF p_mode <> 'R' THEN
50
51 Fnd_Message.Set_Name('FND','SYSTEM-INVALID ARGS');
52 App_Exception.Raise_Exception;
53
54 END IF;
55
56 SELECT Nvl(Max(person_sequence) + 1,1)
57 INTO l_person_sequence
58 FROM igw_prop_persons
59 WHERE proposal_id = p_proposal_id;
60
61 INSERT INTO igw_prop_persons
62 (
63 proposal_id,
64 person_id,
65 person_party_id,
66 person_sequence,
67 proposal_role_code,
68 pi_flag,
69 key_person_flag,
70 percent_effort,
71 person_organization_id,
72 org_party_id,
73 record_version_number,
74 creation_date,
75 created_by,
76 last_update_date,
77 last_updated_by,
78 last_update_login
79 )
80 VALUES
81 (
82 p_proposal_id, /* proposal_id */
83 p_person_id, /* person_id */
84 p_person_party_id, /* person_party_id */
85 l_person_sequence, /* person_sequence */
86 p_proposal_role_code, /* proposal_role_code */
87 p_pi_flag, /* pi_flag */
88 p_key_person_flag, /* key_person_flag */
89 p_percent_effort, /* percent_effort */
90 p_person_organization_id, /* person_organization_id */
91 p_org_party_id, /* org_party_id */
92 1, /* record_version_number */
93 l_last_update_date, /* creation_date */
94 l_last_updated_by, /* created_by */
95 l_last_update_date, /* last_update_date */
96 l_last_updated_by, /* last_updated_by */
97 l_last_update_login /* last_update_login */
98 );
99
100 OPEN c;
101 FETCH c INTO x_rowid;
102
103 IF c%NotFound THEN
104
105 CLOSE c;
106 RAISE no_data_found;
107
108 END IF;
109
110 CLOSE c;
111
112 EXCEPTION
113
114 WHEN others THEN
115
116 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
117
118 Fnd_Msg_Pub.Add_Exc_Msg
119 (
120 p_pkg_name => G_PKG_NAME,
121 p_procedure_name => l_api_name
122 );
123
124 RAISE Fnd_Api.G_Exc_Unexpected_Error;
125
126 END Insert_Row;
127
128 ---------------------------------------------------------------------------
129
130 PROCEDURE Update_Row
131 (
132 p_rowid IN VARCHAR2,
133 p_proposal_id IN NUMBER,
134 p_person_id IN NUMBER,
135 p_person_party_id IN NUMBER,
136 p_proposal_role_code IN VARCHAR2,
137 p_pi_flag IN VARCHAR2,
138 p_key_person_flag IN VARCHAR2,
139 p_percent_effort IN NUMBER,
140 p_person_organization_id IN NUMBER,
141 p_org_party_id IN NUMBER,
142 p_record_version_number IN NUMBER,
143 x_return_status OUT NOCOPY VARCHAR2,
144 p_mode IN VARCHAR2
145 ) IS
146
147 l_api_name CONSTANT VARCHAR2(30) := 'Update_Row';
148
149 l_last_update_date DATE := SYSDATE;
150 l_last_updated_by NUMBER := Nvl(Fnd_Global.User_Id,-1);
151 l_last_update_login NUMBER := Nvl(Fnd_Global.Login_Id,-1);
152
153 BEGIN
154
155 x_return_status := Fnd_Api.G_Ret_Sts_Success;
156
157 IF p_mode = 'I' THEN
158
159 l_last_updated_by := 1;
160 l_last_update_login := 0;
161
162 ELSIF p_mode <> 'R' THEN
163
164 Fnd_Message.Set_Name('FND','SYSTEM-INVALID ARGS');
165 Fnd_Msg_Pub.Add;
166 App_Exception.Raise_Exception;
167
168 END IF;
169
170 UPDATE igw_prop_persons
171 SET proposal_id = p_proposal_id,
172 person_id = p_person_id,
173 person_party_id = p_person_party_id,
174 proposal_role_code = p_proposal_role_code,
175 pi_flag = p_pi_flag,
176 key_person_flag = p_key_person_flag,
177 percent_effort = p_percent_effort,
178 person_organization_id = p_person_organization_id,
179 org_party_id = p_org_party_id,
180 record_version_number = record_version_number + 1,
181 last_update_date = l_last_update_date,
182 last_updated_by = l_last_updated_by,
183 last_update_login = l_last_update_login
184 WHERE rowid = p_rowid
185 AND record_version_number = p_record_version_number;
186
187 IF SQL%NotFound THEN
188
189 Fnd_Message.Set_Name('IGW','IGW_SS_RECORD_CHANGED');
190 Fnd_Msg_Pub.Add;
191 App_Exception.Raise_Exception;
192
193 END IF;
194
195 EXCEPTION
196
197 WHEN others THEN
198
199 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
200
201 Fnd_Msg_Pub.Add_Exc_Msg
202 (
203 p_pkg_name => G_PKG_NAME,
204 p_procedure_name => l_api_name
205 );
206
207 RAISE Fnd_Api.G_Exc_Unexpected_Error;
208
209 END Update_Row;
210
211 ---------------------------------------------------------------------------
212
213 PROCEDURE Delete_Row
214 (
215 p_rowid IN VARCHAR2,
216 p_record_version_number IN NUMBER,
217 x_return_status OUT NOCOPY VARCHAR2
218 ) IS
219
220 l_api_name CONSTANT VARCHAR2(30) := 'Delete_Row';
221
222 BEGIN
223
224 x_return_status := Fnd_Api.G_Ret_Sts_Success;
225
226 DELETE igw_prop_persons
227 WHERE rowid = p_rowid
228 AND record_version_number = p_record_version_number;
229
230 IF SQL%NotFound THEN
231
232 Fnd_Message.Set_Name('IGW','IGW_SS_RECORD_CHANGED');
233 Fnd_Msg_Pub.Add;
234 App_Exception.Raise_Exception;
235
236 END IF;
237
238 EXCEPTION
239
240 WHEN others THEN
241
242 x_return_status := Fnd_Api.G_Ret_Sts_Unexp_Error;
243
244 Fnd_Msg_Pub.Add_Exc_Msg
245 (
246 p_pkg_name => G_PKG_NAME,
247 p_procedure_name => l_api_name
248 );
249
250 RAISE Fnd_Api.G_Exc_Unexpected_Error;
251
252 END Delete_Row;
253
254 ---------------------------------------------------------------------------
255
256 END Igw_Prop_Persons_Tbh;