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.

ETL – Data Wharehouse

Above is the common elements that make up a Data Warehouse. The data warehouse consists of several different elements, the source can come from legacy systems that are usually an operational system used by the corporation or external data sources, the data staging area where it is processed and moved to the presentation server where the data is organized and stored for future queries and reports. The last step is the end user data access point, currently one of the most popular forms to access data is through a web page.

The first step is extraction and involves obtaining the data from the source systems.

Next it is transforming the data using a series of steps to make the data usable for the data warehouse. The steps consists of cleaning the data to improve data quality and consistency, purging of any unnecessary data that is not required by the data warehouse, combining of similar data from different source systems and creating surrogate keys.

After the data is extracted, transformed and cleansed, it is ready to be loaded and indexed into the warehouse for fast querying.

Other steps include running a series of reports to ensure that the data stays consistent and that the quality stays intact. The data is released to users for generating reports and dashboards and is secured against unauthorized user access.

Main steps in the ETL cycle are:

  • Initiating the Cycle
  • Building of reference data
  • Extraction
  • Validation
  • Transforming
  • Staging (if staging tables are necessary)
  • Audit reports to verify that all business rules are kept
  • Publishing to warehouse tables
  • Archiving
  • Clean up.

Elements Used in a Data Warehouse

Many Business Intelligence solutions are based on the use of a data warehouse. Here is a view of the components of a data warehouse both logically and physically.  Data Warehouse Elements

The data warehouse consists of several different elements:

  • The source can come from legacy systems that are usually an operational system used by the corporation or external data sources,
  • The data staging area is where data is processed (normalized and some history is stored) and moved to the presentation server.
  • The presentation server takes the data, organizes it and stores is for future quires and reports
  • The last step is the end user data access point, currently one of the most popular forms to access data is through a web page and mobile applications.

You can download the visio drawing I created here –>data-warehouse-elements.vsd