```
-- Modified Fitra, 2017-11-29
-- perubahan di header :tgl SO baru disamakan dengan tgl SO lama

CREATE OR REPLACE FUNCTION sl_automatic_so(character varying, bigint, bigint, bigint, character varying, character varying, bigint, bigint, character varying, character varying, bigint)
    RETURNS void AS
$BODY$
DECLARE
    pSessionId              alias for $1;
    pTenantId               alias for $2;
    pUserId                 alias for $3;
    pRoleId                 alias for $4;
    pFlgUserRole            alias for $5;
    pDatetime               alias for $6;
    pOldSoId                alias for $7;
    pAutonumId              alias for $8;
    pDocNo                  alias for $9;
    pDocDate                alias for $10;
    pCancelSoId             alias for $11;

    vNewSoId                bigint;
    vFlowSo                 bigint;
    vYes                    character varying(1) := 'Y';
    vStatusInprogress       character varying(1) := 'I';
    vWorkflowApproved       character varying(8) := 'APPROVED';
    vProcessName            character varying    := 'sl_submit_so';
    vParamKey               character varying    := 'soId';
    vSchemeSo               character varying    := 'FA01';
    vDocTypeSo              bigint := 301;
    vFlagDiscountAmount    character varying     := 'AMOUNT';

BEGIN

    -- ambil id manage cancel so
    SELECT nextval('sl_so_seq') INTO vNewSoId;

    -- insert ke table header so
    INSERT INTO sl_so(
            so_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, flg_work_order,
            flg_top_type, partner_ship_cp_id, partner_ship_cp_name, partner_ship_cp_phone1,
            partner_ship_cp_phone2, flg_show_inv_tax, flg_old_status, regular_discount_percentage)
    SELECT  vNewSoId, pTenantId, vDocTypeSo, pDocNo, A.doc_date, A.ou_id, A.doc_no,
            A.doc_date, vDocTypeSo, pOldSoId, A.remark, A.partner_id, A.partner_cp_id,
            A.partner_bill_to_id, A.partner_ship_to_id, A.partner_ship_address_id,
            A.due_date, A.salesman_id, A.flg_delivery, A.curr_code, A.add_discount_percentage,
            A.add_discount_amount, vStatusInprogress, vWorkflowApproved, 0, pDatetime,
            pUserId, pDatetime, pUserId, A.flg_work_order,
            A.flg_top_type, A.partner_ship_cp_id, A.partner_ship_cp_name, A.partner_ship_cp_phone1,
            A.partner_ship_cp_phone2, A.flg_show_inv_tax, A.flg_old_status, A.regular_discount_percentage
    FROM    sl_so A
    WHERE A.so_id = pOldSoId;

    -- insert ke table additional header so
    INSERT INTO sl_so_additional_for_dlg(
            so_id, tenant_id, group_brand_product, promo_sales_id, create_datetime,
            create_user_id, update_datetime, update_user_id, version, region_id,
            warehouse_id, flg_add_discount)
    SELECT  vNewSoId, pTenantId, group_brand_product, promo_sales_id, pDatetime,
            pUserId, pDatetime, pUserId, 0, region_id,
            warehouse_id, flg_add_discount
    FROM    sl_so_additional_for_dlg A
    WHERE A.so_id = pOldSoId;

    UPDATE sl_so A
    SET add_discount_amount = 0
    FROM sl_so_additional_for_dlg B
    WHERE A.so_id = vNewSoId AND
          A.so_id = B.so_id AND
          B.flg_add_discount = vFlagDiscountAmount;

    -- insert ke table so item
   WITH prepare_insert_data_so_item AS(
        SELECT  C.so_item_id, C.partner_ship_to_id, C.partner_ship_address_id, C.product_id,
                D.qty_add * -1 AS qty_so, C.so_uom_id, D.qty_add_int * -1 AS qty_int, C.base_uom_id,
                C.curr_code, C.gross_sell_price, C.flg_tax_amount, C.tax_id,
                C.tax_percentage, C.promo_code, C.discount_percentage, C.discount_amount,
                C.eta, C.tolerance_dlv_qty, C.remark, C.eta_day, C.flg_disc,
                f_get_gross_amount_after_promo_discount(((D.qty_add_int * -1) * C.gross_sell_price), E.promo_percentage, f_get_digit_decimal_doc_curr(vDocTypeSo, C.curr_code)) AS gross_amount_after_disc
        FROM    sl_manage_so A
        INNER JOIN sl_manage_so_item B ON A.manage_so_id = B.manage_so_id
        INNER JOIN sl_so_item C ON B.so_item_id = C.so_item_id AND A.so_id = C.so_id
        INNER JOIN sl_so_balance_item D ON C.so_item_id = D.so_item_id
        INNER JOIN sl_so_item_additional_for_dlg E ON C.so_item_id = E.so_item_id
        WHERE A.manage_so_id = pCancelSoId
    )
    INSERT INTO sl_so_item(
            tenant_id, so_id, line_no, ref_doc_type_id, ref_id,
            partner_ship_to_id, partner_ship_address_id, product_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_percentage, discount_amount,
            nett_sell_price,
            tax_amount,
            nett_item_amount,
            eta, tolerance_dlv_qty, remark, version, create_datetime,
            create_user_id, update_datetime, update_user_id, eta_day, flg_disc)
    SELECT  pTenantId, vNewSoId AS so_id, row_number() over() AS line_no, vDocTypeSo, A.so_item_id,
            A.partner_ship_to_id, A.partner_ship_address_id, A.product_id, A.qty_so,
            A.so_uom_id, A.qty_int, A.base_uom_id, A.curr_code, A.gross_sell_price,
            A.flg_tax_amount, A.tax_id, A.tax_percentage,
            f_get_tax_amount_after_discount(A.gross_amount_after_disc, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeSo, A.curr_code)) / A.qty_int,
            A.promo_code, A.discount_percentage, A.discount_amount,
            f_get_dpp_after_discount(A.gross_amount_after_disc, A.flg_tax_amount, f_get_tax_amount_after_discount(A.gross_amount_after_disc, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeSo, A.curr_code))) / A.qty_int,
            f_get_tax_amount_after_discount(A.gross_amount_after_disc, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeSo, A.curr_code)),
            f_get_dpp_after_discount(A.gross_amount_after_disc, A.flg_tax_amount, f_get_tax_amount_after_discount(A.gross_amount_after_disc, A.flg_tax_amount, A.tax_percentage, f_get_digit_decimal_doc_curr(vDocTypeSo, A.curr_code))),
            A.eta, A.tolerance_dlv_qty, A.remark, 0, pDatetime,
            pUserId, pDatetime, pUserId, A.eta_day, A.flg_disc
    FROM    prepare_insert_data_so_item A;

    -- insert ke table additional so item
    INSERT INTO sl_so_item_additional_for_dlg(
            so_item_id, tenant_id, so_id, promo_percentage,
            promo_amount,
            total_promo_disc_amount,
            create_datetime, create_user_id, update_datetime,
            update_user_id, version)
    SELECT  A.so_item_id, A.tenant_id, A.so_id, B.promo_percentage,
            ( (A.qty_int*A.gross_sell_price) - f_get_gross_amount_after_promo_discount((A.qty_int * A.gross_sell_price), B.promo_percentage, f_get_digit_decimal_doc_curr(vDocTypeSo, A.curr_code)) ) /A.qty_int,
            (A.qty_int*A.gross_sell_price) - f_get_gross_amount_after_promo_discount((A.qty_int * A.gross_sell_price), B.promo_percentage, f_get_digit_decimal_doc_curr(vDocTypeSo, A.curr_code)),
            pDatetime, pUserId, pDatetime,
            pUserId, 0
    FROM    sl_so_item A
    INNER JOIN sl_so_item_additional_for_dlg B ON A.ref_id = B.so_item_id AND A.ref_doc_type_id = vDocTypeSo
    WHERE A.so_id = vNewSoId;

    -- insert ke table formula assembly
    INSERT INTO sl_so_assembly_formula(
            so_item_id, parent_product_id, child_product_id,
            qty_base_uom, base_uom_id, version, create_datetime, create_user_id,
            update_datetime, update_user_id)
    SELECT  c.so_item_id, c.product_id, a.child_product_id,
            a.qty_base_uom, b.base_uom_id, 0, pDatetime, pUserId,
            pDatetime, pUserId
    FROM m_product_assembly a
    INNER JOIN m_product b ON b.tenant_id = a.tenant_id AND b.product_id = a.child_product_id
    INNER JOIN sl_so_item c ON a.parent_product_id = c.product_id
    WHERE a.tenant_id = pTenantId
    AND c.so_id = vNewSoId;

    -- insert ke table finance
    INSERT INTO sl_so_finance(
            so_id, line_no, tenant_id, flg_collection, due_date,
            curr_code, amount, trx_curr_code, trx_amount, remark, version,
            create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT  vNewSoId, A.line_no, A.tenant_id, A.flg_collection, A.due_date,
            A.curr_code, A.amount, A.trx_curr_code, A.trx_amount, A.remark, 0,
            pDatetime, pUserId, pDatetime, pUserId
    FROM    sl_so_finance A
    WHERE A.so_id = pOldSoId;

    --insert process message
    PERFORM generate_process_message_for_submit_doc_dlg(pSessionId, pTenantId, vProcessName,
        A.so_id ||'_'||A.doc_no, pDatetime, vParamKey, A.so_id::character varying, pUserId)
    FROM sl_so A
    WHERE A.so_id = vNewSoId;

    -- Mendapatkan default approval flow ID yang dipakai dari sysconfig
    SELECT awe_flow_id INTO vFlowSo
    FROM awe_flow
    WHERE scheme = vSchemeSo 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.so_id||'_'||A.doc_no, A.tenant_id, vSchemeSo, A.so_id, A.doc_no, A.doc_date,
        vWorkflowApproved, A.remark, pUserId, pRoleId, pFlgUserRole, 'SALES ORDER'||A.doc_no,
        '{}', vFlowSo, pDatetime, pUserId, pRoleId,
        pDatetime, pUserId, pRoleId, 0
    FROM sl_so A
    WHERE A.so_id = vNewSoId;

    -- Insert autonum ref mapping
    INSERT INTO autonum_ref_mapping(
        autonum_generated_id, ref_id
    )
    VALUES (pAutonumId, vNewSoId);

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
/
```