Oracle Projects Billing Interface with Oracle Receivables

Oracle Projects Billing Interface with Oracle Receivables

Oracle Projects (PA) generates draft invoices and uses Oracle Receivables (AR) features to create invoices and interface the accounting transactions to Oracle General Ledger (GL).  When you interface invoices to Receivables, a Projects process is used to collect all eligible, released draft invoices in PA and interfaces them to the Receivables interface tables.  This process also maintains project balances of Unbilled Receivables and unearned revenue and creates accounting transactions for these amounts. 

Once in the AR interface tables, the draft invoices await further processing by the Oracle Receivables AutoInvoice process.  After the AutoInvoice program is run to create invoices in Oracle Receivables, you need to run the tieback process to ensure that your data successfully loaded into Receivables and that there are no transactions requiring correction.   If you have rejected invoices, you will need to correct and resubmit them.

Standard Oracle Projects reports can be used to track your invoices as you interface data between Projects and Receivables. You can also use AutoInvoice output reports to review imported transaction data and transaction data that fails when you run AutoInvoice. Modification Of Transaction Lines (PA/AR Out of Sync). It is possible to modify or delete invoice transactions transferred from Projects to Receivables using the Transaction Entry Workbench (ARXTWMAI). 


Refer to the scripts below to verify the information contained in the PA-AR invoice interface.  Both scripts should normally return the same lines and revenue amounts:

     SELECT l.interface_line_attribute2, l.interface_line_attribute6,   

     l.revenue_amount, l.interface_line_attribute1, h.reason_code,   


     FROM ra_customer_trx_all h, ra_customer_trx_lines_all l

     WHERE h.customer_trx_id = l.customer_trx_id

     AND h.interface_header_attribute2 = l.interface_line_attribute2

     AND h.trx_number = &Transaction_no.

     ORDER BY l.interface_line_attribute6;


     SELECT h.draft_invoice_num, l.line_num, amount, p.segment1,


     FROM pa_draft_invoices_all h, pa_draft_invoice_items l, pa_projects_all p

     WHERE h.ra_invoice_number = &Transaction_no.

     AND h.project_id = l.project_id

     AND h.draft_invoice_num = l.draft_invoice_num

     AND l.project_id = p.project_id(+)

     ORDER BY l.line_num;

  Thanks — Shivmohan Purohit

Oracle Applications – Commonly Useful SQL Queries

Hello friend, here some of the commonly used sql, i tried to put sql query but don’t know how to put in proper format in html, so if u like to use these sql, u need to remove formatting before to use. let give ur feedback if these are useful for you…. thanks

Quick overview of the structure of chart of accounts

Following query comes in handy. Shows chart of accounts, set of books, accounting flexfield segments in use and their attributes, value sets for the key flexfield segments.

Select sob_name

,      sob.set_of_books_id sob_id

,      sob.chart_of_accounts_id coa_id

,      fifst.id_flex_structure_name struct_name

,      ifs.segment_name

,      ifs.application_column_name column_name

,      sav1.attribute_value  BALANCING

,      sav2.attribute_value COST_CENTER

,      sav3.attribute_value NATURAL_ACCOUNT

,      sav4.attribute_value  INTERCOMPANY

,      sav5.attribute_value SECONDARY_TRACKING

,      sav6.attribute_value GLOBAL

,      ffvs.flex_value_set_name

,      ffvs.flex_value_set_id

from   fnd_id_flex_structures fifs

,      fnd_id_flex_structures_tl fifst

,      fnd_segment_attribute_values  sav1

,      fnd_segment_attribute_values sav2

,      fnd_segment_attribute_values sav3

,      fnd_segment_attribute_values  sav4

,      fnd_segment_attribute_values sav5

,      fnd_segment_attribute_values sav6

,      fnd_id_flex_segments ifs

,      fnd_flex_value_sets ffvs

,      gl_sets_of_books sob

where  1=1

and    fifs.id_flex_code = ‘GL#’

and    fifs.application_id =  fifst.application_id

and    fifs.id_flex_code = fifst.id_flex_code

and    fifs.id_flex_num = fifst.id_flex_num

and    fifs.application_id =  ifs.application_id

and    fifs.id_flex_code = ifs.id_flex_code

and    fifs.id_flex_num = ifs.id_flex_num

and    sav1.application_id =  ifs.application_id

and    sav1.id_flex_code = ifs.id_flex_code

and    sav1.id_flex_num = ifs.id_flex_num

and    sav1.application_column_name =  ifs.application_column_name

and    sav2.application_id =  ifs.application_id

and    sav2.id_flex_code = ifs.id_flex_code

and    sav2.id_flex_num = ifs.id_flex_num

and    sav2.application_column_name =  ifs.application_column_name

and    sav3.application_id =  ifs.application_id

and    sav3.id_flex_code = ifs.id_flex_code

and    sav3.id_flex_num = ifs.id_flex_num

and    sav3.application_column_name =  ifs.application_column_name

and    sav4.application_id =  ifs.application_id

and    sav4.id_flex_code = ifs.id_flex_code

and    sav4.id_flex_num = ifs.id_flex_num

and    sav4.application_column_name =  ifs.application_column_name

and    sav5.application_id =  ifs.application_id

and    sav5.id_flex_code = ifs.id_flex_code

and    sav5.id_flex_num = ifs.id_flex_num

and    sav5.application_column_name =  ifs.application_column_name

and    sav6.application_id =  ifs.application_id

and    sav6.id_flex_code = ifs.id_flex_code

and    sav6.id_flex_num = ifs.id_flex_num

and    sav6.application_column_name =  ifs.application_column_name

and    sav1.segment_attribute_type =  ‘GL_BALANCING’

and    sav2.segment_attribute_type = ‘FA_COST_CTR’

and    sav3.segment_attribute_type = ‘GL_ACCOUNT’

and    sav4.segment_attribute_type  = ‘GL_INTERCOMPANY’

and    sav5.segment_attribute_type =  ‘GL_SECONDARY_TRACKING’

and    sav6.segment_attribute_type =  ‘GL_GLOBAL’

and    ifs.id_flex_num = sob.chart_of_accounts_id

and    ifs.flex_value_set_id = ffvs.flex_value_set_id

— comment the next expression to show all books

— currently it show the info for the site level set profile option value

and    sob.set_of_books_id =


order by, sob.chart_of_accounts_id, ifs.application_column_name;


 Query: Find concurrent requests’ status

How about an all in one query? Note: this may not be complete, so any comments welcome! Also we limit to those requests submitted in the last hour.

select fcr.request_id

,      decode(fcr.phase_code






             ) phase

,      decode(fcr.phase_code


                        ,’Y’,’On Hold’

                        ,decode(sign(fcr.requested_start_date – sysdate)





             ) status

from   fnd_concurrent_requests fcr

,      fnd_lookups fl_p

,      fnd_lookups fl_s

where  1=1

and    fcr.phase_code = fl_p.lookup_code

and    fl_p.lookup_type = ‘CP_PHASE_CODE’

and    fcr.status_code = fl_s.lookup_code

and    fl_s.lookup_type = ‘CP_STATUS_CODE’

and    fcr.request_date > sysdate – 60/1440

order by fcr.request_id desc;


 Concurrent Programs by User and Responsibility

 The following query is a combination SQL behind the “Responsibilities” for a given user on the Users form, and the list of values for the Request Name (Concurrent Program) on the “Submit Requests” form, for version (11i.10.2). This can be useful to provide an “Audit” of what concurrent requests/programs a given Oracle eBusiness Suite user or Responsibility has access to.

 select distinct


,      r.responsibility_name

,      p.user_concurrent_program_name

,      a.application_name

,      a.application_short_name

,      p.concurrent_program_name

,      p.concurrent_program_id

,      p.application_id program_application_id

from fnd_concurrent_programs_vl p

,    fnd_application_vl a

,    fnd_request_group_units u

,    fnd_responsibility_vl r

,    fnd_user fu

,   (select user_id

     ,      responsibility_id

     ,      responsibility_application_id — ,start_date,end_date

     from   fnd_user_resp_groups_indirect

     where (responsibility_id,responsibility_application_id)

        in (select responsibility_id, application_id

            from   fnd_responsibility

            where (version = ‘4’ or

                   version = ‘W’ or

                   version = ‘M’ or

                   version = ‘H’)


     and nvl(start_date,sysdate-1) <= sysdate

     and nvl(end_date,sysdate+1) > sysdate


     select user_id

     ,      responsibility_id

     ,      responsibility_application_id

     from   fnd_user_resp_groups_direct

     where (responsibility_id,responsibility_application_id)

        in (select responsibility_id, application_id

            from   fnd_responsibility

            where (version = ‘4’ or

                   version = ‘W’ or

                   version = ‘M’ or

                   version = ‘H’))

     and nvl(start_date,sysdate-1) <= sysdate

     and nvl(end_date,sysdate+1) > sysdate

     ) user_resps

where p.srs_flag in (‘Y’, ‘Q’)

and   p.enabled_flag = ‘Y’

and   request_set_flag = ‘N’

and (

      (a.application_id = u.unit_application_id

       and u.application_id = r.group_application_id

      and u.request_group_id = r.request_group_id

       and u.request_unit_type = ‘A’)

   or (p.application_id = u.unit_application_id

       and p.concurrent_program_id = u.request_unit_id

       and u.application_id = r.group_application_id

       and u.request_group_id = r.request_group_id

       and u.request_unit_type = ‘P’))

and p.application_id = a.application_id

and user_resps.user_id = fu.user_id

and user_resps.responsibility_id = r.responsibility_id

and user_resps.responsibility_application_id = r.application_id

order by 1,2,4;


Thanks – Shivmohan Purohit