Monday, 22 January 2018

Automate PLSQL script to download LDT files for all your Custom Concurrent Programs in Oracle Apps r12


Hi Friends , In this post , I will share you the automate PLSQL script to download LDT files for all your Custom Concurrent Programs in Oracle Apps r12.

In this below example I am taking example of those concurrent requests whose name is stating with XX.

All your Concurrent program LDT files registration will be saved automatically in your Server directory , which you will mentioned in this PLSQL script.


declare
a1 LONG;
b4 number:=1;
b LONG;
B2 LONG;
C1 LONG;
TEST_FILE_1 Utl_File.File_Type;
--PATH1 VARCHAR2(40):='/usr/tmp'
TEST_FILE_2 Utl_File.File_Type;
FILE_NAME_DOWNLOAD varchar2(400):='XX_DOWNLOAD_LTD';
FILE_NAME_UPLOAD  varchar2(400):='XX_UPLOAD_LTD';
BEGIN
EXECUTE IMMEDIATE 'create or replace directory EX_TT_1 AS '||'''/usr/tmp'||'''' ;
COMMIT;
TEST_FILE_1 := Utl_File.FOPEN('EX_TT_1',FILE_NAME_DOWNLOAD||'.txt','W',32767);
TEST_FILE_2 := Utl_File.FOPEN('EX_TT_1',FILE_NAME_UPLOAD||'.txt','W',32767);
Utl_File.PUT_LINE(FILE     => TEST_FILE_1,
    buffer   => 'cd',
    autoflush => TRUE);
    Utl_File.PUT_LINE(FILE     => TEST_FILE_2,
    buffer   => 'cd',
    autoflush => TRUE);
    Utl_File.PUT_LINE(FILE     => TEST_FILE_1,
    buffer   => 'mkdir FILIX_LDT',
    autoflush => TRUE);
    Utl_File.PUT_LINE(FILE     => TEST_FILE_1,
    buffer   => 'cd FILIX_LDT',
    autoflush => TRUE);
    Utl_File.PUT_LINE(FILE     => TEST_FILE_2,
    buffer   => 'cd FILIX_LDT',
    autoflush => TRUE);
      
FOR j in (select (SELECT APPLICATION_SHORT_NAME FROM FND_APPLICATION
WHERE APPLICATION_ID=FP.APPLICATION_ID) APP ,CONCURRENT_PROGRAM_NAME p_name from FND_CONCURRENT_PROGRAMS FP
WHERE CONCURRENT_PROGRAM_NAME like 'XX%')
loop
B:=NULL;
B2:=NULL;
B:='FNDLOAD apps/apps O Y DOWNLOAD $FND_TOP/patch/115/import/afcpprog.lct TEST45'||b4||'.ldt PROGRAM APPLICATION_SHORT_NAME="'||J.APP||'" CONCURRENT_PROGRAM_NAME="'||J.p_name||'"';
B2:='FNDLOAD apps/apps O Y UPLOAD $FND_TOP/patch/115/import/afcpprog.lct TEST45'||b4||'.ldt';
Utl_File.PUT_LINE(FILE     => TEST_FILE_1,
    buffer   => b,
    autoflush => TRUE);
 Utl_File.PUT_LINE(FILE     => TEST_FILE_2,
    buffer   => b2,
    autoflush => TRUE);
dbms_output.put_line(rtrim(b));
dbms_output.put_line(rtrim(b2));
b4:=b4+1;
C1:=NULL;
a1:=null;
end loop;
if Utl_File.is_open(TEST_FILE_1) then
Utl_File.FCLOSE(TEST_FILE_1);
END IF;
if Utl_File.is_open(TEST_FILE_2) then
Utl_File.FCLOSE(TEST_FILE_2);
END IF;
EXCEPTION WHEN NO_DATA_FOUND THEN
NULL;
dbms_output.put_line(sqlerrm);
WHEN OTHERS THEN
dbms_output.put_line(sqlerrm);
end;

if you found this post helpful and you liked it then follow this blog to get notifications for the upcoming posts.



 

0 comments:

Post a Comment