[Home] [Help]
625: | set_single_org |
626: | |
627: | DESCRIPTION |
628: | set to single org for AR/AP processing when
629: | CE_AUTO_BANK_REC.G_org_id is null
630: | - always use base table (_ALL) for AP/AR table when try to find
631: | a match. Our ce_security_profile_gt will handle the security |
632: | - set single org is needed when call AR/AP API, since we cannot
633: | pass the org_id
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)
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 );
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);
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
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
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,
805: CE_AUTO_BANK_MATCH.gt_seq_id
806: --FROM ce_185_transactions_v catv
807: FROM ce_available_transactions_tmp catv
808: WHERE catv.trx_type = tx_type
809: AND catv.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id, catv.legal_entity_id)
810: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
811: AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
812: AND to_char(catv.trx_date,'YYYY/MM/DD') =
813: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
865: CE_AUTO_BANK_MATCH.gt_seq_id
866: --FROM ce_999_transactions_v catv
867: FROM ce_available_transactions_tmp catv
868: WHERE catv.trx_type = tx_type
869: --AND catv.org_id = CE_AUTO_BANK_REC.G_org_id
870: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
871: AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
872: AND to_char(catv.trx_date,'YYYY/MM/DD') =
873: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
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);
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,
941: CE_AUTO_BANK_MATCH.csl_currency_code,
942: nvl(CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
943: CE_AUTO_BANK_MATCH.csl_trx_date),
944: fixed_relation,
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:
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:
993: IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW') THEN
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;
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;
995: ELSE
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;
995: ELSE
996: CE_AUTO_BANK_MATCH.csl_exchange_rate_type := CE_AUTO_BANK_REC.G_exchange_rate_type;
997:
998: END IF;
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;
995: ELSE
996: CE_AUTO_BANK_MATCH.csl_exchange_rate_type := CE_AUTO_BANK_REC.G_exchange_rate_type;
997:
998: END IF;
999:
1000: IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW') THEN
998: END IF;
999:
1000: IF (CE_AUTO_BANK_MATCH.csl_clearing_trx_type = 'CASHFLOW') THEN
1001: IF (CE_AUTO_BANK_MATCH.trx_reference_type = 'STMT') THEN -- JEC
1002: IF (CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE = 'CFD') THEN
1003: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1004: CE_AUTO_BANK_MATCH.trx_date;
1005: ELSIF (CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE = 'CLD') THEN
1006: /* The statement line trx date is used for the cleared date in autoReconciliation
1001: IF (CE_AUTO_BANK_MATCH.trx_reference_type = 'STMT') THEN -- JEC
1002: IF (CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE = 'CFD') THEN
1003: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1004: CE_AUTO_BANK_MATCH.trx_date;
1005: ELSIF (CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE = 'CLD') THEN
1006: /* The statement line trx date is used for the cleared date in autoReconciliation
1007: So, we will use the matching transaction cleared date when the cashflow exchange
1008: date type is set to use the cleared date. If there is no cleared date, then the
1009: statement line transaction date will be used. */
1010:
1011: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1012: NVL(CE_AUTO_BANK_MATCH.trx_cleared_date,CE_AUTO_BANK_MATCH.csl_trx_date);
1013: --NVL(CE_AUTO_BANK_MATCH.trx_cleared_date,CE_AUTO_BANK_MATCH.trx_date);
1014: ELSIF (CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE = 'BSD') THEN
1015: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1016: CE_AUTO_BANK_MATCH.csh_statement_date;
1017: ELSIF (CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE = 'BSG') THEN
1018: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1013: --NVL(CE_AUTO_BANK_MATCH.trx_cleared_date,CE_AUTO_BANK_MATCH.trx_date);
1014: ELSIF (CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE = 'BSD') THEN
1015: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1016: CE_AUTO_BANK_MATCH.csh_statement_date;
1017: ELSIF (CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE = 'BSG') THEN
1018: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1019: CE_AUTO_BANK_MATCH.csh_statement_gl_date;
1020: END IF;
1021:
1020: END IF;
1021:
1022:
1023: ELSE -- BAT
1024: IF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'CFD') THEN
1025: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1026: CE_AUTO_BANK_MATCH.trx_date;
1027: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'AVD') THEN
1028: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1023: ELSE -- BAT
1024: IF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'CFD') THEN
1025: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1026: CE_AUTO_BANK_MATCH.trx_date;
1027: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'AVD') THEN
1028: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1029: CE_AUTO_BANK_MATCH.trx_value_date;
1030: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'CLD') THEN
1031: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1026: CE_AUTO_BANK_MATCH.trx_date;
1027: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'AVD') THEN
1028: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1029: CE_AUTO_BANK_MATCH.trx_value_date;
1030: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'CLD') THEN
1031: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1032: NVL(CE_AUTO_BANK_MATCH.trx_cleared_date,CE_AUTO_BANK_MATCH.csl_trx_date);
1033: --NVL(CE_AUTO_BANK_MATCH.trx_cleared_date,CE_AUTO_BANK_MATCH.trx_date);
1034: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'BSD') THEN
1030: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'CLD') THEN
1031: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1032: NVL(CE_AUTO_BANK_MATCH.trx_cleared_date,CE_AUTO_BANK_MATCH.csl_trx_date);
1033: --NVL(CE_AUTO_BANK_MATCH.trx_cleared_date,CE_AUTO_BANK_MATCH.trx_date);
1034: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'BSD') THEN
1035: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1036: CE_AUTO_BANK_MATCH.csh_statement_date;
1037: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'BSG') THEN
1038: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1033: --NVL(CE_AUTO_BANK_MATCH.trx_cleared_date,CE_AUTO_BANK_MATCH.trx_date);
1034: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'BSD') THEN
1035: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1036: CE_AUTO_BANK_MATCH.csh_statement_date;
1037: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'BSG') THEN
1038: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1039: CE_AUTO_BANK_MATCH.csh_statement_gl_date;
1040: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'SLD') THEN
1041: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1036: CE_AUTO_BANK_MATCH.csh_statement_date;
1037: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'BSG') THEN
1038: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1039: CE_AUTO_BANK_MATCH.csh_statement_gl_date;
1040: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'SLD') THEN
1041: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1042: CE_AUTO_BANK_MATCH.csl_trx_date;
1043: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'TRX') THEN
1044: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1039: CE_AUTO_BANK_MATCH.csh_statement_gl_date;
1040: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'SLD') THEN
1041: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1042: CE_AUTO_BANK_MATCH.csl_trx_date;
1043: ELSIF (CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE = 'TRX') THEN
1044: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1045: CE_AUTO_BANK_MATCH.trx_deposit_date;
1046: END IF;
1047:
1047:
1048: END IF;
1049: ELSE -- not cashflow
1050:
1051: IF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'SLD') THEN
1052: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1053: CE_AUTO_BANK_MATCH.csl_trx_date;
1054: ELSIF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'BSD') THEN
1055: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1050:
1051: IF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'SLD') THEN
1052: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1053: CE_AUTO_BANK_MATCH.csl_trx_date;
1054: ELSIF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'BSD') THEN
1055: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1056: CE_AUTO_BANK_MATCH.csh_statement_date;
1057: ELSIF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'BGD') THEN
1058: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1053: CE_AUTO_BANK_MATCH.csl_trx_date;
1054: ELSIF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'BSD') THEN
1055: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1056: CE_AUTO_BANK_MATCH.csh_statement_date;
1057: ELSIF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'BGD') THEN
1058: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1059: CE_AUTO_BANK_MATCH.csh_statement_gl_date;
1060: ELSIF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'TCD') THEN
1061: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1056: CE_AUTO_BANK_MATCH.csh_statement_date;
1057: ELSIF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'BGD') THEN
1058: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1059: CE_AUTO_BANK_MATCH.csh_statement_gl_date;
1060: ELSIF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'TCD') THEN
1061: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1062: CE_AUTO_BANK_MATCH.trx_date;
1063: ELSIF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'TXD') THEN
1064: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1059: CE_AUTO_BANK_MATCH.csh_statement_gl_date;
1060: ELSIF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'TCD') THEN
1061: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1062: CE_AUTO_BANK_MATCH.trx_date;
1063: ELSIF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'TXD') THEN
1064: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1065: CE_AUTO_BANK_MATCH.trx_exchange_rate_date;
1066: ELSIF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'TGD') THEN
1067: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1062: CE_AUTO_BANK_MATCH.trx_date;
1063: ELSIF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'TXD') THEN
1064: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1065: CE_AUTO_BANK_MATCH.trx_exchange_rate_date;
1066: ELSIF (CE_AUTO_BANK_REC.G_exchange_rate_date = 'TGD') THEN
1067: CE_AUTO_BANK_MATCH.csl_exchange_rate_date :=
1068: CE_AUTO_BANK_MATCH.trx_gl_date;
1069: END IF;
1070:
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:
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:
1644: --FROM ce_260_cf_transactions_v catv
1645: FROM ce_available_transactions_tmp catv
1646: WHERE catv.trx_type = tx_type
1647: AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
1648: AND catv.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id , catv.legal_entity_id)
1649: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
1650: AND catv.check_number = CE_AUTO_BANK_MATCH.csl_statement_line_id
1651: AND catv.application_id = 261
1652: AND NVL(catv.reconciled_status_flag, 'N') = 'N';
1678: --FROM ce_260_cf_transactions_v catv
1679: FROM ce_available_transactions_tmp catv
1680: WHERE catv.trx_type = tx_type
1681: AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
1682: AND catv.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id, catv.legal_entity_id)
1683: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
1684: AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
1685: and catv.check_number is null
1686: AND to_char(catv.trx_date,'YYYY/MM/DD') =
1715: --FROM ce_260_cf_transactions_v catv
1716: FROM ce_available_transactions_tmp catv
1717: WHERE catv.trx_type = tx_type
1718: AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
1719: --AND catv.legal_entity_id = CE_AUTO_BANK_REC.G_legal_entity_id
1720: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
1721: --AND catv.customer_id = CE_AUTO_BANK_MATCH.csl_bank_trx_number
1722: AND catv.bank_account_text = CE_AUTO_BANK_MATCH.csl_bank_account_text
1723: and catv.check_number is null
1882: INTO
1883: CE_AUTO_BANK_MATCH.G_le_amount_tolerance,
1884: CE_AUTO_BANK_MATCH.G_le_percent_tolerance,
1885: CE_AUTO_BANK_MATCH.G_le_Fx_Difference_Handling,
1886: CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT,
1887: CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
1888: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
1889: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
1890: CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE,
1883: CE_AUTO_BANK_MATCH.G_le_amount_tolerance,
1884: CE_AUTO_BANK_MATCH.G_le_percent_tolerance,
1885: CE_AUTO_BANK_MATCH.G_le_Fx_Difference_Handling,
1886: CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT,
1887: CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
1888: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
1889: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
1890: CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE,
1891: le_found;
1884: CE_AUTO_BANK_MATCH.G_le_percent_tolerance,
1885: CE_AUTO_BANK_MATCH.G_le_Fx_Difference_Handling,
1886: CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT,
1887: CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
1888: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
1889: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
1890: CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE,
1891: le_found;
1892:
1885: CE_AUTO_BANK_MATCH.G_le_Fx_Difference_Handling,
1886: CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT,
1887: CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
1888: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
1889: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
1890: CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE,
1891: le_found;
1892:
1893: CLOSE le_sys_par;
1886: CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT,
1887: CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
1888: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
1889: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
1890: CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE,
1891: le_found;
1892:
1893: CLOSE le_sys_par;
1894:
1898: --no system parameter set at LE level default value
1899: CE_AUTO_BANK_MATCH.G_le_amount_tolerance := 0;
1900: CE_AUTO_BANK_MATCH.G_le_percent_tolerance := 0;
1901: CE_AUTO_BANK_MATCH.G_le_Fx_Difference_Handling := 'F';
1902: CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT := 'CHARGES';
1903: --CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE
1904: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT :='NR';
1905: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE :='BSD';
1906: CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE :='TRX';
1899: CE_AUTO_BANK_MATCH.G_le_amount_tolerance := 0;
1900: CE_AUTO_BANK_MATCH.G_le_percent_tolerance := 0;
1901: CE_AUTO_BANK_MATCH.G_le_Fx_Difference_Handling := 'F';
1902: CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT := 'CHARGES';
1903: --CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE
1904: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT :='NR';
1905: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE :='BSD';
1906: CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE :='TRX';
1907:
1900: CE_AUTO_BANK_MATCH.G_le_percent_tolerance := 0;
1901: CE_AUTO_BANK_MATCH.G_le_Fx_Difference_Handling := 'F';
1902: CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT := 'CHARGES';
1903: --CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE
1904: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT :='NR';
1905: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE :='BSD';
1906: CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE :='TRX';
1907:
1908: END IF;
1901: CE_AUTO_BANK_MATCH.G_le_Fx_Difference_Handling := 'F';
1902: CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT := 'CHARGES';
1903: --CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE
1904: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT :='NR';
1905: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE :='BSD';
1906: CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE :='TRX';
1907:
1908: END IF;
1909: */
1902: CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT := 'CHARGES';
1903: --CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE
1904: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT :='NR';
1905: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE :='BSD';
1906: CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE :='TRX';
1907:
1908: END IF;
1909: */
1910: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <>
1907:
1908: END IF;
1909: */
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;
2013: FROM ce_available_transactions_tmp catv
2014: WHERE upper(catv.batch_name) =
2015: upper(CE_AUTO_BANK_MATCH.csl_bank_trx_number)
2016: AND catv.trx_date = CE_AUTO_BANK_MATCH.csl_trx_date
2017: AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
2018: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
2019: AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
2020: AND nvl(catv.status, 'C') <> 'V'
2021: AND catv.application_id = 802
2039: RAISE NO_DATA_FOUND;
2040: END IF;
2041:
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;
2211: AND aba.bank_account_id = ba.bank_account_id
2212: AND aba.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id --bug5182963
2213: AND BA.ACCOUNT_OWNER_ORG_ID = SP.LEGAL_ENTITY_ID
2214: AND aba.org_id = c.org_id
2215: AND aba.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, c.org_id)
2216: --AND aba.bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
2217: AND aba.AP_USE_ENABLE_FLAG = 'Y'
2218: AND c.status_lookup_code IN
2219: ('NEGOTIABLE', 'STOP INITIATED',
2253: SELECT distinct(crh.cash_receipt_history_id),
2254: crh.cash_receipt_id,
2255: crh.rowid,
2256: DECODE(cr.currency_code,
2257: CE_AUTO_BANK_REC.G_functional_currency, crh.amount,
2258: CE_AUTO_BANK_MATCH.aba_bank_currency, crh.amount,
2259: NVL(crh.acctd_amount,crh.amount)),
2260: crh.acctd_amount,
2261: crh.status,
2327: --AND aba.bank_acct_use_id = cr.remittance_bank_account_id
2328: --AND cr.remit_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
2329: AND aba.bank_acct_use_id = cr.remit_bank_acct_use_id
2330: AND aba.org_id = cr.org_id
2331: AND aba.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, aba.org_id)
2332: --AND aba.org_id = sp.org_id
2333: and BA.ACCOUNT_OWNER_ORG_ID = SP.LEGAL_ENTITY_ID
2334: AND aba.bank_account_id = ba.bank_account_id
2335: AND aba.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
2349: AND ba2.bank_account_num = CE_AUTO_BANK_MATCH.csl_bank_account_text;
2350: END IF;
2351:
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;
2506: AND aba.bank_account_id = ba.bank_account_id
2507: AND aba.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id --bug5182963
2508: and BA.ACCOUNT_OWNER_ORG_ID = SP.LEGAL_ENTITY_ID
2509: AND aba.org_id = c.org_id
2510: AND aba.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, aba.org_id)
2511: --AND aba.bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
2512: AND c.status_lookup_code IN
2513: ('NEGOTIABLE', 'STOP INITIATED',
2514: DECODE(CE_AUTO_BANK_MATCH.csl_trx_type,
2541: SELECT distinct(crh.cash_receipt_history_id),
2542: crh.cash_receipt_id,
2543: crh.rowid,
2544: DECODE(cr.currency_code,
2545: CE_AUTO_BANK_REC.G_functional_currency, crh.amount,
2546: CE_AUTO_BANK_MATCH.aba_bank_currency, crh.amount,
2547: NVL(crh.acctd_amount,crh.amount)),
2548: crh.acctd_amount,
2549: crh.status,
2613: and crh.org_id = rec.org_id
2614: --AND aba.bank_account_id = cr.remittance_bank_account_id
2615: --AND cr.remittance_bank_account_id =CE_AUTO_BANK_MATCH.csh_bank_account_id
2616: AND aba.bank_acct_use_id = cr.remit_bank_acct_use_id
2617: AND aba.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, aba.org_id)
2618: --AND aba.bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
2619: AND aba.org_id = cr.org_id
2620: --AND aba.org_id = sp.org_id
2621: AND aba.bank_account_id = ba.bank_account_id
2620: --AND aba.org_id = sp.org_id
2621: AND aba.bank_account_id = ba.bank_account_id
2622: and BA.ACCOUNT_OWNER_ORG_ID = SP.LEGAL_ENTITY_ID
2623: AND crh.status = decode(CE_AUTO_BANK_MATCH.csl_trx_type,
2624: 'NSF', decode(CE_AUTO_BANK_REC.G_nsf_handling,
2625: 'REVERSE',crh.status,
2626: 'REVERSED'),
2627: 'REJECTED', decode('REVERSE',
2628: 'REVERSE', crh.status,
2644: AND ps.trx_number = CE_AUTO_BANK_MATCH.csl_invoice_text;
2645: END IF;
2646:
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;
2766: FROM CE_200_BATCHES_V ab
2767: WHERE
2768: UPPER(ab.trx_number) = UPPER(CE_AUTO_BANK_MATCH.csl_bank_trx_number)
2769: --AND ab.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id --bug 4435028 ignore MO security for IBY batches
2770: AND (ab.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, ab.org_id)
2771: OR ab.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id, ab.legal_entity_id))
2772: AND ab.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id;
2773:
2774: --bug 7514063 added NVL to the columns in select clause
2767: WHERE
2768: UPPER(ab.trx_number) = UPPER(CE_AUTO_BANK_MATCH.csl_bank_trx_number)
2769: --AND ab.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id --bug 4435028 ignore MO security for IBY batches
2770: AND (ab.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, ab.org_id)
2771: OR ab.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id, ab.legal_entity_id))
2772: AND ab.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id;
2773:
2774: --bug 7514063 added NVL to the columns in select clause
2775: curr := 2;
2857: CE_AUTO_BANK_MATCH.trx_bank_acct_use_id
2858: FROM CE_222_BATCHES_V ab
2859: WHERE ab.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
2860: --AND ab.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
2861: AND ab.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, ab.org_id)
2862: AND ab.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id;
2863:
2864: curr := 4;
2865: SELECT count(*),
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 <>
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;
3011: CE_AUTO_BANK_MATCH.logical_group_reference,
3012: CE_AUTO_BANK_MATCH.csl_clearing_trx_type -- bug 7571492
3013: FROM CE_200_GROUPS_V ab
3014: WHERE upper(ab.logical_group_reference) = upper(CE_AUTO_BANK_MATCH.csl_bank_trx_number)
3015: AND (ab.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, ab.org_id)
3016: or
3017: ab.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id, ab.legal_entity_id))
3018: AND ab.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id;
3019:
3013: FROM CE_200_GROUPS_V ab
3014: WHERE upper(ab.logical_group_reference) = upper(CE_AUTO_BANK_MATCH.csl_bank_trx_number)
3015: AND (ab.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, ab.org_id)
3016: or
3017: ab.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id, ab.legal_entity_id))
3018: AND ab.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id;
3019:
3020: -- 7571492 : Changed local variables in INTO clause to global variables.
3021: curr := 2;
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
3065: cep_standard.debug('Forex trx not using the same curr as account');
3066: curr := 6;
3118:
3119: BEGIN
3120: acctd_amount := gl_currency_api.convert_amount(
3121: CE_AUTO_BANK_MATCH.csl_currency_code,
3122: CE_AUTO_BANK_REC.G_functional_currency,
3123: nvl(CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
3124: CE_AUTO_BANK_MATCH.csl_trx_date),
3125: CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
3126: amount_to_convert);
3197: CE_AUTO_BANK_MATCH.trx_currency_type);
3198:
3199: IF (trx_currency_type = 'FOREIGN' and
3200: CE_AUTO_BANK_MATCH.aba_bank_currency
3201: <> CE_AUTO_BANK_REC.G_functional_currency) OR
3202: (trx_currency_type = 'FUNCTIONAL' and
3203: CE_AUTO_BANK_MATCH.aba_bank_currency
3204: <> CE_AUTO_BANK_REC.G_functional_currency) THEN
3205:
3200: CE_AUTO_BANK_MATCH.aba_bank_currency
3201: <> CE_AUTO_BANK_REC.G_functional_currency) OR
3202: (trx_currency_type = 'FUNCTIONAL' and
3203: CE_AUTO_BANK_MATCH.aba_bank_currency
3204: <> CE_AUTO_BANK_REC.G_functional_currency) THEN
3205:
3206: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3207: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3208: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_NO_FOREIGN_MISC');
3247: -- Verified that exchange information is not null
3248: -- when creating foreign currency misc receipts
3249: --
3250: IF (CE_AUTO_BANK_MATCH.aba_bank_currency
3251: <> CE_AUTO_BANK_REC.G_functional_currency
3252: AND CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag = 'Y'
3253: AND (CE_AUTO_BANK_MATCH.csl_exchange_rate_date IS NULL
3254: OR CE_AUTO_BANK_MATCH.csl_exchange_rate_type IS NULL
3255: OR CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL) ) THEN
3269: base_receipt_amount := receipt_amount;
3270: ELSIF (CE_AUTO_BANK_MATCH.csl_exchange_rate_type <> 'User') THEN
3271: BEGIN
3272: receipt_amount := gl_currency_api.convert_amount(
3273: CE_AUTO_BANK_REC.G_functional_currency,
3274: CE_AUTO_BANK_MATCH.csl_currency_code,
3275: nvl(CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
3276: CE_AUTO_BANK_MATCH.csl_trx_date),
3277: CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
3345: X_passin_mode => 'AUTO_TRX',
3346: X_trx_number => l_trx_number,
3347: X_doc_sequence_value => to_number(NULL),
3348: X_doc_sequence_id => to_number(NULL),
3349: X_gl_date => CE_AUTO_BANK_REC.G_gl_date,
3350: X_value_date =>
3351: CE_AUTO_BANK_MATCH.csl_effective_date,
3352: X_trx_date => CE_AUTO_BANK_MATCH.csl_trx_date,
3353: X_deposit_date => CE_AUTO_BANK_MATCH.csl_trx_date,
3353: X_deposit_date => CE_AUTO_BANK_MATCH.csl_trx_date,
3354: X_amount => receipt_amount,
3355: X_bank_account_amount => base_receipt_amount,
3356: X_set_of_books_id =>
3357: CE_AUTO_BANK_REC.G_set_of_books_id,
3358: X_misc_currency_code =>
3359: NVL(CE_AUTO_BANK_MATCH.csl_currency_code,
3360: CE_AUTO_BANK_MATCH.aba_bank_currency),
3361: X_exchange_rate_date => CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
3376: X_reference_type => NULL,
3377: X_reference_id => NULL,
3378: X_clear_currency_code => NULL,
3379: X_statement_line_id => X_statement_line_id,
3380: X_module_name => 'CE_AUTO_BANK_REC',
3381: X_cr_vat_tax_id =>
3382: CE_AUTO_BANK_REC.G_cr_vat_tax_code,
3383: X_dr_vat_tax_id =>
3384: CE_AUTO_BANK_REC.G_dr_vat_tax_code,
3378: X_clear_currency_code => NULL,
3379: X_statement_line_id => X_statement_line_id,
3380: X_module_name => 'CE_AUTO_BANK_REC',
3381: X_cr_vat_tax_id =>
3382: CE_AUTO_BANK_REC.G_cr_vat_tax_code,
3383: X_dr_vat_tax_id =>
3384: CE_AUTO_BANK_REC.G_dr_vat_tax_code,
3385: trx_currency_type =>
3386: CE_AUTO_BANK_MATCH.trx_currency_type,
3380: X_module_name => 'CE_AUTO_BANK_REC',
3381: X_cr_vat_tax_id =>
3382: CE_AUTO_BANK_REC.G_cr_vat_tax_code,
3383: X_dr_vat_tax_id =>
3384: CE_AUTO_BANK_REC.G_dr_vat_tax_code,
3385: trx_currency_type =>
3386: CE_AUTO_BANK_MATCH.trx_currency_type,
3387: X_cr_id =>
3388: CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
3388: CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
3389: X_effective_date =>
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';
3528: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3529: CE_AUTO_BANK_MATCH.csl_statement_line_id,'CE_PMT_AMOUNT');
3530: end if;
3531: ELSIF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'OI' AND
3532: CE_AUTO_BANK_REC.G_open_interface_matching_code = 'D') THEN
3533: -- do not perform the following check for open-interface transactions
3534: -- when matched by Date and Amount
3535: null;
3536: -- 7581995 : Statement line amount is always in bank-account currency
3649: ('PAYMENT','CASH','MISC','PBATCH','RBATCH','CMISC','RECEIPT','PGROUP') --bug 4435028
3650: AND NVL(CE_AUTO_BANK_MATCH.csl_reconcile_flag, 'X') <> 'OI' ) THEN
3651: -- 7571492 : Added PGROUP for csl_match_type
3652: IF (CE_AUTO_BANK_MATCH.csl_match_type IN ('PBATCH','PAYMENT','PGROUP')) THEN
3653: IF(NOT(CE_AUTO_BANK_REC.find_gl_period(CE_AUTO_BANK_REC.G_gl_date, 200))) THEN
3654: valid_trx:=FALSE;
3655: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3656: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3657: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_INVALID_AP_PERIOD');
3662: and (to_char(CE_AUTO_BANK_MATCH.trx_date,'YYYY/MM/DD') >
3663: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'))) THEN
3664: CE_AUTO_BANK_MATCH.trx_clr_flag := 'Y';
3665: END IF;
3666: ELSIF (NOT(CE_AUTO_BANK_REC.find_gl_period(CE_AUTO_BANK_REC.G_gl_date, 222))) THEN
3667: valid_trx:= FALSE;
3668: CE_RECONCILIATION_ERRORS_PKG.insert_row(
3669: CE_AUTO_BANK_MATCH.csh_statement_header_id,
3670: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_INVALID_AR_PERIOD');
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
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
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);
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
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:
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
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
3865: --FROM ce_801_transactions_v catv
3866: FROM ce_available_transactions_tmp catv
3867: WHERE catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
3868: AND catv.bank_account_amount = CE_AUTO_BANK_MATCH.csl_amount
3869: AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
3870: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
3871: AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
3872: AND catv.application_id = 801
3873: AND NVL(catv.reconciled_status_flag, 'N') = 'N';
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
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 ' );
3925:
3926: curr := 3;
3927:
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:
3974: --FROM ce_185_transactions_v catv
3975: FROM ce_available_transactions_tmp catv
3976: WHERE catv.trx_type = tx_type
3977: AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
3978: AND catv.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id,CE_AUTO_BANK_REC.G_legal_entity_id)
3979: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_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';
4024: --FROM ce_999_transactions_v catv
4025: FROM ce_available_transactions_tmp catv
4026: WHERE catv.trx_type = tx_type
4027: AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
4028: --AND catv.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id,CE_AUTO_BANK_REC.G_legal_entity_id)
4029: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
4030: AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
4031: AND catv.application_id = 999
4032: AND NVL(catv.reconciled_status_flag, 'N') = 'N';
4172: 'CASH',
4173: catv.trx_type),
4174: catv.trx_type) = tx_type
4175: AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
4176: AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
4177: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
4178: AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
4179: AND catv.status = decode(CE_AUTO_BANK_REC.G_nsf_handling,
4180: 'REVERSE',catv.status,
4175: AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
4176: AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
4177: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
4178: AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
4179: AND catv.status = decode(CE_AUTO_BANK_REC.G_nsf_handling,
4180: 'REVERSE',catv.status,
4181: 'DM REVERSE',catv.status, 'REVERSED')
4182: UNION
4183: SELECT catv.trx_id,
4209: 'CASH',
4210: catv.trx_type),
4211: catv.trx_type) = tx_type
4212: AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
4213: AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
4214: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
4215: AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
4216: AND catv.status = 'REVERSED'
4217: AND catv.application_id = 222
4273: 'CASH',
4274: catv.trx_type),
4275: catv.trx_type) = tx_type
4276: AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
4277: AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
4278: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
4279: AND catv.trx_number = CE_AUTO_BANK_MATCH.csl_bank_trx_number
4280: AND catv.status <> 'REVERSED'
4281: AND catv.application_id = 222
4335: CE_AUTO_BANK_MATCH.gt_seq_id
4336: --FROM ce_200_transactions_v catv
4337: FROM ce_available_transactions_tmp catv
4338: WHERE catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
4339: AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
4340: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
4341: AND catv.check_number =
4342: to_number(LTRIM(CE_AUTO_BANK_MATCH.csl_bank_trx_number, '0'))
4343: AND catv.application_id = 200
4390: --FROM ce_200_transactions_v catv
4391: FROM ce_available_transactions_tmp catv
4392: WHERE catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
4393: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
4394: AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,catv.org_id)
4395: AND catv.trx_number =
4396: LTRIM(CE_AUTO_BANK_MATCH.csl_bank_trx_number, '0')
4397: AND catv.application_id = 200
4398: AND NVL(catv.reconciled_status_flag, 'N') = 'N';
4402: The amount is not rounded in the view. */
4403:
4404: IF ((CE_AUTO_BANK_MATCH.trx_currency_type = 'FOREIGN')
4405: AND (CE_AUTO_BANK_MATCH.trx_currency_code <>
4406: CE_AUTO_BANK_REC.G_functional_currency)) THEN
4407: fnd_currency.get_info(CE_AUTO_BANK_REC.G_functional_currency,
4408: funct_curr_precision,
4409: funct_curr_ext_precision,
4410: funct_curr_min_acct_unit);
4403:
4404: IF ((CE_AUTO_BANK_MATCH.trx_currency_type = 'FOREIGN')
4405: AND (CE_AUTO_BANK_MATCH.trx_currency_code <>
4406: CE_AUTO_BANK_REC.G_functional_currency)) THEN
4407: fnd_currency.get_info(CE_AUTO_BANK_REC.G_functional_currency,
4408: funct_curr_precision,
4409: funct_curr_ext_precision,
4410: funct_curr_min_acct_unit);
4411: CE_AUTO_BANK_MATCH.trx_amount :=
4418:
4419: END IF;
4420:
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;
4635: AND catv.trx_type in ('MISC_DEBIT', 'MISC_CREDIT')
4636: AND v.trx_type = 'MISC'
4637: AND v.bank_account_id = catv.bank_account_id
4638: --AND v.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
4639: AND v.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,v.org_id)
4640: AND v.trx_number = nvl(catv.trx_number,v.trx_number)
4641: AND to_char(v.trx_date,'YYYY/MM/DD') <=
4642: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
4643: AND v.status in ('REMITTED', 'CLEARED', 'RISK_ELIMINATED')
4735: AND catv.trx_type in ('DEBIT', 'CREDIT')
4736: AND v.trx_type = 'CASH'
4737: AND v.bank_account_id = catv.bank_account_id
4738: --AND v.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
4739: AND v.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, v.org_id)
4740: AND v.trx_number = nvl(catv.trx_number,v.trx_number)
4741: AND to_char(v.trx_date,'YYYY/MM/DD') <=
4742: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
4743: AND v.status in ('REMITTED', 'CLEARED', 'RISK_ELIMINATED')
4821: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
4822: AND catv.trx_id <> CE_AUTO_BANK_MATCH.csl_statement_line_id
4823: AND catv.trx_type in ('DEBIT', 'CREDIT')
4824: AND v2.bank_account_id = catv.bank_account_id
4825: AND v2.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, v2.org_id)
4826: --AND v2.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
4827: AND v2.trx_number = nvl(catv.trx_number,v2.trx_number)
4828: AND to_char(v2.trx_date,'YYYY/MM/DD') <=
4829: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
4876: catv.CE_BANK_ACCT_USE_ID
4877: FROM ce_200_reconciled_v catv
4878: WHERE catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
4879: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
4880: AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
4881: AND nvl(catv.trx_number,'-99999')
4882: = nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number,'-99999')
4883: AND (nvl(catv.invoice_text, '-99999')
4884: = nvl(CE_AUTO_BANK_MATCH.csl_invoice_text,'-99999')
4921: catv.CE_BANK_ACCT_USE_ID
4922: FROM ce_222_reconciled_v catv
4923: WHERE catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
4924: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
4925: AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,catv.org_id)
4926: AND nvl(catv.trx_number,'-99999')
4927: = nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number,'-99999')
4928: AND (nvl(catv.invoice_text, '-99999')
4929: = nvl(CE_AUTO_BANK_MATCH.csl_invoice_text,'-99999')
4983: AND sl.trx_type in ('MISC_DEBIT', 'MISC_CREDIT')
4984: AND catv.trx_type = 'MISC'
4985: AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
4986: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
4987: AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id, catv.org_id)
4988: AND nvl(catv.trx_number, '-99999')
4989: = nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number,'-99999')
4990: AND to_char(catv.trx_date,'YYYY/MM/DD') <=
4991: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
5081: AND sl.trx_type in ('DEBIT', 'CREDIT')
5082: AND catv.trx_type = 'CASH'
5083: AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
5084: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
5085: AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,catv.org_id)
5086: AND nvl(catv.trx_number, '-99999')
5087: = nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number, '-99999')
5088: AND to_char(catv.trx_date,'YYYY/MM/DD') <=
5089: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
5178: AND sl.statement_line_id = catv.statement_line_id
5179: AND sl.trx_type in ('DEBIT', 'CREDIT')
5180: AND catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
5181: --AND catv.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
5182: AND catv.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,catv.org_id)
5183: AND nvl(catv.trx_number,'-99999') =
5184: nvl(CE_AUTO_BANK_MATCH.csl_bank_trx_number,'-99999')
5185: AND to_char(catv.trx_date,'YYYY/MM/DD') <=
5186: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
5243: SELECT l.statement_line_id,
5244: l.rowid,
5245: l.trx_date,
5246: l.currency_code,
5247: decode(l.currency_code, CE_AUTO_BANK_REC.G_functional_currency,
5248: l.amount, CE_AUTO_BANK_MATCH.aba_bank_currency, l.amount,
5249: nvl(l.original_amount, l.amount)),
5250: l.original_amount,
5251: l.status,
5251: l.status,
5252: 0,
5253: l.trx_type,
5254: 1,
5255: decode(l.currency_code, CE_AUTO_BANK_REC.G_functional_currency,
5256: 'FUNCTIONAL', CE_AUTO_BANK_MATCH.aba_bank_currency, 'BANK',
5257: 'FOREIGN'),
5258: l.amount,
5259: l.trx_type,
5311: AND ar.org_id = r.org_id
5312: AND v.trx_type = 'CASH'
5313: AND v.bank_account_id = h.bank_account_id
5314: --AND v.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
5315: AND v.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,v.org_id)
5316: AND v.trx_number = nvl(l.bank_trx_number,v.trx_number)
5317: AND to_char(v.trx_date,'YYYY/MM/DD') <=
5318: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
5319: AND v.status in ('REMITTED', 'CLEARED', 'RISK_ELIMINATED')
5324: + decode(l.trx_type,
5325: 'CREDIT', l.amount,
5326: - l.amount)
5327: - decode(l.currency_code,
5328: CE_AUTO_BANK_REC.G_functional_currency,
5329: CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance,
5330: CE_AUTO_BANK_MATCH.aba_bank_currency,
5331: tolerance_amount,
5332: CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance))
5334: + decode(l.trx_type,
5335: 'CREDIT', l.amount,
5336: - l.amount)
5337: + decode(l.currency_code,
5338: CE_AUTO_BANK_REC.G_functional_currency,
5339: CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance,
5340: CE_AUTO_BANK_MATCH.aba_bank_currency,
5341: tolerance_amount,
5342: CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance))
5358: SELECT l.statement_line_id,
5359: l.rowid,
5360: l.trx_date,
5361: l.currency_code,
5362: decode(l.currency_code, CE_AUTO_BANK_REC.G_functional_currency,
5363: l.amount, CE_AUTO_BANK_MATCH.aba_bank_currency, l.amount,
5364: nvl(l.original_amount, l.amount)),
5365: l.original_amount,
5366: l.status,
5366: l.status,
5367: 0,
5368: l.trx_type,
5369: 1,
5370: decode(l.currency_code, CE_AUTO_BANK_REC.G_functional_currency,
5371: 'FUNCTIONAL', CE_AUTO_BANK_MATCH.aba_bank_currency, 'BANK',
5372: 'FOREIGN'),
5373: l.amount,
5374: l.trx_type,
5423: AND nvl(r.status_flag, 'U') <> 'U'
5424: AND glcc.conversion_type = l.exchange_rate_type
5425: AND v2.bank_account_id = h.bank_account_id
5426: --AND v2.ce_bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
5427: AND v2.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,v2.org_id)
5428: AND v2.trx_number = nvl(l.bank_trx_number,v2.trx_number)
5429: AND to_char(v2.trx_date,'YYYY/MM/DD') <=
5430: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
5431: AND CE_AUTO_BANK_MATCH.calc_csl_amount +
5433: AND v2.bank_account_amount
5434: between (- CE_AUTO_BANK_MATCH.calc_csl_amount
5435: + decode(l.trx_type, 'DEBIT', l.amount, - l.amount)
5436: - decode(l.currency_code,
5437: CE_AUTO_BANK_REC.G_functional_currency,
5438: CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance,
5439: CE_AUTO_BANK_MATCH.aba_bank_currency,
5440: tolerance_amount,
5441: CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance))
5441: CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance))
5442: and (- CE_AUTO_BANK_MATCH.calc_csl_amount
5443: + decode(l.trx_type, 'DEBIT', l.amount, - l.amount)
5444: + decode(l.currency_code,
5445: CE_AUTO_BANK_REC.G_functional_currency,
5446: CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance,
5447: CE_AUTO_BANK_MATCH.aba_bank_currency,
5448: tolerance_amount,
5449: CE_AUTO_BANK_MATCH.ba_ap_amount_tolerance))
5476: /* Calculate calc_tolerance_amount. */
5477:
5478: IF (NVL(CE_AUTO_BANK_MATCH.csl_currency_code,
5479: CE_AUTO_BANK_MATCH.aba_bank_currency)
5480: = CE_AUTO_BANK_REC.G_functional_currency) THEN
5481: CE_AUTO_BANK_MATCH.trx_currency_type := 'FUNCTIONAL';
5482: ELSIF (NVL(CE_AUTO_BANK_MATCH.csl_currency_code,
5483: CE_AUTO_BANK_MATCH.aba_bank_currency)
5484: = CE_AUTO_BANK_MATCH.aba_bank_currency) THEN
5545: END IF;
5546: */
5547: /*
5548: IF (CE_AUTO_BANK_MATCH.trx_currency_type = 'BANK') THEN
5549: IF (NVL(CE_AUTO_BANK_REC.G_amount_tolerance,0) <> 0) THEN
5550: calc_tolerance_amount :=
5551: convert_amount_tolerance(CE_AUTO_BANK_REC.G_amount_tolerance);
5552: ELSE
5553: calc_tolerance_amount := 0;
5547: /*
5548: IF (CE_AUTO_BANK_MATCH.trx_currency_type = 'BANK') THEN
5549: IF (NVL(CE_AUTO_BANK_REC.G_amount_tolerance,0) <> 0) THEN
5550: calc_tolerance_amount :=
5551: convert_amount_tolerance(CE_AUTO_BANK_REC.G_amount_tolerance);
5552: ELSE
5553: calc_tolerance_amount := 0;
5554: END IF;
5555: ELSIF (CE_AUTO_BANK_MATCH.trx_currency_type IN ('FUNCTIONAL','FOREIGN')) THEN
5552: ELSE
5553: calc_tolerance_amount := 0;
5554: END IF;
5555: ELSIF (CE_AUTO_BANK_MATCH.trx_currency_type IN ('FUNCTIONAL','FOREIGN')) THEN
5556: calc_tolerance_amount := CE_AUTO_BANK_REC.G_amount_tolerance;
5557: END IF;
5558: */
5559:
5560: --------------------------------------------------------------------------------
5780:
5781: ARP_CASHBOOK.unclear(
5782: p_cr_id => CE_AUTO_BANK_MATCH.trx_cash_receipt_id,
5783: p_trx_date => CE_AUTO_BANK_MATCH.trx_date,
5784: p_gl_date => CE_AUTO_BANK_REC.G_gl_date,
5785: p_actual_value_date => CE_AUTO_BANK_MATCH.csl_effective_date,
5786: p_module_name => 'CEABRMA',
5787: p_module_version => '1.0',
5788: p_crh_id => CE_AUTO_BANK_MATCH.trx_id);
6028: elsif (loc_match_type = 'RECEIPT') then
6029: ARP_CASHBOOK.unclear(
6030: p_cr_id => loc_cash_receipt_id,
6031: p_trx_date => loc_trx_date,
6032: p_gl_date => CE_AUTO_BANK_REC.G_gl_date,
6033: p_actual_value_date => CE_AUTO_BANK_MATCH.csl_effective_date,
6034: p_module_name => 'CEABRMA',
6035: p_module_version => '1.0',
6036: p_crh_id => loc_trx_id);
6119: and ba.bank_account_id = bau.bank_account_id
6120: and bau.AP_USE_ENABLE_FLAG ='Y'
6121: --and bau.bank_account_id = ac.external_bank_account_id
6122: --and bau.bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
6123: AND bau.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,bau.org_id)
6124: and bau.bank_acct_use_id = ac.CE_BANK_ACCT_USE_ID
6125: and ac.check_id = aip.check_id
6126: and ac.org_id = aip.org_id
6127: and aip.invoice_id = ap.invoice_id
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);
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);
6252:
6253: --
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: --
6255: --
6256:
6257: IF (NVL(CE_AUTO_BANK_MATCH.csl_currency_code,
6258: CE_AUTO_BANK_MATCH.aba_bank_currency)
6259: = CE_AUTO_BANK_REC.G_functional_currency) THEN
6260: CE_AUTO_BANK_MATCH.trx_currency_type := 'FUNCTIONAL';
6261: ELSIF (NVL(CE_AUTO_BANK_MATCH.csl_currency_code,
6262: CE_AUTO_BANK_MATCH.aba_bank_currency)
6263: = CE_AUTO_BANK_MATCH.aba_bank_currency) THEN
6279: END IF;
6280: END IF;
6281:
6282: IF ((csl_bank_trx_number is null) and not (csl_reconcile_flag = 'OI' and
6283: nvl(CE_AUTO_BANK_REC.G_open_interface_matching_code,'T') = 'D') and
6284: (csl_invoice_text is null and csl_bank_account_text is null) and
6285: (csl_invoice_text is null and csl_customer_text is null) and
6286: (CE_AUTO_BANK_MATCH.csl_trx_type not in ('MISC_CREDIT', 'MISC_DEBIT') or
6287: (CE_AUTO_BANK_MATCH.csl_trx_type in ('MISC_CREDIT', 'MISC_DEBIT') and
6289:
6290: raise NO_DATA_FOUND;
6291:
6292: ELSIF (trx_currency_type = 'FOREIGN' and CE_AUTO_BANK_MATCH.aba_bank_currency
6293: <> CE_AUTO_BANK_REC.G_functional_currency) OR
6294: (trx_currency_type = 'FUNCTIONAL' and
6295: CE_AUTO_BANK_MATCH.aba_bank_currency
6296: <> CE_AUTO_BANK_REC.G_functional_currency) THEN
6297:
6292: ELSIF (trx_currency_type = 'FOREIGN' and CE_AUTO_BANK_MATCH.aba_bank_currency
6293: <> CE_AUTO_BANK_REC.G_functional_currency) OR
6294: (trx_currency_type = 'FUNCTIONAL' and
6295: CE_AUTO_BANK_MATCH.aba_bank_currency
6296: <> CE_AUTO_BANK_REC.G_functional_currency) THEN
6297:
6298: CE_RECONCILIATION_ERRORS_PKG.insert_row(
6299: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6300: CE_AUTO_BANK_MATCH.csl_statement_line_id, 'CE_NO_FOREIGN_RECON');
6304: -- Open Interface system option needs to be enabled
6305: -- before we try to find the match
6306: --
6307: IF (CE_AUTO_BANK_MATCH.csl_reconcile_flag = 'OI' AND
6308: NVL(CE_AUTO_BANK_REC.G_open_interface_flag,'N') = 'N' ) THEN
6309: CE_RECONCILIATION_ERRORS_PKG.insert_row(
6310: CE_AUTO_BANK_MATCH.csh_statement_header_id,
6311: CE_AUTO_BANK_MATCH.csl_statement_line_id,
6312: 'CE_OPEN_INTERFACE_DISABLED');
6348: secondary_match := 'I';
6349: ELSE
6350: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('DEBIT', 'STOP')) THEN
6351: --FOR SEPA ER 6700007 START
6352: primary_match := NVL(CE_AUTO_BANK_REC.G_ap_matching_order,'T');
6353: --bug 7565001 : selective setting secondary_match
6354: IF (primary_match = 'T') THEN
6355: secondary_match := NVL(CE_AUTO_BANK_REC.G_ap_matching_order2,'B');
6356: ELSE
6351: --FOR SEPA ER 6700007 START
6352: primary_match := NVL(CE_AUTO_BANK_REC.G_ap_matching_order,'T');
6353: --bug 7565001 : selective setting secondary_match
6354: IF (primary_match = 'T') THEN
6355: secondary_match := NVL(CE_AUTO_BANK_REC.G_ap_matching_order2,'B');
6356: ELSE
6357: secondary_match := NVL(CE_AUTO_BANK_REC.G_ap_matching_order2,'T');
6358: END IF;
6359:
6353: --bug 7565001 : selective setting secondary_match
6354: IF (primary_match = 'T') THEN
6355: secondary_match := NVL(CE_AUTO_BANK_REC.G_ap_matching_order2,'B');
6356: ELSE
6357: secondary_match := NVL(CE_AUTO_BANK_REC.G_ap_matching_order2,'T');
6358: END IF;
6359:
6360:
6361: IF (primary_match = 'T') and (secondary_match = 'B') 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';
6379: ELSE
6380: secondary_match := 'T';
6418: -- bug 1796965
6419: SELECT count(*)
6420: INTO accounting_method_found
6421: FROM ar_system_parameters s
6422: where s.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,CE_AUTO_BANK_MATCH.trx_org_id);
6423:
6424: if (accounting_method_found = 1) then
6425: SELECT accounting_method
6426: INTO CE_AUTO_BANK_MATCH.ar_accounting_method
6424: if (accounting_method_found = 1) then
6425: SELECT accounting_method
6426: INTO CE_AUTO_BANK_MATCH.ar_accounting_method
6427: FROM ar_system_parameters s
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);
6457: end;
6458:
6459: IF (misc_exists = 'N') THEN
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';
6459: IF (misc_exists = 'N') THEN
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.');
6490: -- Verified that exchange information is not null
6491: -- when creating foreign currency misc receipts
6492: --
6493: IF (CE_AUTO_BANK_MATCH.aba_bank_currency <>
6494: CE_AUTO_BANK_REC.G_functional_currency
6495: AND CE_AUTO_BANK_MATCH.csl_create_misc_trx_flag = 'Y'
6496: AND (CE_AUTO_BANK_MATCH.csl_exchange_rate_date IS NULL
6497: OR CE_AUTO_BANK_MATCH.csl_exchange_rate_type IS NULL
6498: OR CE_AUTO_BANK_MATCH.csl_exchange_rate IS NULL) ) THEN
6513: base_receipt_amount := receipt_amount;
6514: ELSIF (CE_AUTO_BANK_MATCH.csl_exchange_rate_type <> 'User') THEN
6515: BEGIN
6516: receipt_amount := gl_currency_api.convert_amount(
6517: CE_AUTO_BANK_REC.G_functional_currency,
6518: CE_AUTO_BANK_MATCH.csl_currency_code,
6519: nvl(CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
6520: CE_AUTO_BANK_MATCH.csl_trx_date),
6521: CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
6579: X_trx_number =>
6580: CE_AUTO_BANK_MATCH.csl_bank_trx_number || '/NSF',
6581: X_doc_sequence_value => NULL,
6582: X_doc_sequence_id => to_number(NULL),
6583: X_gl_date => CE_AUTO_BANK_REC.G_gl_date,
6584: X_value_date => CE_AUTO_BANK_MATCH.csl_effective_date,
6585: X_trx_date => CE_AUTO_BANK_MATCH.csl_trx_date,
6586: X_deposit_date => CE_AUTO_BANK_MATCH.csl_trx_date,
6587: X_amount => receipt_amount,
6585: X_trx_date => CE_AUTO_BANK_MATCH.csl_trx_date,
6586: X_deposit_date => CE_AUTO_BANK_MATCH.csl_trx_date,
6587: X_amount => receipt_amount,
6588: X_bank_account_amount => base_receipt_amount,
6589: X_set_of_books_id => CE_AUTO_BANK_REC.G_set_of_books_id,
6590: X_misc_currency_code =>
6591: NVL(CE_AUTO_BANK_MATCH.csl_currency_code,
6592: CE_AUTO_BANK_MATCH.aba_bank_currency),
6593: X_exchange_rate_date =>
6597: X_exchange_rate =>
6598: CE_AUTO_BANK_MATCH.csl_exchange_rate,
6599: X_receipt_method_id =>
6600: nvl(CE_AUTO_BANK_MATCH.csl_receipt_method_id,
6601: CE_AUTO_BANK_REC.G_payment_method_id),
6602: X_bank_account_id =>
6603: CE_AUTO_BANK_MATCH.csh_bank_account_id,
6604: X_activity_type_id =>
6605: nvl(CE_AUTO_BANK_MATCH.csl_receivables_trx_id,
6602: X_bank_account_id =>
6603: CE_AUTO_BANK_MATCH.csh_bank_account_id,
6604: X_activity_type_id =>
6605: nvl(CE_AUTO_BANK_MATCH.csl_receivables_trx_id,
6606: CE_AUTO_BANK_REC.G_receivables_trx_id),
6607: X_comments => 'Created by Auto Bank Rec',
6608: X_reference_type => NULL,
6609: X_reference_id => to_number(NULL),
6610: X_clear_currency_code => NULL,
6611: X_statement_line_id => X_statement_line_id,
6612: X_tax_id => l_vat_tax_id,
6613: X_tax_rate => l_tax_rate,
6614: X_paid_from => NULL,
6615: X_module_name => 'CE_AUTO_BANK_REC',
6616: X_cr_vat_tax_id => CE_AUTO_BANK_REC.G_cr_vat_tax_code,
6617: X_dr_vat_tax_id => CE_AUTO_BANK_REC.G_dr_vat_tax_code,
6618: trx_currency_type =>
6619: CE_AUTO_BANK_MATCH.trx_currency_type,
6612: X_tax_id => l_vat_tax_id,
6613: X_tax_rate => l_tax_rate,
6614: X_paid_from => NULL,
6615: X_module_name => 'CE_AUTO_BANK_REC',
6616: X_cr_vat_tax_id => CE_AUTO_BANK_REC.G_cr_vat_tax_code,
6617: X_dr_vat_tax_id => CE_AUTO_BANK_REC.G_dr_vat_tax_code,
6618: trx_currency_type =>
6619: CE_AUTO_BANK_MATCH.trx_currency_type,
6620: X_cr_id => p_cr_id,
6613: X_tax_rate => l_tax_rate,
6614: X_paid_from => NULL,
6615: X_module_name => 'CE_AUTO_BANK_REC',
6616: X_cr_vat_tax_id => CE_AUTO_BANK_REC.G_cr_vat_tax_code,
6617: X_dr_vat_tax_id => CE_AUTO_BANK_REC.G_dr_vat_tax_code,
6618: trx_currency_type =>
6619: CE_AUTO_BANK_MATCH.trx_currency_type,
6620: X_cr_id => p_cr_id,
6621: X_effective_date =>
6620: X_cr_id => p_cr_id,
6621: X_effective_date =>
6622: CE_AUTO_BANK_MATCH.csl_effective_date,
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);
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
6905: WHERE rowid = CE_AUTO_BANK_MATCH.csh_rowid;
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
6905: WHERE rowid = CE_AUTO_BANK_MATCH.csh_rowid;
6906: END IF;
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
6905: WHERE rowid = CE_AUTO_BANK_MATCH.csh_rowid;
6906: END IF;
6907: cep_standard.debug('<
7004:
7005: CE_AUTO_BANK_MATCH.tolerance_amount := 0;
7006: IF (CE_AUTO_BANK_MATCH.csl_trx_type IN ('REJECTED', 'STOP')
7007: OR (CE_AUTO_BANK_MATCH.csl_clearing_trx_type in ('ROI_LINE', 'XTR_LINE')
7008: AND CE_AUTO_BANK_REC.G_open_interface_matching_code = 'D')) THEN
7009: CE_AUTO_BANK_MATCH.tolerance_amount := 0;
7010: ELSE
7011: --
7012: -- Amount tolerance
7166:
7167: BEGIN
7168: --bug 4452153 exchanged the currency code parameters
7169: acctd_amount := gl_currency_api.convert_amount(
7170: CE_AUTO_BANK_REC.G_functional_currency,
7171: CE_AUTO_BANK_MATCH.csl_currency_code,
7172: nvl(CE_AUTO_BANK_MATCH.csl_exchange_rate_date,
7173: CE_AUTO_BANK_MATCH.csl_trx_date),
7174: CE_AUTO_BANK_MATCH.csl_exchange_rate_type,
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,
7232: ar_receipt_methods arm
7233: WHERE arm.receipt_method_id = arma.receipt_method_id
7234: --AND arma.REMIT_BANK_ACCT_USE_ID = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
7234: --AND arma.REMIT_BANK_ACCT_USE_ID = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
7235: AND arma.REMIT_BANK_ACCT_USE_ID = nvl(CE_AUTO_BANK_MATCH.trx_bank_acct_use_id, arma.REMIT_BANK_ACCT_USE_ID)
7236: --AND arma.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
7237: --AND arma.org_id = CE_AUTO_BANK_MATCH.bau_org_id
7238: AND arma.org_id = nvl(CE_AUTO_BANK_MATCH.trx_org_id, nvl(CE_AUTO_BANK_REC.G_org_id, arma.org_id))
7239: --AND arm.receipt_method_id = nvl(CE_AUTO_BANK_REC.G_payment_method_id,CE_AUTO_BANK_MATCH.csl_receipt_method_id)
7240: AND arm.receipt_method_id = nvl(CE_AUTO_BANK_MATCH.csl_receipt_method_id, CE_AUTO_BANK_REC.G_payment_method_id)
7241: AND CE_AUTO_BANK_MATCH.csl_trx_date between nvl(arm.start_date,CE_AUTO_BANK_MATCH.csl_trx_date)
7242: AND nvl(arm.end_date,CE_AUTO_BANK_MATCH.csl_trx_date)
7235: AND arma.REMIT_BANK_ACCT_USE_ID = nvl(CE_AUTO_BANK_MATCH.trx_bank_acct_use_id, arma.REMIT_BANK_ACCT_USE_ID)
7236: --AND arma.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
7237: --AND arma.org_id = CE_AUTO_BANK_MATCH.bau_org_id
7238: AND arma.org_id = nvl(CE_AUTO_BANK_MATCH.trx_org_id, nvl(CE_AUTO_BANK_REC.G_org_id, arma.org_id))
7239: --AND arm.receipt_method_id = nvl(CE_AUTO_BANK_REC.G_payment_method_id,CE_AUTO_BANK_MATCH.csl_receipt_method_id)
7240: AND arm.receipt_method_id = nvl(CE_AUTO_BANK_MATCH.csl_receipt_method_id, CE_AUTO_BANK_REC.G_payment_method_id)
7241: AND CE_AUTO_BANK_MATCH.csl_trx_date between nvl(arm.start_date,CE_AUTO_BANK_MATCH.csl_trx_date)
7242: AND nvl(arm.end_date,CE_AUTO_BANK_MATCH.csl_trx_date)
7243: and exists (select 1 from ce_bank_acct_uses_gt_v bau
7236: --AND arma.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
7237: --AND arma.org_id = CE_AUTO_BANK_MATCH.bau_org_id
7238: AND arma.org_id = nvl(CE_AUTO_BANK_MATCH.trx_org_id, nvl(CE_AUTO_BANK_REC.G_org_id, arma.org_id))
7239: --AND arm.receipt_method_id = nvl(CE_AUTO_BANK_REC.G_payment_method_id,CE_AUTO_BANK_MATCH.csl_receipt_method_id)
7240: AND arm.receipt_method_id = nvl(CE_AUTO_BANK_MATCH.csl_receipt_method_id, CE_AUTO_BANK_REC.G_payment_method_id)
7241: AND CE_AUTO_BANK_MATCH.csl_trx_date between nvl(arm.start_date,CE_AUTO_BANK_MATCH.csl_trx_date)
7242: AND nvl(arm.end_date,CE_AUTO_BANK_MATCH.csl_trx_date)
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
7292: SELECT ar.liability_tax_code, ar.asset_tax_code
7293: INTO y_dr_vat_tax_code, y_cr_vat_tax_code
7294: FROM ar_receivables_trx ar
7295: WHERE ar.receivables_trx_id = CE_AUTO_BANK_MATCH.csl_receivables_trx_id
7296: AND ar.org_id = nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id) ; --CE_AUTO_BANK_MATCH.bau_org_id;
7297: else
7298: y_dr_vat_tax_code := CE_AUTO_BANK_REC.G_dr_vat_tax_code;
7299: y_cr_vat_tax_code := CE_AUTO_BANK_REC.G_cr_vat_tax_code;
7300: end if;
7294: FROM ar_receivables_trx ar
7295: WHERE ar.receivables_trx_id = CE_AUTO_BANK_MATCH.csl_receivables_trx_id
7296: AND ar.org_id = nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id) ; --CE_AUTO_BANK_MATCH.bau_org_id;
7297: else
7298: y_dr_vat_tax_code := CE_AUTO_BANK_REC.G_dr_vat_tax_code;
7299: y_cr_vat_tax_code := CE_AUTO_BANK_REC.G_cr_vat_tax_code;
7300: end if;
7301:
7302:
7295: WHERE ar.receivables_trx_id = CE_AUTO_BANK_MATCH.csl_receivables_trx_id
7296: AND ar.org_id = nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id) ; --CE_AUTO_BANK_MATCH.bau_org_id;
7297: else
7298: y_dr_vat_tax_code := CE_AUTO_BANK_REC.G_dr_vat_tax_code;
7299: y_cr_vat_tax_code := CE_AUTO_BANK_REC.G_cr_vat_tax_code;
7300: end if;
7301:
7302:
7303: -- bug 5733971
7305:
7306: select LEGAL_ENTITY_ID
7307: into l_le_id
7308: from XLE_FP_OU_LEDGER_V
7309: where OPERATING_UNIT_ID =nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id) ;
7310: exception
7311: WHEN OTHERS THEN
7312: l_le_id := nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id) ;
7313: end;
7308: from XLE_FP_OU_LEDGER_V
7309: where OPERATING_UNIT_ID =nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id) ;
7310: exception
7311: WHEN OTHERS THEN
7312: l_le_id := nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id) ;
7313: end;
7314:
7315: zx_api_pub.set_tax_security_context(
7316: p_api_version => 1.0,
7319: p_validation_level => NULL,
7320: x_return_status => l_return_status,
7321: x_msg_count => l_msg_count,
7322: x_msg_data => l_msg_data,
7323: p_internal_org_id => nvl(CE_AUTO_BANK_MATCH.trx_org_id, CE_AUTO_BANK_REC.G_org_id), --:org_id,
7324: p_legal_entity_id => l_le_id, --:org_id,
7325: p_transaction_date => sysdate,
7326: p_related_doc_date => NULL,
7327: p_adjusted_doc_date=> NULL,
7333: SELECT ar.vat_tax_id, ar.tax_rate
7334: INTO l_vat_tax_id, X_tax_rate
7335: FROM ce_misc_tax_code_v ar --ar_vat_tax ar
7336: WHERE ar.tax_code = y_dr_vat_tax_code
7337: AND ar.org_id = nvl(CE_AUTO_BANK_MATCH.trx_org_id, CE_AUTO_BANK_REC.G_org_id)
7338: AND to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
7339: between to_char(ar.start_date,'YYYY/MM/DD')
7340: and NVL(to_char(ar.end_date,'YYYY/MM/DD'),
7341: to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD'));
7343: SELECT ar.vat_tax_id, ar.tax_rate
7344: INTO l_vat_tax_id, X_tax_rate
7345: FROM ce_misc_tax_code_v ar --ar_vat_tax ar
7346: WHERE tax_code = y_cr_vat_tax_code
7347: AND ar.org_id = nvl(CE_AUTO_BANK_MATCH.trx_org_id,CE_AUTO_BANK_REC.G_org_id)
7348: AND to_char(CE_AUTO_BANK_MATCH.csl_trx_date,'YYYY/MM/DD')
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'));
7607: --FROM ce_260_cf_transactions_v catv
7608: FROM ce_available_transactions_tmp catv
7609: WHERE catv.bank_account_id = CE_AUTO_BANK_MATCH.csh_bank_account_id
7610: AND catv.TRX_ID = CE_AUTO_BANK_MATCH.csl_cashflow_id
7611: AND catv.legal_entity_id = nvl(CE_AUTO_BANK_REC.G_legal_entity_id,catv.legal_entity_id)
7612: AND catv.application_id = 261
7613: AND NVL(catv.reconciled_status_flag, 'N') = 'N';
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
7678: /* amount_cleared => CE_AUTO_BANK_MATCH.calc_csl_amount,*/
7679: amount_cleared => CE_AUTO_BANK_MATCH.trx_amount, --CE_AUTO_BANK_MATCH.csl_amount,
7680: charges_amount => CE_AUTO_BANK_MATCH.trx_charges_amount,
7681: errors_amount => CE_AUTO_BANK_MATCH.trx_errors_amount,
7682: gl_date => CE_AUTO_BANK_REC.G_gl_date_original,
7683: value_date => CE_AUTO_BANK_MATCH.csl_effective_date,
7684: cleared_date => CE_AUTO_BANK_MATCH.csl_trx_date,
7685: ar_cash_receipt_id => NULL, --l_je_header_id,
7686: X_bank_currency => CE_AUTO_BANK_MATCH.aba_bank_currency,
7691: X_currency_code => NVL(CE_AUTO_BANK_MATCH.csl_currency_code,CE_AUTO_BANK_MATCH.aba_bank_currency),
7692: auto_reconciled_flag => 'Y',
7693: X_statement_header_id => d_statement_header_id,
7694: X_effective_date => CE_AUTO_BANK_MATCH.csl_effective_date,
7695: X_float_handling_flag => CE_AUTO_BANK_REC.G_float_handling_flag,
7696: X_reversed_receipt_flag => CE_AUTO_BANK_MATCH.reversed_receipt_flag);
7697:
7698: CE_AUTO_BANK_CLEAR.update_line_status(CE_AUTO_BANK_MATCH.csl_statement_line_id,'RECONCILED');
7699:
9784: current_org_id NUMBER;
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 */
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:
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:
9794: cep_standard.debug('========== START MATCHING ========== ');
9803: -- bug 1796965
9804: SELECT count(*)
9805: INTO accounting_method_found
9806: FROM ar_system_parameters s
9807: where s.org_id = CE_AUTO_BANK_REC.G_org_id;
9808:
9809: if (accounting_method_found = 1) then
9810: SELECT accounting_method
9811: INTO CE_AUTO_BANK_MATCH.ar_accounting_method
9809: if (accounting_method_found = 1) then
9810: SELECT accounting_method
9811: INTO CE_AUTO_BANK_MATCH.ar_accounting_method
9812: FROM ar_system_parameters s
9813: where s.org_id = CE_AUTO_BANK_REC.G_org_id;
9814: else
9815: CE_AUTO_BANK_MATCH.ar_accounting_method := NULL;
9816: end if;
9817: */
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);
9823: j := 0;
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);
9823: j := 0;
9824: LOOP
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);
9823: j := 0;
9824: LOOP
9825: cep_standard.debug('>>CE_AUTO_BANK_MATCH.Fetching r_branch_cursor');
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);
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,
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
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;
9872: -- bug 1796965
9873: SELECT count(*)
9874: INTO accounting_method_found
9875: FROM ar_system_parameters s
9876: where s.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,CE_AUTO_BANK_MATCH.bau_org_id);
9877:
9878: if (accounting_method_found = 1) then
9879: SELECT accounting_method
9880: INTO CE_AUTO_BANK_MATCH.ar_accounting_method
9878: if (accounting_method_found = 1) then
9879: SELECT accounting_method
9880: INTO CE_AUTO_BANK_MATCH.ar_accounting_method
9881: FROM ar_system_parameters s
9882: where s.org_id = nvl(CE_AUTO_BANK_REC.G_org_id,CE_AUTO_BANK_MATCH.bau_org_id);
9883: else
9884: CE_AUTO_BANK_MATCH.ar_accounting_method := NULL;
9885: end if;
9886: */
9885: end if;
9886: */
9887: -- bug 3676745 1/20/05 Did not enter organization (le_id and org_id missing)
9888: -- use match org_id and legal_entity_id
9889: -- if CE_AUTO_BANK_REC.G_legal_entity_id is not null then it has already been set by ceabrdrb.pls
9890: --IF (CE_AUTO_BANK_REC.G_legal_entity_id is null and CE_AUTO_BANK_REC.G_org_id is null) THEN
9891: --bug 4914608 get owner LE info from ce_system_parameters
9892: IF (CE_AUTO_BANK_REC.G_legal_entity_id is null) THEN
9893: --IF (CE_AUTO_BANK_MATCH.bau_org_id is not null or CE_AUTO_BANK_MATCH.bau_legal_entity_id is not null) THEN
9886: */
9887: -- bug 3676745 1/20/05 Did not enter organization (le_id and org_id missing)
9888: -- use match org_id and legal_entity_id
9889: -- if CE_AUTO_BANK_REC.G_legal_entity_id is not null then it has already been set by ceabrdrb.pls
9890: --IF (CE_AUTO_BANK_REC.G_legal_entity_id is null and CE_AUTO_BANK_REC.G_org_id is null) THEN
9891: --bug 4914608 get owner LE info from ce_system_parameters
9892: IF (CE_AUTO_BANK_REC.G_legal_entity_id is null) THEN
9893: --IF (CE_AUTO_BANK_MATCH.bau_org_id is not null or CE_AUTO_BANK_MATCH.bau_legal_entity_id is not null) THEN
9894: IF (CE_AUTO_BANK_MATCH.ba_owner_le_id is not null) THEN
9888: -- use match org_id and legal_entity_id
9889: -- if CE_AUTO_BANK_REC.G_legal_entity_id is not null then it has already been set by ceabrdrb.pls
9890: --IF (CE_AUTO_BANK_REC.G_legal_entity_id is null and CE_AUTO_BANK_REC.G_org_id is null) THEN
9891: --bug 4914608 get owner LE info from ce_system_parameters
9892: IF (CE_AUTO_BANK_REC.G_legal_entity_id is null) THEN
9893: --IF (CE_AUTO_BANK_MATCH.bau_org_id is not null or CE_AUTO_BANK_MATCH.bau_legal_entity_id is not null) THEN
9894: IF (CE_AUTO_BANK_MATCH.ba_owner_le_id is not null) THEN
9895: CE_SYSTEM_PARAMETERS1_PKG.select_columns(CE_AUTO_BANK_REC.G_rowid,
9896: CE_AUTO_BANK_REC.G_set_of_books_id,
9891: --bug 4914608 get owner LE info from ce_system_parameters
9892: IF (CE_AUTO_BANK_REC.G_legal_entity_id is null) THEN
9893: --IF (CE_AUTO_BANK_MATCH.bau_org_id is not null or CE_AUTO_BANK_MATCH.bau_legal_entity_id is not null) THEN
9894: IF (CE_AUTO_BANK_MATCH.ba_owner_le_id is not null) THEN
9895: CE_SYSTEM_PARAMETERS1_PKG.select_columns(CE_AUTO_BANK_REC.G_rowid,
9896: CE_AUTO_BANK_REC.G_set_of_books_id,
9897: CE_AUTO_BANK_REC.G_cashbook_begin_date,
9898: CE_AUTO_BANK_REC.G_show_cleared_flag,
9899: CE_AUTO_BANK_REC.G_show_void_payment_flag,
9892: IF (CE_AUTO_BANK_REC.G_legal_entity_id is null) THEN
9893: --IF (CE_AUTO_BANK_MATCH.bau_org_id is not null or CE_AUTO_BANK_MATCH.bau_legal_entity_id is not null) THEN
9894: IF (CE_AUTO_BANK_MATCH.ba_owner_le_id is not null) THEN
9895: CE_SYSTEM_PARAMETERS1_PKG.select_columns(CE_AUTO_BANK_REC.G_rowid,
9896: CE_AUTO_BANK_REC.G_set_of_books_id,
9897: CE_AUTO_BANK_REC.G_cashbook_begin_date,
9898: CE_AUTO_BANK_REC.G_show_cleared_flag,
9899: CE_AUTO_BANK_REC.G_show_void_payment_flag,
9900: CE_AUTO_BANK_REC.G_line_autocreation_flag,
9893: --IF (CE_AUTO_BANK_MATCH.bau_org_id is not null or CE_AUTO_BANK_MATCH.bau_legal_entity_id is not null) THEN
9894: IF (CE_AUTO_BANK_MATCH.ba_owner_le_id is not null) THEN
9895: CE_SYSTEM_PARAMETERS1_PKG.select_columns(CE_AUTO_BANK_REC.G_rowid,
9896: CE_AUTO_BANK_REC.G_set_of_books_id,
9897: CE_AUTO_BANK_REC.G_cashbook_begin_date,
9898: CE_AUTO_BANK_REC.G_show_cleared_flag,
9899: CE_AUTO_BANK_REC.G_show_void_payment_flag,
9900: CE_AUTO_BANK_REC.G_line_autocreation_flag,
9901: CE_AUTO_BANK_REC.G_interface_purge_flag,
9894: IF (CE_AUTO_BANK_MATCH.ba_owner_le_id is not null) THEN
9895: CE_SYSTEM_PARAMETERS1_PKG.select_columns(CE_AUTO_BANK_REC.G_rowid,
9896: CE_AUTO_BANK_REC.G_set_of_books_id,
9897: CE_AUTO_BANK_REC.G_cashbook_begin_date,
9898: CE_AUTO_BANK_REC.G_show_cleared_flag,
9899: CE_AUTO_BANK_REC.G_show_void_payment_flag,
9900: CE_AUTO_BANK_REC.G_line_autocreation_flag,
9901: CE_AUTO_BANK_REC.G_interface_purge_flag,
9902: CE_AUTO_BANK_REC.G_interface_archive_flag,
9895: CE_SYSTEM_PARAMETERS1_PKG.select_columns(CE_AUTO_BANK_REC.G_rowid,
9896: CE_AUTO_BANK_REC.G_set_of_books_id,
9897: CE_AUTO_BANK_REC.G_cashbook_begin_date,
9898: CE_AUTO_BANK_REC.G_show_cleared_flag,
9899: CE_AUTO_BANK_REC.G_show_void_payment_flag,
9900: CE_AUTO_BANK_REC.G_line_autocreation_flag,
9901: CE_AUTO_BANK_REC.G_interface_purge_flag,
9902: CE_AUTO_BANK_REC.G_interface_archive_flag,
9903: CE_AUTO_BANK_REC.G_lines_per_commit,
9896: CE_AUTO_BANK_REC.G_set_of_books_id,
9897: CE_AUTO_BANK_REC.G_cashbook_begin_date,
9898: CE_AUTO_BANK_REC.G_show_cleared_flag,
9899: CE_AUTO_BANK_REC.G_show_void_payment_flag,
9900: CE_AUTO_BANK_REC.G_line_autocreation_flag,
9901: CE_AUTO_BANK_REC.G_interface_purge_flag,
9902: CE_AUTO_BANK_REC.G_interface_archive_flag,
9903: CE_AUTO_BANK_REC.G_lines_per_commit,
9904: CE_AUTO_BANK_REC.G_functional_currency,
9897: CE_AUTO_BANK_REC.G_cashbook_begin_date,
9898: CE_AUTO_BANK_REC.G_show_cleared_flag,
9899: CE_AUTO_BANK_REC.G_show_void_payment_flag,
9900: CE_AUTO_BANK_REC.G_line_autocreation_flag,
9901: CE_AUTO_BANK_REC.G_interface_purge_flag,
9902: CE_AUTO_BANK_REC.G_interface_archive_flag,
9903: CE_AUTO_BANK_REC.G_lines_per_commit,
9904: CE_AUTO_BANK_REC.G_functional_currency,
9905: CE_AUTO_BANK_REC.G_sob_short_name,
9898: CE_AUTO_BANK_REC.G_show_cleared_flag,
9899: CE_AUTO_BANK_REC.G_show_void_payment_flag,
9900: CE_AUTO_BANK_REC.G_line_autocreation_flag,
9901: CE_AUTO_BANK_REC.G_interface_purge_flag,
9902: CE_AUTO_BANK_REC.G_interface_archive_flag,
9903: CE_AUTO_BANK_REC.G_lines_per_commit,
9904: CE_AUTO_BANK_REC.G_functional_currency,
9905: CE_AUTO_BANK_REC.G_sob_short_name,
9906: CE_AUTO_BANK_REC.G_account_period_type,
9899: CE_AUTO_BANK_REC.G_show_void_payment_flag,
9900: CE_AUTO_BANK_REC.G_line_autocreation_flag,
9901: CE_AUTO_BANK_REC.G_interface_purge_flag,
9902: CE_AUTO_BANK_REC.G_interface_archive_flag,
9903: CE_AUTO_BANK_REC.G_lines_per_commit,
9904: CE_AUTO_BANK_REC.G_functional_currency,
9905: CE_AUTO_BANK_REC.G_sob_short_name,
9906: CE_AUTO_BANK_REC.G_account_period_type,
9907: CE_AUTO_BANK_REC.G_user_exchange_rate_type,
9900: CE_AUTO_BANK_REC.G_line_autocreation_flag,
9901: CE_AUTO_BANK_REC.G_interface_purge_flag,
9902: CE_AUTO_BANK_REC.G_interface_archive_flag,
9903: CE_AUTO_BANK_REC.G_lines_per_commit,
9904: CE_AUTO_BANK_REC.G_functional_currency,
9905: CE_AUTO_BANK_REC.G_sob_short_name,
9906: CE_AUTO_BANK_REC.G_account_period_type,
9907: CE_AUTO_BANK_REC.G_user_exchange_rate_type,
9908: CE_AUTO_BANK_REC.G_chart_of_accounts_id,
9901: CE_AUTO_BANK_REC.G_interface_purge_flag,
9902: CE_AUTO_BANK_REC.G_interface_archive_flag,
9903: CE_AUTO_BANK_REC.G_lines_per_commit,
9904: CE_AUTO_BANK_REC.G_functional_currency,
9905: CE_AUTO_BANK_REC.G_sob_short_name,
9906: CE_AUTO_BANK_REC.G_account_period_type,
9907: CE_AUTO_BANK_REC.G_user_exchange_rate_type,
9908: CE_AUTO_BANK_REC.G_chart_of_accounts_id,
9909: CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
9902: CE_AUTO_BANK_REC.G_interface_archive_flag,
9903: CE_AUTO_BANK_REC.G_lines_per_commit,
9904: CE_AUTO_BANK_REC.G_functional_currency,
9905: CE_AUTO_BANK_REC.G_sob_short_name,
9906: CE_AUTO_BANK_REC.G_account_period_type,
9907: CE_AUTO_BANK_REC.G_user_exchange_rate_type,
9908: CE_AUTO_BANK_REC.G_chart_of_accounts_id,
9909: CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
9910: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
9903: CE_AUTO_BANK_REC.G_lines_per_commit,
9904: CE_AUTO_BANK_REC.G_functional_currency,
9905: CE_AUTO_BANK_REC.G_sob_short_name,
9906: CE_AUTO_BANK_REC.G_account_period_type,
9907: CE_AUTO_BANK_REC.G_user_exchange_rate_type,
9908: CE_AUTO_BANK_REC.G_chart_of_accounts_id,
9909: CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
9910: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
9911: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
9904: CE_AUTO_BANK_REC.G_functional_currency,
9905: CE_AUTO_BANK_REC.G_sob_short_name,
9906: CE_AUTO_BANK_REC.G_account_period_type,
9907: CE_AUTO_BANK_REC.G_user_exchange_rate_type,
9908: CE_AUTO_BANK_REC.G_chart_of_accounts_id,
9909: CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
9910: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
9911: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
9912: CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE,
9905: CE_AUTO_BANK_REC.G_sob_short_name,
9906: CE_AUTO_BANK_REC.G_account_period_type,
9907: CE_AUTO_BANK_REC.G_user_exchange_rate_type,
9908: CE_AUTO_BANK_REC.G_chart_of_accounts_id,
9909: CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
9910: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
9911: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
9912: CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE,
9913: CE_AUTO_BANK_MATCH.ba_owner_le_id
9906: CE_AUTO_BANK_REC.G_account_period_type,
9907: CE_AUTO_BANK_REC.G_user_exchange_rate_type,
9908: CE_AUTO_BANK_REC.G_chart_of_accounts_id,
9909: CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
9910: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
9911: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
9912: CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE,
9913: CE_AUTO_BANK_MATCH.ba_owner_le_id
9914: );
9907: CE_AUTO_BANK_REC.G_user_exchange_rate_type,
9908: CE_AUTO_BANK_REC.G_chart_of_accounts_id,
9909: CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
9910: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
9911: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
9912: CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE,
9913: CE_AUTO_BANK_MATCH.ba_owner_le_id
9914: );
9915: END IF;
9908: CE_AUTO_BANK_REC.G_chart_of_accounts_id,
9909: CE_AUTO_BANK_REC.G_CASHFLOW_EXCHANGE_RATE_TYPE,
9910: CE_AUTO_BANK_REC.G_AUTHORIZATION_BAT,
9911: CE_AUTO_BANK_REC.G_BSC_EXCHANGE_DATE_TYPE,
9912: CE_AUTO_BANK_REC.G_BAT_EXCHANGE_DATE_TYPE,
9913: CE_AUTO_BANK_MATCH.ba_owner_le_id
9914: );
9915: END IF;
9916: END IF;
9924: CE_AUTO_BANK_MATCH.ba_ar_amount_tolerance,
9925: CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance,
9926: CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance,
9927: CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance,
9928: CE_AUTO_BANK_REC.G_float_handling_flag,
9929: CE_AUTO_BANK_REC.G_ap_matching_order,
9930: CE_AUTO_BANK_REC.G_ar_matching_order,
9931: CE_AUTO_BANK_REC.G_exchange_rate_type,
9932: CE_AUTO_BANK_REC.G_exchange_rate_date,
9925: CE_AUTO_BANK_MATCH.ba_ar_percent_tolerance,
9926: CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance,
9927: CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance,
9928: CE_AUTO_BANK_REC.G_float_handling_flag,
9929: CE_AUTO_BANK_REC.G_ap_matching_order,
9930: CE_AUTO_BANK_REC.G_ar_matching_order,
9931: CE_AUTO_BANK_REC.G_exchange_rate_type,
9932: CE_AUTO_BANK_REC.G_exchange_rate_date,
9933: CE_AUTO_BANK_REC.G_open_interface_flag,
9926: CE_AUTO_BANK_MATCH.ba_ce_amount_tolerance,
9927: CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance,
9928: CE_AUTO_BANK_REC.G_float_handling_flag,
9929: CE_AUTO_BANK_REC.G_ap_matching_order,
9930: CE_AUTO_BANK_REC.G_ar_matching_order,
9931: CE_AUTO_BANK_REC.G_exchange_rate_type,
9932: CE_AUTO_BANK_REC.G_exchange_rate_date,
9933: CE_AUTO_BANK_REC.G_open_interface_flag,
9934: CE_AUTO_BANK_REC.G_open_interface_float_status,
9927: CE_AUTO_BANK_MATCH.ba_ce_percent_tolerance,
9928: CE_AUTO_BANK_REC.G_float_handling_flag,
9929: CE_AUTO_BANK_REC.G_ap_matching_order,
9930: CE_AUTO_BANK_REC.G_ar_matching_order,
9931: CE_AUTO_BANK_REC.G_exchange_rate_type,
9932: CE_AUTO_BANK_REC.G_exchange_rate_date,
9933: CE_AUTO_BANK_REC.G_open_interface_flag,
9934: CE_AUTO_BANK_REC.G_open_interface_float_status,
9935: CE_AUTO_BANK_REC.G_open_interface_clear_status,
9928: CE_AUTO_BANK_REC.G_float_handling_flag,
9929: CE_AUTO_BANK_REC.G_ap_matching_order,
9930: CE_AUTO_BANK_REC.G_ar_matching_order,
9931: CE_AUTO_BANK_REC.G_exchange_rate_type,
9932: CE_AUTO_BANK_REC.G_exchange_rate_date,
9933: CE_AUTO_BANK_REC.G_open_interface_flag,
9934: CE_AUTO_BANK_REC.G_open_interface_float_status,
9935: CE_AUTO_BANK_REC.G_open_interface_clear_status,
9936: CE_AUTO_BANK_REC.G_open_interface_matching_code,
9929: CE_AUTO_BANK_REC.G_ap_matching_order,
9930: CE_AUTO_BANK_REC.G_ar_matching_order,
9931: CE_AUTO_BANK_REC.G_exchange_rate_type,
9932: CE_AUTO_BANK_REC.G_exchange_rate_date,
9933: CE_AUTO_BANK_REC.G_open_interface_flag,
9934: CE_AUTO_BANK_REC.G_open_interface_float_status,
9935: CE_AUTO_BANK_REC.G_open_interface_clear_status,
9936: CE_AUTO_BANK_REC.G_open_interface_matching_code,
9937: CE_AUTO_BANK_MATCH.BA_RECON_OI_AMOUNT_TOLERANCE,
9930: CE_AUTO_BANK_REC.G_ar_matching_order,
9931: CE_AUTO_BANK_REC.G_exchange_rate_type,
9932: CE_AUTO_BANK_REC.G_exchange_rate_date,
9933: CE_AUTO_BANK_REC.G_open_interface_flag,
9934: CE_AUTO_BANK_REC.G_open_interface_float_status,
9935: CE_AUTO_BANK_REC.G_open_interface_clear_status,
9936: CE_AUTO_BANK_REC.G_open_interface_matching_code,
9937: CE_AUTO_BANK_MATCH.BA_RECON_OI_AMOUNT_TOLERANCE,
9938: CE_AUTO_BANK_MATCH.BA_RECON_OI_PERCENT_TOLERANCE,
9931: CE_AUTO_BANK_REC.G_exchange_rate_type,
9932: CE_AUTO_BANK_REC.G_exchange_rate_date,
9933: CE_AUTO_BANK_REC.G_open_interface_flag,
9934: CE_AUTO_BANK_REC.G_open_interface_float_status,
9935: CE_AUTO_BANK_REC.G_open_interface_clear_status,
9936: CE_AUTO_BANK_REC.G_open_interface_matching_code,
9937: CE_AUTO_BANK_MATCH.BA_RECON_OI_AMOUNT_TOLERANCE,
9938: CE_AUTO_BANK_MATCH.BA_RECON_OI_PERCENT_TOLERANCE,
9939: ignore_trx_id,
9932: CE_AUTO_BANK_REC.G_exchange_rate_date,
9933: CE_AUTO_BANK_REC.G_open_interface_flag,
9934: CE_AUTO_BANK_REC.G_open_interface_float_status,
9935: CE_AUTO_BANK_REC.G_open_interface_clear_status,
9936: CE_AUTO_BANK_REC.G_open_interface_matching_code,
9937: CE_AUTO_BANK_MATCH.BA_RECON_OI_AMOUNT_TOLERANCE,
9938: CE_AUTO_BANK_MATCH.BA_RECON_OI_PERCENT_TOLERANCE,
9939: ignore_trx_id,
9940: ignore_trx_id2,
9940: ignore_trx_id2,
9941: CE_AUTO_BANK_MATCH.BA_RECON_AP_FX_DIFF_HANDLING,
9942: CE_AUTO_BANK_MATCH.BA_RECON_AR_FX_DIFF_HANDLING,
9943: CE_AUTO_BANK_MATCH.BA_RECON_CE_FX_DIFF_HANDLING,
9944: CE_AUTO_BANK_REC.G_differences_account,
9945: CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT,
9946: CE_AUTO_BANK_MATCH.ba_owner_le_id,
9947: CE_AUTO_BANK_MATCH.csh_bank_account_id,
9948: CE_AUTO_BANK_REC.G_ap_matching_order2 -- FOR SEPA ER 6700007
9941: CE_AUTO_BANK_MATCH.BA_RECON_AP_FX_DIFF_HANDLING,
9942: CE_AUTO_BANK_MATCH.BA_RECON_AR_FX_DIFF_HANDLING,
9943: CE_AUTO_BANK_MATCH.BA_RECON_CE_FX_DIFF_HANDLING,
9944: CE_AUTO_BANK_REC.G_differences_account,
9945: CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT,
9946: CE_AUTO_BANK_MATCH.ba_owner_le_id,
9947: CE_AUTO_BANK_MATCH.csh_bank_account_id,
9948: CE_AUTO_BANK_REC.G_ap_matching_order2 -- FOR SEPA ER 6700007
9949: );
9944: CE_AUTO_BANK_REC.G_differences_account,
9945: CE_AUTO_BANK_REC.G_CE_DIFFERENCES_ACCOUNT,
9946: CE_AUTO_BANK_MATCH.ba_owner_le_id,
9947: CE_AUTO_BANK_MATCH.csh_bank_account_id,
9948: CE_AUTO_BANK_REC.G_ap_matching_order2 -- FOR SEPA ER 6700007
9949: );
9950:
9951:
9952: END IF;
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);
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);
9966: cep_standard.debug('CE_AUTO_BANK_REC.G_open_interface_flag='|| CE_AUTO_BANK_REC.G_open_interface_flag||
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);
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 ||
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);
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);
9969: cep_standard.debug('REC.G_open_interface_matching_code='||CE_AUTO_BANK_REC.G_open_interface_matching_code||
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 ||
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 ||
9971: ', BA_RECON_OI_PERCENT_TOLERANCE='|| BA_RECON_OI_PERCENT_TOLERANCE);
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 ||
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 ||
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 ||
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:
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:
9980: IF (r_branch_cursor%ROWCOUNT = j) THEN
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,
10005: 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,
10005: CE_AUTO_BANK_MATCH.csh_bank_account_id);
10006: i := 0;
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,
10005: CE_AUTO_BANK_MATCH.csh_bank_account_id);
10006: i := 0;
10007: LOOP
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,
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');
10057: where bau.PAY_USE_ENABLE_FLAG = 'Y'
10058: and sysdate <= nvl(end_date,sysdate)
10059: and BANK_ACCOUNT_ID = CE_AUTO_BANK_MATCH.csh_bank_account_id
10060: --and BAU.bank_acct_use_id = CE_AUTO_BANK_MATCH.bau_bank_acct_use_id
10061: and BAU.ORG_ID = nvl(CE_AUTO_BANK_REC.G_org_id, BAU.ORG_ID)
10062: and BAU.ORG_ID = OU.ORGANIZATION_ID
10063: AND OU.ORGANIZATION_TYPE = 'BUSINESS_GROUP';
10064: */
10065: /* SELECT aba.account_classification
10287: CE_AUTO_BANK_MATCH.csl_statement_line_id,
10288: 'CE_LINE_LOCKED');
10289: END IF;
10290: IF (statement_line_count =
10291: CE_AUTO_BANK_REC.G_lines_per_commit) THEN
10292: COMMIT;
10293: statement_line_count := 0;
10294: END IF;
10295:
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');