1. Create a step that finds the “monthly” period type.

 

1 SQL> create step s_get_month_period_type as (set v_month_pt_name = select name from xactly.xc_period_type where Uppercase(name) like ‘MON%’);

Locate a status_message of “ok” in the output to confirm your command succeeded.

 

2. Create a step to set the last day of the monthly period to run.

 

1 SQL> create step s_set_period_end_date as (set v_period_end_date = ToDate(‘2016-01-31’));

 

Again, confirm that your command succeeded in the command output.

 

3. Create a step that determines the period name.

 

1 SQL> create step s_set_period_name as (set v_period_name = LookupPeriodName(:v_period_end_date,:v_month_pt_name));

Note that we used the variables created in the first two steps in this step’s definition.

 

4. Create a step to insert payment data into a custom delta table.

 

1 SQL> create step s_create_pay_extract as(insert into Delta(TableName=‘delta.payfile_extract’, Overwrite=true)

select part.first_name, eff_participant_id, period_name, order_code, item_code, release_date, is_final,amount, ut.name

from xactly.xc_payment pay

join xactly.xc_unit_type ut on ut.unit_type_id = pay.amount_unit_type_id

join xactly.xc_participant part on part.participant_id = pay.eff_participant_id where pay.period_name = :v_period_name);

 

Note that the WHERE clause above references the v_period_name variable we set in step 3.