What are built-ins used for Processing rows

GET_GROUP_ROW_COUNT(function)
GET_GROUP_SELECTION_COUNT(function)
GET_GROUP_SELECTION(function)
RESET_GROUP_SELECTION(procedure)
SET_GROUP_SELECTION(procedure)
UNSET_GROUP_SELECTION(procedure)

What are the built-ins used for Getting cell values

GET_GROUP_CHAR_CELL (function)
GET_GROUP_DATE_CELL(function)
GET_GROUP_NUMBET_CELL(function)

How to create Materialized Views in Oracle 12

There are two steps to create Materialized Views in 12.2

1.    Create Logical View

2.    Upgrade to Materialized View

Create Logical View
CREATE OR REPLACE VIEW <Custom Schema>.XXXXX_CREATE_CUSTOMER_MV#
AS
<SELECT Statement>;


Upgrade Logical View to Materialized View
exec ad_zd_mview.upgrade('<custom schema>', 'XXXXX_CREATE_CUSTOMER_MV');

How to check if the Materialized Views got created properly in 12.2

SELECT object_type, object_name
FROM dba_objects
WHERE object_name like 'XXXXX_CREATE_CUSTOMER_MV%';

-- 4 rows Selected

These 4 Rows get created in the process for

1. Logical View (Ending with '#') in Custom Schema
2. Materialized View in Custom Schema
3. Table in Custom Schema
4. Synonym in APPS

How to Refresh Materialized Views:


exec DBMS_MVIEW.REFRESH('XXXXX_CREATE_CUSTOMER_MV', method => '?',  atomic_refresh => FALSE, out_of_place => TRUE);

Parameters in detail:

Refresh Method	Parameter	Description
COMPLETE	C	Refreshes by recalculating the defining query of the materialized view.
FAST	F	Refreshes by incrementally applying changes to the materialized view.
		For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log-based FAST and FAST_PCT.
FAST_PCT	P	Refreshes by recomputing the rows in the materialized view affected by changed partitions in the detail tables.
FORCE	?	Attempts a fast refresh. If that is not possible, it does a complete refresh. For local materialized views, it chooses the refresh method which is estimated by optimizer to be most efficient. The refresh methods considered are log based FAST, FAST_PCT, and COMPLETE.

Which event will be fire while Booking Sales Orders

When user press the Book button, two predefined event will be triggered.

These event can be captured by using Forms Personalization and CUSTOM.pll.

OM_PRE_BOOK_EVENT: Validation before booking the order.

OM_POST_BOOK_EVENT: some process once order is booked.

Script to generate .csv file from oracle pl sql

DECLARE

CURSOR C1 IS
SELECT e.empno
, e.ename
, TO_CHAR(e.hiredate,'DD-MON-YYYY') hiredate
, e.sal
, e.deptno
, d.dname
  FROM emp e
, dept d
 WHERE e.deptno = d.deptno;

vfile_handle UTL_FILE.file_type;
v_directory     VARCHAR2 (2000);
v_file VARCHAR2 (240);

BEGIN
dbms_output.put_line( 'Program Start');

vfile_handle := UTL_FILE.FOPEN('/usr/tmp/LR12DEV2','Emp_Data.csv','W');

UTL_FILE.PUT_LINE(vfile_handle,''
|| ',' ||''
|| ',' ||'Revenue Amortizations Functional Report'
);


UTL_FILE.NEW_LINE(vfile_handle);
UTL_FILE.NEW_LINE(vfile_handle);

UTL_FILE.PUT_LINE(vfile_handle,
  'EMPNO'   
  || ',' ||'ENAME'   
  || ',' ||'HIREDATE'
  || ',' ||'SAL'     
  || ',' ||'DEPTNO'
  || ',' ||'DNAME'
  );
 
FOR rec IN c1
LOOP
dbms_output.put_line( 'Loop Start- Empno'||rec.empno);

UTL_FILE.PUT_LINE(vfile_handle,
                      '"'||rec.empno||'"' 
  || ',' ||'"'||rec.ENAME||'"'
  || ',' ||'"'||rec.HIREDATE||'"'     
  || ',' ||'"'||rec.SAL||'"'     
  || ',' ||'"'|| rec.DEPTNO||'"' 
  || ',' ||'"'||rec.DNAME||'"'
  );
END LOOP;

UTL_FILE.FCLOSE(vfile_handle);

dbms_output.put_line( 'Program End');

EXCEPTION WHEN OTHERS THEN
dbms_output.put_line('Program Exception:'||SQLERRM);
END;
/


Sample output
------------------------------------------------------------------
,,Revenue Amortizations Functional Report


EMPNO,ENAME,HIREDATE,SAL,DEPTNO,DNAME
"7782","CLARK","09-JUN-1981","2450","10","ACCOUNTING"
"7934","MILLER","23-JAN-1982","1300","10","ACCOUNTING"
"7839","KING","17-NOV-1981","5000","10","ACCOUNTING"
"7902","FORD","03-DEC-1981","3000","20","RESEARCH"
"7788","SCOTT","09-DEC-1982","3000","20","RESEARCH"
"7566","JONES","02-APR-1981","2975","20","RESEARCH"
"7369","SMITH","17-DEC-1980","800","20","RESEARCH"
"7876","ADAMS","12-JAN-1983","1100","20","RESEARCH"
"7521","WARD","22-FEB-1981","1250","30","SALES"
"7654","MARTIN","28-SEP-1981","1250","30","SALES"
"7844","TURNER","08-SEP-1981","1500","30","SALES"
"7900","JAMES","03-DEC-1981","950","30","SALES"
"7499","ALLEN","20-FEB-1981","1600","30","SALES"
"7698","BLAKE","01-MAY-1981","2850","30","SALES"

PDE-PLI018 Could Not Find Library when Opening CUSTOM.pll in Form Builder

1. Create new folder in your local machine and Copy all .pll from $AU_TOP/resource.

2. Go to system registry by using REGEDIT

 Navigate to path HKEY_LOCAL_MACHINE/SOFTWARE/WOW6432Node/Oracle/<Key for your ORACLE_HOME>/FORMS_PATH

3. Add library directory into FORMS_PATH

  i.e. C:\DevSuiteHome_1\cgenf61\admin;C:\DevSuiteHome_1\forms;C:\pll_files

4. Re-start Forms Builder and test it again.

Query to find the current program request id

select fnd_global.conc_request_id from dual;