G2A Many GEOs
SHARE
TWEET

Untitled

a guest Apr 2nd, 2020 107 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. Function sebelum diperbaiki set search path ditaruh di dalam create function itu salah
  2. CREATE OR REPLACE FUNCTION pb.pu_generate_receive_goods_from_po(
  3.     character varying,
  4.     bigint,
  5.     bigint,
  6.     character varying,
  7.     character varying)
  8.     RETURNS void
  9.     LANGUAGE 'plpgsql'
  10.  
  11.     COST 100
  12.     VOLATILE
  13.     SET search_path='"public, pb, mstr"'
  14. AS $BODY$
  15. DECLARE
  16.  
  17.     pSessionId          ALIAS FOR $1;
  18.     pPoId               ALIAS FOR $2;
  19.     pRecordOwnerId      ALIAS FOR $3;
  20.     pDatetime           ALIAS FOR $4;
  21.     pUsername           ALIAS FOR $5;
  22.  
  23.     vReleased           character varying := 'R';
  24.     vDraft              character varying := 'D';
  25.     vDocTypeId          bigint := 111;
  26.     vPrefixDocNo        character varying := 'TB';
  27.     vInProgress         character varying := 'I';
  28.  
  29.     vEmpty              character varying := '';
  30.     vDocNo              character varying := '';
  31.     vReceiveGoodsId     bigint := -99;
  32.     vWarehouseId        bigint := -99;
  33.  
  34. BEGIN
  35.  
  36.     SELECT nextval('pu_receive_goods_seq') INTO vReceiveGoodsId;
  37.  
  38.     SELECT warehouse_id INTO vWarehouseId FROM m_warehouse WHERE record_owner_id = pRecordOwnerId;
  39.  
  40.     INSERT INTO pu_receive_goods (
  41.         receive_goods_id, record_owner_id, doc_type_id, doc_no, doc_date, ext_doc_no,
  42.         ext_doc_date, warehouse_id, supplier_id, ref_doc_type_id, ref_id,
  43.         remark, status_doc, version, create_datetime, create_username,
  44.         update_username, update_datetime, remark_confirm
  45.     )
  46.     SELECT vReceiveGoodsId, record_owner_id, vDocTypeId, f_gen_autonum(concat(vPrefixDocNo,'/',LEFT(doc_date, 6),'/'), vPrefixDocNo), doc_date, ext_doc_no,
  47.             ext_doc_date, vWarehouseId, supplier_id, doc_type_id, po_id,
  48.             remark, vDraft, 0, create_datetime, create_username,
  49.             update_username, update_datetime, vEmpty
  50.     FROM pu_po
  51.     WHERE po_id = pPoId;
  52.  
  53.     INSERT INTO pu_receive_goods_item (
  54.         receive_goods_id, ref_item_id, product_id, qty_receive, unit_price,
  55.         flg_include_tax, tax_id, tax_percentage, item_amount_discount, item_amount_nett,
  56.         qty_dlv, version, create_datetime, create_username, update_username,
  57.         update_datetime
  58.     )
  59.     SELECT vReceiveGoodsId, A.po_item_id, A.product_id, B.qty_po, A.unit_price,
  60.         A.flg_include_tax, A.tax_id, A.tax_percentage, A.item_amount_discount, A.item_amount_nett,
  61.         0, 0, A.create_datetime, A.create_username, A.update_username,
  62.         A.update_datetime
  63.     FROM pu_po_item A
  64.     INNER JOIN pu_po_balance_item B ON A.po_item_id = B.po_item_id
  65.     WHERE A.po_id = pPoId;
  66.    
  67.     UPDATE pu_po_balance_item A
  68.     SET status_item = vInProgress
  69.     FROM pu_po_item B
  70.     WHERE A.po_item_id = B.po_item_id AND B.po_id = pPoId;
  71.  
  72. END;
  73. $BODY$;
  74.  
  75. Perbaikan SET Search Path ditaruh dibawah setelah $BODY$;
  76. CREATE OR REPLACE FUNCTION pb.pu_generate_receive_goods_from_po(
  77.     character varying,
  78.     bigint,
  79.     bigint,
  80.     character varying,
  81.     character varying)
  82.     RETURNS void
  83.     LANGUAGE 'plpgsql'
  84.  
  85.     COST 100
  86.     VOLATILE
  87. AS $BODY$
  88. DECLARE
  89.  
  90.     pSessionId          ALIAS FOR $1;
  91.     pPoId               ALIAS FOR $2;
  92.     pRecordOwnerId      ALIAS FOR $3;
  93.     pDatetime           ALIAS FOR $4;
  94.     pUsername           ALIAS FOR $5;
  95.  
  96.     vReleased           character varying := 'R';
  97.     vDraft              character varying := 'D';
  98.     vDocTypeId          bigint := 111;
  99.     vPrefixDocNo        character varying := 'TB';
  100.     vInProgress         character varying := 'I';
  101.  
  102.     vEmpty              character varying := '';
  103.     vDocNo              character varying := '';
  104.     vReceiveGoodsId     bigint := -99;
  105.     vWarehouseId        bigint := -99;
  106.  
  107. BEGIN
  108.  
  109.     SELECT nextval('pu_receive_goods_seq') INTO vReceiveGoodsId;
  110.  
  111.     SELECT warehouse_id INTO vWarehouseId FROM m_warehouse WHERE record_owner_id = pRecordOwnerId;
  112.  
  113.     INSERT INTO pu_receive_goods (
  114.         receive_goods_id, record_owner_id, doc_type_id, doc_no, doc_date, ext_doc_no,
  115.         ext_doc_date, warehouse_id, supplier_id, ref_doc_type_id, ref_id,
  116.         remark, status_doc, version, create_datetime, create_username,
  117.         update_username, update_datetime, remark_confirm
  118.     )
  119.     SELECT vReceiveGoodsId, record_owner_id, vDocTypeId, f_gen_autonum(concat(vPrefixDocNo,'/',LEFT(doc_date, 6),'/'), vPrefixDocNo), doc_date, ext_doc_no,
  120.             ext_doc_date, vWarehouseId, supplier_id, doc_type_id, po_id,
  121.             remark, vDraft, 0, create_datetime, create_username,
  122.             update_username, update_datetime, vEmpty
  123.     FROM pu_po
  124.     WHERE po_id = pPoId;
  125.  
  126.     INSERT INTO pu_receive_goods_item (
  127.         receive_goods_id, ref_item_id, product_id, qty_receive, unit_price,
  128.         flg_include_tax, tax_id, tax_percentage, item_amount_discount, item_amount_nett,
  129.         qty_dlv, version, create_datetime, create_username, update_username,
  130.         update_datetime
  131.     )
  132.     SELECT vReceiveGoodsId, A.po_item_id, A.product_id, B.qty_po, A.unit_price,
  133.         A.flg_include_tax, A.tax_id, A.tax_percentage, A.item_amount_discount, A.item_amount_nett,
  134.         0, 0, A.create_datetime, A.create_username, A.update_username,
  135.         A.update_datetime
  136.     FROM pu_po_item A
  137.     INNER JOIN pu_po_balance_item B ON A.po_item_id = B.po_item_id
  138.     WHERE A.po_id = pPoId;
  139.    
  140.     UPDATE pu_po_balance_item A
  141.     SET status_item = vInProgress
  142.     FROM pu_po_item B
  143.     WHERE A.po_item_id = B.po_item_id AND B.po_id = pPoId;
  144.  
  145. END
  146. $BODY$;
  147. SET search_path=public, pb, mstr;
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
Top