Java Servlets Part 3

Download using below:

Java Servlet part 3 from Er Gurpreet singh

 

Query to get clone date and version information Oracle Apps

SELECT product
 ,VERSION
 ,STATUS
FROM product_component_version;

SELECT resetlogs_time
FROM v$database;

Get SID of concurrent request from request id in Oracle Apps

SELECT a.request_id, d.sid, d.serial#, d.osuser, d.process, c.SPID, d.inst_id FROM apps.fnd_concurrent_requests a, apps.fnd_concurrent_processes b, gv$process c, gv$session d WHERE a.controlling_manager = b.concurrent_process_id AND c.pid = b.oracle_process_id AND b.session_id = d.audsid AND a.request_id =&req_id AND a.phase_code = 'R';

AP Invoice Cancellation in Oracle Apps 11i from backend

DECLARE

    CURSOR invoice_holds (

      p_invoice_id NUMBER) IS

      SELECT     *

      FROM   apps.ap_holds_all

      WHERE  invoice_id = p_invoice_id

             AND release_lookup_code IS NULL;

    CURSOR invoices_to_cancel IS

      SELECT aia.invoice_id,

             aia.set_of_books_id,

             last_update_login,

             last_updated_by,

             gl_date,

             org_id

      FROM   apps.ap_invoices_all aia

      WHERE  invoice_id IN( 7216014 );

    v_period_name  VARCHAR2(4000);

    v_msg_name     VARCHAR2(4000);

    v_inv_amt      NUMBER;

    v_base_amt     NUMBER;

    v_tax_amt      NUMBER;

    v_can_amt      NUMBER;

    v_temp_can_amt NUMBER;

    v_can_date     DATE;

    v_prepay_amt   NUMBER;

    v_check_id     NUMBER;

    v_pay_cur_amt  NUMBER;

    v_cancelled_by NUMBER;

    v_update_date  DATE;

    v_is_cancelled BOOLEAN;

    v_resp_id      NUMBER;

    v_hold_cnt     NUMBER;

    v_apprvl_sts   ap_invoices.wfapproval_status % TYPE;

BEGIN

    dbms_output.Put_line('START OF CANCELING INVOICES');



    FOR i IN invoices_to_cancel LOOP

        BEGIN

            IF i.org_id = 125 THEN

              dbms_application_info.Set_client_info(125);

              fnd_global.Apps_initialize
                   (user_id => 25407, resp_id => 50446, resp_appl_id => 200);

              v_resp_id := 50446;

            ELSIF i.org_id = 129 THEN
              dbms_application_info.Set_client_info(129);

              fnd_global.Apps_initialize(user_id => 25407, resp_id => 50434,
              resp_appl_id => 200);

              v_resp_id := 50434;

            END IF;



            dbms_output.Put_line('Start releasing hold for invoice: '

                                 ||i.invoice_id);



            FOR rec IN invoice_holds(i.invoice_id) LOOP

                BEGIN

                    ap_holds_pkg.Quick_release (x_invoice_id => rec.invoice_id,

                    x_hold_lookup_code => rec.hold_lookup_code,

                    x_release_lookup_code => 'OB10 Dup Invoice Release',

                    x_release_reason => 'OB10 Dup Invoice Release',

                    x_responsibility_id => v_resp_id

                    , x_last_updated_by => '-1', x_last_update_date => SYSDATE,

                    x_holds_count => v_hold_cnt, x_approval_status_lookup_code =>  v_apprvl_sts,

                    x_calling_sequence => 'xxap_invoice_util_pkg.release_holds')

                    ;



                    COMMIT;

                EXCEPTION

                    WHEN OTHERS THEN

                      NULL;

                END;

            END LOOP;



          dbms_output.Put_line('end releasing hold of invoice: '  ||i.invoice_id);
          dbms_output.Put_line('CANCELING INVOICE_ID: '  ||i.invoice_id);

                BEGIN

                v_is_cancelled := ap_cancel_pkg.Ap_cancel_single_invoice (

                                  p_invoice_id => i.invoice_id,
                                  p_last_updated_by => i.last_updated_by,
                                  p_last_update_login => i.last_update_login,
                                  p_set_of_books_id =>  i.set_of_books_id,
                                  p_accounting_date =>  i.gl_date, 
                                  p_period_name => v_period_name, 
                                  p_message_name => v_msg_name,
                                  p_invoice_amount => v_inv_amt,
                                  p_base_amount => v_base_amt,
                                  p_tax_amount => v_tax_amt,
                                  p_temp_cancelled_amount =>v_temp_can_amt,
                                  p_cancelled_by => v_cancelled_by,
                                  p_cancelled_amount => v_can_amt,
                                  p_cancelled_date => v_can_date,
                                  p_last_update_date => v_update_date,
                                  p_original_prepayment_amount => v_prepay_amt,
                                  p_check_id => NULL,
                                  p_pay_curr_invoice_amount => v_pay_cur_amt,
                                  p_calling_sequence => NULL

                                  );



                COMMIT;

            EXCEPTION

                WHEN OTHERS THEN

                  NULL;

            END;



            IF v_is_cancelled THEN

              dbms_output.Put_line('CANCELLED THE INVOICE: ' ||i.invoice_id);

            ELSE

              dbms_output.Put_line('NOT CANCELLED THE INVOICE: ' ||i.invoice_id);

            END IF;

        EXCEPTION

            WHEN OTHERS THEN

              dbms_output.Put_line('Error Occurred for invoice: ' ||i.invoice_id);


        END;

    END LOOP;



    dbms_output.Put_line('END OF CANCELING INVOICES');

EXCEPTION

    WHEN OTHERS THEN

      dbms_output.Put_line('Exception: ' ||SQLERRM);

END;