[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_PARTIES_PKG
Source
1 package body PA_PROJECT_PARTIES_PKG as
2 /* $Header: PARPPTBB.pls 120.2 2011/12/13 02:00:26 skkoppul ship $ */
3
4
5 procedure INSERT_ROW (
6 X_PROJECT_PARTY_ID in out NOCOPY NUMBER, --File.Sql.39 bug 4440895
7 X_OBJECT_ID in NUMBER,
8 X_OBJECT_TYPE in VARCHAR2,
9 X_PROJECT_ID in NUMBER,
10 X_RESOURCE_ID in NUMBER,
11 X_RESOURCE_TYPE_ID in NUMBER,
12 X_RESOURCE_SOURCE_ID in NUMBER,
13 X_PROJECT_ROLE_ID in NUMBER,
14 X_START_DATE_ACTIVE in DATE,
15 X_END_DATE_ACTIVE in DATE,
16 X_SCHEDULED_FLAG in varchar2,
17 X_GRANT_ID in raw,
18 X_CREATED_BY in NUMBER,
19 X_LAST_UPDATED_BY in NUMBER,
20 X_LAST_UPDATE_LOGIN in NUMBER
21 ) is
22 cursor C is select project_party_id from PA_PROJECT_PARTIES
23 where PROJECT_PARTY_ID = X_PROJECT_PARTY_ID ;
24 x_party c%rowtype;
25
26 begin
27 select pa_project_parties_s.nextval into x_project_party_id
28 from dual;
29
30 insert into PA_PROJECT_PARTIES (
31 PROJECT_PARTY_ID,
32 OBJECT_ID,
33 OBJECT_TYPE,
34 PROJECT_ID,
35 RESOURCE_ID,
36 RESOURCE_TYPE_ID,
37 RESOURCE_SOURCE_ID,
38 PROJECT_ROLE_ID,
39 START_DATE_ACTIVE,
40 END_DATE_ACTIVE,
41 SCHEDULED_FLAG,
42 record_version_number,
43 grant_id,
44 CREATION_DATE,
45 CREATED_BY,
46 LAST_UPDATE_DATE,
47 LAST_UPDATED_BY,
48 LAST_UPDATE_LOGIN
49 ) select
50 X_PROJECT_PARTY_ID,
51 X_OBJECT_ID,
52 X_OBJECT_TYPE,
53 X_PROJECT_ID,
54 X_RESOURCE_ID,
55 X_RESOURCE_TYPE_ID,
56 X_RESOURCE_SOURCE_ID,
57 X_PROJECT_ROLE_ID,
58 X_START_DATE_ACTIVE,
59 X_END_DATE_ACTIVE,
60 X_SCHEDULED_FLAG,
61 1,
62 x_grant_id,
63 sysdate,
64 X_CREATED_BY,
65 sysdate,
66 X_LAST_UPDATED_BY,
67 X_LAST_UPDATE_LOGIN
68 from dual;
69
70 open c;
71 fetch c into X_PARTY;
72 if (c%notfound) then
73 close c;
74 raise no_data_found;
75 end if;
76 close c;
77
78 EXCEPTION when others then
79 rollback;
80 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_PKG',
81 p_procedure_name => 'INSERT_ROW',
82 p_error_text => SUBSTRB(SQLERRM,1,240));
83 raise;
84 end INSERT_ROW;
85
86
87 procedure LOCK_ROW (
88 X_PROJECT_PARTY_ID in NUMBER,
89 X_OBJECT_ID in NUMBER,
90 X_OBJECT_TYPE in VARCHAR2,
91 X_PROJECT_ID in NUMBER,
92 X_RESOURCE_ID in NUMBER,
93 X_RESOURCE_TYPE_ID in NUMBER,
94 X_RESOURCE_SOURCE_ID in NUMBER,
95 X_PROJECT_ROLE_ID in NUMBER,
96 X_START_DATE_ACTIVE in DATE,
97 X_SCHEDULED_FLAG in VARCHAR2,
98 X_END_DATE_ACTIVE in DATE
99 ) is
100 cursor c1 is select
101 OBJECT_ID,
102 OBJECT_TYPE,
103 PROJECT_ID,
104 RESOURCE_ID,
105 RESOURCE_TYPE_ID,
106 RESOURCE_SOURCE_ID,
107 PROJECT_ROLE_ID,
108 START_DATE_ACTIVE,
109 END_DATE_ACTIVE,
110 SCHEDULED_FLAG,
111 PROJECT_PARTY_ID
112 from PA_PROJECT_PARTIES
113 where PROJECT_PARTY_ID = X_PROJECT_PARTY_ID
114 for update of PROJECT_PARTY_ID nowait;
115 begin
116 for tlinfo in c1 loop
117 if ((tlinfo.PROJECT_PARTY_ID = X_PROJECT_PARTY_ID)
118 AND (tlinfo.OBJECT_ID = X_OBJECT_ID)
119 AND (tlinfo.OBJECT_TYPE = X_OBJECT_TYPE)
120 AND (nvl(tlinfo.PROJECT_ID,-99) = nvl(X_PROJECT_ID,-99))
121 AND ((tlinfo.RESOURCE_ID = X_RESOURCE_ID)
122 OR ((tlinfo.RESOURCE_ID is null) AND (X_RESOURCE_ID is null)))
123 AND (tlinfo.RESOURCE_TYPE_ID = X_RESOURCE_TYPE_ID)
124 AND (tlinfo.RESOURCE_SOURCE_ID = X_RESOURCE_SOURCE_ID)
125 AND (tlinfo.PROJECT_ROLE_ID = X_PROJECT_ROLE_ID)
126 AND (tlinfo.START_DATE_ACTIVE = X_START_DATE_ACTIVE)
127 AND (nvl(tlinfo.SCHEDULED_FLAG,'X') = nvl(X_SCHEDULED_FLAG,'X'))
128 AND ((tlinfo.END_DATE_ACTIVE = X_END_DATE_ACTIVE)
129 OR ((tlinfo.END_DATE_ACTIVE is null) AND (X_END_DATE_ACTIVE is null)))
130 ) then
131 null;
132 else
133 fnd_message.set_name('FND', 'FORM_RECORD_CHANGED');
134 app_exception.raise_exception;
135 end if;
136 end loop;
137 return;
138 end LOCK_ROW;
139
140
141 procedure UPDATE_ROW (
142 X_PROJECT_PARTY_ID in NUMBER,
143 X_PROJECT_ID in NUMBER,
144 X_RESOURCE_SOURCE_ID in NUMBER,
145 X_RESOURCE_TYPE_ID in NUMBER,
146 X_PROJECT_ROLE_ID in NUMBER,
147 X_START_DATE_ACTIVE in DATE,
148 X_END_DATE_ACTIVE in DATE,
149 X_SCHEDULED_FLAG in varchar2,
150 X_GRANT_ID in raw,
151 x_record_version_number in number,
152 X_LAST_UPDATE_DATE in DATE,
153 X_LAST_UPDATED_BY in NUMBER,
154 X_LAST_UPDATE_LOGIN in NUMBER,
155 X_RETURN_STATUS Out NOCOPY VARCHAR2 --File.Sql.39 bug 4440895
156 ) is
157 begin
158 x_return_status := 'S';
159 if pa_install.is_prm_licensed() <> 'Y' then
160 update PA_PROJECT_PARTIES set
161 RESOURCE_SOURCE_ID = X_RESOURCE_SOURCE_ID,
162 RESOURCE_TYPE_ID = X_RESOURCE_TYPE_ID,
163 PROJECT_ROLE_ID = X_PROJECT_ROLE_ID,
164 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
165 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
166 SCHEDULED_FLAG = X_SCHEDULED_FLAG,
167 GRANT_ID = nvl(X_GRANT_ID,GRANT_ID),
168 record_version_number = record_version_number + 1,
169 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
170 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
171 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
172 where PROJECT_PARTY_ID = X_PROJECT_PARTY_ID
173 and record_version_number = nvl(x_record_version_number,record_version_number);
174 else
175 update PA_PROJECT_PARTIES set
176 PROJECT_ROLE_ID = X_PROJECT_ROLE_ID, --Bug 13446954
177 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
178 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
179 SCHEDULED_FLAG = X_SCHEDULED_FLAG,
180 GRANT_ID = nvl(X_GRANT_ID,GRANT_ID),
181 record_version_number = record_version_number + 1,
182 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
183 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
184 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
185 where PROJECT_PARTY_ID = X_PROJECT_PARTY_ID
186 and record_version_number = nvl(x_record_version_number,record_version_number);
187 end if;
188
189 if (sql%notfound) then
190 raise no_data_found;
191 end if;
192
193 Exception
194 when no_data_found then
195 -- fnd_message.set_name('PA','PA_XC_RECORD_CHANGED');
196 -- fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_PKG');
197 -- fnd_message.set_token('PROCEDURE_NAME','UPDATE_ROW');
198 -- fnd_msg_pub.add;
199 x_return_status := 'N';
200 -- raise;
201 when others then
202 rollback;
203 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_PKG',
204 p_procedure_name => 'UPDATE_ROW',
205 p_error_text => SUBSTRB(SQLERRM,1,240));
206 raise;
207
208 end UPDATE_ROW;
209
210
211 procedure DELETE_ROW (
212 X_PROJECT_ID in NUMBER,
213 X_PROJECT_PARTY_ID in NUMBER,
214 X_record_version_number in NUMBER
215 ) is
216 begin
217 if x_project_party_id is null then
218 delete from pa_project_parties
219 where project_id = x_project_id;
220 else
221 delete from PA_PROJECT_PARTIES
222 where PROJECT_PARTY_ID = X_PROJECT_PARTY_ID
223 and record_version_number = nvl(x_record_version_number,record_version_number);
224 end if;
225
226 if (sql%notfound) then
227 raise no_data_found;
228 end if;
229
230 Exception
231 when no_data_found then
232 fnd_message.set_name('PA','PA_XC_RECORD_CHANGED');
233 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_PKG');
234 --fnd_message.set_token('PROCEDURE_NAME','DELETE_ROW');
235 fnd_msg_pub.add;
236 when others then
237 rollback;
238 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_PKG',
239 p_procedure_name => 'DELETE_ROW',
240 p_error_text => SUBSTRB(SQLERRM,1,240));
241 raise;
242 end DELETE_ROW;
243
244
245 end PA_PROJECT_PARTIES_PKG;