CDL (Commission Data Loader) – Data Extract Process

Dear Readers,

Summary

The Data extract feature allows you to export through custom outbound file types when running a pipeline job. The feature is available only on environments that use CDL (Commissions Data Loader) and are configured on HANA Database. You can extract custom files that you have previously or newly configured in CDL. The files are then stored in your previously configured outbound folder.

Prerequisites

• User should have access to run a pipeline
• User should have access to WebIDE/Hana Studio/Eclipse for connecting to Tenant DB
• Custom Table and Custom Stored Proc should be created for the data to transfer
• SFTP dropbox Access to Outbound folder.
• RestAPI v2 and OData API Access for debugging

Data Extract Documentation and Examples

Steps to Setup Data Extract Process in Commission Data Loader (CDL)

Login to SAP Commission Portal with your credentials

After successful login, you can click on APPS highlighted in RED, a dropdown will appear and choose Commission Data Loader

Now you can click on Configuration and select from dropdown File Type Setup

As you see, there are 2 types Inbound & Outbound file type…  To know more about File Types 

Click on Outbound and Click on (+) to create

Configure outbound file type and specify the target table and target stored procedure from below screens

As this task needs to be handled manually, please configure as per the above example

After Saving, you can see from below custom file type is created

Let’s create a Custom Table in HANA DB

Create a custom table to store the Extracted data in the EXT schema. (Extraction  will always run with the EXT schema)

Connect WebIDE or HANA Database with below example provided.

Example 

CREATE COLUMN TABLE Paymentextract (
positionseq BIGINT NOT NULL,
participantseq BIGINT NOT NULL,
periodseq BIGINT NOT NULL,
processingunitseq BIGINT NOT NULL,
payment DECIMAL(25,10) NULL,
unittypeforpayment BIGINT NULL
);

Create Custom Procedure in HANA DB

Create a custom stored procedure to extract the data:

CREATE OR REPLACE PROCEDURE ExtractPayment(OUT FILENAME varchar(120), IN pPlRunSeq BIGINT)
LANGUAGE SQLSCRIPT
SQL SECURITY INVOKER
AS
BEGIN
DECLARE DBMTK_TRUE TINYINT := 1; DECLARE DBMTK_FALSE TINYINT := 0; /* boolean constants */
DECLARE vSeq BIGINT;
declare vPeriodSeq bigint;
declare vPUSeq bigint;

select periodSeq into vPeriodSeq from CS_PlRun where pipelineRunSeq = :pPlRunSeq;
select processingUnitSeq into vPUSeq from CS_PlRun where pipelineRunSeq = :pPlRunSeq;

delete from Paymentextract;

insert into Paymentextract (positionSeq, participantSeq, periodSeq, processingUnitSeq, payment, unitTypeForPayment)
(select positionSeq, payeeSeq, periodSeq, processingUnitSeq, value, unitTypeForValue from CS_Payment
where periodSeq = :vPeriodSeq and processingUnitSeq = :vPUSeq);

commit;

FILENAME := 'PAYMENTEXTRACT.txt';
END

Run a Pipeline for the Data Extract Stage

The Data Extracts stage (option) can be selected from the PipelineRun wizard and below screen is provided with an example which is configured as per above

DataExtract file in Outbound Folder

After the pipeline job is completed with Success, you should see the file extracted in the tenant’s outbound directory (specified during CDL setup): To know more about dropbox (sFTP)

To Automate through RestAPI for Pipeline Job with Data Extract follow below

Request Method : POST 
Request URL : https://<tenantid>.callidusondemand.com/api/v2/pipelines
Content-Type: application/json
Authorization: {{authtoken}}

[
  {
      "command": "PipelineRun",
      "stageTypeSeq": "21673573206720532",
      "calendarSeq": "2251799813685250",
      "periodSeq": "2533274790396303",
      "processingUnitSeq": "38280596832649218",
      "runMode": "full",
      "removeStaleResults":false,
      "onDemand":true,
      "defer":false,
      "runStats": true,
      "traceLevel": "status",
      "runDataExtracts":true,
      "dataExtractsFileType":"PAYMENTEXTRACT",
      "userId":"yogananda.muthaiah@sap.com"
  }
]

Conclusion: Customer/Admins/Finance Team will save man-hours to extract the data from the DB session in a Production environment and moreover this will be a one-time setup for payout extract and easily configurable through UI.

Original Article:
https://blogs.sap.com/2020/05/12/cdl-commission-data-loader-data-extract-process/