Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- DROP FUNCTION IF EXISTS r_purch_invoice_list(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, bigint, bigint, bigint, character varying, character varying);
- CREATE OR REPLACE FUNCTION r_purch_invoice_list(character varying, bigint, bigint, bigint, character varying, bigint, bigint, bigint, bigint, bigint, bigint, character varying, character varying)
- RETURNS SETOF refcursor AS
- $BODY$
- DECLARE
- pRefHeader REFCURSOR := 'refHeader';
- pRefDetail REFCURSOR := 'refDetail';
- pSessionId ALIAS FOR $1;
- pTenantId ALIAS FOR $2;
- pUserId ALIAS FOR $3;
- pRoleId ALIAS FOR $4;
- pDatetime ALIAS FOR $5;
- pOuId ALIAS FOR $6;
- pPartnerId ALIAS FOR $7;
- pCtgrPartnerId ALIAS FOR $8;
- pCtgrProductId ALIAS FOR $9;
- pSubCtgrProductId ALIAS FOR $10;
- pBrandId ALIAS FOR $11;
- pDateFrom ALIAS FOR $12;
- pDateTo ALIAS FOR $13;
- vEmptyId bigint := -99;
- vReceiveGoodsDocTypeId bigint := -99;
- vFilterPartner text := '';
- vFilterCtgrPartner text := '';
- vFilterCtgrProduct text := '';
- vFilterSubCtgrProduct text := '';
- vFilterBrand text := '';
- vEmptyValue character varying(1) := '';
- vRoundingModeNonTax character varying(5) := '';
- vStatusDocRelease character varying := 'R';
- vAlocTypeAmount character varying := 'AMOUNT';
- vActivityCodeAddAmount character varying := 'PURCHASING_DISC';
- vZeroValue numeric := 0;
- vRoundingDigit integer;
- BEGIN
- DELETE FROM tt_invoice_item_with_prorate_amount WHERE session_id = pSessionId;
- vReceiveGoodsDocTypeId := 111;
- SELECT f_get_value_system_config_by_param_code(pTenantId, 'rounding.mode.non.tax') INTO vRoundingModeNonTax;
- IF (pPartnerId <> vEmptyId) THEN
- vFilterPartner := ' AND B.partner_id = ' || pPartnerId;
- END IF;
- IF (pCtgrPartnerId <> vEmptyId) THEN
- vFilterCtgrPartner := ' AND M.ctgr_partner_id = ' || pCtgrPartnerId;
- END IF;
- IF (pCtgrProductId <> vEmptyId) THEN
- vFilterCtgrProduct := ' AND I.ctgr_product_id = ' || pCtgrProductId;
- END IF;
- IF (pSubCtgrProductId <> vEmptyId) THEN
- vFilterSubCtgrProduct := ' AND J.sub_ctgr_product_id = ' || pSubCtgrProductId;
- END IF;
- IF (pBrandId <> vEmptyId) THEN
- vFilterBrand := ' AND O.brand_id = ' || pBrandId;
- END IF;
- vRoundingDigit := CAST(f_get_value_system_config_by_param_code(pTenantId,'rounding.gl.amount') AS integer);
- Open pRefHeader FOR
- SELECT pPartnerId AS partner_id, CASE WHEN pPartnerId = vEmptyId THEN '(All)' ELSE f_get_partner_name(pPartnerId) END AS partner_name,
- pCtgrPartnerId AS ctgr_partner_id, pCtgrProductId AS ctgr_product_id, pSubCtgrProductId AS sub_ctgr_product_id,
- CASE WHEN pCtgrPartnerId = vEmptyId THEN '(All)' ELSE f_get_ctgr_partner_name(pCtgrPartnerId) END AS ctgr_partner_name,
- CASE WHEN pCtgrProductId = vEmptyId THEN '(All)' ELSE f_get_ctgr_product_name(pCtgrProductId) END AS ctgr_product_name,
- CASE WHEN pSubCtgrProductId = vEmptyId THEN '(All)' ELSE f_get_sub_ctgr_product_name(pSubCtgrProductId) END AS sub_ctgr_product_name,
- pBrandId AS brand_id, CASE WHEN pBrandId = vEmptyId THEN '(All)' ELSE f_get_brand_name(pBrandId) END AS brand_name,
- pDateFrom AS date_from, pDateTo AS date_to, pDatetime AS print_datetime, pOuId AS ou_id, f_get_ou_name(pOuId) AS ou_name;
- RETURN NEXT pRefHeader;
- INSERT INTO tt_invoice_item_with_prorate_amount (
- session_id, invoice_id, invoice_item_id, item_amount, qty, add_amount, alloc_type,
- curr_code, flg_tax_amount, tax_percentage,
- prorate_amount
- )
- SELECT pSessionId, C.invoice_id, C.invoice_item_id, E.item_amount, E.qty_rcv_po, BX.add_amount, B.alloc_type,
- F.curr_code, F.flg_tax_amount, F.tax_percentage,
- CASE WHEN B.alloc_type = vAlocTypeAmount
- THEN ROUND((E.item_amount / SUM(E.item_amount) OVER (PARTITION BY A.invoice_id)) * BX.add_amount, vRoundingDigit)
- ELSE ROUND((E.qty_rcv_po / SUM(E.qty_rcv_po) OVER (PARTITION BY A.invoice_id)) * BX.add_amount, vRoundingDigit)
- END prorate_amount
- FROM pu_invoice A
- INNER JOIN pu_invoice_additional_for_dlg B ON A.invoice_id = B.invoice_id
- INNER JOIN pu_invoice_cost BX ON A.invoice_id = BX.invoice_id
- INNER JOIN m_activity_gl BY ON BX.activity_gl_id = BY.activity_gl_id AND BY.activity_gl_code = vActivityCodeAddAmount
- INNER JOIN pu_invoice_item C ON B.invoice_id = C.invoice_id
- INNER JOIN pu_receive_goods_item D ON C.ref_item_id = D.receive_goods_item_id
- INNER JOIN pu_po_balance_invoice E ON D.tenant_id = E.tenant_id
- AND A.ref_id = E.po_id
- AND C.ref_doc_type_id = E.ref_doc_type_id
- AND D.receive_goods_id = E.ref_id
- AND D.receive_goods_item_id = E.ref_item_id
- INNER JOIN pu_po_item F ON D.ref_id = F.po_item_id
- WHERE B.add_disc_amount <> vZeroValue
- AND E.item_amount > vZeroValue
- AND A.tenant_id = pTenantId
- AND A.ou_id = pOuId
- AND A.doc_date BETWEEN pDateFrom AND pDateTo
- AND A.status_doc = vStatusDocRelease;
- WITH perhitungan_selisih AS (
- SELECT SUM(A.prorate_amount) OVER (PARTITION BY A.invoice_id) - A.add_amount AS selisih,
- row_number() OVER(PARTITION BY A.invoice_id ORDER BY A.invoice_id, A.prorate_amount DESC) AS line_no,
- A.session_id, A.invoice_id, A.invoice_item_id, A.item_amount, A.qty, A.alloc_type, A.prorate_amount
- FROM tt_invoice_item_with_prorate_amount A
- WHERE A.session_id = pSessionId
- ORDER BY A.invoice_id, A.prorate_amount DESC
- ), prorate_amount_tambah_selisih AS (
- SELECT CASE WHEN A.line_no = 1
- THEN A.prorate_amount - A.selisih
- ELSE A.prorate_amount
- END prorate_amount,
- A.session_id, A.invoice_id, A.invoice_item_id, A.item_amount, A.qty, A.alloc_type
- FROM perhitungan_selisih A
- )
- UPDATE tt_invoice_item_with_prorate_amount A SET
- prorate_amount = B.prorate_amount,
- tax_amount = f_tax_rounding(
- pTenantId,
- f_get_amount_before_tax(
- B.item_amount+B.prorate_amount,
- A.flg_tax_amount,
- A.tax_percentage,
- f_get_digit_decimal_doc_curr(vReceiveGoodsDocTypeId, A.curr_code),
- vRoundingModeNonTax),
- A.tax_percentage)
- FROM prorate_amount_tambah_selisih B
- WHERE A.session_id = B.session_id
- AND A.invoice_id = B.invoice_id
- AND A.invoice_item_id = B.invoice_item_id
- AND A.session_id = pSessionId;
- Open pRefDetail FOR
- EXECUTE '
- SELECT A.ou_id, B.partner_id, D.invoice_item_id, I.ctgr_product_id,
- J.sub_ctgr_product_id, A.curr_code, B.partner_name, A.doc_no, A.doc_date,
- A.ext_doc_no AS ref_no, A.ext_doc_date AS ref_date, C.doc_no AS po_no, C.doc_date AS po_date, G.product_code, G.product_name,
- SUM(E.qty_rcv_po) AS qty_po, H.uom_code AS uom_po, F.nett_price_po AS price,
- CASE WHEN COALESCE(XX.invoice_id, -99) <> -99
- THEN XX.item_amount + XX.prorate_amount
- ELSE SUM(Q.item_amount)
- END AS item_amount,
- CASE WHEN COALESCE(XX.invoice_id, -99) <> -99
- THEN XX.tax_amount
- ELSE SUM(COALESCE(R.tax_amount, 0))
- END AS tax_amount,
- A.invoice_id,
- ROUND((CASE WHEN (P.flg_discount = ''A'') THEN
- (CASE WHEN (P.gross_price_before_disc = 0) THEN 0
- ELSE (F.discount_amount-COALESCE(XX.prorate_amount,0)*100/P.gross_price_before_disc) END)
- ELSE F.discount_percentage END), 2) AS disc_percentage,
- COALESCE(S.tax_no, $7) AS no_faktur, COALESCE(S.tax_date, $7) AS tgl_faktur
- FROM pu_invoice A
- INNER JOIN m_partner B ON A.partner_id = B.partner_id
- INNER JOIN pu_po C ON A.ref_id = C.po_id
- INNER JOIN pu_invoice_item D ON A.invoice_id = D.invoice_id
- INNER JOIN pu_receive_goods_item E ON D.ref_item_id = E.receive_goods_item_id
- INNER JOIN pu_po_item F ON E.ref_id = F.po_item_id
- INNER JOIN pu_po_item_additional_for_dlg P ON P.po_item_id = F.po_item_id
- INNER JOIN m_product G ON F.product_id = G.product_id
- INNER JOIN m_uom H ON F.po_uom_id = H.uom_id
- INNER JOIN m_ctgr_product I ON G.ctgr_product_id = I.ctgr_product_id
- INNER JOIN m_sub_ctgr_product J ON G.sub_ctgr_product_id = J.sub_ctgr_product_id
- INNER JOIN t_user_role K ON K.user_id = $1 AND K.role_id = $2
- INNER JOIN m_policy_product L ON J.sub_ctgr_product_id = L.sub_ctgr_product_id AND K.user_role_id = L.user_role_id
- INNER JOIN m_ctgr_partner M ON B.ctgr_partner_id = M.ctgr_partner_id
- INNER JOIN m_policy_partner N ON M.ctgr_partner_id = N.ctgr_partner_id AND K.user_role_id = N.user_role_id
- INNER JOIN m_brand O ON G.brand_id = O.brand_id
- INNER JOIN pu_po_balance_invoice Q
- ON A.tenant_id = Q.tenant_id AND A.ou_id = Q.ou_id
- AND C.po_id = Q.po_id AND D.ref_doc_type_id = Q.ref_doc_type_id
- AND E.receive_goods_id = Q.ref_id AND E.receive_goods_item_id = Q.ref_item_id
- LEFT JOIN pu_po_balance_invoice_tax R
- ON Q.tenant_id = R.tenant_id AND Q.ou_id = R.ou_id
- AND Q.po_id = R.po_id AND Q.ref_doc_type_id = R.ref_doc_type_id
- AND Q.ref_id = R.ref_id AND Q.ref_item_id = R.ref_item_id AND F.tax_id = R.tax_id
- LEFT JOIN pu_invoice_tax S ON A.invoice_id = S.invoice_id
- LEFT JOIN tt_invoice_item_with_prorate_amount xx ON A.invoice_id = xx.invoice_id AND D.invoice_item_id = xx.invoice_item_id AND xx.session_id = $9
- WHERE A.tenant_id = $3
- AND A.ou_id = $4
- AND A.doc_date BETWEEN $5 AND $6
- AND A.status_doc = $8 ' ||
- vFilterPartner ||
- vFilterCtgrPartner ||
- vFilterCtgrProduct ||
- vFilterSubCtgrProduct ||
- vFilterBrand ||'
- GROUP BY A.ou_id, B.partner_id, D.invoice_item_id, I.ctgr_product_id,
- J.sub_ctgr_product_id, A.curr_code, B.partner_name, A.doc_no, A.doc_date,
- A.ext_doc_no, A.ext_doc_date, C.doc_no, C.doc_date, G.product_code, G.product_name,
- H.uom_code, F.nett_price_po, A.invoice_id, F.line_no,
- P.flg_discount, P.gross_price_before_disc, F.discount_amount, F.discount_percentage,
- S.tax_no, S.tax_date, xx.prorate_amount, XX.item_amount, XX.invoice_id, XX.tax_amount
- ORDER BY F.line_no, A.curr_code, B.partner_name, A.invoice_id, A.doc_no'
- USING pUserId, pRoleId, pTenantId, pOuId, pDateFrom, pDateTo, vEmptyValue, vStatusDocRelease, pSessionId;
- RETURN NEXT pRefDetail ;
- DELETE FROM tt_invoice_item_with_prorate_amount WHERE session_id = pSessionId;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- ROWS 1000;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement