Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION in_cancel_closing_costing_raw_materials(bigint, character varying, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pProcessNo ALIAS FOR $3;
- vProcessId bigint;
- vOuId bigint;
- vUserId bigint;
- vDatetime character varying(14);
- vYearMonth character varying(6);
- vNextYearMonth character varying(6);
- vGroupProductRM character varying(5);
- vAdminProcessLedgerId bigint;
- vLedgerCodeCostRM character varying(10);
- vDocTypeAwal bigint;
- vEmptyId bigint;
- vStatusDraft character varying(1);
- BEGIN
- vLedgerCodeCostRM := 'COST.RM';
- vDocTypeAwal := -99;
- vGroupProductRM := 'RM';
- vEmptyId := -99;
- vStatusDraft := 'D';
- SELECT A.process_message_id INTO vProcessId
- FROM t_process_message A
- WHERE A.tenant_id = pTenantId AND
- A.process_name = 'in_cancel_closing_costing_raw_materials' AND
- A.process_no = pProcessNo;
- SELECT CAST(A.process_parameter_value AS bigint) INTO vOuId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'ouId';
- SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'userId';
- SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'datetime';
- SELECT CAST(A.process_parameter_value AS character varying(6)) INTO vYearMonth
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'yearmonth';
- SELECT CAST(A.process_parameter_value AS bigint) INTO vAdminProcessLedgerId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'adminProcessLedgerId';
- SELECT TO_CHAR(TO_DATE(vYearMonth,'YYYYMM') + interval '1 Month','YYYYMM') INTO vNextYearMonth;
- /*
- * Hapus harga dasar pokok di bulan ybs
- */
- DELETE FROM in_summary_monthly_cogs A
- WHERE A.date_year_month = vYearMonth AND
- A.ou_id = vOuId AND
- A.remark = vGroupProductRM;
- /*
- * Hapus data saldo akhir tahun bulan ybs, kecuali saldo awal
- */
- DELETE FROM in_summary_monthly_amount A
- WHERE A.date_year_month = vYearMonth AND
- A.ou_bu_id = vOuId AND
- A.doc_type_id <> vDocTypeAwal AND
- A.remark = vGroupProductRM;
- /*
- * delete data in_summary_monthly_zero_qty_amount
- */
- DELETE FROM in_summary_monthly_zero_qty_amount A
- WHERE A.date_year_month = vYearMonth AND
- A.ou_bu_id = vOuId AND
- A.doc_type_id <> vDocTypeAwal AND
- A.remark = vGroupProductRM;
- /*
- * Hapus data saldo awal tahun bulan berikutnya
- */
- DELETE FROM in_summary_monthly_amount A
- WHERE A.date_year_month = vNextYearMonth AND
- A.ou_bu_id = vOuId AND
- A.remark = vGroupProductRM;
- --delete data anomaly
- DELETE FROM in_anomaly_gl_amount_from_process_costing A
- WHERE A.date_year_month = vYearMonth AND
- A.ou_bu_id = vOuId AND
- A.doc_type_id <> vDocTypeAwal AND
- A.remark = vGroupProductRM;
- --delete data anomaly tahun bulan berikutnya
- DELETE FROM in_anomaly_gl_amount_from_process_costing A
- WHERE A.date_year_month = vNextYearMonth AND
- A.ou_bu_id = vOuId AND
- A.remark = vGroupProductRM;
- UPDATE m_admin_process_ledger SET status_ledger = 0, flag_process = 'N',
- process_no = pProcessNo, process_datetime = vDatetime, process_user_id = vUserId
- WHERE tenant_id = pTenantId AND
- ou_id = vOuId AND
- date_year_month = vYearMonth AND
- ledger_code = vLedgerCodeCostRM;
- /* Adrian, Feb 27, 2017
- * update status journal menjadi D
- */
- DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;
- INSERT INTO tt_gl_journal_costing
- (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- journal_trx_id, doc_type_id, journal_trx_item_id, sign_journal, product_id,
- qty, uom_id, amount, total_amount_product, total_qty_product)
- SELECT pSessionId, A.tenant_id, A.ou_bu_id,
- CASE WHEN B.ou_branch_id = vEmptyId AND B.ou_sub_bu_id = vEmptyId THEN A.ou_branch_id ELSE B.ou_branch_id END,
- CASE WHEN B.ou_branch_id = vEmptyId AND B.ou_sub_bu_id = vEmptyId THEN A.ou_sub_bu_id ELSE B.ou_sub_bu_id END,
- A.journal_trx_id, A.doc_type_id, B.journal_trx_item_id, B.sign_journal, B.product_id,
- B.qty, B.uom_id, 0, 0, 0
- FROM gl_journal_trx A, gl_journal_trx_item B, dt_date C, vw_raw_materials_product D
- WHERE A.tenant_id = pTenantId AND
- A.ou_bu_id = vOuId AND
- A.doc_type_id IN (502, 521, 413, 511, 311, 154, 354, 431, 526, 537) AND
- A.doc_date = C.string_date AND
- C.year_month_date = vYearMonth AND
- A.journal_trx_id = B.journal_trx_id AND
- B.product_id <> vEmptyId AND
- B.product_id = D.product_id;
- UPDATE gl_journal_trx SET status_doc = vStatusDraft, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
- FROM (SELECT journal_trx_id
- FROM tt_gl_journal_costing A
- WHERE A.session_id = pSessionId
- GROUP BY journal_trx_id) A
- WHERE gl_journal_trx.journal_trx_id = A.journal_trx_id;
- -- update status document khusus untuk document cost allocation to product (528), dan adjustment stok amount (522)
- UPDATE gl_journal_trx SET status_doc = vStatusDraft, version = version + 1, update_datetime = vDatetime, update_user_id = vUserId
- WHERE doc_type_id IN (528,522)
- AND SUBSTRING(doc_date, 1, 6) = vYearMonth;
- PERFORM in_delete_generate_adj_stock_amount_doc_for_process_costing(pTenantId, pSessionId, vYearMonth, vOuId, vDatetime, vUserId, vGroupProductRM);
- DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement