SELECT PRD_PRDCD PLU,
PRD_DESKRIPSIPANJANG DESKRIPSI,
PRD_UNIT UNIT,
PRD_FRAC FRAC,
PRD_KODETAG TAG,
SUM(DTL_QTY_PCS) QTY_PCS,
SUM(DTL_NETTO) NETT,
SUM(DTL_MARGIN)MARGIN, COUNT(DISTINCT(DTL_CUSNO)) MEMBER FROM
(SELECT dtl_rtype,
dtl_tanggal,
dtl_struk,
dtl_stat,
dtl_kasir,
dtl_no_struk,
dtl_seqno,
dtl_prdcd_ctn,
dtl_prdcd,
dtl_nama_barang,
dtl_unit,
dtl_frac,
dtl_tag,
dtl_bkp,
CASE
WHEN dtl_rtype='S'
THEN dtl_qty_pcs
ELSE dtl_qty_pcs * -1
END dtl_qty_pcs,
CASE
WHEN dtl_rtype='S'
THEN dtl_qty
ELSE dtl_qty*-1
END dtl_qty,
dtl_harga_jual,
dtl_diskon,
CASE
WHEN dtl_rtype='S'
THEN dtl_gross
ELSE dtl_gross*-1
END dtl_gross,
CASE
WHEN dtl_rtype='S'
THEN dtl_netto
ELSE dtl_netto*-1
END dtl_netto,
CASE
WHEN dtl_rtype='S'
THEN dtl_hpp
ELSE dtl_hpp*-1
END dtl_hpp,
CASE
WHEN dtl_rtype='S'
THEN dtl_netto - dtl_hpp
ELSE (dtl_netto - dtl_hpp) * -1
END dtl_margin,
dtl_k_div,
dtl_k_dept,
dtl_k_katb,
dtl_cusno,
dtl_namamember,
dtl_memberkhusus,
dtl_outlet,
dtl_suboutlet,
CASE
WHEN dtl_memberkhusus='Y'
THEN 'KHUSUS'
WHEN dtl_cusno='408501'
OR dtl_cusno ='444960'
THEN 'IDM'
WHEN dtl_memberkhusus IS NULL
AND dtl_kasir <>'OMI'
AND dtl_kasir <>'BKL'
THEN 'REGULER'
ELSE 'OMI'
END dtl_tipemember
FROM
(SELECT sls.TRJD_TRANSACTIONTYPE AS dtl_rtype,
TRUNC(sls.TRJD_TRANSACTIONDATE) AS dtl_tanggal,
TO_CHAR(sls.TRJD_TRANSACTIONDATE,'yyyymmdd')
|| sls.TRJD_CASHIERSTATION
||sls.TRJD_CREATE_BY
|| sls.TRJD_TRANSACTIONNO
||sls.TRJD_TRANSACTIONTYPE AS dtl_struk,
sls.TRJD_CASHIERSTATION AS dtl_stat,
sls.TRJD_CREATE_BY AS dtl_kasir,
sls.TRJD_TRANSACTIONNO AS dtl_no_struk,
sls.TRJD_SEQNO AS dtl_seqno,
SUBSTR(sls.TRJD_PRDCD,1,6)
|| '0' AS dtl_prdcd_ctn,
sls.TRJD_PRDCD AS dtl_prdcd,
prd.PRD_DESKRIPSIPANJANG AS dtl_nama_barang,
prd.PRD_UNIT AS dtl_unit,
prd.PRD_FRAC AS dtl_frac,
prd.prd_kodetag AS dtl_tag,
sls.TRJD_FLAGTAX1 AS dtl_bkp,
sls.TRJD_QUANTITY * prd.PRD_FRAC AS dtl_qty_pcs,
sls.TRJD_QUANTITY AS dtl_qty,
sls.TRJD_UNITPRICE AS dtl_harga_jual,
sls.TRJD_DISCOUNT AS dtl_diskon,
CASE
WHEN sls.TRJD_FLAGTAX1 ='Y'
AND sls.trjd_create_by IN('OMI','BKL')
THEN sls.TRJD_NOMINALAMT*11/10
ELSE sls.TRJD_NOMINALAMT
END dtl_gross,
CASE
WHEN sls.TRJD_FLAGTAX1 ='Y'
AND sls.trjd_create_by NOT IN('OMI','BKL')
THEN sls.TRJD_NOMINALAMT /11*10
ELSE sls.TRJD_NOMINALAMT
END dtl_netto,
CASE
WHEN PRD.PRD_UNIT = 'KG'
THEN sls.TRJD_QUANTITY * sls.TRJD_BASEPRICE/1000
ELSE sls.TRJD_QUANTITY * sls.TRJD_BASEPRICE
END dtl_hpp,
sls.TRJD_DIVISIONCODE AS dtl_k_div,
SUBSTR(sls.trjd_division,1,2) AS dtl_k_dept,
SUBSTR(sls.trjd_division,3,2) AS dtl_k_katb,
sls.TRJD_CUS_KODEMEMBER AS dtl_cusno,
cus.cus_namamember AS dtl_namamember,
cus.cus_flagmemberkhusus AS dtl_memberkhusus,
cus.cus_kodeoutlet AS dtl_outlet,
cus.cus_kodesuboutlet AS dtl_suboutlet
FROM tbtr_jualdetail sls,
TBMASTER_PRODMAST prd,
tbmaster_customer cus
WHERE sls.TRJD_PRDCD = prd.PRD_PRDCD (+)
AND sls.trjd_cus_kodemember = cus.cus_kodemember (+)
AND TRUNC(sls.TRJD_TRANSACTIONDATE)=TRUNC(sysdate)
AND sls.trjd_recordid IS NULL
AND sls.trjd_quantity <> 0
)), TBMASTER_PRODMAST
WHERE DTL_PRDCD_CTN = PRD_PRDCD AND DTL_STAT IN ('23')GROUP BY PRD_PRDCD,
PRD_DESKRIPSIPANJANG, PRD_UNIT, PRD_FRAC, PRD_KODETAG;
SELECT * FROM TAB WHERE TNAME LIKE '%SETTING%';
edp2@yog20
SELECT COUNT(*) FROM TBMASTER_CUSTOMER WHERE CUS_KODEIGR =
( SELECT PRS_KODEIGR FROM TBMASTER_PERUSAHAAN ) AND
(TRUNC(NVL(CUS_TGLMULAI,SYSDATE+1)) <= TRUNC(SYSDATE)
OR TRUNC(NVL(CUS_TGLREGISTRASI,SYSDATE+1)) <= TRUNC(SYSDATE))
AND NVL(CUS_FLAGMEMBERKHUSUS,'N') ='Y'
AND CUS_NAMAMEMBER <>'NEW'
AND CUS_RECORDID IS NULL
AND CUS_KODEMEMBER NOT IN ( SELECT TKO_KODECUSTOMER FROM
TBMASTER_TOKOIGR );
=RIGHT(CONCATENATE("0000",B2),7)
=RIGHT(CONCATENATE("0000",A1),16)
=====//FTP DTA4//====
[Link]
====//CEK FLAG TMI//===
SELECT * FROM TBTR_JUALDETAIL_INTERFACE
WHERE TRJD_CUS_KODEMEMBER IN (SELECT CUS_KODEMEMBER FROM TBMASTER_CUSTOMER WHERE
CUS_RECORDID IS NULL AND CUS_KODEIGR='06'
AND CUS_JENISMEMBER='T' AND NVL(CUS_FLAGMEMBERKHUSUS,'T')='Y') AND TRJD_FLAGTMI NOT
IN ('Y');
====//CEK INTRANSIT KLIK IGR//===
select sum(qtyintransit*obi_hpp) from (
select nvl(obi_qtyintransit,0)qtyintransit, obi_hpp
from tbtr_obi_d where trunc(obi_tgltrans) between
to_date('01/08/2022','dd/mm/yyyy')
and to_date('31/08/2022','dd/mm/yyyy'));
item-item klik indogrosir yang sudah di picking tetapi belum distruk terlampir.
====//CEK POIN MEMBER//===
SELECT POR_KODEMEMBER, NVL(PEROLEHAN_POIN,0)-NVL(PENGGUNAAN_POIN,0) AS POIN FROM
(SELECT POR_KODEMEMBER,SUM(POR_PEROLEHANPOINT) PEROLEHAN_POIN FROM
TBTR_PEROLEHANMYPOIN WHERE TRUNC(POR_CREATE_DT)>='01-DEC-2020' GROUP BY
POR_KODEMEMBER)
LEFT JOIN
(SELECT POT_KODEMEMBER,SUM(POT_PENUKARANPOINT) PENGGUNAAN_POIN FROM
TBTR_PENUKARANMYPOIN WHERE TRUNC(POT_CREATE_DT)>='01-JAN-2021' GROUP BY
POT_KODEMEMBER) ON
(POR_KODEMEMBER=POT_KODEMEMBER) WHERE POR_KODEMEMBER='680916';
====//USER PEMETAAN MEMBER//===
USER : IGRYGY
PASS : 12345
===//SQL MEMBER MERAH DETAIL//===
select dtl_cusno kd_member, dtl_namamember nama,CUS_KODEOUTLET OUTLET,
CUS_KODESUBOUTLET SUBOUTLET,CUS_JARAK JARAK,
SUM(CASE WHEN DTL_PRDCD_CTN NOT IN (SELECT NON_PRDCD FROM
TBMASTER_PLUNONPROMO) THEN DTL_GROSS END) SALES_GROSS_Non,
SUM(CASE WHEN DTL_PRDCD_CTN NOT IN (SELECT NON_PRDCD FROM
TBMASTER_PLUNONPROMO) THEN DTL_NETTO END) SALES_NETT_Non,
SUM(CASE WHEN DTL_PRDCD_CTN NOT IN (SELECT NON_PRDCD FROM
TBMASTER_PLUNONPROMO) THEN DTL_MARGIN END) MARGIN_Non,
SUM(DTL_GROSS) SALES_GROSS,
SUM(DTL_NETTO) SALES_NETT,
SUM(DTL_MARGIN) MARGIN,
SUM(CASE WHEN (DTL_K_DIV||DTL_K_DEPT||DTL_K_KATB)='4 4101' THEN DTL_NETTO END)
NETT_TELUR,
SUM(CASE WHEN (DTL_K_DIV||DTL_K_DEPT||DTL_K_KATB)='4 4101' THEN DTL_MARGIN END)
MARGIN_Telur,
COUNT(DISTINCT(DTL_PRDCD_CTN)) Item,
COUNT(DISTINCT(DTL_TANGGAL)) KUNJ,
Kunj_akhir,
Alamat,
Kelurahan,
Kodepos,
Kecamatan,
Kota,
idgroukat,
Grup,
SubGrup,
Kategori,
Subkatg,
Kelurahan_rumah,
Kecamatan_rumah,
Kota_rumah
from yog_detail_struk
Left join Tbmaster_customer on dtl_cusno=cus_kodemember
Left join
(select jh_cus_kodemember,count(jh_transactiondate) Jml_kunj,
max(trunc(jh_transactiondate)) Kunj_akhir from tbtr_jualheader group by
jh_cus_kodemember)
on DTL_CUSNO =jh_cus_kodemember
Left join
(select crm_kodemember Kode,crm_alamatusaha1 Alamat,crm_alamatusaha4
Kelurahan,crm_alamatusaha3 Kodepos,pos_kecamatan Kecamatan,crm_alamatusaha2 Kota,
crm_idgroupkat idgroukat,Crm_group Grup,crm_subgroup SubGrup,Crm_Kategori
Kategori,
Crm_Subkategori Subkatg from tbmaster_customercrm left join tbmaster_kodepos
on crm_alamatusaha3=pos_kode
where crm_recordid is null and crm_alamatusaha4=pos_kelurahan and
crm_kodeigr='06')
On Dtl_cusno=Kode
Left join
(select cus_kodemember Kodemember,cus_alamatmember1 Alamatrumah,cus_alamatmember4
Kelurahan_rumah,pos_kecamatan Kecamatan_rumah, cus_alamatmember2 Kota_rumah from
tbmaster_customer left join tbmaster_kodepos
on (cus_alamatmember3=pos_kode and cus_alamatmember4=pos_kelurahan)
where cus_recordid is null and cus_kodeigr='06' and
nvl(cus_flagmemberkhusus,'T')='Y')
on Dtl_cusno=Kodemember
where dtl_tanggal between '01-Oct-22' and '31-Oct-22' and
nvl(dtl_memberkhusus,'T')='Y'
group by dtl_cusno, dtl_namamember, CUS_KODEOUTLET, CUS_KODESUBOUTLET, CUS_JARAK,
Kunj_akhir, Alamat, Kelurahan, Kodepos, Kecamatan, Kota, idgroukat, Grup, SubGrup,
Kategori, Subkatg, Kelurahan_rumah, Kecamatan_rumah, Kota_rumah;
SELECT DISTINCT(TO_CHAR(FTP6_TGLTRX,'DD/MM/YYYY')) TGL,FTP6_JENIS
FROM IGRYGY.KIRIM_FTP_SD6 WHERE FTP6_TGLTRX >='01-MAR-2023';
