summaryrefslogtreecommitdiff
path: root/sql/modules/Payment.sql
blob: bee243f8c610479d8187fdbb8cb8c1f1b13bdf53 (plain)
  1. CREATE TYPE payment_vc_info AS (
  2. id int,
  3. name text,
  4. entity_class int,
  5. discount int
  6. );
  7. CREATE OR REPLACE FUNCTION payment_get_entity_accounts
  8. (in_account_class int,
  9. in_vc_name text,
  10. in_vc_idn int)
  11. returns SETOF payment_vc_info AS
  12. $$
  13. DECLARE out_entity payment_vc_info;
  14. BEGIN
  15. FOR out_entity IN
  16. SELECT ec.id, cp.legal_name as name, e.entity_class, ec.discount_account_id
  17. FROM entity e
  18. JOIN entity_credit_account ec ON (ec.entity_id = e.id)
  19. JOIN company cp ON (cp.entity_id = e.id)
  20. WHERE ec.entity_class = in_account_class
  21. AND (cp.legal_name ilike coalesce('%'||in_vc_name||'%','%%') OR cp.tax_id = in_vc_idn)
  22. LOOP
  23. RETURN NEXT out_entity;
  24. END LOOP;
  25. END;
  26. $$ LANGUAGE PLPGSQL;
  27. -- payment_get_open_accounts and the option to get all accounts need to be
  28. -- refactored and redesigned. -- CT
  29. CREATE OR REPLACE FUNCTION payment_get_open_accounts(in_account_class int)
  30. returns SETOF entity AS
  31. $$
  32. DECLARE out_entity entity%ROWTYPE;
  33. BEGIN
  34. FOR out_entity IN
  35. SELECT ec.id, cp.legal_name as name, e.entity_class, e.created
  36. FROM entity e
  37. JOIN entity_credit_account ec ON (ec.entity_id = e.id)
  38. JOIN company cp ON (cp.entity_id = e.id)
  39. WHERE ec.entity_class = in_account_class
  40. -- AND CASE WHEN in_account_class = 1 THEN
  41. -- e.id IN (SELECT entity_id FROM ap
  42. -- WHERE amount <> paid
  43. -- GROUP BY entity_id)
  44. -- WHEN in_account_class = 2 THEN
  45. -- e.id IN (SELECT entity_id FROM ar
  46. -- WHERE amount <> paid
  47. -- GROUP BY entity_id)
  48. -- END
  49. LOOP
  50. RETURN NEXT out_entity;
  51. END LOOP;
  52. END;
  53. $$ LANGUAGE PLPGSQL;
  54. COMMENT ON FUNCTION payment_get_open_accounts(int) IS
  55. $$ This function takes a single argument (1 for vendor, 2 for customer as
  56. always) and returns all entities with open accounts of the appropriate type. $$;
  57. CREATE OR REPLACE FUNCTION payment_get_all_accounts(in_account_class int)
  58. RETURNS SETOF entity AS
  59. $$
  60. DECLARE out_entity entity%ROWTYPE;
  61. BEGIN
  62. FOR out_entity IN
  63. SELECT ec.id,
  64. e.name, e.entity_class, e.created
  65. FROM entity e
  66. JOIN entity_credit_account ec ON (ec.entity_id = e.id)
  67. WHERE e.entity_class = in_account_class
  68. LOOP
  69. RETURN NEXT out_entity;
  70. END LOOP;
  71. END;
  72. $$ LANGUAGE PLPGSQL;
  73. COMMENT ON FUNCTION payment_get_open_accounts(int) IS
  74. $$ This function takes a single argument (1 for vendor, 2 for customer as
  75. always) and returns all entities with accounts of the appropriate type. $$;
  76. CREATE TYPE payment_invoice AS (
  77. invoice_id int,
  78. invnumber text,
  79. invoice_date date,
  80. amount numeric,
  81. discount numeric,
  82. due numeric
  83. );
  84. CREATE OR REPLACE FUNCTION payment_get_open_invoices
  85. (in_account_class int,
  86. in_entity_credit_id int,
  87. in_curr char(3),
  88. in_datefrom date,
  89. in_dateto date,
  90. in_amountfrom numeric,
  91. in_amountto numeric,
  92. in_department_id int)
  93. RETURNS SETOF payment_invoice AS
  94. $$
  95. DECLARE payment_inv payment_invoice;
  96. BEGIN
  97. FOR payment_inv IN
  98. SELECT a.id AS invoice_id, a.invnumber AS invnumber,
  99. a.transdate AS invoice_date, a.amount AS amount,
  100. (CASE WHEN c.discount_terms < extract('days' FROM age(a.transdate))
  101. THEN 0
  102. ELSE (coalesce(ac.due, a.amount)) * coalesce(c.discount, 0) / 100
  103. END) AS discount, ac.due
  104. FROM (SELECT id, invnumber, transdate, amount, entity_id,
  105. 1 as invoice_class, paid, curr,
  106. entity_credit_account, department_id
  107. FROM ap
  108. UNION
  109. SELECT id, invnumber, transdate, amount, entity_id,
  110. 2 AS invoice_class, paid, curr,
  111. entity_credit_account, department_id
  112. FROM ar
  113. ) a
  114. JOIN (SELECT trans_id, chart_id, sum(CASE WHEN in_account_class = 1 THEN amount
  115. WHEN in_account_class = 2
  116. THEN amount * -1
  117. END) as due
  118. FROM acc_trans
  119. GROUP BY trans_id, chart_id) ac ON (ac.trans_id = a.id)
  120. JOIN chart ON (chart.id = ac.chart_id)
  121. JOIN entity_credit_account c ON (c.id = a.entity_credit_account
  122. OR (a.entity_credit_account IS NULL and a.entity_id = c.entity_id))
  123. WHERE ((chart.link = 'AP' AND in_account_class = 1)
  124. OR (chart.link = 'AR' AND in_account_class = 2))
  125. AND a.invoice_class = in_account_class
  126. AND c.entity_class = in_account_class
  127. AND c.id = in_entity_credit_id
  128. AND a.amount - a.paid <> 0
  129. AND a.curr = in_curr
  130. AND (a.transdate >= in_datefrom
  131. OR in_datefrom IS NULL)
  132. AND (a.transdate <= in_dateto
  133. OR in_dateto IS NULL)
  134. AND (a.amount >= in_amountfrom
  135. OR in_amountfrom IS NULL)
  136. AND (a.amount <= in_amountto
  137. OR in_amountto IS NULL)
  138. AND (a.department_id = in_department_id
  139. OR in_department_id IS NULL)
  140. AND due <> 0
  141. GROUP BY a.invnumber, a.transdate, a.amount, discount, ac.due, a.id, c.discount_terms
  142. LOOP
  143. RETURN NEXT payment_inv;
  144. END LOOP;
  145. END;
  146. $$ LANGUAGE PLPGSQL;
  147. COMMENT ON FUNCTION payment_get_open_invoices(int, int, char(3), date, date, numeric, numeric, int) IS
  148. $$ This function takes three arguments:
  149. Type: 1 for vendor, 2 for customer
  150. Entity_id: The entity_id of the customer or vendor
  151. Currency: 3 characters for currency ('USD' for example).
  152. Returns all open invoices for the entity in question. $$;
  153. CREATE TYPE payment_contact_invoice AS (
  154. contact_id int,
  155. contact_name text,
  156. account_number text,
  157. total_due numeric,
  158. invoices text[],
  159. has_vouchers int
  160. );
  161. CREATE OR REPLACE FUNCTION payment_get_all_contact_invoices
  162. (in_account_class int, in_business_id int, in_currency char(3),
  163. in_date_from date, in_date_to date, in_batch_id int,
  164. in_ar_ap_accno text, in_meta_number text)
  165. RETURNS SETOF payment_contact_invoice AS
  166. $$
  167. DECLARE payment_item payment_contact_invoice;
  168. BEGIN
  169. FOR payment_item IN
  170. SELECT c.id AS contact_id, e.name AS contact_name,
  171. c.meta_number AS account_number,
  172. sum (coalesce(p.due, 0) -
  173. CASE WHEN c.discount_terms
  174. > extract('days' FROM age(a.transdate))
  175. THEN 0
  176. ELSE (coalesce(p.due, 0)) * coalesce(c.discount, 0) / 100
  177. END) AS total_due,
  178. compound_array(ARRAY[[
  179. a.id::text, a.invnumber, a.transdate::text,
  180. a.amount::text, (a.amount - p.due)::text,
  181. (CASE WHEN c.discount_terms
  182. > extract('days' FROM age(a.transdate))
  183. THEN 0
  184. ELSE (a.amount - coalesce((a.amount - p.due), 0)) * coalesce(c.discount, 0) / 100
  185. END)::text,
  186. (coalesce(p.due, 0) -
  187. (CASE WHEN c.discount_terms
  188. > extract('days' FROM age(a.transdate))
  189. THEN 0
  190. ELSE (coalesce(p.due, 0)) * coalesce(c.discount, 0) / 100
  191. END))::text]]),
  192. sum(case when a.batch_id = in_batch_id then 1
  193. else 0 END),
  194. bool_and(lock_record(a.id, (select max(session_id) FROM "session" where users_id = (
  195. select id from users WHERE username =
  196. SESSION_USER))))
  197. FROM entity e
  198. JOIN entity_credit_account c ON (e.id = c.entity_id)
  199. JOIN (SELECT ap.id, invnumber, transdate, amount, entity_id,
  200. paid, curr, 1 as invoice_class,
  201. entity_credit_account, on_hold, v.batch_id
  202. FROM ap
  203. LEFT JOIN (select * from voucher where batch_class = 1) v
  204. ON (ap.id = v.trans_id)
  205. WHERE in_account_class = 1
  206. AND (v.batch_class = 1 or v.batch_id IS NULL)
  207. UNION
  208. SELECT ar.id, invnumber, transdate, amount, entity_id,
  209. paid, curr, 2 as invoice_class,
  210. entity_credit_account, on_hold, v.batch_id
  211. FROM ar
  212. LEFT JOIN (select * from voucher where batch_class = 2) v
  213. ON (ar.id = v.trans_id)
  214. WHERE in_account_class = 2
  215. AND (v.batch_class = 2 or v.batch_id IS NULL)
  216. ORDER BY transdate
  217. ) a ON (a.entity_credit_account = c.id)
  218. JOIN transactions t ON (a.id = t.id)
  219. JOIN (SELECT trans_id,
  220. sum(CASE WHEN in_account_class = 1 THEN amount
  221. WHEN in_account_class = 2
  222. THEN amount * -1
  223. END) AS due
  224. FROM acc_trans
  225. JOIN chart ON (chart.id = acc_trans.chart_id)
  226. WHERE ((chart.link = 'AP' AND in_account_class = 1)
  227. OR (chart.link = 'AR' AND in_account_class = 2))
  228. GROUP BY trans_id) p ON (a.id = p.trans_id)
  229. WHERE a.batch_id = in_batch_id
  230. OR (a.invoice_class = in_account_class
  231. AND c.business_id =
  232. coalesce(in_business_id, c.business_id)
  233. AND ((a.transdate >= COALESCE(in_date_from, a.transdate)
  234. AND a.transdate <= COALESCE(in_date_to, a.transdate)))
  235. AND c.entity_class = in_account_class
  236. AND a.curr = in_currency
  237. AND a.entity_credit_account = c.id
  238. AND p.due <> 0
  239. AND a.amount <> a.paid
  240. AND NOT a.on_hold
  241. AND NOT (t.locked_by IS NOT NULL AND t.locked_by IN
  242. (select "session_id" FROM "session"
  243. WHERE users_id IN
  244. (select id from users
  245. where username <> SESSION_USER)))
  246. AND EXISTS (select trans_id FROM acc_trans
  247. WHERE trans_id = a.id AND
  248. chart_id = (SELECT id frOM chart
  249. WHERE accno
  250. = in_ar_ap_accno)
  251. ))
  252. GROUP BY c.id, e.name, c.meta_number, c.threshold
  253. HAVING (in_meta_number IS NULL
  254. OR in_meta_number = c.meta_number) AND
  255. (sum(p.due) > c.threshold
  256. OR sum(case when a.batch_id = in_batch_id then 1
  257. else 0 END) > 0)
  258. ORDER BY c.meta_number ASC
  259. LOOP
  260. RETURN NEXT payment_item;
  261. END LOOP;
  262. END;
  263. $$ LANGUAGE plpgsql;
  264. COMMENT ON FUNCTION payment_get_all_contact_invoices
  265. (in_account_class int, in_business_type int, in_currency char(3),
  266. in_date_from date, in_date_to date, in_batch_id int,
  267. in_ar_ap_accno text) IS
  268. $$
  269. This function takes the following arguments (all prefaced with in_ in the db):
  270. account_class: 1 for vendor, 2 for customer
  271. business_type: integer of business.id.
  272. currency: char(3) of currency (for example 'USD')
  273. date_from, date_to: These dates are inclusive.
  274. 1;3B
  275. batch_id: For payment batches, where fees are concerned.
  276. ar_ap_accno: The AR/AP account number.
  277. This then returns a set of contact information with a 2 dimensional array
  278. cnsisting of outstanding invoices.
  279. $$;
  280. CREATE OR REPLACE FUNCTION payment_bulk_queue
  281. (in_transactions numeric[], in_batch_id int, in_source text, in_total numeric,
  282. in_ar_ap_accno text, in_cash_accno text,
  283. in_payment_date date, in_account_class int)
  284. returns int as
  285. $$
  286. BEGIN
  287. INSERT INTO payments_queue
  288. (transactions, batch_id, source, total, ar_ap_accno, cash_accno,
  289. payment_date, account_class)
  290. VALUES
  291. (in_transactions, in_batch_id, in_source, in_total, in_ar_ap_accno,
  292. in_cash_accno, in_payment_date, in_account_class);
  293. RETURN array_upper(in_transactions, 1) -
  294. array_lower(in_transactions, 1);
  295. END;
  296. $$ LANGUAGE PLPGSQL;
  297. CREATE OR REPLACE FUNCTION job__process_payment(in_job_id int)
  298. RETURNS bool AS $$
  299. DECLARE
  300. queue_record RECORD;
  301. t_auth_name text;
  302. t_counter int;
  303. BEGIN
  304. -- TODO: Move the set session authorization into a utility function
  305. SELECT entered_by INTO t_auth_name FROM pending_job
  306. WHERE id = in_job_id;
  307. EXECUTE 'SET SESSION AUTHORIZATION ' || quote_ident(t_auth_name);
  308. t_counter := 0;
  309. FOR queue_record IN
  310. SELECT *
  311. FROM payments_queue WHERE job_id = in_job_id
  312. LOOP
  313. PERFORM payment_bulk_post
  314. (queue_record.transactions, queue_record.batch_id,
  315. queue_record.source, queue_record.total,
  316. queue_record.ar_ap_accno,
  317. queue_record.cash_accno,
  318. queue_record.payment_date,
  319. queue_record.account_class);
  320. t_counter := t_counter + 1;
  321. RAISE NOTICE 'Processed record %, starting transaction %',
  322. t_counter, queue_record.transactions[1][1];
  323. END LOOP;
  324. DELETE FROM payments_queue WHERE job_id = in_job_id;
  325. UPDATE pending_job
  326. SET completed_at = timeofday()::timestamp,
  327. success = true
  328. WHERE id = in_job_id;
  329. RETURN TRUE;
  330. END;
  331. $$ language plpgsql;
  332. CREATE OR REPLACE FUNCTION job__create(in_batch_class int, in_batch_id int)
  333. RETURNS int AS
  334. $$
  335. BEGIN
  336. INSERT INTO pending_job (batch_class, batch_id)
  337. VALUES (coalesce(in_batch_class, 3), in_batch_id);
  338. RETURN currval('pending_job_id_seq');
  339. END;
  340. $$ LANGUAGE PLPGSQL;
  341. CREATE TYPE job__status AS (
  342. completed int, -- 1 for completed, 0 for no
  343. success int, -- 1 for success, 0 for no
  344. completed_at timestamp,
  345. error_condition text -- error if not successful
  346. );
  347. CREATE OR REPLACE FUNCTION job__status(in_job_id int) RETURNS job__status AS
  348. $$
  349. DECLARE out_row job__status;
  350. BEGIN
  351. SELECT (completed_at IS NULL)::INT, success::int, completed_at,
  352. error_condition
  353. INTO out_row
  354. FROM pending_job
  355. WHERE id = in_job_id;
  356. RETURN out_row;
  357. END;
  358. $$ language plpgsql;
  359. CREATE OR REPLACE FUNCTION payment_bulk_post
  360. (in_transactions numeric[], in_batch_id int, in_source text, in_total numeric,
  361. in_ar_ap_accno text, in_cash_accno text,
  362. in_payment_date date, in_account_class int)
  363. RETURNS int AS
  364. $$
  365. DECLARE
  366. out_count int;
  367. t_voucher_id int;
  368. t_trans_id int;
  369. t_amount numeric;
  370. t_ar_ap_id int;
  371. t_cash_id int;
  372. BEGIN
  373. IF in_batch_id IS NULL THEN
  374. -- t_voucher_id := NULL;
  375. RAISE EXCEPTION 'Bulk Post Must be from Batch!';
  376. ELSE
  377. INSERT INTO voucher (batch_id, batch_class, trans_id)
  378. values (in_batch_id, 3, in_transactions[1][1]);
  379. t_voucher_id := currval('voucher_id_seq');
  380. END IF;
  381. select id into t_ar_ap_id from chart where accno = in_ar_ap_accno;
  382. select id into t_cash_id from chart where accno = in_cash_accno;
  383. FOR out_count IN
  384. array_lower(in_transactions, 1) ..
  385. array_upper(in_transactions, 1)
  386. LOOP
  387. INSERT INTO acc_trans
  388. (trans_id, chart_id, amount, approved, voucher_id,
  389. transdate, source)
  390. VALUES
  391. (in_transactions[out_count][1],
  392. case when in_account_class = 1 THEN t_cash_id
  393. WHEN in_account_class = 2 THEN t_ar_ap_id
  394. ELSE -1 END,
  395. in_transactions[out_count][2],
  396. CASE WHEN t_voucher_id IS NULL THEN true
  397. ELSE false END,
  398. t_voucher_id, in_payment_date, in_source);
  399. INSERT INTO acc_trans
  400. (trans_id, chart_id, amount, approved, voucher_id,
  401. transdate, source)
  402. VALUES
  403. (in_transactions[out_count][1],
  404. case when in_account_class = 1 THEN t_ar_ap_id
  405. WHEN in_account_class = 2 THEN t_cash_id
  406. ELSE -1 END,
  407. in_transactions[out_count][2]* -1,
  408. CASE WHEN t_voucher_id IS NULL THEN true
  409. ELSE false END,
  410. t_voucher_id, in_payment_date, in_source);
  411. UPDATE ap
  412. set paid = paid +in_transactions[out_count][2]
  413. where id =in_transactions[out_count][1];
  414. END LOOP;
  415. return out_count;
  416. END;
  417. $$ language plpgsql;
  418. COMMENT ON FUNCTION payment_bulk_post
  419. (in_transactions numeric[], in_batch_id int, in_source text, in_total numeric,
  420. in_ar_ap_accno text, in_cash_accno text,
  421. in_payment_date date, in_account_class int)
  422. IS
  423. $$ Note that in_transactions is a two-dimensional numeric array. Of each
  424. sub-array, the first element is the (integer) transaction id, and the second
  425. is the amount for that transaction. If the total of the amounts do not add up
  426. to in_total, then an error is generated. $$;
  427. --
  428. -- WE NEED A PAYMENT TABLE
  429. --
  430. CREATE TABLE payment (
  431. id serial primary key,
  432. reference text NOT NULL,
  433. gl_id integer references gl(id),
  434. payment_class integer NOT NULL,
  435. payment_date date default current_date,
  436. closed bool default FALSE,
  437. entity_id integer references entity(id),
  438. employee_id integer references entity_employee(entity_id),
  439. currency char(3),
  440. notes text,
  441. department_id integer default 0);
  442. COMMENT ON TABLE payment IS $$ This table will store the main data on a payment, prepayment, overpayment, et$$;
  443. COMMENT ON COLUMN payment.reference IS $$ This field will store the code for both receipts and payment order $$;
  444. COMMENT ON COLUMN payment.closed IS $$ This will store the current state of a payment/receipt order $$;
  445. COMMENT ON COLUMN payment.gl_id IS $$ A payment should always be linked to a GL movement $$;
  446. CREATE INDEX payment_id_idx ON payment(id);
  447. CREATE TABLE payment_links (
  448. payment_id integer references Payment(id),
  449. entry_id integer references acc_trans(entry_id),
  450. type integer);
  451. COMMENT ON TABLE payment_links IS $$
  452. An explanation to the type field.
  453. * A type 0 means the link is referencing an ar/ap and was created
  454. using an overpayment movement after the receipt was created
  455. * A type 1 means the link is referencing an ar/ap and was made
  456. on the payment creation, its not the product of an overpayment movement
  457. * A type 2 means the link is not referencing an ar/ap and its the product
  458. of the overpayment logic
  459. With this ideas in order we can do the following
  460. To get the payment amount we will sum the entries with type > 0.
  461. To get the linked amount we will sum the entries with type < 2.
  462. The overpayment account can be obtained from the entries with type = 2.
  463. This reasoning is hacky and i hope it can dissapear when we get to 1.4 - D.M.
  464. $$;
  465. CREATE OR REPLACE FUNCTION payment_post
  466. (in_datepaid date,
  467. in_account_class int,
  468. in_entity_id int,
  469. in_curr char(3),
  470. in_notes text,
  471. in_department_id int,
  472. in_gl_description text,
  473. in_cash_account_id int[],
  474. in_amount numeric[],
  475. in_cash_approved bool[],
  476. in_source text[],
  477. in_transaction_id int[],
  478. in_op_amount numeric[],
  479. in_op_cash_account_id int[],
  480. in_op_source text[],
  481. in_op_memo text[],
  482. in_op_account_id int[],
  483. in_approved bool)
  484. RETURNS INT AS
  485. $$
  486. DECLARE var_payment_id int;
  487. DECLARE var_gl_id int;
  488. DECLARE var_entry record;
  489. DECLARE var_entry_id int[];
  490. DECLARE out_count int;
  491. DECLARE coa_id record;
  492. DECLARE var_employee int;
  493. DECLARE var_account_id int;
  494. BEGIN
  495. SELECT INTO var_employee entity_id FROM users WHERE username = SESSION_USER LIMIT 1;
  496. --
  497. -- SECOND WE HAVE TO INSERT THE PAYMENT, USING THE GL INFORMATION
  498. -- THE ID IS GENERATED BY payment_id_seq
  499. --
  500. INSERT INTO payment (reference, payment_class, payment_date,
  501. employee_id, currency, notes, department_id, entity_id)
  502. VALUES ((CASE WHEN in_account_class = 1 THEN
  503. setting_increment('rcptnumber') -- I FOUND THIS ON sql/modules/Settings.sql
  504. ELSE -- and it is very usefull
  505. setting_increment('paynumber')
  506. END),
  507. in_account_class, in_datepaid, var_employee,
  508. in_curr, in_notes, in_department_id, in_entity_id);
  509. SELECT currval('payment_id_seq') INTO var_payment_id; -- WE'LL NEED THIS VALUE TO USE payment_link table
  510. -- WE'LL NEED THIS VALUE TO JOIN WITH PAYMENT
  511. -- NOW COMES THE HEAVY PART, STORING ALL THE POSSIBLE TRANSACTIONS...
  512. --
  513. -- FIRST WE SHOULD INSERT THE CASH ACCOUNTS
  514. --
  515. -- WE SHOULD HAVE THE DATA STORED AS (ACCNO, AMOUNT), SO
  516. FOR out_count IN
  517. array_lower(in_cash_account_id, 1) ..
  518. array_upper(in_cash_account_id, 1)
  519. LOOP
  520. INSERT INTO acc_trans (chart_id, amount,
  521. trans_id, transdate, approved, source)
  522. VALUES (in_cash_account_id[out_count],
  523. CASE WHEN in_account_class = 1 THEN in_amount[out_count]
  524. ELSE in_amount[out_count]* - 1
  525. END,
  526. in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true),
  527. in_source[out_count]);
  528. INSERT INTO payment_links
  529. VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 1);
  530. END LOOP;
  531. -- NOW LETS HANDLE THE AR/AP ACCOUNTS
  532. -- WE RECEIVED THE TRANSACTIONS_ID AND WE CAN OBTAIN THE ACCOUNT FROM THERE
  533. FOR out_count IN
  534. array_lower(in_transaction_id, 1) ..
  535. array_upper(in_transaction_id, 1)
  536. LOOP
  537. SELECT INTO var_account_id chart_id FROM acc_trans as ac
  538. JOIN chart as c ON (c.id = ac.chart_id)
  539. WHERE
  540. trans_id = in_transaction_id[out_count] AND
  541. ( c.link = 'AP' OR c.link = 'AR' );
  542. INSERT INTO acc_trans (chart_id, amount,
  543. trans_id, transdate, approved, source)
  544. VALUES (var_account_id,
  545. CASE WHEN in_account_class = 1 THEN in_amount[out_count] * -1
  546. ELSE in_amount[out_count]
  547. END,
  548. in_transaction_id[out_count], in_datepaid, coalesce(in_approved, true),
  549. in_source[out_count]);
  550. INSERT INTO payment_links
  551. VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 1);
  552. END LOOP;
  553. --
  554. -- WE NEED TO HANDLE THE OVERPAYMENTS NOW
  555. --
  556. --
  557. -- FIRST WE HAVE TO MAKE THE GL TO HOLD THE OVERPAYMENT TRANSACTIONS
  558. -- THE ID IS GENERATED BY gl_id_seq
  559. --
  560. IF (array_upper(in_op_cash_account_id, 1) > 0) THEN
  561. INSERT INTO gl (reference, description, transdate,
  562. person_id, notes, approved, department_id)
  563. VALUES (setting_increment('glnumber'),
  564. in_gl_description, in_datepaid, var_employee,
  565. in_notes, in_approved, in_department_id);
  566. SELECT currval('id') INTO var_gl_id;
  567. --
  568. -- WE NEED TO SET THE GL_ID FIELD ON PAYMENT'S TABLE
  569. --
  570. UPDATE payment SET gl_id = var_gl_id
  571. WHERE id = var_payment_id;
  572. -- NOW COMES THE HEAVY PART, STORING ALL THE POSSIBLE TRANSACTIONS...
  573. --
  574. -- FIRST WE SHOULD INSERT THE OVERPAYMENT CASH ACCOUNTS
  575. --
  576. FOR out_count IN
  577. array_lower(in_op_cash_account_id, 1) ..
  578. array_upper(in_op_cash_account_id, 1)
  579. LOOP
  580. INSERT INTO acc_trans (chart_id, amount,
  581. trans_id, transdate, approved, source)
  582. VALUES (in_op_cash_account_id[out_count],
  583. CASE WHEN in_account_class = 2 THEN in_op_amount[out_count]
  584. ELSE in_op_amount[out_count] * - 1
  585. END,
  586. var_gl_id, in_datepaid, coalesce(in_approved, true),
  587. in_op_source[out_count]);
  588. INSERT INTO payment_links
  589. VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 2);
  590. END LOOP;
  591. -- NOW LETS HANDLE THE OVERPAYMENT ACCOUNTS
  592. FOR out_count IN
  593. array_lower(in_op_account_id, 1) ..
  594. array_upper(in_op_account_id, 1)
  595. LOOP
  596. INSERT INTO acc_trans (chart_id, amount,
  597. trans_id, transdate, approved, source, memo)
  598. VALUES (in_op_account_id[out_count],
  599. CASE WHEN in_account_class = 2 THEN in_op_amount[out_count] * -1
  600. ELSE in_op_amount[out_count]
  601. END,
  602. var_gl_id, in_datepaid, coalesce(in_approved, true),
  603. in_op_source[out_count], in_op_memo[out_count]);
  604. INSERT INTO payment_links
  605. VALUES (var_payment_id, currval('acc_trans_entry_id_seq'), 2);
  606. END LOOP;
  607. END IF;
  608. return 0;
  609. END;
  610. $$ LANGUAGE PLPGSQL;
  611. -- I HAVE TO MAKE A COMMENT ON THIS FUNCTION
  612. -- Move this to the projects module when we start on that. CT
  613. CREATE OR REPLACE FUNCTION project_list_open(in_date date)
  614. RETURNS SETOF project AS
  615. $$
  616. DECLARE out_project project%ROWTYPE;
  617. BEGIN
  618. FOR out_project IN
  619. SELECT * from project
  620. WHERE startdate <= in_date AND enddate >= in_date
  621. AND completed = 0
  622. LOOP
  623. return next out_project;
  624. END LOOP;
  625. END;
  626. $$ language plpgsql;
  627. comment on function project_list_open(in_date date) is
  628. $$ This function returns all projects that were open as on the date provided as
  629. the argument.$$;
  630. -- Move this to the projects module when we start on that. CT
  631. CREATE OR REPLACE FUNCTION department_list(in_role char)
  632. RETURNS SETOF department AS
  633. $$
  634. DECLARE out_department department%ROWTYPE;
  635. BEGIN
  636. FOR out_department IN
  637. SELECT * from department
  638. WHERE role = coalesce(in_role, role)
  639. LOOP
  640. return next out_department;
  641. END LOOP;
  642. END;
  643. $$ language plpgsql;
  644. -- Move this into another module.
  645. comment on function department_list(in_role char) is
  646. $$ This function returns all department that match the role provided as
  647. the argument.$$;
  648. CREATE OR REPLACE FUNCTION payments_get_open_currencies(in_account_class int)
  649. RETURNS SETOF char(3) AS
  650. $$
  651. DECLARE resultrow record;
  652. BEGIN
  653. FOR resultrow IN
  654. SELECT DISTINCT curr FROM ar
  655. UNION
  656. SELECT DISTINCT curr FROM ap
  657. ORDER BY curr
  658. LOOP
  659. return next resultrow.curr;
  660. END LOOP;
  661. END;
  662. $$ language plpgsql;
  663. CREATE OR REPLACE FUNCTION currency_get_exchangerate(in_currency char(3), in_date date, in_account_class int)
  664. RETURNS NUMERIC AS
  665. $$
  666. DECLARE
  667. out_exrate exchangerate.buy%TYPE;
  668. BEGIN
  669. IF in_account_class = 1 THEN
  670. SELECT buy INTO out_exrate
  671. FROM exchangerate
  672. WHERE transdate = in_date AND curr = in_currency;
  673. ELSE
  674. SELECT sell INTO out_exrate
  675. FROM exchangerate
  676. WHERE transdate = in_date AND curr = in_currency;
  677. END IF;
  678. RETURN out_exrate;
  679. END;
  680. $$ language plpgsql;
  681. COMMENT ON FUNCTION currency_get_exchangerate(in_currency char(3), in_date date, in_account_class int) IS
  682. $$ This function return the exchange rate of a given currency, date and exchange rate class (buy or sell). $$;
  683. --
  684. -- payment_location_result has the same arch as location_result, except for one field
  685. -- This should be unified on the API when we get things working - David Mora
  686. --
  687. CREATE TYPE payment_location_result AS (
  688. id int,
  689. line_one text,
  690. line_two text,
  691. line_three text,
  692. city text,
  693. state text,
  694. mail_code text,
  695. country text,
  696. class text
  697. );
  698. --
  699. -- payment_get_vc_info has the same arch as company__list_locations, except for the filtering capabilities
  700. -- This should be unified on the API when we get things working - David Mora
  701. --
  702. CREATE OR REPLACE FUNCTION payment_get_vc_info(in_entity_credit_id int, in_location_class_id int)
  703. RETURNS SETOF payment_location_result AS
  704. $$
  705. DECLARE out_row payment_location_result;
  706. BEGIN
  707. FOR out_row IN
  708. SELECT l.id, l.line_one, l.line_two, l.line_three, l.city,
  709. l.state, l.mail_code, c.name, lc.class
  710. FROM location l
  711. JOIN company_to_location ctl ON (ctl.location_id = l.id)
  712. JOIN company cp ON (ctl.company_id = cp.id)
  713. JOIN location_class lc ON (ctl.location_class = lc.id)
  714. JOIN country c ON (c.id = l.country_id)
  715. JOIN entity_credit_account ec ON (ec.entity_id = cp.entity_id)
  716. WHERE ec.id = in_entity_credit_id AND
  717. lc.id = in_location_class_id
  718. ORDER BY lc.id, l.id, c.name
  719. LOOP
  720. RETURN NEXT out_row;
  721. END LOOP;
  722. END;
  723. $$ LANGUAGE PLPGSQL;
  724. COMMENT ON FUNCTION payment_get_vc_info(in_entity_id int, in_location_class_id int) IS
  725. $$ This function returns vendor or customer info $$;
  726. CREATE TYPE payment_record AS (
  727. amount numeric,
  728. meta_number text,
  729. credit_id int,
  730. company_paid text,
  731. accounts text[],
  732. source text,
  733. date_paid date
  734. );
  735. CREATE OR REPLACE FUNCTION payment__search
  736. (in_source text, in_date_from date, in_date_to date, in_credit_id int,
  737. in_cash_accno text, in_account_class int)
  738. RETURNS SETOF payment_record AS
  739. $$
  740. DECLARE
  741. out_row payment_record;
  742. BEGIN
  743. FOR out_row IN
  744. select sum(CASE WHEN c.entity_class = 1 then a.amount
  745. ELSE a.amount * -1 END), c.meta_number,
  746. c.id, co.legal_name,
  747. compound_array(ARRAY[ARRAY[ch.id::text, ch.accno,
  748. ch.description]]), a.source, a.transdate
  749. FROM entity_credit_account c
  750. JOIN ( select entity_credit_account, id
  751. FROM ar WHERE in_account_class = 2
  752. UNION
  753. SELECT entity_credit_account, id
  754. FROM ap WHERE in_account_class = 1
  755. ) arap ON (arap.entity_credit_account = c.id)
  756. JOIN acc_trans a ON (arap.id = a.trans_id)
  757. JOIN chart ch ON (ch.id = a.chart_id)
  758. JOIN company co ON (c.entity_id = co.entity_id)
  759. WHERE (ch.accno = in_cash_accno)
  760. AND (c.id = in_credit_id OR in_credit_id IS NULL)
  761. AND (a.transdate >= in_date_from
  762. OR in_date_from IS NULL)
  763. AND (a.transdate <= in_date_to OR in_date_to IS NULL)
  764. AND (source = in_source OR in_source IS NULL)
  765. GROUP BY c.meta_number, c.id, co.legal_name, a.transdate,
  766. a.source
  767. ORDER BY a.transdate, c.meta_number, a.source
  768. LOOP
  769. RETURN NEXT out_row;
  770. END LOOP;
  771. END;
  772. $$ language plpgsql;
  773. CREATE OR REPLACE FUNCTION payment__reverse
  774. (in_source text, in_date_paid date, in_credit_id int, in_cash_accno text,
  775. in_date_reversed date, in_account_class int, in_batch_id int)
  776. RETURNS INT
  777. AS $$
  778. DECLARE
  779. pay_row record;
  780. t_voucher_id int;
  781. t_voucher_inserted bool;
  782. BEGIN
  783. IF in_batch_id IS NOT NULL THEN
  784. t_voucher_id := nextval('voucher_id_seq');
  785. t_voucher_inserted := FALSE;
  786. END IF;
  787. FOR pay_row IN
  788. SELECT a.*, c.ar_ap_account_id
  789. FROM acc_trans a
  790. JOIN (select id, entity_credit_account
  791. FROM ar WHERE in_account_class = 2
  792. UNION
  793. SELECT id, entity_credit_account
  794. FROM ap WHERE in_account_class = 1
  795. ) arap ON (a.trans_id = arap.id)
  796. JOIN entity_credit_account c
  797. ON (arap.entity_credit_account = c.id)
  798. JOIN chart ch ON (a.chart_id = ch.id)
  799. WHERE coalesce(source, '') = coalesce(in_source, '')
  800. AND transdate = in_date_paid
  801. AND in_credit_id = c.id
  802. AND in_cash_accno = ch.accno
  803. LOOP
  804. IF in_batch_id IS NOT NULL
  805. AND t_voucher_inserted IS NOT TRUE
  806. THEN
  807. INSERT INTO voucher
  808. (id, trans_id, batch_id, batch_class)
  809. VALUES
  810. (t_voucher_id, pay_row.trans_id, in_batch_id,
  811. CASE WHEN in_account_class = 1 THEN 4
  812. WHEN in_account_class = 2 THEN 7
  813. END);
  814. t_voucher_inserted := TRUE;
  815. END IF;
  816. INSERT INTO acc_trans
  817. (trans_id, chart_id, amount, transdate, source, memo, approved,
  818. voucher_id)
  819. VALUES
  820. (pay_row.trans_id, pay_row.chart_id, pay_row.amount * -1,
  821. in_date_reversed, in_source, 'Reversing ' ||
  822. COALESCE(in_source, ''),
  823. case when in_batch_id is not null then false
  824. else true end, t_voucher_id);
  825. INSERT INTO acc_trans
  826. (trans_id, chart_id, amount, transdate, source, memo, approved,
  827. voucher_id)
  828. VALUES
  829. (pay_row.trans_id, pay_row.ar_ap_account_id, pay_row.amount,
  830. in_date_reversed, in_source, 'Reversing ' ||
  831. COALESCE(in_source, ''),
  832. case when in_batch_id is not null then false
  833. else true end, t_voucher_id);
  834. IF in_account_class = 1 THEN
  835. UPDATE ap SET paid = amount -
  836. (SELECT sum(a.amount)
  837. FROM acc_trans a
  838. JOIN chart c ON (a.chart_id = c.id)
  839. WHERE c.link = 'AP'
  840. AND trans_id = pay_row.trans_id
  841. )
  842. WHERE id = pay_row.trans_id;
  843. ELSIF in_account_class = 2 THEN
  844. update ar SET paid = amount -
  845. (SELECT sum(a.amount)
  846. FROM acc_trans a
  847. JOIN chart c ON (a.chart_id = c.id)
  848. WHERE c.link = 'AR'
  849. AND trans_id = pay_row.trans_id
  850. ) * -1
  851. WHERE id = pay_row.trans_id;
  852. ELSE
  853. RAISE EXCEPTION 'Unknown account class for payments %',
  854. in_account_class;
  855. END IF;
  856. END LOOP;
  857. RETURN 1;
  858. END;
  859. $$ LANGUAGE PLPGSQL;