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.

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 .

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;

    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;

      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;

FNDLOAD Use for form personalization points to remember

FNDLOAD Use for form personalization points to remember


FNDLOAD <login>/<password> 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct <file_name.ldt> FND_FORM_CUSTOM_RULES form_name=<form_name>

FNDLOAD apps/apps 0 Y DOWNLOAD $FND_TOP/patch/115/import/affrmcus.lct XXEAA_POBPA_FRMPERZN.ldt FND_FORM_CUSTOM_RULES form_name=POXPOVPO

POXPOVPO-->po summary form

FNDLOAD <login>/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct <file_name.ldt> 

FNDLOAD apps/<password> 0 Y UPLOAD $FND_TOP/patch/115/import/affrmcus.lct XXEAA_POBPA_FRMPERZN.ldt

  Please note while preparing the ldt based MTP, please check the following1)   Lct file version ($FND_TOP/patch/115/import) in DEV(source) & Production (destination) are same.2)   Always download the production instance ldt file (specific to the form you have made changes) & download the equivalent file in DEV instance. Do a comparison in examdiff (tool). Ensure only intended changes are going to get MTPed. Please note based on last update date, FNDLOAD command will issue Insert / update comments. So if there are any unintended last_update_date changes, kindly pay close attention to it.