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 

SQL Query to get Invoice Status, Accounting Status, Approval Status and GL Segments in Oracle Fusion

 

You can use the below query to get Invoice Validation Status, Accounting Status, Approval Status and GL Segments in Oracle Fusion

 

SELECT
aia.invoice_id,
aia.invoice_num,
aia.invoice_currency_code,
aia.invoice_amount,
aia.invoice_date,
aia.source,
aia.invoice_type_lookup_code,
aia.description,
aia.approval_status,
ap_invoices_pkg.Get_approval_status(aia.invoice_id, aia.invoice_amount,
aia.payment_status_flag, aia.invoice_type_lookup_code)
invoice_approval_status,
ap_invoices_pkg.Get_posting_status(aia.invoice_id)
Accounting_status,
aila.overlay_dist_code_concat,
aila.default_dist_ccid
,
gl_flexfields_pkg.Get_description_sql(gcc.chart_of_accounts_id, 1, gcc.segment1)segment1_desc,
gcc.segment1
,
gl_flexfields_pkg.Get_description_sql(gcc.chart_of_accounts_id, 2, gcc.segment2)segment2_desc,
gcc.segment2
,
gl_flexfields_pkg.Get_description_sql(gcc.chart_of_accounts_id, 3, gcc.segment3)segment3_desc,
gcc.segment3
,
gl_flexfields_pkg.Get_description_sql(gcc.chart_of_accounts_id, 4, gcc.segment4)segment4_desc,
gcc.segment4
FROM   ap_invoices_all aia,
       ap_invoice_lines_all aila,
       gl_code_combinations gcc
WHERE  1 = 1
       AND aia.invoice_id = aila.invoice_id
       AND gcc.code_combination_id = aia.accts_pay_code_combination_id  

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;

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: