--Adrian, Mar 8, 2018
--! Jika terjadi perubahan, sesuaikan juga r_limit_detail_mou !

CREATE OR REPLACE FUNCTION r_limit_detail_mou_for_salesman_csv(character varying, bigint, bigint, bigint, character varying, bigint, bigint, character varying, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader                  REFCURSOR := 'refHeader';
    pRefDetail                  REFCURSOR := 'refDetail';
    pRefDetailGrandTotal        REFCURSOR := 'refDetailGrandTotal';
    pSessionId                  ALIAS FOR $1;
    pTenantId                   ALIAS FOR $2;
    pUserId                     ALIAS FOR $3;
    pRoleId                     ALIAS FOR $4;
    pDatetime                   ALIAS FOR $5;

    pOuId                       ALIAS FOR $6;
    pPartnerId                  ALIAS FOR $7;
    pPeriodFrom                 ALIAS FOR $8;
    pPeriodTo                   ALIAS FOR $9;

    vPartnerCodeMou                 character varying(50);
    vPartnerNameMou                 character varying(100);
    vDocTypeIdSOByBrand             bigint;
    vStatusReleased                 character varying(1);
    vStatusFinal                    character varying(1);
    vStatusInProgress               character varying(1);
    vStatusDraft                    character varying(1);
    vTagKeyMOU                  character varying(10);
    vDocTypeIdReturnNote                bigint;
    vDocTypeIdSalesInvoice              bigint;
    vDocTypeIdInvoiceTemp               bigint;
    vDocTypeIdConversionOfExchangeIn        bigint;
    vDocTypeIdConversionOfExchangeOut       bigint;
    vDocTypeIdRRS                   bigint;
    vStatusMapping                  character varying(1);
    vStatusApproved                 character varying(1);
    vRaw                        character varying(20);
    vSummary                    character varying(20);
    vEmptyId                    bigint;
    vOne                        integer := 1;
    vEmptyValue                 character varying;
    vParamCodeInsentif          character varying := 'INCENTIVE';
    vActivityGLCodeInsentif     character varying;
    vActivityGLInsentif         bigint;

    vFlagYes                    character varying(1) :='Y';

    vDocNoMou                   character varying(50);
    i                           bigint;

BEGIN

    vPartnerCodeMou := ' ';
    vPartnerNameMou := ' ';
    vDocTypeIdSOByBrand := 398;
    vStatusReleased := 'R';
    vStatusFinal := 'F';
    vStatusInProgress := 'I';
    vStatusDraft := 'D';
    vTagKeyMOU := 'MOU';
    vDocTypeIdReturnNote := 502;
    vDocTypeIdSalesInvoice := 321;
    vDocTypeIdInvoiceTemp := 361;
    vDocTypeIdConversionOfExchangeIn := 394;
    vDocTypeIdConversionOfExchangeOut := 395;
    vDocTypeIdRRS :=381;
    vStatusMapping := 'M';
    vStatusApproved := 'A';
    vRaw := 'RAW';
    vSummary := 'SUMMARY';
    vEmptyId := -99;
    vEmptyValue := '';
    vDocNoMou := '';
    i := 1;

    SELECT partner_code INTO vPartnerCodeMou
    FROM m_partner
    WHERE partner_id = pPartnerId;

    SELECT partner_name INTO vPartnerNameMou
    FROM m_partner
    WHERE partner_id = pPartnerId;

    -- Get data-data activity gl code incentive dari system config value
    SELECT TRIM(REPLACE(f_get_value_system_config_by_param_code(pTenantId, vParamCodeInsentif), ';', ''',''')) INTO vActivityGLCodeInsentif;

    /*
    --get data activity gl insentif
    SELECT B.parameter_value INTO vActivityGLCodeInsentif
    FROM t_parameter A
        INNER JOIN t_system_config B ON A.parameter_id = B.parameter_id
    WHERE A.parameter_code = vParamCodeInsentif
        AND B.tenant_id = pTenantId;

    --get activity gl
    SELECT activity_gl_id INTO vActivityGLInsentif
    FROM m_activity_gl
    WHERE tenant_id = pTenantId
        AND activity_gl_code = vActivityGLCodeInsentif;
    */

    --Menghapus semua table temporrer untuk mulai insert data baru
    DELETE FROM tt_sl_r_limit_detail_mou_list WHERE session_id = pSessionId;
    DELETE FROM tt_sl_mou_list WHERE session_id = pSessionId;
    DELETE FROM tt_sl_so WHERE session_id = pSessionId;
    DELETE FROM tt_sl_outstanding_so WHERE session_id = pSessionId;
    DELETE FROM tt_sl_r_limit_detail_mou_list_for_csv_detail WHERE session_id = pSessionId;

    -- Mengambil data MOU sesuai dengan filter nya
    INSERT INTO tt_sl_mou_list(
      session_id ,mou_id,tenant_id ,ou_id ,doc_type_id ,doc_date,doc_no,period_from ,period_to ,partner_id ,
      curr_code ,plafon_amount ,status_doc ,workflow_status)
    SELECT pSessionId ,mou_id,tenant_id ,ou_id ,doc_type_id ,doc_date,doc_no,period_from ,period_to ,partner_id ,
      curr_code ,plafon_amount ,status_doc ,workflow_status
    FROM sl_mou
    WHERE tenant_id = pTenantId
        AND ou_id = pOuId
        AND partner_id = pPartnerId
        AND period_from BETWEEN pPeriodFrom AND pPeriodTo
        AND status_doc IN (vStatusReleased,vStatusFinal)
        AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, ou_id) = vOne
        AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, partner_id) = vOne;

    -- Mengambil semua data SO By Brand yang tagging ke mou (semua status doc)
    INSERT INTO tt_sl_so(
      session_id ,so_id,tenant_id ,ou_id ,doc_type_id ,doc_date ,doc_no ,partner_id , mou_id, curr_code ,
      amount, amount_tax, status_doc ,workflow_status)
    SELECT pSessionId ,A.so_id,A.tenant_id ,A.ou_id ,A.doc_type_id ,A.doc_date,A.doc_no,A.partner_id ,C.mou_id, A.curr_code ,
      SUM(D.nett_brand_amount) AS amount, SUM(D.qty*D.tax_price) AS amount_tax ,A.status_doc ,A.workflow_status
    FROM sl_so A
        INNER JOIN sl_so_info B ON A.so_id = B.so_id
        INNER JOIN tt_sl_mou_list C ON B.mou_id = C.mou_id AND C.session_id = pSessionId
        INNER JOIN sl_so_brand_item D ON A.so_id = D.so_id
    WHERE A.tenant_id = pTenantId
        AND A.ou_id = pOuId
        AND A.doc_type_id = vDocTypeIdSOByBrand
        AND A.doc_date BETWEEN pPeriodFrom AND pPeriodTo
        AND f_authorize_user_role_policy_ou(pTenantId, pUserId, pRoleId, A.ou_id) = vOne
        AND f_authorize_user_role_policy_partner(pTenantId, pUserId, pRoleId, A.partner_id) = vOne
        AND NOT EXISTS (
            SELECT 1 FROM sl_so_balance_invoice Z
            WHERE Z.so_id = A.so_id
            GROUP BY Z.so_id
            HAVING SUM(Z.qty_dlv_so) <= 0
        )
    GROUP BY A.so_id,A.tenant_id ,A.ou_id ,A.doc_type_id ,A.doc_date,A.doc_no,A.partner_id ,C.mou_id,A.curr_code ,
      A.status_doc ,A.workflow_status;

    -- Insert data so by brand ke table tt_sl_outstanding_so yang mana sudah tagging mou dan disubmit , status doc nya (R/F)
    INSERT INTO tt_sl_outstanding_so(session_id ,data_id ,so_id ,amount_so ,amount_tax_so)
    SELECT pSessionId, vRaw, A.so_id, A.amount, A.amount_tax
    FROM tt_sl_so A
        INNER JOIN sl_so_tagging B ON A.so_id = B.so_id AND B.tag_key = vTagKeyMOU
    WHERE A.session_id = pSessionId
        AND A.status_doc IN (vStatusReleased,vStatusFinal);

    -- Insert data invoice temp ke table tt_sl_outstanding_so yang ref_id (dokumen so by brand) tagging ke mou
    INSERT INTO tt_sl_outstanding_so(session_id ,data_id ,so_id,invoice_temp_id ,amount_inv_temp ,amount_tax_inv_temp)
    SELECT pSessionId, vRaw, B.ref_id,B.invoice_temp_id, B.total_amount, COALESCE(C.tax_amount,0)
    FROM tt_sl_outstanding_so A
        INNER JOIN sl_invoice_temp B ON A.so_id = B.ref_id
        LEFT JOIN sl_invoice_temp_tax C ON B.invoice_temp_id = C.invoice_temp_id
    WHERE A.session_id = pSessionId
        AND B.tenant_id = pTenantId
        AND B.ou_id = pOuId
        AND B.status_doc = vStatusReleased
        AND B.doc_type_id = vDocTypeIdInvoiceTemp
        AND NOT EXISTS (SELECT 1 FROM sl_invoice D
                    WHERE D.doc_no = B.inv_doc_no
                        AND D.ref_id = B.ref_id
                        AND D.doc_type_id = vDocTypeIdSalesInvoice
                        AND D.status_doc = vStatusReleased);

    -- Insert data invoice ke table tt_sl_outstanding_so yang ref_id (dokumen so by brand) tagging ke mou
    INSERT INTO tt_sl_outstanding_so(session_id ,data_id ,so_id,invoice_id ,amount_inv, amount_tax_inv)
    SELECT pSessionId, vRaw, B.ref_id, B.invoice_id, B.total_amount, COALESCE(D.tax_amount,0)
    FROM tt_sl_outstanding_so A
        INNER JOIN sl_invoice B ON A.so_id = B.ref_id
        INNER JOIN sl_invoice_tagging C ON B.invoice_id = C.invoice_id AND C.tag_key = vTagKeyMOU
        LEFT JOIN sl_invoice_tax D ON B.invoice_id = D.invoice_id
    WHERE A.session_id = pSessionId
        AND A.invoice_temp_id = vEmptyId
        AND B.tenant_id = pTenantId
        AND B.ou_id = pOuId
        AND B.status_doc = vStatusReleased
        AND B.doc_type_id = vDocTypeIdSalesInvoice;

    -- Insert ke table tt_sl_outstanding_so untuk menghitung summary (untuk mengetahui outstading amount nya so by brand)
    INSERT INTO tt_sl_outstanding_so(session_id ,data_id ,so_id,amount_so, amount_tax_so,
        amount_inv, amount_tax_inv, amount_inv_temp,
        amount_tax_inv_temp, final_so,
        final_tax_so)
    SELECT pSessionId, vSummary, so_id, SUM(amount_so) AS amount_so, SUM(amount_tax_so) AS amount_tax_so,
        SUM(amount_inv) AS amount_inv, SUM(amount_tax_inv) AS amount_tax_inv, SUM(amount_inv_temp) AS amount_inv_temp ,
        SUM(amount_tax_inv_temp) AS amount_tax_inv_temp ,(SUM(amount_so) - SUM(amount_inv) - SUM(amount_inv_temp)) AS final_so,
        (SUM(amount_tax_so) - SUM(amount_tax_inv) - SUM(amount_tax_inv_temp)) AS final_tax_so
    FROM tt_sl_outstanding_so
    WHERE session_id = pSessionId
    GROUP BY so_id;

    /*
    Insert data Invoice yang di mapping(di tarik manual ,sebelum nya SO by brand belum di tagging ke mou)
    dan status doc invoice = R -> ke table tt_sl_r_limit_detail_mou_list
    */
    INSERT INTO tt_sl_r_limit_detail_mou_list(
         session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
         doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
         status_doc ,workflow_status ,status_doc_mou,
         usage_amount_tax, total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
        A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
        C.invoice_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code, C.total_amount AS usage_amount,
        vStatusMapping AS status_doc_invoice, C.workflow_status, A.status_doc AS status_doc_mou,
        COALESCE(D.tax_amount, 0) AS usage_amount_tax, (C.total_amount + COALESCE(D.tax_amount, 0)) AS total_usage_amount
    FROM tt_sl_mou_list A
        INNER JOIN sl_invoice_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMou
        INNER JOIN sl_invoice C ON B.invoice_id = C.invoice_id
        LEFT JOIN sl_invoice_tax D ON C.invoice_id = D.invoice_id
    WHERE A.session_id = pSessionId
        AND C.doc_type_id = vDocTypeIdSalesInvoice
        AND C.status_doc = vStatusReleased
        AND NOT EXISTS(SELECT 1 FROM tt_sl_so D WHERE D.session_id = pSessionId AND D.so_id = C.ref_id);

    -- Insert data Invoice (SO By Brand nya sudah di tagging ke MOU) dan status doc invoice = R -> ke table tt_sl_r_limit_detail_mou_list
    INSERT INTO tt_sl_r_limit_detail_mou_list(
         session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
         doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
         status_doc ,workflow_status ,status_doc_mou,
         usage_amount_tax, total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
        A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
        C.invoice_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code, C.total_amount AS usage_amount,
        vStatusApproved AS status_doc_invoice ,C.workflow_status, A.status_doc AS status_doc_mou,
        COALESCE(D.tax_amount, 0) AS usage_amount_tax, (C.total_amount + COALESCE(D.tax_amount, 0)) AS total_usage_amount
    FROM tt_sl_mou_list A
        INNER JOIN sl_invoice_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMou
        INNER JOIN sl_invoice C ON B.invoice_id = C.invoice_id
        LEFT JOIN sl_invoice_tax D ON C.invoice_id = D.invoice_id
    WHERE A.session_id = pSessionId
        AND C.doc_type_id = vDocTypeIdSalesInvoice
        AND C.status_doc = vStatusReleased
        AND EXISTS(SELECT 1 FROM tt_sl_outstanding_so D
                WHERE D.session_id = pSessionId
                    AND D.data_id = vRaw AND D.invoice_id = C.invoice_id AND D.so_id = C.ref_id);

    /*
    Insert data Invoice temp (SO By Brand nya sudah di tagging ke MOU) dan status doc invoice temp = R
    dan belum di convert ke invoice beneran yang mana status doc invoice beneran nya = R ke table tt_sl_r_limit_detail_mou_list
    */
    INSERT INTO tt_sl_r_limit_detail_mou_list(
         session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
         doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
         status_doc ,workflow_status ,status_doc_mou,
         usage_amount_tax, total_usage_amount)
   SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
        A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
        D.invoice_temp_id AS doc_id, D.doc_type_id, D.doc_no, D.doc_date, D.partner_id, D.curr_code, D.total_amount AS usage_amount,
        D.status_doc AS status_doc_invoice_temp ,D.workflow_status, A.status_doc AS status_doc_mou,
        COALESCE(E.tax_amount, 0) AS usage_amount_tax, (D.total_amount + COALESCE(E.tax_amount, 0)) AS total_usage_amount
    FROM tt_sl_mou_list A
        INNER JOIN sl_so_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMou
        INNER JOIN tt_sl_outstanding_so C ON B.so_id = C.so_id AND C.data_id = vRaw
        INNER JOIN sl_invoice_temp D ON C.invoice_temp_id = D.invoice_temp_id
        LEFT JOIN sl_invoice_temp_tax E ON D.invoice_temp_id = E.invoice_temp_id
    WHERE A.session_id = pSessionId
        AND C.session_id = pSessionId;

    -- Insert data SO By Brand yang sudah di tagging dan status doc( D dan I) ke table tt_sl_r_limit_detail_mou_list
    INSERT INTO tt_sl_r_limit_detail_mou_list(
         session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
         doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
         status_doc ,workflow_status ,status_doc_mou,
         usage_amount_tax, total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
        A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
        B.so_id AS doc_id, B.doc_type_id, B.doc_no, B.doc_date, B.partner_id, B.curr_code, B.amount AS usage_amount,
        B.status_doc AS status_doc_so ,B.workflow_status, A.status_doc AS status_doc_mou,
        B.amount_tax AS usage_amount_tax, (B.amount + B.amount_tax) AS total_usage_amount
    FROM tt_sl_mou_list A
        INNER JOIN tt_sl_so B ON A.mou_id = B.mou_id AND B.session_id = pSessionId
    WHERE A.session_id = pSessionId
        AND B.status_doc IN (vStatusDraft,vStatusInProgress);

    /* Insert data SO By Brand yang sudah di tagging dan status doc( R/F) ke table tt_sl_r_limit_detail_mou_list
     * UNTUK SO YG SUDAH SAMPAI DI INVOICE (STATUS R) TIDAK PERLU DIMUNCULKAN
     */
    INSERT INTO tt_sl_r_limit_detail_mou_list(
         session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
         doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
         status_doc ,workflow_status ,status_doc_mou,
         usage_amount_tax, total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
        A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
        B.so_id AS doc_id, B.doc_type_id, B.doc_no, B.doc_date, B.partner_id, B.curr_code, B.amount AS usage_amount,
        B.status_doc AS status_doc_so ,B.workflow_status, A.status_doc AS status_doc_mou,
        B.amount_tax AS usage_amount_tax, (B.amount + B.amount_tax) AS total_usage_amount
    FROM tt_sl_mou_list A
        INNER JOIN tt_sl_so B ON A.mou_id = B.mou_id AND B.session_id = pSessionId
    WHERE A.session_id = pSessionId
        AND B.status_doc IN (vStatusReleased,vStatusFinal)
        AND NOT EXISTS(SELECT 1 FROM sl_invoice_temp C WHERE B.so_id = C.ref_id AND C.status_doc = vStatusReleased)
        AND NOT EXISTS(SELECT 1 FROM sl_invoice D WHERE B.so_id = D.ref_id AND D.status_doc = vStatusReleased);

    /*
     * TIDAK LAGI MENAMPILKAN OUTSTANDING SO -> KARENA SO YG SUDAH DI INVOICE (R) TIDAK PERLU DIMUNCULKAN
     * KRN di KATAMATA 1 SO 1 INVOICE
     *
    -- Menghapus data dr table tt_sl_outstanding_so yang mana data_id = RAW
    DELETE FROM tt_sl_outstanding_so WHERE session_id = pSessionId AND data_id = vRaw;

    -- Insert data SO By Brand yang sudah di tagging ,status doc( R/F) ke table tt_sl_r_limit_detail_mou_list
    INSERT INTO tt_sl_r_limit_detail_mou_list(
         session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
         doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
         status_doc ,workflow_status ,status_doc_mou,
         usage_amount_tax, total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
        A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
        D.so_id AS doc_id, D.doc_type_id, D.doc_no, D.doc_date, D.partner_id, D.curr_code, C.final_so AS usage_amount,
        D.status_doc AS status_doc_so ,D.workflow_status, A.status_doc AS status_doc_mou,
        C.final_tax_so AS usage_amount_tax, (C.final_so + C.final_tax_so) AS total_usage_amount
    FROM tt_sl_mou_list A
        INNER JOIN sl_so_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMou
        INNER JOIN tt_sl_outstanding_so C ON B.so_id = C.so_id AND C.data_id = vSummary
        INNER JOIN tt_sl_so D ON C.so_id = D.so_id AND D.session_id = pSessionId
    WHERE A.session_id = pSessionId
        AND C.session_id = pSessionId;
    */

    --INSERT data return yang sudah di submit (status doc R/F) ke tt_sl_r_limit_detail_mou_list
    INSERT INTO tt_sl_r_limit_detail_mou_list(
         session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
         doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,
         usage_amount ,status_doc ,workflow_status ,status_doc_mou,
         usage_amount_tax,
         total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
        A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
        C.inventory_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id,G.curr_code,
        SUM(D.qty_realization * F.nett_sell_price) AS usage_amount,C.status_doc AS status_doc_return ,
        C.workflow_status, A.status_doc AS status_doc_mou,
        SUM(D.qty_realization * F.tax_price) AS usage_amount_tax,
        (SUM(D.qty_realization * F.nett_sell_price) + SUM(D.qty_realization * F.tax_price)) AS total_usage_amount
    FROM tt_sl_mou_list A
        INNER JOIN in_return_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMOU
        INNER JOIN in_inventory C ON B.inventory_id = C.inventory_id
        INNER JOIN in_inventory_item D ON C.inventory_id = D.inventory_id
        INNER JOIN sl_request_return_sales E ON C.ref_id = E.request_return_sales_id AND C.ref_doc_type_id = vDocTypeIdRRS
        INNER JOIN sl_request_return_sales_brand_item F ON E.request_return_sales_id = F.request_return_sales_id
            AND f_get_brand_by_product_id(D.product_id) = F.brand_id
        INNER JOIN tt_sl_so G ON E.ref_id = G.so_id AND E.ref_doc_type_id = vDocTypeIdSOByBrand AND G.session_id = pSessionId
    WHERE A.session_id = pSessionId
        AND C.doc_type_id = vDocTypeIdReturnNote
        AND C.status_doc IN (vStatusReleased,vStatusFinal)
    GROUP BY A.tenant_id, A.ou_id, A.mou_id, A.doc_date , A.doc_no,A.period_from, A.period_to, A.partner_id , A.curr_code ,
        A.plafon_amount,C.inventory_id , C.doc_type_id, C.doc_no, C.doc_date, C.partner_id,G.curr_code,
        C.status_doc ,C.workflow_status, A.status_doc;

    --INSERT data return yang belum di submit (status doc D/I) ke tt_sl_r_limit_detail_mou_list
    INSERT INTO tt_sl_r_limit_detail_mou_list(
         session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
         period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
         doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,
         usage_amount ,status_doc ,workflow_status ,status_doc_mou,
         usage_amount_tax,
         total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
        A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
        C.inventory_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id,G.curr_code,
        SUM(D.qty_realization * F.nett_sell_price) AS usage_amount,C.status_doc AS status_doc_return ,
        C.workflow_status, A.status_doc AS status_doc_mou,
        SUM(D.qty_realization * F.tax_price) AS usage_amount_tax,
        (SUM(D.qty_realization * F.nett_sell_price) + SUM(D.qty_realization * F.tax_price)) AS total_usage_amount
    FROM tt_sl_mou_list A
        INNER JOIN tt_sl_r_limit_detail_mou_list B ON A.mou_id = B.mou_id AND B.doc_type_id = vDocTypeIdSOByBrand
        INNER JOIN sl_request_return_sales E ON B.doc_id = E.ref_id AND E.ref_doc_type_id = vDocTypeIdSOByBrand
        INNER JOIN sl_request_return_sales_brand_item F ON E.request_return_sales_id = F.request_return_sales_id
        INNER JOIN in_inventory C ON C.ref_id = E.request_return_sales_id AND C.ref_doc_type_id = vDocTypeIdRRS
        INNER JOIN in_inventory_item D ON C.inventory_id = D.inventory_id
        INNER JOIN tt_sl_so G ON E.ref_id = G.so_id AND E.ref_doc_type_id = vDocTypeIdSOByBrand AND G.session_id = pSessionId
    WHERE A.session_id = pSessionId
        AND B.session_id = pSessionId
        AND C.doc_type_id = vDocTypeIdReturnNote
        AND C.status_doc IN (vStatusDraft,vStatusInProgress)
    GROUP BY A.tenant_id, A.ou_id, A.mou_id, A.doc_date , A.doc_no,A.period_from, A.period_to, A.partner_id , A.curr_code ,
        A.plafon_amount,C.inventory_id , C.doc_type_id, C.doc_no, C.doc_date, C.partner_id,G.curr_code,
        C.status_doc ,C.workflow_status, A.status_doc;

    -- Insert data MOU yang sama sekali belum digunakan ke table tt_sl_r_limit_detail_mou_list
    INSERT INTO tt_sl_r_limit_detail_mou_list(
        session_id, tenant_id, ou_id, mou_id, doc_date_mou, doc_no_mou,
        period_from, period_to, partner_id_mou, curr_code_mou, plafon_amount,
        doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, usage_amount,
        status_doc, workflow_status, status_doc_mou)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
        A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
        vEmptyId, vEmptyId, vEmptyValue, vEmptyValue, A.partner_id, vEmptyValue, 0 AS usage_amount,
        vEmptyValue, vEmptyValue, A.status_doc AS status_doc_mou
    FROM tt_sl_mou_list A
    WHERE A.session_id = pSessionId
        AND A.mou_id NOT IN (SELECT DISTINCT B.mou_id FROM tt_sl_r_limit_detail_mou_list B WHERE B.session_id = pSessionId);

    /* Insert data CN AR non-insentif yang dimapping ke MOU */
    INSERT INTO tt_sl_r_limit_detail_mou_list(
        session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
        period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
        doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
        status_doc ,workflow_status ,status_doc_mou,
        usage_amount_tax, total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
        A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
        C.invoice_ar_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code, SUM(D.add_amount)*-1 AS usage_amount,
        vStatusMapping AS status_doc_so, C.workflow_status, A.status_doc AS status_doc_mou,
        SUM(D.tax_amount)*-1 AS usage_amount_tax, (SUM(D.add_amount) + SUM(D.tax_amount))*-1 AS total_usage_amount
    FROM tt_sl_mou_list A
        INNER JOIN fi_invoice_ar_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMOU
        INNER JOIN fi_invoice_ar C ON B.invoice_ar_id = C.invoice_ar_id
        INNER JOIN fi_invoice_ar_cost D ON C.invoice_ar_id = D.invoice_ar_id
    WHERE A.session_id = pSessionId
        AND C.status_doc IN (vStatusReleased,vStatusFinal)
        AND C.doc_type_id = 251
        AND f_is_invoice_ar_cost_unvalid_by_activity_gl_code(C.invoice_ar_id, vActivityGLCodeInsentif, A.tenant_id) = vFlagYes
    GROUP BY A.tenant_id, A.ou_id, A.mou_id, A.doc_date, A.doc_no,
        A.period_from, A.period_to, A.partner_id, A.curr_code, A.plafon_amount,
        C.invoice_ar_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code,
        C.workflow_status, A.status_doc;

    /* Insert data DN AR yang dimapping ke MOU */
    INSERT INTO tt_sl_r_limit_detail_mou_list(
        session_id, tenant_id,ou_id ,mou_id ,doc_date_mou,doc_no_mou,
        period_from ,period_to,partner_id_mou ,curr_code_mou ,plafon_amount ,
        doc_id,doc_type_id,doc_no,doc_date,partner_id,curr_code ,usage_amount ,
        status_doc ,workflow_status ,status_doc_mou,
        usage_amount_tax, total_usage_amount)
    SELECT pSessionId, A.tenant_id, A.ou_id, A.mou_id, A.doc_date AS doc_date_mou, A.doc_no AS doc_no_mou,
        A.period_from, A.period_to, A.partner_id AS partner_id_mou, A.curr_code AS curr_code_mou, A.plafon_amount,
        C.invoice_ar_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code, C.total_amount AS usage_amount,
        vStatusMapping AS status_doc_so, C.workflow_status, A.status_doc AS status_doc_mou,
        C.tax_amount AS usage_amount_tax, (C.total_amount + C.tax_amount) AS total_usage_amount
    FROM tt_sl_mou_list A
        INNER JOIN fi_invoice_ar_tagging B ON A.mou_id = B.tag_doc_id AND B.tag_key = vTagKeyMOU
        INNER JOIN fi_invoice_ar C ON B.invoice_ar_id = C.invoice_ar_id
    WHERE A.session_id = pSessionId
        AND C.status_doc IN (vStatusReleased,vStatusFinal)
        AND C.doc_type_id = 241;

    Open pRefHeader FOR
        SELECT 12 AS _COUNT, 'LAPORAN LIMIT CUSTOMER (DETAIL)',
            '', '', '', '', '', '',
            '', '', '', '', '',
            1 AS ordial

        UNION

        SELECT 12 AS _COUNT,
            '', '', '', '', '', '',
            '', '', '', '', '', '',
            2 AS ordial

        UNION

        SELECT 12 AS _COUNT, 'OU', ': ' || f_get_ou_name(pOuId),
            '', '', '', '',
            '', '', '', '',
            'PRINT DATE', ': ' || TO_CHAR(TO_TIMESTAMP(pDatetime, 'YYYYMMDDHH24MISS'), 'Day, DD Month, YYYY, HH:MI:SS'),
            3 AS ordial

        UNION

        SELECT 12 AS _COUNT, 'PERIOD', ': ' || TO_CHAR(TO_DATE(pPeriodFrom,'YYYYMMDD'), 'DD/MM/YYYY') || ' s/d ' || TO_CHAR(TO_DATE(pPeriodTo,'YYYYMMDD'), 'DD/MM/YYYY'),
            '', '', '', '',
            '', '', '', '',
            'USER – ROLE', ': ' || f_get_username(pUserId) || ' - ' || f_get_role_name(pRoleId),
            4 AS ordial

        UNION

        SELECT 12 AS _COUNT, 'CUSTOMER', ': ' || vPartnerCodeMou || ' / ' || vPartnerNameMou,
            '', '', '', '',
            '', '', '', '', '', '',
            5 AS ordial

        UNION

        SELECT 12 AS _COUNT,
            '', '', '', '', '', '',
            '', '', '', '', '', '',
            6 AS ordial

        UNION

        SELECT 12 AS _COUNT,
            'MOU No', 'MOU Date', 'Start Period', 'End Period',
            'Nilai Paket', 'Doc No', 'Doc Date', 'Currency',
            'Usage Amount', 'Sisa Plafon', 'Owner Code', 'Owner Name',
            7 AS ordial

        ORDER BY ordial;
    RETURN NEXT pRefHeader;


    FOR vDocNoMou IN (
        SELECT A.doc_no_mou
        FROM tt_sl_r_limit_detail_mou_list A
        GROUP BY A.doc_no_mou
    ) LOOP

        PERFORM SETVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq', 0);

        INSERT INTO tt_sl_r_limit_detail_mou_list_for_csv_detail(
            session_id, doc_no_mou,
            doc_date_mou, period_from, period_to,
            curr_code_mou, plafon_amount,
            doc_no, doc_date,
            curr_code,
            total_usage_amount,
            remaining_plafon_amount, owner_code, owner_name,
            ordial, line_no)
        SELECT pSessionId, A.doc_no_mou,
            TO_CHAR(TO_DATE(A.doc_date_mou,'YYYYMMDD'), 'DD/MM/YYYY'), TO_CHAR(TO_DATE(A.period_from,'YYYYMMDD'), 'DD/MM/YYYY'), TO_CHAR(TO_DATE(A.period_to,'YYYYMMDD'), 'DD/MM/YYYY'),
            A.curr_code_mou, CAST(A.plafon_amount AS character varying) AS plafon_amount,
            A.doc_no, TO_CHAR(TO_DATE(A.doc_date,'YYYYMMDD'), 'DD/MM/YYYY'),
            A.curr_code,
            CAST((CASE WHEN A.doc_type_id = vDocTypeIdReturnNote THEN (A.total_usage_amount * -1)
                  ELSE A.total_usage_amount
            END) AS character varying) AS total_usage_amount,
            '', B.partner_code AS owner_code, B.partner_name AS owner_name,
            i AS ordial, NEXTVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq') AS line_no
        FROM tt_sl_r_limit_detail_mou_list A
            INNER JOIN m_partner B ON A.partner_id = B.partner_id
        WHERE A.session_id = pSessionId AND
            A.doc_no_mou = vDocNoMou
            --AND A.usage_amount <> 0
        ORDER BY A.doc_no_mou,B.partner_code, A.doc_date_mou, A.doc_no, A.doc_type_id, A.doc_date ASC ;

        INSERT INTO tt_sl_r_limit_detail_mou_list_for_csv_detail(
            session_id, doc_no_mou, doc_date_mou, period_from, period_to, curr_code_mou,
            plafon_amount, doc_no, doc_date, curr_code, total_usage_amount,
            remaining_plafon_amount, owner_code, owner_name, ordial, line_no)
        SELECT pSessionId, '', '', '', '', '',
            '', 'Sub Total', '',
            A.curr_code,
            CAST(SUM(A.total_usage_amount::numeric) AS character varying) AS total_usage_amount,
            CAST((A.plafon_amount::numeric - SUM(A.total_usage_amount::numeric)) AS character varying) AS remaining_plafon_amount,
            '', '',
            i AS ordial, NEXTVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq') AS line_no
        FROM tt_sl_r_limit_detail_mou_list_for_csv_detail A
        WHERE A.session_id = pSessionId AND
            A.doc_no_mou = vDocNoMou
            --AND A.usage_amount <> 0
        GROUP BY A.doc_no_mou, A.curr_code, A.plafon_amount;

        INSERT INTO tt_sl_r_limit_detail_mou_list_for_csv_detail(
            session_id, doc_no_mou, doc_date_mou, period_from, period_to, curr_code_mou,
            plafon_amount, doc_no, doc_date, curr_code, total_usage_amount,
            remaining_plafon_amount, owner_code, owner_name, ordial, line_no)
        VALUES (pSessionId, '', '', '', '', '',
            '', '', '', '', '',
            '', '', '',
            i, NEXTVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq'));

        i := i + 1;
    END LOOP;

    PERFORM SETVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq', 0);

    WITH tt_grouped_limit_mou AS(
        SELECT A.curr_code_mou,
            A.plafon_amount::numeric AS plafon_amount,
            A.curr_code,
            SUM(A.total_usage_amount::numeric) AS total_usage_amount
        FROM tt_sl_r_limit_detail_mou_list_for_csv_detail A
        WHERE A.session_id = pSessionId AND
            A.plafon_amount <> '' AND
            A.total_usage_amount <> ''
            --AND A.usage_amount <> 0
        GROUP BY A.curr_code_mou, A.curr_code, A.plafon_amount
    )
    INSERT INTO tt_sl_r_limit_detail_mou_list_for_csv_detail(
        session_id, doc_no_mou, doc_date_mou, period_from, period_to, curr_code_mou,
        plafon_amount, doc_no, doc_date, curr_code, total_usage_amount,
        remaining_plafon_amount, owner_code, owner_name, ordial, line_no)
    SELECT pSessionId, 'Grand Total', '', '', '', A.curr_code_mou,
        CAST(SUM(A.plafon_amount) AS character varying) AS plafon_amount, '', '',
        A.curr_code,
        CAST(SUM(A.total_usage_amount) AS character varying) AS total_usage_amount,
        CAST((SUM(A.plafon_amount) - SUM(A.total_usage_amount)) AS character varying) AS remaining_plafon_amount,
        '', '',
        i AS ordial, NEXTVAL('tt_sl_r_limit_detail_mou_list_for_csv_detail_line_no_seq') AS line_no
    FROM tt_grouped_limit_mou A
        --AND A.usage_amount <> 0
    GROUP BY A.curr_code_mou, A.curr_code
    ORDER BY A.curr_code_mou, A.curr_code;

    Open pRefDetail FOR

        SELECT doc_no_mou, doc_date_mou, period_from, period_to, curr_code_mou,
            plafon_amount, doc_no, doc_date, curr_code, total_usage_amount,
            remaining_plafon_amount, owner_code, owner_name, ordial
        FROM tt_sl_r_limit_detail_mou_list_for_csv_detail A
        WHERE A.session_id = pSessionId
        ORDER BY A.ordial, A.line_no;

    RETURN NEXT pRefDetail;

    --Menghapus semua table temporrer
    DELETE FROM tt_sl_r_limit_detail_mou_list WHERE session_id = pSessionId;
    DELETE FROM tt_sl_mou_list WHERE session_id = pSessionId;
    DELETE FROM tt_sl_so WHERE session_id = pSessionId;
    DELETE FROM tt_sl_outstanding_so WHERE session_id = pSessionId;
    DELETE FROM tt_sl_r_limit_detail_mou_list_for_csv_detail WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  /