Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE OR REPLACE FUNCTION r_print_purchase_order(character varying, bigint, bigint, bigint, character varying, bigint, character varying, 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;
- pPoId ALIAS FOR $6;
- pApprovalName ALIAS FOR $7;
- pPreparedBy ALIAS FOR $8;
- pSupplierCp ALIAS FOR $9;
- vReportName character varying;
- vSubTotal numeric;
- vPpn numeric;
- vTotal numeric;
- vTotalQty numeric;
- vEmptyId bigint;
- vKeySignApprovalName character varying;
- vKeySignPreparedBy character varying;
- vOuBuInfoReport OU_INFO_REPORT%ROWTYPE;
- result record;
- vTermOfPaymentCode character varying := ' ';
- vTermOfPayment character varying := '-';
- vTotalHargaBeli numeric;
- vTotalPotongan numeric;
- vTotalSetelahPotongan numeric;
- vTotalDPP numeric;
- vTotalPPN numeric;
- vTotalDPPdanPPN numeric;
- vIncludeTax character varying;
- vOuBuId bigint;
- BEGIN
- vReportName := 'FormPurchaseOrder';
- vEmptyId := -99;
- SELECT f_get_ou_bu_info_report(pTenantId, A.ou_id) AS info
- FROM pu_po A
- WHERE A.po_id = pPoId INTO result ;
- vOuBuInfoReport := result.info;
- vOuBuId := vOuBuInfoReport.ou_id;
- vKeySignApprovalName := 'path.sign.'||lower(replace(trim(pApprovalName), ' ', '_'));
- vKeySignPreparedBy := 'path.sign.'||lower(replace(trim(pPreparedBy), ' ', '_'));
- -- Untuk PO Item sudah dijaga include tax atau tidak semuanya sama
- SELECT flg_tax_amount INTO vIncludeTax FROM pu_po_item WHERE po_id = pPoId LIMIT 1;
- -- Tambahan data summary
- SELECT COALESCE(SUM(A.gross_item_amount),0) INTO vTotalHargaBeli
- FROM pu_po_item A
- WHERE A.po_id = pPoId;
- SELECT SUM(discount_amount * qty_int) INTO vTotalPotongan
- FROM pu_po_item A
- WHERE A.po_id = pPoId;
- SELECT vTotalHargaBeli - vTotalPotongan INTO vTotalSetelahPotongan;
- IF vIncludeTax = 'Y' THEN
- SELECT COALESCE(SUM(A.nett_item_amount),0) INTO vTotalDPP
- FROM pu_po_item A
- WHERE A.po_id = pPoId;
- SELECT COALESCE(SUM(A.tax_amount),0) INTO vTotalPPN
- FROM pu_po_item A
- WHERE A.po_id = pPoId;
- ELSE
- vTotalDPP := vTotalSetelahPotongan;
- SELECT COALESCE(SUM(A.tax_amount),0) INTO vTotalPPN
- FROM pu_po_item A
- WHERE A.po_id = pPoId;
- END IF;
- SELECT vTotalDPP + vTotalPPN INTO vTotalDPPdanPPN;
- /* get sub total */
- SELECT COALESCE(SUM(a.nett_item_amount), 0) INTO vSubTotal
- FROM pu_po_item A
- LEFT OUTER JOIN pu_po_balance_item E ON A.po_item_id=E.po_item_id
- WHERE A.po_id = pPoId
- AND COALESCE(E.qty_int_po+E.qty_int_add-E.qty_int_cancel,A.qty_int) > 0;
- -- Get total Item
- SELECT SUM( COALESCE(E.qty_int_po+E.qty_int_add-E.qty_int_cancel,A.qty_int) ) INTO vTotalQty
- FROM pu_po_item A
- LEFT OUTER JOIN pu_po_balance_item E ON A.po_item_id=E.po_item_id
- WHERE A.po_id = pPoId
- AND COALESCE(E.qty_int_po+E.qty_int_add-E.qty_int_cancel,A.qty_int) > 0;
- /* get ppn */
- /* David, 4 Februari 2015 : tidak boleh gunakan cara ini, bisa selisih karena ada pembulatan
- * SELECT COALESCE(SUM(A.tax_amount),0) INTO vPpn
- FROM pu_po_item A
- WHERE A.po_id = pPoId;
- */
- IF EXISTS(SELECT 1 FROM pu_po_item WHERE po_id = pPoId AND tax_percentage <> 0) THEN
- SELECT COALESCE(f_tax_rounding(pTenantId, vSubTotal, a.tax_percentage), 0) INTO vPpn
- FROM pu_po_item A WHERE A.po_id = pPoId
- AND A.tax_percentage <> 0
- GROUP BY A.tax_percentage;
- ELSE
- vPpn = 0;
- END IF;
- /* get total */
- SELECT (vSubTotal + vPpn) INTO vTotal;
- /* get term of payment code */
- SELECT A.top_code INTO vTermOfPaymentCode
- FROM pu_po A
- INNER JOIN t_combo_value B ON B.combo_id = 'TERMOFPAYMENT' AND A.top_code = B.code
- WHERE A.po_id = pPoId;
- /* get term of payment */
- IF (vTermOfPaymentCode = 'COD') THEN
- vTermOfPayment := 'COD';
- ELSEIF (vTermOfPaymentCode = 'T7D') THEN
- vTermOfPayment := '7 Days';
- ELSEIF (vTermOfPaymentCode = 'T14D') THEN
- vTermOfPayment := '14 Days';
- ELSEIF (vTermOfPaymentCode = 'T15D') THEN
- vTermOfPayment := '15 Days';
- ELSEIF (vTermOfPaymentCode = 'T21D') THEN
- vTermOfPayment := '21 Days';
- ELSEIF (vTermOfPaymentCode = 'T30D') THEN
- vTermOfPayment := '30 Days';
- ELSEIF (vTermOfPaymentCode = 'T45D') THEN
- vTermOfPayment := '45 Days';
- ELSEIF (vTermOfPaymentCode = 'T60D') THEN
- vTermOfPayment := '60 Days';
- ELSE
- vTermOfPayment := '-';
- END IF;
- Open pRefHeader FOR
- SELECT A.po_id, A.doc_no, A.doc_date, A.ou_id, COALESCE(B.npwp_name, f_get_ou_name(vOuBuInfoReport.ou_id)) AS ou_name,
- (f_get_report_parameter_config_value(pTenantId, vReportName, vOuBuId, 'address1') || ' ' || f_get_report_parameter_config_value(pTenantId, vReportName, vOuBuId, 'address2') || ' ' || f_get_report_parameter_config_value(pTenantId, vReportName, vOuBuId, 'address3')) AS ou_addr_1,
- (f_get_report_parameter_config_value(pTenantId, vReportName, vOuBuId, 'city') || ', ' || f_get_report_parameter_config_value(pTenantId, vReportName, vOuBuId, 'state_or_province') || ', ' || f_get_report_parameter_config_value(pTenantId, vReportName, vOuBuId, 'zip_code')) AS ou_addr_2, COALESCE(B.npwp_no, ' ') AS ou_npwp,
- (vOuBuInfoReport.phone1) AS ou_phone, (vOuBuInfoReport.fax1) AS ou_fax, COALESCE(B.flg_pkp, 'N') AS flg_ppn, A.partner_id,
- CONCAT(f_get_partner_code(A.partner_id), ' - ', f_get_partner_name(A.partner_id)) AS supplier_name,
- COALESCE(C.partner_address_id, vEmptyId) AS partner_address_id,
- COALESCE(C.address1, ' ') AS supplier_addr_1, COALESCE(C.address2, ' ') || COALESCE(C.address3, ' ') AS supplier_addr_2, pSupplierCp AS supplier_cp,
- COALESCE(C.phone1, ' ') AS supplier_phone, COALESCE(C.fax1, ' ') AS supplier_fax, vEmptyId AS partner_cp_id,
- A.remark, A.flg_delivery, A.curr_code, A.top_code, vTermOfPayment AS term_of_payment, A.purchaser_id, pPreparedBy AS prepared_by,
- pApprovalName AS approval_name, vSubTotal AS sub_total, vPpn AS ppn, vTotal AS total, COALESCE(vTotalQty, 0) AS total_qty,
- lower(terbilang(vTotal)) AS total_name, (CASE WHEN A.curr_code = 'IDR' THEN 'rupiah' ELSE A.curr_code END) AS curr_name,
- f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, vKeySignApprovalName) AS path_sign_approval,
- f_get_report_parameter_config_value(pTenantId, vReportName, A.ou_id, vKeySignPreparedBy) AS path_sign_prepared_by,
- vOuBuInfoReport.image_url AS path_logo_ou,
- pDatetime AS datetime,
- vTotalHargaBeli as total_harga_beli,
- vTotalPotongan as total_potongan,
- vTotalSetelahPotongan as total_setelah_potongan,
- vTotalDPP as dpp,
- vTotalPPN as ppn,
- vTotalDPPdanPPN as total_bayar,
- D.warehouse_name as nama_gudang,
- D.address1 as alamat_gudang_1,
- D.address2 as alamat_gudang_2,
- D.city as kota_gudang,
- D.zip_code as kode_pos_gudang,
- D.phone1 as telepon_gudang,
- D.fax1 as fax_gudang
- FROM pu_po A
- LEFT OUTER JOIN t_ou_legal B ON A.ou_id = B.ou_id
- LEFT OUTER JOIN m_partner_address C ON A.partner_id = C.partner_id AND C.flg_official = 'Y'
- INNER JOIN m_warehouse D ON A.warehouse_id = D.warehouse_id
- WHERE A.po_id = pPoId;
- RETURN NEXT pRefHeader;
- Open pRefDetail FOR
- SELECT '1' AS grouping, A.po_item_id, A.po_id, A.product_id, C.product_code, C.product_name, A.curr_code,
- A.gross_price_po AS price,
- COALESCE(E.qty_int_po+E.qty_int_add-E.qty_int_cancel,A.qty_int) AS qty,
- COALESCE(E.qty_int_po+E.qty_int_add-E.qty_int_cancel,A.qty_int) * A.gross_price_po AS amount, A.base_uom_id, D.uom_name,
- (f_get_product_warranty_service(A.product_id) :: integer) AS warranty,
- f_get_product_specs(A.product_id) AS product_specs, A.discount_percentage, A.nett_item_amount,
- COALESCE(E.qty_int_po+E.qty_int_add-E.qty_int_cancel,A.qty_int) * A.discount_amount AS total_discount_amount
- FROM pu_po_item A
- INNER JOIN pu_po B ON A.tenant_id = B.tenant_id AND A.po_id = B.po_id
- LEFT OUTER JOIN pu_po_balance_item E ON A.po_item_id=E.po_item_id
- INNER JOIN m_product C ON A.tenant_id = C.tenant_id AND A.product_id = C.product_id
- INNER JOIN m_uom D ON A.tenant_id = D.tenant_id AND A.base_uom_id = D.uom_id
- WHERE A.po_id = pPoId AND
- COALESCE(E.qty_int_po+E.qty_int_add-E.qty_int_cancel,A.qty_int) > 0
- ORDER BY A.line_no;
- RETURN NEXT pRefDetail;
- END;
- $BODY$
- LANGUAGE plpgsql VOLATILE
- COST 100
- ROWS 1000;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement