Extract/BIP Query

To Schedule HCM Extract  to run Sysdate+1

Log in to the application as an HCM Administrator. 

 Navigator > Data Exchange > Refine HCM Extracts. 

 Select the Payroll Flow. On the Manage Payroll Flow Patterns page, go to the Tasks tab, highlight the       appropriate task, and click Edit. 

Click the pencil icon below the tabs (Tasks, Task Sequence, Parameters). 

Click the Edit pencil icon next to the task that you want to edit. 

On the Edit Task Details: Basic Information flow, locate the Effective Date parameter and click on that row. 

The Effect Date: Parameter Details region will now show at the bottom of the screen. 

In the Effect Date: Parameter Details region, set these parameter values: 

         Parameter Basis = Post SQL 

         Use Below query 

SELECT NVL((SELECT flow_param_value 

             FROM pay_flow_param_values pfpv 

                , pay_flow_parameters pfp 

            WHERE pfpv.flow_instance_id =:pFlowInstanceId 

              And pfpv.base_flow_parameter_id  =pfp.base_flow_parameter_id 

 And pfp.base_flow_parameter_name = 'EFFECTIVE_DATE' 

 And pfp.base_flow_id =:pFlowId ),(SYSDATE+1)) from dual

BIP Scheduled History

SELECT (CASE

              WHEN state = 1 THEN 'Wait'

              WHEN state = 2 THEN 'Ready'

              WHEN state = 3 THEN 'Running'

              WHEN state = 4 THEN 'Completed'

              WHEN state = 9 THEN 'Cancelled'

              WHEN state = 10 THEN 'Error'

              WHEN state = 12 THEN 'Succeeded'

              WHEN state = 13 THEN 'Paused'

              ELSE TO_CHAR (state)

          END)

             request_state,

         erh.SUBMITTER,

erh.PROCESSSTART,

erh.PROCESSEND,

erp.VALUE  

    FROM fusion.ess_request_history erh, fusion.ess_request_property erp

   WHERE     1 = 1

         AND erh.requestid = erp.requestid

         AND erp.name = 'report_url'

         AND erp.VALUE LIKE

'/Custom/<Report Path>/Report_name.xdo'

ORDER BY erh.requestid DESC

Query to get last Extract Run Time

select max(pfi.last_update_date) FROM pay_flows pf ,pay_flow_instances pfi WHERE 1=1 AND pf.base_flow_id = pfi.base_flow_id AND pf.base_flow_name = '<Extract Name>' and pfi.status = 'COMPLETED' and pfi.last_update_date <> (select max(pfi1.last_update_date) FROM pay_flows pf1 ,pay_flow_instances pfi1 WHERE 1=1 AND pf1.base_flow_id = pfi1.base_flow_id AND pf1.base_flow_name = '<Extract Name>' and pfi1.status = 'COMPLETED' )