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.
docnum, linenum, visorder, itemcode, dscription, 0 as ordernum, OpenInvQty, U_SSI_ModPart, cardcode, cardname, docduedate, taxdate, doctime, StDlvTime, docstatus, TrnsCode, trnspname
INNER JOIN ordr ON rdr1.docentry=ordr.docentry
INNER JOIN OSHP ON rdr1.TrnsCode=oshp.TrnspCode
WHERE docstatus != 'C' AND dscription!='Freight Charge'
docnum, aftlinenum, visorder, '' , cast(linetext as nvarchar(500)), rdr10.ordernum, OpenInvQty, U_SSI_ModPart, cardcode, cardname , docduedate, taxdate, doctime, StDlvTime, docstatus, TrnsCode, trnspname
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'
, 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


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.

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

Cyber Crime: Risk Assessment

Cyber Crime: A Clear and Present Danger

This is the excel file used for this post –>   Risk-Registry.xlsx

Information systems give great ways to communicate and learn, but also allow nefarious others access to exploit the power of the Internet for terrorist and/or criminal purposes. Criminal warfare has moved to the virtual world were more damage can be done in less time with a better potential of getting away with it. The term used for criminals that use the Internet as there method for committing crimes is know as cyber criminals. Moor’s Law describes how the number of transistors that can be placed on inexpensive integrated circuitry double every two years. Since a new generation of faster computers can be processed every two years, it also allows for criminals to afford faster, more complex computers to leverage against your organization

Analyzing and avoiding risk should be a part of any organization who, especially if transactions are made on the Net. Include\ing: email, web browsing, online stores, etc.

The rest of this article is an example of what may be used to start your own risk register.

Qualitative Risk Assessment

Threats that have been identified through the risk it holds to the organizations have been qualified in the attached risk register spreadsheet. Download the actual Excel File –>   Risk-Registry.xlsx

Risk RegisterWhile all risk poses a level of threat there are certain ones that can be identified as low medium or high risk based on the severity of risk against cost and time loss to our origination. Damage to reputation from faulty security is actually a side effect of not being able to protect our customer’s assets and personal information. Side by side comparison of the risk register with the risk matrix will give a better forecast of risk as it impacts our organization.

Quantitative Risk Matrix


On average credit scam counts for $260 dollars per customer per year and we can mitigate that price through the use of heuristic programs that can detect unusual purchases as they happen instead of reading reports after the fact.

While the risk of finances from cyber attacks can be measured after the fact the most damaging effects of cyber attacks is the lack of trust from our customers. Irreversible damage could be imposed upon are organization as customer retreat to companies that they believe can manage their finances better. The contingency budget set forth below would save us from going completely under in the future. The quantity of the budget needed is minimally estimated to be around 1% of what we risk loosing. We must be ready to budget at least 30% of our organizations earned income after taxes and deductions.

Contingency Budget

Contingency BudgetColors reflect the same qualitative selection as the risk Matrix and should be considered top priority when considering budgeting amounts. The contingency cost has been lowered based on the probability of the risks occurrence and is 65% of the total budget needed to address all risks.



When money is used it is gone, data is more valuable than money. Our data and systems are worth more than can be quantified in any report. Data can be reused over and over again. In order to protect our data we need to spend our budget targeting mitigation of the highest ranked risks. Targeting unknown risks through monitoring assets as they are accessed and having a fast corrective action time to save the organization from unknown intrusions. All internal and external metrical information that can help target cyber activities should be used to the up-most efficiently to be as effective as possible. Continually vigilance in our security reaction time, as we move towards future technologies to communicate and process information on the Internet, is imperative to our survival.

Additional Resources

Martin H. Bosworth. (2008). Losses From Cybercrime Nearly $240 Million in 2007. Consumer Affairs. Retrieved from:

Tom Mochal. (2006, May). Creating a risk contingency budget using expected monetary value (EMV). TechRebulic. Retrieved July 24, 2011, from

United States Department of Justice. Computer Crime & Intellectual Property Section. Cyberethics. Retrieved from:

Risk Assessment of Cloud Computing

Below, I have  Performed a short qualitative risk assessment of cloud computing that may be used as guidance for any company thinking of moving to the cloud.  Showing some of the strengths, weaknesses and benefits?


Risk Description Probability Impact Risk Affects
Lock-in Difficult to migrate from one service provide to the next. High Medium Company Rep., Data, Service
Loss of Governance Loss of some control  to CP and unknown roles Very High Very High Company rep, data, customers trust, service
Compliance Challenged Compliance with regulations and certifications Very High High Certifications, fines
Business Rep Loss Poor Service harms business during transition. Low High Company rep, service,  data
Cloud service Terminated Poor provider, lack of understandable terms N/A Very High Rep, trust, emp loyaty, service
Provider Acquisition Mergers and buy-outs of CP N/A Medium Rep, customer trust, emp exp, intellectual property, data, service
Supply chain Failure Lack of supplier redundancy Low Medium Company rep, customer trust, data, services
Technical Risk Over/under provisioning Medium Medium Access control, company rep
Malicious Insider Abuse of high privileges Medium Very High Company rep. data, employee and customer trust
Intercept Data in transit Weak encryption, vulnerabilities in cloud Medium High Company rep. data, intellectual property
Insecure/Ineffective deletion of data Proper sanitization or data Medium Very High Sensitive data, personal data
DDoS Distributed Denial of Service Attack Medium High Cloud Interface, Network, Customers, Company rep, service
Data Protection Staorage in multiple locations High High Company rep, data, service
Not part of CP Network Breaks Medium Medium Service
Social Engineering Lack of security & awareness Medium High Intellectual property, data, emp & customer trust, reputation.
Natural Disasters Lack or recovery plan Very Low High Back-ups, all of the above


Strengths & Benefits:

  • Security measures are cheaper when implemented on a large scale.
  • Data is replicated in multiple areas – increasing redundancy and independence from failure.
  • Local network problems are less likely to have global side effects.
  • Larger scale systems can develop more effective incident response capabilities.
  • Threat management is increased since the larger corporations that own the cloud can afford the generalists to deal with specific security threats that smaller companies cannot.
  • Reduces cost of running personal servers
  • Access to better technology

Weakness and Costs:

  • External CP will depend on network bandwidth
  • Integration of variety of software, integration can be very costly
  • Different configuration panel controls, learning curve for IT department
  • Configuring mixed modes between physical, virtual & cloud
  • Reports on performance could be hidden
  • May not integrate with current management controls


Alex Gutman and Martin Perlin. (February 2011) 8 Cloud Building Conditions You Need for Taking your Data Center to the Next Level. Retrieved from:

Daniele Catteddu, Giles Hogben. (n.d. Cloud Computing Risk Assessment — ENISA. Retrieved from…/cloud-computing-risk-assessment

Naushad K. Cherrayil. (October 7, 2011). Cloud computing is the future of networking retrieved from