[Home] [Help]
99: FUNCTION check_debug_flag
100: RETURN VARCHAR2
101: IS
102: BEGIN
103: IF oe_debug_pub.g_debug_level > 0
104: THEN
105: RETURN('Y');
106: ELSE
107: RETURN('N') ;
125: l_id NUMBER ;
126: BEGIN
127: IF G_debug_flag = 'Y'
128: THEN
129: OE_DEBUG_PUB.Add(' Into Check_drawee_exists ');
130: END IF;
131:
132: OPEN drawee_exists_C ;
133: FETCH drawee_exists_C INTO l_id ;
174: BEGIN
175:
176: IF G_debug_flag = 'Y'
177: THEN
178: oe_debug_pub.add('get_drawee_site_use_id ');
179: END IF;
180:
181: BEGIN
182: SELECT cust_acct_site_id
199:
200:
201: IF G_debug_flag = 'Y'
202: THEN
203: oe_debug_pub.add(' No Data found for BR in get_drawee_site_use_id ');
204: END IF;
205:
206: WHEN TOO_MANY_ROWS
207: THEN
208: l_site_use_id := NULL ;
209:
210: IF G_debug_flag = 'Y'
211: THEN
212: oe_debug_pub.add(' TOO_MANY_ROWS BR in get_drawee_site_use_id ');
213: END IF;
214: END ;
215: return ( l_site_use_id );
216:
358: BEGIN
359:
360: IF G_debug_flag = 'Y'
361: THEN
362: OE_DEBUG_PUB.ADD('IN GET_hierarchy_party_limit');
363: OE_DEBUG_PUB.ADD('p_party_id ==> '|| p_party_id );
364: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
365: OE_DEBUG_PUB.ADD('party_hier_limit_no_incl_csr ');
366: END IF;
359:
360: IF G_debug_flag = 'Y'
361: THEN
362: OE_DEBUG_PUB.ADD('IN GET_hierarchy_party_limit');
363: OE_DEBUG_PUB.ADD('p_party_id ==> '|| p_party_id );
364: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
365: OE_DEBUG_PUB.ADD('party_hier_limit_no_incl_csr ');
366: END IF;
367:
360: IF G_debug_flag = 'Y'
361: THEN
362: OE_DEBUG_PUB.ADD('IN GET_hierarchy_party_limit');
363: OE_DEBUG_PUB.ADD('p_party_id ==> '|| p_party_id );
364: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
365: OE_DEBUG_PUB.ADD('party_hier_limit_no_incl_csr ');
366: END IF;
367:
368: OPEN party_hier_limit_no_incl_csr ;
361: THEN
362: OE_DEBUG_PUB.ADD('IN GET_hierarchy_party_limit');
363: OE_DEBUG_PUB.ADD('p_party_id ==> '|| p_party_id );
364: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
365: OE_DEBUG_PUB.ADD('party_hier_limit_no_incl_csr ');
366: END IF;
367:
368: OPEN party_hier_limit_no_incl_csr ;
369: FETCH party_hier_limit_no_incl_csr
391: IF x_limit_curr_code IS NULL
392: THEN
393: IF G_debug_flag = 'Y'
394: THEN
395: OE_DEBUG_PUB.ADD('party_hier_limit_incl_csr ');
396: END IF;
397:
398: OPEN party_hier_limit_incl_csr ;
399: FETCH party_hier_limit_incl_csr
421: IF x_limit_curr_code IS NULL
422: THEN
423: IF G_debug_flag = 'Y'
424: THEN
425: OE_DEBUG_PUB.ADD('party_hier_single_limit_csr');
426: END IF;
427:
428: OPEN party_hier_single_limit_csr ;
429: FETCH party_hier_single_limit_csr
450:
451: IF G_debug_flag = 'Y'
452: THEN
453:
454: OE_DEBUG_PUB.ADD('OUT GET_hierarchy_party_limit' );
455: END IF;
456:
457: EXCEPTION
458: WHEN OTHERS THEN
504:
505: IF G_debug_flag = 'Y'
506: THEN
507:
508: OE_DEBUG_PUB.ADD('IN GET_single_party_limit');
509: OE_DEBUG_PUB.ADD('p_party_id ==> '|| p_party_id );
510: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
511: OE_DEBUG_PUB.ADD(' party_single_limit_csr ');
512: END IF;
505: IF G_debug_flag = 'Y'
506: THEN
507:
508: OE_DEBUG_PUB.ADD('IN GET_single_party_limit');
509: OE_DEBUG_PUB.ADD('p_party_id ==> '|| p_party_id );
510: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
511: OE_DEBUG_PUB.ADD(' party_single_limit_csr ');
512: END IF;
513:
506: THEN
507:
508: OE_DEBUG_PUB.ADD('IN GET_single_party_limit');
509: OE_DEBUG_PUB.ADD('p_party_id ==> '|| p_party_id );
510: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
511: OE_DEBUG_PUB.ADD(' party_single_limit_csr ');
512: END IF;
513:
514: OPEN party_single_limit_csr ;
507:
508: OE_DEBUG_PUB.ADD('IN GET_single_party_limit');
509: OE_DEBUG_PUB.ADD('p_party_id ==> '|| p_party_id );
510: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
511: OE_DEBUG_PUB.ADD(' party_single_limit_csr ');
512: END IF;
513:
514: OPEN party_single_limit_csr ;
515: FETCH party_single_limit_csr
535:
536:
537: IF G_debug_flag = 'Y'
538: THEN
539: OE_DEBUG_PUB.ADD('OUT GET_single_party_limit' );
540: END IF;
541:
542: EXCEPTION
543: WHEN OTHERS THEN
637: BEGIN
638: IF G_debug_flag = 'Y'
639: THEN
640:
641: OE_DEBUG_PUB.ADD('IN GET_multi_party_limit ');
642: OE_DEBUG_PUB.ADD('p_party_id ==> '|| p_party_id );
643: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
644: OE_DEBUG_PUB.ADD('party_multi_limit_no_incl_csr ');
645: END IF;
638: IF G_debug_flag = 'Y'
639: THEN
640:
641: OE_DEBUG_PUB.ADD('IN GET_multi_party_limit ');
642: OE_DEBUG_PUB.ADD('p_party_id ==> '|| p_party_id );
643: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
644: OE_DEBUG_PUB.ADD('party_multi_limit_no_incl_csr ');
645: END IF;
646:
639: THEN
640:
641: OE_DEBUG_PUB.ADD('IN GET_multi_party_limit ');
642: OE_DEBUG_PUB.ADD('p_party_id ==> '|| p_party_id );
643: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
644: OE_DEBUG_PUB.ADD('party_multi_limit_no_incl_csr ');
645: END IF;
646:
647: OPEN party_multi_limit_no_incl_csr ;
640:
641: OE_DEBUG_PUB.ADD('IN GET_multi_party_limit ');
642: OE_DEBUG_PUB.ADD('p_party_id ==> '|| p_party_id );
643: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
644: OE_DEBUG_PUB.ADD('party_multi_limit_no_incl_csr ');
645: END IF;
646:
647: OPEN party_multi_limit_no_incl_csr ;
648: FETCH party_multi_limit_no_incl_csr
670: IF x_limit_curr_code IS NULL
671: THEN
672: IF G_debug_flag = 'Y'
673: THEN
674: OE_DEBUG_PUB.ADD('party_multi_limit_incl_csr ');
675: END IF;
676:
677: OPEN party_multi_limit_incl_csr ;
678: FETCH party_multi_limit_incl_csr
698:
699: IF G_debug_flag = 'Y'
700: THEN
701:
702: OE_DEBUG_PUB.ADD('OUT GET_multi_party_limit ');
703: END IF;
704:
705: EXCEPTION
706: WHEN OTHERS THEN
733: BEGIN
734: IF G_debug_flag = 'Y'
735: THEN
736:
737: OE_DEBUG_PUB.ADD('IN GET_party_limit ');
738: OE_DEBUG_PUB.ADD('p_party_id ==> '|| p_party_id );
739: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
740: END IF;
741:
734: IF G_debug_flag = 'Y'
735: THEN
736:
737: OE_DEBUG_PUB.ADD('IN GET_party_limit ');
738: OE_DEBUG_PUB.ADD('p_party_id ==> '|| p_party_id );
739: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
740: END IF;
741:
742: x_return_status := 'N' ;
735: THEN
736:
737: OE_DEBUG_PUB.ADD('IN GET_party_limit ');
738: OE_DEBUG_PUB.ADD('p_party_id ==> '|| p_party_id );
739: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
740: END IF;
741:
742: x_return_status := 'N' ;
743: x_limit_curr_code := NULL;
757:
758: --Bug 4561384
759: IF G_debug_flag = 'Y'
760: THEN
761: OE_DEBUG_PUB.ADD(' Out GET_multi_party_Limit '|| l_return_status ); --bug 4967828
762: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
763: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
764: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
765: END IF;
758: --Bug 4561384
759: IF G_debug_flag = 'Y'
760: THEN
761: OE_DEBUG_PUB.ADD(' Out GET_multi_party_Limit '|| l_return_status ); --bug 4967828
762: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
763: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
764: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
765: END IF;
766:
759: IF G_debug_flag = 'Y'
760: THEN
761: OE_DEBUG_PUB.ADD(' Out GET_multi_party_Limit '|| l_return_status ); --bug 4967828
762: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
763: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
764: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
765: END IF;
766:
767: x_return_status := NVL(l_return_status, 'N'); --bug 4967828
760: THEN
761: OE_DEBUG_PUB.ADD(' Out GET_multi_party_Limit '|| l_return_status ); --bug 4967828
762: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
763: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
764: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
765: END IF;
766:
767: x_return_status := NVL(l_return_status, 'N'); --bug 4967828
768: IF l_return_status = 'Y'
770: AND x_overall_limit is NULL
771: THEN
772: l_return_status := 'N' ; --bug 4967828
773: x_limit_curr_code := NULL ;
774: OE_DEBUG_PUB.ADD(' Limits NULL for party MULTI ');
775: END IF;
776: --Bug 4561384
777:
778: IF NVL(l_return_status, 'N') = 'N' --bug 4967828
789:
790: --Bug 4561384
791: IF G_debug_flag = 'Y'
792: THEN
793: OE_DEBUG_PUB.ADD(' Out GET_single_party_Limit '|| l_return_status ); --bug 4967828
794: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
795: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
796: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
797: END IF;
790: --Bug 4561384
791: IF G_debug_flag = 'Y'
792: THEN
793: OE_DEBUG_PUB.ADD(' Out GET_single_party_Limit '|| l_return_status ); --bug 4967828
794: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
795: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
796: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
797: END IF;
798:
791: IF G_debug_flag = 'Y'
792: THEN
793: OE_DEBUG_PUB.ADD(' Out GET_single_party_Limit '|| l_return_status ); --bug 4967828
794: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
795: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
796: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
797: END IF;
798:
799: x_return_status := NVL(l_return_status, 'N'); --bug 4967828
792: THEN
793: OE_DEBUG_PUB.ADD(' Out GET_single_party_Limit '|| l_return_status ); --bug 4967828
794: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
795: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
796: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
797: END IF;
798:
799: x_return_status := NVL(l_return_status, 'N'); --bug 4967828
800: IF l_return_status = 'Y'
802: AND x_overall_limit is NULL
803: THEN
804: l_return_status := 'N' ; --bug 4967828
805: x_limit_curr_code := NULL ;
806: OE_DEBUG_PUB.ADD(' Limits NULL for party SITE ');
807: END IF;
808: END IF;
809: --Bug 4561384
810:
807: END IF;
808: END IF;
809: --Bug 4561384
810:
811: OE_DEBUG_PUB.ADD('l_return_status '|| l_return_status ); --bug 4967828
812: OE_DEBUG_PUB.ADD('x_return_status '|| x_return_status );
813:
814: IF NVL(l_return_status, 'N') = 'N' --bug 4967828
815: THEN
808: END IF;
809: --Bug 4561384
810:
811: OE_DEBUG_PUB.ADD('l_return_status '|| l_return_status ); --bug 4967828
812: OE_DEBUG_PUB.ADD('x_return_status '|| x_return_status );
813:
814: IF NVL(l_return_status, 'N') = 'N' --bug 4967828
815: THEN
816: GET_hierarchy_party_limit
825:
826: --Bug 4561384
827: IF G_debug_flag = 'Y'
828: THEN
829: OE_DEBUG_PUB.ADD(' Out GET_hierarchy_party_Limit '|| l_return_status ); --bug 4967828
830: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
831: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
832: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
833: END IF;
826: --Bug 4561384
827: IF G_debug_flag = 'Y'
828: THEN
829: OE_DEBUG_PUB.ADD(' Out GET_hierarchy_party_Limit '|| l_return_status ); --bug 4967828
830: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
831: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
832: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
833: END IF;
834:
827: IF G_debug_flag = 'Y'
828: THEN
829: OE_DEBUG_PUB.ADD(' Out GET_hierarchy_party_Limit '|| l_return_status ); --bug 4967828
830: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
831: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
832: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
833: END IF;
834:
835: x_return_status := NVL(l_return_status,'Y') ; --bug 4967828
828: THEN
829: OE_DEBUG_PUB.ADD(' Out GET_hierarchy_party_Limit '|| l_return_status ); --bug 4967828
830: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
831: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
832: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
833: END IF;
834:
835: x_return_status := NVL(l_return_status,'Y') ; --bug 4967828
836: IF l_return_status = 'Y'
838: AND x_overall_limit is NULL
839: THEN
840: l_return_status := 'N' ; --bug 4967828
841: x_limit_curr_code := NULL ;
842: OE_DEBUG_PUB.ADD(' Limits NULL for party HIERARCHY ');
843: END IF;
844: --Bug 4561384
845:
846: END IF; -- hierarchy
851:
852: IF G_debug_flag = 'Y'
853: THEN
854:
855: OE_DEBUG_PUB.ADD('x_credit_limit_party ==> '|| x_credit_limit_entity_id );
856: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
857: OE_DEBUG_PUB.ADD('x_trx_limit ==> '|| x_trx_limit );
858: OE_DEBUG_PUB.ADD('x_overall_limit ==> '|| x_overall_limit );
859: OE_DEBUG_PUB.ADD('x_return_status ==> '|| x_return_status );
852: IF G_debug_flag = 'Y'
853: THEN
854:
855: OE_DEBUG_PUB.ADD('x_credit_limit_party ==> '|| x_credit_limit_entity_id );
856: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
857: OE_DEBUG_PUB.ADD('x_trx_limit ==> '|| x_trx_limit );
858: OE_DEBUG_PUB.ADD('x_overall_limit ==> '|| x_overall_limit );
859: OE_DEBUG_PUB.ADD('x_return_status ==> '|| x_return_status );
860: OE_DEBUG_PUB.ADD('OUT GET_party_limit ');
853: THEN
854:
855: OE_DEBUG_PUB.ADD('x_credit_limit_party ==> '|| x_credit_limit_entity_id );
856: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
857: OE_DEBUG_PUB.ADD('x_trx_limit ==> '|| x_trx_limit );
858: OE_DEBUG_PUB.ADD('x_overall_limit ==> '|| x_overall_limit );
859: OE_DEBUG_PUB.ADD('x_return_status ==> '|| x_return_status );
860: OE_DEBUG_PUB.ADD('OUT GET_party_limit ');
861: END IF;
854:
855: OE_DEBUG_PUB.ADD('x_credit_limit_party ==> '|| x_credit_limit_entity_id );
856: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
857: OE_DEBUG_PUB.ADD('x_trx_limit ==> '|| x_trx_limit );
858: OE_DEBUG_PUB.ADD('x_overall_limit ==> '|| x_overall_limit );
859: OE_DEBUG_PUB.ADD('x_return_status ==> '|| x_return_status );
860: OE_DEBUG_PUB.ADD('OUT GET_party_limit ');
861: END IF;
862:
855: OE_DEBUG_PUB.ADD('x_credit_limit_party ==> '|| x_credit_limit_entity_id );
856: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
857: OE_DEBUG_PUB.ADD('x_trx_limit ==> '|| x_trx_limit );
858: OE_DEBUG_PUB.ADD('x_overall_limit ==> '|| x_overall_limit );
859: OE_DEBUG_PUB.ADD('x_return_status ==> '|| x_return_status );
860: OE_DEBUG_PUB.ADD('OUT GET_party_limit ');
861: END IF;
862:
863: EXCEPTION
856: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
857: OE_DEBUG_PUB.ADD('x_trx_limit ==> '|| x_trx_limit );
858: OE_DEBUG_PUB.ADD('x_overall_limit ==> '|| x_overall_limit );
859: OE_DEBUG_PUB.ADD('x_return_status ==> '|| x_return_status );
860: OE_DEBUG_PUB.ADD('OUT GET_party_limit ');
861: END IF;
862:
863: EXCEPTION
864: WHEN OTHERS THEN
933: l_global_exposure_flag VARCHAR2(1);
934: BEGIN
935: IF G_debug_flag = 'Y'
936: THEN
937: OE_DEBUG_PUB.ADD('IN Get_global_exposure_flag ');
938: END IF;
939:
940: l_global_exposure_flag := 'N' ;
941:
947: INTO cust_global_exposure_rec ;
948:
949: IF G_debug_flag = 'Y'
950: THEN
951: OE_DEBUG_PUB.ADD( ' credit_usage_rule_set_id => '
952: || cust_global_exposure_rec.credit_usage_rule_set_id);
953: OE_DEBUG_PUB.ADD( ' global_exposure_flag => '
954: || cust_global_exposure_rec.global_exposure_flag);
955: END IF;
949: IF G_debug_flag = 'Y'
950: THEN
951: OE_DEBUG_PUB.ADD( ' credit_usage_rule_set_id => '
952: || cust_global_exposure_rec.credit_usage_rule_set_id);
953: OE_DEBUG_PUB.ADD( ' global_exposure_flag => '
954: || cust_global_exposure_rec.global_exposure_flag);
955: END IF;
956:
957: l_global_exposure_flag :=
962: l_global_exposure_flag := 'N' ;
963:
964: IF G_debug_flag = 'Y'
965: THEN
966: OE_DEBUG_PUB.ADD( ' cust_global_exposure_csr NO FOUND ');
967: END IF;
968:
969: END IF;
970:
978:
979: IF G_debug_flag = 'Y'
980: THEN
981:
982: OE_DEBUG_PUB.ADD( ' credit_usage_rule_set_id => '
983: || dflt_global_exposure_rec.credit_usage_rule_set_id);
984: OE_DEBUG_PUB.ADD( ' global_exposure_flag => '
985: || dflt_global_exposure_rec.global_exposure_flag);
986: END IF;
980: THEN
981:
982: OE_DEBUG_PUB.ADD( ' credit_usage_rule_set_id => '
983: || dflt_global_exposure_rec.credit_usage_rule_set_id);
984: OE_DEBUG_PUB.ADD( ' global_exposure_flag => '
985: || dflt_global_exposure_rec.global_exposure_flag);
986: END IF;
987:
988: l_global_exposure_flag :=
990:
991: IF dflt_global_exposure_csr%NOTFOUND
992: THEN
993: l_global_exposure_flag := 'N' ;
994: OE_DEBUG_PUB.ADD( ' dflt_global_exposure_csr NO FOUND ');
995: END IF;
996:
997: CLOSE dflt_global_exposure_csr ;
998: END IF;
998: END IF;
999:
1000: IF G_debug_flag = 'Y'
1001: THEN
1002: OE_DEBUG_PUB.ADD('Return out NOCOPY from Get_global_exposure_flag with '
1003: || l_global_exposure_flag );
1004: END IF;
1005:
1006: RETURN(l_global_exposure_flag);
1035: l_global_exposure_flag VARCHAR2(1) := 'N' ;
1036: BEGIN
1037: IF G_debug_flag = 'Y'
1038: THEN
1039: OE_DEBUG_PUB.ADD('IN OEXUCRCB: Get_global_exposure_ruleset');
1040: END IF;
1041:
1042: BEGIN
1043: SELECT
1057: END ;
1058:
1059: IF G_debug_flag = 'Y'
1060: THEN
1061: OE_DEBUG_PUB.ADD('OUT OEXUCRCB: Get_global_exposure_ruleset');
1062: END IF;
1063:
1064: RETURN( NVL(l_global_exposure_flag,'N') );
1065:
1088:
1089: BEGIN
1090: IF G_debug_flag = 'Y'
1091: THEN
1092: OE_DEBUG_PUB.ADD('OEXUCRCB: In Get_GL_currency ');
1093: END IF;
1094:
1095: BEGIN
1096: l_sob_id := FND_PROFILE.VALUE('GL_SET_OF_BKS_ID') ;
1096: l_sob_id := FND_PROFILE.VALUE('GL_SET_OF_BKS_ID') ;
1097:
1098: IF G_debug_flag = 'Y'
1099: THEN
1100: OE_DEBUG_PUB.ADD('l_sob_id = '|| l_sob_id );
1101: OE_DEBUG_PUB.ADD('GET SOB currency ');
1102: END IF;
1103:
1104: SELECT
1097:
1098: IF G_debug_flag = 'Y'
1099: THEN
1100: OE_DEBUG_PUB.ADD('l_sob_id = '|| l_sob_id );
1101: OE_DEBUG_PUB.ADD('GET SOB currency ');
1102: END IF;
1103:
1104: SELECT
1105: currency_code
1110: WHERE set_of_books_id = l_sob_id ;
1111:
1112: IF G_debug_flag = 'Y'
1113: THEN
1114: OE_DEBUG_PUB.ADD('l_gl_currency = '|| l_gl_currency );
1115: END IF;
1116:
1117: EXCEPTION
1118: WHEN NO_DATA_FOUND
1117: EXCEPTION
1118: WHEN NO_DATA_FOUND
1119: THEN
1120: l_gl_currency := NULL ;
1121: OE_DEBUG_PUB.ADD('EXCEPTION: NO_DATA_FOUND ');
1122: l_gl_currency := NULL ;
1123: WHEN TOO_MANY_ROWS
1124: THEN
1125: l_gl_currency := NULL ;
1122: l_gl_currency := NULL ;
1123: WHEN TOO_MANY_ROWS
1124: THEN
1125: l_gl_currency := NULL ;
1126: OE_DEBUG_PUB.ADD('EXCEPTION: TOO_MANY_ROWS');
1127: l_gl_currency := NULL ;
1128: END ;
1129:
1130: IF G_debug_flag = 'Y'
1128: END ;
1129:
1130: IF G_debug_flag = 'Y'
1131: THEN
1132: OE_DEBUG_PUB.ADD('OEXUCRCB: Out NOCOPY Get_GL_currency ');
1133: END IF;
1134: RETURN(l_GL_currency);
1135: EXCEPTION
1136: WHEN OTHERS THEN
1159: l_category_set_id NUMBER;
1160:
1161: BEGIN
1162:
1163: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_category_set_id ' );
1164: BEGIN
1165:
1166: SELECT
1167: category_set_id
1177: l_category_set_id := NULL;
1178:
1179: END ;
1180:
1181: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_category_set_id '|| l_category_set_id );
1182: RETURN l_category_set_id ;
1183:
1184:
1185: EXCEPTION
1314:
1315: BEGIN
1316: IF G_debug_flag = 'Y'
1317: THEN
1318: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_Single_Customer_Limit ' );
1319: END IF;
1320:
1321: OPEN cust_single_limit_csr;
1322: FETCH cust_single_limit_csr
1342: CLOSE cust_single_limit_csr;
1343:
1344: IF G_debug_flag = 'Y'
1345: THEN
1346: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_Single_Customer_Limit ' );
1347: END IF;
1348:
1349: EXCEPTION
1350: WHEN OTHERS THEN
1411:
1412: BEGIN
1413: IF G_debug_flag = 'Y'
1414: THEN
1415: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_Single_site_Limit ' );
1416: END IF;
1417:
1418: OPEN site_single_limit_csr ;
1419: FETCH site_single_limit_csr
1439: CLOSE site_single_limit_csr ;
1440:
1441: IF G_debug_flag = 'Y'
1442: THEN
1443: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_Single_site_Limit ' );
1444: END IF;
1445:
1446:
1447: EXCEPTION
1562:
1563: BEGIN
1564: IF G_debug_flag = 'Y'
1565: THEN
1566: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_multi_Customer_Limit ' );
1567: END IF;
1568:
1569: x_credit_usage_rule_set_id := NULL ;
1570:
1629:
1630: END IF;
1631: IF G_debug_flag = 'Y'
1632: THEN
1633: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_multi_Customer_Limit ' );
1634: END IF;
1635:
1636:
1637: EXCEPTION
1738:
1739: BEGIN
1740: IF G_debug_flag = 'Y'
1741: THEN
1742: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_multi_site_Limit ' );
1743: END IF;
1744:
1745: -- The process will first try to find the limit avoiding the
1746: -- Include all factor.
1794:
1795: END IF;
1796: IF G_debug_flag = 'Y'
1797: THEN
1798: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_multi_site_Limit ' );
1799: END IF ;
1800:
1801:
1802: EXCEPTION
1862:
1863: BEGIN
1864: IF G_debug_flag = 'Y'
1865: THEN
1866: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_single_default_Limit ' );
1867: END IF ;
1868:
1869: OPEN dflt_single_limit_csr ;
1870: FETCH dflt_single_limit_csr
1887: CLOSE dflt_single_limit_csr ;
1888:
1889: IF G_debug_flag = 'Y'
1890: THEN
1891: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_single_default_Limit ' );
1892: END IF ;
1893:
1894: EXCEPTION
1895: WHEN OTHERS THEN
2017: BEGIN
2018:
2019: IF G_debug_flag = 'Y'
2020: THEN
2021: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_multi_default_Limit ' );
2022: END IF;
2023:
2024: x_credit_usage_rule_set_id := NULL ;
2025: x_global_exposure_flag := 'N' ;
2075: END IF;
2076:
2077: IF G_debug_flag = 'Y'
2078: THEN
2079: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_multi_default_Limit ' );
2080: END IF;
2081:
2082: EXCEPTION
2083: WHEN OTHERS THEN
2133:
2134:
2135: BEGIN
2136:
2137: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_single_Item_Limit ' );
2138:
2139: OPEN single_Item_limit_csr;
2140: FETCH single_Item_limit_csr
2141: INTO l_single_Item_limit_csr_VAL ;
2153: x_return_status := 'Y' ;
2154:
2155: END IF;
2156:
2157: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_single_Item_Limit ' );
2158:
2159: EXCEPTION
2160: WHEN OTHERS THEN
2161: G_DBG_MSG := SUBSTR(sqlerrm,1,200);
2245: l_multi_Item_limit_incl_VAL multi_Item_limit_incl_csr%ROWTYPE ;
2246:
2247: BEGIN
2248:
2249: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_multi_item_Limit ' );
2250:
2251: x_return_status := 'N' ;
2252:
2253: OPEN multi_Item_limit_no_incl_csr ;
2283: x_return_status := 'Y' ;
2284:
2285: END IF;
2286:
2287: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_multi_item_Limit ' );
2288:
2289: EXCEPTION
2290: WHEN OTHERS THEN
2291: G_DBG_MSG := SUBSTR(sqlerrm,1,200);
2446: l_ctg_count NUMBER := 0;
2447: l_line_value NUMBER := 0 ;
2448: BEGIN
2449:
2450: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_Item_Limit ');
2451:
2452: ----------------------------------------------------
2453: -- Two PL/SQL tables are used to maintain the limits
2454: -- associated with the Item categories.
2466:
2467: g_category_set_id := NULL;
2468: l_ctg_profile_exist := 'N' ;
2469:
2470: OE_DEBUG_PUB.ADD('Initial g_category_set_id ' || g_category_set_id );
2471: -- OE_DEBUG_PUB.ADD(' G_profile ' || G_profile );
2472:
2473: -- For each Line ID/Item, the limits are selected
2474: -- using the Category.
2467: g_category_set_id := NULL;
2468: l_ctg_profile_exist := 'N' ;
2469:
2470: OE_DEBUG_PUB.ADD('Initial g_category_set_id ' || g_category_set_id );
2471: -- OE_DEBUG_PUB.ADD(' G_profile ' || G_profile );
2472:
2473: -- For each Line ID/Item, the limits are selected
2474: -- using the Category.
2475: -- The similar selection pattern in coordination
2479:
2480: -- First verify if any category exist with a profile
2481:
2482: BEGIN
2483: OE_DEBUG_PUB.ADD(' Cusror select to check CTG profile exist ');
2484:
2485: OPEN C_SELECT_CTG_CREDIT_PROFILE ;
2486:
2487: FETCH C_SELECT_CTG_CREDIT_PROFILE
2489:
2490: IF C_SELECT_CTG_CREDIT_PROFILE%NOTFOUND
2491: THEN
2492: l_ctg_profile_exist := 'N' ;
2493: OE_DEBUG_PUB.ADD(' Category profile do NOT exist ');
2494: ELSE
2495: l_credit_profile_id :=
2496: l_SELECT_CTG_CREDIT_PROFILE.credit_profile_id ;
2497: l_ctg_profile_exist := 'Y' ;
2494: ELSE
2495: l_credit_profile_id :=
2496: l_SELECT_CTG_CREDIT_PROFILE.credit_profile_id ;
2497: l_ctg_profile_exist := 'Y' ;
2498: OE_DEBUG_PUB.ADD(' Category profile exist ');
2499: END IF;
2500: CLOSE C_SELECT_CTG_CREDIT_PROFILE ;
2501:
2502: END ;
2501:
2502: END ;
2503:
2504:
2505: OE_DEBUG_PUB.ADD(' Out NOCOPY of CTG profile check cursor ');
2506: OE_DEBUG_PUB.ADD(' l_ctg_profile_exist = '|| l_ctg_profile_exist );
2507:
2508: IF l_ctg_profile_exist = 'Y'
2509: THEN
2502: END ;
2503:
2504:
2505: OE_DEBUG_PUB.ADD(' Out NOCOPY of CTG profile check cursor ');
2506: OE_DEBUG_PUB.ADD(' l_ctg_profile_exist = '|| l_ctg_profile_exist );
2507:
2508: IF l_ctg_profile_exist = 'Y'
2509: THEN
2510:
2507:
2508: IF l_ctg_profile_exist = 'Y'
2509: THEN
2510:
2511: OE_DEBUG_PUB.ADD(' Continue Item category credit get limit ');
2512:
2513: g_category_set_id := GET_category_set_id ;
2514: OE_DEBUG_PUB.ADD('g_category_set_id ' || g_category_set_id );
2515:
2510:
2511: OE_DEBUG_PUB.ADD(' Continue Item category credit get limit ');
2512:
2513: g_category_set_id := GET_category_set_id ;
2514: OE_DEBUG_PUB.ADD('g_category_set_id ' || g_category_set_id );
2515:
2516: FOR L_SELECT_LINES_VAL IN C_SELECT_LINES_CSR
2517: LOOP
2518: l_line_id := L_SELECT_LINES_VAL.line_id ;
2540: l_ordered_quantity := NULL;
2541:
2542: END LOOP; -- CTG cursor loop
2543:
2544: OE_DEBUG_PUB.ADD(' Out NOCOPY of category cursor ');
2545:
2546: OE_DEBUG_PUB.ADD(' l_tmp_lines_tbl.COUNT ' || l_tmp_lines_tbl.COUNT );
2547: -- Now the TMP table needs to be scannned and relocated into
2548: -- the main table with category check.
2542: END LOOP; -- CTG cursor loop
2543:
2544: OE_DEBUG_PUB.ADD(' Out NOCOPY of category cursor ');
2545:
2546: OE_DEBUG_PUB.ADD(' l_tmp_lines_tbl.COUNT ' || l_tmp_lines_tbl.COUNT );
2547: -- Now the TMP table needs to be scannned and relocated into
2548: -- the main table with category check.
2549:
2550: FOR I IN 1.. l_tmp_lines_tbl.COUNT
2558: l_ctg_count := NVL(l_ctg_count,0) + 1 ;
2559:
2560: -- Always get multi first and get single if no usages found
2561:
2562: OE_DEBUG_PUB.ADD(' GET_multi_Item_Limit ');
2563:
2564: GET_multi_Item_Limit
2565: ( p_category_id => l_tmp_lines_tbl(i).item_category_id
2566: , p_trx_curr_code => p_trx_curr_code
2570: );
2571:
2572: IF l_return_status = 'N'
2573: THEN
2574: OE_DEBUG_PUB.ADD(' Call GET_single_Item_Limit ');
2575: GET_single_Item_Limit
2576: ( p_category_id => l_tmp_lines_tbl(i).item_category_id
2577: , p_trx_curr_code => p_trx_curr_code
2578: , x_limit_curr_code => l_limit_curr_code
2584: -- END IF;
2585:
2586: IF G_debug_flag = 'Y'
2587: THEN
2588: OE_DEBUG_PUB.ADD(' after getting the limits ');
2589: OE_DEBUG_PUB.ADD(' l_return_status '|| l_return_status);
2590: OE_DEBUG_PUB.ADD(' l_trx_limit '|| l_trx_limit);
2591: OE_DEBUG_PUB.ADD(' l_limit_curr_code '|| l_limit_curr_code );
2592: OE_DEBUG_PUB.ADD(' p_trx_curr_code '|| p_trx_curr_code );
2585:
2586: IF G_debug_flag = 'Y'
2587: THEN
2588: OE_DEBUG_PUB.ADD(' after getting the limits ');
2589: OE_DEBUG_PUB.ADD(' l_return_status '|| l_return_status);
2590: OE_DEBUG_PUB.ADD(' l_trx_limit '|| l_trx_limit);
2591: OE_DEBUG_PUB.ADD(' l_limit_curr_code '|| l_limit_curr_code );
2592: OE_DEBUG_PUB.ADD(' p_trx_curr_code '|| p_trx_curr_code );
2593: END IF;
2586: IF G_debug_flag = 'Y'
2587: THEN
2588: OE_DEBUG_PUB.ADD(' after getting the limits ');
2589: OE_DEBUG_PUB.ADD(' l_return_status '|| l_return_status);
2590: OE_DEBUG_PUB.ADD(' l_trx_limit '|| l_trx_limit);
2591: OE_DEBUG_PUB.ADD(' l_limit_curr_code '|| l_limit_curr_code );
2592: OE_DEBUG_PUB.ADD(' p_trx_curr_code '|| p_trx_curr_code );
2593: END IF;
2594:
2587: THEN
2588: OE_DEBUG_PUB.ADD(' after getting the limits ');
2589: OE_DEBUG_PUB.ADD(' l_return_status '|| l_return_status);
2590: OE_DEBUG_PUB.ADD(' l_trx_limit '|| l_trx_limit);
2591: OE_DEBUG_PUB.ADD(' l_limit_curr_code '|| l_limit_curr_code );
2592: OE_DEBUG_PUB.ADD(' p_trx_curr_code '|| p_trx_curr_code );
2593: END IF;
2594:
2595: l_curr_category_id := l_tmp_lines_tbl(i).item_category_id ;
2588: OE_DEBUG_PUB.ADD(' after getting the limits ');
2589: OE_DEBUG_PUB.ADD(' l_return_status '|| l_return_status);
2590: OE_DEBUG_PUB.ADD(' l_trx_limit '|| l_trx_limit);
2591: OE_DEBUG_PUB.ADD(' l_limit_curr_code '|| l_limit_curr_code );
2592: OE_DEBUG_PUB.ADD(' p_trx_curr_code '|| p_trx_curr_code );
2593: END IF;
2594:
2595: l_curr_category_id := l_tmp_lines_tbl(i).item_category_id ;
2596:
2681: END LOOP;
2682:
2683: ELSE
2684:
2685: OE_DEBUG_PUB.ADD(' No CTG credit profile found - No Item CC required ');
2686:
2687:
2688: END IF; -- Profile check IF
2689:
2687:
2688: END IF; -- Profile check IF
2689:
2690:
2691: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_Item_Limit ');
2692:
2693:
2694: EXCEPTION
2695: WHEN OTHERS THEN
2736: BEGIN
2737:
2738: IF G_debug_flag = 'Y'
2739: THEN
2740: OE_DEBUG_PUB.ADD('OEXUCRCB: IN Get_Multi_Limit ');
2741: END IF;
2742:
2743: x_global_exposure_flag := 'N' ;
2744:
2759: );
2760:
2761: IF G_debug_flag = 'Y'
2762: THEN
2763: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_customer_Limit '|| l_return_status );
2764: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2765: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2766: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2767: END IF;
2760:
2761: IF G_debug_flag = 'Y'
2762: THEN
2763: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_customer_Limit '|| l_return_status );
2764: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2765: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2766: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2767: END IF;
2768:
2761: IF G_debug_flag = 'Y'
2762: THEN
2763: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_customer_Limit '|| l_return_status );
2764: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2765: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2766: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2767: END IF;
2768:
2769: x_cust_cc_flag := l_return_status; --bug 4582292
2762: THEN
2763: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_customer_Limit '|| l_return_status );
2764: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2765: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2766: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2767: END IF;
2768:
2769: x_cust_cc_flag := l_return_status; --bug 4582292
2770:
2778: l_return_status := 'N' ;
2779: x_limit_curr_code := NULL ;
2780: x_global_exposure_flag := 'N' ;
2781:
2782: OE_DEBUG_PUB.ADD(' Limits NULL for customer MULTI ');
2783:
2784: END IF;
2785:
2786: IF NVL(l_return_status, 'N') = 'N' --bug 5071518
2789: l_credit_usage_rule_set_id := NULL ;
2790:
2791: IF G_debug_flag = 'Y'
2792: THEN
2793: OE_DEBUG_PUB.ADD(' Calling GET_Single_Customer_Limit ');
2794: END IF;
2795:
2796: GET_Single_Customer_Limit
2797: ( p_entity_id => p_entity_id
2803: );
2804:
2805: IF G_debug_flag = 'Y'
2806: THEN
2807: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_Single_Customer_Limit '
2808: || l_return_status );
2809: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '
2810: || x_limit_curr_code );
2811: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2805: IF G_debug_flag = 'Y'
2806: THEN
2807: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_Single_Customer_Limit '
2808: || l_return_status );
2809: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '
2810: || x_limit_curr_code );
2811: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2812: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2813: END IF;
2807: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_Single_Customer_Limit '
2808: || l_return_status );
2809: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '
2810: || x_limit_curr_code );
2811: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2812: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2813: END IF;
2814:
2815: x_cust_cc_flag := l_return_status; -- bug 4582292
2808: || l_return_status );
2809: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '
2810: || x_limit_curr_code );
2811: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2812: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2813: END IF;
2814:
2815: x_cust_cc_flag := l_return_status; -- bug 4582292
2816:
2832: x_global_exposure_flag := 'N' ;
2833:
2834: IF G_debug_flag = 'Y'
2835: THEN
2836: OE_DEBUG_PUB.ADD(' Call GET_multi_site_Limit ');
2837: END IF;
2838:
2839: GET_multi_site_Limit
2840: ( p_entity_id => p_entity_id
2846: );
2847:
2848: IF G_debug_flag = 'Y'
2849: THEN
2850: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_site_Limit '|| l_return_status );
2851: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2852: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2853: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2854: END IF;
2847:
2848: IF G_debug_flag = 'Y'
2849: THEN
2850: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_site_Limit '|| l_return_status );
2851: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2852: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2853: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2854: END IF;
2855:
2848: IF G_debug_flag = 'Y'
2849: THEN
2850: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_site_Limit '|| l_return_status );
2851: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2852: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2853: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2854: END IF;
2855:
2856: x_site_cc_flag := l_return_status; --bug 4212981
2849: THEN
2850: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_site_Limit '|| l_return_status );
2851: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2852: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2853: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2854: END IF;
2855:
2856: x_site_cc_flag := l_return_status; --bug 4212981
2857: -- BUG 4158439
2863: THEN
2864: l_return_status := 'N' ;
2865: x_limit_curr_code := NULL ;
2866:
2867: OE_DEBUG_PUB.ADD(' Limits NULL for site MULTI ');
2868:
2869: END IF;
2870:
2871: IF NVL(l_return_status, 'N') = 'N' --bug 4582292
2880: );
2881:
2882: IF G_debug_flag = 'Y'
2883: THEN
2884: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_Single_Site_Limit '|| l_return_status );
2885: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2886: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2887: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2888: END IF;
2881:
2882: IF G_debug_flag = 'Y'
2883: THEN
2884: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_Single_Site_Limit '|| l_return_status );
2885: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2886: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2887: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2888: END IF;
2889:
2882: IF G_debug_flag = 'Y'
2883: THEN
2884: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_Single_Site_Limit '|| l_return_status );
2885: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2886: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2887: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2888: END IF;
2889:
2890: x_site_cc_flag := l_return_status; --bug 4212981
2883: THEN
2884: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_Single_Site_Limit '|| l_return_status );
2885: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2886: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2887: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2888: END IF;
2889:
2890: x_site_cc_flag := l_return_status; --bug 4212981
2891:
2895: AND x_overall_limit is NULL
2896: THEN
2897: l_return_status := 'N' ;
2898: x_limit_curr_code := NULL ;
2899: OE_DEBUG_PUB.ADD(' Limits NULL for site SINGLE ');
2900: END IF;
2901:
2902: END IF;
2903:
2906: END IF;
2907:
2908: IF G_debug_flag = 'Y'
2909: THEN
2910: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_Multi_Limit ');
2911: END IF;
2912:
2913: EXCEPTION
2914: WHEN OTHERS THEN
2951: BEGIN
2952:
2953: IF G_debug_flag = 'Y'
2954: THEN
2955: OE_DEBUG_PUB.ADD('OEXUCRCB: IN Get_SIngle_Limit ');
2956: END IF;
2957:
2958: IF P_entity_Type = 'CUSTOMER'
2959: THEN
2999: END IF;
3000:
3001: IF G_debug_flag = 'Y'
3002: THEN
3003: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_SIngle_Limit ');
3004: END IF;
3005:
3006:
3007: EXCEPTION
3284: BEGIN
3285:
3286: IF G_debug_flag = 'Y'
3287: THEN
3288: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_usages ');
3289: OE_DEBUG_PUB.ADD(' p_entity_type = '|| p_entity_type );
3290: OE_DEBUG_PUB.ADD(' p_entity_id = '|| p_entity_id );
3291: OE_DEBUG_PUB.ADD(' p_limit_curr_code = '|| p_limit_curr_code );
3292: OE_DEBUG_PUB.ADD(' p_suppress_unused_usages_flag = '||
3285:
3286: IF G_debug_flag = 'Y'
3287: THEN
3288: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_usages ');
3289: OE_DEBUG_PUB.ADD(' p_entity_type = '|| p_entity_type );
3290: OE_DEBUG_PUB.ADD(' p_entity_id = '|| p_entity_id );
3291: OE_DEBUG_PUB.ADD(' p_limit_curr_code = '|| p_limit_curr_code );
3292: OE_DEBUG_PUB.ADD(' p_suppress_unused_usages_flag = '||
3293: p_suppress_unused_usages_flag );
3286: IF G_debug_flag = 'Y'
3287: THEN
3288: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_usages ');
3289: OE_DEBUG_PUB.ADD(' p_entity_type = '|| p_entity_type );
3290: OE_DEBUG_PUB.ADD(' p_entity_id = '|| p_entity_id );
3291: OE_DEBUG_PUB.ADD(' p_limit_curr_code = '|| p_limit_curr_code );
3292: OE_DEBUG_PUB.ADD(' p_suppress_unused_usages_flag = '||
3293: p_suppress_unused_usages_flag );
3294: OE_DEBUG_PUB.ADD(' p_global_exposure_flag '||
3287: THEN
3288: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_usages ');
3289: OE_DEBUG_PUB.ADD(' p_entity_type = '|| p_entity_type );
3290: OE_DEBUG_PUB.ADD(' p_entity_id = '|| p_entity_id );
3291: OE_DEBUG_PUB.ADD(' p_limit_curr_code = '|| p_limit_curr_code );
3292: OE_DEBUG_PUB.ADD(' p_suppress_unused_usages_flag = '||
3293: p_suppress_unused_usages_flag );
3294: OE_DEBUG_PUB.ADD(' p_global_exposure_flag '||
3295: p_global_exposure_flag );
3288: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_usages ');
3289: OE_DEBUG_PUB.ADD(' p_entity_type = '|| p_entity_type );
3290: OE_DEBUG_PUB.ADD(' p_entity_id = '|| p_entity_id );
3291: OE_DEBUG_PUB.ADD(' p_limit_curr_code = '|| p_limit_curr_code );
3292: OE_DEBUG_PUB.ADD(' p_suppress_unused_usages_flag = '||
3293: p_suppress_unused_usages_flag );
3294: OE_DEBUG_PUB.ADD(' p_global_exposure_flag '||
3295: p_global_exposure_flag );
3296:
3290: OE_DEBUG_PUB.ADD(' p_entity_id = '|| p_entity_id );
3291: OE_DEBUG_PUB.ADD(' p_limit_curr_code = '|| p_limit_curr_code );
3292: OE_DEBUG_PUB.ADD(' p_suppress_unused_usages_flag = '||
3293: p_suppress_unused_usages_flag );
3294: OE_DEBUG_PUB.ADD(' p_global_exposure_flag '||
3295: p_global_exposure_flag );
3296:
3297: OE_DEBUG_PUB.ADD(' ---------------------------------------------- ');
3298:
3293: p_suppress_unused_usages_flag );
3294: OE_DEBUG_PUB.ADD(' p_global_exposure_flag '||
3295: p_global_exposure_flag );
3296:
3297: OE_DEBUG_PUB.ADD(' ---------------------------------------------- ');
3298:
3299: END IF;
3300:
3301: OE_CREDIT_CHECK_UTIL.G_excl_curr_list := NULL ;
3302: l_exclude_flag := 'N' ;
3303:
3304: IF G_debug_flag = 'Y'
3305: THEN
3306: OE_DEBUG_PUB.ADD(' Credit Limit found ');
3307: END IF;
3308:
3309: ---------------------------------------------------
3310: -- First get all usage rule sets for this limit --
3313: IF NVL(p_default_limit_flag,'N') = 'N'
3314: THEN
3315: IF G_debug_flag = 'Y'
3316: THEN
3317: OE_DEBUG_PUB.ADD(' Into Default limit = N ');
3318: END IF;
3319:
3320: IF p_entity_type = 'PARTY'
3321: THEN
3455: -- Default limit = 'Y'
3456:
3457: IF G_debug_flag = 'Y'
3458: THEN
3459: OE_DEBUG_PUB.ADD(' Into Default limit = Y ');
3460: END IF ;
3461:
3462: FOR rule_set_rec IN dflt_rule_set_csr
3463: LOOP
3508: OE_CREDIT_CHECK_UTIL.G_excl_curr_list := l_excl_curr_list ;
3509:
3510: IF G_debug_flag = 'Y'
3511: THEN
3512: OE_DEBUG_PUB.ADD(' G_excl_curr_list => '||
3513: OE_CREDIT_CHECK_UTIL.G_excl_curr_list );
3514: END IF;
3515: -----------------------------------------------------------
3516: ---------------------------------------------
3601: THEN
3602:
3603: IF G_debug_flag = 'Y'
3604: THEN
3605: OE_DEBUG_PUB.ADD(' into suppress = Y ');
3606: END IF;
3607:
3608: -------------------------------------------------------
3609: -- include only the intersection of incl currencies --
3646:
3647: ELSE
3648: IF G_debug_flag = 'Y'
3649: THEN
3650: OE_DEBUG_PUB.ADD(' into suppress = N ');
3651: END IF;
3652:
3653: -------------------------------------------------------
3654: -- first include all incl currencies (minus excl) --
3751: x_include_all_flag := l_include_all_flag;
3752:
3753: IF G_debug_flag = 'Y'
3754: THEN
3755: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT get_usages ');
3756: END IF;
3757:
3758: EXCEPTION
3759: WHEN OTHERS THEN
3784: IS
3785: BEGIN
3786: IF G_debug_flag = 'Y'
3787: THEN
3788: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_System_parameters');
3789: END IF;
3790:
3791: -- BEGIN /* MOAC CREDIT CHECK CHANGE */
3792: -- Start Sys Param Change
3816: EXCEPTION
3817: WHEN NO_DATA_FOUND
3818: THEN
3819: x_system_parameter_rec := NULL ;
3820: OE_DEBUG_PUB.ADD(' Exception Get Syetem parameters');
3821: END ;
3822: */
3823: /* End MOAC CREDIT CHECK CHANGE */
3824:
3823: /* End MOAC CREDIT CHECK CHANGE */
3824:
3825: IF G_debug_flag = 'Y'
3826: THEN
3827: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT GET_System_parameters');
3828: END IF;
3829: EXCEPTION
3830: WHEN OTHERS THEN
3831: G_DBG_MSG := SUBSTR(sqlerrm,1,200);
3855: IS
3856: BEGIN
3857: IF G_debug_flag = 'Y'
3858: THEN
3859: OE_DEBUG_PUB.ADD('OEXUCRCB: In Get_Credit_Check_Rule_ID ');
3860: OE_DEBUG_PUB.ADD('p_order_type_id = '|| p_order_type_id );
3861: OE_DEBUG_PUB.ADD('p_calling_action = '|| p_calling_action );
3862: END IF;
3863:
3856: BEGIN
3857: IF G_debug_flag = 'Y'
3858: THEN
3859: OE_DEBUG_PUB.ADD('OEXUCRCB: In Get_Credit_Check_Rule_ID ');
3860: OE_DEBUG_PUB.ADD('p_order_type_id = '|| p_order_type_id );
3861: OE_DEBUG_PUB.ADD('p_calling_action = '|| p_calling_action );
3862: END IF;
3863:
3864: x_credit_rule_id := NULL ;
3857: IF G_debug_flag = 'Y'
3858: THEN
3859: OE_DEBUG_PUB.ADD('OEXUCRCB: In Get_Credit_Check_Rule_ID ');
3860: OE_DEBUG_PUB.ADD('p_order_type_id = '|| p_order_type_id );
3861: OE_DEBUG_PUB.ADD('p_calling_action = '|| p_calling_action );
3862: END IF;
3863:
3864: x_credit_rule_id := NULL ;
3865:
3866: IF p_calling_action in ('BOOKING','BOOKING_INLINE','AUTO','UPDATE')
3867: THEN
3868: IF G_debug_flag = 'Y'
3869: THEN
3870: OE_DEBUG_PUB.ADD('Selecting the order entry credit check rule');
3871: END IF;
3872: /*7194250
3873: SELECT ENTRY_CREDIT_CHECK_RULE_ID
3874: INTO x_credit_rule_id
3889: ELSIF p_calling_action = 'SHIPPING'
3890: THEN
3891: IF G_debug_flag = 'Y'
3892: THEN
3893: OE_DEBUG_PUB.Add('Selecting the shipping credit check rule');
3894: END IF;
3895:
3896: /*7194250
3897: SELECT SHIPPING_CREDIT_CHECK_RULE_ID
3914: THEN
3915:
3916: IF G_debug_flag = 'Y'
3917: THEN
3918: OE_DEBUG_PUB.Add('Selecting the picking credit check rule');
3919: END IF;
3920:
3921: /*7194250
3922: SELECT PICKING_CREDIT_CHECK_RULE_ID
3938: ELSIF p_calling_action = 'PACKING'
3939: THEN
3940: IF G_debug_flag = 'Y'
3941: THEN
3942: OE_DEBUG_PUB.Add('Selecting the packing credit check rule');
3943: END IF;
3944:
3945: /*7194250
3946: SELECT PACKING_CREDIT_CHECK_RULE_ID
3961:
3962: IF G_debug_flag = 'Y'
3963: THEN
3964:
3965: OE_DEBUG_PUB.ADD('OEXUCRCB: Credit Check Rule ID: '
3966: ||TO_CHAR(x_credit_rule_id) );
3967:
3968: OE_DEBUG_PUB.ADD('OEXUCRCB: Out NOCOPY Get_Credit_Check_Rule_ID');
3969: END IF;
3964:
3965: OE_DEBUG_PUB.ADD('OEXUCRCB: Credit Check Rule ID: '
3966: ||TO_CHAR(x_credit_rule_id) );
3967:
3968: OE_DEBUG_PUB.ADD('OEXUCRCB: Out NOCOPY Get_Credit_Check_Rule_ID');
3969: END IF;
3970:
3971:
3972: EXCEPTION
3972: EXCEPTION
3973: WHEN NO_DATA_FOUND
3974: THEN
3975: x_credit_rule_id := NULL ;
3976: OE_DEBUG_PUB.ADD('EXCEPTION:No credit check rule found');
3977: WHEN OTHERS THEN
3978: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3979: OE_MSG_PUB.Add_Exc_Msg
3980: ( G_PKG_NAME
4004:
4005: BEGIN
4006: IF G_debug_flag = 'Y'
4007: THEN
4008: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_credit_check_level ');
4009: OE_DEBUG_PUB.ADD('p_calling_action = '|| p_calling_action );
4010: OE_DEBUG_PUB.ADD('p_order_type_id = '|| p_order_type_id );
4011: END IF;
4012:
4005: BEGIN
4006: IF G_debug_flag = 'Y'
4007: THEN
4008: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_credit_check_level ');
4009: OE_DEBUG_PUB.ADD('p_calling_action = '|| p_calling_action );
4010: OE_DEBUG_PUB.ADD('p_order_type_id = '|| p_order_type_id );
4011: END IF;
4012:
4013: l_level := NULL ;
4006: IF G_debug_flag = 'Y'
4007: THEN
4008: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_credit_check_level ');
4009: OE_DEBUG_PUB.ADD('p_calling_action = '|| p_calling_action );
4010: OE_DEBUG_PUB.ADD('p_order_type_id = '|| p_order_type_id );
4011: END IF;
4012:
4013: l_level := NULL ;
4014: l_credit_check_rule_id := NULL ;
4014: l_credit_check_rule_id := NULL ;
4015:
4016: IF G_debug_flag = 'Y'
4017: THEN
4018: OE_DEBUG_PUB.ADD(' Calling Get_Credit_Check_Rule_ID ');
4019: END IF;
4020:
4021: OE_Credit_CHECK_UTIL.Get_Credit_Check_Rule_ID
4022: ( p_calling_action => p_calling_action
4025: );
4026:
4027: IF G_debug_flag = 'Y'
4028: THEN
4029: OE_DEBUG_PUB.ADD(' out NOCOPY l_credit_check_rule_id = '||
4030: l_credit_check_rule_id );
4031: END IF;
4032:
4033: IF l_credit_check_rule_id is NULL
4033: IF l_credit_check_rule_id is NULL
4034: THEN
4035: IF G_debug_flag = 'Y'
4036: THEN
4037: OE_DEBUG_PUB.ADD(' No credit check attached ');
4038: END IF;
4039:
4040: l_level := NULL ;
4041:
4042: ELSE
4043:
4044: IF G_debug_flag = 'Y'
4045: THEN
4046: OE_DEBUG_PUB.ADD('OEXUCRCB: Call GET_credit_check_rule ');
4047: END IF;
4048:
4049: OE_CREDIT_CHECK_UTIL.GET_credit_check_rule
4050: ( p_credit_check_rule_id => l_credit_check_rule_id
4052: );
4053:
4054: IF G_debug_flag = 'Y'
4055: THEN
4056: OE_DEBUG_PUB.ADD('credit_check_level_code = '||
4057: l_credit_check_rule_rec.credit_check_level_code );
4058: END IF;
4059:
4060: l_level := l_credit_check_rule_rec.credit_check_level_code ;
4062: END IF;
4063:
4064: IF G_debug_flag = 'Y'
4065: THEN
4066: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_credit_check_level ' || l_level);
4067: END IF;
4068:
4069: RETURN( l_level ) ;
4070:
4099: BEGIN
4100:
4101: IF G_debug_flag = 'Y'
4102: THEN
4103: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_credit_check_rule ' );
4104: END IF;
4105:
4106: BEGIN
4107: IF G_debug_flag = 'Y'
4105:
4106: BEGIN
4107: IF G_debug_flag = 'Y'
4108: THEN
4109: OE_DEBUG_PUB.ADD(' Select for ID ' || p_credit_check_rule_id );
4110: END IF;
4111:
4112:
4113: SELECT
4191: EXCEPTION
4192: WHEN NO_DATA_FOUND
4193: THEN
4194: x_credit_check_rules_rec := NULL ;
4195: OE_DEBUG_PUB.ADD(' Get credit check rule No_Data_Found exception ' );
4196:
4197: WHEN TOO_MANY_ROWS
4198: THEN
4199: x_credit_check_rules_rec := NULL ;
4196:
4197: WHEN TOO_MANY_ROWS
4198: THEN
4199: x_credit_check_rules_rec := NULL ;
4200: OE_DEBUG_PUB.ADD(' Get credit check rule TOO_MANY_ROWS ');
4201: END ;
4202:
4203: IF G_debug_flag = 'Y'
4204: THEN
4201: END ;
4202:
4203: IF G_debug_flag = 'Y'
4204: THEN
4205: OE_DEBUG_PUB.ADD(' conversion_type = '
4206: || x_credit_check_rules_rec.conversion_type );
4207: END IF;
4208:
4209: BEGIN
4212: THEN
4213: BEGIN
4214: IF G_debug_flag = 'Y'
4215: THEN
4216: OE_DEBUG_PUB.ADD(' Select user_conversion_type ');
4217: END IF;
4218:
4219: SELECT
4220: user_conversion_type
4229: WHEN NO_DATA_FOUND
4230: THEN
4231: x_credit_check_rules_rec.user_conversion_type :=
4232: x_credit_check_rules_rec.conversion_type ;
4233: OE_DEBUG_PUB.ADD(' conversion type NO_DATA_FOUND ');
4234:
4235: WHEN TOO_MANY_ROWS
4236: THEN
4237: x_credit_check_rules_rec.user_conversion_type :=
4235: WHEN TOO_MANY_ROWS
4236: THEN
4237: x_credit_check_rules_rec.user_conversion_type :=
4238: x_credit_check_rules_rec.conversion_type ;
4239: OE_DEBUG_PUB.ADD(' conversion type TOO_MANY_ROWS ');
4240: END ;
4241:
4242: ELSE
4243: IF G_debug_flag = 'Y'
4241:
4242: ELSE
4243: IF G_debug_flag = 'Y'
4244: THEN
4245: OE_DEBUG_PUB.ADD(' No need for selct user_conversion_type ');
4246: END IF;
4247:
4248: x_credit_check_rules_rec.user_conversion_type :=
4249: x_credit_check_rules_rec.conversion_type ;
4253: END ;
4254:
4255: IF G_debug_flag = 'Y'
4256: THEN
4257: OE_DEBUG_PUB.ADD('Conversion type = '
4258: || x_credit_check_rules_rec.conversion_type );
4259:
4260: OE_DEBUG_PUB.ADD('User Conversion type = '
4261: || x_credit_check_rules_rec.user_conversion_type );
4256: THEN
4257: OE_DEBUG_PUB.ADD('Conversion type = '
4258: || x_credit_check_rules_rec.conversion_type );
4259:
4260: OE_DEBUG_PUB.ADD('User Conversion type = '
4261: || x_credit_check_rules_rec.user_conversion_type );
4262:
4263: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_credit_check_rule ' );
4264: END IF;
4259:
4260: OE_DEBUG_PUB.ADD('User Conversion type = '
4261: || x_credit_check_rules_rec.user_conversion_type );
4262:
4263: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_credit_check_rule ' );
4264: END IF;
4265:
4266: EXCEPTION
4267: WHEN OTHERS THEN
4293: l_precision NUMBER;
4294: l_ext_precision NUMBER;
4295: l_min_acct_unit NUMBER;
4296: --
4297: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4298: --
4299: BEGIN
4300:
4301: IF l_debug_level > 0 THEN
4298: --
4299: BEGIN
4300:
4301: IF l_debug_level > 0 THEN
4302: oe_debug_pub.add( 'OEXUCRCB: IN ROUNDED_AMOUNT ( ) WITH AMOUNT : ' ||P_UNROUNDED_AMOUNT , 5 ) ;
4303: END IF;
4304: FND_CURRENCY.GET_INFO(Currency_Code => p_currency_code,
4305: precision => l_precision,
4306: ext_precision => l_ext_precision,
4311: ELSE
4312: x_rounded_amount := ROUND(p_unrounded_amount/l_min_acct_unit)*l_min_acct_unit;
4313: END IF;
4314: IF l_debug_level > 0 THEN
4315: oe_debug_pub.add( 'OEXUCRCB: OUT ROUNDED AMOUNT ( ) WITH AMOUNT : '||X_ROUNDED_AMOUNT , 5 ) ;
4316: END IF;
4317: END Rounded_Amount;
4318:
4319: -----------------------------------------------------------------------------
4352: BEGIN
4353:
4354: IF G_debug_flag = 'Y'
4355: THEN
4356: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_transaction_amount for '
4357: || p_header_id );
4358: END IF;
4359:
4360: -- For line level, if it is customer level credit check
4370: IF p_credit_check_rule_rec.credit_check_level_code = 'LINE'
4371: THEN
4372: IF G_debug_flag = 'Y'
4373: THEN
4374: OE_DEBUG_PUB.ADD('In Line level ');
4375: END IF;
4376:
4377: IF p_customer_id IS NOT NULL -- line level CC
4378: THEN
4382: ---returns are not included
4383:
4384: IF G_debug_flag = 'Y'
4385: THEN
4386: OE_DEBUG_PUB.ADD('Line - Customer level select,no returns ');
4387: END IF;
4388:
4389: SELECT SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
4390: NVL(l.tax_value,0), 0 )
4421: ---returns are included
4422:
4423: IF G_debug_flag = 'Y'
4424: THEN
4425: OE_DEBUG_PUB.ADD('Line - Customer level select,with returns ');
4426: END IF;
4427:
4428: SELECT
4429: SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
4462: END IF; ----end for checking if returns are included
4463:
4464: IF G_debug_flag = 'Y'
4465: THEN
4466: OE_DEBUG_PUB.ADD('Check for Committment profile ');
4467: END IF;
4468:
4469: IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
4470: BEGIN
4469: IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
4470: BEGIN
4471: IF G_debug_flag = 'Y'
4472: THEN
4473: OE_DEBUG_PUB.ADD('Customer level commitment select ');
4474: END IF;
4475:
4476: SELECT NVL(SUM(P.commitment_applied_amount), 0)
4477: INTO l_commitment
4505:
4506: EXCEPTION
4507: WHEN NO_DATA_FOUND
4508: THEN
4509: OE_DEBUG_PUB.ADD(' NO commitment-1 found ');
4510: l_commitment := 0 ;
4511:
4512: WHEN TOO_MANY_ROWS
4513: THEN
4510: l_commitment := 0 ;
4511:
4512: WHEN TOO_MANY_ROWS
4513: THEN
4514: OE_DEBUG_PUB.ADD(' Too many rows exception NO commtment-1');
4515: l_commitment := 0 ;
4516: END ;
4517: END IF; --- commitment
4518:
4525: THEN
4526: ---returns are not included
4527: IF G_debug_flag = 'Y'
4528: THEN
4529: OE_DEBUG_PUB.ADD(' Process trx freights - Line level customer without returns ');
4530: END IF;
4531:
4532: BEGIN
4533: SELECT
4572:
4573: EXCEPTION
4574: WHEN NO_DATA_FOUND
4575: THEN
4576: OE_DEBUG_PUB.ADD(' NO Freights found ');
4577: l_freights := 0 ;
4578:
4579: END ;
4580: ELSE
4580: ELSE
4581: ---returns are included
4582: IF G_debug_flag = 'Y'
4583: THEN
4584: OE_DEBUG_PUB.ADD(' Process trx freights - Line level customer,with returns ');
4585: END IF;
4586:
4587: BEGIN
4588: SELECT
4625: (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
4626: EXCEPTION
4627: WHEN NO_DATA_FOUND
4628: THEN
4629: OE_DEBUG_PUB.ADD(' NO Freights found ');
4630: l_freights := 0 ;
4631:
4632: END ;
4633:
4644: ---returns are not included
4645:
4646: IF G_debug_flag = 'Y'
4647: THEN
4648: OE_DEBUG_PUB.ADD('Line - Site level select ');
4649: END IF;
4650:
4651: SELECT SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
4652: NVL(l.tax_value,0), 0 )
4679: ---returns are included
4680:
4681: IF G_debug_flag = 'Y'
4682: THEN
4683: OE_DEBUG_PUB.ADD('Line - Site level select,including returns ');
4684: END IF;
4685:
4686: SELECT
4687: SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
4714: END IF; ---end of checking for returns
4715:
4716: IF G_debug_flag = 'Y'
4717: THEN
4718: OE_DEBUG_PUB.ADD('Check for Committment ');
4719: END IF;
4720:
4721: IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
4722: BEGIN
4750:
4751: EXCEPTION
4752: WHEN NO_DATA_FOUND
4753: THEN
4754: OE_DEBUG_PUB.ADD(' NO commitment-1 found ');
4755: l_commitment := 0 ;
4756:
4757: WHEN TOO_MANY_ROWS
4758: THEN
4755: l_commitment := 0 ;
4756:
4757: WHEN TOO_MANY_ROWS
4758: THEN
4759: OE_DEBUG_PUB.ADD(' Too many rows excepn NO commitment-1 found ');
4760: l_commitment := 0 ;
4761: END ;
4762: END IF; --- commitment
4763:
4771: ---returns are not included
4772:
4773: IF G_debug_flag = 'Y'
4774: THEN
4775: OE_DEBUG_PUB.ADD(' Process trx freights - Line level site,no returns ');
4776: END IF;
4777:
4778: BEGIN
4779: SELECT
4813: (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
4814: EXCEPTION
4815: WHEN NO_DATA_FOUND
4816: THEN
4817: OE_DEBUG_PUB.ADD(' NO Freights found ');
4818: l_freights := 0 ;
4819:
4820: END ;
4821:
4823: ---returns are included
4824:
4825: IF G_debug_flag = 'Y'
4826: THEN
4827: OE_DEBUG_PUB.ADD(' Process trx freights - Line level site, with returns ');
4828: END IF;
4829:
4830: BEGIN
4831: SELECT
4865:
4866: EXCEPTION
4867: WHEN NO_DATA_FOUND
4868: THEN
4869: OE_DEBUG_PUB.ADD(' NO Freights found ');
4870: l_freights := 0 ;
4871:
4872: END ;
4873:
4885: ---returns are not included
4886:
4887: IF G_debug_flag = 'Y'
4888: THEN
4889: OE_DEBUG_PUB.ADD('In Order header level select ');
4890: END IF;
4891:
4892: SELECT SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
4893: NVL(l.tax_value,0), 0 )
4919: ---returns are included
4920:
4921: IF G_debug_flag = 'Y'
4922: THEN
4923: OE_DEBUG_PUB.ADD('In Order header level select, with returns ');
4924: END IF;
4925:
4926: SELECT
4927: SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
4954: END IF; ---end for checking if returns are included
4955:
4956: IF G_debug_flag = 'Y'
4957: THEN
4958: OE_DEBUG_PUB.ADD('Check for Committment ');
4959: END IF ;
4960:
4961: IF OE_Commitment_PVT.Do_Commitment_Sequencing
4962: THEN
4962: THEN
4963: BEGIN
4964: IF G_debug_flag = 'Y'
4965: THEN
4966: OE_DEBUG_PUB.ADD('In Order header commitment');
4967: END IF;
4968:
4969: SELECT NVL(SUM(P.commitment_applied_amount), 0)
4970: INTO l_commitment
4995:
4996: EXCEPTION
4997: WHEN NO_DATA_FOUND
4998: THEN
4999: OE_DEBUG_PUB.ADD(' NO commitment-1 found ');
5000: l_commitment := 0 ;
5001:
5002: WHEN TOO_MANY_ROWS
5003: THEN
5000: l_commitment := 0 ;
5001:
5002: WHEN TOO_MANY_ROWS
5003: THEN
5004: OE_DEBUG_PUB.ADD(' Too many rows excepn NO commitment-1 found ');
5005: l_commitment := 0 ;
5006: END ;
5007: END IF; -- commitment
5008:
5013: -- check the check to determine if credit check is required
5014: --
5015: IF G_MULTIPLE_PAYMENTS_ENABLED THEN
5016: IF G_debug_flag = 'Y' THEN
5017: OE_DEBUG_PUB.ADD('In Order header prepayment');
5018: END IF;
5019:
5020: SELECT SUM(P.prepaid_amount)
5021: INTO l_prepayment
5034: ---returns are not included
5035:
5036: IF G_debug_flag = 'Y'
5037: THEN
5038: OE_DEBUG_PUB.ADD(' Process trx freights - order level ');
5039: END IF;
5040:
5041: BEGIN
5042:
5104: ---returns are included
5105:
5106: IF G_debug_flag = 'Y'
5107: THEN
5108: OE_DEBUG_PUB.ADD(' Process trx freights - order level,with returns ');
5109: END IF;
5110:
5111: BEGIN
5112: SELECT
5177: END IF; -- Hdr or line
5178:
5179: IF G_debug_flag = 'Y'
5180: THEN
5181: OE_DEBUG_PUB.ADD(' l_order_value = '|| l_order_value );
5182: OE_DEBUG_PUB.ADD(' l_commitment = '|| l_commitment );
5183: OE_DEBUG_PUB.ADD(' l_freights = ' || l_freights );
5184: END IF;
5185:
5178:
5179: IF G_debug_flag = 'Y'
5180: THEN
5181: OE_DEBUG_PUB.ADD(' l_order_value = '|| l_order_value );
5182: OE_DEBUG_PUB.ADD(' l_commitment = '|| l_commitment );
5183: OE_DEBUG_PUB.ADD(' l_freights = ' || l_freights );
5184: END IF;
5185:
5186:
5179: IF G_debug_flag = 'Y'
5180: THEN
5181: OE_DEBUG_PUB.ADD(' l_order_value = '|| l_order_value );
5182: OE_DEBUG_PUB.ADD(' l_commitment = '|| l_commitment );
5183: OE_DEBUG_PUB.ADD(' l_freights = ' || l_freights );
5184: END IF;
5185:
5186:
5187: -- convert amount
5187: -- convert amount
5188: BEGIN
5189: IF G_debug_flag = 'Y'
5190: THEN
5191: OE_DEBUG_PUB.Add(' GL_CURRENCY = '||
5192: OE_Credit_Engine_GRP.GL_currency );
5193: END IF;
5194:
5195: l_order_value := NVL(l_order_value,0)
5197: - NVL(l_prepayment,0)
5198: + NVL(l_freights,0);
5199: IF G_debug_flag = 'Y'
5200: THEN
5201: OE_DEBUG_PUB.ADD(' Total order amount = '|| l_order_value );
5202: END IF;
5203:
5204: l_limit_order_value :=
5205: OE_CREDIT_CHECK_UTIL.CONVERT_CURRENCY_AMOUNT
5223: OE_CREDIT_CHECK_UTIL.g_current_order_value := x_amount ;
5224:
5225: IF G_debug_flag = 'Y'
5226: THEN
5227: OE_DEBUG_PUB.Add(' g_current_order_value = '||
5228: OE_CREDIT_CHECK_UTIL.g_current_order_value );
5229: OE_DEBUG_PUB.Add(' Final trx check order amount = '|| x_amount,1 );
5230: OE_DEBUG_PUB.Add(' ====================================');
5231: END IF;
5225: IF G_debug_flag = 'Y'
5226: THEN
5227: OE_DEBUG_PUB.Add(' g_current_order_value = '||
5228: OE_CREDIT_CHECK_UTIL.g_current_order_value );
5229: OE_DEBUG_PUB.Add(' Final trx check order amount = '|| x_amount,1 );
5230: OE_DEBUG_PUB.Add(' ====================================');
5231: END IF;
5232:
5233: IF G_debug_flag = 'Y'
5226: THEN
5227: OE_DEBUG_PUB.Add(' g_current_order_value = '||
5228: OE_CREDIT_CHECK_UTIL.g_current_order_value );
5229: OE_DEBUG_PUB.Add(' Final trx check order amount = '|| x_amount,1 );
5230: OE_DEBUG_PUB.Add(' ====================================');
5231: END IF;
5232:
5233: IF G_debug_flag = 'Y'
5234: THEN
5231: END IF;
5232:
5233: IF G_debug_flag = 'Y'
5234: THEN
5235: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_transaction_amount ');
5236: END IF;
5237:
5238: EXCEPTION
5239: WHEN GL_CURRENCY_API.NO_RATE
5237:
5238: EXCEPTION
5239: WHEN GL_CURRENCY_API.NO_RATE
5240: THEN
5241: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE ');
5242: x_conversion_status(1).usage_curr_code := p_transaction_curr_code;
5243: fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
5244: OE_DEBUG_PUB.ADD('Exception table added ');
5245:
5240: THEN
5241: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE ');
5242: x_conversion_status(1).usage_curr_code := p_transaction_curr_code;
5243: fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
5244: OE_DEBUG_PUB.ADD('Exception table added ');
5245:
5246: WHEN NO_DATA_FOUND
5247: THEN
5248: x_amount := 0 ;
5245:
5246: WHEN NO_DATA_FOUND
5247: THEN
5248: x_amount := 0 ;
5249: OE_DEBUG_PUB.ADD('EXCEPTION: NO_DATA_FOUND ');
5250:
5251: WHEN TOO_MANY_ROWS
5252: THEN
5253: x_amount := 0 ;
5250:
5251: WHEN TOO_MANY_ROWS
5252: THEN
5253: x_amount := 0 ;
5254: OE_DEBUG_PUB.ADD('EXCEPTION: TOO_MANY_ROWS ');
5255:
5256: WHEN OTHERS THEN
5257: G_DBG_MSG := SUBSTR(sqlerrm,1,200);
5258: OE_DEBUG_PUB.ADD('EXCEPTION = '||
5254: OE_DEBUG_PUB.ADD('EXCEPTION: TOO_MANY_ROWS ');
5255:
5256: WHEN OTHERS THEN
5257: G_DBG_MSG := SUBSTR(sqlerrm,1,200);
5258: OE_DEBUG_PUB.ADD('EXCEPTION = '||
5259: SUBSTR(sqlerrm,1,200) );
5260: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5261:
5262: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5297: l_dummy VARCHAR2(30);
5298: BEGIN
5299: IF G_debug_flag = 'Y'
5300: THEN
5301: OE_DEBUG_PUB.ADD('OEXUCRCB: In Get_Past_Due_Invoice');
5302: END IF;
5303:
5304: -- Initialize return status to success
5305: x_return_status := FND_API.G_RET_STS_SUCCESS;
5309: -- Bug2408466
5310: -- REmove the OLD code with DECODES
5311: IF G_debug_flag = 'Y'
5312: THEN
5313: OE_DEBUG_PUB.ADD('maximum_days_past_due ==> '||
5314: p_credit_check_rule_rec.maximum_days_past_due,1 );
5315:
5316: OE_DEBUG_PUB.ADD('p_credit_level ==> '|| p_credit_level );
5317: OE_DEBUG_PUB.ADD('p_include_all_flag ==> '|| p_include_all_flag);
5312: THEN
5313: OE_DEBUG_PUB.ADD('maximum_days_past_due ==> '||
5314: p_credit_check_rule_rec.maximum_days_past_due,1 );
5315:
5316: OE_DEBUG_PUB.ADD('p_credit_level ==> '|| p_credit_level );
5317: OE_DEBUG_PUB.ADD('p_include_all_flag ==> '|| p_include_all_flag);
5318: OE_DEBUG_PUB.ADD('p_global_exposure_flag ==> '||
5319: p_global_exposure_flag );
5320: END IF;
5313: OE_DEBUG_PUB.ADD('maximum_days_past_due ==> '||
5314: p_credit_check_rule_rec.maximum_days_past_due,1 );
5315:
5316: OE_DEBUG_PUB.ADD('p_credit_level ==> '|| p_credit_level );
5317: OE_DEBUG_PUB.ADD('p_include_all_flag ==> '|| p_include_all_flag);
5318: OE_DEBUG_PUB.ADD('p_global_exposure_flag ==> '||
5319: p_global_exposure_flag );
5320: END IF;
5321:
5314: p_credit_check_rule_rec.maximum_days_past_due,1 );
5315:
5316: OE_DEBUG_PUB.ADD('p_credit_level ==> '|| p_credit_level );
5317: OE_DEBUG_PUB.ADD('p_include_all_flag ==> '|| p_include_all_flag);
5318: OE_DEBUG_PUB.ADD('p_global_exposure_flag ==> '||
5319: p_global_exposure_flag );
5320: END IF;
5321:
5322: IF NVL(p_global_exposure_flag,'N') = 'N'
5323: THEN
5324: BEGIN
5325: IF G_debug_flag = 'Y'
5326: THEN
5327: OE_DEBUG_PUB.Add(' Into NOT Global ');
5328: END IF;
5329: --bug 4174163
5330: --IF NVL(p_credit_check_rule_rec.maximum_days_past_due,0) > 0
5331: IF p_credit_check_rule_rec.maximum_days_past_due is not null
5335: THEN
5336: BEGIN
5337: IF G_debug_flag = 'Y'
5338: THEN
5339: OE_DEBUG_PUB.Add(' Into CUSTOMER ');
5340: END IF;
5341:
5342: FOR i IN 1..p_usage_curr.count
5343: LOOP
5342: FOR i IN 1..p_usage_curr.count
5343: LOOP
5344: IF G_debug_flag = 'Y'
5345: THEN
5346: OE_DEBUG_PUB.Add('Currency code = '
5347: || p_usage_curr(i).usage_curr_code );
5348: END IF;
5349: BEGIN
5350: SELECT 'Y'
5364: x_exist_flag := 'Y' ;
5365:
5366: IF G_debug_flag = 'Y'
5367: THEN
5368: OE_DEBUG_PUB.Add('Invoices found ' );
5369: END IF;
5370:
5371: EXIT;
5372:
5371: EXIT;
5372:
5373: EXCEPTION
5374: WHEN NO_DATA_FOUND THEN
5375: OE_DEBUG_PUB.Add(' No Invoices Past due found ' );
5376:
5377: x_exist_flag := 'N' ;
5378:
5379: WHEN TOO_MANY_ROWS THEN
5376:
5377: x_exist_flag := 'N' ;
5378:
5379: WHEN TOO_MANY_ROWS THEN
5380: OE_DEBUG_PUB.Add('Invoices found ' );
5381: x_exist_flag := 'Y' ;
5382: EXIT;
5383: END;
5384: END LOOP;
5387: ELSE
5388: BEGIN
5389: IF G_debug_flag = 'Y'
5390: THEN
5391: OE_DEBUG_PUB.Add(' Into SITE ');
5392: END IF;
5393:
5394: FOR i IN 1..p_usage_curr.count
5395: LOOP
5394: FOR i IN 1..p_usage_curr.count
5395: LOOP
5396: IF G_debug_flag = 'Y'
5397: THEN
5398: OE_DEBUG_PUB.Add(' Currency code = '
5399: || p_usage_curr(i).usage_curr_code );
5400: END IF;
5401: BEGIN
5402: SELECT 'Y'
5415: x_exist_flag := 'Y' ;
5416:
5417: IF G_debug_flag = 'Y'
5418: THEN
5419: OE_DEBUG_PUB.Add('Invoices found ' );
5420: END IF;
5421:
5422: EXIT;
5423:
5422: EXIT;
5423:
5424: EXCEPTION
5425: WHEN NO_DATA_FOUND THEN
5426: OE_DEBUG_PUB.Add('No Invoices Past due found in this curr' );
5427: x_exist_flag := 'N' ;
5428: WHEN TOO_MANY_ROWS THEN
5429: OE_DEBUG_PUB.Add('Invoices found ' );
5430: x_exist_flag := 'Y' ;
5425: WHEN NO_DATA_FOUND THEN
5426: OE_DEBUG_PUB.Add('No Invoices Past due found in this curr' );
5427: x_exist_flag := 'N' ;
5428: WHEN TOO_MANY_ROWS THEN
5429: OE_DEBUG_PUB.Add('Invoices found ' );
5430: x_exist_flag := 'Y' ;
5431: EXIT;
5432: END;
5433: END LOOP;
5436:
5437: ELSE
5438: IF G_debug_flag = 'Y'
5439: THEN
5440: OE_DEBUG_PUB.Add(' No need to check ' );
5441: END IF;
5442: x_exist_flag := 'N' ;
5443: END IF;
5444: END;
5447: THEN
5448: BEGIN
5449: IF G_debug_flag = 'Y'
5450: THEN
5451: OE_DEBUG_PUB.Add(' Into GLOBAL checking ');
5452: END IF;
5453: --bug 4174163
5454: --IF NVL(p_credit_check_rule_rec.maximum_days_past_due,0) > 0
5455: IF p_credit_check_rule_rec.maximum_days_past_due is not null
5459: THEN
5460: BEGIN
5461: IF G_debug_flag = 'Y'
5462: THEN
5463: OE_DEBUG_PUB.Add(' Into CUSTOMER ');
5464: END IF;
5465:
5466: FOR i IN 1..p_usage_curr.count
5467: LOOP
5466: FOR i IN 1..p_usage_curr.count
5467: LOOP
5468: IF G_debug_flag = 'Y'
5469: THEN
5470: OE_DEBUG_PUB.Add('Currency code = '
5471: || p_usage_curr(i).usage_curr_code );
5472: END IF;
5473:
5474: BEGIN
5489: x_exist_flag := 'Y' ;
5490:
5491: IF G_debug_flag = 'Y'
5492: THEN
5493: OE_DEBUG_PUB.Add('Invoices found ' );
5494: end if;
5495:
5496: EXIT;
5497:
5496: EXIT;
5497:
5498: EXCEPTION
5499: WHEN NO_DATA_FOUND THEN
5500: OE_DEBUG_PUB.Add(' No Invoices Past due found ' );
5501:
5502: x_exist_flag := 'N' ;
5503:
5504: WHEN TOO_MANY_ROWS THEN
5501:
5502: x_exist_flag := 'N' ;
5503:
5504: WHEN TOO_MANY_ROWS THEN
5505: OE_DEBUG_PUB.Add('Invoices found ' );
5506: x_exist_flag := 'Y' ;
5507: EXIT;
5508: END;
5509: END LOOP;
5509: END LOOP;
5510: END ;
5511: END IF;
5512: ELSE
5513: OE_DEBUG_PUB.Add(' No need to check ' );
5514: x_exist_flag := 'N' ;
5515: END IF;
5516:
5517:
5526: and p_credit_check_rule_rec.maximum_days_past_due >= 0
5527: THEN
5528: IF G_debug_flag = 'Y'
5529: THEN
5530: oe_debug_pub.add('calling get_invoices_over_duedate ');
5531: END IF;
5532:
5533: OE_CREDIT_EXPOSURE_PVT.get_invoices_over_duedate
5534: ( p_customer_id => p_customer_id
5554: END IF ;
5555:
5556: IF G_debug_flag = 'Y'
5557: THEN
5558: OE_DEBUG_PUB.Add(' x_exist_flag = '|| x_exist_flag );
5559: OE_DEBUG_PUB.ADD('OEXUCRCB: Out NOCOPY Get_Past_Due_Invoice');
5560: END IF;
5561:
5562: EXCEPTION
5555:
5556: IF G_debug_flag = 'Y'
5557: THEN
5558: OE_DEBUG_PUB.Add(' x_exist_flag = '|| x_exist_flag );
5559: OE_DEBUG_PUB.ADD('OEXUCRCB: Out NOCOPY Get_Past_Due_Invoice');
5560: END IF;
5561:
5562: EXCEPTION
5563: WHEN OTHERS THEN
8541:
8542: BEGIN
8543: IF G_debug_flag = 'Y'
8544: THEN
8545: OE_DEBUG_PUB.ADD('OEXUCRCB: IN Get_order_exposure',1);
8546: END IF;
8547:
8548: x_return_status := FND_API.G_RET_STS_SUCCESS;
8549:
8548: x_return_status := FND_API.G_RET_STS_SUCCESS;
8549:
8550: IF G_debug_flag = 'Y'
8551: THEN
8552: OE_DEBUG_PUB.Add('exposure Input parameters ');
8553: OE_DEBUG_PUB.Add(' ');
8554: OE_DEBUG_PUB.Add('--------------------------------------------');
8555: OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
8556: OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
8549:
8550: IF G_debug_flag = 'Y'
8551: THEN
8552: OE_DEBUG_PUB.Add('exposure Input parameters ');
8553: OE_DEBUG_PUB.Add(' ');
8554: OE_DEBUG_PUB.Add('--------------------------------------------');
8555: OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
8556: OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
8557: OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id);
8550: IF G_debug_flag = 'Y'
8551: THEN
8552: OE_DEBUG_PUB.Add('exposure Input parameters ');
8553: OE_DEBUG_PUB.Add(' ');
8554: OE_DEBUG_PUB.Add('--------------------------------------------');
8555: OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
8556: OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
8557: OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id);
8558: OE_DEBUG_PUB.Add('p_credit_check_rule_id = '
8551: THEN
8552: OE_DEBUG_PUB.Add('exposure Input parameters ');
8553: OE_DEBUG_PUB.Add(' ');
8554: OE_DEBUG_PUB.Add('--------------------------------------------');
8555: OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
8556: OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
8557: OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id);
8558: OE_DEBUG_PUB.Add('p_credit_check_rule_id = '
8559: || p_credit_check_rule_rec.credit_check_rule_id
8552: OE_DEBUG_PUB.Add('exposure Input parameters ');
8553: OE_DEBUG_PUB.Add(' ');
8554: OE_DEBUG_PUB.Add('--------------------------------------------');
8555: OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
8556: OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
8557: OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id);
8558: OE_DEBUG_PUB.Add('p_credit_check_rule_id = '
8559: || p_credit_check_rule_rec.credit_check_rule_id
8560: );
8553: OE_DEBUG_PUB.Add(' ');
8554: OE_DEBUG_PUB.Add('--------------------------------------------');
8555: OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
8556: OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
8557: OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id);
8558: OE_DEBUG_PUB.Add('p_credit_check_rule_id = '
8559: || p_credit_check_rule_rec.credit_check_rule_id
8560: );
8561:
8554: OE_DEBUG_PUB.Add('--------------------------------------------');
8555: OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
8556: OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
8557: OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id);
8558: OE_DEBUG_PUB.Add('p_credit_check_rule_id = '
8559: || p_credit_check_rule_rec.credit_check_rule_id
8560: );
8561:
8562: OE_DEBUG_PUB.Add('Conversion type = '
8558: OE_DEBUG_PUB.Add('p_credit_check_rule_id = '
8559: || p_credit_check_rule_rec.credit_check_rule_id
8560: );
8561:
8562: OE_DEBUG_PUB.Add('Conversion type = '
8563: || p_credit_check_rule_rec.conversion_type
8564: );
8565:
8566: OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level, 1);
8562: OE_DEBUG_PUB.Add('Conversion type = '
8563: || p_credit_check_rule_rec.conversion_type
8564: );
8565:
8566: OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level, 1);
8567: OE_DEBUG_PUB.Add('p_limit_curr_code = '|| p_limit_curr_code);
8568: OE_DEBUG_PUB.Add('p_include_all_flag = '|| p_include_all_flag);
8569: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8570: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8563: || p_credit_check_rule_rec.conversion_type
8564: );
8565:
8566: OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level, 1);
8567: OE_DEBUG_PUB.Add('p_limit_curr_code = '|| p_limit_curr_code);
8568: OE_DEBUG_PUB.Add('p_include_all_flag = '|| p_include_all_flag);
8569: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8570: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8571: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8564: );
8565:
8566: OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level, 1);
8567: OE_DEBUG_PUB.Add('p_limit_curr_code = '|| p_limit_curr_code);
8568: OE_DEBUG_PUB.Add('p_include_all_flag = '|| p_include_all_flag);
8569: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8570: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8571: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8572: OE_DEBUG_PUB.Add(' ');
8565:
8566: OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level, 1);
8567: OE_DEBUG_PUB.Add('p_limit_curr_code = '|| p_limit_curr_code);
8568: OE_DEBUG_PUB.Add('p_include_all_flag = '|| p_include_all_flag);
8569: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8570: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8571: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8572: OE_DEBUG_PUB.Add(' ');
8573: OE_DEBUG_PUB.Add('Local Variables');
8566: OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level, 1);
8567: OE_DEBUG_PUB.Add('p_limit_curr_code = '|| p_limit_curr_code);
8568: OE_DEBUG_PUB.Add('p_include_all_flag = '|| p_include_all_flag);
8569: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8570: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8571: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8572: OE_DEBUG_PUB.Add(' ');
8573: OE_DEBUG_PUB.Add('Local Variables');
8574: OE_DEBUG_PUB.Add('l_need_exposure_details = '||l_need_exposure_details);
8567: OE_DEBUG_PUB.Add('p_limit_curr_code = '|| p_limit_curr_code);
8568: OE_DEBUG_PUB.Add('p_include_all_flag = '|| p_include_all_flag);
8569: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8570: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8571: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8572: OE_DEBUG_PUB.Add(' ');
8573: OE_DEBUG_PUB.Add('Local Variables');
8574: OE_DEBUG_PUB.Add('l_need_exposure_details = '||l_need_exposure_details);
8575: OE_DEBUG_PUB.Add(' ');
8568: OE_DEBUG_PUB.Add('p_include_all_flag = '|| p_include_all_flag);
8569: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8570: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8571: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8572: OE_DEBUG_PUB.Add(' ');
8573: OE_DEBUG_PUB.Add('Local Variables');
8574: OE_DEBUG_PUB.Add('l_need_exposure_details = '||l_need_exposure_details);
8575: OE_DEBUG_PUB.Add(' ');
8576:
8569: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8570: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8571: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8572: OE_DEBUG_PUB.Add(' ');
8573: OE_DEBUG_PUB.Add('Local Variables');
8574: OE_DEBUG_PUB.Add('l_need_exposure_details = '||l_need_exposure_details);
8575: OE_DEBUG_PUB.Add(' ');
8576:
8577: --------------------------------------------------------------------
8570: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8571: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8572: OE_DEBUG_PUB.Add(' ');
8573: OE_DEBUG_PUB.Add('Local Variables');
8574: OE_DEBUG_PUB.Add('l_need_exposure_details = '||l_need_exposure_details);
8575: OE_DEBUG_PUB.Add(' ');
8576:
8577: --------------------------------------------------------------------
8578: --------- Assign default values: credit check rules ----------------
8571: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8572: OE_DEBUG_PUB.Add(' ');
8573: OE_DEBUG_PUB.Add('Local Variables');
8574: OE_DEBUG_PUB.Add('l_need_exposure_details = '||l_need_exposure_details);
8575: OE_DEBUG_PUB.Add(' ');
8576:
8577: --------------------------------------------------------------------
8578: --------- Assign default values: credit check rules ----------------
8579: --------------------------------------------------------------------
8577: --------------------------------------------------------------------
8578: --------- Assign default values: credit check rules ----------------
8579: --------------------------------------------------------------------
8580:
8581: OE_DEBUG_PUB.Add(' Assign Default Values: Credit rules ');
8582: END IF;
8583:
8584: l_open_ar_days := p_credit_check_rule_rec.open_ar_days ;
8585: l_uninvoiced_orders_flag :=
8605: NVL(p_credit_check_rule_rec.include_returns_flag,'N');
8606:
8607: IF G_debug_flag = 'Y'
8608: THEN
8609: OE_DEBUG_PUB.Add(' Credit rule setup values ');
8610: OE_DEBUG_PUB.Add(' ');
8611: OE_DEBUG_PUB.Add(' -----------------------------------------------');
8612: OE_DEBUG_PUB.Add(' l_open_ar_days = '|| l_open_ar_days );
8613: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8606:
8607: IF G_debug_flag = 'Y'
8608: THEN
8609: OE_DEBUG_PUB.Add(' Credit rule setup values ');
8610: OE_DEBUG_PUB.Add(' ');
8611: OE_DEBUG_PUB.Add(' -----------------------------------------------');
8612: OE_DEBUG_PUB.Add(' l_open_ar_days = '|| l_open_ar_days );
8613: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8614: l_uninvoiced_orders_flag );
8607: IF G_debug_flag = 'Y'
8608: THEN
8609: OE_DEBUG_PUB.Add(' Credit rule setup values ');
8610: OE_DEBUG_PUB.Add(' ');
8611: OE_DEBUG_PUB.Add(' -----------------------------------------------');
8612: OE_DEBUG_PUB.Add(' l_open_ar_days = '|| l_open_ar_days );
8613: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8614: l_uninvoiced_orders_flag );
8615: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8608: THEN
8609: OE_DEBUG_PUB.Add(' Credit rule setup values ');
8610: OE_DEBUG_PUB.Add(' ');
8611: OE_DEBUG_PUB.Add(' -----------------------------------------------');
8612: OE_DEBUG_PUB.Add(' l_open_ar_days = '|| l_open_ar_days );
8613: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8614: l_uninvoiced_orders_flag );
8615: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8616: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8609: OE_DEBUG_PUB.Add(' Credit rule setup values ');
8610: OE_DEBUG_PUB.Add(' ');
8611: OE_DEBUG_PUB.Add(' -----------------------------------------------');
8612: OE_DEBUG_PUB.Add(' l_open_ar_days = '|| l_open_ar_days );
8613: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8614: l_uninvoiced_orders_flag );
8615: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8616: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8617: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8611: OE_DEBUG_PUB.Add(' -----------------------------------------------');
8612: OE_DEBUG_PUB.Add(' l_open_ar_days = '|| l_open_ar_days );
8613: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8614: l_uninvoiced_orders_flag );
8615: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8616: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8617: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8618: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8619: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8612: OE_DEBUG_PUB.Add(' l_open_ar_days = '|| l_open_ar_days );
8613: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8614: l_uninvoiced_orders_flag );
8615: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8616: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8617: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8618: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8619: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8620: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8613: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8614: l_uninvoiced_orders_flag );
8615: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8616: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8617: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8618: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8619: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8620: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8621: OE_DEBUG_PUB.Add(' l_open_ar_balance_flag = '|| l_open_ar_balance_flag );
8614: l_uninvoiced_orders_flag );
8615: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8616: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8617: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8618: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8619: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8620: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8621: OE_DEBUG_PUB.Add(' l_open_ar_balance_flag = '|| l_open_ar_balance_flag );
8622: OE_DEBUG_PUB.Add(' l_incl_external_exposure_flag = ' ||
8615: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8616: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8617: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8618: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8619: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8620: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8621: OE_DEBUG_PUB.Add(' l_open_ar_balance_flag = '|| l_open_ar_balance_flag );
8622: OE_DEBUG_PUB.Add(' l_incl_external_exposure_flag = ' ||
8623: l_incl_external_exposure_flag );
8616: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8617: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8618: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8619: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8620: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8621: OE_DEBUG_PUB.Add(' l_open_ar_balance_flag = '|| l_open_ar_balance_flag );
8622: OE_DEBUG_PUB.Add(' l_incl_external_exposure_flag = ' ||
8623: l_incl_external_exposure_flag );
8624: OE_DEBUG_PUB.Add(' l_include_returns_flag = ' ||
8617: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8618: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8619: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8620: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8621: OE_DEBUG_PUB.Add(' l_open_ar_balance_flag = '|| l_open_ar_balance_flag );
8622: OE_DEBUG_PUB.Add(' l_incl_external_exposure_flag = ' ||
8623: l_incl_external_exposure_flag );
8624: OE_DEBUG_PUB.Add(' l_include_returns_flag = ' ||
8625: l_include_returns_flag );
8618: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8619: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8620: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8621: OE_DEBUG_PUB.Add(' l_open_ar_balance_flag = '|| l_open_ar_balance_flag );
8622: OE_DEBUG_PUB.Add(' l_incl_external_exposure_flag = ' ||
8623: l_incl_external_exposure_flag );
8624: OE_DEBUG_PUB.Add(' l_include_returns_flag = ' ||
8625: l_include_returns_flag );
8626: OE_DEBUG_PUB.Add(' -------------------------------------------------- ');
8620: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8621: OE_DEBUG_PUB.Add(' l_open_ar_balance_flag = '|| l_open_ar_balance_flag );
8622: OE_DEBUG_PUB.Add(' l_incl_external_exposure_flag = ' ||
8623: l_incl_external_exposure_flag );
8624: OE_DEBUG_PUB.Add(' l_include_returns_flag = ' ||
8625: l_include_returns_flag );
8626: OE_DEBUG_PUB.Add(' -------------------------------------------------- ');
8627: OE_DEBUG_PUB.Add(' ');
8628: END IF;
8622: OE_DEBUG_PUB.Add(' l_incl_external_exposure_flag = ' ||
8623: l_incl_external_exposure_flag );
8624: OE_DEBUG_PUB.Add(' l_include_returns_flag = ' ||
8625: l_include_returns_flag );
8626: OE_DEBUG_PUB.Add(' -------------------------------------------------- ');
8627: OE_DEBUG_PUB.Add(' ');
8628: END IF;
8629:
8630:
8623: l_incl_external_exposure_flag );
8624: OE_DEBUG_PUB.Add(' l_include_returns_flag = ' ||
8625: l_include_returns_flag );
8626: OE_DEBUG_PUB.Add(' -------------------------------------------------- ');
8627: OE_DEBUG_PUB.Add(' ');
8628: END IF;
8629:
8630:
8631: IF p_credit_level = 'CUSTOMER'
8633: -- Select total exposure using CUSTOMER CURSORs
8634:
8635: IF G_debug_flag = 'Y'
8636: THEN
8637: OE_DEBUG_PUB.Add('Get_order_Exposure CUSTOMER ',1);
8638: END IF;
8639:
8640: -- The exposure calculation must be done for all the
8641: -- usage currencies as part of the multi currency
8646: LOOP
8647: l_current_usage_cur := NULL ;
8648: IF G_debug_flag = 'Y'
8649: THEN
8650: OE_DEBUG_PUB.ADD(' ');
8651: OE_DEBUG_PUB.ADD('############################### ');
8652: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
8653: OE_DEBUG_PUB.ADD('############################### ');
8654: OE_DEBUG_PUB.ADD(' ');
8647: l_current_usage_cur := NULL ;
8648: IF G_debug_flag = 'Y'
8649: THEN
8650: OE_DEBUG_PUB.ADD(' ');
8651: OE_DEBUG_PUB.ADD('############################### ');
8652: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
8653: OE_DEBUG_PUB.ADD('############################### ');
8654: OE_DEBUG_PUB.ADD(' ');
8655: OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
8648: IF G_debug_flag = 'Y'
8649: THEN
8650: OE_DEBUG_PUB.ADD(' ');
8651: OE_DEBUG_PUB.ADD('############################### ');
8652: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
8653: OE_DEBUG_PUB.ADD('############################### ');
8654: OE_DEBUG_PUB.ADD(' ');
8655: OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
8656: OE_DEBUG_PUB.ADD(' ');
8649: THEN
8650: OE_DEBUG_PUB.ADD(' ');
8651: OE_DEBUG_PUB.ADD('############################### ');
8652: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
8653: OE_DEBUG_PUB.ADD('############################### ');
8654: OE_DEBUG_PUB.ADD(' ');
8655: OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
8656: OE_DEBUG_PUB.ADD(' ');
8657: OE_DEBUG_PUB.ADD('.');
8650: OE_DEBUG_PUB.ADD(' ');
8651: OE_DEBUG_PUB.ADD('############################### ');
8652: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
8653: OE_DEBUG_PUB.ADD('############################### ');
8654: OE_DEBUG_PUB.ADD(' ');
8655: OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
8656: OE_DEBUG_PUB.ADD(' ');
8657: OE_DEBUG_PUB.ADD('.');
8658: END IF;
8651: OE_DEBUG_PUB.ADD('############################### ');
8652: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
8653: OE_DEBUG_PUB.ADD('############################### ');
8654: OE_DEBUG_PUB.ADD(' ');
8655: OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
8656: OE_DEBUG_PUB.ADD(' ');
8657: OE_DEBUG_PUB.ADD('.');
8658: END IF;
8659:
8652: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
8653: OE_DEBUG_PUB.ADD('############################### ');
8654: OE_DEBUG_PUB.ADD(' ');
8655: OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
8656: OE_DEBUG_PUB.ADD(' ');
8657: OE_DEBUG_PUB.ADD('.');
8658: END IF;
8659:
8660: l_current_usage_cur := p_usage_curr(i).usage_curr_code ;
8653: OE_DEBUG_PUB.ADD('############################### ');
8654: OE_DEBUG_PUB.ADD(' ');
8655: OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
8656: OE_DEBUG_PUB.ADD(' ');
8657: OE_DEBUG_PUB.ADD('.');
8658: END IF;
8659:
8660: l_current_usage_cur := p_usage_curr(i).usage_curr_code ;
8661:
8666: IF p_global_exposure_flag = 'Y'
8667: THEN
8668: IF G_debug_flag = 'Y'
8669: THEN
8670: OE_DEBUG_PUB.Add(' Get cust_external_csr_global ');
8671: END IF;
8672:
8673: OPEN cust_external_csr_global (p_usage_curr(i).usage_curr_code);
8674: FETCH cust_external_csr_global INTO l_external_exposure;
8673: OPEN cust_external_csr_global (p_usage_curr(i).usage_curr_code);
8674: FETCH cust_external_csr_global INTO l_external_exposure;
8675: IF cust_external_csr_global%NOTFOUND
8676: THEN
8677: OE_DEBUG_PUB.Add(' No cust_external_csr_global balance found');
8678: l_external_exposure := 0;
8679: END IF;
8680: CLOSE cust_external_csr_global ;
8681:
8680: CLOSE cust_external_csr_global ;
8681:
8682: ELSE
8683:
8684: OE_DEBUG_PUB.Add(' Get customer external exposure balance ');
8685:
8686: OPEN cust_external_exposure_csr (p_usage_curr(i).usage_curr_code);
8687: FETCH cust_external_exposure_csr INTO l_external_exposure;
8688: IF cust_external_exposure_csr%NOTFOUND
8686: OPEN cust_external_exposure_csr (p_usage_curr(i).usage_curr_code);
8687: FETCH cust_external_exposure_csr INTO l_external_exposure;
8688: IF cust_external_exposure_csr%NOTFOUND
8689: THEN
8690: OE_DEBUG_PUB.Add(' No external exposure balance found');
8691: l_external_exposure := 0;
8692: END IF;
8693: CLOSE cust_external_exposure_csr;
8694: END IF ; -- p_global check
8705: IF p_global_exposure_flag = 'Y'
8706: THEN
8707: IF G_debug_flag = 'Y'
8708: THEN
8709: OE_DEBUG_PUB.Add(' Get cust_ar_balance_global ');
8710: END IF;
8711:
8712: OPEN cust_ar_balance_global (p_usage_curr(i).usage_curr_code);
8713: FETCH cust_ar_balance_global INTO l_total_from_ar ;
8715: IF cust_ar_balance_global%NOTFOUND
8716: THEN
8717: IF G_debug_flag = 'Y'
8718: THEN
8719: OE_DEBUG_PUB.Add(' No AR balance found');
8720: END IF;
8721:
8722: l_total_from_ar := 0 ;
8723: END IF;
8726:
8727: ELSE
8728: IF G_debug_flag = 'Y'
8729: THEN
8730: OE_DEBUG_PUB.Add(' Get cust_ar_balance ');
8731: END IF;
8732:
8733: OPEN cust_ar_balance (p_usage_curr(i).usage_curr_code);
8734: FETCH cust_ar_balance INTO l_total_from_ar ;
8736: IF cust_ar_balance%NOTFOUND
8737: THEN
8738: IF G_debug_flag = 'Y'
8739: THEN
8740: OE_DEBUG_PUB.Add(' No AR balance found');
8741: END IF;
8742:
8743: l_total_from_ar := 0 ;
8744: END IF;
8752: IF p_global_exposure_flag = 'Y'
8753: THEN
8754: IF G_debug_flag = 'Y'
8755: THEN
8756: OE_DEBUG_PUB.Add(' Get cust_pay_risk_global ');
8757: END IF;
8758:
8759: OPEN cust_pay_risk_global (p_usage_curr(i).usage_curr_code);
8760: FETCH cust_pay_risk_global INTO l_payments_at_risk;
8762: THEN
8763: l_payments_at_risk := 0 ;
8764: IF G_debug_flag = 'Y'
8765: THEN
8766: OE_DEBUG_PUB.Add(' No Pay at risk found ');
8767: END IF;
8768: END IF;
8769:
8770: CLOSE cust_pay_risk_global;
8770: CLOSE cust_pay_risk_global;
8771: ELSE
8772: IF G_debug_flag = 'Y'
8773: THEN
8774: OE_DEBUG_PUB.Add(' Get cust_pay_risk ');
8775: END IF;
8776:
8777: OPEN cust_pay_risk (p_usage_curr(i).usage_curr_code);
8778: FETCH cust_pay_risk INTO l_payments_at_risk;
8778: FETCH cust_pay_risk INTO l_payments_at_risk;
8779: IF cust_pay_risk%NOTFOUND
8780: THEN
8781: l_payments_at_risk := 0 ;
8782: OE_DEBUG_PUB.Add(' No Pay at risk found ');
8783: END IF;
8784:
8785: CLOSE cust_pay_risk;
8786: END IF; -- Global
8793: (p_cust_account_id => p_customer_id ) = 'Y'
8794: THEN
8795: IF G_debug_flag = 'Y'
8796: THEN
8797: OE_DEBUG_PUB.Add(' Call DRAWEE BR exposure ');
8798: END IF;
8799:
8800: IF p_global_exposure_flag = 'Y'
8801: THEN
8800: IF p_global_exposure_flag = 'Y'
8801: THEN
8802: IF G_debug_flag = 'Y'
8803: THEN
8804: OE_DEBUG_PUB.Add(' Get cust_br_balance_global ');
8805: END IF;
8806:
8807: OPEN cust_br_balance_global (p_usage_curr(i).usage_curr_code);
8808: FETCH cust_br_balance_global INTO l_total_from_br ;
8810: IF cust_br_balance_global%NOTFOUND
8811: THEN
8812: IF G_debug_flag = 'Y'
8813: THEN
8814: OE_DEBUG_PUB.Add(' No AR balance found');
8815: END IF;
8816:
8817: l_total_from_br := 0 ;
8818: END IF;
8821:
8822: ELSE
8823: IF G_debug_flag = 'Y'
8824: THEN
8825: OE_DEBUG_PUB.Add(' Get cust_br_balance ');
8826: END IF;
8827:
8828: OPEN cust_br_balance (p_usage_curr(i).usage_curr_code);
8829: FETCH cust_br_balance INTO l_total_from_br ;
8829: FETCH cust_br_balance INTO l_total_from_br ;
8830:
8831: IF cust_br_balance%NOTFOUND
8832: THEN
8833: OE_DEBUG_PUB.Add(' No AR balance found');
8834: l_total_from_br := 0 ;
8835: END IF;
8836:
8837: CLOSE cust_br_balance;
8843: IF p_global_exposure_flag = 'Y'
8844: THEN
8845: IF G_debug_flag = 'Y'
8846: THEN
8847: OE_DEBUG_PUB.Add(' Get cust_pay_risk_global ');
8848: END IF;
8849:
8850: OPEN cust_br_pay_risk_global (p_usage_curr(i).usage_curr_code);
8851: FETCH cust_br_pay_risk_global INTO l_payments_at_risk_br;
8851: FETCH cust_br_pay_risk_global INTO l_payments_at_risk_br;
8852: IF cust_br_pay_risk_global%NOTFOUND
8853: THEN
8854: l_payments_at_risk_br := 0 ;
8855: OE_DEBUG_PUB.Add(' No Pay at risk found ');
8856: END IF;
8857:
8858: CLOSE cust_br_pay_risk_global;
8859: ELSE
8858: CLOSE cust_br_pay_risk_global;
8859: ELSE
8860: IF G_debug_flag = 'Y'
8861: THEN
8862: OE_DEBUG_PUB.Add(' Get cust_pay_risk ');
8863: END IF;
8864:
8865: OPEN cust_br_pay_risk (p_usage_curr(i).usage_curr_code);
8866: FETCH cust_br_pay_risk INTO l_payments_at_risk_br;
8866: FETCH cust_br_pay_risk INTO l_payments_at_risk_br;
8867: IF cust_br_pay_risk%NOTFOUND
8868: THEN
8869: l_payments_at_risk_br := 0 ;
8870: OE_DEBUG_PUB.Add(' No Pay at risk found ');
8871: END IF;
8872: CLOSE cust_br_pay_risk;
8873: END IF; -- Global
8874: ELSE
8873: END IF; -- Global
8874: ELSE
8875: IF G_debug_flag = 'Y'
8876: THEN
8877: OE_DEBUG_PUB.Add(' No Drawee check required ');
8878: END IF;
8879:
8880: END IF; -- DRAWEE
8881: ELSE
8880: END IF; -- DRAWEE
8881: ELSE
8882: IF G_debug_flag = 'Y'
8883: THEN
8884: OE_DEBUG_PUB.Add(' No Drawee check - NO horizon ' ||
8885: p_credit_check_rule_rec.OPEN_AR_DAYS) ;
8886: END IF;
8887: END IF;
8888:
8894: THEN
8895: BEGIN
8896: IF G_debug_flag = 'Y'
8897: THEN
8898: OE_DEBUG_PUB.Add(' Begin OM Exposure calculation ');
8899: END IF;
8900:
8901: IF OE_Commitment_PVT.Do_Commitment_Sequencing
8902: THEN
8903: IF p_global_exposure_flag = 'Y'
8904: THEN
8905: IF G_debug_flag = 'Y'
8906: THEN
8907: OE_DEBUG_PUB.Add(' cust_commitments_global');
8908: END IF;
8909:
8910: OPEN cust_commitment_total_global
8911: (p_usage_curr(i).usage_curr_code);
8914:
8915: IF cust_commitment_total_global%NOTFOUND
8916: THEN
8917: l_total_commitment := 0 ;
8918: OE_DEBUG_PUB.Add('No commitment amount found ');
8919: END IF;
8920:
8921: CLOSE cust_commitment_total_global ;
8922: ELSE
8921: CLOSE cust_commitment_total_global ;
8922: ELSE
8923: IF G_debug_flag = 'Y'
8924: THEN
8925: OE_DEBUG_PUB.Add(' cust_commitments ');
8926: END IF;
8927:
8928: OPEN customer_commitment_total (p_usage_curr(i).usage_curr_code);
8929: FETCH customer_commitment_total INTO l_total_commitment ;
8930:
8931: IF customer_commitment_total%NOTFOUND
8932: THEN
8933: l_total_commitment := 0 ;
8934: OE_DEBUG_PUB.Add('No commitment amount found ');
8935: END IF;
8936:
8937: CLOSE customer_commitment_total ;
8938: END IF ; -- Global
8955: -----returns are not included
8956:
8957: IF G_debug_flag = 'Y'
8958: THEN
8959: OE_DEBUG_PUB.Add(' select cust_uninvoiced_orders_global ');
8960: END IF;
8961:
8962: OPEN cust_uninvoiced_orders_global(p_usage_curr(i).usage_curr_code);
8963: FETCH cust_uninvoiced_orders_global INTO l_total_on_order;
8964:
8965: IF cust_uninvoiced_orders_global%NOTFOUND
8966: THEN
8967: l_total_on_order := 0 ;
8968: OE_DEBUG_PUB.Add('No Uninvoiced Order amount found ');
8969: END IF;
8970: CLOSE cust_uninvoiced_orders_global;
8971:
8972: ELSE
8972: ELSE
8973: -----returns are included
8974: IF G_debug_flag = 'Y'
8975: THEN
8976: OE_DEBUG_PUB.Add(' select cust_uninv_orders_global_ret ');
8977: END IF;
8978:
8979: OPEN cust_uninv_orders_global_ret(p_usage_curr(i).usage_curr_code);
8980: FETCH cust_uninv_orders_global_ret INTO l_total_on_order;
8981:
8982: IF cust_uninv_orders_global_ret%NOTFOUND
8983: THEN
8984: l_total_on_order := 0 ;
8985: OE_DEBUG_PUB.Add('No Uninvoiced Order amount found ');
8986: END IF;
8987:
8988: CLOSE cust_uninv_orders_global_ret;
8989:
8996: THEN
8997: -----returns are not included
8998: IF G_debug_flag = 'Y'
8999: THEN
9000: OE_DEBUG_PUB.Add(' select cust_uninvoiced_orders ');
9001: END IF;
9002:
9003: OPEN cust_uninvoiced_orders(p_usage_curr(i).usage_curr_code);
9004: FETCH cust_uninvoiced_orders INTO l_total_on_order;
9005:
9006: IF cust_uninvoiced_orders%NOTFOUND
9007: THEN
9008: l_total_on_order := 0 ;
9009: OE_DEBUG_PUB.Add('No Uninvoiced Order amount found ');
9010: END IF;
9011:
9012: CLOSE cust_uninvoiced_orders;
9013:
9014: ELSE
9015: -----returns are included
9016: IF G_debug_flag = 'Y'
9017: THEN
9018: OE_DEBUG_PUB.Add(' select cust_uninv_orders_ret ');
9019: END IF;
9020:
9021: OPEN cust_uninv_orders_ret(p_usage_curr(i).usage_curr_code);
9022: FETCH cust_uninv_orders_ret INTO l_total_on_order;
9023:
9024: IF cust_uninv_orders_ret%NOTFOUND
9025: THEN
9026: l_total_on_order := 0 ;
9027: OE_DEBUG_PUB.Add('No Uninvoiced Order amount found ');
9028: END IF;
9029:
9030: CLOSE cust_uninv_orders_ret;
9031:
9042: THEN
9043: -----returns are not included
9044: IF G_debug_flag = 'Y'
9045: THEN
9046: OE_DEBUG_PUB.Add(' cust_uninv_hdr_freight1_global ');
9047: END IF;
9048:
9049: OPEN cust_uninv_hdr_freight1_global
9050: (p_usage_curr(i).usage_curr_code);
9054:
9055: IF cust_uninv_hdr_freight1_global%NOTFOUND
9056: THEN
9057: l_uninvoiced_hdr_freight1 := 0 ;
9058: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight1 found ');
9059: END IF;
9060:
9061: CLOSE cust_uninv_hdr_freight1_global;
9062:
9063: ELSE
9064: -----returns are included
9065: IF G_debug_flag = 'Y'
9066: THEN
9067: OE_DEBUG_PUB.Add(' open cust_uninv_hdr_fr1_glb_ret ');
9068: END IF;
9069:
9070: OPEN cust_uninv_hdr_fr1_glb_ret
9071: (p_usage_curr(i).usage_curr_code);
9075:
9076: IF cust_uninv_hdr_fr1_glb_ret%NOTFOUND
9077: THEN
9078: l_uninvoiced_hdr_freight1 := 0 ;
9079: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight1 found ');
9080: END IF;
9081:
9082: CLOSE cust_uninv_hdr_fr1_glb_ret;
9083:
9089: -----returns are not included
9090:
9091: IF G_debug_flag = 'Y'
9092: THEN
9093: OE_DEBUG_PUB.Add(' cust_uninv_hdr_freight2_global ');
9094: END IF;
9095:
9096:
9097: OPEN cust_uninv_hdr_freight2_global
9104: THEN
9105: l_uninvoiced_hdr_freight2 := 0 ;
9106: IF G_debug_flag = 'Y'
9107: THEN
9108: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight2 found ');
9109: END IF;
9110: END IF;
9111:
9112: CLOSE cust_uninv_hdr_freight2_global ;
9115: -----returns are included
9116:
9117: IF G_debug_flag = 'Y'
9118: THEN
9119: OE_DEBUG_PUB.Add(' open cust_uninv_hdr_fr2_glb_ret ');
9120: END IF;
9121:
9122: OPEN cust_uninv_hdr_fr2_glb_ret
9123: (p_usage_curr(i).usage_curr_code);
9129: THEN
9130: l_uninvoiced_hdr_freight2 := 0 ;
9131: IF G_debug_flag = 'Y'
9132: THEN
9133: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight2 found ');
9134: END IF;
9135: END IF;
9136:
9137: CLOSE cust_uninv_hdr_fr2_glb_ret;
9146: -----returns are not included
9147:
9148: IF G_debug_flag = 'Y'
9149: THEN
9150: OE_DEBUG_PUB.Add(' cust_uninv_hdr_freight1 ');
9151: END IF;
9152:
9153: OPEN cust_uninv_hdr_freight1(p_usage_curr(i).usage_curr_code);
9154: FETCH cust_uninv_hdr_freight1 INTO l_uninvoiced_hdr_freight1;
9157: THEN
9158: l_uninvoiced_hdr_freight1 := 0 ;
9159: IF G_debug_flag = 'Y'
9160: THEN
9161: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight1 found ');
9162: END IF;
9163: END IF;
9164:
9165: CLOSE cust_uninv_hdr_freight1;
9165: CLOSE cust_uninv_hdr_freight1;
9166:
9167: IF G_debug_flag = 'Y'
9168: THEN
9169: OE_DEBUG_PUB.Add(' cust_uninv_hdr_freight2 ');
9170: END IF;
9171:
9172: OPEN cust_uninv_hdr_freight2(p_usage_curr(i).usage_curr_code);
9173: FETCH cust_uninv_hdr_freight2 INTO l_uninvoiced_hdr_freight2;
9176: THEN
9177: l_uninvoiced_hdr_freight2 := 0 ;
9178: IF G_debug_flag = 'Y'
9179: THEN
9180: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight2 found ');
9181: END IF;
9182: END IF;
9183: CLOSE cust_uninv_hdr_freight2;
9184:
9186: -----returns are included
9187:
9188: IF G_debug_flag = 'Y'
9189: THEN
9190: OE_DEBUG_PUB.Add(' open cust_uninv_hdr_freight1_ret ');
9191: END IF;
9192:
9193: OPEN cust_uninv_hdr_freight1_ret(p_usage_curr(i).usage_curr_code);
9194: FETCH cust_uninv_hdr_freight1_ret INTO l_uninvoiced_hdr_freight1;
9197: THEN
9198: l_uninvoiced_hdr_freight1 := 0 ;
9199: IF G_debug_flag = 'Y'
9200: THEN
9201: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight1 found ');
9202: END IF;
9203: END IF;
9204:
9205: CLOSE cust_uninv_hdr_freight1_ret;
9205: CLOSE cust_uninv_hdr_freight1_ret;
9206:
9207: IF G_debug_flag = 'Y'
9208: THEN
9209: OE_DEBUG_PUB.Add(' open cust_uninv_hdr_freight2_ret ');
9210: END IF;
9211:
9212: OPEN cust_uninv_hdr_freight2_ret(p_usage_curr(i).usage_curr_code);
9213: FETCH cust_uninv_hdr_freight2_ret INTO l_uninvoiced_hdr_freight2;
9216: THEN
9217: l_uninvoiced_hdr_freight2 := 0 ;
9218: IF G_debug_flag = 'Y'
9219: THEN
9220: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight2 found ');
9221: END IF;
9222: END IF;
9223: CLOSE cust_uninv_hdr_freight2_ret;
9224:
9241: -----returns are not included
9242:
9243: IF G_debug_flag = 'Y'
9244: THEN
9245: OE_DEBUG_PUB.Add(' cust_orders_not_on_hold_global ');
9246: END IF;
9247:
9248: OPEN cust_orders_not_on_hold_global
9249: (p_usage_curr(i).usage_curr_code);
9252:
9253: IF cust_orders_not_on_hold_global%NOTFOUND
9254: THEN
9255: l_total_no_hold := 0 ;
9256: OE_DEBUG_PUB.Add('No Orders not on hold amount found ');
9257: END IF;
9258:
9259: CLOSE cust_orders_not_on_hold_global ;
9260:
9261: -- If the l_need_exposure_details flag enabled, get the
9262: -- OM amount on hold
9263: IF l_need_exposure_details = 'Y' THEN
9264: IF G_debug_flag = 'Y' THEN
9265: OE_DEBUG_PUB.Add(' cust_orders_on_hold_global ');
9266: END IF;
9267: OPEN cust_orders_on_hold_global(p_usage_curr(i).usage_curr_code);
9268: FETCH cust_orders_on_hold_global
9269: INTO l_total_on_hold;
9269: INTO l_total_on_hold;
9270:
9271: IF cust_orders_on_hold_global%NOTFOUND THEN
9272: l_total_on_hold := 0 ;
9273: OE_DEBUG_PUB.Add('No global orders on hold amount found ');
9274: END IF;
9275: CLOSE cust_orders_on_hold_global ;
9276: END IF;
9277:
9279: -----returns are included
9280:
9281: IF G_debug_flag = 'Y'
9282: THEN
9283: OE_DEBUG_PUB.Add(' open cust_ord_not_on_hold_glb_ret ');
9284: END IF;
9285:
9286: OPEN cust_ord_not_on_hold_glb_ret
9287: (p_usage_curr(i).usage_curr_code);
9290:
9291: IF cust_ord_not_on_hold_glb_ret%NOTFOUND
9292: THEN
9293: l_total_no_hold := 0 ;
9294: OE_DEBUG_PUB.Add('No Orders not on hold amount found ');
9295: END IF;
9296:
9297: CLOSE cust_ord_not_on_hold_glb_ret ;
9298:
9299: -- If the l_need_exposure_details flag enabled, get the
9300: -- OM amount on hold
9301: IF l_need_exposure_details = 'Y' THEN
9302: IF G_debug_flag = 'Y' THEN
9303: OE_DEBUG_PUB.Add(' cust_ord_on_hold_glb_ret ');
9304: END IF;
9305: OPEN cust_ord_on_hold_glb_ret(p_usage_curr(i).usage_curr_code);
9306: FETCH cust_ord_on_hold_glb_ret
9307: INTO l_total_on_hold;
9307: INTO l_total_on_hold;
9308:
9309: IF cust_ord_on_hold_glb_ret%NOTFOUND THEN
9310: l_total_on_hold := 0 ;
9311: OE_DEBUG_PUB.Add('No return global orders on hold amount found ');
9312: END IF;
9313: CLOSE cust_ord_on_hold_glb_ret ;
9314: END IF;
9315:
9329: -----returns are not included
9330:
9331: IF G_debug_flag = 'Y'
9332: THEN
9333: OE_DEBUG_PUB.Add(' cust_no_hold_hdr_freight1_glb ');
9334: END IF;
9335:
9336: OPEN cust_no_hold_hdr_freight1_glb
9337: (p_usage_curr(i).usage_curr_code);
9343: THEN
9344: l_no_hold_hdr_freight1 := 0 ;
9345: IF G_debug_flag = 'Y'
9346: THEN
9347: OE_DEBUG_PUB.Add('No orders without hold freight1 amount found ');
9348: END IF;
9349: END IF;
9350:
9351: CLOSE cust_no_hold_hdr_freight1_glb ;
9351: CLOSE cust_no_hold_hdr_freight1_glb ;
9352:
9353: IF G_debug_flag = 'Y'
9354: THEN
9355: OE_DEBUG_PUB.Add(' cust_no_hold_hdr_freight2_glb ');
9356: END IF;
9357:
9358: OPEN cust_no_hold_hdr_freight2_glb
9359: (p_usage_curr(i).usage_curr_code);
9365: THEN
9366: l_hold_hdr_freight2 := 0 ;
9367: IF G_debug_flag = 'Y'
9368: THEN
9369: OE_DEBUG_PUB.Add('No orders without hold freight2 amount found');
9370: END IF;
9371: END IF;
9372: CLOSE cust_no_hold_hdr_freight2_glb ;
9373:
9374: -- If the l_need_exposure_details flag enabled, get the
9375: -- OM amount on hold
9376: IF l_need_exposure_details = 'Y' THEN
9377: IF G_debug_flag = 'Y' THEN
9378: OE_DEBUG_PUB.Add(' cust_hold_hdr_freight1_glb ');
9379: END IF;
9380:
9381: OPEN cust_hold_hdr_freight1_glb
9382: (p_usage_curr(i).usage_curr_code);
9384: INTO l_hold_hdr_freight1;
9385:
9386: IF cust_hold_hdr_freight1_glb%NOTFOUND THEN
9387: l_hold_hdr_freight1 := 0 ;
9388: OE_DEBUG_PUB.Add
9389: ('No orders on hold with freight1 amount found ');
9390: END IF;
9391:
9392: CLOSE cust_hold_hdr_freight1_glb;
9392: CLOSE cust_hold_hdr_freight1_glb;
9393:
9394: -- get header freight2 on hold
9395: IF G_debug_flag = 'Y' THEN
9396: OE_DEBUG_PUB.Add(' cust_hold_hdr_freight2_glb ');
9397: END IF;
9398:
9399: OPEN cust_hold_hdr_freight2_glb
9400: (p_usage_curr(i).usage_curr_code);
9402: INTO l_hold_hdr_freight2;
9403:
9404: IF cust_hold_hdr_freight2_glb%NOTFOUND THEN
9405: l_hold_hdr_freight2 := 0 ;
9406: OE_DEBUG_PUB.Add
9407: ('No orders on hold with freight2 amount found ');
9408: END IF;
9409:
9410: CLOSE cust_hold_hdr_freight2_glb;
9414: -----returns are included
9415:
9416: IF G_debug_flag = 'Y'
9417: THEN
9418: OE_DEBUG_PUB.Add('open cust_no_hold_hdr_fr1_glb_ret ');
9419: END IF;
9420:
9421: OPEN cust_no_hold_hdr_fr1_glb_ret
9422: (p_usage_curr(i).usage_curr_code);
9428: THEN
9429: l_no_hold_hdr_freight1 := 0 ;
9430: IF G_debug_flag = 'Y'
9431: THEN
9432: OE_DEBUG_PUB.Add('No orders without hold freight1 amount found ');
9433: END IF;
9434: END IF;
9435:
9436: CLOSE cust_no_hold_hdr_fr1_glb_ret ;
9436: CLOSE cust_no_hold_hdr_fr1_glb_ret ;
9437:
9438: IF G_debug_flag = 'Y'
9439: THEN
9440: OE_DEBUG_PUB.Add('open cust_no_hold_hdr_fr2_glb_ret ');
9441: END IF;
9442:
9443: OPEN cust_no_hold_hdr_fr2_glb_ret
9444: (p_usage_curr(i).usage_curr_code);
9450: THEN
9451: l_hold_hdr_freight2 := 0 ;
9452: IF G_debug_flag = 'Y'
9453: THEN
9454: OE_DEBUG_PUB.Add('No orders without hold freight2 amount found');
9455: END IF;
9456: END IF;
9457: CLOSE cust_no_hold_hdr_fr2_glb_ret ;
9458:
9459: -- If the l_need_exposure_details flag enabled, get the
9460: -- OM amount on hold
9461: IF l_need_exposure_details = 'Y' THEN
9462: IF G_debug_flag = 'Y' THEN
9463: OE_DEBUG_PUB.Add(' cust_hold_hdr_fr1_glb_ret ');
9464: END IF;
9465:
9466: OPEN cust_hold_hdr_fr1_glb_ret
9467: (p_usage_curr(i).usage_curr_code);
9469: INTO l_hold_hdr_freight1;
9470:
9471: IF cust_hold_hdr_fr1_glb_ret%NOTFOUND THEN
9472: l_hold_hdr_freight1 := 0 ;
9473: OE_DEBUG_PUB.Add
9474: ('No orders on hold with freight1 amount found ');
9475: END IF;
9476:
9477: CLOSE cust_hold_hdr_fr1_glb_ret;
9477: CLOSE cust_hold_hdr_fr1_glb_ret;
9478:
9479: -- get header freight2 on hold
9480: IF G_debug_flag = 'Y' THEN
9481: OE_DEBUG_PUB.Add(' cust_hold_hdr_fr2_glb_ret ');
9482: END IF;
9483:
9484: OPEN cust_hold_hdr_fr2_glb_ret
9485: (p_usage_curr(i).usage_curr_code);
9487: INTO l_hold_hdr_freight2;
9488:
9489: IF cust_hold_hdr_fr2_glb_ret%NOTFOUND THEN
9490: l_hold_hdr_freight2 := 0 ;
9491: OE_DEBUG_PUB.Add
9492: ('No orders on hold with freight2 amount found ');
9493: END IF;
9494:
9495: CLOSE cust_hold_hdr_fr2_glb_ret;
9507: -----returns are not included
9508:
9509: IF G_debug_flag = 'Y'
9510: THEN
9511: OE_DEBUG_PUB.Add(' cust_orders_not_on_hold ');
9512: END IF;
9513:
9514: OPEN cust_orders_not_on_hold (p_usage_curr(i).usage_curr_code);
9515: FETCH cust_orders_not_on_hold INTO l_total_no_hold;
9518: THEN
9519: l_total_no_hold := 0 ;
9520: IF G_debug_flag = 'Y'
9521: THEN
9522: OE_DEBUG_PUB.Add('No orders not on hold amount found ');
9523: END IF;
9524: END IF;
9525:
9526: CLOSE cust_orders_not_on_hold;
9528: -- If the l_need_exposure_details flag enabled, get the
9529: -- OM amount on hold
9530: IF l_need_exposure_details = 'Y' THEN
9531: IF G_debug_flag = 'Y' THEN
9532: OE_DEBUG_PUB.Add(' cust_orders_on_hold ');
9533: END IF;
9534:
9535: OPEN cust_orders_on_hold (p_usage_curr(i).usage_curr_code);
9536: FETCH cust_orders_on_hold INTO l_total_on_hold;
9537:
9538: IF cust_orders_on_hold%NOTFOUND THEN
9539: l_total_on_hold := 0 ;
9540: IF G_debug_flag = 'Y' THEN
9541: OE_DEBUG_PUB.Add('No customer orders on hold amount found ');
9542: END IF;
9543: END IF;
9544: CLOSE cust_orders_on_hold;
9545: END IF;
9548: -----returns are not included
9549:
9550: IF G_debug_flag = 'Y'
9551: THEN
9552: OE_DEBUG_PUB.Add(' cust_ord_not_on_hold_ret ');
9553: END IF;
9554:
9555: OPEN cust_ord_not_on_hold_ret (p_usage_curr(i).usage_curr_code);
9556: FETCH cust_ord_not_on_hold_ret INTO l_total_no_hold;
9559: THEN
9560: l_total_no_hold := 0 ;
9561: IF G_debug_flag = 'Y'
9562: THEN
9563: OE_DEBUG_PUB.Add('No orders not on hold amount found ');
9564: END IF;
9565: END IF;
9566:
9567: CLOSE cust_ord_not_on_hold_ret;
9569: -- If the l_need_exposure_details flag enabled, get the
9570: -- OM amount on hold
9571: IF l_need_exposure_details = 'Y' THEN
9572: IF G_debug_flag = 'Y' THEN
9573: OE_DEBUG_PUB.Add(' cust_ord_on_hold_ret ');
9574: END IF;
9575:
9576: OPEN cust_ord_on_hold_ret (p_usage_curr(i).usage_curr_code);
9577: FETCH cust_ord_on_hold_ret INTO l_total_on_hold;
9578:
9579: IF cust_ord_on_hold_ret%NOTFOUND THEN
9580: l_total_on_hold := 0 ;
9581: IF G_debug_flag = 'Y' THEN
9582: OE_DEBUG_PUB.Add('No customer orders on hold amount found ');
9583: END IF;
9584: END IF;
9585: CLOSE cust_ord_on_hold_ret;
9586: END IF;
9600: -----returns are not included
9601:
9602: IF G_debug_flag = 'Y'
9603: THEN
9604: OE_DEBUG_PUB.Add(' cust_no_hold_hdr_freight1 ');
9605: END IF;
9606:
9607: OPEN cust_no_hold_hdr_freight1(p_usage_curr(i).usage_curr_code);
9608: FETCH cust_no_hold_hdr_freight1 INTO l_no_hold_hdr_freight1;
9611: THEN
9612: l_no_hold_hdr_freight1 := 0 ;
9613: IF G_debug_flag = 'Y'
9614: THEN
9615: OE_DEBUG_PUB.Add('No orders without hold freight1 amount found ');
9616: END IF;
9617: END IF;
9618:
9619: CLOSE cust_no_hold_hdr_freight1;
9619: CLOSE cust_no_hold_hdr_freight1;
9620:
9621: IF G_debug_flag = 'Y'
9622: THEN
9623: OE_DEBUG_PUB.Add(' cust_no_hold_hdr_freight2 ');
9624: END IF;
9625:
9626: OPEN cust_no_hold_hdr_freight2(p_usage_curr(i).usage_curr_code);
9627: FETCH cust_no_hold_hdr_freight2 INTO l_no_hold_hdr_freight2;
9630: THEN
9631: l_no_hold_hdr_freight2 := 0 ;
9632: IF G_debug_flag = 'Y'
9633: THEN
9634: OE_DEBUG_PUB.Add('No orders without hold freight2 amount found');
9635: END IF;
9636: END IF;
9637: CLOSE cust_no_hold_hdr_freight2;
9638:
9642: --
9643: -- Get cust on hold header freight1
9644: --
9645: IF G_debug_flag = 'Y' THEN
9646: OE_DEBUG_PUB.Add(' cust_hold_hdr_freight1 ');
9647: END IF;
9648:
9649: OPEN cust_hold_hdr_freight1(p_usage_curr(i).usage_curr_code);
9650: FETCH cust_hold_hdr_freight1 INTO l_hold_hdr_freight1;
9651:
9652: IF cust_hold_hdr_freight1%NOTFOUND THEN
9653: l_hold_hdr_freight1 := 0 ;
9654: IF G_debug_flag = 'Y' THEN
9655: OE_DEBUG_PUB.Add('No orders with hold freight1 amount found ');
9656: END IF;
9657: END IF;
9658:
9659: CLOSE cust_hold_hdr_freight1;
9660: --
9661: -- Get cust on hold header freight2
9662: --
9663: IF G_debug_flag = 'Y' THEN
9664: OE_DEBUG_PUB.Add(' cust_hold_hdr_freight2 ');
9665: END IF;
9666:
9667: OPEN cust_hold_hdr_freight2(p_usage_curr(i).usage_curr_code);
9668: FETCH cust_hold_hdr_freight2 INTO l_hold_hdr_freight2;
9669:
9670: IF cust_hold_hdr_freight2%NOTFOUND THEN
9671: l_hold_hdr_freight2 := 0 ;
9672: IF G_debug_flag = 'Y' THEN
9673: OE_DEBUG_PUB.Add('No orders with hold freight2 amount found ');
9674: END IF;
9675: END IF;
9676:
9677: CLOSE cust_hold_hdr_freight2;
9681: -----returns are included
9682:
9683: IF G_debug_flag = 'Y'
9684: THEN
9685: OE_DEBUG_PUB.Add(' cust_no_hold_hdr_freight1_ret ');
9686: END IF;
9687:
9688: OPEN cust_no_hold_hdr_freight1_ret(p_usage_curr(i).usage_curr_code);
9689: FETCH cust_no_hold_hdr_freight1_ret INTO l_no_hold_hdr_freight1;
9692: THEN
9693: l_no_hold_hdr_freight1 := 0 ;
9694: IF G_debug_flag = 'Y'
9695: THEN
9696: OE_DEBUG_PUB.Add('No orders without hold freight1 amount found ');
9697: END IF;
9698: END IF;
9699:
9700: CLOSE cust_no_hold_hdr_freight1_ret;
9700: CLOSE cust_no_hold_hdr_freight1_ret;
9701:
9702: IF G_debug_flag = 'Y'
9703: THEN
9704: OE_DEBUG_PUB.Add('open cust_no_hold_hdr_freight2_ret ');
9705: END IF;
9706:
9707: OPEN cust_no_hold_hdr_freight2_ret(p_usage_curr(i).usage_curr_code);
9708: FETCH cust_no_hold_hdr_freight2_ret INTO l_no_hold_hdr_freight2;
9711: THEN
9712: l_no_hold_hdr_freight2 := 0 ;
9713: IF G_debug_flag = 'Y'
9714: THEN
9715: OE_DEBUG_PUB.Add('No orders without hold freight2 amount found');
9716: END IF;
9717: END IF;
9718:
9719: CLOSE cust_no_hold_hdr_freight2_ret;
9725: --
9726: -- Get cust on hold header freight1 with returns
9727: --
9728: IF G_debug_flag = 'Y' THEN
9729: OE_DEBUG_PUB.Add(' cust_hold_hdr_freight1_ret ');
9730: END IF;
9731:
9732: OPEN cust_hold_hdr_freight1_ret(p_usage_curr(i).usage_curr_code);
9733: FETCH cust_hold_hdr_freight1_ret INTO l_hold_hdr_freight1;
9734:
9735: IF cust_hold_hdr_freight1_ret%NOTFOUND THEN
9736: l_hold_hdr_freight1 := 0 ;
9737: IF G_debug_flag = 'Y' THEN
9738: OE_DEBUG_PUB.Add('No orders with hold freight1 amount found ');
9739: END IF;
9740: END IF;
9741:
9742: CLOSE cust_hold_hdr_freight1_ret;
9743: --
9744: -- Get cust on hold header freight2
9745: --
9746: IF G_debug_flag = 'Y' THEN
9747: OE_DEBUG_PUB.Add(' cust_hold_hdr_freight2_ret ');
9748: END IF;
9749:
9750: OPEN cust_hold_hdr_freight2_ret(p_usage_curr(i).usage_curr_code);
9751: FETCH cust_hold_hdr_freight2_ret INTO l_hold_hdr_freight2;
9752:
9753: IF cust_hold_hdr_freight2_ret%NOTFOUND THEN
9754: l_hold_hdr_freight2 := 0 ;
9755: IF G_debug_flag = 'Y' THEN
9756: OE_DEBUG_PUB.Add('No orders with hold freight2 amount found ');
9757: END IF;
9758: END IF;
9759:
9760: CLOSE cust_hold_hdr_freight2_ret;
9768: END ;
9769: ELSE
9770: IF G_debug_flag = 'Y'
9771: THEN
9772: OE_DEBUG_PUB.Add(' No OM Exposure calculation ',1);
9773: END IF;
9774: END IF; -- Uninvoiced Orders
9775:
9776:
9837:
9838: IF G_debug_flag = 'Y'
9839: THEN
9840:
9841: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
9842: || l_usage_total_exposure );
9843: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
9844: || l_cum_total_from_ar );
9845: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
9839: THEN
9840:
9841: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
9842: || l_usage_total_exposure );
9843: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
9844: || l_cum_total_from_ar );
9845: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
9846: || l_total_from_ar);
9847: OE_DEBUG_PUB.ADD(' l_total_from_br = '
9841: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
9842: || l_usage_total_exposure );
9843: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
9844: || l_cum_total_from_ar );
9845: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
9846: || l_total_from_ar);
9847: OE_DEBUG_PUB.ADD(' l_total_from_br = '
9848: || l_total_from_br);
9849: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
9843: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
9844: || l_cum_total_from_ar );
9845: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
9846: || l_total_from_ar);
9847: OE_DEBUG_PUB.ADD(' l_total_from_br = '
9848: || l_total_from_br);
9849: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
9850: || l_cum_payments_at_risk );
9851: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
9845: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
9846: || l_total_from_ar);
9847: OE_DEBUG_PUB.ADD(' l_total_from_br = '
9848: || l_total_from_br);
9849: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
9850: || l_cum_payments_at_risk );
9851: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
9852: || l_payments_at_risk);
9853: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
9847: OE_DEBUG_PUB.ADD(' l_total_from_br = '
9848: || l_total_from_br);
9849: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
9850: || l_cum_payments_at_risk );
9851: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
9852: || l_payments_at_risk);
9853: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
9854: || l_payments_at_risk_br);
9855: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
9849: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
9850: || l_cum_payments_at_risk );
9851: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
9852: || l_payments_at_risk);
9853: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
9854: || l_payments_at_risk_br);
9855: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
9856: || l_cum_total_on_order );
9857: OE_DEBUG_PUB.ADD(' l_total_on_order = '
9851: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
9852: || l_payments_at_risk);
9853: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
9854: || l_payments_at_risk_br);
9855: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
9856: || l_cum_total_on_order );
9857: OE_DEBUG_PUB.ADD(' l_total_on_order = '
9858: || l_total_on_order );
9859: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
9853: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
9854: || l_payments_at_risk_br);
9855: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
9856: || l_cum_total_on_order );
9857: OE_DEBUG_PUB.ADD(' l_total_on_order = '
9858: || l_total_on_order );
9859: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
9860: || l_uninvoiced_hdr_freight1 );
9861: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
9855: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
9856: || l_cum_total_on_order );
9857: OE_DEBUG_PUB.ADD(' l_total_on_order = '
9858: || l_total_on_order );
9859: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
9860: || l_uninvoiced_hdr_freight1 );
9861: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
9862: || l_uninvoiced_hdr_freight2 );
9863: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
9857: OE_DEBUG_PUB.ADD(' l_total_on_order = '
9858: || l_total_on_order );
9859: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
9860: || l_uninvoiced_hdr_freight1 );
9861: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
9862: || l_uninvoiced_hdr_freight2 );
9863: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
9864: || l_cum_total_commitment );
9865: OE_DEBUG_PUB.ADD(' l_total_commitment = '
9859: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
9860: || l_uninvoiced_hdr_freight1 );
9861: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
9862: || l_uninvoiced_hdr_freight2 );
9863: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
9864: || l_cum_total_commitment );
9865: OE_DEBUG_PUB.ADD(' l_total_commitment = '
9866: || l_total_commitment );
9867: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
9861: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
9862: || l_uninvoiced_hdr_freight2 );
9863: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
9864: || l_cum_total_commitment );
9865: OE_DEBUG_PUB.ADD(' l_total_commitment = '
9866: || l_total_commitment );
9867: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
9868: || l_usage_total_exposure );
9869:
9863: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
9864: || l_cum_total_commitment );
9865: OE_DEBUG_PUB.ADD(' l_total_commitment = '
9866: || l_total_commitment );
9867: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
9868: || l_usage_total_exposure );
9869:
9870: -- no hold
9871: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
9867: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
9868: || l_usage_total_exposure );
9869:
9870: -- no hold
9871: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
9872: || l_cum_total_no_hold );
9873: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
9874: || l_total_no_hold );
9875: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
9869:
9870: -- no hold
9871: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
9872: || l_cum_total_no_hold );
9873: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
9874: || l_total_no_hold );
9875: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
9876: || l_no_hold_hdr_freight1 );
9877: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
9871: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
9872: || l_cum_total_no_hold );
9873: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
9874: || l_total_no_hold );
9875: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
9876: || l_no_hold_hdr_freight1 );
9877: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
9878: || l_no_hold_hdr_freight2);
9879: -- external
9873: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
9874: || l_total_no_hold );
9875: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
9876: || l_no_hold_hdr_freight1 );
9877: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
9878: || l_no_hold_hdr_freight2);
9879: -- external
9880: OE_DEBUG_PUB.ADD(' l_external_exposure = '
9881: || l_external_exposure );
9876: || l_no_hold_hdr_freight1 );
9877: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
9878: || l_no_hold_hdr_freight2);
9879: -- external
9880: OE_DEBUG_PUB.ADD(' l_external_exposure = '
9881: || l_external_exposure );
9882: OE_DEBUG_PUB.ADD(' l_cum_external_exposure = '
9883: || l_cum_external_exposure );
9884: -- details
9878: || l_no_hold_hdr_freight2);
9879: -- external
9880: OE_DEBUG_PUB.ADD(' l_external_exposure = '
9881: || l_external_exposure );
9882: OE_DEBUG_PUB.ADD(' l_cum_external_exposure = '
9883: || l_cum_external_exposure );
9884: -- details
9885: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
9886: || l_cum_total_on_hold );
9881: || l_external_exposure );
9882: OE_DEBUG_PUB.ADD(' l_cum_external_exposure = '
9883: || l_cum_external_exposure );
9884: -- details
9885: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
9886: || l_cum_total_on_hold );
9887: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
9888: || l_total_on_hold );
9889: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
9883: || l_cum_external_exposure );
9884: -- details
9885: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
9886: || l_cum_total_on_hold );
9887: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
9888: || l_total_on_hold );
9889: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
9890: || l_hold_hdr_freight1 );
9891: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
9885: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
9886: || l_cum_total_on_hold );
9887: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
9888: || l_total_on_hold );
9889: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
9890: || l_hold_hdr_freight1 );
9891: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
9892: || l_hold_hdr_freight2);
9893: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
9887: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
9888: || l_total_on_hold );
9889: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
9890: || l_hold_hdr_freight1 );
9891: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
9892: || l_hold_hdr_freight2);
9893: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
9894: || l_cum_order_amount );
9895: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
9889: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
9890: || l_hold_hdr_freight1 );
9891: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
9892: || l_hold_hdr_freight2);
9893: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
9894: || l_cum_order_amount );
9895: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
9896: || l_cum_order_hold_amount );
9897: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
9891: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
9892: || l_hold_hdr_freight2);
9893: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
9894: || l_cum_order_amount );
9895: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
9896: || l_cum_order_hold_amount );
9897: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
9898: || l_cum_ar_amount);
9899:
9893: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
9894: || l_cum_order_amount );
9895: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
9896: || l_cum_order_hold_amount );
9897: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
9898: || l_cum_ar_amount);
9899:
9900: OE_DEBUG_PUB.ADD(' Call currency conversion for exposure ' );
9901: OE_DEBUG_PUB.Add(' GL_CURRENCY = '||
9896: || l_cum_order_hold_amount );
9897: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
9898: || l_cum_ar_amount);
9899:
9900: OE_DEBUG_PUB.ADD(' Call currency conversion for exposure ' );
9901: OE_DEBUG_PUB.Add(' GL_CURRENCY = '||
9902: OE_Credit_Engine_GRP.GL_currency );
9903: END IF;
9904:
9897: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
9898: || l_cum_ar_amount);
9899:
9900: OE_DEBUG_PUB.ADD(' Call currency conversion for exposure ' );
9901: OE_DEBUG_PUB.Add(' GL_CURRENCY = '||
9902: OE_Credit_Engine_GRP.GL_currency );
9903: END IF;
9904:
9905:
9904:
9905:
9906: IF OE_Credit_Engine_GRP.GL_currency IS NULL
9907: THEN
9908: OE_DEBUG_PUB.ADD(' Call GET_GL_currency ');
9909:
9910: OE_Credit_Engine_GRP.GL_currency :=
9911: OE_CREDIT_CHECK_UTIL.GET_GL_currency ;
9912:
9909:
9910: OE_Credit_Engine_GRP.GL_currency :=
9911: OE_CREDIT_CHECK_UTIL.GET_GL_currency ;
9912:
9913: OE_DEBUG_PUB.ADD(' GL_CURRENCY after = '
9914: || OE_Credit_Engine_GRP.GL_currency );
9915:
9916: END IF;
9917:
9931: IF p_need_exposure_details = 'Y'
9932: THEN
9933: IF G_debug_flag = 'Y'
9934: THEN
9935: oe_debug_pub.add( ' Into p_need_exposure_details ');
9936: END IF;
9937:
9938: l_limit_cum_order_amount :=
9939: OE_CREDIT_CHECK_UTIL.CONVERT_CURRENCY_AMOUNT
9981: END IF;
9982:
9983: IF G_debug_flag = 'Y'
9984: THEN
9985: OE_DEBUG_PUB.ADD(' l_limit_total_exposure = '
9986: || l_limit_total_exposure );
9987: OE_DEBUG_PUB.ADD(' l_total_exposure = '|| l_total_exposure );
9988: END IF;
9989:
9983: IF G_debug_flag = 'Y'
9984: THEN
9985: OE_DEBUG_PUB.ADD(' l_limit_total_exposure = '
9986: || l_limit_total_exposure );
9987: OE_DEBUG_PUB.ADD(' l_total_exposure = '|| l_total_exposure );
9988: END IF;
9989:
9990: l_limit_total_exposure := 0;
9991: l_usage_total_exposure := 0;
10029: l_limit_cum_ar_amount := 0 ;
10030:
10031: IF G_debug_flag = 'Y'
10032: THEN
10033: OE_DEBUG_PUB.Add('--------------------------');
10034: OE_DEBUG_PUB.Add(' ');
10035: END IF;
10036:
10037: END LOOP ; -- CURRENCY LOOP
10030:
10031: IF G_debug_flag = 'Y'
10032: THEN
10033: OE_DEBUG_PUB.Add('--------------------------');
10034: OE_DEBUG_PUB.Add(' ');
10035: END IF;
10036:
10037: END LOOP ; -- CURRENCY LOOP
10038:
10038:
10039: ELSE -- SITE
10040: IF G_debug_flag = 'Y'
10041: THEN
10042: OE_DEBUG_PUB.Add('Get_order_Exposure SITE : ',1);
10043: END IF;
10044:
10045: l_current_usage_cur := NULL ;
10046:
10049: l_current_usage_cur := NULL ;
10050:
10051: IF G_debug_flag = 'Y'
10052: THEN
10053: OE_DEBUG_PUB.ADD(' ');
10054: OE_DEBUG_PUB.ADD('############################### ');
10055: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
10056: OE_DEBUG_PUB.ADD('############################### ');
10057: OE_DEBUG_PUB.ADD(' ');
10050:
10051: IF G_debug_flag = 'Y'
10052: THEN
10053: OE_DEBUG_PUB.ADD(' ');
10054: OE_DEBUG_PUB.ADD('############################### ');
10055: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
10056: OE_DEBUG_PUB.ADD('############################### ');
10057: OE_DEBUG_PUB.ADD(' ');
10058: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10051: IF G_debug_flag = 'Y'
10052: THEN
10053: OE_DEBUG_PUB.ADD(' ');
10054: OE_DEBUG_PUB.ADD('############################### ');
10055: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
10056: OE_DEBUG_PUB.ADD('############################### ');
10057: OE_DEBUG_PUB.ADD(' ');
10058: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10059: l_current_usage_cur );
10052: THEN
10053: OE_DEBUG_PUB.ADD(' ');
10054: OE_DEBUG_PUB.ADD('############################### ');
10055: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
10056: OE_DEBUG_PUB.ADD('############################### ');
10057: OE_DEBUG_PUB.ADD(' ');
10058: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10059: l_current_usage_cur );
10060: OE_DEBUG_PUB.ADD(' ');
10053: OE_DEBUG_PUB.ADD(' ');
10054: OE_DEBUG_PUB.ADD('############################### ');
10055: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
10056: OE_DEBUG_PUB.ADD('############################### ');
10057: OE_DEBUG_PUB.ADD(' ');
10058: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10059: l_current_usage_cur );
10060: OE_DEBUG_PUB.ADD(' ');
10061: OE_DEBUG_PUB.ADD('.');
10054: OE_DEBUG_PUB.ADD('############################### ');
10055: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
10056: OE_DEBUG_PUB.ADD('############################### ');
10057: OE_DEBUG_PUB.ADD(' ');
10058: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10059: l_current_usage_cur );
10060: OE_DEBUG_PUB.ADD(' ');
10061: OE_DEBUG_PUB.ADD('.');
10062: END IF;
10056: OE_DEBUG_PUB.ADD('############################### ');
10057: OE_DEBUG_PUB.ADD(' ');
10058: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10059: l_current_usage_cur );
10060: OE_DEBUG_PUB.ADD(' ');
10061: OE_DEBUG_PUB.ADD('.');
10062: END IF;
10063:
10064: l_current_usage_cur := p_usage_curr(i).usage_curr_code ;
10057: OE_DEBUG_PUB.ADD(' ');
10058: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10059: l_current_usage_cur );
10060: OE_DEBUG_PUB.ADD(' ');
10061: OE_DEBUG_PUB.ADD('.');
10062: END IF;
10063:
10064: l_current_usage_cur := p_usage_curr(i).usage_curr_code ;
10065:
10065:
10066: -- get site external exposure
10067: IF l_incl_external_exposure_flag = 'Y'
10068: THEN
10069: OE_DEBUG_PUB.Add(' Get site external exposure balance ');
10070:
10071: OPEN site_external_exposure_csr (p_usage_curr(i).usage_curr_code);
10072: FETCH site_external_exposure_csr INTO l_external_exposure;
10073: IF site_external_exposure_csr%NOTFOUND
10071: OPEN site_external_exposure_csr (p_usage_curr(i).usage_curr_code);
10072: FETCH site_external_exposure_csr INTO l_external_exposure;
10073: IF site_external_exposure_csr%NOTFOUND
10074: THEN
10075: OE_DEBUG_PUB.Add(' No external exposure balance found');
10076: l_external_exposure := 0;
10077: END IF;
10078: CLOSE site_external_exposure_csr;
10079: END IF;
10083: IF l_open_ar_balance_flag = 'Y'
10084: THEN
10085: IF G_debug_flag = 'Y'
10086: THEN
10087: OE_DEBUG_PUB.Add(' Get site_ar_balance ');
10088: END IF;
10089:
10090: OPEN site_ar_balance (p_usage_curr(i).usage_curr_code);
10091: FETCH site_ar_balance INTO l_total_from_ar;
10091: FETCH site_ar_balance INTO l_total_from_ar;
10092: IF site_ar_balance%NOTFOUND
10093: THEN
10094: l_total_from_ar := 0 ;
10095: OE_DEBUG_PUB.Add(' No site_ar_balance found ');
10096: END IF;
10097:
10098: CLOSE site_ar_balance;
10099: END IF;
10101: IF l_include_risk_flag = 'Y'
10102: THEN
10103: IF G_debug_flag = 'Y'
10104: THEN
10105: OE_DEBUG_PUB.Add(' Get site_pay_risk ');
10106: END IF;
10107: OPEN site_pay_risk (p_usage_curr(i).usage_curr_code);
10108: FETCH site_pay_risk INTO l_payments_at_risk;
10109: IF site_pay_risk%NOTFOUND
10108: FETCH site_pay_risk INTO l_payments_at_risk;
10109: IF site_pay_risk%NOTFOUND
10110: THEN
10111: l_payments_at_risk := 0 ;
10112: OE_DEBUG_PUB.Add(' No site_pay_risk found ');
10113: END IF;
10114:
10115: CLOSE site_pay_risk;
10116: END IF;
10124: END IF;
10125:
10126: IF G_debug_flag = 'Y'
10127: THEN
10128: OE_DEBUG_PUB.Add(' l_drawee_site_use_id ==> '|| l_drawee_site_use_id );
10129: END IF;
10130:
10131: IF l_drawee_site_use_id is NOT NULL
10132: THEN
10133: IF l_open_ar_balance_flag = 'Y'
10134: THEN
10135: IF G_debug_flag = 'Y'
10136: THEN
10137: OE_DEBUG_PUB.Add(' Get site_br_balance ');
10138: END IF;
10139: OPEN site_br_balance (p_usage_curr(i).usage_curr_code);
10140: FETCH site_br_balance INTO l_total_from_br;
10141: IF site_br_balance%NOTFOUND
10140: FETCH site_br_balance INTO l_total_from_br;
10141: IF site_br_balance%NOTFOUND
10142: THEN
10143: l_total_from_br := 0 ;
10144: OE_DEBUG_PUB.Add(' No site_br_balance found ');
10145: END IF;
10146:
10147: CLOSE site_br_balance;
10148: END IF;
10150: IF l_include_risk_flag = 'Y'
10151: THEN
10152: IF G_debug_flag = 'Y'
10153: THEN
10154: OE_DEBUG_PUB.Add(' Get site_br_pay_risk ');
10155: END IF;
10156: OPEN site_br_pay_risk (p_usage_curr(i).usage_curr_code);
10157: FETCH site_br_pay_risk INTO l_payments_at_risk_br;
10158: IF site_br_pay_risk%NOTFOUND
10157: FETCH site_br_pay_risk INTO l_payments_at_risk_br;
10158: IF site_br_pay_risk%NOTFOUND
10159: THEN
10160: l_payments_at_risk_br := 0 ;
10161: OE_DEBUG_PUB.Add(' No site_pay_risk found ');
10162: END IF;
10163:
10164: CLOSE site_br_pay_risk;
10165: END IF; --ar
10165: END IF; --ar
10166: ELSE
10167: IF G_debug_flag = 'Y'
10168: THEN
10169: OE_DEBUG_PUB.Add(' No site BR required ');
10170: END IF;
10171: END IF;
10172: --vto2
10173: IF l_uninvoiced_orders_flag = 'Y'
10174: THEN
10175: BEGIN
10176: IF G_debug_flag = 'Y'
10177: THEN
10178: OE_DEBUG_PUB.Add(' Begin OM Exposure for site ');
10179: END IF;
10180:
10181: IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
10182: IF G_debug_flag = 'Y'
10180:
10181: IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
10182: IF G_debug_flag = 'Y'
10183: THEN
10184: OE_DEBUG_PUB.Add(' site_commitments ');
10185: END IF;
10186:
10187: OPEN site_commitment_total (p_usage_curr(i).usage_curr_code);
10188: FETCH site_commitment_total INTO l_total_commitment ;
10188: FETCH site_commitment_total INTO l_total_commitment ;
10189: IF site_commitment_total%NOTFOUND
10190: THEN
10191: l_total_commitment := 0 ;
10192: OE_DEBUG_PUB.Add('No site commitments found ');
10193: END IF;
10194:
10195: CLOSE site_commitment_total ;
10196: END IF;
10204: -----returns are not included
10205:
10206: IF G_debug_flag = 'Y'
10207: THEN
10208: OE_DEBUG_PUB.Add(' Get site_uninvoiced_orders,site_uninvoiced_orders ');
10209: END IF;
10210:
10211: OPEN site_uninvoiced_orders(p_usage_curr(i).usage_curr_code);
10212: FETCH site_uninvoiced_orders INTO l_total_on_order;
10213:
10214: IF site_uninvoiced_orders%NOTFOUND
10215: THEN
10216: l_total_on_order := 0 ;
10217: OE_DEBUG_PUB.Add(' No site_uninvoiced_orders found ');
10218: END IF;
10219:
10220: CLOSE site_uninvoiced_orders;
10221:
10223: -----returns are included
10224:
10225: IF G_debug_flag = 'Y'
10226: THEN
10227: OE_DEBUG_PUB.Add(' open site_uninvoiced_orders_ret ');
10228: END IF;
10229:
10230: OPEN site_uninvoiced_orders_ret(p_usage_curr(i).usage_curr_code);
10231: FETCH site_uninvoiced_orders_ret INTO l_total_on_order;
10232:
10233: IF site_uninvoiced_orders_ret%NOTFOUND
10234: THEN
10235: l_total_on_order := 0 ;
10236: OE_DEBUG_PUB.Add(' No site_uninvoiced_orders found ');
10237: END IF;
10238:
10239: CLOSE site_uninvoiced_orders_ret;
10240:
10248: THEN
10249: -----returns are not included
10250: IF G_debug_flag = 'Y'
10251: THEN
10252: OE_DEBUG_PUB.Add(' Get site_uninv_hdr_freight1 ');
10253: END IF;
10254:
10255: OPEN site_uninv_hdr_freight1(p_usage_curr(i).usage_curr_code);
10256: FETCH site_uninv_hdr_freight1 INTO l_uninvoiced_hdr_freight1;
10257:
10258: IF site_uninv_hdr_freight1%NOTFOUND
10259: THEN
10260: l_uninvoiced_hdr_freight1 := 0 ;
10261: OE_DEBUG_PUB.Add(' No site_uninv_hdr_freight1 found ');
10262: END IF;
10263:
10264: CLOSE site_uninv_hdr_freight1;
10265:
10264: CLOSE site_uninv_hdr_freight1;
10265:
10266: IF G_debug_flag = 'Y'
10267: THEN
10268: OE_DEBUG_PUB.Add(' Get site_uninv_hdr_freight2 ');
10269: END IF;
10270:
10271: OPEN site_uninv_hdr_freight2(p_usage_curr(i).usage_curr_code);
10272:
10274:
10275: IF site_uninv_hdr_freight2%NOTFOUND
10276: THEN
10277: l_uninvoiced_hdr_freight2 := 0 ;
10278: OE_DEBUG_PUB.Add(' No site_uninv_hdr_freight2 found ');
10279: END IF;
10280:
10281: CLOSE site_uninv_hdr_freight2;
10282:
10283: ELSE
10284: -----returns are included
10285: IF G_debug_flag = 'Y'
10286: THEN
10287: OE_DEBUG_PUB.Add(' Get site_uninv_hdr_freight1_ret ');
10288: END IF;
10289:
10290: OPEN site_uninv_hdr_freight1_ret(p_usage_curr(i).usage_curr_code);
10291: FETCH site_uninv_hdr_freight1_ret INTO l_uninvoiced_hdr_freight1;
10292:
10293: IF site_uninv_hdr_freight1_ret%NOTFOUND
10294: THEN
10295: l_uninvoiced_hdr_freight1 := 0 ;
10296: OE_DEBUG_PUB.Add(' No site_uninv_hdr_freight1 found ');
10297: END IF;
10298:
10299: CLOSE site_uninv_hdr_freight1_ret;
10300:
10299: CLOSE site_uninv_hdr_freight1_ret;
10300:
10301: IF G_debug_flag = 'Y'
10302: THEN
10303: OE_DEBUG_PUB.Add(' Get site_uninv_hdr_freight2_ret ');
10304: END IF;
10305:
10306: OPEN site_uninv_hdr_freight2_ret(p_usage_curr(i).usage_curr_code);
10307:
10309:
10310: IF site_uninv_hdr_freight2_ret%NOTFOUND
10311: THEN
10312: l_uninvoiced_hdr_freight2 := 0 ;
10313: OE_DEBUG_PUB.Add(' No site_uninv_hdr_freight2_ret found ');
10314: END IF;
10315:
10316: CLOSE site_uninv_hdr_freight2_ret;
10317:
10326: -----returns are not included
10327:
10328: IF G_debug_flag = 'Y'
10329: THEN
10330: OE_DEBUG_PUB.Add(' Get site_orders_not_on_hold ');
10331: END IF;
10332:
10333: OPEN site_orders_not_on_hold (p_usage_curr(i).usage_curr_code);
10334:
10336:
10337: IF site_orders_not_on_hold%NOTFOUND
10338: THEN
10339: l_total_no_hold := 0 ;
10340: OE_DEBUG_PUB.Add(' No site orders not on hold found ');
10341: END IF;
10342:
10343: CLOSE site_orders_not_on_hold;
10344: -- Get orders on hold amount if need_exposure_details is enabled
10345: IF l_need_exposure_details = 'Y'
10346: THEN
10347: IF G_debug_flag = 'Y'
10348: THEN
10349: OE_DEBUG_PUB.Add(' Get site_orders_on_hold ');
10350: END IF;
10351:
10352: OPEN site_orders_on_hold (p_usage_curr(i).usage_curr_code);
10353: FETCH site_orders_on_hold INTO l_total_on_hold;
10354:
10355: IF site_orders_on_hold%NOTFOUND
10356: THEN
10357: l_total_on_hold := 0 ;
10358: OE_DEBUG_PUB.Add(' No site orders on hold found ');
10359: END IF;
10360:
10361: CLOSE site_orders_on_hold;
10362: END IF;
10365: -----returns are included
10366:
10367: IF G_debug_flag = 'Y'
10368: THEN
10369: OE_DEBUG_PUB.Add(' Get site_orders_not_on_hold_ret ');
10370: END IF;
10371:
10372: OPEN site_orders_not_on_hold_ret (p_usage_curr(i).usage_curr_code);
10373:
10375:
10376: IF site_orders_not_on_hold_ret%NOTFOUND
10377: THEN
10378: l_total_no_hold := 0 ;
10379: OE_DEBUG_PUB.Add(' No site orders not on hold found ');
10380: END IF;
10381:
10382: CLOSE site_orders_not_on_hold_ret;
10383:
10385: IF l_need_exposure_details = 'Y'
10386: THEN
10387: IF G_debug_flag = 'Y'
10388: THEN
10389: OE_DEBUG_PUB.Add(' Get site_orders_on_hold_ret ');
10390: END IF;
10391:
10392: OPEN site_orders_on_hold_ret(p_usage_curr(i).usage_curr_code);
10393: FETCH site_orders_on_hold_ret INTO l_total_on_hold;
10394:
10395: IF site_orders_on_hold_ret%NOTFOUND
10396: THEN
10397: l_total_on_hold := 0 ;
10398: OE_DEBUG_PUB.Add(' No site orders on hold found ');
10399: END IF;
10400:
10401: CLOSE site_orders_on_hold_ret;
10402: END IF;
10415: THEN
10416: -----returns are not included
10417: IF G_debug_flag = 'Y'
10418: THEN
10419: OE_DEBUG_PUB.Add(' Get site_no_hold_hdr_freight1 ');
10420: END IF;
10421:
10422: OPEN site_no_hold_hdr_freight1(p_usage_curr(i).usage_curr_code);
10423: FETCH site_no_hold_hdr_freight1 INTO l_no_hold_hdr_freight1;
10424:
10425: IF site_no_hold_hdr_freight1%NOTFOUND
10426: THEN
10427: l_no_hold_hdr_freight1 := 0 ;
10428: OE_DEBUG_PUB.Add(' No site_no_hold_hdr_freight1 found ');
10429: END IF;
10430:
10431: CLOSE site_no_hold_hdr_freight1;
10432:
10431: CLOSE site_no_hold_hdr_freight1;
10432:
10433: IF G_debug_flag = 'Y'
10434: THEN
10435: OE_DEBUG_PUB.Add(' Get site_no_hold_hdr_freight2 ');
10436: END IF;
10437:
10438: OPEN site_no_hold_hdr_freight2(p_usage_curr(i).usage_curr_code);
10439:
10441:
10442: IF site_no_hold_hdr_freight2%NOTFOUND
10443: THEN
10444: l_no_hold_hdr_freight2 := 0 ;
10445: OE_DEBUG_PUB.Add(' No site_no_hold_hdr_freight2 found ');
10446: END IF;
10447:
10448: CLOSE site_no_hold_hdr_freight2;
10449:
10451: IF l_need_exposure_details = 'Y'
10452: THEN
10453: IF G_debug_flag = 'Y'
10454: THEN
10455: OE_DEBUG_PUB.Add(' Get site_hold_hdr_freight1 ');
10456: END IF;
10457:
10458: OPEN site_hold_hdr_freight1(p_usage_curr(i).usage_curr_code);
10459: FETCH site_hold_hdr_freight1 INTO l_hold_hdr_freight1;
10460:
10461: IF site_hold_hdr_freight1%NOTFOUND
10462: THEN
10463: l_hold_hdr_freight1 := 0 ;
10464: OE_DEBUG_PUB.Add(' No site_hold_hdr_freight1 found ');
10465: END IF;
10466:
10467: CLOSE site_hold_hdr_freight1;
10468:
10467: CLOSE site_hold_hdr_freight1;
10468:
10469: IF G_debug_flag = 'Y'
10470: THEN
10471: OE_DEBUG_PUB.Add(' Get site_hold_hdr_freight2 ');
10472: END IF;
10473:
10474: OPEN site_hold_hdr_freight2(p_usage_curr(i).usage_curr_code);
10475: FETCH site_hold_hdr_freight2 INTO l_hold_hdr_freight2;
10476:
10477: IF site_hold_hdr_freight2%NOTFOUND
10478: THEN
10479: l_hold_hdr_freight2 := 0 ;
10480: OE_DEBUG_PUB.Add(' No site_hold_hdr_freight2 found ');
10481: END IF;
10482: CLOSE site_hold_hdr_freight2;
10483: END IF; -- details freight on hold
10484: ELSE
10484: ELSE
10485: -----returns are included
10486: IF G_debug_flag = 'Y'
10487: THEN
10488: OE_DEBUG_PUB.Add(' Get site_no_hold_hdr_freight1_ret ');
10489: END IF;
10490:
10491: OPEN site_no_hold_hdr_freight1_ret(p_usage_curr(i).usage_curr_code);
10492: FETCH site_no_hold_hdr_freight1_ret INTO l_no_hold_hdr_freight1;
10493:
10494: IF site_no_hold_hdr_freight1_ret%NOTFOUND
10495: THEN
10496: l_no_hold_hdr_freight1 := 0 ;
10497: OE_DEBUG_PUB.Add(' No site_no_hold_hdr_freight1 found ');
10498: END IF;
10499:
10500: CLOSE site_no_hold_hdr_freight1_ret;
10501:
10500: CLOSE site_no_hold_hdr_freight1_ret;
10501:
10502: IF G_debug_flag = 'Y'
10503: THEN
10504: OE_DEBUG_PUB.Add(' Get site_no_hold_hdr_freight2_ret ');
10505: END IF;
10506:
10507: OPEN site_no_hold_hdr_freight2_ret(p_usage_curr(i).usage_curr_code);
10508:
10510:
10511: IF site_no_hold_hdr_freight2_ret%NOTFOUND
10512: THEN
10513: l_no_hold_hdr_freight2 := 0 ;
10514: OE_DEBUG_PUB.Add(' No site_no_hold_hdr_freight2_ret found ');
10515: END IF;
10516:
10517: CLOSE site_no_hold_hdr_freight2_ret;
10518:
10520: IF l_need_exposure_details = 'Y'
10521: THEN
10522: IF G_debug_flag = 'Y'
10523: THEN
10524: OE_DEBUG_PUB.Add(' Get site_hold_hdr_freight1_ret ');
10525: END IF;
10526:
10527: OPEN site_hold_hdr_freight1_ret(p_usage_curr(i).usage_curr_code);
10528: FETCH site_hold_hdr_freight1_ret INTO l_hold_hdr_freight1;
10529:
10530: IF site_hold_hdr_freight1_ret%NOTFOUND
10531: THEN
10532: l_hold_hdr_freight1 := 0 ;
10533: OE_DEBUG_PUB.Add(' No site_hold_hdr_freight1_ret found ');
10534: END IF;
10535:
10536: CLOSE site_hold_hdr_freight1_ret;
10537:
10536: CLOSE site_hold_hdr_freight1_ret;
10537:
10538: IF G_debug_flag = 'Y'
10539: THEN
10540: OE_DEBUG_PUB.Add(' Get site_hold_hdr_freight2_ret ');
10541: END IF;
10542:
10543: OPEN site_hold_hdr_freight2_ret(p_usage_curr(i).usage_curr_code);
10544: FETCH site_hold_hdr_freight2_ret INTO l_hold_hdr_freight2;
10545:
10546: IF site_hold_hdr_freight2_ret%NOTFOUND
10547: THEN
10548: l_hold_hdr_freight2 := 0 ;
10549: OE_DEBUG_PUB.Add(' No site_hold_hdr_freight2_ret found ');
10550: END IF;
10551: CLOSE site_hold_hdr_freight2_ret;
10552: END IF; -- details freight on hold
10553: END IF; -----end of checking if returns are included
10556: END;
10557: ELSE
10558: IF G_debug_flag = 'Y'
10559: THEN
10560: OE_DEBUG_PUB.Add(' No OM Exposure calculation for site ',1);
10561: END IF;
10562: END IF;
10563:
10564: l_cum_total_from_ar := l_cum_total_from_ar + NVL(l_total_from_ar,0) +
10617:
10618: IF G_debug_flag = 'Y'
10619: THEN
10620:
10621: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10622: || l_usage_total_exposure );
10623: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
10624: || l_cum_total_from_ar );
10625: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
10619: THEN
10620:
10621: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10622: || l_usage_total_exposure );
10623: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
10624: || l_cum_total_from_ar );
10625: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
10626: || l_total_from_ar);
10627: OE_DEBUG_PUB.ADD(' l_total_from_br = '
10621: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10622: || l_usage_total_exposure );
10623: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
10624: || l_cum_total_from_ar );
10625: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
10626: || l_total_from_ar);
10627: OE_DEBUG_PUB.ADD(' l_total_from_br = '
10628: || l_total_from_br);
10629: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
10623: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
10624: || l_cum_total_from_ar );
10625: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
10626: || l_total_from_ar);
10627: OE_DEBUG_PUB.ADD(' l_total_from_br = '
10628: || l_total_from_br);
10629: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
10630: || l_cum_payments_at_risk );
10631: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
10625: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
10626: || l_total_from_ar);
10627: OE_DEBUG_PUB.ADD(' l_total_from_br = '
10628: || l_total_from_br);
10629: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
10630: || l_cum_payments_at_risk );
10631: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
10632: || l_payments_at_risk);
10633: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
10627: OE_DEBUG_PUB.ADD(' l_total_from_br = '
10628: || l_total_from_br);
10629: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
10630: || l_cum_payments_at_risk );
10631: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
10632: || l_payments_at_risk);
10633: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
10634: || l_payments_at_risk_br);
10635: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
10629: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
10630: || l_cum_payments_at_risk );
10631: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
10632: || l_payments_at_risk);
10633: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
10634: || l_payments_at_risk_br);
10635: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
10636: || l_cum_total_on_order );
10637: OE_DEBUG_PUB.ADD(' l_total_on_order = '
10631: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
10632: || l_payments_at_risk);
10633: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
10634: || l_payments_at_risk_br);
10635: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
10636: || l_cum_total_on_order );
10637: OE_DEBUG_PUB.ADD(' l_total_on_order = '
10638: || l_total_on_order );
10639: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
10633: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
10634: || l_payments_at_risk_br);
10635: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
10636: || l_cum_total_on_order );
10637: OE_DEBUG_PUB.ADD(' l_total_on_order = '
10638: || l_total_on_order );
10639: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
10640: || l_uninvoiced_hdr_freight1 );
10641: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
10635: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
10636: || l_cum_total_on_order );
10637: OE_DEBUG_PUB.ADD(' l_total_on_order = '
10638: || l_total_on_order );
10639: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
10640: || l_uninvoiced_hdr_freight1 );
10641: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
10642: || l_uninvoiced_hdr_freight2 );
10643: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
10637: OE_DEBUG_PUB.ADD(' l_total_on_order = '
10638: || l_total_on_order );
10639: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
10640: || l_uninvoiced_hdr_freight1 );
10641: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
10642: || l_uninvoiced_hdr_freight2 );
10643: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
10644: || l_cum_total_commitment );
10645: OE_DEBUG_PUB.ADD(' l_total_commitment = '
10639: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
10640: || l_uninvoiced_hdr_freight1 );
10641: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
10642: || l_uninvoiced_hdr_freight2 );
10643: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
10644: || l_cum_total_commitment );
10645: OE_DEBUG_PUB.ADD(' l_total_commitment = '
10646: || l_total_commitment );
10647: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10641: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
10642: || l_uninvoiced_hdr_freight2 );
10643: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
10644: || l_cum_total_commitment );
10645: OE_DEBUG_PUB.ADD(' l_total_commitment = '
10646: || l_total_commitment );
10647: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10648: || l_usage_total_exposure );
10649:
10643: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
10644: || l_cum_total_commitment );
10645: OE_DEBUG_PUB.ADD(' l_total_commitment = '
10646: || l_total_commitment );
10647: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10648: || l_usage_total_exposure );
10649:
10650: -- details
10651: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
10647: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10648: || l_usage_total_exposure );
10649:
10650: -- details
10651: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
10652: || l_cum_total_on_hold );
10653: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
10654: || l_total_on_hold );
10655: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
10649:
10650: -- details
10651: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
10652: || l_cum_total_on_hold );
10653: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
10654: || l_total_on_hold );
10655: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
10656: || l_hold_hdr_freight1 );
10657: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
10651: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
10652: || l_cum_total_on_hold );
10653: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
10654: || l_total_on_hold );
10655: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
10656: || l_hold_hdr_freight1 );
10657: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
10658: || l_hold_hdr_freight2);
10659: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
10653: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
10654: || l_total_on_hold );
10655: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
10656: || l_hold_hdr_freight1 );
10657: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
10658: || l_hold_hdr_freight2);
10659: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
10660: || l_cum_order_amount );
10661: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
10655: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
10656: || l_hold_hdr_freight1 );
10657: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
10658: || l_hold_hdr_freight2);
10659: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
10660: || l_cum_order_amount );
10661: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
10662: || l_cum_order_hold_amount );
10663: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
10657: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
10658: || l_hold_hdr_freight2);
10659: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
10660: || l_cum_order_amount );
10661: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
10662: || l_cum_order_hold_amount );
10663: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
10664: || l_cum_ar_amount);
10665:
10659: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
10660: || l_cum_order_amount );
10661: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
10662: || l_cum_order_hold_amount );
10663: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
10664: || l_cum_ar_amount);
10665:
10666: -- external
10667: OE_DEBUG_PUB.ADD(' l_external_exposure = '
10663: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
10664: || l_cum_ar_amount);
10665:
10666: -- external
10667: OE_DEBUG_PUB.ADD(' l_external_exposure = '
10668: || l_external_exposure );
10669: OE_DEBUG_PUB.ADD(' l_cum_external_exposure = '
10670: || l_cum_external_exposure );
10671:
10665:
10666: -- external
10667: OE_DEBUG_PUB.ADD(' l_external_exposure = '
10668: || l_external_exposure );
10669: OE_DEBUG_PUB.ADD(' l_cum_external_exposure = '
10670: || l_cum_external_exposure );
10671:
10672: -- no holds
10673: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
10669: OE_DEBUG_PUB.ADD(' l_cum_external_exposure = '
10670: || l_cum_external_exposure );
10671:
10672: -- no holds
10673: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
10674: || l_cum_total_no_hold );
10675: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
10676: || l_total_no_hold );
10677: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
10671:
10672: -- no holds
10673: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
10674: || l_cum_total_no_hold );
10675: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
10676: || l_total_no_hold );
10677: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
10678: || l_no_hold_hdr_freight1 );
10679: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
10673: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
10674: || l_cum_total_no_hold );
10675: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
10676: || l_total_no_hold );
10677: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
10678: || l_no_hold_hdr_freight1 );
10679: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
10680: || l_no_hold_hdr_freight2);
10681:
10675: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
10676: || l_total_no_hold );
10677: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
10678: || l_no_hold_hdr_freight1 );
10679: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
10680: || l_no_hold_hdr_freight2);
10681:
10682: OE_DEBUG_PUB.ADD(' Call currency conversion for exposure ' );
10683:
10678: || l_no_hold_hdr_freight1 );
10679: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
10680: || l_no_hold_hdr_freight2);
10681:
10682: OE_DEBUG_PUB.ADD(' Call currency conversion for exposure ' );
10683:
10684: OE_DEBUG_PUB.Add(' GL_CURRENCY = '||
10685: OE_Credit_Engine_GRP.GL_currency );
10686: END IF;
10680: || l_no_hold_hdr_freight2);
10681:
10682: OE_DEBUG_PUB.ADD(' Call currency conversion for exposure ' );
10683:
10684: OE_DEBUG_PUB.Add(' GL_CURRENCY = '||
10685: OE_Credit_Engine_GRP.GL_currency );
10686: END IF;
10687:
10688: l_limit_total_exposure :=
10699: l_total_exposure := l_total_exposure + NVL(l_limit_total_exposure,0) ;
10700:
10701: IF l_need_exposure_details = 'Y' THEN
10702: IF G_debug_flag = 'Y' THEN
10703: oe_debug_pub.add( ' Into p_need_exposure_details ');
10704: END IF;
10705:
10706: l_limit_cum_order_amount :=
10707: OE_CREDIT_CHECK_UTIL.CONVERT_CURRENCY_AMOUNT
10747: END IF; -- details
10748:
10749: IF G_debug_flag = 'Y'
10750: THEN
10751: OE_DEBUG_PUB.ADD(' l_limit_total_exposure = '
10752: || l_limit_total_exposure );
10753: OE_DEBUG_PUB.ADD(' l_total_exposure = '|| l_total_exposure );
10754: END IF;
10755:
10749: IF G_debug_flag = 'Y'
10750: THEN
10751: OE_DEBUG_PUB.ADD(' l_limit_total_exposure = '
10752: || l_limit_total_exposure );
10753: OE_DEBUG_PUB.ADD(' l_total_exposure = '|| l_total_exposure );
10754: END IF;
10755:
10756:
10757: l_limit_total_exposure := 0 ;
10793: l_limit_cum_ar_amount := 0 ;
10794:
10795: IF G_debug_flag = 'Y'
10796: THEN
10797: OE_DEBUG_PUB.Add(' ');
10798: OE_DEBUG_PUB.Add('=====================================');
10799: OE_DEBUG_PUB.Add(' ');
10800: END IF;
10801:
10794:
10795: IF G_debug_flag = 'Y'
10796: THEN
10797: OE_DEBUG_PUB.Add(' ');
10798: OE_DEBUG_PUB.Add('=====================================');
10799: OE_DEBUG_PUB.Add(' ');
10800: END IF;
10801:
10802: END LOOP; -- currency loop
10795: IF G_debug_flag = 'Y'
10796: THEN
10797: OE_DEBUG_PUB.Add(' ');
10798: OE_DEBUG_PUB.Add('=====================================');
10799: OE_DEBUG_PUB.Add(' ');
10800: END IF;
10801:
10802: END LOOP; -- currency loop
10803:
10800: END IF;
10801:
10802: END LOOP; -- currency loop
10803:
10804: OE_DEBUG_PUB.ADD('** Out NOCOPY of usage currency loop ' );
10805:
10806: END IF;
10807:
10808: l_current_usage_cur := NULL;
10820: NVL(OE_CREDIT_CHECK_UTIL.g_current_order_value,0);
10821:
10822: IF G_debug_flag = 'Y'
10823: THEN
10824: OE_DEBUG_PUB.Add(' Current order value is available already = '
10825: || OE_CREDIT_CHECK_UTIL.g_current_order_value );
10826: OE_DEBUG_PUB.Add(' l_limit_current_order = '||
10827: l_limit_current_order );
10828: END IF;
10822: IF G_debug_flag = 'Y'
10823: THEN
10824: OE_DEBUG_PUB.Add(' Current order value is available already = '
10825: || OE_CREDIT_CHECK_UTIL.g_current_order_value );
10826: OE_DEBUG_PUB.Add(' l_limit_current_order = '||
10827: l_limit_current_order );
10828: END IF;
10829: END ; -- p_header_id
10830:
10830:
10831: ELSE
10832: l_limit_current_order := 0 ;
10833:
10834: OE_DEBUG_PUB.Add(' P_header_id is NULL, No current order check ',1);
10835:
10836: END IF ; -- p_header_id is NULL
10837:
10838: IF G_debug_flag = 'Y'
10836: END IF ; -- p_header_id is NULL
10837:
10838: IF G_debug_flag = 'Y'
10839: THEN
10840: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
10841: OE_DEBUG_PUB.Add(' l_order_amount = '|| l_order_amount);
10842: OE_DEBUG_PUB.Add(' l_order_hold_amount = '|| l_order_hold_amount);
10843: OE_DEBUG_PUB.Add(' l_ar_amount = '|| l_ar_amount);
10844: END IF;
10837:
10838: IF G_debug_flag = 'Y'
10839: THEN
10840: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
10841: OE_DEBUG_PUB.Add(' l_order_amount = '|| l_order_amount);
10842: OE_DEBUG_PUB.Add(' l_order_hold_amount = '|| l_order_hold_amount);
10843: OE_DEBUG_PUB.Add(' l_ar_amount = '|| l_ar_amount);
10844: END IF;
10845:
10838: IF G_debug_flag = 'Y'
10839: THEN
10840: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
10841: OE_DEBUG_PUB.Add(' l_order_amount = '|| l_order_amount);
10842: OE_DEBUG_PUB.Add(' l_order_hold_amount = '|| l_order_hold_amount);
10843: OE_DEBUG_PUB.Add(' l_ar_amount = '|| l_ar_amount);
10844: END IF;
10845:
10846: x_total_exposure := NVL(l_total_exposure,0) + NVL(l_limit_current_order,0) ;
10839: THEN
10840: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
10841: OE_DEBUG_PUB.Add(' l_order_amount = '|| l_order_amount);
10842: OE_DEBUG_PUB.Add(' l_order_hold_amount = '|| l_order_hold_amount);
10843: OE_DEBUG_PUB.Add(' l_ar_amount = '|| l_ar_amount);
10844: END IF;
10845:
10846: x_total_exposure := NVL(l_total_exposure,0) + NVL(l_limit_current_order,0) ;
10847:
10871: );
10872:
10873: IF G_debug_flag = 'Y'
10874: THEN
10875: OE_DEBUG_PUB.Add('after get_exposure_amount ');
10876: OE_DEBUG_PUB.Add(' x_return_status = '|| x_return_status );
10877: OE_DEBUG_PUB.Add(' l_exposure_amount = '|| l_exposure_amount );
10878: OE_DEBUG_PUB.Add(' err cur tbl count = '|| x_conversion_status.COUNT );
10879: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
10872:
10873: IF G_debug_flag = 'Y'
10874: THEN
10875: OE_DEBUG_PUB.Add('after get_exposure_amount ');
10876: OE_DEBUG_PUB.Add(' x_return_status = '|| x_return_status );
10877: OE_DEBUG_PUB.Add(' l_exposure_amount = '|| l_exposure_amount );
10878: OE_DEBUG_PUB.Add(' err cur tbl count = '|| x_conversion_status.COUNT );
10879: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
10880: OE_DEBUG_PUB.Add(' l_total_exposure = '|| l_total_exposure );
10873: IF G_debug_flag = 'Y'
10874: THEN
10875: OE_DEBUG_PUB.Add('after get_exposure_amount ');
10876: OE_DEBUG_PUB.Add(' x_return_status = '|| x_return_status );
10877: OE_DEBUG_PUB.Add(' l_exposure_amount = '|| l_exposure_amount );
10878: OE_DEBUG_PUB.Add(' err cur tbl count = '|| x_conversion_status.COUNT );
10879: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
10880: OE_DEBUG_PUB.Add(' l_total_exposure = '|| l_total_exposure );
10881: OE_DEBUG_PUB.Add(' x_total_exposure = '|| x_total_exposure );
10874: THEN
10875: OE_DEBUG_PUB.Add('after get_exposure_amount ');
10876: OE_DEBUG_PUB.Add(' x_return_status = '|| x_return_status );
10877: OE_DEBUG_PUB.Add(' l_exposure_amount = '|| l_exposure_amount );
10878: OE_DEBUG_PUB.Add(' err cur tbl count = '|| x_conversion_status.COUNT );
10879: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
10880: OE_DEBUG_PUB.Add(' l_total_exposure = '|| l_total_exposure );
10881: OE_DEBUG_PUB.Add(' x_total_exposure = '|| x_total_exposure );
10882: END IF;
10875: OE_DEBUG_PUB.Add('after get_exposure_amount ');
10876: OE_DEBUG_PUB.Add(' x_return_status = '|| x_return_status );
10877: OE_DEBUG_PUB.Add(' l_exposure_amount = '|| l_exposure_amount );
10878: OE_DEBUG_PUB.Add(' err cur tbl count = '|| x_conversion_status.COUNT );
10879: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
10880: OE_DEBUG_PUB.Add(' l_total_exposure = '|| l_total_exposure );
10881: OE_DEBUG_PUB.Add(' x_total_exposure = '|| x_total_exposure );
10882: END IF;
10883:
10876: OE_DEBUG_PUB.Add(' x_return_status = '|| x_return_status );
10877: OE_DEBUG_PUB.Add(' l_exposure_amount = '|| l_exposure_amount );
10878: OE_DEBUG_PUB.Add(' err cur tbl count = '|| x_conversion_status.COUNT );
10879: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
10880: OE_DEBUG_PUB.Add(' l_total_exposure = '|| l_total_exposure );
10881: OE_DEBUG_PUB.Add(' x_total_exposure = '|| x_total_exposure );
10882: END IF;
10883:
10884: x_total_exposure := NVL(x_total_exposure,0) + NVL(l_exposure_amount,0) ;
10877: OE_DEBUG_PUB.Add(' l_exposure_amount = '|| l_exposure_amount );
10878: OE_DEBUG_PUB.Add(' err cur tbl count = '|| x_conversion_status.COUNT );
10879: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
10880: OE_DEBUG_PUB.Add(' l_total_exposure = '|| l_total_exposure );
10881: OE_DEBUG_PUB.Add(' x_total_exposure = '|| x_total_exposure );
10882: END IF;
10883:
10884: x_total_exposure := NVL(x_total_exposure,0) + NVL(l_exposure_amount,0) ;
10885:
10888: FOR f IN 1..x_conversion_status.COUNT
10889: LOOP
10890: IF G_debug_flag = 'Y'
10891: THEN
10892: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
10893: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||x_conversion_status(f).usage_curr_code
10894: ||' and credit limit currency '
10895: ||p_limit_curr_code
10896: ||' is missing for conversion type '
10889: LOOP
10890: IF G_debug_flag = 'Y'
10891: THEN
10892: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
10893: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||x_conversion_status(f).usage_curr_code
10894: ||' and credit limit currency '
10895: ||p_limit_curr_code
10896: ||' is missing for conversion type '
10897: || NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
10894: ||' and credit limit currency '
10895: ||p_limit_curr_code
10896: ||' is missing for conversion type '
10897: || NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
10898: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
10899: END IF;
10900: END LOOP;
10901: ELSIF x_return_status = FND_API.G_RET_STS_ERROR
10902: THEN
10908: --bug# 2714553
10909:
10910: IF G_debug_flag = 'Y'
10911: THEN
10912: OE_DEBUG_PUB.Add(' ');
10913: OE_DEBUG_PUB.Add('XX*****-------------##########-------------------XX' );
10914: OE_DEBUG_PUB.Add(' ');
10915: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
10916: OE_DEBUG_PUB.Add(' ');
10909:
10910: IF G_debug_flag = 'Y'
10911: THEN
10912: OE_DEBUG_PUB.Add(' ');
10913: OE_DEBUG_PUB.Add('XX*****-------------##########-------------------XX' );
10914: OE_DEBUG_PUB.Add(' ');
10915: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
10916: OE_DEBUG_PUB.Add(' ');
10917: OE_DEBUG_PUB.Add('XX-------------- ##########------------------------XX' );
10910: IF G_debug_flag = 'Y'
10911: THEN
10912: OE_DEBUG_PUB.Add(' ');
10913: OE_DEBUG_PUB.Add('XX*****-------------##########-------------------XX' );
10914: OE_DEBUG_PUB.Add(' ');
10915: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
10916: OE_DEBUG_PUB.Add(' ');
10917: OE_DEBUG_PUB.Add('XX-------------- ##########------------------------XX' );
10918: OE_DEBUG_PUB.Add(' ');
10911: THEN
10912: OE_DEBUG_PUB.Add(' ');
10913: OE_DEBUG_PUB.Add('XX*****-------------##########-------------------XX' );
10914: OE_DEBUG_PUB.Add(' ');
10915: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
10916: OE_DEBUG_PUB.Add(' ');
10917: OE_DEBUG_PUB.Add('XX-------------- ##########------------------------XX' );
10918: OE_DEBUG_PUB.Add(' ');
10919: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_order_exposure',1);
10912: OE_DEBUG_PUB.Add(' ');
10913: OE_DEBUG_PUB.Add('XX*****-------------##########-------------------XX' );
10914: OE_DEBUG_PUB.Add(' ');
10915: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
10916: OE_DEBUG_PUB.Add(' ');
10917: OE_DEBUG_PUB.Add('XX-------------- ##########------------------------XX' );
10918: OE_DEBUG_PUB.Add(' ');
10919: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_order_exposure',1);
10920: END IF;
10913: OE_DEBUG_PUB.Add('XX*****-------------##########-------------------XX' );
10914: OE_DEBUG_PUB.Add(' ');
10915: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
10916: OE_DEBUG_PUB.Add(' ');
10917: OE_DEBUG_PUB.Add('XX-------------- ##########------------------------XX' );
10918: OE_DEBUG_PUB.Add(' ');
10919: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_order_exposure',1);
10920: END IF;
10921:
10914: OE_DEBUG_PUB.Add(' ');
10915: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
10916: OE_DEBUG_PUB.Add(' ');
10917: OE_DEBUG_PUB.Add('XX-------------- ##########------------------------XX' );
10918: OE_DEBUG_PUB.Add(' ');
10919: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_order_exposure',1);
10920: END IF;
10921:
10922: EXCEPTION
10915: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
10916: OE_DEBUG_PUB.Add(' ');
10917: OE_DEBUG_PUB.Add('XX-------------- ##########------------------------XX' );
10918: OE_DEBUG_PUB.Add(' ');
10919: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_order_exposure',1);
10920: END IF;
10921:
10922: EXCEPTION
10923: WHEN GL_CURRENCY_API.NO_RATE THEN
10920: END IF;
10921:
10922: EXCEPTION
10923: WHEN GL_CURRENCY_API.NO_RATE THEN
10924: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE in get_order_exp',1);
10925: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10926: l_current_usage_cur );
10927: x_conversion_status(1).usage_curr_code := l_current_usage_cur ;
10928:
10921:
10922: EXCEPTION
10923: WHEN GL_CURRENCY_API.NO_RATE THEN
10924: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE in get_order_exp',1);
10925: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10926: l_current_usage_cur );
10927: x_conversion_status(1).usage_curr_code := l_current_usage_cur ;
10928:
10929: fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
10926: l_current_usage_cur );
10927: x_conversion_status(1).usage_curr_code := l_current_usage_cur ;
10928:
10929: fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
10930: OE_DEBUG_PUB.ADD('Exception table added ');
10931: IF cust_external_exposure_csr%ISOPEN THEN
10932: CLOSE cust_external_exposure_csr;
10933: END IF;
10934: IF site_external_exposure_csr%ISOPEN THEN
11023: BEGIN
11024: BEGIN
11025: IF G_debug_flag = 'Y'
11026: THEN
11027: OE_DEBUG_PUB.ADD('OEXUCRCB: IN CONVERT_CURRENCY_AMOUNT ');
11028: OE_DEBUG_PUB.ADD(' ');
11029: OE_DEBUG_PUB.ADD('------------------------------------');
11030: OE_DEBUG_PUB.ADD('p_amount = '|| p_amount );
11031: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11024: BEGIN
11025: IF G_debug_flag = 'Y'
11026: THEN
11027: OE_DEBUG_PUB.ADD('OEXUCRCB: IN CONVERT_CURRENCY_AMOUNT ');
11028: OE_DEBUG_PUB.ADD(' ');
11029: OE_DEBUG_PUB.ADD('------------------------------------');
11030: OE_DEBUG_PUB.ADD('p_amount = '|| p_amount );
11031: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11032: || p_transactional_currency );
11025: IF G_debug_flag = 'Y'
11026: THEN
11027: OE_DEBUG_PUB.ADD('OEXUCRCB: IN CONVERT_CURRENCY_AMOUNT ');
11028: OE_DEBUG_PUB.ADD(' ');
11029: OE_DEBUG_PUB.ADD('------------------------------------');
11030: OE_DEBUG_PUB.ADD('p_amount = '|| p_amount );
11031: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11032: || p_transactional_currency );
11033: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11026: THEN
11027: OE_DEBUG_PUB.ADD('OEXUCRCB: IN CONVERT_CURRENCY_AMOUNT ');
11028: OE_DEBUG_PUB.ADD(' ');
11029: OE_DEBUG_PUB.ADD('------------------------------------');
11030: OE_DEBUG_PUB.ADD('p_amount = '|| p_amount );
11031: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11032: || p_transactional_currency );
11033: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11034: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11027: OE_DEBUG_PUB.ADD('OEXUCRCB: IN CONVERT_CURRENCY_AMOUNT ');
11028: OE_DEBUG_PUB.ADD(' ');
11029: OE_DEBUG_PUB.ADD('------------------------------------');
11030: OE_DEBUG_PUB.ADD('p_amount = '|| p_amount );
11031: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11032: || p_transactional_currency );
11033: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11034: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11035: OE_DEBUG_PUB.ADD('p_conversion_date = '|| p_conversion_date );
11029: OE_DEBUG_PUB.ADD('------------------------------------');
11030: OE_DEBUG_PUB.ADD('p_amount = '|| p_amount );
11031: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11032: || p_transactional_currency );
11033: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11034: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11035: OE_DEBUG_PUB.ADD('p_conversion_date = '|| p_conversion_date );
11036: OE_DEBUG_PUB.ADD('p_conversion_type = '|| p_conversion_type );
11037: OE_DEBUG_PUB.ADD(' ');
11030: OE_DEBUG_PUB.ADD('p_amount = '|| p_amount );
11031: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11032: || p_transactional_currency );
11033: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11034: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11035: OE_DEBUG_PUB.ADD('p_conversion_date = '|| p_conversion_date );
11036: OE_DEBUG_PUB.ADD('p_conversion_type = '|| p_conversion_type );
11037: OE_DEBUG_PUB.ADD(' ');
11038: OE_DEBUG_PUB.ADD('------------------------------------');
11031: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11032: || p_transactional_currency );
11033: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11034: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11035: OE_DEBUG_PUB.ADD('p_conversion_date = '|| p_conversion_date );
11036: OE_DEBUG_PUB.ADD('p_conversion_type = '|| p_conversion_type );
11037: OE_DEBUG_PUB.ADD(' ');
11038: OE_DEBUG_PUB.ADD('------------------------------------');
11039: END IF;
11032: || p_transactional_currency );
11033: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11034: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11035: OE_DEBUG_PUB.ADD('p_conversion_date = '|| p_conversion_date );
11036: OE_DEBUG_PUB.ADD('p_conversion_type = '|| p_conversion_type );
11037: OE_DEBUG_PUB.ADD(' ');
11038: OE_DEBUG_PUB.ADD('------------------------------------');
11039: END IF;
11040:
11033: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11034: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11035: OE_DEBUG_PUB.ADD('p_conversion_date = '|| p_conversion_date );
11036: OE_DEBUG_PUB.ADD('p_conversion_type = '|| p_conversion_type );
11037: OE_DEBUG_PUB.ADD(' ');
11038: OE_DEBUG_PUB.ADD('------------------------------------');
11039: END IF;
11040:
11041: -- The conversion amount can be les than or greater than
11034: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11035: OE_DEBUG_PUB.ADD('p_conversion_date = '|| p_conversion_date );
11036: OE_DEBUG_PUB.ADD('p_conversion_type = '|| p_conversion_type );
11037: OE_DEBUG_PUB.ADD(' ');
11038: OE_DEBUG_PUB.ADD('------------------------------------');
11039: END IF;
11040:
11041: -- The conversion amount can be les than or greater than
11042: -- 0. For maounts equal to 0, there is not need for
11061: ELSE
11062: l_converted_amount := 0 ;
11063: IF G_debug_flag = 'Y'
11064: THEN
11065: OE_DEBUG_PUB.ADD(' No conversion, amount 0 ');
11066: END IF;
11067: END IF;
11068:
11069: IF G_debug_flag = 'Y'
11067: END IF;
11068:
11069: IF G_debug_flag = 'Y'
11070: THEN
11071: OE_DEBUG_PUB.ADD('Convert amt using trx curr = '|| l_converted_amount );
11072: END IF;
11073:
11074: return l_converted_amount;
11075:
11080: l_functional_amount NUMBER;
11081: BEGIN
11082: IF G_debug_flag = 'Y'
11083: THEN
11084: OE_DEBUG_PUB.ADD(' Convert using functional curr traingulate');
11085: END IF;
11086:
11087: gl_currency_api.convert_closest_amount
11088: ( x_from_currency => p_transactional_currency
11113: );
11114:
11115: IF G_debug_flag = 'Y'
11116: THEN
11117: OE_DEBUG_PUB.ADD('Convert amt using functional curr = '
11118: || l_converted_amount );
11119: END IF;
11120: return l_converted_amount;
11121:
11122: END;
11123: END;
11124: IF G_debug_flag = 'Y'
11125: THEN
11126: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY CONVERT_CURRENCY_AMOUNT ');
11127: END IF;
11128: END CONVERT_CURRENCY_AMOUNT;
11129:
11130: --
11163:
11164: BEGIN
11165: IF G_debug_flag = 'Y'
11166: THEN
11167: oe_debug_pub.ADD('In OE_CREDIT_CHECK_UTIL.Send_Credit_Hold_NTF', 1);
11168: END IF;
11169:
11170: -- Initialize return status to success
11171: x_return_status := FND_API.G_RET_STS_SUCCESS;
11174:
11175: BEGIN
11176: IF G_debug_flag = 'Y'
11177: THEN
11178: oe_debug_pub.ADD('Get the Notification approver ', 2);
11179: END IF;
11180:
11181: OPEN wfn_to;
11182: FETCH wfn_to INTO l_wfn_to;
11188: END ;
11189:
11190: IF G_debug_flag = 'Y'
11191: THEN
11192: oe_debug_pub.ADD(' l_wfn_to = '|| l_wfn_to, 2 );
11193: END IF;
11194:
11195: -- Set message attributes and send notification
11196: IF l_wfn_to is not NULL
11197: THEN
11198: BEGIN
11199: IF G_debug_flag = 'Y'
11200: THEN
11201: oe_debug_pub.ADD('Set the order type ', 2);
11202: END IF;
11203:
11204: OPEN c_order_type;
11205: FETCH c_order_type INTO l_order_type;
11222: END IF;
11223:
11224: IF G_debug_flag = 'Y'
11225: THEN
11226: oe_debug_pub.ADD(' Before send notification ', 2);
11227: END IF;
11228:
11229: l_notification_id := wf_notification.send
11230: ( role => l_wfn_to
11233: );
11234:
11235: IF G_debug_flag = 'Y'
11236: THEN
11237: oe_debug_pub.ADD(' After send notification with ID = '||
11238: l_notification_id, 2 );
11239: END IF;
11240:
11241: wf_notification.setattrtext
11274:
11275:
11276: IF G_debug_flag = 'Y'
11277: THEN
11278: oe_debug_pub.ADD('-------Message Attribute Values-------',2);
11279: oe_debug_pub.ADD('l_order_type: '||l_order_type,2);
11280: oe_debug_pub.ADD('l_order_number: '||p_header_rec.order_number,2);
11281: oe_debug_pub.ADD('l_order_hold_comment: '||l_order_hold_comment,2);
11282: oe_debug_pub.ADD('l_line_hold_count: '||l_line_hold_count,2);
11275:
11276: IF G_debug_flag = 'Y'
11277: THEN
11278: oe_debug_pub.ADD('-------Message Attribute Values-------',2);
11279: oe_debug_pub.ADD('l_order_type: '||l_order_type,2);
11280: oe_debug_pub.ADD('l_order_number: '||p_header_rec.order_number,2);
11281: oe_debug_pub.ADD('l_order_hold_comment: '||l_order_hold_comment,2);
11282: oe_debug_pub.ADD('l_line_hold_count: '||l_line_hold_count,2);
11283: oe_debug_pub.ADD('--------------------------------------',2);
11276: IF G_debug_flag = 'Y'
11277: THEN
11278: oe_debug_pub.ADD('-------Message Attribute Values-------',2);
11279: oe_debug_pub.ADD('l_order_type: '||l_order_type,2);
11280: oe_debug_pub.ADD('l_order_number: '||p_header_rec.order_number,2);
11281: oe_debug_pub.ADD('l_order_hold_comment: '||l_order_hold_comment,2);
11282: oe_debug_pub.ADD('l_line_hold_count: '||l_line_hold_count,2);
11283: oe_debug_pub.ADD('--------------------------------------',2);
11284: END IF;
11277: THEN
11278: oe_debug_pub.ADD('-------Message Attribute Values-------',2);
11279: oe_debug_pub.ADD('l_order_type: '||l_order_type,2);
11280: oe_debug_pub.ADD('l_order_number: '||p_header_rec.order_number,2);
11281: oe_debug_pub.ADD('l_order_hold_comment: '||l_order_hold_comment,2);
11282: oe_debug_pub.ADD('l_line_hold_count: '||l_line_hold_count,2);
11283: oe_debug_pub.ADD('--------------------------------------',2);
11284: END IF;
11285:
11278: oe_debug_pub.ADD('-------Message Attribute Values-------',2);
11279: oe_debug_pub.ADD('l_order_type: '||l_order_type,2);
11280: oe_debug_pub.ADD('l_order_number: '||p_header_rec.order_number,2);
11281: oe_debug_pub.ADD('l_order_hold_comment: '||l_order_hold_comment,2);
11282: oe_debug_pub.ADD('l_line_hold_count: '||l_line_hold_count,2);
11283: oe_debug_pub.ADD('--------------------------------------',2);
11284: END IF;
11285:
11286: ELSE
11279: oe_debug_pub.ADD('l_order_type: '||l_order_type,2);
11280: oe_debug_pub.ADD('l_order_number: '||p_header_rec.order_number,2);
11281: oe_debug_pub.ADD('l_order_hold_comment: '||l_order_hold_comment,2);
11282: oe_debug_pub.ADD('l_line_hold_count: '||l_line_hold_count,2);
11283: oe_debug_pub.ADD('--------------------------------------',2);
11284: END IF;
11285:
11286: ELSE
11287: IF G_debug_flag = 'Y'
11285:
11286: ELSE
11287: IF G_debug_flag = 'Y'
11288: THEN
11289: oe_debug_pub.ADD('No send notification l_wfn_to = '|| l_wfn_to, 2);
11290: END IF;
11291: END IF;
11292:
11293: IF G_debug_flag = 'Y'
11291: END IF;
11292:
11293: IF G_debug_flag = 'Y'
11294: THEN
11295: oe_debug_pub.ADD('OEXUCRCB: OUT NOCOPY Send_Credit_Hold_NTF');
11296: END IF;
11297:
11298: -- No exception raised as notification send is not considered
11299: -- as a stop for credit checking
11303: BEGIN
11304: x_return_status := FND_API.G_RET_STS_ERROR ;
11305: IF G_debug_flag = 'Y'
11306: THEN
11307: oe_debug_pub.ADD(' EXCEPTION OTHERS IN SEND_NOTIFICATION');
11308: oe_debug_pub.ADD( ' Errm = '|| SUBSTR(sqlerrm,1,200) );
11309: END IF;
11310: OE_MSG_PUB.Count_And_Get
11311: ( p_count => l_msg_count,
11304: x_return_status := FND_API.G_RET_STS_ERROR ;
11305: IF G_debug_flag = 'Y'
11306: THEN
11307: oe_debug_pub.ADD(' EXCEPTION OTHERS IN SEND_NOTIFICATION');
11308: oe_debug_pub.ADD( ' Errm = '|| SUBSTR(sqlerrm,1,200) );
11309: END IF;
11310: OE_MSG_PUB.Count_And_Get
11311: ( p_count => l_msg_count,
11312: p_data => l_msg_data
11312: p_data => l_msg_data
11313: );
11314: IF G_debug_flag = 'Y'
11315: THEN
11316: oe_debug_pub.ADD(' l_msg_data = ' || l_msg_data );
11317: END IF;
11318: l_msg_data := NULL ;
11319: l_msg_count := NULL ;
11320: END ;
11347: BEGIN
11348:
11349: IF G_debug_flag = 'Y'
11350: THEN
11351: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_external_trx_amount');
11352: END IF;
11353:
11354: x_return_status := FND_API.G_RET_STS_SUCCESS;
11355: OE_CREDIT_CHECK_UTIL.g_current_order_value := NULL ;
11355: OE_CREDIT_CHECK_UTIL.g_current_order_value := NULL ;
11356:
11357: IF G_debug_flag = 'Y'
11358: THEN
11359: OE_DEBUG_PUB.ADD('Order Level Credit Check only');
11360: END IF ;
11361: -- convert amount
11362: BEGIN
11363: IF G_debug_flag = 'Y'
11361: -- convert amount
11362: BEGIN
11363: IF G_debug_flag = 'Y'
11364: THEN
11365: OE_DEBUG_PUB.Add(' GL_CURRENCY = '|| OE_Credit_Engine_GRP.GL_currency);
11366: OE_DEBUG_PUB.ADD(' Total order amount = '|| p_transaction_amount);
11367: END IF;
11368:
11369:
11362: BEGIN
11363: IF G_debug_flag = 'Y'
11364: THEN
11365: OE_DEBUG_PUB.Add(' GL_CURRENCY = '|| OE_Credit_Engine_GRP.GL_currency);
11366: OE_DEBUG_PUB.ADD(' Total order amount = '|| p_transaction_amount);
11367: END IF;
11368:
11369:
11370: l_limit_order_value :=
11386: OE_CREDIT_CHECK_UTIL.g_current_order_value := x_amount ;
11387:
11388: IF G_debug_flag = 'Y'
11389: THEN
11390: OE_DEBUG_PUB.Add(' g_current_order_value = '||
11391: OE_CREDIT_CHECK_UTIL.g_current_order_value);
11392: OE_DEBUG_PUB.Add(' Final trx check order amount = '|| x_amount,1 );
11393: OE_DEBUG_PUB.Add(' ====================================');
11394: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_external_trx_amount ');
11388: IF G_debug_flag = 'Y'
11389: THEN
11390: OE_DEBUG_PUB.Add(' g_current_order_value = '||
11391: OE_CREDIT_CHECK_UTIL.g_current_order_value);
11392: OE_DEBUG_PUB.Add(' Final trx check order amount = '|| x_amount,1 );
11393: OE_DEBUG_PUB.Add(' ====================================');
11394: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_external_trx_amount ');
11395: END IF;
11396:
11389: THEN
11390: OE_DEBUG_PUB.Add(' g_current_order_value = '||
11391: OE_CREDIT_CHECK_UTIL.g_current_order_value);
11392: OE_DEBUG_PUB.Add(' Final trx check order amount = '|| x_amount,1 );
11393: OE_DEBUG_PUB.Add(' ====================================');
11394: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_external_trx_amount ');
11395: END IF;
11396:
11397: EXCEPTION
11390: OE_DEBUG_PUB.Add(' g_current_order_value = '||
11391: OE_CREDIT_CHECK_UTIL.g_current_order_value);
11392: OE_DEBUG_PUB.Add(' Final trx check order amount = '|| x_amount,1 );
11393: OE_DEBUG_PUB.Add(' ====================================');
11394: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_external_trx_amount ');
11395: END IF;
11396:
11397: EXCEPTION
11398: WHEN GL_CURRENCY_API.NO_RATE THEN
11395: END IF;
11396:
11397: EXCEPTION
11398: WHEN GL_CURRENCY_API.NO_RATE THEN
11399: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE ');
11400: x_conversion_status(1).usage_curr_code := p_transaction_curr_code;
11401: fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
11402: OE_DEBUG_PUB.ADD('Exception table added ');
11403: WHEN NO_DATA_FOUND THEN
11398: WHEN GL_CURRENCY_API.NO_RATE THEN
11399: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE ');
11400: x_conversion_status(1).usage_curr_code := p_transaction_curr_code;
11401: fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
11402: OE_DEBUG_PUB.ADD('Exception table added ');
11403: WHEN NO_DATA_FOUND THEN
11404: x_amount := 0 ;
11405: OE_DEBUG_PUB.ADD('EXCEPTION: NO_DATA_FOUND ');
11406: WHEN TOO_MANY_ROWS THEN
11401: fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
11402: OE_DEBUG_PUB.ADD('Exception table added ');
11403: WHEN NO_DATA_FOUND THEN
11404: x_amount := 0 ;
11405: OE_DEBUG_PUB.ADD('EXCEPTION: NO_DATA_FOUND ');
11406: WHEN TOO_MANY_ROWS THEN
11407: x_amount := 0 ;
11408: OE_DEBUG_PUB.ADD('EXCEPTION: TOO_MANY_ROWS ');
11409: WHEN OTHERS THEN
11404: x_amount := 0 ;
11405: OE_DEBUG_PUB.ADD('EXCEPTION: NO_DATA_FOUND ');
11406: WHEN TOO_MANY_ROWS THEN
11407: x_amount := 0 ;
11408: OE_DEBUG_PUB.ADD('EXCEPTION: TOO_MANY_ROWS ');
11409: WHEN OTHERS THEN
11410: G_DBG_MSG := SUBSTRB(sqlerrm,1,200);
11411: OE_DEBUG_PUB.ADD('EXCEPTION = '|| SUBSTRB(sqlerrm,1,200));
11412: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
11407: x_amount := 0 ;
11408: OE_DEBUG_PUB.ADD('EXCEPTION: TOO_MANY_ROWS ');
11409: WHEN OTHERS THEN
11410: G_DBG_MSG := SUBSTRB(sqlerrm,1,200);
11411: OE_DEBUG_PUB.ADD('EXCEPTION = '|| SUBSTRB(sqlerrm,1,200));
11412: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
11413: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
11414: OE_MSG_PUB.Add_Exc_Msg
11415: ( G_PKG_NAME
11437: l_credit_usage_rule_set_id NUMBER;
11438: BEGIN
11439: IF G_debug_flag = 'Y'
11440: THEN
11441: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_default_Limit ');
11442: END IF;
11443:
11444: x_default_limit_flag := 'N' ;
11445: x_limit_curr_code := NULL ;
11444: x_default_limit_flag := 'N' ;
11445: x_limit_curr_code := NULL ;
11446: x_global_exposure_flag := 'N' ;
11447:
11448: OE_DEBUG_PUB.ADD(' calling GET_multi_default_Limit ');
11449:
11450: GET_multi_default_Limit
11451: ( p_entity_id => p_entity_id
11452: , p_trx_curr_code => p_trx_curr_code
11460: );
11461:
11462: IF G_debug_flag = 'Y'
11463: THEN
11464: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_default_Limit '
11465: || x_default_limit_flag );
11466: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
11467: END IF;
11468:
11462: IF G_debug_flag = 'Y'
11463: THEN
11464: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_default_Limit '
11465: || x_default_limit_flag );
11466: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
11467: END IF;
11468:
11469: IF x_default_limit_flag = 'Y'
11470: AND x_trx_limit is NULL
11471: AND x_overall_limit is NULL
11472: THEN
11473: x_default_limit_flag := 'N' ;
11474: x_limit_curr_code := NULL ;
11475: OE_DEBUG_PUB.ADD(' Limits NULL ');
11476:
11477: END IF;
11478:
11479: IF x_limit_curr_code IS NULL
11478:
11479: IF x_limit_curr_code IS NULL
11480: THEN
11481: x_default_limit_flag := 'N' ;
11482: OE_DEBUG_PUB.ADD(' calling GET_single_default_Limit ');
11483: GET_single_default_Limit
11484: ( p_entity_id => p_entity_id
11485: , p_trx_curr_code => p_trx_curr_code
11486: , x_limit_curr_code => x_limit_curr_code
11490: );
11491:
11492: IF G_debug_flag = 'Y'
11493: THEN
11494: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_single_default_Limit '
11495: || x_default_limit_flag );
11496: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
11497: END IF;
11498:
11492: IF G_debug_flag = 'Y'
11493: THEN
11494: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_single_default_Limit '
11495: || x_default_limit_flag );
11496: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
11497: END IF;
11498:
11499: END IF;
11500:
11500:
11501:
11502: IF G_debug_flag = 'Y'
11503: THEN
11504: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_default_Limit ' ||
11505: x_default_limit_flag );
11506: END IF;
11507:
11508: EXCEPTION
11577:
11578: IF G_debug_flag = 'Y'
11579: THEN
11580:
11581: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_limit_info', 1);
11582: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11583: OE_DEBUG_PUB.ADD('G_ORG_ID ==> '|| G_ORG_ID );
11584: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11585: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11578: IF G_debug_flag = 'Y'
11579: THEN
11580:
11581: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_limit_info', 1);
11582: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11583: OE_DEBUG_PUB.ADD('G_ORG_ID ==> '|| G_ORG_ID );
11584: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11585: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11586: OE_DEBUG_PUB.ADD('P_party_id ==> '|| P_party_id );
11579: THEN
11580:
11581: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_limit_info', 1);
11582: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11583: OE_DEBUG_PUB.ADD('G_ORG_ID ==> '|| G_ORG_ID );
11584: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11585: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11586: OE_DEBUG_PUB.ADD('P_party_id ==> '|| P_party_id );
11587: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
11580:
11581: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_limit_info', 1);
11582: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11583: OE_DEBUG_PUB.ADD('G_ORG_ID ==> '|| G_ORG_ID );
11584: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11585: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11586: OE_DEBUG_PUB.ADD('P_party_id ==> '|| P_party_id );
11587: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
11588: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '||
11581: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_limit_info', 1);
11582: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11583: OE_DEBUG_PUB.ADD('G_ORG_ID ==> '|| G_ORG_ID );
11584: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11585: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11586: OE_DEBUG_PUB.ADD('P_party_id ==> '|| P_party_id );
11587: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
11588: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '||
11589: p_navigate_to_next_level );
11582: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11583: OE_DEBUG_PUB.ADD('G_ORG_ID ==> '|| G_ORG_ID );
11584: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11585: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11586: OE_DEBUG_PUB.ADD('P_party_id ==> '|| P_party_id );
11587: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
11588: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '||
11589: p_navigate_to_next_level );
11590:
11583: OE_DEBUG_PUB.ADD('G_ORG_ID ==> '|| G_ORG_ID );
11584: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11585: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11586: OE_DEBUG_PUB.ADD('P_party_id ==> '|| P_party_id );
11587: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
11588: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '||
11589: p_navigate_to_next_level );
11590:
11591: OE_DEBUG_PUB.ADD('p_suppress_unused_usages_flag ==> '||
11584: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11585: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11586: OE_DEBUG_PUB.ADD('P_party_id ==> '|| P_party_id );
11587: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
11588: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '||
11589: p_navigate_to_next_level );
11590:
11591: OE_DEBUG_PUB.ADD('p_suppress_unused_usages_flag ==> '||
11592: p_suppress_unused_usages_flag );
11587: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
11588: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '||
11589: p_navigate_to_next_level );
11590:
11591: OE_DEBUG_PUB.ADD('p_suppress_unused_usages_flag ==> '||
11592: p_suppress_unused_usages_flag );
11593:
11594: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11595:
11590:
11591: OE_DEBUG_PUB.ADD('p_suppress_unused_usages_flag ==> '||
11592: p_suppress_unused_usages_flag );
11593:
11594: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11595:
11596: OE_DEBUG_PUB.ADD(' Call Get_Multi_Limit ');
11597: END IF;
11598:
11592: p_suppress_unused_usages_flag );
11593:
11594: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11595:
11596: OE_DEBUG_PUB.ADD(' Call Get_Multi_Limit ');
11597: END IF;
11598:
11599: IF p_entity_type in ('SITE','CUSTOMER')
11600: THEN
11612: );
11613:
11614: IF G_debug_flag = 'Y'
11615: THEN
11616: OE_DEBUG_PUB.ADD(' Out NOCOPY of Get_Multi_Limit ');
11617: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
11618: END IF;
11619:
11620: IF x_limit_curr_code IS NOT NULL
11613:
11614: IF G_debug_flag = 'Y'
11615: THEN
11616: OE_DEBUG_PUB.ADD(' Out NOCOPY of Get_Multi_Limit ');
11617: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
11618: END IF;
11619:
11620: IF x_limit_curr_code IS NOT NULL
11621: THEN
11628: AND NVL(l_site_cc_flag, 'Y') = 'Y' --bug 4212981 , bug 4582292
11629: THEN
11630: IF G_debug_flag = 'Y'
11631: THEN
11632: OE_DEBUG_PUB.ADD('calling Get_Multi_Limit for CUSTOMER ');
11633: END IF;
11634: Get_Multi_Limit
11635: ( p_entity_type => 'CUSTOMER'
11636: , p_entity_id => p_cust_account_id
11651: END IF;
11652: END IF;
11653: END IF;
11654:
11655: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '|| p_navigate_to_next_level );
11656: OE_DEBUG_PUB.ADD('l_site_cc_flag ==> '|| l_site_cc_flag );
11657: OE_DEBUG_PUB.ADD('l_cust_cc_flag ==> '|| l_cust_cc_flag );
11658: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
11659: OE_DEBUG_PUB.ADD('l_return_status ==> '|| l_return_status );
11652: END IF;
11653: END IF;
11654:
11655: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '|| p_navigate_to_next_level );
11656: OE_DEBUG_PUB.ADD('l_site_cc_flag ==> '|| l_site_cc_flag );
11657: OE_DEBUG_PUB.ADD('l_cust_cc_flag ==> '|| l_cust_cc_flag );
11658: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
11659: OE_DEBUG_PUB.ADD('l_return_status ==> '|| l_return_status );
11660:
11653: END IF;
11654:
11655: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '|| p_navigate_to_next_level );
11656: OE_DEBUG_PUB.ADD('l_site_cc_flag ==> '|| l_site_cc_flag );
11657: OE_DEBUG_PUB.ADD('l_cust_cc_flag ==> '|| l_cust_cc_flag );
11658: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
11659: OE_DEBUG_PUB.ADD('l_return_status ==> '|| l_return_status );
11660:
11661: IF p_navigate_to_next_level = 'Y' THEN --bug 5071518
11654:
11655: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '|| p_navigate_to_next_level );
11656: OE_DEBUG_PUB.ADD('l_site_cc_flag ==> '|| l_site_cc_flag );
11657: OE_DEBUG_PUB.ADD('l_cust_cc_flag ==> '|| l_cust_cc_flag );
11658: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
11659: OE_DEBUG_PUB.ADD('l_return_status ==> '|| l_return_status );
11660:
11661: IF p_navigate_to_next_level = 'Y' THEN --bug 5071518
11662: IF NVL(l_site_cc_flag, 'Y') = 'Y' AND NVL(l_cust_cc_flag, 'Y') = 'Y' --bug 4212981 , bug4582292, bug 5071518
11655: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '|| p_navigate_to_next_level );
11656: OE_DEBUG_PUB.ADD('l_site_cc_flag ==> '|| l_site_cc_flag );
11657: OE_DEBUG_PUB.ADD('l_cust_cc_flag ==> '|| l_cust_cc_flag );
11658: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
11659: OE_DEBUG_PUB.ADD('l_return_status ==> '|| l_return_status );
11660:
11661: IF p_navigate_to_next_level = 'Y' THEN --bug 5071518
11662: IF NVL(l_site_cc_flag, 'Y') = 'Y' AND NVL(l_cust_cc_flag, 'Y') = 'Y' --bug 4212981 , bug4582292, bug 5071518
11663: THEN
11678: IF x_limit_curr_code IS NULL
11679: THEN
11680: IF G_debug_flag = 'Y'
11681: THEN
11682: OE_DEBUG_PUB.ADD( 'OEXUCRCB: GET_party_limit ');
11683: END IF;
11684:
11685: GET_party_limit
11686: ( p_party_id => p_party_id
11693: );
11694:
11695: IF G_debug_flag = 'Y'
11696: THEN
11697: OE_DEBUG_PUB.ADD( ' out NOCOPY GET_party_limit ');
11698: OE_DEBUG_PUB.ADD( ' l_return_status ==> '|| l_return_status );
11699: END IF;
11700:
11701: IF x_limit_curr_code IS NOT NULL
11694:
11695: IF G_debug_flag = 'Y'
11696: THEN
11697: OE_DEBUG_PUB.ADD( ' out NOCOPY GET_party_limit ');
11698: OE_DEBUG_PUB.ADD( ' l_return_status ==> '|| l_return_status );
11699: END IF;
11700:
11701: IF x_limit_curr_code IS NOT NULL
11702: THEN
11711:
11712: IF x_limit_curr_code IS NULL
11713: AND NVL(l_return_status,'Y') = 'Y' -- bug 4958142
11714: THEN
11715: OE_DEBUG_PUB.ADD( ' calling GET_default_Limit ');
11716: GET_default_Limit
11717: ( p_entity_id => p_entity_id
11718: , p_trx_curr_code => p_trx_curr_code
11719: , x_limit_curr_code => x_limit_curr_code
11730: END IF;
11731: END IF;
11732:
11733: -- Start bug 5071518
11734: OE_DEBUG_PUB.ADD(' x_limit_curr_code => '|| x_limit_curr_code );
11735: OE_DEBUG_PUB.ADD(' x_trx_limit => '|| x_trx_limit );
11736: OE_DEBUG_PUB.ADD(' x_overall_limit => '|| x_overall_limit );
11737: OE_DEBUG_PUB.ADD(' x_default_limit_flag => '|| x_default_limit_flag );
11738:
11731: END IF;
11732:
11733: -- Start bug 5071518
11734: OE_DEBUG_PUB.ADD(' x_limit_curr_code => '|| x_limit_curr_code );
11735: OE_DEBUG_PUB.ADD(' x_trx_limit => '|| x_trx_limit );
11736: OE_DEBUG_PUB.ADD(' x_overall_limit => '|| x_overall_limit );
11737: OE_DEBUG_PUB.ADD(' x_default_limit_flag => '|| x_default_limit_flag );
11738:
11739: END IF; -- navigate
11732:
11733: -- Start bug 5071518
11734: OE_DEBUG_PUB.ADD(' x_limit_curr_code => '|| x_limit_curr_code );
11735: OE_DEBUG_PUB.ADD(' x_trx_limit => '|| x_trx_limit );
11736: OE_DEBUG_PUB.ADD(' x_overall_limit => '|| x_overall_limit );
11737: OE_DEBUG_PUB.ADD(' x_default_limit_flag => '|| x_default_limit_flag );
11738:
11739: END IF; -- navigate
11740:
11733: -- Start bug 5071518
11734: OE_DEBUG_PUB.ADD(' x_limit_curr_code => '|| x_limit_curr_code );
11735: OE_DEBUG_PUB.ADD(' x_trx_limit => '|| x_trx_limit );
11736: OE_DEBUG_PUB.ADD(' x_overall_limit => '|| x_overall_limit );
11737: OE_DEBUG_PUB.ADD(' x_default_limit_flag => '|| x_default_limit_flag );
11738:
11739: END IF; -- navigate
11740:
11741: IF x_limit_curr_code IS NULL
11739: END IF; -- navigate
11740:
11741: IF x_limit_curr_code IS NULL
11742: THEN
11743: OE_DEBUG_PUB.ADD(' No limits for any level ');
11744:
11745: x_trx_limit := NULL ;
11746: x_overall_limit := NULL ;
11747: x_include_all_flag := 'N' ;
11752:
11753: ELSE
11754: IF G_debug_flag = 'Y'
11755: THEN
11756: OE_DEBUG_PUB.ADD( ' Calling Get_Usages' );
11757: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
11758: OE_DEBUG_PUB.ADD('x_default_limit_flag ==> '|| x_default_limit_flag );
11759: OE_DEBUG_PUB.ADD('x_global_exposure_flag ==> '|| x_global_exposure_flag );
11760: OE_DEBUG_PUB.ADD('x_include_all_flag ==> '|| x_include_all_flag );
11753: ELSE
11754: IF G_debug_flag = 'Y'
11755: THEN
11756: OE_DEBUG_PUB.ADD( ' Calling Get_Usages' );
11757: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
11758: OE_DEBUG_PUB.ADD('x_default_limit_flag ==> '|| x_default_limit_flag );
11759: OE_DEBUG_PUB.ADD('x_global_exposure_flag ==> '|| x_global_exposure_flag );
11760: OE_DEBUG_PUB.ADD('x_include_all_flag ==> '|| x_include_all_flag );
11761: OE_DEBUG_PUB.ADD('x_credit_limit_entity_id ==> '||
11754: IF G_debug_flag = 'Y'
11755: THEN
11756: OE_DEBUG_PUB.ADD( ' Calling Get_Usages' );
11757: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
11758: OE_DEBUG_PUB.ADD('x_default_limit_flag ==> '|| x_default_limit_flag );
11759: OE_DEBUG_PUB.ADD('x_global_exposure_flag ==> '|| x_global_exposure_flag );
11760: OE_DEBUG_PUB.ADD('x_include_all_flag ==> '|| x_include_all_flag );
11761: OE_DEBUG_PUB.ADD('x_credit_limit_entity_id ==> '||
11762: x_credit_limit_entity_id );
11755: THEN
11756: OE_DEBUG_PUB.ADD( ' Calling Get_Usages' );
11757: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
11758: OE_DEBUG_PUB.ADD('x_default_limit_flag ==> '|| x_default_limit_flag );
11759: OE_DEBUG_PUB.ADD('x_global_exposure_flag ==> '|| x_global_exposure_flag );
11760: OE_DEBUG_PUB.ADD('x_include_all_flag ==> '|| x_include_all_flag );
11761: OE_DEBUG_PUB.ADD('x_credit_limit_entity_id ==> '||
11762: x_credit_limit_entity_id );
11763: OE_DEBUG_PUB.ADD('x_credit_check_level ==> '||
11756: OE_DEBUG_PUB.ADD( ' Calling Get_Usages' );
11757: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
11758: OE_DEBUG_PUB.ADD('x_default_limit_flag ==> '|| x_default_limit_flag );
11759: OE_DEBUG_PUB.ADD('x_global_exposure_flag ==> '|| x_global_exposure_flag );
11760: OE_DEBUG_PUB.ADD('x_include_all_flag ==> '|| x_include_all_flag );
11761: OE_DEBUG_PUB.ADD('x_credit_limit_entity_id ==> '||
11762: x_credit_limit_entity_id );
11763: OE_DEBUG_PUB.ADD('x_credit_check_level ==> '||
11764: x_credit_check_level );
11757: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
11758: OE_DEBUG_PUB.ADD('x_default_limit_flag ==> '|| x_default_limit_flag );
11759: OE_DEBUG_PUB.ADD('x_global_exposure_flag ==> '|| x_global_exposure_flag );
11760: OE_DEBUG_PUB.ADD('x_include_all_flag ==> '|| x_include_all_flag );
11761: OE_DEBUG_PUB.ADD('x_credit_limit_entity_id ==> '||
11762: x_credit_limit_entity_id );
11763: OE_DEBUG_PUB.ADD('x_credit_check_level ==> '||
11764: x_credit_check_level );
11765: END IF;
11759: OE_DEBUG_PUB.ADD('x_global_exposure_flag ==> '|| x_global_exposure_flag );
11760: OE_DEBUG_PUB.ADD('x_include_all_flag ==> '|| x_include_all_flag );
11761: OE_DEBUG_PUB.ADD('x_credit_limit_entity_id ==> '||
11762: x_credit_limit_entity_id );
11763: OE_DEBUG_PUB.ADD('x_credit_check_level ==> '||
11764: x_credit_check_level );
11765: END IF;
11766:
11767: IF x_credit_check_level = 'PARTY'
11781: );
11782:
11783: IF G_debug_flag = 'Y'
11784: THEN
11785: OE_DEBUG_PUB.ADD( ' Out NOCOPY of Get_Usages ');
11786: END IF;
11787: END IF;
11788:
11789: IF G_debug_flag = 'Y'
11787: END IF;
11788:
11789: IF G_debug_flag = 'Y'
11790: THEN
11791: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY get_limit_info ',1);
11792: END IF;
11793:
11794: EXCEPTION
11795: WHEN OTHERS THEN