abirama62

i_ho_process_pos_shop

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