| |
SELECT LL_CNT, ITEMNO, BARCODE, IMAGENAME, VENDORID, VENDORTYPE,
CUSTPRICE, SELLINGPRICE, WONREWARD,MUIZAMON, ISGIFT,
BRANDCODE, BRANDNAME, ISOPTION, PRIORITY, CODE, CODENAME,
ITEMNAME, STATUS, KEYWORD, TOTALCOUNT
FROM (
SELECT ROWNUM LL_CNT, ITEMNO, BARCODE, IMAGENAME, VENDORID, VENDORTYPE,
CUSTPRICE, SELLINGPRICE, WONREWARD,MUIZAMON, ISGIFT,
BRANDCODE, BRANDNAME, ISOPTION, PRIORITY, CODE, CODENAME, ITEMNAME, STATUS, KEYWORD, TOTALCOUNT
FROM (
SELECT A.ITEMNO, MAX(NVL(A.BARCODE, A.ADDITIONNO)) BARCODE,
MAX(A.IMAGENAME) IMAGENAME, MAX(A.VENDORID) VENDORID, MAX(A.VENDORTYPE) VENDORTYPE,
MAX(A.CUSTPRICE) CUSTPRICE, MAX(A.SELLINGPRICE) SELLINGPRICE, ROUND(MAX(A.REWARD*SELLINGPRICE)) WONREWARD,
MAX(NVL(A.MUIZAMON,0)) MUIZAMON, MAX(DECODE(B.ITEMNO,NULL,'0','1')) ISGIFT,
MAX(C.BRANDCODE) BRANDCODE, MAX(NVL(C.BRANDNAME,'없음')) BRANDNAME, MAX(DECODE(H.ITEMNO,NULL,'0','1')) ISOPTION,
MAX(A.PRIORITY) PRIORITY, MIN(F.CODE) CODE, MIN(F.CODENAME) CODENAME, MAX(ITEMNAME) ITEMNAME, MAX(A.STATUS) STATUS, MAX(A.KEYWORD) KEYWORD
FROM (SELECT A.ITEMNO, A.VENDORID, A.VENDORTYPE, A.BARCODE, A.ADDITIONNO, A.BRANDCODE, A.ITEMNAME, A.SELLINGPRICE, A.CUSTPRICE, A.IMAGENAME, A.REWARD, A.STATUS, A.KEYWORD, A.MUIZAMON, B.PRIORITY
FROM ITEMS A, DISPLAY B
WHERE B.ITEMNO = A.ITEMNO
AND B.KIND = :1
AND B.LINKCATE = :2
AND B.VENDORID = A.VENDORID
AND B.VENDORTYPE = A.VENDORTYPE
AND SYSDATE BETWEEN B.FRDATE AND B.TODATE) A,
(SELECT A.*
FROM GIFTITEM A, GIFT B
WHERE B.GIFTNO = A.GIFTNO
AND A.ISUSING = '1'
AND B.ISUSING = '1') B,
BRAND C, CATEGORY_DUP E, CATEGORY F, ITEMOPTIONS H, INVENTORY J
WHERE E.ITEMNO = A.ITEMNO
AND E.CODE = F.CODE
AND F.ISUSING = '1'
AND A.STATUS IN ('1','4')
AND B.ITEMNO(+) = A.ITEMNO
AND B.FRDATE(+) < SYSDATE
AND B.TODATE(+) > SYSDATE
AND B.ISUSING(+) = '1'
AND C.BRANDCODE = A.BRANDCODE
AND H.ITEMNO(+) = A.ITEMNO
AND J.ITEMNO = A.ITEMNO
AND J.INVQTY > 0
GROUP BY A.ITEMNO
ORDER BY PRIORITY
) A,
(
SELECT COUNT(*) TOTALCOUNT
FROM (SELECT A.ITEMNO
FROM (SELECT A.*, B.PRIORITY
FROM ITEMS A, DISPLAY B
WHERE B.ITEMNO = A.ITEMNO
AND B.KIND = :3
AND B.LINKCATE = :4
AND B.VENDORID = A.VENDORID
AND B.VENDORTYPE = A.VENDORTYPE
AND SYSDATE BETWEEN B.FRDATE AND B.TODATE) A,
BRAND C, CATEGORY_DUP E, CATEGORY F, INVENTORY J
WHERE E.ITEMNO = A.ITEMNO
AND E.CODE = F.CODE
AND F.ISUSING = '1'
AND A.STATUS IN ('1','4')
AND C.BRANDCODE = A.BRANDCODE
AND J.ITEMNO = A.ITEMNO
AND J.INVQTY>0
GROUP BY A.ITEMNO
)
) B
)
WHERE LL_CNT BETWEEN :5 AND :6
2.
SELECT item.deliverymain,
MAX(delven.venname),
SUM(ROUND(sale.saleprice, -1) * bask.orderquantity) subsum,
MAX(NVL(delven.divlimit,0)) divlimit,
MAX(NVL(delven.divprice,0)) divprice,
MAX(DECODE(item.deliverymain,'104', dpri.low, '0')) tempdivlimit,
MAX(DECODE(item.deliverymain,'104', dpri.high, '0')) tempdivprice,
MAX(NVL(gendername,'')) gendername,
SUM(ROUND(sale.guestprice, -1) * bask.orderquantity) subguestsum,
MAX(sale.dlimit) dlimit, MAX(sale.dprice) dprice
FROM basketitem bask, items item, vendors delven, policy dpri
,(
SELECT itemno, prodcode, gendername, dlimit, dprice, isgong, isgonggu,
ROUND(NVL(sellingprice,0)
* (1-decode(isgonggu,0,decode(issale, 1, 0,
decode(ismember, 0, guestrate,
decode(gender,'1',generalrate,
'2',premiumrate,
'3',chargerate,0)
)
),0)), -1) saleprice,
ROUND(NVL(sellingprice,0)*decode(issale,0,(1-guestrate),1), -1) guestprice
FROM
(
SELECT bask.sessionid, bask.orderchannel, bst.memberid memberid,
NVL(mem.ismember,'0') ismember, NVL(mem.grade,'0') gender,
item.itemno, bask.prodcode, nvl(gong.endprice,item.sellingprice) sellingprice, item.discountrate,
NVL(item.guestrate,0) guestrate, NVL(item.generalrate,0) generalrate,
NVL(item.premiumrate,0) premiumrate,NVL(item.chargerate,0) chargerate,
DECODE(item.saleflag, '0', 0,
DECODE(SIGN(SYSDATE-NVL(item.salestart, SYSDATE+1)), -1, 0,
DECODE(SIGN(NVL(item.saleend, SYSDATE-1) - SYSDATE), -1,0,1 )
)
) issale, mgi.codename gendername, dpri.low dlimit, dpri.high dprice,
nvl(gong.isgong,2) isgong, nvl(gong.gongid,0) isgonggu
FROM BASKETITEM bask, ITEMS item, BASKET bst, MEMBERS mem, policy mgi, policy dpri,
(select a.gongid, a.isgong, b.itemno, b.vendorid, b.vendortype,
b.custprice, b.endprice, b.maxcount, b.sellingcount
from gong a, itemgong b
where sysdate between a.frdate and a.todate
and a.gongid = b.gongid
and b.isusing = '1') gong
WHERE bask.sessionid = :1
AND bask.orderchannel = :2
AND bask.itemno = item.itemno
AND bst.sessionid = bask.sessionid
AND bst.orderchannel = bask.orderchannel
AND bst.memberid = mem.memberid(+)
AND mgi.maincode = 'MGI'
AND mgi.isuse ='1'
AND mgi.subcode = mem.gender
AND dpri.maincode = 'DPRI'
AND dpri.isuse ='1'
AND dpri.subcode = '100'
and bask.itemno = gong.itemno(+)
and bask.vendorid = gong.vendorid(+)
and bask.vendortype = gong.vendortype(+)
)
) sale
WHERE bask.sessionid = :3
AND bask.orderchannel = :4
AND bask.itemno = item.itemno
AND bask.vendorid = item.vendorid
AND bask.vendortype = item.vendortype
AND item.deliverymain = delven.vendorid
AND dpri.maincode='DPRI'
AND dpri.subcode = '100'
AND dpri.isuse = '1'
AND bask.itemno = sale.itemno
AND bask.prodcode = sale.prodcode
GROUP BY item.deliverymain
3.
SELECT bask.vendorid vendorid,
bask.vendortype vendortype,
bnd.brandname brandname,
bask.itemno itemno,
MAX(item.itemname) itemname,
bask.prodcode prodcode,
MAX(bask.selectattribute) selectattribute,
MAX(ROUND(sale.saleprice, -1)) sallingprice,
MAX(ROUND(sale.saleprice, -1) * bask.orderquantity) subsum,
MAX(bask.orderquantity) orderquantity,
MAX(item.muizamon) muizamon,
MAX(item.deliverymain) deliverymain,
MAX(delven.venname) deliverymainname,
MAX(NVL(bask.iscobuy,'0')) iscobuy,
MAX(NVL(gift.giftno,'000')) giftno,
MAX(isgong) isgong, MAX(isgonggu) isgonggu,
MAX(item.muizamon) muizamon, MAX(inv.invqty) invqty
FROM basketitem bask, items item, brand bnd, vendors delven, inventory inv,
(
SELECT bask.itemno, gift.giftno
FROM basketitem bask, giftitem gitem, gift
WHERE bask.sessionid = :1
AND bask.orderchannel = :2
AND bask.vendorid = gitem.vendorid
AND bask.vendortype = gitem.vendortype
AND bask.itemno = gitem.itemno
AND gitem.isusing ='1'
AND sysdate >= gitem.frdate
AND sysdate < gitem.todate
AND gitem.giftno= gift.giftno
AND gift.isusing ='1'
) gift
,(
SELECT itemno, isgong, isgonggu,
ROUND(NVL(sellingprice,0)
* (1-decode(isgonggu,0,decode(issale, 1, 0,
decode(ismember, 0, guestrate,
decode(gender,'1',generalrate,
'2',premiumrate,
'3',chargerate,0)
)
),0)), -1) saleprice
FROM
(
SELECT bask.sessionid, bask.orderchannel, bst.memberid memberid,
NVL(mem.ismember,'0') ismember, NVL(mem.grade,'0') gender,
item.itemno, bask.prodcode,
nvl(gong.endprice,item.sellingprice) sellingprice, item.discountrate,
NVL(item.guestrate,0) guestrate, NVL(item.generalrate,0) generalrate,
NVL(item.premiumrate,0) premiumrate,NVL(item.chargerate,0) chargerate,
DECODE(item.saleflag, '0', 0,
DECODE(SIGN(SYSDATE-NVL(item.salestart, SYSDATE+1)), -1, 0,
DECODE(SIGN(NVL(item.saleend, SYSDATE-1) - SYSDATE), -1,0,1 )
)
) issale, nvl(gong.isgong,2) isgong, nvl(gong.gongid,0) isgonggu
FROM BASKETITEM bask, items item, BASKET bst, MEMBERS mem,
(select a.gongid, a.isgong, b.itemno, b.vendorid, b.vendortype,
b.custprice, b.endprice, b.maxcount, b.sellingcount
from gong a, itemgong b
where sysdate between a.frdate and a.todate
and a.gongid = b.gongid
and b.isusing = '1') gong
WHERE bask.sessionid = :3
AND bask.orderchannel = :4
AND bask.itemno = item.itemno
AND bst.sessionid = bask.sessionid
AND bst.orderchannel = bask.orderchannel
AND bst.memberid = mem.memberid(+)
and bask.itemno = gong.itemno(+)
and bask.vendorid = gong.vendorid(+)
and bask.vendortype = gong.vendortype(+)
)
) sale
WHERE bask.sessionid = :5
AND bask.orderchannel = :6
AND bask.itemno = item.itemno
AND bask.vendorid = item.vendorid
AND bask.vendortype = item.vendortype
AND item.brandcode = bnd.brandcode
AND item.deliverymain = delven.vendorid
AND bask.itemno = gift.itemno(+)
AND bask.itemno = sale.itemno
AND inv.itemno = bask.itemno
GROUP BY bask.vendorid, bask.vendortype, bnd.brandname, bask.itemno, bask.prodcode
|