Query Absence in Oracle Fusion

 SELECT distinct * FROM(

SELECT papf.person_id,

       papf.person_number,

       ppnf.display_name

       employee_name,

       pasf_Sup.manager_name

       manager_name,

       pjft.name

       job_name,

       pgft.name

       grade_name,

       --abt.absence_type_id,

       abt.name

       absence_type,

       (SELECT DISTINCT( pd.name )

        FROM   per_departments pd

        WHERE  pd.organization_id = paam.organization_id

               AND pd.effective_start_date =

                   (SELECT Max(pd1.effective_start_date)

                    FROM   per_departments pd1

                    WHERE

                   pd1.organization_id = pd.organization_id

))

       department_name,

       paam.ass_attribute2 sector,

       paam.ass_attribute1

       unit,

       To_char(apae.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

       start_date,

       To_char(apae.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

       end_date,

       apae.duration,

   apae.start_date order_by_date,

   apae.attribute1 Delegated_Person

FROM   anc_per_abs_entries apae,

       per_periods_of_service pps,

       anc_absence_types_vl abt,

       per_all_people_f papf,

       per_person_names_f ppnf,

       (SELECT ppnf2.full_name manager_name,

               pasf.person_id

        FROM   per_assignment_supervisors_f pasf,

               per_person_names_f ppnf2

        WHERE  pasf.manager_id = ppnf2.person_id

               AND pasf.manager_type = 'LINE_MANAGER'

               AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date AND

                                          pasf.effective_end_date

               AND Trunc(SYSDATE) BETWEEN ppnf2.effective_start_date AND

                                          ppnf2.effective_end_date

               AND ppnf2.name_type = 'GLOBAL') pasf_Sup,

       per_all_assignments_m paam,

       per_jobs_f_vl pjft,

       per_grades_f_vl pgft,

   hr_organization_units bu,

       hr_organization_units le,

   per_asg_responsibilities resp

   --hr_organization_units dept

WHERE  apae.period_of_service_id = pps.period_of_service_id

       AND apae.absence_type_id = abt.absence_type_id

   AND apae.ABSENCE_STATUS_CD <> 'ORA_WITHDRAWN'

   AND apae.APPROVAL_STATUS_CD = 'APPROVED'

       AND abt.name = 'Tentative Leave Plan'

       AND pps.person_id = papf.person_id

       AND papf.person_id = ppnf.person_id

       AND papf.person_id = pasf_Sup.person_id(+)

       AND papf.person_id = paam.person_id(+)

       AND paam.primary_assignment_flag(+) = 'Y'

       --AND paam.assignment_type = 'E'

       --AND paam.effective_latest_change = 'Y'

       AND paam.job_id = pjft.job_id (+)

       AND paam.grade_id = pgft.grade_id (+)

   AND paam.business_unit_id = bu.organization_id

       AND paam.legal_entity_id = le.organization_id

   AND resp.person_id = HRC_SESSION_UTIL.get_user_personid

       AND resp.responsibility_type ='HR_REP'

       AND resp.status = 'Active'

       AND  resp.legal_entity_id = paam.legal_entity_id

   --AND resp.BUSINESS_UNIT_ID=paam.BUSINESS_UNIT_ID

   --AND resp.organization_id =paam.organization_id 

       AND TRUNC(sysdate) BETWEEN resp.start_date AND NVL(resp.end_date,TRUNC(sysdate))

       AND Trunc(SYSDATE) BETWEEN pjft.effective_start_date(+) AND

                                  pjft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN pgft.effective_start_date(+) AND

                                  pgft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

                                  paam.effective_end_date

       AND Trunc(SYSDATE) BETWEEN abt.effective_start_date AND

                                  abt.effective_end_date

       AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND

                                  papf.effective_end_date

       AND Trunc(SYSDATE) BETWEEN ppnf.effective_start_date AND

                                  ppnf.effective_end_date

       AND ppnf.name_type = 'GLOBAL'

      --AND papf.person_number IN ( '0015', '0033' )

  AND to_number(Extract(year FROM apae.start_date)) = to_number(:p_date)

  

  UNION

SELECT papf.person_id,

       papf.person_number,

       ppnf.display_name

       employee_name,

       pasf_Sup.manager_name

       manager_name,

       pjft.name

       job_name,

       pgft.name

       grade_name,

       --abt.absence_type_id,

       abt.name

       absence_type,

       (SELECT DISTINCT( pd.name )

        FROM   per_departments pd

        WHERE  pd.organization_id = paam.organization_id

               AND pd.effective_start_date =

                   (SELECT Max(pd1.effective_start_date)

                    FROM   per_departments pd1

                    WHERE

                   pd1.organization_id = pd.organization_id))

       department_name,

       paam.ass_attribute2 sector,

       paam.ass_attribute1

       unit,

       To_char(apae.start_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

       start_date,

       To_char(apae.end_date, 'DD-MON-YYYY', 'NLS_DATE_LANGUAGE = AMERICAN')

       end_date,

       apae.duration,

   apae.start_date order_by_date,

   apae.attribute1 Delegated_Person

FROM   anc_per_abs_entries apae,

       per_periods_of_service pps,

       anc_absence_types_vl abt,

       per_all_people_f papf,

       per_person_names_f ppnf,

       (SELECT ppnf2.full_name manager_name,

               pasf.person_id

        FROM   per_assignment_supervisors_f pasf,

               per_person_names_f ppnf2

        WHERE  pasf.manager_id = ppnf2.person_id

               AND pasf.manager_type = 'LINE_MANAGER'

               AND Trunc(SYSDATE) BETWEEN pasf.effective_start_date AND

                                          pasf.effective_end_date

               AND Trunc(SYSDATE) BETWEEN ppnf2.effective_start_date AND

                                          ppnf2.effective_end_date

               AND ppnf2.name_type = 'GLOBAL') pasf_Sup,

       per_all_assignments_m paam,

       per_jobs_f_vl pjft,

       per_grades_f_vl pgft,

   hr_organization_units bu,

       hr_organization_units le,

   per_asg_responsibilities resp

   --hr_organization_units dept

WHERE  apae.period_of_service_id = pps.period_of_service_id

       AND apae.absence_type_id = abt.absence_type_id

   AND apae.ABSENCE_STATUS_CD <> 'ORA_WITHDRAWN'

   AND apae.APPROVAL_STATUS_CD = 'APPROVED'

       AND abt.name = 'Tentative Leave Plan'

       AND pps.person_id = papf.person_id

       AND papf.person_id = ppnf.person_id

       AND papf.person_id = pasf_Sup.person_id(+)

       AND papf.person_id = paam.person_id(+)

       AND paam.primary_assignment_flag(+) = 'Y'

       --AND paam.assignment_type = 'E'

       --AND paam.effective_latest_change = 'Y'

       AND paam.job_id = pjft.job_id (+)

       AND paam.grade_id = pgft.grade_id (+)

   AND paam.business_unit_id = bu.organization_id

       AND paam.legal_entity_id = le.organization_id

   AND resp.person_id = HRC_SESSION_UTIL.get_user_personid

       AND resp.responsibility_type IN ('XXXXXXXXXX')

       AND resp.status = 'Active'

       --AND  resp.legal_entity_id = paam.legal_entity_id

   --AND resp.BUSINESS_UNIT_ID=paam.BUSINESS_UNIT_ID

   AND resp.organization_id =paam.organization_id 

       AND TRUNC(sysdate) BETWEEN resp.start_date AND NVL(resp.end_date,TRUNC(sysdate))

       AND Trunc(SYSDATE) BETWEEN pjft.effective_start_date(+) AND

                                  pjft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN pgft.effective_start_date(+) AND

                                  pgft.effective_end_date(+)

       AND Trunc(SYSDATE) BETWEEN paam.effective_start_date AND

                                  paam.effective_end_date

       AND Trunc(SYSDATE) BETWEEN abt.effective_start_date AND

                                  abt.effective_end_date

       AND Trunc(SYSDATE) BETWEEN papf.effective_start_date AND

                                  papf.effective_end_date

       AND Trunc(SYSDATE) BETWEEN ppnf.effective_start_date AND

                                  ppnf.effective_end_date

       AND ppnf.name_type = 'GLOBAL'

      --AND papf.person_number IN ( '0015', '0033' )

  AND to_number(Extract(year FROM apae.start_date)) = to_number(:p_date)  

)

   WHERE 1=1

    AND (LEAST(:p_sector) IS NULL

OR  trim(sector) IN trim( :p_sector ))

--AND ( (coalesce(null, :p_sector) is null) or (trim(sector) in trim(:p_sector)))

AND (LEAST(:p_dept) IS NULL

OR  department_name IN ( :p_dept ))

ORDER  BY department_name,order_by_date

Query For Payroll Run Results

 SELECT 

PERSON_NUMBER,

assignment_number,

Date_of_Joining,

grade,

Job,

Position,

Department,

BU_Name,

payroll_name,

PERIOD_NAME,

DEFAULT_PAYDATE,

Order_date,

Amount,

entitle_amount,

child_name_age,

child_school_type,

child_academic_year,

element_name,

base_element_name,

reporting_name

FROM (

SELECT

(select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE

from PAY_RUN_RESULT_VALUES prrv

,PAY_RUN_RESULTS prr

    ,PAY_INPUT_VALUES_F pivf

,PAY_INPUT_VALUES_TL pivt

,PAY_REL_GROUPS_DN pasg

where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID

AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID

AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID

AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID

AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID

AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID

AND (USERENV('LANG')) = pivt.LANGUAGE 

AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE

AND pivt.NAME = 'Pay Value'

AND pivf.uom = 'M'

AND prr.element_entry_id(+) = peeF.element_entry_id

AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)aMOUNT

,(select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE

from PAY_RUN_RESULT_VALUES prrv

,PAY_RUN_RESULTS prr

    ,PAY_INPUT_VALUES_F pivf

,PAY_INPUT_VALUES_TL pivt

,PAY_REL_GROUPS_DN pasg

where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID

AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID

AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID

AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID

AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID

AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID

AND (USERENV('LANG')) = pivt.LANGUAGE 

AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE

AND pivt.NAME in ('XXXXXXXXXXXXXXX')

AND pivf.uom = 'C'

AND prr.element_entry_id(+) = peeF.element_entry_id

AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)entitle_amount

,((select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE

from PAY_RUN_RESULT_VALUES prrv

,PAY_RUN_RESULTS prr

    ,PAY_INPUT_VALUES_F pivf

,PAY_INPUT_VALUES_TL pivt

,PAY_REL_GROUPS_DN pasg

where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID

AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID

AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID

AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID

AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID

AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID

AND (USERENV('LANG')) = pivt.LANGUAGE 

AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE

AND pivt.NAME in ('XXXXXXXXXXXXXXXX')

AND pivf.uom = 'C'

AND prr.element_entry_id(+) = peeF.element_entry_id

AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)||'_'||SUBSTR(pett.element_name,20))child_name_age

,(select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE

from PAY_RUN_RESULT_VALUES prrv

,PAY_RUN_RESULTS prr

    ,PAY_INPUT_VALUES_F pivf

,PAY_INPUT_VALUES_TL pivt

,PAY_REL_GROUPS_DN pasg

where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID

AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID

AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID

AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID

AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID

AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID

AND (USERENV('LANG')) = pivt.LANGUAGE 

AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE

AND pivt.NAME in ('XXXXXXXXXXXXX')

AND pivf.uom = 'C'

AND prr.element_entry_id(+) = peeF.element_entry_id

AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID)child_school_type

,(SELECT MEANING FROM HCM_LOOKUPS WHERE LOOKUP_TYPE = 'XXXXXXXXX' AND LOOKUP_CODE = (select /*+ qb_name(RUNRESULTPVO) */ prrv.RESULT_VALUE

from PAY_RUN_RESULT_VALUES prrv

,PAY_RUN_RESULTS prr

    ,PAY_INPUT_VALUES_F pivf

,PAY_INPUT_VALUES_TL pivt

,PAY_REL_GROUPS_DN pasg

where prrv.RUN_RESULT_ID = prr.RUN_RESULT_ID

AND prrv.INPUT_VALUE_ID = pivf.INPUT_VALUE_ID

AND prr.PAYROLL_REL_ACTION_ID = ppra.PAYROLL_REL_ACTION_ID

AND pasg.RELATIONSHIP_GROUP_ID = prr.PAYROLL_ASSIGNMENT_ID

AND prr.ELEMENT_TYPE_ID = petf.ELEMENT_TYPE_ID

AND pivf.INPUT_VALUE_ID = pivt.INPUT_VALUE_ID

AND (USERENV('LANG')) = pivt.LANGUAGE 

AND ppa.EFFECTIVE_DATE BETWEEN pivf.EFFECTIVE_START_DATE AND pivf.EFFECTIVE_END_DATE

AND pivt.NAME in ('XXXXXXXXXXXXX')

AND pivf.uom = 'C'

AND prr.element_entry_id(+) = peeF.element_entry_id

AND pasg.ASSIGNMENT_ID = asg.ASSIGNMENT_ID))child_academic_year

,ptp.PERIOD_NAME

,TO_CHAR(ptp.DEFAULT_PAYDATE,'DD-Mon-YYYY') DEFAULT_PAYDATE

,Trim(DEFAULT_PAYDATE) Order_date

,petf.base_element_name

,petT.reporting_name

,to_char(PAPF.PERSON_NUMBER) PERSON_NUMBER

,to_char(ASG.assignment_number) assignment_number

,To_char(papf.start_date,'DD-Mon-YYYY') Date_of_Joining

,peTt.element_name

,pgft.name  grade

,pjft.name  Job

,hapft.name Position

,pd.name Department

,haou.name BU_Name

,pay.PAYROLL_NAME

FROM 

PAY_PAYROLL_REL_ACTIONS ppra

,PAY_PAYROLL_ACTIONS ppa

,PAY_PAY_RELATIONSHIPS_DN pprd

,PAY_REQUESTS pr

,PAY_FLOW_TASK_INSTANCES pfti

,PER_ALL_ASSIGNMENTS_M asg

,PAY_TIME_PERIODS ptp

,PAY_ELEMENT_TYPES_F petf

,PAY_ELEMENT_TYPES_TL pett

,PAY_ELE_CLASSIFICATIONS pec

,PAY_ELE_CLASSIFICATIONS pec1

,PAY_ELE_CLASSIFICATIONS_TL pect

,PAY_TIME_DEFINITIONS ptd

,PAY_TIME_DEFINITIONS ptd1

,PER_LEGISLATIVE_DATA_GROUPS pldg

,pay_all_payrolls_f pay

       ,per_all_people_f papf

       ,per_grades_f pgf

,per_grades_f_tl pgft

,per_jobs_f_tl pjft

,hr_all_positions_f_tl hapft

,per_departments PD

,hr_all_organization_units haou

,pay_element_entries_f        peef

WHERE  1=1

AND ppra.PAYROLL_ACTION_ID = ppa.PAYROLL_ACTION_ID

AND ppra.PAYROLL_RELATIONSHIP_ID = pprd.PAYROLL_RELATIONSHIP_ID

AND ppa.PAY_REQUEST_ID = pr.PAY_REQUEST_ID(+)

AND pr.FLOW_TASK_INSTANCE_ID = pfti.FLOW_TASK_INSTANCE_ID(+)

AND asg.PERSON_ID = pprd.PERSON_ID

AND asg.PRIMARY_FLAG = 'Y'

AND asg.WORK_TERMS_ASSIGNMENT_ID IS NOT NULL

AND ppa.EFFECTIVE_DATE BETWEEN asg.effective_start_Date AND asg.effective_end_date

AND asg.assignment_status_type <> 'INACTIVE'

AND ppra.RETRO_COMPONENT_ID IS NULL

AND ppa.EARN_TIME_PERIOD_ID = ptp.TIME_PERIOD_ID(+)

AND petf.ELEMENT_TYPE_ID = pett.ELEMENT_TYPE_ID(+)

AND (USERENV('LANG')) = pett.LANGUAGE (+)

AND petf.CLASSIFICATION_ID = pec1.CLASSIFICATION_ID

AND pec1.CLASSIFICATION_ID = pec.BASE_CLASSIFICATION_ID

AND petf.CLASSIFICATION_ID = pec.BASE_CLASSIFICATION_ID

AND pec.CLASSIFICATION_ID = pect.CLASSIFICATION_ID(+)

AND (USERENV('LANG')) = pect.LANGUAGE (+)

AND petf.STARTING_TIME_DEF_ID = ptd.TIME_DEFINITION_ID(+)

AND petf.ENDING_TIME_DEF_ID = ptd1.TIME_DEFINITION_ID(+)

AND pec.LEGISLATION_CODE = NVL(petf.LEGISLATION_CODE, pldg.LEGISLATION_CODE)

AND petf.LEGISLATIVE_DATA_GROUP_ID = pldg.LEGISLATIVE_DATA_GROUP_ID(+)

AND sysdate BETWEEN petf.EFFECTIVE_START_DATE AND petf.EFFECTIVE_END_DATE

AND pett.ELEMENT_NAME IN (

'XXXXXXXXXXX'

)

AND pay.payroll_id = ptp.payroll_id(+)

AND pay.payroll_id = ppa.payroll_id(+)

       AND ASG.person_id = papf.person_id

       --AND papf.person_id = ppnf.person_id

AND pgf.grade_id = ASG.grade_id

AND pgft.grade_id = pgf.grade_id

AND pgft.LANGUAGE = 'US'

AND pjft.job_id(+) = ASG.job_id

AND pjft.LANGUAGE = 'US'

AND hapft.position_id(+) = ASG.position_id

AND hapft.LANGUAGE = 'US'

AND ASG.organization_id = pd.organization_id(+)

AND haou.organization_id(+) = ASG.business_unit_id

AND sysdate between papf.effective_start_date and papf.effective_end_date

and sysdate between hapft.effective_start_date and hapft.effective_end_date

AND ASG.assignment_type IN ( 'E' )

AND papf.person_id = peef.PERSON_ID

AND peef.element_type_id = petf.element_type_id

AND peef.EFFECTIVE_START_DATE >= ptp.START_DATE

AND peef.EFFECTIVE_END_DATE <= ptp.END_DATE

AND (COALESCE(NULL, :P_Emp_No) IS null OR PAPF.PERSON_NUMBER IN (:P_Emp_No))

AND (COALESCE(NULL, :p_ass_no) IS null OR ASG.assignment_number IN (:p_ass_no))

AND (COALESCE(NULL, :p_payroll) IS null OR pay.payroll_name IN (:p_payroll))

AND (COALESCE(NULL, :P_Element_Name) IS null OR petT.element_name IN (:P_Element_Name))

AND ptp.DEFAULT_PAYDATE between (:P_From) and (:P_To)

)                               

WHERE 1=1

AND (COALESCE(NULL, :P_child_academic_year) IS null OR child_academic_year IN (:P_child_academic_year))

AND (Amount IS NOT NULL OR Amount >0)

--and entitle_amount is not null

ORDER BY PERSON_NUMBER,Order_date desc

Fixed Asset ERP Data to Blackline Tool

 Select  '1' as KEY,

        market,

       segment4,

       segment1,

       segment2,

       Period_End_Date,

       sum(period_activity)+ sum(begin_balance) BALANCE

from 

(

with bal as (select /*+ materialize */ * FROM  (SELECT 

FTH.book_type_code book_type_code,

fab.asset_number Cloud_Asset_Number,

gcc.segment1 , 

gcc.segment2 , 

gcc.segment3 , 

'COST' Type,

fab.asset_id,

FDD.cost,

FDP.PERIOD_NAME PERIOD_NAME,

fadh.units_assigned

FROM 

fa_distribution_history fadh, 

fa_transaction_headers fth,

gl_code_combinations gcc, 

fa_additions_b fab,

FA_DEPRN_PERIODS FDP,

FA_DEPRN_DETAIL FDD

WHERE 1=1

and fth.asset_id = fadh.asset_id

and fth.book_type_code = fadh.book_type_code

and fadh.code_combination_id = gcc.code_combination_id

and fab.asset_id = fth.asset_id

and fadh.transaction_header_id_in = fth.transaction_header_id

and fadh.book_type_code = fth.book_type_code

AND FDD.ASSET_ID = FTH.ASSET_ID

AND FDD.DISTRIBUTION_ID = FADH.DISTRIBUTION_ID

AND FDD.PERIOD_COUNTER = (SELECT MAX(FDP1.PERIOD_COUNTER)

FROM FA_DEPRN_PERIODS FDP1

,FA_DEPRN_DETAIL FDS1

WHERE FDS1.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE

AND FDS1.ASSET_ID = FDD.ASSET_ID

AND FDS1.DISTRIBUTION_ID = FDD.DISTRIBUTION_ID

AND FDP1.BOOK_TYPE_CODE = FDD.BOOK_TYPE_CODE

AND FDS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER

AND FDP1.CALENDAR_PERIOD_OPEN_DATE <= fnd_date.String_to_date(:P_PERIOD_NUM||'-'||:P_PERIOD_YEAR,'MM-YY')

)

AND FDD.PERIOD_COUNTER = FDP.PERIOD_COUNTER

AND FDD.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE

UNION ALL

SELECT 

FADH.book_type_code book_type_code,

fab.asset_number Cloud_Asset_Number,

gcc.segment1 , 

gcc.segment2 , 

gcc.segment3 , 

'DEP_RES' Type,

fab.asset_id,

fds.deprn_reserve COST,

FDP.PERIOD_NAME PERIOD_NAME,

fadh.units_assigned

FROM 

FA_DEPRN_DETAIL fds, 

fa_distribution_history fadh, 

gl_code_combinations gcc, 

fa_additions_b fab, 

FA_DEPRN_PERIODS FDP

WHERE 1=1

and fadh.asset_id = fds.asset_id

and fadh.DISTRIBUTION_ID = fds.DISTRIBUTION_ID

and fadh.book_type_code = fds.book_type_code

and fadh.code_combination_id = gcc.code_combination_id

and fab.asset_id = fadh.asset_id

AND FDS.PERIOD_COUNTER = FDP.PERIOD_COUNTER

AND FDP.PERIOD_COUNTER = (SELECT MAX(FDP1.PERIOD_COUNTER)

FROM FA_DEPRN_PERIODS FDP1

,FA_DEPRN_DETAIL FDS1

WHERE FDS1.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE

AND FDS1.ASSET_ID = FDS.ASSET_ID

AND FDS1.DISTRIBUTION_ID = FDS.DISTRIBUTION_ID

AND FDP1.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE

AND FDS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER

AND FDP1.CALENDAR_PERIOD_OPEN_DATE <= fnd_date.String_to_date(:P_PERIOD_NUM||'-'||:P_PERIOD_YEAR,'MM-YY')

) 

AND FDS.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE))

,CURRENT_UNITS AS (select /*+ materialize */ * FROM  (SELECT FAH.ASSET_ID , FAH.UNITS , FAH.BOOK_TYPE_CODE , GCC1.SEGMENT4 COST_SEGMENT4, GCC2.SEGMENT4 RESERVE_SEGMENT4

FROM 

FA_ASSET_HISTORY FAH

,FA_CATEGORY_BOOKS FCB

,GL_CODE_COMBINATIONS GCC1

,GL_CODE_COMBINATIONS GCC2

,(SELECT MAX(FTH.TRANSACTION_HEADER_ID) TRANSACTION_HEADER_ID, FTH.ASSET_ID , FTH.BOOK_TYPE_CODE

FROM  FA_TRANSACTION_HEADERS FTH

,XLA_EVENTS XE

,FA_ASSET_HISTORY FAH1

WHERE 1=1

AND FTH.EVENT_ID = XE.EVENT_ID

AND XE.EVENT_DATE  <= LAST_DAY(fnd_date.String_to_date(:P_PERIOD_NUM||'-'||:P_PERIOD_YEAR,'MM-YY'))

AND FTH.TRANSACTION_HEADER_ID = FAH1.TRANSACTION_HEADER_ID_IN

AND FTH.BOOK_TYPE_CODE = FAH1.BOOK_TYPE_CODE

AND FTH.ASSET_ID = FAH1.ASSET_ID

GROUP BY FTH.ASSET_ID , FTH.BOOK_TYPE_CODE) ASSET_HISTORY

WHERE 1=1

AND ASSET_HISTORY.ASSET_ID = FAH.ASSET_ID

AND ASSET_HISTORY.TRANSACTION_HEADER_ID = FAH.TRANSACTION_HEADER_ID_IN

AND ASSET_HISTORY.BOOK_TYPE_CODE = FAH.BOOK_TYPE_CODE

AND FAH.CATEGORY_ID = FCB.CATEGORY_ID

AND FAH.BOOK_TYPE_CODE = FCB.BOOK_TYPE_CODE

AND FCB.ASSET_COST_ACCOUNT_CCID = GCC1.CODE_COMBINATION_ID

AND FCB.RESERVE_ACCOUNT_CCID = GCC2.CODE_COMBINATION_ID))

SELECT rf.ancestor_pk1_value           

                        || Chr(9)            market,

                         (CASE WHEN Type = 'DEP_RES' THEN CU.RESERVE_SEGMENT4

ELSE CU.COST_SEGMENT4 END)

                        || Chr(9)            segment4,

                        bal.segment1

                        || Chr(9)            segment1,

                        bal.segment2

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)

                        || Chr(9)            segment2,

To_char(Last_day(fnd_date.String_to_date(gp.period_name,

                 'Mon-YY')),

'MM/DD/YYYY'

|| Chr(9)

|| Chr(9)

|| Chr(9))           Period_End_Date,

(CASE WHEN Type = 'DEP_RES' THEN To_char((round(bal.COST,2))*(-1),'fm999999999990.00')

ELSE  To_char(round(bal.cost,2),'fm999999999990.00') END) period_activity ,

TO_CHAR(nvl(null,0) ,'fm999999999990.00') begin_balance

from 

bal,

CURRENT_UNITS CU,

gl_ledgers GLG,

(SELECT DISTINCT tree_code,ancestor_pk1_value,distance,pk1_value FROM gl_seg_val_hier_rf) rf,

gl_periods GP,

fnd_lookup_values  flv,

fa_book_controls fbc

where 1=1

and GLG.ledger_category_code = 'PRIMARY'

AND gp.period_num = :P_PERIOD_NUM

AND gp.period_year = :P_PERIOD_YEAR

AND GLG.name = nvl(:P_LEDGER,GLG.name)

AND fnd_date.String_to_date(bal.period_name,'Mon-YY') <= fnd_date.String_to_date(gp.period_name,'Mon-YY')

AND fbc.book_type_code = bal.book_type_code

and fnd_date.String_to_date(bal.period_name,'Mon-YY') <=NVL(flv.end_Date_active,SYSDATE+1)

AND rf.pk1_value = bal.segment1

AND rf.tree_code = flv.description

AND flv.lookup_type = 'xxxxxxxxxxxx'

AND rf.distance = flv.tag

and flv.language='US'

and flv.lookup_code = 'xxxxxxxxxxxx' -- RICE ID for the given object

AND fbc.book_class = 'CORPORATE'

AND GLG.LEDGER_ID = fbc.set_of_books_id

AND CU.ASSET_ID = BAL.ASSET_ID

AND CU.BOOK_TYPE_CODE = BAL.BOOK_TYPE_CODE

)

group by 

market,

       segment4,

       segment1,

       segment2,

       Period_End_Date

order by 

market,

       segment4,

       segment1,

       segment2

Oracle Fixed Assets Integration Report

 

WITH hier_area AS (SELECT /*+ materialize */ DISTINCT dep29_pk1_value, dep1_pk1_value

                     FROM gl_seg_val_hier_cf WHERE tree_structure_code = 'GL_ACCT_FLEX'

                      AND tree_code = 'xxxxxxxxx'

                      AND tree_version_id = :P_TREE_V

                  ), 

report AS (

    SELECT 

        open_period,

        comp_code,

        facility_id,

        facility_desc,

        asset_id,

        description,

        conversion_id,

        parent_id,

        unit_number,

        vin,

        category_major,

        category_minor,

        quantity,

        SUM(cost)                        cost,

        SUM(accum_deprn)                 accum_deprn,

        ( SUM(cost) - SUM(accum_deprn) ) nbv,

        SUM(deprn)                       deprn,

        ( SUM(ytd_dpr) + SUM(ytd_pdp) )  ytd_dpr,

        revaluation_reserve,

        in_service_date,

        life,

        CASE

            WHEN rem_life_in_mon <= 0 THEN 0

            ELSE rem_life_in_mon

        END rem_life_in_mon,

        convention,

        dept,

        product,

        project,

        from_curr,

        wm_ownership,

        acquired_asset,

        fa_lcode,

        address,

        city,

        county,

        state,

        country,

        postal_code,

        concat_expns_acc,

        goodwill_ownership,

        run_date,

        area,

        book_type_code,

        'X' AS transaction_type_code

    FROM

        (

            SELECT /*+ materialize */

                gcc.segment1                                                                     comp_code,

                gcc.segment2                                                                     facility_id,

                gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id, 2, gcc.segment2) facility_desc,

                fab.asset_number                                                                 asset_id,

                fab.attribute1                                                                   conversion_id,

                fab.description,

                fcb.segment1                                                                     category_major,

                fcb.segment2                                                                     category_minor,

                fl.segment2                                                                      fa_lcode,

                hl.address_line_1                                                                address,

                hl.postal_code,

                hl.town_or_city                                                                  city,

                hl.region_1                                                                      county,

                hl.region_2                                                                      state,

                hl.country,

                fct.prorate_convention_code                                                      convention,

                fm.life_in_months                                                                life,

                to_char(fb.date_placed_in_service, 'MM/DD/YYYY', 'nls_date_language=American')   in_service_date,

                nvl(fdh.units_assigned, 0)                                                       quantity,

                gcc.segment3                                                                     dept,

                gcc.segment5                                                                     product,

                gcc.segment6                                                                     project,

                sob.currency_code                                                                from_curr,

                nvl(fdd.cost, 0)                                                                 cost,

                nvl(fdd.deprn_reserve, 0)                                                        accum_deprn,

                nvl(fdd.ytd_deprn, 0)                                                            ytd_dpr1,

                nvl((SELECT SUM(nvl(xl.accounted_cr, - 1 * xl.accounted_dr)) ytd_deprn

                       FROM fa_deprn_detail      fdd1,

                            xla_ae_headers       xh,

                            xla_ae_lines         xl,

                            gl_code_combinations gcc1

                      WHERE fdd1.asset_id = fab.asset_id

                        AND fdd1.book_type_code = fb.book_type_code

                        AND fdd1.distribution_id = fdd.distribution_id

                        AND fdd1.event_id = xh.event_id

                        AND xh.ae_header_id = xl.ae_header_id

and xl.application_id = 140

                        AND xl.accounting_class_code = 'DEPRECIATION_RESERVE'

                        AND gcc1.code_combination_id = xl.code_combination_id

                        AND gcc1.segment2 = gcc.segment2

                        AND fdd1.period_counter IN(SELECT MAX(fdpi.period_counter)

                                                     FROM fa_deprn_detail fdpi

                                                    WHERE fdpi.book_type_code = fb.book_type_code

                                                      AND fdpi.asset_id = fab.asset_id

                                                      AND fdpi.distribution_id = fdd.distribution_id)

                    ),

                  0)                                                                             deprn,

                nvl((

                    SELECT 

                        SUM(nvl(xla1.accounted_cr, - 1 * xla1.accounted_dr))

                    FROM

                        ( SELECT DISTINCT fdd1.event_id

                            FROM fa_deprn_detail fdd1

                           WHERE fdd1.asset_id = fab.asset_id

                             AND fdd1.book_type_code = fb.book_type_code

                             AND fdd1.deprn_source_code = 'D'

                             AND fdd1.period_counter IN( SELECT period_counter

                                                           FROM fa_deprn_periods fdp1

                                                          WHERE fdp1.book_type_code = fb.book_type_code -- fdd1.book_type_code commented by amruta

                                                            AND fdp1.fiscal_year = EXTRACT(YEAR FROM sysdate) )

                        )                    fdd1,

                        xla_ae_headers       xah1,

                        xla_ae_lines         xla1,

                        gl_code_combinations gcc1

                    WHERE 1=1

and fdd1.event_id = xah1.event_id

                        AND xah1.ae_header_id = xla1.ae_header_id

and xla1.application_id = 140

                        AND xla1.accounting_class_code = 'DEPRECIATION_RESERVE'

                        AND gcc1.code_combination_id = xla1.code_combination_id

                        AND gcc1.segment1 = gcc.segment1

                        AND gcc1.segment2 = gcc.segment2

                        AND EXTRACT(YEAR FROM xla1.accounting_date) = EXTRACT(YEAR FROM sysdate)

                ),

                    0)                                                                          ytd_dpr,

                nvl((

                    SELECT SUM(nvl(xl1.accounted_dr, - 1 * xl1.accounted_cr))

                    FROM fa_transaction_headers ftv1,

                        xla_ae_headers         xh1,

                        xla_ae_lines           xl1,

                        gl_code_combinations   gcc1,

                        fa_additions_vl        fab11

                    WHERE ftv1.asset_id = fab.asset_id

                        AND ftv1.book_type_code = fb.book_type_code

                        AND ftv1.event_id = xh1.event_id

                        AND xl1.ae_header_id = xh1.ae_header_id

                        AND xl1.code_combination_id = gcc1.code_combination_id

                        AND gcc1.segment1 = gcc.segment1

                        AND gcc1.segment2 = gcc.segment2

                        AND ftv1.asset_id = fab11.asset_id

                        AND EXTRACT(YEAR FROM xl1.accounting_date) = EXTRACT(YEAR FROM sysdate)

                        AND xl1.accounting_class_code IN('DEPRECIATION_EXPENSE', 'UNPLANNED_DEPRECIATION_EXPENSE')

                ),

                    0) AS ytd_pdp,

                decode(fds.reval_reserve, 0, '-', fds.reval_reserve)                             revaluation_reserve,

                fab.manufacturer_name                                                            unit_number,

                (CASE

                     WHEN (SELECT COUNT(asset_id) FROM fa_additions_vl WHERE parent_asset_id = fab.asset_id) > 0 THEN fab.asset_number

                     WHEN (SELECT COUNT(parent_asset_id) FROM fa_additions_vl WHERE asset_id = fab.asset_id) > 0 

                           THEN (SELECT asset_number FROM fa_additions_vl WHERE asset_id = fab.parent_asset_id)

                     WHEN ((SELECT COUNT(asset_id) FROM fa_additions_vl WHERE parent_asset_id = fab.asset_id) = 0 AND

                            (SELECT COUNT(parent_asset_id) FROM fa_additions_vl WHERE asset_id = fab.asset_id ) = 0 

                          ) THEN NULL

                     ELSE NULL

                 END)                                                                            parent_id,

                fab.serial_number                                                                vin,

                fab.attribute2                                                                   wm_ownership,

                fab.attribute4                                                                   goodwill_ownership,

                fab.attribute5                                                                   acquired_asset,

                (

                    SELECT

                        to_char(sysdate, 'dd')

                        || '-'

                        || upper(period_name)

                    FROM

                        gl_periods

                    WHERE

                            period_num = to_char(sysdate, 'mm')

                        AND period_set_name = 'xxxxxxxxx'

                        AND period_year = EXTRACT(YEAR FROM sysdate)

                )                                                                                run_date,

                ftn.dep29_pk1_value                                                              area,

                fb.book_type_code,

                ftv.transaction_type_code,

                (

                    SELECT

                        substr(period_name, 1, 3)

                        || '-'

                        || substr(period_name, 7, 8)

                    FROM

                        fa_deprn_periods

                    WHERE

                        period_close_date IS NULL

                        AND book_type_code = fb.book_type_code

                )                                                                                open_period,

                gcc.segment1||'.'||gcc.segment2||'.'||gcc.segment3||'.'||gcc.segment4 

               ||'.'||gcc.segment5||'.'||gcc.segment6||'.'||gcc.segment7||'.'||gcc.segment8      concat_expns_acc,

                ( ( fm.life_in_months ) - ( ( ( ( (

                    SELECT

                        fiscal_year

                    FROM

                        fa_deprn_periods

                    WHERE

                        period_close_date IS NULL

                        AND book_type_code = fb.book_type_code

                ) - EXTRACT(YEAR FROM fb.date_placed_in_service) ) * 12 ) + (

                    SELECT

                        period_num

                    FROM

                        fa_deprn_periods

                    WHERE

                        period_close_date IS NULL

                        AND book_type_code = fb.book_type_code

                ) ) - EXTRACT(MONTH FROM fb.date_placed_in_service) ) )                          rem_life_in_mon

            FROM

                fa_transaction_headers  ftv,

                fa_additions_vl         fab,

                fa_books                fb,

                fa_retirements          fr,

                fa_deprn_summary        fds,

                fa_deprn_detail         fdd,

                fa_distribution_history fdh,

                gl_code_combinations    gcc,

                hier_area               ftn,

                fa_deprn_periods        fdp,

                fa_methods              fm,

                fa_locations            fl,

                hr_locations_all        hl,

                fa_categories_b         fcb,

                fa_convention_types     fct,

                fa_book_controls        fbc,

                gl_sets_of_books        sob

            WHERE fab.asset_id NOT IN (SELECT DISTINCT fth1.asset_id

                                         FROM fa_transaction_headers fth1

                                        WHERE fth1.transaction_type_code = 'FULL RETIREMENT'

                                          AND fth1.date_effective = (SELECT MAX(date_effective) FROM fa_transaction_headers

                                                                      WHERE asset_id = fab.asset_id

                                                                        AND book_type_code = ftv.book_type_code)

                                          AND fth1.book_type_code = fb.book_type_code

                                      )

                AND fab.asset_category_id = fcb.category_id

                AND fab.asset_id = fb.asset_id

                AND fb.date_ineffective IS NULL

                AND fb.convention_type_id = fct.convention_type_id

                AND fab.asset_id = fr.asset_id (+)

                AND fr.retirement_id (+) = fb.retirement_id

                AND fbc.book_type_code = fb.book_type_code

                AND fbc.set_of_books_id = sob.set_of_books_id

                AND fds.deprn_source_code = 'DEPRN'

                AND fds.asset_id = fb.asset_id

                AND fds.book_type_code = fb.book_type_code

                AND fdp.period_counter = fds.period_counter

                AND fdp.book_type_code = fds.book_type_code

                AND fdd.asset_id = fb.asset_id

                AND fdd.asset_id = fds.asset_id

                AND fdd.book_type_code = fb.book_type_code

                AND fdd.period_counter = fds.period_counter

                AND fdd.period_counter = fdp.period_counter

                AND fdd.period_counter IN ( SELECT MAX(fdpi.period_counter) FROM fa_deprn_detail fdpi

                                             WHERE fdpi.book_type_code = fb.book_type_code

                                               AND fdpi.asset_id = fb.asset_id)

                AND fb.method_id = fm.method_id

                AND fdd.distribution_id = fdh.distribution_id

                AND fdh.asset_id = fab.asset_id

                AND fdh.book_type_code = fb.book_type_code

                AND fdh.code_combination_id = gcc.code_combination_id

                AND fdh.date_ineffective IS NULL

                AND ftn.dep1_pk1_value = gcc.segment1

                AND fbc.book_class = 'CORPORATE' -- p_book_class

                AND fb.date_effective IN (

                    SELECT

                        MAX(fb1.date_effective)

                    FROM

                        fa_books fb1

                    WHERE

                            fb1.asset_id = fab.asset_id

                        AND fb1.book_type_code = fb.book_type_code

                )

                AND fl.location_id = fdh.location_id

                AND fl.segment2 = hl.internal_location_code

                AND fdh.transaction_header_id_in = ftv.transaction_header_id

                AND fb.asset_id = ftv.asset_id

                AND fb.book_type_code = ftv.book_type_code

                 -- and ftv.event_id = xh.event_id

        )

    GROUP BY

        comp_code,

        facility_id,

        facility_desc,

        asset_id,

        conversion_id,

        description,

        category_major,

        category_minor,

        fa_lcode,

        address,

        postal_code,

        city,

        county,

        state,

        country,

        convention,

        life,

        in_service_date,

        quantity,

        dept,

        product,

        project,

        from_curr,

        revaluation_reserve,

        unit_number,

        parent_id,

        vin,

        wm_ownership,

        goodwill_ownership,

        acquired_asset,

        run_date,

        area,

        book_type_code,

        transaction_type_code,

        open_period,

        concat_expns_acc,

        rem_life_in_mon

    ORDER BY

        comp_code,

        facility_id,

        asset_id

)

SELECT

    *

FROM

    report

ORDER BY

    comp_code,

    facility_id,

    asset_id