Export a concurrent program and executable using FNDLOAD

Hello Friends, a very useful easy to do……. How to export a concurrent program and executable using FNDLOAD?  








Please use the following FNDLOAD command to download a concurrent program to a flat file :
FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt PROGRAM APPLICATION_SHORT_NAME=”<your_application_short_name>” CONCURRENT_PROGRAM_NAME=”<your_concurrent_name>”

To upload it to another environment, use the following command :
FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct file_name.ldt

You may found additional explanation in the Oracle Applications System Administrator’s Guide – configuration Release 11i ( appendix C)

 thanks – Shivmohan

Embracing yourself

Here i sharing a very articulated and thoughtful article written by one of my inspirational friend, you can find more at — sandeep joshi’s blog

You will never be the perfect entrepreneur. No matter how hard you work to avoid catastrophes… at some point, disasters will happen. At times, you will look bad, and your reputation will be injured. But, that doesn’t mean you or your company have to suffer long-term repercussions because of it.People are generally more forgiving than they seem. If you are willing to “expose” your weaknesses, your contacts will feel your honesty and sincerity. The harder you try to be the “perfect” small business owner (and hide your errors), the more detached from your contacts you become. And, the more likely they are to mistrust you.Enjoy your imperfections and learn to use them to your advantage!

Here are some steps for embracing your own humanity, and turning “bad” situations into incredible, marketing situations.

Step 1- Laugh at yourself and your mistakes. (Getting upset about the situation won’t fix things. Look at this as an opportunity instead.)

Step 2- Fix the disaster, tenaciously.

Step 3- Offer the injured customers or prospects even more than they expect. (If done properly, these individuals may become your biggest fans.)

Step 4- Use the situation to fix your current systems. (Making a mistake is alright. Repeating that mistake can be devastating.)

Step 5- Freely share these experiences in marketing messages, blogs, webinars, or as a “reason” to have another sale or promotion

Eclipsys India in Pune – New Openings – Testing and Apps DBA

Hello Friends, This is first time i posting for opening, usually this site is not for job posting or referral, here i posting these as these are for my team, here i am looking 2 Testing analyst for oracle application domain and 2 Apps DBA for Oracle Applications.

If you like to referr yourself or any of your friends, please forward CV/resume to shivmohan.purohit@eclipsys.com

India Tester /QA Analyst ( 2 Positions)
Primary responsibilities –

  • This position is responsible for ensuring compliance and quality standards are met for multiple information system products and services. The incumbent may coordinate/ facilitate training sessions for internal/external customers and may assist in change & release management functions.
  • Define, document and communicate Software/Hardware Testing process and procedures for the enterprise.
  • Analyze, design and construct a variety of quality control tests that ensure software/hardware meets specified standards/designs and/or requirements.
  • Execute a variety of quality control tests encompassing both manual and automated tests.
  • Design and document test strategies and test plans for IT projects and efforts.
  • Conduct defect management process and root cause analysis.
  • Analyze, document and communicate test results to project teams.
  • Improve Software/Hardware testing process and procedures.
  • Create and manage test data efficiently
  • Coordinate User Acceptance Testing with business partners.

Skill Required –

  • Bachelor’s degree in computer science, information technology, or related field
  • 2-5 years experience in software/hardware testing
  • Strong working knowledge and understanding of quality assurance and software development methodologies
  • Experience leading a project that includes creating estimates and managing QA deliverables.
  • Experience in all test phases (Functional manual and automation, system and performance)
  • Must possess strong hands-on knowledge with HP Test Tools (Quality Center, QTP, LoadRunner or any automation tool)
  • Working scripting and programming knowledge
  • Ideal candidate will be self-starter, very detailed-oriented and possess sharp problem solving and documentation skills
  • Preferred Qualifications:
  • Working SQL knowledge
  • DBMS familiarity
  • Working knowledge of infrastructure/operational environments (server, hardware, network, etc..)
  • Knowledge of the SDLC process – 2 years; exposure to multiple Project methodologies a Plus
  • Working Knowledge of Oracle ERP, Purchasing, Oracle 11.5.10-R12 a plus, Inventory, BI tools, FA, Projects
  • 5 years testing Oracle applications with legacy interfaces.


Oracle Database Administrator ( 2 Positions)

 Primary Responsibilities –

  • Plans, coordinates and administers matters related to Oracle database(s), including base definition, structures, documentation, upgrades, long-range requirements, operational guidelines and protection. Ensures accuracy and completeness of data in master files and various support tools, such as base dictionaries. Establishes and maintains security integrity controls. Formulates and monitors policies, procedures and standards relating to database management. May direct the following: troubleshooting, recovery, tuning of the database, software installation and upgrades, resolving Oracle errors and failures, auditing activities, and resource utilization. Will be internal, system-focused, working within our department, and occasional with outside groups (such as finance, sales, human resources). Will include company-wide, Web-enabled solutions. Must participate in fully documented change management process, work with system administrators to ensure backups, network connectivity, and other basic infrastructure needs are met. Additionally, will be asked to participate in periodic audit activities to ensure basic controls are maintained and functioning as it pertains to database security, compliance with change management etc. For any and all activities that are unique to Eclipsys, detailed training will be provided to ensure employee understands his/her role and responsibilities.
  • KNOWLEDGE: Uses professional concepts; applies company policies and procedures to resolve a variety of issues. Must be an 11i/12i ERP Application DBA and added plus performed Linus Adminfunctionsc familiarity with Oracle 11g a huge plus. PLSQL TOAD Oracle Developer
  • JOB COMPLEXITY: Works on problems of moderate scope where analysis of situations or data requires a review of a variety of factors. Exercises judgment within defined procedures and practices to determine appropriate action. Has internal and some external contacts.
  • SUPERVISION: Normally receives general instructions on routine work, detailed instructions on new projects or assignments.

Discoverer SQL: Long Running Discoverer Report

Discoverer SQL: Long Running Discoverer Report

Here is a small SQL quite useful to work with discoverer to identify long running reports and start looking into the reason / troubleshooting.

SELECT q.qs_doc_name Worksheet,

 q.qs_doc_details Sheet ,

 q.qs_num_rows Lines,


 q.qs_act_elap_time Seconds,

 FLOOR(q.qs_act_elap_time/60) || ‘.’ || MOD(FLOOR(q.qs_act_elap_time), 60) minutes, 

 q.qs_created_date Executed,

 q.QS_DOC_OWNER Owner,

 u.user_name APPS_User,



WHEN q.qs_state = 1 THEN ‘RUNNING’

WHEN q.qs_state = 2 THEN ‘COMPLETED’


  FROM discovr_apps.eul5_qpp_stats q,

 fnd_user u

 WHERE TRUNC(qs_created_date) >= TRUNC(SYSDATE)-1

AND TO_NUMBER(SUBSTR(q.qs_created_by,2,8)) = u.user_id

–AND u.user_name LIKE ‘D%’

–AND q.qs_state =1

  –AND FLOOR(q.qs_act_elap_time/60) >= 30 

  ORDER BY q.qs_act_elap_time DESC;

Thanks – Shivmohan Purohit

Sample Example on How To Extract Worksheet IDs of a Workbook through Discoverer Desktop via the d51wkdmp utility

Sample Example on How To Extract Worksheet IDs of a Workbook through Discoverer Desktop via the d51wkdmp utility


d51wkdmp.exe <Workbook_Name> <Output_File> DB <Connect_String>  <Eul_Schema> -f

(1) Log into your pc where Discoverer Desktop is installed.

(2) Find out the exact location of the ‘d51wkdmp.exe’ file.

(3) Click <Start> <Run> and enter ‘cmd’ to go to command prompt.

(4) Enter and run the following at the prompt, for example:
C:\> d:\oracle\BI_1012\bin\d51wkdmp.exe “Employee By Dept” Outfile2.txt DB scott/tiger@TestDB1 Eul_owner -f

WHERE there are 3 Worksheets in this example.

RESULT:  Outfile2.txt

EUL Item Reference
IoId = 16
Id = 100671
Identifier = DEPTNO
Name = Deptno
Folder Identifier = EMP_1
Folder Name = Emp 1
*** Found in EUL by id ***
EUL Item Reference
IoId = 24
Id = 100664
Identifier = EMPNO
Name = Empno
Folder Identifier = EMP_1
Folder Name = Emp 1
*** Found in EUL by id ***
EUL Item Reference
IoId = 32
Id = 100665
Identifier = ENAME
Name = Ename
Folder Identifier = EMP_1
Folder Name = Emp 1
*** Found in EUL by id ***
EUL Item Reference
IoId = 40
Id = 100666
Identifier = JOB
Name = Job
Folder Identifier = EMP_1
Folder Name = Emp 1
*** Found in EUL by id ***
EUL Item Reference
IoId = 48
Id = 100667
Identifier = MGR
Name = Mgr
Folder Identifier = EMP_1
Folder Name = Emp 1
*** Found in EUL by id ***
EUL Item Reference
IoId = 56
Id = 100668
Identifier = HIREDATE
Name = Hiredate
Folder Identifier = EMP_1
Folder Name = Emp 1
*** Found in EUL by id ***
EUL Item Reference
IoId = 64
Id = 100669
Identifier = SAL
Name = Sal
Folder Identifier = EMP_1
Folder Name = Emp 1
*** Found in EUL by id ***
EUL Private Item
Id = -32
Name = Sal SUM
Identifier = 7
Desc =
DataType = 2
Placement = 1
Hidden = 0
IsACalc = 0
IOFormula = [1,1]([6,64])
DisplayFormula = SUM(Sal)
Query Request QR1
Distinct = 1
Axis Item Usage – Name = EUL Item – Emp 1.Empno
Axis Item Usage – Name = EUL Item – Emp 1.Ename
Axis Item Usage – Name = EUL Item – Emp 1.Job
Axis Item Usage – Name = EUL Item – Emp 1.Mgr
Axis Item Usage – Name = EUL Item – Emp 1.Hiredat
Axis Item Usage – Name = EUL Item – Emp 1.Deptno
Measure Item Usage – Name = Calculation – Sal SUM
Identifier = 68

Sheet Number 1
Sheet Name = Sheet 1
Sheet Unique Name = Workbook 11503332070
Sheet Identifier = 1
Query(s) used =
Query 1
Items :-
EUL Item – Emp 1.Empno
EUL Item – Emp 1.Ename
EUL Item – Emp 1.Job
EUL Item – Emp 1.Mgr
EUL Item – Emp 1.Hiredate
EUL Item – Emp 1.Deptno
Calculation – Sal SUM



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_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.name, 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