Write A Query Based On Inventory On Hand Packed Quantity
Jul 18, 2013which table do I have to use to get the Inventory On Hand Packed Quantity based on Oracle EBS Suite using backend?
View 2 Replieswhich table do I have to use to get the Inventory On Hand Packed Quantity based on Oracle EBS Suite using backend?
View 2 RepliesI want to write select query to get sum of quantity for each type of item ,in the table I have itemnum,itemtype and quantity ,type have value(A,B,C,....)
the result will be like that ex:
item.No A B C D ....
10 50 60 80 20
--- 50 will be sum(quantity) for item type A
how can I do that, I mean how can I retrieve many column from the same field?
I am having a problem with auto populating different fields based on inventory no. field.. This is a bug giving to me to work on and i not able to figure out how to populate the other fields.
How to set any triggers for the items to auto populate and i am suppose to finish this work today.
I have employee, location, city tables, I have written following code to use joins
select ename from employee e, location l, city c
where e.c_locationid= l.locationid and l.cityid= c.cityid and c.cityname='XYZ';
The same can be written by using non- correlated sub query as follow..
select ename
from employee
where c_locationid in (select locationid from location where
cityid in (select cityid from city where cityname='XYZ'))
I need to implement the same concept using correlated sub query...
How to write column to row...in a SQL query?
For example..
SQL> select empno,deptno from emp where empno = 7369;
EMPNO DEPTNO
---------- ----------
7369 20
7369 10
7369 40
The above output to be written in a single row like given below.
7369 20 10 40
1) Actually it may change dynamically...It may be 2 records for some values and there may be 10 records for some value and different for some other
2) It should be in SQL query only..Not in procedures or functions.
Without using any templates, i created a customized sample form....now i want to add data thru that form....i dont know where to write code and what is code if i want to add data?
View 9 Replies View RelatedI have table xx_xml_test m which have row single entry
<logentry
revision="3">
<author>MA111300</author>
<date>2012-10-03T12:42:40</date>
<paths>
<path
[code].......
i want convert the table like below
Revision author date kind action path
3 MA111300 2012-10-03 12:42:40 file A /root.txt
3 MA111300 2012-10-03 12:42:40 file A /sample2/test_2.txt
i have table structure (emp_no, emp_sal,emp_startwork)
i want query to show the monthly salary such as jan month salary Feb month salary
write a query to see how many(no) employees getting salary 3000 & what are their names respectively.
View 2 Replies View Relatedi need to write a query in order to receive a new table.
select table_name,col1, null col2
from
(SELECT b.table_name, b.col1 col1, a.CountPrimaryKeys
from
(SELECT user_constraints.table_name,
COUNT(user_cons_columns.column_name) CountPrimaryKeys
[code].......
I need to write a single query showing the number of requests (job_no) in my organization sections (1A,3A,4C,9Z....etc) based on these conditions..Less than 2 months & between 2 and 6 & between 6 and 12month & more than 12 months in my organization sections.
--Table structure
SQL> desc job_transfer
Name
----------------------
JOB_NO (Request number)
IN_SECT (name of entered section)
IN_DATE (date entered the section)
OUT_SECT
OUT_DATE (date went out from section)
I tried using UNION ALL but it didnt work fine.
is it possible to write select query o/p in html tag?
View 6 Replies View RelatedI am writing following query
SELECT DISTINCT a.list_type_code, a.list_type_name
FROM jls_list_type a, jls_list_control b
WHERE b.jalsa_srl = :jalsa_srl
AND b.list_no != a.list_type_code
ORDER BY list_type_code
I just want to display only those records from JLS_LIST_TYPE which is not present in other table JLS_LIST_CONTROL ... for this i wrote above query but it is not working.
I have to write a sub query / build a logic for the below.
There are several accounts which should have a zero balance i.e sum of all the amoutns in that account should be zero. If they are non zero , i have to report which amounts make up non zero balance.
If i have amts as +20 , -20 , -30,-10 i.e the sum is -40 indicating a non zero amount. I need the entire details of the records which makes up non zero sum. So in above case details related to -30 aand -10.
I'm using a sum group clause on several fields at which sum is required to be checked ie. date , account , currency . query that will bring individual records that don't make the sum zero.
Is it possible to write a outer query which will bring individual records which don't sum up to zero.
is it possible to write sql query o/p in HTML page?
View 3 Replies View RelatedIn my main query, There is a single row sub query which returns row with del_flag as N. Now in my table, there are 2 rows getting added from the incoming feed with del_flag as Y & N.
Here is my Table:
ID_1 DEL_F ID_2
-------------------------------
16643162 Y 49696
16643162 N 16643162
16612344 Y 98888
So how can i write a single row sub-query which returns ID_2 value of a row with del_flag as N. If there is no row with DEL_F as N, it should return ID_2 value of row with DEL_F as Y.i have tried below query, but it showing the error.
select
(case
when min(del_f)='N' then to_number(ID_2)
when min(del_f)='Y' then to_number(ID_2)
end ) ID_2 from table where ID_1=?
Here, im passing ID_1 value from main query
I need to write a dynamic SQL in PL SQL to query an unknown number of columns. Let me take a simple example query here:
SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS
If I have known the number of columns, e.g. querying two columns: "FIRST_NAME" and "LAST_NAME", I can write a DYNAMIC SQL based on the template in table 8-2 of URL....
DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
FIRST_NAME varchar2(200);
LAST_NAME varchar2(200);
BEGIN
cur_hdl := dbms_sql.open_cursor; -- open cursor
stmt_str := 'SELECT FIRST_NAME, LAST_NAME FROM VENDOR_CONTACTS';
[code]....
However, if I wish to write a dynamical sql to query these two columns for a more general purpose (which should meet the requirement to query different number of columns, e.g. three columns, FIRST_NAME, LAST_NAME, BIRTHDAY instead of two columns FIRST_NAME and LAST_NAME). To do this I first try to query the same two columns but using a different method, following URL.....My code for the same query has error, and I cannot solve it.
DECLARE
stmt_str varchar2(200);
cur_hdl int;
rows_processed int;
l_columns dbms_sql.desc_tab;
l_dummy NUMBER;
l_value NUMBER;
[code]....
I am having Oracle 11g (Release 11.2.0.2.0) running on Windows Server 2007 SP2. I am trying to connect to this server from my system using SQL Plus command prompt. (Dedicated - redirect (client and server are on different computers)
I am getting an error: TNS-12518: TNS:listener could not hand off client connection.
When I checked the listener log in the server machine, these were the errors:
TNS-12518: TNS:listener could not hand off client connection
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
32-bit Windows Error: 203: Unknown error
tnsname.ora entry in the client side(my system)
BAM = (DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = md1svcvwm01)(PORT = 1521))
)
[code]...
From the command prompt, I use sqlplus bamtest10/bamtest10@BAM to connect where in I am getting the errors.
how to write the query option in expdp ....in expdp by using query option...
where columnname between '05-May-12 02:57:00.000 AM' and '6-May-12 02:59:59.999 AM';
how to assign the values by taking the values on LEFT HAND SIDE DYNAMICALLY.
Say if I have a,b,c,d in an array then I need to take variables from the array and assign some values to it. Like a=1;b=2;c=3;d=4;
write a query to delete similar records in particular fields(columns) in different tables.
View 2 Replies View RelatedTNS-12518: TNS:listener could not hand off client connection
TNS-12571: TNS:packet writer failure
TNS-12560: TNS:protocol adapter error
TNS-00530: Protocol adapter error
Linux Error: 104: Connection reset by peer
how to reduce quantity from one table due to other table for example .i have a purchase a shampoo in 20 quantity and then sale this shampo 2 quantity...so i want now there should be 18 quantity of shampoo in purchase table automatically
Purchase tableSale tabel
Product IdSale id
Product Nameproduct name
QuantityQuantity
Unable to connect to database getting below error
sqlplus sys/oracle@server_name/orcl
SQL*Plus: Release 10.2.0.1.0 - Production on Fri Apr 12 11:07:15 2013
Copyright (c) 1982, 2005, Oracle. All rights reserved.
ERROR:
ORA-12518: TNS:listener could not hand off client connection
Below is alertlog entry
Completed: alter database open
Thu Apr 04 17:06:19 2013
db_recovery_file_dest_size of 4096 MB is 0.00% used. This is a
user-specified limit on the amount of space that will be used by this
[code]...
I have problem about sum record on form.
This is records display on form.
NO Merchandise Slip Quantity
------------------------------------------------
152501B002CAH2-002980016
162501B002CAH1-003528716
142501B002CAH1-003529812
122501B002CAH2-002979316
132501B002CAH2-002979612
202501B002CAH1-003529612
212501B002CAH1-003529716
192501B002CAH2-002979516
172501B002CAH2-002978716
182501B002CAH1-003529116
112501B002CAH1-003529212
42501B002CAH1-003530012
52501B002CAH2-002978916
[Code]....
Now, i want to count quantity follow slip_no and merchandise.
Example:
Merchandise :2501B002CA have 8 slip with quantity is 12 and 5 slip with quantity is 16.
Merchandise: 2501B001CA have 6 slip with quantity is 12 and 7 slip with quantity is 16.
Detail you can see file attach
-------
But i don't know how to do for result same above.
I have schedule table like below format:Materila Code Schedule No. Schdule Quanity Balance Quantity
ABC 1 500 500
ABC 2 300 300
ABC 3 200 200
If i received 600 quantity then data should update as below in schedule table:
ABC 1 500 0
ABC 2 300 200
ABC 3 200 200
Single material contain multiple schedules. how to programatically do the above updation.
getting the yearly qty distributed evenly across 12 months. I can do this in PL/SQL but want to know is it possible in SQL.
Database version 10g
create table test_tbl( qty number(22),
yr number,
key_val varchar(10)
);
insert into test_tbl values( 500000, 2013, '1');
insert into test_tbl values (56789, 2014, '2');
commit;
This is the output I need to get from a SQL select query
Key Month Year qty
--------------------------------------------------------------
1 1 2013 41667
1 2 2013 41667
1 3 2013 41667
1 4 2013 41667
1 5 2013 41667
1 6 2013 41667
1 7 2013 41667
[code]....
i am trying to create a update trigger that allows any quantity reduction of 5 units and if the reduction is more than 5 units it blocks it and just reduces it by 5.here are two scenarios
SQL Update statement where the new Quantity value being smaller than 5 is allowed by the trigger.
-- old qty 20
update purchase_order
set quantity = 18
where po_no = 'PO11';
-- new qty 18
SQL Update statement where the new Quantity value being larger than 5 is only reduced by 5 by the trigger.
--old qty 25
update purchase_order
set quantity = 19
where po_no = 'PO15';
-- new qty 20
i just have the basic trigger code, but i think it is the math that i am not getting (i was never good at math)
create or replace trigger purchase_quantity_updt
before update of quantity on purchase_order
for each row
when (new.quantity < old.quantity )
begin
-- not sure what to put here
end;
I have one requirement.I am populating LOV dynamically for the subinventory field in the order form through custom.pll.
If Dropship flag= 'Y' then populate suninv LOV with D1,d2..values.If it is 'N' then populate suninv LOV with w01,w02,....values.This is done and working fine in the form.But If i save dropship flag='Y' and Subinv value is D1 then it saves the record.Now I am changing Drop ship flag as 'N' then 'D1' value appearing in the SUBINV field.and it allow the save changes.
So How can i refresh Lov at the time of making drop ship flag as 'Y'/'N'?
1)What is the use of inventory?
2) Global inventory is found by checking the oraInst.loc file. How to find the location of Local inventory?
3)Why is every patchset(11.2.0.2) now a full release?