General Query to find data from HRMS

Submitted by code_admin on Mon, 07/23/2018 - 09:25
  1. select
  2.     XXGCEUL.XXGCEUL_CACULATIONS_PKG.Sick_Cat_A(
  3.         so_far2.reporting_class
  4.     ) as Sick_Cat_A,
  5.     XXGCEUL.XXGCEUL_CACULATIONS_PKG.Sick_Cat_B(
  6.         so_far2.BVPI_Employment_Category
  7.     ) as Sick_Cat_B,
  8.     so_far2.*
  9. from (
  10.     select
  11.         so_far.*,
  12.         --Now run calce
  13.             XXGCEUL.XXGCEUL_CACULATIONS_PKG.FTE(
  14.                 so_far.salary_payment_basis,
  15.                 so_far.full_time_hours,
  16.                 so_far.anualised_hours,
  17.                 so_far.assignment_id,
  18.                 xxgceul_context_pkg.show_context_date ('Effective Date')
  19.             ) as FTE,
  20.             XXGCEUL.XXGCEUL_CACULATIONS_PKG.BVPI_Employment_Category(
  21.                 so_far.assignment_category,
  22.                 xxgceul_context_pkg.show_context_date ('Effective Date'),
  23.                 so_far.Assignment_Start_Date        --Hire_Date IN Date
  24.             ) as BVPI_Employment_Category,
  25.             XXGCEUL.XXGCEUL_CACULATIONS_PKG.Age(
  26.                 so_far.date_of_birth,
  27.                 xxgceul_context_pkg.show_context_date ('Effective Date')
  28.             ) as Age,
  29.             XXGCEUL.XXGCEUL_CACULATIONS_PKG.Age_range_10(
  30.                 so_far.date_of_birth,
  31.                 xxgceul_context_pkg.show_context_date ('Effective Date')
  32.             ) as Age_range_10,
  33.             XXGCEUL.XXGCEUL_CACULATIONS_PKG.Age_range_5(
  34.                 so_far.date_of_birth,
  35.                 xxgceul_context_pkg.show_context_date ('Effective Date')
  36.             ) as Age_range_5,
  37.             XXGCEUL.XXGCEUL_CACULATIONS_PKG.BME_Minority(
  38.                 so_far.Ethnic_Origin
  39.             ) as BME_Minority,
  40.             XXGCEUL.XXGCEUL_CACULATIONS_PKG.Months_Service(
  41.                 so_far.original_date_of_hire,
  42.                 xxgceul_context_pkg.show_context_date ('Effective Date')
  43.             ) as Months_Service,
  44.             XXGCEUL.XXGCEUL_CACULATIONS_PKG.Length_Of_Service_range(
  45.                 so_far.original_date_of_hire,
  46.                 xxgceul_context_pkg.show_context_date ('Effective Date')
  47.             ) as Length_Of_Service_range,
  48.             XXGCEUL.XXGCEUL_CACULATIONS_PKG.Service_Greenwich(
  49.                 so_far.latest_start_date, --ppos_date_start
  50.                 xxgceul_context_pkg.show_context_date ('Effective Date')
  51.             ) as Service_Greenwich,
  52.             XXGCEUL.XXGCEUL_CACULATIONS_PKG.Service_L_Gov(
  53.                 so_far.adjusted_svc_date,
  54.                 xxgceul_context_pkg.show_context_date ('Effective Date')
  55.             ) as Service_L_Gov,
  56.             XXGCEUL.XXGCEUL_CACULATIONS_PKG.Reporting_Class(
  57.                 so_far.payroll_id,
  58.                 so_far.assignment_status,
  59.                 so_far.salary_payment_basis,
  60.                 so_far.job_name,
  61.                 so_far.pos_name,
  62.                 so_far.assignment_category,
  63.                 so_far.team
  64.             ) as Reporting_Class,
  65.             XXGCEUL.XXGCEUL_CACULATIONS_PKG.Pen_ContPerc(
  66.                 so_far.pension,
  67.                 so_far.pen_lbg_percent,
  68.                 so_far.pen_lbg_ab_percent,
  69.                 so_far.pen_lpf_percent,
  70.                 so_far.pen_teach_percent,
  71.                 so_far.pen_councillors_percent,
  72.                 so_far.business_group_id
  73.             ) pen_all_percent,
  74.             XXGCEUL.XXGCEUL_CACULATIONS_PKG.Pen_ContSD(
  75.                 so_far.pension,
  76.                 so_far.pen_lbg_start,
  77.                 so_far.pen_lbg_ab_start,
  78.                 so_far.pen_lpf_start,
  79.                 so_far.pen_teach_start,
  80.                 so_far.pen_councillors_start,
  81.                 so_far.business_group_id
  82.             ) pen_all_start,
  83.             XXGCEUL.XXGCEUL_CACULATIONS_PKG.Pen_ContED(
  84.                 so_far.pension,
  85.                 so_far.pen_lbg_end,
  86.                 so_far.pen_lbg_ab_end,
  87.                 so_far.pen_lpf_end,
  88.                 so_far.pen_teach_end,
  89.                 so_far.pen_councillors_end,
  90.                 so_far.business_group_id
  91.             ) pen_all_end,
  92.             XXGCEUL.XXGCEUL_CACULATIONS_PKG.Eth_Category(
  93.                 eth_lookup
  94.             ) Ethnic_Category
  95.     --
  96.     --        XXGCEUL.XXGCEUL_CACULATIONS_PKG.Pen_ContPerc(
  97.     --            so_far.assignment_id,
  98.     --            so_far.pension,
  99.     --            xxgceul_context_pkg.show_context_date ('Effective Date')
  100.     --        ) as Pen_Cont_Per,
  101.     --        XXGCEUL.XXGCEUL_CACULATIONS_PKG.Pen_ContSD(
  102.     --            so_far.assignment_id,
  103.     --            so_far.pension,
  104.     --            xxgceul_context_pkg.show_context_date ('Effective Date')
  105.     --        ) as Pen_Cont_Start,
  106.     --        XXGCEUL.XXGCEUL_CACULATIONS_PKG.Pen_ContED(
  107.     --            so_far.assignment_id,
  108.     --            so_far.pension,
  109.     --            xxgceul_context_pkg.show_context_date ('Effective Date')
  110.     --        ) as Pen_Cont_End
  111.     from (
  112.         select
  113.             papf.person_id,
  114.             papf.applicant_number,
  115.             papf.employee_number,
  116.             papf.full_name,
  117.             papf.first_name,
  118.             papf.last_name,
  119.             initcap(papf.title) as title,
  120.             papf.date_of_birth,
  121.             papf.national_identifier as NI_Number,
  122.             papf.sex as gender,
  123.             papf.original_date_of_hire,
  124.             papf.previous_last_name,
  125.             papf.per_information1 as eth_lookup,
  126.             eth_orig.Meaning as Ethnic_Origin,
  127.             paaf.assignment_id,
  128.             paaf.effective_start_date as ass_esd,
  129.             paaf.effective_end_date as ass_eed,
  130.             paaf.assignment_number,
  131.             paaf.normal_hours,
  132.             paaf.ass_attribute3 as Leg_Contract_Num,
  133.             apps.fnd_date.canonical_to_date(paaf.ass_attribute4) as Motor_Ins_Expiry,
  134.             apps.fnd_date.canonical_to_date(paaf.ass_attribute5) as Fixed_Term_Cont_End,
  135.             decode(paaf.business_group_id,4194,paaf.ass_attribute10,'') as Leg_GC_Assign_Num,
  136.             paaf.grade_id,
  137.             paaf.supervisor_assignment_id,
  138.             ass_cat_lu.meaning as assignment_category,
  139.             paaf.primary_flag,
  140.             paaf.payroll_id,
  141.             paaf.business_group_id,
  142.             xoh.TEAM_ID,
  143.             xoh.TEAM_COST_ID,
  144.             xoh.TEAM_LOC_ID,
  145.             xoh.TEAM_FROM,
  146.             xoh.TEAM_TO,
  147.             nvl(xoh.TEAM,'???') as team,
  148.             xoh.TEAM_COST1,
  149.             xoh.TEAM_COST2,
  150.             xoh.TEAM_COST3,
  151.             xoh.TEAM_LOC_CODE,
  152.             xoh.TEAM_LOC_DESC,
  153.             xoh.TEAM_ADD1,
  154.             xoh.TEAM_ADD2,
  155.             xoh.TEAM_ADD3,
  156.             xoh.TEAM_TOWN,
  157.             xoh.TEAM_COUNTRY,
  158.             xoh.TEAM_POSTCODE,
  159.             xoh.TEAM_REP_LEA,
  160.             xoh.TEAM_ESTAB_NUM,
  161.             xoh.TEAM_ESTAB_NAME,
  162.             xoh.TEAM_ESTAB_TYPE,
  163.             xoh.TEAM_SCH_NUM,
  164.             xoh.TEAM_LEA_NUM,
  165.             xoh.SECTION_ID,
  166.             xoh.SECTION_COST_ID,
  167.             xoh.SECTION_LOC_ID,
  168.             xoh.SECTION_FROM,
  169.             xoh.SECTION_TO,
  170.             nvl(xoh.SECTION,'???') as section,
  171.             xoh.SECTION_COST1,
  172.             xoh.SECTION_COST2,
  173.             xoh.SECTION_COST3,
  174.             xoh.SECTION_LOC_CODE,
  175.             xoh.SECTION_LOC_DESC,
  176.             xoh.SECTION_ADD1,
  177.             xoh.SECTION_ADD2,
  178.             xoh.SECTION_ADD3,
  179.             xoh.SECTION_TOWN,
  180.             xoh.SECTION_COUNTRY,
  181.             xoh.SECTION_POSTCODE,
  182.             xoh.SECTION_REP_LEA,
  183.             xoh.SECTION_ESTAB_NUM,
  184.             xoh.SECTION_ESTAB_NAME,
  185.             xoh.SECTION_ESTAB_TYPE,
  186.             xoh.SECTION_SCH_NUM,
  187.             xoh.SECTION_LEA_NUM,
  188.             xoh.SERV_ID,
  189.             xoh.SERV_COST_ID,
  190.             xoh.SERV_LOC_ID,
  191.             xoh.SERV_FROM,
  192.             xoh.SERV_TO,
  193.             nvl(xoh.SERV,'???') as Service_Area,
  194.             xoh.SERV_COST1,
  195.             xoh.SERV_COST2,
  196.             xoh.SERV_COST3,
  197.             xoh.SERV_LOC_CODE,
  198.             xoh.SERV_LOC_DESC,
  199.             xoh.SERV_ADD1,
  200.             xoh.SERV_ADD2,
  201.             xoh.SERV_ADD3,
  202.             xoh.SERV_TOWN,
  203.             xoh.SERV_COUNTRY,
  204.             xoh.SERV_POSTCODE,
  205.             xoh.SERV_REP_LEA,
  206.             xoh.SERV_ESTAB_NUM,
  207.             xoh.SERV_ESTAB_NAME,
  208.             xoh.SERV_ESTAB_TYPE,
  209.             xoh.SERV_SCH_NUM,
  210.             xoh.SERV_LEA_NUM,
  211.             xoh.DIV_ID,
  212.             xoh.DIV_COST_ID,
  213.             xoh.DIV_LOC_ID,
  214.             xoh.DIV_FROM,
  215.             xoh.DIV_TO,
  216.             nvl(xoh.DIV,'???') as Division,
  217.             xoh.DIV_COST1,
  218.             xoh.DIV_COST2,
  219.             xoh.DIV_COST3,
  220.             xoh.DIV_LOC_CODE,
  221.             xoh.DIV_LOC_DESC,
  222.             xoh.DIV_ADD1,
  223.             xoh.DIV_ADD2,
  224.             xoh.DIV_ADD3,
  225.             xoh.DIV_TOWN,
  226.             xoh.DIV_COUNTRY,
  227.             xoh.DIV_POSTCODE,
  228.             xoh.DIV_REP_LEA,
  229.             xoh.DIV_ESTAB_NUM,
  230.             xoh.DIV_ESTAB_NAME,
  231.             xoh.DIV_ESTAB_TYPE,
  232.             xoh.DIV_SCH_NUM,
  233.             xoh.DIV_LEA_NUM,
  234.             xoh.DIR_ID,
  235.             xoh.DIR_COST_ID,
  236.             xoh.DIR_LOC_ID,
  237.             xoh.DIR_FROM,
  238.             xoh.DIR_TO,
  239.             nvl(xoh.DIR,'???') as Directorate,
  240.             xoh.DIR_COST1,
  241.             xoh.DIR_COST2,
  242.             xoh.DIR_COST3,
  243.             xoh.DIR_LOC_CODE,
  244.             xoh.DIR_LOC_DESC,
  245.             xoh.DIR_ADD1,
  246.             xoh.DIR_ADD2,
  247.             xoh.DIR_ADD3,
  248.             xoh.DIR_TOWN,
  249.             xoh.DIR_COUNTRY,
  250.             xoh.DIR_POSTCODE,
  251.             xoh.DIR_REP_LEA,
  252.             xoh.DIR_ESTAB_NUM,
  253.             xoh.DIR_ESTAB_NAME,
  254.             xoh.DIR_ESTAB_TYPE,
  255.             xoh.DIR_SCH_NUM,
  256.             xoh.DIR_LEA_NUM,
  257.             past.user_status as assignment_status,
  258.             pj.name as job_name,
  259.             --**People Group**
  260.             --Greenwich BG and GSP
  261.             decode(paaf.business_group_id,101,ppg.segment1,4194,ppg.segment1,'') as salary_payment_basis,
  262.             decode(paaf.business_group_id,101,pen_sch_des.description,4194,pen_sch_des_gsp.description ,'') as pension,
  263.             decode(paaf.business_group_id,101,ppg.segment3,4194,ppg.segment3,'') as car_user_type,
  264.             decode(paaf.business_group_id,101,ppg.segment4,4194,ppg.segment4,'') as osp_scheme,
  265.             decode(paaf.business_group_id,101,ppg.segment5,4194,ppg.segment5,'') as retained_pay,
  266.             decode(paaf.business_group_id,101,ppg.segment6,4194,ppg.segment6,'') as anualised_hours,
  267.             decode(paaf.business_group_id,101,ppg.segment7,4194,ppg.segment7,'') as term_time_weeks,
  268.             decode(paaf.business_group_id,101,ppg.segment8,4194,ppg.segment8,'') as number_of_sessions,
  269.             decode(paaf.business_group_id,101,ppg.segment9,4194,ppg.segment9,'') as teacher_tpa_code,
  270.             decode(paaf.business_group_id,101,ppg.segment10,4194,ppg.segment10,'') as Leave_Band,
  271.             --Pensions BG
  272.             case paaf.business_group_id when 102 then ppg.segment1 else '' end as Ill_Health_Indicator,
  273.             --**People Group End**
  274.             --**Additional Person Details**
  275.             papf.ATTRIBUTE1 as ExILEAContractNum,            
  276.             apps.fnd_date.canonical_to_date(papf.ATTRIBUTE2) as RetirementExtensionExp,            
  277.             --**END Additional Person Details**
  278.             paa.contract_type,
  279.             paa.work_pattern,
  280.             paa.start_day,
  281.             paa.effective_start_date as paa_esd,
  282.             paa.effective_end_date as paa_eed,
  283.             paa.LGPS_PROCESS_FLAG,
  284.             paa.LGPS_EXCLUSION_TYPE,
  285.             paa.LGPS_PENSIONABLE_PAY,
  286.             paa.LGPS_TRANS_ARRANG_FLAG,
  287.             paa.LGPS_Membership_number,
  288.             ppos.adjusted_svc_date,
  289.             ppos.date_start as latest_start_date,
  290.             ppos.accepted_termination_date,
  291.             ppos.actual_termination_date,
  292.             ppos.final_process_date,
  293.             ppos.last_standard_process_date,
  294.             ppos.leaving_reason,
  295.             APPS.hr_reports.get_lookup_meaning('LEAV_REAS', ppos.leaving_reason) as leaving_reason_full,
  296.             ppos.notified_termination_date,
  297.             ppos.projected_termination_date,
  298.             hapf.position_id,
  299.             hapf.name as pos_name,
  300.             hapf.pos_post_num,
  301.             hapf.pos_org,
  302.             hapf.pos_SSDS_Line_Num,
  303.             hapf.pos_req_crb,
  304.             hapf.pos_crb_lev,
  305.             hapf.pos_tied_accom,
  306.             hapf.pos_wf_plan_pri,
  307.             hapf.pos_ISA,
  308.             ppf.payroll_name as payroll,
  309.             loc.location_code as loc_location_code,
  310.             loc.description as loc_description,
  311.             loc.address_line_1 as loc_address_line_1,
  312.             loc.address_line_2 as loc_address_line_2,
  313.             loc.address_line_3 as loc_address_line_3,
  314.             loc.town_or_city as loc_town,
  315.             loc.country as loc_country,
  316.             loc.postal_code as loc_postal_code,
  317.             loc.Rep_Through_LEA as loc_Rep_Through_LEA,
  318.             loc.Establishment_Num as loc_Establishment_Num,
  319.             loc.Establishment_Name as loc_Establishment_Name,
  320.             loc.Establishment_Type as loc_Establishment_Type,
  321.             loc.School_Number as loc_School_Number,
  322.             loc.LEA_Number as loc_LEA_Number,
  323.             pdf.Disabled,
  324.             pq_dfes.membership_number as dfes_number,
  325.             pq_dfes.professional_body_name as dfes_pro_body,
  326.             ppei.etw_Entitlement_to_work_in_UK,
  327.             ppei.etw_ID_Verfied,
  328.             ppei.etw_Type_of_ID,
  329.             ppei.etw_DateWP_SponsorReq,
  330.             ppei.etw_DateWP_SponsorRec,
  331.             ppei.etw_WP_SponsorNum,
  332.             ppei.etw_WP_SponsorExp,
  333.             ppei.etw_Leave_To_Remain_Exp,
  334.             ppei.etw_Student_Visa,
  335.             ppei.etw_Student_Visa_Exp_Date,
  336.             ppei.etw_Terms_And_Conditions,
  337.             ppei_ls.long_serv_invite,
  338.             ppei_ls.long_serv_recieved,
  339.             fthlu.full_time_hours,
  340.             sup_v.full_name as sup_name,
  341.             sup_v.assignment_number as sup_ass_num,
  342.             nvl((
  343.                 select min(effective_Start_date)
  344.                 from hr.per_all_assignments_f paaf1
  345.                 where paaf1.assignment_id=paaf.assignment_id
  346.                 and paaf1.assignment_type='E'
  347.             ),TO_DATE('01-JAN-1951','DD-MON-YYYY')) as Assignment_Start_Date,
  348.             pg.Grade_Name,
  349.             pg.Grade,
  350.             pg.Grade_Description,
  351.             pg.Grade_TPA,
  352.             pg.Grade_Band,
  353.             pg_sp.SPINAL_POINT,
  354.             pg_sp.auto_increment_flag,
  355.             pg_sp.Ceiling_Spinal_Point,
  356.             pg_sp.parent_spine as pg_sp_parent_spine,
  357.             nvl(rates.pg_sp_annual_value,0) as pg_sp_annual_value,
  358.             nvl(pg_sp_lw_annual_value,0) as pg_sp_lw_annual_value,
  359.             spot.approved as spot_approved,
  360.             spot.proposed_salary_n as spot_amount,
  361.             spot.change_date as spot_from,
  362.             spot.date_to as spot_to,
  363.             (nvl(spot.proposed_salary_n,0) + nvl(rates.pg_sp_annual_value,0)) as sal_basic,
  364.             (nvl(spot.proposed_salary_n,0) + nvl(rates.pg_sp_annual_value,0) + nvl(pg_sp_lw_annual_value,0)) as sal_total,                      
  365.             (
  366.                 select hl.meaning
  367.                 from apps.hr_lookups hl  
  368.                 where hl.lookup_type = 'HOME_OFFICE'
  369.                 and papf.expense_check_send_to_address = lookup_code
  370.             ) as Office_Details_Mail_To,
  371.             pa.address_line1 as per_add_line1,
  372.             pa.address_line2 as per_add_line2,
  373.             pa.address_line3 as per_add_line3,
  374.             pa.country  as per_add_country,
  375.             pa.postal_code as per_postcode,
  376.             pa.address_type as per_address_type,
  377.             nvl(ppei_crb.crb_Rec_Ent,'No') as crb_Rec_Ent,
  378.             ppei_crb.crb_Number,
  379.             ppei_crb.crb_Form_Ref,
  380.             ppei_crb.crb_Type,
  381.             ppei_crb.crb_Requested,
  382.             ppei_crb.crb_App_Recieved,
  383.             ppei_crb.crb_Outcome,
  384.             ppei_crb.crb_Disc_Num,
  385.             ppei_crb.crb_Renewal,
  386.             ppei_crb.crb_Port_Cert,
  387.             ppei_crb.crb_Port_Date,
  388.             ppei_crb.crb_Suitability_Conf,
  389.             ppei_crb.crb_Unsuitable_Offence,
  390.             ppei_crb.crb_CO_Notif_of_Offences,
  391.             ppei_crb.crb_CO_Notif_of_Offences_date,
  392.             ppei_crb.crb_CO_Notif_of_Clear,
  393.             ppei_crb.crb_CO_Notif_of_Date,
  394.             ppei_crb.crb_Appeal_Lodged,
  395.             ppei_crb.crb_Appeal_Hear_Date,
  396.             ppei_crb.crb_Outcome_Decision,
  397.             ppei_crb.crb_Medical_Sent_Out,
  398.             ppei_crb.crb_Medical_Cleared,
  399.             ppei_crb.crb_Letter_Sent_Out,
  400.             ppei_crb.crb_Notes,
  401.             ppei_crb.crb_List99ISAClear,
  402.             ppei_crb.crb_List99ISAClearDate,
  403.             ppei_crb.crb_CPAccess,
  404.             ppei_crb.crb_CPAccessDate,
  405.             ppei_crb.crb_CPAdminNotified,
  406.             pen_ele.lbg_p as pen_lbg_percent,
  407.             pen_ele.lbg_s as pen_lbg_start,
  408.             pen_ele.lbg_e as pen_lbg_end,
  409.             pen_ele.lbga_p as pen_lbg_ab_percent,
  410.             pen_ele.lbga_s as pen_lbg_ab_start,
  411.             pen_ele.lbga_e as pen_lbg_ab_end,
  412.             pen_ele.lpf_p as pen_lpf_percent,
  413.             pen_ele.lpf_s as pen_lpf_start,
  414.             pen_ele.lpf_e as pen_lpf_end,
  415.             pen_ele.t_p as pen_teach_percent,
  416.             pen_ele.t_s as pen_teach_start,
  417.             pen_ele.t_e as pen_teach_end,
  418.             pen_ele.c_p as pen_councillors_percent,
  419.             pen_ele.c_s as pen_councillors_start,
  420.             pen_ele.c_e as pen_councillors_end,
  421.             bank.sort_code as bank_sort_code,
  422.             bank.bank_name as bank_name,
  423.             bank.bank_branch as bank_branch,
  424.             bank.Account_number as bank_Account_number,
  425.             bank.Account_Name as bank_Account_Name,
  426.             bank.Account_Type as bank_Account_Type,
  427.             bank.BID_Soc_Acc_No as bank_BID_Soc_Acc_No,
  428.             bank.bank_Branc_Loc as bank_Branc_Loc,
  429.             bank.org_payment_method_name as bank_org_payment_method_name,
  430.             bank.percentage as bank_percentage,
  431.             bank.PO_Number as bank_PO_Number,
  432.             bank.Overseas_Payment_Ind as bank_Overseas_Payment_Ind,
  433.             bank.bank_context,
  434.             ev_pd.Tax_Code,
  435.             ev_pd.effective_start_date as Tax_Code_ESD,
  436.             ev_pd.effective_end_date as Tax_Code_EED,
  437.             ev_pd.Tax_Basis,
  438.             ev_pd.effective_start_date as Tax_Basis_ESD,
  439.             ev_pd.effective_end_date as Tax_Basis_EED,
  440.             ev_pd.Tax_PayPrev,
  441.             ev_pd.Tax_Prev,
  442.             ev_pd.Tax_Refundable,
  443.             ev_pd.Tax_Authority,
  444.             ev_nic.NI_Cat as NI_Cat,
  445.             ev_nic.effective_start_date as NI_Cat_ESD,
  446.             ev_nic.effective_end_date as NI_Cat_EED,        
  447.             ev_nic.NI_Cert,
  448.             ev_nic.NI_Pension,
  449.             ev_nic.NI_Process
  450.         from (
  451.             select paaf1.*
  452.             from hr.per_all_assignments_f paaf1
  453.             where (
  454.                 xxgceul_context_pkg.show_context_date ('Effective Date')
  455.                          BETWEEN paaf1.effective_start_date
  456.                              AND paaf1.effective_end_date
  457.             )
  458.         ) paaf
  459.         left join (
  460.             select papf1.*
  461.             from hr.per_all_people_f papf1
  462.             where xxgceul_context_pkg.show_context_date ('Effective Date')
  463.                          BETWEEN papf1.effective_start_date
  464.                              AND papf1.effective_end_date
  465.         ) papf on paaf.person_id=papf.person_id
  466.         left join (
  467.             select
  468.                 flv.lookup_code,
  469.                 flv.Meaning,
  470.                 XXGCEUL.XXGCEUL_CACULATIONS_PKG.GetSecurityGroupFromKey(fsg.security_group_key) as security_group_key
  471.             from apps.fnd_lookup_values flv
  472.             left join apps.fnd_security_groups fsg on flv.security_group_id=fsg.security_group_id
  473.             where flv.lookup_type='ETH_TYPE'
  474.             and enabled_flag='Y'  
  475.         ) eth_orig on eth_orig.lookup_code=papf.per_information1 and eth_orig.security_group_key=paaf.business_group_id
  476.         left join (
  477.                 select
  478.                     flv.lookup_code,
  479.                     case substr(flv.meaning,0,1)
  480.                     when 'Z' then
  481.                         substr(flv.meaning,2)
  482.                     else
  483.                         flv.meaning
  484.                     end as meaning,
  485.                     XXGCEUL.XXGCEUL_CACULATIONS_PKG.GetSecurityGroupFromKey(fsg.security_group_key) as security_group_key
  486.                 from apps.fnd_lookup_values flv
  487.                 left join apps.fnd_security_groups fsg on flv.security_group_id=fsg.security_group_id
  488.                 where flv.lookup_type='EMP_CAT'
  489.         ) ass_cat_lu on ass_cat_lu.lookup_code=paaf.employment_category and ass_cat_lu.security_group_key=paaf.business_group_id
  490.         left join (
  491.             select *
  492.             from APPS.XXGCEUL_ORG_HIERARCHY_mv
  493.         ) xoh on xoh.team_id=paaf.organization_id
  494.         left join (
  495.             select *
  496.             from hr.per_assignment_status_types
  497.         ) past on past.assignment_status_type_id=paaf.assignment_status_type_id
  498.         left join (
  499.             select pj1.*
  500.             from hr.per_jobs pj1
  501.         ) pj on pj.job_id=paaf.job_id
  502.         left join (
  503.             select ppg1.*
  504.             from hr.pay_people_groups ppg1
  505.         ) ppg on ppg.people_group_id=paaf.people_group_id
  506.         left join (
  507.             select
  508.                 paa1.assignment_id,
  509.                 paa1.business_group_id,
  510.                 paa1.contract_type,
  511.                 paa1.work_pattern,
  512.                 paa1.start_day,
  513.                 paa1.effective_start_date,
  514.                 paa1.effective_end_date,
  515.                 paa1.LGPS_PROCESS_FLAG,
  516.                 paa1.LGPS_EXCLUSION_TYPE,
  517.                 paa1.LGPS_PENSIONABLE_PAY,
  518.                 paa1.LGPS_TRANS_ARRANG_FLAG,
  519.                 paa1.LGPS_Membership_number
  520.             from hr.PQP_assignment_attributes_f paa1
  521.             where xxgceul_context_pkg.show_context_date ('Effective Date')
  522.                          BETWEEN paa1.effective_start_date
  523.                              AND paa1.effective_end_date    
  524.         ) paa on paa.ASSIGNMENT_ID=paaf.assignment_id
  525.         left join (
  526.             select ppos1.*
  527.             from hr.PER_Periods_of_service ppos1
  528.         ) ppos on ppos.period_of_service_id=paaf.period_of_service_id
  529.         left join ( --No Improvement
  530.             select
  531.                 hapf1.position_id,
  532.                 ppd1.segment1 as pos_post_num,
  533.                 ppd1.segment2 as name,
  534.                 ppd1.segment3 as pos_org,
  535.                 hapf1.attribute1 as pos_SSDS_Line_Num,
  536.                 hapf1.attribute2 as pos_req_crb,
  537.                 hapf1.attribute3 as pos_crb_lev,
  538.                 hapf1.attribute4 as pos_tied_accom,
  539.                 hapf1.attribute5 as pos_wf_plan_pri,
  540.                 hapf1.attribute6 as pos_ISA
  541.             from hr.hr_all_positions_f hapf1
  542.             left join (
  543.                 select *
  544.                 from hr.per_position_definitions ppd2
  545.             ) ppd1 on ppd1.position_definition_id=hapf1.position_definition_id
  546.             where xxgceul_context_pkg.show_context_date ('Effective Date')
  547.                          BETWEEN hapf1.effective_start_date
  548.                              AND hapf1.effective_end_date    
  549.         ) hapf on hapf.position_id=paaf.position_id
  550.         left join ( -- No Improvement
  551.             select ppf1.*
  552.             from apps.pay_all_payrolls_f ppf1
  553.             where xxgceul_context_pkg.show_context_date ('Effective Date')
  554.                          BETWEEN ppf1.effective_start_date
  555.                              AND ppf1.effective_end_date    
  556.         ) ppf on ppf.payroll_id=paaf.payroll_id
  557.         left join ( -- No Improvement
  558.             select
  559.                 hla.location_id,
  560.                 hla.location_code,
  561.                 hla.description,
  562.                 hla.address_line_1,
  563.                 hla.address_line_2,
  564.                 hla.address_line_3,
  565.                 hla.town_or_city,
  566.                 ft.country,
  567.                 hla.postal_code,
  568.                 hlei.lei_information1 as Rep_Through_LEA,
  569.                 hlei.lei_information2 as Establishment_Num,
  570.                 hlei.lei_information3 as Establishment_Name,
  571.                 hlei.lei_information4 as Establishment_Type,
  572.                 hlei.lei_information5 as School_Number,
  573.                 hlei.lei_information6 as LEA_Number    
  574.             from (
  575.                 select *
  576.                 from hr.hr_locations_all hla1
  577.             ) hla
  578.             left join (
  579.                 select ft1.TERRITORY_CODE, ft1.TERRITORY_SHORT_NAME as country
  580.                 from apps.fnd_territories_tl ft1
  581.             ) ft on ft.territory_code=hla.country
  582.             left join (
  583.                 select *
  584.                 from hr.hr_location_extra_info hlei1
  585.             ) hlei on hlei.location_id=hla.location_id
  586.         ) loc on loc.location_id=paaf.location_id
  587.         left join ( --No Improvement
  588.             select
  589.                 person_id,
  590.                 category as Disabled,
  591.                 pdf1.effective_start_date,
  592.                 pdf1.effective_end_date
  593.             from per_disabilities_f pdf1
  594.             where xxgceul_context_pkg.show_context_date ('Effective Date')
  595.                          BETWEEN pdf1.effective_start_date
  596.                              AND pdf1.effective_end_date
  597.         ) pdf on pdf.person_id=paaf.person_id
  598.         left join ( --No Improvement
  599.             select
  600.                 pq1.person_id,
  601.                 pq1.membership_number,
  602.                 pq1.professional_body_name
  603.                 --,pq1.*
  604.             from hr.per_qualifications pq1
  605.             where pq1.qualification_type_id=8 --Qualified Teacher Status (QTS)
  606.             --and person_id=29627
  607.         ) pq_dfes on pq_dfes.person_id=paaf.person_id
  608.         left join ( --No Improvement
  609.             select
  610.                 ppei1.person_id,
  611.                 ppei1.pei_information1 as etw_Entitlement_to_work_in_UK,
  612.                 ppei1.pei_information2 as etw_ID_Verfied,
  613.                 ppei1.pei_information3 as etw_Type_of_ID,
  614.                 apps.fnd_date.CANONICAL_TO_DATE(ppei1.pei_information4) as etw_DateWP_SponsorReq,
  615.                 apps.fnd_date.CANONICAL_TO_DATE(ppei1.pei_information5) as etw_DateWP_SponsorRec,
  616.                 ppei1.pei_information6 as etw_WP_SponsorNum,
  617.                 apps.fnd_date.CANONICAL_TO_DATE(ppei1.pei_information7) as etw_WP_SponsorExp,
  618.                 apps.fnd_date.CANONICAL_TO_DATE(ppei1.pei_information8) as etw_Leave_To_Remain_Exp,
  619.                 ppei1.pei_information9 as etw_Student_Visa,
  620.                 apps.fnd_date.CANONICAL_TO_DATE(ppei1.pei_information10) as etw_Student_Visa_Exp_Date,
  621.                 ppei1.pei_information11 as etw_Terms_And_Conditions
  622.             from hr.per_people_extra_info ppei1
  623.             where ppei1.information_type = 'GC_ENTITLEMENT_TO_WORK'
  624.             and ppei1.person_extra_info_id = (
  625.                 select max(ppei2.person_extra_info_id)
  626.                 from hr.per_people_extra_info ppei2
  627.                 where ppei2.information_type = 'GC_ENTITLEMENT_TO_WORK'
  628.                 and ppei1.person_id=ppei2.person_id
  629.             )
  630.         ) ppei on ppei.person_id = paaf.person_id
  631.         left join (
  632.             select /*+ FIRST_ROWS */
  633.                 ppei1.person_id,
  634.                 ppei1.pei_information1 as long_serv_invite,
  635.                 ppei1.pei_information2 as long_serv_recieved
  636.             from hr.per_people_extra_info ppei1
  637.             where ppei1.information_type = 'GC_LONG_SERVICE_AWARD'
  638.             and ppei1.person_extra_info_id = (
  639.                 select max(ppei2.person_extra_info_id)
  640.                 from hr.per_people_extra_info ppei2
  641.                 where ppei2.information_type = 'GC_LONG_SERVICE_AWARD'
  642.                 and ppei1.person_id=ppei2.person_id
  643.             )
  644.         ) ppei_ls on ppei_ls.person_id = paaf.person_id
  645.         left join ( --Optermised no improvement
  646.             select
  647.                 'Yes' as crb_Rec_Ent,
  648.                 ppei1.person_id,
  649.                 ppei1.pei_information1 as crb_Number,
  650.                 ppei1.pei_information18 as crb_Form_Ref,
  651.                 ppei1.pei_information2 as crb_Type,
  652.                 apps.fnd_date.CANONICAL_TO_DATE(ppei1.pei_information3) as crb_Requested,
  653.                 apps.fnd_date.CANONICAL_TO_DATE(ppei1.pei_information4) as crb_App_Recieved,
  654.                 ppei1.pei_information5 as crb_Outcome,
  655.                 ppei1.pei_information6 as crb_Disc_Num,
  656.                 apps.fnd_date.CANONICAL_TO_DATE(ppei1.pei_information7) as crb_Renewal,
  657.                 ppei1.pei_information8 as crb_Port_Cert,
  658.                 apps.fnd_date.CANONICAL_TO_DATE(ppei1.pei_information9) as crb_Port_Date,
  659.                 ppei1.pei_information10 as crb_Suitability_Conf,
  660.                 ppei1.pei_information11 as crb_Unsuitable_Offence,
  661.                 ppei1.pei_information12 as crb_CO_Notif_of_Offences,
  662.                 apps.fnd_date.CANONICAL_TO_DATE(ppei1.pei_information13) as crb_CO_Notif_of_Offences_date,
  663.                 ppei1.pei_information20 as crb_CO_Notif_of_Clear,
  664.                 apps.fnd_date.CANONICAL_TO_DATE(ppei1.pei_information21) as crb_CO_Notif_of_Date,
  665.                 ppei1.pei_information14 as crb_Appeal_Lodged,
  666.                 apps.fnd_date.CANONICAL_TO_DATE(ppei1.pei_information15) as crb_Appeal_Hear_Date,
  667.                 ppei1.pei_information16 as crb_Outcome_Decision,
  668.                 apps.fnd_date.CANONICAL_TO_DATE(ppei1.pei_information22) as crb_Medical_Sent_Out,
  669.                 apps.fnd_date.CANONICAL_TO_DATE(ppei1.pei_information23) as crb_Medical_Cleared,
  670.                 apps.fnd_date.CANONICAL_TO_DATE(ppei1.pei_information24) as crb_Letter_Sent_Out,
  671.                 ppei1.pei_information25 as crb_Notes,
  672.                 --ppei1.pei_information17 as crb_xx17,
  673.                 --ppei1.pei_information19 as crb_xx19,
  674.                 ppei1.pei_information26 as crb_List99ISAClear,
  675.                 ppei1.pei_information27 as crb_List99ISAClearDate,
  676.                 ppei1.pei_information28 as crb_CPAccess,
  677.                 ppei1.pei_information29 as crb_CPAccessDate,
  678.                 ppei1.pei_information30 as crb_CPAdminNotified
  679.             from hr.per_people_extra_info ppei1
  680.             where ppei1.information_type = 'GC_PRE_EMPLOYMENT_CRB_CHECK'
  681.             and ppei1.person_extra_info_id = (
  682.                 select max(ppei2.person_extra_info_id)
  683.                 from hr.per_people_extra_info ppei2
  684.                 where ppei2.information_type = 'GC_PRE_EMPLOYMENT_CRB_CHECK'
  685.                 and ppei1.person_id=ppei2.person_id
  686.             )      
  687.         ) ppei_crb on ppei_crb.person_id = paaf.person_id
  688.         left join ( --Index set recomended
  689.             SELECT
  690.                 purf.ROW_LOW_RANGE_OR_NAME as contract_type,
  691.                 pucif.value as full_time_hours,
  692.                 purf.business_group_id
  693.             FROM hr.PAY_USER_ROWS_F purf
  694.             left join (
  695.                 SELECT
  696.                     pucif1.user_row_id,
  697.                     pucif1.VALUE
  698.                 FROM hr.PAY_USER_COLUMN_INSTANCES_F pucif1
  699.                 inner join (
  700.                     select *
  701.                     from hr.pay_user_columns
  702.                     where user_column_name='Contract Hours'
  703.                     and user_table_id=77
  704.                 ) puc on puc.business_group_id=pucif1.business_group_id
  705.                      and puc.user_column_id=pucif1.user_column_id
  706.                 WHERE  ( pucif1.LEGISLATION_CODE IS NULL OR pucif1.LEGISLATION_CODE = 'GB' )
  707.                 and xxgceul_context_pkg.show_context_date ('Effective Date')
  708.                              BETWEEN pucif1.effective_start_date
  709.                                  AND pucif1.effective_end_date
  710.             ) pucif on pucif.USER_ROW_ID=purf.user_row_id
  711.             WHERE ( LEGISLATION_CODE IS NULL OR LEGISLATION_CODE = 'GB' )
  712.             and purf.user_table_id=77
  713.             and xxgceul_context_pkg.show_context_date ('Effective Date')
  714.                          BETWEEN purf.effective_start_date
  715.                              AND purf.effective_end_date
  716.         ) fthlu on fthlu.contract_type = paa.contract_type
  717.                and fthlu.business_group_id=paa.business_group_id
  718.         left join ( --Optermised but no improvement
  719.                 select paaf_i2.assignment_id, papf_i3.full_name, assignment_number
  720.                 from hr.per_all_assignments_f paaf_i2
  721.                 left join (
  722.                     select person_id, full_name
  723.                     from hr.per_all_people_f papf_i2
  724.                     where effective_start_date = (
  725.                         select max(papf_i1.effective_start_date)
  726.                         from hr.per_all_people_f papf_i1
  727.                         where papf_i1.person_id=papf_i2.person_id
  728.                     )
  729.                 ) papf_i3 on paaf_i2.person_id=papf_i3.person_id
  730.                 where assignment_number is not null
  731.                 and paaf_i2.effective_start_date = (
  732.                     select max(paaf_i1.effective_start_date)
  733.                     from hr.per_all_assignments_f paaf_i1
  734.                     where assignment_number is not null
  735.                     and paaf_i1.assignment_id=paaf_i2.assignment_id
  736.                 )
  737.         ) sup_v on sup_v.assignment_id=paaf.supervisor_assignment_id
  738.         left join ( --Optermised but no improvement
  739.             select
  740.                 pg1.grade_id,
  741.                 pg1.name as Grade_Name,
  742.                 pgd1.segment1 as Grade,
  743.                 pgd1.segment2 as Grade_Description,
  744.                 pgd1.segment3 as Grade_TPA,
  745.                 pg1.attribute1 as Grade_Band
  746.             from per_grades pg1
  747.             left join (
  748.                 select *
  749.                 from hr.per_grade_definitions
  750.             ) pgd1 on pgd1.grade_definition_id=pg1.grade_definition_id
  751.             where xxgceul_context_pkg.show_context_date ('Effective Date') between nvl(pg1.date_from,TO_DATE('01-JAN-1951','DD-MON-YYYY')) and nvl(pg1.Date_to,TO_DATE('31-DEC-4712','DD-MON-YYYY'))
  752.         ) pg on pg.grade_id=paaf.grade_id
  753.         left join ( --Optermised but further optimization possible
  754.             select /*+ USE_MERGE(PPS,PGSF,PSP1,PSPS1,PSPP1) */ pspp1.ASSIGNMENT_ID,
  755.                    psp1.SPINAL_POINT,
  756.                    pgsf.spinal_point as Ceiling_Spinal_Point,
  757.                    psps1.grade_spine_id,
  758.                    pspp1.auto_increment_flag,
  759.                    pps.name as parent_spine
  760.               from PER_SPINAL_POINT_PLACEMENTS_f pspp1
  761.                    inner join per_spinal_point_steps_f psps1
  762.                       on pspp1.STEP_ID = psps1.STEP_ID
  763.                    inner join per_spinal_points psp1
  764.                       on psps1.spinal_point_id = psp1.spinal_point_id
  765.                    left join (select pgsf1.grade_spine_id,
  766.                                      psp2.SPINAL_POINT,
  767.                                      psp2.PARENT_SPINE_ID
  768.                                 from per_grade_spines_f pgsf1,
  769.                                      per_spinal_point_steps_f psps2,
  770.                                      per_spinal_points psp2
  771.                                WHERE xxgceul_context_pkg.show_context_date ('Effective Date') between pgsf1.effective_start_date and pgsf1.effective_end_date
  772.                                  AND psps2.spinal_point_id = psp2.spinal_point_id
  773.                                  AND pgsf1.ceiling_step_id = psps2.STEP_ID) pgsf
  774.                      on pgsf.grade_spine_id = psps1.grade_spine_id
  775.                    left join (select *
  776.                                 from per_parent_spines) pps
  777.                      on pps.parent_spine_id = pgsf.parent_spine_id
  778.              where xxgceul_context_pkg.show_context_date ('Effective Date') between pspp1.EFFECTIVE_START_DATE and pspp1.EFFECTIVE_END_DATE
  779.                and psp1.SPINAL_POINT is not null
  780.         ) pg_sp on pg_sp.assignment_id=paaf.assignment_id
  781.         left join ( --Optomised no changes
  782.             select
  783.             psp.BUSINESS_GROUP_ID,
  784.             psp.NAME as Scale,
  785.             psps.spinal_point,
  786.             pgrf.val as pg_sp_annual_value,
  787.             pgrflw.val as pg_sp_lw_annual_value
  788.             from PER_PARENT_SPINES psp
  789.             inner join (
  790.                 select
  791.                     psps1.BUSINESS_GROUP_ID,
  792.                     psps1.SPINAL_POINT_ID,
  793.                     psps1.PARENT_SPINE_ID,
  794.                     psps1.SEQUENCE,
  795.                     psps1.SPINAL_POINT
  796.                 from PER_SPINAL_POINTS psps1
  797.             ) psps on psp.business_group_id=psps.business_group_id and psp.parent_spine_id=psps.parent_spine_id
  798.             left join (
  799.                 select
  800.                     pgrf1.BUSINESS_GROUP_ID,
  801.                     pgrf1.GRADE_OR_SPINAL_POINT_ID,
  802.                     pgrf1.CURRENCY_CODE,
  803.                     sum(pgrf1.VALUE) as val
  804.                 from apps.PAY_GRADE_RULES_F pgrf1
  805.                 inner join (
  806.                     select rate_id, name
  807.                     from hr.pay_rates
  808.                     where name not like 'LW%'
  809.                 ) pr on pr.rate_id=pgrf1.rate_id
  810.                 where pgrf1.RATE_TYPE='SP'
  811.                 and xxgceul.xxgceul_context_pkg.show_context_date ('Effective Date') BETWEEN nvl(pgrf1.Effective_start_date,TO_DATE('01-JAN-1951','DD-MON-YYYY')) AND nvl(pgrf1.Effective_end_date,TO_DATE('01-JAN-4712','DD-MON-YYYY'))
  812.                 group by pgrf1.BUSINESS_GROUP_ID,pgrf1.GRADE_OR_SPINAL_POINT_ID,pgrf1.CURRENCY_CODE
  813.             ) pgrf on pgrf.business_group_id=psp.business_group_id and pgrf.grade_or_spinal_point_id=psps.spinal_point_id
  814.             left join (
  815.                 select
  816.                     pgrf1.BUSINESS_GROUP_ID,
  817.                     pgrf1.GRADE_OR_SPINAL_POINT_ID,
  818.                     pgrf1.CURRENCY_CODE,
  819.                     sum(pgrf1.VALUE) as val
  820.                 from apps.PAY_GRADE_RULES_F pgrf1
  821.                 inner join (
  822.                     select rate_id, name
  823.                     from hr.pay_rates
  824.                     where name like 'LW%'
  825.                 ) pr on pr.rate_id=pgrf1.rate_id
  826.                 where pgrf1.RATE_TYPE='SP'
  827.                 and xxgceul.xxgceul_context_pkg.show_context_date ('Effective Date') BETWEEN nvl(pgrf1.Effective_start_date,TO_DATE('01-JAN-1951','DD-MON-YYYY')) AND nvl(pgrf1.Effective_end_date,TO_DATE('01-JAN-4712','DD-MON-YYYY'))
  828.                 group by pgrf1.BUSINESS_GROUP_ID,pgrf1.GRADE_OR_SPINAL_POINT_ID,pgrf1.CURRENCY_CODE
  829.             ) pgrflw on pgrflw.business_group_id=psp.business_group_id and pgrflw.grade_or_spinal_point_id=psps.spinal_point_id
  830.         ) rates on rates.BUSINESS_GROUP_ID=paaf.business_Group_id and rates.scale=pg_sp.parent_spine and rates.spinal_point=pg_sp.spinal_point
  831.         left join (
  832.             select
  833.                 ppp.assignment_id,
  834.                 ppp.approved,
  835.                 ppp.proposed_salary_n,
  836.                 ppp.change_date,
  837.                 ppp.date_to
  838.             from hr.per_pay_proposals ppp
  839.             where xxgceul.xxgceul_context_pkg.show_context_date ('Effective Date') BETWEEN ppp.change_date and ppp.date_to
  840.             and ppp.approved = 'Y'        
  841.         ) spot on spot.assignment_id=paaf.assignment_id
  842.         left join ( --Optomised no changes
  843.             select
  844.                 pa1.person_id,
  845.                 pa1.address_line1,
  846.                 pa1.address_line2,
  847.                 pa1.address_line3,
  848.                 ft.country,
  849.                 pa1.postal_code,
  850.                 hrlat.meaning as address_type
  851.             from hr.per_addresses pa1
  852.             left join (
  853.                 select ft1.TERRITORY_CODE, ft1.TERRITORY_SHORT_NAME as country
  854.                 from apps.fnd_territories_tl ft1
  855.             ) ft on ft.territory_code=pa1.country
  856.             left join (
  857.                 select lookup_code, meaning
  858.                 from hr_lookups
  859.                 where lookup_type='ADDRESS_TYPE'
  860.             ) hrlat on hrlat.lookup_code=pa1.address_type
  861.             where xxgceul_context_pkg.show_context_date ('ADD Effective Date') between nvl(pa1.date_from,TO_DATE('01-JAN-1951','DD-MON-YYYY')) and nvl(pa1.Date_to,TO_DATE('31-DEC-4712','DD-MON-YYYY'))
  862.             and Primary_flag='Y'
  863.         ) pa on pa.person_id=paaf.person_id
  864.         left join ( --Optomised no changes
  865.             select
  866.                 peef.assignment_id,
  867.                 lbg_p.screen_entry_value as lbg_p,
  868.                 apps.fnd_date.CANONICAL_TO_DATE(lbg_s.screen_entry_value) as lbg_s,
  869.                 apps.fnd_date.CANONICAL_TO_DATE(lbg_e.screen_entry_value) as lbg_e,
  870.                 lbga_p.screen_entry_value as lbga_p,
  871.                 apps.fnd_date.CANONICAL_TO_DATE(lbga_s.screen_entry_value) as lbga_s,
  872.                 apps.fnd_date.CANONICAL_TO_DATE(lbga_e.screen_entry_value) as lbga_e,
  873.                 lpf_p.screen_entry_value as lpf_p,
  874.                 apps.fnd_date.CANONICAL_TO_DATE(lpf_s.screen_entry_value) as lpf_s,
  875.                 apps.fnd_date.CANONICAL_TO_DATE(lpf_e.screen_entry_value) as lpf_e,
  876.                 t_p.screen_entry_value as t_p,
  877.                 apps.fnd_date.CANONICAL_TO_DATE(t_s.screen_entry_value) as t_s,
  878.                 apps.fnd_date.CANONICAL_TO_DATE(t_e.screen_entry_value) as t_e,
  879.                 c_p.screen_entry_value as c_p,
  880.                 apps.fnd_date.CANONICAL_TO_DATE(c_s.screen_entry_value) as c_s,
  881.                 apps.fnd_date.CANONICAL_TO_DATE(c_e.screen_entry_value) as c_e
  882.             from hr.pay_element_entries_f peef
  883.             left join (
  884.                 select peevf1.element_entry_id, peevf1.screen_entry_value
  885.                 from hr.pay_element_entry_values_f peevf1
  886.                 where xxgceul_context_pkg.show_context_date ('Effective Date') between peevf1.effective_start_date and peevf1.effective_end_date
  887.                 and peevf1.input_value_id=decode(apps.hr_general.get_business_group_id,4194,14436,11592)
  888.             ) lbg_p on lbg_p.element_entry_id=peef.element_entry_id
  889.             left join (
  890.                 select peevf1.element_entry_id, peevf1.screen_entry_value from hr.pay_element_entry_values_f peevf1 where xxgceul_context_pkg.show_context_date ('Effective Date') between peevf1.effective_start_date and peevf1.effective_end_date and peevf1.input_value_id=11593
  891.             ) lbg_s on lbg_s.element_entry_id=peef.element_entry_id
  892.             left join (
  893.                 select peevf1.element_entry_id, peevf1.screen_entry_value from hr.pay_element_entry_values_f peevf1 where xxgceul_context_pkg.show_context_date ('Effective Date') between peevf1.effective_start_date and peevf1.effective_end_date and peevf1.input_value_id=11594
  894.             ) lbg_e on lbg_e.element_entry_id=peef.element_entry_id
  895.             --
  896.             left join (
  897.                 select peevf1.element_entry_id, peevf1.screen_entry_value from hr.pay_element_entry_values_f peevf1 where xxgceul_context_pkg.show_context_date ('Effective Date') between peevf1.effective_start_date and peevf1.effective_end_date and peevf1.input_value_id=11595
  898.             ) lbga_p on lbga_p.element_entry_id=peef.element_entry_id
  899.             left join (
  900.                 select peevf1.element_entry_id, peevf1.screen_entry_value from hr.pay_element_entry_values_f peevf1 where xxgceul_context_pkg.show_context_date ('Effective Date') between peevf1.effective_start_date and peevf1.effective_end_date and peevf1.input_value_id=11596
  901.             ) lbga_s on lbga_s.element_entry_id=peef.element_entry_id
  902.             left join (
  903.                 select peevf1.element_entry_id, peevf1.screen_entry_value from hr.pay_element_entry_values_f peevf1 where xxgceul_context_pkg.show_context_date ('Effective Date') between peevf1.effective_start_date and peevf1.effective_end_date and peevf1.input_value_id=11597
  904.             ) lbga_e on lbga_e.element_entry_id=peef.element_entry_id
  905.             --
  906.             left join (
  907.                 select peevf1.element_entry_id, peevf1.screen_entry_value from hr.pay_element_entry_values_f peevf1 where xxgceul_context_pkg.show_context_date ('Effective Date') between peevf1.effective_start_date and peevf1.effective_end_date and peevf1.input_value_id=11598
  908.             ) lpf_p on lpf_p.element_entry_id=peef.element_entry_id
  909.             left join (
  910.                 select peevf1.element_entry_id, peevf1.screen_entry_value from hr.pay_element_entry_values_f peevf1 where xxgceul_context_pkg.show_context_date ('Effective Date') between peevf1.effective_start_date and peevf1.effective_end_date and peevf1.input_value_id=11599
  911.             ) lpf_s on lpf_s.element_entry_id=peef.element_entry_id
  912.             left join (
  913.                 select peevf1.element_entry_id, peevf1.screen_entry_value from hr.pay_element_entry_values_f peevf1 where xxgceul_context_pkg.show_context_date ('Effective Date') between peevf1.effective_start_date and peevf1.effective_end_date and peevf1.input_value_id=11600
  914.             ) lpf_e on lpf_e.element_entry_id=peef.element_entry_id
  915.             --
  916.             left join (
  917.                 select peevf1.element_entry_id, peevf1.screen_entry_value from hr.pay_element_entry_values_f peevf1 where xxgceul_context_pkg.show_context_date ('Effective Date') between peevf1.effective_start_date and peevf1.effective_end_date and peevf1.input_value_id=11601
  918.             ) t_p on t_p.element_entry_id=peef.element_entry_id
  919.             left join (
  920.                 select peevf1.element_entry_id, peevf1.screen_entry_value from hr.pay_element_entry_values_f peevf1 where xxgceul_context_pkg.show_context_date ('Effective Date') between peevf1.effective_start_date and peevf1.effective_end_date and peevf1.input_value_id=11602
  921.             ) t_s on t_s.element_entry_id=peef.element_entry_id
  922.             left join (
  923.                 select peevf1.element_entry_id, peevf1.screen_entry_value from hr.pay_element_entry_values_f peevf1 where xxgceul_context_pkg.show_context_date ('Effective Date') between peevf1.effective_start_date and peevf1.effective_end_date and peevf1.input_value_id=11603
  924.             ) t_e on t_e.element_entry_id=peef.element_entry_id
  925.             --
  926.             left join (
  927.                 select peevf1.element_entry_id, peevf1.screen_entry_value from hr.pay_element_entry_values_f peevf1 where xxgceul_context_pkg.show_context_date ('Effective Date') between peevf1.effective_start_date and peevf1.effective_end_date and peevf1.input_value_id=11604
  928.             ) c_p on c_p.element_entry_id=peef.element_entry_id
  929.             left join (
  930.                 select peevf1.element_entry_id, peevf1.screen_entry_value from hr.pay_element_entry_values_f peevf1 where xxgceul_context_pkg.show_context_date ('Effective Date') between peevf1.effective_start_date and peevf1.effective_end_date and peevf1.input_value_id=11605
  931.             ) c_s on c_s.element_entry_id=peef.element_entry_id
  932.             left join (
  933.                 select peevf1.element_entry_id, peevf1.screen_entry_value from hr.pay_element_entry_values_f peevf1 where xxgceul_context_pkg.show_context_date ('Effective Date') between peevf1.effective_start_date and peevf1.effective_end_date and peevf1.input_value_id=11606
  934.             ) c_e on c_e.element_entry_id=peef.element_entry_id
  935.             --
  936.             where peef.element_type_id=decode(apps.hr_general.get_business_group_id,4194,5515,5113)
  937.             and xxgceul_context_pkg.show_context_date ('Effective Date') between peef.effective_start_date and peef.effective_end_date
  938.         ) pen_ele on pen_ele.assignment_id=paaf.assignment_id
  939.         left join (
  940.             select
  941.                 pppmf.assignment_id,
  942.                 pea.sort_code,
  943.                 pea.bank_name,
  944.                 pea.bank_branch,
  945.                 pea.Account_number,
  946.                 pea.Account_Name,
  947.                 pea.Account_Type,
  948.                 pea.BID_Soc_Acc_No,
  949.                 pea.Bank_Branc_Loc,
  950.                 popmf.org_payment_method_name,
  951.                 pppmf.percentage,
  952.                 pppmf.PO_Number,
  953.                 pppmf.Overseas_Payment_Ind,
  954.                 pppmf.bank_Context
  955.             from (
  956.                 select
  957.                     pppmf1.assignment_id,
  958.                     pppmf1.percentage,
  959.                     pppmf1.org_payment_method_id,
  960.                     pppmf1.external_account_id,
  961.                     pppmf1.effective_start_date,
  962.                     pppmf1.effective_end_date,
  963.                     case when nvl(pppmf1.attribute_category,'_')='GC_PO_NUMBER' then
  964.                         pppmf1.attribute1
  965.                     else
  966.                         null
  967.                     end as PO_Number,
  968.                     case when nvl(pppmf1.attribute_category,'_')='GC_OVERSEAS_PAYMENT_IND' then
  969.                         pppmf1.attribute2
  970.                     else
  971.                         null
  972.                     end as Overseas_Payment_Ind,
  973.                     decode(nvl(pppmf1.attribute_category,'_'),
  974.                         'GC_OVERSEAS_PAYMENT_IND','Overseas Payment Indicator',
  975.                         'GC_PO_NUMBER','Post Office Number'
  976.                     ) as bank_Context
  977.                 from hr.pay_personal_payment_methods_f pppmf1
  978.                 where ((personal_payment_method_id < 33958)
  979.                 OR (personal_payment_method_id > 33958)) --Ignore this one as it was an early error
  980.                                                       --CEUL Health also ignroes this record
  981.             ) pppmf
  982.             left join (
  983.                 select
  984.                     pea1.external_account_id,
  985.                     pea1.segment3 as sort_code,
  986.                     pea1.segment1 as bank_name,
  987.                     pea1.segment2 as bank_branch,
  988.                     pea1.segment4 as Account_number,
  989.                     pea1.segment5 as Account_Name,
  990.                     pea1.segment6 as Account_Type,
  991.                     pea1.segment7 as BID_Soc_Acc_No,
  992.                     pea1.segment8 as Bank_Branc_Loc
  993.                 from hr.pay_external_accounts pea1
  994.             ) pea on pea.external_account_id=pppmf.external_account_id
  995.             left join (
  996.                 select popmf1.org_payment_method_id, popmf1.org_payment_method_name
  997.                 from hr.PAY_ORG_PAYMENT_METHODS_f popmf1
  998.                 where xxgceul_context_pkg.show_context_date ('Effective Date') between popmf1.effective_start_date and popmf1.effective_end_date
  999.             ) popmf on popmf.org_payment_method_id=pppmf.org_payment_method_id
  1000.             where xxgceul_context_pkg.show_context_date ('Effective Date') between pppmf.effective_start_date and pppmf.effective_end_date
  1001.         ) bank on paaf.assignment_id=bank.assignment_id
  1002.         left join (
  1003.             select
  1004.                 peef.assignment_id,
  1005.                 peef.effective_start_date,
  1006.                 peef.effective_end_date,
  1007.                 peevf_code.screen_entry_value as Tax_Code,
  1008.                 peevf_basis.screen_entry_value as Tax_Basis,
  1009.                 peevf_PayPrev.screen_entry_value as Tax_PayPrev,
  1010.                 peevf_Prev.screen_entry_value as Tax_Prev,
  1011.                 peevf_Refundable.screen_entry_value as Tax_Refundable,
  1012.                 peevf_Authority.screen_entry_value as Tax_Authority
  1013.             from pay_element_entries_f peef
  1014.             inner join (
  1015.                 --This join will restrict element to PAY Basis only and look up all the input value codes
  1016.                 select /*+ NO_USE_NL(PIVF_PROCESS) */
  1017.                     petf.element_type_id,
  1018.                     pivf.input_value_id as input_value_id_tax_code,
  1019.                     pivf_basis.input_value_id as input_value_id_tax_basis,
  1020.                     pivf_PayPrev.input_value_id as input_value_id_Tax_PayPrev,
  1021.                     pivf_Prev.input_value_id as input_value_id_Tax_Prev,
  1022.                     pivf_Refundable.input_value_id as input_value_id_Tax_Refundable,
  1023.                     pivf_Authority.input_value_id as input_value_id_Tax_Authority
  1024.                 from (
  1025.                     select *
  1026.                     from pay_element_types_f
  1027.                     where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1028.                     and element_name like 'PAYE Details'
  1029.                 ) petf
  1030.                 left join (
  1031.                     select *
  1032.                     from pay_input_values_f
  1033.                     where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1034.                     and name like 'Tax Code'
  1035.                 ) pivf on pivf.element_type_id=petf.element_type_id
  1036.                 left join (
  1037.                     select *
  1038.                     from pay_input_values_f
  1039.                     where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1040.                     and name like 'Tax Basis'
  1041.                 ) pivf_basis on pivf_basis.element_type_id=petf.element_type_id
  1042.                 left join (
  1043.                     select *
  1044.                     from pay_input_values_f
  1045.                     where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1046.                     and name like 'Pay Previous'
  1047.                 ) pivf_PayPrev on pivf_PayPrev.element_type_id=petf.element_type_id
  1048.                 left join (
  1049.                     select *
  1050.                     from pay_input_values_f
  1051.                     where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1052.                     and name like 'Tax Previous'
  1053.                 ) pivf_Prev on pivf_Prev.element_type_id=petf.element_type_id
  1054.                 left join (
  1055.                     select *
  1056.                     from pay_input_values_f
  1057.                     where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1058.                     and name like 'Refundable'
  1059.                 ) pivf_Refundable on pivf_Refundable.element_type_id=petf.element_type_id
  1060.                 left join (
  1061.                     select *
  1062.                     from pay_input_values_f
  1063.                     where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1064.                     and name like 'Authority'
  1065.                 ) pivf_Authority on pivf_Authority.element_type_id=petf.element_type_id  
  1066.             ) element_info on element_info.element_type_id=peef.element_type_id
  1067.             left join (
  1068.                 select input_value_id, element_entry_id, screen_entry_value
  1069.                 from pay_element_entry_values_f
  1070.                 where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1071.             ) peevf_code on peevf_code.element_entry_id=peef.element_entry_id and element_info.input_value_id_tax_code=peevf_code.input_value_id        
  1072.             left join (
  1073.                 select input_value_id, element_entry_id, screen_entry_value
  1074.                 from pay_element_entry_values_f
  1075.                 where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1076.             ) peevf_basis on peevf_basis.element_entry_id=peef.element_entry_id and element_info.input_value_id_tax_basis=peevf_basis.input_value_id        
  1077.             left join (
  1078.                 select input_value_id, element_entry_id, screen_entry_value
  1079.                 from pay_element_entry_values_f
  1080.                 where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1081.             ) peevf_PayPrev on peevf_PayPrev.element_entry_id=peef.element_entry_id and element_info.input_value_id_Tax_PayPrev=peevf_PayPrev.input_value_id        
  1082.             left join (
  1083.                 select input_value_id, element_entry_id, screen_entry_value
  1084.                 from pay_element_entry_values_f
  1085.                 where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1086.             ) peevf_Prev on peevf_Prev.element_entry_id=peef.element_entry_id and element_info.input_value_id_Tax_Prev=peevf_Prev.input_value_id        
  1087.             left join (
  1088.                 select input_value_id, element_entry_id, screen_entry_value
  1089.                 from pay_element_entry_values_f
  1090.                 where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1091.             ) peevf_Refundable on peevf_Refundable.element_entry_id=peef.element_entry_id and element_info.input_value_id_Tax_Refundable=peevf_Refundable.input_value_id        
  1092.             left join (
  1093.                 select input_value_id, element_entry_id, screen_entry_value
  1094.                 from pay_element_entry_values_f
  1095.                 where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1096.             ) peevf_Authority on peevf_Authority.element_entry_id=peef.element_entry_id and element_info.input_value_id_Tax_Authority=peevf_Authority.input_value_id        
  1097.             where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1098.             --************LINE TO REMOVE DATA ERRORS
  1099.             --****THESE ARE TERMINATED EMPLOYEES WITH MUTIPLE PAYE Details records
  1100.             --****This causes duplicates so PAYE Details (Tax Code and Tab Basis) not looked up                    
  1101.             and assignment_id not in (44440,15770,17709,46897,47089,34241,23694,31847,44522)
  1102.             --******END OF DATA ERROR REMOVAL LINES
  1103.         ) ev_pd on ev_pd.assignment_id=paaf.assignment_id
  1104.         left join (
  1105.             select
  1106.                 peef.assignment_id,
  1107.                 peef.effective_start_date,
  1108.                 peef.effective_end_date,
  1109.                 peevf_cat.screen_entry_value as NI_Cat,
  1110.                 peevf_cert.screen_entry_value as NI_Cert,
  1111.                 peevf_pen.screen_entry_value as NI_Pension,
  1112.                 peevf_process.screen_entry_value as NI_Process
  1113.             from pay_element_entries_f peef
  1114.             inner join (
  1115.                 --This join will restrict element to NI only and look up all the ni input value codes
  1116.                 select /*+ NO_USE_NL(PIVF_PROCESS) */
  1117.                     petf.element_type_id,
  1118.                     pivf.input_value_id as input_value_id_cat,
  1119.                     pivf_cert.input_value_id as input_value_id_cert,
  1120.                     pivf_pen.input_value_id as input_value_id_pen,
  1121.                     pivf_process.input_value_id as input_value_id_process
  1122.                 from (
  1123.                     select *
  1124.                     from pay_element_types_f
  1125.                     where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1126.                     and element_name like 'NI'
  1127.                 ) petf
  1128.                 left join (
  1129.                     select *
  1130.                     from pay_input_values_f
  1131.                     where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1132.                     and name like 'Category'
  1133.                 ) pivf on pivf.element_type_id=petf.element_type_id
  1134.                 left join (
  1135.                     select *
  1136.                     from pay_input_values_f
  1137.                     where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1138.                     and name like 'Certificate'
  1139.                 ) pivf_cert on pivf_cert.element_type_id=petf.element_type_id
  1140.                 left join (
  1141.                     select *
  1142.                     from pay_input_values_f
  1143.                     where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1144.                     and name like 'Pension'
  1145.                 ) pivf_pen on pivf_pen.element_type_id=petf.element_type_id
  1146.                 left join (
  1147.                     select *
  1148.                     from pay_input_values_f
  1149.                     where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1150.                     and name like 'Process Type'
  1151.                 ) pivf_process on pivf_process.element_type_id=petf.element_type_id
  1152.             ) element_info on element_info.element_type_id=peef.element_type_id
  1153.             left join (
  1154.                 select input_value_id, element_entry_id, screen_entry_value
  1155.                 from pay_element_entry_values_f
  1156.                 where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1157.             ) peevf_cat on peevf_cat.element_entry_id=peef.element_entry_id and element_info.input_value_id_cat=peevf_cat.input_value_id        
  1158.             left join (
  1159.                 select input_value_id, element_entry_id, screen_entry_value
  1160.                 from pay_element_entry_values_f
  1161.                 where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1162.             ) peevf_cert on peevf_cert.element_entry_id=peef.element_entry_id and element_info.input_value_id_cert=peevf_cert.input_value_id        
  1163.             left join (
  1164.                 select input_value_id, element_entry_id, screen_entry_value
  1165.                 from pay_element_entry_values_f
  1166.                 where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1167.             ) peevf_pen on peevf_pen.element_entry_id=peef.element_entry_id and element_info.input_value_id_pen=peevf_pen.input_value_id        
  1168.             left join (
  1169.                 select input_value_id, element_entry_id, screen_entry_value
  1170.                 from pay_element_entry_values_f
  1171.                 where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1172.             ) peevf_process on peevf_process.element_entry_id=peef.element_entry_id and element_info.input_value_id_process=peevf_process.input_value_id        
  1173.             where xxgceul_context_pkg.show_context_date ('Effective Date') between effective_start_date and effective_end_date
  1174.             --************LINE TO REMOVE DATA ERRORS
  1175.             --****This is employee number 66661 who has 2 NI elements live on 30-nov-2007
  1176.             --****This causes duplicates so NI not looked up for this employee                    
  1177.             and assignment_id not in (31847)
  1178.             --******END OF DATA ERROR REMOVAL LINES
  1179.         ) ev_nic on ev_nic.assignment_id=paaf.assignment_id  
  1180.         left join (
  1181.             SELECT
  1182.                 ffv.flex_value,
  1183.                 ffv.flex_value_meaning,
  1184.                 ffv.description,
  1185.                 101 as business_group_id
  1186.             FROM apps.FND_FLEX_VALUES_VL ffv
  1187.             left join apps.fnd_flex_value_sets ffvs ON ffvs.flex_value_set_id=ffv.flex_value_set_id
  1188.             WHERE ffvs.flex_value_set_name='GC_PENSION_SCHEME'
  1189.             AND ffv.enabled_flag='Y'
  1190.         ) pen_sch_des on pen_sch_des.flex_value = ppg.segment2
  1191.                      and pen_sch_des.business_group_id = paaf.business_group_id
  1192.         left join (
  1193.             SELECT
  1194.                 ffv.flex_value,
  1195.                 ffv.flex_value_meaning,
  1196.                 ffv.description,
  1197.                 4194 as business_group_id
  1198.             FROM apps.FND_FLEX_VALUES_VL ffv
  1199.             left join apps.fnd_flex_value_sets ffvs ON ffvs.flex_value_set_id=ffv.flex_value_set_id
  1200.             WHERE ffvs.flex_value_set_name='GSP_PENSION_SCHEME'
  1201.             AND ffv.enabled_flag='Y'
  1202.         ) pen_sch_des_gsp on pen_sch_des_gsp.flex_value = ppg.segment2
  1203.                          and pen_sch_des_gsp.business_group_id = paaf.business_group_id
  1204.     ) so_far
  1205.     --WHERE so_far.business_group_id = nvl(so_far.business_group_id,apps.hr_general.get_business_group_id)
  1206.     WHERE so_far.business_group_id = apps.hr_general.get_business_group_id
  1207. ) so_far2

Search

mas Greenwich EUL

Tags

RJM Article Type
Quick Reference