Query to Check Expense Line Distribution Mismatch Issue

alter session set current_schema=apps;

DECLARE

TYPE analysis_type IS TABLE OF VARCHAR2(1000);

analysis_list analysis_type := analysis_type();

p_report_header_id NUMBER:= :report_header_id;

CURSOR lines_cursor IS
    SELECT aerl.report_line_id, aerl.amount, aerl.itemization_parent_id,
        aerl.currency_code, aerl.distribution_line_number, aerl.web_parameter_id,
        aerl.daily_amount, aerl.receipt_currency_amount, aep.prompt, aerl.credit_card_trx_id, aerl.allocation_split_code, aerl.org_id
    FROM ap_expense_report_lines_all aerl,
        ap_expense_report_params_all aep
    WHERE aerl.report_header_id = p_report_header_id
    AND aerl.web_parameter_id = aep.parameter_id;

CURSOR dists_cursor IS
    SELECT report_distribution_id, report_line_id, amount, org_id, code_combination_id,
        project_id, task_id, preparer_modified_flag
    FROM ap_exp_report_dists_all
    WHERE report_header_id = p_report_header_id;

l_header_total            ap_expense_report_headers_all.total%TYPE;
l_header_org            ap_expense_report_headers_all.org_id%TYPE;
l_expense_status_code        ap_expense_report_headers_all.expense_status_code%TYPE;
l_workflow_approved_flag    ap_expense_report_headers_all.workflow_approved_flag%TYPE;
l_amt_due_employee        ap_expense_report_headers_all.amt_due_employee%TYPE;
l_amt_due_ccard_company        ap_expense_report_headers_all.amt_due_ccard_company%TYPE;
l_reject_code            ap_expense_report_headers_all.reject_code%TYPE;
l_line_id1            ap_expense_report_lines_all.report_line_id%TYPE;
l_line_id2            ap_expense_report_lines_all.report_line_id%TYPE;
l_line_id3            ap_expense_report_lines_all.report_line_id%TYPE;
l_analysis_count        INTEGER := 0;
l_item_count            NUMBER := 0;
l_item_amount            NUMBER := 0;
l_dist_count            NUMBER := 0;
l_dist_amount            NUMBER := 0;
l_line_total            NUMBER := 0;
l_dist_total            NUMBER := 0;
l_debug_msg            VARCHAR2(1000);

BEGIN
    DBMS_OUTPUT.PUT_LINE('*********************Expense Report Data Analysis*******************************');
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('****************************************IMPORTANT********************************************************');
    DBMS_OUTPUT.PUT_LINE('THIS REPORT IS FOR INFOMATION PURPOSE ONLY, NO ACTION SHOULD BE TAKEN ON THE DATA BASED ON THIS REPORT.  ');
    DBMS_OUTPUT.PUT_LINE('     Please run the following sqls and provide the data to development.                                  ');
    DBMS_OUTPUT.PUT_LINE('     1) select * from ap_expense_report_headers_all where report_header_id = '||p_report_header_id||'    ');
    DBMS_OUTPUT.PUT_LINE('     2) select * from ap_expense_report_lines_all where report_header_id = '||p_report_header_id||'      ');
    DBMS_OUTPUT.PUT_LINE('     3) select * from ap_exp_report_dists_all where report_header_id = '||p_report_header_id||'          ');
    DBMS_OUTPUT.PUT_LINE('     4) select * from ap_credit_card_trxns_all where report_header_id = '||p_report_header_id||'         ');
    DBMS_OUTPUT.PUT_LINE('*********************************************************************************************************');
    DBMS_OUTPUT.PUT_LINE('');
    l_analysis_count := l_analysis_count + 1;
    analysis_list.extend;
    analysis_list(l_analysis_count) := '==Analysis==';

    l_debug_msg := 'Fetching Header Data';

    SELECT total, reject_code,
        expense_status_code,
        workflow_approved_flag,
        amt_due_employee,
        amt_due_ccard_company,
        org_id
    INTO l_header_total, l_reject_code, l_expense_status_code,
        l_workflow_approved_flag, l_amt_due_employee,
        l_amt_due_ccard_company, l_header_org
    FROM ap_expense_report_headers_all
    WHERE report_header_id = p_report_header_id;
   
    l_debug_msg := 'Printing Header Data';

    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('==Header Data==');
    DBMS_OUTPUT.PUT_LINE('Report Header Id : *'||p_report_header_id||'*');
    DBMS_OUTPUT.PUT_LINE('Header ORG : *'||l_header_org||'*');
    DBMS_OUTPUT.PUT_LINE('Total : *'||l_header_total||'*');
    DBMS_OUTPUT.PUT_LINE('Expense Status Code : *'||l_expense_status_code||'*');
    DBMS_OUTPUT.PUT_LINE('Reject Code : *'||l_reject_code||'*');
    DBMS_OUTPUT.PUT_LINE('Workflow Approved Flag : *'||l_workflow_approved_flag||'*');
    DBMS_OUTPUT.PUT_LINE('Amount Due Employee : *'||l_amt_due_employee||'*');
    DBMS_OUTPUT.PUT_LINE('Amount Due Card Company : *'||l_amt_due_ccard_company||'*');
 
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('==Line Data==');
   
    l_debug_msg := 'Looping Line Data';
    FOR line_rec in lines_cursor
    LOOP
        l_debug_msg := 'Printing Line Data';
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('Report Line Id : *'||line_rec.report_line_id||'*');
        DBMS_OUTPUT.PUT_LINE('Report Line Org : *'||line_rec.org_id||'*');
        DBMS_OUTPUT.PUT_LINE('Expense Type : *'||line_rec.prompt||'*');
        DBMS_OUTPUT.PUT_LINE('Line Amount : *'||line_rec.amount||'*');
        DBMS_OUTPUT.PUT_LINE('Line Currency : *'||line_rec.currency_code||'*');
        DBMS_OUTPUT.PUT_LINE('Dist Line Number : *'||line_rec.distribution_line_number||'*');
        DBMS_OUTPUT.PUT_LINE('Daily Amount : *'||line_rec.daily_amount||'*');
        DBMS_OUTPUT.PUT_LINE('Receipt Amount : *'||line_rec.receipt_currency_amount||'*');
        DBMS_OUTPUT.PUT_LINE('Credit Card Trx Id : *'||line_rec.credit_card_trx_id||'*');
        DBMS_OUTPUT.PUT_LINE('Allocation Split Code : *'||line_rec.allocation_split_code||'*');
        DBMS_OUTPUT.PUT_LINE('Itmization Parent Id : *'||line_rec.itemization_parent_id||'*');       
       
        IF (line_rec.distribution_line_number = line_rec.report_line_id) THEN
            DBMS_OUTPUT.PUT_LINE('The line was potentially itemized in Audit ');   
        END IF;
        IF (line_rec.amount IS NULL) THEN
            l_analysis_count := l_analysis_count + 1;
            analysis_list.extend;
            analysis_list(l_analysis_count) := '' || line_rec.report_line_id || ': Line Amount is null';
        END IF;

        IF(line_rec.itemization_parent_id IS NOT NULL AND line_rec.itemization_parent_id = -1) THEN
            l_item_count := 0;
            l_item_amount := 0;
            SELECT COUNT(*), SUM(amount) INTO l_item_count, l_item_amount
            FROM ap_expense_report_lines_all WHERE itemization_parent_id = line_rec.report_line_id;

            DBMS_OUTPUT.PUT_LINE('Total Child Lines *'||l_item_count||'*');       

            IF (l_item_count = 0) THEN
                l_analysis_count := l_analysis_count + 1;
                analysis_list.extend;
                analysis_list(l_analysis_count) := 'Line ' || line_rec.report_line_id || ': Itemized Line with No Child Lines';
            END IF;

            IF (nvl(l_item_amount, -9999) > nvl(line_rec.amount, -9999)) THEN
                l_analysis_count := l_analysis_count + 1;
                analysis_list.extend;
                analysis_list(l_analysis_count) := 'Line ' || line_rec.report_line_id || ': Itemized total more than parent';
            END IF;

            IF (nvl(l_item_amount, -9999) < nvl(line_rec.amount, -9999)) THEN
                l_analysis_count := l_analysis_count + 1;
                analysis_list.extend;
                analysis_list(l_analysis_count) := 'Line ' || line_rec.report_line_id || ': Personal Itemized lines potentially exist';
            END IF;
        END IF;
       
        l_dist_count := 0;
        l_dist_amount := 0;
       
        IF (line_rec.itemization_parent_id IS NULL OR line_rec.itemization_parent_id <> -1) THEN
            l_debug_msg := 'Fetching Dist count and totals for line ' || line_rec.report_line_id;

            SELECT COUNT(*) INTO l_dist_count FROM ap_exp_report_dists_all WHERE report_line_id = line_rec.report_line_id;
       
            IF (l_dist_count > 0) THEN           
                SELECT SUM(AMOUNT) INTO l_dist_amount FROM ap_exp_report_dists_all WHERE report_line_id = line_rec.report_line_id;
            END IF;

            IF (l_dist_count = 0) THEN
                l_analysis_count := l_analysis_count + 1;
                analysis_list.extend;
                analysis_list(l_analysis_count) := 'Line ' || line_rec.report_line_id || ': No Distributions';
            END IF;

            IF (line_rec.allocation_split_code IS NULL AND l_dist_count > 1) THEN
                l_analysis_count := l_analysis_count + 1;
                analysis_list.extend;
                analysis_list(l_analysis_count) := 'Line ' || line_rec.report_line_id || ': Multiple distributions exists for line, Allocation Split Code is NULL';
            END IF;

            DBMS_OUTPUT.PUT_LINE('Total Distributions *'||l_dist_count||'*');
            DBMS_OUTPUT.PUT_LINE('Distribution Total *'||l_dist_amount||'*');
           
            IF (nvl(l_dist_amount, -9999) <> nvl(line_rec.amount, -9999)) THEN
                l_analysis_count := l_analysis_count + 1;
                analysis_list.extend;
                analysis_list(l_analysis_count) := 'Line ' || line_rec.report_line_id || ': Line and Distribution amounts do not match';
            END IF;
        END IF;
    END LOOP;

    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('==Dist Data==');

    FOR dists_rec IN dists_cursor
    LOOP
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('Report Dist Id : *'||dists_rec.report_distribution_id||'*');
        DBMS_OUTPUT.PUT_LINE('Report Line Id : *'||dists_rec.report_line_id||'*');
        DBMS_OUTPUT.PUT_LINE('Dist Org Id : *'||dists_rec.org_id||'*');
        DBMS_OUTPUT.PUT_LINE('Dist Amount : *'||dists_rec.amount||'*');
        DBMS_OUTPUT.PUT_LINE('Dist CCID : *'||dists_rec.code_combination_id||'*');
        DBMS_OUTPUT.PUT_LINE('Dist Project Id : *'||dists_rec.project_id||'*');
        DBMS_OUTPUT.PUT_LINE('Dist Task Id : *'||dists_rec.task_id||'*');
        DBMS_OUTPUT.PUT_LINE('Dist Preparer Modified : *'||dists_rec.preparer_modified_flag||'*');
       
        IF (dists_rec.amount IS NULL) THEN
            l_analysis_count := l_analysis_count + 1;
            analysis_list.extend;
            analysis_list(l_analysis_count) := 'Line ' || dists_rec.report_line_id || ':Dist: ' || dists_rec.report_distribution_id || ': Distribution Amount is Null';
        END IF;

    END LOOP;
   
    l_debug_msg := 'Fetching line total for final processing';
    SELECT SUM(amount) INTO l_line_total FROM ap_expense_report_lines_all WHERE report_header_id = p_report_header_id and (itemization_parent_id IS NULL OR itemization_parent_id <> -1);

    l_debug_msg := 'Fetching dist total for final processing';
    SELECT SUM(amount) INTO l_dist_total FROM ap_exp_report_dists_all WHERE report_header_id = p_report_header_id;

    IF (nvl(l_header_total, -9999) <> nvl(l_line_total, -9999)) THEN
        l_analysis_count := l_analysis_count + 1;
        analysis_list.extend;
        analysis_list(l_analysis_count) := 'Header and Line Totals do not match';
        BEGIN
            SELECT report_line_id INTO l_line_id1 FROM ap_expense_report_lines_all WHERE ROUND(amount) = ROUND(l_line_total - l_header_total);
            l_analysis_count := l_analysis_count + 1;
            analysis_list.extend;
            analysis_list(l_analysis_count) := '1. Line ID ' || l_line_id1 || ' might be problemetic';
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
        BEGIN
            SELECT report_line_id INTO l_line_id2 FROM ap_expense_report_lines_all WHERE amount = (l_line_total - l_header_total);
            l_analysis_count := l_analysis_count + 1;
            analysis_list.extend;
            analysis_list(l_analysis_count) := '2. Line ID ' || l_line_id2 || ' might be problemetic';
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
        BEGIN
            SELECT report_line_id INTO l_line_id3 FROM ap_expense_report_lines_all WHERE amount = (l_header_total - l_line_total);
            l_analysis_count := l_analysis_count + 1;
            analysis_list.extend;
            analysis_list(l_analysis_count) := '3. Line ID ' || l_line_id3 || ' might be problemetic';
        EXCEPTION
            WHEN OTHERS THEN
                NULL;
        END;
    END IF;

    IF (nvl(l_header_total, -9999) <> nvl(l_dist_total, -9999)) THEN
        l_analysis_count := l_analysis_count + 1;
        analysis_list.extend;
        analysis_list(l_analysis_count) := 'Header and Distribution Totals do not match';
    END IF;

    IF (nvl(l_line_total, -9999) <> nvl(l_dist_total, -9999)) THEN
        l_analysis_count := l_analysis_count + 1;
        analysis_list.extend;
        analysis_list(l_analysis_count) := 'Line and Distribution Totals do not match';
    END IF;
   
    l_debug_msg := 'Printing Final';
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('Header Total :*'||l_header_total||'*');
    DBMS_OUTPUT.PUT_LINE('Line Total :*'||l_line_total||'*');
    DBMS_OUTPUT.PUT_LINE('Dist Total :*'||l_dist_total||'*');
    DBMS_OUTPUT.PUT_LINE('');
    DBMS_OUTPUT.PUT_LINE('');
    FOR i IN 1 .. analysis_list.count LOOP
        dbms_output.put_line(analysis_list(i));
    END LOOP;
   

    IF (analysis_list.count = 1) THEN
        DBMS_OUTPUT.PUT_LINE('Data appears to be valid');
    END IF;

    DBMS_OUTPUT.PUT_LINE('');
   
   
    DBMS_OUTPUT.PUT_LINE('*********************************************************************************');
EXCEPTION
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('');
        DBMS_OUTPUT.PUT_LINE('An Exception Occurred while ' || l_debug_msg);   
        DBMS_OUTPUT.PUT_LINE('Exception:  ' || SQLERRM);
END;