1. User Guide
  2. Data Loaders
  3. Pricing Management Transfer Rate Population

5.13 Pricing Management Transfer Rate Population

This function populates FSI_M_PROD_TRANSFER_RATE table from FSI_PM_GENERATED_INSTRMTS table for particular Effective date.

After executing this procedure, you should query FSI_M_PROD_TRANSFER_RATE table.

Executing the POPULATE_PM_TRANS_RATE_TABLE (earlier known as POPULATE_TPOL_TRANS_RATE) Procedure

You can execute this procedure either from SQL*Plus or from within a PL/SQL block or from Batch Maintenance window within OFSAAI framework.

To run the procedure from SQL*Plus, login to SQL*Plus as the Schema Owner.

The procedure requires the following 6 parameters:

  • Batch Id (Batch_Id): can be used to see the log of the procedure executed.
  • Misdate (Mis_date): the date for which batch is run.
  • Run Id (p_v_run_id): Unique Run ID for the run.
  • Process Id (p_v_process_id): Unique Process ID for the batch.
  • Run Execution Id (p_v_run_execution_id): Unique Run Execution Id for the Run.
  • Run skey (p_n_run_skey): Unique run skey generated by the run.

The syntax for calling the procedure is:

Declare output number; Begin Output:= POPULATE_PM_TRANS_RATE_TABLE (Batch_Id varchar2, Mis_date varchar2, p_v_run_id varchar2, p_v_process_id varchar2, p_v_run_execution_id varchar2, p_n_run_skey varchar2); End;

Mis_date should be passed as 'YYYYMMDD' format.

An example of running the function from SQL*Plus is as follows:

SQL> var output number; SQL> execute: output:= POPULATE_PM_TRANS_RATE_TABLE('Batch_Id', '20100131,' $RUNID=1306182237482', '$PHID=1228363751510', '$EXEID=RQEXE016','$RUNSK=99');

To execute the stored procedure from within a PL/SQL block or procedure, see the example that follows.

SQL> declare output number; begin Output:= POPULATE_PM_TRANS_RATE_TABLE ('Batch_Id','Mis_date', 'p_v_run_id','p_v_process_id','p_v_run_execution_id',' p_n_run_skey'); End; /

To execute the procedure from OFSAAI Batch Maintenance, create a new Batch with the Task as TRANSFORM DATA and specify the following parameters for the task:

  • Datastore Type : Select appropriate datastore from the list
  • Datastore Name : Select appropriate name from the list
  • IP address : Select the IP address from the list
  • Rule Name : POPULATE_PM_TRANS_RATE_TABLE

BATCHID and MISDATE will be passed explicitly in Batch Maintenance