[Home] [Help]
637:
638: PROCEDURE set_single_org(x_org_id number) IS
639: current_org_id number;
640: BEGIN
641: cep_standard.debug('>>CE_AUTO_BANK_MATCH.set_single_org x_org_id =' || x_org_id);
642:
643: select mo_global.GET_CURRENT_ORG_ID
644: into current_org_id
645: from dual;
643: select mo_global.GET_CURRENT_ORG_ID
644: into current_org_id
645: from dual;
646:
647: cep_standard.debug('current_org_id =' ||current_org_id );
648:
649: -- bug 3782741 set single org, since AR will not allow org_id to be passed
650: --IF CE_AUTO_BANK_MATCH.bau_org_id is not null THEN
651: --IF CE_AUTO_BANK_REC.G_org_id is not null THEN (this is set at ceabrdrb ce_auto_bank_rec)
651: --IF CE_AUTO_BANK_REC.G_org_id is not null THEN (this is set at ceabrdrb ce_auto_bank_rec)
652: IF (x_org_id is not null) THEN
653: IF ((current_org_id is null) or (x_org_id <> current_org_id )) THEN
654: mo_global.set_policy_context('S',x_org_id);
655: cep_standard.debug('set current_org_id to ' ||x_org_id );
656: END IF;
657: END IF;
658:
659: cep_standard.debug('<
655: cep_standard.debug('set current_org_id to ' ||x_org_id );
656: END IF;
657: END IF;
658:
659: cep_standard.debug('<
661: WHEN OTHERS THEN
662: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.set_single_org' );
663: RAISE;
658:
659: cep_standard.debug('<
661: WHEN OTHERS THEN
662: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.set_single_org' );
663: RAISE;
664: END set_single_org;
665: /* ---------------------------------------------------------------------
666: | PRIVATE PROCEDURE |
674:
675: PROCEDURE set_multi_org(x_org_id number) IS
676:
677: BEGIN
678: cep_standard.debug('>>CE_AUTO_BANK_MATCH.set_multi_org x_org_id =' || x_org_id);
679:
680: MO_GLOBAL.init('CE');
681:
682: cep_standard.debug('<
678: cep_standard.debug('>>CE_AUTO_BANK_MATCH.set_multi_org x_org_id =' || x_org_id);
679:
680: MO_GLOBAL.init('CE');
681:
682: cep_standard.debug('<
684: WHEN OTHERS THEN
685: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.set_multi_org' );
686: RAISE;
681:
682: cep_standard.debug('<
684: WHEN OTHERS THEN
685: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.set_multi_org' );
686: RAISE;
687: END set_multi_org;
688:
689: /* ----------------------------------------------------------------------
698: PROCEDURE get_receivables_org_id (X_ORG_ID OUT NOCOPY NUMBER ) IS
699: x_receivables_trx_id number;
700:
701: BEGIN
702: cep_standard.debug( '>>CE_AUTO_BANK_MATCH.get_receivables_org_id' );
703: cep_standard.debug( 'CE_AUTO_BANK_MATCH.csl_receivables_trx_id= '|| CE_AUTO_BANK_MATCH.csl_receivables_trx_id||
704: ',CE_AUTO_BANK_MATCH.trx_org_id='||CE_AUTO_BANK_MATCH.trx_org_id );
705:
706: x_receivables_trx_id := nvl(CE_AUTO_BANK_MATCH.csl_receivables_trx_id,
699: x_receivables_trx_id number;
700:
701: BEGIN
702: cep_standard.debug( '>>CE_AUTO_BANK_MATCH.get_receivables_org_id' );
703: cep_standard.debug( 'CE_AUTO_BANK_MATCH.csl_receivables_trx_id= '|| CE_AUTO_BANK_MATCH.csl_receivables_trx_id||
704: ',CE_AUTO_BANK_MATCH.trx_org_id='||CE_AUTO_BANK_MATCH.trx_org_id );
705:
706: x_receivables_trx_id := nvl(CE_AUTO_BANK_MATCH.csl_receivables_trx_id,
707: CE_AUTO_BANK_REC.G_receivables_trx_id);
705:
706: x_receivables_trx_id := nvl(CE_AUTO_BANK_MATCH.csl_receivables_trx_id,
707: CE_AUTO_BANK_REC.G_receivables_trx_id);
708:
709: cep_standard.debug( 'x_receivables_trx_id= '|| x_receivables_trx_id);
710:
711: -- bug 5722367 removed the reference to ar_receivables_trx_all table to ar_receivables_trx
712: if (x_receivables_trx_id is not null) THEN
713: select org_id
714: into X_ORG_ID
715: from AR_RECEIVABLES_TRX
716: where RECEIVABLES_TRX_ID = x_receivables_trx_id;
717:
718: cep_standard.debug( 'x_ORG_ID= '|| X_ORG_ID);
719:
720: ELSE
721: cep_standard.debug('receivables_trx_id is missing');
722: CE_RECONCILIATION_ERRORS_PKG.insert_row(
717:
718: cep_standard.debug( 'x_ORG_ID= '|| X_ORG_ID);
719:
720: ELSE
721: cep_standard.debug('receivables_trx_id is missing');
722: CE_RECONCILIATION_ERRORS_PKG.insert_row(
723: CE_AUTO_BANK_MATCH.csh_statement_header_id,
724: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_MISSING_REC_ACT_ID');
725:
724: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_MISSING_REC_ACT_ID');
725:
726: END IF;
727:
728: cep_standard.debug( '<
730: WHEN NO_DATA_FOUND THEN
731: cep_standard.debug('receivables_trx_id does not exists');
732: CE_RECONCILIATION_ERRORS_PKG.insert_row(
727:
728: cep_standard.debug( '<
730: WHEN NO_DATA_FOUND THEN
731: cep_standard.debug('receivables_trx_id does not exists');
732: CE_RECONCILIATION_ERRORS_PKG.insert_row(
733: CE_AUTO_BANK_MATCH.csh_statement_header_id,
734: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_NO_REC_ACT_ID');
735: WHEN OTHERS THEN
732: CE_RECONCILIATION_ERRORS_PKG.insert_row(
733: CE_AUTO_BANK_MATCH.csh_statement_header_id,
734: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_NO_REC_ACT_ID');
735: WHEN OTHERS THEN
736: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.get_receivables_org_id' );
737: RAISE;
738: END get_receivables_org_id;
739:
740: /* --------------------------------------------------------------------
754: tx_match_amount NUMBER,
755: precision NUMBER,
756: no_of_matches OUT NOCOPY NUMBER) IS
757: BEGIN
758: cep_standard.debug( '>>CE_AUTO_BANK_MATCH.match_oi_trx' );
759:
760: -- match xtr transaction first then non-xtr OI trx
761: -- bug 4914608 some bank acct used by xtr might not have xtr_use_enable_flag = Y
762: IF ((CE_AUTO_BANK_REC.G_legal_entity_id is not null) or
761: -- bug 4914608 some bank acct used by xtr might not have xtr_use_enable_flag = Y
762: IF ((CE_AUTO_BANK_REC.G_legal_entity_id is not null) or
763: --IF ((CE_AUTO_BANK_MATCH.bau_legal_entity_id is not null) AND
764: (CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag = 'Y')) THEN
765: cep_standard.debug( ' use ce_185_transactions_v CE_AUTO_BANK_MATCH.csl_trx_date='|| CE_AUTO_BANK_MATCH.csl_trx_date);
766: SELECT catv.trx_id,
767: catv.cash_receipt_id,
768: catv.row_id,
769: catv.trx_date,
818: END IF;
819:
820: -- no xtr match from above query
821: IF ( CE_AUTO_BANK_MATCH.trx_id is null) THEN
822: cep_standard.debug( ' use ce_999_transactions_v ' );
823:
824: SELECT catv.trx_id,
825: catv.cash_receipt_id,
826: catv.row_id,
875: AND round(catv.amount, precision) = tx_match_amount
876: AND catv.application_id = 999
877: AND NVL(catv.reconciled_status_flag, 'N') = 'N';
878: END IF;
879: cep_standard.debug( '<
881:
882: /* --------------------------------------------------------------------
883: | PRIVATE FUNCTION |
898: curr_relation VARCHAR2(30);
899: fixed_relation_temp varchar2(30);
900: BEGIN
901:
902: cep_standard.debug('>>CE_AUTO_BANK_MATCH.validate_exchange_details');
903: error_found := FALSE;
904:
905: --
906: -- TRX Currency needs to be the same that the SL currency
918:
919: IF (CE_AUTO_BANK_MATCH.trx_currency_code <>
920: NVL(CE_AUTO_BANK_MATCH.csl_currency_code,
921: CE_AUTO_BANK_MATCH.trx_currency_code)) THEN
922: cep_standard.debug('Inconsistent currencies');
923:
924: CE_RECONCILIATION_ERRORS_PKG.insert_row(
925: CE_AUTO_BANK_MATCH.csh_statement_header_id,
926: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_DIFFERENT_CURRENCY');
929:
930: --
931: -- When Fixed relationship is found, exchange info is not mandatory.
932: --
933: cep_standard.debug('CE_AUTO_BANK_REC.G_functional_currency '||CE_AUTO_BANK_REC.G_functional_currency ||
934: ', CE_AUTO_BANK_MATCH.csl_currency_code ' ||CE_AUTO_BANK_MATCH.csl_currency_code);
935:
936: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_date '||CE_AUTO_BANK_MATCH.csl_exchange_rate_date ||
937: ', CE_AUTO_BANK_MATCH.csl_trx_date ' ||CE_AUTO_BANK_MATCH.csl_trx_date);
932: --
933: cep_standard.debug('CE_AUTO_BANK_REC.G_functional_currency '||CE_AUTO_BANK_REC.G_functional_currency ||
934: ', CE_AUTO_BANK_MATCH.csl_currency_code ' ||CE_AUTO_BANK_MATCH.csl_currency_code);
935:
936: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_date '||CE_AUTO_BANK_MATCH.csl_exchange_rate_date ||
937: ', CE_AUTO_BANK_MATCH.csl_trx_date ' ||CE_AUTO_BANK_MATCH.csl_trx_date);
938:
939: BEGIN
940: gl_currency_api.get_relation(CE_AUTO_BANK_REC.G_functional_currency,
944: fixed_relation,
945: curr_relation);
946: EXCEPTION
947: WHEN OTHERS THEN
948: cep_standard.debug('Cannot find relationship for the give curr');
949: fixed_relation := FALSE;
950: curr_relation := 'OTHER';
951: END;
952: IF fixed_relation THEN
953: fixed_relation_temp := 'TRUE';
954: ELSE
955: fixed_relation_temp := 'FALSE';
956: END IF;
957: cep_standard.debug('fixed_relation_temp ' ||fixed_relation_temp || ', curr_relation '||curr_relation);
958:
959: IF (fixed_relation) THEN
960: CE_AUTO_BANK_MATCH.csl_exchange_rate_type := 'EMU FIXED';
961: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
984: CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL AND
985: CE_AUTO_BANK_MATCH.csl_exchange_rate_date IS NULL AND
986: CE_AUTO_BANK_MATCH.csl_original_amount IS NULL) THEN
987:
988: cep_standard.debug('++CE_AUTO_BANK_REC.G_exchange_rate_type = ' ||
989: CE_AUTO_BANK_REC.G_exchange_rate_type);
990: cep_standard.debug('++CE_AUTO_BANK_REC.G_exchange_rate_date = ' ||
991: CE_AUTO_BANK_REC.G_exchange_rate_date);
992:
986: CE_AUTO_BANK_MATCH.csl_original_amount IS NULL) THEN
987:
988: cep_standard.debug('++CE_AUTO_BANK_REC.G_exchange_rate_type = ' ||
989: CE_AUTO_BANK_REC.G_exchange_rate_type);
990: cep_standard.debug('++CE_AUTO_BANK_REC.G_exchange_rate_date = ' ||
991: CE_AUTO_BANK_REC.G_exchange_rate_date);
992:
993: IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW') THEN
994: CE_AUTO_BANK_MATCH.csl_exchange_rate_type := CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE;
1073:
1074: CE_AUTO_BANK_MATCH.foreign_exchange_defaulted := 'Y';
1075:
1076: ELSE
1077: cep_standard.debug('MATCH.CSL_xtype: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1078: cep_standard.debug('MATCH.CSL_xdate: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_date);
1079:
1080: --
1081: -- line must have either xrate, original_amount or (xdate+xtype)
1074: CE_AUTO_BANK_MATCH.foreign_exchange_defaulted := 'Y';
1075:
1076: ELSE
1077: cep_standard.debug('MATCH.CSL_xtype: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1078: cep_standard.debug('MATCH.CSL_xdate: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_date);
1079:
1080: --
1081: -- line must have either xrate, original_amount or (xdate+xtype)
1082: --
1089: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1090: CE_AUTO_BANK_MATCH.csl_trx_date;
1091: CE_AUTO_BANK_MATCH.csl_exchange_rate_type := 'User';
1092:
1093: cep_standard.debug('xtype: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1094: cep_standard.debug('xdate: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_date);
1095:
1096: ELSE
1097:
1090: CE_AUTO_BANK_MATCH.csl_trx_date;
1091: CE_AUTO_BANK_MATCH.csl_exchange_rate_type := 'User';
1092:
1093: cep_standard.debug('xtype: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1094: cep_standard.debug('xdate: '||CE_AUTO_BANK_MATCH.csl_exchange_rate_date);
1095:
1096: ELSE
1097:
1098: IF (CE_AUTO_BANK_MATCH.csl_exchange_rate_type IS NULL) THEN
1116:
1117: --
1118: -- Rate Validation for emu and non-emu
1119: --
1120: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_type ='||CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1121:
1122: IF (CE_AUTO_BANK_MATCH.csl_exchange_rate_type = 'User') THEN
1123:
1124: IF (CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL) THEN
1136:
1137: ELSE
1138:
1139: BEGIN
1140: cep_standard.debug('CE_AUTO_BANK_REC.G_set_of_books_id = '||CE_AUTO_BANK_REC.G_set_of_books_id ||
1141: ', CE_AUTO_BANK_MATCH.trx_currency_code = '|| CE_AUTO_BANK_MATCH.trx_currency_code );
1142: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_date = '|| CE_AUTO_BANK_MATCH.csl_exchange_rate_date ||
1143: ', CE_AUTO_BANK_MATCH.csl_exchange_rate_type = '|| CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1144:
1138:
1139: BEGIN
1140: cep_standard.debug('CE_AUTO_BANK_REC.G_set_of_books_id = '||CE_AUTO_BANK_REC.G_set_of_books_id ||
1141: ', CE_AUTO_BANK_MATCH.trx_currency_code = '|| CE_AUTO_BANK_MATCH.trx_currency_code );
1142: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_exchange_rate_date = '|| CE_AUTO_BANK_MATCH.csl_exchange_rate_date ||
1143: ', CE_AUTO_BANK_MATCH.csl_exchange_rate_type = '|| CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1144:
1145: x_exchange_rate := gl_currency_api.get_rate(
1146: x_set_of_books_id => CE_AUTO_BANK_REC.G_set_of_books_id,
1147: x_from_currency => nvl(CE_AUTO_BANK_MATCH.trx_currency_code,CE_AUTO_BANK_MATCH.csl_currency_code),
1148: x_conversion_date => CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
1149: x_conversion_type => CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1150:
1151: cep_standard.debug('x_ex: '||x_exchange_rate);
1152: cep_standard.debug('csl_ex: '||CE_AUTO_BANK_MATCH.csl_exchange_rate);
1153:
1154: IF (CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL) THEN
1155: CE_AUTO_BANK_MATCH.csl_exchange_rate := x_exchange_rate;
1148: x_conversion_date => CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
1149: x_conversion_type => CE_AUTO_BANK_MATCH.csl_exchange_rate_type);
1150:
1151: cep_standard.debug('x_ex: '||x_exchange_rate);
1152: cep_standard.debug('csl_ex: '||CE_AUTO_BANK_MATCH.csl_exchange_rate);
1153:
1154: IF (CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL) THEN
1155: CE_AUTO_BANK_MATCH.csl_exchange_rate := x_exchange_rate;
1156: END IF;
1167:
1168: EXCEPTION
1169: WHEN OTHERS THEN
1170: IF (SQL%NOTFOUND) THEN
1171: cep_standard.debug('No Rate for the given criteria');
1172: --for bug 6786355 start
1173: CE_AUTO_BANK_MATCH.csl_exchange_rate_type := null;
1174: CE_AUTO_BANK_MATCH.csl_exchange_rate := null;
1175: CE_AUTO_BANK_MATCH.csl_exchange_rate_date := null;
1179: CE_AUTO_BANK_MATCH.csh_statement_header_id,
1180: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_OTHER_NO_RATE');
1181: RETURN(FALSE);
1182: ELSE
1183: cep_standard.debug('EXCEPTION:gl_currency_api.get_rate' );
1184: RAISE;
1185: END IF;
1186: END;
1187:
1186: END;
1187:
1188: END IF; -- user type
1189: return(TRUE);
1190: cep_standard.debug('<
1192: EXCEPTION
1193: WHEN OTHERS THEN
1194: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.validate_exchange_details');
1190: cep_standard.debug('<
1192: EXCEPTION
1193: WHEN OTHERS THEN
1194: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.validate_exchange_details');
1195: RAISE;
1196: END validate_exchange_details;
1197:
1198: /* ---------------------------------------------------------------------
1274: FUNCTION get_min_statement_line_id RETURN NUMBER IS
1275: min_statement_line NUMBER;
1276: min_statement_line_num NUMBER;
1277: BEGIN
1278: cep_standard.debug('>>CE_AUTO_BANK_MATCH.get_min_statement_line_id');
1279: SELECT min(line_number)
1280: INTO min_statement_line_num
1281: FROM ce_statement_lines
1282: WHERE statement_header_id = CE_AUTO_BANK_MATCH.csh_statement_header_id;
1285: INTO min_statement_line
1286: FROM ce_statement_lines
1287: WHERE line_number = min_statement_line_num
1288: AND statement_header_id = CE_AUTO_BANK_MATCH.csh_statement_header_id;
1289: cep_standard.debug('<
1291: RETURN (min_statement_line);
1292: EXCEPTION
1293: WHEN OTHERS THEN
1290:
1291: RETURN (min_statement_line);
1292: EXCEPTION
1293: WHEN OTHERS THEN
1294: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.get_min_statement_line_id');
1295: RAISE;
1296: END get_min_statement_line_id;
1297:
1298: /* ---------------------------------------------------------------------
1323: current_record_flag AR_CASH_RECEIPT_HISTORY_ALL.current_record_flag%TYPE;
1324: tx_status CE_LOOKUPS.lookup_code%TYPE;
1325: batch_ba_amount NUMBER;
1326: BEGIN
1327: cep_standard.debug('>>CE_AUTO_BANK_MATCH.lock_transaction');
1328: cep_standard.debug('X_RECONCILE_FLAG='||X_RECONCILE_FLAG ||
1329: ', X_CALL_MODE='|| X_CALL_MODE ||',X_TRX_TYPE='|| X_TRX_TYPE||
1330: ', X_CLEARING_TRX_TYPE='|| X_CLEARING_TRX_TYPE );
1331: cep_standard.debug('X_TRX_ROWID='|| X_TRX_ROWID||
1324: tx_status CE_LOOKUPS.lookup_code%TYPE;
1325: batch_ba_amount NUMBER;
1326: BEGIN
1327: cep_standard.debug('>>CE_AUTO_BANK_MATCH.lock_transaction');
1328: cep_standard.debug('X_RECONCILE_FLAG='||X_RECONCILE_FLAG ||
1329: ', X_CALL_MODE='|| X_CALL_MODE ||',X_TRX_TYPE='|| X_TRX_TYPE||
1330: ', X_CLEARING_TRX_TYPE='|| X_CLEARING_TRX_TYPE );
1331: cep_standard.debug('X_TRX_ROWID='|| X_TRX_ROWID||
1332: ', X_BATCH_BA_AMOUNT='||X_BATCH_BA_AMOUNT||
1327: cep_standard.debug('>>CE_AUTO_BANK_MATCH.lock_transaction');
1328: cep_standard.debug('X_RECONCILE_FLAG='||X_RECONCILE_FLAG ||
1329: ', X_CALL_MODE='|| X_CALL_MODE ||',X_TRX_TYPE='|| X_TRX_TYPE||
1330: ', X_CLEARING_TRX_TYPE='|| X_CLEARING_TRX_TYPE );
1331: cep_standard.debug('X_TRX_ROWID='|| X_TRX_ROWID||
1332: ', X_BATCH_BA_AMOUNT='||X_BATCH_BA_AMOUNT||
1333: ', X_MATCH_CORRECTION_TYPE='||X_MATCH_CORRECTION_TYPE);
1334:
1335: IF (X_reconcile_flag = 'Y') THEN
1378: END IF;
1379: CLOSE CE_AUTO_BANK_MATCH.lock_200;
1380: END IF;
1381: ELSIF (X_trx_type IN ('MISC','CASH'))THEN
1382: cep_standard.debug('open lock_222');
1383: OPEN CE_AUTO_BANK_MATCH.lock_222(X_CALL_MODE, x_trx_rowid);
1384: FETCH CE_AUTO_BANK_MATCH.lock_222 INTO id1, id2, current_record_flag;
1385: cep_standard.debug('id1 '||id1);
1386: cep_standard.debug('id2 '||id2);
1381: ELSIF (X_trx_type IN ('MISC','CASH'))THEN
1382: cep_standard.debug('open lock_222');
1383: OPEN CE_AUTO_BANK_MATCH.lock_222(X_CALL_MODE, x_trx_rowid);
1384: FETCH CE_AUTO_BANK_MATCH.lock_222 INTO id1, id2, current_record_flag;
1385: cep_standard.debug('id1 '||id1);
1386: cep_standard.debug('id2 '||id2);
1387: cep_standard.debug('current_record_flag '||current_record_flag);
1388:
1389: IF (CE_AUTO_BANK_MATCH.lock_222%NOTFOUND) THEN
1382: cep_standard.debug('open lock_222');
1383: OPEN CE_AUTO_BANK_MATCH.lock_222(X_CALL_MODE, x_trx_rowid);
1384: FETCH CE_AUTO_BANK_MATCH.lock_222 INTO id1, id2, current_record_flag;
1385: cep_standard.debug('id1 '||id1);
1386: cep_standard.debug('id2 '||id2);
1387: cep_standard.debug('current_record_flag '||current_record_flag);
1388:
1389: IF (CE_AUTO_BANK_MATCH.lock_222%NOTFOUND) THEN
1390: RAISE NO_DATA_FOUND;
1383: OPEN CE_AUTO_BANK_MATCH.lock_222(X_CALL_MODE, x_trx_rowid);
1384: FETCH CE_AUTO_BANK_MATCH.lock_222 INTO id1, id2, current_record_flag;
1385: cep_standard.debug('id1 '||id1);
1386: cep_standard.debug('id2 '||id2);
1387: cep_standard.debug('current_record_flag '||current_record_flag);
1388:
1389: IF (CE_AUTO_BANK_MATCH.lock_222%NOTFOUND) THEN
1390: RAISE NO_DATA_FOUND;
1391: END IF;
1406: END IF;
1407: CLOSE CE_AUTO_BANK_MATCH.lock_260;
1408: elsif (nvl(CE_AUTO_BANK_MATCH.csl_match_correction_type, 'NONE')
1409: = 'ADJUSTMENT') then
1410: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.trx_rowid2='||
1411: CE_AUTO_BANK_MATCH.trx_rowid2);
1412: OPEN CE_AUTO_BANK_MATCH.lock_260(X_CALL_MODE,
1413: CE_AUTO_BANK_MATCH.trx_rowid2);
1414: FETCH CE_AUTO_BANK_MATCH.lock_260 INTO id1;
1443: end if;
1444: end if; -- CE_AUTO_BANK_MATCH.reconciled_this_run is NULL
1445: end if;
1446: ELSIF (X_trx_type = 'PBATCH') THEN
1447: cep_standard.debug('open lock_batch_checks X_trx_rowid='|| X_trx_rowid);
1448: OPEN CE_AUTO_BANK_MATCH.lock_batch_checks(X_trx_rowid);
1449: FETCH CE_AUTO_BANK_MATCH.lock_batch_checks INTO id1, id2;
1450: IF (CE_AUTO_BANK_MATCH.lock_batch_checks%NOTFOUND) THEN
1451: cep_standard.debug('no_data_found for lock_batch_checks');
1447: cep_standard.debug('open lock_batch_checks X_trx_rowid='|| X_trx_rowid);
1448: OPEN CE_AUTO_BANK_MATCH.lock_batch_checks(X_trx_rowid);
1449: FETCH CE_AUTO_BANK_MATCH.lock_batch_checks INTO id1, id2;
1450: IF (CE_AUTO_BANK_MATCH.lock_batch_checks%NOTFOUND) THEN
1451: cep_standard.debug('no_data_found for lock_batch_checks');
1452:
1453: RAISE NO_DATA_FOUND;
1454: END IF;
1455: cep_standard.debug('open check_amounts id2='||id2);
1451: cep_standard.debug('no_data_found for lock_batch_checks');
1452:
1453: RAISE NO_DATA_FOUND;
1454: END IF;
1455: cep_standard.debug('open check_amounts id2='||id2);
1456: OPEN CE_AUTO_BANK_MATCH.check_amounts(id2);
1457: FETCH CE_AUTO_BANK_MATCH.check_amounts INTO batch_ba_amount;
1458: IF (CE_AUTO_BANK_MATCH.check_amounts%NOTFOUND) THEN
1459: cep_standard.debug('EKA NO DATA');
1455: cep_standard.debug('open check_amounts id2='||id2);
1456: OPEN CE_AUTO_BANK_MATCH.check_amounts(id2);
1457: FETCH CE_AUTO_BANK_MATCH.check_amounts INTO batch_ba_amount;
1458: IF (CE_AUTO_BANK_MATCH.check_amounts%NOTFOUND) THEN
1459: cep_standard.debug('EKA NO DATA');
1460: RAISE NO_DATA_FOUND;
1461: END IF;
1462:
1463: cep_standard.debug('batch_ba_amount='||batch_ba_amount||', X_batch_ba_amount='||X_batch_ba_amount );
1459: cep_standard.debug('EKA NO DATA');
1460: RAISE NO_DATA_FOUND;
1461: END IF;
1462:
1463: cep_standard.debug('batch_ba_amount='||batch_ba_amount||', X_batch_ba_amount='||X_batch_ba_amount );
1464:
1465: IF ((batch_ba_amount = X_batch_ba_amount) OR
1466: ((batch_ba_amount IS NULL) AND (X_batch_ba_amount IS NULL))) THEN
1467: NULL;
1465: IF ((batch_ba_amount = X_batch_ba_amount) OR
1466: ((batch_ba_amount IS NULL) AND (X_batch_ba_amount IS NULL))) THEN
1467: NULL;
1468: ELSE
1469: cep_standard.debug('TOKA NO DATA batch_ba_amount <> X_batch_ba_amount ');
1470: RAISE NO_DATA_FOUND;
1471: END IF;
1472: CLOSE CE_AUTO_BANK_MATCH.Check_Amounts;
1473: CLOSE CE_AUTO_BANK_MATCH.Lock_Batch_Checks;
1471: END IF;
1472: CLOSE CE_AUTO_BANK_MATCH.Check_Amounts;
1473: CLOSE CE_AUTO_BANK_MATCH.Lock_Batch_Checks;
1474: ELSIF (X_trx_type = 'PGROUP') THEN -- SEPA ER 6700007
1475: cep_standard.debug('open lock_group_checks X_trx_rowid='|| X_trx_rowid||' X_LOGICAL_GROUP_REFERENCE-'||X_LOGICAL_GROUP_REFERENCE);
1476: OPEN CE_AUTO_BANK_MATCH.lock_group_checks(X_trx_rowid,X_LOGICAL_GROUP_REFERENCE);
1477: FETCH CE_AUTO_BANK_MATCH.lock_group_checks INTO id1, id2;
1478: IF (CE_AUTO_BANK_MATCH.lock_group_checks%NOTFOUND) THEN
1479: cep_standard.debug('no_data_found for lock_group_checks');
1475: cep_standard.debug('open lock_group_checks X_trx_rowid='|| X_trx_rowid||' X_LOGICAL_GROUP_REFERENCE-'||X_LOGICAL_GROUP_REFERENCE);
1476: OPEN CE_AUTO_BANK_MATCH.lock_group_checks(X_trx_rowid,X_LOGICAL_GROUP_REFERENCE);
1477: FETCH CE_AUTO_BANK_MATCH.lock_group_checks INTO id1, id2;
1478: IF (CE_AUTO_BANK_MATCH.lock_group_checks%NOTFOUND) THEN
1479: cep_standard.debug('no_data_found for lock_group_checks');
1480:
1481: RAISE NO_DATA_FOUND;
1482: END IF;
1483: cep_standard.debug('open check_amounts id2='||id2);
1479: cep_standard.debug('no_data_found for lock_group_checks');
1480:
1481: RAISE NO_DATA_FOUND;
1482: END IF;
1483: cep_standard.debug('open check_amounts id2='||id2);
1484: OPEN CE_AUTO_BANK_MATCH.check_group_amounts(id2,X_LOGICAL_GROUP_REFERENCE);
1485: FETCH CE_AUTO_BANK_MATCH.check_group_amounts INTO batch_ba_amount;
1486: IF (CE_AUTO_BANK_MATCH.check_group_amounts%NOTFOUND) THEN
1487: cep_standard.debug('EKA NO DATA');
1483: cep_standard.debug('open check_amounts id2='||id2);
1484: OPEN CE_AUTO_BANK_MATCH.check_group_amounts(id2,X_LOGICAL_GROUP_REFERENCE);
1485: FETCH CE_AUTO_BANK_MATCH.check_group_amounts INTO batch_ba_amount;
1486: IF (CE_AUTO_BANK_MATCH.check_group_amounts%NOTFOUND) THEN
1487: cep_standard.debug('EKA NO DATA');
1488: RAISE NO_DATA_FOUND;
1489: END IF;
1490:
1491: cep_standard.debug('batch_ba_amount='||batch_ba_amount||', X_batch_ba_amount='||X_batch_ba_amount );
1487: cep_standard.debug('EKA NO DATA');
1488: RAISE NO_DATA_FOUND;
1489: END IF;
1490:
1491: cep_standard.debug('batch_ba_amount='||batch_ba_amount||', X_batch_ba_amount='||X_batch_ba_amount );
1492:
1493: IF ((batch_ba_amount = X_batch_ba_amount) OR
1494: ((batch_ba_amount IS NULL) AND (X_batch_ba_amount IS NULL))) THEN
1495: NULL;
1493: IF ((batch_ba_amount = X_batch_ba_amount) OR
1494: ((batch_ba_amount IS NULL) AND (X_batch_ba_amount IS NULL))) THEN
1495: NULL;
1496: ELSE
1497: cep_standard.debug('TOKA NO DATA batch_ba_amount <> X_batch_ba_amount ');
1498: RAISE NO_DATA_FOUND;
1499: END IF;
1500: CLOSE CE_AUTO_BANK_MATCH.Check_GROUP_Amounts;
1501: CLOSE CE_AUTO_BANK_MATCH.Lock_group_Checks;
1499: END IF;
1500: CLOSE CE_AUTO_BANK_MATCH.Check_GROUP_Amounts;
1501: CLOSE CE_AUTO_BANK_MATCH.Lock_group_Checks;
1502: ELSIF (X_trx_type = 'RBATCH') THEN
1503: cep_standard.debug('open lock_batch_receipts X_trx_rowid='|| X_trx_rowid);
1504: OPEN CE_AUTO_BANK_MATCH.lock_batch_receipts(X_trx_rowid);
1505: FETCH CE_AUTO_BANK_MATCH.lock_batch_receipts INTO id1, id2, id3;
1506: IF (CE_AUTO_BANK_MATCH.lock_batch_receipts%NOTFOUND) THEN
1507: RAISE NO_DATA_FOUND;
1506: IF (CE_AUTO_BANK_MATCH.lock_batch_receipts%NOTFOUND) THEN
1507: RAISE NO_DATA_FOUND;
1508: END IF;
1509:
1510: cep_standard.debug('open receipt_amounts id3='||id3);
1511:
1512: OPEN CE_AUTO_BANK_MATCH.receipt_amounts(id3);
1513: FETCH CE_AUTO_BANK_MATCH.receipt_amounts INTO batch_ba_amount;
1514: IF (CE_AUTO_BANK_MATCH.receipt_amounts%NOTFOUND) THEN
1514: IF (CE_AUTO_BANK_MATCH.receipt_amounts%NOTFOUND) THEN
1515: RAISE NO_DATA_FOUND;
1516: END IF;
1517:
1518: cep_standard.debug('batch_ba_amount='||batch_ba_amount||', X_batch_ba_amount='||X_batch_ba_amount );
1519:
1520: IF ((batch_ba_amount = X_batch_ba_amount) OR
1521: ((batch_ba_amount IS NULL) AND (X_batch_ba_amount IS NULL))) THEN
1522: NULL;
1520: IF ((batch_ba_amount = X_batch_ba_amount) OR
1521: ((batch_ba_amount IS NULL) AND (X_batch_ba_amount IS NULL))) THEN
1522: NULL;
1523: ELSE
1524: cep_standard.debug(' remittance batch no_data_found batch_ba_amount <> X_batch_ba_amount ');
1525: RAISE NO_DATA_FOUND;
1526: END IF;
1527: CLOSE CE_AUTO_BANK_MATCH.Receipt_Amounts;
1528: CLOSE CE_AUTO_BANK_MATCH.Lock_Batch_Receipts;
1569: END IF;
1570: CLOSE CE_AUTO_BANK_MATCH.clear_lock_260;
1571: END IF;
1572: END IF;
1573: cep_standard.debug('<
1575: WHEN NO_DATA_FOUND THEN
1576: cep_standard.debug('Transaction Either Deleted OR Reconciled');
1577: CE_AUTO_BANK_MATCH.close_cursors;
1572: END IF;
1573: cep_standard.debug('<
1575: WHEN NO_DATA_FOUND THEN
1576: cep_standard.debug('Transaction Either Deleted OR Reconciled');
1577: CE_AUTO_BANK_MATCH.close_cursors;
1578: RAISE NO_DATA_FOUND;
1579: WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1580: cep_standard.debug('Could not lock transactions');
1576: cep_standard.debug('Transaction Either Deleted OR Reconciled');
1577: CE_AUTO_BANK_MATCH.close_cursors;
1578: RAISE NO_DATA_FOUND;
1579: WHEN APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION THEN
1580: cep_standard.debug('Could not lock transactions');
1581: CE_AUTO_BANK_MATCH.close_cursors;
1582: RAISE APP_EXCEPTIONS.RECORD_LOCK_EXCEPTION;
1583: END lock_transaction;
1584:
1743: curr NUMBER;
1744: tx_type VARCHAR2(30);
1745: le_found NUMBER;
1746: BEGIN
1747: cep_standard.debug('>>CE_AUTO_BANK_MATCH.ce_match');
1748:
1749: no_of_matches := 0;
1750: le_found := 0;
1751:
1748:
1749: no_of_matches := 0;
1750: le_found := 0;
1751:
1752: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type = '|| CE_AUTO_BANK_MATCH.csl_trx_type
1753: ||' CE_AUTO_BANK_MATCH.csl_bank_trx_number = '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
1754: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id = '|| CE_AUTO_BANK_MATCH.csh_bank_account_id
1755: ||', CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date
1756: ||', CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
1750: le_found := 0;
1751:
1752: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type = '|| CE_AUTO_BANK_MATCH.csl_trx_type
1753: ||' CE_AUTO_BANK_MATCH.csl_bank_trx_number = '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
1754: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id = '|| CE_AUTO_BANK_MATCH.csh_bank_account_id
1755: ||', CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date
1756: ||', CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
1757:
1758: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP','SWEEP_OUT') AND
1765: tx_type := 'MISC';
1766: END IF;
1767:
1768:
1769: cep_standard.debug('>>MATCH ce trx by statement_line_id');
1770: curr:=1;
1771: OPEN stmt_ln_id_cur(tx_type);
1772: FETCH stmt_ln_id_cur
1773: INTO CE_AUTO_BANK_MATCH.trx_id,
1796: CLOSE stmt_ln_id_cur;
1797:
1798:
1799: IF (no_of_matches = 0) THEN
1800: cep_standard.debug('>>MATCH ce trx by transaction number, date and amount');
1801: curr:=2;
1802: OPEN trx_num_date_amt_cur(tx_type);
1803: FETCH trx_num_date_amt_cur
1804: INTO CE_AUTO_BANK_MATCH.trx_id,
1829: END IF;
1830:
1831:
1832: IF (no_of_matches = 0) THEN
1833: cep_standard.debug('>>MATCH ce trx by agent bank account, date and amount');
1834: curr:=3;
1835: OPEN agent_ba_date_amt_cur(tx_type);
1836: FETCH agent_ba_date_amt_cur
1837: INTO CE_AUTO_BANK_MATCH.trx_id,
1861: CLOSE agent_ba_date_amt_cur;
1862:
1863: END IF;
1864:
1865: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_id = '|| CE_AUTO_BANK_MATCH.trx_id
1866: ||', CE_AUTO_BANK_MATCH.trx_amount = '|| CE_AUTO_BANK_MATCH.trx_amount
1867: );
1868: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type = '|| CE_AUTO_BANK_MATCH.csl_match_type
1869: ||', no_of_matches = '|| no_of_matches
1864:
1865: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_id = '|| CE_AUTO_BANK_MATCH.trx_id
1866: ||', CE_AUTO_BANK_MATCH.trx_amount = '|| CE_AUTO_BANK_MATCH.trx_amount
1867: );
1868: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type = '|| CE_AUTO_BANK_MATCH.csl_match_type
1869: ||', no_of_matches = '|| no_of_matches
1870: ||', CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date
1871: );
1872:
1910: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <>
1911: CE_AUTO_BANK_REC.G_functional_currency) and
1912: (CE_AUTO_BANK_MATCH.aba_bank_currency <>
1913: CE_AUTO_BANK_MATCH.trx_currency_code) THEN
1914: cep_standard.debug('Forex account not using the same curr as bk');
1915: curr := 6;
1916: RAISE NO_DATA_FOUND;
1917: END IF;
1918:
1915: curr := 6;
1916: RAISE NO_DATA_FOUND;
1917: END IF;
1918:
1919: cep_standard.debug('<
1921: EXCEPTION
1922: WHEN NO_DATA_FOUND THEN
1923: cep_standard.debug('EXCEPTION - NO_DATA_FOUND: No data found in CE_AUTO_BANK_MATCH.ce_match');
1919: cep_standard.debug('<
1921: EXCEPTION
1922: WHEN NO_DATA_FOUND THEN
1923: cep_standard.debug('EXCEPTION - NO_DATA_FOUND: No data found in CE_AUTO_BANK_MATCH.ce_match');
1924: if (curr = 6) then
1925: CE_RECONCILIATION_ERRORS_PKG.insert_row(
1926: CE_AUTO_BANK_MATCH.csh_statement_header_id,
1927: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
1932: end if;
1933: no_of_matches := 0;
1934: WHEN OTHERS THEN
1935: IF (SQL%NOTFOUND) THEN
1936: cep_standard.debug('EXCEPTION - OTHERS: NO data found in CE_AUTO_BANK_MATCH.ce_match');
1937: CE_RECONCILIATION_ERRORS_PKG.insert_row(
1938: CE_AUTO_BANK_MATCH.csh_statement_header_id,
1939: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_TRX_MATCH');
1940: no_of_matches:=0;
1938: CE_AUTO_BANK_MATCH.csh_statement_header_id,
1939: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_TRX_MATCH');
1940: no_of_matches:=0;
1941: ELSIF (SQL%ROWCOUNT >0) THEN
1942: cep_standard.debug('EXCEPTION: More than one CE trx match this statement line' );
1943:
1944: CE_RECONCILIATION_ERRORS_PKG.insert_row(
1945: CE_AUTO_BANK_MATCH.csh_statement_header_id,
1946: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_DUP_CE_TRX_MATCH');
1945: CE_AUTO_BANK_MATCH.csh_statement_header_id,
1946: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_DUP_CE_TRX_MATCH');
1947: no_of_matches:=999;
1948: ELSE
1949: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.ce_match' );
1950: RAISE;
1951: END IF;
1952: END ce_match;
1953:
1968: trx_count NUMBER;
1969: curr NUMBER;
1970:
1971: BEGIN
1972: cep_standard.debug('>>CE_AUTO_BANK_MATCH.pay_eft_match');
1973: no_of_matches := 0;
1974: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type = '|| CE_AUTO_BANK_MATCH.csl_trx_type
1975: ||' CE_AUTO_BANK_MATCH.csl_bank_trx_number = '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
1976: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id = '|| CE_AUTO_BANK_MATCH.csh_bank_account_id
1970:
1971: BEGIN
1972: cep_standard.debug('>>CE_AUTO_BANK_MATCH.pay_eft_match');
1973: no_of_matches := 0;
1974: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type = '|| CE_AUTO_BANK_MATCH.csl_trx_type
1975: ||' CE_AUTO_BANK_MATCH.csl_bank_trx_number = '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
1976: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id = '|| CE_AUTO_BANK_MATCH.csh_bank_account_id
1977: ||', CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date
1978: ||', CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
1972: cep_standard.debug('>>CE_AUTO_BANK_MATCH.pay_eft_match');
1973: no_of_matches := 0;
1974: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type = '|| CE_AUTO_BANK_MATCH.csl_trx_type
1975: ||' CE_AUTO_BANK_MATCH.csl_bank_trx_number = '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
1976: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id = '|| CE_AUTO_BANK_MATCH.csh_bank_account_id
1977: ||', CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date
1978: ||', CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
1979:
1980: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT')) THEN
1983: ELSE
1984: curr := 2;
1985: END IF;
1986:
1987: cep_standard.debug('>>MATCH trx ');
1988:
1989: SELECT count(*),
1990: sum(catv.bank_account_amount),
1991: nvl(sum(catv.base_amount),0),
2024: group by catv.batch_id, catv.batch_name, catv.trx_date, catv.org_id, catv.ce_bank_acct_use_id; -- bug 7242853
2025:
2026: END IF;
2027:
2028: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_count = '|| CE_AUTO_BANK_MATCH.trx_count
2029: ||', CE_AUTO_BANK_MATCH.trx_amount = '|| CE_AUTO_BANK_MATCH.trx_amount
2030: ||', no_of_currencies = '|| no_of_currencies);
2031: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type = '|| CE_AUTO_BANK_MATCH.csl_match_type
2032: ||', no_of_matches = '|| no_of_matches
2027:
2028: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_count = '|| CE_AUTO_BANK_MATCH.trx_count
2029: ||', CE_AUTO_BANK_MATCH.trx_amount = '|| CE_AUTO_BANK_MATCH.trx_amount
2030: ||', no_of_currencies = '|| no_of_currencies);
2031: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type = '|| CE_AUTO_BANK_MATCH.csl_match_type
2032: ||', no_of_matches = '|| no_of_matches
2033: ||', CE_AUTO_BANK_MATCH.csl_trx_date = '|| CE_AUTO_BANK_MATCH.csl_trx_date
2034: ||', CE_AUTO_BANK_MATCH.trx_group = '|| CE_AUTO_BANK_MATCH.trx_group);
2035:
2042: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <>
2043: CE_AUTO_BANK_REC.G_functional_currency) and
2044: (CE_AUTO_BANK_MATCH.aba_bank_currency <>
2045: CE_AUTO_BANK_MATCH.trx_currency_code) THEN
2046: cep_standard.debug('Forex account not using the same curr as bk');
2047: curr := 6;
2048: RAISE NO_DATA_FOUND;
2049: END IF;
2050:
2047: curr := 6;
2048: RAISE NO_DATA_FOUND;
2049: END IF;
2050:
2051: cep_standard.debug('<
2053: EXCEPTION
2054: WHEN NO_DATA_FOUND THEN
2055: cep_standard.debug('EXCEPTION - NO_DATA_FOUND: No data found in CE_AUTO_BANK_MATCH.pay_eft_match');
2051: cep_standard.debug('<
2053: EXCEPTION
2054: WHEN NO_DATA_FOUND THEN
2055: cep_standard.debug('EXCEPTION - NO_DATA_FOUND: No data found in CE_AUTO_BANK_MATCH.pay_eft_match');
2056: if (curr = 6) then
2057: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2058: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2059: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
2068: end if;
2069: no_of_matches := 0;
2070: WHEN OTHERS THEN
2071: IF (SQL%NOTFOUND) THEN
2072: cep_standard.debug('EXCEPTION - OTHERS: NO data found in CE_AUTO_BANK_MATCH.pay_eft_match');
2073: if (curr = 1) 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_BATCH_BACS');
2080: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_BATCH_NACHA');
2081: end if;
2082: no_of_matches:=0;
2083: ELSIF (SQL%ROWCOUNT >0) THEN
2084: cep_standard.debug('EXCEPTION: More than one EFT batch match this payment' );
2085: -- if (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) then
2086: if (curr = 1) then
2087: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2088: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2093: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_DUP_BATCH_NACHA');
2094: end if;
2095: no_of_matches:=999;
2096: ELSE
2097: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.pay_eft_match' );
2098: RAISE;
2099: END IF;
2100: END pay_eft_match;
2101:
2113: --------------------------------------------------------------------- */
2114: PROCEDURE bank_account_match(no_of_matches OUT NOCOPY NUMBER) IS
2115: curr NUMBER;
2116: BEGIN
2117: cep_standard.debug('>>CE_AUTO_BANK_MATCH.bank_account_match');
2118: no_of_matches := 1;
2119: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) THEN
2120: SELECT DISTINCT(c.check_id),
2121: to_number(NULL),
2352: IF ((CE_AUTO_BANK_MATCH.aba_bank_currency <>
2353: CE_AUTO_BANK_REC.G_functional_currency) and
2354: (CE_AUTO_BANK_MATCH.aba_bank_currency <>
2355: CE_AUTO_BANK_MATCH.trx_currency_code)) THEN
2356: cep_standard.debug('Forex account not using the same curr as bk');
2357: curr := 1;
2358: RAISE NO_DATA_FOUND;
2359: END IF;
2360:
2357: curr := 1;
2358: RAISE NO_DATA_FOUND;
2359: END IF;
2360:
2361: cep_standard.debug('<
2363: WHEN NO_DATA_FOUND THEN
2364: cep_standard.debug('EXCEPTION: No data found');
2365: if (curr = 1) then
2360:
2361: cep_standard.debug('<
2363: WHEN NO_DATA_FOUND THEN
2364: cep_standard.debug('EXCEPTION: No data found');
2365: if (curr = 1) then
2366: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2367: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2368: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
2366: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2367: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2368: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
2369: end if;
2370: cep_standard.debug('EXCEPTION: NO bank account match this receipt');
2371: if (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) then
2372: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2373: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2374: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_BAP');
2379: end if;
2380: no_of_matches := 0;
2381: WHEN OTHERS THEN
2382: IF (SQL%ROWCOUNT > 0) THEN
2383: cep_standard.debug('EXCEPTION: More than one bank account match this transaction');
2384: if (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) then
2385: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2386: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2387: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APT_PARTIAL');
2391: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_ART_PARTIAL');
2392: end if;
2393: no_of_matches:=999;
2394: ELSE
2395: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.bank_account_match' );
2396: RAISE;
2397: END IF;
2398: END bank_account_match;
2399:
2410: --------------------------------------------------------------------- */
2411: PROCEDURE invoice_match (no_of_matches OUT NOCOPY NUMBER) IS
2412: curr NUMBER;
2413: BEGIN
2414: cep_standard.debug('>>CE_AUTO_BANK_MATCH.invoice_match');
2415: no_of_matches := 1;
2416: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) THEN
2417: SELECT DISTINCT(c.check_id),
2418: to_number(NULL),
2647: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <>
2648: CE_AUTO_BANK_REC.G_functional_currency) and
2649: (CE_AUTO_BANK_MATCH.aba_bank_currency <>
2650: CE_AUTO_BANK_MATCH.trx_currency_code) THEN
2651: cep_standard.debug('Forex account not using the same curr as bk');
2652: curr := 1;
2653: RAISE NO_DATA_FOUND;
2654: END IF;
2655:
2652: curr := 1;
2653: RAISE NO_DATA_FOUND;
2654: END IF;
2655:
2656: cep_standard.debug('<
2658: WHEN NO_DATA_FOUND THEN
2659: cep_standard.debug('EXCEPTION: No data found');
2660: if (curr = 1) then
2655:
2656: cep_standard.debug('<
2658: WHEN NO_DATA_FOUND THEN
2659: cep_standard.debug('EXCEPTION: No data found');
2660: if (curr = 1) 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_NO_FOREIGN_RECON');
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_NO_FOREIGN_RECON');
2664: end if;
2665: cep_standard.debug('EXCEPTION: NO invoices match this receipt');
2666: if (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) then
2667: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2668: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2669: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_INP');
2674: end if;
2675: no_of_matches := 0;
2676: WHEN OTHERS THEN
2677: IF (SQL%ROWCOUNT >0) THEN
2678: cep_standard.debug('EXCEPTION: More than one invoice match this transaction');
2679: if (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) then
2680: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2681: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2682: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APT_PARTIAL');
2686: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_ART_PARTIAL');
2687: end if;
2688: no_of_matches:=999;
2689: ELSE
2690: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.invoice_match' );
2691: RAISE;
2692: END IF;
2693: END invoice_match;
2694:
2722: trx_curr_amount_ap NUMBER;
2723: trx_curr_amount_ce NUMBER;
2724:
2725: BEGIN
2726: cep_standard.debug('>>CE_AUTO_BANK_MATCH.batch_match csl_trx_type='||CE_AUTO_BANK_MATCH.csl_trx_type);
2727: no_of_matches := 0;
2728:
2729: IF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'PAY_EFT') THEN
2730: pay_eft_match(no_of_matches, no_of_currencies);
2827: CE_AUTO_BANK_MATCH.trx_cleared_amount := trx_cleared_amount_ap + trx_cleared_amount_ce;
2828: no_of_currencies := no_of_currencies_ap + no_of_currencies_ce;
2829: CE_AUTO_BANK_MATCH.trx_curr_amount := trx_curr_amount_ap + trx_curr_amount_ce;
2830:
2831: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_amount='|| CE_AUTO_BANK_MATCH.trx_amount);
2832:
2833: ELSIF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('CREDIT','NSF','REJECTED')) THEN
2834:
2835: curr := 3;
2885: curr := 5;
2886: IF (trx_count = 0) THEN
2887: RAISE NO_DATA_FOUND;
2888: END IF;
2889: cep_standard.debug('Batch trx_count = '||trx_count);
2890: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
2891: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
2892: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
2893: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
2886: IF (trx_count = 0) THEN
2887: RAISE NO_DATA_FOUND;
2888: END IF;
2889: cep_standard.debug('Batch trx_count = '||trx_count);
2890: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
2891: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
2892: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
2893: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
2894: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
2887: RAISE NO_DATA_FOUND;
2888: END IF;
2889: cep_standard.debug('Batch trx_count = '||trx_count);
2890: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
2891: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
2892: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
2893: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
2894: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
2895:
2888: END IF;
2889: cep_standard.debug('Batch trx_count = '||trx_count);
2890: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
2891: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
2892: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
2893: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
2894: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
2895:
2896: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <>
2889: cep_standard.debug('Batch trx_count = '||trx_count);
2890: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
2891: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
2892: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
2893: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
2894: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
2895:
2896: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <>
2897: CE_AUTO_BANK_REC.G_functional_currency) and
2890: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
2891: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
2892: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
2893: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
2894: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
2895:
2896: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <>
2897: CE_AUTO_BANK_REC.G_functional_currency) and
2898: (CE_AUTO_BANK_MATCH.aba_bank_currency <>
2896: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <>
2897: CE_AUTO_BANK_REC.G_functional_currency) and
2898: (CE_AUTO_BANK_MATCH.aba_bank_currency <>
2899: CE_AUTO_BANK_MATCH.trx_currency_code) THEN
2900: cep_standard.debug('Forex account not using the same curr as bk');
2901: curr := 6;
2902: RAISE NO_DATA_FOUND;
2903: END IF;
2904:
2901: curr := 6;
2902: RAISE NO_DATA_FOUND;
2903: END IF;
2904:
2905: cep_standard.debug('<
2907: EXCEPTION
2908: WHEN NO_DATA_FOUND THEN
2909: cep_standard.debug('EXCEPTION: No data found');
2905: cep_standard.debug('<
2907: EXCEPTION
2908: WHEN NO_DATA_FOUND THEN
2909: cep_standard.debug('EXCEPTION: No data found');
2910: if (curr = 6) then
2911: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2912: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2913: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
2922: end if;
2923: no_of_matches := 0;
2924: WHEN OTHERS THEN
2925: IF (SQL%NOTFOUND) THEN
2926: cep_standard.debug('EXCEPTION: NO data found in batch_match');
2927: if (curr = 1) then
2928: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2929: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2930: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_BATCH_P');
2934: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_BATCH_R');
2935: end if;
2936: no_of_matches:=0;
2937: ELSIF (SQL%ROWCOUNT >0) THEN
2938: cep_standard.debug('EXCEPTION: More than one batch match this receipt' );
2939: if (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP')) then
2940: CE_RECONCILIATION_ERRORS_PKG.insert_row(
2941: CE_AUTO_BANK_MATCH.csh_statement_header_id,
2942: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APB_PARTIAL');
2946: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_ARB_PARTIAL');
2947: end if;
2948: no_of_matches:=999;
2949: ELSE
2950: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.batch_match' );
2951: RAISE;
2952: END IF;
2953: END batch_match;
2954:
2971: trx_count NUMBER;
2972: curr NUMBER;
2973:
2974: BEGIN
2975: cep_standard.debug('>>CE_AUTO_BANK_MATCH.group_match csl_trx_type='||CE_AUTO_BANK_MATCH.csl_trx_type);
2976: no_of_matches := 0;
2977:
2978: curr := 1;
2979: --
3048: AND IPA.PAYMENT_ID = ACA.PAYMENT_ID
3049: AND IPA.LOGICAL_GROUP_REFERENCE = NVL(CE_AUTO_BANK_MATCH.LOGICAL_GROUP_REFERENCE,IPA.LOGICAL_GROUP_REFERENCE));
3050:
3051: IF (trx_count = 0) THEN
3052: cep_standard.debug('No trx for group '||CE_AUTO_BANK_MATCH.csl_bank_trx_number);
3053: RAISE NO_DATA_FOUND;
3054: END IF;
3055: cep_standard.debug('Group trx_count = '||trx_count);
3056: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
3051: IF (trx_count = 0) THEN
3052: cep_standard.debug('No trx for group '||CE_AUTO_BANK_MATCH.csl_bank_trx_number);
3053: RAISE NO_DATA_FOUND;
3054: END IF;
3055: cep_standard.debug('Group trx_count = '||trx_count);
3056: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
3057: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
3058: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
3059: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
3052: cep_standard.debug('No trx for group '||CE_AUTO_BANK_MATCH.csl_bank_trx_number);
3053: RAISE NO_DATA_FOUND;
3054: END IF;
3055: cep_standard.debug('Group trx_count = '||trx_count);
3056: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
3057: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
3058: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
3059: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
3060: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
3053: RAISE NO_DATA_FOUND;
3054: END IF;
3055: cep_standard.debug('Group trx_count = '||trx_count);
3056: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
3057: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
3058: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
3059: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
3060: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
3061:
3054: END IF;
3055: cep_standard.debug('Group trx_count = '||trx_count);
3056: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
3057: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
3058: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
3059: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
3060: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
3061:
3062: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency) AND
3055: cep_standard.debug('Group trx_count = '||trx_count);
3056: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
3057: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
3058: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
3059: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
3060: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
3061:
3062: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency) AND
3063: (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_MATCH.trx_currency_code)
3056: cep_standard.debug('trx_amount = '||CE_AUTO_BANK_MATCH.trx_amount);
3057: cep_standard.debug('trx_base_amount = '||CE_AUTO_BANK_MATCH.trx_base_amount);
3058: cep_standard.debug('trx_cleared_amount = '||CE_AUTO_BANK_MATCH.trx_cleared_amount);
3059: cep_standard.debug('trx_curr_amount = '||CE_AUTO_BANK_MATCH.trx_curr_amount);
3060: cep_standard.debug('csl_match_type = '||CE_AUTO_BANK_MATCH.csl_match_type);
3061:
3062: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency) AND
3063: (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_MATCH.trx_currency_code)
3064: THEN
3061:
3062: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_REC.G_functional_currency) AND
3063: (CE_AUTO_BANK_MATCH.aba_bank_currency <> CE_AUTO_BANK_MATCH.trx_currency_code)
3064: THEN
3065: cep_standard.debug('Forex trx not using the same curr as account');
3066: curr := 6;
3067: RAISE NO_DATA_FOUND;
3068: END IF;
3069:
3066: curr := 6;
3067: RAISE NO_DATA_FOUND;
3068: END IF;
3069:
3070: cep_standard.debug('<
3072: EXCEPTION
3073: WHEN NO_DATA_FOUND THEN
3074: cep_standard.debug('EXCEPTION: #1 No data found in group_match');
3070: cep_standard.debug('<
3072: EXCEPTION
3073: WHEN NO_DATA_FOUND THEN
3074: cep_standard.debug('EXCEPTION: #1 No data found in group_match');
3075: cep_standard.debug('curr = '||curr);
3076: if (curr = 6) then
3077: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3078: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3071:
3072: EXCEPTION
3073: WHEN NO_DATA_FOUND THEN
3074: cep_standard.debug('EXCEPTION: #1 No data found in group_match');
3075: cep_standard.debug('curr = '||curr);
3076: if (curr = 6) then
3077: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3078: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3079: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_NO_FOREIGN_RECON');
3084: end if;
3085: no_of_matches := 0;
3086: WHEN OTHERS THEN
3087: IF (SQL%NOTFOUND) THEN
3088: cep_standard.debug('EXCEPTION: NO data found in group_match');
3089: cep_standard.debug('curr = '||curr);
3090: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3091: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3092: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_GROUP_P');
3085: no_of_matches := 0;
3086: WHEN OTHERS THEN
3087: IF (SQL%NOTFOUND) THEN
3088: cep_standard.debug('EXCEPTION: NO data found in group_match');
3089: cep_standard.debug('curr = '||curr);
3090: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3091: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3092: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_GROUP_P');
3093: no_of_matches:=0;
3091: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3092: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_GROUP_P');
3093: no_of_matches:=0;
3094: ELSIF (SQL%ROWCOUNT >0) THEN
3095: cep_standard.debug('EXCEPTION: More than one batch match this group' );
3096: cep_standard.debug('curr = '||curr);
3097: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3098: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3099: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APG_PARTIAL');
3092: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_GROUP_P');
3093: no_of_matches:=0;
3094: ELSIF (SQL%ROWCOUNT >0) THEN
3095: cep_standard.debug('EXCEPTION: More than one batch match this group' );
3096: cep_standard.debug('curr = '||curr);
3097: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3098: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3099: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APG_PARTIAL');
3100: no_of_matches:=999;
3098: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3099: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_APG_PARTIAL');
3100: no_of_matches:=999;
3101: ELSE
3102: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.group_match' );
3103: RAISE;
3104: END IF;
3105: END group_match;
3106:
3111: min_acct_unit NUMBER;
3112: acctd_amount NUMBER;
3113: rounded_amount NUMBER;
3114: BEGIN
3115: cep_standard.debug('>>CE_AUTO_BANK_MATCH.convert_to_base_curr');
3116:
3117: IF (CE_AUTO_BANK_MATCH.csl_exchange_rate_type <> 'User') THEN
3118:
3119: BEGIN
3124: CE_AUTO_BANK_MATCH.csl_trx_date),
3125: CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
3126: amount_to_convert);
3127:
3128: cep_standard.debug('acctd_amount '||acctd_amount);
3129: EXCEPTION
3130: WHEN OTHERS THEN
3131: cep_standard.debug('EXCEPTION: Could not convert amount');
3132: acctd_amount := NULL;
3127:
3128: cep_standard.debug('acctd_amount '||acctd_amount);
3129: EXCEPTION
3130: WHEN OTHERS THEN
3131: cep_standard.debug('EXCEPTION: Could not convert amount');
3132: acctd_amount := NULL;
3133: END;
3134:
3135: rounded_amount := acctd_amount;
3146: END IF;
3147:
3148: END IF;
3149:
3150: cep_standard.debug('<
3152:
3153: EXCEPTION
3154: WHEN OTHERS THEN
3151: RETURN(rounded_amount);
3152:
3153: EXCEPTION
3154: WHEN OTHERS THEN
3155: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.convert_to_base_curr');
3156: RAISE;
3157: END convert_to_base_curr;
3158:
3159: /* ---------------------------------------------------------------------
3184: l_trx_number CE_STATEMENT_LINES.BANK_TRX_NUMBER%TYPE; --Bug 3385023 added this variable.
3185: current_org_id number;
3186: receivables_trx_org_id number;
3187: BEGIN
3188: cep_standard.debug('>>CE_AUTO_BANK_MATCH.create_misc_trx');
3189:
3190: -- Changes for Release 11.
3191: -- We can create a new misc receipt only if the matching_against can
3192: -- be a normal MISC (i.e. <> 'STMT').
3192: -- be a normal MISC (i.e. <> 'STMT').
3193: IF (CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag = 'Y' AND
3194: CE_AUTO_BANK_MATCH.csl_matching_against <> 'STMT') THEN
3195:
3196: cep_standard.debug('DEBUG: trx_curr: '||
3197: CE_AUTO_BANK_MATCH.trx_currency_type);
3198:
3199: IF (trx_currency_type = 'FOREIGN' and
3200: CE_AUTO_BANK_MATCH.aba_bank_currency
3237: CE_AUTO_BANK_MATCH.csl_exchange_rate is null) OR
3238: (CE_AUTO_BANK_MATCH.csl_exchange_rate_type <> 'User' and
3239: CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL)) THEN
3240: IF (NOT validate_exchange_details) THEN
3241: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.trx_validation' );
3242: END IF;
3243: END IF;
3244:
3245: --
3277: CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
3278: CE_AUTO_BANK_MATCH.calc_csl_amount);
3279: EXCEPTION
3280: WHEN OTHERS THEN
3281: cep_standard.debug('EXCEPTION: Could not convert amount');
3282: receipt_amount := NULL;
3283: END;
3284:
3285: base_receipt_amount := convert_to_base_curr(receipt_amount);
3321: CE_RECONCILIATION_ERRORS_PKG.insert_row(CE_AUTO_BANK_MATCH.csh_statement_header_id,
3322: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_PAYMENT_METHOD');
3323: ELSE
3324:
3325: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_receivables_trx_id= '||
3326: CE_AUTO_BANK_MATCH.csl_receivables_trx_id);
3327:
3328: -- bug 5185358 not able to create misc receipt
3329: get_receivables_org_id(receivables_trx_org_id);
3328: -- bug 5185358 not able to create misc receipt
3329: get_receivables_org_id(receivables_trx_org_id);
3330: CE_AUTO_BANK_MATCH.trx_org_id := receivables_trx_org_id;
3331:
3332: cep_standard.debug('receivables_trx_org_id= '|| receivables_trx_org_id);
3333:
3334: set_single_org(receivables_trx_org_id);
3335:
3336: select mo_global.GET_CURRENT_ORG_ID
3336: select mo_global.GET_CURRENT_ORG_ID
3337: into current_org_id
3338: from dual;
3339:
3340: cep_standard.debug('current_org_id =' ||current_org_id );
3341:
3342: cep_standard.debug('create_misc_trx: >> CE_AUTO_BANK_CLEAR.misc_receipt');
3343:
3344: CE_AUTO_BANK_CLEAR.misc_receipt(
3338: from dual;
3339:
3340: cep_standard.debug('current_org_id =' ||current_org_id );
3341:
3342: cep_standard.debug('create_misc_trx: >> CE_AUTO_BANK_CLEAR.misc_receipt');
3343:
3344: CE_AUTO_BANK_CLEAR.misc_receipt(
3345: X_passin_mode => 'AUTO_TRX',
3346: X_trx_number => l_trx_number,
3390: CE_AUTO_BANK_MATCH.csl_effective_date,
3391: X_org_id =>
3392: nvl(CE_AUTO_BANK_MATCH.trx_org_id, CE_AUTO_BANK_REC.G_org_id));
3393: --CE_AUTO_BANK_MATCH.bau_org_id);
3394: cep_standard.debug('end create_misc_trx: >> CE_AUTO_BANK_CLEAR.misc_receipt');
3395:
3396: CE_AUTO_BANK_MATCH.csl_match_found := 'FULL';
3397: END IF;--validate payment method
3398: END IF; -- if not creating foreign misc receipts with null exchange info
3414: ELSE -- create flag = 'N'
3415: CE_AUTO_BANK_MATCH.csl_match_found := 'NONE';
3416: END IF; -- If create-misc-flag = 'Y'
3417:
3418: cep_standard.debug('<
3420: EXCEPTION
3421: WHEN NO_DATA_FOUND THEN
3422: cep_standard.debug('CE_AUTO_BANK_MATCH.create_misc_trx no_data_found');
3418: cep_standard.debug('<
3420: EXCEPTION
3421: WHEN NO_DATA_FOUND THEN
3422: cep_standard.debug('CE_AUTO_BANK_MATCH.create_misc_trx no_data_found');
3423: RAISE NO_DATA_FOUND;
3424: WHEN OTHERS THEN
3425: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.create_misc_trx' );
3426: RAISE;
3421: WHEN NO_DATA_FOUND THEN
3422: cep_standard.debug('CE_AUTO_BANK_MATCH.create_misc_trx no_data_found');
3423: RAISE NO_DATA_FOUND;
3424: WHEN OTHERS THEN
3425: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.create_misc_trx' );
3426: RAISE;
3427:
3428: END create_misc_trx;
3429:
3458: base_tolerance_amount NUMBER;
3459: valid_trx_temp varchar2(10);
3460:
3461: BEGIN
3462: cep_standard.debug(' ++++++++++++++ trx_validation ++++++++++++++ ');
3463: cep_standard.debug('>>CE_AUTO_BANK_MATCH.trx_validation');
3464: valid_trx := TRUE;
3465: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type '||CE_AUTO_BANK_MATCH.csl_match_type ||
3466: ', CE_AUTO_BANK_MATCH.csl_clearing_trx_type ' ||CE_AUTO_BANK_MATCH.csl_clearing_trx_type );
3459: valid_trx_temp varchar2(10);
3460:
3461: BEGIN
3462: cep_standard.debug(' ++++++++++++++ trx_validation ++++++++++++++ ');
3463: cep_standard.debug('>>CE_AUTO_BANK_MATCH.trx_validation');
3464: valid_trx := TRUE;
3465: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type '||CE_AUTO_BANK_MATCH.csl_match_type ||
3466: ', CE_AUTO_BANK_MATCH.csl_clearing_trx_type ' ||CE_AUTO_BANK_MATCH.csl_clearing_trx_type );
3467:
3461: BEGIN
3462: cep_standard.debug(' ++++++++++++++ trx_validation ++++++++++++++ ');
3463: cep_standard.debug('>>CE_AUTO_BANK_MATCH.trx_validation');
3464: valid_trx := TRUE;
3465: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type '||CE_AUTO_BANK_MATCH.csl_match_type ||
3466: ', CE_AUTO_BANK_MATCH.csl_clearing_trx_type ' ||CE_AUTO_BANK_MATCH.csl_clearing_trx_type );
3467:
3468: -- 7571492: Added 'PGROUP'
3469: IF (CE_AUTO_BANK_MATCH.csl_match_type IN
3480: -- Validate the transaction currency for foreign transactions
3481: --
3482: IF (CE_AUTO_BANK_MATCH.trx_currency_type IN ('BANK','FOREIGN')) THEN
3483: IF (NOT validate_exchange_details) THEN
3484: cep_standard.debug('not validate_exchange_details' );
3485: valid_trx := FALSE;
3486: END IF;
3487: END IF;
3488: IF valid_trx THEN
3489: valid_trx_temp := 'TRUE';
3490: ELSE
3491: valid_trx_temp := 'FALSE';
3492: END IF;
3493: cep_standard.debug('valid_trx_temp validate_exchange_details validate trx cur for FX trx ' ||valid_trx_temp);
3494: cep_standard.debug('DEBUG - trx_currency_type = ' || trx_currency_type);
3495: -------------------------------------------------------------------------
3496: --
3497: -- We calculate the tolerance here since here is where we need that
3490: ELSE
3491: valid_trx_temp := 'FALSE';
3492: END IF;
3493: cep_standard.debug('valid_trx_temp validate_exchange_details validate trx cur for FX trx ' ||valid_trx_temp);
3494: cep_standard.debug('DEBUG - trx_currency_type = ' || trx_currency_type);
3495: -------------------------------------------------------------------------
3496: --
3497: -- We calculate the tolerance here since here is where we need that
3498: -- for the first time
3497: -- We calculate the tolerance here since here is where we need that
3498: -- for the first time
3499: --
3500: calc_actual_tolerance;
3501: cep_standard.debug('DEBUG#6.3- tolerance_amount = ' || tolerance_amount);
3502:
3503: -------------------------------------------------------------------------
3504: --
3505: -- Validate the transaction amount
3515: else
3516: comp_csl_amount := CE_AUTO_BANK_MATCH.corr_csl_amount;
3517: end if;
3518:
3519: cep_standard.debug('comp_csl_amount: '||comp_csl_amount);
3520: cep_standard.debug('trx_amount: '||trx_amount);
3521: if CE_AUTO_BANK_MATCH.trx_amount
3522: NOT BETWEEN (comp_csl_amount
3523: - CE_AUTO_BANK_MATCH.tolerance_amount)
3516: comp_csl_amount := CE_AUTO_BANK_MATCH.corr_csl_amount;
3517: end if;
3518:
3519: cep_standard.debug('comp_csl_amount: '||comp_csl_amount);
3520: cep_standard.debug('trx_amount: '||trx_amount);
3521: if CE_AUTO_BANK_MATCH.trx_amount
3522: NOT BETWEEN (comp_csl_amount
3523: - CE_AUTO_BANK_MATCH.tolerance_amount)
3524: AND (comp_csl_amount
3558: ELSE
3559: valid_trx_temp := 'FALSE';
3560: END IF;
3561:
3562: cep_standard.debug('valid_trx_temp validate charge amount ' ||valid_trx_temp);
3563:
3564: --
3565: -- If trx_currency_type is BANK, check that there are no gross
3566: -- discrepancies in foreign exchange rates provided
3571: convert_to_base_curr(CE_AUTO_BANK_MATCH.tolerance_amount);
3572: base_csl_amount :=
3573: convert_to_base_curr(CE_AUTO_BANK_MATCH.calc_csl_amount);
3574:
3575: cep_standard.debug('calc_csl_amount: '||calc_csl_amount);
3576: cep_standard.debug('trx_base_amount: '||trx_base_amount);
3577: cep_standard.debug('base_csl_amount: '||base_csl_amount);
3578: cep_standard.debug('base_tolerance_amount: '||base_tolerance_amount);
3579:
3572: base_csl_amount :=
3573: convert_to_base_curr(CE_AUTO_BANK_MATCH.calc_csl_amount);
3574:
3575: cep_standard.debug('calc_csl_amount: '||calc_csl_amount);
3576: cep_standard.debug('trx_base_amount: '||trx_base_amount);
3577: cep_standard.debug('base_csl_amount: '||base_csl_amount);
3578: cep_standard.debug('base_tolerance_amount: '||base_tolerance_amount);
3579:
3580: IF (CE_AUTO_BANK_MATCH.trx_base_amount
3573: convert_to_base_curr(CE_AUTO_BANK_MATCH.calc_csl_amount);
3574:
3575: cep_standard.debug('calc_csl_amount: '||calc_csl_amount);
3576: cep_standard.debug('trx_base_amount: '||trx_base_amount);
3577: cep_standard.debug('base_csl_amount: '||base_csl_amount);
3578: cep_standard.debug('base_tolerance_amount: '||base_tolerance_amount);
3579:
3580: IF (CE_AUTO_BANK_MATCH.trx_base_amount
3581: NOT BETWEEN (base_csl_amount - base_tolerance_amount)
3574:
3575: cep_standard.debug('calc_csl_amount: '||calc_csl_amount);
3576: cep_standard.debug('trx_base_amount: '||trx_base_amount);
3577: cep_standard.debug('base_csl_amount: '||base_csl_amount);
3578: cep_standard.debug('base_tolerance_amount: '||base_tolerance_amount);
3579:
3580: IF (CE_AUTO_BANK_MATCH.trx_base_amount
3581: NOT BETWEEN (base_csl_amount - base_tolerance_amount)
3582: AND (base_csl_amount + base_tolerance_amount)) THEN
3591: valid_trx_temp := 'TRUE';
3592: ELSE
3593: valid_trx_temp := 'FALSE';
3594: END IF;
3595: cep_standard.debug('valid_trx_temp transaction amount ' ||valid_trx_temp);
3596:
3597: cep_standard.debug('DEBUG#6.5- calc_csl_amount = ' || calc_csl_amount);
3598: --
3599: -- Remittance batches with amount differences
3593: valid_trx_temp := 'FALSE';
3594: END IF;
3595: cep_standard.debug('valid_trx_temp transaction amount ' ||valid_trx_temp);
3596:
3597: cep_standard.debug('DEBUG#6.5- calc_csl_amount = ' || calc_csl_amount);
3598: --
3599: -- Remittance batches with amount differences
3600: -- try to create misc receipts
3601: -- we need to validate the payment method
3638: valid_trx_temp := 'TRUE';
3639: ELSE
3640: valid_trx_temp := 'FALSE';
3641: END IF;
3642: cep_standard.debug('valid_trx_temp batch ' ||valid_trx_temp);
3643: -------------------------------------------------------------------------------
3644: --
3645: -- Validate GL date #5
3646: --
3674: valid_trx_temp := 'TRUE';
3675: ELSE
3676: valid_trx_temp := 'FALSE';
3677: END IF;
3678: cep_standard.debug('valid_trx_temp gl date ' ||valid_trx_temp);
3679: -------------------------------------------------------------------------------
3680:
3681: --
3682: -- 7. The cleared GL date cannot be earlier than the original
3682: -- 7. The cleared GL date cannot be earlier than the original
3683: -- GL date of the receipt. -bug 1941362
3684: --
3685: --
3686: -- cep_standard.debug('7 Before** to_date( CE_AUTO_BANK_REC.G_gl_date = ' || CE_AUTO_BANK_REC.G_gl_date);
3687: -- cep_standard.debug('7 Before** to_date( CE_AUTO_BANK_REC.G_gl_date_original = ' || CE_AUTO_BANK_REC.G_gl_date_original);
3688: -- cep_standard.debug('7 Before** to_date( CE_AUTO_BANK_MATCH.trx_gl_date = ' || CE_AUTO_BANK_MATCH.trx_gl_date);
3689:
3690:
3683: -- GL date of the receipt. -bug 1941362
3684: --
3685: --
3686: -- cep_standard.debug('7 Before** to_date( CE_AUTO_BANK_REC.G_gl_date = ' || CE_AUTO_BANK_REC.G_gl_date);
3687: -- cep_standard.debug('7 Before** to_date( CE_AUTO_BANK_REC.G_gl_date_original = ' || CE_AUTO_BANK_REC.G_gl_date_original);
3688: -- cep_standard.debug('7 Before** to_date( CE_AUTO_BANK_MATCH.trx_gl_date = ' || CE_AUTO_BANK_MATCH.trx_gl_date);
3689:
3690:
3691: IF ((CE_AUTO_BANK_MATCH.csl_match_type = 'CASH' or CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW' ) AND
3684: --
3685: --
3686: -- cep_standard.debug('7 Before** to_date( CE_AUTO_BANK_REC.G_gl_date = ' || CE_AUTO_BANK_REC.G_gl_date);
3687: -- cep_standard.debug('7 Before** to_date( CE_AUTO_BANK_REC.G_gl_date_original = ' || CE_AUTO_BANK_REC.G_gl_date_original);
3688: -- cep_standard.debug('7 Before** to_date( CE_AUTO_BANK_MATCH.trx_gl_date = ' || CE_AUTO_BANK_MATCH.trx_gl_date);
3689:
3690:
3691: IF ((CE_AUTO_BANK_MATCH.csl_match_type = 'CASH' or CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW' ) AND
3692: (to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD') < to_char(CE_AUTO_BANK_MATCH.trx_gl_date,'YYYY/MM/DD'))) THEN
3692: (to_char(CE_AUTO_BANK_REC.G_gl_date,'YYYY/MM/DD') < to_char(CE_AUTO_BANK_MATCH.trx_gl_date,'YYYY/MM/DD'))) THEN
3693: CE_AUTO_BANK_REC.G_gl_date := CE_AUTO_BANK_MATCH.trx_gl_date;
3694: END IF;
3695:
3696: -- cep_standard.debug('7 After** to_date(CE_AUTO_BANK_REC.G_gl_date = ' || CE_AUTO_BANK_REC.G_gl_date);
3697:
3698: --
3699: -- 8. The cleared_date cannot be earlier than the original GL date
3700: -- of the receipts -bug 1941362
3699: -- 8. The cleared_date cannot be earlier than the original GL date
3700: -- of the receipts -bug 1941362
3701: --
3702:
3703: -- cep_standard.debug('8 Before** to_date( CE_AUTO_BANK_MATCH.csl_trx_date = ' || CE_AUTO_BANK_MATCH.csl_trx_date);
3704: -- cep_standard.debug('8 Before** to_date( CE_AUTO_BANK_REC.G_gl_date = ' || CE_AUTO_BANK_REC.G_gl_date);
3705: -- cep_standard.debug('8 Before** to_date( CE_AUTO_BANK_MATCH.trx_date = ' || CE_AUTO_BANK_MATCH.trx_date);
3706:
3707: IF ((CE_AUTO_BANK_MATCH.csl_match_type = 'CASH' or CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW' ) AND
3700: -- of the receipts -bug 1941362
3701: --
3702:
3703: -- cep_standard.debug('8 Before** to_date( CE_AUTO_BANK_MATCH.csl_trx_date = ' || CE_AUTO_BANK_MATCH.csl_trx_date);
3704: -- cep_standard.debug('8 Before** to_date( CE_AUTO_BANK_REC.G_gl_date = ' || CE_AUTO_BANK_REC.G_gl_date);
3705: -- cep_standard.debug('8 Before** to_date( CE_AUTO_BANK_MATCH.trx_date = ' || CE_AUTO_BANK_MATCH.trx_date);
3706:
3707: IF ((CE_AUTO_BANK_MATCH.csl_match_type = 'CASH' or CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW' ) AND
3708: (to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD') < to_char(CE_AUTO_BANK_MATCH.trx_gl_date,'YYYY/MM/DD'))) THEN
3701: --
3702:
3703: -- cep_standard.debug('8 Before** to_date( CE_AUTO_BANK_MATCH.csl_trx_date = ' || CE_AUTO_BANK_MATCH.csl_trx_date);
3704: -- cep_standard.debug('8 Before** to_date( CE_AUTO_BANK_REC.G_gl_date = ' || CE_AUTO_BANK_REC.G_gl_date);
3705: -- cep_standard.debug('8 Before** to_date( CE_AUTO_BANK_MATCH.trx_date = ' || CE_AUTO_BANK_MATCH.trx_date);
3706:
3707: IF ((CE_AUTO_BANK_MATCH.csl_match_type = 'CASH' or CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW' ) AND
3708: (to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD') < to_char(CE_AUTO_BANK_MATCH.trx_gl_date,'YYYY/MM/DD'))) THEN
3709: CE_AUTO_BANK_MATCH.csl_trx_date := CE_AUTO_BANK_MATCH.trx_gl_date;
3708: (to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD') < to_char(CE_AUTO_BANK_MATCH.trx_gl_date,'YYYY/MM/DD'))) THEN
3709: CE_AUTO_BANK_MATCH.csl_trx_date := CE_AUTO_BANK_MATCH.trx_gl_date;
3710: END IF;
3711:
3712: -- cep_standard.debug('8 After** to_date(CE_AUTO_BANK_MATCH.csl_trx_date = ' || CE_AUTO_BANK_MATCH.csl_trx_date);
3713: -- cep_standard.debug('8 After** to_date(CE_AUTO_BANK_MATCH.trx_gl_date = ' || CE_AUTO_BANK_MATCH.trx_gl_date);
3714:
3715: IF valid_trx THEN
3716: valid_trx_temp := 'TRUE';
3709: CE_AUTO_BANK_MATCH.csl_trx_date := CE_AUTO_BANK_MATCH.trx_gl_date;
3710: END IF;
3711:
3712: -- cep_standard.debug('8 After** to_date(CE_AUTO_BANK_MATCH.csl_trx_date = ' || CE_AUTO_BANK_MATCH.csl_trx_date);
3713: -- cep_standard.debug('8 After** to_date(CE_AUTO_BANK_MATCH.trx_gl_date = ' || CE_AUTO_BANK_MATCH.trx_gl_date);
3714:
3715: IF valid_trx THEN
3716: valid_trx_temp := 'TRUE';
3717: ELSE
3716: valid_trx_temp := 'TRUE';
3717: ELSE
3718: valid_trx_temp := 'FALSE';
3719: END IF;
3720: cep_standard.debug('valid_trx_temp 7 8 ' ||valid_trx_temp);
3721:
3722: --
3723: -- Lock the transaction
3724: --
3722: --
3723: -- Lock the transaction
3724: --
3725:
3726: cep_standard.debug('trx_validation - call CE_AUTO_BANK_MATCH.lock_transaction');
3727: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type='||CE_AUTO_BANK_MATCH.csl_match_type||
3728: ', CE_AUTO_BANK_MATCH.csl_clearing_trx_type='||CE_AUTO_BANK_MATCH.csl_clearing_trx_type );
3729:
3730: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_rowid='||CE_AUTO_BANK_MATCH.trx_rowid||
3723: -- Lock the transaction
3724: --
3725:
3726: cep_standard.debug('trx_validation - call CE_AUTO_BANK_MATCH.lock_transaction');
3727: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type='||CE_AUTO_BANK_MATCH.csl_match_type||
3728: ', CE_AUTO_BANK_MATCH.csl_clearing_trx_type='||CE_AUTO_BANK_MATCH.csl_clearing_trx_type );
3729:
3730: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_rowid='||CE_AUTO_BANK_MATCH.trx_rowid||
3731: ', CE_AUTO_BANK_MATCH.trx_amount='||CE_AUTO_BANK_MATCH.trx_amount);
3726: cep_standard.debug('trx_validation - call CE_AUTO_BANK_MATCH.lock_transaction');
3727: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_type='||CE_AUTO_BANK_MATCH.csl_match_type||
3728: ', CE_AUTO_BANK_MATCH.csl_clearing_trx_type='||CE_AUTO_BANK_MATCH.csl_clearing_trx_type );
3729:
3730: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_rowid='||CE_AUTO_BANK_MATCH.trx_rowid||
3731: ', CE_AUTO_BANK_MATCH.trx_amount='||CE_AUTO_BANK_MATCH.trx_amount);
3732:
3733: BEGIN
3734: CE_AUTO_BANK_MATCH.lock_transaction(
3755: valid_trx_temp := 'TRUE';
3756: ELSE
3757: valid_trx_temp := 'FALSE';
3758: END IF;
3759: cep_standard.debug('valid_trx_temp ' ||valid_trx_temp);
3760: cep_standard.debug('<
3762: RETURN (valid_trx);
3763: EXCEPTION
3756: ELSE
3757: valid_trx_temp := 'FALSE';
3758: END IF;
3759: cep_standard.debug('valid_trx_temp ' ||valid_trx_temp);
3760: cep_standard.debug('<
3762: RETURN (valid_trx);
3763: EXCEPTION
3764: WHEN OTHERS THEN
3761:
3762: RETURN (valid_trx);
3763: EXCEPTION
3764: WHEN OTHERS THEN
3765: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.trx_validation' );
3766: RAISE;
3767: END trx_validation;
3768:
3769: /* ---------------------------------------------------------------------
3794: funct_curr_min_acct_unit NUMBER;
3795: /* Bug 2925260 */
3796:
3797: BEGIN
3798: cep_standard.debug('>>CE_AUTO_BANK_MATCH.trx_match');
3799: no_of_matches := 0;
3800: -- bug 5122576 - zba trx in xtr
3801: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT','STOP','SWEEP_OUT') AND
3802: ( CE_AUTO_BANK_MATCH.csl_reconcile_flag NOT IN ('PAY', 'PAY_EFT'))) THEN
3805: tx_type := 'CASH';
3806: ELSIF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('MISC_CREDIT','MISC_DEBIT')) THEN
3807: tx_type := 'MISC';
3808: END IF;
3809: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type ' ||CE_AUTO_BANK_MATCH.csl_trx_type);
3810: cep_standard.debug('tx_type ' ||tx_type);
3811: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag ' ||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
3812: /* cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
3813: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
3806: ELSIF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('MISC_CREDIT','MISC_DEBIT')) THEN
3807: tx_type := 'MISC';
3808: END IF;
3809: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type ' ||CE_AUTO_BANK_MATCH.csl_trx_type);
3810: cep_standard.debug('tx_type ' ||tx_type);
3811: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag ' ||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
3812: /* cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
3813: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
3814: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
3807: tx_type := 'MISC';
3808: END IF;
3809: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type ' ||CE_AUTO_BANK_MATCH.csl_trx_type);
3810: cep_standard.debug('tx_type ' ||tx_type);
3811: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag ' ||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
3812: /* cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
3813: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
3814: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
3815: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);*/
3808: END IF;
3809: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type ' ||CE_AUTO_BANK_MATCH.csl_trx_type);
3810: cep_standard.debug('tx_type ' ||tx_type);
3811: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag ' ||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
3812: /* cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
3813: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
3814: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
3815: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);*/
3816: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number ' ||CE_AUTO_BANK_MATCH.csl_bank_trx_number);
3809: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type ' ||CE_AUTO_BANK_MATCH.csl_trx_type);
3810: cep_standard.debug('tx_type ' ||tx_type);
3811: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag ' ||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
3812: /* cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
3813: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
3814: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
3815: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);*/
3816: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number ' ||CE_AUTO_BANK_MATCH.csl_bank_trx_number);
3817:
3810: cep_standard.debug('tx_type ' ||tx_type);
3811: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag ' ||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
3812: /* cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
3813: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
3814: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
3815: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);*/
3816: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number ' ||CE_AUTO_BANK_MATCH.csl_bank_trx_number);
3817:
3818: IF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'CE') THEN
3811: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag ' ||CE_AUTO_BANK_MATCH.csl_reconcile_flag);
3812: /* cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
3813: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
3814: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
3815: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);*/
3816: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number ' ||CE_AUTO_BANK_MATCH.csl_bank_trx_number);
3817:
3818: IF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'CE') THEN
3819: ce_match(no_of_matches);
3812: /* cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
3813: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
3814: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
3815: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);*/
3816: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number ' ||CE_AUTO_BANK_MATCH.csl_bank_trx_number);
3817:
3818: IF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'CE') THEN
3819: ce_match(no_of_matches);
3820:
3920: AND NVL(catv.reconciled_status_flag, 'N') = 'N';
3921:
3922: ELSIF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'OI') THEN
3923:
3924: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
3925:
3926: curr := 3;
3927:
3928: if (CE_AUTO_BANK_REC.G_open_interface_matching_code = 'T') then
3928: if (CE_AUTO_BANK_REC.G_open_interface_matching_code = 'T') then
3929: IF ((CE_AUTO_BANK_REC.G_legal_entity_id is not null) or
3930: (CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag = 'Y')) THEN
3931:
3932: cep_standard.debug('use ce_185_transactions_v ' );
3933:
3934: SELECT catv.trx_id,
3935: catv.cash_receipt_id,
3936: catv.row_id,
3980: AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
3981: AND catv.application_id = 185
3982: AND NVL(catv.reconciled_status_flag, 'N') = 'N';
3983: ELSE -- no LE_ID or not XTR acct
3984: cep_standard.debug('use ce_999_transactions_v ' );
3985:
3986: SELECT catv.trx_id,
3987: catv.cash_receipt_id,
3988: catv.row_id,
4033:
4034: END IF;
4035: else -- match by DATE and AMOUNT
4036:
4037: cep_standard.debug('match by DATE and AMOUNT ' );
4038: -- bug 5122576 issue with zba trx in xtr
4039: --if (CE_AUTO_BANK_MATCH.csl_trx_type = 'CREDIT') then
4040: if (CE_AUTO_BANK_MATCH.csl_trx_type in ('SWEEP_IN', 'CREDIT')) then
4041: bank_charges := -nvl(CE_AUTO_BANK_MATCH.csl_charges_amount,0);
4052:
4053: /* bank currency match */
4054: amount_to_match := round(stmt_amount, precision);
4055:
4056: cep_standard.debug('tx_type='||tx_type ||', amount_to_match =' || amount_to_match);
4057:
4058: match_oi_trx(tx_type, tx_curr, amount_to_match, precision,
4059: no_of_matches);
4060:
4077: else
4078: amount_to_match := round(stmt_amount
4079: / CE_AUTO_BANK_MATCH.csl_exchange_rate, precision);
4080:
4081: cep_standard.debug('tx_type='||tx_type ||', amount_to_match =' || amount_to_match);
4082:
4083: match_oi_trx(tx_type, tx_curr, amount_to_match, precision,
4084: no_of_matches);
4085: end if;
4086: else
4087: amount_to_match := round(CE_AUTO_BANK_MATCH.csl_original_amount,
4088: precision);
4089:
4090: cep_standard.debug('tx_type='||tx_type ||', amount_to_match =' || amount_to_match);
4091:
4092: match_oi_trx(tx_type, tx_curr, amount_to_match, precision,
4093: no_of_matches);
4094: end if;
4107: else
4108: amount_to_match := round(stmt_amount
4109: / CE_AUTO_BANK_MATCH.csl_exchange_rate, precision);
4110:
4111: cep_standard.debug('tx_type='||tx_type ||', amount_to_match =' || amount_to_match);
4112:
4113: match_oi_trx(tx_type, tx_curr, amount_to_match, precision,
4114: no_of_matches);
4115: end if;
4218: AND NVL(catv.reconciled_status_flag, 'N') = 'N';
4219:
4220: ELSIF ((tx_type IN ('CASH','MISC')) AND
4221: (CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag = 'Y')) THEN
4222: cep_standard.debug('curr 5 ' );
4223:
4224: curr := 5;
4225: SELECT catv.trx_id,
4226: catv.cash_receipt_id,
4421: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <>
4422: CE_AUTO_BANK_REC.G_functional_currency) and
4423: (CE_AUTO_BANK_MATCH.aba_bank_currency <>
4424: CE_AUTO_BANK_MATCH.trx_currency_code) THEN
4425: cep_standard.debug('Forex account not using the same curr as bk');
4426: curr := 9;
4427: RAISE NO_DATA_FOUND;
4428: END IF;
4429:
4426: curr := 9;
4427: RAISE NO_DATA_FOUND;
4428: END IF;
4429:
4430: cep_standard.debug('<
4432: EXCEPTION
4433: WHEN NO_DATA_FOUND THEN
4434: cep_standard.debug('EXCEPTION: No data found in trx_match');
4430: cep_standard.debug('<
4432: EXCEPTION
4433: WHEN NO_DATA_FOUND THEN
4434: cep_standard.debug('EXCEPTION: No data found in trx_match');
4435: if (curr = 7) then
4436: CE_RECONCILIATION_ERRORS_PKG.insert_row(
4437: CE_AUTO_BANK_MATCH.csh_statement_header_id,
4438: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_BAD_ARL');
4447: elsif (curr = 4 or curr = 5) then
4448: IF (CE_AUTO_BANK_MATCH.csl_trx_type in
4449: ('CREDIT','MISC_CREDIT','MISC_DEBIT')) THEN
4450: IF (CE_AUTO_BANK_MATCH.trx_status = 'REVERSED') then
4451: cep_standard.debug('>>receipt with reversed status');
4452: CE_RECONCILIATION_ERRORS_PKG.insert_row(
4453: CE_AUTO_BANK_MATCH.csh_statement_header_id,
4454: CE_AUTO_BANK_MATCH.csl_statement_line_id,
4455: 'CE_STATEMENT_REVERSAL_NSF');
4454: CE_AUTO_BANK_MATCH.csl_statement_line_id,
4455: 'CE_STATEMENT_REVERSAL_NSF');
4456: ELSIF (CE_AUTO_BANK_MATCH.trx_status NOT IN
4457: ('REMITTED', 'CLEARED', 'RISK_ELIMINATED')) THEN
4458: cep_standard.debug('>>receipt with wrong status');
4459: CE_RECONCILIATION_ERRORS_PKG.insert_row(
4460: CE_AUTO_BANK_MATCH.csh_statement_header_id,
4461: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_BAD_ARL');
4462: ELSE
4466: END IF;
4467: ELSE -- NSF, REJECTED
4468: IF (CE_AUTO_BANK_MATCH.trx_status NOT IN
4469: ('REMITTED', 'CLEARED', 'RISK_ELIMINATED','REVERSED')) THEN
4470: cep_standard.debug('>>NSF/REJECTED with wrong status');
4471: CE_RECONCILIATION_ERRORS_PKG.insert_row(
4472: CE_AUTO_BANK_MATCH.csh_statement_header_id,
4473: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_BAD_NSF');
4474: END IF;
4492: end if;
4493: no_of_matches := 0;
4494: WHEN OTHERS THEN
4495: IF (SQL%ROWCOUNT >0) THEN
4496: cep_standard.debug('EXCEPTION: More than one transaction match this receipt' );
4497: if (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'JE') then
4498: CE_RECONCILIATION_ERRORS_PKG.insert_row(
4499: CE_AUTO_BANK_MATCH.csh_statement_header_id,
4500: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_JEL_PARTIAL');
4516: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_ART_PARTIAL');
4517: end if;
4518: no_of_matches:=999;
4519: ELSE
4520: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.trx_match' );
4521: RAISE;
4522: END IF;
4523: END trx_match;
4524:
5469: loc_cash_receipt_id AR_CASH_RECEIPT_HISTORY_ALL.cash_receipt_id%TYPE;
5470: loc_trx_date DATE;
5471:
5472: BEGIN
5473: cep_standard.debug('>>CE_AUTO_BANK_MATCH.stmtline_match');
5474: no_of_matches := 0;
5475:
5476: /* Calculate calc_tolerance_amount. */
5477:
5557: END IF;
5558: */
5559:
5560: --------------------------------------------------------------------------------
5561: cep_standard.debug('DEBUG: calc_tolerance_amount = '|| calc_tolerance_amount);
5562: cep_standard.debug('DEBUG: calc_tolerance_amount_ap = '|| calc_tolerance_amount_ap);
5563: cep_standard.debug('DEBUG: calc_tolerance_amount_ar = '|| calc_tolerance_amount_ar);
5564: cep_standard.debug('DEBUG: csl_correction_method = '|| csl_correction_method);
5565:
5558: */
5559:
5560: --------------------------------------------------------------------------------
5561: cep_standard.debug('DEBUG: calc_tolerance_amount = '|| calc_tolerance_amount);
5562: cep_standard.debug('DEBUG: calc_tolerance_amount_ap = '|| calc_tolerance_amount_ap);
5563: cep_standard.debug('DEBUG: calc_tolerance_amount_ar = '|| calc_tolerance_amount_ar);
5564: cep_standard.debug('DEBUG: csl_correction_method = '|| csl_correction_method);
5565:
5566: if (CE_AUTO_BANK_MATCH.csl_correction_method in ('REVERSAL', 'BOTH')) then
5559:
5560: --------------------------------------------------------------------------------
5561: cep_standard.debug('DEBUG: calc_tolerance_amount = '|| calc_tolerance_amount);
5562: cep_standard.debug('DEBUG: calc_tolerance_amount_ap = '|| calc_tolerance_amount_ap);
5563: cep_standard.debug('DEBUG: calc_tolerance_amount_ar = '|| calc_tolerance_amount_ar);
5564: cep_standard.debug('DEBUG: csl_correction_method = '|| csl_correction_method);
5565:
5566: if (CE_AUTO_BANK_MATCH.csl_correction_method in ('REVERSAL', 'BOTH')) then
5567: OPEN get_reversal;
5560: --------------------------------------------------------------------------------
5561: cep_standard.debug('DEBUG: calc_tolerance_amount = '|| calc_tolerance_amount);
5562: cep_standard.debug('DEBUG: calc_tolerance_amount_ap = '|| calc_tolerance_amount_ap);
5563: cep_standard.debug('DEBUG: calc_tolerance_amount_ar = '|| calc_tolerance_amount_ar);
5564: cep_standard.debug('DEBUG: csl_correction_method = '|| csl_correction_method);
5565:
5566: if (CE_AUTO_BANK_MATCH.csl_correction_method in ('REVERSAL', 'BOTH')) then
5567: OPEN get_reversal;
5568: FETCH get_reversal
5585: CE_AUTO_BANK_MATCH.trx_exchange_rate_type,
5586: CE_AUTO_BANK_MATCH.gt_seq_id;
5587: CLOSE get_reversal;
5588:
5589: cep_standard.debug('DEBUG: get_reversal no_of_matches = '|| no_of_matches);
5590: if (no_of_matches = 1) then
5591: CE_AUTO_BANK_MATCH.csl_match_correction_type := 'REVERSAL';
5592: CE_AUTO_BANK_MATCH.corr_csl_amount := 0;
5593: CE_AUTO_BANK_MATCH.calc_csl_amount := 0;
5595: end if;
5596:
5597: if (CE_AUTO_BANK_MATCH.csl_correction_method in ('ADJUSTMENT', 'BOTH') AND
5598: no_of_matches <> 1) then
5599: cep_standard.debug('DEBUG: get_adjustment calc_tolerance_amount = '
5600: || calc_tolerance_amount);
5601: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csh_bank_account_id = '
5602: || CE_AUTO_BANK_MATCH.csh_bank_account_id);
5603: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_bank_trx_number = '
5597: if (CE_AUTO_BANK_MATCH.csl_correction_method in ('ADJUSTMENT', 'BOTH') AND
5598: no_of_matches <> 1) then
5599: cep_standard.debug('DEBUG: get_adjustment calc_tolerance_amount = '
5600: || calc_tolerance_amount);
5601: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csh_bank_account_id = '
5602: || CE_AUTO_BANK_MATCH.csh_bank_account_id);
5603: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_bank_trx_number = '
5604: || CE_AUTO_BANK_MATCH.csl_bank_trx_number);
5605: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_trx_date = '
5599: cep_standard.debug('DEBUG: get_adjustment calc_tolerance_amount = '
5600: || calc_tolerance_amount);
5601: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csh_bank_account_id = '
5602: || CE_AUTO_BANK_MATCH.csh_bank_account_id);
5603: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_bank_trx_number = '
5604: || CE_AUTO_BANK_MATCH.csl_bank_trx_number);
5605: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_trx_date = '
5606: || CE_AUTO_BANK_MATCH.csl_trx_date);
5607: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_statement_line_id = '
5601: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csh_bank_account_id = '
5602: || CE_AUTO_BANK_MATCH.csh_bank_account_id);
5603: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_bank_trx_number = '
5604: || CE_AUTO_BANK_MATCH.csl_bank_trx_number);
5605: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_trx_date = '
5606: || CE_AUTO_BANK_MATCH.csl_trx_date);
5607: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_statement_line_id = '
5608: || CE_AUTO_BANK_MATCH.csl_statement_line_id);
5609: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.calc_csl_amount = '
5603: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_bank_trx_number = '
5604: || CE_AUTO_BANK_MATCH.csl_bank_trx_number);
5605: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_trx_date = '
5606: || CE_AUTO_BANK_MATCH.csl_trx_date);
5607: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_statement_line_id = '
5608: || CE_AUTO_BANK_MATCH.csl_statement_line_id);
5609: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.calc_csl_amount = '
5610: || CE_AUTO_BANK_MATCH.calc_csl_amount);
5611:
5605: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_trx_date = '
5606: || CE_AUTO_BANK_MATCH.csl_trx_date);
5607: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_statement_line_id = '
5608: || CE_AUTO_BANK_MATCH.csl_statement_line_id);
5609: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.calc_csl_amount = '
5610: || CE_AUTO_BANK_MATCH.calc_csl_amount);
5611:
5612:
5613: -- No transaction match if the sum of statement line amount is $0.
5653: CE_AUTO_BANK_MATCH.trx_bank_acct_use_id,
5654: CE_AUTO_BANK_MATCH.gt_seq_id;
5655: CLOSE get_adjustment;
5656:
5657: cep_standard.debug('DEBUG: get_adjustment no_of_matches = '||no_of_matches);
5658: if (no_of_matches = 1) then
5659: CE_AUTO_BANK_MATCH.csl_match_correction_type := 'ADJUSTMENT';
5660: if (CE_AUTO_BANK_MATCH.csl_trx_type = 'MISC_CREDIT') then
5661: if (CE_AUTO_BANK_MATCH.trx_type2 in ('CREDIT', 'MISC_CREDIT')) then
5673: CE_AUTO_BANK_MATCH.corr_csl_amount := CE_AUTO_BANK_MATCH.csl_amount
5674: - CE_AUTO_BANK_MATCH.trx_amount2;
5675: end if;
5676: end if;
5677: cep_standard.debug('corr_csl_amount: '||corr_csl_amount);
5678: CE_AUTO_BANK_MATCH.calc_csl_amount := CE_AUTO_BANK_MATCH.corr_csl_amount;
5679: end if;
5680: end if;
5681:
5731: CE_AUTO_BANK_MATCH.trx_bank_acct_use_id;
5732: CLOSE get_rev_debit;
5733: end if;
5734:
5735: cep_standard.debug('DEBUG: get_rev_xx no_of_matches = '|| no_of_matches);
5736: if (no_of_matches = 1) then
5737:
5738: -- bug 4914608 set the org after a match for AR/AP
5739: set_single_org(CE_AUTO_BANK_MATCH.trx_org_id);
5886: CE_AUTO_BANK_MATCH.trx_bank_acct_use_id;
5887: CLOSE get_recon_adj_pay;
5888: end if;
5889:
5890: cep_standard.debug('DEBUG: get_reconciled_adj no_of_matches = '||
5891: no_of_matches);
5892: if (no_of_matches = 1) then
5893: CE_AUTO_BANK_MATCH.reconciled_this_run := 'Y';
5894: else
5979: CE_AUTO_BANK_MATCH.gt_seq_id;
5980: CLOSE get_recon_adj2_ap;
5981: end if;
5982:
5983: cep_standard.debug('DEBUG: get_reconciled_adj2 no_of_matches = '||
5984: no_of_matches);
5985: if (no_of_matches = 1) then
5986:
5987: -- bug 4914608 set the org after a match for AR/AP
6065: raise NO_DATA_FOUND;
6066: elsif (no_of_matches > 1) then
6067: raise TOO_MANY_ROWS;
6068: end if;
6069: cep_standard.debug('<
6071: EXCEPTION
6072: WHEN NO_DATA_FOUND THEN
6073: CE_RECONCILIATION_ERRORS_PKG.insert_row(
6074: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6075: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_NO_STMTL');
6076: no_of_matches:=0;
6077: WHEN TOO_MANY_ROWS THEN
6078: cep_standard.debug('EXCEPTION: More than one statement line match this receipt' );
6079: CE_RECONCILIATION_ERRORS_PKG.insert_row(
6080: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6081: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_STMT_PARTIAL');
6082: no_of_matches:=999;
6080: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6081: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_STMT_PARTIAL');
6082: no_of_matches:=999;
6083: WHEN OTHERS THEN
6084: cep_standard.debug('SQLCODE = '|| sqlcode);
6085: cep_standard.debug('SQLERRM = '|| sqlerrm);
6086: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.stmtline_match' );
6087: RAISE;
6088:
6081: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_ABR_STMT_PARTIAL');
6082: no_of_matches:=999;
6083: WHEN OTHERS THEN
6084: cep_standard.debug('SQLCODE = '|| sqlcode);
6085: cep_standard.debug('SQLERRM = '|| sqlerrm);
6086: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.stmtline_match' );
6087: RAISE;
6088:
6089: END stmtline_match;
6082: no_of_matches:=999;
6083: WHEN OTHERS THEN
6084: cep_standard.debug('SQLCODE = '|| sqlcode);
6085: cep_standard.debug('SQLERRM = '|| sqlerrm);
6086: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.stmtline_match' );
6087: RAISE;
6088:
6089: END stmtline_match;
6090:
6127: and aip.invoice_id = ap.invoice_id
6128: and ap.invoice_num = CE_AUTO_BANK_MATCH.csl_invoice_text;
6129:
6130: BEGIN
6131: cep_standard.debug('>>CE_AUTO_BANK_MATCH.match_line');
6132:
6133: /* Comment this out NOCOPY since Kayo move this to the match_engine().
6134: if (CE_AUTO_BANK_MATCH.csl_bank_trx_number is NULL AND
6135: (CE_AUTO_BANK_MATCH.csl_invoice_text is NULL AND
6154: ELSIF (call_mode = 'S') THEN
6155: stmtline_match(no_of_matches);
6156: END IF;
6157:
6158: cep_standard.debug('no_of_matches = '|| no_of_matches);
6159: cep_standard.debug('no_of_currencies = '|| no_of_currencies);
6160:
6161: IF (no_of_matches = 1) THEN
6162: IF (trx_validation(no_of_currencies)) THEN
6155: stmtline_match(no_of_matches);
6156: END IF;
6157:
6158: cep_standard.debug('no_of_matches = '|| no_of_matches);
6159: cep_standard.debug('no_of_currencies = '|| no_of_currencies);
6160:
6161: IF (no_of_matches = 1) THEN
6162: IF (trx_validation(no_of_currencies)) THEN
6163: CE_AUTO_BANK_MATCH.csl_match_found := 'FULL';
6181: */
6182: ELSE
6183: CE_AUTO_BANK_MATCH.csl_match_found := 'NONE';
6184: END IF;
6185: cep_standard.debug('DEBUG #5 - csl_match_found = '|| csl_match_found);
6186: cep_standard.debug('<
6188: WHEN OTHERS THEN
6189: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.match_line' );
6182: ELSE
6183: CE_AUTO_BANK_MATCH.csl_match_found := 'NONE';
6184: END IF;
6185: cep_standard.debug('DEBUG #5 - csl_match_found = '|| csl_match_found);
6186: cep_standard.debug('<
6188: WHEN OTHERS THEN
6189: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.match_line' );
6190: RAISE;
6185: cep_standard.debug('DEBUG #5 - csl_match_found = '|| csl_match_found);
6186: cep_standard.debug('<
6188: WHEN OTHERS THEN
6189: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.match_line' );
6190: RAISE;
6191: END match_line;
6192:
6193: /* ---------------------------------------------------------------------
6233: receivables_trx_org_id number;
6234:
6235: BEGIN
6236:
6237: cep_standard.debug('>>CE_AUTO_BANK_MATCH.match_engine');
6238:
6239: CE_AUTO_BANK_MATCH.csl_match_correction_type := 'NONE';
6240: CE_AUTO_BANK_MATCH.reconciled_this_run := NULL;
6241: CE_AUTO_BANK_MATCH.reconcile_to_statement_flag := NULL;
6243: --
6244: -- bug 1941362
6245: -- Reset G_gl_date because trx_match might have changed the G_gl_date
6246: --
6247: -- cep_standard.debug('Before reset ** to_date( CE_AUTO_BANK_REC.G_gl_date = ' || CE_AUTO_BANK_REC.G_gl_date);
6248:
6249: CE_AUTO_BANK_REC.G_gl_date := CE_AUTO_BANK_REC.G_gl_date_original;
6250:
6251: -- cep_standard.debug('After reset ** to_date( CE_AUTO_BANK_REC.G_gl_date = ' || CE_AUTO_BANK_REC.G_gl_date);
6247: -- cep_standard.debug('Before reset ** to_date( CE_AUTO_BANK_REC.G_gl_date = ' || CE_AUTO_BANK_REC.G_gl_date);
6248:
6249: CE_AUTO_BANK_REC.G_gl_date := CE_AUTO_BANK_REC.G_gl_date_original;
6250:
6251: -- cep_standard.debug('After reset ** to_date( CE_AUTO_BANK_REC.G_gl_date = ' || CE_AUTO_BANK_REC.G_gl_date);
6252:
6253: --
6254: -- Set the trx_currency_type
6255: --
6273: CE_AUTO_BANK_MATCH.csl_exchange_rate is null) OR
6274: (CE_AUTO_BANK_MATCH.csl_exchange_rate_type <> 'User' and
6275: CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL)) THEN
6276: IF (NOT validate_exchange_details) THEN
6277: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.trx_validation' );
6278: END IF;
6279: END IF;
6280: END IF;
6281:
6337: ELSE
6338: --
6339: -- Prod16 NEW, ability to match by invoice number also for AP
6340: --
6341: cep_standard.debug('AP/AR trx');
6342: IF (CE_AUTO_BANK_MATCH.csl_bank_account_text IS NOT NULL AND
6343: CE_AUTO_BANK_MATCH.csl_invoice_text IS NOT NULL) THEN
6344: primary_match := 'T';
6345: secondary_match := 'A';
6370: Tertiary_match := 'G';
6371: ELSIF (primary_match = 'B') and (secondary_match = 'G') THEN
6372: Tertiary_match := 'T';
6373: END IF;
6374: cep_standard.debug('AP primary_match -'||primary_match||' secondary_match -'||secondary_match||' Tertiary_match -'||Tertiary_match);
6375: ELSE
6376: primary_match := NVL(CE_AUTO_BANK_REC.G_ar_matching_order,'T');
6377: IF (primary_match = 'T') THEN
6378: secondary_match := 'B';
6382: END IF;
6383: --FOR SEPA ER 6700007 END
6384: END IF;
6385: END IF;
6386: cep_standard.debug('EXECUTING primary_match ');
6387:
6388: match_line(primary_match);
6389: IF (CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL') AND
6390: nvl(CE_AUTO_BANK_MATCH.csl_reconcile_flag,'NONE')
6389: IF (CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL') AND
6390: nvl(CE_AUTO_BANK_MATCH.csl_reconcile_flag,'NONE')
6391: NOT IN ('JE', 'OI', 'PAY', 'PAY_EFT', 'CE') AND secondary_match IS NOT NULL) THEN
6392:
6393: cep_standard.debug('EXECUTING secondary_match ');
6394:
6395:
6396: match_line(secondary_match);
6397:
6399: IF (CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL') AND
6400: CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT', 'STOP') AND
6401: Tertiary_match IS NOT NULL) THEN
6402:
6403: cep_standard.debug('EXECUTING Tertiary_match ');
6404:
6405: match_line(Tertiary_match);
6406:
6407: END IF;
6408: --FOR SEPA ER 6700007 END
6409: END IF;
6410:
6411: -- bug 4914608 set the org after a match for AR/AP
6412: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_org_id =' ||CE_AUTO_BANK_MATCH.trx_org_id);
6413: if (CE_AUTO_BANK_MATCH.csl_match_found = 'FULL') THEN
6414: set_single_org(CE_AUTO_BANK_MATCH.trx_org_id);
6415: END IF;
6416:
6428: where s.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,CE_AUTO_BANK_MATCH.trx_org_id);
6429: else
6430: CE_AUTO_BANK_MATCH.ar_accounting_method := NULL;
6431: end if;
6432: cep_standard.debug('CE_AUTO_BANK_MATCH.ar_accounting_method =' ||CE_AUTO_BANK_MATCH.ar_accounting_method);
6433:
6434: --
6435: -- Bug 928060: Create a misc receipt for NSF line with tolerance.
6436: --
6460: if (nvl(CE_AUTO_BANK_MATCH.csl_receipt_method_id,
6461: CE_AUTO_BANK_REC.G_payment_method_id) is null OR
6462: nvl(CE_AUTO_BANK_MATCH.csl_receivables_trx_id,
6463: CE_AUTO_BANK_REC.G_receivables_trx_id) is null) then
6464: cep_standard.debug('No receipt method or receivable activity info.');
6465: CE_AUTO_BANK_MATCH.nsf_info_flag := 'Y';
6466: else
6467: cep_standard.debug('Create a misc receipt for NSF line with tolerance.');
6468: declare
6463: CE_AUTO_BANK_REC.G_receivables_trx_id) is null) then
6464: cep_standard.debug('No receipt method or receivable activity info.');
6465: CE_AUTO_BANK_MATCH.nsf_info_flag := 'Y';
6466: else
6467: cep_standard.debug('Create a misc receipt for NSF line with tolerance.');
6468: declare
6469: p_cr_id ar_cash_receipts.cash_receipt_id%TYPE;
6470: begin
6471:
6480: CE_AUTO_BANK_MATCH.csl_exchange_rate is null) OR
6481: (CE_AUTO_BANK_MATCH.csl_exchange_rate_type <> 'User' and
6482: CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL)) THEN
6483: IF (NOT validate_exchange_details) THEN
6484: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.trx_validation' );
6485: END IF;
6486: END IF;
6487:
6488: --
6522: (CE_AUTO_BANK_MATCH.trx_amount
6523: - CE_AUTO_BANK_MATCH.calc_csl_amount));
6524: EXCEPTION
6525: WHEN OTHERS THEN
6526: cep_standard.debug('EXCEPTION: Could not convert amount');
6527: receipt_amount := NULL;
6528: END;
6529:
6530: base_receipt_amount := convert_to_base_curr(receipt_amount);
6553: CE_RECONCILIATION_ERRORS_PKG.insert_row(CE_AUTO_BANK_MATCH.csh_statement_header_id,
6554: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_PAYMENT_METHOD');
6555: ELSE
6556:
6557: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_receivables_trx_id= '||
6558: CE_AUTO_BANK_MATCH.csl_receivables_trx_id);
6559:
6560: -- bug 5185358 not able to create misc receipt
6561: get_receivables_org_id(receivables_trx_org_id);
6560: -- bug 5185358 not able to create misc receipt
6561: get_receivables_org_id(receivables_trx_org_id);
6562: CE_AUTO_BANK_MATCH.trx_org_id := receivables_trx_org_id;
6563:
6564: cep_standard.debug('receivables_trx_org_id= '|| receivables_trx_org_id);
6565:
6566: set_single_org(receivables_trx_org_id);
6567:
6568: select mo_global.GET_CURRENT_ORG_ID
6568: select mo_global.GET_CURRENT_ORG_ID
6569: into current_org_id
6570: from dual;
6571:
6572: cep_standard.debug('current_org_id =' ||current_org_id );
6573:
6574:
6575: cep_standard.debug('match_engine: >> CE_AUTO_BANK_CLEAR.misc_receipt');
6576:
6571:
6572: cep_standard.debug('current_org_id =' ||current_org_id );
6573:
6574:
6575: cep_standard.debug('match_engine: >> CE_AUTO_BANK_CLEAR.misc_receipt');
6576:
6577: CE_AUTO_BANK_CLEAR.misc_receipt(
6578: X_passin_mode => 'AUTO_TRX',
6579: X_trx_number =>
6623: X_org_id =>
6624: nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id));
6625: --CE_AUTO_BANK_MATCH.bau_org_id);
6626:
6627: cep_standard.debug('end match_engine: >> CE_AUTO_BANK_CLEAR.misc_receipt');
6628: cep_standard.debug('p_cr_id = '|| p_cr_id);
6629:
6630: cep_standard.debug('Create a misc receipt with cash_receipt_id='|| to_char(p_cr_id));
6631: END IF; --validate payment method
6624: nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id));
6625: --CE_AUTO_BANK_MATCH.bau_org_id);
6626:
6627: cep_standard.debug('end match_engine: >> CE_AUTO_BANK_CLEAR.misc_receipt');
6628: cep_standard.debug('p_cr_id = '|| p_cr_id);
6629:
6630: cep_standard.debug('Create a misc receipt with cash_receipt_id='|| to_char(p_cr_id));
6631: END IF; --validate payment method
6632: END IF; -- if not creating foreign misc receipts with null exchange info
6626:
6627: cep_standard.debug('end match_engine: >> CE_AUTO_BANK_CLEAR.misc_receipt');
6628: cep_standard.debug('p_cr_id = '|| p_cr_id);
6629:
6630: cep_standard.debug('Create a misc receipt with cash_receipt_id='|| to_char(p_cr_id));
6631: END IF; --validate payment method
6632: END IF; -- if not creating foreign misc receipts with null exchange info
6633:
6634: exception
6632: END IF; -- if not creating foreign misc receipts with null exchange info
6633:
6634: exception
6635: when others then
6636: cep_standard.debug('Error in CE_AUTO_BANK_CLEAR.misc_receipt');
6637: raise;
6638: end;
6639: end if;
6640:
6638: end;
6639: end if;
6640:
6641: ELSE /* misc_exists = 'Y' */
6642: cep_standard.debug('Exist a misc receipt with trx number <'||
6643: CE_AUTO_BANK_MATCH.csl_bank_trx_number || '/NSF>.');
6644: END IF;
6645: end if;
6646:
6674: secondary_match := 'T';
6675: end if;
6676:
6677: match_line(primary_match);
6678: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_match_found = '||
6679: CE_AUTO_BANK_MATCH.csl_match_found);
6680: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_reconcile_flag = '||
6681: CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6682: cep_standard.debug('DEBUG: secondary_match = '|| secondary_match);
6676:
6677: match_line(primary_match);
6678: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_match_found = '||
6679: CE_AUTO_BANK_MATCH.csl_match_found);
6680: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_reconcile_flag = '||
6681: CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6682: cep_standard.debug('DEBUG: secondary_match = '|| secondary_match);
6683: if (CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL') AND
6684: nvl(CE_AUTO_BANK_MATCH.csl_reconcile_flag,'NONE')
6678: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_match_found = '||
6679: CE_AUTO_BANK_MATCH.csl_match_found);
6680: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_reconcile_flag = '||
6681: CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6682: cep_standard.debug('DEBUG: secondary_match = '|| secondary_match);
6683: if (CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL') AND
6684: nvl(CE_AUTO_BANK_MATCH.csl_reconcile_flag,'NONE')
6685: NOT IN ('JE', 'OI') AND secondary_match IS NOT NULL) then
6686: match_line(secondary_match);
6686: match_line(secondary_match);
6687: end if;
6688:
6689:
6690: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_found - '|| CE_AUTO_BANK_MATCH.csl_match_found);
6691: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag - '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6692: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_matching_against - '|| CE_AUTO_BANK_MATCH.csl_matching_against);
6693: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6694: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6687: end if;
6688:
6689:
6690: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_found - '|| CE_AUTO_BANK_MATCH.csl_match_found);
6691: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag - '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6692: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_matching_against - '|| CE_AUTO_BANK_MATCH.csl_matching_against);
6693: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6694: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6695: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text - '|| CE_AUTO_BANK_MATCH.csl_invoice_text);
6688:
6689:
6690: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_found - '|| CE_AUTO_BANK_MATCH.csl_match_found);
6691: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag - '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6692: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_matching_against - '|| CE_AUTO_BANK_MATCH.csl_matching_against);
6693: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6694: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6695: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text - '|| CE_AUTO_BANK_MATCH.csl_invoice_text);
6696: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text - '|| CE_AUTO_BANK_MATCH.csl_bank_account_text);
6689:
6690: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_found - '|| CE_AUTO_BANK_MATCH.csl_match_found);
6691: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag - '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6692: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_matching_against - '|| CE_AUTO_BANK_MATCH.csl_matching_against);
6693: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6694: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6695: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text - '|| CE_AUTO_BANK_MATCH.csl_invoice_text);
6696: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text - '|| CE_AUTO_BANK_MATCH.csl_bank_account_text);
6697: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_customer_text - '|| CE_AUTO_BANK_MATCH.csl_customer_text);
6690: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_found - '|| CE_AUTO_BANK_MATCH.csl_match_found);
6691: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag - '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6692: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_matching_against - '|| CE_AUTO_BANK_MATCH.csl_matching_against);
6693: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6694: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6695: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text - '|| CE_AUTO_BANK_MATCH.csl_invoice_text);
6696: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text - '|| CE_AUTO_BANK_MATCH.csl_bank_account_text);
6697: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_customer_text - '|| CE_AUTO_BANK_MATCH.csl_customer_text);
6698:
6691: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag - '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag);
6692: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_matching_against - '|| CE_AUTO_BANK_MATCH.csl_matching_against);
6693: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6694: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6695: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text - '|| CE_AUTO_BANK_MATCH.csl_invoice_text);
6696: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text - '|| CE_AUTO_BANK_MATCH.csl_bank_account_text);
6697: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_customer_text - '|| CE_AUTO_BANK_MATCH.csl_customer_text);
6698:
6699: -- bug 4542114 If there is no match, then create the misc receipt
6692: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_matching_against - '|| CE_AUTO_BANK_MATCH.csl_matching_against);
6693: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6694: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6695: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text - '|| CE_AUTO_BANK_MATCH.csl_invoice_text);
6696: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text - '|| CE_AUTO_BANK_MATCH.csl_bank_account_text);
6697: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_customer_text - '|| CE_AUTO_BANK_MATCH.csl_customer_text);
6698:
6699: -- bug 4542114 If there is no match, then create the misc receipt
6700: /* bug 6049035 If there is no match with and data is available in "Customer name" or "account number" and also the
6693: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag - '|| CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag );
6694: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_trx_number - '|| CE_AUTO_BANK_MATCH.csl_bank_trx_number);
6695: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_invoice_text - '|| CE_AUTO_BANK_MATCH.csl_invoice_text);
6696: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_bank_account_text - '|| CE_AUTO_BANK_MATCH.csl_bank_account_text);
6697: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_customer_text - '|| CE_AUTO_BANK_MATCH.csl_customer_text);
6698:
6699: -- bug 4542114 If there is no match, then create the misc receipt
6700: /* bug 6049035 If there is no match with and data is available in "Customer name" or "account number" and also the
6701: invoice field in the Bank Statement lines window, then create the misc receipt */
6708: (CE_AUTO_BANK_MATCH.csl_invoice_text IS NOT NULL and
6709: (CE_AUTO_BANK_MATCH.csl_bank_account_text IS NOT NULL or
6710: CE_AUTO_BANK_MATCH.csl_customer_text IS NOT NULL)))) then
6711:
6712: cep_standard.debug('calling create_misc_trx ');
6713: create_misc_trx;
6714: end if;
6715:
6716: ELSE
6730: END IF;
6731: END IF; -- End main IF statement -- CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'OI'
6732: END IF; -- Forex
6733:
6734: cep_standard.debug('<
6736: EXCEPTION
6737: WHEN NO_DATA_FOUND THEN
6738: cep_standard.debug('info missing');
6734: cep_standard.debug('<
6736: EXCEPTION
6737: WHEN NO_DATA_FOUND THEN
6738: cep_standard.debug('info missing');
6739: CE_RECONCILIATION_ERRORS_PKG.insert_row(
6740: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6741: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_ABR_INFO_MISSING');
6742: WHEN app_exception.application_exception THEN
6739: CE_RECONCILIATION_ERRORS_PKG.insert_row(
6740: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6741: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_ABR_INFO_MISSING');
6742: WHEN app_exception.application_exception THEN
6743: cep_standard.debug('EXCEPTION:CE_AUTO_BANK_MATCH.match_engine-application_exception' );
6744: l_encoded_message := FND_MESSAGE.GET_ENCODED;
6745: IF (l_encoded_message IS NOT NULL) THEN
6746: cep_standard.debug('Encoded message is: ' ||l_encoded_message);
6747: FND_MESSAGE.parse_encoded(l_encoded_message,l_app_short_name,
6742: WHEN app_exception.application_exception THEN
6743: cep_standard.debug('EXCEPTION:CE_AUTO_BANK_MATCH.match_engine-application_exception' );
6744: l_encoded_message := FND_MESSAGE.GET_ENCODED;
6745: IF (l_encoded_message IS NOT NULL) THEN
6746: cep_standard.debug('Encoded message is: ' ||l_encoded_message);
6747: FND_MESSAGE.parse_encoded(l_encoded_message,l_app_short_name,
6748: l_message_name);
6749: ELSE
6750: cep_standard.debug('No messages on stack');
6746: cep_standard.debug('Encoded message is: ' ||l_encoded_message);
6747: FND_MESSAGE.parse_encoded(l_encoded_message,l_app_short_name,
6748: l_message_name);
6749: ELSE
6750: cep_standard.debug('No messages on stack');
6751: l_message_name := 'OTHER_APP_ERROR';
6752: END IF;
6753: CE_RECONCILIATION_ERRORS_PKG.insert_row(
6754: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6754: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6755: CE_AUTO_BANK_MATCH.csl_statement_line_id, l_message_name,
6756: l_app_short_name);
6757: WHEN OTHERS THEN
6758: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.match_engine' );
6759: RAISE;
6760: END match_engine;
6761:
6762: /* ---------------------------------------------------------------------
6780: lockmode NUMBER;
6781: timeout NUMBER;
6782: release_on_commit BOOLEAN;
6783: BEGIN
6784: cep_standard.debug('>>CE_AUTO_BANK_MATCH.lock_statement');
6785:
6786: SELECT statement_header_id
6787: INTO x_statement_header_id
6788: FROM ce_statement_headers
6788: FROM ce_statement_headers
6789: WHERE rowid = CE_AUTO_BANK_MATCH.csh_rowid
6790: FOR UPDATE OF statement_header_id NOWAIT;
6791:
6792: cep_standard.debug('>>CE_AUTO_BANK_MATCH.Regular statement lock OK');
6793: lockname := CE_AUTO_BANK_MATCH.csh_rowid;
6794: timeout := 1;
6795: lockmode := 6;
6796: expiration_secs := 10;
6797: release_on_commit := FALSE;
6798: --
6799: -- dbms_lock of row to deal with other locking
6800: --
6801: cep_standard.debug('>>CE_AUTO_BANK_MATCH.Allocating unique');
6802: dbms_lock.allocate_unique (lockname, lockhandle, expiration_secs);
6803: lock_status := dbms_lock.request( lockhandle, lockmode, timeout,
6804: release_on_commit );
6805: IF (lock_status <> 0) THEN
6805: IF (lock_status <> 0) THEN
6806: lock_status := dbms_lock.release(lockhandle);
6807: RAISE APP_EXCEPTIONS.record_lock_exception;
6808: END IF;
6809: cep_standard.debug('<
6811: EXCEPTION
6812: WHEN APP_EXCEPTIONS.record_lock_exception THEN
6813: return(FALSE);
6811: EXCEPTION
6812: WHEN APP_EXCEPTIONS.record_lock_exception THEN
6813: return(FALSE);
6814: WHEN OTHERS THEN
6815: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.lock_statement' );
6816: RAISE;
6817: return(FALSE);
6818: END lock_statement;
6819:
6828: | match_process |
6829: --------------------------------------------------------------------- */
6830: FUNCTION lock_statement_line RETURN BOOLEAN IS
6831: BEGIN
6832: cep_standard.debug('>>CE_AUTO_BANK_MATCH.lock_statement_line');
6833: SELECT statement_line_id,
6834: trx_date,
6835: trx_type,
6836: trx_code_id,
6861: FROM ce_statement_lines
6862: WHERE rowid = CE_AUTO_BANK_MATCH.csl_rowid
6863: FOR UPDATE OF status NOWAIT;
6864:
6865: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type='||CE_AUTO_BANK_MATCH.csl_trx_type||
6866: ', csl_currency_code=' || csl_currency_code ||
6867: ', csl_bank_trx_number='||csl_bank_trx_number );
6868: cep_standard.debug('csl_customer_text='||csl_customer_text ||
6869: ', csl_invoice_text='|| csl_invoice_text||
6864:
6865: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_trx_type='||CE_AUTO_BANK_MATCH.csl_trx_type||
6866: ', csl_currency_code=' || csl_currency_code ||
6867: ', csl_bank_trx_number='||csl_bank_trx_number );
6868: cep_standard.debug('csl_customer_text='||csl_customer_text ||
6869: ', csl_invoice_text='|| csl_invoice_text||
6870: ', csl_bank_account_text='||csl_bank_account_text);
6871: cep_standard.debug('csl_amount='||csl_amount ||
6872: ', csl_charges_amount='||csl_charges_amount||
6867: ', csl_bank_trx_number='||csl_bank_trx_number );
6868: cep_standard.debug('csl_customer_text='||csl_customer_text ||
6869: ', csl_invoice_text='|| csl_invoice_text||
6870: ', csl_bank_account_text='||csl_bank_account_text);
6871: cep_standard.debug('csl_amount='||csl_amount ||
6872: ', csl_charges_amount='||csl_charges_amount||
6873: ', csl_original_amount='||csl_original_amount);
6874:
6875: cep_standard.debug('<
6871: cep_standard.debug('csl_amount='||csl_amount ||
6872: ', csl_charges_amount='||csl_charges_amount||
6873: ', csl_original_amount='||csl_original_amount);
6874:
6875: cep_standard.debug('<
6877:
6878: EXCEPTION
6879: WHEN APP_EXCEPTIONS.record_lock_exception THEN
6878: EXCEPTION
6879: WHEN APP_EXCEPTIONS.record_lock_exception THEN
6880: return(FALSE);
6881: WHEN OTHERS THEN
6882: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.lock_statement_line' );
6883: RAISE;
6884: return(FALSE);
6885: END lock_statement_line;
6886:
6896: | match_process |
6897: --------------------------------------------------------------------- */
6898: PROCEDURE update_gl_date IS
6899: BEGIN
6900: cep_standard.debug('>>CE_AUTO_BANK_MATCH.update_gl_date');
6901: IF ((CE_AUTO_BANK_REC.find_gl_period(CE_AUTO_BANK_REC.G_gl_date, 200)) OR
6902: (CE_AUTO_BANK_REC.find_gl_period(CE_AUTO_BANK_REC.G_gl_date, 222))) THEN
6903: UPDATE ce_statement_headers
6904: SET gl_date = CE_AUTO_BANK_REC.G_gl_date
6903: UPDATE ce_statement_headers
6904: SET gl_date = CE_AUTO_BANK_REC.G_gl_date
6905: WHERE rowid = CE_AUTO_BANK_MATCH.csh_rowid;
6906: END IF;
6907: cep_standard.debug('<
6909: WHEN OTHERS THEN
6910: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.update_gl_date' );
6911: RAISE;
6906: END IF;
6907: cep_standard.debug('<
6909: WHEN OTHERS THEN
6910: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.update_gl_date' );
6911: RAISE;
6912: END update_gl_date;
6913:
6914: /* ---------------------------------------------------------------------
6925: /*
6926: PROCEDURE find_le_sys_par(x_bank_account_id number) IS
6927:
6928: BEGIN
6929: cep_standard.debug('>>CE_AUTO_BANK_MATCH.find_le_sys_par x_bank_account_id'|| x_bank_account_id);
6930: -- populate ce_security_profiles_tmp table with ce_security_procfiles_v
6931: CEP_STANDARD.init_security;
6932:
6933: IF (x_bank_account_id is not null)
6927:
6928: BEGIN
6929: cep_standard.debug('>>CE_AUTO_BANK_MATCH.find_le_sys_par x_bank_account_id'|| x_bank_account_id);
6930: -- populate ce_security_profiles_tmp table with ce_security_procfiles_v
6931: CEP_STANDARD.init_security;
6932:
6933: IF (x_bank_account_id is not null)
6934: select ACCOUNT_OWNER_ORG_ID
6935: into p_le_id
6945: OI_MATCHING_CODE_OLD
6946: FROM CE_SYSTEM_PARAMETERS;
6947:
6948: END IF;
6949: cep_standard.debug('<
6951: WHEN OTHERS THEN
6952: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.find_le_sys_par' );
6953: RAISE;
6948: END IF;
6949: cep_standard.debug('<
6951: WHEN OTHERS THEN
6952: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.find_le_sys_par' );
6953: RAISE;
6954: END find_le_sys_par;
6955: */
6956:
6971: calc_percent_tolerance NUMBER;
6972: calc_amount_tolerance NUMBER;
6973: calc_charges_amount NUMBER;
6974: BEGIN
6975: cep_standard.debug('>>CE_AUTO_BANK_MATCH.calc_actual_tolerance');
6976: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.trx_currency_type = '||
6977: CE_AUTO_BANK_MATCH.trx_currency_type);
6978:
6979: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.corr_csl_amount = '||
6972: calc_amount_tolerance NUMBER;
6973: calc_charges_amount NUMBER;
6974: BEGIN
6975: cep_standard.debug('>>CE_AUTO_BANK_MATCH.calc_actual_tolerance');
6976: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.trx_currency_type = '||
6977: CE_AUTO_BANK_MATCH.trx_currency_type);
6978:
6979: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.corr_csl_amount = '||
6980: CE_AUTO_BANK_MATCH.corr_csl_amount);
6975: cep_standard.debug('>>CE_AUTO_BANK_MATCH.calc_actual_tolerance');
6976: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.trx_currency_type = '||
6977: CE_AUTO_BANK_MATCH.trx_currency_type);
6978:
6979: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.corr_csl_amount = '||
6980: CE_AUTO_BANK_MATCH.corr_csl_amount);
6981:
6982: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance = '||
6983: CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance ||
6978:
6979: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.corr_csl_amount = '||
6980: CE_AUTO_BANK_MATCH.corr_csl_amount);
6981:
6982: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance = '||
6983: CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance ||
6984: ', CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance = '||
6985: CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance);
6986:
6983: CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance ||
6984: ', CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance = '||
6985: CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance);
6986:
6987: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance = '||
6988: CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance ||
6989: ', CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance = '||
6990: CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
6991:
6988: CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance ||
6989: ', CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance = '||
6990: CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
6991:
6992: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance = '||
6993: CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance ||
6994: ', CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance = '||
6995: CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
6996:
6993: CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance ||
6994: ', CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance = '||
6995: CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
6996:
6997: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.BA_RECON_OI_AMOUNT_TOLERANCE = '||
6998: CE_AUTO_BANK_MATCH.BA_RECON_OI_AMOUNT_TOLERANCE ||
6999: ', CE_AUTO_BANK_MATCH.BA_RECON_OI_PERCENT_TOLERANCE = '||
7000: CE_AUTO_BANK_MATCH.BA_RECON_OI_PERCENT_TOLERANCE);
7001:
6998: CE_AUTO_BANK_MATCH.BA_RECON_OI_AMOUNT_TOLERANCE ||
6999: ', CE_AUTO_BANK_MATCH.BA_RECON_OI_PERCENT_TOLERANCE = '||
7000: CE_AUTO_BANK_MATCH.BA_RECON_OI_PERCENT_TOLERANCE);
7001:
7002: cep_standard.debug('DEBUG: CE_AUTO_BANK_MATCH.csl_clearing_trx_type = '||
7003: CE_AUTO_BANK_MATCH.csl_clearing_trx_type);
7004:
7005: CE_AUTO_BANK_MATCH.tolerance_amount := 0;
7006: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('REJECTED', 'STOP')
7111: (CE_AUTO_BANK_MATCH.BA_RECON_OI_PERCENT_TOLERANCE / 100);
7112: END IF;
7113: end if;
7114:
7115: cep_standard.debug('%calc_amount_tolerance: '||calc_amount_tolerance);
7116: cep_standard.debug('%calc_percent_tolerance: '||calc_percent_tolerance);
7117:
7118: if (calc_percent_tolerance < 0) then
7119: calc_percent_tolerance := calc_percent_tolerance * -1;
7112: END IF;
7113: end if;
7114:
7115: cep_standard.debug('%calc_amount_tolerance: '||calc_amount_tolerance);
7116: cep_standard.debug('%calc_percent_tolerance: '||calc_percent_tolerance);
7117:
7118: if (calc_percent_tolerance < 0) then
7119: calc_percent_tolerance := calc_percent_tolerance * -1;
7120: end if;
7120: end if;
7121: --
7122: -- Comparison
7123: --
7124: cep_standard.debug('calc_amount_tolerance: '||calc_amount_tolerance);
7125: cep_standard.debug('calc_percent_tolerance: '||calc_percent_tolerance);
7126: IF (calc_amount_tolerance = 0) THEN
7127: CE_AUTO_BANK_MATCH.tolerance_amount := calc_percent_tolerance;
7128: ELSIF (calc_percent_tolerance = 0) THEN
7121: --
7122: -- Comparison
7123: --
7124: cep_standard.debug('calc_amount_tolerance: '||calc_amount_tolerance);
7125: cep_standard.debug('calc_percent_tolerance: '||calc_percent_tolerance);
7126: IF (calc_amount_tolerance = 0) THEN
7127: CE_AUTO_BANK_MATCH.tolerance_amount := calc_percent_tolerance;
7128: ELSIF (calc_percent_tolerance = 0) THEN
7129: CE_AUTO_BANK_MATCH.tolerance_amount := calc_amount_tolerance;
7134: CE_AUTO_BANK_MATCH.tolerance_amount := calc_percent_tolerance;
7135: END IF;
7136: END IF;
7137: END IF;
7138: cep_standard.debug('<
7140: WHEN OTHERS THEN
7141: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.calc_actual_tolerance' );
7142: RAISE;
7137: END IF;
7138: cep_standard.debug('<
7140: WHEN OTHERS THEN
7141: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.calc_actual_tolerance' );
7142: RAISE;
7143: END calc_actual_tolerance;
7144:
7145: /* ---------------------------------------------------------------------
7159: min_acct_unit NUMBER;
7160: acctd_amount NUMBER;
7161: rounded_amount NUMBER;
7162: BEGIN
7163: cep_standard.debug('>>CE_AUTO_BANK_MATCH.convert_amount_tolerance');
7164:
7165: IF (CE_AUTO_BANK_MATCH.csl_exchange_rate_type <> 'User') THEN
7166:
7167: BEGIN
7174: CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
7175: amount_to_convert);
7176: EXCEPTION
7177: WHEN OTHERS THEN
7178: cep_standard.debug('EXCEPTION: Could not convert amount');
7179: acctd_amount := NULL;
7180: END;
7181:
7182: rounded_amount := acctd_amount;
7179: acctd_amount := NULL;
7180: END;
7181:
7182: rounded_amount := acctd_amount;
7183: cep_standard.debug('convert_amount_tolerance: rounded_amount: '||
7184: acctd_amount);
7185:
7186: ELSE
7187:
7195: END IF;
7196:
7197: END IF;
7198:
7199: cep_standard.debug('<
7201:
7202: EXCEPTION
7203: WHEN OTHERS THEN
7200: RETURN(rounded_amount);
7201:
7202: EXCEPTION
7203: WHEN OTHERS THEN
7204: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.convert_amount_tolerance');
7205: RAISE;
7206: END convert_amount_tolerance;
7207:
7208: /* ---------------------------------------------------------------------
7221: | valid_method TRUE / FALSE |
7222: --------------------------------------------------------------------- */
7223: FUNCTION validate_payment_method RETURN BOOLEAN IS
7224: BEGIN
7225: cep_standard.debug('>>CE_AUTO_BANK_MATCH.validate_payment_method_id');
7226: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_receipt_method_id '|| CE_AUTO_BANK_MATCH.csl_receipt_method_id);
7227: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_org_id '|| CE_AUTO_BANK_MATCH.trx_org_id);
7228:
7229: SELECT arm.name
7222: --------------------------------------------------------------------- */
7223: FUNCTION validate_payment_method RETURN BOOLEAN IS
7224: BEGIN
7225: cep_standard.debug('>>CE_AUTO_BANK_MATCH.validate_payment_method_id');
7226: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_receipt_method_id '|| CE_AUTO_BANK_MATCH.csl_receipt_method_id);
7227: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_org_id '|| CE_AUTO_BANK_MATCH.trx_org_id);
7228:
7229: SELECT arm.name
7230: INTO CE_AUTO_BANK_REC.G_payment_method_name
7223: FUNCTION validate_payment_method RETURN BOOLEAN IS
7224: BEGIN
7225: cep_standard.debug('>>CE_AUTO_BANK_MATCH.validate_payment_method_id');
7226: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_receipt_method_id '|| CE_AUTO_BANK_MATCH.csl_receipt_method_id);
7227: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_org_id '|| CE_AUTO_BANK_MATCH.trx_org_id);
7228:
7229: SELECT arm.name
7230: INTO CE_AUTO_BANK_REC.G_payment_method_name
7231: FROM ar_receipt_method_accounts arma,
7243: and exists (select 1 from ce_bank_acct_uses_gt_v bau
7244: where bau.bank_acct_use_id = arma.REMIT_BANK_ACCT_USE_ID
7245: and bau.bank_account_id =CE_AUTO_BANK_MATCH.csh_bank_account_id
7246: and bau.AR_USE_ENABLE_FLAG = 'Y' );
7247: cep_standard.debug('<
7249: EXCEPTION
7250: WHEN NO_DATA_FOUND THEN
7251: cep_standard.debug('<
7247: cep_standard.debug('<
7249: EXCEPTION
7250: WHEN NO_DATA_FOUND THEN
7251: cep_standard.debug('<
7253: WHEN TOO_MANY_ROWS THEN
7254: cep_standard.debug('<
7250: WHEN NO_DATA_FOUND THEN
7251: cep_standard.debug('<
7253: WHEN TOO_MANY_ROWS THEN
7254: cep_standard.debug('<
7256: WHEN OTHERS THEN
7257: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.validate_payment_method');
7258: RAISE;
7253: WHEN TOO_MANY_ROWS THEN
7254: cep_standard.debug('<
7256: WHEN OTHERS THEN
7257: cep_standard.debug('EXCEPTION: CE_AUTO_BANK_MATCH.validate_payment_method');
7258: RAISE;
7259: END validate_payment_method;
7260:
7261: /* ----------------------------------------------------------------------
7284: l_eff_date date ;
7285: l_le_id NUMBER;
7286:
7287: BEGIN
7288: cep_standard.debug( '>>CE_AUTO_BANK_MATCH.get_vat_tax_id' );
7289: cep_standard.debug( 'CE_AUTO_BANK_MATCH.csl_receivables_trx_id= '|| CE_AUTO_BANK_MATCH.csl_receivables_trx_id||
7290: ',CE_AUTO_BANK_MATCH.trx_org_id='||CE_AUTO_BANK_MATCH.trx_org_id );
7291: if (X_pass_mode = 'AUTO_TRX') then
7292: SELECT ar.liability_tax_code, ar.asset_tax_code
7285: l_le_id NUMBER;
7286:
7287: BEGIN
7288: cep_standard.debug( '>>CE_AUTO_BANK_MATCH.get_vat_tax_id' );
7289: cep_standard.debug( 'CE_AUTO_BANK_MATCH.csl_receivables_trx_id= '|| CE_AUTO_BANK_MATCH.csl_receivables_trx_id||
7290: ',CE_AUTO_BANK_MATCH.trx_org_id='||CE_AUTO_BANK_MATCH.trx_org_id );
7291: if (X_pass_mode = 'AUTO_TRX') then
7292: SELECT ar.liability_tax_code, ar.asset_tax_code
7293: INTO y_dr_vat_tax_code, y_cr_vat_tax_code
7349: between to_char(ar.start_date,'YYYY/MM/DD')
7350: and NVL(to_char(ar.end_date,'YYYY/MM/DD'),
7351: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'));
7352: END IF;
7353: cep_standard.debug('>>get_vat_tax_id.l_vat_tax_id: '||TO_CHAR(l_vat_tax_id));
7354: cep_standard.debug('>>get_vat_tax_id.X_tax_rate: '||TO_CHAR(X_tax_rate));
7355: cep_standard.debug( '<
7357: EXCEPTION
7350: and NVL(to_char(ar.end_date,'YYYY/MM/DD'),
7351: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'));
7352: END IF;
7353: cep_standard.debug('>>get_vat_tax_id.l_vat_tax_id: '||TO_CHAR(l_vat_tax_id));
7354: cep_standard.debug('>>get_vat_tax_id.X_tax_rate: '||TO_CHAR(X_tax_rate));
7355: cep_standard.debug( '<
7357: EXCEPTION
7358: WHEN OTHERS THEN
7351: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'));
7352: END IF;
7353: cep_standard.debug('>>get_vat_tax_id.l_vat_tax_id: '||TO_CHAR(l_vat_tax_id));
7354: cep_standard.debug('>>get_vat_tax_id.X_tax_rate: '||TO_CHAR(X_tax_rate));
7355: cep_standard.debug( '<
7357: EXCEPTION
7358: WHEN OTHERS THEN
7359: return;
7378: x_pay_only_count NUMBER;
7379: x_acct_uses_count NUMBER;
7380: x_ou_exists_in_bau NUMBER;
7381: BEGIN
7382: cep_standard.debug( '>>CE_AUTO_BANK_MATCH.set_acct_type x_bank_account_id ' ||x_bank_account_id );
7383:
7384: IF (x_bank_account_id is not null) THEN
7385:
7386: -- IS AP ACCOUNT?
7448: CE_AUTO_BANK_MATCH.BAU_PAY_USE_ENABLE_FLAG := 'N';
7449: END IF;
7450: END IF; -- (x_bank_account_id is not null)
7451:
7452: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
7453: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
7454: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
7455: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);
7456:
7449: END IF;
7450: END IF; -- (x_bank_account_id is not null)
7451:
7452: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
7453: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
7454: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
7455: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);
7456:
7457: cep_standard.debug( '<
7450: END IF; -- (x_bank_account_id is not null)
7451:
7452: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
7453: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
7454: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
7455: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);
7456:
7457: cep_standard.debug( '<
7451:
7452: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ar_use_enable_flag);
7453: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
7454: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
7455: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);
7456:
7457: cep_standard.debug( '<
7459: EXCEPTION
7453: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_ap_use_enable_flag);
7454: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_xtr_use_enable_flag);
7455: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag ' ||CE_AUTO_BANK_MATCH.bau_pay_use_enable_flag);
7456:
7457: cep_standard.debug( '<
7459: EXCEPTION
7460: WHEN OTHERS THEN
7461: cep_standard.debug('EXCEPTION: ce_auto_bank_match.set_acct_type');
7457: cep_standard.debug( '<
7459: EXCEPTION
7460: WHEN OTHERS THEN
7461: cep_standard.debug('EXCEPTION: ce_auto_bank_match.set_acct_type');
7462:
7463: RAISE;
7464: END set_acct_type;
7465:
7504: P_CURRENCY_CODE VARCHAR2(15);
7505: P_STATUS VARCHAR2(30);
7506: P_AMOUNT NUMBER;
7507: BEGIN
7508: cep_standard.debug( '>>CE_AUTO_BANK_MATCH.match_stmt_line_JE ' );
7509:
7510: -- bug 4435028 - get JEC lines from ce_cashflows
7511:
7512: /*
7614: --AND catv.bank_account_amount = CE_AUTO_BANK_MATCH.csl_amount
7615: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
7616: --AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number;
7617:
7618: cep_standard.debug( 'no_of_matches '||no_of_matches );
7619:
7620: --
7621: -- Currency and amount should be matched based on
7622: -- whether the stmt line is DOMESTIC, FOREIGN or
7651: l_je_amount := l_je_entered_dr;
7652: END IF;
7653: */
7654:
7655: cep_standard.debug( 'CE_AUTO_BANK_MATCH.trx_currency_code '||CE_AUTO_BANK_MATCH.trx_currency_code
7656: || ', l_sl_currency_code '|| l_sl_currency_code
7657: || ', CE_AUTO_BANK_MATCH.trx_amount '|| CE_AUTO_BANK_MATCH.trx_amount
7658: || ', l_sl_amount ' ||l_sl_amount);
7659:
7701: -- to 'Y'
7702: update_gt_reconciled_status (CE_AUTO_BANK_MATCH.gt_seq_id, 'Y');
7703:
7704: ELSE
7705: cep_standard.debug( 'currency or amount does not match' );
7706: CE_RECONCILIATION_ERRORS_PKG.delete_row(
7707: CE_AUTO_BANK_MATCH.csh_statement_header_id,
7708: CE_AUTO_BANK_MATCH.csl_statement_line_id);
7709:
7711: CE_AUTO_BANK_MATCH.csh_statement_header_id,
7712: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_CE_TRX_AMT_OR_CUR_NOT_MATCH');
7713:
7714: END IF;
7715: cep_standard.debug( '<
7717: EXCEPTION
7718: WHEN NO_DATA_FOUND THEN
7719: CE_RECONCILIATION_ERRORS_PKG.delete_row(
7761: l_trx_source VARCHAR2(20);
7762: l_matching_against VARCHAR2(20);
7763: l_cf NUMBER(15) := 0;
7764: BEGIN
7765: cep_standard.debug('>>CE_AUTO_BANK_MATCH.populate_available_gt');
7766:
7767: -- clean up the temp table
7768: delete ce_available_transactions_tmp;
7769:
7781: and sl.cashflow_id is not null;
7782:
7783: IF l_cf > 0 THEN -- some line has cashflow_id
7784: IF CE_AUTO_BANK_MATCH.av_260_cf_inserted_flag = 'N' THEN
7785: cep_standard.debug('inserting data from ce_260_cf_transactions_v');
7786: insert into ce_available_transactions_tmp
7787: (seq_id,
7788: ROW_ID,
7789: MULTI_SELECT,
7947: l_trx_source,
7948: l_matching_against;
7949: EXIT WHEN r_trx_source%NOTFOUND OR r_trx_source%NOTFOUND IS NULL;
7950:
7951: cep_standard.debug('bank_account_id = ' || p_bank_account_id);
7952: cep_standard.debug('l_trx_source = ' || l_trx_source ||
7953: ', l_trx_type = ' || l_trx_type ||
7954: ', l_trx_code = ' || l_trx_code ||
7955: ', l_matching_against = ' || l_matching_against);
7948: l_matching_against;
7949: EXIT WHEN r_trx_source%NOTFOUND OR r_trx_source%NOTFOUND IS NULL;
7950:
7951: cep_standard.debug('bank_account_id = ' || p_bank_account_id);
7952: cep_standard.debug('l_trx_source = ' || l_trx_source ||
7953: ', l_trx_type = ' || l_trx_type ||
7954: ', l_trx_code = ' || l_trx_code ||
7955: ', l_matching_against = ' || l_matching_against);
7956: IF l_trx_source = 'AP' THEN
7956: IF l_trx_source = 'AP' THEN
7957:
7958: IF CE_AUTO_BANK_MATCH.av_200_inserted_flag = 'N' THEN -- AP data has not been inserted into the GT table
7959:
7960: cep_standard.debug('inserting data from ce_200_transactions_v');
7961:
7962: insert into ce_available_transactions_tmp
7963: (seq_id,
7964: ROW_ID,
8106:
8107: ELSIF l_trx_source = 'AR' THEN
8108:
8109: IF CE_AUTO_BANK_MATCH.av_222_inserted_flag = 'N' THEN -- AP data has not been inserted into the GT table
8110: cep_standard.debug('inserting data from ce_222_transactions_v');
8111:
8112: insert into ce_available_transactions_tmp
8113: (seq_id,
8114: ROW_ID,
8258: -- when l_trx_source is 'CE', only populate from ce_260_cf_transactions_v
8259: /*
8260: IF av_260_inserted_flag = 'N' THEN
8261:
8262: cep_standard.debug('inserting data from ce_260_transactions_v');
8263:
8264: insert into ce_available_transactions_tmp
8265: (seq_id,
8266: ROW_ID,
8412: av_260_inserted_flag := 'Y';
8413: END IF;
8414: */
8415: IF CE_AUTO_BANK_MATCH.av_260_cf_inserted_flag = 'N' THEN
8416: cep_standard.debug('inserting data from ce_260_cf_transactions_v');
8417: insert into ce_available_transactions_tmp
8418: (seq_id,
8419: ROW_ID,
8420: MULTI_SELECT,
8572: ELSIF l_trx_source = 'JE' THEN
8573:
8574: IF CE_AUTO_BANK_MATCH.av_101_inserted_flag = 'N' THEN
8575:
8576: cep_standard.debug('inserting data from ce_101_transactions_v');
8577:
8578: insert into ce_available_transactions_tmp
8579: (seq_id,
8580: ROW_ID,
8727: -- Bug 7356199 removed the inserting data from ce_185_transactions_v
8728: -- as ce_999_transactions_v consists a union of ce_185_transactions_v
8729: IF CE_AUTO_BANK_MATCH.av_999_inserted_flag = 'N' THEN
8730:
8731: cep_standard.debug('inserting data from ce_999_transactions_v');
8732:
8733: insert into ce_available_transactions_tmp
8734: (seq_id,
8735: ROW_ID,
8876: END IF;
8877:
8878: ELSIF l_trx_source = 'PAY' THEN
8879: IF CE_AUTO_BANK_MATCH.av_801_inserted_flag = 'N' THEN
8880: cep_standard.debug('inserting data from ce_801_transactions_v');
8881:
8882: insert into ce_available_transactions_tmp
8883: (seq_id,
8884: ROW_ID,
9025: END IF;
9026:
9027: ELSIF l_trx_source = 'PAY_EFT' THEN
9028: IF CE_AUTO_BANK_MATCH.av_801_eft_inserted_flag = 'N' THEN
9029: cep_standard.debug('inserting data from ce_801_eft_transactions_v');
9030:
9031: insert into ce_available_transactions_tmp
9032: (seq_id,
9033: ROW_ID,
9176: ELSIF l_trx_source = 'X' OR l_trx_source is null THEN
9177: IF l_trx_type IN ('MISC_CREDIT', 'MISC_DEBIT') THEN
9178: IF l_matching_against IN ('STMT', 'MS', 'SM') THEN
9179: IF CE_AUTO_BANK_MATCH.av_260_inserted_flag = 'N' THEN
9180: cep_standard.debug('inserting data from ce_260_transactions_v');
9181: insert into ce_available_transactions_tmp
9182: (seq_id,
9183: ROW_ID,
9184: MULTI_SELECT,
9332: END IF; -- l_matching_against
9333:
9334: IF l_matching_against IN ('MISC', 'MS', 'SM') THEN
9335: IF CE_AUTO_BANK_MATCH.av_222_inserted_flag = 'N' THEN
9336: cep_standard.debug('inserting data from ce_222_transactions_v');
9337: insert into ce_available_transactions_tmp
9338: (seq_id,
9339: ROW_ID,
9340: MULTI_SELECT,
9481: END IF; -- l_matching_against
9482:
9483: ELSIF l_trx_type IN ('REJECTED', 'NSF') THEN
9484: IF CE_AUTO_BANK_MATCH.av_222_inserted_flag = 'N' THEN
9485: cep_standard.debug('inserting data from ce_222_transactions_v');
9486:
9487: insert into ce_available_transactions_tmp
9488: (seq_id,
9489: ROW_ID,
9633: END LOOP; -- r_trx_source cursor
9634: CLOSE r_trx_source;
9635: EXCEPTION
9636: WHEN OTHERS THEN
9637: cep_standard.debug('EXCEPTION: populate_available_gt');
9638: IF r_trx_source%ISOPEN THEN
9639: CLOSE r_trx_source;
9640: END IF;
9641: RAISE;
9666: set reconciled_status_flag = p_status
9667: where seq_id = p_seq_id;
9668: EXCEPTION
9669: WHEN OTHERS THEN
9670: cep_standard.debug('EXCEPTION: update_gt_reconciled_status');
9671: RAISE;
9672: END update_gt_reconciled_status;
9673:
9674:
9696: where application_id = p_application_id
9697: and trx_id = p_trx_id;
9698: EXCEPTION
9699: WHEN OTHERS THEN
9700: cep_standard.debug('EXCEPTION: update_gt_reconciled_status');
9701: RAISE;
9702: END update_gt_reconciled_status;
9703:
9704: /* ---------------------------------------------------------------------
9722: update ce_available_transactions_tmp
9723: set reconciled_status_flag = p_reconciled_status;
9724: EXCEPTION
9725: WHEN OTHERS THEN
9726: cep_standard.debug('EXCEPTION: update_gt_reconciled_status');
9727: RAISE;
9728: END update_gt_reconciled_status;
9729:
9730:
9785: x_trx_code_row_count NUMBER;
9786: BEGIN
9787: /* Bug 3364143 - Start code fix */
9788: IF CE_AUTO_BANK_REC.G_ce_debug_flag in ('Y', 'C') THEN
9789: cep_standard.enable_debug(CE_AUTO_BANK_REC.G_debug_path,
9790: CE_AUTO_BANK_REC.G_debug_file);
9791: end if;
9792: /* Bug 3364143 - End code fix */
9793:
9790: CE_AUTO_BANK_REC.G_debug_file);
9791: end if;
9792: /* Bug 3364143 - End code fix */
9793:
9794: cep_standard.debug('========== START MATCHING ========== ');
9795: cep_standard.debug('>>CE_AUTO_BANK_MATCH.match_process');
9796:
9797: -- populate ce_security_profiles_tmp table with ce_security_procfiles_v
9798: CEP_STANDARD.init_security;
9791: end if;
9792: /* Bug 3364143 - End code fix */
9793:
9794: cep_standard.debug('========== START MATCHING ========== ');
9795: cep_standard.debug('>>CE_AUTO_BANK_MATCH.match_process');
9796:
9797: -- populate ce_security_profiles_tmp table with ce_security_procfiles_v
9798: CEP_STANDARD.init_security;
9799:
9794: cep_standard.debug('========== START MATCHING ========== ');
9795: cep_standard.debug('>>CE_AUTO_BANK_MATCH.match_process');
9796:
9797: -- populate ce_security_profiles_tmp table with ce_security_procfiles_v
9798: CEP_STANDARD.init_security;
9799:
9800: /* --bug3676745 move this down to after fetch r_branch_cursor
9801: cep_standard.debug('Get AR accounting method.');
9802:
9797: -- populate ce_security_profiles_tmp table with ce_security_procfiles_v
9798: CEP_STANDARD.init_security;
9799:
9800: /* --bug3676745 move this down to after fetch r_branch_cursor
9801: cep_standard.debug('Get AR accounting method.');
9802:
9803: -- bug 1796965
9804: SELECT count(*)
9805: INTO accounting_method_found
9814: else
9815: CE_AUTO_BANK_MATCH.ar_accounting_method := NULL;
9816: end if;
9817: */
9818: cep_standard.debug('>>CE_AUTO_BANK_MATCH.Opening r_branch_cursor');
9819: OPEN r_branch_cursor( CE_AUTO_BANK_REC.G_bank_branch_id,
9820: CE_AUTO_BANK_REC.G_bank_account_id,
9821: CE_AUTO_BANK_REC.G_org_id,
9822: CE_AUTO_BANK_REC.G_legal_entity_id);
9821: CE_AUTO_BANK_REC.G_org_id,
9822: CE_AUTO_BANK_REC.G_legal_entity_id);
9823: j := 0;
9824: LOOP
9825: cep_standard.debug('>>CE_AUTO_BANK_MATCH.Fetching r_branch_cursor');
9826: FETCH r_branch_cursor INTO CE_AUTO_BANK_MATCH.csh_bank_account_id,
9827: CE_AUTO_BANK_MATCH.ba_owner_le_id,
9828: CE_AUTO_BANK_MATCH.aba_asset_code_combination_id;
9829: /*
9841:
9842: -- populate the GT table
9843: populate_available_gt (CE_AUTO_BANK_MATCH.csh_bank_account_id);
9844: */
9845: cep_standard.debug('CE_AUTO_BANK_MATCH.csh_bank_account_id = '||CE_AUTO_BANK_MATCH.csh_bank_account_id);
9846: --||', CE_AUTO_BANK_MATCH.bau_bank_acct_use_id = '||CE_AUTO_BANK_MATCH.bau_bank_acct_use_id);
9847:
9848: -- For each bank account set the use flag
9849: set_acct_type(CE_AUTO_BANK_MATCH.csh_bank_account_id);
9848: -- For each bank account set the use flag
9849: set_acct_type(CE_AUTO_BANK_MATCH.csh_bank_account_id);
9850:
9851: /*
9852: cep_standard.debug('CE_AUTO_BANK_MATCH.bau_org_id = '||CE_AUTO_BANK_MATCH.bau_org_id
9853: ||',CE_AUTO_BANK_MATCH.bau_legal_entity_id = '||CE_AUTO_BANK_MATCH.bau_legal_entity_id);
9854:
9855: select mo_global.GET_CURRENT_ORG_ID
9856: into current_org_id
9855: select mo_global.GET_CURRENT_ORG_ID
9856: into current_org_id
9857: from dual;
9858:
9859: cep_standard.debug('current_org_id =' ||current_org_id );
9860:
9861: -- bug 3782741 set single org, since AR will not allow org_id to be passed
9862: --IF CE_AUTO_BANK_MATCH.bau_org_id is not null THEN
9863: IF CE_AUTO_BANK_REC.G_org_id is not null THEN
9862: --IF CE_AUTO_BANK_MATCH.bau_org_id is not null THEN
9863: IF CE_AUTO_BANK_REC.G_org_id is not null THEN
9864: IF ((current_org_id is null) or (CE_AUTO_BANK_MATCH.bau_org_id <> current_org_id )) THEN
9865: mo_global.set_policy_context('S',CE_AUTO_BANK_MATCH.bau_org_id);
9866: cep_standard.debug('set current_org_id to ' ||CE_AUTO_BANK_MATCH.bau_org_id );
9867: END IF;
9868: END IF;
9869:
9870: cep_standard.debug('Get AR accounting method.');
9866: cep_standard.debug('set current_org_id to ' ||CE_AUTO_BANK_MATCH.bau_org_id );
9867: END IF;
9868: END IF;
9869:
9870: cep_standard.debug('Get AR accounting method.');
9871:
9872: -- bug 1796965
9873: SELECT count(*)
9874: INTO accounting_method_found
9950:
9951:
9952: END IF;
9953:
9954: cep_standard.debug('CE_AUTO_BANK_MATCH.BA_ROWID '|| CE_AUTO_BANK_MATCH.BA_ROWID);
9955: cep_standard.debug('CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance ||
9956: ', CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance);
9957: cep_standard.debug('CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance ||
9958: ', CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
9951:
9952: END IF;
9953:
9954: cep_standard.debug('CE_AUTO_BANK_MATCH.BA_ROWID '|| CE_AUTO_BANK_MATCH.BA_ROWID);
9955: cep_standard.debug('CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance ||
9956: ', CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance);
9957: cep_standard.debug('CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance ||
9958: ', CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
9959: cep_standard.debug('CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance ||
9953:
9954: cep_standard.debug('CE_AUTO_BANK_MATCH.BA_ROWID '|| CE_AUTO_BANK_MATCH.BA_ROWID);
9955: cep_standard.debug('CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance ||
9956: ', CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance);
9957: cep_standard.debug('CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance ||
9958: ', CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
9959: cep_standard.debug('CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance ||
9960: ', CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
9961: cep_standard.debug('CE_AUTO_BANK_REC.G_float_handling_flag='||CE_AUTO_BANK_REC.G_float_handling_flag ||
9955: cep_standard.debug('CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance ||
9956: ', CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ap_percent_tolerance);
9957: cep_standard.debug('CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance ||
9958: ', CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
9959: cep_standard.debug('CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance ||
9960: ', CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
9961: cep_standard.debug('CE_AUTO_BANK_REC.G_float_handling_flag='||CE_AUTO_BANK_REC.G_float_handling_flag ||
9962: ', CE_AUTO_BANK_REC.G_ap_matching_order ='|| CE_AUTO_BANK_REC.G_ap_matching_order ||
9963: ', CE_AUTO_BANK_REC.G_ar_matching_order ='|| CE_AUTO_BANK_REC.G_ar_matching_order);
9957: cep_standard.debug('CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance ||
9958: ', CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance);
9959: cep_standard.debug('CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance= ' || CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance ||
9960: ', CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
9961: cep_standard.debug('CE_AUTO_BANK_REC.G_float_handling_flag='||CE_AUTO_BANK_REC.G_float_handling_flag ||
9962: ', CE_AUTO_BANK_REC.G_ap_matching_order ='|| CE_AUTO_BANK_REC.G_ap_matching_order ||
9963: ', CE_AUTO_BANK_REC.G_ar_matching_order ='|| CE_AUTO_BANK_REC.G_ar_matching_order);
9964: cep_standard.debug('CE_AUTO_BANK_REC.G_exchange_rate_type='||CE_AUTO_BANK_REC.G_exchange_rate_type ||
9965: ', CE_AUTO_BANK_REC.G_exchange_rate_date='|| CE_AUTO_BANK_REC.G_exchange_rate_date);
9960: ', CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance= '||CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance);
9961: cep_standard.debug('CE_AUTO_BANK_REC.G_float_handling_flag='||CE_AUTO_BANK_REC.G_float_handling_flag ||
9962: ', CE_AUTO_BANK_REC.G_ap_matching_order ='|| CE_AUTO_BANK_REC.G_ap_matching_order ||
9963: ', CE_AUTO_BANK_REC.G_ar_matching_order ='|| CE_AUTO_BANK_REC.G_ar_matching_order);
9964: cep_standard.debug('CE_AUTO_BANK_REC.G_exchange_rate_type='||CE_AUTO_BANK_REC.G_exchange_rate_type ||
9965: ', CE_AUTO_BANK_REC.G_exchange_rate_date='|| CE_AUTO_BANK_REC.G_exchange_rate_date);
9966: cep_standard.debug('CE_AUTO_BANK_REC.G_open_interface_flag='|| CE_AUTO_BANK_REC.G_open_interface_flag||
9967: ', REC.G_open_interface_float_status='|| CE_AUTO_BANK_REC.G_open_interface_float_status ||
9968: ', REC.G_open_interface_clear_status='|| CE_AUTO_BANK_REC.G_open_interface_clear_status);
9962: ', CE_AUTO_BANK_REC.G_ap_matching_order ='|| CE_AUTO_BANK_REC.G_ap_matching_order ||
9963: ', CE_AUTO_BANK_REC.G_ar_matching_order ='|| CE_AUTO_BANK_REC.G_ar_matching_order);
9964: cep_standard.debug('CE_AUTO_BANK_REC.G_exchange_rate_type='||CE_AUTO_BANK_REC.G_exchange_rate_type ||
9965: ', CE_AUTO_BANK_REC.G_exchange_rate_date='|| CE_AUTO_BANK_REC.G_exchange_rate_date);
9966: cep_standard.debug('CE_AUTO_BANK_REC.G_open_interface_flag='|| CE_AUTO_BANK_REC.G_open_interface_flag||
9967: ', REC.G_open_interface_float_status='|| CE_AUTO_BANK_REC.G_open_interface_float_status ||
9968: ', REC.G_open_interface_clear_status='|| CE_AUTO_BANK_REC.G_open_interface_clear_status);
9969: cep_standard.debug('REC.G_open_interface_matching_code='||CE_AUTO_BANK_REC.G_open_interface_matching_code||
9970: ', BA_RECON_OI_AMOUNT_TOLERANCE='|| BA_RECON_OI_AMOUNT_TOLERANCE ||
9965: ', CE_AUTO_BANK_REC.G_exchange_rate_date='|| CE_AUTO_BANK_REC.G_exchange_rate_date);
9966: cep_standard.debug('CE_AUTO_BANK_REC.G_open_interface_flag='|| CE_AUTO_BANK_REC.G_open_interface_flag||
9967: ', REC.G_open_interface_float_status='|| CE_AUTO_BANK_REC.G_open_interface_float_status ||
9968: ', REC.G_open_interface_clear_status='|| CE_AUTO_BANK_REC.G_open_interface_clear_status);
9969: cep_standard.debug('REC.G_open_interface_matching_code='||CE_AUTO_BANK_REC.G_open_interface_matching_code||
9970: ', BA_RECON_OI_AMOUNT_TOLERANCE='|| BA_RECON_OI_AMOUNT_TOLERANCE ||
9971: ', BA_RECON_OI_PERCENT_TOLERANCE='|| BA_RECON_OI_PERCENT_TOLERANCE);
9972: cep_standard.debug('BA_RECON_AP_FX_DIFF_HANDLING='|| BA_RECON_AP_FX_DIFF_HANDLING ||
9973: ', BA_RECON_AR_FX_DIFF_HANDLING='|| BA_RECON_AR_FX_DIFF_HANDLING ||
9968: ', REC.G_open_interface_clear_status='|| CE_AUTO_BANK_REC.G_open_interface_clear_status);
9969: cep_standard.debug('REC.G_open_interface_matching_code='||CE_AUTO_BANK_REC.G_open_interface_matching_code||
9970: ', BA_RECON_OI_AMOUNT_TOLERANCE='|| BA_RECON_OI_AMOUNT_TOLERANCE ||
9971: ', BA_RECON_OI_PERCENT_TOLERANCE='|| BA_RECON_OI_PERCENT_TOLERANCE);
9972: cep_standard.debug('BA_RECON_AP_FX_DIFF_HANDLING='|| BA_RECON_AP_FX_DIFF_HANDLING ||
9973: ', BA_RECON_AR_FX_DIFF_HANDLING='|| BA_RECON_AR_FX_DIFF_HANDLING ||
9974: ', BA_RECON_CE_FX_DIFF_HANDLING='|| BA_RECON_CE_FX_DIFF_HANDLING);
9975: cep_standard.debug('REC.G_differences_account=' || CE_AUTO_BANK_REC.G_differences_account ||
9976: ', REC.G_CE_DIFFERENCES_ACCOUNT=' || CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT);
9971: ', BA_RECON_OI_PERCENT_TOLERANCE='|| BA_RECON_OI_PERCENT_TOLERANCE);
9972: cep_standard.debug('BA_RECON_AP_FX_DIFF_HANDLING='|| BA_RECON_AP_FX_DIFF_HANDLING ||
9973: ', BA_RECON_AR_FX_DIFF_HANDLING='|| BA_RECON_AR_FX_DIFF_HANDLING ||
9974: ', BA_RECON_CE_FX_DIFF_HANDLING='|| BA_RECON_CE_FX_DIFF_HANDLING);
9975: cep_standard.debug('REC.G_differences_account=' || CE_AUTO_BANK_REC.G_differences_account ||
9976: ', REC.G_CE_DIFFERENCES_ACCOUNT=' || CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT);
9977:
9978:
9979:
9996: -- populate the GT table
9997: populate_available_gt (CE_AUTO_BANK_MATCH.csh_bank_account_id);
9998: END IF;
9999:
10000: cep_standard.debug('>>CE_AUTO_BANK_MATCH.Opening r_bank_cursor');
10001: OPEN r_bank_cursor (CE_AUTO_BANK_REC.G_statement_number_from,
10002: CE_AUTO_BANK_REC.G_statement_number_to,
10003: CE_AUTO_BANK_REC.G_statement_date_from,
10004: CE_AUTO_BANK_REC.G_statement_date_to,
10004: CE_AUTO_BANK_REC.G_statement_date_to,
10005: CE_AUTO_BANK_MATCH.csh_bank_account_id);
10006: i := 0;
10007: LOOP
10008: cep_standard.debug('>>CE_AUTO_BANK_MATCH.Fetching r_bank_cursor');
10009: FETCH r_bank_cursor INTO CE_AUTO_BANK_MATCH.csh_statement_header_id,
10010: CE_AUTO_BANK_MATCH.csh_statement_number,
10011: CE_AUTO_BANK_MATCH.csh_statement_date,
10012: CE_AUTO_BANK_MATCH.csh_check_digits,
10015: CE_AUTO_BANK_MATCH.aba_multi_currency_flag,
10016: CE_AUTO_BANK_MATCH.aba_check_digits,
10017: CE_AUTO_BANK_MATCH.csh_rowid,
10018: CE_AUTO_BANK_MATCH.csh_statement_complete_flag;
10019: cep_standard.debug('>>CE_AUTO_BANK_MATCH.After fetch header');
10020: cep_standard.debug('>>CE_AUTO_BANK_MATCH.statement_header_id:' ||
10021: CE_AUTO_BANK_MATCH.csh_statement_header_id );
10022:
10023: if (r_bank_cursor%ROWCOUNT = i) then
10016: CE_AUTO_BANK_MATCH.aba_check_digits,
10017: CE_AUTO_BANK_MATCH.csh_rowid,
10018: CE_AUTO_BANK_MATCH.csh_statement_complete_flag;
10019: cep_standard.debug('>>CE_AUTO_BANK_MATCH.After fetch header');
10020: cep_standard.debug('>>CE_AUTO_BANK_MATCH.statement_header_id:' ||
10021: CE_AUTO_BANK_MATCH.csh_statement_header_id );
10022:
10023: if (r_bank_cursor%ROWCOUNT = i) then
10024: EXIT;
10078: --
10079: -- Read in all the lines on the statement for the selected bank
10080: -- account.
10081: --
10082: cep_standard.debug('>>CE_AUTO_BANK_MATCH.Opening line_cursor');
10083: OPEN line_cursor (CE_AUTO_BANK_MATCH.csh_statement_header_id);
10084: LOOP
10085: FETCH line_cursor INTO CE_AUTO_BANK_MATCH.csl_rowid,
10086: CE_AUTO_BANK_MATCH.csl_statement_line_id,
10108: ignore_trx_id,
10109: ignore_trx_id2 ;
10110: EXIT WHEN line_cursor%NOTFOUND OR line_cursor%NOTFOUND IS NULL;
10111:
10112: cep_standard.debug('========= new statement line ============ ');
10113:
10114: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_statement_line_id = '|| CE_AUTO_BANK_MATCH.csl_statement_line_id
10115: ||' CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format
10116: ||' CE_AUTO_BANK_MATCH.csl_cashflow_id = '|| CE_AUTO_BANK_MATCH.csl_cashflow_id);
10110: EXIT WHEN line_cursor%NOTFOUND OR line_cursor%NOTFOUND IS NULL;
10111:
10112: cep_standard.debug('========= new statement line ============ ');
10113:
10114: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_statement_line_id = '|| CE_AUTO_BANK_MATCH.csl_statement_line_id
10115: ||' CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format
10116: ||' CE_AUTO_BANK_MATCH.csl_cashflow_id = '|| CE_AUTO_BANK_MATCH.csl_cashflow_id);
10117:
10118:
10201: END IF;
10202:
10203: --CE_AUTO_BANK_MATCH.calc_csl_amount := CE_AUTO_BANK_MATCH.csl_amount;
10204:
10205: cep_standard.debug('CE_AUTO_BANK_MATCH.calc_csl_amount = '|| CE_AUTO_BANK_MATCH.calc_csl_amount);
10206:
10207: /*JEC*/
10208: -- bug4435028
10209: --IF(CE_AUTO_BANK_MATCH.csl_je_status_flag = 'C') THEN
10210: IF(CE_AUTO_BANK_MATCH.csl_cashflow_id is not null) THEN --this will handle JEC/ZBA(?) trx
10211: match_stmt_line_JE;
10212: ELSE
10213: -- bug 4435028 multi-matching based on trx_code
10214: cep_standard.debug('>>CE_AUTO_BANK_MATCH.Opening trx_code_cursor');
10215:
10216: OPEN trx_code_cursor (CE_AUTO_BANK_MATCH.csl_statement_line_id,CE_AUTO_BANK_MATCH.csh_bank_account_id);
10217: LOOP
10218: FETCH trx_code_cursor INTO
10226: CE_AUTO_BANK_MATCH.csl_payroll_payment_format;
10227:
10228: EXIT WHEN trx_code_cursor%NOTFOUND OR trx_code_cursor%NOTFOUND IS NULL;
10229:
10230: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag = '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag
10231: ||', CE_AUTO_BANK_MATCH.csl_matching_against = '|| CE_AUTO_BANK_MATCH.csl_matching_against
10232: ||', CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
10233:
10234: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_found = '|| CE_AUTO_BANK_MATCH.csl_match_found);
10230: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_reconcile_flag = '|| CE_AUTO_BANK_MATCH.csl_reconcile_flag
10231: ||', CE_AUTO_BANK_MATCH.csl_matching_against = '|| CE_AUTO_BANK_MATCH.csl_matching_against
10232: ||', CE_AUTO_BANK_MATCH.csl_payroll_payment_format = '|| CE_AUTO_BANK_MATCH.csl_payroll_payment_format);
10233:
10234: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_found = '|| CE_AUTO_BANK_MATCH.csl_match_found);
10235:
10236: IF (CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL') ) THEN
10237:
10238: CE_AUTO_BANK_MATCH.match_engine;
10236: IF (CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL') ) THEN
10237:
10238: CE_AUTO_BANK_MATCH.match_engine;
10239:
10240: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_found = '|| CE_AUTO_BANK_MATCH.csl_match_found);
10241: IF (CE_AUTO_BANK_MATCH.csl_match_found = 'FULL') THEN
10242:
10243: -- bug 4914608 set the org after a match for AR/AP
10244: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_org_id =' ||CE_AUTO_BANK_MATCH.trx_org_id);
10240: cep_standard.debug('CE_AUTO_BANK_MATCH.csl_match_found = '|| CE_AUTO_BANK_MATCH.csl_match_found);
10241: IF (CE_AUTO_BANK_MATCH.csl_match_found = 'FULL') THEN
10242:
10243: -- bug 4914608 set the org after a match for AR/AP
10244: cep_standard.debug('CE_AUTO_BANK_MATCH.trx_org_id =' ||CE_AUTO_BANK_MATCH.trx_org_id);
10245: set_single_org(CE_AUTO_BANK_MATCH.trx_org_id);
10246:
10247: CE_AUTO_BANK_CLEAR.reconcile_process;
10248:
10276: END IF; --CE_AUTO_BANK_MATCH.csl_match_found IN ('ERROR','NONE','PARTIAL')
10277: END LOOP; -- trx_code_cursor
10278:
10279: x_trx_code_row_count := trx_code_cursor%ROWCOUNT;
10280: cep_standard.debug('x_trx_code_row_count = '|| x_trx_code_row_count );
10281:
10282: CLOSE trx_code_cursor;
10283: END IF; -- cashflow_id is not null
10284: ELSE -- statement line is locked
10337: END LOOP; -- statement headers
10338: CLOSE r_bank_cursor;
10339: END LOOP;
10340: CLOSE r_branch_cursor;
10341: cep_standard.debug('<
10343:
10344: /* Bug 3364143 start code fix */
10345: cep_standard.disable_debug(CE_AUTO_BANK_REC.G_display_debug);
10338: CLOSE r_bank_cursor;
10339: END LOOP;
10340: CLOSE r_branch_cursor;
10341: cep_standard.debug('<
10343:
10344: /* Bug 3364143 start code fix */
10345: cep_standard.disable_debug(CE_AUTO_BANK_REC.G_display_debug);
10346: /* Bug 3364143 end code fix */
10341: cep_standard.debug('<
10343:
10344: /* Bug 3364143 start code fix */
10345: cep_standard.disable_debug(CE_AUTO_BANK_REC.G_display_debug);
10346: /* Bug 3364143 end code fix */
10347: EXCEPTION
10348: WHEN OTHERS THEN
10349: cep_standard.debug(' EXCEPTION: CE_AUTO_BANK_MATCH.match_process - OTHERS');
10345: cep_standard.disable_debug(CE_AUTO_BANK_REC.G_display_debug);
10346: /* Bug 3364143 end code fix */
10347: EXCEPTION
10348: WHEN OTHERS THEN
10349: cep_standard.debug(' EXCEPTION: CE_AUTO_BANK_MATCH.match_process - OTHERS');
10350: IF r_branch_cursor%ISOPEN THEN
10351: CLOSE r_branch_cursor;
10352: END IF;
10353: IF r_bank_cursor%ISOPEN THEN
10359: IF trx_code_cursor%ISOPEN THEN
10360: CLOSE trx_code_cursor;
10361: END IF;
10362: lock_status := dbms_lock.release(lockhandle);
10363: cep_standard.debug('DEBUG: sqlcode:' || sqlcode );
10364: cep_standard.debug('DEBUG: sqlerrm:' || sqlerrm);
10365: RAISE;
10366: END match_process;
10367:
10360: CLOSE trx_code_cursor;
10361: END IF;
10362: lock_status := dbms_lock.release(lockhandle);
10363: cep_standard.debug('DEBUG: sqlcode:' || sqlcode );
10364: cep_standard.debug('DEBUG: sqlerrm:' || sqlerrm);
10365: RAISE;
10366: END match_process;
10367:
10368: END CE_AUTO_BANK_MATCH;