DINARDAP - Credit Sales Report for Equator Government

[former_member187538]

Hello, All!

I'd like to share this Business requirement I needed to develop for a client, that has one of its Company Codes in Equator.

It's very demanding due to the short deadlines and consequences of not delivering such report. From the Business lack of knowledge, once it's a new obligation for them, to the absence of support from the local authorities, makes this business case very special.

You will probably need some adjustments to objects and codes, depending on the business segment of your client but, 70% of the scenario is covered with all it's technical aspects and added values.

Below, an extract of the document which, can be accessed through the link provided. I really hope you take some benefit form it!!

I could not manage to attach the PDF file here, so I had to paste it. Some of the layout has messed up so, if you want the original file, please provide a channel so that I can send it to you.

Please, you all feel free to contact me for any further info you may need! :smile:

1 Business requirement

This business case describes how to extract de Credit Sales data to be delivered to the Ecuadorian Government. This business case will cover the process from both the sales and accounting perspective to provide a complete overview of the extraction and maintenance of these records.

Credit Sales Data will be extracted using different methodologies:

• Past three years. For the years of 2010, 2011 and 2012, the extraction will consider the whole YEAR period, as demanded by the Government. The extraction will produce a file to be delivered through an Internet portal for validation and storage.
• From December 2013 on. As of this period, the extraction period to be considered will be reduced to 01 MONTH, always considering the previous month to the report extraction.

2 Business process

2.1 Gap

As of October 2013, the Ecuadorian Government has published the law that sets DSM Equator as one of the obligated companies to deliver a Report of Credit Sales, in an electronic format (file .TXT) to be delivered through its portal.

This mandatory report may represent penalties in case it’s not delivered, or even, not delivered in time, and it may cause DSM to receive a “red flag” in its status and be blocked from public bids or any other business process that checks Companies’ status.

From the publishing date to its first deadline, the period was too short to produce a solution in SAP. First speed reducer is the fact that, Equator does not have Localization Group in SAP Standard. So, the solution had to be developed from scratch. Also, this Business gap went through an IT reaction time, for matters of resources allocation and analysis. Consequently, the delivery of the solution happened later than the expected time.

1.1 Description

To support the identified requirements, the system has been configured to allow the extraction and maintenance of credit sales data.  Some user profiles will need to be adjusted in order to allow key-users:

• To maintain customer additional data regarding localization
• To maintain customer additional data table for new users and cancelled ones.
• To maintain document type exceptions table
• To run the report itself, as well as saving files to their local station
• To use the Reference Document (VBELN) when performing clearing of Sales Documents in FI transactions

When populating customers’ additional data, it is possible and advisable to create it with reference to an existing customer. Most fields are defaulted with values from the source customer standard table; only the remaining fields with local (Equator) values have to be changed.

The customer additional master record is organized at Country level only. All the data contained in this table is not relevant for foreign customers. 

• Additional data – as additional data we consider the data related to address and personal info, for the natural person customers, as indicated by the flag in KNA1.

1.2 Value Added

This development brings some values added that will enable Business to:

• Comply with Equatorian Government mandatory reports
• Maintain the active and positive COMPANY Status towards the Government
• Report past data, at any time, considering any period, and retrieve the Credit Sales Status at the end of the given period
• Edit and store the resulting report data file (.TXT)
• Associate additional info, not held by Standard SAP, to customers created in Standard tables
• Track payments for future income, categorized by due date, even more than one year after the report limit date
• Track open payments, categorized by due date, even more than one year before the report limit date
• Track all non-cancelled Sales Documents within a given period.
• (BI) Track Credit Sales, with future payments and open payments by Customer (FBL5N) altogether
• (BI) Track Credit Sales by foreign customers – Exports.
• (BI) Track Credit Quota assigned to a customer within a period

Moreover, this development as well as the Mandatory Business Scenario, are both under a maturing process and changes are likely to happen. This development can be enhanced at any time, on the Business demand, and more facilities and values will merge.

1.3 Process flow

See appendix for the process flow charts.

1.4 Scenarios covered

The mandatory delivery of this report not only involves the Sales Scenarios but, also a bunch of other MD and Financial spot scenarios. The code has been commented in order to facilitate maintenance.

Below you can find the scenarios treated by this report:

• Sales view
  • Credit Sales Report from Sales and Logistics (SD) standpoint. For the COMPANY-Equator company code, Credit Sales were considered as all sales with payment terms which were not Cash Payment.
  • Credit Sales Report from Financial (FI) standpoint. With the SD documents as source of data, the report shows all cleared and open payments of those documents, as well as, cleared items for an open credit quota and, open documents of slow payers.
  • Filter for valid document types to be considered in the report.
  • Extraction of Sales Data within a given period, even with past Limit Dates.
• Customers view
  • Filter for Equator enabled customers only.
  • Localization specific data for local customers – Equator
  • Treatment of Companies and Natural Persons type of data (ID Type, gender, marital status….)
• Financial view
  • Retrieval of all “RV” cleared documents for a referring Sales Document
  • Retrieval of all open documents for a Sales operation and its related partial payments, categorized by due date.
  • Retrieval of all open documents for customers under Slow Payment status, and their related partial payments, categorized by due dates
  • Calculation of slow payment values and periods, based on due dates
  • Calculation of remaining open amount of a Credit Quota
  • Demonstration of paid off documents, for the period informed in the selection screen. Once the document has been paid off and is out of the report range date, it’s not considered anymore. This is to prevent duplicity in the delivery to the Government.

1.5 Security

  This development is eligible to follow the S&A Policies and therefore, is prepared to validate users’ profile for its use. 

Up to the moment, UAT, no specific role has been assigned so, the security feature is still disabled.

2 Business procedure

2.1 Create customer general data

Business key users, together with MD Staff, should create/enable a customer to work with Equator Company Code (2795). All possible data should be filled, with special focus to ID Number (STCD1) and Natural Person indicator (STKZN). The report performs some validations during its execution in order to derive the Customer Type and Id Type to be informed in the delivery file.

2.1.1 General Information – Maintenance functions

There are a few different options to get at the customer master maintenance functions. The first possibility is to look at it from an accounting or a sales perspective; second is central (all views on all levels) or decentral. This leads to 4 different menu paths and 4 corresponding transaction codes for creation of a customer:

• Central, from an accounting view, via Accounting / Financial accounting / Accounts receivable / Master records / Maintain centrally /  XD01- Create
• Decentral, from an accounting view, via Accounting / Financial accounting / Accounts receivable / Master records / FD01- Create 
• Decentral, from a sales view, via Logistics / Sales and Distribution / Master Data / Business Partners / Customers / Create / VD01 - Sales and Distribution.
• Central, from a sales view, via Logistics / Sales and Distribution / Master data / Business Partners / Customers  / Create / XD01 – Complete

The same applies to change and display of customers.

2.1.2 Additional Information – Maintenance functions

For matters of attending the needs of this report, another customer table was created to store specific data for Equator (ZEC_CUST_DATA), regarding:

• Natural Person: Cantón, Provincia, Parroquia, gender, marital status and income source
• Company:             Cantón,

The creation of a customer additional, data can be done through transaction ZEC_CUST_DATA, to be assigned to the respective users’ profiles.

The same applies to maintenance and display of customers.

2.2 Change customer data

Business procedure
Description,  Menu Path, Fields, Check points	Values	Comments
Trigger
If it becomes necessary to change any element of a customer master record then the following procedure should be used.
SAP: Change customer
Logistics / Sales and Distribution / Master data / Business partners / Customer / Change / Sales and Distribution	VD02	Central financial master data management group for all funloc customers, locally for all other customers. Transactions XD02, FD02 can also be used.
Customer		Enter the number of the customer which you wish to modify
Company code		Enter the company code for which you wish to make the modification
Sales area		Enter the sales area for which you wish to make the modification
Select the views in which you wish to make modifications from the list provided
Enter
Modify the data on each screen as required
Save customer

2.3 Display changes in customer master data

Changes in customer master data are recorded and can be reviewed. The changes made to the customer master data can be displayed for a specific customer on sales data level, for central customer master data and for multiple customers at once.

Business procedure
Description,  Menu path, Fields, Check points	Values	Comments
Customer account changes
Logistics / Sales and Distribution /  Master data / Business partners / Customer / Display changes / Complete	XD04	Changes shown per customer
Customer number
Company code
Dunning Area
Sales organization
Distribution channel
Division
From change data
Time
Changed by
Field selection
Sensitive fields only E
Company code specific fields only E

Business procedure
Description,  Menu path, Fields, Check points	Values	Comments
Display changes to customers
Logistics / Sales and Distribution /  Master data / Business partners / Customer / Display changes / Several customers	OV51	Changes shown for multiple customers at once
General selections
Customer number
Changed on
Changed by
Further selections
General data E
All customers E
Company code data E
Company code
Sales area data E
Sales organization
Distribution channel
Division
Field group
Output control
Sorting
Technical field names E
Names E

2.4 Maintain specific localization tables

For this report, some additional tables were required. Below you can find them and their characteristics:

Table:                      ZEC_TABLE3

Description:        Table of Provinces in Equator

Usage:                     Additional data to be delivered in the report as one of the customer’s data

Maintenance:   Transaction: ZEC_TABLE3. An initial load was performed, based on the Government sources. In case of maintenance or creation of new ones, this can be done through the transaction given.

Table:                      ZEC_TABLE4

Description:        Table of “Cantóns” in Equator

Usage:                     Additional data to be delivered in the report as one of the customer’s data

Maintenance:   Transaction: ZEC_TABLE4. An initial load was performed, based on the Government sources. In case of maintenance or creation of new ones, this can be done through the transaction given.

Table:                      ZEC_DOCTYP_EXC

Description:        Table of doc types exceptions

Usage:                     Determination of filter for Sales Document Types

Maintenance:   Transaction: ZEC_DOCTYP_EXC. An initial load was performed, based on Business Scenario analysis. This report should retrieve the data from documents that belong to a certain group of types. These types are dynamic so, the removal or inclusion of a new doc type needs to be performed in this table so that the report brings correct data.

Changes in customer master data are recorded and can be reviewed. The changes made to the customer master data can be displayed for a specific customer on sales data level, for central customer master data and for multiple customers at once.

3 Work Instructions

3.1 Before running the Report

Before running this Report for the first time, Business Staff should perform some actions:

• Customer Master Data maintenance: Customers’ data need to be maintained so that correct information is delivered to the Government. Among all the data, some fields should receive special attention – ID Number, Name and Natural Person indicator.
• Customer Master Data (Additional Info) maintenance: specific address data and natural Person data (when applicable) are maintained here. These pieces of data are not present in Standard SAP. This table uses the Customer Number in SAP (KUNNR) as its key. Below, item 4.2 brings a step by step guide for maintaining this table.
• Province / Canton Data maintenance: in case there’s new data or data change, occurred later than the initial load of these tables (JAN/2014), Business people should maintain these tables through their respective transactions. Below, item 4.3 brings a step by step guide for maintaining this table.
• Sales Document Exceptions: in case other Doc types need to be added, or even, any one needs to be removed due to a change in Business, Business people should maintain this table through its respective transaction. There was an initial load (JAN/2014) with the following Doc types: Z2AR / Z2CR / Z2DR / Z2RE / Z2XR / ZF2R. Below, item 4.4 brings a step by step guide for maintaining this table.

3.2 Maintaining Customer Additional Info

The user should access transaction ZEC_CUST_DATA, through the command line (or Favorite menu link)

Then, theuser will access to the CHANGE view of the table. Now, he can use one of the action buttons in the highlighted bar, according to his intention.

NEW ENTRIES

If the user selects the New Entries button, a blank screen will be provided. Then, he can type in the new data. For the Customer Code field, a Search-help is provided to facilitate the user’s procedure.

Other fields like Customer Gender, also provide a drop-down list for fast input.

After inserting and saving all the new data, the user can leave the transaction.

3.3 Maintaining Province codes

The user should access transaction ZEC_TABLE3, through the command line (or Favorite menu link)

Confirm the pop-up message

Then, theuser will access to the CHANGE view of the table. Now, he can use one of the action buttons in the highlighted bar, according to his intention.

NEW ENTRIES

If the user selects the New Entries button, a blank screen will be provided. Then, he can type in the new data and then, press the SAVE button

After inserting and saving all the new data, the user can leave the transaction.

3.4 Maintaining Canton codes

The user should access transaction ZEC_TABLE4, through the command line (or Favorite menu link)

Confirm the pop-up message

Then, theuser will access to the CHANGE view of the table. Now, he can use one of the action buttons in the highlighted bar, according to his intention.

NEW ENTRIES

If the user selects the New Entries button, a blank screen will be provided. Then, he can type in the new data and then, press the SAVE button

During data insertion, the user can make use of the Search-help button for Provinces.

After inserting and saving all the new data, the user can leave the transaction.

3.5 Maintaining Exception Sales Document Types

The user should access transaction ZEC_DOCTYP_EXC, through the command line (or Favorite menu link)

Then, the user will access to the CHANGE view of the table. Now, he can use one of the action buttons in the highlighted bar, according to his intention.

NEW ENTRIES

If the user selects the New Entries button, a blank screen will be provided. Then, he can type in the new data and then, press the SAVE button

After inserting and saving all the new data, the user can leave the transaction.

3.6 Running the Report

Through transaction ZDINARDAP, users will be able to access the Selection Parameters Screen of the report. As there’s no strict policy for languages for deliverables, or at least not explicitly presented, the texts of the Report are all presented in Spanish Language, no matter the logon language associated to the user.

• In the command line (or via Favorite Menu link), the user should access:
  
• The system will then show the Selection Screen with the following parameters:
  
  • Nr Documento                     à here the user can select one Sales Document, some or even a range of them. The Screen controls work the same as any other SAP development
  • Tp Documento                     à this parameter works the same as above but for Sales Document Types. It can work as a secondary filter to Doc types once there’s another filter determined by Business, through the provided Ztable for this development.
  • Org. de Ventas                     à the same as above. The default value ‘2337’ will always be shown, no matter user runs the report. This value can be deleted, adjusted or even incremented with others.
  • Canal de Distribuición   à the same as above, with no previous definitions of values.
  • Cliente                                      à the same as above, with no previous definitions of values.
  • Doc. Referencia                                     à the same as above, with no previous definitions of values. This topic is a heads-up because, during the accounting procesing of customers’ payments, the users do not inform the reference document (VBELN) by procedure. Business stakeholders are to provide key users training and knowledge transfer about this new procedure.
  • Fecha Facturación            à this parameter is the most important because, besides being mandatory, it will determine the compentence period of the result data and drive the data fetching during processing.
  • Codigo de la Entidad       à this is a fixed value, determined by the Government, by the time COMPANY Equator made its registration in the determined portal.
  • Generar archivo local     à this second part of the Selection Screen is guided for the final flat file generation. The file will have the same fields shown on screen but the two first ones, because they are auxiliary fields for data manipulation by the key-users but, they are not part of the official layout to be delivered. Once the key-users have marked the generation flag (Activar generación), they just need to inform the destination path of the file to be generated.

NOTE:        for the first phase of the report usage, as the data to be retrieved is far in the past, some of the selection parameters may not work properly. That can be explained to the fact that, they have effect over cleared documents (BSAD table), but when needs to retrieve all open documents for up to the upper limit date of the report, the program only uses the Sales Organization and Limit Dates as parameters.
This situation will disappear by the time files start being generated right in the subsequent month to the Sales Documents generation.

4 IT Delivery

4.1 Deliverables to Running Team

The Running Support Team will receive a product that belongs to the FI/CO Competence Group. This product is a mandatory report to be electronically delivered to the Government. It’s a under maturing process solution, even by the Official side so, changes are likely to happen.

Up to the moment, this electronic delivery will take place via flat file that will be uploaded in the Government portal. The regular frequency of delivery is monthly, around the fifth day of the following month to the reported data.

After delivering the file for the first time, the user will receive a protocol number (or file). There’s no specification of storage of such number or file. It’s still up to the user’s procedure.

Future waves of this solution are likely to happen, both for the evolution of the solution to meet the Government’s expectations, both for future delivered solutions by Standard SAP.

SAP A/G has no localization components for Ecuador, so COMPANY Global BPOs must accept this solution (Z_program) as the only and valid workaround for keeping Equator CC compliant.

A full pack was created in the system: ZEC. All the objects were encapsulated in this pack, for better maintenance and tansport.

4.2 Delivered Objects

System	Obj Type	Object Name	Created by
R3TR	PROG	ZEC_SD_DINARDAP_EXTRACTOR	CARVALHOA
R3TR	TRAN	ZDINARDAP	CARVALHOA
R3TR	MSAG	ZYBRCO	CARETTAH
R3TR	DEVC	ZEC	CARVALHOA
R3TR	DOMA	ZEC_CUSTGEND	CARVALHOA
R3TR	DOMA	ZEC_CUSTICSRC	CARVALHOA
R3TR	DOMA	ZEC_CUSTIDTP	CARVALHOA
R3TR	DOMA	ZEC_CUSTMARST	CARVALHOA
R3TR	DTEL	ZCODE	CARVALHOA
R3TR	DTEL	ZCODE_TAB4	CARVALHOA
R3TR	DTEL	ZEC_CANTCD	CARVALHOA
R3TR	DTEL	ZEC_CUSTCL	CARVALHOA
R3TR	DTEL	ZEC_CUSTGEND	CARVALHOA
R3TR	DTEL	ZEC_CUSTICSRC	CARVALHOA
R3TR	DTEL	ZEC_CUSTIDTP	CARVALHOA
R3TR	DTEL	ZEC_CUSTMARST	CARVALHOA
R3TR	DTEL	ZEC_DESCR	CARVALHOA
R3TR	DTEL	ZEC_PARRCD	CARVALHOA
R3TR	DTEL	ZEC_PROVCD	CARVALHOA
R3TR	FUGR	ZEC_CUST_DATA	CARVALHOA
R3TR	FUGR	ZEC_DOCTYP_EXC	CARVALHOA
R3TR	FUGR	ZEC_TABLE3	CARVALHOA
R3TR	FUGR	ZEC_TABLE4	CARVALHOA
R3TR	LDBA	ZDINARDAP	CARVALHOA
R3TR	SHLP	ZEC_PROVINCE	CARVALHOA
R3TR	TABL	ZEC_CUST_DATA	CARVALHOA
R3TR	TABL	ZEC_DOCTYP_EXC	CARVALHOA
R3TR	TABL	ZEC_TABLE3	CARVALHOA
R3TR	TABL	ZEC_TABLE4	CARVALHOA
R3TR	TOBJ	ZEC_CUST_DATAS	CARVALHOA
R3TR	TOBJ	ZEC_DOCTYP_EXCS	CARVALHOA
R3TR	TOBJ	ZEC_TABLE3S	CARVALHOA
R3TR	TOBJ	ZEC_TABLE4S	CARVALHOA
R3TR	TRAN	ZEC_CUST_DATA	CARVALHOA
R3TR	TRAN	ZEC_DOCTYP_EXC	CARVALHOA
R3TR	TRAN	ZEC_TABLE3	CARVALHOA
R3TR	TRAN	ZEC_TABLE4	CARVALHOA

The code for program ZEC_SD_DINARDAP_EXTRACTOR is fully commented for beter guidance during future enhancements and corrections. The running team should have no problems understanding. Future enhancement for OO model coding is also possible through a Class development.

4.3 New transactions

Users have received new transactions for maintaining auxiliary tables and running the report. Such transactions have been added to users’ profiles. They are:

ZDINARDAP                            - Credit Sales Data Extraction - Ecuador

ZEC_TABLE3 - Table of Provinces

ZEC_TABLE4 - Table of Cantóns

ZEC_DOCTYP_EXC - Table of Sales Document Types Exceptions

ZEC_CUST_DATA - Table of Additional Local Customers Data - Equator

[antonio_longo1]

Dear Alexandre,

thank you for your BLOG on this legal requirement for Ecuador, we are interested in the same. Could you please share the PDF you are mentioning? I was not able to find any link to it.

Also, where we could fine the source code of the program also reported here? Is there a possibility to download/upload it via SAPLink or other methods?

Thanks

Antonio