[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_REQUEST_PKG
Source
1 PACKAGE BODY PA_PROJECT_REQUEST_PKG as
2 /* $Header: PAYRPKGB.pls 120.2 2005/08/19 17:24:46 mwasowic noship $ */
3
4 --
5 -- Procedure : Insert_row
6 -- Purpose : Create Row in PA_PROJECT_REQUESTS.
7 --
8 --
9 PROCEDURE insert_row
10 ( p_request_name IN pa_project_requests.request_name%TYPE,
11 p_request_type IN pa_project_requests.request_type%TYPE,
12 p_request_status_code IN pa_project_requests.status_code%TYPE,
13 p_description IN pa_project_requests.description%TYPE,
14 p_expected_proj_approval_date IN pa_project_requests.expected_project_approval_date%TYPE,
15 p_closed_date IN pa_project_requests.closed_date%TYPE,
16 p_source_type IN pa_project_requests.source_type%TYPE :='ORACLE_APPLICATION',
17 p_application_id IN pa_project_requests.application_id%TYPE,
18 p_source_reference IN pa_project_requests.source_reference%TYPE,
19 p_value IN pa_project_requests.value%TYPE,
20 p_currency_code IN pa_project_requests.currency_code%TYPE,
21 p_cust_party_id IN pa_project_requests.cust_party_id%TYPE,
22 p_cust_party_site_id IN pa_project_requests.cust_party_site_id%TYPE,
23 p_cust_account_id IN pa_project_requests.cust_account_id%TYPE,
24 p_source_org_id IN pa_project_requests.source_org_id%TYPE,
25 p_record_version_number IN pa_project_requests.record_version_number%TYPE,
26 p_attribute_category IN pa_project_requests.attribute_category%TYPE,
27 p_attribute1 IN pa_project_requests.attribute1%TYPE,
28 p_attribute2 IN pa_project_requests.attribute2%TYPE,
29 p_attribute3 IN pa_project_requests.attribute3%TYPE,
30 p_attribute4 IN pa_project_requests.attribute4%TYPE,
31 p_attribute5 IN pa_project_requests.attribute5%TYPE,
32 p_attribute6 IN pa_project_requests.attribute6%TYPE,
33 p_attribute7 IN pa_project_requests.attribute7%TYPE,
34 p_attribute8 IN pa_project_requests.attribute8%TYPE,
35 p_attribute9 IN pa_project_requests.attribute9%TYPE,
36 p_attribute10 IN pa_project_requests.attribute10%TYPE,
37 p_attribute11 IN pa_project_requests.attribute11%TYPE,
38 p_attribute12 IN pa_project_requests.attribute12%TYPE,
39 p_attribute13 IN pa_project_requests.attribute13%TYPE,
40 p_attribute14 IN pa_project_requests.attribute14%TYPE,
41 p_attribute15 IN pa_project_requests.attribute15%TYPE,
42 x_request_id OUT NOCOPY pa_project_requests.request_id%TYPE, --File.Sql.39 bug 4440895
43 x_request_number OUT NOCOPY pa_project_requests.request_number%TYPE, --File.Sql.39 bug 4440895
44 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
45 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
46 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
47 IS
48
49 l_request_id pa_project_requests.request_id%TYPE;
50 l_request_number pa_project_requests.request_number%TYPE;
51 l_msg_index_out NUMBER;
52 -- added for bug: 4537865
53 l_new_msg_data VARCHAR2(2000);
54 -- added for bug: 4537865
55
56 BEGIN
57
58 x_return_status := FND_API.G_RET_STS_SUCCESS;
59
60 SELECT pa_project_requests_s.nextval
61 INTO l_request_id
62 FROM DUAL;
63
64 SELECT pa_project_requests_num_s.nextval
65 INTO l_request_number
66 FROM DUAL;
67
68
69 INSERT INTO pa_project_requests
70 (request_id ,
71 request_name ,
72 request_number ,
73 request_type ,
74 status_code ,
75 description ,
76 expected_project_approval_date ,
77 closed_date ,
78 source_type ,
79 application_id ,
80 source_reference ,
81 value ,
82 currency_code ,
83 cust_party_id ,
84 cust_party_site_id ,
85 cust_account_id ,
86 source_org_id ,
87 record_version_number ,
88 program_request_id ,
89 program_application_id ,
90 program_id ,
91 program_update_date ,
92 created_by ,
93 creation_date ,
94 last_updated_by ,
95 last_update_date ,
96 last_update_login ,
97 attribute_category ,
98 attribute1 ,
99 attribute2 ,
100 attribute3 ,
101 attribute4 ,
102 attribute5 ,
103 attribute6 ,
104 attribute7 ,
105 attribute8 ,
106 attribute9 ,
107 attribute10 ,
108 attribute11 ,
109 attribute12 ,
110 attribute13 ,
111 attribute14 ,
112 attribute15)
113 VALUES
114 (l_request_id ,
115 p_request_name ,
116 l_request_number ,
117 p_request_type ,
118 p_request_status_code ,
119 p_description ,
120 p_expected_proj_approval_date ,
121 p_closed_date ,
122 p_source_type ,
123 p_application_id ,
124 p_source_reference ,
125 p_value ,
126 p_currency_code ,
127 p_cust_party_id ,
128 p_cust_party_site_id ,
129 p_cust_account_id ,
130 p_source_org_id ,
131 1 ,
132 fnd_global.conc_request_id() ,
133 fnd_global.prog_appl_id () ,
134 fnd_global.conc_program_id() ,
135 sysdate ,
136 fnd_global.user_id ,
137 sysdate ,
138 fnd_global.user_id ,
139 sysdate ,
140 fnd_global.login_id ,
141 p_attribute_category ,
142 p_attribute1 ,
143 p_attribute2 ,
144 p_attribute3 ,
145 p_attribute4 ,
146 p_attribute5 ,
147 p_attribute6 ,
148 p_attribute7 ,
149 p_attribute8 ,
150 p_attribute9 ,
151 p_attribute10 ,
152 p_attribute11 ,
153 p_attribute12 ,
154 p_attribute13 ,
155 p_attribute14 ,
156 p_attribute15);
157
158 x_request_id := l_request_id;
159 x_request_number := l_request_number;
160
161 EXCEPTION
162 WHEN OTHERS THEN
163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
164 x_msg_count := FND_MSG_PUB.Count_Msg;
165 x_msg_data := substr(SQLERRM,1,240);
166
167 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJECT_REQUESTS_PKG',
168 p_procedure_name => 'insert_row');
169
170 IF x_msg_count = 1 THEN
171 pa_interface_utils_pub.get_messages
172 (p_encoded => FND_API.G_TRUE,
173 p_msg_index => 1,
174 p_msg_count => x_msg_count,
175 p_msg_data => x_msg_data,
176 --p_data => x_msg_data, * commented for Bug: 4537865
177 p_data => l_new_msg_data, -- added for Bug: 4537865
178 p_msg_index_out => l_msg_index_out );
179 -- added for Bug: 4537865
180 x_msg_data := l_new_msg_data;
181 -- added for Bug: 4537865
182 END IF;
183 RAISE;
184
185 END insert_row;
186
187 --
188 -- Procedure : update_row
189 -- Purpose : Update a row in pa_project_requests.
190 --
191 --
192 PROCEDURE update_row
193 ( p_request_id IN pa_project_requests.request_id%TYPE ,
194 p_request_status_code IN pa_project_requests.status_code%TYPE,
195 p_closed_date IN pa_project_requests.closed_date%TYPE DEFAULT NULL,
196 p_record_version_number IN NUMBER DEFAULT NULL,
197 x_return_status OUT NOCOPY VARCHAR2 , --File.Sql.39 bug 4440895
198 x_msg_count OUT NOCOPY NUMBER , --File.Sql.39 bug 4440895
199 x_msg_data OUT NOCOPY VARCHAR2 ) --File.Sql.39 bug 4440895
200 IS
201 l_msg_index_out NUMBER;
202 l_record_version_number NUMBER;
203 -- added for bug: 4537865
204 l_new_msg_data VARCHAR2(2000);
205 -- added for bug: 4537865
206 BEGIN
207 x_return_status := FND_API.G_RET_STS_SUCCESS;
208
209 -- Increment the record version number by 1
210 l_record_version_number := p_record_version_number +1;
211
212
213 UPDATE pa_project_requests
214 SET status_code = p_request_status_code,
215 closed_date = p_closed_date,
216 record_version_number = DECODE(p_record_version_number, NULL, record_version_number, l_record_version_number),
217 last_update_date = sysdate,
218 last_updated_by = fnd_global.user_id,
219 last_update_login = fnd_global.login_id
220 WHERE request_id = p_request_id
221 AND NVL(p_record_version_number, record_version_number) = record_version_number;
222
223 EXCEPTION
224 WHEN OTHERS THEN
225 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
226 x_msg_count := FND_MSG_PUB.Count_Msg;
227 x_msg_data := substr(SQLERRM,1,240);
228
229 FND_MSG_PUB.add_exc_msg( p_pkg_name => 'PA_PROJECT_REQUESTS_PKG',
230 p_procedure_name => 'update_row');
231
232 IF x_msg_count = 1 THEN
233 pa_interface_utils_pub.get_messages
234 (p_encoded => FND_API.G_TRUE,
235 p_msg_index => 1,
236 p_msg_count => x_msg_count,
237 p_msg_data => x_msg_data,
238 --p_data => x_msg_data, * commented for bug: 4537865
239 p_data => l_new_msg_data, -- added for bug: 4537865
240 p_msg_index_out => l_msg_index_out );
241 -- added for bug: 4537865
242 x_msg_data := l_new_msg_data;
243 -- added for bug: 4537865
244 END IF;
245 RAISE;
246
247 END update_row;
248
249
250 END PA_PROJECT_REQUEST_PKG;