r_achievement_vs_target_20180312

CREATE OR REPLACE FUNCTION r_achievement_vs_target(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, 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;

    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';

    vCurrentDate                character varying;
    vSystemCurrentYear          character varying;
    vLastYear                   character varying;
    vEndDate                    character varying;
    vZero                       numeric;

BEGIN

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

    -- 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;

    SELECT TO_CHAR(current_timestamp, 'YYYY') INTO vSystemCurrentYear;
    SELECT TO_CHAR(TO_DATE(pYear,'YYYY') - INTERVAL '1 Year','YYYY') INTO vLastYear;

    IF (pYear = vSystemCurrentYear) THEN
        SELECT TO_CHAR(current_timestamp, 'YYYYMMDD') INTO vDateTo;
    ELSE IF (pYear < vSystemCurrentYear)
        SELECT vLastYear || vEndDate INTO vDateTo;
    END IF;


    DELETE FROM tt_salesman_from_salesman_hierarchy WHERE session_id = pSessionId;

    --Mendapatkan daftar salesman dalam hierarchy periode
    PERFORM f_get_salesman_from_salesman_hierarchy(pSessionId, pTenantId, pSalesmanId, pPeriod);

    WITH grouped_salesman AS (
        SELECT DISTINCT A.tenant_id, A.sales_child_id, A.group_brand
        FROM tt_salesman_from_salesman_hierarchy A
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId AND
            pPeriod BETWEEN A.period_from AND A.period_to
    )
    INSERT INTO tt_salesman_customer_info_for_report_achievement_and_target (
        session_id, customer_id, customer_code, customer_name,
        salesman_id, salesman_code, salesman_name, group_brand,
        target, q1_amount, q2_amount, q3_amount
    )
    SELECT pSessionId, vEmptyId, vEmptyValue, vEmptyValue,
        A.sales_child_id, C.partner_code, C.partner_name, A.group_brand,
        vZero, vZero, vZero, vZero
    FROM grouped_salesman A
    INNER JOIN m_partner C
        ON A.sales_child_id = C.partner_id
            AND A.tenant_id = C.tenant_id
    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 C.partner_id = D.partner_id
            AND e.ou_id = pOuId
    )

    UPDATE tt_salesman_customer_info_for_report_achievement_and_target A
    SET customer_id = A.customer_id,
        customer_code = C.partner_code,
        customer_name = C.partner_name
    FROM m_salesman_to_customer B
    INNER JOIN m_partner C
        ON B.customer_id = C.partner_id
            AND B.tenant_id = C.tenant_id
    INNER JOIN m_ctgr_partner D
        ON D.tenant_id = C.tenant_id
            AND D.ctgr_partner_id = C.ctgr_partner_id
    INNER JOIN t_user_role E
        ON E.user_id = pUserId
            AND E.role_id = pRoleId
    INNER JOIN m_policy_partner F
        ON F.ctgr_partner_id = D.ctgr_partner_id
            AND F.user_role_id = E.user_role_id;

    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,
        q2_amount = B.q2_amount
    FROM m_customer_target_sales B
    WHERE B.tenant_id = A.tenant_id
        AND A.customer_id = B.customer_id
        AND B.year = pYear;

    --Temporary Sales Invoice
    /*INSERT INTO tt_achievement_transaction (
        session_id, tenant_id, ou_id,
        doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, amount,
        status_doc, workflow_status,
        tax_amount, total_amount
    )
    SELECT pSessionId, A.tenant_id, D.ou_id,
        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 amount,
        D.status_doc AS status_doc_invoice_temp ,D.workflow_status,
        COALESCE(E.tax_amount, 0) AS tax_amount, (D.total_amount + COALESCE(E.tax_amount, 0)) AS total_amount
    FROM tt_salesman_customer_info_for_report_achievement_and_target A
    INNER JOIN sl_invoice_temp D ON A.customer_id = D.partner_id
    LEFT JOIN sl_invoice_temp_tax E ON D.invoice_temp_id = E.invoice_temp_id
    WHERE A.session_id = pSessionId
        AND D.tenant_id = pTenantId
        AND D.ou_id = pOuId
        AND D.doc_type_id = vDocTypeIdInvoiceTemp
        AND D.status_doc = vStatusReleased
        AND SUBSTR(D.doc_date, 1, 4) BETWEEN vLastYear AND pYear
        AND NOT EXISTS (
            SELECT 1
            FROM sl_invoice F
            WHERE F.doc_no = D.inv_doc_no
                AND F.ref_id = D.ref_id
                AND F.doc_type_id = vDocTypeIdSalesInvoice
                AND F.status_doc = vStatusReleased
        );

    -- Sales Invoice
    INSERT INTO tt_achievement_transaction (
        session_id, tenant_id, ou_id,
        doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, amount,
        status_doc, workflow_status,
        tax_amount, total_amount
    )
    SELECT pSessionId, 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, C.curr_code, C.total_amount AS amount,
        C.status_doc AS status_doc_invoice, C.workflow_status,
        COALESCE(D.tax_amount, 0) AS tax_amount, (C.total_amount + COALESCE(D.tax_amount, 0)) AS total_amount
    FROM tt_salesman_customer_info_for_report_achievement_and_target A
    INNER JOIN sl_invoice C ON A.customer_id = C.partner_id
    LEFT JOIN sl_invoice_tax D ON C.invoice_id = D.invoice_id
    WHERE A.session_id = pSessionId
        AND C.tenant_id = pTenantId
        AND C.ou_id = pOuId
        AND C.invoice_temp_id = vEmptyId
        AND C.doc_type_id = vDocTypeIdSalesInvoice
        AND C.status_doc = vStatusReleased
        AND SUBSTR(C.doc_date, 1, 4) BETWEEN vLastYear AND pYear;

    --Return
    INSERT INTO tt_achievement_transaction (
        session_id, tenant_id, ou_id,
        doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, amount,
        status_doc, workflow_status,
        tax_amount, total_amount
    )
    SELECT pSessionId, 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, C.curr_code, -1 * C.total_amount AS amount,
        C.status_doc AS status_doc_invoice, C.workflow_status,
        COALESCE(-1 * D.tax_amount, 0) AS tax_amount, -1 * (C.total_amount + COALESCE(D.tax_amount, 0)) AS total_amount
    FROM tt_salesman_customer_info_for_report_achievement_and_target A
    INNER JOIN sl_invoice C ON A.customer_id = C.partner_id
    LEFT JOIN sl_invoice_tax D ON C.invoice_id = D.invoice_id
    WHERE A.session_id = pSessionId
        AND C.tenant_id = pTenantId
        AND C.ou_id = pOuId
        AND C.invoice_temp_id = vEmptyId
        AND C.doc_type_id = vDocTypeIdReturnSalesInvoice
        AND C.status_doc = vStatusReleased
        AND SUBSTR(C.doc_date, 1, 4) BETWEEN vLastYear AND pYear;

    --CN AR
    INSERT INTO tt_achievement_transaction (
        session_id, tenant_id, ou_id,
        doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, amount,
        status_doc, workflow_status,
        tax_amount, total_amount
    )
    SELECT pSessionId, 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, C.curr_code, SUM(D.add_amount)*-1 AS amount,
        C.status_doc AS status_doc, C.workflow_status,
        SUM(D.tax_amount)*-1 AS tax_amount, (SUM(D.add_amount) + SUM(D.tax_amount))*-1 AS total_amount
    FROM tt_salesman_customer_info_for_report_achievement_and_target A
        INNER JOIN fi_invoice_ar C ON A.customer_id = C.partner_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,
        C.invoice_ar_id, C.doc_type_id, C.doc_no, C.doc_date, C.partner_id, C.curr_code,
        C.workflow_status, C.status_doc;

    --DN AR
    INSERT INTO tt_achievement_transaction (
        session_id, tenant_id, ou_id,
        doc_id, doc_type_id, doc_no, doc_date, partner_id, curr_code, amount,
        status_doc, workflow_status,
        tax_amount, total_amount
    )
    SELECT pSessionId, A.tenant_id, A.ou_id,
        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 amount,
        C.status_doc AS status_doc, C.workflow_status,
        C.tax_amount AS tax_amount, (C.total_amount + C.tax_amount) AS total_amount
    FROM tt_salesman_customer_info_for_report_achievement_and_target A
        INNER JOIN fi_invoice_ar C ON A.customer_id = C.partner_id
    WHERE A.session_id = pSessionId
        AND C.status_doc IN (vStatusReleased,vStatusFinal)
        AND C.doc_type_id = 241;*/

    Open pRefHeader FOR
        SELECT f_get_partner_name(pSalesmanId) AS salesman_name, vDateTo AS date_to, SELECT vLastYear AS last_year, pYear AS current_year;
    RETURN NEXT pRefHeader;

    INSERT INTO tt_summary_for_report_achievement_and_target (
        session_id, tenant_id, salesman_id,
        target_amount,
        year_to_date_accumulative_amount,
        year_to_date_accumulative_percentage
    )
    SELECT pSessionId, A.tenant_id, B.salesman_id,
        SUM(B.target) AS target,
        SUM(A.amount) AS year_to_date_accumulative_amount,
        SUM(A.amount) / SUM(B.target) AS year_to_date_accumulative_percentage;
    FROM tt_achievement_transaction A
    INNER JOIN tt_salesman_customer_info_for_report_achievement_and_target B
        ON A.partner_id = B.customer_id AND
            A.tenant_id = B.tenant_id
    WHERE A.session_id = pSessionId AND
        A.tenant_id = pTenantId AND
        SUBSTR(A.doc_date, 1, 4) = pYear
    GROUP BY B.salesman_id;

    PERFORM f_get_total_achievement_from_salesman_hierarchy(pSessionId, pTenantId, A.salesman_id, pYear);
    FROM tt_summary_for_report_achievement_and_target A
    WHERE A.session_id = pSessionId AND
        A.tenant_id = pTenantId;

    Open pRefDetailSummary FOR
        SELECT f_get_partner_name(A.salesman_id) AS salesman_name,
            A.target, A.year_to_date_accumulative_amount, A.year_to_date_accumulative_percentage
        FROM tt_summary_for_report_achievement_and_target A
        WHERE A.session_id = pSessionId AND
            A.tenant_id = pTenantId;
    RETURN NEXT pRefDetailSummary;

    PERFORM SETVAL('r_achievement_vs_target_line_no_seq', 0);

    Open pRefDetail FOR
        SELECT NEXTVAL('r_achievement_vs_target_line_no_seq') AS line_no,
            B.customer_code, B.customer_name, B.city,
            'Septi' AS manager_name, '-' AS supervisor_name, 'Efendi' AS senior_sales_name, 'Hasen' AS junior_sales_name,
            SUM(
                CASE WHEN C.year_date = vLastYear THEN
                    A.amount
                ELSE
                    0
                END
            ) AS last_year_achievement_amount,
            B.target_sales AS current_year_target_amount, B.q1_amount, B.q2_amount, B.q3_amount,
            SUM(
                CASE WHEN C.year_date = pYear THEN
                    A.amount
                ELSE
                    0
                END
            ) AS year_to_date_amount,
            SUM(
                CASE WHEN C.q_no = 1 THEN
                    A.amount
                ELSE
                    0
                END
            ) AS q1_achievement_amount,
            SUM(
                CASE WHEN C.q_no = 2 THEN
                    A.amount
                ELSE
                    0
                END
            ) AS q2_achievement_amount,
            SUM(
                CASE WHEN C.q_no = 3 THEN
                    A.amount
                ELSE
                    0
                END
            ) AS q3_achievement_amount
        FROM tt_achievement_transaction A
        INNER JOIN tt_salesman_customer_info_for_report_achievement_and_target B
            ON A.partner_id = B.customer_id AND
                A.tenant_id = B.tenant_id
        INNER JOIN dt_date C ON A.doc_date = C.string_date
    RETURN NEXT pRefDetail;

    DELETE FROM tt_salesman_from_salesman_hierarchy WHERE session_id = pSessionId;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  /