abirama62

coba edit func void pos

May 11th, 2020
233
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION i_ho_void_pos_shop(
  2.     bigint,
  3.     character varying,
  4.     character varying,
  5.     bigint,
  6.     bigint,
  7.     bigint,
  8.     bigint,
  9.     character varying,
  10.     character varying,
  11.     bigint)
  12.   RETURNS void AS
  13. $BODY$
  14. DECLARE
  15.   pTenantId         ALIAS FOR $1;
  16.   pSessionId            ALIAS FOR $2;
  17.   pProcessNo            ALIAS FOR $3;
  18.   pOutletId         ALIAS FOR $4;
  19.   pOuId             ALIAS FOR $5;
  20.   pWarehouseId      ALIAS FOR $6;
  21.   pUserId               ALIAS FOR $7;
  22.   pDatetime         ALIAS FOR $8;
  23.   pDateVoid         ALIAS FOR $9;
  24.   pPosId            ALIAS FOR $10;
  25.  
  26. vFlagNo             character varying(1);
  27.     vProductStatus      character varying(5);
  28.     vEmptyId            bigint;
  29.     vDocTypeAdjStock    bigint;
  30.     vFlagCash           character varying(1);
  31.     vDebitType          character varying(1);
  32.     vFlagYes            character varying(1);
  33.     vEmptyValue     character varying(1);
  34.     vJournalType        character varying(20);
  35.     vJournalTypeDoPos       character varying(20);
  36.     vJournalTypeAdjStock    character varying(20);
  37.     vSignDebit          character varying(1);
  38.     vSignCredit         character varying(1);
  39.     vTypeRate           character varying(3);
  40.     vProductCOA         character varying(10);
  41.     vSystemCOA          character varying(10);
  42.     vTaxCOA             character varying(10);
  43.     vCashBankCOA        character varying(10);
  44.     vDocTypeDoPos           bigint;
  45.     vRounding           integer;
  46.     vParentOuId         bigint;
  47.     vActivityCOA        character varying(10);
  48.     vStatusPos          character varying(1);
  49.     vStatusDraft        character varying(1);
  50.     vActivityChargesId  bigint;
  51.  
  52.     vProcessId          bigint;
  53.  
  54.     vDocJournalDoPos        DOC_JOURNAL%ROWTYPE;
  55.     vDocJournalAdjStock     DOC_JOURNAL%ROWTYPE;
  56.     vOuStructure            OU_BU_STRUCTURE%ROWTYPE;
  57.     result                  RECORD;
  58.     vLedgerCode             character varying(10);
  59.  
  60.     vPosDocTypeId bigint;
  61.     vRoundingModeNonTax character varying(5);
  62.  
  63.     vFlgPkp                 character varying;
  64.     vCountBelumDiBayar      numeric;
  65.     vDefaultRoleId          bigint;
  66.     vFlowArDebtNoteId       bigint;
  67.     vDebtNoteARDocScheme    character varying(10);
  68.     vStateDraft             character varying(10);
  69.     vFlagRole               character varying(1);
  70.     vDocTypeDebtNoteAr      bigint;
  71.     vCount                  character varying;
  72.     vTotalTaxItem           numeric;
  73.     vTotalTaxItemNempil     numeric;
  74.     vVoidState      character varying;
  75.     vCreditType     character varying;
  76.  
  77. BEGIN
  78.  vDocTypeDebtNoteAr := 241; -- TODO: ganti ke CreditNoteAR
  79.     vFlagRole := 'R';
  80.     vStateDraft := 'DRAFT';
  81.     vDebtNoteARDocScheme := 'DF01'; -- TODO: ganti ke CreditNoteAR
  82.     vCountBelumDiBayar := 0;
  83.     vTotalTaxItem := 0;
  84.     vTotalTaxItemNempil := 0;
  85.     vFlgPkp := 'N';
  86.     vStatusPos := 'V'; -- hanya mencari yg status POS Void
  87.     vFlagNo := 'N';
  88.     vProductStatus := 'GOOD';
  89.     vEmptyId := -99;
  90.     vDocTypeAdjStock := 413;
  91.     vFlagCash := 'C';
  92.     vDebitType := 'D';
  93.     vFlagYes := 'Y';
  94.     vEmptyValue := ' ';
  95.     vJournalType := 'POS.SHOP';
  96.     vSignDebit := 'D';
  97.     vSignCredit := 'C';
  98.     vTypeRate := 'COM';
  99.     vProductCOA := 'PRODUCT';
  100.     vSystemCOA := 'SYSTEM';
  101.     vTaxCOA := 'TAX';
  102.     vCashBankCOA := 'CASHBANK';
  103.     vParentOuId := -99;
  104.     vActivityCOA := 'ACTIVITY';
  105.     vActivityChargesId := f_get_value_system_config_by_param_code(pTenantId, 'CHARGES');
  106.     vVoidState := '_VOID';
  107.     vCreditType := 'C';
  108.  
  109.     vPosDocTypeId := 401;
  110.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
  111.  
  112.  
  113.  
  114.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
  115.  
  116.     DELETE FROM i_tt_pos_cash_balance WHERE session_id = pSessionId;
  117.  
  118.     DELETE FROM i_tt_pos_product_balance_stock WHERE session_id = pSessionId;
  119.  
  120.     DELETE FROM i_tt_nempil_barang_balance_stock WHERE session_id = pSessionId;
  121.  
  122.  
  123.     /*
  124.      * jenis transaksi bayangan untuk Delivery Order POS untuk kepentingan jurnal inventory POS
  125.      */
  126.     vDocTypeDoPos := 431;
  127.     vStatusDraft := 'D';
  128.     vLedgerCode := 'SALES';
  129.  
  130.     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
  131.         INTO result;
  132.  
  133.     vOuStructure := result.ou;
  134.     vDocJournalDoPos := result.doc_do_pos;
  135.     vDocJournalAdjStock := result.doc_adj_stock;
  136.  
  137.     /*
  138.      * rounding nilai tax
  139.      */
  140.     vRounding := 0;
  141.  
  142.     -- get flag_pkp
  143.     SELECT C.flg_pkp INTO vFlgPkp
  144.     FROM m_ou_structure A
  145.     INNER JOIN t_ou B ON A.ou_bu_id = B.ou_id
  146.     INNER JOIN t_ou_legal C ON B.ou_id = C.ou_id AND B.tenant_id = C.tenant_id
  147.     WHERE A.ou_id = pOuId AND B.tenant_id = pTenantId;
  148.  
  149.     -- cek LUNAS , jika count = 0 berarti LUNAS
  150.     -- lUNAS = guest atau corporate bayar lunas
  151.     -- get Lunas
  152.     SELECT count(1) INTO vCountBelumDiBayar
  153.     FROM i_trx_pos A
  154.     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
  155.     INNER JOIN t_ou C ON A.ou_id = C.ou_id AND A.tenant_id = C.tenant_id
  156.     WHERE A.tenant_id = pTenantId
  157.     AND A.process_no = pProcessNo
  158.         AND A.ou_id = pOuId
  159.         AND A.trx_pos_id = pPosId
  160.         AND B.under_payment_amount > 0;
  161.  
  162.   -- 1. update data stock dan log stock sesuai dengan tgl void
  163.     /*
  164.      * buat data product pos yang akan digunakan untuk buat log product balance stock dan
  165.      * update product balance stock
  166.      * 1.product yang bukan product assembly ( product balance id <> -99 ) -- Didit : bukan service (product balance id <> -99)
  167.      * 2.product assembly ( dari i_trx_pos_assembly_product )
  168.      */
  169.     INSERT INTO i_tt_pos_product_balance_stock
  170.     (session_id, tenant_id, ou_id, doc_type_id, ref_id, partner_id,
  171.     doc_no, doc_date, product_id, warehouse_id,
  172.     product_balance_id, product_status, base_uom_id, qty)
  173.     SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id, vEmptyId,
  174.         A.doc_no, pDateVoid, B.product_id, pWarehouseId,
  175.         B.product_balance_id, vProductStatus, B.base_uom_id, SUM(B.qty)
  176.     FROM i_trx_pos A, i_trx_pos_item B
  177.     WHERE A.tenant_id = pTenantId AND
  178.         A.ou_id = pOuId AND
  179.         A.process_no = pProcessNo AND
  180.         A.status = vStatusPos AND
  181.         A.process_no = B.process_no AND
  182.         A.trx_pos_id = B.trx_pos_id AND
  183.         A.tenant_id = B.tenant_id AND
  184.         A.trx_pos_id = pPosId AND
  185.         B.product_balance_id <> vEmptyId
  186.     GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.trx_pos_id,
  187.         A.doc_no, B.product_id, B.product_balance_id, B.base_uom_id;
  188.  
  189.     /*
  190.      * insert log product balance stock berdasarkan data tampung di i_tt_pos_product_balance_stock
  191.      */
  192.     INSERT INTO in_log_product_balance_stock
  193.     (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date,
  194.      partner_id, product_id, warehouse_id, product_balance_id, product_status,
  195.      base_uom_id, qty, VERSION, create_datetime, create_user_id, update_datetime, update_user_id)
  196.     SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
  197.         A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status,
  198.         A.base_uom_id, SUM(A.qty), 0, pDatetime, pUserId, pDatetime, pUserId
  199.     FROM i_tt_pos_product_balance_stock A
  200.     WHERE A.session_id = pSessionId
  201.     GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.ref_id, A.doc_no, A.doc_date,
  202.         A.partner_id, A.product_id, A.warehouse_id, A.product_balance_id, A.product_status, A.base_uom_id ;
  203.  
  204.     /*
  205.      * update product balance stock berdasarkan data tampung di i_tt_pos_product_balance_stock
  206.      */
  207.     WITH i_tt_pos_product_balance_stock_for_update_current AS (
  208.         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
  209.         FROM i_tt_pos_product_balance_stock A
  210.         WHERE A.tenant_id = pTenantId
  211.         AND A.session_id = pSessionId
  212.         GROUP BY A.tenant_id, A.warehouse_id, A.product_id, A.product_balance_id, A.product_status
  213.     )
  214.     UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty + A.qty, update_datetime = pDatetime , update_user_id = pUserId,
  215.         VERSION = in_product_balance_stock.VERSION + 1
  216.     FROM i_tt_pos_product_balance_stock_for_update_current A
  217.     WHERE A.session_id = pSessionId AND
  218.         A.tenant_id = in_product_balance_stock.tenant_id AND
  219.         A.warehouse_id = in_product_balance_stock.warehouse_id AND
  220.         A.product_id = in_product_balance_stock.product_id AND
  221.         A.product_balance_id = in_product_balance_stock.product_balance_id AND
  222.         A.product_status = in_product_balance_stock.product_status;
  223.  
  224.     /*
  225.      * update stock nempil barang
  226.      */
  227.     INSERT INTO i_tt_nempil_barang_balance_stock
  228.     (session_id, tenant_id, ou_id, doc_type_id,
  229.     doc_no, doc_date, partner_name, product_code, product_name, qty)
  230.     SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id,
  231.         A.doc_no, pDateVoid, f_get_partner_name(A.partner_id), B.product_code, B.product_name, SUM(B.qty)
  232.     FROM i_trx_pos A, i_trx_pos_item_nempil_barang B
  233.     WHERE  A.tenant_id = pTenantId AND
  234.            A.ou_id = pOuId AND
  235.            A.process_no = pProcessNo AND
  236.            A.status = vStatusPos AND
  237.            A.process_no = B.process_no AND
  238.            A.trx_pos_id = B.trx_pos_id AND
  239.            A.trx_pos_id = pPosId AND
  240.            A.tenant_id = B.tenant_id
  241.     GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.partner_id,
  242.         A.doc_no, B.product_code, B.product_name;
  243.  
  244.    UPDATE p_nempil_barang_balance_stock SET qty = p_nempil_barang_balance_stock.qty - B.qty
  245.     FROM (SELECT A.tenant_id, A.ou_id, A.product_code,
  246.                 SUM(A.qty) AS qty
  247.         FROM i_tt_nempil_barang_balance_stock A
  248.         WHERE A.session_id = pSessionId
  249.         GROUP BY A.tenant_id, A.ou_id, A.product_code) B
  250.     WHERE B.tenant_id = p_nempil_barang_balance_stock.tenant_id AND
  251.         B.ou_id = p_nempil_barang_balance_stock.ou_id AND
  252.         B.product_code = p_nempil_barang_balance_stock.product_code;
  253.  
  254.     INSERT INTO p_log_nempil_barang_balance_stock
  255.     (tenant_id, ou_id, doc_type_id, doc_no, doc_date,
  256.      partner_name, product_code, product_name, qty, VERSION, create_datetime, create_user_id, update_datetime, update_user_id)
  257.     SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
  258.         A.partner_name, A.product_code, A.product_name, SUM(A.qty), 0, pDatetime, pUserId, pDatetime, pUserId
  259.     FROM i_tt_nempil_barang_balance_stock A
  260.     WHERE A.session_id = pSessionId
  261.     GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
  262.         A.partner_name, A.product_code, A.product_name;
  263.  
  264.     /*
  265.      * tampung data pos cash payment untuk update ke data cb_cashbank_balance
  266.      * dan cb_trx_cashbank_balance
  267.      */
  268.     INSERT INTO i_tt_pos_cash_balance
  269.     (session_id, tenant_id, ou_id, doc_type_id, doc_no, payment_id,
  270.     partner_id, curr_code, amount,
  271.     cashbank_id, cash_bank_date, ref_doc_type_id, ref_id)
  272.     SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.trx_pos_id,
  273.         A.partner_id, B.curr_payment_code, SUM(B.payment_amount),
  274.         C.cashbank_id, pDateVoid, A.doc_type_id, A.trx_pos_id
  275.     FROM i_trx_pos A, i_trx_pos_cash_payment B, i_cash_bank_data_outlet C, m_cashbank D
  276.     WHERE A.tenant_id = pTenantId AND
  277.         A.ou_id = pOuId AND
  278.         A.process_no = pProcessNo AND
  279.         A.status = vStatusPos AND
  280.         A.process_no = B.process_no AND
  281.         A.trx_pos_id = B.trx_pos_id AND
  282.         A.tenant_id = B.tenant_id AND
  283.         C.outlet_id = pOutletId AND
  284.         C.cashbank_id = D.cashbank_id AND
  285.         B.curr_payment_code = D.curr_code AND
  286.         A.trx_pos_id = pPosId AND
  287.         D.flg_cash_bank = vFlagCash
  288.     GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.trx_pos_id,
  289.         A.partner_id, B.curr_payment_code, C.cashbank_id;
  290.  
  291.     /*
  292.      * tampung data pos total refund untuk update ke data cb_cashbank_balance
  293.      * dan cb_trx_cashbank_balance
  294.      */
  295.     INSERT INTO i_tt_pos_cash_balance
  296.     (session_id, tenant_id, ou_id, doc_type_id, doc_no, payment_id,
  297.     partner_id, curr_code, amount,
  298.     cashbank_id, cash_bank_date, ref_doc_type_id, ref_id)
  299.     SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.trx_pos_id,
  300.         A.partner_id, A.curr_code, SUM(A.total_refund) * -1,
  301.         B.cashbank_id, pDateVoid, A.doc_type_id, A.trx_pos_id
  302.     FROM i_trx_pos A, i_cash_bank_data_outlet B, m_cashbank C
  303.     WHERE A.tenant_id = pTenantId AND
  304.         A.ou_id = pOuId AND
  305.         A.process_no = pProcessNo AND
  306.         A.status = vStatusPos AND
  307.         B.outlet_id = pOutletId AND
  308.         B.cashbank_id = C.cashbank_id AND
  309.         A.curr_code = C.curr_code AND
  310.         A.trx_pos_id = pPosId AND
  311.         C.flg_cash_bank = vFlagCash
  312.     GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.trx_pos_id,
  313.         A.partner_id, A.curr_code, B.cashbank_id;
  314.  
  315.     /*
  316.      * insert ke data cb_trx_cashbank_balance, amount set to be minus because void
  317.      */
  318.     INSERT INTO cb_trx_cashbank_balance
  319.     (tenant_id, ou_id, doc_type_id, payment_id,
  320.     payment_doc_no, payment_doc_date, payment_remark,
  321.     partner_id, partner_bank_id, curr_code, amount,
  322.     due_date, flg_payment, ref_doc_type_id, ref_id,
  323.     version, create_datetime, create_user_id, update_datetime, update_user_id)
  324.     SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.payment_id,
  325.         CONCAT(A.doc_no, vVoidState), A.cash_bank_date, vEmptyValue,
  326.         A.partner_id, vEmptyId, A.curr_code, SUM(A.amount) * -1,
  327.         A.cash_bank_date, vFlagYes, A.ref_doc_type_id, A.ref_id,
  328.         0, pDatetime, pUserId, pDatetime, pUserId
  329.     FROM i_tt_pos_cash_balance A
  330.     WHERE A.session_id = pSessionId
  331.     GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.payment_id, A.doc_no, A.partner_id, A.curr_code,
  332.         A.cashbank_id, A.cash_bank_date, A.ref_doc_type_id, A.ref_id;
  333.  
  334.     /*
  335.      * insert cb_cashbank_balance yang data belum ada
  336.      */
  337.     INSERT INTO cb_cashbank_balance
  338.     (tenant_id, ou_id, cashbank_id, cash_bank_date,
  339.     rec_type, curr_code, amount,
  340.     VERSION, create_datetime, create_user_id, update_datetime, update_user_id)
  341.     SELECT A.tenant_id, D.ou_id, A.cashbank_id, A.cash_bank_date,
  342.         vCreditType, A.curr_code, 0,
  343.         0, pDatetime, pUserId, pDatetime, pUserId
  344.     FROM i_tt_pos_cash_balance A, m_cashbank_ou D
  345.     WHERE A.session_id = pSessionId AND
  346.         D.cashbank_id = A.cashbank_id AND
  347.         NOT EXISTS (SELECT 1 FROM cb_cashbank_balance B
  348.                     WHERE A.tenant_id = B.tenant_id AND
  349.                         D.ou_id = B.ou_id AND
  350.                         A.cashbank_id = B.cashbank_id AND
  351.                         A.cash_bank_date = B.cash_bank_date AND
  352.                         B.rec_type = vCreditType)
  353.     GROUP BY A.tenant_id, A.cashbank_id, A.cash_bank_date, A.curr_code, D.ou_id;
  354.  
  355.     /*
  356.      * update cb_cashbank_balance yang sudah ada
  357.      */
  358.     UPDATE cb_cashbank_balance SET amount = cb_cashbank_balance.amount + B.amount
  359.     FROM (SELECT A.tenant_id, D.ou_id, A.cashbank_id, A.cash_bank_date, A.curr_code,
  360.                 SUM(A.amount) AS amount
  361.         FROM i_tt_pos_cash_balance A, m_cashbank_ou D
  362.         WHERE A.session_id = pSessionId AND
  363.             D.cashbank_id = A.cashbank_id
  364.         GROUP BY A.tenant_id, A.cashbank_id, A.cash_bank_date, A.curr_code, D.ou_id) B
  365.     WHERE B.tenant_id = cb_cashbank_balance.tenant_id AND
  366.         B.ou_id = cb_cashbank_balance.ou_id AND
  367.         B.cashbank_id = cb_cashbank_balance.cashbank_id AND
  368.         B.cash_bank_date = cb_cashbank_balance.cash_bank_date AND
  369.         B.curr_code = cb_cashbank_balance.curr_code AND
  370.         cb_cashbank_balance.rec_type = vCreditType;
  371.  
  372.  
  373.   -- untuk transaksi non cash, isi data ke cb_balance_settlement
  374.  
  375. INSERT INTO cb_balance_settlement
  376.     (tenant_id, ou_id, doc_type_id, doc_no, doc_date,
  377.     device_merchant_id, card_type, bank_card_code, card_no,
  378.     curr_payment_code, amount_payment, flg_settle, cashbank_id, cash_bank_date,
  379.     ref_doc_type_id, ref_id,
  380.     VERSION, create_datetime, create_user_id, update_datetime, update_user_id)
  381.     SELECT A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, pDateVoid,
  382.         B.device_merchant_id, B.card_type, B.bank_card_code, B.card_no,
  383.         B.curr_payment_code, SUM(B.payment_amount), vFlagNo, vEmptyId, vEmptyValue,
  384.         vEmptyId, vEmptyId,
  385.         0, pDatetime, pUserId, pDatetime, pUserId
  386.     FROM i_trx_pos A, i_trx_pos_non_cash_payment B
  387.     WHERE A.tenant_id = pTenantId AND
  388.         A.ou_id = pOuId AND
  389.         A.process_no = pProcessNo AND
  390.         A.status = vStatusPos AND
  391.         A.trx_pos_id = B.trx_pos_id AND
  392.         A.process_no = B.process_no AND
  393.         A.trx_pos_id = pPosId AND
  394.         A.tenant_id = B.tenant_id
  395.     GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no,
  396.         B.device_merchant_id, B.card_type, B.bank_card_code, B.card_no,
  397.         B.curr_payment_code;
  398.  
  399.     /*
  400.      * journal Void DO POS di group by product
  401.      * Credit Inventory
  402.      * Debit COGS
  403.      */
  404.     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(pDateVoid), 'MONTHLY', pDatetime, pUserId)
  405.     FROM i_trx_pos A
  406.     WHERE A.tenant_id = pTenantId AND
  407.         A.ou_id = pOuId AND
  408.         A.process_no = pProcessNo AND
  409.         A.status = vStatusPos AND A.trx_pos_id = pPosId;
  410.  
  411.     INSERT INTO gl_journal_trx
  412.     (tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
  413.     ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
  414.     ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
  415.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  416.     SELECT A.tenant_id, (vDocJournalDoPos).journal_type, vDocTypeDoPos, A.trx_pos_id, A.doc_no, pDateVoid,
  417.         (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,
  418.         A.ref_doc_type_id, A.ref_id, A.doc_date, f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), '(VOID POS) - ' || A.remark, vStatusDraft, 'DRAFT',
  419.         0, pDatetime, pUserId, pDatetime, pUserId
  420.     FROM i_trx_pos A
  421.     WHERE A.tenant_id = pTenantId AND
  422.         A.ou_id = pOuId AND
  423.         A.process_no = pProcessNo AND
  424.         A.status = vStatusPos AND
  425.         A.trx_pos_id = pPosId AND
  426.         EXISTS( -- added by Didit, 25 Juni 2018. Transaksi POS yang hanya produk nempil saja / service saja tidak perlu dibuatkan jurnal stock
  427.             SELECT 1 FROM i_trx_pos_item B
  428.             WHERE A.trx_pos_id = B.trx_pos_id AND
  429.                 A.process_no = B.process_no AND
  430.                 A.tenant_id = B.tenant_id
  431.         );
  432.  
  433.     INSERT INTO tt_journal_trx_item
  434.     (session_id, tenant_id, journal_trx_id, line_no,
  435.     ref_doc_type_id, ref_id,
  436.     partner_id, product_id, cashbank_id, ou_rc_id,
  437.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  438.     coa_id, curr_code, qty, uom_id,
  439.     amount, journal_date, type_rate,
  440.     numerator_rate, denominator_rate, journal_desc, remark)
  441.     SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
  442.         A.doc_type_id, vEmptyId,
  443.         A.partner_id, B.product_id, vEmptyId, vEmptyId,
  444.         vEmptyId, vSignDebit, vProductCOA, vEmptyId,
  445.         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,
  446.         0 , pDateVoid, vTypeRate,
  447.         1, 1, 'PRODUCT_STOCK', vEmptyValue
  448.     FROM i_trx_pos A, i_trx_pos_item B, gl_journal_trx C
  449.     WHERE A.tenant_id = pTenantId AND
  450.         A.ou_id = pOuId AND
  451.         A.process_no = pProcessNo AND
  452.         A.status = vStatusPos AND
  453.         A.trx_pos_id = B.trx_pos_id AND
  454.         A.process_no = B.process_no AND
  455.         A.tenant_id = B.tenant_id AND
  456.         A.trx_pos_id = C.doc_id AND
  457.         A.tenant_id = C.tenant_id AND
  458.         A.trx_pos_id = pPosId AND
  459.         C.doc_type_id = vDocTypeDoPos AND
  460.         C.journal_type = (vDocJournalDoPos).journal_type AND
  461.         C.ou_bu_id = (vOuStructure).ou_bu_id AND
  462.         C.ou_branch_id = (vOuStructure).ou_branch_id AND
  463.         C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
  464. --      A.ou_id = C.sub_ou_id AND
  465.         A.doc_no = C.doc_no
  466.     GROUP BY A.tenant_id, C.journal_trx_id, A.doc_type_id,
  467.         A.partner_id, B.product_id, B.base_uom_id;
  468.  
  469.     INSERT INTO gl_journal_trx_item
  470.     (tenant_id, journal_trx_id, line_no,
  471.     ref_doc_type_id, ref_id,
  472.     partner_id, product_id, cashbank_id, ou_rc_id,
  473.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  474.     coa_id, curr_code, qty, uom_id,
  475.     amount, journal_date, type_rate,
  476.     numerator_rate, denominator_rate, journal_desc, remark,
  477.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  478.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  479.         A.ref_doc_type_id, A.ref_id,
  480.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  481.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  482.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  483.         A.amount, A.journal_date, A.type_rate,
  484.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  485.         0, pDatetime, pUserId, pDatetime, pUserId
  486.     FROM tt_journal_trx_item A
  487.     WHERE A.session_id = pSessionId;
  488.  
  489.     INSERT INTO gl_journal_trx_mapping
  490.     (tenant_id, journal_trx_id, line_no,
  491.     ref_doc_type_id, ref_id,
  492.     partner_id, product_id, cashbank_id, ou_rc_id,
  493.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  494.     coa_id, curr_code, qty, uom_id,
  495.     amount, journal_date, type_rate,
  496.     numerator_rate, denominator_rate, journal_desc, remark, -- DanieL : penambahan journal_desc
  497.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  498.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  499.         vEmptyId, vEmptyId,
  500.         vEmptyId, vEmptyId, vEmptyId, vEmptyId,
  501.         vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
  502.         f_get_system_coa_by_group_coa(A.tenant_id, 'HargaPokokPenjualan'), A.curr_code, 0, vEmptyId,
  503.         0, A.journal_date, A.type_rate,
  504.         1, 1, 'COGS', vEmptyValue,
  505.         0, pDatetime, pUserId, pDatetime, pUserId
  506.     FROM tt_journal_trx_item A
  507.     WHERE A.session_id = pSessionId
  508.     GROUP BY A.tenant_id, A.journal_trx_id, A.journal_date, A.type_rate, A.curr_code;
  509.  
  510.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
  511.  
  512.     /*
  513.      * journal Void POS
  514.      * Debit Sales           nett price * qty, without tax
  515.      * Debit BankCharges     add amount
  516.      * Debit Tax Amount      tax amount ( sudah nett price )
  517.      * Debit Rounding         rouding
  518.      * Debit Refund Cash        refund amount
  519.      * Credit Cash            cash payment
  520.      * Credit AccSettlement    non cash payment
  521.      */
  522.     PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, vJournalType, vLedgerCode, f_get_year_month_date(pDateVoid), 'DAILY', pDatetime, pUserId)
  523.     FROM i_trx_pos A
  524.     WHERE A.tenant_id = pTenantId AND
  525.         A.ou_id = pOuId AND
  526.         A.process_no = pProcessNo AND
  527.         A.status = vStatusPos AND A.trx_pos_id = pPosId;
  528.  
  529.     INSERT INTO gl_journal_trx
  530.     (tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
  531.     ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,
  532.     ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
  533.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  534.     SELECT A.tenant_id, vJournalType, A.doc_type_id, A.trx_pos_id, A.doc_no, pDateVoid,
  535.         (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,
  536.         A.ref_doc_type_id, A.ref_id, A.doc_date, A.curr_code, '(VOID POS) - ' || A.remark, vStatusDraft, 'DRAFT',
  537.         0, pDatetime, pUserId, pDatetime, pUserId
  538.     FROM i_trx_pos A
  539.     WHERE A.tenant_id = pTenantId AND
  540.         A.ou_id = pOuId AND
  541.         A.process_no = pProcessNo AND
  542.         A.status = vStatusPos;
  543.  
  544.     INSERT INTO tt_journal_trx_item
  545.     (session_id, tenant_id, journal_trx_id, line_no,
  546.     ref_doc_type_id, ref_id,
  547.     partner_id, product_id, cashbank_id, ou_rc_id,
  548.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  549.     coa_id, curr_code, qty, uom_id,
  550.     amount, journal_date, type_rate,
  551.     numerator_rate, denominator_rate, journal_desc, remark)
  552.     SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
  553.         A.doc_type_id, vEmptyId,
  554.         A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  555.         vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
  556.         f_get_system_coa_by_group_coa(A.tenant_id, 'PendapatanPenjualan'), B.curr_code, SUM(B.qty), B.base_uom_id,
  557.         --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,
  558.         CASE WHEN vFlgPkp = vFlagYes THEN
  559.         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
  560.         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')
  561.         --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)
  562.         END
  563.         , pDateVoid, vTypeRate,
  564.         1, 1, 'SALES_POS', vEmptyValue
  565.     FROM i_trx_pos A, i_trx_pos_item B, gl_journal_trx C
  566.     WHERE A.tenant_id = pTenantId AND
  567.         A.ou_id = pOuId AND
  568.         A.process_no = pProcessNo AND
  569.         A.status = vStatusPos AND
  570.         A.trx_pos_id = B.trx_pos_id AND
  571.         A.process_no = B.process_no AND
  572.         A.tenant_id = B.tenant_id AND
  573.         A.trx_pos_id = C.doc_id AND
  574.         A.tenant_id = C.tenant_id AND
  575.         A.doc_type_id = C.doc_type_id AND
  576.         C.journal_type = vJournalType AND
  577.         C.ou_bu_id = (vOuStructure).ou_bu_id AND
  578.         C.ou_branch_id = (vOuStructure).ou_branch_id AND
  579.         C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
  580.         A.trx_pos_id = pPosId AND
  581. --      A.ou_id = C.sub_ou_id AND
  582.         A.doc_no = C.doc_no
  583.     GROUP BY A.tenant_id, C.journal_trx_id, A.doc_type_id,
  584.         A.partner_id, B.curr_code, B.base_uom_id, B.flg_tax_amount, B.tax_percentage;
  585.  
  586.  
  587.     IF vFlgPkp = vFlagYes THEN
  588.     -- hitung total tax item
  589.     SELECT SUM(B.tax_amount) INTO vTotalTaxItem
  590.     FROM i_trx_pos A, i_trx_pos_item B
  591.     WHERE A.tenant_id = pTenantId AND
  592.         A.ou_id = pOuId AND
  593.         A.process_no = pProcessNo AND
  594.         A.status = vStatusPos AND
  595.         A.trx_pos_id = B.trx_pos_id AND
  596.         A.process_no = B.process_no AND
  597.         A.tenant_id = B.tenant_id AND A.trx_pos_id = pPosId;
  598.  
  599.     IF vTotalTaxItem > 0 THEN
  600.  
  601.     SELECT SUM(B.tax_amount)-vTotalTaxItem INTO vTotalTaxItemNempil
  602.     FROM i_trx_pos A, i_trx_pos_tax B
  603.     WHERE A.tenant_id = pTenantId AND
  604.         A.ou_id = pOuId AND
  605.         A.process_no = pProcessNo AND
  606.         A.status = vStatusPos AND
  607.         A.trx_pos_id = B.trx_pos_id AND
  608.         A.process_no = B.process_no AND
  609.         A.tenant_id = B.tenant_id AND A.trx_pos_id = pPosId;
  610.  
  611.     END IF;
  612.     END IF;
  613.  
  614.  
  615. -- jurnal penjualan dari produk nempil, 6 Nov 2015
  616.     WITH data_tax_pos AS(
  617.         SELECT A.trx_pos_id, A.process_no, A.tenant_id,
  618.             A.doc_type_id, SUM(B.tax_amount) AS tax_amount
  619.         FROM i_trx_pos A, i_trx_pos_item B
  620.         WHERE A.tenant_id = pTenantId AND
  621.             A.ou_id = pOuId AND
  622.             A.process_no = pProcessNo AND
  623.             A.status = vStatusPos AND
  624.             A.trx_pos_id = B.trx_pos_id AND
  625.             A.process_no = B.process_no AND
  626.             A.tenant_id = B.tenant_id  AND A.trx_pos_id = pPosId
  627.         GROUP BY A.trx_pos_id, A.tenant_id, A.process_no, A.doc_type_id
  628.     ),
  629.     data_tax_total AS(
  630.         SELECT A.trx_pos_id, A.process_no, A.tenant_id,
  631.             A.doc_type_id, SUM(B.tax_amount) AS tax_amount
  632.         FROM i_trx_pos A, i_trx_pos_tax B
  633.         WHERE A.tenant_id = pTenantId AND
  634.             A.ou_id = pOuId AND
  635.             A.process_no = pProcessNo AND
  636.             A.status = vStatusPos AND
  637.             A.trx_pos_id = B.trx_pos_id AND
  638.             A.process_no = B.process_no AND
  639.             A.tenant_id = B.tenant_id  AND A.trx_pos_id = pPosId
  640.         GROUP BY A.trx_pos_id, A.tenant_id, A.process_no, A.doc_type_id
  641.     ),
  642.     data_tax_nempil AS(
  643.         SELECT A.trx_pos_id, A.process_no, A.tenant_id,
  644.             A.doc_type_id, A.tax_amount - COALESCE(B.tax_amount,0) AS tax_amount
  645.         FROM data_tax_total A
  646.         LEFT JOIN data_tax_pos B ON A.trx_pos_id = B.trx_pos_id AND
  647.                                        A.tenant_id = B.tenant_id AND
  648.                                        A.process_no = B.process_no AND
  649.                                        A.doc_type_id = B.doc_type_id  AND A.trx_pos_id = pPosId
  650.     )
  651.     INSERT INTO tt_journal_trx_item
  652.     (session_id, tenant_id, journal_trx_id, line_no,
  653.     ref_doc_type_id, ref_id,
  654.     partner_id, product_id, cashbank_id, ou_rc_id,
  655.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  656.     coa_id, curr_code, qty, uom_id,
  657.     amount, journal_date, type_rate,
  658.     numerator_rate, denominator_rate, journal_desc, remark)
  659.     SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
  660.         A.doc_type_id, vEmptyId,
  661.         A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  662.         vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
  663.         f_get_system_coa_by_group_coa(A.tenant_id, 'PendapatanPenjualan'), B.curr_code, SUM(B.qty), vEmptyId,
  664.         CASE WHEN vFlgPkp = vFlagYes THEN
  665.         SUM(B.sell_price) - COALESCE(D.tax_amount, 0) ELSE -- sell price = gross sell price * qty
  666.         SUM(B.sell_price)
  667.         END,
  668.         pDateVoid, vTypeRate,
  669.         1, 1, 'SALES_POS', vEmptyValue
  670.     FROM i_trx_pos A
  671.     INNER JOIN i_trx_pos_item_nempil_barang B ON A.trx_pos_id = B.trx_pos_id AND
  672.                                                  A.process_no = B.process_no AND
  673.                                                  A.tenant_id = B.tenant_id
  674.     INNER JOIN gl_journal_trx C ON A.trx_pos_id = C.doc_id AND
  675.                                    A.tenant_id = C.tenant_id AND
  676.                                    A.doc_type_id = C.doc_type_id AND
  677.                                    A.doc_no = C.doc_no
  678.     LEFT JOIN data_tax_nempil D ON A.trx_pos_id = D.trx_pos_id AND
  679.                                    A.tenant_id = D.tenant_id AND
  680.                                    A.doc_type_id = D.doc_type_id AND A.process_no = D.process_no
  681.     WHERE A.tenant_id = pTenantId AND
  682.         A.ou_id = pOuId AND
  683.         A.process_no = pProcessNo AND
  684.         A.status = vStatusPos AND
  685.         C.journal_type = vJournalType AND
  686.         C.ou_bu_id = (vOuStructure).ou_bu_id AND
  687.         C.ou_branch_id = (vOuStructure).ou_branch_id AND
  688.         C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id
  689. --      A.ou_id = C.sub_ou_id AND
  690.     GROUP BY A.tenant_id, C.journal_trx_id, A.doc_type_id,
  691.         A.partner_id, B.curr_code, D.tax_amount;
  692.  
  693.     INSERT INTO tt_journal_trx_item
  694.     (session_id, tenant_id, journal_trx_id, line_no,
  695.     ref_doc_type_id, ref_id,
  696.     partner_id, product_id, cashbank_id, ou_rc_id,
  697.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  698.     coa_id, curr_code, qty, uom_id,
  699.     amount, journal_date, type_rate,
  700.     numerator_rate, denominator_rate, journal_desc, remark)
  701.     SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
  702.         A.doc_type_id, B.trx_pos_add_cost_id,
  703.         A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  704.         vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
  705.         f_get_system_coa_by_group_coa(A.tenant_id, 'BiayaKartuKredit'), B.curr_code, 0, vEmptyId,
  706.         B.add_amount, pDateVoid, vTypeRate,
  707.         1, 1, 'CHARGES_POS', B.remark
  708.     FROM i_trx_pos A, i_trx_pos_add_cost B, gl_journal_trx C
  709.     WHERE A.tenant_id = pTenantId AND
  710.         A.ou_id = pOuId AND
  711.         A.process_no = pProcessNo AND
  712.         A.status = vStatusPos AND
  713.         A.trx_pos_id = B.trx_pos_id AND
  714.         A.process_no = B.process_no AND
  715.         A.tenant_id = B.tenant_id AND
  716.         A.trx_pos_id = C.doc_id AND
  717.         A.tenant_id = C.tenant_id AND
  718.         A.doc_type_id = C.doc_type_id AND
  719.         C.journal_type = vJournalType AND
  720.         C.ou_bu_id = (vOuStructure).ou_bu_id AND
  721.         C.ou_branch_id = (vOuStructure).ou_branch_id AND
  722.         C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
  723. --      A.ou_id = C.sub_ou_id AND
  724.         A.doc_no = C.doc_no AND
  725.         B.activity_gl_id = vActivityChargesId AND A.trx_pos_id = pPosId;
  726.  
  727.         -- add if By: Ping An , 27 Nov 2015
  728.     -- Jurnal VAT Hanya untuk yang PKP saja , kalo non PKP gak perlu dijurnal PPN karena non PKP tidak memungut dan melaporkan PPN
  729.     IF vFlgPkp = vFlagYes THEN
  730.  
  731.         INSERT INTO tt_journal_trx_item
  732.         (session_id, tenant_id, journal_trx_id, line_no,
  733.         ref_doc_type_id, ref_id,
  734.         partner_id, product_id, cashbank_id, ou_rc_id,
  735.         segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  736.         coa_id, curr_code, qty, uom_id,
  737.         amount, journal_date, type_rate,
  738.         numerator_rate, denominator_rate, journal_desc, remark)
  739.         SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
  740.             A.doc_type_id, vEmptyId,
  741.             A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  742.             vEmptyId, vSignCredit, vTaxCOA, vEmptyId,
  743.             D.create_coa_id, D.tax_curr_code, 0, vEmptyId,
  744.             B.tax_amount , pDateVoid, vTypeRate,
  745.             1, 1, 'VAT_OUT_POS', vEmptyValue
  746.         FROM i_trx_pos A, i_trx_pos_tax B, gl_journal_trx C, m_tax D
  747.         WHERE A.tenant_id = pTenantId AND
  748.             A.ou_id = pOuId AND
  749.             A.process_no = pProcessNo AND
  750.             A.status = vStatusPos AND
  751.             A.trx_pos_id = B.trx_pos_id AND
  752.             A.process_no = B.process_no AND
  753.             A.tenant_id = B.tenant_id AND
  754.             B.tax_id = D.tax_id AND
  755.             A.trx_pos_id = C.doc_id AND
  756.             A.tenant_id = C.tenant_id AND
  757.             A.doc_type_id = C.doc_type_id AND
  758.             C.journal_type = vJournalType AND
  759.             C.ou_bu_id = (vOuStructure).ou_bu_id AND
  760.             C.ou_branch_id = (vOuStructure).ou_branch_id AND
  761.             C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
  762.     --      A.ou_id = C.sub_ou_id AND
  763.             A.doc_no = C.doc_no AND A.trx_pos_id = pPosId;
  764.  
  765.  
  766.     END IF;
  767.  
  768.  
  769.     INSERT INTO tt_journal_trx_item
  770.     (session_id, tenant_id, journal_trx_id, line_no,
  771.     ref_doc_type_id, ref_id,
  772.     partner_id, product_id, cashbank_id, ou_rc_id,
  773.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  774.     coa_id, curr_code, qty, uom_id,
  775.     amount, journal_date, type_rate,
  776.     numerator_rate, denominator_rate, journal_desc, remark)
  777.     SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
  778.         A.doc_type_id, vEmptyId,
  779.         A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  780.         vEmptyId, vSignDebit, vSystemCOA, vEmptyId,
  781.         f_get_system_coa_by_group_coa(A.tenant_id, 'BiayaPembulatanNilai'), A.curr_code, 0, vEmptyId,
  782.         A.rounding_amount, pDateVoid, vTypeRate,
  783.         1, 1, 'ROUNDING_POS', C.remark
  784.     FROM i_trx_pos A, gl_journal_trx C
  785.     WHERE A.tenant_id = pTenantId AND
  786.         A.ou_id = pOuId AND
  787.         A.process_no = pProcessNo AND
  788.         A.status = vStatusPos AND
  789.         A.trx_pos_id = C.doc_id AND
  790.         A.tenant_id = C.tenant_id AND
  791.         A.doc_type_id = C.doc_type_id AND
  792.         C.journal_type = vJournalType AND
  793.         C.ou_bu_id = (vOuStructure).ou_bu_id AND
  794.         C.ou_branch_id = (vOuStructure).ou_branch_id AND
  795.         C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
  796. --      A.ou_id = C.sub_ou_id AND
  797.         A.doc_no = C.doc_no AND A.trx_pos_id = pPosId;
  798.  
  799.     INSERT INTO tt_journal_trx_item
  800.     (session_id, tenant_id, journal_trx_id, line_no,
  801.     ref_doc_type_id, ref_id,
  802.     partner_id, product_id, cashbank_id, ou_rc_id,
  803.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  804.     coa_id, curr_code, qty, uom_id,
  805.     amount, journal_date, type_rate,
  806.     numerator_rate, denominator_rate, journal_desc, remark)
  807.     SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
  808.         A.doc_type_id, vEmptyId,
  809.         A.partner_id, vEmptyId, E.cashbank_id, vEmptyId,
  810.         vEmptyId, vSignDebit, vCashBankCOA, vEmptyId,
  811.         E.coa_id, A.curr_code, 0, vEmptyId,
  812.         A.total_refund, pDateVoid, vTypeRate,
  813.         1, 1, 'REFUND_POS', A.remark
  814.     FROM i_trx_pos A, gl_journal_trx C, i_cash_bank_data_outlet D, m_cashbank E
  815.     WHERE A.tenant_id = pTenantId AND
  816.         A.ou_id = pOuId AND
  817.         A.process_no = pProcessNo AND
  818.         A.status = vStatusPos AND
  819.         A.trx_pos_id = C.doc_id AND
  820.         A.tenant_id = C.tenant_id AND
  821.         A.doc_type_id = C.doc_type_id AND
  822.         D.outlet_id = pOutletId AND
  823.         D.cashbank_id = E.cashbank_id AND
  824.         A.curr_code = E.curr_code AND
  825.         E.flg_cash_bank = vFlagCash AND
  826.         C.journal_type = vJournalType AND
  827.         C.ou_bu_id = (vOuStructure).ou_bu_id AND
  828.         C.ou_branch_id = (vOuStructure).ou_branch_id AND
  829.         C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
  830. --      A.ou_id = C.sub_ou_id AND
  831.         A.doc_no = C.doc_no AND
  832.         A.total_refund > 0 AND A.trx_pos_id = pPosId;
  833.  
  834.     INSERT INTO tt_journal_trx_item
  835.     (session_id, tenant_id, journal_trx_id, line_no,
  836.     ref_doc_type_id, ref_id,
  837.     partner_id, product_id, cashbank_id, ou_rc_id,
  838.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  839.     coa_id, curr_code, qty, uom_id,
  840.     amount, journal_date, type_rate,
  841.     numerator_rate, denominator_rate, journal_desc, remark)
  842.     SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
  843.         A.doc_type_id, B.trx_pos_cash_payment_id,
  844.         A.partner_id, vEmptyId, E.cashbank_id, vEmptyId,
  845.         vEmptyId, vSignCredit, vCashBankCOA, vEmptyId,
  846.         E.coa_id, B.curr_payment_code, 0, vEmptyId,
  847.         B.payment_amount, pDateVoid, vTypeRate,
  848.         1, 1, 'CASH_PAYMENT_POS', B.remark
  849.     FROM i_trx_pos A, i_trx_pos_cash_payment B, gl_journal_trx C, i_cash_bank_data_outlet D, m_cashbank E
  850.     WHERE A.tenant_id = pTenantId AND
  851.         A.ou_id = pOuId AND
  852.         A.process_no = pProcessNo AND
  853.         A.status = vStatusPos AND
  854.         A.process_no = B.process_no AND
  855.         A.trx_pos_id = B.trx_pos_id AND
  856.         A.tenant_id = B.tenant_id AND
  857.         D.outlet_id = pOutletId AND
  858.         D.cashbank_id = E.cashbank_id AND
  859.         B.curr_payment_code = E.curr_code AND
  860.         E.flg_cash_bank = vFlagCash AND
  861.         A.trx_pos_id = C.doc_id AND
  862.         A.tenant_id = C.tenant_id AND
  863.         A.doc_type_id = C.doc_type_id AND
  864.         C.journal_type = vJournalType AND
  865.         C.ou_bu_id = (vOuStructure).ou_bu_id AND
  866.         C.ou_branch_id = (vOuStructure).ou_branch_id AND
  867.         C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
  868. --      A.ou_id = C.sub_ou_id AND
  869.         A.doc_no = C.doc_no AND
  870.         B.payment_amount > 0 AND A.trx_pos_id = pPosId; -- add by: Ping An 30 Nov 2015, ditambah filter kalau tidak ada pembayaran tidak perlu dijurnal
  871.  
  872.     INSERT INTO tt_journal_trx_item
  873.     (session_id, tenant_id, journal_trx_id, line_no,
  874.     ref_doc_type_id, ref_id,
  875.     partner_id, product_id, cashbank_id, ou_rc_id,
  876.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  877.     coa_id, curr_code, qty, uom_id,
  878.     amount, journal_date, type_rate,
  879.     numerator_rate, denominator_rate, journal_desc, remark)
  880.     SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
  881.         A.doc_type_id, B.trx_pos_non_cash_payment_id,
  882.         A.partner_id, vEmptyId, E.cashbank_id, vEmptyId,
  883.         vEmptyId, vSignCredit, vCashBankCOA, vEmptyId,
  884.         E.settle_coa_id, B.curr_payment_code, 0, vEmptyId,
  885.         B.payment_amount, pDateVoid, vTypeRate,
  886.         1, 1, 'NON_CASH_PAYMENT_POS', B.remark
  887.     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
  888.     WHERE A.tenant_id = pTenantId AND
  889.         A.ou_id = pOuId AND
  890.         A.process_no = pProcessNo AND
  891.         A.status = vStatusPos AND
  892.         A.process_no = B.process_no AND
  893.         A.trx_pos_id = B.trx_pos_id AND
  894.         A.tenant_id = B.tenant_id AND
  895.         D.outlet_id = pOutletId AND
  896.         B.device_merchant_id = D.device_merchant_id AND
  897.         D.device_merchant_id = E.device_merchant_id AND
  898.         A.trx_pos_id = C.doc_id AND
  899.         A.tenant_id = C.tenant_id AND
  900.         A.doc_type_id = C.doc_type_id AND
  901.         C.journal_type = vJournalType AND
  902.         C.ou_bu_id = (vOuStructure).ou_bu_id AND
  903.         C.ou_branch_id = (vOuStructure).ou_branch_id AND
  904.         C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
  905. --      A.ou_id = C.sub_ou_id AND
  906.         A.doc_no = C.doc_no AND
  907.         B.payment_amount > 0 AND A.trx_pos_id = pPosId; -- add by: Ping An 30 Nov 2015, ditambah filter kalau tidak ada pembayaran tidak perlu dijurnal
  908.  
  909.  
  910.  
  911.     -- hanya dijalankan jika ada kurang bayar(BELUM LUNAS PEMBAYARANNYA ), kalau sudah lunas tidak perlu dijurnal DP
  912.     IF vCountBelumDiBayar > 0 THEN
  913.         -- sisa pembayaran dipindahkan ke AR
  914.             INSERT INTO tt_journal_trx_item
  915.             (session_id, tenant_id, journal_trx_id, line_no,
  916.             ref_doc_type_id, ref_id,
  917.             partner_id, product_id, cashbank_id, ou_rc_id,
  918.             segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  919.             coa_id, curr_code, qty, uom_id,
  920.             amount, journal_date, type_rate,
  921.             numerator_rate, denominator_rate, journal_desc, remark)
  922.             SELECT pSessionId, A.tenant_id, C.journal_trx_id, 1,
  923.                 A.doc_type_id, A.ref_id,
  924.                 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  925.                 vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
  926.                 f_get_ar_coa_partner(A.tenant_id, D.partner_id), A.curr_code, 0, vEmptyId,
  927.                 A.total_amount, pDateVoid, vTypeRate,
  928.                 1, 1, 'AR_POS', 'VOID POS NILAI CREDIT NOTE AR'
  929.             FROM i_trx_pos A, gl_journal_trx C, i_trx_pos_ext D, i_trx_pos_termin_payment E
  930.             WHERE A.tenant_id = pTenantId AND
  931.                 A.ou_id = pOuId AND
  932.                 A.process_no = pProcessNo AND
  933.                 A.status = vStatusPos AND
  934.                 A.trx_pos_id = D.trx_pos_id AND
  935.                 -- rev by: Ping An , 28 Jan 2016 joinnya kurang process_no dan tenant_id
  936.                 A.process_no = D.process_no AND
  937.                 A.tenant_id = D.tenant_id AND
  938.                 A.tenant_id = E.tenant_id AND
  939.                 A.process_no = E.process_no AND
  940.                 A.trx_pos_id = E.trx_pos_id AND
  941.                 E.under_payment_amount > 0 AND
  942.  
  943.                 A.trx_pos_id = C.doc_id AND
  944.                 A.tenant_id = C.tenant_id AND
  945.                 A.doc_type_id = C.doc_type_id AND
  946.                 C.journal_type = vJournalType AND
  947.                 C.ou_bu_id = (vOuStructure).ou_bu_id AND
  948.                 C.ou_branch_id = (vOuStructure).ou_branch_id AND
  949.                 C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
  950.         --      A.ou_id = C.sub_ou_id AND
  951.                 A.doc_no = C.doc_no AND A.trx_pos_id = pPosId;
  952.  
  953.  
  954.      END IF;
  955.  
  956.  
  957.  
  958. -- TAMBAHKAN JURNAL DP add by Ping An 18 Nov 2015
  959. -- karena nantinya di function submit invoice AR, debt note ar yang tercreate dari POS tidak akan di jurnal, jadi perlu dijurnal disini DP ARnya
  960. -- jika ada bayar termin dari customer corporate
  961.         --      K   m_activity_gl                   SYSTEM  DP_AR_POS
  962. INSERT INTO tt_journal_trx_item
  963.             (   session_id, tenant_id, journal_trx_id, line_no,
  964.                 ref_doc_type_id, ref_id,
  965.                 partner_id, product_id, cashbank_id, ou_rc_id,
  966.                 segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  967.                 coa_id, curr_code, qty, uom_id,
  968.                 amount, journal_date, type_rate,
  969.                 numerator_rate, denominator_rate, journal_desc, remark)
  970.         SELECT  pSessionId, A.tenant_id, C.journal_trx_id, 1,
  971.                 A.trx_pos_id, B.trx_pos_termin_payment_id,
  972.                 A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  973.                 vEmptyId, vSignDebit, vSystemCOA, D.activity_gl_id,
  974.                 D.coa_id, B.curr_payment_code, 0, vEmptyId,
  975.                 B.payment_amount, pDateVoid, vTypeRate,
  976.                 1, 1, 'DP_AR_POS', 'VOID POS NILAI DP AR'
  977.         FROM i_trx_pos A, i_trx_pos_termin_payment B, gl_journal_trx C, m_activity_gl D
  978.     WHERE A.tenant_id = pTenantId AND
  979.         A.ou_id = pOuId AND
  980.         A.process_no = pProcessNo AND
  981.         A.status = vStatusPos AND
  982.         A.process_no = B.process_no AND
  983.         A.trx_pos_id = B.trx_pos_id AND
  984.         A.tenant_id = B.tenant_id AND
  985.         A.trx_pos_id = C.doc_id AND
  986.         A.tenant_id = C.tenant_id AND
  987.         A.doc_type_id = C.doc_type_id AND
  988.         C.journal_type = vJournalType AND
  989.         C.ou_bu_id = (vOuStructure).ou_bu_id AND
  990.         C.ou_branch_id = (vOuStructure).ou_branch_id AND
  991.         C.ou_sub_bu_id = (vOuStructure).ou_sub_bu_id AND
  992. --      A.ou_id = C.sub_ou_id AND
  993.         A.doc_no = C.doc_no AND
  994.         B.under_payment_amount > 0 AND -- ditambah filter: jika LUNAS tidak perlu di jurnal DP
  995.         B.payment_amount > 0 AND -- ditambah filter : jika tidak ada pembayaran juga tidak perlu di jurnal DP
  996.         D.activity_gl_id = f_get_activity_gl_id_for_downpayment(A.tenant_id) AND A.trx_pos_id = pPosId;
  997.  
  998. -- END PENAMBAHKAN JURNAL DP add by Ping An 18 Nov 2015        
  999.  
  1000.     INSERT INTO gl_journal_trx_item
  1001.     (tenant_id, journal_trx_id, line_no,
  1002.     ref_doc_type_id, ref_id,
  1003.     partner_id, product_id, cashbank_id, ou_rc_id,
  1004.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  1005.     coa_id, curr_code, qty, uom_id,
  1006.     amount, journal_date, type_rate,
  1007.     numerator_rate, denominator_rate, journal_desc, remark,
  1008.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  1009.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  1010.         A.ref_doc_type_id, A.ref_id,
  1011.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  1012.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  1013.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  1014.         A.amount, A.journal_date, A.type_rate,
  1015.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  1016.         0, pDatetime, pUserId, pDatetime, pUserId
  1017.     FROM tt_journal_trx_item A
  1018.     WHERE A.session_id = pSessionId AND
  1019.         A.journal_desc IN ('SALES_POS','CHARGES_POS','VAT_OUT_POS', 'ROUNDING_POS', 'REFUND_POS', 'DP_AR_POS');
  1020.  
  1021.     INSERT INTO gl_journal_trx_mapping
  1022.     (tenant_id, journal_trx_id, line_no,
  1023.     ref_doc_type_id, ref_id,
  1024.     partner_id, product_id, cashbank_id, ou_rc_id,
  1025.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  1026.     coa_id, curr_code, qty, uom_id,
  1027.     amount, journal_date, type_rate,
  1028.     numerator_rate, denominator_rate, journal_desc, remark,
  1029.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  1030.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id),
  1031.         A.ref_doc_type_id, A.ref_id,
  1032.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  1033.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  1034.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  1035.         A.amount, A.journal_date, A.type_rate,
  1036.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  1037.         0, pDatetime, pUserId, pDatetime, pUserId
  1038.     FROM tt_journal_trx_item A
  1039.     WHERE A.session_id = pSessionId AND
  1040.          -- rev by Ping An 18nov2015 A.journal_desc IN ('CASH_PAYMENT_POS','NON_CASH_PAYMENT_POS');
  1041.          A.journal_desc IN ('CASH_PAYMENT_POS','NON_CASH_PAYMENT_POS','AR_POS');
  1042.  
  1043.  
  1044.     -- buat nota debit ar untuk transaksi pos yg masih hutang
  1045.     -- added by didit, 25 September 2015
  1046.     -- TODO : buat function migrate credit note ar
  1047.     PERFORM f_migrate_data_trx_pos_to_credit_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)
  1048.     FROM i_trx_pos A
  1049.     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
  1050.     WHERE A.tenant_id = pTenantId
  1051.         AND A.process_no = pProcessNo
  1052.         AND A.trx_pos_id = pPosId
  1053.         AND A.status = vStatusPos
  1054.         AND A.ou_id = pOuId;
  1055.  
  1056.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId;
  1057.  
  1058.     DELETE FROM i_tt_pos_cash_balance WHERE session_id = pSessionId;
  1059.  
  1060.     DELETE FROM i_tt_pos_product_balance_stock WHERE session_id = pSessionId;
  1061.  
  1062.     DELETE FROM i_tt_nempil_barang_balance_stock WHERE session_id = pSessionId;
  1063.  
  1064. END;
  1065. $BODY$
  1066.   LANGUAGE plpgsql VOLATILE
  1067.   COST 100;
  1068.   /
RAW Paste Data