Sending sales to 3e requires connecting to their software via an ODBC connection.
First of all we install the ODBC driver, following the instructions here.
*Attention there is a case that it does not run in 32bit and must be set to 64bit
Next we need to create the following views in the database:
Sales documents
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 |
CREATE OR ALTER VIEW CC_DOCUMENT_HEADER( DOCUMENTID, REF_NUMBER, CREATIONDATE, MODIFIEDDATE, DISTRIBUTORAFM, GLN_RETAILER, AFM_RETAILER, NAME_RETAILER, ADDRESS_RETAILER, CITY_RETAILER, REGION_NAME_DELIVERY, ZIPCODE_RETAILER, MAIN_ACTIVITY, GLN_DELIVERYID, GLN_DELIVERY, ADDRESS_DELIVERY, CITY_DELIVERY, REGION_DELIVERY, ZIPCODE_DELIVERY, INV_TYPE, INV_SEC, INV_NUMBER, INV_DATE, CANCELATION, DOCUMENTID_CANCELING_INVOICE) AS select "pvlhseis"."Aa" DocumentID, doc_abbreviation(eid."Parastatiko",coalesce( "pvlhseis"."Seira",'')||"Ariumospar") ref_number, "pvlhseis"."Hmeromhnia" CreationDate, "pvlhseis"."modified" ModifiedDate, '000000000' DistributorAfm, pel."Aa" GLN_retailer, replace(pel."Afm",'EL','') afm_retailer, coalesce(case when pel."Epvnymia"='' then null else pel."Epvnymia" end,coalesce(pel."Onoma"||' ','')||coalesce(pel."Epiueto",'')) name_retailer, pel."Dieyuynsh" address_retailer, pol."Onomasia" city_retailer, pel."Perioxh" Region_Name_delivery, pel."Tk" zipCode_retailer, (select "epaggelmata"."Epaggelmata" from "epaggelmata" where "epaggelmata"."Aa"=pel."Epaggelma") Main_Activity, case when "pvlhseis"."Paradosh" is null then "pvlhseis"."Kvdikospelath" else "pvlhseis"."Paradosh" end GLN_deliveryID, case when "pvlhseis"."Paradosh" is null then pel."Aa" else (select "pelates"."Kvdikos" from "pelates" where "pelates"."Aa"="pvlhseis"."Paradosh") end GLN_delivery, case when "pvlhseis"."Paradosh" is null then pel."Dieyuynsh" else (select "pelates"."Dieyuynsh" from "pelates" where "pelates"."Aa"="pvlhseis"."Paradosh") end address_delivery, null city_delivery, null region_delivery, null zipCode_delivery, eid."Parastatiko" inv_type, "pvlhseis"."Seira" inv_sec, "Ariumospar" inv_number, "pvlhseis"."Hmeromhnia" inv_date, 0 Cancelation, null DocumentID_canceling_invoice from "pvlhseis" left join "pelates" as pel on "pvlhseis"."Kvdikospelath"=pel."Aa" left join "poleis" as pol on pol."Aa"=pel."Polh" left join "eidhpar" as eid on eid."Aa"="pvlhseis"."Parastatiko" where ("pvlhseis"."Ariumospar">-1) and ((eid."Xrevstiko" in (1,6) and eid."Emf_timvn"=1) or (eid."Xrevstiko" in (0,5) and eid."Emf_timvn"=2)) and exists (select first 1 apoi."Aa" from "apouhkh" apoi,"grammes" gri where apoi."Aa"=gri."Eidos" and apoi."custom1"='3E' and gri."Aapar"="pvlhseis"."Aa") --and "pvlhseis"."Parastatiko"=6 --and ("pvlhseis"."Hmeromhnia" >= '4/1/2022 00:00:00' and "pvlhseis"."Hmeromhnia" <= '4/30/2022 23:59:59') ; |
Lines of sales receipts
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 |
CREATE OR ALTER VIEW CC_DOCUMENT_LINES( DOCUMENTID, LINEID, LINENUMBER, SUPPLIERAFM, PRODUCTID, PROD_DESCRIPTION, SKU, DELIVERY_MU, PRICE, DELIVERY_QTY, QTYBASEMU, NET_AMOUNT, ALLOW_PERCENT1, ALLOW_AMOUNT1, ALLOW_PERCENT2, ALLOW_AMOUNT2, ALLOW_PERCENT3, ALLOW_AMOUNT3, TOTAL_ALLOWS, ALLOWQTY, ALLOWCUSTOMER, ALLOWPAYMENTTYPE, TAXABLE_AMOUNT, VAT_PERCENT, VAT_AMOUNT, TOTALVALUE, CREATIONDATE, MODIFIEDDATE) AS select "pvlhseis"."Aa" DocumentID, "grammes"."Aa" LineID, ROW_NUMBER() OVER (PARTITION BY "Aapar" ORDER BY "Aapar") AS LineNumber, null SupplierAfm, "grammes"."Eidos" ProductID, "grammes"."PerigrafhEidoys" prod_description, "grammes"."KvdikosEidoys" sku, (select "monades"."Monades" from "monades" where "monades"."Aa"="grammes"."Monada") delivery_mu, (case eid."Emf_timvn" when 2 then -1 when 0 then 0 else 1 end)*"grammes"."Timh" price, (case eid."Emf_timvn" when 2 then -1 else 1 end)*"Posothta" delivery_qty, (case eid."Emf_timvn" when 2 then -1 else 1 end)*"Posothta" QtyBaseMU, null net_amount, null allow_percent1, null allow_amount1, null allow_percent2, null allow_amount2, null allow_percent3, null allow_amount3, null total_allows, null AllowQty, null AllowCustomer, null AllowPaymentType, (case eid."Emf_timvn" when 2 then -1 when 0 then 0 else 1 end)*"grammes"."Posothta"*("grammes"."Timh"-((cast("grammes"."Timh"*"grammes"."Ekptvsh" as double precision))/100)) taxable_amount, round("grammes"."fpa_",2) vat_percent, "grammes"."Timh"*"grammes"."fpa_"/100 vat_amount, (case eid."Emf_timvn" when 2 then -1 when 0 then 0 else 1 end)*"grammes"."Posothta"*("grammes"."Timh"-((cast("grammes"."Timh"*"grammes"."Ekptvsh" as double precision))/100)+ (("grammes"."Timh"-((cast("grammes"."Timh"*"grammes"."Ekptvsh" as double precision))/100))* "grammes"."fpa_" /100) ) TotalValue, null CreationDate, null ModifiedDate from "grammes", "pvlhseis" left join "pelates" as pel on "pvlhseis"."Kvdikospelath"=pel."Aa" left join "poleis" as pol on pol."Aa"=pel."Polh" left join "eidhpar" as eid on eid."Aa"="pvlhseis"."Parastatiko" where "grammes"."Aapar"="pvlhseis"."Aa" and ("pvlhseis"."Ariumospar">-1) and ((eid."Xrevstiko" in (1,6) and eid."Emf_timvn"=1) or (eid."Xrevstiko" in (0,5) and eid."Emf_timvn"=2)) and exists (select first 1 apoi."Aa" from "apouhkh" apoi where apoi."Aa"="grammes"."Eidos" and apoi."custom1"='3E') ; |
Items
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 |
CREATE OR ALTER VIEW CC_PRODUCTS( PRODUCTID, SUPPLIERAFM, CREATIONDATE, MODIFIEDDATE, DISTRIBUTORAFM, SKU, PROD_DESCRIPTION, DELIVERY_MU, DELIVERY_MU2, DELIVERY_CAPACITY, SUPPLIER_SKU, EAN_CU) AS select "grammes"."Eidos" ProductID, null SupplierAfm, (select "apouhkh"."Hmeromhnia" from "apouhkh" where "apouhkh"."Aa"="grammes"."Eidos") CreationDate, (select "apouhkh"."modified" from "apouhkh" where "apouhkh"."Aa"="grammes"."Eidos") ModifiedDate, '0000000' DistributorAfm, max("grammes"."KvdikosEidoys") sku, max("grammes"."PerigrafhEidoys") prod_description, (select "monades"."Monades" from "monades" where "monades"."Aa"=max("grammes"."Monada")) delivery_mu, null delivery_mu2, null delivery_capacity, null supplier_sku, null EAN_cu from "grammes", "pvlhseis" left join "pelates" as pel on "pvlhseis"."Kvdikospelath"=pel."Aa" left join "poleis" as pol on pol."Aa"=pel."Polh" left join "eidhpar" as eid on eid."Aa"="pvlhseis"."Parastatiko" where "grammes"."Eidos" is not null and "grammes"."Aapar"="pvlhseis"."Aa" and ("pvlhseis"."Ariumospar">-1) and ((eid."Xrevstiko" in (1,6) and eid."Emf_timvn"=1) or (eid."Xrevstiko" in (0,5) and eid."Emf_timvn"=2)) and exists (select first 1 apoi."Aa" from "apouhkh" apoi where apoi."Aa"="grammes"."Eidos" and apoi."custom1"='3E') group by "grammes"."Eidos" ; |
Customers
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 |
CREATE OR ALTER VIEW CC_CUSTOMERS( CUSTOMERID, CREATIONDATE, MODIFIEDDATE, DISTRIBUTORAFM, GLN_RETAILER, AFM_RETAILER, NAME_RETAILER, ADDRESS_RETAILER, CITY_RETAILER, REGION_NAME_DELIVERY, ZIPCODE_RETAILER, MAIN_ACTIVITY, RECCATEGORY1, RECCATEGORY2, RECCATEGORY3) AS select pel."Aa" CustomerID, pel."Hmeromhnia" CreationDate, pel."modified" ModifiedDate, '000000000' DistributorAfm, pel."Aa" GLN_retailer, replace(pel."Afm",'EL','') afm_retailer, coalesce(case when pel."Epvnymia"='' then null else pel."Epvnymia" end,coalesce(pel."Onoma"||' ','')||coalesce(pel."Epiueto",'')) name_retailer, pel."Dieyuynsh" address_retailer, pol."Onomasia" city_retailer, pel."Perioxh" Region_Name_delivery, pel."Tk" zipCode_retailer, (select "epaggelmata"."Epaggelmata" from "epaggelmata" where "epaggelmata"."Aa"=pel."Epaggelma") Main_Activity, null recCategory1, null recCategory2, null recCategory3 from "pvlhseis" left join "pelates" as pel on "pvlhseis"."Kvdikospelath"=pel."Aa" left join "poleis" as pol on pol."Aa"=pel."Polh" left join "eidhpar" as eid on eid."Aa"="pvlhseis"."Parastatiko" where ("pvlhseis"."Ariumospar">-1) and ((eid."Xrevstiko" in (1,6) and eid."Emf_timvn"=1) or (eid."Xrevstiko" in (0,5) and eid."Emf_timvn"=2)) and exists (select first 1 apoi."Aa" from "apouhkh" apoi,"grammes" gri where apoi."Aa"=gri."Eidos" and apoi."custom1"='3E' and gri."Aapar"="pvlhseis"."Aa") group by pel."Aa",pel."Hmeromhnia",pel."modified",pel."Afm",pel."Epvnymia",pel."Onoma",pel."Epiueto",pel."Dieyuynsh",pol."Onomasia",pel."Perioxh",pel."Tk",pel."Epaggelma",pel."Kvdikos" ; |
The results consist of documents that include items that in custom field 1 have the phrase “3E”.
To check that it works correctly, we can download libre office and see if it connects to libre base. Below are the settings in libre base.
Codes for the EMDI database.
Enable Password required.
