SQL Query to get Concatenated Segments in Oracle Fusion (Invoice Tables)

In this post, we will see how to get Concatenated Segments in Oracle Fusion. For this, we will take example of Code Combination available on AP_INVOICES_ALL table in column ACCTS_PAY_CODE_COMBINATION_ID. We can also use other Code Combination columns available in other Invoices table:

 

 To get the concatenated segments we can use the below function:

FND_FLEX_EXT.GET_SEGS(
    application_short_name => v_application_short_name,     --'GL' 
    key_flex_code          => v_key_flex_code,              --'GL#'
    structure_number       => v_structure_number,           --Chart Of Accounts ID
    combination_id         => v_combination_id              
);

 So, to get concatenated segments for ACCTS_PAY_CODE_COMBINATION_ID in table AP_INVOICES_ALL use the below SQL:

SELECT
     aia.invoice_id
    ,aia.invoice_num
    ,aia.description
    ,aia.invoice_type_lookup_code
    ,aia.source
    ,aia.invoice_date
    ,aia.invoice_amount
    ,aia.accts_pay_code_combination_id
    ,(	SELECT  fnd_flex_ext.get_segs(
                    'GL'                            --Application Short Name
                    ,'GL#'                          --Key Flex Code
                    ,gcc.chart_of_accounts_id       --Structure Number
                    ,gcc.code_combination_id
                ) 
        FROM    gl_code_combinations gcc 
        WHERE   gcc.code_combination_id = aia.accts_pay_code_combination_id
     ) accts_pay_code_combination
FROM     
    ap_invoices_all aia
WHERE   aia.invoice_id = <InvoiceId>

 

Similarly, we can extract concatenated segments for other columns in other Invoice Tables.

Source: https://docs.oracle.com 

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

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

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