1 PACKAGE BODY CN_ADD_TBH_PVT AS
2 /*$Header: cnvatbhb.pls 115.8 2003/05/02 18:48:56 fting ship $*/
3
4 G_PKG_NAME CONSTANT VARCHAR2(30) := 'CN_ADD_TBH_PVT';
5
6 -- Start of comments
7 -- API name : Create_TBH - Private.
8 -- Pre-reqs : None.
9 -- IN : standard params
10 -- mgr_srp_id, emp_num, comp_group, job_title_id, role_id
11 -- start+end date for srp, mgr assignment, job assignment
12 -- OUT : standard params
13 -- x_srp_id
14 -- Version : 1.0
15 -- End of comments
16
17 PROCEDURE Create_TBH
18 (p_api_version IN NUMBER, -- required
19 p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE,
20 p_commit IN VARCHAR2 := FND_API.G_FALSE,
21 p_validation_level IN NUMBER := FND_API.G_VALID_LEVEL_FULL,
22 p_mgr_srp_id IN NUMBER,
23 p_name IN VARCHAR2,
24 p_emp_num IN VARCHAR2,
25 p_comp_group_id IN NUMBER,
26 p_start_date_active IN DATE,
27 p_end_date_active IN DATE,
28 x_return_status OUT NOCOPY VARCHAR2,
29 x_msg_count OUT NOCOPY NUMBER,
30 x_msg_data OUT NOCOPY VARCHAR2,
31 x_srp_id OUT NOCOPY NUMBER) IS
32
33 l_api_name CONSTANT VARCHAR2(30) := 'Create_TBH';
34 l_api_version CONSTANT NUMBER := 1.0;
35
36 l_resource_id NUMBER;
37 l_resource_number NUMBER;
38 l_srp_id NUMBER;
39 l_return_status VARCHAR2(1);
40 l_msg_count NUMBER;
41 l_msg_data VARCHAR2(2000);
42 l_mgr_sct_id NUMBER;
43 l_group_member_id NUMBER;
44
45 cursor mgr_info is
46 select s.sales_credit_type_id
47 from cn_rs_salesreps s,
48 jtf_rs_resource_extns r
49 where s.salesrep_id = p_mgr_srp_id
50 and s.resource_id = r.resource_id;
51
52 BEGIN
53 -- Standard Start of API savepoint
54 SAVEPOINT Create_TBH;
55
56 -- Standard call to check for call compatibility.
57 IF NOT FND_API.Compatible_API_Call
58 (l_api_version, p_api_version, l_api_name, G_PKG_NAME) THEN
59 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
60 END IF;
61
62 -- Initialize message list if p_init_msg_list is set to TRUE.
63 IF FND_API.to_Boolean( p_init_msg_list ) THEN
64 FND_MSG_PUB.initialize;
65 END IF;
66
67 -- Initialize API return status to success
68 x_return_status := FND_API.G_RET_STS_SUCCESS;
69
70 -- create the resource
71 -- create the salesrep
72 -- create the role, job_title, and comp group assignment
73 -- create the manager assignment
74
75 -- inherit properties from manager
76 open mgr_info;
77 fetch mgr_info into l_mgr_sct_id;
78 if mgr_info%notfound then
79 close mgr_info;
80 RAISE FND_API.G_EXC_ERROR;
81 end if;
82 close mgr_info;
83
84 jtf_rs_resource_pub.create_resource
85 (P_API_VERSION => 1.0,
86 P_CATEGORY => 'TBH',
87 P_START_DATE_ACTIVE => p_start_date_active,
88 P_END_DATE_ACTIVE => p_end_date_active,
89 P_RESOURCE_NAME => p_name,
90 P_SOURCE_NAME => p_name,
91
92 -- all other properties are left as null (they aren't required)
93 X_RETURN_STATUS => l_return_status,
94 X_MSG_COUNT => l_msg_count,
95 X_MSG_DATA => l_msg_data,
96 X_RESOURCE_ID => l_resource_id,
97 X_RESOURCE_NUMBER => l_resource_number);
98 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
99 raise FND_API.G_EXC_ERROR;
100 end if;
101
102 jtf_rs_salesreps_pub.create_salesrep
103 (P_API_VERSION => 1.0,
104 P_RESOURCE_ID => l_resource_id,
105 P_NAME => p_name,
106 P_SALESREP_NUMBER => p_emp_num,
107 P_START_DATE_ACTIVE => p_start_date_active, -- same as resource
108 P_END_DATE_ACTIVE => p_end_date_active, -- same as resource
109
110 -- inherited
111 P_SALES_CREDIT_TYPE_ID => l_mgr_sct_id,
112
113 -- all other properties are left as null (they aren't required)
114 X_RETURN_STATUS => l_return_status,
115 X_MSG_COUNT => l_msg_count,
116 X_MSG_DATA => l_msg_data,
117 X_SALESREP_ID => l_srp_id);
118 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
122 x_srp_id := l_srp_id;
119 raise FND_API.G_EXC_ERROR;
120 end if;
121
123
124 -- assign the salesrep to the given compensation group for as long as
125 -- he is active
126 jtf_rs_group_members_pub.create_resource_group_members
127 (P_API_VERSION => 1.0,
128 P_GROUP_ID => p_comp_group_id,
129 P_GROUP_NUMBER => null, -- not needed... looked up from ID
130 P_RESOURCE_ID => l_resource_id,
131 P_RESOURCE_NUMBER => null, -- not needed... looked up from ID
132 X_RETURN_STATUS => l_return_status,
133 X_MSG_COUNT => l_msg_count,
134 X_MSG_DATA => l_msg_data,
135 X_GROUP_MEMBER_ID => l_group_member_id);
136
137 if l_return_status <> FND_API.G_RET_STS_SUCCESS then
138 -- pass on warning on msg stack that plan type couldn't be created
139 FND_MESSAGE.SET_NAME('CN', 'CN_SRP_GROUP_ERR');
140 FND_MSG_PUB.ADD;
141 RAISE FND_API.G_EXC_ERROR;
142 end if;
143
144 -- Standard check of p_commit.
145 IF FND_API.To_Boolean( p_commit ) THEN
146 COMMIT WORK;
147 END IF;
148
149 FND_MSG_PUB.Count_And_Get
150 (p_count => x_msg_count,
151 p_data => x_msg_data,
152 p_encoded => FND_API.G_FALSE);
153 EXCEPTION
154 WHEN FND_API.G_EXC_ERROR THEN
155 ROLLBACK TO Create_TBH;
156 x_return_status := FND_API.G_RET_STS_ERROR;
157 FND_MSG_PUB.Count_And_Get
158 (p_count => x_msg_count,
159 p_data => x_msg_data,
160 p_encoded => FND_API.G_FALSE);
161 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
162 ROLLBACK TO Create_TBH;
163 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
164 FND_MSG_PUB.Count_And_Get
165 (p_count => x_msg_count,
166 p_data => x_msg_data,
167 p_encoded => FND_API.G_FALSE);
168 WHEN OTHERS THEN
169 ROLLBACK TO Create_TBH;
170 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
171 IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
172 FND_MSG_PUB.Add_Exc_Msg(G_PKG_NAME, l_api_name);
173 END IF;
174 FND_MSG_PUB.Count_And_Get
175 (p_count => x_msg_count,
176 p_data => x_msg_data,
177 p_encoded => FND_API.G_FALSE);
178 END Create_TBH;
179
180 -- Given a manager's employee number, create the next sequence number
181 -- for a TBH under that manager
182 FUNCTION Get_TBH_Emp_Num
183 (p_mgr_emp_num IN VARCHAR2) RETURN NUMBER IS
184
185 tbh_pre varchar2(31) := p_mgr_emp_num || '-';
186 res number := 1;
187
188 cursor tbh_nums is
189 select to_number(replace(emp_num,tbh_pre,'')) n
190 from cn_srp_hr_data
191 where emp_num like tbh_pre || '%'
192 and emp_num not like tbh_pre || '%-%'
193 and category = 'TBH'
194 order by n;
195 BEGIN
196 for c in tbh_nums loop
197 if c.n > res then
198 return res;
199 end if;
200 res := res + 1;
201 end loop;
202 return res;
203 EXCEPTION
204 when others then
205 return 1;
206 END Get_TBH_Emp_Num;
207
208 END CN_ADD_TBH_PVT;
[Home] [Help]