Uncategorized

Move order header different status and their meaning

select lookup_code, substr(meaning, 1, 60) “Meaning”
from mfg_lookups
where lookup_type = ‘MTL_TXN_REQUEST_STATUS’
order by lookup_code

1 = Incomplete
2 = Pending Approval
3 = Approved
4 = Not Approved
5 = Closed
6 = Cancelled
7 = Pre-Approved
8 = Partially Approved
9 = Cancelled by Source

Advertisements

CREATE LOOKUP FOR PERSONALIZATION THERES I AM ENABLE AND DISABLE PERSONALIZATION

CREATE LOOKUP FOR PERSONALIZATION THERES I AM ENABLE AND DISABLE PERSONALIZATION

Untitled.jpg

 

THIS IS THE FUNCTION THEY GIVE THE CONDITION :—–à

 

CREATE OR REPLACE function xxc11_personalization_enabled(p_personalization_code VARCHAR2)

return varchar2

is

l_return varchar2(1);

begin

 

select decode(count(1),0,’N’,’Y’)

into l_return

from fnd_lookup_values

where lookup_type=’XXC11_PERSONALIZATION_LIST’

and lookup_code = p_personalization_code

and nvl(enabled_flag,’N’)=’Y’;

return l_return;

end;

IN THE PERSONALIZATION CONDITION IS :———–à

xxc11_personalization_enabled(‘XXC11_PUR001′)=’Y’

Instead of Trigger

Instead of Triggers:-
With the help of INSTEAD OF TRIGGER we perform DML on complex view.

Examples:-

CREATE OR REPLACE VIEW XXC05_EMP_DETAIL1
AS
(
SELECT E.EMPLOYEE_ID,
E.SALARY,
MAX(D.DEPARTMENT_ID)
FROM EMPLOYEES E,
DEPARTMENTS D,
LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
GROUP BY (E.EMPLOYEE_ID,SALARY)
);

create trigger XXC05_INSTEAD_OF_TRI
instead of update on XXC05_EMP_DETAIL1
begin
update employees
set employee_id = :new.employee_id
where employee_id = 198;
end XXC05_INSTEAD_OF_TRI;

UPDATE XXC05_EMP_DETAIL1
SET SALARY = SALARY+(SALARY*COMMISSION_PCT)
WHERE EMPLOYEE_ID = 198;

When we execute update command it update view data.

Set Operator in SQL

Set Operator:-

              Set Operators combine the result of two or more component queries into a single result. Queries containing set operators are called compound queries.

 

1. UNION

2. MINUS

3. UNION ALL

4. INTERSECT

 

Table EMP

 

ID    NAME    SALARY

1    AMAL    80000

2    SAJAL    20000

3    SEEMA    50000

4    SUSHIL    90000

 

Table EMP_DETAIL

 

ID    NAME    SALARY

1    AMAL    80000

2    NAVAL    10000

3    SEEMA    50000

4    ANKIT    60000

5   AYUSH    30000

 

 

UNION:-

        All distinct rows selected by either query. In UNION operator data is sorted in ascending order according to first column of first query.

 

Example:-

        SELECT ID,NAME,SALARY

        FROM EMP

        union

        SELECT ID,NAME,SALARY

        FROM EMP_DETAIL;

 

Output:-

 

ID    NAME    SALARY

1    AMAL    80000

2    SAJAL    20000

2    NAVAL    10000

3    SEEMA    50000

4    SUSHIL    90000

4    ANKIT    60000

5   AYUSH    30000

 

 

MINUS:-

        All distinct rows that are selected by the first SELECT statement and not selected in the second SELECT statement.

 

Example:-

        SELECT ID,NAME,SALARY

        FROM EMP

        minus

        SELECT ID,NAME,SALARY

        FROM EMP_DETAIL;

 

Output:-

 

ID    NAME    SALARY

2    SAJAL    20000

4    SUSHIL    90000

       

UNION ALL:-

            All rows selected by either queries, including all duplicates.

 

Example:-

        SELECT ID,NAME,SALARY

        FROM EMP

        union all

        SELECT ID,NAME,SALARY

        FROM EMP_DETAIL;

       

Output:-

 

ID    NAME    SALARY

1    AMAL    80000

2    SAJAL    20000

3    SEEMA    50000

4    SUSHIL    90000

1    AMAL    80000

2    NAVAL    10000

3    SEEMA    50000

4    ANKIT    60000

5   AYUSH    30000

           

INTERSECT :-

            All distinct rows selected by both queries.

           

Example:-

        SELECT ID,NAME,SALARY

        FROM EMP

        intersect

        SELECT ID,NAME,SALARY

        FROM EMP_DETAIL;

               

Output:-

ID    NAME    SALARY

1    AMAL    80000

3    SEEMA    50000