[Home] [Help]
PACKAGE BODY: APPS.PA_OBJECT_RELATIONSHIPS_PKG
Source
1 package body PA_OBJECT_RELATIONSHIPS_PKG as
2 /* $Header: PAOBRPKB.pls 120.1 2005/08/19 16:36:25 mwasowic noship $ */
3
4
5 procedure INSERT_ROW
6 ( p_user_id IN NUMBER,
7 p_object_type_from IN VARCHAR2,
8 p_object_id_from1 IN NUMBER,
9 p_object_id_from2 IN NUMBER,
10 p_object_id_from3 IN NUMBER,
11 p_object_id_from4 IN NUMBER,
12 p_object_id_from5 IN NUMBER,
13 p_object_type_to IN VARCHAR2,
14 p_object_id_to1 IN NUMBER,
15 p_object_id_to2 IN NUMBER,
16 p_object_id_to3 IN NUMBER,
17 p_object_id_to4 IN NUMBER,
18 p_object_id_to5 IN NUMBER,
19 p_relationship_type IN VARCHAR2,
20 p_relationship_subtype IN VARCHAR2,
21 p_lag_day IN NUMBER,
22 p_imported_lag IN VARCHAR2,
23 p_priority IN VARCHAR2,
24 p_pm_product_code IN VARCHAR2,
25 p_weighting_percentage IN NUMBER := 0,
26 --FPM bug 3301192
27 p_comments IN VARCHAR2 := NULL,
28 p_status_code IN VARCHAR2 := NULL,
29 --end FPM bug 3301192
30 x_object_relationship_id OUT NOCOPY NUMBER, --File.Sql.39 bug 4440895
31 x_return_status OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
32
33 cursor C is select object_relationship_id from PA_OBJECT_RELATIONSHIPS
34 where OBJECT_RELATIONSHIP_ID = X_OBJECT_RELATIONSHIP_ID ;
35 x_object_relationship c%rowtype;
36
37 begin
38
39 x_return_status := 'S';
40
41 select pa_object_relationships_s.nextval into x_object_relationship_id
42 from dual;
43
44 insert into PA_OBJECT_RELATIONSHIPS (
45 object_relationship_id,
46 object_type_from,
47 object_id_from1,
48 object_id_from2,
49 object_id_from3,
50 object_id_from4,
51 object_id_from5,
52 object_type_to,
53 object_id_to1,
54 object_id_to2,
55 object_id_to3,
56 object_id_to4,
57 object_id_to5,
58 relationship_type,
59 relationship_subtype,
60 lag_day,
61 imported_lag,
62 priority,
63 pm_product_code,
64 Record_Version_Number,
65 CREATED_BY,
66 CREATION_DATE,
67 LAST_UPDATED_BY,
68 LAST_UPDATE_DATE,
69 LAST_UPDATE_LOGIN,
70 weighting_percentage,
71 --FPM bug 3301192
72 comments,
73 status_code
74 --end FPM bug 3301192
75 )
76 values ( x_object_relationship_id,
77 p_object_type_from,
78 p_object_id_from1,
79 p_object_id_from2,
80 p_object_id_from3,
81 p_object_id_from4,
82 p_object_id_from5,
83 p_object_type_to,
84 p_object_id_to1,
85 p_object_id_to2,
86 p_object_id_to3,
87 p_object_id_to4,
88 p_object_id_to5,
89 p_relationship_type,
90 p_relationship_subtype,
91 p_lag_day,
92 p_imported_lag,
93 p_priority,
94 p_pm_product_code,
95 1,
96 p_user_id,
97 sysdate,
98 p_user_id,
99 sysdate,
100 p_user_id,
101 p_weighting_percentage,
102 --FPM bug 3301192
103 p_comments,
104 p_status_code
105 --end FPM bug 3301192
106 );
107
108 open c;
109 fetch c into x_object_relationship;
110 if (c%notfound) then
111 x_return_status := 'E';
112 close c;
113 -- raise no_data_found;
114 end if;
115 close c;
116
117 EXCEPTION when others then
118 x_return_status := 'U';
119 end INSERT_ROW;
120
121 procedure UPDATE_ROW
122 ( p_user_id IN NUMBER,
123 p_object_relationship_id IN NUMBER,
124 p_relationship_type IN VARCHAR2,
125 p_relationship_subtype IN VARCHAR2,
126 p_lag_day IN NUMBER,
127 p_priority IN VARCHAR2,
128 p_pm_product_code IN VARCHAR2,
129 p_weighting_percentage IN NUMBER := PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM,
130 --FPM bug 3301192
131 p_comments IN VARCHAR2 := NULL,
132 p_status_code IN VARCHAR2 := NULL,
133 --end FPM bug 3301192
134 p_record_version_number IN NUMBER,
135 x_return_status OUT NOCOPY VARCHAR2) IS --File.Sql.39 bug 4440895
136
137 Begin
138
139 x_return_status := 'S';
140
141 if p_weighting_percentage = PA_INTERFACE_UTILS_PUB.G_PA_MISS_NUM then
142
143 update pa_object_relationships
144 set
145 relationship_subtype = p_relationship_subtype,
146 lag_day = p_lag_day,
147 priority = p_priority,
148 --FPM bug 3301192
149 comments = p_comments,
150 status_code = p_status_code,
151 --end FPM bug 3301192
152 record_version_number = record_version_number + 1,
153 last_updated_by = p_user_id
154 where
155 object_relationship_id = p_object_relationship_id and
156 record_version_number = p_record_version_number and
157 pm_product_code is null;
158
159 if (sql%notfound) then
160 -- Modified by HSIU
161 -- BUG 1712957
162 -- Changed message to PA_RECORD_CHANGED
163 -- fnd_message.set_name('PA','PA_XC_RECORD_CHANGED');
164 fnd_message.set_name('PA','PA_RECORD_CHANGED');
165 x_return_status := 'E';
166 end if;
167
168 else
169 update pa_object_relationships
170 set
171 relationship_subtype = p_relationship_subtype,
172 lag_day = p_lag_day,
173 priority = p_priority,
174 --FPM bug 3301192
175 comments = p_comments,
176 status_code = p_status_code,
177 --end FPM bug 3301192
178 record_version_number = record_version_number + 1,
179 weighting_percentage = p_weighting_percentage,
180 last_updated_by = p_user_id
181 where
182 object_relationship_id = p_object_relationship_id and
183 record_version_number = p_record_version_number and
184 pm_product_code is null;
185
186 if (sql%notfound) then
187 fnd_message.set_name('PA','PA_RECORD_CHANGED');
188 x_return_status := 'E';
189 end if;
190
191 end if;
192
193 EXCEPTION when others then
194 x_return_status := 'U';
195
196 End;
197
198 procedure DELETE_ROW (
199 p_object_relationship_id IN NUMBER,
200 p_object_type_from IN VARCHAR2,
201 p_object_id_from1 IN NUMBER,
202 p_object_id_from2 IN NUMBER,
203 p_object_id_from3 IN NUMBER,
204 p_object_id_from4 IN NUMBER,
205 p_object_id_from5 IN NUMBER,
206 p_object_type_to IN VARCHAR2,
207 p_object_id_to1 IN NUMBER,
208 p_object_id_to2 IN NUMBER,
209 p_object_id_to3 IN NUMBER,
210 p_object_id_to4 IN NUMBER,
211 p_object_id_to5 IN NUMBER,
212 p_record_version_number IN NUMBER,
213 p_pm_product_code IN VARCHAR2,
214 x_return_status OUT NOCOPY VARCHAR2) is --File.Sql.39 bug 4440895
215 begin
216
217 x_return_status := 'S';
218
219 if p_object_relationship_id is null or p_object_relationship_id = FND_API.G_MISS_NUM then
220 if p_object_type_to is null and (p_object_id_to1 is null or p_object_id_to1 = FND_API.G_MISS_NUM) then
221 delete from pa_object_relationships
222 where object_type_from = 'PA_TASKS'
223 and object_id_from1 = p_object_id_from1
224 and object_id_from2 = p_object_id_from2;
225
226 delete from pa_object_relationships
227 where object_type_to = 'PA_TASKS'
228 and object_id_to1 = p_object_id_from1
229 and object_id_to2 = p_object_id_from2;
230
231 else
232 delete from pa_object_relationships
233 where object_type_from in ('PA_TASKS', 'PA_PROJECTS')
234 and object_type_to in ('PA_TASKS', 'PA_PROJECTS')
235 and object_id_from1 = p_object_id_from1
236 and object_id_to1 = p_object_id_to1
237 and pm_product_code is not null;
238 end if;
239
240 else
241 delete from pa_object_relationships
242 where object_relationship_id = p_object_relationship_id
243 and record_version_number = nvl(p_record_version_number,record_version_number);
244
245 if (sql%notfound) then
246 -- Modified by HSIU
247 -- BUG 1712957
248 -- Changed message to PA_RECORD_CHANGED
249 -- fnd_message.set_name('PA','PA_XC_RECORD_CHANGED');
250 fnd_message.set_name('PA','PA_RECORD_CHANGED');
251 x_return_status := 'E';
252 end if;
253
254 end if;
255
256
257 EXCEPTION when others then
258 x_return_status := 'U';
259
260 end DELETE_ROW;
261
262 end PA_OBJECT_RELATIONSHIPS_PKG;
263