API tools faq
paste
aadddrr

Untitled

aadddrr
Jun 14th, 2017
77
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 7.00 KB | None | 0 0
raw download clone embed print report
  1. --Modified by Adrian, Jan 4 2016,
  2. --menambahkan sort
  3.  
  4. CREATE OR REPLACE FUNCTION f_get_inquiry_stock_qty(character varying, bigint, bigint, bigint, bigint, bigint, character varying, character varying, character varying, character varying, bigint, bigint, character varying, character varying)
  5.   RETURNS SETOF refcursor AS
  6. $BODY$
  7. DECLARE
  8.     pRefQtyDetail       REFCURSOR := 'refQtyDetail';
  9.     pSessionId          ALIAS FOR $1;
  10.     pTenantId           ALIAS FOR $2;
  11.     pOuId               ALIAS FOR $3;
  12.     pBrandId            ALIAS FOR $4;
  13.     pCtgrProductId      ALIAS FOR $5;
  14.     pSubCtgrProductId   ALIAS FOR $6;
  15.     pProductCode            ALIAS FOR $7;
  16.     pProductName            ALIAS FOR $8;
  17.     pShowData           ALIAS FOR $9;
  18.     pWarehouseName      ALIAS FOR $10;
  19.     pLimit              ALIAS FOR $11;
  20.     pOffset             ALIAS FOR $12;
  21.     pSortField          ALIAS FOR $13;
  22.     pSortOrder          ALIAS FOR $14;
  23.    
  24.     vEmptyId            bigint := -99;
  25.    
  26.     vEmptyIdString      character varying := '-99';
  27.     vEmptyString        character varying := '';
  28.     vFlagYes            character varying := 'Y';
  29.    
  30.     vFilterBrandId            text := '';
  31.     vFilterCtgrProductId      text := '';
  32.     vFilterSubCtgrProductId   text := '';
  33.     vFilterProductCode        text := '';
  34.     vFilterProductName        text := '';  
  35.     vFilterProductCode2       text := '';
  36.     vFilterProductName2       text := '';
  37.     vFilterWarehouseName      text := '';
  38.     vFilterShowData           text := '';
  39.     vFilterLimit              text := '';
  40.     vFilterOffset             text := '';  
  41.     vFilterOrderBy            text := '';
  42.     vFilterSortOrder          text := '';
  43.     vUpperProductCode         text := UPPER(pProductCode);
  44.     vUpperProductName         text := UPPER(pProductName);
  45.     vUpperWarehouseName       text := UPPER(pWarehouseName);
  46.  
  47. BEGIN
  48.     -- hapus tabel penampung data stok produk semua warehouse tanpa filter warehousename
  49.     DELETE FROM tr_get_inquiry_stock_qty WHERE session_id = pSessionId;
  50.     -- hapus tabel penampung data stok produk semua warehouse dengan filter warehousename
  51.     DELETE FROM tr_get_inquiry_stock_all_qty WHERE session_id = pSessionId;
  52.     -- hapus tabel penampung data warehouse
  53.     DELETE FROM tr_warehouse_inquiry_stock WHERE session_id = pSessionId;
  54.     -- hapus tabel penampung data product    
  55.     DELETE FROM tr_product_inquiry_stock WHERE session_id = pSessionId;
  56.    
  57.     /**
  58.      * Ambil dari table saldo filter berdasarkan ,
  59.      * Tenant, OU, Brand, kategory, subkategory, produk , dan qty
  60.      */
  61.    
  62.     IF (pBrandId <> vEmptyId) THEN
  63.         vFilterBrandId := ' AND E.brand_id = ' || pBrandId;
  64.     END IF;
  65.    
  66.     IF (pCtgrProductId <> vEmptyId) THEN
  67.         vFilterCtgrProductId := ' AND E.ctgr_product_id = ' || pCtgrProductId;
  68.     END IF;
  69.    
  70.     IF (pSubCtgrProductId <> vEmptyId) THEN
  71.         vFilterSubCtgrProductId := ' AND E.sub_ctgr_product_id = ' || pSubCtgrProductId;
  72.     END IF;
  73.    
  74.     IF (pProductCode <> vEmptyString) THEN
  75.         vFilterProductCode := ' AND UPPER(E.product_code) = ''' || vUpperProductCode || '''';
  76.     END IF;
  77.    
  78.     IF (pProductName <> vEmptyString) THEN
  79.         vFilterProductName := ' AND UPPER(E.product_name) like ''%' || vUpperProductName || '%''';
  80.     END IF;
  81.    
  82.     IF (pWarehouseName <> vEmptyString) THEN
  83.         vFilterWarehouseName  := ' AND UPPER(F.warehouse_name) like ''%' || vUpperWarehouseName || '%''';
  84.     END IF;
  85.    
  86.     IF (pShowData <> vEmptyIdString) THEN
  87.         vFilterShowData := ' HAVING SUM(A.qty) > 0 ';
  88.     END IF;
  89.    
  90.    
  91.     IF (pLimit <> vEmptyId) THEN
  92.         vFilterLimit := ' LIMIT  ' || pLimit;
  93.     END IF;
  94.        
  95.     IF (pOffset <> vEmptyId) THEN
  96.         vFilterOffset := ' OFFSET  ' || pOffset;
  97.     END IF;
  98.        
  99.     --Added by Adrian, Jan 4, 2016
  100.     IF pSortField <> vEmptyString THEN
  101.         IF pSortField = 'productCode' THEN
  102.             vFilterOrderBy := ' ORDER BY product_code ';
  103.         ELSEIF pSortField = 'productName' THEN
  104.             vFilterOrderBy := ' ORDER BY product_name ';
  105.         ELSEIF pSortField = 'warehouseName' THEN
  106.             vFilterOrderBy := ' ORDER BY warehouse_name ';
  107.         ELSEIF pSortField = 'productStatus' THEN
  108.             vFilterOrderBy := ' ORDER BY A.product_status ';
  109.         ELSEIF pSortField = 'qty' THEN
  110.             vFilterOrderBy := ' ORDER BY qty ';
  111.         ELSEIF pSortField = 'sellPrice' THEN
  112.             vFilterOrderBy := ' ORDER BY gross_sell_price ';
  113.         ELSEIF pSortField = 'groupProductCode' THEN
  114.             vFilterOrderBy := ' ORDER BY group_product_ou_code ';
  115.         END IF;
  116.     END IF;
  117.    
  118.     --Added by Adrian, Jan 4, 2016
  119.     IF pSortField <> vEmptyString THEN
  120.         IF (pSortOrder = 'ASCENDING') THEN
  121.             vFilterSortOrder := vFilterOrderBy || ' ASC ';
  122.         ELSE
  123.             vFilterSortOrder := vFilterOrderBy || ' DESC ';
  124.         END IF;
  125.     ELSE
  126.         vFilterSortOrder := ' ORDER BY product_name, warehouse_name ';
  127.     END IF;
  128.    
  129.     Open pRefQtyDetail FOR
  130.       EXECUTE  ' SELECT A.product_id, f_get_product_code(A.product_id) AS product_code, f_get_product_name(A.product_id) AS product_name, '||
  131.                '        A.warehouse_id, f_get_warehouse_code(A.warehouse_id) AS warehouse_code, f_get_warehouse_name(A.warehouse_id) AS warehouse_name, '||
  132.                '        SUM(A.qty) AS qty, A.base_uom_id, f_get_uom_code(A.base_uom_id) AS base_uom_code, f_get_uom_name(A.base_uom_id) AS base_uom_name, D.flg_serial_number, '||
  133.                '        A.product_status, COALESCE(f_get_group_product_ou_code(A.tenant_id, B.ou_id, A.product_id), '' '') AS group_product_ou_code, '||
  134.                '        f_get_group_product_ou_name(A.tenant_id, B.ou_id, A.product_id) AS group_product_ou_name, '||
  135.                '        f_get_gross_sell_price (A.tenant_id, B.ou_id, A.product_id) AS gross_sell_price, '||
  136.                '        f_get_gross_sell_price_curr_code (A.tenant_id, B.ou_id, A.product_id) AS curr_code '||
  137.                ' FROM in_product_balance_stock A '||
  138.                ' INNER JOIN m_product E ON A.product_id = E.product_id '||
  139.                ' INNER JOIN m_warehouse_ou B ON A.warehouse_id = B.warehouse_id '||
  140.                ' INNER JOIN m_warehouse F ON A.warehouse_id = F.warehouse_id '||
  141.                ' INNER JOIN m_sub_ctgr_product D ON E.sub_ctgr_product_id = D.sub_ctgr_product_id '||
  142.                ' WHERE A.tenant_id = '|| pTenantId ||              
  143.                 vFilterWarehouseName ||            
  144.                 vFilterProductCode ||
  145.                 vFilterProductName ||
  146.                 vFilterSubCtgrProductId ||
  147.                 vFilterCtgrProductId ||
  148.                 vFilterBrandId ||
  149.                 ' AND B.ou_id IN ( ' ||
  150.                 '     SELECT C.ou_id FROM m_ou_structure C WHERE C.ou_bu_id = '|| pOuId ||' OR C.ou_branch_id = '|| pOuId ||' OR C.ou_sub_bu_id = '|| pOuId ||
  151.                 ' ) ' ||
  152.                 ' GROUP BY A.tenant_id, B.ou_id, A.product_id, A.warehouse_id, A.base_uom_id, D.flg_serial_number, A.product_status '||
  153.                 vFilterShowData ||
  154.                 vFilterSortOrder ||
  155.                 vFilterLimit ||
  156.                 vFilterOffset;
  157.    
  158.             RETURN NEXT pRefQtyDetail ;
  159.    
  160.  
  161.     -- hapus tabel penampung data stok produk semua warehouse tanpa filter warehousename
  162.     DELETE FROM tr_get_inquiry_stock_qty WHERE session_id = pSessionId;
  163.     -- hapus tabel penampung data stok produk semua warehouse dengan filter warehousename
  164.     DELETE FROM tr_get_inquiry_stock_all_qty WHERE session_id = pSessionId;
  165.     -- hapus tabel penampung data warehouse
  166.     DELETE FROM tr_warehouse_inquiry_stock WHERE session_id = pSessionId;
  167.     -- hapus tabel penampung data product    
  168.     DELETE FROM tr_product_inquiry_stock WHERE session_id = pSessionId;
  169.    
  170. END;
  171. $BODY$
  172.   LANGUAGE plpgsql VOLATILE
  173.   COST 100
  174.   ROWS 1000;
Advertisement
Add Comment
Please, Sign In to add comment
Public Pastes
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
Not a member of Pastebin yet?
Sign Up, it unlocks many cool features!