i want to get balanced amount based on max(suid ).
my query is
create or replace procedure prc_stk_upd_pur(v_comp_suid in i_purchase_h.pih_company_suid%type, v_area_suid in i_purchase_h.pih_area_suid%type, v_invoice_dt in i_purchase_h.pih_invoice_date%type, v_pih_suid in i_purchase_h.pih_suid%type, v_type in i_purchase_h.pih_type%type) is [code]......
my output is SEFF_SUIDSEFF_INV_DATESEFF_LOT_SUIDSEFF_LOC_SUIDSEFF_TRAN_SUIDSEFF_TRAN_TYPESEFF_COMP_SUIDSEFF_AREA_SUIDSEFF_STOCK_INSEFF_STOCK_OUTSEFF_CURRENT_STOCKCREATE_USERCREATE_TIMESEFF_RATE_USDSEFF_RATE_LCURNSEFF_PID_SUID 1121411/29/20121317151346LP11115720.00020.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14107665.30601382 2121511/29/20121317151346LP11115755.00055.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14097665.30001383 3121611/29/20121317151346LP11115730.00030.000SYSTEM29-NOV-12 06.53.45.000000 PM0.440729.08401384
my output should come SEFF_SUIDSEFF_INV_DATESEFF_LOT_SUIDSEFF_LOC_SUIDSEFF_TRAN_SUIDSEFF_TRAN_TYPESEFF_COMP_SUIDSEFF_AREA_SUIDSEFF_STOCK_INSEFF_STOCK_OUTSEFF_CURRENT_STOCKCREATE_USERCREATE_TIMESEFF_RATE_USDSEFF_RATE_LCURNSEFF_PID_SUID 1121411/29/20121317151346LP11115720.00020.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14107665.30601382 2121511/29/20121317151346LP11115755.00075.000SYSTEM29-NOV-12 06.53.45.000000 PM116.14097665.30001383 3121611/29/20121317151346LP11115730.000105.000SYSTEM29-NOV-12 06.53.45.000000 PM0.440729.08401384
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.
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.
I want to create a Stock Movement Report of a Health Management System in Oracle Report Builder 6i, as per following columns.
Sr. No Medicine Name Entry Date Opening Stock Stock In Stock Out Stock on-Hand 1 Mefnac DS 14-Aug-2012 50 450 50 450 15-Aug-2012 450 0 100 350 16-Aug-2012 350 50 400 500
Stock-In would be an aggregate sum of purchased quantity against particular medicine in a specific date. Stock-Out would be an aggregate sum of issued quantity against particular medicne in a specific date.
i've a problem in using store procedure. My code is to get postcode id when i pass a postcode. First it will check the postcode that i pass if already exist it will get postcode id but if not it will insert new postcode and get a new postcode id created then pass into ASP system. When i try run this stock procedure i got error as below :-
SQL> exec INSERT_PCODE_GMDS BEGIN INSERT_PCODE_GMDS; END;
* ERROR at line 1: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'INSERT_PCODE_GMDS' ORA-06550: line 1, column 7: PL/SQL: Statement ignored
( Postcode1 IN varchar2, citiID IN Number, county_ID IN number, city_name IN varchar2, sub_cityID IN number, pcode OUT number ) [code].......
in ASP to pass and get back the values i used code as below. but i think the problems occurs in my stock procedure
i want to create as procedure when i issue in issuance table then update + stock in stock table. and when i recieve in recieve table then minus- stock in stock table.
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.
I am trying to create a function that when called will add the salary and commission a certain way to return an employee's annual salary.Here's my code
create or replace function Get_Annual_Comp (Sal in number, Commission in number) return number as [code]...
When I run the query, I get the proper rows return; however, my function does no calculation. If I input random numbers, I get the proper value returned. What I want is for my function to return the salary and commission of the employee specified in my select's where clause to be calculated as an annual salary.
I am using the below sql query to calculate working hours. The problem which i am facing is that query is taking lot of time to calculate the working hours. reduce the execution time of this query or if there is any other way to calculate working hours
The following query take 63.499 sec
SELECT sql_calc_found_rows gstime, MAX(stoptime) AS mx, MIN(starttime) AS mn,
Basically what I want is that I need to get desired result in such a way that, whenever Transaction type is Sales Order Issue, I want last TRANSACTION_COSTED_DATE of 'Intransit Shipment'
INVENTORY_ITEM_ID TRANSACTION_COSTED_DATE TRANSACTION_TYPE R 123 28-06-2012 21:36 Intransit Shipment 123 23-07-2012 01:25 Sales order issue 28-06-2012 21:36 123 30-07-2012 05:20 Sales order issue 28-06-2012 21:36
[Code]...
Lag with offset 1 doesn’t work as it will only go to previous row, What I want is that it should go to row above where transaction type is Intransit Shipment
A function should accept two parameters: from_date and to_date which returns no.of Saturdays and Sundays between these dates and also show the dates of those weekends.
I am trying to find sum for one record for each partition but while taking that timestamp giving me bit trouble, i have tried to reproduce the table and some little data
CREATE TABLE TEST_COUNT (END_TIME DATE ,SUCCESSFUL_ROWS NUMBER ,FAILED_ROWS NUMBER ,TBL_NAME VARCHAR (4) ,PARTITION_NAME VARCHAR (240) )
I have to create the following table. The fields Trend_Date, Price and Trend are already given. I have to calculate the field permanently and to insert the value in this permanent table.
Fields:
The field price belong to the value of a product during the trade. The field trade_date belongs to the moment of the trade. The field trend belongs to the future behavior of the the price. Here, the price of the present moment is compared to the following price (possible characteristics: 'UP', 'DOWN', 'STABLE'). The field permanently belongs to the time (in seconds) how long the value of the field Trend_Date (depending on the price) is still true.
For example:
Row 1: The trend in row 1 is 'UP' and it has a price of '11'. Until row 3 this remains true (the price is greater or equal to 11). In this case, the difference between row 1 and row 3 are 9801 (rounded) seconds.
Row 2: The trend in row 2 is 'DOWN' and it has a price of '12'. This remains true till to the end (the price is never greater than 12) In this case, the difference between row 2 and row 11 are 97346 (rounded) seconds. To calculate the 97346 seconds the field has to consider that between row 2 and row 11 are two days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (2*46800) 93600. -> 190945-93600 = 97346s
Row 6: The trend in row 6 is 'UP' and it has a price of '5'. This remains true till to the end (the price is never smaller than 5) In this case, the difference between row 6 and row 11 are 65729 (rounded) seconds. To calculate the 65729 seconds the field has to consider that between row 65729 and row 11 are one days. There will be no trade between 18:00 and 07:00 o'clock. This belongs to 7 hours for each days, in seconds (1*46800) 46800. -> 112528-46800 = 65729s
Row 9: The trend in row 9 is 'STABLE' and it has a price of '8'. Until row 10 this remains true (the price is equal to 8 ). In this case, the difference between row 9 and row 10 is 14418 (rounded) seconds.
Row 11: Is empty because there are no values to compare.
CREATE TABLE "ALLOCATEASSOCIATES" ( "PROJID" VARCHAR2(30) NOT NULL ENABLE, "ASSOCIATEID" NUMBER(*,0) NOT NULL ENABLE, "ALLOCATIONSTARTDATE" DATE, "ALLOCATIONPERCENT" NUMBER(*,0),
[code]...
Given that 1. An associate must be allocated to at-least and a maximum of 100% at any given point of time 2. User selects 2 dates between which inconsistency of allocation needs to be displayed
If the end user selects 1st Apr 2012 and July 31st 2012 between which reports needs to be generated, am looking for the following output
The Allocation_Inconsistency denotes that the associate has a deficit of allocation between the 2 dates. The associate with ID 2 has a deficit of 75% of allocation from 1st Apr 2012 till 15th Apr 2012. Similarly 25% deficit between 16th Apr 2012 and 15th June 2012 and so on so forth. However, there is no allocation deficit for the month of July as he is allocated 100% for this month and hence is not appearing in the expected output.
11.2.0.2 on Solaris..I have such a large post on a very basic space calculation.
We have several tablespaces starting with WLMCS in our DB..I just wanted to calculate the total space consumed in the disk by all these tablespace combined .When I queried DBA_DATA_FILES.MAXBYTES and DBA_DATA_FILES.USER_BYTES , I've noticed that ,
When AUTOEXTEND is NO: MAXBYTES is 0 for these datafiles . But USER_BYTES won't be 0 for these files
When AUTOEXTEND is YES: MAXBYTES will be a non-zero value for these datafiles . USER_BYTES won't be 0 either for these files-- Not including datafile names for better readability.
SYS > select tablespace_name, maxbytes/1024/1024, user_bytes/1024/1024, autoextensible from dba_data_files where tablespace_name like 'WLMCS%';
11 rows selected.To calculate the space consumed , I made 2 assumptions.Are the below 2 assumptions right?
Assumption 1. Whenever MAXBYTES = 0 , USER_BYTES should be considered for the space calculation.
Assumption 2. Whenever you have non-zero values for both MAXBYTES and USER_BYTES , MAXBYTES should be considered for the space calcuation.I did the calculation (adding up of) based on the above assumptions. Is this calcualtion Correct ?
-- Not including datafile names for better readability.
I have a large table and want to calculate just a few values. Therefore, I don't want to create a new table, I want to update the table. Here an example:
I want to calculate the VALUE_LAG with ID = 4 only (-> two values).
create table zTEST ( PRODUCT number, ID number, VALUE number, VALUE_L1 number );
[Code]..
I tried this, but obviously, windows functions are not allowed in the update statement.
update zTEST set VALUE_L1 = lag(VALUE) over (partition by PRODUCT, order by ID) where ID = 4
