, ,

Operating Unit name based on the Operating Unit ID

In the following Query you can get Operating Unit Name based on the Operating Unit ID.

select Business_group_id, Organization_id, name,date_from,date_to, legal_entity_id, set_of_books_id from hr_operating_units where organization_id = ‘your Operating Unit ID’;
/by
, ,

Concurrent program name based on the request ID

With the following query, you can get the concurrent program name based on the Concurrent request ID

Select program from FND_CONC_REQ_SUMMARY_V
where request_id = ‘Your request ID’;
/by
, , ,

FORALL

FORALL concept help us reducing the Iterations between PL/SQL Block and SQL Engine.

Note:- When ever, we excute any DML (INSERT or UPDATE or DELETE) Operations in the PL/SQL block then, It will move from PL/SQL Block to SQL Engine to excute the DML Operation in SQL Engine.

If we have some LOOP and it is looping for 100 times then, this will iterate from 100 times from PL/SQL block to SQL Engine.

Example:-

CREATE TABLE emp_by_dept AS SELECT employee_id, department_id
FROM employees WHERE 1 = 0;
DECLARE
TYPE dept_tab IS TABLE OF departments.department_id%TYPE;
deptnums dept_tab;
TYPE NumList IS TABLE OF NUMBER;
— The zeros in this list will cause divide-by-zero errors.
num_tab NumList := NumList(10,0,11,12,30,0,20,199,2,0,9,1);
errors NUMBER;
dml_errors EXCEPTION;
PRAGMA exception_init(dml_errors, -24381);
BEGIN
SELECT department_id BULK COLLECT INTO deptnums FROM departments;
— SAVE EXCEPTIONS means don’t stop if some INSERT fail.
FORALL i IN 1..deptnums.COUNT SAVE EXCEPTIONS
INSERT INTO emp_by_dept
SELECT employee_id, department_id FROM employees
WHERE department_id = deptnums(i);
FOR i IN 1..deptnums.COUNT LOOP
— Count how many rows were inserted for each department; that is,
— how many employees are in each department.
dbms_output.put_line(‘Dept ‘||deptnums(i)||’: inserted ‘||
SQL%BULK_ROWCOUNT(i)||’ records’);
END LOOP;
dbms_output.put_line(‘Total records inserted =’ || SQL%ROWCOUNT);
— If any errors occurred during the FORALL SAVE EXCEPTIONS,
— a single exception is raised when the statement completes.
EXCEPTION
WHEN dml_errors THEN — Now we figure out what failed and why.
errors := SQL%BULK_EXCEPTIONS.COUNT;
dbms_output.put_line(‘Number of INSERT statements that failed: ‘ || errors);
FOR i IN 1..errors LOOP
dbms_output.put_line(‘Error #’ || i || ‘ occurred during ‘||
‘iteration #’ || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX);
dbms_output.put_line(‘Error message is ‘ ||
SQLERRM(-SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
END LOOP;
END;
/by
, , , ,

BULK COLLECT

Bulk Collect concept help us reducing the Iterations between SQL Engine and PL/SQL Block.

Note:- When ever, we excute any DML (INSERT or UPDATE or DELETE) Operations in the PL/SQL block then It will move from PL/SQL Block to SQL Engine to excute the DML Operation in SQL Engine. And if we have some SELECT statement in the PL/SQL Block, then it will move from SQL Enginer to get the data from SQL Enginer to the PL/SQL Block. For the SELECT statement, we use the BULK COLLECT to record the Iteration from the SQL Enginer to the PL/SQL Block.

If I have to put my self in other word then, I should say, BULK COLLECT will process group of SELECT statements at one short.

Similarly for the DML Operations, we use FORALL Concept, please refer the FORALL Concept for more details.

Note:- This Example is based on the EMP table in the SCOTT schema. Here say, EMP table have 90 Records and if we do it in the Normal way (Without BULK COLLECT concept, then It will iterate SQL enginer to the PL/SQL Block for 90 Times). By using BULK COLLECT, we can completly reduce the Iterations.

In the following Example, I have limited to 10, so for 90 records, this will iterate for 9 times.

Example:-
———–

declare
type array is table of number index by binary_integer;
l_data array;
cursor c is select empno from emp;
begin
open c;
loop
fetch c bulk collect into l_data limit 10;
if ( c%notfound )
then
dbms_output.put_line
( ‘Cursor returned NOT FOUND but array has ‘ || l_data.count
|| ‘ left to process’ );
else
dbms_output.put_line
( ‘We have ‘ || l_data.count
|| ‘ to process’ );
end if;
exit when c%notfound;
end loop;
close c;
end;

Returning clause with the Bulk collect with the DML opeartions:-
—————————————————————————

DELETE FROM emp WHERE num = 30
RETURNING empno, ename BULK COLLECT INTO p_num, p_ename;
/by
, , ,

Registering Executable, Concurrent Program etc from backend

For Registering the Executable from backend.

PROMPT Creating Concurrent Executable XXM_XYZ_EMPLOYEE ……
PROMPT

BEGIN
FND_PROGRAM.executable(‘XXM_XYZ_EMPLOYEE’ — executable
, ‘ABC’ — application
, ‘ABC_XYZ_EMPLOYEE’ — short_name
, ‘Executable for Migrating Employee’ — description
, ‘PL/SQL Stored Procedure’ — execution_method
, ‘abc_xyz_employee_pkg.create_employee’ — execution_file_name
, ” — subroutine_name
, ” — icon_name
, ‘US’ — language_code
, ”); 

END;
/

For Registering the Concurrent program for the Executable file created.

PROMPT Creating Concurrent Program ABC_XYZ_EMPLOYEE …
PROMPT

BEGIN
FND_PROGRAM.register(‘ABC Data migration program for HR-Employee’ — program
, ‘XXM’ — application
, ‘Y’ — enabled
, ‘ABC_XYZ_EMPLOYEE’ — short_name
, ‘Data Migration Program for Migrating HR-Employee’ — description
, ‘ABC_XYZ_EMPLOYEE’ — executable_short_name
, ‘XYZ’ — executable_application
, ” — execution_options
, ” — priority
, ‘Y’ — save_output
, ‘Y’ — print
, ” — cols
, ” — rows
, ” — style
, ‘N’ — style_required
, ” — printer
, ” — request_type
, ” — request_type_application
, ‘Y’ — use_in_srs
, ‘N’ — allow_disabled_values
, ‘N’ — run_alone
, ‘TEXT’ — output_type
, ‘N’ — enable_trace
, ‘Y’ — restart
, ‘Y’ — nls_compliant
, ” — icon_name
, ‘US’); — language_code
END;
/

For attaching the concurrent program to the request group.

PROMPT Adding Concurrent program to request group ‘XYZ Group’
PROMPT

BEGIN
FND_PROGRAM.add_to_group(‘XYZ_ABC_EMPLOYEE’ — program_short_name
, ‘XYZ’ — application
, ‘XYZ Group’ — Report Group Name
, ‘XYZ’); — Report Group Application

END;
/

Submit the Concurrent Program from Backend.
Submit Program is the Function. where. we need to pass the Application name, Program, stage etc.

function FND_SUBMIT.SUBMIT_PROGRAM
(application IN varchar2,
program IN varchar2,
stage IN varchar2,
argument1,…argument100)
return boolean;

PROMPT Submitting the Concurrent Program from backend.
PROMPT

BEGIN
–fnd_global.apps_initialize( user_id => , resp_id => , resp_appl_id => );

l_success := fnd_submit.set_request_set(‘‘, ‘‘);

IF l_success = TRUE
THEN

l_success := fnd_submit.submit_program(
‘‘ –Application
,’‘ — program
,’‘ –Stage
,’NEW’ — Arument1
,’10’ -Arument2
,’N’
,’N’
,’N’);

— If fail then for the log record.

IF (NOT l_success)
THEN
fnd_file.put_line(
fnd_file.LOG
,’Request submission of stage STAGE FAILED’);
ELSE
fnd_file.put_line(
fnd_file.LOG
,’Request submission of stage STAGE10 SUCCESSFUL’);
END IF;
l_req_id := fnd_submit.submit_set(NULL, FALSE );

IF (l_req_id <= 0) THEN fnd_file.put_line( fnd_file.LOG ,’REQUEST SET SUBMISSION FAILED’); ELSE fnd_file.put_line( fnd_file.LOG ,’REQUEST SET SUBMITTED SUCCESSFULLY: ‘ || l_req_id); END IF; END IF; END;

I hope the above document is helpful to all of you for understanding the backend process and API’s to register the program and submit from the backend.

/by