PL/SQL Developer: Can't Connect to Database Error


Right after you install PL/SQL Developer, you realize that you cannot connect to your databases. You get the following message:

ORA-12154: TNS:could not resolve the connect identifier specified

For a moment, you may think, it is something to do with TNSNAMES.ora file or Pinging issue. It is, however, purely a PL/SQL Developer issue. When I downloaded SQL Developer from Oracle and installed it, I could connect to all my databases fine.

The solution is actually pretty simple. There are probably multiple ORACLE_HOME defined (such as for reports and forms, workflow, discoverer, etc.) in your machine, and PL/SQL Developer is probably not finding the correct one to choose from. All you have to do is to choose the correct version for your PL/SQL Developer. To do so, follow the below instruction.
  1. Make sure you are connected to Network/VPN.
  2. Open PL/SQL Developer.
  3. Click on Tools -> Preferences. Select the very first option: Connection.
  4. From Oracle Home drop down list, select the correct version. For example, OH159997875 works for my machine.
  5. Restart PL/SQL Developer.
  6. Cross your fingers and retry to connect to your databases.
  7. If it works, say "Buiya". If it doesn't, scream "F***", and Google search.

Printer type and its significance and How you relate Printer driver and Printer type.

 A printer type identifies a printer by manufacturer and model. A print style tells the printer how a printed output should look. A printer driver delivers commands that tell the printer how to output the specified print style. The ability to print a report in a particular print style depends on the type of printer the report file is sent to.

Step 1. Printer Type: What kind of printer do you have. This is the manufacturer and model. Two examples are a DEC LN03 printer and an HP Laserjet III printer.

Step 2. Print Style: A description of how your report should be printed. Print style determines the:
- Number of lines per page.
- Width of each line.
- Whether a header page should be printed.
- Number of lines per page.
- Width of each line.
- Whether a header page should be printed.

Step 3.Printer Driver: The set of commands that tell a printer how to print in the Print Style chosen.
- Initialization sets printing orientation.
- Reset clears printer’s instructions for next print job.

SetUp Steps
Step 4 - Setup Printer Driver - run on Client
Go into applications as a user with 'System Administrator' responsibility.
Go to Install-->Printer-->Driver
Create a new driver by entering the following information:-
Driver Name : testDriver
User Driver : testDriver
SRW Driver: L
Arguments : print /d:\\\ $PROFILES$.FILENAME (where is the host name returned by the hostname command and is the Share Name you gave to the printer in step (1) above) e.g. 'print /d:\\ukp19999-4w\testprn $PROFILES$.FILENAME'

All other options should be left at default - which is blank for everything except 'Driver Method' which is defaulted to 'Command'.

Step 5 - Setup Print Style - run on Client
Go to Install-->Printer-->Style
Create new style by entering the following information:-
Style Name : testStyle
Seq : 4User
Style : testStyle
SRW Driver : L
Columns : 132
Rows : 64
All other options should be left blank. NOTE - the 'Seq' number needs to be unique, so if you get an error to the effect number 4 has already been used, then use a different number that has not previously been used. The seeded data uses multiples of 5 (ie - 5, 10, 15, etc) up to a value of 185.

Step 6 - Setup Printer Types - run on ClientGo to Install-->Printer-->TypesCreate new type by entering the following information:-
Type : testType
Style : testStyle
Driver Name : testDriver

Step 7 - Register Printer - run on ClientGo to Install-->Printer-->RegisterCreate new printer by entering the following information:-
Printer : testPrinter
Type : testType

Step 8 - Setup profile option - run on ClientGo to Profile-->System Type in 'printer' in the 'Profile' and click on 'Find' This should display the System Profile option called Printer on screen. Click on "Site" and then use the List Of Values to select your printer called'testPrinter' Save this setting, then exit Oracle Applications

Step 9 - Stop and restart Concurrent Manager - run on Server Go to Settings-->Control Panel-->Service Scroll down to and click on 'OracleConcMgr' where is your APPL_CONFIG setting. Click on 'Stop' to stop the service. This could take a couple of minutes whilst the FNDLIBR, etc processes are gracefully stopped. When you are returned to the Services list, click on 'Start' to restart the Concurrent Manager.

FND_GLOBAL.APPS_INITIALIZE in oracle applications

 Setting the Applications Context FND_GLOBAL.APPS_INITIALIZE in oracle applications:

Use the API FND_GLOBAL.APPS_INITIALIZE to set the context of the application in standalone sessions that were not initialized through normal means. Typically, you would use this API in external custom programs that are establishing their own connections.

procedure APPS_INITIALIZE(user_id IN number,resp_id IN number,resp_appl_id IN number security_group_id IN number);
USER_ID - The User ID number.
RESP_ID - The ID number of the responsibility.
RESP_APPL_ID - The ID number of the application too which the responsibility belongs.
SECURITY_GROUP_ID - The ID number of the security GROUP. This argument IS automatically defaulted BY the API. The caller should NOT pass a value FOR it.
Usage: fnd_global.APPS_INITIALIZE (1010,20417,201):
This procedure sets up global variables and profile values in a database session. Call this procedure to initialize the global security context for a database session. You can use it for routines such as PL/SQL or other programs that are not integrated with either the Oracle Applications concurrent processing facility or Oracle Forms (both of which already do a similar initialization for a database session). The typical use for this routine would be as part of the logic for launching a separate non–Forms session from an established Oracle Applications form session. You can also use this procedure to set up a database session for manually testing application code using SQL*Plus. This routine should only be used when the session must be established outside of a normal form or concurrent program connection. You can obtain valid values to use with this procedure by using profile option routines to retrieve these values in an existing Oracle Applications form session.


SELECT fnd_profile.value (‘RESP_ID’) FROM dual
SELECT fnd_profile.value (‘USER_ID’) FROM dual
SELECT fnd_profile.value (‘APPLICATION_ID’) FROM dual

Core HR Basic Concepts


What is Human Resources Management?
Any successful enterprise needs to search for new ways to streamline the processes. One of the important aspects of any enterprise is Workforce. This is nothing but the labor force or personnel.
Human Resources Management is used for addressing the employees’ aspect of this continuous improvement. It helps the enterprises in reviewing and improving their business functions related to personnel management.

Oracle Human Resources – Salient features

·         Manage the entire recruitment cycle
·         Design organizational models that match current and future business strategies and objectives
·         Perform position management by defining and recording required skills, competencies, experience, and qualifications for positions, jobs, and organizations
·         Perform career management functions relating to the definition of competencies, assessments, suitability matching, graphical ranking, and succession planning
·         Administer and maintain benefits plans, coverage levels, and contribution allocations
·         Manage salary proposals and approve these by component

Work Structures

Work structures represent the different ways in which employees can work within the enterprise. These are the frameworks for defining the assignments for the employees. These can be also understood as the representation of the units within the enterprise. The Business Group is the largest unit and represents the enterprise as a whole.
Work structures include internal organizations (e.g. departments, divisions etc.), payrolls, jobs, positions, grades, grading structures and the employee grouping used in the enterprise.


People are the nucleus of any enterprise. Oracle HRMS gives the ability to hold information about the current employees, ex-employees, applicants, contacts, and contingent workers. Along with the standard information, the system can store the information about the addresses, phones, nationality, qualifications, absence history, interview records, etc. Special information can be defined to store any other information for the employees. E.g. special information could be languages known or hobbies. Work-related information such as the preference of working hours (shifts), hours of work could also be maintained.


Assignment for an employee is nothing but the employee’s place within the enterprise. The assignment gives information about the organization for which the employee works. The details about the employee’s location, job, role, position, grade, location, payroll, supervisor, working hours and loads of other information can also be obtained from assignment data. As and when the information about the employee assignment is changed, work history is automatically built up for the employee.
Assignments are also used to group people for reporting, managing and costing purposes. Compensation benefits planning and administration could also be done with the help of the assignments.

Date Tracking

Typical data that changes for any enterprise includes corporate restructuring, employee development-related changes, departmental changes etc. Using Oracle HRMS, the changes can be effectively managed without the need of redefining of the parts.
These changes could be either Dated or Date Tracked.
Work Structures-related data is dated. Dates can be attached to a work structure to maintain the versions ad the structure changes. The previous structure can be maintained as historical data and a new version can be created with the future dates. In Oracle HRMS, the information related to organizations, jobs, grades, locations is dated. The work structure is active as of a Date From, which is entered while defining it. It remains valid till the Date To. A work structure cannot be assigned to an employee before the Date From or after the Date To.
Date Tracked
The dynamic information related to the employees, employee assignments, compensations and benefits is date tracked. Date tracking is a feature available in HRMS, which is useful in maintaining a history of the data when any changes are made. To view the date tracked data from the front end, an effective start date needs to be set. The setting of effective date ensures that the data effective only of that date is available for the users to view, change, validate or report. When any changes are done to the data, the user can choose whether the change would be a correction to the last changes or a new update that needs to be recorded in the history. The Set effective date option is available on the screens which deal with the date-tracked data. This is discussed in detail in the technical section for date tracking.


Navigation: Work Structures à Organization à Description
Business Group
In Oracle HRMS, the enterprise needs to be represented as an employer. This can be done by creating Business Group. Business Group is the largest organizational unit that is set up. Depending on the need, a single business group or multiple business groups can be set up. The defaults entered for a business group are the information that will generally apply to the people entered for that particular business group. These defaults can be overridden.
While setting up the business group, the following information needs to be set up:
·         Employee Number Generation for Employees and Applicants (Manual/Automatic)
·         National Identifier Generation only for Employees (Manual/Automatic)
·         Key flexfield structures that would be used in the business group.
·         Legislation code and default currency
Government Reporting Entity
This is available for selected legislations e.g. US. One more GREs can be set up within a business group. GRE organization is recognized as an employer by the government.
Representing Organizations
All the levels of an enterprise can be represented using Internal Organizations and External Organizations.
Internal Organizations are the departments, divisions or branches where the employees work.
External Organizations could be the hiring agencies, contractors, training institutes etc.
The locations need to be set up before an organization can be defined. This is the exact location of the organization along with the address and phone number.

Navigation: Work Structures à Location

Organization Classifications

This describes the purpose and use of the organization. The organization must be classified as an HR Organization to assign employees. 

·         HR_ORGANIZATION_INFORMATION – Stores the classification and the business group details. ORGANIZATION_ID is linked with HR_ALL_ORGANIZATION_UNITS.ORGANIZATION_ID

Note: The data stored in above details is for a business group. In case of a Global implementation, data for all the business groups will be stored in these tables.
The field business_group_id stores the id of the business group.


Navigation: Work Structures à Job à Description
An employee’s primary role is set up using a job. It is a generic role within the business group that is independent of the organization. E.g. manager's job can be present in any organization.
Job Name is a key flexfield. Each job is held in a job group. A job group stores the jobs of similar types. All the jobs defined in HRMS for employees must be a part of the default HR Job Group.

Job Group Navigation: Work Structures à Job à Job Group

·         PER_JOB_DEFINITIONS – Stores the segment of the KFF. This is linked to fnd tables that store the KFF structure details.
·         PER_JOB_GROUPS

Note: The data stored in above details is for a business group. In case of a Global implementation, data for all the business groups will be stored in these tables.
The field business_group_id stores the id of the business group.


Navigation: Work Structures à Position à Description
Position is a specific occurrence of a job. Position is fixed within an organization. E.g. Sales Manager is an occurrence of the job Manager in the Sales Organization. Similarly, HR Manager will be one more occurrence of the same job in the HR Organization. There could be one or many holders of the position in an organization. This is a key flexfield.
A Position is defined for an Organization and a Job. Positions data is date tracked.

Note: The data stored in above details is for a business group. In case of a Global implementation, data for all the business groups will be stored in these tables.
The field business_group_id stores the id of the business group.


Navigation: Work Structures à Grade à Description

Grades are used to decide the relative status of employee assignments. Compensation and Benefits are also decided based on the grades. E.g. Salary, company cell phone, company car – decided based on the grades. This is a key flexfield.
Valid grades could be defined for a job or for a position. A grade could be a valid grade either for a job or for a position. A single grade cannot be valid one for both a job and a position.

How to open Periods in Oracle Apps


To Create  sales order,  for a given month(period), that  month  should be opened in  five Modules.
·         GL-> Setup-> Open /Close.
·         AP-> Accounting ->Control Payable Periods.
·         PO->Setup->Financials-> Accounting->Open/Close Periods.
·         INV->Accounting Close Cycle ->Inventory Accounting Period->(Selct Org )
·         AR->Control ->Accounting ->Open/Close  Periods.

Query to find AP Supplier Remittance Email Addresses


The following query will display all the active vendors' email addresses by their associated sites. This will work only in Oracle R12.

       -- s.vendor_id,
       -- st.vendor_site_id,
       -- s.party_id,
       -- st.party_site_id,
       s.vendor_name              "Vendor Name",
       s.segment1                 "Vendor Number",
       s.vendor_type_lookup_code  "Vendor Type",
       st.vendor_site_code        "Vendor Site Code",                    "Operating Unit",
       iepa.remit_advice_delivery_method  "Remittance Delivery Method",
       iepa.remit_advice_email            "Remittance Advice Email"
       ap.ap_suppliers              s,
       ap.ap_supplier_sites_all     st,
       hr_operating_units           ou,
       iby.iby_external_payees_all  iepa
   -- AND s.vendor_type_lookup_code = 'EMPLOYEE'
   AND TRUNC (SYSDATEBETWEEN TRUNC (s.start_date_active) AND TRUNC (NVL (s.end_date_active, SYSDATE+1))
   AND s.enabled_flag = 'Y'
   AND iepa.supplier_site_id = st.vendor_site_id
   AND iepa.payee_party_id = s.party_id
   AND st.org_id = ou.organization_id
   AND st.vendor_id = s.vendor_id
 ORDER BY s.vendor_name, st.vendor_site_code;

PO Approval Hierarchy Query -R12


pha.segment1 AS PO_NUMBER,
papf.full_name AS performed_by,
(pha.rate * pha.blanket_total_amount) AS BLANKET_TOTAL_AMOUNT_CAD,
pah.object_revision_num AS Revision_Number
FROM po_action_history pah,
per_all_people_f papf,
po_headers_all pha,
( SELECT object_id,
CASE WHEN COUNT (Action_Code) <= 1 THEN 'N' ELSE 'Y' END
FROM po_action_history
WHERE 1 = 1
AND Action_Code = 'FORWARD'
AND object_sub_type_code = 'BLANKET'
GROUP BY object_id, Action_Code, object_revision_num
HAVING COUNT (Action_Code) > 0) abc
WHERE pah.action_code = 'APPROVE'
AND pah.employee_id = papf.person_id
AND pah.object_id = pha.po_header_id
AND pah.object_id = abc.object_id
AND pah.object_revision_num = abc.object_revision_num
AND pha.segment1 = 'XX_PO_NUMBER' -- PO Number
AND pah.object_sub_type_code = 'BLANKET'
ORDER BY pha.segment1 ASC, pah.object_revision_num

Cancel po line using Plsql script PO_DOCUMENT_CONTROL_PUB.CONTROL_DOCUMENT

 ************** Cancel PO Line *****************#

 v_return_status   VARCHAR2 (10);
 v_msg_data        VARCHAR2(1000);
 v_po_header_id    NUMBER        := 2503180;
 v_doc_subtype     VARCHAR2(10)  := 'STANDARD';
 v_doc_type        VARCHAR2(10)  := 'PO';
 v_org_id          NUMBER        := 894;
 v_action          VARCHAR2(10)  := 'CANCEL';
 v_action_date     DATE          := SYSDATE;
 l_user_id NUMBER :=40231;
 l_resp_id NUMBER :=20707;
 l_appl_id NUMBER :=201;


 fnd_global.apps_initialize(l_user_id, l_resp_id, l_appl_id);
mo_global.set_policy_context ('S',v_org_id);
 DBMS_OUTPUT.PUT_LINE ('Calling API For Cancelling Documents');
                                         ,p_init_msg_list  =>fnd_api.g_true 
                                         ,p_commit          =>fnd_api.g_false
                                         ,x_return_status  =>v_return_status
                                         ,p_doc_type      =>v_doc_type
                                         ,p_doc_subtype   =>v_doc_subtype
                                         ,p_doc_id        =>v_po_header_id
                                         ,p_doc_num       =>NULL
                                         ,p_release_id    =>NULL
                                         ,p_release_num   =>NULL
                                         ,p_doc_line_id   =>NULL
                                         ,p_doc_line_num  =>NULL
                                         ,p_doc_line_loc_id =>NULL
                                         ,p_doc_shipment_num => NULL
                                         ,p_action           => v_action
                                         ,p_action_date      => v_action_date
                                         ,p_cancel_reason    => 'OLD PURCHASE ORDER'
                                         ,p_cancel_reqs_flag => 'N'
                                         ,p_print_flag       => NULL
                                         ,p_note_to_vendor   => NULL
                                         ,p_use_gldate       => NULL
                                         ,p_org_id           => v_org_id);
DBMS_OUTPUT.PUT_LINE('The Return Status of the API : '|| v_return_status);
IF v_return_status = fnd_api.g_ret_sts_success THEN
 DBMS_OUTPUT.PUT_LINE ('Cancellation of PO is Sucessfull : '||v_po_header_id) ;
  DBMS_OUTPUT.PUT_LINE ('Cancellation of PO Failed ');
   v_msg_data := FND_MSG_PUB.GET( p_msg_index =>i,p_encoded => 'F');
  DBMS_OUTPUT.PUT_LINE( i|| ') '|| v_msg_data);