[Home] [Help]
PACKAGE BODY: APPS.OE_EXT_CREDIT_EXPOSURE_PVT
Source
1 PACKAGE BODY OE_EXT_CREDIT_EXPOSURE_PVT AS
2 -- $Header: OEXVECEB.pls 120.4 2008/02/13 10:11:41 vybhatia ship $
3 --------------------
4 -- TYPE DECLARATIONS
5 --------------------
6
7 ------------
8 -- CONSTANTS
9 ------------
10 G_PKG_NAME CONSTANT VARCHAR2(30) := 'OE_Ext_Credit_Exposure_PVT';
11 G_COMMIT_SIZE CONSTANT NUMBER := 10000;
12 G_ERROR CONSTANT VARCHAR2(30) := 'ERROR';
13 G_VALIDATED CONSTANT VARCHAR2(30) := 'VALIDATED';
14 G_PROCESSING CONSTANT VARCHAR2(30) := 'PROCESSING';
15 G_COMPLETE CONSTANT VARCHAR2(30) := 'COMPLETE';
16 -------------------
17 -- GLOBAL VARIABLES
18 -------------------
19 G_request_id NUMBER := FND_GLOBAL.CONC_REQUEST_ID;
20 G_appl_id NUMBER := FND_GLOBAL.PROG_APPL_ID;
21 G_program_id NUMBER := FND_GLOBAL.CONC_PROGRAM_ID;
22 G_user_id NUMBER := FND_GLOBAL.USER_ID;
23 G_login_id NUMBER := FND_GLOBAL.CONC_LOGIN_ID;
24 G_org_id NUMBER := mo_global.get_current_org_id; -- MOAC Changes TO_NUMBER(FND_PROFILE.value('ORG_ID'));
25
26 ---------------------------
27 -- PROCEDURES AND FUNCTIONS
28 ---------------------------
29 --
30 --=====================================================================
31 --NAME: Insert_In_Errors_Table
32 --TYPE: PRIVATE
33 --DESCRIPTION: This procedure insert a row in the OE_EXP_INTERFACE_ERRORS
34 -- table.
35 --Parameters:
36 --IN
37 --OUT
38 --=====================================================================
39 PROCEDURE Insert_To_Errors_Table
40 ( p_exposure_source_code IN VARCHAR2
41 , p_batch_id IN NUMBER
42 , p_exposure_interface_id IN NUMBER
43 , p_error_message_name IN VARCHAR2
44 , p_error_message_text IN VARCHAR2
45 )
46 IS
47 BEGIN
48 OE_DEBUG_PUB.Add('OEXVECEB: In Insert_To_Errors_Table');
49 --
50 -- Insert row into OE_EXP_INTERFACE_ERRORS table
51 --
52 INSERT INTO OE_EXP_INTERFACE_ERRORS (
53 EXPOSURE_SOURCE_CODE
54 , EXPOSURE_INTERFACE_ID
55 , BATCH_ID
56 , ERROR_MESSAGE_NAME
57 , ERROR_MESSAGE
58 , CREATED_BY
59 , CREATION_DATE
60 , LAST_UPDATED_BY
61 , LAST_UPDATE_DATE
62 , LAST_UPDATE_LOGIN
63 , PROGRAM_APPLICATION_ID
64 , PROGRAM_ID
65 , PROGRAM_UPDATE_DATE
66 , REQUEST_ID
67 )
68 VALUES (
69 p_exposure_source_code
70 , p_exposure_interface_id
71 , p_batch_id
72 , p_error_message_name
73 , p_error_message_text
74 , G_user_id
75 , SYSDATE
76 , G_user_id
77 , SYSDATE
78 , G_login_id
79 , G_appl_id
80 , G_program_id
81 , SYSDATE
82 , G_request_id
83 );
84 OE_DEBUG_PUB.Add('OEXVECEB: Out Insert_To_Errors_Table');
85 EXCEPTION
86 WHEN OTHERS THEN
87 OE_DEBUG_PUB.Add('Insert_To_Errors_Table: Unexpected Error');
88 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
89 END;
90
91 --=====================================================================
92 -- NAME: Is_Currency_Valid
93 -- TYPE: PRIVATE FUNCTION
94 -- DESCRIPTION: This function returns TRUE if the currency code is a
95 -- valid currency code and FALSE otherwise.
96 --=====================================================================
97 FUNCTION Is_Currency_Valid
98 ( p_exposure_rec IN oe_exposure_interface%ROWTYPE
99 )
100 RETURN BOOLEAN
101 IS
102 l_return_value BOOLEAN := TRUE;
103 l_curr_valid NUMBER;
104 l_message_text VARCHAR2(2000);
105 BEGIN
106 OE_DEBUG_PUB.Add('OEXVECEB: In Is_Currency_Valid');
107 BEGIN
108 SELECT 1
109 INTO l_curr_valid
110 FROM fnd_currencies
111 WHERE currency_code = p_exposure_rec.currency_code
112 AND enabled_flag = 'Y'
113 AND NVL(start_date_active, TO_DATE('01/01/1000','DD/MM/YYYY'))
114 <= TRUNC(SYSDATE)
115 AND NVL(end_date_active, TO_DATE('31/12/9999','DD/MM/YYYY'))
116 >= TRUNC(SYSDATE) ;
117 EXCEPTION
118 WHEN NO_DATA_FOUND THEN
119 OE_DEBUG_PUB.Add('Validate Currency Failed - Invalid.', 5);
120 FND_MESSAGE.Set_Name('ONT', 'OE_CC_IMP_CURRENCY_INVALID');
121 FND_MESSAGE.SET_TOKEN ('COLUMN_NAME','CURRENCY_CODE');
122 FND_MESSAGE.SET_TOKEN ('COLUMN_VALUE', p_exposure_rec.currency_code);
123 l_message_text := FND_MESSAGE.GET;
124 OE_DEBUG_PUB.Add('Error: Currency invalid', 5);
125 Insert_To_Errors_Table(
126 p_exposure_source_code => p_exposure_rec.exposure_source_code
127 , p_exposure_interface_id => p_exposure_rec.exposure_interface_id
128 , p_batch_id => p_exposure_rec.batch_id
129 , p_error_message_name => 'OE_CC_IMP_CURRENCY_INVALID'
130 , p_error_message_text => l_message_text
131 );
132 l_return_value := FALSE;
133 END;
134 OE_DEBUG_PUB.Add('OEXVECEB: Out Is_Currency_Valid');
135 RETURN l_return_value;
136 EXCEPTION
137 WHEN OTHERS THEN
138 OE_DEBUG_PUB.Add('Is_Currency_Valid: Unexpected Error');
139 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
140 END Is_Currency_Valid;
141
142 --=====================================================================
143 -- NAME: Address_Value_To_ID
144 -- TYPE: PRIVATE FUNCTION
145 -- DESCRIPTION: This function returns the bill_to site use ID given the
146 -- bill-to address and customer information.
147 --=====================================================================
148
149 FUNCTION Address_Value_To_ID
150 ( p_exposure_rec IN oe_exposure_interface%ROWTYPE
151 ) RETURN NUMBER
152 IS
153
154 CURSOR c_bill_to_site_use_id (p_bill_to_state VARCHAR2) IS
155 SELECT ORGANIZATION_ID
156 FROM OE_INVOICE_TO_ORGS_V
157 WHERE ADDRESS_LINE_1 = p_exposure_rec.bill_to_address1
158 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
159 nvl( p_exposure_rec.bill_to_address2, fnd_api.g_miss_char)
160 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
161 nvl( p_exposure_rec.bill_to_address3,fnd_api.g_miss_char)
162 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
163 nvl( p_exposure_rec.bill_to_address4,fnd_api.g_miss_char)
164 AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
165 nvl( p_exposure_rec.bill_to_city, fnd_api.g_miss_char)
166 AND nvl(STATE,fnd_api.g_miss_char) =
167 nvl( p_bill_to_state, fnd_api.g_miss_char)
168 AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
169 nvl( p_exposure_rec.bill_to_postal_code, fnd_api.g_miss_char)
170 AND nvl(COUNTRY,fnd_api.g_miss_char) =
171 nvl( p_exposure_rec.bill_to_country, fnd_api.g_miss_char)
172 AND STATUS = 'A'
173 AND CUSTOMER_ID = p_exposure_rec.bill_to_customer_id
174 and address_status='A'; --2752321
175
176 CURSOR C1 (p_bill_to_state VARCHAR2) IS
177 SELECT /* MOAC_SQL_NO_CHANGE */ ORGANIZATION_ID
178 FROM OE_INVOICE_TO_ORGS_V
179 WHERE ADDRESS_LINE_1 = p_exposure_rec.bill_to_address1
180 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
181 nvl( p_exposure_rec.bill_to_address2, fnd_api.g_miss_char)
182 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
183 nvl( p_exposure_rec.bill_to_address3,fnd_api.g_miss_char)
184 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
185 nvl( p_exposure_rec.bill_to_address4,fnd_api.g_miss_char)
186 AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
187 nvl( p_exposure_rec.bill_to_city, fnd_api.g_miss_char)
188 AND nvl(STATE,fnd_api.g_miss_char) =
189 nvl( p_bill_to_state, fnd_api.g_miss_char)
190 AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
191 nvl( p_exposure_rec.bill_to_postal_code, fnd_api.g_miss_char)
192 AND nvl(COUNTRY,fnd_api.g_miss_char) =
193 nvl( p_exposure_rec.bill_to_country, fnd_api.g_miss_char)
194 AND STATUS = 'A'
195 and address_status='A' --2752321
196 AND CUSTOMER_ID IN
197 (
198 SELECT p_exposure_rec.bill_to_customer_id
199 FROM DUAL
200 UNION
201 SELECT CUST_ACCOUNT_ID
202 FROM HZ_CUST_ACCT_RELATE
203 WHERE RELATED_CUST_ACCOUNT_ID = p_exposure_rec.bill_to_customer_id
204 AND bill_to_flag = 'Y');
205
206 CURSOR C2 (p_bill_to_state VARCHAR2) IS
207 SELECT ORGANIZATION_ID
208 FROM OE_INVOICE_TO_ORGS_V
209 WHERE ADDRESS_LINE_1 = p_exposure_rec.bill_to_address1
210 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
211 nvl( p_exposure_rec.bill_to_address2, fnd_api.g_miss_char)
212 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
213 nvl( p_exposure_rec.bill_to_address3,fnd_api.g_miss_char)
214 AND nvl( ADDRESS_LINE_4, fnd_api.g_miss_char) =
215 nvl( p_exposure_rec.bill_to_address4,fnd_api.g_miss_char)
216 AND nvl(TOWN_OR_CITY,fnd_api.g_miss_char) =
217 nvl( p_exposure_rec.bill_to_city, fnd_api.g_miss_char)
218 AND nvl(STATE,fnd_api.g_miss_char) =
219 nvl( p_bill_to_state, fnd_api.g_miss_char)
220 AND nvl(POSTAL_CODE,fnd_api.g_miss_char) =
221 nvl( p_exposure_rec.bill_to_postal_code, fnd_api.g_miss_char)
222 AND nvl(COUNTRY,fnd_api.g_miss_char) =
223 nvl( p_exposure_rec.bill_to_country, fnd_api.g_miss_char)
224 AND STATUS = 'A'
225 and address_status='A'; --2752321
226
227 l_bill_to_site_use_id NUMBER;
228 l_bill_to_site_use_id2 NUMBER;
229 l_customer_relations VARCHAR2(1);
230 --MOAC Changes
231 --l_org varchar2(100);
232 l_message_text VARCHAR2(2000);
233 l_bill_to_state VARCHAR2(60);
234
235 BEGIN
236 OE_DEBUG_PUB.Add('OEXVECEB: In Address_Value_To_ID', 4);
237 OE_DEBUG_PUB.Add('bill_to_customer_id: '||p_exposure_rec.bill_to_customer_id, 5);
238 OE_DEBUG_PUB.Add('bill_to_address1 : '||p_exposure_rec.bill_to_address1, 5);
239 OE_DEBUG_PUB.Add('bill_to_address2 : '||p_exposure_rec.bill_to_address2, 5);
240 OE_DEBUG_PUB.Add('bill_to_address3 : '||p_exposure_rec.bill_to_address3, 5);
241 OE_DEBUG_PUB.Add('bill_to_address4 : '||p_exposure_rec.bill_to_address4, 5);
242 OE_DEBUG_PUB.Add('bill_to_state : '||p_exposure_rec.bill_to_state, 5);
243 OE_DEBUG_PUB.Add('bill_to_province : '||p_exposure_rec.bill_to_province, 5);
244
245 l_customer_relations:= OE_Sys_Parameters.VALUE('CUSTOMER_RELATIONSHIPS_FLAG');
246 OE_DEBUG_PUB.Add('CUSTOMER_RELATIONSHIPS_FLAG: '||l_customer_relations, 5);
247 --
248 -- bug 2346992: Get province if state is NULL or G_MISS_CHAR
249 --
250 IF NVL(p_exposure_rec.bill_to_state, FND_API.G_MISS_CHAR) =
251 FND_API.G_MISS_CHAR
252 THEN
253 l_bill_to_state := p_exposure_rec.bill_to_province;
254 ELSE
255 l_bill_to_state := p_exposure_rec.bill_to_state;
256 END IF;
257 OE_DEBUG_PUB.Add('l_bill_to_state : '||l_bill_to_state, 5);
258
259 IF l_customer_relations = 'N' THEN
260 OPEN c_bill_to_site_use_id(l_bill_to_state);
261 FETCH c_bill_to_site_use_id
262 INTO l_bill_to_site_use_id;
263
264 IF c_bill_to_site_use_id%FOUND THEN
265 -- Check for more than one site use
266 FETCH c_bill_to_site_use_id
267 INTO l_bill_to_site_use_id2;
268 IF c_bill_to_site_use_id%FOUND THEN
269 RAISE TOO_MANY_ROWS;
270 END IF;
271 CLOSE c_bill_to_site_use_id;
272 RETURN l_bill_to_site_use_id;
273 ELSE
274 SELECT ORGANIZATION_ID
275 INTO l_bill_to_site_use_id
276 FROM OE_INVOICE_TO_ORGS_V
277 WHERE ADDRESS_LINE_1 = p_exposure_rec.bill_to_address1
278 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
279 nvl( p_exposure_rec.bill_to_address2, fnd_api.g_miss_char)
280 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
281 nvl( p_exposure_rec.bill_to_address3,fnd_api.g_miss_char)
282 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
283 DECODE(STATE, NULL, NULL, STATE || ', ')||
284 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
285 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
286 NVL( p_exposure_rec.bill_to_address4, fnd_api.g_miss_char)
287 AND STATUS = 'A'
288 AND CUSTOMER_ID = p_exposure_rec.bill_to_customer_id
289 and address_status='A'; --2752321
290 END IF;
291
292 CLOSE c_bill_to_site_use_id;
293 RETURN l_bill_to_site_use_id;
294
295 ELSIF l_customer_relations = 'Y' THEN
296 OPEN C1(l_bill_to_state);
297 FETCH C1
298 INTO l_bill_to_site_use_id;
299
300 IF C1%FOUND then
301 OE_DEBUG_PUB.Add('Found', 5);
302 -- Check for more than one site use
303 FETCH C1
304 INTO l_bill_to_site_use_id2;
305 IF C1%FOUND THEN
306 RAISE TOO_MANY_ROWS;
307 END IF;
308 CLOSE C1;
309 RETURN l_bill_to_site_use_id;
310 ELSE
311 oe_debug_pub.add('not found', 5);
312 --MOAC Changes
313 /*select userenv('CLIENT_INFO') into l_org from dual;
314 oe_debug_pub.add('org='||l_org, 5);*/
315 --MOAC Changes
316 SELECT /* MOAC_SQL_NO_CHANGE */ ORGANIZATION_ID
317 INTO l_bill_to_site_use_id
318 FROM OE_INVOICE_TO_ORGS_V
319 WHERE ADDRESS_LINE_1 = p_exposure_rec.bill_to_address1
320 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
321 nvl( p_exposure_rec.bill_to_address2, fnd_api.g_miss_char)
322 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
323 nvl( p_exposure_rec.bill_to_address3,fnd_api.g_miss_char)
324 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
325 DECODE(STATE, NULL, NULL, STATE || ', ')||
326 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
327 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
328 nvl( p_exposure_rec.bill_to_address4, fnd_api.g_miss_char)
329 AND STATUS = 'A'
330 and address_status='A' --2752321
331 AND CUSTOMER_ID IN
332 (SELECT p_exposure_rec.bill_to_customer_id
333 FROM DUAL
334 UNION
335 SELECT CUST_ACCOUNT_ID
336 FROM HZ_CUST_ACCT_RELATE
337 WHERE RELATED_CUST_ACCOUNT_ID = p_exposure_rec.bill_to_customer_id
338 AND bill_to_flag = 'Y');
339 oe_debug_pub.add('after select found='||l_bill_to_site_use_id);
340 END IF;
341
342 CLOSE C1;
343 oe_debug_pub.add('returning from the function', 5);
344 RETURN l_bill_to_site_use_id;
345 ELSIF l_customer_relations = 'A' THEN
346 OPEN C2(l_bill_to_state);
347 FETCH C2
348 INTO l_bill_to_site_use_id;
349
350 IF C2%FOUND then
351 -- Check for more than one site use
352 FETCH C2
353 INTO l_bill_to_site_use_id2;
354 IF C2%FOUND THEN
355 RAISE TOO_MANY_ROWS;
356 END IF;
357 CLOSE C2;
358 RETURN l_bill_to_site_use_id;
359 ELSE
360 SELECT ORGANIZATION_ID
361 INTO l_bill_to_site_use_id
362 FROM OE_INVOICE_TO_ORGS_V
363 WHERE ADDRESS_LINE_1 = p_exposure_rec.bill_to_address1
364 AND nvl( ADDRESS_LINE_2, fnd_api.g_miss_char) =
365 nvl( p_exposure_rec.bill_to_address2, fnd_api.g_miss_char)
366 AND nvl( ADDRESS_LINE_3, fnd_api.g_miss_char) =
367 nvl( p_exposure_rec.bill_to_address3,fnd_api.g_miss_char)
368 AND DECODE(TOWN_OR_CITY,NULL,NULL,TOWN_OR_CITY||', ')||
369 DECODE(STATE, NULL, NULL, STATE || ', ')||
370 DECODE(POSTAL_CODE, NULL, NULL, POSTAL_CODE || ', ')||
371 DECODE(COUNTRY, NULL, NULL, COUNTRY) =
372 NVL( p_exposure_rec.bill_to_address4, fnd_api.g_miss_char)
373 AND STATUS = 'A'
374 and address_status='A'; --2752321
375 END IF;
376
377 CLOSE C2;
378 RETURN l_bill_to_site_use_id;
379 END IF;
380
381 EXCEPTION
382 WHEN NO_DATA_FOUND THEN
383 IF c_bill_to_site_use_id%ISOPEN then
384 CLOSE c_bill_to_site_use_id;
385 END IF;
386
387 IF C1%ISOPEN then
388 CLOSE C1;
389 END IF;
390
391 IF C2%ISOPEN then
392 CLOSE C2;
393 END IF;
394
395 FND_MESSAGE.SET_NAME('ONT','OE_CC_IMP_BILL_TO_ADDR_INVALID');
396 l_message_text := FND_MESSAGE.Get;
397 OE_DEBUG_PUB.Add('Error: No valid address found', 5);
398 Insert_To_Errors_Table(
399 p_exposure_source_code => p_exposure_rec.exposure_source_code
400 , p_exposure_interface_id => p_exposure_rec.exposure_interface_id
401 , p_batch_id => p_exposure_rec.batch_id
402 , p_error_message_name => 'OE_CC_IMP_BILL_TO_ADDR_INVALID'
403 , p_error_message_text => l_message_text
404 );
405
406 OE_DEBUG_PUB.Add('No data found error in Address_Value_To_ID', 5);
407 RETURN FND_API.G_MISS_NUM;
408 WHEN TOO_MANY_ROWS THEN
409 IF c_bill_to_site_use_id%ISOPEN then
410 CLOSE c_bill_to_site_use_id;
411 END IF;
412
413 IF C1%ISOPEN then
414 CLOSE C1;
415 END IF;
416
417 IF C2%ISOPEN then
418 CLOSE C2;
419 END IF;
420
421 FND_MESSAGE.SET_NAME('ONT','OE_CC_IMP_BILL_TO_ADDR_MULTI');
422 l_message_text := FND_MESSAGE.Get;
423 OE_DEBUG_PUB.Add('Error: Found multiple addresses', 5);
424 Insert_To_Errors_Table(
425 p_exposure_source_code => p_exposure_rec.exposure_source_code
426 , p_exposure_interface_id => p_exposure_rec.exposure_interface_id
427 , p_batch_id => p_exposure_rec.batch_id
428 , p_error_message_name => 'OE_CC_IMP_BILL_TO_ADDR_MULTI'
429 , p_error_message_text => l_message_text
430 );
431 OE_DEBUG_PUB.Add('Too many rows error in Address_Value_To_ID', 5);
432
433 RETURN FND_API.G_MISS_NUM;
434 WHEN OTHERS THEN
435 OE_DEBUG_PUB.Add('Unexpected error in Address_Value_To_ID', 5);
436 IF c_bill_to_site_use_id%ISOPEN then
437 CLOSE c_bill_to_site_use_id;
438 END IF;
439
440 IF C1%ISOPEN then
441 CLOSE C1;
442 END IF;
443
444 IF C2%ISOPEN then
445 CLOSE C2;
446 END IF;
447
448 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
449 OE_MSG_PUB.Add_Exc_Msg
450 ( G_PKG_NAME
451 , 'Address_Value_To_ID'
452 );
453 END IF;
454 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
455 END Address_Value_To_ID;
456
457 --=====================================================================
458 -- NAME: Get_Bill_To_Site_Use_ID
459 -- TYPE: PRIVATE PROCEDURE
460 -- DESCRIPTION: This procedure validate the bill-to site use ID if provided.
461 -- else it validate the other parameters and derive the site use ID.
462 --=====================================================================
463 PROCEDURE Get_Bill_To_Site_Use_ID
464 ( p_exposure_rec IN oe_exposure_interface%ROWTYPE
465 ,x_return_status OUT NOCOPY VARCHAR2
466 ,x_bill_to_site_use_id OUT NOCOPY NUMBER
467 ,x_org_id OUT NOCOPY NUMBER
468 ,x_bill_to_customer_id OUT NOCOPY NUMBER
469 )
470 IS
471 l_bill_to_site_use_id NUMBER;
472 l_bill_to_customer_id NUMBER;
473 l_message_text VARCHAR2(2000);
474 l_exposure_rec oe_exposure_interface%ROWTYPE;
475 BEGIN
476 OE_DEBUG_PUB.Add('OEXVECEB: In Get_Bill_To_Site_Use_ID', 4);
477 x_return_status := FND_API.G_RET_STS_SUCCESS;
478
479 /* Added the following line to fix the bug 6451056 */
480
481 MO_GLOBAL.set_policy_context('S', p_exposure_rec.org_id);
482
483
484 IF p_exposure_rec.bill_to_site_use_id IS NOT NULL THEN
485 BEGIN
486 --
487 -- use the invoice_to_orgs_v
488 --
489 SELECT organization_id,
490 customer_id
491 INTO l_bill_to_site_use_id,
492 l_bill_to_customer_id
493 FROM oe_invoice_to_orgs_v
494 WHERE site_use_id=p_exposure_rec.bill_to_site_use_id;
495
496 EXCEPTION
497 WHEN NO_DATA_FOUND THEN
498 -- This same error message takes care of the case of NULL value
499 -- passed in since a NULL in the select will not select any rows.
500 x_return_status := FND_API.G_RET_STS_ERROR;
501 FND_MESSAGE.Set_Name('ONT', 'OE_CC_IMP_SITE_USE_ID_INVALID');
502 FND_MESSAGE.Set_Token('COLUMN_NAME' , 'BILL_TO_SITE_USE_ID');
503 FND_MESSAGE.Set_Token('COLUMN_VALUE', p_exposure_rec.bill_to_site_use_id);
504 l_message_text := FND_MESSAGE.Get;
505 OE_DEBUG_PUB.Add('Error: Bill-to site use ID invalid', 5);
506 Insert_To_Errors_Table(
507 p_exposure_source_code => p_exposure_rec.exposure_source_code
508 , p_exposure_interface_id => p_exposure_rec.exposure_interface_id
509 , p_batch_id => p_exposure_rec.batch_id
510 , p_error_message_name => 'OE_CC_IMP_SITE_USE_ID_INVALID'
511 , p_error_message_text => l_message_text
512 );
513 END;
514 ELSIF
515 -- bill_to_site_use_id is NULL. Derive it from address and customer info.
516 -- The location and customer information must be provided to derive a
517 -- unique site use id.
518 (p_exposure_rec.bill_to_address1 IS NOT NULL )
519 THEN
520 -- location information exists, now check for a valid customer.
521
522 IF p_exposure_rec.bill_to_customer_id IS NOT NULL THEN
523 l_bill_to_site_use_id := Address_Value_To_ID
524 (
525 p_exposure_rec => p_exposure_rec
526 );
527 l_bill_to_customer_id := p_exposure_rec.bill_to_customer_id;
528 OE_DEBUG_PUB.Add('l_bill_to_site_use_id: '||l_bill_to_site_use_id, 5);
529 OE_DEBUG_PUB.Add('l_bill_to_customer_id: '||l_bill_to_customer_id, 5);
530 IF NVL(l_bill_to_site_use_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM THEN
531 x_return_status := FND_API.G_RET_STS_ERROR;
532 END IF;
533 ELSIF p_exposure_rec.bill_to_customer_name IS NOT NULL AND
534 p_exposure_rec.bill_to_customer_number IS NOT NULL THEN
535 -- check for valid customer ID
536 BEGIN
537 SELECT hca.cust_account_id
538 INTO l_bill_to_customer_id
539 FROM hz_cust_accounts hca,
540 hz_parties hp
541 WHERE hca.party_id = hp.party_id
542 AND hp.party_name = p_exposure_rec.bill_to_customer_name
543 AND hca.account_number = p_exposure_rec.bill_to_customer_number;
544 --
545 -- then get bill_to_site_use_id
546 --
547 l_exposure_rec := p_exposure_rec;
548 l_exposure_rec.bill_to_customer_id := l_bill_to_customer_id;
549 l_bill_to_site_use_id := Address_Value_To_ID
550 (
551 p_exposure_rec => p_exposure_rec
552 );
553
554 OE_DEBUG_PUB.Add('l_bill_to_site_use_id: '||l_bill_to_site_use_id, 5);
555 OE_DEBUG_PUB.Add('l_bill_to_customer_id: '||l_bill_to_customer_id, 5);
556 IF NVL(l_bill_to_site_use_id,FND_API.G_MISS_NUM)=FND_API.G_MISS_NUM THEN
557 x_return_status := FND_API.G_RET_STS_ERROR;
558 END IF;
559 EXCEPTION
560 WHEN NO_DATA_FOUND THEN
561 x_return_status := FND_API.G_RET_STS_ERROR;
562 FND_MESSAGE.Set_Name('ONT', 'OE_CC_IMP_CUST_INFO_INVALID');
563 FND_MESSAGE.SET_TOKEN ('CUSTOMER_NAME', 'BILL_TO_CUSTOMER_NAME' );
564 FND_MESSAGE.SET_TOKEN ('CUSTOMER_NUMBER', 'BILL_TO_CUSTOMER_NUMBER' );
565 l_message_text := FND_MESSAGE.Get;
566 OE_DEBUG_PUB.Add('Error: Customer ID cannot be derived from customer name and number', 5);
567 Insert_To_Errors_Table(
568 p_exposure_source_code => p_exposure_rec.exposure_source_code
569 , p_exposure_interface_id => p_exposure_rec.exposure_interface_id
570 , p_batch_id => p_exposure_rec.batch_id
571 , p_error_message_name => 'OE_CC_IMP_CUST_INFO_INVALID'
572 , p_error_message_text => l_message_text
573 );
574 END;
575 ELSE
576 -- customer information is missing
577 x_return_status := FND_API.G_RET_STS_ERROR;
578 FND_MESSAGE.Set_Name('ONT', 'OE_CC_IMP_CUST_INFO_MISSING');
579 FND_MESSAGE.SET_TOKEN ('CUSTOMER_NAME', 'BILL_TO_CUSTOMER_NAME' );
580 FND_MESSAGE.SET_TOKEN ('CUSTOMER_NUMBER', 'BILL_TO_CUSTOMER_NUMBER' );
581 FND_MESSAGE.SET_TOKEN ('CUSTOMER_ID', 'BILL_TO_CUSTOMER_ID');
582 l_message_text := FND_MESSAGE.Get;
583 OE_DEBUG_PUB.Add('Error: No customer information provided', 5);
584 Insert_To_Errors_Table(
585 p_exposure_source_code => p_exposure_rec.exposure_source_code
586 , p_exposure_interface_id => p_exposure_rec.exposure_interface_id
587 , p_batch_id => p_exposure_rec.batch_id
588 , p_error_message_name => 'OE_CC_IMP_CUST_INFO_MISSING'
589 , p_error_message_text => l_message_text
590 );
591
592 END IF;
593 ELSE
594 -- insufficient information is provided to derive the invoice site use id.
595 -- Either the bill_to site use ID needs to be provided or the bill-to address.
596 x_return_status := FND_API.G_RET_STS_ERROR;
597 FND_MESSAGE.Set_Name('ONT', 'OE_CC_IMP_SITE_USE_INF_INVALID');
598 l_message_text := FND_MESSAGE.Get;
599 OE_DEBUG_PUB.Add('Error: Insufficient information provided to derive site use ID', 5);
600
601 Insert_To_Errors_Table(
602 p_exposure_source_code => p_exposure_rec.exposure_source_code
603 , p_exposure_interface_id => p_exposure_rec.exposure_interface_id
604 , p_batch_id => p_exposure_rec.batch_id
605 , p_error_message_name => 'OE_CC_IMP_SITE_USE_INF_INVALID'
606 , p_error_message_text => l_message_text
607 );
608
609 END IF;
610
611 x_bill_to_site_use_id := l_bill_to_site_use_id;
612 x_bill_to_customer_id := l_bill_to_customer_id;
613 -- x_org_id := G_org_id; -- MOAC
614 x_org_id := p_exposure_rec.org_id;
615
616 OE_DEBUG_PUB.Add('OEXVECEB: Out Get_Bill_To_Site_Use_ID', 4);
617 EXCEPTION
618 WHEN OTHERS THEN
619 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
620 END Get_Bill_To_Site_Use_ID;
621
622 --=====================================================================
623 -- NAME: Validate_Exposure_Source
624 -- TYPE: PRIVATE PROCEDURE
625 -- DESCRIPTION: This procedure validate the exposure source within the
626 -- a batch. It checks that
627 -- 1) the exposure source code is valid
628 -- 2) there is only one operation code per exposure source
629 -- 3) the operation code is either INSERT or UPDATE
630 --=====================================================================
631 PROCEDURE Validate_Exposure_Source
632 IS
633
634 CURSOR c_exposure_source IS
635 SELECT distinct exposure_source_code
636 FROM oe_exposure_interface
637 WHERE request_id = G_request_id
638 AND import_status_code = G_PROCESSING;
639
640 CURSOR c_multi_op_code
641 (p_exposure_source_code oe_exposure_interface.exposure_source_code%TYPE) IS
642 SELECT COUNT(distinct operation_code)
643 FROM oe_exposure_interface
644 WHERE request_id = G_request_id
645 AND import_status_code = G_PROCESSING
646 AND exposure_source_code = p_exposure_source_code
647 HAVING COUNT(distinct operation_code) > 1;
648
649 CURSOR c_invalid_op_code
650 (p_exposure_source_code oe_exposure_interface.exposure_source_code%TYPE) IS
651 SELECT distinct operation_code
652 FROM oe_exposure_interface
653 WHERE request_id = G_request_id
654 AND import_status_code = G_PROCESSING
655 AND operation_code NOT IN ('INSERT', 'UPDATE');
656
657 l_exposure_soure_code oe_exposure_interface.exposure_source_code%TYPE;
658 l_op_code_count NUMBER := 0;
659 l_operation_code oe_exposure_interface.operation_code%TYPE;
660 l_message_text oe_exp_interface_errors.error_message%TYPE;
661 l_source_valid NUMBER;
662 l_any_op_code_errors BOOLEAN := FALSE;
663 BEGIN
664 OE_DEBUG_PUB.Add('OEXVECEB: In Validate_Exposure_Source ');
665 --l_result_out := 'PASS';
666 FOR l_row IN c_exposure_source LOOP
667 --
668 -- Check the exposure source code
669 --
670 BEGIN
671 SELECT 1
672 INTO l_source_valid
673 FROM oe_lookups
674 WHERE lookup_type = 'EXTERNAL_EXPOSURE_SOURCE'
675 AND lookup_code = l_row.exposure_source_code
676 AND enabled_flag = 'Y'
677 AND NVL(start_date_active, TO_DATE('01/01/1000','DD/MM/YYYY'))
678 <= TRUNC(SYSDATE)
679 AND NVL(end_date_active, TO_DATE('31/12/9999','DD/MM/YYYY'))
680 >= TRUNC(SYSDATE) ;
681 EXCEPTION
682 WHEN NO_DATA_FOUND THEN
683 -- exposure source code is not valid
684 FND_MESSAGE.Set_Name('ONT', 'OE_CC_IMP_SOURCE_INVALID');
685 FND_MESSAGE.Set_Token ('COLUMN_NAME','EXPOSURE_SOURCE_CODE');
686 FND_MESSAGE.Set_Token ('COLUMN_VALUE', l_row.exposure_source_code);
687 l_message_text := FND_MESSAGE.Get;
688 OE_DEBUG_PUB.Add('Error: Exposure source code is not valid', 2);
689 Insert_To_Errors_Table(
690 p_exposure_source_code => l_row.exposure_source_code
691 , p_exposure_interface_id => NULL
692 , p_batch_id => NULL
693 , p_error_message_name => 'OE_CC_IMP_SOURCE_INVALID'
694 , p_error_message_text => l_message_text
695 );
696 UPDATE oe_exposure_interface
697 SET import_status_code = G_ERROR
698 WHERE exposure_source_code = l_row.exposure_source_code
699 AND request_id = G_request_id;
700
701 COMMIT;
702 END;
703 --
704 -- Check source for multiple operation code
705 --
706 OPEN c_multi_op_code(l_row.exposure_source_code);
707 FETCH c_multi_op_code INTO l_op_code_count;
708
709 IF c_multi_op_code%FOUND THEN
710 OE_DEBUG_PUB.Add('Exposure source code '||l_row.exposure_source_code||
711 ' contains multiple operation codes', 2);
712 -- write message to errors table
713 l_any_op_code_errors := TRUE;
714 FND_MESSAGE.Set_Name('ONT', 'OE_CC_IMP_OP_CODE_MULTIPLE');
715 l_message_text := FND_MESSAGE.Get;
716 OE_DEBUG_PUB.Add('Error: Exposure source contains multiple operation codes', 2);
717 ELSE
718 OE_DEBUG_PUB.Add('Exposure source code '||l_row.exposure_source_code||
719 ' contains 1 operation code', 2);
720 -- check for invalid operation code
721 OPEN c_invalid_op_code(l_row.exposure_source_code);
722 FETCH c_invalid_op_code INTO l_operation_code;
723 IF c_invalid_op_code%FOUND THEN
724 l_any_op_code_errors := TRUE;
725 OE_DEBUG_PUB.Add('Exposure source code '||l_row.exposure_source_code||
726 ' contains an invalid operation code', 2);
727 -- write message to errors table
728 FND_MESSAGE.Set_Name('ONT', 'OE_CC_IMP_OP_CODE_MULTIPLE');
729 l_message_text := FND_MESSAGE.Get;
730 END IF;
731 CLOSE c_invalid_op_code;
732 END IF;
733 CLOSE c_multi_op_code;
734
735 IF l_any_op_code_errors THEN
736 --
737 -- Insert message to errors table
738 --
739 Insert_To_Errors_Table(
740 p_exposure_source_code => l_row.exposure_source_code
741 , p_exposure_interface_id => NULL
742 , p_batch_id => NULL
743 , p_error_message_name => 'OE_CC_IMP_OP_CODE_MULTIPLE'
744 , p_error_message_text => l_message_text
745 );
746 --
747 -- update import_status_code if errors found
748 --
749 UPDATE oe_exposure_interface
750 SET import_status_code = G_ERROR
751 WHERE exposure_source_code = l_row.exposure_source_code
752 AND request_id = G_request_id;
753
754 COMMIT;
755 END IF;
756 END LOOP; -- end of check of exposure source codes
757 OE_DEBUG_PUB.Add('OEXVECEB: Out Validate_Exposure_Source ');
758 EXCEPTION
759 WHEN OTHERS THEN
760 IF c_multi_op_code%ISOPEN THEN
761 CLOSE c_multi_op_code;
762 END IF;
763 IF c_invalid_op_code%ISOPEN THEN
764 CLOSE c_invalid_op_code;
765 END IF;
766 IF c_exposure_source%ISOPEN THEN
767 CLOSE c_exposure_source;
768 END IF;
769 OE_DEBUG_PUB.Add('Validate_Exposure_Source -- Unexpected Error');
770 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
771 END Validate_Exposure_Source;
772
773 --=====================================================================
774 --NAME: Validate_Exposure
775 --TYPE: PRIVATE
776 --DESCRIPTION: This procedure validate the exposure rows in the interface
777 -- table and derive the IDs use for loading the exposure info
778 -- into OM.
779 --Parameters:
780 --IN
781 --OUT
782 --=====================================================================
783 PROCEDURE Validate_Exposure
784 IS
785 CURSOR c_rows_to_process IS
786 SELECT *
787 FROM oe_exposure_interface
788 WHERE request_id = G_request_id
789 AND import_status_code = G_PROCESSING;
790
791 l_bill_to_site_use_id NUMBER;
792 l_bill_to_customer_id NUMBER;
793 l_commit_count NUMBER := 0;
794 l_any_errors BOOLEAN := FALSE;
795 l_return_status VARCHAR2(30);
796 l_org_id NUMBER;
797 l_result_out VARCHAR2(30);
798 BEGIN
799 OE_DEBUG_PUB.Add('OEXVECEB: In Validate_Exposure');
800 --
801 -- Validate Operation Code. Should be the same within a given exposure source
802 --
803 Validate_Exposure_Source;
804
805 -- Fetch each row and validate
806 FOR l_exposure_rec IN c_rows_to_process LOOP
807 --
808 -- Validate each row
809 --
810 OE_DEBUG_PUB.Add('Validating interface ID: '||
811 l_exposure_rec.exposure_interface_id, 4);
812 l_bill_to_site_use_id := l_exposure_rec.bill_to_site_use_id;
813
814 -- Validate currency code
815 IF NOT Is_Currency_Valid(p_exposure_rec => l_exposure_rec)
816 THEN
817 IF NOT l_any_errors THEN
818 l_any_errors := TRUE;
819 END IF;
820 OE_DEBUG_PUB.Add('Validate Currency Failed.', 5);
821 END IF;
822
823 --
824 -- Derive the bill-to site use ID, the customer ID, and the org ID
825 -- The org ID is derived from the bill-to site use ID.
826 -- The customer ID is either given or derived from customer name and
827 -- customer number.
828 --
829 Get_Bill_To_Site_Use_ID
830 ( p_exposure_rec => l_exposure_rec
831 ,x_return_status => l_return_status
832 ,x_bill_to_site_use_id => l_bill_to_site_use_id
833 ,x_bill_to_customer_id => l_bill_to_customer_id
834 ,x_org_id => l_org_id
835 );
836
837 OE_DEBUG_PUB.Add('l_bill_to_site_use_id: '||l_bill_to_site_use_id, 5);
838 OE_DEBUG_PUB.Add('l_bill_to_customer_id: '||l_bill_to_customer_id, 5);
839 OE_DEBUG_PUB.Add('l_org_id : '||l_org_id, 5);
840 OE_DEBUG_PUB.Add('l_return_status : '||l_return_status, 5);
841
842 IF l_return_status <> FND_API.G_RET_STS_SUCCESS
843 THEN
844 IF NOT l_any_errors THEN
845 l_any_errors := TRUE;
846 END IF;
847 OE_DEBUG_PUB.Add('Validate Bill-To Site Use Failed.', 5);
848 END IF;
849
850 IF l_any_errors THEN
851 OE_DEBUG_PUB.Add('Validation Errors Found', 5);
852 UPDATE oe_exposure_interface
853 SET import_status_code = G_ERROR
854 WHERE exposure_interface_id = l_exposure_rec.exposure_interface_id;
855 ELSE
856 -- Update status code and IDs and Who Columns
857 OE_DEBUG_PUB.Add(
858 'No Validation Errors Found. Updating the interface table...', 5);
859 UPDATE oe_exposure_interface
860 SET import_status_code = G_VALIDATED,
861 bill_to_site_use_id = l_bill_to_site_use_id,
862 bill_to_customer_id = l_bill_to_customer_id,
863 last_update_login = G_login_id,
864 program_application_id = G_appl_id,
865 program_id = G_program_id,
866 program_update_date = TRUNC(sysdate),
867 -- org_id = G_org_id -- MOAC
868 org_id = l_org_id
869 WHERE exposure_interface_id = l_exposure_rec.exposure_interface_id;
870 END IF;
871 l_commit_count := l_commit_count + 1;
872 IF l_commit_count >= G_COMMIT_SIZE THEN
873 COMMIT;
874 END IF;
875 END LOOP;
876 -- END IF; -- validation
877 OE_DEBUG_PUB.Add('OEXVECEB: Out Validate_Exposure');
878 EXCEPTION
879 WHEN FND_API.G_EXC_ERROR THEN
880 OE_DEBUG_PUB.Add('OEXVECEB: Validate_Exposure -- Expected Error', 2);
881 RAISE;
882 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
883 OE_DEBUG_PUB.Add('OEXVECEB: Validate_Exposure -- Unexpected Error', 2);
884 RAISE;
885 WHEN OTHERS THEN
886 IF c_rows_to_process%ISOPEN THEN
887 CLOSE c_rows_to_process;
888 END IF;
889 OE_DEBUG_PUB.Add('OEXVECEB: Validate_Exposure -- Other Unexpected Error', 2);
890 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
891 OE_MSG_PUB.Add_Exc_Msg (
892 G_PKG_NAME
893 , 'Validate_Exposure');
894 END IF;
895 RAISE;
896 END Validate_Exposure;
897
898 --=====================================================================
899 --NAME: Insert_Exposure
900 --TYPE: PRIVATE
901 --DESCRIPTION: This procedure delete/insert row into OE_CREDIT_SUMMARIES
902 -- table.
903 --Parameters:
904 --IN
905 --OUT
906 --=====================================================================
907 PROCEDURE Insert_Exposure
908 ( p_exposure_rec IN oe_exposure_interface%ROWTYPE
909 )
910 IS
911 CURSOR c_summary_row IS
912 SELECT rowid
913 FROM oe_credit_summaries
914 WHERE cust_account_id = p_exposure_rec.bill_to_customer_id
915 AND org_id = p_exposure_rec.org_id
916 AND site_use_id = p_exposure_rec.bill_to_site_use_id
917 AND currency_code = p_exposure_rec.currency_code
918 AND exposure_source_code = p_exposure_rec.exposure_source_code
919 AND balance_type = 18;
920
921 l_row_id VARCHAR2(30);
922 BEGIN
923 OE_DEBUG_PUB.Add('OEXVECEB: In Insert_Exposure', 4);
924 OPEN c_summary_row;
925 FETCH c_summary_row INTO l_row_id;
926
927 IF c_summary_row%FOUND THEN
928 -- Delete any rows first, then insert.
929 OE_DEBUG_PUB.Add('Row exist. Delete existing row', 5);
930 oe_credit_summaries_pkg.Delete_Row(
931 p_row_id => l_row_id
932 );
933 END IF;
934
935 IF p_exposure_rec.exposure_amount <> 0 THEN
936 -- Insert row only if amount <> 0
937 OE_DEBUG_PUB.Add('Insert the new exposure row', 5);
938 oe_credit_summaries_pkg.insert_row(
939 p_cust_account_id => p_exposure_rec.bill_to_customer_id
940 , p_org_id => p_exposure_rec.org_id
941 , p_site_use_id => p_exposure_rec.bill_to_site_use_id
942 , p_currency_code => p_exposure_rec.currency_code
943 , p_balance_type => 18
944 , p_balance => p_exposure_rec.exposure_amount
945 , p_creation_date => p_exposure_rec.creation_date
946 , p_created_by => p_exposure_rec.created_by
947 , p_last_update_date => p_exposure_rec.last_update_date
948 , p_last_updated_by => p_exposure_rec.last_updated_by
949 , p_last_update_login => p_exposure_rec.last_update_login
950 , p_program_application_id => p_exposure_rec.program_application_id
951 , p_program_id => p_exposure_rec.program_id
952 , p_program_update_date => p_exposure_rec.program_update_date
953 , p_request_id => p_exposure_rec.request_id
954 , p_exposure_source_code => p_exposure_rec.exposure_source_code
955 );
956 END IF;
957 CLOSE c_summary_row;
958 OE_DEBUG_PUB.Add('OEXVECEB: Out Insert_Exposure', 4);
959 EXCEPTION
960 WHEN OTHERS THEN
961 IF c_summary_row%ISOPEN THEN
962 CLOSE c_summary_row;
963 END IF;
964 RAISE;
965 END Insert_Exposure;
966
967 --=====================================================================
968 --NAME: Update_Exposure
969 --TYPE: PRIVATE
970 --DESCRIPTION: This procedure insert/update row into OE_CREDIT_SUMMARIES
971 -- table.
972 --Parameters:
973 --IN
974 --OUT
975 --=====================================================================
976 PROCEDURE Update_Exposure
977 ( p_exposure_rec IN oe_exposure_interface%ROWTYPE
978 )
979 IS
980 CURSOR c_summary_exposure IS
981 SELECT rowid, balance
982 FROM oe_credit_summaries
983 WHERE cust_account_id = p_exposure_rec.bill_to_customer_id
984 AND org_id = p_exposure_rec.org_id
985 AND site_use_id = p_exposure_rec.bill_to_site_use_id
986 AND currency_code = p_exposure_rec.currency_code
987 AND exposure_source_code = p_exposure_rec.exposure_source_code
988 AND balance_type = 18;
989
990 l_row_id VARCHAR2(30);
991 l_balance NUMBER;
992 BEGIN
993 OE_DEBUG_PUB.Add('OEXVECEB: In Update_Exposure', 4);
994 -- Check if exposure exist in the summary table
995 OPEN c_summary_exposure;
996 FETCH c_summary_exposure INTO l_row_id, l_balance;
997
998 IF p_exposure_rec.exposure_amount <> 0 THEN
999 IF c_summary_exposure%FOUND THEN
1000 OE_DEBUG_PUB.Add('Updating exposure row', 5);
1001 oe_credit_summaries_pkg.Update_Row(
1002 p_row_id => l_row_id
1003 , p_balance => p_exposure_rec.exposure_amount + l_balance
1004 , p_last_update_date => p_exposure_rec.last_update_date
1005 , p_last_updated_by => p_exposure_rec.last_updated_by
1006 , p_last_update_login => p_exposure_rec.last_update_login
1007 , p_program_application_id => p_exposure_rec.program_application_id
1008 , p_program_id => p_exposure_rec.program_id
1009 , p_program_update_date => p_exposure_rec.program_update_date
1010 , p_request_id => p_exposure_rec.request_id
1011 );
1012 ELSE
1013 OE_DEBUG_PUB.Add('Inserting exposure row', 5);
1014 oe_credit_summaries_pkg.Insert_Row(
1015 p_cust_account_id => p_exposure_rec.bill_to_customer_id
1016 , p_org_id => p_exposure_rec.org_id
1017 , p_site_use_id => p_exposure_rec.bill_to_site_use_id
1018 , p_currency_code => p_exposure_rec.currency_code
1019 , p_balance_type => 18
1020 , p_balance => p_exposure_rec.exposure_amount
1021 , p_creation_date => p_exposure_rec.creation_date
1022 , p_created_by => p_exposure_rec.created_by
1023 , p_last_update_date => p_exposure_rec.last_update_date
1024 , p_last_updated_by => p_exposure_rec.last_updated_by
1025 , p_last_update_login => p_exposure_rec.last_update_login
1026 , p_program_application_id => p_exposure_rec.program_application_id
1027 , p_program_id => p_exposure_rec.program_id
1028 , p_program_update_date => p_exposure_rec.program_update_date
1029 , p_request_id => p_exposure_rec.request_id
1030 , p_exposure_source_code => p_exposure_rec.exposure_source_code
1031 );
1032 END IF;
1033 END IF;
1034 CLOSE c_summary_exposure;
1035 OE_DEBUG_PUB.Add('OEXVECEB: Out Update_Exposure', 4);
1036 EXCEPTION
1037 WHEN OTHERS THEN
1038 IF c_summary_exposure%ISOPEN THEN
1039 CLOSE c_summary_exposure;
1040 END IF;
1041 RAISE;
1042 END Update_Exposure;
1043
1044 --=====================================================================
1045 --NAME: Import_Exposure
1046 --TYPE: PRIVATE
1047 --DESCRIPTION: This procedure load the exposure rows in the interface
1048 -- table into the OM credit summary table.
1049 --Parameters:
1050 --IN
1051 --OUT
1052 --=====================================================================
1053 PROCEDURE Import_Exposure
1054 IS
1055 CURSOR c_valid_exposures IS
1056 SELECT *
1057 FROM oe_exposure_interface
1058 WHERE request_id = G_request_id
1059 AND import_status_code = G_VALIDATED
1060 ORDER BY exposure_source_code;
1061
1062 l_commitsize_count NUMBER := 0;
1063 BEGIN
1064 OE_DEBUG_PUB.Add('OEXVECEB: In Import_Exposure');
1065 FOR l_exposure_rec IN c_valid_exposures LOOP
1066 IF l_exposure_rec.operation_code = 'INSERT' THEN
1067 Insert_Exposure(l_exposure_rec);
1068 ELSE
1069 Update_Exposure(l_exposure_rec);
1070 END IF;
1071
1072 --
1073 -- Set status to complete after updating/inserting
1074 --
1075 UPDATE oe_exposure_interface
1076 SET import_status_code = 'COMPLETE'
1077 WHERE exposure_interface_id = l_exposure_rec.exposure_interface_id;
1078
1079 l_commitsize_count := l_commitsize_count + 1;
1080 IF l_commitsize_count >= G_COMMIT_SIZE THEN
1081 COMMIT;
1082 l_commitsize_count := 0;
1083 END IF;
1084 END LOOP;
1085 COMMIT;
1086 OE_DEBUG_PUB.Add('OEXVECEB: Out Import_Exposure');
1087 EXCEPTION
1088 WHEN OTHERS THEN
1089 OE_DEBUG_PUB.Add('OEXECEB: Import_Exposure -- Unexpected Error');
1090 RAISE;
1091 END Import_Exposure;
1092
1093 --=====================================================================
1094 --API NAME: Import_Credit_Exposure
1095 --TYPE: PRIVATE
1096 --DESCRIPTION: This procedure validate the exposure rows in the interface
1097 -- table and load them into the OM credit summary table when
1098 -- appropriate.
1099 --Parameters:
1100 --IN
1101 --OUT
1102 --Version: Current Version 1.0
1103 -- Previous Version 1.0
1104 --=====================================================================
1105 PROCEDURE Import_Credit_Exposure
1106 ( p_api_version IN NUMBER
1107 , p_org_id IN NUMBER
1108 , p_exposure_source_code IN VARCHAR2
1109 , p_batch_id IN NUMBER
1110 , p_validate_only IN VARCHAR2
1111 , x_num_rows_to_process OUT NOCOPY NUMBER
1112 , x_num_rows_validated OUT NOCOPY NUMBER
1113 , x_num_rows_failed OUT NOCOPY NUMBER
1114 , x_num_rows_imported OUT NOCOPY NUMBER
1115 )
1116 IS
1117 l_api_name CONSTANT VARCHAR2(30) := 'Import Credit Exposure';
1118 l_api_version CONSTANT NUMBER := 1.0;
1119 l_any_errors BOOLEAN := FALSE;
1120 l_org_id NUMBER;
1121 l_return_status VARCHAR2(30);
1122
1123 l_num_rows_to_process NUMBER := 0;
1124 l_num_rows_failed NUMBER := 0;
1125 l_num_rows_imported NUMBER := 0;
1126 l_num_rows_validated NUMBER := 0;
1127 l_validated BOOLEAN := FALSE;
1128 l_commit_count NUMBER := 0;
1129 l_message_text VARCHAR2(2000);
1130
1131 -- MOAC start
1132 CURSOR l_secured_ou_cur IS
1133 SELECT ou.organization_id
1134 FROM hr_operating_units ou
1135 WHERE mo_global.check_access(ou.organization_id) = 'Y';
1136
1137 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1138 -- MOAC end
1139
1140 BEGIN
1141 IF l_debug_level > 0 THEN
1142 OE_DEBUG_PUB.Add('OEXVECEB: In Import_Credit_Exposure');
1143 END IF;
1144 --
1145 -- Show input parameters
1146 --
1147 OE_DEBUG_PUB.Add('***** Input Parameters*****');
1148 OE_DEBUG_PUB.Add('p_api_version: '||p_api_version);
1149 OE_DEBUG_PUB.Add('p_org_id: '||p_org_id);
1150 OE_DEBUG_PUB.Add('p_exposure_source_code: '||p_exposure_source_code);
1151 OE_DEBUG_PUB.Add('p_batch_id: '||p_batch_id);
1152 OE_DEBUG_PUB.Add('p_validate_only: '||p_validate_only);
1153 --
1154 -- Check the version and issue an error if the given version does not
1155 -- match the one in this package.
1156 --
1157 IF NOT FND_API.Compatible_API_Call( l_api_version,
1158 p_api_version,
1159 l_api_name,
1160 G_PKG_NAME)
1161 THEN
1162 FND_MSG_PUB.Delete_Msg;
1163 FND_MESSAGE.Set_Name('ONT', 'OE_CC_API_VERSION_MISMATCH');
1164 FND_MESSAGE.SET_TOKEN ('API_NAME', l_api_name );
1165 FND_MESSAGE.SET_TOKEN ('P_API_VERSION', p_api_version );
1166 FND_MESSAGE.SET_TOKEN ('CURR_VER_NUM',l_api_version);
1167 FND_MESSAGE.SET_TOKEN ('CALLER_VER_NUM',p_api_version);
1168 OE_DEBUG_PUB.Add('l_api_version: '||l_api_version);
1169 OE_DEBUG_PUB.Add('p_api_version: '||p_api_version);
1170 OE_DEBUG_PUB.Add('API Version Check Failed.');
1171 l_message_text := FND_MESSAGE.GET;
1172 OE_DEBUG_PUB.Add('message text: '||SUBSTRB(l_message_text, 1, 200));
1173 RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
1174 END IF;
1175
1176 -- MOAC start
1177 l_org_id := p_org_id;
1178
1179 IF l_org_id IS NOT NULL THEN
1180 MO_GLOBAL.set_policy_context('S', l_org_id);
1181
1182 UPDATE oe_exposure_interface
1183 SET exposure_interface_id = OE_EXPOSURE_INTERFACE_S.NextVal,
1184 import_status_code = G_PROCESSING,
1185 request_id = G_request_id
1186 WHERE exposure_source_code = NVL(p_exposure_source_code, exposure_source_code)
1187 AND NVL(batch_id, -99) = NVL(p_batch_id, NVL(batch_id,-99))
1188 AND NVL(org_id, -99) = NVL(l_org_id, -99)
1189 AND import_status_code IS NULL;
1190
1191 IF l_debug_level > 0 THEN
1192 OE_DEBUG_PUB.Add('org_id : ' || l_org_id);
1193 OE_DEBUG_PUB.Add(TO_CHAR(SQL%ROWCOUNT)||' rows updated');
1194 END IF;
1195 ELSE
1196 OPEN l_secured_ou_cur;
1197
1198 LOOP
1199 FETCH l_secured_ou_cur
1200 into l_org_id;
1201 EXIT WHEN l_secured_ou_cur%NOTFOUND;
1202
1203 IF l_org_id IS NULL THEN
1204 l_org_id := mo_global.get_current_org_id;
1205 END IF;
1206
1207 MO_GLOBAL.set_policy_context('S', l_org_id);
1208
1209 UPDATE oe_exposure_interface
1210 SET exposure_interface_id = OE_EXPOSURE_INTERFACE_S.NextVal,
1211 import_status_code = G_PROCESSING,
1212 request_id = G_request_id
1213 WHERE exposure_source_code = NVL(p_exposure_source_code, exposure_source_code)
1214 AND NVL(batch_id, -99) = NVL(p_batch_id, NVL(batch_id,-99))
1215 AND NVL(org_id, -99) = NVL(l_org_id, -99)
1216 AND import_status_code IS NULL;
1217
1218 IF l_debug_level > 0 THEN
1219 OE_DEBUG_PUB.Add('org_id : ' || l_org_id);
1220 OE_DEBUG_PUB.Add(TO_CHAR(SQL%ROWCOUNT)||' rows updated');
1221 END IF;
1222 END LOOP;
1223
1224 CLOSE l_secured_ou_cur;
1225 END IF;
1226 -- MOAC End
1227
1228 --
1229 -- Select exposure rows for processing and assign an exposure_interface_id
1230 --
1231 -- UPDATE oe_exposure_interface
1232 -- SET exposure_interface_id = OE_EXPOSURE_INTERFACE_S.NextVal,
1233 -- import_status_code = G_PROCESSING,
1234 -- request_id = G_request_id
1235 -- WHERE exposure_source_code = NVL(p_exposure_source_code, exposure_source_code)
1236 -- AND NVL(batch_id, -99) = NVL(p_batch_id, NVL(batch_id,-99))
1237 -- AND import_status_code IS NULL;
1238
1239 OE_DEBUG_PUB.Add('Selected rows for processing',2);
1240
1241 --
1242 -- Count the number of rows selected for processing
1243 --
1244 SELECT count(1)
1245 INTO l_num_rows_to_process
1246 FROM oe_exposure_interface
1247 WHERE request_id = G_request_id
1248 AND import_status_code = G_PROCESSING;
1249
1250 --
1251 -- Delete any existing error messages for the selected exposure source and batch
1252 --
1253 DELETE FROM oe_exp_interface_errors
1254 WHERE exposure_source_code = NVL(p_exposure_source_code, exposure_source_code)
1255 AND NVL(batch_id, -99) = NVL(p_batch_id, NVL(batch_id,-99));
1256
1257 --
1258 -- Call Validate_Exposure to validate the exposure rows in the
1259 -- interface table and derive the necessary IDs use for loading
1260 -- the exposure data into the summary table.
1261 --
1262 Validate_Exposure;
1263
1264 --
1265 -- Determine if all rows passed validation. If so, then set the
1266 -- l_validated to TRUE.
1267 --
1268 SELECT count(1)
1269 INTO l_num_rows_validated
1270 FROM oe_exposure_interface
1271 WHERE request_id = G_request_id
1272 AND import_status_code = G_VALIDATED;
1273
1274 --
1275 -- Set the validated flag to TRUE when all rows for the batch are validated.
1276 -- else get the number of rows that failed validation.
1277 --
1278 -- on second thought, why need to do this if we don't really perform a all
1279 -- or nothing approach since we commit every batchsize.
1280 --
1281 IF l_num_rows_validated < l_num_rows_to_process THEN
1282 SELECT count(1)
1283 INTO l_num_rows_failed
1284 FROM oe_exposure_interface
1285 WHERE request_id = G_request_id
1286 AND import_status_code = G_ERROR;
1287 END IF;
1288
1289 IF p_validate_only = 'Y' THEN
1290 -- bug 234505. Reset the import_status_code to NULL
1291 -- since only status of NULL records will be selected
1292 -- for processing in next import run.
1293 UPDATE oe_exposure_interface
1294 SET import_status_code = NULL
1295 WHERE request_id = G_request_id
1296 AND import_status_code = G_VALIDATED;
1297
1298 ELSE
1299 -- Load the credit exposure into the summary table
1300 Import_Exposure;
1301 --
1302 -- Count the number of exposure rows loaded. Default is 0.
1303 --
1304 SELECT count(1)
1305 INTO l_num_rows_imported
1306 FROM oe_exposure_interface
1307 WHERE request_id = G_request_id
1308 AND import_status_code = G_COMPLETE;
1309
1310 --
1311 -- Delete exposure from the interface table after they are imported.
1312 --
1313 DELETE
1314 FROM oe_exposure_interface
1315 WHERE request_id = G_request_id
1316 AND import_status_code = G_COMPLETE;
1317 END IF;
1318 --
1319 -- Set the values for the output variables
1320 --
1321 x_num_rows_to_process := l_num_rows_to_process;
1322 x_num_rows_validated := l_num_rows_validated;
1323 x_num_rows_failed := l_num_rows_failed;
1324 x_num_rows_imported := l_num_rows_imported;
1325
1326 OE_DEBUG_PUB.Add('***** Output Parameters *****');
1327 OE_DEBUG_PUB.Add('x_num_rows_to_process = '||x_num_rows_to_process);
1328 OE_DEBUG_PUB.Add('x_num_rows_validated = '||x_num_rows_validated);
1329 OE_DEBUG_PUB.Add('x_num_rows_failed = '||x_num_rows_failed);
1330 OE_DEBUG_PUB.Add('x_num_rows_imported = '||x_num_rows_imported);
1331 OE_DEBUG_PUB.Add('*****************************');
1332
1333 COMMIT;
1334
1335 OE_DEBUG_PUB.Add('OEXVECEB: Out Import_Credit_Exposure');
1336
1337 EXCEPTION
1338 WHEN FND_API.G_EXC_ERROR THEN
1339 OE_DEBUG_PUB.ADD('OEXVECEB: Import_Credit_Exposure - Expected Error',1);
1340 OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200),1);
1341 ROLLBACK;
1342 RAISE;
1343 WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
1344 OE_DEBUG_PUB.ADD('OEXVECEB: Import_Credit_Exposure - Unexpected Error',1);
1345 OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200),1);
1346 ROLLBACK;
1347 RAISE;
1348 WHEN OTHERS THEN
1349 OE_DEBUG_PUB.ADD('OEXVECEB: Import_Credit_Exposure - Unexpected Other Error',1);
1350 OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200),1);
1351 ROLLBACK;
1352 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1353 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Import_Credit_Exposure');
1354 END IF;
1355 RAISE;
1356 END Import_Credit_Exposure;
1357
1358 --=====================================================================
1359 --NAME: Purge
1360 --TYPE: PRIVATE
1361 --DESCRIPTION: This procedure delete external exposure from the summary
1362 -- table.
1363 --Parameters:
1364 --IN
1365 --OUT
1366 --=====================================================================
1367 PROCEDURE Purge
1368 ( p_org_id IN NUMBER
1369 , p_exposure_source_code IN VARCHAR2
1370 )
1371 IS
1372 CURSOR l_secured_ou_cur IS
1373 SELECT ou.organization_id
1374 FROM hr_operating_units ou
1375 WHERE mo_global.check_access(ou.organization_id) = 'Y';
1376
1377 l_org_id NUMBER;
1378 l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
1379
1380 BEGIN
1381 -- MOAC Start
1382 l_org_id := p_org_id;
1383
1384 IF l_debug_level > 0 THEN
1385 OE_DEBUG_PUB.Add('OEXVECEB: In Purge');
1386 END IF;
1387
1388 IF l_org_id IS NOT NULL THEN
1389 MO_GLOBAL.set_policy_context('S', l_org_id);
1390
1391 DELETE FROM oe_credit_summaries
1392 WHERE exposure_source_code = NVL(p_exposure_source_code, exposure_source_code)
1393 AND balance_type = 18
1394 AND NVL(org_id, -99) = NVL(l_org_id, -99);
1395
1396 IF l_debug_level > 0 THEN
1397 OE_DEBUG_PUB.Add('org_id : ' || l_org_id);
1398 OE_DEBUG_PUB.Add(TO_CHAR(SQL%ROWCOUNT)||' rows purged');
1399 END IF;
1400 ELSE
1401 OPEN l_secured_ou_cur;
1402
1403 LOOP
1404 FETCH l_secured_ou_cur
1405 into l_org_id;
1406 EXIT WHEN l_secured_ou_cur%NOTFOUND;
1407
1408 IF l_org_id IS NULL THEN
1409 l_org_id := mo_global.get_current_org_id;
1410 END IF;
1411
1412 MO_GLOBAL.set_policy_context('S', l_org_id);
1413
1414 DELETE FROM oe_credit_summaries
1415 WHERE exposure_source_code = NVL(p_exposure_source_code, exposure_source_code)
1416 AND balance_type = 18
1417 AND NVL(org_id, -99) = NVL(l_org_id, -99);
1418
1419 IF l_debug_level > 0 THEN
1420 OE_DEBUG_PUB.Add('org_id : ' || l_org_id);
1421 OE_DEBUG_PUB.Add(TO_CHAR(SQL%ROWCOUNT)||' rows purged');
1422 END IF;
1423 END LOOP;
1424
1425 CLOSE l_secured_ou_cur;
1426
1427 END IF;
1428 -- MOAC End
1429
1430 IF l_debug_level > 0 THEN
1431 OE_DEBUG_PUB.Add('OEXVECEB: Out Purge');
1432 END IF;
1433
1434 COMMIT;
1435 EXCEPTION
1436 WHEN OTHERS THEN
1437 ROLLBACK;
1438 IF l_debug_level > 0 THEN
1439 OE_DEBUG_PUB.ADD('OEXVECEB: Purge - Unexpected Error');
1440 OE_DEBUG_PUB.ADD('EXCEPTION: '||SUBSTR(sqlerrm,1,200));
1441 END IF;
1442
1443 IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
1444 OE_MSG_PUB.Add_Exc_Msg(G_PKG_NAME,'Purge');
1445 END IF;
1446 RAISE;
1447 END Purge;
1448 END OE_EXT_CREDIT_EXPOSURE_PVT;