[Home] [Help]
PACKAGE BODY: APPS.ASO_SALES_CREDIT_PVT
Source
4 -- Package name : ASO_SALES_CREDIT_PVT
1 PACKAGE BODY ASO_SALES_CREDIT_PVT as
2 /* $Header: asovscab.pls 120.6 2005/12/16 11:20:57 skulkarn ship $ */
3 -- Start of Comments
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10
11 G_PKG_NAME CONSTANT VARCHAR2(30) := 'ASO_SALES_CREDIT_PVT';
12 G_USER_ID NUMBER := FND_GLOBAL.USER_ID;
13 G_LOGIN_ID NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
14
15 PROCEDURE Allocate_Sales_Credits
16 (
17 P_Api_Version_Number IN NUMBER,
18 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
19 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
20 p_control_rec IN ASO_QUOTE_PUB.SALES_ALLOC_CONTROL_REC_TYPE
21 := ASO_QUOTE_PUB.G_MISS_SALES_ALLOC_CONTROL_REC,
22 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
23 X_Qte_Header_Rec OUT NOCOPY /* file.sql.39 change */ ASO_QUOTE_PUB.Qte_Header_Rec_Type,
24 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
25 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
26 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
27 IS
28
29 l_auto_sales_cred_prof VARCHAR2(50) := NVL(FND_PROFILE.Value('ASO_AUTO_SALES_CREDIT'),'NONE');
30 l_auto_sales_team_prof VARCHAR2(50) := NVL(FND_PROFILE.Value('ASO_AUTO_TEAM_ASSIGN'),'NONE');
31
32 Leave_Proc EXCEPTION;
33
34 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
35 lx_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
36 lx_return_status VARCHAR2(1);
37 l_ordered VARCHAR2(1) := 'N';
38 l_line_exists VARCHAR2(1) := 'N';
39 l_sreps_count NUMBER := 0;
40 l_api_name CONSTANT VARCHAR2 ( 30 ) := 'Allocate_Sales_Credits';
41 l_api_version_number CONSTANT NUMBER := 1.0;
42
43 CURSOR C_Check_Qte_Ordered (l_status NUMBER) IS
44 SELECT 'Y'
45 FROM ASO_QUOTE_STATUSES_B
46 WHERE Quote_Status_Id = l_status
47 AND Status_Code = 'ORDER SUBMITTED';
48
49 CURSOR C_Check_Qte_Line (l_qte_hdr NUMBER) IS
50 SELECT 'Y'
51 FROM ASO_QUOTE_LINES_ALL
52 WHERE Quote_Header_Id = l_qte_hdr;
53
54 CURSOR C_Get_Sreps_Count (l_qte_num NUMBER) IS
55 SELECT Count (Resource_Id)
56 FROM ASO_QUOTE_ACCESSES A
57 WHERE A.Quote_Number = l_qte_num
58 AND A.Role_Id IS NOT NULL
59 AND EXISTS
60 ( SELECT B.Resource_Id
61 /* FROM JTF_RS_SRP_VL B */ --Commented Code Yogeshwar (MOAC)
62 FROM JTF_RS_SALESREPS_MO_V B --New Code Yogeshwar (MOAC)
63 WHERE B.Resource_Id = A.Resource_Id
64 AND NVL(B.status,'A') = 'A'
65 AND nvl(trunc(B.start_date_active), trunc(sysdate)) <= trunc(sysdate)
66 AND nvl(trunc(B.end_date_active), trunc(sysdate)) >= trunc(sysdate));
67 --Commented Code Start Yogeshwar (MOAC)
68 /*
69 AND NVL(B.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,
70 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
71 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,
72 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99));
73 */
74 --Commented Code End Yogeshwar (MOAC)
75
76
77 BEGIN
78
79 aso_debug_pub.g_debug_flag := nvl(fnd_profile.value('ASO_ENABLE_DEBUG'),'N');
80
81 -- Standard Start of API savepoint
82 SAVEPOINT ALLOCATE_SALES_CREDITS_PVT;
83
84 -- Standard call to check for call compatibility.
85 IF NOT FND_API.Compatible_API_Call ( l_api_version_number,
86 1.0,
87 l_api_name,
88 G_PKG_NAME)
89 THEN
90 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
91 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
92 END IF;
93
94 -- Initialize message list if p_init_msg_list is set to TRUE.
95 IF FND_API.to_Boolean( p_init_msg_list )
96 THEN
97 FND_MSG_PUB.initialize;
98 END IF;
99
100 -- Initialize API return status to SUCCESS
101 x_return_status := FND_API.G_RET_STS_SUCCESS;
102
103 --
104 -- API body
105 --
106
107 IF aso_debug_pub.g_debug_flag = 'Y' THEN
108 aso_debug_pub.add('Allocate_Sales_Credits: Begin ',1,'Y');
109 aso_debug_pub.add('Allocate_Sales_Credits: l_auto_sales_cred_prof: '||l_auto_sales_cred_prof,1,'N');
110 aso_debug_pub.add('Allocate_Sales_Credits: l_auto_sales_team_prof: '||l_auto_sales_team_prof,1,'N');
111 aso_debug_pub.add('Allocate_Sales_Credits: p_control_rec.submit_quote_flag: '||p_control_rec.submit_quote_flag,1,'N');
112 aso_debug_pub.add('Allocate_Sales_Credits: p_qte_header_rec.quote_header_id: '||p_qte_header_rec.quote_header_id,1,'N');
116 -- Basic Validations
113 aso_debug_pub.add('Allocate_Sales_Credits: p_qte_header_rec.last_update_date: '||p_qte_header_rec.last_update_date,1,'N');
114 END IF;
115
117 -- Check is Auto Sales Credit Alloc Prof is valid
118 IF l_auto_sales_cred_prof <> 'FULL' AND l_auto_sales_cred_prof <> 'PARTIAL' THEN
119 RAISE Leave_Proc;
120 END IF;
121
122 -- Check if security profiles are set
123 IF (NVL(FND_PROFILE.Value('ASO_API_ENABLE_SECURITY'),'N') = 'N') THEN
124
125 IF aso_debug_pub.g_debug_flag = 'Y' THEN
126 aso_debug_pub.add('API_Enable_Sec is N ',1,'Y');
127 END IF;
128
129 RAISE Leave_Proc;
130
131 END IF;
132
133 IF p_control_rec.submit_quote_flag = FND_API.G_TRUE AND l_auto_sales_cred_prof <> 'FULL' THEN
134 RAISE Leave_Proc;
135 END IF;
136
137 l_qte_header_rec := ASO_UTILITY_PVT.Query_Header_Row(p_qte_header_rec.quote_header_id);
138
139 -- Check Whether record has been changed
140 IF (l_qte_header_rec.last_update_date IS NULL OR
141 l_qte_header_rec.last_update_date = FND_API.G_MISS_DATE) THEN
142 x_return_status := FND_API.G_RET_STS_ERROR;
143 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
144 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_COLUMN');
145 FND_MESSAGE.Set_Token('COLUMN', 'Last_Update_Date', FALSE);
146 FND_MSG_PUB.ADD;
147 END IF;
148 RAISE FND_API.G_EXC_ERROR;
149 END IF;
150
151
152 IF (p_qte_header_rec.last_update_date IS NOT NULL AND
153 p_qte_header_rec.last_update_date <> FND_API.G_MISS_DATE) AND
154 (l_qte_header_rec.last_update_date <> p_qte_header_rec.last_update_date) THEN
155 x_return_status := FND_API.G_RET_STS_ERROR;
156 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
157 FND_MESSAGE.Set_Name('ASO', 'ASO_API_RECORD_CHANGED');
158 FND_MESSAGE.Set_Token('INFO', 'quote', FALSE);
159 FND_MSG_PUB.ADD;
160 END IF;
161 RAISE FND_API.G_EXC_ERROR;
162 END IF;
163
164 -- Check if a concurrent lock exists
165 ASO_CONC_REQ_INT.Lock_Exists(
166 p_quote_header_id => p_qte_header_rec.quote_header_id,
167 x_status => lx_return_status);
168
169 IF (lx_return_status = FND_API.G_TRUE) THEN
170 x_return_status := FND_API.G_RET_STS_ERROR;
171 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
172 FND_MESSAGE.Set_Name('ASO', 'ASO_CONC_REQUEST_RUNNING');
173 FND_MSG_PUB.ADD;
174 END IF;
175 RAISE FND_API.G_EXC_ERROR;
176 END IF;
177
178 IF aso_debug_pub.g_debug_flag = 'Y' THEN
179 aso_debug_pub.add('Allocate_Sales_Credits: l_qte_header_rec.Resource_Id: '||l_qte_header_rec.Resource_Id,1,'N');
180 aso_debug_pub.add('Allocate_Sales_Credits: l_qte_header_rec.Quote_Status_Id: '||l_qte_header_rec.Quote_Status_Id,1,'N');
181 aso_debug_pub.add('Allocate_Sales_Credits: l_qte_header_rec.Pricing_Status_Indicator: '||l_qte_header_rec.Pricing_Status_Indicator,1,'N');
182 END IF;
183 -- Check if Primary resource exists in the quote
184 IF l_qte_header_rec.Resource_Id IS NULL OR l_qte_header_rec.Resource_Id = FND_API.G_MISS_NUM THEN
185 RAISE Leave_Proc;
186 END IF;
187
188 -- Check if Quote is ordered
189 OPEN C_Check_Qte_Ordered (l_qte_header_rec.Quote_Status_Id);
190 FETCH C_Check_Qte_Ordered INTO l_ordered;
191 CLOSE C_Check_Qte_Ordered;
192
193 IF aso_debug_pub.g_debug_flag = 'Y' THEN
194 aso_debug_pub.add('Allocate_Sales_Credits: l_ordered: '||l_ordered,1,'N');
195 END IF;
196 IF l_ordered IS NOT NULL AND l_ordered = 'Y' THEN
197 x_return_status := FND_API.G_RET_STS_ERROR;
198 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
199 FND_MESSAGE.Set_Name('ASO', 'ASO_API_ORDERED_STATUS_TRANS');
200 FND_MSG_PUB.ADD;
201 END IF;
202 RAISE FND_API.G_EXC_ERROR;
203 END IF;
204
205 -- Check if pricing status is Complete
206 IF l_qte_header_rec.Pricing_Status_Indicator IS NOT NULL AND
207 l_qte_header_rec.Pricing_Status_Indicator <> 'C' THEN
208 x_return_status := FND_API.G_RET_STS_ERROR;
209 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
210 FND_MESSAGE.Set_Name('ASO', 'ASO_PRC_INCOMPLETE');
211 FND_MSG_PUB.ADD;
212 END IF;
213 RAISE FND_API.G_EXC_ERROR;
214 END IF;
215
216 -- Check if Atleast one line exists for the quote
217 OPEN C_Check_Qte_Line (P_Qte_Header_Rec.Quote_Header_Id);
218 FETCH C_Check_Qte_Line INTO l_line_exists;
219 CLOSE C_Check_Qte_Line;
220
221 IF aso_debug_pub.g_debug_flag = 'Y' THEN
222 aso_debug_pub.add('Allocate_Sales_Credits: l_line_exists: '||l_line_exists,1,'N');
223 END IF;
224 IF l_line_exists IS NULL OR l_line_exists <> 'Y' THEN
225 x_return_status := FND_API.G_RET_STS_ERROR;
226 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
227 FND_MESSAGE.Set_Name('ASO', 'ASO_NO_QUOTE_LINES');
228 FND_MSG_PUB.ADD;
229 END IF;
230 RAISE FND_API.G_EXC_ERROR;
231 END IF;
232
233 -- Call Sales Team Assign if required
234 IF l_auto_sales_team_prof <> 'FULL' AND l_auto_sales_team_prof <> 'PARTIAL' THEN
235 x_return_status := FND_API.G_RET_STS_ERROR;
236 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
237 FND_MESSAGE.Set_Name('ASO', 'ASO_S_TEAM_PROF_NOT_SET');
238 FND_MSG_PUB.ADD;
239 END IF;
240 RAISE FND_API.G_EXC_ERROR;
241 END IF;
242
246 (
243 IF P_Control_Rec.Submit_Quote_Flag = FND_API.G_FALSE THEN
244
245 ASO_SALES_TEAM_PVT.Assign_Sales_Team
247 P_Init_Msg_List => FND_API.G_FALSE,
248 P_Commit => FND_API.G_FALSE,
249 p_Qte_Header_Rec => p_qte_header_rec,
250 P_Operation => 'UPDATE',
251 x_Qte_Header_Rec => lx_qte_header_rec,
252 x_return_status => x_return_status,
253 x_msg_count => x_msg_count,
254 x_msg_data => x_msg_data
255 );
256 IF aso_debug_pub.g_debug_flag = 'Y' THEN
257 aso_debug_pub.add('Allocate_Sales_Credits: Assign_Sales_Team:x_return_status: '||x_return_status,1,'N');
258 END IF;
259 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
260 RAISE FND_API.G_EXC_ERROR;
261 END IF;
262 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
263 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
264 END IF;
265
266 END IF;
267
268 -- Throw error if no valid salesreps were found
269 OPEN C_Get_Sreps_Count (l_qte_header_rec.Quote_Number);
270 FETCH C_Get_Sreps_Count INTO l_sreps_count;
271
272 IF aso_debug_pub.g_debug_flag = 'Y' THEN
273 aso_debug_pub.add('Allocate_Sales_Credits: l_sreps_count: '||l_sreps_count,1,'N');
274 END IF;
275 IF C_Get_Sreps_Count%NOTFOUND OR l_sreps_count = 0 THEN
276 x_return_status := FND_API.G_RET_STS_ERROR;
277 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
278 FND_MESSAGE.Set_Name('ASO', 'ASO_NO_SALES_CREDIT_RECEIVERS');
279 FND_MSG_PUB.ADD;
280 END IF;
281 CLOSE C_Get_Sreps_Count;
282 RAISE FND_API.G_EXC_ERROR;
283 END IF;
284 IF aso_debug_pub.g_debug_flag = 'Y' THEN
285 aso_debug_pub.add('Allocate_Sales_Credits: l_sreps_count: '||l_sreps_count,1,'N');
286 END IF;
287 CLOSE C_Get_Sreps_Count;
288
289 IF aso_debug_pub.g_debug_flag = 'Y' THEN
290 aso_debug_pub.add('Allocate_Sales_Credits: Before Get_Credits ',1,'N');
291 END IF;
292 -- Initiate Temp Tables and Call CN API
293 ASO_SALES_CREDIT_PVT.Get_Credits
294 (
295 P_Api_Version_Number => 1.0,
296 P_Init_Msg_List => FND_API.G_FALSE,
297 P_Commit => FND_API.G_FALSE,
298 p_Qte_Header_Rec => l_qte_header_rec,
299 x_return_status => x_return_status,
300 x_msg_count => x_msg_count,
301 x_msg_data => x_msg_data
302 );
303 IF aso_debug_pub.g_debug_flag = 'Y' THEN
304 aso_debug_pub.add('Allocate_Sales_Credits: After Get_Credits:x_return_status: '||x_return_status,1,'N');
305 END IF;
306 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
307 RAISE FND_API.G_EXC_ERROR;
308 END IF;
309 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
310 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
311 END IF;
312
313
314 IF aso_debug_pub.g_debug_flag = 'Y' THEN
315 aso_debug_pub.add('Allocate_Sales_Credits: Before Update Qte Hdr ',1,'N');
316 END IF;
317 -- Update quote header with the credit_update_date
318 UPDATE ASO_QUOTE_HEADERS_ALL
319 SET Credit_Update_date = sysdate,
320 last_update_date = sysdate,
321 last_updated_by = fnd_global.user_id,
322 last_update_login = fnd_global.conc_login_id,
323 object_version_number = object_version_number+1
324 WHERE Quote_Header_Id = l_Qte_Header_Rec.Quote_Header_Id
325 RETURNING quote_header_id, last_update_date, credit_update_date, object_version_number
326 INTO x_qte_header_rec.Quote_Header_Id, x_qte_header_rec.Last_Update_Date,
327 x_qte_header_rec.credit_update_date, x_qte_header_rec.object_version_number;
328
329 IF aso_debug_pub.g_debug_flag = 'Y' THEN
330 aso_debug_pub.add('Allocate_Sales_Credits: After Update Qte Hdr ',1,'N');
331 aso_debug_pub.add('Allocate_Sales_Credits: End ',1,'Y');
332 END IF;
333
334 -- Change START
335 -- Release 12 TAP Changes
336 -- Girish Sachdeva 8/30/2005
337 -- Adding the call to insert record in the ASO_CHANGED_QUOTES
338
339 IF aso_debug_pub.g_debug_flag = 'Y' THEN
340 aso_debug_pub.add('ASO_SALES_CREDIT_PVT.Allocate_Sales_Credits : Calling ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES, quote number : ' || l_qte_header_rec.Quote_Number, 1, 'Y');
341 END IF;
342
343 -- Call to insert record in ASO_CHANGED_QUOTES
344 ASO_UTILITY_PVT.UPDATE_CHANGED_QUOTES(l_qte_header_rec.Quote_Number);
345
346 -- Change END
347
348
349 -- Standard call to get message count and if count is 1, get message info.
350 FND_MSG_PUB.Count_And_Get
351 ( p_count => x_msg_count,
352 p_data => x_msg_data
353 );
354
355 EXCEPTION
356
357 WHEN Leave_Proc THEN
358 X_Qte_Header_Rec := P_Qte_Header_Rec;
359
360 WHEN FND_API.G_EXC_ERROR THEN
361 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
362 P_API_NAME => L_API_NAME,
363 P_PKG_NAME => G_PKG_NAME,
364 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
365 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
366 P_SQLCODE => SQLCODE,
367 P_SQLERRM => SQLERRM,
368 X_MSG_COUNT => X_MSG_COUNT,
369 X_MSG_DATA => X_MSG_DATA,
370 X_RETURN_STATUS => X_RETURN_STATUS
371 );
372
373 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
374 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
375 P_API_NAME => L_API_NAME,
379 P_SQLCODE => SQLCODE,
376 P_PKG_NAME => G_PKG_NAME,
377 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
378 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
380 P_SQLERRM => SQLERRM,
381 X_MSG_COUNT => X_MSG_COUNT,
382 X_MSG_DATA => X_MSG_DATA,
383 X_RETURN_STATUS => X_RETURN_STATUS
384 );
385
386 WHEN OTHERS THEN
387 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
388 P_API_NAME => L_API_NAME,
389 P_PKG_NAME => G_PKG_NAME,
390 P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS,
391 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
392 P_SQLCODE => SQLCODE,
393 P_SQLERRM => SQLERRM,
394 X_MSG_COUNT => X_MSG_COUNT,
395 X_MSG_DATA => X_MSG_DATA,
396 X_RETURN_STATUS => X_RETURN_STATUS
397 );
398
399
400 END Allocate_Sales_Credits;
401
402
403 PROCEDURE Get_Credits
404 (
405 P_Api_Version_Number IN NUMBER := 1.0,
406 P_Init_Msg_List IN VARCHAR2 := FND_API.G_FALSE,
407 P_Commit IN VARCHAR2 := FND_API.G_FALSE,
408 P_Qte_Header_Rec IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
409 X_Return_Status OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
410 X_Msg_Count OUT NOCOPY /* file.sql.39 change */ NUMBER,
411 X_Msg_Data OUT NOCOPY /* file.sql.39 change */ VARCHAR2)
412
413 IS
414
415 l_cred_upd_lines_prof VARCHAR2(50) := FND_PROFILE.Value('ASO_SALES_CREDIT_UPDATE');
416
417 l_quota_id NUMBER;
418 l_non_quota_id NUMBER;
419 l_total_rev NUMBER := 0;
420 l_Line_rev NUMBER := 0;
421 l_hdr_total NUMBER;
422 l_line_total NUMBER;
423 l_credit_diff NUMBER;
424 l_cred_line_diff NUMBER;
425 l_batch_id NUMBER;
426 l_PSRep NUMBER;
427 l_api_name CONSTANT VARCHAR2 ( 30 ) := 'Get_Credits';
428 l_api_version_number CONSTANT NUMBER := 1.0;
429
430 CURSOR C_Get_Batch_Id IS
431 SELECT CN_SCA_BATCH_S.NextVal
432 FROM DUAL;
433
434 CURSOR C_Get_Acct_Info (l_acct_id NUMBER) IS
435 SELECT Account_Number
436 FROM HZ_CUST_ACCOUNTS
437 WHERE Cust_Account_Id = l_acct_id;
438
439 CURSOR C_Get_Party_Info (l_party_id NUMBER) IS
440 SELECT Party_Name
441 FROM HZ_PARTIES
442 WHERE Party_Id = l_party_id;
443
444 CURSOR C_Get_Party_Site_Info (l_party_site NUMBER) IS
445 SELECT UPPER(B.city) City, UPPER(B.county) County, UPPER(B.state)State, UPPER(B.province) Province, B.postal_code, B.country
446 FROM HZ_PARTY_SITES A, HZ_LOCATIONS B
447 WHERE A.Location_Id = B.Location_Id
448 AND A.party_site_id = l_party_site;
449
450 CURSOR C_Get_Cust_Cont_Info (l_party_id NUMBER) IS
451 SELECT Phone_Area_Code
452 FROM HZ_CONTACT_POINTS
453 WHERE Owner_Table_Id = l_party_id
454 AND Owner_Table_Name = 'HZ_PARTIES'
455 AND Contact_Point_Type = 'PHONE'
456 AND Status = 'A'
457 AND Primary_Flag = 'Y';
458
459 CURSOR C_Get_Quota_Credit_Type IS
460 SELECT Sales_Credit_Type_Id
461 FROM OE_SALES_CREDIT_TYPES
462 WHERE Quota_Flag = 'Y';
463
464 CURSOR C_Get_Non_Quota_Credit_Type IS
465 SELECT Sales_Credit_Type_Id
466 FROM OE_SALES_CREDIT_TYPES
467 WHERE Quota_Flag = 'N';
468
469 CURSOR C_Get_Total_Revenue (l_batch NUMBER) IS
470 SELECT SUM (NVL(A.Allocation_Percentage,0))
471 FROM CN_SCA_LINES_OUTPUT_GTT A, CN_SCA_HEADERS_INTERFACE_GTT B
472 WHERE A.SCA_Batch_Id = l_batch
473 AND A.Revenue_Type = 'REVENUE'
474 AND B.SCA_Batch_Id = l_batch
475 AND B.SCA_Headers_Interface_Id = A.SCA_Headers_Interface_Id
476 AND B.Source_Line_Id IS NULL;
477
478 CURSOR C_Get_Line_Revenue (l_batch NUMBER, l_line_id NUMBER) IS
479 SELECT SUM (NVL(A.Allocation_Percentage,0))
480 FROM CN_SCA_LINES_OUTPUT_GTT A, CN_SCA_HEADERS_INTERFACE_GTT B
481 WHERE A.SCA_Batch_Id = l_batch
482 AND A.Revenue_Type = 'REVENUE'
483 AND B.SCA_Batch_Id = l_batch
484 AND B.SCA_Headers_Interface_Id = A.SCA_Headers_Interface_Id
485 AND B.Source_Line_Id = l_line_id;
486
487 CURSOR C_Get_Hdr_Total (l_batch NUMBER) IS
488 SELECT SUM (NVL(Allocation_Percentage,0))
489 FROM CN_SCA_LINES_OUTPUT_GTT A, CN_SCA_HEADERS_INTERFACE_GTT B
490 WHERE A.SCA_Batch_Id = l_batch
491 AND B.SCA_Batch_Id = l_batch
492 AND B.SCA_Headers_Interface_Id = A.SCA_Headers_Interface_Id
493 AND B.Source_Line_Id IS NULL;
494
495 CURSOR C_Get_Line_Total (l_batch NUMBER) IS
496 SELECT SUM (NVL(Allocation_Percentage,0))
497 FROM CN_SCA_LINES_OUTPUT_GTT A, CN_SCA_HEADERS_INTERFACE_GTT B
498 WHERE A.SCA_Batch_Id = l_batch
499 AND B.SCA_Batch_Id = l_batch
500 AND B.SCA_Headers_Interface_Id = A.SCA_Headers_Interface_Id
501 AND B.Source_Line_Id IS NOT NULL;
502
503 CURSOR C_PSRep_Credit (l_qte_hdr NUMBER, l_res NUMBER, l_quota NUMBER) IS
504 SELECT Resource_Id
505 FROM ASO_SALES_CREDITS
506 WHERE Resource_Id = l_res
507 AND Quote_Header_Id = l_qte_Hdr
508 AND Sales_Credit_Type_Id = l_quota
509 AND Quote_Line_Id IS NULL;
510
511 CURSOR C_PSRep_Credit_Line (l_qte_hdr NUMBER, l_res NUMBER, l_quota NUMBER, l_qte_line NUMBER) IS
512 SELECT Resource_Id
516 AND Sales_Credit_Type_Id = l_quota
513 FROM ASO_SALES_CREDITS
514 WHERE Resource_Id = l_res
515 AND Quote_Header_Id = l_qte_Hdr
517 AND Quote_Line_Id = l_qte_line;
518
519 CURSOR C_Get_Line (l_qte_hdr NUMBER) IS
520 SELECT Quote_Line_Id
521 FROM ASO_QUOTE_LINES_ALL
522 WHERE Quote_Header_Id = l_qte_hdr;
523
524 CURSOR C_Hd_Inter IS
525 SELECT Credit_Rule_Id, Process_Status
526 FROM CN_SCA_HEADERS_INTERFACE_GTT;
527
528 C_Acct_Rec C_Get_Acct_Info%ROWTYPE;
529 C_Party_Rec C_Get_Party_Info%ROWTYPE;
530 C_Party_Site_Rec C_Get_Party_Site_Info%ROWTYPE;
531 C_Cust_Cont_Rec C_Get_Cust_Cont_Info%ROWTYPE;
532
533 l_dumb number := 0;
534 BEGIN
535
536 -- Initialize API return status to SUCCESS
537 x_return_status := FND_API.G_RET_STS_SUCCESS;
538
539 -- Standard Start of API savepoint
540 SAVEPOINT GET_CREDITS_PVT;
541
542 -- Generate sca_batch_id from Sequence
543 OPEN C_Get_Batch_Id;
544 FETCH C_Get_Batch_Id INTO l_batch_id;
545 CLOSE C_Get_Batch_Id;
546
547 IF aso_debug_pub.g_debug_flag = 'Y' THEN
548 aso_debug_pub.add('Get_Credits: l_batch_id: '||l_batch_id,1,'N');
549 aso_debug_pub.add('Get_Credits: l_cred_upd_lines_prof: '||l_cred_upd_lines_prof,1,'N');
550 aso_debug_pub.add('Get_Credits: p_qte_header_rec.Cust_Account_Id: '||p_qte_header_rec.Cust_Account_Id,1,'N');
551 aso_debug_pub.add('Get_Credits: p_qte_header_rec.Party_Id: '||p_qte_header_rec.Party_Id,1,'N');
552 aso_debug_pub.add('Get_Credits: p_qte_header_rec.Cust_Party_Id: '||p_qte_header_rec.Cust_Party_Id,1,'N');
553 aso_debug_pub.add('Get_Credits: p_qte_header_rec.sold_to_party_site_id: '||p_qte_header_rec.sold_to_party_site_id,1,'N');
554 aso_debug_pub.add('Get_Credits: attribute1: '||(NVL(p_qte_header_rec.Total_Quote_Price,0) - (NVL(p_qte_header_rec.Total_Tax,0) + NVL(p_qte_header_rec.Total_Shipping_Charge,0))),1,'N');
555 END IF;
556
557 -- Truncate temp tables
558 DELETE FROM CN_SCA_LINES_INTERFACE_GTT;
559
560 DELETE FROM CN_SCA_HEADERS_INTERFACE_GTT;
561
562 DELETE FROM CN_SCA_LINES_OUTPUT_GTT;
563
564 IF aso_debug_pub.g_debug_flag = 'Y' THEN
565 aso_debug_pub.add('Get_Credits: After Truncating tables ',1,'N');
566 END IF;
567 -- Get Info to populate attributes
568 OPEN C_Get_Acct_Info (p_qte_header_rec.Cust_Account_Id);
569 FETCH C_Get_Acct_Info INTO C_Acct_Rec;
570 CLOSE C_Get_Acct_Info;
571
572 OPEN C_Get_Party_Info (p_qte_header_rec.Cust_Party_Id);
573 FETCH C_Get_Party_Info INTO C_Party_Rec;
574 CLOSE C_Get_Party_Info;
575
576 OPEN C_Get_Party_Site_Info (p_qte_header_rec.sold_to_party_site_id);
577 FETCH C_Get_Party_Site_Info INTO C_Party_Site_Rec;
578 CLOSE C_Get_Party_Site_Info;
579
580 OPEN C_Get_Cust_Cont_Info (p_qte_header_rec.Party_Id);
581 FETCH C_Get_Cust_Cont_Info INTO C_Cust_Cont_Rec;
582 CLOSE C_Get_Cust_Cont_Info;
583
584 IF aso_debug_pub.g_debug_flag = 'Y' THEN
585 aso_debug_pub.add('Get_Credits: Before insert header info ',1,'N');
586 aso_debug_pub.add('Get_Credits: C_Party_Site_Rec.Postal_Code: '||C_Party_Site_Rec.Postal_Code,1,'N');
587 aso_debug_pub.add('Get_Credits: C_Party_Rec.Party_Name: '||C_Party_Rec.Party_Name,1,'N');
588 aso_debug_pub.add('Get_Credits: C_Cust_Cont_Rec.Phone_Area_Code: '||C_Cust_Cont_Rec.Phone_Area_Code,1,'N');
589 aso_debug_pub.add('Get_Credits: C_Party_Site_Rec.City: '||C_Party_Site_Rec.City,1,'N');
590 aso_debug_pub.add('Get_Credits: C_Party_Site_Rec.Country: '||C_Party_Site_Rec.Country,1,'N');
591 aso_debug_pub.add('Get_Credits: C_Party_Site_Rec.State: '||C_Party_Site_Rec.State,1,'N');
592 aso_debug_pub.add('Get_Credits: C_Party_Site_Rec.Province: '||C_Party_Site_Rec.Province,1,'N');
593 aso_debug_pub.add('Get_Credits: C_Party_Site_Rec.County: '||C_Party_Site_Rec.County,1,'N');
594 aso_debug_pub.add('Get_Credits: p_qte_header_rec.Marketing_Source_Code_Id: '||p_qte_header_rec.Marketing_Source_Code_Id,1,'N');
595 aso_debug_pub.add('Get_Credits: p_qte_header_rec.Sales_Channel_Code: '||p_qte_header_rec.Sales_Channel_Code,1,'N');
596 END IF;
597 -- Populate CN_SCA_HEADERS_INTERFACE_GTT with header info
598 INSERT INTO CN_SCA_HEADERS_INTERFACE_GTT (
599 SCA_HEADERS_INTERFACE_ID,
600 SCA_BATCH_ID,
601 TRANSACTION_SOURCE,
602 SOURCE_TYPE,
603 SOURCE_ID,
604 SOURCE_LINE_ID,
605 PROCESSED_DATE,
606 ATTRIBUTE1,
607 ATTRIBUTE2,
608 ATTRIBUTE3,
609 ATTRIBUTE4,
610 ATTRIBUTE5,
611 ATTRIBUTE14,
615 ATTRIBUTE18,
612 ATTRIBUTE15,
613 ATTRIBUTE16,
614 ATTRIBUTE17,
616 ATTRIBUTE19,
617 ATTRIBUTE20,
618 ATTRIBUTE21,
619 ATTRIBUTE22,
620 ATTRIBUTE23,
621 ATTRIBUTE24 )
622 VALUES ( CN_SCA_HEADERS_INTERFACE_GTT_S.NextVal,
623 l_batch_id,
624 'QOT',
625 NULL,
626 p_qte_header_rec.Quote_Header_Id,
627 NULL,
628 SYSDATE,
629 (NVL(p_qte_header_rec.Total_Quote_Price,0) - (NVL(p_qte_header_rec.Total_Tax,0) + NVL(p_qte_header_rec.Total_Shipping_Charge,0))),
630 p_qte_header_rec.Total_List_Price,
631 p_qte_header_rec.Total_Quote_Price,
632 p_qte_header_rec.Total_Adjusted_Amount,
633 p_qte_header_rec.Total_Adjusted_Percent,
634 C_Acct_Rec.Account_Number,
635 C_Party_Rec.Party_Name,
636 C_Cust_Cont_Rec.Phone_Area_Code,
637 C_Party_Site_Rec.City,
638 C_Party_Site_Rec.Country,
639 C_Party_Site_Rec.State,
640 C_Party_Site_Rec.Province,
641 C_Party_Site_Rec.County,
642 C_Party_Site_Rec.Postal_Code,
643 p_qte_header_rec.Marketing_Source_Code_Id,
644 p_qte_header_rec.Sales_Channel_Code );
645
646 IF l_cred_upd_lines_prof = 'Y' THEN
647
648 IF aso_debug_pub.g_debug_flag = 'Y' THEN
649 aso_debug_pub.add('Get_Credits: Before insert line info ',1,'N');
650 END IF;
651 -- Populate CN_SCA_HEADERS_INTERFACE_GTT with lines info
652 INSERT INTO CN_SCA_HEADERS_INTERFACE_GTT (
653 SCA_HEADERS_INTERFACE_ID,
654 SCA_BATCH_ID,
655 TRANSACTION_SOURCE,
656 SOURCE_TYPE,
657 SOURCE_ID,
658 SOURCE_LINE_ID,
659 PROCESSED_DATE,
660 ATTRIBUTE6,
661 ATTRIBUTE7,
662 ATTRIBUTE8,
663 ATTRIBUTE9,
664 ATTRIBUTE10,
665 ATTRIBUTE11,
666 ATTRIBUTE12,
667 ATTRIBUTE13,
668 ATTRIBUTE14,
669 ATTRIBUTE15,
670 ATTRIBUTE16,
671 ATTRIBUTE17,
672 ATTRIBUTE18,
673 ATTRIBUTE19,
674 ATTRIBUTE20,
675 ATTRIBUTE21,
676 ATTRIBUTE22,
677 ATTRIBUTE23,
678 ATTRIBUTE24 )
679 SELECT CN_SCA_HEADERS_INTERFACE_GTT_S.NextVal,
680 l_batch_id,
681 'QOT',
682 NULL,
683 p_qte_header_rec.Quote_Header_Id,
684 A.Quote_Line_Id,
685 SYSDATE,
686 (A.Line_Quote_Price * A.Quantity),
687 A.Line_List_Price,
688 A.Line_Quote_Price,
689 A.Line_Adjusted_Amount,
690 A.Line_Adjusted_Percent,
691 A.Quantity,
692 A.UOM_Code,
693 A.Inventory_Item_Id,
694 C_Acct_Rec.Account_Number,
695 C_Party_Rec.Party_Name,
696 C_Cust_Cont_Rec.Phone_Area_Code,
697 C_Party_Site_Rec.City,
698 C_Party_Site_Rec.Country,
699 C_Party_Site_Rec.State,
700 C_Party_Site_Rec.Province,
701 C_Party_Site_Rec.County,
702 C_Party_Site_Rec.Postal_Code,
703 p_qte_header_rec.Marketing_Source_Code_Id,
704 p_qte_header_rec.Sales_Channel_Code
705 FROM ASO_QUOTE_LINES_ALL A
706 WHERE Quote_Header_Id = p_qte_header_rec.Quote_Header_Id;
707
708 END IF;
709
710 IF aso_debug_pub.g_debug_flag = 'Y' THEN
711 aso_debug_pub.add('Get_Credits: Before insert line interface tbl ',1,'N');
712 END IF;
713 -- Populate CN_SCA_LINES_INTERFACE_GTT
714 INSERT INTO CN_SCA_LINES_INTERFACE_GTT (
715 SCA_LINES_INTERFACE_ID,
716 SCA_HEADERS_INTERFACE_ID,
717 SCA_BATCH_ID,
718 RESOURCE_ID,
719 ROLE_ID,
720 SOURCE_TRX_ID )
721 SELECT CN_SCA_LINES_INTERFACE_GTT_S.NextVal,
722 B.SCA_Headers_Interface_Id,
723 l_batch_id,
724 A.Resource_Id,
725 A.Role_Id,
726 p_qte_header_rec.quote_header_id
727 FROM ASO_QUOTE_ACCESSES A, CN_SCA_HEADERS_INTERFACE_GTT B
728 WHERE A.Quote_Number = p_qte_header_rec.Quote_Number
729 AND A.Role_Id IS NOT NULL
730 AND EXISTS
734 WHERE C.Resource_Id = A.Resource_Id
731 ( SELECT C.Resource_Id
732 /* FROM JTF_RS_SRP_VL C */ --Commented Code Yogeshwar (MOAC)
733 FROM JTF_RS_SALESREPS_MO_V C --New Code yogeshwar (MOAC)
735 AND NVL(status,'A') = 'A'
736 AND nvl(trunc(C.start_date_active), trunc(sysdate)) <= trunc(sysdate)
737 AND nvl(trunc(C.end_date_active), trunc(sysdate)) >= trunc(sysdate));
738 --Commented Code Start Yogeshwar (MOAC)
739 /*
740 AND NVL(C.org_id,NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,
741 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99)) =
742 NVL(TO_NUMBER(DECODE(SUBSTRB(USERENV('CLIENT_INFO'),1,1),' ',NULL,
743 SUBSTRB(USERENV('CLIENT_INFO'),1,10))),-99));
744 */
745 --Commented Code End Yogeshwar (MOAC)
746
747 IF aso_debug_pub.g_debug_flag = 'Y' THEN
748 select count(*) into l_dumb from CN_SCA_HEADERS_INTERFACE_GTT;
749 aso_debug_pub.add('Get_Credits: l_headers: '||l_dumb,1,'N');
750 IF l_dumb > 0 THEN
751 FOR C_Hd_Rec IN C_Hd_Inter LOOP
752 aso_debug_pub.add('Get_Credits: tmp credit_rule_id: '||C_Hd_Rec.credit_rule_id,1,'N');
753 aso_debug_pub.add('Get_Credits: tmp process_status: '||C_Hd_Rec.process_status,1,'N');
754 END LOOP;
755 END IF;
756 select count(*) into l_dumb from CN_SCA_LINES_INTERFACE_GTT;
757 aso_debug_pub.add('Get_Credits: l_lines: '||l_dumb,1,'N');
758 END IF;
759
760 IF aso_debug_pub.g_debug_flag = 'Y' THEN
761 aso_debug_pub.add('Get_Credits: Before CN_SCA_CREDITS_ONLINE_PUB.Get_Sales_Credits ',1,'N');
762 aso_utility_pvt.print_login_info();
763 END IF;
764 -- Call CN API to get sales credits
765 CN_SCA_CREDITS_ONLINE_PUB.Get_Sales_Credits (
766 p_api_version => 1.0,
767 p_init_msg_list => FND_API.G_FALSE,
768 x_batch_id => l_batch_id,
769 p_org_id => p_qte_header_rec.org_id,
770 x_return_status => x_return_status,
771 x_msg_count => x_msg_count,
772 x_msg_data => x_msg_data );
773
774 IF aso_debug_pub.g_debug_flag = 'Y' THEN
775 aso_debug_pub.add('Get_Credits: After CN_SCA_CREDITS_ONLINE_PUB.Get_Sales_Credits: '||x_return_status,1,'N');
776 aso_utility_pvt.print_login_info();
777 END IF;
778
779 IF aso_debug_pub.g_debug_flag = 'Y' THEN
780 select count(*) into l_dumb from CN_SCA_LINES_OUTPUT_GTT;
781 aso_debug_pub.add('Get_Credits: l_dumb: '||l_dumb,1,'N');
782 END IF;
783 IF x_return_status = FND_API.G_RET_STS_ERROR THEN
784 RAISE FND_API.G_EXC_ERROR;
785 END IF;
786 IF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR THEN
787 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
788 END IF;
789
790 -- Call Get Sales Credit Type for quota and non quota
791 OPEN C_Get_Quota_Credit_Type;
792 FETCH C_Get_Quota_Credit_Type INTO l_quota_id;
793 CLOSE C_Get_Quota_Credit_Type;
794
795 OPEN C_Get_Non_Quota_Credit_Type;
796 FETCH C_Get_Non_Quota_Credit_Type INTO l_non_quota_id;
797 CLOSE C_Get_Non_Quota_Credit_Type;
798
799 OPEN C_Get_Total_Revenue (l_batch_id);
800 FETCH C_Get_Total_Revenue INTO l_total_rev;
801 CLOSE C_Get_Total_Revenue;
802
803 OPEN C_Get_Hdr_Total (l_batch_id);
804 FETCH C_Get_Hdr_Total INTO l_hdr_total;
805 CLOSE C_Get_Hdr_Total;
806
807 OPEN C_Get_Line_Total (l_batch_id);
808 FETCH C_Get_Line_Total INTO l_line_total;
809 CLOSE C_Get_Line_Total;
810
811 IF aso_debug_pub.g_debug_flag = 'Y' THEN
812 aso_debug_pub.add('Get_Credits: l_quota_id: '||l_quota_id,1,'N');
813 aso_debug_pub.add('Get_Credits: l_non_quota_id: '||l_non_quota_id,1,'N');
814 aso_debug_pub.add('Get_Credits: l_total_rev: '||l_total_rev,1,'N');
815 aso_debug_pub.add('Get_Credits: l_hdr_total: '||l_hdr_total,1,'N');
816 aso_debug_pub.add('Get_Credits: l_line_total: '||l_line_total,1,'N');
817 END IF;
818
819 -- Check if atleast some credit has been allocated
820 IF (l_hdr_total IS NULL OR l_hdr_total < 1) AND
821 (l_line_total IS NULL OR l_line_total < 1) THEN
822 -- x_return_status := FND_API.G_RET_STS_ERROR;
823 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
824 FND_MESSAGE.Set_Name('ASO', 'ASO_NO_CREDIT_ALLOCATED');
825 FND_MSG_PUB.ADD;
826 END IF;
827 -- RAISE FND_API.G_EXC_ERROR;
828 END IF;
829
830 IF l_hdr_total IS NOT NULL AND l_hdr_total > 0 THEN
831
832 -- Save credit diff if below 100, else diff is 0
833 l_credit_diff := 100 - l_total_rev;
834
835 IF aso_debug_pub.g_debug_flag = 'Y' THEN
836 aso_debug_pub.add('Get_Credits: l_credit_diff: '||l_credit_diff,1,'N');
837 END IF;
838 -- Delete existing credits for the quote
839 ASO_SALES_CREDITS_PKG.Delete_Header_Row ( P_Quote_Header_Id => p_Qte_Header_Rec.Quote_Header_Id );
840
841 IF aso_debug_pub.g_debug_flag = 'Y' THEN
842 aso_debug_pub.add('Get_Credits: After Delete_Row ',1,'N');
843 END IF;
844 -- Insert new credits for this quote
845 INSERT INTO ASO_SALES_CREDITS (
846 CREATION_DATE,
847 CREATED_BY,
848 LAST_UPDATED_BY,
849 LAST_UPDATE_DATE,
850 LAST_UPDATE_LOGIN,
851 SALES_CREDIT_ID,
852 QUOTE_HEADER_ID,
853 QUOTE_LINE_ID,
854 PERCENT,
855 RESOURCE_ID,
856 RESOURCE_GROUP_ID,
857 SALES_CREDIT_TYPE_ID,
861 G_USER_ID,
858 SYSTEM_ASSIGNED_FLAG,
859 CREDIT_RULE_ID )
860 SELECT SYSDATE,
862 G_USER_ID,
863 SYSDATE,
864 G_LOGIN_ID,
865 ASO_SALES_CREDITS_S.nextval,
866 B.Source_Id,
867 B.Source_Line_Id,
868 A.Allocation_Percentage,
869 A.Resource_Id,
870 C.Resource_Grp_Id,
871 Decode(A.Revenue_Type, 'REVENUE', l_quota_id,l_non_quota_id),
872 'Y',
873 B.Credit_Rule_Id
874 FROM CN_SCA_LINES_OUTPUT_GTT A,
875 CN_SCA_HEADERS_INTERFACE_GTT B,
876 ASO_QUOTE_ACCESSES C
877 WHERE A.SCA_Batch_Id = l_batch_id
878 AND B.SCA_Batch_Id = l_batch_id
879 AND B.SCA_Headers_Interface_Id = A.SCA_Headers_Interface_Id
880 AND A.Resource_Id = C.Resource_Id
881 AND C.Quote_Number = p_qte_header_rec.Quote_Number
882 AND B.Source_Line_Id IS NULL;
883
884
885 IF aso_debug_pub.g_debug_flag = 'Y' THEN
886 aso_debug_pub.add('Get_Credits: After Insert_Row to ASO_SALES_CREDITS ',1,'N');
887 END IF;
888 -- Update the primary salesrep percent in aso_sales_credits
889 IF l_credit_diff > 0 THEN
890 OPEN C_PSRep_Credit (p_Qte_Header_Rec.Quote_Header_Id, p_Qte_Header_Rec.Resource_Id, l_quota_id);
891 FETCH C_PSRep_Credit INTO l_PSRep;
892 CLOSE C_PSRep_Credit;
893
894 IF aso_debug_pub.g_debug_flag = 'Y' THEN
895 aso_debug_pub.add('Get_Credits: l_PSRep '||l_PSRep,1,'N');
896 END IF;
897 IF l_PSRep IS NOT NULL THEN
898
899 UPDATE ASO_SALES_CREDITS
900 SET Percent = Percent + l_credit_diff
901 WHERE Resource_Id = l_PSRep
902 AND Sales_Credit_Type_Id = l_quota_id
903 AND Quote_Header_Id = p_Qte_Header_Rec.Quote_Header_Id;
904
905 IF aso_debug_pub.g_debug_flag = 'Y' THEN
906 aso_debug_pub.add('Get_Credits: After Update to ASO_SALES_CREDITS PSRep ',1,'N');
907 END IF;
908 ELSE
909
910 INSERT INTO ASO_SALES_CREDITS (
911 CREATION_DATE,
912 CREATED_BY,
913 LAST_UPDATED_BY,
914 LAST_UPDATE_DATE,
915 LAST_UPDATE_LOGIN,
916 SALES_CREDIT_ID,
917 QUOTE_HEADER_ID,
918 QUOTE_LINE_ID,
919 PERCENT,
920 RESOURCE_ID,
921 RESOURCE_GROUP_ID,
922 SALES_CREDIT_TYPE_ID,
923 SYSTEM_ASSIGNED_FLAG,
924 CREDIT_RULE_ID )
925 SELECT SYSDATE,
926 G_USER_ID,
927 G_USER_ID,
928 SYSDATE,
929 G_LOGIN_ID,
930 ASO_SALES_CREDITS_S.nextval,
931 p_Qte_Header_Rec.Quote_Header_Id,
932 NULL,
933 l_credit_diff,
934 A.Resource_Id,
935 A.Resource_Grp_Id,
936 l_quota_id,
937 'Y',
938 NULL
939 FROM ASO_QUOTE_ACCESSES A
940 WHERE A.Resource_Id = p_Qte_Header_Rec.Resource_Id
941 AND A.Quote_Number = p_qte_header_rec.Quote_Number;
942
943 IF aso_debug_pub.g_debug_flag = 'Y' THEN
944 aso_debug_pub.add('Get_Credits: After Insert to ASO_SALES_CREDITS PSRep ',1,'N');
945 END IF;
946 END IF; -- l_PSRep
947
948 END IF; -- l_credit_diff
949
950 END IF; -- l_hdr_total
951
952
953 IF (l_cred_upd_lines_prof = 'Y') AND (l_line_total IS NOT NULL AND l_line_total > 0) THEN
954
955 FOR C_Get_Line_Rec IN C_Get_Line (p_qte_header_rec.Quote_Header_Id) LOOP
956
957 OPEN C_Get_Line_Revenue (l_batch_id, C_Get_Line_Rec.Quote_Line_Id);
958 FETCH C_Get_Line_Revenue INTO l_line_rev;
959 CLOSE C_Get_Line_Revenue;
960
961 l_cred_line_diff := 100 - l_line_rev;
962
963 -- Delete existing credits for the quote
964 ASO_SALES_CREDITS_PKG.Delete_Row ( P_Quote_Line_Id => C_Get_Line_Rec.Quote_Line_Id );
965
966 IF aso_debug_pub.g_debug_flag = 'Y' THEN
967 aso_debug_pub.add('Get_Credits: After Delete_Row ',1,'N');
968 END IF;
969 -- Insert new credits for this quote
970 INSERT INTO ASO_SALES_CREDITS (
971 CREATION_DATE,
972 CREATED_BY,
973 LAST_UPDATED_BY,
974 LAST_UPDATE_DATE,
975 LAST_UPDATE_LOGIN,
976 SALES_CREDIT_ID,
977 QUOTE_HEADER_ID,
978 QUOTE_LINE_ID,
979 PERCENT,
980 RESOURCE_ID,
981 RESOURCE_GROUP_ID,
982 SALES_CREDIT_TYPE_ID,
983 SYSTEM_ASSIGNED_FLAG,
984 CREDIT_RULE_ID )
985 SELECT SYSDATE,
986 G_USER_ID,
987 G_USER_ID,
988 SYSDATE,
989 G_LOGIN_ID,
990 ASO_SALES_CREDITS_S.nextval,
991 B.Source_Id,
992 B.Source_Line_Id,
993 A.Allocation_Percentage,
994 A.Resource_Id,
995 C.Resource_Grp_Id,
996 Decode(A.Revenue_Type, 'REVENUE', l_quota_id,l_non_quota_id),
997 'Y',
998 B.Credit_Rule_Id
999 FROM CN_SCA_LINES_OUTPUT_GTT A,
1000 CN_SCA_HEADERS_INTERFACE_GTT B,
1001 ASO_QUOTE_ACCESSES C
1002 WHERE A.SCA_Batch_Id = l_batch_id
1003 AND B.SCA_Batch_Id = l_batch_id
1004 AND B.SCA_Headers_Interface_Id = A.SCA_Headers_Interface_Id
1005 AND A.Resource_Id = C.Resource_Id
1006 AND C.Quote_Number = p_qte_header_rec.Quote_Number
1007 AND B.Source_Line_Id = C_Get_Line_Rec.Quote_Line_Id;
1008
1009 IF aso_debug_pub.g_debug_flag = 'Y' THEN
1010 aso_debug_pub.add('Get_Credits: After Insert_Row to ASO_SALES_CREDITS ',1,'N');
1011 END IF;
1012 -- Update the primary salesrep percent in aso_sales_credits
1013 IF l_cred_line_diff > 0 THEN
1014
1015 l_PSRep := NULL;
1016
1017 OPEN C_PSRep_Credit_Line (p_Qte_Header_Rec.Quote_Header_Id, p_Qte_Header_Rec.Resource_Id,
1018 l_quota_id, C_Get_Line_Rec.Quote_Line_Id);
1019 FETCH C_PSRep_Credit_Line INTO l_PSRep;
1020 CLOSE C_PSRep_Credit_Line;
1021
1022 IF l_PSRep IS NOT NULL THEN
1023
1024 UPDATE ASO_SALES_CREDITS
1025 SET Percent = Percent + l_credit_diff
1026 WHERE Resource_Id = l_PSRep
1027 AND Sales_Credit_Type_Id = l_quota_id
1028 AND Quote_Header_Id = p_Qte_Header_Rec.Quote_Header_Id
1029 AND Quote_Line_Id = C_Get_Line_Rec.Quote_Line_Id;
1030
1031 ELSE
1032
1033 INSERT INTO ASO_SALES_CREDITS (
1034 CREATION_DATE,
1035 CREATED_BY,
1036 LAST_UPDATED_BY,
1037 LAST_UPDATE_DATE,
1038 LAST_UPDATE_LOGIN,
1039 SALES_CREDIT_ID,
1040 QUOTE_HEADER_ID,
1041 QUOTE_LINE_ID,
1042 PERCENT,
1043 RESOURCE_ID,
1044 RESOURCE_GROUP_ID,
1045 SALES_CREDIT_TYPE_ID,
1046 SYSTEM_ASSIGNED_FLAG,
1047 CREDIT_RULE_ID )
1048 SELECT SYSDATE,
1049 G_USER_ID,
1050 G_USER_ID,
1051 SYSDATE,
1052 G_LOGIN_ID,
1053 ASO_SALES_CREDITS_S.nextval,
1054 p_Qte_Header_Rec.Quote_Header_Id,
1055 C_Get_Line_Rec.Quote_Line_Id,
1056 l_cred_line_diff,
1057 A.Resource_Id,
1058 A.Resource_Grp_Id,
1059 l_quota_id,
1060 'Y',
1061 NULL
1062 FROM ASO_QUOTE_ACCESSES A
1063 WHERE A.Resource_Id = p_Qte_Header_Rec.Resource_Id
1064 AND A.Quote_Number = p_qte_header_rec.Quote_Number;
1065
1066 END IF; -- l_PSRep
1067
1068 END IF; -- l_credit_line_diff
1069
1070 END LOOP;
1071
1072 END IF; -- credit_upd_prof or l_line_total
1073
1074 EXCEPTION
1075
1076 WHEN FND_API.G_EXC_ERROR THEN
1077 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1078 P_API_NAME => L_API_NAME,
1079 P_PKG_NAME => G_PKG_NAME,
1080 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR,
1081 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
1082 P_SQLCODE => SQLCODE,
1083 P_SQLERRM => SQLERRM,
1084 X_MSG_COUNT => X_MSG_COUNT,
1085 X_MSG_DATA => X_MSG_DATA,
1086 X_RETURN_STATUS => X_RETURN_STATUS
1087 );
1088
1089 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1090 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1091 P_API_NAME => L_API_NAME,
1092 P_PKG_NAME => G_PKG_NAME,
1093 P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR,
1094 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
1095 P_SQLCODE => SQLCODE,
1096 P_SQLERRM => SQLERRM,
1097 X_MSG_COUNT => X_MSG_COUNT,
1098 X_MSG_DATA => X_MSG_DATA,
1099 X_RETURN_STATUS => X_RETURN_STATUS
1100 );
1101
1102 WHEN OTHERS THEN
1103 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
1104 P_API_NAME => L_API_NAME,
1105 P_PKG_NAME => G_PKG_NAME,
1106 P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS,
1107 P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT,
1108 P_SQLCODE => SQLCODE,
1109 P_SQLERRM => SQLERRM,
1110 X_MSG_COUNT => X_MSG_COUNT,
1111 X_MSG_DATA => X_MSG_DATA,
1112 X_RETURN_STATUS => X_RETURN_STATUS
1113 );
1114
1115 END Get_Credits;
1116
1117
1118 END ASO_SALES_CREDIT_PVT;