[Home] [Help]
PACKAGE BODY: APPS.PA_PROJECT_PARTIES_PKG
Source
1 package body PA_PROJECT_PARTIES_PKG as
2 /* $Header: PARPPTBB.pls 120.1 2005/08/19 16:58:25 mwasowic noship $ */
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 START_DATE_ACTIVE = X_START_DATE_ACTIVE,
177 END_DATE_ACTIVE = X_END_DATE_ACTIVE,
178 SCHEDULED_FLAG = X_SCHEDULED_FLAG,
179 GRANT_ID = nvl(X_GRANT_ID,GRANT_ID),
180 record_version_number = record_version_number + 1,
181 LAST_UPDATE_DATE = X_LAST_UPDATE_DATE,
182 LAST_UPDATED_BY = X_LAST_UPDATED_BY,
183 LAST_UPDATE_LOGIN = X_LAST_UPDATE_LOGIN
184 where PROJECT_PARTY_ID = X_PROJECT_PARTY_ID
185 and record_version_number = nvl(x_record_version_number,record_version_number);
186 end if;
187
188 if (sql%notfound) then
189 raise no_data_found;
190 end if;
191
192 Exception
193 when no_data_found then
194 -- fnd_message.set_name('PA','PA_XC_RECORD_CHANGED');
195 -- fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_PKG');
196 -- fnd_message.set_token('PROCEDURE_NAME','UPDATE_ROW');
197 -- fnd_msg_pub.add;
198 x_return_status := 'N';
199 -- raise;
200 when others then
201 rollback;
202 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_PKG',
203 p_procedure_name => 'UPDATE_ROW',
204 p_error_text => SUBSTRB(SQLERRM,1,240));
205 raise;
206
207 end UPDATE_ROW;
208
209
210 procedure DELETE_ROW (
211 X_PROJECT_ID in NUMBER,
212 X_PROJECT_PARTY_ID in NUMBER,
213 X_record_version_number in NUMBER
214 ) is
215 begin
216 if x_project_party_id is null then
217 delete from pa_project_parties
218 where project_id = x_project_id;
219 else
220 delete from PA_PROJECT_PARTIES
221 where PROJECT_PARTY_ID = X_PROJECT_PARTY_ID
222 and record_version_number = nvl(x_record_version_number,record_version_number);
223 end if;
224
225 if (sql%notfound) then
226 raise no_data_found;
227 end if;
228
229 Exception
230 when no_data_found then
231 fnd_message.set_name('PA','PA_XC_RECORD_CHANGED');
232 --fnd_message.set_token('PKG_NAME','PA_PROJECT_PARTIES_PKG');
233 --fnd_message.set_token('PROCEDURE_NAME','DELETE_ROW');
234 fnd_msg_pub.add;
235 when others then
236 rollback;
237 fnd_msg_pub.add_exc_msg(p_pkg_name => 'PA_PROJECT_PARTIES_PKG',
238 p_procedure_name => 'DELETE_ROW',
239 p_error_text => SUBSTRB(SQLERRM,1,240));
240 raise;
241 end DELETE_ROW;
242
243
244 end PA_PROJECT_PARTIES_PKG;