Uncategorized

How to make comments field mandatory for an approver in workflow notification

Add below function in Approval Notification in Oracle Workflow.

PROCEDURE comment_reject (
itemtype IN VARCHAR2,
itemkey IN VARCHAR2,
actid IN NUMBER,
funcmode IN VARCHAR2,
resultout IN OUT VARCHAR2
)
IS
l_notfication_result VARCHAR2 (1000);
l_sup_comments VARCHAR2 (10000);
BEGIN
IF (funcmode = ‘RESPOND’)
THEN
l_notfication_result :=
wf_notification.getattrtext (wf_engine.context_nid, ‘RESULT’);
l_comments :=
wf_notification.getattrtext (nid => wf_engine.context_nid,
aname => ‘COMMENTS’
);

IF (l_notfication_result = ‘REJECTED’)
THEN
IF l_comments IS NULL
THEN
raise_application_error
(‘-20002’,
‘Comments are mandatory while rejecting request’
);
ELSE
resultout := ‘COMPLETE:REJECT’;
END IF;
ELSIF (l_notfication_result = ‘APPROVED’)
THEN
resultout := ‘COMPLETE:APPROVE’;
END IF;

wf_engine.setitemattrtext (itemtype, itemkey, ‘COMMENTS’, l_comments);
END IF;
EXCEPTION
WHEN OTHERS
THEN
wf_core.CONTEXT (‘check_comment_reject’,
itemtype,
itemkey,
actid,
funcmode
);
END;

Set operators with examples

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

 

 

Different Types of Table in Oracle Apps

In oracle applications there are tables that ends with similar suffixes. Here I am trying to list the meaning of those, please provide your suggestion and help me if I have missed anything.

_B The Main base tables
_ALL Contains multi org data. There will be similar table without _ALL. Before querying this data the environment variable needs to be set. Dbms_application_info.set_client_info(‘org_id’), or apps_initialize can be used to set the environment variable.common column.
_V View created on base table. Mostly forms are created based on this views
_TL Tables that support multi language.
_VL View created on multi language tables. The view generally uses the base table and _tl table
_F This indicates that these are the date tracking tables. These tables are generally seen for HRMS and contain 2 common columns effective_start_date and effective_end_date
_S sequence related tables
_DFV /_KFV The DFF/KFF table created on the base table. This is the best way to get the concatenated value of DFF/KFF.
Also using this table the values can be queried based on the DFF/KFF name and not attributes column.
_X Current information table..there is no date tracking .

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