a

/**
 * author : fredi, 27 Desember 2015
 */
CREATE OR REPLACE FUNCTION r_generate_vat_out_for_efaktur(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;

    vOuId               bigint;
    vPartnerId          bigint;
    vTaxDateFrom        character varying;
    vTaxDateTo          character varying;
    vFilterPartnerQuery     text;
    vEmpty              character varying := '';
    vStatusPkp              character varying;
    vQueryStatusPkp     character varying := '';

BEGIN
    -- get input
    vOuId := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'ouId') AS bigint);
    vPartnerId := CAST(f_get_report_parameter_value_by_id(pReportMessageId, 'partnerId') AS bigint);
    vTaxDateFrom := f_get_report_parameter_value_by_id(pReportMessageId, 'taxDateFrom');
    vTaxDateTo := f_get_report_parameter_value_by_id(pReportMessageId, 'taxDateTo');
    vStatusPkp := f_get_report_parameter_value_by_id(pReportMessageId, 'statusPkp');

    OPEN pRefHeader FOR
    SELECT 20 AS _COUNT,
            'FK', 'KD_JENIS_TRANSAKSI', 'FG_PENGGANTI',
            'NOMOR_FAKTUR', 'MASA_PAJAK', 'TAHUN_PAJAK',
            'TANGGAL_FAKTUR', 'NPWP', 'NAMA',
            'ALAMAT_LENGKAP', 'JUMLAH_DPP', 'JUMLAH_PPN',
            'JUMLAH_PPNBM', 'ID_KETERANGAN_TAMBAHAN', 'FG_UANG_MUKA',
            'UANG_MUKA_DPP', 'UANG_MUKA_PPN', 'UANG_MUKA_PPNBM',
            'REFERENSI','KODE_DOKUMEN_PENDUKUNG', 1 AS ordial
    UNION
    SELECT 14 AS _COUNT,
            'LT', 'NPWP', 'NAMA',
            'JALAN', 'BLOK', 'NOMOR',
            'RT', 'RW', 'KECAMATAN',
            'KELURAHAN', 'KABUPATEN', 'PROPINSI',
            'KODE_POS', 'NOMOR_TELEPON', '',
            '', '', '',
            '', '',2 AS ordial
    UNION
    SELECT 11 AS _COUNT,
            'OF', 'KODE_OBJEK', 'NAMA',
            'HARGA_SATUAN', 'JUMLAH_BARANG', 'HARGA_TOTAL',
            'DISKON', 'DPP', 'PPN',
            'TARIF_PPNBM', 'PPNBM', '',
            '', '', '',
            '', '', '',
            '', '', 3 AS ordial
    ORDER BY ordial;

    RETURN NEXT pRefHeader;


    vFilterPartnerQuery := '';

    IF vPartnerId <> -99 THEN
        vFilterPartnerQuery := ' AND A.partner_id = '||vPartnerId|| ' ';
    END IF;

    IF vStatusPkp <> vEmpty THEN
        vQueryStatusPkp := ' AND f_get_flg_pkp_partner(A.partner_id) = ''' || vStatusPkp || '''';
    END IF;

    OPEN pRefDetail FOR
    EXECUTE '
    SELECT CASE WHEN record_type = $7 THEN 20 WHEN record_type = $8 THEN 14 WHEN record_type = $9 THEN 11 END AS _COUNT,
        A.record_type, COALESCE(A.col1, $1) AS col1,

        COALESCE(A.col2, $1) AS col2, COALESCE(A.col3, $1) AS col3, COALESCE(A.col4, $1) AS col4, COALESCE(A.col5, $1) AS col5,
        COALESCE(A.col6, $1) AS col6, COALESCE(A.col7, $1) AS col7, COALESCE(A.col8, $1) AS col8, COALESCE(A.col9, $1) AS col9,

        CASE WHEN record_type = $7 THEN TRUNC(COALESCE(NULLIF(A.col10, $1), $6)::numeric, 0)::character varying ELSE COALESCE(A.col10 , $1) END AS col10,
        CASE WHEN record_type = $7 THEN TRUNC(COALESCE(NULLIF(A.col11, $1), $6)::numeric, 0)::character varying ELSE COALESCE(A.col11 , $1) END AS col11,
        CASE WHEN record_type = $7 THEN TRUNC(COALESCE(NULLIF(A.col12, $1), $6)::numeric, 0)::character varying ELSE COALESCE(A.col12 , $1) END AS col12,
        COALESCE(A.col13, $1) AS col13,

        COALESCE(A.col14, $1) AS col14,
        CASE WHEN record_type = $7 THEN TRUNC(COALESCE(NULLIF(A.col15, $1), $6)::numeric, 0)::character varying ELSE COALESCE(A.col15 , $1) END AS col15,
        CASE WHEN record_type = $7 THEN TRUNC(COALESCE(NULLIF(A.col16, $1), $6)::numeric, 0)::character varying ELSE COALESCE(A.col16 , $1) END AS col16,
        CASE WHEN record_type = $7 THEN TRUNC(COALESCE(NULLIF(A.col17, $1), $6)::numeric, 0)::character varying ELSE COALESCE(A.col17 , $1) END AS col17,

        COALESCE(A.col18, $1) AS col18, COALESCE(A.col19, $1) AS col19,
        CASE WHEN record_type = $7 THEN $6 ELSE $1 END AS col20
    FROM fi_vat_out_for_efaktur A
    JOIN m_ou_structure B ON A.ou_id = B.ou_id
    WHERE A.tenant_id = $2
        AND (B.ou_bu_id = $3 OR B.ou_branch_id = $3)
        AND A.tax_date BETWEEN $4 AND $5 '
        || vFilterPartnerQuery  || vQueryStatusPkp || '
    ORDER BY A.tax_date, A.tax_no, A.doc_type_id, A.doc_id, A.vat_out_for_efaktur_id'
    USING vEmpty, pTenantId, vOuId, vTaxDateFrom, vTaxDateTo, '0', 'FK', 'LT', 'OF';

    RETURN NEXT pRefDetail;

END
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100
  ROWS 1000;
/