Open Oracle Concurrent Program Output Directly on WEB browser through Oracle Forms

–Add library on form

FNDCONC.pll

 

— Create CP

CREATE OR REPLACE PROCEDURE XXX_CALL_CP(errbuf out varchar2,retcode out varchar2)

is

begin

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,’ASASASSASASAS ASASASASASASAS test’);

end;

 

— Create Procedure on form and call it on when-button-pressed

PROCEDURE CALL_CP IS

vlrequestid NUMBER := 0;

vlrphase VARCHAR2 (30);

vlrstatus VARCHAR2 (30);

vldphase VARCHAR2 (30);

vldstatus VARCHAR2 (30);

vlmessage VARCHAR2 (30);

vlwaitstatus BOOLEAN;

l_PICKING_RULE_ID NUMBER;

 

l_user_id NUMBER := -1;

l_resp_id NUMBER := -1;

l_application_id NUMBER := -1;

 

l_req_id NUMBER;

l_request_completed BOOLEAN := FALSE;

 

l_req_phase VARCHAR2(20);

l_req_status VARCHAR2(1000);

l_req_dev_phase VARCHAR2(1000);

l_req_dev_status VARCHAR2(1000);

l_req_message VARCHAR2(1000);

 

l_x varchar2(1);

l_y varchar2(1);

l_z varchar2(1);

l_a varchar2(1);

l_b varchar2(1);

 

L_PICK_RELEASE VARCHAR2(1);

 

BEGIN

l_user_id := fnd_global.user_id;

l_application_id := fnd_global.resp_appl_id;

l_resp_id := fnd_global.resp_id;

fnd_global.apps_initialize(l_user_id, l_resp_id, l_application_id);

 

l_req_id := fnd_request.submit_request (‘XXX’,

‘XXX_CALL_CP’,

NULL,

SYSDATE,

FALSE);

 

:System.Message_Level := ’25’;

commit;

l_request_completed := fnd_concurrent.wait_for_request(request_id =>

l_req_id

,INTERVAL => 1

,phase => l_req_phase

,status => l_req_status

,dev_phase => l_req_dev_phase

,dev_status => l_req_dev_status

,MESSAGE => l_req_message);

 

:System.Message_Level := ’25’;

COMMIT;

editor_pkg.report(l_req_id, ‘Y’);

 

END CALL_CP;

 

 

— WHEN-BUTTON-PRESSED

CALL_CP;

Advertisements

Standard API of Rice Components

1. FND_PROGRAM.EXECUTABLE();

2. FND_PROGRAM.DELETE_EXECUTABLE();

3. FND_PROGRAM.REGISTER();

4. FND_PROGRAM.DELETE_PROGRAM();

5. FND_PROGRAM.PARAMETER();

6. FND_PROGRAM.DELETE_PARAMETER();

7. FND_PROGRAM.INCOMPATIBILITY();

 

8. FND_PROGRAM.DELETE_INCOMPATIBILITY();

9. FND_PROGRAM.REQUEST_GROUP();

10. FND_PROGRAM.DELETE_GROUP();

11. FND_PROGRAM.ADD_TO_GROUP();

12. FND_PROGRAM.REMOVE_FROM_GROUP();

13. FND_REQUEST.SUBMIT_REQUEST();

14. FND_CONCURRENT.WAIT_FOR_REQUEST();

15. FND_PROFILE.VALUE();

16. FND_PROFILE.SAVE();

17. FND_PROFILE.GET();

18. FND_MESSAGE.SET_NAME();

19. FND_MESSAGE.CLEAR();

20. FND_MESSAGE.GET();

21. FND_MESSAGE.SET_TOKEN();

22. FND_MESSAGE.RAISE_ERROR();

23. FND_MESSAGE.GET_STRING();

24. FND_MESSAGE.SET_NAME();

25. FND_MESSAGE.PUT();

26. FND_GLOBAL.APPS_INITIALIZE()

27. FND_GLOBAL.LOGIN_ID();

28. FND_GLOBAL.APPLICATION_ID();

29. FND_GLOBAL.USER_ID();

30. FND_GLOBAL.ORG_ID();

31. FND_GLOBAL.CONC_PROGRAM_ID();

32. FND_GLOBAL.CONC_REQUEST_ID();

33. FND_USER_PKG.CREATEUSER();

34. FND_USER_PKG.UPDATEUSER();

35. FND_USER_PKG.DISABLEUSER();

How many execution methods in Executable?

Ques:- How many execution methods in Executable?

Ans:- There are 12 execution methods in executable.

1.  Host

2.  PL/SQL Stored procedure

3.  Oracle Reports

4.  SQL *Loader

5.  SQL *Plus

6.  Java Stored Procedure

7.  Java Concurrent Program

8.  Immediate

9.  Multi Language Function

10. Spawned

11.  Request Set Stage Function

12.  Perl  Concurrent Program

How to Create a Concurrent Program in Oracle Apps.

Now I discuss with you How to Create a Concurrent Program in Oracle Apps.

We Create a concurrent program which execution method is PL/SQL Stored Procedure.

Step 1:- First we create a stored procedure in database.

Ex:-

CREATE OR REPLACE PROCEDURE XXC_HELLO_PROC

(

ERRBUFF OUT VARCHAR2

, RETCODE OUT VARCHAR2

)

IS

BEGIN

FND_FILE.PUT_LINE(FND_FILE.OUTPUT,’HELLO ORACLE’);

END XXC_HELLO_PROC;

ERRBUFF and RETCODE are the mendetory parameter.

Compile that procedure in database.

Step 2:- Now we create login Oracle Apps.

Step 3:- Select the Application Developer Responsibility.

Step 4:- Open the Executable Form

Navigation :- Application Developer Responsibility -> Concurrent -> Executable

Step 5:-

a)  Give the name of Executable(Which is Display for User).

b)  Give the short name of Executable(for Back end).

c)  Chose Application Name in which we want to register these Executable(we select “Work in Process”).

d)  Give Description(Its is not mandatory field).

e)  Chose Execution method Name from LOV. (we select  PL/SQL Stored Procedure)

f)  Give the procedure name in Execution File Name field.

g) Save it.

Step 6:- Open the Concurrent Program Form

 

Navigation :- Application Developer Responsibility -> Concurrent -> Program

Step 7:-

a) Give the Concurrent Program Name(Which is Display for User).

b) Give the short name of Program(for Back end).

c) Chose Execution method Name from LOV. (we select “Work in Process”)

d) Give the Executable Short name in Executable name,which is created in above step.

e) Save it.

Step 8:- Go to System Administrator Responsibility

Navigation :- System Administrator Responsibility -> Security -> Responsibility -> Request

Step 9:- Attach those Concurrent program in your request group.

a) In name field give the concurrent program name and save it.

Step 10:- Select our responsibility which is assign on our user.

Step 11:- Now we submit our Concurrent program.

 

Click on View from menu bar then click on request.

Click on Submit a New Request button,select Single Request then press Ok button.

Give Concurrent program Name, press Submit Button.Press No and find.

Now we see our concurrent program in SRS window.

Step 12:- See Output

Click on View output button and see Output “HELLO ORACLE”.

Launch Workflow from OAF Page

Step 1:- Create a workspace and Project.

Workspace

Name:- Launch_WorkflowWorkspace.jws

Project

Name:- Launch _WorkflowProject

Package:- XXC05.oracle.apps.wip. Launch_Workflow

Step 2:- Create a Application Module (AM)

Name:- Launch_WorkflowAM

Package:- XXC05.oracle.apps.wip. Launch_Workflow.server

Step 3:- Create a New Page

Name:- Launch_WorkflowPG

Package:- XXC05.oracle.apps.wip. Launch_Workflow.webui

Step 4:- Select region1 and set the following properties:

                ID:-  PageLayoutRN

Region Style:- pageLayout

Window Title:- Launch Workflow Window

Title:- Launch Workflow Title

AM Defination:- XXC05.oracle.apps.wip. Launch_Workflow.server. Launch_WorkflowAM

Step 5:- Add new Region in PageLayoutRN

ID:- MainRN

Region Style:- messageComponentLayout

Step 6:- Create other Region into MainRN(messageLayout)

ID:- ButtonLayout

Step 7:- Create new item into ButtonLayout

ID:- Launch_Workflow_OAF

Region Style:- SubmitButton

Attribute Set:- /oracle/apps/fnd/attributesets/Buttons/Go

Prompt:- Launch Workflow

Step 8:- Create a CO

Name:- Launch_WorkflowCO

Package:- XXC05.oracle.apps.wip. Launch_Workflow.webui

Step 9:- Write That code in Launch_WorkflowCO ( ProcessFormRequest)

if (pageContext.getParameter(“Launch_Workflow_OAF “) != null)

{

LaunchWorkFlow(pageContext);

}

———————————————————————————————————

public void LaunchWorkFlow(OAPageContext pageContext)

{

String strWfItemType = “XC05_WF2”;     // Workflow item Name

String strWfProcess = “XXC05_PROC”;   // Workflow process Name

String strWfItemKey = “Practice-1”;      // Workflow Item Key value

OANavigation wfNavigation = new OANavigation();

// Create Workflow Process

wfNavigation.createProcess(pageContext, strWfItemType, strWfProcess, strWfItemKey);

// Start Workflow Process

wfNavigation.startProcess(pageContext, strWfItemType, strWfProcess, strWfItemKey);

}

Page is created. Now you run page…..

Comparison and Logical Condition in SQL

Comparison Condition:-

Comparison conditions are used in conditions that compare one expression to another value or expression. These operator are used in WHERE condition.

Comparison Operator:-

 

1. Equal to (=):-

SELECT *

FROM EMPLOYEES

WHERE EMPLOYEE_ID = 100;

 

2. GREATER THAN (>):-

SELECT *

FROM EMPLOYEES

WHERE SALARY > 4000;

 

3. LESS THAN (<):-

SELECT *

FROM EMPLOYEES

WHERE SALARY < 5000;

 

4. GREATER THAT OR EQUAL TO (>=)

SELECT *

FROM EMPLOYEES

WHERE SALARY >= 4000;

 

5. LESS THAN OR EQUAL TO (<=):-

SELECT *

FROM EMPLOYEES

WHERE SALARY <= 5000;

 

6. NOT EQUAL TO (<>):-

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID <> 50;

 

 

Others Comparison Operator:-

1. BETWEEN .. AND ..:- Between two value(include)

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID BETWEEN (200 AND 300);

 

2. IN:- Match any of the list of values

SELECT *

FROM EMPLOYEES

WHERE EMPLOYEE_ID IN(101, 110, 200, 220, 109);

3. LIKE:- Match a character pattern.

SELECT *

FROM EMPLOYEES

WHERE LAST_NAME LIKE ‘%A%’;

 

4. IS NULL:- Is a null value.

SELECT *

FROM EMPLOYEES

WHERE COMMISSION_PCT IS NULL;

 

 

Logical Condition:-

1. AND:- If both compare condition are TRUE then it return TRUE.

SELECT *

FROM EMPLOYEES

WHERE SALARY < 20000

AND DEPARTMENT_ID = 20;

2. OR:- If any one compare condition is TRUE then it return TRUE.

 

SELECT *

FROM EMPLOYEES

WHERE SALARY < 20000

OR DEPARTMENT_ID = 20;

 

3. NOT:- If condition is false then it return TRUE.

SELECT *

FROM EMPLOYEES

WHERE DEPARTMENT_ID NOT IN(10,50);

 

 

 

Sql, PL/SQL Special Query

Ques:-

TEAM RESULT

A        WIN

B        WIN

A        LOSS

A        WIN

B        DRAW

C        WIN

C        LOSS

D        LOSS

E        DRAW

EXPECTED OUTPUT

TEAM  WIN  LOSS DRAW

A       2    1    0

B       1    0    1

C       1    1    0

D       0    1    0

E       0    0    1

ANS:-

select TEAM,SUM(CASE RESULT when ‘WIN’ then 1

else 0 end) WIN

,SUM(CASE RESULT when ‘LOSS’ then 1

else 0 end) LOSS

,SUM(CASE RESULT when ‘DRAW’ then 1

else 0 end) DRAW

,SUM((CASE RESULT when ‘WIN’ then 1 else 0 end)+(CASE RESULT when ‘LOSS’ then 1 else 0 end)+(CASE RESULT when ‘DRAW’ then 1 else 0 end)) TOTAL

from xxc05_test1

GROUP BY TEAM

order by 1

 

 

———————————————————————-

 

Ques:- Display the salary of each and every employee in 1991,1992 and 1993.

(hint: use ‘DECODE’ function)

Zigzag

NAME YEAR ATM_NO

————- ———– —————–

JOHN 1991 1000

JOHN 1992 2000

JOHN 1993 3000

JACK 1991 1500

JACK 1992 1200

JACK 1993 1340

MARY 1991 1250

MARY 1992 2323

MARY 1993 8700

EXPECTED OUTPUT

YEAR JOHN JACK MARY

———— ———- ———- ———-

1991 1000 1500 1250

1992 2000 1200 2323

1993 3000 1340 8700

Ans:-

SELECT YEAR,SUM(DECODE(NAME,’JOHN’,ATM_NO,0)) JOHN

,SUM(DECODE(NAME,’JACK’,ATM_NO,0)) JACK

,SUM(DECODE(NAME,’MERRY’,ATM_NO,0)) MERRY

FROM XXC05_TEST2

GROUP BY YEAR

ORDER BY 1

———————————————————————-

Ques:-

Write a Query to display each letter of the world “Happy” in a separate row.

(Hint: use ‘SUBSTRING and CONNECT BY LEVEL’)

Answer table should be

Output

result

H

A

P

P

Y

Ans:-

select substr(‘HAPPY’,LEVEL,1) FROM DUAL

CONNECT BY LEVEL<=LENGTH(‘HAPPY’);

———————————————————————-

Ques:-

My table have some records

NAME    ACT_NO

SAJAL    1234567890

AMAL    2345678901

NAVAL    9876542211

but i want to print Like thet

******7890

******8901

******2211

Ans:-

SELECT LPAD(SUBSTR(ACT_NO,LENGTH(ACT_NO)-3),LENGTH(ACT_NO),’*’) FROM DUAL

———————————————————————-

Ques:- Convert number to cahr

Ans:-

select to_char(to_date(999999,’j’),’jsp’) from dual;

range is 1 to 5373484

———————————————————————-

Ques:-

Find Nth Highest Salary

Ans:-

select * from

EMPloyees x

where &no=(

select count(*)

from EMPloyees y

where y.employee_id>=x.employee_id

)

 

 

———————————————————————-

Ques:- Count the no of Male and Female Candidate in a table.

Ans:-

select SUM(case sex when ‘MALE’ then 1

end )”MALE”

,SUM(case sex when ‘FEMALE’ then 1

end)”FEMALE”

from XXC05_TEST3;

———————————————————————-

Ques:- how to insert a column in a table which is already existing in other table

First you add column name in second table then

Run that query.

DECLARE

BEGIN

FOR I IN (SELECT ID,DEPARTMENT_NAME FROM XXC05_T1)

LOOP

UPDATE XXC05_T2 SET NAME = I.DEPARTMENT_NAME

WHERE ID = I.ID;

END LOOP;

COMMIT;

END;

———————————————————————-

QUES:- HOW TO PRINT MAX,MIN,AVG,SUM OF SALARY AND PRINT ALL COLUMN NAME.

ANS:-

SELECT E.*,MAX(SALARY) OVER() MAX_SALARY ,MIN(SALARY) OVER() MIN_SALARY,ROUND(AVG(SALARY) OVER()) AVG_SALARY,SUM(SALARY) OVER() SUM_SALARY FROM EMPLOYEES E

or

SELECT

e.*,

(SELECT MAX(salary)

FROM Employees) MAXSAL,

(SELECT SUM(salary)

FROM Employees) SUMSAL

FROM Employees e;

———————————————————————-

Ques:-

HI FRND HOW TO REPLACE

col

a*b@c#d

o/p

a,b,c,d

ANS:-

select ‘a*b@c#d’,regexp_replace(‘a*b@c#d’, ‘[^0-9a-zA-Z]’, ‘,’ ) from dual;

or

SELECT translate(‘a*b@c#d’,’*@#’,’,,,’) FROM dual

or

select replace (replace (replace (‘a*b@c#d’,’*’,’,’),’@’,’,’),’#’,’,’) N from dual

———————————————————————-

Ques:- Without use rowid delete duplicate record.

ANS:-

select *

from (select e.empno, rank() over(order by e.deptno desc) rk

from scott.emp e)

———————————————————————-

Ques:- How to find greated, smallest number

Table is

c1 c2  c3

1    4    10

2    5    15

10    20    28

Ouput is 28,1

ANS:-

SELECT MAX(GREATEST(C1,C2,C3)) FROM T1;

Or

SELECT GREATEST(MAX(C1),MAX(C2), MAX(C3)) FROM T1

SELECT LEAST(MIN(C1),MIN(C2),MIN(C3)) FROM  T1;

Or

SELECT MIN(LEAST(C1,C2,C3)) FROM T1;

———————————————————————-

QUES:- hi…frd….suppose a number is 2642.how we get output 14 (2+6+4+2) by the use of sql.

ANS:- select sum(s) total from(

select substr(num,level,1) s from(select &g num from dual)

connect by level<=length(num))

 

 

SELECT SUBSTR(7654,1,1)+SUBSTR(7654,2,1)+SUBSTR(7654,3,1)+SUBSTR(7654,4,1) FROM DUAL

 

———————————————————————-

 

QUES:- HOW TO CHACK STRING IS PALINDROM OR NOT

DECLARE

LEN NUMBER;

PALSTR VARCHAR2(20) := ‘&PALSTR’;

CHKSTR VARCHAR2(20);

BEGIN

LEN := LENGTH(PALSTR);

FOR I IN REVERSE 1..LEN LOOP

CHKSTR := CHKSTR||SUBSTR(PALSTR,I,1);

END LOOP;

IF CHKSTR = PALSTR THEN

DBMS_OUTPUT.PUT_LINE(PALSTR||’ IS A PALINDROME’);

ELSE

DBMS_OUTPUT.PUT_LINE(PALSTR||’ IS NOT A PALINDROME’);

END IF;

END;

 

 

———————————————————————-

 

DIFFERENCE BETWEEN DATASET AND DATATABLE

DataSet:- DataSet is the Collection of DataTables

DataSet can Fetch multiple TablesRows at a time

In DataSet DataTable objects can be related to each other like(primary key, forign key, unique key etc)

 

DataTable:- DataTable is single datbase table

DataTable Fetch data from only one table.

As DataTable is a single database table, so there is no Data relation object in it.

 

———————————————————————-

QUES:-  How to find Those record in which and special symbol.

SELECT * FROM T11

WHERE REGEXP_LIKE(NAME,’%’);

OR

SELECT * FROM T11

WHERE NAME LIKE ‘%\%%’ ESCAPE ‘\’;

———————————————————————-

QUES:- Given String is   11223344556677 or give it at run time

output is:- 11**********77

Ans:-

SELECT RPAD(SUBSTR(&A,1,2),LENGTH(&&A)-2,’*’)||SUBSTR(&&A,-2,2) FROM DUAL

———————————————————————-

Ques:- In table data is

1  SAJAL     MALE

2  AMAL        MALE

3  MEENA    FEMALE

4  SONAM    FEMALE

5  RAHUL    MALE

6  SONIA    FEMALE

7  NANCY    FEMALE

8  NAVAL    MALE

My requirement is print record alternative of Gender

1  SAJAL     MALE

3  MEENA    FEMALE

2  AMAL        MALE

4  SONAM    FEMALE

5  RAHUL    MALE

6  SONIA    FEMALE

8  NAVAL    MALE

7  NANCY    FEMALE

ANS:-

select ROW_NUM,id,name,gender  from (

select (rownum*2) ROW_NUM,id,name,gender from XXC05_GENDER

where gender = ‘M’

union

select (rownum*2-1) ROW_NUM,id,name,gender from XXC05_GENDER

where gender = ‘F’

)

order by ROW_NUM

———————————————————————-

QUES:- Print 1 to 100 number in sql

ANS:-

Select Rownum

From dual

Connect By Rownum <= 100

OR

SELECT LEVEL

FROM DUAL

CONNECT BY LEVEL <= 100;