AR invoice adjustment api script ( ar_adjust_pub.create_adjustment)

SET SERVEROUTPUT ON

DECLARE
    v_init_msg_list         VARCHAR2(1000);
    v_commit_flag           VARCHAR2(5) := 'F';
    v_validation_level      NUMBER(4) := fnd_api.g_valid_level_full;
    v_msg_count             NUMBER(4);
    v_msg_data              VARCHAR2(1000);
    v_return_status         VARCHAR2(5);
    v_adj_rec               ar_adjustments%rowtype;
    v_chk_approval_limits   VARCHAR2(5) := 'F';
    v_check_amount          VARCHAR2(5) := 'F';
    v_move_deferred_tax     VARCHAR2(1) := 'Y';
    v_new_adjust_number     ar_adjustments.adjustment_number%TYPE;
    v_new_adjust_id         ar_adjustments.adjustment_id%TYPE;
    v_called_from           VARCHAR2(25) := 'ADJ-API';
    v_old_adjust_id         ar_adjustments.adjustment_id%TYPE;
    l_customer_trx_id       NUMBER;
    l_payment_schedule_id   NUMBER;
    l_receivables_trx_id    NUMBER;
    l_first_char            VARCHAR2(150);
    l_second_char           VARCHAR2(150);

BEGIN
    dbms_output.put_line('Program Start');
    BEGIN
        SELECT
            a.customer_trx_id,
            b.payment_schedule_id,
            substr(interface_header_attribute2,1,instr(interface_header_attribute2,'.') - 1),
            TRIM(regexp_substr(interface_header_attribute2,'(\S*)(\s)',1,2) )
        INTO
            l_customer_trx_id,
            l_payment_schedule_id,
            l_first_char,
            l_second_char
        FROM
            ra_customer_trx_all a,
            ar_payment_schedules_all b
        WHERE
            a.customer_trx_id = b.customer_trx_id
            AND a.trx_number = '167694'
            AND a.org_id = 143;

    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Transaction Number does not exist' );
    END;

    dbms_output.put_line('l_first_char-' || l_first_char);
    dbms_output.put_line('l_second_char-' || l_second_char);
    BEGIN
        SELECT
            receivables_trx_id
        INTO l_receivables_trx_id
        FROM
            ar_receivables_trx_all
        WHERE
            name LIKE '%'
                      || l_first_char
                      || '%'
                      || l_second_char
                      || '%';-- = 'CL.5Q Admin Fee USD- AGDF';--'Adjustment-Data Migration';
  --CL.5Q Admin Fee CLP- AGDF

    EXCEPTION
        WHEN OTHERS THEN
            dbms_output.put_line('Receivable Transaction  does not exist' || 'Adjustment-Data Migration');
    END;

    dbms_output.put_line('l_receivables_trx_id-' || l_receivables_trx_id);

/*------------------------------------+
| Setting global initialization |
+------------------------------------*/
    mo_global.init('AR');
    mo_global.set_policy_context('S','143');
    fnd_global.apps_initialize(12658,50731,222,0);
/*------------------------------------+
| Setting value to input parameters |
+------------------------------------*/
--Populate v_adj_rec record
    v_adj_rec.customer_trx_id := l_customer_trx_id;
    v_adj_rec.type := 'LINE';
    v_adj_rec.payment_schedule_id := l_payment_schedule_id;
    v_adj_rec.receivables_trx_id := l_receivables_trx_id;
    v_adj_rec.associated_cash_receipt_id := 9643009;--9253005;--cash_receipt_id from AR_CASH_RECEIPTS
    v_adj_rec.associated_application_id := 18118738;--16400596;-- RECEIVABLE_APPLICATION_ID from AR_RECEIVABLE_APPLICATIONS_V
    v_adj_rec.amount :=300;--c1.AMOUNT;
    v_adj_rec.apply_date := SYSDATE;--TO_DATE (c1.apply_date);
    v_adj_rec.gl_date := SYSDATE;--TO_DATE (c1.gl_date);
    v_adj_rec.created_from := 'ADJ-API';
 
 ar_adjust_pub.create_adjustment
    ('AR_ADJUST_PUB',1.0,
    v_init_msg_list,
    v_commit_flag,
    v_validation_level,
    v_msg_count,
    v_msg_data
    ,v_return_status
    ,v_adj_rec,
    v_chk_approval_limits,
    v_check_amount,
    v_move_deferred_tax,
    v_new_adjust_number,
    v_new_adjust_id,
    v_called_from,
    v_old_adjust_id
    );

    dbms_output.put_line('v_msg_count-' || v_msg_count);
    dbms_output.put_line('v_msg_data-' || v_msg_data);
    dbms_output.put_line('v_new_adjust_number-' || v_new_adjust_number);
    dbms_output.put_line('v_new_adjust_id-' || v_new_adjust_id);
    dbms_output.put_line('v_init_msg_list-' || v_init_msg_list);
    dbms_output.put_line('Successfully Completed-' || v_return_status);

    FOR i IN 1..v_msg_count LOOP
        dbms_output.put_line(i
                               || '. '
                               || substr(fnd_msg_pub.get(p_encoded => fnd_api.g_false),1,255) );
    END LOOP;

    COMMIT;

EXCEPTION
    WHEN OTHERS THEN
        FOR i IN 1..v_msg_count LOOP
            dbms_output.put_line(v_msg_data);
            dbms_output.put_line(v_new_adjust_number);
            dbms_output.put_line(v_new_adjust_id);
            dbms_output.put_line(v_init_msg_list);
        END LOOP;
END;
/



Query to find out Adjustment amount
-----------------------------------------------------
select nvl(sum(amount),0)
into v_adjustments_amt
FROM
    ar_adjustments_v
    where customer_trx_id = p_invoice_id;
------------------------------------------------------