Schema "transactions"

Tables

#	Table Name	Owner	Tablespace	Description
1	customer_receipts	postgres	DEFAULT	This table stores information on receipts from customers.
2	day_operation	postgres	DEFAULT	This table stores information on the transaction, date & time of its occurance.
3	day_operation_routines	postgres	DEFAULT
4	non_gl_stock_details	postgres	DEFAULT
5	non_gl_stock_master	postgres	DEFAULT	This table stores information of quotations which were upgraded to order(s).
6	non_gl_stock_master_relations	postgres	DEFAULT
7	non_gl_stock_tax_details	postgres	DEFAULT
8	routines	postgres	DEFAULT
9	stock_details	postgres	DEFAULT
10	stock_master	postgres	DEFAULT
11	stock_master_non_gl_relations	postgres	DEFAULT	This table stores information of Non GL Stock Transactions such as orders and quotations which were upgraded to deliveries or invoices.
12	stock_return	postgres	DEFAULT
13	stock_tax_details	postgres	DEFAULT
14	transaction_details	postgres	DEFAULT
15	transaction_master	postgres	DEFAULT

Sequences

#	Sequence Name	Owner	Data Type	Start Value	Increment
1	customer_receipts_receipt_id_seq	postgres	bigint	1	1
2	day_operation_day_id_seq	postgres	bigint	1	1
3	day_operation_routines_day_operation_routine_id_seq	postgres	bigint	1	1
4	non_gl_stock_details_non_gl_stock_detail_id_seq	postgres	bigint	1	1
5	non_gl_stock_master_non_gl_stock_master_id_seq	postgres	bigint	1	1
6	non_gl_stock_master_relations_non_gl_stock_master_relation__seq	postgres	bigint	1	1
7	routines_routine_id_seq	postgres	bigint	1	1
8	stock_details_stock_detail_id_seq	postgres	bigint	1	1
9	stock_master_non_gl_relations_stock_master_non_gl_relation__seq	postgres	bigint	1	1
10	stock_master_stock_master_id_seq	postgres	bigint	1	1
11	stock_return_sales_return_id_seq	postgres	bigint	1	1
12	transaction_details_transaction_detail_id_seq	postgres	bigint	1	1
13	transaction_master_transaction_master_id_seq	postgres	bigint	1	1

Views

#	View Name	Owner	Tablespace
1	sales_by_country_view	postgres	DEFAULT
2	stock_transaction_view	postgres	DEFAULT
3	transaction_view	postgres	DEFAULT
4	verified_stock_details_view	postgres	DEFAULT
5	verified_transaction_view	postgres	DEFAULT

Materialized Views

#	Matview Name	Owner	Tablespace
1	trial_balance_view	mix_erp	DEFAULT
2	verified_cash_transaction_mat_view	mix_erp	DEFAULT
3	verified_stock_transaction_view	mix_erp	DEFAULT
4	verified_transaction_mat_view	mix_erp	DEFAULT

Functions

#	Function	Owner	Description
1	are_sales_orders_already_merged(VARIADIC arr bigint[]) RETURNS boolean	postgres
2	are_sales_quotations_already_merged(VARIADIC arr bigint[]) RETURNS boolean	postgres
3	auto_verify(_tran_id bigint, _office_id integer) RETURNS void	postgres
4	contains_incompatible_taxes(VARIADIC arr bigint[]) RETURNS boolean	postgres
5	create_routine(_routine_code character varying, _routine regproc, _order integer) RETURNS void	postgres
6	get_account_statement(_value_date_from date, _value_date_to date, _user_id integer, _account_id bigint, _office_id integer) RETURNS TABLE(id integer, value_date date, tran_code text, statement_reference text, debit numeric, credit numeric, balance numeric, office text, book text, account_id integer, account_number text, account text, posted_on timestamp with time zone, posted_by text, approved_by text, verification_status integer, flag_bg text, flag_fg text)	postgres
7	get_accrued_interest(office_id integer, party_id bigint) RETURNS money_strict2	postgres
8	get_average_party_transaction(party_id bigint, office_id integer) RETURNS money_strict2	postgres
9	get_average_party_transaction(party_id bigint) RETURNS money_strict2	postgres
10	get_balance_sheet(_previous_period date, _current_period date, _user_id integer, _office_id integer, _factor integer) RETURNS TABLE(id bigint, item text, previous_period numeric, current_period numeric, account_id integer, account_number text, is_retained_earning boolean)	postgres
11	get_cash_flow_statement(_date_from date, _date_to date, _user_id integer, _office_id integer, _factor integer) RETURNS json	postgres
12	get_cash_repository_balance(_cash_repository_id integer) RETURNS money_strict2	postgres
13	get_cash_repository_balance(_cash_repository_id integer, _currency_code character varying) RETURNS money_strict2	postgres
14	get_closing_stock(_on_date date, _office_id integer) RETURNS numeric	postgres
15	get_cost_of_goods_sold(_item_id integer, _unit_id integer, _store_id integer, _quantity integer) RETURNS money_strict	postgres
16	get_default_currency_code(cash_repository_id integer) RETURNS character varying	postgres
17	get_default_currency_code_by_office_id(office_id integer) RETURNS character varying	postgres
18	get_exchange_rate(office_id integer, source_currency_code character varying, destination_currency_code character varying) RETURNS decimal_strict2	postgres
19	get_exchange_rate(office_id integer, currency_code character varying) RETURNS decimal_strict2	postgres
20	get_income_expenditure_statement(_date_from date, _date_to date, _user_id integer, _office_id integer, _compact boolean) RETURNS TABLE(id integer, account_id integer, account_number text, account text, previous_debit numeric, previous_credit numeric, previous_balance numeric, debit numeric, credit numeric, balance numeric, closing_debit numeric, closing_credit numeric, closing_balance numeric)	postgres
21	get_invoice_amount(transaction_master_id_ bigint) RETURNS money_strict2	postgres
22	get_journal_view(_user_id integer, _office_id integer, _from date, _to date, _tran_id bigint, _tran_code character varying, _book character varying, _reference_number character varying, _statement_reference character varying, _posted_by character varying, _office character varying, _status character varying, _verified_by character varying, _reason character varying) RETURNS TABLE(transaction_master_id bigint, transaction_code character varying, book character varying, value_date date, reference_number character varying, statement_reference text, posted_by text, office text, status text, verified_by text, verified_on timestamp with time zone, reason character varying, transaction_ts timestamp with time zone, flag_bg text, flag_fg text)	postgres
23	get_last_receipt_date(office_id integer, party_id bigint) RETURNS date	postgres
24	get_mavcogs(_item_id integer, _store_id integer, _base_quantity numeric, _factor numeric) RETURNS numeric	postgres
25	get_net_profit(_date_from date, _date_to date, _office_id integer, _factor integer, _no_provison boolean DEFAULT false) RETURNS numeric	postgres
26	get_new_transaction_counter(date) RETURNS integer	postgres
27	get_non_gl_product_view(user_id_ integer, book_ text, office_id_ integer, date_from_ date, date_to_ date, office_ character varying, party_ text, price_type_ text, user_ character varying, reference_number_ character varying, statement_reference_ text) RETURNS TABLE(id bigint, value_date date, office character varying, party text, price_type text, amount numeric, transaction_ts timestamp with time zone, "user" character varying, reference_number character varying, statement_reference text, book text, flag_background_color text, flag_foreground_color text)	postgres
28	get_party_transaction_summary(office_id integer, party_id bigint, OUT currency_code text, OUT currency_symbol text, OUT total_due_amount numeric, OUT office_due_amount numeric, OUT accrued_interest numeric, OUT last_receipt_date date, OUT transaction_value numeric) RETURNS record	postgres
29	get_product_view(user_id_ integer, book_ text, office_id_ integer, date_from_ date, date_to_ date, office_ character varying, party_ text, price_type_ text, user_ character varying, reference_number_ character varying, statement_reference_ text) RETURNS TABLE(id bigint, value_date date, office character varying, party text, price_type text, amount numeric, transaction_ts timestamp with time zone, "user" character varying, reference_number character varying, statement_reference text, book text, salesperson text, is_credit boolean, shipper text, shipping_address_code text, store text, flag_background_color text, flag_foreground_color text)	postgres
30	get_profit_and_loss_statement(_date_from date, _date_to date, _user_id integer, _office_id integer, _factor integer, _compact boolean DEFAULT true) RETURNS json	postgres
31	get_purchase(_date_from date, _date_to date, _office_id integer) RETURNS numeric	postgres
32	get_receipt_view(_user_id integer, _office_id integer, _date_from date, _date_to date, _office character varying, _party text, _user character varying, _reference_number character varying, _statement_reference text) RETURNS TABLE(id bigint, value_date date, reference_number text, statement_reference text, office text, party text, "user" text, currency_code text, amount money_strict, transaction_ts timestamp with time zone, flag_background_color text, flag_foreground_color text)	postgres
33	get_reorder_view_function(office_id integer) RETURNS TABLE(item_id integer, item_code character varying, item_name character varying, unit_id integer, unit text, quantity_on_hand numeric, reorder_level integer, reorder_quantity integer, preferred_supplier_id bigint, preferred_supplier text, price money_strict2, tax character varying)	postgres
34	get_retained_earnings(_date_to date, _office_id integer, _factor integer) RETURNS numeric	postgres
35	get_retained_earnings_statement(_date_to date, _office_id integer, _factor integer) RETURNS TABLE(id integer, value_date date, tran_code text, statement_reference text, debit numeric, credit numeric, balance numeric, office text, book text, account_id integer, account_number text, account text, posted_on timestamp with time zone, posted_by text, approved_by text, verification_status integer)	postgres
36	get_sales_by_offices(office_id integer, divide_by integer) RETURNS TABLE(office text, jan numeric, feb numeric, mar numeric, apr numeric, may numeric, jun numeric, jul numeric, aug numeric, sep numeric, oct numeric, nov numeric, "dec" numeric)	postgres
37	get_sales_by_offices(divide_by integer) RETURNS TABLE(office text, jan numeric, feb numeric, mar numeric, apr numeric, may numeric, jun numeric, jul numeric, aug numeric, sep numeric, oct numeric, nov numeric, "dec" numeric)	postgres
38	get_sales_tax(_tran_book character varying, _store_id integer, _party_code character varying, _shipping_address_code character varying, _price_type_id integer, _item_code character varying, _price money_strict2, _quantity integer_strict2, _discount money_strict2, _shipping_charge money_strict2, _sales_tax_id integer) RETURNS SETOF transactions.sales_tax_type	postgres
39	get_sales_tax_id(_tran_book character varying, _store_id integer, _party_code character varying, _shipping_address_code character varying, _price_type_id integer, _item_code character varying, _unit_id integer, _price money_strict) RETURNS integer	postgres
40	get_stock_account_statement(_value_date_from date, _value_date_to date, _user_id integer, _item_id integer, _store_id integer) RETURNS TABLE(id integer, value_date date, tran_code text, statement_reference text, debit numeric, credit numeric, balance numeric, book text, item_id integer, item_code text, item_name text, posted_on timestamp with time zone, posted_by text, approved_by text, verification_status integer, flag_bg text, flag_fg text)	postgres
41	get_stock_account_statement(_value_date_from date, _value_date_to date, _user_id integer, _item_code text, _store_id integer) RETURNS TABLE(id integer, value_date date, tran_code text, statement_reference text, debit numeric, credit numeric, balance numeric, book text, item_id integer, item_code text, item_name text, posted_on timestamp with time zone, posted_by text, approved_by text, verification_status integer, flag_bg text, flag_fg text)	postgres
42	get_stock_master_id_by_transaction_master_id(_stock_master_id bigint) RETURNS bigint	postgres
43	get_top_selling_products_by_office(_office_id integer, top integer) RETURNS TABLE(id integer, office_id integer, office_code text, office_name text, item_id integer, item_code text, item_name text, total_sales numeric)	postgres
44	get_top_selling_products_by_office() RETURNS TABLE(id integer, office_id integer, office_code text, office_name text, item_id integer, item_code text, item_name text, total_sales numeric)	postgres
45	get_top_selling_products_of_all_time() RETURNS TABLE(id integer, item_id integer, item_code text, item_name text, total_sales numeric)	postgres
46	get_top_selling_products_of_all_time(top integer) RETURNS TABLE(id integer, item_id integer, item_code text, item_name text, total_sales numeric)	postgres
47	get_total_due(office_id integer, party_id bigint) RETURNS numeric	postgres
48	get_transaction_code(value_date date, office_id integer, user_id integer, login_id bigint) RETURNS text	postgres
49	get_transaction_master_id_by_stock_master_id(_stock_master_id bigint) RETURNS bigint	postgres
50	get_trial_balance(_date_from date, _date_to date, _user_id integer, _office_id integer, _compact boolean, _factor numeric, _change_side_when_negative boolean DEFAULT true, _include_zero_balance_accounts boolean DEFAULT true) RETURNS TABLE(id integer, account_id integer, account_number text, account text, previous_debit numeric, previous_credit numeric, debit numeric, credit numeric, closing_debit numeric, closing_credit numeric)	postgres
51	get_value_date(_office_id integer) RETURNS date	postgres
52	get_write_off_cost_of_goods_sold(_stock_master_id bigint, _item_id integer, _unit_id integer, _quantity integer) RETURNS money_strict2	postgres
53	has_nexus(_state_id integer) RETURNS boolean	postgres
54	initialize_eod_operation(_user_id integer, _office_id integer, _value_date date) RETURNS void	postgres
55	is_eod_initialized(_office_id integer, _value_date date) RETURNS boolean	postgres
56	is_normally_debit(_account_id bigint) RETURNS boolean	postgres
57	is_purchase(_transaction_master_id bigint) RETURNS boolean	postgres
58	is_valid_party_by_stock_master_id(_stock_master_id bigint, _party_id bigint) RETURNS boolean	postgres
59	is_valid_party_by_transaction_master_id(_transaction_master_id bigint, _party_id bigint) RETURNS boolean	postgres
60	is_valid_stock_transaction_by_stock_master_id(_stock_master_id bigint) RETURNS boolean	postgres
61	is_valid_stock_transaction_by_transaction_master_id(_transaction_master_id bigint) RETURNS boolean	postgres
62	list_closing_stock(_store_id integer) RETURNS TABLE(item_id integer, item_code text, item_name text, unit_id integer, unit_name text, quantity integer)	postgres	Lists stock items, their respective base units, and closing stock quantity.
63	opening_inventory_exists(_office_id integer) RETURNS boolean	postgres
64	perform_eod_operation(_user_id integer, _office_id integer, _value_date date) RETURNS boolean	postgres
65	perform_eod_operation(_login_id bigint) RETURNS boolean	postgres
66	post_non_gl_transaction(_book_name character varying, _office_id integer, _user_id integer, _login_id bigint, _value_date date, _reference_number character varying, _statement_reference text, _party_code character varying, _price_type_id integer, _is_non_taxable_sales boolean, _salesperson_id integer, _shipper_id integer, _shipping_address_code character varying, _store_id integer, _tran_ids bigint[], _details transactions.stock_detail_type[], _attachments core.attachment_type[]) RETURNS bigint	postgres
67	post_opening_inventory(_office_id integer, _user_id integer, _login_id bigint, _value_date date, _reference_number character varying, _statement_reference text, _details transactions.opening_stock_type[]) RETURNS bigint	postgres
68	post_purchase(_book_name character varying, _office_id integer, _user_id integer, _login_id bigint, _value_date date, _cost_center_id integer, _reference_number character varying, _statement_reference text, _is_credit boolean, _party_code character varying, _price_type_id integer, _shipper_id integer, _store_id integer, _tran_ids bigint[], _details transactions.stock_detail_type[], _attachments core.attachment_type[]) RETURNS bigint	postgres
69	post_purchase_return(_transaction_master_id bigint, _office_id integer, _user_id integer, _login_id bigint, _value_date date, _store_id integer, _party_code character varying, _price_type_id integer, _reference_number character varying, _statement_reference text, _details transactions.stock_detail_type[], _attachments core.attachment_type[]) RETURNS bigint	postgres
70	post_purhcase_reorder(_value_date date, _login_id bigint, _user_id integer, _office_id integer, _details transactions.purchase_reorder_type[]) RETURNS boolean	postgres
71	post_receipt_function(_user_id integer, _office_id integer, _login_id bigint, _party_code character varying, _currency_code character varying, _amount money_strict, _exchange_rate_debit decimal_strict, _exchange_rate_credit decimal_strict, _reference_number character varying, _statement_reference character varying, _cost_center_id integer, _cash_repository_id integer, _posted_date date, _bank_account_id integer, _bank_instrument_code character varying, _bank_tran_code character varying) RETURNS bigint	postgres
72	post_sales(_book_name character varying, _office_id integer, _user_id integer, _login_id bigint, _value_date date, _cost_center_id integer, _reference_number character varying, _statement_reference text, _is_credit boolean, _payment_term_id integer, _party_code character varying, _price_type_id integer, _salesperson_id integer, _shipper_id integer, _shipping_address_code character varying, _store_id integer, _is_non_taxable_sales boolean, _details transactions.stock_detail_type[], _attachments core.attachment_type[]) RETURNS bigint	postgres
73	post_sales_return(_transaction_master_id bigint, _office_id integer, _user_id integer, _login_id bigint, _value_date date, _store_id integer, _party_code character varying, _price_type_id integer, _reference_number character varying, _statement_reference text, _details transactions.stock_detail_type[], _attachments core.attachment_type[]) RETURNS bigint	postgres
74	post_stock_adjustment(_office_id integer, _user_id integer, _login_id bigint, _value_date date, _reference_number character varying, _statement_reference text, _details transactions.stock_adjustment_type[]) RETURNS bigint	postgres
75	post_stock_journal(_office_id integer, _user_id integer, _login_id bigint, _value_date date, _reference_number character varying, _statement_reference text, _details transactions.stock_adjustment_type[]) RETURNS bigint	postgres
76	refresh_materialized_views(_office_id integer) RETURNS void	postgres
77	validate_item_for_return(_transaction_master_id bigint, _store_id integer, _item_code character varying, _unit_name character varying, _quantity integer, _price money_strict) RETURNS boolean	postgres
78	verify_transaction(_transaction_master_id bigint, _office_id integer, _user_id integer, _login_id bigint, _verification_status_id smallint, _reason character varying) RETURNS void	postgres

Triggers

#	Trigger	Owner
1	check_cash_balance_trigger() RETURNS TRIGGER	postgres
2	restrict_delete_trigger() RETURNS TRIGGER	postgres
3	verification_trigger() RETURNS TRIGGER	postgres
4	verify_stock_master_integrity_trigger() RETURNS TRIGGER	postgres

Types

#	Type	Base Type	Owner	Type	StoreType	NotNull
1	opening_stock_type	-	postgres	Composite Type	Compressed Inline/Seconary	False
2	purchase_reorder_type	-	postgres	Composite Type	Compressed Inline/Seconary	False
3	sales_tax_type	-	postgres	Composite Type	Compressed Inline/Seconary	False
4	stock_adjustment_type	-	postgres	Composite Type	Compressed Inline/Seconary	False
5	stock_detail_type	-	postgres	Composite Type	Compressed Inline/Seconary	False