System Analysis Queries¶
Based on your fnshrev and delivery_summary code, run these in order:
1. Check your EmailDelivery task setup¶
2. See the EmailDelivery parameters (MaxAttachments should be 3)¶
SELECT * FROM task_parameter_details
WHERE task_seq = (SELECT task_seq FROM task_parameters WHERE task_name = 'Email Delivery')
ORDER BY slot;
3. Find jobs that use RPTDELJOB (from your task setup - job_type = 'RPTDELJOB')¶
SELECT job_name, job_action, enabled, repeat_interval, comments
FROM user_scheduler_jobs
WHERE job_action LIKE '%RPTDELJOB%'
OR job_action LIKE '%EmailDelivery%'
OR job_action LIKE '%hui_utility.SendEmail%';
4. Check report_delivery table structure (from fnshrev code)¶
5. See current report_delivery entries¶
SELECT delivery_seq, delivery_id, delivery_method, delivery_type, status,
comp_date, filename, addr_seq, cust_id
FROM report_delivery
WHERE delivery_method = 'E' -- Email from your fnshrev code
ORDER BY delivery_seq DESC
FETCH FIRST 10 ROWS ONLY;
6. Check hui_utility package (used in your delivery_summary)¶
SELECT text FROM user_source
WHERE name = 'HUI_UTILITY'
AND type = 'PACKAGE BODY'
AND UPPER(text) LIKE '%SENDEMAIL%'
ORDER BY line;
7. Look for any scheduled jobs that process report_delivery¶
SELECT job_name, program_name, job_action, enabled, repeat_interval
FROM user_scheduler_jobs
WHERE UPPER(job_action) LIKE '%REPORT_DELIVERY%'
OR UPPER(job_action) LIKE '%HUI_UTILITY%'
OR UPPER(job_action) LIKE '%SENDEMAIL%';
8. Check delivery_summary function parameters (you use this)¶
SELECT text FROM user_source
WHERE name = 'DELIVERY_SUMMARY'
AND type = 'FUNCTION'
AND line <= 20
ORDER BY line;
9. See if there are jobs using your delivery_summary function¶
SELECT job_name, job_action
FROM user_scheduler_jobs
WHERE UPPER(job_action) LIKE '%DELIVERY_SUMMARY%';
10. Check samples table for the column you mentioned¶
11. See sample data with your template column¶
SELECT hsn, column_template_to_use, COUNT(*)
FROM samples
WHERE column_template_to_use IS NOT NULL
GROUP BY hsn, column_template_to_use
ORDER BY COUNT(*) DESC
FETCH FIRST 10 ROWS ONLY;
12. Check map_to_hdm table (from fnshrev code)¶
13. Look for any tables that might queue EmailDelivery jobs¶
SELECT table_name FROM user_tables
WHERE table_name IN (
'EMAIL_QUEUE', 'JOB_QUEUE', 'TASK_QUEUE',
'DELIVERY_QUEUE', 'EMAIL_JOBS', 'RPTDELJOB'
);
14. Check sequences used in your code¶
SELECT sequence_name, last_number FROM user_sequences
WHERE sequence_name IN ('DELIVERY_SEQ', 'TASK_SEQ', 'HDM_SEQ');