API tools faq
paste
widana

query2

widana
Nov 17th, 2017
65
0
Never
Add comment
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PostgreSQL 7.42 KB | None | 0 0
raw download clone embed print report
  1. CREATE OR REPLACE FUNCTION r_report_inquiry_sales_outlet_csv(CHARACTER VARYING, BIGINT, BIGINT, BIGINT, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, BIGINT)
  2.   RETURNS SETOF refcursor AS
  3. $BODY$
  4. DECLARE
  5.     pRefHeader              REFCURSOR := 'refHeader';
  6.     pRefDetail              REFCURSOR := 'refDetail';
  7.    
  8.     pSessionId              ALIAS FOR $1;
  9.     pTenantId               ALIAS FOR $2;
  10.     pGroupOutletId          ALIAS FOR $3;
  11.     pOutletId               ALIAS FOR $4;
  12.     pPeriodStart            ALIAS FOR $5;
  13.     pPeriodEnd              ALIAS FOR $6;
  14.     pFlgVerified            ALIAS FOR $7;
  15.     pFlgUnused              ALIAS FOR $8;
  16.     pRoleId                 ALIAS FOR $9;
  17.  
  18.     vVerified               CHARACTER VARYING := 'Verified';
  19.     vNotVerified            CHARACTER VARYING := 'Not Verified';
  20.     vInProgressVerified     CHARACTER VARYING := 'In Progress';
  21.     vYes                    CHARACTER VARYING := 'Y';
  22.     vNo                     CHARACTER VARYING := 'N';
  23.     vInProgress             CHARACTER VARYING := 'I';
  24.     vUse                    CHARACTER VARYING := 'Dipakai';
  25.     vUnUsed                 CHARACTER VARYING := 'Tidak Dipakai';
  26.     vFine                   CHARACTER VARYING := 'Denda';
  27.     vNotFine                CHARACTER VARYING := 'Tidak Denda';
  28.     vEmptyValue             CHARACTER VARYING := '';
  29.     vFilterVerified         CHARACTER VARYING := '';
  30.     vFilterStatus           CHARACTER VARYING := '';
  31.     vFilterOutlet           CHARACTER VARYING := '';
  32.     vFilterShowHpp          CHARACTER VARYING := '';
  33.     vFilterShowHppSubTotal  CHARACTER VARYING := '';
  34.     vFilterGroup            CHARACTER VARYING := '';
  35.     vShowHpp                CHARACTER VARYING;
  36.     vEmptyId                BIGINT := -99;
  37.     vCount                  BIGINT:= 0;
  38.     vOuGrazia               BIGINT;
  39.      
  40. BEGIN
  41.  
  42.     SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, 'OU.GRAZIA.INDONESIA') AS BIGINT) INTO vOuGrazia;
  43.    
  44.     IF (pFlgVerified <> vEmptyValue) THEN
  45.         vFilterVerified := ' AND A.status_item = '''|| pFlgVerified || ''' ';
  46.     END IF;
  47.  
  48.     IF (pFlgUnused <> vEmptyValue) THEN
  49.         vFilterStatus := ' AND A.flg_unused = '''|| pFlgUnused || ''' ';
  50.     END IF;
  51.  
  52.     IF (pOutletId <> vEmptyId) THEN
  53.         vFilterOutlet := ' AND D.outlet_id = '|| pOutletId || '';
  54.     END IF;
  55.  
  56.     SELECT role_name INTO vShowHpp
  57.     FROM t_role A
  58.     INNER JOIN (
  59.         SELECT unnest(string_to_array(f_get_value_system_config_by_param_code(pTenantId, 'authorized.role.id'), ';')) AS authorized_id
  60.     ) B
  61.     ON A.role_id = CAST(B.authorized_id AS bigint)
  62.     WHERE role_id = pRoleId;
  63.    
  64.     IF vShowHpp IS NOT NULL THEN
  65.         vFilterShowHpp := ', COALESCE(E.purch_price, 0) AS hpp';
  66.         vFilterShowHppSubTotal := ', SUM(A.qty_verified* COALESCE(E.purch_price, 0)) AS sub_total_hpp';
  67.         vFilterGroup           := 'GROUP BY C.group_outlet_name, D.outlet_name, A.doc_no, A.doc_date, A.promo_code_verified,
  68.                                A.status_item, A.flg_unused,A.product_id , A.gross_sell_price,
  69.                                A.nett_sell_price_system, A.nett_sell_price, A.nett_sell_price_verified,
  70.                                A.qty, A.qty_verified, A.flg_denda, A.sub_total, a.denda, e.purch_price';
  71.         vCount         := 20;
  72.        
  73.         OPEN pRefHeader FOR
  74.             SELECT 20 AS _COUNT,
  75.                     'Dept Store', 'Outlet', 'Doc No',
  76.                     'Doc Date', 'Promo', 'Status',
  77.                     'Verified', 'Artikel', 'Harga Gross',
  78.                     'Harga Nett System', 'Harga Nett Diinput', 'Harga Nett Koreksi',
  79.                     'Qty Diinput', 'Qty Koreksi', 'Denda SPG',
  80.                     'Nilai Denda', 'Status', 'Sub Total', 'HPP', 'Sub Total Hpp',
  81.                     1 AS ordial;
  82.         RETURN NEXT pRefHeader;
  83.     ELSE
  84.         vCount         := 18;
  85.         OPEN pRefHeader FOR
  86.             SELECT 18 AS _COUNT,
  87.                     'Dept Store', 'Outlet', 'Doc No',
  88.                     'Doc Date', 'Promo', 'Status',
  89.                     'Verified', 'Artikel', 'Harga Gross',
  90.                     'Harga Nett System', 'Harga Nett Diinput', 'Harga Nett Koreksi',
  91.                     'Qty Diinput', 'Qty Koreksi', 'Denda SPG',
  92.                     'Nilai Denda', 'Status', 'Sub Total',
  93.                     1 AS ordial;
  94.         RETURN NEXT pRefHeader;
  95.     END IF;
  96.      
  97.     OPEN pRefDetail FOR
  98.         EXECUTE '
  99.             WITH TEMP AS (  
  100.                     SELECT A.doc_no, A.doc_date, A.gross_sell_price,
  101.                         A.nett_sell_price_system,  A.nett_sell_price, A.nett_sell_price_verified, A.qty, A.qty_verified, A.flg_unused,  
  102.                         A.nett_sell_price_verified * A.qty_verified AS sub_total, C.flg_denda, COALESCE(D.fine_amount,0) AS denda,
  103.                         A.outlet_id, A.promo_code_verified, A.status_item, A.product_id
  104.                     FROM i_trx_mobile_pos_item_balance A  
  105.                     INNER JOIN i_verification_trx_mobile_pos_item C  ON A.trx_mobile_pos_item_id = C.ref_item_id  
  106.                     LEFT JOIN i_spg_fine D  ON C.verification_trx_item_id = D.ref_item_id  
  107.                     UNION  
  108.                     SELECT A.doc_no, A.doc_date, A.gross_sell_price,
  109.                         A.nett_sell_price_system,  A.nett_sell_price, A.nett_sell_price_verified, A.qty, A.qty_verified, A.flg_unused,  
  110.                         A.nett_sell_price_verified * A.qty_verified AS sub_total, C.flg_denda, COALESCE(D.fine_amount,0) AS denda,
  111.                         A.outlet_id, A.promo_code_verified, A.status_item, A.product_id
  112.                     FROM i_trx_mobile_pos_item_balance A  
  113.                     INNER JOIN i_verification_trx_mobile_pos_item C  ON A.trx_mobile_pos_item_id = C.verification_trx_item_id  
  114.                         AND C.ref_item_id = -99  LEFT JOIN i_spg_fine D  ON C.verification_trx_item_id = D.ref_item_id  
  115.                     UNION  
  116.                     SELECT A.doc_no, A.doc_date, A.gross_sell_price,
  117.                         A.nett_sell_price_system,  A.nett_sell_price, A.nett_sell_price_verified, A.qty, A.qty_verified, A.flg_unused,  
  118.                         A.nett_sell_price_verified * A.qty_verified AS sub_total, $9 AS flg_denda, 0 AS denda,
  119.                         A.outlet_id, A.promo_code_verified, A.status_item, A.product_id
  120.                     FROM i_trx_mobile_pos_item_balance A  
  121.                     WHERE NOT EXISTS(  
  122.                         SELECT 1  FROM i_verification_trx_mobile_pos_item C  
  123.                         LEFT JOIN i_spg_fine D  ON C.verification_trx_item_id = D.ref_item_id  
  124.                         WHERE A.trx_mobile_pos_item_id = C.verification_trx_item_id  
  125.                             OR A.trx_mobile_pos_item_id = C.ref_item_id ))  
  126.                 SELECT '|| vCount ||' AS _COUNT,C.group_outlet_name, D.outlet_name, A.doc_no, A.doc_date, A.promo_code_verified,
  127.                     CASE WHEN A.status_item = $8 THEN $5
  128.                         WHEN A.status_item = $9 THEN $6
  129.                         WHEN A.status_item = $10 THEN $7 END status_item,
  130.                     CASE WHEN A.flg_unused = $9 THEN $11 ELSE $12 END flg_unused,
  131.                     f_get_product_code(A.product_id) AS product_code, A.gross_sell_price,
  132.                     A.nett_sell_price_system, A.nett_sell_price, A.nett_sell_price_verified, CAST(A.qty AS BIGINT) AS qty, CAST(A.qty_verified AS BIGINT) AS qty_verified ,
  133.                     CASE WHEN A.flg_denda = $8 THEN $13 ELSE $14 END flg_denda, A.denda, CASE WHEN A.flg_unused = $9 THEN $11 ELSE $12 END AS flg_unused2,
  134.                     A.sub_total '|| vFilterShowHpp || vFilterShowHppSubTotal || '
  135.                 FROM TEMP A
  136.                 INNER JOIN m_outlet_group B ON A.outlet_id = B.outlet_id
  137.                 INNER JOIN m_group_outlet C ON B.group_outlet_id = C.group_outlet_id
  138.                 INNER JOIN i_outlet D ON A.outlet_id = D.outlet_id
  139.                 LEFT JOIN m_purch_price_product E ON A.product_id = E.product_id
  140.                     AND E.ou_id = $15
  141.                     AND D.tenant_id = E.tenant_id
  142.                     AND A.doc_date BETWEEN E.date_from AND E.date_to
  143.                 WHERE  C.group_outlet_id = $1
  144.                        AND A.doc_date BETWEEN $3 AND $4'
  145.                        || vFilterVerified  || vFilterStatus || vFilterOutlet || vFilterGroup || '
  146.                 ORDER BY A.doc_no, A.doc_date ASC'
  147.         USING pGroupOutletId, pOutletId, pPeriodStart, pPeriodEnd, vVerified, vNotVerified, vInProgressVerified,
  148.                 vYes, vNo, vInProgress, vUse, vUnUsed, vFine, vNotFine, vOuGrazia;
  149.     RETURN NEXT pRefDetail;
  150.  
  151.    
  152. END
  153. $BODY$
  154.   LANGUAGE plpgsql VOLATILE
  155.   COST 100
  156.   ROWS 1000
  157. /
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!