Document Control
Changes History
Version CR No. Date Name Change Transport No
0.1 N/A 24.05.2006 Copied from 3.1i specification for Project Sapphire
Updates references to BHI with BRI.
Developer notes added as changes required for Project Sapphire
0.3 N/A 27.07.2006 Update specification according to approved CR21
Authorisations
Functional Specification
Name Role Signed Date
xxxx Functional Consultant
xxxxxxx Business Representative
Technical Specification
Name Role Signed Date
A developer SAP Developer xxxxxxxxx 31.07.2006
Another Developer SAP Developer
This document may contain text that has been struck through (e.g. this has been struck through).
This text no longer applies to the current document version but has been retained for information
purposes only.
Functional Specification
1. Description and business rationale
This functional specification describes the requirements for a download of delivery data to Excel,
which will provide details of what products and quantities and (if required) batches are being
shipped on each delivery.
The following selection criteria should be added to the selection screen (below Delivery Date):
Delivery number from to
Business wants to run this report for single deliveries and for a range.
In addition billing information can also be selected for inclusion on the downloaded report.
The following fields should be added to the report when selecting Billing details only or Batch
and billing details:
Barcode - From material master
Commodity code - from material master
Country of Origin - from material master
Gross Weight - from material master
Net Weight - from material master
Material Freight Group - from material master
The following field should be added to the report when selecting Batch details only or Batch &
Billing details (last column):
Basic Material from material master
As a requirement BRI needs the selection screen to be changed. In the section Document data
the description of the selection option Deliveries from needs to be changed to Delivery date .
1. Net Weight and Gross Weight. Please can this be extended to include a column that multiplies
the quantity by the net weight by line, and another column that multiplies the quantity by the
gross weight by line.
2. The material description on the report should use the following sequence:
a) if a CMIR exists, for the customer, use the CMIR
b) if no CMIR exists, use the sales text (long description) from the material master
c) if no sales text exists, use the 40 character (short description) from the material master
The download will be run manually by the business users both in country users and centre
users will have access to run the download.
This report should be added to the customer services reporting tree.
A user viewing the list can see whether a delivery has already been downloaded. The user can
also see who has downloaded the delivery with the person who carried out the most recent
download first being shown first.
2. Referencing procedures
None
3. Dependencies
None.
4. Report Program Requirements
4.1 Processing Flow
Selection Criteria
The following selection criteria will be required, all of which should have a search help available.
All selection criteria are optional unless otherwise specified and should be validated. If a to
value is not entered then from value will be the sole selection criteria.
Standard multiple selection should be available.
Ship to party (mandatory)
Shipping point (mandatory)
Delivery date
Delivery number
Other selection criteria are
Sort order (PGI date or order number)
Include batch and or billing details radio button
The ship to customer must exist in KNA1.
The shipping point, if entered, must be defined in table TVST.
The requested delivery date from, if entered, must be a valid date.
The requested delivery date to, if entered, must be a valid date.
The requested delivery date to, if entered, must be greater than the requested delivery date
from.
Only deliveries which have been post goods issued must be displayed on the report. The report
should either be ordered on Post Goods Issue date (most recent first) or by order number in
ascending order).
A screen print of the standard SAP delivery list report selection criteria is included for reference.
The new program selection screen should be similar to this.
If the entered selection criteria result in no deliveries being selected then a pop-up should be
displayed containing message number VR420. A screen print from the standard SAP Backorder
report is included for reference.
The selection criteria will be displayed/printed in the header of the report.
The report detail will contain the following information
Output Data
Required information in the download is:
Partner item code (CMIR)
Boots Item Code
Product description (CMIR description, if not available description on delivery)
Quantity
Order number
Expected delivery date
Item line
Delivery Number
Batch number (if required)
Batch expiry date (if required)
Batch Date of Manufacture (if required)
Customer Purchase Order No (if required and applicable)
Sold to Customer (if required)
Billing No (if required)
Billing Date (if required)
Invoice Price for a single. (if required)
Invoice Line Value (if required)
Currency (if required)
Bar Code Number
Net Weight
Gross Weight
Material Freight Group
Commodity Code
Country of Origin
Total Gross Weight
Total Net Weight
Basic Material
Any specific formatting for partner requirements will be done manually by the BRI country teams.
Key Figure
Description
Formula/Calculation
Partner item code (CMIR) Normally this would be retrieved from LIPS- KDMAT however this is
not filled in for stock transport orders so we will need to go to the master data table for customer
material information records (knmt) and look up the customers material (kdmat) with the the
sold-to customer LIKP-KUNAG for Sales orders and the sold-to customer held against the
receiving plant on the purchase order on the plant/branches table T001W for a STO .
Product description (can be CMIR description or material master description) First program will
search for the CMIR text, If it is available then It will be retrieved to print in the down loaded
report. If CMIR text is not available then Sales text is retrieved. If both the above texts are not
available then Material Description from MAKT table is retrieved and down loaded into report.
The sold-to customer LIKP-KUNAG for Sales orders and the sold-to customer held against the
receiving plant on the purchase order on the plant/branches table T001W for a STO.
Quantity LIPS- LFIMG
Order number LIPS- VGBEL
Expected delivery date LIKP- LFDAT
Item line LIPS- POSNR
Delivery Number LIPS- VBELN
Boots Item Code LIPS- MATNR
Batch number LIPS-CHARG
Batch Expiry Date MCH1-VFDAT
Batch Manufacture Date Retrieve Maximum Storage Period MARC-MAXLZ and Unit of time for
max. storage period MARC-LZIEH.
If LZIEH is not in DAYS then convert MAXLZ to DAYS. If time unit is not in days, weeks, months
or years then enter Shelf Life Error.
If MAXLZ is 0, manufacture date is n/a.
else calculate manufacture date as MCH1-VFDAT MARC-MAXLZ.
If date is after the picking date then enter shelf life error else use the calculated date.
Customer Purchase Order No VBAK-BSTNK
Sold to Customer VBRK-KUNAG
Billing No VBRK-VBELN
Billing Date VBRK-FKDAT
Invoice Price for a single. Read table A005 to get the KNUMH field using the material, customer,
sales org, distribution channel, pricing date and condition type = ZGSA .
Read table KONP-KBETR using A005-KNUMH.
Invoice Line Value Invoice Price for a single * LIPS- LFIMG
Currency KONP-KONWA
<<< v1.2 End of Insert >>>
Bar Code Number This Number is retrieved from Material Master (MARA-EAN11) on the basis of
LIPS-MATNR.
Net Weight This Value is retrieved from Material Master MARA-NTGEW) on the basis of LIPS-
MATNR.
Gross Weight This Value is retrieved from Material Master(MARA-BRGEW) on the basis of LIPS-
MATNR.
Material Freight Group This Value (MFRGR) retrieved from MARC table on the basis of the LIPS-
MATNR & LIPS-WERKS.
Commodity Code This Value (STAWN) retrieved from MARC table on the basis of the LIPS-
MATNR & LIPS-WERKS.
Country of Origin This Value (HERKL) retrieved from MARC table on the basis of the LIPS-
MATNR & LIPS-WERKS.
Total Gross Weight This value is determined by multiplying the Gross Weight with Quantity for
each line.
Total Net Weight This Value is determined by multiplying the Net Weight with the Quantity for
each line.
Basic Material MARA-WRKST
All deliveries for one ship to customer will be displayed on a delivery list organised (default) by
post goods issue date with most recent first. (as Vl05 deliveries list).
A delivery can be selected from this list for download to Excel.
A user viewing the list can see whether a delivery has already been downloaded
The user can also see who has downloaded the delivery with the most recent user first.
The report output should display the following
Order number Delivery number Actual goods issue date Downloaded by Date downloaded UK
download time
When a user double clicks on a line this should take the user into transaction VL03 (display
delivery).
A button should be added at the top called download to Excel. If the user selects this the delivery
is downloaded to Excel.
Downloaded by -The name of the user should be displayed, however if this is blank then the user
id should be displayed.
Date downloaded - if a delivery has been downloaded more than once- the most recent
download should be displayed first i.e. the sort sequence within a delivery number should be on
the most recently downloaded date to the oldest downloaded date.
The whole report should be sorted on the actual goods issue date or the order number
depending on the selection made on the selection screen.
The Excel output should be as follows
There are four possible download versions.
Version 1: Download Report with no batch or billing details
Order Number Delivery Number Delivery item number Boots material code Customer material
code Description Quantity Expected delivery date
4500000721 80000587 10 10-10-110 3089479 Skin Kindly Gentle
Refreshing Toner 1171 01.09.2005
Version 2: Download Report with batch details
Order Number Delivery Number Delivery item number Boots material code Customer material
code Description Quantity Expected delivery date Batch Expiry Date Manufacture Date
4500000721 80000587 10 10-10-110 3089479 Skin Kindly Gentle
Refreshing Toner 1171 01.09.2005 CD1 31.05.2010 01.08.2005
Version 3: Download Report with billing details
Purch Order No Customer Order Number Delivery Number Invoice No Invoice Date Delivery Item
Number Boots Material Code Customer Material Code Description Quantity Invoice Price Each
Invoice Line Value Invoice Currency Expected Delivery Date
Ref 5 4071 1559 80001935 90001335 01.11.2005 10 10-11-401 Sparkle Eye Shadow 102 10
1020 THB 01.01.2006
Ref 5 4071 1559 80001935 90001335 01.11.2005 20 10-11-402 Sparkle Eye Liner 108 0 0
01.01.2006
Version 4: Download Report with Billing Details only.
Material Details in the Down Loaded Excel report.
Barcode Number Net Weight Gross Weight Material Freight Group Commodity Code Country of
Origin Total Net Weight Total Gross Weight
0.25 1.5 UN1266I GB 2.5 15
0.005 0.005 00 GB 0.05 0.05
5045094732470 0.04 0.04 00 0.4 0.4
Version 5: Download Report with Batch & Billing details
Purch Order No Customer Order Number Delivery Number Invoice No Invoice Date Delivery Item
Number Boots Material Code Customer Material Code Description Quantity Invoice Price Each
Invoice Line Value Invoice Currency Expected Delivery Date Batch Batch Expiry Date
Manufacture Date
Ref 5 4071 1559 80001935 90001335 01.11.2005 10 10-11-401 Sparkle Eye Shadow 102 10
1020 THB 01.01.2006 SGTEST 01.01.2010 Not Available
Ref 5 4071 1559 80001935 90001335 01.11.2005 20 10-11-402 Sparkle Eye Liner 108 0 0
01.01.2006 SGTEST 01.01.2010 Shelf Life Error
Version 6: Download Report with Batch & Billing Details.
Material Details in the Down Loaded Excel report.
Barcode Number Net Weight Gross Weight Material Freight Group Commodity Code Country of
Origin Total Net Weight Total Gross Weight Batch material
0.25 1.5 UN1266I GB 2.5 15
0.005 0.005 00 GB 0.05 0.05
5045094732470 0.04 0.04 00 0.4 0.4
4.2 Request Mechanism
A user will manually run the report using transaction ZO99.
The report will also be added to the report tree under Customer Services -> All SPC reports.
4.3 Frequency and Timing
This report will run on an ad-hoc basis.
5. Data Volumes
6. Any Unresolved Issues
None.
7. Technical details:
The following changes are added in the Report:
Batch material is added at the end of report (in Excel) when selecting Batch details only or
Batch & Billing details.
Added new selection field in the Selection screen - Delivery Number as selection option
Changed in the section Document data the description of the selection option Deliveries from
to Delivery date.
Technical Specification
1. Processing Flow
Select Deliveries
Upon execution the program sets PF-STATUS DLVLIST. It then will select all deliveries from the
delivery header table LIKP into internal table T_LIKP according to the selection criteria for
customer number (P_KUNNR), shipping point (P_VSTEL), delivery date (S_LFDAT) and which
have an actual post goods issue date (WADAT_IST is not equal to 0).
If no deliveries are found an error message is displayed and processing stops.
If deliveries have been found, the order number is retrieved by selecting VGBEL from LIPS for
each delivery in the table. This assumes that there are only ever items from one order on a
delivery.
Display the list of deliveries
The program then displays the list of deliveries. First internal table T_LIKP is sorted in the order
selected on the selection screen (P_GIDAT ).
The customer name NAME1 is read from customer table KNA1.
The shipping point text VTEXT is read from the shipping point text table TVSTT.
The program then writes the customer, shipping point and date details.
The program then loops at table T_LIKP and writes out the delivery header details in the required
format. For each delivery download table Z194A is read to retrieve the download history. The
history is ordered by date and time in descending order and the user name retrieved from table
USR03. The download history is written to the report.
Fields VBELN, LFDAT and VKORG from internal table T_LIKP are hidden behind each line
written out for the current delivery.
Along with above fields Customer Number (T_LIKP-KUNNR) is also hidden each line written out
for the current delivery.
At line selection
When the user double clicks on a line transaction VL03 is executed skipping the first screen
passing in T_LIKP-VBELN in parameter id VL .
Selecting the Download to Excel Button
Refresh and clear T_LIPS, T_LIPS_NO_BATCH_NO_BILL, T_LIPS_WITH_BATCH,
T_LIPS_WITH_BILL.
When the user selects the Download to Excel button (user command DLEX) a pop up message
is displayed prompting the user to confirm that the selected delivery should be downloaded to
Excel.
If the user has selected an option to include billing information a check is performed to see if the
delivery is waiting to be billed using table VKDFS. If the delivery has not been billed a pop up is
displayed to ask the user if the selected delivery should be downloaded to Excel.
If the user selects yes the program continues.
If the user confirms the download the program reads delivery item table LIPS into internal table
T_LIPS for the selected delivery T_LIKP-VBELN.
The program then loops at internal table T_LIPS to process each line. First it checks for batch
splits. If batch details are not required
P_BATCH not selected and P_BOTH not selected
) and there is a reference number to a header item in T_LIPS-UECHA the line is deleted from
T_LIPS as it is a batch split line.
If batch details are required (P_BATCH selected
or P_BOTH selected
) and the quantity in T_LIPS-KCMENG is not zero the line is deleted from T_LIPS as it is a
header line.
If the line is retained, the program selects the customer material number KDMAT from customer
material info table KNMT.
The Sold-to customer LIKP-KUNAG retrieved for Sales orders and the Sold-to customer is
retrieved held against the receiving plant on the purchase order on the plant/branches table
T001W for a STO to determine the CMIR Text for the Material using the Function Module
READ_TEXT.
The program then checks whether there is a customer material text using function module
READ_TEXT. If text exists, the lines are concatenated and written to T_LIPS-ARKTX. If there is
no text, the description on the delivery is retained. Only the first 5 lines of text can be
downloaded to Excel.
In case of there is no Customer Material Text (CMIR) found by Using the FM READ_TEXT , then
again same function module used to retrieve the Sales Text. Here to determine the Sales Text
Material Number (T_LIPS-MATNR), Sales Organization (T_LIKP-VKORG) and Distribution
Channel (T_LIPS-VTWEG) to the function module. If Sales text is found then this sales text is
moved to T_LIPS-ARKTX.
If there is no Sales Text read from the above function module then Material Description (MAKTX)
is retrieved from the MAKT table later, which is passed to the T_LIPS-ARKTX.
If batch details are required the program selects the batch expiry date VFDAT from Batch table
MCH1 for the batch shown against the delivery line.
Select the maximum storage period MAXLZ and the unit of measurement LZEIH from MARC for
the MATNR and delivering plant.
Check to see if LZEIH is in days. If not convert to days as follows:
If in weeks MAXLZ * 7.
If in months MAXLZ * 30.
If in years MAXLZ * 365.
If any other unit of measurement is read then set the Manufacture date to Shelf Life Error .
If MAXLZ is 0 then set the Manufacture date to Not Available else calculate the Manufacture
Date by subtracting the MAXLZ from the Date of Expiry VFDAT.
Check to see if the Manufacture date is prior to the Picking Date KODAT for the delivery. If yes,
output to the calculated date to Manufacture Date else set the Manufacture Date to Shelf Life
Error.
If billing details are required (P_BILL or P_BOTH = X)
Select the customer purchase order number from VBAK using the order number T_LIPS-VGBEL.
If a record is present assign BSTNK to T_LIPS-BSTNK.
If the delivery has been billed extract all entries in VBRP to T_VBRP for the delivery item. Loop
through each of these entries and extract the SD document category VBRK-VBTYP. If the SD
document category is not a 5 or an M then delete the row from T_VBRP.
Sort the entries by the billing number in descending order. Read the first row in T_VBRP.
Select the billing header row from VBRK using the billing number in T_VBRP.
Move the sold-to Customer Number KUNAG, the billing date FKDAT and the billing number
VBELN from the header record to the corresponding fields in T_LIPS.
Get the Material Details.
Select the Barcode Number (EAN11), Net Weight (NTGEW), and Gross Weight (BRGEW) into
working variables W_EAN11, W_NTGEW, W_BRGEW respectively from Material Master Table
MARA on the basis of the T_LIPS-MATNR.
From the above selected data Barcode Number is concatenated with Equal to (=) sign and with
Double Quotes sign ( ) to T_LIPS-EAN11o report along with leading zeroes. Remaining
Values would be moved internal table T_LIPS.
The additional columns for Total Gross Weight and Total Net Weight are determined by
multiplying the above retrieved Gross Weight and Net Weight by Quantity for each line.
Then these values are displayed in the down loaded excel report.
Select the Material Freight Group (MFRGR),Commodity Code (STAWN), Country of
Origin(HERKL) into working variables W_MFRGR,W_STAWN,W_HERKL respectively from
MARC table by comparing the T_LIPS-MATNR and T_LIPS-WERKS.
From the above selected data Material freight Group and Commodity Code are concatenated as
mentioned above and moved to the internal table T_LIPS.
When Billing details choosed in the selection then corresponding Document Number & Item
Number details are selected from VBRP table. Then Price will be determined by Net Price
(NETWR) by Quantity (FKIMG). If Quantity is equal to Zero then Net price (NETWR) will be
moved to the Price.
After selecting the all above mentioned data Batch Number (T_LIPS-CHARG) is concatenated
with a Equal to (=) and Quotes ( ) to down load into the report along with leading Zeroes.
The program then sets the column names for the columns to be downloaded in internal table
T_FIELDNAMES.
Column Headers for Material Details.
When user selects both (Billing & Batch Details) or Billing Details then relevant column headers
appended to the internal table T_FIELDNAMES.
The file name is set to directory c:\temp and the delivery number T_LIKP-VBELN.
The delivery download table Z194A is updated with the details of the current download.
The internal tables
T_LIPS
T_LIPS_NO_BATCH_NO_BILL
T_LIPS_WITH_BILL
T_LIPS_WITH_BATCH
and the column table T_FIELDNAMES are then used to call the function
MS_EXCEL_OLE_STANDARD_DAT to export the data to Excel.
If billing data is being downloaded a form is called at the end to confirm the delivery has
completed.
2. Object Names
Name
Type
Read / Write / Update
Description
ZOR43701
ABAP Program
Delivery Download Program
LIKP
SAP Standard Table
Read
SD document: Delivery Header Data
LIPS
SAP Standard Table
Read
SD document: Delivery Item Data
KNMT
SAP Standard Table
Read
Customer Material Info Record Data Table
MCH1
SAP Standard Table
Read
Batches
MARC
SAP Standard Table
Read
Material Master: C Segment
USR03
SAP Standard Table
Read
User address data
KNA1
SAP Standard Table
Read
General Data in customer master
TVSTT
SAP Standard Table
Read
Shipping point text table
VBRK
SAP Standard Table
Read
Billing: Header Data
VBRP
SAP Standard Table
Read
Billing: Item Data
A005
SAP Standard Table
Read
Customer/Material
VKDFS
SAP Standard Table
Read
SD Index : Billing Initiator
VBAK
SAP Standard Table
Read
Sales Document: Header Data
Z194A
Bespoke Table
Read/Insert
Delivery Download History Table
ZO99
Transaction
Delivery Download to Excel
3. Report Program Requirements
3.1 Interface Parameters and Select-options
Old Parameter screen
New Parameter screen:
3.2 Special Processing of Parameters
None.
4. Report output (see appendix)
4.1 Output data source
Output data
Source
Line value
T_LIPS-NETWR
Unit price * qty
T_LIPS-KBETR * T_LIPS-LFIMG
5. Field Groups
None.
5.1 Header:
Field Name
Description
5.2 Item:
Field Name
Description
6. Additional Features
The users can drill down to display the selected delivery by double clicking on the line. This will
execute transaction VL03 with the selected delivery number.
The users can select to download a delivery to Excel by selecting the Download to Excel button.
7. GUI - User Interface
7.1 Status DLVLST
BACK, EXIT, PRINT etc. as for a standard SAP screen additional functions as below.
7.1.1 Menu Options
Func
Name
DLEX
Download to Excel
7.1.2 Pushbuttons
F-key
Func
Name
F5
DLEX
Download to Excel
7.1.3 Functions Keys
F-key
Func
Name
F5
DLEX
Download to Excel
8. Appendices
8.1 Appendix A - Report Layout
