r_log_stock_without_documents

-- Author by Widana Nur Azis, 18 Jan 2018
/*
 * Menghasilkan data dari log product balance stock dan log product balance stock consignment
 * yang status dokumen nya tidak approved dan tidak ada dokumen dari suatu table master dokumen.
 */
CREATE OR REPLACE FUNCTION r_log_stock_without_documents(character varying,bigint, bigint, bigint, character varying, bigint, character varying)
  RETURNS SETOF refcursor AS
$BODY$
DECLARE
    pRefHeader                  REFCURSOR := 'refHeader';
    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;
    pPeriodDate                 ALIAS FOR $7;

    vApproved               character varying := 'APPROVED';
    vNotYetStatus           character varying := 'NOT YET STATUS';

BEGIN

    DELETE FROM tt_log_stock_without_documents WHERE session_id = pSessionId;

    /*
     * Melakukan simpan data yang status dokumen nya belum approved,
     * document terdaftar di master
     * in_log_product_balance_stock
     */
    INSERT INTO tt_log_stock_without_documents (session_id, ou_id, tenant_id, doc_type, doc_no, doc_date, product_code, product_name,
        serial_number, warehouse_code, warehouse_name, base_uom_code, base_uom_name, qty, status)
    SELECT pSessionId, C.ou_id, C.tenant_id, f_get_doc_desc(C.doc_type_id), C.doc_no, C.doc_date, f_get_product_code(C.product_id), f_get_product_name(C.product_id),
        f_get_serial_number(C.product_balance_id), f_get_warehouse_code(C.warehouse_id), f_get_warehouse_name(C.warehouse_id),
        f_get_uom_code(C.base_uom_id), f_get_uom_name(C.base_uom_id), C.qty, A.current_state
    FROM awe_currdoc_status A
    INNER JOIN m_document B ON A.scheme = B.scheme
    INNER JOIN in_log_product_balance_stock C ON A.doc_id = C.ref_id AND B.doc_type_id = C.doc_type_id
    WHERE A.current_state NOT IN(vApproved)
        AND C.ou_id IN (SELECT Z.ou_id FROM t_ou Z WHERE Z.ou_parent_id  = pOuId OR Z.ou_id = pOuId)
        AND C.tenant_id = pTenantId
        AND substring(C.doc_date, 0, 7) = pPeriodDate;

    /*
     * Melakukan simpan data yang status dokumen nya belum approved,
     * document tidak terdaftar di master
     * in_log_product_balance_stock
     */
    INSERT INTO tt_log_stock_without_documents (session_id, ou_id, tenant_id, doc_type, doc_no, doc_date, product_code, product_name,
        serial_number, warehouse_code, warehouse_name, base_uom_code, base_uom_name, qty, status)
    SELECT pSessionId, C.ou_id, C.tenant_id, CASE WHEN C.doc_type_id = -99 THEN 'SALDO AWAL' ELSE f_get_doc_desc(C.doc_type_id) END , C.doc_no, C.doc_date, f_get_product_code(C.product_id), f_get_product_name(C.product_id),
        f_get_serial_number(C.product_balance_id), f_get_warehouse_code(C.warehouse_id), f_get_warehouse_name(C.warehouse_id),
        f_get_uom_code(C.base_uom_id), f_get_uom_name(C.base_uom_id), C.qty, vNotYetStatus
    FROM in_log_product_balance_stock C
    WHERE NOT EXISTS (SELECT 1 FROM m_document Z WHERE C.doc_type_id = Z.doc_type_id)
        AND C.ou_id IN (SELECT Z.ou_id FROM t_ou Z WHERE Z.ou_parent_id  = pOuId OR Z.ou_id = pOuId)
        AND C.tenant_id = pTenantId
        AND substring(C.doc_date, 0, 7) = pPeriodDate;

    /*
     * Melakukan simpan data yang status dokumen nya belum approved,
     * document terdaftar di master
     * in_log_product_consignment_balance_stock
     */
    INSERT INTO tt_log_stock_without_documents (session_id, ou_id, tenant_id, doc_type, doc_no, doc_date, product_code, product_name,
        serial_number, warehouse_code, warehouse_name, base_uom_code, base_uom_name, qty, status)
    SELECT pSessionId, C.ou_id, C.tenant_id, f_get_doc_desc(C.doc_type_id), C.doc_no, C.doc_date, f_get_product_code(C.product_id), f_get_product_name(C.product_id),
        f_get_serial_number(C.product_balance_id), f_get_warehouse_code(C.warehouse_id), f_get_warehouse_name(C.warehouse_id),
        f_get_uom_code(C.base_uom_id), f_get_uom_name(C.base_uom_id), C.qty, A.current_state
    FROM awe_currdoc_status A
    INNER JOIN m_document B ON A.scheme = B.scheme
    INNER JOIN in_log_product_consignment_balance_stock C ON A.doc_id = C.ref_id AND B.doc_type_id = C.doc_type_id
    WHERE A.current_state NOT IN(vApproved)
        AND C.ou_id IN (SELECT Z.ou_id FROM t_ou Z WHERE Z.ou_parent_id  = pOuId OR Z.ou_id = pOuId)
        AND C.tenant_id = pTenantId
        AND substring(C.doc_date, 0, 7) = pPeriodDate;

    /*
     * Melakukan simpan data yang status dokumen nya belum approved,
     * document tidak terdaftar di master
     * in_log_product_consignment_balance_stock
     */
    INSERT INTO tt_log_stock_without_documents (session_id, ou_id, tenant_id, doc_type, doc_no, doc_date, product_code, product_name,
        serial_number, warehouse_code, warehouse_name, base_uom_code, base_uom_name, qty, status)
    SELECT pSessionId, C.ou_id, C.tenant_id, CASE WHEN C.doc_type_id = -99 THEN 'SALDO AWAL' ELSE f_get_doc_desc(C.doc_type_id) END , C.doc_no, C.doc_date, f_get_product_code(C.product_id), f_get_product_name(C.product_id),
        f_get_serial_number(C.product_balance_id), f_get_warehouse_code(C.warehouse_id), f_get_warehouse_name(C.warehouse_id),
        f_get_uom_code(C.base_uom_id), f_get_uom_name(C.base_uom_id), C.qty, vNotYetStatus
    FROM in_log_product_consignment_balance_stock C
    WHERE NOT EXISTS (SELECT 1 FROM m_document Z WHERE C.doc_type_id = Z.doc_type_id)
        AND C.ou_id IN (SELECT Z.ou_id FROM t_ou Z WHERE Z.ou_parent_id  = pOuId OR Z.ou_id = pOuId)
        AND C.tenant_id = pTenantId
        AND substring(C.doc_date, 0, 7) = pPeriodDate;

    OPEN pRefHeader FOR
        SELECT f_get_ou_name(pOuId) AS ou_name, pPeriodDate AS period_date, pDatetime AS datetime,
                f_get_role_name(pRoleId) AS role_name, pPeriodDate AS year_month;
    RETURN NEXT pRefHeader;

    OPEN pRefDetail FOR
        SELECT session_id, ou_id, tenant_id, doc_type, doc_no, doc_date, product_code, product_name,
                serial_number, warehouse_code, warehouse_name, base_uom_code, base_uom_name, qty, status
        FROM tt_log_stock_without_documents;
    RETURN NEXT  pRefDetail;

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