Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- /*
- * WTC, 20161219:
- * 1. Update sign journal untuk trx mapping adj stock qty (521), baik yg bernilai minus maupun plus (sebelumnya hanya yg bernilai minus)
- * 2. Exclude adj stock amount dalam proses posting journal ini, melainkan set status doc nya saja menjadi R
- */
- CREATE OR REPLACE FUNCTION gl_posting_raw_materials_costing_journal(bigint, character varying, bigint, bigint, character varying, character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pOuId ALIAS FOR $3;
- pUserId ALIAS FOR $4;
- pYearMonth ALIAS FOR $5;
- pDatetime ALIAS FOR $6;
- vStatusRelease character varying(1);
- vEmptyValue character varying(1);
- vEmptyId bigint;
- vJournalProcessPeriode character varying(10);
- vLedgerCode character varying(10);
- vCurrGL character varying(5);
- vRoundingAmount integer;
- vSignCredit character varying(1);
- vSignDebit character varying(1);
- vSystemCOA character varying(10);
- BEGIN
- vStatusRelease := 'R';
- vEmptyId := -99;
- vEmptyValue := ' ';
- vJournalProcessPeriode := 'MONTHLY';
- vLedgerCode := 'COSTING.FG';
- vSignCredit := 'C';
- vSignDebit := 'D';
- vSystemCOA := 'SYSTEM';
- DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;
- DELETE FROM tt_gl_summary_amount_product WHERE session_id = pSessionId;
- DELETE FROM tt_gl_summary_journal WHERE session_id = pSessionId;
- DELETE FROM tt_gl_journal_trx_mapping WHERE session_id = pSessionId;
- DELETE FROM tt_gl_admin_journal_rate WHERE session_id = pSessionId;
- vCurrGL := f_get_value_system_config_by_param_code(pTenantId,'ValutaBuku');
- vRoundingAmount := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);
- /*
- * 1.ambil data journal trx yang melakukan Debit / Credit Stok finish goods product
- * doc type : 502, 521, 413, 511, 311, 154, 354, 431, 526, 537
- * doc_desc : adj stok amount balance, return note, adj stok qty, adj stok outlet qty,
- * Nota klaim, DO, DO Internal, Sales Invoice Konsinyasi, POS Shop, Return POS Shop, Pos Shop In Shop, Return POS Shop In Shop,
- * DO Receipt, Goods Transfer In Receipt Lost
- * 2.berikan nilai tiap journal trx item sesuai dengan product, doc_type_id, ou_bu_id, ou_branch_id, ou_sub_bu_id di in_summary_monthly_product
- * 3.update nilai amount, untuk memastikan jumlah amount di in_summary_monthly_product = jumlah nilai product di tt_gl_journal_costing
- *
- * WTC, 20161219, adjustment stock amount tidak perlu diset lagi dari hasil proses costing, melainkan diset saja status nya menjadi R
- */
- 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 = pOuId 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 = pYearMonth AND
- A.journal_trx_id = B.journal_trx_id AND
- B.product_id <> vEmptyId AND
- B.product_id = D.product_id;
- /* Update amount dipisahkan menjadi 3 bagian, yaitu:
- 1. Tuk doc selain adj qty
- 2. Tuk doc adj qty yg qty > 0
- 3. Tuk doc adj qty yg qty < 0
- Hal ini perlu dilakukan, karena pada in_summary_monthly_amount bisa ada 2 nilai berbeda tuk doc adj qty dgn product_id yg sama,
- yaitu yg mengurangi stock dan yg menambah stock, dimana yg mengurangi stock nilainya berdasarkan nilai cogs,
- sedangkan yg menambah stock nilainya berdasarkan harga standar/
- */
- UPDATE tt_gl_journal_costing SET amount = ROUND(tt_gl_journal_costing.qty * (A.gl_amount / A.qty), vRoundingAmount),
- total_amount_product = CASE WHEN (A.gl_amount / A.qty) < 0 THEN -1*ABS(A.gl_amount) ELSE ABS(A.gl_amount) END,
- total_qty_product = ABS(A.qty)
- FROM in_summary_monthly_amount A
- WHERE tt_gl_journal_costing.session_id = pSessionId AND
- tt_gl_journal_costing.tenant_id = A.tenant_id AND
- tt_gl_journal_costing.ou_bu_id = A.ou_bu_id AND
- tt_gl_journal_costing.ou_branch_id = A.ou_branch_id AND
- tt_gl_journal_costing.ou_sub_bu_id = A.ou_sub_bu_id AND
- tt_gl_journal_costing.doc_type_id = A.doc_type_id AND
- tt_gl_journal_costing.product_id = A.product_id AND
- A.date_year_month = pYearMonth AND
- A.qty <> 0 AND
- A.doc_type_id <> 521;
- UPDATE tt_gl_journal_costing SET amount = ROUND(tt_gl_journal_costing.qty * (A.gl_amount / A.qty), vRoundingAmount),
- total_amount_product = CASE WHEN (A.gl_amount / A.qty) < 0 THEN -1*ABS(A.gl_amount) ELSE ABS(A.gl_amount) END,
- total_qty_product = ABS(A.qty)
- FROM in_summary_monthly_amount A
- WHERE tt_gl_journal_costing.session_id = pSessionId AND
- tt_gl_journal_costing.tenant_id = A.tenant_id AND
- tt_gl_journal_costing.ou_bu_id = A.ou_bu_id AND
- tt_gl_journal_costing.ou_branch_id = A.ou_branch_id AND
- tt_gl_journal_costing.ou_sub_bu_id = A.ou_sub_bu_id AND
- tt_gl_journal_costing.doc_type_id = A.doc_type_id AND
- tt_gl_journal_costing.product_id = A.product_id AND
- A.date_year_month = pYearMonth AND
- A.doc_type_id = 521 AND
- A.qty > 0 AND
- tt_gl_journal_costing.sign_journal = vSignDebit;
- UPDATE tt_gl_journal_costing SET amount = ROUND(tt_gl_journal_costing.qty * (A.gl_amount / A.qty), vRoundingAmount),
- total_amount_product = CASE WHEN (A.gl_amount / A.qty) < 0 THEN -1*ABS(A.gl_amount) ELSE ABS(A.gl_amount) END,
- total_qty_product = ABS(A.qty)
- FROM in_summary_monthly_amount A
- WHERE tt_gl_journal_costing.session_id = pSessionId AND
- tt_gl_journal_costing.tenant_id = A.tenant_id AND
- tt_gl_journal_costing.ou_bu_id = A.ou_bu_id AND
- tt_gl_journal_costing.ou_branch_id = A.ou_branch_id AND
- tt_gl_journal_costing.ou_sub_bu_id = A.ou_sub_bu_id AND
- tt_gl_journal_costing.doc_type_id = A.doc_type_id AND
- tt_gl_journal_costing.product_id = A.product_id AND
- A.date_year_month = pYearMonth AND
- A.doc_type_id = 521 AND
- A.qty < 0 AND
- tt_gl_journal_costing.sign_journal = vSignCredit;
- /*
- * summary untuk menghitung total nilai alokasi hasil costing dari in_summary_monthly_amount
- * selisih nilai alokasi dan total nilai dari in_summary_monthly_amount akan diberikan
- * ke item journal trx yang paling maksimum id nya.
- * Mod by WTC, 20161019, Khusus untuk dokumen adj stock qty, perlu memperhatikan sign journal nya juga
- */
- INSERT INTO tt_gl_summary_amount_product
- (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, journal_trx_item_id, alloc_amount, gl_amount)
- SELECT A.session_id, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
- A.doc_type_id, A.product_id, MAX(A.journal_trx_item_id), SUM(A.amount), A.total_amount_product
- FROM tt_gl_journal_costing A
- WHERE A.session_id = pSessionId AND
- A.doc_type_id NOT IN (521)
- GROUP BY A.session_id, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
- A.doc_type_id, A.product_id, A.total_amount_product;
- INSERT INTO tt_gl_summary_amount_product
- (session_id, tenant_id, ou_bu_id, ou_branch_id, ou_sub_bu_id,
- doc_type_id, product_id, journal_trx_item_id, alloc_amount, gl_amount, sign_journal)
- SELECT A.session_id, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
- A.doc_type_id, A.product_id, MAX(A.journal_trx_item_id), SUM(A.amount), A.total_amount_product, A.sign_journal
- FROM tt_gl_journal_costing A
- WHERE A.session_id = pSessionId AND
- A.doc_type_id IN (521)
- GROUP BY A.session_id, A.tenant_id, A.ou_bu_id, A.ou_branch_id, A.ou_sub_bu_id,
- A.doc_type_id, A.product_id, A.total_amount_product, A.sign_journal;
- UPDATE tt_gl_journal_costing SET amount = amount + A.gl_amount - A.alloc_amount
- FROM tt_gl_summary_amount_product A
- WHERE A.session_id = pSessionId AND
- tt_gl_journal_costing.session_id = A.session_id AND
- tt_gl_journal_costing.journal_trx_item_id = A.journal_trx_item_id AND
- A.alloc_amount <> A.gl_amount;
- /*
- * update nilai buku untuk semua transaksi di gl_journal_trx_item
- */
- UPDATE gl_journal_trx_item SET numerator_rate = 1, denominator_rate = 1,
- gl_amount = A.amount,
- gl_curr_code = vCurrGL
- FROM tt_gl_journal_costing A
- WHERE A.session_id = pSessionId AND
- gl_journal_trx_item.journal_trx_item_id = A.journal_trx_item_id;
- /*
- * hitung summary journal per trx, nilai summary akan digunakan
- * 1.untuk update nilai item journal mapping
- * 2.untuk hitung selisih forex : hny untuk trx Nota Klaim
- */
- INSERT INTO tt_gl_summary_journal
- (session_id, ctgr_journal, journal_trx_id, sign_journal,
- gl_amount, temp_amount)
- SELECT A.session_id, 'ITEM', A.journal_trx_id, A.sign_journal,
- SUM(A.amount), SUM(A.amount)
- FROM tt_gl_journal_costing A
- WHERE A.session_id = pSessionId
- GROUP BY session_id, journal_trx_id, sign_journal;
- /*
- * ambil data journal trx mapping untuk doc type id = Nota Klaim
- * hitung rate dan nilai buku nya
- * update langsung data gl_journal_trx_mapping nya
- */
- INSERT INTO tt_gl_journal_trx_mapping
- (session_id, doc_type_id, journal_trx_mapping_id, journal_trx_id, line_no,
- sign_journal, curr_code, amount,
- journal_date, type_rate)
- SELECT pSessionId, A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
- D.sign_journal, D.curr_code, D.amount,
- D.journal_date, D.type_rate
- FROM tt_gl_journal_costing A, gl_journal_trx_mapping D
- WHERE A.session_id = pSessionId AND
- A.doc_type_id = 511 AND
- A.journal_trx_id = D.journal_trx_id
- GROUP BY A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
- D.sign_journal, D.curr_code, D.amount, D.journal_date, D.type_rate;
- INSERT INTO tt_gl_admin_journal_rate
- (session_id, type_rate, journal_date, curr_code, gl_curr_code, data_exchange_rate)
- SELECT pSessionId, A.type_rate, A.journal_date, A.curr_code, vCurrGL, f_get_exchange_rate(pTenantId, A.type_rate, A.journal_date, A.curr_code, vCurrGL)
- FROM (
- SELECT A.journal_date, A.type_rate, A.curr_code
- FROM tt_gl_journal_trx_mapping A
- WHERE A.session_id = pSessionId
- GROUP BY journal_date, type_rate, curr_code) A;
- UPDATE tt_gl_journal_trx_mapping SET numerator_rate = (data_exchange_rate).numerator_rate,
- denominator_rate = (data_exchange_rate).denominator_rate,
- gl_amount = ROUND(amount * (data_exchange_rate).numerator_rate / (data_exchange_rate).denominator_rate, vRoundingAmount)
- FROM tt_gl_admin_journal_rate A
- WHERE tt_gl_journal_trx_mapping.session_id = pSessionId AND
- tt_gl_journal_trx_mapping.session_id = A.session_id AND
- tt_gl_journal_trx_mapping.type_rate = A.type_rate AND
- tt_gl_journal_trx_mapping.journal_date = A.journal_date AND
- tt_gl_journal_trx_mapping.curr_code = A.curr_code;
- /*
- * update nilai buku untuk item journal mapping Nota Klaim
- */
- UPDATE gl_journal_trx_mapping SET numerator_rate = A.numerator_rate, denominator_rate = A.denominator_rate, gl_amount = A.gl_amount, gl_curr_code = vCurrGL
- FROM tt_gl_journal_trx_mapping A
- WHERE A.session_id = pSessionId AND
- gl_journal_trx_mapping.journal_trx_mapping_id = A.journal_trx_mapping_id;
- /*
- * buat data journal trx mapping untuk doc type yang bukan nota klaim
- * dengan menghitung total nilai dari tt_gl_journal_costing
- * hal ini dilakukan untuk memastikan jika terjadi kesalahan coding dalam membuat template journal
- * maka nanti akan bisa muncul Forex, padahal transaksi selain Nota Klaim seharusnya tidak ada forex.
- *
- * Khusus untuk adj qty, amountnya harus memperhatikan sign journal nya, karena dlm 1 trx adj qty,
- * bisa saja memiliki 2 jenis journal item dengan sign journal yg berbeda.
- */
- INSERT INTO tt_gl_journal_trx_mapping
- (session_id, doc_type_id, journal_trx_mapping_id, journal_trx_id, line_no,
- sign_journal, curr_code, amount, gl_amount,
- journal_date, type_rate)
- SELECT pSessionId, A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
- D.sign_journal, D.curr_code, D.amount, SUM(A.amount),
- D.journal_date, D.type_rate
- FROM tt_gl_journal_costing A, gl_journal_trx_mapping D
- WHERE A.session_id = pSessionId AND
- A.doc_type_id NOT IN (511, 521, 536) AND
- A.journal_trx_id = D.journal_trx_id
- GROUP BY A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
- D.sign_journal, D.curr_code, D.journal_date, D.type_rate;
- INSERT INTO tt_gl_journal_trx_mapping
- (session_id, doc_type_id, journal_trx_mapping_id, journal_trx_id, line_no,
- sign_journal, curr_code, amount, gl_amount,
- journal_date, type_rate)
- SELECT pSessionId, A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
- D.sign_journal, D.curr_code, D.amount, SUM(CASE WHEN A.sign_journal = vSignDebit THEN A.amount ELSE -1 * A.amount END),
- D.journal_date, D.type_rate
- FROM tt_gl_journal_costing A, gl_journal_trx_mapping D
- WHERE A.session_id = pSessionId AND
- A.doc_type_id IN (521, 536) AND
- A.journal_trx_id = D.journal_trx_id
- GROUP BY A.doc_type_id, D.journal_trx_mapping_id, D.journal_trx_id, D.line_no,
- D.sign_journal, D.curr_code, D.journal_date, D.type_rate;
- /*
- * WTC, 170130, Harus update yg gl_amount nya positif dl. Jika terbalik, maka akibatnya semua akan terupdate sign_journal menjadi C (karena semua gl_amount sudah bernilai positif)
- * Update sign_journal menjadi C untuk doc adj qty yg amountnya > 0
- */
- UPDATE tt_gl_journal_trx_mapping SET sign_journal = vSignCredit
- WHERE doc_type_id IN (521, 536) AND
- gl_amount > 0 AND
- session_id = pSessionId;
- /*
- * Update sign_journal menjadi D untuk doc adj qty yg amountnya < 0
- */
- UPDATE tt_gl_journal_trx_mapping SET sign_journal = vSignDebit, gl_amount = ABS(gl_amount)
- WHERE doc_type_id IN (521, 536) AND
- gl_amount < 0 AND
- session_id = pSessionId;
- /*
- * update gl_journal_trx_mapping untuk nilai buku transaksi yang bukan Nota Klaim
- */
- UPDATE gl_journal_trx_mapping SET numerator_rate = 1, denominator_rate = 1,
- gl_amount = A.gl_amount,
- gl_curr_code = vCurrGL,
- sign_journal = A.sign_journal
- FROM tt_gl_journal_trx_mapping A
- WHERE A.session_id = pSessionId AND
- A.doc_type_id <> 511 AND
- gl_journal_trx_mapping.journal_trx_mapping_id = A.journal_trx_mapping_id;
- INSERT INTO tt_gl_summary_journal
- (session_id, ctgr_journal, journal_trx_id, sign_journal, gl_amount, temp_amount)
- SELECT A.session_id, 'MAPPING', A.journal_trx_id, A.sign_journal, SUM(A.gl_amount), SUM(A.gl_amount)
- FROM tt_gl_journal_trx_mapping A
- WHERE A.session_id = pSessionId
- GROUP BY session_id, journal_trx_id, sign_journal;
- UPDATE tt_gl_summary_journal SET temp_amount = temp_amount * -1
- WHERE sign_journal = vSignCredit AND
- session_id = pSessionId;
- INSERT INTO tt_gl_summary_journal
- (session_id, ctgr_journal, journal_trx_id, sign_journal, gl_amount, temp_amount)
- SELECT A.session_id, 'ALL', A.journal_trx_id, vSignCredit, 0, SUM(A.temp_amount)
- FROM tt_gl_summary_journal A
- WHERE A.session_id = pSessionId
- GROUP BY A.session_id, A.journal_trx_id;
- UPDATE tt_gl_summary_journal SET sign_journal = vSignDebit, temp_amount = temp_amount * -1
- WHERE session_id = pSessionId AND
- ctgr_journal = 'ALL' AND
- temp_amount < 0;
- DELETE FROM gl_journal_trx_fx A
- WHERE EXISTS (
- SELECT 1
- FROM tt_gl_summary_journal B
- WHERE A.journal_trx_id = B.journal_trx_id AND
- B.ctgr_journal = 'ALL' AND
- B.session_id = pSessionId
- );
- /*
- * buat transaksi forex
- * seharusnya cuma ada untuk transaksi Nota Klaim
- */
- INSERT INTO gl_journal_trx_fx
- (tenant_id, journal_trx_id, line_no, ref_doc_type_id, ref_id,
- partner_id, product_id, cashbank_id, ou_rc_id, segmen_id,
- sign_journal, flg_source_coa, activity_gl_id, coa_id,
- curr_code, amount, journal_date, type_rate,
- numerator_rate, denominator_rate, gl_curr_code, gl_amount,
- journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT B.tenant_id, A.journal_trx_id, 1, vEmptyId, vEmptyId,
- vEmptyId, vEmptyId, vEmptyId, vEmptyId, vEmptyId,
- A.sign_journal, vSystemCOA, vEmptyId, f_get_system_coa_by_group_coa(B.tenant_id, 'BiayaSelisihKurs'),
- vCurrGL, A.temp_amount, B.doc_date, vEmptyValue,
- 1, 1, vCurrGL, A.temp_amount,
- 'FX', 'Forex ' || C.doc_desc || ' ' || B.doc_date,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_gl_summary_journal A,
- gl_journal_trx B, m_document C
- WHERE A.session_id = pSessionId AND
- A.journal_trx_id = B.journal_trx_id AND
- B.doc_type_id = C.doc_type_id AND
- A.ctgr_journal = 'ALL' AND
- A.temp_amount <> 0;
- /*
- * data rate dari transaksi klaim
- */
- INSERT INTO gl_admin_journal_rate
- (tenant_id, journal_year_month, process_datetime, process_user_id,
- journal_date, type_rate, curr_code, gl_curr_code, numerator_rate, denominator_rate)
- SELECT pTenantId, pYearMonth, pDatetime, pUserId,
- journal_date, type_rate, curr_code, gl_curr_code, (data_exchange_rate).numerator_rate, (data_exchange_rate).denominator_rate
- FROM tt_gl_admin_journal_rate
- WHERE session_id = pSessionId AND
- curr_code <> gl_curr_code;
- UPDATE gl_admin_journal_trx SET process_datetime = pDatetime, process_user_id = pUserId
- WHERE tenant_id = pTenantId AND
- ou_id = pOuId AND
- journal_process_periode = vJournalProcessPeriode AND
- ledger_code = vLedgerCode AND
- journal_year_month = pYearMonth;
- UPDATE gl_journal_trx SET status_doc = vStatusRelease, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
- 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 = vStatusRelease, version = version + 1, update_datetime = pDatetime, update_user_id = pUserId
- WHERE doc_type_id IN (528,522)
- AND SUBSTRING(doc_date, 1, 6) = pYearMonth;
- DELETE FROM tt_gl_journal_costing WHERE session_id = pSessionId;
- DELETE FROM tt_gl_summary_amount_product WHERE session_id = pSessionId;
- DELETE FROM tt_gl_summary_journal WHERE session_id = pSessionId;
- DELETE FROM tt_gl_journal_trx_mapping WHERE session_id = pSessionId;
- DELETE FROM tt_gl_admin_journal_rate WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement