in

#!/bin/bash

# Set the environment
cd $HOME
source Ebsapps.env run

# Define variables
REPORT_FILE="objects_report_$(date +%Y%m%d_%H%M%S).html"
ORACLE_USER="apps"
ORACLE_PASS="mad15max"
ORACLE_SID=$(echo $TWO_TASK)  # Assumes TWO_TASK is set in Ebsapps.env

# SQL query to fetch data from DBA_OBJECTS and AD_OBJECTS
SQL_QUERY=$(cat << 'EOF'
SET HEADING OFF
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINESIZE 500
SET ECHO OFF
SET MARKUP HTML ON TABLE "border='1' cellpadding='5' cellspacing='0' style='border-collapse: collapse; width: 80%; margin: 20px auto;'"
SPOOL dba_ad_objects.html

SELECT '<h2 style="color: #2E7D32; text-align: center;">Database Objects Report - ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || '</h2>' FROM dual;

SELECT '<table>' ||
       '<tr style="background-color: #4CAF50; color: white;">' ||
       '<th>Owner</th><th>Object Name</th><th>Object Type</th><th>Status</th><th>Created</th>' ||
       '</tr>'
FROM dual;

SELECT CASE
         WHEN status = 'VALID' THEN '<tr style="background-color: #E8F5E9;">'
         ELSE '<tr style="background-color: #FFEBEE;">'
       END ||
       '<td>' || owner || '</td>' ||
       '<td>' || object_name || '</td>' ||
       '<td>' || object_type || '</td>' ||
       '<td>' || status || '</td>' ||
       '<td>' || TO_CHAR(created, 'DD-MON-YYYY') || '</td>' ||
       '</tr>'
FROM dba_objects
WHERE owner NOT IN ('SYS', 'SYSTEM')
  AND rownum <= 50;  -- Limit to 50 rows for demo; adjust as needed

SELECT '<h2 style="color: #0277BD; text-align: center;">EBS AD Objects Report</h2>' FROM dual;

SELECT '<table>' ||
       '<tr style="background-color: #0288D1; color: white;">' ||
       '<th>Object Name</th><th>Object Type</th><th>Status</th><th>Last DDL Time</th>' ||
       '</tr>'
FROM dual;

SELECT CASE
         WHEN status = 'VALID' THEN '<tr style="background-color: #E3F2FD;">'
         ELSE '<tr style="background-color: #FFEBEE;">'
       END ||
       '<td>' || object_name || '</td>' ||
       '<td>' || object_type || '</td>' ||
       '<td>' || status || '</td>' ||
       '<td>' || TO_CHAR(last_ddl_time, 'DD-MON-YYYY HH24:MI:SS') || '</td>' ||
       '</tr>'
FROM ad_objects
WHERE rownum <= 50;  -- Limit to 50 rows for demo; adjust as needed

SELECT '</table>' FROM dual;

SELECT '<p style="text-align: center; color: #555;">Report generated on ' || TO_CHAR(SYSDATE, 'DD-MON-YYYY HH24:MI:SS') || ' by Grok 3 (xAI)</p>' FROM dual;

SPOOL OFF
EXIT
EOF
)

# Execute the SQL query and generate the HTML report
echo "$SQL_QUERY" | sqlplus -S $ORACLE_USER/$ORACLE_PASS@$ORACLE_SID

# Move and rename the output file
mv dba_ad_objects.html $REPORT_FILE

# Check if the report was generated successfully
if [ -f "$REPORT_FILE" ]; then
    echo "Report generated successfully: $REPORT_FILE"
else
    echo "Error: Failed to generate the report."
    exit 1
fi

exit 0