Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- -- Function: r_outlet_top_selling_products_xlsx_no_group_by_periode(character varying, bigint, bigint, character varying, character varying, bigint, bigint, bigint, bigint, character varying)
- -- DROP FUNCTION r_outlet_top_selling_products_xlsx_no_group_by_periode(character varying, bigint, bigint, character varying, character varying, bigint, bigint, bigint, bigint, character varying);
- CREATE OR REPLACE FUNCTION r_outlet_top_selling_products_xlsx_no_group_by_periode(character varying, bigint, bigint, character varying, character varying, bigint, bigint, bigint, bigint, character varying)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pOuId ALIAS FOR $3;
- pDateFrom ALIAS FOR $4;
- pDateTo ALIAS FOR $5;
- pGroupProductOuId ALIAS FOR $6;
- pLimit ALIAS FOR $7;
- pUserId ALIAS FOR $8;
- pWarehouseId ALIAS FOR $9;
- pProductCodeName ALIAS FOR $10;
- vEmptyValue character varying(1);
- vFlgBu character varying(1);
- vFlgSubBu character varying(1);
- vFlgBranch character varying(1);
- vProductStatus character varying(10);
- vDatetime character varying(14);
- vGroupProductOuCode character varying(50) := 'All';
- vGroupProductOuName character varying(100) := 'All';
- vOuBuId bigint;
- vAllId bigint;
- vEmptyId bigint;
- BEGIN
- vEmptyValue := ' ';
- vProductStatus := 'GOOD';
- vAllId := -99;
- vEmptyId := -99;
- DELETE FROM tr_top_selling_products WHERE session_id = pSessionId;
- DELETE FROM tr_pos_by_periode WHERE session_id = pSessionId;
- DELETE FROM tt_in_latest_purchasing_price_by_date WHERE session_id = pSessionId;
- DELETE FROM tt_out_latest_purchasing_price_by_date WHERE session_id = pSessionId;
- SELECT A.flg_bu, A.flg_sub_bu, flg_branch INTO vFlgBu, vFlgSubBu, vFlgBranch
- FROM t_ou_type A
- INNER JOIN t_ou B ON A.ou_type_id = B.ou_type_id
- WHERE B.ou_id = pOuId;
- SELECT ou_bu_id INTO vOuBuId
- FROM m_ou_structure
- WHERE ou_id = pOuId;
- /*
- * ambil data yang tidak pakai promo
- */
- INSERT INTO tr_pos_by_periode
- (session_id, tenant_id, ou_id, doc_type_id, doc_no, doc_date,
- curr_code, line_no, ctgr_product_id, sub_ctgr_product_id, product_id,
- product_code, product_name, uom_id, uom_code, promo_code,
- qty, flg_tax_amount, tax_amount, nett_sell_price, nett_amount_item, gross_sell_price, discount_amount,
- group_product_ou_id, group_product_ou_code, group_product_ou_name,
- commision_ou_id, commision_type, commision_percentage, commision_curr_code, commision_amount)
- SELECT pSessionId, A.tenant_id, A.ou_id, A.doc_type_id, A.doc_no, A.doc_date,
- B.curr_code, B.line_no, C.ctgr_product_id, C.sub_ctgr_product_id, COALESCE(G.core_product_id, B.product_id),
- C.product_code, C.product_Name, B.base_uom_id, D.uom_code, B.promo_code,
- B.qty, B.flg_tax_amount, B.tax_amount, B.nett_sell_price, B.nett_amount_item, B.gross_sell_price, B.discount_amount,
- B.group_product_ou_id, E.group_product_ou_code, E.group_product_ou_name,
- F.commision_ou_id, F.commision_type, F.commision_percentage, F.curr_code, F.commision_amount
- FROM i_trx_pos A
- INNER JOIN i_trx_pos_item B ON A.trx_pos_id = B.trx_pos_id AND A.tenant_id = B.tenant_id AND A.process_no = B.process_no
- INNER JOIN m_product C ON B.product_id = C.product_id
- INNER JOIN m_uom D ON B.base_uom_id = D.uom_id
- INNER JOIN m_group_product_ou E ON B.group_product_ou_id = E.group_product_ou_id
- INNER JOIN m_commision_ou F ON B.tenant_id = F.tenant_id AND B.group_product_ou_id = F.group_product_ou_id
- LEFT OUTER JOIN m_ext_product G ON B.product_id = G.product_id
- WHERE A.tenant_id = pTenantId AND
- --A.ou_id = pOuId AND
- A.doc_date BETWEEN pDateFrom AND pDateTo AND
- A.doc_date BETWEEN F.date_from AND F.date_to AND
- F.flg_promo = 'N';
- UPDATE tr_pos_by_periode SET product_code = A.product_code, product_name = A.product_name
- FROM m_product A
- WHERE A.product_id = tr_pos_by_periode.product_id
- AND tr_pos_by_periode.session_id = pSessionId;
- UPDATE tr_pos_by_periode SET commision_amount = ROUND(nett_sell_price * commision_percentage / 100,0), commision_curr_code = curr_code
- WHERE commision_type = 'PCT' AND
- session_id = pSessionId;
- /*
- * transaksi POS yang di void, tidak akan dimunculkan
- * NK, 22 Mei 2013
- */
- DELETE FROM tr_pos_by_periode
- WHERE session_id = pSessionId AND
- EXISTS (SELECT 1 FROM i_trx_log_voided_pos A
- WHERE tr_pos_by_periode.tenant_id = A.tenant_id AND
- --tr_pos_by_periode.doc_type_id = A.doc_type_id AND
- tr_pos_by_periode.doc_no = A.doc_no AND
- tr_pos_by_periode.doc_date = A.doc_date AND
- tr_pos_by_periode.ou_id = A.ou_id);
- IF pOuId <> vAllId THEN
- -- OU BU
- IF vFlgBu = 'Y' AND vFlgSubBu = 'N' AND vFlgBranch = 'N' THEN
- DELETE FROM tr_pos_by_periode A
- WHERE A.session_id = pSessionId
- AND NOT EXISTS (
- SELECT 1 FROM m_ou_structure B
- WHERE A.ou_id = B.ou_id
- AND B.ou_bu_id = pOuId
- );
- ELSEIF vFlgBu = 'Y' AND vFlgSubBu = 'N' AND vFlgBranch = 'Y' THEN -- OU Branch
- DELETE FROM tr_pos_by_periode A
- WHERE A.session_id = pSessionId
- AND NOT EXISTS (
- SELECT 1 FROM m_ou_structure B
- WHERE A.ou_id = B.ou_id
- AND B.ou_branch_id = pOuId
- );
- ELSE
- DELETE FROM tr_pos_by_periode WHERE session_id = pSessionId AND ou_id <> pOuId;
- END IF;
- END IF;
- IF pGroupProductOuId <> vAllId THEN
- SELECT group_product_ou_code, group_product_ou_name INTO vGroupProductOuCode, vGroupProductOuName
- FROM m_group_product_ou
- WHERE group_product_ou_id = pGroupProductOuId;
- DELETE FROM tr_pos_by_periode WHERE session_id = pSessionId AND group_product_ou_id <> pGroupProductOuId;
- END IF;
- INSERT INTO tt_in_latest_purchasing_price_by_date(session_id, tenant_id, ou_bu_id, doc_date, product_id)
- SELECT pSessionId, pTenantId, B.ou_bu_id, A.doc_date, A.product_id
- FROM tr_pos_by_periode A
- INNER JOIN m_ou_structure B ON A.ou_id = B.ou_id
- WHERE A.tenant_id = pTenantId
- AND A.session_id = pSessionId
- GROUP BY B.ou_bu_id, A.doc_date, A.product_id;
- SELECT TO_CHAR(now(), 'YYYYMMDDHH24MISS') INTO vDatetime;
- --PERFORM pu_get_latest_purchasing_price_by_date(pSessionId, pTenantId, SUBSTRING(vDatetime, 1, 6), vOuBuId, vDatetime, pUserId);
- IF pOuId <> vAllId THEN
- PERFORM pu_get_latest_purchasing_price_by_date(pSessionId, pTenantId, SUBSTRING(vDatetime, 1, 6), vOuBuId, vDatetime, pUserId);
- ELSE
- PERFORM pu_get_latest_purchasing_price_by_date(pSessionId, pTenantId, SUBSTRING(vDatetime, 1, 6), B.ou_bu_id, vDatetime, pUserId)
- FROM t_ou A JOIN m_ou_structure B ON A.ou_id=B.ou_id where A.tenant_id=pTenantId;
- END IF;
- INSERT INTO tr_top_selling_products(
- session_id, tenant_id, ou_id,
- curr_code, group_product_ou_id, group_product_ou_code, group_product_ou_name,
- product_id, product_code, product_name,
- qty, uom_id, uom_code, uom_name,
- nett_amount, remaining_stock, profit
- )
- SELECT pSessionId, pTenantId, pOuId,
- A.curr_code, -99, ' ', ' ',
- A.product_id, A.product_code, A.product_name,
- SUM(A.qty) AS qty, A.uom_id, A.uom_code, C.uom_name,
- SUM(A.nett_amount_item) AS nett_amount, 0 AS remaining_stock, SUM(A.nett_amount_item - (A.qty * E.gl_purch_gross_price)) AS profit
- FROM tr_pos_by_periode A
- INNER JOIN m_uom C ON A.uom_id = C.uom_id
- INNER JOIN m_ou_structure D ON A.ou_id = D.ou_id
- INNER JOIN tt_out_latest_purchasing_price_by_date E ON E.session_id = A.session_id AND A.product_id = E.product_id AND A.doc_date = E.doc_date AND A.tenant_id = E.tenant_id AND D.ou_bu_id = E.ou_bu_id
- --LEFT OUTER JOIN in_product_balance_stock B ON A.tenant_id = B.tenant_id AND A.product_id = B.product_id AND B.warehouse_id = pWarehouseId AND B.product_status = vProductStatus
- WHERE A.session_id = pSessionId
- AND UPPER(A.product_code) LIKE '%' || UPPER(pProductCodeName) || '%' OR UPPER(A.product_name) LIKE '%' || UPPER(pProductCodeName) || '%'
- GROUP BY A.curr_code, A.product_id, A.product_code, A.product_name, A.uom_id, A.uom_code, C.uom_name
- ORDER BY qty DESC, nett_amount DESC;
- IF pOuId <> vAllId THEN
- IF vFlgBu = 'Y' AND vFlgSubBu = 'N' AND vFlgBranch = 'N' THEN
- UPDATE tr_top_selling_products
- SET remaining_stock = A.qty
- FROM (
- SELECT B.product_id, SUM(B.qty) AS qty
- FROM in_product_balance_stock B
- INNER JOIN m_warehouse C ON B.warehouse_id = C.warehouse_id
- INNER JOIN m_warehouse_ou D ON C.warehouse_id = D.warehouse_id
- INNER JOIN t_ou E ON D.ou_id = E.ou_id
- INNER JOIN t_ou_type F ON E.ou_type_id = F.ou_type_id
- INNER JOIN m_ou_structure G ON G.ou_id = E.ou_id
- WHERE B.tenant_id = pTenantId
- AND G.ou_bu_id = pOuId
- GROUP BY B.product_id
- ) A
- WHERE tr_top_selling_products.session_id = pSessionId
- AND tr_top_selling_products.product_id = A.product_id;
- ELSEIF vFlgBu = 'Y' AND vFlgSubBu = 'N' AND vFlgBranch = 'Y' THEN
- UPDATE tr_top_selling_products
- SET remaining_stock = A.qty
- FROM (
- SELECT B.product_id, SUM(B.qty) AS qty
- FROM in_product_balance_stock B
- INNER JOIN m_warehouse C ON B.warehouse_id = C.warehouse_id
- INNER JOIN m_warehouse_ou D ON C.warehouse_id = D.warehouse_id
- INNER JOIN t_ou E ON D.ou_id = E.ou_id
- INNER JOIN t_ou_type F ON E.ou_type_id = F.ou_type_id
- INNER JOIN m_ou_structure G ON G.ou_id = E.ou_id
- WHERE B.tenant_id = pTenantId
- AND G.ou_branch_id = pOuId
- GROUP BY B.product_id
- ) A
- WHERE tr_top_selling_products.session_id = pSessionId
- AND tr_top_selling_products.product_id = A.product_id;
- ELSE
- UPDATE tr_top_selling_products
- SET remaining_stock = A.qty
- FROM (
- SELECT B.product_id, SUM(B.qty) AS qty
- FROM in_product_balance_stock B
- WHERE B.tenant_id = pTenantId
- AND B.warehouse_id = pWarehouseId
- GROUP BY B.product_id
- ) A
- WHERE tr_top_selling_products.session_id = pSessionId
- AND tr_top_selling_products.product_id = A.product_id;
- END IF;
- ELSE
- UPDATE tr_top_selling_products
- SET remaining_stock = A.qty
- FROM (
- SELECT B.product_id, SUM(B.qty) AS qty
- FROM in_product_balance_stock B
- INNER JOIN m_warehouse C ON B.warehouse_id = C.warehouse_id
- INNER JOIN m_warehouse_ou D ON C.warehouse_id = D.warehouse_id
- INNER JOIN t_ou E ON D.ou_id = E.ou_id
- INNER JOIN t_ou_type F ON E.ou_type_id = F.ou_type_id
- WHERE B.tenant_id = pTenantId
- AND F.flg_bu = 'Y' AND F.flg_sub_bu = 'Y'
- GROUP BY B.product_id
- ) A
- WHERE tr_top_selling_products.session_id = pSessionId
- AND tr_top_selling_products.product_id = A.product_id;
- END IF;
- Open pRefHeader FOR
- SELECT fullName AS full_name, pDateFrom AS date_from, pDateTo AS date_to
- FROM t_user
- WHERE user_id = pUserId;
- RETURN NEXT pRefHeader;
- IF pLimit <> vAllId THEN
- Open pRefDetail FOR
- SELECT A.curr_code AS curr_code, vGroupProductOuCode AS dept_code,
- vGroupProductOuName AS dept_name,
- A.product_code AS product_code, A.product_name AS product_name,
- SUM(A.qty) AS qty, A.uom_name AS uom_code,
- SUM(A.nett_amount) AS nett_amount, SUM(A.remaining_stock) AS remaining_stock,
- SUM(A.profit) AS profit
- FROM tr_top_selling_products A
- WHERE A.session_id = pSessionId
- GROUP BY A.curr_code,
- A.product_code, A.product_name, A.uom_name
- ORDER BY qty DESC, nett_amount DESC
- LIMIT pLimit;
- ELSE
- Open pRefDetail FOR
- SELECT A.curr_code AS curr_code, vGroupProductOuCode AS dept_code,
- vGroupProductOuName AS dept_name,
- A.product_code AS product_code, A.product_name AS product_name,
- SUM(A.qty) AS qty, A.uom_name AS uom_code,
- SUM(A.nett_amount) AS nett_amount, SUM(A.remaining_stock) AS remaining_stock,
- SUM(A.profit) AS profit
- FROM tr_top_selling_products A
- WHERE A.session_id = pSessionId
- GROUP BY A.curr_code,
- A.product_code, A.product_name, A.uom_name
- ORDER BY qty DESC, nett_amount DESC;
- END IF;
- RETURN NEXT pRefDetail;
- DELETE FROM tr_pos_by_periode WHERE session_id = pSessionId;
- DELETE FROM tr_top_selling_products WHERE session_id = pSessionId;
- DELETE FROM tt_in_latest_purchasing_price_by_date WHERE session_id = pSessionId;
- DELETE FROM tt_out_latest_purchasing_price_by_date WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- ROWS 1000;
- ALTER FUNCTION r_outlet_top_selling_products_xlsx_no_group_by_periode(character varying, bigint, bigint, character varying, character varying, bigint, bigint, bigint, bigint, character varying)
- OWNER TO sts;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement