Protel Simphony Custom

Döviz Güncelleme
MC Otomatik
MC Aktarım

MC Otomatik

Dün

Geçen Hafta

Geçen Ay

Mağazalar

SQL SORGU:

SELECT
            ''''
            || TO_CHAR(g.businessdate,'YYYY-MM-DD hh24:mi:ss')
            || '''' AS businessdate,
            ( ( l.orglocationref * 1000 ) + r.revenuecenterposref ) AS store,
            g.postransref AS objnum,
            CASE
                    WHEN coalesce(SUM(g.linetotal2),0) <> 0   THEN SUM(g.linetotal2)
                    ELSE coalesce(SUM(g.reportlinecount),0)
                END
            AS qty,
            coalesce(SUM(g.reportlinetotal+nvl(dsc.dsctotal,0)),0) AS total,
            '1' AS bir,
            CASE
                    WHEN SUM(reportlinetotal) <> 0   THEN round( (SUM(reportlinetotal) - (SUM(reportlinetotal) - SUM(reportincltaxtotalext) ) ) / (SUM(reportlinetotal
) - SUM(reportincltaxtotalext) ),2)
                    ELSE 0
                END
            AS kdv,
            '0' AS sifir,
            '1' AS bir2
        FROM
            location_activity_db.guest_check_line_item_hist g 
           join location_activity_db.vmm_locations l on  g.locationid = l.locationid
           join location_activity_db.vmm_revcenters r on g.revenuecenterid = r.revenuecenterid
           left join (select guestcheckid,parentdtlid,sum(reportlinetotal) dsctotal from location_activity_db.guest_check_line_item_hist tp 
           where detailtype=2 and tp.BUSINESSDATE>=:DATESTART and tp.BUSINESSDATE<=:DATESTOP and locationid in ({0})
           group by guestcheckid,parentdtlid) dsc on g.guestcheckid=dsc.guestcheckid and g.dtlid=dsc.parentdtlid
        WHERE
            g.detailtype = 1
            AND   g.BUSINESSDATE>=:DATESTART and g.BUSINESSDATE<=:DATESTOP
            AND   l.locationid in ({0})
        GROUP BY
            g.businessdate,
            l.orglocationref,
            r.revenuecenterposref,
            g.postransref