red1.org

[red1]

Topic 3 shall cover the Import Order – loader format setup. Then we convert the dbf files to CSV. A close look at the ImportOrder.java. 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.

[red1]

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.

[red1]

TODO List to make all this happen!
1. ImportOrder.java 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.

KEYFIELDS IN I_ORDER TO BE USED (SET IN LOADER)
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

CREATE EXTRA KEYFIELDS THRU APP. DICT. FOR I_ORDER
a. OrgValue
b. SalesRepValue

CHANGES IN ImportOrder.java
• 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)

[Jiang Nan]

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.

[red1]

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 www.compiere.org 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! . But i like to write that too!
Thanks for your comment and advice on this other tool, i will look it up too.

red1

[der_bonner]

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?

cheers

[trifon]

Hi,

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)

Regards,
Trifon
_________________
Trifon Trifonov

[neilg]

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!

Neil