iDempiere ERP: how to add Incorporating Incoterms into Sales Order Window

Trabla: iDempiere ERP: how to add Incorporating Incoterms into Sales Order Window

iDempiere Business Suite, also known as OSGi + ADempiere, is an open source Enterprise Resource Planning (ERP) software. It has also customer relationship management (CRM) and supply chain management (SCM) functions. It is in contrast to proprietary or most other open source ERP solutions driven only by a community of supporters. iDempiere is a winner of Infoworld's Bossie award for best open source software application, awarded in 2015 and 2016.

iDempiere ERP Official Site - http://www.idempiere.org/


Sponsored by: MM Cargo Services http://www.mmcargoservices.co.za/

Precondition: you've installed iDempiere on your PC.
If not, tutorial  here - Install iDempiere  ( OSGi + ADempiere) ERP with PostgreSQL 9.4 on Windows 7 x64 - https://codingtrabla.blogspot.com/2016/08/install-idempiere-erp-on-windows-7-x64.html

Solving:

1. Create database table in iDempiere ERP database for incorporating incoterms 

- run pgAdmin III ( PostgreSQL GUI Client ) and click on your iDempiere database

- click on "SQL" button in pgAdmin III ( PostgreSQL GUI Client )

- copy paste following SQL query to create mm_incoterms table, and click 
execute query button

CREATE TABLE adempiere.mm_incoterms
(
-- Primary Key
mm_incoterms_id numeric(10,0) NOT NULL,

-- Mandatory Fields Start
ad_client_id numeric(10,0) NOT NULL,
ad_org_id numeric(10,0) NOT NULL,
isactive character(1) NOT NULL DEFAULT 'Y'::bpchar,
created timestamp without time zone NOT NULL DEFAULT now(),
createdby numeric(10,0) NOT NULL,
updated timestamp without time zone NOT NULL DEFAULT now(),
updatedby numeric(10,0) NOT NULL,
-- Mandatory Fields End

name character varying(60) NOT NULL,
description character varying(255),

-- CONSTRAINTS Start
CONSTRAINT mm_incoterms_pkey PRIMARY KEY (mm_incoterms_id),
CONSTRAINT mm_incoterms_isactive_check CHECK (isactive = ANY (ARRAY['Y'::bpchar, 'N'::bpchar]))
-- CONSTRAINTS End
   
)
WITH (
  OIDS=FALSE
);
ALTER TABLE adempiere.mm_incoterms
  OWNER TO adempiere;

- result of executing query 

Query returned successfully with no result in 1.1 secs.

2. Update Sales Order Table ( c_order ) add reference to incoterms table ( mm_incoterms )

- copy & paste following query to add new field and constraint to  mm_incoterms
in c_order table


ALTER TABLE adempiere.c_order ADD COLUMN mm_incoterms_id numeric(10,0);

ALTER TABLE adempiere.c_order
  ADD CONSTRAINT mmincoterms_corder FOREIGN KEY (mm_incoterms_id)
      REFERENCES adempiere.mm_incoterms (mm_incoterms_id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED;

- click "Execute pgScript" button in pgAdmin III

- result of execution SQL query

[QUERY    ] ALTER TABLE adempiere.c_order ADD COLUMN mm_incoterms_id numeric(10,0)
[QUERY    ] ALTER TABLE adempiere.c_order
              ADD CONSTRAINT mmincoterms_corder FOREIGN KEY (mm_incoterms_id)
                  REFERENCES adempiere.mm_incoterms (mm_incoterms_id) MATCH SIMPLE
                  ON UPDATE NO ACTION ON DELETE NO ACTION DEFERRABLE INITIALLY DEFERRED

3. Login as SuperUser into iDempiere ERP

- login / password - SuperUser / System ( default ),
enable "Select Role" checkbox and click "OK" button

- client / role - System / System Admin...

4. Register Incoterms Table ( mm_incoterms ) in iDempiere ERP Application Dictionary

- in Menu Search field type - "Table and Column" and press search button
and click on "New" button

- enter data to new record
DB Table Name               -> MM_Incoterms
Name                               -> MM_Incoterms
Data Access Level          -> Client + Organization
and click "Save" button

- click on "Process" button,
than click on "Create Columns from DB"

- in "Create Columns from DB" dialog click "OK" button

- in dialog "Create Columns from DB - Process Completed Successfully"
click "OK" button

- result - all fields in database table mm_incoterms now are in iDempiere ERP

5. Create New iDempiere Window for Incoterms

- in Menu Search field type - "Window, Tab & Field" and press search button
and click on "New" button

- type Name -> MM_Incoterms  and click "Save" button

- click "New" button in Tab "Tab"

- enter data to new record
type Name  -> MM_Incoterms
select Table  -> MM_Incoterms_MM_Incoterms
and click "Save" button

- click "Process" button

- click "Create Fields" button

- in dialog "Create Fields - Create Field from Table Column ..." 
click "OK" button

- in dialog "Create Fields - Process completed successfully"
click "OK" button

- result - new fields created ( from window "Table and Column" )

6. Connect ( set reference ) Incoterms iDempiere Table to Incoterms iDempiere Window

- in Menu Search field type - "Table and Column" and press search button
and click on it

- in dialog "Lookup Record" 
type DB Table Name -> MM_Incoterms
and click "Ok" button

- find field "Window"

- select "Window" -> "MM_Incoterms"

- click "Save" button

7. Create iDempiere Menu item for Incoterms iDempiere window

- in Menu Search field type - "Menu" and press search button
and click on "New" button

- type Name -> MM_Incoterms
- select Action -> Window

- after selection Action->Window new field will appear - "Window"
select Window -> MM_Incoterms

- click "Save" button

8. Add Incoterms field ( MM_Incoterms_ID ) into iDempiere Sales Order Table ( C_Order)

- - in Menu Search field type - "Table and Column" and press search button
and click on it

- in dialog "Lookup Record" 
type DB Table Name -> C_Order
and click "Ok" button

- in tab "Column" click "New" button

- find field "System Element" and click green arrow

- in dialog "Info System Element"
1) Type in DB column Name -> MM_Incoterms
2) Click "Reload" button
3) Select "MM_Incoterms_ID" in result set
4) Click "OK" button

- scroll down, find field "Reference"

- in Reference field select "Table " ( Default is "Table Direct" )

- click "Save" button

9. Add Incoterms fields into iDempiere Sales Order Window 

- in Menu Search field type - "Window, Tab & Field" and press search button
and click on it button

- type "Name" -> Sales Order  and click "OK" button

- select tab "Tab" , find "Sales Order" Tab, click "Edit record" 

- select tab "Field" , click "New record" button

- type "Name" - MM_Incoterms ,
select Column -> MM_Incoterms_ID_MM_Incoterms

- click Save button

10. Run "Role access update" process

- close all opened iDempiere windows (on page )

- in Menu Search field type - "Role Access Update" and press search button
and click on it

- select Client -> "System" ( will update all roles in all clients )
and click "OK" button

- click "Close Window" button

11. Run Cache Reset process

- click "OK" button

- click "Close Window" button

12. Login into Client Admin account ( we will use  default GardenWorld)

- logout from System Admin...

- login into Client Admin account ( we will use  default GardenWorld)

13. Fill Incoterms Window with values

Ex Works
Free Carrier
Carriage Paid to
Carriage & Insurance Paid To
Delivered At Terminal
Delivered At Place
Delivered Duty Paid
Free Alongside Ship
Free On Board
Cost & Freight
Cost Insurance & Freight

- in Menu Search field type - "MM_Incoterms" and press search button
and click on "New" button

- foreach Incoterm
1) create new record
2) Type Name
3) Click Save button

- result - all incoterms

14. Create new Sales Order - check Incoterms Field + Zoom to Incoterms Dictionary window

Hooray !!! Well Done !!!