RANGE UNBOUNDED

select
gwid,
spidm,
ctrm,
ctrm_yr,
ctrm_sort,
gamaj11,
gacol,
ehrs_cum,
gpa_hrs gpa_hrs1,
nod_gpahrs GPA_HRS2,
to_char(avg(GPA),'fm99990.00') GPA,
to_char(nvl(avg(pts),0),'fm999999990.00') pts,
to_char(avg(GPA2),'fm999990.00') GPA2,
shrttrm_astd_code_end_of_term,
to_char(SUM(ehrs_cum) OVER (ORDER BY ctrm_yr,ctrm_sort RANGE UNBOUNDED PRECEDING),'fm99990.00') ehrs_cum_ext,
to_char(SUM(gpa_hrs) OVER (ORDER BY ctrm_yr,ctrm_sort RANGE UNBOUNDED PRECEDING),'fm99990.00') gpa_hrs_ext,
to_char(SUM(avg(GPA)) OVER (ORDER BY ctrm_yr,ctrm_sort RANGE UNBOUNDED PRECEDING),'fm99990.00') gpa_ext,
to_char(SUM(avg(pts)) OVER (ORDER BY ctrm_yr,ctrm_sort RANGE UNBOUNDED PRECEDING),'fm99990.00') pts_ext,
shrttrm_astd_code_dl
from (
select
 distinct gwid,
spidm,
ctrm,
ctrm_yr,
ctrm_sort,
gamaj11,
gacol,
to_char(round(ehrs_cum),'fm999990.00') ehrs_cum,
to_char(round(gpa_hrs),'fm999990.00') gpa_hrs,
to_char(gpa,'fm999990.00') gpa,
pts pts,
to_char(round(nod_gpahrs),'fm999990.00')  nod_gpahrs,
tqpts,
to_char(GPA2,'fm999990.00')  GPA2,
shrttrm_astd_code_end_of_term,
shrttrm_astd_code_dl

from
(
select
gzrbipr_char_3 gwid,
gzrbipr_char_4 spidm,
gzrbipr_char_77 ctrm ,
SUBSTR(gzrbipr_char_77,instr(gzrbipr_char_77,' ',-1,1)+1) ctrm_yr,
(case when SUBSTR(gzrbipr_char_77,1,instr(gzrbipr_char_77,' ',-1,1)-1)='Spring' then 'a'
when SUBSTR(gzrbipr_char_77,1,instr(gzrbipr_char_77,' ',-1,1)-1)='Summer' then 'b'
when SUBSTR(gzrbipr_char_77,1,instr(gzrbipr_char_77,' ',-1,1)-1)='Fall' then 'c'
else null
end ) ctrm_sort,
gzrbipr_char_31 gacol ,
gzrbipr_char_36 gamaj11 ,
gzrbipr_char_88 ehrs_cum ,
nvl(gzrbipr_char_91,0)  gpa_hrs ,
nvl(gzrbipr_char_165,0) gpa ,
( case when gzrbipr_char_167='Y' THEN (case when gzrbipr_char_69 < '199303' then nvl(gzrbipr_char_166,0)  end)
  else nvl(gzrbipr_char_166,0)
  end)  pts,
nvl(gzrbipr_char_91,0) nod_gpahrs,
nvl(gzrbipr_char_166,0) tqpts
,
(case when gzrbipr_char_91>0 then gzrbipr_char_166/gzrbipr_char_91 end)GPA2,
    gzrbipr_char_82 shrttrm_astd_code_end_of_term,
  gzrbipr_char_83 shrttrm_astd_code_dl
from gzrbipr
)

group by
gwid,
spidm,
ctrm,
ctrm_yr,
ctrm_sort,
gamaj11,
gacol,
ehrs_cum,
gpa_hrs,
gpa,
pts,
nod_gpahrs,
tqpts,
shrttrm_astd_code_end_of_term,
shrttrm_astd_code_dl
,
GPA2
)
where gwid='G14759230'
Group by
gwid,
spidm,
ctrm,
ctrm_yr,
ctrm_sort,
gamaj11,
gacol,
ehrs_cum,
gpa_hrs ,
nod_gpahrs,
shrttrm_astd_code_end_of_term,
shrttrm_astd_code_dl
order by ctrm_yr,ctrm_sort

To Get text from Data source

select *
from user_source
where upper(text) like upper('%Enter_text_hear%');

Ref CUrsor Example

I ref cursor is a variable that holds a cursor. Underneath, it is a "regular" cursor. It is a pointer to a cursor. 

Normally a cursor is a static thing, it points to A query: 


declare 
c1 cursor for select * from emp; 
.... 


It is clear that C1 will run that query and none other. A ref cursor can have an arbitrary query associated with it: 

declare 
type rc is ref cursor; 
c1 rc; 
begin 
if ( a = b ) then 
open c1 for select * from emp; 
else 
open c1 for select * from dept; 
end if;

How to use parameters in a 'where value in…' clause? (integer)

DECLARE
EX varchar2(100):='10,20,01,02,03,04';
param1 varchar2(100):='vishnu';
param2 varchar2(100):='teja';
retval BOOLEAN;
BEGIN
retval:=F_DEMO(EX);
END;

//////////////////////////////////////////////////
CREATE OR REPLACE FUNCTION F_DEMO(EX VARCHAR2)
RETURN BOOLEAN
IS


  l_rc       sys_refcursor;
  l_tbbestu_rec tbbestu%rowtype;
  begin
   open l_rc for 'SELECT  *
FROM tbbestu
WHERE   xxxxxxxx =('|| param1||')
and yyyyyyyy=('|| param2||')
 tbbestu_exemption_code  IN(' || EX || ')';
   loop
    fetch l_rc into l_tbbestu_rec;
     exit when l_rc%notfound;
     dbms_output.put_line( l_tbbestu_rec.tbbestu_exemption_code );
   end loop;
  close l_rc;
  return true;
 end F_DEMO;

\\ 

How to use parameters in a 'where value in…' clause? (Char)

CREATE OR REPLACE FUNCTION F_DEMO(pprccmt_cmty_code0 VARCHAR2,stu_pidm VARCHAR2)
RETURN BOOLEAN
IS
QUERY24       sys_refcursor;
l_pprccmt_rec pprccmt%rowtype; 
BEGIN
dbms_output.put_line( stu_pidm||'  '||pprccmt_cmty_code0);
OPEN QUERY24 FOR 'select
*
from payroll.pprccmt
where pprccmt_pidm = '|| stu_pidm ||'
AND pprccmt_cmty_code IN ('|| pprccmt_cmty_code0 ||')';
LOOP
  FETCH QUERY24 INTO l_pprccmt_rec;
  EXIT WHEN QUERY24%notfound;
  dbms_output.put_line( l_pprccmt_rec.pprccmt_cmty_code||'  '||l_pprccmt_rec.pprccmt_pidm);
END LOOP;
close QUERY24;
return true;
end F_DEMO;
//////////////////////////////
DECLARE
pprccmt_cmty_code varchar2(100):='''TRS'',''HD4'',''ORH''';
stu_pidm VARCHAR2(100):='5564';
retval BOOLEAN;
BEGIN
retval:=F_DEMO(pprccmt_cmty_code,stu_pidm);
END;

SELECT
  CASE
    WHEN level = 1
    THEN '01'
    WHEN level = 2
    THEN '02'
    WHEN level = 3
    THEN '04'
    WHEN level = 4
    THEN '05'
  END TEST
FROM dual
  CONNECT BY level <= 4;

How to increase dbms_output buffer?

 

dbms_output.enable(buffer_size IN INTEGER DEFAULT 20000);
exec dbms_output.enable(1000000);

Surrogate Keys, Natural Keys, Candidate Keys, Composite Keys and Super Keys

Candidate Key

A candidate key is a combination of attributes that can be uniquely used to identify a database record without any extraneous data. Each table may have one or more candidate keys. In general, one of these candidate keys is selected as the table primary key.

Example - From the above table EMPLOYEE_ID, EMPLOYEE_SSN_ID, and EMPLOYEE_DEPT_ID can be considered as candidate keys

Primary Key

A primary key is a single column or combination of columns that uniquely defines a record. None of the columns that are part of the primary key can contain a null value. A table can have only one primary key.

Example - EMPLOYEE_ID or EMPLOYEE_SSN_ID can be considered as primary keys

Unique Key

A unique key or primary key [is a candidate key] to uniquely identify each row in a table. It be comprised of either a single column or multiple columns.

The major difference is that for unique keys the implicit NOT NULL constraint is not automatically enforced, while for primary keys it is enforced. Thus, the values in unique key columns may or may not be NULL.

Differences between Primary Key and Unique Key

Primary Keys -
1. It will not accept null values.      
2. There will be only one primary key in a table.      
3. Clustered index is created in Primary key.      
4. Primary key allows each row in a table to be uniquely identified and ensures that no duplicate rows exist.      

Unique Keys -
1. Null values are accepted.
2. More than one unique key will be there in a table.
3. Non-Clustered index is created in unique key.
4. Unique key constraint is used to prevent the duplication of key values within the rows of a table and allow null values.

Alternate Key

A candidate key that is not the primary key is called an alternate key.

Example - If EMPLOYEE_ID is considered as primary keys then EMPLOYEE_SSN_ID is an alternate key.

Superkey

A superkey is a combination of attributes that can be uniquely used to identify a database record. A table might have many superkeys. Candidate keys are a special subset of superkeys that do not have any extraneous information in them.

A primary key is therefore a minimum superkey.

Examples - Any combination of the following can be considered as a Super key

- EMPLOYEE_ID - Minimal Super Key

- EMPLOYEE_ID and EMPLOYEE_SSN_ID

- EMPLOYEE_ID, EMPLOYEE_SSN_ID and EMPLOYEE_DEPT_ID

- EMPLOYEE_ID, EMPLOYEE_SSN_ID, EMPLOYEE_DEPT_ID, EMPLOYEE_FIRST_NAME

- EMPLOYEE_SSN_ID, EMPLOYEE_FIRST_NAME, EMPLOYEE_LAST_NAME

Foreign Key

The foreign key identifies a column or a set of columns in one (referencing) table that refers to a column or set of columns in another (referenced) table.

Composite Key

A primary key that made up of more than one attribute is known as a composite key.

Example - [ EMPLOYEE_ID and EMPLOYEE_SSN_ID ] can together be treated as (one of) composite keys. Another combination can be [ EMPLOYEE_ID, EMPLOYEE_SSN_ID and EMPLOYEE_DEPT_ID ]

Surrogate Key

Surrogate keys are keys that have no business meaning and are solely used to identify a record in the table.

Such keys are either database generated (example: Identity in SQL Server, Sequence in Oracle, Sequence/Identity in DB2 UDB etc.) or system generated values (like generated via a table in the schema).

Joins

Joins


FROM

 HZ_PARTIES PARTY,
  HZ_CUST_ACCOUNTS CUST_ACCT,
  OE_ORDER_LINES_ALL LINE,
  OE_AGREEMENTS AGREE,
  OE_TRANSACTION_TYPES_TL lt,
  QP_LIST_HEADERS_TL PL,
  MTL_SYSTEM_ITEMS_VL ITEMS,
  MTL_UNITS_OF_MEASURE_TL UOM,


WHERE

LINE.line_type_id                        = lt.transaction_type_id
AND lt.language                                = userenv('LANG')
AND LINE.agreement_id                          = agree.agreement_id(+)
AND LINE.price_list_id                         = pl.list_header_id(+)
AND pl.language(+)                             = userenv('LANG')
AND LINE.sold_to_org_id                        = cust_acct.cust_account_id(+)
AND PARTY.party_id(+)                          = cust_acct.party_id
AND LINE.inventory_item_id                     = items.inventory_item_id(+)
AND items.organization_id                      =ITEMS.organization_id
AND LINE.order_quantity_uom                    = uom.uom_code(+)
AND UOM.language(+)                            = userenv('LANG')

Joins

Joins


FROM

 HZ_PARTIES PARTY,
  HZ_CUST_ACCOUNTS CUST_ACCT,
  OE_ORDER_LINES_ALL LINE,
  OE_AGREEMENTS AGREE,
  OE_TRANSACTION_TYPES_TL lt,
  QP_LIST_HEADERS_TL PL,
  MTL_SYSTEM_ITEMS_VL ITEMS,
  MTL_UNITS_OF_MEASURE_TL UOM,


WHERE

LINE.line_type_id                        = lt.transaction_type_id
AND lt.language                                = userenv('LANG')
AND LINE.agreement_id                          = agree.agreement_id(+)
AND LINE.price_list_id                         = pl.list_header_id(+)
AND pl.language(+)                             = userenv('LANG')
AND LINE.sold_to_org_id                        = cust_acct.cust_account_id(+)
AND PARTY.party_id(+)                          = cust_acct.party_id
AND LINE.inventory_item_id                     = items.inventory_item_id(+)
AND items.organization_id                      =ITEMS.organization_id
AND LINE.order_quantity_uom                    = uom.uom_code(+)
AND UOM.language(+)                            = userenv('LANG')

To find locks in Oracle Database

select * from dba_locks;

select * from dba_locks where blocking_others!='Not Blocking';

Price list setup Query

SELECT h.DESCRIPTION,
  h.CURRENCY_CODE,
  h.ACTIVE_FLAG,
  h.LAST_UPDATE_DATE,
  h.ROUNDING_FACTOR,
  h.FREIGHT_TERMS_CODE,
  h.PTE_CODE,
  h.COMMENTS,
  L.LIST_PRICE_UOM_CODE,
  L.PRIMARY_UOM_FLAG,
  L.LIST_PRICE,
  L.LIST_LINE_TYPE_CODE,
  L.OPERAND ,
  L. START_DATE_ACTIVE,
  L.PRODUCT_PRECEDENCE PRODUCT_VALUE ,
  L.ARITHMETIC_OPERATOR APPLICATION_METHOD,
  L. ATTRIBUTE1 INITIAL_PRICE,
  L.ATTRIBUTE2 INCREMENTS,
  L.ATTRIBUTE3 RETAIL_PRICE,
  L. ATTRIBUTE4 CLSS,
  L.ATTRIBUTE5 DIVISION,
  L.ATTRIBUTE6 DEPARTMENT,
  L. ATTRIBUTE7 VENDOR_SLOT,
  L. ATTRIBUTE8 COUNTRY_OF_ORIGIN,
  L. ATTRIBUTE9 LABEL_NAME1,
  L. ATTRIBUTE10 LABEL_NAME2,
  L. ATTRIBUTE14 PR_NUMBER1,
  L.ATTRIBUTE15 PR_NUMBER2,
  F.NAME,
  I.DESCRIPTION product_description,
  I.SEGMENT1
  ||'.'
  ||I.SEGMENT2 PRODUCT_VALUE,
  A.PRODUCT_ATTRIBUTE_CONTEXT PRODUCT_CONTEXT,
  A.PRODUCT_ATTRIBUTE PRODUCT_ATTRIBUTE,
  A.PRODUCT_ATTR_VALUE PRODUCT_VALUE,
  A.PRICING_ATTRIBUTE_CONTEXT PRICING_CONTEXT,
  A.PRICING_ATTRIBUTE,
  A.COMPARISON_OPERATOR_CODE OPERATOR,
  A.PRICING_ATTR_VALUE_FROM VALUE_FROM,
  A.PRICING_ATTR_VALUE_TO VALUE_TO
FROM qp_list_headers h,
  QP_LIST_LINES L,
  QP_PRICE_FORMULAS_TL F,
  mtl_system_items_b I,
  QP_PRICING_ATTRIBUTES A
WHERE H.LIST_HEADER_ID               =L.LIST_HEADER_ID
AND L.GENERATE_USING_FORMULA_ID      =F.PRICE_FORMULA_ID
AND TO_NUMBER (A.product_attr_value) = I.inventory_item_id
AND TO_NUMBER(A.LIST_LINE_ID)        =L.LIST_LINE_ID
AND I.organization_id                =
  (SELECT qp_util.get_item_validation_org FROM DUAL
  ); 

Invoice Querys

INV GROUP BY CUSTOMER NAME

Select rc.customer_NAME,sum(rctla.extended_amount) ,COUNT(rcta.trx_number)--,rctla.revenue_amount
from ra_customers rc,ra_customer_trx_all rcta ,ra_customer_trx_lines_all rctla
where rc.customer_id =nvl(rcta.sold_to_customer_id,rcta.bill_to_customer_id)
and rcta.customer_trx_id = rctla.customer_trx_id
--and rc.customer_number =:customer
and rcta.trx_number BETWEEN 'xxxxxx' AND 'xxxxxxx'
group by rc.customer_name;


*********************************************************************************

invoices with trx date

select DISTINCT rct.trx_number,rct.trx_date
from ra_customers ac,
ra_customer_trx_all rct,
ra_customer_trx_lines_all b
where ac.customer_id=rct.sold_to_customer_id(+)
and rct.customer_trx_id = b.customer_trx_id
--and  ac.customer_number=3059
--and rct.trx_date = '06-DEC-11'
and rct.trx_number in (select DISTINCT PO from Imported Table)
--and purchase_order in('xxxxxxx')
order by rct.trx_date desc;


*********************************************************************************

INVOICE NUMBERS

select * from ra_interface_lines_all where interface_line_attribute1='12873516';


SELECT attribute14 FROM RA_CUSTOMER_TRX_LINES_ALL WHERE interface_line_attribute1='12873516';


SELECT trx_number FROM RA_CUSTOMER_TRX_ALL WHERE interface_HEADER_attribute1='12873516';


*********************************************************************************

Invoice amout Check Query

Select rc.customer_number,rc.customer_name,rcta.purchase_order,rcta.trx_number,sum(rctla.extended_amount)--,rctla.revenue_amount

from ra_customers rc,ra_customer_trx_all rcta ,ra_customer_trx_lines_all rctla

where rc.customer_id =nvl(rcta.sold_to_customer_id,rcta.bill_to_customer_id)

and rcta.customer_trx_id = rctla.customer_trx_id

--and rc.customer_number =:customer

and rcta.trx_number in  ('xxxxxxx','xxxxx','xxxx')

group by rc.customer_number,rc.customer_name,rcta.purchase_order,rcta.trx_number;

SQL Query to get Quantity On Hand of any item

SELECT   item.segment1 item_code, item.description item_description,

         SUM (on_hand.on_hand) on_hand

    FROM mtl_system_items_b item, mtl_onhand_total_mwb_v on_hand

   WHERE item.organization_id = 4  --ORG ID

  AND item.inventory_item_id = on_hand.inventory_item_id

     AND on_hand.subinventory_code = 'XXX'  --sub inventory

     AND item.segment1 = 'XXXXX' ---item name

GROUP BY item.segment1, item.description;

Join Relations Between tables in O2C

1------------HEADER_ID------------
select * from oe_order_headers_all;
select * from oe_order_lines_all;

2---------CUSTOMER_ID-----SOLD_TO_ORG_ID-----
select * from RA_CUSTOMERS;
SELECT * FROM OE_ORDER_HEADERS_ALL;

3------CUSTOMER_ID---SOLD_TO_ORG_ID------------------
select * from RA_CUSTOMERS;
select * from oe_order_lines_all;


4------------wdd.delivery_detail_id----wdt.delivery_detail_id----
SELECT * FROM WSH_DELIVERY_DETAILS;
SELECT * FROM WSH_DELIVERY_TRIPS_V;

5------CUSTOMER_ID-----------CUSTOMER_ID-----------

SELECT * FROM WSH_DELIVERY_DETAILS;
select * from RA_CUSTOMERS;

6-------------CUST_PO_NUMBER----------CUST_PO_NUMBER----------
SELECT * FROM oe_order_headers_all;
SELECT * FROM WSH_DELIVERY_DETAILS ;

7-------------CUST_PO_NUMBER----------CUST_PO_NUMBER----------
SELECT * FROM WSH_DELIVERY_DETAILS ;
select * from oe_order_lines_all;



8--------D.SEGMENT1||'.'||D.SEGMENT2=L.ORDERED_ITEM----------
select * from oe_order_lines_all D;
SELECT * FROM mtl_system_items_b L;
--Join oe_order_lines_all and oe_order_headers_all using ---HEADER_ID----

9------------D.SOLD_TO_CUSTOMER_ID=L.customer_id----------
select * from ra_customer_trx_all D;
select * from RA_CUSTOMERS L;

10-----------D.LIST_HEADER_ID=L.LIST_HEADER_ID-------------
SELECT * FROM QP_LIST_HEADERS_V D;
SELECT * FROM QP_LIST_LINES_V L;

11.-----------L.LIST_HEADER_ID=D.LIST_HEADER_ID---------------
SELECT * FROM qp_pricing_attributes L;
SELECT * FROM QP_LIST_HEADERS_V D;

12-------------D.LIST_LINE_ID=L.LIST_LINE_ID---------------
SELECT * FROM qp_pricing_attributes L;
SELECT * FROM QP_LIST_LINES_V D;

Join Relations Between tables in O2C

1------------HEADER_ID------------
select * from oe_order_headers_all;
select * from oe_order_lines_all;

2---------CUSTOMER_ID-----SOLD_TO_ORG_ID-----
select * from RA_CUSTOMERS;
SELECT * FROM OE_ORDER_HEADERS_ALL;

3------CUSTOMER_ID---SOLD_TO_ORG_ID------------------
select * from RA_CUSTOMERS;
select * from oe_order_lines_all;


4------------wdd.delivery_detail_id----wdt.delivery_detail_id----
SELECT * FROM WSH_DELIVERY_DETAILS;
SELECT * FROM WSH_DELIVERY_TRIPS_V;

5------CUSTOMER_ID-----------CUSTOMER_ID-----------

SELECT * FROM WSH_DELIVERY_DETAILS;
select * from RA_CUSTOMERS;

6-------------CUST_PO_NUMBER----------CUST_PO_NUMBER----------
SELECT * FROM oe_order_headers_all;
SELECT * FROM WSH_DELIVERY_DETAILS ;

7-------------CUST_PO_NUMBER----------CUST_PO_NUMBER----------
SELECT * FROM WSH_DELIVERY_DETAILS ;
select * from oe_order_lines_all;



8--------D.SEGMENT1||'.'||D.SEGMENT2=L.ORDERED_ITEM----------
select * from oe_order_lines_all D;
SELECT * FROM mtl_system_items_b L;
--Join oe_order_lines_all and oe_order_headers_all using ---HEADER_ID----

9------------D.SOLD_TO_CUSTOMER_ID=L.customer_id----------
select * from ra_customer_trx_all D;
select * from RA_CUSTOMERS L;

10-----------D.LIST_HEADER_ID=L.LIST_HEADER_ID-------------
SELECT * FROM QP_LIST_HEADERS_V D;
SELECT * FROM QP_LIST_LINES_V L;

11.-----------L.LIST_HEADER_ID=D.LIST_HEADER_ID---------------
SELECT * FROM qp_pricing_attributes L;
SELECT * FROM QP_LIST_HEADERS_V D;

12-------------D.LIST_LINE_ID=L.LIST_LINE_ID---------------
SELECT * FROM qp_pricing_attributes L;
SELECT * FROM QP_LIST_LINES_V D;

Created Three Tables

1.LOCATIONS
2.DEPARTMENTS
3.EMPLOYEES


1.LOCATIONS

CREATE TABLE LOCATIONS(
 LOCATION_ID                                        NUMBER(4),
 STREET_ADDRESS                                     VARCHAR2(40),
 POSTAL_CODE                                        VARCHAR2(12),
 CITY                                       VARCHAR2(30) NOT NULL,
 STATE_PROVINCE                                     VARCHAR2(25),
 COUNTRY_ID                                         CHAR(2),
 constraint pk_loc primary key (LOCATION_ID));

2.DEPARTMENTS
 /
 CREATE TABLE DEPARTMENTS(
 DEPARTMENT_ID                                       NUMBER(4),
 DEPARTMENT_NAME                            VARCHAR2(30) NOT NULL,
 MANAGER_ID                                         NUMBER(6),
 LOCATION_ID                                        NUMBER(4),
 constraint pk_dep primary key (DEPARTMENT_ID,MANAGER_ID),
  --constraint pk_dep primary key (MANAGER_ID),
 constraint fk_locId foreign key (LOCATION_ID ) references LOCATIONS (LOCATION_ID ) );
 /

3.EMPLOYEES

 CREATE TABLE EMPLOYEES(
 EMPLOYEE_ID                                        NUMBER(6),
 FIRST_NAME                                         VARCHAR2(20),
 LAST_NAME                                          VARCHAR2(25) NOT NULL,
 EMAIL                                              VARCHAR2(25) NOT NULL,
 PHONE_NUMBER                                       VARCHAR2(20),
 HIRE_DATE                                          DATE NOT NULL,
 JOB_ID                                             VARCHAR2(10) NOT NULL,
 SALARY                                             NUMBER(8,2),
 COMMISSION_PCT                                     NUMBER(2,2),
 MANAGER_ID                                         NUMBER(6),
DEPARTMENT_ID                                      NUMBER(4),

 constraint pk_emp primary key (EMPLOYEE_ID),
 constraint fk_DeptID foreign key (DEPARTMENT_ID,MANAGER_ID ) references DEPARTMENTS (DEPARTMENT_ID,MANAGER_ID )) ;


Insert Values into Tables


select * from LOCATIONS;

insert into LOCATIONS values(7,'Kirkland','98007','Kent','Lynnwood','K');

select * from  DEPARTMENTS;

insert into DEPARTMENTS values(10,'ACCOUNTING',010,1);
insert into DEPARTMENTS values(20,'RESEARCH',020,2);
insert into DEPARTMENTS values(30,'SALES',030,3);
insert into DEPARTMENTS values(40,'OPERATIONS',040,4);
insert into DEPARTMENTS values(41,'SHIPPING',050,5);
insert into DEPARTMENTS values(50,'SHIPPING_DESK',060,6);
insert into DEPARTMENTS values(60,'CARGO_DESK',070,7);

select * from EMPLOYEES;

insert into EMPLOYEES values(7839,'KING','KING','king@gmail.com','123456789','17-NOV-81','PRESIDENT',5500,null,010,10);
insert into EMPLOYEES values(7698,'BLAKE','BLAKE','BLAKE@gmail.com','123456789','01-MAY-81','MANAGER',3350,null,030,30);
insert into EMPLOYEES values(7782,'CLARK','CLARK','CLARK@gmail.com','123456789','09-JUN-81','MANAGER',2950,null,010,10);
insert into EMPLOYEES values(7566,'JONES','JONES','JONES@gmail.com','123456789','02-APR-81','MANAGER',3475,null,020,20);
insert into EMPLOYEES values(7788,'SCOTT','SCOTT','SCOTT@gmail.com','123456789','09-DEC-82','ANALYST',3500,null,020,20);
insert into EMPLOYEES values(7902,'FORD','FORD','FORD@gmail.com','123456789','03-DEC-81','ANALYST',3500,null,020,20);
insert into EMPLOYEES values(7369,'SMITH','SMITH','SMITH@gmail.com','123456789','17-DEC-80','CLERK',1300,null,020,20);
insert into EMPLOYEES values(7499,'ALLEN','ALLEN','ALLEN@gmail.com','123456789','20-FEB-81','SALESMAN',2100,null,030,30);
insert into EMPLOYEES values(7521,'WARD','WARD','WARD@gmail.com','123456789','22-FEB-81','SALESMAN',1750,null,030,30);
insert into EMPLOYEES values(7654,'MARTIN','MARTIN','MARTIN@gmail.com','123456789','28-SEP-81','SALESMAN',1750,null,030,30);
insert into EMPLOYEES values(7844,'TURNER','TURNER','TURNER@gmail.com','123456789','08-SEP-811','SALESMAN',2000,null,030,30);
insert into EMPLOYEES values(7876,'ADAMS','ADAMS','ADAMS@gmail.com','123456789','12-JAN-83','CLERK',1600,null,020,20);
insert into EMPLOYEES values(7900,'JAMES','JAMES','JAMES@gmail.com','123456789','03-DEC-81','CLERK',1450,null,030,30);
insert into EMPLOYEES values(7934,'MILLER','MILLER','MILLER@gmail.com','123456789','23-JAN-82','CLERK',1800,null,010,10);

Query to find Oracle Form Name and associated function

select
fnf.application_id,
fa.APPLICATION_NAME,
ff.FUNCTION_NAME ,
ffl.USER_FUNCTION_NAME ,
ffl.description ,
fnf.form_name,
ff.parameters,
ff.type
from fnd_form_functions_tl ffl,
fnd_form_functions ff,
fnd_form fnf,
fnd_application_tl fa
where
–ff.function_name like ‘******’
ffl.FUNCTION_ID=ff.FUNCTION_ID
–and ff.type=’FORM’
and fnf.form_id=ff.form_id
and fa.application_id=fnf.application_id
–and fa.APPLICATION_ID=******

Error You are trying to access a page that is no longer active. - The referring page may have come from a previous session. Please select Home to proceed.

The root cause of this error are the profile options:
1) FND_VALIDATION_LEVEL - None
2) FND_FUNCTION_VALIDATION_LEVEL - None
3)Framework Validation Level- None
They should be none in order to avoid the error.The root cause of the error is MAC key url validation, which is governed by the above 3 profiles.

Also, if you are getting error in doing a page submit on pop up page confirm:
FND: Framework Compatibility Mode profile option should have value greater than or equal to "11.5.10".

To Check Custom Messages in Oracle apps

SELECT * FROM  FND_NEW_MESSAGES where MESSAGE_NAME='XXXXX';

Query's to check Order Management

-- Query to check all orders in interface with details

select h.attribute1, h.attribute10,orig_sys_document_ref,h.customer_number,h.price_list_id,r.customer_name,h.order_type,h.creation_date,h.attribute6,error_flag,booked_flag,h.request_date,h.attribute2,h.ship_to_site_int,h.payment_term,h.payment_term_id
from oe_headers_iface_all h,ra_customers r
where h.customer_number= r.customer_number
--and error_flag is null
---and error_flag ='Y'
--and h.order_type like '%PRO%'
--and h.customer_po_number in ('365')
and h.customer_number  in()
--and trunc(h.creation_date)= '10-DEC-2012'
order by customer_number,creation_date desc;
*********************************************************************************

---Update the ship_to in interface using PO#

Headers::

update oe_headers_iface_all
set ship_to_site_int='xxxx'
where orig_sys_document_ref ='xxxx;

Lines::

update oe_lines_iface_all
set global_attribute2='5465A'
where orig_sys_document_ref ='xxxxx';

*********************************************************************************
---Query to Book the Orders using Script::


declare
cursor stylprc is
select product_attr_val_disp, product_attr_value, operand from qp_list_lines_v where list_header_id=XXXX and
product_attr_value in (select distinct ordered_item_id from oe_order_lines_all
where header_id in ('XXXXX') and unit_selling_price is null);

begin
for sp in stylprc loop
begin
update oe_order_lines_all set unit_selling_price=sp.operand, unit_list_price=sp.operand where header_id in ('XXXXX')
and ordered_item_id=sp.product_attr_value and unit_selling_price is null;
end;
end loop;
end;
*********************************************************************************


-- Query to get ship to details for an order.

SELECT h.orig_sys_document_ref,
h.customer_number,
l.inventory_item_id,
l.inventory_item,
h.SHIP_TO_ADDRESS1,
h.SHIP_TO_ADDRESS2,
h.SHIP_TO_ADDRESS3,
h.SHIP_TO_ADDRESS4
FROM oe_headers_iface_all h,oe_lines_iface_all l
where h.orig_sys_document_ref =l.orig_sys_document_ref
and h.orig_sys_document_ref = 'xxxx';

*********************************************************************************

---Query to get duplicate records for a customer in interface

SELECT distinct ''''||cust_po_number||''''||',',customer_name
  FROM oe_order_headers_all l,ra_customers r
WHERE l.sold_to_org_id =r.customer_id
and cust_po_number IN (SELECT orig_sys_document_ref
                                   FROM oe_headers_iface_all                              
                                   WHERE customer_number = xxxx) ;

To Find RESPONSIBILITY_KEY using BASEPATH or APPLICATION_SHORT_NAME

SELECT r.responsibility_key,
  fa.APPLICATION_SHORT_NAME
FROM fnd_responsibility r,
  fnd_application_tl a,
  fnd_application fa
WHERE fa.APPLICATION_ID = a.APPLICATION_ID
AND r.application_id    = a.application_id
AND fa.BASEPATH         ='ONT_TOP';


******************************************************************************

QUERY TO CHECK, UPDATE START AND END DATE FOR AN ORDER IN ORACLE(uploaded order)

select r.customer_name,h.cust_po_number, 'PO# '||h.cust_po_number ,'SO# '||h.order_number,h.booked_flag,''''||h.header_id||''''||',',to_char(h.creation_date,'MM-DD-YYYY HH:MI:SS')from oe_order_headers_all h, ra_customers rwhere h.sold_to_org_id = r.customer_idand h.open_flag='Y'--and to_char(h.creation_date,'MMDDYYYY')='MMDDYYYY'-- > sysdate-0.5and h.orig_sys_document_ref like '%XXXX%'--and r.customer_name like'JEW%';--and r.customer_number in(XXX)order by h.creation_date desc;

update oe_order_headers_allset request_date='28-OCT-11'where header_id in ('HEADER_ID');update oe_order_headers_allset attribute2='04-NOV-11'where header_id in ('HEADER_ID');update oe_order_lines_allset request_date='28-OCT-11', SCHEDULE_SHIP_DATE ='28-OCT-11',PROMISE_DATE='28-OCT-11'where header_id in ('HEADER_ID');
update oe_order_lines_allset attribute2='04-NOV-11'where header_id in ('HEADER_ID');


select h.attribute2 hd,l.attribute2 ld,l.request_date,l.SCHEDULE_SHIP_DATE,l.PROMISE_DATE from oe_order_headers_all h,oe_order_lines_all lwhere h.header_id=l.header_idand h.open_flag='Y'and  h.cust_po_number in ('xxxxx');

Sql Querys regarding Order Management

-- Query to check all orders uploaded  with details

select h.price_list_id,r.customer_number,r.customer_name,h.sold_to_org_id,h.cust_po_number, 'PO# '||h.cust_po_number ,'SO# '||h.order_number,h.attribute1,h.attribute10
,h.booked_flag,''''||h.header_id||''''||',',to_char(h.creation_date,'MM-DD-YYYY HH:MI:SS')
from oe_order_headers_all h, ra_customers r
where h.sold_to_org_id = r.customer_id
and to_char(h.creation_date,'MMDDYYYY')='05112016'-- >
--and h.orig_sys_document_ref like ' '
--and r.customer_name like' ';
--and h.open_flag='Y'
--and h.booked_flag='N'
--and r.customer_number in( )
--and h.attribute10 is null
order by h.creation_date desc;

--Query to check Interface Details before uploading into Oracle 

select rc.customer_number,rc.customer_name,h.error_flag, count(orig_sys_document_ref)
from oe_headers_iface_all h, ra_customers rc
where
--sold_to_org_id=1191 and
--h.creation_date >'17-jan-09'
h.customer_number=rc.customer_number
--and h.error_flag is null
--and h.error_flag ='Y'
group by rc.customer_name,rc.customer_number,rc.customer_id,h.error_flag
order by rc.customer_name;