sl_modify_doc_date_approved_do_KPS_20180219

-- Function: sl_modify_doc_date_approved_do(bigint, character varying, bigint, character varying, character varying, character varying)

-- DROP FUNCTION sl_modify_doc_date_approved_do(bigint, character varying, bigint, character varying, character varying, character varying);

CREATE OR REPLACE FUNCTION sl_modify_doc_date_approved_do(bigint, character varying, bigint, character varying, character varying, character varying)
  RETURNS void AS
$BODY$
DECLARE
    pTenantId           ALIAS FOR $1;
    pSessionId          ALIAS FOR $2;
    pOuId               ALIAS FOR $3;
    pDoDocNo            ALIAS FOR $4;
    pDoDocDate          ALIAS FOR $5;
    pNewDocDate         ALIAS FOR $6;

    vDoId               bigint;
    vDoDocTypeId        bigint;
    vSchemeDo           character varying(5);
BEGIN

    vSchemeDo := 'FB01';
    vDoDocTypeId := 311;

    -- TODO: validasi bahwa DO ini belum pernah difollow up sama sekali (Invoice Temp, Invoice, DO Receipt)

    -- ambil do id dan doc type id nya
    SELECT do_id INTO vDoId
    FROM sl_do A
    WHERE A.tenant_id = pTenantId
    AND   A.doc_type_id = vDoDocTypeId
    AND   A.ou_id = pOuId
    AND   A.doc_no = pDoDocNo
    AND   A.doc_date = pDoDocDate;

    -- update sl_so_balance_invoice (ref_doc_date)
    UPDATE sl_so_balance_invoice SET ref_doc_date = pNewDocDate WHERE ref_id = vDoId AND ref_doc_type_id = vDoDocTypeId AND ref_doc_no = pDoDocNo AND ref_doc_date = pDoDocDate AND ou_id = pOuId;

    -- update in_log_product_balance_stock (doc_date)
    UPDATE in_log_product_balance_stock SET doc_date = pNewDocDate WHERE ref_id = vDoId AND doc_type_id = vDoDocTypeId AND doc_no = pDoDocNo AND ou_id = pOuId AND tenant_id = pTenantId AND doc_date = pDoDocDate;

    -- update in_balance_do_item (doc_date)
    UPDATE in_balance_do_item SET doc_date = pNewDocDate WHERE do_id = vDoId AND doc_no = pDoDocNo AND ou_id = pOuId AND tenant_id = pTenantId AND doc_date = pDoDocDate;

    -- update sl_do
    UPDATE sl_do SET doc_date = pNewDocDate WHERE do_id = vDoId;

    -- update gl_journal_trx_item (journal_date)
    UPDATE gl_journal_trx_item B SET journal_date = pNewDocDate
    FROM gl_journal_trx A
    WHERE A.journal_trx_id = B.journal_trx_id
    AND A.tenant_id = pTenantId
    AND A.doc_type_id = vDoDocTypeId
    AND A.doc_id = vDoId
    AND A.doc_no = pDoDocNo
    AND A.doc_date = pDoDocDate;

    -- update gl_journal_trx_mapping (journal_date)
    UPDATE gl_journal_trx_mapping B SET journal_date = pNewDocDate
    FROM gl_journal_trx A
    WHERE A.journal_trx_id = B.journal_trx_id
    AND A.tenant_id = pTenantId
    AND A.doc_type_id = vDoDocTypeId
    AND A.doc_id = vDoId
    AND A.doc_no = pDoDocNo
    AND A.doc_date = pDoDocDate;

    -- update gl_journal_trx (doc_date, due_date)
    UPDATE gl_journal_trx SET doc_date = pNewDocDate, due_date = pNewDocDate
    WHERE tenant_id = pTenantId
    AND doc_type_id = vDoDocTypeId
    AND doc_id = vDoId
    AND doc_no = pDoDocNo
    AND doc_date = pDoDocDate;

    -- awe_currdoc_status
    UPDATE awe_currdoc_status
    SET doc_date = pNewDocDate
    WHERE scheme = vSchemeDo
    AND doc_id = vDoId
    AND doc_no = pDoDocNo
    AND doc_date = pDoDocDate;

    -- awe_worklist
    UPDATE awe_worklist
    SET doc_date = pNewDocDate
    WHERE scheme = vSchemeDo
    AND doc_id = vDoId
    AND doc_no = pDoDocNo
    AND doc_date = pDoDocDate;

END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION sl_modify_doc_date_approved_do(bigint, character varying, bigint, character varying, character varying, character varying)
  OWNER TO sts;
GRANT EXECUTE ON FUNCTION sl_modify_doc_date_approved_do(bigint, character varying, bigint, character varying, character varying, character varying) TO public;
GRANT EXECUTE ON FUNCTION sl_modify_doc_date_approved_do(bigint, character varying, bigint, character varying, character varying, character varying) TO sts;