Forms :: Calculate Difference Time Between Two Fields
Mar 24, 2011
I have two different date in my payroll software,
1-Shift_date shift date *used to contain shift timings
2-Attendace_datedate *used to contain employee IN timings
As you all know that shift is a setup form, where user input data once in the starting of software so the shift_date can be "01/jan/2011 16:00 pm" but attendance loads daily and attendance field data can be in this form "24/mar/2011 16:15 pm"..Now I want to calculate difference time between these two fields therefore I used this statement
SQL> Select to_char(attendance_date,'HH24:MI') to_char(shift_date,'HH24:MI') from dual;
but it is showing error: ORA-01722: invalid number...I used hours/minutes format mask in my query because you can see there is a difference of dates between these fields and it will be increase in the coming future and I need late hours and minutes.
I want to calculate time difference b/w two time clocks, just like we calculate the date difference and answer is in days, In the same way i like to have answer in hrs,min and ss.
during application migration, i got one table from MS Access, and have situation where two events are splited into 4 columns (start: date1 time1 and stop: dat2 and time2). How to properly calculate duration between these two events, and show it in format: hh:mi ?
I need to subtract multiple numbers to get the difference between each numbers, the amount of numbers to subtract between each others can vary between 2 and 10.
setting up the query/correcting the syntax below so that it calculates the 'number of days difference' between whatever the 'Biggest Date' field value is and whatever the 'current date' is using the 'sysdate'. So far, I've only managed to get the query to calculate the number of days difference (days past due) between the 'need date' and 'estimated delivery date'.
CODESELECT To_Date(need_date, 'YYYYMMDD') Need_Dt, To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Biggest_Date, To_Date(need_date, 'YYYYMMDD') - To_Date(Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END, 'YYYYMMDD') Date_Diff
FROM tableT
WHERE need_date <= (Case when estimated_delivery > ' ' THEN estimated_delivery ELSE need_date END)
create or replace function getDate(p_joing_date Date,p_sysdate) Return Date; IS v_compltd_mnths; BEGIN SELECT into v_compltd_mnths MONTHS_BETWEEN(TO_DATE('sysdate','MM-DD-YYYY'), TO_DATE('joing_date','MM-DD-YYYY') ) "Months"FROM DUAL; return v_compltd_mnths; END;
We have a table which contains the outage data as below,
TAG CI_NAME OUTAGE_START OUTAGE_END IM10366 FD0004 06-jul-2010 10:00:00 10-jul-2010 10:00:00
so from the above record, the outage is from 6th - 10th July. So for the given TAG & CI_NAME, we have to calculate the outage for day. So the above record should be split as below
i want to find out the last refresh elasped time for materialized view. i do not see last refresh elapsed time in data dictionary. i see only last refresh date in data dictionary.
how to find the last refresh elapsed time for materialized view.
I have a table with a date time column which actually stores the data as and when it is inserted. I need to calculate the time the machine has worked hourly wise. For example the table contains records from 13:00 to 13:15 and 13:45 to 14:00 i should get 30 mins as working time..
in the above, for job 1 ,on 19/08/2010 the time difference should be calculated as
01:23:12-00:56:00 and the difference should be in minutes.
for end_time. i can't take the substring as the length of the date varies for 19 and 7. In case of start date, the time has to be in format, hh:mm:ss, to calculate the difference.
I created a new form for Oracle Apps, At first when I ran the form from the application all the fields backgrounds were black, so I changed the background in the property palette to white and foreground to black.
Now it shows fine but when I close this form and open another those fields are now blacked out. What should I do, I know the problem comes from the new form.
How To Calculate Average in Forms 6i for example a summary column named (Amount = 5000) and i want to calculate 15% average of this amount i want to calculate it like summary column .
how do w calculate sum in a form? for example in datablock sale, I have few rows of data, i want to calculate the total of product purchased (column totitem) and , sum of the customer have to pay. (totalprice). how to do the calculation in a program unit?
I have a horrible problem with EBS (actually, all problems with EBS are horrible) and I think I am stuck because of my ignorance of Forms. if I use terms that are not correct in the Forms world. The form consists of a number of named "blocks" and each block consists of a number of named "fields", not all of which are visible. I need to find the source of the data values in one of these fields. I have searched every table for a column of that name, also all the views and stored PL/SQL that I think might be relevant, but I can no find no mention of a column or variable with the same name as the field. The name does not get a hit in the online EBS tech ref manual, and only two ancient and irrelevant hits in MOS.
My question is: What are the possible sources of data for a field in a form? Have I missed any?
Is there any way to see items in the block having null in them? I want to figure out null field before inserting the data in the database table. At the moment I am having an error ORA 01400 which means that a NULL cannot be inserted into NOT NULL column. So, I want to create a procedure which will fire before insert trigger of the block and it will show me the names of items having null it them.
Procedure check_data(block_name in varchar2) First_item varchar(20); Current_item varchar(20); Last_item varchar(20); Begin
[Code]..
The problem is that I don't know the function to get the value inserted by user in the field so that I can compare it in IF condition.
I am working on Oracle Apps 11i. Need Forms Personalization.
Client requirement: Selected user should only see Office Phone Number for all the people in the organization when he enters the Phones form.
Means, the client is having around 10 Lookup values for Phones (Like Mobile (Office), Mobile (Personal), Office (fax), Office No. etc). Now, they want to give access to admin, only to update the Phone numbers related to Office. So, when the admin logs-in to the phone form of a employee, that admin should not be able to see other phone numbers of that Employee.
I am using Forms 6i in Application. In Form, After i enter the data and saved then,if i retrieve the data by using Ctrl+F11, all the fields of data block turns to be disabled (insert not allowed, update not allowed) of course i am getting the data.If i close the application, and open freshly it works good (insert allowed, update allowed).
i have a emp block with multiple rows in which col1,col2,col3,col4,col5,col6 are the columns...In emp block col1,col2,col3 are the primary key fields.. i need to validate the primary key columns such that it should not allow duplicates values in the primary key col's with out checking the database values with out using enforce primary key property
Is there any possibility of creating a selection interface similar to our report or forms wizard where user can select single fields or multiple fields together and when he clicks on the arrow those fields data to be inserted into another table.Please refer attachment.
--this is the main table create table batch_item (batch_no varchar2(12),item_batch varchar2(12),total_batch_qty number);
insert into batch_item ('0001','a',300); insert into batch_item ('0002','b',200); insert into batch_item ('0003','a',102);
--after inserting the data i should be able to move this data to corresponding batches by manually selection and pushing them to a batch.create table ct_item (item varchar2(12),item_nm varchar2(20),item_qty number);
insert into om_item values ('a','alpha',2); insert into om_item values ('b','beta',3); insert into om_item values ('c','gama',4);
--left side is om_item and right will have batch items where batch qty will be accumulated upon choose arrows.
I have an Oracle Form 6i. There are two blocks. One is a database block called CUSTOMER and the other is a non-database block called CONTROL.
In the PRE-INSERT trigger of the database block, values from the non-database table block are passed to the database table block. When I pass values I use the :BLOCK_NAME.field_name eg. :CUSTOMER.scale_code := :CONTROL.scale.
In this form the values passed to the database block from the non-database block in the PRE-INSERT trigger do not use the block name e.g. :-
This application used to work fine for months, last week when writing the values in the PRE-INSERT trigger, just the warehouse field had a value. The remaining fields after the warehouse did not pass any values, although it was verified that values would have been present in the non-database block. The date_captured field should have at least had the system date. The last insert into dd_audit was successful.
I have done numerous tests on our test database and could not replicate the problem. Would passing the values from the non-database block to the database block without the :BLOCK_name preceding the field name cause this problem.
