Friday, July 23, 2021

Query to restrict data in Custom report based on procurement Agent's access of user

/* Query to fetch PO Number and the Procurement BU and data is restricted based on the user's Procurement Agent Access */

\SELECT P.SEGMENT1 PO_NUMBER,

HR.NAME PROCUREMENT_BU

FROM PO_HEADERS_ALL P,

HR_ALL_ORGANIZATION_UNITS HR

WHERE P.PRC_BU_ID=HR.ORGANIZATION_ID

AND EXISTS --Exists condition to check user access as Procurement Agent

(SELECT 0

FROM PO_AGENT_ACCESSES PoAgentAccess

WHERE PoAgentAccess.agent_id IN (SELECT HRC_SESSION_UTIL.GET_USER_PERSONID FROM DUAL)

AND PoAgentAccess.PRC_BU_ID = P.PRC_BU_ID

AND (P.TYPE_LOOKUP_CODE = 'STANDARD' 

AND PoAgentAccess.ACCESS_ACTION_CODE = 'MANAGE_PURCHASE_ORDERS')

AND PoAgentAccess.ACTIVE_FLAG = 'Y'

AND PoAgentAccess.ALLOWED_FLAG = 'Y'

AND (PoAgentAccess.ACCESS_OTHERS_LEVEL_CODE IN ('VIEW','MODIFY','FULL')

OR P.AGENT_ID = PoAgentAccess.AGENT_ID)

)

Monday, March 8, 2021

Query to get Candidate Requisition and Offer details

SELECT IC.CANDIDATE_NUMBER,

IRB.REQUISITION_NUMBER,

PPNF.LAST_NAME,

PPNF.FIRST_NAME,

PHONE.AREA_CODE || PHONE.PHONE_NUMBER PHONENUMBER,

EMAIL.EMAIL_ADDRESS EMAIL,

PJFT.NAME JOB,

PPNFR.FULL_NAME "RECRUITER NAME",

IRB.CREATION_DATE,

IC.PERSON_ID,

ADDRESS_LINE_1 || ' ' || ADDRESS_LINE_2 "ADDRESS",

LOC.INTERNAL_LOCATION_CODE LOCATION,

IO.OFFER_ID,

(

SELECT PAPF.PERSON_NUMBER

FROM PER_ALL_ASSIGNMENTS_M PAAM,

PER_ALL_PEOPLE_F PAPF

WHERE PAAM.PERSON_ID = IC.PERSON_ID

AND SYSDATE BETWEEN PAAM.EFFECTIVE_START_DATE

AND PAAM.EFFECTIVE_END_DATE

AND PAAM.ASSIGNMENT_TYPE = 'E'

AND PAAM.PERSON_ID = PAPF.PERSON_ID

) EMPLOYEE_NUMBER

FROM IRC_OFFERS IO,

PER_PERSON_NAMES_F PPNF,

IRC_CANDIDATES IC,

PER_EMAIL_ADDRESSES EMAIL,

PER_PHONES PHONE,

PER_ADDRESSES_F ADDRESS,

IRC_REQUISITIONS_B IRB,

IRC_SUBMISSIONS IRS,

PER_PERSON_NAMES_F PPNFR,

PER_JOBS_F_TL PJFT,

IRC_STATES_TL IST,

PER_LOCATIONS LOC

WHERE IO.PERSON_ID = IC.PERSON_ID

AND IC.PERSON_ID = PPNF.PERSON_ID

AND PPNF.NAME_TYPE = 'GLOBAL'

AND TRUNC(SYSDATE) BETWEEN PPNF.EFFECTIVE_START_DATE

AND PPNF.EFFECTIVE_END_DATE

AND EMAIL.EMAIL_ADDRESS_ID(+) = IC.CAND_EMAIL_ID

AND PHONE.PHONE_ID(+) = IC.CAND_PHONE_ID

AND ADDRESS.ADDRESS_ID(+) = IC.CAND_ADDRESS_ID

AND LOC.LOCATION_ID(+) = IRB.LOCATION_ID

AND IRS.REQUISITION_ID = IRB.REQUISITION_ID

AND IO.SUBMISSION_ID = IRS.SUBMISSION_ID

AND IRB.RECRUITER_ID = PPNFR.PERSON_ID

AND PPNFR.NAME_TYPE = 'GLOBAL'

AND TRUNC(SYSDATE) BETWEEN PPNFR.EFFECTIVE_START_DATE

AND PPNFR.EFFECTIVE_END_DATE

AND PJFT.JOB_ID = IRB.JOB_ID

AND PJFT.SOURCE_LANG = USERENV('LANG')

AND SYSDATE BETWEEN PJFT.EFFECTIVE_START_DATE

AND PJFT.EFFECTIVE_END_DATE

AND IST.STATE_ID = IRS.CURRENT_STATE_ID

AND IST.SOURCE_LANG = USERENV('LANG')

AND IC.CANDIDATE_NUMBER = :P_CANDIDATE_NUM

ORDER BY IRB.REQUISITION_NUMBER,

IC.CANDIDATE_NUMBER DESC

Query to get data from HCM User-Defined tables

SELECT *

FROM (

SELECT R.ROW_LOW_RANGE_OR_NAME ROW_ID,

V.VALUE,

C_TL.USER_COLUMN_NAME

FROM FF_USER_TABLES T,

FF_USER_ROWS_F R,

FF_USER_COLUMNS C,

FF_USER_COLUMNS_TL C_TL,

FF_USER_COLUMN_INSTANCES_F V

WHERE T.BASE_USER_TABLE_NAME = :UDT_TABLE_NAME

AND T.USER_TABLE_ID = R.USER_TABLE_ID

AND C.USER_COLUMN_ID = C_TL.USER_COLUMN_ID

AND T.USER_TABLE_ID = C.USER_TABLE_ID

AND C.USER_COLUMN_ID = V.USER_COLUMN_ID

AND R.USER_ROW_ID = V.USER_ROW_ID

AND SYSDATE BETWEEN V.EFFECTIVE_START_DATE

AND V.EFFECTIVE_END_DATE

AND SYSDATE BETWEEN R.EFFECTIVE_START_DATE

AND R.EFFECTIVE_END_DATE

)

PIVOT(MAX(VALUE) FOR USER_COLUMN_NAME IN (

USER_COLUMN_NAME1,

USER_COLUMN_NAME2

))

Thursday, February 4, 2021

Query to fetch the Fixed Asset details with PTD/ QTD/ YTD information for a given accounting period

SELECT DISTINCT FAB.ASSET_NUMBER, 

  FAT.DESCRIPTION ASSET_DESCRIPTION, 

  FAB.MANUFACTURER_NAME, 

  FASI.VENDOR_NAME, 

  FASI.PO_NUMBER, 

  FASI.REQUESTER, 

  FAB.ATTRIBUTE_CATEGORY_CODE CATEGORY_DESCRIPTION, 

  FB.ORIGINAL_COST, 

  FAC.COST CURRENT_COST, 

  FB.DATE_PLACED_IN_SERVICE, 

  FM.LIFE_IN_MONTHS / 12 LIFE_IN_YEARS, 

  FAB.TAG_NUMBER, 

  DECODE(

    FAB.IN_USE_FLAG, 'YES', 'IN USE', 

    'DISPOSED'

  ) ASSETS_CONDITION, 

  FAL.NUMBER_OF_UNITS, 

  FAL.LOCATION, 

  FAB.ATTRIBUTE2 STORAGE_BUILDING_ROOM, 

  FAB.SERIAL_NUMBER, 

  FAD.DEPRECIATION_RESERVATION, 

  FAC.COST - NVL(FAD.DEPRECIATION_RESERVATION, 0) NET_BOOK_VALUE, 

  FAH.ASSET_TYPE, 

  (

    SELECT 

      DISTINCT SEGMENT1 

    FROM 

      GL_CODE_COMBINATIONS 

    WHERE 

      CODE_COMBINATION_ID = FAL.CODE_COMBINATION_ID

  ) COMPANY_CODE, 

  (

    SELECT 

      DISTINCT SEGMENT2 

    FROM 

      GL_CODE_COMBINATIONS 

    WHERE 

      CODE_COMBINATION_ID = FAL.CODE_COMBINATION_ID

  ) COST_CENTER, 

  (

    SELECT 

      SEGMENT3 

    FROM 

      GL_CODE_COMBINATIONS 

    WHERE 

      CODE_COMBINATION_ID = (

        SELECT 

          FCB.ASSET_COST_ACCOUNT_CCID 

        FROM 

          FA_CATEGORY_BOOKS FCB, 

          FA_ADDITIONS_B FA, 

          FA_CATEGORIES_B FC 

        WHERE 

          FA.ASSET_CATEGORY_ID = FC.CATEGORY_ID 

          AND FC.CATEGORY_ID = FCB.CATEGORY_ID 

          AND FA.ASSET_ID = FAB.ASSET_ID

      )

  ) ASSET_ACCOUNT, 

  (

    CASE WHEN (

      SELECT 

        PERIOD_COUNTER 

      FROM 

        FA_DEPRN_PERIODS 

      WHERE 

        PERIOD_NAME = : P_ACCOUNTING_PERIOD 

        AND BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE

    ) IN (

      SELECT 

        FDSS1.PERIOD_COUNTER 

      FROM 

        FA_DEPRN_SUMMARY FDSS1, 

        FA_DEPRN_PERIODS FDP1 

      WHERE 

        FDSS1.DEPRN_SOURCE_CODE = 'DEPRN' 

        AND FDSS1.ASSET_ID = FB.ASSET_ID 

        AND FDSS1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE 

        AND FDSS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER 

        AND FDP1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE

    ) THEN NVL(

      (

        SELECT 

          FDSS.YTD_DEPRN 

        FROM 

          FA_DEPRN_SUMMARY FDSS 

        WHERE 

          FDSS.DEPRN_SOURCE_CODE = 'DEPRN' 

          AND FDSS.ASSET_ID = FB.ASSET_ID 

          AND FDSS.PERIOD_COUNTER = (

            SELECT 

              FDSS1.PERIOD_COUNTER 

            FROM 

              FA_DEPRN_SUMMARY FDSS1, 

              FA_DEPRN_PERIODS FDP1 

            WHERE 

              FDSS1.DEPRN_SOURCE_CODE = 'DEPRN' 

              AND FDSS1.ASSET_ID = FB.ASSET_ID 

              AND FDSS1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE 

              AND FDSS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER 

              AND FDP1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE 

              AND FDP1.PERIOD_NAME = : P_ACCOUNTING_PERIOD

          ) 

          AND FDSS.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE

      ), 

      0

    ) WHEN (

      SELECT 

        PERIOD_COUNTER 

      FROM 

        FA_DEPRN_PERIODS 

      WHERE 

        PERIOD_NAME = : P_ACCOUNTING_PERIOD 

        AND BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE

    ) > (

      SELECT 

        MAX(FDSS1.PERIOD_COUNTER) 

      FROM 

        FA_DEPRN_SUMMARY FDSS1, 

        FA_DEPRN_PERIODS FDP1 

      WHERE 

        FDSS1.DEPRN_SOURCE_CODE = 'DEPRN' 

        AND FDSS1.ASSET_ID = FB.ASSET_ID 

        AND FDSS1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE 

        AND FDSS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER 

        AND FDP1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE

    ) THEN NVL(

      (

        SELECT 

          FDSS.YTD_DEPRN 

        FROM 

          FA_DEPRN_SUMMARY FDSS 

        WHERE 

          FDSS.DEPRN_SOURCE_CODE = 'DEPRN' 

          AND FDSS.ASSET_ID = FB.ASSET_ID 

          AND FDSS.PERIOD_COUNTER = (

            SELECT 

              MAX(FDSS1.PERIOD_COUNTER) 

            FROM 

              FA_DEPRN_SUMMARY FDSS1, 

              FA_DEPRN_PERIODS FDP1 

            WHERE 

              FDSS1.DEPRN_SOURCE_CODE = 'DEPRN' 

              AND FDSS1.ASSET_ID = FB.ASSET_ID 

              AND FDSS1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE 

              AND FDSS1.PERIOD_COUNTER = FDP1.PERIOD_COUNTER 

              AND FDP1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE 

              AND SUBSTR(FDP1.PERIOD_NAME,-2, 2) = SUBSTR(

                (: P_ACCOUNTING_PERIOD), 

                -2, 

                2

              )

          ) 

          AND FDSS.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE

      ), 

      0

    ) ELSE 0 END

  ) YTD1, 

  (

    SELECT 

      FDS.DEPRN_AMOUNT 

    FROM 

      FA_DEPRN_PERIODS FDP, 

      FA_DEPRN_SUMMARY FDS 

    WHERE 

      1 = 1 

      AND FDS.ASSET_ID = FAB.ASSET_ID 

      AND FDP.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE 

      AND FB.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE 

      AND FDP.PERIOD_COUNTER = FDS.PERIOD_COUNTER 

      AND FDS.DEPRN_SOURCE_CODE = 'DEPRN' 

      AND FDS.PERIOD_COUNTER = (

        SELECT 

          FDP1.PERIOD_COUNTER 

        FROM 

          FA_DEPRN_PERIODS FDP1 

        WHERE 

          FDP1.PERIOD_NAME = : P_ACCOUNTING_PERIOD 

          AND FDS.BOOK_TYPE_CODE = FDP1.BOOK_TYPE_CODE

      )

  ) "CURRENT_DEPRECIATION", 

  (

    SELECT 

      SUM(FDS.DEPRN_AMOUNT) 

    FROM 

      FA_DEPRN_PERIODS FDP, 

      FA_DEPRN_SUMMARY FDS 

    WHERE 

      1 = 1 

      AND FDS.ASSET_ID = FAB.ASSET_ID 

      AND FDP.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE 

      AND FB.BOOK_TYPE_CODE = FDS.BOOK_TYPE_CODE 

      AND FDP.PERIOD_COUNTER = FDS.PERIOD_COUNTER 

      AND FDS.DEPRN_SOURCE_CODE = 'DEPRN' 

      AND FDS.PERIOD_COUNTER IN (

        SELECT 

          FDP1.PERIOD_COUNTER 

        FROM 

          FA_DEPRN_PERIODS FDP1 

        WHERE 

          FDP1.PERIOD_NAME IN (

            SELECT 

              PERIOD_NAME 

            FROM 

              GL_PERIODS 

            WHERE 

              (PERIOD_YEAR, QUARTER_NUM) = (

                SELECT 

                  GP1.PERIOD_YEAR, 

                  GP1.QUARTER_NUM 

                FROM 

                  GL_PERIODS GP1 

                WHERE 

                  GP1.PERIOD_NAME = : P_ACCOUNTING_PERIOD

              ) 

              AND ADJUSTMENT_PERIOD_FLAG <> 'Y' 

              AND PERIOD_NUM <= (

                SELECT 

                  PERIOD_NUM 

                FROM 

                  GL_PERIODS 

                WHERE 

                  PERIOD_NAME = : P_ACCOUNTING_PERIOD

              )

          ) 

          AND FDS.BOOK_TYPE_CODE = FDP1.BOOK_TYPE_CODE

      )

  ) "QTD_DEPRECIATION", 

  (

    SELECT 

      DISTINCT GCC11.SEGMENT1 || '-' || GCC11.SEGMENT2 || '-' || GCC11.SEGMENT3 || '-' || GCC11.SEGMENT4 || '-' || GCC11.SEGMENT5 || '-' || GCC11.SEGMENT6 || '-' || GCC11.SEGMENT7 DEPRN_EXP_ACCOUNT 

    FROM 

      GL_CODE_COMBINATIONS GCC11, 

      FA_DISTRIBUTION_HISTORY FDH2 

    WHERE 

      1 = 1 

      AND FAB.ASSET_ID = FDH2.ASSET_ID 

      AND FDH2.DATE_INEFFECTIVE IS NULL 

      AND FDH2.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE 

      AND GCC11.CODE_COMBINATION_ID = FDH2.CODE_COMBINATION_ID

  ) DEPRN_EXP_ACCOUNT 

FROM 

  FA_ADDITIONS_B FAB, 

  FA_ADDITIONS_TL FAT, 

  (

    SELECT 

      FAB.ASSET_ID, 

      (

        SELECT 

          COST 

        FROM 

          (

            SELECT 

              FB.COST 

            FROM 

              FA_TRANSACTION_HEADERS FTH, 

              FA_BOOKS FB, 

              (

                SELECT 

                  MIN(CALENDAR_PERIOD_CLOSE_DATE) TO_PERIOD_CLOSE_DATE 

                FROM 

                  FA_DEPRN_PERIODS 

                WHERE 

                  PERIOD_NAME =: P_ACCOUNTING_PERIOD

              ) FAP 

            WHERE 

              1 = 1 

              AND FTH.ASSET_ID = FAB.ASSET_ID 

              AND FTH.TRANSACTION_TYPE_CODE IN (

                'ADDITION', 'REINSTATEMENT', 'FULL RETIREMENT', 

                'ADDITION/VOID', 'ADJUSTMENT', 'CIP ADDITION', 

                'CIP REVERSE', 'CIP ADJUSTMENT'

              ) 

              AND FTH.TRANSACTION_DATE_ENTERED <= FAP.TO_PERIOD_CLOSE_DATE 

              AND FTH.TRANSACTION_HEADER_ID = FB.TRANSACTION_HEADER_ID_IN 

            ORDER BY 

              FTH.TRANSACTION_HEADER_ID DESC

          ) 

        WHERE 

          ROWNUM = 1

      ) COST 

    FROM 

      FA_ADDITIONS_B FAB

  ) FAC,   --COST

  (

    SELECT 

      FB.* 

    FROM 

      FA_BOOKS FB 

    WHERE 

      FB.TRANSACTION_HEADER_ID_IN =(

        SELECT 

          MAX(TRANSACTION_HEADER_ID_IN) 

        FROM 

          FA_BOOKS FB1 

        WHERE 

          FB1.ASSET_ID = FB.ASSET_ID 

          AND FB1.BOOK_TYPE_CODE = FB.BOOK_TYPE_CODE

      ) 

      AND FB.DATE_INEFFECTIVE IS NULL

  ) FB,   --METHOD

  FA_METHODS FM, 

  (

    SELECT 

      ASSET_ID, 

      LISTAGG(VENDOR_NAME, ', ') WITHIN GROUP (

        ORDER BY 

          PO_NUMBER

      ) VENDOR_NAME, 

      LISTAGG(PO_NUMBER, ', ') WITHIN GROUP (

        ORDER BY 

          PO_NUMBER

      ) PO_NUMBER, 

      LISTAGG(REQUESTER, ', ') WITHIN GROUP (

        ORDER BY 

          PO_NUMBER

      ) REQUESTER 

    FROM 

      (

        SELECT 

          DISTINCT FAI.ASSET_ID, 

          PSV.VENDOR_NAME, 

          FAI.PO_NUMBER, 

          PPNF.DISPLAY_NAME REQUESTER 

        FROM 

          FA_ASSET_INVOICES FAI, 

          AP_INVOICE_DISTRIBUTIONS_ALL AIDA, 

          PO_DISTRIBUTIONS_ALL PDA, 

          PER_PERSON_NAMES_F PPNF, 

          POZ_SUPPLIERS_V PSV 

        WHERE 

          1 = 1 

          AND FAI.INVOICE_LINE_TYPE(+) = 'ITEM' 

          AND AIDA.INVOICE_DISTRIBUTION_ID(+) = FAI.INVOICE_DISTRIBUTION_ID 

          AND PDA.PO_DISTRIBUTION_ID(+) = AIDA.PO_DISTRIBUTION_ID 

          AND PPNF.PERSON_ID(+) = PDA.DELIVER_TO_PERSON_ID 

          AND PPNF.NAME_TYPE(+) = 'GLOBAL' 

          AND SYSDATE BETWEEN PPNF.EFFECTIVE_START_DATE(+) 

          AND PPNF.EFFECTIVE_END_DATE(+) 

          AND PSV.VENDOR_ID(+) = FAI.PO_VENDOR_ID

      ) 

    GROUP BY 

      ASSET_ID

  ) FASI,   --FIXED ASSET SOURCE INFO

  (

    SELECT 

      ASSET_ID, 

      LISTAGG(NUMBER_OF_UNITS, ', ') WITHIN GROUP (

        ORDER BY 

          LOCATION

      ) NUMBER_OF_UNITS, 

      LISTAGG(LOCATION, ', ') WITHIN GROUP (

        ORDER BY 

          LOCATION

      ) LOCATION, 

      CODE_COMBINATION_ID 

    FROM 

      (

        SELECT 

          FDH.ASSET_ID, 

          GCC.CODE_COMBINATION_ID, 

          SUM(FDH.UNITS_ASSIGNED) NUMBER_OF_UNITS, 

          FALO.SEGMENT1 || '.' || FALO.SEGMENT2 || '.' || FALO.SEGMENT3 || '.' || FALO.SEGMENT4 LOCATION 

        FROM 

          FA_DISTRIBUTION_HISTORY FDH, 

          FA_LOCATIONS FALO, 

          GL_CODE_COMBINATIONS GCC 

        WHERE 

          1 = 1 

          AND NVL (

            FDH.DATE_INEFFECTIVE(+), 

            SYSDATE + 1

          ) > SYSDATE 

          AND FDH.TRANSACTION_HEADER_ID_OUT IS NULL 

          AND FALO.LOCATION_ID = FDH.LOCATION_ID 

          AND GCC.CODE_COMBINATION_ID = FDH.CODE_COMBINATION_ID 

          AND GCC.SEGMENT1 IN (: P_COMPANY_CODE) 

        GROUP BY 

          FDH.ASSET_ID, 

          FALO.SEGMENT1, 

          FALO.SEGMENT2, 

          FALO.SEGMENT3, 

          FALO.SEGMENT4, 

          GCC.CODE_COMBINATION_ID

      ) 

    GROUP BY 

      ASSET_ID, 

      CODE_COMBINATION_ID

  ) FAL,   --FIXED ASSET LOCATIONS

  (

    SELECT 

      FDS.ASSET_ID, 

      FDS.DEPRN_RESERVE DEPRECIATION_RESERVATION 

    FROM 

      FA_DEPRN_SUMMARY FDS, 

      (

        SELECT 

          FDS.ASSET_ID, 

          MAX(FDS.DEPRN_RUN_DATE) MAX_DEPRN_RUN_DATE 

        FROM 

          FA_DEPRN_SUMMARY FDS, 

          (

            SELECT 

              MIN(PERIOD_COUNTER) TO_PERIOD_COUNTER 

            FROM 

              FA_DEPRN_PERIODS 

            WHERE 

              PERIOD_NAME =: P_ACCOUNTING_PERIOD

          ) FTP --TO PERIOD

        WHERE 

          FDS.PERIOD_COUNTER <= FTP.TO_PERIOD_COUNTER 

        GROUP BY 

          FDS.ASSET_ID 

        ORDER BY 

          FDS.ASSET_ID

      ) FMDR --MAX DEPRECIATION RUN

    WHERE 

      1 = 1 

      AND FDS.ASSET_ID = FMDR.ASSET_ID 

      AND FDS.DEPRN_RUN_DATE = FMDR.MAX_DEPRN_RUN_DATE

  ) FAD,   --DEPRECIATION  

  FA_TRANSACTION_HEADERS FTH, 

  FA_DEPRN_PERIODS FDP, 

  FA_ASSET_HISTORY FAH 

WHERE 

  1 = 1 

  AND FAT.ASSET_ID = FAB.ASSET_ID 

  AND FAT.LANGUAGE = USERENV('LANG') 

  AND FAC.ASSET_ID(+)= FAB.ASSET_ID 

  AND FB.ASSET_ID(+)= FAB.ASSET_ID 

  AND FM.METHOD_ID(+)= FB.METHOD_ID 

  and FASI.ASSET_ID(+) = FAB.ASSET_ID 

  AND FAL.ASSET_ID(+) = FAB.ASSET_ID 

  AND FAD.ASSET_ID(+) = FAB.ASSET_ID 

  AND FAH.ASSET_ID(+) = FAB.ASSET_ID 

  AND FAH.BOOK_TYPE_CODE(+)= FB.BOOK_TYPE_CODE 

  AND FAH.DATE_INEFFECTIVE is null 

  AND FTH.ASSET_ID = FAB.ASSET_ID 

  AND FTH.TRANSACTION_TYPE_CODE IN ('ADDITION') 

  AND FTH.BOOK_TYPE_CODE = FDP.BOOK_TYPE_CODE 

  AND FTH.DATE_EFFECTIVE BETWEEN FDP.PERIOD_OPEN_DATE AND NVL(FDP.PERIOD_CLOSE_DATE, SYSDATE)

-- Asset addition period should be less than Accounting_period parameter  

  AND FDP.PERIOD_OPEN_DATE <= (

    SELECT 

      MIN(PERIOD_OPEN_DATE) TO_PERIOD_CLOSE_DATE 

    FROM 

      FA_DEPRN_PERIODS 

    WHERE 

      PERIOD_NAME =: P_ACCOUNTING_PERIOD

  ) 

ORDER BY 

  FAB.ASSET_NUMBER

Monday, November 9, 2020

Downloading and Creating FRS Report

Role Required:  Financial Analyst

Process:

·       Downloading FRS Studio

·       Log in to FRS Studio

·       Creating Grid and setting up POV(Point of View) Values

·       Formatting the Report output

 

Downloading FRS Studio:

 Navigate to the below path as shown in the screenshot

 Welcome spring board à Navigator Menu à Financial Reporting Center



Allow popup to open FRS Window

Navigate as shown in the below screenshot to Install Studio 

Once the Studio is downloaded, Navigate to the installed location and run as administrator.


 

 Login to FRS Studio:

Open the App and Run as administrator 

 

 

Enter Credentials as below

User Name & Password: User Login Credentials

Server URL: Oracle Instance URL till .com

 


 

Example :

Let us try to build the Report shown in the below screenshot. 

 

 

Creating Grid and setting up POV Values:

From the FRS Studio, follow the Navigation shown in the below screenshot to Create new Report


Create Grid by Dragging and select the Database connection



Now to get the Report in the desired Format, we need to Drag Account to the Rows and Period to the Column as shown and click OK



Once you click on OK , you will see the selected Dimensions in the Grid.

 

To select the desired accounts, double click on the Account Dimension. Member selection screen will appear as shown


First we need to remove the Account present on the Right side by moving to left as shown


Now, expand the account hierarchy and move the desired accounts from left to the right as shown.


Once we select the desired Accounts and move, enable Place selections into separate rows checkbox to see accounts in separate lines.


And click OK.

Now select the Desired Periods by double clicking Accounting Period Dimension in the same way as we selected Accounts.


Now, the grid will look like this


If you run Report at this Stage, you will not see any Data. To get the Data we need to set the POV Members as shown


Double click on any POV Member to set the Value as shown below and click OK

Follow the same for Remaining Point of Views and select Values.

 

Now save the Report in desired Location

 


Now run the Report to see the Values


Output

Formatting Report:

To rename the Accounts to match with the sample 

 Select the account and give the custom heading from Row properties as shown 


Follow for the Remaining rows and columns as required.

Now, we will add the formula row to show the check value as shown


 Give custom heading similar to Accounts

 Enter formula and click on Right mark as shown

 


Now save the Report and Run to view the latest output


Adding borders to the formula row


Right Align Columns and displaying $ symbol



Making column Headings Bold


Now save the Report and Run to view latest output

Adding Report Name in the Header

Now drag the Text Box and add heading and format as shown

 

Position the Header to Display in the Middle as shown

Now save the Report and view the final Output


 

Monday, November 2, 2020

Procure to Pay (P2P) Technical Flow

 P2P cycle flows through the following stages

 

·       Creating Requisition

·       Requisition Approval

·       Creating Purchase Order

·       Purchase Order Approval

·       Creating Invoice and Validating

·       Creation of Payment

·       Transfer and Import Journal to General Ledger

 

Tables Involved at each step:

Requisition:

POR_REQUISITION_HEADERS_ALL

POR_REQUISITION_LINES_ALL

POR_REQ_DISTRIBUTIONS_ALL

 

Approval:

PO_ACTION_HISTORY

 

Purchase Order:

PO_HEADERS_ALL

PO_HEADERS_ARCHIVE_ALL

PO_HEADERS_DRAFT_ALL

 

PO_LINES_ALL

PO_LINES_ARCHIVE_ALL

PO_LINES_DRAFT_ALL

PO_LINE_LOCATIONS_ALL

 

PO_DISTRIBUTIONS_ALL

PO_DISTRIBUTIONS_ARCHIVE_ALL

PO_DISTRIBUTIONS_DRAFT_ALL

 

Invoice:

AP_INVOICES_ALL

AP_INVOICE_LINES_ALL

AP_INVOICE_DISTRIBUTIONS_ALL

 

AP_INVOICE_PAYMENTS_ALL

AP_INV_APRVL_HIST_ALL

 

Payment:

AP_CHECKS_ALL

AP_HOLDS_ALL

AP_PAYMENT_SCHEDULES_ALL

AP_PAYMENT_HISTORY_ALL

 

General Ledger Transfer:

GL_JE_HEADERS

GL_JE_LINES

GL_IMPORT_REFERENCES

 

XLA_AE_HEADERS

XLA_AE_LINES

XLA_DISTRIBUTION_LINKS

 

Some other useful tables

PO_VERSIONS

POZ_SUPPLIERS

PO_VENDORS

PO_VENDOR_SITES_ALL

PO_VENDOR_CONTACTS_ALL

PO_AGENTS_V

PO_LOOKUP_CODES

AP_LOOKUP_CODES

AP_HISTORY_INVOICES_ALL

AP_HISTORY_CHECKS_ALL

 

P2P Flow chart:

 

 

P2P Query:

 SELECT PRH.REQUISITION_NUMBER,

            PRL.LINE_NUMBER REQ_LINE_NUM,

            PAH.LAST_UPDATED_BY APPROVER,

            PHA.SEGMENT1 PO_NUMBER,

            PLA.LINE_NUM PO_LINE_NUM,

            AIA.INVOICE_NUM,

            AIA.INVOICE_AMOUNT,

            NVL((

                                    SELECT SUM(AMOUNT)

                                    FROM AP_INVOICE_LINES_ALL

                                    WHERE INVOICE_ID = AIA.INVOICE_ID

                                                AND LINE_TYPE_LOOKUP_CODE = 'TAX'

                                    ), 0) TAX,

            ACA.CHECK_NUMBER,

            AIPA.AMOUNT AMOUNT_PAID,

            (

                        SELECT APPROVER_ID

                        FROM (

                                    SELECT AIAH.APPROVER_ID

                                    FROM AP_INV_APRVL_HIST_ALL AIAH

                                    WHERE AIPA.INVOICE_ID = AIAH.INVOICE_ID

                                                AND AIAH.RESPONSE LIKE '%APPROVED'

                                    ORDER BY LAST_UPDATE_DATE

                                    )

                        WHERE ROWNUM = 1

                        ) INVOICE_APPROVER,

            XDL.UNROUNDED_ACCOUNTED_CR,

            XDL.UNROUNDED_ACCOUNTED_DR,

            XAL.AE_LINE_NUM,

            GJH.NAME JOURNAL_NAME,

            GJL.JE_LINE_NUM

FROM POR_REQUISITION_HEADERS_ALL PRH,

            POR_REQUISITION_LINES_ALL PRL,

            PO_ACTION_HISTORY PAH,

            POR_REQ_DISTRIBUTIONS_ALL PRD,

            PO_DISTRIBUTIONS_ALL PDA,

            PO_LINES_ALL PLA,

            PO_HEADERS_ALL PHA,

            AP_INVOICE_DISTRIBUTIONS_ALL AIDA,

            AP_INVOICES_ALL AIA,

            AP_INVOICE_PAYMENTS_ALL AIPA,

            AP_CHECKS_ALL ACA,

            -- AP_INV_APRVL_HIST_ALL AIAH,

            XLA_DISTRIBUTION_LINKS XDL,

            XLA_AE_HEADERS XAH,

            XLA_AE_LINES XAL,

            GL_JE_LINES GJL,

            GL_IMPORT_REFERENCES GIR,

            GL_JE_HEADERS GJH

WHERE 1 = 1

            AND AIA.INVOICE_NUM = :P_INVOICE_NUM

            AND PRH.REQUISITION_HEADER_ID = PRL.REQUISITION_HEADER_ID

            AND PRL.REQUISITION_LINE_ID = PRD.REQUISITION_LINE_ID

            AND PAH.OBJECT_ID = PRH.REQUISITION_HEADER_ID

            AND PAH.SEQUENCE_NUM = (

                        SELECT MAX(SEQUENCE_NUM)

                        FROM PO_ACTION_HISTORY PAH1

                        WHERE PAH1.OBJECT_ID = PAH.OBJECT_ID

                                    AND PAH1.OBJECT_TYPE_CODE = 'REQ'

                                    AND PAH1.ACTION_CODE = 'APPROVE'

                        )

            AND PRD.DISTRIBUTION_ID = PDA.REQ_DISTRIBUTION_ID

            AND PLA.PO_LINE_ID = PDA.PO_LINE_ID

            AND PLA.PO_HEADER_ID = PDA.PO_HEADER_ID

            AND PLA.PO_HEADER_ID = PHA.PO_HEADER_ID

            AND PDA.PO_DISTRIBUTION_ID = AIDA.PO_DISTRIBUTION_ID

            AND AIDA.INVOICE_ID = AIA.INVOICE_ID

            AND AIDA.OBJECT_VERSION_NUMBER = (

                        SELECT MAX(OBJECT_VERSION_NUMBER)

                        FROM AP_INVOICE_DISTRIBUTIONS_ALL AIDA1

                        WHERE AIDA1.INVOICE_ID = AIDA.INVOICE_ID

                        )

            AND AIPA.CHECK_ID = ACA.CHECK_ID

            AND AIPA.INVOICE_ID = AIA.INVOICE_ID

            AND XDL.SOURCE_DISTRIBUTION_ID_NUM_1 = AIDA.INVOICE_DISTRIBUTION_ID

            AND XDL.AE_HEADER_ID = XAL.AE_HEADER_ID

            AND XDL.AE_LINE_NUM = XAL.AE_LINE_NUM

            AND XAH.AE_HEADER_ID = XAL.AE_HEADER_ID

            AND GJL.JE_LINE_NUM = GIR.JE_LINE_NUM

            AND GJL.JE_HEADER_ID = GIR.JE_HEADER_ID

            AND GIR.GL_SL_LINK_TABLE = XAL.GL_SL_LINK_TABLE

            AND GIR.GL_SL_LINK_ID = XAL.GL_SL_LINK_ID

            AND GJL.JE_HEADER_ID = GJH.JE_HEADER_ID