Search code examples
sqljoinoracle11goracle-ebs

How do I convert a user-generated Oracle EBS spreadsheet into a SQL-driven script?

I need to convert a manually-created spreadsheet into a SQL report. The report is generated via Oracle Forms (one of the BI Suite apps).

There's an inbox where many tickets come in to, identified by a "Request Number"

Each Request Number is a help desk ticket in the system. Here is a screenshot of that spreadsheet:

enter image description here

A full listing of columns(with descriptions) :

Request No. 
Modules
Submitted ( the request being submitted , a timestamp )
Supervisor ( the request being approved , by supervisor )
Average Supervisor Approval Duration
Responsibility
Average Resp. Approval Duration
Training (the request being approved for completing training)
Average Training Approval Duration - 
Configuration - depending on whether its PRISM or iProcurement
Average Config. Approval Duration
Approved
Total Overall Duration
Security 
Average Security  Approval Duration
SOD 
Avg SOD Approval Duration

So what I'm trying to do is to generate, some or all, of it using a SQL script.

One of my issues is that .. for a specific "Request Number" I'll often get redundant and conflicting data in the database.

Below is my query so far.

SELECT   hur.reg_request_id AS "Request No", 
     RESPONSIBILITY_NAME AS "Module", 
     to_char(hur.creation_date, 'DD-Mon-YYYY HH24:MI:SS') AS "Submitted" ,  
     to_char(hur.last_update_date,    'DD-Mon-YYYY HH24:MI:SS') AS "Supervisor" ,     
     ROUND((hur.last_update_date - hur.creation_date), 3 ) || 
           ' days' AS "Avg Supervisor Appr Duration" ,          
     /* hura.creation_date AS "Responsibility" */, 
     NULL AS "Avg Resp Appr Duration", NULL AS "Training",     
     NULL AS "Avg Training Appr Duration" , 
     hur.LAST_UPDATE_DATE AS "Security", 
     NULL AS "Avg Security Appr Duration",     
     NULL AS "SOD", 
     NULL AS "Average SOD Approval Duration" , 
     NULL AS "Configuration",      
     NULL AS "Avg Config Appr Duration",       
     hurr.last_update_date AS "Approved", 
     ROUND( hurr.last_update_date - hur.creation_date, 2 )  
              AS  "Total Overall Duration",     
     NULL AS "Notes"           

     FROM HHS_UMX_REG_SERVICES hur     
        JOIN fnd_responsibility_vl frt 
          ON (hur.responsibility_id = frt.responsibility_id and
              hur.responsibility_application_id = frt.application_id)
    JOIN 
         hhs_umx_reg_requests hurr ON 
           (hurr.reg_request_id = hur.reg_request_id )

       /* JOIN hhs_umx_resp_activity  hura */
      left outer JOIN 
          hhs_umx_resp_activity  
          hura ON (hur.reg_request_id = hura.reg_request_id )--ADD ON 

      WHERE EXISTS (
          SELECT  NULL  FROM hhs_umx_resp_activity hura
      WHERE hura.created_by = hur.created_by 
          AND
      hura.creation_date > hur.creation_date)
          AND hur.reg_request_id IN 
           ('263428', '263458', '263473', 
            '264717', '263402', '263404', 
            '262671', '263229', '263268')                        
      ORDER BY hur.reg_request_id ASC

Here are the schemas :

HHS_UMX_RESP_ACTIVITY enter image description here

HHS_UMX_REG_SERVICES enter image description here

fnd_responsibility_vl

enter image description here

**hhs_umx_reg_requests **

enter image description here

thanks

Solution

  • As a simple solution you can just create rows that add the columns together in an SQL command. Something like "INSERT INTO TABLE ('REQ_NO', 'MODULE') VALUES (A2, B2)" etc. Then you can just use the excel function that copies it again and again for each row.

    Another simple solution is to save the spreadsheet as CSV (play around with the exact CSV format to see what suits you) and then you might be able to just import it into your SQL table and play around there.

    There are of course other more advanced solutions like TommCatt pointed out.