-- Function: sl_so_mlm_add_item_from_cart_use_voucher(bigint, character varying, bigint, character varying, bigint)

-- DROP FUNCTION sl_so_mlm_add_item_from_cart_use_voucher(bigint, character varying, bigint, character varying, bigint);

CREATE OR REPLACE FUNCTION sl_so_mlm_add_item_from_cart_use_voucher_for_member(bigint, character varying, bigint, character varying, bigint, character varying)
  RETURNS SETOF refcursor AS
$BODY$
    DECLARE
        pTenantId               alias for $1;
        pDateTime               alias for $2;
        pUserId                 alias for $3;
        pMemberCode             alias for $4;
        pSlCartId               alias for $5;
        pPaymentMethod          alias for $6;

        vYearMonth              character varying(6);
        vOuId                   bigint;
        vStatusDraft            character varying(1);
        vStatusRelease          character varying(1);
        vStatusCancel           character varying(1);
        vSoMlmId                bigint;
        vSoMlmItemId            bigint;
        vSoMlmDocTypeId         bigint;
        vSoMlmDocNo             character varying(30);
        vSoMlmDocScheme         character varying(10);
        vStateDraft             character varying(10);
        vProductId              bigint;
        vQtySo                  bigint;
        vQtyBackOrder           bigint;
        vQtyAfterRsv            bigint;
        vQtyForReserved         bigint;

        vPartnerId              bigint;
        vPartnerShipToId        bigint;
        vPartnerShipAddressId   bigint;
        vPartnerCpId            bigint;
        vPartnerRelBillingId    bigint;
        vPartnerCpName          character varying(100);
        vPhone1                 character varying(100);
        vMobilePhone1           character varying(50);

        vRoundingMode           character varying;
        vDecimalRounding        integer;
        vTaxId                  bigint;
        vTaxPercentage          numeric;
        vTaxPrice               numeric;
        vGrossPrice             numeric;
        vDiscMemberPercent      numeric;
        vDiscMemberAmount       numeric;
        vDiscPromoPercent       numeric;
        vDiscPromoAmount        numeric;
        vMaxLineNo              numeric;

        vSubTotalPriceAfterDisc numeric;
        vDsCommission           numeric(5,2);

        vDefaultRoleId          bigint;
        vDefaultUomId           bigint;
        vFlowSoMlmId            bigint;
        vFlagYes                character varying(1);
        vFlagNo                 character varying(1);
        vSpaceValue             character varying(6);
        vFlagRole               character varying(1);
        vReqId                  character varying(50);
        vUserWebDS              bigint;
        vDocTypeCartId          bigint;
        vUserSms                bigint;
        vUserMobile             bigint;

        vDocNo1                 character varying(10);
        vDocNo2                 character varying(10);
        vDocNoSo                character varying(10);
        vDocNoPo                character varying(10);
        vSessionId              character varying(50);
        vEventLogCreate         character varying(50);
        vEventLogAddItem        character varying(50);
        vOrigin                 character varying(50);
        vRemarkCreate           character varying(50);
        vRemarkAddItem          character varying(50);

        -- SW, 21 Maret 2017
        vFlgManageStock         character varying(1);
        --/SW

        pRefHeader              REFCURSOR := 'refHeader';

        vTtTempCartSo           tt_temp_cart_so%ROWTYPE;
        vBulk                   bigint;
        vReceived               bigint;
        vDisplay                bigint;

        vQtyPakaiTemp           numeric;
        vQtyBalanceTemp         numeric;
        vQtyReservedTemp        numeric;

    vCurrentStatusDoc   character varying(1);
    --vCartPartnerId        bigint;
    vCartMemberId       bigint;
    vMemberId           bigint;
    BEGIN
        vUserWebDS              := -3;
        vUserSms                := -2;
        vUserMobile             := -4;

        vDecimalRounding        := 0;
        vSubTotalPriceAfterDisc := 0;
        vSoMlmDocTypeId         := 306;
        vSoMlmDocScheme         := 'FA06';
        vStatusDraft            := 'D';
        vStatusRelease          := 'R';
        vStatusCancel           := 'C';
        vStateDraft             := 'DRAFT';
        vFlagRole               := 'R';
        vRoundingMode           := 'RD';
        vFlagYes                := 'Y';
        vFlagNo                 := 'N';
        vSpaceValue             := ' ';
        vYearMonth              := ' ';
        vDocTypeCartId          := 396;
        vBulk                   := 13;
        vReceived               := 14;
        vDisplay                := 12;
        vMaxLineNo              := 0;
        vEventLogCreate         :='SO.CREATE';
        vEventLogAddItem        :='SO.ADDITEM';
        vRemarkCreate           :='SO dibuat dari ';
        vRemarkAddItem          :='Ditambahkan produk ';


    IF(pUserId = vUserSms )THEN
        vOrigin :='SMS';
    ELSIF (pUserId = vUserMobile ) THEN
        vOrigin :='MOBILE';
    END IF;


    -- Validate sl_cart cannot status_doc = 'R'
    -- Validate sl_cart member code is exact partner_id
    SELECT status_doc INTO vCurrentStatusDoc FROM sl_cart WHERE sl_cart_id = pSlCartId;
    --SELECT partner_id INTO vCartPartnerId FROM sl_cart WHERE sl_cart_id = pSlCartId;
    SELECT member_id INTO vCartMemberId FROM sl_cart WHERE sl_cart_id = pSlCartId ;
    SELECT member_id INTO vMemberId FROM mlm_member WHERE member_code = pMemberCode ;

    -- cari partner_id
    SELECT A.partner_id INTO vPartnerId
    FROM mlm_ds A
    JOIN sl_cart B ON A.member_id = B.stock_point_id
    WHERE B.sl_cart_id = pSlCartId;


    IF vCurrentStatusDoc <> 'I' THEN
        RAISE EXCEPTION 'Cart is not being checked out';
    END IF;

--  akan diganti validasi member_id
--  IF vCartPartnerId <> vPartnerId THEN
--      RAISE EXCEPTION 'Cart partner ID is not match';
--  END IF;

    IF vCartMemberId <> vMemberId THEN
        RAISE EXCEPTION 'Cart member ID is not match';
    END IF;

        --Session Id
        SELECT pSlCartId::character varying||'_'||pDateTime INTO vSessionId;

        --Ou Id
        SELECT f_get_value_system_config_by_param_code(pTenantId, 'DEFAULT.OU.ID') INTO vOuId;

        --tax Id
        SELECT f_get_default_tax_id(pTenantId) INTO vTaxId;

        -- default UOM
        SELECT f_get_default_uom_id(pTenantId) INTO vDefaultUomId;


            -- default Partner Ship Address Id
        SELECT partner_address_id INTO vPartnerShipAddressId
        FROM m_partner_address WHERE partner_id = vPartnerId AND flg_ship = vFlagYes ORDER BY partner_address_id LIMIT 1;


        -- Untuk di isi ke sl_so_mlm
        SELECT partner_cp_id INTO vPartnerCpId FROM m_partner_cp WHERE partner_id = vPartnerId ORDER BY partner_cp_id LIMIT 1;
        SELECT relation_id INTO vPartnerRelBillingId
            FROM m_partner_rel WHERE partner_id = vPartnerId AND flg_bill = vFlagYes ORDER BY partner_rel_id LIMIT 1;
        SELECT cp_name INTO vPartnerCpName FROM m_partner_cp WHERE partner_id = vPartnerId ORDER BY partner_cp_id LIMIT 1;
        SELECT phone1 INTO vPhone1 FROM m_partner_cp WHERE partner_id = vPartnerId ORDER BY partner_cp_id LIMIT 1;
        SELECT mobile_phone1 INTO vMobilePhone1 FROM m_partner_cp WHERE partner_id = vPartnerId ORDER BY partner_cp_id LIMIT 1;

        --bisa jadi ada perubahan,karena komisi ds yang didapat dari member itu berbeda
        SELECT f_get_value_system_config_by_param_code(pTenantId, 'dsCommision')::numeric INTO vDsCommission;

        -- cari partner ship to id
        SELECT relation_id INTO vPartnerShipToId
            FROM m_partner_rel WHERE partner_id = vPartnerId AND flg_shipp = vFlagYes ORDER BY partner_rel_id LIMIT 1;


        -- calculate untuk qty_reserved
        INSERT INTO tt_temp_cart_so(
            session_id,sl_cart_item_id,product_catalog_id,product_id,product_catalog_code,
            qty_cart,qty_backorder,flg_out_of_stock)
        SELECT vSessionId,A.sl_cart_item_id,A.product_catalog_id,B.product_id,B.product_catalog_code,
            A.qty,0,vFlagYes
        FROM sl_cart_item A
        JOIN m_product_catalog B ON A.product_catalog_id = B.product_catalog_id
        WHERE A.sl_cart_id = pSlCartId;


        INSERT INTO tt_temp_calculate_cart_so(
            session_id,product_id,qty_balance,qty_reserved,qty_pakai)
        SELECT A.session_id,A.product_id,0,0,0
        FROM tt_temp_cart_so A
        WHERE A.session_id = vSessionId
        GROUP BY A.product_id,A.session_id;

        -- UPDATE qty_balance
        WITH stock_in_warehouse AS(
            SELECT A.product_id,SUM(A.qty) as Qty
            FROM in_product_balance_stock A
            JOIN tt_temp_calculate_cart_so B ON A.product_id =B.product_id
            AND A.warehouse_id IN (vBulk,vReceived,vDisplay)
            AND B.session_id = vSessionId
            GROUP BY A.product_id
        )
        UPDATE tt_temp_calculate_cart_so Z
        SET qty_balance = A.qty
        FROM stock_in_warehouse A
        WHERE A.product_id = Z.product_id
        AND Z.session_id = vSessionId;

        -- UPDATE qty_reserved
          WITH reserved_qty AS(
            SELECT COALESCE(SUM(qty),0) AS reserved_qty,product_id
            FROM in_product_balance_stock_reserved
            WHERE tenant_id = pTenantId
            AND ou_id = vOuId
            GROUP BY product_id
        )
        UPDATE tt_temp_calculate_cart_so Z
        SET qty_reserved = A.reserved_qty
        FROM reserved_qty A
        WHERE A.product_id = Z.product_id
        AND Z.session_id = vSessionId;

        FOR vTtTempCartSo IN
            SELECT *
            FROM tt_temp_cart_so
            WHERE session_id = vSessionId
            ORDER BY sl_cart_item_id
        LOOP

            SELECT A.qty_pakai,A.qty_balance,A.qty_reserved INTO vQtyPakaiTemp,vQtyBalanceTemp,vQtyReservedTemp
            FROM tt_temp_calculate_cart_so A
            WHERE A.product_id = vTtTempCartSo.product_id
            AND A.session_id = vSessionId;

            -- SW, 21 Maret 2017
            SELECT flg_manage_stock INTO vFlgManageStock
            FROM m_product_custom
            WHERE product_id = vTtTempCartSo.product_id;

            IF vFlgManageStock = vFlagNo THEN

                --update untuk product yang tidak flg_out_of_stock
                UPDATE tt_temp_cart_so Z
                SET flg_out_of_stock = vFlagNo
                WHERE Z.sl_cart_item_id = vTtTempCartSo.sl_cart_item_id
                AND Z.session_id = vSessionId;

                --product_id qty_cart
                UPDATE tt_temp_calculate_cart_so Z
                SET qty_pakai = vTtTempCartSo.qty_cart
                WHERE Z.product_id = vTtTempCartSo.product_id
                AND Z.session_id = vSessionId;

            ELSE IF vQtyBalanceTemp-vQtyReservedTemp-vQtyPakaiTemp > 0 THEN
            -- /SW

                --update untuk product yang tidak flg_out_of_stock
                UPDATE tt_temp_cart_so Z
                SET flg_out_of_stock = vFlagNo
                WHERE Z.sl_cart_item_id = vTtTempCartSo.sl_cart_item_id
                AND Z.session_id = vSessionId;

                IF vQtyBalanceTemp-vQtyReservedTemp-vQtyPakaiTemp - vTtTempCartSo.qty_cart > 0 THEN
                    --product_id qty_cart
                    UPDATE tt_temp_calculate_cart_so Z
                    SET qty_pakai = vTtTempCartSo.qty_cart
                    WHERE Z.product_id = vTtTempCartSo.product_id
                    AND Z.session_id = vSessionId;

                ELSE
                    --product_id qty_cart
                    UPDATE tt_temp_calculate_cart_so Z
                    SET qty_pakai = COALESCE(vQtyBalanceTemp-vQtyReservedTemp-vQtyPakaiTemp,0)
                    WHERE Z.product_id = vTtTempCartSo.product_id
                    AND Z.session_id = vSessionId;

                    vQtyBackOrder := vTtTempCartSo.qty_cart - (vQtyBalanceTemp-vQtyReservedTemp) - vQtyPakaiTemp;

                END IF;

            ELSE

                vQtyBackOrder := vTtTempCartSo.qty_cart;

            END IF;


            UPDATE tt_temp_cart_so Z
            SET qty_backorder = COALESCE(vQtyBackOrder,0)
            WHERE Z.sl_cart_item_id = vTtTempCartSo.sl_cart_item_id
            AND Z.product_id = vTtTempCartSo.product_id
            AND Z.session_id = vSessionId;

            vQtyBackOrder := 0;

        END LOOP;



        IF(
            SELECT COUNT(1) > 0
            FROM tt_temp_cart_so
            WHERE session_id =vSessionId
            AND flg_out_of_stock = vFlagNo
           )THEN
              -- Check apakah ada SO MLM yang masih DRAFT untuk vPartnerId bersangkutan dan flg_special = 'N' (Bukan BKT / Catalog)
            IF EXISTS( SELECT 1 FROM sl_so_mlm WHERE status_doc = vStatusDraft AND partner_id = vPartnerId
                                 AND doc_type_id = vSoMlmDocTypeId AND flg_special = vFlagNo AND flg_tutup_so = vFlagNo
                                 AND create_user_id IN (vUserWebDS, vUserSms, vUserMobile)
                                 LIMIT 1)
            THEN
                    -- ada yang draft, ambil SO MLM id nya
                    SELECT so_mlm_id INTO vSoMlmId FROM sl_so_mlm
                    WHERE status_doc = vStatusDraft AND partner_id = vPartnerId
                        AND doc_type_id = vSoMlmDocTypeId AND flg_special = vFlagNo AND flg_tutup_so = vFlagNo
                        AND create_user_id IN (vUserWebDS, vUserSms, vUserMobile)
                    ORDER BY so_mlm_id DESC
                    LIMIT 1;

                    -- UPDATE sl_so_mlm SET update_user_id, update_datetime, version
                    UPDATE sl_so_mlm SET update_user_id = pUserId, update_datetime = pDateTime, version = version + 1 WHERE so_mlm_id = vSoMlmId;

            ELSE
            vSoMlmId := nextval('sl_so_mlm_seq');

                    SELECT f_get_value_system_config_by_param_code(11, 'DOC.NO.SO.PALOMAWEB') INTO vDocNoSo;
                    SELECT f_get_value_system_config_by_param_code(11, 'DOC.NO.PO.PALOMAWEB') INTO vDocNoPo;
                    SELECT substring(current_date::text from 3 for 2)||substring(current_date::text from 6 for 2)||'/' INTO vDocNo1;
                    SELECT lpad(nextval('sl_so_mlm_from_web_seq')::text, 6, '0') INTO vDocNo2;

                       -- Tidak ada yang draft, buat header
                    INSERT INTO sl_so_mlm(
                        so_mlm_id, tenant_id, doc_type_id,
                        doc_no,
                        doc_date, ou_id,
                        ext_doc_no,
                        ext_doc_date, ref_doc_type_id, ref_id, remark, partner_id,
                        partner_cp_id, partner_bill_to_id, partner_ship_to_id, partner_ship_address_id,
                        due_date, salesman_id, flg_delivery, curr_code, add_discount_percentage,
                        add_discount_amount, status_doc, workflow_status, version, create_datetime,
                        create_user_id, update_datetime, update_user_id, partner_ship_cp_id,
                        partner_ship_cp_name, partner_ship_cp_phone1, member_mobile_phone,
                        flg_show_inv_tax, commission_percentage, commission_amount, flg_special)
                    SELECT
                        vSoMlmId, pTenantId, vSoMlmDocTypeId,
                        vDocNoSo||vDocNo1||vDocNo2 AS doc_no, -- 'SOMLM/'|| A.partner_code || '/' || vSoMlmId,
                        substring(pDateTime,1,8) as doc_date, vOuId,
                        vDocNoPo||vDocNo1||vDocNo2 AS ext_doc_no,  -- 'PO/'|| A.partner_code || '/' || vSoMlmId
                        substring(pDateTime,1,8) AS ext_doc_date,
                        -99, -99, '', vPartnerId,
                        vPartnerCpId, vPartnerRelBillingId, vPartnerShipToId, vPartnerShipAddressId,
                        substring(pDateTime,1,8) AS due_date, -99, vFlagYes, 'IDR', 0,
                        0, vStatusDraft, vStateDraft, 0, pDateTime,
                        pUserId, pDateTime, pUserId, vPartnerCpId,
                        vPartnerCpName, vPhone1, vMobilePhone1,
                        vFlagYes, vDsCommission, 0, vFlagNo
                    FROM m_partner A
                    WHERE A.partner_id = vPartnerId;

                     --[update 6 februari] init add so approved status
                    INSERT INTO sl_so_approved_status (so_mlm_id, last_ss_approve_user, last_ss_approve_time, last_fin_approve_user,
                                last_fin_approve_time, last_auto_approve_user, last_auto_approve_time,
                                last_print_picking_user, last_print_picking_time, last_scan_picking_user,
                                last_scan_picking_time, version, create_datetime, update_datetime,
                                create_user_id, update_user_id)
                    SELECT vSoMlmId, '', '',  '' ,
                         '', '', '',
                         '', '', '',
                        '', 0, pDateTime, pDateTime,
                        pUserId, pUserId;

                /**
                   Bagian ini untuk melengkapi data awe
                   Harus dipastikan sudah:
                     1. di setting system config ROLE.ASM.ID -> diisi dengan Role ID Area Sales Manager
                     2. sudah set approval flow untuk SO MLM FA06, dan hanya 1
                **/

                -- Mendapatkan default role yang dipakai dari sysconfig
                SELECT f_get_value_system_config_by_param_code(11, 'ROLE.ASM.ID') INTO vDefaultRoleId;

                -- Mendapatkan default approval flow ID yang dipakai dari sysconfig
                SELECT awe_flow_id INTO vFlowSoMlmId
                FROM awe_flow WHERE scheme = vSoMlmDocScheme AND flg_validate= vFlagYes LIMIT 1;

                -- Generate unique Request ID
                SELECT f_make_uid() INTO vReqId;

                -- 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 vReqId, pTenantId,   vSoMlmDocScheme, so_mlm_id, doc_no, doc_date, vStateDraft,
                           ' ', -99, vDefaultRoleId, vFlagRole, 'SALES ORDER MLM'||doc_no,
                           '{}', vFlowSoMlmId, pDateTime, pUserId, vDefaultRoleId, pDateTime,pUserId, vDefaultRoleId, 0
                    FROM sl_so_mlm WHERE so_mlm_id = vSoMlmId;

                -- Generate data awe_worklist
                INSERT INTO awe_worklist(
                        req_id, user_id, role_id, no_item, tenant_id, scheme, doc_id,
                        doc_no, doc_date, status, remark, flg_user_role, flg_to_do, create_datetime,
                        create_user_id, update_datetime, update_user_id, version)
                    SELECT vReqId, -99, vDefaultRoleId, 1, pTenantId, vSoMlmDocScheme, so_mlm_id,
                        doc_no, doc_date, vStateDraft, ' ', vFlagRole, vFlagYes, pDateTime,
                        pUserId, pDateTime, pUserId, 0
                    FROM sl_so_mlm WHERE so_mlm_id = vSoMlmId;

                -- Generate data awe_historydoc
                INSERT INTO awe_historydoc(
                        tenant_id, req_id, doc_id, scheme, user_id, role_id,
                        activity, previous_state, next_state, remark, next_user_id, next_role_id,
                        flg_user_role, activity_datetime, version)
                    SELECT pTenantId, vReqId, so_mlm_id, vSoMlmDocScheme, -99, vDefaultRoleId,
                        'CREATE', '', vStateDraft, '', -99, -99,
                        'U', pDateTime, 0
                    FROM sl_so_mlm WHERE so_mlm_id = vSoMlmId;

                -- insert into sl_so_mlm_log event SO.CREATE
                INSERT INTO sl_so_mlm_log(
                    so_mlm_log_id, so_mlm_id, so_mlm_item_id, event, remark,
                    origin, create_datetime, create_user_id, update_datetime, update_user_id,
                    version)
                SELECT nextval('sl_so_mlm_log_seq'),vSoMlmId,-99,vEventLogCreate,vRemarkCreate||vOrigin,
                    vOrigin,pDateTime, pUserId, pDateTime, pUserId,
                    0;

            END IF;

            -- insert ke table temp tt_cart_to_so
            INSERT INTO tt_cart_to_so(
                    session_id,so_mlm_item_id,tenant_id,so_mlm_id,cart_item_id,cart_id,discount_member_percentage,discount_member_amount,
                    discount_promo_percentage,discount_promo_amount,qty,gross_sell_price,
                    price_after_disc,product_catalog_id,product_id,tax_percentage,
                    tax_price,line_no)
            SELECT vSessionId,nextval('sl_so_mlm_item_seq'),pTenantId,vSoMlmId,B.sl_cart_item_id,pSlCartId,B.discount_member,B.discount_member_amount,
                    B.discount_promo,B.discount_promo_amount,(C.qty_cart - C.qty_backorder),B.catalog_price,
                    B.price_after_discount,B.product_catalog_id,f_get_product_by_product_catalog_id(B.product_catalog_id),
                    f_get_product_tax_value (f_get_product_by_product_catalog_id(B.product_catalog_id)),
                    f_calc_tax_price_from_gross_price(B.catalog_price,B.discount_member, B.discount_promo, vDecimalRounding, vRoundingMode, vFlagYes, vTaxId, f_get_product_tax_value (f_get_product_by_product_catalog_id(B.product_catalog_id))),
                    ROW_NUMBER() OVER (PARTITION BY session_id ORDER BY session_id)
            FROM sl_cart A
            JOIN sl_cart_item B ON B.sl_cart_id = A.sl_cart_id
            JOIN tt_temp_cart_so C ON B.sl_cart_item_id = C.sl_cart_item_id
            WHERE A.sl_cart_id = pSlCartId
            AND (C.qty_cart - C.qty_backorder) > 0
            AND C.session_id = vSessionId;

        -- Get Max Line Number
        SELECT COALESCE(MAX(line_no),0) into vMaxLineNo
        FROM sl_so_mlm_item WHERE so_mlm_id = vSoMlmId;

            -- insert ke sl_so_mlm_item
            INSERT INTO sl_so_mlm_item(
                    so_mlm_item_id,
                    tenant_id,
                    so_mlm_id,
                    line_no,
                    ref_doc_type_id,
                    ref_id,
                    partner_ship_to_id,
                    partner_ship_address_id,
                    product_catalog_id,
                    qty_so,
                    so_uom_id,
                    qty_int,
                    base_uom_id,
                    curr_code,
                    gross_sell_price,
                    flg_tax_amount,
                    tax_id,
                    tax_percentage,
                    tax_price,
                    promo_code,
                    discount_member_percentage, discount_member_amount,
                    discount_promo_percentage, discount_promo_amount,
                    price_after_disc,
                    nett_sell_price, tax_amount, nett_item_amount,
                    tolerance_dlv_qty, remark, version, create_datetime, create_user_id,
                    update_datetime, update_user_id)
            SELECT A.so_mlm_item_id,
                    A.tenant_id,
                    A.so_mlm_id,
                    vMaxLineNo+A.line_no,
                    -99,
                    -99,
                    vPartnerShipToId, -- partner ship to id
                    vPartnerShipAddressId, -- partner ship address id
                    A.product_catalog_id,
                    A.qty,
                    vDefaultUomId,
                    A.qty,
                    vDefaultUomId,
                    'IDR',
                    A.gross_sell_price, -- gross_sell_price (harus sesuai dengan harga katalok)
                    vFlagYes,
                    vTaxId, -- nilainya berdasarkan taxCode PPn (tax id)
                    A.tax_percentage, -- tax percentage
                    A.tax_price, -- tax price
                    '', -- promo_code
                    A.discount_member_percentage,A.discount_member_amount,
                    A.discount_promo_percentage,A.discount_promo_amount,
                    A.price_after_disc, -- price_after_disc
                    0, --nett_sell_price
                    0, -- tax_amount,
                    0, -- nett_item_amount,
                    0, -- tolerance_dlv_qty
                    '', -- remark
                    0, pDateTime, pUserId, pDateTime, pUserId
            FROM tt_cart_to_so A
            WHERE A.session_id = vSessionId;

            -- insert ke sl_so_mlm_item_ext
            INSERT INTO sl_so_mlm_item_ext(
                    so_mlm_item_id,member_id,flg_payment_method,flg_paid,ref_item_id,ref_id,level_price,
                    flg_direct_order,create_datetime,create_user_id,update_datetime,update_user_id,version,flg_cancel,commission_percentage)
            SELECT A.so_mlm_item_id,D.member_id,pPaymentMethod,vFlagNo,A.cart_item_id,A.cart_id,C.zone,
                    vFlagYes,pDateTime, pUserId, pDateTime, pUserId,0,'NONE',vDsCommission
            FROM tt_cart_to_so A
            JOIN sl_cart B ON A.cart_id = B.sl_cart_id
            JOIN mlm_ds C ON B.stock_point_id = C.member_id
            JOIN mlm_member D ON B.member_id = D.member_id
            WHERE A.session_id = vSessionId;

            -- insert into sl_so_mlm_log event SO.ADDITEM
            INSERT INTO sl_so_mlm_log(
                so_mlm_log_id, so_mlm_id, so_mlm_item_id, event, remark,
                origin, create_datetime, create_user_id, update_datetime, update_user_id,
                version)
            SELECT nextval('sl_so_mlm_log_seq'),vSoMlmId,A.so_mlm_item_id,vEventLogAddItem,vRemarkAddItem||B.product_catalog_code||' dengan qty '||A.qty||' dari '||vOrigin,
                vOrigin,pDateTime, pUserId, pDateTime, pUserId,
                0
            FROM tt_cart_to_so A
            INNER JOIN m_product_catalog B ON A.product_catalog_id = B.product_catalog_id
            WHERE A.session_id = vSessionId;

            -- seharusnya berubah
            -- Update nilai commission_amount di so_mlm
            UPDATE sl_so_mlm SET commission_amount = f_get_commission_amount_by_so_mlm_id(pTenantId, vSoMlmId, vDecimalRounding, vRoundingMode)
            WHERE so_mlm_id = vSoMlmId;

            -- Update nilai nett sell price
            UPDATE sl_so_mlm_item Z
            SET nett_sell_price = Z.price_after_disc - Z.tax_price
            FROM tt_cart_to_so A
            WHERE A.so_mlm_id = vSoMlmId
            AND A.so_mlm_id = Z.so_mlm_id
            AND A.so_mlm_item_id = Z.so_mlm_item_id
            AND A.cart_id =pSlCartId
            AND A.session_id = vSessionId;

            -- Update nilai tax_amount and nett_item_amount (dikalikan dengan qty)
            UPDATE sl_so_mlm_item Z
            SET tax_amount = Z.qty_so * Z.tax_price,
                nett_item_amount =Z.qty_so * Z.nett_sell_price
            FROM tt_cart_to_so A
            WHERE A.so_mlm_id = vSoMlmId
            AND A.so_mlm_id = Z.so_mlm_id
            AND A.so_mlm_item_id = Z.so_mlm_item_id
            AND A.cart_id =pSlCartId
            AND A.session_id = vSessionId;

            --seharusnya ada, karena mau catat voucher ini punya member siapa
            -- update sl_so_mlm_item_voucher terlebih dahulu dari pada insert sl_so_mlm_item_voucher
            -- karena apabila condition update tidak di temukan, update akan langsung di skip
            UPDATE sl_so_mlm_item_voucher_per_member B
            SET qty = qty + A.promo_qty,
                update_datetime = pDateTime,
                update_user_id = pUserId,
                version = B.version + 1
            FROM sl_cart_item_voucher A
            WHERE A.sl_cart_id = pSlCartId AND
                  B.so_mlm_id = vSoMlmId AND
                  B.promo_id = A.promo_id;

            --seharusnya ada, karena mau catat voucher ini punya member siapa
            -- insert into sl_so_mlm_item_voucher
            INSERT INTO sl_so_mlm_item_voucher_per_member(so_mlm_id,promo_id,qty,member_id,create_datetime,create_user_id,update_datetime,update_user_id,version)
            SELECT vSoMlmId,A.promo_id,A.promo_qty,B.member_id,pDateTime,pUserId,pDateTime,pUserId,0
            FROM sl_cart_item_voucher A
            JOIN sl_cart B ON A.sl_cart_id = B.sl_cart_id
            WHERE A.sl_cart_id = pSlCartId AND NOT EXISTS (
                SELECT 1 FROM sl_so_mlm_item_voucher_per_member B WHERE B.so_mlm_id = vSoMlmId AND B.promo_id = A.promo_id
            );

            --tidak ada perubahan, tapi ada penamabahan, saldo voucher yang dimiliki member
            UPDATE sl_partner_promo_balance Z
            SET qty = Z.qty - A.promo_qty,
                update_user_id = pUserId,
                update_datetime = pDateTime,
                version = Z.version + 1
            FROM sl_cart_item_voucher A,sl_cart B
            WHERE A.sl_cart_id = B.sl_cart_id
            AND B.partner_id = Z.partner_id
            AND Z.promo_id = A.promo_id
            AND A.sl_cart_id = pSlCartId;

            INSERT INTO sl_log_partner_promo_balance (
                tenant_id,partner_id,promo_id,qty_usage_so,
                create_datetime,create_user_id, update_datetime,update_user_id,
                version,qty_cancel,qty_usage_do,ref_doc_type_id,ref_doc_no,ref_doc_date,
                remark,ref_id)
            SELECT A.tenant_id,A.partner_id,B.promo_id,B.promo_qty,
                   pDateTime,pUserId,pDateTime,pUserId,
                   0,0,0,396,A.doc_no,A.doc_date,'CHECK OUT',A.sl_cart_id
            FROM sl_cart A
            JOIN sl_cart_item_voucher B ON A.sl_cart_id = B.sl_cart_id
            AND B.sl_cart_id = pSlCartId;

        END IF;

                -- insert ke backorder
        INSERT INTO sl_so_backorder(
            tenant_id, ou_id, partner_id, product_id,
            ref_doc_type_id, ref_doc_no, ref_doc_date, base_uom_id, flg_followup,
            qty_backorder, qty_order, version, create_datetime, create_user_id,
            update_datetime, update_user_id, product_catalog_id, ref_item_id)
        SELECT pTenantId,vOuId,vPartnerId,A.product_id,
            vDocTypeCartId, C.doc_no,C.doc_date,vDefaultUomId,vFlagNo,
            A.qty_backorder,B.qty,0,pDateTime,pUserId,
            pDateTime,pUserId,B.product_catalog_id,B.sl_cart_item_id
        FROM tt_temp_cart_so A
        JOIN sl_cart_item B ON A.sl_cart_item_id = B.sl_cart_item_id
        JOIN sl_cart C ON C.sl_cart_id = B.sl_cart_id
        WHERE A.session_id = vSessionId
        AND C.sl_cart_id = pSlCartId
        AND A.qty_backorder > 0;


        INSERT INTO sl_cart_item_log(
            sl_cart_item_log_id, sl_cart_item_id, qty, event,
            remark, version, create_user_id, create_datetime, update_user_id, update_datetime
        )
        SELECT nextval('sl_cart_item_log_seq'), B.sl_cart_item_id, A.qty_backorder, 'BACK.ORDER',
        'Produk ' || D.product_catalog_code || ' backorder sebanyak ' || A.qty_backorder :: varchar || '.', 0 AS version, pUserId, pDateTime, pUserId, pDateTime
        FROM tt_temp_cart_so A
        JOIN sl_cart_item B ON A.sl_cart_item_id = B.sl_cart_item_id
        JOIN sl_cart C ON C.sl_cart_id = B.sl_cart_id
        JOIN m_product_catalog D ON B.product_catalog_id = D.product_catalog_id
        WHERE A.session_id = vSessionId
        AND C.sl_cart_id = pSlCartId
        AND A.qty_backorder > 0;


       IF(
            SELECT COUNT(1) > 0
            FROM tt_temp_cart_so
            WHERE session_id =vSessionId
            AND flg_out_of_stock = vFlagNo
       )THEN

            -- Update sl_cart status R
            UPDATE sl_cart
            SET status_doc = vStatusRelease,
                status_remark = 'Menunggu Konfirmasi',
                update_datetime = pDateTime
            WHERE sl_cart_id = pSlCartId;

        ELSE

            -- Update sl_cart status C
            UPDATE sl_cart
            SET status_doc = vStatusCancel,
                status_remark = 'Cancel',
                update_datetime = pDateTime
            WHERE sl_cart_id = pSlCartId;

        END IF;

        -- Update sl_cart_item
        UPDATE sl_cart_item Z
        SET so_mlm_id = vSoMlmId,
            so_mlm_item_id = A.so_mlm_item_id
        FROM tt_cart_to_so A
        WHERE Z.sl_cart_id = A.cart_id
        AND Z.sl_cart_item_id = A.cart_item_id
        AND A.session_id =vSessionId
        AND A.cart_id = pSlCartId;
        -- RAISE NOTICE 'balikan data';
        Open pRefHeader FOR
        SELECT sl_cart_item_id,product_catalog_id,product_id,product_catalog_code,qty_cart,qty_backorder,flg_out_of_stock
        FROM tt_temp_cart_so
        WHERE session_id =vSessionId;

        -- insert ke reserved
        INSERT INTO in_product_balance_stock_reserved(
            tenant_id,ou_id,product_id,base_uom_id,product_status,qty,version,
            create_datetime,create_user_id,update_datetime,update_user_id, ref_doc_type_id,
            ref_id, ref_item_id, ref_doc_no, ref_doc_date, qty_original, partner_id
        )
        SELECT A.tenant_id,A.ou_id,C.product_id,vDefaultUomId,'GOOD',B.qty_so,0,
            A.create_datetime,A.create_user_id,A.create_datetime,A.create_user_id,A.doc_type_id,A.
            so_mlm_id, B.so_mlm_item_id,A.doc_no, A.doc_date, B.qty_so,A.partner_id
        FROM sl_so_mlm A
        JOIN sl_so_mlm_item B ON A.so_mlm_id = B.so_mlm_id
        JOIN m_product_catalog C ON C.product_catalog_id =B.product_catalog_id
        JOIN sl_so_mlm_item_ext D ON B.so_mlm_item_id = D.so_mlm_item_id
        JOIN tt_cart_to_so E ON D.ref_item_id = E.cart_item_id
        WHERE A.so_mlm_id = vSoMlmId
        AND A.tenant_id = pTenantId
        AND A.ou_id = vOuId
        AND E.session_id = vSessionId;

        RETURN NEXT pRefHeader;


    END;
    $BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
/