Pylon SQL - Λίστα πωλήσεων

sales.sql

SELECT
    DOCENTRIES.HEID,
    DOCENTRIES.HEENTRYDATE,
    DOCENTRIES.HEOFFICIALDATE,
    DOCENTRIES.HEDOCCODE,
    DOCENTRIES.HESTATUS,
    DOCENTRIES.HEDOCNUM,
    DOCENTRIES.HESOURCETYPE,
    DOCENTRIES.HEDCSRID,
    DOCENTRIES.HEDCSRTYPE,
    DOCENTRIES.HECMBRID,
    DOCENTRIES.HEBVATVAL,
    DOCENTRIES.HEBTOTALVAL,
    DOCENTRIES.HEISPRINTED,
    DOCENTRIES.HETRANSFORMED,
    DOCENTRIES.HEISOFFICIAL,
    DOCENTRIES.HECONTID,
    CASE
        WHEN (DOCENTRIES.HETRANSFORMED = 0) THEN 'Όχι'
        WHEN (DOCENTRIES.HETRANSFORMED = 1) THEN 'Μερικώς'
        WHEN (DOCENTRIES.HETRANSFORMED = 2) THEN 'Πλήρως'
        ELSE NULL
    END HETRANSFORMEDCCASE,
    CASE
        WHEN (DOCENTRIES.HESTATUS = 0) THEN 'Έγκυρο'
        WHEN (DOCENTRIES.HESTATUS = 1) THEN 'Ακυρωμένo'
        WHEN (DOCENTRIES.HESTATUS = 2) THEN 'Ακυρωτικό'
        ELSE NULL
    END HESTATUSCCASE,
    CASE
        WHEN (DOCENTRIES.HESOURCETYPE = 0) THEN 'Απροσδιόριστος'
        WHEN (DOCENTRIES.HESOURCETYPE = 1) THEN 'Χρήστης'
        WHEN (DOCENTRIES.HESOURCETYPE = 2) THEN 'Αυτόματη Πληρωμή'
        WHEN (DOCENTRIES.HESOURCETYPE = 3) THEN 'Αυτόματη Είσπραξη'
        WHEN (DOCENTRIES.HESOURCETYPE = 4) THEN 'Μετασχηματισμός'
        WHEN (DOCENTRIES.HESOURCETYPE = 5) THEN 'POS Retail'
        WHEN (DOCENTRIES.HESOURCETYPE = 6) THEN 'Ακύρωση'
        WHEN (DOCENTRIES.HESOURCETYPE = 7) THEN 'CRM'
        WHEN (DOCENTRIES.HESOURCETYPE = 8) THEN 'Εύκολη Εγγραφή'
        WHEN (DOCENTRIES.HESOURCETYPE = 9) THEN 'Εισαγωγή'
        WHEN (DOCENTRIES.HESOURCETYPE = 10) THEN 'Ξενοδοχείο'
        WHEN (DOCENTRIES.HESOURCETYPE = 11) THEN 'Έναρξη Χρήσης'
        WHEN (DOCENTRIES.HESOURCETYPE = 12) THEN 'Item Fiscal Year Start Quantities'
        WHEN (DOCENTRIES.HESOURCETYPE = 13) THEN 'Item Fiscal Year Start Values'
        WHEN (DOCENTRIES.HESOURCETYPE = 14) THEN 'Previous Fiscal Year Order'
        WHEN (DOCENTRIES.HESOURCETYPE = 15) THEN 'Παραγωγή'
        WHEN (DOCENTRIES.HESOURCETYPE = 16) THEN 'Σύνθεση'
        WHEN (DOCENTRIES.HESOURCETYPE = 17) THEN 'Φυσική Απογραφή'
        WHEN (DOCENTRIES.HESOURCETYPE = 18) THEN 'Φάκελοι Κοστολόγησης'
        WHEN (DOCENTRIES.HESOURCETYPE = 19) THEN 'Ομάδα Πρότυπου Χρέωσης'
        WHEN (DOCENTRIES.HESOURCETYPE = 20) THEN 'Κόστος Σύνθεσης'
        WHEN (DOCENTRIES.HESOURCETYPE = 21) THEN 'Κόστος Αποσύνθεσης'
        WHEN (DOCENTRIES.HESOURCETYPE = 22) THEN 'Σύμβαση'
        WHEN (DOCENTRIES.HESOURCETYPE = 23) THEN 'Προσαρμοσμένο'
        WHEN (DOCENTRIES.HESOURCETYPE = 24) THEN 'Commission'
        WHEN (DOCENTRIES.HESOURCETYPE = 25) THEN 'Συναλλαγματικές Διαφορές'
        WHEN (DOCENTRIES.HESOURCETYPE = 26) THEN 'Αναίρεση Μετασχηματισμού'
        WHEN (DOCENTRIES.HESOURCETYPE = 27) THEN 'Exchange Differences Transactions'
        WHEN (DOCENTRIES.HESOURCETYPE = 28) THEN 'Πρόβλεψη'
        WHEN (DOCENTRIES.HESOURCETYPE = 29) THEN 'Mobile Application'
        WHEN (DOCENTRIES.HESOURCETYPE = 30) THEN 'Cashier Opening'
        WHEN (DOCENTRIES.HESOURCETYPE = 31) THEN 'Cashier Closing'
        WHEN (DOCENTRIES.HESOURCETYPE = 32) THEN 'Cashier Deposit'
        WHEN (DOCENTRIES.HESOURCETYPE = 33) THEN 'Cashier Withdrawal'
        WHEN (DOCENTRIES.HESOURCETYPE = 34) THEN 'Προβλέψεις Πιστωτικών Έκπτ.Τζίρου'
        WHEN (DOCENTRIES.HESOURCETYPE = 35) THEN 'Εκκαθάριση Πιστωτικών Έκπτ.Τζίρου'
        WHEN (DOCENTRIES.HESOURCETYPE = 36) THEN 'Πιστωτικά Έκπτ.Τζίρου Έναντι'
        WHEN (DOCENTRIES.HESOURCETYPE = 37) THEN 'Item Reorder'
        WHEN (DOCENTRIES.HESOURCETYPE = 38) THEN 'Material Requirement Planning'
        ELSE NULL
    END HESOURCETYPECCASE,
    CASE
        WHEN (DOCENTRIES.HEDCSRTYPE = 0) THEN 'Πωλήσεις'
        WHEN (DOCENTRIES.HEDCSRTYPE = 1) THEN 'Αγορά'
        WHEN (DOCENTRIES.HEDCSRTYPE = 2) THEN 'Είδος'
        WHEN (DOCENTRIES.HEDCSRTYPE = 3) THEN 'Εισπράξεις Πελάτη'
        WHEN (DOCENTRIES.HEDCSRTYPE = 4) THEN 'Εισπράξεις Χρεώστη'
        WHEN (DOCENTRIES.HEDCSRTYPE = 5) THEN 'Πληρωμές Προμηθευτή'
        WHEN (DOCENTRIES.HEDCSRTYPE = 6) THEN 'Πληρωμές Πιστωτή'
        WHEN (DOCENTRIES.HEDCSRTYPE = 7) THEN 'Λοιπά Παραστατικά Πελάτη'
        WHEN (DOCENTRIES.HEDCSRTYPE = 8) THEN 'Λοιπά Παραστατικά Χρεώστη'
        WHEN (DOCENTRIES.HEDCSRTYPE = 9) THEN 'Λοιπά Παραστατικά Προμηθευτή'
        WHEN (DOCENTRIES.HEDCSRTYPE = 10) THEN 'Λοιπά Παραστατικά Πιστωτή'
        WHEN (DOCENTRIES.HEDCSRTYPE = 11) THEN 'Λοιπά Παραστατικά Λογαριασμού Τράπεζας'
        WHEN (DOCENTRIES.HEDCSRTYPE = 12) THEN 'Κινήσεις Λογαριασμών Τράπεζας'
        WHEN (DOCENTRIES.HEDCSRTYPE = 13) THEN 'Πινάκια Αξιογράφων'
        WHEN (DOCENTRIES.HEDCSRTYPE = 14) THEN 'Συμψηφισμός Πελάτη'
        WHEN (DOCENTRIES.HEDCSRTYPE = 15) THEN 'Συμψηφισμός Χρεώστη'
        WHEN (DOCENTRIES.HEDCSRTYPE = 16) THEN 'Συμψηφισμός Προμηθευτή'
        WHEN (DOCENTRIES.HEDCSRTYPE = 17) THEN 'Συμψηφισμός Πιστωτή'
        WHEN (DOCENTRIES.HEDCSRTYPE = 18) THEN 'Χρεώσεις'
        WHEN (DOCENTRIES.HEDCSRTYPE = 19) THEN 'Λοιπά Παραστατικά Χρημ/μικών Λογ/σμών'
        WHEN (DOCENTRIES.HEDCSRTYPE = 20) THEN 'Διακανονισμός Χρημ/μικών Λογ/σμών'
        WHEN (DOCENTRIES.HEDCSRTYPE = 21) THEN 'Πολλαπλού Σκοπού'
        ELSE NULL
    END HEDCSRTYPECCASE,
    DOCSERIES.HEALLOWCOSTPROFITDISTR DCSRALLOWCOSTPROFITDISTR,
    DOCSERIES.HEDOCTYPEID HEDOCTYPEID,
    DOCSERIES.HESHORTCUT + ' (' + DOCSERIES.HENAME + ')' DCSRDISPLAYNAME,
    COMPBRANCH.HECODE CMBRCODE,
    COMPBRANCH.HENAME CMBRNAME,
    COMENTRIES.HEBILLCSTMID CSTMID,
    COMENTRIES.HEBILLSPLRID SPLRID,
    COMENTRIES.HEPMMTID PMMTID,
    COMENTRIES.HEBTOTALDISCVAL,
    COMENTRIES.HEBNETVAL,
    COMENTRIES.HEMEDAGNT01ID,
    COMENTRIES.HEMEDAGNT02ID,
    PAYMETHODS.HECODE PMMTCODE,
    PAYMETHODS.HENAME PMMTNAME,
    CASE
        WHEN (DOCTYPES.HEBEHAVIOUR = 0) THEN 'Απροσδιόριστος'
        WHEN (DOCTYPES.HEBEHAVIOUR = 1) THEN 'Προσφορά'
        WHEN (DOCTYPES.HEBEHAVIOUR = 2) THEN 'Παραγγελία'
        WHEN (DOCTYPES.HEBEHAVIOUR = 3) THEN 'Δελτίο Παράδοσης'
        WHEN (DOCTYPES.HEBEHAVIOUR = 4) THEN 'Τιμολόγιο'
        WHEN (DOCTYPES.HEBEHAVIOUR = 5) THEN 'Τιμολόγιο Δελτίο Αποστολής'
        WHEN (DOCTYPES.HEBEHAVIOUR = 6) THEN 'Είσπραξη'
        WHEN (DOCTYPES.HEBEHAVIOUR = 7) THEN 'Δελτίο Αυτοπαράδοσης'
        WHEN (DOCTYPES.HEBEHAVIOUR = 8) THEN 'Τιμολόγιο Αυτοπαράδοσης'
        WHEN (DOCTYPES.HEBEHAVIOUR = 9) THEN 'Επιστροφή'
        WHEN (DOCTYPES.HEBEHAVIOUR = 10) THEN 'Πιστωτικό Τιμολόγιο'
        WHEN (DOCTYPES.HEBEHAVIOUR = 11) THEN 'Πιστωτικό Τιμολόγιο Δελτίο Επιστροφής'
        WHEN (DOCTYPES.HEBEHAVIOUR = 12) THEN 'Απόδειξη'
        WHEN (DOCTYPES.HEBEHAVIOUR = 13) THEN 'Πιστωτικό Τιμολόγιο Αυτοπαράδοσης'
        WHEN (DOCTYPES.HEBEHAVIOUR = 14) THEN 'Πιστωτικά Έκπτωσης Τζίρου'
        WHEN (DOCTYPES.HEBEHAVIOUR = 18) THEN 'Έξοδα'
        WHEN (DOCTYPES.HEBEHAVIOUR = 19) THEN 'Έσοδο'
        WHEN (DOCTYPES.HEBEHAVIOUR = 30) THEN 'Απογραφή Έναρξης'
        WHEN (DOCTYPES.HEBEHAVIOUR = 31) THEN 'Εισαγωγή'
        WHEN (DOCTYPES.HEBEHAVIOUR = 32) THEN 'Εξαγωγή'
        WHEN (DOCTYPES.HEBEHAVIOUR = 33) THEN 'Εξαγωγή Εισαγωγή '
        WHEN (DOCTYPES.HEBEHAVIOUR = 35) THEN 'Σύνθεση'
        WHEN (DOCTYPES.HEBEHAVIOUR = 36) THEN 'Αποσύνθεση'
        ELSE NULL
    END HEBEHAVIOURCCASE,
    DOCTYPES.HEBEHAVIOUR,
    DOCTYPES.HEPARTINLNCS,
    DOCTYPES.HEINTRASTATPART,
    DOCTYPES.HELISTINGPART,
    DOCENTRIES.HELNCSID LNCSID,
    DOCENTRIES.HEBOOKCURRID,
    COMENTRIES.HEBILLCSTMID,
    COMENTRIES.HEBILLCSBRID,
    COMENTRIES.HETRANSCURRID,
    heCustomers.HECODE CSTMCODE,
    heCustomers.HENAME CSTMNAME,
    heCustomers.HENAMESOUNDEX CSTMNAMESOUNDEX,
    heTraders.HECNTCID HECNTCID,
    DOCENTRIES.HEBKEPYOVAL U000000565,
    DOCENTRIES.HEBKEPYOVATVAL U000000571,
    DOCTYPES.HENAME U000000840
FROM heDocEntries DOCENTRIES
LEFT JOIN heDocSeries DOCSERIES
    ON (DOCENTRIES.HEDCSRID = DOCSERIES.HEID)
LEFT JOIN heCompanyBranches COMPBRANCH
    ON (DOCENTRIES.HECMBRID = COMPBRANCH.HEID)
INNER JOIN heCommercialEntries COMENTRIES
    ON (DOCENTRIES.HEID = COMENTRIES.HEDENTID)
INNER JOIN hePaymentMethods PAYMETHODS
    ON (COMENTRIES.HEPMMTID = PAYMETHODS.HEID)
LEFT JOIN heCommDocTypes DOCTYPES
    ON (DOCSERIES.HEDOCTYPEID = DOCTYPES.HEID)
LEFT JOIN heCustomers
    ON (COMENTRIES.HEBILLCSTMID = heCustomers.HEID)
INNER JOIN heTraders
    ON (heCustomers.HETRDRID = heTraders.HEID)
WHERE DOCENTRIES.HEDCSRTYPE = 0
AND DOCTYPES.HEBEHAVIOUR <> 2
AND DOCTYPES.HEBEHAVIOUR <> 1
AND DOCENTRIES.HECOMPID = 'd701e34c-a37f-11e3-be7a-f4b7e2d0efba'
-- And PAYMETHODS.HECODE <> 000005

Εχεις script για ισοζυγιο πελατών και προμηθευτών ?