Oracle Fusion - HCM - User and Manager Details Query

 /*

************************************************************************************************************************************

Seq            Date            Version            Developer            Requested By            Comments                

--------------------------------------------------------------------------------------------------------------

1            04-NOV-2020        1.0               Chetan Rajput        Aniket                    Newly Developed            

************************************************************************************************************************************

*/

select 

'KEY' AS "KEY",

ROWNUM SR_NO,

X.*

FROM

(

SELECT

PU.PERSON_ID EMP_PERSON_ID, 

PU.USERNAME EMP_USERNAME,

ppnf.EFFECTIVE_START_DATE EMP_START_DATE, 

ppnf.EFFECTIVE_END_DATE EMP_END_DATE, 

ppnf.FULL_NAME EMP_FULL_NAME,

ppnf.TITLE,

ppnf.FIRST_NAME,

ppnf.LAST_NAME,

email.email_address USER_EMAIL,

PRDV.ROLE_NAME EMP_ROLE_NAME,

haouf.NAME  EMP_RIG_NAME,

haouf.ORGANIZATION_ID  EMP_RIG_ID,

PASF.MANAGER_ID,

T_MANAGER.USERNAME MANAGER_USERNAME,

T_MANAGER.PERSON_ID MANAGER_PERSON_ID,

T_MANAGER.ROLE_NAME MANAGER_ROLE_NAME,

T_MANAGER.RIG_NAME MANAGER_RIG_NAME,

T_MANAGER.RIG_ID MANAGER_RIG_ID,

T_MANAGER.EFFECTIVE_START_DATE MANAGER_START_DATE,

T_MANAGER.EFFECTIVE_END_DATE MANAGER_END_DATE,

T_MANAGER.FULL_NAME MANAGER_FULL_NAME

/*

CASE

                    WHEN (TRUNC (PU.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)

                    THEN

                       PU.LAST_UPDATE_DATE

                    WHEN (TRUNC (ppnf.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       ppnf.LAST_UPDATE_DATE

                    WHEN (TRUNC (FURDA.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)

                    THEN

                       FURDA.LAST_UPDATE_DATE

                    WHEN (TRUNC (PUR.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       PUR.LAST_UPDATE_DATE

                    WHEN (TRUNC (PRDV.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       PRDV.LAST_UPDATE_DATE

                    WHEN (TRUNC (PASF.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       PASF.LAST_UPDATE_DATE  

WHEN (TRUNC (T_MANAGER.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       T_MANAGER.LAST_UPDATE_DATE

                 END

                    AS LAST_UPDATE_DATE

*/

from PER_USERS PU,

per_person_names_f ppnf,

FUSION.FUN_USER_ROLE_DATA_ASGNMNTS FURDA,

INV_ORG_PARAMETERS IOP,

hr_all_organization_units_f_vl haouf,

PER_USER_ROLES PUR,

PER_ROLES_DN_VL PRDV,

PER_ASSIGNMENT_SUPERVISORS_F_V PASF,

per_email_addresses email,

PER_ALL_PEOPLE_F B,

(select 

PU.PERSON_ID, 

PU.USERNAME,

ppnf.EFFECTIVE_START_DATE, 

ppnf.EFFECTIVE_END_DATE, 

ppnf.FULL_NAME,

PRDV.ROLE_NAME,

haouf.NAME RIG_NAME,

haouf.ORGANIZATION_ID RIG_ID

/*,

CASE

                    WHEN (TRUNC (PU.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)

                    THEN

                       PU.LAST_UPDATE_DATE

                    WHEN (TRUNC (ppnf.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       ppnf.LAST_UPDATE_DATE

                    WHEN (TRUNC (FURDA.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)

                    THEN

                       FURDA.LAST_UPDATE_DATE

                    WHEN (TRUNC (PUR.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       PUR.LAST_UPDATE_DATE

                    WHEN (TRUNC (PRDV.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                    THEN

                       PRDV.LAST_UPDATE_DATE

                   

                 END

                    AS LAST_UPDATE_DATE

*/

from PER_USERS PU,

per_person_names_f ppnf,

FUSION.FUN_USER_ROLE_DATA_ASGNMNTS FURDA,

INV_ORG_PARAMETERS IOP,

hr_all_organization_units_f_vl haouf,

PER_USER_ROLES PUR,

PER_ROLES_DN_VL PRDV

where 

1=1

AND PU.PERSON_ID=ppnf.PERSON_ID

and ppnf.NAME_TYPE <> 'GLOBAL'

AND trunc(sysdate) between ppnf.EFFECTIVE_START_DATE and NVL(ppnf.EFFECTIVE_END_DATE,sysdate)

AND FURDA.USER_GUID=PU.USER_GUID

AND FURDA.INV_ORGANIZATION_ID=IOP.ORGANIZATION_ID

AND haouf.ORGANIZATION_ID=IOP.ORGANIZATION_ID

AND PUR.user_id=PU.user_id

AND PUR.role_id=PRDV.role_id

AND PRDV.ROLE_COMMON_NAME=FURDA.ROLE_NAME

AND PRDV.ROLE_NAME <> 'Employee Custom'

) T_MANAGER

where 

1=1

AND PU.PERSON_ID=ppnf.PERSON_ID

and ppnf.NAME_TYPE <> 'GLOBAL'

AND trunc(sysdate) between ppnf.EFFECTIVE_START_DATE and NVL(ppnf.EFFECTIVE_END_DATE,sysdate)

AND FURDA.USER_GUID=PU.USER_GUID

AND FURDA.INV_ORGANIZATION_ID=IOP.ORGANIZATION_ID

AND haouf.ORGANIZATION_ID=IOP.ORGANIZATION_ID

AND PUR.user_id=PU.user_id

AND PUR.role_id=PRDV.role_id

AND PRDV.ROLE_COMMON_NAME=FURDA.ROLE_NAME

AND PRDV.ROLE_NAME <> 'Employee Custom'

AND ppnf.PERSON_ID=PASF.person_id(+) 

AND PASF.MANAGER_ID=T_MANAGER.PERSON_ID(+)

AND TRUNC (SYSDATE) BETWEEN TRUNC (b.EFFECTIVE_START_DATE)

                               AND TRUNC (b.EFFECTIVE_END_DATE)

AND b.person_id = ppnf.person_id

AND email.person_id(+) = b.person_id

AND email.email_address_id(+) = b.primary_email_id

AND TRUNC (SYSDATE) BETWEEN NVL (email.date_from(+), SYSDATE)

                           AND NVL (email.date_to(+), SYSDATE)

AND email.email_type = 'W1'      

/*

AND (   (TRUNC (PU.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)

                      OR (TRUNC (ppnf.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                      OR (TRUNC (FURDA.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)

                      OR (TRUNC (PUR.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                      OR (TRUNC (PRDV.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                            AND :P_END_DT)

                      OR (TRUNC (PASF.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)

  OR (TRUNC (T_MANAGER.LAST_UPDATE_DATE) BETWEEN :P_START_DT

                                                           AND :P_END_DT)    

   )

*/

-- AND PU.USERNAME='5140'

ORDER BY PU.USERNAME

)X

Oracle Fusion - GL Trail Balance Query with Cost Center & Natural Account

 select

COST_CENTER,

ACCOUNT,

COST_CENTER_DESC,

ACCOUNT_DESC,

SUM(OPENING_BAL) OPENING_BAL_USD,

SUM(PERIOD_NET_DR) NET_DR_USD,

SUM(PERIOD_NET_CR) NET_CR_USD,

SUM(CLOSING_BAL) CLOSING_BAL_USD

FROM

(

SELECT

CC.SEGMENT3 COST_CENTER,

CC.SEGMENT4 ACCOUNT,

(SELECT

VAL3.DESCRIPTION

FROM

FND_FLEX_VALUES_VL VAL3,

FND_ID_FLEX_SEGMENTS SEG3

WHERE    

SEG3.ID_FLEX_NUM = CC.CHART_OF_ACCOUNTS_ID

       AND VAL3.FLEX_VALUE_SET_ID = SEG3.FLEX_VALUE_SET_ID

       AND SEG3.APPLICATION_ID = 101

       AND SEG3.ID_FLEX_CODE = 'GL#'

       AND SEG3.ENABLED_FLAG = 'Y'

       AND SEG3.APPLICATION_COLUMN_NAME = 'SEGMENT3'

       AND VAL3.FLEX_VALUE = CC.SEGMENT3

) COST_CENTER_DESC,    

(

SELECT

VAL3.DESCRIPTION

FROM

FND_FLEX_VALUES_VL VAL3,

FND_ID_FLEX_SEGMENTS SEG3

WHERE    

SEG3.ID_FLEX_NUM = CC.CHART_OF_ACCOUNTS_ID

       AND VAL3.FLEX_VALUE_SET_ID = SEG3.FLEX_VALUE_SET_ID

       AND SEG3.APPLICATION_ID = 101

       AND SEG3.ID_FLEX_CODE = 'GL#'

       AND SEG3.ENABLED_FLAG = 'Y'

       AND SEG3.APPLICATION_COLUMN_NAME = 'SEGMENT4'

       AND VAL3.FLEX_VALUE = CC.SEGMENT4

) ACCOUNT_DESC,

(CASE WHEN GP.PERIOD_YEAR || LPAD (GP.PERIOD_NUM, 2, 0)=:P_FROM_PERIOD THEN NVL(GLB.BEGIN_BALANCE_DR, 0) - NVL(GLB.BEGIN_BALANCE_CR, 0)

END) OPENING_BAL,

-- NVL(GLB.BEGIN_BALANCE_DR, 0) - NVL(GLB.BEGIN_BALANCE_CR, 0) BEGIN_BALANCE,

GLB.PERIOD_NET_DR,

GLB.PERIOD_NET_CR,

-- (NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0) - NVL(BEGIN_BALANCE_CR, 0) - NVL(PERIOD_NET_CR, 0)) END_BALANCE,

(CASE WHEN GP.PERIOD_YEAR || LPAD (GP.PERIOD_NUM, 2, 0)=:P_TO_PERIOD THEN (NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0) - NVL(BEGIN_BALANCE_CR, 0) - NVL(PERIOD_NET_CR, 0)) 

END) CLOSING_BAL,

GP.PERIOD_YEAR || LPAD (GP.PERIOD_NUM, 2, 0) PERIOD

FROM

    GL_BALANCES      GLB,

    GL_CODE_COMBINATIONS   CC,

    GL_LEDGERS    GLL,

GL_PERIODS GP

WHERE

 GLB.ACTUAL_FLAG = 'A'

AND GLB.CURRENCY_CODE = 'USD'

-- AND GLB.PERIOD_NAME IN

AND GLB.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID

AND CC.SUMMARY_FLAG = 'N'

AND CC.TEMPLATE_ID IS NULL

AND GLL.LEDGER_ID = :P_LEDGER_ID --Ledger Parameter

AND GLL.PERIOD_SET_NAME = GP.PERIOD_SET_NAME

AND GP.ADJUSTMENT_PERIOD_FLAG = 'N'

AND GLB.PERIOD_NAME=GP.PERIOD_NAME

AND GP.PERIOD_YEAR || LPAD (GP.PERIOD_NUM, 2, 0) BETWEEN :P_FROM_PERIOD AND :P_TO_PERIOD    -- From and Period Parameter

AND cc.segment3 BETWEEN NVL (:p_segment3_low, cc.segment3) AND NVL (:p_segment3_high, cc.segment3) -- Cost Center Parameter

AND cc.segment4 BETWEEN NVL (:p_segment4_low, cc.segment4) AND NVL (:p_segment4_high, cc.segment4) -- Account Paramter

)

GROUP BY 

COST_CENTER,

ACCOUNT,

COST_CENTER_DESC,

ACCOUNT_DESC

ORDER BY COST_CENTER,ACCOUNT

Oracle Fusion - Fixed Asset Extract Query

 SELECT D.BOOK_TYPE_CODE,

         a.asset_number,

         a.description ASSET_DESC,

         a.ASSET_TYPE,

         fb.date_placed_in_service,

         (SELECT PRORATE_CONVENTION_CODE

            FROM FA_CONVENTION_TYPES

           WHERE CONVENTION_TYPE_ID = fb.CONVENTION_TYPE_ID)

            Proprate_Convention,

         fc.segment1 Asset_Category_Major,

         fc.segment2 Asset_Category_Minor,

         a.TAG_NUMBER,

         a.SERIAL_NUMBER,

         a.MANUFACTURER_NAME,

         a.MODEL_NUMBER Model,

         a.ATTRIBUTE1 Item_Detail,

         a.ATTRIBUTE2 PROJECT,

         (SELECT SEGMENT1

            FROM FA_ASSET_KEYWORDS

           WHERE CODE_COMBINATION_ID = a.ASSET_KEY_CCID)

            Asset_Key_Seg1,

         fb.cost Asset_Cost,

         (SELECT YTD_DEPRN

            FROM FA_DEPRN_SUMMARY

           WHERE     asset_id = a.asset_id

                 AND DEPRN_SOURCE_CODE = 'DEPRN'

                 AND DEPRN_RUN_DATE =

                        (SELECT MAX (DEPRN_RUN_DATE)

                           FROM FA_DEPRN_SUMMARY

                          WHERE     asset_id = a.asset_id

                                AND DEPRN_SOURCE_CODE = 'DEPRN'))

            YTD_Depreciation,

         (SELECT DEPRN_RESERVE

            FROM FA_DEPRN_SUMMARY

           WHERE     asset_id = a.asset_id

                 AND DEPRN_SOURCE_CODE = 'DEPRN'

                 AND DEPRN_RUN_DATE =

                        (SELECT MAX (DEPRN_RUN_DATE)

                           FROM FA_DEPRN_SUMMARY

                          WHERE     asset_id = a.asset_id

                                AND DEPRN_SOURCE_CODE = 'DEPRN'))

            Depreciation_Reserve,

           fb.cost

         - (SELECT DEPRN_RESERVE

              FROM FA_DEPRN_SUMMARY

             WHERE     asset_id = a.asset_id

                   AND DEPRN_SOURCE_CODE = 'DEPRN'

                   AND DEPRN_RUN_DATE =

                          (SELECT MAX (DEPRN_RUN_DATE)

                             FROM FA_DEPRN_SUMMARY

                            WHERE     asset_id = a.asset_id

                                  AND DEPRN_SOURCE_CODE = 'DEPRN'))

            Net_Book_Value,

         fai.invoice_number,

         fai.INVOICE_LINE_DESCRIPTION,

         fai.INVOICE_LINE_NUMBER,

         fai.PAYABLES_COST INV_LINE_AMOUNT,

         fai.VENDOR_NAME,

         fai.VENDOR_NUMBER,

         fai.PO_NUMBER,

         fai.PROJECT_NUMBER,

         fai.PROJECT_TASK_NUMBER,

         fai.FEEDER_SYSTEM_NAME SOURCE,

         a.CURRENT_UNITS,

         (SELECT (   ppnf.first_name

                  || ' '

                  || ppnf.nam_information1

                  || ' '

                  || ppnf.nam_information2

                  || ' '

                  || ppnf.last_name)

                    EMP_NAME

            FROM per_all_people_f b, per_person_names_f ppnf

           WHERE     d.assigned_to = b.person_id(+)

                 AND TRUNC (SYSDATE) BETWEEN TRUNC (b.effective_start_date)

                                         AND NVL (b.effective_end_date,

                                                  SYSDATE)

                 AND TRUNC (SYSDATE) BETWEEN ppnf.effective_start_date

                                         AND NVL (ppnf.effective_end_date,

                                                  SYSDATE)

                 AND b.person_id = ppnf.person_id

                 AND UPPER (ppnf.name_type) = 'GLOBAL')

            Employee_Name,

         (SELECT b.person_number

            FROM per_all_people_f b, per_person_names_f ppnf

           WHERE     d.assigned_to = b.person_id(+)

                 AND TRUNC (SYSDATE) BETWEEN TRUNC (b.effective_start_date)

                                         AND NVL (b.effective_end_date,

                                                  SYSDATE)

                 AND TRUNC (SYSDATE) BETWEEN ppnf.effective_start_date

                                         AND NVL (ppnf.effective_end_date,

                                                  SYSDATE)

                 AND b.person_id = ppnf.person_id

                 AND UPPER (ppnf.name_type) = 'GLOBAL')

            EMPLOYEE_NUM,

         (SELECT    GCC.SEGMENT1

                 || '-'

                 || GCC.SEGMENT2

                 || '-'

                 || GCC.SEGMENT3

                 || '-'

                 || GCC.SEGMENT4

                 || '-'

                 || GCC.SEGMENT5

                 || '-'

                 || GCC.SEGMENT6

            FROM gl_code_combinations gcc

           WHERE gcc.CODE_COMBINATION_ID = d.CODE_COMBINATION_ID)

            Depreciation_Exp_Acct,

         (   l.SEGMENT1

          || '-'

          || l.SEGMENT2

          || '-'

          || l.SEGMENT3

          || '-'

          || l.segment4)

            location

    FROM fa_additions_vl a,

         fa_locations l,

         fa_categories_b c,

         fa_asset_history h,

         fa_distribution_history d,

         fa_categories_vl fc,

         fa_category_books cb,

         fa_books fb,

         fa_asset_invoices fai

   WHERE     a.asset_id = d.asset_id

         AND d.location_id = l.location_id

         AND h.asset_id = a.asset_id

         AND h.category_id = c.category_id

         AND c.category_id = fc.category_id

         AND cb.category_id = fc.category_id

         AND cb.book_type_code = d.book_type_code

         AND d.date_ineffective IS NULL

         AND a.asset_id = fb.asset_id

         AND fb.period_counter_fully_retired IS NULL

         AND NVL (fb.DATE_INEFFECTIVE, SYSDATE) >= SYSDATE

         AND fai.asset_id = a.asset_id

         AND d.BOOK_TYPE_CODE = NVL (:p_book_type, d.BOOK_TYPE_CODE)

AND a.ASSET_TYPE=NVL(:p_Asset_Type,a.ASSET_TYPE)

AND fc.segment1=NVL(:P_MAJOR_CATEGORY,fc.segment1)

-- AND a.ASSET_NUMBER = '10333'

ORDER BY a.asset_number