Untitled

var listprocmtdhid = ValueList(qChkPF.procmtdh_id);
            if (isdefined("url.isdebug") and url.isdebug eq 1){
               //<cfdump var = "#listprocmtdhid#">
            }
            local.list_of_columns = "emp_id,member_no,full_name,first_name,last_name,salname,effective_date_from,currcode_emp,allvalue_emp,currcode_comp,allvalue_comp,local_name,birthdate,paydate,empaccount_number,emp_no,start_date,end_date,tax_number,company_code,register_no,procmtdh_id,percentage,comppercentage,work_location_code, cost_code, costcenter_name_en, local_first_name, local_last_name";
            local.type_of_columns = "VarChar,VarChar,VarChar,VarChar,VarChar,VarChar,Date,VarChar,Decimal,VarChar,Decimal,VarChar,Date,Date,VarChar,VarChar,Date,Date,VarChar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar,varchar, varchar";
            local.list_of_columns = local.itsInvestmentReport eq "Y" ? local.list_of_columns & ",investment_group_code,investment_code,investment_rate" : local.list_of_columns;
            local.type_of_columns = local.itsInvestmentReport eq "Y" ? local.type_of_columns & ",VarChar,VarChar,VarChar" : local.type_of_columns;

            var newQuery = querynew("#local.list_of_columns#","#local.type_of_columns#");

            if (Application.ACCOUNT.DB.DBDRIVER eq "MSSQL"){
                if (curr eq 'RESPECTIVE'){
                    var allvalue_emp = "DBO.SF2356896(pfemp.comp_value,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var currcode_emp = "pfemp.currency_code";
                    var currcode_comp = "pfcomp.currency_code";
                    var allvalue_comp = "DBO.SF2356896(pfcomp.comp_value,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var salary = "DBO.SF2356896(a.salary,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var currsal = "a.currency_code";
                } else if (curr eq 'TAX'){
                    var currcode_emp = "pfemp.currency_code_tax";
                    var allvalue_emp = "DBO.SF2356896(pfemp.comp_value_tax,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var currcode_comp = "pfcomp.currency_code_tax";
                    var allvalue_comp = "DBO.SF2356896(pfcomp.comp_value_tax,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var salary = "DBO.SF2356896(a.salary_tax,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var currsal = "a.currency_code_tax";
                } else {
                    var currcode_emp = "pfemp.currency_code_period";
                    var allvalue_emp = "DBO.SF2356896(pfemp.comp_value_period,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var currcode_comp = "pfcomp.currency_code_period";
                    var allvalue_comp = "DBO.SF2356896(pfcomp.comp_value_period,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var salary = "DBO.SF2356896(a.salary_period,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var currsal = "a.currency_code_period";
                }
                var result = "dbo.SF2356896(mtdhpf.result,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
            } else {
                if (curr eq 'RESPECTIVE'){
                    var currcode_emp = "pfemp.currency_code";
                    var allvalue_emp = "SF2356896(pfemp.comp_value,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var currcode_comp = "pfcomp.currency_code";
                    var allvalue_comp = "SF2356896(pfcomp.comp_value,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var salary = "SF2356896(a.salary,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var currsal = "a.currency_code";
                } else if (curr eq 'TAX'){
                    var currcode_emp = "pfemp.currency_code_tax";
                    var allvalue_emp = "SF2356896(pfemp.comp_value_tax,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var currcode_comp = "pfcomp.currency_code_tax";
                    var allvalue_comp = "SF2356896(pfcomp.comp_value_tax,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var salary = "SF2356896(a.salary_tax,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var currsal = "a.currency_code_tax";
                } else {
                    var currcode_emp = "pfemp.currency_code_period";
                    var allvalue_emp = "SF2356896(pfemp.comp_value_period,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var currcode_comp = "pfcomp.currency_code_period";
                    var allvalue_comp = "SF2356896(pfcomp.comp_value_period,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var salary = "SF2356896(a.salary_period,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
                    var currsal = "a.currency_code_period";
                }
                var result = "SF2356896(mtdhpf.result,'#REQUEST.SCookie.PNUMBER#','#REQUEST.SCookie.LNUMBER#',a.emp_id)";
            }

            if (provfund neq ''){
                sqlqData = "AND a.fund_id = :provfund";
            } else {
                sqlqData = "";
            }

            local.sqlqDataReport = " SELECT DISTINCT
                    d.procmtdh_id,
                    c.paydate,
                    a.emp_id,
                    f.emp_no,
                    f.start_date,
                    f.end_date,
                    a.member_no,
                    b.full_name,
                    b.first_name,
                    b.last_name,
                    b.local_name,
                    b.local_firstname,
                    b.local_lastname,
                    salutation.salname,
                    salutation.birthdate,
                    effective_date_from,
                    c.taxfilenumber,
                    e.empaccount_number,
                    comp.company_code,
                    compins.register_no,
                    a.empfund_id,
                    g.work_location_code,
                    g.cost_code,
                    h.costcenter_name_en";
            local.sqlqDataReport = local.itsInvestmentReport eq "Y" ? local.sqlqDataReport & ", a.investment_group_code , ip.investment_code, ip.investment_rate " : local.sqlqDataReport;
            local.sqlqDataReport = local.sqlqDataReport & " FROM TPYDEMPPROVIDENTFUND a  ";
            local.sqlqDataReport = local.itsInvestmentReport eq "Y" ? local.sqlqDataReport & " LEFT JOIN TPYRINVESTMENTGROUP ip ON a.investment_group_code = ip.investment_group_code AND ip.company_id = :company_id " : local.sqlqDataReport;
            local.sqlqDataReport = local.sqlqDataReport & " LEFT JOIN TEOMEMPPERSONAL b ON a.emp_id = b.emp_id
                    LEFT JOIN TPYDPROCMTDH c ON a.emp_id = c.emp_id
                    LEFT JOIN TEODEMPCOMPANY f ON a.emp_id = f.emp_id
                        AND f.company_id = :company_id
                    LEFT JOIN TEOMCOMPANY comp ON comp.company_id = f.company_id
                        AND comp.company_id = :company_id
                    LEFT JOIN TEORCOMPINSURANCE compins ON compins.company_id = f.company_id
                        AND compins.company_id = :company_id
                        AND compins.institution_code = 'PF'
                    INNER JOIN TPYDPROCMTDD d ON c.procmtdh_id = d.procmtdh_id
                    LEFT JOIN TPYDBANKTRANSFER e ON c.procmtdh_id = e.procmtdh_id
                    LEFT JOIN
                    (
                        SELECT emp_id, name_#REQUEST.SCOOKIE.LANG# salname,birthdate
                        FROM TEODEMPPERSONAL d
                        LEFT JOIN TEOMSALUTATION e ON d.salutation_code = e.code
                    ) salutation ON c.emp_id = salutation.emp_id
                    LEFT JOIN TEODEMPCOMPANY g on c.emp_id = g.emp_id
                    LEFT JOIN TEOMCOSTCENTER h on g.cost_code = h.costcenter_code and h.company_id = :company_id
                WHERE
                    c.emp_id IN (:emp_id)
                    AND c.procmtdh_id IN (#ListQualify(listprocmtdhid,"'")#)
                    AND a.status ='Y'
                    #sqlqData# ";