create or replace PACKAGE BODY "sample_pkg" AS

    PROCEDURE initiate_process (
        p_integration_run_id NUMBER
    ) AS

        l_invoice_id    NUMBER;
        l_invoice_num   VARCHAR2(240);
        l_source        VARCHAR2(100);
        l_error_message VARCHAR2(4000);
        ln_count        NUMBER;
        ln_hold_count   NUMBER;
      

        CURSOR cur_inv IS
        SELECT
            invoice_id,
            vendor_id,
            invoice_num,
            invoice_currency_code,
            source,
            invoice_amount,
            invoice_date,
            approval_status,
            org_id,
            vendor_number,
            hold_status
        FROM
            mpl_manual_entry_invoice_tl
        WHERE
                integration_run_id = p_integration_run_id
            AND is_duplicate IS NULL
            AND hold_status IS NULL;

    BEGIN
        FOR l_inv IN cur_inv LOOP
            BEGIN
                SELECT
                    COUNT(*)
                INTO ln_count
                FROM
                      
                       ( SELECT
                            aia.invoice_num
                        FROM
                            apps.ap_invoices_all aia,
                            apps.ap_suppliers sup
                           
                        WHERE
                                1 = 1
                            AND aia.invoice_num = l_inv.invoice_num
                            and aia.vendor_id=sup.vendor_id
                            and sup.segment1=l_inv.vendor_number
                            --AND vendor_id = l_inv.vendor_number
                    );

                SELECT
                    COUNT(*)
                INTO ln_hold_count
                FROM
                    mpl_manual_entry_invoice_tl
                WHERE
                        invoice_num = l_inv.invoice_num
                    AND hold_status = 'HOLD'
                    AND is_duplicate = 'YES';

                IF
                    ln_count > 0
                    AND ln_hold_count > 0
                THEN
                    UPDATE mpl_manual_entry_invoice_tl
                    SET
                        hold_status = 'HOLD_RELEASED',
                        is_duplicate = 'YES'
                    WHERE
                            invoice_num = l_inv.invoice_num
                        AND integration_run_id = p_integration_run_id;

                ELSIF
                    ln_count > 0
                    AND ln_hold_count = 0
                THEN
                    UPDATE mpl_manual_entry_invoice_tl
                    SET
                        is_duplicate = 'YES'
                    WHERE
                            invoice_num = l_inv.invoice_num
                        AND integration_run_id = p_integration_run_id;

                ELSE
                    UPDATE mpl_manual_entry_invoice_tl
                    SET
                        is_duplicate = 'NO'
                    WHERE
                            invoice_num = l_inv.invoice_num
                        AND integration_run_id = p_integration_run_id;

                    COMMIT;
                END IF;

            END;
        END LOOP;
    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line(sqlerrm);
            l_error_message := 'FATAL ERROR Inside initiate_process getiNVOICENUMBER: '
                               || substr(sqlerrm, 1, 230);
            dbms_output.put_line('l_error_message: ' || l_error_message);
            --update_status(p_integration_run_id, 'FAILED', NULL, l_error_message);
    END initiate_process;

    PROCEDURE invoke_async (
        p_integration_run_id IN NUMBER
       -- p_status             OUT VARCHAR2
    ) IS

        lv_plsqlblock VARCHAR2(1000);
        lv_job_name   VARCHAR2(65);
        l_error_msg   VARCHAR2(1000);
        l_status      VARCHAR2(10);
    BEGIN
        lv_plsqlblock := 'BEGIN '
                         || 'sample_pkg.initiate_process ('
                         || p_integration_run_id
                         || '); '
                         || 'END;';
        lv_job_name := 'MPL_INV_DUPLICATE_INV_' || p_integration_run_id;
        dbms_scheduler.create_job(job_name => lv_job_name, job_type => 'PLSQL_BLOCK', job_action => lv_plsqlblock, start_date => sysdate
        , enabled => true,
                                 auto_drop => true, comments => lv_job_name);

        COMMIT;
    EXCEPTION
        WHEN OTHERS THEN
            --p_status := 'ERROR';
            RAISE;
    END invoke_async;

END sample_pkg;