Advertisement
aadddrr

Untitled

Mar 23rd, 2017
96
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. /*
  2.  * rekap stok
  3.  * filter mandatory : session_id, tenant_id, ou_id, date_to
  4.  * filter optional : category product, sub category product, product
  5.  * jika filter optional diisi -99 artinya data digunakan semua
  6.  */
  7. CREATE OR REPLACE FUNCTION r_outlet_saldo_stok_akhir_vs_harga_beli(character varying, bigint, bigint, character varying, bigint, bigint, bigint)
  8.   RETURNS SETOF refcursor AS
  9. $BODY$
  10. DECLARE
  11.     pRefHeader          REFCURSOR := 'refHeader';
  12.     pRefDetail          REFCURSOR := 'refDetail';  
  13.     pSessionId              ALIAS FOR $1;
  14.     pTenantId               ALIAS FOR $2;
  15.     pOuId                   ALIAS FOR $3;
  16.     pDateTo                 ALIAS FOR $4;
  17.     pCtgrProductId          ALIAS FOR $5;
  18.     pSubCtgrProductId       ALIAS FOR $6;
  19.     pUserId                 ALIAS FOR $7;
  20.    
  21.     vEmptyValue             character varying(1);
  22.     vEmptyId                bigint;
  23.     vAllId                  bigint;
  24.     vWarehouseId            bigint;
  25.     vParentOuId             bigint;
  26.     vRoundingMode           character varying(5);
  27.    
  28.     vYes                    character varying(1);
  29.     vNo                     character varying(1);
  30.    
  31. BEGIN
  32.    
  33.     vEmptyValue := ' ';
  34.     vEmptyId := -99;
  35.     vAllId := -99;
  36.     vParentOuId := f_get_parent_ou_bu(pTenantId, pOuId);
  37.     vYes := 'Y';
  38.     vNo := 'N';
  39.     SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingMode;
  40.    
  41.     SELECT warehouse_id INTO vWarehouseId
  42.     FROM i_outlet
  43.     WHERE tenant_id = pTenantId AND ou_id = pOuId;
  44.    
  45.     DELETE FROM tr_saldo_stok WHERE session_id = pSessionId;
  46.     DELETE FROM tr_sell_price WHERE session_id = pSessionId;   
  47.    
  48.     INSERT INTO tr_saldo_stok
  49.     (session_id, tenant_id, product_id, base_uom_id, qty,
  50.     product_code, product_name, ctgr_product_id, sub_ctgr_product_id)
  51.     SELECT pSessionId, A.tenant_id, A.product_id, A.base_uom_id, SUM(A.qty),
  52.         B.product_code, B.product_name, B.ctgr_product_id, B.sub_ctgr_product_id       
  53.     FROM in_product_balance_stock A
  54.         INNER JOIN m_product B ON A.product_id = B.product_id
  55.     WHERE A.tenant_id = pTenantId AND
  56.         A.warehouse_id = vWarehouseId AND
  57.         A.qty <> 0
  58.     GROUP BY A.tenant_id, A.product_id, A.base_uom_id,
  59.         B.product_code, B.product_name, B.ctgr_product_id, B.sub_ctgr_product_id;
  60.        
  61.     /* Adrian, Mar 24, 2017
  62.      * tax percentage gross sell price di-set selalu 0 untuk flg tax amount Y
  63.      * Pada saat update:
  64.      * - tax amount dihitung terpisah  
  65.      */
  66.     INSERT INTO tr_sell_price
  67.     (session_id, tenant_id, ou_id, product_id,
  68.     curr_code, gross_sell_price, flg_tax_amount,
  69.     tax_percentage)
  70.     SELECT pSessionId, A.tenant_id, B.ou_id, A.product_id,
  71.         B.curr_code, COALESCE(f_get_gross_price_from_nett_amount(B.amount, B.qty, B.flg_tax_amount, 0, vRoundingMode, f_get_digit_decimal_doc_curr(-99, B.curr_code)), 0), B.flg_tax_amount,
  72.         B.tax_percentage
  73.     FROM tr_saldo_stok A, pu_monthly_price_product B
  74.     WHERE A.session_id = pSessionId AND
  75.         A.tenant_id = B.tenant_id AND
  76.         A.product_id = B.product_id AND
  77.         B.ou_id = vParentOuId AND
  78.         B.year_month_date = SUBSTRING(pDateTo, 1, 6) AND
  79.         B.flg_tax_amount = vYes;
  80.        
  81.     INSERT INTO tr_sell_price
  82.     (session_id, tenant_id, ou_id, product_id,
  83.     curr_code, gross_sell_price, flg_tax_amount,
  84.     tax_percentage)
  85.     SELECT pSessionId, A.tenant_id, B.ou_id, A.product_id,
  86.         B.curr_code, COALESCE(f_get_gross_price_from_nett_amount(B.amount, B.qty, B.flg_tax_amount, B.tax_percentage, vRoundingMode, f_get_digit_decimal_doc_curr(-99, B.curr_code)), 0), B.flg_tax_amount,
  87.         B.tax_percentage
  88.     FROM tr_saldo_stok A, pu_monthly_price_product B
  89.     WHERE A.session_id = pSessionId AND
  90.         A.tenant_id = B.tenant_id AND
  91.         A.product_id = B.product_id AND
  92.         B.ou_id = vParentOuId AND
  93.         B.year_month_date = SUBSTRING(pDateTo, 1, 6) AND
  94.         B.flg_tax_amount = vNo;
  95.    
  96.     INSERT INTO tr_sell_price
  97.     (session_id, tenant_id, ou_id, product_id,
  98.         curr_code, gross_sell_price, flg_tax_amount,
  99.         tax_percentage)
  100.     SELECT pSessionId, A.tenant_id, pOuId, A.product_id,
  101.         f_get_value_system_config_by_param_code(A.tenant_id, 'ValutaBuku'), 0, 'Y',
  102.         10.00
  103.     FROM tr_saldo_stok A
  104.     WHERE A.session_id = pSessionId AND
  105.         NOT EXISTS (SELECT 1 FROM tr_sell_price C
  106.                     WHERE A.session_id = C.session_id AND
  107.                         A.tenant_id = C.tenant_id AND
  108.                         A.product_Id = C.product_id);
  109.    
  110.     UPDATE tr_sell_price Z
  111.     SET tax_amount = f_get_tax_amount(Z.tenant_id, A.qty * Z.gross_sell_price, Z.flg_tax_amount, Z.tax_percentage)
  112.     FROM tr_saldo_stok A
  113.     WHERE A.session_id = Z.session_id AND
  114.         A.tenant_id = Z.tenant_id AND
  115.         A.product_Id = Z.product_id;
  116.  
  117.     IF pCtgrProductId <> vAllId THEN
  118.    
  119.         DELETE FROM tr_saldo_stok WHERE session_id = pSessionId AND ctgr_product_id <> pCtgrProductId;
  120.    
  121.     END IF;
  122.  
  123.     IF pSubCtgrProductId <> vAllId THEN
  124.    
  125.         DELETE FROM tr_saldo_stok WHERE session_id = pSessionId AND sub_ctgr_product_id <> pSubCtgrProductId;
  126.    
  127.     END IF;
  128.    
  129.     Open pRefHeader FOR
  130.     SELECT B.ou_name AS outlet, A.fullname AS full_name, pDateTo AS date_to, pCtgrProductId AS ctgr_product_id, pSubCtgrProductId AS sub_ctgr_product_id
  131.     FROM t_user A
  132.     INNER JOIN t_ou B ON A.tenant_id = B.tenant_id
  133.     WHERE A.user_id = pUserId AND B.ou_id = pOuId;
  134.    
  135.     RETURN NEXT pRefHeader;
  136.    
  137.     Open pRefDetail FOR
  138.     SELECT B.curr_code, C.ctgr_product_name AS ctgr_product_name, D.sub_ctgr_product_name AS sub_ctgr_product_name,
  139.         A.product_code AS product_code, A.product_name AS product_name, E.uom_code AS uom_code,
  140.         SUM(A.qty) AS qty, B.gross_sell_price AS gross_sell_price, SUM(A.qty * B.gross_sell_price) AS saldo_amount, SUM(B.tax_amount) AS saldo_tax_amount
  141.     FROM tr_saldo_stok A, tr_sell_price B, m_ctgr_product C, m_sub_ctgr_product D, m_uom E
  142.     WHERE A.session_id = pSessionId AND
  143.         A.session_id = B.session_id AND
  144.         A.tenant_id = B.tenant_id AND
  145.         A.product_id = B.product_id AND
  146.         A.ctgr_product_id = C.ctgr_product_id AND
  147.         A.sub_ctgr_product_id = D.sub_ctgr_product_id AND
  148.         A.base_uom_id = E.uom_id
  149.     GROUP BY B.curr_code, C.ctgr_product_name, D.sub_ctgr_product_name, A.product_code, A.product_name, E.uom_code, B.gross_sell_price
  150.     ORDER BY B.curr_code, C.ctgr_product_name, D.sub_ctgr_product_name, A.product_code;
  151.    
  152.     RETURN NEXT pRefDetail;
  153.  
  154.     DELETE FROM tr_saldo_stok WHERE session_id = pSessionId;
  155.     DELETE FROM tr_sell_price WHERE session_id = pSessionId;   
  156. END;
  157. $BODY$
  158.   LANGUAGE plpgsql VOLATILE
  159.   COST 100
  160.   ROWS 1000;
  161.   /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement