Skip to Content
Technical Articles

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.

7 Comments
You must be Logged on to comment or reply to a post.
  • Hi Yogananda,

    In your example above you create a custom CDL outbound file type named “PAYOUTEXTRACT”, then when you run the pipeline and use the Data Extracts feature (both with UI and rest api call) you use file type “PAYMENTEXTRACT”.

    I’d assume that’s a typo?

    Nice article otherwise, it’s helpful to know the plRunSeq will be automatically passed to the proc in this scenario.

    -Dan H

  • Thanks Yogananda for the article. We are on Oracle instance and eventually migrating to HANA.  Quick question – We usually generate Outbound – PAYMENT extract  once we run Finalize pipeline. In the screenshot mentioned above, Data extracts option available under Comp and Pay stage. Could you please share your feedback how we could utilize this Data Extract process with finalize pipeline ? Thanks, Srinivas

  • Hi Yogananda

    In example above Parameter pipelinerunseq is being passed from Pipeline to Stored Procedure. Are there more parameters available for use ? e.g. periodseq, calendarseq, etc.. Any standard documentation available?

     

    Regards

    Sohil.

    • Hi Sohil Vidja

      It depends and varies by customer implementation on using DataExtract.

      For example, If customer has multiple calendars then you may need to include calendarseq

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

      Note: you need include in Declare and In select statement