[Home] [Help]
PACKAGE BODY: APPS.OE_EXTERNAL_CREDIT_PVT
Source
1 PACKAGE BODY OE_EXTERNAL_CREDIT_PVT AS
2 -- $Header: OEXVCECB.pls 120.2 2006/01/10 16:53:04 spooruli ship $
3 --------------------
4 -- TYPE DECLARATIONS
5 --------------------
6
7 ------------
8 -- CONSTANTS
9 ------------
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_External_Credit_PVT';
11 -------------------
12 -- PUBLIC VARIABLES
13 -------------------
14
15 ---------------------------
16 -- PROCEDURES AND FUNCTIONS
17 ---------------------------
18 --
19 --=====================================================================
20 -- NAME: Is_Amount_Valid
21 -- TYPE: PRIVATE FUNCTION
22 -- DESCRIPTION: This function returns TRUE if the amount is a valid amount
23 -- and FALSE otherwise.
24 --=====================================================================
25 FUNCTION Is_Amount_Valid
26 ( p_amount IN NUMBER
27 )
28 RETURN BOOLEAN
29 IS
30 BEGIN
31 OE_DEBUG_PUB.Add('OEXVCECB: In Is_Amount_Valid');
32 IF p_amount IS NULL THEN
33 FND_MESSAGE.Set_Name('ONT', 'OE_CC_PARAMETER_NULL');
34 FND_MESSAGE.SET_TOKEN ('PARAMETER_NAME', 'P_TRANSACTION_AMOUNT' );
35 OE_MSG_PUB.Add;
36 OE_DEBUG_PUB.Add('Validate Amount Failed.');
37 RETURN FALSE;
38 ELSE
39 RETURN TRUE;
40 END IF;
41 OE_DEBUG_PUB.Add('OEXVCECB: Out Is_Amount_Valid');
42 EXCEPTION
43 WHEN OTHERS THEN
44 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
45 END Is_Amount_Valid;
46
47 --=====================================================================
48 -- NAME: Is_Currency_Valid
49 -- TYPE: PRIVATE FUNCTION
50 -- DESCRIPTION: This function returns TRUE if the currency code is a
51 -- valid currency code and FALSE otherwise.
52 --=====================================================================
53 FUNCTION Is_Currency_Valid
54 ( p_currency_code IN VARCHAR2
55 , p_parameter_name IN VARCHAR2
56 )
57 RETURN BOOLEAN
58 IS
59 l_return_value BOOLEAN := TRUE;
60 l_curr_valid NUMBER;
61 BEGIN
62 OE_DEBUG_PUB.Add('OEXVCECB: In Is_Currency_Valid');
63 IF p_currency_code IS NULL THEN
64 FND_MESSAGE.Set_Name('ONT', 'OE_CC_PARAMETER_NULL');
65 FND_MESSAGE.SET_TOKEN ('PARAMETER_NAME', p_parameter_name );
66 OE_MSG_PUB.Add;
67 OE_DEBUG_PUB.Add('Validate Currency Failed - NULL.');
68 l_return_value := FALSE;
69 ELSE
70 BEGIN
71 SELECT 1
72 INTO l_curr_valid
73 FROM fnd_currencies
74 WHERE currency_code = p_currency_code
75 AND enabled_flag = 'Y'
76 AND NVL(start_date_active, TO_DATE('01/01/1000','DD/MM/YYYY'))
77 <= TRUNC(SYSDATE)
78 AND NVL(end_date_active, TO_DATE('31/12/9999','DD/MM/YYYY'))
79 >= TRUNC(SYSDATE) ;
80 EXCEPTION
81 WHEN NO_DATA_FOUND THEN
82 FND_MESSAGE.Set_Name('ONT', 'OE_CC_CURRENCY_INVALID');
83 FND_MESSAGE.SET_TOKEN ('PARAMETER_NAME', p_parameter_name);
84 FND_MESSAGE.SET_TOKEN ('PARAMETER_VALUE', p_currency_code);
85 OE_MSG_PUB.Add;
86 OE_DEBUG_PUB.Add('Validate Currency Failed - Invalid.');
87 l_return_value := FALSE;
88 END;
89 END IF;
90 RETURN l_return_value;
91 OE_DEBUG_PUB.Add('OEXVCECB: Out Is_Currency_Valid');
92 EXCEPTION
93 WHEN OTHERS THEN
94 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
95 END Is_Currency_Valid;
96
97 --=====================================================================
98 -- NAME: Get_Operating_Unit_ID
99 -- TYPE: PRIVATE PROCEDURE
100 -- DESCRIPTION: This procedure validate the operating unit ID if provided.
101 -- else it validate the operating unit name and convert it to the ID.
102 --=====================================================================
103 PROCEDURE Get_Operating_Unit_ID
104 ( p_org_id IN NUMBER
105 , p_operating_unit_name IN VARCHAR2
106 , x_org_id OUT NOCOPY NUMBER
107 , x_return_status OUT NOCOPY VARCHAR2
108 )
109 IS
110 l_multi_org_flag fnd_product_groups.multi_org_flag%TYPE;
111 l_org_id NUMBER;
112 BEGIN
113 OE_DEBUG_PUB.Add('OEXVCECB: In Get_Operating_Unit_ID');
114 x_return_status := FND_API.G_RET_STS_SUCCESS;
115 --If the set is multi-org, then get the operating unit information, else
116 --return NULL for the org_id.
117 SELECT NVL(multi_org_flag, 'N')
118 INTO l_multi_org_flag
119 FROM fnd_product_groups;
120 --
121 --If both the ou name and org ID are provided, the ou name will be ignored.
122 --
123 IF l_multi_org_flag = 'Y' THEN
124 IF p_org_id IS NOT NULL AND p_org_id<>FND_API.G_MISS_NUM THEN
125 BEGIN
126 SELECT organization_id
127 INTO l_org_id
128 FROM hr_operating_units
129 WHERE organization_id = p_org_id;
130 x_org_id := p_org_id;
131 EXCEPTION
132 WHEN NO_DATA_FOUND THEN
133 x_return_status := FND_API.G_RET_STS_ERROR;
134 FND_MESSAGE.Set_Name('ONT', 'OE_CC_ORG_ID_INVALID');
135 FND_MESSAGE.SET_TOKEN ('PARAMETER_NAME', 'P_ORG_ID' );
136 FND_MESSAGE.SET_TOKEN ('PARAMETER_VALUE', p_org_id);
137 OE_MSG_PUB.Add;
138 END;
139 ELSIF p_org_id IS NULL THEN
140 x_return_status := FND_API.G_RET_STS_ERROR;
141 FND_MESSAGE.Set_Name('ONT', 'OE_CC_ORG_PARAMETER_NULL');
142 FND_MESSAGE.SET_TOKEN ('PARAMETER_NAME', 'P_ORG_ID' );
143 OE_MSG_PUB.Add;
144 ELSIF p_operating_unit_name IS NOT NULL AND
145 p_operating_unit_name <> FND_API.G_MISS_CHAR THEN
146 BEGIN
147 SELECT organization_id
148 INTO l_org_id
149 FROM hr_operating_units
150 WHERE name = p_operating_unit_name;
151 x_org_id := l_org_id;
152 EXCEPTION
153 WHEN NO_DATA_FOUND THEN
154 x_return_status := FND_API.G_RET_STS_ERROR;
155 FND_MESSAGE.Set_Name('ONT', 'OE_CC_ORG_NAME_INVALID');
156 FND_MESSAGE.SET_TOKEN ('PARAMETER_NAME', 'P_OPERATION_UNIT_NAME');
157 FND_MESSAGE.SET_TOKEN ('PARAMETER_VALUE', p_operating_unit_name );
158 OE_MSG_PUB.Add;
159 END;
160 ELSIF p_operating_unit_name IS NULL THEN
161 x_return_status := FND_API.G_RET_STS_ERROR;
162 FND_MESSAGE.Set_Name('ONT', 'OE_CC_ORG_PARAMETER_NULL');
163 FND_MESSAGE.SET_TOKEN ('PARAMETER_NAME', 'P_OPERATION_UNIT_NAME' );
164 OE_MSG_PUB.Add;
165 ELSE
166 x_return_status := FND_API.G_RET_STS_ERROR;
167 FND_MESSAGE.Set_Name('ONT', 'OE_CC_ORG_INFO_MISSING');
168 FND_MESSAGE.SET_TOKEN ('P_ORG_NAME', 'P_OPERATION_UNIT_NAME' );
169 FND_MESSAGE.SET_TOKEN ('P_ORG_ID', 'P_ORG_ID' );
170 OE_MSG_PUB.Add;
171 END IF;
172 ELSE
173 x_org_id := NULL;
174 END IF;
175 OE_DEBUG_PUB.Add('OEXVCECB: Out Get_Operating_Unit_ID');
176 EXCEPTION
177 WHEN OTHERS THEN
178 OE_DEBUG_PUB.Add('Get_Operating_Unit_ID: Unexpected Error');
179 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
180 END Get_Operating_Unit_ID;
181
182 --=====================================================================
183 -- NAME: Get_Credit_Check_Rule_ID
184 -- TYPE: PRIVATE PROCEDURE
185 -- DESCRIPTION: This procedure validate the credit check rule ID if provided.
186 -- else it validate the operating unit name and convert it to the ID.
187 --=====================================================================
188 PROCEDURE Get_Credit_Check_Rule_ID
189 ( p_credit_check_rule_id IN NUMBER
190 , p_credit_check_rule_name IN VARCHAR2
191 , x_credit_check_rule_id OUT NOCOPY NUMBER
192 , x_return_status OUT NOCOPY VARCHAR2
193 )
194 IS
195 l_credit_check_rule_id NUMBER := NULL;
196 l_credit_check_rule_name
197 oe_credit_check_rules.name%TYPE := NULL;
198 l_credit_check_level_code
199 oe_credit_check_rules.credit_check_level_code%TYPE := NULL;
200 l_check_item_categories_flag
201 oe_credit_check_rules.check_item_categories_flag%TYPE := NULL;
202 BEGIN
203 OE_DEBUG_PUB.Add('OEXVCECB: In Get_Credit_Check_Rule_ID');
204 x_return_status := FND_API.G_RET_STS_SUCCESS;
205 IF p_credit_check_rule_id IS NOT NULL AND
206 p_credit_check_rule_id <> FND_API.G_MISS_NUM THEN
207 BEGIN
208 SELECT credit_check_rule_id,
209 name,
210 credit_check_level_code,
211 check_item_categories_flag
212 INTO l_credit_check_rule_id,
213 l_credit_check_rule_name,
214 l_credit_check_level_code,
215 l_check_item_categories_flag
216 FROM oe_credit_check_rules
217 WHERE credit_check_rule_id = p_credit_check_rule_id;
218 EXCEPTION
219 WHEN NO_DATA_FOUND THEN
220 x_return_status := FND_API.G_RET_STS_ERROR;
221 FND_MESSAGE.Set_Name('ONT', 'OE_CC_CCR_ID_INVALID');
222 FND_MESSAGE.SET_TOKEN('PARAMETER_NAME', 'P_CREDIT_CHECK_RULE_ID');
223 FND_MESSAGE.SET_TOKEN('PARAMETER_VALUE', p_credit_check_rule_id );
224 OE_MSG_PUB.Add;
225 END;
226 ELSIF p_credit_check_rule_id IS NULL THEN
227 x_return_status := FND_API.G_RET_STS_ERROR;
228 FND_MESSAGE.Set_Name('ONT', 'OE_CC_PARAMETER_NULL');
229 FND_MESSAGE.SET_TOKEN ('PARAMETER_NAME', 'P_CREDIT_CHECK_RULE_ID' );
230 OE_MSG_PUB.Add;
231 ELSIF p_credit_check_rule_name IS NOT NULL AND
232 p_credit_check_rule_name <> FND_API.G_MISS_CHAR THEN
233 BEGIN
234 SELECT credit_check_rule_id,
235 name,
236 credit_check_level_code,
237 check_item_categories_flag
238 INTO l_credit_check_rule_id,
239 l_credit_check_rule_name,
240 l_credit_check_level_code,
241 l_check_item_categories_flag
242 FROM oe_credit_check_rules
243 WHERE name = p_credit_check_rule_name;
244 EXCEPTION
245 WHEN NO_DATA_FOUND THEN
246 x_return_status := FND_API.G_RET_STS_ERROR;
247 FND_MESSAGE.Set_Name('ONT', 'OE_CC_CCR_NAME_INVALID');
248 FND_MESSAGE.SET_TOKEN ('PARAMETER_NAME', 'P_CREDIT_CHECK_RULE_NAME');
249 FND_MESSAGE.SET_TOKEN ('PARAMETER_VALUE', p_credit_check_rule_name );
250 OE_MSG_PUB.Add;
251 END;
252 ELSIF p_credit_check_rule_name IS NULL THEN
253 x_return_status := FND_API.G_RET_STS_ERROR;
254 FND_MESSAGE.Set_Name('ONT', 'OE_CC_PARAMETER_NULL');
255 FND_MESSAGE.SET_TOKEN('PARAMETER_NAME', 'P_CREDIT_CHECK_RULE_NAME');
256 OE_MSG_PUB.Add;
257 ELSE
258 x_return_status := FND_API.G_RET_STS_ERROR;
259 FND_MESSAGE.Set_Name('ONT', 'OE_CC_CCR_INFO_MISSING');
260 FND_MESSAGE.SET_TOKEN ('P_CCR_NAME', 'P_CREDIT_CHECK_RULE_NAME' );
261 FND_MESSAGE.SET_TOKEN ('P_CCR_ID', 'P_CREDIT_CHECK_RULE_ID' );
262 OE_MSG_PUB.Add;
263 END IF;
264 --
265 -- If the credit rule is valid, check that it has the correct flag set.
266 --
267 OE_DEBUG_PUB.Add('l_credit_check_rule_id: '||l_credit_check_rule_id);
268 OE_DEBUG_PUB.Add('l_name : '||l_credit_check_rule_name);
269 OE_DEBUG_PUB.Add('l_check_item_categories_flag: '||l_check_item_categories_flag);
270 OE_DEBUG_PUB.Add('l_credit_check_level_code: '||l_credit_check_level_code);
271 IF l_credit_check_rule_id IS NOT NULL THEN
272 IF NVL(l_credit_check_level_code, 'ORDER') <> 'ORDER' OR
273 NVL(l_check_item_categories_flag,'N') <> 'N' THEN
274 x_return_status := FND_API.G_RET_STS_ERROR;
275 FND_MESSAGE.Set_Name('ONT', 'OE_CC_CCR_INVALID');
276 FND_MESSAGE.Set_Token('CCR_NAME', l_credit_check_rule_name);
277 FND_MESSAGE.Set_Token('API_NAME', 'Check_External_Credit');
278 OE_MSG_PUB.Add;
279 ELSE
280 x_credit_check_rule_id := l_credit_check_rule_id;
281 END IF;
282 END IF;
283 OE_DEBUG_PUB.Add('OEXVCECB: Out Get_Credit_Check_Rule_ID');
284 EXCEPTION
285 WHEN OTHERS THEN
286 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
287 END Get_Credit_Check_Rule_ID;
288
289 --=====================================================================
290 -- NAME: Address_Value_To_ID
291 -- TYPE: PRIVATE FUNCTION
292 -- DESCRIPTION: This function returns the bill_to site use ID given the
293 -- bill-to address and customer information.
294 --=====================================================================
295
296 FUNCTION Address_Value_To_ID
297 ( p_bill_to_address1 IN VARCHAR2
298 , p_bill_to_address2 IN VARCHAR2
299 , p_bill_to_address3 IN VARCHAR2
300 , p_bill_to_address4 IN VARCHAR2
301 , p_customer_id IN NUMBER
302 , p_bill_to_city IN VARCHAR2 DEFAULT NULL
303 , p_bill_to_state IN VARCHAR2 DEFAULT NULL
304 , p_bill_to_postal_code IN VARCHAR2 DEFAULT NULL
305 , p_bill_to_country IN VARCHAR2 DEFAULT NULL
306 ) RETURN NUMBER
307 IS
308 CURSOR c_bill_to_site_use_id IS
309 SELECT /* MOAC_SQL_NO_CHANGE */ ORGANIZATION_ID
310 FROM OE_INVOICE_TO_ORGS_V
311 WHERE ADDRESS_LINE_1 = p_bill_to_address1
312 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
313 nvl( p_bill_to_address2, fnd_api.g_miss_char)
314 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
315 nvl( p_bill_to_address3,fnd_api.g_miss_char)
316 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
317 nvl( p_bill_to_address4,fnd_api.g_miss_char)
318 AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
319 nvl( p_bill_to_city, fnd_api.g_miss_char)
320 AND nvl(STATE,fnd_api.g_miss_char) =
321 nvl( p_bill_to_state, fnd_api.g_miss_char)
322 AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
323 nvl( p_bill_to_postal_code, fnd_api.g_miss_char)
324 AND nvl(COUNTRY,fnd_api.g_miss_char) =
325 nvl( p_bill_to_country, fnd_api.g_miss_char)
326 AND STATUS = 'A'
327 AND CUSTOMER_ID = p_customer_id
328 and address_status='A'; --2752321
329
330 CURSOR C1 IS
331 SELECT /* MOAC_SQL_NO_CHANGE */ ORGANIZATION_ID
332 FROM OE_INVOICE_TO_ORGS_V
333 WHERE ADDRESS_LINE_1 = p_bill_to_address1
334 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
335 nvl( p_bill_to_address2, fnd_api.g_miss_char)
336 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
337 nvl( p_bill_to_address3,fnd_api.g_miss_char)
338 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
339 nvl( p_bill_to_address4,fnd_api.g_miss_char)
340 AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
341 nvl( p_bill_to_city, fnd_api.g_miss_char)
342 AND nvl(STATE,fnd_api.g_miss_char) =
343 nvl( p_bill_to_state, fnd_api.g_miss_char)
344 AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
345 nvl( p_bill_to_postal_code, fnd_api.g_miss_char)
346 AND nvl(COUNTRY,fnd_api.g_miss_char) =
347 nvl( p_bill_to_country, fnd_api.g_miss_char)
348 AND STATUS = 'A'
349 and address_status='A' --2752321
350 AND CUSTOMER_ID IN
351 (
352 SELECT p_customer_id
353 FROM DUAL
354 UNION
355 SELECT CUST_ACCOUNT_ID
356 FROM HZ_CUST_ACCT_RELATE
357 WHERE RELATED_CUST_ACCOUNT_ID = p_customer_id
358 AND bill_to_flag = 'Y');
359
360 CURSOR C2 IS
361 SELECT /* MOAC_SQL_NO_CHANGE */ ORGANIZATION_ID
362 FROM OE_INVOICE_TO_ORGS_V
363 WHERE ADDRESS_LINE_1 = p_bill_to_address1
364 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
365 nvl( p_bill_to_address2, fnd_api.g_miss_char)
366 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
367 nvl( p_bill_to_address3,fnd_api.g_miss_char)
368 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
369 nvl( p_bill_to_address4,fnd_api.g_miss_char)
370 AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
371 nvl( p_bill_to_city, fnd_api.g_miss_char)
372 AND nvl(STATE,fnd_api.g_miss_char) =
373 nvl( p_bill_to_state, fnd_api.g_miss_char)
374 AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
375 nvl( p_bill_to_postal_code, fnd_api.g_miss_char)
376 AND nvl(COUNTRY,fnd_api.g_miss_char) =
377 nvl( p_bill_to_country, fnd_api.g_miss_char)
378 AND STATUS = 'A'
379 and address_status='A'; --2752321
380
381 l_bill_to_site_use_id NUMBER;
382 l_bill_to_site_use_id2 NUMBER;
383 l_customer_relations VARCHAR2(1);
384 l_org varchar2(100);
385 BEGIN
386 OE_DEBUG_PUB.Add('OEXVCECB: In Address_Value_To_ID');
387 OE_DEBUG_PUB.Add('p_customer_id: '||p_customer_id);
388 OE_DEBUG_PUB.Add('p_bill_to_address1: '||p_bill_to_address1);
389 OE_DEBUG_PUB.Add('p_bill_to_address2: '||p_bill_to_address2);
390 OE_DEBUG_PUB.Add('p_bill_to_address3: '||p_bill_to_address3);
391 OE_DEBUG_PUB.Add('p_bill_to_address4: '||p_bill_to_address4);
392 -- Comment out this part as this is not needed for this API
393 --IF p_bill_to_address1 IS NULL
394 -- OR p_bill_to_address2 IS NULL
395 -- OR p_bill_to_address3 IS NULL
396 -- OR p_bill_to_address4 IS NULL
397 --THEN
398 -- RETURN NULL;
399 --END IF;
400
401 l_customer_relations:= OE_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG');
402 OE_DEBUG_PUB.Add('CUSTOMER_RELATIONSHIPS_FLAG: '||l_customer_relations);
403
404 IF l_customer_relations = 'N' THEN
405 OPEN c_bill_to_site_use_id;
406 FETCH c_bill_to_site_use_id
407 INTO l_bill_to_site_use_id;
408
409 IF c_bill_to_site_use_id%FOUND THEN
410 -- Check for more than one site use
411 FETCH c_bill_to_site_use_id
412 INTO l_bill_to_site_use_id2;
413 IF c_bill_to_site_use_id%FOUND THEN
414 RAISE TOO_MANY_ROWS;
415 END IF;
416 CLOSE c_bill_to_site_use_id;
417 RETURN l_bill_to_site_use_id;
418 ELSE
419 SELECT /* MOAC_SQL_NO_CHANGE */ ORGANIZATION_ID
420 INTO l_bill_to_site_use_id
421 FROM OE_INVOICE_TO_ORGS_V
422 WHERE ADDRESS_LINE_1 = p_bill_to_address1
423 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
424 nvl( p_bill_to_address2, fnd_api.g_miss_char)
425 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
426 nvl( p_bill_to_address3,fnd_api.g_miss_char)
427 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
428 DECODE(STATE, NULL, NULL, STATE || ', ')||
429 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
430 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
431 NVL( p_bill_to_address4, fnd_api.g_miss_char)
432 AND STATUS = 'A'
433 AND CUSTOMER_ID = p_customer_id
434 and address_status='A'; --2752321;
435 END IF;
436
437 CLOSE c_bill_to_site_use_id;
438 RETURN l_bill_to_site_use_id;
439
440 ELSIF l_customer_relations = 'Y' THEN
441 OPEN C1;
442 FETCH C1
443 INTO l_bill_to_site_use_id;
444
445 IF C1%FOUND then
446 OE_DEBUG_PUB.Add('Found');
447 -- Check for more than one site use
448 FETCH C1
449 INTO l_bill_to_site_use_id2;
450 IF C1%FOUND THEN
451 RAISE TOO_MANY_ROWS;
452 END IF;
453 CLOSE C1;
454 RETURN l_bill_to_site_use_id;
455 ELSE
456 oe_debug_pub.add('not found');
457 -- comment out the following call for MOAC
458 -- select userenv('CLIENT_INFO') into l_org from dual;
459 -- oe_debug_pub.add('org='||l_org);
460
461 SELECT /* MOAC_SQL_NO_CHANGE */ ORGANIZATION_ID
462 INTO l_bill_to_site_use_id
463 FROM OE_INVOICE_TO_ORGS_V
464 WHERE ADDRESS_LINE_1 = p_bill_to_address1
465 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
466 nvl( p_bill_to_address2, fnd_api.g_miss_char)
467 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
468 nvl( p_bill_to_address3,fnd_api.g_miss_char)
469 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
470 DECODE(STATE, NULL, NULL, STATE || ', ')||
471 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
472 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
473 nvl( p_bill_to_address4, fnd_api.g_miss_char)
474 AND STATUS = 'A'
475 and address_status='A' --2752321
476 AND CUSTOMER_ID IN
477 (SELECT p_customer_id
478 FROM DUAL
479 UNION
480 SELECT CUST_ACCOUNT_ID
481 FROM HZ_CUST_ACCT_RELATE
482 WHERE RELATED_CUST_ACCOUNT_ID = p_customer_id
483 AND bill_to_flag = 'Y');
484 oe_debug_pub.add('after select found='||l_bill_to_site_use_id);
485 END IF;
486
487 CLOSE C1;
488 oe_debug_pub.add('returning from the function');
489 RETURN l_bill_to_site_use_id;
490 ELSIF l_customer_relations = 'A' THEN
491 OPEN C2;
492 FETCH C2
493 INTO l_bill_to_site_use_id;
494
495 IF C2%FOUND then
496 -- Check for more than one site use
497 FETCH C2
498 INTO l_bill_to_site_use_id2;
499 IF C2%FOUND THEN
500 RAISE TOO_MANY_ROWS;
501 END IF;
502 CLOSE C2;
503 RETURN l_bill_to_site_use_id;
504 ELSE
505 SELECT ORGANIZATION_ID
506 INTO l_bill_to_site_use_id
507 FROM OE_INVOICE_TO_ORGS_V
508 WHERE ADDRESS_LINE_1 = p_bill_to_address1
509 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
510 nvl( p_bill_to_address2, fnd_api.g_miss_char)
511 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
512 nvl( p_bill_to_address3,fnd_api.g_miss_char)
513 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
514 DECODE(STATE, NULL, NULL, STATE || ', ')||
515 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
516 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
517 NVL( p_bill_to_address4, fnd_api.g_miss_char)
518 AND STATUS = 'A'
519 and address_status='A'; --2752321
520 END IF;
521
522 CLOSE C2;
523 RETURN l_bill_to_site_use_id;
524 END IF;
525
526 EXCEPTION
527 WHEN NO_DATA_FOUND THEN
528 IF c_bill_to_site_use_id%ISOPEN then
529 CLOSE c_bill_to_site_use_id;
530 END IF;
531
532 IF C1%ISOPEN then
533 CLOSE C1;
534 END IF;
535
536 IF C2%ISOPEN then
537 CLOSE C2;
538 END IF;
539
540 FND_MESSAGE.SET_NAME('ONT','OE_CC_BILL_TO_ADDRESS_INVALID');
541 OE_MSG_PUB.Add;
542 OE_DEBUG_PUB.Add('No data found error in Address_Value_To_ID');
543 RETURN FND_API.G_MISS_NUM;
544 WHEN TOO_MANY_ROWS THEN
545 IF c_bill_to_site_use_id%ISOPEN then
546 CLOSE c_bill_to_site_use_id;
547 END IF;
548
549 IF C1%ISOPEN then
550 CLOSE C1;
551 END IF;
552
553 IF C2%ISOPEN then
554 CLOSE C2;
555 END IF;
556
557 FND_MESSAGE.SET_NAME('ONT','OE_CC_BILL_TO_ADDRESS_MULTI');
558 OE_MSG_PUB.Add;
559 OE_DEBUG_PUB.Add('Too many rows error in Address_Value_To_ID');
560 RETURN FND_API.G_MISS_NUM;
561 WHEN OTHERS THEN
562 OE_DEBUG_PUB.Add('Unexpected error in Address_Value_To_ID');
563 IF c_bill_to_site_use_id%ISOPEN then
564 CLOSE c_bill_to_site_use_id;
565 END IF;
566
567 IF C1%ISOPEN then
568 CLOSE C1;
569 END IF;
570
571 IF C2%ISOPEN then
572 CLOSE C2;
573 END IF;
574
575 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
576 OE_MSG_PUB.Add_Exc_Msg
577 ( G_PKG_NAME
578 , 'Address_Value_To_ID'
579 );
580 END IF;
581 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
582 END Address_Value_To_ID;
583
584 --=====================================================================
585 -- NAME: Get_Bill_To_Site_Use_ID
586 -- TYPE: PRIVATE PROCEDURE
587 -- DESCRIPTION: This procedure validate the bill-to site use ID if provided.
588 -- else it validate the other parameters and derive the site use ID.
589 --=====================================================================
590 PROCEDURE Get_Bill_To_Site_Use_ID
591 ( p_bill_to_site_use_id IN NUMBER
592 ,p_customer_name IN VARCHAR2
593 ,p_customer_number IN VARCHAR2
594 ,p_customer_id IN NUMBER
595 ,p_bill_to_address1 IN VARCHAR2
596 ,p_bill_to_address2 IN VARCHAR2
597 ,p_bill_to_address3 IN VARCHAR2
598 ,p_bill_to_address4 IN VARCHAR2
599 ,p_bill_to_city IN VARCHAR2
600 ,p_bill_to_country IN VARCHAR2
601 ,p_bill_to_postal_code IN VARCHAR2
602 ,p_bill_to_state IN VARCHAR2
603 ,p_bill_to_county IN VARCHAR2
604 ,p_bill_to_province IN VARCHAR2
605 ,p_org_id IN NUMBER
606 ,x_return_status OUT NOCOPY VARCHAR2
607 ,x_bill_to_site_use_id OUT NOCOPY NUMBER
608 )
609 IS
610 l_bill_to_site_use_id NUMBER;
611 l_customer_id NUMBER;
612 l_bill_to_state VARCHAR2(60);
613 BEGIN
614 OE_DEBUG_PUB.Add('OEXVCECB: In Get_Bill_To_Site_Use_ID');
615 x_return_status := FND_API.G_RET_STS_SUCCESS;
616
617 --bug4933291
618 OE_DEBUG_PUB.Add('p_org_id: '||p_org_id);
619 OE_DEBUG_PUB.Add('p_bill_to_site_use_id: '||p_bill_to_site_use_id);
620
621 IF p_bill_to_site_use_id IS NOT NULL AND
622 p_bill_to_site_use_id <> FND_API.G_MISS_NUM THEN
623 BEGIN
624 SELECT site_use_id
625 INTO l_bill_to_site_use_id
626 FROM hz_cust_site_uses_all
627 WHERE site_use_id = p_bill_to_site_use_id
628 AND site_use_code = 'BILL_TO'
629 AND NVL(org_id, -99) = NVL(p_org_id, -99);
630 EXCEPTION
631 WHEN NO_DATA_FOUND THEN
632 -- This same error message takes care of the case of NULL value
633 -- passed in since a NULL in the select will not select any rows.
634 x_return_status := FND_API.G_RET_STS_ERROR;
635 FND_MESSAGE.Set_Name('ONT', 'OE_CC_SITE_USE_ID_INVALID');
636 FND_MESSAGE.Set_Token('PARAMETER_NAME', 'P_BILL_TO_SITE_USE_ID');
637 FND_MESSAGE.Set_Token('PARAMETER_VALUE', p_bill_to_site_use_id );
638 OE_MSG_PUB.Add;
639 END;
640 ELSIF p_bill_to_site_use_id IS NULL THEN
641 x_return_status := FND_API.G_RET_STS_ERROR;
642 FND_MESSAGE.Set_Name('ONT', 'OE_CC_PARAMETER_NULL');
643 FND_MESSAGE.SET_TOKEN ('PARAMETER_NAME', 'P_BILL_TO_SITE_USE_ID' );
644 OE_MSG_PUB.Add;
645
646 ELSIF
647 -- The location and customer information must be provided to derive a
648 -- unique site use id.
649 -- Note that county is not used for ID derivation since it is not used
650 -- by order import either.
651 (p_bill_to_address1 <> FND_API.G_MISS_CHAR OR
652 p_bill_to_address2 <> FND_API.G_MISS_CHAR OR
653 p_bill_to_address3 <> FND_API.G_MISS_CHAR OR
654 p_bill_to_address4 <> FND_API.G_MISS_CHAR OR
655 p_bill_to_city <> FND_API.G_MISS_CHAR OR
656 p_bill_to_country <> FND_API.G_MISS_CHAR OR
657 p_bill_to_postal_code <> FND_API.G_MISS_CHAR OR
658 p_bill_to_state <> FND_API.G_MISS_CHAR OR
659 p_bill_to_province <> FND_API.G_MISS_CHAR)
660 THEN
661 -- Determine the l_bill_to_state value to pass to Address_Value_To_ID
662 -- function. If the state parameter is provided, pass it, else pass the
663 -- province information (bug 2346992).
664 IF NVL(p_bill_to_state,FND_API.G_MISS_CHAR)=FND_API.G_MISS_CHAR THEN
665 l_bill_to_state := p_bill_to_province;
666 ELSE
667 l_bill_to_state := p_bill_to_state;
668 END IF;
669
670 -- location information exists, now check for a valid customer.
671 IF p_customer_id IS NOT NULL AND p_customer_id <> FND_API.G_MISS_NUM THEN
672 l_bill_to_site_use_id := Address_Value_To_ID(
673 p_bill_to_address1 => p_bill_to_address1,
674 p_bill_to_address2 => p_bill_to_address2,
675 p_bill_to_address3 => p_bill_to_address3,
676 p_bill_to_address4 => p_bill_to_address4,
677 p_customer_id => p_customer_id,
678 p_bill_to_city => p_bill_to_city,
679 p_bill_to_state => l_bill_to_state,
680 p_bill_to_postal_code => p_bill_to_postal_code,
681 p_bill_to_country => p_bill_to_country
682 );
683 OE_DEBUG_PUB.Add('l_bill_to_site_use_id: '||l_bill_to_site_use_id);
684 IF NVL(l_bill_to_site_use_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM THEN
685 x_return_status := FND_API.G_RET_STS_ERROR;
686 END IF;
687 ELSIF p_customer_id IS NULL THEN
688 -- set error message
689 x_return_status := FND_API.G_RET_STS_ERROR;
690 FND_MESSAGE.Set_Name('ONT', 'OE_CC_PARAMETER_NULL');
691 FND_MESSAGE.SET_TOKEN ('PARAMETER_NAME', 'P_CUSTOMER_ID' );
692 OE_MSG_PUB.Add;
693 ELSIF p_customer_name <> FND_API.G_MISS_CHAR AND
694 p_customer_number <> FND_API.G_MISS_CHAR THEN
695 -- check for valid customer_id
696 BEGIN
697 SELECT hca.cust_account_id
698 INTO l_customer_id
699 FROM hz_cust_accounts hca,
700 hz_parties hp
701 WHERE hca.party_id = hp.party_id
702 AND hp.party_name = p_customer_name
703 AND hca.account_number = p_customer_number;
704 --
705 -- then get bill_to_site_use_id
706 --
707 l_bill_to_site_use_id := Address_Value_To_ID(
708 p_bill_to_address1 => p_bill_to_address1,
709 p_bill_to_address2 => p_bill_to_address2,
710 p_bill_to_address3 => p_bill_to_address3,
711 p_bill_to_address4 => p_bill_to_address4,
712 p_customer_id => l_customer_id,
713 p_bill_to_city => p_bill_to_city,
714 p_bill_to_state => l_bill_to_state,
715 p_bill_to_postal_code => p_bill_to_postal_code,
716 p_bill_to_country => p_bill_to_country
717 );
718 OE_DEBUG_PUB.Add('l_bill_to_site_use_id: '||l_bill_to_site_use_id);
719 IF NVL(l_bill_to_site_use_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM THEN
720 x_return_status := FND_API.G_RET_STS_ERROR;
721 END IF;
722 EXCEPTION
723 WHEN NO_DATA_FOUND THEN
724 x_return_status := FND_API.G_RET_STS_ERROR;
725 FND_MESSAGE.Set_Name('ONT', 'OE_CC_CUSTOMER_INFO_INVALID');
726 FND_MESSAGE.SET_TOKEN ('P_CUSTOMER_NAME', 'P_CUSTOMER_NAME' );
727 FND_MESSAGE.SET_TOKEN ('P_CUSTOMER_NUMBER', 'P_CUSTOMER_NUMBER' );
728 OE_MSG_PUB.Add;
729 END;
730 ELSE
731 -- customer information is missing
732 x_return_status := FND_API.G_RET_STS_ERROR;
733 FND_MESSAGE.Set_Name('ONT', 'OE_CC_CUSTOMER_INFO_MISSING');
734 FND_MESSAGE.SET_TOKEN ('P_CUSTOMER_NAME', 'P_CUSTOMER_NAME' );
735 FND_MESSAGE.SET_TOKEN ('P_CUSTOMER_NUMBER', 'P_CUSTOMER_NUMBER' );
736 FND_MESSAGE.SET_TOKEN ('P_CUSTOMER_ID', 'P_CUSTOMER_ID');
737 OE_MSG_PUB.Add;
738 END IF;
739 ELSE
740 -- insufficient information is provided to derive the invoice site use id.
741 -- refer use to API documentation. Either the bill_to site use ID needs
742 -- to be provided or the bill-to address.
743 x_return_status := FND_API.G_RET_STS_ERROR;
744 FND_MESSAGE.Set_Name('ONT', 'OE_CC_SITE_USE_INFO_INVALID');
745 OE_MSG_PUB.Add;
746 END IF;
747 x_bill_to_site_use_id := l_bill_to_site_use_id;
748 OE_DEBUG_PUB.Add('OEXVCECB: Out Get_Bill_To_Site_Use_ID');
749 EXCEPTION
750 WHEN OTHERS THEN
751 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
752 END Get_Bill_To_Site_Use_ID;
753
754 --=====================================================================
755 --API NAME: Check_External_Credit
756 --TYPE: PRIVATE
757 --DESCRIPTION: This procedure validate the input parameters given to the
758 -- Check External Credit API and calls the credit check engine.
759 --Parameters:
760 --IN
761 --OUT
762 --Version: Current Version 1.0
763 -- Previous Version 1.0
764 --=====================================================================
765 PROCEDURE Check_External_Credit
766 ( p_api_version IN NUMBER
767 , p_init_msg_list IN VARCHAR2 := FND_API.G_FALSE
768 , x_return_status OUT NOCOPY VARCHAR2
769 , x_msg_count OUT NOCOPY NUMBER
770 , x_msg_data OUT NOCOPY VARCHAR2
771 , p_customer_name IN VARCHAR2 := FND_API.G_MISS_CHAR
772 , p_customer_number IN VARCHAR2 := FND_API.G_MISS_CHAR
773 , p_customer_id IN NUMBER := FND_API.G_MISS_NUM
774 , p_bill_to_site_use_id IN NUMBER := FND_API.G_MISS_NUM
775 , p_bill_to_address1 IN VARCHAR2 := FND_API.G_MISS_CHAR
776 , p_bill_to_address2 IN VARCHAR2 := FND_API.G_MISS_CHAR
777 , p_bill_to_address3 IN VARCHAR2 := FND_API.G_MISS_CHAR
778 , p_bill_to_address4 IN VARCHAR2 := FND_API.G_MISS_CHAR
779 , p_bill_to_city IN VARCHAR2 := FND_API.G_MISS_CHAR
780 , p_bill_to_country IN VARCHAR2 := FND_API.G_MISS_CHAR
781 , p_bill_to_postal_code IN VARCHAR2 := FND_API.G_MISS_CHAR
782 , p_bill_to_state IN VARCHAR2 := FND_API.G_MISS_CHAR
783 , p_bill_to_county IN VARCHAR2 := FND_API.G_MISS_CHAR
784 , p_bill_to_province IN VARCHAR2 := FND_API.G_MISS_CHAR
785 , p_credit_check_rule_name IN VARCHAR2 := FND_API.G_MISS_CHAR
786 , p_credit_check_rule_id IN NUMBER := FND_API.G_MISS_NUM
787 , p_functional_currency_code IN VARCHAR2
788 , p_transaction_currency_code IN VARCHAR2
789 , p_transaction_amount IN NUMBER
790 , p_operating_unit_name IN VARCHAR2 := FND_API.G_MISS_CHAR
791 , p_org_id IN NUMBER := FND_API.G_MISS_NUM
792 , x_result_out OUT NOCOPY VARCHAR2
793 , x_cc_hold_comment OUT NOCOPY VARCHAR2
794 )
795 IS
796 l_api_name CONSTANT VARCHAR2(30) := 'Check_External_Credit';
797 l_api_version CONSTANT NUMBER := 1.0;
798 l_any_errors BOOLEAN := FALSE;
799 l_return_status VARCHAR2(30);
800 l_org_id NUMBER;
801 l_bill_to_site_use_id NUMBER;
802 l_credit_check_rule_id NUMBER;
803 BEGIN
804 OE_DEBUG_PUB.Add('OEXVCECB: In Check_External_Credit');
805 -- Initialize API return status to success
806 x_return_status := FND_API.G_RET_STS_SUCCESS;
807 -- Check the API version and issue an error if the given API version does not
808 -- match the one in this package.
809 IF NOT FND_API.Compatible_API_Call( l_api_version,
810 p_api_version,
811 l_api_name,
812 G_PKG_NAME)
813 THEN
814 FND_MSG_PUB.Delete_Msg;
815 FND_MESSAGE.Set_Name('ONT', 'OE_CC_API_VERSION_MISMATCH');
816 FND_MESSAGE.SET_TOKEN ('API_NAME', l_api_name );
817 FND_MESSAGE.SET_TOKEN ('P_API_VERSION', p_api_version );
818 FND_MESSAGE.SET_TOKEN ('CURR_VER_NUM',l_api_version);
819 FND_MESSAGE.SET_TOKEN ('CALLER_VER_NUM',p_api_version);
820 OE_MSG_PUB.Add;
821 OE_DEBUG_PUB.Add('l_api_version: '||l_api_version);
822 OE_DEBUG_PUB.Add('p_api_version: '||p_api_version);
823 OE_DEBUG_PUB.Add('API Versin Check Failed.');
824 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
825 END IF;
826 --
827 -- Show input parameters
828 --
829 OE_DEBUG_PUB.Add('*****API Input Parameters*****');
830 OE_DEBUG_PUB.Add('p_credit_check_rule_name: '||p_credit_check_rule_name);
831 OE_DEBUG_PUB.Add('p_credit_check_rule_id: '||p_credit_check_rule_id);
832 OE_DEBUG_PUB.Add('p_operating_unit_name: '||p_operating_unit_name);
833 OE_DEBUG_PUB.Add('p_org_id: '||p_org_id);
834 OE_DEBUG_PUB.Add('p_functional_currency_code: '||p_functional_currency_code);
835 OE_DEBUG_PUB.Add('p_transaction_currency_code: '||p_transaction_currency_code);
836 OE_DEBUG_PUB.Add('p_transaction_amount: '||p_transaction_amount);
837 OE_DEBUG_PUB.Add('p_customer_name: '||p_customer_name);
838 OE_DEBUG_PUB.Add('p_customer_number: '||p_customer_number);
839 OE_DEBUG_PUB.Add('p_customer_id: '||p_customer_id);
840 OE_DEBUG_PUB.Add('p_bill_to_site_use_id: '||p_bill_to_site_use_id);
841 OE_DEBUG_PUB.Add('p_bill_to_address1: '||p_bill_to_address1);
842 OE_DEBUG_PUB.Add('p_bill_to_address2: '||p_bill_to_address2);
843 OE_DEBUG_PUB.Add('p_bill_to_address3: '||p_bill_to_address3);
844 OE_DEBUG_PUB.Add('p_bill_to_address4: '||p_bill_to_address4);
845 OE_DEBUG_PUB.Add('p_bill_to_city: '||p_bill_to_city);
846 OE_DEBUG_PUB.Add('p_bill_to_state: '||p_bill_to_state);
847 OE_DEBUG_PUB.Add('p_bill_to_postal_code: '||p_bill_to_postal_code);
848 OE_DEBUG_PUB.Add('p_bill_to_country: '||p_bill_to_country);
849 OE_DEBUG_PUB.Add('p_bill_to_county: '||p_bill_to_county);
850 OE_DEBUG_PUB.Add('p_bill_to_province: '||p_bill_to_province);
851
852
853 -- Validate the transaction amount parameter
854 IF NOT Is_Amount_Valid(p_amount => p_transaction_amount) AND
855 NOT l_any_errors THEN
856 l_any_errors := TRUE;
857 OE_DEBUG_PUB.Add('Validate Amount Failed.');
858 END IF;
859
860 -- Validate the currency parameters
861 IF NOT Is_Currency_Valid(p_currency_code => p_transaction_currency_code,
862 p_parameter_name => 'P_TRANSACTION_CURRENCY_CODE')
863 AND
864 NOT l_any_errors THEN
865 l_any_errors := TRUE;
866 OE_DEBUG_PUB.Add('Validate Transaction Currency Failed.');
867 END IF;
868
869 IF NOT Is_Currency_Valid(p_currency_code => p_functional_currency_code,
870 p_parameter_name => 'P_FUNCTIONAL_CURRENCY_CODE')
871 AND
872 NOT l_any_errors THEN
873 l_any_errors := TRUE;
874 OE_DEBUG_PUB.Add('Validate Functional Currency Failed.');
875 END IF;
876
877 -- Validate the credit check rule parameters
878 Get_Credit_Check_Rule_ID(
879 p_credit_check_rule_id => p_credit_check_rule_id
880 , p_credit_check_rule_name => p_credit_check_rule_name
881 , x_credit_check_rule_id => l_credit_check_rule_id
882 , x_return_status => l_return_status
883 );
884 OE_DEBUG_PUB.Add('l_credit_check_rule_id: '||l_credit_check_rule_id);
885 IF l_return_status <> FND_API.G_RET_STS_SUCCESS AND
886 NOT l_any_errors THEN
887 l_any_errors := TRUE;
888 OE_DEBUG_PUB.Add('Validate Credit Check Rule Failed.');
889 END IF;
890
891 -- Validate the operating unit parameters
892 -- MOAC related changes, comment out the code per discussion
893 -- with Sam
894 /*
895 Get_Operating_Unit_ID(
896 p_org_id => p_org_id
897 , p_operating_unit_name => p_operating_unit_name
898 , x_org_id => l_org_id
899 , x_return_status => l_return_status
900 );
901 OE_DEBUG_PUB.Add('l_return_status: '||l_return_status);
902 OE_DEBUG_PUB.Add('l_org_id: '|| l_org_id);
903 */
904
905 -- dbms_application_info.set_client_info(l_org_id);
906 -- MO_GLOBAL.set_policy_context ('S', p_org_id); --bug4933291
907
908 --bug4933291
909 IF p_org_id IS NOT NULL THEN
910 MO_GLOBAL.Set_Policy_Context('S',p_org_id);
911 l_org_id := p_org_id;
912 OE_DEBUG_PUB.Add('Context is set for org_id : '|| l_org_id);
913 ELSE
914 l_return_status := FND_API.G_RET_STS_ERROR;
915 OE_DEBUG_PUB.Add('Could not set org context');
916 END IF;
917
918 IF l_return_status <> FND_API.G_RET_STS_SUCCESS AND
919 NOT l_any_errors THEN
920 l_any_errors := TRUE;
921 OE_DEBUG_PUB.Add('Validate Operating Unit Failed.');
922 END IF;
923
924 -- Validate the location parameters
925 Get_Bill_To_Site_Use_ID
926 ( p_bill_to_site_use_id => p_bill_to_site_use_id
927 ,p_customer_name => p_customer_name
928 ,p_customer_number => p_customer_number
929 ,p_customer_id => p_customer_id
930 ,p_bill_to_address1 => p_bill_to_address1
931 ,p_bill_to_address2 => p_bill_to_address2
932 ,p_bill_to_address3 => p_bill_to_address3
933 ,p_bill_to_address4 => p_bill_to_address4
934 ,p_bill_to_city => p_bill_to_city
935 ,p_bill_to_country => p_bill_to_country
936 ,p_bill_to_postal_code => p_bill_to_postal_code
937 ,p_bill_to_state => p_bill_to_state
938 ,p_bill_to_county => p_bill_to_county
939 ,p_bill_to_province => p_bill_to_province
940 ,p_org_id => l_org_id
941 ,x_return_status => l_return_status
942 ,x_bill_to_site_use_id => l_bill_to_site_use_id
943 );
944
945 OE_DEBUG_PUB.Add('l_bill_to_site_use_id: '|| l_bill_to_site_use_id);
946 IF l_return_status <> FND_API.G_RET_STS_SUCCESS AND
947 NOT l_any_errors THEN
948 l_any_errors := TRUE;
949 OE_DEBUG_PUB.Add('Validate Bill-To Site Use Failed.');
950 END IF;
951
952 --
953 -- If the are any errors encountered during validation of the parameters
954 -- raise error and error messages and do not continue with credit checking.
955 --
956 IF l_any_errors THEN
957 RAISE FND_API.G_EXC_ERROR;
958 END IF;
959 --
960 -- Call the OM credit check engine to perform credit checking
961
962 OE_Credit_Engine_GRP.Check_Credit (
963 x_return_status => x_return_status
964 , x_msg_count => x_msg_count
965 , x_msg_data => x_msg_data
966 , p_header_id => NULL
967 , p_calling_action => 'EXTERNAL'
968 , p_bill_to_site_use_id => l_bill_to_site_use_id
969 , p_credit_check_rule_id => l_credit_check_rule_id
970 , p_functional_currency_code => p_functional_currency_code
971 , p_transaction_currency_code => p_transaction_currency_code
972 , p_transaction_amount => p_transaction_amount
973 , p_org_id => l_org_id
974 , x_result_out => x_result_out
975 , x_cc_hold_comment => x_cc_hold_comment
976 );
977
978 OE_DEBUG_PUB.Add('Check_Credit Results');
979 OE_DEBUG_PUB.Add('x_return_status: '|| x_return_status);
980 OE_DEBUG_PUB.Add('x_result_out: '|| x_result_out);
981 OE_DEBUG_PUB.Add('x_cc_hold_comment: '|| x_cc_hold_comment);
982 OE_DEBUG_PUB.Add('OEXVCECB: Out Check_External_Credit');
983 EXCEPTION
984 WHEN FND_API.G_EXC_ERROR THEN
985 x_return_status := FND_API.G_RET_STS_ERROR;
986 OE_MSG_PUB.Count_and_Get (
987 p_count => x_msg_count
988 ,p_data => x_msg_data);
989 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
990 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
991 OE_MSG_PUB.Count_and_Get (
992 p_count => x_msg_count
993 ,p_data => x_msg_data);
994 WHEN OTHERS THEN
995 x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
996 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
997 OE_MSG_PUB.Add_Exc_Msg (
998 G_PKG_NAME
999 , l_api_name);
1000 END IF;
1001 OE_MSG_PUB.Count_and_Get(
1002 p_count => x_msg_count
1003 ,p_data => x_msg_data);
1004 END Check_External_Credit;
1005 END OE_EXTERNAL_CREDIT_PVT;