[Home] [Help]
PACKAGE BODY: APPS.ASO_CREDIT_CHECK_PVT
Source
1 PACKAGE BODY ASO_CREDIT_CHECK_PVT as
2 /* $Header: asoiqccb.pls 120.1 2005/06/29 12:35:19 appldev ship $ */
3 -- Start of Comments
4 -- Package name : ASO_CREDIT_CHECK_PVT
5 -- Purpose :
6 -- History :
7 -- NOTE :
8 -- End of Comments
9
10 G_PKG_NAME CONSTANT VARCHAR2(30):= 'ASO_CREDIT_CHECK_PVT';
11 G_FILE_NAME CONSTANT VARCHAR2(12) := 'asoiqccb.pls';
12
13 PROCEDURE Credit_Check(
14 P_API_VERSION IN NUMBER,
15 P_INIT_MSG_LIST IN VARCHAR2 := FND_API.G_FALSE,
16 P_COMMIT IN VARCHAR2 := FND_API.G_FALSE,
17 P_QTE_HEADER_REC IN ASO_QUOTE_PUB.Qte_Header_Rec_Type,
18 X_RESULT_OUT OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
19 X_CC_HOLD_COMMENT OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
20 X_RETURN_STATUS OUT NOCOPY /* file.sql.39 change */ VARCHAR2,
21 X_MSG_COUNT OUT NOCOPY /* file.sql.39 change */ NUMBER,
22 X_MSG_DATA OUT NOCOPY /* file.sql.39 change */ VARCHAR2
23 )
24 IS
25
26 CURSOR c_functional_currency(p_set_of_books_id NUMBER) IS
27 SELECT currency_code
28 FROM gl_sets_of_books
29 WHERE set_of_books_id = p_set_of_books_id;
30
31 CURSOR c_credit_check_flag(p_credit_check_rule_id NUMBER) IS
32 SELECT incl_freight_charges_flag,include_tax_flag
33 FROM OE_CREDIT_CHECK_RULES
34 WHERE credit_check_rule_id = p_credit_check_rule_id;
35
36 l_api_version NUMBER := 1.0;
37 l_api_name VARCHAR2(50) := 'Credit_Check';
38 l_return_status VARCHAR2(1);
39 l_msg_data VARCHAR2(2000);
40 l_msg_count NUMBER := 0;
41 l_set_of_books_id NUMBER;
42 l_functional_currency VARCHAR2(15);
43 l_credit_check_rule_id NUMBER;
44 l_site_use_id NUMBER;
45 l_qte_header_rec ASO_QUOTE_PUB.Qte_Header_Rec_Type;
46 l_include_tax_flag OE_CREDIT_CHECK_RULES.INCLUDE_TAX_FLAG%TYPE;
47 l_incl_freight_charges_flag OE_CREDIT_CHECK_RULES.INCL_FREIGHT_CHARGES_FLAG%TYPE;
48 l_transactional_amount ASO_QUOTE_HEADERS_ALL.TOTAL_QUOTE_PRICE%TYPE;
49 x_status VARCHAR2(1);
50
51
52 BEGIN
53 -- Standard Start of API savepoint
54 SAVEPOINT CREDIT_CHECK_PVT;
55
56 -- Standard call to check for call compatibility.
57 IF NOT FND_API.Compatible_API_Call ( l_api_version,
58 p_api_version,
59 l_api_name,
60 G_PKG_NAME)
61 THEN
62 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
63 END IF;
64
65
66 -- Initialize message list if p_init_msg_list is set to TRUE.
67 IF FND_API.to_Boolean( p_init_msg_list )
68 THEN
69 FND_MSG_PUB.initialize;
70 END IF;
71
72
73
74 -- Initializing Global Debug Variable.
75 aso_debug_pub.g_debug_flag := NVL(FND_PROFILE.VALUE('ASO_ENABLE_DEBUG'),'N');
76
77 -- Initialize API return status to SUCCESS
78 x_return_status := FND_API.G_RET_STS_SUCCESS;
79
80 IF aso_debug_pub.g_debug_flag = 'Y' THEN
81 aso_debug_pub.add('Credit Check - Begin...', 1, 'Y');
82 END IF;
83
84
85 --
86 -- API body
87 --
88
89 -- ******************************************************************
90 -- Validate Environment
91 -- ******************************************************************
92 IF FND_GLOBAL.User_Id IS NULL
93 THEN
94 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR)
95 THEN
96 FND_MESSAGE.Set_Name(' + appShortName +', 'ASO_CANNOT_GET_PROFILE_VALUE');
97 FND_MESSAGE.Set_Token('PROFILE', 'USER_ID', FALSE);
98 FND_MSG_PUB.ADD;
99 END IF;
100 RAISE FND_API.G_EXC_ERROR;
101 END IF;
102
103
104 -- ******************************************************************
105 -- Validate Profile
106 -- ******************************************************************
107
108 l_credit_check_rule_id := NVL(FND_PROFILE.VALUE('ASO_CREDIT_CHECK_RULE'),0);
109
110 IF aso_debug_pub.g_debug_flag = 'Y' THEN
111 aso_debug_pub.add('Credit Check Rule Profile Value : '||NVL(to_char(l_credit_check_rule_id),'null'), 1, 'Y');
112 END IF;
113
114 IF NVL(l_credit_check_rule_id,0) > 0 THEN
115
116 -- ******************************************************************
117 -- Validate Required Information
118 -- ******************************************************************
119 IF aso_debug_pub.g_debug_flag = 'Y' THEN
120 aso_debug_pub.add('Quote Header Id : '||NVL(to_char(p_qte_header_rec.quote_header_id),'null'), 1, 'Y');
121 END IF;
122
123 IF p_qte_header_rec.quote_header_id IS NOT NULL AND
124 p_qte_header_rec.quote_header_id <> FND_API.G_MISS_NUM
125 THEN
126 IF aso_debug_pub.g_debug_flag = 'Y' THEN
127 aso_debug_pub.add(' Credit Check Lock Exists or not... ', 1, 'Y');
128 END IF;
129
130 -- ******************************************************************
131 -- Validate For any locks on the quote.
132 -- ******************************************************************
133
134 ASO_CONC_REQ_INT.Lock_Exists(
135 p_quote_header_id => p_qte_header_rec.quote_header_id,
136 x_status => x_status);
137 IF aso_debug_pub.g_debug_flag = 'Y' THEN
138 aso_debug_pub.add(' Credit Check Lock Exists : x_status '||NVL(x_status,'null'), 1, 'Y');
139 END IF;
140
141 IF (x_status = FND_API.G_TRUE) THEN
142 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
143 FND_MESSAGE.Set_Name('ASO', 'ASO_CONC_REQUEST_RUNNING');
144 FND_MSG_PUB.ADD;
145 END IF;
146 RAISE FND_API.G_EXC_ERROR;
147 END IF;
148
149 -- get quote information
150
151 BEGIN
152 IF aso_debug_pub.g_debug_flag = 'Y' THEN
153 aso_debug_pub.add('Before ASO_UTILITY_PVT.Query_Header_Row... ', 1, 'Y');
154 END IF;
155
156 l_qte_header_rec := ASO_UTILITY_PVT.Query_Header_Row(p_qte_header_rec.quote_header_id);
157 IF aso_debug_pub.g_debug_flag = 'Y' THEN
158 aso_debug_pub.add('After ASO_UTILITY_PVT.Query_Header_Row... ', 1, 'Y');
159 END IF;
160 EXCEPTION
161 WHEN OTHERS THEN
162 IF aso_debug_pub.g_debug_flag = 'Y' THEN
163 aso_debug_pub.add('Exception ASO_UTILITY_PVT.Query_Header_Row... ', 1, 'Y');
164 END IF;
165 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
166 FND_MESSAGE.Set_Name('ASO', 'ASO_API_INVALID_ID');
167 FND_MESSAGE.Set_Token('COLUMN', 'Quote_header_id', FALSE);
168 FND_MESSAGE.Set_Token('VALUE', l_qte_header_rec.quote_header_id, FALSE);
169 FND_MSG_PUB.ADD;
170 END IF;
171 raise FND_API.G_EXC_ERROR;
172 END;
173 ELSE
174 IF aso_debug_pub.g_debug_flag = 'Y' THEN
175 aso_debug_pub.add('before - p_qte_header_rec is NULL... ', 1, 'Y');
176 END IF;
177
178 l_qte_header_rec := p_qte_header_rec;
179
180 IF aso_debug_pub.g_debug_flag = 'Y' THEN
181 aso_debug_pub.add('After - p_qte_header_rec is NULL... ', 1, 'Y');
182 END IF;
183 END IF;
184
185 IF aso_debug_pub.g_debug_flag = 'Y' THEN
186 aso_debug_pub.add('Checking for all the required values... ', 1, 'Y');
187 aso_debug_pub.add('Org Id :'||NVL(to_char(l_qte_header_rec.org_id),'null'), 1, 'Y');
188 aso_debug_pub.add('Currency Code:'||NVL(l_qte_header_rec.currency_code,'null'), 1, 'Y');
189 aso_debug_pub.add('Invoice To Party Site Id :'||NVL(to_char(l_qte_header_rec.invoice_to_party_site_id),'null'), 1, 'Y');
190 aso_debug_pub.add('Invoice To Cust Account Id :'||NVL(to_char(l_qte_header_rec.invoice_to_cust_account_id),'null'), 1, 'Y');
191 END IF;
192
193 -- Check if all the required values are available.
194
195 IF l_qte_header_rec.org_id IS NOT NULL AND
196 l_qte_header_rec.org_id <> FND_API.G_MISS_NUM AND
197 l_qte_header_rec.currency_code IS NOT NULL AND
198 l_qte_header_rec.currency_code <> FND_API.G_MISS_CHAR AND
199 l_qte_header_rec.invoice_to_party_site_id IS NOT NULL AND
200 l_qte_header_rec.invoice_to_party_site_id <> FND_API.G_MISS_NUM AND
201 NVL(l_qte_header_rec.invoice_to_cust_account_id,l_qte_header_rec.cust_account_id) IS NOT NULL AND
202 NVL(l_qte_header_rec.invoice_to_cust_account_id,l_qte_header_rec.cust_account_id) <> FND_API.G_MISS_NUM
203 THEN
204 l_set_of_books_id := OE_PROFILE.VALUE('OE_SET_OF_BOOKS_ID',l_qte_header_rec.org_id);
205
206 IF aso_debug_pub.g_debug_flag = 'Y' THEN
207 aso_debug_pub.add('Set Of books ID:'||NVL(to_char(l_set_of_books_id),'null'), 1, 'Y');
208 END IF;
209
210 IF l_set_of_books_id IS NOT NULL THEN
211
212 -- get functional currency
213
214 IF aso_debug_pub.g_debug_flag = 'Y' THEN
215 aso_debug_pub.add('Before Fetching Functional Currency', 1, 'Y');
216 END IF;
217
218 OPEN C_functional_currency(l_set_of_books_id);
219 FETCH C_functional_currency INTO l_functional_currency;
220
221 IF aso_debug_pub.g_debug_flag = 'Y' THEN
222 aso_debug_pub.add('After Fetching Functional Currency :'||NVL(l_functional_currency,'null'), 1, 'Y');
223 END IF;
224
225
226 IF ( C_functional_currency%NOTFOUND) Then
227 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
228 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_GL_CURRENCY');
229 FND_MSG_PUB.ADD;
230 END IF;
231 raise FND_API.G_EXC_ERROR;
232 END IF;
233
234 CLOSE C_functional_currency;
235 ELSE
236 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
237 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_OE_PROFILE');
238 FND_MSG_PUB.ADD;
239 END IF;
240 raise FND_API.G_EXC_ERROR;
241 END IF;
242
243 -- Get Bill to Site Use Id.
244
245 IF aso_debug_pub.g_debug_flag = 'Y' THEN
246 aso_debug_pub.add('Before Bill To Site Use Id Fetch', 1, 'Y');
247 END IF;
248
249
250 ASO_MAP_QUOTE_ORDER_INT.GET_ACCT_SITE_USES (
251 P_Cust_Account_Id => NVL(l_qte_header_rec.invoice_to_cust_account_id,l_qte_header_rec.cust_account_id)
252 ,P_Party_Site_Id =>l_qte_header_rec.invoice_to_party_site_id
253 ,P_Acct_Site_type => 'BILL_TO'
254 ,x_return_status => l_return_status
255 ,x_site_use_id => l_site_use_id
256 );
257
258 IF aso_debug_pub.g_debug_flag = 'Y' THEN
259 aso_debug_pub.add('After Bill To Site Use Id Fetch :'||NVL(to_char(l_site_use_id),'null'), 1, 'Y');
260 aso_debug_pub.add('After Bill To Site Use Id Fetch l_return_status :'||NVL(l_return_status,' '), 1, 'Y');
261 END IF;
262
263 IF l_return_status <> FND_API.G_RET_STS_SUCCESS OR
264 NVL(l_site_use_id ,FND_API.G_MISS_NUM) = FND_API.G_MISS_NUM
265 THEN
266 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
267 FND_MESSAGE.Set_Name('ASO', 'ASO_API_INVALID_BILLTO_INFO');
268 FND_MSG_PUB.ADD;
269 END IF;
270 raise FND_API.G_EXC_ERROR;
271 END IF;
272
273 -- Transaction Amount Calculation.
274
275 IF aso_debug_pub.g_debug_flag = 'Y' THEN
276 aso_debug_pub.add('Before Credit Check Flags Fetch', 1, 'Y');
277 END IF;
278
279 OPEN C_credit_check_flag(l_credit_check_rule_id);
280 FETCH C_credit_check_flag INTO l_incl_freight_charges_flag,l_include_tax_flag;
281
282 IF aso_debug_pub.g_debug_flag = 'Y' THEN
283 aso_debug_pub.add('After Credit Check Flag Fetch... ', 1, 'Y');
284 aso_debug_pub.add('Include Tax Flag : '||NVL(l_include_tax_flag,'null'), 1, 'Y');
285 aso_debug_pub.add('Include Freight Charges Flag : '||NVL(l_incl_freight_charges_flag,'null'), 1, 'Y');
286 END IF;
287
288 IF ( C_credit_check_flag%NOTFOUND) Then
289 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
290 FND_MESSAGE.Set_Name('ASO', 'ASO_API_NO_TAX_FREIGHT_INFO');
291 FND_MSG_PUB.ADD;
292 END IF;
293 raise FND_API.G_EXC_ERROR;
294 END IF;
295
296 CLOSE C_credit_check_flag;
297
298 IF aso_debug_pub.g_debug_flag = 'Y' THEN
299 aso_debug_pub.add('Before Transactional Amount Calulation', 1, 'Y');
300 aso_debug_pub.add('Total Quote Price :'||NVL(to_char(l_qte_header_rec.total_quote_price),'null'), 1, 'Y');
301 aso_debug_pub.add('Total Tax :'||NVL(to_char(l_qte_header_rec.total_tax),'null'), 1, 'Y');
302 aso_debug_pub.add('Total Freight Charges :'||NVL(to_char(l_qte_header_rec.total_shipping_charge),'null'), 1, 'Y');
303 END IF;
304
305 IF NVL(l_include_tax_flag,'N') = 'N' THEN
306 l_transactional_amount := NVL(l_qte_header_rec.total_quote_price,0) - NVL(l_qte_header_rec.total_tax,0);
307 ELSE
308 l_transactional_amount := NVL(l_qte_header_rec.total_quote_price,0);
309 END IF;
310
311 IF NVL(l_incl_freight_charges_flag,'N') = 'N' THEN
312 l_transactional_amount := NVL(l_transactional_amount,0) - NVL(l_qte_header_rec.total_shipping_charge,0);
313 END IF;
314 IF aso_debug_pub.g_debug_flag = 'Y' THEN
315 aso_debug_pub.add('After Transactional Amount Calulation : '||NVL(to_char(l_transactional_amount),'null'), 1, 'Y');
316
317 aso_debug_pub.add('Before OM Public API call', 1, 'Y');
318 END IF;
319
320 -- calling the om api to do internal credit check.
321 OE_EXTERNAL_CREDIT_PUB.CHECK_EXTERNAL_CREDIT
322 (
323 P_API_VERSION => 1.0,
324 P_INIT_MSG_LIST => p_init_msg_list,
325 X_RETURN_STATUS => x_return_status,
326 X_RESULT_OUT => x_result_out,
327 X_MSG_DATA => x_msg_data,
328 X_MSG_COUNT => x_msg_count,
329 X_CC_HOLD_COMMENT => x_cc_hold_comment,
330 p_bill_to_site_use_id => l_site_use_id,
331 p_functional_currency_code => l_functional_currency,
332 p_transaction_currency_code => l_qte_header_rec.currency_code,
333 p_transaction_amount => l_transactional_amount,
334 p_credit_check_rule_id => l_credit_check_rule_id,
335 p_org_id => l_qte_header_rec.org_id
336 );
337
338 IF aso_debug_pub.g_debug_flag = 'Y' THEN
339 aso_debug_pub.add('After OM Public API call - return status :'||x_return_status, 1, 'Y');
340 aso_debug_pub.add('After OM Public API call - msg count :'||nvl(to_char(x_msg_count),'0'), 1, 'Y');
341 aso_debug_pub.add('After OM Public API call - msg data :'||nvl(x_msg_data,'null'), 1, 'Y');
342 END IF;
343 -- Check return status from the above procedure call
344 IF x_return_status = FND_API.G_RET_STS_ERROR then
345 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
346 oe_msg_pub.count_and_get( p_encoded => 'F'
347 , p_count => x_msg_count
348 , p_data => x_msg_data);
349 for k in 1 .. x_msg_count loop
350 x_msg_data := oe_msg_pub.get( p_msg_index => k,
351 p_encoded => 'F');
352 IF aso_debug_pub.g_debug_flag = 'Y' THEN
353 aso_debug_pub.add('k='||k||'.OM error Message :'||nvl(x_msg_data,'null'), 1, 'Y');
354 END IF;
355 FND_MESSAGE.Set_Name('ASO', 'ASO_OM_ERROR');
356 FND_MESSAGE.Set_Token('MSG_TXT', x_msg_data, FALSE);
357 FND_MSG_PUB.ADD;
358 end loop;
359 END IF;
360 raise FND_API.G_EXC_ERROR;
361 ELSIF x_return_status = FND_API.G_RET_STS_UNEXP_ERROR then
362 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
363 FND_MESSAGE.Set_Name('ASO', 'ASO_API_UNEXP_ERROR');
364 FND_MESSAGE.Set_Token('ROW', 'ASO_CREDIT_CHECK_PVT AFTER OM CALL', TRUE);
365 FND_MSG_PUB.ADD;
366 END IF;
367 raise FND_API.G_EXC_UNEXPECTED_ERROR;
368 END IF;
369 ELSE
370 IF FND_MSG_PUB.Check_Msg_Level (FND_MSG_PUB.G_MSG_LVL_ERROR) THEN
371 FND_MESSAGE.Set_Name('ASO', 'ASO_API_MISSING_INFO');
372 FND_MESSAGE.Set_Token('COLUMN', 'Internal Credit Check Required ', FALSE);
373 FND_MSG_PUB.ADD;
374 END IF;
375 raise FND_API.G_EXC_ERROR;
376 END IF; -- Missing Values Check
377
378 END IF; -- Credit Check Rule Profile is not set
379
380 --
381 -- End of API body.
382 --
383
384 -- Standard check for p_commit
385 IF FND_API.to_Boolean( p_commit )
386 THEN
387 COMMIT WORK;
388 END IF;
389
390
391
392
393 -- Standard call to get message count and if count is 1, get message info.
394 FND_MSG_PUB.Count_And_Get
395 ( p_count => x_msg_count,
396 p_data => x_msg_data
397 );
398
399 EXCEPTION
400 WHEN FND_API.G_EXC_ERROR THEN
401 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
402 P_API_NAME => L_API_NAME
403 ,P_PKG_NAME => G_PKG_NAME
404 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_ERROR
405 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
406 ,P_SQLCODE => SQLCODE
407 ,P_SQLERRM => SQLERRM
408 ,X_MSG_COUNT => X_MSG_COUNT
409 ,X_MSG_DATA => X_MSG_DATA
410 ,X_RETURN_STATUS => X_RETURN_STATUS);
411
412 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
413 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
414 P_API_NAME => L_API_NAME
415 ,P_PKG_NAME => G_PKG_NAME
416 ,P_EXCEPTION_LEVEL => FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR
417 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
418 ,P_SQLCODE => SQLCODE
419 ,P_SQLERRM => SQLERRM
420 ,X_MSG_COUNT => X_MSG_COUNT
421 ,X_MSG_DATA => X_MSG_DATA
422 ,X_RETURN_STATUS => X_RETURN_STATUS);
423
424 WHEN OTHERS THEN
425 ASO_UTILITY_PVT.HANDLE_EXCEPTIONS(
426 P_API_NAME => L_API_NAME
427 ,P_PKG_NAME => G_PKG_NAME
428 ,P_EXCEPTION_LEVEL => ASO_UTILITY_PVT.G_EXC_OTHERS
429 ,P_PACKAGE_TYPE => ASO_UTILITY_PVT.G_PVT
430 ,P_SQLCODE => SQLCODE
431 ,P_SQLERRM => SQLERRM
432 ,X_MSG_COUNT => X_MSG_COUNT
433 ,X_MSG_DATA => X_MSG_DATA
434 ,X_RETURN_STATUS => X_RETURN_STATUS);
435 END Credit_Check;
436
437 -- subha madapusi - quote credit check end.
438
439 End ASO_CREDIT_CHECK_PVT;