widana

sl_validate_process_so_do_from_pos

Mar 19th, 2017
138
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. CREATE OR REPLACE FUNCTION sl_validate_process_so_do_from_pos(character varying,bigint,bigint,bigint,bigint)
  2.   RETURNS CHARACTER VARYING AS
  3. $BODY$
  4. DECLARE
  5.     pSessionId              ALIAS FOR $1;
  6.     pOuId                   ALIAS FOR $2;
  7.     pTenantId               ALIAS FOR $3;
  8.     pEventId                ALIAS FOR $4;
  9.     pPosId                  ALIAS FOR $5;
  10.    
  11.     vNullLongValue          BIGINT := -99;
  12.     vCountFail              BIGINT;
  13.     vStatusOk               CHARACTER VARYING;
  14.     vStatusFail             CHARACTER VARYING;
  15.     vCurrentDateTime        CHARACTER VARYING;
  16.     vCurrentDate            CHARACTER VARYING;
  17.     vYes                    CHARACTER VARYING;
  18.     vNo                     CHARACTER VARYING;
  19.     vEmptyValue             CHARACTER VARYING;
  20.    
  21. BEGIN
  22.     vStatusOk := 'OK';
  23.     vStatusFail := 'FAIL';
  24.     vYes := 'Y';
  25.     vNo := 'N';
  26.     vEmptyValue := '';
  27.    
  28.     SELECT f_datetime((extract(epoch from now())*1000)::bigint) INTO vCurrentDateTime;
  29.     SELECT to_char(CURRENT_DATE, 'YYYYMMDD') INTO vCurrentDate;
  30.    
  31.     /*
  32.      * Delete table temporary
  33.      */
  34.     DELETE FROM tt_sl_so_closing_event_pos WHERE tenant_id = pTenantId AND ou_id = pOuId AND event_id = pEventId AND session_id = pSessionId;
  35.        
  36.     IF (SELECT COUNT(1) FROM m_event WHERE end_date < vCurrentDate and event_id = pEventId) > 0 THEN
  37.         RETURN 'cannot.be.processed.because.the.date.is.now.smaller.than.the.date.of.the.event.ends';
  38.     END IF;
  39.    
  40.     /*
  41.      * Insert ke table temporary
  42.      */
  43.     INSERT INTO tt_sl_so_closing_event_pos(
  44.             session_id, tenant_id, ou_id, event_id, pos_id, line_no, product_id,
  45.             product_code, product_name, qty_pos, uom_code, price_per_unit, sub_total,
  46.             status, message)
  47.     SELECT pSessionId,pTenantId,pOuId,pEventId,B.pos_id,ROW_NUMBER() OVER (ORDER BY A.product_id),A.product_id,
  48.             A.product_code_correction,A.product_name_correction,SUM(A.qty_correction) AS qty_pos,A.uom_code,A.price_per_unit_correction,SUM(A.sub_total_correction) AS sub_total,
  49.             vEmptyValue AS status,vEmptyValue AS message
  50.     FROM sl_pos_item A
  51.     INNER JOIN sl_pos B ON A.pos_id = B.pos_id
  52.     INNER JOIN m_event C ON B.event_id = C.event_id
  53.     WHERE B.tenant_id = pTenantId
  54.     AND B.ou_id = pOuId
  55.     AND C.event_id = pEventId
  56.     AND B.pos_id = pPosId
  57.     AND A.flg_correction = vYes
  58.     AND C.active = vYes
  59.     GROUP BY B.pos_id,A.product_code_correction,A.product_name_correction,
  60.             A.price_per_unit_correction,A.product_id,A.uom_code;
  61.            
  62.     RAISE NOTICE '1 SELESAI INSERT KE TABLE TEMPORARY';
  63.    
  64.     IF EXISTS (SELECT 1 FROM tt_sl_so_closing_event_pos WHERE session_id = pSessionId) THEN
  65.    
  66.         /*
  67.          * Cek Ketersediaan Kode Produk di table master
  68.          */
  69.          UPDATE tt_sl_so_closing_event_pos Z
  70.              SET status = vStatusFail , message = Z.message || 'Kode Produk tidak terdaftar.'
  71.          FROM tt_sl_so_closing_event_pos A
  72.          LEFT OUTER JOIN m_product B ON A.product_id = B.product_id
  73.          WHERE A.session_id = pSessionId
  74.          AND A.status <> vStatusFail
  75.          AND B.product_id IS NULL;
  76.          
  77.         RAISE NOTICE '2 CEK KETERSEDIAAN PRODUK';
  78.          
  79.          /*
  80.           * Cek ketersediaan stock di gudang
  81.           */
  82.         WITH availabelQty AS (
  83.             SELECT A.session_id,A.tenant_id,A.ou_id,A.pos_id,A.event_id,A.product_id,B.warehouse_id
  84.             FROM tt_sl_so_closing_event_pos A
  85.             INNER JOIN m_event B ON A.event_id = B.event_id
  86.             INNER JOIN in_product_balance_stock C ON A.product_id = C.product_id
  87.             WHERE A.qty_pos > C.qty
  88.             AND B.warehouse_id = C.warehouse_id
  89.         )
  90.         UPDATE tt_sl_so_closing_event_pos Z
  91.             SET status = vStatusFail, message= Z.message || 'Qty tidak cukup di gudang ' || f_get_warehouse_name(A.warehouse_id)
  92.         FROM availabelQty A
  93.         WHERE Z.tenant_id = A.tenant_id
  94.         AND Z.session_id = A.session_id
  95.         AND Z.ou_id = A.ou_id
  96.         AND Z.product_id = A.product_id
  97.         AND Z.status <> vStatusFail;
  98.    
  99.         RAISE NOTICE '3 CEK KETERSEDIAAN QTY';
  100.  
  101.         UPDATE tt_sl_so_closing_event_pos
  102.             SET status = vStatusOk
  103.         WHERE session_id = pSessionId AND status = vEmptyValue;
  104.        
  105.         IF (SELECT COUNT(1) FROM tt_sl_so_closing_event_pos WHERE session_id = pSessionId AND status = vStatusFail) = 0 THEN
  106.             /*
  107.              * Panggil function untuk lakukan generate so dan do
  108.              * PERFORM process_generate_so_do_from_pos(pSessionId);
  109.              */
  110.             RETURN vStatusOk;
  111.         ELSE
  112.             RETURN 'error.data.product';
  113.         END IF;
  114.        
  115.     ELSE
  116.         RETURN 'cannot.process.because.not.yet.data.pos.transaction';
  117.     END IF;
  118.    
  119. END;
  120. $BODY$
  121.   LANGUAGE plpgsql VOLATILE
  122.   COST 100;
Advertisement
Add Comment
Please, Sign In to add comment