1. Get rid of all advertisements and get unlimited access to documents by upgrading to Premium Membership. Upgrade to Premium Now and also get a Premium Badge!

R12 Create Accounting Queries for AR

Discussion in 'Oracle Apps Technical' started by saptarsi.goswami, Jun 17, 2015.

  1. saptarsi.goswami

    saptarsi.goswami Active Member

    Messages:
    3
    Likes Received:
    0
    Trophy Points:
    55
    Location:
    St. Paul, MN, United States
    Count of records processed with a particular Request ID:

    Code (SQL):
    ====================================================
    SELECT COUNT(1)
    FROM   apps.XLA_EVENTS
    --where  application_id = 222  
    WHERE    request_id = XXXXXXXX;
    =========================================================
    Count of records processed with a particular Request ID grouped by Event_Type_code

    Code (SQL):
    ===========================================================================
    SELECT event_type_code, COUNT (1)
      FROM   apps.XLA_EVENTS
      WHERE  request_id = XXXXXXXX
      GROUP BY event_type_code;
    ================================================================
    No. Of unprocessed events

    Code (SQL):
    =================================================================
      SELECT event_type_code,
           event_status_code,
           process_status_code,
           COUNT(1)      
    FROM   apps.XLA_EVENTS
    WHERE  application_id = 222
    AND    entity_id IN (SELECT entity_id
                         FROM   xla.xla_transaction_entities xte
                         WHERE  xte.application_id = 222
                         AND    xte.ledger_id = XXXX)
    AND    process_status_code = 'U'
    GROUP BY event_type_code,        
             event_status_code,
             process_status_code;
    ===================================================================
    No of Events Processed by a request

    Code (SQL):
    =========================================================================        
    SELECT event_type_code, TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS'), COUNT (1)
      FROM   apps.XLA_EVENTS
      WHERE  request_id = XXXXXXXX
        --and event_type_code = 'RECP_UPDATE'
        --and event_type_code = 'ADJ_CREATE'
        --and event_type_code not in ('INV_CREATE', 'DM_CREATE')
        GROUP BY event_type_code, TO_CHAR(sysdate,'DD-MON-YYYY HH24:MI:SS');
       
    ==============================================================================
    No of Unprocessed events along with the concurrent Programs that has created those events

    Code (SQL):
    =================================================================================
      SELECT x.event_type_code,
           x.event_status_code,
           x.process_status_code,
           x.creation_date ,
           x.request_id ,
           x.*,
           (SELECT b.user_concurrent_program_name
    FROM apps.fnd_concurrent_requests a ,apps.fnd_concurrent_programs_vl b
    WHERE a.concurrent_program_id = b.concurrent_program_id
    AND a.request_id = x.request_id
    ) Prgram_name
           -- count(1)      
    FROM   apps.XLA_EVENTS x
    WHERE  x.application_id = 222
    AND x.process_status_code ='U'
    AND    x.entity_id IN (SELECT entity_id
                         FROM   xla.xla_transaction_entities xte
                         --WHERE  xte.application_id = 222
                        WHERE    xte.ledger_id = XXXX);
    ==================================================================================