Select “Workflow Administrator Web Applications” and Select the function “Status Monitor”. This opens a jsp page show below.
For the purchase orders stuck in the flow, please locate “PO Approval” workflow using search function. This windows provides multiple choices to limit the amount of data retrieved. Apply it whenever it is possible to reduce the time collecting and producing data.
Image: Finding the correct workflow (for Purchase requests stuck in the workflow)
For Purchase Requests stuck in the work flow, please use the following query
select requisition_header_id, segment1, wf_item_type, wf_item_key, authorization_status, org_id from po_requisition_headers_all where segment1 = ‘1980’ and org_id = 105;
for Purchase order change the table name to PO_HEADERS_ALL
to find the wf_item_key which is referred as “item key” with the JSP form.
Once the desired information derived
Click on “Activity History” button
Now you may use “Reassign” function (Shown within green rectangle) to reassign the Purchase Request for approval by next immediate subordinate.
You may use the “Rewind” button to rewind the workflow to an earlier stage as well
The first solution was “too” professional approach for guys like us, who hardly have anything more than few hours of experiences with .net programming
The second solution looked more appropriate as we were looking at something which could be easily altered and adopted to our particular requirement.
Hence we copied the scripts available with the link and started altering them, and with our “extreme” level of exposure to the technology, almost after 72 hours we were able to shape up something which fits into our requirements, somehow and we are sharing the same with you.
We know, it could be done much easier or in a simpler manner, well that part we are leaving for the seasoned .net developers.
First create a .aspx file with name “Default” (eg:Default.aspx) and copy the following code inside the file (Notepad++)
One of the best features of Oracle applications is the flexibility to add custom applications and extend the functionality of the business suite. Many time developers who are not well versed with the Oracle’s guidelines for custom development for Oracle applications will totally ignore the pre-requisites for fnd_standard.set_who API to work properly by avoiding to include the following mandatory columns while designing tables
Which will fail the API call and result in “No record history available here” notification
Another possibility is, developer adds these columns with the custom tables at later stages and manually add the columns to the block, without involving the datablock wizard, thus not properly linking the block with newly created columns.
Manually adding the columns with proper column names and data types may not generate an error while compiling, however the API will not able to see those columns.
The best method to avoid this problem is, by running the data block wizard once after new columns are added to the custom table(s)
Run the data block wizard, refresh the data source and make sure you don’t have any column within the left side pan
Recompile and test the custom application once again. 99% this method should solve the fnd_standard.set_who API not updating information.
Why not? From our experiences, Oracle applications has one of the best transaction auditing approach, by logging who created and updated transactions while data is processed through a form based application.
Adapting the same methodology, recently we have created almost similar functionality for our upcoming custom applications. If you intend to use this solution with your own forms applications, make sure that your tables have the following four columns as mandatory.
ALTER TABLE BAC_MENU
CREATED_BY NUMBER ,
CREATION_DATE DATE ,
LAST_UPDATE_DATE DATE ,
PROCEDURE set_who IS
trx_date date := sysdate;
--Get the current block name
blk_name := name_in(':SYSTEM.CURRENT_BLOCK');
--Judge the current transaction mode
curr_mode := name_in(':SYSTEM.RECORD_STATUS');
--if you are passing login details
--using global variables, adjust the following
curr_user := name_in(':PARAMETER.P_USER_ID');
if curr_mode='CHANGED' then
elsif curr_mode = 'INSERT' then
--Mandatory columns with tables
--against which you will log
--the insert, update user details &amp; time
Now attach this procedure with individual forms or make it a part of a custom PL/SQL library for global calls. You can call the procedure from PRE-INSERT and PRE-UPDATE triggers at block level to achieve the scope.
How are you guys? Happy New Year! We were quite busy and quiet during last few weeks. Here we are once again, coming up with a simple, yet powerful utility which could make a system administrator’s life bit easier.
We have a 12 years old Oracle database server with oracle data folder residing in D:\ drive with less than 20GB total free space where we do a full export everyday by late night.
Each full export (.dmp) file is almost 5GB, thus occupying the entire 20GB by every forth day. We were painfully following up the schedules and deleting the files to preserve much valued disk space.
Well, finally we decided to write a small console application to handle this task through a scheduled task. You can download the entire solution from following link
We developed this solution using Visual Studio Express for Desktop, ie, akka free version of VS 2012. You may need to download the same, so that you can open the solution.
However, to make stuff easier, we are copying the module1.vb code over here
[sourcecode language="vb" padlinenumbers="true" wraplines="true" gutter="false"]
Dim strStartupArguments() As String, intCount As Integer
Dim fldrName As String, fileType As String, nDays As Integer
strStartupArguments = System.Environment.GetCommandLineArgs
For intCount = 0 To UBound(strStartupArguments)
Select Case strStartupArguments(intCount).ToLower
' fldrName = strStartupArguments(intCount).ToLower
fldrName = strStartupArguments(intCount + 1)
fileType = strStartupArguments(intCount + 1)
nDays = strStartupArguments(intCount + 1)
If ((fldrName Is Nothing) Or (fileType Is Nothing) Or (nDays = 0)) Then
Console.WriteLine("No or not all arguments given, USAGE DeleteOldFiles.exe -dFolder <folder name> -fType <*.extention> -nDays <N> ")
Console.WriteLine("Example: DeleteOldFiles.exe -dFolder C:\myfolder -fType *.txt -nDays 100")
Console.WriteLine(" C:\myfolder --is the target folder from files will be deleted")
Console.WriteLine(" *.txt --tells the system what kind of files should be deleted ")
Console.WriteLine(" 100 --defines file age in number of days")
Dim fileName = Date.Now.ToString("ddMMyyyy") & ".log"
Dim filePath = IO.Path.Combine(fldrName, fileName)
Using sw As StreamWriter = New StreamWriter(filePath)
sw.WriteLine("File Name" + ";" + "Creation Date" + ";" + "Deletion Time")
' For Each file As IO.FileInfo In New IO.DirectoryInfo("D:\Documents").GetFiles("*.pdf")
For Each file As IO.FileInfo In New IO.DirectoryInfo(fldrName).GetFiles(fileType)
' Console.WriteLine((Now - file.CreationTime))
If file.IsReadOnly = False Then
If (Now - file.CreationTime).Days >= nDays Then
sw.WriteLine(file.Name + ";" + file.CreationTime + ";" + Now)
Catch ex As Exception
The default solution name is “DeleteOldFiles” hence when you open and build the solution, the .exe file name would be DeleteOldFiles.exe
DeleteOldFiles.exe –dFolder <driverletter:\foldername> –fType *.extension –nDays <number of days>
Now you can schedule a job, and let this small piece of application doing the cleanup job for you
Yes, it does a logging for you :), so that you can always check which files were deleted from the folder (for eg: C\temp). The log file location will be the same folder from which the files were permanently deleted.
Tested on Windows Server 2003, Windows 8 64Bit, Windows 2008 Server
So, just upgraded to Windows 8 and having troubles with Metro tile applications?
Are you using Avast Antivirus (may be applicable to other antivirus applications also) by any chance? if YES is the answer, hurry up, there is a new version of Avast, update it and after restart you will find the wonderful Metro applications in action.
If you are asked to make a report for listing the elements with a named Position Hierarchy, it could quite difficult because of the complexity with the way Oracle is maintaining the position hierarchies. please find below a practical solution to this requirement
Create a view
Create view XXPOSHIERARCHY_V AS SELECT pps.NAME, LPAD (' ', 5 * LEVEL) || has.NAME hierarchy, has.position_id,LEVEL rep_level, hap.NAME parent_name, pse.parent_position_id, has.NAME child_name, pse.subordinate_position_id FROM (SELECT NAME, position_id FROM hr_all_positions_f_tl WHERE LANGUAGE = USERENV ('LANG')) hap, (SELECT NAME, position_id FROM hr_all_positions_f_tl WHERE LANGUAGE = USERENV ('LANG')) has, per_pos_structure_elements pse, per_pos_structure_versions pve, per_position_structures pps WHERE pse.business_group_id = 81 --Replace with your own business group id AND pve.position_structure_id = pps.position_structure_id AND pse.POS_STRUCTURE_VERSION_ID = pve.POS_STRUCTURE_VERSION_ID AND sysdate between pve.date_from and NVL(pve.date_to, sysdate) AND hap.position_id = pse.parent_position_id AND has.position_id = pse.subordinate_position_id start with pse.parent_position_id = (SELECT parent_position_id FROM per_pos_structure_elements a WHERE A.POS_STRUCTURE_VERSION_ID = pse.pos_structure_version_id AND a.POS_STRUCTURE_ELEMENT_ID = (SELECT MIN (POS_STRUCTURE_ELEMENT_ID) FROM per_pos_structure_elements b WHERE b.POS_STRUCTURE_VERSION_ID = A.POS_STRUCTURE_VERSION_ID)) CONNECT BY PRIOR pse.subordinate_position_id = pse.parent_position_id AND PRIOR pse.pos_structure_version_id = pse.pos_structure_version_id AND PRIOR pse.business_group_id = pse.business_group_id;
Now from the view above, you can easily populate the reporting structure using the following query (including the positions, employee names etc)
Select 0 rnum, opv.parent_name position_effective, 0 rep_level, paaf.person_id, papf.full_name, opv.parent_position_id, opv.name hierarchy_name from XXPOSHIERARCHY_V opv, per_all_assignments_f paaf, per_all_people_f papf where NAME LIKE 'XYZ PR%' and paaf.position_id = opv.parent_position_id and papf.person_id = paaf.person_id and sysdate between paaf.effective_start_date and nvl(paaf.effective_end_date, sysdate) and sysdate between papf.effective_start_date and nvl(papf.effective_end_date, sysdate) and rep_level = 1 UNION ALL Select ROWNUM rnum, opv.hierarchy, opv.rep_level, paaf.person_id, papf.full_name, opv.parent_position_id, opv.name from XXPOSHIERARCHY_V opv, per_all_assignments_f paaf, per_all_people_f papf where NAME LIKE 'XYZ PR%' and paaf.position_id = opv.position_id and papf.person_id = paaf.person_id and sysdate between paaf.effective_start_date and nvl(paaf.effective_end_date, sysdate) and sysdate between papf.effective_start_date and nvl(papf.effective_end_date, sysdate) order by 1;
Adding rownum along with the query will provide you the flexibility to maintain the rpad -ed position names intact while retrieving a particular position hierarchy details.
In order to make the entire reporting dynamic we have created a PL/SQL sequence, populating all hierarchies into a local table. Please find the logic below
and by executing the below PL/SQL sequence populate the table one time prior the report ran (please do not forget to truncate the table prior each report run!)
SET SERVEROUTPUT ON;
DECLARE CURSOR c1 IS SELECT DISTINCT name hierarchy_name FROM XXPOSHIERARCHY_V ORDER BY 1; BEGIN FOR i IN C1 LOOP DBMS_OUTPUT.PUT_LINE (i.hierarchy_name);
INSERT INTO XXHIERELEMENTS SELECT 0 rnum, opv.parent_name position_effective, 0 rep_level, paaf.person_id, papf.full_name, opv.parent_position_id, opv.name hierarchy_name FROM XXPOSHIERARCHY_V opv, per_all_assignments_f paaf, per_all_people_f papf WHERE NAME = i.hierarchy_name AND paaf.position_id = opv.parent_position_id AND papf.person_id = paaf.person_id AND SYSDATE BETWEEN paaf.effective_start_date AND NVL (paaf.effective_end_date, SYSDATE) AND SYSDATE BETWEEN papf.effective_start_date AND NVL (papf.effective_end_date, SYSDATE) AND rep_level = 1 UNION ALL SELECT ROWNUM rnum, opv.hierarchy, opv.rep_level, paaf.person_id, papf.full_name, opv.parent_position_id, opv.name FROM XXPOSHIERARCHY_V opv, per_all_assignments_f paaf, per_all_people_f papf WHERE NAME = i.hierarchy_name AND paaf.position_id = opv.position_id AND papf.person_id = paaf.person_id AND SYSDATE BETWEEN paaf.effective_start_date AND NVL (paaf.effective_end_date, SYSDATE) AND SYSDATE BETWEEN papf.effective_start_date AND NVL (papf.effective_end_date, SYSDATE); END LOOP;
Another situation could be when the customer you have created doesn’t have Payment Terms set. Once the Payment Terms set, open the Sales Order that is stuck using Order organizer, set the payment terms on both header and line levels and try to book once again. This should solve the situation.
A user may be provided this particular error while trying to book a sales order. If checked in Oracle support documentation, you may not find much useful information addressing this particular error.
Our case, most of the times this error occured while users tried to book an order against a customer who doesn’t have site information for that particular organization. ie, Customer ‘ABC’ was created for organization ‘XYZ’ and users from organization ‘DEF’ tried to create sales orders for customer ‘ABC’. This issue could happen to any organization which has “multi-org” structure.
The first thing the consultant should check should be the customer site information for the organization, if the site information is missing, proceed towards creating it. Once the site information is updated, manually select the shipping address and other details and proceed to book the order.
If the error is not addressed after creating the site information, proceed to next level of trouble shooting. Metalink documentation points towards items not in price list and tax etc. Metalink document id(s) : 396427.1, 988810.1