[civicrm-core.git] / xml / schema / Financial / FinancialTrxn.xml

<?xml version="1.0" encoding="iso-8859-1" ?>

<table>
  <base>CRM/Financial</base>
  <class>FinancialTrxn</class>
  <name>civicrm_financial_trxn</name>
  <add>1.3</add>
  <log>true</log>
  <component>CiviContribute</component>
  <field>
    <name>id</name>
    <title>Financial Transaction ID</title>
    <type>int unsigned</type>
    <required>true</required>
    <html>
      <type>Number</type>
    </html>
    <add>1.3</add>
  </field>
  <primaryKey>
    <name>id</name>
    <autoincrement>true</autoincrement>
  </primaryKey>
  <field>
    <name>from_financial_account_id</name>
    <type>int unsigned</type>
    <title>From Account ID</title>
    <comment>FK to financial_account table.</comment>
    <add>4.3</add>
    <pseudoconstant>
      <table>civicrm_financial_account</table>
      <keyColumn>id</keyColumn>
      <labelColumn>name</labelColumn>
    </pseudoconstant>
    <html>
      <type>Select</type>
      <label>From Account</label>
    </html>
  </field>
  <foreignKey>
    <name>from_financial_account_id</name>
    <table>civicrm_financial_account</table>
    <key>id</key>
    <add>4.3</add>
  </foreignKey>
  <field>
    <name>to_financial_account_id</name>
    <title>To Account ID</title>
    <type>int unsigned</type>
    <comment>FK to financial_financial_account table.</comment>
    <add>4.3</add>
    <pseudoconstant>
      <table>civicrm_financial_account</table>
      <keyColumn>id</keyColumn>
      <labelColumn>name</labelColumn>
    </pseudoconstant>
    <html>
      <type>Select</type>
      <label>To Account</label>
    </html>
  </field>
  <foreignKey>
    <name>to_financial_account_id</name>
    <table>civicrm_financial_account</table>
    <key>id</key>
    <add>4.3</add>
  </foreignKey>
  <field>
    <name>trxn_date</name>
    <title>Financial Transaction Date</title>
    <type>datetime</type>
    <default>NULL</default>
    <comment>date transaction occurred</comment>
    <add>1.3</add>
    <html>
      <type>Select Date</type>
      <formatType>activityDateTime</formatType>
    </html>
  </field>
  <field>
    <name>total_amount</name>
    <title>Financial Total Amount</title>
    <type>decimal</type>
    <required>true</required>
    <comment>amount of transaction</comment>
    <add>1.3</add>
  </field>
  <field>
    <name>fee_amount</name>
    <title>Financial Fee Amount</title>
    <type>decimal</type>
    <comment>actual processor fee if known - may be 0.</comment>
    <add>1.3</add>
  </field>
  <field>
    <name>net_amount</name>
    <title>Financial Net Amount</title>
    <type>decimal</type>
    <comment>actual funds transfer amount. total less fees. if processor does not report actual fee during transaction, this is set to total_amount.</comment>
    <add>1.3</add>
  </field>
  <field>
    <name>currency</name>
    <title>Financial Currency</title>
    <type>varchar</type>
    <length>3</length>
    <default>NULL</default>
    <import>true</import>
    <headerPattern>/cur(rency)?/i</headerPattern>
    <dataPattern>/^[A-Z]{3}$/</dataPattern>
    <comment>3 character string, value from config setting or input via user.</comment>
    <add>1.3</add>
    <pseudoconstant>
      <table>civicrm_currency</table>
      <keyColumn>name</keyColumn>
      <labelColumn>full_name</labelColumn>
      <nameColumn>name</nameColumn>
      <abbrColumn>symbol</abbrColumn>
    </pseudoconstant>
    <html>
      <type>Select</type>
    </html>
  </field>
  <field>
    <name>is_payment</name>
    <title>Is Payment?</title>
    <type>boolean</type>
    <default>0</default>
    <import>true</import>
    <comment>Is this entry either a payment or a reversal of a payment?</comment>
    <add>4.7</add>
  </field>
  <field>
    <name>trxn_id</name>
    <title>Transaction ID</title>
    <type>varchar</type>
    <length>255</length>
    <comment>Transaction id supplied by external processor. This may not be unique.</comment>
    <html>
      <type>Text</type>
      <size>10</size>
    </html>
    <add>1.3</add>
  </field>
  <index>
    <name>UI_ftrxn_trxn_id</name>
    <fieldName>trxn_id</fieldName>
    <add>4.7</add>
  </index>
  <field>
    <name>trxn_result_code</name>
    <title>Transaction Result Code</title>
    <type>varchar</type>
    <length>255</length>
    <comment>processor result code</comment>
    <add>1.3</add>
  </field>
  <field>
    <name>status_id</name>
    <title>Financial Transaction Status ID</title>
    <type>int unsigned</type>
    <import>true</import>
    <export>true</export>
    <pseudoconstant>
      <optionGroupName>contribution_status</optionGroupName>
    </pseudoconstant>
    <comment>pseudo FK to civicrm_option_value of contribution_status_id option_group</comment>
    <headerPattern>/status/i</headerPattern>
    <add>4.3</add>
  </field>
  <field>
    <name>payment_processor_id</name>
    <title>Payment Processor ID</title>
    <type>int unsigned</type>
    <comment>Payment Processor for this financial transaction</comment>
    <pseudoconstant>
      <table>civicrm_payment_processor</table>
      <keyColumn>id</keyColumn>
      <labelColumn>name</labelColumn>
    </pseudoconstant>
    <html>
      <type>Select</type>
      <label>Payment Processor</label>
    </html>
    <add>4.3</add>
  </field>
  <foreignKey>
    <name>payment_processor_id</name>
    <table>civicrm_payment_processor</table>
    <key>id</key>
    <onDelete>SET NULL</onDelete>
    <add>4.3</add>
  </foreignKey>
  <field>
    <name>payment_instrument_id</name>
    <uniqueName>financial_trxn_payment_instrument_id</uniqueName>
    <title>Payment Method</title>
    <type>int unsigned</type>
    <comment>FK to payment_instrument option group values</comment>
    <pseudoconstant>
      <optionGroupName>payment_instrument</optionGroupName>
    </pseudoconstant>
    <html>
      <type>Select</type>
    </html>
    <add>4.3</add>
  </field>
  <index>
    <name>UI_ftrxn_payment_instrument_id</name>
    <fieldName>payment_instrument_id</fieldName>
    <add>4.3</add>
  </index>
  <field>
    <name>card_type_id</name>
    <uniqueName>financial_trxn_card_type_id</uniqueName>
    <title>Card Type ID</title>
    <type>int unsigned</type>
    <comment>FK to accept_creditcard option group values</comment>
    <pseudoconstant>
      <optionGroupName>accept_creditcard</optionGroupName>
    </pseudoconstant>
    <html>
      <type>Select</type>
    </html>
    <add>4.7</add>
  </field>
  <field>
    <name>check_number</name>
    <uniqueName>financial_trxn_check_number</uniqueName>
    <title>Check Number</title>
    <comment>Check number</comment>
    <type>varchar</type>
    <length>255</length>
    <html>
      <type>Text</type>
      <size>6</size>
    </html>
    <add>4.3</add>
  </field>
  <index>
    <name>UI_ftrxn_check_number</name>
    <fieldName>check_number</fieldName>
    <add>4.3</add>
  </index>
  <field>
    <name>pan_truncation</name>
    <uniqueName>financial_trxn_pan_truncation</uniqueName>
    <title>PAN Truncation</title>
    <type>varchar</type>
    <length>4</length>
    <html>
      <type>Text</type>
      <size>4</size>
    </html>
    <comment>Last 4 digits of credit card</comment>
    <add>4.7</add>
  </field>
  <field>
    <name>order_reference</name>
    <uniqueName>financial_trxn_order_reference</uniqueName>
    <title>Order Reference</title>
    <type>varchar</type>
    <length>255</length>
    <html>
      <type>Text</type>
      <size>25</size>
    </html>
    <comment>Payment Processor external order reference</comment>
    <add>5.20</add>
  </field>
</table>
Commit	Line	Data
6a488035 TO	1	<?xml version="1.0" encoding="iso-8859-1" ?>
6a488035 TO	2
6d68a4cb CW	3	<table>
	4	<base>CRM/Financial</base>
	5	<class>FinancialTrxn</class>
	6	<name>civicrm_financial_trxn</name>
	7	<add>1.3</add>
6a488035	8	<log>true</log>
d31fb4e3	9	<component>CiviContribute</component>
6d68a4cb CW	10	<field>
6d68a4cb CW	11	<name>id</name>
256aba1f	12	<title>Financial Transaction ID</title>
6d68a4cb CW	13	<type>int unsigned</type>
6d68a4cb CW	14	<required>true</required>
0481e12c A	15	<html>
	16	<type>Number</type>
	17	</html>
6d68a4cb CW	18	<add>1.3</add>
	19	</field>
	20	<primaryKey>
	21	<name>id</name>
	22	<autoincrement>true</autoincrement>
	23	</primaryKey>
6d68a4cb CW	24	<field>
	25	<name>from_financial_account_id</name>
	26	<type>int unsigned</type>
047c5009	27	<title>From Account ID</title>
6d68a4cb	28	<comment>FK to financial_account table.</comment>
6a488035	29	<add>4.3</add>
7611ae71 AS	30	<pseudoconstant>
	31	<table>civicrm_financial_account</table>
	32	<keyColumn>id</keyColumn>
	33	<labelColumn>name</labelColumn>
	34	</pseudoconstant>
614d72ec	35	<html>
e0991796	36	<type>Select</type>
047c5009	37	<label>From Account</label>
614d72ec	38	</html>
6d68a4cb CW	39	</field>
	40	<foreignKey>
	41	<name>from_financial_account_id</name>
	42	<table>civicrm_financial_account</table>
	43	<key>id</key>
6a488035 TO	44	<add>4.3</add>
6a488035 TO	45	</foreignKey>
6d68a4cb CW	46	<field>
6d68a4cb CW	47	<name>to_financial_account_id</name>
047c5009	48	<title>To Account ID</title>
6d68a4cb CW	49	<type>int unsigned</type>
6d68a4cb CW	50	<comment>FK to financial_financial_account table.</comment>
6a488035	51	<add>4.3</add>
7611ae71 AS	52	<pseudoconstant>
	53	<table>civicrm_financial_account</table>
	54	<keyColumn>id</keyColumn>
	55	<labelColumn>name</labelColumn>
	56	</pseudoconstant>
614d72ec	57	<html>
e0991796	58	<type>Select</type>
047c5009	59	<label>To Account</label>
614d72ec	60	</html>
6d68a4cb CW	61	</field>
	62	<foreignKey>
	63	<name>to_financial_account_id</name>
	64	<table>civicrm_financial_account</table>
	65	<key>id</key>
6a488035 TO	66	<add>4.3</add>
6a488035 TO	67	</foreignKey>
6d68a4cb CW	68	<field>
6d68a4cb CW	69	<name>trxn_date</name>
256aba1f	70	<title>Financial Transaction Date</title>
6d68a4cb CW	71	<type>datetime</type>
	72	<default>NULL</default>
	73	<comment>date transaction occurred</comment>
	74	<add>1.3</add>
f7fb5615	75	<html>
	76	<type>Select Date</type>
	77	<formatType>activityDateTime</formatType>
	78	</html>
6d68a4cb	79	</field>
6d68a4cb CW	80	<field>
6d68a4cb CW	81	<name>total_amount</name>
256aba1f	82	<title>Financial Total Amount</title>
6d68a4cb	83	<type>decimal</type>
6a488035 TO	84	<required>true</required>
6a488035 TO	85	<comment>amount of transaction</comment>
6d68a4cb CW	86	<add>1.3</add>
	87	</field>
	88	<field>
	89	<name>fee_amount</name>
256aba1f	90	<title>Financial Fee Amount</title>
6d68a4cb	91	<type>decimal</type>
6a488035	92	<comment>actual processor fee if known - may be 0.</comment>
6d68a4cb CW	93	<add>1.3</add>
	94	</field>
	95	<field>
	96	<name>net_amount</name>
256aba1f	97	<title>Financial Net Amount</title>
6d68a4cb	98	<type>decimal</type>
6a488035	99	<comment>actual funds transfer amount. total less fees. if processor does not report actual fee during transaction, this is set to total_amount.</comment>
6d68a4cb	100	<add>1.3</add>
6a488035 TO	101	</field>
	102	<field>
	103	<name>currency</name>
256aba1f	104	<title>Financial Currency</title>
6a488035 TO	105	<type>varchar</type>
6a488035 TO	106	<length>3</length>
6d68a4cb	107	<default>NULL</default>
6a488035 TO	108	<import>true</import>
	109	<headerPattern>/cur(rency)?/i</headerPattern>
	110	<dataPattern>/^[A-Z]{3}$/</dataPattern>
	111	<comment>3 character string, value from config setting or input via user.</comment>
	112	<add>1.3</add>
bd44e0df AS	113	<pseudoconstant>
	114	<table>civicrm_currency</table>
	115	<keyColumn>name</keyColumn>
a38a89fc	116	<labelColumn>full_name</labelColumn>
c3de5c5a	117	<nameColumn>name</nameColumn>
a8fdb24e	118	<abbrColumn>symbol</abbrColumn>
bd44e0df	119	</pseudoconstant>
614d72ec	120	<html>
e0991796	121	<type>Select</type>
614d72ec	122	</html>
6a488035	123	</field>
3987f9b4 DG	124	<field>
	125	<name>is_payment</name>
	126	<title>Is Payment?</title>
	127	<type>boolean</type>
	128	<default>0</default>
	129	<import>true</import>
	130	<comment>Is this entry either a payment or a reversal of a payment?</comment>
	131	<add>4.7</add>
	132	</field>
6d68a4cb CW	133	<field>
6d68a4cb CW	134	<name>trxn_id</name>
256aba1f	135	<title>Transaction ID</title>
6d68a4cb	136	<type>varchar</type>
6a488035	137	<length>255</length>
169abce4	138	<comment>Transaction id supplied by external processor. This may not be unique.</comment>
a7a8f17d	139	<html>
	140	<type>Text</type>
	141	<size>10</size>
	142	</html>
6d68a4cb CW	143	<add>1.3</add>
6d68a4cb CW	144	</field>
169abce4	145	<index>
	146	<name>UI_ftrxn_trxn_id</name>
	147	<fieldName>trxn_id</fieldName>
	148	<add>4.7</add>
	149	</index>
6d68a4cb CW	150	<field>
6d68a4cb CW	151	<name>trxn_result_code</name>
7a39e5e9	152	<title>Transaction Result Code</title>
6d68a4cb CW	153	<type>varchar</type>
	154	<length>255</length>
	155	<comment>processor result code</comment>
	156	<add>1.3</add>
	157	</field>
6a488035 TO	158	<field>
6a488035 TO	159	<name>status_id</name>
047c5009	160	<title>Financial Transaction Status ID</title>
6a488035 TO	161	<type>int unsigned</type>
6a488035 TO	162	<import>true</import>
6d68a4cb	163	<export>true</export>
566f87a4	164	<pseudoconstant>
	165	<optionGroupName>contribution_status</optionGroupName>
	166	</pseudoconstant>
	167	<comment>pseudo FK to civicrm_option_value of contribution_status_id option_group</comment>
6a488035 TO	168	<headerPattern>/status/i</headerPattern>
	169	<add>4.3</add>
	170	</field>
6d68a4cb CW	171	<field>
6d68a4cb CW	172	<name>payment_processor_id</name>
57e94608	173	<title>Payment Processor ID</title>
6d68a4cb CW	174	<type>int unsigned</type>
6d68a4cb CW	175	<comment>Payment Processor for this financial transaction</comment>
68fba329 MW	176	<pseudoconstant>
	177	<table>civicrm_payment_processor</table>
	178	<keyColumn>id</keyColumn>
	179	<labelColumn>name</labelColumn>
	180	</pseudoconstant>
	181	<html>
	182	<type>Select</type>
57e94608	183	<label>Payment Processor</label>
68fba329	184	</html>
6d68a4cb CW	185	<add>4.3</add>
	186	</field>
	187	<foreignKey>
	188	<name>payment_processor_id</name>
	189	<table>civicrm_payment_processor</table>
	190	<key>id</key>
6a488035	191	<onDelete>SET NULL</onDelete>
6d68a4cb	192	<add>4.3</add>
6a488035 TO	193	</foreignKey>
	194	<field>
	195	<name>payment_instrument_id</name>
	196	<uniqueName>financial_trxn_payment_instrument_id</uniqueName>
536f0e02	197	<title>Payment Method</title>
6a488035 TO	198	<type>int unsigned</type>
	199	<comment>FK to payment_instrument option group values</comment>
	200	<pseudoconstant>
6d68a4cb	201	<optionGroupName>payment_instrument</optionGroupName>
6a488035	202	</pseudoconstant>
614d72ec	203	<html>
e0991796	204	<type>Select</type>
614d72ec	205	</html>
6a488035 TO	206	<add>4.3</add>
	207	</field>
	208	<index>
	209	<name>UI_ftrxn_payment_instrument_id</name>
	210	<fieldName>payment_instrument_id</fieldName>
	211	<add>4.3</add>
	212	</index>
ea927522	213	<field>
5e0343e8	214	<name>card_type_id</name>
	215	<uniqueName>financial_trxn_card_type_id</uniqueName>
	216	<title>Card Type ID</title>
ea927522 PN	217	<type>int unsigned</type>
	218	<comment>FK to accept_creditcard option group values</comment>
	219	<pseudoconstant>
	220	<optionGroupName>accept_creditcard</optionGroupName>
	221	</pseudoconstant>
	222	<html>
	223	<type>Select</type>
	224	</html>
	225	<add>4.7</add>
	226	</field>
6a488035 TO	227	<field>
	228	<name>check_number</name>
	229	<uniqueName>financial_trxn_check_number</uniqueName>
	230	<title>Check Number</title>
	231	<comment>Check number</comment>
	232	<type>varchar</type>
	233	<length>255</length>
614d72ec	234	<html>
614d72ec	235	<type>Text</type>
54f5e87f	236	<size>6</size>
614d72ec	237	</html>
6a488035 TO	238	<add>4.3</add>
	239	</field>
	240	<index>
	241	<name>UI_ftrxn_check_number</name>
	242	<fieldName>check_number</fieldName>
	243	<add>4.3</add>
	244	</index>
ea927522 PN	245	<field>
	246	<name>pan_truncation</name>
	247	<uniqueName>financial_trxn_pan_truncation</uniqueName>
788d0288	248	<title>PAN Truncation</title>
4e0ede5a PN	249	<type>varchar</type>
4e0ede5a PN	250	<length>4</length>
ea927522 PN	251	<html>
	252	<type>Text</type>
	253	<size>4</size>
	254	</html>
	255	<comment>Last 4 digits of credit card</comment>
	256	<add>4.7</add>
	257	</field>
1f34d30a AS	258	<field>
	259	<name>order_reference</name>
	260	<uniqueName>financial_trxn_order_reference</uniqueName>
	261	<title>Order Reference</title>
	262	<type>varchar</type>
	263	<length>255</length>
	264	<html>
	265	<type>Text</type>
	266	<size>25</size>
	267	</html>
	268	<comment>Payment Processor external order reference</comment>
	269	<add>5.20</add>
	270	</field>
6a488035	271	</table>