[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);
2447: l_ctg_count NUMBER := 0;
2448: l_line_value NUMBER := 0 ;
2449: BEGIN
2450:
2451: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_Item_Limit ');
2452:
2453: ----------------------------------------------------
2454: -- Two PL/SQL tables are used to maintain the limits
2455: -- associated with the Item categories.
2467:
2468: g_category_set_id := NULL;
2469: l_ctg_profile_exist := 'N' ;
2470:
2471: OE_DEBUG_PUB.ADD('Initial g_category_set_id ' || g_category_set_id );
2472: -- OE_DEBUG_PUB.ADD(' G_profile ' || G_profile );
2473:
2474: -- For each Line ID/Item, the limits are selected
2475: -- using the Category.
2468: g_category_set_id := NULL;
2469: l_ctg_profile_exist := 'N' ;
2470:
2471: OE_DEBUG_PUB.ADD('Initial g_category_set_id ' || g_category_set_id );
2472: -- OE_DEBUG_PUB.ADD(' G_profile ' || G_profile );
2473:
2474: -- For each Line ID/Item, the limits are selected
2475: -- using the Category.
2476: -- The similar selection pattern in coordination
2480:
2481: -- First verify if any category exist with a profile
2482:
2483: BEGIN
2484: OE_DEBUG_PUB.ADD(' Cusror select to check CTG profile exist ');
2485:
2486: OPEN C_SELECT_CTG_CREDIT_PROFILE ;
2487:
2488: FETCH C_SELECT_CTG_CREDIT_PROFILE
2490:
2491: IF C_SELECT_CTG_CREDIT_PROFILE%NOTFOUND
2492: THEN
2493: l_ctg_profile_exist := 'N' ;
2494: OE_DEBUG_PUB.ADD(' Category profile do NOT exist ');
2495: ELSE
2496: l_credit_profile_id :=
2497: l_SELECT_CTG_CREDIT_PROFILE.credit_profile_id ;
2498: l_ctg_profile_exist := 'Y' ;
2495: ELSE
2496: l_credit_profile_id :=
2497: l_SELECT_CTG_CREDIT_PROFILE.credit_profile_id ;
2498: l_ctg_profile_exist := 'Y' ;
2499: OE_DEBUG_PUB.ADD(' Category profile exist ');
2500: END IF;
2501: CLOSE C_SELECT_CTG_CREDIT_PROFILE ;
2502:
2503: END ;
2502:
2503: END ;
2504:
2505:
2506: OE_DEBUG_PUB.ADD(' Out NOCOPY of CTG profile check cursor ');
2507: OE_DEBUG_PUB.ADD(' l_ctg_profile_exist = '|| l_ctg_profile_exist );
2508:
2509: IF l_ctg_profile_exist = 'Y'
2510: THEN
2503: END ;
2504:
2505:
2506: OE_DEBUG_PUB.ADD(' Out NOCOPY of CTG profile check cursor ');
2507: OE_DEBUG_PUB.ADD(' l_ctg_profile_exist = '|| l_ctg_profile_exist );
2508:
2509: IF l_ctg_profile_exist = 'Y'
2510: THEN
2511:
2508:
2509: IF l_ctg_profile_exist = 'Y'
2510: THEN
2511:
2512: OE_DEBUG_PUB.ADD(' Continue Item category credit get limit ');
2513:
2514: g_category_set_id := GET_category_set_id ;
2515: OE_DEBUG_PUB.ADD('g_category_set_id ' || g_category_set_id );
2516:
2511:
2512: OE_DEBUG_PUB.ADD(' Continue Item category credit get limit ');
2513:
2514: g_category_set_id := GET_category_set_id ;
2515: OE_DEBUG_PUB.ADD('g_category_set_id ' || g_category_set_id );
2516:
2517: FOR L_SELECT_LINES_VAL IN C_SELECT_LINES_CSR
2518: LOOP
2519: l_line_id := L_SELECT_LINES_VAL.line_id ;
2541: l_ordered_quantity := NULL;
2542:
2543: END LOOP; -- CTG cursor loop
2544:
2545: OE_DEBUG_PUB.ADD(' Out NOCOPY of category cursor ');
2546:
2547: OE_DEBUG_PUB.ADD(' l_tmp_lines_tbl.COUNT ' || l_tmp_lines_tbl.COUNT );
2548: -- Now the TMP table needs to be scannned and relocated into
2549: -- the main table with category check.
2543: END LOOP; -- CTG cursor loop
2544:
2545: OE_DEBUG_PUB.ADD(' Out NOCOPY of category cursor ');
2546:
2547: OE_DEBUG_PUB.ADD(' l_tmp_lines_tbl.COUNT ' || l_tmp_lines_tbl.COUNT );
2548: -- Now the TMP table needs to be scannned and relocated into
2549: -- the main table with category check.
2550:
2551: FOR I IN 1.. l_tmp_lines_tbl.COUNT
2559: l_ctg_count := NVL(l_ctg_count,0) + 1 ;
2560:
2561: -- Always get multi first and get single if no usages found
2562:
2563: OE_DEBUG_PUB.ADD(' GET_multi_Item_Limit ');
2564:
2565: GET_multi_Item_Limit
2566: ( p_category_id => l_tmp_lines_tbl(i).item_category_id
2567: , p_trx_curr_code => p_trx_curr_code
2571: );
2572:
2573: IF l_return_status = 'N'
2574: THEN
2575: OE_DEBUG_PUB.ADD(' Call GET_single_Item_Limit ');
2576: GET_single_Item_Limit
2577: ( p_category_id => l_tmp_lines_tbl(i).item_category_id
2578: , p_trx_curr_code => p_trx_curr_code
2579: , x_limit_curr_code => l_limit_curr_code
2585: -- END IF;
2586:
2587: IF G_debug_flag = 'Y'
2588: THEN
2589: OE_DEBUG_PUB.ADD(' after getting the limits ');
2590: OE_DEBUG_PUB.ADD(' l_return_status '|| l_return_status);
2591: OE_DEBUG_PUB.ADD(' l_trx_limit '|| l_trx_limit);
2592: OE_DEBUG_PUB.ADD(' l_limit_curr_code '|| l_limit_curr_code );
2593: OE_DEBUG_PUB.ADD(' p_trx_curr_code '|| p_trx_curr_code );
2586:
2587: IF G_debug_flag = 'Y'
2588: THEN
2589: OE_DEBUG_PUB.ADD(' after getting the limits ');
2590: OE_DEBUG_PUB.ADD(' l_return_status '|| l_return_status);
2591: OE_DEBUG_PUB.ADD(' l_trx_limit '|| l_trx_limit);
2592: OE_DEBUG_PUB.ADD(' l_limit_curr_code '|| l_limit_curr_code );
2593: OE_DEBUG_PUB.ADD(' p_trx_curr_code '|| p_trx_curr_code );
2594: END IF;
2587: IF G_debug_flag = 'Y'
2588: THEN
2589: OE_DEBUG_PUB.ADD(' after getting the limits ');
2590: OE_DEBUG_PUB.ADD(' l_return_status '|| l_return_status);
2591: OE_DEBUG_PUB.ADD(' l_trx_limit '|| l_trx_limit);
2592: OE_DEBUG_PUB.ADD(' l_limit_curr_code '|| l_limit_curr_code );
2593: OE_DEBUG_PUB.ADD(' p_trx_curr_code '|| p_trx_curr_code );
2594: END IF;
2595:
2588: THEN
2589: OE_DEBUG_PUB.ADD(' after getting the limits ');
2590: OE_DEBUG_PUB.ADD(' l_return_status '|| l_return_status);
2591: OE_DEBUG_PUB.ADD(' l_trx_limit '|| l_trx_limit);
2592: OE_DEBUG_PUB.ADD(' l_limit_curr_code '|| l_limit_curr_code );
2593: OE_DEBUG_PUB.ADD(' p_trx_curr_code '|| p_trx_curr_code );
2594: END IF;
2595:
2596: l_curr_category_id := l_tmp_lines_tbl(i).item_category_id ;
2589: OE_DEBUG_PUB.ADD(' after getting the limits ');
2590: OE_DEBUG_PUB.ADD(' l_return_status '|| l_return_status);
2591: OE_DEBUG_PUB.ADD(' l_trx_limit '|| l_trx_limit);
2592: OE_DEBUG_PUB.ADD(' l_limit_curr_code '|| l_limit_curr_code );
2593: OE_DEBUG_PUB.ADD(' p_trx_curr_code '|| p_trx_curr_code );
2594: END IF;
2595:
2596: l_curr_category_id := l_tmp_lines_tbl(i).item_category_id ;
2597:
2682: END LOOP;
2683:
2684: ELSE
2685:
2686: OE_DEBUG_PUB.ADD(' No CTG credit profile found - No Item CC required ');
2687:
2688:
2689: END IF; -- Profile check IF
2690:
2688:
2689: END IF; -- Profile check IF
2690:
2691:
2692: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_Item_Limit ');
2693:
2694:
2695: EXCEPTION
2696: WHEN OTHERS THEN
2737: BEGIN
2738:
2739: IF G_debug_flag = 'Y'
2740: THEN
2741: OE_DEBUG_PUB.ADD('OEXUCRCB: IN Get_Multi_Limit ');
2742: END IF;
2743:
2744: x_global_exposure_flag := 'N' ;
2745:
2760: );
2761:
2762: IF G_debug_flag = 'Y'
2763: THEN
2764: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_customer_Limit '|| l_return_status );
2765: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2766: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2767: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2768: END IF;
2761:
2762: IF G_debug_flag = 'Y'
2763: THEN
2764: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_customer_Limit '|| l_return_status );
2765: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2766: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2767: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2768: END IF;
2769:
2762: IF G_debug_flag = 'Y'
2763: THEN
2764: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_customer_Limit '|| l_return_status );
2765: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2766: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2767: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2768: END IF;
2769:
2770: x_cust_cc_flag := l_return_status; --bug 4582292
2763: THEN
2764: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_customer_Limit '|| l_return_status );
2765: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2766: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2767: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2768: END IF;
2769:
2770: x_cust_cc_flag := l_return_status; --bug 4582292
2771:
2779: l_return_status := 'N' ;
2780: x_limit_curr_code := NULL ;
2781: x_global_exposure_flag := 'N' ;
2782:
2783: OE_DEBUG_PUB.ADD(' Limits NULL for customer MULTI ');
2784:
2785: END IF;
2786:
2787: IF NVL(l_return_status, 'N') = 'N' --bug 5071518
2790: l_credit_usage_rule_set_id := NULL ;
2791:
2792: IF G_debug_flag = 'Y'
2793: THEN
2794: OE_DEBUG_PUB.ADD(' Calling GET_Single_Customer_Limit ');
2795: END IF;
2796:
2797: GET_Single_Customer_Limit
2798: ( p_entity_id => p_entity_id
2804: );
2805:
2806: IF G_debug_flag = 'Y'
2807: THEN
2808: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_Single_Customer_Limit '
2809: || l_return_status );
2810: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '
2811: || x_limit_curr_code );
2812: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2806: IF G_debug_flag = 'Y'
2807: THEN
2808: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_Single_Customer_Limit '
2809: || l_return_status );
2810: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '
2811: || x_limit_curr_code );
2812: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2813: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2814: END IF;
2808: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_Single_Customer_Limit '
2809: || l_return_status );
2810: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '
2811: || x_limit_curr_code );
2812: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2813: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2814: END IF;
2815:
2816: x_cust_cc_flag := l_return_status; -- bug 4582292
2809: || l_return_status );
2810: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '
2811: || x_limit_curr_code );
2812: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2813: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2814: END IF;
2815:
2816: x_cust_cc_flag := l_return_status; -- bug 4582292
2817:
2833: x_global_exposure_flag := 'N' ;
2834:
2835: IF G_debug_flag = 'Y'
2836: THEN
2837: OE_DEBUG_PUB.ADD(' Call GET_multi_site_Limit ');
2838: END IF;
2839:
2840: GET_multi_site_Limit
2841: ( p_entity_id => p_entity_id
2847: );
2848:
2849: IF G_debug_flag = 'Y'
2850: THEN
2851: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_site_Limit '|| l_return_status );
2852: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2853: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2854: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2855: END IF;
2848:
2849: IF G_debug_flag = 'Y'
2850: THEN
2851: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_site_Limit '|| l_return_status );
2852: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2853: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2854: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2855: END IF;
2856:
2849: IF G_debug_flag = 'Y'
2850: THEN
2851: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_site_Limit '|| l_return_status );
2852: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2853: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2854: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2855: END IF;
2856:
2857: x_site_cc_flag := l_return_status; --bug 4212981
2850: THEN
2851: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_site_Limit '|| l_return_status );
2852: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2853: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2854: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2855: END IF;
2856:
2857: x_site_cc_flag := l_return_status; --bug 4212981
2858: -- BUG 4158439
2864: THEN
2865: l_return_status := 'N' ;
2866: x_limit_curr_code := NULL ;
2867:
2868: OE_DEBUG_PUB.ADD(' Limits NULL for site MULTI ');
2869:
2870: END IF;
2871:
2872: IF NVL(l_return_status, 'N') = 'N' --bug 4582292
2881: );
2882:
2883: IF G_debug_flag = 'Y'
2884: THEN
2885: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_Single_Site_Limit '|| l_return_status );
2886: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2887: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2888: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2889: END IF;
2882:
2883: IF G_debug_flag = 'Y'
2884: THEN
2885: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_Single_Site_Limit '|| l_return_status );
2886: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2887: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2888: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2889: END IF;
2890:
2883: IF G_debug_flag = 'Y'
2884: THEN
2885: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_Single_Site_Limit '|| l_return_status );
2886: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2887: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2888: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2889: END IF;
2890:
2891: x_site_cc_flag := l_return_status; --bug 4212981
2884: THEN
2885: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_Single_Site_Limit '|| l_return_status );
2886: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
2887: OE_DEBUG_PUB.ADD(' x_trx_limit = '|| x_trx_limit );
2888: OE_DEBUG_PUB.ADD(' x_overall_limit = '|| x_overall_limit );
2889: END IF;
2890:
2891: x_site_cc_flag := l_return_status; --bug 4212981
2892:
2896: AND x_overall_limit is NULL
2897: THEN
2898: l_return_status := 'N' ;
2899: x_limit_curr_code := NULL ;
2900: OE_DEBUG_PUB.ADD(' Limits NULL for site SINGLE ');
2901: END IF;
2902:
2903: END IF;
2904:
2907: END IF;
2908:
2909: IF G_debug_flag = 'Y'
2910: THEN
2911: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_Multi_Limit ');
2912: END IF;
2913:
2914: EXCEPTION
2915: WHEN OTHERS THEN
2952: BEGIN
2953:
2954: IF G_debug_flag = 'Y'
2955: THEN
2956: OE_DEBUG_PUB.ADD('OEXUCRCB: IN Get_SIngle_Limit ');
2957: END IF;
2958:
2959: IF P_entity_Type = 'CUSTOMER'
2960: THEN
3000: END IF;
3001:
3002: IF G_debug_flag = 'Y'
3003: THEN
3004: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_SIngle_Limit ');
3005: END IF;
3006:
3007:
3008: EXCEPTION
3285: BEGIN
3286:
3287: IF G_debug_flag = 'Y'
3288: THEN
3289: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_usages ');
3290: OE_DEBUG_PUB.ADD(' p_entity_type = '|| p_entity_type );
3291: OE_DEBUG_PUB.ADD(' p_entity_id = '|| p_entity_id );
3292: OE_DEBUG_PUB.ADD(' p_limit_curr_code = '|| p_limit_curr_code );
3293: OE_DEBUG_PUB.ADD(' p_suppress_unused_usages_flag = '||
3286:
3287: IF G_debug_flag = 'Y'
3288: THEN
3289: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_usages ');
3290: OE_DEBUG_PUB.ADD(' p_entity_type = '|| p_entity_type );
3291: OE_DEBUG_PUB.ADD(' p_entity_id = '|| p_entity_id );
3292: OE_DEBUG_PUB.ADD(' p_limit_curr_code = '|| p_limit_curr_code );
3293: OE_DEBUG_PUB.ADD(' p_suppress_unused_usages_flag = '||
3294: p_suppress_unused_usages_flag );
3287: IF G_debug_flag = 'Y'
3288: THEN
3289: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_usages ');
3290: OE_DEBUG_PUB.ADD(' p_entity_type = '|| p_entity_type );
3291: OE_DEBUG_PUB.ADD(' p_entity_id = '|| p_entity_id );
3292: OE_DEBUG_PUB.ADD(' p_limit_curr_code = '|| p_limit_curr_code );
3293: OE_DEBUG_PUB.ADD(' p_suppress_unused_usages_flag = '||
3294: p_suppress_unused_usages_flag );
3295: OE_DEBUG_PUB.ADD(' p_global_exposure_flag '||
3288: THEN
3289: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_usages ');
3290: OE_DEBUG_PUB.ADD(' p_entity_type = '|| p_entity_type );
3291: OE_DEBUG_PUB.ADD(' p_entity_id = '|| p_entity_id );
3292: OE_DEBUG_PUB.ADD(' p_limit_curr_code = '|| p_limit_curr_code );
3293: OE_DEBUG_PUB.ADD(' p_suppress_unused_usages_flag = '||
3294: p_suppress_unused_usages_flag );
3295: OE_DEBUG_PUB.ADD(' p_global_exposure_flag '||
3296: p_global_exposure_flag );
3289: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_usages ');
3290: OE_DEBUG_PUB.ADD(' p_entity_type = '|| p_entity_type );
3291: OE_DEBUG_PUB.ADD(' p_entity_id = '|| p_entity_id );
3292: OE_DEBUG_PUB.ADD(' p_limit_curr_code = '|| p_limit_curr_code );
3293: OE_DEBUG_PUB.ADD(' p_suppress_unused_usages_flag = '||
3294: p_suppress_unused_usages_flag );
3295: OE_DEBUG_PUB.ADD(' p_global_exposure_flag '||
3296: p_global_exposure_flag );
3297:
3291: OE_DEBUG_PUB.ADD(' p_entity_id = '|| p_entity_id );
3292: OE_DEBUG_PUB.ADD(' p_limit_curr_code = '|| p_limit_curr_code );
3293: OE_DEBUG_PUB.ADD(' p_suppress_unused_usages_flag = '||
3294: p_suppress_unused_usages_flag );
3295: OE_DEBUG_PUB.ADD(' p_global_exposure_flag '||
3296: p_global_exposure_flag );
3297:
3298: OE_DEBUG_PUB.ADD(' ---------------------------------------------- ');
3299:
3294: p_suppress_unused_usages_flag );
3295: OE_DEBUG_PUB.ADD(' p_global_exposure_flag '||
3296: p_global_exposure_flag );
3297:
3298: OE_DEBUG_PUB.ADD(' ---------------------------------------------- ');
3299:
3300: END IF;
3301:
3302: OE_CREDIT_CHECK_UTIL.G_excl_curr_list := NULL ;
3303: l_exclude_flag := 'N' ;
3304:
3305: IF G_debug_flag = 'Y'
3306: THEN
3307: OE_DEBUG_PUB.ADD(' Credit Limit found ');
3308: END IF;
3309:
3310: ---------------------------------------------------
3311: -- First get all usage rule sets for this limit --
3314: IF NVL(p_default_limit_flag,'N') = 'N'
3315: THEN
3316: IF G_debug_flag = 'Y'
3317: THEN
3318: OE_DEBUG_PUB.ADD(' Into Default limit = N ');
3319: END IF;
3320:
3321: IF p_entity_type = 'PARTY'
3322: THEN
3456: -- Default limit = 'Y'
3457:
3458: IF G_debug_flag = 'Y'
3459: THEN
3460: OE_DEBUG_PUB.ADD(' Into Default limit = Y ');
3461: END IF ;
3462:
3463: FOR rule_set_rec IN dflt_rule_set_csr
3464: LOOP
3509: OE_CREDIT_CHECK_UTIL.G_excl_curr_list := l_excl_curr_list ;
3510:
3511: IF G_debug_flag = 'Y'
3512: THEN
3513: OE_DEBUG_PUB.ADD(' G_excl_curr_list => '||
3514: OE_CREDIT_CHECK_UTIL.G_excl_curr_list );
3515: END IF;
3516: -----------------------------------------------------------
3517: ---------------------------------------------
3602: THEN
3603:
3604: IF G_debug_flag = 'Y'
3605: THEN
3606: OE_DEBUG_PUB.ADD(' into suppress = Y ');
3607: END IF;
3608:
3609: -------------------------------------------------------
3610: -- include only the intersection of incl currencies --
3647:
3648: ELSE
3649: IF G_debug_flag = 'Y'
3650: THEN
3651: OE_DEBUG_PUB.ADD(' into suppress = N ');
3652: END IF;
3653:
3654: -------------------------------------------------------
3655: -- first include all incl currencies (minus excl) --
3752: x_include_all_flag := l_include_all_flag;
3753:
3754: IF G_debug_flag = 'Y'
3755: THEN
3756: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT get_usages ');
3757: END IF;
3758:
3759: EXCEPTION
3760: WHEN OTHERS THEN
3785: IS
3786: BEGIN
3787: IF G_debug_flag = 'Y'
3788: THEN
3789: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_System_parameters');
3790: END IF;
3791:
3792: -- BEGIN /* MOAC CREDIT CHECK CHANGE */
3793: -- Start Sys Param Change
3817: EXCEPTION
3818: WHEN NO_DATA_FOUND
3819: THEN
3820: x_system_parameter_rec := NULL ;
3821: OE_DEBUG_PUB.ADD(' Exception Get Syetem parameters');
3822: END ;
3823: */
3824: /* End MOAC CREDIT CHECK CHANGE */
3825:
3824: /* End MOAC CREDIT CHECK CHANGE */
3825:
3826: IF G_debug_flag = 'Y'
3827: THEN
3828: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT GET_System_parameters');
3829: END IF;
3830: EXCEPTION
3831: WHEN OTHERS THEN
3832: G_DBG_MSG := SUBSTR(sqlerrm,1,200);
3856: IS
3857: BEGIN
3858: IF G_debug_flag = 'Y'
3859: THEN
3860: OE_DEBUG_PUB.ADD('OEXUCRCB: In Get_Credit_Check_Rule_ID ');
3861: OE_DEBUG_PUB.ADD('p_order_type_id = '|| p_order_type_id );
3862: OE_DEBUG_PUB.ADD('p_calling_action = '|| p_calling_action );
3863: END IF;
3864:
3857: BEGIN
3858: IF G_debug_flag = 'Y'
3859: THEN
3860: OE_DEBUG_PUB.ADD('OEXUCRCB: In Get_Credit_Check_Rule_ID ');
3861: OE_DEBUG_PUB.ADD('p_order_type_id = '|| p_order_type_id );
3862: OE_DEBUG_PUB.ADD('p_calling_action = '|| p_calling_action );
3863: END IF;
3864:
3865: x_credit_rule_id := NULL ;
3858: IF G_debug_flag = 'Y'
3859: THEN
3860: OE_DEBUG_PUB.ADD('OEXUCRCB: In Get_Credit_Check_Rule_ID ');
3861: OE_DEBUG_PUB.ADD('p_order_type_id = '|| p_order_type_id );
3862: OE_DEBUG_PUB.ADD('p_calling_action = '|| p_calling_action );
3863: END IF;
3864:
3865: x_credit_rule_id := NULL ;
3866:
3867: IF p_calling_action in ('BOOKING','BOOKING_INLINE','AUTO','UPDATE')
3868: THEN
3869: IF G_debug_flag = 'Y'
3870: THEN
3871: OE_DEBUG_PUB.ADD('Selecting the order entry credit check rule');
3872: END IF;
3873: /*7194250
3874: SELECT ENTRY_CREDIT_CHECK_RULE_ID
3875: INTO x_credit_rule_id
3890: ELSIF p_calling_action = 'SHIPPING'
3891: THEN
3892: IF G_debug_flag = 'Y'
3893: THEN
3894: OE_DEBUG_PUB.Add('Selecting the shipping credit check rule');
3895: END IF;
3896:
3897: /*7194250
3898: SELECT SHIPPING_CREDIT_CHECK_RULE_ID
3915: THEN
3916:
3917: IF G_debug_flag = 'Y'
3918: THEN
3919: OE_DEBUG_PUB.Add('Selecting the picking credit check rule');
3920: END IF;
3921:
3922: /*7194250
3923: SELECT PICKING_CREDIT_CHECK_RULE_ID
3939: ELSIF p_calling_action = 'PACKING'
3940: THEN
3941: IF G_debug_flag = 'Y'
3942: THEN
3943: OE_DEBUG_PUB.Add('Selecting the packing credit check rule');
3944: END IF;
3945:
3946: /*7194250
3947: SELECT PACKING_CREDIT_CHECK_RULE_ID
3962:
3963: IF G_debug_flag = 'Y'
3964: THEN
3965:
3966: OE_DEBUG_PUB.ADD('OEXUCRCB: Credit Check Rule ID: '
3967: ||TO_CHAR(x_credit_rule_id) );
3968:
3969: OE_DEBUG_PUB.ADD('OEXUCRCB: Out NOCOPY Get_Credit_Check_Rule_ID');
3970: END IF;
3965:
3966: OE_DEBUG_PUB.ADD('OEXUCRCB: Credit Check Rule ID: '
3967: ||TO_CHAR(x_credit_rule_id) );
3968:
3969: OE_DEBUG_PUB.ADD('OEXUCRCB: Out NOCOPY Get_Credit_Check_Rule_ID');
3970: END IF;
3971:
3972:
3973: EXCEPTION
3973: EXCEPTION
3974: WHEN NO_DATA_FOUND
3975: THEN
3976: x_credit_rule_id := NULL ;
3977: OE_DEBUG_PUB.ADD('EXCEPTION:No credit check rule found');
3978: WHEN OTHERS THEN
3979: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
3980: OE_MSG_PUB.Add_Exc_Msg
3981: ( G_PKG_NAME
4005:
4006: BEGIN
4007: IF G_debug_flag = 'Y'
4008: THEN
4009: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_credit_check_level ');
4010: OE_DEBUG_PUB.ADD('p_calling_action = '|| p_calling_action );
4011: OE_DEBUG_PUB.ADD('p_order_type_id = '|| p_order_type_id );
4012: END IF;
4013:
4006: BEGIN
4007: IF G_debug_flag = 'Y'
4008: THEN
4009: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_credit_check_level ');
4010: OE_DEBUG_PUB.ADD('p_calling_action = '|| p_calling_action );
4011: OE_DEBUG_PUB.ADD('p_order_type_id = '|| p_order_type_id );
4012: END IF;
4013:
4014: l_level := NULL ;
4007: IF G_debug_flag = 'Y'
4008: THEN
4009: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_credit_check_level ');
4010: OE_DEBUG_PUB.ADD('p_calling_action = '|| p_calling_action );
4011: OE_DEBUG_PUB.ADD('p_order_type_id = '|| p_order_type_id );
4012: END IF;
4013:
4014: l_level := NULL ;
4015: l_credit_check_rule_id := NULL ;
4015: l_credit_check_rule_id := NULL ;
4016:
4017: IF G_debug_flag = 'Y'
4018: THEN
4019: OE_DEBUG_PUB.ADD(' Calling Get_Credit_Check_Rule_ID ');
4020: END IF;
4021:
4022: OE_Credit_CHECK_UTIL.Get_Credit_Check_Rule_ID
4023: ( p_calling_action => p_calling_action
4026: );
4027:
4028: IF G_debug_flag = 'Y'
4029: THEN
4030: OE_DEBUG_PUB.ADD(' out NOCOPY l_credit_check_rule_id = '||
4031: l_credit_check_rule_id );
4032: END IF;
4033:
4034: IF l_credit_check_rule_id is NULL
4034: IF l_credit_check_rule_id is NULL
4035: THEN
4036: IF G_debug_flag = 'Y'
4037: THEN
4038: OE_DEBUG_PUB.ADD(' No credit check attached ');
4039: END IF;
4040:
4041: l_level := NULL ;
4042:
4043: ELSE
4044:
4045: IF G_debug_flag = 'Y'
4046: THEN
4047: OE_DEBUG_PUB.ADD('OEXUCRCB: Call GET_credit_check_rule ');
4048: END IF;
4049:
4050: OE_CREDIT_CHECK_UTIL.GET_credit_check_rule
4051: ( p_credit_check_rule_id => l_credit_check_rule_id
4053: );
4054:
4055: IF G_debug_flag = 'Y'
4056: THEN
4057: OE_DEBUG_PUB.ADD('credit_check_level_code = '||
4058: l_credit_check_rule_rec.credit_check_level_code );
4059: END IF;
4060:
4061: l_level := l_credit_check_rule_rec.credit_check_level_code ;
4063: END IF;
4064:
4065: IF G_debug_flag = 'Y'
4066: THEN
4067: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_credit_check_level ' || l_level);
4068: END IF;
4069:
4070: RETURN( l_level ) ;
4071:
4100: BEGIN
4101:
4102: IF G_debug_flag = 'Y'
4103: THEN
4104: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_credit_check_rule ' );
4105: END IF;
4106:
4107: BEGIN
4108: IF G_debug_flag = 'Y'
4106:
4107: BEGIN
4108: IF G_debug_flag = 'Y'
4109: THEN
4110: OE_DEBUG_PUB.ADD(' Select for ID ' || p_credit_check_rule_id );
4111: END IF;
4112:
4113:
4114: SELECT
4201: EXCEPTION
4202: WHEN NO_DATA_FOUND
4203: THEN
4204: x_credit_check_rules_rec := NULL ;
4205: OE_DEBUG_PUB.ADD(' Get credit check rule No_Data_Found exception ' );
4206:
4207: WHEN TOO_MANY_ROWS
4208: THEN
4209: x_credit_check_rules_rec := NULL ;
4206:
4207: WHEN TOO_MANY_ROWS
4208: THEN
4209: x_credit_check_rules_rec := NULL ;
4210: OE_DEBUG_PUB.ADD(' Get credit check rule TOO_MANY_ROWS ');
4211: END ;
4212:
4213: IF G_debug_flag = 'Y'
4214: THEN
4211: END ;
4212:
4213: IF G_debug_flag = 'Y'
4214: THEN
4215: OE_DEBUG_PUB.ADD(' conversion_type = '
4216: || x_credit_check_rules_rec.conversion_type );
4217: END IF;
4218:
4219: BEGIN
4222: THEN
4223: BEGIN
4224: IF G_debug_flag = 'Y'
4225: THEN
4226: OE_DEBUG_PUB.ADD(' Select user_conversion_type ');
4227: END IF;
4228:
4229: SELECT
4230: user_conversion_type
4239: WHEN NO_DATA_FOUND
4240: THEN
4241: x_credit_check_rules_rec.user_conversion_type :=
4242: x_credit_check_rules_rec.conversion_type ;
4243: OE_DEBUG_PUB.ADD(' conversion type NO_DATA_FOUND ');
4244:
4245: WHEN TOO_MANY_ROWS
4246: THEN
4247: x_credit_check_rules_rec.user_conversion_type :=
4245: WHEN TOO_MANY_ROWS
4246: THEN
4247: x_credit_check_rules_rec.user_conversion_type :=
4248: x_credit_check_rules_rec.conversion_type ;
4249: OE_DEBUG_PUB.ADD(' conversion type TOO_MANY_ROWS ');
4250: END ;
4251:
4252: ELSE
4253: IF G_debug_flag = 'Y'
4251:
4252: ELSE
4253: IF G_debug_flag = 'Y'
4254: THEN
4255: OE_DEBUG_PUB.ADD(' No need for selct user_conversion_type ');
4256: END IF;
4257:
4258: x_credit_check_rules_rec.user_conversion_type :=
4259: x_credit_check_rules_rec.conversion_type ;
4263: END ;
4264:
4265: IF G_debug_flag = 'Y'
4266: THEN
4267: OE_DEBUG_PUB.ADD('Conversion type = '
4268: || x_credit_check_rules_rec.conversion_type );
4269:
4270: OE_DEBUG_PUB.ADD('User Conversion type = '
4271: || x_credit_check_rules_rec.user_conversion_type );
4266: THEN
4267: OE_DEBUG_PUB.ADD('Conversion type = '
4268: || x_credit_check_rules_rec.conversion_type );
4269:
4270: OE_DEBUG_PUB.ADD('User Conversion type = '
4271: || x_credit_check_rules_rec.user_conversion_type );
4272:
4273: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_credit_check_rule ' );
4274: END IF;
4269:
4270: OE_DEBUG_PUB.ADD('User Conversion type = '
4271: || x_credit_check_rules_rec.user_conversion_type );
4272:
4273: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_credit_check_rule ' );
4274: END IF;
4275:
4276: EXCEPTION
4277: WHEN OTHERS THEN
4303: l_precision NUMBER;
4304: l_ext_precision NUMBER;
4305: l_min_acct_unit NUMBER;
4306: --
4307: l_debug_level CONSTANT NUMBER := oe_debug_pub.g_debug_level;
4308: --
4309: BEGIN
4310:
4311: IF l_debug_level > 0 THEN
4308: --
4309: BEGIN
4310:
4311: IF l_debug_level > 0 THEN
4312: oe_debug_pub.add( 'OEXUCRCB: IN ROUNDED_AMOUNT ( ) WITH AMOUNT : ' ||P_UNROUNDED_AMOUNT , 5 ) ;
4313: END IF;
4314: FND_CURRENCY.GET_INFO(Currency_Code => p_currency_code,
4315: precision => l_precision,
4316: ext_precision => l_ext_precision,
4321: ELSE
4322: x_rounded_amount := ROUND(p_unrounded_amount/l_min_acct_unit)*l_min_acct_unit;
4323: END IF;
4324: IF l_debug_level > 0 THEN
4325: oe_debug_pub.add( 'OEXUCRCB: OUT ROUNDED AMOUNT ( ) WITH AMOUNT : '||X_ROUNDED_AMOUNT , 5 ) ;
4326: END IF;
4327: END Rounded_Amount;
4328: --ER 12363706 start
4329: --------------------------------------------------------------------------------------------
4343: l_conversion_status OE_CREDIT_CHECK_UTIL.CURR_TBL_TYPE;
4344: l_return_status VARCHAR2(30);
4345: BEGIN
4346: IF G_debug_flag = 'Y' THEN
4347: oe_debug_pub.add('Starting Update_Released_Amount');
4348: oe_debug_pub.add('Header ID:'||p_header_id);
4349: oe_debug_pub.add('Hold Release ID:'||p_hold_release_id);
4350: END IF;
4351: l_calling_action := OE_Verify_Payment_PUB.Which_Rule(p_header_id => p_header_id);
4344: l_return_status VARCHAR2(30);
4345: BEGIN
4346: IF G_debug_flag = 'Y' THEN
4347: oe_debug_pub.add('Starting Update_Released_Amount');
4348: oe_debug_pub.add('Header ID:'||p_header_id);
4349: oe_debug_pub.add('Hold Release ID:'||p_hold_release_id);
4350: END IF;
4351: l_calling_action := OE_Verify_Payment_PUB.Which_Rule(p_header_id => p_header_id);
4352: oe_debug_pub.add('slagiset: l_calling_action ' || l_calling_action);
4345: BEGIN
4346: IF G_debug_flag = 'Y' THEN
4347: oe_debug_pub.add('Starting Update_Released_Amount');
4348: oe_debug_pub.add('Header ID:'||p_header_id);
4349: oe_debug_pub.add('Hold Release ID:'||p_hold_release_id);
4350: END IF;
4351: l_calling_action := OE_Verify_Payment_PUB.Which_Rule(p_header_id => p_header_id);
4352: oe_debug_pub.add('slagiset: l_calling_action ' || l_calling_action);
4353: OE_HEADER_UTIL.QUERY_ROW ( p_header_id => p_header_id, x_header_rec => l_header_rec);
4348: oe_debug_pub.add('Header ID:'||p_header_id);
4349: oe_debug_pub.add('Hold Release ID:'||p_hold_release_id);
4350: END IF;
4351: l_calling_action := OE_Verify_Payment_PUB.Which_Rule(p_header_id => p_header_id);
4352: oe_debug_pub.add('slagiset: l_calling_action ' || l_calling_action);
4353: OE_HEADER_UTIL.QUERY_ROW ( p_header_id => p_header_id, x_header_rec => l_header_rec);
4354: OE_CREDIT_CHECK_UTIL.Get_Credit_Check_Rule_ID ( p_calling_action => l_calling_action , p_order_type_id => l_header_rec.order_type_id , x_credit_rule_id => l_credit_check_rule_id );
4355: oe_debug_pub.add('slagiset: l_credit_check_rule_id ' || l_credit_check_rule_id);
4356:
4351: l_calling_action := OE_Verify_Payment_PUB.Which_Rule(p_header_id => p_header_id);
4352: oe_debug_pub.add('slagiset: l_calling_action ' || l_calling_action);
4353: OE_HEADER_UTIL.QUERY_ROW ( p_header_id => p_header_id, x_header_rec => l_header_rec);
4354: OE_CREDIT_CHECK_UTIL.Get_Credit_Check_Rule_ID ( p_calling_action => l_calling_action , p_order_type_id => l_header_rec.order_type_id , x_credit_rule_id => l_credit_check_rule_id );
4355: oe_debug_pub.add('slagiset: l_credit_check_rule_id ' || l_credit_check_rule_id);
4356:
4357: OE_CREDIT_CHECK_UTIL.GET_credit_check_rule ( p_header_id => p_header_id ,
4358: p_credit_check_rule_id => l_credit_check_rule_id ,
4359: x_credit_check_rules_rec => l_credit_check_rule_rec );
4370: p_all_lines => 'Y' ,
4371: x_amount => l_released_amount ,
4372: x_conversion_status => l_conversion_status ,
4373: x_return_status => l_return_status );
4374: oe_debug_pub.add('slagiset: l_released_amount ' || l_released_amount);
4375: oe_debug_pub.add('slagiset: l_return_status ' || l_return_status);
4376: UPDATE OE_HOLD_RELEASES
4377: SET Released_Order_Amount=l_released_amount,
4378: Released_Curr_code =l_header_rec.transactional_curr_code
4371: x_amount => l_released_amount ,
4372: x_conversion_status => l_conversion_status ,
4373: x_return_status => l_return_status );
4374: oe_debug_pub.add('slagiset: l_released_amount ' || l_released_amount);
4375: oe_debug_pub.add('slagiset: l_return_status ' || l_return_status);
4376: UPDATE OE_HOLD_RELEASES
4377: SET Released_Order_Amount=l_released_amount,
4378: Released_Curr_code =l_header_rec.transactional_curr_code
4379: WHERE hold_release_id =p_hold_release_id;
4377: SET Released_Order_Amount=l_released_amount,
4378: Released_Curr_code =l_header_rec.transactional_curr_code
4379: WHERE hold_release_id =p_hold_release_id;
4380: IF G_debug_flag = 'Y' THEN
4381: oe_debug_pub.add('Ending Update_Released_Amount');
4382: END IF;
4383: EXCEPTION
4384: WHEN OTHERS THEN
4385: IF G_debug_flag = 'Y' THEN
4382: END IF;
4383: EXCEPTION
4384: WHEN OTHERS THEN
4385: IF G_debug_flag = 'Y' THEN
4386: oe_debug_pub.add('Error in Update_Released_Amount:'||SUBSTR(SQLERRM,1,250));
4387: END IF;
4388: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4389: END Update_Released_Amount;
4390:
4398: IS
4399: l_hold_source_id NUMBER;
4400: BEGIN
4401: IF G_debug_flag = 'Y' THEN
4402: oe_debug_pub.add('Starting Update_Credit_Profile_Level');
4403: oe_debug_pub.add('OEXUCRCB: OE_Credit_Engine_GRP.G_Credit_Profile_Level ' || OE_Credit_Engine_GRP.G_Credit_Profile_Level);
4404: END IF;
4405:
4406: IF p_hold_source_rec.hold_source_id IS NOT NULL
4399: l_hold_source_id NUMBER;
4400: BEGIN
4401: IF G_debug_flag = 'Y' THEN
4402: oe_debug_pub.add('Starting Update_Credit_Profile_Level');
4403: oe_debug_pub.add('OEXUCRCB: OE_Credit_Engine_GRP.G_Credit_Profile_Level ' || OE_Credit_Engine_GRP.G_Credit_Profile_Level);
4404: END IF;
4405:
4406: IF p_hold_source_rec.hold_source_id IS NOT NULL
4407: THEN
4406: IF p_hold_source_rec.hold_source_id IS NOT NULL
4407: THEN
4408: l_hold_source_id := p_hold_source_rec.hold_source_id;
4409: IF G_debug_flag = 'Y' THEN
4410: oe_debug_pub.add('OEXUCRCB: Hold Source Id is passed: '||p_hold_source_rec.hold_source_id);
4411: END IF;
4412:
4413: ELSIF p_hold_source_rec.HOLD_ENTITY_CODE is NOT NULL AND
4414: p_hold_source_rec.HOLD_ENTITY_ID is NOT NULL
4413: ELSIF p_hold_source_rec.HOLD_ENTITY_CODE is NOT NULL AND
4414: p_hold_source_rec.HOLD_ENTITY_ID is NOT NULL
4415: THEN
4416: IF G_debug_flag = 'Y' THEN
4417: oe_debug_pub.add('OEXUCRCB: Retrieving Hold Source Id');
4418: END IF;
4419:
4420: BEGIN
4421: IF p_hold_source_rec.line_id IS NOT NULL THEN
4442: AND NVL(HOLD_UNTIL_DATE, SYSDATE + 1) > SYSDATE;
4443: END IF;
4444: EXCEPTION
4445: WHEN OTHERS THEN
4446: oe_debug_pub.ADD('OEXUCRCB: Exception raised!');
4447: -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4448:
4449: END;
4450: IF G_debug_flag = 'Y' THEN
4447: -- RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4448:
4449: END;
4450: IF G_debug_flag = 'Y' THEN
4451: oe_debug_pub.add('OEXUCRCB: Retrieved Hold Source Id: '|| l_hold_source_id);
4452: END IF;
4453: END IF;
4454:
4455: UPDATE OE_ORDER_HOLDS_ALL
4458: Last_Updated_By = FND_GLOBAL.user_id
4459: WHERE hold_source_id = l_hold_source_id;
4460:
4461: IF G_debug_flag = 'Y' THEN
4462: oe_debug_pub.add('OEXUCRCB: Ending Update_Credit_Profile_Level');
4463: END IF;
4464:
4465: EXCEPTION
4466: WHEN OTHERS THEN
4464:
4465: EXCEPTION
4466: WHEN OTHERS THEN
4467: IF G_debug_flag = 'Y' THEN
4468: oe_debug_pub.add('Error in Update_Credit_Profile_Level:'||SUBSTR(SQLERRM,1,250));
4469: END IF;
4470: RAISE FND_API.G_EXC_UNEXPECTED_ERROR;
4471: END Update_Credit_Profile_Level;
4472: --ER 12363706 end
4508: BEGIN
4509:
4510: IF G_debug_flag = 'Y'
4511: THEN
4512: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_transaction_amount for '
4513: || p_header_id );
4514: END IF;
4515:
4516: -- For line level, if it is customer level credit check
4526: IF p_credit_check_rule_rec.credit_check_level_code = 'LINE'
4527: THEN
4528: IF G_debug_flag = 'Y'
4529: THEN
4530: OE_DEBUG_PUB.ADD('In Line level ');
4531: END IF;
4532:
4533: IF p_customer_id IS NOT NULL -- line level CC
4534: THEN
4538: ---returns are not included
4539:
4540: IF G_debug_flag = 'Y'
4541: THEN
4542: OE_DEBUG_PUB.ADD('Line - Customer level select,no returns ');
4543: END IF;
4544:
4545: SELECT SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
4546: NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ) --TaxER
4578: ---returns are included
4579:
4580: IF G_debug_flag = 'Y'
4581: THEN
4582: OE_DEBUG_PUB.ADD('Line - Customer level select,with returns ');
4583: END IF;
4584:
4585: SELECT
4586: SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
4620: END IF; ----end for checking if returns are included
4621:
4622: IF G_debug_flag = 'Y'
4623: THEN
4624: OE_DEBUG_PUB.ADD('Check for Committment profile ');
4625: END IF;
4626:
4627: IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
4628: BEGIN
4627: IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
4628: BEGIN
4629: IF G_debug_flag = 'Y'
4630: THEN
4631: OE_DEBUG_PUB.ADD('Customer level commitment select ');
4632: END IF;
4633:
4634: SELECT NVL(SUM(P.commitment_applied_amount), 0)
4635: INTO l_commitment
4664:
4665: EXCEPTION
4666: WHEN NO_DATA_FOUND
4667: THEN
4668: OE_DEBUG_PUB.ADD(' NO commitment-1 found ');
4669: l_commitment := 0 ;
4670:
4671: WHEN TOO_MANY_ROWS
4672: THEN
4669: l_commitment := 0 ;
4670:
4671: WHEN TOO_MANY_ROWS
4672: THEN
4673: OE_DEBUG_PUB.ADD(' Too many rows exception NO commtment-1');
4674: l_commitment := 0 ;
4675: END ;
4676: END IF; --- commitment
4677:
4684: THEN
4685: ---returns are not included
4686: IF G_debug_flag = 'Y'
4687: THEN
4688: OE_DEBUG_PUB.ADD(' Process trx freights - Line level customer without returns ');
4689: END IF;
4690:
4691: BEGIN
4692: SELECT
4736:
4737: EXCEPTION
4738: WHEN NO_DATA_FOUND
4739: THEN
4740: OE_DEBUG_PUB.ADD(' NO Freights found ');
4741: l_freights := 0 ;
4742:
4743: END ;
4744: ELSE
4744: ELSE
4745: ---returns are included
4746: IF G_debug_flag = 'Y'
4747: THEN
4748: OE_DEBUG_PUB.ADD(' Process trx freights - Line level customer,with returns ');
4749: END IF;
4750:
4751: BEGIN
4752: SELECT
4794: (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
4795: EXCEPTION
4796: WHEN NO_DATA_FOUND
4797: THEN
4798: OE_DEBUG_PUB.ADD(' NO Freights found ');
4799: l_freights := 0 ;
4800:
4801: END ;
4802:
4813: ---returns are not included
4814:
4815: IF G_debug_flag = 'Y'
4816: THEN
4817: OE_DEBUG_PUB.ADD('Line - Site level select ');
4818: END IF;
4819:
4820: SELECT SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
4821: NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ) --TaxER
4849: ---returns are included
4850:
4851: IF G_debug_flag = 'Y'
4852: THEN
4853: OE_DEBUG_PUB.ADD('Line - Site level select,including returns ');
4854: END IF;
4855:
4856: SELECT
4857: SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
4885: END IF; ---end of checking for returns
4886:
4887: IF G_debug_flag = 'Y'
4888: THEN
4889: OE_DEBUG_PUB.ADD('Check for Committment ');
4890: END IF;
4891:
4892: IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
4893: BEGIN
4922:
4923: EXCEPTION
4924: WHEN NO_DATA_FOUND
4925: THEN
4926: OE_DEBUG_PUB.ADD(' NO commitment-1 found ');
4927: l_commitment := 0 ;
4928:
4929: WHEN TOO_MANY_ROWS
4930: THEN
4927: l_commitment := 0 ;
4928:
4929: WHEN TOO_MANY_ROWS
4930: THEN
4931: OE_DEBUG_PUB.ADD(' Too many rows excepn NO commitment-1 found ');
4932: l_commitment := 0 ;
4933: END ;
4934: END IF; --- commitment
4935:
4943: ---returns are not included
4944:
4945: IF G_debug_flag = 'Y'
4946: THEN
4947: OE_DEBUG_PUB.ADD(' Process trx freights - Line level site,no returns ');
4948: END IF;
4949:
4950: BEGIN
4951: SELECT
4990: (l.payment_type_code IS NULL AND h.payment_type_code IS NULL));
4991: EXCEPTION
4992: WHEN NO_DATA_FOUND
4993: THEN
4994: OE_DEBUG_PUB.ADD(' NO Freights found ');
4995: l_freights := 0 ;
4996:
4997: END ;
4998:
5000: ---returns are included
5001:
5002: IF G_debug_flag = 'Y'
5003: THEN
5004: OE_DEBUG_PUB.ADD(' Process trx freights - Line level site, with returns ');
5005: END IF;
5006:
5007: BEGIN
5008: SELECT
5047:
5048: EXCEPTION
5049: WHEN NO_DATA_FOUND
5050: THEN
5051: OE_DEBUG_PUB.ADD(' NO Freights found ');
5052: l_freights := 0 ;
5053:
5054: END ;
5055:
5067: ---returns are not included
5068:
5069: IF G_debug_flag = 'Y'
5070: THEN
5071: OE_DEBUG_PUB.ADD('In Order header level select ');
5072: END IF;
5073:
5074: SELECT SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
5075: NVL(NVL(l.tax_line_value,l.tax_value),0), 0 ) --TaxER
5102: ---returns are included
5103:
5104: IF G_debug_flag = 'Y'
5105: THEN
5106: OE_DEBUG_PUB.ADD('In Order header level select, with returns ');
5107: END IF;
5108:
5109: SELECT
5110: SUM ( DECODE( p_credit_check_rule_rec.include_tax_flag , 'Y',
5138: END IF; ---end for checking if returns are included
5139:
5140: IF G_debug_flag = 'Y'
5141: THEN
5142: OE_DEBUG_PUB.ADD('Check for Committment ');
5143: END IF ;
5144:
5145: IF OE_Commitment_PVT.Do_Commitment_Sequencing
5146: THEN
5146: THEN
5147: BEGIN
5148: IF G_debug_flag = 'Y'
5149: THEN
5150: OE_DEBUG_PUB.ADD('In Order header commitment');
5151: END IF;
5152:
5153: SELECT NVL(SUM(P.commitment_applied_amount), 0)
5154: INTO l_commitment
5180:
5181: EXCEPTION
5182: WHEN NO_DATA_FOUND
5183: THEN
5184: OE_DEBUG_PUB.ADD(' NO commitment-1 found ');
5185: l_commitment := 0 ;
5186:
5187: WHEN TOO_MANY_ROWS
5188: THEN
5185: l_commitment := 0 ;
5186:
5187: WHEN TOO_MANY_ROWS
5188: THEN
5189: OE_DEBUG_PUB.ADD(' Too many rows excepn NO commitment-1 found ');
5190: l_commitment := 0 ;
5191: END ;
5192: END IF; -- commitment
5193:
5198: -- check the check to determine if credit check is required
5199: --
5200: IF G_MULTIPLE_PAYMENTS_ENABLED THEN
5201: IF G_debug_flag = 'Y' THEN
5202: OE_DEBUG_PUB.ADD('In Order header prepayment');
5203: END IF;
5204:
5205: SELECT SUM(P.prepaid_amount)
5206: INTO l_prepayment
5219: ---returns are not included
5220:
5221: IF G_debug_flag = 'Y'
5222: THEN
5223: OE_DEBUG_PUB.ADD(' Process trx freights - order level ');
5224: END IF;
5225:
5226: BEGIN
5227:
5298: ---returns are included
5299:
5300: IF G_debug_flag = 'Y'
5301: THEN
5302: OE_DEBUG_PUB.ADD(' Process trx freights - order level,with returns ');
5303: END IF;
5304:
5305: BEGIN
5306: SELECT
5380: END IF; -- Hdr or line
5381:
5382: IF G_debug_flag = 'Y'
5383: THEN
5384: OE_DEBUG_PUB.ADD(' l_order_value = '|| l_order_value );
5385: OE_DEBUG_PUB.ADD(' l_commitment = '|| l_commitment );
5386: OE_DEBUG_PUB.ADD(' l_freights = ' || l_freights );
5387: END IF;
5388:
5381:
5382: IF G_debug_flag = 'Y'
5383: THEN
5384: OE_DEBUG_PUB.ADD(' l_order_value = '|| l_order_value );
5385: OE_DEBUG_PUB.ADD(' l_commitment = '|| l_commitment );
5386: OE_DEBUG_PUB.ADD(' l_freights = ' || l_freights );
5387: END IF;
5388:
5389:
5382: IF G_debug_flag = 'Y'
5383: THEN
5384: OE_DEBUG_PUB.ADD(' l_order_value = '|| l_order_value );
5385: OE_DEBUG_PUB.ADD(' l_commitment = '|| l_commitment );
5386: OE_DEBUG_PUB.ADD(' l_freights = ' || l_freights );
5387: END IF;
5388:
5389:
5390: -- convert amount
5390: -- convert amount
5391: BEGIN
5392: IF G_debug_flag = 'Y'
5393: THEN
5394: OE_DEBUG_PUB.Add(' GL_CURRENCY = '||
5395: OE_Credit_Engine_GRP.GL_currency );
5396: END IF;
5397: -- Bug 8249878
5398: /* l_order_value := NVL(l_order_value,0)
5403: - NVL(l_commitment,0)
5404: + NVL(l_freights,0);
5405: IF G_debug_flag = 'Y'
5406: THEN
5407: OE_DEBUG_PUB.ADD(' Total order amount = '|| l_order_value );
5408: END IF;
5409:
5410: l_limit_order_value :=
5411: OE_CREDIT_CHECK_UTIL.CONVERT_CURRENCY_AMOUNT
5429: OE_CREDIT_CHECK_UTIL.g_current_order_value := x_amount ;
5430:
5431: IF G_debug_flag = 'Y'
5432: THEN
5433: OE_DEBUG_PUB.Add(' g_current_order_value = '||
5434: OE_CREDIT_CHECK_UTIL.g_current_order_value );
5435: OE_DEBUG_PUB.Add(' Final trx check order amount = '|| x_amount,1 );
5436: OE_DEBUG_PUB.Add(' ====================================');
5437: END IF;
5431: IF G_debug_flag = 'Y'
5432: THEN
5433: OE_DEBUG_PUB.Add(' g_current_order_value = '||
5434: OE_CREDIT_CHECK_UTIL.g_current_order_value );
5435: OE_DEBUG_PUB.Add(' Final trx check order amount = '|| x_amount,1 );
5436: OE_DEBUG_PUB.Add(' ====================================');
5437: END IF;
5438:
5439: IF G_debug_flag = 'Y'
5432: THEN
5433: OE_DEBUG_PUB.Add(' g_current_order_value = '||
5434: OE_CREDIT_CHECK_UTIL.g_current_order_value );
5435: OE_DEBUG_PUB.Add(' Final trx check order amount = '|| x_amount,1 );
5436: OE_DEBUG_PUB.Add(' ====================================');
5437: END IF;
5438:
5439: IF G_debug_flag = 'Y'
5440: THEN
5437: END IF;
5438:
5439: IF G_debug_flag = 'Y'
5440: THEN
5441: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_transaction_amount ');
5442: END IF;
5443:
5444: EXCEPTION
5445: WHEN GL_CURRENCY_API.NO_RATE
5443:
5444: EXCEPTION
5445: WHEN GL_CURRENCY_API.NO_RATE
5446: THEN
5447: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE ');
5448: x_conversion_status(1).usage_curr_code := p_transaction_curr_code;
5449: fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
5450: OE_DEBUG_PUB.ADD('Exception table added ');
5451:
5446: THEN
5447: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE ');
5448: x_conversion_status(1).usage_curr_code := p_transaction_curr_code;
5449: fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
5450: OE_DEBUG_PUB.ADD('Exception table added ');
5451:
5452: WHEN NO_DATA_FOUND
5453: THEN
5454: x_amount := 0 ;
5451:
5452: WHEN NO_DATA_FOUND
5453: THEN
5454: x_amount := 0 ;
5455: OE_DEBUG_PUB.ADD('EXCEPTION: NO_DATA_FOUND ');
5456:
5457: WHEN TOO_MANY_ROWS
5458: THEN
5459: x_amount := 0 ;
5456:
5457: WHEN TOO_MANY_ROWS
5458: THEN
5459: x_amount := 0 ;
5460: OE_DEBUG_PUB.ADD('EXCEPTION: TOO_MANY_ROWS ');
5461:
5462: WHEN OTHERS THEN
5463: G_DBG_MSG := SUBSTR(sqlerrm,1,200);
5464: OE_DEBUG_PUB.ADD('EXCEPTION = '||
5460: OE_DEBUG_PUB.ADD('EXCEPTION: TOO_MANY_ROWS ');
5461:
5462: WHEN OTHERS THEN
5463: G_DBG_MSG := SUBSTR(sqlerrm,1,200);
5464: OE_DEBUG_PUB.ADD('EXCEPTION = '||
5465: SUBSTR(sqlerrm,1,200) );
5466: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
5467:
5468: IF FND_MSG_PUB.Check_Msg_Level(FND_MSG_PUB.G_MSG_LVL_UNEXP_ERROR)
5503: l_dummy VARCHAR2(30);
5504: BEGIN
5505: IF G_debug_flag = 'Y'
5506: THEN
5507: OE_DEBUG_PUB.ADD('OEXUCRCB: In Get_Past_Due_Invoice');
5508: END IF;
5509:
5510: -- Initialize return status to success
5511: x_return_status := FND_API.G_RET_STS_SUCCESS;
5515: -- Bug2408466
5516: -- REmove the OLD code with DECODES
5517: IF G_debug_flag = 'Y'
5518: THEN
5519: OE_DEBUG_PUB.ADD('maximum_days_past_due ==> '||
5520: p_credit_check_rule_rec.maximum_days_past_due,1 );
5521:
5522: OE_DEBUG_PUB.ADD('p_credit_level ==> '|| p_credit_level );
5523: OE_DEBUG_PUB.ADD('p_include_all_flag ==> '|| p_include_all_flag);
5518: THEN
5519: OE_DEBUG_PUB.ADD('maximum_days_past_due ==> '||
5520: p_credit_check_rule_rec.maximum_days_past_due,1 );
5521:
5522: OE_DEBUG_PUB.ADD('p_credit_level ==> '|| p_credit_level );
5523: OE_DEBUG_PUB.ADD('p_include_all_flag ==> '|| p_include_all_flag);
5524: OE_DEBUG_PUB.ADD('p_global_exposure_flag ==> '||
5525: p_global_exposure_flag );
5526: END IF;
5519: OE_DEBUG_PUB.ADD('maximum_days_past_due ==> '||
5520: p_credit_check_rule_rec.maximum_days_past_due,1 );
5521:
5522: OE_DEBUG_PUB.ADD('p_credit_level ==> '|| p_credit_level );
5523: OE_DEBUG_PUB.ADD('p_include_all_flag ==> '|| p_include_all_flag);
5524: OE_DEBUG_PUB.ADD('p_global_exposure_flag ==> '||
5525: p_global_exposure_flag );
5526: END IF;
5527:
5520: p_credit_check_rule_rec.maximum_days_past_due,1 );
5521:
5522: OE_DEBUG_PUB.ADD('p_credit_level ==> '|| p_credit_level );
5523: OE_DEBUG_PUB.ADD('p_include_all_flag ==> '|| p_include_all_flag);
5524: OE_DEBUG_PUB.ADD('p_global_exposure_flag ==> '||
5525: p_global_exposure_flag );
5526: END IF;
5527:
5528: IF NVL(p_global_exposure_flag,'N') = 'N'
5529: THEN
5530: BEGIN
5531: IF G_debug_flag = 'Y'
5532: THEN
5533: OE_DEBUG_PUB.Add(' Into NOT Global ');
5534: END IF;
5535: --bug 4174163
5536: --IF NVL(p_credit_check_rule_rec.maximum_days_past_due,0) > 0
5537: IF p_credit_check_rule_rec.maximum_days_past_due is not null
5541: THEN
5542: BEGIN
5543: IF G_debug_flag = 'Y'
5544: THEN
5545: OE_DEBUG_PUB.Add(' Into CUSTOMER ');
5546: END IF;
5547:
5548: FOR i IN 1..p_usage_curr.count
5549: LOOP
5548: FOR i IN 1..p_usage_curr.count
5549: LOOP
5550: IF G_debug_flag = 'Y'
5551: THEN
5552: OE_DEBUG_PUB.Add('Currency code = '
5553: || p_usage_curr(i).usage_curr_code );
5554: END IF;
5555: BEGIN
5556: SELECT 'Y'
5570: x_exist_flag := 'Y' ;
5571:
5572: IF G_debug_flag = 'Y'
5573: THEN
5574: OE_DEBUG_PUB.Add('Invoices found ' );
5575: END IF;
5576:
5577: EXIT;
5578:
5577: EXIT;
5578:
5579: EXCEPTION
5580: WHEN NO_DATA_FOUND THEN
5581: OE_DEBUG_PUB.Add(' No Invoices Past due found ' );
5582:
5583: x_exist_flag := 'N' ;
5584:
5585: WHEN TOO_MANY_ROWS THEN
5582:
5583: x_exist_flag := 'N' ;
5584:
5585: WHEN TOO_MANY_ROWS THEN
5586: OE_DEBUG_PUB.Add('Invoices found ' );
5587: x_exist_flag := 'Y' ;
5588: EXIT;
5589: END;
5590: END LOOP;
5593: ELSE
5594: BEGIN
5595: IF G_debug_flag = 'Y'
5596: THEN
5597: OE_DEBUG_PUB.Add(' Into SITE ');
5598: END IF;
5599:
5600: FOR i IN 1..p_usage_curr.count
5601: LOOP
5600: FOR i IN 1..p_usage_curr.count
5601: LOOP
5602: IF G_debug_flag = 'Y'
5603: THEN
5604: OE_DEBUG_PUB.Add(' Currency code = '
5605: || p_usage_curr(i).usage_curr_code );
5606: END IF;
5607: BEGIN
5608: SELECT 'Y'
5621: x_exist_flag := 'Y' ;
5622:
5623: IF G_debug_flag = 'Y'
5624: THEN
5625: OE_DEBUG_PUB.Add('Invoices found ' );
5626: END IF;
5627:
5628: EXIT;
5629:
5628: EXIT;
5629:
5630: EXCEPTION
5631: WHEN NO_DATA_FOUND THEN
5632: OE_DEBUG_PUB.Add('No Invoices Past due found in this curr' );
5633: x_exist_flag := 'N' ;
5634: WHEN TOO_MANY_ROWS THEN
5635: OE_DEBUG_PUB.Add('Invoices found ' );
5636: x_exist_flag := 'Y' ;
5631: WHEN NO_DATA_FOUND THEN
5632: OE_DEBUG_PUB.Add('No Invoices Past due found in this curr' );
5633: x_exist_flag := 'N' ;
5634: WHEN TOO_MANY_ROWS THEN
5635: OE_DEBUG_PUB.Add('Invoices found ' );
5636: x_exist_flag := 'Y' ;
5637: EXIT;
5638: END;
5639: END LOOP;
5642:
5643: ELSE
5644: IF G_debug_flag = 'Y'
5645: THEN
5646: OE_DEBUG_PUB.Add(' No need to check ' );
5647: END IF;
5648: x_exist_flag := 'N' ;
5649: END IF;
5650: END;
5653: THEN
5654: BEGIN
5655: IF G_debug_flag = 'Y'
5656: THEN
5657: OE_DEBUG_PUB.Add(' Into GLOBAL checking ');
5658: END IF;
5659: --bug 4174163
5660: --IF NVL(p_credit_check_rule_rec.maximum_days_past_due,0) > 0
5661: IF p_credit_check_rule_rec.maximum_days_past_due is not null
5665: THEN
5666: BEGIN
5667: IF G_debug_flag = 'Y'
5668: THEN
5669: OE_DEBUG_PUB.Add(' Into CUSTOMER ');
5670: END IF;
5671:
5672: FOR i IN 1..p_usage_curr.count
5673: LOOP
5672: FOR i IN 1..p_usage_curr.count
5673: LOOP
5674: IF G_debug_flag = 'Y'
5675: THEN
5676: OE_DEBUG_PUB.Add('Currency code = '
5677: || p_usage_curr(i).usage_curr_code );
5678: END IF;
5679:
5680: BEGIN
5695: x_exist_flag := 'Y' ;
5696:
5697: IF G_debug_flag = 'Y'
5698: THEN
5699: OE_DEBUG_PUB.Add('Invoices found ' );
5700: end if;
5701:
5702: EXIT;
5703:
5702: EXIT;
5703:
5704: EXCEPTION
5705: WHEN NO_DATA_FOUND THEN
5706: OE_DEBUG_PUB.Add(' No Invoices Past due found ' );
5707:
5708: x_exist_flag := 'N' ;
5709:
5710: WHEN TOO_MANY_ROWS THEN
5707:
5708: x_exist_flag := 'N' ;
5709:
5710: WHEN TOO_MANY_ROWS THEN
5711: OE_DEBUG_PUB.Add('Invoices found ' );
5712: x_exist_flag := 'Y' ;
5713: EXIT;
5714: END;
5715: END LOOP;
5715: END LOOP;
5716: END ;
5717: END IF;
5718: ELSE
5719: OE_DEBUG_PUB.Add(' No need to check ' );
5720: x_exist_flag := 'N' ;
5721: END IF;
5722:
5723:
5732: and p_credit_check_rule_rec.maximum_days_past_due >= 0
5733: THEN
5734: IF G_debug_flag = 'Y'
5735: THEN
5736: oe_debug_pub.add('calling get_invoices_over_duedate ');
5737: END IF;
5738:
5739: OE_CREDIT_EXPOSURE_PVT.get_invoices_over_duedate
5740: ( p_customer_id => p_customer_id
5760: END IF ;
5761:
5762: IF G_debug_flag = 'Y'
5763: THEN
5764: OE_DEBUG_PUB.Add(' x_exist_flag = '|| x_exist_flag );
5765: OE_DEBUG_PUB.ADD('OEXUCRCB: Out NOCOPY Get_Past_Due_Invoice');
5766: END IF;
5767:
5768: EXCEPTION
5761:
5762: IF G_debug_flag = 'Y'
5763: THEN
5764: OE_DEBUG_PUB.Add(' x_exist_flag = '|| x_exist_flag );
5765: OE_DEBUG_PUB.ADD('OEXUCRCB: Out NOCOPY Get_Past_Due_Invoice');
5766: END IF;
5767:
5768: EXCEPTION
5769: WHEN OTHERS THEN
8894:
8895: BEGIN
8896: IF G_debug_flag = 'Y'
8897: THEN
8898: OE_DEBUG_PUB.ADD('OEXUCRCB: IN Get_order_exposure',1);
8899: END IF;
8900:
8901: x_return_status := FND_API.G_RET_STS_SUCCESS;
8902:
8901: x_return_status := FND_API.G_RET_STS_SUCCESS;
8902:
8903: IF G_debug_flag = 'Y'
8904: THEN
8905: OE_DEBUG_PUB.Add('exposure Input parameters ');
8906: OE_DEBUG_PUB.Add(' ');
8907: OE_DEBUG_PUB.Add('--------------------------------------------');
8908: OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
8909: OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
8902:
8903: IF G_debug_flag = 'Y'
8904: THEN
8905: OE_DEBUG_PUB.Add('exposure Input parameters ');
8906: OE_DEBUG_PUB.Add(' ');
8907: OE_DEBUG_PUB.Add('--------------------------------------------');
8908: OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
8909: OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
8910: OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id);
8903: IF G_debug_flag = 'Y'
8904: THEN
8905: OE_DEBUG_PUB.Add('exposure Input parameters ');
8906: OE_DEBUG_PUB.Add(' ');
8907: OE_DEBUG_PUB.Add('--------------------------------------------');
8908: OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
8909: OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
8910: OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id);
8911: OE_DEBUG_PUB.Add('p_credit_check_rule_id = '
8904: THEN
8905: OE_DEBUG_PUB.Add('exposure Input parameters ');
8906: OE_DEBUG_PUB.Add(' ');
8907: OE_DEBUG_PUB.Add('--------------------------------------------');
8908: OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
8909: OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
8910: OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id);
8911: OE_DEBUG_PUB.Add('p_credit_check_rule_id = '
8912: || p_credit_check_rule_rec.credit_check_rule_id
8905: OE_DEBUG_PUB.Add('exposure Input parameters ');
8906: OE_DEBUG_PUB.Add(' ');
8907: OE_DEBUG_PUB.Add('--------------------------------------------');
8908: OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
8909: OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
8910: OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id);
8911: OE_DEBUG_PUB.Add('p_credit_check_rule_id = '
8912: || p_credit_check_rule_rec.credit_check_rule_id
8913: );
8906: OE_DEBUG_PUB.Add(' ');
8907: OE_DEBUG_PUB.Add('--------------------------------------------');
8908: OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
8909: OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
8910: OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id);
8911: OE_DEBUG_PUB.Add('p_credit_check_rule_id = '
8912: || p_credit_check_rule_rec.credit_check_rule_id
8913: );
8914:
8907: OE_DEBUG_PUB.Add('--------------------------------------------');
8908: OE_DEBUG_PUB.Add('p_customer_id = '|| p_customer_id, 1);
8909: OE_DEBUG_PUB.Add('p_site_use_id = '|| p_site_use_id, 1);
8910: OE_DEBUG_PUB.Add('p_header_id = '|| p_header_id);
8911: OE_DEBUG_PUB.Add('p_credit_check_rule_id = '
8912: || p_credit_check_rule_rec.credit_check_rule_id
8913: );
8914:
8915: OE_DEBUG_PUB.Add('Conversion type = '
8911: OE_DEBUG_PUB.Add('p_credit_check_rule_id = '
8912: || p_credit_check_rule_rec.credit_check_rule_id
8913: );
8914:
8915: OE_DEBUG_PUB.Add('Conversion type = '
8916: || p_credit_check_rule_rec.conversion_type
8917: );
8918:
8919: OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level, 1);
8915: OE_DEBUG_PUB.Add('Conversion type = '
8916: || p_credit_check_rule_rec.conversion_type
8917: );
8918:
8919: OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level, 1);
8920: OE_DEBUG_PUB.Add('p_limit_curr_code = '|| p_limit_curr_code);
8921: OE_DEBUG_PUB.Add('p_include_all_flag = '|| p_include_all_flag);
8922: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8923: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8916: || p_credit_check_rule_rec.conversion_type
8917: );
8918:
8919: OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level, 1);
8920: OE_DEBUG_PUB.Add('p_limit_curr_code = '|| p_limit_curr_code);
8921: OE_DEBUG_PUB.Add('p_include_all_flag = '|| p_include_all_flag);
8922: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8923: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8924: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8917: );
8918:
8919: OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level, 1);
8920: OE_DEBUG_PUB.Add('p_limit_curr_code = '|| p_limit_curr_code);
8921: OE_DEBUG_PUB.Add('p_include_all_flag = '|| p_include_all_flag);
8922: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8923: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8924: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8925: OE_DEBUG_PUB.Add(' ');
8918:
8919: OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level, 1);
8920: OE_DEBUG_PUB.Add('p_limit_curr_code = '|| p_limit_curr_code);
8921: OE_DEBUG_PUB.Add('p_include_all_flag = '|| p_include_all_flag);
8922: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8923: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8924: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8925: OE_DEBUG_PUB.Add(' ');
8926: OE_DEBUG_PUB.Add('Local Variables');
8919: OE_DEBUG_PUB.Add('p_credit_level = '|| p_credit_level, 1);
8920: OE_DEBUG_PUB.Add('p_limit_curr_code = '|| p_limit_curr_code);
8921: OE_DEBUG_PUB.Add('p_include_all_flag = '|| p_include_all_flag);
8922: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8923: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8924: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8925: OE_DEBUG_PUB.Add(' ');
8926: OE_DEBUG_PUB.Add('Local Variables');
8927: OE_DEBUG_PUB.Add('l_need_exposure_details = '||l_need_exposure_details);
8920: OE_DEBUG_PUB.Add('p_limit_curr_code = '|| p_limit_curr_code);
8921: OE_DEBUG_PUB.Add('p_include_all_flag = '|| p_include_all_flag);
8922: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8923: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8924: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8925: OE_DEBUG_PUB.Add(' ');
8926: OE_DEBUG_PUB.Add('Local Variables');
8927: OE_DEBUG_PUB.Add('l_need_exposure_details = '||l_need_exposure_details);
8928: OE_DEBUG_PUB.Add(' ');
8921: OE_DEBUG_PUB.Add('p_include_all_flag = '|| p_include_all_flag);
8922: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8923: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8924: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8925: OE_DEBUG_PUB.Add(' ');
8926: OE_DEBUG_PUB.Add('Local Variables');
8927: OE_DEBUG_PUB.Add('l_need_exposure_details = '||l_need_exposure_details);
8928: OE_DEBUG_PUB.Add(' ');
8929:
8922: OE_DEBUG_PUB.Add('p_global_exposure_flag = '|| p_global_exposure_flag, 1);
8923: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8924: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8925: OE_DEBUG_PUB.Add(' ');
8926: OE_DEBUG_PUB.Add('Local Variables');
8927: OE_DEBUG_PUB.Add('l_need_exposure_details = '||l_need_exposure_details);
8928: OE_DEBUG_PUB.Add(' ');
8929:
8930: --------------------------------------------------------------------
8923: OE_DEBUG_PUB.Add('p_need_exposure_details= '|| p_need_exposure_details);
8924: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8925: OE_DEBUG_PUB.Add(' ');
8926: OE_DEBUG_PUB.Add('Local Variables');
8927: OE_DEBUG_PUB.Add('l_need_exposure_details = '||l_need_exposure_details);
8928: OE_DEBUG_PUB.Add(' ');
8929:
8930: --------------------------------------------------------------------
8931: --------- Assign default values: credit check rules ----------------
8924: OE_DEBUG_PUB.Add('----------------End Parameters------------------------ ');
8925: OE_DEBUG_PUB.Add(' ');
8926: OE_DEBUG_PUB.Add('Local Variables');
8927: OE_DEBUG_PUB.Add('l_need_exposure_details = '||l_need_exposure_details);
8928: OE_DEBUG_PUB.Add(' ');
8929:
8930: --------------------------------------------------------------------
8931: --------- Assign default values: credit check rules ----------------
8932: --------------------------------------------------------------------
8930: --------------------------------------------------------------------
8931: --------- Assign default values: credit check rules ----------------
8932: --------------------------------------------------------------------
8933:
8934: OE_DEBUG_PUB.Add(' Assign Default Values: Credit rules ');
8935: END IF;
8936:
8937: l_open_ar_days := p_credit_check_rule_rec.open_ar_days ;
8938: l_uninvoiced_orders_flag :=
8958: NVL(p_credit_check_rule_rec.include_returns_flag,'N');
8959:
8960: IF G_debug_flag = 'Y'
8961: THEN
8962: OE_DEBUG_PUB.Add(' Credit rule setup values ');
8963: OE_DEBUG_PUB.Add(' ');
8964: OE_DEBUG_PUB.Add(' -----------------------------------------------');
8965: OE_DEBUG_PUB.Add(' l_open_ar_days = '|| l_open_ar_days );
8966: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8959:
8960: IF G_debug_flag = 'Y'
8961: THEN
8962: OE_DEBUG_PUB.Add(' Credit rule setup values ');
8963: OE_DEBUG_PUB.Add(' ');
8964: OE_DEBUG_PUB.Add(' -----------------------------------------------');
8965: OE_DEBUG_PUB.Add(' l_open_ar_days = '|| l_open_ar_days );
8966: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8967: l_uninvoiced_orders_flag );
8960: IF G_debug_flag = 'Y'
8961: THEN
8962: OE_DEBUG_PUB.Add(' Credit rule setup values ');
8963: OE_DEBUG_PUB.Add(' ');
8964: OE_DEBUG_PUB.Add(' -----------------------------------------------');
8965: OE_DEBUG_PUB.Add(' l_open_ar_days = '|| l_open_ar_days );
8966: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8967: l_uninvoiced_orders_flag );
8968: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8961: THEN
8962: OE_DEBUG_PUB.Add(' Credit rule setup values ');
8963: OE_DEBUG_PUB.Add(' ');
8964: OE_DEBUG_PUB.Add(' -----------------------------------------------');
8965: OE_DEBUG_PUB.Add(' l_open_ar_days = '|| l_open_ar_days );
8966: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8967: l_uninvoiced_orders_flag );
8968: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8969: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8962: OE_DEBUG_PUB.Add(' Credit rule setup values ');
8963: OE_DEBUG_PUB.Add(' ');
8964: OE_DEBUG_PUB.Add(' -----------------------------------------------');
8965: OE_DEBUG_PUB.Add(' l_open_ar_days = '|| l_open_ar_days );
8966: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8967: l_uninvoiced_orders_flag );
8968: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8969: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8970: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8964: OE_DEBUG_PUB.Add(' -----------------------------------------------');
8965: OE_DEBUG_PUB.Add(' l_open_ar_days = '|| l_open_ar_days );
8966: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8967: l_uninvoiced_orders_flag );
8968: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8969: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8970: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8971: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8972: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8965: OE_DEBUG_PUB.Add(' l_open_ar_days = '|| l_open_ar_days );
8966: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8967: l_uninvoiced_orders_flag );
8968: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8969: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8970: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8971: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8972: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8973: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8966: OE_DEBUG_PUB.Add(' l_uninvoiced_orders_flag = '||
8967: l_uninvoiced_orders_flag );
8968: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8969: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8970: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8971: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8972: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8973: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8974: OE_DEBUG_PUB.Add(' l_open_ar_balance_flag = '|| l_open_ar_balance_flag );
8967: l_uninvoiced_orders_flag );
8968: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8969: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8970: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8971: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8972: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8973: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8974: OE_DEBUG_PUB.Add(' l_open_ar_balance_flag = '|| l_open_ar_balance_flag );
8975: OE_DEBUG_PUB.Add(' l_incl_external_exposure_flag = ' ||
8968: OE_DEBUG_PUB.Add(' l_orders_on_hold_flag = '|| l_orders_on_hold_flag );
8969: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8970: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8971: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8972: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8973: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8974: OE_DEBUG_PUB.Add(' l_open_ar_balance_flag = '|| l_open_ar_balance_flag );
8975: OE_DEBUG_PUB.Add(' l_incl_external_exposure_flag = ' ||
8976: l_incl_external_exposure_flag );
8969: OE_DEBUG_PUB.Add(' l_include_tax_flag = '|| l_include_tax_flag );
8970: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8971: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8972: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8973: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8974: OE_DEBUG_PUB.Add(' l_open_ar_balance_flag = '|| l_open_ar_balance_flag );
8975: OE_DEBUG_PUB.Add(' l_incl_external_exposure_flag = ' ||
8976: l_incl_external_exposure_flag );
8977: OE_DEBUG_PUB.Add(' l_include_returns_flag = ' ||
8970: OE_DEBUG_PUB.Add(' l_shipping_horizon = '|| l_shipping_horizon );
8971: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8972: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8973: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8974: OE_DEBUG_PUB.Add(' l_open_ar_balance_flag = '|| l_open_ar_balance_flag );
8975: OE_DEBUG_PUB.Add(' l_incl_external_exposure_flag = ' ||
8976: l_incl_external_exposure_flag );
8977: OE_DEBUG_PUB.Add(' l_include_returns_flag = ' ||
8978: l_include_returns_flag );
8971: OE_DEBUG_PUB.Add(' l_include_risk_flag = '|| l_include_risk_flag );
8972: OE_DEBUG_PUB.Add(' l_quick_cr_check_flag = '|| l_quick_cr_check_flag );
8973: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8974: OE_DEBUG_PUB.Add(' l_open_ar_balance_flag = '|| l_open_ar_balance_flag );
8975: OE_DEBUG_PUB.Add(' l_incl_external_exposure_flag = ' ||
8976: l_incl_external_exposure_flag );
8977: OE_DEBUG_PUB.Add(' l_include_returns_flag = ' ||
8978: l_include_returns_flag );
8979: OE_DEBUG_PUB.Add(' -------------------------------------------------- ');
8973: OE_DEBUG_PUB.Add(' l_freight_charges_flag = '|| l_freight_charges_flag );
8974: OE_DEBUG_PUB.Add(' l_open_ar_balance_flag = '|| l_open_ar_balance_flag );
8975: OE_DEBUG_PUB.Add(' l_incl_external_exposure_flag = ' ||
8976: l_incl_external_exposure_flag );
8977: OE_DEBUG_PUB.Add(' l_include_returns_flag = ' ||
8978: l_include_returns_flag );
8979: OE_DEBUG_PUB.Add(' -------------------------------------------------- ');
8980: OE_DEBUG_PUB.Add(' ');
8981: END IF;
8975: OE_DEBUG_PUB.Add(' l_incl_external_exposure_flag = ' ||
8976: l_incl_external_exposure_flag );
8977: OE_DEBUG_PUB.Add(' l_include_returns_flag = ' ||
8978: l_include_returns_flag );
8979: OE_DEBUG_PUB.Add(' -------------------------------------------------- ');
8980: OE_DEBUG_PUB.Add(' ');
8981: END IF;
8982:
8983:
8976: l_incl_external_exposure_flag );
8977: OE_DEBUG_PUB.Add(' l_include_returns_flag = ' ||
8978: l_include_returns_flag );
8979: OE_DEBUG_PUB.Add(' -------------------------------------------------- ');
8980: OE_DEBUG_PUB.Add(' ');
8981: END IF;
8982:
8983:
8984: IF p_credit_level = 'CUSTOMER'
8986: -- Select total exposure using CUSTOMER CURSORs
8987:
8988: IF G_debug_flag = 'Y'
8989: THEN
8990: OE_DEBUG_PUB.Add('Get_order_Exposure CUSTOMER ',1);
8991: END IF;
8992:
8993: -- The exposure calculation must be done for all the
8994: -- usage currencies as part of the multi currency
8999: LOOP
9000: l_current_usage_cur := NULL ;
9001: IF G_debug_flag = 'Y'
9002: THEN
9003: OE_DEBUG_PUB.ADD(' ');
9004: OE_DEBUG_PUB.ADD('############################### ');
9005: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
9006: OE_DEBUG_PUB.ADD('############################### ');
9007: OE_DEBUG_PUB.ADD(' ');
9000: l_current_usage_cur := NULL ;
9001: IF G_debug_flag = 'Y'
9002: THEN
9003: OE_DEBUG_PUB.ADD(' ');
9004: OE_DEBUG_PUB.ADD('############################### ');
9005: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
9006: OE_DEBUG_PUB.ADD('############################### ');
9007: OE_DEBUG_PUB.ADD(' ');
9008: OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
9001: IF G_debug_flag = 'Y'
9002: THEN
9003: OE_DEBUG_PUB.ADD(' ');
9004: OE_DEBUG_PUB.ADD('############################### ');
9005: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
9006: OE_DEBUG_PUB.ADD('############################### ');
9007: OE_DEBUG_PUB.ADD(' ');
9008: OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
9009: OE_DEBUG_PUB.ADD(' ');
9002: THEN
9003: OE_DEBUG_PUB.ADD(' ');
9004: OE_DEBUG_PUB.ADD('############################### ');
9005: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
9006: OE_DEBUG_PUB.ADD('############################### ');
9007: OE_DEBUG_PUB.ADD(' ');
9008: OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
9009: OE_DEBUG_PUB.ADD(' ');
9010: OE_DEBUG_PUB.ADD('.');
9003: OE_DEBUG_PUB.ADD(' ');
9004: OE_DEBUG_PUB.ADD('############################### ');
9005: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
9006: OE_DEBUG_PUB.ADD('############################### ');
9007: OE_DEBUG_PUB.ADD(' ');
9008: OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
9009: OE_DEBUG_PUB.ADD(' ');
9010: OE_DEBUG_PUB.ADD('.');
9011: END IF;
9004: OE_DEBUG_PUB.ADD('############################### ');
9005: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
9006: OE_DEBUG_PUB.ADD('############################### ');
9007: OE_DEBUG_PUB.ADD(' ');
9008: OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
9009: OE_DEBUG_PUB.ADD(' ');
9010: OE_DEBUG_PUB.ADD('.');
9011: END IF;
9012:
9005: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
9006: OE_DEBUG_PUB.ADD('############################### ');
9007: OE_DEBUG_PUB.ADD(' ');
9008: OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
9009: OE_DEBUG_PUB.ADD(' ');
9010: OE_DEBUG_PUB.ADD('.');
9011: END IF;
9012:
9013: l_current_usage_cur := p_usage_curr(i).usage_curr_code ;
9006: OE_DEBUG_PUB.ADD('############################### ');
9007: OE_DEBUG_PUB.ADD(' ');
9008: OE_DEBUG_PUB.ADD('l_current_usage_cur = '|| l_current_usage_cur );
9009: OE_DEBUG_PUB.ADD(' ');
9010: OE_DEBUG_PUB.ADD('.');
9011: END IF;
9012:
9013: l_current_usage_cur := p_usage_curr(i).usage_curr_code ;
9014:
9019: IF p_global_exposure_flag = 'Y'
9020: THEN
9021: IF G_debug_flag = 'Y'
9022: THEN
9023: OE_DEBUG_PUB.Add(' Get cust_external_csr_global ');
9024: END IF;
9025:
9026: OPEN cust_external_csr_global (p_usage_curr(i).usage_curr_code);
9027: FETCH cust_external_csr_global INTO l_external_exposure;
9026: OPEN cust_external_csr_global (p_usage_curr(i).usage_curr_code);
9027: FETCH cust_external_csr_global INTO l_external_exposure;
9028: IF cust_external_csr_global%NOTFOUND
9029: THEN
9030: OE_DEBUG_PUB.Add(' No cust_external_csr_global balance found');
9031: l_external_exposure := 0;
9032: END IF;
9033: CLOSE cust_external_csr_global ;
9034:
9033: CLOSE cust_external_csr_global ;
9034:
9035: ELSE
9036:
9037: OE_DEBUG_PUB.Add(' Get customer external exposure balance ');
9038:
9039: OPEN cust_external_exposure_csr (p_usage_curr(i).usage_curr_code);
9040: FETCH cust_external_exposure_csr INTO l_external_exposure;
9041: IF cust_external_exposure_csr%NOTFOUND
9039: OPEN cust_external_exposure_csr (p_usage_curr(i).usage_curr_code);
9040: FETCH cust_external_exposure_csr INTO l_external_exposure;
9041: IF cust_external_exposure_csr%NOTFOUND
9042: THEN
9043: OE_DEBUG_PUB.Add(' No external exposure balance found');
9044: l_external_exposure := 0;
9045: END IF;
9046: CLOSE cust_external_exposure_csr;
9047: END IF ; -- p_global check
9058: IF p_global_exposure_flag = 'Y'
9059: THEN
9060: IF G_debug_flag = 'Y'
9061: THEN
9062: OE_DEBUG_PUB.Add(' Get cust_ar_balance_global ');
9063: END IF;
9064:
9065: OPEN cust_ar_balance_global (p_usage_curr(i).usage_curr_code);
9066: FETCH cust_ar_balance_global INTO l_total_from_ar ;
9068: IF cust_ar_balance_global%NOTFOUND
9069: THEN
9070: IF G_debug_flag = 'Y'
9071: THEN
9072: OE_DEBUG_PUB.Add(' No AR balance found');
9073: END IF;
9074:
9075: l_total_from_ar := 0 ;
9076: END IF;
9079:
9080: ELSE
9081: IF G_debug_flag = 'Y'
9082: THEN
9083: OE_DEBUG_PUB.Add(' Get cust_ar_balance ');
9084: END IF;
9085:
9086: OPEN cust_ar_balance (p_usage_curr(i).usage_curr_code);
9087: FETCH cust_ar_balance INTO l_total_from_ar ;
9089: IF cust_ar_balance%NOTFOUND
9090: THEN
9091: IF G_debug_flag = 'Y'
9092: THEN
9093: OE_DEBUG_PUB.Add(' No AR balance found');
9094: END IF;
9095:
9096: l_total_from_ar := 0 ;
9097: END IF;
9105: IF p_global_exposure_flag = 'Y'
9106: THEN
9107: IF G_debug_flag = 'Y'
9108: THEN
9109: OE_DEBUG_PUB.Add(' Get cust_pay_risk_global ');
9110: END IF;
9111:
9112: OPEN cust_pay_risk_global (p_usage_curr(i).usage_curr_code);
9113: FETCH cust_pay_risk_global INTO l_payments_at_risk;
9115: THEN
9116: l_payments_at_risk := 0 ;
9117: IF G_debug_flag = 'Y'
9118: THEN
9119: OE_DEBUG_PUB.Add(' No Pay at risk found ');
9120: END IF;
9121: END IF;
9122:
9123: CLOSE cust_pay_risk_global;
9123: CLOSE cust_pay_risk_global;
9124: ELSE
9125: IF G_debug_flag = 'Y'
9126: THEN
9127: OE_DEBUG_PUB.Add(' Get cust_pay_risk ');
9128: END IF;
9129:
9130: OPEN cust_pay_risk (p_usage_curr(i).usage_curr_code);
9131: FETCH cust_pay_risk INTO l_payments_at_risk;
9131: FETCH cust_pay_risk INTO l_payments_at_risk;
9132: IF cust_pay_risk%NOTFOUND
9133: THEN
9134: l_payments_at_risk := 0 ;
9135: OE_DEBUG_PUB.Add(' No Pay at risk found ');
9136: END IF;
9137:
9138: CLOSE cust_pay_risk;
9139: END IF; -- Global
9146: (p_cust_account_id => p_customer_id ) = 'Y'
9147: THEN
9148: IF G_debug_flag = 'Y'
9149: THEN
9150: OE_DEBUG_PUB.Add(' Call DRAWEE BR exposure ');
9151: END IF;
9152:
9153: IF p_global_exposure_flag = 'Y'
9154: THEN
9153: IF p_global_exposure_flag = 'Y'
9154: THEN
9155: IF G_debug_flag = 'Y'
9156: THEN
9157: OE_DEBUG_PUB.Add(' Get cust_br_balance_global ');
9158: END IF;
9159:
9160: OPEN cust_br_balance_global (p_usage_curr(i).usage_curr_code);
9161: FETCH cust_br_balance_global INTO l_total_from_br ;
9163: IF cust_br_balance_global%NOTFOUND
9164: THEN
9165: IF G_debug_flag = 'Y'
9166: THEN
9167: OE_DEBUG_PUB.Add(' No AR balance found');
9168: END IF;
9169:
9170: l_total_from_br := 0 ;
9171: END IF;
9174:
9175: ELSE
9176: IF G_debug_flag = 'Y'
9177: THEN
9178: OE_DEBUG_PUB.Add(' Get cust_br_balance ');
9179: END IF;
9180:
9181: OPEN cust_br_balance (p_usage_curr(i).usage_curr_code);
9182: FETCH cust_br_balance INTO l_total_from_br ;
9182: FETCH cust_br_balance INTO l_total_from_br ;
9183:
9184: IF cust_br_balance%NOTFOUND
9185: THEN
9186: OE_DEBUG_PUB.Add(' No AR balance found');
9187: l_total_from_br := 0 ;
9188: END IF;
9189:
9190: CLOSE cust_br_balance;
9196: IF p_global_exposure_flag = 'Y'
9197: THEN
9198: IF G_debug_flag = 'Y'
9199: THEN
9200: OE_DEBUG_PUB.Add(' Get cust_pay_risk_global ');
9201: END IF;
9202:
9203: OPEN cust_br_pay_risk_global (p_usage_curr(i).usage_curr_code);
9204: FETCH cust_br_pay_risk_global INTO l_payments_at_risk_br;
9204: FETCH cust_br_pay_risk_global INTO l_payments_at_risk_br;
9205: IF cust_br_pay_risk_global%NOTFOUND
9206: THEN
9207: l_payments_at_risk_br := 0 ;
9208: OE_DEBUG_PUB.Add(' No Pay at risk found ');
9209: END IF;
9210:
9211: CLOSE cust_br_pay_risk_global;
9212: ELSE
9211: CLOSE cust_br_pay_risk_global;
9212: ELSE
9213: IF G_debug_flag = 'Y'
9214: THEN
9215: OE_DEBUG_PUB.Add(' Get cust_pay_risk ');
9216: END IF;
9217:
9218: OPEN cust_br_pay_risk (p_usage_curr(i).usage_curr_code);
9219: FETCH cust_br_pay_risk INTO l_payments_at_risk_br;
9219: FETCH cust_br_pay_risk INTO l_payments_at_risk_br;
9220: IF cust_br_pay_risk%NOTFOUND
9221: THEN
9222: l_payments_at_risk_br := 0 ;
9223: OE_DEBUG_PUB.Add(' No Pay at risk found ');
9224: END IF;
9225: CLOSE cust_br_pay_risk;
9226: END IF; -- Global
9227: ELSE
9226: END IF; -- Global
9227: ELSE
9228: IF G_debug_flag = 'Y'
9229: THEN
9230: OE_DEBUG_PUB.Add(' No Drawee check required ');
9231: END IF;
9232:
9233: END IF; -- DRAWEE
9234: ELSE
9233: END IF; -- DRAWEE
9234: ELSE
9235: IF G_debug_flag = 'Y'
9236: THEN
9237: OE_DEBUG_PUB.Add(' No Drawee check - NO horizon ' ||
9238: p_credit_check_rule_rec.OPEN_AR_DAYS) ;
9239: END IF;
9240: END IF;
9241:
9247: THEN
9248: BEGIN
9249: IF G_debug_flag = 'Y'
9250: THEN
9251: OE_DEBUG_PUB.Add(' Begin OM Exposure calculation ');
9252: END IF;
9253:
9254: IF OE_Commitment_PVT.Do_Commitment_Sequencing
9255: THEN
9256: IF p_global_exposure_flag = 'Y'
9257: THEN
9258: IF G_debug_flag = 'Y'
9259: THEN
9260: OE_DEBUG_PUB.Add(' cust_commitments_global');
9261: END IF;
9262:
9263: OPEN cust_commitment_total_global
9264: (p_usage_curr(i).usage_curr_code);
9267:
9268: IF cust_commitment_total_global%NOTFOUND
9269: THEN
9270: l_total_commitment := 0 ;
9271: OE_DEBUG_PUB.Add('No commitment amount found ');
9272: END IF;
9273:
9274: CLOSE cust_commitment_total_global ;
9275: ELSE
9274: CLOSE cust_commitment_total_global ;
9275: ELSE
9276: IF G_debug_flag = 'Y'
9277: THEN
9278: OE_DEBUG_PUB.Add(' cust_commitments ');
9279: END IF;
9280:
9281: OPEN customer_commitment_total (p_usage_curr(i).usage_curr_code);
9282: FETCH customer_commitment_total INTO l_total_commitment ;
9283:
9284: IF customer_commitment_total%NOTFOUND
9285: THEN
9286: l_total_commitment := 0 ;
9287: OE_DEBUG_PUB.Add('No commitment amount found ');
9288: END IF;
9289:
9290: CLOSE customer_commitment_total ;
9291: END IF ; -- Global
9308: -----returns are not included
9309:
9310: IF G_debug_flag = 'Y'
9311: THEN
9312: OE_DEBUG_PUB.Add(' select cust_uninvoiced_orders_global ');
9313: END IF;
9314:
9315: OPEN cust_uninvoiced_orders_global(p_usage_curr(i).usage_curr_code);
9316: FETCH cust_uninvoiced_orders_global INTO l_total_on_order;
9317:
9318: IF cust_uninvoiced_orders_global%NOTFOUND
9319: THEN
9320: l_total_on_order := 0 ;
9321: OE_DEBUG_PUB.Add('No Uninvoiced Order amount found ');
9322: END IF;
9323: CLOSE cust_uninvoiced_orders_global;
9324:
9325: ELSE
9325: ELSE
9326: -----returns are included
9327: IF G_debug_flag = 'Y'
9328: THEN
9329: OE_DEBUG_PUB.Add(' select cust_uninv_orders_global_ret ');
9330: END IF;
9331:
9332: OPEN cust_uninv_orders_global_ret(p_usage_curr(i).usage_curr_code);
9333: FETCH cust_uninv_orders_global_ret INTO l_total_on_order;
9334:
9335: IF cust_uninv_orders_global_ret%NOTFOUND
9336: THEN
9337: l_total_on_order := 0 ;
9338: OE_DEBUG_PUB.Add('No Uninvoiced Order amount found ');
9339: END IF;
9340:
9341: CLOSE cust_uninv_orders_global_ret;
9342:
9349: THEN
9350: -----returns are not included
9351: IF G_debug_flag = 'Y'
9352: THEN
9353: OE_DEBUG_PUB.Add(' select cust_uninvoiced_orders ');
9354: END IF;
9355:
9356: OPEN cust_uninvoiced_orders(p_usage_curr(i).usage_curr_code);
9357: FETCH cust_uninvoiced_orders INTO l_total_on_order;
9358:
9359: IF cust_uninvoiced_orders%NOTFOUND
9360: THEN
9361: l_total_on_order := 0 ;
9362: OE_DEBUG_PUB.Add('No Uninvoiced Order amount found ');
9363: END IF;
9364:
9365: CLOSE cust_uninvoiced_orders;
9366:
9367: ELSE
9368: -----returns are included
9369: IF G_debug_flag = 'Y'
9370: THEN
9371: OE_DEBUG_PUB.Add(' select cust_uninv_orders_ret ');
9372: END IF;
9373:
9374: OPEN cust_uninv_orders_ret(p_usage_curr(i).usage_curr_code);
9375: FETCH cust_uninv_orders_ret INTO l_total_on_order;
9376:
9377: IF cust_uninv_orders_ret%NOTFOUND
9378: THEN
9379: l_total_on_order := 0 ;
9380: OE_DEBUG_PUB.Add('No Uninvoiced Order amount found ');
9381: END IF;
9382:
9383: CLOSE cust_uninv_orders_ret;
9384:
9395: THEN
9396: -----returns are not included
9397: IF G_debug_flag = 'Y'
9398: THEN
9399: OE_DEBUG_PUB.Add(' cust_uninv_hdr_freight1_global ');
9400: END IF;
9401:
9402: OPEN cust_uninv_hdr_freight1_global
9403: (p_usage_curr(i).usage_curr_code);
9407:
9408: IF cust_uninv_hdr_freight1_global%NOTFOUND
9409: THEN
9410: l_uninvoiced_hdr_freight1 := 0 ;
9411: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight1 found ');
9412: END IF;
9413:
9414: CLOSE cust_uninv_hdr_freight1_global;
9415:
9416: ELSE
9417: -----returns are included
9418: IF G_debug_flag = 'Y'
9419: THEN
9420: OE_DEBUG_PUB.Add(' open cust_uninv_hdr_fr1_glb_ret ');
9421: END IF;
9422:
9423: OPEN cust_uninv_hdr_fr1_glb_ret
9424: (p_usage_curr(i).usage_curr_code);
9428:
9429: IF cust_uninv_hdr_fr1_glb_ret%NOTFOUND
9430: THEN
9431: l_uninvoiced_hdr_freight1 := 0 ;
9432: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight1 found ');
9433: END IF;
9434:
9435: CLOSE cust_uninv_hdr_fr1_glb_ret;
9436:
9442: -----returns are not included
9443:
9444: IF G_debug_flag = 'Y'
9445: THEN
9446: OE_DEBUG_PUB.Add(' cust_uninv_hdr_freight2_global ');
9447: END IF;
9448:
9449:
9450: OPEN cust_uninv_hdr_freight2_global
9457: THEN
9458: l_uninvoiced_hdr_freight2 := 0 ;
9459: IF G_debug_flag = 'Y'
9460: THEN
9461: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight2 found ');
9462: END IF;
9463: END IF;
9464:
9465: CLOSE cust_uninv_hdr_freight2_global ;
9468: -----returns are included
9469:
9470: IF G_debug_flag = 'Y'
9471: THEN
9472: OE_DEBUG_PUB.Add(' open cust_uninv_hdr_fr2_glb_ret ');
9473: END IF;
9474:
9475: OPEN cust_uninv_hdr_fr2_glb_ret
9476: (p_usage_curr(i).usage_curr_code);
9482: THEN
9483: l_uninvoiced_hdr_freight2 := 0 ;
9484: IF G_debug_flag = 'Y'
9485: THEN
9486: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight2 found ');
9487: END IF;
9488: END IF;
9489:
9490: CLOSE cust_uninv_hdr_fr2_glb_ret;
9499: -----returns are not included
9500:
9501: IF G_debug_flag = 'Y'
9502: THEN
9503: OE_DEBUG_PUB.Add(' cust_uninv_hdr_freight1 ');
9504: END IF;
9505:
9506: OPEN cust_uninv_hdr_freight1(p_usage_curr(i).usage_curr_code);
9507: FETCH cust_uninv_hdr_freight1 INTO l_uninvoiced_hdr_freight1;
9510: THEN
9511: l_uninvoiced_hdr_freight1 := 0 ;
9512: IF G_debug_flag = 'Y'
9513: THEN
9514: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight1 found ');
9515: END IF;
9516: END IF;
9517:
9518: CLOSE cust_uninv_hdr_freight1;
9518: CLOSE cust_uninv_hdr_freight1;
9519:
9520: IF G_debug_flag = 'Y'
9521: THEN
9522: OE_DEBUG_PUB.Add(' cust_uninv_hdr_freight2 ');
9523: END IF;
9524:
9525: OPEN cust_uninv_hdr_freight2(p_usage_curr(i).usage_curr_code);
9526: FETCH cust_uninv_hdr_freight2 INTO l_uninvoiced_hdr_freight2;
9529: THEN
9530: l_uninvoiced_hdr_freight2 := 0 ;
9531: IF G_debug_flag = 'Y'
9532: THEN
9533: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight2 found ');
9534: END IF;
9535: END IF;
9536: CLOSE cust_uninv_hdr_freight2;
9537:
9539: -----returns are included
9540:
9541: IF G_debug_flag = 'Y'
9542: THEN
9543: OE_DEBUG_PUB.Add(' open cust_uninv_hdr_freight1_ret ');
9544: END IF;
9545:
9546: OPEN cust_uninv_hdr_freight1_ret(p_usage_curr(i).usage_curr_code);
9547: FETCH cust_uninv_hdr_freight1_ret INTO l_uninvoiced_hdr_freight1;
9550: THEN
9551: l_uninvoiced_hdr_freight1 := 0 ;
9552: IF G_debug_flag = 'Y'
9553: THEN
9554: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight1 found ');
9555: END IF;
9556: END IF;
9557:
9558: CLOSE cust_uninv_hdr_freight1_ret;
9558: CLOSE cust_uninv_hdr_freight1_ret;
9559:
9560: IF G_debug_flag = 'Y'
9561: THEN
9562: OE_DEBUG_PUB.Add(' open cust_uninv_hdr_freight2_ret ');
9563: END IF;
9564:
9565: OPEN cust_uninv_hdr_freight2_ret(p_usage_curr(i).usage_curr_code);
9566: FETCH cust_uninv_hdr_freight2_ret INTO l_uninvoiced_hdr_freight2;
9569: THEN
9570: l_uninvoiced_hdr_freight2 := 0 ;
9571: IF G_debug_flag = 'Y'
9572: THEN
9573: OE_DEBUG_PUB.Add('No Uninvoiced Order Freight2 found ');
9574: END IF;
9575: END IF;
9576: CLOSE cust_uninv_hdr_freight2_ret;
9577:
9594: -----returns are not included
9595:
9596: IF G_debug_flag = 'Y'
9597: THEN
9598: OE_DEBUG_PUB.Add(' cust_orders_not_on_hold_global ');
9599: END IF;
9600:
9601: OPEN cust_orders_not_on_hold_global
9602: (p_usage_curr(i).usage_curr_code);
9605:
9606: IF cust_orders_not_on_hold_global%NOTFOUND
9607: THEN
9608: l_total_no_hold := 0 ;
9609: OE_DEBUG_PUB.Add('No Orders not on hold amount found ');
9610: END IF;
9611:
9612: CLOSE cust_orders_not_on_hold_global ;
9613:
9614: -- If the l_need_exposure_details flag enabled, get the
9615: -- OM amount on hold
9616: IF l_need_exposure_details = 'Y' THEN
9617: IF G_debug_flag = 'Y' THEN
9618: OE_DEBUG_PUB.Add(' cust_orders_on_hold_global ');
9619: END IF;
9620: OPEN cust_orders_on_hold_global(p_usage_curr(i).usage_curr_code);
9621: FETCH cust_orders_on_hold_global
9622: INTO l_total_on_hold;
9622: INTO l_total_on_hold;
9623:
9624: IF cust_orders_on_hold_global%NOTFOUND THEN
9625: l_total_on_hold := 0 ;
9626: OE_DEBUG_PUB.Add('No global orders on hold amount found ');
9627: END IF;
9628: CLOSE cust_orders_on_hold_global ;
9629: END IF;
9630:
9632: -----returns are included
9633:
9634: IF G_debug_flag = 'Y'
9635: THEN
9636: OE_DEBUG_PUB.Add(' open cust_ord_not_on_hold_glb_ret ');
9637: END IF;
9638:
9639: OPEN cust_ord_not_on_hold_glb_ret
9640: (p_usage_curr(i).usage_curr_code);
9643:
9644: IF cust_ord_not_on_hold_glb_ret%NOTFOUND
9645: THEN
9646: l_total_no_hold := 0 ;
9647: OE_DEBUG_PUB.Add('No Orders not on hold amount found ');
9648: END IF;
9649:
9650: CLOSE cust_ord_not_on_hold_glb_ret ;
9651:
9652: -- If the l_need_exposure_details flag enabled, get the
9653: -- OM amount on hold
9654: IF l_need_exposure_details = 'Y' THEN
9655: IF G_debug_flag = 'Y' THEN
9656: OE_DEBUG_PUB.Add(' cust_ord_on_hold_glb_ret ');
9657: END IF;
9658: OPEN cust_ord_on_hold_glb_ret(p_usage_curr(i).usage_curr_code);
9659: FETCH cust_ord_on_hold_glb_ret
9660: INTO l_total_on_hold;
9660: INTO l_total_on_hold;
9661:
9662: IF cust_ord_on_hold_glb_ret%NOTFOUND THEN
9663: l_total_on_hold := 0 ;
9664: OE_DEBUG_PUB.Add('No return global orders on hold amount found ');
9665: END IF;
9666: CLOSE cust_ord_on_hold_glb_ret ;
9667: END IF;
9668:
9682: -----returns are not included
9683:
9684: IF G_debug_flag = 'Y'
9685: THEN
9686: OE_DEBUG_PUB.Add(' cust_no_hold_hdr_freight1_glb ');
9687: END IF;
9688:
9689: OPEN cust_no_hold_hdr_freight1_glb
9690: (p_usage_curr(i).usage_curr_code);
9696: THEN
9697: l_no_hold_hdr_freight1 := 0 ;
9698: IF G_debug_flag = 'Y'
9699: THEN
9700: OE_DEBUG_PUB.Add('No orders without hold freight1 amount found ');
9701: END IF;
9702: END IF;
9703:
9704: CLOSE cust_no_hold_hdr_freight1_glb ;
9704: CLOSE cust_no_hold_hdr_freight1_glb ;
9705:
9706: IF G_debug_flag = 'Y'
9707: THEN
9708: OE_DEBUG_PUB.Add(' cust_no_hold_hdr_freight2_glb ');
9709: END IF;
9710:
9711: OPEN cust_no_hold_hdr_freight2_glb
9712: (p_usage_curr(i).usage_curr_code);
9718: THEN
9719: l_hold_hdr_freight2 := 0 ;
9720: IF G_debug_flag = 'Y'
9721: THEN
9722: OE_DEBUG_PUB.Add('No orders without hold freight2 amount found');
9723: END IF;
9724: END IF;
9725: CLOSE cust_no_hold_hdr_freight2_glb ;
9726:
9727: -- If the l_need_exposure_details flag enabled, get the
9728: -- OM amount on hold
9729: IF l_need_exposure_details = 'Y' THEN
9730: IF G_debug_flag = 'Y' THEN
9731: OE_DEBUG_PUB.Add(' cust_hold_hdr_freight1_glb ');
9732: END IF;
9733:
9734: OPEN cust_hold_hdr_freight1_glb
9735: (p_usage_curr(i).usage_curr_code);
9737: INTO l_hold_hdr_freight1;
9738:
9739: IF cust_hold_hdr_freight1_glb%NOTFOUND THEN
9740: l_hold_hdr_freight1 := 0 ;
9741: OE_DEBUG_PUB.Add
9742: ('No orders on hold with freight1 amount found ');
9743: END IF;
9744:
9745: CLOSE cust_hold_hdr_freight1_glb;
9745: CLOSE cust_hold_hdr_freight1_glb;
9746:
9747: -- get header freight2 on hold
9748: IF G_debug_flag = 'Y' THEN
9749: OE_DEBUG_PUB.Add(' cust_hold_hdr_freight2_glb ');
9750: END IF;
9751:
9752: OPEN cust_hold_hdr_freight2_glb
9753: (p_usage_curr(i).usage_curr_code);
9755: INTO l_hold_hdr_freight2;
9756:
9757: IF cust_hold_hdr_freight2_glb%NOTFOUND THEN
9758: l_hold_hdr_freight2 := 0 ;
9759: OE_DEBUG_PUB.Add
9760: ('No orders on hold with freight2 amount found ');
9761: END IF;
9762:
9763: CLOSE cust_hold_hdr_freight2_glb;
9767: -----returns are included
9768:
9769: IF G_debug_flag = 'Y'
9770: THEN
9771: OE_DEBUG_PUB.Add('open cust_no_hold_hdr_fr1_glb_ret ');
9772: END IF;
9773:
9774: OPEN cust_no_hold_hdr_fr1_glb_ret
9775: (p_usage_curr(i).usage_curr_code);
9781: THEN
9782: l_no_hold_hdr_freight1 := 0 ;
9783: IF G_debug_flag = 'Y'
9784: THEN
9785: OE_DEBUG_PUB.Add('No orders without hold freight1 amount found ');
9786: END IF;
9787: END IF;
9788:
9789: CLOSE cust_no_hold_hdr_fr1_glb_ret ;
9789: CLOSE cust_no_hold_hdr_fr1_glb_ret ;
9790:
9791: IF G_debug_flag = 'Y'
9792: THEN
9793: OE_DEBUG_PUB.Add('open cust_no_hold_hdr_fr2_glb_ret ');
9794: END IF;
9795:
9796: OPEN cust_no_hold_hdr_fr2_glb_ret
9797: (p_usage_curr(i).usage_curr_code);
9803: THEN
9804: l_hold_hdr_freight2 := 0 ;
9805: IF G_debug_flag = 'Y'
9806: THEN
9807: OE_DEBUG_PUB.Add('No orders without hold freight2 amount found');
9808: END IF;
9809: END IF;
9810: CLOSE cust_no_hold_hdr_fr2_glb_ret ;
9811:
9812: -- If the l_need_exposure_details flag enabled, get the
9813: -- OM amount on hold
9814: IF l_need_exposure_details = 'Y' THEN
9815: IF G_debug_flag = 'Y' THEN
9816: OE_DEBUG_PUB.Add(' cust_hold_hdr_fr1_glb_ret ');
9817: END IF;
9818:
9819: OPEN cust_hold_hdr_fr1_glb_ret
9820: (p_usage_curr(i).usage_curr_code);
9822: INTO l_hold_hdr_freight1;
9823:
9824: IF cust_hold_hdr_fr1_glb_ret%NOTFOUND THEN
9825: l_hold_hdr_freight1 := 0 ;
9826: OE_DEBUG_PUB.Add
9827: ('No orders on hold with freight1 amount found ');
9828: END IF;
9829:
9830: CLOSE cust_hold_hdr_fr1_glb_ret;
9830: CLOSE cust_hold_hdr_fr1_glb_ret;
9831:
9832: -- get header freight2 on hold
9833: IF G_debug_flag = 'Y' THEN
9834: OE_DEBUG_PUB.Add(' cust_hold_hdr_fr2_glb_ret ');
9835: END IF;
9836:
9837: OPEN cust_hold_hdr_fr2_glb_ret
9838: (p_usage_curr(i).usage_curr_code);
9840: INTO l_hold_hdr_freight2;
9841:
9842: IF cust_hold_hdr_fr2_glb_ret%NOTFOUND THEN
9843: l_hold_hdr_freight2 := 0 ;
9844: OE_DEBUG_PUB.Add
9845: ('No orders on hold with freight2 amount found ');
9846: END IF;
9847:
9848: CLOSE cust_hold_hdr_fr2_glb_ret;
9860: -----returns are not included
9861:
9862: IF G_debug_flag = 'Y'
9863: THEN
9864: OE_DEBUG_PUB.Add(' cust_orders_not_on_hold ');
9865: END IF;
9866:
9867: OPEN cust_orders_not_on_hold (p_usage_curr(i).usage_curr_code);
9868: FETCH cust_orders_not_on_hold INTO l_total_no_hold;
9871: THEN
9872: l_total_no_hold := 0 ;
9873: IF G_debug_flag = 'Y'
9874: THEN
9875: OE_DEBUG_PUB.Add('No orders not on hold amount found ');
9876: END IF;
9877: END IF;
9878:
9879: CLOSE cust_orders_not_on_hold;
9881: -- If the l_need_exposure_details flag enabled, get the
9882: -- OM amount on hold
9883: IF l_need_exposure_details = 'Y' THEN
9884: IF G_debug_flag = 'Y' THEN
9885: OE_DEBUG_PUB.Add(' cust_orders_on_hold ');
9886: END IF;
9887:
9888: OPEN cust_orders_on_hold (p_usage_curr(i).usage_curr_code);
9889: FETCH cust_orders_on_hold INTO l_total_on_hold;
9890:
9891: IF cust_orders_on_hold%NOTFOUND THEN
9892: l_total_on_hold := 0 ;
9893: IF G_debug_flag = 'Y' THEN
9894: OE_DEBUG_PUB.Add('No customer orders on hold amount found ');
9895: END IF;
9896: END IF;
9897: CLOSE cust_orders_on_hold;
9898: END IF;
9901: -----returns are not included
9902:
9903: IF G_debug_flag = 'Y'
9904: THEN
9905: OE_DEBUG_PUB.Add(' cust_ord_not_on_hold_ret ');
9906: END IF;
9907:
9908: OPEN cust_ord_not_on_hold_ret (p_usage_curr(i).usage_curr_code);
9909: FETCH cust_ord_not_on_hold_ret INTO l_total_no_hold;
9912: THEN
9913: l_total_no_hold := 0 ;
9914: IF G_debug_flag = 'Y'
9915: THEN
9916: OE_DEBUG_PUB.Add('No orders not on hold amount found ');
9917: END IF;
9918: END IF;
9919:
9920: CLOSE cust_ord_not_on_hold_ret;
9922: -- If the l_need_exposure_details flag enabled, get the
9923: -- OM amount on hold
9924: IF l_need_exposure_details = 'Y' THEN
9925: IF G_debug_flag = 'Y' THEN
9926: OE_DEBUG_PUB.Add(' cust_ord_on_hold_ret ');
9927: END IF;
9928:
9929: OPEN cust_ord_on_hold_ret (p_usage_curr(i).usage_curr_code);
9930: FETCH cust_ord_on_hold_ret INTO l_total_on_hold;
9931:
9932: IF cust_ord_on_hold_ret%NOTFOUND THEN
9933: l_total_on_hold := 0 ;
9934: IF G_debug_flag = 'Y' THEN
9935: OE_DEBUG_PUB.Add('No customer orders on hold amount found ');
9936: END IF;
9937: END IF;
9938: CLOSE cust_ord_on_hold_ret;
9939: END IF;
9953: -----returns are not included
9954:
9955: IF G_debug_flag = 'Y'
9956: THEN
9957: OE_DEBUG_PUB.Add(' cust_no_hold_hdr_freight1 ');
9958: END IF;
9959:
9960: OPEN cust_no_hold_hdr_freight1(p_usage_curr(i).usage_curr_code);
9961: FETCH cust_no_hold_hdr_freight1 INTO l_no_hold_hdr_freight1;
9964: THEN
9965: l_no_hold_hdr_freight1 := 0 ;
9966: IF G_debug_flag = 'Y'
9967: THEN
9968: OE_DEBUG_PUB.Add('No orders without hold freight1 amount found ');
9969: END IF;
9970: END IF;
9971:
9972: CLOSE cust_no_hold_hdr_freight1;
9972: CLOSE cust_no_hold_hdr_freight1;
9973:
9974: IF G_debug_flag = 'Y'
9975: THEN
9976: OE_DEBUG_PUB.Add(' cust_no_hold_hdr_freight2 ');
9977: END IF;
9978:
9979: OPEN cust_no_hold_hdr_freight2(p_usage_curr(i).usage_curr_code);
9980: FETCH cust_no_hold_hdr_freight2 INTO l_no_hold_hdr_freight2;
9983: THEN
9984: l_no_hold_hdr_freight2 := 0 ;
9985: IF G_debug_flag = 'Y'
9986: THEN
9987: OE_DEBUG_PUB.Add('No orders without hold freight2 amount found');
9988: END IF;
9989: END IF;
9990: CLOSE cust_no_hold_hdr_freight2;
9991:
9995: --
9996: -- Get cust on hold header freight1
9997: --
9998: IF G_debug_flag = 'Y' THEN
9999: OE_DEBUG_PUB.Add(' cust_hold_hdr_freight1 ');
10000: END IF;
10001:
10002: OPEN cust_hold_hdr_freight1(p_usage_curr(i).usage_curr_code);
10003: FETCH cust_hold_hdr_freight1 INTO l_hold_hdr_freight1;
10004:
10005: IF cust_hold_hdr_freight1%NOTFOUND THEN
10006: l_hold_hdr_freight1 := 0 ;
10007: IF G_debug_flag = 'Y' THEN
10008: OE_DEBUG_PUB.Add('No orders with hold freight1 amount found ');
10009: END IF;
10010: END IF;
10011:
10012: CLOSE cust_hold_hdr_freight1;
10013: --
10014: -- Get cust on hold header freight2
10015: --
10016: IF G_debug_flag = 'Y' THEN
10017: OE_DEBUG_PUB.Add(' cust_hold_hdr_freight2 ');
10018: END IF;
10019:
10020: OPEN cust_hold_hdr_freight2(p_usage_curr(i).usage_curr_code);
10021: FETCH cust_hold_hdr_freight2 INTO l_hold_hdr_freight2;
10022:
10023: IF cust_hold_hdr_freight2%NOTFOUND THEN
10024: l_hold_hdr_freight2 := 0 ;
10025: IF G_debug_flag = 'Y' THEN
10026: OE_DEBUG_PUB.Add('No orders with hold freight2 amount found ');
10027: END IF;
10028: END IF;
10029:
10030: CLOSE cust_hold_hdr_freight2;
10034: -----returns are included
10035:
10036: IF G_debug_flag = 'Y'
10037: THEN
10038: OE_DEBUG_PUB.Add(' cust_no_hold_hdr_freight1_ret ');
10039: END IF;
10040:
10041: OPEN cust_no_hold_hdr_freight1_ret(p_usage_curr(i).usage_curr_code);
10042: FETCH cust_no_hold_hdr_freight1_ret INTO l_no_hold_hdr_freight1;
10045: THEN
10046: l_no_hold_hdr_freight1 := 0 ;
10047: IF G_debug_flag = 'Y'
10048: THEN
10049: OE_DEBUG_PUB.Add('No orders without hold freight1 amount found ');
10050: END IF;
10051: END IF;
10052:
10053: CLOSE cust_no_hold_hdr_freight1_ret;
10053: CLOSE cust_no_hold_hdr_freight1_ret;
10054:
10055: IF G_debug_flag = 'Y'
10056: THEN
10057: OE_DEBUG_PUB.Add('open cust_no_hold_hdr_freight2_ret ');
10058: END IF;
10059:
10060: OPEN cust_no_hold_hdr_freight2_ret(p_usage_curr(i).usage_curr_code);
10061: FETCH cust_no_hold_hdr_freight2_ret INTO l_no_hold_hdr_freight2;
10064: THEN
10065: l_no_hold_hdr_freight2 := 0 ;
10066: IF G_debug_flag = 'Y'
10067: THEN
10068: OE_DEBUG_PUB.Add('No orders without hold freight2 amount found');
10069: END IF;
10070: END IF;
10071:
10072: CLOSE cust_no_hold_hdr_freight2_ret;
10078: --
10079: -- Get cust on hold header freight1 with returns
10080: --
10081: IF G_debug_flag = 'Y' THEN
10082: OE_DEBUG_PUB.Add(' cust_hold_hdr_freight1_ret ');
10083: END IF;
10084:
10085: OPEN cust_hold_hdr_freight1_ret(p_usage_curr(i).usage_curr_code);
10086: FETCH cust_hold_hdr_freight1_ret INTO l_hold_hdr_freight1;
10087:
10088: IF cust_hold_hdr_freight1_ret%NOTFOUND THEN
10089: l_hold_hdr_freight1 := 0 ;
10090: IF G_debug_flag = 'Y' THEN
10091: OE_DEBUG_PUB.Add('No orders with hold freight1 amount found ');
10092: END IF;
10093: END IF;
10094:
10095: CLOSE cust_hold_hdr_freight1_ret;
10096: --
10097: -- Get cust on hold header freight2
10098: --
10099: IF G_debug_flag = 'Y' THEN
10100: OE_DEBUG_PUB.Add(' cust_hold_hdr_freight2_ret ');
10101: END IF;
10102:
10103: OPEN cust_hold_hdr_freight2_ret(p_usage_curr(i).usage_curr_code);
10104: FETCH cust_hold_hdr_freight2_ret INTO l_hold_hdr_freight2;
10105:
10106: IF cust_hold_hdr_freight2_ret%NOTFOUND THEN
10107: l_hold_hdr_freight2 := 0 ;
10108: IF G_debug_flag = 'Y' THEN
10109: OE_DEBUG_PUB.Add('No orders with hold freight2 amount found ');
10110: END IF;
10111: END IF;
10112:
10113: CLOSE cust_hold_hdr_freight2_ret;
10121: END ;
10122: ELSE
10123: IF G_debug_flag = 'Y'
10124: THEN
10125: OE_DEBUG_PUB.Add(' No OM Exposure calculation ',1);
10126: END IF;
10127: END IF; -- Uninvoiced Orders
10128:
10129:
10190:
10191: IF G_debug_flag = 'Y'
10192: THEN
10193:
10194: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10195: || l_usage_total_exposure );
10196: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
10197: || l_cum_total_from_ar );
10198: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
10192: THEN
10193:
10194: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10195: || l_usage_total_exposure );
10196: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
10197: || l_cum_total_from_ar );
10198: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
10199: || l_total_from_ar);
10200: OE_DEBUG_PUB.ADD(' l_total_from_br = '
10194: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10195: || l_usage_total_exposure );
10196: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
10197: || l_cum_total_from_ar );
10198: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
10199: || l_total_from_ar);
10200: OE_DEBUG_PUB.ADD(' l_total_from_br = '
10201: || l_total_from_br);
10202: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
10196: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
10197: || l_cum_total_from_ar );
10198: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
10199: || l_total_from_ar);
10200: OE_DEBUG_PUB.ADD(' l_total_from_br = '
10201: || l_total_from_br);
10202: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
10203: || l_cum_payments_at_risk );
10204: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
10198: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
10199: || l_total_from_ar);
10200: OE_DEBUG_PUB.ADD(' l_total_from_br = '
10201: || l_total_from_br);
10202: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
10203: || l_cum_payments_at_risk );
10204: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
10205: || l_payments_at_risk);
10206: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
10200: OE_DEBUG_PUB.ADD(' l_total_from_br = '
10201: || l_total_from_br);
10202: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
10203: || l_cum_payments_at_risk );
10204: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
10205: || l_payments_at_risk);
10206: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
10207: || l_payments_at_risk_br);
10208: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
10202: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
10203: || l_cum_payments_at_risk );
10204: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
10205: || l_payments_at_risk);
10206: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
10207: || l_payments_at_risk_br);
10208: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
10209: || l_cum_total_on_order );
10210: OE_DEBUG_PUB.ADD(' l_total_on_order = '
10204: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
10205: || l_payments_at_risk);
10206: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
10207: || l_payments_at_risk_br);
10208: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
10209: || l_cum_total_on_order );
10210: OE_DEBUG_PUB.ADD(' l_total_on_order = '
10211: || l_total_on_order );
10212: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
10206: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
10207: || l_payments_at_risk_br);
10208: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
10209: || l_cum_total_on_order );
10210: OE_DEBUG_PUB.ADD(' l_total_on_order = '
10211: || l_total_on_order );
10212: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
10213: || l_uninvoiced_hdr_freight1 );
10214: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
10208: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
10209: || l_cum_total_on_order );
10210: OE_DEBUG_PUB.ADD(' l_total_on_order = '
10211: || l_total_on_order );
10212: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
10213: || l_uninvoiced_hdr_freight1 );
10214: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
10215: || l_uninvoiced_hdr_freight2 );
10216: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
10210: OE_DEBUG_PUB.ADD(' l_total_on_order = '
10211: || l_total_on_order );
10212: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
10213: || l_uninvoiced_hdr_freight1 );
10214: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
10215: || l_uninvoiced_hdr_freight2 );
10216: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
10217: || l_cum_total_commitment );
10218: OE_DEBUG_PUB.ADD(' l_total_commitment = '
10212: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
10213: || l_uninvoiced_hdr_freight1 );
10214: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
10215: || l_uninvoiced_hdr_freight2 );
10216: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
10217: || l_cum_total_commitment );
10218: OE_DEBUG_PUB.ADD(' l_total_commitment = '
10219: || l_total_commitment );
10220: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10214: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
10215: || l_uninvoiced_hdr_freight2 );
10216: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
10217: || l_cum_total_commitment );
10218: OE_DEBUG_PUB.ADD(' l_total_commitment = '
10219: || l_total_commitment );
10220: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10221: || l_usage_total_exposure );
10222:
10216: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
10217: || l_cum_total_commitment );
10218: OE_DEBUG_PUB.ADD(' l_total_commitment = '
10219: || l_total_commitment );
10220: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10221: || l_usage_total_exposure );
10222:
10223: -- no hold
10224: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
10220: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10221: || l_usage_total_exposure );
10222:
10223: -- no hold
10224: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
10225: || l_cum_total_no_hold );
10226: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
10227: || l_total_no_hold );
10228: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
10222:
10223: -- no hold
10224: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
10225: || l_cum_total_no_hold );
10226: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
10227: || l_total_no_hold );
10228: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
10229: || l_no_hold_hdr_freight1 );
10230: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
10224: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
10225: || l_cum_total_no_hold );
10226: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
10227: || l_total_no_hold );
10228: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
10229: || l_no_hold_hdr_freight1 );
10230: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
10231: || l_no_hold_hdr_freight2);
10232: -- external
10226: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
10227: || l_total_no_hold );
10228: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
10229: || l_no_hold_hdr_freight1 );
10230: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
10231: || l_no_hold_hdr_freight2);
10232: -- external
10233: OE_DEBUG_PUB.ADD(' l_external_exposure = '
10234: || l_external_exposure );
10229: || l_no_hold_hdr_freight1 );
10230: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
10231: || l_no_hold_hdr_freight2);
10232: -- external
10233: OE_DEBUG_PUB.ADD(' l_external_exposure = '
10234: || l_external_exposure );
10235: OE_DEBUG_PUB.ADD(' l_cum_external_exposure = '
10236: || l_cum_external_exposure );
10237: -- details
10231: || l_no_hold_hdr_freight2);
10232: -- external
10233: OE_DEBUG_PUB.ADD(' l_external_exposure = '
10234: || l_external_exposure );
10235: OE_DEBUG_PUB.ADD(' l_cum_external_exposure = '
10236: || l_cum_external_exposure );
10237: -- details
10238: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
10239: || l_cum_total_on_hold );
10234: || l_external_exposure );
10235: OE_DEBUG_PUB.ADD(' l_cum_external_exposure = '
10236: || l_cum_external_exposure );
10237: -- details
10238: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
10239: || l_cum_total_on_hold );
10240: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
10241: || l_total_on_hold );
10242: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
10236: || l_cum_external_exposure );
10237: -- details
10238: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
10239: || l_cum_total_on_hold );
10240: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
10241: || l_total_on_hold );
10242: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
10243: || l_hold_hdr_freight1 );
10244: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
10238: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
10239: || l_cum_total_on_hold );
10240: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
10241: || l_total_on_hold );
10242: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
10243: || l_hold_hdr_freight1 );
10244: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
10245: || l_hold_hdr_freight2);
10246: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
10240: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
10241: || l_total_on_hold );
10242: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
10243: || l_hold_hdr_freight1 );
10244: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
10245: || l_hold_hdr_freight2);
10246: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
10247: || l_cum_order_amount );
10248: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
10242: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
10243: || l_hold_hdr_freight1 );
10244: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
10245: || l_hold_hdr_freight2);
10246: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
10247: || l_cum_order_amount );
10248: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
10249: || l_cum_order_hold_amount );
10250: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
10244: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
10245: || l_hold_hdr_freight2);
10246: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
10247: || l_cum_order_amount );
10248: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
10249: || l_cum_order_hold_amount );
10250: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
10251: || l_cum_ar_amount);
10252:
10246: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
10247: || l_cum_order_amount );
10248: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
10249: || l_cum_order_hold_amount );
10250: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
10251: || l_cum_ar_amount);
10252:
10253: OE_DEBUG_PUB.ADD(' Call currency conversion for exposure ' );
10254: OE_DEBUG_PUB.Add(' GL_CURRENCY = '||
10249: || l_cum_order_hold_amount );
10250: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
10251: || l_cum_ar_amount);
10252:
10253: OE_DEBUG_PUB.ADD(' Call currency conversion for exposure ' );
10254: OE_DEBUG_PUB.Add(' GL_CURRENCY = '||
10255: OE_Credit_Engine_GRP.GL_currency );
10256: END IF;
10257:
10250: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
10251: || l_cum_ar_amount);
10252:
10253: OE_DEBUG_PUB.ADD(' Call currency conversion for exposure ' );
10254: OE_DEBUG_PUB.Add(' GL_CURRENCY = '||
10255: OE_Credit_Engine_GRP.GL_currency );
10256: END IF;
10257:
10258:
10257:
10258:
10259: IF OE_Credit_Engine_GRP.GL_currency IS NULL
10260: THEN
10261: OE_DEBUG_PUB.ADD(' Call GET_GL_currency ');
10262:
10263: OE_Credit_Engine_GRP.GL_currency :=
10264: OE_CREDIT_CHECK_UTIL.GET_GL_currency ;
10265:
10262:
10263: OE_Credit_Engine_GRP.GL_currency :=
10264: OE_CREDIT_CHECK_UTIL.GET_GL_currency ;
10265:
10266: OE_DEBUG_PUB.ADD(' GL_CURRENCY after = '
10267: || OE_Credit_Engine_GRP.GL_currency );
10268:
10269: END IF;
10270:
10284: IF p_need_exposure_details = 'Y'
10285: THEN
10286: IF G_debug_flag = 'Y'
10287: THEN
10288: oe_debug_pub.add( ' Into p_need_exposure_details ');
10289: END IF;
10290:
10291: l_limit_cum_order_amount :=
10292: OE_CREDIT_CHECK_UTIL.CONVERT_CURRENCY_AMOUNT
10334: END IF;
10335:
10336: IF G_debug_flag = 'Y'
10337: THEN
10338: OE_DEBUG_PUB.ADD(' l_limit_total_exposure = '
10339: || l_limit_total_exposure );
10340: OE_DEBUG_PUB.ADD(' l_total_exposure = '|| l_total_exposure );
10341: END IF;
10342:
10336: IF G_debug_flag = 'Y'
10337: THEN
10338: OE_DEBUG_PUB.ADD(' l_limit_total_exposure = '
10339: || l_limit_total_exposure );
10340: OE_DEBUG_PUB.ADD(' l_total_exposure = '|| l_total_exposure );
10341: END IF;
10342:
10343: l_limit_total_exposure := 0;
10344: l_usage_total_exposure := 0;
10382: l_limit_cum_ar_amount := 0 ;
10383:
10384: IF G_debug_flag = 'Y'
10385: THEN
10386: OE_DEBUG_PUB.Add('--------------------------');
10387: OE_DEBUG_PUB.Add(' ');
10388: END IF;
10389:
10390: END LOOP ; -- CURRENCY LOOP
10383:
10384: IF G_debug_flag = 'Y'
10385: THEN
10386: OE_DEBUG_PUB.Add('--------------------------');
10387: OE_DEBUG_PUB.Add(' ');
10388: END IF;
10389:
10390: END LOOP ; -- CURRENCY LOOP
10391:
10391:
10392: ELSE -- SITE
10393: IF G_debug_flag = 'Y'
10394: THEN
10395: OE_DEBUG_PUB.Add('Get_order_Exposure SITE : ',1);
10396: END IF;
10397:
10398: l_current_usage_cur := NULL ;
10399:
10402: l_current_usage_cur := NULL ;
10403:
10404: IF G_debug_flag = 'Y'
10405: THEN
10406: OE_DEBUG_PUB.ADD(' ');
10407: OE_DEBUG_PUB.ADD('############################### ');
10408: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
10409: OE_DEBUG_PUB.ADD('############################### ');
10410: OE_DEBUG_PUB.ADD(' ');
10403:
10404: IF G_debug_flag = 'Y'
10405: THEN
10406: OE_DEBUG_PUB.ADD(' ');
10407: OE_DEBUG_PUB.ADD('############################### ');
10408: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
10409: OE_DEBUG_PUB.ADD('############################### ');
10410: OE_DEBUG_PUB.ADD(' ');
10411: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10404: IF G_debug_flag = 'Y'
10405: THEN
10406: OE_DEBUG_PUB.ADD(' ');
10407: OE_DEBUG_PUB.ADD('############################### ');
10408: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
10409: OE_DEBUG_PUB.ADD('############################### ');
10410: OE_DEBUG_PUB.ADD(' ');
10411: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10412: l_current_usage_cur );
10405: THEN
10406: OE_DEBUG_PUB.ADD(' ');
10407: OE_DEBUG_PUB.ADD('############################### ');
10408: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
10409: OE_DEBUG_PUB.ADD('############################### ');
10410: OE_DEBUG_PUB.ADD(' ');
10411: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10412: l_current_usage_cur );
10413: OE_DEBUG_PUB.ADD(' ');
10406: OE_DEBUG_PUB.ADD(' ');
10407: OE_DEBUG_PUB.ADD('############################### ');
10408: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
10409: OE_DEBUG_PUB.ADD('############################### ');
10410: OE_DEBUG_PUB.ADD(' ');
10411: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10412: l_current_usage_cur );
10413: OE_DEBUG_PUB.ADD(' ');
10414: OE_DEBUG_PUB.ADD('.');
10407: OE_DEBUG_PUB.ADD('############################### ');
10408: OE_DEBUG_PUB.ADD('USAGE CURR = '|| p_usage_curr(i).usage_curr_code );
10409: OE_DEBUG_PUB.ADD('############################### ');
10410: OE_DEBUG_PUB.ADD(' ');
10411: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10412: l_current_usage_cur );
10413: OE_DEBUG_PUB.ADD(' ');
10414: OE_DEBUG_PUB.ADD('.');
10415: END IF;
10409: OE_DEBUG_PUB.ADD('############################### ');
10410: OE_DEBUG_PUB.ADD(' ');
10411: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10412: l_current_usage_cur );
10413: OE_DEBUG_PUB.ADD(' ');
10414: OE_DEBUG_PUB.ADD('.');
10415: END IF;
10416:
10417: l_current_usage_cur := p_usage_curr(i).usage_curr_code ;
10410: OE_DEBUG_PUB.ADD(' ');
10411: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
10412: l_current_usage_cur );
10413: OE_DEBUG_PUB.ADD(' ');
10414: OE_DEBUG_PUB.ADD('.');
10415: END IF;
10416:
10417: l_current_usage_cur := p_usage_curr(i).usage_curr_code ;
10418:
10418:
10419: -- get site external exposure
10420: IF l_incl_external_exposure_flag = 'Y'
10421: THEN
10422: OE_DEBUG_PUB.Add(' Get site external exposure balance ');
10423:
10424: OPEN site_external_exposure_csr (p_usage_curr(i).usage_curr_code);
10425: FETCH site_external_exposure_csr INTO l_external_exposure;
10426: IF site_external_exposure_csr%NOTFOUND
10424: OPEN site_external_exposure_csr (p_usage_curr(i).usage_curr_code);
10425: FETCH site_external_exposure_csr INTO l_external_exposure;
10426: IF site_external_exposure_csr%NOTFOUND
10427: THEN
10428: OE_DEBUG_PUB.Add(' No external exposure balance found');
10429: l_external_exposure := 0;
10430: END IF;
10431: CLOSE site_external_exposure_csr;
10432: END IF;
10436: IF l_open_ar_balance_flag = 'Y'
10437: THEN
10438: IF G_debug_flag = 'Y'
10439: THEN
10440: OE_DEBUG_PUB.Add(' Get site_ar_balance ');
10441: END IF;
10442:
10443: OPEN site_ar_balance (p_usage_curr(i).usage_curr_code);
10444: FETCH site_ar_balance INTO l_total_from_ar;
10444: FETCH site_ar_balance INTO l_total_from_ar;
10445: IF site_ar_balance%NOTFOUND
10446: THEN
10447: l_total_from_ar := 0 ;
10448: OE_DEBUG_PUB.Add(' No site_ar_balance found ');
10449: END IF;
10450:
10451: CLOSE site_ar_balance;
10452: END IF;
10454: IF l_include_risk_flag = 'Y'
10455: THEN
10456: IF G_debug_flag = 'Y'
10457: THEN
10458: OE_DEBUG_PUB.Add(' Get site_pay_risk ');
10459: END IF;
10460: OPEN site_pay_risk (p_usage_curr(i).usage_curr_code);
10461: FETCH site_pay_risk INTO l_payments_at_risk;
10462: IF site_pay_risk%NOTFOUND
10461: FETCH site_pay_risk INTO l_payments_at_risk;
10462: IF site_pay_risk%NOTFOUND
10463: THEN
10464: l_payments_at_risk := 0 ;
10465: OE_DEBUG_PUB.Add(' No site_pay_risk found ');
10466: END IF;
10467:
10468: CLOSE site_pay_risk;
10469: END IF;
10477: END IF;
10478:
10479: IF G_debug_flag = 'Y'
10480: THEN
10481: OE_DEBUG_PUB.Add(' l_drawee_site_use_id ==> '|| l_drawee_site_use_id );
10482: END IF;
10483:
10484: IF l_drawee_site_use_id is NOT NULL
10485: THEN
10486: IF l_open_ar_balance_flag = 'Y'
10487: THEN
10488: IF G_debug_flag = 'Y'
10489: THEN
10490: OE_DEBUG_PUB.Add(' Get site_br_balance ');
10491: END IF;
10492: OPEN site_br_balance (p_usage_curr(i).usage_curr_code);
10493: FETCH site_br_balance INTO l_total_from_br;
10494: IF site_br_balance%NOTFOUND
10493: FETCH site_br_balance INTO l_total_from_br;
10494: IF site_br_balance%NOTFOUND
10495: THEN
10496: l_total_from_br := 0 ;
10497: OE_DEBUG_PUB.Add(' No site_br_balance found ');
10498: END IF;
10499:
10500: CLOSE site_br_balance;
10501: END IF;
10503: IF l_include_risk_flag = 'Y'
10504: THEN
10505: IF G_debug_flag = 'Y'
10506: THEN
10507: OE_DEBUG_PUB.Add(' Get site_br_pay_risk ');
10508: END IF;
10509: OPEN site_br_pay_risk (p_usage_curr(i).usage_curr_code);
10510: FETCH site_br_pay_risk INTO l_payments_at_risk_br;
10511: IF site_br_pay_risk%NOTFOUND
10510: FETCH site_br_pay_risk INTO l_payments_at_risk_br;
10511: IF site_br_pay_risk%NOTFOUND
10512: THEN
10513: l_payments_at_risk_br := 0 ;
10514: OE_DEBUG_PUB.Add(' No site_pay_risk found ');
10515: END IF;
10516:
10517: CLOSE site_br_pay_risk;
10518: END IF; --ar
10518: END IF; --ar
10519: ELSE
10520: IF G_debug_flag = 'Y'
10521: THEN
10522: OE_DEBUG_PUB.Add(' No site BR required ');
10523: END IF;
10524: END IF;
10525: --vto2
10526: IF l_uninvoiced_orders_flag = 'Y'
10527: THEN
10528: BEGIN
10529: IF G_debug_flag = 'Y'
10530: THEN
10531: OE_DEBUG_PUB.Add(' Begin OM Exposure for site ');
10532: END IF;
10533:
10534: IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
10535: IF G_debug_flag = 'Y'
10533:
10534: IF OE_Commitment_PVT.Do_Commitment_Sequencing THEN
10535: IF G_debug_flag = 'Y'
10536: THEN
10537: OE_DEBUG_PUB.Add(' site_commitments ');
10538: END IF;
10539:
10540: OPEN site_commitment_total (p_usage_curr(i).usage_curr_code);
10541: FETCH site_commitment_total INTO l_total_commitment ;
10541: FETCH site_commitment_total INTO l_total_commitment ;
10542: IF site_commitment_total%NOTFOUND
10543: THEN
10544: l_total_commitment := 0 ;
10545: OE_DEBUG_PUB.Add('No site commitments found ');
10546: END IF;
10547:
10548: CLOSE site_commitment_total ;
10549: END IF;
10557: -----returns are not included
10558:
10559: IF G_debug_flag = 'Y'
10560: THEN
10561: OE_DEBUG_PUB.Add(' Get site_uninvoiced_orders,site_uninvoiced_orders ');
10562: END IF;
10563:
10564: OPEN site_uninvoiced_orders(p_usage_curr(i).usage_curr_code);
10565: FETCH site_uninvoiced_orders INTO l_total_on_order;
10566:
10567: IF site_uninvoiced_orders%NOTFOUND
10568: THEN
10569: l_total_on_order := 0 ;
10570: OE_DEBUG_PUB.Add(' No site_uninvoiced_orders found ');
10571: END IF;
10572:
10573: CLOSE site_uninvoiced_orders;
10574:
10576: -----returns are included
10577:
10578: IF G_debug_flag = 'Y'
10579: THEN
10580: OE_DEBUG_PUB.Add(' open site_uninvoiced_orders_ret ');
10581: END IF;
10582:
10583: OPEN site_uninvoiced_orders_ret(p_usage_curr(i).usage_curr_code);
10584: FETCH site_uninvoiced_orders_ret INTO l_total_on_order;
10585:
10586: IF site_uninvoiced_orders_ret%NOTFOUND
10587: THEN
10588: l_total_on_order := 0 ;
10589: OE_DEBUG_PUB.Add(' No site_uninvoiced_orders found ');
10590: END IF;
10591:
10592: CLOSE site_uninvoiced_orders_ret;
10593:
10601: THEN
10602: -----returns are not included
10603: IF G_debug_flag = 'Y'
10604: THEN
10605: OE_DEBUG_PUB.Add(' Get site_uninv_hdr_freight1 ');
10606: END IF;
10607:
10608: OPEN site_uninv_hdr_freight1(p_usage_curr(i).usage_curr_code);
10609: FETCH site_uninv_hdr_freight1 INTO l_uninvoiced_hdr_freight1;
10610:
10611: IF site_uninv_hdr_freight1%NOTFOUND
10612: THEN
10613: l_uninvoiced_hdr_freight1 := 0 ;
10614: OE_DEBUG_PUB.Add(' No site_uninv_hdr_freight1 found ');
10615: END IF;
10616:
10617: CLOSE site_uninv_hdr_freight1;
10618:
10617: CLOSE site_uninv_hdr_freight1;
10618:
10619: IF G_debug_flag = 'Y'
10620: THEN
10621: OE_DEBUG_PUB.Add(' Get site_uninv_hdr_freight2 ');
10622: END IF;
10623:
10624: OPEN site_uninv_hdr_freight2(p_usage_curr(i).usage_curr_code);
10625:
10627:
10628: IF site_uninv_hdr_freight2%NOTFOUND
10629: THEN
10630: l_uninvoiced_hdr_freight2 := 0 ;
10631: OE_DEBUG_PUB.Add(' No site_uninv_hdr_freight2 found ');
10632: END IF;
10633:
10634: CLOSE site_uninv_hdr_freight2;
10635:
10636: ELSE
10637: -----returns are included
10638: IF G_debug_flag = 'Y'
10639: THEN
10640: OE_DEBUG_PUB.Add(' Get site_uninv_hdr_freight1_ret ');
10641: END IF;
10642:
10643: OPEN site_uninv_hdr_freight1_ret(p_usage_curr(i).usage_curr_code);
10644: FETCH site_uninv_hdr_freight1_ret INTO l_uninvoiced_hdr_freight1;
10645:
10646: IF site_uninv_hdr_freight1_ret%NOTFOUND
10647: THEN
10648: l_uninvoiced_hdr_freight1 := 0 ;
10649: OE_DEBUG_PUB.Add(' No site_uninv_hdr_freight1 found ');
10650: END IF;
10651:
10652: CLOSE site_uninv_hdr_freight1_ret;
10653:
10652: CLOSE site_uninv_hdr_freight1_ret;
10653:
10654: IF G_debug_flag = 'Y'
10655: THEN
10656: OE_DEBUG_PUB.Add(' Get site_uninv_hdr_freight2_ret ');
10657: END IF;
10658:
10659: OPEN site_uninv_hdr_freight2_ret(p_usage_curr(i).usage_curr_code);
10660:
10662:
10663: IF site_uninv_hdr_freight2_ret%NOTFOUND
10664: THEN
10665: l_uninvoiced_hdr_freight2 := 0 ;
10666: OE_DEBUG_PUB.Add(' No site_uninv_hdr_freight2_ret found ');
10667: END IF;
10668:
10669: CLOSE site_uninv_hdr_freight2_ret;
10670:
10679: -----returns are not included
10680:
10681: IF G_debug_flag = 'Y'
10682: THEN
10683: OE_DEBUG_PUB.Add(' Get site_orders_not_on_hold ');
10684: END IF;
10685:
10686: OPEN site_orders_not_on_hold (p_usage_curr(i).usage_curr_code);
10687:
10689:
10690: IF site_orders_not_on_hold%NOTFOUND
10691: THEN
10692: l_total_no_hold := 0 ;
10693: OE_DEBUG_PUB.Add(' No site orders not on hold found ');
10694: END IF;
10695:
10696: CLOSE site_orders_not_on_hold;
10697: -- Get orders on hold amount if need_exposure_details is enabled
10698: IF l_need_exposure_details = 'Y'
10699: THEN
10700: IF G_debug_flag = 'Y'
10701: THEN
10702: OE_DEBUG_PUB.Add(' Get site_orders_on_hold ');
10703: END IF;
10704:
10705: OPEN site_orders_on_hold (p_usage_curr(i).usage_curr_code);
10706: FETCH site_orders_on_hold INTO l_total_on_hold;
10707:
10708: IF site_orders_on_hold%NOTFOUND
10709: THEN
10710: l_total_on_hold := 0 ;
10711: OE_DEBUG_PUB.Add(' No site orders on hold found ');
10712: END IF;
10713:
10714: CLOSE site_orders_on_hold;
10715: END IF;
10718: -----returns are included
10719:
10720: IF G_debug_flag = 'Y'
10721: THEN
10722: OE_DEBUG_PUB.Add(' Get site_orders_not_on_hold_ret ');
10723: END IF;
10724:
10725: OPEN site_orders_not_on_hold_ret (p_usage_curr(i).usage_curr_code);
10726:
10728:
10729: IF site_orders_not_on_hold_ret%NOTFOUND
10730: THEN
10731: l_total_no_hold := 0 ;
10732: OE_DEBUG_PUB.Add(' No site orders not on hold found ');
10733: END IF;
10734:
10735: CLOSE site_orders_not_on_hold_ret;
10736:
10738: IF l_need_exposure_details = 'Y'
10739: THEN
10740: IF G_debug_flag = 'Y'
10741: THEN
10742: OE_DEBUG_PUB.Add(' Get site_orders_on_hold_ret ');
10743: END IF;
10744:
10745: OPEN site_orders_on_hold_ret(p_usage_curr(i).usage_curr_code);
10746: FETCH site_orders_on_hold_ret INTO l_total_on_hold;
10747:
10748: IF site_orders_on_hold_ret%NOTFOUND
10749: THEN
10750: l_total_on_hold := 0 ;
10751: OE_DEBUG_PUB.Add(' No site orders on hold found ');
10752: END IF;
10753:
10754: CLOSE site_orders_on_hold_ret;
10755: END IF;
10768: THEN
10769: -----returns are not included
10770: IF G_debug_flag = 'Y'
10771: THEN
10772: OE_DEBUG_PUB.Add(' Get site_no_hold_hdr_freight1 ');
10773: END IF;
10774:
10775: OPEN site_no_hold_hdr_freight1(p_usage_curr(i).usage_curr_code);
10776: FETCH site_no_hold_hdr_freight1 INTO l_no_hold_hdr_freight1;
10777:
10778: IF site_no_hold_hdr_freight1%NOTFOUND
10779: THEN
10780: l_no_hold_hdr_freight1 := 0 ;
10781: OE_DEBUG_PUB.Add(' No site_no_hold_hdr_freight1 found ');
10782: END IF;
10783:
10784: CLOSE site_no_hold_hdr_freight1;
10785:
10784: CLOSE site_no_hold_hdr_freight1;
10785:
10786: IF G_debug_flag = 'Y'
10787: THEN
10788: OE_DEBUG_PUB.Add(' Get site_no_hold_hdr_freight2 ');
10789: END IF;
10790:
10791: OPEN site_no_hold_hdr_freight2(p_usage_curr(i).usage_curr_code);
10792:
10794:
10795: IF site_no_hold_hdr_freight2%NOTFOUND
10796: THEN
10797: l_no_hold_hdr_freight2 := 0 ;
10798: OE_DEBUG_PUB.Add(' No site_no_hold_hdr_freight2 found ');
10799: END IF;
10800:
10801: CLOSE site_no_hold_hdr_freight2;
10802:
10804: IF l_need_exposure_details = 'Y'
10805: THEN
10806: IF G_debug_flag = 'Y'
10807: THEN
10808: OE_DEBUG_PUB.Add(' Get site_hold_hdr_freight1 ');
10809: END IF;
10810:
10811: OPEN site_hold_hdr_freight1(p_usage_curr(i).usage_curr_code);
10812: FETCH site_hold_hdr_freight1 INTO l_hold_hdr_freight1;
10813:
10814: IF site_hold_hdr_freight1%NOTFOUND
10815: THEN
10816: l_hold_hdr_freight1 := 0 ;
10817: OE_DEBUG_PUB.Add(' No site_hold_hdr_freight1 found ');
10818: END IF;
10819:
10820: CLOSE site_hold_hdr_freight1;
10821:
10820: CLOSE site_hold_hdr_freight1;
10821:
10822: IF G_debug_flag = 'Y'
10823: THEN
10824: OE_DEBUG_PUB.Add(' Get site_hold_hdr_freight2 ');
10825: END IF;
10826:
10827: OPEN site_hold_hdr_freight2(p_usage_curr(i).usage_curr_code);
10828: FETCH site_hold_hdr_freight2 INTO l_hold_hdr_freight2;
10829:
10830: IF site_hold_hdr_freight2%NOTFOUND
10831: THEN
10832: l_hold_hdr_freight2 := 0 ;
10833: OE_DEBUG_PUB.Add(' No site_hold_hdr_freight2 found ');
10834: END IF;
10835: CLOSE site_hold_hdr_freight2;
10836: END IF; -- details freight on hold
10837: ELSE
10837: ELSE
10838: -----returns are included
10839: IF G_debug_flag = 'Y'
10840: THEN
10841: OE_DEBUG_PUB.Add(' Get site_no_hold_hdr_freight1_ret ');
10842: END IF;
10843:
10844: OPEN site_no_hold_hdr_freight1_ret(p_usage_curr(i).usage_curr_code);
10845: FETCH site_no_hold_hdr_freight1_ret INTO l_no_hold_hdr_freight1;
10846:
10847: IF site_no_hold_hdr_freight1_ret%NOTFOUND
10848: THEN
10849: l_no_hold_hdr_freight1 := 0 ;
10850: OE_DEBUG_PUB.Add(' No site_no_hold_hdr_freight1 found ');
10851: END IF;
10852:
10853: CLOSE site_no_hold_hdr_freight1_ret;
10854:
10853: CLOSE site_no_hold_hdr_freight1_ret;
10854:
10855: IF G_debug_flag = 'Y'
10856: THEN
10857: OE_DEBUG_PUB.Add(' Get site_no_hold_hdr_freight2_ret ');
10858: END IF;
10859:
10860: OPEN site_no_hold_hdr_freight2_ret(p_usage_curr(i).usage_curr_code);
10861:
10863:
10864: IF site_no_hold_hdr_freight2_ret%NOTFOUND
10865: THEN
10866: l_no_hold_hdr_freight2 := 0 ;
10867: OE_DEBUG_PUB.Add(' No site_no_hold_hdr_freight2_ret found ');
10868: END IF;
10869:
10870: CLOSE site_no_hold_hdr_freight2_ret;
10871:
10873: IF l_need_exposure_details = 'Y'
10874: THEN
10875: IF G_debug_flag = 'Y'
10876: THEN
10877: OE_DEBUG_PUB.Add(' Get site_hold_hdr_freight1_ret ');
10878: END IF;
10879:
10880: OPEN site_hold_hdr_freight1_ret(p_usage_curr(i).usage_curr_code);
10881: FETCH site_hold_hdr_freight1_ret INTO l_hold_hdr_freight1;
10882:
10883: IF site_hold_hdr_freight1_ret%NOTFOUND
10884: THEN
10885: l_hold_hdr_freight1 := 0 ;
10886: OE_DEBUG_PUB.Add(' No site_hold_hdr_freight1_ret found ');
10887: END IF;
10888:
10889: CLOSE site_hold_hdr_freight1_ret;
10890:
10889: CLOSE site_hold_hdr_freight1_ret;
10890:
10891: IF G_debug_flag = 'Y'
10892: THEN
10893: OE_DEBUG_PUB.Add(' Get site_hold_hdr_freight2_ret ');
10894: END IF;
10895:
10896: OPEN site_hold_hdr_freight2_ret(p_usage_curr(i).usage_curr_code);
10897: FETCH site_hold_hdr_freight2_ret INTO l_hold_hdr_freight2;
10898:
10899: IF site_hold_hdr_freight2_ret%NOTFOUND
10900: THEN
10901: l_hold_hdr_freight2 := 0 ;
10902: OE_DEBUG_PUB.Add(' No site_hold_hdr_freight2_ret found ');
10903: END IF;
10904: CLOSE site_hold_hdr_freight2_ret;
10905: END IF; -- details freight on hold
10906: END IF; -----end of checking if returns are included
10909: END;
10910: ELSE
10911: IF G_debug_flag = 'Y'
10912: THEN
10913: OE_DEBUG_PUB.Add(' No OM Exposure calculation for site ',1);
10914: END IF;
10915: END IF;
10916:
10917: l_cum_total_from_ar := l_cum_total_from_ar + NVL(l_total_from_ar,0) +
10970:
10971: IF G_debug_flag = 'Y'
10972: THEN
10973:
10974: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10975: || l_usage_total_exposure );
10976: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
10977: || l_cum_total_from_ar );
10978: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
10972: THEN
10973:
10974: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10975: || l_usage_total_exposure );
10976: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
10977: || l_cum_total_from_ar );
10978: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
10979: || l_total_from_ar);
10980: OE_DEBUG_PUB.ADD(' l_total_from_br = '
10974: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10975: || l_usage_total_exposure );
10976: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
10977: || l_cum_total_from_ar );
10978: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
10979: || l_total_from_ar);
10980: OE_DEBUG_PUB.ADD(' l_total_from_br = '
10981: || l_total_from_br);
10982: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
10976: OE_DEBUG_PUB.ADD(' l_cum_total_from_ar = '
10977: || l_cum_total_from_ar );
10978: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
10979: || l_total_from_ar);
10980: OE_DEBUG_PUB.ADD(' l_total_from_br = '
10981: || l_total_from_br);
10982: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
10983: || l_cum_payments_at_risk );
10984: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
10978: OE_DEBUG_PUB.ADD(' l_total_from_ar = '
10979: || l_total_from_ar);
10980: OE_DEBUG_PUB.ADD(' l_total_from_br = '
10981: || l_total_from_br);
10982: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
10983: || l_cum_payments_at_risk );
10984: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
10985: || l_payments_at_risk);
10986: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
10980: OE_DEBUG_PUB.ADD(' l_total_from_br = '
10981: || l_total_from_br);
10982: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
10983: || l_cum_payments_at_risk );
10984: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
10985: || l_payments_at_risk);
10986: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
10987: || l_payments_at_risk_br);
10988: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
10982: OE_DEBUG_PUB.ADD(' l_cum_payments_at_risk = '
10983: || l_cum_payments_at_risk );
10984: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
10985: || l_payments_at_risk);
10986: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
10987: || l_payments_at_risk_br);
10988: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
10989: || l_cum_total_on_order );
10990: OE_DEBUG_PUB.ADD(' l_total_on_order = '
10984: OE_DEBUG_PUB.ADD(' l_payments_at_risk = '
10985: || l_payments_at_risk);
10986: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
10987: || l_payments_at_risk_br);
10988: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
10989: || l_cum_total_on_order );
10990: OE_DEBUG_PUB.ADD(' l_total_on_order = '
10991: || l_total_on_order );
10992: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
10986: OE_DEBUG_PUB.ADD(' l_payments_at_risk_br = '
10987: || l_payments_at_risk_br);
10988: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
10989: || l_cum_total_on_order );
10990: OE_DEBUG_PUB.ADD(' l_total_on_order = '
10991: || l_total_on_order );
10992: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
10993: || l_uninvoiced_hdr_freight1 );
10994: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
10988: OE_DEBUG_PUB.ADD(' l_cum_total_on_order = '
10989: || l_cum_total_on_order );
10990: OE_DEBUG_PUB.ADD(' l_total_on_order = '
10991: || l_total_on_order );
10992: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
10993: || l_uninvoiced_hdr_freight1 );
10994: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
10995: || l_uninvoiced_hdr_freight2 );
10996: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
10990: OE_DEBUG_PUB.ADD(' l_total_on_order = '
10991: || l_total_on_order );
10992: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
10993: || l_uninvoiced_hdr_freight1 );
10994: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
10995: || l_uninvoiced_hdr_freight2 );
10996: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
10997: || l_cum_total_commitment );
10998: OE_DEBUG_PUB.ADD(' l_total_commitment = '
10992: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight1 = '
10993: || l_uninvoiced_hdr_freight1 );
10994: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
10995: || l_uninvoiced_hdr_freight2 );
10996: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
10997: || l_cum_total_commitment );
10998: OE_DEBUG_PUB.ADD(' l_total_commitment = '
10999: || l_total_commitment );
11000: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
10994: OE_DEBUG_PUB.ADD(' l_uninvoiced_hdr_freight2 = '
10995: || l_uninvoiced_hdr_freight2 );
10996: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
10997: || l_cum_total_commitment );
10998: OE_DEBUG_PUB.ADD(' l_total_commitment = '
10999: || l_total_commitment );
11000: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
11001: || l_usage_total_exposure );
11002:
10996: OE_DEBUG_PUB.ADD(' l_cum_total_commitment = '
10997: || l_cum_total_commitment );
10998: OE_DEBUG_PUB.ADD(' l_total_commitment = '
10999: || l_total_commitment );
11000: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
11001: || l_usage_total_exposure );
11002:
11003: -- details
11004: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
11000: OE_DEBUG_PUB.ADD(' l_usage_total_exposure = '
11001: || l_usage_total_exposure );
11002:
11003: -- details
11004: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
11005: || l_cum_total_on_hold );
11006: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
11007: || l_total_on_hold );
11008: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
11002:
11003: -- details
11004: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
11005: || l_cum_total_on_hold );
11006: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
11007: || l_total_on_hold );
11008: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
11009: || l_hold_hdr_freight1 );
11010: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
11004: OE_DEBUG_PUB.ADD(' l_cum_total_on_hold = '
11005: || l_cum_total_on_hold );
11006: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
11007: || l_total_on_hold );
11008: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
11009: || l_hold_hdr_freight1 );
11010: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
11011: || l_hold_hdr_freight2);
11012: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
11006: OE_DEBUG_PUB.ADD(' l_total_on_hold = '
11007: || l_total_on_hold );
11008: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
11009: || l_hold_hdr_freight1 );
11010: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
11011: || l_hold_hdr_freight2);
11012: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
11013: || l_cum_order_amount );
11014: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
11008: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight1 = '
11009: || l_hold_hdr_freight1 );
11010: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
11011: || l_hold_hdr_freight2);
11012: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
11013: || l_cum_order_amount );
11014: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
11015: || l_cum_order_hold_amount );
11016: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
11010: OE_DEBUG_PUB.ADD(' l_hold_hdr_freight2 = '
11011: || l_hold_hdr_freight2);
11012: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
11013: || l_cum_order_amount );
11014: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
11015: || l_cum_order_hold_amount );
11016: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
11017: || l_cum_ar_amount);
11018:
11012: OE_DEBUG_PUB.ADD(' l_cum_order_amount = '
11013: || l_cum_order_amount );
11014: OE_DEBUG_PUB.ADD(' l_cum_order_hold_amount = '
11015: || l_cum_order_hold_amount );
11016: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
11017: || l_cum_ar_amount);
11018:
11019: -- external
11020: OE_DEBUG_PUB.ADD(' l_external_exposure = '
11016: OE_DEBUG_PUB.ADD(' l_cum_ar_amount = '
11017: || l_cum_ar_amount);
11018:
11019: -- external
11020: OE_DEBUG_PUB.ADD(' l_external_exposure = '
11021: || l_external_exposure );
11022: OE_DEBUG_PUB.ADD(' l_cum_external_exposure = '
11023: || l_cum_external_exposure );
11024:
11018:
11019: -- external
11020: OE_DEBUG_PUB.ADD(' l_external_exposure = '
11021: || l_external_exposure );
11022: OE_DEBUG_PUB.ADD(' l_cum_external_exposure = '
11023: || l_cum_external_exposure );
11024:
11025: -- no holds
11026: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
11022: OE_DEBUG_PUB.ADD(' l_cum_external_exposure = '
11023: || l_cum_external_exposure );
11024:
11025: -- no holds
11026: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
11027: || l_cum_total_no_hold );
11028: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
11029: || l_total_no_hold );
11030: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
11024:
11025: -- no holds
11026: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
11027: || l_cum_total_no_hold );
11028: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
11029: || l_total_no_hold );
11030: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
11031: || l_no_hold_hdr_freight1 );
11032: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
11026: OE_DEBUG_PUB.ADD(' l_cum_total_no_hold = '
11027: || l_cum_total_no_hold );
11028: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
11029: || l_total_no_hold );
11030: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
11031: || l_no_hold_hdr_freight1 );
11032: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
11033: || l_no_hold_hdr_freight2);
11034:
11028: OE_DEBUG_PUB.ADD(' l_total_no_hold = '
11029: || l_total_no_hold );
11030: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight1 = '
11031: || l_no_hold_hdr_freight1 );
11032: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
11033: || l_no_hold_hdr_freight2);
11034:
11035: OE_DEBUG_PUB.ADD(' Call currency conversion for exposure ' );
11036:
11031: || l_no_hold_hdr_freight1 );
11032: OE_DEBUG_PUB.ADD(' l_no_hold_hdr_freight2 = '
11033: || l_no_hold_hdr_freight2);
11034:
11035: OE_DEBUG_PUB.ADD(' Call currency conversion for exposure ' );
11036:
11037: OE_DEBUG_PUB.Add(' GL_CURRENCY = '||
11038: OE_Credit_Engine_GRP.GL_currency );
11039: END IF;
11033: || l_no_hold_hdr_freight2);
11034:
11035: OE_DEBUG_PUB.ADD(' Call currency conversion for exposure ' );
11036:
11037: OE_DEBUG_PUB.Add(' GL_CURRENCY = '||
11038: OE_Credit_Engine_GRP.GL_currency );
11039: END IF;
11040:
11041: l_limit_total_exposure :=
11052: l_total_exposure := l_total_exposure + NVL(l_limit_total_exposure,0) ;
11053:
11054: IF l_need_exposure_details = 'Y' THEN
11055: IF G_debug_flag = 'Y' THEN
11056: oe_debug_pub.add( ' Into p_need_exposure_details ');
11057: END IF;
11058:
11059: l_limit_cum_order_amount :=
11060: OE_CREDIT_CHECK_UTIL.CONVERT_CURRENCY_AMOUNT
11100: END IF; -- details
11101:
11102: IF G_debug_flag = 'Y'
11103: THEN
11104: OE_DEBUG_PUB.ADD(' l_limit_total_exposure = '
11105: || l_limit_total_exposure );
11106: OE_DEBUG_PUB.ADD(' l_total_exposure = '|| l_total_exposure );
11107: END IF;
11108:
11102: IF G_debug_flag = 'Y'
11103: THEN
11104: OE_DEBUG_PUB.ADD(' l_limit_total_exposure = '
11105: || l_limit_total_exposure );
11106: OE_DEBUG_PUB.ADD(' l_total_exposure = '|| l_total_exposure );
11107: END IF;
11108:
11109:
11110: l_limit_total_exposure := 0 ;
11146: l_limit_cum_ar_amount := 0 ;
11147:
11148: IF G_debug_flag = 'Y'
11149: THEN
11150: OE_DEBUG_PUB.Add(' ');
11151: OE_DEBUG_PUB.Add('=====================================');
11152: OE_DEBUG_PUB.Add(' ');
11153: END IF;
11154:
11147:
11148: IF G_debug_flag = 'Y'
11149: THEN
11150: OE_DEBUG_PUB.Add(' ');
11151: OE_DEBUG_PUB.Add('=====================================');
11152: OE_DEBUG_PUB.Add(' ');
11153: END IF;
11154:
11155: END LOOP; -- currency loop
11148: IF G_debug_flag = 'Y'
11149: THEN
11150: OE_DEBUG_PUB.Add(' ');
11151: OE_DEBUG_PUB.Add('=====================================');
11152: OE_DEBUG_PUB.Add(' ');
11153: END IF;
11154:
11155: END LOOP; -- currency loop
11156:
11153: END IF;
11154:
11155: END LOOP; -- currency loop
11156:
11157: OE_DEBUG_PUB.ADD('** Out NOCOPY of usage currency loop ' );
11158:
11159: END IF;
11160:
11161: l_current_usage_cur := NULL;
11173: NVL(OE_CREDIT_CHECK_UTIL.g_current_order_value,0);
11174:
11175: IF G_debug_flag = 'Y'
11176: THEN
11177: OE_DEBUG_PUB.Add(' Current order value is available already = '
11178: || OE_CREDIT_CHECK_UTIL.g_current_order_value );
11179: OE_DEBUG_PUB.Add(' l_limit_current_order = '||
11180: l_limit_current_order );
11181: END IF;
11175: IF G_debug_flag = 'Y'
11176: THEN
11177: OE_DEBUG_PUB.Add(' Current order value is available already = '
11178: || OE_CREDIT_CHECK_UTIL.g_current_order_value );
11179: OE_DEBUG_PUB.Add(' l_limit_current_order = '||
11180: l_limit_current_order );
11181: END IF;
11182: END ; -- p_header_id
11183:
11183:
11184: ELSE
11185: l_limit_current_order := 0 ;
11186:
11187: OE_DEBUG_PUB.Add(' P_header_id is NULL, No current order check ',1);
11188:
11189: END IF ; -- p_header_id is NULL
11190:
11191: IF G_debug_flag = 'Y'
11189: END IF ; -- p_header_id is NULL
11190:
11191: IF G_debug_flag = 'Y'
11192: THEN
11193: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
11194: OE_DEBUG_PUB.Add(' l_order_amount = '|| l_order_amount);
11195: OE_DEBUG_PUB.Add(' l_order_hold_amount = '|| l_order_hold_amount);
11196: OE_DEBUG_PUB.Add(' l_ar_amount = '|| l_ar_amount);
11197: END IF;
11190:
11191: IF G_debug_flag = 'Y'
11192: THEN
11193: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
11194: OE_DEBUG_PUB.Add(' l_order_amount = '|| l_order_amount);
11195: OE_DEBUG_PUB.Add(' l_order_hold_amount = '|| l_order_hold_amount);
11196: OE_DEBUG_PUB.Add(' l_ar_amount = '|| l_ar_amount);
11197: END IF;
11198:
11191: IF G_debug_flag = 'Y'
11192: THEN
11193: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
11194: OE_DEBUG_PUB.Add(' l_order_amount = '|| l_order_amount);
11195: OE_DEBUG_PUB.Add(' l_order_hold_amount = '|| l_order_hold_amount);
11196: OE_DEBUG_PUB.Add(' l_ar_amount = '|| l_ar_amount);
11197: END IF;
11198:
11199: x_total_exposure := NVL(l_total_exposure,0) + NVL(l_limit_current_order,0) ;
11192: THEN
11193: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
11194: OE_DEBUG_PUB.Add(' l_order_amount = '|| l_order_amount);
11195: OE_DEBUG_PUB.Add(' l_order_hold_amount = '|| l_order_hold_amount);
11196: OE_DEBUG_PUB.Add(' l_ar_amount = '|| l_ar_amount);
11197: END IF;
11198:
11199: x_total_exposure := NVL(l_total_exposure,0) + NVL(l_limit_current_order,0) ;
11200:
11224: );
11225:
11226: IF G_debug_flag = 'Y'
11227: THEN
11228: OE_DEBUG_PUB.Add('after get_exposure_amount ');
11229: OE_DEBUG_PUB.Add(' x_return_status = '|| x_return_status );
11230: OE_DEBUG_PUB.Add(' l_exposure_amount = '|| l_exposure_amount );
11231: OE_DEBUG_PUB.Add(' err cur tbl count = '|| x_conversion_status.COUNT );
11232: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
11225:
11226: IF G_debug_flag = 'Y'
11227: THEN
11228: OE_DEBUG_PUB.Add('after get_exposure_amount ');
11229: OE_DEBUG_PUB.Add(' x_return_status = '|| x_return_status );
11230: OE_DEBUG_PUB.Add(' l_exposure_amount = '|| l_exposure_amount );
11231: OE_DEBUG_PUB.Add(' err cur tbl count = '|| x_conversion_status.COUNT );
11232: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
11233: OE_DEBUG_PUB.Add(' l_total_exposure = '|| l_total_exposure );
11226: IF G_debug_flag = 'Y'
11227: THEN
11228: OE_DEBUG_PUB.Add('after get_exposure_amount ');
11229: OE_DEBUG_PUB.Add(' x_return_status = '|| x_return_status );
11230: OE_DEBUG_PUB.Add(' l_exposure_amount = '|| l_exposure_amount );
11231: OE_DEBUG_PUB.Add(' err cur tbl count = '|| x_conversion_status.COUNT );
11232: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
11233: OE_DEBUG_PUB.Add(' l_total_exposure = '|| l_total_exposure );
11234: OE_DEBUG_PUB.Add(' x_total_exposure = '|| x_total_exposure );
11227: THEN
11228: OE_DEBUG_PUB.Add('after get_exposure_amount ');
11229: OE_DEBUG_PUB.Add(' x_return_status = '|| x_return_status );
11230: OE_DEBUG_PUB.Add(' l_exposure_amount = '|| l_exposure_amount );
11231: OE_DEBUG_PUB.Add(' err cur tbl count = '|| x_conversion_status.COUNT );
11232: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
11233: OE_DEBUG_PUB.Add(' l_total_exposure = '|| l_total_exposure );
11234: OE_DEBUG_PUB.Add(' x_total_exposure = '|| x_total_exposure );
11235: END IF;
11228: OE_DEBUG_PUB.Add('after get_exposure_amount ');
11229: OE_DEBUG_PUB.Add(' x_return_status = '|| x_return_status );
11230: OE_DEBUG_PUB.Add(' l_exposure_amount = '|| l_exposure_amount );
11231: OE_DEBUG_PUB.Add(' err cur tbl count = '|| x_conversion_status.COUNT );
11232: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
11233: OE_DEBUG_PUB.Add(' l_total_exposure = '|| l_total_exposure );
11234: OE_DEBUG_PUB.Add(' x_total_exposure = '|| x_total_exposure );
11235: END IF;
11236:
11229: OE_DEBUG_PUB.Add(' x_return_status = '|| x_return_status );
11230: OE_DEBUG_PUB.Add(' l_exposure_amount = '|| l_exposure_amount );
11231: OE_DEBUG_PUB.Add(' err cur tbl count = '|| x_conversion_status.COUNT );
11232: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
11233: OE_DEBUG_PUB.Add(' l_total_exposure = '|| l_total_exposure );
11234: OE_DEBUG_PUB.Add(' x_total_exposure = '|| x_total_exposure );
11235: END IF;
11236:
11237: x_total_exposure := NVL(x_total_exposure,0) + NVL(l_exposure_amount,0) ;
11230: OE_DEBUG_PUB.Add(' l_exposure_amount = '|| l_exposure_amount );
11231: OE_DEBUG_PUB.Add(' err cur tbl count = '|| x_conversion_status.COUNT );
11232: OE_DEBUG_PUB.Add(' l_limit_current_order = '|| l_limit_current_order );
11233: OE_DEBUG_PUB.Add(' l_total_exposure = '|| l_total_exposure );
11234: OE_DEBUG_PUB.Add(' x_total_exposure = '|| x_total_exposure );
11235: END IF;
11236:
11237: x_total_exposure := NVL(x_total_exposure,0) + NVL(l_exposure_amount,0) ;
11238:
11241: FOR f IN 1..x_conversion_status.COUNT
11242: LOOP
11243: IF G_debug_flag = 'Y'
11244: THEN
11245: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
11246: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||x_conversion_status(f).usage_curr_code
11247: ||' and credit limit currency '
11248: ||p_limit_curr_code
11249: ||' is missing for conversion type '
11242: LOOP
11243: IF G_debug_flag = 'Y'
11244: THEN
11245: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
11246: OE_DEBUG_PUB.ADD('!!!!! Exchange rate between '||x_conversion_status(f).usage_curr_code
11247: ||' and credit limit currency '
11248: ||p_limit_curr_code
11249: ||' is missing for conversion type '
11250: || NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
11247: ||' and credit limit currency '
11248: ||p_limit_curr_code
11249: ||' is missing for conversion type '
11250: || NVL(p_credit_check_rule_rec.user_conversion_type,'Corporate'),1);
11251: OE_DEBUG_PUB.ADD('!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!');
11252: END IF;
11253: END LOOP;
11254: ELSIF x_return_status = FND_API.G_RET_STS_ERROR
11255: THEN
11261: --bug# 2714553
11262:
11263: IF G_debug_flag = 'Y'
11264: THEN
11265: OE_DEBUG_PUB.Add(' ');
11266: OE_DEBUG_PUB.Add('XX*****-------------##########-------------------XX' );
11267: OE_DEBUG_PUB.Add(' ');
11268: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
11269: OE_DEBUG_PUB.Add(' ');
11262:
11263: IF G_debug_flag = 'Y'
11264: THEN
11265: OE_DEBUG_PUB.Add(' ');
11266: OE_DEBUG_PUB.Add('XX*****-------------##########-------------------XX' );
11267: OE_DEBUG_PUB.Add(' ');
11268: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
11269: OE_DEBUG_PUB.Add(' ');
11270: OE_DEBUG_PUB.Add('XX-------------- ##########------------------------XX' );
11263: IF G_debug_flag = 'Y'
11264: THEN
11265: OE_DEBUG_PUB.Add(' ');
11266: OE_DEBUG_PUB.Add('XX*****-------------##########-------------------XX' );
11267: OE_DEBUG_PUB.Add(' ');
11268: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
11269: OE_DEBUG_PUB.Add(' ');
11270: OE_DEBUG_PUB.Add('XX-------------- ##########------------------------XX' );
11271: OE_DEBUG_PUB.Add(' ');
11264: THEN
11265: OE_DEBUG_PUB.Add(' ');
11266: OE_DEBUG_PUB.Add('XX*****-------------##########-------------------XX' );
11267: OE_DEBUG_PUB.Add(' ');
11268: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
11269: OE_DEBUG_PUB.Add(' ');
11270: OE_DEBUG_PUB.Add('XX-------------- ##########------------------------XX' );
11271: OE_DEBUG_PUB.Add(' ');
11272: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_order_exposure',1);
11265: OE_DEBUG_PUB.Add(' ');
11266: OE_DEBUG_PUB.Add('XX*****-------------##########-------------------XX' );
11267: OE_DEBUG_PUB.Add(' ');
11268: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
11269: OE_DEBUG_PUB.Add(' ');
11270: OE_DEBUG_PUB.Add('XX-------------- ##########------------------------XX' );
11271: OE_DEBUG_PUB.Add(' ');
11272: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_order_exposure',1);
11273: END IF;
11266: OE_DEBUG_PUB.Add('XX*****-------------##########-------------------XX' );
11267: OE_DEBUG_PUB.Add(' ');
11268: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
11269: OE_DEBUG_PUB.Add(' ');
11270: OE_DEBUG_PUB.Add('XX-------------- ##########------------------------XX' );
11271: OE_DEBUG_PUB.Add(' ');
11272: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_order_exposure',1);
11273: END IF;
11274:
11267: OE_DEBUG_PUB.Add(' ');
11268: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
11269: OE_DEBUG_PUB.Add(' ');
11270: OE_DEBUG_PUB.Add('XX-------------- ##########------------------------XX' );
11271: OE_DEBUG_PUB.Add(' ');
11272: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_order_exposure',1);
11273: END IF;
11274:
11275: EXCEPTION
11268: OE_DEBUG_PUB.Add('Final Total Exposure amount = '|| x_total_exposure,1);
11269: OE_DEBUG_PUB.Add(' ');
11270: OE_DEBUG_PUB.Add('XX-------------- ##########------------------------XX' );
11271: OE_DEBUG_PUB.Add(' ');
11272: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_order_exposure',1);
11273: END IF;
11274:
11275: EXCEPTION
11276: WHEN GL_CURRENCY_API.NO_RATE THEN
11273: END IF;
11274:
11275: EXCEPTION
11276: WHEN GL_CURRENCY_API.NO_RATE THEN
11277: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE in get_order_exp',1);
11278: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
11279: l_current_usage_cur );
11280: x_conversion_status(1).usage_curr_code := l_current_usage_cur ;
11281:
11274:
11275: EXCEPTION
11276: WHEN GL_CURRENCY_API.NO_RATE THEN
11277: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE in get_order_exp',1);
11278: OE_DEBUG_PUB.ADD('l_current_usage_cur = '||
11279: l_current_usage_cur );
11280: x_conversion_status(1).usage_curr_code := l_current_usage_cur ;
11281:
11282: fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
11279: l_current_usage_cur );
11280: x_conversion_status(1).usage_curr_code := l_current_usage_cur ;
11281:
11282: fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
11283: OE_DEBUG_PUB.ADD('Exception table added ');
11284: IF cust_external_exposure_csr%ISOPEN THEN
11285: CLOSE cust_external_exposure_csr;
11286: END IF;
11287: IF site_external_exposure_csr%ISOPEN THEN
11376: BEGIN
11377: BEGIN
11378: IF G_debug_flag = 'Y'
11379: THEN
11380: OE_DEBUG_PUB.ADD('OEXUCRCB: IN CONVERT_CURRENCY_AMOUNT ');
11381: OE_DEBUG_PUB.ADD(' ');
11382: OE_DEBUG_PUB.ADD('------------------------------------');
11383: OE_DEBUG_PUB.ADD('p_amount = '|| p_amount );
11384: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11377: BEGIN
11378: IF G_debug_flag = 'Y'
11379: THEN
11380: OE_DEBUG_PUB.ADD('OEXUCRCB: IN CONVERT_CURRENCY_AMOUNT ');
11381: OE_DEBUG_PUB.ADD(' ');
11382: OE_DEBUG_PUB.ADD('------------------------------------');
11383: OE_DEBUG_PUB.ADD('p_amount = '|| p_amount );
11384: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11385: || p_transactional_currency );
11378: IF G_debug_flag = 'Y'
11379: THEN
11380: OE_DEBUG_PUB.ADD('OEXUCRCB: IN CONVERT_CURRENCY_AMOUNT ');
11381: OE_DEBUG_PUB.ADD(' ');
11382: OE_DEBUG_PUB.ADD('------------------------------------');
11383: OE_DEBUG_PUB.ADD('p_amount = '|| p_amount );
11384: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11385: || p_transactional_currency );
11386: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11379: THEN
11380: OE_DEBUG_PUB.ADD('OEXUCRCB: IN CONVERT_CURRENCY_AMOUNT ');
11381: OE_DEBUG_PUB.ADD(' ');
11382: OE_DEBUG_PUB.ADD('------------------------------------');
11383: OE_DEBUG_PUB.ADD('p_amount = '|| p_amount );
11384: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11385: || p_transactional_currency );
11386: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11387: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11380: OE_DEBUG_PUB.ADD('OEXUCRCB: IN CONVERT_CURRENCY_AMOUNT ');
11381: OE_DEBUG_PUB.ADD(' ');
11382: OE_DEBUG_PUB.ADD('------------------------------------');
11383: OE_DEBUG_PUB.ADD('p_amount = '|| p_amount );
11384: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11385: || p_transactional_currency );
11386: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11387: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11388: OE_DEBUG_PUB.ADD('p_conversion_date = '|| p_conversion_date );
11382: OE_DEBUG_PUB.ADD('------------------------------------');
11383: OE_DEBUG_PUB.ADD('p_amount = '|| p_amount );
11384: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11385: || p_transactional_currency );
11386: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11387: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11388: OE_DEBUG_PUB.ADD('p_conversion_date = '|| p_conversion_date );
11389: OE_DEBUG_PUB.ADD('p_conversion_type = '|| p_conversion_type );
11390: OE_DEBUG_PUB.ADD(' ');
11383: OE_DEBUG_PUB.ADD('p_amount = '|| p_amount );
11384: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11385: || p_transactional_currency );
11386: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11387: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11388: OE_DEBUG_PUB.ADD('p_conversion_date = '|| p_conversion_date );
11389: OE_DEBUG_PUB.ADD('p_conversion_type = '|| p_conversion_type );
11390: OE_DEBUG_PUB.ADD(' ');
11391: OE_DEBUG_PUB.ADD('------------------------------------');
11384: OE_DEBUG_PUB.ADD('p_transactional_currency = '
11385: || p_transactional_currency );
11386: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11387: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11388: OE_DEBUG_PUB.ADD('p_conversion_date = '|| p_conversion_date );
11389: OE_DEBUG_PUB.ADD('p_conversion_type = '|| p_conversion_type );
11390: OE_DEBUG_PUB.ADD(' ');
11391: OE_DEBUG_PUB.ADD('------------------------------------');
11392: END IF;
11385: || p_transactional_currency );
11386: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11387: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11388: OE_DEBUG_PUB.ADD('p_conversion_date = '|| p_conversion_date );
11389: OE_DEBUG_PUB.ADD('p_conversion_type = '|| p_conversion_type );
11390: OE_DEBUG_PUB.ADD(' ');
11391: OE_DEBUG_PUB.ADD('------------------------------------');
11392: END IF;
11393:
11386: OE_DEBUG_PUB.ADD('p_limit_currency = '|| p_limit_currency );
11387: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11388: OE_DEBUG_PUB.ADD('p_conversion_date = '|| p_conversion_date );
11389: OE_DEBUG_PUB.ADD('p_conversion_type = '|| p_conversion_type );
11390: OE_DEBUG_PUB.ADD(' ');
11391: OE_DEBUG_PUB.ADD('------------------------------------');
11392: END IF;
11393:
11394: -- The conversion amount can be les than or greater than
11387: OE_DEBUG_PUB.ADD('p_functional_currency = '|| p_functional_currency );
11388: OE_DEBUG_PUB.ADD('p_conversion_date = '|| p_conversion_date );
11389: OE_DEBUG_PUB.ADD('p_conversion_type = '|| p_conversion_type );
11390: OE_DEBUG_PUB.ADD(' ');
11391: OE_DEBUG_PUB.ADD('------------------------------------');
11392: END IF;
11393:
11394: -- The conversion amount can be les than or greater than
11395: -- 0. For maounts equal to 0, there is not need for
11414: ELSE
11415: l_converted_amount := 0 ;
11416: IF G_debug_flag = 'Y'
11417: THEN
11418: OE_DEBUG_PUB.ADD(' No conversion, amount 0 ');
11419: END IF;
11420: END IF;
11421:
11422: IF G_debug_flag = 'Y'
11420: END IF;
11421:
11422: IF G_debug_flag = 'Y'
11423: THEN
11424: OE_DEBUG_PUB.ADD('Convert amt using trx curr = '|| l_converted_amount );
11425: END IF;
11426:
11427: return l_converted_amount;
11428:
11433: l_functional_amount NUMBER;
11434: BEGIN
11435: IF G_debug_flag = 'Y'
11436: THEN
11437: OE_DEBUG_PUB.ADD(' Convert using functional curr traingulate');
11438: END IF;
11439:
11440: gl_currency_api.convert_closest_amount
11441: ( x_from_currency => p_transactional_currency
11466: );
11467:
11468: IF G_debug_flag = 'Y'
11469: THEN
11470: OE_DEBUG_PUB.ADD('Convert amt using functional curr = '
11471: || l_converted_amount );
11472: END IF;
11473: return l_converted_amount;
11474:
11475: END;
11476: END;
11477: IF G_debug_flag = 'Y'
11478: THEN
11479: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY CONVERT_CURRENCY_AMOUNT ');
11480: END IF;
11481: END CONVERT_CURRENCY_AMOUNT;
11482:
11483: --
11516:
11517: BEGIN
11518: IF G_debug_flag = 'Y'
11519: THEN
11520: oe_debug_pub.ADD('In OE_CREDIT_CHECK_UTIL.Send_Credit_Hold_NTF', 1);
11521: END IF;
11522:
11523: -- Initialize return status to success
11524: x_return_status := FND_API.G_RET_STS_SUCCESS;
11527:
11528: BEGIN
11529: IF G_debug_flag = 'Y'
11530: THEN
11531: oe_debug_pub.ADD('Get the Notification approver ', 2);
11532: END IF;
11533:
11534: --Code added for ER 2925718
11535: l_wfn_to := fnd_profile.Value('OE_CC_NTF_RECIPIENT');
11547: END ;
11548:
11549: IF G_debug_flag = 'Y'
11550: THEN
11551: oe_debug_pub.ADD(' l_wfn_to = '|| l_wfn_to, 2 );
11552: END IF;
11553:
11554: -- Set message attributes and send notification
11555: IF l_wfn_to is not NULL
11556: THEN
11557: BEGIN
11558: IF G_debug_flag = 'Y'
11559: THEN
11560: oe_debug_pub.ADD('Set the order type ', 2);
11561: END IF;
11562:
11563: OPEN c_order_type;
11564: FETCH c_order_type INTO l_order_type;
11581: END IF;
11582:
11583: IF G_debug_flag = 'Y'
11584: THEN
11585: oe_debug_pub.ADD(' Before send notification ', 2);
11586: END IF;
11587:
11588: l_notification_id := wf_notification.send
11589: ( role => l_wfn_to
11592: );
11593:
11594: IF G_debug_flag = 'Y'
11595: THEN
11596: oe_debug_pub.ADD(' After send notification with ID = '||
11597: l_notification_id, 2 );
11598: END IF;
11599:
11600: wf_notification.setattrtext
11633:
11634:
11635: IF G_debug_flag = 'Y'
11636: THEN
11637: oe_debug_pub.ADD('-------Message Attribute Values-------',2);
11638: oe_debug_pub.ADD('l_order_type: '||l_order_type,2);
11639: oe_debug_pub.ADD('l_order_number: '||p_header_rec.order_number,2);
11640: oe_debug_pub.ADD('l_order_hold_comment: '||l_order_hold_comment,2);
11641: oe_debug_pub.ADD('l_line_hold_count: '||l_line_hold_count,2);
11634:
11635: IF G_debug_flag = 'Y'
11636: THEN
11637: oe_debug_pub.ADD('-------Message Attribute Values-------',2);
11638: oe_debug_pub.ADD('l_order_type: '||l_order_type,2);
11639: oe_debug_pub.ADD('l_order_number: '||p_header_rec.order_number,2);
11640: oe_debug_pub.ADD('l_order_hold_comment: '||l_order_hold_comment,2);
11641: oe_debug_pub.ADD('l_line_hold_count: '||l_line_hold_count,2);
11642: oe_debug_pub.ADD('--------------------------------------',2);
11635: IF G_debug_flag = 'Y'
11636: THEN
11637: oe_debug_pub.ADD('-------Message Attribute Values-------',2);
11638: oe_debug_pub.ADD('l_order_type: '||l_order_type,2);
11639: oe_debug_pub.ADD('l_order_number: '||p_header_rec.order_number,2);
11640: oe_debug_pub.ADD('l_order_hold_comment: '||l_order_hold_comment,2);
11641: oe_debug_pub.ADD('l_line_hold_count: '||l_line_hold_count,2);
11642: oe_debug_pub.ADD('--------------------------------------',2);
11643: END IF;
11636: THEN
11637: oe_debug_pub.ADD('-------Message Attribute Values-------',2);
11638: oe_debug_pub.ADD('l_order_type: '||l_order_type,2);
11639: oe_debug_pub.ADD('l_order_number: '||p_header_rec.order_number,2);
11640: oe_debug_pub.ADD('l_order_hold_comment: '||l_order_hold_comment,2);
11641: oe_debug_pub.ADD('l_line_hold_count: '||l_line_hold_count,2);
11642: oe_debug_pub.ADD('--------------------------------------',2);
11643: END IF;
11644:
11637: oe_debug_pub.ADD('-------Message Attribute Values-------',2);
11638: oe_debug_pub.ADD('l_order_type: '||l_order_type,2);
11639: oe_debug_pub.ADD('l_order_number: '||p_header_rec.order_number,2);
11640: oe_debug_pub.ADD('l_order_hold_comment: '||l_order_hold_comment,2);
11641: oe_debug_pub.ADD('l_line_hold_count: '||l_line_hold_count,2);
11642: oe_debug_pub.ADD('--------------------------------------',2);
11643: END IF;
11644:
11645: ELSE
11638: oe_debug_pub.ADD('l_order_type: '||l_order_type,2);
11639: oe_debug_pub.ADD('l_order_number: '||p_header_rec.order_number,2);
11640: oe_debug_pub.ADD('l_order_hold_comment: '||l_order_hold_comment,2);
11641: oe_debug_pub.ADD('l_line_hold_count: '||l_line_hold_count,2);
11642: oe_debug_pub.ADD('--------------------------------------',2);
11643: END IF;
11644:
11645: ELSE
11646: IF G_debug_flag = 'Y'
11644:
11645: ELSE
11646: IF G_debug_flag = 'Y'
11647: THEN
11648: oe_debug_pub.ADD('No send notification l_wfn_to = '|| l_wfn_to, 2);
11649: END IF;
11650: END IF;
11651:
11652: IF G_debug_flag = 'Y'
11650: END IF;
11651:
11652: IF G_debug_flag = 'Y'
11653: THEN
11654: oe_debug_pub.ADD('OEXUCRCB: OUT NOCOPY Send_Credit_Hold_NTF');
11655: END IF;
11656:
11657: -- No exception raised as notification send is not considered
11658: -- as a stop for credit checking
11662: BEGIN
11663: x_return_status := FND_API.G_RET_STS_ERROR ;
11664: IF G_debug_flag = 'Y'
11665: THEN
11666: oe_debug_pub.ADD(' EXCEPTION OTHERS IN SEND_NOTIFICATION');
11667: oe_debug_pub.ADD( ' Errm = '|| SUBSTR(sqlerrm,1,200) );
11668: END IF;
11669: OE_MSG_PUB.Count_And_Get
11670: ( p_count => l_msg_count,
11663: x_return_status := FND_API.G_RET_STS_ERROR ;
11664: IF G_debug_flag = 'Y'
11665: THEN
11666: oe_debug_pub.ADD(' EXCEPTION OTHERS IN SEND_NOTIFICATION');
11667: oe_debug_pub.ADD( ' Errm = '|| SUBSTR(sqlerrm,1,200) );
11668: END IF;
11669: OE_MSG_PUB.Count_And_Get
11670: ( p_count => l_msg_count,
11671: p_data => l_msg_data
11671: p_data => l_msg_data
11672: );
11673: IF G_debug_flag = 'Y'
11674: THEN
11675: oe_debug_pub.ADD(' l_msg_data = ' || l_msg_data );
11676: END IF;
11677: l_msg_data := NULL ;
11678: l_msg_count := NULL ;
11679: END ;
11706: BEGIN
11707:
11708: IF G_debug_flag = 'Y'
11709: THEN
11710: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_external_trx_amount');
11711: END IF;
11712:
11713: x_return_status := FND_API.G_RET_STS_SUCCESS;
11714: OE_CREDIT_CHECK_UTIL.g_current_order_value := NULL ;
11714: OE_CREDIT_CHECK_UTIL.g_current_order_value := NULL ;
11715:
11716: IF G_debug_flag = 'Y'
11717: THEN
11718: OE_DEBUG_PUB.ADD('Order Level Credit Check only');
11719: END IF ;
11720: -- convert amount
11721: BEGIN
11722: IF G_debug_flag = 'Y'
11720: -- convert amount
11721: BEGIN
11722: IF G_debug_flag = 'Y'
11723: THEN
11724: OE_DEBUG_PUB.Add(' GL_CURRENCY = '|| OE_Credit_Engine_GRP.GL_currency);
11725: OE_DEBUG_PUB.ADD(' Total order amount = '|| p_transaction_amount);
11726: END IF;
11727:
11728:
11721: BEGIN
11722: IF G_debug_flag = 'Y'
11723: THEN
11724: OE_DEBUG_PUB.Add(' GL_CURRENCY = '|| OE_Credit_Engine_GRP.GL_currency);
11725: OE_DEBUG_PUB.ADD(' Total order amount = '|| p_transaction_amount);
11726: END IF;
11727:
11728:
11729: l_limit_order_value :=
11745: OE_CREDIT_CHECK_UTIL.g_current_order_value := x_amount ;
11746:
11747: IF G_debug_flag = 'Y'
11748: THEN
11749: OE_DEBUG_PUB.Add(' g_current_order_value = '||
11750: OE_CREDIT_CHECK_UTIL.g_current_order_value);
11751: OE_DEBUG_PUB.Add(' Final trx check order amount = '|| x_amount,1 );
11752: OE_DEBUG_PUB.Add(' ====================================');
11753: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_external_trx_amount ');
11747: IF G_debug_flag = 'Y'
11748: THEN
11749: OE_DEBUG_PUB.Add(' g_current_order_value = '||
11750: OE_CREDIT_CHECK_UTIL.g_current_order_value);
11751: OE_DEBUG_PUB.Add(' Final trx check order amount = '|| x_amount,1 );
11752: OE_DEBUG_PUB.Add(' ====================================');
11753: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_external_trx_amount ');
11754: END IF;
11755:
11748: THEN
11749: OE_DEBUG_PUB.Add(' g_current_order_value = '||
11750: OE_CREDIT_CHECK_UTIL.g_current_order_value);
11751: OE_DEBUG_PUB.Add(' Final trx check order amount = '|| x_amount,1 );
11752: OE_DEBUG_PUB.Add(' ====================================');
11753: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_external_trx_amount ');
11754: END IF;
11755:
11756: EXCEPTION
11749: OE_DEBUG_PUB.Add(' g_current_order_value = '||
11750: OE_CREDIT_CHECK_UTIL.g_current_order_value);
11751: OE_DEBUG_PUB.Add(' Final trx check order amount = '|| x_amount,1 );
11752: OE_DEBUG_PUB.Add(' ====================================');
11753: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY Get_external_trx_amount ');
11754: END IF;
11755:
11756: EXCEPTION
11757: WHEN GL_CURRENCY_API.NO_RATE THEN
11754: END IF;
11755:
11756: EXCEPTION
11757: WHEN GL_CURRENCY_API.NO_RATE THEN
11758: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE ');
11759: x_conversion_status(1).usage_curr_code := p_transaction_curr_code;
11760: fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
11761: OE_DEBUG_PUB.ADD('Exception table added ');
11762: WHEN NO_DATA_FOUND THEN
11757: WHEN GL_CURRENCY_API.NO_RATE THEN
11758: OE_DEBUG_PUB.ADD('EXCEPTION: GL_CURRENCY_API.NO_RATE ');
11759: x_conversion_status(1).usage_curr_code := p_transaction_curr_code;
11760: fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
11761: OE_DEBUG_PUB.ADD('Exception table added ');
11762: WHEN NO_DATA_FOUND THEN
11763: x_amount := 0 ;
11764: OE_DEBUG_PUB.ADD('EXCEPTION: NO_DATA_FOUND ');
11765: WHEN TOO_MANY_ROWS THEN
11760: fnd_message.set_name('ONT', 'OE_CONVERSION_ERROR');
11761: OE_DEBUG_PUB.ADD('Exception table added ');
11762: WHEN NO_DATA_FOUND THEN
11763: x_amount := 0 ;
11764: OE_DEBUG_PUB.ADD('EXCEPTION: NO_DATA_FOUND ');
11765: WHEN TOO_MANY_ROWS THEN
11766: x_amount := 0 ;
11767: OE_DEBUG_PUB.ADD('EXCEPTION: TOO_MANY_ROWS ');
11768: WHEN OTHERS THEN
11763: x_amount := 0 ;
11764: OE_DEBUG_PUB.ADD('EXCEPTION: NO_DATA_FOUND ');
11765: WHEN TOO_MANY_ROWS THEN
11766: x_amount := 0 ;
11767: OE_DEBUG_PUB.ADD('EXCEPTION: TOO_MANY_ROWS ');
11768: WHEN OTHERS THEN
11769: G_DBG_MSG := SUBSTRB(sqlerrm,1,200);
11770: OE_DEBUG_PUB.ADD('EXCEPTION = '|| SUBSTRB(sqlerrm,1,200));
11771: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
11766: x_amount := 0 ;
11767: OE_DEBUG_PUB.ADD('EXCEPTION: TOO_MANY_ROWS ');
11768: WHEN OTHERS THEN
11769: G_DBG_MSG := SUBSTRB(sqlerrm,1,200);
11770: OE_DEBUG_PUB.ADD('EXCEPTION = '|| SUBSTRB(sqlerrm,1,200));
11771: x_return_status := FND_API.G_RET_STS_UNEXP_ERROR;
11772: IF OE_MSG_PUB.Check_Msg_Level(OE_MSG_PUB.G_MSG_LVL_UNEXP_ERROR) THEN
11773: OE_MSG_PUB.Add_Exc_Msg
11774: ( G_PKG_NAME
11796: l_credit_usage_rule_set_id NUMBER;
11797: BEGIN
11798: IF G_debug_flag = 'Y'
11799: THEN
11800: OE_DEBUG_PUB.ADD('OEXUCRCB: IN GET_default_Limit ');
11801: END IF;
11802:
11803: x_default_limit_flag := 'N' ;
11804: x_limit_curr_code := NULL ;
11803: x_default_limit_flag := 'N' ;
11804: x_limit_curr_code := NULL ;
11805: x_global_exposure_flag := 'N' ;
11806:
11807: OE_DEBUG_PUB.ADD(' calling GET_multi_default_Limit ');
11808:
11809: GET_multi_default_Limit
11810: ( p_entity_id => p_entity_id
11811: , p_trx_curr_code => p_trx_curr_code
11819: );
11820:
11821: IF G_debug_flag = 'Y'
11822: THEN
11823: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_default_Limit '
11824: || x_default_limit_flag );
11825: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
11826: END IF;
11827:
11821: IF G_debug_flag = 'Y'
11822: THEN
11823: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_multi_default_Limit '
11824: || x_default_limit_flag );
11825: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
11826: END IF;
11827:
11828: IF x_default_limit_flag = 'Y'
11829: AND x_trx_limit is NULL
11830: AND x_overall_limit is NULL
11831: THEN
11832: x_default_limit_flag := 'N' ;
11833: x_limit_curr_code := NULL ;
11834: OE_DEBUG_PUB.ADD(' Limits NULL ');
11835:
11836: END IF;
11837:
11838: IF x_limit_curr_code IS NULL
11837:
11838: IF x_limit_curr_code IS NULL
11839: THEN
11840: x_default_limit_flag := 'N' ;
11841: OE_DEBUG_PUB.ADD(' calling GET_single_default_Limit ');
11842: GET_single_default_Limit
11843: ( p_entity_id => p_entity_id
11844: , p_trx_curr_code => p_trx_curr_code
11845: , x_limit_curr_code => x_limit_curr_code
11849: );
11850:
11851: IF G_debug_flag = 'Y'
11852: THEN
11853: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_single_default_Limit '
11854: || x_default_limit_flag );
11855: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
11856: END IF;
11857:
11851: IF G_debug_flag = 'Y'
11852: THEN
11853: OE_DEBUG_PUB.ADD(' Out NOCOPY GET_single_default_Limit '
11854: || x_default_limit_flag );
11855: OE_DEBUG_PUB.ADD(' x_limit_curr_code = '|| x_limit_curr_code );
11856: END IF;
11857:
11858: END IF;
11859:
11859:
11860:
11861: IF G_debug_flag = 'Y'
11862: THEN
11863: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY GET_default_Limit ' ||
11864: x_default_limit_flag );
11865: END IF;
11866:
11867: EXCEPTION
11936:
11937: IF G_debug_flag = 'Y'
11938: THEN
11939:
11940: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_limit_info', 1);
11941: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11942: OE_DEBUG_PUB.ADD('G_ORG_ID ==> '|| G_ORG_ID );
11943: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11944: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11937: IF G_debug_flag = 'Y'
11938: THEN
11939:
11940: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_limit_info', 1);
11941: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11942: OE_DEBUG_PUB.ADD('G_ORG_ID ==> '|| G_ORG_ID );
11943: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11944: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11945: OE_DEBUG_PUB.ADD('P_party_id ==> '|| P_party_id );
11938: THEN
11939:
11940: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_limit_info', 1);
11941: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11942: OE_DEBUG_PUB.ADD('G_ORG_ID ==> '|| G_ORG_ID );
11943: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11944: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11945: OE_DEBUG_PUB.ADD('P_party_id ==> '|| P_party_id );
11946: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
11939:
11940: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_limit_info', 1);
11941: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11942: OE_DEBUG_PUB.ADD('G_ORG_ID ==> '|| G_ORG_ID );
11943: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11944: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11945: OE_DEBUG_PUB.ADD('P_party_id ==> '|| P_party_id );
11946: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
11947: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '||
11940: OE_DEBUG_PUB.ADD('OEXUCRCB: IN get_limit_info', 1);
11941: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11942: OE_DEBUG_PUB.ADD('G_ORG_ID ==> '|| G_ORG_ID );
11943: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11944: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11945: OE_DEBUG_PUB.ADD('P_party_id ==> '|| P_party_id );
11946: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
11947: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '||
11948: p_navigate_to_next_level );
11941: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11942: OE_DEBUG_PUB.ADD('G_ORG_ID ==> '|| G_ORG_ID );
11943: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11944: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11945: OE_DEBUG_PUB.ADD('P_party_id ==> '|| P_party_id );
11946: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
11947: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '||
11948: p_navigate_to_next_level );
11949:
11942: OE_DEBUG_PUB.ADD('G_ORG_ID ==> '|| G_ORG_ID );
11943: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11944: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11945: OE_DEBUG_PUB.ADD('P_party_id ==> '|| P_party_id );
11946: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
11947: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '||
11948: p_navigate_to_next_level );
11949:
11950: OE_DEBUG_PUB.ADD('p_suppress_unused_usages_flag ==> '||
11943: OE_DEBUG_PUB.ADD('P_entity_Type ==> '|| P_entity_Type );
11944: OE_DEBUG_PUB.ADD('P_entity_id ==> '|| P_entity_id );
11945: OE_DEBUG_PUB.ADD('P_party_id ==> '|| P_party_id );
11946: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
11947: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '||
11948: p_navigate_to_next_level );
11949:
11950: OE_DEBUG_PUB.ADD('p_suppress_unused_usages_flag ==> '||
11951: p_suppress_unused_usages_flag );
11946: OE_DEBUG_PUB.ADD('p_trx_curr_code ==> '|| p_trx_curr_code );
11947: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '||
11948: p_navigate_to_next_level );
11949:
11950: OE_DEBUG_PUB.ADD('p_suppress_unused_usages_flag ==> '||
11951: p_suppress_unused_usages_flag );
11952:
11953: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11954:
11949:
11950: OE_DEBUG_PUB.ADD('p_suppress_unused_usages_flag ==> '||
11951: p_suppress_unused_usages_flag );
11952:
11953: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11954:
11955: OE_DEBUG_PUB.ADD(' Call Get_Multi_Limit ');
11956: END IF;
11957:
11951: p_suppress_unused_usages_flag );
11952:
11953: OE_DEBUG_PUB.ADD(' ---------------------------------------------' );
11954:
11955: OE_DEBUG_PUB.ADD(' Call Get_Multi_Limit ');
11956: END IF;
11957:
11958: IF p_entity_type in ('SITE','CUSTOMER')
11959: THEN
11971: );
11972:
11973: IF G_debug_flag = 'Y'
11974: THEN
11975: OE_DEBUG_PUB.ADD(' Out NOCOPY of Get_Multi_Limit ');
11976: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
11977: END IF;
11978:
11979: IF x_limit_curr_code IS NOT NULL
11972:
11973: IF G_debug_flag = 'Y'
11974: THEN
11975: OE_DEBUG_PUB.ADD(' Out NOCOPY of Get_Multi_Limit ');
11976: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
11977: END IF;
11978:
11979: IF x_limit_curr_code IS NOT NULL
11980: THEN
11987: AND NVL(l_site_cc_flag, 'Y') = 'Y' --bug 4212981 , bug 4582292
11988: THEN
11989: IF G_debug_flag = 'Y'
11990: THEN
11991: OE_DEBUG_PUB.ADD('calling Get_Multi_Limit for CUSTOMER ');
11992: END IF;
11993: Get_Multi_Limit
11994: ( p_entity_type => 'CUSTOMER'
11995: , p_entity_id => p_cust_account_id
12010: END IF;
12011: END IF;
12012: END IF;
12013:
12014: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '|| p_navigate_to_next_level );
12015: OE_DEBUG_PUB.ADD('l_site_cc_flag ==> '|| l_site_cc_flag );
12016: OE_DEBUG_PUB.ADD('l_cust_cc_flag ==> '|| l_cust_cc_flag );
12017: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
12018: OE_DEBUG_PUB.ADD('l_return_status ==> '|| l_return_status );
12011: END IF;
12012: END IF;
12013:
12014: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '|| p_navigate_to_next_level );
12015: OE_DEBUG_PUB.ADD('l_site_cc_flag ==> '|| l_site_cc_flag );
12016: OE_DEBUG_PUB.ADD('l_cust_cc_flag ==> '|| l_cust_cc_flag );
12017: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
12018: OE_DEBUG_PUB.ADD('l_return_status ==> '|| l_return_status );
12019:
12012: END IF;
12013:
12014: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '|| p_navigate_to_next_level );
12015: OE_DEBUG_PUB.ADD('l_site_cc_flag ==> '|| l_site_cc_flag );
12016: OE_DEBUG_PUB.ADD('l_cust_cc_flag ==> '|| l_cust_cc_flag );
12017: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
12018: OE_DEBUG_PUB.ADD('l_return_status ==> '|| l_return_status );
12019:
12020: --IF p_navigate_to_next_level = 'Y' THEN --bug 5071518 --14505181
12013:
12014: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '|| p_navigate_to_next_level );
12015: OE_DEBUG_PUB.ADD('l_site_cc_flag ==> '|| l_site_cc_flag );
12016: OE_DEBUG_PUB.ADD('l_cust_cc_flag ==> '|| l_cust_cc_flag );
12017: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
12018: OE_DEBUG_PUB.ADD('l_return_status ==> '|| l_return_status );
12019:
12020: --IF p_navigate_to_next_level = 'Y' THEN --bug 5071518 --14505181
12021: IF p_navigate_to_next_level = 'Y' AND --14505181
12014: OE_DEBUG_PUB.ADD('p_navigate_to_next_level ==> '|| p_navigate_to_next_level );
12015: OE_DEBUG_PUB.ADD('l_site_cc_flag ==> '|| l_site_cc_flag );
12016: OE_DEBUG_PUB.ADD('l_cust_cc_flag ==> '|| l_cust_cc_flag );
12017: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
12018: OE_DEBUG_PUB.ADD('l_return_status ==> '|| l_return_status );
12019:
12020: --IF p_navigate_to_next_level = 'Y' THEN --bug 5071518 --14505181
12021: IF p_navigate_to_next_level = 'Y' AND --14505181
12022: -- IF --14505181
12039: IF x_limit_curr_code IS NULL
12040: THEN
12041: IF G_debug_flag = 'Y'
12042: THEN
12043: OE_DEBUG_PUB.ADD( 'OEXUCRCB: GET_party_limit ');
12044: END IF;
12045:
12046: GET_party_limit
12047: ( p_party_id => p_party_id
12054: );
12055:
12056: IF G_debug_flag = 'Y'
12057: THEN
12058: OE_DEBUG_PUB.ADD( ' out NOCOPY GET_party_limit ');
12059: OE_DEBUG_PUB.ADD( ' l_return_status ==> '|| l_return_status );
12060: END IF;
12061:
12062: IF x_limit_curr_code IS NOT NULL
12055:
12056: IF G_debug_flag = 'Y'
12057: THEN
12058: OE_DEBUG_PUB.ADD( ' out NOCOPY GET_party_limit ');
12059: OE_DEBUG_PUB.ADD( ' l_return_status ==> '|| l_return_status );
12060: END IF;
12061:
12062: IF x_limit_curr_code IS NOT NULL
12063: THEN
12072:
12073: IF x_limit_curr_code IS NULL
12074: AND NVL(l_return_status,'Y') = 'Y' -- bug 4958142
12075: THEN
12076: OE_DEBUG_PUB.ADD( ' calling GET_default_Limit ');
12077: GET_default_Limit
12078: ( p_entity_id => p_entity_id
12079: , p_trx_curr_code => p_trx_curr_code
12080: , x_limit_curr_code => x_limit_curr_code
12091: END IF;
12092: END IF;
12093:
12094: -- Start bug 5071518
12095: OE_DEBUG_PUB.ADD(' x_limit_curr_code => '|| x_limit_curr_code );
12096: OE_DEBUG_PUB.ADD(' x_trx_limit => '|| x_trx_limit );
12097: OE_DEBUG_PUB.ADD(' x_overall_limit => '|| x_overall_limit );
12098: OE_DEBUG_PUB.ADD(' x_default_limit_flag => '|| x_default_limit_flag );
12099:
12092: END IF;
12093:
12094: -- Start bug 5071518
12095: OE_DEBUG_PUB.ADD(' x_limit_curr_code => '|| x_limit_curr_code );
12096: OE_DEBUG_PUB.ADD(' x_trx_limit => '|| x_trx_limit );
12097: OE_DEBUG_PUB.ADD(' x_overall_limit => '|| x_overall_limit );
12098: OE_DEBUG_PUB.ADD(' x_default_limit_flag => '|| x_default_limit_flag );
12099:
12100: END IF; -- navigate
12093:
12094: -- Start bug 5071518
12095: OE_DEBUG_PUB.ADD(' x_limit_curr_code => '|| x_limit_curr_code );
12096: OE_DEBUG_PUB.ADD(' x_trx_limit => '|| x_trx_limit );
12097: OE_DEBUG_PUB.ADD(' x_overall_limit => '|| x_overall_limit );
12098: OE_DEBUG_PUB.ADD(' x_default_limit_flag => '|| x_default_limit_flag );
12099:
12100: END IF; -- navigate
12101:
12094: -- Start bug 5071518
12095: OE_DEBUG_PUB.ADD(' x_limit_curr_code => '|| x_limit_curr_code );
12096: OE_DEBUG_PUB.ADD(' x_trx_limit => '|| x_trx_limit );
12097: OE_DEBUG_PUB.ADD(' x_overall_limit => '|| x_overall_limit );
12098: OE_DEBUG_PUB.ADD(' x_default_limit_flag => '|| x_default_limit_flag );
12099:
12100: END IF; -- navigate
12101:
12102: IF x_limit_curr_code IS NULL
12100: END IF; -- navigate
12101:
12102: IF x_limit_curr_code IS NULL
12103: THEN
12104: OE_DEBUG_PUB.ADD(' No limits for any level ');
12105:
12106: x_trx_limit := NULL ;
12107: x_overall_limit := NULL ;
12108: x_include_all_flag := 'N' ;
12113:
12114: ELSE
12115: IF G_debug_flag = 'Y'
12116: THEN
12117: OE_DEBUG_PUB.ADD( ' Calling Get_Usages' );
12118: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
12119: OE_DEBUG_PUB.ADD('x_default_limit_flag ==> '|| x_default_limit_flag );
12120: OE_DEBUG_PUB.ADD('x_global_exposure_flag ==> '|| x_global_exposure_flag );
12121: OE_DEBUG_PUB.ADD('x_include_all_flag ==> '|| x_include_all_flag );
12114: ELSE
12115: IF G_debug_flag = 'Y'
12116: THEN
12117: OE_DEBUG_PUB.ADD( ' Calling Get_Usages' );
12118: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
12119: OE_DEBUG_PUB.ADD('x_default_limit_flag ==> '|| x_default_limit_flag );
12120: OE_DEBUG_PUB.ADD('x_global_exposure_flag ==> '|| x_global_exposure_flag );
12121: OE_DEBUG_PUB.ADD('x_include_all_flag ==> '|| x_include_all_flag );
12122: OE_DEBUG_PUB.ADD('x_credit_limit_entity_id ==> '||
12115: IF G_debug_flag = 'Y'
12116: THEN
12117: OE_DEBUG_PUB.ADD( ' Calling Get_Usages' );
12118: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
12119: OE_DEBUG_PUB.ADD('x_default_limit_flag ==> '|| x_default_limit_flag );
12120: OE_DEBUG_PUB.ADD('x_global_exposure_flag ==> '|| x_global_exposure_flag );
12121: OE_DEBUG_PUB.ADD('x_include_all_flag ==> '|| x_include_all_flag );
12122: OE_DEBUG_PUB.ADD('x_credit_limit_entity_id ==> '||
12123: x_credit_limit_entity_id );
12116: THEN
12117: OE_DEBUG_PUB.ADD( ' Calling Get_Usages' );
12118: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
12119: OE_DEBUG_PUB.ADD('x_default_limit_flag ==> '|| x_default_limit_flag );
12120: OE_DEBUG_PUB.ADD('x_global_exposure_flag ==> '|| x_global_exposure_flag );
12121: OE_DEBUG_PUB.ADD('x_include_all_flag ==> '|| x_include_all_flag );
12122: OE_DEBUG_PUB.ADD('x_credit_limit_entity_id ==> '||
12123: x_credit_limit_entity_id );
12124: OE_DEBUG_PUB.ADD('x_credit_check_level ==> '||
12117: OE_DEBUG_PUB.ADD( ' Calling Get_Usages' );
12118: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
12119: OE_DEBUG_PUB.ADD('x_default_limit_flag ==> '|| x_default_limit_flag );
12120: OE_DEBUG_PUB.ADD('x_global_exposure_flag ==> '|| x_global_exposure_flag );
12121: OE_DEBUG_PUB.ADD('x_include_all_flag ==> '|| x_include_all_flag );
12122: OE_DEBUG_PUB.ADD('x_credit_limit_entity_id ==> '||
12123: x_credit_limit_entity_id );
12124: OE_DEBUG_PUB.ADD('x_credit_check_level ==> '||
12125: x_credit_check_level );
12118: OE_DEBUG_PUB.ADD('x_limit_curr_code ==> '|| x_limit_curr_code );
12119: OE_DEBUG_PUB.ADD('x_default_limit_flag ==> '|| x_default_limit_flag );
12120: OE_DEBUG_PUB.ADD('x_global_exposure_flag ==> '|| x_global_exposure_flag );
12121: OE_DEBUG_PUB.ADD('x_include_all_flag ==> '|| x_include_all_flag );
12122: OE_DEBUG_PUB.ADD('x_credit_limit_entity_id ==> '||
12123: x_credit_limit_entity_id );
12124: OE_DEBUG_PUB.ADD('x_credit_check_level ==> '||
12125: x_credit_check_level );
12126: END IF;
12120: OE_DEBUG_PUB.ADD('x_global_exposure_flag ==> '|| x_global_exposure_flag );
12121: OE_DEBUG_PUB.ADD('x_include_all_flag ==> '|| x_include_all_flag );
12122: OE_DEBUG_PUB.ADD('x_credit_limit_entity_id ==> '||
12123: x_credit_limit_entity_id );
12124: OE_DEBUG_PUB.ADD('x_credit_check_level ==> '||
12125: x_credit_check_level );
12126: END IF;
12127:
12128: IF x_credit_check_level = 'PARTY'
12142: );
12143:
12144: IF G_debug_flag = 'Y'
12145: THEN
12146: OE_DEBUG_PUB.ADD( ' Out NOCOPY of Get_Usages ');
12147: END IF;
12148: END IF;
12149:
12150: IF G_debug_flag = 'Y'
12148: END IF;
12149:
12150: IF G_debug_flag = 'Y'
12151: THEN
12152: OE_DEBUG_PUB.ADD('OEXUCRCB: OUT NOCOPY get_limit_info ',1);
12153: END IF;
12154:
12155: EXCEPTION
12156: WHEN OTHERS THEN