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.HR_EDW_WRK_CMPSTN_F_C SQL Statements

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

Line: 41


   Insert /*+ NOPARALLEL */ Into HR_EDW_WRK_CMPSTN_FSTG(
     AGE_BAND_FK,
     ASG_ASSIGNMENT_ID,
     ASG_BUSINESS_GROUP_ID,
     ASG_GRADE_ID,
     ASG_JOB_ID,
     ASG_LOCATION_ID,
     ASG_ORGANIZATION_ID,
     ASG_PERSON_ID,
     ASG_POSITION_ID,
     ASSIGNMENT_FK,
     ASSIGNMENT_START_DATE,
     COMPOSITION_FTE,
     COMPOSITION_HEADCOUNT,
     COMPOSITION_PK,
     CREATION_DATE,
     CRNT_ANNLZED_SLRY,
     CRNT_ANNLZED_SLRY_BC,
     DATE_OF_BIRTH,
     GEOGRAPHY_FK,
     GRADE_FK,
     HGHST_GRD_SLRY,
     INSTANCE_FK,
     JOB_FK,
     LAST_UPDATE_DATE,
     LWST_GRD_SLRY,
     ORGANIZATION_FK,
     PERSON_FK,
     PERSON_TYPE_FK,
     POSITION_FK,
     SERVICE_BAND_FK,
     SNAPSHOT_DATE,
     TIME_FK,
     USER_FK1,
     USER_FK2,
     USER_FK3,
     USER_FK4,
     USER_FK5,
     USER_MEASURE1,
     USER_MEASURE2,
     USER_MEASURE3,
     USER_MEASURE4,
     USER_MEASURE5,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE10,
     USER_ATTRIBUTE11,
     USER_ATTRIBUTE12,
     USER_ATTRIBUTE13,
     USER_ATTRIBUTE14,
     USER_ATTRIBUTE15,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     USER_ATTRIBUTE6,
     USER_ATTRIBUTE7,
     USER_ATTRIBUTE8,
     USER_ATTRIBUTE9,
     OPERATION_CODE,
     COLLECTION_STATUS,
     CRRNCY_CNVRSN_RATE,
     CURRENCY_FK)
   select /*+ PARALLEL (WCP,3) */
     NVL(AGE_BAND_FK,'NA_EDW'),
     ASG_ASSIGNMENT_ID,
     ASG_BUSINESS_GROUP_ID,
     ASG_GRADE_ID,
     ASG_JOB_ID,
     ASG_LOCATION_ID,
     ASG_ORGANIZATION_ID,
     ASG_PERSON_ID,
     ASG_POSITION_ID,
     NVL(ASSIGNMENT_FK,'NA_EDW'),
     ASSIGNMENT_START_DATE,
     COMPOSITION_FTE,
     COMPOSITION_HEADCOUNT,
     COMPOSITION_PK,
     CREATION_DATE,
     CRNT_ANNLZED_SLRY,
     CRNT_ANNLZED_SLRY_BC,
     DATE_OF_BIRTH,
     NVL(GEOGRAPHY_FK,'NA_EDW'),
     NVL(GRADE_FK,'NA_EDW'),
     HGHST_GRD_SLRY,
     NVL(INSTANCE_FK,'NA_EDW'),
     NVL(JOB_FK,'NA_EDW'),
     LAST_UPDATE_DATE,
     LWST_GRD_SLRY,
     NVL(ORGANIZATION_FK,'NA_EDW'),
     NVL(PERSON_FK,'NA_EDW'),
     NVL(PERSON_TYPE_FK,'NA_EDW'),
     NVL(POSITION_FK,'NA_EDW'),
     NVL(SERVICE_BAND_FK,'NA_EDW'),
     SNAPSHOT_DATE,
     NVL(TIME_FK,'NA_EDW'),
     NVL(USER_FK1,'NA_EDW'),
     NVL(USER_FK2,'NA_EDW'),
     NVL(USER_FK3,'NA_EDW'),
     NVL(USER_FK4,'NA_EDW'),
     NVL(USER_FK5,'NA_EDW'),
     USER_MEASURE1,
     USER_MEASURE2,
     USER_MEASURE3,
     USER_MEASURE4,
     USER_MEASURE5,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE10,
     USER_ATTRIBUTE11,
     USER_ATTRIBUTE12,
     USER_ATTRIBUTE13,
     USER_ATTRIBUTE14,
     USER_ATTRIBUTE15,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     USER_ATTRIBUTE6,
     USER_ATTRIBUTE7,
     USER_ATTRIBUTE8,
     USER_ATTRIBUTE9,
     NULL, -- OPERATION_CODE
     DECODE(CRRNCY_CNVRSN_RATE,-1,'RATE_NOT_AVAILABLE',-2,'INVALID_CURRENCY','LOCAL READY'),
     CRRNCY_CNVRSN_RATE,
     NVL(CURRENCY_FK,'NA_EDW')
   from HR_EDW_WRK_CMPSTN_FCV;

Line: 180


   l_rows_inserted    Number:=0;

Line: 192


   Insert /*+ NOPARALLEL */ Into HR_EDW_WRK_CMPSTN_FSTG(
     AGE_BAND_FK,
     ASG_ASSIGNMENT_ID,
     ASG_BUSINESS_GROUP_ID,
     ASG_GRADE_ID,
     ASG_JOB_ID,
     ASG_LOCATION_ID,
     ASG_ORGANIZATION_ID,
     ASG_PERSON_ID,
     ASG_POSITION_ID,
     ASSIGNMENT_FK,
     ASSIGNMENT_START_DATE,
     COMPOSITION_FTE,
     COMPOSITION_HEADCOUNT,
     COMPOSITION_PK,
     CREATION_DATE,
     CRNT_ANNLZED_SLRY,
     CRNT_ANNLZED_SLRY_BC,
     DATE_OF_BIRTH,
     GEOGRAPHY_FK,
     GRADE_FK,
     HGHST_GRD_SLRY,
     INSTANCE_FK,
     JOB_FK,
     LAST_UPDATE_DATE,
     LWST_GRD_SLRY,
     ORGANIZATION_FK,
     PERSON_FK,
     PERSON_TYPE_FK,
     POSITION_FK,
     SERVICE_BAND_FK,
     SNAPSHOT_DATE,
     TIME_FK,
     USER_FK1,
     USER_FK2,
     USER_FK3,
     USER_FK4,
     USER_FK5,
     USER_MEASURE1,
     USER_MEASURE2,
     USER_MEASURE3,
     USER_MEASURE4,
     USER_MEASURE5,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE10,
     USER_ATTRIBUTE11,
     USER_ATTRIBUTE12,
     USER_ATTRIBUTE13,
     USER_ATTRIBUTE14,
     USER_ATTRIBUTE15,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     USER_ATTRIBUTE6,
     USER_ATTRIBUTE7,
     USER_ATTRIBUTE8,
     USER_ATTRIBUTE9,
     OPERATION_CODE,
     COLLECTION_STATUS,
     CRRNCY_CNVRSN_RATE,
     CURRENCY_FK)
   select /*+ PARALLEL (WCP,3) */
     NVL(AGE_BAND_FK,'NA_EDW'),
     ASG_ASSIGNMENT_ID,
     ASG_BUSINESS_GROUP_ID,
     ASG_GRADE_ID,
     ASG_JOB_ID,
     ASG_LOCATION_ID,
     ASG_ORGANIZATION_ID,
     ASG_PERSON_ID,
     ASG_POSITION_ID,
     NVL(ASSIGNMENT_FK,'NA_EDW'),
     ASSIGNMENT_START_DATE,
     COMPOSITION_FTE,
     COMPOSITION_HEADCOUNT,
     COMPOSITION_PK,
     CREATION_DATE,
     CRNT_ANNLZED_SLRY,
     CRNT_ANNLZED_SLRY_BC,
     DATE_OF_BIRTH,
     NVL(GEOGRAPHY_FK,'NA_EDW'),
     NVL(GRADE_FK,'NA_EDW'),
     HGHST_GRD_SLRY,
     NVL(INSTANCE_FK,'NA_EDW'),
     NVL(JOB_FK,'NA_EDW'),
     LAST_UPDATE_DATE,
     LWST_GRD_SLRY,
     NVL(ORGANIZATION_FK,'NA_EDW'),
     NVL(PERSON_FK,'NA_EDW'),
     NVL(PERSON_TYPE_FK,'NA_EDW'),
     NVL(POSITION_FK,'NA_EDW'),
     NVL(SERVICE_BAND_FK,'NA_EDW'),
     SNAPSHOT_DATE,
     NVL(TIME_FK,'NA_EDW'),
     NVL(USER_FK1,'NA_EDW'),
     NVL(USER_FK2,'NA_EDW'),
     NVL(USER_FK3,'NA_EDW'),
     NVL(USER_FK4,'NA_EDW'),
     NVL(USER_FK5,'NA_EDW'),
     USER_MEASURE1,
     USER_MEASURE2,
     USER_MEASURE3,
     USER_MEASURE4,
     USER_MEASURE5,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE10,
     USER_ATTRIBUTE11,
     USER_ATTRIBUTE12,
     USER_ATTRIBUTE13,
     USER_ATTRIBUTE14,
     USER_ATTRIBUTE15,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     USER_ATTRIBUTE6,
     USER_ATTRIBUTE7,
     USER_ATTRIBUTE8,
     USER_ATTRIBUTE9,
     NULL, -- OPERATION_CODE
     DECODE(CRRNCY_CNVRSN_RATE,-1,'RATE_NOT_AVAILABLE',-2,'INVALID_CURRENCY','READY'),
     CRRNCY_CNVRSN_RATE,
     NVL(CURRENCY_FK,'NA_EDW')
   from HR_EDW_WRK_CMPSTN_FCV WCP;

Line: 324


   l_rows_inserted := sql%rowcount;

Line: 327


   edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
     ' rows into the HR_EDW_WRK_CMPSTN_FSTG staging table');

Line: 340


   l_rows_inserted    Number:=0;

Line: 348


    Insert  Into HR_EDW_WRK_CMPSTN_FSTG@EDW_APPS_TO_WH(
     AGE_BAND_FK,
     ASG_ASSIGNMENT_ID,
     ASG_BUSINESS_GROUP_ID,
     ASG_GRADE_ID,
     ASG_JOB_ID,
     ASG_LOCATION_ID,
     ASG_ORGANIZATION_ID,
     ASG_PERSON_ID,
     ASG_POSITION_ID,
     ASSIGNMENT_FK,
     ASSIGNMENT_START_DATE,
     COMPOSITION_FTE,
     COMPOSITION_HEADCOUNT,
     COMPOSITION_PK,
     CREATION_DATE,
     CRNT_ANNLZED_SLRY,
     CRNT_ANNLZED_SLRY_BC,
     DATE_OF_BIRTH,
     GEOGRAPHY_FK,
     GRADE_FK,
     HGHST_GRD_SLRY,
     INSTANCE_FK,
     JOB_FK,
     LAST_UPDATE_DATE,
     LWST_GRD_SLRY,
     ORGANIZATION_FK,
     PERSON_FK,
     PERSON_TYPE_FK,
     POSITION_FK,
     SERVICE_BAND_FK,
     SNAPSHOT_DATE,
     TIME_FK,
     USER_FK1,
     USER_FK2,
     USER_FK3,
     USER_FK4,
     USER_FK5,
     USER_MEASURE1,
     USER_MEASURE2,
     USER_MEASURE3,
     USER_MEASURE4,
     USER_MEASURE5,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE10,
     USER_ATTRIBUTE11,
     USER_ATTRIBUTE12,
     USER_ATTRIBUTE13,
     USER_ATTRIBUTE14,
     USER_ATTRIBUTE15,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     USER_ATTRIBUTE6,
     USER_ATTRIBUTE7,
     USER_ATTRIBUTE8,
     USER_ATTRIBUTE9,
     OPERATION_CODE,
     COLLECTION_STATUS,
     CRRNCY_CNVRSN_RATE,
     CURRENCY_FK)
   select /*+ PARALLEL(WCP) */
     NVL(AGE_BAND_FK,'NA_EDW'),
     ASG_ASSIGNMENT_ID,
     ASG_BUSINESS_GROUP_ID,
     ASG_GRADE_ID,
     ASG_JOB_ID,
     ASG_LOCATION_ID,
     ASG_ORGANIZATION_ID,
     ASG_PERSON_ID,
     ASG_POSITION_ID,
     NVL(ASSIGNMENT_FK,'NA_EDW'),
     ASSIGNMENT_START_DATE,
     COMPOSITION_FTE,
     COMPOSITION_HEADCOUNT,
     COMPOSITION_PK,
     CREATION_DATE,
     CRNT_ANNLZED_SLRY,
     CRNT_ANNLZED_SLRY_BC,
     DATE_OF_BIRTH,
     NVL(GEOGRAPHY_FK,'NA_EDW'),
     NVL(GRADE_FK,'NA_EDW'),
     HGHST_GRD_SLRY,
     NVL(INSTANCE_FK,'NA_EDW'),
     NVL(JOB_FK,'NA_EDW'),
     LAST_UPDATE_DATE,
     LWST_GRD_SLRY,
     NVL(ORGANIZATION_FK,'NA_EDW'),
     NVL(PERSON_FK,'NA_EDW'),
     NVL(PERSON_TYPE_FK,'NA_EDW'),
     NVL(POSITION_FK,'NA_EDW'),
     NVL(SERVICE_BAND_FK,'NA_EDW'),
     SNAPSHOT_DATE,
     NVL(TIME_FK,'NA_EDW'),
     NVL(USER_FK1,'NA_EDW'),
     NVL(USER_FK2,'NA_EDW'),
     NVL(USER_FK3,'NA_EDW'),
     NVL(USER_FK4,'NA_EDW'),
     NVL(USER_FK5,'NA_EDW'),
     USER_MEASURE1,
     USER_MEASURE2,
     USER_MEASURE3,
     USER_MEASURE4,
     USER_MEASURE5,
     USER_ATTRIBUTE1,
     USER_ATTRIBUTE10,
     USER_ATTRIBUTE11,
     USER_ATTRIBUTE12,
     USER_ATTRIBUTE13,
     USER_ATTRIBUTE14,
     USER_ATTRIBUTE15,
     USER_ATTRIBUTE2,
     USER_ATTRIBUTE3,
     USER_ATTRIBUTE4,
     USER_ATTRIBUTE5,
     USER_ATTRIBUTE6,
     USER_ATTRIBUTE7,
     USER_ATTRIBUTE8,
     USER_ATTRIBUTE9,
     NULL, -- OPERATION_CODE
     'READY',
     CRRNCY_CNVRSN_RATE,
     NVL(CURRENCY_FK,'NA_EDW')
   from HR_EDW_WRK_CMPSTN_FSTG WCP
   where COLLECTION_STATUS = 'LOCAL READY';

Line: 477


   l_rows_inserted := sql%rowcount;

Line: 480


   edw_log.put_line('Inserted '||to_char(nvl(sql%rowcount,0))||
     ' rows into the HR_EDW_WRK_CMPSTN_FSTG staging table');

Line: 511


       DELETE FROM hri_edw_daily_salary_details;

Line: 514


   INSERT INTO hri_edw_daily_salary_details
   (salary,
    salary_currency_code,
    assignment_id)
   select  s.proposed_salary_n*
           nvl(ppb.pay_annualization_factor,
               tpt.number_per_fiscal_year)    salary
   ,         pet.input_currency_code          salary_currency_code
   ,     a.assignment_id
   from    pay_element_types_f pet
   ,       pay_input_values_f piv
   ,       per_pay_bases ppb
   ,       per_time_period_types tpt
   ,       pay_all_payrolls_f prl
   ,       per_assignments_f a
   ,       per_pay_proposals_v2 s
   ,       hri_edw_cmpstn_snpsht_dts snp
   where  a.assignment_type = 'E'
   and    snp.snapshot_date between a.effective_start_date
                           and     a.effective_end_date
   and    s.change_date IN (select max(ppp2.change_date)
                            from per_pay_proposals_v2 ppp2
                            where ppp2.change_date   < snp.snapshot_date
                            and   ppp2.assignment_id = a.assignment_id)
   and    a.pay_basis_id = ppb.pay_basis_id
   and    ppb.input_value_id = piv.input_value_id
   and    s.change_date between
             prl.effective_start_date and prl.effective_end_date
   and    a.payroll_id=prl.payroll_id
   and    prl.period_type=tpt.period_type
   and    snp.snapshot_date between
              piv.effective_start_date and piv.effective_end_date
   and    piv.element_type_id = pet.element_type_id
   and    snp.snapshot_date between
              pet.effective_start_date and pet.effective_end_date
   and    a.assignment_id = s.assignment_id
   and   s.approved = 'Y';

Line: 584


  SELECT
   DECODE(a.collection_status,
            'INVALID_CURRENCY','Invalid currency    ',
         'Rate not available  ')       collection_status
  ,to_char(a.snapshot_date,'DD Mon YYYY  ')
                                     snapshot_date
  ,rpad(b.name,20)                   currency_name
  ,b.currency_code                   currency_code
  ,count(*)                          total
  FROM hr_edw_wrk_cmpstn_fstg a, fnd_currencies_vl b
  WHERE a.currency_fk = b.currency_code (+)
  AND a.collection_status IN ('INVALID_CURRENCY','RATE_NOT_AVAILABLE')
  GROUP BY a.collection_status, a.snapshot_date, b.name, b.currency_code
  ORDER BY 1,2,3;

Line: 602


   select count(*) into l_no_missing_rates
   from hr_edw_wrk_cmpstn_fstg
   where collection_status IN ('INVALID_CURRENCY','RATE_NOT_AVAILABLE');

Line: 685


    /* Initialize loop - l_snapshot_date holds next date to insert */
    /*                   l_counter holds number of dates inserted  */
    l_snapshot_date := l_date1;

Line: 694


    DELETE FROM hri_edw_cmpstn_snpsht_dts;

Line: 697


    INSERT INTO hri_edw_cmpstn_snpsht_dts
    (snapshot_date)
    VALUES
    (l_snapshot_date);

Line: 709


        UPDATE hri_edw_cmpstn_snpsht_dts
        SET snapshot_date = l_snapshot_date;

Line: 732


        DELETE FROM hr_edw_wrk_cmpstn_fstg@apps_to_apps
        WHERE collection_status = 'LOCAL READY';

Line: 760


    DELETE FROM HR_EDW_WRK_CMPSTN_FSTG
    WHERE collection_status IN ('RATE_NOT_AVAILABLE','INVALID_CURRENCY');