/* $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
                       := 'AsgDefFieldSearch';

BEGIN

  xxas_com_sr_pkg.set_rest_service
    (p_service_name       => l_SERVICE
    ,p_description        => 'Assignment Definition Field Search'
    ,p_path               => '/asgdef-fieldsearch'
    ,p_consumes           => 'application/xml'
    ,p_produces           => 'application/xml');

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'Organization'
    ,p_description        => 'Find organization matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'bg/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  {requestParam.q} is not null
               AND    UPPER(hout.name) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    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    hoi.org_information1  = 'HR_ORG' 
               AND    hoi.org_information2  = 'Y'
               UNION
               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  {requestParam.id} is not null
               AND    hou.organization_id   = {requestParam.id}
               AND    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    hoi.org_information1  = 'HR_ORG' 
               AND    hoi.org_information2  = 'Y') p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'Job'
    ,p_description        => 'Find job matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'bg/org/pos/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
                       (p.job_id   "id"
                       ,p.job_name "value"
                       ,p.job_name "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT pj.job_id
                     ,pjt.name job_name
               FROM   per_jobs    pj
                     ,per_jobs_tl pjt
               WHERE  {requestParam.q} is not null
               AND    UPPER(pjt.name) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    pj.business_group_id = {requestParam.bgId}
               AND    pj.job_id            = pjt.job_id
               AND    pjt.language         = userenv('LANG')
               AND    trunc(sysdate) BETWEEN pj.date_from 
                                     AND     nvl(pj.date_to,trunc(sysdate))
               AND    ({requestParam.posId} = -1 OR (pj.job_id IN (SELECT hap.job_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.position_id       = {requestParam.posId}                                                                 
                                                                      AND    trunc(sysdate) BETWEEN hap.effective_start_date 
                                                                                            AND     nvl(hap.effective_end_date,trunc(sysdate)))))
               UNION
               SELECT pj.job_id
                     ,pjt.name job_name
               FROM   per_jobs    pj
                     ,per_jobs_tl pjt
               WHERE  {requestParam.id} is not null
               AND    pj.job_id            = pjt.job_id
               AND    pjt.language         = userenv('LANG')
               AND    pj.job_id            = {requestParam.id}
               AND    pj.business_group_id = {requestParam.bgId}
               AND    trunc(sysdate) BETWEEN pj.date_from 
                                     AND     nvl(pj.date_to,trunc(sysdate))
               AND    ({requestParam.posId} = -1 OR (pj.job_id IN (SELECT hap.job_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.position_id       = {requestParam.posId}
                                                                      AND    trunc(sysdate) BETWEEN hap.effective_start_date 
                                                                                            AND     nvl(hap.effective_end_date,trunc(sysdate)))))) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'Position'
    ,p_description        => 'Find position matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'bg/org/job/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
                       (p.position_id   "id"
                       ,p.position_name "value"
                       ,p.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  {requestParam.q} is not null
               AND    UPPER(hapt.name) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    hap.business_group_id = {requestParam.bgId}
               AND    ({requestParam.orgId} = -1 OR hap.organization_id = {requestParam.orgId})
               AND    hap.position_id       = hapt.position_id
               AND    hapt.language         = userenv('LANG')
               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)))))
               UNION
               SELECT hap.position_id
                     ,hapt.name position_name
               FROM   hr_all_positions_f    hap
                     ,hr_all_positions_f_tl hapt
               WHERE  {requestParam.id} is not null
               AND    hap.position_id       = hapt.position_id
               AND    hapt.language         = userenv('LANG')
               AND    hap.position_id       = {requestParam.id}
               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)))))) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'Location'
    ,p_description        => 'Find location matching the given criteria including function predicate'
    ,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
                       (p.location_id "id"
                       ,p.loc_name    "value"
                       ,p.loc_name    "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT hl.location_id
                     ,hlt.location_code loc_name
               FROM   hr_locations_all    hl
                     ,hr_locations_all_tl hlt
               WHERE  {requestParam.q} is not null
               AND    UPPER(hlt.location_code) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    hl.location_id       = hlt.location_id
               AND    hlt.language         = userenv('LANG')
               AND    ({requestParam.bgId} IS NULL 
                        OR
                       hl.business_group_id = {requestParam.bgId}  
                      )
               AND nvl(hl.inactive_date, trunc(sysdate))  >= trunc(sysdate)
               UNION
               SELECT hl.location_id
                     ,hlt.location_code loc_name
               FROM   hr_locations_all    hl
                     ,hr_locations_all_tl hlt
               WHERE  {requestParam.id} is not null
               AND    hl.location_id       = {requestParam.id}
               
               AND    hl.location_id       = hlt.location_id
               AND    hlt.language         = userenv('LANG')
               AND    ({requestParam.bgId} IS NULL 
                        OR
                       hl.business_group_id = {requestParam.bgId}  
                      )
			   ) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'Grade'
    ,p_description        => 'Find grade matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'bg/position/job/grade'
    ,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
                       (p.grade_id   "id"
                       ,p.grade_name "value"
                       ,p.grade_name "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT pg.grade_id
                     ,pgt.name grade_name
               FROM   per_grades       pg
                     ,per_grades_tl    pgt
               WHERE  {requestParam.q} is not null
               AND    UPPER(pgt.name) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    pg.business_group_id  = {requestParam.bgId}
               AND    pg.grade_id           = pgt.grade_id
               AND    pgt.language          = userenv('LANG')
               AND    trunc(sysdate) BETWEEN pg.date_from 
                                     AND     nvl(pg.date_to,trunc(sysdate))
               UNION
               SELECT pg.grade_id
                     ,pgt.name grade_name
               FROM   per_grades       pg
                     ,per_grades_tl    pgt
               WHERE  {requestParam.id} is not null
               AND    pg.grade_id          = {requestParam.id}
               AND    pg.business_group_id = {requestParam.bgId}
               AND    pg.grade_id          = pgt.grade_id
               AND    pgt.language         = userenv('LANG')
               AND    trunc(sysdate) BETWEEN pg.date_from 
                                     AND     nvl(pg.date_to,trunc(sysdate))) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'Payroll'
    ,p_description        => 'Find payroll matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'bg/payroll'
    ,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
                     ("payroll"
                     ,XMLForest
                       (p.payroll_id   "id"
                       ,p.payroll_name "value"
                       ,p.payroll_name "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT pap.payroll_id
                     ,pap.payroll_name
               FROM   pay_all_payrolls_f pap
               WHERE  {requestParam.q} is not null
               AND    UPPER(pap.payroll_name) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    pap.business_group_id = {requestParam.bgId}
               AND    trunc(sysdate) BETWEEN pap.effective_start_date 
                                     AND     nvl(pap.effective_end_date,trunc(sysdate))
               UNION
               SELECT pap.payroll_id
                     ,pap.payroll_name
               FROM   pay_all_payrolls_f pap
               WHERE  {requestParam.id} is not null
               AND    pap.payroll_id        = {requestParam.id}
               AND    pap.business_group_id = {requestParam.bgId}
               AND    trunc(sysdate) BETWEEN pap.effective_start_date 
                                     AND     nvl(pap.effective_end_date,trunc(sysdate))) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'PayBasis'
    ,p_description        => 'Find paybasis matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'bg/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
                       (p.pay_basis_id   "id"
                       ,p.pay_basis_name "value"
                       ,p.pay_basis_name "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT ppb.pay_basis_id
                     ,ppb.name pay_basis_name
               FROM   per_pay_bases      ppb
                     ,pay_input_values_f piv
               WHERE  {requestParam.q} is not null
               AND    UPPER(ppb.name) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    ppb.business_group_id = {requestParam.bgId}
               AND    ppb.input_value_id    = piv.input_value_id
               AND    trunc(sysdate) BETWEEN piv.effective_start_date 
                                     AND     piv.effective_end_date
               UNION
               SELECT ppb.pay_basis_id
                     ,ppb.name pay_basis_name
               FROM   per_pay_bases      ppb
                     ,pay_input_values_f piv
               WHERE  {requestParam.id} is not null
               AND    ppb.pay_basis_id      = {requestParam.id}
               AND    ppb.business_group_id = {requestParam.bgId}
               AND    ppb.input_value_id    = piv.input_value_id
               AND    trunc(sysdate) BETWEEN piv.effective_start_date 
                                     AND     piv.effective_end_date) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'GradeLadder'
    ,p_description        => 'Find gradeladder matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'bg/grade/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  {requestParam.q} is not null
               AND    UPPER(bpg.name) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    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    ({requestParam.gradeId} = -1 OR 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    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))
               UNION
               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  {requestParam.id} is not null
               AND    bpg.pgm_id              = {requestParam.id}
               AND    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    ({requestParam.gradeId} = -1 OR 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    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        => 'ColAgreement'
    ,p_description        => 'Find Collective Agreement matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'bg/colagreement'
    ,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
                     ("colagreement"
                     ,XMLForest
                       (p.collective_agreement_id   "id"
                       ,p.cag_name "value"
                       ,p.cag_name "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT pca.collective_agreement_id
                     ,pca.name cag_name
               FROM   per_collective_agreements pca
               WHERE  {requestParam.q} is not null
               AND    UPPER(pca.name) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    pca.business_group_id = {requestParam.bgId}
               AND    pca.status NOT IN('I')
               AND    trunc(sysdate) BETWEEN pca.start_date 
                                     AND     nvl(pca.end_date,trunc(sysdate))
               UNION
               SELECT pca.collective_agreement_id
                     ,pca.name cag_name
               FROM   per_collective_agreements pca
               WHERE  {requestParam.id} is not null
               AND    pca.collective_agreement_id = {requestParam.id}
               AND    pca.business_group_id       = {requestParam.bgId}
               AND    pca.status NOT IN('I')
               AND    trunc(sysdate) BETWEEN pca.start_date 
                                     AND     nvl(pca.end_date,trunc(sysdate))) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'SpecialCeiling'
    ,p_description        => 'Find Special Ceiling Steps matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'bg/grade/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  {requestParam.q} is not null
               AND    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
               AND    UPPER(psp.spinal_point) LIKE UPPER('%' || {requestParam.q} || '%')
               UNION
               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  {requestParam.id} is not null
               AND    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
               AND    psps.step_id           = {requestParam.id}) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'Contract'
    ,p_description        => 'Find contract matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'bg/person/contract'
    ,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
                     ("contract"
                     ,XMLForest
                       (p.contract_id "id"
                       ,p.reference   "value"
                       ,p.reference   "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT pc.contract_id
                     ,pc.reference
               FROM   per_contracts_f       pc
                     ,per_all_assignments_f paaf
               WHERE  {requestParam.q} is not null
               AND    UPPER(pc.reference) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    pc.business_group_id = {requestParam.bgId}
               AND    pc.person_id         = {requestParam.personId}
               AND    paaf.person_id       = pc.person_id
               AND    paaf.primary_flag    = 'Y'
               AND    trunc(sysdate) BETWEEN pc.effective_start_date 
                                     AND     nvl(pc.effective_end_date,trunc(sysdate))
               AND    xxas_mss_util_pkg.max_asg_last_change_date(paaf.assignment_id) BETWEEN paaf.effective_start_date 
                                                                                     AND     paaf.effective_end_date
               UNION
               SELECT pc.contract_id
                     ,pc.reference
               FROM   per_contracts_f pc
                     ,per_all_assignments_f paaf
               WHERE  {requestParam.id} is not null
               AND    pc.contract_id       = {requestParam.id}
               AND    pc.business_group_id = {requestParam.bgId}
               AND    pc.person_id         = {requestParam.personId}
               AND    paaf.person_id       = pc.person_id
               AND    paaf.primary_flag    = 'Y'
               AND    trunc(sysdate) BETWEEN pc.effective_start_date 
                                     AND     nvl(pc.effective_end_date,trunc(sysdate))
               AND    xxas_mss_util_pkg.max_asg_last_change_date(paaf.assignment_id) BETWEEN paaf.effective_start_date 
                                                                                     AND     paaf.effective_end_date) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'AsgStatus'
    ,p_description        => 'Find Assignment Statuses matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'bg/asgstatus'
    ,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
                     ("asgstatus"
                     ,XMLForest
                       (p.asg_status_id "id"
                       ,p.asg_status    "value"
                       ,p.asg_status    "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT stt.assignment_status_type_id            asg_status_id
                     ,nvl(statl.user_status,stttl.user_status) asg_status
               FROM   per_assignment_status_types stt
                     ,per_assignment_status_types_tl stttl
                     ,hr_lookups l
                     ,per_ass_status_type_amends sta
                     ,per_ass_status_type_amends_tl statl
               WHERE  {requestParam.q} is not null
               AND    UPPER(nvl(statl.user_status,stttl.user_status)) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    nvl(sta.per_system_status,stt.per_system_status) IN('ACTIVE_ASSIGN','END','SUSP_ASSIGN','TERM_ASSIGN')
               AND    stt.assignment_status_type_id = sta.assignment_status_type_id (+)
               AND    sta.business_group_id(+) = {requestParam.bgId}
               AND    nvl(stt.business_group_id,{requestParam.bgId}) = {requestParam.bgId}
               AND    nvl(sta.active_flag,stt.active_flag) = 'Y'
               AND    stt.pay_system_status = l.lookup_code
               AND    nvl(stt.legislation_code, xxas_util_pkg.get_leg_code_from_bg({requestParam.bgId})) = xxas_util_pkg.get_leg_code_from_bg({requestParam.bgId})
               AND    l.lookup_type = 'PAY_ASS_SYS_STATUS' AND l.enabled_flag = 'Y'
               AND    trunc(sysdate) BETWEEN nvl(l.start_date_active, trunc(sysdate))
                                     AND     nvl(l.end_date_active, trunc(sysdate))
               AND    stt.assignment_status_type_id = stttl.assignment_status_type_id
               AND    stttl.language = userenv('LANG')
               AND    sta.ass_status_type_amend_id = statl.ass_status_type_amend_id(+)
               AND    DECODE(statl.ass_status_type_amend_id, null, '1',statl.language) =
                      DECODE(statl.ass_status_type_amend_id, null, '1', userenv('LANG'))
               UNION
               SELECT stt.assignment_status_type_id            asg_status_id
                     ,nvl(statl.user_status,stttl.user_status) asg_status
               FROM   per_assignment_status_types stt
                     ,per_assignment_status_types_tl stttl
                     ,hr_lookups l
                     ,per_ass_status_type_amends sta
                     ,per_ass_status_type_amends_tl statl
               WHERE  {requestParam.id} is not null
               AND    stt.assignment_status_type_id = {requestParam.id}
               AND    nvl(sta.per_system_status,stt.per_system_status) IN('ACTIVE_ASSIGN','END','SUSP_ASSIGN','TERM_ASSIGN')
               AND    stt.assignment_status_type_id = sta.assignment_status_type_id (+)
               AND    sta.business_group_id(+) = {requestParam.bgId}
               AND    nvl(stt.business_group_id,{requestParam.bgId}) = {requestParam.bgId}
               AND    nvl(sta.active_flag,stt.active_flag) = 'Y'
               AND    stt.pay_system_status = l.lookup_code
               AND    nvl(stt.legislation_code, xxas_util_pkg.get_leg_code_from_bg({requestParam.bgId})) = xxas_util_pkg.get_leg_code_from_bg({requestParam.bgId})
               AND    l.lookup_type = 'PAY_ASS_SYS_STATUS' AND l.enabled_flag = 'Y'
               AND    trunc(sysdate) BETWEEN nvl(l.start_date_active, trunc(sysdate))
                                     AND     nvl(l.end_date_active, trunc(sysdate))
               AND    stt.assignment_status_type_id = stttl.assignment_status_type_id
               AND    stttl.language = userenv('LANG')
               AND    sta.ass_status_type_amend_id = statl.ass_status_type_amend_id(+)
               AND    DECODE(statl.ass_status_type_amend_id, null, '1',statl.language) =
                      DECODE(statl.ass_status_type_amend_id, null, '1', userenv('LANG'))) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CagrGradeTypes'
    ,p_description        => 'Find CAGR Grade Types matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'bg/collag/cagrgradetypes'
    ,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
                     ("cagrgradetypes"
                     ,XMLForest
                       (p.grade_type_name "id"
                       ,p.grade_type_name "value"
                       ,p.grade_type_name "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT cgs.id_flex_num
                     ,fx.id_flex_structure_name grade_type_name
               FROM   fnd_id_flex_structures_vl fx
                     ,per_collective_agreements ca
                     ,per_cagr_grade_structures cgs
               WHERE  {requestParam.q} is not null
               AND    UPPER(fx.id_flex_structure_name) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    cgs.collective_agreement_id  = ca.collective_agreement_id 
               AND    cgs.id_flex_num              = fx.id_flex_num 
               AND    fx.id_flex_code              = 'CAGR' 
               AND    fx.application_id            = 800
               AND    ca.business_group_id         = {requestParam.bgId}
               AND    cgs.collective_agreement_id  = {requestParam.collAgId}
               UNION
               SELECT cgs.id_flex_num
                     ,fx.id_flex_structure_name grade_type_name
               FROM   fnd_id_flex_structures_vl fx
                     ,per_collective_agreements ca
                     ,per_cagr_grade_structures cgs
               WHERE  {requestParam.id} is not null
               AND    fx.id_flex_structure_name    = {requestParam.id}
               AND    ca.business_group_id         = {requestParam.bgId}
               AND    cgs.collective_agreement_id  = {requestParam.collAgId}
               AND    cgs.collective_agreement_id  = ca.collective_agreement_id 
               AND    cgs.id_flex_num              = fx.id_flex_num 
               AND    fx.id_flex_code              = 'CAGR' 
               AND    fx.application_id            = 800) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'CWKAsgStatus'
    ,p_description        => 'Find CWK Assignment Statuses matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'bg/cwkasgstatus'
    ,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
                     ("asgstatus"
                     ,XMLForest
                       (p.asg_status_id "id"
                       ,p.asg_status    "value"
                       ,p.asg_status    "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT stt.assignment_status_type_id            asg_status_id
                     ,nvl(statl.user_status,stttl.user_status) asg_status
               FROM   per_assignment_status_types stt
                     ,per_assignment_status_types_tl stttl
                     ,hr_lookups l
                     ,per_ass_status_type_amends sta
                     ,per_ass_status_type_amends_tl statl
               WHERE  {requestParam.q} is not null
               AND    UPPER(nvl(statl.user_status,stttl.user_status)) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    nvl(sta.per_system_status,stt.per_system_status) IN('ACTIVE_CWK','END','SUSP_CWK_ASG','TERM_CWK')
               AND    stt.assignment_status_type_id = sta.assignment_status_type_id (+)
               AND    sta.business_group_id(+) = {requestParam.bgId}
               AND    nvl(stt.business_group_id,{requestParam.bgId}) = {requestParam.bgId}
               AND    nvl(sta.active_flag,stt.active_flag) = 'Y'
               AND    stt.pay_system_status = l.lookup_code
               AND    nvl(stt.legislation_code, xxas_util_pkg.get_leg_code_from_bg({requestParam.bgId})) = xxas_util_pkg.get_leg_code_from_bg({requestParam.bgId})
               AND    l.lookup_type = 'PAY_ASS_SYS_STATUS' AND l.enabled_flag = 'Y'
               AND    trunc(sysdate) BETWEEN nvl(l.start_date_active, trunc(sysdate))
                                     AND     nvl(l.end_date_active, trunc(sysdate))
               AND    stt.assignment_status_type_id = stttl.assignment_status_type_id
               AND    stttl.language = userenv('LANG')
               AND    sta.ass_status_type_amend_id = statl.ass_status_type_amend_id(+)
               AND    DECODE(statl.ass_status_type_amend_id, null, '1',statl.language) =
                      DECODE(statl.ass_status_type_amend_id, null, '1', userenv('LANG'))
               UNION
               SELECT stt.assignment_status_type_id            asg_status_id
                     ,nvl(statl.user_status,stttl.user_status) asg_status
               FROM   per_assignment_status_types stt
                     ,per_assignment_status_types_tl stttl
                     ,hr_lookups l
                     ,per_ass_status_type_amends sta
                     ,per_ass_status_type_amends_tl statl
               WHERE  {requestParam.id} is not null
               AND    stt.assignment_status_type_id = {requestParam.id}
               AND    nvl(sta.per_system_status,stt.per_system_status) IN('ACTIVE_CWK','END','SUSP_CWK_ASG','TERM_CWK')
               AND    stt.assignment_status_type_id = sta.assignment_status_type_id (+)
               AND    sta.business_group_id(+) = {requestParam.bgId}
               AND    nvl(stt.business_group_id,{requestParam.bgId}) = {requestParam.bgId}
               AND    nvl(sta.active_flag,stt.active_flag) = 'Y'
               AND    stt.pay_system_status = l.lookup_code
               AND    nvl(stt.legislation_code, xxas_util_pkg.get_leg_code_from_bg({requestParam.bgId})) = xxas_util_pkg.get_leg_code_from_bg({requestParam.bgId})
               AND    l.lookup_type = 'PAY_ASS_SYS_STATUS' AND l.enabled_flag = 'Y'
               AND    trunc(sysdate) BETWEEN nvl(l.start_date_active, trunc(sysdate))
                                     AND     nvl(l.end_date_active, trunc(sysdate))
               AND    stt.assignment_status_type_id = stttl.assignment_status_type_id
               AND    stttl.language = userenv('LANG')
               AND    sta.ass_status_type_amend_id = statl.ass_status_type_amend_id(+)
               AND    DECODE(statl.ass_status_type_amend_id, null, '1',statl.language) =
                      DECODE(statl.ass_status_type_amend_id, null, '1', userenv('LANG'))) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'Vendor'
    ,p_description        => 'Find Suppliers matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'vendor'
    ,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
                     ("vendor"
                     ,XMLForest
                       (p.vendor_id   "id"
                       ,p.vendor_name "value"
                       ,p.vendor_name "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT pov.vendor_id
                     ,pov.vendor_name
               FROM   po_vendors pov
               WHERE  {requestParam.q} is not null
               AND    UPPER(pov.vendor_name) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    trunc(sysdate) BETWEEN nvl(pov.start_date_active,trunc(sysdate)) 
                                     AND     nvl(pov.end_date_active,trunc(sysdate))
               AND    pov.enabled_flag = 'Y'
               UNION
               SELECT pov.vendor_id
                     ,pov.vendor_name
               FROM   po_vendors pov
               WHERE  {requestParam.id} is not null
               AND    pov.vendor_id    = {requestParam.id}
               AND    trunc(sysdate) BETWEEN nvl(pov.start_date_active,trunc(sysdate)) 
                                     AND     nvl(pov.end_date_active,trunc(sysdate))
               AND    pov.enabled_flag = 'Y') p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'VendorSite'
    ,p_description        => 'Find Supplier Sites matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'vendor/vendorsite'
    ,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
                     ("vendorsite"
                     ,XMLForest
                       (p.vendor_site_id   "id"
                       ,p.vendor_site_code "value"
                       ,p.vendor_site_code "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT povs.vendor_site_id
                     ,povs.vendor_site_code
               FROM   po_vendor_sites_all povs
               WHERE  {requestParam.q} is not null
               AND    UPPER(povs.vendor_site_code) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    povs.vendor_id = {requestParam.vendorId}
               UNION
               SELECT povs.vendor_site_id
                     ,povs.vendor_site_code
               FROM   po_vendor_sites_all povs
               WHERE  {requestParam.id} is not null
               AND    povs.vendor_site_id = {requestParam.id}
               AND    povs.vendor_id      = {requestParam.vendorId}) p
        ]'
    );


  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'PoHeader'
    ,p_description        => 'Find PO Headers matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'vendor/vendorsite/poheader'
    ,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
                     ("poheader"
                     ,XMLForest
                       (p.po_header_id "id"
                       ,p.po_header_id "value"
                       ,p.po_header_id "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT pha.po_header_id
               FROM   po_headers_all pha
               WHERE  {requestParam.q} is not null
               AND    UPPER(pha.po_header_id) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    pha.vendor_id      = {requestParam.vendorId}
               AND    pha.vendor_site_id = {requestParam.vendorSiteId}
               UNION
               SELECT pha.po_header_id
               FROM   po_headers_all pha
               WHERE  {requestParam.id} is not null
               AND    pha.po_header_id   = {requestParam.id}
               AND    pha.vendor_id      = {requestParam.vendorId}
               AND    pha.vendor_site_id = {requestParam.vendorSiteId}) p
        ]'
    );

  xxas_com_sr_pkg.set_rest_method
    (p_service_name       => l_SERVICE
    ,p_method_name        => 'PoHeaderLine'
    ,p_description        => 'Find PO Header Lines matching the given criteria including function predicate'
    ,p_methods            => 'GET'
    ,p_path               => 'poheader/poheaderline'
    ,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
                     ("poheaderline"
                     ,XMLForest
                       (p.po_line_id "id"
                       ,p.po_line_id "value"
                       ,p.po_line_id "label")
                     )
                   )
                 ) "data"
        FROM  (SELECT pla.po_line_id
               FROM   po_lines_all pla
               WHERE  {requestParam.q} is not null
               AND    UPPER(pla.po_line_id) LIKE UPPER('%' || {requestParam.q} || '%')
               AND    pla.po_header_id = {requestParam.poHeaderId}
               UNION
               SELECT pla.po_line_id
               FROM   po_lines_all pla
               WHERE  {requestParam.id} is not null
               AND    pla.po_line_id   = {requestParam.id}
               AND    pla.po_header_id = {requestParam.poHeaderId}) p
        ]'
    );

END;
/
COMMIT;
EXIT;
