Advertisement
Guest User

Untitled

a guest
Mar 27th, 2017
44
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION in_submit_put_away(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.     vId                 bigint;
  11.     vUserId             bigint;
  12.     vDatetime           character varying(14);
  13.     vFlagNo             character varying(1);
  14.     vFlagYes            character varying(1);
  15.     vStatusRelease      character varying(1);
  16. BEGIN
  17.    
  18.     vFlagNo := 'N';
  19.     vFlagYes := 'Y';
  20.     vStatusRelease := 'R';
  21.    
  22.     SELECT A.process_message_id INTO vProcessId
  23.     FROM t_process_message A
  24.     WHERE A.tenant_id = pTenantId AND
  25.         A.process_name = 'in_submit_put_away' AND
  26.         A.process_no = pProcessNo;
  27.        
  28.     SELECT CAST(A.process_parameter_value AS bigint) INTO vId
  29.     FROM t_process_parameter A
  30.     WHERE A.process_message_id = vProcessId AND
  31.         A.process_parameter_key = 'id';
  32.    
  33.     SELECT CAST(A.process_parameter_value AS bigint) INTO vUserId
  34.     FROM t_process_parameter A
  35.     WHERE A.process_message_id = vProcessId AND
  36.         A.process_parameter_key = 'userId';
  37.  
  38.     SELECT CAST(A.process_parameter_value AS character varying(14)) INTO vDatetime
  39.     FROM t_process_parameter A
  40.     WHERE A.process_message_id = vProcessId AND
  41.         A.process_parameter_key = 'datetime';
  42.        
  43.     /*
  44.      * 1.update status doc in_inventory
  45.      * 2.insert data ke in_log_product_balance_stock
  46.      * 3.add data in_balance_transfer_out
  47.      */
  48.        
  49.     UPDATE in_inventory SET status_doc = vStatusRelease, update_datetime = vDatetime, update_user_id = vUserId
  50.     WHERE inventory_id = vId;
  51.  
  52.     INSERT INTO in_log_product_balance_stock
  53.     (tenant_id, ou_id, doc_type_id, ref_id, doc_no, doc_date, partner_id,
  54.      product_id, product_balance_id, warehouse_id, product_status, base_uom_id, qty,
  55.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  56.     SELECT A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
  57.         B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id, SUM(B.qty_realization) * -1,
  58.         0, vDatetime, vUserId, vDatetime, vUserId
  59.     FROM in_inventory A, in_inventory_item B, m_warehouse_ou C
  60.     WHERE A.inventory_id = vId AND
  61.         A.inventory_id = B.inventory_id AND
  62.         A.warehouse_from_id = C.warehouse_id
  63.     GROUP BY A.tenant_id, C.ou_id, A.doc_type_id, A.inventory_id, A.doc_no, A.doc_date, A.partner_id,
  64.         B.product_id, B.product_balance_id, A.warehouse_from_id, B.product_status, B.base_uom_id;
  65.        
  66.     -- INSERT kan ke in_balance_transfer_out langsung dengan flg_real = Y
  67.     INSERT INTO in_balance_transfer_out
  68.     (inventory_id, tenant_id, ou_id, warehouse_id, flg_real, remark,
  69.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  70.     SELECT A.inventory_id, A.tenant_id, A.ou_to_id, A.warehouse_to_id, vFlagYes, A.remark,
  71.         0, vDatetime, vUserId, vDatetime, vUserId
  72.     FROM in_inventory A
  73.     WHERE A.inventory_id = vId;
  74.    
  75.     ---------------------------------------------------------------------------
  76.    
  77.     /*
  78.      * create summary dari in_inventory_item untuk update yg sudah ada di in_product_balance_stock
  79.      */
  80.     INSERT INTO tt_in_product_balance_summary_stock
  81.             (session_id, tenant_id, inventory_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty)
  82.     SELECT pSessionId, A.tenant_id, A.inventory_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status,
  83.             B.base_uom_id, SUM(B.qty_realization)
  84.     FROM in_inventory A, in_inventory_item B
  85.     WHERE A.inventory_id = B.inventory_id
  86.         AND A.inventory_id = vId
  87.     GROUP BY A.tenant_id, A.inventory_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
  88.        
  89.     UPDATE in_product_balance_stock SET qty = in_product_balance_stock.qty + A.qty, update_datetime = vDatetime, update_user_id = vUserId
  90.     FROM tt_in_product_balance_summary_stock A
  91.     WHERE A.session_id = pSessionId AND
  92.         A.inventory_id = vId AND
  93.         in_product_balance_stock.tenant_id = A.tenant_id AND
  94.         in_product_balance_stock.warehouse_id = A.warehouse_id AND
  95.         in_product_balance_stock.product_id = A.product_id AND
  96.         in_product_balance_stock.product_balance_id = A.product_balance_id AND
  97.         in_product_balance_stock.product_status = A.product_status;
  98.        
  99.     /*
  100.      * insert data in_product_balance_stock
  101.      */
  102.     INSERT INTO in_product_balance_stock
  103.     (tenant_id, warehouse_id, product_id, product_balance_id, product_status, base_uom_id, qty,
  104.     "version", create_datetime, create_user_id, update_datetime, update_user_id)
  105.     SELECT A.tenant_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id, SUM(B.qty_realization),
  106.            0, vDatetime, vUserId, vDatetime, vUserId
  107.     FROM in_inventory A
  108.     INNER JOIN in_inventory_item B ON A.inventory_id = B.inventory_id
  109.     WHERE A.inventory_id = vId AND
  110.     NOT EXISTS (SELECT 1 FROM in_product_balance_stock C
  111.                 WHERE A.tenant_id = C.tenant_id AND
  112.                       A.warehouse_to_id = C.warehouse_id AND
  113.                       B.product_id = C.product_id AND
  114.                       B.product_balance_id = C.product_balance_id AND
  115.                       B.product_status = C.product_status)
  116.     GROUP BY A.tenant_id, A.warehouse_to_id, B.product_id, B.product_balance_id, B.product_status, B.base_uom_id;
  117.    
  118. END;
  119. $BODY$
  120.   LANGUAGE plpgsql VOLATILE
  121.   COST 100;
  122.   /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement