Last active
January 6, 2020 18:39
-
-
Save nikolasd/a9a38aa600be72610a29bbcfd07b69bc to your computer and use it in GitHub Desktop.
Pylon SQL - Λίστα πωλήσεων
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
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 |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
Εχεις script για ισοζυγιο πελατών και προμηθευτών ?