r_report_sales_invoice_mlm_generated

CREATE OR REPLACE FUNCTION r_report_sales_invoice_mlm_generated(bigint, bigint, character varying, character varying, bigint)
  RETURNS SETOF refcursor AS
$BODY$
    DECLARE
        pRefHeaderSalesInvoiceMlm       REFCURSOR := 'refHeaderSalesInvoiceMlm';
        pRefSalesInvoiceMlm         REFCURSOR := 'refSalesInvoiceMlm';

        pOuId                   ALIAS FOR $1;
        pTenantId               ALIAS FOR $2;
        pSessionId              ALIAS FOR $3;
        pYearMonth              ALIAS FOR $4;
        pPartnerId              ALIAS FOR $5;

        vYes                    character varying := 'Y';
        vEmptyString                character varying := '';
        vEmptyBigintValue           bigint := -99;

        vDoDocTypeId                bigint;
        vDoMlmDocTypeId             bigint;
        vReturDocTypeId             bigint;
        vBuybackDocTypeId           bigint;
        vReturnNoteDocTypeId            bigint;
        vDoRecieptDocTypeId         bigint;
        vFilterPartner              character varying := '';
        vFilterPartnerDoAssembly        character varying := '';
        vDateFrom               character varying := '';
        vDateTo                 character varying := '';
        vReportTitle                character varying := 'REPORT HASIL GENERATE SALES INVOICE';

BEGIN
    vDoDocTypeId := 311;
    vDoMlmDocTypeId := 313;
    vReturDocTypeId := 562;
    vBuybackDocTypeId := 563;
    vReturnNoteDocTypeId := 502;
    vDoRecieptDocTypeId := 526;

    IF pPartnerId <> vEmptyBigintValue THEN
        vFilterPartner := 'AND A.partner_id = ' || pPartnerId;
        vFilterPartnerDoAssembly := ' AND D.partner_id = ' || pPartnerId;
    END IF;

    --membuat dateFrom dan dateEnd
    vDateFrom := pYearMonth || '01';
    SELECT TO_CHAR(TO_DATE(MAX(pYearMonth),'YYYYMM') + interval '1 Month - 1 day','YYYYMMDD') INTO vDateTo;

    --inisialisasi pRefHeaderSalesInvoiceMlm
    OPEN pRefHeaderSalesInvoiceMlm FOR
        SELECT vReportTitle AS report_title, f_get_partner_code(pPartnerId) AS partner_code, f_get_partner_name(pPartnerId) AS partner_name,
            pYearMonth AS year_month_period, vDateFrom, vDateTo;
    RETURN NEXT pRefHeaderSalesInvoiceMlm;

    --memastikan tabel temp kosong
    DELETE FROM tt_report_sales_invoice_generated WHERE session_id = pSessionId;
    DELETE FROM tt_item_penjualan_by_period WHERE session_id = pSessionId;

    --insert data ke tt_item_penjualan_by_period dengan function f_get_data_penjualan_by_period
    PERFORM f_get_data_penjualan_by_period(pSessionId, pTenantId, pOuId, vDateFrom, vDateTo);

    -- DO MLM
    EXECUTE 'INSERT INTO tt_report_sales_invoice_generated (session_id, doc_type_id, doc_no, doc_date, so_no, so_date, komisi, additional_cost,
        product_catalog_id, product_id, qty, harga_katalog, disc_member, disc_promo, harga_netto, total_harga_katalog, total_harga_netto)
    WITH komisi_do_mlm AS (
        SELECT A.doc_id, (SUM(A.qty * F.price_after_disc) * E.commission_percentage * 0.01) AS komisi
        FROM tt_item_penjualan_by_period A
        INNER JOIN sl_do_mlm D
            ON A.doc_id = D.do_mlm_id
        INNER JOIN sl_so_mlm E
            ON D.ref_id = E.so_mlm_id
        INNER JOIN sl_so_mlm_balance_product F
            ON E.so_mlm_id = F.so_mlm_id
            AND A.product_id = F.product_id
        LEFT JOIN sl_so_mlm_cost G
            ON E.so_mlm_id = G.so_mlm_id
        WHERE A.session_id = $1
            AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3
        GROUP BY A.doc_id, E.commission_percentage
    )
    SELECT '''|| pSessionId ||''',  A.doc_type_id, A.doc_no, A.doc_date, E.doc_no, E.doc_date, H.komisi, COALESCE(G.add_amount, 0) AS add_amount,
        A.product_catalog_id, A.product_id, A.qty, F.gross_sell_price, F.discount_member_percentage, F.discount_promo_percentage,
        F.price_after_disc, (A.qty * F.gross_sell_price) AS total_harga_katalog, (A.qty * F.price_after_disc) AS total_harga_netto
    FROM tt_item_penjualan_by_period A
    INNER JOIN sl_do_mlm D
        ON A.doc_id = D.do_mlm_id
    INNER JOIN sl_so_mlm E
        ON D.ref_id = E.so_mlm_id
    INNER JOIN sl_so_mlm_balance_product F
        ON E.so_mlm_id = F.so_mlm_id
        AND A.product_id = F.product_id
    LEFT JOIN sl_so_mlm_cost G
        ON E.so_mlm_id = G.so_mlm_id
    LEFT JOIN komisi_do_mlm H
        ON A.doc_id = H.doc_id
    WHERE A.session_id = $1
    AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3;'
    USING pSessionId, vDoMlmDocTypeId, pYearMonth;

    -- Retur SO MLM
    EXECUTE 'INSERT INTO tt_report_sales_invoice_generated (session_id, doc_type_id, doc_no, doc_date, so_no, so_date, komisi, additional_cost,
        product_catalog_id, product_id, qty, harga_katalog, disc_member, disc_promo, harga_netto, total_harga_katalog, total_harga_netto)
    WITH komisi_retur_mlm AS (
        SELECT A.doc_id, (SUM(A.qty * I.price_after_disc) * H.commission_percentage * 0.01) AS komisi
        FROM tt_item_penjualan_by_period A
        INNER JOIN in_inventory E
            ON A.doc_id = E.inventory_id
            AND A.doc_type_id = E.doc_type_id
        INNER JOIN in_inventory_return_buyback_mlm F
            ON E.inventory_id = F.inventory_id
            AND A.product_id = F.product_id
        INNER JOIN sl_so_mlm H
            ON E.ref_id = H.so_mlm_id
            AND E.ref_doc_type_id = H.doc_type_id
        INNER JOIN sl_so_mlm_balance_product I
            ON I.so_mlm_id = H.so_mlm_id
            AND I.product_id = A.product_id
        WHERE A.session_id = $1
            AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3
        GROUP BY A.doc_id, H.commission_percentage
    )
    SELECT '''|| pSessionId ||''', A.doc_type_id, A.doc_no, A.doc_date, H.doc_no, H.doc_date, J.komisi, 0, A.product_catalog_id, A.product_id,
        A.qty, I.gross_sell_price , I.discount_member_percentage, I.discount_promo_percentage,
        I.price_after_disc, (I.gross_sell_price * A.qty) AS total_harga_katalog, (I.price_after_disc * A.qty) AS total_harga_netto
    FROM tt_item_penjualan_by_period A
    INNER JOIN in_inventory E
        ON A.doc_id = E.inventory_id
        AND A.doc_type_id = E.doc_type_id
    INNER JOIN in_inventory_return_buyback_mlm F
        ON E.inventory_id = F.inventory_id
        AND A.product_id = F.product_id
    INNER JOIN sl_so_mlm H
        ON E.ref_id = H.so_mlm_id
        AND E.ref_doc_type_id = H.doc_type_id
    INNER JOIN sl_so_mlm_balance_product I
        ON I.so_mlm_id = H.so_mlm_id
        AND I.product_id = A.product_id
    LEFT JOIN komisi_retur_mlm J
        ON A.doc_id = J.doc_id
    WHERE A.session_id = $1
    AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3;'
    USING pSessionId, vReturDocTypeId, pYearMonth;


    -- Buyback
    EXECUTE 'INSERT INTO tt_report_sales_invoice_generated (session_id, doc_type_id, doc_no, doc_date, so_no, so_date, komisi, additional_cost,
        product_catalog_id, product_id, qty, harga_katalog, disc_member, disc_promo, harga_netto, total_harga_katalog, total_harga_netto)
    WITH komisi_buyback AS (
        SELECT A.doc_id, (SUM(A.qty * I.price_after_disc) * H.commission_percentage * 0.01) AS komisi
        FROM tt_item_penjualan_by_period A
        INNER JOIN in_inventory E
            ON A.doc_id = E.inventory_id AND A.doc_type_id = E.doc_type_id
        INNER JOIN in_inventory_return_buyback_mlm F
            ON E.inventory_id = F.inventory_id
            AND A.product_id = F.product_id
        INNER JOIN sl_so_mlm H
            ON E.ref_id = H.so_mlm_id
            AND E.ref_doc_type_id = H.doc_type_id
        INNER JOIN sl_so_mlm_balance_product I
            ON I.so_mlm_id = H.so_mlm_id
            AND I.product_id = A.product_id
        WHERE A.session_id = $1
            AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3
        GROUP BY A.doc_id, H.commission_percentage
    )
    SELECT '''|| pSessionId ||''', A.doc_type_id, A.doc_no, A.doc_date, H.doc_no, H.doc_date, J.komisi, 0, A.product_catalog_id, A.product_id,
        A.qty, I.gross_sell_price , I.discount_member_percentage, I.discount_promo_percentage,
        I.price_after_disc, (I.gross_sell_price * A.qty) AS total_harga_katalog, (I.price_after_disc * A.qty) AS total_harga_netto
    FROM tt_item_penjualan_by_period A
    INNER JOIN in_inventory E
        ON A.doc_id = E.inventory_id AND A.doc_type_id = E.doc_type_id
    INNER JOIN in_inventory_return_buyback_mlm F
        ON E.inventory_id = F.inventory_id
        AND A.product_id = F.product_id
    INNER JOIN sl_so_mlm H
        ON E.ref_id = H.so_mlm_id
        AND E.ref_doc_type_id = H.doc_type_id
    INNER JOIN sl_so_mlm_balance_product I
        ON I.so_mlm_id = H.so_mlm_id
        AND I.product_id = A.product_id
    LEFT JOIN komisi_buyback J
        ON A.doc_id = J.doc_id
    WHERE A.session_id = $1
        AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3;'
    USING pSessionId, vBuybackDocTypeId, pYearMonth;

    --return note
    EXECUTE 'INSERT INTO tt_report_sales_invoice_generated (session_id, doc_type_id, doc_no, doc_date, so_no, so_date, komisi, additional_cost,
        product_catalog_id, product_id, qty, harga_katalog, disc_member, disc_promo, harga_netto, total_harga_katalog, total_harga_netto)
    SELECT '''|| pSessionId ||''', A.doc_type_id, A.doc_no, A.doc_date, H.doc_no, H.doc_date, 0, 0, A.product_catalog_id, A.product_id,
        A.qty, (I.gross_sell_price - discount_amount) AS price_after_disc, 0, 0, (I.gross_sell_price - discount_amount) AS price_after_disc,
        (A.qty * (I.gross_sell_price - discount_amount)) AS total_price_after_disc, (A.qty * (I.gross_sell_price - discount_amount)) AS total_price_after_disc
    FROM tt_item_penjualan_by_period A
    INNER JOIN in_inventory D
        ON A.doc_id = D.inventory_id
        AND A.doc_type_id = D.doc_type_id
    INNER JOIN sl_do G
        ON D.ref_id = G.do_id
        AND A.doc_id = G.do_id
    INNER JOIN sl_so H
        ON G.ref_id = H.so_id
    INNER JOIN sl_so_item I
        ON H.so_id = I.so_id
        AND A.product_id = I.product_id
    WHERE A.session_id = $1
    AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3;'
    USING pSessionId, vReturnNoteDocTypeId, pYearMonth;

    --DO
    EXECUTE 'INSERT INTO tt_report_sales_invoice_generated (session_id, doc_type_id, doc_no, doc_date, so_no, so_date, komisi, additional_cost,
        product_catalog_id, product_id, qty, harga_katalog, disc_member, disc_promo, harga_netto, total_harga_katalog, total_harga_netto)
    SELECT '''|| pSessionId ||''', A.doc_type_id, A.doc_no, A.doc_date, E.doc_no, E.doc_date, 0, COALESCE(G.add_amount, 0), A.product_catalog_id, A.product_id,
        A.qty, (F.gross_sell_price - discount_amount) AS price_after_disc, 0, 0, (F.gross_sell_price - discount_amount) AS price_after_disc,
        (A.qty * (F.gross_sell_price - discount_amount)) AS total_price_after_disc, (A.qty * (F.gross_sell_price - discount_amount)) AS total_price_after_disc
    FROM tt_item_penjualan_by_period A
    INNER JOIN sl_do D
        ON A.doc_id = D.do_id
    INNER JOIN sl_so E
        ON D.ref_id = E.so_id
    INNER JOIN sl_so_item F
        ON E.so_id = F.so_id
        AND A.product_id = F.product_id
    LEFT JOIN sl_so_cost G
        ON E.so_id = G.so_id
    WHERE A.session_id = $1
    AND A.doc_type_id = $2 ' || vFilterPartner || ' AND SUBSTR(A.doc_date, 1, 6) = $3;'
    USING pSessionId, vDoDocTypeId, pYearMonth;

    -- Added By Julius, 22 Sept 2017
    -- DO PRODUCT ASSEMBLY
    EXECUTE 'INSERT INTO tt_report_sales_invoice_generated (session_id, doc_type_id, doc_no, doc_date, so_no, so_date, komisi, additional_cost,
        product_catalog_id, product_id, qty, harga_katalog, disc_member, disc_promo, harga_netto, total_harga_katalog, total_harga_netto)
    SELECT '''|| pSessionId ||''', A.doc_type_id, A.doc_no, A.doc_date, E.doc_no, E.doc_date, 0, COALESCE(G.add_amount, 0), D.product_catalog_id, H.parent_product_id,
        B.qty_dlv_int, (F.price_so - 0) AS price_after_disc, 0, 0, (F.price_so - 0) AS price_after_disc,
        (B.qty_dlv_int * (F.price_so - 0)) AS total_price_after_disc, (B.qty_dlv_int * (F.price_so - 0)) AS total_price_after_disc
    FROM sl_do A
    INNER JOIN sl_do_item B
        ON A.do_id = B.do_id
    INNER JOIN sl_do_child_item C
        ON B.do_item_id = C.do_item_id
    INNER JOIN tt_item_penjualan_by_period D
        ON A.do_id = D.doc_id
        AND C.product_id = D.product_id
    INNER JOIN sl_so E
        ON A.ref_id = E.so_id
    INNER JOIN sl_so_balance_invoice F
        ON E.so_id = F.so_id
        AND B.do_item_id = F.ref_item_id
    LEFT JOIN sl_so_cost G
        ON E.so_id = G.so_id
    INNER JOIN m_product_assembly H
        ON C.product_id = H.child_product_id
        AND B.product_id = H.parent_product_id
    WHERE D.session_id = $1
    AND D.doc_type_id = $2 ' || vFilterPartnerDoAssembly || ' AND SUBSTR(A.doc_date, 1, 6) = $3;'
    USING pSessionId, vDoDocTypeId, pYearMonth;

    --DO RECIEPT
    EXECUTE 'INSERT INTO tt_report_sales_invoice_generated (session_id, doc_type_id, doc_no, doc_date, so_no, so_date, komisi, additional_cost,
        product_catalog_id, product_id, qty, harga_katalog, disc_member, disc_promo, harga_netto, total_harga_katalog, total_harga_netto)
    SELECT '''|| pSessionId ||''', A.doc_type_id, A.doc_no, A.doc_date, G.doc_no, G.doc_date, 0, 0, A.product_catalog_id, A.product_id,
        A.qty, (H.gross_sell_price - discount_amount) AS price_after_disc, 0, 0, (H.gross_sell_price - discount_amount) AS price_after_disc,
        (A.qty * (H.gross_sell_price - discount_amount)) AS total_price_after_disc, (A.qty * (H.gross_sell_price - discount_amount)) AS total_price_after_disc
    FROM tt_item_penjualan_by_period A
    INNER JOIN in_do_receipt D
        ON A.doc_id = D.do_receipt_id
    INNER JOIN in_do_receipt_item E
        ON D.do_receipt_id = E.do_receipt_id
        AND A.product_id = E.product_id
    INNER JOIN sl_do F
        ON D.ref_id = F.do_id AND D.ref_doc_type_id = 311
    INNER JOIN sl_so G
        ON F.ref_id = G.so_id AND F.ref_doc_type_id = 301
    INNER JOIN sl_so_item H
        ON G.so_id = H.so_id
    WHERE A.session_id = $1
    AND A.doc_type_id = $2 ' || vFilterPartner ||' AND SUBSTR(A.doc_date, 1, 6) = $3;'
    USING pSessionId, vDoRecieptDocTypeId, pYearMonth;


    /* NOTE: untuk Zona HK, ditemukan kasus:
     * Jika nilai catalog_price0 dan catalog_price1 sama, maka zona yang akan muncul adalah zona yang terlebih dahulu masuk ke kondisi CASE WHEN
     * Untuk saat ini, sementara ditentukan jika nilainya sama, akan dibuat menjadi HK (catalog_price0)
     */
    OPEN pRefSalesInvoiceMlm FOR
        SELECT f_get_doc_desc(A.doc_type_id) AS doc_desc, A.doc_no, A.doc_date, A.so_no, A.so_date, A.komisi, A.additional_cost, COALESCE(C.product_catalog_code,'-') AS product_catalog_code,
            COALESCE(Z.catalog_name,'-') AS catalog_name, B.product_code, B.product_name, A.qty, A.harga_katalog, A.disc_member, A.disc_promo, A.harga_netto,
            A.total_harga_katalog, A.total_harga_netto,
            CASE
                WHEN A.harga_katalog = COALESCE(C.catalog_price0,0) THEN 'HK'
                WHEN A.harga_katalog = COALESCE(C.catalog_price,0) THEN 'HK1'
                WHEN A.harga_katalog = COALESCE(C.catalog_price2,0) THEN 'HK2'
            ELSE 'OTHER-HK' END AS zona_hk
        FROM tt_report_sales_invoice_generated A
        INNER JOIN m_product B
            ON A.product_id = B.product_id
        LEFT JOIN m_product_catalog C
            ON B.product_id = C.product_id
            AND A.product_catalog_id = C.product_catalog_id
        LEFT JOIN m_catalog Z
            ON C.catalog_id = Z.catalog_id
        WHERE session_id = pSessionId
        GROUP BY f_get_doc_desc(A.doc_type_id), A.doc_no, A.doc_date, A.so_no, A.so_date, A.komisi, A.additional_cost, COALESCE(C.product_catalog_code,'-'),
            COALESCE(Z.catalog_name,'-'), B.product_code, B.product_name, A.qty, A.harga_katalog, A.disc_member, A.disc_promo, A.harga_netto,
            A.total_harga_katalog, A.total_harga_netto,A.harga_katalog,COALESCE(C.catalog_price0,0),COALESCE(C.catalog_price,0),COALESCE(C.catalog_price2,0), C.product_catalog_code
        ORDER BY f_get_doc_desc(A.doc_type_id), A.doc_date, A.doc_no, COALESCE(Z.catalog_name,'-'), C.product_catalog_code ASC;
    RETURN NEXT pRefSalesInvoiceMlm;

    DELETE FROM tt_report_sales_invoice_generated WHERE session_id = pSessionId;
    DELETE FROM tt_item_penjualan_by_period WHERE session_id = pSessionId;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
/