CREATE OR REPLACE FUNCTION r_sales_csv(BIGINT, BIGINT, CHARACTER VARYING, CHARACTER VARYING) RETURNS SETOF refcursor AS $BODY$ DECLARE pRefHeader REFCURSOR := 'refHeader'; pRefDetail REFCURSOR := 'refDetail'; pTenantId ALIAS FOR $1; pReportMessageId ALIAS FOR $2; pSessionId ALIAS FOR $3; pProcessNo ALIAS FOR $4; vDocTypeIdDo BIGINT := 311; vDocTypeIdRn BIGINT := 502; vEmptyId BIGINT := -99; vNo CHARACTER VARYING := 'N'; vYes CHARACTER VARYING := 'Y'; vPromoEvent CHARACTER VARYING := 'PROMOEVENT'; vEmptyString CHARACTER VARYING := ''; vOuId BIGINT; vFlgIncludeAllBranch CHARACTER VARYING; vYearMonth CHARACTER VARYING; vSalesmanCodeName CHARACTER VARYING; vCustomerCodeName CHARACTER VARYING; vCtgrProduct CHARACTER VARYING; vSubCtgrProduct CHARACTER VARYING; vFilterOU CHARACTER VARYING :=''; vFilterSalesmanCodeName CHARACTER VARYING :=''; vFilterCustomerCodeName CHARACTER VARYING :=''; vFilterCtgrProduct CHARACTER VARYING :=''; vFilterSubCtgrProduct CHARACTER VARYING :=''; BEGIN DELETE FROM tt_report_sales WHERE session_id = pSessionId; /** * Mendapatkan parameter yang dikirim dari UI */ vOuId := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'ouId') AS BIGINT); vFlgIncludeAllBranch := f_get_report_parameter_value_by_id(pReportMessageId, 'flgIncludeAllBranch'); vYearMonth := f_get_report_parameter_value_by_id(pReportMessageId, 'yearMonth'); vSalesmanCodeName := f_get_report_parameter_value_by_id(pReportMessageId, 'salesmanCodeName'); vCustomerCodeName := f_get_report_parameter_value_by_id(pReportMessageId, 'customerCodeName'); vCtgrProduct := f_get_report_parameter_value_by_id(pReportMessageId, 'ctgrProduct'); vSubCtgrProduct := f_get_report_parameter_value_by_id(pReportMessageId, 'subCtgrProduct'); /** * Menbuat filter */ IF (vFlgIncludeAllBranch!=vEmptyString AND vFlgIncludeAllBranch=vYes) THEN vFilterOU := ' AND AY.ou_bu_id = '||vOuId; ELSE vFilterOU := ' AND A.ou_id = '||vOuId; END IF; IF (vSalesmanCodeName!=vEmptyString) THEN vFilterSalesmanCodeName := ' AND (UPPER(f_get_partner_code(A.salesman_id)) LIKE UPPER(''%'||vSalesmanCodeName||'%'') OR UPPER(f_get_partner_name(A.salesman_id)) LIKE UPPER(''%'||vSalesmanCodeName||'%'')) '; END IF; IF (vCustomerCodeName!=vEmptyString) THEN vFilterCustomerCodeName := ' AND (UPPER(f_get_partner_code(A.partner_id)) LIKE UPPER(''%'||vCustomerCodeName||'%'') OR UPPER(f_get_partner_name(A.partner_id)) LIKE UPPER(''%'||vCustomerCodeName||'%'')) '; END IF; IF (vCtgrProduct!=vEmptyString) THEN vFilterCtgrProduct := ' AND UPPER(f_get_ctgr_product_name(B.ctgr_product_id)) LIKE UPPER(''%'||vCtgrProduct||'%'') '; END IF; IF (vSubCtgrProduct!=vEmptyString) THEN vSubCtgrProduct := ' AND UPPER(f_get_sub_ctgr_product_name(B.sub_ctgr_product_id)) LIKE UPPER(''%'||vSubCtgrProduct||'%'') '; END IF; /** * HEADER CSV */ Open pRefHeader FOR SELECT 15 AS _COUNT,'Year Month', 'OU', 'Customer code', 'Customer name', 'Salesman code', 'Salesman name', 'Promo code', 'Category product', 'Sub category product', 'Product code', 'Product name', 'Qty', 'Currency', 'Amount', 'Discount amount', 'Nett amount',1 AS ordinal ORDER BY ordinal; RETURN NEXT pRefHeader; /** * PREPARE DATA */ -- DATA PENJUALAN YANG SUDAH DO/DOR/INVOICE EXECUTE ' INSERT INTO tt_report_sales( session_id, ou_id, partner_id, salesman_id, promo_code, product_id, qty, curr_code, amount, discount_amount, nett_amount) SELECT $1, A.ou_id, A.partner_id, B.salesman_id, COALESCE(E.promo_code, $2) AS promo_code, D.product_id, A.qty_dlv_so, A.curr_code, A.item_amount, (A.regular_disc_amount + A.promo_disc_amount + A.adj_regular_disc_amount + A.adj_promo_disc_amount) AS discount_amount, A.item_amount - (A.regular_disc_amount + A.promo_disc_amount + A.adj_regular_disc_amount + A.adj_promo_disc_amount) AS nett_amount FROM sl_so_balance_invoice A INNER JOIN dt_date AX ON A.ref_doc_date = AX.string_date INNER JOIN m_ou_structure AY ON A.ou_id = AY.ou_id INNER JOIN sl_so B ON A.so_id = B.so_id INNER JOIN sl_so_additional_for_dlg C ON B.so_id = C.so_id INNER JOIN sl_do_item D ON A.ref_item_id = D.do_item_id LEFT JOIN m_promo_sales E ON C.promo_sales_id = E.promo_sales_id WHERE A.ref_doc_type_id = $3 '|| vFilterOU || ' AND AX.year_month_date = $4 ' USING pSessionId, vEmptyString, vDocTypeIdDo, vYearMonth; -- DATA PENJUALAN DENGAN RETURN NOTE EXECUTE ' INSERT INTO tt_report_sales( session_id, ou_id, partner_id, salesman_id, promo_code, product_id, qty, curr_code, amount, discount_amount, nett_amount) SELECT $1, A.ou_id, A.partner_id, COALESCE(E.salesman_id, $2) AS salesman_id, COALESCE(G.promo_code, $3) AS promo_code, B.product_id, (-1*A.qty_dlv_so) qty_dlv_so, A.curr_code, (-1*A.item_amount) item_amount, 0 discount_amount, (-1*A.item_amount) AS nett_amount FROM sl_so_balance_invoice A INNER JOIN dt_date AX ON A.ref_doc_date = AX.string_date INNER JOIN m_ou_structure AY ON A.ou_id = AY.ou_id INNER JOIN sl_request_return_sales_item B ON A.ref_item_id = B.request_return_sales_item_id LEFT JOIN sl_do_item C ON B.ref_id = C.do_item_id LEFT JOIN sl_do D ON C.do_id = D.do_id LEFT JOIN sl_so E ON D.ref_id = E.so_id LEFT JOIN sl_so_additional_for_dlg F ON E.so_id = F.so_id LEFT JOIN m_promo_sales G ON F.promo_sales_id = G.promo_sales_id WHERE A.ref_doc_type_id = $4 '|| vFilterOU || ' AND AX.year_month_date = $5 ' USING pSessionId, vEmptyId, vEmptyString, vDocTypeIdRn, vYearMonth; Open pRefDetail FOR EXECUTE ' SELECT $1 AS year_month, f_get_ou_name(A.ou_id) AS ou, f_get_partner_code(A.partner_id) AS customer_code, f_get_partner_name(A.partner_id) AS customer_name, f_get_partner_code(A.salesman_id) AS salesman_code, f_get_partner_name(A.salesman_id) AS salesman_name, A.promo_code, f_get_ctgr_product_name(B.ctgr_product_id) AS category_product, f_get_sub_ctgr_product_name(B.sub_ctgr_product_id) AS sub_category_product, B.product_code, B.product_name, ROUND(SUM(A.qty), f_get_digit_decimal_doc_curr($2, A.curr_code)) AS qty, A.curr_code, ROUND(SUM(A.amount), f_get_digit_decimal_doc_curr($2, A.curr_code)) AS amount, ROUND(SUM(A.discount_amount), f_get_digit_decimal_doc_curr($2, A.curr_code)) AS discount_amount, ROUND(SUM(A.nett_amount), f_get_digit_decimal_doc_curr($2, A.curr_code)) AS nett_amount FROM tt_report_sales A INNER JOIN m_product B ON A.product_id = B.product_id WHERE A.session_id = $3 '|| vFilterSalesmanCodeName || vFilterCustomerCodeName || vFilterCtgrProduct || vFilterSubCtgrProduct ||' GROUP BY A.ou_id, A.partner_id, A.salesman_id, A.promo_code, A.curr_code, B.ctgr_product_id, B.sub_ctgr_product_id, B.product_code, B.product_name ' USING vYearMonth, vDocTypeIdDo, pSessionId; RETURN NEXT pRefDetail; DELETE FROM tt_report_sales WHERE session_id = pSessionId; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100;