red1.org

[red1]

Generate BOL is now in progress...

This is the Batch Processing Run to Generate Bills of Lading based on Customers, Orders or BOLNo. ( BOLNO will be used as filter key)

>Created Druid for C_BOL and C_BOL_Line in minimal form for POC.
>Generated XML2AD files.
>Executed in local instance.

>Identifier is Name

>Created Report & Process (modified from present BatchPrintBOL)

[red1]

Trifon has mailed me from Cyprus the ImportBOLComment stuff:
latest build http://red1.org/barn/HutzlerCompiereUSA/sources/impBOL.zip

[red1]

I have done the BOL Generate routine but i want to clear one point before i prepare the druid files, SQL and link to the PrintFormat.

1) Does BOL Generate appendsthe OrderLines of similar BOL Nos. together?
I.e. if Order A is BOLNO '001' with 2 lines L1 and L2, Order B is BOLNO '001' with just one line L3, then the generated single BOL has 3 lines from L1, L2, L3?

I have made additional fields to the Order table:
a) BOLNO used as ID for common merging.
b) C_BOL_ID to mark that it has been used during generation and not used unless cleared.

I m now commiting my codes to a new location in Unversity > C-252d.

[riche]

[red1]

Here is the first part of http://red1.org/barn/HutzlerCompiereUSA/sources/BOL.zip .

1) RUN XML2AD on the ADBOL.xml

2) go into SystemAdmin>Table&Column , look for C_BOL and synch just one field in there. Do the same for C_BOL_Line.

3) in your DB Editor, run the alterBOL.sql.

4) RUN GenerateModel 'A'

5) CVS for BOL_Generate.java or take it from the zip and place in your source.

6) SystemAdmin>Process&Report create a new process Generate BOL which has no parameters and classname is org.compiere.process.BOL_Generate.

7) The window part for the BOL tables will arrive tomorrow. Up to here only the C_BOL and C_BOLLine tables are created, and also the batch process.

Further updates will be sent to the same spot.

[red1]

BOL progress & steps to take at US side:

Belated realisation that BOL Main table doesnt keep C_BPartner_ID. Its not stated clearly on the BOLfields.xls. Reckon it should be stated as #14 on the HutzBOL_mark. Otherwise the Location doesnt get tucked right.

RUN ADBOL.xml again from XML2AD to get the new C_BPartner field. Sync the column from Table&Column window.

GenModel 'A'

RUN viewBOL.xml from XML2AD

In ClientAdmin>Role - WindowAccess tab - new record>Window - Bill Of Lading> Read/Write checked

[red1]

Also we need to establish the parent-child relationship in BOLMain / BOL Lines, by going to SystemAdmin>Table&Column>Table(BOLLine)>Column(C_BOL_ID)>Parent link column - checked.

I m now tidying up the codes to fetch BOLComment from BPLocation Product PACKC and WHSLC.

Then the PrintFormat switch for Batch Run and manual invdividual print.

[red1]

EMBARGO - PLS DO NOT DO ANYTHING YET - I DISCOVERED SOME PREREQUISITE ERRORS - LET ME PROVE AND SORT OUT SHORTLY AND THEN GIVE U THE GO AHEAD.

Otherwise i wanted u to follow the following notes:

[red1]

Deleted BillOfLading PF
Added DocumentNo to druid, added manually in Table/Col, synched
GenModel A
Bring into BOL Window
setDocumentNo in BOL_Generate.java
set in C_BOL_Header_v.sql
recreate Table&Column C_BOL_Header_v
copy BOLPrintFormat
Change AD_Table_ID to 1000015 and AD_Client_ID to 0.
Update AD_PrintFormatItem set AD_Client_ID = '0' where AD_PrintFormat_ID = '1000039'
Commit in TOAD
Remove delivery via rule ID from PrintFormatItem (SystemDate row)

Single record print brings problem due to query asking for C_BOL_Header_v_ID which doesnt exist.
Fooled it by creating a dummy namesake ID.

Attach BOLWatermark

SUCCESS!!

Both single and batch BOL printing can now work. Now for me to take some rest and continue

documentation for transfer

But still we have to first get the Detail format setup.
examine BOLLine PF
refering table RV_InOutDetails
goto TOAD, make a copy of that SQL, and setup in TableColumn and PF, remembering to have table_ID as

the main hdr remedy.

[riche]

[red1]

final notes on BOL which is now fully proven

[red1]

To solve Import BOLComment

1) The field is too short. Its only 40 char in I_Bpartner.comments and 80 char in C_BPartner_Location.BOLComment, when your description is much longer. So..
a) adjust in SystemAdmin.Table&Column for the two tables. Set them to text
b) RUN GenModel 'D' and 'A'
c) adjust length in DB Editor for those two field lengths. Or delete those two fields and in SystemAdmin>Table&Column at those 2 fields 'synch columns'

2) Look for this snippet in ImportBPartnerBOLComment:

[trifon]

Sorry, it is my mistake,

but in my code source code is like this:

if (bpLoc.getBOLComment() == null) {
bpLoc.setBOLComment(impBP.getComments());
} else {
bpLoc.setBOLComment(bpLoc.getBOLComment() + "\n" + impBP.getComments());
}

Regards,
Trifon
_________________
Trifon Trifonov

[red1]

Trifon,
i cant find the above in the code u sent via email. Can u send it again to Rich and/or me?

[red1]

After step 6 above we have to make the process appear in the Menu
a) SystemAdmin>Menu
b) Create new>Action - Process > Window> select your newly created Process.

What this BOL_Generate.java does is:

1) Examine each Order record and look for a common BOL No.
2) Merge the Orders' Lines into a BOL Detail file.
3) Mark each processed Order with a BOL_ID so that it wont be processed again.

So u have to create a new field BOLNO in the Table&Column > C_Order
Also create field C_BOL_ID (number).
Do a GenerateModel 'D' after it.

[riche]

[red1]

I got these too. So ignore them. It may be due to the role setting that culdnt get thru and u have todo it manually as stated above.

Also, after running, check the physical tables from AD and see if the reference and other settings are normal. Use other tables presently in Compiere as reference. It shuld be intuitive.

[riche]

So as I test the BOL window, I have the following comments/questions:
1-When I try to save BOLLine, I get the following error:
ORA-01400: cannot insert NULL into ("COMPIERE"."C_BOL_LINE"."C_BOL_ID"). It's a mandatory field but it doesn't autopopulate. Also, C_BOL_LINE does not autopopulate (shouldn't it so that it can be sorted?). This is solved by lines in the view.xml such as <field name="C_BOL_ID" columnname="C_BOL_ID" isDisplayed="N" isSameLine="Y" />
<field name="C_BOL_Line_ID" columnname="C_BOL_Line_ID" isDisplayed="N" isSameLine="Y" />
which make them present but not seen. So u have to put them manually back into the window of both tabs, and then when its ok, make them not displayed. -red1

2-Instead of just copying (merging) the line items of the Sales Order, the BOL generate should "process" the line items and output into the BOL document (with the following fields): PackCode, quantity, & weight (as well as PackCode Description and Class - see below, but these can autopopulate off of a reference table). I know that you are going to say that this can be accomplished in the printformat/view, but the staff want what is in the BOL window to be what is printed out and be able to edit it. If it is done by a view, then it is not editable easily. If we make these changes the view will be a lot simpler (and will have to be changed obviously). This can be done in the BOL main tab (reversing the PrintView idea back to the window) too. Thus i will infused the BOL Main with the above during processing, in subtotals too, so that the PrintFormat can transfer them directly. -red1
3-We need a new table (PackCodes) that has the following fields: PackCode, Description, Class. Regardless of whether you do above item (2), we will need to reference (either during BOL_Generate or view) this table to get the details of each PackCode. We can do this as a separate independent tab too in the BOL window for quick reference. Otherwise we make it as a lookup that pulls from and put into the BOLLine together. - red1
4-In Bill Of Lading window, BOL tab, the following fields need to be fixed:
a-C_Bpartner_ID- I changed the reference of this field (in table&column) to search so that it would bring up a search window. The change works, but the search window (BP Info) has the "vendors" box checked so it only brings up vendors. How do I have it default so it brings up only customers or all BPs? It seems centrally controlled. I can either turn it off or set to customers for all cases.
b-Location_ID- this needs to populate when BP is chosen (if BOL is created manually) and/or during Generate. Will do that
c-Value- What is this field here for? We already have doc # field/
d-No. Packages- Is this a calculated field? If so can you also add a (calculated) field for total weight? see above, just give the rules we infuse into main tab during processing.
5-In Bill Of Lading window, BOLLine tab:
a-PackCode, Weight - Do these fields auto-populate during BOLGenerate? They should. FYI, Weight should be linetotal weight. Since each package can be a different weight, the total weight field is populated during generate or manually (if BOL is created manually). They do.
b-Name field is required. This should not be the case.. Its auto populated as the BOLNO. Name is a controlled field in Compiere.We can change its label to BOLNO later
6-Does BOLGenerate generate a BOL only if M_Shipper.PRINTBILLOFLADDING (should change field name so that LADDING only has 1 "d") = Y? Please make sure this is true. Will do that
7-Where does the BOLNO in Sales Order come from when it autopopulates (I assume it autopopulates - correct me if I am wrong)? From BOL doc #? If not BOL doc #, then where and how do I change the BOL sequence? If yes, does it reserve the BOL doc # in the case that I manually create a BOL so that it doesn't duplicate the doc #? I tried to test in GardenWorld, by "requested doc type" was required, but there was nothing to select for some strange reason, though this wasn't the case in Hutzler (but I didn't want to test in live DB). I have no rules on BOLNO yet. Its manually entered, not auto. Give two SOs the same BOLNO and they will merge. If this is not the whole rule-set, then give me the rules and i will program them.
Thanks,
Rich

[riche]

[red1]

On the first item it shuld work like u said. Anyway to get a better understanding just do the following (1) and the next (2) later when it worked.

1) Make the IDs appear in your tabs. i.e. C_BOL_ID for Main, and C_BOL_ID and C_BOL_Line_ID in Detail tab.

>U can examine them also in the Sequence tab of AD-Window whether these appear in the left hand side before this (after running the view.xml).
If they do then they are in the system (but not been displayed) and thus wont be considered missing. If they didnt, then the XML2Ad didnt work as shuld in yours.

2) When things work fine, u can check the IsDisplayed box so that they dont appear. If u wish to leave them alone its also ok. I was just following the other compeire display conventions.

>>>
Now about the BOL PackCode subtotals, i think we can try an almost exact fit for the PrintFormat:
A) Make the subtotals append as extra detail lines. The grandtotal as a final line. The description field of the line will carry the words "PACK CODE SUBTOTAL for 'K' OR 'C'" and "GrandTotal" respectively.

B) We use callouts to allow any edit to the detail lines to affect the totalings. No edit shuld be allowed to the totalling lines.

C) Can u confirm that there will be only two pack codes 'K' and 'C'?

[riche]

[red1]

I have read your post on BOL and will try to iterate in pieces until i digest it in its entirety. Meanwhile i m heading North tomorrow for a planned 4 days.

I may online tonite my time to respond further on what i can to the BOL.

[riche]

So you bring up a key point here, which is that BOL No is in fact used for matching order/BOLs together, but it is also used to identify the BOL No (that will be merged). It would work as following:
1-Order is entered, BOL No field is autopopulated.
2-When 2nd order (which should merge BOL), BOL No field is edited with BOL No from other Sales Order that will be matched.

Now if this get's too complicated (using a field in Sales Order for both BOL doc # and matching field), perhaps I can suggest an alternative. Perhaps we just try to accomplish 1 function (in this field(s) - whatever we call it) which would be the matching function. This way we don't have to worry about autogenerating the doc number. I know this is what you were originally thinking. In this case, I suggest we use 2 (new) fields:
1-Order Match - enter the order # for the other sales orders to merge BOLs with OR a unique identifier to look for in other orders
2- a checkbox which called "merge BOL" and then just merge BOLs based on the Order Matching field

Let me know your preference. I will check with Ed on how he would like an order matching field to work.

-Rich

[red1]

[red1]

I have done the PackCode sub-totalling Tab and Process. Next i will do the PrintFormat adjusting, and post the files and steps.

>>>>>>>>> They are now in http://red1.org/barn/HutzlerCompiereUSA/sources/BOLPack.zip .

For the CBOLPackV.sql u can see the fields now in used