r_jurnal_umum

CREATE OR REPLACE FUNCTION r_jurnal_umum(character varying, bigint, character varying)
    RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pSessionId          ALIAS FOR $1;
    pTenantId           ALIAS FOR $2;
    pDateYearMonth      ALIAS FOR $3;

    pRefHeader          REFCURSOR := 'refHeader';
    pRefDetail          REFCURSOR := 'refDetail';

    vTitle              character varying := 'REPORT JURNAL UMUM';

    vCOGSCoaId          bigint;
    vInventoryCoaId     bigint;
    vPPNCoaId           bigint;

    vCOGSCoaAcc         character varying;
    vInventoryCoaAcc    character varying;
    vPPNCoaAcc          character varying;

    vCOGSCoaDesc        character varying;
    vInventoryCoaDesc   character varying;
    vPPNCoaDesc         character varying;

    vYes                character varying := 'Y';
    vNo                 character varying := 'N';
    vOuId               bigint;

BEGIN

    SELECT f_get_value_system_config_by_param_code(pTenantId, 'OU.GRAZIA.INDONESIA') INTO vOuId;

    -- Get Coa main_acc.sub_acc and desc (DEBET)
    SELECT f_get_system_coa_by_group_coa(pTenantId, 'HargaPokokPenjualan') INTO vCOGSCoaId;
    SELECT main_acc || '.' || sub_acc, coa_desc INTO vCOGSCoaAcc, vCOGSCoaDesc FROM m_coa WHERE coa_id = vCOGSCoaId;

    -- Get Coa main_acc.sub_acc and desc (KREDIT)
    SELECT coa_id FROM m_group_product WHERE group_product_code = 'FG' INTO vInventoryCoaId;
    SELECT main_acc || '.' || sub_acc, coa_desc INTO vInventoryCoaAcc, vInventoryCoaDesc FROM m_coa WHERE coa_id = vInventoryCoaId;

    SELECT f_get_system_coa_by_group_coa(pTenantId, 'PajakPertambahanNilaiKeluaran') INTO vPPNCoaId;
    SELECT main_acc || '.' || sub_acc, coa_desc INTO vPPNCoaAcc, vPPNCoaDesc FROM m_coa WHERE coa_id = vPPNCoaId;


    INSERT INTO tt_jurnal_umum_dn_ar(
            session_id, invoice_ar_id, recap_sales_department_store_id)
    WITH temp AS (
        SELECT recap_sales_department_store_id, date_year_month, outlet_id
        FROM i_recap_sales_department_store
        WHERE date_year_month = pDateYearMonth
    )
    SELECT pSessionId AS session_id, B.invoice_ar_id, A.recap_sales_department_store_id
    FROM fi_invoice_ar_recap_sales_department_store B
    INNER JOIN temp A
    ON A.recap_sales_department_store_id = B.recap_sales_department_store_id;



    -- Insert COGS
    INSERT INTO tt_jurnal_umum(
            session_id, outlet_id, outlet_name, partner_id, partner_name, brand_id,
            brand_code, coa_acc, coa_desc, debet, kredit)
    SELECT pSessionId AS session_id, B.outlet_id, B.outlet_name, C.partner_id, C.partner_name, D.brand_id,
           COALESCE(E.brand_code,''), vCOGSCoaAcc AS coa_acc, vCOGSCoaDesc AS coa_desc, SUM(A.qty_verified * COALESCE(F.purch_price, 0)) AS debet, 0 AS kredit
    FROM i_trx_mobile_pos_item_balance A
    INNER JOIN i_outlet B
    ON A.outlet_id = B.outlet_id
    INNER JOIN m_partner C
    ON B.partner_id = C.partner_id
    INNER JOIN i_outlet_brand D
    ON A.outlet_id = D.outlet_id
    LEFT JOIN m_brand E
    ON D.brand_id = E.brand_id
    LEFT JOIN m_purch_price_product F
    ON A.tenant_id = F.tenant_id
    AND F.ou_id = vOuId
    AND pDateYearMonth || '01' between F.date_from AND F.date_to
    AND A.product_id = F.product_id
    WHERE A.tenant_id = pTenantId
    AND SUBSTR(A.doc_date , 1, 6) = pDateYearMonth
    AND A.status_item = vYes
    AND A.flg_unused = vNo
    GROUP BY B.outlet_id, B.outlet_name, C.partner_id, C.partner_name, D.brand_id, COALESCE(E.brand_code,'')
    ORDER BY B.outlet_name, C.partner_code, COALESCE(E.brand_code,'');


--      -- Insert Piutang
--  INSERT INTO tt_jurnal_umum(
--            session_id, outlet_id, outlet_name, partner_id, partner_name, brand_id,
--            brand_code, coa_acc, coa_desc, debet, kredit)
--  WITH temp AS(
--      SELECT C.outlet_id, A.partner_id, C.amount
--      FROM fi_invoice_ar A
--      INNER JOIN fi_invoice_ar_cost B
--      ON A.invoice_ar_id = B.invoice_ar_id
--      INNER JOIN fi_invoice_ar_cost_ext C
--      ON B.invoice_ar_cost_id = C.invoice_ar_cost_id
--      WHERE A.tenant_id = pTenantId
--      AND SUBSTR(A.doc_date , 1, 6) = pDateYearMonth
--      UNION ALL
--      SELECT C.outlet_id, A.partner_id, C.amount
--      FROM fi_invoice_ar A
--      INNER JOIN fi_invoice_ar_tax B
--      ON A.invoice_ar_id = B.invoice_ar_id
--      INNER JOIN fi_invoice_ar_tax_ext C
--      ON B.invoice_ar_tax_id = C.invoice_ar_tax_id
--      WHERE A.tenant_id = pTenantId
--      AND SUBSTR(A.doc_date , 1, 6) = pDateYearMonth
--  )
--  SELECT pSessionId AS session_id, A.outlet_id, C.outlet_name, A.partner_id, B.partner_name, E.brand_id,
--       E.brand_code, H.main_acc || '.' || H.sub_acc AS  coa_acc, H.coa_desc AS coa_desc, SUM(A.amount) AS debet, 0 AS kredit
--  FROM temp A
--  INNER JOIN m_partner B
--  ON A.partner_id = B.partner_id
--  INNER JOIN i_outlet C
--  ON A.outlet_id = C.outlet_id
--  INNER JOIN i_outlet_brand D
--  ON C.outlet_id = D.outlet_id
--  INNER JOIN m_brand E
--  ON D.brand_id = E.brand_id
--  INNER JOIN m_partner_type F
--  ON B.partner_id = F.partner_id
--  INNER JOIN m_type_partner G
--  ON F.type_partner_id = G.type_partner_id
--  INNER JOIN m_coa H
--  ON G.coa_id = H.coa_id
--  GROUP BY A.outlet_id, C.outlet_name, A.partner_id, B.partner_name, E.brand_id,
--       E.brand_code, coa_acc, coa_desc;


    -- Insert Piutang
    INSERT INTO tt_jurnal_umum(
            session_id, outlet_id, outlet_name, partner_id, partner_name, brand_id,
            brand_code, coa_acc, coa_desc, debet, kredit)
    WITH temp AS(
        SELECT D.outlet_id, A.partner_id, D.amount
        FROM fi_invoice_ar A
        INNER JOIN tt_jurnal_umum_dn_ar B
        ON A.invoice_ar_id = B.invoice_ar_id
        INNER JOIN fi_invoice_ar_cost C
        ON B.invoice_ar_id = C.invoice_ar_id
        INNER JOIN fi_invoice_ar_cost_ext D
        ON C.invoice_ar_cost_id = D.invoice_ar_cost_id
        WHERE A.tenant_id = pTenantId
        UNION ALL
        SELECT D.outlet_id, A.partner_id, D.amount
        FROM fi_invoice_ar A
        INNER JOIN tt_jurnal_umum_dn_ar B
        ON A.invoice_ar_id = B.invoice_ar_id
        INNER JOIN fi_invoice_ar_tax C
        ON B.invoice_ar_id = C.invoice_ar_id
        INNER JOIN fi_invoice_ar_tax_ext D
        ON C.invoice_ar_tax_id = D.invoice_ar_tax_id
        WHERE A.tenant_id = pTenantId
    )
    SELECT pSessionId AS session_id, A.outlet_id, C.outlet_name, A.partner_id, B.partner_name, D.brand_id,
         COALESCE(E.brand_code,''), H.main_acc || '.' || H.sub_acc AS  coa_acc, H.coa_desc AS coa_desc, SUM(A.amount) AS debet, 0 AS kredit
    FROM temp A
    INNER JOIN m_partner B
    ON A.partner_id = B.partner_id
    INNER JOIN i_outlet C
    ON A.outlet_id = C.outlet_id
    INNER JOIN i_outlet_brand D
    ON C.outlet_id = D.outlet_id
    LEFT JOIN m_brand E
    ON D.brand_id = E.brand_id
    INNER JOIN m_partner_type F
    ON B.partner_id = F.partner_id
    INNER JOIN m_type_partner G
    ON F.type_partner_id = G.type_partner_id
    INNER JOIN m_coa H
    ON G.coa_id = H.coa_id
    GROUP BY A.outlet_id, C.outlet_name, A.partner_id, B.partner_name, D.brand_id,
         COALESCE(E.brand_code,''), coa_acc, coa_desc;


    -- Insert Discount dan Penjualan
    INSERT INTO tt_jurnal_umum(
            session_id, outlet_id, outlet_name, partner_id, partner_name, brand_id,
            brand_code, coa_acc, coa_desc, debet, kredit)
    SELECT pSessionId AS session_id, F.outlet_id, F.outlet_name, E.partner_id, E.partner_name, G.brand_id,
           COALESCE(H.brand_code,''), J.main_acc || '.' || J.sub_acc AS coa_acc, J.coa_desc AS coa_desc,
           (CASE WHEN J.sign_coa = 'D' AND D.amount > 0 THEN D.amount
                 WHEN J.sign_coa = 'C' AND D.amount < 0 THEN (D.amount * -1) ELSE 0 END) AS debet,
           (CASE WHEN J.sign_coa = 'C' AND D.amount > 0 THEN D.amount
                 WHEN J.sign_coa = 'D' AND D.amount < 0 THEN (D.amount * -1) ELSE 0 END) AS kredit
    FROM fi_invoice_ar A
    INNER JOIN tt_jurnal_umum_dn_ar B
    ON A.invoice_ar_id = B.invoice_ar_id
    INNER JOIN fi_invoice_ar_cost C
    ON B.invoice_ar_id = C.invoice_ar_id
    INNER JOIN fi_invoice_ar_cost_ext D
    ON C.invoice_ar_cost_id = D.invoice_ar_cost_id
    INNER JOIN m_partner E
    ON A.partner_id = E.partner_id
    INNER JOIN i_outlet F
    ON D.outlet_id = F.outlet_id
    INNER JOIN i_outlet_brand G
    ON F.outlet_id = G.outlet_id
    LEFT JOIN m_brand H
    ON G.brand_id = H.brand_id
    INNER JOIN m_activity_gl I
    ON C.activity_gl_id = I.activity_gl_id
    INNER JOIN m_coa J
    ON I.coa_id = J.coa_id
    WHERE A.tenant_id = pTenantId;

    -- Insert Inventory
    INSERT INTO tt_jurnal_umum(
            session_id, outlet_id, outlet_name, partner_id, partner_name, brand_id,
            brand_code, coa_acc, coa_desc, debet, kredit)
    SELECT pSessionId AS session_id, B.outlet_id, B.outlet_name, C.partner_id, C.partner_name, D.brand_id,
           COALESCE(E.brand_code,''), vInventoryCoaAcc AS coa_acc, vInventoryCoaDesc AS coa_desc, 0 AS debet, SUM(A.qty_verified * COALESCE(F.purch_price, 0)) AS kredit
    FROM i_trx_mobile_pos_item_balance A
    INNER JOIN i_outlet B
    ON A.outlet_id = B.outlet_id
    INNER JOIN m_partner C
    ON B.partner_id = C.partner_id
    INNER JOIN i_outlet_brand D
    ON A.outlet_id = D.outlet_id
    LEFT JOIN m_brand E
    ON D.brand_id = E.brand_id
    LEFT JOIN m_purch_price_product F
    ON A.tenant_id = F.tenant_id
    AND F.ou_id = vOuId
    AND pDateYearMonth || '01' BETWEEN F.date_from AND F.date_to
    AND A.product_id = F.product_id
    WHERE A.tenant_id = pTenantId
    AND SUBSTR(A.doc_date , 1, 6) = pDateYearMonth
    AND A.status_item = vYes
    AND A.flg_unused = vNo
    GROUP BY B.outlet_id, B.outlet_name, C.partner_id, C.partner_name, D.brand_id, COALESCE(E.brand_code,'')
    ORDER BY B.outlet_name, C.partner_code, COALESCE(E.brand_code,'');


--  -- Insert PPN
--  INSERT INTO tt_jurnal_umum(
--            session_id, outlet_id, outlet_name, partner_id, partner_name, brand_id,
--            brand_code, coa_acc, coa_desc, debet, kredit)
--  SELECT pSessionId AS session_id, E.outlet_id, E.outlet_name, D.partner_id, D.partner_name, G.brand_id,
--         G.brand_code, vPPNCoaAcc AS coa_acc, vPPNCoaDesc AS coa_desc, 0 AS debet, SUM (A.tax_amount) AS kredit
--  FROM fi_invoice_ar A
--  INNER JOIN fi_invoice_ar_tax B
--  ON A.invoice_ar_id = B.invoice_ar_id
--  INNER JOIN fi_invoice_ar_tax_ext C
--  ON B.invoice_ar_tax_id = C.invoice_ar_tax_id
--  INNER JOIN m_partner D
--  ON A.partner_id = D.partner_id
--  INNER JOIN i_outlet E
--  ON C.outlet_id = E.outlet_id
--  INNER JOIN i_outlet_brand F
--  ON E.outlet_id = F.outlet_id
--  INNER JOIN m_brand G
--  ON F.brand_id = G.brand_id
--  WHERE A.tenant_id = pTenantId
--  AND SUBSTR(A.doc_date , 1, 6) = pDateYearMonth
--  GROUP BY E.outlet_id, E.outlet_name, D.partner_id, D.partner_name, G.brand_id, G.brand_code
--  ORDER BY E.outlet_name, D.partner_code, G.brand_code;


    OPEN pRefHeader FOR
        SELECT vTitle AS report_title,
        pDateYearMonth AS date_year_month;
    RETURN NEXT pRefHeader;

    OPEN pRefDetail FOR
        SELECT outlet_name, partner_name, brand_code, coa_acc, coa_desc, debet, kredit
        FROM tt_jurnal_umum
        ORDER BY partner_name, brand_code, outlet_name, coa_desc;
    RETURN NEXT pRefDetail;

    DELETE FROM tt_jurnal_umum WHERE session_id = pSessionId;
    DELETE FROM tt_jurnal_umum_dn_ar WHERE session_id = pSessionId;

END;
$BODY$
    LANGUAGE plpgsql VOLATILE
    COST 100
    ROWS 1000;
/