Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Function: i_ho_process_pos_shop_spk(bigint, character varying, character varying)
- -- DROP FUNCTION i_ho_process_pos_shop_spk(bigint, character varying, character varying);
- CREATE OR REPLACE FUNCTION i_ho_process_pos_shop_spk(
- bigint,
- character varying,
- character varying)
- RETURNS void AS
- $BODY$
- DECLARE
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pProcessNo ALIAS FOR $3;
- /*
- pTenantId ALIAS FOR $1;
- pSessionId ALIAS FOR $2;
- pProcessNo ALIAS FOR $3;
- pOutletId ALIAS FOR $4;
- pOuId ALIAS FOR $5;
- pWarehouseId ALIAS FOR $6;
- pDatetime ALIAS FOR $7;
- pUserId ALIAS FOR $8;
- */
- vFlagNo character varying(1);
- vProductStatus character varying(5);
- vEmptyId bigint;
- vDocTypeAdjStock bigint;
- vFlagCash character varying(1);
- vDebitType character varying(1);
- vFlagYes character varying(1);
- vEmptyValue character varying(1);
- vJournalType character varying(20);
- vJournalTypeDoPos character varying(20);
- vJournalTypeAdjStock character varying(20);
- vSignDebit character varying(1);
- vSignCredit character varying(1);
- vTypeRate character varying(3);
- vProductCOA character varying(10);
- vSystemCOA character varying(10);
- vTaxCOA character varying(10);
- vCashBankCOA character varying(10);
- vDocTypeDoPos bigint;
- vRounding integer;
- vParentOuId bigint;
- vActivityCOA character varying(10);
- vStatusPos character varying(1);
- vStatusDraft character varying(1);
- vActivityChargesId bigint;
- vProcessId bigint;
- pOutletId bigint;
- pOuId bigint;
- pWarehouseId bigint;
- pDatetime character varying(14);
- pUserId bigint;
- vDocJournalDoPos DOC_JOURNAL%ROWTYPE;
- vDocJournalAdjStock DOC_JOURNAL%ROWTYPE;
- vOuStructure OU_BU_STRUCTURE%ROWTYPE;
- result RECORD;
- vLedgerCode character varying(10);
- vPosDocTypeId bigint;
- vRoundingModeNonTax character varying(5);
- vFlgPkp character varying;
- vCountBelumDiBayar numeric;
- vDefaultRoleId bigint;
- vFlowArDebtNoteId bigint;
- vDebtNoteARDocScheme character varying(10);
- vStateDraft character varying(10);
- vFlagRole character varying(1);
- vDocTypeDebtNoteAr bigint;
- vCount character varying;
- vTotalTaxItem numeric;
- vTotalTaxItemNempil numeric;
- BEGIN
- vDocTypeDebtNoteAr := 241;
- vFlagRole := 'R';
- vStateDraft := 'DRAFT';
- vDebtNoteARDocScheme := 'DF01';
- vCountBelumDiBayar := 0;
- vTotalTaxItem := 0;
- vTotalTaxItemNempil := 0;
- vFlgPkp := 'N';
- vStatusPos := 'S';
- vFlagNo := 'N';
- vProductStatus := 'GOOD';
- vEmptyId := -99;
- vDocTypeAdjStock := 413;
- vFlagCash := 'C';
- vDebitType := 'D';
- vFlagYes := 'Y';
- vEmptyValue := ' ';
- vJournalType := 'POS.SHOP';
- vSignDebit := 'D';
- vSignCredit := 'C';
- vTypeRate := 'COM';
- vProductCOA := 'PRODUCT';
- vSystemCOA := 'SYSTEM';
- vTaxCOA := 'TAX';
- vCashBankCOA := 'CASHBANK';
- vParentOuId := -99;
- vActivityCOA := 'ACTIVITY'; -- DanieL : tambahan sementara agar tidak error
- --vActivityChargesId := 2;
- vActivityChargesId := f_get_value_system_config_by_param_code(pTenantId, 'CHARGES');
- vPosDocTypeId := 401;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
- SELECT A.process_message_id INTO vProcessId
- FROM t_process_message A
- WHERE A.tenant_id = pTenantId AND
- A.process_name = 'i_ho_process_pos_shop_spk' AND
- A.process_no = pProcessNo;
- SELECT CAST(A.process_parameter_value AS bigint) INTO pOutletId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'outletId';
- SELECT CAST(A.process_parameter_value AS bigint) INTO pOuId
- 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 pWarehouseId
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'warehouseId';
- SELECT CAST(A.process_parameter_value AS bigint) INTO pUserId
- 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 pDatetime
- FROM t_process_parameter A
- WHERE A.process_message_id = vProcessId AND
- A.process_parameter_key = 'datetime';
- /*
- * jenis transaksi bayangan untuk Delivery Order POS untuk kepentingan jurnal inventory POS
- */
- vDocTypeDoPos := 431;
- vStatusDraft := 'D';
- vLedgerCode := 'SALES';
- SELECT f_get_ou_bu_structure(pOuId) AS ou, f_get_document_journal(vDocTypeDoPos) as doc_do_pos, f_get_document_journal(vDocTypeAdjStock) as doc_adj_stock
- INTO result;
- vOuStructure := result.ou;
- vDocJournalDoPos := result.doc_do_pos;
- vDocJournalAdjStock := result.doc_adj_stock;
- /*
- * rounding nilai tax
- */
- vRounding := 0;
- -- get flag_pkp
- SELECT C.flg_pkp INTO vFlgPkp
- FROM m_ou_structure A
- INNER JOIN t_ou B ON A.ou_bu_id = B.ou_id
- INNER JOIN t_ou_legal C ON B.ou_id = C.ou_id AND B.tenant_id = C.tenant_id
- WHERE A.ou_id = pOuId AND B.tenant_id = pTenantId;
- -- cek LUNAS , jika count = 0 berarti LUNAS
- -- lUNAS = guest atau corporate bayar lunas
- -- get Lunas
- SELECT count(1) INTO vCountBelumDiBayar
- FROM
- i_trx_pos A
- INNER JOIN i_trx_pos_termin_payment B ON A.tenant_id = B.tenant_id AND A.process_no = B.process_no AND A.trx_pos_id = B.trx_pos_id
- INNER JOIN t_ou C ON A.ou_id = C.ou_id AND A.tenant_id = C.tenant_id
- WHERE A.tenant_id = pTenantId
- AND A.process_no = pProcessNo
- AND A.ou_id = pOuId
- AND B.under_payment_amount > 0;
- -- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- -- DELETE FROM i_tt_pos_cash_balance WHERE session_id = pSessionId;
- -- DELETE FROM i_tt_pos_product_balance_stock WHERE session_id = pSessionId;
- -- DELETE FROM i_tt_nempil_barang_balance_stock WHERE session_id = pSessionId;
- /*
- * buat data product pos yang akan digunakan untuk buat log product balance stock dan
- * update product balance stock
- * 1.product yang bukan product assembly ( product balance id <> -99 ) -- Didit : bukan service (product balance id <> -99)
- * 2.product assembly ( dari i_trx_pos_assembly_product )
- */
- INSERT INTO i_tt_pos_product_balance_stock
- (session_id, tenant_id, ou_id, doc_type_id, ref_id, partner_id,
- doc_no, doc_date, product_id, warehouse_id,
- product_balance_id, product_status, base_uom_id, qty)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id, vEmptyId,
- A.doc_no, A.doc_date, B.product_id, pWarehouseId,
- B.product_balance_id, vProductStatus, B.base_uom_id, SUM(B.qty)
- FROM i_trx_pos A, i_trx_pos_item B
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- B.product_balance_id <> vEmptyId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
- A.doc_no, A.doc_date, B.product_id, B.product_balance_id, B.base_uom_id;
- /*
- * insert log product balance stock berdasarkan data tampung di i_tt_pos_product_balance_stock
- */
- INSERT INTO in_log_product_balance_stock
- (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date,
- partner_id, product_id, warehouse_id, product_balance_id, product_status,
- base_uom_id, qty, VERSION, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status,
- A.base_uom_id, SUM(A.qty) * -1, 0, pDatetime, pUserId, pDatetime, pUserId
- FROM i_tt_pos_product_balance_stock A
- WHERE A.session_id = pSessionId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
- A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;
- /*
- * update product balance stock berdasarkan data tampung di i_tt_pos_product_balance_stock
- */
- WITH i_tt_pos_product_balance_stock_for_update_current AS (
- SELECT pSessionId AS session_id, A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_status, SUM(A.qty) AS qty
- FROM i_tt_pos_product_balance_stock A
- WHERE A.tenant_id = pTenantId
- AND A.session_id = pSessionId
- GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_status
- )
- UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty - A.qty, update_datetime = pDatetime , update_user_id = pUserId,
- VERSION = in_product_balance_stock.VERSION + 1
- FROM i_tt_pos_product_balance_stock_for_update_current A
- WHERE A.session_id = pSessionId AND
- A.tenant_id = in_product_balance_stock.tenant_id AND
- A.warehouse_id = in_product_balance_stock.warehouse_id AND
- A.product_id = in_product_balance_stock.product_id AND
- A.product_balance_id = in_product_balance_stock.product_balance_id AND
- A.product_status = in_product_balance_stock.product_status;
- /*
- * update stock nempil barang
- */
- INSERT INTO i_tt_nempil_barang_balance_stock
- (session_id, tenant_id, ou_id, doc_type_id,
- doc_no, doc_date, partner_name, product_code, product_name, qty)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id,
- A.doc_no, A.doc_date, f_get_partner_name(A.partner_id), B.product_code, B.product_name, SUM(B.qty) * -1
- FROM i_trx_pos A, i_trx_pos_item_nempil_barang B
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.partner_id,
- A.doc_no, A.doc_date, B.product_code, B.product_name;
- UPDATE p_nempil_barang_balance_stock SET qty = p_nempil_barang_balance_stock.qty + B.qty
- FROM (SELECT A.tenant_id, A.ou_id, A.product_code,
- SUM(A.qty) AS qty
- FROM i_tt_nempil_barang_balance_stock A
- WHERE A.session_id = pSessionId
- GROUP BY A.tenant_id, A.ou_id, A.product_code) B
- WHERE B.tenant_id = p_nempil_barang_balance_stock.tenant_id AND
- B.ou_id = p_nempil_barang_balance_stock.ou_id AND
- B.product_code = p_nempil_barang_balance_stock.product_code;
- /*
- INSERT INTO p_log_nempil_barang_balance_stock
- (tenant_id, ou_id, doc_type_id, doc_no, doc_date,
- partner_name, product_code, product_name, qty, VERSION, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
- A.partner_name, A.product_code, A.product_name, SUM(A.qty), 0, pDatetime, pUserId, pDatetime, pUserId
- FROM i_tt_nempil_barang_balance_stock A
- WHERE A.session_id = pSessionId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
- A.partner_name, A.product_code, A.product_name;
- */
- INSERT INTO in_log_nempil_barang_balance_stock
- (tenant_id, ou_id, doc_type_id, doc_no, doc_date,
- partner_name, product_code, product_name, qty, VERSION, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
- A.partner_name, A.product_code, A.product_name, SUM(A.qty), 0, pDatetime, pUserId, pDatetime, pUserId
- FROM i_tt_nempil_barang_balance_stock A
- WHERE A.session_id = pSessionId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
- A.partner_name, A.product_code, A.product_name;
- /*
- * mengolah data pos untuk :
- * 1.insert cb_trx_cashbank_balance
- *
- * journal :
- * 1.journal DO POS Shop
- * 2.journal POS Shop
- */
- /*
- * tampung data pos cash payment untuk update ke data cb_cashbank_balance
- * dan cb_trx_cashbank_balance
- */
- INSERT INTO i_tt_pos_cash_balance
- (session_id, tenant_id, ou_id, doc_type_id, doc_no, payment_id,
- partner_id, curr_code, amount,
- cashbank_id, cash_bank_date, ref_doc_type_id, ref_id)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.trx_pos_id,
- A.partner_id, B.curr_payment_code, SUM(B.payment_amount),
- C.cashbank_id, A.doc_date, A.doc_type_id, A.trx_pos_id
- FROM i_trx_pos A, i_trx_pos_cash_payment B, i_cash_bank_data_outlet C, m_cashbank D
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- C.outlet_id = pOutletId AND
- C.cashbank_id = D.cashbank_id AND
- B.curr_payment_code = D.curr_code AND
- D.flg_cash_bank = vFlagCash
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.trx_pos_id,
- A.partner_id, B.curr_payment_code, C.cashbank_id, A.doc_date;
- /*
- * tampung data pos total refund untuk update ke data cb_cashbank_balance
- * dan cb_trx_cashbank_balance
- */
- INSERT INTO i_tt_pos_cash_balance
- (session_id, tenant_id, ou_id, doc_type_id, doc_no, payment_id,
- partner_id, curr_code, amount,
- cashbank_id, cash_bank_date, ref_doc_type_id, ref_id)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.trx_pos_id,
- A.partner_id, A.curr_code, SUM(A.total_refund) * -1,
- B.cashbank_id, A.doc_date, A.doc_type_id, A.trx_pos_id
- FROM i_trx_pos A, i_cash_bank_data_outlet B, m_cashbank C
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- B.outlet_id = pOutletId AND
- B.cashbank_id = C.cashbank_id AND
- A.curr_code = C.curr_code AND
- C.flg_cash_bank = vFlagCash
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.trx_pos_id,
- A.partner_id, A.curr_code, B.cashbank_id, A.doc_date;
- /*
- * insert ke data cb_trx_cashbank_balance
- */
- INSERT INTO cb_trx_cashbank_balance
- (tenant_id, ou_id, doc_type_id, payment_id,
- payment_doc_no, payment_doc_date, payment_remark,
- partner_id, partner_bank_id, curr_code, amount,
- due_date, flg_payment, ref_doc_type_id, ref_id,
- version, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.payment_id,
- A.doc_no, A.cash_bank_date, vEmptyValue,
- A.partner_id, vEmptyId, A.curr_code, SUM(A.amount),
- A.cash_bank_date, vFlagYes, A.ref_doc_type_id, A.ref_id,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM i_tt_pos_cash_balance A
- WHERE A.session_id = pSessionId
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.payment_id, A.doc_no, A.partner_id, A.curr_code,
- A.cashbank_id, A.cash_bank_date, A.ref_doc_type_id, A.ref_id;
- /*
- * insert cb_cashbank_balance yang data belum ada
- */
- INSERT INTO cb_cashbank_balance
- (tenant_id, ou_id, cashbank_id, cash_bank_date,
- rec_type, curr_code, amount,
- VERSION, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, D.ou_id, A.cashbank_id, A.cash_bank_date,
- vDebitType, A.curr_code, 0,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM i_tt_pos_cash_balance A, m_cashbank_ou D
- WHERE A.session_id = pSessionId AND
- D.cashbank_id = A.cashbank_id AND
- NOT EXISTS (SELECT 1 FROM cb_cashbank_balance B
- WHERE A.tenant_id = B.tenant_id AND
- D.ou_id = B.ou_id AND
- A.cashbank_id = B.cashbank_id AND
- A.cash_bank_date = B.cash_bank_date AND
- B.rec_type = vDebitType)
- GROUP BY A.tenant_id, A.cashbank_id, A.cash_bank_date, A.curr_code, D.ou_id;
- /*
- * update cb_cashbank_balance yang sudah ada
- */
- UPDATE cb_cashbank_balance SET amount = cb_cashbank_balance.amount + B.amount
- FROM (SELECT A.tenant_id, D.ou_id, A.cashbank_id, A.cash_bank_date, A.curr_code,
- SUM(A.amount) AS amount
- FROM i_tt_pos_cash_balance A, m_cashbank_ou D
- WHERE A.session_id = pSessionId AND
- D.cashbank_id = A.cashbank_id
- GROUP BY A.tenant_id, A.cashbank_id, A.cash_bank_date, A.curr_code, D.ou_id) B
- WHERE B.tenant_id = cb_cashbank_balance.tenant_id AND
- B.ou_id = cb_cashbank_balance.ou_id AND
- B.cashbank_id = cb_cashbank_balance.cashbank_id AND
- B.cash_bank_date = cb_cashbank_balance.cash_bank_date AND
- B.curr_code = cb_cashbank_balance.curr_code AND
- cb_cashbank_balance.rec_type = vDebitType;
- /*
- * buat saldo payment non cash, menggunakan EDC
- */
- INSERT INTO cb_balance_settlement
- (tenant_id, ou_id, doc_type_id, doc_no, doc_date,
- device_merchant_id, card_type, bank_card_code, card_no,
- curr_payment_code, amount_payment, flg_settle, cashbank_id, cash_bank_date,
- ref_doc_type_id, ref_id,
- VERSION, create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
- B.device_merchant_id, B.card_type, B.bank_card_code, B.card_no,
- B.curr_payment_code, SUM(B.payment_amount), vFlagNo, vEmptyId, vEmptyValue,
- vEmptyId, vEmptyId,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM i_trx_pos A, i_trx_pos_non_cash_payment B
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.trx_pos_id = B.trx_pos_id AND
- A.process_no = B.process_no AND
- A.tenant_id = B.tenant_id
- GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
- B.device_merchant_id, B.card_type, B.bank_card_code, B.card_no,
- B.curr_payment_code;
- /*
- * journal DO POS di group by product
- * Credit Inventory
- * Debit COGS
- */
- PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournalDoPos).journal_type, (vDocJournalDoPos).ledger_code, f_get_year_month_date(A.doc_date), 'MONTHLY', pDatetime, pUserId)
- FROM i_trx_pos A
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos;
- INSERT INTO gl_journal_trx
- (tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
- ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, (vDocJournalDoPos).journal_type, vDocTypeDoPos, A.trx_pos_id, A.doc_no, A.doc_date,
- (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, pWarehouseId, A.ext_doc_no, A.ext_doc_date,
- A.ref_doc_type_id, A.ref_id, A.doc_date, f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), A.remark, vStatusDraft, 'DRAFT',
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM i_trx_pos A
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- EXISTS( -- added by Didit, 25 Juni 2018. Transaksi POS yang hanya produk nempil saja / service saja tidak perlu dibuatkan jurnal stock
- SELECT 1 FROM i_trx_pos_item B
- WHERE A.trx_pos_id = B.trx_pos_id AND
- A.process_no = B.process_no AND
- A.tenant_id = B.tenant_id
- );
- INSERT INTO tt_journal_trx_item
- (session_id, 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, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
- A.doc_type_id, vEmptyId,
- A.partner_id, B.product_id, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vProductCOA, vEmptyId,
- f_get_product_coa_group_product(A.tenant_id, B.product_id), f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), SUM(B.qty), B.base_uom_id,
- 0 , A.doc_date, vTypeRate,
- 1, 1, 'PRODUCT_STOCK', vEmptyValue
- FROM i_trx_pos A, i_trx_pos_item B, gl_journal_trx C
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.trx_pos_id = B.trx_pos_id AND
- A.process_no = B.process_no AND
- A.tenant_id = B.tenant_id AND
- A.trx_pos_id = C.doc_id AND
- A.tenant_id = C.tenant_id AND
- C.doc_type_id = vDocTypeDoPos AND
- C.journal_type = (vDocJournalDoPos).journal_type AND
- C.ou_bu_id = (vOuStructure).ou_bu_id AND
- C.ou_branch_id = (vOuStructure).ou_branch_id AND
- C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
- -- A.ou_id = C.sub_ou_id AND
- A.doc_no = C.doc_no
- GROUP BY A.tenant_id, C.journal_trx_id, A.doc_type_id,
- A.partner_id, B.product_id, B.base_uom_id, A.doc_date;
- INSERT INTO gl_journal_trx_item
- (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, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
- A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
- A.coa_id, A.curr_code, A.qty, A.uom_id,
- A.amount, A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId;
- /*
- * NK, 1 Feb 2014
- * journal COGS tidak perlu breakdown per product, karena nilainya akan merupakan summary dari PRODUCT_STOCK
- * (sebelumnya breakdown per product)
- */
- INSERT INTO gl_journal_trx_mapping
- (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, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark, -- DanieL : penambahan journal_desc
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
- vEmptyId, vEmptyId,
- vEmptyId, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), A.curr_code, 0, vEmptyId,
- 0, A.journal_date, A.type_rate,
- 1, 1, 'COGS', vEmptyValue,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId
- GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate, A.curr_code;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- /*
- * journal POS
- * Credit Sales nett price * qty, without tax
- * Credit BankCharges add amount
- * Credit Tax Amount tax amount ( sudah nett price )
- * Credit Rounding rouding
- * Credit Refund Cash refund amount
- * Debit Cash cash payment
- * Debit AccSettlement non cash payment
- */
- PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, vJournalType, vLedgerCode, f_get_year_month_date(A.doc_date), 'DAILY', pDatetime, pUserId)
- FROM i_trx_pos A
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos;
- INSERT INTO gl_journal_trx
- (tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
- ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
- ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, vJournalType, A.doc_type_id, A.trx_pos_id, A.doc_no, A.doc_date,
- (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, pWarehouseId, A.ext_doc_no, A.ext_doc_date,
- A.ref_doc_type_id, A.ref_id, A.doc_date, A.curr_code, A.remark, vStatusDraft, 'DRAFT',
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM i_trx_pos A
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos;
- INSERT INTO tt_journal_trx_item
- (session_id, 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, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
- A.doc_type_id, vEmptyId,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'PendapatanPenjualan'), B.curr_code, SUM(B.qty), B.base_uom_id,
- --f_get_amount_before_tax(SUM(B.nett_sell_price * B.qty),B.flg_tax_amount,B.tax_percentage,f_get_digit_decimal_doc_curr(vPosDocTypeId, B.curr_code),vRoundingModeNonTax) , A.doc_date, vTypeRate,
- CASE WHEN vFlgPkp = vFlagYes THEN
- f_get_amount_before_tax(SUM(B.nett_sell_price * B.qty),B.flg_tax_amount,B.tax_percentage,f_get_digit_decimal_doc_curr(vPosDocTypeId, B.curr_code),vRoundingModeNonTax) ELSE
- f_calculate_dpp_amount_from_gross_price(SUM(B.nett_sell_price * B.qty),1,0,B.flg_tax_amount,B.tax_percentage,f_get_digit_decimal_doc_curr(vPosDocTypeId, B.curr_code),'RHU') + f_calculate_tax_amount_from_gross_price(SUM(B.nett_sell_price * B.qty),1,0,B.flg_tax_amount,B.tax_percentage,f_get_digit_decimal_doc_curr(vPosDocTypeId, B.curr_code),'RHU')
- --f_get_amount_before_tax(SUM(B.nett_sell_price * B.qty),B.flg_tax_amount,B.tax_percentage,f_get_digit_decimal_doc_curr(vPosDocTypeId, B.curr_code),vRoundingModeNonTax) + SUM(B.tax_amount)
- END
- , A.doc_date, vTypeRate,
- 1, 1, 'SALES_POS', vEmptyValue
- FROM i_trx_pos A, i_trx_pos_item B, gl_journal_trx C
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.trx_pos_id = B.trx_pos_id AND
- A.process_no = B.process_no AND
- A.tenant_id = B.tenant_id AND
- A.trx_pos_id = C.doc_id AND
- A.tenant_id = C.tenant_id AND
- A.doc_type_id = C.doc_type_id AND
- C.journal_type = vJournalType AND
- C.ou_bu_id = (vOuStructure).ou_bu_id AND
- C.ou_branch_id = (vOuStructure).ou_branch_id AND
- C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
- -- A.ou_id = C.sub_ou_id AND
- A.doc_no = C.doc_no
- GROUP BY A.tenant_id, C.journal_trx_id, A.doc_type_id,
- A.partner_id, B.curr_code, B.base_uom_id, B.flg_tax_amount, B.tax_percentage, A.doc_date;
- IF vFlgPkp = vFlagYes THEN
- -- hitung total tax item
- SELECT SUM(B.tax_amount) INTO vTotalTaxItem
- FROM i_trx_pos A, i_trx_pos_item B
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.trx_pos_id = B.trx_pos_id AND
- A.process_no = B.process_no AND
- A.tenant_id = B.tenant_id;
- IF vTotalTaxItem > 0 THEN
- SELECT SUM(B.tax_amount)-vTotalTaxItem INTO vTotalTaxItemNempil
- FROM i_trx_pos A, i_trx_pos_tax B
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.trx_pos_id = B.trx_pos_id AND
- A.process_no = B.process_no AND
- A.tenant_id = B.tenant_id;
- END IF;
- END IF;
- -- jurnal penjualan dari produk nempil, 6 Nov 2015
- WITH data_tax_pos AS(
- SELECT A.trx_pos_id, A.process_no, A.tenant_id,
- A.doc_type_id, SUM(B.tax_amount) AS tax_amount
- FROM i_trx_pos A, i_trx_pos_item B
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.trx_pos_id = B.trx_pos_id AND
- A.process_no = B.process_no AND
- A.tenant_id = B.tenant_id
- GROUP BY A.trx_pos_id, A.tenant_id, A.process_no, A.doc_type_id
- ),
- data_tax_total AS(
- SELECT A.trx_pos_id, A.process_no, A.tenant_id,
- A.doc_type_id, SUM(B.tax_amount) AS tax_amount
- FROM i_trx_pos A, i_trx_pos_tax B
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.trx_pos_id = B.trx_pos_id AND
- A.process_no = B.process_no AND
- A.tenant_id = B.tenant_id
- GROUP BY A.trx_pos_id, A.tenant_id, A.process_no, A.doc_type_id
- ),
- data_tax_nempil AS(
- SELECT A.trx_pos_id, A.process_no, A.tenant_id,
- A.doc_type_id, A.tax_amount - COALESCE(B.tax_amount,0) AS tax_amount
- FROM data_tax_total A
- LEFT JOIN data_tax_pos B ON A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- A.process_no = B.process_no AND
- A.doc_type_id = B.doc_type_id
- )
- INSERT INTO tt_journal_trx_item
- (session_id, 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, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
- A.doc_type_id, vEmptyId,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'PendapatanPenjualan'), B.curr_code, SUM(B.qty), vEmptyId,
- CASE WHEN vFlgPkp = vFlagYes THEN
- SUM(B.sell_price) - COALESCE(D.tax_amount, 0) ELSE -- sell price = gross sell price * qty
- SUM(B.sell_price)
- END,
- A.doc_date, vTypeRate,
- 1, 1, 'SALES_POS', vEmptyValue
- FROM i_trx_pos A
- INNER JOIN i_trx_pos_item_nempil_barang B ON A.trx_pos_id = B.trx_pos_id AND
- A.process_no = B.process_no AND
- A.tenant_id = B.tenant_id
- INNER JOIN gl_journal_trx C ON A.trx_pos_id = C.doc_id AND
- A.tenant_id = C.tenant_id AND
- A.doc_type_id = C.doc_type_id AND
- A.doc_no = C.doc_no
- LEFT JOIN data_tax_nempil D ON A.trx_pos_id = D.trx_pos_id AND
- A.tenant_id = D.tenant_id AND
- A.doc_type_id = D.doc_type_id AND A.process_no = D.process_no
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- C.journal_type = vJournalType AND
- C.ou_bu_id = (vOuStructure).ou_bu_id AND
- C.ou_branch_id = (vOuStructure).ou_branch_id AND
- C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id
- -- A.ou_id = C.sub_ou_id AND
- GROUP BY A.tenant_id, C.journal_trx_id, A.doc_type_id,
- A.partner_id, B.curr_code, A.doc_date, D.tax_amount;
- INSERT INTO tt_journal_trx_item
- (session_id, 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, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
- A.doc_type_id, B.trx_pos_add_cost_id,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'BiayaKartuKredit'), B.curr_code, 0, vEmptyId,
- B.add_amount, A.doc_date, vTypeRate,
- 1, 1, 'CHARGES_POS', B.remark
- FROM i_trx_pos A, i_trx_pos_add_cost B, gl_journal_trx C
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.trx_pos_id = B.trx_pos_id AND
- A.process_no = B.process_no AND
- A.tenant_id = B.tenant_id AND
- A.trx_pos_id = C.doc_id AND
- A.tenant_id = C.tenant_id AND
- A.doc_type_id = C.doc_type_id AND
- C.journal_type = vJournalType AND
- C.ou_bu_id = (vOuStructure).ou_bu_id AND
- C.ou_branch_id = (vOuStructure).ou_branch_id AND
- C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
- -- A.ou_id = C.sub_ou_id AND
- A.doc_no = C.doc_no AND
- B.activity_gl_id = vActivityChargesId;
- -- add if By: Ping An , 27 Nov 2015
- -- Jurnal VAT Hanya untuk yang PKP saja , kalo non PKP gak perlu dijurnal PPN karena non PKP tidak memungut dan melaporkan PPN
- IF vFlgPkp = vFlagYes THEN
- INSERT INTO tt_journal_trx_item
- (session_id, 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, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
- A.doc_type_id, vEmptyId,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vTaxCOA, vEmptyId,
- D.create_coa_id, D.tax_curr_code, 0, vEmptyId,
- B.tax_amount , A.doc_date, vTypeRate,
- 1, 1, 'VAT_OUT_POS', vEmptyValue
- FROM i_trx_pos A, i_trx_pos_tax B, gl_journal_trx C, m_tax D
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.trx_pos_id = B.trx_pos_id AND
- A.process_no = B.process_no AND
- A.tenant_id = B.tenant_id AND
- B.tax_id = D.tax_id AND
- A.trx_pos_id = C.doc_id AND
- A.tenant_id = C.tenant_id AND
- A.doc_type_id = C.doc_type_id AND
- C.journal_type = vJournalType AND
- C.ou_bu_id = (vOuStructure).ou_bu_id AND
- C.ou_branch_id = (vOuStructure).ou_branch_id AND
- C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
- -- A.ou_id = C.sub_ou_id AND
- A.doc_no = C.doc_no;
- END IF;
- INSERT INTO tt_journal_trx_item
- (session_id, 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, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
- A.doc_type_id, vEmptyId,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
- f_get_system_coa_by_group_coa(A.tenant_id, 'BiayaPembulatanNilai'), A.curr_code, 0, vEmptyId,
- A.rounding_amount, A.doc_date, vTypeRate,
- 1, 1, 'ROUNDING_POS', C.remark
- FROM i_trx_pos A, gl_journal_trx C
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.trx_pos_id = C.doc_id AND
- A.tenant_id = C.tenant_id AND
- A.doc_type_id = C.doc_type_id AND
- C.journal_type = vJournalType AND
- C.ou_bu_id = (vOuStructure).ou_bu_id AND
- C.ou_branch_id = (vOuStructure).ou_branch_id AND
- C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
- -- A.ou_id = C.sub_ou_id AND
- A.doc_no = C.doc_no;
- INSERT INTO tt_journal_trx_item
- (session_id, 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, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
- A.doc_type_id, vEmptyId,
- A.partner_id, vEmptyId, E.cashbank_id, vEmptyId,
- vEmptyId, vSignCredit, vCashBankCOA, vEmptyId,
- E.coa_id, A.curr_code, 0, vEmptyId,
- A.total_refund, A.doc_date, vTypeRate,
- 1, 1, 'REFUND_POS', A.remark
- FROM i_trx_pos A, gl_journal_trx C, i_cash_bank_data_outlet D, m_cashbank E
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.trx_pos_id = C.doc_id AND
- A.tenant_id = C.tenant_id AND
- A.doc_type_id = C.doc_type_id AND
- D.outlet_id = pOutletId AND
- D.cashbank_id = E.cashbank_id AND
- A.curr_code = E.curr_code AND
- E.flg_cash_bank = vFlagCash AND
- C.journal_type = vJournalType AND
- C.ou_bu_id = (vOuStructure).ou_bu_id AND
- C.ou_branch_id = (vOuStructure).ou_branch_id AND
- C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
- -- A.ou_id = C.sub_ou_id AND
- A.doc_no = C.doc_no AND
- A.total_refund > 0;
- INSERT INTO tt_journal_trx_item
- (session_id, 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, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
- A.doc_type_id, B.trx_pos_cash_payment_id,
- A.partner_id, vEmptyId, E.cashbank_id, vEmptyId,
- vEmptyId, vSignDebit, vCashBankCOA, vEmptyId,
- E.coa_id, B.curr_payment_code, 0, vEmptyId,
- B.payment_amount, A.doc_date, vTypeRate,
- 1, 1, 'CASH_PAYMENT_POS', B.remark
- FROM i_trx_pos A, i_trx_pos_cash_payment B, gl_journal_trx C, i_cash_bank_data_outlet D, m_cashbank E
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- D.outlet_id = pOutletId AND
- D.cashbank_id = E.cashbank_id AND
- B.curr_payment_code = E.curr_code AND
- E.flg_cash_bank = vFlagCash AND
- A.trx_pos_id = C.doc_id AND
- A.tenant_id = C.tenant_id AND
- A.doc_type_id = C.doc_type_id AND
- C.journal_type = vJournalType AND
- C.ou_bu_id = (vOuStructure).ou_bu_id AND
- C.ou_branch_id = (vOuStructure).ou_branch_id AND
- C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
- -- A.ou_id = C.sub_ou_id AND
- A.doc_no = C.doc_no AND
- B.payment_amount > 0; -- add by: Ping An 30 Nov 2015, ditambah filter kalau tidak ada pembayaran tidak perlu dijurnal
- INSERT INTO tt_journal_trx_item
- (session_id, 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, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
- A.doc_type_id, B.trx_pos_non_cash_payment_id,
- A.partner_id, vEmptyId, E.cashbank_id, vEmptyId,
- vEmptyId, vSignDebit, vCashBankCOA, vEmptyId,
- E.settle_coa_id, B.curr_payment_code, 0, vEmptyId,
- B.payment_amount, A.doc_date, vTypeRate,
- 1, 1, 'NON_CASH_PAYMENT_POS', B.remark
- FROM i_trx_pos A, i_trx_pos_non_cash_payment B, gl_journal_trx C, i_device_merchant_data_outlet D, m_device_merchant E
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- D.outlet_id = pOutletId AND
- B.device_merchant_id = D.device_merchant_id AND
- D.device_merchant_id = E.device_merchant_id AND
- A.trx_pos_id = C.doc_id AND
- A.tenant_id = C.tenant_id AND
- A.doc_type_id = C.doc_type_id AND
- C.journal_type = vJournalType AND
- C.ou_bu_id = (vOuStructure).ou_bu_id AND
- C.ou_branch_id = (vOuStructure).ou_branch_id AND
- C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
- -- A.ou_id = C.sub_ou_id AND
- A.doc_no = C.doc_no AND
- B.payment_amount > 0; -- add by: Ping An 30 Nov 2015, ditambah filter kalau tidak ada pembayaran tidak perlu dijurnal
- -- hanya dijalankan jika ada kurang bayar(BELUM LUNAS PEMBAYARANNYA ), kalau sudah lunas tidak perlu dijurnal DP
- IF vCountBelumDiBayar > 0 THEN
- -- sisa pembayaran dipindahkan ke AR
- INSERT INTO tt_journal_trx_item
- (session_id, 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, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
- A.doc_type_id, A.ref_id,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
- f_get_ar_coa_partner(A.tenant_id, D.partner_id), A.curr_code, 0, vEmptyId,
- -- rev by Ping An 17 Nov 2015, ambil nilai utuhnya karena nnti di submit invoice debt note ar yg dari POS dijaga tidak akan dijurnal
- -- B.under_payment_amount, A.doc_date, vTypeRate,
- A.total_amount, A.doc_date, vTypeRate,
- 1, 1, 'AR_POS', 'NILAI DEBIT NOTE AR'
- FROM i_trx_pos A, gl_journal_trx C, i_trx_pos_ext D, i_trx_pos_termin_payment E
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.trx_pos_id = D.trx_pos_id AND
- -- rev by: Ping An , 28 Jan 2016 joinnya kurang process_no dan tenant_id
- A.process_no = D.process_no AND
- A.tenant_id = D.tenant_id AND
- A.tenant_id = E.tenant_id AND
- A.process_no = E.process_no AND
- A.trx_pos_id = E.trx_pos_id AND
- E.under_payment_amount > 0 AND
- A.trx_pos_id = C.doc_id AND
- A.tenant_id = C.tenant_id AND
- A.doc_type_id = C.doc_type_id AND
- C.journal_type = vJournalType AND
- C.ou_bu_id = (vOuStructure).ou_bu_id AND
- C.ou_branch_id = (vOuStructure).ou_branch_id AND
- C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
- -- A.ou_id = C.sub_ou_id AND
- A.doc_no = C.doc_no;
- END IF;
- -- TAMBAHKAN JURNAL DP add by Ping An 18 Nov 2015
- -- karena nantinya di function submit invoice AR, debt note ar yang tercreate dari POS tidak akan di jurnal, jadi perlu dijurnal disini DP ARnya
- -- jika ada bayar termin dari customer corporate
- -- K m_activity_gl SYSTEM DP_AR_POS
- INSERT INTO tt_journal_trx_item
- ( session_id, 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, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark)
- SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
- A.trx_pos_id, B.trx_pos_termin_payment_id,
- A.partner_id, vEmptyId, vEmptyId, vEmptyId,
- vEmptyId, vSignCredit, vSystemCOA, D.activity_gl_id,
- D.coa_id, B.curr_payment_code, 0, vEmptyId,
- B.payment_amount, A.doc_date, vTypeRate,
- 1, 1, 'DP_AR_POS', 'NILAI DP AR'
- FROM i_trx_pos A, i_trx_pos_termin_payment B, gl_journal_trx C, m_activity_gl D
- WHERE A.tenant_id = pTenantId AND
- A.ou_id = pOuId AND
- A.process_no = pProcessNo AND
- A.status = vStatusPos AND
- A.process_no = B.process_no AND
- A.trx_pos_id = B.trx_pos_id AND
- A.tenant_id = B.tenant_id AND
- A.trx_pos_id = C.doc_id AND
- A.tenant_id = C.tenant_id AND
- A.doc_type_id = C.doc_type_id AND
- C.journal_type = vJournalType AND
- C.ou_bu_id = (vOuStructure).ou_bu_id AND
- C.ou_branch_id = (vOuStructure).ou_branch_id AND
- C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
- -- A.ou_id = C.sub_ou_id AND
- A.doc_no = C.doc_no AND
- B.under_payment_amount > 0 AND -- ditambah filter: jika LUNAS tidak perlu di jurnal DP
- B.payment_amount > 0 AND -- ditambah filter : jika tidak ada pembayaran juga tidak perlu di jurnal DP
- D.activity_gl_id = f_get_activity_gl_id_for_downpayment(A.tenant_id);
- -- END PENAMBAHKAN JURNAL DP add by Ping An 18 Nov 2015
- INSERT INTO gl_journal_trx_item
- (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, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
- A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
- A.coa_id, A.curr_code, A.qty, A.uom_id,
- A.amount, A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- A.journal_desc IN ('SALES_POS','CHARGES_POS','VAT_OUT_POS', 'ROUNDING_POS', 'REFUND_POS', 'DP_AR_POS');
- INSERT INTO gl_journal_trx_mapping
- (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, qty, uom_id,
- amount, journal_date, type_rate,
- numerator_rate, denominator_rate, journal_desc, remark,
- "version", create_datetime, create_user_id, update_datetime, update_user_id)
- SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
- A.ref_doc_type_id, A.ref_id,
- A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
- A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
- A.coa_id, A.curr_code, A.qty, A.uom_id,
- A.amount, A.journal_date, A.type_rate,
- A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
- 0, pDatetime, pUserId, pDatetime, pUserId
- FROM tt_journal_trx_item A
- WHERE A.session_id = pSessionId AND
- -- rev by Ping An 18nov2015 A.journal_desc IN ('CASH_PAYMENT_POS','NON_CASH_PAYMENT_POS');
- A.journal_desc IN ('CASH_PAYMENT_POS','NON_CASH_PAYMENT_POS','AR_POS');
- -- buat nota debit ar untuk transaksi pos yg masih hutang
- -- added by didit, 25 September 2015
- PERFORM f_migrate_data_trx_pos_to_debt_note_ar(pSessionId, A.tenant_id, A.process_no, A.ou_id, pUserId, pDatetime, vFlgPkp, CASE WHEN COALESCE(B.under_payment_amount, -1) < 0 THEN 0 ELSE 1 END, A.trx_pos_id)
- FROM i_trx_pos A
- LEFT JOIN i_trx_pos_termin_payment B ON A.tenant_id = B.tenant_id AND A.process_no = B.process_no AND A.trx_pos_id = B.trx_pos_id
- WHERE A.tenant_id = pTenantId
- AND A.process_no = pProcessNo
- AND A.ou_id = pOuId;
- DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
- DELETE FROM i_tt_pos_cash_balance WHERE session_id = pSessionId;
- DELETE FROM i_tt_pos_product_balance_stock WHERE session_id = pSessionId;
- DELETE FROM i_tt_nempil_barang_balance_stock WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100;
- /
Add Comment
Please, Sign In to add comment