in_submit_return_note_katamata

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

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

CREATE OR REPLACE FUNCTION in_submit_return_note(bigint, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pProcessNo          ALIAS FOR $3;

    vProcessId      bigint;
    vReturnNoteId   bigint;
    vUserId         bigint;
    vDatetime       character varying(14);
    vFlagInvoice    character varying(1);
    vEmptyId        bigint;
    vStatusRelease  character varying(1);
    vEmptyValue     character varying(1);
    vStatusFinal    character varying(1);
    vStatusDraft    character varying(1);
    vFlagYes            character varying(1);
    vFlagNo             character varying(1);
    vEmptyString        character varying(1);
    vJournalType        character varying(20);
    vSignDebit          character varying(1);
    vSignCredit         character varying(1);
    vTypeRate           character varying(3);
    vProductCOA         character varying(10);
    vSystemCOA          character varying(10);
    vParentOuId         bigint;
    vJournalTrxId       bigint;
    vTagKeyMou          character varying(10);

    vDocJournal             DOC_JOURNAL%ROWTYPE;
    vOuStructure            OU_BU_STRUCTURE%ROWTYPE;
    result                  RECORD;

    vReturnNoteDocTypeId    bigint;
    vRoundingModeNonTax     character varying(5);

    vSoId                   bigint;
    vDigitDppRoundingTax    integer;

    vEmptyAmount            numeric := 0;
    vFlgYes                 character varying(1) := 'Y';
    vFlgNo                  character varying(1) := 'N';

BEGIN

    vFlagInvoice := 'N';
    vEmptyId := -99;
    vEmptyValue := ' ';
    vStatusRelease := 'R';
    vStatusFinal := 'F';
    vStatusDraft := 'D';
    vFlagYes := 'Y';
    vFlagNo := 'N';
    vEmptyString := ' ';
    vSignDebit := 'D';
    vSignCredit := 'C';
    vTypeRate := 'COM';
    vProductCOA := 'PRODUCT';
    vSystemCOA := 'SYSTEM';
    vTagKeyMou := 'MOU';

    vReturnNoteDocTypeId := 502;
    SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;


    SELECT A.process_message_id INTO vProcessId
    FROM t_process_message A
    WHERE A.tenant_id = pTenantId AND
        A.process_name = 'in_submit_return_note' AND
        A.process_no = pProcessNo;

    SELECT CAST(A.process_parameter_value AS bigint) INTO vReturnNoteId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'returnNoteId';

    SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'userId';

    SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
    FROM t_process_parameter A
    WHERE A.process_message_id = vProcessId AND
        A.process_parameter_key = 'datetime';

    -- DELETE table temporary
    DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;
    DELETE FROM tt_coin_detail_brand WHERE session_id = pSessionId;
    DELETE FROM tt_coin_summary WHERE session_id = pSessionId;

    SELECT f_get_value_system_config_by_param_code(pTenantId, 'dpp.rounding.decimal.tax')::integer INTO vDigitDppRoundingTax;

    /*
     * 1.update status doc in_inventory
     * 2.update data ke in_product_balance_stock / insert data ke in_product-balance_stock
     * 3.insert data ke in_log_product_balance_stock
     * 4.update data sl_so_balance_item
     * 5.insert data sl_log_so_balance_item
     * 6.insert data sl_so_balance_invoice
     * 7.insert data sl_so_balance_invoice_tax
     * 8.update data in_balance_do_item
     * 9.add trx jurnal
     */

    SELECT f_get_ou_bu_structure(A.ou_from_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
    FROM in_inventory A
    WHERE A.inventory_id = vReturnNoteId INTO result;

    vOuStructure := result.ou;
    vDocJournal := result.doc;

    UPDATE in_inventory SET status_doc = vStatusRelease, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
    WHERE inventory_id = vReturnNoteId;

    /*
     * mengambil data dari in_inventory_item tanpa melakukan group by untuk ditampung ke tt_in_product_balance
     */
    INSERT INTO tt_in_product_balance
    (session_id, tenant_id, inventory_id, inventory_item_id, product_balance_id, product_id, product_status,
     serial_number, product_expired_date, product_year_made, lot_number, qty_realization, base_uom_id)
    SELECT pSessionId, A.tenant_id, A.inventory_id, B.inventory_item_id, B.product_balance_id, B.product_id, B.product_status,
        B.serial_number, B.product_expired_date, B.product_year_made, B.lot_number, B.qty_realization, B.base_uom_id
    FROM in_inventory A, in_inventory_item B
    WHERE A.inventory_id = B.inventory_id AND
        A.inventory_id = vReturnNoteId;

    /*
     * add product balance yang belum ada
     */
    INSERT INTO in_product_balance
    (tenant_id, product_id, serial_number, lot_number,
    product_expired_date, product_year_made,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.product_id, A.serial_number, A.lot_number,
        A.product_expired_date, A.product_year_made,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_in_product_balance A
    WHERE A.session_id = pSessionId AND
        A.product_balance_id = vEmptyId AND
        NOT EXISTS (SELECT 1 FROM in_product_balance B
                    WHERE A.tenant_id = B.tenant_id AND
                        A.product_id = B.product_id AND
                        A.serial_number = B.serial_number AND
                        A.lot_number = B.lot_number)
    GROUP BY A.tenant_id, A.product_id, A.serial_number, A.lot_number,
        A.product_expired_date, A.product_year_made;

    -- Update product_balance_id yang ada di table temp
    UPDATE tt_in_product_balance A
        SET product_balance_id = B.product_balance_id
    FROM in_product_balance B
    WHERE A.session_id = pSessionId AND
        A.product_balance_id = vEmptyId AND
        A.tenant_id = B.tenant_id AND
        A.product_id = B.product_id AND
        A.serial_number = B.serial_number AND
        A.lot_number = B.lot_number;

    /*
     * create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock
     */
    INSERT INTO tt_in_product_balance_summary_stock
            (session_id, tenant_id, inventory_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty)
    SELECT  B.session_id, B.tenant_id, B.inventory_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status,
            B.base_uom_id, SUM(B.qty_realization)
    FROM    in_inventory A, tt_in_product_balance B
    WHERE   A.inventory_id = B.inventory_id
        AND B.session_id = pSessionId
    GROUP BY B.session_id, B.tenant_id, B.inventory_id, A.warehouse_from_id, B.product_id, B.product_balance_id, B.product_status,
            B.base_uom_id;

    /*
     * update product_balance_stock
     */
    UPDATE in_product_balance_stock
        SET version = in_product_balance_stock.version + 1,
            qty = in_product_balance_stock.qty + A.qty,
            update_datetime = vDatetime,
            update_user_id = vUserId
    FROM tt_in_product_balance_summary_stock A
    WHERE A.session_id = pSessionId AND
        in_product_balance_stock.tenant_id = A.tenant_id AND
        in_product_balance_stock.warehouse_id = A.warehouse_id AND
        in_product_balance_stock.product_id = A.product_id AND
        in_product_balance_stock.product_balance_id = A.product_balance_id AND
        in_product_balance_stock.product_status = A.product_status;

    /*
     * insert data in_product_balance_stock
     */
    INSERT INTO in_product_balance_stock
    (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_status, A.base_uom_id, A.qty,
            0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_in_product_balance_summary_stock A
    WHERE A.session_id = pSessionId AND
        NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
                    WHERE C.tenant_id = A.tenant_id AND
                        C.warehouse_id = A.warehouse_id AND
                        C.product_id = A.product_id AND
                        C.product_balance_id = A.product_balance_id AND
                        C.product_status = A.product_status);

    /*
     * insert data in_log_product_balance_stock
     */
    INSERT INTO in_log_product_balance_stock
    (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
     product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_to_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
        B.product_id, B.product_balance_id, A.warehouse_to_id, B.product_status, B.base_uom_id, B.qty,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory A, tt_in_product_balance_summary_stock B
    WHERE B.session_id = pSessionId AND
        A.inventory_id = B.inventory_id;


    /*
     * insert data temporer tt_in_so_balance_item
     * custom ambil dari RRS
     *
     * Modify @author Thea, 15 Jul 2016
     * ubah ambil nilai item
     */
    INSERT INTO tt_in_so_balance_item
    (session_id, tenant_id, ou_id, doc_type_id,
    doc_no, doc_date, inventory_id, partner_id,
    inventory_item_id, so_id, do_id, do_item_id,
    qty_return, base_uom_id, remark,
    so_item_id, curr_code, price,
    flg_tax_amount, qty_so, qty_int_so,
    so_uom_id, tax_id, tax_percentage)
    SELECT pSessionId, A.tenant_id, A.ou_from_id, A.doc_type_id,
            A.doc_no, A.doc_date, A.inventory_id, C.partner_id,
            B.inventory_item_id, A.ref_id, B.ref_id, D.request_return_sales_brand_item_id,
            SUM(B.qty_realization), B.base_uom_id, A.remark,
            vEmptyId, D.curr_code, D.nett_sell_price,
            D.flg_tax_amount, D.qty_return, D.qty_return,
            D.uom_id, D.tax_id, D.tax_percentage
    FROM in_inventory A, in_inventory_item B, sl_request_return_sales C, sl_request_return_sales_brand_item D, m_product E
    WHERE A.inventory_id = vReturnNoteId AND
          A.inventory_id = B.inventory_id AND
          A.ref_id = C.request_return_sales_id AND
          C.request_return_sales_id = D.request_return_sales_id AND
          E.product_id = B.product_id AND
          E.brand_id = D.brand_id
    GROUP BY A.tenant_id, A.ou_from_id, A.doc_type_id,
            A.doc_no, A.doc_date, A.inventory_id, C.partner_id,
            B.inventory_item_id, A.ref_id, B.ref_id, D.request_return_sales_brand_item_id,
            B.base_uom_id, A.remark, D.curr_code,  D.nett_sell_price,
            D.flg_tax_amount, D.qty_so,
            D.uom_id, D.tax_id, D.tax_percentage;

    /*
     * buat data sl_so_balance_invoice
     * --B.regular_disc_amount langsung SET 0 karena nilai regular disc amount nya selalu 0, tidak di set pada RRS
     */
    INSERT INTO sl_so_balance_invoice
    (tenant_id, ou_id, partner_id, so_id,
    ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_dlv_so, so_uom_id,
    curr_code, price_so, item_amount, flg_invoice, invoice_id,
    regular_disc_amount, promo_disc_amount, adj_regular_disc_amount, adj_promo_disc_amount,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, A.partner_id, A.so_id,
        A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.do_item_id, ROUND(SUM((A.qty_return * A.qty_so) / A.qty_int_so)), A.so_uom_id,
        A.curr_code, A.price,
        f_get_dpp_after_discount(ROUND(SUM((A.qty_return * A.qty_so) / A.qty_int_so)) * B.gross_sell_price, A.flg_tax_amount, f_get_tax_amount_after_discount(ROUND(SUM((A.qty_return * A.qty_so) / A.qty_int_so)) * B.gross_sell_price, A.flg_tax_amount, A.tax_percentage, 0)),
        vFlagInvoice, vEmptyId,
        0, 0, 0, 0,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_in_so_balance_item A
    INNER JOIN sl_request_return_sales_brand_item B ON A.do_item_id = B.request_return_sales_brand_item_id
    WHERE A.session_id = pSessionId
    GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.do_item_id, A.so_uom_id, A.curr_code, A.price,
        B.gross_sell_price, A.flg_tax_amount, A.tax_percentage;

    /*
     * buat data sl_so_balance_invoice_tax
     */
    INSERT INTO sl_so_balance_invoice_tax
    (tenant_id, ou_id, partner_id, so_id,
    ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
    tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.ou_id, A.partner_id, A.so_id,
        A.doc_type_id, A.inventory_id, A.do_item_id, A.tax_id, D.flg_amount,
        A.tax_percentage, A.curr_code,
        f_get_dpp_after_discount(ROUND(SUM((A.qty_return * A.qty_so) / A.qty_int_so)) * B.gross_sell_price, A.flg_tax_amount, f_get_tax_amount_after_discount(ROUND(SUM((A.qty_return * A.qty_so) / A.qty_int_so)) * B.gross_sell_price, A.flg_tax_amount, A.tax_percentage, 0)),
        f_get_tax_amount_after_discount(ROUND(SUM((A.qty_return * A.qty_so) / A.qty_int_so)) * B.gross_sell_price, A.flg_tax_amount, A.tax_percentage, 0),
        vFlagInvoice, vEmptyId,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_in_so_balance_item A, m_tax D, sl_request_return_sales_brand_item B
    WHERE A.session_id = pSessionId AND
          A.tax_id = D.tax_id AND
          A.do_item_id = B.request_return_sales_brand_item_id
    GROUP BY A.tenant_id, A.ou_id, A.partner_id, A.so_id, A.doc_type_id, A.inventory_id, A.do_item_id, A.tax_id,
        D.flg_amount, A.tax_percentage, A.curr_code, B.gross_sell_price, A.flg_tax_amount;

    UPDATE in_balance_do_item SET status_item = vStatusFinal
    FROM tt_in_so_balance_item A
    WHERE A.session_id = pSessionId AND
        in_balance_do_item.do_item_id = A.do_item_id AND
        in_balance_do_item.qty_dlv -in_balance_do_item.qty_return <= 0;

    UPDATE in_balance_do_item SET status_item = vStatusRelease
    FROM tt_in_so_balance_item A
    WHERE A.session_id = pSessionId AND
        in_balance_do_item.do_item_id = A.do_item_id AND
        in_balance_do_item.qty_dlv -in_balance_do_item.qty_return > 0;

    /*
     * Henik
     * For Project Katamata Only, update data balance MOU & insert Log if SO was tagging to MOU
     * For Project Katamata Only, insert into return tagging based on SO if SO was tagging to MOU
     *
     * ======= BEGIN =======
     */
    -- update plafon mou di mou balance
    -- And insert ke table in_return_tagging
    SELECT B.ref_id INTO vSoId
    FROM in_inventory  A
    INNER JOIN sl_request_return_sales B
        ON A.ref_id = B.request_return_sales_id
        AND A.ref_doc_type_id = B.doc_type_id
    WHERE A.inventory_id = vReturnNoteId;

    IF EXISTS(SELECT 1 FROM sl_so_tagging WHERE tenant_id = pTenantId AND so_id = vSoId AND tag_key = vTagKeyMou) THEN
        --update data MOU balance if so = so by brand yang tagging ke mou ( and mou_id != -99 )
        WITH summary AS (
            SELECT E.ref_id AS so_id, B.tag_doc_id AS mou_id,
                SUM(A.item_amount-A.regular_disc_amount)+SUM(COALESCE(C.tax_amount,0)) AS amount_and_tax
            FROM sl_so_balance_invoice A
            INNER JOIN in_inventory D ON A.ref_id = D.inventory_id AND A.ref_doc_type_id = D.doc_type_id
            INNER JOIN sl_request_return_sales E ON D.ref_id = E.request_return_sales_id AND D.ref_doc_type_id = E.doc_type_id
            INNER JOIN sl_so_tagging B ON E.ref_id = B.so_id AND A.tenant_id = B.tenant_id AND B.tag_key = vTagKeyMou
            LEFT JOIN sl_so_balance_invoice_tax C
                ON A.partner_id = C.partner_id
                AND A.ref_doc_type_id = C.ref_doc_type_id
                AND A.ref_id = C.ref_id
                AND A.ref_item_id = C.ref_item_id
                AND A.do_receipt_item_id = C.do_receipt_item_id
            WHERE A.tenant_id = pTenantId
                AND A.ref_id = vReturnNoteId
                AND A.ref_doc_type_id = vReturnNoteDocTypeId
            GROUP BY E.ref_id, B.tag_doc_id
        )
        UPDATE sl_mou_balance D
        SET usage_balance = D.usage_balance - E.amount_and_tax,
            version = D.version + 1,
            update_datetime = vDatetime,
            update_user_id = vUserId
        FROM summary E
        WHERE D.mou_id = E.mou_id;

        --insert log untuk amount nya
        WITH summary AS (
            SELECT A.tenant_id, A.ou_from_id, C.tag_doc_id AS mou_id, A.doc_type_id,
                A.inventory_id, A.doc_no, A.doc_date, A.partner_id, B.curr_code,
                SUM(B.item_amount-B.regular_disc_amount) AS amount
            FROM in_inventory A
                INNER JOIN sl_request_return_sales D ON A.ref_id = D.request_return_sales_id AND A.ref_doc_type_id = D.doc_type_id
                INNER JOIN sl_so_balance_invoice B ON A.inventory_id = B.ref_id AND A.doc_type_id = B.ref_doc_type_id
                INNER JOIN sl_so_tagging C ON D.ref_id = C.so_id AND A.tenant_id = C.tenant_id AND C.tag_key = vTagKeyMou
            WHERE A.tenant_id = pTenantId
                 AND A.inventory_id = vReturnNoteId
                 AND A.doc_type_id = vReturnNoteDocTypeId
            GROUP BY A.tenant_id, A.ou_from_id, C.tag_doc_id, A.doc_type_id,
                A.inventory_id, A.doc_no, A.doc_date, A.partner_id, B.curr_code
        )
        INSERT INTO sl_mou_balance_log (
            tenant_id, ou_id, mou_id, ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
            partner_id, curr_code, amount, remark,
            create_datetime, create_user_id, update_datetime, update_user_id, version)
        SELECT D.tenant_id, D.ou_from_id, D.mou_id, D.doc_type_id, D.inventory_id, D.doc_no, D.doc_date,
            D.partner_id, D.curr_code,  D.amount , 'Return Balance MOU from Return-Note',
            vDatetime, vUserId, vDatetime, vUserId, 0
        FROM summary D;

        --insert log untuk amount - tax nya
        WITH summary AS (
            SELECT A.tenant_id, A.ou_from_id, C.tag_doc_id AS mou_id, A.doc_type_id,
                A.inventory_id, A.doc_no, A.doc_date, A.partner_id, B.curr_code,
                SUM(B.tax_amount) AS tax_amount
            FROM in_inventory A
                INNER JOIN sl_request_return_sales D ON A.ref_id = D.request_return_sales_id AND A.ref_doc_type_id = D.doc_type_id
                INNER JOIN sl_so_balance_invoice_tax B ON A.inventory_id = B.ref_id AND A.doc_type_id = B.ref_doc_type_id
                INNER JOIN sl_so_tagging C ON D.ref_id = C.so_id AND A.tenant_id = C.tenant_id AND C.tag_key = vTagKeyMou
            WHERE A.tenant_id = pTenantId
                 AND A.inventory_id = vReturnNoteId
                 AND A.doc_type_id = vReturnNoteDocTypeId
            GROUP BY A.tenant_id, A.ou_from_id, C.tag_doc_id, A.doc_type_id,
                A.inventory_id, A.doc_no, A.doc_date, A.partner_id, B.curr_code
        )
        INSERT INTO sl_mou_balance_log (
            tenant_id, ou_id, mou_id, ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date,
            partner_id, curr_code, amount, remark,
            create_datetime, create_user_id, update_datetime, update_user_id, version)
        SELECT D.tenant_id, D.ou_from_id, D.mou_id, D.doc_type_id, D.inventory_id, D.doc_no, D.doc_date,
            D.partner_id, D.curr_code,  D.tax_amount , 'Return Balance MOU from Return-Note - Tax',
            vDatetime, vUserId, vDatetime, vUserId, 0
        FROM summary D;

        -- Insert ke table in_return_tagging
        INSERT INTO in_return_tagging (
        tenant_id, ou_id, inventory_id, tag_key, tag_doc_id, tag_doc_no, remark,
        create_datetime, create_user_id, update_datetime, update_user_id, version)
        SELECT A.tenant_id, A.ou_from_id, vReturnNoteId, vTagKeyMou, B.tag_doc_id, B.tag_doc_no, 'Tag To MOU (Default by SO)',
            vDatetime, vUserId, vDatetime, vUserId, 0
        FROM in_inventory A
        INNER JOIN sl_request_return_sales C
            ON A.ref_id = C.request_return_sales_id
            AND A.ref_doc_type_id = C.doc_type_id
        INNER JOIN sl_so_tagging B ON C.ref_id = B.so_id AND C.tenant_id = B.tenant_id AND B.tag_key = vTagKeyMou
        WHERE A.inventory_id = vReturnNoteId;

    END IF;
    /*======== END ==========*/

    ---------------------------------------------------------------------------------------------------------------------------------
    -- Modif by Henik , 25 Agustus 2017
    /* Perhitungan coin (pengurang) dari dok Return Note
     * 1. Selalu lakukan perhitungan coin, baik yg ada referensi SOB maupun tidak
     * 2. Hanya dapat coin promo
     * 3. Menentukan masuk ke promo mana
     *      1. Berdasarkan tgl return, dan brand yg direturn
     *      2. Cek tgl return masuk ke periode promo mana yg masih aktif,
     *         jika masuk ke lebih dari 1 promo maka ambil date from paling akhir
     *         ( date_return BETWEEN date_from AND date_to) -> lebih dr 1 : ambil MAX(date_from)
     *                                                      -> lebih dr 1 : ambil MIN(date_to) ambil satu data yg dibuat paling duluan
     *
     * 4. Perhitungan : coin promo * (qty return per brand * -1)
     *
     */

    -- Prepare data Return Note , dan cari promo mana yg diperoleh
    -- Qty Return di kali -1
    -- nilai promo coin di set bernilai Yes
   WITH prepare_get_promo_return AS (

        -- Menentukan RN masuk ke daftar promo mana saja
        SELECT A.tenant_id, A.partner_id, A.inventory_id, A.doc_type_id,
            E.promo_id, E.date_from, E.date_to
        FROM in_inventory A
        INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
        INNER JOIN sl_request_return_sales_brand_item C ON A.ref_id = C.request_return_sales_id
                                                        AND f_get_brand_by_product_id(B.product_id) = C.brand_id
        INNER JOIN m_promo E ON A.tenant_id = E.tenant_id
                             AND E.flg_launching = vFlgNo
                             AND E.active = vFlgYes
        WHERE A.inventory_id = vReturnNoteId
            AND A.doc_date BETWEEN E.date_from AND E.date_to
            AND EXISTS(SELECT 1 FROM m_promo_item X
                       WHERE E.promo_id = X.promo_id
                            AND C.brand_id = X.brand_id)
        GROUP BY A.tenant_id, A.partner_id, A.inventory_id, A.doc_type_id, E.promo_id, E.date_from, E.date_to

    ), select_promo_for_return AS (

        -- Mengambil promo yg date_from nya paling mendekati tgl return
         SELECT A.tenant_id, A.partner_id, A.inventory_id, A.doc_type_id,
            A.promo_id, A.date_from, A.date_to
         FROM prepare_get_promo_return A
         WHERE EXISTS(SELECT 1
                      FROM prepare_get_promo_return B
                      WHERE A.inventory_id = B.inventory_id
                      HAVING A.date_from = MAX(B.date_from)
                    )

    ), filter_promo_for_return AS (

        -- jika dari max(date_from) promo ada lebih dari 1, maka ambil date_to promo yg paling dekat dengan tgl RN
        -- data promo akan diorder by promo_id dan dilimit 1 data yg diambil
         SELECT  A.tenant_id, A.partner_id, A.inventory_id, A.doc_type_id,
                A.promo_id, A.date_from, A.date_to
         FROM select_promo_for_return A
         WHERE EXISTS(SELECT 1
                      FROM select_promo_for_return B
                      WHERE A.inventory_id = B.inventory_id
                      HAVING A.date_to = MIN(B.date_to)
                     )
         ORDER BY A.promo_id
         LIMIT 1

    )
    -- insert data prepare promo for return note untuk di follow up proses perhitungan coinnya
    INSERT INTO tt_coin_detail_brand (
        session_id,tenant_id, partner_id, so_id, ref_id, ref_doc_type_id, salesman_id,
        brand_id, group_brand, qty,
        promo_id, flg_promo_coin, sub_promo_id,
        flg_sub_promo_coin, product_launching_id, flg_launching_coin,
        coin_promo, coin_sub_promo, coin_launching,
        coin_adjustment, coin_periodic_adjustment
    )
    SELECT pSessionId, A.tenant_id, A.partner_id, vEmptyId, A.inventory_id, A.doc_type_id, vEmptyId,
        C.brand_id, C.group_brand, SUM(B.qty_realization *-1) AS qty,
        A.promo_id, vFlgYes, vEmptyId,
        vFlgNo, vEmptyId, vFlgNo,
        vEmptyAmount, vEmptyAmount, vEmptyAmount,
        vEmptyAmount, vEmptyAmount
    FROM filter_promo_for_return A
    INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
    INNER JOIN m_brand_ext C ON f_get_brand_by_product_id(B.product_id) = C.brand_id
    GROUP BY A.tenant_id, A.partner_id, A.inventory_id, A.doc_type_id,
        C.brand_id, C.group_brand, A.promo_id;

    -- Dilakukan perhitungan coin dan update data coin promo balance jika dokumen return note memiliki promo
    IF EXISTS(SELECT 1 FROM tt_coin_detail_brand WHERE session_id = pSessionId AND ref_id = vReturnNoteId AND promo_id <> vEmptyId) THEN

        -- Execute function perhitungan coin
        PERFORM f_coin_calculation(pSessionId);

        -- Update data coin promo balance jika data nya sudah ada (lihat berdasarkan partner_id dan promo_id yg sama)
        -- Berdasarkan data di table temp tt_coin_summary hasil dari function f_coin_calculation
        UPDATE sl_coin_promo_balance A SET
                coin_promo = A.coin_promo + B.coin_promo,
                coin_sub_promo = A.coin_sub_promo + B.coin_sub_promo,
                coin_launching = A.coin_launching + B.coin_launching,
                coin_total = A.coin_total + B.coin_total,
                version = A.version + 1,
                update_user_id = vUserId,
                update_datetime = vDatetime
        FROM tt_coin_summary B
        WHERE B.session_id = pSessionId
            AND A.tenant_id = B.tenant_id
            AND A.partner_id = B.partner_id
            AND A.promo_id = B.promo_id;

        -- Insert data coin balance jika belum ada datanya
        -- Berdasarkan data di table temp tt_coin_summary hasil dari function f_coin_calculation
        INSERT INTO sl_coin_promo_balance (
            tenant_id, partner_id, promo_id, coin_promo, coin_sub_promo,
            coin_launching, coin_adjustment, coin_periodic_adjustment, coin_total,
            create_datetime, create_user_id, update_datetime, update_user_id, version
        )
        SELECT A.tenant_id, A.partner_id, A.promo_id, A.coin_promo, A.coin_sub_promo,
            A.coin_launching, A.coin_adjustment, A.coin_periodic_adjustment, A.coin_total,
            vDatetime, vUserId, vDatetime, vUserId, 0
        FROM tt_coin_summary A
        WHERE session_id = pSessionId
            AND NOT EXISTS (SELECT 1 FROM sl_coin_promo_balance B
                                     WHERE A.tenant_id = B.tenant_id
                                            AND A.partner_id = B.partner_id
                                            AND A.promo_id = B.promo_id);

        -- Insert data log coin ke table sl_log_coin_promo_balance
        -- Berdasarkan data di table temp tt_coin_detail_brand hasil dari olahan function f_coin_calculation
        INSERT INTO sl_log_coin_promo_balance (
            tenant_id, partner_id, so_id, ref_id, ref_doc_type_id, promo_id, flg_promo_coin,
            sub_promo_id, flg_sub_promo_coin, product_launching_id, flg_launching_coin,
            brand_id, group_brand, salesman_id, qty, coin_promo, coin_sub_promo,
            coin_launching, coin_adjustment, coin_periodic_adjustment,
            create_datetime, create_user_id, update_datetime, update_user_id, version
        )
        SELECT tenant_id, partner_id, so_id, ref_id, ref_doc_type_id, promo_id, flg_promo_coin,
            sub_promo_id, flg_sub_promo_coin, product_launching_id, flg_launching_coin,
            brand_id, group_brand, salesman_id, qty, coin_promo, coin_sub_promo,
            coin_launching, coin_adjustment, coin_periodic_adjustment,
            vDatetime, vUserId, vDatetime, vUserId, 0
        FROM tt_coin_detail_brand A
        WHERE A.session_id = pSessionId
        ORDER BY A.brand_id;

    END IF;


    ---------------------------------------------------------------------------------------------------------------------------------

    /*
     * journal return note
     * Debit Inventory = dari nilai COGS
     * Credit HPP
     */
    /*
     * membuat data transaksi jurnal :
     * 1. buat admin
     * 2. buat temlate jurnal
     */
    PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_from_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', vDatetime, vUserId)
    FROM in_inventory A
    WHERE A.inventory_id = vReturnNoteId;

    SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;

    INSERT INTO gl_journal_trx
    (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
    ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
    ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date,
        (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_from_id, A.ext_doc_no, A.ext_doc_date,
        A.ref_doc_type_id, A.ref_id, A.doc_date, f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), A.remark, vStatusDraft, 'DRAFT',
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM in_inventory A
    WHERE A.inventory_id = vReturnNoteId;

    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
        A.doc_type_id, B.inventory_item_id,
        A.partner_id, B.product_id, vEmptyId, vEmptyId,
        vEmptyId, vSignDebit, vProductCOA, vEmptyId,
        f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
        0 , A.doc_date, vTypeRate,
        1, 1, 'PRODUCT_STOCK', B.remark
    FROM in_inventory A, in_inventory_item B
    WHERE A.inventory_id = vReturnNoteId AND
        A.inventory_id = B.inventory_id;


/* NK, 1 Feb 2014, journal HPP tidak perlu break down sampai ke product
    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
        A.doc_type_id, B.inventory_item_id,
        A.partner_id, B.product_id, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), B.qty_realization, B.base_uom_id,
        0 , A.doc_date, vTypeRate,
        1, 1, 'HPP', B.remark
    FROM in_inventory A, in_inventory_item B
    WHERE A.inventory_id = vReturnNoteId AND
        A.inventory_id = B.inventory_id;
*/
/*
    INSERT INTO tt_journal_trx_item
    (session_id, tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id, ou_id, sub_ou_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark)
    SELECT pSessionId, A.tenant_id, B.journal_trx_id, 1,
        A.doc_type_id, A.inventory_item_id, B.ou_id, B.sub_ou_id,
        vEmptyId, C.product_id, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), A.curr_code, A.qty_return, A.base_uom_id,
        f_get_amount_before_tax((A.qty_return * A.qty_so * A.price) / A.qty_int_so, A.flg_tax_amount, A.tax_percentage,0),
        A.doc_date, vTypeRate,
        1, 1, 'HPP', A.remark
    FROM tt_in_so_balance_item A, gl_journal_trx B, in_inventory_item C
    WHERE A.session_id = pSessionId AND
        B.journal_trx_id = vJournalTrxId AND
        A.inventory_item_id = C.inventory_item_id;

    INSERT INTO gl_journal_trx_mapping
    (tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
        A.ref_doc_type_id, A.ref_id,
        A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
        A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
        A.coa_id, A.curr_code, A.qty, A.uom_id,
        A.amount, A.journal_date, A.type_rate,
        A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId AND
        A.journal_desc = 'HPP';

*/
    INSERT INTO gl_journal_trx_item
    (tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
        A.ref_doc_type_id, A.ref_id,
        A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
        A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
        A.coa_id, A.curr_code, A.qty, A.uom_id,
        A.amount, A.journal_date, A.type_rate,
        A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId AND
        A.journal_desc = 'PRODUCT_STOCK';

    INSERT INTO gl_journal_trx_mapping
    (tenant_id, journal_trx_id, line_no,
    ref_doc_type_id, ref_id,
    partner_id, product_id, cashbank_id, ou_rc_id,
    segmen_id, sign_journal, flg_source_coa, activity_gl_id,
    coa_id, curr_code, qty, uom_id,
    amount, journal_date, type_rate,
    numerator_rate, denominator_rate, journal_desc, remark,
    "version", create_datetime, create_user_id, update_datetime, update_user_id)
    SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
        vEmptyId, vEmptyId,
        vEmptyId, vEmptyId, vEmptyId, vEmptyId,
        vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
        f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), f_get_value_system_config_by_param_code(pTenantId, 'ValutaBuku'), 0, vEmptyId,
        0, A.journal_date, A.type_rate,
        1, 1, 'COGS', vEmptyValue,
        0, vDatetime, vUserId, vDatetime, vUserId
    FROM tt_journal_trx_item A
    WHERE A.session_id = pSessionId
    GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate;

    -- DELETE table temporary
    DELETE FROM tt_in_product_balance_summary_stock WHERE session_id = pSessionId;
    DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
    DELETE FROM tt_in_so_balance_item WHERE session_id = pSessionId;
    DELETE FROM tt_coin_detail_brand WHERE session_id = pSessionId;
    DELETE FROM tt_coin_summary WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION in_submit_return_note(bigint, character varying, character varying)
  OWNER TO sts;
GRANT EXECUTE ON FUNCTION in_submit_return_note(bigint, character varying, character varying) TO sts;
GRANT EXECUTE ON FUNCTION in_submit_return_note(bigint, character varying, character varying) TO public;