Query to Get Leave Balance for different Leaves

Query to Get Leave Balance for different Leaves
SELECT   a.employee_number staff_no,
a.full_name staff_name,
a.start_date date_of_join,
d.name Position,
c.parent_org_name department,
c.child_org_name section,
(SELECT   NVL (SUM (paa.absence_days), 0)
FROM   per_absence_attendances paa,
per_absence_attendance_types pat
WHERE   1 = 1 AND paa.person_id = a.person_id
AND paa.absence_attendance_type_id =
AND pat.absence_attendance_type_id IN (63, 79, 80)
AND paa.date_start IS NOT NULL
AND TRUNC (paa.date_start) BETWEEN :p_from AND :p_to)
— xxhy_leave_bal_fun (b.assignment_id) annual_leave_balance,
get_net_entitlement (b.assignment_id, 61, :p_to)
(SELECT   NVL (SUM (paa.absence_days), 0)
FROM   per_absence_attendances paa,
per_absence_attendance_types pat
WHERE   1 = 1 AND paa.person_id = a.person_id
AND paa.absence_attendance_type_id =
AND pat.absence_attendance_type_id = 1081
AND paa.date_start IS NOT NULL
AND TRUNC (paa.date_start) BETWEEN :p_from AND :p_to)
get_net_entitlement (b.assignment_id, 1061, :p_to)
(SELECT   NVL (SUM (paa.absence_days), 0)
FROM   per_absence_attendances paa,
per_absence_attendance_types pat
WHERE   1 = 1 AND paa.person_id = a.person_id
AND paa.absence_attendance_type_id =
AND pat.absence_attendance_type_id = 77
AND paa.date_start IS NOT NULL
AND TRUNC (paa.date_start) BETWEEN :p_from AND :p_to)
(SELECT   NVL (SUM (paa.absence_days), 0)
FROM   per_absence_attendances paa,
per_absence_attendance_types pat
WHERE   1 = 1 AND paa.person_id = a.person_id
AND paa.absence_attendance_type_id =
AND pat.absence_attendance_type_id = 67
AND paa.date_start IS NOT NULL
AND TRUNC (paa.date_start) BETWEEN :p_from AND :p_to)
Emergency_leave_util_2016,(SELECT   NVL (SUM (paa.absence_days),
FROM   per_absence_attendances paa,
per_absence_attendance_types pat
WHERE   1 = 1 AND paa.person_id = a.person_id
AND paa.absence_attendance_type_id =
AND pat.absence_attendance_type_id not in ( 67,77,63, 79,
AND paa.date_start IS NOT NULL
AND TRUNC (paa.date_start) BETWEEN :p_from AND :p_to)
— xxhy_leave_bal_compen_fun (b.assignment_id) compen_leave_balance
FROM   per_all_people_f a,
per_all_assignments_f b,
xxhw_organization_hierarchy_v1 c,
per_positions d
WHERE       1 = 1
AND a.person_id = b.person_id               — and a.person_id=1102
AND b.organization_id = c.child_org_id
AND TRUNC (SYSDATE) BETWEEN a.effective_start_date
AND  a.effective_end_date
AND TRUNC (SYSDATE) BETWEEN b.effective_start_date
AND  b.effective_end_date
AND TRUNC (SYSDATE) BETWEEN c.effective_date_from
AND  c.effective_date_to
AND a.current_employee_flag = ‘Y’
–AND a.employee_number LIKE ‘004014’
AND b.assignment_type = ‘E’
AND b.position_id(+) = d.position_id
–and c.parent_org_name=’Telecom unit_DT’

Error while mounting NTFS file system as read write

Below is the error which comes when trying to mount NTFS file system with read and write access

The disk contains an unclean file system (0, 0).
Metadata kept in Windows cache, refused to mount.
Falling back to read-only mount because the NTFS partition is in an
unsafe state. Please resume and shutdown Windows fully (no hibernation
or fast restarting.)

After searching I got what to do

Run the below command

$ sudo ntfsfix /dev/sda8

Now mount the file system read and write access by below command

$ sudo mount -o rw /dev/sda8 /media/user


Code for Making a field or Response box mandatory in Workflow

Below is the code that will check the workflow for mandatory of response field.

Here the p_funcmode = ‘VALIDATE’ sets in validate mode and after handling the validation the we can set the appropriate message in the this line

x_resultout := ‘ERROR:You Must the Reason’;


if( p_funcmode = ‘VALIDATE’) then
l_nid := wf_engine.context_nid;
v_result :=wf_notification.getattrtext (l_nid, ‘APPROVE_COMMENTS’);
if(v_result is null)
x_resultout := ‘ERROR:You Must the Reason’;
end if;
end if;

Oracle HRMS Payroll -Retry,Reverse and Rollback a Payroll process

Marking and Retrying Payroll Runs and Other Processes

To mark a small payroll process for retry

    • In the Payroll Processes window, select the process to retry and check the Retry box. This effectively marks every employee assignment included in the process for reprocessing when you retry it.

To mark a large payroll process for retry ->

  1. In the Submit Requests window, select Mark Payroll Run for Retry in the Name field if you are retrying a payroll run. Otherwise select Mark Process for Retry. These processes have the same parameters, Payroll Process and Assignment Set.
  2. Select the process you are marking for retry.
  3. Leave the Assignment Set field blank.
  4. Choose Submit.

To mark an individual employee assignment for retry

    • In the Assignment Processes window, select the assignment to retry and check the Retry box. Then when you retry the process, only these assignments are reprocessed. This is a convenient approach when only a few assignments require reprocessing.

Correcting Run Results Reversals

You reverse pay runs in the Reverse Payroll Run window for the employee assignment.

To reverse a payroll run or QuickPay for an assignment

  1. Select the run you want to reverse.

All the payroll runs for the assignment are displayed when you open the window.

  1. Select the Reverse check box for the run you want to reverse.

The consolidation set for the run automatically displays as the consolidation set for the reversal.

  1. Select a different consolidation set if you want to process the reversal in a different set to the original run set.

After running the payroll and if post run processing has already occurred we cannot correct the payroll by retry, even in case of roll back we need to roll back the complete Payroll for correcting an entry for one or two assignment.
In these cases we can use Payroll Reversal. In short Payroll Reversal helps to correct the run result for an single assignment

To Reverse the Payroll run for an assignment
(R) US HRMS Manager
(N) Fast Path > Reverse Payroll Run
Select the Assignment that you need to reverse from the LOV, Tick the Reverse Check Box for the Payroll that we need to reverse and save the work. Consolidation set for the run will be automatically displayed for the Reversal
Now the Run Result is been reversed to the Value before the payroll Run. Now you can modify the Entry and start processing your payroll again.

Rolling Back Payroll Runs and Other Processes

You can roll back a process in one of several ways depending on the extent of the rollback.

If you want to … Then refer to…
Roll back processes for a few individuals Rolling Back a Process from the Payroll Processes Window
Roll back processes for many employees (defined as an assignment set) Rolling Back a Process from the Submit Requests Window
Roll back an assignment process for an individual employee Rolling Back a Process from the Assignment Processes Window

Once the Payroll /Quick Pay process is completed if there is some issue or if some correction need to be made. We can either

  • Retry
  • Reverse
  • Rollback

What Rollback does?

In simple word to explain Rollback process reset the Run Result and the Latest balance back to the value before running the Payroll Process.

Rollback process has to be run in the same reverse sequence of the Payroll Run. for eg. for processing the payroll, we run the following process in sequence Payroll run, Prepayment, costing, Transfer to GL. Similarly for the Rollback we need to run in the reverse sequence Rollback Transfer to GL, Rollback Costing, Rollback Prepayment, Rollback Payroll Run.

Note: if the costing details are imported into GL then we cannot rollback the process.So it is always advisable to run the Transfer to GL process after all validation and checking are done and run this process when we feel no more modification are needed

List of Process type that can be rolled back in PF K RUP1

  • Advance Pay Element
  • Balance Adjustment
  • Bank or Post office Payment
  • Batch element entry
  • cheque writer
  • Costing
  • Costing of Payment
  • Estimate costing
  • ext/Manual Payment
  • Magnetic report
  • Magnetic Transfer
  • Pre-Payments
  • Purge
  • QuickPay Pre-Payment
  • Quick Pay run
  • Report Generator
  • Retro Costing
  • Retro Pay
  • Retro Pay by Action
  • Retro Pay by element
  • Reversal
  • Run
  • Transfer to GL
  • Void Payment

Rolling Back a Process from the Payroll Processes Window

Use the Payroll Processes window when you want to roll back a process for a few individuals.

To roll back a process from the Payroll Processes window:

  1. Select the process record.
  2. Select Delete Record from the Edit menu.

Rolling Back a Process from the Submit Requests Window

Use the Submit Requests window when you want to roll back a process for many employees. If you do not want to roll back the process for all employees originally processed, you must create an assignment set. See: Creating an Assignment Set.

To roll back a process from the Submit Requests window:

  1. In the Name field, do one of the following:
      • Select Rollback to roll back all processes except a payroll run.
      • Select Rollback Run to roll back a payroll run.
      • Mexico only: Select Rollback MX Payroll Processes.
  1. If the Parameters box does not appear, click in the Parameters field.
  2. Select the process to roll back. The list for Rollback displays processing dates with the corresponding process types, payrolls, and consolidation sets. The list for Rollback Run displays process run dates with the corresponding period dates and payrolls.
  3. To roll back processing for an assignment set, select the set name.
  4. Choose Submit.

Rolling Back a Process from the Assignment Processes Window

Use the Assignment Processes window when you want to roll back a process for an individual employee assignment.

To roll back an assignment process:

  1. Select the record for the assignment process.
  2. Choose Delete Record from the Edit menu.

Source of the Information(Copied)

SQL plus executing a sql file in Corresponding Path


I had a problem to run the script file in the server.

After then I have done some search and then I got how to run.

So first define the path of the file located in a variable. Im taking as dir

now execute the command as shown in screenshot

define dir=”E:\prod\prodappl\per\11.5.0\sql”


Query has exceeded 200 rows. Potentially more rows exists, please restrict your query.


This is the expected behavior. The application throws the warning message ‘Query has exceeded 200 rows. Potentially more rows exist, please restrict your query.’ , when the process crosses the 200 row limit which is being set in profile

FND: View Object Max Fetch Size.
The profile FND: View Object Max Fetch Size can be changed to a higher value.  However, as per Oracle Applications guidelines increasing the value can have a performance impact.

1. Go into the responsibility: System Administrator (or equivalent).
2. Navigate to Profiles > Query the profile FND: View Object Max Fetch Size at Site, Application (Oracle Configurator)
and User Level
3.Change the value from 200 to a higher value (i.e. 250, 300 etc..).
4. Log out of the Application and back in.
5. Please retest the issue.
6. If the issue is resolved, please migrate the solution as appropriate to other environments.

Also you can check the profile option that has been already set using the below query


select substr(e.profile_option_name,1,25) Profile,decode(a.level_id,10001,’Site’,10002,’Application’,10003,’Resp’,10004,’User’) L,
decode(a.level_id,10001,’Site’,10002,c.application_short_name,10003,b.responsibility_name,10004,d.user_name) LValue,nvl(a.profile_option_value,’Is Null’) Value
from fnd_profile_option_values a, fnd_responsibility_tl b, fnd_application c,fnd_user d, fnd_profile_options e
where e.profile_option_name in (‘VO_MAX_FETCH_SIZE’)
and e.profile_option_id = a.profile_option_id
and a.level_value = b.responsibility_id (+)
and a.level_value = c.application_id (+)
and a.level_value = d.user_id (+);


If you feel that due to personalization there is a problem you can disable personalization through the below profile option

Disable Self-Service Personal

Set the below profile option to “Yes” and then check.

Invoices was not created from Sales order(Autoimport and AutoInvoices)

We are having a custom process from which Order numbers will be generated and then automatically Invoices will be created through AutoInvoice Process

Now the Invoices were not created for 1 week of records and again the invoices get started to get created.

After that for 1 week we tried to create Auto Invoice but the records were not taken as it has been already processed.

So we have taken the selected records from ra_interface_lines_all table and the we applied the below script

UPDATE ra_interface_lines_all
SET request_id = NULL,
customer_trx_id = NULL,
request_id = NULL,
link_to_line_id = NULL
WHERE interface_line_id IN
(SELECT interface_line_id
FROM ra_interface_lines_all
WHERE     trx_date >= TO_DATE (’01-SEP-2016′, ‘DD-MON-YYYY’)
AND interface_status IS NULL);

So this will remove the processed flags of the receivable interface data and show it as a fresh one so the AutoImport or AutoInvoice Program will again fetch the same records.


How To: Enable High Quality Audio in Linux

We can handle 96KHz 24-bit audio – but by default pulseaudio and alsa are configured for 44.1KHz 16-bit audio. At the 44.1/16-bit settings everything sounds great, but I figure if the quality can go higher and I’m not fussy about a bit of extra CPU usage then I may as well bump the quality settings up a bit to take full advantage. Here’s how to do it:

1 – Check current settings

To see what your current settings are, you can either run the following for the full details:

Or to cut to the sample rates directly use:

Depending on how many sound devices are connected you should see something like this (in my example I have an Intel HDA internal soundcard as the first sink, and HDMI audio out as the second sink):

You might want to play an mp3 at this point and then look at the pulseaudio cpu usage via the top command so you can compare the current and HQ CPU usage as it might be a factor for you (i.e. depending on the spec of your PC, you might not want to sacrifice 10% or more CPU usage for higher quality audio – but seeing as you’re here and reading this I’ll assume that you are!). As a baseline, with the default settings pulseaudio uses around 2-3% CPU to play an mp3 on my Intel i7.

2 – Modify for high quality

Pulseaudio’s global settings are stored in /etc/pulse/daemon.conf, so edit that with your editor of choice and look for the following lines (the ‘resample-method’ line is above the default-sample-* lines btw – that is, they’re not all together as below):

These are the defaults, and are currently commented out (you can use either ; or # to indicate a comment). Uncomment each line and modify it to the below (or even better – just add an uncommented line below each one so you can still see the default values), so you end up with:

The Arch Audiophile Playback wiki recommends medium quality for resampling, you might want to try src-sinc-best-quality if you’re going for broke. A description of the libsamplerate resampling methods can be found here if you’re interested: http://www.mega-nerd.com/SRC/api_misc.html.

I get about 10% CPU usage on the pulseaudio daemon with 96KHz @ 24-bit audio with medium quality resampling as above, whilst using src-sinc-best-quality results in a rather significant 24% CPU usage just to play an mp3! ( It’s like having a Pentium 100MHz all over again!😉 ) The ‘s24le’ means 24-bit samples, little-endian order, btw.

3 – Restart pulseaudio and check the settings

Now that’s done we need to restart the pulseaudio daemon (don’t run these as sudo – pulseaudio runs per user so just execute as normal!):

Once that’s done, you should be able to check the settings have taken by issuing:

It’s jumped up to 32-bit samples in this case, but that’s fine. Check the audio still works, and maybe have a look at the CPU usage when playing an mp3 via top again if you want to see how much this is affecting the CPU usage on your machine.

FiiO E17 Amp Check

Now that we’ve set higher quality default settings, if you’ve got some suitable hardware for them like the above-mentioned amp, plug that bad-boy in, switch it on – and it should validate that it’s getting the higher sample-rate and sample-depth:

Fiio E17 at 96KHz 24-bit via pulseaudio via USB (i.e. it’s acting as a sound card). The ’40’ value is just the current volume setting.

Sound Quality Differences

So – does changing these audio setting make a definite, perceivable difference to the audio quality? The short answer is yes!

How much it changes the audio quality is going to depend on your hardware (amp, headphones etc.) as well as the actual audio file(s) you’re playing on it. I don’t have any 24-bit audio to try it out on, but I rip everything at the highest possible quality VBR mp3s using lame – so I’ve been testing on the first track of Alt-J’s album (intro) with some Sennheiser HD650’s – and my honest answer? I’d go as far as to say it’s a strong yes (html joke ;-)). The high-frequency elements in particular seem a little brighter with the 96KHz 24-bit sampling, at least when run through the FiiO E17. Now, this could be placebo – that is, you expect something to sound different/react in a particular way so you look for it and (internally, subconciously) try to convince yourself of it. But I’ve honestly swapped back and forth and listed to the same track a number of times during the writing of this article and I genuinely believe it’s sounding better with the HQ settings.

Unfortunately, the schoolboy error I’ve made here is that I’ve changed two (if not three!) things at once: I’ve enabled the higher sampling-rate and sample-size/depth, but I’ve also modified the resampling algorithm to one of higher quality at the same time – so figuring out what elements of the audio sound better because of the audio settings and which are from the resampling-method (because I’m not playing native 96KHz @ 24-bit audio files to test with) can’t really be done. To get any real results I’d need to strip back to default settings and modify each in turn, doing listening tests with notes as I go, which frankly isn’t all that high up there on my priority list of things to do because…


…I’m delighted with the audio quality after these changes! It really does sound great =D

Ideally, I’d prefer to have things set to 44.1KHz on each audio device EXCEPT the Fiio – that way, when I’m playing games there’s minimal CPU used on the audio side (which isn’t really going to benefit that much from upsampling and such, I’d imagine) and then have the HQ settings for the FiiO only so that when I plug it to listen to music I get the HQ audiophile kick. However, my understanding is that I’d need to go into the alsa side of things to configure device specific settings – and again, it’s not really a high priority at the moment.

I’d love to hear what difference (or lack thereof!) any of you find when changing over to HQ audio (asides from the bump in CPU usage) – and if you run or have played with different samplerate/re-sample settings for diff devices I’d be especially interested! Cheers!

AP: Document Sequencing for Bank wise Check Payments

Oracle E-Business Suite R12 Pakistan's Weblog

There are scenarios or requirement where the payment voucher number should be generated Bank wise like payments made from HBL should have its own sequence and payment made from MCB should have its own. You can also narrow down the sequencing to Check Books.

Following is the levels of the Payment and its Document Categories:

  • All Payment (Check Pay: It is a seeded or default category)
  • Bank Account (this needs to be defined)
  • Check Book (this also needs to be defined)

I am not referring to other types of payment like EFT or Wire Transfer cuz frankly I haven’t seen any organization in Pakistan using these mode of payments, I am not denying that there aren’t but I haven’t seen and in turn haven’t worked on it. Although I worked with modes like Netting Payment and Refunds😀

The process is very simple. You just have to be familiar with Document Sequencing. For…

View original post 400 more words

Change Look And Feel and Colors Of Oracle Applications 11i and R12

There are two system profile options that can be used to change look and feel.

  1. Java Look and Feel.
    Java Look and Feel can have two values: GENERIC or ORACLE.
    If set to GENERIC, then set Java Color Scheme to blank.
    Which will leave look and feel to generic.
    The Generic look and feel adheres to the native interface and color scheme of the current operating system.
    If set to ORACLE (or if the value is left blank), then the profile option ‘Java Color Scheme’ can be used to personalize the colors of your screen.
  2. Java Color Scheme.
    If ‘Java Look and Feel’ is set to ORACLE (or if the value is left blank), then ‘Java Color Scheme’ can be set to the following values:

    • Blaf        (in R12: Swan)
    • Blue
    • Khaki
    • Olive
    • Purple
    • Red
    • Teal
    • Titanium

Do the following:

  1. Login to Oracle Applications as the System Administator responsibility.
  2. Navigate to Profile > System.
  3. Ensure that the Site display is checked.
  4. Query up ‘Java Color Scheme’ for the profile.
  5. Select the appropriate color under the Site column.
  6. Save the selection.
  7. Sign off and back on (or change responsibility) for your new color scheme to take effect.

Be aware that the ‘Java Color Scheme’ profile has no effect if ‘Java Look and Feel’ is set to GENERIC.