dchrissandy

Untitled

Jan 20th, 2021
1,854
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION fi_process_alloc_cn_ar(
  2.     BIGINT,
  3.     CHARACTER VARYING,
  4.     BIGINT,
  5.     BIGINT,
  6.     CHARACTER VARYING)
  7.   RETURNS void AS
  8. $BODY$
  9. DECLARE
  10.    
  11.     pTenantId                   alias FOR $1;
  12.     pSessionId                  alias FOR $2;
  13.     pOuId                       alias FOR $3;
  14.     pUserId                     alias FOR $4;
  15.     pDatetime                   alias FOR $5;
  16.    
  17.     vYes                        CHARACTER VARYING(1);
  18.     vNo                         CHARACTER VARYING(1);
  19.     vInprogress                 CHARACTER VARYING(1);
  20.     vAlloc                      CHARACTER VARYING(1);
  21.     vEmpty                      CHARACTER VARYING(1);
  22.     vCurrCode                   CHARACTER VARYING(10);
  23.    
  24.     vDocTypeIdCreditAllocAr     BIGINT;
  25.     vStartCount                 BIGINT;
  26.     vSummaryCount               BIGINT:=0;
  27.     vCountDn                    BIGINT;
  28.     vPartnerId                  BIGINT;
  29.     vCount                      BIGINT;
  30.     vNull                       BIGINT;
  31.     vDnArId                     BIGINT;
  32.     vDnDocTypeId                BIGINT;
  33.     vCursorSummaryCnAr          tt_summary_cn_ar%ROWTYPE;
  34.     vDnAmount                   NUMERIC;
  35.     vPeriod                     CHARACTER VARYING(20);
  36.     vSchemeAlloc                CHARACTER VARYING(10);
  37.     vFlowAlloc                  CHARACTER VARYING;
  38.     vProcessName                CHARACTER VARYING;
  39.     vParamKey                   CHARACTER VARYING;
  40.     vWorkflowApproved           CHARACTER VARYING;
  41. BEGIN
  42.  
  43.     vDocTypeIdCreditAllocAr :=262;
  44.    
  45.     vNo                         :='N';
  46.     vYes                        :='Y';
  47.     vInprogress                 :='I';
  48.     vNull                       :=-99;
  49.     vAlloc                      :='A';
  50.     vEmpty                      :=' ';
  51.     vSchemeAlloc                := 'DH02';
  52.     vProcessName                := 'fi_submit_alloc_credit_ar';
  53.     vParamKey                   := 'allocCreditArId';
  54.     vWorkflowApproved           := 'APPROVED';
  55.    
  56.     SELECT LEFT(pDatetime,8) INTO vPeriod;
  57.    
  58.     -- memberikan tanda untuk dokumen pengurang piutang yang tidak memiliki dokumen penambah piutang, jika ada maka hapus data dokumen pengurang piutang tersebut, agar tidak dipakai diproses berikutnya
  59.     -- karena hanya akan membentuk 1 dokumen alokasi tiap kali function ini dijalankan maka allocation ar id bisa i set ke variale terlebih dahulu
  60.     UPDATE tt_summary_cn_ar Z
  61.         SET flg_payment = vYes
  62.             --, allocation_ar_id = nextval('fi_allocation_ar_seq')
  63.     FROM tt_dn_ar A
  64.     WHERE A.session_id = pSessionId
  65.     AND A.partner_id = Z.partner_id
  66.     AND A.ou_id = Z.ou_id
  67.     AND A.tenant_id = Z.tenant_id
  68.     AND Z.session_id = pSessionId;
  69.  
  70.     -- lakukan multi alokasi credit ar per pelanggan per valuta, dan lakukan pencatatan dokumen credit ar yang digunakan untuk melunasi dokumen debit ar per masing-masing dokumen, data alokasi dibuat seperti data fi_allocation_ar_balance, tolong perhatikan nilai line no di debit ar nya.
  71.         -- lihat function fi_submit_payment_order_ap line 147
  72.         -- count yang di process yang Yes di tt_summary_cn_ar
  73.     SELECT COUNT(1) INTO vSummaryCount
  74.     FROM tt_summary_cn_ar
  75.     WHERE session_id = pSessionId
  76.     AND flg_payment = vYes;
  77.  
  78.     RAISE NOTICE '% vSummaryCount', vSummaryCount;
  79.    
  80.     -- bikin cursor ambil dari table tt_summary_cn_ar looping terhadap partner_id order by curr_code, partner_id
  81.     IF vSummaryCount > 0 THEN
  82.         FOR vCursorSummaryCnAr IN
  83.             SELECT *
  84.             FROM tt_summary_cn_ar
  85.             WHERE session_id = pSessionId
  86.             AND flg_payment = vYes
  87.             ORDER BY curr_code,partner_id
  88.         LOOP
  89.             -- masuk ke loop ambil partner_id dan curr_code
  90.             vPartnerId := vCursorSummaryCnAr.partner_id;
  91.             vCurrCode := vCursorSummaryCnAr.curr_code;
  92.  
  93.             -- count jumlah doc no untuk partner_id dan curr_code (jumlah document) untuk DN
  94.             SELECT COUNT(1) INTO vCountDn
  95.             FROM tt_dn_ar
  96.             WHERE session_id = pSessionId
  97.             AND partner_id = vPartnerId
  98.             AND curr_code = vCurrCode;
  99.  
  100.             -- bikin variable vStartCount = 1
  101.             vStartCount :=1;
  102.  
  103.             -- FOR i IN vStartCount.. jumlah document LOOP (masuk ke loop baru)
  104.             FOR i IN vStartCount..vCountDn LOOP
  105.  
  106.                 -- Loop DN, ambil nilai saldo awal-payment
  107.                 SELECT (amount-allocation_amount),dn_ar_id,doc_type_id INTO vDnAmount,vDnArId,vDnDocTypeId
  108.                 FROM tt_dn_ar
  109.                 WHERE line_no = i
  110.                 AND session_id =pSessionId
  111.                 AND partner_id = vPartnerId
  112.                 AND curr_code = vCurrCode;
  113.  
  114.                 -- Update Running Total table CN
  115.                 WITH runningAmount AS(
  116.                     SELECT doc_no, SUM(amount-allocation_amount) OVER (ORDER BY doc_date,doc_no) AS running_amount
  117.                     FROM   tt_cn_ar
  118.                     WHERE session_id = pSessionId
  119.                     AND partner_id = vPartnerId
  120.                     AND curr_code = vCurrCode
  121.                     ORDER  BY doc_date,doc_no
  122.                     )
  123.                 UPDATE tt_cn_ar Z
  124.                 SET running_amount = A.running_amount
  125.                 FROM runningAmount A
  126.                 WHERE Z.doc_no = A.doc_no
  127.                 AND Z.session_id = pSessionId;
  128.  
  129.                 IF (SELECT COUNT(1) >0
  130.                     FROM tt_cn_ar
  131.                     WHERE running_amount >= vDnAmount
  132.                     AND session_id = pSessionId
  133.                     AND partner_id = vPartnerId
  134.                     AND curr_code = vCurrCode
  135.                     ) THEN
  136.  
  137.                     -- Get Count min runing total CN >= nilai DN
  138.                     SELECT MIN(line_no) INTO vCount
  139.                     FROM tt_cn_ar
  140.                     WHERE running_amount >= vDnAmount
  141.                     AND session_id = pSessionId
  142.                     AND partner_id = vPartnerId
  143.                     AND curr_code = vCurrCode;
  144.                    
  145.                 ELSE
  146.    
  147.                     -- Get Count max running total CN < nilai DN
  148.                         SELECT MAX(line_no) INTO vCount
  149.                         FROM tt_cn_ar
  150.                         WHERE running_amount < vDnAmount
  151.                         AND session_id = pSessionId
  152.                         AND partner_id = vPartnerId
  153.                         AND curr_code = vCurrCode
  154.                         AND running_amount <> 0;
  155.                
  156.                 END IF;
  157.                
  158.                 RAISE NOTICE '% vCount', vCount;
  159.  
  160.                 -- Buat table untuk allocate
  161.                 -- nilai CN Amount didapat dari saldo awal - payment yang >0
  162.                 -- running amount dari nilai running total tanpa di hitung ulang
  163.  
  164.                 INSERT INTO tt_allocation(
  165.                     session_id,cn_ar_id,doc_type_id,cn_amount,alloc_amount,running_amount,line_no
  166.                     )
  167.                 SELECT session_id,cn_ar_id,doc_type_id,(amount-allocation_amount),0,running_amount,line_no
  168.                 FROM tt_cn_ar
  169.                 WHERE session_id = pSessionId
  170.                 AND partner_id =vPartnerId
  171.                 AND curr_code = vCurrCode
  172.                 AND line_no <= vCount
  173.                 AND (amount-allocation_amount)>0;
  174.  
  175.                 IF (
  176.                         SELECT (running_amount - vDnAmount)>=0
  177.                         FROM tt_allocation
  178.                         WHERE session_id = pSessionId
  179.                         AND line_no = vCount
  180.                     ) THEN
  181.                 -- Update untuk nilai allocate amount paling terakhir
  182.                 UPDATE tt_allocation Z
  183.                     SET alloc_amount = abs(Z.cn_amount) - abs(running_amount - vDnAmount)
  184.                 WHERE session_id = pSessionId
  185.                 AND line_no = vCount;
  186.                
  187.                 ELSE
  188.                
  189.                 UPDATE tt_allocation Z
  190.                     SET alloc_amount = abs(Z.cn_amount)
  191.                 WHERE session_id = pSessionId
  192.                 AND line_no = vCount;
  193.                
  194.                
  195.                 END IF;
  196.  
  197.                 -- Update nilai allocate amount yang masih 0 sesuai dengan nilai CN amount
  198.                 UPDATE tt_allocation Z
  199.                     SET alloc_amount = abs(Z.cn_amount)
  200.                 WHERE session_id = pSessionId
  201.                 AND line_no < vCount
  202.                 AND alloc_amount = 0;
  203.  
  204.                 -- Update table DN sesuai dengan table alocation
  205.                 WITH tt_allocation AS(
  206.                         SELECT SUM(alloc_amount) AS alloc_amount,session_id
  207.                         FROM tt_allocation
  208.                         WHERE session_id = pSessionId
  209.                         GROUP BY session_id
  210.                     )
  211.                 UPDATE tt_dn_ar Z
  212.                     SET allocation_amount = Z.allocation_amount+A.alloc_amount
  213.                 FROM tt_allocation A
  214.                 WHERE A.session_id = Z.session_id
  215.                 AND A.session_id=pSessionId
  216.                 AND Z.partner_id = vPartnerId
  217.                 AND Z.dn_ar_id = vDnArId
  218.                 AND Z.doc_type_id = vDnDocTypeId;
  219.  
  220.                 -- join berdasarkan ID dan doc type id
  221.                 -- Update table CN sesuai dengan nilai di table alocation
  222.                 UPDATE tt_cn_ar Z
  223.                     SET allocation_amount = Z.allocation_amount+A.alloc_amount
  224.                 FROM tt_allocation A
  225.                 WHERE A.doc_type_id = Z.doc_type_id
  226.                 AND A.cn_ar_id = Z.cn_ar_id
  227.                 AND A.session_id = Z.session_id
  228.                 AND A.session_id = pSessionId
  229.                 AND Z.session_id = pSessionId
  230.                 AND Z.partner_id = vPartnerId;
  231.  
  232.                 -- Insert Table mapping(yang sesuai dengan fi allocation ar balance) lihat dari table alocation
  233.                 INSERT INTO tt_mapping_alloc_ar(
  234.                     session_id,--allocation_ar_id,
  235.                     tenant_id,ou_id,
  236.                     credit_doc_type_id,credit_doc_no,credit_doc_date,credit_id,credit_curr_code,credit_amount,
  237.                     debit_doc_type_id,debit_doc_no,debit_doc_date,debit_ext_doc_no,debit_ext_doc_date,debit_due_date,debit_id,debit_curr_code,debit_amount,
  238.                     flg_receipt,ref_receipt_id,
  239.                     version,create_datetime,create_user_id,update_datetime,update_user_id,flg_alloc,ref_alloc_id,ref_doc_type_id,
  240.                     ref_doc_no,ref_doc_date,partner_id
  241.                     )
  242.                 SELECT pSessionId, --nextval('fi_allocation_ar_seq'), --D.allocation_ar_id,
  243.                         A.tenant_id,pOuId,
  244.                         A.doc_type_id,A.doc_no,A.doc_date,A.cn_ar_id,A.curr_code,C.alloc_amount,
  245.                         B.doc_type_id,B.doc_no,B.doc_date,B.ext_doc_no,B.ext_doc_date,B.due_date,B.dn_ar_id,B.curr_code,C.alloc_amount,
  246.                         vNo,vNull,
  247.                         0,pDatetime,pUserId,pDatetime,pUserId,vAlloc,vNull,vNull,
  248.                         vEmpty,vEmpty,A.partner_id
  249.                 FROM tt_cn_ar A
  250.                 JOIN tt_dn_ar B ON A.partner_id = B.partner_id AND A.session_id = B.session_id AND A.curr_code = B.curr_code
  251.                 JOIN tt_allocation C ON A.doc_type_id = C.doc_type_id AND A.session_id = C.session_id AND A.cn_ar_id = C.cn_ar_id
  252. --                JOIN tt_summary_cn_ar D ON A.partner_id = D.partner_id AND A.session_id = D.session_id AND A.curr_code = D.curr_code
  253.                 WHERE A.session_id = pSessionId
  254.                 AND B.dn_ar_id = vDnArId
  255.                 AND B.doc_type_id = vDnDocTypeId;
  256. --                AND D.flg_payment = vYes;
  257.  
  258.                 -- Truncate table alocation
  259.                 TRUNCATE TABLE tt_allocation;
  260.  
  261.             END LOOP;
  262.  
  263.         END LOOP;
  264.        
  265.         -- UPDATE allocation_ar_id PADA tt_mapping_alloc_ar
  266.         WITH alloc_ar_list AS (
  267.             SELECT  A.session_id, A.credit_id, A.credit_doc_type_id, NEXTVAL('fi_allocation_ar_seq') AS alloc_ar_id
  268.             FROM tt_mapping_alloc_ar A
  269.             WHERE session_id = pSessionId
  270.             GROUP BY A.session_id, A.credit_id, A.credit_doc_type_id
  271.         )
  272.         UPDATE tt_mapping_alloc_ar A
  273.         SET allocation_ar_id = B.alloc_ar_id
  274.         FROM alloc_ar_list B
  275.         WHERE A.session_id = B.session_id AND A.credit_id = B.credit_id AND A.credit_doc_type_id = B.credit_doc_type_id;
  276.        
  277.         -- insert ke table asli
  278.         INSERT INTO fi_allocation_ar(
  279.             allocation_ar_id,tenant_id,doc_type_id,doc_no,doc_date,ou_id,partner_id,
  280.             partner_bank_id,ext_doc_no,ext_doc_date,ref_doc_type_id,ref_id,due_date,
  281.             curr_code,debit_amount,credit_amount,payment_amount,remark,status_doc,
  282.             workflow_status,version,create_datetime,create_user_id,update_datetime,update_user_id,
  283.             ref_amount,flg_automatic_credit_note_ar
  284.             )
  285.         SELECT A.allocation_ar_id,A.tenant_id,vDocTypeIdCreditAllocAr,A.credit_doc_no,A.credit_doc_date,pOuId,A.partner_id,
  286.             vNull,vEmpty,vEmpty,A.credit_doc_type_id,A.credit_id,A.credit_doc_date,
  287.             A.credit_curr_code,SUM(A.debit_amount),SUM(A.credit_amount),0,'Auto Allocation Credit AR : '||B.partner_name||' - '||A.credit_curr_code||' - '||vPeriod,'R',
  288.             'APPROVED',0,pDatetime,pUserId,pDatetime,pUserId,
  289.             SUM(A.credit_amount),vYes
  290.         FROM tt_mapping_alloc_ar A
  291.         JOIN m_partner B ON A.partner_id = B.partner_id
  292.         WHERE A.session_id = pSessionId
  293.         GROUP BY A.allocation_ar_id,A.tenant_id,A.credit_curr_code,A.partner_id,A.credit_doc_no,A.credit_doc_date,
  294.                 B.partner_name, A.credit_id, A.credit_doc_type_id;
  295.  
  296.         INSERT INTO fi_allocation_ar_invoice(
  297.             tenant_id, allocation_ar_id, ref_doc_type_id,
  298.             ref_id, curr_code, debit_amount, credit_amount, payment_amount,
  299.             version, create_datetime, create_user_id, update_datetime, update_user_id,
  300.             ref_remain_amount)
  301.         SELECT A.tenant_id,A.allocation_ar_id, A.debit_doc_type_id,
  302.             A.debit_id, A.debit_curr_code, SUM(A.debit_amount),0,SUM(A.debit_amount),
  303.             0, pDatetime,pUserId, pDatetime,pUserId,
  304.             B.amount
  305.         FROM tt_mapping_alloc_ar A
  306.         INNER JOIN tt_dn_ar B ON B.session_id = A.session_id AND B.dn_ar_id = A.debit_id
  307.         WHERE A.session_id = pSessionId
  308.         GROUP BY A.tenant_id,A.allocation_ar_id, A.debit_doc_type_id,
  309.             A.debit_id, A.debit_curr_code, B.amount;
  310.  
  311.         UPDATE fi_invoice_ar_balance Z
  312.         SET flg_payment = vInprogress
  313.         FROM tt_cn_ar A
  314.         WHERE A.session_id = pSessionId
  315.         AND A.cn_ar_id = Z.invoice_ar_balance_id
  316.         AND A.doc_type_id = Z.doc_type_id;
  317.        
  318.         UPDATE fi_invoice_ar_balance Z
  319.         SET flg_payment = vInprogress
  320.         FROM tt_dn_ar A
  321.         WHERE A.session_id = pSessionId
  322.         AND A.dn_ar_id = Z.invoice_ar_balance_id
  323.         AND A.doc_type_id = Z.doc_type_id;
  324.  
  325.         UPDATE fi_invoice_tax_ar_balance Z
  326.         SET flg_payment = vInprogress
  327.         FROM tt_cn_ar A
  328.         WHERE A.session_id = pSessionId
  329.         AND A.cn_ar_id = Z.invoice_tax_ar_balance_id
  330.         AND A.doc_type_id = Z.doc_type_id;
  331.        
  332.        
  333.         UPDATE fi_invoice_tax_ar_balance Z
  334.         SET flg_payment = vInprogress
  335.         FROM tt_dn_ar A
  336.         WHERE A.session_id = pSessionId
  337.         AND A.dn_ar_id = Z.invoice_tax_ar_balance_id
  338.         AND A.doc_type_id = Z.doc_type_id;  
  339.  
  340.         --insert process message
  341.         PERFORM generate_process_message_for_submit_doc(pSessionId, pTenantId, vProcessName,
  342.             A.allocation_ar_id ||'_'||A.credit_doc_no, pDatetime, vParamKey, A.allocation_ar_id::CHARACTER VARYING, pUserId)
  343.         FROM tt_mapping_alloc_ar A
  344.         WHERE A.session_id = pSessionId
  345.         GROUP BY A.allocation_ar_id, A.credit_doc_no;
  346.          
  347.         -- Mendapatkan default approval flow ID yang dipakai dari sysconfig
  348.         SELECT awe_flow_id INTO vFlowAlloc
  349.         FROM awe_flow
  350.         WHERE scheme = vSchemeAlloc
  351.         AND flg_validate = vYes
  352.         AND active = vYes;
  353.        
  354.         -- Generate data awe_currdoc_status
  355.         INSERT INTO awe_currdoc_status(
  356.             req_id, tenant_id, scheme, doc_id, doc_no, doc_date,
  357.             current_state, remark, current_user_id, current_role_id, flg_user_role, label,
  358.             DATA, flow_id, create_datetime, create_user_id, create_role_id,
  359.             update_datetime, update_user_id, update_role_id, version)
  360.         SELECT A.allocation_ar_id||'_'||A.credit_doc_no, A.tenant_id, vSchemeAlloc, A.allocation_ar_id, A.credit_doc_no, vPeriod,
  361.             vWorkflowApproved, B.remark, pUserId, -1, 'U', 'ALLOCATION CN'||A.credit_doc_no,
  362.             '{}', vFlowAlloc, pDatetime, pUserId, -1,
  363.             pDatetime, pUserId, -1, 0
  364.         FROM tt_mapping_alloc_ar A
  365.         INNER JOIN fi_allocation_ar B ON B.allocation_ar_id = A.allocation_ar_id
  366.         WHERE A.session_id = pSessionId
  367.         GROUP BY A.allocation_ar_id, A.credit_doc_no, A.tenant_id, B.remark;
  368.            
  369.     END IF;
  370.  
  371. END;
  372. $BODY$
  373.   LANGUAGE plpgsql VOLATILE
  374.   COST 100;
RAW Paste Data