[Home] [Help]
PACKAGE BODY: APPS.IGW_PROP_CHECKLIST_PVT
Source
1 PACKAGE BODY IGW_PROP_CHECKLIST_PVT as
2 /* $Header: igwvpchb.pls 115.7 2002/11/14 18:52:13 vmedikon ship $*/
3
4
5 Procedure update_prop_checklist (
6 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
7 p_commit IN VARCHAR2 := FND_API.G_FALSE,
8 p_validate_only IN VARCHAR2 := FND_API.G_FALSE,
9 x_rowid IN VARCHAR2,
10 p_proposal_id IN NUMBER,
11 p_document_type_code IN VARCHAR2,
12 p_checklist_order IN NUMBER,
13 p_complete IN VARCHAR2,
14 p_not_applicable IN VARCHAR2,
15 p_record_version_number IN NUMBER,
16 x_return_status OUT NOCOPY VARCHAR2,
17 x_msg_count OUT NOCOPY NUMBER,
18 x_msg_data OUT NOCOPY VARCHAR2) is
19
20
21 l_return_status VARCHAR2(1);
22 l_error_msg_code VARCHAR2(250);
23 l_msg_count NUMBER;
24 l_data VARCHAR2(250);
25 l_msg_data VARCHAR2(250);
26 l_msg_index_out NUMBER;
27 n NUMBER;
28
29 BEGIN
30 -- create savepoint if p_commit is true
31 IF p_commit = FND_API.G_TRUE THEN
32 SAVEPOINT update_prop_checklist;
33 END IF;
34
35 -- initialize message list if p_init_msg_list is true
36 if FND_API.to_boolean(nvl(p_init_msg_list, FND_API.G_FALSE)) then
37 fnd_msg_pub.initialize;
38 end if;
39
40 -- initialize return_status to success
41 x_return_status := fnd_api.g_ret_sts_success;
42
43 -- and also check locking.
44 CHECK_LOCK
45 (x_rowid => x_rowid
46 ,p_record_version_number => p_record_version_number
47 ,x_return_status => x_return_status);
48
49 check_errors;
50
51 ------------------------------------- value_id conversion ---------------------------------
52
53 -------------------------------------------- validations -----------------------------------------------------
54 -- check to make sure that the user has not checked both complete and not applicable
55 if ((p_complete = 'Y') and (p_not_applicable = 'Y')) then
56 FND_MESSAGE.SET_NAME('IGW','IGW_SS_CANNOT_CHECK_BOTH');
57 FND_MSG_PUB.Add;
58 end if;
59
60 if ((p_document_type_code = 'BUDGETS') and (p_complete = 'Y')) then
61 select count(*)
62 into n
63 from igw_budgets
64 where proposal_id = p_proposal_id
65 and final_version_flag = 'Y';
66
67 if (n = 0) then
68 FND_MESSAGE.SET_NAME('IGW','IGW_NO_BUDGET_FINAL_VERSION');
69 FND_MSG_PUB.Add;
70 end if;
71 end if;
72
73 check_errors;
74
75 if (NOT FND_API.TO_BOOLEAN (p_validate_only)) then
76 igw_prop_checklist_tbh.update_row (
77 x_rowid => x_rowid
78 ,P_PROPOSAL_ID => P_PROPOSAL_ID
79 ,P_DOCUMENT_TYPE_CODE => P_DOCUMENT_TYPE_CODE
80 ,P_CHECKLIST_ORDER => P_CHECKLIST_ORDER
81 ,P_COMPLETE => P_COMPLETE
82 ,P_NOT_APPLICABLE => P_NOT_APPLICABLE
83 ,p_mode => 'R'
84 ,p_record_version_number => p_record_version_number
85 ,x_return_status => x_return_status);
86
87 end if;
88
89 check_errors;
90
91 -- standard check of p_commit
92 if fnd_api.to_boolean(p_commit) then
93 commit work;
94 end if;
95
96
97 -- standard call to get message count and if count is 1, get message info
98 fnd_msg_pub.count_and_get(p_count => x_msg_count,
99 p_data => x_msg_data);
100
101
102 EXCEPTION
103 WHEN FND_API.G_EXC_ERROR THEN
104 IF p_commit = FND_API.G_TRUE THEN
105 ROLLBACK TO update_prop_checklist;
106 END IF;
107
108 x_return_status := FND_API.G_RET_STS_ERROR;
109
110 fnd_msg_pub.count_and_get(p_count => x_msg_count,
111 p_data => x_msg_data);
112
113
114 WHEN OTHERS THEN
115 IF p_commit = FND_API.G_TRUE THEN
116 ROLLBACK TO update_prop_checklist;
117 END IF;
118
119 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
120
121 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_CHECKLIST_PVT',
122 p_procedure_name => 'UPDATE_PROP_CHECKLIST',
123 p_error_text => SUBSTRB(SQLERRM,1,240));
124
125 fnd_msg_pub.count_and_get(p_count => x_msg_count,
126 p_data => x_msg_data);
127
128
129 END update_prop_checklist;
130
131 ------------------------------------------------------------------------------------------
132 PROCEDURE CHECK_LOCK
133 (x_rowid IN VARCHAR2
134 ,p_record_version_number IN NUMBER
135 ,x_return_status OUT NOCOPY VARCHAR2) is
136
137 l_proposal_id number;
138 BEGIN
139 select proposal_id
140 into l_proposal_id
141 from igw_prop_checklist
142 where rowid = x_rowid
143 and record_version_number = p_record_version_number;
144
145 EXCEPTION
146 WHEN NO_DATA_FOUND THEN
147 x_return_status := FND_API.G_RET_STS_ERROR;
148 FND_MESSAGE.SET_NAME('IGW','IGW_SS_RECORD_CHANGED');
149 FND_MSG_PUB.Add;
150 raise fnd_api.g_exc_error;
151
152 WHEN OTHERS THEN
153 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
154 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_CHECKLIST_PVT',
155 p_procedure_name => 'CHECK_LOCK',
156 p_error_text => SUBSTRB(SQLERRM,1,240));
157 raise fnd_api.g_exc_unexpected_error;
158
159 END CHECK_LOCK;
160
161 -------------------------------------------------------------------------------------------------------
162 PROCEDURE CHECK_ERRORS is
163 l_msg_count NUMBER;
164 BEGIN
165 l_msg_count := fnd_msg_pub.count_msg;
166 IF (l_msg_count > 0) THEN
167 RAISE FND_API.G_EXC_ERROR;
168 END IF;
169
170 END CHECK_ERRORS;
171 -----------------------------------------------------------------------------------------------------
172 PROCEDURE POPULATE_CHECKLIST (
173 P_PROPOSAL_ID IN NUMBER,
174 x_return_status OUT NOCOPY VARCHAR2) is
175
176 BEGIN
177 insert into igw_prop_checklist (
178 proposal_id,
179 document_type_code,
180 checklist_order,
181 complete,
182 not_applicable,
183 record_version_number,
184 last_update_date,
185 last_updated_by,
186 creation_date,
187 created_by,
188 last_update_login)
189 select p_proposal_id,
190 lookup_code,
191 decode(lookup_code, 'BASIC_INFORMATION', 1,
192 'PROGRAM', 2,
193 'PERSONNEL', 3,
194 'PERSON_ASSURANCES', 4,
195 'PERSON_BIOSKETCH', 5,
196 'PERSON_OTHER_SUPPORT', 6,
197 'SPECIAL_REVIEWS', 7,
198 'PARAGRAPHS', 8,
199 'RESEARCH_SUBJECTS', 9,
200 'ASSURANCES', 10,
201 'KEYWORDS', 11,
202 'BUDGETS', 12,
203 'NARRATIVES', 13, 14),
204 'N',
205 'N',
206 1,
207 null,
208 null,
209 sysdate,
210 fnd_global.user_id,
211 fnd_global.user_id
212 from fnd_lookups
213 where lookup_type = 'IGW_SS_PROP_DOC_TYPES';
214
215 EXCEPTION
216 WHEN OTHERS THEN
217
218 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
219
220 fnd_msg_pub.add_exc_msg(p_pkg_name => 'IGW_PROP_CHECKLIST_PVT',
221 p_procedure_name => 'POPULATE_CHECKLIST',
222 p_error_text => SUBSTRB(SQLERRM,1,240));
223
224 END POPULATE_CHECKLIST;
225 -----------------------------------------------------------------------------------------------------------
226 FUNCTION GET_PERSON_NAME_FROM_USER_ID (P_USER_ID IN NUMBER) RETURN VARCHAR2 IS
227
228 l_full_name varchar2(1000);
229 BEGIN
230 select ppx.last_name || ',' || ppx.first_name
231 into l_full_name
232 from per_all_people_f ppx,
233 fnd_user fu
234 where fu.user_id = p_user_id
235 and fu.employee_id = ppx.person_id
236 and rownum < 2;
237
238 return l_full_name;
239
240 EXCEPTION
241 WHEN NO_DATA_FOUND THEN
242 l_full_name := null;
243 return l_full_name;
244 END GET_PERSON_NAME_FROM_USER_ID;
245
246 END IGW_PROP_CHECKLIST_PVT;