eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.HRI_OLTP_PMV_DTL_WRK_EVENT SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 14


/* derived from the INSERTs into the table HRI_DBI_**_WMV_CHGS. This SQL can  */
/* be found in the calc_events_** procedures of the headcount changes package */
/* HRI_DBI_WMV_CHANGES (hriwvch.pkb).                                         */
/******************************************************************************/

/******************************************************************************/
/* Returns the number of hires which should match the figure where the drill  */
/* came from because it runs off the same DBI summary table.                  */
/*                                                                            */
/* All the lookup views are DBI specific for easy DBI maintenance             */
/******************************************************************************/

PROCEDURE get_hire_detail2(p_param          IN  BIS_PMV_PAGE_PARAMETER_TBL,
                           x_custom_sql     OUT NOCOPY VARCHAR2,
                           x_custom_output  OUT NOCOPY BIS_QUERY_ATTRIBUTES_TBL) IS

  l_lnk_emp_name       VARCHAR2(4000);

Line: 33


  l_select_clause      VARCHAR2(4000);

Line: 74


  l_select_clause :=
'SELECT -- headcount hire detail' || g_rtn ||
/* View by name of person hired */
' peo.value              VIEWBY ' || g_rtn ||
/* Name of person hired */
',peo.value              HRI_P_PER_LNAME_CN ' || g_rtn ||
',peo.id                 HRI_P_PER_ID ' || g_rtn ||
',''' || l_lnk_emp_name || '''
                         HRI_P_DRILL_URL1' || g_rtn ||
/* Manager of person hired as of hire date */
',sup.value              HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
',sup.id                 HRI_P_SUP_ID ' || g_rtn ||
',''' || l_lnk_mgr_name || '''
                         HRI_P_DRILL_URL2' || g_rtn ||
/* Organization person hired into */
',org.value              HRI_P_ORG_CN ' || g_rtn ||
/* Country where person was hired */
',ctr.value              HRI_P_GEO_CTY_CN ' || g_rtn ||
/* Job person was hired into (using default display configuration) */
',hri_bpl_job.get_job_display_name
        (job.id
        ,job.business_group_id
        ,job.value)      HRI_P_JOB_CN ' || g_rtn ||
/* Person type on hire date */
', null                  HRI_P_CHAR1_GA ' || g_rtn ||
/* Hire Date converted to chars */
',tch.effective_date     HRI_P_DATE1_GA ' || g_rtn ||
/* Event Type - for future use */
',null                   HRI_P_CHAR2_GA ' || g_rtn ||
/* Order by default name sort order */
',peo.order_by           HRI_P_ORDER_BY_1';

Line: 137


  l_select_clause :=
'SELECT -- headcount hire detail' || g_rtn ||
/* View by name of person hired */
' peo.value              VIEWBY ' || g_rtn ||
/* Name of person hired */
',peo.value              HRI_P_PER_LNAME_CN ' || g_rtn ||
',peo.id                 HRI_P_PER_ID ' || g_rtn ||
',''' || l_lnk_emp_name || '''
                         HRI_P_DRILL_URL1' || g_rtn ||
/* Manager of person hired as of hire date */
',sup.value              HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
',sup.id                 HRI_P_SUP_ID ' || g_rtn ||
',''' || l_lnk_mgr_name || '''
                         HRI_P_DRILL_URL2' || g_rtn ||
/* Organization person hired into */
',org.value              HRI_P_ORG_CN ' || g_rtn ||
/* Country where person was hired */
',ctr.value              HRI_P_GEO_CTY_CN ' || g_rtn ||
/* Job person was hired into (using default display configuration) */
',hri_bpl_job.get_job_display_name
        (job.id
        ,job.business_group_id
        ,job.value)      HRI_P_JOB_CN ' || g_rtn ||
/* Person type on hire date */
', null                  HRI_P_CHAR1_GA ' || g_rtn ||
/* Hire Date converted to chars */
',hri_asg.effective_change_date     HRI_P_DATE1_GA ' || g_rtn ||
/* Event Type - for future use */
',null                   HRI_P_CHAR2_GA ' || g_rtn ||
/* Order by default name sort order */
',peo.order_by           HRI_P_ORDER_BY_1';

Line: 215


  x_custom_sql := l_select_clause    || g_rtn
                ||l_from_clause      || g_rtn
                ||l_where_clause     || g_rtn
                ||l_security_clause  || g_rtn
                ||l_orderby_clause;

Line: 234


  l_select_clause      VARCHAR2(4000);

Line: 273


  l_select_clause :=
'SELECT -- headcount terminations detail' || g_rtn ||
/* View by name of person terminated */
' peo.value              VIEWBY ' || g_rtn ||
/* Name of person terminated */
',peo.value              HRI_P_PER_LNAME_CN ' || g_rtn ||
/* Manager of person terminated as of termination date */
',sup.value              HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
',sup.id                 HRI_P_SUP_ID ' || g_rtn ||
',''' || l_lnk_mgr_name || '''
                         HRI_P_DRILL_URL1' || g_rtn ||
/* Organization person terminated from */
',org.value              HRI_P_ORG_CN ' || g_rtn ||
/* Country where person was terminated */
',ctr.value              HRI_P_GEO_CTY_CN ' || g_rtn ||
/* Job person was terminated from (using default display configuration) */
',hri_bpl_job.get_job_display_name
        (job.id
        ,job.business_group_id
        ,job.value)      HRI_P_JOB_CN ' || g_rtn ||
/* Person type on termination date */
',''''                   HRI_P_CHAR1_GA ' || g_rtn ||
/* bug 3147015 Most recent hire date of terminated person */
',hri_asg.pow_start_date_adj     HRI_P_DATE1_GA ' || g_rtn ||
/* Termination Date */
',tch.effective_date-1   HRI_P_DATE2_GA ' || g_rtn ||
/* Termination Reason */
',decode(hri_asg.worker_term_ind
         , 1, hrl.meaning
         , to_char(null))      HRI_P_CHAR2_GA ' || g_rtn ||
/* Period of work in years
Length of Service is defined as the number of years (in decimal format) between an employee's
most recent hire date and the event date (termination date)
*/
',DECODE(tch.wkth_wktyp_sk_fk,''EMP'',pow_days_on_event_date/365
        ,DECODE(tch.wkth_wktyp_sk_fk,''CWK'',pow_months_on_event_date
               ,0)
        )                HRI_P_MEASURE1 '|| g_rtn ||
/* Performance Band */
',prf.value              HRI_P_CHAR3_GA ' || g_rtn ||
/* Event Type - for future use */
',null                   HRI_P_CHAR4_GA' || g_rtn ||
/* Order by default person name sort order */
',peo.order_by           HRI_P_ORDER_BY_1 ';

Line: 355


l_select_clause :=
'SELECT -- headcount terminations detail' || g_rtn ||
/* View by name of person terminated */
' peo.value              VIEWBY ' || g_rtn ||
/* Name of person terminated */
',peo.value              HRI_P_PER_LNAME_CN ' || g_rtn ||
/* Manager of person terminated as of termination date */
',sup.value              HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
',sup.id                 HRI_P_SUP_ID ' || g_rtn ||
',''' || l_lnk_mgr_name || '''
                         HRI_P_DRILL_URL1' || g_rtn ||
/* Organization person terminated from */
',org.value              HRI_P_ORG_CN ' || g_rtn ||
/* Country where person was terminated */
',ctr.value              HRI_P_GEO_CTY_CN ' || g_rtn ||
/* Job person was terminated from (using default display configuration) */
',hri_bpl_job.get_job_display_name
        (job.id
        ,job.business_group_id
        ,job.value)     HRI_P_JOB_CN ' || g_rtn ||
/* Person type on termination date */
',null                  HRI_P_CHAR1_GA ' || g_rtn ||
/* bug 3147015 Most recent hire date of terminated person */
',hri_asg.pow_start_date_adj     HRI_P_DATE1_GA ' || g_rtn ||
/* Termination Date */
',hri_asg.effective_change_date-1      HRI_P_DATE2_GA ' || g_rtn ||
/* Termination Reason */
',decode(hri_asg.worker_term_ind
         , 1, hrl.meaning
         , to_char(null))      HRI_P_CHAR2_GA ' || g_rtn ||
/* Period of work in years
Length of Service is defined as the number of years (in decimal format) between an employee's
most recent hire date and  the event date (termination date).
*/
',DECODE(tch.wkth_wktyp_sk_fk,''EMP'',pow_days_on_event_date/365
        ,DECODE(tch.wkth_wktyp_sk_fk,''CWK'',pow_months_on_event_date
               ,0)
        )
                         HRI_P_MEASURE1 '|| g_rtn ||
/* Performance Band */
',prf.value              HRI_P_CHAR3_GA ' ||
/* Event Type - for future use */
',null                   HRI_P_CHAR4_GA' || g_rtn ||
/* Order by default person name sort order */
',peo.order_by           HRI_P_ORDER_BY_1 ' ;

Line: 456


  x_custom_sql := l_select_clause    || g_rtn
                ||l_from_clause      || g_rtn
                ||l_where_clause     || g_rtn
                ||l_security_clause  || g_rtn
                ||l_orderby_clause;

Line: 477


  l_select_clause      VARCHAR2(4000);

Line: 515


  l_select_clause :=
'SELECT -- headcount transfers in detail' || g_rtn ||
/* View by name of person transferred */
' peo.value              VIEWBY ' || g_rtn ||
/* Name of person transferred */
',peo.value              HRI_P_PER_LNAME_CN ' || g_rtn ||
',peo.id                 HRI_P_PER_ID ' || g_rtn ||
',''' || l_lnk_emp_name || '''
                         HRI_P_DRILL_URL1' || g_rtn ||
/* Transfer to Manager */
',sup.value              HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
',sup.id                 HRI_P_SUP_ID ' || g_rtn ||
',''' || l_lnk_mgr_name || '''
                         HRI_P_DRILL_URL2' || g_rtn ||
/* Transfer from Organization */
',org_prev.value         HRI_P_ORG_CN ' || g_rtn ||
/* Transfer to Organization */
',org.value              HRI_P_CHAR1_GA ' || g_rtn ||
/* Transfer to country */
',ctr.value              HRI_P_GEO_CTY_CN ' || g_rtn ||
/* Transfer to job (using default display configuration) */
',hri_bpl_job.get_job_display_name
        (job.id
        ,job.business_group_id
        ,job.value)      HRI_P_JOB_CN ' || g_rtn ||
/* Person type on hire date */
',null                   HRI_P_CHAR2_GA ' || g_rtn ||
/* Most Recent Hire Date */
', to_char(null)         HRI_P_DATE1_GA ' || g_rtn ||
/* Transfer (In) Date */
',tch.effective_date     HRI_P_DATE2_GA ' || g_rtn ||
/* Event Type - for future use */
',null                   HRI_P_CHAR3_GA ' || g_rtn ||
/* Order by default name sort order */
',peo.order_by           HRI_P_ORDER_BY_1 ';

Line: 612


  x_custom_sql := l_select_clause    || g_rtn
                ||l_from_clause      || g_rtn
                ||l_where_clause     || g_rtn
                ||l_security_clause  || g_rtn
                ||l_orderby_clause;

Line: 632


  l_select_clause      VARCHAR2(4000);

Line: 670


  l_select_clause :=
'SELECT -- headcount transfers out detail' || g_rtn ||
/* View by name of person transferring out */
' peo.value              VIEWBY ' || g_rtn ||
/* Name of person transferred */
',peo.value              HRI_P_PER_LNAME_CN ' || g_rtn ||
',peo.id                 HRI_P_PER_ID ' || g_rtn ||
',''' || l_lnk_emp_name || '''
                         HRI_P_DRILL_URL1' || g_rtn ||
/* Transfer from manager */
',sup.value              HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
',sup.id                 HRI_P_SUP_ID ' || g_rtn ||
',''' || l_lnk_mgr_name || '''
                         HRI_P_DRILL_URL2' || g_rtn ||
/* Transfer from Organization */
',org_prev.value         HRI_P_ORG_CN ' || g_rtn ||
/* Transfer to Organization */
',org.value           HRI_P_CHAR1_GA ' || g_rtn ||
/* Transfer from Country */
',ctr.value              HRI_P_GEO_CTY_CN ' || g_rtn ||
/* Transfer from Job (using default display configuration) */
',hri_bpl_job.get_job_display_name
        (job.id
        ,job.business_group_id
        ,job.value)     HRI_P_JOB_CN ' || g_rtn ||
/* Transfer from Person Type */
', null                  HRI_P_CHAR2_GA  ' || g_rtn ||
/* bug Most recent hire date of transferee */
', to_char(null)        HRI_P_DATE1_GA ' || g_rtn ||
/* Transfer Out Date */
', tch.effective_date    HRI_P_DATE2_GA ' || g_rtn ||
/* Period of work in years
Length of Service is defined as the number of years (in decimal format) between an employee's
most recent hire date up the event date (transfer out).
*/
',ROUND ( ( (asg_from.POW_DAYS_ON_EVENT_DATE +
            (tch.effective_date - asg_from.EFFECTIVE_CHANGE_DATE)
         )
          /365),2)
HRI_P_MEASURE1 '|| g_rtn ||
/* Performance Band */
',prf.value              HRI_P_CHAR3_GA ' || g_rtn ||
/* Event Type - for future use */
',null                   HRI_P_CHAR4_GA ' || g_rtn ||
/* Order by default person name sort order */
',peo.order_by           HRI_P_ORDER_BY_1 ';

Line: 780


  x_custom_sql := l_select_clause    || g_rtn
                ||l_from_clause      || g_rtn
                ||l_where_clause     || g_rtn
                ||l_security_clause  || g_rtn
                ||l_orderby_clause;

Line: 878


'SELECT -- turnover detail'  || g_rtn ||
/* Order by default person name sort order */
' peo.order_by                      HRI_P_ORDER_BY_1' || g_rtn ||
/* View by name of person terminated */
',peo.id                            VIEWBYID' || g_rtn ||
',peo.value                         VIEWBY' || g_rtn ||
/* Name of person terminated  */
',peo.value                         HRI_P_CHAR1_GA' || g_rtn ||
/* Manager of person terminated as of termination date */
',sup.value                         HRI_P_CHAR2_GA' || g_rtn ||
',sup.id                            HRI_P_SUP_ID' || g_rtn ||
',''' || l_lnk_mgr_name || '''      HRI_P_DRILL_URL1' || g_rtn ||
/* Organization person terminated from */
',org.value                         HRI_P_CHAR3_GA' || g_rtn ||
/* Country where person was terminated */
',ctr.value                         HRI_P_CHAR4_GA' || g_rtn ||
/* Job person was terminated from (using default display configuration) */
',hri_oltp_view_job.get_job_display_name
        (job.id
        ,job.business_group_id
        ,job.value)                 HRI_P_CHAR5_GA' || g_rtn ||
/* Most recent hire date of terminated person  */
',fact.pow_start_date               HRI_P_DATE1_GA' || g_rtn ||
/* Termination Date  */
',fact.effective_date - 1           HRI_P_DATE2_GA' || g_rtn ||
/* Termination Reason */
',rsn.value                         HRI_P_CHAR6_GA' || g_rtn ||
/* Period of work in years
Length of Service is defined as the number of years (in decimal format) between an employee's
most recent hire date and the  event_date (termination date).  */
',(fact.effective_date - fact.pow_start_date) / 365
                                    HRI_P_MEASURE1' || g_rtn ||
/* Performance Band */
',prf.value                         HRI_P_MEASURE2
FROM
 hri_mdp_sup_wcnt_term_asg_mv  fact
,hri_cs_geo_lochr_ct           geo
,hri_dbi_cl_geo_country_v      ctr
,hri_dbi_cl_job_n_v            job
,hri_dbi_cl_org_n_v            org
,hri_dbi_cl_per_n_v            sup
,hri_dbi_cl_per_n_v            peo
,hri_cl_prfmnc_rtng_x_v        prf
,hri_cl_rsn_sep_x_v            rsn
WHERE fact.supervisor_person_id = &HRI_PERSON+HRI_PER_USRDR_H
AND fact.separation_hdc > 0
AND fact.person_id = peo.id
AND fact.direct_supervisor_person_id = sup.id
AND fact.organization_id = org.id
AND fact.job_id      = job.id (+)
AND fact.perf_band = prf.id (+)
AND fact.location_id = geo.location_id
AND fact.geo_country_code = ctr.id
AND fact.effective_date - 1 BETWEEN peo.effective_start_date
                            AND peo.effective_end_date
AND fact.effective_date - 1 BETWEEN sup.effective_start_date
                            AND sup.effective_end_date
AND fact.effective_date BETWEEN &BIS_CURRENT_EFFECTIVE_START_DATE
                        AND &BIS_CURRENT_EFFECTIVE_END_DATE
AND fact.leaving_reason_code = rsn.id' || g_rtn ||
 l_fact_conditions ||
'&ORDER_BY_CLAUSE';

Line: 952


  l_select_clause      VARCHAR2(4000);

Line: 992


  l_select_clause :=
'SELECT -- Staff transfers out detail' || g_rtn ||
/* View by name of person transferring out */
' peo.value              VIEWBY ' || g_rtn ||
/* Name of person transferred */
',peo.value              HRI_P_PER_LNAME_CN ' || g_rtn ||
',peo.id                 HRI_P_PER_ID ' || g_rtn ||
',''' || l_lnk_emp_name || ''' HRI_P_DRILL_URL1' || g_rtn ||
/* Transfer from manager */
',sup.value              HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
',sup.id                 HRI_P_SUP_ID ' || g_rtn ||
',''' || l_lnk_mgr_name || '''  HRI_P_DRILL_URL2' || g_rtn ||
/* Transfer To manager */
',supTo.value            HRI_P_PER_SUP_TO_LNAME_CN ' || g_rtn ||
',supTo.id               HRI_P_SUP_TO_ID ' || g_rtn ||
',''' || l_lnk_mgr_to_name || '''  HRI_P_DRILL_URL3' || g_rtn ||
/* Transfer from Organization */
',org_prev.value         HRI_P_ORG_CN' || g_rtn ||
/* Transfer to Organization */
',org.value              HRI_P_CHAR1_GA' || g_rtn ||
/* Transfer from Country */
',ctr.value              HRI_P_GEO_CTY_CN ' || g_rtn ||
/* Transfer from Job (using default display configuration) */
',hri_bpl_job.get_job_display_name
        (job.id
        ,job.business_group_id
        ,job.value)      HRI_P_JOB_CN'   || g_rtn ||
',tch.transfer_out_hdc   HRI_P_MEASURE1' || g_rtn ||
',prsnwtyp.value         HRI_P_CHAR3_GA' || g_rtn ||
',tch.effective_date     HRI_P_DATE2_GA' || g_rtn ||
',peo.order_by           HRI_P_ORDER_BY_1 ';

Line: 1091


  x_custom_sql := l_select_clause    || g_rtn
                ||l_from_clause      || g_rtn
                ||l_where_clause     || g_rtn
                ||l_security_clause  || g_rtn
                ||l_orderby_clause;

Line: 1106


  l_select_clause      VARCHAR2(4000);

Line: 1146


  l_select_clause :=
'SELECT -- Staff transfers out detail' || g_rtn ||
/* View by name of person transferring out */
' peo.value              VIEWBY ' || g_rtn ||
/* Name of person transferred */
',peo.value              HRI_P_PER_LNAME_CN ' || g_rtn ||
',peo.id                 HRI_P_PER_ID ' || g_rtn ||
',''' || l_lnk_emp_name || ''' HRI_P_DRILL_URL1' || g_rtn ||
/* Transfer from manager */
',sup.value              HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
',sup.id                 HRI_P_SUP_ID ' || g_rtn ||
',''' || l_lnk_mgr_name || '''  HRI_P_DRILL_URL2' || g_rtn ||
/* Transfer To manager */
',supTo.value            HRI_P_PER_SUP_TO_LNAME_CN ' || g_rtn ||
',supTo.id               HRI_P_SUP_TO_ID ' || g_rtn ||
',''' || l_lnk_mgr_to_name || '''  HRI_P_DRILL_URL3' || g_rtn ||
/* Transfer from Organization */
',org_prev.value         HRI_P_ORG_CN' || g_rtn ||
/* Transfer to Organization */
',org.value              HRI_P_CHAR1_GA' || g_rtn ||
/* Transfer from Country */
',ctr.value              HRI_P_GEO_CTY_CN ' || g_rtn ||
/* Transfer from Job (using default display configuration) */
',hri_bpl_job.get_job_display_name
        (job.id
        ,job.business_group_id
        ,job.value)      HRI_P_JOB_CN'   || g_rtn ||
',tch.transfer_out_hdc   HRI_P_MEASURE1' || g_rtn ||
',prsnwtyp.value         HRI_P_CHAR3_GA' || g_rtn ||
',asg_to.pow_start_date_adj  HRI_P_DATE1_GA' || g_rtn ||
',tch.effective_date     HRI_P_DATE2_GA' || g_rtn ||
',ROUND ( ( (asg_from.POW_DAYS_ON_EVENT_DATE +
            (tch.effective_date - asg_from.EFFECTIVE_CHANGE_DATE)
         )
          /30.42),2)     HRI_P_MEASURE2' || g_rtn ||
',peo.order_by           HRI_P_ORDER_BY_1 ';

Line: 1239


  x_custom_sql := l_select_clause    || g_rtn
                ||l_from_clause      || g_rtn
                ||l_where_clause     || g_rtn
                ||l_security_clause  || g_rtn
                ||l_orderby_clause;

Line: 1253


  l_select_clause      VARCHAR2(4000);

Line: 1291


  l_select_clause :=
'SELECT -- headcount transfers in detail' || g_rtn ||
/* View by name of person transferred */
' peo.value              VIEWBY ' || g_rtn ||
/* Name of person transferred */
',peo.value              HRI_P_PER_LNAME_CN ' || g_rtn ||
',peo.id                 HRI_P_PER_ID ' || g_rtn ||
',''' || l_lnk_emp_name || '''
                         HRI_P_DRILL_URL1' || g_rtn ||
/* Transfer to Manager */
',sup.value              HRI_P_PER_SUP_LNAME_CN ' || g_rtn ||
',sup.id                 HRI_P_SUP_ID ' || g_rtn ||
',''' || l_lnk_mgr_name || '''
                         HRI_P_DRILL_URL2' || g_rtn ||
/* Transfer from Organization */
',org_prev.value         HRI_P_ORG_CN ' || g_rtn ||
/* Transfer to Organization */
',org.value           HRI_P_CHAR1_GA ' || g_rtn ||
/* Transfer to country */
',ctr.value              HRI_P_GEO_CTY_CN ' || g_rtn ||
/* Transfer to job (using default display configuration) */
',hri_bpl_job.get_job_display_name
        (job.id
        ,job.business_group_id
        ,job.value)      HRI_P_JOB_CN ' || g_rtn ||
/* Person type on hire date */
',null                  HRI_P_CHAR2_GA ' || g_rtn ||
/* Most Recent Hire Date */
', to_char(null)        HRI_P_DATE1_GA ' || g_rtn ||
/* Transfer (In) Date */
',tch.effective_date    HRI_P_DATE2_GA ' || g_rtn ||
/* Event Type - for future use */
',null                   HRI_P_CHAR3_GA ' || g_rtn ||
/* Order by default name sort order */
',peo.order_by           HRI_P_ORDER_BY_1 ';

Line: 1377


  x_custom_sql := l_select_clause    || g_rtn
                ||l_from_clause      || g_rtn
                ||l_where_clause     || g_rtn
                ||l_security_clause  || g_rtn
                ||l_orderby_clause;