Case II - Topic 3: setup ImportOrder, batch run OrderProcess

All Case Experiences in Topic format. Easy reading mode for contextual understanding. Quickie snapshot and howtos Files are linked into

Case II - Topic 3: setup ImportOrder, batch run OrderProcess

Postby red1 » Fri Jul 09, 2004 12:03 pm

Topic 3 shall cover the Import Order – loader format setup. Then we convert the dbf files to CSV. A close look at the Building a batch process for high volume POS Orders. Linking it to the Compiere Menu with parameters. We also parse the orders to various walk-in BPartners of the credit-cards for final settlements with the respective merchants.
Having specific BPartners for each credit card operator allows us to assign specific accounting elements to them. When the credit card merchant pays the client, there will be visibility of the credit card fulfillment or variance thereof. To assign the BPartner in the Import Order begins prior to the import. This is done via a Delphi program that parses the data and insert the PaymentRule as B for cash, or K for credit card. To proceed without modification, the Description field of the OrderLine is used to house the credit card details. Also the PaymentTermValue is used to house the PaymentRule as the later is not created in the I_Order table. I avoided extending too much the structure to avoid creeping bugs (debatable) but more importantly to Proof-of-Concept Compiere’s case as this is again a pioneer installation. Isn’t the whole world watching? Especially big brothers sap and oracle. :twisted:
Last edited by red1 on Mon Jul 12, 2004 10:19 am, edited 2 times in total.
Site Admin
Posts: 2762
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

sub-topic - Batch Process Sales Orders

Postby red1 » Fri Jul 09, 2004 4:48 pm

When you are in a Sales Order, there is a process button on the bottom right that summons a SQL process - 'C_Order_Post.sql. We will reuse that process as a Batch process. This means it will have to run for all records in question rather than a single Order.
You have to define a new Report & Process from the Application Dictionary. Make this in turn to call B_Order_Pos.sql . The modifications are minimal, just the reception area for parameters and getting a CURSOR to loop into the I_Order.
Site Admin
Posts: 2762
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Work progress

Postby red1 » Tue Jul 13, 2004 1:00 pm

TODO List to make all this happen!
1. changes - to allow keyfield conversion
2. Create extra I_Order fields – not present for keyfields
3. Build Import File Loader – to select/activate I_Order fields used in Import
4. Create Standard Walk-In Business Partners for CASH, VISA, MASTER AMEX
- for Accounts posting and merchant bank clearing management.
5. Create ImpOrderDelete.sql – to reverse Sales Orders import as exit plan.

a. DateOrdered format dd/MM/yyyy
b. OrgValue
c. PaymentTermValue
d. DocumentNo
e. SalesRepValue
f. DocTypeName
g. C_Tax_ID
h. ProductValue
i. PriceActual
j. QtyOrdered
k. Description

a. OrgValue
b. SalesRepValue

• Convert OrgValue to Org_ID.
• Convert SalesRepValue to ID
• Set documentNo to combi key Date-Org+PayRule
• set C_BPartner according to VISA/MASTER
• Disable PaymentTerm as taken over by PayRule (POS orders has no terms)
• Rule in C_Order set to K or B (credit card or cash - Compiere hardcode setting)
Last edited by red1 on Thu Jul 15, 2004 10:44 am, edited 2 times in total.
Site Admin
Posts: 2762
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

Postby Jiang Nan » Tue Jul 13, 2004 6:30 pm

So many tables in the oracle database, how do you understand each columns's meaning in the table. since there are no comments. ERStudio is new to me, then I can hardly figure out the relationships between tables. what's different between table i_order and c_order. I think you have read source code, or maybe you have internal develope document:). BTW, You can import data by a oracle tool called sqlldr.
Jiang Nan
Posts: 4
Joined: Thu Jul 08, 2004 2:45 pm

Understanding Compiere Tables

Postby red1 » Thu Jul 15, 2004 10:19 am

Well Jiang,
There are certainly tons of tables in Compiere, and when i first open them for a peek in ERStudio, i was like in a little universe!
Anyway, i hardly refer to the ER, i mostly understood the role and context of such tables from about a year of working with them.
To understand for example C_Order, it was first discovered thru the Application Dictionary. U can refer to for these fundamentals. By looking under Menu, we identify the Window which we want to look into. From there we look up which table the tab is refering to, and so on we get to the Table-Column and thus one can find out that Sales and Purchase Orders uses the same C_Order table. When we enter data, we can quickly use TOAD, or other oracle database manager to peek inside that our data is indeed in that table.
I do not have an internal development document. I am trying to write it up. This Case Workshop is just the beginning. 1 thing i can say is that a real technical commentary will make very boring reading as it will be thick and terse. So i try to use real case scenario to make it less painful! 8) . But i like to write that too! :roll:
Thanks for your comment and advice on this other tool, i will look it up too.

Site Admin
Posts: 2762
Joined: Tue Jul 06, 2004 3:01 pm
Location: Kuala Lumpur, Malaysia

some special needs

Postby der_bonner » Sat May 28, 2005 6:02 pm

Hi board,

in order of red1's brilliant introduction I have some (little) special needs that I can't solve yet...

I have a certain format of order lines that I would like to import in compiere. Well... I try to explain what I need to do:

Businessparters are using a service with recurring charges. I think Compiere is able to handle that. Beneeth this recurring charges there are additional services they use. Reference is a ID numberof the recurring service. So Compiere will regularly produce invoices for the recurring service.

I need to add the additional services to a n recurring service.

Unfortunatly the files providing the additional services have a very special format.

text file with fixed field lenth (easy...)

but: first line has some special header info
second line has some special header info
then many lines follow describing additional services for a service ID

then a summary lilne for the service ID follows and again many lines follow describing another service ID....

Most basics I understood from red1's documentation.

But how to import these mixed lines and how to get them to the subscription?

Any idea how to help?

Posts: 27
Joined: Thu Dec 16, 2004 12:41 am

Postby trifon » Mon Jun 06, 2005 6:11 am


I do not understand fully your format, but if you post some part of it probably we could find some solution.
and in order to import this you will need to change some imports(java source, sql statements)

Trifon Trifonov
Posts: 324
Joined: Thu Dec 23, 2004 11:20 pm
Location: Bulgaria, Sofia

Postby neilg » Sat Jun 25, 2005 4:31 am

I'm also writing something, it's in the contributors corner, which will ease the importing of data (e.g. from CSV) because the row by row processing is made simpler.

Keep watching that spot because it should be finished soon!

Posts: 141
Joined: Thu Jun 16, 2005 1:58 pm

Return to Compiere Case Workshop

Who is online

Users browsing this forum: No registered users and 1 guest