Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement