abirama62

func pos shop

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