red1.org

[riche]

[red1]

i m not going to do that spelling correction and printbilloflading checking just yet to isolate and solve first the 2 problems u now facing below.

1.1) The IDs shuld autopopulate due to their definition in the Document Sequence. So pls check the tables existence there. Go to Document Sequence and see C_BOL, C_BOL_Line and C_BOL_Pack records.
1.2) Now if u have done all and it still doesnt work, take note what are the exact error prompts so we can figure other causes.

2) Pls put a common phrase or symbol such as 'AA' in any two SO's BOLNOs to see them merge into a single BOL. I keep repeating this step to u and suspect that i m missing your reqs altogether.

[riche]

I got the BOL window issues worked out (so I can manually create a new BOL), and figured out that you can't leave the BOLNO field blank (we should discuss further), but still have problems generating them using the Generate BOL process.

When I try to generate a BOL, I get the following errors (summarized for simplicity) even though it seems to generate most of the BOL, BOL_Lines, & PackCode Total:
18:09:03.056 MBOLLine.saveNew: C_BOL_Line - C_BOL_Line_ID=1000015 [28]
===========> CPreparedStatement.executeQuery: executeQuery - AppsServer error [28]
java.rmi.ServerException: RemoteException occurred in server thread; nested exception is:
java.rmi.ServerException: RuntimeException; nested exception is:
java.lang.RuntimeException: java.sql.SQLException: ORA-00942: table or view does not exist
***
-----------> CPreparedStatement.executeQuery: executeQuery - execute locally [28]
===========> BOL_Generate.createPackTotal: Select Qty, Weight from C_BOL_Lines where C_BOL_ID=? AND PACKC=? [28]
java.sql.SQLException: ORA-00942: table or view does not exist
; State=42000; ErrorCode=942
***
18:09:03.056 MBOLPack.saveNew: C_BOL_Pack - C_BOL_Pack_ID=1000016 [28]
===========> CPreparedStatement.executeQuery: executeQuery - AppsServer error [28]
java.rmi.ServerException: RemoteException occurred in server thread; nested exception is:
java.rmi.ServerException: RuntimeException; nested exception is:
java.lang.RuntimeException: java.sql.SQLException: ORA-00942: table or view does not exist
***
-----------> CPreparedStatement.executeQuery: executeQuery - execute locally [28]
===========> BOL_Generate.createPackTotal: Select Qty, Weight from C_BOL_Lines where C_BOL_ID=? AND PACKC=? [28]
java.sql.SQLException: ORA-00942: table or view does not exist
; State=42000; ErrorCode=942
***
18:09:03.056 MBOLPack.saveNew: C_BOL_Pack - C_BOL_Pack_ID=1000017 [28]
18:09:03.056 MBOLLine.saveNew: C_BOL_Line - C_BOL_Line_ID=1000016 [28]
===========> CPreparedStatement.executeQuery: executeQuery - AppsServer error [28]
java.rmi.ServerException: RemoteException occurred in server thread; nested exception is:
java.rmi.ServerException: RuntimeException; nested exception is:
java.lang.RuntimeException: java.sql.SQLException: ORA-00942: table or view does not exist
***
-----------> CPreparedStatement.executeQuery: executeQuery - execute locally [28]
===========> BOL_Generate.createPackTotal: Select Qty, Weight from C_BOL_Lines where C_BOL_ID=? AND PACKC=? [28]
java.sql.SQLException: ORA-00942: table or view does not exist
; State=42000; ErrorCode=942
***
18:09:03.056 MBOLPack.saveNew: C_BOL_Pack - C_BOL_Pack_ID=1000018 [28]
===========> CPreparedStatement.executeQuery: executeQuery - AppsServer error [28]
java.rmi.ServerException: RemoteException occurred in server thread; nested exception is:
java.rmi.ServerException: RuntimeException; nested exception is:
java.lang.RuntimeException: java.sql.SQLException: ORA-00942: table or view does not exist
***
-----------> CPreparedStatement.executeQuery: executeQuery - execute locally [28]
===========> BOL_Generate.createPackTotal: Select Qty, Weight from C_BOL_Lines where C_BOL_ID=? AND PACKC=? [28]
java.sql.SQLException: ORA-00942: table or view does not exist
; State=42000; ErrorCode=942
***
18:09:03.056 MBOLPack.saveNew: C_BOL_Pack - C_BOL_Pack_ID=1000019 [28]

When I shut down the app server, I don't get all the app server errors, but I still get the rest. FYI, prior to this I was having some app server problems so I had to copy the JBOSS directory from a previous build because my current build was giving me errors when I ran the server. I have no idea why that was happening (I get a clean build) but this seemed to work as a fix for loading of the jboss server problem. Either was, a table or view seems to be missing. I know that C_BOL, C_BOL_Line, C_BOL_Pack all exist. Is it the Pack Code lookup that's missing?

The only part of BOL that doesn't seem to be created properly is the PackCode Totals. 2 K lines and 2 C lines were created but were not populated. This would probably be because I was mistaken about the Pack Codes - they should be K & S and this order that I was creating a BOL for was Pack Code S. Otherwise it looks like it created properly, so I don't know why I got the above errors when creating it. Also, each time I ran BOL Generate, it re-created another copy of the BOL, even if the BOL was previously generated for this order.

What are your thought? I will be online around 9pm, but otherwise you can vnc in and look at the result yourself.

-Rich

[riche]

Please make the following changes:
1-I tested the BOL changes you made and I still got an error (another typo). I corrected it in my version, but wanted to make sure you fix it in the CVS version: QTY should be Quantity (only occurs once in an SQL). Once I changed this, I didn't get any more errors.
2-Make it so that Pack Code Description populates from a lookup table. This table should have 3 fields: Pack Code (3 alphanumeric characters), Description (255 Characters), and Rate (integer).
3-Generate_BOL still generates BOL even if it has already been generated. Please make sure it only generates once. Let me know if you are waiting until everything else works or if you didn't mean for this to happen.

Thanks,
Rich

[red1]

1) thanks for that note. will correct here too.

2) understood. hope to finish the packcode lookup today.

3) i noticed and corrected the code again yesterday and CVSed it. i can pass to u some hours later cos i m at my sister's home without my notebook, and shall go back shortly.

[red1]

The PackCode reference table is here http://red1.org/barn/HutzlerCompiereUSA/sources/PackCode.zip .

Run as usual
XML2AD > CreateBOL.sql , ADBOL.xml
GenModel 'A'
XML2AD > ViewBOL.xml

Pls run by me again on what to do with the Description and Rate fields. Are they to appear sumwer?

I will work out the java dynamic handling of the codes later.

[red1]

Codes formulated for this. Pls collect from CVS - MPackCode.java and BOL_Generate.java

Now the system will lookup the C_PackCode table for the K and C, etc. So, u have to create those 2 new records accordingly in the BOL>Reference window/tab.

[riche]

Did you come up with what the choices are about how to deal with merging BOLs (and how to deal with populating BOLNO)?

Please post where we are on this.

Thanks,
Rich

[red1]

Here are the options discussed and we are at option 1:
1) SO's BOLNOs left blank for user to key in a code where matching will result in a merged BOL.

2) Same as 1 but system check if they match the SO's DocNo instead

3) Same as 2 and consider NULL value as single BOLs.

I still find merit in Option (1) but u may prefer (3). I will check out about how (2),(3) can be done first.

[riche]

I'm leaning towards option 3, but let me know if it can be done. Also, as another option, consider autopopulating BOLNO with the actual BOL doc no that will be used to create the resulting BOL. Then, for matching SO, filling in BOLNO to be the same.

Thanks,
Rich

[riche]

I just wanted to check on the status of the final BOL changes. Have you been able to finish it?

Thanks,
Rich

[red1]

[red1]

Option 3 is done and CVSed (BOL_Generate.java).

Logic:
1) System shall go thru all Sales Orders that hasn't been processed before.
2) Any null or empty BOLNO in SO will be a new BOL with DocumentNo as its BOLNO (C_BOL.Name).
3) Any SO's DocumentNo or BOLNO that is same to a new C_BOL.Name is merged to it.
HowTo) Thus to merge other SOs to a SO.DocumentNo, just key in the DocNo to those SO.BOLNOs. That target SO.DocumentNo need not have its BOLNO filled.

Code reference:

[riche]

I've been doing more BOL testing and here are some issues I found:
1-If BOLNO in Sales Order is blank, BOL is not generated during "generate BOL". Your previous post said if it was blank it would just not be combined with another SO, I thought.
2-Generate BOL creates seperate BOL for each sales order line. In other words a single SO with 2 lines (not being merged with another BOL) outputed 2 BOLs, each with only 1 of the lines from the original Sales Order.

-rich

[red1]

I have corrected the blank BOLNO in SO with 2 lines shuld generate into a single BOL. CVSed BOL_Generate.java. My mistake was i put an AND clause when it shuld be an OR clause

[riche]

[red1]

Ok now i realise the issue. I was confused between header check with detail level checks. Toh the codes claims to take care of that i will test and ensure for multiple lines within same SO.

[red1]

The BOL generate part is ok but found out its not so for PackCode. Already resolved and CVSed.

Tested with my own full set of data having an SO with blank BOLNO + 2 lines with prod.packingcode; merged non-blank BOLNO Orders, all seems OK.

Only code changed is still BOL_Generate.java

[red1]

To aid in testing and retesting, reset the tested cases by running the SQLs below:

1) For Deleting all BOLs generated:
a) Delete from C_BOL
b) Delete from C_BOL_Line
c) Delete from C_BOL_Pack

2) For Reseting SOs to ungenerated state
a) Update C_Order set C_BOL_ID = null

Item (2) is compulsory as progam will not generate further when its SO.C_BOL_ID flag is always set during run.

[riche]

So BOL seems to work properly when creating BOLs from a single Sales Order. I do see a problem with the merging of BOLs. I created a Hutzler SO, filled in the BOLNO field with 1234, then generated BOL. BOL was created properly. Then I created a Progressus SO, filled in the BOLNO field with 1234, then generated BOL. It should have added the lines to the existing BOL 1234, but instead it created another BOL, also with name = 1234. Am I doing something wrong or was the functionality not finished? Please help me get this working.

The next step, once we have proved this works, would be to combine generate BOL and generate shipment (manual & batch) into a single process. I don't know if it is best to create a new process that combines the 2 or to add the functionality to generate shipment. I would lean towards the first choice. Do you think this would be easy to do?


-Rich

[red1]

Thats gud to hear that it really works, after all!! .

And u have demonstrated a new loop that i haven't tot about - Creating BOLs with same Nos., consecutively.

I dont think the codes take care of saved BOLNos (residing in file) during execution. It needs to read in the BOL File first. I will incorporate that.

Will research up on your other posts too.

[riche]

While you work on the merging of BOLs, let's go back to printing. The printing doesn't work (the entire page is blank). I think this should be a quick fix. I believe is caused by the Org_Location_ID in C_BOL_HEADER_V sql. Here's my reasoning:

1-I get the following error in terminal when I try to print BOL:
===========> MLookupFactory.getLookup_TableDirEmbed: No Identifier records found: Org_Location_ID [175]
===========> CPreparedStatement.executeQuery: executeQuery - AppsServer error [175]
java.rmi.ServerException: RemoteException occurred in server thread; nested exception is:
java.rmi.ServerException: RuntimeException; nested exception is:
java.lang.RuntimeException: java.sql.SQLException: ORA-00936: missing expression

at org.compiere.util.CPreparedStatement.executeQuery(CPreparedStatement.java:110)
------etc.

2-C_BOL_HEADER_V only has 25 records while C_BOL has 35 records. I will email you the data from these files.

Also, it is a little unclear what the unique ID field in C_BOL_HEADER_V. Is M_Inout_ID = C_BOL_ID. Why do you use M_InOut_ID? C_BOL_ID <> M_InOut_ID, right?

Thanks,
Rich

[red1]

I wrongly tested, and later did correctly test.

1) I go to the ClientAdmin.BOL Window where i can view the BOL records. Then i click on the Print icon of the menu bar. It did generate a BOL. I click on its PrintFormat (edit icon) and notice its as below:

http://red1.org/barn/HutzlerCompiereUSA/operational_documents/BOLFormats.gif

So pls check if yours is so. I didnt incorporate the BOLPack format as yet, toh i did submit for u to do so (refering to earlier part of this thread). Proof that this stage can work first.

I didnt put in the Org_Location_ID into the PrintFormat above. So when i did, i replicated your error

Then i remedy that with
a) Table/Column = C_BOL_Header_V
b) Column = Org_Location_ID
c) Reference = Location(Address) instead of TableDir

2) I got the same number of records in C_BOL and C_BOL_Header.

About the UniqueID field, wer did u see that? I cant find it in PrintFormats, SQL or Table/Column related to CBOL.

[riche]

I get a different error now when I try to print BOL:
===========> DataEngine.loadPrintData: null - ORA-00904: "C_BOL_HEADER_V"."C_BOL_HEADER_V_ID": invalid identifier

[red1]

BOL_Generate should now update previous BOLs, also corrected further PackCode totallings to be recalculated instead of accumulated at each run as its the case presently.

Code reference