[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_PERSON_QUESTIONS_TBH
Source
1 package body IGW_PROP_PERSON_QUESTIONS_TBH as
2 /* $Header: igwtppqb.pls 115.5 2002/11/15 00:42:17 ashkumar ship $*/
3
4 PROCEDURE INSERT_ROW (
5 x_rowid out NOCOPY VARCHAR2,
6 p_proposal_id in NUMBER,
7 p_party_id in NUMBER,
8 p_person_id in NUMBER,
9 p_question_number in VARCHAR2,
10 p_answer in VARCHAR2,
11 p_explanation in VARCHAR2,
12 p_review_date in DATE,
13 p_mode in VARCHAR2,
14 x_return_status out NOCOPY VARCHAR2
15 ) is
16
17 cursor c is select ROWID from IGW_PROP_PERSON_QUESTIONS
18 where proposal_id = p_proposal_id
19 and party_id = p_party_id
20 and question_number = p_question_number;
21
22 l_last_update_date DATE;
23 l_last_updated_by NUMBER;
24 l_last_update_login NUMBER;
25
26 BEGIN
27 x_return_status := FND_API.G_RET_STS_SUCCESS;
28
29 l_last_update_date := SYSDATE;
30
31 if(p_mode = 'I') then
32 l_last_updated_by := 1;
33 l_last_update_login := 0;
34 elsif (p_mode = 'R') then
35 l_last_updated_by := FND_GLOBAL.USER_ID;
36
37 if l_last_updated_by is NULL then
38 l_last_updated_by := -1;
39 end if;
40
41 l_last_update_login := FND_GLOBAL.LOGIN_ID;
42
43 if l_last_update_login is NULL then
44 l_last_update_login := -1;
45 end if;
46 else
47 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
48 app_exception.raise_exception;
49 end if;
50
51 insert into igw_prop_person_questions (
52 proposal_id,
53 party_id,
54 person_id,
55 question_number,
56 answer,
57 explanation,
58 review_date,
59 last_update_date,
60 last_updated_by,
61 creation_date,
62 created_by,
63 last_update_login,
64 record_version_number
65 ) values (
66 p_proposal_id,
67 p_party_id,
68 p_person_id,
69 p_question_number,
70 p_answer,
71 p_explanation,
72 p_review_date,
73 l_last_update_date,
74 l_last_updated_by,
75 l_last_update_date,
76 l_last_updated_by,
77 l_last_update_login,
78 1
79 );
80
81 open c;
82 fetch c into x_rowid;
83 if (c%notfound) then
84 close c;
85 raise no_data_found;
86 end if;
87 close c;
88
89 EXCEPTION
90 when others then
91 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_PERSON_QUESTIONS_TBH',
92 p_procedure_name => 'INSERT_ROW',
93 p_error_text => SUBSTRB(SQLERRM, 1, 240));
94 x_return_status := fnd_api.g_ret_sts_unexp_error;
95 raise;
96
97 END INSERT_ROW;
98 ----------------------------------------------------------------------------------------------------
99 -------- Since the primary key is updateable on the front end rowid is REQUIRED for doing update ------
100 PROCEDURE UPDATE_ROW (
101 x_rowid in VARCHAR2,
102 p_record_version_number in NUMBER,
103 p_proposal_id in NUMBER,
104 p_party_id in NUMBER,
105 p_person_id in NUMBER,
106 p_question_number in VARCHAR2,
107 p_answer in VARCHAR2,
108 p_explanation in VARCHAR2,
109 p_review_date in DATE,
110 p_mode in VARCHAR2,
111 x_return_status out NOCOPY VARCHAR2
112 ) is
113
114 l_last_update_date DATE;
115 l_last_updated_by NUMBER;
116 l_last_update_login NUMBER;
117
118 BEGIN
119 x_return_status := fnd_api.g_ret_sts_success;
120
121 l_last_update_date := SYSDATE;
122 if (p_mode = 'I') then
123 l_last_updated_by := 1;
124 l_last_update_login := 0;
125 elsif (p_mode = 'R') then
126 l_last_updated_by := FND_GLOBAL.USER_ID;
127
128 if l_last_updated_by is NULL then
129 l_last_updated_by := -1;
130 end if;
131
132 l_last_update_login := FND_GLOBAL.LOGIN_ID;
133
134 if l_last_update_login is NULL then
135 l_last_update_login := -1;
136 end if;
137 else
138 FND_MESSAGE.SET_NAME( 'FND', 'SYSTEM-INVALID ARGS');
139 app_exception.raise_exception;
140 end if;
141
142 update IGW_PROP_PERSON_QUESTIONS set
143 proposal_id = p_proposal_id,
144 party_id = p_party_id,
145 person_id = p_person_id,
146 question_number = p_question_number,
147 answer = p_answer,
148 explanation = p_explanation,
149 review_date = p_review_date,
150 last_update_date = l_last_update_date,
151 last_updated_by = l_last_updated_by,
152 last_update_login = l_last_update_login,
153 record_version_number = record_version_number + 1
154 where rowid = x_rowid
155 and record_version_number = p_record_version_number;
156
157 if (sql%notfound) then
158 fnd_message.set_name('IGW', 'IGW_SS_RECORD_CHANGED');
159 fnd_msg_pub.Add;
160 x_return_status := 'E';
161 end if;
162
163
164 EXCEPTION
165 when others then
166 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_PERSON_QUESTIONS_TBH',
167 p_procedure_name => 'UPDATE_ROW',
168 p_error_text => SUBSTRB(SQLERRM, 1, 240));
169 x_return_status := fnd_api.g_ret_sts_unexp_error;
170 raise;
171
172 END UPDATE_ROW;
173 ----------------------------------------------------------------------------------------------------
174
175 PROCEDURE DELETE_ROW (
176 x_rowid in VARCHAR2,
177 p_record_version_number in NUMBER,
178 x_return_status out NOCOPY VARCHAR2
179 ) is
180
181 BEGIN
182 x_return_status := fnd_api.g_ret_sts_success;
183
184 delete from IGW_PROP_PERSON_QUESTIONS
185 where rowid = x_rowid
186 and record_version_number = p_record_version_number;
187
188 if (sql%notfound) then
189 fnd_message.set_name('IGW', 'IGW_SS_RECORD_CHANGED');
190 fnd_msg_pub.Add;
191 x_return_status := 'E';
192 end if;
193
194
195 EXCEPTION
196 when others then
197 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_PERSON_QUESTIONS_TBH',
198 p_procedure_name => 'DELETE_ROW',
199 p_error_text => SUBSTRB(SQLERRM, 1, 240));
200 x_return_status := fnd_api.g_ret_sts_unexp_error;
201 raise;
202
203 END DELETE_ROW;
204
205 END IGW_PROP_PERSON_QUESTIONS_TBH;