summaryrefslogtreecommitdiff
path: root/sql/modules/Reconciliaton.sql
blob: 045d5e93605b641afd6742e76006da4417b7a5d3 (plain)
  1. CREATE TABLE reports (
  2. id bigserial primary key not null,
  3. report_id int NOT NULL,
  4. account text not null,
  5. scn text not null, -- SCN is the check #
  6. their_balance numeric,
  7. our_balance numeric,
  8. errorcode INT,
  9. user int references entity(id) not null, -- why ois this not an entity reference?
  10. corrections INT NOT NULL DEFAULT 0
  11. clear_time TIMESTAMP NOT NULL,
  12. insert_time TIMESTAMPTZ NOT NULL DEFAULT now(),
  13. ledger_id int REFERENCES acc_trans(entry_id),
  14. overlook boolean not null default 'f',
  15. approved boolean not null default 'f'
  16. );
  17. CREATE TABLE coa_to_account (
  18. chart_id int not null references chart(id),
  19. account text not null CHECK (account ~ '[0-9]{7}(xxx)')
  20. );
  21. CREATE TABLE report_corrections (
  22. id serial primary key not null,
  23. correction_id int not null default 1,
  24. entry references reports(id) not null,
  25. user references entity(id) not null, -- why is this not an entity reference?
  26. reason text not null,
  27. insert_time timestamptz not null default now()
  28. );
  29. -- to correct OUR wrong amount.
  30. CREATE OR REPLACE FUNCTION reconciliation__correct_ledger (in_report_id INT, in_id int, in_new_amount NUMERIC, reason TEXT) returns INT AS $$
  31. DECLARE
  32. new_code INT;
  33. current_row RECORD;
  34. l_row acc_trans;
  35. in_user TEXT;
  36. full_reason TEXT;
  37. BEGIN
  38. select into in_user from current_user;
  39. select into current_row from reports where reports.id = in_report_id and reports.id = in_id;
  40. select into l_row from acc_trans where entry_id = current_row.lid;
  41. IF NOT FOUND THEN
  42. RAISE EXCEPTION 'No such id % in this report.', in_scn;
  43. END IF;
  44. IF user <> current_row.user THEN
  45. IF current_row.our_balance <> in_new_amount AND in_new_amount = current_row.their_balance THEN
  46. update reports pr
  47. set pr.corrections = reports.corrections + 1,
  48. pr.new_balance = in_new_amount,
  49. error_code = 0
  50. where id = in_report_id and scn = in_scn;
  51. return 0;
  52. -- After that, it is required to update the general ledger.
  53. full_reason := "User % is filing a reconciliation correction on the general ledger, changing amount % to amount %.
  54. Their reason given is: %", in_user, current_row.our_balance, in_new_amount, reason;
  55. perform select reconciliation__update_ledger(current_row.lid, in_new_amount, full_reason)
  56. ELSE IF current_row.our_balance = in_new_amount THEN
  57. -- This should be something, does it equal the original
  58. -- balance? If so, there's no change.
  59. return current_row.error_code;
  60. END IF;
  61. END IF;
  62. return current_row.error_code;
  63. END;
  64. $$ language 'plpgsql';
  65. -- to correct an incorrect bank statement value.
  66. CREATE OR REPLACE FUNCTION reconciliation__correct_bank_statement (in_report_id INT, in_id int, in_new_amount NUMERIC) returns INT AS $$
  67. DECLARE
  68. new_code INT;
  69. current_row RECORD;
  70. in_user TEXT;
  71. BEGIN
  72. select into in_user from current_user;
  73. select into current_row from reports where reports.id = in_id and reports.report_id = in_report_id;
  74. IF NOT FOUND THEN
  75. RAISE EXCEPTION 'No such SCN % in this report.', in_scn;
  76. END IF;
  77. IF user <> current_row.user THEN
  78. IF current_row.their_balance <> in_new_amount AND in_new_amount = current_row.our_balance THEN
  79. update reports pr
  80. set pr.corrections = reports.corrections + 1,
  81. pr.new_balance = in_new_amount,
  82. error_code = 0
  83. where id = in_report_id and scn = in_scn;
  84. return 0;
  85. ELSE IF current_row.their_balance = in_new_amount THEN
  86. -- This should be something, does it equal the original
  87. -- balance? If so, there's no change.
  88. return current_row.error_code;
  89. END IF;
  90. END IF;
  91. return current_row.error_code;
  92. END;
  93. $$ language 'plpgsql';
  94. CREATE OR REPLACE reconciliation__correct_passthrough ( in_report_id int, in_id int ) returns INT AS $$
  95. DECLARE
  96. in_user TEXT;
  97. pending_entry reports;
  98. BEGIN
  99. select into in_user from current_user;
  100. select into pending_entry from reports where report_id = in_report_id and id = in_id;
  101. IF NOT FOUND THEN
  102. -- Raise an exception.
  103. RAISE EXCEPTION "Cannot find entry.";
  104. ELSE IF pending_entry.errorcode <> 4 THEN
  105. -- Only error codes of 4 may be "passed through" safely.
  106. RAISE EXCEPTION "Selected entry not permitted to be passed through.";
  107. ELSE
  108. -- Then we mark it passthroughable, and "approve" will overlook it.
  109. update reports set overlook = 't', errorcode = 0 where report_id = in_report_id and id = in_id;
  110. return 0;
  111. END IF;
  112. END;
  113. $$ language 'plpgsql';
  114. CREATE OR REPLACE FUNCTION reconciliation__correct_bank_charge (in_report_id int, in_id int) returns INT AS $$
  115. DECLARE
  116. in_user TEXT;
  117. pending_entry reports;
  118. BEGIN
  119. IF NOT FOUND THEN
  120. -- Raise an exception.
  121. RAISE EXCEPTION "Cannot find entry with ID % in report %.", in_id, in_report_id;
  122. ELSE IF pending_entry.errorcode <> 2 THEN
  123. -- Only error codes of 2 may be "passed through" safely.
  124. RAISE EXCEPTION "Attempt to retroactively add a non-bank-charge entry to the ledger.";
  125. ELSE
  126. -- Then we mark it passthroughable, and "approve" will overlook it.
  127. select create_entry (pending_entry.their_balance, 'payable', pending_entry.clear_date, 'Bank charge');
  128. update reports set errorcode = 0 where report_id = in_report_id and id = in_id;
  129. return 0;
  130. END IF;
  131. END;
  132. $$ LANGUAGE 'plpgsql';
  133. CREATE OR REPLACE FUNCTION reconciliation__correct_unaccounted_charge (in_report_id int, in_id int, reason TEXT) RETURNS INT AS $$
  134. DECLARE
  135. in_user TEXT;
  136. pending_entry reports;
  137. note TEXT;
  138. BEGIN
  139. IF NOT FOUND THEN
  140. -- Raise an exception.
  141. RAISE EXCEPTION "Cannot find entry with ID % in report %.", in_id, in_report_id;
  142. ELSE IF pending_entry.errorcode <> 3 THEN
  143. -- Only error codes of 3 may be "passed through" safely.
  144. RAISE EXCEPTION "Not an unaccounted charge; cannot be retroactively added to the ledger.";
  145. ELSE
  146. -- Then we mark it passthroughable, and "approve" will overlook it.
  147. note := 'Retroactive addition of an unaccounted entry, of value %.
  148. Being added by user % with the following explanation: %', pending_entry.their_balance, in_user, in_reason;
  149. select create_entry (pending_entry.their_balance, 'payable', pending_entry.clear_date,note);
  150. update reports set errorcode = 0 where report_id = in_report_id and id = in_id;
  151. return in_id;
  152. END IF;
  153. END;
  154. $$ language 'plpgsql';
  155. CREATE OR REPLACE FUNCTION reconciliation__report_approve (in_report_id INT) returns INT as $$
  156. -- Does some basic checks before allowing the approval to go through;
  157. -- moves the approval to "reports", I guess, or some other "final" table.
  158. --
  159. -- Pending may just be a single flag in the database to mark that it is
  160. -- not finalized. Will need to discuss with Chris.
  161. DECLARE
  162. current_row RECORD;
  163. completed reports;
  164. total_errors INT;
  165. in_user TEXT;
  166. BEGIN
  167. select into in_user current_user;
  168. select into current_row distinct on user * from reports where report_id = in_report_id;
  169. IF NOT FOUND THEN
  170. RAISE EXCEPTION "Fatal Error: Pending report % not found", in_report_id;
  171. END IF;
  172. IF current_row.user = in_user THEN
  173. RAISE EXCEPTION "Fatal Error: User % cannot self-approve report!", in_user;
  174. END IF;
  175. SELECT INTO total_errors count(*) from reports where report_id = in_report_id and error_code <> 0;
  176. IF total_errors <> 0 THEN
  177. RAISE EXCEPTION "Fatal Error: Cannot approve while % uncorrected errors remain.", total_errors;
  178. END IF;
  179. -- so far, so good. Different user, and no errors remain. Therefore, we can move it to completed reports.
  180. --
  181. -- User may not be necessary - I would think it better to use the
  182. -- in_user, to note who approved the report, than the user who
  183. -- filed it. This may require clunkier syntax..
  184. --
  185. update reports set approved = 't', clear_time = now() where report_id = in_report_id;
  186. return 1;
  187. END;
  188. $$ language 'plpgsql';
  189. CREATE OR REPLACE FUNCTION reconciliation__new_report_id () returns INT as $$
  190. SELECT nextval('pending_report_report_id_seq');
  191. $$ language 'sql';
  192. create or replace function reconciliation__add_entry(
  193. in_report_id INT,
  194. in_scn INT,
  195. in_amount numeric,
  196. in_account INT,
  197. in_user TEXT,
  198. in_date TIMESTAMP
  199. ) RETURNS INT AS $$
  200. DELCARE
  201. la RECORD;
  202. errorcode INT;
  203. our_value NUMERIC;
  204. lid INT;
  205. BEGIN
  206. SELECT INTO la FROM acc_trans gl
  207. JOIN chart c on gl.chart_id = c.id
  208. JOIN ap ON gl.trans_id = ap.id
  209. JOIN coa_to_account cta on cta.chart_id = gl.chart_id
  210. WHERE gl.source ~ in_scn -- does it look like it?
  211. and cta.account = in_account
  212. and gl.amount = in_amount
  213. AND gl.transdate = in_date;
  214. lid := NULL;
  215. IF NOT FOUND THEN
  216. -- they have it, we don't. This is Bad, and implies either a bank
  217. -- charge or an unaccounted cheque.
  218. if in_scn <> '' and in_scn IS NOT NULL THEN
  219. -- It's a bank charge. Approval action will probably be
  220. -- adding it as an entry to the general ledger.
  221. errorcode := 2;
  222. our_value := 0;
  223. ELSE
  224. -- Okay, now this is bad.
  225. -- They have a cheque/sourced charge that we don't.
  226. -- REsolution action is going to be
  227. errorcode := 3;
  228. our_value := 0;
  229. END IF;
  230. ELSE if la.amount <> in_amount THEN
  231. errorcode := 1;
  232. our_value := la.amount;
  233. lid := la.entry_id;
  234. ELSE
  235. -- it reconciles. No problem.
  236. errorcode := 0;
  237. our_value := la.amount;
  238. lid := la.entry_id;
  239. END IF;
  240. INSERT INTO reports (
  241. report_id,
  242. scn,
  243. their_balance,
  244. our_balance,
  245. error_code,
  246. user,
  247. clear_time,
  248. ledger_id
  249. )
  250. VALUES (
  251. in_report_id,
  252. in_scn,
  253. in_amount,
  254. gl.amount,
  255. errorcode,
  256. in_user,
  257. in_date,
  258. lid
  259. );
  260. -- success, basically. This could very likely be collapsed to
  261. -- do the compare check here, instead of in the Perl app. Save us a DB
  262. -- call.
  263. return 1;
  264. END;
  265. $$ language 'plpgsql';
  266. create or replace function reconciliation__pending_transactions (in_month DATE) RETURNS setof acc_trans as $$
  267. DECLARE
  268. gl_row acc_trans;
  269. BEGIN
  270. FOR gl_row IN
  271. select gl.* from acc_trans gl, reports pr
  272. where gl.cleared = 'f'
  273. and date_trunc('month',gl.transdate) <= date_trunc('month', in_month)
  274. and gl.entry_id <> pr.ledger_id -- there's no entries in the reports for this
  275. LOOP
  276. RETURN NEXT gl_row;
  277. END LOOP;
  278. END;
  279. $$ LANGUAGE plpgsql;
  280. CREATE OR REPLACE FUNCTION reconciliation__report (in_report_id INT) RETURNS setof reports as $$
  281. DECLARE
  282. row reports;
  283. BEGIN
  284. FOR row IN select * from reports where report_id = in_report_id LOOP
  285. RETURN NEXT row;
  286. END LOOP;
  287. END;
  288. $$ language 'plpgsql';
  289. CREATE OR REPLACE FUNCTION reconciliation__get_total (in_report_id INT) returns setof reports AS $$
  290. DECLARE
  291. row reports;
  292. BEGIN
  293. SELECT INTO row FROM reports
  294. WHERE ledger_id IS NULL
  295. and report_id = in_report_id
  296. AND scn = -1;
  297. IF NOT FOUND THEN -- I think this is a fairly major error condition
  298. RAISE EXCEPTION "No Bank Total found.";
  299. ELSE
  300. return row;
  301. END IF;
  302. END;
  303. $$ language 'plpgsql';
  304. CREATE OR REPLACE FUNCTION reconciliation__corrections (in_report_id INT, in_id INT) returns setof report_corrections AS $$
  305. DECLARE
  306. corr report_corrections;
  307. BEGIN
  308. SELECT INTO corr FROM report_corrections WHERE report_id = in_report_id AND id = in_id LIMIT 1;
  309. IF NOT FOUND THEN
  310. RAISE EXCEPTION "No corrections for selected entry.";
  311. ELSE
  312. FOR corr IN select * from report_corrections WHERE report_id = in_report_id AND id = in_id LOOP
  313. RETURN NEXT corr;
  314. END LOOP;
  315. END IF;
  316. END;
  317. $$ language 'plplsql';
  318. CREATE OR REPLACE FUNCTION reconciliation__single_entry (in_report_id INT, in_id INT) returns setof reports AS $$
  319. DECLARE
  320. row reports;
  321. BEGIN
  322. SELECT INTO row FROM reports WHERE report_id = in_report_id and id = in_id LIMIT 1;
  323. -- if there's more than one, that's a Bad Thing
  324. IF NOT FOUND THEN
  325. RAISE EXCEPTION "Could not find selected report entry";
  326. ELSE
  327. RETURN row;
  328. END IF;
  329. END;
  330. $$ language 'plpgsql';
  331. CREATE OR REPLACE FUNCTION reconciliation__search (
  332. in_date_begin DATE,
  333. in_date_end DATE,
  334. in_account TEXT,
  335. in_status TEXT
  336. ) RETURNS setof reports AS $$
  337. DECLARE
  338. row reports;
  339. statement text;
  340. where_stmt text;
  341. v_status BOOLEAN;
  342. v_accum NUMERIC;
  343. BEGIN
  344. if in_status = "pending" then
  345. v_status = 'ft'::bool;
  346. ELSIF in_status = "approved" THEN
  347. v_status = 't'::bool;
  348. END IF;
  349. IF in_date_begin IS NOT NULL
  350. or in_date_end IS NOT NULL
  351. or in_account IS NOT NULL
  352. or v_status IS NOT NULL
  353. THEN
  354. statement = "select pr.* from reports pr ";
  355. statement = statement + "join acc_trans at on pr.ledger_id = at.entry_id ";
  356. IF in_account IS NOT NULL THEN
  357. statement = statement + "join chart c on at.chart_id = c.id ";
  358. where_stmt = "c.accno =~ " + quote_literal(in_account) + " AND ";
  359. END IF;
  360. IF in_date_begin IS NOT NULL THEN
  361. where_stmt = where_stmt + "insert_time >= " + quote_literal(in_date_begin) + " AND ";
  362. END IF;
  363. IF in_date_end IS NOT NULL THEN
  364. where_stmt = where_stmt + "insert_time <= " + quote_literal(in_date_end) + " AND ";
  365. END IF;
  366. IF in_status IS NOT NULL THEN
  367. if v_status == 't'::bool THEN
  368. where_stmt = where_stmt + " approved = 't'::bool AND ";
  369. ELSIF v_status == 'f'::bool THEN
  370. where_stmt = where_stmt + " approved = 'f'::bool AND ";
  371. END IF;
  372. END IF;
  373. FOR row in EXECUTE statement LOOP
  374. RETURN NEXT row;
  375. END LOOP;
  376. ELSE
  377. FOR row IN SELECT * FROM reports LOOP
  378. RETURN NEXT row;
  379. END LOOP;
  380. END IF;
  381. END;
  382. $$ language 'plpgsql';
  383. create type recon_accounts as (
  384. name text,
  385. id int
  386. );
  387. create or replace function reconciliation__get_accounts () returns setof recon_accounts as $$
  388. SELECT
  389. coa.accno || ' ' || coa.description as name,
  390. coa.id as id
  391. FROM chart coa, coa_to_account cta
  392. WHERE cta.chart_id = coa.id;
  393. $$ language sql;