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 

SOLVED- ORA-12541: TNS: no listener

To Resolve this follow these steps:

• Go to run and type services.msc

• Now in services, find all Services whose name start with Oracle.

• Now Start all these services by  Right clicking the Service and then clicking start.

• Most importantly start the OracleXETNSListener service

Commonly Used Look Up Types in Oracle Apps

TABLE_NAME: FND_CONCURRENT_REQUESTS
COLUMN NAME: PHASE_CODE

PHASE CODE
LOOKUP_TYPE	LOOKUP_CODE	MEANING
CP_PHASE_CODE	C	Completed
CP_PHASE_CODE	I	Inactive
CP_PHASE_CODE	P	Pending
CP_PHASE_CODE	R	Running

_________________________________________________________________________

TABLE_NAME: FND_CONCURRENT_REQUESTS
COLUMN NAME: STATUS_CODE

STATUS CODE
LOOKUP_TYPE	LOOKUP_CODE	MEANING
CP_STATUS_CODE	A	Waiting
CP_STATUS_CODE	B	Resuming
CP_STATUS_CODE	C	Normal
CP_STATUS_CODE	D	Cancelled
CP_STATUS_CODE	E	Error
CP_STATUS_CODE	G	Warning
CP_STATUS_CODE	H	On Hold
CP_STATUS_CODE	I	Normal
CP_STATUS_CODE	M	No Manager
CP_STATUS_CODE	P	Scheduled
CP_STATUS_CODE	Q	Standby
CP_STATUS_CODE	R	Normal
CP_STATUS_CODE	S	Suspended
CP_STATUS_CODE	T	Terminating
CP_STATUS_CODE	U	Disabled
CP_STATUS_CODE	W	Paused
CP_STATUS_CODE	X	Terminated
CP_STATUS_CODE	Z	Waiting

_________________________________________________________________________

TABLE_NAME: FND_EXECUTABLES
COLUMN NAME: EXECUTION_METHOD_CODE

EXECUTION METHOD
LOOKUP_TYPE	LOOKUP_CODE	MEANING
CP_EXECUTION_METHOD_CODE	A	Spawned
CP_EXECUTION_METHOD_CODE	B	Request Set Stage Function
CP_EXECUTION_METHOD_CODE	F	FlexSql
CP_EXECUTION_METHOD_CODE	H	Host
CP_EXECUTION_METHOD_CODE	I	PL/SQL Stored Procedure
CP_EXECUTION_METHOD_CODE	J	Java Stored Procedure
CP_EXECUTION_METHOD_CODE	L	SQL*Loader
CP_EXECUTION_METHOD_CODE	M	Multi Language Function
CP_EXECUTION_METHOD_CODE	P	Oracle Reports
CP_EXECUTION_METHOD_CODE	Q	SQL*Plus
CP_EXECUTION_METHOD_CODE	R	SQL*Report
CP_EXECUTION_METHOD_CODE	S	Immediate
CP_EXECUTION_METHOD_CODE	X	FlexRpt

 To check this write the following query:

SELECT LOOKUP_TYPE,LOOKUP_CODE,MEANING FROM apps.fnd_lookups
 WHERE lookup_type = 'CP_PHASE_CODE';  --write lookup_type here
 

Oracle TNS names configuration. How to add TNSNAMES.ORA to toad?

What is TNSNames.ora file?

The TNSNames.ora file contains the specific information required to connect to the Oracle instance. By default, the TNSNames.ora file resides in {oracle home directory}\network\admin. For example, C:\Oracle_Client\network\admin.

If a TNSNames.ora file does not exist on the machine, you will need to create one in a text editor. We recommend storing the file in your My Documents folder as this is an easily identified folder and common on most machines. For example: C:\Users\gsbprogramming\Documents.  

Sample TNSNames.ora entry

[net_service_name]=
( DESCRIPTION=
  ( ADDRESS=[protocol_address_information] )
  ( CONNECT_DATA=
    ( SERVICE_NAME=[service_name] )
   )

)

You can get this entry by using TNSPING

Here XE is SID of oracle database. So we used TNSPING XE 

If SID is ORCL you will use:

TNSPING ORCL 

So TNSNAMES.ORA will have entry as:

MYTNS=

( DESCRIPTION=

    ( ADDRESS = ( PROTOCOL = TCP ) ( HOST = ErJatt) ( PORT = 1521 ))

    ( CONNECT_DATA = ( SERVER = DEDICATED ) ( SERVICE_NAME = XE ))

)

Here ErJatt is username . You can also use 127.0.0.1 here 

So write this in notepad 

Save this file as TNSNAMES.ORA in any directory (For example: C:\Oracle\TNS_ADMIN\)

As we have saved this file in C:\Oracle\TNS_ADMIN\ so we will create a TNS_ADMIN system environment variable with value C:\Oracle\TNS_ADMIN\

  Now tnsnames.ora will be automatically added to toad