1 PACKAGE BODY PA_RES_MERGE_PKG AS
2 --$Header: PARTCAMB.pls 120.0 2005/05/29 19:07:36 appldev noship $
3 --
4 PROCEDURE res_party_merge(
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 l_from_resource_id NUMBER;
16 l_to_resource_id NUMBER;
17 l_new_party_resource_exists VARCHAR2(1);
18 l_return_status VARCHAR2(1);
19 l_exists VARCHAR2(1);
20 begin
21
22 x_return_status := fnd_api.g_ret_sts_success;
23
24 -- This API merges the resource records in PA_RESOURCE_TXN_ATTRIBUTES.
25 -- We will merge the resource record *ONLY* if it is not referenced in
26 -- any PA tables. The only exception is if there is a reference in
27 -- PA_PROJECT_PARTIES for a key member. We do allow merging party records
28 -- for external key members, so we will allow merging the resource records for
29 -- them, provided that recource record is not reference in any other PA table.
30 IF p_from_fk_id <> p_to_fk_id THEN
31
32 -- Select the resource_id corresponding to the old party id
33 BEGIN
34 SELECT resource_id
35 INTO l_from_resource_id
36 FROM PA_RESOURCE_TXN_ATTRIBUTES
37 WHERE PARTY_ID = p_from_fk_id
38 AND ROWNUM=1;
39 EXCEPTION
40 WHEN NO_DATA_FOUND THEN
41 return;
42 END;
43
44 -- Select the resource_id corresponding to the new party id
45
46 l_new_party_resource_exists := 'Y';
47
48 BEGIN
49 SELECT resource_id
50 INTO l_to_resource_id
51 FROM PA_RESOURCE_TXN_ATTRIBUTES
52 WHERE PARTY_ID = p_to_fk_id
53 AND ROWNUM=1;
54 EXCEPTION
55 WHEN NO_DATA_FOUND THEN
56 l_new_party_resource_exists := 'N';
57 END;
58
59
60 IF l_new_party_resource_exists = 'N'
61 THEN
62 -- No record exists for new party id. Simply update
63 -- the resource record for the old party id.
64
65 UPDATE PA_RESOURCE_TXN_ATTRIBUTES
66 SET PARTY_ID = P_TO_FK_ID,
67 last_update_date = hz_utility_pub.last_update_date,
68 last_updated_by = hz_utility_pub.user_id,
69 last_update_login = hz_utility_pub.last_update_login,
70 request_id = hz_utility_pub.request_id,
71 program_application_id = hz_utility_pub.program_application_id,
72 program_id = hz_utility_pub.program_id,
73 program_update_date = sysdate
74 WHERE PARTY_ID = P_FROM_FK_ID;
75
76 RETURN;
77 END IF;
78
79 -- If we are here is means there are 2 resource records - 1 for the
80 -- from_party_id and 1 for the to_party_id. We need to delete the resource
81 -- record corresponding to the from_party_id. All references to this
82 -- resource_id needs to move to the resource corresponding to the
83 -- to_party_id. The only table where the references will be updated is
84 -- PA_PROJECT_PARTIES. If any other PA table references the old resource
85 -- id, we will veto the merge.
86
87 l_exists := 'N';
88
89 BEGIN
90 SELECT 'Y'
91 INTO l_exists
92 FROM PA_PROJECT_ASSIGNMENTS
93 WHERE RESOURCE_ID = l_from_resource_id
94 AND ROWNUM = 1;
95
96 IF l_exists = 'Y' THEN
97 fnd_message.set_name ('PA', 'PA_REJECT_MERGE');
98 FND_MSG_PUB.add;
99 x_return_status := fnd_api.g_ret_sts_error;
100 return;
101 END IF;
102
103 EXCEPTION
104 WHEN NO_DATA_FOUND THEN
105 null;
106 END;
107
108 BEGIN
109 SELECT 'Y'
110 INTO l_exists
111 FROM PA_CANDIDATES
112 WHERE RESOURCE_ID = l_from_resource_id
113 AND ROWNUM = 1;
114
115 IF l_exists = 'Y' THEN
116 fnd_message.set_name ('PA', 'PA_REJECT_MERGE');
117 FND_MSG_PUB.add;
118 x_return_status := fnd_api.g_ret_sts_error;
119 return;
120 END IF;
121
122 EXCEPTION
123 WHEN NO_DATA_FOUND THEN
124 null;
125 END;
126
127 pa_ci_merge_pkg.update_project_parties_res_id
128 (p_from_resource_id => l_from_resource_id,
129 p_to_resource_id => l_to_resource_id,
130 x_return_status => l_return_status);
131
132 delete from pa_resources
133 where resource_id = l_from_resource_id;
134
135 delete from pa_resource_txn_attributes
136 where resource_id = l_from_resource_id;
137
138 END IF; /* IF p_from_fk_id <> p_to_fk_id */
139
140 end res_party_merge;
141
142 END PA_RES_MERGE_PKG;
143
[Home] [Help]