Order notes & line sequence number

[kbieber]

On multi-line sales orders with line item comments, shipping or deleting lines can cause the line items and their corresponding comments to have different line sequence numbers. I believe this only occurs when order maintenance is performed after the shipment or deletion. The Oeordlin line_seq_no is resequenced, but the oelincmt is not. Does anyone have a way to fix this problem? My thought was to resequence the oelincmt table if the oeordlin table has been resequenced (using flex?), however I'm sure there's a better way.

Thanks.

 

[kbieber]

I thought of one solution - the oeordlin line_no does not change during the life of the order, so I could match the oeordlin line_no to oelincmt line_seq_no and update the oelincmt line_seq_no. However, that will only work the first time, then I'll really be in no man's land. I guess I still need a brilliant solution.

Thanks.

 

[chadt]

ok......I have run into the same situation before.

A company I was doing work for bases their lot numbers off the Order Number and Line No.....and I was too in the same situation if a line item was deleted from the order.

The best solution that I have found is with Flex (i know...I hate flex but it works for this) Just use one of the user defined fields and have OE enter the line number on the order. This works good unless you have lots of line items on the order. (This company would maybe have 10 or 20 at the most.

 

[kbieber]

I'm not sure I understand your solution. The oeordlin has a line number and line sequence number already. The oelincmt has only the line sequence number, which initially corresponds to oeordlin line number and sequence number. Somehow I need to re-sequence the oelincmt entries when the oeordlin is re-sequenced.

Thanks for the help.

 

[MacolaHelp]

While this condition is indeed true about the order line #s & sequence numbers going out of sync when adding or deleting lines from the original order, I so far have not found it to be problem in generic Macola forms or recent ICRs. I experienced the problem using a Macola ICR for quotes as a base & the subreports that pulled in the notes & comments would be linked to the incorrect lines. I eventually rebuilt from a corrected Macola form.

I guess I am unclear as to why you think you should resequence if you are only using generic macola forms & reports??

 

[kbieber]

I have this problem on both ICRs (pick ticket and list by order number) and custom Crystal reports. Apparently there is some logic here that I am missing. The subreport linkage is on the order number and the line sequence number. Once the sequence numbers have changed, I either have the wrong note for the wrong line item or no note at all. And I'm not sure what 'rebuilt from a correct Macola form' means.

 

[MacolaHelp]

The first macola icr's are linked differently than the current ones at the subreport level. The comment subreport now links the oeordlin_sql.line_no to that parameter in the subreport & the data is selected in the subreport based on field oelincmt_sql.line_seq_no. I had started with oeqtefrm.rpt from 11/2001 & the comments & notes subreports were returning the notes & comments to the wrong line items on the order. Tori sent me a new report from 4/2003 & it works correctly. I haven't done anything with the pick ticket or the order edit lists as yet on notes & comments, so I'm not sure if they have been corrected or not. If you have access to support, have Tori find out if they have corrected it or not. I tried to take the corrected subreport for notes & comments into a form I had spent a great deal of time on, but bringing in the new sub didn't work in my case (probably my own lack of expertise on subreports). So I ended up starting over with the 4/7 ICR & rebuilt my form & everything works fine now.


Here is the select statement from the new notes subreport that is working (if that helps):
{OELINCMT_SQL.Ord_No} = {?Pm-OEORDLIN_SQL.Ord_No} and
{OELINCMT_SQL.Cmt_Type} = "I" and
({OELINCMT_SQL.Doc_Type} = "O" OR {OELINCMT_SQL.Doc_Type} = "Q&quot and
{OELINCMT_SQL.Ord_Type} = {?Pm-OEORDLIN_SQL.Ord_Type} and
{OELINCMT_SQL.Line_Seq_No} = {?Pm-OEORDLIN_SQL.Line_Seq_No}

and here is the one in the old ICR:

{OELINCMT_SQL.Ord_No} = {?Pm-OEORDLIN_SQL.Ord_No} and
{OELINCMT_SQL.Cmt_Type} in ["I","Q","O"] and
{OELINCMT_SQL.Doc_Type} in ["O", "Q"] and
{OELINCMT_SQL.Line_Seq_No} = {?Pm-OEORDLIN_SQL.Line_No} and
{OELINCMT_SQL.Ord_Type} = {?Pm-OEORDLIN_SQL.Ord_Type}

 

[kbieber]

There are times when the answer is so obvious....thanks MacolaHelp for helping me see that answer. Your solution worked just fine for my needs.