Untitled
unknown
csharp
2 months ago
12 kB
3
Indexable
sql = @"
SELECT VDRAFT, VTRANSNO,
VSUPPMDMCO,
VSUPPDOCNO,
VSUPPNAME,
DSET_VITEMCODE_VSUPTY,
DSET_VITEMCODE_VSUPTL,
DSET_VITEMCODE_VCOMT,
VSUPPSAPCO,
VDESKSUPP,
VCREA,
DCREA,
NNRP,
VDATAREQNAM,
VDATAREQDIV,
VITEMNAME,
DESTPOFIN,
TITLE ,
BNTKBDNUSAHA,
KOLEKSI,
STATSUP,
JENISSUPP
FROM(
SELECT NULL AS VDRAFT,
NULL AS VTRANSNO,
A.VSUPPMDMCO,
A.VSUPPDOCNO,
A.VSUPPNAME,
A.DSET_VITEMCODE_VSUPTY,
A.DSET_VITEMCODE_VSUPTL,
A.DSET_VITEMCODE_VCOMT,
A.VSUPPSAPCO,
A.VDESKSUPP,
A.VCREA,
NVL(A.DCREA, '01-Jan-1990') DCREA,
--B.NNRP,
null as NNRP,
--B.VDATAREQNAM,
(select to_char(LISTAGG(VDATAREQNAM, ', ')WITHIN GROUP(ORDER BY VDATAREQNAM))
from AHMSISMD_DTLDATAREQS
where RGEND_VSUPPMDMCO = A.VSUPPMDMCO
) VDATAREQNAM,
--B.VDATAREQDIV,
(select to_char(LISTAGG(VDATAREQDIV, ', ')WITHIN GROUP(ORDER BY VDATAREQDIV))
from AHMSISMD_DTLDATAREQS
where RGEND_VSUPPMDMCO = A.VSUPPMDMCO
) VDATAREQDIV,
C.VITEMNAME,
--'01-Jan-1990' DESTPOFIN,
--TO_DATE('01-Jan-1990','DD-MON-YYYY') DESTPOFIN,
NVL(x.DESTPOFIN, '01-Jan-1990') DESTPOFIN,
D.VITEMNAME AS TITLE ,
E.VITEMNAME AS BNTKBDNUSAHA,
--(SELECT TO_CHAR(wm_concat(DISTINCT MRPTY_NREPTYPE))
-- FROM AHMSISMD_DTLREPFISLS
-- WHERE RGEND_VSUPPMDMCO = A.VSUPPMDMCO
--) AS KOLEKSI,
(select to_char(LISTAGG(MRPTY_NREPTYPE, ', ')WITHIN GROUP(ORDER BY MRPTY_NREPTYPE))
from AHMSISMD_DTLREPFISLS
where RGEND_VSUPPMDMCO = A.VSUPPMDMCO
) AS KOLEKSI,
F.VITEMNAME AS STATSUP,
--(SELECT LISTAGG(G.MSCLA_NSUCLASS, ', ') WITHIN GROUP(
-- ORDER BY G.MSCLA_NSUCLASS)
-- FROM AHMSISMD_DTLSUPCLSES G
-- WHERE A.VSUPPMDMCO = G.RGEND_VSUPPMDMCO
-- GROUP BY G.RGEND_VSUPPMDMCO
--) JENISSUPP
null as JENISSUPP
FROM AHMSISMD_HDRGENDATAS A
--LEFT JOIN AHMSISMD_DTLDATAREQS B
--ON A.VSUPPMDMCO = B.RGEND_VSUPPMDMCO
LEFT JOIN AHMMOERP_DTLSETTINGS C
ON A.DSET_RSET_VID_VSUPTY = C.RSET_VID AND A.DSET_VITEMCODE_VSUPTY = C.VITEMCODE
LEFT JOIN AHMMOERP_DTLSETTINGS D
ON A.DSET_RSET_VID_VSUPTL = D.RSET_VID AND A.DSET_VITEMCODE_VSUPTL = D.VITEMCODE
LEFT JOIN AHMMOERP_DTLSETTINGS E
ON A.DSET_RSET_VID_VCOMT = E.RSET_VID AND A.DSET_VITEMCODE_VCOMT = E.VITEMCODE
--LEFT JOIN AHMSISMD_DTLPURCORGS G
--ON A.VSUPPMDMCO = G.RGEND_VSUPPMDMCO
--LEFT JOIN AHMSISMD_TXNGENDATAS H
-- ON A.VSUPPMDMCO = H.VSUPPMDMCO
--LEFT JOIN AHMSISMD_TXNBLKUNRES I
-- ON H.VTRANSNO = I.VTRANSNO
left join (
select vsuppmdmco,DESTPOFIN from
(select vsuppmdmco, max(vtransno) vtransno from ahmsismd_txngendatas
where dset_vitemcode_vtstt in ('3','5')
group by vsuppmdmco) h
left join(select vtransno,DESTPOFIN from ahmsismd_txnblkunres where vblkuntype in ('Block PO','Block Total','Unblock Temp','Delete')) i on h.vtransno = i.vtransno
) x on a.vsuppmdmco = x.vsuppmdmco
LEFT JOIN AHMMOERP_DTLSETTINGS F
ON A.DSET_RSET_VID_VSTSUP = F.RSET_VID AND A.DSET_VITEMCODE_VSTSUP = F.VITEMCODE
--OR G.DSET_RSET_VID_VSTSUP = F.RSET_VID AND G.DSET_VITEMCODE_VSTSUP = F.VITEMCODE
WHERE A.VSUPPSAPCO IS NOT NULL
AND UPPER(A.VSUPPSAPCO) NOT LIKE UPPER('%error%')
AND TRUNC(A.DCREA) BETWEEN :astart AND :aend
AND A.VSUPPMDMCO NOT IN
(
SELECT distinct VSUPPMDMCO
FROM AHMSISMD_TXNGENDATAS
WHERE VSUPPMDMCO IS NOT NULL
AND (UPPER(VSTATWF) NOT LIKE '%WAITING%')
)
-- AND A.VSUPPMDMCO NOT IN
-- (
-- SELECT distinct VSUPPMDMCO
-- FROM AHMSISMD_TXNSAPMSGS
-- WHERE VSAPMSG is null
-- )
AND A.VSUPPMDMCO NOT IN
(SELECT distinct VSUPPMDMCO FROM AHMSISMD_TXNGENDATAS
WHERE VDRAFT = 'Y' and dset_vitemcode_vtstt = '3' and vstatwf is null and vsuppmdmco is not null
)
";
if (purcorg == "4")
{
sql = sql + " AND A.DSET_VITEMCODE_VSTSUP = :STSUP AND F.VITEMCODE = '4'";
}
else
{
sql = sql + " AND A.DSET_VITEMCODE_VSTSUP = :STSUP AND F.VITEMCODE = '1'";
sql = sql + @" AND :NNRP in (select NNRP
from AHMSISMD_DTLDATAREQS
where RGEND_VSUPPMDMCO = A.VSUPPMDMCO)";
}
// Logger.WriteLog('purcorg' + purcorg);
//sql = sql + " ORDER BY A.DCREA DESC";
sql = sql + @"
UNION
SELECT A.VDRAFT,
A.VTRANSNO,
A.VSUPPMDMCO,
A.VSUPPDOCNO,
A.VSUPPNAME,
A.DSET_VITEMCODE_VSUPTY,
A.DSET_VITEMCODE_VSUPTL,
A.DSET_VITEMCODE_VCOMT,
A.VSUPPSAPCO,
A.VDESKSUPP,
A.VCREA,
NVL(A.DCREA, '01-Jan-1990') DCREA,
B.NNRP,
B.VDATAREQNAM,
--A.VTRANSNO,
B.VDATAREQDIV,
C.VITEMNAME,
NVL(I.DESTPOFIN, '01-Jan-1990') DESTPOFIN,
--'01-Jan-1990' DESTPOFIN,
D.VITEMNAME AS TITLE ,
E.VITEMNAME AS BNTKBDNUSAHA,
--(SELECT TO_CHAR(wm_concat(DISTINCT MRPTY_NREPTYPE))
-- FROM AHMSISMD_DTLREPFISLS
-- WHERE RGEND_VSUPPMDMCO = A.VSUPPMDMCO
--) AS KOLEKSI,
(select to_char(LISTAGG(MRPTY_NREPTYPE, ', ')WITHIN GROUP(ORDER BY MRPTY_NREPTYPE))
from AHMSISMD_TXNREPFISLS
where VTRANSNO = A.VTRANSNO
) AS KOLEKSI,
F.VITEMNAME AS STATSUP,
--(SELECT LISTAGG(G.MSCLA_NSUCLASS, ', ') WITHIN GROUP(
-- ORDER BY G.MSCLA_NSUCLASS)
-- FROM AHMSISMD_DTLSUPCLSES G
-- WHERE A.VSUPPMDMCO = G.RGEND_VSUPPMDMCO
-- GROUP BY G.RGEND_VSUPPMDMCO
--) JENISSUPP
null as JENISSUPP
FROM AHMSISMD_TXNGENDATAS A
LEFT JOIN AHMSISMD_TXNDATAREQS B
ON A.VTRANSNO = B.VTRANSNO
LEFT JOIN AHMMOERP_DTLSETTINGS C
ON A.DSET_RSET_VID_VSUPTY = C.RSET_VID AND A.DSET_VITEMCODE_VSUPTY = C.VITEMCODE
LEFT JOIN AHMMOERP_DTLSETTINGS D
ON A.DSET_RSET_VID_VSUPTL = D.RSET_VID AND A.DSET_VITEMCODE_VSUPTL = D.VITEMCODE
LEFT JOIN AHMMOERP_DTLSETTINGS E
ON A.DSET_RSET_VID_VCOMT = E.RSET_VID AND A.DSET_VITEMCODE_VCOMT = E.VITEMCODE
--LEFT JOIN AHMSISMD_TXNPURCORGS G
--ON A.VTRANSNO = G.VTRANSNO
--LEFT JOIN AHMSISMD_TXNGENDATAS H
-- ON A.VSUPPMDMCO = H.VSUPPMDMCO
LEFT JOIN AHMSISMD_TXNBLKUNRES I
ON A.VTRANSNO = I.VTRANSNO
LEFT JOIN AHMMOERP_DTLSETTINGS F
ON A.DSET_RSET_VID_VSTSUP = F.RSET_VID AND A.DSET_VITEMCODE_VSTSUP = F.VITEMCODE
--OR G.DSET_RSET_VID_VSTSUP = F.RSET_VID AND G.DSET_VITEMCODE_VSTSUP = F.VITEMCODE
WHERE A.VSUPPSAPCO IS NOT NULL
AND UPPER(A.VSUPPSAPCO) NOT LIKE UPPER('%error%')
AND TRUNC(A.DCREA) BETWEEN :astart AND :aend
AND VSTATWF IS NULL AND VDRAFT = 'Y'
AND A.DSET_VITEMCODE_VTSTT = '3'
-- AND A.VTRANSNO = (select max(cast(b.vtransno as integer)) from ahmsismd_txngendatas b where b.vsuppmdmco = a.vsuppmdmco)
";
if (purcorg == "4")
{
sql = sql + " AND A.DSET_VITEMCODE_VSTSUP = :STSUP AND F.VITEMCODE = '4'";
}
else
{
sql = sql + " AND A.DSET_VITEMCODE_VSTSUP = :STSUP AND F.VITEMCODE = '1'";
sql = sql + " AND B.NNRP = :NNRP ";
}
//sql = sql + ")WHERE NNRP = :NNRP ORDER BY DCREA DESC ";
sql = sql + @") WHERE
UPPER(VSUPPSAPCO) LIKE UPPER(:VSUPPSAPCO)
AND UPPER(VSUPPNAME) LIKE UPPER(:VSUPPNAME) ORDER BY DCREA DESC ";Editor is loading...
Leave a Comment