1 PACKAGE BODY CN_CompGroups_PVT AS
2 --$Header: cnvmcgb.pls 115.1 2001/10/29 17:20:29 pkm ship $
3
4 G_PKG_NAME CONSTANT VARCHAR2(30):='CN_CompGroups_PVT';
5 G_LAST_UPDATE_DATE DATE := Sysdate;
6 G_LAST_UPDATED_BY NUMBER := fnd_global.user_id;
7 G_CREATION_DATE DATE := Sysdate;
8 G_CREATED_BY NUMBER := fnd_global.user_id;
9 G_LAST_UPDATE_LOGIN NUMBER := fnd_global.login_id;
10
11 -------------------------------------------------------------------------------
12 -- Procedure Name : Assign_ErrorMessage --
13 -- Purpose : Assign the proper error message based on the error code --
14 -- Parameters : --
15 -- IN : p_err_mesg IN NUMBER Required --
16 -- History --
17 -- 04-MAY-99 Ram Kalyanasundaram Created --
18 -------------------------------------------------------------------------------
19 PROCEDURE Assign_ErrorMessage(p_err_mesg IN VARCHAR2)
20 IS
21 BEGIN
22 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
23 FND_MESSAGE.SET_NAME ('CN' , p_err_mesg);
24 FND_MSG_PUB.Add;
25 END IF;
26 END Assign_ErrorMessage;
27
28 -- Start of comments
29 -- API name : Move_Comp_Group
30 -- Type : Public.
31 -- Function : Move a compensation group; this may involve
32 -- terminating the group under a particular parent and
33 -- moving it to another parent effective the date of move
34 -- Also update all the entries of admins adn salesreps
35 -- Pre-reqs : The parent for this comp group should exist unless the
36 -- the group is a toplevel group; Also this group itself
37 -- should exist
38 -- Parameters :
39 -- IN : p_api_version IN NUMBER Required
40 -- Description Standard API parameter which states
41 -- the api version; used to check
42 -- compatibility of calling code
43 -- p_init_msg_list IN VARCHAR2 Optional
44 -- Default = FND_API.G_FALSE
45 -- Description Standard API parameter which
46 -- specifies whether message stack
47 -- should be re-initialized
48 -- p_commit IN VARCHAR2 Optional
49 -- Default = FND_API.G_FALSE
50 -- Description Standard API parameter; specifies
51 -- whether a commit should be issued
52 -- on successful completion of
53 -- transaction
54 -- p_validation_level IN NUMBER Optional
55 -- Default = FND_API.G_VALID_LEVEL_FULL
56 -- Description Standard API parameter; specifies
57 -- validation level
58 -- IMPORTANT: USE DEFAULT
59 -- p_comp_group_rec IN comp_group_rec_type Required
60 -- Description The record containing the
61 -- comp group to be moved
62 -- p_effective_move_date IN DATE Required
63 -- Description The date on which the move is to
64 -- be made
65 -- OUT : x_return_status OUT VARCHAR2(1)
66 -- Description Contains the status of the call
67 -- FND_API.G_RE_STS_SUCCESS
68 -- - SUCCESS
69 -- FND_API.G_RE_STS_UNEXP_ERROR
70 -- - UNEXPECTED ERROR
71 -- FND_API.G_RE_STS_ERROR
72 -- - EXPECTED ERROR
73 -- x_msg_count OUT NUMBER
74 -- Description The number of messages in the
75 -- message stack
76 -- x_msg_data OUT VARCHAR2(2000)
77 -- Description Contains the message data if the
78 -- message count is 1
79 -- Version : Current version 4.x?
80 -- Changed see timestamp
81 -- Initial version 4.0?
82 -- Created 29-APR-99 Ram Kalyanasundaram
83 --
84 -- Notes : Note text
85 -- 1, Check for existence of parent comp group.
86 -- 2, Check for existence of the comp group.
87 -- 3, Get all the groups underneath this group.
88 -- 4, Check if the move date lies between the start and end dates of the
89 -- comp group.
90 -- 5, Check if the new parent is a valid comp group and that its start date
91 -- and end date are atleast partially overlapping the current comp group's
92 -- dates.
93 -- 6, Insert the new comp group with an effective start date of move
94 -- date +1 and the old end_dates.
95 --
96 -- End of comments
97
98 PROCEDURE Move_Group
99 (p_api_version IN NUMBER ,
100 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE ,
101 p_commit IN VARCHAR2 := FND_API.G_FALSE ,
102 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
103 p_comp_group_rec IN comp_group_rec_type ,
104 p_effective_move_date IN date ,
105 p_new_parent_group_id IN cn_comp_groups.comp_group_id%TYPE ,
106 x_return_status OUT VARCHAR2 ,
107 x_msg_count OUT NUMBER ,
108 x_msg_data OUT VARCHAR2 ) IS
109
110 l_api_name CONSTANT VARCHAR2(30) := 'Move_Group';
111 l_api_version CONSTANT NUMBER := 1.0;
112 l_group_relate_id number := 0;
113 l_relation_type varchar2 (30) := 'PARENT_GROUP';
114 l_object_version_number
115 jtf_rs_grp_relations.object_version_number%TYPE;
119 BEGIN
116 l_group_number jtf_rs_groups_b.group_number%TYPE;
117 l_parent_group_number jtf_rs_groups_b.group_number%TYPE;
118
120 -- Standard Start of API savepoint
121 SAVEPOINT Move_Group_PUB;
122 -- Standard call to check for call compatibility.
123 IF NOT FND_API.Compatible_API_Call (
124 l_api_version ,
125 p_api_version ,
126 l_api_name ,
127 G_PKG_NAME )
128 THEN
129 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
130 END IF;
131 -- Initialize message list if p_init_msg_list is set to TRUE.
132 IF FND_API.to_Boolean( p_init_msg_list ) THEN
133 FND_MSG_PUB.initialize;
134 END IF;
135 -- Initialize API return status to success
136 x_return_status := FND_API.G_RET_STS_SUCCESS;
137 -- API body
138 SELECT group_relate_id, object_version_number
139 INTO l_group_relate_id, l_object_version_number
140 FROM jtf_rs_grp_relations
141 WHERE group_id = p_comp_group_rec.comp_group_id
142 AND related_group_id = p_comp_group_rec.parent_comp_group_id
143 AND start_date_active = p_comp_group_rec.comp_group_start_date
144 AND end_date_active = p_comp_group_rec.comp_group_end_date
145 AND relation_type = l_relation_type;
146
147 SELECT group_number
148 INTO l_group_number
149 FROM jtf_rs_groups_b
150 WHERE group_id = p_comp_group_rec.comp_group_id;
151
152 SELECT group_number
153 INTO l_parent_group_number
154 FROM jtf_rs_groups_b
155 WHERE group_id = p_comp_group_rec.parent_comp_group_id;
156
157 jtf_rs_group_relate_pub.update_resource_group_relate
158 (p_api_version => p_api_version,
159 p_init_msg_list => p_init_msg_list,
160 p_commit => p_commit,
161 p_group_relate_id => l_group_relate_id,
162 p_start_date_active => p_comp_group_rec.comp_group_start_date,
163 p_end_date_active => p_effective_move_date,
164 p_object_version_num => l_object_version_number,
165 x_return_status => x_return_status,
166 x_msg_count => x_msg_count,
167 x_msg_data => x_msg_data);
168
169 jtf_rs_group_relate_pub.create_resource_group_relate
170 (p_api_version => p_api_version,
171 p_init_msg_list => p_init_msg_list,
172 p_commit => p_commit,
173 p_group_id => p_comp_group_rec.comp_group_id,
174 p_group_number => l_group_number,
175 p_related_group_id => p_comp_group_rec.parent_comp_group_id,
176 p_related_group_number => l_parent_group_number,
177 p_relation_type => l_relation_type,
178 p_start_date_active => p_effective_move_date+1,
179 p_end_date_active => p_comp_group_rec.comp_group_end_date,
180 x_return_status => x_return_status,
181 x_msg_count => x_msg_count,
182 x_msg_data => x_msg_data,
183 x_group_relate_id => l_group_relate_id);
184
185 --return success
186 assign_errormessage('CN_MOVED');
187 -- End of API body.
188 -- Standard check of p_commit.
189 IF FND_API.To_Boolean( p_commit ) THEN
190 COMMIT WORK;
191 END IF;
192 -- Standard call to get message count and if count is 1, get message info.
193 FND_MSG_PUB.Count_And_get(
194 p_count => x_msg_count ,
195 p_data => x_msg_data );
196 EXCEPTION
197 WHEN FND_API.G_EXC_ERROR THEN
198 ROLLBACK TO Move_Group_PUB;
199 x_return_status := FND_API.G_RET_STS_ERROR ;
200 FND_MSG_PUB.Count_And_Get(
201 p_count => x_msg_count ,
202 p_data => x_msg_data );
203 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
204 ROLLBACK TO Move_Group_PUB;
205 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
206 FND_MSG_PUB.Count_And_Get(
207 p_count => x_msg_count ,
208 p_data => x_msg_data );
209 WHEN OTHERS THEN
210 ROLLBACK TO Move_Group_PUB;
211 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
212 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
213 FND_MSG_PUB.Add_Exc_Msg(
214 G_PKG_NAME ,
215 l_api_name );
216 END IF;
217 FND_MSG_PUB.Count_And_Get(
218 p_count => x_msg_count ,
219 p_data => x_msg_data );
220 END Move_Group;
221
222 END CN_CompGroups_PVT;