SQL Query to get Code Combination Segment Value Description in Oracle Fusion (Invoice Tables)

In this post, we will see how to get Segments Description 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:

GL_FLEXFIELDS_PKG.GET_DESCRIPTION_SQL(
    v_chart_of_account_id,     
    v_segment_number,
    v_segment_value                  
);

So, to get segments descriptions 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
    ,gcc.segment1

    ,gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,1,gcc.segment1) segment1_desc

    ,gcc.segment2    

    ,gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,2,gcc.segment2) segment2_desc

    ,gcc.segment3

    ,gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,3,gcc.segment3) segment3_desc

    ,gcc.segment4

    ,gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,4,gcc.segment4) segment4_desc

    ,gcc.segment5

    ,gl_flexfields_pkg.get_description_sql(gcc.chart_of_accounts_id,5,gcc.segment5) segment5_desc

FROM     
     ap_invoices_all aia

    ,gl_code_combinations gcc 

WHERE   1=1

AND gcc.code_combination_id = aia.accts_pay_code_combination_id

AND aia.invoice_id = <InvoiceId>

 

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

Source: https://docs.oracle.com