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