/* $Id$ */
/*============================================================================*
 |                 Copyright (c) 2016 Applaud Solutions UK Ltd                |
 |                             All rights reserved                            |
 *===========================================================================*/

SET VERIFY OFF
SET FEEDBACK OFF
SET SCAN OFF
WHENEVER OSERROR  EXIT FAILURE ROLLBACK
WHENEVER SQLERROR EXIT FAILURE ROLLBACK

DECLARE

  --
  -- Local constants and variables
  --
  l_SERVICE CONSTANT xxas_com_service_repository.service_name%TYPE
                       := 'NewHire';

BEGIN

  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'New hire'
    ,p_path               => '/newhire'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml');

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetGradenamebasedongradeladder'
    ,p_description        => 'Get Grade Name based on grade ladder'
    ,p_methods            => 'GET'
    ,p_path               => 'gradeName'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("grade"
                     ,XMLForest
                       (qrslt.grade_id   "id"
                       ,qrslt.name "value"
                       ,qrslt.name "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT
				grd.name,
				grd.grade_id,
				vgj.date_from,
				vgj.date_to,
				grd.business_group_id,
				job.job_id,
				lkp.meaning valid_for_job
			FROM
				hr_leg_lookups lkp,
				per_valid_grades vgj,
				per_grades_vl grd,
				per_jobs_v job
			WHERE
				grd.grade_id = vgj.grade_id
				AND vgj.date_from BETWEEN grd.date_from AND nvl(grd.date_to,vgj.date_from + 1)
				AND lkp.lookup_type = 'YES_NO'
				AND lkp.lookup_code = 'Y'
				AND vgj.job_id = job.job_id
				AND job.date_from &lt; nvl(grd.date_to,job.date_from + 1)
				AND nvl(job.date_to,grd.date_from + 1) &gt; grd.date_from
			UNION
			SELECT
				grd.name,
				grd.grade_id,
				grd.date_from,
				grd.date_to,
				grd.business_group_id,
				job.job_id,
				lkp.meaning valid_for_job
			FROM
				hr_leg_lookups lkp,
				per_grades_vl grd,
				per_jobs_v job
			WHERE
			    lkp.lookup_type = 'YES_NO'
				AND lkp.lookup_code = 'N'
				AND job.date_from &lt; nvl(grd.date_to,job.date_from + 1)
				AND nvl(job.date_to,grd.date_from + 1) &gt; grd.date_from
				AND grd.business_group_id = job.business_group_id
				AND NOT EXISTS (
					SELECT
						NULL
					FROM
						per_valid_grades vgj
					WHERE
						job.job_id = vgj.job_id
						AND grd.grade_id = vgj.grade_id
						AND vgj.date_from BETWEEN grd.date_from AND nvl(grd.date_to,vgj.date_from + 1)
				)
		) qrslt
	WHERE
		(  job_id = {requestParam.jobId}
		   AND 
			( trunc(sysdate) between date_from and NVL(date_to,trunc(sysdate)) )
				AND grade_id IN (
						SELECT
							pl.mapping_table_pk_id
						FROM
							ben_plip_f plip,
							ben_pl_f pl
						WHERE
							plip.pgm_id = {requestParam.gradeLadderId}
							AND plip.business_group_id =  {requestParam.bgId}
							AND plip.plip_stat_cd = 'A'
							AND (trunc(sysdate) between plip.effective_start_date and NVL(plip.effective_end_date,trunc(sysdate)))
							AND pl.pl_id = plip.pl_id
							AND pl.mapping_table_name = 'PER_GRADES'
							AND pl.business_group_id = {requestParam.bgId}
							AND pl.pl_stat_cd = 'A'
							AND ( trunc(sysdate) BETWEEN pl.effective_start_date AND nvl(pl.effective_end_date,trunc(sysdate) ) )
			)
		)
	ORDER BY
		name
        ]'
    );


   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetpersonTypebasedonBG'
    ,p_description        => 'Get Person Type based on Business Group'
    ,p_methods            => 'GET'
    ,p_path               => 'personType'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("personType"
                     ,XMLForest
                       (ppt.person_type_id      "id"
                       ,ppt.person_type_id  "value"
                       ,ppttl.user_person_type  "label")
                     )
                   )
                 ) "data"
        FROM     per_person_types ppt, per_person_types_tl ppttl
		WHERE    ppt.person_type_id = ppttl.person_type_id
		AND      ppt.business_group_id = {requestParam.bgId}
		AND      ppt.system_person_type = 'EMP' 
		AND      ppt.active_flag = 'Y'
		AND      ppttl.language = userenv('LANG')
		ORDER BY ppttl.user_person_type
        ]'
    );
	
	xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetOrgIDbasedonBG'
    ,p_description        => 'Get Organization ID based on Business Group'
    ,p_methods            => 'GET'
    ,p_path               => 'organization'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("organization"
                     ,XMLForest
                       (p.organization_id "id"
                       ,p.org_name        "value"
                       ,p.org_name        "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT hou.organization_id
                     ,hout.name org_name
               FROM   hr_all_organization_units    hou
                     ,hr_all_organization_units_tl hout
                     ,hr_organization_information  hoi
               WHERE  hou.business_group_id = {requestParam.bgId}
               AND    hou.organization_id   = hout.organization_id
               AND    hout.language         = userenv('LANG')
               AND    trunc(sysdate) BETWEEN hou.date_from 
                                     AND     nvl(hou.date_to,trunc(sysdate))
               AND    hou.organization_id   = hoi.organization_id
               AND    hoi.org_information_context||''='CLASS' 
               AND    hou.type in ('BG','DPT')
               AND    hoi.org_information1  in ('HR_ORG','HR_BG') 
               AND    hoi.org_information2  = 'Y') p
        ]'
    );

    xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetPayBasisbasedonBG'
    ,p_description        => 'Get Pay Basis ID based on Business Group'
    ,p_methods            => 'GET'
    ,p_path               => 'payBasis'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("payBasis"
                     ,XMLForest
                       (pay_basis_id "id"
                       ,name        "value"
                       ,name      "label"
                      )
                     )
                   )
                 ) "data"
        FROM  (SELECT ppb.pay_basis_id
                     ,ppb.name
               FROM   per_pay_bases ppb
               WHERE  business_group_id = {requestParam.bgId}) 
        ]'
    );

	 xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetPayBasis'
    ,p_description        => 'Get Pay Basis ID '
    ,p_methods            => 'GET'
    ,p_path               => 'payBasisId'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("payBasisId"
                     ,XMLForest
                       (pay_basis_id "id"
                       ,name        "value"
                       ,name      "label"
                      )
                     )
                   )
                 ) "data"
        FROM  (SELECT ppb.pay_basis_id
                     ,ppb.name
               FROM   per_pay_bases ppb) 
        ]'
    );

   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetLocation'
    ,p_description        => 'Get Location'
    ,p_methods            => 'GET'
    ,p_path               => 'location'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("location"
                     ,XMLForest
                       (location_id    "id"
                       ,location_code  "value"
                       ,location_code  "label"
                      )
                     )
                   )
                 ) "data"
        FROM  (SELECT loc.location_id
                     ,loc.location_code
               from hr_all_organization_units org,
               hr_locations_all loc
               where org.organization_id = {requestParam.orgId}
               and org.location_id = loc.location_id) 
        ]'
    );

   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetJob'
    ,p_description        => 'Get Job'
    ,p_methods            => 'GET'
    ,p_path               => 'job'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("job"
                     ,XMLForest
                       (job_id  "id"
                       ,name    "value"
                       ,name    "label"
                      )
                     )
                   )
                 ) "data"
        FROM  (SELECT pj.job_id
                     ,pj.name
               FROM   per_jobs pj, fnd_sessions s
               WHERE  pj.business_group_id = {requestParam.bgId}
                      AND SESSION_ID = USERENV('SESSIONID')
                      and s.effective_date between pj.date_from and nvl(pj.date_to,to_date('31/12/4712','DD/MM/YYYY'))) 
        ]'
    );

   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetAltBGSupervisor'
    ,p_description        => 'Get Alternatice Businnes Group Supervisor'
    ,p_methods            => 'GET'
    ,p_path               => 'AltBGSupervisor'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("AltBGSupervisor"
                     ,XMLForest
                       (person_id    "id"
                       ,full_name    "value"
                       ,full_name    "label"
                      )
                     )
                   )
                 ) "data"
        FROM  (SELECT ppf.person_id
                     ,ppf.full_name||' (Person ID='||ppf.person_id||')' full_name
               FROM   per_people_f ppf, per_person_types ppt
               WHERE  ppf.business_group_id +0 = {requestParam.bgId}
                      AND	ppt.person_type_id	= ppf.person_type_id +0
                      AND	ppt.system_person_type in ('EMP','EMP_APL','EX_EMP','EX_EMP_APL')
                      AND	ppf.effective_start_date =
  	                                    (select max(effective_start_date)
  	                                     from	per_people_f	ppf1
  	                                     where	ppf1.person_id	= ppf.person_id)
              ORDER BY nvl(ppf.order_name, ppf.full_name)) 
        ]'
    );

   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetPosition'
    ,p_description        => 'Get Position'
    ,p_methods            => 'GET'
    ,p_path               => 'position'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("position"
                     ,XMLForest
                       (position_id    "id"
                       ,position_name    "value"
                       ,position_name    "label"
                      )
                     )
                   )
                 ) "data"
        FROM  (SELECT hap.position_id
                     ,hapt.name position_name
               FROM   hr_all_positions_f    hap
                     ,hr_all_positions_f_tl hapt
               WHERE  hap.position_id       = hapt.position_id
               AND    hapt.language         = userenv('LANG')
               AND    hap.business_group_id = {requestParam.bgId}
               AND    ({requestParam.orgId} = -1 OR hap.organization_id = {requestParam.orgId})
               AND    trunc(sysdate) BETWEEN hap.effective_start_date 
                                     AND     nvl(hap.effective_end_date,trunc(sysdate))
               AND    ({requestParam.jobId} = -1 OR (hap.position_id IN (SELECT hap.position_id
                                                                         FROM   hr_all_positions_f    hap
                                                                         WHERE  hap.business_group_id = {requestParam.bgId}
                                                                         AND    ({requestParam.orgId} = -1 OR hap.organization_id = {requestParam.orgId})
                                                                         AND    hap.job_id            = {requestParam.jobId}
                                                                         AND    trunc(sysdate) BETWEEN hap.effective_start_date 
                                                                                               AND     nvl(hap.effective_end_date,trunc(sysdate)))))) 
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GradeLadder'
    ,p_description        => 'Get Grade Ladder Based on BG'
    ,p_methods            => 'GET'
    ,p_path               => 'gradeladder'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("gradeladder"
                     ,XMLForest
                       (p.pgm_id   "id"
                       ,p.pgm_name "value"
                       ,p.pgm_name "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT bpg.pgm_id
                     ,bpg.name pgm_name
               FROM   ben_pgm_f  bpg
                     ,ben_plip_f bplp 
                     ,per_grades pg
                     ,ben_pl_f   bpl
               WHERE  bpg.business_group_id   = {requestParam.bgId}
               AND    trunc(sysdate) BETWEEN bpg.effective_start_date 
                                     AND     nvl(bpg.effective_end_date,trunc(sysdate))
               AND    bpg.pgm_typ_cd          = 'GSP'
               AND    bpg.pgm_stat_cd         = 'A'
               AND    bpg.pgm_id              = bplp.pgm_id
               AND    bplp.business_group_id  = {requestParam.bgId}
               AND    bplp.plip_stat_cd       = 'A'
               AND    trunc(sysdate) BETWEEN bplp.effective_start_date 
                                     AND     nvl(bplp.effective_end_date,trunc(sysdate))
               AND    bplp.pl_id              = bpl.pl_id
               AND    bpl.mapping_table_name  = 'PER_GRADES'
               AND    bpl.mapping_table_pk_id = pg.grade_id
               AND    pg.business_group_id    = {requestParam.bgId}
               AND    trunc(sysdate) BETWEEN pg.date_from 
                                     AND     nvl(pg.date_to,trunc(sysdate))
               AND    bpl.business_group_id   = {requestParam.bgId}
               AND    bpl.pl_stat_cd          = 'A'
               AND    trunc(sysdate) BETWEEN bpl.effective_start_date 
                                     AND     nvl(bpl.effective_end_date,trunc(sysdate))) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'SpecialCeiling'
    ,p_description        => 'Get Special Ceiling Steps'
    ,p_methods            => 'GET'
    ,p_path               => 'specialceiling'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("specialceiling"
                     ,XMLForest
                       (p.step_id      "id"
                       ,p.spinal_point "value"
                       ,p.spinal_point "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT psps.step_id
                     ,psp.spinal_point
               FROM   per_grades               pg
                     ,per_grade_spines_f       pgs
                     ,per_parent_spines        pps
                     ,per_spinal_points        psp
                     ,per_spinal_point_steps_f psps
               WHERE  pg.grade_id            = {requestParam.gradeId}
               AND    pg.business_group_id   = {requestParam.bgId}
               AND    trunc(sysdate) BETWEEN pg.date_from 
                                     AND     nvl(pg.date_to,trunc(sysdate))
               AND    pg.grade_id            = pgs.grade_id
               AND    pgs.business_group_id  = {requestParam.bgId}
               AND    trunc(sysdate) BETWEEN pgs.effective_start_date 
                                     AND     nvl(pgs.effective_end_date,trunc(sysdate))
               AND    pgs.parent_spine_id    = pps.parent_spine_id
               AND    pps.business_group_id  = {requestParam.bgId}
               AND    pps.parent_spine_id    = psp.parent_spine_id
               AND    psp.business_group_id  = {requestParam.bgId}
               AND    psp.spinal_point_id    = psps.spinal_point_id
               AND    psps.business_group_id = {requestParam.bgId}
               AND    trunc(sysdate) BETWEEN psps.effective_start_date 
                                     AND     nvl(psps.effective_end_date,trunc(sysdate))
               AND    psps.grade_spine_id    = pgs.grade_spine_id
               ) p
        ]'
    );

   xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GetGradeLadderName'
    ,p_description        => 'Get Grade Ladder Name'
    ,p_methods            => 'GET'
    ,p_path               => 'gradeLadderName'
    ,p_remove_root_ele    => true
    ,p_sql                =>
      q'[
        SELECT XMLElement
                 ("data"
                 ,XMLAttributes
                   ('true' as "json:force-array"
                   ,'http://json.org/' as "xmlns:json")
                 ,XMLAgg
                   (XMLElement
                     ("gradeLadderName"
                     ,XMLForest
                       (grade_id    "id"
                       ,grade_name  "value"
                       ,grade_name  "label"
                      )
                     )
                   )
                 ) "data"
        FROM  (SELECT pg.grade_id grade_id
                     ,bpg.name grade_name
               FROM   ben_pgm_f  bpg
                     ,ben_plip_f bplp 
                     ,per_grades pg
                     ,ben_pl_f   bpl
               WHERE  bpg.pgm_id              = bplp.pgm_id
               AND    bplp.pl_id              = bpl.pl_id
               AND    bpl.mapping_table_pk_id = pg.grade_id) 
        ]'
    );

END;
/
COMMIT;
EXIT;
