credit settlement file validation - thdfi.zendesk.com€¦ · web viewselect wordpad. check the...

Credit Settlement File Validation 2018

THE HOME DEPOT

Credit Settlement File Validation

Training Guide

THE HOME DEPOT PROPRIETARY

This document is proprietary and confidential to The Home Depot. This document shall be treated and safeguarded in the same manner in which the recipient treats its own confidential information of like kind or in accordance with the non-disclosure agreement between the parties. Neither this document nor any information contained herein may be distributed, disclosed or disseminated in any way to parties outside The Home Depot without the prior written consent of The Home Depot as provided in the non-disclosure agreement.

2018 The Home Depot

1


Table of ContentsCredit Settlement Data and Sales By Tender Report...............................................................................3

Prepare Sales By Tender Report for Credit Settlement........................................................................3

Prepare Settlement Data.........................................................................................................................9

Request Credit Settlement Zipped Files...............................................................................................9

Extract Credit Settlement Files............................................................................................................9

Prepare Credit Settlement Spreadsheet................................................................................................12

Credit Settlement and SAP Data............................................................................................................23

Extract and Validate SAP Input File....................................................................................................23

Extract and Validate SAP Output File (Part 1)....................................................................................28

Extract and Validate SAP Output File (Part 2)....................................................................................37

2


Credit Settlement Data and Sales by Tender ReportPrepare Sales by Tender Report for Credit Settlement

An email will be sent from the Project Team containing zipped files which contains the Sales by Tender Report.

This file will need to be saved to a drive in order to be unzipped. To do this, click on the file and select “Save As” (Right clicking and selecting “Save As” will work as well).

3


Navigate to the drive where the file will be saved (It can be anywhere).

Click on the “Save” button.

4


Note: Once ‘Save’ button is clicked, the pop up will disappear and the email will be displayed again.

Go to the location where the folder is saved. This is a zipped file which will need to be unzipped.

5


Right click on the zipped file, click on” 7-Zip” and click on “Extract Here” to unzip the files.

6


Click “OK”.

Double click on the folder unzipped folder.

7


Once the files are unzipped, the directory would look like this:

To retrieve the Sales by Tender Report, click on “ISP Reports” folder.

8


The ISP Reports folder contains the Sales by Tender Report. It is called “tender”.

Double click on the “tender” report. Verify the sales date and the store number.

9


Prepare Credit Settlement DataRequesting Credit Settlement zipped filesGo to the Financial Validation Settlement Request url, fill out the test fields, check all the checkboxes, and click “Submit” to send the request. Ig anything goes wrong, email Isaac Suggs.

10


Extracting Credit Settlement FilesAn email with the subject of “Financial Validations – Store # - mmddyy – FV#” is sent from Credit Settlement once the processing is complete. The email contains a file titled “settlement_reports_mmddyy.tar.gz”. This is a zipped folder that contains all the files needed to be sent to SAP and the banks.

Save it to desired drive and right click on the “settlement_reports_mmddyy.tar.gz” file, click on” 7-Zip” and click on “Extract Here” to unzip the files.

11


The results will look like this:

12


The “settlement_reports_mmddyy.tar” file will need to be unzipped as well. Follow the same process as above; right click on the “settlement_reports_mmddyy.tar” file, click on” 7-Zip” and then click on “Extract Here”.

The results will look like this:

13


Files inside the zipped file will look like this.

14


Prepare Credit Settlement SpreadsheetPrepare a clean Credit Settlement template by naming it with the FV #, Store #, Sales Date that is being worked on [Credit Settlement_FV-18.xxx_Strxxxx_Dmmddyy]. Open Credit Settlement Validation spreadsheet created.

Go to Match tab. There is a space for the Sales by Tender Report as shown below.

Go back to the zipped file sent by the project team. Go to ISP Reports folder and open the tender [this is the SalesByTender report] report on notepad.

15


Open the Snipping Tool. Click on New.

16


The system will lighten the report and display a + as the cursor. Left click on the mouse and drag the plus cursor from above the “ptr449a” report name to the right after the page number, then down to just after the PayPal line.

Let go of the left portion of the mouse and the screen will pop up on the screen showing what was copied. The next step is to paste the Sales by Tender report onto the spreadsheet. To do this, click on the two sheets of paper to copy what was snipped.

17


Paste the text you copied from the tender.txt report to the space in the Match tab.

Insert the values from the tender report to the designated fields on the right shown below.

18


Note: Canada Stores will follow these rules:

No CHASE Processor No Home Improver Cards No Discover Cards

19


Go back to ISP Report folder. Open the SalesByTenderByProcessor document on WordPad or Notepad++. (Canada store will not have a SalesByTenderByProcessore report. Omit the steps related to the SalesByTenderByProcessor)

20


Copy ALL text.

21


Go back to the Credit Settlement spreadsheet, SALES_By_Tender_By_Processor tab, and paste it on cell A1.

Compare highlighted values [Up to PayPal line]to the SalesByTender report on Match tab.

22


+

Open the excel file titled acs_cr_trans_mmddyy.

23


Copy ALL data.

Go to the Settlement Data tab on the template and paste the copied text on A2.

24


Data should look like this:

Look at column R and S (everything should say ‘Match’ all the way down).

25


Next, convert the amounts to numeric. Highlight the amounts and click on the yellow warning sign. Do not highlight the header.

26


Next click on “Convert to Number”

The system will convert the amounts to a number. The green tabs on the upper left hand corner of each of the cells will disappear.

27


Go to Match tab. Under Summarized Data look at column D (Sum of Amount), should say Match.

Go to Pivot_Table tab.

28


Click on Data tab on top. Click on Refresh All. This is going to update the Pivot Table on the page.

29


Credit Settlement and SAP DataExtract and Validate SAP Input FileFirst extract the SAP Input File. To do this, right click on the file titled rlse_cr_card_payment_Dyymmdd. Click on “Open with…”

Select WordPad.

30


Check the store number in the file (first column). This should equal the store number you are testing. Also check the date (second column). This should equal to the sales date you’re testing.

Note: If there is a line for a store that is not our test store, reach out to Isaac Suggs. If Isaac confirms those values can be ignored, do not copy them over to the Credit Settlement Validation Template. Delete those extra lines with a different store number and save the file with same name.

Open SAP Input tab on template. Paste copied text to cell A1.

31


Go to ‘Data’ tab on top. Follow steps 1-4 shown below.

32


Click on “Other” and type the pipe (|shift \) in the box next to “Other”. Next click on “Finish”.

Highlight the data and click on “Copy”

33


Paste the data on the “Match” tab highlighting cell below the “Store” (A26) heading and click “Paste”

34


Look at Column E under Summarized Settlement Data. Should say Match.

35


Extract and Validate SAP Output File (Part 1)Log in to SAP by double clicking on SAP icon.

You will land on the below screen. Double click on “ES1”.

Enter your LDAP and password you created for SAP.

36


Click on “I Agree” button.

Land on the SAP Easy Access page.

37


Click on Credit Settlement Validations under Favorites. You will see highlighted TCodes.

38


Click on YAR0002.

Click on checkbox with green checkmark.

Find the POS store number that is being validated and get the EFI number and CoCd (Company Code).

39


Paste the copied text on SAP Output tab under Store Cross Reference. Note that CoCd is different for .COM stores.

Click on the green arrow icon to go back.

40


Click on YAR0003.

Below page will come up.

Note: Should never have to use this.

Click on FB03.

41


Click on Document List icon.

42


Click on the Variant icon.

Select CS variant.

43


Click on the green checkbox.

Check posting date is sales date.

44


Copy all data from columns CoCode – Posting Date.

Go to the SAP Output tab and paste copied text under Credit Settlement Part 1 (Column G1).

45


Highlight the entries in the DocumentNo column and Hold down Ctrl +C to copy the document numbers.

Load the documents from FB03 Display Document into FBL3N – G/L Account Line Items

Click on the Variant Icon

46


Enter *cs* into the Variant textbox. Delete the information in the ‘Created by’ Textbox.

Click the Execute button.

47


The G/L account will now be populated. Update the Company Code if needed.

***Note: If this is a Canadian Store leave the G/L account textbox empty. ***

Change the Key Date to the Sales Date.

Click the Dynamic Selections button

48


Click the multiple selections button to the right of the Document Number

49


50


In the Multiple Selection for Document textbox, click the ‘Delete Entire Selection Line’ Button.

51


With all the documents numbers in your clipboard, Click the ‘Upload from Clipboard’ button.

52


Click on the Copy button

53


Click the execute button

54


The SAP Accounting Documents will be displayed.

55


Filter the list so that the store used for testing is on the list. Click on the ‘Text’ Header. The Column will be highlighted

56


Click the Filter button.

When the Textbox appears, enter the EFI store number. This information can be found in the Store Cross Reference on the SAP Output tab on the Credit Settlement Validation worksheet.

57


Enter the EFI store number as *EFI store number* and then click execute.

58


The filtered list will be displayed.

59


Copy this list and post it in the SAP Output Tab in the Credit Settlement Part 2 column.

Delete the minus signs at the end of the numbers. Copy the entire grid of information and paste it into the SAP Credit Settlement File in the Match Tab.

60


NOTE***The documents for a US store will not include an entry for IP in the Text Column, but Canadian stores will have this entry. This is an example of documents from a Canadian store***

Look at the Summarized Settlement Data Grid in the upper left corner in the Match Tab.

61


If you do not see any ‘No Match’ entries, the Credit Settlement Validation has passed. If you have no matches, you must go into SAP and look for the Accounting documents that had issues.

Extract and Validate SAP Output File (Part 2)Click on WE05.

62


Click on Variant icon.

Type in cs* in variant textbox. Leave rest of the fields blank.

Select CS_US variant (for Canada stores select CS_CA).

63


Created On should be the date that the files were created.

Once SAP team will respond to the email it’s done, check again. Look at the time SAP team sent the email, and look for the time on the list (that’ll ensure that you’re getting the idocs for the right store).

64


65


Double click on each to open. Should look like this:

Click on Control Rec.

66


Copy the IDoc Number (this will be needed soon).

67


Click and open the Data Records folder.

Open the E1BPACGL09 text. Copy the GL_ACCOUNT and ITEM_TEXT data (this will be needed soon).

68


69


Open the E1BPACCR09. Copy the AMT_DOCCUR (this will be needed soon).

Paste ALL the copied data (IDoc Number, GL_ACCOUNT and ITEM_TEXT, AMT_DOCCUR) on Match tab, under SAP Credit Settlement File.

70


To exit click on the orange arrow icon.

Troubleshooting Examples:

Problem: The acs_cr_trans_ddmmyy.xls file is empty or missing data

Solution: Contact Isaac. Most likely there was a problem creating the files.

Problem:

Documents are not automatically sent to SAP. The steps were followed for FB03 – Display Document, but no documents were generated.

Sometimes, documents are not automatically sent to SAP. In this case, an email has to be sent to the SAP team to manually do it. Then the date will be the day it was manually entered). Follow these steps:

1) Send an email to Muthurajan, Sundarabharathi and CC Haase, Andrea, Parks, Keith, Killingsworth, Steven, and Holstege, Corey. Here is an example

71


Sundar will send an email similar to this after the Idoc’s have been created.

72


Process Idoc’s via BD87

Click on BD87 – Status Monitor for ALE Messages

73


Look at the IDocs in the email. Look for the smallest ant the largest numbers. In this example 137970114 is the smallest and 137970019 is the largest.

Enter these two values in the SAP screen

74


Change the Created Date to the date that Sundar created them.

Click Execute

75


The Status Monitor for ALE Messages screen will display

If successful, the number of documents created will equal the same number of documents that showed up in the email from Sundar. Once these IDocs are processed, go to Step FBL3N G/L Account Line Items and follow the steps to complete the validation.

76


77