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