List All Sales Order Lines on the Pack List whether shipped or not

Epicor ERP 10
,
1

Is there a way to list all of the sales order lines on a packing list whether the items have shipped or not.

2

Hi Tim

I would think a simple BAQ would get you your answers. Are you needing something more complex than that?

3

I want to pull in the ordered lines form the OrderDtl table to the packing list and show qty ordered, qty shipped and backorder qty. We created a modified SSRS report from the standard out of the box Epicor report but it is only showing the items shipped from the shipdtl table. So I need to know how I can list the ordered lines on the packing list SSRS report.

4

We added the orderrel table to packlsip rdd and created a subreport listing the remaining balance for each release.

I dont have a screenshot with me, stuck in airport trying to leave insights.

5

Thanks for the reply. A screen shot would be good. The problem I am having is getting the parts to show that don’t ship. So if line 2 and 3 ship and line 1 doesn’t only line 2 and 3 show on the packing list. Line one does not show.

6

Hello Aaron,

I created the BAQ but how do I bring it into the SSRS report.

7

What I would like to do, if possible, is to utilize the existing report.

8

If it’s an stock SSRS report that you need to modify, you’ll need to modify the Report data Definition. If it’s a custom BAQ report (also an SSRS report), then you’d modify the BAQ behind the BAQ report.

But i think this is a stock SSRS report?

9

It is a modified stock SSRS, updated to display as we prefer. Now we would like for all ordered lines to show. I have updated the RDD. I have the OrderDtl table included in the RDD. However, when I try to reference what is in the OrderDtl table it will only match what has been shipped. I can’t get the non shipped line items to print. Only the shipped line items are recognized.

Below is the join code where I am joining the OrderDtl with ShipDtl. I have tried to join on other tables but I have not had any success in doing that.

LEFT OUTER JOIN ShipDtl_" + Parameters!TableGuid.Value + " T2
ON T1.Company = T2.Company AND T1.PackNum = T2.PackNum
LEFT OUTER JOIN RptLabels_" + Parameters!TableGuid.Value + " T3
ON T1.RptLanguageID = T3.RptLanguageID LEFT OUTER JOIN OrderHed_" + Parameters!TableGuid.Value + " T4
ON T2.OrderNum = T4.OrderNum LEFT OUTER JOIN OrderMsc_" + Parameters!TableGuid.Value + " T5
ON T2.OrderNum = T5.OrderNum LEFT OUTER JOIN Customer_" + Parameters!TableGuid.Value + " T6
ON T1.Company = T6.Company AND T1.CustNum = T6.CustNum LEFT OUTER JOIN Part_" + Parameters!TableGuid.Value + " T7
ON T2.Company = T7.Company AND T2.PartNum = T7.PartNum LEFT OUTER JOIN OrderDtl_" + Parameters!TableGuid.Value + " T8
ON T2.OrderNum = T8.OrderNum AND T2.PartNum = T8.PartNum"

10

Can you verify that your OrderDtl_[guid] table is being filled out in the report database when you generate a report data?

11

I ran the report and pulled the PartNum from the OrderDtl table. It populates the same part number in the order detail field on the SSRS report that the ship detail field populates,

I am calling this field ODPartNum. Below is the reference in the Dataset Properties Query.
T8.PartNum as ODPartNum

I think the problem is that I have it joined to the ShipDtl table, but I have not gotten any other table to work. I must tie OrderDtl to a table that includes a part number. I believe that is why I can’t get the other ordered parts to show.

12

So from that, do we conclude that OrderDtl is in fact joined properly to ShipDtl?

ShipDtl definitely includes a part number, so I’m not sure why that join wouldn’t work. Maybe include a Company to Company link as well

13

I thought I could join a header table on order number with OrderDtl Table and list all the parts on that order but I can’t get that join to work. I assume that is why it is only getting the Order part numbers for items shipped is because it is joined by ordernum and partnum. Do you agree?

14

Well from the code, you’re joining OrderDtl to ShipDtl on OrderDtl.PartNum = ShipDtl.PartNum and OrderDtl.OrderNum = ShipDtl.OrderNum.

From that, you should be getting results, assuming the data behind it is valid.

I’ll be honest, I’m not the most strong with RDD/SSRS questions. but I think you’re on the right track

15

So, I think the problem is that you’re joining OrderRel to ShipDtl, so because there is no ShipDtl record in the report for any lines that have not been shipped, the extra OrderRel records will not be included. The best way to resolve this in my opinion would be to remove the “AND T2.PartNum = T8.PartNum” from the join criteria in your SSRS query. This way, you’re always including all OrderRel records.

Note that you’ll need to adjust the tablix groupings in the report so that they group by OrderRel.OrderNum, OrderLine, and OrderRelNum. You’ll also need to double check to make sure that the changes to the SSRS query don’t duplicate lines. This way, the tablix will iterate through each ordered line and your logic will either show 0 pieces shipped, or if that order release is included on this pack, it will include the shipment information.

16

Tyler,

Tyler, thanks for you response. I did change the joins and the report runs and populates. I have added the OrderRel table to the RDD and change the groupings on the SSRS, I have included the OrderRel.OrderLine on the report but it is not populating. It only shows the field name as the field value. Not sure what I am missing here.

17

Sorry, I don’t have specific answers… just a couple questions
In SSRS, if you right click on the dataset, select properties, Query, “Expression” do you see the table, joins and fields?
Also in dataset properties - Fields - your field(s) are listed?

And that when I tried this (in V8) I think I started out with a simple BAQ to verify the joins - starting with ShipDtl as my “base” table. (should be the same for E10).
As I remember, it was not as simple as modifying the existing RDD/Report style - due to the way the ShipDtl table/fields were used in the existing report. I believe I ended using a subreport(s).
And timing was another issue - once “Shipped” is checked on the Pack, OrderDtl and OrderRel might change.

18

Bruce, Yes, the fields are joined and they are listed. I did work on a sub report as Bryan’s suggestion. I created a simple query that is pulling all orders in which is only 4 or 5 at this point. Tried to populate an SSRS from the BAQ Report Designer but it only pulls the first record in. I don’t understand why only one line record is listed on the SSRS.