On January 27th, this site will be read-only as we migrate to Oracle Forums for an improved community experience. You will not be able to initiate activity until January 30th, when you will be able to use this site as normal.

    Forum Stats

  • 3,889,585 Users
  • 2,269,760 Discussions
  • 7,916,785 Comments

Discussions

SQL statement to get all PROCESS_ID's belonging to a batch's PROCESS_ID

samlambert
samlambert Member Posts: 39 Green Ribbon
edited Apr 9, 2020 11:47AM in Financial Data Management

I have an SQL/Jython function in FDMEE that returns the process_id of a batch. The batch has load rules associated with it. I want to execute an SQL statement that get's the PROCESS_ID's of the load rules that are in the batch. I'm executing the script on a server so I can't just say SELECT the PROCESS_ID minus 2 and 1. I'm not sure what tables to query in FDMEE, or what would be the most efficient. Any help would be much appreciated!

Best Answer

Answers

  • SH_INT
    SH_INT Member Posts: 3,193 Bronze Crown
    edited Apr 2, 2020 10:15AM Answer ✓

    You need to use a combination of data in the following tables AIF_BATCH_LOAD_AUDIT, AIF_PROCESSES and AIF_BALANCE_RULES

  • user6692921
    user6692921 Member Posts: 179 Silver Badge
    edited Apr 2, 2020 12:32PM

    I think this might do what you want:

    SELECT     LOADID

    FROM         AIF_BATCH_LOAD_AUDIT AS ABLA1

    WHERE     (PARENT_BATCH_LOADID =

                              (SELECT     MAX(BATCH_LOADID) AS Expr1

                                FROM          AIF_BATCH_LOAD_AUDIT AS ABLA2

                                WHERE      (BATCH_ID =

                                                           (SELECT     BATCH_ID

                                                             FROM          AIF_BATCHES

                                                             WHERE      (BATCH_NAME = 'BATCHNAME')))))