Oracle Fusion - SQL - Conversion Rate GL_DAILY_CONVERSION_TYPES

SELECT conversion_type

     , user_conversion_type 

  FROM gl_daily_conversion_types

SELECT * 

  FROM gl_daily_rates

Oracle R12 - LE Entity

This summary is not available. Please click here to view the post.

Oracle Fusion - SQL - AP Invoice Interface

Status of Interface by load_request_id ========================================= 

SELECT aii.load_request_id 

     , aii.status 

     , aii.operating_unit 

     , aii.source 

     , aii.invoice_type_lookup_code 

     , count(distinct aii.invoice_id) inv_count 

     , count(distinct aili.invoice_line_id) line_count 

  FROM ap_invoices_interface aii 

     , ap_invoice_lines_interface aili 

 WHERE aii.invoice_id = aili.invoice_id 

   AND aii.source = nvl(:p_source,aii.source) 

   AND aii.operating_unit = nvl(:p_operating_unit,aii.operating_unit)

   AND aii.load_request_id = nvl(:p_load_request_id,aii.load_request_id) 

 GROUP BY aii.status 

        , aii.operating_unit 

        , aii.source 

        , aii.invoice_type_lookup_code 

        , aii.load_request_id 

 order by 1,2,3,4,5 

AP Invoice Interface Errors 

=========================== 

SELECT apr.load_request_id

      ,apr.parent_table

      ,apr.reject_lookup_code

      ,count(1) reject_count 

  FROM ap_interface_rejections apr

     , ap_invoices_interface inv 

 WHERE apr.parent_id = inv.invoice_id 

   AND inv.status='REJECTED' 

   AND inv.source = nvl(:p_source,inv.source) 

   AND inv.load_request_id = nvl(:p_load_request_id,inv.load_request_id) 

 GROUP BY 

        apr.load_request_id

       ,apr.parent_table

       ,apr.reject_lookup_code 

UNION 

SELECT apr.load_request_id

      ,apr.parent_table

      ,apr.reject_lookup_code

      ,count(1) reject_count 

  FROM ap_interface_rejections apr

     , ap_invoices_interface inv

     , ap_invoice_lines_interface ln 

 WHERE apr.parent_id =ln.invoice_line_id 

   AND ln.invoice_id= inv.invoice_id 

   AND inv.status='REJECTED' 

   AND inv.source = nvl(:p_source,inv.source) 

   AND inv.load_request_id = nvl(:p_load_request_id,inv.load_request_id) 

 GROUP BY apr.load_request_id

         ,apr.parent_table

         ,apr.reject_lookup_code

Purge Interface Tables using program "Purge Interface Tables"

=======================================================

=> Import Payables Invoices

=> Load Request ID

"Load Interface File for Import" Program to load FBDI File

=======================================================

=> Import Payables Invoices

=> ZIP file created by FBDI Template

"Import Payables Invoices" program to load AP invoices from interface to Main tables

=======================================================

Oracle Fusion - SQL - Item Category - Product Categories - Catalogs

Item Categories =============== SELECT * FROM egp_item_categories c Item Category Assignment ======================== SELECT i.item_number,i.ORGANIZATION_ID,cat.CATEGORY_NAME FROM EGP_SYSTEM_ITEMS_B i ,egp_item_categories c,egp_categories_tl cat where i.INVENTORY_ITEM_ID=c.INVENTORY_ITEM_ID and i.ORGANIZATION_ID=c.ORGANIZATION_ID and c.CATEGORY_ID=cat.CATEGORY_ID and cat.LANGUAGE = 'US' List of Item Categories ======================= SELECT CATEGORY_ID, DESCRIPTION, CATEGORY_NAME, SOURCE_LANG, OBJECT_VERSION_NUMBER FROM EGP_CATEGORIES_TL ECT WHERE LANGUAGE = 'US' order by 3

The Create Accounting process ended in an error because you have global permissions for the Subledger Source Transaction data resource (Oracle Fusion)

Identify the role using the following query and then remove the role from user to run the create accounting.

select distinct role_name 

  from fnd_session_role_sets 

 where role_guid in 

          (select grantee_key 

             from fusion.fnd_grants 

            where object_id in 

                      (select object_id

                         from fnd_objects 

                        where obj_name in ('XLA_SUBLEDGERS'

                                         , 'XLA_GL_LEDGERS'

                                         , 'XLA_TRANSACTION_ENTITIES'

                                          )

                      ) 

              and INSTANCE_TYPE = 'GLOBAL'

          )

SQL Query for List of Concurrent Programs with Request Group and Responsibility Name

---List of Concurrent Programs with Request Group and Responsibility Name

 SELECT DISTINCT fnrtl.responsibility_name

              , frg.request_group_name

              , fcpl.user_concurrent_program_name

              , fcp.concurrent_program_name

              , fapp.application_name

              , fl.meaning execution_type

              , fe.executable_name

              , fe.execution_file_name

  FROM apps.fnd_request_groups         frg,

       apps.fnd_application_tl         fapp,

       apps.fnd_request_group_units    frgu,a

       apps.fnd_concurrent_programs    fcp,

       apps.fnd_concurrent_programs_tl fcpl,

       apps.fnd_responsibility         fnr,

       apps.fnd_responsibility_tl      fnrtl,

       apps.fnd_executables fe,

       apps.fnd_lookups fl

 WHERE     frg.application_id = fapp.application_id

       AND frg.application_id = frgu.application_id

       AND frg.request_group_id = frgu.request_group_id

       AND frg.request_group_id = fnr.request_group_id

       AND fnr.responsibility_id = fnrtl.responsibility_id

       AND frgu.request_unit_id = fcp.concurrent_program_id

       AND frgu.unit_application_id = fcp.application_id

       AND fcp.concurrent_program_id = fcpl.concurrent_program_id

       --AND fcpl.user_concurrent_program_name LIKE   '<concurrent program name>'

       AND frg.application_short_code in('XX')

       AND fe.executable_id = fcp.executable_id

       AND fcp.execution_method_code = fe.execution_method_code

       AND fl.lookup_code = fe.execution_method_code

       AND fl.lookup_type = 'CP_EXECUTION_METHOD_CODE'

       AND fnrtl.LANGUAGE = 'US'

       AND fapp.LANGUAGE = 'US'

       ORDER BY 3;

Oracle Fusion - Oracle Project Resource Allocation

 SELECT PA.project_id

       ,ppa.segment1 project_code

       ,ppnf.person_id

       ,START_DATE_ACTIVE

       ,END_DATE_ACTIVE 

       ,(ppnf.first_name||' '||ppnf.last_name) EMP_Name
  FROM PJF_PROJECT_PARTIES  PA, per_person_names_f ppnf
     , PJF_PROJECTS_ALL_VL PPA
 WHERE pa.project_id = ppa.project_id
   AND ppa.segment1 = 'PROJECT_CODE'
   AND ppnf.person_id = pa.RESOURCE_SOURCE_ID
   AND ppnf.name_type = 'GLOBAL'
   AND SYSDATE BETWEEN TRUNC(ppnf.effective_start_date) 

                   AND TRUNC(ppnf.effective_end_date)