[Home] [Help]
627:
628: PROCEDURE set_single_org(x_org_id number) IS
629: current_org_id number;
630: BEGIN
631: cep_standard.debug('>>CE_AUTO_BANK_MATCH.set_single_org x_org_id =' || x_org_id);
632:
633: select mo_global.GET_CURRENT_ORG_ID
634: into current_org_id
635: from dual;
633: select mo_global.GET_CURRENT_ORG_ID
634: into current_org_id
635: from dual;
636:
637: cep_standard.debug('current_org_id =' ||current_org_id );
638:
639: -- bug 3782741 set single org, since AR will not allow org_id to be passed
640: --IF CE_AUTO_BANK_MATCH.bau_org_id is not null THEN
641: --IF CE_AUTO_BANK_REC.G_org_id is not null THEN (this is set at ceabrdrb ce_auto_bank_rec)
641: --IF CE_AUTO_BANK_REC.G_org_id is not null THEN (this is set at ceabrdrb ce_auto_bank_rec)
642: IF (x_org_id is not null) THEN
643: IF ((current_org_id is null) or (x_org_id <> current_org_id )) THEN
644: mo_global.set_policy_context('S',x_org_id);
645: cep_standard.debug('set current_org_id to ' ||x_org_id );
646: END IF;
647: END IF;
648:
649: cep_standard.debug('<
645: cep_standard.debug('set current_org_id to ' ||x_org_id );
646: END IF;
647: END IF;
648:
649: cep_standard.debug('<
651: WHEN OTHERS THEN
652: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.set_single_org' );
653: RAISE;
648:
649: cep_standard.debug('<
651: WHEN OTHERS THEN
652: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.set_single_org' );
653: RAISE;
654: END set_single_org;
655: /* ---------------------------------------------------------------------
656: | PRIVATE PROCEDURE |
664:
665: PROCEDURE set_multi_org(x_org_id number) IS
666:
667: BEGIN
668: cep_standard.debug('>>CE_AUTO_BANK_MATCH.set_multi_org x_org_id =' || x_org_id);
669:
670: MO_GLOBAL.init('CE');
671:
672: cep_standard.debug('<
668: cep_standard.debug('>>CE_AUTO_BANK_MATCH.set_multi_org x_org_id =' || x_org_id);
669:
670: MO_GLOBAL.init('CE');
671:
672: cep_standard.debug('<
674: WHEN OTHERS THEN
675: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.set_multi_org' );
676: RAISE;
671:
672: cep_standard.debug('<
674: WHEN OTHERS THEN
675: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.set_multi_org' );
676: RAISE;
677: END set_multi_org;
678:
679: /* ----------------------------------------------------------------------
688: PROCEDURE get_receivables_org_id (X_ORG_ID OUT NOCOPY NUMBER ) IS
689: x_receivables_trx_id number;
690:
691: BEGIN
692: cep_standard.debug( '>>CE_AUTO_BANK_MATCH.get_receivables_org_id' );
693: cep_standard.debug( 'CE_AUTO_BANK_MATCH.csl_receivables_trx_id= '|| CE_AUTO_BANK_MATCH.csl_receivables_trx_id||
694: ',CE_AUTO_BANK_MATCH.trx_org_id='||CE_AUTO_BANK_MATCH.trx_org_id );
695:
696: x_receivables_trx_id := nvl(CE_AUTO_BANK_MATCH.csl_receivables_trx_id,
689: x_receivables_trx_id number;
690:
691: BEGIN
692: cep_standard.debug( '>>CE_AUTO_BANK_MATCH.get_receivables_org_id' );
693: cep_standard.debug( 'CE_AUTO_BANK_MATCH.csl_receivables_trx_id= '|| CE_AUTO_BANK_MATCH.csl_receivables_trx_id||
694: ',CE_AUTO_BANK_MATCH.trx_org_id='||CE_AUTO_BANK_MATCH.trx_org_id );
695:
696: x_receivables_trx_id := nvl(CE_AUTO_BANK_MATCH.csl_receivables_trx_id,
697: CE_AUTO_BANK_REC.G_receivables_trx_id);
695:
696: x_receivables_trx_id := nvl(CE_AUTO_BANK_MATCH.csl_receivables_trx_id,
697: CE_AUTO_BANK_REC.G_receivables_trx_id);
698:
699: cep_standard.debug( 'x_receivables_trx_id= '|| x_receivables_trx_id);
700:
701: -- bug 5722367 removed the reference to ar_receivables_trx_all table to ar_receivables_trx
702: if (x_receivables_trx_id is not null) THEN
703:
708: from AR_RECEIVABLES_TRX
709: where RECEIVABLES_TRX_ID = x_receivables_trx_id;
710: exception
711: when no_data_found then
712: cep_standard.debug( 'NO_DATA_FOUND in AR_RECEIVABLES_TRX, picking up org_id from AR_RECEIVABLES_TRX_ALL..');
713: select org_id
714: into X_ORG_ID
715: from AR_RECEIVABLES_TRX_ALL
716: where RECEIVABLES_TRX_ID = x_receivables_trx_id;
717: end;
718: -- Bug 16568960 End.
719:
720:
721: cep_standard.debug( 'x_ORG_ID= '|| X_ORG_ID);
722:
723: ELSE
724: cep_standard.debug('receivables_trx_id is missing');
725: CE_RECONCILIATION_ERRORS_PKG.insert_row(
720:
721: cep_standard.debug( 'x_ORG_ID= '|| X_ORG_ID);
722:
723: ELSE
724: cep_standard.debug('receivables_trx_id is missing');
725: CE_RECONCILIATION_ERRORS_PKG.insert_row(
726: CE_AUTO_BANK_MATCH.csh_statement_header_id,
727: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_MISSING_REC_ACT_ID');
728:
727: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_MISSING_REC_ACT_ID');
728:
729: END IF;
730:
731: cep_standard.debug( '<
733: WHEN NO_DATA_FOUND THEN
734: cep_standard.debug('receivables_trx_id does not exists');
735: CE_RECONCILIATION_ERRORS_PKG.insert_row(
730:
731: cep_standard.debug( '<
733: WHEN NO_DATA_FOUND THEN
734: cep_standard.debug('receivables_trx_id does not exists');
735: CE_RECONCILIATION_ERRORS_PKG.insert_row(
736: CE_AUTO_BANK_MATCH.csh_statement_header_id,
737: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_NO_REC_ACT_ID');
738: WHEN OTHERS THEN
735: CE_RECONCILIATION_ERRORS_PKG.insert_row(
736: CE_AUTO_BANK_MATCH.csh_statement_header_id,
737: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_NO_REC_ACT_ID');
738: WHEN OTHERS THEN
739: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.get_receivables_org_id' );
740: RAISE;
741: END get_receivables_org_id;
742:
743: /* --------------------------------------------------------------------
757: tx_match_amount NUMBER,
758: precision NUMBER,
759: no_of_matches OUT NOCOPY NUMBER) IS
760: BEGIN
761: cep_standard.debug( '>>CE_AUTO_BANK_MATCH.match_oi_trx' );
762:
763: -- match xtr transaction first then non-xtr OI trx
764: -- bug 4914608 some bank acct used by xtr might not have xtr_use_enable_flag = Y
765: IF ((CE_AUTO_BANK_REC.G_legal_entity_id is not null) or
764: -- bug 4914608 some bank acct used by xtr might not have xtr_use_enable_flag = Y
765: IF ((CE_AUTO_BANK_REC.G_legal_entity_id is not null) or
766: --IF ((CE_AUTO_BANK_MATCH.bau_legal_entity_id is not null) AND
767: (CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag = 'Y')) THEN
768: cep_standard.debug( ' use ce_185_transactions_v CE_AUTO_BANK_MATCH.csl_trx_date='|| CE_AUTO_BANK_MATCH.csl_trx_date);
769: BEGIN -- BUG 11719138
770: SELECT catv.trx_id,
771: catv.cash_receipt_id,
772: catv.row_id,
830: END IF;
831:
832: -- no xtr match from above query
833: IF ( CE_AUTO_BANK_MATCH.trx_id is null) THEN
834: cep_standard.debug( ' use ce_999_transactions_v ' );
835:
836: SELECT catv.trx_id,
837: catv.cash_receipt_id,
838: catv.row_id,
887: AND round(catv.amount, precision) = tx_match_amount
888: AND catv.application_id = 999
889: AND NVL(catv.reconciled_status_flag, 'N') = 'N';
890: END IF;
891: cep_standard.debug( '<
893:
894: /* --------------------------------------------------------------------
895: | PRIVATE FUNCTION |
910: fixed_relation BOOLEAN;
911: curr_relation VARCHAR2(30);
912: BEGIN
913:
914: cep_standard.debug('>>CE_AUTO_BANK_MATCH.validate_exchange_details');
915: error_found := FALSE;
916:
917: --
918: -- TRX Currency needs to be the same that the SL currency
929: END IF;
930:
931: IF (CE_AUTO_BANK_MATCH.trx_currency_code <> NVL(CE_AUTO_BANK_MATCH.csl_currency_code,CE_AUTO_BANK_MATCH.trx_currency_code))
932: THEN
933: cep_standard.debug('Inconsistent currencies');
934: CE_RECONCILIATION_ERRORS_PKG.insert_row(
935: CE_AUTO_BANK_MATCH.csh_statement_header_id,
936: CE_AUTO_BANK_MATCH.csl_statement_line_id,
937: 'CE_DIFFERENT_CURRENCY');
940:
941: --
942: -- When Fixed relationship is found, exchange info is not mandatory.
943: --
944: cep_standard.debug('CE_AUTO_BANK_REC.G_functional_currency='||CE_AUTO_BANK_REC.G_functional_currency ||
945: ', CE_AUTO_BANK_MATCH.csl_currency_code=' ||CE_AUTO_BANK_MATCH.csl_currency_code);
946:
947: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_date='||CE_AUTO_BANK_MATCH.csl_exchange_rate_date ||
948: ', CE_AUTO_BANK_MATCH.csl_trx_date='||CE_AUTO_BANK_MATCH.csl_trx_date);
943: --
944: cep_standard.debug('CE_AUTO_BANK_REC.G_functional_currency='||CE_AUTO_BANK_REC.G_functional_currency ||
945: ', CE_AUTO_BANK_MATCH.csl_currency_code=' ||CE_AUTO_BANK_MATCH.csl_currency_code);
946:
947: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_date='||CE_AUTO_BANK_MATCH.csl_exchange_rate_date ||
948: ', CE_AUTO_BANK_MATCH.csl_trx_date='||CE_AUTO_BANK_MATCH.csl_trx_date);
949:
950: BEGIN
951: gl_currency_api.get_relation(
956: fixed_relation,
957: curr_relation);
958: EXCEPTION
959: WHEN OTHERS THEN
960: cep_standard.debug('Cannot find relationship for the give curr');
961: fixed_relation := FALSE;
962: curr_relation := 'OTHER';
963: END;
964:
965: -- 16169940: Removed fixed_relation_temp which was only used to print
966: -- debug messages.
967: IF (fixed_relation)
968: THEN
969: cep_standard.debug('fixed relation found, curr_relation='||curr_relation);
970: CE_AUTO_BANK_MATCH.csl_exchange_rate_type := 'EMU FIXED';
971: CE_AUTO_BANK_MATCH.csl_exchange_rate_date := nvl(CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
972: CE_AUTO_BANK_MATCH.csl_trx_date);
973: ELSE -- non-emu
970: CE_AUTO_BANK_MATCH.csl_exchange_rate_type := 'EMU FIXED';
971: CE_AUTO_BANK_MATCH.csl_exchange_rate_date := nvl(CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
972: CE_AUTO_BANK_MATCH.csl_trx_date);
973: ELSE -- non-emu
974: cep_standard.debug('fixed relation not found, curr_relation='||curr_relation);
975:
976: -- If we have a foreign currency trx and line does not have ANY xrate info
977: -- we calculate the exchange rate and provide that as 'User' rate
978: -- (International)
992: CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL AND
993: CE_AUTO_BANK_MATCH.csl_exchange_rate_date IS NULL AND
994: CE_AUTO_BANK_MATCH.csl_original_amount IS NULL)
995: THEN
996: cep_standard.debug('++CE_AUTO_BANK_REC.G_exchange_rate_type = ' || CE_AUTO_BANK_REC.G_exchange_rate_type);
997: cep_standard.debug('++CE_AUTO_BANK_REC.G_exchange_rate_date = ' || CE_AUTO_BANK_REC.G_exchange_rate_date);
998: cep_standard.debug('++CE_AUTO_BANK_MATCH.csl_clearing_trx_type = '|| CE_AUTO_BANK_MATCH.csl_clearing_trx_type);
999:
1000: IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW')
993: CE_AUTO_BANK_MATCH.csl_exchange_rate_date IS NULL AND
994: CE_AUTO_BANK_MATCH.csl_original_amount IS NULL)
995: THEN
996: cep_standard.debug('++CE_AUTO_BANK_REC.G_exchange_rate_type = ' || CE_AUTO_BANK_REC.G_exchange_rate_type);
997: cep_standard.debug('++CE_AUTO_BANK_REC.G_exchange_rate_date = ' || CE_AUTO_BANK_REC.G_exchange_rate_date);
998: cep_standard.debug('++CE_AUTO_BANK_MATCH.csl_clearing_trx_type = '|| CE_AUTO_BANK_MATCH.csl_clearing_trx_type);
999:
1000: IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW')
1001: THEN
994: CE_AUTO_BANK_MATCH.csl_original_amount IS NULL)
995: THEN
996: cep_standard.debug('++CE_AUTO_BANK_REC.G_exchange_rate_type = ' || CE_AUTO_BANK_REC.G_exchange_rate_type);
997: cep_standard.debug('++CE_AUTO_BANK_REC.G_exchange_rate_date = ' || CE_AUTO_BANK_REC.G_exchange_rate_date);
998: cep_standard.debug('++CE_AUTO_BANK_MATCH.csl_clearing_trx_type = '|| CE_AUTO_BANK_MATCH.csl_clearing_trx_type);
999:
1000: IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW')
1001: THEN
1002: CE_AUTO_BANK_MATCH.csl_exchange_rate_type := CE_AUTO_BANK_REC.G_cashflow_exchange_rate_type;
1005: END IF;
1006:
1007: IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW')
1008: THEN
1009: cep_standard.debug('++CE_AUTO_BANK_MATCH.trx_reference_type = ' || CE_AUTO_BANK_MATCH.trx_reference_type);
1010:
1011: IF (CE_AUTO_BANK_MATCH.trx_reference_type = 'STMT') -- JEC
1012: THEN
1013: cep_standard.debug('[JEC]++CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE = ' || CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE);
1009: cep_standard.debug('++CE_AUTO_BANK_MATCH.trx_reference_type = ' || CE_AUTO_BANK_MATCH.trx_reference_type);
1010:
1011: IF (CE_AUTO_BANK_MATCH.trx_reference_type = 'STMT') -- JEC
1012: THEN
1013: cep_standard.debug('[JEC]++CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE = ' || CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE);
1014: IF (CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE = 'CFD')
1015: THEN
1016: CE_AUTO_BANK_MATCH.csl_exchange_rate_date := CE_AUTO_BANK_MATCH.trx_date;
1017: ELSIF (CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE = 'CLD')
1031: CE_AUTO_BANK_MATCH.csl_exchange_rate_date := CE_AUTO_BANK_MATCH.csh_statement_gl_date;
1032: END IF;
1033:
1034: ELSE -- BAT
1035: cep_standard.debug('[BAT]++CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = ' || CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE);
1036: IF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'CFD')
1037: THEN
1038: CE_AUTO_BANK_MATCH.csl_exchange_rate_date := CE_AUTO_BANK_MATCH.trx_date;
1039: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'AVD')
1055: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'TRX')
1056: THEN
1057: CE_AUTO_BANK_MATCH.csl_exchange_rate_date := CE_AUTO_BANK_MATCH.trx_deposit_date;
1058: END IF;
1059: cep_standard.debug('[BAT]++CE_AUTO_BANK_MATCH.csl_exchange_rate_date = ' || CE_AUTO_BANK_MATCH.csl_exchange_rate_date);
1060: END IF;
1061: ELSE -- not cashflow
1062: IF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'SLD')
1063: THEN
1081: END IF;
1082:
1083: CE_AUTO_BANK_MATCH.foreign_exchange_defaulted := 'Y';
1084: ELSE
1085: cep_standard.debug('MATCH.CSL_xtype: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1086: cep_standard.debug('MATCH.CSL_xdate: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_date);
1087:
1088: --
1089: -- line must have either xrate, original_amount or (xdate+xtype)
1082:
1083: CE_AUTO_BANK_MATCH.foreign_exchange_defaulted := 'Y';
1084: ELSE
1085: cep_standard.debug('MATCH.CSL_xtype: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1086: cep_standard.debug('MATCH.CSL_xdate: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_date);
1087:
1088: --
1089: -- line must have either xrate, original_amount or (xdate+xtype)
1090: --
1104: FROM CE_BANK_ACCOUNTS
1105: WHERE BANK_ACCOUNT_ID = CE_AUTO_BANK_MATCH.CSH_BANK_ACCOUNT_ID ;
1106: -- Bug 12564116 End.
1107:
1108: cep_standard.debug('xtype: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1109: cep_standard.debug('xdate: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_date);
1110: ELSE
1111: IF (CE_AUTO_BANK_MATCH.csl_exchange_rate_type IS NULL)
1112: THEN
1105: WHERE BANK_ACCOUNT_ID = CE_AUTO_BANK_MATCH.CSH_BANK_ACCOUNT_ID ;
1106: -- Bug 12564116 End.
1107:
1108: cep_standard.debug('xtype: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1109: cep_standard.debug('xdate: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_date);
1110: ELSE
1111: IF (CE_AUTO_BANK_MATCH.csl_exchange_rate_type IS NULL)
1112: THEN
1113: CE_RECONCILIATION_ERRORS_PKG.insert_row(
1129:
1130: --
1131: -- Rate Validation for emu and non-emu
1132: --
1133: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_type ='||CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1134:
1135: IF (CE_AUTO_BANK_MATCH.csl_exchange_rate_type = 'User')
1136: THEN
1137:
1150: END IF;
1151:
1152: ELSE -- not 'User'
1153: BEGIN
1154: cep_standard.debug('CE_AUTO_BANK_REC.G_set_of_books_id = '||CE_AUTO_BANK_REC.G_set_of_books_id);
1155: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_currency_code = '|| CE_AUTO_BANK_MATCH.trx_currency_code );
1156: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_date = '|| CE_AUTO_BANK_MATCH.csl_exchange_rate_date);
1157: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_type = '|| CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1158:
1151:
1152: ELSE -- not 'User'
1153: BEGIN
1154: cep_standard.debug('CE_AUTO_BANK_REC.G_set_of_books_id = '||CE_AUTO_BANK_REC.G_set_of_books_id);
1155: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_currency_code = '|| CE_AUTO_BANK_MATCH.trx_currency_code );
1156: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_date = '|| CE_AUTO_BANK_MATCH.csl_exchange_rate_date);
1157: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_type = '|| CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1158:
1159: x_exchange_rate := gl_currency_api.get_rate(
1152: ELSE -- not 'User'
1153: BEGIN
1154: cep_standard.debug('CE_AUTO_BANK_REC.G_set_of_books_id = '||CE_AUTO_BANK_REC.G_set_of_books_id);
1155: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_currency_code = '|| CE_AUTO_BANK_MATCH.trx_currency_code );
1156: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_date = '|| CE_AUTO_BANK_MATCH.csl_exchange_rate_date);
1157: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_type = '|| CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1158:
1159: x_exchange_rate := gl_currency_api.get_rate(
1160: x_set_of_books_id => CE_AUTO_BANK_REC.G_set_of_books_id,
1153: BEGIN
1154: cep_standard.debug('CE_AUTO_BANK_REC.G_set_of_books_id = '||CE_AUTO_BANK_REC.G_set_of_books_id);
1155: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_currency_code = '|| CE_AUTO_BANK_MATCH.trx_currency_code );
1156: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_date = '|| CE_AUTO_BANK_MATCH.csl_exchange_rate_date);
1157: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_type = '|| CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1158:
1159: x_exchange_rate := gl_currency_api.get_rate(
1160: x_set_of_books_id => CE_AUTO_BANK_REC.G_set_of_books_id,
1161: x_from_currency => nvl(CE_AUTO_BANK_MATCH.trx_currency_code,CE_AUTO_BANK_MATCH.csl_currency_code),
1161: x_from_currency => nvl(CE_AUTO_BANK_MATCH.trx_currency_code,CE_AUTO_BANK_MATCH.csl_currency_code),
1162: x_conversion_date => CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
1163: x_conversion_type => CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1164:
1165: cep_standard.debug('x_ex: '||x_exchange_rate);
1166: cep_standard.debug('csl_ex: '||CE_AUTO_BANK_MATCH.csl_exchange_rate);
1167:
1168: IF (CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL)
1169: THEN
1162: x_conversion_date => CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
1163: x_conversion_type => CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1164:
1165: cep_standard.debug('x_ex: '||x_exchange_rate);
1166: cep_standard.debug('csl_ex: '||CE_AUTO_BANK_MATCH.csl_exchange_rate);
1167:
1168: IF (CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL)
1169: THEN
1170: CE_AUTO_BANK_MATCH.csl_exchange_rate := x_exchange_rate;
1183:
1184: EXCEPTION
1185: WHEN OTHERS THEN
1186: IF (SQL%NOTFOUND) THEN
1187: cep_standard.debug('No Rate for the given criteria');
1188: --for bug 6786355 start
1189: CE_AUTO_BANK_MATCH.csl_exchange_rate_type := null;
1190: CE_AUTO_BANK_MATCH.csl_exchange_rate := null;
1191: CE_AUTO_BANK_MATCH.csl_exchange_rate_date := null;
1196: CE_AUTO_BANK_MATCH.csl_statement_line_id,
1197: 'CE_OTHER_NO_RATE');
1198: RETURN(FALSE);
1199: ELSE
1200: cep_standard.debug('EXCEPTION:gl_currency_api.get_rate' );
1201: RAISE;
1202: END IF;
1203: END;
1204: END IF; -- user type
1201: RAISE;
1202: END IF;
1203: END;
1204: END IF; -- user type
1205: cep_standard.debug('<
1207: EXCEPTION
1208: WHEN OTHERS THEN
1209: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.validate_exchange_details');
1205: cep_standard.debug('<
1207: EXCEPTION
1208: WHEN OTHERS THEN
1209: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.validate_exchange_details');
1210: RAISE;
1211: END validate_exchange_details;
1212:
1213: /* ---------------------------------------------------------------------
1289: FUNCTION get_min_statement_line_id RETURN NUMBER IS
1290: min_statement_line NUMBER;
1291: min_statement_line_num NUMBER;
1292: BEGIN
1293: cep_standard.debug('>>CE_AUTO_BANK_MATCH.get_min_statement_line_id');
1294: SELECT min(line_number)
1295: INTO min_statement_line_num
1296: FROM ce_statement_lines
1297: WHERE statement_header_id = CE_AUTO_BANK_MATCH.csh_statement_header_id;
1300: INTO min_statement_line
1301: FROM ce_statement_lines
1302: WHERE line_number = min_statement_line_num
1303: AND statement_header_id = CE_AUTO_BANK_MATCH.csh_statement_header_id;
1304: cep_standard.debug('<
1306: RETURN (min_statement_line);
1307: EXCEPTION
1308: WHEN OTHERS THEN
1305:
1306: RETURN (min_statement_line);
1307: EXCEPTION
1308: WHEN OTHERS THEN
1309: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.get_min_statement_line_id');
1310: RAISE;
1311: END get_min_statement_line_id;
1312:
1313: /* ---------------------------------------------------------------------
1338: current_record_flag AR_CASH_RECEIPT_HISTORY_ALL.current_record_flag%TYPE;
1339: tx_status CE_LOOKUPS.lookup_code%TYPE;
1340: batch_ba_amount NUMBER;
1341: BEGIN
1342: cep_standard.debug('>>CE_AUTO_BANK_MATCH.lock_transaction');
1343: cep_standard.debug('X_RECONCILE_FLAG='||X_RECONCILE_FLAG ||
1344: ', X_CALL_MODE='|| X_CALL_MODE ||',X_TRX_TYPE='|| X_TRX_TYPE||
1345: ', X_CLEARING_TRX_TYPE='|| X_CLEARING_TRX_TYPE );
1346: cep_standard.debug('X_TRX_ROWID='|| X_TRX_ROWID||
1339: tx_status CE_LOOKUPS.lookup_code%TYPE;
1340: batch_ba_amount NUMBER;
1341: BEGIN
1342: cep_standard.debug('>>CE_AUTO_BANK_MATCH.lock_transaction');
1343: cep_standard.debug('X_RECONCILE_FLAG='||X_RECONCILE_FLAG ||
1344: ', X_CALL_MODE='|| X_CALL_MODE ||',X_TRX_TYPE='|| X_TRX_TYPE||
1345: ', X_CLEARING_TRX_TYPE='|| X_CLEARING_TRX_TYPE );
1346: cep_standard.debug('X_TRX_ROWID='|| X_TRX_ROWID||
1347: ', X_BATCH_BA_AMOUNT='||X_BATCH_BA_AMOUNT||
1342: cep_standard.debug('>>CE_AUTO_BANK_MATCH.lock_transaction');
1343: cep_standard.debug('X_RECONCILE_FLAG='||X_RECONCILE_FLAG ||
1344: ', X_CALL_MODE='|| X_CALL_MODE ||',X_TRX_TYPE='|| X_TRX_TYPE||
1345: ', X_CLEARING_TRX_TYPE='|| X_CLEARING_TRX_TYPE );
1346: cep_standard.debug('X_TRX_ROWID='|| X_TRX_ROWID||
1347: ', X_BATCH_BA_AMOUNT='||X_BATCH_BA_AMOUNT||
1348: ', X_MATCH_CORRECTION_TYPE='||X_MATCH_CORRECTION_TYPE);
1349:
1350: IF (X_reconcile_flag = 'Y') THEN
1393: END IF;
1394: CLOSE CE_AUTO_BANK_MATCH.lock_200;
1395: END IF;
1396: ELSIF (X_trx_type IN ('MISC','CASH'))THEN
1397: cep_standard.debug('open lock_222');
1398: OPEN CE_AUTO_BANK_MATCH.lock_222(X_CALL_MODE, x_trx_rowid);
1399: FETCH CE_AUTO_BANK_MATCH.lock_222 INTO id1, id2, current_record_flag;
1400: cep_standard.debug('id1 '||id1);
1401: cep_standard.debug('id2 '||id2);
1396: ELSIF (X_trx_type IN ('MISC','CASH'))THEN
1397: cep_standard.debug('open lock_222');
1398: OPEN CE_AUTO_BANK_MATCH.lock_222(X_CALL_MODE, x_trx_rowid);
1399: FETCH CE_AUTO_BANK_MATCH.lock_222 INTO id1, id2, current_record_flag;
1400: cep_standard.debug('id1 '||id1);
1401: cep_standard.debug('id2 '||id2);
1402: cep_standard.debug('current_record_flag '||current_record_flag);
1403:
1404: IF (CE_AUTO_BANK_MATCH.lock_222%NOTFOUND) THEN
1397: cep_standard.debug('open lock_222');
1398: OPEN CE_AUTO_BANK_MATCH.lock_222(X_CALL_MODE, x_trx_rowid);
1399: FETCH CE_AUTO_BANK_MATCH.lock_222 INTO id1, id2, current_record_flag;
1400: cep_standard.debug('id1 '||id1);
1401: cep_standard.debug('id2 '||id2);
1402: cep_standard.debug('current_record_flag '||current_record_flag);
1403:
1404: IF (CE_AUTO_BANK_MATCH.lock_222%NOTFOUND) THEN
1405: RAISE NO_DATA_FOUND;
1398: OPEN CE_AUTO_BANK_MATCH.lock_222(X_CALL_MODE, x_trx_rowid);
1399: FETCH CE_AUTO_BANK_MATCH.lock_222 INTO id1, id2, current_record_flag;
1400: cep_standard.debug('id1 '||id1);
1401: cep_standard.debug('id2 '||id2);
1402: cep_standard.debug('current_record_flag '||current_record_flag);
1403:
1404: IF (CE_AUTO_BANK_MATCH.lock_222%NOTFOUND) THEN
1405: RAISE NO_DATA_FOUND;
1406: END IF;
1421: END IF;
1422: CLOSE CE_AUTO_BANK_MATCH.lock_260;
1423: elsif (nvl(CE_AUTO_BANK_MATCH.csl_match_correction_type, 'NONE')
1424: = 'ADJUSTMENT') then
1425: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.trx_rowid2='||
1426: CE_AUTO_BANK_MATCH.trx_rowid2);
1427: OPEN CE_AUTO_BANK_MATCH.lock_260(X_CALL_MODE,
1428: CE_AUTO_BANK_MATCH.trx_rowid2);
1429: FETCH CE_AUTO_BANK_MATCH.lock_260 INTO id1;
1458: end if;
1459: end if; -- CE_AUTO_BANK_MATCH.reconciled_this_run is NULL
1460: end if;
1461: ELSIF (X_trx_type = 'PBATCH') THEN
1462: cep_standard.debug('open lock_batch_checks X_trx_rowid='|| X_trx_rowid);
1463: OPEN CE_AUTO_BANK_MATCH.lock_batch_checks(X_trx_rowid);
1464: FETCH CE_AUTO_BANK_MATCH.lock_batch_checks INTO id1, id2;
1465: IF (CE_AUTO_BANK_MATCH.lock_batch_checks%NOTFOUND) THEN
1466: cep_standard.debug('no_data_found for lock_batch_checks');
1462: cep_standard.debug('open lock_batch_checks X_trx_rowid='|| X_trx_rowid);
1463: OPEN CE_AUTO_BANK_MATCH.lock_batch_checks(X_trx_rowid);
1464: FETCH CE_AUTO_BANK_MATCH.lock_batch_checks INTO id1, id2;
1465: IF (CE_AUTO_BANK_MATCH.lock_batch_checks%NOTFOUND) THEN
1466: cep_standard.debug('no_data_found for lock_batch_checks');
1467:
1468: RAISE NO_DATA_FOUND;
1469: END IF;
1470: cep_standard.debug('open check_amounts id2='||id2);
1466: cep_standard.debug('no_data_found for lock_batch_checks');
1467:
1468: RAISE NO_DATA_FOUND;
1469: END IF;
1470: cep_standard.debug('open check_amounts id2='||id2);
1471: OPEN CE_AUTO_BANK_MATCH.check_amounts(id2);
1472: FETCH CE_AUTO_BANK_MATCH.check_amounts INTO batch_ba_amount;
1473: IF (CE_AUTO_BANK_MATCH.check_amounts%NOTFOUND) THEN
1474: cep_standard.debug('EKA NO DATA');
1470: cep_standard.debug('open check_amounts id2='||id2);
1471: OPEN CE_AUTO_BANK_MATCH.check_amounts(id2);
1472: FETCH CE_AUTO_BANK_MATCH.check_amounts INTO batch_ba_amount;
1473: IF (CE_AUTO_BANK_MATCH.check_amounts%NOTFOUND) THEN
1474: cep_standard.debug('EKA NO DATA');
1475: RAISE NO_DATA_FOUND;
1476: END IF;
1477:
1478: cep_standard.debug('batch_ba_amount='||batch_ba_amount||', X_batch_ba_amount='||X_batch_ba_amount );
1474: cep_standard.debug('EKA NO DATA');
1475: RAISE NO_DATA_FOUND;
1476: END IF;
1477:
1478: cep_standard.debug('batch_ba_amount='||batch_ba_amount||', X_batch_ba_amount='||X_batch_ba_amount );
1479:
1480: IF ((batch_ba_amount = X_batch_ba_amount) OR
1481: ((batch_ba_amount IS NULL) AND (X_batch_ba_amount IS NULL))) THEN
1482: NULL;
1480: IF ((batch_ba_amount = X_batch_ba_amount) OR
1481: ((batch_ba_amount IS NULL) AND (X_batch_ba_amount IS NULL))) THEN
1482: NULL;
1483: ELSE
1484: cep_standard.debug('TOKA NO DATA batch_ba_amount <> X_batch_ba_amount ');
1485: RAISE NO_DATA_FOUND;
1486: END IF;
1487: CLOSE CE_AUTO_BANK_MATCH.Check_Amounts;
1488: CLOSE CE_AUTO_BANK_MATCH.Lock_Batch_Checks;
1486: END IF;
1487: CLOSE CE_AUTO_BANK_MATCH.Check_Amounts;
1488: CLOSE CE_AUTO_BANK_MATCH.Lock_Batch_Checks;
1489: ELSIF (X_trx_type = 'PGROUP') THEN -- SEPA ER 6700007
1490: cep_standard.debug('open lock_group_checks X_trx_rowid='|| X_trx_rowid||' X_LOGICAL_GROUP_REFERENCE-'||X_LOGICAL_GROUP_REFERENCE);
1491: OPEN CE_AUTO_BANK_MATCH.lock_group_checks(X_trx_rowid,X_LOGICAL_GROUP_REFERENCE);
1492: FETCH CE_AUTO_BANK_MATCH.lock_group_checks INTO id1, id2;
1493: IF (CE_AUTO_BANK_MATCH.lock_group_checks%NOTFOUND) THEN
1494: cep_standard.debug('no_data_found for lock_group_checks');
1490: cep_standard.debug('open lock_group_checks X_trx_rowid='|| X_trx_rowid||' X_LOGICAL_GROUP_REFERENCE-'||X_LOGICAL_GROUP_REFERENCE);
1491: OPEN CE_AUTO_BANK_MATCH.lock_group_checks(X_trx_rowid,X_LOGICAL_GROUP_REFERENCE);
1492: FETCH CE_AUTO_BANK_MATCH.lock_group_checks INTO id1, id2;
1493: IF (CE_AUTO_BANK_MATCH.lock_group_checks%NOTFOUND) THEN
1494: cep_standard.debug('no_data_found for lock_group_checks');
1495:
1496: RAISE NO_DATA_FOUND;
1497: END IF;
1498: cep_standard.debug('open check_amounts id2='||id2);
1494: cep_standard.debug('no_data_found for lock_group_checks');
1495:
1496: RAISE NO_DATA_FOUND;
1497: END IF;
1498: cep_standard.debug('open check_amounts id2='||id2);
1499: OPEN CE_AUTO_BANK_MATCH.check_group_amounts(id2,X_LOGICAL_GROUP_REFERENCE);
1500: FETCH CE_AUTO_BANK_MATCH.check_group_amounts INTO batch_ba_amount;
1501: IF (CE_AUTO_BANK_MATCH.check_group_amounts%NOTFOUND) THEN
1502: cep_standard.debug('EKA NO DATA');
1498: cep_standard.debug('open check_amounts id2='||id2);
1499: OPEN CE_AUTO_BANK_MATCH.check_group_amounts(id2,X_LOGICAL_GROUP_REFERENCE);
1500: FETCH CE_AUTO_BANK_MATCH.check_group_amounts INTO batch_ba_amount;
1501: IF (CE_AUTO_BANK_MATCH.check_group_amounts%NOTFOUND) THEN
1502: cep_standard.debug('EKA NO DATA');
1503: RAISE NO_DATA_FOUND;
1504: END IF;
1505:
1506: cep_standard.debug('batch_ba_amount='||batch_ba_amount||', X_batch_ba_amount='||X_batch_ba_amount );
1502: cep_standard.debug('EKA NO DATA');
1503: RAISE NO_DATA_FOUND;
1504: END IF;
1505:
1506: cep_standard.debug('batch_ba_amount='||batch_ba_amount||', X_batch_ba_amount='||X_batch_ba_amount );
1507:
1508: IF ((batch_ba_amount = X_batch_ba_amount) OR
1509: ((batch_ba_amount IS NULL) AND (X_batch_ba_amount IS NULL))) THEN
1510: NULL;
1508: IF ((batch_ba_amount = X_batch_ba_amount) OR
1509: ((batch_ba_amount IS NULL) AND (X_batch_ba_amount IS NULL))) THEN
1510: NULL;
1511: ELSE
1512: cep_standard.debug('TOKA NO DATA batch_ba_amount <> X_batch_ba_amount ');
1513: RAISE NO_DATA_FOUND;
1514: END IF;
1515: CLOSE CE_AUTO_BANK_MATCH.Check_GROUP_Amounts;
1516: CLOSE CE_AUTO_BANK_MATCH.Lock_group_Checks;
1514: END IF;
1515: CLOSE CE_AUTO_BANK_MATCH.Check_GROUP_Amounts;
1516: CLOSE CE_AUTO_BANK_MATCH.Lock_group_Checks;
1517: ELSIF (X_trx_type = 'RBATCH') THEN
1518: cep_standard.debug('open lock_batch_receipts X_trx_rowid='|| X_trx_rowid);
1519: OPEN CE_AUTO_BANK_MATCH.lock_batch_receipts(X_trx_rowid);
1520: FETCH CE_AUTO_BANK_MATCH.lock_batch_receipts INTO id1, id2, id3;
1521: IF (CE_AUTO_BANK_MATCH.lock_batch_receipts%NOTFOUND) THEN
1522: RAISE NO_DATA_FOUND;
1521: IF (CE_AUTO_BANK_MATCH.lock_batch_receipts%NOTFOUND) THEN
1522: RAISE NO_DATA_FOUND;
1523: END IF;
1524:
1525: cep_standard.debug('open receipt_amounts id3='||id3);
1526:
1527: OPEN CE_AUTO_BANK_MATCH.receipt_amounts(id3);
1528: FETCH CE_AUTO_BANK_MATCH.receipt_amounts INTO batch_ba_amount;
1529: IF (CE_AUTO_BANK_MATCH.receipt_amounts%NOTFOUND) THEN
1529: IF (CE_AUTO_BANK_MATCH.receipt_amounts%NOTFOUND) THEN
1530: RAISE NO_DATA_FOUND;
1531: END IF;
1532:
1533: cep_standard.debug('batch_ba_amount='||batch_ba_amount||', X_batch_ba_amount='||X_batch_ba_amount );
1534:
1535: IF ((batch_ba_amount = X_batch_ba_amount) OR
1536: ((batch_ba_amount IS NULL) AND (X_batch_ba_amount IS NULL))) THEN
1537: NULL;
1535: IF ((batch_ba_amount = X_batch_ba_amount) OR
1536: ((batch_ba_amount IS NULL) AND (X_batch_ba_amount IS NULL))) THEN
1537: NULL;
1538: ELSE
1539: cep_standard.debug(' remittance batch no_data_found batch_ba_amount <> X_batch_ba_amount ');
1540: RAISE NO_DATA_FOUND;
1541: END IF;
1542: CLOSE CE_AUTO_BANK_MATCH.Receipt_Amounts;
1543: CLOSE CE_AUTO_BANK_MATCH.Lock_Batch_Receipts;
1584: END IF;
1585: CLOSE CE_AUTO_BANK_MATCH.clear_lock_260;
1586: END IF;
1587: END IF;
1588: cep_standard.debug('<
1590: WHEN NO_DATA_FOUND THEN
1591: cep_standard.debug('Transaction Either Deleted OR Reconciled');
1592: CE_AUTO_BANK_MATCH.close_cursors;
1587: END IF;
1588: cep_standard.debug('<
1590: WHEN NO_DATA_FOUND THEN
1591: cep_standard.debug('Transaction Either Deleted OR Reconciled');
1592: CE_AUTO_BANK_MATCH.close_cursors;
1593: RAISE NO_DATA_FOUND;
1594: WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1595: cep_standard.debug('Could not lock transactions');
1591: cep_standard.debug('Transaction Either Deleted OR Reconciled');
1592: CE_AUTO_BANK_MATCH.close_cursors;
1593: RAISE NO_DATA_FOUND;
1594: WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1595: cep_standard.debug('Could not lock transactions');
1596: CE_AUTO_BANK_MATCH.close_cursors;
1597: RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1598: END lock_transaction;
1599:
1758: curr NUMBER;
1759: tx_type VARCHAR2(30);
1760: le_found NUMBER;
1761: BEGIN
1762: cep_standard.debug('>>CE_AUTO_BANK_MATCH.ce_match');
1763:
1764: no_of_matches := 0;
1765: le_found := 0;
1766:
1763:
1764: no_of_matches := 0;
1765: le_found := 0;
1766:
1767: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type = '|| CE_AUTO_BANK_MATCH.csl_trx_type
1768: ||' CE_AUTO_BANK_MATCH.csl_bank_trx_number = '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
1769: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id = '|| CE_AUTO_BANK_MATCH.csh_bank_account_id
1770: ||', CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date
1771: ||', CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
1765: le_found := 0;
1766:
1767: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type = '|| CE_AUTO_BANK_MATCH.csl_trx_type
1768: ||' CE_AUTO_BANK_MATCH.csl_bank_trx_number = '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
1769: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id = '|| CE_AUTO_BANK_MATCH.csh_bank_account_id
1770: ||', CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date
1771: ||', CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
1772:
1773: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP','SWEEP_OUT') AND
1780: tx_type := 'MISC';
1781: END IF;
1782:
1783:
1784: cep_standard.debug('>>MATCH ce trx by statement_line_id');
1785: curr:=1;
1786: OPEN stmt_ln_id_cur(tx_type);
1787: FETCH stmt_ln_id_cur
1788: INTO CE_AUTO_BANK_MATCH.trx_id,
1811: CLOSE stmt_ln_id_cur;
1812:
1813:
1814: IF (no_of_matches = 0) THEN
1815: cep_standard.debug('>>MATCH ce trx by transaction number, date and amount');
1816: curr:=2;
1817: OPEN trx_num_date_amt_cur(tx_type);
1818: FETCH trx_num_date_amt_cur
1819: INTO CE_AUTO_BANK_MATCH.trx_id,
1844: END IF;
1845:
1846:
1847: IF (no_of_matches = 0) THEN
1848: cep_standard.debug('>>MATCH ce trx by agent bank account, date and amount');
1849: curr:=3;
1850: OPEN agent_ba_date_amt_cur(tx_type);
1851: FETCH agent_ba_date_amt_cur
1852: INTO CE_AUTO_BANK_MATCH.trx_id,
1876: CLOSE agent_ba_date_amt_cur;
1877:
1878: END IF;
1879:
1880: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_id = '|| CE_AUTO_BANK_MATCH.trx_id
1881: ||', CE_AUTO_BANK_MATCH.trx_amount = '|| CE_AUTO_BANK_MATCH.trx_amount
1882: );
1883: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type = '|| CE_AUTO_BANK_MATCH.csl_match_type
1884: ||', no_of_matches = '|| no_of_matches
1879:
1880: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_id = '|| CE_AUTO_BANK_MATCH.trx_id
1881: ||', CE_AUTO_BANK_MATCH.trx_amount = '|| CE_AUTO_BANK_MATCH.trx_amount
1882: );
1883: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type = '|| CE_AUTO_BANK_MATCH.csl_match_type
1884: ||', no_of_matches = '|| no_of_matches
1885: ||', CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date
1886: );
1887:
1925: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <>
1926: CE_AUTO_BANK_REC.G_functional_currency) and
1927: (CE_AUTO_BANK_MATCH.aba_bank_currency <>
1928: CE_AUTO_BANK_MATCH.trx_currency_code) THEN
1929: cep_standard.debug('Forex account not using the same curr as bk');
1930: curr := 6;
1931: RAISE NO_DATA_FOUND;
1932: END IF;
1933:
1930: curr := 6;
1931: RAISE NO_DATA_FOUND;
1932: END IF;
1933:
1934: cep_standard.debug('<
1936: EXCEPTION
1937: WHEN NO_DATA_FOUND THEN
1938: cep_standard.debug('EXCEPTION - NO_DATA_FOUND: No data found in CE_AUTO_BANK_MATCH.ce_match');
1934: cep_standard.debug('<
1936: EXCEPTION
1937: WHEN NO_DATA_FOUND THEN
1938: cep_standard.debug('EXCEPTION - NO_DATA_FOUND: No data found in CE_AUTO_BANK_MATCH.ce_match');
1939: if (curr = 6) then
1940: CE_RECONCILIATION_ERRORS_PKG.insert_row(
1941: CE_AUTO_BANK_MATCH.csh_statement_header_id,
1942: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
1947: end if;
1948: no_of_matches := 0;
1949: WHEN OTHERS THEN
1950: IF (SQL%NOTFOUND) THEN
1951: cep_standard.debug('EXCEPTION - OTHERS: NO data found in CE_AUTO_BANK_MATCH.ce_match');
1952: CE_RECONCILIATION_ERRORS_PKG.insert_row(
1953: CE_AUTO_BANK_MATCH.csh_statement_header_id,
1954: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_TRX_MATCH');
1955: no_of_matches:=0;
1953: CE_AUTO_BANK_MATCH.csh_statement_header_id,
1954: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_TRX_MATCH');
1955: no_of_matches:=0;
1956: ELSIF (SQL%ROWCOUNT >0) THEN
1957: cep_standard.debug('EXCEPTION: More than one CE trx match this statement line' );
1958:
1959: CE_RECONCILIATION_ERRORS_PKG.insert_row(
1960: CE_AUTO_BANK_MATCH.csh_statement_header_id,
1961: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_DUP_CE_TRX_MATCH');
1960: CE_AUTO_BANK_MATCH.csh_statement_header_id,
1961: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_DUP_CE_TRX_MATCH');
1962: no_of_matches:=999;
1963: ELSE
1964: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.ce_match' );
1965: RAISE;
1966: END IF;
1967: END ce_match;
1968:
1983: trx_count NUMBER;
1984: curr NUMBER;
1985:
1986: BEGIN
1987: cep_standard.debug('>>CE_AUTO_BANK_MATCH.pay_eft_match');
1988: no_of_matches := 0;
1989: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type = '|| CE_AUTO_BANK_MATCH.csl_trx_type
1990: ||' CE_AUTO_BANK_MATCH.csl_bank_trx_number = '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
1991: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id = '|| CE_AUTO_BANK_MATCH.csh_bank_account_id
1985:
1986: BEGIN
1987: cep_standard.debug('>>CE_AUTO_BANK_MATCH.pay_eft_match');
1988: no_of_matches := 0;
1989: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type = '|| CE_AUTO_BANK_MATCH.csl_trx_type
1990: ||' CE_AUTO_BANK_MATCH.csl_bank_trx_number = '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
1991: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id = '|| CE_AUTO_BANK_MATCH.csh_bank_account_id
1992: ||', CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date
1993: ||', CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
1987: cep_standard.debug('>>CE_AUTO_BANK_MATCH.pay_eft_match');
1988: no_of_matches := 0;
1989: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type = '|| CE_AUTO_BANK_MATCH.csl_trx_type
1990: ||' CE_AUTO_BANK_MATCH.csl_bank_trx_number = '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
1991: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id = '|| CE_AUTO_BANK_MATCH.csh_bank_account_id
1992: ||', CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date
1993: ||', CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
1994:
1995: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT')) THEN
1998: ELSE
1999: curr := 2;
2000: END IF;
2001:
2002: cep_standard.debug('>>MATCH trx ');
2003: /*added ltrim on the upper(catv.batch_name) for bug 12702799*/
2004:
2005:
2006: SELECT count(*),
2041: group by catv.batch_id, catv.batch_name, catv.trx_date, catv.org_id, catv.ce_bank_acct_use_id; -- bug 7242853
2042:
2043: END IF;
2044:
2045: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_count = '|| CE_AUTO_BANK_MATCH.trx_count
2046: ||', CE_AUTO_BANK_MATCH.trx_amount = '|| CE_AUTO_BANK_MATCH.trx_amount
2047: ||', no_of_currencies = '|| no_of_currencies);
2048: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type = '|| CE_AUTO_BANK_MATCH.csl_match_type
2049: ||', no_of_matches = '|| no_of_matches
2044:
2045: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_count = '|| CE_AUTO_BANK_MATCH.trx_count
2046: ||', CE_AUTO_BANK_MATCH.trx_amount = '|| CE_AUTO_BANK_MATCH.trx_amount
2047: ||', no_of_currencies = '|| no_of_currencies);
2048: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type = '|| CE_AUTO_BANK_MATCH.csl_match_type
2049: ||', no_of_matches = '|| no_of_matches
2050: ||', CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date
2051: ||', CE_AUTO_BANK_MATCH.trx_group = '|| CE_AUTO_BANK_MATCH.trx_group);
2052:
2059: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <>
2060: CE_AUTO_BANK_REC.G_functional_currency) and
2061: (CE_AUTO_BANK_MATCH.aba_bank_currency <>
2062: CE_AUTO_BANK_MATCH.trx_currency_code) THEN
2063: cep_standard.debug('Forex account not using the same curr as bk');
2064: curr := 6;
2065: RAISE NO_DATA_FOUND;
2066: END IF;
2067:
2064: curr := 6;
2065: RAISE NO_DATA_FOUND;
2066: END IF;
2067:
2068: cep_standard.debug('<
2070: EXCEPTION
2071: WHEN NO_DATA_FOUND THEN
2072: cep_standard.debug('EXCEPTION - NO_DATA_FOUND: No data found in CE_AUTO_BANK_MATCH.pay_eft_match');
2068: cep_standard.debug('<
2070: EXCEPTION
2071: WHEN NO_DATA_FOUND THEN
2072: cep_standard.debug('EXCEPTION - NO_DATA_FOUND: No data found in CE_AUTO_BANK_MATCH.pay_eft_match');
2073: if (curr = 6) then
2074: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2075: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2076: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
2085: end if;
2086: no_of_matches := 0;
2087: WHEN OTHERS THEN
2088: IF (SQL%NOTFOUND) THEN
2089: cep_standard.debug('EXCEPTION - OTHERS: NO data found in CE_AUTO_BANK_MATCH.pay_eft_match');
2090: if (curr = 1) then
2091: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2092: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2093: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_BATCH_BACS');
2097: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_BATCH_NACHA');
2098: end if;
2099: no_of_matches:=0;
2100: ELSIF (SQL%ROWCOUNT >0) THEN
2101: cep_standard.debug('EXCEPTION: More than one EFT batch match this payment' );
2102: -- if (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) then
2103: if (curr = 1) then
2104: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2105: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2110: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_DUP_BATCH_NACHA');
2111: end if;
2112: no_of_matches:=999;
2113: ELSE
2114: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.pay_eft_match' );
2115: RAISE;
2116: END IF;
2117: END pay_eft_match;
2118:
2131: PROCEDURE bank_account_match(no_of_matches OUT NOCOPY NUMBER)
2132: IS
2133: curr NUMBER;
2134: BEGIN
2135: cep_standard.debug('>>CE_AUTO_BANK_MATCH.bank_account_match');
2136: no_of_matches := 1;
2137: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) THEN
2138: cep_standard.debug('Searching for matching payment, agent account');
2139: SELECT DISTINCT(c.check_id),
2134: BEGIN
2135: cep_standard.debug('>>CE_AUTO_BANK_MATCH.bank_account_match');
2136: no_of_matches := 1;
2137: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) THEN
2138: cep_standard.debug('Searching for matching payment, agent account');
2139: SELECT DISTINCT(c.check_id),
2140: to_number(NULL),
2141: c.rowid,
2142: DECODE(c.currency_code,
2251: and aph2.org_id = c.org_id
2252: and aph2.transaction_type = 'PAYMENT CLEARING'
2253: and aph2.payment_history_id > aph.payment_history_id);
2254: ELSE
2255: cep_standard.debug('Searching for matching receipt, agent account...');
2256: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text='||CE_AUTO_BANK_MATCH.csl_bank_account_text);
2257: cep_standard.debug('CE_AUTO_BANK_REC.G_org_id='||CE_AUTO_BANK_REC.G_org_id);
2258: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text='||CE_AUTO_BANK_MATCH.csl_invoice_text);
2259: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id='||CE_AUTO_BANK_MATCH.csh_bank_account_id);
2252: and aph2.transaction_type = 'PAYMENT CLEARING'
2253: and aph2.payment_history_id > aph.payment_history_id);
2254: ELSE
2255: cep_standard.debug('Searching for matching receipt, agent account...');
2256: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text='||CE_AUTO_BANK_MATCH.csl_bank_account_text);
2257: cep_standard.debug('CE_AUTO_BANK_REC.G_org_id='||CE_AUTO_BANK_REC.G_org_id);
2258: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text='||CE_AUTO_BANK_MATCH.csl_invoice_text);
2259: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id='||CE_AUTO_BANK_MATCH.csh_bank_account_id);
2260:
2253: and aph2.payment_history_id > aph.payment_history_id);
2254: ELSE
2255: cep_standard.debug('Searching for matching receipt, agent account...');
2256: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text='||CE_AUTO_BANK_MATCH.csl_bank_account_text);
2257: cep_standard.debug('CE_AUTO_BANK_REC.G_org_id='||CE_AUTO_BANK_REC.G_org_id);
2258: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text='||CE_AUTO_BANK_MATCH.csl_invoice_text);
2259: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id='||CE_AUTO_BANK_MATCH.csh_bank_account_id);
2260:
2261: SELECT distinct(crh.cash_receipt_history_id),
2254: ELSE
2255: cep_standard.debug('Searching for matching receipt, agent account...');
2256: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text='||CE_AUTO_BANK_MATCH.csl_bank_account_text);
2257: cep_standard.debug('CE_AUTO_BANK_REC.G_org_id='||CE_AUTO_BANK_REC.G_org_id);
2258: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text='||CE_AUTO_BANK_MATCH.csl_invoice_text);
2259: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id='||CE_AUTO_BANK_MATCH.csh_bank_account_id);
2260:
2261: SELECT distinct(crh.cash_receipt_history_id),
2262: crh.cash_receipt_id,
2255: cep_standard.debug('Searching for matching receipt, agent account...');
2256: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text='||CE_AUTO_BANK_MATCH.csl_bank_account_text);
2257: cep_standard.debug('CE_AUTO_BANK_REC.G_org_id='||CE_AUTO_BANK_REC.G_org_id);
2258: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text='||CE_AUTO_BANK_MATCH.csl_invoice_text);
2259: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id='||CE_AUTO_BANK_MATCH.csh_bank_account_id);
2260:
2261: SELECT distinct(crh.cash_receipt_history_id),
2262: crh.cash_receipt_id,
2263: crh.rowid,
2345:
2346: IF ((CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency) and
2347: (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_MATCH.trx_currency_code))
2348: THEN
2349: cep_standard.debug('Forex account not using the same curr as bk');
2350: curr := 1;
2351: RAISE NO_DATA_FOUND;
2352: END IF;
2353:
2350: curr := 1;
2351: RAISE NO_DATA_FOUND;
2352: END IF;
2353:
2354: cep_standard.debug('<
2356: WHEN NO_DATA_FOUND THEN
2357: cep_standard.debug('EXCEPTION: No data found');
2358: if (curr = 1) then
2353:
2354: cep_standard.debug('<
2356: WHEN NO_DATA_FOUND THEN
2357: cep_standard.debug('EXCEPTION: No data found');
2358: if (curr = 1) then
2359: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2360: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2361: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
2359: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2360: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2361: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
2362: end if;
2363: cep_standard.debug('EXCEPTION: NO bank account match this receipt');
2364: if (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) then
2365: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2366: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2367: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_BAP');
2372: end if;
2373: no_of_matches := 0;
2374: WHEN OTHERS THEN
2375: IF (SQL%ROWCOUNT > 0) THEN
2376: cep_standard.debug('EXCEPTION: More than one bank account match this transaction');
2377: if (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) then
2378: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2379: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2380: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APT_PARTIAL');
2384: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_ART_PARTIAL');
2385: end if;
2386: no_of_matches:=999;
2387: ELSE
2388: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.bank_account_match' );
2389: RAISE;
2390: END IF;
2391: END bank_account_match;
2392:
2403: --------------------------------------------------------------------- */
2404: PROCEDURE invoice_match (no_of_matches OUT NOCOPY NUMBER) IS
2405: curr NUMBER;
2406: BEGIN
2407: cep_standard.debug('>>CE_AUTO_BANK_MATCH.invoice_match');
2408: no_of_matches := 1;
2409: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) THEN
2410: SELECT DISTINCT(c.check_id),
2411: to_number(NULL),
2641: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <>
2642: CE_AUTO_BANK_REC.G_functional_currency) and
2643: (CE_AUTO_BANK_MATCH.aba_bank_currency <>
2644: CE_AUTO_BANK_MATCH.trx_currency_code) THEN
2645: cep_standard.debug('Forex account not using the same curr as bk');
2646: curr := 1;
2647: RAISE NO_DATA_FOUND;
2648: END IF;
2649:
2646: curr := 1;
2647: RAISE NO_DATA_FOUND;
2648: END IF;
2649:
2650: cep_standard.debug('<
2652: WHEN NO_DATA_FOUND THEN
2653: cep_standard.debug('EXCEPTION: No data found');
2654: if (curr = 1) then
2649:
2650: cep_standard.debug('<
2652: WHEN NO_DATA_FOUND THEN
2653: cep_standard.debug('EXCEPTION: No data found');
2654: if (curr = 1) then
2655: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2656: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2657: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
2655: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2656: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2657: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
2658: end if;
2659: cep_standard.debug('EXCEPTION: NO invoices match this receipt');
2660: if (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) then
2661: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2662: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2663: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_INP');
2668: end if;
2669: no_of_matches := 0;
2670: WHEN OTHERS THEN
2671: IF (SQL%ROWCOUNT >0) THEN
2672: cep_standard.debug('EXCEPTION: More than one invoice match this transaction');
2673: if (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) then
2674: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2675: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2676: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APT_PARTIAL');
2680: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_ART_PARTIAL');
2681: end if;
2682: no_of_matches:=999;
2683: ELSE
2684: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.invoice_match' );
2685: RAISE;
2686: END IF;
2687: END invoice_match;
2688:
2721: funct_curr_ext_precision NUMBER;
2722: funct_curr_min_acct_unit NUMBER;
2723:
2724: BEGIN
2725: cep_standard.debug('>>CE_AUTO_BANK_MATCH.batch_match csl_trx_type='||CE_AUTO_BANK_MATCH.csl_trx_type);
2726: no_of_matches := 0;
2727:
2728: IF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'PAY_EFT')
2729: THEN
2833: CE_AUTO_BANK_MATCH.trx_cleared_amount := trx_cleared_amount_ap + trx_cleared_amount_ce;
2834: no_of_currencies := no_of_currencies_ap + no_of_currencies_ce;
2835: CE_AUTO_BANK_MATCH.trx_curr_amount := trx_curr_amount_ap + trx_curr_amount_ce;
2836:
2837: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_amount='|| CE_AUTO_BANK_MATCH.trx_amount);
2838:
2839: /* Bug 8218042 - The amount is not rounded in the view. */
2840: IF ((CE_AUTO_BANK_MATCH.trx_currency_type = 'FOREIGN') AND
2841: (CE_AUTO_BANK_MATCH.trx_currency_code <> CE_AUTO_BANK_REC.G_functional_currency))
2839: /* Bug 8218042 - The amount is not rounded in the view. */
2840: IF ((CE_AUTO_BANK_MATCH.trx_currency_type = 'FOREIGN') AND
2841: (CE_AUTO_BANK_MATCH.trx_currency_code <> CE_AUTO_BANK_REC.G_functional_currency))
2842: THEN
2843: cep_standard.debug('rounding trx amount');
2844: fnd_currency.get_info(
2845: CE_AUTO_BANK_REC.G_functional_currency,
2846: funct_curr_precision,
2847: funct_curr_ext_precision,
2851: round(CE_AUTO_BANK_MATCH.trx_curr_amount * CE_AUTO_BANK_MATCH.trx_exchange_rate,
2852: funct_curr_precision);
2853: END IF;
2854:
2855: cep_standard.debug('rounded CE_AUTO_BANK_MATCH.trx_amount='|| CE_AUTO_BANK_MATCH.trx_amount);
2856: /* Bug 8218042 */
2857:
2858: ELSIF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('CREDIT','NSF','REJECTED'))
2859: THEN
2897: AND ab.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, ab.org_id)
2898: AND ab.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id;
2899:
2900: -- 16169940: Added debug statements
2901: cep_standard.debug('16169940: CE_AUTO_BANK_MATCH.trx_date='||CE_AUTO_BANK_MATCH.trx_date);
2902: cep_standard.debug('16169940: CE_AUTO_BANK_MATCH.trx_gl_date='||CE_AUTO_BANK_MATCH.trx_gl_date);
2903: cep_standard.debug('16169940: CE_AUTO_BANK_MATCH.trx_exchange_rate_date='||CE_AUTO_BANK_MATCH.trx_exchange_rate_date);
2904:
2905: curr := 4;
2898: AND ab.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id;
2899:
2900: -- 16169940: Added debug statements
2901: cep_standard.debug('16169940: CE_AUTO_BANK_MATCH.trx_date='||CE_AUTO_BANK_MATCH.trx_date);
2902: cep_standard.debug('16169940: CE_AUTO_BANK_MATCH.trx_gl_date='||CE_AUTO_BANK_MATCH.trx_gl_date);
2903: cep_standard.debug('16169940: CE_AUTO_BANK_MATCH.trx_exchange_rate_date='||CE_AUTO_BANK_MATCH.trx_exchange_rate_date);
2904:
2905: curr := 4;
2906: SELECT
2899:
2900: -- 16169940: Added debug statements
2901: cep_standard.debug('16169940: CE_AUTO_BANK_MATCH.trx_date='||CE_AUTO_BANK_MATCH.trx_date);
2902: cep_standard.debug('16169940: CE_AUTO_BANK_MATCH.trx_gl_date='||CE_AUTO_BANK_MATCH.trx_gl_date);
2903: cep_standard.debug('16169940: CE_AUTO_BANK_MATCH.trx_exchange_rate_date='||CE_AUTO_BANK_MATCH.trx_exchange_rate_date);
2904:
2905: curr := 4;
2906: SELECT
2907: count(*),
2931: THEN
2932: RAISE NO_DATA_FOUND;
2933: END IF;
2934:
2935: cep_standard.debug('Batch trx_count = '||trx_count);
2936: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
2937: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
2938: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
2939: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
2932: RAISE NO_DATA_FOUND;
2933: END IF;
2934:
2935: cep_standard.debug('Batch trx_count = '||trx_count);
2936: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
2937: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
2938: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
2939: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
2940: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
2933: END IF;
2934:
2935: cep_standard.debug('Batch trx_count = '||trx_count);
2936: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
2937: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
2938: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
2939: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
2940: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
2941:
2934:
2935: cep_standard.debug('Batch trx_count = '||trx_count);
2936: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
2937: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
2938: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
2939: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
2940: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
2941:
2942: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency) AND
2935: cep_standard.debug('Batch trx_count = '||trx_count);
2936: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
2937: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
2938: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
2939: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
2940: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
2941:
2942: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency) AND
2943: (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_MATCH.trx_currency_code)
2936: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
2937: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
2938: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
2939: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
2940: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
2941:
2942: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency) AND
2943: (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_MATCH.trx_currency_code)
2944: THEN
2941:
2942: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency) AND
2943: (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_MATCH.trx_currency_code)
2944: THEN
2945: cep_standard.debug('Forex account not using the same curr as bk');
2946: curr := 6;
2947: RAISE NO_DATA_FOUND;
2948: END IF;
2949:
2946: curr := 6;
2947: RAISE NO_DATA_FOUND;
2948: END IF;
2949:
2950: cep_standard.debug('<
2952: EXCEPTION
2953: WHEN NO_DATA_FOUND
2954: THEN
2951:
2952: EXCEPTION
2953: WHEN NO_DATA_FOUND
2954: THEN
2955: cep_standard.debug('EXCEPTION: No data found, curr='||curr);
2956: IF (curr = 6)
2957: THEN
2958: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2959: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2975: no_of_matches := 0;
2976: WHEN OTHERS THEN
2977: IF (SQL%NOTFOUND)
2978: THEN
2979: cep_standard.debug('EXCEPTION: NO data found in batch_match, curr='||curr);
2980: IF (curr = 1)
2981: THEN
2982: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2983: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2992: END IF;
2993: no_of_matches:=0;
2994: ELSIF (SQL%ROWCOUNT >0)
2995: THEN
2996: cep_standard.debug('EXCEPTION: More than one batch match this line, curr='||curr );
2997: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP'))
2998: THEN
2999: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3000: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3007: 'CE_ABR_ARB_PARTIAL');
3008: END IF;
3009: no_of_matches:=999;
3010: ELSE
3011: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.batch_match' );
3012: RAISE;
3013: END IF;
3014: END batch_match;
3015:
3037: funct_curr_ext_precision NUMBER;
3038: funct_curr_min_acct_unit NUMBER;
3039:
3040: BEGIN
3041: cep_standard.debug('>>CE_AUTO_BANK_MATCH.group_match csl_trx_type='||CE_AUTO_BANK_MATCH.csl_trx_type);
3042: no_of_matches := 0;
3043:
3044: curr := 1;
3045: --
3114: AND IPA.PAYMENT_ID = ACA.PAYMENT_ID
3115: AND IPA.LOGICAL_GROUP_REFERENCE = NVL(CE_AUTO_BANK_MATCH.LOGICAL_GROUP_REFERENCE,IPA.LOGICAL_GROUP_REFERENCE));
3116:
3117: IF (trx_count = 0) THEN
3118: cep_standard.debug('No trx for group '||CE_AUTO_BANK_MATCH.csl_bank_trx_number);
3119: RAISE NO_DATA_FOUND;
3120: END IF;
3121: cep_standard.debug('Group trx_count = '||trx_count);
3122: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
3117: IF (trx_count = 0) THEN
3118: cep_standard.debug('No trx for group '||CE_AUTO_BANK_MATCH.csl_bank_trx_number);
3119: RAISE NO_DATA_FOUND;
3120: END IF;
3121: cep_standard.debug('Group trx_count = '||trx_count);
3122: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
3123: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
3124: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
3125: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
3118: cep_standard.debug('No trx for group '||CE_AUTO_BANK_MATCH.csl_bank_trx_number);
3119: RAISE NO_DATA_FOUND;
3120: END IF;
3121: cep_standard.debug('Group trx_count = '||trx_count);
3122: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
3123: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
3124: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
3125: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
3126: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
3119: RAISE NO_DATA_FOUND;
3120: END IF;
3121: cep_standard.debug('Group trx_count = '||trx_count);
3122: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
3123: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
3124: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
3125: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
3126: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
3127:
3120: END IF;
3121: cep_standard.debug('Group trx_count = '||trx_count);
3122: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
3123: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
3124: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
3125: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
3126: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
3127:
3128: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency) AND
3121: cep_standard.debug('Group trx_count = '||trx_count);
3122: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
3123: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
3124: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
3125: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
3126: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
3127:
3128: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency) AND
3129: (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_MATCH.trx_currency_code)
3122: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
3123: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
3124: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
3125: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
3126: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
3127:
3128: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency) AND
3129: (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_MATCH.trx_currency_code)
3130: THEN
3127:
3128: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency) AND
3129: (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_MATCH.trx_currency_code)
3130: THEN
3131: cep_standard.debug('Forex trx not using the same curr as account');
3132: curr := 6;
3133: RAISE NO_DATA_FOUND;
3134: END IF;
3135:
3136: /* Bug 8218042 - The amount is not rounded in the view. */
3137: IF ((CE_AUTO_BANK_MATCH.trx_currency_type = 'FOREIGN') AND
3138: (CE_AUTO_BANK_MATCH.trx_currency_code <> CE_AUTO_BANK_REC.G_functional_currency))
3139: THEN
3140: cep_standard.debug('rounding trx amount');
3141: fnd_currency.get_info(CE_AUTO_BANK_REC.G_functional_currency,
3142: funct_curr_precision,
3143: funct_curr_ext_precision,
3144: funct_curr_min_acct_unit);
3146: CE_AUTO_BANK_MATCH.trx_amount := round(CE_AUTO_BANK_MATCH.trx_curr_amount *
3147: CE_AUTO_BANK_MATCH.trx_exchange_rate,
3148: funct_curr_precision);
3149: END IF;
3150: cep_standard.debug('rounded CE_AUTO_BANK_MATCH.trx_amount='|| CE_AUTO_BANK_MATCH.trx_amount);
3151: /* Bug 8218042 */
3152:
3153: cep_standard.debug('<
3149: END IF;
3150: cep_standard.debug('rounded CE_AUTO_BANK_MATCH.trx_amount='|| CE_AUTO_BANK_MATCH.trx_amount);
3151: /* Bug 8218042 */
3152:
3153: cep_standard.debug('<
3155: WHEN NO_DATA_FOUND THEN
3156: cep_standard.debug('EXCEPTION: #1 No data found in group_match');
3157: cep_standard.debug('curr = '||curr);
3152:
3153: cep_standard.debug('<
3155: WHEN NO_DATA_FOUND THEN
3156: cep_standard.debug('EXCEPTION: #1 No data found in group_match');
3157: cep_standard.debug('curr = '||curr);
3158: if (curr = 6) then
3159: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3160: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3153: cep_standard.debug('<
3155: WHEN NO_DATA_FOUND THEN
3156: cep_standard.debug('EXCEPTION: #1 No data found in group_match');
3157: cep_standard.debug('curr = '||curr);
3158: if (curr = 6) then
3159: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3160: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3161: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
3166: end if;
3167: no_of_matches := 0;
3168: WHEN OTHERS THEN
3169: IF (SQL%NOTFOUND) THEN
3170: cep_standard.debug('EXCEPTION: NO data found in group_match');
3171: cep_standard.debug('curr = '||curr);
3172: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3173: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3174: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_GROUP_P');
3167: no_of_matches := 0;
3168: WHEN OTHERS THEN
3169: IF (SQL%NOTFOUND) THEN
3170: cep_standard.debug('EXCEPTION: NO data found in group_match');
3171: cep_standard.debug('curr = '||curr);
3172: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3173: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3174: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_GROUP_P');
3175: no_of_matches:=0;
3173: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3174: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_GROUP_P');
3175: no_of_matches:=0;
3176: ELSIF (SQL%ROWCOUNT >0) THEN
3177: cep_standard.debug('EXCEPTION: More than one batch match this group' );
3178: cep_standard.debug('curr = '||curr);
3179: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3180: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3181: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APG_PARTIAL');
3174: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_GROUP_P');
3175: no_of_matches:=0;
3176: ELSIF (SQL%ROWCOUNT >0) THEN
3177: cep_standard.debug('EXCEPTION: More than one batch match this group' );
3178: cep_standard.debug('curr = '||curr);
3179: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3180: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3181: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APG_PARTIAL');
3182: no_of_matches:=999;
3180: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3181: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APG_PARTIAL');
3182: no_of_matches:=999;
3183: ELSE
3184: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.group_match' );
3185: RAISE;
3186: END IF;
3187: END group_match;
3188:
3193: min_acct_unit NUMBER;
3194: acctd_amount NUMBER;
3195: rounded_amount NUMBER;
3196: BEGIN
3197: cep_standard.debug('>>CE_AUTO_BANK_MATCH.convert_to_base_curr');
3198:
3199: IF (CE_AUTO_BANK_MATCH.csl_exchange_rate_type <> 'User') THEN
3200:
3201: BEGIN
3206: CE_AUTO_BANK_MATCH.csl_trx_date),
3207: CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
3208: amount_to_convert);
3209:
3210: cep_standard.debug('acctd_amount '||acctd_amount);
3211: EXCEPTION
3212: WHEN OTHERS THEN
3213: cep_standard.debug('EXCEPTION: Could not convert amount');
3214: acctd_amount := NULL;
3209:
3210: cep_standard.debug('acctd_amount '||acctd_amount);
3211: EXCEPTION
3212: WHEN OTHERS THEN
3213: cep_standard.debug('EXCEPTION: Could not convert amount');
3214: acctd_amount := NULL;
3215: END;
3216:
3217: rounded_amount := acctd_amount;
3228: END IF;
3229:
3230: END IF;
3231:
3232: cep_standard.debug('<
3234:
3235: EXCEPTION
3236: WHEN OTHERS THEN
3233: RETURN(rounded_amount);
3234:
3235: EXCEPTION
3236: WHEN OTHERS THEN
3237: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.convert_to_base_curr');
3238: RAISE;
3239: END convert_to_base_curr;
3240:
3241: /* ---------------------------------------------------------------------
3267: current_org_id NUMBER;
3268: receivables_trx_org_id NUMBER;
3269: l_creation_status AR_RECEIPT_CLASSES.creation_status%TYPE; --Bug 9021558 Added
3270: BEGIN
3271: cep_standard.debug('>>CE_AUTO_BANK_MATCH.create_misc_trx');
3272: IF (CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag = 'Y' AND
3273: CE_AUTO_BANK_MATCH.csl_matching_against <> 'STMT')
3274: THEN
3275: cep_standard.debug('DEBUG: trx_curr: '|| CE_AUTO_BANK_MATCH.trx_currency_type);
3271: cep_standard.debug('>>CE_AUTO_BANK_MATCH.create_misc_trx');
3272: IF (CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag = 'Y' AND
3273: CE_AUTO_BANK_MATCH.csl_matching_against <> 'STMT')
3274: THEN
3275: cep_standard.debug('DEBUG: trx_curr: '|| CE_AUTO_BANK_MATCH.trx_currency_type);
3276: IF (trx_currency_type = 'FOREIGN' AND
3277: CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency)
3278: OR(trx_currency_type = 'FUNCTIONAL' AND
3279: CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency)
3305: OR (CE_AUTO_BANK_MATCH.csl_exchange_rate_type <> 'User' AND
3306: CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL))
3307: THEN
3308: IF (NOT validate_exchange_details) THEN
3309: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.trx_validation' );
3310: END IF;
3311: END IF;
3312: END IF; -- 9095828: trx_currency_type <> 'FUNCTIONAL' check
3313:
3347: CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
3348: CE_AUTO_BANK_MATCH.calc_csl_amount);
3349: EXCEPTION
3350: WHEN OTHERS THEN
3351: cep_standard.debug('EXCEPTION: Could not convert amount');
3352: receipt_amount := NULL;
3353: END;
3354: base_receipt_amount := convert_to_base_curr(receipt_amount);
3355: ELSE -- forigen currency type 'User'
3388: 'CE_PAYMENT_METHOD');
3389: ELSE
3390:
3391: --9021558: Check creation_status
3392: cep_standard.debug('9021558: Checking creation_status');
3393: SELECT creation_status
3394: INTO l_creation_status
3395: FROM ce_receipt_methods_v
3396: WHERE receipt_method_id = CE_AUTO_BANK_MATCH.csl_receipt_method_id;
3393: SELECT creation_status
3394: INTO l_creation_status
3395: FROM ce_receipt_methods_v
3396: WHERE receipt_method_id = CE_AUTO_BANK_MATCH.csl_receipt_method_id;
3397: cep_standard.debug('9021558: creation_status='||l_creation_status);
3398:
3399: -- 9021558: Don't create receipt if status is CONFIRMED
3400: IF (l_creation_status = 'CONFIRMED') THEN
3401: CE_RECONCILIATION_ERRORS_PKG.insert_row(CE_AUTO_BANK_MATCH.csh_statement_header_id,
3400: IF (l_creation_status = 'CONFIRMED') THEN
3401: CE_RECONCILIATION_ERRORS_PKG.insert_row(CE_AUTO_BANK_MATCH.csh_statement_header_id,
3402: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3403: 'CE_ABR_BAD_RM');
3404: cep_standard.debug('9021558: CE_AUTO_BANK_MATCH.trx_status='||
3405: NVL(CE_AUTO_BANK_MATCH.trx_status,'X'));
3406:
3407: ELSE -- 9021558: receipt can be created
3408: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_receivables_trx_id= '
3404: cep_standard.debug('9021558: CE_AUTO_BANK_MATCH.trx_status='||
3405: NVL(CE_AUTO_BANK_MATCH.trx_status,'X'));
3406:
3407: ELSE -- 9021558: receipt can be created
3408: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_receivables_trx_id= '
3409: || CE_AUTO_BANK_MATCH.csl_receivables_trx_id);
3410: -- bug 5185358 not able to create misc receipt
3411: -- get_receivables_org_id(receivables_trx_org_id); -- Bug 7655528 Commented The Line
3412: -- CE_AUTO_BANK_MATCH.trx_org_id := receivables_trx_org_id; -- Bug 7655528 Commented The Line
3409: || CE_AUTO_BANK_MATCH.csl_receivables_trx_id);
3410: -- bug 5185358 not able to create misc receipt
3411: -- get_receivables_org_id(receivables_trx_org_id); -- Bug 7655528 Commented The Line
3412: -- CE_AUTO_BANK_MATCH.trx_org_id := receivables_trx_org_id; -- Bug 7655528 Commented The Line
3413: cep_standard.debug('receivables_trx_org_id= '|| receivables_trx_org_id);
3414: -- set_single_org(receivables_trx_org_id); --Bug 16568960 Commented the Line
3415: SELECT mo_global.GET_CURRENT_ORG_ID
3416: INTO current_org_id
3417: FROM dual;
3415: SELECT mo_global.GET_CURRENT_ORG_ID
3416: INTO current_org_id
3417: FROM dual;
3418:
3419: cep_standard.debug('current_org_id =' ||current_org_id );
3420: cep_standard.debug('create_misc_trx: >> CE_AUTO_BANK_CLEAR.misc_receipt');
3421: -- Bug 14227991 Added Savepoint
3422: savepoint ce_crt_misc_rec_sp;
3423:
3416: INTO current_org_id
3417: FROM dual;
3418:
3419: cep_standard.debug('current_org_id =' ||current_org_id );
3420: cep_standard.debug('create_misc_trx: >> CE_AUTO_BANK_CLEAR.misc_receipt');
3421: -- Bug 14227991 Added Savepoint
3422: savepoint ce_crt_misc_rec_sp;
3423:
3424: CE_AUTO_BANK_CLEAR.misc_receipt(
3457: X_cr_id => CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
3458: X_effective_date => CE_AUTO_BANK_MATCH.csl_effective_date,
3459: X_org_id => NVL(CE_AUTO_BANK_MATCH.trx_org_id, CE_AUTO_BANK_REC.G_org_id)
3460: );
3461: cep_standard.debug('end create_misc_trx: >> CE_AUTO_BANK_CLEAR.misc_receipt');
3462: CE_AUTO_BANK_MATCH.csl_match_found := 'FULL';
3463: END IF; --9021558: check creation_status
3464: END IF; -- validate payment method
3465: END IF; -- if not creating foreign misc receipts with null exchange info
3475: END IF; -- FOREIGN CURRENCY check
3476: ELSE -- create flag = 'N'
3477: CE_AUTO_BANK_MATCH.csl_match_found := 'NONE';
3478: END IF; -- If create-misc-flag = 'Y'
3479: cep_standard.debug('<
3481: WHEN NO_DATA_FOUND THEN
3482: cep_standard.debug('CE_AUTO_BANK_MATCH.create_misc_trx no_data_found');
3483: -- Bug 14227991 Added Rollback to Savepoint And Error Message
3478: END IF; -- If create-misc-flag = 'Y'
3479: cep_standard.debug('<
3481: WHEN NO_DATA_FOUND THEN
3482: cep_standard.debug('CE_AUTO_BANK_MATCH.create_misc_trx no_data_found');
3483: -- Bug 14227991 Added Rollback to Savepoint And Error Message
3484: rollback to savepoint ce_crt_misc_rec_sp;
3485: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3486: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3486: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3487: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_ABR_ERR_CRT_MISC_RCT');
3488: RAISE NO_DATA_FOUND;
3489: WHEN OTHERS THEN
3490: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.create_misc_trx' );
3491: RAISE;
3492: END create_misc_trx;
3493:
3494: /* ---------------------------------------------------------------------
3520: base_tolerance_amount NUMBER;
3521: l_unclearing_gl_date DATE := NULL; -- Bug 14725907: Added
3522: l_cleared_record_exists NUMBER; -- Bug 14725907: Added
3523: BEGIN
3524: cep_standard.debug('>>CE_AUTO_BANK_MATCH.trx_validation');
3525: valid_trx := TRUE;
3526: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type='||CE_AUTO_BANK_MATCH.csl_match_type||
3527: ', CE_AUTO_BANK_MATCH.csl_clearing_trx_type='||CE_AUTO_BANK_MATCH.csl_clearing_trx_type );
3528:
3522: l_cleared_record_exists NUMBER; -- Bug 14725907: Added
3523: BEGIN
3524: cep_standard.debug('>>CE_AUTO_BANK_MATCH.trx_validation');
3525: valid_trx := TRUE;
3526: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type='||CE_AUTO_BANK_MATCH.csl_match_type||
3527: ', CE_AUTO_BANK_MATCH.csl_clearing_trx_type='||CE_AUTO_BANK_MATCH.csl_clearing_trx_type );
3528:
3529: -- 7571492: Added 'PGROUP'
3530: IF (CE_AUTO_BANK_MATCH.csl_match_type IN
3533: IF (CE_AUTO_BANK_MATCH.csl_match_type IN ('PBATCH','RBATCH','PGROUP'))
3534: THEN
3535: IF (no_of_currencies > 0)
3536: THEN
3537: cep_standard.debug('no_of_currencies='||no_of_currencies);
3538: cep_standard.debug('ERROR#01: CE_PBATCH_CURRENCY');
3539: valid_trx := FALSE;
3540: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3541: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3534: THEN
3535: IF (no_of_currencies > 0)
3536: THEN
3537: cep_standard.debug('no_of_currencies='||no_of_currencies);
3538: cep_standard.debug('ERROR#01: CE_PBATCH_CURRENCY');
3539: valid_trx := FALSE;
3540: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3541: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3542: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3543: 'CE_PBATCH_CURRENCY');
3544: END IF;
3545: END IF;
3546:
3547: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_currency_type='||trx_currency_type);
3548: -- Validate the transaction currency for foreign transactions
3549: IF (CE_AUTO_BANK_MATCH.trx_currency_type IN ('BANK','FOREIGN'))
3550: THEN
3551: IF (NOT validate_exchange_details)
3549: IF (CE_AUTO_BANK_MATCH.trx_currency_type IN ('BANK','FOREIGN'))
3550: THEN
3551: IF (NOT validate_exchange_details)
3552: THEN
3553: cep_standard.debug('validate_exchange_details failed');
3554: valid_trx := FALSE;
3555: ELSE
3556: cep_standard.debug('validate_exchange_details successful');
3557: END IF;
3552: THEN
3553: cep_standard.debug('validate_exchange_details failed');
3554: valid_trx := FALSE;
3555: ELSE
3556: cep_standard.debug('validate_exchange_details successful');
3557: END IF;
3558: ELSE
3559: cep_standard.debug('validate_exchange_details not required.');
3560: END IF;
3555: ELSE
3556: cep_standard.debug('validate_exchange_details successful');
3557: END IF;
3558: ELSE
3559: cep_standard.debug('validate_exchange_details not required.');
3560: END IF;
3561: -------------------------------------------------------------------------
3562: -- We calculate the tolerance here since here is where we need that
3563: -- for the first time
3561: -------------------------------------------------------------------------
3562: -- We calculate the tolerance here since here is where we need that
3563: -- for the first time
3564: calc_actual_tolerance;
3565: cep_standard.debug('DEBUG#01.tolerance_amount=' || tolerance_amount);
3566: -------------------------------------------------------------------------
3567: -- Validate the transaction amount
3568: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_correction_type='||CE_AUTO_BANK_MATCH.csl_match_correction_type);
3569: IF (CE_AUTO_BANK_MATCH.csl_match_correction_type = 'REVERSAL')
3564: calc_actual_tolerance;
3565: cep_standard.debug('DEBUG#01.tolerance_amount=' || tolerance_amount);
3566: -------------------------------------------------------------------------
3567: -- Validate the transaction amount
3568: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_correction_type='||CE_AUTO_BANK_MATCH.csl_match_correction_type);
3569: IF (CE_AUTO_BANK_MATCH.csl_match_correction_type = 'REVERSAL')
3570: THEN
3571: CE_AUTO_BANK_MATCH.csl_charges_amount := NULL;
3572: ELSIF (CE_AUTO_BANK_MATCH.csl_match_correction_type = 'ADJUSTMENT')
3580: ELSE
3581: comp_csl_amount := CE_AUTO_BANK_MATCH.corr_csl_amount;
3582: END IF;
3583:
3584: cep_standard.debug('comp_csl_amount: '||comp_csl_amount);
3585: cep_standard.debug('trx_amount: '||trx_amount);
3586: IF CE_AUTO_BANK_MATCH.trx_amount NOT BETWEEN
3587: (comp_csl_amount- CE_AUTO_BANK_MATCH.tolerance_amount)
3588: AND (comp_csl_amount + CE_AUTO_BANK_MATCH.tolerance_amount)
3581: comp_csl_amount := CE_AUTO_BANK_MATCH.corr_csl_amount;
3582: END IF;
3583:
3584: cep_standard.debug('comp_csl_amount: '||comp_csl_amount);
3585: cep_standard.debug('trx_amount: '||trx_amount);
3586: IF CE_AUTO_BANK_MATCH.trx_amount NOT BETWEEN
3587: (comp_csl_amount- CE_AUTO_BANK_MATCH.tolerance_amount)
3588: AND (comp_csl_amount + CE_AUTO_BANK_MATCH.tolerance_amount)
3589: THEN
3586: IF CE_AUTO_BANK_MATCH.trx_amount NOT BETWEEN
3587: (comp_csl_amount- CE_AUTO_BANK_MATCH.tolerance_amount)
3588: AND (comp_csl_amount + CE_AUTO_BANK_MATCH.tolerance_amount)
3589: THEN
3590: cep_standard.debug('ERROR#02: CE_PMT_AMOUNT');
3591: valid_trx := FALSE;
3592: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3593: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3594: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3598: AND CE_AUTO_BANK_REC.G_open_interface_matching_code = 'D')
3599: THEN
3600: -- do not perform the following check for open-interface
3601: -- transactions when matched by Date and Amount
3602: cep_standard.debug('no check for OI trx matched by Date and Amount.');
3603: null;
3604:
3605: /* 7581995 : Statement line amount is always in bank-account currency */
3606: ELSIF (CE_AUTO_BANK_MATCH.trx_amount NOT BETWEEN
3606: ELSIF (CE_AUTO_BANK_MATCH.trx_amount NOT BETWEEN
3607: (CE_AUTO_BANK_MATCH.calc_csl_amount-CE_AUTO_BANK_MATCH.tolerance_amount)
3608: AND (CE_AUTO_BANK_MATCH.calc_csl_amount+CE_AUTO_BANK_MATCH.tolerance_amount))
3609: THEN
3610: cep_standard.debug('ERROR#03: CE_PMT_AMOUNT');
3611: valid_trx := FALSE;
3612: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3613: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3614: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3616:
3617: /* Validate the charges_amount */
3618: ELSIF (abs(CE_AUTO_BANK_MATCH.csl_charges_amount) > CE_AUTO_BANK_MATCH.tolerance_amount)
3619: THEN
3620: cep_standard.debug('ERROR#04: CE_CHARGES_AMOUNT');
3621: valid_trx:= FALSE;
3622: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3623: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3624: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3625: 'CE_CHARGES_AMOUNT');
3626: END IF;
3627:
3628: IF valid_trx THEN
3629: cep_standard.debug('Validated tolerances#01 - no errors');
3630: END IF;
3631:
3632: -- If trx_currency_type is BANK, check that there are no gross
3633: -- discrepancies in foreign exchange rates provided
3636: THEN
3637: base_tolerance_amount := convert_to_base_curr(CE_AUTO_BANK_MATCH.tolerance_amount);
3638: base_csl_amount := convert_to_base_curr(CE_AUTO_BANK_MATCH.calc_csl_amount);
3639:
3640: cep_standard.debug('CE_AUTO_BANK_MATCH.calc_csl_amount: '||calc_csl_amount);
3641: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_base_amount: '||trx_base_amount);
3642: cep_standard.debug('CE_AUTO_BANK_MATCH.base_csl_amount: '||base_csl_amount);
3643: cep_standard.debug('CE_AUTO_BANK_MATCH.base_tolerance_amount: '||base_tolerance_amount);
3644:
3637: base_tolerance_amount := convert_to_base_curr(CE_AUTO_BANK_MATCH.tolerance_amount);
3638: base_csl_amount := convert_to_base_curr(CE_AUTO_BANK_MATCH.calc_csl_amount);
3639:
3640: cep_standard.debug('CE_AUTO_BANK_MATCH.calc_csl_amount: '||calc_csl_amount);
3641: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_base_amount: '||trx_base_amount);
3642: cep_standard.debug('CE_AUTO_BANK_MATCH.base_csl_amount: '||base_csl_amount);
3643: cep_standard.debug('CE_AUTO_BANK_MATCH.base_tolerance_amount: '||base_tolerance_amount);
3644:
3645: IF (CE_AUTO_BANK_MATCH.trx_base_amount NOT BETWEEN
3638: base_csl_amount := convert_to_base_curr(CE_AUTO_BANK_MATCH.calc_csl_amount);
3639:
3640: cep_standard.debug('CE_AUTO_BANK_MATCH.calc_csl_amount: '||calc_csl_amount);
3641: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_base_amount: '||trx_base_amount);
3642: cep_standard.debug('CE_AUTO_BANK_MATCH.base_csl_amount: '||base_csl_amount);
3643: cep_standard.debug('CE_AUTO_BANK_MATCH.base_tolerance_amount: '||base_tolerance_amount);
3644:
3645: IF (CE_AUTO_BANK_MATCH.trx_base_amount NOT BETWEEN
3646: (base_csl_amount - base_tolerance_amount)
3639:
3640: cep_standard.debug('CE_AUTO_BANK_MATCH.calc_csl_amount: '||calc_csl_amount);
3641: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_base_amount: '||trx_base_amount);
3642: cep_standard.debug('CE_AUTO_BANK_MATCH.base_csl_amount: '||base_csl_amount);
3643: cep_standard.debug('CE_AUTO_BANK_MATCH.base_tolerance_amount: '||base_tolerance_amount);
3644:
3645: IF (CE_AUTO_BANK_MATCH.trx_base_amount NOT BETWEEN
3646: (base_csl_amount - base_tolerance_amount)
3647: AND (base_csl_amount + base_tolerance_amount))
3645: IF (CE_AUTO_BANK_MATCH.trx_base_amount NOT BETWEEN
3646: (base_csl_amount - base_tolerance_amount)
3647: AND (base_csl_amount + base_tolerance_amount))
3648: THEN
3649: cep_standard.debug('ERROR#05: CE_XCH_AMOUNT');
3650: valid_trx := FALSE;
3651: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3652: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3653: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3655: END IF;
3656: END IF;
3657:
3658: IF valid_trx THEN
3659: cep_standard.debug('Validated transaction amount - no errors');
3660: END IF;
3661:
3662: cep_standard.debug('DEBUG#6.5- calc_csl_amount = ' || calc_csl_amount);
3663: -- Remittance batches with amount differences try to create misc receipts
3658: IF valid_trx THEN
3659: cep_standard.debug('Validated transaction amount - no errors');
3660: END IF;
3661:
3662: cep_standard.debug('DEBUG#6.5- calc_csl_amount = ' || calc_csl_amount);
3663: -- Remittance batches with amount differences try to create misc receipts
3664: -- we need to validate the payment method
3665: IF (CE_AUTO_BANK_MATCH.trx_amount <> CE_AUTO_BANK_MATCH.calc_csl_amount AND
3666: CE_AUTO_BANK_MATCH.csl_match_type = 'RBATCH')
3664: -- we need to validate the payment method
3665: IF (CE_AUTO_BANK_MATCH.trx_amount <> CE_AUTO_BANK_MATCH.calc_csl_amount AND
3666: CE_AUTO_BANK_MATCH.csl_match_type = 'RBATCH')
3667: THEN
3668: cep_standard.debug('checking for valid payment method');
3669: IF NOT (validate_payment_method)
3670: THEN
3671: cep_standard.debug('ERROR#06: CE_PAYMENT_METHOD');
3672: valid_trx := FALSE;
3667: THEN
3668: cep_standard.debug('checking for valid payment method');
3669: IF NOT (validate_payment_method)
3670: THEN
3671: cep_standard.debug('ERROR#06: CE_PAYMENT_METHOD');
3672: valid_trx := FALSE;
3673: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3674: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3675: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3681: -- Make sure the Future Dated Payment has already Matured
3682: -- with status 'NEGOTIABLE' and not 'ISSUED'
3683: IF (CE_AUTO_BANK_MATCH.trx_status = 'ISSUED')
3684: THEN
3685: cep_standard.debug('ERROR#07: CE_CANNOT_RECONCILE_FD_PAYMENT');
3686: valid_trx := FALSE;
3687: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3688: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3689: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3690: 'CE_CANNOT_RECONCILE_FD_PAYMENT');
3691: END IF;
3692:
3693: IF valid_trx THEN
3694: cep_standard.debug('Validated transaction amount Type 1 - no errors');
3695: END IF;
3696:
3697: /* JE_LINE and PAY_LINE validation: */
3698: ELSIF (CE_AUTO_BANK_MATCH.csl_match_type IN ('JE_LINE', 'PAY_LINE', 'PAY_EFT')) THEN
3698: ELSIF (CE_AUTO_BANK_MATCH.csl_match_type IN ('JE_LINE', 'PAY_LINE', 'PAY_EFT')) THEN
3699: IF (CE_AUTO_BANK_MATCH.trx_amount <> CE_AUTO_BANK_MATCH.calc_csl_amount)
3700: THEN
3701: valid_trx := FALSE;
3702: cep_standard.debug('ERROR#08: CE_PMT_AMOUNT');
3703: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3704: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3705: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3706: 'CE_PMT_AMOUNT');
3705: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3706: 'CE_PMT_AMOUNT');
3707: END IF;
3708: IF valid_trx THEN
3709: cep_standard.debug('Validated transaction amount Type 2 - no errors');
3710: END IF;
3711: END IF;
3712:
3713: -------------------------------------------------------------------------------
3712:
3713: -------------------------------------------------------------------------------
3714: -- Validate GL date #5
3715: -- 7571492 : Added PGROUP for csl_match_type
3716: cep_standard.debug('Validate GL date');
3717: IF CE_AUTO_BANK_MATCH.csl_match_type IN
3718: ('PAYMENT','CASH','MISC','PBATCH','RBATCH','CMISC','RECEIPT','PGROUP') --bug 4435028
3719: AND NVL(CE_AUTO_BANK_MATCH.csl_reconcile_flag, 'X') <> 'OI'
3720: THEN
3722: IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW')
3723: THEN
3724: IF(NOT(CE_AUTO_BANK_REC.find_gl_period(CE_AUTO_BANK_REC.G_gl_date, 101)))
3725: THEN
3726: cep_standard.debug('ERROR#09: CE_INVALID_GL_PERIOD');
3727: valid_trx:=FALSE;
3728: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3729: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3730: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3735: ELSIF (CE_AUTO_BANK_MATCH.csl_match_type IN ('PBATCH','PAYMENT','PGROUP'))
3736: THEN
3737: IF(NOT(CE_AUTO_BANK_REC.find_gl_period(CE_AUTO_BANK_REC.G_gl_date, 200)))
3738: THEN
3739: cep_standard.debug('ERROR#10: CE_INVALID_AP_PERIOD');
3740: valid_trx:=FALSE;
3741: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3742: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3743: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3748: IF CE_AUTO_BANK_MATCH.csl_match_type in ('PAYMENT', 'PBATCH','PGROUP')
3749: AND TRUNC(CE_AUTO_BANK_MATCH.trx_date) > TRUNC(CE_AUTO_BANK_MATCH.csl_trx_date)
3750: THEN
3751: CE_AUTO_BANK_MATCH.trx_clr_flag := 'Y';
3752: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_clr_flag='||CE_AUTO_BANK_MATCH.trx_clr_flag);
3753: END IF;
3754:
3755: ELSIF (NOT(CE_AUTO_BANK_REC.find_gl_period(CE_AUTO_BANK_REC.G_gl_date, 222)))
3756: THEN
3753: END IF;
3754:
3755: ELSIF (NOT(CE_AUTO_BANK_REC.find_gl_period(CE_AUTO_BANK_REC.G_gl_date, 222)))
3756: THEN
3757: cep_standard.debug('ERROR#11: CE_INVALID_AR_PERIOD');
3758: valid_trx:= FALSE;
3759: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3760: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3761: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3762: 'CE_INVALID_AR_PERIOD');
3763: END IF;
3764: END IF;
3765: IF valid_trx THEN
3766: cep_standard.debug('Validated GL date - no errors');
3767: END IF;
3768:
3769: ------------------------------------------------------------------------------------
3770: -- Bug 14725907: Start
3767: END IF;
3768:
3769: ------------------------------------------------------------------------------------
3770: -- Bug 14725907: Start
3771: cep_standard.debug('--> bug 14725907: Check for unclearing date');
3772: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_clearing_trx_type='||CE_AUTO_BANK_MATCH.csl_clearing_trx_type);
3773: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_id='||CE_AUTO_BANK_MATCH.trx_id);
3774:
3775: if ( CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'PAYMENT' )
3768:
3769: ------------------------------------------------------------------------------------
3770: -- Bug 14725907: Start
3771: cep_standard.debug('--> bug 14725907: Check for unclearing date');
3772: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_clearing_trx_type='||CE_AUTO_BANK_MATCH.csl_clearing_trx_type);
3773: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_id='||CE_AUTO_BANK_MATCH.trx_id);
3774:
3775: if ( CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'PAYMENT' )
3776: then
3769: ------------------------------------------------------------------------------------
3770: -- Bug 14725907: Start
3771: cep_standard.debug('--> bug 14725907: Check for unclearing date');
3772: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_clearing_trx_type='||CE_AUTO_BANK_MATCH.csl_clearing_trx_type);
3773: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_id='||CE_AUTO_BANK_MATCH.trx_id);
3774:
3775: if ( CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'PAYMENT' )
3776: then
3777: select max(accounting_date)
3801:
3802: -- if receipt has been cleared get gl date of unclearing event
3803: if (l_cleared_record_exists > 0)
3804: then
3805: cep_standard.debug('receipt has been cleared before');
3806: select gl_date
3807: into l_unclearing_gl_date
3808: from ar_cash_receipt_history
3809: where cash_receipt_history_id = CE_AUTO_BANK_MATCH.trx_id;
3812: end if;
3813:
3814: exception
3815: when no_data_found then
3816: cep_standard.debug('No Clearing/Unclearing Records found');
3817: l_unclearing_gl_date := NULL;
3818: end;
3819:
3820: end if ;
3817: l_unclearing_gl_date := NULL;
3818: end;
3819:
3820: end if ;
3821: cep_standard.debug('l_unclearing_gl_date='||nvl(to_char(l_unclearing_gl_date),'
3823: cep_standard.debug('CE_AUTO_BANK_REC.G_gl_date='||CE_AUTO_BANK_REC.G_gl_date);
3824:
3825: if CE_AUTO_BANK_REC.G_gl_date < trunc(nvl(l_unclearing_gl_date,CE_AUTO_BANK_MATCH.trx_date))
3818: end;
3819:
3820: end if ;
3821: cep_standard.debug('l_unclearing_gl_date='||nvl(to_char(l_unclearing_gl_date),'
3822: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_date='||CE_AUTO_BANK_MATCH.trx_date);
3823: cep_standard.debug('CE_AUTO_BANK_REC.G_gl_date='||CE_AUTO_BANK_REC.G_gl_date);
3824:
3825: if CE_AUTO_BANK_REC.G_gl_date < trunc(nvl(l_unclearing_gl_date,CE_AUTO_BANK_MATCH.trx_date))
3826: then
3819:
3820: end if ;
3821: cep_standard.debug('l_unclearing_gl_date='||nvl(to_char(l_unclearing_gl_date),'
3822: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_date='||CE_AUTO_BANK_MATCH.trx_date);
3823: cep_standard.debug('CE_AUTO_BANK_REC.G_gl_date='||CE_AUTO_BANK_REC.G_gl_date);
3824:
3825: if CE_AUTO_BANK_REC.G_gl_date < trunc(nvl(l_unclearing_gl_date,CE_AUTO_BANK_MATCH.trx_date))
3826: then
3827: cep_standard.debug('ERROR#12: GL Date earlier than unrec/trx date');
3823: cep_standard.debug('CE_AUTO_BANK_REC.G_gl_date='||CE_AUTO_BANK_REC.G_gl_date);
3824:
3825: if CE_AUTO_BANK_REC.G_gl_date < trunc(nvl(l_unclearing_gl_date,CE_AUTO_BANK_MATCH.trx_date))
3826: then
3827: cep_standard.debug('ERROR#12: GL Date earlier than unrec/trx date');
3828: valid_trx:= FALSE;
3829: if l_unclearing_gl_date is null
3830: then
3831: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3841: end if;
3842: -- Bug 14725907: End
3843:
3844: -- Lock the transaction
3845: cep_standard.debug('trx_validation - attempting to lock_transaction');
3846: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type='||CE_AUTO_BANK_MATCH.csl_match_type||
3847: ', CE_AUTO_BANK_MATCH.csl_clearing_trx_type='||CE_AUTO_BANK_MATCH.csl_clearing_trx_type );
3848: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_rowid='||CE_AUTO_BANK_MATCH.trx_rowid||
3849: ', CE_AUTO_BANK_MATCH.trx_amount='||CE_AUTO_BANK_MATCH.trx_amount);
3842: -- Bug 14725907: End
3843:
3844: -- Lock the transaction
3845: cep_standard.debug('trx_validation - attempting to lock_transaction');
3846: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type='||CE_AUTO_BANK_MATCH.csl_match_type||
3847: ', CE_AUTO_BANK_MATCH.csl_clearing_trx_type='||CE_AUTO_BANK_MATCH.csl_clearing_trx_type );
3848: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_rowid='||CE_AUTO_BANK_MATCH.trx_rowid||
3849: ', CE_AUTO_BANK_MATCH.trx_amount='||CE_AUTO_BANK_MATCH.trx_amount);
3850:
3844: -- Lock the transaction
3845: cep_standard.debug('trx_validation - attempting to lock_transaction');
3846: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type='||CE_AUTO_BANK_MATCH.csl_match_type||
3847: ', CE_AUTO_BANK_MATCH.csl_clearing_trx_type='||CE_AUTO_BANK_MATCH.csl_clearing_trx_type );
3848: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_rowid='||CE_AUTO_BANK_MATCH.trx_rowid||
3849: ', CE_AUTO_BANK_MATCH.trx_amount='||CE_AUTO_BANK_MATCH.trx_amount);
3850:
3851: BEGIN
3852: CE_AUTO_BANK_MATCH.lock_transaction(
3857: X_TRX_ROWID => CE_AUTO_BANK_MATCH.trx_rowid,
3858: X_BATCH_BA_AMOUNT => CE_AUTO_BANK_MATCH.trx_amount);
3859: EXCEPTION
3860: WHEN NO_DATA_FOUND THEN
3861: cep_standard.debug('ERROR#12: CE_TRX_RECONCILED');
3862: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3863: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3864: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3865: 'CE_TRX_RECONCILED');
3863: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3864: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3865: 'CE_TRX_RECONCILED');
3866: WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
3867: cep_standard.debug('ERROR#13: CE_TRX_BUSY');
3868: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3869: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3870: CE_AUTO_BANK_MATCH.csl_statement_line_id,
3871: 'CE_TRX_BUSY');
3872: WHEN OTHERS THEN
3873: RAISE;
3874: END;
3875:
3876: cep_standard.debug('<
3878: EXCEPTION
3879: WHEN OTHERS THEN
3880: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.trx_validation' );
3876: cep_standard.debug('<
3878: EXCEPTION
3879: WHEN OTHERS THEN
3880: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.trx_validation' );
3881: RAISE;
3882: END trx_validation;
3883:
3884: /* ---------------------------------------------------------------------
3909: funct_curr_min_acct_unit NUMBER;
3910: /* Bug 2925260 */
3911:
3912: BEGIN
3913: cep_standard.debug('>>CE_AUTO_BANK_MATCH.trx_match');
3914: no_of_matches := 0;
3915: -- bug 5122576 - zba trx in xtr
3916: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP','SWEEP_OUT') AND
3917: (CE_AUTO_BANK_MATCH.csl_reconcile_flag NOT IN ('PAY', 'PAY_EFT')))
3924: THEN
3925: tx_type := 'MISC';
3926: END IF;
3927:
3928: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type=' ||CE_AUTO_BANK_MATCH.csl_trx_type);
3929: cep_standard.debug('tx_type=' ||tx_type);
3930: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag=' ||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
3931: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number=' ||CE_AUTO_BANK_MATCH.csl_bank_trx_number);
3932:
3925: tx_type := 'MISC';
3926: END IF;
3927:
3928: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type=' ||CE_AUTO_BANK_MATCH.csl_trx_type);
3929: cep_standard.debug('tx_type=' ||tx_type);
3930: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag=' ||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
3931: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number=' ||CE_AUTO_BANK_MATCH.csl_bank_trx_number);
3932:
3933: IF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'CE')
3926: END IF;
3927:
3928: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type=' ||CE_AUTO_BANK_MATCH.csl_trx_type);
3929: cep_standard.debug('tx_type=' ||tx_type);
3930: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag=' ||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
3931: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number=' ||CE_AUTO_BANK_MATCH.csl_bank_trx_number);
3932:
3933: IF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'CE')
3934: THEN
3927:
3928: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type=' ||CE_AUTO_BANK_MATCH.csl_trx_type);
3929: cep_standard.debug('tx_type=' ||tx_type);
3930: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag=' ||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
3931: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number=' ||CE_AUTO_BANK_MATCH.csl_bank_trx_number);
3932:
3933: IF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'CE')
3934: THEN
3935: ce_match(no_of_matches);
3993:
3994: ELSIF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'JE')
3995: THEN
3996: -- Bug 11727491 Start
3997: cep_standard.DEBUG('Matching JE Line...');
3998: cep_standard.DEBUG('tx_type = ' || tx_type);
3999: -- Bug 11727491 End
4000:
4001: curr := 2;
3994: ELSIF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'JE')
3995: THEN
3996: -- Bug 11727491 Start
3997: cep_standard.DEBUG('Matching JE Line...');
3998: cep_standard.DEBUG('tx_type = ' || tx_type);
3999: -- Bug 11727491 End
4000:
4001: curr := 2;
4002: SELECT
4050:
4051: ELSIF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'OI')
4052: THEN
4053:
4054: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
4055: curr := 3;
4056:
4057: IF (CE_AUTO_BANK_REC.G_open_interface_matching_code = 'T')
4058: THEN
4058: THEN
4059: IF ((CE_AUTO_BANK_REC.G_legal_entity_id is not null) OR
4060: (CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag = 'Y'))
4061: THEN
4062: cep_standard.debug('use ce_185_transactions_v');
4063:
4064: BEGIN ---- Bug 12699623
4065:
4066: SELECT
4119:
4120: -- Bug 12699623 Start
4121: EXCEPTION
4122: WHEN NO_DATA_FOUND THEN
4123: cep_standard.DEBUG('NO DATA FOUND in ce_185_transactions_v..');
4124: NULL;
4125: WHEN OTHERS THEN
4126: cep_standard.DEBUG('Exception in seraching ce_185_transactions_v..');
4127: RAISE;
4122: WHEN NO_DATA_FOUND THEN
4123: cep_standard.DEBUG('NO DATA FOUND in ce_185_transactions_v..');
4124: NULL;
4125: WHEN OTHERS THEN
4126: cep_standard.DEBUG('Exception in seraching ce_185_transactions_v..');
4127: RAISE;
4128: END;
4129: END IF;
4130:
4130:
4131: -- If above treasury query is not able to find a matching transaction, find in external data through CE_999 objects
4132: IF (CE_AUTO_BANK_MATCH.trx_id IS NULL)
4133: THEN -- Bug 12699623 End
4134: cep_standard.debug('use ce_999_transactions_v ' );
4135: SELECT
4136: catv.trx_id,
4137: catv.cash_receipt_id,
4138: catv.row_id,
4182: AND catv.application_id = 999
4183: AND NVL(catv.reconciled_status_flag, 'N') = 'N';
4184: END IF;
4185: ELSE -- match by DATE and AMOUNT
4186: cep_standard.debug('match OI by DATE and AMOUNT ' );
4187: -- bug 5122576 issue with zba trx in xtr
4188: IF (CE_AUTO_BANK_MATCH.csl_trx_type in ('SWEEP_IN', 'CREDIT'))
4189: THEN
4190: bank_charges := -NVL(CE_AUTO_BANK_MATCH.csl_charges_amount,0);
4200: min_unit);
4201:
4202: IF (tx_curr = CE_AUTO_BANK_MATCH.aba_bank_currency)
4203: THEN
4204: cep_standard.debug('OI: bank currency match');
4205: amount_to_match := round(stmt_amount, precision);
4206: cep_standard.debug('tx_type='||tx_type ||', amount_to_match =' || amount_to_match);
4207: match_oi_trx(
4208: tx_type,
4202: IF (tx_curr = CE_AUTO_BANK_MATCH.aba_bank_currency)
4203: THEN
4204: cep_standard.debug('OI: bank currency match');
4205: amount_to_match := round(stmt_amount, precision);
4206: cep_standard.debug('tx_type='||tx_type ||', amount_to_match =' || amount_to_match);
4207: match_oi_trx(
4208: tx_type,
4209: tx_curr,
4210: amount_to_match,
4227: 'CE_REQUIRED_EXCHANGE_FIELD');
4228: END IF;
4229: RAISE NO_DATA_FOUND;
4230: ELSE
4231: cep_standard.debug('OI: foreign currency match');
4232: amount_to_match := round(stmt_amount/CE_AUTO_BANK_MATCH.csl_exchange_rate, precision);
4233: cep_standard.debug('tx_type='||tx_type ||', amount_to_match =' || amount_to_match);
4234: match_oi_trx(
4235: tx_type,
4229: RAISE NO_DATA_FOUND;
4230: ELSE
4231: cep_standard.debug('OI: foreign currency match');
4232: amount_to_match := round(stmt_amount/CE_AUTO_BANK_MATCH.csl_exchange_rate, precision);
4233: cep_standard.debug('tx_type='||tx_type ||', amount_to_match =' || amount_to_match);
4234: match_oi_trx(
4235: tx_type,
4236: tx_curr,
4237: amount_to_match,
4238: precision,
4239: no_of_matches);
4240: END IF;
4241: ELSE -- csl_charges_amount = 0
4242: cep_standard.debug('OI: No csl_charges match');
4243: amount_to_match := round(CE_AUTO_BANK_MATCH.csl_original_amount,precision);
4244: cep_standard.debug('tx_type='||tx_type ||', amount_to_match =' || amount_to_match);
4245: match_oi_trx(
4246: tx_type,
4240: END IF;
4241: ELSE -- csl_charges_amount = 0
4242: cep_standard.debug('OI: No csl_charges match');
4243: amount_to_match := round(CE_AUTO_BANK_MATCH.csl_original_amount,precision);
4244: cep_standard.debug('tx_type='||tx_type ||', amount_to_match =' || amount_to_match);
4245: match_oi_trx(
4246: tx_type,
4247: tx_curr,
4248: amount_to_match,
4262: 'CE_REQUIRED_EXCHANGE_FIELD');
4263: END IF;
4264: RAISE NO_DATA_FOUND;
4265: ELSE
4266: cep_standard.debug('OI: No csl_original_amount match');
4267: amount_to_match := round(stmt_amount/CE_AUTO_BANK_MATCH.csl_exchange_rate, precision);
4268: cep_standard.debug('tx_type='||tx_type ||', amount_to_match =' || amount_to_match);
4269: match_oi_trx(
4270: tx_type,
4264: RAISE NO_DATA_FOUND;
4265: ELSE
4266: cep_standard.debug('OI: No csl_original_amount match');
4267: amount_to_match := round(stmt_amount/CE_AUTO_BANK_MATCH.csl_exchange_rate, precision);
4268: cep_standard.debug('tx_type='||tx_type ||', amount_to_match =' || amount_to_match);
4269: match_oi_trx(
4270: tx_type,
4271: tx_curr,
4272: amount_to_match,
4435: ELSIF ((tx_type = 'PAYMENT') AND (CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag = 'Y'))
4436: THEN
4437: curr := 6;
4438:
4439: cep_standard.DEBUG('G_show_void_payment_flag = ' || CE_AUTO_BANK_REC.G_show_void_payment_flag); -- Bug 16301520 Added debug
4440: numeric_result_trx_num :=
4441: ce_check_numeric(
4442: CE_AUTO_BANK_MATCH.csl_bank_trx_number,
4443: 1,
4641:
4642: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency) AND
4643: (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_MATCH.trx_currency_code)
4644: THEN
4645: cep_standard.debug('Forex account not using the same curr as bk');
4646: curr := 9;
4647: RAISE NO_DATA_FOUND;
4648: END IF;
4649:
4646: curr := 9;
4647: RAISE NO_DATA_FOUND;
4648: END IF;
4649:
4650: cep_standard.debug('<
4652: EXCEPTION
4653: WHEN NO_DATA_FOUND THEN
4654: cep_standard.debug('EXCEPTION: No data found in trx_match');
4650: cep_standard.debug('<
4652: EXCEPTION
4653: WHEN NO_DATA_FOUND THEN
4654: cep_standard.debug('EXCEPTION: No data found in trx_match');
4655: IF (curr = 7)
4656: THEN
4657: CE_RECONCILIATION_ERRORS_PKG.insert_row(
4658: CE_AUTO_BANK_MATCH.csh_statement_header_id,
4675: IF (CE_AUTO_BANK_MATCH.csl_trx_type in('CREDIT','MISC_CREDIT','MISC_DEBIT'))
4676: THEN
4677: IF (CE_AUTO_BANK_MATCH.trx_status = 'REVERSED')
4678: THEN -- 9092830 removed NVL
4679: cep_standard.debug('CE_ABR_NO_ARL: receipt with reversed status');
4680: CE_RECONCILIATION_ERRORS_PKG.insert_row(
4681: CE_AUTO_BANK_MATCH.csh_statement_header_id,
4682: CE_AUTO_BANK_MATCH.csl_statement_line_id,
4683: 'CE_STATEMENT_REVERSAL_NSF');
4682: CE_AUTO_BANK_MATCH.csl_statement_line_id,
4683: 'CE_STATEMENT_REVERSAL_NSF');
4684: ELSIF (CE_AUTO_BANK_MATCH.trx_status) NOT IN ('REMITTED', 'CLEARED', 'RISK_ELIMINATED')
4685: THEN -- 9092830 removed NVL
4686: cep_standard.debug('CE_ABR_NO_ARL: receipt with wrong status');
4687: CE_RECONCILIATION_ERRORS_PKG.insert_row(
4688: CE_AUTO_BANK_MATCH.csh_statement_header_id,
4689: CE_AUTO_BANK_MATCH.csl_statement_line_id,
4690: 'CE_ABR_BAD_ARL');
4696: END IF;
4697: ELSE -- NSF, REJECTED
4698: IF (NVL(CE_AUTO_BANK_MATCH.trx_status,'X') NOT IN ('REMITTED', 'CLEARED', 'RISK_ELIMINATED','REVERSED'))
4699: THEN -- Bug 8310127 Added NVL
4700: cep_standard.debug('>>NSF/REJECTED with wrong status');
4701: CE_RECONCILIATION_ERRORS_PKG.insert_row(
4702: CE_AUTO_BANK_MATCH.csh_statement_header_id,
4703: CE_AUTO_BANK_MATCH.csl_statement_line_id,
4704: 'CE_ABR_BAD_NSF');
4730: no_of_matches := 0;
4731: WHEN OTHERS THEN
4732: IF (SQL%ROWCOUNT > 0)
4733: THEN
4734: cep_standard.debug('EXCEPTION: More than one transaction match this line' );
4735: cep_standard.debug('SQL%ROWCOUNT='||SQL%ROWCOUNT);
4736: IF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'JE')
4737: THEN
4738: CE_RECONCILIATION_ERRORS_PKG.insert_row(
4731: WHEN OTHERS THEN
4732: IF (SQL%ROWCOUNT > 0)
4733: THEN
4734: cep_standard.debug('EXCEPTION: More than one transaction match this line' );
4735: cep_standard.debug('SQL%ROWCOUNT='||SQL%ROWCOUNT);
4736: IF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'JE')
4737: THEN
4738: CE_RECONCILIATION_ERRORS_PKG.insert_row(
4739: CE_AUTO_BANK_MATCH.csh_statement_header_id,
4763: 'CE_ABR_ART_PARTIAL');
4764: END IF;
4765: no_of_matches:=999;
4766: ELSE
4767: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.trx_match' );
4768: RAISE;
4769: END IF;
4770: END trx_match;
4771:
5716: loc_cash_receipt_id AR_CASH_RECEIPT_HISTORY_ALL.cash_receipt_id%TYPE;
5717: loc_trx_date DATE;
5718:
5719: BEGIN
5720: cep_standard.debug('>>CE_AUTO_BANK_MATCH.stmtline_match');
5721: no_of_matches := 0;
5722:
5723: /* Calculate calc_tolerance_amount. */
5724:
5804: END IF;
5805: */
5806:
5807: --------------------------------------------------------------------------------
5808: cep_standard.debug('DEBUG: calc_tolerance_amount = '|| calc_tolerance_amount);
5809: cep_standard.debug('DEBUG: calc_tolerance_amount_ap = '|| calc_tolerance_amount_ap);
5810: cep_standard.debug('DEBUG: calc_tolerance_amount_ar = '|| calc_tolerance_amount_ar);
5811: cep_standard.debug('DEBUG: csl_correction_method = '|| csl_correction_method);
5812:
5805: */
5806:
5807: --------------------------------------------------------------------------------
5808: cep_standard.debug('DEBUG: calc_tolerance_amount = '|| calc_tolerance_amount);
5809: cep_standard.debug('DEBUG: calc_tolerance_amount_ap = '|| calc_tolerance_amount_ap);
5810: cep_standard.debug('DEBUG: calc_tolerance_amount_ar = '|| calc_tolerance_amount_ar);
5811: cep_standard.debug('DEBUG: csl_correction_method = '|| csl_correction_method);
5812:
5813: if (CE_AUTO_BANK_MATCH.csl_correction_method in ('REVERSAL', 'BOTH')) then
5806:
5807: --------------------------------------------------------------------------------
5808: cep_standard.debug('DEBUG: calc_tolerance_amount = '|| calc_tolerance_amount);
5809: cep_standard.debug('DEBUG: calc_tolerance_amount_ap = '|| calc_tolerance_amount_ap);
5810: cep_standard.debug('DEBUG: calc_tolerance_amount_ar = '|| calc_tolerance_amount_ar);
5811: cep_standard.debug('DEBUG: csl_correction_method = '|| csl_correction_method);
5812:
5813: if (CE_AUTO_BANK_MATCH.csl_correction_method in ('REVERSAL', 'BOTH')) then
5814: OPEN get_reversal;
5807: --------------------------------------------------------------------------------
5808: cep_standard.debug('DEBUG: calc_tolerance_amount = '|| calc_tolerance_amount);
5809: cep_standard.debug('DEBUG: calc_tolerance_amount_ap = '|| calc_tolerance_amount_ap);
5810: cep_standard.debug('DEBUG: calc_tolerance_amount_ar = '|| calc_tolerance_amount_ar);
5811: cep_standard.debug('DEBUG: csl_correction_method = '|| csl_correction_method);
5812:
5813: if (CE_AUTO_BANK_MATCH.csl_correction_method in ('REVERSAL', 'BOTH')) then
5814: OPEN get_reversal;
5815: FETCH get_reversal
5832: CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
5833: CE_AUTO_BANK_MATCH.gt_seq_id;
5834: CLOSE get_reversal;
5835:
5836: cep_standard.debug('DEBUG: get_reversal no_of_matches = '|| no_of_matches);
5837: if (no_of_matches = 1) then
5838: CE_AUTO_BANK_MATCH.csl_match_correction_type := 'REVERSAL';
5839: CE_AUTO_BANK_MATCH.corr_csl_amount := 0;
5840: CE_AUTO_BANK_MATCH.calc_csl_amount := 0;
5842: end if;
5843:
5844: if (CE_AUTO_BANK_MATCH.csl_correction_method in ('ADJUSTMENT', 'BOTH') AND
5845: no_of_matches <> 1) then
5846: cep_standard.debug('DEBUG: get_adjustment calc_tolerance_amount = '
5847: || calc_tolerance_amount);
5848: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csh_bank_account_id = '
5849: || CE_AUTO_BANK_MATCH.csh_bank_account_id);
5850: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_bank_trx_number = '
5844: if (CE_AUTO_BANK_MATCH.csl_correction_method in ('ADJUSTMENT', 'BOTH') AND
5845: no_of_matches <> 1) then
5846: cep_standard.debug('DEBUG: get_adjustment calc_tolerance_amount = '
5847: || calc_tolerance_amount);
5848: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csh_bank_account_id = '
5849: || CE_AUTO_BANK_MATCH.csh_bank_account_id);
5850: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_bank_trx_number = '
5851: || CE_AUTO_BANK_MATCH.csl_bank_trx_number);
5852: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_trx_date = '
5846: cep_standard.debug('DEBUG: get_adjustment calc_tolerance_amount = '
5847: || calc_tolerance_amount);
5848: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csh_bank_account_id = '
5849: || CE_AUTO_BANK_MATCH.csh_bank_account_id);
5850: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_bank_trx_number = '
5851: || CE_AUTO_BANK_MATCH.csl_bank_trx_number);
5852: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_trx_date = '
5853: || CE_AUTO_BANK_MATCH.csl_trx_date);
5854: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_statement_line_id = '
5848: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csh_bank_account_id = '
5849: || CE_AUTO_BANK_MATCH.csh_bank_account_id);
5850: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_bank_trx_number = '
5851: || CE_AUTO_BANK_MATCH.csl_bank_trx_number);
5852: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_trx_date = '
5853: || CE_AUTO_BANK_MATCH.csl_trx_date);
5854: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_statement_line_id = '
5855: || CE_AUTO_BANK_MATCH.csl_statement_line_id);
5856: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.calc_csl_amount = '
5850: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_bank_trx_number = '
5851: || CE_AUTO_BANK_MATCH.csl_bank_trx_number);
5852: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_trx_date = '
5853: || CE_AUTO_BANK_MATCH.csl_trx_date);
5854: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_statement_line_id = '
5855: || CE_AUTO_BANK_MATCH.csl_statement_line_id);
5856: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.calc_csl_amount = '
5857: || CE_AUTO_BANK_MATCH.calc_csl_amount);
5858:
5852: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_trx_date = '
5853: || CE_AUTO_BANK_MATCH.csl_trx_date);
5854: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_statement_line_id = '
5855: || CE_AUTO_BANK_MATCH.csl_statement_line_id);
5856: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.calc_csl_amount = '
5857: || CE_AUTO_BANK_MATCH.calc_csl_amount);
5858:
5859:
5860: -- No transaction match if the sum of statement line amount is $0.
5900: CE_AUTO_BANK_MATCH.trx_bank_acct_use_id,
5901: CE_AUTO_BANK_MATCH.gt_seq_id;
5902: CLOSE get_adjustment;
5903:
5904: cep_standard.debug('DEBUG: get_adjustment no_of_matches = '||no_of_matches);
5905: if (no_of_matches = 1) then
5906: CE_AUTO_BANK_MATCH.csl_match_correction_type := 'ADJUSTMENT';
5907: if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_CREDIT') then
5908: if (CE_AUTO_BANK_MATCH.trx_type2 in ('CREDIT', 'MISC_CREDIT')) then
5920: CE_AUTO_BANK_MATCH.corr_csl_amount := CE_AUTO_BANK_MATCH.csl_amount
5921: - CE_AUTO_BANK_MATCH.trx_amount2;
5922: end if;
5923: end if;
5924: cep_standard.debug('corr_csl_amount: '||corr_csl_amount);
5925: CE_AUTO_BANK_MATCH.calc_csl_amount := CE_AUTO_BANK_MATCH.corr_csl_amount;
5926: end if;
5927: end if;
5928:
5978: CE_AUTO_BANK_MATCH.trx_bank_acct_use_id;
5979: CLOSE get_rev_debit;
5980: end if;
5981:
5982: cep_standard.debug('DEBUG: get_rev_xx no_of_matches = '|| no_of_matches);
5983: if (no_of_matches = 1) then
5984:
5985: -- bug 4914608 set the org after a match for AR/AP
5986: set_single_org(CE_AUTO_BANK_MATCH.trx_org_id);
6133: CE_AUTO_BANK_MATCH.trx_bank_acct_use_id;
6134: CLOSE get_recon_adj_pay;
6135: end if;
6136:
6137: cep_standard.debug('DEBUG: get_reconciled_adj no_of_matches = '||
6138: no_of_matches);
6139: if (no_of_matches = 1) then
6140: CE_AUTO_BANK_MATCH.reconciled_this_run := 'Y';
6141: else
6226: CE_AUTO_BANK_MATCH.gt_seq_id;
6227: CLOSE get_recon_adj2_ap;
6228: end if;
6229:
6230: cep_standard.debug('DEBUG: get_reconciled_adj2 no_of_matches = '||
6231: no_of_matches);
6232: if (no_of_matches = 1) then
6233:
6234: -- bug 4914608 set the org after a match for AR/AP
6312: raise NO_DATA_FOUND;
6313: elsif (no_of_matches > 1) then
6314: raise TOO_MANY_ROWS;
6315: end if;
6316: cep_standard.debug('<
6318: EXCEPTION
6319: WHEN NO_DATA_FOUND THEN
6320: CE_RECONCILIATION_ERRORS_PKG.insert_row(
6321: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6322: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_STMTL');
6323: no_of_matches:=0;
6324: WHEN TOO_MANY_ROWS THEN
6325: cep_standard.debug('EXCEPTION: More than one statement line match this receipt' );
6326: CE_RECONCILIATION_ERRORS_PKG.insert_row(
6327: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6328: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_STMT_PARTIAL');
6329: no_of_matches:=999;
6327: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6328: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_STMT_PARTIAL');
6329: no_of_matches:=999;
6330: WHEN OTHERS THEN
6331: cep_standard.debug('SQLCODE = '|| sqlcode);
6332: cep_standard.debug('SQLERRM = '|| sqlerrm);
6333: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.stmtline_match' );
6334: RAISE;
6335:
6328: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_STMT_PARTIAL');
6329: no_of_matches:=999;
6330: WHEN OTHERS THEN
6331: cep_standard.debug('SQLCODE = '|| sqlcode);
6332: cep_standard.debug('SQLERRM = '|| sqlerrm);
6333: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.stmtline_match' );
6334: RAISE;
6335:
6336: END stmtline_match;
6329: no_of_matches:=999;
6330: WHEN OTHERS THEN
6331: cep_standard.debug('SQLCODE = '|| sqlcode);
6332: cep_standard.debug('SQLERRM = '|| sqlerrm);
6333: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.stmtline_match' );
6334: RAISE;
6335:
6336: END stmtline_match;
6337:
6355: no_of_currencies NUMBER;
6356: -- 16169940: Removed unused cursor COUNT_DUP_INVOICE and related
6357: -- variables
6358: BEGIN
6359: cep_standard.debug('>>CE_AUTO_BANK_MATCH.match_line');
6360: IF (call_mode = 'T') THEN
6361: trx_match(no_of_matches);
6362: ELSIF (call_mode = 'B') THEN
6363: batch_match(no_of_matches, no_of_currencies);
6370: ELSIF (call_mode = 'S') THEN
6371: stmtline_match(no_of_matches);
6372: END IF;
6373:
6374: cep_standard.debug('no_of_matches = '|| no_of_matches);
6375: cep_standard.debug('no_of_currencies = '|| no_of_currencies);
6376:
6377: IF (no_of_matches = 1)
6378: THEN
6371: stmtline_match(no_of_matches);
6372: END IF;
6373:
6374: cep_standard.debug('no_of_matches = '|| no_of_matches);
6375: cep_standard.debug('no_of_currencies = '|| no_of_currencies);
6376:
6377: IF (no_of_matches = 1)
6378: THEN
6379: IF (trx_validation(no_of_currencies))
6387: CE_AUTO_BANK_MATCH.csl_match_found := 'ERROR';
6388: ELSE
6389: CE_AUTO_BANK_MATCH.csl_match_found := 'NONE';
6390: END IF;
6391: cep_standard.debug('DEBUG #5 - csl_match_found = '|| csl_match_found);
6392: cep_standard.debug('<
6394: WHEN OTHERS THEN
6395: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.match_line' );
6388: ELSE
6389: CE_AUTO_BANK_MATCH.csl_match_found := 'NONE';
6390: END IF;
6391: cep_standard.debug('DEBUG #5 - csl_match_found = '|| csl_match_found);
6392: cep_standard.debug('<
6394: WHEN OTHERS THEN
6395: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.match_line' );
6396: RAISE;
6391: cep_standard.debug('DEBUG #5 - csl_match_found = '|| csl_match_found);
6392: cep_standard.debug('<
6394: WHEN OTHERS THEN
6395: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.match_line' );
6396: RAISE;
6397: END match_line;
6398:
6399: /* ---------------------------------------------------------------------|
6438: accounting_method_found NUMBER := 0;
6439: current_org_id NUMBER;
6440: receivables_trx_org_id NUMBER;
6441: BEGIN
6442: cep_standard.debug('>>CE_AUTO_BANK_MATCH.match_engine');
6443:
6444: CE_AUTO_BANK_MATCH.csl_match_correction_type := 'NONE';
6445: CE_AUTO_BANK_MATCH.reconciled_this_run := NULL;
6446: CE_AUTO_BANK_MATCH.reconcile_to_statement_flag := NULL;
6480: AND (CE_AUTO_BANK_MATCH.csl_trx_type NOT IN ('MISC_CREDIT', 'MISC_DEBIT')
6481: OR (CE_AUTO_BANK_MATCH.csl_trx_type IN ('MISC_CREDIT', 'MISC_DEBIT')
6482: AND NVL(CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag,'N') = 'N')))
6483: THEN
6484: cep_standard.debug('Required field(s) for matching not available at statement line');
6485: RAISE NO_DATA_FOUND;
6486:
6487: ELSIF ( trx_currency_type = 'FOREIGN'
6488: AND CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency)
6488: AND CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency)
6489: OR( trx_currency_type = 'FUNCTIONAL'
6490: AND CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency)
6491: THEN
6492: cep_standard.debug('Currency mismatch: CE_NO_FOREIGN_RECON');
6493: CE_RECONCILIATION_ERRORS_PKG.insert_row(
6494: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6495: CE_AUTO_BANK_MATCH.csl_statement_line_id,
6496: 'CE_NO_FOREIGN_RECON');
6536: ELSE
6537: --
6538: -- Prod16 NEW, ability to match by invoice number also for AP
6539: --
6540: cep_standard.debug('Looking for AP/AR trx');
6541:
6542: IF (CE_AUTO_BANK_MATCH.csl_bank_account_text IS NOT NULL AND
6543: CE_AUTO_BANK_MATCH.csl_invoice_text IS NOT NULL)
6544: THEN
6577: Tertiary_match := 'G';
6578: ELSIF (primary_match = 'B') and (secondary_match = 'G') THEN
6579: Tertiary_match := 'T';
6580: END IF;
6581: cep_standard.debug('AP primary_match -'||primary_match||' secondary_match -'||secondary_match||' Tertiary_match -'||Tertiary_match);
6582: ELSE
6583: primary_match := NVL(CE_AUTO_BANK_REC.G_ar_matching_order,'T');
6584: IF (primary_match = 'T') THEN
6585: secondary_match := 'B';
6590: --FOR SEPA ER 6700007 END
6591: END IF;
6592: END IF;
6593:
6594: cep_standard.debug('EXECUTING primary_match ');
6595: match_line(primary_match);
6596:
6597: IF (CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL')
6598: AND nvl(CE_AUTO_BANK_MATCH.csl_reconcile_flag,'NONE')
6598: AND nvl(CE_AUTO_BANK_MATCH.csl_reconcile_flag,'NONE')
6599: NOT IN ('JE', 'OI', 'PAY', 'PAY_EFT', 'CE')
6600: AND secondary_match IS NOT NULL)
6601: THEN
6602: cep_standard.debug('EXECUTING secondary_match ');
6603: match_line(secondary_match);
6604:
6605: -- Bug 9434957 Start
6606: -- If Secondary Match Based on Invoice Number and Supplier Bank Account Number Fetches no Matches Then
6609: CE_AUTO_BANK_MATCH.csl_customer_text IS NOT NULL AND
6610: secondary_match <> 'I' AND
6611: CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL'))
6612: THEN
6613: cep_standard.debug('EXECUTING secondary_match For Invoice');
6614: match_line('I');
6615: END IF;
6616: -- Bug 9434957 End
6617:
6620: (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT', 'STOP')
6621: OR Tertiary_batch_match = TRUE ) AND -- Bug 9577381 Added OR Clause
6622: Tertiary_match IS NOT NULL)
6623: THEN
6624: cep_standard.debug('EXECUTING Tertiary_match ');
6625: match_line(Tertiary_match);
6626: END IF;
6627: --FOR SEPA ER 6700007 END
6628: END IF;
6627: --FOR SEPA ER 6700007 END
6628: END IF;
6629:
6630: -- bug 4914608 set the org after a match for AR/AP
6631: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_org_id =' ||CE_AUTO_BANK_MATCH.trx_org_id);
6632: IF (CE_AUTO_BANK_MATCH.csl_match_found = 'FULL')
6633: THEN
6634: set_single_org(CE_AUTO_BANK_MATCH.trx_org_id);
6635: END IF;
6649: WHERE s.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,CE_AUTO_BANK_MATCH.trx_org_id);
6650: ELSE
6651: CE_AUTO_BANK_MATCH.ar_accounting_method := NULL;
6652: END IF;
6653: cep_standard.debug('CE_AUTO_BANK_MATCH.ar_accounting_method =' ||CE_AUTO_BANK_MATCH.ar_accounting_method);
6654:
6655: --
6656: -- Bug 928060: Create a misc receipt for NSF line with tolerance.
6657: --
6654:
6655: --
6656: -- Bug 928060: Create a misc receipt for NSF line with tolerance.
6657: --
6658: cep_standard.debug('Creating a misc receipt for NSF line with tolerance');
6659: IF (CE_AUTO_BANK_MATCH.csl_match_found = 'FULL' AND
6660: CE_AUTO_BANK_MATCH.csl_trx_type = 'NSF' AND
6661: (CE_AUTO_BANK_MATCH.trx_amount <> CE_AUTO_BANK_MATCH.calc_csl_amount))
6662: THEN
6671: AND application_id = 222
6672: AND reconciled_status_flag = 'N';
6673: EXCEPTION
6674: WHEN NO_DATA_FOUND THEN
6675: cep_standard.debug('No misc receipt found for NSF');
6676: misc_exists := 'N';
6677: WHEN OTHERS THEN
6678: cep_standard.debug('Exception finding misc receipt found for NSF');
6679: misc_exists := 'N';
6674: WHEN NO_DATA_FOUND THEN
6675: cep_standard.debug('No misc receipt found for NSF');
6676: misc_exists := 'N';
6677: WHEN OTHERS THEN
6678: cep_standard.debug('Exception finding misc receipt found for NSF');
6679: misc_exists := 'N';
6680: END;
6681:
6682: IF (misc_exists = 'N')
6686: OR
6687: NVL(CE_AUTO_BANK_MATCH.csl_receivables_trx_id,
6688: CE_AUTO_BANK_REC.G_receivables_trx_id) IS NULL)
6689: THEN
6690: cep_standard.debug('No receipt method or receivable activity info.');
6691: CE_AUTO_BANK_MATCH.nsf_info_flag := 'Y';
6692: ELSE
6693: cep_standard.debug('Create a misc receipt for NSF line with tolerance.');
6694: DECLARE
6689: THEN
6690: cep_standard.debug('No receipt method or receivable activity info.');
6691: CE_AUTO_BANK_MATCH.nsf_info_flag := 'Y';
6692: ELSE
6693: cep_standard.debug('Create a misc receipt for NSF line with tolerance.');
6694: DECLARE
6695: p_cr_id ar_cash_receipts.cash_receipt_id%TYPE;
6696: BEGIN
6697: --
6707: CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL))
6708: THEN
6709: IF (NOT validate_exchange_details)
6710: THEN
6711: cep_standard.debug('Error in validate_exchange_details');
6712: END IF;
6713: END IF;
6714:
6715: --
6748: CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
6749: (CE_AUTO_BANK_MATCH.trx_amount-CE_AUTO_BANK_MATCH.calc_csl_amount));
6750: EXCEPTION
6751: WHEN OTHERS THEN
6752: cep_standard.debug('EXCEPTION: Could not convert amount');
6753: receipt_amount := NULL;
6754: END;
6755:
6756: base_receipt_amount := convert_to_base_curr(receipt_amount);
6783: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6784: CE_AUTO_BANK_MATCH.csl_statement_line_id,
6785: 'CE_PAYMENT_METHOD');
6786: ELSE
6787: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_receivables_trx_id= '||
6788: CE_AUTO_BANK_MATCH.csl_receivables_trx_id);
6789:
6790: -- bug 5185358 not able to create misc receipt
6791: get_receivables_org_id(receivables_trx_org_id);
6790: -- bug 5185358 not able to create misc receipt
6791: get_receivables_org_id(receivables_trx_org_id);
6792: CE_AUTO_BANK_MATCH.trx_org_id := receivables_trx_org_id;
6793:
6794: cep_standard.debug('receivables_trx_org_id= '|| receivables_trx_org_id);
6795: set_single_org(receivables_trx_org_id);
6796:
6797: select mo_global.GET_CURRENT_ORG_ID
6798: into current_org_id
6797: select mo_global.GET_CURRENT_ORG_ID
6798: into current_org_id
6799: from dual;
6800:
6801: cep_standard.debug('current_org_id =' ||current_org_id );
6802: cep_standard.debug('match_engine: calling CE_AUTO_BANK_CLEAR.misc_receipt');
6803:
6804: CE_AUTO_BANK_CLEAR.misc_receipt(
6805: X_passin_mode => 'AUTO_TRX',
6798: into current_org_id
6799: from dual;
6800:
6801: cep_standard.debug('current_org_id =' ||current_org_id );
6802: cep_standard.debug('match_engine: calling CE_AUTO_BANK_CLEAR.misc_receipt');
6803:
6804: CE_AUTO_BANK_CLEAR.misc_receipt(
6805: X_passin_mode => 'AUTO_TRX',
6806: X_trx_number => CE_AUTO_BANK_MATCH.csl_bank_trx_number || '/NSF',
6835: X_cr_id => p_cr_id,
6836: X_effective_date => CE_AUTO_BANK_MATCH.csl_effective_date,
6837: X_org_id => nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id));
6838:
6839: cep_standard.debug('end match_engine: >> CE_AUTO_BANK_CLEAR.misc_receipt');
6840: cep_standard.debug('p_cr_id = '|| p_cr_id);
6841: cep_standard.debug('Create a misc receipt with cash_receipt_id='|| to_char(p_cr_id));
6842: END IF; --validate payment method
6843: END IF; -- if not creating foreign misc receipts with null exchange info
6836: X_effective_date => CE_AUTO_BANK_MATCH.csl_effective_date,
6837: X_org_id => nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id));
6838:
6839: cep_standard.debug('end match_engine: >> CE_AUTO_BANK_CLEAR.misc_receipt');
6840: cep_standard.debug('p_cr_id = '|| p_cr_id);
6841: cep_standard.debug('Create a misc receipt with cash_receipt_id='|| to_char(p_cr_id));
6842: END IF; --validate payment method
6843: END IF; -- if not creating foreign misc receipts with null exchange info
6844: EXCEPTION
6837: X_org_id => nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id));
6838:
6839: cep_standard.debug('end match_engine: >> CE_AUTO_BANK_CLEAR.misc_receipt');
6840: cep_standard.debug('p_cr_id = '|| p_cr_id);
6841: cep_standard.debug('Create a misc receipt with cash_receipt_id='|| to_char(p_cr_id));
6842: END IF; --validate payment method
6843: END IF; -- if not creating foreign misc receipts with null exchange info
6844: EXCEPTION
6845: WHEN OTHERS THEN
6842: END IF; --validate payment method
6843: END IF; -- if not creating foreign misc receipts with null exchange info
6844: EXCEPTION
6845: WHEN OTHERS THEN
6846: CEP_STANDARD.DEBUG('ERROR IN CE_AUTO_BANK_CLEAR.MISC_RECEIPT');
6847: RAISE;
6848: END;
6849: END IF;
6850: ELSE /* misc_exists = 'Y' */
6847: RAISE;
6848: END;
6849: END IF;
6850: ELSE /* misc_exists = 'Y' */
6851: cep_standard.debug('misc receipt exists with trx number <'||CE_AUTO_BANK_MATCH.csl_bank_trx_number || '/NSF>.');
6852: END IF;
6853: END IF;
6854:
6855: --
6887: secondary_match := 'T';
6888: END IF;
6889:
6890: match_line(primary_match);
6891: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_match_found = '||CE_AUTO_BANK_MATCH.csl_match_found);
6892: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_reconcile_flag = '||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6893: cep_standard.debug('DEBUG: secondary_match = '|| secondary_match);
6894: IF (CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL')
6895: AND nvl(CE_AUTO_BANK_MATCH.csl_reconcile_flag,'NONE') NOT IN ('JE', 'OI')
6888: END IF;
6889:
6890: match_line(primary_match);
6891: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_match_found = '||CE_AUTO_BANK_MATCH.csl_match_found);
6892: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_reconcile_flag = '||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6893: cep_standard.debug('DEBUG: secondary_match = '|| secondary_match);
6894: IF (CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL')
6895: AND nvl(CE_AUTO_BANK_MATCH.csl_reconcile_flag,'NONE') NOT IN ('JE', 'OI')
6896: AND secondary_match IS NOT NULL)
6889:
6890: match_line(primary_match);
6891: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_match_found = '||CE_AUTO_BANK_MATCH.csl_match_found);
6892: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_reconcile_flag = '||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6893: cep_standard.debug('DEBUG: secondary_match = '|| secondary_match);
6894: IF (CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL')
6895: AND nvl(CE_AUTO_BANK_MATCH.csl_reconcile_flag,'NONE') NOT IN ('JE', 'OI')
6896: AND secondary_match IS NOT NULL)
6897: THEN
6897: THEN
6898: match_line(secondary_match);
6899: END IF;
6900:
6901: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_found - '|| CE_AUTO_BANK_MATCH.csl_match_found);
6902: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag - '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6903: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_matching_against - '|| CE_AUTO_BANK_MATCH.csl_matching_against);
6904: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6905: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6898: match_line(secondary_match);
6899: END IF;
6900:
6901: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_found - '|| CE_AUTO_BANK_MATCH.csl_match_found);
6902: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag - '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6903: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_matching_against - '|| CE_AUTO_BANK_MATCH.csl_matching_against);
6904: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6905: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6906: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text - '|| CE_AUTO_BANK_MATCH.csl_invoice_text);
6899: END IF;
6900:
6901: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_found - '|| CE_AUTO_BANK_MATCH.csl_match_found);
6902: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag - '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6903: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_matching_against - '|| CE_AUTO_BANK_MATCH.csl_matching_against);
6904: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6905: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6906: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text - '|| CE_AUTO_BANK_MATCH.csl_invoice_text);
6907: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text - '|| CE_AUTO_BANK_MATCH.csl_bank_account_text);
6900:
6901: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_found - '|| CE_AUTO_BANK_MATCH.csl_match_found);
6902: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag - '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6903: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_matching_against - '|| CE_AUTO_BANK_MATCH.csl_matching_against);
6904: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6905: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6906: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text - '|| CE_AUTO_BANK_MATCH.csl_invoice_text);
6907: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text - '|| CE_AUTO_BANK_MATCH.csl_bank_account_text);
6908: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_customer_text - '|| CE_AUTO_BANK_MATCH.csl_customer_text);
6901: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_found - '|| CE_AUTO_BANK_MATCH.csl_match_found);
6902: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag - '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6903: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_matching_against - '|| CE_AUTO_BANK_MATCH.csl_matching_against);
6904: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6905: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6906: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text - '|| CE_AUTO_BANK_MATCH.csl_invoice_text);
6907: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text - '|| CE_AUTO_BANK_MATCH.csl_bank_account_text);
6908: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_customer_text - '|| CE_AUTO_BANK_MATCH.csl_customer_text);
6909:
6902: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag - '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6903: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_matching_against - '|| CE_AUTO_BANK_MATCH.csl_matching_against);
6904: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6905: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6906: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text - '|| CE_AUTO_BANK_MATCH.csl_invoice_text);
6907: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text - '|| CE_AUTO_BANK_MATCH.csl_bank_account_text);
6908: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_customer_text - '|| CE_AUTO_BANK_MATCH.csl_customer_text);
6909:
6910: -- bug 4542114 If there is no match, then create the misc receipt
6903: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_matching_against - '|| CE_AUTO_BANK_MATCH.csl_matching_against);
6904: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6905: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6906: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text - '|| CE_AUTO_BANK_MATCH.csl_invoice_text);
6907: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text - '|| CE_AUTO_BANK_MATCH.csl_bank_account_text);
6908: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_customer_text - '|| CE_AUTO_BANK_MATCH.csl_customer_text);
6909:
6910: -- bug 4542114 If there is no match, then create the misc receipt
6911: /* bug 6049035 If there is no match with and data is available in
6904: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6905: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6906: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text - '|| CE_AUTO_BANK_MATCH.csl_invoice_text);
6907: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text - '|| CE_AUTO_BANK_MATCH.csl_bank_account_text);
6908: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_customer_text - '|| CE_AUTO_BANK_MATCH.csl_customer_text);
6909:
6910: -- bug 4542114 If there is no match, then create the misc receipt
6911: /* bug 6049035 If there is no match with and data is available in
6912: "Customer name" or "account number" and also the invoice field in
6922: AND (
6923: CE_AUTO_BANK_MATCH.csl_bank_account_text IS NOT NULL
6924: OR CE_AUTO_BANK_MATCH.csl_customer_text IS NOT NULL))))
6925: THEN
6926: cep_standard.debug('calling create_misc_trx ');
6927: create_misc_trx;
6928: END IF;
6929: ELSE
6930: -- bug 4542114
6942: END IF;
6943: END IF; -- End main IF statement -- CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'OI'
6944: END IF; -- Forex
6945:
6946: cep_standard.debug('<
6948: EXCEPTION
6949: WHEN NO_DATA_FOUND THEN
6950: cep_standard.debug('EXCEPTION:CE_AUTO_BANK_MATCH.match_engine-no_data_found');
6946: cep_standard.debug('<
6948: EXCEPTION
6949: WHEN NO_DATA_FOUND THEN
6950: cep_standard.debug('EXCEPTION:CE_AUTO_BANK_MATCH.match_engine-no_data_found');
6951: CE_RECONCILIATION_ERRORS_PKG.insert_row(
6952: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6953: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_ABR_INFO_MISSING');
6954: WHEN app_exception.application_exception THEN
6951: CE_RECONCILIATION_ERRORS_PKG.insert_row(
6952: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6953: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_ABR_INFO_MISSING');
6954: WHEN app_exception.application_exception THEN
6955: cep_standard.debug('EXCEPTION:CE_AUTO_BANK_MATCH.match_engine-application_exception');
6956: l_encoded_message := FND_MESSAGE.GET_ENCODED;
6957: IF (l_encoded_message IS NOT NULL)
6958: THEN
6959: cep_standard.debug('Encoded message is: ' ||l_encoded_message);
6955: cep_standard.debug('EXCEPTION:CE_AUTO_BANK_MATCH.match_engine-application_exception');
6956: l_encoded_message := FND_MESSAGE.GET_ENCODED;
6957: IF (l_encoded_message IS NOT NULL)
6958: THEN
6959: cep_standard.debug('Encoded message is: ' ||l_encoded_message);
6960: FND_MESSAGE.parse_encoded(l_encoded_message,l_app_short_name,l_message_name);
6961: ELSE
6962: cep_standard.debug('No messages on stack');
6963: l_message_name := 'OTHER_APP_ERROR';
6958: THEN
6959: cep_standard.debug('Encoded message is: ' ||l_encoded_message);
6960: FND_MESSAGE.parse_encoded(l_encoded_message,l_app_short_name,l_message_name);
6961: ELSE
6962: cep_standard.debug('No messages on stack');
6963: l_message_name := 'OTHER_APP_ERROR';
6964: END IF;
6965: CE_RECONCILIATION_ERRORS_PKG.insert_row(
6966: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6967: CE_AUTO_BANK_MATCH.csl_statement_line_id,
6968: l_message_name,
6969: l_app_short_name);
6970: WHEN OTHERS THEN
6971: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.match_engine' );
6972: RAISE;
6973: END match_engine;
6974:
6975: /* ---------------------------------------------------------------------
6985: --------------------------------------------------------------------- */
6986: FUNCTION lock_statement RETURN BOOLEAN IS
6987: x_statement_header_id CE_STATEMENT_HEADERS.statement_header_id%TYPE;
6988: BEGIN
6989: cep_standard.debug('>>CE_AUTO_BANK_MATCH.lock_statement');
6990:
6991: SELECT statement_header_id
6992: INTO x_statement_header_id
6993: FROM ce_statement_headers
6993: FROM ce_statement_headers
6994: WHERE rowid = CE_AUTO_BANK_MATCH.csh_rowid
6995: FOR UPDATE OF statement_header_id NOWAIT;
6996:
6997: cep_standard.debug('>>CE_AUTO_BANK_MATCH.Statement lock OK');
6998: cep_standard.debug('<
7000: RETURN(TRUE);
7001: EXCEPTION
6994: WHERE rowid = CE_AUTO_BANK_MATCH.csh_rowid
6995: FOR UPDATE OF statement_header_id NOWAIT;
6996:
6997: cep_standard.debug('>>CE_AUTO_BANK_MATCH.Statement lock OK');
6998: cep_standard.debug('<
7000: RETURN(TRUE);
7001: EXCEPTION
7002: WHEN APP_EXCEPTIONS.record_lock_exception THEN
7001: EXCEPTION
7002: WHEN APP_EXCEPTIONS.record_lock_exception THEN
7003: return(FALSE);
7004: WHEN OTHERS THEN
7005: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.lock_statement' );
7006: RAISE;
7007: END lock_statement;
7008:
7009: /* ---------------------------------------------------------------------
7017: | match_process |
7018: --------------------------------------------------------------------- */
7019: FUNCTION lock_statement_line RETURN BOOLEAN IS
7020: BEGIN
7021: cep_standard.debug('>>CE_AUTO_BANK_MATCH.lock_statement_line');
7022: SELECT statement_line_id,
7023: trx_date,
7024: trx_type,
7025: trx_code_id,
7050: FROM ce_statement_lines
7051: WHERE rowid = CE_AUTO_BANK_MATCH.csl_rowid
7052: FOR UPDATE OF status NOWAIT;
7053:
7054: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type='||CE_AUTO_BANK_MATCH.csl_trx_type||
7055: ', csl_currency_code=' || csl_currency_code ||
7056: ', csl_bank_trx_number='||csl_bank_trx_number );
7057: cep_standard.debug('csl_customer_text='||csl_customer_text ||
7058: ', csl_invoice_text='|| csl_invoice_text||
7053:
7054: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type='||CE_AUTO_BANK_MATCH.csl_trx_type||
7055: ', csl_currency_code=' || csl_currency_code ||
7056: ', csl_bank_trx_number='||csl_bank_trx_number );
7057: cep_standard.debug('csl_customer_text='||csl_customer_text ||
7058: ', csl_invoice_text='|| csl_invoice_text||
7059: ', csl_bank_account_text='||csl_bank_account_text);
7060: cep_standard.debug('csl_amount='||csl_amount ||
7061: ', csl_charges_amount='||csl_charges_amount||
7056: ', csl_bank_trx_number='||csl_bank_trx_number );
7057: cep_standard.debug('csl_customer_text='||csl_customer_text ||
7058: ', csl_invoice_text='|| csl_invoice_text||
7059: ', csl_bank_account_text='||csl_bank_account_text);
7060: cep_standard.debug('csl_amount='||csl_amount ||
7061: ', csl_charges_amount='||csl_charges_amount||
7062: ', csl_original_amount='||csl_original_amount);
7063:
7064: cep_standard.debug('<
7060: cep_standard.debug('csl_amount='||csl_amount ||
7061: ', csl_charges_amount='||csl_charges_amount||
7062: ', csl_original_amount='||csl_original_amount);
7063:
7064: cep_standard.debug('<
7066:
7067: EXCEPTION
7068: WHEN APP_EXCEPTIONS.record_lock_exception THEN
7067: EXCEPTION
7068: WHEN APP_EXCEPTIONS.record_lock_exception THEN
7069: return(FALSE);
7070: WHEN OTHERS THEN
7071: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.lock_statement_line' );
7072: RAISE;
7073: return(FALSE);
7074: END lock_statement_line;
7075:
7085: | match_process |
7086: --------------------------------------------------------------------- */
7087: PROCEDURE update_gl_date IS
7088: BEGIN
7089: cep_standard.debug('>>CE_AUTO_BANK_MATCH.update_gl_date');
7090: IF ((CE_AUTO_BANK_REC.find_gl_period(CE_AUTO_BANK_REC.G_gl_date, 200)) OR
7091: (CE_AUTO_BANK_REC.find_gl_period(CE_AUTO_BANK_REC.G_gl_date, 222))) THEN
7092: UPDATE ce_statement_headers
7093: SET gl_date = CE_AUTO_BANK_REC.G_gl_date
7092: UPDATE ce_statement_headers
7093: SET gl_date = CE_AUTO_BANK_REC.G_gl_date
7094: WHERE rowid = CE_AUTO_BANK_MATCH.csh_rowid;
7095: END IF;
7096: cep_standard.debug('<
7098: WHEN OTHERS THEN
7099: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.update_gl_date' );
7100: RAISE;
7095: END IF;
7096: cep_standard.debug('<
7098: WHEN OTHERS THEN
7099: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.update_gl_date' );
7100: RAISE;
7101: END update_gl_date;
7102:
7103: /* ---------------------------------------------------------------------
7114: /*
7115: PROCEDURE find_le_sys_par(x_bank_account_id number) IS
7116:
7117: BEGIN
7118: cep_standard.debug('>>CE_AUTO_BANK_MATCH.find_le_sys_par x_bank_account_id'|| x_bank_account_id);
7119: -- populate ce_security_profiles_tmp table with ce_security_procfiles_v
7120: CEP_STANDARD.init_security;
7121:
7122: IF (x_bank_account_id is not null)
7116:
7117: BEGIN
7118: cep_standard.debug('>>CE_AUTO_BANK_MATCH.find_le_sys_par x_bank_account_id'|| x_bank_account_id);
7119: -- populate ce_security_profiles_tmp table with ce_security_procfiles_v
7120: CEP_STANDARD.init_security;
7121:
7122: IF (x_bank_account_id is not null)
7123: select ACCOUNT_OWNER_ORG_ID
7124: into p_le_id
7134: OI_MATCHING_CODE_OLD
7135: FROM CE_SYSTEM_PARAMETERS;
7136:
7137: END IF;
7138: cep_standard.debug('<
7140: WHEN OTHERS THEN
7141: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.find_le_sys_par' );
7142: RAISE;
7137: END IF;
7138: cep_standard.debug('<
7140: WHEN OTHERS THEN
7141: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.find_le_sys_par' );
7142: RAISE;
7143: END find_le_sys_par;
7144: */
7145:
7160: calc_percent_tolerance NUMBER;
7161: calc_amount_tolerance NUMBER;
7162: calc_charges_amount NUMBER;
7163: BEGIN
7164: cep_standard.debug('>>CE_AUTO_BANK_MATCH.calc_actual_tolerance');
7165: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.trx_currency_type = '||
7166: CE_AUTO_BANK_MATCH.trx_currency_type);
7167:
7168: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.corr_csl_amount = '||
7161: calc_amount_tolerance NUMBER;
7162: calc_charges_amount NUMBER;
7163: BEGIN
7164: cep_standard.debug('>>CE_AUTO_BANK_MATCH.calc_actual_tolerance');
7165: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.trx_currency_type = '||
7166: CE_AUTO_BANK_MATCH.trx_currency_type);
7167:
7168: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.corr_csl_amount = '||
7169: CE_AUTO_BANK_MATCH.corr_csl_amount);
7164: cep_standard.debug('>>CE_AUTO_BANK_MATCH.calc_actual_tolerance');
7165: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.trx_currency_type = '||
7166: CE_AUTO_BANK_MATCH.trx_currency_type);
7167:
7168: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.corr_csl_amount = '||
7169: CE_AUTO_BANK_MATCH.corr_csl_amount);
7170:
7171: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance = '||
7172: CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance ||
7167:
7168: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.corr_csl_amount = '||
7169: CE_AUTO_BANK_MATCH.corr_csl_amount);
7170:
7171: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance = '||
7172: CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance ||
7173: ', CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance = '||
7174: CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance);
7175:
7172: CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance ||
7173: ', CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance = '||
7174: CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance);
7175:
7176: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance = '||
7177: CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance ||
7178: ', CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance = '||
7179: CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
7180:
7177: CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance ||
7178: ', CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance = '||
7179: CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
7180:
7181: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance = '||
7182: CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance ||
7183: ', CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance = '||
7184: CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
7185:
7182: CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance ||
7183: ', CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance = '||
7184: CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
7185:
7186: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.BA_RECON_OI_AMOUNT_TOLERANCE = '||
7187: CE_AUTO_BANK_MATCH.BA_RECON_OI_AMOUNT_TOLERANCE ||
7188: ', CE_AUTO_BANK_MATCH.BA_RECON_OI_PERCENT_TOLERANCE = '||
7189: CE_AUTO_BANK_MATCH.BA_RECON_OI_PERCENT_TOLERANCE);
7190:
7187: CE_AUTO_BANK_MATCH.BA_RECON_OI_AMOUNT_TOLERANCE ||
7188: ', CE_AUTO_BANK_MATCH.BA_RECON_OI_PERCENT_TOLERANCE = '||
7189: CE_AUTO_BANK_MATCH.BA_RECON_OI_PERCENT_TOLERANCE);
7190:
7191: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_clearing_trx_type = '||
7192: CE_AUTO_BANK_MATCH.csl_clearing_trx_type);
7193:
7194: CE_AUTO_BANK_MATCH.tolerance_amount := 0;
7195: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('REJECTED', 'STOP')
7300: (CE_AUTO_BANK_MATCH.BA_RECON_OI_PERCENT_TOLERANCE / 100);
7301: END IF;
7302: end if;
7303:
7304: cep_standard.debug('%calc_amount_tolerance: '||calc_amount_tolerance);
7305: cep_standard.debug('%calc_percent_tolerance: '||calc_percent_tolerance);
7306:
7307: if (calc_percent_tolerance < 0) then
7308: calc_percent_tolerance := calc_percent_tolerance * -1;
7301: END IF;
7302: end if;
7303:
7304: cep_standard.debug('%calc_amount_tolerance: '||calc_amount_tolerance);
7305: cep_standard.debug('%calc_percent_tolerance: '||calc_percent_tolerance);
7306:
7307: if (calc_percent_tolerance < 0) then
7308: calc_percent_tolerance := calc_percent_tolerance * -1;
7309: end if;
7309: end if;
7310: --
7311: -- Comparison
7312: --
7313: cep_standard.debug('calc_amount_tolerance: '||calc_amount_tolerance);
7314: cep_standard.debug('calc_percent_tolerance: '||calc_percent_tolerance);
7315: IF (calc_amount_tolerance = 0) THEN
7316: CE_AUTO_BANK_MATCH.tolerance_amount := calc_percent_tolerance;
7317: ELSIF (calc_percent_tolerance = 0) THEN
7310: --
7311: -- Comparison
7312: --
7313: cep_standard.debug('calc_amount_tolerance: '||calc_amount_tolerance);
7314: cep_standard.debug('calc_percent_tolerance: '||calc_percent_tolerance);
7315: IF (calc_amount_tolerance = 0) THEN
7316: CE_AUTO_BANK_MATCH.tolerance_amount := calc_percent_tolerance;
7317: ELSIF (calc_percent_tolerance = 0) THEN
7318: CE_AUTO_BANK_MATCH.tolerance_amount := calc_amount_tolerance;
7323: CE_AUTO_BANK_MATCH.tolerance_amount := calc_percent_tolerance;
7324: END IF;
7325: END IF;
7326: END IF;
7327: cep_standard.debug('<
7329: WHEN OTHERS THEN
7330: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.calc_actual_tolerance' );
7331: RAISE;
7326: END IF;
7327: cep_standard.debug('<
7329: WHEN OTHERS THEN
7330: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.calc_actual_tolerance' );
7331: RAISE;
7332: END calc_actual_tolerance;
7333:
7334: /* ---------------------------------------------------------------------
7348: min_acct_unit NUMBER;
7349: acctd_amount NUMBER;
7350: rounded_amount NUMBER;
7351: BEGIN
7352: cep_standard.debug('>>CE_AUTO_BANK_MATCH.convert_amount_tolerance');
7353:
7354: IF (CE_AUTO_BANK_MATCH.csl_exchange_rate_type <> 'User') THEN
7355:
7356: BEGIN
7363: CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
7364: amount_to_convert);
7365: EXCEPTION
7366: WHEN OTHERS THEN
7367: cep_standard.debug('EXCEPTION: Could not convert amount');
7368: acctd_amount := NULL;
7369: END;
7370:
7371: rounded_amount := acctd_amount;
7368: acctd_amount := NULL;
7369: END;
7370:
7371: rounded_amount := acctd_amount;
7372: cep_standard.debug('convert_amount_tolerance: rounded_amount: '||
7373: acctd_amount);
7374:
7375: ELSE
7376:
7384: END IF;
7385:
7386: END IF;
7387:
7388: cep_standard.debug('<
7390:
7391: EXCEPTION
7392: WHEN OTHERS THEN
7389: RETURN(rounded_amount);
7390:
7391: EXCEPTION
7392: WHEN OTHERS THEN
7393: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.convert_amount_tolerance');
7394: RAISE;
7395: END convert_amount_tolerance;
7396:
7397: /* ---------------------------------------------------------------------
7410: | valid_method TRUE / FALSE |
7411: --------------------------------------------------------------------- */
7412: FUNCTION validate_payment_method RETURN BOOLEAN IS
7413: BEGIN
7414: cep_standard.debug('>>CE_AUTO_BANK_MATCH.validate_payment_method_id');
7415: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_receipt_method_id '|| CE_AUTO_BANK_MATCH.csl_receipt_method_id);
7416: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_org_id '|| CE_AUTO_BANK_MATCH.trx_org_id);
7417:
7418: SELECT arm.name
7411: --------------------------------------------------------------------- */
7412: FUNCTION validate_payment_method RETURN BOOLEAN IS
7413: BEGIN
7414: cep_standard.debug('>>CE_AUTO_BANK_MATCH.validate_payment_method_id');
7415: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_receipt_method_id '|| CE_AUTO_BANK_MATCH.csl_receipt_method_id);
7416: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_org_id '|| CE_AUTO_BANK_MATCH.trx_org_id);
7417:
7418: SELECT arm.name
7419: INTO CE_AUTO_BANK_REC.G_payment_method_name
7412: FUNCTION validate_payment_method RETURN BOOLEAN IS
7413: BEGIN
7414: cep_standard.debug('>>CE_AUTO_BANK_MATCH.validate_payment_method_id');
7415: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_receipt_method_id '|| CE_AUTO_BANK_MATCH.csl_receipt_method_id);
7416: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_org_id '|| CE_AUTO_BANK_MATCH.trx_org_id);
7417:
7418: SELECT arm.name
7419: INTO CE_AUTO_BANK_REC.G_payment_method_name
7420: FROM ar_receipt_method_accounts arma,
7432: and exists (select 1 from ce_bank_acct_uses_gt_v bau
7433: where bau.bank_acct_use_id = arma.REMIT_BANK_ACCT_USE_ID
7434: and bau.bank_account_id =CE_AUTO_BANK_MATCH.csh_bank_account_id
7435: and bau.AR_USE_ENABLE_FLAG = 'Y' );
7436: cep_standard.debug('<
7438: EXCEPTION
7439: WHEN NO_DATA_FOUND THEN
7440: cep_standard.debug('<
7436: cep_standard.debug('<
7438: EXCEPTION
7439: WHEN NO_DATA_FOUND THEN
7440: cep_standard.debug('<
7442: WHEN TOO_MANY_ROWS THEN
7443: cep_standard.debug('<
7439: WHEN NO_DATA_FOUND THEN
7440: cep_standard.debug('<
7442: WHEN TOO_MANY_ROWS THEN
7443: cep_standard.debug('<
7445: WHEN OTHERS THEN
7446: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.validate_payment_method');
7447: RAISE;
7442: WHEN TOO_MANY_ROWS THEN
7443: cep_standard.debug('<
7445: WHEN OTHERS THEN
7446: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.validate_payment_method');
7447: RAISE;
7448: END validate_payment_method;
7449:
7450: /* ----------------------------------------------------------------------
7473: l_eff_date date ;
7474: l_le_id NUMBER;
7475:
7476: BEGIN
7477: cep_standard.debug( '>>CE_AUTO_BANK_MATCH.get_vat_tax_id' );
7478: cep_standard.debug( 'CE_AUTO_BANK_MATCH.csl_receivables_trx_id= '|| CE_AUTO_BANK_MATCH.csl_receivables_trx_id||
7479: ',CE_AUTO_BANK_MATCH.trx_org_id='||CE_AUTO_BANK_MATCH.trx_org_id );
7480: if (X_pass_mode = 'AUTO_TRX') then
7481: -- bug 5733971
7474: l_le_id NUMBER;
7475:
7476: BEGIN
7477: cep_standard.debug( '>>CE_AUTO_BANK_MATCH.get_vat_tax_id' );
7478: cep_standard.debug( 'CE_AUTO_BANK_MATCH.csl_receivables_trx_id= '|| CE_AUTO_BANK_MATCH.csl_receivables_trx_id||
7479: ',CE_AUTO_BANK_MATCH.trx_org_id='||CE_AUTO_BANK_MATCH.trx_org_id );
7480: if (X_pass_mode = 'AUTO_TRX') then
7481: -- bug 5733971
7482: begin
7487: where OPERATING_UNIT_ID =nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id) ;
7488: exception
7489: WHEN OTHERS THEN
7490: -- Bug 12989812 Start
7491: cep_standard.debug('Fetching LE ID from Bank Acc ID.');
7492: -- l_le_id := nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id) ;
7493: SELECT ACCOUNT_OWNER_ORG_ID
7494: INTO L_LE_ID
7495: FROM CE_BANK_ACCOUNTS
7493: SELECT ACCOUNT_OWNER_ORG_ID
7494: INTO L_LE_ID
7495: FROM CE_BANK_ACCOUNTS
7496: WHERE BANK_ACCOUNT_ID = CE_AUTO_BANK_MATCH.CSH_BANK_ACCOUNT_ID ;
7497: cep_standard.debug('New LE Id = ' || l_le_id);
7498: -- Bug 12989812 End
7499: end;
7500: cep_standard.debug( 'LEGAL_ENTITY_ID ='||l_le_id );
7501:
7496: WHERE BANK_ACCOUNT_ID = CE_AUTO_BANK_MATCH.CSH_BANK_ACCOUNT_ID ;
7497: cep_standard.debug('New LE Id = ' || l_le_id);
7498: -- Bug 12989812 End
7499: end;
7500: cep_standard.debug( 'LEGAL_ENTITY_ID ='||l_le_id );
7501:
7502: /* bug 10069324
7503: SELECT ar.liability_tax_code, ar.asset_tax_code
7504: INTO y_dr_vat_tax_code, y_cr_vat_tax_code
7528: y_dr_vat_tax_code := CE_AUTO_BANK_REC.G_dr_vat_tax_code;
7529: y_cr_vat_tax_code := CE_AUTO_BANK_REC.G_cr_vat_tax_code;
7530: end if;
7531:
7532: cep_standard.debug( 'y_dr_vat_tax_code ='||y_dr_vat_tax_code);
7533: cep_standard.debug( 'y_cr_vat_tax_code ='||y_cr_vat_tax_code);
7534:
7535:
7536:
7529: y_cr_vat_tax_code := CE_AUTO_BANK_REC.G_cr_vat_tax_code;
7530: end if;
7531:
7532: cep_standard.debug( 'y_dr_vat_tax_code ='||y_dr_vat_tax_code);
7533: cep_standard.debug( 'y_cr_vat_tax_code ='||y_cr_vat_tax_code);
7534:
7535:
7536:
7537:
7573: between to_char(ar.start_date,'YYYY/MM/DD')
7574: and NVL(to_char(ar.end_date,'YYYY/MM/DD'),
7575: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'));
7576: END IF;
7577: cep_standard.debug('>>get_vat_tax_id.l_vat_tax_id: '||TO_CHAR(l_vat_tax_id));
7578: cep_standard.debug('>>get_vat_tax_id.X_tax_rate: '||TO_CHAR(X_tax_rate));
7579: cep_standard.debug( '<
7581: EXCEPTION
7574: and NVL(to_char(ar.end_date,'YYYY/MM/DD'),
7575: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'));
7576: END IF;
7577: cep_standard.debug('>>get_vat_tax_id.l_vat_tax_id: '||TO_CHAR(l_vat_tax_id));
7578: cep_standard.debug('>>get_vat_tax_id.X_tax_rate: '||TO_CHAR(X_tax_rate));
7579: cep_standard.debug( '<
7581: EXCEPTION
7582: WHEN OTHERS THEN
7575: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'));
7576: END IF;
7577: cep_standard.debug('>>get_vat_tax_id.l_vat_tax_id: '||TO_CHAR(l_vat_tax_id));
7578: cep_standard.debug('>>get_vat_tax_id.X_tax_rate: '||TO_CHAR(X_tax_rate));
7579: cep_standard.debug( '<
7581: EXCEPTION
7582: WHEN OTHERS THEN
7583: return;
7602: x_pay_only_count NUMBER;
7603: x_acct_uses_count NUMBER;
7604: x_ou_exists_in_bau NUMBER;
7605: BEGIN
7606: cep_standard.debug( '>>CE_AUTO_BANK_MATCH.set_acct_type x_bank_account_id ' ||x_bank_account_id );
7607:
7608: IF (x_bank_account_id is not null) THEN
7609:
7610: -- IS AP ACCOUNT?
7672: CE_AUTO_BANK_MATCH.BAU_PAY_USE_ENABLE_FLAG := 'N';
7673: END IF;
7674: END IF; -- (x_bank_account_id is not null)
7675:
7676: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
7677: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
7678: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
7679: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);
7680:
7673: END IF;
7674: END IF; -- (x_bank_account_id is not null)
7675:
7676: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
7677: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
7678: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
7679: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);
7680:
7681: cep_standard.debug( '<
7674: END IF; -- (x_bank_account_id is not null)
7675:
7676: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
7677: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
7678: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
7679: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);
7680:
7681: cep_standard.debug( '<
7675:
7676: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
7677: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
7678: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
7679: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);
7680:
7681: cep_standard.debug( '<
7683: EXCEPTION
7677: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
7678: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
7679: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);
7680:
7681: cep_standard.debug( '<
7683: EXCEPTION
7684: WHEN OTHERS THEN
7685: cep_standard.debug('EXCEPTION: ce_auto_bank_match.set_acct_type');
7681: cep_standard.debug( '<
7683: EXCEPTION
7684: WHEN OTHERS THEN
7685: cep_standard.debug('EXCEPTION: ce_auto_bank_match.set_acct_type');
7686:
7687: RAISE;
7688: END set_acct_type;
7689:
7738: l_unclearing_gl_date DATE; -- 14725907: Added
7739: l_clearing_gl_date DATE; -- 14725907: Added
7740: BEGIN
7741:
7742: cep_standard.debug( '>>CE_AUTO_BANK_MATCH.match_stmt_line_JE ' );
7743:
7744:
7745: SELECT
7746: catv.trx_id,
7800: AND catv.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id,catv.legal_entity_id)
7801: AND catv.application_id = 261
7802: AND NVL(catv.reconciled_status_flag, 'N') = 'N';
7803:
7804: cep_standard.debug( 'no_of_matches '||no_of_matches );
7805:
7806: --
7807: -- Currency and amount should be matched based on whether the stmt line
7808: -- is DOMESTIC, FOREIGN or INTERNATIONAL
7824: l_sl_currency_code := CE_AUTO_BANK_MATCH.csl_currency_code;
7825: l_sl_amount := CE_AUTO_BANK_MATCH.csl_amount;
7826: END IF;
7827:
7828: cep_standard.debug(
7829: 'CE_AUTO_BANK_MATCH.trx_currency_code='||CE_AUTO_BANK_MATCH.trx_currency_code
7830: || ', l_sl_currency_code='|| l_sl_currency_code
7831: || ', CE_AUTO_BANK_MATCH.trx_amount='|| CE_AUTO_BANK_MATCH.trx_amount
7832: || ', l_sl_amount=' ||l_sl_amount);
7842: CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL))
7843: THEN
7844: IF (NOT validate_exchange_details)
7845: THEN
7846: cep_standard.debug('EXCEPTION: Exchange details could not be fetched/validated');
7847: RETURN;
7848: END IF;
7849: END IF;
7850: END IF;
7856: -- Autorecon parameter GL date will not be used for cashflows generated for bank statements
7857: -- l_clearing_gl_date := CE_AUTO_BANK_REC.G_gl_date_original;
7858: l_clearing_gl_date := CE_AUTO_BANK_MATCH.csl_trx_date;
7859:
7860: cep_standard.debug('l_clearing_gl_date='||to_Char(l_clearing_gl_date));
7861: IF(NOT(CE_AUTO_BANK_REC.find_gl_period(l_clearing_gl_date, 101)))
7862: THEN
7863: cep_standard.debug('GL period is closed');
7864: CE_RECONCILIATION_ERRORS_PKG.delete_row(
7859:
7860: cep_standard.debug('l_clearing_gl_date='||to_Char(l_clearing_gl_date));
7861: IF(NOT(CE_AUTO_BANK_REC.find_gl_period(l_clearing_gl_date, 101)))
7862: THEN
7863: cep_standard.debug('GL period is closed');
7864: CE_RECONCILIATION_ERRORS_PKG.delete_row(
7865: CE_AUTO_BANK_MATCH.csh_statement_header_id,
7866: CE_AUTO_BANK_MATCH.csl_statement_line_id);
7867: CE_RECONCILIATION_ERRORS_PKG.insert_row(
7879: Where cashflow_id = CE_AUTO_BANK_MATCH.trx_id
7880: and event_type = 'CE_BAT_UNCLEARED';
7881: exception
7882: when no_data_found then
7883: cep_standard.debug('no unclearing record for cashflow_id '||CE_AUTO_BANK_MATCH.trx_id);
7884: l_unclearing_gl_date := null;
7885: end;
7886: cep_standard.debug('l_clearing_gl_date='||l_clearing_gl_date);
7887: cep_standard.debug('l_unclearing_gl_date='||nvl(to_Char(l_unclearing_gl_date),'
7882: when no_data_found then
7883: cep_standard.debug('no unclearing record for cashflow_id '||CE_AUTO_BANK_MATCH.trx_id);
7884: l_unclearing_gl_date := null;
7885: end;
7886: cep_standard.debug('l_clearing_gl_date='||l_clearing_gl_date);
7887: cep_standard.debug('l_unclearing_gl_date='||nvl(to_Char(l_unclearing_gl_date),'
7888:
7889: if l_clearing_gl_date < trunc(nvl(l_unclearing_gl_date,CE_AUTO_BANK_MATCH.trx_date))
7890: then
7883: cep_standard.debug('no unclearing record for cashflow_id '||CE_AUTO_BANK_MATCH.trx_id);
7884: l_unclearing_gl_date := null;
7885: end;
7886: cep_standard.debug('l_clearing_gl_date='||l_clearing_gl_date);
7887: cep_standard.debug('l_unclearing_gl_date='||nvl(to_Char(l_unclearing_gl_date),'
7889: if l_clearing_gl_date < trunc(nvl(l_unclearing_gl_date,CE_AUTO_BANK_MATCH.trx_date))
7890: then
7891: cep_standard.debug('GL Date earlier than unrec/trx date');
7887: cep_standard.debug('l_unclearing_gl_date='||nvl(to_Char(l_unclearing_gl_date),'
7888:
7889: if l_clearing_gl_date < trunc(nvl(l_unclearing_gl_date,CE_AUTO_BANK_MATCH.trx_date))
7890: then
7891: cep_standard.debug('GL Date earlier than unrec/trx date');
7892:
7893: CE_RECONCILIATION_ERRORS_PKG.delete_row(
7894: CE_AUTO_BANK_MATCH.csh_statement_header_id,
7895: CE_AUTO_BANK_MATCH.csl_statement_line_id);
7946: -- to 'Y'
7947: update_gt_reconciled_status (CE_AUTO_BANK_MATCH.gt_seq_id, 'Y');
7948:
7949: ELSE
7950: cep_standard.debug( 'currency or amount does not match' );
7951: CE_RECONCILIATION_ERRORS_PKG.delete_row(
7952: CE_AUTO_BANK_MATCH.csh_statement_header_id,
7953: CE_AUTO_BANK_MATCH.csl_statement_line_id);
7954:
7957: CE_AUTO_BANK_MATCH.csl_statement_line_id,
7958: 'CE_CE_TRX_AMT_OR_CUR_NOT_MATCH');
7959:
7960: END IF;
7961: cep_standard.debug( '<
7963: EXCEPTION
7964: WHEN NO_DATA_FOUND THEN
7965: cep_standard.debug('NO_DATA_FOUND EXCEPTION in CE_AUTO_BANK_MATCH.match_stmt_line_JE');
7961: cep_standard.debug( '<
7963: EXCEPTION
7964: WHEN NO_DATA_FOUND THEN
7965: cep_standard.debug('NO_DATA_FOUND EXCEPTION in CE_AUTO_BANK_MATCH.match_stmt_line_JE');
7966: CE_RECONCILIATION_ERRORS_PKG.delete_row(
7967: CE_AUTO_BANK_MATCH.csh_statement_header_id,
7968: CE_AUTO_BANK_MATCH.csl_statement_line_id);
7969: CE_RECONCILIATION_ERRORS_PKG.insert_row(
7971: CE_AUTO_BANK_MATCH.csl_statement_line_id,
7972: 'CE_NO_CE_TRX_MATCH');
7973:
7974: WHEN TOO_MANY_ROWS THEN
7975: cep_standard.debug('TOO_MANY_ROWS EXCEPTION in CE_AUTO_BANK_MATCH.match_stmt_line_JE');
7976: CE_RECONCILIATION_ERRORS_PKG.delete_row(
7977: CE_AUTO_BANK_MATCH.csh_statement_header_id,
7978: CE_AUTO_BANK_MATCH.csl_statement_line_id);
7979: CE_RECONCILIATION_ERRORS_PKG.insert_row(
8009: l_trx_source VARCHAR2(20);
8010: l_matching_against VARCHAR2(20);
8011: l_cf NUMBER(15) := 0;
8012: BEGIN
8013: cep_standard.debug('>>CE_AUTO_BANK_MATCH.populate_available_gt');
8014:
8015: -- clean up the temp table
8016: delete ce_available_transactions_tmp;
8017:
8029: and sl.cashflow_id is not null;
8030:
8031: IF l_cf > 0 THEN -- some line has cashflow_id
8032: IF CE_AUTO_BANK_MATCH.av_260_cf_inserted_flag = 'N' THEN
8033: cep_standard.debug('inserting data from ce_260_cf_transactions_v');
8034: insert into ce_available_transactions_tmp
8035: (seq_id,
8036: ROW_ID,
8037: MULTI_SELECT,
8195: l_trx_source,
8196: l_matching_against;
8197: EXIT WHEN r_trx_source%NOTFOUND OR r_trx_source%NOTFOUND IS NULL;
8198:
8199: cep_standard.debug('bank_account_id = ' || p_bank_account_id);
8200: cep_standard.debug('l_trx_source = ' || l_trx_source ||
8201: ', l_trx_type = ' || l_trx_type ||
8202: ', l_trx_code = ' || l_trx_code ||
8203: ', l_matching_against = ' || l_matching_against);
8196: l_matching_against;
8197: EXIT WHEN r_trx_source%NOTFOUND OR r_trx_source%NOTFOUND IS NULL;
8198:
8199: cep_standard.debug('bank_account_id = ' || p_bank_account_id);
8200: cep_standard.debug('l_trx_source = ' || l_trx_source ||
8201: ', l_trx_type = ' || l_trx_type ||
8202: ', l_trx_code = ' || l_trx_code ||
8203: ', l_matching_against = ' || l_matching_against);
8204: IF l_trx_source = 'AP' THEN
8204: IF l_trx_source = 'AP' THEN
8205:
8206: IF CE_AUTO_BANK_MATCH.av_200_inserted_flag = 'N' THEN -- AP data has not been inserted into the GT table
8207:
8208: cep_standard.debug('inserting data from ce_200_transactions_v');
8209:
8210: insert into ce_available_transactions_tmp
8211: (seq_id,
8212: ROW_ID,
8354:
8355: ELSIF l_trx_source = 'AR' THEN
8356:
8357: IF CE_AUTO_BANK_MATCH.av_222_inserted_flag = 'N' THEN -- AP data has not been inserted into the GT table
8358: cep_standard.debug('inserting data from ce_222_transactions_v');
8359:
8360: insert into ce_available_transactions_tmp
8361: (seq_id,
8362: ROW_ID,
8506: -- when l_trx_source is 'CE', only populate from ce_260_cf_transactions_v
8507: /*
8508: IF av_260_inserted_flag = 'N' THEN
8509:
8510: cep_standard.debug('inserting data from ce_260_transactions_v');
8511:
8512: insert into ce_available_transactions_tmp
8513: (seq_id,
8514: ROW_ID,
8660: av_260_inserted_flag := 'Y';
8661: END IF;
8662: */
8663: IF CE_AUTO_BANK_MATCH.av_260_cf_inserted_flag = 'N' THEN
8664: cep_standard.debug('inserting data from ce_260_cf_transactions_v');
8665: insert into ce_available_transactions_tmp
8666: (seq_id,
8667: ROW_ID,
8668: MULTI_SELECT,
8820: ELSIF l_trx_source = 'JE' THEN
8821:
8822: IF CE_AUTO_BANK_MATCH.av_101_inserted_flag = 'N' THEN
8823:
8824: cep_standard.debug('inserting data from ce_101_transactions_v');
8825:
8826: insert into ce_available_transactions_tmp
8827: (seq_id,
8828: ROW_ID,
8975: -- Bug 7356199 removed the inserting data from ce_185_transactions_v
8976: -- as ce_999_transactions_v consists a union of ce_185_transactions_v
8977: IF CE_AUTO_BANK_MATCH.av_999_inserted_flag = 'N' THEN
8978:
8979: cep_standard.debug('inserting data from ce_999_transactions_v');
8980:
8981: insert into ce_available_transactions_tmp
8982: (seq_id,
8983: ROW_ID,
9124: END IF;
9125:
9126: ELSIF l_trx_source = 'PAY' THEN
9127: IF CE_AUTO_BANK_MATCH.av_801_inserted_flag = 'N' THEN
9128: cep_standard.debug('inserting data from ce_801_transactions_v');
9129:
9130: insert into ce_available_transactions_tmp
9131: (seq_id,
9132: ROW_ID,
9273: END IF;
9274:
9275: ELSIF l_trx_source = 'PAY_EFT' THEN
9276: IF CE_AUTO_BANK_MATCH.av_801_eft_inserted_flag = 'N' THEN
9277: cep_standard.debug('inserting data from ce_801_eft_transactions_v');
9278:
9279: insert into ce_available_transactions_tmp
9280: (seq_id,
9281: ROW_ID,
9424: ELSIF l_trx_source = 'X' OR l_trx_source is null THEN
9425: IF l_trx_type IN ('MISC_CREDIT', 'MISC_DEBIT') THEN
9426: IF l_matching_against IN ('STMT', 'MS', 'SM') THEN
9427: IF CE_AUTO_BANK_MATCH.av_260_inserted_flag = 'N' THEN
9428: cep_standard.debug('inserting data from ce_260_transactions_v');
9429: insert into ce_available_transactions_tmp
9430: (seq_id,
9431: ROW_ID,
9432: MULTI_SELECT,
9580: END IF; -- l_matching_against
9581:
9582: IF l_matching_against IN ('MISC', 'MS', 'SM') THEN
9583: IF CE_AUTO_BANK_MATCH.av_222_inserted_flag = 'N' THEN
9584: cep_standard.debug('inserting data from ce_222_transactions_v');
9585: insert into ce_available_transactions_tmp
9586: (seq_id,
9587: ROW_ID,
9588: MULTI_SELECT,
9729: END IF; -- l_matching_against
9730:
9731: ELSIF l_trx_type IN ('REJECTED', 'NSF') THEN
9732: IF CE_AUTO_BANK_MATCH.av_222_inserted_flag = 'N' THEN
9733: cep_standard.debug('inserting data from ce_222_transactions_v');
9734:
9735: insert into ce_available_transactions_tmp
9736: (seq_id,
9737: ROW_ID,
9881: END LOOP; -- r_trx_source cursor
9882: CLOSE r_trx_source;
9883: EXCEPTION
9884: WHEN OTHERS THEN
9885: cep_standard.debug('EXCEPTION: populate_available_gt');
9886: IF r_trx_source%ISOPEN THEN
9887: CLOSE r_trx_source;
9888: END IF;
9889: RAISE;
9914: set reconciled_status_flag = p_status
9915: where seq_id = p_seq_id;
9916: EXCEPTION
9917: WHEN OTHERS THEN
9918: cep_standard.debug('EXCEPTION: update_gt_reconciled_status');
9919: RAISE;
9920: END update_gt_reconciled_status;
9921:
9922:
9944: where application_id = p_application_id
9945: and trx_id = p_trx_id;
9946: EXCEPTION
9947: WHEN OTHERS THEN
9948: cep_standard.debug('EXCEPTION: update_gt_reconciled_status');
9949: RAISE;
9950: END update_gt_reconciled_status;
9951:
9952: /* ---------------------------------------------------------------------
9970: update ce_available_transactions_tmp
9971: set reconciled_status_flag = p_reconciled_status;
9972: EXCEPTION
9973: WHEN OTHERS THEN
9974: cep_standard.debug('EXCEPTION: update_gt_reconciled_status');
9975: RAISE;
9976: END update_gt_reconciled_status;
9977:
9978:
10029: x_pay_count NUMBER;
10030: current_org_id NUMBER;
10031: x_trx_code_row_count NUMBER;
10032: BEGIN
10033: cep_standard.debug('>>CE_AUTO_BANK_MATCH.match_process');
10034: cep_standard.debug('========== START MATCHING ========== ');
10035:
10036: -- populate ce_security_profiles_tmp table with ce_security_procfiles_v
10037: cep_standard.init_security;
10030: current_org_id NUMBER;
10031: x_trx_code_row_count NUMBER;
10032: BEGIN
10033: cep_standard.debug('>>CE_AUTO_BANK_MATCH.match_process');
10034: cep_standard.debug('========== START MATCHING ========== ');
10035:
10036: -- populate ce_security_profiles_tmp table with ce_security_procfiles_v
10037: cep_standard.init_security;
10038:
10033: cep_standard.debug('>>CE_AUTO_BANK_MATCH.match_process');
10034: cep_standard.debug('========== START MATCHING ========== ');
10035:
10036: -- populate ce_security_profiles_tmp table with ce_security_procfiles_v
10037: cep_standard.init_security;
10038:
10039: -- Bug 16298972 Start
10040: -- Reset the G_RECEIPT_PROCESSED_FLAG flag for a new run of autorecon.
10041: IF CE_AUTO_BANK_CLEAR1.G_RECEIPT_PROCESSED_FLAG THEN
10042: CE_AUTO_BANK_CLEAR1.G_RECEIPT_PROCESSED_FLAG := FALSE;
10043: END IF;
10044: -- Bug 16298972 End
10045:
10046: cep_standard.debug('-> Opening r_branch_cursor');
10047: OPEN r_branch_cursor(
10048: CE_AUTO_BANK_REC.G_bank_branch_id,
10049: CE_AUTO_BANK_REC.G_bank_account_id,
10050: CE_AUTO_BANK_REC.G_org_id,
10050: CE_AUTO_BANK_REC.G_org_id,
10051: CE_AUTO_BANK_REC.G_legal_entity_id);
10052: j := 0;
10053: LOOP
10054: cep_standard.debug('-> Fetching r_branch_cursor');
10055: FETCH r_branch_cursor INTO
10056: CE_AUTO_BANK_MATCH.csh_bank_account_id,
10057: CE_AUTO_BANK_MATCH.ba_owner_le_id,
10058: CE_AUTO_BANK_MATCH.aba_asset_code_combination_id;
10056: CE_AUTO_BANK_MATCH.csh_bank_account_id,
10057: CE_AUTO_BANK_MATCH.ba_owner_le_id,
10058: CE_AUTO_BANK_MATCH.aba_asset_code_combination_id;
10059:
10060: cep_standard.debug('-> csh_bank_account_id = '||CE_AUTO_BANK_MATCH.csh_bank_account_id);
10061: -- For each bank account set the use flag
10062: set_acct_type(CE_AUTO_BANK_MATCH.csh_bank_account_id);
10063:
10064: -- configuration set at system parameter's level level
10121: CE_AUTO_BANK_REC.G_ap_matching_order2 -- FOR SEPA ER 6700007
10122: );
10123: END IF;
10124:
10125: cep_standard.debug('-> CE_AUTO_BANK_MATCH.BA_ROWID '|| CE_AUTO_BANK_MATCH.BA_ROWID);
10126: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance);
10127: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance);
10128: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance);
10129: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
10122: );
10123: END IF;
10124:
10125: cep_standard.debug('-> CE_AUTO_BANK_MATCH.BA_ROWID '|| CE_AUTO_BANK_MATCH.BA_ROWID);
10126: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance);
10127: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance);
10128: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance);
10129: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
10130: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance);
10123: END IF;
10124:
10125: cep_standard.debug('-> CE_AUTO_BANK_MATCH.BA_ROWID '|| CE_AUTO_BANK_MATCH.BA_ROWID);
10126: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance);
10127: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance);
10128: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance);
10129: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
10130: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance);
10131: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
10124:
10125: cep_standard.debug('-> CE_AUTO_BANK_MATCH.BA_ROWID '|| CE_AUTO_BANK_MATCH.BA_ROWID);
10126: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance);
10127: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance);
10128: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance);
10129: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
10130: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance);
10131: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
10132: cep_standard.debug('-> CE_AUTO_BANK_REC.G_float_handling_flag='||CE_AUTO_BANK_REC.G_float_handling_flag);
10125: cep_standard.debug('-> CE_AUTO_BANK_MATCH.BA_ROWID '|| CE_AUTO_BANK_MATCH.BA_ROWID);
10126: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance);
10127: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance);
10128: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance);
10129: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
10130: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance);
10131: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
10132: cep_standard.debug('-> CE_AUTO_BANK_REC.G_float_handling_flag='||CE_AUTO_BANK_REC.G_float_handling_flag);
10133: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ap_matching_order ='|| CE_AUTO_BANK_REC.G_ap_matching_order);
10126: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance);
10127: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance);
10128: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance);
10129: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
10130: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance);
10131: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
10132: cep_standard.debug('-> CE_AUTO_BANK_REC.G_float_handling_flag='||CE_AUTO_BANK_REC.G_float_handling_flag);
10133: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ap_matching_order ='|| CE_AUTO_BANK_REC.G_ap_matching_order);
10134: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ar_matching_order ='|| CE_AUTO_BANK_REC.G_ar_matching_order);
10127: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance);
10128: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance);
10129: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
10130: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance);
10131: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
10132: cep_standard.debug('-> CE_AUTO_BANK_REC.G_float_handling_flag='||CE_AUTO_BANK_REC.G_float_handling_flag);
10133: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ap_matching_order ='|| CE_AUTO_BANK_REC.G_ap_matching_order);
10134: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ar_matching_order ='|| CE_AUTO_BANK_REC.G_ar_matching_order);
10135: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_type='||CE_AUTO_BANK_REC.G_exchange_rate_type);
10128: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance);
10129: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
10130: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance);
10131: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
10132: cep_standard.debug('-> CE_AUTO_BANK_REC.G_float_handling_flag='||CE_AUTO_BANK_REC.G_float_handling_flag);
10133: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ap_matching_order ='|| CE_AUTO_BANK_REC.G_ap_matching_order);
10134: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ar_matching_order ='|| CE_AUTO_BANK_REC.G_ar_matching_order);
10135: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_type='||CE_AUTO_BANK_REC.G_exchange_rate_type);
10136: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_date='|| CE_AUTO_BANK_REC.G_exchange_rate_date);
10129: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
10130: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance);
10131: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
10132: cep_standard.debug('-> CE_AUTO_BANK_REC.G_float_handling_flag='||CE_AUTO_BANK_REC.G_float_handling_flag);
10133: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ap_matching_order ='|| CE_AUTO_BANK_REC.G_ap_matching_order);
10134: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ar_matching_order ='|| CE_AUTO_BANK_REC.G_ar_matching_order);
10135: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_type='||CE_AUTO_BANK_REC.G_exchange_rate_type);
10136: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_date='|| CE_AUTO_BANK_REC.G_exchange_rate_date);
10137: cep_standard.debug('-> CE_AUTO_BANK_REC.G_open_interface_flag='|| CE_AUTO_BANK_REC.G_open_interface_flag);
10130: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance);
10131: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
10132: cep_standard.debug('-> CE_AUTO_BANK_REC.G_float_handling_flag='||CE_AUTO_BANK_REC.G_float_handling_flag);
10133: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ap_matching_order ='|| CE_AUTO_BANK_REC.G_ap_matching_order);
10134: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ar_matching_order ='|| CE_AUTO_BANK_REC.G_ar_matching_order);
10135: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_type='||CE_AUTO_BANK_REC.G_exchange_rate_type);
10136: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_date='|| CE_AUTO_BANK_REC.G_exchange_rate_date);
10137: cep_standard.debug('-> CE_AUTO_BANK_REC.G_open_interface_flag='|| CE_AUTO_BANK_REC.G_open_interface_flag);
10138: cep_standard.debug('-> REC.G_open_interface_float_status='|| CE_AUTO_BANK_REC.G_open_interface_float_status);
10131: cep_standard.debug('-> CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
10132: cep_standard.debug('-> CE_AUTO_BANK_REC.G_float_handling_flag='||CE_AUTO_BANK_REC.G_float_handling_flag);
10133: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ap_matching_order ='|| CE_AUTO_BANK_REC.G_ap_matching_order);
10134: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ar_matching_order ='|| CE_AUTO_BANK_REC.G_ar_matching_order);
10135: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_type='||CE_AUTO_BANK_REC.G_exchange_rate_type);
10136: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_date='|| CE_AUTO_BANK_REC.G_exchange_rate_date);
10137: cep_standard.debug('-> CE_AUTO_BANK_REC.G_open_interface_flag='|| CE_AUTO_BANK_REC.G_open_interface_flag);
10138: cep_standard.debug('-> REC.G_open_interface_float_status='|| CE_AUTO_BANK_REC.G_open_interface_float_status);
10139: cep_standard.debug('-> REC.G_open_interface_clear_status='|| CE_AUTO_BANK_REC.G_open_interface_clear_status);
10132: cep_standard.debug('-> CE_AUTO_BANK_REC.G_float_handling_flag='||CE_AUTO_BANK_REC.G_float_handling_flag);
10133: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ap_matching_order ='|| CE_AUTO_BANK_REC.G_ap_matching_order);
10134: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ar_matching_order ='|| CE_AUTO_BANK_REC.G_ar_matching_order);
10135: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_type='||CE_AUTO_BANK_REC.G_exchange_rate_type);
10136: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_date='|| CE_AUTO_BANK_REC.G_exchange_rate_date);
10137: cep_standard.debug('-> CE_AUTO_BANK_REC.G_open_interface_flag='|| CE_AUTO_BANK_REC.G_open_interface_flag);
10138: cep_standard.debug('-> REC.G_open_interface_float_status='|| CE_AUTO_BANK_REC.G_open_interface_float_status);
10139: cep_standard.debug('-> REC.G_open_interface_clear_status='|| CE_AUTO_BANK_REC.G_open_interface_clear_status);
10140: cep_standard.debug('-> REC.G_open_interface_matching_code='||CE_AUTO_BANK_REC.G_open_interface_matching_code);
10133: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ap_matching_order ='|| CE_AUTO_BANK_REC.G_ap_matching_order);
10134: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ar_matching_order ='|| CE_AUTO_BANK_REC.G_ar_matching_order);
10135: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_type='||CE_AUTO_BANK_REC.G_exchange_rate_type);
10136: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_date='|| CE_AUTO_BANK_REC.G_exchange_rate_date);
10137: cep_standard.debug('-> CE_AUTO_BANK_REC.G_open_interface_flag='|| CE_AUTO_BANK_REC.G_open_interface_flag);
10138: cep_standard.debug('-> REC.G_open_interface_float_status='|| CE_AUTO_BANK_REC.G_open_interface_float_status);
10139: cep_standard.debug('-> REC.G_open_interface_clear_status='|| CE_AUTO_BANK_REC.G_open_interface_clear_status);
10140: cep_standard.debug('-> REC.G_open_interface_matching_code='||CE_AUTO_BANK_REC.G_open_interface_matching_code);
10141: cep_standard.debug('-> BA_RECON_OI_AMOUNT_TOLERANCE='|| BA_RECON_OI_AMOUNT_TOLERANCE);
10134: cep_standard.debug('-> CE_AUTO_BANK_REC.G_ar_matching_order ='|| CE_AUTO_BANK_REC.G_ar_matching_order);
10135: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_type='||CE_AUTO_BANK_REC.G_exchange_rate_type);
10136: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_date='|| CE_AUTO_BANK_REC.G_exchange_rate_date);
10137: cep_standard.debug('-> CE_AUTO_BANK_REC.G_open_interface_flag='|| CE_AUTO_BANK_REC.G_open_interface_flag);
10138: cep_standard.debug('-> REC.G_open_interface_float_status='|| CE_AUTO_BANK_REC.G_open_interface_float_status);
10139: cep_standard.debug('-> REC.G_open_interface_clear_status='|| CE_AUTO_BANK_REC.G_open_interface_clear_status);
10140: cep_standard.debug('-> REC.G_open_interface_matching_code='||CE_AUTO_BANK_REC.G_open_interface_matching_code);
10141: cep_standard.debug('-> BA_RECON_OI_AMOUNT_TOLERANCE='|| BA_RECON_OI_AMOUNT_TOLERANCE);
10142: cep_standard.debug('-> BA_RECON_OI_PERCENT_TOLERANCE='|| BA_RECON_OI_PERCENT_TOLERANCE);
10135: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_type='||CE_AUTO_BANK_REC.G_exchange_rate_type);
10136: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_date='|| CE_AUTO_BANK_REC.G_exchange_rate_date);
10137: cep_standard.debug('-> CE_AUTO_BANK_REC.G_open_interface_flag='|| CE_AUTO_BANK_REC.G_open_interface_flag);
10138: cep_standard.debug('-> REC.G_open_interface_float_status='|| CE_AUTO_BANK_REC.G_open_interface_float_status);
10139: cep_standard.debug('-> REC.G_open_interface_clear_status='|| CE_AUTO_BANK_REC.G_open_interface_clear_status);
10140: cep_standard.debug('-> REC.G_open_interface_matching_code='||CE_AUTO_BANK_REC.G_open_interface_matching_code);
10141: cep_standard.debug('-> BA_RECON_OI_AMOUNT_TOLERANCE='|| BA_RECON_OI_AMOUNT_TOLERANCE);
10142: cep_standard.debug('-> BA_RECON_OI_PERCENT_TOLERANCE='|| BA_RECON_OI_PERCENT_TOLERANCE);
10143: cep_standard.debug('-> BA_RECON_AP_FX_DIFF_HANDLING='|| BA_RECON_AP_FX_DIFF_HANDLING);
10136: cep_standard.debug('-> CE_AUTO_BANK_REC.G_exchange_rate_date='|| CE_AUTO_BANK_REC.G_exchange_rate_date);
10137: cep_standard.debug('-> CE_AUTO_BANK_REC.G_open_interface_flag='|| CE_AUTO_BANK_REC.G_open_interface_flag);
10138: cep_standard.debug('-> REC.G_open_interface_float_status='|| CE_AUTO_BANK_REC.G_open_interface_float_status);
10139: cep_standard.debug('-> REC.G_open_interface_clear_status='|| CE_AUTO_BANK_REC.G_open_interface_clear_status);
10140: cep_standard.debug('-> REC.G_open_interface_matching_code='||CE_AUTO_BANK_REC.G_open_interface_matching_code);
10141: cep_standard.debug('-> BA_RECON_OI_AMOUNT_TOLERANCE='|| BA_RECON_OI_AMOUNT_TOLERANCE);
10142: cep_standard.debug('-> BA_RECON_OI_PERCENT_TOLERANCE='|| BA_RECON_OI_PERCENT_TOLERANCE);
10143: cep_standard.debug('-> BA_RECON_AP_FX_DIFF_HANDLING='|| BA_RECON_AP_FX_DIFF_HANDLING);
10144: cep_standard.debug('-> BA_RECON_AR_FX_DIFF_HANDLING='|| BA_RECON_AR_FX_DIFF_HANDLING);
10137: cep_standard.debug('-> CE_AUTO_BANK_REC.G_open_interface_flag='|| CE_AUTO_BANK_REC.G_open_interface_flag);
10138: cep_standard.debug('-> REC.G_open_interface_float_status='|| CE_AUTO_BANK_REC.G_open_interface_float_status);
10139: cep_standard.debug('-> REC.G_open_interface_clear_status='|| CE_AUTO_BANK_REC.G_open_interface_clear_status);
10140: cep_standard.debug('-> REC.G_open_interface_matching_code='||CE_AUTO_BANK_REC.G_open_interface_matching_code);
10141: cep_standard.debug('-> BA_RECON_OI_AMOUNT_TOLERANCE='|| BA_RECON_OI_AMOUNT_TOLERANCE);
10142: cep_standard.debug('-> BA_RECON_OI_PERCENT_TOLERANCE='|| BA_RECON_OI_PERCENT_TOLERANCE);
10143: cep_standard.debug('-> BA_RECON_AP_FX_DIFF_HANDLING='|| BA_RECON_AP_FX_DIFF_HANDLING);
10144: cep_standard.debug('-> BA_RECON_AR_FX_DIFF_HANDLING='|| BA_RECON_AR_FX_DIFF_HANDLING);
10145: cep_standard.debug('-> BA_RECON_CE_FX_DIFF_HANDLING='|| BA_RECON_CE_FX_DIFF_HANDLING);
10138: cep_standard.debug('-> REC.G_open_interface_float_status='|| CE_AUTO_BANK_REC.G_open_interface_float_status);
10139: cep_standard.debug('-> REC.G_open_interface_clear_status='|| CE_AUTO_BANK_REC.G_open_interface_clear_status);
10140: cep_standard.debug('-> REC.G_open_interface_matching_code='||CE_AUTO_BANK_REC.G_open_interface_matching_code);
10141: cep_standard.debug('-> BA_RECON_OI_AMOUNT_TOLERANCE='|| BA_RECON_OI_AMOUNT_TOLERANCE);
10142: cep_standard.debug('-> BA_RECON_OI_PERCENT_TOLERANCE='|| BA_RECON_OI_PERCENT_TOLERANCE);
10143: cep_standard.debug('-> BA_RECON_AP_FX_DIFF_HANDLING='|| BA_RECON_AP_FX_DIFF_HANDLING);
10144: cep_standard.debug('-> BA_RECON_AR_FX_DIFF_HANDLING='|| BA_RECON_AR_FX_DIFF_HANDLING);
10145: cep_standard.debug('-> BA_RECON_CE_FX_DIFF_HANDLING='|| BA_RECON_CE_FX_DIFF_HANDLING);
10146: cep_standard.debug('-> REC.G_differences_account=' || CE_AUTO_BANK_REC.G_differences_account);
10139: cep_standard.debug('-> REC.G_open_interface_clear_status='|| CE_AUTO_BANK_REC.G_open_interface_clear_status);
10140: cep_standard.debug('-> REC.G_open_interface_matching_code='||CE_AUTO_BANK_REC.G_open_interface_matching_code);
10141: cep_standard.debug('-> BA_RECON_OI_AMOUNT_TOLERANCE='|| BA_RECON_OI_AMOUNT_TOLERANCE);
10142: cep_standard.debug('-> BA_RECON_OI_PERCENT_TOLERANCE='|| BA_RECON_OI_PERCENT_TOLERANCE);
10143: cep_standard.debug('-> BA_RECON_AP_FX_DIFF_HANDLING='|| BA_RECON_AP_FX_DIFF_HANDLING);
10144: cep_standard.debug('-> BA_RECON_AR_FX_DIFF_HANDLING='|| BA_RECON_AR_FX_DIFF_HANDLING);
10145: cep_standard.debug('-> BA_RECON_CE_FX_DIFF_HANDLING='|| BA_RECON_CE_FX_DIFF_HANDLING);
10146: cep_standard.debug('-> REC.G_differences_account=' || CE_AUTO_BANK_REC.G_differences_account);
10147: cep_standard.debug('-> REC.G_CE_DIFFERENCES_ACCOUNT=' || CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT);
10140: cep_standard.debug('-> REC.G_open_interface_matching_code='||CE_AUTO_BANK_REC.G_open_interface_matching_code);
10141: cep_standard.debug('-> BA_RECON_OI_AMOUNT_TOLERANCE='|| BA_RECON_OI_AMOUNT_TOLERANCE);
10142: cep_standard.debug('-> BA_RECON_OI_PERCENT_TOLERANCE='|| BA_RECON_OI_PERCENT_TOLERANCE);
10143: cep_standard.debug('-> BA_RECON_AP_FX_DIFF_HANDLING='|| BA_RECON_AP_FX_DIFF_HANDLING);
10144: cep_standard.debug('-> BA_RECON_AR_FX_DIFF_HANDLING='|| BA_RECON_AR_FX_DIFF_HANDLING);
10145: cep_standard.debug('-> BA_RECON_CE_FX_DIFF_HANDLING='|| BA_RECON_CE_FX_DIFF_HANDLING);
10146: cep_standard.debug('-> REC.G_differences_account=' || CE_AUTO_BANK_REC.G_differences_account);
10147: cep_standard.debug('-> REC.G_CE_DIFFERENCES_ACCOUNT=' || CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT);
10148:
10141: cep_standard.debug('-> BA_RECON_OI_AMOUNT_TOLERANCE='|| BA_RECON_OI_AMOUNT_TOLERANCE);
10142: cep_standard.debug('-> BA_RECON_OI_PERCENT_TOLERANCE='|| BA_RECON_OI_PERCENT_TOLERANCE);
10143: cep_standard.debug('-> BA_RECON_AP_FX_DIFF_HANDLING='|| BA_RECON_AP_FX_DIFF_HANDLING);
10144: cep_standard.debug('-> BA_RECON_AR_FX_DIFF_HANDLING='|| BA_RECON_AR_FX_DIFF_HANDLING);
10145: cep_standard.debug('-> BA_RECON_CE_FX_DIFF_HANDLING='|| BA_RECON_CE_FX_DIFF_HANDLING);
10146: cep_standard.debug('-> REC.G_differences_account=' || CE_AUTO_BANK_REC.G_differences_account);
10147: cep_standard.debug('-> REC.G_CE_DIFFERENCES_ACCOUNT=' || CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT);
10148:
10149: IF (r_branch_cursor%ROWCOUNT = j) THEN
10142: cep_standard.debug('-> BA_RECON_OI_PERCENT_TOLERANCE='|| BA_RECON_OI_PERCENT_TOLERANCE);
10143: cep_standard.debug('-> BA_RECON_AP_FX_DIFF_HANDLING='|| BA_RECON_AP_FX_DIFF_HANDLING);
10144: cep_standard.debug('-> BA_RECON_AR_FX_DIFF_HANDLING='|| BA_RECON_AR_FX_DIFF_HANDLING);
10145: cep_standard.debug('-> BA_RECON_CE_FX_DIFF_HANDLING='|| BA_RECON_CE_FX_DIFF_HANDLING);
10146: cep_standard.debug('-> REC.G_differences_account=' || CE_AUTO_BANK_REC.G_differences_account);
10147: cep_standard.debug('-> REC.G_CE_DIFFERENCES_ACCOUNT=' || CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT);
10148:
10149: IF (r_branch_cursor%ROWCOUNT = j) THEN
10150: cep_standard.debug('-> no more bank accounts in branch. exit r_branch_cursor loop');
10143: cep_standard.debug('-> BA_RECON_AP_FX_DIFF_HANDLING='|| BA_RECON_AP_FX_DIFF_HANDLING);
10144: cep_standard.debug('-> BA_RECON_AR_FX_DIFF_HANDLING='|| BA_RECON_AR_FX_DIFF_HANDLING);
10145: cep_standard.debug('-> BA_RECON_CE_FX_DIFF_HANDLING='|| BA_RECON_CE_FX_DIFF_HANDLING);
10146: cep_standard.debug('-> REC.G_differences_account=' || CE_AUTO_BANK_REC.G_differences_account);
10147: cep_standard.debug('-> REC.G_CE_DIFFERENCES_ACCOUNT=' || CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT);
10148:
10149: IF (r_branch_cursor%ROWCOUNT = j) THEN
10150: cep_standard.debug('-> no more bank accounts in branch. exit r_branch_cursor loop');
10151: EXIT;
10146: cep_standard.debug('-> REC.G_differences_account=' || CE_AUTO_BANK_REC.G_differences_account);
10147: cep_standard.debug('-> REC.G_CE_DIFFERENCES_ACCOUNT=' || CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT);
10148:
10149: IF (r_branch_cursor%ROWCOUNT = j) THEN
10150: cep_standard.debug('-> no more bank accounts in branch. exit r_branch_cursor loop');
10151: EXIT;
10152: ELSE
10153: j := r_branch_cursor%ROWCOUNT;
10154: -- bug 5221561
10167: -- populate the GT table
10168: populate_available_gt (CE_AUTO_BANK_MATCH.csh_bank_account_id);
10169: END IF;
10170:
10171: cep_standard.debug('--> Opening r_bank_cursor');
10172: OPEN r_bank_cursor (
10173: CE_AUTO_BANK_REC.G_statement_number_from,
10174: CE_AUTO_BANK_REC.G_statement_number_to,
10175: CE_AUTO_BANK_REC.G_statement_date_from,
10176: CE_AUTO_BANK_REC.G_statement_date_to,
10177: CE_AUTO_BANK_MATCH.csh_bank_account_id);
10178: i := 0;
10179: LOOP
10180: cep_standard.debug('--> CE_AUTO_BANK_MATCH.Fetching r_bank_cursor');
10181: FETCH r_bank_cursor INTO
10182: CE_AUTO_BANK_MATCH.csh_statement_header_id,
10183: CE_AUTO_BANK_MATCH.csh_statement_number,
10184: CE_AUTO_BANK_MATCH.csh_statement_date,
10189: CE_AUTO_BANK_MATCH.aba_check_digits,
10190: CE_AUTO_BANK_MATCH.csh_rowid,
10191: CE_AUTO_BANK_MATCH.csh_statement_complete_flag;
10192:
10193: cep_standard.debug('--> CE_AUTO_BANK_MATCH.statement_header_id:' ||
10194: CE_AUTO_BANK_MATCH.csh_statement_header_id );
10195:
10196: if (r_bank_cursor%ROWCOUNT = i) then
10197: cep_standard.debug('--> no more statements for account in branch. exit r_bank_cursor loop');
10193: cep_standard.debug('--> CE_AUTO_BANK_MATCH.statement_header_id:' ||
10194: CE_AUTO_BANK_MATCH.csh_statement_header_id );
10195:
10196: if (r_bank_cursor%ROWCOUNT = i) then
10197: cep_standard.debug('--> no more statements for account in branch. exit r_bank_cursor loop');
10198: EXIT;
10199: else
10200: i := r_bank_cursor%ROWCOUNT;
10201: end if;
10204: into row_count
10205: from ce_statement_lines
10206: where statement_header_id = CE_AUTO_BANK_MATCH.csh_statement_header_id;
10207:
10208: cep_standard.debug('--> # of statement lines: '||row_count);
10209: if (row_count = 0 ) then
10210: CE_RECONCILIATION_ERRORS_PKG.delete_row(
10211: CE_AUTO_BANK_MATCH.csh_statement_header_id,
10212: to_number(NULL));
10221: -- Lock the statement
10222: IF (lock_statement) THEN /*15974172 Removed parameter lockhandle*/
10223: IF (csh_statement_complete_flag = 'N') THEN
10224: IF (CE_AUTO_BANK_MATCH.BAU_PAY_USE_ENABLE_FLAG <> 'Y') THEN
10225: cep_standard.debug('--> payroll use enabled. call update gl_date.');
10226: update_gl_date;
10227: END IF;
10228: statement_line_count := 0;
10229: -- Read in all the lines on the statement for the selected bank
10227: END IF;
10228: statement_line_count := 0;
10229: -- Read in all the lines on the statement for the selected bank
10230: -- account.
10231: cep_standard.debug('---> Opening line_cursor');
10232: OPEN line_cursor (CE_AUTO_BANK_MATCH.csh_statement_header_id);
10233: LOOP
10234: FETCH line_cursor
10235: INTO CE_AUTO_BANK_MATCH.csl_rowid,
10250: ignore_trx_id2 ;
10251: EXIT WHEN line_cursor%NOTFOUND
10252: OR line_cursor%NOTFOUND IS NULL;
10253:
10254: cep_standard.debug('<========= new statement line ============>');
10255: cep_standard.debug('---> CE_AUTO_BANK_MATCH.csl_statement_line_id = ' || CE_AUTO_BANK_MATCH.csl_statement_line_id);
10256: cep_standard.debug('---> CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
10257: cep_standard.debug('---> CE_AUTO_BANK_MATCH.csl_cashflow_id = ' || CE_AUTO_BANK_MATCH.csl_cashflow_id);
10258: cep_standard.debug('---> CE_AUTO_BANK_MATCH.trx_bank_acct_use_id = ' || CE_AUTO_BANK_MATCH.trx_bank_acct_use_id); -- Bug 8945387
10251: EXIT WHEN line_cursor%NOTFOUND
10252: OR line_cursor%NOTFOUND IS NULL;
10253:
10254: cep_standard.debug('<========= new statement line ============>');
10255: cep_standard.debug('---> CE_AUTO_BANK_MATCH.csl_statement_line_id = ' || CE_AUTO_BANK_MATCH.csl_statement_line_id);
10256: cep_standard.debug('---> CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
10257: cep_standard.debug('---> CE_AUTO_BANK_MATCH.csl_cashflow_id = ' || CE_AUTO_BANK_MATCH.csl_cashflow_id);
10258: cep_standard.debug('---> CE_AUTO_BANK_MATCH.trx_bank_acct_use_id = ' || CE_AUTO_BANK_MATCH.trx_bank_acct_use_id); -- Bug 8945387
10259: CE_AUTO_BANK_MATCH.trx_bank_acct_use_id := NULL; -- Bug 8945387
10252: OR line_cursor%NOTFOUND IS NULL;
10253:
10254: cep_standard.debug('<========= new statement line ============>');
10255: cep_standard.debug('---> CE_AUTO_BANK_MATCH.csl_statement_line_id = ' || CE_AUTO_BANK_MATCH.csl_statement_line_id);
10256: cep_standard.debug('---> CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
10257: cep_standard.debug('---> CE_AUTO_BANK_MATCH.csl_cashflow_id = ' || CE_AUTO_BANK_MATCH.csl_cashflow_id);
10258: cep_standard.debug('---> CE_AUTO_BANK_MATCH.trx_bank_acct_use_id = ' || CE_AUTO_BANK_MATCH.trx_bank_acct_use_id); -- Bug 8945387
10259: CE_AUTO_BANK_MATCH.trx_bank_acct_use_id := NULL; -- Bug 8945387
10260:
10253:
10254: cep_standard.debug('<========= new statement line ============>');
10255: cep_standard.debug('---> CE_AUTO_BANK_MATCH.csl_statement_line_id = ' || CE_AUTO_BANK_MATCH.csl_statement_line_id);
10256: cep_standard.debug('---> CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
10257: cep_standard.debug('---> CE_AUTO_BANK_MATCH.csl_cashflow_id = ' || CE_AUTO_BANK_MATCH.csl_cashflow_id);
10258: cep_standard.debug('---> CE_AUTO_BANK_MATCH.trx_bank_acct_use_id = ' || CE_AUTO_BANK_MATCH.trx_bank_acct_use_id); -- Bug 8945387
10259: CE_AUTO_BANK_MATCH.trx_bank_acct_use_id := NULL; -- Bug 8945387
10260:
10261: select count(*)
10254: cep_standard.debug('<========= new statement line ============>');
10255: cep_standard.debug('---> CE_AUTO_BANK_MATCH.csl_statement_line_id = ' || CE_AUTO_BANK_MATCH.csl_statement_line_id);
10256: cep_standard.debug('---> CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
10257: cep_standard.debug('---> CE_AUTO_BANK_MATCH.csl_cashflow_id = ' || CE_AUTO_BANK_MATCH.csl_cashflow_id);
10258: cep_standard.debug('---> CE_AUTO_BANK_MATCH.trx_bank_acct_use_id = ' || CE_AUTO_BANK_MATCH.trx_bank_acct_use_id); -- Bug 8945387
10259: CE_AUTO_BANK_MATCH.trx_bank_acct_use_id := NULL; -- Bug 8945387
10260:
10261: select count(*)
10262: into rec_status
10264: where statement_line_id = CE_AUTO_BANK_MATCH.csl_statement_line_id
10265: and nvl(status_flag, 'U') = 'M'
10266: and nvl(current_record_flag, 'Y') = 'Y';
10267:
10268: cep_standard.debug('---> rec_status='||rec_status);
10269: if (rec_status = 0)
10270: then
10271: -- NULL values to transaction holders
10272: CE_AUTO_BANK_MATCH.trx_id := TO_NUMBER(NULL);
10319: ELSE
10320: CE_AUTO_BANK_MATCH.calc_csl_amount := CE_AUTO_BANK_MATCH.csl_amount;
10321: END IF;
10322:
10323: cep_standard.debug('---> CE_AUTO_BANK_MATCH.calc_csl_amount = '|| CE_AUTO_BANK_MATCH.calc_csl_amount);
10324:
10325: IF(CE_AUTO_BANK_MATCH.csl_cashflow_id is not null)
10326: THEN --this will handle JEC/ZBA(?) trx
10327: cep_standard.debug('----> JEC/ZBA line. csl_cashflow_id='||csl_cashflow_id);
10323: cep_standard.debug('---> CE_AUTO_BANK_MATCH.calc_csl_amount = '|| CE_AUTO_BANK_MATCH.calc_csl_amount);
10324:
10325: IF(CE_AUTO_BANK_MATCH.csl_cashflow_id is not null)
10326: THEN --this will handle JEC/ZBA(?) trx
10327: cep_standard.debug('----> JEC/ZBA line. csl_cashflow_id='||csl_cashflow_id);
10328: match_stmt_line_JE;
10329: ELSE
10330: -- bug 4435028 multi-matching based on trx_code
10331: cep_standard.debug('----> Opening trx_code_cursor');
10327: cep_standard.debug('----> JEC/ZBA line. csl_cashflow_id='||csl_cashflow_id);
10328: match_stmt_line_JE;
10329: ELSE
10330: -- bug 4435028 multi-matching based on trx_code
10331: cep_standard.debug('----> Opening trx_code_cursor');
10332: OPEN trx_code_cursor (CE_AUTO_BANK_MATCH.csl_statement_line_id,CE_AUTO_BANK_MATCH.csh_bank_account_id);
10333: LOOP
10334: FETCH trx_code_cursor
10335: INTO CE_AUTO_BANK_MATCH.csl_receivables_trx_id,
10342: CE_AUTO_BANK_MATCH.csl_payroll_payment_format;
10343: EXIT WHEN trx_code_cursor%NOTFOUND
10344: OR trx_code_cursor%NOTFOUND IS NULL;
10345:
10346: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_reconcile_flag = '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
10347: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_matching_against = '|| CE_AUTO_BANK_MATCH.csl_matching_against);
10348: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
10349: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_match_found = '|| CE_AUTO_BANK_MATCH.csl_match_found);
10350:
10343: EXIT WHEN trx_code_cursor%NOTFOUND
10344: OR trx_code_cursor%NOTFOUND IS NULL;
10345:
10346: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_reconcile_flag = '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
10347: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_matching_against = '|| CE_AUTO_BANK_MATCH.csl_matching_against);
10348: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
10349: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_match_found = '|| CE_AUTO_BANK_MATCH.csl_match_found);
10350:
10351: IF (CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL') )
10344: OR trx_code_cursor%NOTFOUND IS NULL;
10345:
10346: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_reconcile_flag = '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
10347: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_matching_against = '|| CE_AUTO_BANK_MATCH.csl_matching_against);
10348: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
10349: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_match_found = '|| CE_AUTO_BANK_MATCH.csl_match_found);
10350:
10351: IF (CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL') )
10352: THEN
10345:
10346: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_reconcile_flag = '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
10347: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_matching_against = '|| CE_AUTO_BANK_MATCH.csl_matching_against);
10348: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
10349: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_match_found = '|| CE_AUTO_BANK_MATCH.csl_match_found);
10350:
10351: IF (CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL') )
10352: THEN
10353: CE_AUTO_BANK_MATCH.match_engine;
10350:
10351: IF (CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL') )
10352: THEN
10353: CE_AUTO_BANK_MATCH.match_engine;
10354: cep_standard.debug('----> CE_AUTO_BANK_MATCH.csl_match_found = '
10355: || CE_AUTO_BANK_MATCH.csl_match_found);
10356: IF (CE_AUTO_BANK_MATCH.csl_match_found = 'FULL')
10357: THEN
10358: -- bug 4914608 set the org after a match for AR/AP
10355: || CE_AUTO_BANK_MATCH.csl_match_found);
10356: IF (CE_AUTO_BANK_MATCH.csl_match_found = 'FULL')
10357: THEN
10358: -- bug 4914608 set the org after a match for AR/AP
10359: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_org_id =' ||CE_AUTO_BANK_MATCH.trx_org_id);
10360: set_single_org(CE_AUTO_BANK_MATCH.trx_org_id);
10361:
10362: CE_AUTO_BANK_CLEAR.reconcile_process;
10363:
10375: END IF; --CE_AUTO_BANK_MATCH.csl_match_found = FULL
10376: END IF; --CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL')
10377: END LOOP; -- trx_code_cursor
10378: x_trx_code_row_count := trx_code_cursor%ROWCOUNT;
10379: cep_standard.debug('---> x_trx_code_row_count = '|| x_trx_code_row_count );
10380: CLOSE trx_code_cursor;
10381: END IF; -- cashflow_id is not null
10382: ELSE -- statement line is locked
10383: cep_standard.debug('---> error: CE_LINE_LOCKED');
10379: cep_standard.debug('---> x_trx_code_row_count = '|| x_trx_code_row_count );
10380: CLOSE trx_code_cursor;
10381: END IF; -- cashflow_id is not null
10382: ELSE -- statement line is locked
10383: cep_standard.debug('---> error: CE_LINE_LOCKED');
10384: CE_RECONCILIATION_ERRORS_PKG.insert_row(
10385: CE_AUTO_BANK_MATCH.csh_statement_header_id,
10386: CE_AUTO_BANK_MATCH.csl_statement_line_id,
10387: 'CE_LINE_LOCKED');
10394: END IF; -- rec_status = 0
10395: END LOOP; -- statement lines
10396: CLOSE line_cursor;
10397: ELSE -- statement_complete_flag = 'Y'
10398: cep_standard.debug('--> error: CE_STATEMENT_COMPLETED');
10399: error_statement_line_id := get_min_statement_line_id;
10400: CE_RECONCILIATION_ERRORS_PKG.delete_row(CE_AUTO_BANK_MATCH.csh_statement_header_id,error_statement_line_id);
10401: CE_RECONCILIATION_ERRORS_PKG.insert_row(
10402: CE_AUTO_BANK_MATCH.csh_statement_header_id,
10403: error_statement_line_id,
10404: 'CE_STATEMENT_COMPLETED');
10405: END IF; -- check for statement completed
10406: ELSE -- statement is locked
10407: cep_standard.debug('--> error: CE_LOCK_STATEMENT_HEADER_ERR');
10408: CE_RECONCILIATION_ERRORS_PKG.delete_row(CE_AUTO_BANK_MATCH.csh_statement_header_id, to_number(NULL));
10409: CE_RECONCILIATION_ERRORS_PKG.insert_row(
10410: CE_AUTO_BANK_MATCH.csh_statement_header_id,
10411: to_number(NULL),
10411: to_number(NULL),
10412: 'CE_LOCK_STATEMENT_HEADER_ERR');
10413: END IF; -- check for statement lock
10414: ELSE -- check digits failed
10415: cep_standard.debug('--> error: CE_CHECK_DIGITS');
10416: CE_RECONCILIATION_ERRORS_PKG.delete_row(
10417: CE_AUTO_BANK_MATCH.csh_statement_header_id, to_number(NULL));
10418: CE_RECONCILIATION_ERRORS_PKG.insert_row(
10419: CE_AUTO_BANK_MATCH.csh_statement_header_id,
10426: CLOSE r_branch_cursor;
10427:
10428: -- Bug 16298972 Start
10429: -- If Atleast one receipt was cleared during the autoreconciliation process, call the AR API ARP_CASHBOOK.refresh_risk_value
10430: cep_standard.debug('CE_AUTO_BANK_CLEAR1.G_RECEIPT_PROCESSED_FLAG = ' || CASE WHEN CE_AUTO_BANK_CLEAR1.G_RECEIPT_PROCESSED_FLAG THEN 'TRUE' ELSE 'FALSE' END);
10431:
10432: IF CE_AUTO_BANK_CLEAR1.G_RECEIPT_PROCESSED_FLAG THEN
10433: cep_standard.debug('Calling AR API ARP_CASHBOOK.refresh_risk_value for Autorecon.');
10434: ARP_CASHBOOK.refresh_risk_value;
10429: -- If Atleast one receipt was cleared during the autoreconciliation process, call the AR API ARP_CASHBOOK.refresh_risk_value
10430: cep_standard.debug('CE_AUTO_BANK_CLEAR1.G_RECEIPT_PROCESSED_FLAG = ' || CASE WHEN CE_AUTO_BANK_CLEAR1.G_RECEIPT_PROCESSED_FLAG THEN 'TRUE' ELSE 'FALSE' END);
10431:
10432: IF CE_AUTO_BANK_CLEAR1.G_RECEIPT_PROCESSED_FLAG THEN
10433: cep_standard.debug('Calling AR API ARP_CASHBOOK.refresh_risk_value for Autorecon.');
10434: ARP_CASHBOOK.refresh_risk_value;
10435: cep_standard.debug('Call to AR API ARP_CASHBOOK.refresh_risk_value ended successfully.');
10436: END IF;
10437: -- Bug 16298972 End
10431:
10432: IF CE_AUTO_BANK_CLEAR1.G_RECEIPT_PROCESSED_FLAG THEN
10433: cep_standard.debug('Calling AR API ARP_CASHBOOK.refresh_risk_value for Autorecon.');
10434: ARP_CASHBOOK.refresh_risk_value;
10435: cep_standard.debug('Call to AR API ARP_CASHBOOK.refresh_risk_value ended successfully.');
10436: END IF;
10437: -- Bug 16298972 End
10438:
10439: cep_standard.debug('========== END MATCHING ========== ');
10435: cep_standard.debug('Call to AR API ARP_CASHBOOK.refresh_risk_value ended successfully.');
10436: END IF;
10437: -- Bug 16298972 End
10438:
10439: cep_standard.debug('========== END MATCHING ========== ');
10440: cep_standard.debug('<
10442: WHEN OTHERS THEN
10443: cep_standard.debug(' EXCEPTION: CE_AUTO_BANK_MATCH.match_process - OTHERS');
10436: END IF;
10437: -- Bug 16298972 End
10438:
10439: cep_standard.debug('========== END MATCHING ========== ');
10440: cep_standard.debug('<
10442: WHEN OTHERS THEN
10443: cep_standard.debug(' EXCEPTION: CE_AUTO_BANK_MATCH.match_process - OTHERS');
10444: IF r_branch_cursor%ISOPEN THEN
10439: cep_standard.debug('========== END MATCHING ========== ');
10440: cep_standard.debug('<
10442: WHEN OTHERS THEN
10443: cep_standard.debug(' EXCEPTION: CE_AUTO_BANK_MATCH.match_process - OTHERS');
10444: IF r_branch_cursor%ISOPEN THEN
10445: CLOSE r_branch_cursor;
10446: END IF;
10447: IF r_bank_cursor%ISOPEN THEN
10452: END IF;
10453: IF trx_code_cursor%ISOPEN THEN
10454: CLOSE trx_code_cursor;
10455: END IF;
10456: cep_standard.debug('DEBUG: sqlcode:' || sqlcode );
10457: cep_standard.debug('DEBUG: sqlerrm:' || sqlerrm);
10458: RAISE;
10459: END match_process;
10460:
10453: IF trx_code_cursor%ISOPEN THEN
10454: CLOSE trx_code_cursor;
10455: END IF;
10456: cep_standard.debug('DEBUG: sqlcode:' || sqlcode );
10457: cep_standard.debug('DEBUG: sqlerrm:' || sqlerrm);
10458: RAISE;
10459: END match_process;
10460:
10461: END CE_AUTO_BANK_MATCH;