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;

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%';

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

Reset the password of user in Oracle apps

To reset the password of a user in oracle apps run the following query:

DECLARE
X BOOLEAN ;
BEGIN
X:= FND_USER_PKG.CHANGEPASSWORD('XG63','welcome123');
IF X THEN
dbms_output.put_line('true');
COMMIT;
ELSE
dbms_output.put_line('flase');
END IF;
END;


Here XG63 is username and welcome123 is new password

Assign role AME_BUS_ANALYST to user in oracle apps

By default, the “Approvals Management Administrator” and “Approvals Management Business Analyst” responsibilities do not have any functions associated with them, because their access is restricted by Role Based Access Control (RBAC).

So we need to assign role AME_BUS_ANALYST to user

Assigning role AME_BUS_ANALYST from Er Gurpreet singh

How to create a new employee in oracle apps?

Creating a new employee in oracle apps from Er Gurpreet singh

How to create a user in Oracle Apps?

Create a new user from Er Gurpreet singh

Get Program Name, Schedule date, Responsibility name, Requestor, Status, Phase, Start Time, End Time, Arguments from request Id in oracle apps

SELECT DECODE (fcrs.description,

               NULL, fcrs.user_concurrent_program_name,

               fcrs.description)

          Program_Name,

       TO_CHAR (fcrs.requested_start_date, 'Dy HH24:MI') Schedule_Date,

       fcrs.request_id,

       fresp.responsibility_name,

       fcrs.requestor,

       (SELECT meaning

          FROM apps.fnd_lookups

         WHERE     lookup_type = 'CP_STATUS_CODE'

               AND lookup_code = fcrs.status_code)

          Status,

       (SELECT meaning

          FROM apps.fnd_lookups

         WHERE     lookup_type = 'CP_PHASE_CODE'

               AND lookup_code = fcrs.status_code)

          Phase_Status,

       fcrs.actual_start_date Start_Time,

       fcrs.actual_completion_date End_Time,

       fcr.argument_text

  FROM apps.fnd_conc_req_summary_v fcrs,

       apps.fnd_responsibility_vl fresp,

       fnd_concurrent_requests fcr

 WHERE     fcrs.responsibility_id = fresp.responsibility_id

       AND fcrs.request_id = fcr.request_id

       AND fcrs.request_id IN ('Enter request Id');

Get Child Request of Concurrent Request and other details in Oracle Apps

SELECT

  /*+ USE_CONCAT */

  CONCURRENT_PROGRAM_ID,

  (SELECT fcpt.user_concurrent_program_name

  FROM fnd_concurrent_programs_tl fcpt

  WHERE fcpt.concurrent_program_id=fcr.concurrent_program_id

  ) name,

  (SELECT meaning

  FROM apps.fnd_lookups

  WHERE lookup_type = 'CP_STATUS_CODE'

  AND lookup_code   = fcr.status_code

  )status,

  (SELECT meaning

  FROM apps.fnd_lookups

  WHERE lookup_type = 'CP_PHASE_CODE'

  AND lookup_code   = fcr.phase_code

  )phase,

  logfile_name log_file,

  PROGRAM_APPLICATION_ID,

  PROGRAM_SHORT_NAME,

  ARGUMENT_TEXT,

  ACTUAL_COMPLETION_DATE,

  COMPLETION_TEXT,

  PARENT_REQUEST_ID,

  FCP_REQUIRED_STYLE,

  LAST_UPDATE_DATE,

  LAST_UPDATED_BY,

  REQUESTED_BY,

  RESPONSIBILITY_APPLICATION_ID,

  RESPONSIBILITY_ID,

  ENABLED,

  REQUESTED_START_DATE,

  PHASE_CODE,

  HOLD_FLAG,

  STATUS_CODE,

  REQUEST_ID,

  PROGRAM,

  REQUESTOR

FROM apps.FND_CONC_REQ_SUMMARY_V fcr

WHERE (DECODE(IMPLICIT_CODE, 'N', STATUS_CODE,'E', 'E', 'W', 'G') = STATUS_CODE

OR DECODE(IMPLICIT_CODE, 'W', 'E')                                = STATUS_CODE)

AND (((parent_request_id                                          = :req_id

AND request_type                                                  = 'S')

OR (request_id                                                    = :req_id)

OR ((priority_request_id                                          = :req_id

AND request_type                                                  = 'P')

OR (parent_request_id                                             = :req_id

AND request_type                                                  = 'P'))

OR (priority_request_id                                           = :req_id

AND (has_sub_request                                              = 'Y'

OR is_sub_request                                                 = 'Y'))) )

ORDER BY REQUEST_ID DESC