SAP-B1 | Pack List for Sales Orders | SQL | Crystal Report

Looking for a better packing list for your shippers to view?

This one will add descriptions and notes from the sales order text lines (unless the word “Freight Charge” is in the text line). Lines will be  added them in the same ‘visual order’ as the shipping order.
It will also add the Shipping Type from OSHP so the you know how to pack the material for certain shipping companies.

SQL Used as a command in Crystal Report:

The SQL and DocKey@ are the tough part of the report. Go ahead and design a report to look the way you want. I’m just helping you join all the information together with a couple inner joins and an union so you don’t have to figure it out on your own. If you would like to add additional data to this SQL. Remember a Union had to have the same amount of columns on both of the Union selected.
SELECT *
FROM
(
SELECT
docnum, linenum, visorder, itemcode, dscription, 0 as ordernum, OpenInvQty, U_SSI_ModPart, cardcode, cardname, docduedate, taxdate, doctime, StDlvTime, docstatus, TrnsCode, trnspname
FROM
rdr1
INNER JOIN ordr ON rdr1.docentry=ordr.docentry
INNER JOIN OSHP ON rdr1.TrnsCode=oshp.TrnspCode
WHERE docstatus != 'C' AND dscription!='Freight Charge'
UNION
SELECT
docnum, aftlinenum, visorder, '' , cast(linetext as nvarchar(500)), rdr10.ordernum, OpenInvQty, U_SSI_ModPart, cardcode, cardname , docduedate, taxdate, doctime, StDlvTime, docstatus, TrnsCode, trnspname
FROM
rdr10
INNER JOIN ordr ON ordr.docentry=rdr10.docentry
INNER JOIN rdr1 ON rdr10.docentry=rdr1.docentry AND rdr10.aftlinenum=rdr1.visorder
INNER JOIN OSHP ON rdr1.TrnsCode=oshp.TrnspCode
WHERE docstatus != 'C' AND dscription!='Freight Charge'
)
PICKLIST
ORDER BY
docnum
, visorder
, ordernum

If you have an issue where the report looks good but when imported into SAP it has the incorrect Sales Order Number. Add a Formula that changes the DocKey@ to increment by the difference.

number is off

{?DocKey@}={Command.docnum}-n

In the below image is to remind you to add a parameter for DocKey@ here in the parameter list. You will need to add one to the report also. Make sure it is a number. It will default to text if you are not careful.CR-Command

 

Once you have your report built, don’t forget to add a DocKey@ to the report also. I change the text of mine to white so it would not be seen.

Show All PO Lines in PO Pack List for SAP Business One Crystal Reports

It is so hard to find useful information for SAP. I hope this helps. I created this document in SAP Business One 9.2 and Crystal Reports 2011.

Database Expert

If you think you can find a good relationship between ORDR, RDR1 and RDR10…good luck.

I needed to create a command to do it for me.

Crystal Reports

Create your command using this SQL join. If there are more fields you would like to use or less, you can modify your command whenever you want.

SELECT *
FROM
(
SELECT
docnum, linenum, visorder, itemcode, dscription, 0 as ordernum, OpenInvQty,
U_SSI_ModPart, cardcode, cardname, docduedate, taxdate, doctime, StDlvTime
FROM
rdr1 INNER JOIN ordr
ON rdr1.docentry=ordr.docentry
UNION
SELECT
docnum, aftlinenum, visorder, '', cast(linetext as nvarchar(500)), rdr10.ordernum,
OpenInvQty, U_SSI_ModPart, cardcode, cardname, docduedate, taxdate, doctime, StDlvTime
FROM
rdr10 INNER JOIN ordr
ON ordr.docentry=rdr10.docentry
INNER JOIN rdr1 ON rdr10.docentry =rdr1.docentry AND rdr10.aftlinenum=rdr1.visorder
)
O ORDER BY docnum, visorder, ordernum

That’s it. Do not use any other Tables (it may crash CR). Go ahead and modify the report so that you do not have to go through all records. I.e. filter for open POs only.

* If you want to preview, make sure to create a record select on the DocNum so you do not load all PO’s.

** Use Parameter field DocKey@ and link it to your DocNum in the formula workshop before importing to SAP B1.