Evra70

a

Nov 28th, 2021
765
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. -- modiefied by fredy, 24 March 2015
  2. -- add document with payment order but not yet cashbank out
  3. -- into aging AP document
  4. CREATE 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$
  7. DECLARE
  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;
  36. BEGIN
  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;
  299. END;
  300. $BODY$
  301.   LANGUAGE plpgsql VOLATILE
  302.   COST 100
  303.   ROWS 1000;
  304. /
RAW Paste Data