widana

pu_submit_retur_proforma

Apr 6th, 2017
114
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION pu_submit_retur_proforma(bigint, character varying, character varying)
  2.   RETURNS void AS
  3. $BODY$
  4. DECLARE
  5.     pTenantId           ALIAS FOR $1;
  6.     pSessionId          ALIAS FOR $2;
  7.     pProcessNo          ALIAS FOR $3;
  8.  
  9.     vProcessId              bigint;
  10.     vRpId                   bigint;
  11.     vUserId                 bigint;
  12.     vReturProformaDocTypeId bigint := 117;
  13.     vEmptyId                bigint := -99;
  14.     vOutletId               bigint;
  15.     vOuWarehouseId          bigint;
  16.     vOuId                   bigint;
  17.     vOuWarehouseIdForJournal    bigint;
  18.     vJournalTrxId           bigint;
  19.    
  20.     vDatetime               character varying(14);
  21.     vYes                    character varying := 'Y';
  22.     vNo                     character varying := 'N';
  23.     vStatusRelease          character varying := 'R';
  24.     vRoundingModeNonTax     character varying(5);
  25.     vProductStatus          character varying := 'GOOD';
  26.     vProductCOA             character varying := 'PRODUCT';
  27.     vSignCredit             character varying := 'C';
  28.     vTypeRate               character varying := 'COM';
  29.     vActivityCOA            character varying := 'ACTIVITY';
  30.     vSystemCOA              character varying := 'SYSTEM';
  31.     vEmptyValue             character varying := '';
  32.     vDbVersion              character varying(10);
  33.  
  34.     vDocJournal                 DOC_JOURNAL%ROWTYPE;
  35.     vOuStructure                OU_BU_STRUCTURE%ROWTYPE;
  36.     vOuStructureJournalItem     OU_BU_STRUCTURE%ROWTYPE;
  37.     result                      RECORD;
  38.    
  39. BEGIN
  40.  
  41.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId; 
  42.  
  43.     vDbVersion = '1.0';
  44.    
  45.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
  46.    
  47.     SELECT A.process_message_id INTO vProcessId
  48.     FROM t_process_message A
  49.     WHERE A.tenant_id = pTenantId AND
  50.         A.process_name = 'pu_submit_retur_proforma' AND
  51.         A.process_no = pProcessNo;
  52.        
  53.     SELECT CAST(A.process_parameter_value AS bigint) INTO vRpId
  54.     FROM t_process_parameter A
  55.     WHERE A.process_message_id = vProcessId AND
  56.         A.process_parameter_key = 'returProformaId';
  57.    
  58.     SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
  59.     FROM t_process_parameter A
  60.     WHERE A.process_message_id = vProcessId AND
  61.         A.process_parameter_key = 'userId';
  62.  
  63.     SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
  64.     FROM t_process_parameter A
  65.     WHERE A.process_message_id = vProcessId AND
  66.         A.process_parameter_key = 'datetime';
  67.  
  68.     UPDATE pu_retur_proforma
  69.         SET status_doc = vStatusRelease, version = version+1, update_datetime = vDatetime, update_user_id = vUserId
  70.     WHERE retur_proforma_id = vRpId;
  71.        
  72.     -- Serial Number referensi data dari pu_receive_goods_item 
  73.     INSERT INTO pu_retur_proforma_product
  74.             (tenant_id, retur_proforma_item_id, line_no, product_id,
  75.             serial_number, product_expired_date, product_year_made,
  76.             lot_number, qty_rcv_po, po_uom_id, qty_rcv_int, base_uom_id, remark,
  77.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  78.     SELECT  A.tenant_id, A.retur_proforma_item_id, D.line_no, A.product_id,
  79.         D.serial_number, D.product_expired_date, D.product_year_made,
  80.         D.lot_number, D.qty_rcv_po, D.po_uom_id,D.qty_rcv_int, D.base_uom_id,D.remark,
  81.         0, vDatetime, vUserId, vDatetime, vUserId
  82.     FROM pu_retur_proforma_item A,pu_retur_proforma B, pu_receive_goods_item C, pu_receive_goods_product D
  83.     WHERE A.retur_proforma_id = B.retur_proforma_id
  84.     AND A.ref_id = C.receive_goods_item_id
  85.     AND C.receive_goods_item_id = D.receive_goods_item_id
  86.     AND A.retur_proforma_id = vRpId;
  87.  
  88.     INSERT INTO pu_log_po_balance_item
  89.             (tenant_id, po_id, po_item_id, ref_doc_type_id, ref_id, ref_item_id,
  90.              qty_trx, trx_uom_id, qty_int, base_uom_id, remark,
  91.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  92.     SELECT A.tenant_id, D.po_id, D.po_item_id, A.doc_type_id, A.retur_proforma_id, B.retur_proforma_item_id,
  93.             B.qty_rcv_po * 1, B.po_uom_id, B.qty_rcv_int * 1, B.base_uom_id, B.remark,
  94.             0, vDatetime, vUserId, vDatetime, vUserId
  95.     FROM pu_retur_proforma A, pu_retur_proforma_item B, pu_receive_goods_item C, pu_po_item D
  96.     WHERE A.retur_proforma_id = B.retur_proforma_id
  97.     AND B.ref_id = C.receive_goods_item_id
  98.     AND C.ref_id = D.po_item_id
  99.     AND A.retur_proforma_id = vRpId;
  100.  
  101.     INSERT INTO pu_po_balance_invoice
  102.             (tenant_id, ou_id, partner_id, po_id,
  103.             ref_doc_type_id, ref_id, ref_doc_no, ref_doc_date, ref_item_id, qty_rcv_po, po_uom_id,
  104.             curr_code, price_po, item_amount, flg_invoice, invoice_id,
  105.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  106.     SELECT A.tenant_id, A.ou_id, A.partner_id, D.po_id,
  107.             A.doc_type_id, A.retur_proforma_id, A.doc_no, A.doc_date, B.retur_proforma_item_id, B.qty_rcv_po, B.po_uom_id,
  108.             D.curr_code, D.nett_price_po,
  109.             f_get_amount_before_tax(B.qty_rcv_po * (D.gross_price_po - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vReturProformaDocTypeId, D.curr_code), vRoundingModeNonTax),
  110.             vYes, vEmptyId,
  111.             0, vDatetime, vUserId, vDatetime, vUserId
  112.     FROM pu_retur_proforma A, pu_retur_proforma_item B, pu_receive_goods_item C, pu_po_item D
  113.     WHERE A.retur_proforma_id = B.retur_proforma_id
  114.     AND B.ref_id = C.receive_goods_item_id
  115.     AND C.ref_id = D.po_item_id
  116.     AND A.retur_proforma_id = vRpId;
  117.  
  118.     INSERT INTO pu_po_balance_invoice_tax
  119.             (tenant_id, ou_id, partner_id, po_id,
  120.             ref_doc_type_id, ref_id, ref_item_id, tax_id, flg_amount,
  121.             tax_percentage, curr_code, base_amount, tax_amount, flg_invoice, invoice_id,
  122.             "version", create_datetime, create_user_id, update_datetime, update_user_id)
  123.     SELECT A.tenant_id, A.ou_id, A.partner_id, A.ref_id,
  124.             A.doc_type_id, A.retur_proforma_id, B.retur_proforma_item_id, D.tax_id, E.flg_amount,
  125.             D.tax_percentage, D.curr_code,
  126.             f_get_amount_before_tax(B.qty_rcv_po * (D.gross_price_po - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vReturProformaDocTypeId, D.curr_code), vRoundingModeNonTax),
  127.             f_tax_rounding(A.tenant_id, f_get_amount_before_tax(B.qty_rcv_po * (D.gross_price_po - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vReturProformaDocTypeId, D.curr_code), vRoundingModeNonTax), D.tax_percentage),
  128.             vYes, vEmptyId,
  129.             0, vDatetime, vUserId, vDatetime, vUserId
  130.     FROM pu_retur_proforma A, pu_retur_proforma_item B, pu_receive_goods_item C, pu_po_item D, m_tax E
  131.     WHERE A.retur_proforma_id = B.retur_proforma_id
  132.     AND B.ref_id = C.receive_goods_item_id
  133.     AND C.ref_id = D.po_item_id
  134.     AND D.tax_id = E.tax_id
  135.     AND A.retur_proforma_id = vRpId;
  136.  
  137.     INSERT INTO in_log_product_balance_stock
  138.         (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
  139.          product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
  140.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  141.     SELECT A.tenant_id, A.ou_id, A.doc_type_id, B.retur_proforma_id, A.doc_no, A.doc_date, A.partner_id,
  142.            C.product_id,E.product_balance_id,D.warehouse_id,vProductStatus, B.base_uom_id, SUM(B.qty_rcv_int),
  143.            0, vDatetime, vUserId, vDatetime, vUserId
  144.     FROM pu_retur_proforma A, pu_retur_proforma_item B, m_product C, m_warehouse_ou D, in_product_balance E
  145.     WHERE A.retur_proforma_id = B.retur_proforma_id
  146.     AND B.product_id = C.product_id
  147.     AND A.warehouse_id = D.warehouse_id
  148.     AND C.product_id = E.product_id
  149.     AND A.retur_proforma_id = vRpId
  150.     GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, B.retur_proforma_item_id, A.doc_no, A.doc_date, A.partner_id,
  151.         C.product_id,E.product_balance_id,D.warehouse_id,B.base_uom_id;
  152.  
  153.     -- cek dulu warehouse ou nya   
  154.     SELECT B.ou_id INTO vOuWarehouseId
  155.     FROM pu_retur_proforma A
  156.     INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id
  157.     WHERE retur_proforma_id = vRpId;
  158.    
  159.     IF vOuWarehouseId IS NULL THEN
  160.         vOuWarehouseId = vEmptyId;
  161.     END IF;
  162.        
  163.     -- cek dulu apakah ou yang digunakan ini merupakan ou outlet
  164.     IF EXISTS(SELECT 1 FROM i_outlet A WHERE A.ou_id = vOuWarehouseId) THEN
  165.         SELECT A.outlet_id INTO vOutletId
  166.         FROM i_outlet A WHERE A.ou_id = vOuWarehouseId;
  167.     END IF;
  168.    
  169.     IF vOutletId IS NULL THEN
  170.         vOutletId = vEmptyId;
  171.     END IF;
  172.    
  173.     -- jika outlet, maka harus tulis data log agar outlet bisa lakukan update
  174.     IF vOutletId <> vEmptyId THEN
  175.         vDbVersion := f_get_value_system_config_by_param_code(pTenantId, 'DbVersion');
  176.        
  177.         INSERT INTO i_trx_data_log(
  178.             tenant_id, ou_id, doc_date, db_version, type_data,
  179.             mode_log, data_log, version, create_datetime, create_user_id,
  180.             update_datetime, update_user_id)
  181.         SELECT B.tenant_id, B.ou_id, B.doc_date, '1.0', 'in_log_product_balance_stock',
  182.                'U', '{"tenantId":'||B.tenant_id||',"createDateTime":"'||B.create_datetime||'","baseUomId":'||B.base_uom_id||',"ouId":'||B.ou_id||',"docNo":"'||B.doc_no||'","refId":'||B.ref_id||',"partnerId":'||B.partner_id||',"qty":'||B.qty||',"warehouseId":'||B.warehouse_id||',"version":'||B.version||',"productId":'||B.product_id||',"updateUserId":'||B.update_user_id||',"productStatus":"'||B.product_status||'","docDate":"'||B.doc_date||'","updateDateTime":"'||B.update_datetime||'","productBalanceId":'||B.product_balance_id||',"createUserId":'||B.create_user_id||',"docTypeId":'||B.doc_type_id||'}',
  183.                0, vDatetime, vUserId, vDatetime, vUserId
  184.         FROM pu_retur_proforma A
  185.         INNER JOIN in_log_product_balance_stock B
  186.             ON A.tenant_id = B.tenant_id
  187.             AND A.doc_no = B.doc_no
  188.             AND A.doc_date = B.doc_date
  189.             AND A.doc_type_id = B.doc_type_id
  190.             AND A.retur_proforma_id = B.ref_id
  191.             AND A.warehouse_id = B.warehouse_id
  192.         WHERE A.retur_proforma_id = vRpId;
  193.     END IF;
  194.  
  195.     INSERT INTO in_log_product_price_balance_stock
  196.         (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
  197.          product_id, warehouse_id, product_balance_id, product_price_balance_id, product_status, base_uom_id, qty,
  198.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  199.     SELECT A.tenant_id, A.ou_id, A.doc_type_id, B.retur_proforma_id, A.doc_no, A.doc_date, A.partner_id,
  200.            C.product_id,A.warehouse_id, D.product_balance_id, E.product_price_balance_id ,vProductStatus,B.base_uom_id,SUM(B.qty_rcv_int),
  201.            0, vDatetime, vUserId, vDatetime, vUserId
  202.     FROM pu_retur_proforma A, pu_retur_proforma_item B, m_product C, in_product_balance D , in_product_price_balance_stock E
  203.     WHERE A.retur_proforma_id = B.retur_proforma_id
  204.     AND B.product_id = C.product_id
  205.     AND C.product_id = D.product_id
  206.     AND D.product_balance_id = E.product_balance_id
  207.     AND A.retur_proforma_id = vRpId
  208.     GROUP BY A.tenant_id, A.ou_id, A.doc_type_id, B.retur_proforma_id, A.doc_date, A.doc_no, A.partner_id,
  209.         C.product_id,A.warehouse_id, D.product_balance_id,B.base_uom_id,E.product_price_balance_id;
  210.    
  211.  
  212.     SELECT A.ref_id, f_get_ou_bu_structure(A.ou_id) AS ou, f_get_document_journal(A.doc_type_id) as doc
  213.     FROM pu_retur_proforma A
  214.     WHERE A.retur_proforma_id = vRpId INTO result;
  215.  
  216. --  vRpId := result.ref_id;
  217.     vOuStructure := result.ou;
  218.     vDocJournal := result.doc;      
  219.    
  220.     /*
  221.      * Membuat journal
  222.      * Journal yang terbentuk
  223.      * D dan ACCL_AP (yang mau di koreksi) --> Dokumen Dari receive Goods
  224.      * C dan ACCL_AP (yang betul) --> Dokumen yg terbentuk Retur Proforma
  225.      */
  226.    
  227.     PERFORM gl_manage_admin_journal_trx(A.tenant_id, (vOuStructure).ou_bu_id, A.ou_id, (vDocJournal).journal_type, (vDocJournal).ledger_code, f_get_year_month_date(A.doc_date), 'DAILY', vDatetime, vUserId)
  228.     FROM pu_retur_proforma A
  229.     WHERE A.retur_proforma_id = vRpId;
  230.  
  231.     SELECT NEXTVAL('gl_journal_trx_seq') INTO vJournalTrxId;
  232.  
  233.     INSERT INTO gl_journal_trx
  234.         (journal_trx_id, tenant_id, journal_type, doc_type_id, doc_id, doc_no, doc_date,
  235.         ou_bu_id, ou_branch_id, ou_sub_bu_id, partner_id, cashbank_id, warehouse_id, ext_doc_no, ext_doc_date,  
  236.         ref_doc_type_id, ref_id, due_date, curr_code, remark, status_doc, workflow_status,
  237.         "version", create_datetime, create_user_id, update_datetime, update_user_id)
  238.     SELECT vJournalTrxId, A.tenant_id, (vDocJournal).journal_type, A.doc_type_id, A.retur_proforma_id, A.doc_no, A.doc_date,
  239.         (vOuStructure).ou_bu_id, (vOuStructure).ou_branch_id, (vOuStructure).ou_sub_bu_id, A.partner_id, vEmptyId, A.warehouse_id, A.ext_doc_no, A.ext_doc_date,
  240.         A.ref_doc_type_id, A.ref_id, A.doc_date, C.curr_code, A.remark, 'D', 'DRAFT',
  241.         0, vDatetime, vUserId, vDatetime, vUserId
  242.     FROM pu_retur_proforma A, pu_receive_goods B, pu_po C
  243.     WHERE A.retur_proforma_id = vRpId
  244.     AND B.ref_doc_type_id = C.doc_type_id
  245.     AND A.ref_id = C.po_id
  246.     AND A.ref_doc_type_id = B.doc_type_id
  247.     AND A.ref_id = B.receive_goods_id;
  248.  
  249.     INSERT INTO tt_journal_trx_item
  250.     (session_id, tenant_id, journal_trx_id, line_no,
  251.     ref_doc_type_id, ref_id,
  252.     partner_id, product_id, cashbank_id, ou_rc_id,
  253.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  254.     coa_id, curr_code, qty, uom_id,
  255.     amount, journal_date, type_rate,
  256.     numerator_rate, denominator_rate, journal_desc, remark)
  257.     SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  258.         A.doc_type_id, B.retur_proforma_item_id,
  259.         A.partner_id, B.product_id, vEmptyId, D.ou_rc_id,
  260.         vEmptyId, vSignCredit, vProductCOA, D.activity_gl_id,
  261.         D.product_coa_id, D.curr_code, B.qty_rcv_int, B.base_uom_id,
  262.         f_get_amount_before_tax(B.qty_rcv_po * (D.gross_price_po - D.discount_amount), D.flg_tax_amount, D.tax_percentage,f_get_digit_decimal_doc_curr(vReturProformaDocTypeId, D.curr_code), vRoundingModeNonTax),
  263.         A.doc_date, vTypeRate,
  264.         1, 1, 'PRODUCT_STOCK', B.remark
  265.     FROM pu_retur_proforma A, pu_retur_proforma_item B, pu_receive_goods_item C, pu_po_item D
  266.     WHERE A.retur_proforma_id = B.retur_proforma_id
  267.     AND B.ref_id = C.receive_goods_item_id
  268.     AND C.ref_id = D.po_item_id
  269.     AND A.retur_proforma_id = vRpId
  270.     AND D.flg_stock = vYes;
  271.  
  272.     INSERT INTO tt_journal_trx_item
  273.     (session_id, tenant_id, journal_trx_id, line_no,
  274.     ref_doc_type_id, ref_id,
  275.     partner_id, product_id, cashbank_id, ou_rc_id,
  276.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  277.     coa_id, curr_code, qty, uom_id,
  278.     amount, journal_date, type_rate,
  279.     numerator_rate, denominator_rate, journal_desc, remark)
  280.     SELECT pSessionId, A.tenant_id, vJournalTrxId, 1,
  281.         A.doc_type_id, B.retur_proforma_id,
  282.         A.partner_id, B.product_id, vEmptyId, D.ou_rc_id,
  283.         D.segment_id, vSignCredit, vActivityCOA, D.activity_gl_id,
  284.         E.coa_id, D.curr_code, B.qty_rcv_int, B.base_uom_id,
  285.         f_get_amount_before_tax(B.qty_rcv_po * (D.gross_price_po - D.discount_amount), D.flg_tax_amount, D.tax_percentage, f_get_digit_decimal_doc_curr(vReturProformaDocTypeId, D.curr_code), vRoundingModeNonTax),
  286.         A.doc_date, vTypeRate,
  287.         1, 1, 'PRODUCT_NON_STOCK', B.remark
  288.     FROM pu_retur_proforma A, pu_retur_proforma_item B, pu_receive_goods_item C, pu_po_item D, m_activity_gl E
  289.     WHERE A.retur_proforma_id = B.retur_proforma_id
  290.     AND B.ref_id = C.receive_goods_item_id
  291.     AND C.ref_id = D.po_item_id
  292.     AND A.retur_proforma_id = vRpId
  293.     AND D.flg_stock = 'N'
  294.     AND D.activity_gl_id = E.activity_gl_id;
  295.  
  296.         INSERT INTO gl_journal_trx_item
  297.     (tenant_id, journal_trx_id, line_no,
  298.     ref_doc_type_id, ref_id,
  299.     partner_id, product_id, cashbank_id, ou_rc_id,
  300.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  301.     coa_id, curr_code, qty, uom_id,
  302.     amount, journal_date, type_rate,
  303.     numerator_rate, denominator_rate, journal_desc, remark,
  304.     "version", create_datetime, create_user_id, update_datetime, update_user_id,
  305.     ou_branch_id, ou_sub_bu_id)
  306.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  307.         A.ref_doc_type_id, A.ref_id,
  308.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  309.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  310.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  311.         A.amount, A.journal_date, A.type_rate,
  312.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  313.         0, vDatetime, vUserId, vDatetime, vUserId,
  314.         (vOuStructureJournalItem).ou_branch_id, (vOuStructureJournalItem).ou_sub_bu_id
  315.     FROM tt_journal_trx_item A
  316.     WHERE A.session_id = pSessionId
  317.     AND A.journal_desc = 'PRODUCT_STOCK';
  318.  
  319.     INSERT INTO gl_journal_trx_item
  320.     (tenant_id, journal_trx_id, line_no,
  321.     ref_doc_type_id, ref_id,
  322.     partner_id, product_id, cashbank_id, ou_rc_id,
  323.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  324.     coa_id, curr_code, qty, uom_id,
  325.     amount, journal_date, type_rate,
  326.     numerator_rate, denominator_rate, journal_desc, remark,
  327.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  328.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  329.         A.ref_doc_type_id, A.ref_id,
  330.         A.partner_id, A.product_id, A.cashbank_id, A.ou_rc_id,
  331.         A.segmen_id, A.sign_journal, A.flg_source_coa, A.activity_gl_id,
  332.         A.coa_id, A.curr_code, A.qty, A.uom_id,
  333.         A.amount, A.journal_date, A.type_rate,
  334.         A.numerator_rate, A.denominator_rate, A.journal_desc, A.remark,
  335.         0, vDatetime, vUserId, vDatetime, vUserId
  336.     FROM tt_journal_trx_item A
  337.     WHERE A.session_id = pSessionId
  338.     AND A.journal_desc = 'PRODUCT_NON_STOCK';
  339.  
  340.     INSERT INTO gl_journal_trx_mapping
  341.     (tenant_id, journal_trx_id, line_no,
  342.     ref_doc_type_id, ref_id,
  343.     partner_id, product_id, cashbank_id, ou_rc_id,
  344.     segmen_id, sign_journal, flg_source_coa, activity_gl_id,
  345.     coa_id, curr_code, qty, uom_id,
  346.     amount, journal_date, type_rate,
  347.     numerator_rate, denominator_rate, journal_desc, remark,
  348.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  349.     SELECT A.tenant_id, A.journal_trx_id, ROW_NUMBER() OVER ( PARTITION BY A.journal_trx_id ),
  350.         vEmptyId, vEmptyId,    
  351.         A.partner_id, vEmptyId, vEmptyId, vEmptyId,
  352.         vEmptyId, vSignCredit, vSystemCOA, vEmptyId,
  353.         f_get_system_coa_by_group_coa(A.tenant_id, 'HutangHarusDibayar'), A.curr_code, 0, vEmptyId,
  354.         SUM(A.amount), A.journal_date, A.type_rate,
  355.         1, 1, 'ACCL_AP', vEmptyValue,
  356.         0, vDatetime, vUserId, vDatetime, vUserId
  357.     FROM tt_journal_trx_item A
  358.     WHERE A.session_id = pSessionId
  359.     GROUP BY A.tenant_id, A.journal_trx_id, A.partner_id, A.curr_code, A.journal_date, A.type_rate;
  360.    
  361.     DELETE FROM tt_journal_trx_item WHERE session_id = pSessionId; 
  362.    
  363. END;
  364. $BODY$
  365.   LANGUAGE plpgsql VOLATILE
  366.   COST 100;
  367. /
Advertisement
Add Comment
Please, Sign In to add comment