r_print_purchase_order

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;