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;

Java Servlets Part 2

Java Servlets Part 2 from Er Gurpreet singh

Java Servlets Part 1

Java Servlets Part 1 from Er Gurpreet singh

Get Concurrent Program details Oracle Apps

select
      a.concurrent_program_id,
      a.concurrent_program_name,
      b.user_concurrent_program_name,
      c.executable_name,
      c.execution_method_code,
      (select meaning FROM apps.fnd_lookups
         WHERE lookup_type like 'CP_EXECUTION_METHOD_CODE' and                   lookup_code= c.execution_method_code) type,
      c.execution_file_name
from
      apps.fnd_concurrent_programs a,
      apps.fnd_concurrent_programs_tl b,
      apps.fnd_executables c
where
      a.concurrent_program_id=b.concurrent_program_id
      and a.executable_id=c.executable_id
      and lower(b.user_concurrent_program_name) like '%program_name%';

Get CPU, Memory and Version in UNIX

Following command is used to get Memory Information:

  cat /proc/meminfo

Sample Output:

Following command is used to get CPU Information:


  cat /proc/cpuinfo

Sample Output:

Following command is used to get version Information:


  cat /proc/version

Sample Output:

Query to get open period details in Oracle Apps

SELECT
      distinct b.application_id,
      b.application_short_name,
      c.application_name,
      a.set_of_books_id,
      e.name set_of_books_name,
      e.short_name,
      a.period_name,
      a.start_date,
      a.end_date,
      a.closing_status ||' - '||d.meaning  period_status,
      a.year_start_date,
      a.quarter_num,
      a.quarter_start_date,
      a.period_year,
      a.period_num
FROM
      apps.gl_period_statuses a ,
      apps.fnd_application b,
      apps.fnd_application_tl c,
      apps.fnd_lookups d,
      apps.gl_sets_of_books e
WHERE
      a.application_id=b.application_id and
      a.application_id=c.application_id and
      a.closing_status=d.lookup_code and
      d.lookup_type='OKL_PERIOD_STATUS' and
      e.set_of_books_id=a.set_of_books_id and
      a.closing_status='O' and
      c.application_name like 'Payables';   -- Change the name of application

Get Request Set programs details with their exetuables

SELECT A.request_set_id,
  B.REQUEST_SET_STAGE_ID,
  A.USER_REQUEST_SET_NAME,
  C.USER_CONCURRENT_PROGRAM_NAME,
  E.EXECUTION_FILE_NAME,
  E.EXECUTION_METHOD_CODE
FROM apps.FND_REQUEST_SETS_TL A,
  apps.FND_REQUEST_SET_PROGRAMS B,
  apps.fnd_concurrent_programs_tl C,
  apps.fnd_concurrent_programs D,
  apps.fnd_executables E
WHERE a.request_set_id     =b.request_set_id
AND b.concurrent_program_id=c.concurrent_program_id
AND c.concurrent_program_id=d.concurrent_program_id
AND d.executable_id        =e.executable_id
AND (A.USER_REQUEST_SET_NAME) LIKE '%'   ||:RequestSetName   ||'%'
ORDER BY b.REQUEST_SET_STAGE_ID;

Creating Business Group in oracle apps

Creating business group in oracle apps from Er Gurpreet singh

Defining Locations in Oracle Apps

Defing locations in Oracle Apps from Er Gurpreet singh