Almost every Commissions system will need a payfile to be extracted.
With the Express data loader and WebIDE in HANA it is easier than ever to write some SQL and generate yourself a payfile.
However, there are a number of things to consider when extracting a payfile that relate to the way Incentive Manager handles payments.
This blog post will hopefully help you avoid a lot of the common mistakes we have seen over our years of consulting. We will start off with some simple queries to demonstrate some basic concepts, and then build up to a more robust query.
Understand the Payment Pipeline Process
Before creating any pay files it is critical to understand how the payment process in Incentive Manager works.
The pay stage of the pipeline generates payments which are held in the database in the CS_PAYMENT table.
A payment is not final until the Post Pipeline has been run. Until that point all payments are considered “Trial” payments.
The SAP Documentation provides a good guide to this:
https://help.sap.com/docs/SAP_Commissions/4d9ef4343250406ca4a28b5508195aca/726488857c231014a804993ce...
One of the most common mistakes we see (from those new to commissions) is to simply take a complete extract of the payment table as a payfile.
For example:
SELECT *
FROM CS_PAYMENT
WHERE PERIODSEQ = [2XXXXXXXXX]
If you consider the above you will see that this will result in Trial payments being included in your payfile.
This is fine as long as you understand that they are trial payments which could change in future pipeline runs! This is not ideal if you need your payfile to accurately reflect commission payments. If you sent out a payfile generated in this way, and someone subsequently reran a pipeline, all of your results could change.
The key is to ensure that we only look at Posted payments. Once a payment is Posted then it is stored in the CS_PAYMENT table along with a value of the pipelinerunseq stored in the POSTPIPELINERUNSEQ column. (I always think of it like putting a cheque in the post… once this has happened that payment cannot be changed and is thus locked down).
Tip 1: Always look for Posted payments!
We can tell if a payment has been posted by looking for a value in the PostPipelineRunseq column. If it is populated, then that payment is posted. The seq number will link us back to the Pipeline Posting run from which additional information can be obtained.
So, the below is a far more useful query than the one above as it will return only the posted payments:
SELECT *
FROM CS_PAYMENT
WHERE POSTPIPELINERUNSEQ IS NOT NULL
AND PERIODSEQ = [2XXXXXXXX]
This is useful but still not complete. There is nothing to prevent us from posting multiple times in a period.
The above query would bring back every single posted payment in a specific period. If you are seeking to generate a payfile for each batch of posted payments then this query might include posted payments which have already been included in a previous payfile, which is obviously not ideal.
The solution to this problem is an approach which considers both the unposted (trial) payments and the posted payments together.
We need a way of keeping track of which Posted Payments have already been extracted, and a way of indicating which payments are trial (not posted yet) so that we can preview those before they are sent.
We can do this using a control table in the EXT schema to hold all of the extracted Post PipelineRunseq values.
We can then use this control table to extract only those payments from CS_PAYMENT which are Trial payments or Posted payments which have not yet been extracted:
CREATE OR REPLACE PROCEDURE EXT.PAYFILE(OUT FILENAME VARCHAR(120), IN pPlRunSeq BIGINT)
LANGUAGE SQLSCRIPT
SQL SECURITY DEFINER
AS
/*--------------------------------------------------------------------------------------
| Author: Philip Holtom
| Project Title:
| Company: OpenSymmetry
| Initial Version Date: 2022
|---------------------------------------------------------------------------------------
| Procedure Purpose: Extract PayFile
|---------------------------------------------------------------------------------------
|---------------------------------------------------------------------------------------
| Version: 1.1
| Comments: Initial Version
| 1.0 2022 - Inital Version
| 1.1 2023 - Update to an IN rather than >
|----------------------------------------------------------------------------------------
*/
BEGIN
DECLARE vSeq BIGINT;
DECLARE g_removedate CONSTANT DATE := TO_DATE('01/01/2200','dd/mm/yyyy');
DECLARE vPeriodSeq BIGINT;
DECLARE v_rundate DATE := CURRENT_DATE;
DECLARE v_lastpost NUMBER;
DECLARE v_lastpostcnt NUMBER;
DECLARE vPUSeq BIGINT;
DECLARE vPeriodName VARCHAR(255);
--Get our Variables based on the Pipeline Run
SELECT periodSeq INTO vPeriodSeq FROM CS_PlRun WHERE pipelineRunSeq = :pPlRunSeq;
SELECT processingUnitSeq INTO vPUSeq FROM CS_PlRun WHERE pipelineRunSeq = :pPlRunSeq;
--Get the last posted periodseq from the logs to compare to the current posted periodseq
SELECT IFNULL(MAX(LASTPOSTPIPELINERUNSEQ),0) INTO v_lastpost
FROM EXT.PAYFILE_LOG PAY
WHERE PAY.PERIODSEQ = :vPeriodSeq;
seqs = SELECT POSTPIPELINERUNSEQ
FROM CS_PAYMENT PAY
WHERE NOT EXISTS
(SELECT POSTPIPELINERUNSEQ FROM EXT.PAYFILE_LOG
WHERE LASTPOSTPIPELINERUNSEQ = PAY.POSTPIPELINERUNSEQ)
AND PAY.PERIODSEQ = :vPeriodSeq;
DELETE FROM EXT.OUTBOUND_PAYFILE WHERE PERIODSEQ = :vPeriodSeq;
INSERT INTO EXT.OUTBOUND_PAYFILE
(PERIODSEQ,PERIODNAME,PAYEEID,EARNINGCODE,EARNINGGROUP,VALUE)
SELECT PER.PERIODSEQ,
PER.NAME,
PYE.USERID,
PAY.EARNINGCODEID,
PAY.EARNINGGROUPID,
PAY.VALUE
FROM CS_PAYMENT PAY
JOIN CS_PERIOD PER
ON PER.PERIODSEQ = PAY.PERIODSEQ
AND PER.REMOVEDATE = g_removedate
JOIN CS_PARTICIPANT PYE
ON PAY.PAYEESEQ = PYE.PAYEESEQ
AND (
(
PYE.EFFECTIVESTARTDATE < PER.ENDDATE AND PYE.EFFECTIVEENDDATE >= PER.ENDDATE
)
OR
(
PYE.EFFECTIVEENDDATE > PER.STARTDATE
AND PYE.EFFECTIVEENDDATE <= PER.ENDDATE
AND PYE.ISLAST =1
)
)
AND PYE.REMOVEDATE = g_removedate
WHERE PAY.PERIODSEQ = vPeriodSeq
AND PAY.POSTPIPELINERUNSEQ IN (SELECT POSTPIPELINERUNSEQ FROM :seqs);
--Update the log file based on the latest run so we know what has paid / posted already
MERGE INTO EXT.PAYFILE_LOG l
USING
(SELECT NULL AS PERIODNAME
,vPeriodSeq AS PERIODSEQ
,POSTPIPELINERUNSEQ AS LASTPOSTPIPELINERUNSEQ
,v_rundate AS LASTRUNDATE
FROM CS_PAYMENT PAY
WHERE PAY.PERIODSEQ = vPeriodSeq
AND POSTPIPELINERUNSEQ IS NOT NULL
GROUP BY periodseq,vPeriodSeq,v_rundate,POSTPIPELINERUNSEQ) s
ON (s.PERIODSEQ = l.periodseq AND s.LASTPOSTPIPELINERUNSEQ = l.LASTPOSTPIPELINERUNSEQ)
WHEN MATCHED THEN
UPDATE SET l.LASTRUNDATE = s.LASTRUNDATE
,l.LASTPOSTPIPELINERUNSEQ = s.LASTPOSTPIPELINERUNSEQ
WHEN NOT MATCHED THEN
INSERT (PERIODNAME,PERIODSEQ,LASTPOSTPIPELINERUNSEQ,LASTRUNDATE)
VALUES(S.PERIODNAME,S.PERIODSEQ,S.LASTPOSTPIPELINERUNSEQ,S.LASTRUNDATE);
COMMIT;
END
In this procedure we are looking for POSTPIPELINERUNSEQ not existing in our control table of already extracted posted payments.
If we wanted to show trial posted payments we could extract these in a separate extract and search for POSTPIPELINERUNSEQ is null.
The next stage of the process is then to store this new posted pipelinerunseq so we know we have extracted the payfile. So that the next time it is run it will pick up new posted payments. This does mean that we get one extract of a payfile.
From the above example you can hopefully see a more robust way of extracting payments rather than simply taking the whole payment table every time. We need to consider if a payment has been posted and account for multiple postings and extracts.
You could also expose the control table via a workflow if desired to remove values from the control table and re-run payfiles if needed.
Other Considerations For Best Practice
Another common mistake we see is to simply extract payment information straight from the deposit table (CS_DEPOSIT).
The key point to realise here is that
SUM(Deposits) <> Payments!
The payment table will take into account many different factors, such as if the deposit is held and other concepts such as balances.
There is often a case for needing to bring a deposit into a pay file due to the use of attributes on the deposits or other considerations.
However, this should be done in the right way using the trace-tables which we will cover in the next blog.
