Home/Oracle E-Business Suite/Oracle EBS Queries
  1. Asked: July 29, 2022In:

    What are GL Tables in Oracle APPS R12

    siddhardha manubrolu Explorer

    SELECT * FROM gl_code_combinations;

    SELECT * FROM gl_code_combinations;
    See less
    • 0
  2. Asked: April 2, 2023In:

    How to Initialize Oracle APPS session using SQL Developer

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant
    This answer was edited.

    You can use this script to initialize a session in SQL Developer and to be able to select from multi-org views in Oracle EBS R12 DECLARE l_user_id NUMBER; l_resp_id NUMBER; l_app_id NUMBER; l_org_id NUMBER; l_err_num VARCHAR2(1000); l_err_msg VARCHAR2(1000); BEGIN SELECT user_id INTO l_user_id FROMRead more

    You can use this script to initialize a session in SQL Developer and to be able to select from multi-org views in Oracle EBS R12

    DECLARE
    l_user_id NUMBER;
    l_resp_id NUMBER;
    l_app_id NUMBER;
    l_org_id NUMBER;
    l_err_num VARCHAR2(1000);
    l_err_msg VARCHAR2(1000);

BEGIN

    SELECT user_id
    INTO l_user_id
    FROM fnd_user
    WHERE user_name = 'OPERATIONS';

    SELECT responsibility_id, application_id
    INTO l_resp_id,l_app_id
    FROM fnd_responsibility
    WHERE responsibility_key = 'PAYABLES_MANAGER';

    BEGIN

        fnd_global.apps_initialize(l_user_id,l_resp_id,l_app_id);

        fnd_global.set_nls_context(p_nls_language => 'AMERICAN'
                                  ,p_nls_date_format => NULL
                                  ,p_nls_date_language => NULL
                                  ,p_nls_numeric_characters => NULL
                                  ,p_nls_sort => NULL
                                  ,p_nls_territory => NULL);

        fnd_profile.get(name => 'DEFAULT_ORG_ID',val => l_org_id);
        mo_global.set_policy_context(p_access_mode => 'S',p_org_id => l_org_id);
        mo_global.init(p_appl_short_name => 'SQLAP');

    EXCEPTION WHEN OTHERS THEN
        l_err_num := sqlcode;
        l_err_msg := substr(sqlerrm,1,1000);
        raise_application_error(l_err_num,l_err_msg);
    END;
END;
    See less
    • 0
  3. Asked: February 21, 2023In:

    Query to Get Supplier Details in Oracle APPS R12

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant
    This answer was edited.

    Hi @Beter, Please find below a query to get the master supplier informations: SELECT HOU.NAME OU_NAME ,APPS.ORG_ID ,APS.SEGMENT1 SUPPLIER_NUMBER ,APS.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE ,APS.VENDOR_NAME SUPPLIER_NAME ,APS.VENDOR_NAME_ALT SUPPLIER_NAME_ALT ,APS.NUM_1099 TAXPAYER_ID ,APS.VAT_REGISTRRead more

    Hi Beter, Please find below a query to get the master supplier informations:

    SELECT HOU.NAME OU_NAME
,APPS.ORG_ID
,APS.SEGMENT1 SUPPLIER_NUMBER
,APS.VENDOR_TYPE_LOOKUP_CODE SUPPLIER_TYPE
,APS.VENDOR_NAME SUPPLIER_NAME
,APS.VENDOR_NAME_ALT SUPPLIER_NAME_ALT
,APS.NUM_1099 TAXPAYER_ID
,APS.VAT_REGISTRATION_NUM TAX_REGISTRATION_NUM
,APS.ALLOW_AWT_FLAG SUPPLIER_ALLOW_WITHHOLDING_TAX
,DECODE (APS.END_DATE_ACTIVE, NULL, 'ACTIVE', 'IN ACTIVE') ACTIVE_CODE
,APPS.VENDOR_SITE_ID
,APPS.INACTIVE_DATE
,APPS.VENDOR_SITE_CODE SUPPLIER_SITE_CODE
,APPS.VENDOR_SITE_CODE_ALT SITE_CODE_ALT
,APPS.ALLOW_AWT_FLAG SITE_ALLOW_WITHHOLDING_TAX
,APPS.ADDRESS_LINE1
,APPS.ADDRESS_LINE2
,APPS.ADDRESS_LINE3
,APPS.ADDRESS_LINE4
,APPS.CITY
,APPS.STATE
,APPS.ZIP POST_CODE
,APPS.SUPPLIER_NOTIF_METHOD SITE_PO_NOTIF_METHOD
,APPS.EMAIL_ADDRESS SITE_PO_EMAIL
,APS.PAY_GROUP_LOOKUP_CODE
FROM AP_SUPPLIERS APS, AP_SUPPLIER_SITES_ALL APPS, HR_OPERATING_UNITS HOU
WHERE APS.VENDOR_ID = APPS.VENDOR_ID(+)
AND APS.END_DATE_ACTIVE IS NULL
AND APPS.INACTIVE_DATE IS NULL
AND HOU.ORGANIZATION_ID(+) = APPS.ORG_ID;

    Above query will list all suppliers details: OU_NAME, ORG_ID, SUPPLIER_NUMBER, SUPPLIER_TYPE, SUPPLIER_NAME, SUPPLIER_NAME_ALT, TAXPAYER_ID, TAX_REGISTRATION_NUM, SUPPLIER_ALLOW_WITHHOLDING_TAX, ACTIVE_CODE, VENDOR_SITE_ID, INACTIVE_DATE, SUPPLIER_SITE_CODE, SITE_CODE_ALT, SITE_ALLOW_WITHHOLDING_TAX, ADDRESS_LINE1, ADDRESS_LINE2, ADDRESS_LINE3, ADDRESS_LINE4 ,CITY, STATE, POST_CODE, SITE_PO_NOTIF_METHOD, and SITE_PO_EMAIL. And the list is open to add any other column/s you prefer. Note: If you are looking also about other details for the supplier you can find them in the list below:

     

    Hope this helps.

    See less
    • 0
  4. Asked: September 14, 2021In:

    Query To Find Operating Unit And Corresponding Inventory Organizations Details in Oracle Apps R12

    andy.haack Junior
    This answer was edited.

    You can use the following SQL from the GL Ledgers and Organizations using blitz report SELECT HAOUV.NAME BUSINESS_GROUP , (SELECT DISTINCT LISTAGG (GL0.NAME, ', ') WITHIN GROUP (ORDER BY GL0.NAME) OVER (PARTITION BY GLSNAV.LEDGER_ID) LEDGER_SET FROM GL_LEDGER_SET_NORM_ASSIGN_V GLSNAV, GL_LEDGERS GL0Read more

    You can use the following SQL from the GL Ledgers and Organizations using blitz report

    SELECT HAOUV.NAME BUSINESS_GROUP
, (SELECT DISTINCT LISTAGG (GL0.NAME, ', ') WITHIN GROUP (ORDER BY GL0.NAME) OVER (PARTITION BY GLSNAV.LEDGER_ID) LEDGER_SET
FROM GL_LEDGER_SET_NORM_ASSIGN_V GLSNAV, GL_LEDGERS GL0
WHERE GL.LEDGER_ID = GLSNAV.LEDGER_ID
AND GLSNAV.LEDGER_SET_ID = GL0.LEDGER_ID)
LEDGER_SET
,GL.NAME LEDGER
,XXEN_UTIL.MEANING (GL.LEDGER_CATEGORY_CODE, 'GL_ASF_LEDGER_CATEGORY', 101) LEDGER_CATEGORY
,GL.CURRENCY_CODE CURRENCY
,FIFSV.ID_FLEX_STRUCTURE_NAME CHART_OF_ACCOUNTS_NAME
,HOU.NAME OPERATING_UNIT
,OOD.ORGANIZATION_CODE
,OOD.ORGANIZATION_NAME ORGANIZATION
,FTV.TERRITORY_SHORT_NAME COUNTRY
,XEP.NAME LEGAL_ENTITY
,GL.LEDGER_ID
,FIFSV.ID_FLEX_STRUCTURE_CODE CHART_OF_ACCOUNTS_CODE
,GL.CHART_OF_ACCOUNTS_ID
,OOD.OPERATING_UNIT OPERATING_UNIT_ID
,OOD.ORGANIZATION_ID
FROM GL_LEDGERS GL
,FND_ID_FLEX_STRUCTURES_VL FIFSV
,HR_OPERATING_UNITS HOU
,HR_ALL_ORGANIZATION_UNITS_VL HAOUV
,ORG_ORGANIZATION_DEFINITIONS OOD
,HR_ALL_ORGANIZATION_UNITS HAOU
,HR_LOCATIONS_ALL HLA
,FND_TERRITORIES_VL FTV
,XLE_ENTITY_PROFILES XEP
WHERE NVL (OOD.DISABLE_DATE, SYSDATE) >= SYSDATE
AND NVL (HOU.DATE_TO, SYSDATE) >= SYSDATE
AND NVL (OOD.DISABLE_DATE, SYSDATE) >= SYSDATE
AND NVL (HOU.DATE_TO, SYSDATE) >= SYSDATE
AND GL.OBJECT_TYPE_CODE = 'L'
AND GL.CHART_OF_ACCOUNTS_ID = FIFSV.ID_FLEX_NUM
AND FIFSV.APPLICATION_ID = 101
AND FIFSV.ID_FLEX_CODE = 'GL#'
AND GL.LEDGER_ID = HOU.SET_OF_BOOKS_ID(+)
AND HOU.BUSINESS_GROUP_ID = HAOUV.ORGANIZATION_ID(+)
AND HOU.ORGANIZATION_ID = OOD.OPERATING_UNIT(+)
AND OOD.ORGANIZATION_ID = HAOU.ORGANIZATION_ID(+)
AND HAOU.LOCATION_ID = HLA.LOCATION_ID(+)
AND HLA.COUNTRY = FTV.TERRITORY_CODE(+)
AND OOD.LEGAL_ENTITY = XEP.LEGAL_ENTITY_ID(+)
ORDER BY CHART_OF_ACCOUNTS_NAME
,LEDGER_SET
,BUSINESS_GROUP
,LEDGER
,CURRENCY
,OPERATING_UNIT
,COUNTRY
,ORGANIZATION_CODE
    See less
    • 0
  5. Asked: December 10, 2022In:

    Query to Get Chart of Account Description in Oracle APPS R12

    andy.haack Junior
    This answer was edited.

    You can use the following query from blitz report FND Key Flexfields: SELECT FAV.APPLICATION_NAME,FIF.ID_FLEX_NAME TITLE ,FIF.DESCRIPTION ,FIF.ID_FLEX_CODE ,FIF.APPLICATION_TABLE_NAME ,FIF.UNIQUE_ID_COLUMN_NAME ,FIF.SET_DEFINING_COLUMN_NAME ,DECODE (FIFSV.ENABLED_FLAG, 'Y', 'Y') STRUCTURE_ENABLED ,FRead more

    You can use the following query from blitz report FND Key Flexfields:

    SELECT FAV.APPLICATION_NAME
    ,FIF.ID_FLEX_NAME TITLE
,FIF.DESCRIPTION
,FIF.ID_FLEX_CODE
,FIF.APPLICATION_TABLE_NAME
,FIF.UNIQUE_ID_COLUMN_NAME
,FIF.SET_DEFINING_COLUMN_NAME
,DECODE (FIFSV.ENABLED_FLAG, 'Y', 'Y') STRUCTURE_ENABLED
,FIFSV.ID_FLEX_STRUCTURE_CODESTRUCTURE_CODE
,FIFSV.ID_FLEX_STRUCTURE_NAMESTRUCTURE_NAME
,FIFSV.ID_FLEX_NUM
,FIFSV.CONCATENATED_SEGMENT_DELIMITER DELIMITER
,FIFSV.SHORTHAND_PROMPT
,FIFSGV.SEGMENT_NUMSEGMENT_NUMBER
,FIFSGV.SEGMENT_NAME
,FIFSGV.FORM_LEFT_PROMPT WINDOW_PROMPT
,FIFSGV.APPLICATION_COLUMN_NAMECOLUMN_NAME
, (SELECT DISTINCT LISTAGG (XXEN_UTIL.MEANING (FSAV.SEGMENT_ATTRIBUTE_TYPE, 'XLA_FLEXFIELD_SEGMENTS_QUAL', 602), ', ')
WITHIN GROUP (ORDER BY XXEN_UTIL.MEANING (FSAV.SEGMENT_ATTRIBUTE_TYPE, 'XLA_FLEXFIELD_SEGMENTS_QUAL', 602))
OVER (PARTITION BY FSAV.APPLICATION_ID
,FSAV.ID_FLEX_CODE
,FSAV.ID_FLEX_NUM
,FSAV.APPLICATION_COLUMN_NAME)
SEGMENT_ATTRIBUTES
FROM FND_SEGMENT_ATTRIBUTE_VALUES FSAV
WHERE FIFSGV.APPLICATION_ID = FSAV.APPLICATION_ID
AND FIFSGV.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
AND FIFSGV.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
AND FIFSGV.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
AND FSAV.ATTRIBUTE_VALUE = 'Y'
AND FSAV.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL')
FLEXFIELD_QUALIFIER
, (SELECT DISTINCT LISTAGG (FSAV.SEGMENT_ATTRIBUTE_TYPE, ', ')
WITHIN GROUP (ORDER BY FSAV.SEGMENT_ATTRIBUTE_TYPE)
OVER (PARTITION BY FSAV.APPLICATION_ID
,FSAV.ID_FLEX_CODE
,FSAV.ID_FLEX_NUM
,FSAV.APPLICATION_COLUMN_NAME)
SEGMENT_ATTRIBUTES
FROM FND_SEGMENT_ATTRIBUTE_VALUES FSAV
WHERE FIFSGV.APPLICATION_ID = FSAV.APPLICATION_ID
AND FIFSGV.ID_FLEX_CODE = FSAV.ID_FLEX_CODE
AND FIFSGV.ID_FLEX_NUM = FSAV.ID_FLEX_NUM
AND FIFSGV.APPLICATION_COLUMN_NAME = FSAV.APPLICATION_COLUMN_NAME
AND FSAV.ATTRIBUTE_VALUE = 'Y'
AND FSAV.SEGMENT_ATTRIBUTE_TYPE <> 'GL_GLOBAL')
FLEXFIELD_QUALIFIER_CODE
,FFVS.FLEX_VALUE_SET_NAME VALUE_SET_NAME
,DECODE (FIFSGV.ENABLED_FLAG, 'Y', 'Y') ENABLED
,DECODE (FIFSGV.DISPLAY_FLAG, 'Y', 'Y') DISPLAYED
,DECODE (FIFSGV.REQUIRED_FLAG, 'Y', 'Y') REQUIRED
,DECODE (FIFSGV.SECURITY_ENABLED_FLAG, 'Y', 'Y') SECURITY_ENABLED
,XXEN_UTIL.MEANING (FFVS.VALIDATION_TYPE, 'SEG_VAL_TYPES', 0) VALIDATION_TYPE
,FFVT.APPLICATION_TABLE_NAME VALIDATION_TABLE
,FFVT.ADDITIONAL_WHERE_CLAUSE WHERE_CLAUSE
,FIFSGV.FLEX_VALUE_SET_ID
FROM FND_APPLICATION_VL FAV
,FND_ID_FLEXS FIF
,FND_ID_FLEX_STRUCTURES_VL FIFSV
,FND_ID_FLEX_SEGMENTS_VL FIFSGV
,FND_FLEX_VALUE_SETS FFVS
,FND_FLEX_VALIDATION_TABLES FFVT
WHERE FIF.ID_FLEX_CODE = 'GL#'
AND 1 = 1
AND FAV.APPLICATION_ID = FIF.APPLICATION_ID
AND FIF.APPLICATION_ID = FIFSV.APPLICATION_ID(+)
AND FIF.ID_FLEX_CODE = FIFSV.ID_FLEX_CODE(+)
AND FIFSV.APPLICATION_ID = FIFSGV.APPLICATION_ID(+)
AND FIFSV.ID_FLEX_CODE = FIFSGV.ID_FLEX_CODE(+)
AND FIFSV.ID_FLEX_NUM = FIFSGV.ID_FLEX_NUM(+)
AND FIFSGV.FLEX_VALUE_SET_ID = FFVS.FLEX_VALUE_SET_ID(+)
AND DECODE (FFVS.VALIDATION_TYPE, 'F', FFVS.FLEX_VALUE_SET_ID) = FFVT.FLEX_VALUE_SET_ID(+)
ORDER BY FAV.APPLICATION_NAME
,FIF.ID_FLEX_NAME
,FIFSV.ID_FLEX_STRUCTURE_NAME
,FIFSGV.SEGMENT_NUM
    See less
    • 0
  6. Asked: January 25, 2023In:

    Query to get posted AP invoice in Oracle APPS R12

    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    There is a seeded standard function provided from oracle to identify whether the invoice is posted or not. Which is (AP_INVOICES_PKG.GET_POSTING_STATUS) . This function take the invoice_id parameter and return one of four values: 'Y' - Posted 'N' - Unposted 'S' - Selected 'P' - Partially Posted ThesRead more

    There is a seeded standard function provided from oracle to identify whether the invoice is posted or not. Which is (AP_INVOICES_PKG.GET_POSTING_STATUS) .

    This function take the invoice_id parameter and return one of four values:

    ‘Y’ – Posted
    ‘N’ – Unposted
    ‘S’ – Selected
    ‘P’ – Partially Posted

    These values is the invoice posting status flag.

    Example:

    SELECT AIA.INVOICE_NUM, AP_INVOICES_PKG.GET_POSTING_STATUS (AIA.INVOICE_ID) INVOICE_POSTING_FLAG
FROM AP_INVOICES_ALL AIA;

    Output:

    INVOICE_NUM
    ————————————————–
    INVOICE_POSTING_FLAG
    ——————————————————————————–
    ERS-9163-109073
    Y

    19879-781
    N

    ERS-4400-109091
    Y

    ERS-9164-109093
    P

    ERS-6970-109094
    Y

    ERS-6971-109095
    Y

    See less
    • 1
  7. Asked: September 9, 2021In:

    Query to get supplier bank details in Oracle APPS R12

    Daniel

    Fantastic

    Fantastic

    See less
    • 0
  8. Asked: March 23, 2022In:

    SQL query to find all customised dff in oracle 12g

    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    What do you mean by Customized DFF? If you want to list all custom context that is created on a specific DFF you can run this query to get the result: SELECT FAPT.APPLICATION_NAME, FDFC.DESCRIPTIVE_FLEXFIELD_NAME, FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE, FDFC.ENABLED_FLAG, FDFC.GLOBAL_FLAG, FDFC.DESCRIPTRead more

    What do you mean by Customized DFF?

    If you want to list all custom context that is created on a specific DFF you can run this query to get the result:

    SELECT FAPT.APPLICATION_NAME,
FDFC.DESCRIPTIVE_FLEXFIELD_NAME,
FDFC.DESCRIPTIVE_FLEX_CONTEXT_CODE,
FDFC.ENABLED_FLAG,
FDFC.GLOBAL_FLAG,
FDFC.DESCRIPTION,
FDFC.DESCRIPTIVE_FLEX_CONTEXT_NAME
FROM FND_DESCR_FLEX_CONTEXTS_VL FDFC, FND_USER FU, FND_APPLICATION_TL FAPT
WHERE FAPT.APPLICATION_NAME = :P_APPLICATION_NAME
AND FDFC.DESCRIPTIVE_FLEXFIELD_NAME LIKE :P_DFF_NAME
AND FDFC.CREATED_BY = FU.USER_ID
AND FDFC.APPLICATION_ID = FAPT.APPLICATION_ID
AND FAPT.LANGUAGE = 'US'
AND ( FU.USER_NAME NOT LIKE '%ORACLE%'
AND FU.USER_NAME NOT LIKE '%AUTOINSTALL%'
AND FU.USER_NAME NOT LIKE '%INITIAL SETUP%');

    P_APPLICATION_NAME : Is your application name

    P_DFF_NAME : Is your Descriptive flexfield name. It’s not the title which is available on DFF screen, but you can find the column name “DESCRIPTIVE_FLEXFIELD_NAME” from help -> diagnostic -> examine.

    Note: We have excluded three users because Oracle seeded DFF contexts are created by these users above, thus, we can get only the custom ones.

    See less
    • 0
  9. Asked: February 9, 2022In:

    Query to get FND messages in Oracle apps R12

    aargawy Explorer https://www.linkedin.com/in/abdelrahman-argawy-73798861/

    try this select MESSAGE_NAME from fnd_new_messages

    try this

    select MESSAGE_NAME from fnd_new_messages

    See less
    • 0
  10. Asked: September 14, 2021In:

    Query to Get The Oracle Application URL From Database in Oracle APPS R12

    Best Answer
    Hassan AbdElrahman Master Oracle ACE Pro ♠ | Oracle Senior ERP Technical Consultant

    To find the application URL from the backend use one of the following queries : SELECT HOME_URL FROM ICX_PARAMETERS; Result http://<host.domain:port>/OA_HTML/AppsLogin OR SELECT FPOV.PROFILE_OPTION_VALUE FROM FND_PROFILE_OPTIONS FPO, FND_PROFILE_OPTION_VALUES FPOV WHERE FPO.PROFILE_OPTION_ID =Read more

    To find the application URL from the backend use one of the following queries :

    SELECT HOME_URL FROM ICX_PARAMETERS;

    Result

    http://<host.domain:port>/OA_HTML/AppsLogin

    OR

    SELECT FPOV.PROFILE_OPTION_VALUE
FROM FND_PROFILE_OPTIONS FPO, FND_PROFILE_OPTION_VALUES FPOV
WHERE FPO.PROFILE_OPTION_ID = FPOV.PROFILE_OPTION_ID
AND PROFILE_OPTION_NAME = 'APPS_FRAMEWORK_AGENT'
AND LEVEL_VALUE = 0;

    Result

    http://<host.domain:port&gt;

    See less
    • 0