[Home] [Help]
PACKAGE BODY: APPS.AST_ASN_INTEROP
Source
1 PACKAGE BODY AST_ASN_INTEROP as
2 /* $Header: astasnib.pls 120.2 2006/03/25 05:51:40 savadhan noship $ */
3 -- Start of Comments
4 -- Package name : AST_ASN_INTEROP
5 -- Purpose :
6 -- History :
7 -- 02-10-04 SUBABU Created
8 -- End of Comments
9 --
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'AST_ASN_INTEROP';
11 PROCEDURE RECONCILE_SALESCREDIT(
12 p_api_version_number IN NUMBER,
13 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
14 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
15 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
16 p_lead_id IN NUMBER,
17 X_Return_Status OUT NOCOPY VARCHAR2,
18 X_Msg_Count OUT NOCOPY NUMBER,
19 X_Msg_Data OUT NOCOPY VARCHAR2
20 ) IS
21
22 CURSOR CUR_SALESCREDIT IS
23 SELECT salesforce_id,salesgroup_id,lead_line_id,person_id,credit_percent
24 FROM as_sales_credits
25 WHERE lead_id = p_lead_id;
26
27 l_prev_lead_line_id NUMBER;
28 l_salesforce_id NUMBER;
29 l_sales_group_id NUMBER;
30 l_person_id NUMBER;
31 l_api_name CONSTANT VARCHAR2(30) := 'RECONCILE_SALESCREDIT';
32 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
33 BEGIN
34 -- Standard Start of API savepoint
35 SAVEPOINT RECONCILE_SALESCREDIT_PVT;
36
37 -- Initialize API return status to SUCCESS
38 x_return_status := FND_API.G_RET_STS_SUCCESS;
39
40 BEGIN
41 SELECT PERSON_ID,SALESFORCE_ID,SALES_GROUP_ID
42 INTO l_person_id,l_salesforce_id,l_sales_group_id
43 FROM AS_ACCESSES_ALL
44 WHERE LEAD_ID = p_lead_id
45 AND OWNER_FLAG = 'Y';
46 EXCEPTION
47 WHEN NO_DATA_FOUND OR TOO_MANY_ROWS THEN
48 FND_MESSAGE.Set_Name('AST', 'AST_ASN_INTEROP_MUST_OWNER');
49 FND_MSG_PUB.ADD;
50 RAISE FND_API.G_EXC_ERROR;
51 END;
52
53 IF nvl(x_return_status,'N') <> 'E' THEN
54 -- Debug Message
55 IF l_debug THEN
56 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
57 'Sales Force Id: ' || l_salesforce_id || ' Sales Group Id :' || l_sales_group_id ||' Person id :'||l_person_id );
58 END IF;
59 UPDATE AS_SALES_CREDITS
60 SET SALESFORCE_ID = l_salesforce_id,
61 SALESGROUP_ID = l_sales_group_id,
62 PERSON_ID = l_person_id,
63 LAST_UPDATE_DATE = SYSDATE
64 WHERE LEAD_ID = P_LEAD_ID
65 AND (SALESFORCE_ID <> l_salesforce_id
66 or nvl(SALESGROUP_ID,0) <> nvl(l_sales_group_id,0))
67 AND NVL(DEFAULTED_FROM_OWNER_FLAG,'N') = 'Y';
68
69 UPDATE AS_ACCESSES_ALL acc
70 SET object_version_number = nvl(object_version_number,0) + 1,
71 acc.team_leader_flag = 'Y'
72 WHERE acc.LEAD_ID = p_lead_id
73 and team_leader_flag = 'N'
74 and exists
75 (
76 select 'x'
77 from as_sales_credits
78 where lead_id=acc.lead_id
79 and salesforce_id=acc.salesforce_id
80 and salesgroup_id =acc.sales_group_id
81 );
82 END IF;
83
84 IF FND_API.to_Boolean( p_commit )
85 THEN
86 COMMIT WORK;
87 END IF;
88
89 EXCEPTION
90 WHEN FND_API.G_EXC_ERROR THEN
91 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
92 P_API_NAME => L_API_NAME
93 ,P_PKG_NAME => G_PKG_NAME
94 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
95 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
96 ,X_MSG_COUNT => X_MSG_COUNT
97 ,X_MSG_DATA => X_MSG_DATA
98 ,X_RETURN_STATUS => X_RETURN_STATUS);
99
100 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
101 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
102 P_API_NAME => L_API_NAME
103 ,P_PKG_NAME => G_PKG_NAME
104 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
105 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
106 ,X_MSG_COUNT => X_MSG_COUNT
107 ,X_MSG_DATA => X_MSG_DATA
108 ,X_RETURN_STATUS => X_RETURN_STATUS);
109
110 WHEN OTHERS THEN
111 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
112 P_API_NAME => L_API_NAME
113 ,P_PKG_NAME => G_PKG_NAME
114 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
115 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
116 ,X_MSG_COUNT => X_MSG_COUNT
117 ,X_MSG_DATA => X_MSG_DATA
118 ,X_RETURN_STATUS => X_RETURN_STATUS);
119 END RECONCILE_SALESCREDIT;
120
121 PROCEDURE CHECK_SALES_STAGE(
122 p_api_version_number IN NUMBER,
123 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
124 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
125 p_sales_lead_id IN NUMBER,
126 X_sales_stage_id OUT NOCOPY NUMBER,
127 X_sales_methodology_id OUT NOCOPY NUMBER,
128 X_Return_Status OUT NOCOPY VARCHAR2,
129 X_Msg_Count OUT NOCOPY NUMBER,
130 X_Msg_Data OUT NOCOPY VARCHAR2)
131 IS
132 cursor c_lead(p_sales_lead_id NUMBER) is
133 select SALES_METHODOLOGY_ID, SALES_STAGE_ID
134 from as_sales_leads
135 where sales_lead_id = p_sales_lead_id;
136
137 cursor c_sales_stage(p_sales_stage_id NUMBER) is
138 select applicability
139 from as_sales_stages_all_vl
140 where sales_stage_id = p_sales_stage_id;
141
142 cursor c_first_sales_stage(p_sales_method_id NUMBER) is
143 SELECT stage.sales_stage_id
144 FROM as_sales_stages_all_vl stage, as_sales_meth_stage_map map1
145 WHERE stage.sales_stage_id = map1.sales_stage_id
146 AND nvl(stage.applicability,'BOTH') in ('OPPORTUNITY', 'BOTH')
147 AND nvl(stage.ENABLED_FLAG,'Y') = 'Y'
148 AND trunc(sysdate) between trunc(nvl(START_DATE_ACTIVE,sysdate))
149 AND trunc(nvl(END_DATE_ACTIVE,sysdate))
150 AND map1.sales_methodology_id = p_sales_method_id
151 ORDER BY STAGE_SEQUENCE;
152
153 l_sales_methodology_id NUMBER;
154 l_sales_stage_id NUMBER;
155 l_applicability VARCHAR2(100);
156 l_api_name CONSTANT VARCHAR2(40) := 'CHECK_SALES_STAGE';
157 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
158 l_last_update_date date;
159 BEGIN
160 -- Standard Start of API savepoint
161 SAVEPOINT CHECK_SALES_STAGE_PVT;
162 -- Initialize API return status to SUCCESS
163 x_return_status := FND_API.G_RET_STS_SUCCESS;
164 -- Initialize message list if p_init_msg_list is set to TRUE.
165 IF FND_API.to_Boolean( p_init_msg_list )
166 THEN
167 FND_MSG_PUB.initialize;
168 END IF;
169 OPEN c_lead(p_sales_lead_id);
170 FETCH c_lead INTO l_sales_methodology_id,l_sales_stage_id;
171 CLOSE c_lead;
172
173 IF l_sales_methodology_id IS NOT NULL THEN
174 IF l_sales_stage_id IS NOT NULL THEN
175 OPEN c_sales_stage(l_sales_stage_id);
176 FETCH c_sales_stage INTO l_applicability;
177 CLOSE c_sales_stage;
178 END IF;
179 IF l_sales_stage_id IS NULL or
180 nvl(l_applicability,'BOTH') NOT IN ('OPPORTUNITY', 'BOTH') THEN
181 OPEN c_first_sales_stage(l_sales_methodology_id);
182 FETCH c_first_sales_stage INTO l_sales_stage_id;
183 IF c_first_sales_stage%NOTFOUND THEN
184 x_return_status :='E';
185 CLOSE c_first_sales_stage;
186 FND_MESSAGE.Set_Name('AST', 'AST_STAGE_NOT_SETUP_FOR_METH');
187 FND_MSG_PUB.ADD;
188 RAISE FND_API.G_EXC_ERROR;
189 END IF;
190 CLOSE c_first_sales_stage;
191 END IF;
192 END IF;
193 X_sales_stage_id := l_sales_stage_id;
194 X_sales_methodology_id := l_sales_methodology_id;
195 EXCEPTION
196 WHEN FND_API.G_EXC_ERROR THEN
197 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
198 P_API_NAME => L_API_NAME
199 ,P_PKG_NAME => G_PKG_NAME
200 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
201 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
202 ,X_MSG_COUNT => X_MSG_COUNT
203 ,X_MSG_DATA => X_MSG_DATA
204 ,X_RETURN_STATUS => X_RETURN_STATUS);
205
206 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
207 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
208 P_API_NAME => L_API_NAME
209 ,P_PKG_NAME => G_PKG_NAME
210 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
211 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
212 ,X_MSG_COUNT => X_MSG_COUNT
213 ,X_MSG_DATA => X_MSG_DATA
214 ,X_RETURN_STATUS => X_RETURN_STATUS);
215
216 WHEN OTHERS THEN
217 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
218 P_API_NAME => L_API_NAME
219 ,P_PKG_NAME => G_PKG_NAME
220 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
221 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
222 ,X_MSG_COUNT => X_MSG_COUNT
223 ,X_MSG_DATA => X_MSG_DATA
224 ,X_RETURN_STATUS => X_RETURN_STATUS);
225 END CHECK_SALES_STAGE;
226
227 PROCEDURE RECONCILE_SALESMETHODOLOGY(
228 p_api_version_number IN NUMBER,
229 p_init_msg_list IN VARCHAR2 DEFAULT FND_API.G_FALSE,
230 p_commit IN VARCHAR2 DEFAULT FND_API.G_FALSE,
231 p_validation_level IN NUMBER DEFAULT FND_API.G_VALID_LEVEL_FULL,
232 p_lead_id IN NUMBER,
233 p_sales_stage_id IN NUMBER,
234 p_sales_methodology_id IN NUMBER,
235 P_Admin_Flag IN VARCHAR2 := FND_API.G_FALSE,
236 P_Admin_Group_Id IN NUMBER,
237 P_Identity_Salesforce_Id IN NUMBER := NULL,
238 P_identity_salesgroup_id IN NUMBER := NULL,
239 P_profile_tbl IN AS_UTILITY_PUB.PROFILE_TBL_TYPE,
240 X_Return_Status OUT NOCOPY VARCHAR2,
241 X_Msg_Count OUT NOCOPY NUMBER,
242 X_Msg_Data OUT NOCOPY VARCHAR2
243 ) is
244
245
246 cursor c_opp(p_lead_id NUMBER) is
247 select last_update_date
248 from as_leads_all
249 where lead_id = p_lead_id;
250
251 l_api_name CONSTANT VARCHAR2(40) := 'RECONCILE_SALESMETHODOLOGY';
252 header_rec AS_OPPORTUNITY_PUB.Header_Rec_Type;
253 l_debug BOOLEAN := FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW);
254 l_lead_id NUMBER;
255 l_last_update_date date;
256 BEGIN
257 -- Standard Start of API savepoint
258 SAVEPOINT RECONCILE_SALESMETHODOLOGY_PVT;
259 -- Initialize API return status to SUCCESS
260 x_return_status := FND_API.G_RET_STS_SUCCESS;
261
262 OPEN c_opp(p_lead_id);
263 FETCH c_opp into l_last_update_date;
264 CLOSE c_opp;
265 IF p_sales_methodology_id IS NOT NULL THEN
266 header_rec.sales_stage_id := p_sales_stage_id;
267 header_rec.Sales_Methodology_Id := p_sales_methodology_id;
268 END IF;
269 header_rec.lead_id := p_lead_id;
270 header_rec.owner_salesforce_id := P_Identity_Salesforce_Id;
271 header_rec.owner_sales_group_id := P_identity_salesgroup_id;
272 header_rec.last_update_date := l_last_update_date;
273 IF l_debug THEN
274 AS_UTILITY_PVT.Debug_Message(FND_MSG_PUB.G_MSG_LVL_DEBUG_LOW,
275 'Before calling Update opp Header Lead id : ' || p_lead_id ||' Sales Force id :'|| P_Identity_Salesforce_Id ||
276 ' Sales Group Id :' || P_identity_salesgroup_id ||
277 ' Sales Methodology id :'||p_sales_methodology_id );
278 END IF;
279 AS_OPPORTUNITY_PUB.Update_Opp_header
280 (
281 p_api_version_number => p_api_version_number,
282 p_init_msg_list => p_init_msg_list,
283 p_commit => p_commit,
284 p_validation_level => p_validation_level,
285 p_header_rec => header_rec,
286 p_check_access_flag => 'Y',
287 p_admin_flag => p_admin_flag,
288 p_admin_group_id => P_Admin_Group_Id,
289 p_identity_salesforce_id => P_Identity_Salesforce_Id,
290 p_profile_tbl => P_profile_tbl,
291 x_return_status => x_return_status,
292 p_partner_cont_party_id => NULL,
293 x_msg_count => x_msg_count,
294 x_msg_data => x_msg_data,
295 x_lead_id => l_lead_id
296 );
297 IF x_return_status <> FND_API.G_RET_STS_SUCCESS THEN
298 RAISE FND_API.G_EXC_ERROR;
299 END IF;
300 EXCEPTION
301 WHEN FND_API.G_EXC_ERROR THEN
302 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
303 P_API_NAME => L_API_NAME
304 ,P_PKG_NAME => G_PKG_NAME
305 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
306 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
307 ,X_MSG_COUNT => X_MSG_COUNT
308 ,X_MSG_DATA => X_MSG_DATA
309 ,X_RETURN_STATUS => X_RETURN_STATUS);
310
311 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
312 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
313 P_API_NAME => L_API_NAME
314 ,P_PKG_NAME => G_PKG_NAME
315 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
316 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
317 ,X_MSG_COUNT => X_MSG_COUNT
318 ,X_MSG_DATA => X_MSG_DATA
319 ,X_RETURN_STATUS => X_RETURN_STATUS);
320
321 WHEN OTHERS THEN
322 AS_UTILITY_PVT.HANDLE_EXCEPTIONS(
323 P_API_NAME => L_API_NAME
324 ,P_PKG_NAME => G_PKG_NAME
325 ,P_EXCEPTION_LEVEL => AS_UTILITY_PVT.G_EXC_OTHERS
326 ,P_PACKAGE_TYPE => AS_UTILITY_PVT.G_PVT
327 ,X_MSG_COUNT => X_MSG_COUNT
328 ,X_MSG_DATA => X_MSG_DATA
329 ,X_RETURN_STATUS => X_RETURN_STATUS);
330 END RECONCILE_SALESMETHODOLOGY;
331
332 END AST_ASN_INTEROP;