CREATE OR REPLACE FUNCTION fi_process_alloc_cn_ar(
    BIGINT,
    CHARACTER VARYING,
    BIGINT,
    BIGINT,
    CHARACTER VARYING)
  RETURNS void AS
$BODY$
DECLARE

    pTenantId                   alias FOR $1;
    pSessionId                  alias FOR $2;
    pOuId                       alias FOR $3;
    pUserId                     alias FOR $4;
    pDatetime                   alias FOR $5;

    vYes                        CHARACTER VARYING(1);
    vNo                         CHARACTER VARYING(1);
    vInprogress                 CHARACTER VARYING(1);
    vAlloc                      CHARACTER VARYING(1);
    vEmpty                      CHARACTER VARYING(1);
    vCurrCode                   CHARACTER VARYING(10);

    vDocTypeIdCreditAllocAr     BIGINT;
    vStartCount                 BIGINT;
    vSummaryCount               BIGINT:=0;
    vCountDn                    BIGINT;
    vPartnerId                  BIGINT;
    vCount                      BIGINT;
    vNull                       BIGINT;
    vDnArId                     BIGINT;
    vDnDocTypeId                BIGINT;
    vCursorSummaryCnAr          tt_summary_cn_ar%ROWTYPE;
    vDnAmount                   NUMERIC;
    vPeriod                     CHARACTER VARYING(20);
    vSchemeAlloc                CHARACTER VARYING(10);
    vFlowAlloc                  CHARACTER VARYING;
    vProcessName                CHARACTER VARYING;
    vParamKey                   CHARACTER VARYING;
    vWorkflowApproved           CHARACTER VARYING;
BEGIN

    vDocTypeIdCreditAllocAr :=262;

    vNo                         :='N';
    vYes                        :='Y';
    vInprogress                 :='I';
    vNull                       :=-99;
    vAlloc                      :='A';
    vEmpty                      :=' ';
    vSchemeAlloc                := 'DH02';
    vProcessName                := 'fi_submit_alloc_credit_ar';
    vParamKey                   := 'allocCreditArId';
    vWorkflowApproved           := 'APPROVED';

    SELECT LEFT(pDatetime,8) INTO vPeriod;

    -- 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
    -- karena hanya akan membentuk 1 dokumen alokasi tiap kali function ini dijalankan maka allocation ar id bisa i set ke variale terlebih dahulu
    UPDATE tt_summary_cn_ar Z
        SET flg_payment = vYes
            --, allocation_ar_id = nextval('fi_allocation_ar_seq')
    FROM tt_dn_ar A
    WHERE A.session_id = pSessionId
    AND A.partner_id = Z.partner_id
    AND A.ou_id = Z.ou_id
    AND A.tenant_id = Z.tenant_id
    AND Z.session_id = pSessionId;

    -- 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.
        -- lihat function fi_submit_payment_order_ap line 147
        -- count yang di process yang Yes di tt_summary_cn_ar
    SELECT COUNT(1) INTO vSummaryCount
    FROM tt_summary_cn_ar
    WHERE session_id = pSessionId
    AND flg_payment = vYes;

    RAISE NOTICE '% vSummaryCount', vSummaryCount;

    -- bikin cursor ambil dari table tt_summary_cn_ar looping terhadap partner_id order by curr_code, partner_id
    IF vSummaryCount > 0 THEN
        FOR vCursorSummaryCnAr IN
            SELECT *
            FROM tt_summary_cn_ar
            WHERE session_id = pSessionId
            AND flg_payment = vYes
            ORDER BY curr_code,partner_id
        LOOP
            -- masuk ke loop ambil partner_id dan curr_code
            vPartnerId := vCursorSummaryCnAr.partner_id;
            vCurrCode := vCursorSummaryCnAr.curr_code;

            -- count jumlah doc no untuk partner_id dan curr_code (jumlah document) untuk DN
            SELECT COUNT(1) INTO vCountDn
            FROM tt_dn_ar
            WHERE session_id = pSessionId
            AND partner_id = vPartnerId
            AND curr_code = vCurrCode;

            -- bikin variable vStartCount = 1
            vStartCount :=1;

            -- FOR i IN vStartCount.. jumlah document LOOP (masuk ke loop baru)
            FOR i IN vStartCount..vCountDn LOOP

                -- Loop DN, ambil nilai saldo awal-payment
                SELECT (amount-allocation_amount),dn_ar_id,doc_type_id INTO vDnAmount,vDnArId,vDnDocTypeId
                FROM tt_dn_ar
                WHERE line_no = i
                AND session_id =pSessionId
                AND partner_id = vPartnerId
                AND curr_code = vCurrCode;

                -- Update Running Total table CN
                WITH runningAmount AS(
                    SELECT doc_no, SUM(amount-allocation_amount) OVER (ORDER BY doc_date,doc_no) AS running_amount
                    FROM   tt_cn_ar
                    WHERE session_id = pSessionId
                    AND partner_id = vPartnerId
                    AND curr_code = vCurrCode
                    ORDER  BY doc_date,doc_no
                    )
                UPDATE tt_cn_ar Z
                SET running_amount = A.running_amount
                FROM runningAmount A
                WHERE Z.doc_no = A.doc_no
                AND Z.session_id = pSessionId;

                IF (SELECT COUNT(1) >0
                    FROM tt_cn_ar
                    WHERE running_amount >= vDnAmount
                    AND session_id = pSessionId
                    AND partner_id = vPartnerId
                    AND curr_code = vCurrCode
                    ) THEN

                    -- Get Count min runing total CN >= nilai DN
                    SELECT MIN(line_no) INTO vCount
                    FROM tt_cn_ar
                    WHERE running_amount >= vDnAmount
                    AND session_id = pSessionId
                    AND partner_id = vPartnerId
                    AND curr_code = vCurrCode;

                ELSE

                    -- Get Count max running total CN < nilai DN
                        SELECT MAX(line_no) INTO vCount
                        FROM tt_cn_ar
                        WHERE running_amount < vDnAmount
                        AND session_id = pSessionId
                        AND partner_id = vPartnerId
                        AND curr_code = vCurrCode
                        AND running_amount <> 0;

                END IF;

                RAISE NOTICE '% vCount', vCount;

                -- Buat table untuk allocate
                -- nilai CN Amount didapat dari saldo awal - payment yang >0
                -- running amount dari nilai running total tanpa di hitung ulang

                INSERT INTO tt_allocation(
                    session_id,cn_ar_id,doc_type_id,cn_amount,alloc_amount,running_amount,line_no
                    )
                SELECT session_id,cn_ar_id,doc_type_id,(amount-allocation_amount),0,running_amount,line_no
                FROM tt_cn_ar
                WHERE session_id = pSessionId
                AND partner_id =vPartnerId
                AND curr_code = vCurrCode
                AND line_no <= vCount
                AND (amount-allocation_amount)>0;

                IF (
                        SELECT (running_amount - vDnAmount)>=0
                        FROM tt_allocation
                        WHERE session_id = pSessionId
                        AND line_no = vCount
                    ) THEN
                -- Update untuk nilai allocate amount paling terakhir
                UPDATE tt_allocation Z
                    SET alloc_amount = abs(Z.cn_amount) - abs(running_amount - vDnAmount)
                WHERE session_id = pSessionId
                AND line_no = vCount;

                ELSE

                UPDATE tt_allocation Z
                    SET alloc_amount = abs(Z.cn_amount)
                WHERE session_id = pSessionId
                AND line_no = vCount;


                END IF;

                -- Update nilai allocate amount yang masih 0 sesuai dengan nilai CN amount
                UPDATE tt_allocation Z
                    SET alloc_amount = abs(Z.cn_amount)
                WHERE session_id = pSessionId
                AND line_no < vCount
                AND alloc_amount = 0;

                -- Update table DN sesuai dengan table alocation
                WITH tt_allocation AS(
                        SELECT SUM(alloc_amount) AS alloc_amount,session_id
                        FROM tt_allocation
                        WHERE session_id = pSessionId
                        GROUP BY session_id
                    )
                UPDATE tt_dn_ar Z
                    SET allocation_amount = Z.allocation_amount+A.alloc_amount
                FROM tt_allocation A
                WHERE A.session_id = Z.session_id
                AND A.session_id=pSessionId
                AND Z.partner_id = vPartnerId
                AND Z.dn_ar_id = vDnArId
                AND Z.doc_type_id = vDnDocTypeId;

                -- join berdasarkan ID dan doc type id
                -- Update table CN sesuai dengan nilai di table alocation
                UPDATE tt_cn_ar Z
                    SET allocation_amount = Z.allocation_amount+A.alloc_amount
                FROM tt_allocation A
                WHERE A.doc_type_id = Z.doc_type_id
                AND A.cn_ar_id = Z.cn_ar_id
                AND A.session_id = Z.session_id
                AND A.session_id = pSessionId
                AND Z.session_id = pSessionId
                AND Z.partner_id = vPartnerId;

                -- Insert Table mapping(yang sesuai dengan fi allocation ar balance) lihat dari table alocation
                INSERT INTO tt_mapping_alloc_ar(
                    session_id,--allocation_ar_id,
                    tenant_id,ou_id,
                    credit_doc_type_id,credit_doc_no,credit_doc_date,credit_id,credit_curr_code,credit_amount,
                    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,
                    flg_receipt,ref_receipt_id,
                    version,create_datetime,create_user_id,update_datetime,update_user_id,flg_alloc,ref_alloc_id,ref_doc_type_id,
                    ref_doc_no,ref_doc_date,partner_id
                    )
                SELECT pSessionId, --nextval('fi_allocation_ar_seq'), --D.allocation_ar_id,
                        A.tenant_id,pOuId,
                        A.doc_type_id,A.doc_no,A.doc_date,A.cn_ar_id,A.curr_code,C.alloc_amount,
                        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,
                        vNo,vNull,
                        0,pDatetime,pUserId,pDatetime,pUserId,vAlloc,vNull,vNull,
                        vEmpty,vEmpty,A.partner_id
                FROM tt_cn_ar A
                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
                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
--                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
                WHERE A.session_id = pSessionId
                AND B.dn_ar_id = vDnArId
                AND B.doc_type_id = vDnDocTypeId;
--                AND D.flg_payment = vYes;

                -- Truncate table alocation
                TRUNCATE TABLE tt_allocation;

            END LOOP;

        END LOOP;

        -- UPDATE allocation_ar_id PADA tt_mapping_alloc_ar
        WITH alloc_ar_list AS (
            SELECT  A.session_id, A.credit_id, A.credit_doc_type_id, NEXTVAL('fi_allocation_ar_seq') AS alloc_ar_id
            FROM tt_mapping_alloc_ar A
            WHERE session_id = pSessionId
            GROUP BY A.session_id, A.credit_id, A.credit_doc_type_id
        )
        UPDATE tt_mapping_alloc_ar A
        SET allocation_ar_id = B.alloc_ar_id
        FROM alloc_ar_list B
        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;

        -- insert ke table asli
        INSERT INTO fi_allocation_ar(
            allocation_ar_id,tenant_id,doc_type_id,doc_no,doc_date,ou_id,partner_id,
            partner_bank_id,ext_doc_no,ext_doc_date,ref_doc_type_id,ref_id,due_date,
            curr_code,debit_amount,credit_amount,payment_amount,remark,status_doc,
            workflow_status,version,create_datetime,create_user_id,update_datetime,update_user_id,
            ref_amount,flg_automatic_credit_note_ar
            )
        SELECT A.allocation_ar_id,A.tenant_id,vDocTypeIdCreditAllocAr,A.credit_doc_no,A.credit_doc_date,pOuId,A.partner_id,
            vNull,vEmpty,vEmpty,A.credit_doc_type_id,A.credit_id,A.credit_doc_date,
            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',
            'APPROVED',0,pDatetime,pUserId,pDatetime,pUserId,
            SUM(A.credit_amount),vYes
        FROM tt_mapping_alloc_ar A
        JOIN m_partner B ON A.partner_id = B.partner_id
        WHERE A.session_id = pSessionId
        GROUP BY A.allocation_ar_id,A.tenant_id,A.credit_curr_code,A.partner_id,A.credit_doc_no,A.credit_doc_date,
                B.partner_name, A.credit_id, A.credit_doc_type_id;

        INSERT INTO fi_allocation_ar_invoice(
            tenant_id, allocation_ar_id, ref_doc_type_id,
            ref_id, curr_code, debit_amount, credit_amount, payment_amount,
            version, create_datetime, create_user_id, update_datetime, update_user_id,
            ref_remain_amount)
        SELECT A.tenant_id,A.allocation_ar_id, A.debit_doc_type_id,
            A.debit_id, A.debit_curr_code, SUM(A.debit_amount),0,SUM(A.debit_amount),
            0, pDatetime,pUserId, pDatetime,pUserId,
            B.amount
        FROM tt_mapping_alloc_ar A
        INNER JOIN tt_dn_ar B ON B.session_id = A.session_id AND B.dn_ar_id = A.debit_id
        WHERE A.session_id = pSessionId
        GROUP BY A.tenant_id,A.allocation_ar_id, A.debit_doc_type_id,
            A.debit_id, A.debit_curr_code, B.amount;

        UPDATE fi_invoice_ar_balance Z
        SET flg_payment = vInprogress
        FROM tt_cn_ar A
        WHERE A.session_id = pSessionId
        AND A.cn_ar_id = Z.invoice_ar_balance_id
        AND A.doc_type_id = Z.doc_type_id;

        UPDATE fi_invoice_ar_balance Z
        SET flg_payment = vInprogress
        FROM tt_dn_ar A
        WHERE A.session_id = pSessionId
        AND A.dn_ar_id = Z.invoice_ar_balance_id
        AND A.doc_type_id = Z.doc_type_id;

        UPDATE fi_invoice_tax_ar_balance Z
        SET flg_payment = vInprogress
        FROM tt_cn_ar A
        WHERE A.session_id = pSessionId
        AND A.cn_ar_id = Z.invoice_tax_ar_balance_id
        AND A.doc_type_id = Z.doc_type_id;


        UPDATE fi_invoice_tax_ar_balance Z
        SET flg_payment = vInprogress
        FROM tt_dn_ar A
        WHERE A.session_id = pSessionId
        AND A.dn_ar_id = Z.invoice_tax_ar_balance_id
        AND A.doc_type_id = Z.doc_type_id;

        --insert process message
        PERFORM generate_process_message_for_submit_doc(pSessionId, pTenantId, vProcessName,
            A.allocation_ar_id ||'_'||A.credit_doc_no, pDatetime, vParamKey, A.allocation_ar_id::CHARACTER VARYING, pUserId)
        FROM tt_mapping_alloc_ar A
        WHERE A.session_id = pSessionId
        GROUP BY A.allocation_ar_id, A.credit_doc_no;

        -- Mendapatkan default approval flow ID yang dipakai dari sysconfig
        SELECT awe_flow_id INTO vFlowAlloc
        FROM awe_flow
        WHERE scheme = vSchemeAlloc
        AND flg_validate = vYes
        AND active = vYes;

        -- Generate data awe_currdoc_status
        INSERT INTO awe_currdoc_status(
            req_id, tenant_id, scheme, doc_id, doc_no, doc_date,
            current_state, remark, current_user_id, current_role_id, flg_user_role, label,
            DATA, flow_id, create_datetime, create_user_id, create_role_id,
            update_datetime, update_user_id, update_role_id, version)
        SELECT A.allocation_ar_id||'_'||A.credit_doc_no, A.tenant_id, vSchemeAlloc, A.allocation_ar_id, A.credit_doc_no, vPeriod,
            vWorkflowApproved, B.remark, pUserId, -1, 'U', 'ALLOCATION CN'||A.credit_doc_no,
            '{}', vFlowAlloc, pDatetime, pUserId, -1,
            pDatetime, pUserId, -1, 0
        FROM tt_mapping_alloc_ar A
        INNER JOIN fi_allocation_ar B ON B.allocation_ar_id = A.allocation_ar_id
        WHERE A.session_id = pSessionId
        GROUP BY A.allocation_ar_id, A.credit_doc_no, A.tenant_id, B.remark;

    END IF;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;