Oracle Applications R12, Re-assign PR/PO stuck in the workflow (awaiting approval)

February 15, 2014

 

Handling Purchase Orders stuck in the work flow.

Logon to the instance as “SYSADMIN”

1

Select “Workflow Administrator Web Applications” and Select the function “Status Monitor”. This opens a jsp page show below.

2

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)

3

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.

4

Once the desired information derived

Click on “Activity History” button

5

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

Hope you enjoyed another quality solution from us

for Windows7bugs

rajesh

Advertisements

A simple asp.net application for listing files and folders within a folder

December 25, 2013

We tease each other calling “Google Programmers” occasionally as we just cut and paste code from forums/websites and meet new business requirements and deadlines.

We started revamping our intranet site recently and was frantically looking at a prospective of converting few .asp driven details using .net application(s)

Our primary requirement was to list the content of a folder, along with subdirectories and files (mostly .pdf and .doc/.docx/.xls/.xlsx)

After loads of googling we came across two potential solutions and they were

http://www.4guysfromrolla.com/articles/090110-1.aspx

http://www.encodedna.com/2013/08/extract-display-files-from-folder-and-bind-with-gridview.htm

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++)

<%@ Page Language="C#" AutoEventWireup="true"  CodeFile="Default.aspx.cs" Inherits="_Default" %>
<!DOCTYPE html>
<html>
<head>
    <title>Display | Bind Files from Folder to GridView</title>
    <style type="text/css">
        div { font:11px Verdana; width:750px }
        .grid { width:100%; font:inherit; background-color:#FFF; border:solid 1px #525252}
        .grid td { font:inherit; padding:2px; border:solid 1px #C1C1C1; color:#333; text-align:left;
            text-transform: capitalize}
        .grid th { padding:3px; color:#FFF; background:#424242 url(grd.png) repeat-x top; 
            border-left:solid 1px #525252; font:inherit; text-align:center; text-transform:uppercase}
        #drop1 { width:70px; padding:3px }
    </style>
</head>
<body>
   <%-- <% 
        Response.Write("<br/> " + HttpContext.Current.Request.Url.Host);
        Response.Write("<br/> " + HttpContext.Current.Request.Url.Authority);
        Response.Write("<br/> " + HttpContext.Current.Request.Url.AbsolutePath);
        Response.Write("<br/> " + HttpContext.Current.Request.ApplicationPath);
        Response.Write("<br/> " + HttpContext.Current.Request.Url.AbsoluteUri);
        Response.Write("<br/> " + HttpContext.Current.Request.Url.PathAndQuery);
         %>--%>
    <form id="form1" runat="server">
    <div>
        <%--LISTBOX SHOWING A LIST OF FILE TYPES.--%>
       <%-- <p> <asp:ListBox id="drop1" rows="3" runat="server">
                <asp:ListItem selected="true">All</asp:ListItem>
                <asp:ListItem>pdf</asp:ListItem>
                <asp:ListItem>jpg</asp:ListItem>
                <asp:ListItem>png</asp:ListItem>
                <asp:ListItem>txt</asp:ListItem> 
                <asp:ListItem>doclt</asp:ListItem> 
            </asp:ListBox>
            <input type="button" id="btShowFiles" onserverclick="btShowFiles_Click" value="Show Files" runat="server" />
        </p>--%>

        <%--ADD A GRIDVIEW WITH FEW COLUMNS--%>
         <asp:GridView ID="GridView2" CssClass="grid" GridLines="None" ShowFooter="true" 
            AllowPaging="true" PageSize="5" AutoGenerateColumns="false" 
            runat="server">

                <Columns>

                    <asp:TemplateField HeaderText="Folder(s)">
                        <ItemTemplate>
                            <asp:HyperLink runat="server" ID="HyperLink1" Text='<%# Eval("Name") %>' NavigateUrl='<%# HttpContext.Current.Request.Url.AbsoluteUri +"/"+ Eval("Name") %>' />
                        </ItemTemplate>
                    </asp:TemplateField>
                </Columns>
        </asp:GridView>

        <asp:GridView ID="GridView1" CssClass="grid" GridLines="None" ShowFooter="true" 
            AllowPaging="true" PageSize="20" AutoGenerateColumns="false" 
            OnPageIndexChanging="GridView1_PageIndexChanging" runat="server">

                <Columns>

                    <asp:TemplateField HeaderText="Name">

                        <ItemTemplate>
                            <%--<asp:Label ID="lblName" runat="server" Text='<%#System.IO.Path.GetFileNameWithoutExtension(Eval("Name").ToString()) %>'></asp:Label>--%>
                             <asp:HyperLink runat="server" ID="HyperLink2" Text='<%# System.IO.Path.GetFileNameWithoutExtension(Eval("Name").ToString()) %>' NavigateUrl='<%# 
                              Request.QueryString["p"] +"/"+ Eval("Name") %>' />
                        </ItemTemplate>
                    </asp:TemplateField>

				   <%--                         
                    <asp:TemplateField HeaderText="File Length">
                    <ItemTemplate><asp:Label ID="lblLen" runat="server" Text='<%#Eval("Length")%>'></asp:Label>
                        </ItemTemplate>
                </asp:TemplateField>
                <asp:TemplateField HeaderText="File Extention">
                    <ItemTemplate><asp:Label ID="lblFileType" runat="server" Text='<%#Eval("Extension")%>'>
                        </asp:Label></ItemTemplate>
                </asp:TemplateField>--%>
                <asp:TemplateField HeaderText="Creation Date & Time">
                    <ItemTemplate><asp:Label ID="lblDateTime" runat="server" Text='<%#Eval("CreationTime")%>'>
                        </asp:Label></ItemTemplate>
                </asp:TemplateField>
            </Columns>
        </asp:GridView>

        <%--A LABEL SHOWING NUMBER OF FILES FOUND IN THE FOLDER.--%>
        <p><asp:Label Text="" ID="lblMsg" runat="server"></asp:Label></p>
    </div>
    </form>
</body>
</html>

Now create another file “Default.aspx.cs” and copy the below code inside the file

using System;
using System.IO;
using System.Globalization;

public partial class _Default : System.Web.UI.Page 
{

    protected void btShowFiles_Click(object sender, EventArgs e)
    {
     //   ViewState["FileType"] = drop1.SelectedValue;     // GET THE FILE TYPE.
        GetFilesFromFolder();
    }

    // GRIDVIEW PAGING.
    protected void GridView1_PageIndexChanging(object sender, 
        System.Web.UI.WebControls.GridViewPageEventArgs e)
    {
        GridView1.PageIndex = e.NewPageIndex;
        GetFilesFromFolder();
    }

    protected void GetFilesFromFolder()
    {
        String pathname = Request.QueryString["p"];

        //String pathname;

      //  pathname = Request.QueryString["p"];

        //response.redirect("a.aspx?ids=1&val=100",true)

        //and in the second page that is a.aspx

        //a=Request.QueryString("ids") 

        //b= Request.QueryString("val")

        // GET A LIST OF FILES FROM A SPECIFILED FOLDER.

        DirectoryInfo objDir = new DirectoryInfo(Server.MapPath(pathname)); 

        //(@"D:\Dell Drivers");
		//(Server.MapPath("listfiles\\"));    

        FileInfo[] listfiles = objDir.GetFiles("*");

        DirectoryInfo[] listDirs = objDir.GetDirectories(".");

        if (listDirs.Length > 0)
        {
            GridView2.Visible = true;
            GridView2.DataSource = listDirs;
            GridView2.DataBind();
        }
        else
        {
            GridView2.Visible = false;
        }

        if (listfiles.Length > 0)
        {
            // BIND THE LIST OF FILES (IF ANY) WITH GRIDVIEW.
            GridView1.Visible = true;
            GridView1.DataSource = listfiles; 
            GridView1.DataBind();

            lblMsg.Text = listfiles.Length + " files found";    
        }
        else {
            GridView1.Visible = false ;
            lblMsg.Text = "No files found";
        }
    }
    protected void Page_Load(object sender, EventArgs e)
    {
        GetFilesFromFolder();
    }
}

Now move both the files to your web application folder. We were using the default “C:\inetpub\wwwroot” for the testing, hence moved the files to there.

Now you can start calling the application like following

http://localhost/Default.aspx?p=memos

Where memos is an actually folder available within “C:\inetpub\wwwroot” path

If you have folders within the “memos” folder, the application will present you view like following

image

Based on whether you have subfolders within the “memos” folder the application will either display or hide the Folder(s) grid, the same applies to Files grid as well

The best part is, this application can drill down into any level of nested folders and populate folder and file lists as URLs.

We thank A2S from forums.asp.net for helping us to strip out the extensions from filenames. Please refer to the below link for more details.

http://forums.asp.net/t/1958264.aspx?Stripping+the+extension+from+file+name+derived+using+Eval+Name+

Enjoy guys!

After all it is another wonderful Christmas time

regards,

for Windows7bugs

rajesh


Oracle applications, fnd_standard.set_who

September 5, 2013

 

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

  1. CREATION_DATE    DATE
  2. CREATED_BY    NUMBER
  3. LAST_UPDATE_DATE    DATE
  4. LAST_UPDATED_BY    NUMBER
  5. LAST_UPDATE_LOGIN    NUMBER

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.

 

regards,

admin


Oracle Applications style “set_who” for custom applications

April 16, 2013

 

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
ADD(
CREATED_BY    NUMBER    ,
CREATION_DATE    DATE    ,
LAST_UPDATE_DATE    DATE    ,
LAST_UPDATED_BY    NUMBER    
);

set_who procedure

PROCEDURE set_who IS
blk_name VARCHAR2(40);
curr_item VARCHAR2(40);
curr_mode VARCHAR2(40);
trx_date date := sysdate;
curr_user NUMBER;
BEGIN
	--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
 	--lines accordingly
 	curr_user := name_in(':PARAMETER.P_USER_ID');
 	
 	if curr_mode='CHANGED' then
 		COPY(curr_user,blk_name||'.LAST_UPDATED_BY');
 		COPY(to_char(trx_date,'dd/mm/yyyy HH24:MI:SS'),blk_name||'.LAST_UPDATE_DATE');
 	elsif curr_mode = 'INSERT' then
 		COPY(curr_user,blk_name||'.CREATED_BY');
 		COPY(to_char(trx_date,'dd/mm/yyyy HH24:MI:SS'),blk_name||'.CREATION_DATE');
 	end if;
 	
 	
--Mandatory columns with tables
--against which you will log
--the insert, update user details &amp; time
/*
CREATED_BY
CREATION_DATE
LAST_UPDATE_DATE
LAST_UPDATED_BY
*/ 	
 	
  
END;
 
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.
 
Enjoy!
 
Regards,
 
Admin

VB.net console application for Deleting old files from a particular folder

January 13, 2013

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

https://skydrive.live.com/redir?resid=68080371B15625F8!126&authkey=!AHiWHLBzE1x1YdU

The executable could be downloaded from following link

https://skydrive.live.com/redir?resid=68080371B15625F8!127&authkey=!AKmvC5fF61iSkiA

 

 

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

Imports System.Console
Imports System.IO
Imports System
Imports System.Collections.Generic
Imports System.Configuration
Imports System.Text
Imports System.Environment




Module Module1


    Sub Main()
        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)
            ' Console.WriteLine(strStartupArguments(intCount).ToLower)
            ' Console.WriteLine(strStartupArguments(intCount))
            Select Case strStartupArguments(intCount).ToLower
                Case &quot;-dfolder&quot;
                    ' fldrName = strStartupArguments(intCount).ToLower
                    fldrName = strStartupArguments(intCount + 1)
                    'Console.WriteLine(fldrName)
                Case &quot;-ftype&quot;
                    fileType = strStartupArguments(intCount + 1)
                    'Console.WriteLine(fileType)
                Case &quot;-ndays&quot;
                    nDays = strStartupArguments(intCount + 1)
            End Select
        Next intCount

        If ((fldrName Is Nothing) Or (fileType Is Nothing) Or (nDays = 0)) Then
            Console.WriteLine(&quot;No or not all arguments given, USAGE DeleteOldFiles.exe -dFolder &lt;folder name&gt; -fType &lt;*.extention&gt; -nDays &lt;N&gt; &quot;)
            Console.WriteLine(&quot;Example: DeleteOldFiles.exe -dFolder C:\myfolder -fType *.txt -nDays 100&quot;)
            Console.WriteLine(&quot;         C:\myfolder --is the target folder from files will be deleted&quot;)
            Console.WriteLine(&quot;         *.txt --tells the system what kind of files should be deleted &quot;)
            Console.WriteLine(&quot;         100 --defines file age in number of days&quot;)
            End
        End If

        Dim fileName = Date.Now.ToString(&quot;ddMMyyyy&quot;) &amp; &quot;.log&quot;
        Dim filePath = IO.Path.Combine(fldrName, fileName)
        Using sw As StreamWriter = New StreamWriter(filePath)
            sw.WriteLine(&quot;File Name&quot; + &quot;;&quot; + &quot;Creation Date&quot; + &quot;;&quot; + &quot;Deletion Time&quot;)
            Try
                ' For Each file As IO.FileInfo In New IO.DirectoryInfo(&quot;D:\Documents&quot;).GetFiles(&quot;*.pdf&quot;)
                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 &gt;= nDays Then
                            sw.WriteLine(file.Name + &quot;;&quot; + file.CreationTime + &quot;;&quot; + Now)
                            file.Delete()
                        End If
                    End If


                Next
            Catch ex As Exception
                Console.WriteLine(ex.ToString)
            End Try
        End Using


    End Sub
End Module

The default solution name is “DeleteOldFiles” hence when you open and build the solution, the .exe file name would be DeleteOldFiles.exe

Usage syntax

DeleteOldFiles.exe –dFolder <driverletter:\foldername> –fType *.extension –nDays <number of days>

eg: DeleteOldFiles.exe –dFolder C:\temp –fType *.tmp –nDays 3

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

regards,

admin


Windows 8, Metro applications do not open/work/respond

November 1, 2012

 

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.

Regards,

Admin


Oracle Application “Position Hierarchy SQL Query”

June 19, 2012

 

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

Step 1

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

CREATE TABLE XXHIERELEMENTS
(
  RNUM                NUMBER,
  POSITION_EFFECTIVE  VARCHAR2(4000 BYTE),
  REP_LEVEL           NUMBER,
  PERSON_ID           NUMBER(10),
  FULL_NAME           VARCHAR2(240 BYTE),
  PARENT_POSITION_ID  NUMBER(15),
  HIERARCHY_NAME      VARCHAR2(30 BYTE)
)

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;

   COMMIT;
END;

We hope this thread is useful for you

For Windows7bugs,

Admin