APPS.HRI_OLTP_VIEW_WRKFRC_ORGH SQL Statements

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

Line: 11

  SELECT NVL(SUM(wrkfc.headcount_end), 0)
  FROM hri_mds_wrkfc_orgh_c01_ct   wrkfc
  WHERE wrkfc.sup_organztn_fk = p_sup_organization_id
  AND wrkfc.time_month_snp_fk = to_number(to_char(p_effective_date, 'YYYYQMM'))
  AND wrkfc.ptyp_wrktyp_fk = p_worker_type
  AND wrkfc.sup_directs_only_flag = p_directs_only;

Line: 37

  SELECT NVL(SUM(wrkfc.fte_end), 0)
  FROM hri_mds_wrkfc_orgh_c01_ct   wrkfc
  WHERE wrkfc.sup_organztn_fk = p_sup_organization_id
  AND wrkfc.time_month_snp_fk = to_char(p_effective_date, 'YYYYQMM')
  AND wrkfc.ptyp_wrktyp_fk = p_worker_type
  AND wrkfc.sup_directs_only_flag = p_directs_only;

Line: 63

  SELECT NVL(SUM(wrkfc.count_pasg_end), 0)
  FROM hri_mds_wrkfc_orgh_c01_ct   wrkfc
  WHERE wrkfc.sup_organztn_fk = p_sup_organization_id
  AND wrkfc.time_month_snp_fk = to_char(p_effective_date, 'YYYYQMM')
  AND wrkfc.ptyp_wrktyp_fk = p_worker_type
  AND wrkfc.sup_directs_only_flag = p_directs_only;

Line: 89

  SELECT NVL(SUM(wrkfc.count_asg_end), 0)
  FROM hri_mds_wrkfc_orgh_c01_ct   wrkfc
  WHERE wrkfc.sup_organztn_fk = p_sup_organization_id
  AND wrkfc.time_month_snp_fk = to_char(p_effective_date, 'YYYYQMM')
  AND wrkfc.ptyp_wrktyp_fk = p_worker_type
  AND wrkfc.sup_directs_only_flag = p_directs_only;

Line: 118

  SELECT
    NVL(SUM(wevt.headcount * trn.transfer_in_ind), 0)     hdc_trn_in
   ,NVL(SUM(wevt.headcount * trn.transfer_out_ind), 0)    hdc_trn_out
   ,NVL(SUM(wevt.fte * trn.transfer_in_ind), 0)           fte_trn_in
   ,NVL(SUM(wevt.fte * trn.transfer_out_ind), 0)          fte_trn_out
   ,NVL(SUM(wevt.primary_ind * trn.transfer_in_ind), 0)   pasg_cnt_trn_in
   ,NVL(SUM(wevt.primary_ind * trn.transfer_out_ind), 0)  pasg_cnt_trn_out
   ,NVL(SUM(trn.transfer_in_ind), 0)                      asg_cnt_trn_in
   ,NVL(SUM(trn.transfer_out_ind), 0)                     asg_cnt_trn_out
  FROM
   hri_mdp_orgh_transfers_ct trn
  ,hri_mb_wrkfc_evt_ct       wevt
  WHERE trn.org_sup_organztn_fk = p_sup_organization_id
  AND trn.ptyp_wrktyp_fk = p_worker_type
  AND trn.asg_assgnmnt_fk = wevt.asg_assgnmnt_fk
  AND trn.time_day_evt_fk BETWEEN wevt.time_day_evt_fk
                          AND wevt.time_day_evt_end_fk
  AND (p_directs_only = 'N' OR trn.direct_ind = 1)
  AND trn.time_day_evt_fk BETWEEN p_from_date
                          AND p_to_date;