Oracle ERP Professionals

Select
--       (select name from v$database) "Instance",
--        pca.segment2 "CC Code",
--        papf.person_id per_id ,paaf.assignment_id asg_id ,paaf.organization_id org_id ,paaf.payroll_id,
    papf.employee_number as "Employee ID"
--        ,paaf.assignment_number
--        ,(select name from apps.per_pay_bases ppb where paaf.pay_basis_id = ppb.pay_basis_id) "Pay Basis"
--        ,papf.title "Title"
--        ,NVL(initcap(replace(substr(papf.email_address,0,instr(papf.email_address,'@')-1),'.',' ')),initcap(rtrim(papf.known_as))) "Preferred Name"
--        ,initcap(rtrim(papf.known_as)) as "Preferred Name"
--        ,initcap(rtrim(papf.full_name,'-')) as "Employee Name"
    ,initcap(rtrim(nvl(papf.known_as,papf.full_name),'-')) as "Employee Name"
--        ,papf.first_name "First Name"
--        ,papf.middle_names "Middle Name"
--        ,papf.last_name "Last Name"
--        ,papf.registered_disabled_flag "Registered Disabled Flag"
    ,ppos.date_start "Joining Date"
--        ,apps.hr_general.decode_lookup('SEX',papf.sex) "Gender"
--        ,apps.hr_general.decode_lookup('MAR_STATUS',papf.marital_status) as "Marital Status"
--        ,apps.hr_general.decode_lookup('AE_RELIGION',papf.per_information10) as "Religion"
--        ,apps.hr_general.decode_lookup('AE_NATIONALITY',papf.per_information12)"Country"
--        ,apps.hr_general.decode_lookup('AE_NATIONALITY',papf.per_information18) as "Nationality"
--        ,papf.region_of_birth as "Point of Origin"
--       ,papf.date_of_birth "Date of Birth"
--       ,round(months_between(trunc(sysdate),papf.date_of_birth)/12) "Age"  
--       ,papf.town_of_birth "Town of Birth"  ,papf.country_of_birth "Country of Birth"
--       ,papf.email_address as "Email Address"
--       ,papf.previous_last_name "Previous Last Name"
--       ,papf.internal_location "Location"
--       ,apps.hr_general.get_phone_number(papf.person_id,'W1',TRUNC(SYSDATE)) "Work No"
--       ,apps.hr_general.get_phone_number(papf.person_id,'M',TRUNC(SYSDATE)) "Mobile No"
       --,pg.name "Grade"
--       ,pj.name "Job"
--       ,pap.name as "Position"
--       ,substr(pap.name,instr(pap.name,'.',1,1)+1,4) pos_cc_code    
--       ,substr(pap.name,instr(pap.name,'.',1,2)+1) "Position"
--       ,ppf.payroll_name as "Payroll"
--       ,paaf.ass_attribute4 "Remarks"
--       ,paaf.ass_attribute1 "Excep Leave Entitlement"
--       ,paaf.effective_start_date "Eff Start Date"
--       ,paaf.assignment_status_type_id asg_typ_id
--       ,past.user_status as "Assignment Status"
--       ,apps.hr_general.decode_lookup('AE_EDUCATION_LEVEL',papf.per_information11) "Education Level"
--       ,nvl(apps.fnd_date.canonical_to_date(ppos.attribute1),ppos.actual_termination_date) "Leaving Date"
--       ,ppos.last_standard_process_date "LSP Date"
--       ,replace(substr(pex.email_address,0,instr(pex.email_address,'@')-1),'.',' ') "Supervisor Name"
,c.PROPOSED_SALARY_n Basic_Salary
,c.CHANGE_DATE
--,d.APPROVED Sal_Component_status
,apps.hr_general.decode_lookup('PER_SAL_PROPOSAL_STATUS', d.APPROVED) AS SALARY_PROPOSAL_STATUS
--,d.Component_Reason
,apps.hr_general.decode_lookup('PROPOSAL_REASON',d.Component_Reason) COMPONENT_REASON_MEANING
,d.change_amount_n Change_Amount
,d.change_percentage
from   apps.per_all_people_f             papf
      ,apps.per_all_assignments_f        paaf
      ,apps.per_business_groups          pbg
      ,apps.hr_all_organization_units    haou
      ,apps.per_periods_of_service       ppos
      ,apps.per_assignment_status_types  past
      ,apps.pay_cost_allocation_keyflex  pca
      ,apps.pay_payrolls_f               ppf
      ,apps.per_grades                   pg
      ,apps.per_all_positions            pap
      ,apps.pER_PAY_pROposals             c,
       apps.per_pay_proposal_components   d
--      ,apps.per_position_definitions     ppd
--      ,apps.per_jobs                     pj
--      ,apps.pay_people_groups            ppg
--      ,apps.per_employees_x              pex
--      ,apps.hr_soft_coding_keyflex       hsck
where papf.person_id                   = paaf.person_id
AND   paaf.ASSIGNMENT_ID = C.ASSIGNMENT_ID
and c.pay_proposal_id = d.pay_proposal_id(+)
AND c.CHANGE_DATE =
               (SELECT   MAX (d.CHANGE_DATE)
                  FROM   pER_PAY_pROposals d
                 WHERE   d.ASSIGNMENT_ID = paaf.ASSIGNMENT_ID
                         AND d.approved = 'Y')
and   paaf.primary_flag                = 'Y'
and   paaf.assignment_type             = 'E'
and   papf.current_employee_flag       = 'Y'
and   paaf.period_of_service_id        = ppos.period_of_service_id
and   pbg.business_group_id            = haou.business_group_id
and   paaf.organization_id             = haou.organization_id(+)
and   paaf.assignment_status_type_id   = past.assignment_status_type_id
and   haou.cost_allocation_keyflex_id  = pca.cost_allocation_keyflex_id(+)
and   paaf.payroll_id                  = ppf.payroll_id(+)
and   paaf.grade_id                    = pg.grade_id(+)
and   paaf.business_group_id           = pap.business_group_id(+)
and   paaf.position_id                 = pap.position_id(+)
--and   pap.position_definition_id       = ppd.position_definition_id(+)
--and   paaf.people_group_id             = ppg.people_group_id(+)
--and   paaf.job_id                      = pj.job_id(+)
--and   pex.employee_id                  = paaf.supervisor_id(+)
--and   paaf.soft_coding_keyflex_id      = hsck.soft_coding_keyflex_id(+)
and   trunc(sysdate) between papf.effective_start_date and papf.effective_end_date
and   trunc(sysdate) between paaf.effective_start_date and paaf.effective_end_date
and   trunc(sysdate) between haou.date_from and nvl(haou.date_to,'31-DEC-4712')
and   trunc(sysdate) between ppf.effective_start_date(+) and ppf.effective_end_date(+)
--and   paaf.effective_start_date <= (SELECT MAX(NVL(b2.actual_termination_date, TRUNC(SYSDATE))) FROM apps.per_periods_of_service b2 WHERE b2.person_id = paaf.person_id AND b2.date_start <= TRUNC(SYSDATE))
--and   paaf.effective_end_date   >= (SELECT MAX(NVL(b2.actual_termination_date, TRUNC(SYSDATE))) FROM apps.per_periods_of_service b2 WHERE b2.person_id = paaf.person_id AND b2.date_start <= TRUNC(SYSDATE))
--and   papf.sex = 'M'
--and   paaf.payroll_id is not null
--and   paaf.grade_id is not null
--and   to_char(ppos.date_start,'YYYY') = '2013'
--and   pg.attribute2 = 'Y'
----Query to get -----Organization and Child Organizations Details------
and   paaf.organization_id = any (
                          select e.organization_id_child
                            from per_org_structure_elements e
                           where e.org_structure_version_id = any (select org_structure_version_id
                                                                from per_org_structure_versions
                                                                where organization_structure_id = any(
                                                                select organization_structure_id
                                                                from per_organization_structures
                                                                where primary_structure_flag ='Y')
                                                                and trunc(sysdate) between date_from and nvl(date_to,trunc(sysdate)) )
                      connect by e.organization_id_parent =
                                                  prior e.organization_id_child
                             and e.org_structure_version_id = any(select org_structure_version_id
                                                                from per_org_structure_versions
                                                                where organization_structure_id = any(
                                                                select organization_structure_id
                                                                from per_organization_structures
                                                                where primary_structure_flag ='Y')
                                                                and trunc(sysdate) between date_from and nvl(date_to,trunc(sysdate)) )
                      start with e.organization_id_parent =   XX
                             and e.org_structure_version_id = any (
                                                              select org_structure_version_id
                                                              from per_org_structure_versions
                                                              where organization_structure_id = any(
                                                              select organization_structure_id
                                                              from per_organization_structures
                                                              where primary_structure_flag ='Y')
                                                              and trunc(sysdate) between date_from and nvl(date_to,trunc(sysdate)) )
                                       )
--order by paaf.last_update_date desc