Tuesday, October 22, 2019

AP Payment Administrator PPR Genration

R12:AP/IBY Understanding XML Payment Templates and Formats (Doc ID 562806.1)

Sunday, October 20, 2019

Find OAF Pages Personalization SQL Query

--Find OAF Pages Personalization SQL Query

SELECT
jp.path_docid ,
jdr_mds_internal.getdocumentname(jp.path_docid) Personalization_path,
jp.path_name,
Jp.Path_Owner_Docid,
Jp.Path_Seq,
Jp.Path_Type,
Jp.Path_Xml_Encoding,
Jp.Path_Xml_Version,
Jp.Created_By,
Jp.Creation_Date,
Jp.Last_Updated_By,
Jp.Last_Update_Date
FROM apps.jdr_paths jp
WHERE jp.path_docid IN (
SELECT DISTINCT comp_docid
FROM jdr_components
WHERE
comp_seq = 0
AND comp_element = 'customization'
AND comp_id IS NULL
)
AND upper(jdr_mds_internal.getdocumentname(jp.path_docid) ) LIKE upper('%ViewExtBankAcctPG%')
--upper('%UpdateExtBankAcctPG%')--upper('%SuppSummPG%')

Sunday, October 13, 2019

Delete Workflow Business Event and Subscription API

--API To Delete the Workflow Business Events and Subscriptions

declare

cursor c_del_lines is
SELECT we.guid event_guid
, wes.guid subscription_guid
, wes.rule_function
FROM wf_events we
, wf_event_subscriptions wes
WHERE we.NAME = 'oracle.apps.wf.notification.respond'--give your event name
AND wes.event_filter_guid = we.guid;

begin

for i in c_del_lines loop
EXEC WF_EVENTS_PKG.DELETE_ROW(i.event_guid);

EXEC WF_EVENT_SUBSCRIPTIONS_PKG.DELETE_ROW(i.subscription_guid);

COMMIT;

end loop;

end;

Workflow Business Event and Subscription details SQL Query

/*Workflow Business Event and Subscription details SQL Query*/
SELECT we.guid event_guid,we.NAME, we.status event_status, wes.status subscription_status,
NVL (wes.phase, 0) subscription_phase,
wes.licensed_flag subscription_licensed_flag,
we.licensed_flag event_licensed_flag,
wes.guid subscription_guid,
wes.rule_function,
wes.on_error_code, wes.action_code, wes.customization_level,
wes.owner_name subscription_owner_name,
wes.owner_tag subscription_owner_tag,
wes.source_type subscription_source_type,
wes.rule_data subscription_rule_data, we.owner_name event_owner_name,
we.owner_tag event_owner_tag,
we.security_group_id event_security_group_id
FROM wf_events we, wf_event_subscriptions wes
WHERE 1 = 1
AND we.NAME IN
('oracle.apps.wf.notification.send',
'oracle.apps.wf.notification.respond',
'oracle.apps.wf.notification.reassign'
)
AND wes.event_filter_guid = we.guid

Sunday, August 25, 2019

Create .csv or .txt file on usr/tmp/ path by UTL_FILE package

--UTL_FILE Example Using Cursor
--Create .csv or .txt file on usr/tmp/ path by UTL_FILE package

--select * from v$parameter where name like 'utl%' --Get utl directory path from

CREATE OR REPLACE PROCEDURE xx_utl_txt_file_crt_p
( p_errbuf OUT VARCHAR2,
p_retcode OUT NUMBER ) IS
v_file_handle UTL_FILE.FILE_TYPE;

CURSOR c_persons IS

select papf.person_id,papf.EMPLOYEE_NUMBER,papf.FULL_NAME from per_all_people_f papf
where rownum <100
ORDER BY 1;

BEGIN

v_file_handle := UTL_FILE.FOPEN('/usr/tmp','MY_UTL_PERSONS.txt', 'W');--txt
--v_file_handle := UTL_FILE.FOPEN('/usr/tmp','MY_UTL_PERSONS.csv', 'W');--CSV
UTL_FILE.PUT_LINE(v_file_handle,'PersonID'||'|'||
'EMP_NO'||'|'||
'EMP_Full_NAME' --column names
);
FOR c_rec IN c_persons LOOP
UTL_FILE.PUT_LINE(v_file_handle,c_rec.person_id||'|'||
c_rec.EMPLOYEE_NUMBER||'|'||
c_rec.FULL_NAME
);
END LOOP;
UTL_FILE.PUT_LINE(v_file_handle,'END OF Extraction');
UTL_FILE.FCLOSE(v_file_handle);
COMMIT;
/* Handle UTL_FILE exceptions Starts */
EXCEPTION
WHEN UTL_FILE.INVALID_OPERATION THEN
utl_file.fclose_all;
dbms_output.put_line(' invalid operation');
WHEN UTL_FILE.INVALID_FILEHANDLE THEN
utl_file.fclose_all;
dbms_output.put_line(' invalid filehandle');
WHEN UTL_FILE.INVALID_PATH THEN
utl_file.fclose_all;
dbms_output.put_line(' invalid path');
WHEN UTL_FILE.INTERNAL_ERROR THEN
utl_file.fclose_all;
dbms_output.put_line(' internal error');
WHEN UTL_FILE.INVALID_MODE THEN
utl_file.fclose_all;
dbms_output.put_line(' invalid mode');
WHEN UTL_FILE.WRITE_ERROR THEN
utl_file.fclose_all;
dbms_output.put_line(' write error');
WHEN UTL_FILE.READ_ERROR THEN
utl_file.fclose_all;
dbms_output.put_line(' read error');
WHEN OTHERS THEN
utl_file.fclose_all;
dbms_output.put_line('other exception');
/* Handle UTL_FILE exceptions Ends */
END xx_utl_txt_file_crt_p;

--- **The file will create on database tire.

Sunday, August 4, 2019

How to log EPF Grievance online for Exit date not updated by previous employer

How to log EPF Grievance online for Exit date not updated by previous employer

Exit dates are mandatory to update from your previous employer as its required when you want to transfer the PF balance to new account or withdraw the amount.

UAN Exit dates and Details find steps :-

1)Log in to employee UAN portal

https://unifiedportal-mem.epfindia.gov.in/memberinterface/

2)Take screen shot of your “Date Of Exit” EPF (DOE EPF) and “Date Of Exit” EPS (DOE EPS).

And save it in word file.

Mention you PF no, UAN no, (previous )Employer name.

And actual date of leaving the organization in same word file.

Then finally convert that file to .pdf as we have to attach this .pdf file at the time of log “Grievance”.

Grievance Log Steps :-

Log in to EPF Grievance Site

https://epfigms.gov.in/

Select related to PF Employer (Radio Checkbox).

Select reason from List of values "Evasion of PF by Employer".

Put Grievance subject as “Exit dates are not updated by previous employer”

and attach the PDF file we created in first step.

Finally you will get the grievance no on mobile and mail which you can track later.

Normally, it will take 10-15 days to resolve any grievance .

Friday, August 2, 2019

CUSTOM.pll Customization Example for responsibility wise all forms read only

CUSTOM.pll Customization Example for responsibility wise all forms read only :-

CUSTOM.pll to CUSTOM.plx generation command

frmcmp_batch module=CUSTOM.pll userid=apps/apps output_file=/oraapp/DEV/apps/apps_st/appl/au/12.0.0/resource/CUSTOM.plx compile_all=special module_type=LIBRARY batch=yes

This is "/oraapp/DEV/apps/apps_st/appl/au/12.0.0/resource/CUSTOM.plx"

Whole path of $AU_TOP/resource/

Custom Procedure Code :-

PROCEDURE XXEAA_READ_ONLY(event_name varchar2) IS

form_name VARCHAR2 (150);

block_name VARCHAR2 (150);

item_name VARCHAR2 (150);

v_mark_read_only VARCHAR2 (100);

begin

form_name := NAME_IN ('SYSTEM.CURRENT_FORM');

block_name := NAME_IN ('system.cursor_block');

item_name := NAME_IN ('system.cursor_item');

IF event_name = 'WHEN-NEW-FORM-INSTANCE' THEN

--EXECUTE IMMEDIATE 'CALL CallFunc(''Hello from PL/SQL'') INTO :v_mark_read_only';

--v_mark_read_only := XX_READ_ONLY_ACCESS.chk_entry_exits(fnd_profile.value('RESP_NAME'),fnd_profile.value('USERNAME'));

--IF fnd_profile.value('RESP_ID') = 52419 THEN

--IF fnd_profile.value('RESP_NAME') = 'System Administrator (Read Only)' THEN

IF (fnd_profile.value('RESP_NAME') like '%(Read Only)%' and form_name not like 'FNDRSRUN%')THEN

BEGIN

COPY('Entering app_form.query_only_mode.', 'global.frd_debug');

COPY('YES', 'PARAMETER.QUERY_ONLY');

APP_MENU2.SET_PROP('FILE.SAVE', ENABLED, PROPERTY_OFF);

APP_MENU2.SET_PROP('FILE.ACCEPT', ENABLED, PROPERTY_OFF);

form_name := NAME_IN('system.current_form');

block_name := GET_FORM_PROPERTY(form_name, FIRST_BLOCK);

WHILE (block_name is not null) LOOP

IF (GET_BLOCK_PROPERTY(block_name, BASE_TABLE) is not NULL) THEN

SET_BLOCK_PROPERTY(block_name, INSERT_ALLOWED, PROPERTY_FALSE);

SET_BLOCK_PROPERTY(block_name, UPDATE_ALLOWED, PROPERTY_FALSE);

SET_BLOCK_PROPERTY(block_name, DELETE_ALLOWED, PROPERTY_FALSE);

END IF;

block_name := GET_BLOCK_PROPERTY(block_name, NEXTBLOCK);

END LOOP;

-- copy(25,'SYSTEM.MESSAGE_LEVEL');

END;

END IF;

IF (event_name = 'WHEN-NEW-RECORD-INSTANCE' or event_name = 'WHEN-NEW-BLOCK-INSTANCE'

or event_name = 'WHEN-NEW-FORM-INSTANCE' or event_name = 'WHEN-NEW-ITEM-INSTANCE')

AND (fnd_profile.value('RESP_NAME') like '%(Read Only)%' and form_name not like 'FNDRSRUN%')THEN

copy(25,'SYSTEM.MESSAGE_LEVEL');

END IF;

-- WHEN-NEW-FORM-INSTANCE

-- WHEN-NEW-BLOCK-INSTANCE

-- WHEN-NEW-RECORD-INSTANCE

-- WHEN-NEW-ITEM-INSTANCE

-- WHEN-VALIDATE-RECORD

/* IF ( event_name = 'WHEN-NEW-RECORD-INSTANCE' OR event_name = 'WHEN-NEW-BLOCK-INSTANCE' )

AND (fnd_profile.value('RESP_NAME') LIKE '%(Read Only)%'

AND form_name NOT LIKE 'FNDRSRUN%' )

THEN

copy(25,'SYSTEM.MESSAGE_LEVEL');

END IF;

END;

======

CUSTOM procedure :-

package body custom is

-- Customize this package to provide specific responses to events

-- within Oracle Applications forms.

-- Do not change the specification of the CUSTOM package in any way.

-- You may, however, add additional packages to this library.

--------------------------------------------------------------------

function zoom_available return boolean is

-- This function allows you to specify if zooms exist for the current

-- context. If zooms are available for this block, then return TRUE;

-- else return FALSE.

-- This routine is called on a per-block basis within every Applications

-- form from the WHEN-NEW-BLOCK-INSTANCE trigger. Therefore, any code

-- that will enable Zoom must test the current form and block from

-- which the call is being made.

-- By default this routine must return FALSE.

/* Sample code:

form_name varchar2(30) := name_in('system.current_form');

block_name varchar2(30) := name_in('system.cursor_block');

begin

if (form_name = 'DEMXXEOR' and block_name = 'ORDERS') then

return TRUE;

else

return FALSE;

end if;

end zoom_available;

-- Real code starts here

begin

return FALSE;

end zoom_available;

--------------------------------------------------------------------

function style(event_name varchar2) return integer is

-- This function allows you to determine the execution style for some

-- product-specific events. You can choose to have your code execute

-- before, after, or in place of the code provided in Oracle

-- Applications. See the Applications Technical Reference manuals for a

-- list of events that are available through this interface.

-- Any event that returns a style other than custom.standard must have

-- corresponding code in custom.event which will be executed at the

-- time specified.

-- The following package variables should be used as return values:

-- custom.before

-- custom.after

-- custom.override

-- custom.standard

-- By default this routine must return custom.standard

-- Oracle Corporation reserves the right to change the events

-- available through this interface at any time.

/* Sample code:

begin

if event_name = 'OE_LINES_PRICING' then

return custom.override;

else

return custom.standard;

end if;

end style;

-- Real code starts here

begin

return custom.standard;

end style;

--------------------------------------------------------------------

procedure event(event_name varchar2) is

-- This procedure allows you to execute your code at specific events

-- including:

-- ZOOM

-- WHEN-NEW-FORM-INSTANCE

-- WHEN-NEW-BLOCK-INSTANCE

-- WHEN-NEW-RECORD-INSTANCE

-- WHEN-NEW-ITEM-INSTANCE

-- WHEN-VALIDATE-RECORD

-- Additionally, product-specific events will be passed via this

-- interface (see the Applications Technical Reference manuals for

-- a list of events that are available).

-- By default this routine must perform 'null;'.

-- Oracle Corporation reserves the right to change the events

-- available through this interface at any time.

/* Sample code:

form_name varchar2(30) := name_in('system.current_form');

block_name varchar2(30) := name_in('system.cursor_block');

param_to_pass1 varchar2(255);

param_to_pass2 varchar2(255);

begin

-- Zoom event opens a new session of a form and

-- passes parameter values to the new session. The parameters

-- already exist in the form being opened.

if (event_name = 'ZOOM') then

if (form_name = 'DEMXXEOR' and block_name = 'ORDERS') then

param_to_pass1 := name_in('ORDERS.order_id');

param_to_pass2 := name_in('ORDERS.customer_name');

fnd_function.execute(FUNCTION_NAME=>'DEM_DEMXXEOR',

OPEN_FLAG=>'Y',

SESSION_FLAG=>'Y',

OTHER_PARAMS=>'ORDER_ID="'||param_to_pass1||

'" CUSTOMER_NAME="'||param_to_pass2||'"');

-- all the extra single and double quotes account for

-- any spaces that might be in the passed values

end if;

-- This is an example of a product-specific event. Note that as

-- of Prod 15, this event doesn't exist.

elsif (event_name = 'OE_LINES_PRICING') then

get_custom_pricing('ORDERS.item_id', 'ORDERS.price');

-- This is an example of enforcing a company-specific business

-- rule, in this case, that all vendor names must be uppercase.

elsif (event_name = 'WHEN-VALIDATE-RECORD') then

if (form_name = 'APXVENDR') then

if (block_name = 'VENDOR') then

copy(upper(name_in('VENDOR.NAME')), 'VENDOR.NAME');

end if;

else

null;

end if;

end event;

-- Real code starts here

begin

XXEAA_READ_ONLY('WHEN-NEW-FORM-INSTANCE');

--XXEAA_PO_REQSTR_READONLY_P('WHEN-NEW-FORM-INSTANCE');--added on ---30-Jul-19 for --For Resposibility "EAA Purchasing Requestor" (ID-50680)

end event;

BEGIN

-- You should consider updating the version information listed below as you

-- make any customizations to this library. This information will be

-- displayed in the 'About Oracle Applications' window in the Forms PL/SQL

-- section. Only change the revision, date and time sections of this string.

fdrcsid('$Header: CUSTOM.pld 120.0 2005/05/07 16:43:22 appldev ship $');

end custom;