Monday, 22 January 2018

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

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