· 3 years ago · Nov 29, 2021, 04:40 AM
1-- modiefied by fredy, 24 March 2015
2-- add document with payment order but not yet cashbank out
3-- into aging AP document
4CREATE OR REPLACE FUNCTION r_aging_ap(character varying, bigint, bigint, bigint, character varying, bigint, bigint, character varying, character varying, character varying, character varying, character varying)
5 RETURNS SETOF refcursor AS
6$BODY$
7DECLARE
8
9 pRefHeader REFCURSOR := 'refHeader';
10 pRefDetail REFCURSOR := 'refDetail';
11 pSessionId ALIAS FOR $1;
12 pTenantId ALIAS FOR $2;
13 pUserId ALIAS FOR $3;
14 pRoleId ALIAS FOR $4;
15 pDatetime ALIAS FOR $5;
16 pOuId ALIAS FOR $6;
17 pPartnerId ALIAS FOR $7; -- bisa all
18 pDueDateType ALIAS FOR $8; -- bisa all
19 pDueDateFrom ALIAS FOR $9;
20 pDueDateTo ALIAS FOR $10;
21 pDateNow ALIAS FOR $11;
22 pCurrCode ALIAS FOR $12;
23
24 vEmptyId bigint := -99;
25 vEmptyIdString character varying := '-99';
26 vFlagYes character varying := 'Y';
27 vFlagNo character varying := 'N';
28 vFlagInprogress character varying := 'I';
29 vDocPaymentOrderAp bigint := 231;
30 vDocPrepayment bigint := 202;
31 vDocPrepaymentInvoice bigint := 203;
32 vDocTypePo bigint := 101;
33
34 vRoundingMode character varying;
35 vDigit integer;
36BEGIN
37
38 SELECT parameter_value INTO vRoundingMode
39 FROM t_system_config A
40 INNER JOIN t_parameter B ON A.parameter_id = B.parameter_id
41 WHERE A.tenant_id = pTenantId
42 AND B.parameter_code = 'rounding.mode.tax';
43
44 SELECT parameter_value::integer INTO vDigit
45 FROM t_system_config A
46 INNER JOIN t_parameter B ON A.parameter_id = B.parameter_id
47 WHERE A.tenant_id = pTenantId
48 AND B.parameter_code = 'rounding.scale.tax';
49
50 -- RAISE WARNING 'sessionId = ' || pSessionId;
51 DELETE FROM tr_aging_ap WHERE session_id = pSessionId;
52
53 /**
54 * ambil finance ap balance yang flg payment <> Y
55 balance = amount - payment amount
56
57 ageDate = tgl system - dueDate > 0
58
59 due date type :
60 NOT YET DUE : tgl system < due_date dokumen
61 OVER DUE : tgl system > due_date dokumen
62 CURRENT : tgl system = due_date
63 */
64
65 INSERT INTO tr_aging_ap(
66 session_id, tenant_id, curr_code, partner_id, partner_code,
67 partner_name, doc_desc, doc_no, doc_date, ext_doc_no,
68 ext_doc_date, due_date, age_date, amount, payment_amount,
69 balance_over_due, balance_current, balance_not_yet_due,
70 balance_over_7_days, balance_over_14_days, balance_over_30_days, balance_over_in_7_days,
71 invoice_ap_balance_id, doc_type_id, ref_id, po_no, over_due )
72 SELECT pSessionId, pTenantId, A.curr_code, A.partner_id, f_get_partner_code(A.partner_id),
73 f_get_partner_name(A.partner_id), f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date, A.ext_doc_no,
74 A.ext_doc_date, A.due_date, 0, A.amount, A.payment_amount,
75 0, 0, 0,
76 0, 0, 0, 0,
77 A.invoice_ap_balance_id, A.doc_type_id, A.invoice_ap_id, B.doc_no, (CURRENT_DATE - CAST(A.due_date AS DATE) )
78 FROM fi_invoice_ap_balance A
79 LEFT JOIN pu_po B ON A.ref_id = B.po_id AND A.ref_doc_type_id = B.doc_type_id AND B.doc_type_id = vDocTypePo
80 WHERE A.flg_payment <> vFlagYes
81 AND A.ou_id = pOuId
82 AND A.tenant_id = pTenantId
83 AND A.ref_doc_type_id NOT IN (vDocPrepayment)
84 AND A.due_date BETWEEN pDueDateFrom AND pDueDateTo
85 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = 1;
86
87 --insert prepayment balance
88 INSERT INTO tr_aging_ap(
89 session_id, tenant_id, curr_code, partner_id, partner_code,
90 partner_name, doc_desc, doc_no, doc_date, ext_doc_no,
91 ext_doc_date, due_date, age_date, amount, payment_amount,
92 balance_over_due, balance_current, balance_not_yet_due,
93 balance_over_7_days, balance_over_14_days, balance_over_30_days, balance_over_in_7_days,
94 invoice_ap_balance_id, doc_type_id, ref_id, po_no, over_due )
95 SELECT pSessionId, pTenantId, A.curr_code, A.partner_id, f_get_partner_code(A.partner_id),
96 f_get_partner_name(A.partner_id), f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date, A.ext_doc_no,
97 A.ext_doc_date, A.due_date, 0, A.amount, A.payment_amount,
98 0, 0, 0,
99 0, 0, 0, 0,
100 A.invoice_ap_balance_id, A.doc_type_id, A.invoice_ap_id, C.doc_no, (CURRENT_DATE - CAST(A.due_date AS DATE) )
101 FROM fi_invoice_ap_balance A
102 LEFT JOIN fi_invoice_ap_balance B ON A.ref_id = B.invoice_ap_balance_id
103 LEFT JOIN pu_po C ON B.ref_id = po_id
104 WHERE A.flg_payment <> vFlagYes
105 AND A.ou_id = pOuId
106 AND A.tenant_id = pTenantId
107 AND A.ref_doc_type_id IN (vDocPrepayment)
108 AND A.due_date BETWEEN pDueDateFrom AND pDueDateTo
109 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = 1;
110
111 INSERT INTO tr_aging_ap(
112 session_id, tenant_id, curr_code, partner_id, partner_code,
113 partner_name, doc_desc, doc_no, doc_date, ext_doc_no,
114 ext_doc_date, due_date, age_date, amount, payment_amount,
115 balance_over_due, balance_current, balance_not_yet_due,
116 balance_over_7_days, balance_over_14_days, balance_over_30_days, balance_over_in_7_days,
117 invoice_ap_balance_id, doc_type_id, ref_id, po_no, over_due )
118 SELECT pSessionId, pTenantId, A.curr_code, A.partner_id, f_get_partner_code(A.partner_id),
119 f_get_partner_name(A.partner_id), f_get_doc_desc(A.doc_type_id), A.tax_no, A.tax_date, ' ',
120 ' ', A.due_date, 0, A.tax_amount, CASE WHEN vRoundingMode = 'RD' THEN TRUNC(A.payment_amount * A.tax_amount / A.gov_tax_amount, vDigit) ELSE ROUND(A.payment_amount * A.tax_amount / A.gov_tax_amount, vDigit) END,
121 0, 0, 0,
122 0, 0, 0, 0,
123 A.invoice_tax_ap_balance_id, A.doc_type_id, A.invoice_ap_balance_id, C.doc_no, (CURRENT_DATE - CAST(A.due_date AS DATE) )
124 FROM fi_invoice_tax_ap_balance A
125 INNER JOIN fi_invoice_ap_balance B ON A.invoice_ap_balance_id = B.invoice_ap_balance_id
126 LEFT JOIN pu_po C ON B.ref_id = C.po_id AND B.ref_doc_type_id = C.doc_type_id AND C.doc_type_id = vDocTypePo
127 WHERE A.flg_payment <> vFlagYes
128 AND A.ou_id = pOuId
129 AND A.tenant_id = pTenantId
130 AND A.due_date BETWEEN pDueDateFrom AND pDueDateTo
131 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = 1
132 AND A.gov_tax_amount <> 0;
133
134 /**
135 * added by fredi, 24 March 2015
136 * 1. ambil balance invoice yang fi_invoice_ap_balance.flg_payment = Y
137 * tetapi, cb_trx_cashbank_balance.flg_payment <> Y
138 * 2. ambil balance tax invoice fi_invoice_tax_ap_balance.flg_payment = Y
139 * tetapi, cb_trx_cashbank_balance.flg_payment <> Y
140 * 3. Update payment in temporary table, return payment amount that not yet cash bank out
141 *
142 * Perubahan 2017-12-29
143 * hanya mengambil data jika cb_trx_cashbank_balance.flg_payment adalah N atau I
144 */
145 INSERT INTO tr_aging_ap(
146 session_id, tenant_id, curr_code, partner_id, partner_code,
147 partner_name, doc_desc, doc_no, doc_date, ext_doc_no,
148 ext_doc_date, due_date, age_date, amount, payment_amount,
149 balance_over_due, balance_current, balance_not_yet_due,
150 balance_over_7_days, balance_over_14_days, balance_over_30_days, balance_over_in_7_days,
151 invoice_ap_balance_id, doc_type_id, ref_id, po_no, over_due)
152 SELECT pSessionId, pTenantId, A.curr_code, A.partner_id, f_get_partner_code(A.partner_id),
153 f_get_partner_name(A.partner_id), f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date, A.ext_doc_no,
154 A.ext_doc_date, A.due_date, 0, A.amount, A.payment_amount,
155 0, 0, 0,
156 0, 0, 0, 0,
157 A.invoice_ap_balance_id, A.doc_type_id, A.invoice_ap_id, B.doc_no, (CURRENT_DATE - CAST(A.due_date AS DATE) )
158 FROM fi_invoice_ap_balance A
159 LEFT JOIN pu_po B ON A.ref_id = B.po_id AND A.ref_doc_type_id = B.doc_type_id AND B.doc_type_id = vDocTypePo
160 WHERE A.flg_payment = vFlagYes
161 AND A.ou_id = pOuId
162 AND A.tenant_id = pTenantId
163 AND A.due_date BETWEEN pDueDateFrom AND pDueDateTo
164 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = 1
165 AND EXISTS (
166 SELECT 1
167 FROM fi_payment_order_invoice B
168 INNER JOIN cb_trx_cashbank_balance C ON B.payment_order_id = C.payment_id
169 AND C.doc_type_id = vDocPaymentOrderAp
170 WHERE A.doc_type_id = B.ref_doc_type_id
171 AND A.invoice_ap_balance_id = B.ref_id
172 AND C.flg_payment IN (vFlagNo, vFlagInprogress)
173 );
174
175 INSERT INTO tr_aging_ap(
176 session_id, tenant_id, curr_code, partner_id, partner_code,
177 partner_name, doc_desc, doc_no, doc_date, ext_doc_no,
178 ext_doc_date, due_date, age_date, amount, payment_amount,
179 balance_over_due, balance_current, balance_not_yet_due,
180 balance_over_7_days, balance_over_14_days, balance_over_30_days, balance_over_in_7_days,
181 invoice_ap_balance_id, doc_type_id, ref_id, po_no, over_due )
182 SELECT pSessionId, pTenantId, A.curr_code, A.partner_id, f_get_partner_code(A.partner_id),
183 f_get_partner_name(A.partner_id), f_get_doc_desc(A.doc_type_id), A.tax_no, A.tax_date, ' ',
184 ' ', A.due_date, 0, A.tax_amount, CASE WHEN vRoundingMode = 'RD' THEN TRUNC(A.payment_amount * A.tax_amount / A.gov_tax_amount, vDigit) ELSE ROUND(A.payment_amount * A.tax_amount / A.gov_tax_amount, vDigit) END,
185 0, 0, 0,
186 0, 0, 0, 0,
187 A.invoice_tax_ap_balance_id, A.doc_type_id, A.invoice_ap_balance_id, C.doc_no , (CURRENT_DATE - CAST(A.due_date AS DATE) )
188 FROM fi_invoice_tax_ap_balance A
189 INNER JOIN fi_invoice_ap_balance B ON A.invoice_ap_balance_id = B.invoice_ap_balance_id
190 LEFT JOIN pu_po C ON B.ref_id = C.po_id AND B.ref_doc_type_id = C.doc_type_id AND C.doc_type_id = vDocTypePo
191 WHERE A.flg_payment = vFlagYes
192 AND A.ou_id = pOuId
193 AND A.tenant_id = pTenantId
194 AND A.due_date BETWEEN pDueDateFrom AND pDueDateTo
195 AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = 1
196 AND A.gov_tax_amount <> 0
197 AND EXISTS (
198 SELECT 1
199 FROM fi_payment_order_invoice B
200 INNER JOIN cb_trx_cashbank_balance C ON B.payment_order_id = C.payment_id
201 AND C.doc_type_id = vDocPaymentOrderAp
202 WHERE A.doc_type_id = B.ref_doc_type_id
203 AND A.invoice_tax_ap_balance_id = B.ref_id
204 AND C.flg_payment IN (vFlagNo, vFlagInprogress)
205 );
206
207 -- * 3. Update payment in temporary table, return payment amount that not yet cash bank out
208 WITH os_payment_order AS (
209 SELECT SUM(B.credit_amount - B.debit_amount) AS credit_amount,
210 A.invoice_ap_balance_id, A.doc_type_id, A.ref_id
211 FROM tr_aging_ap A
212 INNER JOIN fi_payment_order_invoice B ON A.invoice_ap_balance_id = B.ref_id
213 AND A.doc_type_id = B.ref_doc_type_id
214 INNER JOIN cb_trx_cashbank_balance C ON B.payment_order_id = C.payment_id
215 AND C.doc_type_id = vDocPaymentOrderAp
216 WHERE A.session_id = pSessionId
217 AND C.flg_payment IN (vFlagNo, vFlagInprogress)
218 GROUP BY A.invoice_ap_balance_id, A.doc_type_id, A.ref_id
219 )
220 UPDATE tr_aging_ap
221 SET payment_amount = payment_amount - A.credit_amount
222 FROM os_payment_order A
223 WHERE tr_aging_ap.session_id = pSessionId
224 AND A.invoice_ap_balance_id = tr_aging_ap.invoice_ap_balance_id
225 AND A.doc_type_id = tr_aging_ap.doc_type_id;
226
227 UPDATE tr_aging_ap
228 SET age_date = DATE_PART('day', to_timestamp(pDateNow, 'YYYYMMDD') - to_timestamp(due_date, 'YYYYMMDD'))
229 WHERE session_id = pSessionId;
230
231 UPDATE tr_aging_ap
232 SET balance_over_due = amount - payment_amount
233 WHERE session_id = pSessionId
234 AND age_date > 0;
235
236 UPDATE tr_aging_ap
237 SET balance_over_in_7_days = amount - payment_amount
238 WHERE session_id = pSessionId
239 AND age_date > 0 AND age_date <= 7;
240
241 UPDATE tr_aging_ap
242 SET balance_over_7_days = amount - payment_amount
243 WHERE session_id = pSessionId
244 AND age_date > 7 AND age_date <= 14;
245
246 UPDATE tr_aging_ap
247 SET balance_over_14_days = amount - payment_amount
248 WHERE session_id = pSessionId
249 AND age_date > 14 AND age_date <= 30;
250
251 UPDATE tr_aging_ap
252 SET balance_over_30_days = amount - payment_amount
253 WHERE session_id = pSessionId
254 AND age_date > 30;
255
256 UPDATE tr_aging_ap
257 SET balance_current = amount - payment_amount
258 WHERE session_id = pSessionId
259 AND age_date = 0;
260
261 UPDATE tr_aging_ap
262 SET balance_not_yet_due = amount - payment_amount
263 WHERE session_id = pSessionId
264 AND age_date < 0;
265
266 IF pPartnerId <> vEmptyId THEN
267 DELETE FROM tr_aging_ap A
268 WHERE A.session_id = pSessionId AND A.partner_id <> pPartnerId;
269 END IF;
270
271 IF pCurrCode <> vEmptyIdString THEN
272 DELETE FROM tr_aging_ap A
273 WHERE A.session_id = pSessionId AND A.curr_code <> pCurrCode;
274 END IF;
275
276 Open pRefHeader FOR
277 SELECT f_get_ou_name(pOuId) AS ou_name, f_get_username(pUserId) AS username, pDatetime AS datetime;
278 RETURN NEXT pRefHeader;
279
280 Open pRefDetail FOR
281 SELECT A.doc_desc AS doc_type, A.doc_no AS doc_no, A.doc_date AS doc_date, A.due_date AS due_date,
282 A.partner_code AS partner_code, A.partner_name AS partner_name,
283 A.curr_code AS curr_code, SUM(A.amount - A.payment_amount) AS amount,
284 SUM(A.balance_not_yet_due) AS balance_not_yet_due,
285 SUM(A.balance_current) AS balance_current,
286 SUM(A.balance_over_7_days) AS balance_over_7_days,
287 SUM(A.balance_over_14_days) AS balance_over_14_days,
288 SUM(A.balance_over_30_days) AS balance_over_30_days,
289 SUM(A.balance_over_in_7_days) AS balance_over_in_7_days,
290 A.po_no AS po_no, A.over_due AS over_due
291 FROM tr_aging_ap A
292 WHERE A.session_id = pSessionId
293 GROUP BY A.doc_desc, A.doc_no, A.doc_date, A.curr_code, A.due_date, A.partner_code, A.partner_name, A.po_no, A.over_due
294 ORDER BY A.curr_code, A.partner_name, A.due_date, A.doc_date, A.doc_no;
295
296 RETURN NEXT pRefDetail;
297
298 DELETE FROM tr_aging_ap WHERE session_id = pSessionId;
299END;
300$BODY$
301 LANGUAGE plpgsql VOLATILE
302 COST 100
303 ROWS 1000;
304/