Oracle EBS R12: Archive of useful Standard DB Objects

The purpose of this post is to pool together useful seeded Oracle db objects that may help you for multiple purposes (Reporting, Queries, Data Extraction, Personalizations etc.).

1. Payables

1.1. Function to get Invoice Validation Status (Approval Status)

Function: AP_INVOICES_PKG.GET_APPROVAL_STATUS

select AP_INVOICES_PKG.GET_APPROVAL_STATUS   (aia.INVOICE_ID,
aia.INVOICE_AMOUNT,
aia.PAYMENT_STATUS_FLAG,
aia.INVOICE_TYPE_LOOKUP_CODE) INVOICE_STATUS
from ap_invoices_all aia
where aia.INVOICE_ID = :P_INVOICE_ID;

1.2 Function to get Invoice Accounting Status

Function: AP_INVOICES_PKG.GET_POSTING_STATUS

select AP_INVOICES_PKG.GET_POSTING_STATUS   (aia.INVOICE_ID) INVOICE_ACCOUNTED_STATUS
from ap_invoices_all aia
where aia.INVOICE_ID = :P_INVOICE_ID;

2. General Ledger

2.1 Function to get GL Code Combination’s Concatenated Description:

Function: gl_flexfields_pkg.get_concat_description

select gl_flexfields_pkg.get_concat_description(gcc.CHART_OF_ACCOUNTS_ID, gcc.CODE_COMBINATION_ID) concat_description
from gl_code_combinations gcc
where gcc.CODE_COMBINATION_ID = :P_CODE_COMBINATION_ID;

2.2 Function to get Description of a single segment of the GL Accounting Flexfield:

Function: gl_flexfields_pkg.get_description_sql

SELECT gl_flexfields_pkg.get_description_sql (gcc.chart_of_accounts_id,
1,
gcc.segment1
)
FROM gl_code_combinations gcc
WHERE gcc.code_combination_id = :p_code_combination_id;

 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s