1 PACKAGE BODY PA_CI_MERGE_PKG AS
2 --$Header: PACIMRGB.pls 120.0.12020000.2 2012/10/29 21:27:19 skkoppul ship $
3
4 PROCEDURE control_items_owner_id(
5 p_entity_name IN VARCHAR2,
6 p_from_id IN NUMBER,
7 x_to_id OUT NOCOPY NUMBER,
8 p_from_fk_id IN NUMBER,
9 p_to_fk_id IN NUMBER,
10 p_parent_entity_name IN VARCHAR2,
11 p_batch_id IN NUMBER,
12 p_batch_party_id IN NUMBER,
13 x_return_status OUT NOCOPY VARCHAR2)
14 IS
15 BEGIN
16 x_return_status := FND_API.G_RET_STS_SUCCESS;
17
18 IF (p_from_fk_id <> p_to_fk_id) THEN
19 UPDATE pa_control_items
20 SET owner_id = p_to_fk_id
21 WHERE owner_id = p_from_fk_id;
22
23 x_to_id := p_from_id;
24
25 END IF;
26 END control_items_owner_id;
27
28 PROCEDURE control_items_last_mod_by_id(
29 p_entity_name IN VARCHAR2,
30 p_from_id IN NUMBER,
31 x_to_id OUT NOCOPY NUMBER,
32 p_from_fk_id IN NUMBER,
33 p_to_fk_id IN NUMBER,
34 p_parent_entity_name IN VARCHAR2,
35 p_batch_id IN NUMBER,
36 p_batch_party_id IN NUMBER,
37 x_return_status OUT NOCOPY VARCHAR2)
38 IS
39 BEGIN
40 x_return_status := FND_API.G_RET_STS_SUCCESS;
41
42 IF (p_from_fk_id <> p_to_fk_id) THEN
43 UPDATE pa_control_items
44 SET last_modified_by_id = p_to_fk_id
45 WHERE last_modified_by_id = p_from_fk_id;
46
47 x_to_id := p_from_id;
48
49 END IF;
50 END control_items_last_mod_by_id;
51
52 PROCEDURE control_items_closed_by_id(
53 p_entity_name IN VARCHAR2,
54 p_from_id IN NUMBER,
55 x_to_id OUT NOCOPY NUMBER,
56 p_from_fk_id IN NUMBER,
57 p_to_fk_id IN NUMBER,
58 p_parent_entity_name IN VARCHAR2,
59 p_batch_id IN NUMBER,
60 p_batch_party_id IN NUMBER,
61 x_return_status OUT NOCOPY VARCHAR2)
62 IS
63 BEGIN
64 x_return_status := FND_API.G_RET_STS_SUCCESS;
65
66 IF (p_from_fk_id <> p_to_fk_id) THEN
67 UPDATE pa_control_items
68 SET closed_by_id = p_to_fk_id
69 WHERE closed_by_id = p_from_fk_id;
70
71 x_to_id := p_from_id;
72
73 END IF;
74 END control_items_closed_by_id;
75
76 PROCEDURE ci_actions_assigned_to(
77 p_entity_name IN VARCHAR2,
78 p_from_id IN NUMBER,
79 x_to_id OUT NOCOPY NUMBER,
80 p_from_fk_id IN NUMBER,
81 p_to_fk_id IN NUMBER,
82 p_parent_entity_name IN VARCHAR2,
83 p_batch_id IN NUMBER,
84 p_batch_party_id IN NUMBER,
85 x_return_status OUT NOCOPY VARCHAR2)
86 IS
87 BEGIN
88 x_return_status := FND_API.G_RET_STS_SUCCESS;
89
90 IF (p_from_fk_id <> p_to_fk_id) THEN
91 UPDATE pa_ci_actions
92 SET assigned_to = p_to_fk_id
93 WHERE assigned_to = p_from_fk_id;
94
95 x_to_id := p_from_id;
96
97 END IF;
98 END ci_actions_assigned_to;
99
100 PROCEDURE ci_impacts_implemented_by(
101 p_entity_name IN VARCHAR2,
102 p_from_id IN NUMBER,
103 x_to_id OUT NOCOPY NUMBER,
104 p_from_fk_id IN NUMBER,
105 p_to_fk_id IN NUMBER,
106 p_parent_entity_name IN VARCHAR2,
107 p_batch_id IN NUMBER,
108 p_batch_party_id IN NUMBER,
109 x_return_status OUT NOCOPY VARCHAR2)
110 IS
111 BEGIN
112 x_return_status := FND_API.G_RET_STS_SUCCESS;
113
114 IF (p_from_fk_id <> p_to_fk_id) THEN
115 UPDATE pa_ci_impacts
116 SET implemented_by = p_to_fk_id
117 WHERE implemented_by = p_from_fk_id;
118
119 x_to_id := p_from_id;
120
121 END IF;
122 END ci_impacts_implemented_by;
123
124 PROCEDURE project_parties_res_src_id(
125 p_entity_name IN VARCHAR2,
126 p_from_id IN NUMBER,
127 x_to_id OUT NOCOPY NUMBER,
128 p_from_fk_id IN NUMBER,
129 p_to_fk_id IN NUMBER,
130 p_parent_entity_name IN VARCHAR2,
131 p_batch_id IN NUMBER,
132 p_batch_party_id IN NUMBER,
133 x_return_status OUT NOCOPY VARCHAR2)
134 IS
135 l_dummy NUMBER;
136 l_party_type hz_parties.party_type%TYPE;
137
138 CURSOR c_parties IS
139 SELECT project_party_id,
140 object_id,
141 object_type,
142 project_role_id,
143 start_date_active,
144 end_date_active
145 FROM pa_project_parties
146 WHERE resource_source_id=p_from_fk_id
147 AND resource_type_id=112;
148
149 cp_party c_parties%ROWTYPE;
150
151 BEGIN
152 x_return_status := FND_API.G_RET_STS_SUCCESS;
153
154 IF (p_from_fk_id <> p_to_fk_id) THEN
155 BEGIN
156 --Looking for a grant from PA for the party being merged
157 SELECT 1
158 INTO l_dummy
159 FROM fnd_grants g, fnd_objects o
160 WHERE o.application_id = 275
161 AND g.object_id = o.object_id
162 AND g.grantee_type = 'USER'
163 AND g.grantee_key = 'HZ_PARTY:'||p_from_fk_id
164 AND ROWNUM = 1;
165
166 --Veto if grant is found for the same party, because grants are not yet merged
167 x_return_status := FND_API.G_RET_STS_ERROR;
168
169 FND_MESSAGE.SET_NAME('PA', 'PA_REJECT_MERGE');
170 FND_MSG_PUB.add;
171
172 EXCEPTION
173 WHEN NO_DATA_FOUND THEN
174 SELECT party_type
175 INTO l_party_type
176 FROM hz_parties
177 WHERE party_id = p_to_fk_id;
178
179 IF l_party_type = 'ORGANIZATION' THEN
180 FOR cp_party IN c_parties LOOP
181 --Looping through every project_party_id corresponding to a party_id
182 BEGIN
183 --Detecting duplicate assignment for a given organization, regardless of effective dates
184 SELECT project_party_id
185 INTO l_dummy
186 FROM pa_project_parties
187 WHERE object_id = cp_party.object_id
188 AND object_type = cp_party.object_type
189 AND project_role_id = cp_party.project_role_id
190 AND resource_type_id = 112
191 AND resource_source_id = p_to_fk_id
192 AND ROWNUM = 1;
193
194 --If duplicated, deleting the old project_party and update customer table
195 UPDATE pa_project_customers
196 SET project_party_id = l_dummy
197 WHERE project_party_id = cp_party.project_party_id;
198
199 DELETE FROM pa_project_parties
200 WHERE project_party_id = cp_party.project_party_id;
201
202 EXCEPTION
203 WHEN NO_DATA_FOUND THEN
204 --Update party_id if no duplicates
205 UPDATE pa_project_parties
206 SET resource_source_id = p_to_fk_id
207 WHERE project_party_id = cp_party.project_party_id;
208 END;
209 END LOOP;
210
211 ELSE
212 --For non organizational parties
213 BEGIN
214 UPDATE pa_project_parties
215 SET resource_source_id = p_to_fk_id
216 WHERE resource_type_id = 112
217 AND resource_source_id = p_from_fk_id;
218 -- added exception block for bug 14593440
219 EXCEPTION
220 When DUP_VAL_ON_INDEX then
221 DELETE pa_project_parties
222 WHERE resource_type_id = 112
223 AND resource_source_id = p_from_fk_id;
224 END;
225 END IF;
226
227 x_to_id := p_from_id;
228 END;
229 END IF;
230 END project_parties_res_src_id;
231
232 PROCEDURE update_project_parties_res_id(
233 p_from_resource_id IN NUMBER,
234 p_to_resource_id IN NUMBER,
235 x_return_status OUT NOCOPY VARCHAR2)
236 IS
237 BEGIN
238 x_return_status := FND_API.G_RET_STS_SUCCESS;
239
240 IF (p_from_resource_id <> p_to_resource_id) THEN
241 UPDATE pa_project_parties
242 SET resource_id = p_to_resource_id
243 WHERE resource_id = p_from_resource_id;
244 END IF;
245 END update_project_parties_res_id;
246
247 END;
[Home] [Help]