var sql =
"SELECT substr(a.acptseq, 3, 5) AS acptyear, substr(a.acptseq, 5, 3) AS acptmonth, to_char(to_date(a.acpviewdd, 'YYYY-MM-DD'), 'MM.DD') as acpviewdd, " +
" (SELECT cdnm FROM com.zbcmcode WHERE cdgrupid = 'C0001' AND cdid = a.acpttypecd AND a.instcd = '{?instcd}') AS acpttype, " +
" (SELECT cdnm FROM com.zbcmcode WHERE cdgrupid = 'C0003' AND cdid = a.acptkindlrgcd AND a.instcd = '{?instcd}') AS rcptkindlrg, " +
" b.clntnm, b.mpphonno, a.acptcnts, " +
" (SELECT f.depthngnm " +
" FROM com.zsdddept f " +
" WHERE f.deptcd = d.deptcd " +
" and f.instcd = '{?instcd}' AND rownum = 1) as deptnm, " +
" nvl(to_char(to_date(substr(d.repldt, 1, 8), 'YYYYMMDD'), 'MM.DD'), '추가요청부서') AS repldt, " +
" TO_CHAR(TO_DATE(substr(d.endreqdt, 1, 8), 'YYYYMMDD') - TO_DATE(a.acpviewdd, 'YYYYMMDD')) AS tretperd, " +
" d.replcnts " +
" FROM " +
" crm.cuvhacpi a, " +
" crm.cuvhhtmi b, " +
" crm.cuvhrqrp d, " +
" crm.cuvhdepi e " +
" WHERE a.instcd = '{?instcd}' " +
" AND a.instcd = b.instcd AND a.acptdd = b.acptdd AND a.acptseq = b.acptseq " +
" AND a.instcd = e.instcd AND a.acptdd = e.acptdd AND a.acptseq = e.acptseq " +
" AND d.instcd = e.instcd AND d.acptdd = e.acptdd AND d.acptseq = e.acptseq AND d.rgstseq = e.rgstseq " +
" AND d.cnclyn <> 'Y' " +
" AND a.acptdd >= '{?srchacptdd1}' AND a.acptdd <= '{?srchacptdd2}' ";
if ('{?srchacpttypecd}' != "") {
sql += " AND a.acpttypecd = '{?srchacpttypecd}' ";
}
if ('{?srchprcsstatcd}' != "") {
sql += " AND a.prcsstatcd = '{?srchprcsstatcd}' ";
}
if ('{?srchdeptnm}' != "") {
sql += " AND e.deptnm = '{?srchdeptnm}' ";
}
if ('{?srchdeptcd}' != "") {
sql += " AND e.deptcd = '{?srchdeptcd}' ";
}
if ('{?srchpatnm}' != "") {
sql += " AND b.patnm like '%' || '{?srchpatnm}' || '%' ";
}
if ('{?srchpid}' != "") {
sql += " AND b.pid = '{?srchpid}' ";
}
if ('{?srchfstrgstrnm}' != "") {
sql += " AND a.fstrgstrid = '{?srchfstrgstrnm}' ";
}
if ('{?acptkindlrg}' != "") {
sql += " AND a.acptkindlrgcd = '{?acptkindlrg}' ";
}
sql += " ORDER BY a.acptdd, a.acptseq";
return sql;