Concurrent Request Details

SELECT  /*+ ORDERED */       
        fcr.request_id request_id,
         NVL (fu.description, fu.user_name) requested_by,
         DECODE (fcp.concurrent_program_name,
                 'FNDRSSUB', 'Request Set - ' || fcr.description,
                 fcpt.user_concurrent_program_name)
            concurrent_program,
         DECODE (fcr.phase_code,
                 'I', 'Inactive',
                 'P', DECODE (fcr.hold_flag, 'Y', 'Inactive', 'Pending'),
                 'R', 'Running',
                 'C', 'Complete',
                 fcr.phase_code)
            phase,
         DECODE (
            fcr.status_code,
            'U', 'Disabled',
            'W', 'Paused',
            'X', 'Terminated',
            'Z', 'Waiting',
            'M', 'No Manager',
            'Q', 'Standby',
            'R', 'Normal',
            'S', 'Suspended',
            'T', 'Terminating',
            'D', 'Cancelled',
            'E', 'Error',
            'F', 'Scheduled',
            'G', 'Warning',
            'H', 'On Hold',
            'I', CASE
                    WHEN fcr.request_date < fcr.requested_start_date
                    THEN
                       'Scheduled'
                    ELSE
                       'Normal'
                 END,
            'A', 'Waiting',
            'B', 'Resuming',
            'C', 'Normal',
            fcr.status_code)
            status,
         fcr.argument_text,
         TO_CHAR (fcr.request_date, 'DD-Mon-RRRR HH12:MI:SS AM') date_requested,
         TO_CHAR (fcr.requested_start_date, 'DD-Mon-RRRR HH12:MI:SS AM')
            requested_start_date,
         TO_CHAR (fcr.actual_start_date, 'DD-Mon-RRRR HH12:MI:SS AM')
            date_started,
         TO_CHAR (fcr.actual_completion_date, 'DD-Mon-RRRR HH12:MI:SS AM')
            date_completed,
         fcr.oracle_process_id,
         fcr.os_process_id,
         fcr.logfile_node_name || ': ' || fcr.logfile_name logfile_name,
         fcr.outfile_node_name || ': ' || fcr.outfile_name output_name
    FROM fnd_concurrent_requests fcr,
         fnd_concurrent_programs fcp,
         fnd_concurrent_programs_tl fcpt,
         fnd_user fu
   WHERE     fcr.concurrent_program_id = fcp.concurrent_program_id
         AND fcp.concurrent_program_id = fcpt.concurrent_program_id
         AND fcr.program_application_id = fcp.application_id
         AND fcp.application_id = fcpt.application_id
         AND fcr.requested_by = fu.user_id
         AND fcpt.language = 'US'
ORDER BY fcr.request_date DESC

To find Responsibilities having a particular Concurrent Program

SELECT frt.responsibility_name, fcpt.user_concurrent_program_name
    FROM fnd_concurrent_programs fcp,
         fnd_concurrent_programs_tl fcpt,
         fnd_request_group_units frgu,
         fnd_responsibility fr,
         fnd_responsibility_tl frt
   WHERE     fcp.concurrent_program_id = fcpt.concurrent_program_id
         AND fcp.application_id = fcpt.application_id
         AND fcp.concurrent_program_id = frgu.request_unit_id(+)
         AND frgu.unit_application_id(+) = fcp.application_id
         AND frgu.request_group_id = fr.request_group_id(+)
         AND fr.responsibility_id = frt.responsibility_id(+)
         AND fr.application_id = frt.application_id(+)
         AND frgu.request_unit_type(+) = 'P'
         AND frt.language(+) = 'US'
         AND fcpt.language = 'US'
         AND fcpt.user_concurrent_program_name LIKE '%Trial Balance All Entities Mapping Report%'
ORDER BY frt.responsibility_name

Concurrent Programs details

SELECT fcp.concurrent_program_id,

       fcp.concurrent_program_name,

       fcpt.user_concurrent_program_name,

       fcpt.description,

       fe.executable_name,

       fet.user_executable_name,

       fe.execution_file_name

  FROM apps.fnd_concurrent_programs fcp,

       apps.fnd_concurrent_programs_tl fcpt,

       apps.fnd_executables fe,

       apps.fnd_executables_tl fet

 WHERE     fe.executable_id = fet.executable_id

       AND fcp.concurrent_program_id = fcpt.concurrent_program_id

       AND fcpt.language = fet.language

       AND fcp.executable_id = fe.executable_id

       AND fcp.executable_application_id = fe.application_id

       AND fcpt.language = 'US'

       AND fcpt.user_concurrent_program_name LIKE

              'Averda Trial Balance All Entities Mapping Report'

Trial Balance All Entities Report Internal 12.2.7

Query As per Standard

For YTD :

:CURRENCY_TYPE = 'T';
:AMOUNT_YPE = 'YTD'
:FIRST_PERIOD = 'Jan-19'
:PERIOD_NAME : = 'Dec-19'


This is for  ACTUAL_FLAG = 'A' - Actual - below coding




select * from (SELECT /*+ ORDERED */
L.LEDGER_ID , L.Name,cc.segment1||'-'||cc.segment2||'-'||cc.SEGMENT3||'-'||SEGMENT4||'-'||cc.segment5||'-'||cc.segment6||'-'||cc.segment7 Code_combinations,
GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(51515,3,cc.SEGMENT3) ACCOUNT_DESCRIPTION,
  DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), 'S', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), NVL(BEGIN_BALANCE_DR_BEQ, 0) - NVL(BEGIN_BALANCE_CR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', 0, 'S', 0, 'E', 0)), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR_BEQ, 0) - NVL(BEGIN_BALANCE_CR_BEQ, 0), 0))))) BEGIN_BALANCE,
  DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PERIOD_NET_DR, 0), 'S', NVL(PERIOD_NET_DR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PERIOD_NET_DR, 0), NVL(PERIOD_NET_DR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), 'S', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), NVL(PROJECT_TO_DATE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ, 0)))), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR_BEQ, 0), 0))))) PERIOD_DR,
  DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PERIOD_NET_CR, 0), 'S', NVL(PERIOD_NET_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PERIOD_NET_CR, 0), NVL(PERIOD_NET_CR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), 'S', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), NVL(PROJECT_TO_DATE_CR_BEQ, 0) + NVL(PERIOD_NET_CR_BEQ, 0)))), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR_BEQ, 0) + NVL(PERIOD_NET_CR_BEQ, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR_BEQ, 0), 0))))) PERIOD_CR ,
  DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), 'S', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(BEGIN_BALANCE_DR, 0) - NVL(BEGIN_BALANCE_CR, 0), NVL(BEGIN_BALANCE_DR_BEQ, 0) - NVL(BEGIN_BALANCE_CR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', 0, 'S', 0, 'E', 0)), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0)     - NVL(BEGIN_BALANCE_CR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR_BEQ, 0) - NVL(BEGIN_BALANCE_CR_BEQ, 0), 0))))) + DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PERIOD_NET_DR, 0), 'S', NVL(PERIOD_NET_DR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PERIOD_NET_DR, 0), NVL(PERIOD_NET_DR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), 'S', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PROJECT_TO_DATE_DR, 0) + NVL(PERIOD_NET_DR, 0), NVL(PROJECT_TO_DATE_DR_BEQ, 0) + NVL(PERIOD_NET_DR_BEQ, 0)))), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR, 0) + NVL(PERIOD_NET_DR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_DR_BEQ, 0)          + NVL(PERIOD_NET_DR_BEQ, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_DR_BEQ, 0), 0))))) - DECODE(
  :AMOUNT_YPE, 'PTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PERIOD_NET_CR, 0), 'S', NVL(PERIOD_NET_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PERIOD_NET_CR, 0), NVL(PERIOD_NET_CR_BEQ, 0)))), 'PJTD', SUM(DECODE(
  :CURRENCY_TYPE, 'T', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), 'S', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), 'E', DECODE(BAL.TRANSLATED_FLAG, 'R', NVL(PROJECT_TO_DATE_CR, 0) + NVL(PERIOD_NET_CR, 0), NVL(PROJECT_TO_DATE_CR_BEQ, 0) + NVL(PERIOD_NET_CR_BEQ, 0)))), 'YTD', DECODE(
  :CURRENCY_TYPE, 'T', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0)), 'S', SUM(DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0)), 'E', SUM(DECODE(BAL.TRANSLATED_FLAG, 'R', DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR, 0) + NVL(PERIOD_NET_CR, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR, 0), 0), DECODE(BAL.PERIOD_NAME,
  :PERIOD_NAME, NVL(BEGIN_BALANCE_CR_BEQ, 0) + NVL(PERIOD_NET_CR_BEQ, 0), 0) - DECODE(BAL.PERIOD_NAME,
  :FIRST_PERIOD, NVL(BEGIN_BALANCE_CR_BEQ, 0), 0))))) ENDING_BALANCE
FROM GL_BALANCES BAL,
  GL_CODE_COMBINATIONS CC,
  GL_LEDGERS L,
  GL_LEDGER_SET_ASSIGNMENTS ASG,
  GL_LEDGER_RELATIONSHIPS LR
WHERE BAL.ACTUAL_FLAG = 'A'
AND BAL.CURRENCY_CODE = (select CURRENCY_CODE from gl_ledgers where ledger_id  = l.ledger_id)--nvl(:ENTERED_CURRENCY,BAL.CURRENCY_CODE)
AND BAL.PERIOD_NAME IN (
  :PERIOD_NAME, DECODE(
  :AMOUNT_YPE, 'PTD',
  :PERIOD_NAME, 'PJTD',
  :PERIOD_NAME, 'YTD',
  :FIRST_PERIOD))
AND BAL.CODE_COMBINATION_ID = CC.CODE_COMBINATION_ID
  -- AND CC.CHART_OF_ACCOUNTS_ID = &STRUCT_NUM
AND CC.TEMPLATE_ID IS NULL
AND CC.SUMMARY_FLAG = 'N'
AND L.LEDGER_ID     = nvl(:P_LEDGER_ID,L.LEDGER_ID)
AND ASG.LEDGER_SET_ID(+)    = L.LEDGER_ID
AND LR.TARGET_LEDGER_ID     = NVL(ASG.LEDGER_ID, L.LEDGER_ID)
AND LR.SOURCE_LEDGER_ID     = NVL(ASG.LEDGER_ID, L.LEDGER_ID)
AND LR.TARGET_CURRENCY_CODE =  (select TARGET_CURRENCY_CODE from GL_LEDGER_RELATIONSHIPS where PRIMARY_LEDGER_ID = l.ledger_id and TARGET_LEDGER_SHORT_NAME = l.SHORT_NAME) --nvl(:LEDGER_CURRENCY,LR.TARGET_CURRENCY_CODE)--nvl(:LEDGER_CURRENCY,LR.TARGET_CURRENCY_CODE)
AND LR.SOURCE_LEDGER_ID = BAL.LEDGER_ID
AND LR.TARGET_LEDGER_ID = BAL.LEDGER_ID
 and  nvl(bal.translated_flag, 'x') != 'R'
 --and BAL.LEDGER_ID IN ( SELECT acc.ledger_id FROM gl_access_set_ledgers acc WHERE acc.access_set_id = 1521 )
 --and CC.SEGMENT1 = :SEGMENT1_LOW--  and cc.segment3 = '1121024'
GROUP BY cc.SEGMENT4 , cc.SEGMENT3 , cc.segment1,L.LEDGER_ID ,cc.segment5,cc.segment6,cc.segment7,cc.segment2,L.Name
ORDER BY L.LEDGER_ID , segment3
) where nvl(BEGIN_BALANCE,0) != 0 or nvl(PERIOD_DR,0) != 0 or nvl(PERIOD_CR,0) != 0 or nvl(ENDING_BALANCE,0) != 0