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