Oracle Blogs

This is bat file code to create a folder with todays date and in that create backup and working folders. USES : devs take backup of objects they are woking on in this way this is a repetitive task that can be done by this code , for those who are lazy  ;) HOW TO USE : copy below code and paste it in notepad and save it as .bat file , to use it just double click on that file @echo off :: Get the current date in YYYYMMDD format for /f "skip=1 tokens=2 delims==" %%i in ('wmic os get localdatetime /value') do set datetime=%%i :: Extract the year, month, and day set year=%datetime:~0,4% set month=%datetime:~4,2% set day=%datetime:~6,2% :: Convert the numeric month to the abbreviated month name setlocal enabledelayedexpansion set "Month[01]=Jan" set "Month[02]=Feb" set "Month[03]=Mar" set "Month[04]=Apr" set "Month[05]=May" set "Month[06]=Jun" set "Month[07]=Jul" set "Month[08]=Aug" set "Mo...

 select  aia.invoice_num , ap.vendor_name, apsa.amount_remaining unpaid_amount, aipa.AMOUNT paid_amount, ac.check_number document_number from  ap_suppliers ap, ap_invoices_all aia, ap_payment_schedules_all apsa, ap_invoice_payments_all aipa, ap_checks_all          ac where 1=1  and apsa.invoice_id=aia.invoice_id and aipa.invoice_id(+)=aia.invoice_id and aipa.check_id = ac.check_id(+) and aia.vendor_id=ap.vendor_id  AND aia.payment_status_flag in ('N','P') AND apsa.amount_remaining != 0 and aia.vendor_id = :vendor_id  and aia.PAYMENT_STATUS_FLAG in ('N','P') and aia.CANCELLED_DATE is null

Following are the Methods(DELIVERY CHANNEL) to send invoice using bursting program: EMAIL, FAX, FTP, SFTP, FILE, PRINT & WEBDEV. This is brief explanaition for more detailed blog : click here 1. Create Data Model Exmple Query: select b.trx_number, a.customer_trx_id, b.term_id, b.org_id, b.batch_id, b.status_trx, b.application_id FROM ra_customer_trx_lines_all a, ra_customer_trx_all b WHERE a.customer_trx_id= b.customer_trx_id AND a.customer_trx_id in (1002,1) 2. Create Layout and Template. 3. Run The Report and check it is generating output properly or not. 4. Go to Bursting in left corner of data model screen, add new & give required details. 5. In Split by field & Delivered by field select the value(Column) on which you have to split the PDF to send the output (like customer_name, transaction_id or Invoice_no). E.g: split by customer_name Split by Transaction no. Invoice 5 Customer1 -- 1 file, 5 Inv...

  DOCUMENTATION ON ESS AND ESS JOB SET FOR BI REPORTS Table of Contents 1.        WHAT IS ESS JOB .. 1 2.        PRE REQUISTS . 2 3.        PARAMETERIZED ESS JOB REPORT . 4 4.        SCHEDULE NEW PROCESS . 6     1.    STEPS TO CREATE CUSTOM ESS JOB 1.     WHAT IS ESS JOB   ESS Stands for  Enterprise Scheduler  Job. Once the report is developed we can register it as an ESS, Users can submit the ESS job from the Schedule process screen. Also, the ESS job can be invoked in asynchronous mode from Web Services and using an ERP adapter in OIC. In Fusion applications ESS jobs can be run on demand and as per schedules. These jobs are analogous to concurrent programs in Oracle EBS applications. There are many ESS jobs that are delivered as part of Fusion applications, and users can also create and...

Process to Insert Image into table BLOB column create  table testr13 (col1 blob); / select * from testr13; / create DIRECTORY testr13 as '\Rehman\Training\My docs'; / DECLARE     l_bfile BFILE;     l_blob  BLOB; BEGIN     -- Initialize the BLOB column     INSERT INTO testr13 (col1)     VALUES (EMPTY_BLOB())     RETURNING col1 INTO l_blob;     -- Specify the location of the image file     l_bfile := BFILENAME('XX_SHAREPOINT_TEST', 'm.jpg');     -- Open the BFILE     DBMS_LOB.fileopen(l_bfile, DBMS_LOB.file_readonly);     -- Load the BFILE data into the BLOB column     DBMS_LOB.loadfromfile(l_blob, l_bfile, DBMS_LOB.getlength(l_bfile));     -- Close the BFILE     DBMS_LOB.fileclose(l_bfile);          -- Commit the transaction     COMMIT; END; /

  query to get DD&DT Tables in r12  ------------------------------- select * from XDO_TEMPLATES_B where template_code = 'SITCO_PORD_RPT_V'; select * from XDO_TEMPLATES_TL where template_code = 'SITCO_PORD_RPT_V'; select * from XDO_LOBS where lob_code = 'SITCO_PORD_RPT_V'; select * from XDO_DS_DEFINITIONS_B where data_source_code = 'SITCO_PORD_RPT_V'; select * from XDO_DS_DEFINITIONS_TL where data_source_code = 'SITCO_PORD_RPT_V'; -------------------------------------------------------------------------------- script to delete DD&DT ---------------------- BEGIN DELETE FROM XDO_TEMPLATES_B WHERE template_code = 'SITCO_PORD_RPT'; DELETE FROM XDO_TEMPLATES_TL WHERE template_code = 'SITCO_PORD_RPT'; DELETE FROM XDO_LOBS WHERE lob_code = 'SITCO_PORD_RPT'; DELETE FROM XDO_DS_DEFINITIONS_B WHERE data_source_code = 'SITCO_PORD_RPT'; DELETE FROM XDO_DS_DEFINITIONS_TL WHERE data_source_code = 'SITCO_PORD_RPT';...

QR code Generate From Oracle PLSQL Here is a GITHUB link For necessary Files. https://github.com/Rehman0270/PLSQLQRcode 1) Download The required files from above link. 2) upload all jar archives in lib folder to your database  (Eample path in WINSCP to put the jar files : /u02/oracle/VIS/fs1/EBSapps/10.1.2/lib ) 3) After uploading open putty and change directory to lib folder in WINSCP and run below commands one by one cd  /u02/oracle/VIS/fs1/EBSapps/10.1.2/lib loadjava -force -genmissing -r -user username/password@database -verbose core-1.7.jar loadjava -force -genmissing -r -user username/password@database -verbose javase-1.7.jar loadjava -force -genmissing -r -user username/password@database -verbose qrgen-1.2.jar 4) Open sql developer and run this functions CREATE OR REPLACE AND RESOLVE JAVA SOURCE NAMED "QRCodeBean" as import oracle.sql.BLOB; import oracle.sql.*; import oracle.jdbc.driver.*; import java.sql.*; import javax.imageio.ImageIO; import java.awt.image.Buffered...

How to migrate from one instance to another using FNDLOAD In the following I have discussed on how to migrate from one instance to another using FNDLOAD. Following is the list of .lct files that are used for different objects 1) Concurrent Program –> afcpprog.lct 2) Value Sets –> afffload.lct 3) Menus –> afsload.lct Download FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt MENU MENU_NAME=’XXXX’ Upload FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/afsload.lct file_name.ldt 4) Lookups –> aflvmlu.lct Download FNDLOAD apps/APPS_PWD O Y DOWNLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt FND_LOOKUP_TYPE APPLICATION_SHORT_NAME=’XXXX’ LOOKUP_TYPE=’XXXX’ Upload FNDLOAD apps/APPS_PWD O Y UPLOAD $FND_TOP/patch/115/import/aflvmlu.lct file_name.ldt 5) Flexfield –> afffload.lct Descriptive Flexfield Download FNDLOAD apps/APPS_PWD 0 Y DOWNLOAD $FND_TOP/patch/115/import/afffload.lct file_name.ldt DESC_FLEX APPLICATION_S...

 PLSQL PROCEDURE TO CALL A REST API POST METHOD create or replace procedure XXXCC_XXX_XXX_PROC(oredrno number) as -- varibles for running REST API   l_http_request  UTL_HTTP.req;   l_http_response UTL_HTTP.resp;   l_response_text CLOB;   l_payload       CLOB; --below 3 are non mandatroy varibales only to store data from table to paas in payload   NUM varchar2(200);   RNAME varchar2(200); BEGIN --sql query only use if you are passing values from tables into api SELECT a,b,into num,rname FROM dual where ord = oredrno; -- Create the payload l_payload := '{     "method": "add",     "data": {       "recipient_details": {         "recp_mobile": "'||NUM||'",         "recp_name": "'||RNAME||'"       },   }';   -- Initialize the HTTP request    ...