Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION r_report_inquiry_sales_outlet_csv(CHARACTER VARYING, BIGINT, BIGINT, BIGINT, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, CHARACTER VARYING, BIGINT)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pGroupOutletId ALIAS FOR $3;
- pOutletId ALIAS FOR $4;
- pPeriodStart ALIAS FOR $5;
- pPeriodEnd ALIAS FOR $6;
- pFlgVerified ALIAS FOR $7;
- pFlgUnused ALIAS FOR $8;
- pRoleId ALIAS FOR $9;
- vVerified CHARACTER VARYING := 'Verified';
- vNotVerified CHARACTER VARYING := 'Not Verified';
- vInProgressVerified CHARACTER VARYING := 'In Progress';
- vYes CHARACTER VARYING := 'Y';
- vNo CHARACTER VARYING := 'N';
- vInProgress CHARACTER VARYING := 'I';
- vUse CHARACTER VARYING := 'Dipakai';
- vUnUsed CHARACTER VARYING := 'Tidak Dipakai';
- vFine CHARACTER VARYING := 'Denda';
- vNotFine CHARACTER VARYING := 'Tidak Denda';
- vEmptyValue CHARACTER VARYING := '';
- vFilterVerified CHARACTER VARYING := '';
- vFilterStatus CHARACTER VARYING := '';
- vFilterOutlet CHARACTER VARYING := '';
- vFilterShowHpp CHARACTER VARYING := '';
- vFilterShowHppSubTotal CHARACTER VARYING := '';
- vFilterGroup CHARACTER VARYING := '';
- vShowHpp CHARACTER VARYING;
- vEmptyId BIGINT := -99;
- vCount BIGINT:= 0;
- vOuGrazia BIGINT;
- BEGIN
- SELECT CAST(f_get_value_system_config_by_param_code(pTenantId, 'OU.GRAZIA.INDONESIA') AS BIGINT) INTO vOuGrazia;
- IF (pFlgVerified <> vEmptyValue) THEN
- vFilterVerified := ' AND A.status_item = '''|| pFlgVerified || ''' ';
- END IF;
- IF (pFlgUnused <> vEmptyValue) THEN
- vFilterStatus := ' AND A.flg_unused = '''|| pFlgUnused || ''' ';
- END IF;
- IF (pOutletId <> vEmptyId) THEN
- vFilterOutlet := ' AND D.outlet_id = '|| pOutletId || '';
- END IF;
- SELECT role_name INTO vShowHpp
- FROM t_role A
- INNER JOIN (
- SELECT unnest(string_to_array(f_get_value_system_config_by_param_code(pTenantId, 'authorized.role.id'), ';')) AS authorized_id
- ) B
- ON A.role_id = CAST(B.authorized_id AS bigint)
- WHERE role_id = pRoleId;
- IF vShowHpp IS NOT NULL THEN
- vFilterShowHpp := ', COALESCE(E.purch_price, 0) AS hpp';
- vFilterShowHppSubTotal := ', SUM(A.qty_verified* COALESCE(E.purch_price, 0)) AS sub_total_hpp';
- vFilterGroup := 'GROUP BY C.group_outlet_name, D.outlet_name, A.doc_no, A.doc_date, A.promo_code_verified,
- A.status_item, A.flg_unused,A.product_code, A.gross_sell_price,
- A.nett_sell_price_system, A.nett_sell_price, A.nett_sell_price_verified,
- A.qty, A.qty_verified, A.sub_total, A.flg_denda, e.purch_price, A.trx_mobile_pos_item_balance_id';
- vCount := 20;
- OPEN pRefHeader FOR
- SELECT 20 AS _COUNT,
- 'Dept Store', 'Outlet', 'Doc No',
- 'Doc Date', 'Promo', 'Status',
- 'Verified', 'Artikel', 'Harga Gross',
- 'Harga Nett System', 'Harga Nett Diinput', 'Harga Nett Koreksi',
- 'Qty Diinput', 'Qty Koreksi', 'Denda SPG',
- 'Nilai Denda', 'Status', 'Sub Total', 'HPP', 'Sub Total Hpp',
- 1 AS ordial;
- RETURN NEXT pRefHeader;
- ELSE
- vCount := 18;
- OPEN pRefHeader FOR
- SELECT 18 AS _COUNT,
- 'Dept Store', 'Outlet', 'Doc No',
- 'Doc Date', 'Promo', 'Status',
- 'Verified', 'Artikel', 'Harga Gross',
- 'Harga Nett System', 'Harga Nett Diinput', 'Harga Nett Koreksi',
- 'Qty Diinput', 'Qty Koreksi', 'Denda SPG',
- 'Nilai Denda', 'Status', 'Sub Total',
- 1 AS ordial;
- RETURN NEXT pRefHeader;
- END IF;
- OPEN pRefDetail FOR
- EXECUTE '
- WITH TEMP AS (
- SELECT A.doc_no, A.doc_date, B.product_code, A.gross_sell_price,
- A.nett_sell_price_system, A.nett_sell_price, A.nett_sell_price_verified, A.qty, A.qty_verified, A.flg_unused,
- A.nett_sell_price_verified * A.qty_verified AS sub_total, A.flg_denda,
- A.outlet_id, A.promo_code_verified, A.status_item, B.product_id, A.trx_mobile_pos_item_balance_id
- FROM i_trx_mobile_pos_item_balance A
- INNER JOIN m_product B ON A.product_id = B.product_id
- INNER JOIN i_verification_trx_mobile_pos_item C ON A.trx_mobile_pos_item_id = C.verification_trx_item_id
- AND C.ref_item_id = -99
- UNION
- SELECT A.doc_no, A.doc_date, B.product_code, A.gross_sell_price,
- A.nett_sell_price_system, A.nett_sell_price, A.nett_sell_price_verified, A.qty, A.qty_verified, A.flg_unused,
- A.nett_sell_price_verified * A.qty_verified AS sub_total, A.flg_denda,
- A.outlet_id, A.promo_code_verified, A.status_item, B.product_id, A.trx_mobile_pos_item_balance_id
- FROM i_trx_mobile_pos_item_balance A
- INNER JOIN m_product B ON A.product_id = B.product_id
- WHERE NOT EXISTS(
- SELECT 1 FROM i_verification_trx_mobile_pos_item C
- WHERE A.trx_mobile_pos_item_id = C.verification_trx_item_id)
- )
- SELECT '|| vCount ||' AS _COUNT,C.group_outlet_name, D.outlet_name, A.doc_no, A.doc_date, A.promo_code_verified,
- CASE WHEN A.status_item = $8 THEN $5
- WHEN A.status_item = $9 THEN $6
- WHEN A.status_item = $10 THEN $7 END status_item,
- CASE WHEN A.flg_unused = $9 THEN $11 ELSE $12 END flg_unused,
- A.product_code, A.gross_sell_price,
- 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 ,
- CASE WHEN A.flg_denda = $8 THEN $13 ELSE $14 END flg_denda, f_get_fine_amount_spg(A.trx_mobile_pos_item_balance_id) AS denda, CASE WHEN A.flg_unused = $9 THEN $11 ELSE $12 END AS flg_unused2,
- A.sub_total '|| vFilterShowHpp || vFilterShowHppSubTotal || '
- FROM TEMP A
- INNER JOIN m_outlet_group B ON A.outlet_id = B.outlet_id
- INNER JOIN m_group_outlet C ON B.group_outlet_id = C.group_outlet_id
- INNER JOIN i_outlet D ON A.outlet_id = D.outlet_id
- LEFT JOIN m_purch_price_product E ON A.product_id = E.product_id
- AND E.ou_id = $15
- AND D.tenant_id = E.tenant_id
- AND A.doc_date BETWEEN E.date_from AND E.date_to
- WHERE C.group_outlet_id = $1
- AND A.doc_date BETWEEN $3 AND $4'
- || vFilterVerified || vFilterStatus || vFilterOutlet || vFilterGroup || '
- ORDER BY A.doc_no, A.doc_date ASC'
- USING pGroupOutletId, pOutletId, pPeriodStart, pPeriodEnd, vVerified, vNotVerified, vInProgressVerified,
- vYes, vNo, vInProgress, vUse, vUnUsed, vFine, vNotFine, vOuGrazia;
- RETURN NEXT pRefDetail;
- END
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- ROWS 1000
- /
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement