Trace message on packing list

unknown

I'm developing an override of VMSHPFRM and I need to print the trace id's associated with each line. The only field I see is LN_TRACE_MESSAGE, but it's not showing my trace info. I don't think an extended query will help because I need the data per line, not once for the form.

Can anyone tell me where LNTRACE_MESSAGE derives its data? Or is there a different field I can use to grab trace id's?

unknown

We use an extended query on VMSHPFRM1 and it prints our trace (serial numbers) specific to each line on the packlist. The output format is defined in the function below but ultimately looks like

Here is how we are accomplishing:

----Extended Query on VMSHPFM1------------------------------------------------
SELECT V.TRACE_LIST , V.CO_LINE_SPECS FROM VW_DD_SHIPPER_LINE_TRACE_IDS V WHERE PACKLIST_ID = :CO_PACKLIST_ID AND CO_LINE_NO = :LN_LINE_NO

----View Referenced in Extended Query Above------------------------------------------------
CREATE view [dbo].[VW_DD_SHIPPER_LINE_TRACE_IDS]
as
select sl.packlist_id as PACKLIST_ID,
sl.line_no as PL_LINE_NO,
sl.cust_order_line_no as CO_LINE_NO,
convert(image,convert(varbinary(max),convert(nvarchar(max),dbo.fn_dd_shipper_line_trace_ids(sl.packlist_id,sl.line_no)))) as TRACE_LIST,
clb.bits as CO_LINE_SPECS
from shipper_line sl
left join cust_line_binary clb
on clb.cust_order_id = sl.cust_order_id
and clb.cust_order_line_no = sl.cust_order_line_no
GO
----Function Referenced in View Above------------------------------------------------

CREATE function [dbo].[fn_dd_shipper_line_trace_ids]
(
@p_packlist_id varchar(15),
@p_packlist_line_no smallint
)
returns varchar(max)
as
begin

declare @v_part_id varchar(30),
@v_trace_id varchar(30),
@v_trace_comments varchar(250),
@v_trace_label varchar(30),
@v_trace_qty decimal(14,4),
@v_trace_list varchar(max)

declare c_trace cursor local for
select tt.trace_id, isnull(t.comments,isnull(t.aproperty_1,'')),
sum(tt.qty * -1) as trace_qty
from shipper_line sl,
trace_inv_trans tt,
trace t
where sl.packlist_id = @p_packlist_id
and sl.line_no = @p_packlist_line_no
and tt.transaction_id = sl.transaction_id
and t.part_id = tt.part_id
and t.id = tt.trace_id
group by tt.trace_id, isnull(t.comments,isnull(t.aproperty_1,''))
having sum(tt.qty * -1) <> 0
order by 1

select @v_trace_list = ''

open c_trace

fetch next from c_trace
into @v_trace_id, @v_trace_comments, @v_trace_qty

@fetch_status = 0
begin

if len(@v_trace_list) > 0
select @v_trace_list = @v_trace_list + char(13) + char(10) + @v_trace_id +
case
when len(isnull(@v_trace_comments,'')) > 0 then ' - ' + @v_trace_comments
else ''
end
else
select @v_trace_list =
'Serial Numbers: ' + char(13) + char(10) + @v_trace_id +
case
when len(isnull(@v_trace_comments,'')) > 0 then ' - ' + @v_trace_comments
else ''
end

fetch next from c_trace
into @v_trace_id, @v_trace_comments, @v_trace_qty

end

close c_trace
deallocate c_trace


return (@v_trace_list)

end

GO