[Home] [Help]
257: x_exist_flag := 'N' ;
258:
259: IF G_debug_flag = 'Y'
260: THEN
261: oe_debug_pub.add(' l_jdate => '|| l_jdate );
262: oe_debug_pub.add(' p_credit_level => '|| p_credit_level );
263: oe_debug_pub.add(' p_site_use_id => '|| p_site_use_id );
264: oe_debug_pub.add(' p_customer_id => '|| p_customer_id );
265: oe_debug_pub.add(' p_party_id => '|| p_party_id );
258:
259: IF G_debug_flag = 'Y'
260: THEN
261: oe_debug_pub.add(' l_jdate => '|| l_jdate );
262: oe_debug_pub.add(' p_credit_level => '|| p_credit_level );
263: oe_debug_pub.add(' p_site_use_id => '|| p_site_use_id );
264: oe_debug_pub.add(' p_customer_id => '|| p_customer_id );
265: oe_debug_pub.add(' p_party_id => '|| p_party_id );
266: END IF;
259: IF G_debug_flag = 'Y'
260: THEN
261: oe_debug_pub.add(' l_jdate => '|| l_jdate );
262: oe_debug_pub.add(' p_credit_level => '|| p_credit_level );
263: oe_debug_pub.add(' p_site_use_id => '|| p_site_use_id );
264: oe_debug_pub.add(' p_customer_id => '|| p_customer_id );
265: oe_debug_pub.add(' p_party_id => '|| p_party_id );
266: END IF;
267:
260: THEN
261: oe_debug_pub.add(' l_jdate => '|| l_jdate );
262: oe_debug_pub.add(' p_credit_level => '|| p_credit_level );
263: oe_debug_pub.add(' p_site_use_id => '|| p_site_use_id );
264: oe_debug_pub.add(' p_customer_id => '|| p_customer_id );
265: oe_debug_pub.add(' p_party_id => '|| p_party_id );
266: END IF;
267:
268: BEGIN
261: oe_debug_pub.add(' l_jdate => '|| l_jdate );
262: oe_debug_pub.add(' p_credit_level => '|| p_credit_level );
263: oe_debug_pub.add(' p_site_use_id => '|| p_site_use_id );
264: oe_debug_pub.add(' p_customer_id => '|| p_customer_id );
265: oe_debug_pub.add(' p_party_id => '|| p_party_id );
266: END IF;
267:
268: BEGIN
269:
469: END IF;
470:
471: IF G_debug_flag = 'Y'
472: THEN
473: oe_debug_pub.add( 'Shipping Horizon Julian Date is:' || l_date, 2 );
474: END IF;
475:
476: RETURN l_date;
477:
496: END IF;
497:
498: IF G_debug_flag = 'Y'
499: THEN
500: oe_debug_pub.add( 'Open Receivables Horizon Julian is:' || l_date, 2 );
501: END IF;
502:
503: RETURN l_date;
504:
738:
739:
740: IF G_debug_flag = 'Y'
741: THEN
742: oe_debug_pub.add
743: (b1 || ' ' || b2 || ' ' || b3 || ' ' || b4 || ' ' || b5 || ' ' || b6 || ' '
744: || b7 || ' ' || b8 || ' ' || b9 || ' ' || b10 || ' ' || b11 || ' ' || b12 ||
745: ' '
746: || b13 || ' ' || b14 || ' ' || b15 || ' ' || b16 || ' ' || b17 || ' ' || b18||
748: ' '|| b29 || ' ' || b30 || ' ' || b31 || ' ' || b32 || ' ' || b33 || ' ' || b34||
749: ' '|| b35 || ' ' || b36,
750: 2 );
751:
752: oe_debug_pub.add( ' Out from balance_types_om_and_ar ');
753: END IF;
754:
755: END balance_types_om_and_ar ;
756:
885: END IF;
886:
887: IF G_debug_flag = 'Y'
888: THEN
889: oe_debug_pub.add
890: (b1 || ' ' || b2 || ' ' || b3 || ' ' || b4 || ' ' || b5 || ' ' || b6 || ' '
891: || b7 || ' ' || b8 || ' ' || b9 || ' ' || b10 || ' ' || b11 || ' ' || b12 || ' '
892: || b13 || ' ' || b14 || ' ' || b15 || ' ' || b16 || ' ' || b17 || ' ' || b18||' '
893: || b23 || ' ' || b24 || ' ' || b25 || ' ' || b26 || ' ' || b27 || ' ' || b28||' '||b29, 2 );
891: || b7 || ' ' || b8 || ' ' || b9 || ' ' || b10 || ' ' || b11 || ' ' || b12 || ' '
892: || b13 || ' ' || b14 || ' ' || b15 || ' ' || b16 || ' ' || b17 || ' ' || b18||' '
893: || b23 || ' ' || b24 || ' ' || b25 || ' ' || b26 || ' ' || b27 || ' ' || b28||' '||b29, 2 );
894:
895: oe_debug_pub.add( ' Out from balance_types_om_nohold ' );
896: END IF;
897: END balance_types_om_nohold;
898: ---------------------------------------------------------------
899:
1034: END IF;
1035:
1036: IF G_debug_flag = 'Y'
1037: THEN
1038: oe_debug_pub.add
1039: (b1 || ' ' || b2 || ' ' || b3 || ' ' || b4 || ' ' || b5 || ' ' || b6 || ' '
1040: || b7 || ' ' || b8 || ' ' || b9 || ' ' || b10 || ' ' || b11 || ' ' || b12 || ' '
1041: || b13 || ' ' || b14 || ' ' || b15 || ' ' || b16 || ' ' || b17 || ' ' || b18|| ' '
1042: || b30 || ' ' || b31 || ' ' || b32 || ' ' || b33 || ' ' || b34 || ' ' || b35|| ' '
1041: || b13 || ' ' || b14 || ' ' || b15 || ' ' || b16 || ' ' || b17 || ' ' || b18|| ' '
1042: || b30 || ' ' || b31 || ' ' || b32 || ' ' || b33 || ' ' || b34 || ' ' || b35|| ' '
1043: || b36, 2 );
1044:
1045: oe_debug_pub.add( ' Out from balance_types_om_hold ' );
1046: END IF;
1047: END balance_types_om_hold;
1048:
1049: PROCEDURE balance_types_om
1253: END IF;
1254:
1255: IF G_debug_flag = 'Y'
1256: THEN
1257: oe_debug_pub.add
1258: (b1 || ' ' || b2 || ' ' || b3 || ' ' || b4 || ' ' || b5 || ' ' || b6 || ' '
1259: || b7 || ' ' || b8 || ' ' || b9 || ' ' || b10 || ' ' || b11 || ' ' || b12 || ' '
1260: || b13 || ' ' || b14 || ' ' || b15 || ' ' || b16 || ' ' || b17 || ' ' || b18 || ' '
1261: || b23 || ' ' || b24 || ' ' || b25 || ' ' || b26 || ' ' || b27 || ' ' || b28|| ' '
1261: || b23 || ' ' || b24 || ' ' || b25 || ' ' || b26 || ' ' || b27 || ' ' || b28|| ' '
1262: || b29 || ' ' || b30 || ' ' || b31 || ' ' || b32 || ' ' || b33 || ' ' || b34|| ' '
1263: || b35 || ' ' || b36, 2 );
1264:
1265: oe_debug_pub.add( ' Out from balance_types_om ' );
1266: END IF;
1267: END balance_types_om;
1268:
1269: -----------------------------------------------------------
1332:
1333:
1334: IF G_debug_flag = 'Y'
1335: THEN
1336: oe_debug_pub.add
1337: (b1 || ' ' || b2 || ' ' || b3 || ' ' || b4 || ' ' || b5 || ' ' || b6 || ' '
1338: || b7 || ' ' || b8 || ' ' || b9 || ' ' || b10 || ' ' || b11 || ' ' || b12 || ' '
1339: || b13 || ' ' || b14 || ' ' || b15 || ' ' || b16 || ' ' || b17 || ' ' || b18 || ' ' || b21 || ' ' ||b22,2 );
1340:
1337: (b1 || ' ' || b2 || ' ' || b3 || ' ' || b4 || ' ' || b5 || ' ' || b6 || ' '
1338: || b7 || ' ' || b8 || ' ' || b9 || ' ' || b10 || ' ' || b11 || ' ' || b12 || ' '
1339: || b13 || ' ' || b14 || ' ' || b15 || ' ' || b16 || ' ' || b17 || ' ' || b18 || ' ' || b21 || ' ' ||b22,2 );
1340:
1341: oe_debug_pub.add( ' Out from balance_types_ar ');
1342: END IF;
1343:
1344: END balance_types_ar;
1345:
1383: l_date := p_date;
1384:
1385: IF G_debug_flag = 'Y'
1386: THEN
1387: oe_debug_pub.add( 'Into calculate_buckets ');
1388: oe_debug_pub.add( 'p_date =>'|| p_date );
1389: oe_debug_pub.add( 'p_interval =>' || p_interval );
1390: END IF;
1391:
1384:
1385: IF G_debug_flag = 'Y'
1386: THEN
1387: oe_debug_pub.add( 'Into calculate_buckets ');
1388: oe_debug_pub.add( 'p_date =>'|| p_date );
1389: oe_debug_pub.add( 'p_interval =>' || p_interval );
1390: END IF;
1391:
1392: -- the Main Bucket
1385: IF G_debug_flag = 'Y'
1386: THEN
1387: oe_debug_pub.add( 'Into calculate_buckets ');
1388: oe_debug_pub.add( 'p_date =>'|| p_date );
1389: oe_debug_pub.add( 'p_interval =>' || p_interval );
1390: END IF;
1391:
1392: -- the Main Bucket
1393: l_level := G_MAX_BUCKET_LEVEL;
1398: x_main_bucket := l_bucket;
1399:
1400: IF G_debug_flag = 'Y'
1401: THEN
1402: oe_debug_pub.add( 'l_date ==> '|| l_date);
1403: oe_debug_pub.add( 'l_bucket_length ==> '|| l_bucket_length );
1404: oe_debug_pub.add( 'Main Bucket ==> '|| x_main_bucket ,1);
1405: END IF;
1406:
1399:
1400: IF G_debug_flag = 'Y'
1401: THEN
1402: oe_debug_pub.add( 'l_date ==> '|| l_date);
1403: oe_debug_pub.add( 'l_bucket_length ==> '|| l_bucket_length );
1404: oe_debug_pub.add( 'Main Bucket ==> '|| x_main_bucket ,1);
1405: END IF;
1406:
1407: IF p_interval is NOT NULL
1400: IF G_debug_flag = 'Y'
1401: THEN
1402: oe_debug_pub.add( 'l_date ==> '|| l_date);
1403: oe_debug_pub.add( 'l_bucket_length ==> '|| l_bucket_length );
1404: oe_debug_pub.add( 'Main Bucket ==> '|| x_main_bucket ,1);
1405: END IF;
1406:
1407: IF p_interval is NOT NULL
1408: THEN
1437: END LOOP;
1438:
1439: IF G_debug_flag = 'Y'
1440: THEN
1441: oe_debug_pub.add( 'Out of the Main LOOP');
1442: oe_debug_pub.add( '-------------------------------------- ');
1443: oe_debug_pub.add( 'selected buckets: ' );
1444: END IF;
1445:
1438:
1439: IF G_debug_flag = 'Y'
1440: THEN
1441: oe_debug_pub.add( 'Out of the Main LOOP');
1442: oe_debug_pub.add( '-------------------------------------- ');
1443: oe_debug_pub.add( 'selected buckets: ' );
1444: END IF;
1445:
1446: i := x_binary_tbl.FIRST;
1439: IF G_debug_flag = 'Y'
1440: THEN
1441: oe_debug_pub.add( 'Out of the Main LOOP');
1442: oe_debug_pub.add( '-------------------------------------- ');
1443: oe_debug_pub.add( 'selected buckets: ' );
1444: END IF;
1445:
1446: i := x_binary_tbl.FIRST;
1447: WHILE i IS NOT NULL LOOP
1447: WHILE i IS NOT NULL LOOP
1448:
1449: IF G_debug_flag = 'Y'
1450: THEN
1451: oe_debug_pub.add
1452: ( 'Bucket Number: ' || x_binary_tbl(i).bucket || ', '
1453: || 'Bucket Length:' || x_binary_tbl(i).bucket_length, 2 );
1454: END IF;
1455:
1455:
1456: i := x_binary_tbl.NEXT(i);
1457: END LOOP;
1458:
1459: oe_debug_pub.add( '-------------------------------------- ');
1460:
1461: END;
1462: ELSE
1463: oe_debug_pub.add( 'No need to calculate Buckets, no Horizon ');
1459: oe_debug_pub.add( '-------------------------------------- ');
1460:
1461: END;
1462: ELSE
1463: oe_debug_pub.add( 'No need to calculate Buckets, no Horizon ');
1464: END IF;
1465:
1466: IF G_debug_flag = 'Y'
1467: THEN
1464: END IF;
1465:
1466: IF G_debug_flag = 'Y'
1467: THEN
1468: oe_debug_pub.add( 'Out calculate_buckets ');
1469: END IF;
1470:
1471:
1472: END calculate_buckets;
1499: EXCEPTION
1500: WHEN GL_CURRENCY_API.NO_RATE OR
1501: GL_CURRENCY_API.INVALID_CURRENCY
1502: THEN
1503: oe_debug_pub.add( 'conversion exception for '
1504: || p_transactional_currency, 1 );
1505: add_error_currency( g_error_curr_tbl, p_transactional_currency );
1506: END;
1507:
2402: -- are the cursor names.
2403:
2404: IF G_debug_flag = 'Y'
2405: THEN
2406: oe_debug_pub.add( 'Into retrieve_exposure ');
2407: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++');
2408: oe_debug_pub.add( 'p_site_use_id ==> '|| p_site_use_id );
2409: oe_debug_pub.add( 'p_customer_id ==> '|| p_customer_id );
2410: oe_debug_pub.add( 'p_party_id ==> ' || p_party_id );
2403:
2404: IF G_debug_flag = 'Y'
2405: THEN
2406: oe_debug_pub.add( 'Into retrieve_exposure ');
2407: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++');
2408: oe_debug_pub.add( 'p_site_use_id ==> '|| p_site_use_id );
2409: oe_debug_pub.add( 'p_customer_id ==> '|| p_customer_id );
2410: oe_debug_pub.add( 'p_party_id ==> ' || p_party_id );
2411: oe_debug_pub.add( 'p_org_id ==> '|| p_org_id);
2404: IF G_debug_flag = 'Y'
2405: THEN
2406: oe_debug_pub.add( 'Into retrieve_exposure ');
2407: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++');
2408: oe_debug_pub.add( 'p_site_use_id ==> '|| p_site_use_id );
2409: oe_debug_pub.add( 'p_customer_id ==> '|| p_customer_id );
2410: oe_debug_pub.add( 'p_party_id ==> ' || p_party_id );
2411: oe_debug_pub.add( 'p_org_id ==> '|| p_org_id);
2412: oe_debug_pub.add( 'p_include_all_flag ==> '|| p_include_all_flag );
2405: THEN
2406: oe_debug_pub.add( 'Into retrieve_exposure ');
2407: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++');
2408: oe_debug_pub.add( 'p_site_use_id ==> '|| p_site_use_id );
2409: oe_debug_pub.add( 'p_customer_id ==> '|| p_customer_id );
2410: oe_debug_pub.add( 'p_party_id ==> ' || p_party_id );
2411: oe_debug_pub.add( 'p_org_id ==> '|| p_org_id);
2412: oe_debug_pub.add( 'p_include_all_flag ==> '|| p_include_all_flag );
2413: oe_debug_pub.add( 'p_main_bucket ==> '|| p_main_bucket );
2406: oe_debug_pub.add( 'Into retrieve_exposure ');
2407: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++');
2408: oe_debug_pub.add( 'p_site_use_id ==> '|| p_site_use_id );
2409: oe_debug_pub.add( 'p_customer_id ==> '|| p_customer_id );
2410: oe_debug_pub.add( 'p_party_id ==> ' || p_party_id );
2411: oe_debug_pub.add( 'p_org_id ==> '|| p_org_id);
2412: oe_debug_pub.add( 'p_include_all_flag ==> '|| p_include_all_flag );
2413: oe_debug_pub.add( 'p_main_bucket ==> '|| p_main_bucket );
2414: oe_debug_pub.add( 'p_global_exposure_flag ==> '|| p_global_exposure_flag );
2407: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++');
2408: oe_debug_pub.add( 'p_site_use_id ==> '|| p_site_use_id );
2409: oe_debug_pub.add( 'p_customer_id ==> '|| p_customer_id );
2410: oe_debug_pub.add( 'p_party_id ==> ' || p_party_id );
2411: oe_debug_pub.add( 'p_org_id ==> '|| p_org_id);
2412: oe_debug_pub.add( 'p_include_all_flag ==> '|| p_include_all_flag );
2413: oe_debug_pub.add( 'p_main_bucket ==> '|| p_main_bucket );
2414: oe_debug_pub.add( 'p_global_exposure_flag ==> '|| p_global_exposure_flag );
2415: oe_debug_pub.add( 'p_limit_curr_code ==> '|| p_limit_curr_code );
2408: oe_debug_pub.add( 'p_site_use_id ==> '|| p_site_use_id );
2409: oe_debug_pub.add( 'p_customer_id ==> '|| p_customer_id );
2410: oe_debug_pub.add( 'p_party_id ==> ' || p_party_id );
2411: oe_debug_pub.add( 'p_org_id ==> '|| p_org_id);
2412: oe_debug_pub.add( 'p_include_all_flag ==> '|| p_include_all_flag );
2413: oe_debug_pub.add( 'p_main_bucket ==> '|| p_main_bucket );
2414: oe_debug_pub.add( 'p_global_exposure_flag ==> '|| p_global_exposure_flag );
2415: oe_debug_pub.add( 'p_limit_curr_code ==> '|| p_limit_curr_code );
2416: oe_debug_pub.add( 'include_returns_flag ==> '|| p_credit_check_rule_rec.include_returns_flag);
2409: oe_debug_pub.add( 'p_customer_id ==> '|| p_customer_id );
2410: oe_debug_pub.add( 'p_party_id ==> ' || p_party_id );
2411: oe_debug_pub.add( 'p_org_id ==> '|| p_org_id);
2412: oe_debug_pub.add( 'p_include_all_flag ==> '|| p_include_all_flag );
2413: oe_debug_pub.add( 'p_main_bucket ==> '|| p_main_bucket );
2414: oe_debug_pub.add( 'p_global_exposure_flag ==> '|| p_global_exposure_flag );
2415: oe_debug_pub.add( 'p_limit_curr_code ==> '|| p_limit_curr_code );
2416: oe_debug_pub.add( 'include_returns_flag ==> '|| p_credit_check_rule_rec.include_returns_flag);
2417: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++');
2410: oe_debug_pub.add( 'p_party_id ==> ' || p_party_id );
2411: oe_debug_pub.add( 'p_org_id ==> '|| p_org_id);
2412: oe_debug_pub.add( 'p_include_all_flag ==> '|| p_include_all_flag );
2413: oe_debug_pub.add( 'p_main_bucket ==> '|| p_main_bucket );
2414: oe_debug_pub.add( 'p_global_exposure_flag ==> '|| p_global_exposure_flag );
2415: oe_debug_pub.add( 'p_limit_curr_code ==> '|| p_limit_curr_code );
2416: oe_debug_pub.add( 'include_returns_flag ==> '|| p_credit_check_rule_rec.include_returns_flag);
2417: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++');
2418: END IF;
2411: oe_debug_pub.add( 'p_org_id ==> '|| p_org_id);
2412: oe_debug_pub.add( 'p_include_all_flag ==> '|| p_include_all_flag );
2413: oe_debug_pub.add( 'p_main_bucket ==> '|| p_main_bucket );
2414: oe_debug_pub.add( 'p_global_exposure_flag ==> '|| p_global_exposure_flag );
2415: oe_debug_pub.add( 'p_limit_curr_code ==> '|| p_limit_curr_code );
2416: oe_debug_pub.add( 'include_returns_flag ==> '|| p_credit_check_rule_rec.include_returns_flag);
2417: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++');
2418: END IF;
2419:
2412: oe_debug_pub.add( 'p_include_all_flag ==> '|| p_include_all_flag );
2413: oe_debug_pub.add( 'p_main_bucket ==> '|| p_main_bucket );
2414: oe_debug_pub.add( 'p_global_exposure_flag ==> '|| p_global_exposure_flag );
2415: oe_debug_pub.add( 'p_limit_curr_code ==> '|| p_limit_curr_code );
2416: oe_debug_pub.add( 'include_returns_flag ==> '|| p_credit_check_rule_rec.include_returns_flag);
2417: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++');
2418: END IF;
2419:
2420: l_total := 0;
2413: oe_debug_pub.add( 'p_main_bucket ==> '|| p_main_bucket );
2414: oe_debug_pub.add( 'p_global_exposure_flag ==> '|| p_global_exposure_flag );
2415: oe_debug_pub.add( 'p_limit_curr_code ==> '|| p_limit_curr_code );
2416: oe_debug_pub.add( 'include_returns_flag ==> '|| p_credit_check_rule_rec.include_returns_flag);
2417: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++');
2418: END IF;
2419:
2420: l_total := 0;
2421: l_br_site_use_id := NULL ;
2433: ---------------------- Site Level Exposure ----------------------
2434:
2435: IF G_debug_flag = 'Y'
2436: THEN
2437: oe_debug_pub.add( 'exposure at site level ',1);
2438: END IF;
2439:
2440: l_site_use_id := p_site_use_id;
2441:
2440: l_site_use_id := p_site_use_id;
2441:
2442: IF G_debug_flag = 'Y'
2443: THEN
2444: oe_debug_pub.add( 'l_site_use_id => '|| l_site_use_id );
2445: END IF;
2446:
2447: IF NVL(p_include_all_flag,'N') = 'N'
2448: THEN
2447: IF NVL(p_include_all_flag,'N') = 'N'
2448: THEN
2449: IF G_debug_flag = 'Y'
2450: THEN
2451: oe_debug_pub.add('site - NOT all currencies');
2452: END IF;
2453: -- exposure is at site level and for a list of currencies
2454:
2455: -- for each currency in the list, loop
2476: INTO l_balance;
2477: ELSE
2478: IF G_debug_flag = 'Y'
2479: THEN
2480: oe_debug_pub.add('OPEN site_balance_stub_ret_csr');
2481: END IF;
2482:
2483: OPEN site_balance_stub_ret_csr;
2484: FETCH site_balance_stub_ret_csr
2485: INTO l_balance;
2486:
2487: IF G_debug_flag = 'Y'
2488: THEN
2489: oe_debug_pub.add('l_balance='||TO_CHAR(l_balance));
2490: END IF;
2491: END IF;
2492:
2493: IF p_credit_check_rule_rec.open_ar_balance_flag = 'Y'
2508: END IF;
2509:
2510: IF G_debug_flag = 'Y'
2511: THEN
2512: oe_debug_pub.add
2513: ( 'stub cursor at ' || l_currency_code || ' currency with balance '
2514: || l_balance || ' and bucket/length ' || l_bucket
2515: || '/' || l_bucket_length, 2 );
2516:
2513: ( 'stub cursor at ' || l_currency_code || ' currency with balance '
2514: || l_balance || ' and bucket/length ' || l_bucket
2515: || '/' || l_bucket_length, 2 );
2516:
2517: oe_debug_pub.add
2518: ( 'stub cursor at ' || l_currency_code
2519: || ' currency with BR balance '
2520: || l_br_balance || ' and bucket/length ' || l_bucket
2521: || '/' || l_bucket_length, 2 );
2557: INTO l_balance;
2558: ELSE
2559: IF G_debug_flag = 'Y'
2560: THEN
2561: oe_debug_pub.add('OPEN site_balance_ret_csr');
2562: END IF;
2563:
2564: OPEN site_balance_ret_csr;
2565: FETCH site_balance_ret_csr
2568:
2569:
2570: IF G_debug_flag = 'Y'
2571: THEN
2572: oe_debug_pub.add
2573: ( 'cursor at ' || l_currency_code || ' currency with balance '
2574: || l_balance || ' and bucket/length ' || l_bucket || '/'
2575: || l_bucket_length, 2 );
2576: END IF;
2593: add_error_currency( x_error_curr_tbl, l_currency_code );
2594:
2595: IF G_debug_flag = 'Y'
2596: THEN
2597: oe_debug_pub.add( 'conversion exception for '
2598: || l_currency_code, 1 );
2599: END IF;
2600: END;
2601: ----change for the Return project
2614: ELSE
2615: -- exposure is at site level and for all currencies
2616: IF G_debug_flag = 'Y'
2617: THEN
2618: oe_debug_pub.add( 'SITE, ALL currencies ');
2619: END IF;
2620: i := 0;
2621:
2622: --------------------------
2635: WHILE NOT site_balance_all_curr_stub_csr%NOTFOUND
2636: LOOP
2637: IF G_debug_flag = 'Y'
2638: THEN
2639: oe_debug_pub.add
2640: ( '(all) stub cursor at ' || l_currency_code || ' currency with balance '
2641: || l_balance || ' and bucket/length '
2642: || l_bucket || '/' || l_bucket_length, 2 );
2643: END IF;
2665:
2666: EXCEPTION
2667: WHEN GL_CURRENCY_API.NO_RATE OR
2668: GL_CURRENCY_API.INVALID_CURRENCY THEN
2669: oe_debug_pub.add( 'conversion exception for '
2670: || l_currency_code, 1 );
2671: add_error_currency( x_error_curr_tbl, l_currency_code );
2672: END;
2673: ELSE
2670: || l_currency_code, 1 );
2671: add_error_currency( x_error_curr_tbl, l_currency_code );
2672: END;
2673: ELSE
2674: oe_debug_pub.add( 'Currency excluded from usages '
2675: || l_currency_code);
2676: END IF; -- exclude curr list
2677:
2678: FETCH site_balance_all_curr_stub_csr
2691:
2692: ELSE
2693: IF G_debug_flag = 'Y'
2694: THEN
2695: oe_debug_pub.add('OPEN site_bal_all_curr_stub_ret_csr');
2696: END IF;
2697:
2698: OPEN site_bal_all_curr_stub_ret_csr;
2699: FETCH site_bal_all_curr_stub_ret_csr
2703: WHILE NOT site_bal_all_curr_stub_ret_csr%NOTFOUND
2704: LOOP
2705: IF G_debug_flag = 'Y'
2706: THEN
2707: oe_debug_pub.add
2708: ( '(all) stub cursor at ' || l_currency_code || ' currency with balance '
2709: || l_balance || ' and bucket/length '
2710: || l_bucket || '/' || l_bucket_length, 2 );
2711: END IF;
2733:
2734: EXCEPTION
2735: WHEN GL_CURRENCY_API.NO_RATE OR
2736: GL_CURRENCY_API.INVALID_CURRENCY THEN
2737: oe_debug_pub.add( 'conversion exception for '
2738: || l_currency_code, 1 );
2739: add_error_currency( x_error_curr_tbl, l_currency_code );
2740: END;
2741: ELSE
2738: || l_currency_code, 1 );
2739: add_error_currency( x_error_curr_tbl, l_currency_code );
2740: END;
2741: ELSE
2742: oe_debug_pub.add( 'Currency excluded from usages '
2743: || l_currency_code);
2744: END IF; -- exclude curr list
2745:
2746: FETCH site_bal_all_curr_stub_ret_csr
2779: WHILE NOT site_br_all_curr_stub_csr%NOTFOUND
2780: LOOP
2781: IF G_debug_flag = 'Y'
2782: THEN
2783: oe_debug_pub.add
2784: ( '(all) stub cursor at ' || l_currency_code
2785: || ' currency with BR balance '
2786: || l_br_balance || ' and bucket/length '
2787: || l_bucket || '/' || l_bucket_length, 2 );
2809:
2810: EXCEPTION
2811: WHEN GL_CURRENCY_API.NO_RATE OR
2812: GL_CURRENCY_API.INVALID_CURRENCY THEN
2813: oe_debug_pub.add( 'conversion exception for '
2814: || l_currency_code, 1 );
2815: add_error_currency( x_error_curr_tbl, l_currency_code );
2816: END;
2817: ELSE
2814: || l_currency_code, 1 );
2815: add_error_currency( x_error_curr_tbl, l_currency_code );
2816: END;
2817: ELSE
2818: oe_debug_pub.add( 'Currency excluded from usages '
2819: || l_currency_code);
2820: END IF; -- exclude curr list
2821:
2822: FETCH site_br_all_curr_stub_csr
2850: WHILE NOT site_balance_all_curr_csr%NOTFOUND
2851: LOOP
2852: IF G_debug_flag = 'Y'
2853: THEN
2854: oe_debug_pub.add
2855: ( 'cursor at ' || l_currency_code || ' currency with balance '
2856: || l_balance || ' and bucket/length ' || l_bucket
2857: || '/' || l_bucket_length, 2 );
2858: END IF;
2879:
2880: EXCEPTION
2881: WHEN GL_CURRENCY_API.NO_RATE OR
2882: GL_CURRENCY_API.INVALID_CURRENCY THEN
2883: oe_debug_pub.add( 'conversion exception for '
2884: || l_currency_code, 1 );
2885: add_error_currency( x_error_curr_tbl, l_currency_code );
2886: END;
2887: ELSE
2884: || l_currency_code, 1 );
2885: add_error_currency( x_error_curr_tbl, l_currency_code );
2886: END;
2887: ELSE
2888: oe_debug_pub.add( 'Currency excluded from usages ');
2889: END IF; -- exclude curr list
2890:
2891: FETCH site_balance_all_curr_csr
2892: INTO l_balance, l_currency_code;
2908: WHILE NOT site_bal_all_curr_ret_csr%NOTFOUND
2909: LOOP
2910: IF G_debug_flag = 'Y'
2911: THEN
2912: oe_debug_pub.add
2913: ( 'cursor at ' || l_currency_code || ' currency with balance '
2914: || l_balance || ' and bucket/length ' || l_bucket
2915: || '/' || l_bucket_length, 2 );
2916: END IF;
2937:
2938: EXCEPTION
2939: WHEN GL_CURRENCY_API.NO_RATE OR
2940: GL_CURRENCY_API.INVALID_CURRENCY THEN
2941: oe_debug_pub.add( 'conversion exception for '
2942: || l_currency_code, 1 );
2943: add_error_currency( x_error_curr_tbl, l_currency_code );
2944: END;
2945: ELSE
2942: || l_currency_code, 1 );
2943: add_error_currency( x_error_curr_tbl, l_currency_code );
2944: END;
2945: ELSE
2946: oe_debug_pub.add( 'Currency excluded from usages ');
2947: END IF; -- exclude curr list
2948:
2949: FETCH site_bal_all_curr_ret_csr
2950: INTO l_balance, l_currency_code;
2964: ELSIF p_customer_id IS NOT NULL
2965: THEN
2966: IF G_debug_flag = 'Y'
2967: THEN
2968: oe_debug_pub.add( 'exposure at customer level', 1 );
2969: END IF;
2970:
2971: IF NVL(p_include_all_flag,'N') = 'N'
2972: THEN
2974: -- for each currency in the list
2975:
2976: IF G_debug_flag = 'Y'
2977: THEN
2978: oe_debug_pub.add( 'NOT ALL currencies ');
2979: END IF;
2980:
2981: i := p_usage_curr_tbl.FIRST;
2982: WHILE i IS NOT NULL
2990: IF p_global_exposure_flag = 'Y'
2991: THEN
2992: IF G_debug_flag = 'Y'
2993: THEN
2994: oe_debug_pub.add( ' Into CUST global ');
2995: END IF;
2996:
2997: ----change for the Return project
2998: IF NVL(p_credit_check_rule_rec.include_returns_flag,'N') = 'N'
3017: END IF;
3018:
3019: IF G_debug_flag = 'Y'
3020: THEN
3021: oe_debug_pub.add
3022: ( 'global stub cursor at ' || l_currency_code
3023: || ' currency with balance ' || l_balance, 2 );
3024: oe_debug_pub.add
3025: ( 'global stub cursor at ' || l_currency_code
3020: THEN
3021: oe_debug_pub.add
3022: ( 'global stub cursor at ' || l_currency_code
3023: || ' currency with balance ' || l_balance, 2 );
3024: oe_debug_pub.add
3025: ( 'global stub cursor at ' || l_currency_code
3026: || ' currency with BR balance ' || l_br_balance, 2 );
3027: END IF;
3028: BEGIN
3040:
3041: EXCEPTION
3042: WHEN GL_CURRENCY_API.NO_RATE OR
3043: GL_CURRENCY_API.INVALID_CURRENCY THEN
3044: oe_debug_pub.add( 'conversion exception for '
3045: || l_currency_code, 1 );
3046: add_error_currency( x_error_curr_tbl, l_currency_code );
3047: END;
3048: ----change for the Return project
3075: END IF;
3076:
3077: IF G_debug_flag = 'Y'
3078: THEN
3079: oe_debug_pub.add
3080: ( 'cust global cursor at ' || l_currency_code
3081: || ' currency with balance ' || l_balance, 2 );
3082: END IF;
3083: BEGIN
3094:
3095: EXCEPTION
3096: WHEN GL_CURRENCY_API.NO_RATE OR
3097: GL_CURRENCY_API.INVALID_CURRENCY THEN
3098: oe_debug_pub.add( 'conversion exception for '
3099: || l_currency_code, 1 );
3100: add_error_currency( x_error_curr_tbl, l_currency_code );
3101: END;
3102:
3113:
3114: ELSE -- not global cck
3115: IF G_debug_flag = 'Y'
3116: THEN
3117: oe_debug_pub.add( 'Into cust NOT global ');
3118: END IF;
3119:
3120: ----change for the Return project
3121: IF NVL(p_credit_check_rule_rec.include_returns_flag,'N') = 'N'
3141: END IF;
3142:
3143: IF G_debug_flag = 'Y'
3144: THEN
3145: oe_debug_pub.add
3146: ( 'cust stub cursor at ' || l_currency_code
3147: || ' currency with balance ' || l_balance, 2 );
3148: oe_debug_pub.add
3149: ( 'cust stub cursor at ' || l_currency_code
3144: THEN
3145: oe_debug_pub.add
3146: ( 'cust stub cursor at ' || l_currency_code
3147: || ' currency with balance ' || l_balance, 2 );
3148: oe_debug_pub.add
3149: ( 'cust stub cursor at ' || l_currency_code
3150: || ' currency with BR balance ' || l_br_balance, 2 );
3151: END IF;
3152:
3164: l_total := l_total + NVL( l_term, 0 );
3165: EXCEPTION
3166: WHEN GL_CURRENCY_API.NO_RATE OR
3167: GL_CURRENCY_API.INVALID_CURRENCY THEN
3168: oe_debug_pub.add( 'conversion exception for '
3169: || l_currency_code, 1 );
3170: add_error_currency( x_error_curr_tbl, l_currency_code );
3171: END;
3172:
3200: END IF;
3201:
3202: IF G_debug_flag = 'Y'
3203: THEN
3204: oe_debug_pub.add
3205: ( 'cust cursor at ' || l_currency_code
3206: || ' currency with balance ' || l_balance, 2 );
3207: END IF;
3208:
3220:
3221: EXCEPTION
3222: WHEN GL_CURRENCY_API.NO_RATE OR
3223: GL_CURRENCY_API.INVALID_CURRENCY THEN
3224: oe_debug_pub.add( 'conversion exception for '
3225: || l_currency_code, 1 );
3226: add_error_currency( x_error_curr_tbl, l_currency_code );
3227: END;
3228:
3245: -- for each currency in the list
3246:
3247: IF G_debug_flag = 'Y'
3248: THEN
3249: oe_debug_pub.add(' Into ALL currencies ');
3250: END IF;
3251:
3252:
3253: i := 0;
3259: IF p_global_exposure_flag = 'Y'
3260: THEN
3261: IF G_debug_flag = 'Y'
3262: THEN
3263: oe_debug_pub.add( ' Into cust GLOBAL');
3264: END IF;
3265:
3266: ----change for the Return project
3267: IF NVL(p_credit_check_rule_rec.include_returns_flag,'N') = 'N'
3275: WHILE NOT cust_all_curr_stub_csr_global%NOTFOUND
3276: LOOP
3277: IF G_debug_flag = 'Y'
3278: THEN
3279: oe_debug_pub.add
3280: ( '(all) stub global cursor at ' || l_currency_code
3281: || ' currency with balance '
3282: || l_balance || ' and bucket/length '
3283: || l_bucket || '/' || l_bucket_length, 2 );
3305:
3306: EXCEPTION
3307: WHEN GL_CURRENCY_API.NO_RATE
3308: OR GL_CURRENCY_API.INVALID_CURRENCY THEN
3309: oe_debug_pub.add( 'conversion exception for '
3310: || l_currency_code, 1 );
3311: add_error_currency( x_error_curr_tbl, l_currency_code );
3312: END;
3313: ELSE
3310: || l_currency_code, 1 );
3311: add_error_currency( x_error_curr_tbl, l_currency_code );
3312: END;
3313: ELSE
3314: oe_debug_pub.add( 'Currency excluded from usages ');
3315: END IF; -- exclude curr list
3316: FETCH cust_all_curr_stub_csr_global
3317: INTO l_balance, l_currency_code;
3318: i := i + 1;
3330: WHILE NOT cust_all_curr_stub_ret_csr_gl%NOTFOUND
3331: LOOP
3332: IF G_debug_flag = 'Y'
3333: THEN
3334: oe_debug_pub.add
3335: ( '(all) stub global cursor at ' || l_currency_code
3336: || ' currency with balance '
3337: || l_balance || ' and bucket/length '
3338: || l_bucket || '/' || l_bucket_length, 2 );
3360:
3361: EXCEPTION
3362: WHEN GL_CURRENCY_API.NO_RATE
3363: OR GL_CURRENCY_API.INVALID_CURRENCY THEN
3364: oe_debug_pub.add( 'conversion exception for '
3365: || l_currency_code, 1 );
3366: add_error_currency( x_error_curr_tbl, l_currency_code );
3367: END;
3368: ELSE
3365: || l_currency_code, 1 );
3366: add_error_currency( x_error_curr_tbl, l_currency_code );
3367: END;
3368: ELSE
3369: oe_debug_pub.add( 'Currency excluded from usages ');
3370: END IF; -- exclude curr list
3371:
3372: FETCH cust_all_curr_stub_ret_csr_gl
3373: INTO l_balance, l_currency_code;
3391: l_bucket_length := G_MAX_BUCKET_LENGTH;
3392:
3393: IF G_debug_flag = 'Y'
3394: THEN
3395: oe_debug_pub.add( ' Into cust GLOBAL');
3396: END IF;
3397:
3398: OPEN cust_br_all_curr_stub_csr_gl;
3399: FETCH cust_br_all_curr_stub_csr_gl
3404: WHILE NOT cust_br_all_curr_stub_csr_gl%NOTFOUND
3405: LOOP
3406: IF G_debug_flag = 'Y'
3407: THEN
3408: oe_debug_pub.add
3409: ( '(all) stub global cursor at ' || l_currency_code
3410: || ' currency with BR balance '
3411: || l_br_balance || ' and bucket/length '
3412: || l_bucket || '/' || l_bucket_length, 2 );
3435:
3436: EXCEPTION
3437: WHEN GL_CURRENCY_API.NO_RATE
3438: OR GL_CURRENCY_API.INVALID_CURRENCY THEN
3439: oe_debug_pub.add( 'conversion exception for '
3440: || l_currency_code, 1 );
3441: add_error_currency( x_error_curr_tbl, l_currency_code );
3442: END;
3443: ELSE
3440: || l_currency_code, 1 );
3441: add_error_currency( x_error_curr_tbl, l_currency_code );
3442: END;
3443: ELSE
3444: oe_debug_pub.add( 'Currency excluded from usages ');
3445: END IF; -- exclude curr list
3446: FETCH cust_br_all_curr_stub_csr_gl
3447: INTO l_br_balance, l_currency_code;
3448: i := i + 1;
3474: WHILE NOT cust_all_curr_csr_global%NOTFOUND
3475: LOOP
3476: IF G_debug_flag = 'Y'
3477: THEN
3478: oe_debug_pub.add
3479: ( 'cust all global cursor at ' || l_currency_code
3480: || ' currency with balance '
3481: || l_balance || ' and bucket/length '
3482: || l_bucket || '/' || l_bucket_length, 2 );
3505:
3506: EXCEPTION
3507: WHEN GL_CURRENCY_API.NO_RATE OR
3508: GL_CURRENCY_API.INVALID_CURRENCY THEN
3509: oe_debug_pub.add( 'conversion exception for ' ||
3510: l_currency_code, 1 );
3511: add_error_currency( x_error_curr_tbl, l_currency_code );
3512: END;
3513: ELSE
3510: l_currency_code, 1 );
3511: add_error_currency( x_error_curr_tbl, l_currency_code );
3512: END;
3513: ELSE
3514: oe_debug_pub.add( 'Currency excluded from usages ');
3515: END IF; -- exclude curr list
3516: FETCH cust_all_curr_csr_global INTO l_balance, l_currency_code;
3517: i := i + 1;
3518: END LOOP;
3530: WHILE NOT cust_all_curr_ret_csr_global%NOTFOUND
3531: LOOP
3532: IF G_debug_flag = 'Y'
3533: THEN
3534: oe_debug_pub.add
3535: ( 'cust all global cursor at ' || l_currency_code
3536: || ' currency with balance '
3537: || l_balance || ' and bucket/length '
3538: || l_bucket || '/' || l_bucket_length, 2 );
3561:
3562: EXCEPTION
3563: WHEN GL_CURRENCY_API.NO_RATE OR
3564: GL_CURRENCY_API.INVALID_CURRENCY THEN
3565: oe_debug_pub.add( 'conversion exception for ' ||
3566: l_currency_code, 1 );
3567: add_error_currency( x_error_curr_tbl, l_currency_code );
3568: END;
3569: ELSE
3566: l_currency_code, 1 );
3567: add_error_currency( x_error_curr_tbl, l_currency_code );
3568: END;
3569: ELSE
3570: oe_debug_pub.add( 'Currency excluded from usages ');
3571: END IF; -- exclude curr list
3572:
3573: FETCH cust_all_curr_ret_csr_global
3574: INTO l_balance, l_currency_code;
3584:
3585: ELSE -- not global cck
3586: IF G_debug_flag = 'Y'
3587: THEN
3588: oe_debug_pub.add(' Into cust NOT global ');
3589: END IF;
3590:
3591: ----change for the Return project
3592: IF NVL(p_credit_check_rule_rec.include_returns_flag,'N') = 'N'
3600: WHILE NOT customer_all_curr_stub_csr%NOTFOUND
3601: LOOP
3602: IF G_debug_flag = 'Y'
3603: THEN
3604: oe_debug_pub.add
3605: ( '(all) stub cursor at ' || l_currency_code
3606: || ' currency with balance '
3607: || l_balance || ' and bucket/length '
3608: || l_bucket || '/' || l_bucket_length, 2 );
3630:
3631: EXCEPTION
3632: WHEN GL_CURRENCY_API.NO_RATE
3633: OR GL_CURRENCY_API.INVALID_CURRENCY THEN
3634: oe_debug_pub.add( 'conversion exception for '
3635: || l_currency_code, 1 );
3636: add_error_currency( x_error_curr_tbl, l_currency_code );
3637: END;
3638: ELSE
3635: || l_currency_code, 1 );
3636: add_error_currency( x_error_curr_tbl, l_currency_code );
3637: END;
3638: ELSE
3639: oe_debug_pub.add(' Currency excluded from usages list ');
3640: END IF;
3641:
3642: FETCH customer_all_curr_stub_csr
3643: INTO l_balance, l_currency_code;
3658: WHILE NOT customer_all_curr_stub_ret_csr%NOTFOUND
3659: LOOP
3660: IF G_debug_flag = 'Y'
3661: THEN
3662: oe_debug_pub.add
3663: ( '(all) stub cursor at ' || l_currency_code
3664: || ' currency with balance '
3665: || l_balance || ' and bucket/length '
3666: || l_bucket || '/' || l_bucket_length, 2 );
3688:
3689: EXCEPTION
3690: WHEN GL_CURRENCY_API.NO_RATE
3691: OR GL_CURRENCY_API.INVALID_CURRENCY THEN
3692: oe_debug_pub.add( 'conversion exception for '
3693: || l_currency_code, 1 );
3694: add_error_currency( x_error_curr_tbl, l_currency_code );
3695: END;
3696: ELSE
3693: || l_currency_code, 1 );
3694: add_error_currency( x_error_curr_tbl, l_currency_code );
3695: END;
3696: ELSE
3697: oe_debug_pub.add(' Currency excluded from usages list ');
3698: END IF;
3699:
3700: FETCH customer_all_curr_stub_ret_csr
3701: INTO l_balance, l_currency_code;
3721: WHILE NOT customer_br_all_curr_stub_csr%NOTFOUND
3722: LOOP
3723: IF G_debug_flag = 'Y'
3724: THEN
3725: oe_debug_pub.add
3726: ( '(all) stub cursor at ' || l_currency_code
3727: || ' currency with BR balance '
3728: || l_br_balance || ' and bucket/length '
3729: || l_bucket || '/' || l_bucket_length, 2 );
3751:
3752: EXCEPTION
3753: WHEN GL_CURRENCY_API.NO_RATE
3754: OR GL_CURRENCY_API.INVALID_CURRENCY THEN
3755: oe_debug_pub.add( 'conversion exception for '
3756: || l_currency_code, 1 );
3757: add_error_currency( x_error_curr_tbl, l_currency_code );
3758: END;
3759: ELSE
3756: || l_currency_code, 1 );
3757: add_error_currency( x_error_curr_tbl, l_currency_code );
3758: END;
3759: ELSE
3760: oe_debug_pub.add(' Currency excluded from usages list ');
3761: END IF;
3762:
3763: FETCH customer_br_all_curr_stub_csr
3764: INTO l_br_balance, l_currency_code;
3790: WHILE NOT customer_balance_all_curr_csr%NOTFOUND
3791: LOOP
3792: IF G_debug_flag = 'Y'
3793: THEN
3794: oe_debug_pub.add
3795: ( '(all) cursor at ' || l_currency_code
3796: || ' currency with balance '
3797: || l_balance || ' and bucket/length '
3798: || l_bucket || '/' || l_bucket_length, 2 );
3821: EXCEPTION
3822: WHEN GL_CURRENCY_API.NO_RATE OR
3823: GL_CURRENCY_API.INVALID_CURRENCY THEN
3824:
3825: oe_debug_pub.add( 'conversion exception for '
3826: || l_currency_code, 1 );
3827: add_error_currency( x_error_curr_tbl, l_currency_code );
3828: END;
3829: ELSE
3826: || l_currency_code, 1 );
3827: add_error_currency( x_error_curr_tbl, l_currency_code );
3828: END;
3829: ELSE
3830: oe_debug_pub.add(' Currency excluded from usages list ');
3831: END IF;
3832:
3833: FETCH customer_balance_all_curr_csr
3834: INTO l_balance, l_currency_code;
3847: WHILE NOT customer_bal_all_curr_ret_csr%NOTFOUND
3848: LOOP
3849: IF G_debug_flag = 'Y'
3850: THEN
3851: oe_debug_pub.add
3852: ( '(all) cursor at ' || l_currency_code
3853: || ' currency with balance '
3854: || l_balance || ' and bucket/length '
3855: || l_bucket || '/' || l_bucket_length, 2 );
3878: EXCEPTION
3879: WHEN GL_CURRENCY_API.NO_RATE OR
3880: GL_CURRENCY_API.INVALID_CURRENCY THEN
3881:
3882: oe_debug_pub.add( 'conversion exception for '
3883: || l_currency_code, 1 );
3884: add_error_currency( x_error_curr_tbl, l_currency_code );
3885: END;
3886: ELSE
3883: || l_currency_code, 1 );
3884: add_error_currency( x_error_curr_tbl, l_currency_code );
3885: END;
3886: ELSE
3887: oe_debug_pub.add(' Currency excluded from usages list ');
3888: END IF;
3889:
3890: FETCH customer_bal_all_curr_ret_csr
3891: INTO l_balance, l_currency_code;
3911: THEN
3912:
3913: IF G_debug_flag = 'Y'
3914: THEN
3915: oe_debug_pub.add( 'exposure at Party level ',1);
3916: oe_debug_pub.add( 'g_use_party_hierarchy => '|| g_use_party_hierarchy );
3917: END IF;
3918:
3919: IF NVL(p_include_all_flag,'N') = 'N'
3912:
3913: IF G_debug_flag = 'Y'
3914: THEN
3915: oe_debug_pub.add( 'exposure at Party level ',1);
3916: oe_debug_pub.add( 'g_use_party_hierarchy => '|| g_use_party_hierarchy );
3917: END IF;
3918:
3919: IF NVL(p_include_all_flag,'N') = 'N'
3920: THEN
3919: IF NVL(p_include_all_flag,'N') = 'N'
3920: THEN
3921: IF G_debug_flag = 'Y'
3922: THEN
3923: oe_debug_pub.add( 'NOT ALL currencies ');
3924: END IF;
3925:
3926: IF g_use_party_hierarchy = 'N'
3927: THEN
3961: END IF;
3962:
3963: IF G_debug_flag = 'Y'
3964: THEN
3965: oe_debug_pub.add
3966: ( 'global stub cursor at ' || l_currency_code
3967: || ' currency with balance ' || l_balance, 2 );
3968: oe_debug_pub.add
3969: ( 'global stub cursor at ' || l_currency_code
3964: THEN
3965: oe_debug_pub.add
3966: ( 'global stub cursor at ' || l_currency_code
3967: || ' currency with balance ' || l_balance, 2 );
3968: oe_debug_pub.add
3969: ( 'global stub cursor at ' || l_currency_code
3970: || ' currency with BR balance ' || l_br_balance, 2 );
3971: END IF;
3972: BEGIN
3984:
3985: EXCEPTION
3986: WHEN GL_CURRENCY_API.NO_RATE OR
3987: GL_CURRENCY_API.INVALID_CURRENCY THEN
3988: oe_debug_pub.add( 'conversion exception for '
3989: || l_currency_code, 1 );
3990: add_error_currency( x_error_curr_tbl, l_currency_code );
3991: END;
3992:
4025: END IF;
4026:
4027: IF G_debug_flag = 'Y'
4028: THEN
4029: oe_debug_pub.add
4030: ( 'global cursor at ' || l_currency_code
4031: || ' currency with balance ' || l_balance, 2 );
4032: END IF;
4033: BEGIN
4044:
4045: EXCEPTION
4046: WHEN GL_CURRENCY_API.NO_RATE OR
4047: GL_CURRENCY_API.INVALID_CURRENCY THEN
4048: oe_debug_pub.add( 'conversion exception for '
4049: || l_currency_code, 1 );
4050: add_error_currency( x_error_curr_tbl, l_currency_code );
4051: END;
4052:
4068:
4069: ELSE --- Hierarchy usages
4070: IF G_debug_flag = 'Y'
4071: THEN
4072: oe_debug_pub.add(' Hierarchy Usages ',1);
4073: oe_debug_pub.add(' OE_CREDIT_CHECK_UTIL.G_hierarchy_type => '||
4074: OE_CREDIT_CHECK_UTIL.G_hierarchy_type );
4075: oe_debug_pub.add(' G_MAX_BUCKET_LENGTH => '||
4076: G_MAX_BUCKET_LENGTH);
4069: ELSE --- Hierarchy usages
4070: IF G_debug_flag = 'Y'
4071: THEN
4072: oe_debug_pub.add(' Hierarchy Usages ',1);
4073: oe_debug_pub.add(' OE_CREDIT_CHECK_UTIL.G_hierarchy_type => '||
4074: OE_CREDIT_CHECK_UTIL.G_hierarchy_type );
4075: oe_debug_pub.add(' G_MAX_BUCKET_LENGTH => '||
4076: G_MAX_BUCKET_LENGTH);
4077: END IF;
4071: THEN
4072: oe_debug_pub.add(' Hierarchy Usages ',1);
4073: oe_debug_pub.add(' OE_CREDIT_CHECK_UTIL.G_hierarchy_type => '||
4074: OE_CREDIT_CHECK_UTIL.G_hierarchy_type );
4075: oe_debug_pub.add(' G_MAX_BUCKET_LENGTH => '||
4076: G_MAX_BUCKET_LENGTH);
4077: END IF;
4078:
4079: i := p_usage_curr_tbl.FIRST;
4108: END IF;
4109:
4110: IF G_debug_flag = 'Y'
4111: THEN
4112: oe_debug_pub.add
4113: ( 'global stub cursor at ' || l_currency_code
4114: || ' currency with balance ' || l_balance, 2 );
4115: END IF;
4116:
4129:
4130: EXCEPTION
4131: WHEN GL_CURRENCY_API.NO_RATE OR
4132: GL_CURRENCY_API.INVALID_CURRENCY THEN
4133: oe_debug_pub.add( 'conversion exception for '
4134: || l_currency_code, 1 );
4135: add_error_currency( x_error_curr_tbl, l_currency_code );
4136: END;
4137:
4166: END IF;
4167:
4168: IF G_debug_flag = 'Y'
4169: THEN
4170: oe_debug_pub.add
4171: ( 'global cursor at ' || l_currency_code
4172: || ' currency with balance ' || l_balance, 2 );
4173: END IF;
4174:
4186:
4187: EXCEPTION
4188: WHEN GL_CURRENCY_API.NO_RATE OR
4189: GL_CURRENCY_API.INVALID_CURRENCY THEN
4190: oe_debug_pub.add( 'conversion exception for '
4191: || l_currency_code, 1 );
4192: add_error_currency( x_error_curr_tbl, l_currency_code );
4193: END;
4194:
4212:
4213: ELSE -- level all currencies
4214: IF G_debug_flag = 'Y'
4215: THEN
4216: oe_debug_pub.add(' Into ALL currencies party ');
4217: END IF;
4218:
4219:
4220: i := 0;
4226: IF g_use_party_hierarchy = 'N'
4227: THEN
4228: IF G_debug_flag = 'Y'
4229: THEN
4230: oe_debug_pub.add( ' Into party all curr ');
4231: END IF;
4232:
4233: ---change for the Return project
4234: IF NVL(p_credit_check_rule_rec.include_returns_flag,'N') = 'N'
4242: WHILE NOT party_all_curr_stub_csr_global%NOTFOUND
4243: LOOP
4244: IF G_debug_flag = 'Y'
4245: THEN
4246: oe_debug_pub.add
4247: ( '(all) stub global cursor at ' || l_currency_code
4248: || ' currency with balance '
4249: || l_balance || ' and bucket/length '
4250: || l_bucket || '/' || l_bucket_length, 2 );
4272:
4273: EXCEPTION
4274: WHEN GL_CURRENCY_API.NO_RATE
4275: OR GL_CURRENCY_API.INVALID_CURRENCY THEN
4276: oe_debug_pub.add( 'conversion exception for '
4277: || l_currency_code, 1 );
4278: add_error_currency( x_error_curr_tbl, l_currency_code );
4279: END;
4280: ELSE
4277: || l_currency_code, 1 );
4278: add_error_currency( x_error_curr_tbl, l_currency_code );
4279: END;
4280: ELSE
4281: oe_debug_pub.add( 'Currency excluded from usages ');
4282: END IF; -- exclude curr list
4283:
4284: FETCH party_all_curr_stub_csr_global
4285: INTO l_balance, l_currency_code;
4299: WHILE NOT party_all_curr_stub_ret_csr_gl%NOTFOUND
4300: LOOP
4301: IF G_debug_flag = 'Y'
4302: THEN
4303: oe_debug_pub.add
4304: ( '(all) stub global cursor at ' || l_currency_code
4305: || ' currency with balance '
4306: || l_balance || ' and bucket/length '
4307: || l_bucket || '/' || l_bucket_length, 2 );
4329:
4330: EXCEPTION
4331: WHEN GL_CURRENCY_API.NO_RATE
4332: OR GL_CURRENCY_API.INVALID_CURRENCY THEN
4333: oe_debug_pub.add( 'conversion exception for '
4334: || l_currency_code, 1 );
4335: add_error_currency( x_error_curr_tbl, l_currency_code );
4336: END;
4337: ELSE
4334: || l_currency_code, 1 );
4335: add_error_currency( x_error_curr_tbl, l_currency_code );
4336: END;
4337: ELSE
4338: oe_debug_pub.add( 'Currency excluded from usages ');
4339: END IF; -- exclude curr list
4340:
4341: FETCH party_all_curr_stub_ret_csr_gl
4342: INTO l_balance, l_currency_code;
4363: LOOP
4364:
4365: IF G_debug_flag = 'Y'
4366: THEN
4367: oe_debug_pub.add
4368: ( '(all) stub global cursor at ' || l_currency_code
4369: || ' currency with BR balance '
4370: || l_br_balance || ' and bucket/length '
4371: || l_bucket || '/' || l_bucket_length, 2 );
4393:
4394: EXCEPTION
4395: WHEN GL_CURRENCY_API.NO_RATE
4396: OR GL_CURRENCY_API.INVALID_CURRENCY THEN
4397: oe_debug_pub.add( 'conversion exception for '
4398: || l_currency_code, 1 );
4399: add_error_currency( x_error_curr_tbl, l_currency_code );
4400: END;
4401: ELSE
4398: || l_currency_code, 1 );
4399: add_error_currency( x_error_curr_tbl, l_currency_code );
4400: END;
4401: ELSE
4402: oe_debug_pub.add( 'Currency excluded from usages ');
4403: END IF; -- exclude curr list
4404:
4405: FETCH party_br_all_curr_stub_csr_gl
4406: INTO l_br_balance, l_currency_code;
4435: LOOP
4436:
4437: IF G_debug_flag = 'Y'
4438: THEN
4439: oe_debug_pub.add
4440: ( 'party all global cursor at ' || l_currency_code
4441: || ' currency with balance '
4442: || l_balance || ' and bucket/length '
4443: || l_bucket || '/' || l_bucket_length, 2 );
4465:
4466: EXCEPTION
4467: WHEN GL_CURRENCY_API.NO_RATE OR
4468: GL_CURRENCY_API.INVALID_CURRENCY THEN
4469: oe_debug_pub.add( 'conversion exception for ' ||
4470: l_currency_code, 1 );
4471: add_error_currency( x_error_curr_tbl, l_currency_code );
4472: END;
4473: ELSE
4470: l_currency_code, 1 );
4471: add_error_currency( x_error_curr_tbl, l_currency_code );
4472: END;
4473: ELSE
4474: oe_debug_pub.add( 'Currency excluded from usages ');
4475: END IF; -- exclude curr list
4476:
4477: FETCH party_all_curr_csr_global INTO l_balance, l_currency_code;
4478:
4494: LOOP
4495:
4496: IF G_debug_flag = 'Y'
4497: THEN
4498: oe_debug_pub.add
4499: ( 'party all global cursor at ' || l_currency_code
4500: || ' currency with balance '
4501: || l_balance || ' and bucket/length '
4502: || l_bucket || '/' || l_bucket_length, 2 );
4524:
4525: EXCEPTION
4526: WHEN GL_CURRENCY_API.NO_RATE OR
4527: GL_CURRENCY_API.INVALID_CURRENCY THEN
4528: oe_debug_pub.add( 'conversion exception for ' ||
4529: l_currency_code, 1 );
4530: add_error_currency( x_error_curr_tbl, l_currency_code );
4531: END;
4532: ELSE
4529: l_currency_code, 1 );
4530: add_error_currency( x_error_curr_tbl, l_currency_code );
4531: END;
4532: ELSE
4533: oe_debug_pub.add( 'Currency excluded from usages ');
4534: END IF; -- exclude curr list
4535:
4536: FETCH party_all_curr_ret_csr_gl
4537: INTO l_balance, l_currency_code;
4549: ELSE -- Hierarchy
4550:
4551: IF G_debug_flag = 'Y'
4552: THEN
4553: oe_debug_pub.add(' Into party Hierarchy ',1);
4554: END IF;
4555:
4556: ---change for the Return project
4557: IF NVL(p_credit_check_rule_rec.include_returns_flag,'N') = 'N'
4567: WHILE NOT party_h_all_curr_stub_csr_gl%NOTFOUND
4568: LOOP
4569: IF G_debug_flag = 'Y'
4570: THEN
4571: oe_debug_pub.add
4572: ( '(all) stub cursor at ' || l_currency_code
4573: || ' currency with balance '
4574: || l_balance || ' and bucket/length '
4575: || l_bucket || '/' || l_bucket_length, 2 );
4598: EXCEPTION
4599: WHEN GL_CURRENCY_API.NO_RATE
4600: OR GL_CURRENCY_API.INVALID_CURRENCY
4601: THEN
4602: oe_debug_pub.add( 'conversion exception for '
4603: || l_currency_code, 1 );
4604: add_error_currency( x_error_curr_tbl, l_currency_code );
4605: END;
4606: ELSE
4603: || l_currency_code, 1 );
4604: add_error_currency( x_error_curr_tbl, l_currency_code );
4605: END;
4606: ELSE
4607: oe_debug_pub.add(' Currency excluded from usages list ');
4608: END IF;
4609:
4610: FETCH party_h_all_curr_stub_csr_gl
4611: INTO l_balance, l_currency_code;
4626: WHILE NOT p_h_all_curr_stub_ret_csr_gl%NOTFOUND
4627: LOOP
4628: IF G_debug_flag = 'Y'
4629: THEN
4630: oe_debug_pub.add
4631: ( '(all) stub cursor at ' || l_currency_code
4632: || ' currency with balance '
4633: || l_balance || ' and bucket/length '
4634: || l_bucket || '/' || l_bucket_length, 2 );
4657: EXCEPTION
4658: WHEN GL_CURRENCY_API.NO_RATE
4659: OR GL_CURRENCY_API.INVALID_CURRENCY
4660: THEN
4661: oe_debug_pub.add( 'conversion exception for '
4662: || l_currency_code, 1 );
4663: add_error_currency( x_error_curr_tbl, l_currency_code );
4664: END;
4665: ELSE
4662: || l_currency_code, 1 );
4663: add_error_currency( x_error_curr_tbl, l_currency_code );
4664: END;
4665: ELSE
4666: oe_debug_pub.add(' Currency excluded from usages list ');
4667: END IF;
4668:
4669: FETCH p_h_all_curr_stub_ret_csr_gl
4670: INTO l_balance, l_currency_code;
4688: WHILE NOT party_br_h_all_curr_stub_gl%NOTFOUND
4689: LOOP
4690: IF G_debug_flag = 'Y'
4691: THEN
4692: oe_debug_pub.add
4693: ( '(all) stub cursor at ' || l_currency_code
4694: || ' currency with BR balance '
4695: || l_BR_balance || ' and bucket/length '
4696: || l_bucket || '/' || l_bucket_length, 2 );
4718:
4719: EXCEPTION
4720: WHEN GL_CURRENCY_API.NO_RATE
4721: OR GL_CURRENCY_API.INVALID_CURRENCY THEN
4722: oe_debug_pub.add( 'conversion exception for '
4723: || l_currency_code, 1 );
4724: add_error_currency( x_error_curr_tbl, l_currency_code );
4725: END;
4726: ELSE
4723: || l_currency_code, 1 );
4724: add_error_currency( x_error_curr_tbl, l_currency_code );
4725: END;
4726: ELSE
4727: oe_debug_pub.add(' Currency excluded from usages list ');
4728: END IF;
4729:
4730: FETCH party_br_h_all_curr_stub_gl
4731: INTO l_br_balance, l_currency_code;
4755: WHILE NOT party_h_all_curr_csr_global%NOTFOUND
4756: LOOP
4757: IF G_debug_flag = 'Y'
4758: THEN
4759: oe_debug_pub.add
4760: ( '(all) cursor at ' || l_currency_code
4761: || ' currency with balance '
4762: || l_balance || ' and bucket/length '
4763: || l_bucket || '/' || l_bucket_length, 2 );
4787: WHEN GL_CURRENCY_API.NO_RATE OR
4788: GL_CURRENCY_API.INVALID_CURRENCY
4789: THEN
4790:
4791: oe_debug_pub.add( 'conversion exception for '
4792: || l_currency_code, 1 );
4793: add_error_currency( x_error_curr_tbl, l_currency_code );
4794: END;
4795: ELSE
4792: || l_currency_code, 1 );
4793: add_error_currency( x_error_curr_tbl, l_currency_code );
4794: END;
4795: ELSE
4796: oe_debug_pub.add(' Currency excluded from usages list ');
4797: END IF;
4798:
4799: FETCH party_h_all_curr_csr_global
4800: INTO l_balance, l_currency_code;
4814: WHILE NOT party_h_all_curr_csr_global%NOTFOUND
4815: LOOP
4816: IF G_debug_flag = 'Y'
4817: THEN
4818: oe_debug_pub.add
4819: ( '(all) cursor at ' || l_currency_code
4820: || ' currency with balance '
4821: || l_balance || ' and bucket/length '
4822: || l_bucket || '/' || l_bucket_length, 2 );
4846: WHEN GL_CURRENCY_API.NO_RATE OR
4847: GL_CURRENCY_API.INVALID_CURRENCY
4848: THEN
4849:
4850: oe_debug_pub.add( 'conversion exception for '
4851: || l_currency_code, 1 );
4852: add_error_currency( x_error_curr_tbl, l_currency_code );
4853: END;
4854: ELSE
4851: || l_currency_code, 1 );
4852: add_error_currency( x_error_curr_tbl, l_currency_code );
4853: END;
4854: ELSE
4855: oe_debug_pub.add(' Currency excluded from usages list ');
4856: END IF;
4857:
4858: FETCH party_h_all_curr_ret_csr_gl
4859: INTO l_balance, l_currency_code;
4875:
4876:
4877: IF G_debug_flag = 'Y'
4878: THEN
4879: oe_debug_pub.add( ' -------------------------------------');
4880: oe_debug_pub.add( ' Final l_total ===> ' || l_total,1 );
4881: oe_debug_pub.add( ' Return out from retrieve_exposure ');
4882: END IF;
4883:
4876:
4877: IF G_debug_flag = 'Y'
4878: THEN
4879: oe_debug_pub.add( ' -------------------------------------');
4880: oe_debug_pub.add( ' Final l_total ===> ' || l_total,1 );
4881: oe_debug_pub.add( ' Return out from retrieve_exposure ');
4882: END IF;
4883:
4884: return NVL(l_total,0) ;
4877: IF G_debug_flag = 'Y'
4878: THEN
4879: oe_debug_pub.add( ' -------------------------------------');
4880: oe_debug_pub.add( ' Final l_total ===> ' || l_total,1 );
4881: oe_debug_pub.add( ' Return out from retrieve_exposure ');
4882: END IF;
4883:
4884: return NVL(l_total,0) ;
4885:
4885:
4886: EXCEPTION
4887:
4888: WHEN OTHERS THEN
4889: oe_debug_pub.add(SQLERRM );
4890:
4891: IF
4892: FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
4893: THEN
4960: BEGIN
4961:
4962: IF G_debug_flag = 'Y'
4963: THEN
4964: oe_debug_pub.add( 'In OEXVCRXB.Init_Summary_Table ', 1 );
4965: END IF;
4966:
4967: -----------
4968: -- Locks needed for periodic operation (no triggers/table handlers):
5016:
5017:
5018: IF G_debug_flag = 'Y'
5019: THEN
5020: oe_debug_pub.add( 'starting to lock tables', 2 );
5021: END IF;
5022:
5023: LOCK TABLE oe_order_lines_all IN EXCLUSIVE MODE;
5024: LOCK TABLE oe_order_headers_all IN EXCLUSIVE MODE;
5028: LOCK TABLE ar_cash_receipt_history_all IN EXCLUSIVE MODE;
5029:
5030: IF G_debug_flag = 'Y'
5031: THEN
5032: oe_debug_pub.add( 'locked tables', 2 );
5033: END IF;
5034: END IF;
5035:
5036:
5051: --before populating the credit exposure.
5052:
5053: IF G_debug_flag = 'Y'
5054: THEN
5055: oe_debug_pub.add( 'Value of the profile OM: Preserve External Credit Balances is :' || NVL(FND_PROFILE.VALUE('ONT_PRESERVE_EXT_CR_BAL'),'Y'),2);
5056: END IF;
5057:
5058: IF NVL(FND_PROFILE.VALUE('ONT_PRESERVE_EXT_CR_BAL'),'Y') = 'Y' THEN
5059: DELETE FROM oe_credit_summaries
5072:
5073:
5074: IF G_debug_flag = 'Y'
5075: THEN
5076: oe_debug_pub.add( 'purged summary table at ' || DO_TIME, 2 );
5077: END IF;
5078:
5079: -- Bug 3960616 : Scan all PreCalc CC Rules to find required Balances.
5080: l_past_due_bal := 'N';
5102: END IF;
5103:
5104: END LOOP;
5105:
5106: oe_debug_pub.add( 'Create Past Due Invoice Balance : '||l_past_due_bal);
5107: oe_debug_pub.add( 'Create Orders on Hold Balance : '||l_order_hold_bal);
5108: oe_debug_pub.add( 'Create Freight Charges Balance : '||l_freight_bal);
5109:
5110: INSERT INTO OE_INIT_CREDIT_SUMM_GTT
5103:
5104: END LOOP;
5105:
5106: oe_debug_pub.add( 'Create Past Due Invoice Balance : '||l_past_due_bal);
5107: oe_debug_pub.add( 'Create Orders on Hold Balance : '||l_order_hold_bal);
5108: oe_debug_pub.add( 'Create Freight Charges Balance : '||l_freight_bal);
5109:
5110: INSERT INTO OE_INIT_CREDIT_SUMM_GTT
5111: (line_ordered_quantity ,
5104: END LOOP;
5105:
5106: oe_debug_pub.add( 'Create Past Due Invoice Balance : '||l_past_due_bal);
5107: oe_debug_pub.add( 'Create Orders on Hold Balance : '||l_order_hold_bal);
5108: oe_debug_pub.add( 'Create Freight Charges Balance : '||l_freight_bal);
5109:
5110: INSERT INTO OE_INIT_CREDIT_SUMM_GTT
5111: (line_ordered_quantity ,
5112: line_unit_selling_price,
5270:
5271:
5272: IF G_debug_flag = 'Y'
5273: THEN
5274: oe_debug_pub.add( 'Start Inserting into summary tables ',1);
5275: END IF;
5276:
5277: -- balance type 1
5278: INSERT INTO oe_credit_summaries
5341:
5342:
5343: IF G_debug_flag = 'Y'
5344: THEN
5345: oe_debug_pub.add( 'G_HEADER_UNINVOICED_ORDERS done at ' || DO_TIME, 2);
5346: END IF;
5347:
5348:
5349: -- balance type 3
5407:
5408:
5409: IF G_debug_flag = 'Y'
5410: THEN
5411: oe_debug_pub.add( 'G_HEADER_UNINVOICED_ORDERS_TAX done at ' || DO_TIME, 2);
5412: END IF;
5413:
5414: ----------------------------------------------
5415: ---------- RETURNS ---------------------------
5478:
5479:
5480: IF G_debug_flag = 'Y'
5481: THEN
5482: oe_debug_pub.add( 'G_HEAD_RETURN_UNINV_ORDERS done at ' || DO_TIME, 2);
5483: END IF;
5484:
5485: -- balance type 25
5486:
5538: , m.order_org_id;
5539:
5540: IF G_debug_flag = 'Y'
5541: THEN
5542: oe_debug_pub.add( 'G_HEAD_RETURN_UNINV_ORD_TAX done at ' || DO_TIME, 2);
5543: END IF;
5544:
5545:
5546: --------------------------
5617:
5618:
5619: IF G_debug_flag = 'Y'
5620: THEN
5621: oe_debug_pub.add( 'G_LINE_UNINVOICED_ORDERS done at ' || DO_TIME, 2);
5622: END IF;
5623:
5624: -- balance type 4
5625:
5683:
5684:
5685: IF G_debug_flag = 'Y'
5686: THEN
5687: oe_debug_pub.add( 'G_LINE_UNINVOICED_ORDERS_TAX done at ' || DO_TIME, 2);
5688: END IF;
5689:
5690: ----------------------------------
5691: ------ RETURNS--------------------
5758:
5759:
5760: IF G_debug_flag = 'Y'
5761: THEN
5762: oe_debug_pub.add( 'G_LINE_RETURN_UNINV_ORDERS done at ' || DO_TIME, 2);
5763: END IF;
5764:
5765: -- balance type 26
5766:
5820:
5821:
5822: IF G_debug_flag = 'Y'
5823: THEN
5824: oe_debug_pub.add( 'G_LINE_RETURN_UNINV_ORD_TAX done at ' || DO_TIME, 2);
5825: END IF;
5826:
5827: -- Bug 4219133 : Insert Freight Balances only if Freight Charges to be Included
5828: IF l_freight_bal = 'Y' THEN
5902:
5903:
5904: IF G_debug_flag = 'Y'
5905: THEN
5906: oe_debug_pub.add( 'G_LINE_UNINVOICED_FREIGHT done at ' || DO_TIME, 2);
5907: END IF;
5908: END IF; -- l_freight_bal
5909:
5910: --------------------------
5981:
5982:
5983: IF G_debug_flag = 'Y'
5984: THEN
5985: oe_debug_pub.add( 'G_LINE_RETURN_UNINV_FREIGHT done at ' || DO_TIME, 2);
5986: END IF;
5987:
5988: -- Bug 4219133 : Insert Freight Balances only if Freight Charges to be Included.
5989:
6065:
6066:
6067: IF G_debug_flag = 'Y'
6068: THEN
6069: oe_debug_pub.add( 'G_HEADER_UNINVOICED_FREIGHT done at ' || DO_TIME, 2 );
6070: END IF;
6071:
6072: END IF; -- l_freight_bal
6073:
6144:
6145:
6146: IF G_debug_flag = 'Y'
6147: THEN
6148: oe_debug_pub.add( 'G_HEAD_RETURN_UNINV_FREIGHT done at ' || DO_TIME, 2 );
6149: END IF;
6150:
6151: -- Bug 4219133 : Insert Freight Balances only if Freight Charges to be Included.
6152:
6310:
6311:
6312: IF G_debug_flag = 'Y'
6313: THEN
6314: oe_debug_pub.add( 'G_HEADER_AND_LINE_FREIGHT done at ' || DO_TIME, 2 );
6315: END IF;
6316: END IF; -- l_freight_bal
6317:
6318: -------------------------------
6473:
6474:
6475: IF G_debug_flag = 'Y'
6476: THEN
6477: oe_debug_pub.add( 'G_HEAD_LINE_RETURN_FREIGHT done at ' || DO_TIME, 2 );
6478: END IF;
6479:
6480: --------------------------
6481: -- Holds
6570:
6571:
6572: IF G_debug_flag = 'Y'
6573: THEN
6574: oe_debug_pub.add( 'G_ORDER_HOLDS done at ' || DO_TIME, 2 );
6575: END IF;
6576:
6577: -- balance type 13
6578:
6646:
6647:
6648: IF G_debug_flag = 'Y'
6649: THEN
6650: oe_debug_pub.add( 'G_ORDER_TAX_HOLDS done at ' || DO_TIME, 2 );
6651: END IF;
6652: END IF; -- IF l_order_hold_bal
6653:
6654: -------------------------------
6763:
6764:
6765: IF G_debug_flag = 'Y'
6766: THEN
6767: oe_debug_pub.add( 'G_ORDER_RETURN_HOLDS done at ' || DO_TIME, 2 );
6768: END IF;
6769:
6770: -- balance type 32
6771:
6834:
6835:
6836: IF G_debug_flag = 'Y'
6837: THEN
6838: oe_debug_pub.add( 'G_ORDER_RETURN_TAX_HOLDS done at ' || DO_TIME, 2 );
6839: END IF;
6840:
6841: IF l_order_hold_bal = 'Y' THEN
6842: --------------------------
6922:
6923:
6924: IF G_debug_flag = 'Y'
6925: THEN
6926: oe_debug_pub.add( 'G_LINE_HOLDS done at ' || DO_TIME, 2 );
6927: END IF;
6928:
6929: -- balance type 14
6930:
6998:
6999:
7000: IF G_debug_flag = 'Y'
7001: THEN
7002: oe_debug_pub.add( 'G_LINE_TAX_HOLDS done at ' || DO_TIME, 2 );
7003: END IF;
7004: END IF; -- IF l_order_hold_bal
7005:
7006: --------------------------------
7082:
7083:
7084: IF G_debug_flag = 'Y'
7085: THEN
7086: oe_debug_pub.add( 'G_LINE_RETURN_HOLDS done at ' || DO_TIME, 2 );
7087: END IF;
7088:
7089: -- balance type 33
7090:
7152: ;
7153:
7154: IF G_debug_flag = 'Y'
7155: THEN
7156: oe_debug_pub.add( 'G_LINE_RETURN_TAX_HOLDS done at ' || DO_TIME, 2 );
7157: END IF;
7158:
7159:
7160: IF l_order_hold_bal = 'Y' THEN
7244:
7245:
7246: IF G_debug_flag = 'Y'
7247: THEN
7248: oe_debug_pub.add( 'G_LINE_FREIGHT_HOLDS done at ' || DO_TIME, 2 );
7249: END IF;
7250: END IF; -- IF l_order_hold_bal
7251:
7252: ---------------------------------
7331:
7332:
7333: IF G_debug_flag = 'Y'
7334: THEN
7335: oe_debug_pub.add( 'G_LINE_RETURN_FREIGHT_HOLDS done at ' || DO_TIME, 2 );
7336: END IF;
7337:
7338:
7339: IF l_order_hold_bal = 'Y' THEN
7423:
7424:
7425: IF G_debug_flag = 'Y'
7426: THEN
7427: oe_debug_pub.add( 'G_ORDER_FREIGHT_HOLDS done at ' || DO_TIME, 2 );
7428: END IF;
7429: END IF; -- IF l_order_hold_bal
7430:
7431: --------------------------------------
7510:
7511:
7512: IF G_debug_flag = 'Y'
7513: THEN
7514: oe_debug_pub.add( 'G_ORDER_RETURN_FREIGHT_HOLDS done at ' || DO_TIME, 2 );
7515: END IF;
7516:
7517:
7518: IF l_order_hold_bal = 'Y' THEN
7611: ;
7612:
7613: IF G_debug_flag = 'Y'
7614: THEN
7615: oe_debug_pub.add( 'G_HEADER_LINE_FREIGHT_HOLDS done at ' || DO_TIME, 2 );
7616: END IF;
7617: END IF; -- IF l_order_hold_bal
7618:
7619: --------------------------------------
7711: ;
7712:
7713: IF G_debug_flag = 'Y'
7714: THEN
7715: oe_debug_pub.add( 'G_H_L_RETURN_FREIGHT_HOLDS done at ' || DO_TIME, 2 );
7716: END IF;
7717:
7718:
7719:
7788:
7789:
7790: IF G_debug_flag = 'Y'
7791: THEN
7792: oe_debug_pub.add( 'G_INVOICES done at ' || DO_TIME, 2 );
7793: END IF;
7794:
7795:
7796: -----------------------------------------
7885: ;
7886:
7887: IF G_debug_flag = 'Y'
7888: THEN
7889: oe_debug_pub.add( 'G_PAYMENTS_AT_RISK done at ' || DO_TIME, 2 );
7890: END IF;
7891:
7892: ---------------------- BR ------------------
7893:
7957:
7958:
7959: IF G_debug_flag = 'Y'
7960: THEN
7961: oe_debug_pub.add( 'G_BR_INVOICES done at ' || DO_TIME, 2 );
7962: END IF;
7963:
7964:
7965: -----------------------------------------
8054: ;
8055:
8056: IF G_debug_flag = 'Y'
8057: THEN
8058: oe_debug_pub.add( 'G_PAYMENTS_AT_RISK done at ' || DO_TIME, 2 );
8059: END IF;
8060:
8061: -- Bug 4219133 : Insert Past Due Inv Balance only if needed.
8062:
8125: ;
8126:
8127: IF G_debug_flag = 'Y'
8128: THEN
8129: oe_debug_pub.add( 'past due invt ' || DO_TIME, 2 );
8130: END IF;
8131:
8132: END IF; -- IF l_past_due_bal
8133:
8132: END IF; -- IF l_past_due_bal
8133:
8134: IF G_debug_flag = 'Y'
8135: THEN
8136: oe_debug_pub.add( ' End of first phase ');
8137: END IF;
8138:
8139:
8140: ----------------------
8160: END IF;
8161:
8162: IF G_debug_flag = 'Y'
8163: THEN
8164: oe_debug_pub.add( 'level 0 records updated at ' || DO_TIME, 2 );
8165: END IF;
8166:
8167: l_level := 1;
8168: l_bucket_length := POWER( 2, l_level );
8229: ;
8230:
8231: IF G_debug_flag = 'Y'
8232: THEN
8233: oe_debug_pub.add( 'level ' || l_level || ' records done at '||DO_TIME, 2 );
8234: END IF;
8235:
8236: l_level := l_level + 1;
8237: l_bucket_length := POWER( 2, l_level );
8238: END LOOP;
8239:
8240: IF G_debug_flag = 'Y'
8241: THEN
8242: oe_debug_pub.add( ' Done Inserting into summary table, about to COMMIT');
8243: END IF;
8244:
8245: COMMIT;
8246:
8247: x_retcode := 0;
8248:
8249: IF G_debug_flag = 'Y'
8250: THEN
8251: oe_debug_pub.add( ' after COMMIT command');
8252: oe_debug_pub.add( 'Exiting OE_CREDIT_EXPOSURE_PVT.Init_Summary_Table ', 1 );
8253: END IF;
8254:
8255: EXCEPTION
8248:
8249: IF G_debug_flag = 'Y'
8250: THEN
8251: oe_debug_pub.add( ' after COMMIT command');
8252: oe_debug_pub.add( 'Exiting OE_CREDIT_EXPOSURE_PVT.Init_Summary_Table ', 1 );
8253: END IF;
8254:
8255: EXCEPTION
8256:
8352:
8353:
8354: IF G_debug_flag = 'Y'
8355: THEN
8356: oe_debug_pub.add( 'IN OEXVCRXB.OE_CREDIT_EXPOSURE_PVT.Get_Exposure', 1 );
8357: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++++++++++');
8358: oe_debug_pub.add( 'p_customer_id : ' || p_customer_id, 2 );
8359: oe_debug_pub.add( 'p_site_use_id : ' || p_site_use_id, 2 );
8360: oe_debug_pub.add( 'p_party_id : '|| p_party_id ,2);
8353:
8354: IF G_debug_flag = 'Y'
8355: THEN
8356: oe_debug_pub.add( 'IN OEXVCRXB.OE_CREDIT_EXPOSURE_PVT.Get_Exposure', 1 );
8357: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++++++++++');
8358: oe_debug_pub.add( 'p_customer_id : ' || p_customer_id, 2 );
8359: oe_debug_pub.add( 'p_site_use_id : ' || p_site_use_id, 2 );
8360: oe_debug_pub.add( 'p_party_id : '|| p_party_id ,2);
8361: oe_debug_pub.add( 'p_header_id : ' || p_header_id, 2 );
8354: IF G_debug_flag = 'Y'
8355: THEN
8356: oe_debug_pub.add( 'IN OEXVCRXB.OE_CREDIT_EXPOSURE_PVT.Get_Exposure', 1 );
8357: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++++++++++');
8358: oe_debug_pub.add( 'p_customer_id : ' || p_customer_id, 2 );
8359: oe_debug_pub.add( 'p_site_use_id : ' || p_site_use_id, 2 );
8360: oe_debug_pub.add( 'p_party_id : '|| p_party_id ,2);
8361: oe_debug_pub.add( 'p_header_id : ' || p_header_id, 2 );
8362: oe_debug_pub.add( 'g_use_party_hierarchy : '|| g_use_party_hierarchy );
8355: THEN
8356: oe_debug_pub.add( 'IN OEXVCRXB.OE_CREDIT_EXPOSURE_PVT.Get_Exposure', 1 );
8357: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++++++++++');
8358: oe_debug_pub.add( 'p_customer_id : ' || p_customer_id, 2 );
8359: oe_debug_pub.add( 'p_site_use_id : ' || p_site_use_id, 2 );
8360: oe_debug_pub.add( 'p_party_id : '|| p_party_id ,2);
8361: oe_debug_pub.add( 'p_header_id : ' || p_header_id, 2 );
8362: oe_debug_pub.add( 'g_use_party_hierarchy : '|| g_use_party_hierarchy );
8363: oe_debug_pub.add( ' p_include_all_flag => '||
8356: oe_debug_pub.add( 'IN OEXVCRXB.OE_CREDIT_EXPOSURE_PVT.Get_Exposure', 1 );
8357: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++++++++++');
8358: oe_debug_pub.add( 'p_customer_id : ' || p_customer_id, 2 );
8359: oe_debug_pub.add( 'p_site_use_id : ' || p_site_use_id, 2 );
8360: oe_debug_pub.add( 'p_party_id : '|| p_party_id ,2);
8361: oe_debug_pub.add( 'p_header_id : ' || p_header_id, 2 );
8362: oe_debug_pub.add( 'g_use_party_hierarchy : '|| g_use_party_hierarchy );
8363: oe_debug_pub.add( ' p_include_all_flag => '||
8364: p_include_all_flag );
8357: oe_debug_pub.add( '+++++++++++++++++++++++++++++++++++++++++++');
8358: oe_debug_pub.add( 'p_customer_id : ' || p_customer_id, 2 );
8359: oe_debug_pub.add( 'p_site_use_id : ' || p_site_use_id, 2 );
8360: oe_debug_pub.add( 'p_party_id : '|| p_party_id ,2);
8361: oe_debug_pub.add( 'p_header_id : ' || p_header_id, 2 );
8362: oe_debug_pub.add( 'g_use_party_hierarchy : '|| g_use_party_hierarchy );
8363: oe_debug_pub.add( ' p_include_all_flag => '||
8364: p_include_all_flag );
8365: oe_debug_pub.add(' p_global_exposure_flag => '||
8358: oe_debug_pub.add( 'p_customer_id : ' || p_customer_id, 2 );
8359: oe_debug_pub.add( 'p_site_use_id : ' || p_site_use_id, 2 );
8360: oe_debug_pub.add( 'p_party_id : '|| p_party_id ,2);
8361: oe_debug_pub.add( 'p_header_id : ' || p_header_id, 2 );
8362: oe_debug_pub.add( 'g_use_party_hierarchy : '|| g_use_party_hierarchy );
8363: oe_debug_pub.add( ' p_include_all_flag => '||
8364: p_include_all_flag );
8365: oe_debug_pub.add(' p_global_exposure_flag => '||
8366: p_global_exposure_flag );
8359: oe_debug_pub.add( 'p_site_use_id : ' || p_site_use_id, 2 );
8360: oe_debug_pub.add( 'p_party_id : '|| p_party_id ,2);
8361: oe_debug_pub.add( 'p_header_id : ' || p_header_id, 2 );
8362: oe_debug_pub.add( 'g_use_party_hierarchy : '|| g_use_party_hierarchy );
8363: oe_debug_pub.add( ' p_include_all_flag => '||
8364: p_include_all_flag );
8365: oe_debug_pub.add(' p_global_exposure_flag => '||
8366: p_global_exposure_flag );
8367: oe_debug_pub.add(' p_need_exposure_details => '||
8361: oe_debug_pub.add( 'p_header_id : ' || p_header_id, 2 );
8362: oe_debug_pub.add( 'g_use_party_hierarchy : '|| g_use_party_hierarchy );
8363: oe_debug_pub.add( ' p_include_all_flag => '||
8364: p_include_all_flag );
8365: oe_debug_pub.add(' p_global_exposure_flag => '||
8366: p_global_exposure_flag );
8367: oe_debug_pub.add(' p_need_exposure_details => '||
8368: p_need_exposure_details );
8369: oe_debug_pub.add
8363: oe_debug_pub.add( ' p_include_all_flag => '||
8364: p_include_all_flag );
8365: oe_debug_pub.add(' p_global_exposure_flag => '||
8366: p_global_exposure_flag );
8367: oe_debug_pub.add(' p_need_exposure_details => '||
8368: p_need_exposure_details );
8369: oe_debug_pub.add
8370: ( 'p_credit_check_rule_rec.credit_check_rule_id: '
8371: || p_credit_check_rule_rec.credit_check_rule_id, 2 );
8365: oe_debug_pub.add(' p_global_exposure_flag => '||
8366: p_global_exposure_flag );
8367: oe_debug_pub.add(' p_need_exposure_details => '||
8368: p_need_exposure_details );
8369: oe_debug_pub.add
8370: ( 'p_credit_check_rule_rec.credit_check_rule_id: '
8371: || p_credit_check_rule_rec.credit_check_rule_id, 2 );
8372: oe_debug_pub.add
8373: ( 'p_credit_check_rule_rec.name: '
8368: p_need_exposure_details );
8369: oe_debug_pub.add
8370: ( 'p_credit_check_rule_rec.credit_check_rule_id: '
8371: || p_credit_check_rule_rec.credit_check_rule_id, 2 );
8372: oe_debug_pub.add
8373: ( 'p_credit_check_rule_rec.name: '
8374: || p_credit_check_rule_rec.name, 2 );
8375: oe_debug_pub.add
8376: ( 'p_credit_check_rule_rec.failure_result_code: '
8371: || p_credit_check_rule_rec.credit_check_rule_id, 2 );
8372: oe_debug_pub.add
8373: ( 'p_credit_check_rule_rec.name: '
8374: || p_credit_check_rule_rec.name, 2 );
8375: oe_debug_pub.add
8376: ( 'p_credit_check_rule_rec.failure_result_code: '
8377: || p_credit_check_rule_rec.failure_result_code, 2 );
8378: oe_debug_pub.add
8379: ( 'p_credit_check_rule_rec.open_ar_balance_flag : '
8374: || p_credit_check_rule_rec.name, 2 );
8375: oe_debug_pub.add
8376: ( 'p_credit_check_rule_rec.failure_result_code: '
8377: || p_credit_check_rule_rec.failure_result_code, 2 );
8378: oe_debug_pub.add
8379: ( 'p_credit_check_rule_rec.open_ar_balance_flag : '
8380: || p_credit_check_rule_rec.open_ar_balance_flag, 2 );
8381: oe_debug_pub.add
8382: ( 'p_credit_check_rule_rec.uninvoiced_orders_flag : '
8377: || p_credit_check_rule_rec.failure_result_code, 2 );
8378: oe_debug_pub.add
8379: ( 'p_credit_check_rule_rec.open_ar_balance_flag : '
8380: || p_credit_check_rule_rec.open_ar_balance_flag, 2 );
8381: oe_debug_pub.add
8382: ( 'p_credit_check_rule_rec.uninvoiced_orders_flag : '
8383: || p_credit_check_rule_rec.uninvoiced_orders_flag, 2 );
8384: oe_debug_pub.add
8385: ( 'p_credit_check_rule_rec.orders_on_hold_flag : '
8380: || p_credit_check_rule_rec.open_ar_balance_flag, 2 );
8381: oe_debug_pub.add
8382: ( 'p_credit_check_rule_rec.uninvoiced_orders_flag : '
8383: || p_credit_check_rule_rec.uninvoiced_orders_flag, 2 );
8384: oe_debug_pub.add
8385: ( 'p_credit_check_rule_rec.orders_on_hold_flag : '
8386: || p_credit_check_rule_rec.orders_on_hold_flag, 2 );
8387: oe_debug_pub.add
8388: ( 'p_credit_check_rule_rec.shipping_interval : '
8383: || p_credit_check_rule_rec.uninvoiced_orders_flag, 2 );
8384: oe_debug_pub.add
8385: ( 'p_credit_check_rule_rec.orders_on_hold_flag : '
8386: || p_credit_check_rule_rec.orders_on_hold_flag, 2 );
8387: oe_debug_pub.add
8388: ( 'p_credit_check_rule_rec.shipping_interval : '
8389: || p_credit_check_rule_rec.shipping_interval, 2 );
8390: oe_debug_pub.add
8391: ( 'p_credit_check_rule_rec.open_ar_days : '
8386: || p_credit_check_rule_rec.orders_on_hold_flag, 2 );
8387: oe_debug_pub.add
8388: ( 'p_credit_check_rule_rec.shipping_interval : '
8389: || p_credit_check_rule_rec.shipping_interval, 2 );
8390: oe_debug_pub.add
8391: ( 'p_credit_check_rule_rec.open_ar_days : '
8392: || p_credit_check_rule_rec.open_ar_days, 2 );
8393: oe_debug_pub.add
8394: ( 'p_credit_check_rule_rec.start_date_active : '
8389: || p_credit_check_rule_rec.shipping_interval, 2 );
8390: oe_debug_pub.add
8391: ( 'p_credit_check_rule_rec.open_ar_days : '
8392: || p_credit_check_rule_rec.open_ar_days, 2 );
8393: oe_debug_pub.add
8394: ( 'p_credit_check_rule_rec.start_date_active : '
8395: || p_credit_check_rule_rec.start_date_active, 2 );
8396: oe_debug_pub.add
8397: ( 'p_credit_check_rule_rec.end_date_active : '
8392: || p_credit_check_rule_rec.open_ar_days, 2 );
8393: oe_debug_pub.add
8394: ( 'p_credit_check_rule_rec.start_date_active : '
8395: || p_credit_check_rule_rec.start_date_active, 2 );
8396: oe_debug_pub.add
8397: ( 'p_credit_check_rule_rec.end_date_active : '
8398: || p_credit_check_rule_rec.end_date_active, 2 );
8399: oe_debug_pub.add
8400: ( 'p_credit_check_rule_rec.include_payments_at_risk_flag : '
8395: || p_credit_check_rule_rec.start_date_active, 2 );
8396: oe_debug_pub.add
8397: ( 'p_credit_check_rule_rec.end_date_active : '
8398: || p_credit_check_rule_rec.end_date_active, 2 );
8399: oe_debug_pub.add
8400: ( 'p_credit_check_rule_rec.include_payments_at_risk_flag : '
8401: || p_credit_check_rule_rec.include_payments_at_risk_flag, 2 );
8402: oe_debug_pub.add
8403: ( 'p_credit_check_rule_rec.include_tax_flag : '
8398: || p_credit_check_rule_rec.end_date_active, 2 );
8399: oe_debug_pub.add
8400: ( 'p_credit_check_rule_rec.include_payments_at_risk_flag : '
8401: || p_credit_check_rule_rec.include_payments_at_risk_flag, 2 );
8402: oe_debug_pub.add
8403: ( 'p_credit_check_rule_rec.include_tax_flag : '
8404: || p_credit_check_rule_rec.include_tax_flag, 2 );
8405: oe_debug_pub.add
8406: ( 'p_credit_check_rule_rec.maximum_days_past_due : '
8401: || p_credit_check_rule_rec.include_payments_at_risk_flag, 2 );
8402: oe_debug_pub.add
8403: ( 'p_credit_check_rule_rec.include_tax_flag : '
8404: || p_credit_check_rule_rec.include_tax_flag, 2 );
8405: oe_debug_pub.add
8406: ( 'p_credit_check_rule_rec.maximum_days_past_due : '
8407: || p_credit_check_rule_rec.maximum_days_past_due, 2 );
8408: oe_debug_pub.add
8409: ( 'p_credit_check_rule_rec.QUICK_CR_CHECK_FLAG : '
8404: || p_credit_check_rule_rec.include_tax_flag, 2 );
8405: oe_debug_pub.add
8406: ( 'p_credit_check_rule_rec.maximum_days_past_due : '
8407: || p_credit_check_rule_rec.maximum_days_past_due, 2 );
8408: oe_debug_pub.add
8409: ( 'p_credit_check_rule_rec.QUICK_CR_CHECK_FLAG : '
8410: || p_credit_check_rule_rec.QUICK_CR_CHECK_FLAG, 2 );
8411: oe_debug_pub.add
8412: ( 'p_credit_check_rule_rec.incl_freight_charges_flag : '
8407: || p_credit_check_rule_rec.maximum_days_past_due, 2 );
8408: oe_debug_pub.add
8409: ( 'p_credit_check_rule_rec.QUICK_CR_CHECK_FLAG : '
8410: || p_credit_check_rule_rec.QUICK_CR_CHECK_FLAG, 2 );
8411: oe_debug_pub.add
8412: ( 'p_credit_check_rule_rec.incl_freight_charges_flag : '
8413: || p_credit_check_rule_rec.incl_freight_charges_flag, 2 );
8414: oe_debug_pub.add
8415: ( 'p_credit_check_rule_rec.shipping_horizon : '
8410: || p_credit_check_rule_rec.QUICK_CR_CHECK_FLAG, 2 );
8411: oe_debug_pub.add
8412: ( 'p_credit_check_rule_rec.incl_freight_charges_flag : '
8413: || p_credit_check_rule_rec.incl_freight_charges_flag, 2 );
8414: oe_debug_pub.add
8415: ( 'p_credit_check_rule_rec.shipping_horizon : '
8416: || p_credit_check_rule_rec.shipping_horizon, 2 );
8417: oe_debug_pub.add
8418: ( 'p_credit_check_rule_rec.include_external_exposure_flag : '
8413: || p_credit_check_rule_rec.incl_freight_charges_flag, 2 );
8414: oe_debug_pub.add
8415: ( 'p_credit_check_rule_rec.shipping_horizon : '
8416: || p_credit_check_rule_rec.shipping_horizon, 2 );
8417: oe_debug_pub.add
8418: ( 'p_credit_check_rule_rec.include_external_exposure_flag : '
8419: || p_credit_check_rule_rec.include_external_exposure_flag, 2 );
8420: oe_debug_pub.add
8421: ( 'p_credit_check_rule_rec.include_returns_flag : '
8416: || p_credit_check_rule_rec.shipping_horizon, 2 );
8417: oe_debug_pub.add
8418: ( 'p_credit_check_rule_rec.include_external_exposure_flag : '
8419: || p_credit_check_rule_rec.include_external_exposure_flag, 2 );
8420: oe_debug_pub.add
8421: ( 'p_credit_check_rule_rec.include_returns_flag : '
8422: || p_credit_check_rule_rec.include_returns_flag, 2 );
8423: oe_debug_pub.add
8424: ( 'p_system_parameters_rec.org_id : '
8419: || p_credit_check_rule_rec.include_external_exposure_flag, 2 );
8420: oe_debug_pub.add
8421: ( 'p_credit_check_rule_rec.include_returns_flag : '
8422: || p_credit_check_rule_rec.include_returns_flag, 2 );
8423: oe_debug_pub.add
8424: ( 'p_system_parameters_rec.org_id : '
8425: || p_system_parameters_rec.org_id, 2 );
8426: oe_debug_pub.add
8427: ( 'p_system_parameters_rec.master_organization_id : '
8422: || p_credit_check_rule_rec.include_returns_flag, 2 );
8423: oe_debug_pub.add
8424: ( 'p_system_parameters_rec.org_id : '
8425: || p_system_parameters_rec.org_id, 2 );
8426: oe_debug_pub.add
8427: ( 'p_system_parameters_rec.master_organization_id : '
8428: || p_system_parameters_rec.master_organization_id, 2 );
8429: oe_debug_pub.add( 'p_limit_curr_code: ' || p_limit_curr_code, 2 );
8430: oe_debug_pub.add( 'p_include_all_flag: ' || p_include_all_flag, 2 );
8425: || p_system_parameters_rec.org_id, 2 );
8426: oe_debug_pub.add
8427: ( 'p_system_parameters_rec.master_organization_id : '
8428: || p_system_parameters_rec.master_organization_id, 2 );
8429: oe_debug_pub.add( 'p_limit_curr_code: ' || p_limit_curr_code, 2 );
8430: oe_debug_pub.add( 'p_include_all_flag: ' || p_include_all_flag, 2 );
8431: oe_debug_pub.add( 'p_global_exposure_flag = '|| p_global_exposure_flag );
8432:
8433: END IF;
8426: oe_debug_pub.add
8427: ( 'p_system_parameters_rec.master_organization_id : '
8428: || p_system_parameters_rec.master_organization_id, 2 );
8429: oe_debug_pub.add( 'p_limit_curr_code: ' || p_limit_curr_code, 2 );
8430: oe_debug_pub.add( 'p_include_all_flag: ' || p_include_all_flag, 2 );
8431: oe_debug_pub.add( 'p_global_exposure_flag = '|| p_global_exposure_flag );
8432:
8433: END IF;
8434:
8427: ( 'p_system_parameters_rec.master_organization_id : '
8428: || p_system_parameters_rec.master_organization_id, 2 );
8429: oe_debug_pub.add( 'p_limit_curr_code: ' || p_limit_curr_code, 2 );
8430: oe_debug_pub.add( 'p_include_all_flag: ' || p_include_all_flag, 2 );
8431: oe_debug_pub.add( 'p_global_exposure_flag = '|| p_global_exposure_flag );
8432:
8433: END IF;
8434:
8435: ------------------------------------------------------------------
8450:
8451:
8452: IF G_debug_flag = 'Y'
8453: THEN
8454: oe_debug_pub.add( 'final g_use_party_hierarchy ==> '||
8455: g_use_party_hierarchy );
8456: END IF;
8457: ------------------------------------------------------------------
8458:
8460:
8461: WHILE i IS NOT NULL LOOP
8462: IF G_debug_flag = 'Y'
8463: THEN
8464: oe_debug_pub.add( 'usage curr: '
8465: || p_usage_curr_tbl(i).usage_curr_code, 2 );
8466: END IF;
8467: i := p_usage_curr_tbl.NEXT(i);
8468: END LOOP;
8500: THEN
8501: -- Order Management Summaries
8502: IF G_debug_flag = 'Y'
8503: THEN
8504: oe_debug_pub.add(' OM Balances ');
8505: oe_debug_pub.add(' call balance_types_om ');
8506: END IF;
8507:
8508: balance_types_om( p_credit_check_rule_rec );
8501: -- Order Management Summaries
8502: IF G_debug_flag = 'Y'
8503: THEN
8504: oe_debug_pub.add(' OM Balances ');
8505: oe_debug_pub.add(' call balance_types_om ');
8506: END IF;
8507:
8508: balance_types_om( p_credit_check_rule_rec );
8509:
8525: AND p_credit_check_rule_rec.shipping_interval IS NULL
8526: THEN
8527: IF G_debug_flag = 'Y'
8528: THEN
8529: oe_debug_pub.add(' Call balance_types_om_and_ar ');
8530: END IF;
8531:
8532: balance_types_om_and_ar( p_credit_check_rule_rec );
8533:
8535:
8536: ELSE
8537: IF G_debug_flag = 'Y'
8538: THEN
8539: oe_debug_pub.add(' Select OM and AR exposure separately ');
8540: END IF;
8541: END IF;
8542:
8543: IF G_debug_flag = 'Y'
8541: END IF;
8542:
8543: IF G_debug_flag = 'Y'
8544: THEN
8545: oe_debug_pub.add(' l_both_exposure ==> '|| l_both_exposure );
8546: oe_debug_pub.add(' Call retrieve_exposure ');
8547: END IF;
8548:
8549: l_total := l_total + retrieve_exposure
8542:
8543: IF G_debug_flag = 'Y'
8544: THEN
8545: oe_debug_pub.add(' l_both_exposure ==> '|| l_both_exposure );
8546: oe_debug_pub.add(' Call retrieve_exposure ');
8547: END IF;
8548:
8549: l_total := l_total + retrieve_exposure
8550: ( p_binary_tbl => l_binary_tbl
8562: );
8563:
8564: IF G_debug_flag = 'Y'
8565: THEN
8566: oe_debug_pub.add(' Out of Retreive exposure with total ==> '
8567: || l_total );
8568: END IF;
8569:
8570: ---------------------------------
8573: IF l_both_exposure = 'N'
8574: THEN
8575: IF G_debug_flag = 'Y'
8576: THEN
8577: oe_debug_pub.add(' AR exposure ');
8578: oe_debug_pub.add(' Call balance_types_ar ');
8579: END IF;
8580:
8581: balance_types_ar( p_credit_check_rule_rec );
8574: THEN
8575: IF G_debug_flag = 'Y'
8576: THEN
8577: oe_debug_pub.add(' AR exposure ');
8578: oe_debug_pub.add(' Call balance_types_ar ');
8579: END IF;
8580:
8581: balance_types_ar( p_credit_check_rule_rec );
8582:
8587: , l_main_bucket
8588: , l_binary_tbl
8589: );
8590:
8591: oe_debug_pub.add(' Call retrieve_exposure for AR balance ');
8592:
8593: l_total := l_total + retrieve_exposure
8594: ( p_binary_tbl => l_binary_tbl
8595: , p_site_use_id => p_site_use_id
8606: );
8607:
8608: IF G_debug_flag = 'Y'
8609: THEN
8610: oe_debug_pub.add(' out of AR retrieve_exposure ==> '||
8611: l_total );
8612: END IF;
8613:
8614: ELSE
8613:
8614: ELSE
8615: IF G_debug_flag = 'Y'
8616: THEN
8617: oe_debug_pub.add(' NO need for AR expe as both exp calculated = '
8618: || l_both_exposure );
8619: END IF;
8620: END IF;
8621:
8625:
8626: ------------------------------ support detail exposure ------------
8627: IF G_debug_flag = 'Y'
8628: THEN
8629: oe_debug_pub.add(' support detail exposure ',1);
8630: END IF;
8631:
8632: balance_types_om_hold( p_credit_check_rule_rec );
8633:
8654: );
8655:
8656: IF G_debug_flag = 'Y'
8657: THEN
8658: oe_debug_pub.add(' x_order_hold_amount => '||
8659: x_order_hold_amount );
8660:
8661: oe_debug_pub.add(' x_order_amount => '|| x_order_amount );
8662:
8657: THEN
8658: oe_debug_pub.add(' x_order_hold_amount => '||
8659: x_order_hold_amount );
8660:
8661: oe_debug_pub.add(' x_order_amount => '|| x_order_amount );
8662:
8663: oe_debug_pub.add(' x_ar_amount ' || x_ar_amount );
8664: END IF;
8665: --------------------------------------------------------
8659: x_order_hold_amount );
8660:
8661: oe_debug_pub.add(' x_order_amount => '|| x_order_amount );
8662:
8663: oe_debug_pub.add(' x_ar_amount ' || x_ar_amount );
8664: END IF;
8665: --------------------------------------------------------
8666: balance_types_om_nohold ( p_credit_check_rule_rec );
8667:
8688: );
8689:
8690: IF G_debug_flag = 'Y'
8691: THEN
8692: oe_debug_pub.add(' x_order_amount => '|| x_order_amount );
8693: oe_debug_pub.add(' AR exposure ');
8694: oe_debug_pub.add(' Call balance_types_ar ');
8695: END IF;
8696:
8689:
8690: IF G_debug_flag = 'Y'
8691: THEN
8692: oe_debug_pub.add(' x_order_amount => '|| x_order_amount );
8693: oe_debug_pub.add(' AR exposure ');
8694: oe_debug_pub.add(' Call balance_types_ar ');
8695: END IF;
8696:
8697: balance_types_ar( p_credit_check_rule_rec );
8690: IF G_debug_flag = 'Y'
8691: THEN
8692: oe_debug_pub.add(' x_order_amount => '|| x_order_amount );
8693: oe_debug_pub.add(' AR exposure ');
8694: oe_debug_pub.add(' Call balance_types_ar ');
8695: END IF;
8696:
8697: balance_types_ar( p_credit_check_rule_rec );
8698:
8703: , l_main_bucket
8704: , l_binary_tbl
8705: );
8706:
8707: oe_debug_pub.add(' Call retrieve_exposure for AR balance ');
8708:
8709: x_ar_amount := retrieve_exposure
8710: ( p_binary_tbl => l_binary_tbl
8711: , p_site_use_id => p_site_use_id
8722: );
8723:
8724: IF G_debug_flag = 'Y'
8725: THEN
8726: oe_debug_pub.add(' out of AR retrieve_exposure ==> '||
8727: x_ar_amount );
8728: END IF;
8729:
8730: --bug# 5597791
8729:
8730: --bug# 5597791
8731: IF G_debug_flag = 'Y'
8732: THEN
8733: oe_debug_pub.add(' x_order_hold_amount => '||
8734: x_order_hold_amount );
8735:
8736: oe_debug_pub.add(' x_order_amount => '|| x_order_amount );
8737:
8732: THEN
8733: oe_debug_pub.add(' x_order_hold_amount => '||
8734: x_order_hold_amount );
8735:
8736: oe_debug_pub.add(' x_order_amount => '|| x_order_amount );
8737:
8738: oe_debug_pub.add(' x_ar_amount ' || x_ar_amount );
8739: END IF;
8740:
8734: x_order_hold_amount );
8735:
8736: oe_debug_pub.add(' x_order_amount => '|| x_order_amount );
8737:
8738: oe_debug_pub.add(' x_ar_amount ' || x_ar_amount );
8739: END IF;
8740:
8741: x_total_exposure := nvl(x_order_hold_amount,0) + nvl(x_order_amount,0) + nvl(x_ar_amount,0);
8742:
8746: x_return_status := FND_API.G_RET_STS_SUCCESS;
8747:
8748: IF G_debug_flag = 'Y'
8749: THEN
8750: oe_debug_pub.add(' ');
8751: oe_debug_pub.add(' Total FINAL exposure ==> '|| x_total_exposure,1 );
8752: oe_debug_pub.add(' ');
8753: oe_debug_pub.add('*************************************************');
8754: oe_debug_pub.add( 'Exiting OE_CREDIT_EXPOSURE_PVT.Get_Exposure', 1 );
8747:
8748: IF G_debug_flag = 'Y'
8749: THEN
8750: oe_debug_pub.add(' ');
8751: oe_debug_pub.add(' Total FINAL exposure ==> '|| x_total_exposure,1 );
8752: oe_debug_pub.add(' ');
8753: oe_debug_pub.add('*************************************************');
8754: oe_debug_pub.add( 'Exiting OE_CREDIT_EXPOSURE_PVT.Get_Exposure', 1 );
8755: END IF;
8748: IF G_debug_flag = 'Y'
8749: THEN
8750: oe_debug_pub.add(' ');
8751: oe_debug_pub.add(' Total FINAL exposure ==> '|| x_total_exposure,1 );
8752: oe_debug_pub.add(' ');
8753: oe_debug_pub.add('*************************************************');
8754: oe_debug_pub.add( 'Exiting OE_CREDIT_EXPOSURE_PVT.Get_Exposure', 1 );
8755: END IF;
8756:
8749: THEN
8750: oe_debug_pub.add(' ');
8751: oe_debug_pub.add(' Total FINAL exposure ==> '|| x_total_exposure,1 );
8752: oe_debug_pub.add(' ');
8753: oe_debug_pub.add('*************************************************');
8754: oe_debug_pub.add( 'Exiting OE_CREDIT_EXPOSURE_PVT.Get_Exposure', 1 );
8755: END IF;
8756:
8757:
8750: oe_debug_pub.add(' ');
8751: oe_debug_pub.add(' Total FINAL exposure ==> '|| x_total_exposure,1 );
8752: oe_debug_pub.add(' ');
8753: oe_debug_pub.add('*************************************************');
8754: oe_debug_pub.add( 'Exiting OE_CREDIT_EXPOSURE_PVT.Get_Exposure', 1 );
8755: END IF;
8756:
8757:
8758: l_total := 0;
8766: x_return_status := FND_API.G_RET_STS_ERROR;
8767:
8768: WHEN FND_API.G_EXC_UNEXPECTED_ERROR THEN
8769: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8770: oe_debug_pub.add(SQLERRM );
8771:
8772: WHEN OTHERS THEN
8773: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8774: oe_debug_pub.add(SQLERRM );
8770: oe_debug_pub.add(SQLERRM );
8771:
8772: WHEN OTHERS THEN
8773: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR ;
8774: oe_debug_pub.add(SQLERRM );
8775: IF
8776: FND_MSG_PUB.Check_Msg_Level( FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR )
8777: THEN
8778: -- FND_MSG_PUB.Add_Exc_Msg( l_api_name, 'Get_Exposure' );