r_achievement_vs_target_csv_20180508_1

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

    pOuId                       ALIAS FOR $6;
    pSalesmanId                 ALIAS FOR $7;
    pCustomerId                 ALIAS FOR $8;
    pYear                       ALIAS FOR $9;
    pPeriod                     ALIAS FOR $10;
    pGroupBrand                 ALIAS FOR $11;

    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);
    vDocTypeIdReturnNote                bigint;
    vDocTypeIdSalesInvoice              bigint;
    vDocTypeIdInvoiceTemp               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;

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

    vCurrentDate                character varying;
    vSystemCurrentYear          character varying;
    vLastYear                   character varying;
    vEndDate                    character varying;
    vDateTo                     character varying;
    vFlgTypeSOReg               character varying;
    vZero                       numeric;
    vDocTypeIdSOB               bigint;
    vComboGroupBrand            character varying;
    vArraySeparator             character varying;
    vQuote                      character varying;
    vDocTypeIdReturnInvoice     bigint;
    vDocTypeIdCNAR              bigint;
    vDocTypeIdDNAR              bigint;
    i                           bigint;
    vFilterCustomer             character varying;
    vDecimalNo                  int;
    vFilterGroupBrand           character varying;
    vConfigSeparator            character varying;
    vConfigPrefix               character varying;
    vSalesmanLevel              bigint;
    vFilterSalesman             character varying;
    vSpaceValue                 character varying;
    vDash                       character varying;
    vPositionLevelManager       bigint;
    vSalesmanHeaderId           bigint;
    vSalesmanLevelHeader        bigint;
    vFilterSalesmanHeader       character varying;

BEGIN

    vPartnerCodeMou := ' ';
    vPartnerNameMou := ' ';
    vDocTypeIdSOByBrand := 398;
    vStatusReleased := 'R';
    vStatusFinal := 'F';
    vStatusInProgress := 'I';
    vStatusDraft := 'D';
    vDocTypeIdReturnNote := 502;
    vDocTypeIdSalesInvoice := 321;
    vDocTypeIdInvoiceTemp := 361;
    vDocTypeIdRRS :=381;
    vStatusMapping := 'M';
    vStatusApproved := 'A';
    vRaw := 'RAW';
    vSummary := 'SUMMARY';
    vEmptyId := -99;
    vEmptyValue := '';
    vEndDate := '1231';
    vFlgTypeSOReg := 'REG';
    vZero := 0;
    vDocTypeIdSOB := 398;
    vComboGroupBrand := 'GROUPBRANDPARENT';
    vArraySeparator := ', ';
    vQuote := '''';
    vDocTypeIdReturnInvoice := 380;
    vDocTypeIdCNAR := 251;
    vDocTypeIdDNAR := 241;
    i := 5; --Level salesman senior
    vFilterCustomer := '';
    vDecimalNo := 2;
    vFilterGroupBrand := '';
    vConfigSeparator := ';';
    vConfigPrefix := 'ACTIVITY_GL_GROUP_BRAND_';
    vSalesmanLevel := -99;
    vFilterSalesman := '';
    vSpaceValue := ' ';
    vDash := '-';
    vPositionLevelManager := 3;

    -- Ambil salesman dari user login (sebagai salesman leader)
    -- karena customer dan salesman yang akan dimunculkan hanya
    -- customer dan salesman dari hierarchy salesman yang sedang login,
    -- bukan dari salesman id yang dikirim.
    -- Salesman id yang dikirim, digunakan untuk memfilter
    -- salesman dari hierarchy salesman yang sedang login
    SELECT A.salesman_id
    INTO vSalesmanHeaderId
    FROM m_user_salesman_mapping A
    WHERE A.user_id = pUserId;

    -- Ambil level salesman, untuk menentukan filter pengambilan data hierarchy
    -- Note : jika hasilnya -99, artinya salesman belum diset jabatanya
    SELECT f_get_salesman_level(vSalesmanHeaderId) INTO vSalesmanLevelHeader;

    -- Tentukan filter berdasarkan salesman yang sedang login
    IF(vSalesmanLevelHeader = 1) THEN
        vFilterSalesmanHeader := ' AND A.bod_id = '||vSalesmanHeaderId||' ';
    ELSIF (vSalesmanLevelHeader = 2) THEN
        vFilterSalesmanHeader := ' AND A.saso_id = '||vSalesmanHeaderId||' ';
    ELSIF (vSalesmanLevelHeader = 3) THEN
        vFilterSalesmanHeader := ' AND A.sales_manager_id = '||vSalesmanHeaderId||' ';
    ELSIF (vSalesmanLevelHeader = 4) THEN
        vFilterSalesmanHeader := ' AND A.sales_supervisor_id = '||vSalesmanHeaderId||' ';
    ELSIF (vSalesmanLevelHeader = 5) THEN
        vFilterSalesmanHeader := ' AND A.salesman_id = '||vSalesmanHeaderId||' ';
    ELSIF (vSalesmanLevelHeader = 6) THEN
        vFilterSalesmanHeader := ' AND A.salesman_junior_id = '||vSalesmanHeaderId||' ';
    ELSE
        vFilterSalesmanHeader := ' AND FALSE ';
    END IF;

    -- Ambil tahun sekarang (sistem) dan tahun lalu dari filter tahun
    SELECT TO_CHAR(current_timestamp, 'YYYY') INTO vSystemCurrentYear;
    SELECT TO_CHAR(TO_DATE(pYear,'YYYY') - INTERVAL '1 Year','YYYY') INTO vLastYear;

    -- Jika filter tahun >= tahun sistem, data adalah data sampai tanggal sistem
    -- Jika tidak, data adalah data sampai tanggal akhir dari filter tahun
    IF (pYear >= vSystemCurrentYear) THEN
        SELECT TO_CHAR(current_timestamp, 'YYYYMMDD') INTO vDateTo;
    ELSIF (pYear < vSystemCurrentYear) THEN
        SELECT pYear || vEndDate INTO vDateTo;
    END IF;

    -- Ambil level salesman dari filter salesman
    SELECT f_get_salesman_level(pSalesmanId) INTO vSalesmanLevel;

    -- Tentukan filter berdasarkan filter salesman
    IF(vSalesmanLevel = 1) THEN
        vFilterSalesman := ' AND A.bod_id = '||pSalesmanId||' ';
    ELSIF (vSalesmanLevel = 2) THEN
        vFilterSalesman := ' AND A.saso_id = '||pSalesmanId||' ';
    ELSIF (vSalesmanLevel = 3) THEN
        vFilterSalesman := ' AND A.sales_manager_id = '||pSalesmanId||' ';
    ELSIF (vSalesmanLevel = 4) THEN
        vFilterSalesman := ' AND A.sales_supervisor_id = '||pSalesmanId||' ';
    ELSIF (vSalesmanLevel = 5) THEN
        vFilterSalesman := ' AND A.salesman_id = '||pSalesmanId||' ';
    ELSIF (vSalesmanLevel = 6) THEN
        vFilterSalesman := ' AND A.salesman_junior_id = '||pSalesmanId||' ';
    END IF;

    -- Tentukan filter customer berdasarkan filter
    IF (pCustomerId <> vEmptyId) THEN
        vFilterCustomer := ' AND A.customer_id = '|| pCustomerId ||' ';
    END IF;

    -- Tentukan filter group brand berdasarkan filter
    IF (pGroupBrand <> vEmptyValue) THEN
        vFilterGroupBrand := ' AND A.group_brand = '''|| pGroupBrand ||''' ';
    END IF;

    -- Hapus isi table temporary
    DELETE FROM tt_salesman_customer_info_for_report_achievement_and_target WHERE session_id = pSessionId;
    DELETE FROM tt_achievement_transaction_for_report_achievement_and_target WHERE session_id = pSessionId;
    DELETE FROM tt_summary_for_report_achievement_and_target WHERE session_id = pSessionId;

    -- Ambil data salesman dan customer dari hierarchy berdasarkan filter
    -- Salesman yang meng-handle customer adalah salesman dengan level terendah dalam hierarchy tersebut
    EXECUTE
        'WITH tt_salesman_customer AS(
            SELECT A.tenant_id, A.customer_id, B.partner_code AS customer_code,
                B.partner_name AS customer_name, G.city AS customer_city,
                A.salesman_junior_id, A.salesman_id, A.sales_supervisor_id,
                A.sales_manager_id, A.saso_id, A.bod_id,
                CASE WHEN (salesman_junior_id <> $4) THEN
                    salesman_junior_id
                WHEN (salesman_id <> $4) THEN
                    salesman_id
                WHEN (sales_supervisor_id <> $4) THEN
                    sales_supervisor_id
                WHEN (sales_manager_id <> $4) THEN
                    sales_manager_id
                WHEN (saso_id <> $4) THEN
                    saso_id
                ELSE
                    bod_id
                END AS salesman_handle_id, A.group_brand,
                $6, $6, $6, $6, $11
            FROM m_mapping_salesman_hierarchy_customer A
            INNER JOIN m_partner B ON A.customer_id = B.partner_id
            INNER JOIN m_ctgr_partner C ON A.tenant_id = C.tenant_id AND B.ctgr_partner_id = C.ctgr_partner_id
            INNER JOIN t_user_role D ON D.user_id = $7 AND D.role_id = $8
            INNER JOIN m_policy_partner E ON B.ctgr_partner_id = E.ctgr_partner_id AND D.user_role_id = E.user_role_id
            INNER JOIN m_partner_address G ON A.tenant_id = G.tenant_id AND A.customer_id = G.partner_id AND G.flg_default = $9
            WHERE A.tenant_id = 10
                AND $3 BETWEEN A.date_from AND A.date_to
                AND $12 <> $4
                AND A.active = $9 '
                || vFilterSalesmanHeader
                || vFilterSalesman
                || vFilterCustomer
                || vFilterGroupBrand ||
        ')
        INSERT INTO tt_salesman_customer_info_for_report_achievement_and_target(
            session_id, tenant_id, customer_id, customer_code, customer_name,
            customer_city, salesman_junior_id, salesman_id, sales_supervisor_id,
            sales_manager_id, saso_id, bod_id, salesman_handle_id, salesman_handle_code,
            salesman_handle_name, salesman_handle_level, group_brand, curr_code,
            target_sales, q1_amount, q2_amount, q3_amount, flg_target
        )
        SELECT $1, A.tenant_id, A.customer_id, A.customer_code, A.customer_name,
            A.customer_city, A.salesman_junior_id, A.salesman_id, A.sales_supervisor_id,
            A.sales_manager_id, A.saso_id, A.bod_id, A.salesman_handle_id, f_get_partner_code(A.salesman_handle_id) AS salesman_handle_code,
            f_get_partner_name(A.salesman_handle_id) AS salesman_handle_name, f_get_salesman_level(A.salesman_handle_id) AS salesman_handle_level, A.group_brand, $4,
            $6, $6, $6, $6, $11
        FROM tt_salesman_customer A
        WHERE EXISTS (
            SELECT 1
            FROM m_partner_ou D
            INNER JOIN m_ou_structure E
                ON ( E.ou_bu_id = D.ou_id
                    OR E.ou_branch_id = D.ou_id
                    OR E.ou_sub_bu_id = D.ou_id )
            WHERE A.salesman_handle_id = D.partner_id
                AND e.ou_id = $10
        ) AND f_get_salesman_level(A.salesman_handle_id) >= $13'
    USING pSessionId, pTenantId, pPeriod, vEmptyId, vEmptyValue, vZero, pUserId, pRoleId, vFlagYes, pOuId, vFlagNo, vSalesmanLevel, vPositionLevelManager;

    -- Update target dari tiap customer
    -- Jika tidak memiliki target, tidak ditampilkan
    UPDATE tt_salesman_customer_info_for_report_achievement_and_target A
    SET curr_code = B.curr_code,
        target_sales = B.target_sales,
        q1_amount = B.q1_amount,
        q2_amount = B.q2_amount,
        q3_amount = B.q3_amount,
        flg_target = vFlagYes
    FROM m_customer_target_sales B
    WHERE B.tenant_id = A.tenant_id
        AND A.customer_id = B.customer_id
        AND B.year = pYear
        AND B.group_brand = A.group_brand;

    --Temporary Sales Invoice
    EXECUTE
    'INSERT INTO tt_achievement_transaction_for_report_achievement_and_target (
        session_id, tenant_id, ou_id,
        doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, amount,
        status_doc, workflow_status,
        group_brand_parent_code
    )
    SELECT DISTINCT ON (D.so_id) $1, A.tenant_id, C.ou_id,
        C.invoice_temp_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, B.curr_code, B.amount,
        C.status_doc AS status_doc_invoice_temp, C.workflow_status,
        G.code
    FROM tt_salesman_customer_info_for_report_achievement_and_target A
    INNER JOIN fi_invoice_ar_balance B ON B.partner_id = A.customer_id
    INNER JOIN sl_invoice_temp C ON C.invoice_temp_id = B.invoice_ar_id
    INNER JOIN sl_so_info D ON D.so_id = C.ref_id
    INNER JOIN sl_so_brand_item E ON E.so_id = D.so_id
    INNER JOIN m_brand_ext F ON F.brand_id = E.brand_id
    INNER JOIN t_combo_value G ON A.group_brand = G.code
    WHERE A.session_id = $1 AND
        C.tenant_id = $2 AND
        C.ou_id = $3 AND
        B.doc_type_id = $4 AND
        C.ref_doc_type_id = $5 AND
        D.flg_type_so = $6 AND
        SUBSTR(C.doc_date, 1, 4) BETWEEN $7 AND $8 AND
        G.combo_id = $9 AND
        $11 || F.group_brand || $11 = ANY (string_to_array (G.prop_key, $10))'
    USING pSessionId, pTenantId, pOuId, vDocTypeIdInvoiceTemp, vDocTypeIdSOB, vFlgTypeSOReg, vLastYear, pYear, vComboGroupBrand, vArraySeparator, vQuote;

    --Sales Invoice
    EXECUTE
    'INSERT INTO tt_achievement_transaction_for_report_achievement_and_target (
        session_id, tenant_id, ou_id,
        doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, amount,
        status_doc, workflow_status,
        group_brand_parent_code
    )
    SELECT DISTINCT ON (D.so_id) $1, A.tenant_id, C.ou_id,
        C.invoice_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, B.curr_code, B.amount,
        C.status_doc AS status_doc_invoice_temp, C.workflow_status,
        G.code
    FROM tt_salesman_customer_info_for_report_achievement_and_target A
    INNER JOIN fi_invoice_ar_balance B ON B.partner_id = A.customer_id
    INNER JOIN sl_invoice C ON C.invoice_id = B.invoice_ar_id
    INNER JOIN sl_so_info D ON D.so_id = C.ref_id
    INNER JOIN sl_so_brand_item E ON E.so_id = D.so_id
    INNER JOIN m_brand_ext F ON F.brand_id = E.brand_id
    INNER JOIN t_combo_value G ON A.group_brand = G.code
    WHERE A.session_id = $1 AND
        C.tenant_id = $2 AND
        C.ou_id = $3 AND
        B.doc_type_id = $4 AND
        C.ref_doc_type_id = $5 AND
        D.flg_type_so = $6 AND
        SUBSTR(C.doc_date, 1, 4) BETWEEN $7 AND $8 AND
        G.combo_id = $9 AND
        $11 || F.group_brand || $11 = ANY (string_to_array (G.prop_key, $10))'
    USING pSessionId, pTenantId, pOuId, vDocTypeIdSalesInvoice, vDocTypeIdSOB, vFlgTypeSOReg, vLastYear, pYear, vComboGroupBrand, vArraySeparator, vQuote;

    --Return
    EXECUTE
    'INSERT INTO tt_achievement_transaction_for_report_achievement_and_target (
        session_id, tenant_id, ou_id,
        doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, amount,
        status_doc, workflow_status,
        group_brand_parent_code
    )
    SELECT DISTINCT ON (D.request_return_sales_id) $1, A.tenant_id, C.ou_id,
        C.invoice_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, B.curr_code, B.amount,
        C.status_doc AS status_doc_invoice_temp, C.workflow_status,
        G.code
    FROM tt_salesman_customer_info_for_report_achievement_and_target A
    INNER JOIN fi_invoice_ar_balance B ON B.partner_id = A.customer_id
    INNER JOIN sl_invoice C ON C.invoice_id = B.invoice_ar_id
    INNER JOIN sl_request_return_sales_brand_item D ON D.request_return_sales_id = C.ref_id
    INNER JOIN m_brand_ext F ON F.brand_id = D.brand_id
    INNER JOIN t_combo_value G ON A.group_brand = G.code
    WHERE A.session_id = $1 AND
        C.tenant_id = $2 AND
        C.ou_id = $3 AND
        B.doc_type_id = $4 AND
        C.ref_doc_type_id = $5 AND
        SUBSTR(C.doc_date, 1, 4) BETWEEN $7 AND $8 AND
        G.combo_id = $9 AND
        $11 || F.group_brand || $11 = ANY (string_to_array (G.prop_key, $10))'
    USING pSessionId, pTenantId, pOuId, vDocTypeIdReturnInvoice, vDocTypeIdRRS, vFlgTypeSOReg, vLastYear, pYear, vComboGroupBrand, vArraySeparator, vQuote;

    --CN AR
    EXECUTE
    'INSERT INTO tt_achievement_transaction_for_report_achievement_and_target (
        session_id, tenant_id, ou_id,
        doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, amount,
        status_doc, workflow_status,
        group_brand_parent_code
    )
    SELECT $1, A.tenant_id, C.ou_id,
        C.invoice_ar_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, B.curr_code, B.amount,
        C.status_doc AS status_doc_invoice_temp, C.workflow_status,
        G.code
    FROM tt_salesman_customer_info_for_report_achievement_and_target A
    INNER JOIN fi_invoice_ar_balance B ON B.partner_id = A.customer_id
    INNER JOIN fi_invoice_ar C ON C.invoice_ar_id = B.invoice_ar_id
    INNER JOIN t_combo_value G ON A.group_brand = G.code
    WHERE A.session_id = $1 AND
        C.tenant_id = $2 AND
        C.ou_id = $3 AND
        B.doc_type_id = $4 AND
        SUBSTR(C.doc_date, 1, 4) BETWEEN $7 AND $8 AND
        G.combo_id = $9 AND
        EXISTS(
            SELECT 1
            FROM fi_invoice_ar_cost D
            WHERE D.invoice_ar_id = C.invoice_ar_id AND
                f_get_activity_gl_code(D.activity_gl_id) = ANY (string_to_array (f_get_value_system_config_by_param_code($2, $13 || G.code), $12))
        )'
    USING pSessionId, pTenantId, pOuId, vDocTypeIdCNAR, vDocTypeIdCNAR, vFlgTypeSOReg, vLastYear, pYear, vComboGroupBrand, vArraySeparator, vQuote, vConfigSeparator, vConfigPrefix;

    --DN AR
    EXECUTE
    'INSERT INTO tt_achievement_transaction_for_report_achievement_and_target (
        session_id, tenant_id, ou_id,
        doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, amount,
        status_doc, workflow_status,
        group_brand_parent_code
    )
    SELECT $1, A.tenant_id, C.ou_id,
        C.invoice_ar_id AS doc_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, B.curr_code, B.amount,
        C.status_doc AS status_doc_invoice_temp, C.workflow_status,
        G.code
    FROM tt_salesman_customer_info_for_report_achievement_and_target A
    INNER JOIN fi_invoice_ar_balance B ON B.partner_id = A.customer_id
    INNER JOIN fi_invoice_ar C ON C.invoice_ar_id = B.invoice_ar_id
    INNER JOIN t_combo_value G ON A.group_brand = G.code
    WHERE A.session_id = $1 AND
        C.tenant_id = $2 AND
        C.ou_id = $3 AND
        B.doc_type_id = $4 AND
        SUBSTR(C.doc_date, 1, 4) BETWEEN $7 AND $8 AND
        G.combo_id = $9 AND
        EXISTS(
            SELECT 1
            FROM fi_invoice_ar_cost D
            WHERE D.invoice_ar_id = C.invoice_ar_id AND
                f_get_activity_gl_code(D.activity_gl_id) = ANY (string_to_array (f_get_value_system_config_by_param_code($2, $13 || G.code), $12))
        )'
    USING pSessionId, pTenantId, pOuId, vDocTypeIdDNAR, vDocTypeIdDNAR, vFlgTypeSOReg, vLastYear, pYear, vComboGroupBrand, vArraySeparator, vQuote, vConfigSeparator, vConfigPrefix;

    -- Ambil data sales dan sum berdasarkan tiap level untuk perhitungan summary
    -- Jumlah achievement adalah total tahun ini
    WITH tt_grouped_summary AS (
        SELECT A.tenant_id, A.sales_manager_id AS salesman_id,
            A.target_sales AS target,
            SUM(COALESCE(B.amount, 0)) AS year_to_date_accumulative_amount,
            3 AS salesman_level
        FROM tt_salesman_customer_info_for_report_achievement_and_target A
        LEFT JOIN tt_achievement_transaction_for_report_achievement_and_target B
            ON B.partner_id = A.customer_id AND
                B.tenant_id = A.tenant_id AND
                SUBSTR(B.doc_date, 1, 4) = pYear AND
                B.group_brand_parent_code = A.group_brand AND
                A.session_id = B.session_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.flg_target = vFlagYes
        GROUP BY A.tenant_id, A.sales_manager_id, A.target_sales

        UNION

        SELECT A.tenant_id, A.sales_supervisor_id AS salesman_id,
            A.target_sales AS target,
            SUM(COALESCE(B.amount, 0)) AS year_to_date_accumulative_amount,
            4 AS salesman_level
        FROM tt_salesman_customer_info_for_report_achievement_and_target A
        LEFT JOIN tt_achievement_transaction_for_report_achievement_and_target B
            ON B.partner_id = A.customer_id AND
                B.tenant_id = A.tenant_id AND
                SUBSTR(B.doc_date, 1, 4) = pYear AND
                B.group_brand_parent_code = A.group_brand AND
                A.session_id = B.session_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.flg_target = vFlagYes
        GROUP BY A.tenant_id, A.sales_supervisor_id, A.target_sales

        UNION

        SELECT A.tenant_id, A.salesman_id AS salesman_id,
            A.target_sales AS target,
            SUM(COALESCE(B.amount, 0)) AS year_to_date_accumulative_amount,
            5 AS salesman_level
        FROM tt_salesman_customer_info_for_report_achievement_and_target A
        LEFT JOIN tt_achievement_transaction_for_report_achievement_and_target B
            ON B.partner_id = A.customer_id AND
                B.tenant_id = A.tenant_id AND
                SUBSTR(B.doc_date, 1, 4) = pYear AND
                B.group_brand_parent_code = A.group_brand AND
                A.session_id = B.session_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.flg_target = vFlagYes
        GROUP BY A.tenant_id, A.salesman_id, A.target_sales

        UNION

        SELECT A.tenant_id, A.salesman_junior_id AS salesman_id,
            A.target_sales AS target,
            SUM(COALESCE(B.amount, 0)) AS year_to_date_accumulative_amount,
            6 AS salesman_level
        FROM tt_salesman_customer_info_for_report_achievement_and_target A
        LEFT JOIN tt_achievement_transaction_for_report_achievement_and_target B
            ON B.partner_id = A.customer_id AND
                B.tenant_id = A.tenant_id AND
                SUBSTR(B.doc_date, 1, 4) = pYear AND
                B.group_brand_parent_code = A.group_brand AND
                A.session_id = B.session_id
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            A.flg_target = vFlagYes
        GROUP BY A.tenant_id, A.salesman_junior_id, A.target_sales
    )
    INSERT INTO tt_summary_for_report_achievement_and_target (
        session_id, tenant_id,
        salesman_id,
        target_amount,
        year_to_date_accumulative_amount,
        salesman_level
    )
    SELECT pSessionId, A.tenant_id,
        A.salesman_id,
        SUM(A.target) AS target,
        SUM(A.year_to_date_accumulative_amount) AS year_to_date_accumulative_amount,
        salesman_level
    FROM tt_grouped_summary A
    GROUP BY A.tenant_id, A.salesman_id, A.salesman_level;

    -- Header
    Open pRefHeader FOR
        SELECT 21 AS _COUNT,
            'Nama', 'Target', 'YTD Acv', '% YTD Acv', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '', '',
            ';' AS _separator;
    RETURN NEXT pRefHeader;

    -- Set sequence untuk line no menjadi 0
    PERFORM SETVAL('r_achievement_vs_target_line_no_seq', 0);

    -- Detail
    -- tt_ordered_achievement adalah data detail yang diurutkan
    --      Hanya tampilkan nama salesman tiap level (manager, supervisor, salesman senior, salesman junior)
    --          jika levelnya >= level salesman filter
    --      Achievement tiap quarter dikelompokkan berdasarkan doc date
    -- data_detail berisi data summary yang digabungkan dengan tt_ordered_achievement
    -- summary_detail berisi data total dari kolom-kolom pada detail
    Open pRefDetail FOR
        WITH tt_ordered_achievement AS (
            SELECT B.customer_code, B.customer_name, B.customer_city,
                CASE WHEN ((f_get_partner_name(B.sales_manager_id) <> vSpaceValue)
                        AND (vSalesmanLevel <= f_get_salesman_level(B.sales_manager_id))) THEN
                    f_get_partner_name(B.sales_manager_id)
                ELSE
                    vDash
                END AS manager_name,
                CASE WHEN ((f_get_partner_name(B.sales_supervisor_id) <> vSpaceValue)
                        AND (vSalesmanLevel <= f_get_salesman_level(B.sales_supervisor_id))) THEN
                    f_get_partner_name(B.sales_supervisor_id)
                ELSE
                    vDash
                END AS supervisor_name,
                CASE WHEN ((f_get_partner_name(B.salesman_id) <> vSpaceValue)
                        AND (vSalesmanLevel <= f_get_salesman_level(B.salesman_id))) THEN
                    f_get_partner_name(B.salesman_id)
                ELSE
                    vDash
                END AS senior_sales_name,
                CASE WHEN ((f_get_partner_name(B.salesman_junior_id) <> vSpaceValue)
                        AND (vSalesmanLevel <= f_get_salesman_level(B.salesman_junior_id))) THEN
                    f_get_partner_name(B.salesman_junior_id)
                ELSE
                    vDash
                END AS junior_sales_name,
                ROUND(SUM(
                    CASE WHEN C.year_date = vLastYear THEN
                        COALESCE(A.amount, 0)
                    ELSE
                        0
                    END
                ), vDecimalNo) AS last_year_achievement_amount,
                B.target_sales AS current_year_target_amount, B.q1_amount, B.q2_amount, B.q3_amount,
                ROUND(SUM(
                    CASE WHEN C.year_date = pYear THEN
                        COALESCE(A.amount, 0)
                    ELSE
                        0
                    END
                ), vDecimalNo) AS year_to_date_amount,
                ROUND(SUM(
                    CASE WHEN C.q_no = 1 AND C.year_date = pYear THEN
                        COALESCE(A.amount, 0)
                    ELSE
                        0
                    END
                ), vDecimalNo) AS q1_achievement_amount,
                ROUND(SUM(
                    CASE WHEN C.q_no = 2 AND C.year_date = pYear THEN
                        COALESCE(A.amount, 0)
                    ELSE
                        0
                    END
                ), vDecimalNo) AS q2_achievement_amount,
                ROUND(SUM(
                    CASE WHEN C.q_no = 3 AND C.year_date = pYear THEN
                        COALESCE(A.amount, 0)
                    ELSE
                        0
                    END
                ), vDecimalNo) AS q3_achievement_amount
            FROM tt_salesman_customer_info_for_report_achievement_and_target B
            LEFT JOIN tt_achievement_transaction_for_report_achievement_and_target A
                ON A.partner_id = B.customer_id AND
                    A.tenant_id = B.tenant_id AND
                    A.group_brand_parent_code = B.group_brand AND
                    A.partner_id = B.customer_id AND
                    A.session_id = B.session_id
            LEFT JOIN dt_date C ON A.doc_date = C.string_date
            WHERE B.session_id = pSessionId
                AND (B.customer_code <> vEmptyValue OR B.customer_name <> vEmptyValue)
                AND B.flg_target = vFlagYes
            GROUP BY B.customer_code, B.customer_name, B.customer_city,
                B.sales_manager_id, B.sales_supervisor_id, B.salesman_id, B.salesman_junior_id,
                B.target_sales, B.q1_amount, B.q2_amount, B.q3_amount, group_brand_parent_code
            ORDER BY manager_name, supervisor_name, senior_sales_name, customer_city, year_to_date_amount DESC
        ), data_detail AS (

            SELECT f_get_partner_name(A.salesman_id) AS no, ROUND(SUM(A.target_amount), vDecimalNo)::character varying AS code,
                ROUND(SUM(A.year_to_date_accumulative_amount), vDecimalNo)::character varying AS cust_name,
                (CASE WHEN SUM(A.target_amount) > 0 THEN ROUND(SUM(A.year_to_date_accumulative_amount) * 100 / SUM(A.target_amount), vDecimalNo) ELSE 100 END)::character varying AS kota,
                vEmptyValue AS manager, vEmptyValue AS spv, vEmptyValue AS senior_sales, vEmptyValue AS junior_sales,
                vEmptyValue AS last_year_achv, vEmptyValue AS target_current_year, vEmptyValue AS target_q1,
                vEmptyValue AS target_q2, vEmptyValue AS target_q3, vEmptyValue AS achievment_amount_ytd,
                vEmptyValue AS achievment_ytd_percentage, vEmptyValue AS achievment_amount_q1,
                vEmptyValue AS achievment_q1_percentage, vEmptyValue AS achievment_amount_q2,
                vEmptyValue AS achievment_q2_percentage, vEmptyValue AS achievment_amount_q3,
                vEmptyValue AS achievment_q3_percentage, 1 AS ordial
            FROM tt_summary_for_report_achievement_and_target A
            WHERE A.session_id = pSessionId AND
                A.tenant_id = pTenantId AND
                A.salesman_id <> vEmptyId AND
                A.salesman_level >= vSalesmanLevel
            GROUP BY A.tenant_id, A.salesman_id, A.salesman_level

            UNION ALL

            SELECT vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,
            vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue,vEmptyValue, 2 AS ordial

            UNION ALL

            SELECT 'No', 'Code', 'Cust. Name', 'Kota',
                'Manager', 'SPV', 'Senior Sales', 'Junior Sales',
                'Last Year Achv ('||vLastYear||')', 'Target '||pYear,
                'Target Q1 '||pYear, 'Target Q2 '||pYear, 'Target Q3 '||pYear,
                'ACHIEVEMENT Amount YTD', 'ACHIEVEMENT YTD %',
                'ACHIEVEMENT Amount Q1', 'ACHIEVEMENT Q1 %', 'ACHIEVEMENT Amount Q2', 'ACHIEVEMENT Q2 %', 'ACHIEVEMENT Amount Q3', 'ACHIEVEMENT Q3 %', 3 AS ordial

            UNION ALL

            SELECT (row_number() OVER ())::character varying AS no, A.customer_code, A.customer_name, A.customer_city,
                A.manager_name, A.supervisor_name, A.senior_sales_name, A.junior_sales_name,
                A.last_year_achievement_amount::character varying, A.current_year_target_amount::character varying,
                ROUND(A.q1_amount, vDecimalNo)::character varying, ROUND(A.q2_amount, vDecimalNo)::character varying, ROUND(A.q3_amount, vDecimalNo)::character varying,
                A.year_to_date_amount::character varying, (CASE WHEN A.current_year_target_amount > 0 THEN ROUND(A.year_to_date_amount * 100 / A.current_year_target_amount, vDecimalNo) ELSE 100 END)::character varying,
                A.q1_achievement_amount::character varying, (CASE WHEN A.q1_amount > 0 THEN ROUND(A.q1_achievement_amount * 100 / A.q1_amount, vDecimalNo) ELSE 100 END)::character varying,
                A.q2_achievement_amount::character varying, (CASE WHEN A.q2_amount > 0 THEN ROUND(A.q2_achievement_amount * 100 / A.q2_amount, vDecimalNo) ELSE 100 END)::character varying,
                A.q3_achievement_amount::character varying, (CASE WHEN A.q3_amount > 0 THEN ROUND(A.q3_achievement_amount * 100 / A.q3_amount, vDecimalNo) ELSE 100 END)::character varying,
                row_number() OVER () + 3 AS ordial
            FROM tt_ordered_achievement A

            ORDER BY ordial, no

        ), summary_detail AS (
            SELECT ROUND(SUM(A.last_year_achievement_amount), vDecimalNo)::character varying AS last_year_achv,
                    ROUND(SUM(A.current_year_target_amount), vDecimalNo)::character varying AS target_current_year,
                    ROUND(SUM(A.q1_amount), vDecimalNo)::character varying AS target_q1,
                    ROUND(SUM(A.q2_amount), vDecimalNo)::character varying AS target_q2,
                    ROUND(SUM(A.q3_amount), vDecimalNo)::character varying AS target_q3,
                    ROUND(SUM(A.year_to_date_amount), vDecimalNo)::character varying AS achievment_amount_ytd,
                    (CASE WHEN SUM(A.current_year_target_amount) > 0 THEN ROUND(SUM(A.year_to_date_amount) * 100 / SUM(A.current_year_target_amount), vDecimalNo) ELSE 100 END)::character varying AS achievment_ytd_percentage,
                    ROUND(SUM(A.q1_achievement_amount), vDecimalNo)::character varying AS achievment_amount_q1,
                    (CASE WHEN SUM(A.q1_amount) > 0 THEN ROUND(SUM(A.q1_achievement_amount) * 100 / SUM(A.q1_amount), vDecimalNo) ELSE 100 END)::character varying AS achievment_q1_percentage,
                    ROUND(SUM(A.q2_achievement_amount), vDecimalNo)::character varying AS achievment_amount_q2,
                    (CASE WHEN SUM(A.q2_amount) > 0 THEN ROUND(SUM(A.q2_achievement_amount) * 100 / SUM(A.q2_amount), vDecimalNo) ELSE 100 END)::character varying AS achievment_q2_percentage,
                    ROUND(SUM(A.q3_achievement_amount), vDecimalNo)::character varying AS achievment_amount_q3,
                    (CASE WHEN SUM(A.q3_amount) > 0 THEN ROUND(SUM(A.q3_achievement_amount) * 100 / SUM(A.q3_amount), vDecimalNo) ELSE 100 END)::character varying AS achievment_q3_percentage
            FROM tt_ordered_achievement A
        )

        SELECT A.no, A.code, A.cust_name, A.kota, A.manager, A.spv, A.senior_sales, A.junior_sales,
            A.last_year_achv, A.target_current_year, A.target_q1, A.target_q2, A.target_q3,
            A.achievment_amount_ytd, A.achievment_ytd_percentage, A.achievment_amount_q1,
            A.achievment_q1_percentage, A.achievment_amount_q2, A.achievment_q2_percentage,
            A.achievment_amount_q3, A.achievment_q3_percentage
        FROM data_detail A
        UNION ALL
        SELECT 'Total', vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue, vEmptyValue,
            COALESCE(A.last_year_achv, '0'), COALESCE(A.target_current_year, '0'), COALESCE(A.target_q1, '0'), COALESCE(A.target_q2, '0'), COALESCE(A.target_q3, '0'),
            COALESCE(A.achievment_amount_ytd, '0'),
            CASE WHEN (A.target_current_year IS NULL AND A.achievment_amount_ytd IS NULL) THEN '0' ELSE A.achievment_ytd_percentage END AS achievment_ytd_percentage,
            COALESCE(A.achievment_amount_q1, '0'),
            CASE WHEN (A.target_q1 IS NULL AND A.achievment_amount_q1 IS NULL) THEN '0' ELSE A.achievment_q1_percentage END AS achievment_q1_percentage,
            COALESCE(A.achievment_amount_q2, '0'),
            CASE WHEN (A.target_q2 IS NULL AND A.achievment_amount_q2 IS NULL) THEN '0' ELSE A.achievment_q2_percentage END AS achievment_q2_percentage,
            COALESCE(A.achievment_amount_q3, '0'),
            CASE WHEN (A.target_q3 IS NULL AND A.achievment_amount_q3 IS NULL) THEN '0' ELSE A.achievment_q3_percentage END AS achievment_q3_percentage
        FROM summary_detail A;

    RETURN NEXT pRefDetail;

    -- Hapus isi table temporary
    DELETE FROM tt_salesman_customer_info_for_report_achievement_and_target WHERE session_id = pSessionId;
    DELETE FROM tt_achievement_transaction_for_report_achievement_and_target WHERE session_id = pSessionId;
    DELETE FROM tt_summary_for_report_achievement_and_target WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  /