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.

 

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

Develope a Web Based CMS Using PHP

Download this File Here

Abstract
The Content Management System (CMS) is a web based application using a Linux Server,
Apache Web-server, MySQL Database, and PHP Programming Language (LAMP). The
objective of managing users, and information in any given network environment can only be
hindered by the creativity of an information technology professional and not by technology. The
main objective of this thesis is to develop the early development steps of a LAMP software bundleCMS. By creating the
building blocks for developing, and taking into consideration basic methods for creating the core
platform of a CMS for further development. All information gathered, and experience gained will
assist with developing and offering my own personal e-commerce business solutions in the future
and to obtain additional business and practical knowledge in an open source software and ecommerce.
Continue reading

Sources of Data for Business intelligence

CassettesRaw data on its own is not very useful, it is just stacks of symbols, sounds, pictures, numbers or words. After data is collected the ability to connect the data, to give it relational meaning is when data becomes useful information. Information can answer questions of who, what, when and where. Once you see the relation in information you can start to see patterns in how the information changes through visual representations. Knowledge is gained from information when businesses can understand the changing patterns of information to answer questions on how items function in the real world; this is the main goal of business intelligence. Unless business information data compromises privacy or security, all data in every activity that can be observed and recorded will eventual become legally obtained business intelligence. Data acquisition helps in making informed business decisions by transforming information into graphs, charts, simulations or datasets to analyze trends and conclude on what businesses decisions should be made. Wither it is for a government or commercial business the optimal amount of data collection that is wanted is unlimited, because once you now everything about your target goal, you can begin to make the most accurate decisions.
Data Collection in Manufacturing
Managers want feedback from the manufacturing equipment they are in charge of, to monitor how machine processes are, how long the machine has been idle, and how many parts per shift have been produced. Equipment can be attached to machines that can monitor all aspects of physical change from several different points. Analog to digital converters can send the information to a management resource planner or process planner for real time updates on the total progress of a manufacturing plant. Events are monitored in real time for information that can help with corrective actions or adjust accountancy billing based on the amount of parts produced. Thousands of data points can be monitored each second for change on equipment, the several samples of variable changes are “critical to process inputs, even from multiple channels, as fast as possible. But there is more to it than that. Inputs must be processed and correlated so that the feedback loop can initiate changes earlier” (Varhol, 2006, para. 7).
Data entry from security entry ways within factories has also been tied into the billable hours for payroll and taxes, by using the same devices that scan your identification card to gain access to the company to also track when a employee logged in or out of work. So you know when personnel entered the building and when they started working to when they finished working and left the building. Their is several additional data points that that when entered and managed can help better manage personnel, production, billing and collections, sales management, customer care, marketing campaigns, supply chains, accounting, decision supports and any other business decisions. Information and data that is collected in a manufacturing company is obvious and usually serves an internal purpose. More and more companies are gleaning data that can be taken without people even realizing it and used for better advertisement.
Data for Online Marketing
More and more data every year is being amassed from companies over the internet. The use of smart phones has probably doubled or tripled the amount of data available for use in marketing campaigns. Websites, interactive applications, emails, and advertisements can incorporate cookies, pixel tags and web beacons to track individuals browsing behavior to better facilitate the effectiveness for advertising and search engine optimization. Online companies also try to track occupation, language, location, and unique device identifier of device when a product is used so that companies can better understand customer behavior and improve products, services, and advertising. Data gathered from Internet enabled devices also help to control the amount of times you see an Internet ad, to help display ads that are similar to your personal interests and help to monitor the effectiveness of an on-line ad campaign. The most recent data mining craze is storing your personal location using the global positioning system (GPS) and your phone.
Wireless Data Collection
Your location is just as important as all the other data previously discussed. Apple even changed there privacy policies in 2010 so the companies “iPhone, iPad and Mac computers collect location information, but do so anonymously in batches and encrypt it before sending the data over a WiFi connection from the devices to Apple’s servers every 12 hours (Apple, 2010, para. 1). Apple’s admitted that the main reason for wanting to track location of there customers is to provide location based services.
More and more governments are pursuing locational data. Companies like Chevron have been able save millions in freight costs with “more efficient routing that cuts the number of hours and miles the boats travel” (Feldman, 2010, para. 3). Companies can also use location based web services and geospatial information systems (GIS) to decide where the next office or store should be built based on actual driving times and traffic patterns. Other companies are using GPS, RFID, and Wi-Fi technologies to control shipments and other logistics in supply chain management, local government can report problems instantly of time sensitive situations like a down power line, government workers can use smart phones to geo-tag a picture and send it to the main office so the correct department can be notified to fix the problem. The United Parcel Service uses GPS to report were your package is during transit and GPS can be used for just socially updating friends were they are by using applications like Foursquare, a social driven location sharing application.
Conclusion
Data collection is being used more in businesses and governments every year. It has saved money in production and shipping because of the ability to monitor production and shipping in real time. Marketing can be used to target individuals on-line and remember who you are for your visit. We have only scratched the surface on how business intelligence will develop in the future. Even small business can take advantages of some of the technology that was to expensive to consider just a decade ago because of the price drop in devices like smart phones and other technological advancements.

Reference
Apple Tells Congressmen it Batches Encrypts Location Data 411968. July 20, 2010 pNAeWeek, p.NA. Retrieved March 17, 2011, from Computer Database via Gale: http://0-find.galegroup.com.libcat.ferris.edu/gtx/start.do?prodId=CDB&userGroupName=lom_ferrissu
Feldman, J. (Nov 1, 2010). [fusion_builder_container hundred_percent=”yes” overflow=”visible”][fusion_builder_row][fusion_builder_column type=”1_1″ background_position=”left top” background_color=”” border_size=”” border_color=”” border_style=”solid” spacing=”yes” background_image=”” background_repeat=”no-repeat” padding=”” margin_top=”0px” margin_bottom=”0px” class=”” id=”” animation_type=”” animation_speed=”0.3″ animation_direction=”left” hide_on_mobile=”no” center_content=”no” min_height=”none”][Location Data] Here And Now. InformationWeek, 1284. p.53. Retrieved March 17, 2011, from Computer Database via Gale: http://0-find.galegroup.com.libcat.ferris.edu/gtx/start.do?prodId=CDB&userGroupName=lom_ferrissu
Varhol, P. (June 26, 2006). Advanced control designs are drowning in data. Electronic Engineering Times, p.39. Retrieved March 17, 2011, from Computer Database via Gale: http://0-find.galegroup.com.libcat.ferris.edu/gtx/start.do?prodId=CDB&userGroupName=lom_ferrissu

[/fusion_builder_column][/fusion_builder_row][/fusion_builder_container]

Tagged