SQL & PL/SQL :: FIFO Method Calculation
Jun 11, 2010
I have three table for the stock calculations. The structure are like this
Product_master
product_id number,
product_name varchar2(30),
company_id number(3),
rate_per_unit number(14,4)
Purchase_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Sales_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Purchase_return_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
Sales_return_master
trans_date date
product_id number,
company_id number(3),
quantity number(14,4),
rate_per_unit number(14,4)
I need to find out the valuation on particular sales date at FIFO method.
View 22 Replies
ADVERTISEMENT
Jul 12, 2011
How can I calculate (by SQL) outstanding stock on a First In First Out basis per month?
Table A
Month Oustanding Stock Sales Expired Stock
January 200 0 0
February 100 100 0
March 100 0 0
April 50 150 0
June 50 0 100
July 50 0 0
Desired Results
Month Outstanding Stock
January 0
February 0
March 50
April 50
June 50
July 50
View 5 Replies
View Related
Apr 25, 2013
I need a query to divide the total consumption of an item into parts as it was stored in store on the basis of FIFO. The item that was stored first it will consumed first.
My sample data is given below.
DROP TABLE STORE_STOCK;
CREATE TABLE STORE_STOCK
(
no NUMBER(4),
vdate DATE,
code VARCHAR2(8),
clqty NUMBER(8,3),
dept_id NUMBER(4)
)
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
NOMONITORING;
[code]........
SQL>select * from store_stock
2 order by dept_id,vdate,no;
NO VDATE CODE CLQTY DEPT_ID
---------- --------- -------- ---------- ----------
359 21-JUL-12 012-042C 1050.1 19
2144 14-NOV-12 012-042C 990 19
1876 23-OCT-12 012-042C 1010 34
4006 12-MAR-13 012-043D 515.425 34
4086 15-MAR-13 012-043D 870 34
4495 13-APR-13 012-043D 498 34
6 rows selected.
SQL>select * from store_issue;
CODE IDATE DEPT_ID QTY
-------- --------- ---------- ----------
012-043D 24-APR-13 34 1650
012-042C 24-APR-13 19 990
Required Output will be
NO VDATE CODE CLQTY DEPT_ID ISS_QTY BAL_ISS
---------- --------- -------- ---------- ---------- ---------- ---------
359 21-JUL-12 012-042C 1050.1 19 990 0
2144 14-NOV-12 012-042C 990 19 0 0
1876 23-OCT-12 012-042C 1010 34 1010 640
4006 12-MAR-13 012-043D 515.425 34 515.425 124.575
4086 15-MAR-13 012-043D 870 34 124.575 0
4495 13-APR-13 012-043D 498 34 0 0
The total consumed against code '012-042C' is 990 for dept_id 19 and we have stored qty 1050.1 against goods receipt no 359 dated 12-jul-12. So have issued the whole qty against no 359 and balance is 0. For item code '012-043D' the total issuance is 1650 and we have issued it against 03 goods receipt nos.
View 5 Replies
View Related
Sep 30, 2010
select code,inv_date,inv_co_code,inv_co_name,inv_fnd_code,inv_fnd_name,inv_amount,inv_nofu
from retreport order by inv_co_code, inv_fnd_Code,inv_date
STAT INV_DATE CODE NV_CO_CODE INV_CO_NAME INV_FND_CODE INV_FND_NAME INV_AMOUNT INV_NOFU
----- ------ -------- ----------- ------------ -------------- ---------------------- ----------------------
I 28/06/2010 117 13 CAD 1 MCB 15104708.75 148375.7308
I 01/07/2010 13 CAD 1 MCB 2804.7464
I 30/08/2010 117 13 CAD 1 MCB 700000.00 6882.2380
I 30/08/2010 117 13 CAD 1 MCB 9500000.00 93401.8018
R 31/08/2010 117 13 CAD 1 MCB 39315.8646
R 08/09/2010 117 13 CAD 1 MCB 24515.9089
6 rows selected
STAT I = IN STOCK
STAT R = OUT STOCK
I need FIFO Query First in Fist out (INV_NOFU)
STAT CODE INV_CO_CODE INV_CO_NAME INV_FND_CODE INV_FND_NAME INV_AMOUNT INV_NOFU BALACNE
----- ------ ----------- ----------- ------------ -------------- ---------------------- ---------------------- ------------
I 117 13 CAD 1 MCB 15104708.75 148375.7308 148375.7308
R 117 13 CAD 1 MCB 39315.8646 109059.8662
R 117 13 CAD 1 MCB 24515.9089 84543.8773
I 13 CAD 1 MCB 2804.7464 87348.6237
I 117 13 CAD 1 MCB 700000.00 6882.2380 94230.8617
I 117 13 CAD 1 MCB 9500000.00 93401.8018 187632.6635
View 2 Replies
View Related
Jun 16, 2010
I want to adjust 2900 against certain amount from table on fifo basis.
DROP TABLE ABC
CREATE TABLE ABC(ID NUMBER PRIMARY KEY,AMT NUMBER);
INSERT INTO ABC VALUES(1,1000);
INSERT INTO ABC VALUES(2,2000);
INSERT INTO ABC VALUES(3,3000);
Can i do it using SQL. I know that it can be done using PL/SQL but i just wanted to confirm if its possible using SQL.
View 9 Replies
View Related
May 9, 2013
We have a requirement to update one column as given in the below mentioned pl/sql block.The challenge is to update the a large volume of data every day.Generally this kind of one to one update will takes long time (45 min approxmately) We need a solution that will be able to update 10 million records in few minutes.
begin
for i in (select t3.event_id, t1.header_id from
table_1 t1, table_2 t2, table_3 t3
where t1.header_id=t2.header_id and t2.entity_id = t3.entity_id)
loop
update table_1 set event_id = i.event_id
where header_id =i.header_id;
end loop;
commit;
end;
View 5 Replies
View Related
Mar 4, 2011
NOVALIDATE CONSTRAINT METHOD.
Below is the step i gone through:
1) CREATE TABLE mutu (id NUMBER, text VARCHAR2(20));
2) INSERT INTO mutu SELECT rownum , 'mutu' FROM dual CONNECT BY LEVEL <= 10000;
3) Insert Duplicate row.
INSERT INTO mutu VALUES (42, 'DUPLICATE');
4) COMMIT;
5)Create a NON-UNIQUE INDEX's CONSTRAINT as below.
ALTER TABLE mutu ADD CONSTRAINT mutu_pk PRIMARY KEY(id)
USING INDEX(CREATE INDEX mutu_pk ON mutu(id)) ENABLE NOVALIDATE;
6) Insert Duplicate row.
INSERT INTO mutu VALUES (42, 'DUPLICATE');
7) ERROR at line 1:
ORA-00001: unique constraint (SYS.ZIGGY_PK) violated
Why it shows the error. Even though I had created a NON-UNIQUE INDEX's Constraint only.
View 2 Replies
View Related
Oct 21, 2010
I have 2 tests Oracle instances (a 10g and a 11g) in which somebody added many users (almost 50k), every user having a TEST table with no rows in its schema.
I have to drop these users without recreating the instances, but DROP USER username CASCADE is taking almost 1 minute for each user. This will take me to almost a month.
View 2 Replies
View Related
Apr 3, 2008
i got a table and it had 5000 rows of data...ive deleted around 2000 to decrease the db size but i have no success. My harddrive is still showing the same size with no increase in mb.
I've looked at shrink etc methods but some are not compatible with 8i.
I take it the db is still reserving that those deleted rows thinking it may be used again which is the reason for no increase in space.
View 1 Replies
View Related
Aug 23, 2011
Question:
How to find out the status of the import job(how much work is done, ETA), i have started the job few days back it still running on the server. i am using import not datapump, because i got the dump which took with export. DB version is 11.2.0.2.0
for datapump i know i can use the following queries, but these are not giving any output l think these are only for datapump
select sid, serial#, sofar, totalwork from v$session_longops;
select sid, serial# from v$session s, dba_datapump_sessions d where s.saddr = d.saddr;
View 1 Replies
View Related
Sep 15, 2010
Which Oracle access method is the fastest way for Oracle to retrieve a single row?
a) Primary key access
b) Access via unique index
c)Table access by ROWID
d)Full table scan
View 2 Replies
View Related
Mar 16, 2012
i have attached the .csv file for gratuity calculation.
In the given criteria you can see if number of years 1 then he can get 1 salary if years 1.5 then 2 salary and so on also we need to take care about the leap year.
View 2 Replies
View Related
Sep 14, 2011
,i had given the sample data below
create table ex (sno number,ename varchar2(10),job_code char(4),sal number);
insert into ex values(101,'John','Java',21000,'IT');
insert into ex values(102,'Michel','BI',25000,'IT');
insert into ex values(103,'Johny','Java',30000,'IT');
[code]...
My expected output is attached in a text file
View 12 Replies
View Related
Jun 2, 2011
I have a table :
Product
A
B
C
D
and I was wondering if there is a quick method of populating it with calendar data so it would look like the following:
Product Year Month
A 2008 Jan
A 2008 Feb
A 2008 Mar
A 2008 Apr
A 2008 May
A 2008 Jun
A 2008 Jul
A 2008 Aug
A 2008 Sep
A 2008 Oct
A 2008 Nov
A 2008 Dec
A 2009 Jan
A 2009 Feb
Etc.
This would be done for all products for 4 years.
View 2 Replies
View Related
Feb 11, 2013
I have installed oracle 10g in D drive .
The instalation says it is succeessful, without asking the database name or the password .
I have followed the steps given in the attached file .
However it is not asking the databse name or password ( no configuration set up is appearing for the user to interact with the system ) .
So, after installation I can not give the username and password .
The tnsnames.ora file is given below
# tnsnames.ora Network Configuration File: D:DevSuiteHome_1
etworkadmin nsnames.ora
# Generated by Oracle configuration tools.
[Code]....
The files from where I installed is ds_windowx86 disk1 and disk 2.
View 26 Replies
View Related
May 9, 2012
I have a form of a user feedback with SMS Sending. which I gave to my all clients when ever my any client enter a feedback they press send SMS button so I got SMS if they press the button 2 times I got 2 same sms and if press the button 3 times I got 3 same sms and so on I want to restrict them and allow them to send only 2 sms of that feedback.any method on WHEN-BUTTON-PRESSED Trigger.
View 7 Replies
View Related
Feb 25, 2010
Is it possible to solve an equation with the newton-raphson method with sql?
I have an equation f(x)=1-a/x**2, f(x)'= 2a/x**3
and want to solve x for several a's
The newton-raphson methos uses the derivation as next approximation:
xn2 = xn1 - f(x)/f(x)'
The result should be like this:
create table temp_res (a integer, res number);
insert into temp_res values(1, 1);
insert into temp_res values(2, 1.41421288939285);
insert into temp_res values(3, 1.73205078513617);
insert into temp_res values(4, 1.99999999999617);
insert into temp_res values(5, 2.23606797658102);
insert into temp_res values(6, 2.4494897130809);
insert into temp_res values(7, 2.64575095945042);
insert into temp_res values(8, 2.82842712474403);
View 33 Replies
View Related
Jun 30, 2013
DB: 11.2.0.3 & OS: RHEL5 Easy Connect Naming method enhances the host naming method by allowing for a port and service specification. My question is does Oracle 11g support Host naming method?
View 4 Replies
View Related
Aug 29, 2013
Our Audit Company has given us a recommendation:"Old DB Link encrypted Passwords: The password of the Oracle databases links are encrypted using DES (password starts with 05). This encryption methord is known and users can decrypt the passwords using a simple SQL query. Please recreate the database links to use the new encryption method (password starts with 06)."What does it mean and how can we perform this recommendation?
View 2 Replies
View Related
Mar 16, 2010
I tried to convert rows to columns using the pivot method.But i am not able to do that.This is what i tried:
SQL> desc pt
Name Null? Type
----------------- -------- ------------
NAME VARCHAR2(10)
YEAR NUMBER(4)
VALUE NUMBER(4)
[code]....
As you can see from the above output i am not getting as expected.
Expected output:
YEAR JOHN JACK MARY
---------- ---------- ---------- ----------
1993 3000 1340 8700
1991 1000 1500 1250
1992 2000 1200 2323
View 7 Replies
View Related
Apr 19, 2010
I want to display Week No as the heading.So, I have these:
define week_no=number
column week_col new_value week_no
select to_number(to_char(sysdate, 'ww')) week_col from dual;
I have tested it and it is working as the way I expected: select 'to display week no as column' "Week &week_no" from dual;
The output:
Week 16
----------------------------
to display week no as column ...So how do I display Week 15, Week 17 etc based on the calculation of &week_no e.g. &week_no+1?
View 19 Replies
View Related
Jul 5, 2012
I have time fields that have been converted from a 12hr clock to a 24hr clock and these fields hold only the time...no date.
I need to be able to determine the difference between column A and B eg.
Column A: Column B:
11:00 14:00
I can remove the ':' by using the replace command, but as I need to run on a actual 24hr clock I am not sure how to tackle the calculation as it is not the full oracle datetime format.
View 8 Replies
View Related
Feb 24, 2011
Is it possible to calculate cumulatively in oracle sql queries, i.e. using the results of the last row for calculating the values in the current row?For example I want my query to return the following:
Month col1 _ col2 _ col3
jan 1 _ 100 _ 100 * 1
feb 2 _ 200 _ 100 * 2 + 200
mar 3 _ 300 _ 400 * 3 + 300
apr 4 _ 400 _ 1500 * 4 + 400
may 5 _ 500 _ 6400 * 5 + 500
In col3 above, for feb I want to use the result returned for jan ((100 * 1)*2+200), for mar I am using the result returned for feb((100 * 2 + 200) * 3 + 300) and so on.
i.e. I want to use the previous value of column3 to derive the current value of column3. Like using the LAG function but on the analytically derived column itself.
View 16 Replies
View Related
May 30, 2010
Check the following
SELECT FROM_TZ(CAST(TO_DATE('1999-12-01 11:00:00',
'YYYY-MM-DD HH:MI:SS') AS TIMESTAMP), 'gmt')
AT TIME ZONE '+05:30' "Time at Time zone"
FROM DUAL;
Time at Time zone
01-DEC-99 04.30.00.000000 PM +05:30
My requirement is that
I want to add 2 hours to DateTime i get here i.e. add two hours in result and display the resultant date and tine in time zone '+05:30'. I also want to check that the resultant time falls in business hours (9 am to 6 pm).
View 11 Replies
View Related
Mar 16, 2012
for making the query for following data,when we give start and end date then query need to calculate number of years.
if less than one year then return 0
if exact one year then return 1
if exact 1.5 years (18 months) then return 2
if exact 2 years (24 months) then also return 2
if exact 2.5 years (30 months) then return 3
if exact 3 years (36 months) then also return 3
if exact 3.5 years (42 months) then return 4
if exact 4 years (48 months) then also return 4
and so.
also we need to add leap year 1 day if exist in start and end date.
YearSalary
11
1.52
22
2.53
33
3.54
44
4.55
55
[code]....
View 15 Replies
View Related
Feb 5, 2011
I am developing a form where I need to add Numbers.In fact we have a bag of Cones that contain 24 cones.In normal calculation when I add numbers for example
5.24 Plus 5.24 it will give the result 10.48
I Need the appropriate method to calculate if I add these two numbers it should give the result 5.24 Plus 5.24 the result should be 12
View 7 Replies
View Related
Oct 20, 2011
Currently I am working on payroll system where I have to calculate employees working hours/late coming hours/early going hours against its roster which is defined in the beginning of every month/week.
In roster form user define shift of every employee like
Code Name Shift
7 Saad Nafees A - 09:00 17:00
492 Muhammad Nasir Shahzad B - 17:00 01:00
243 Muhammad Tahir C - 01:00 09:00
Roster table structure
code varchar2
name varchar2
Shift date
Remarks varchar2
shift table structure
code varchar2
timein date
timeout date
latetime date
Hdaytime date
Oracle stores both date and time information in date data type, suppose today user change shift timings from 17:00 to 17:30 or user change timein/timeout in attendance form then oracle will store current date with user define timings.
Now this is the main problem which I am facing because whenever you calculate difference between timein and timeout or compare with its roster then output comes wrong because oracle returns total no of hours whenever you minus two dates.
View 11 Replies
View Related
Jun 10, 2011
T1 = 06-Jun-2011 4:00PM
T2 = 10-Jun-2011 11:AM
Calculation is Required.
If at the end of Day Hours of T1 > 6 hours then it should calculate 1 Day.
same with T2. hours of T2(from begining of Day till end Time (11AM), it should again caculate if >6 hours then 1 Day.
remaining will be date calculation. this is how T2 - T1 should give us 5 Days.
Detail
06-Jun-2011 4:00PM to 06-Jun-2011 12:00AM (8 hours > 6 = 1 Day)
06-Jun-2011 12:00AM to 07-Jun-2011 12:00AM = (1 Day)
07-Jun-2011 12:00AM to 08-Jun-2011 12:00AM = (1 Day)
08-Jun-2011 12:00AM to 09-Jun-2011 12:00AM = (1 Day)
09-Jun-2011 12:00AM to 10-Jun-2011 12:00AM = (1 Day)
10-Jun-2011 12:00AM to 10-Jun-2011 11:00AM (11 hrs > 6 = 1 Day)
this is how 5Days calculation is required.
View 1 Replies
View Related
May 29, 2012
We want to calculate Annual leave the scenerio is as follows
1. Employee service more the one year
2. if joining date is 07-04-2008 than on 07-04-2009 completed one year and on 07-04-2010 completed two years and so on. day and month of date must be the same.
so we need YEAR*14. e.g. if 1 year completed then 1*14 if two years completed then 2*14 and so on. by this we will get the opening of Annual leaves.
We have another table where we entered the leave day by day in whole year, if he avail 10 leaves in first year then 4 will be remaining and we can say it is the closing of first year and opening of second year. so on second year he will entitled 14 more leaves so 14 + 4 = 18 should be opening of next year.In year Caclulation Day and Month must be the same for example 07 day, 04 month for every year. 14 Annual leaves are fix for each year
following are columns for output.
empid, ename, date of joining, sysdate, leave opening, leave avail, closing balance,
1, jone, 29-05-2009, 29-05-2012
2, herry,29-05-2008, 29-05-2012
3, bell, 29-05-2006, 29-05-2012
between two dates (date of joining and system date)
View 1 Replies
View Related
Aug 31, 2012
V: 10.2.0.3
I want to calculate on column-layer of a sql-query:
select a "column1", "column1" + 1
from ...but it does not work...
View 9 Replies
View Related
