Calling Bursting Program

   
DECLARE
l_request_id number;

begin
fnd_global.APPS_INITIALIZE(78866, 60094, 809);
COMMIT;

l_request_id := FND_REQUEST.SUBMIT_REQUEST ('XDO','XDOBURSTREP',NULL,NULL,FALSE,'Y', 33149993, 'Y');

COMMIT;

IF l_request_id = 0
  THEN
     dbms_output.put_line ('Concurrent request failed to submit');
  ELSE
     dbms_output.put_line('Successfully Submitted the Concurrent Request');
  END IF;
  --
EXCEPTION
WHEN OTHERS THEN
  dbms_output.put_line('Error While Submitting Concurrent Request '||TO_CHAR(SQLCODE)||'-'||sqlerrm);
END;

Reading and Inserting contents of ACH file into Oracle Table using Java Concurrent Program

Create Tables to store contents of ACH File:

DROP TABLE DSDAP_ACH_FILE_HEADER;

CREATE TABLE DSDAP_ACH_FILE_HEADER

(

FILE_ID NUMBER,

SOURCE_CODE VARCHAR2(30),

FILE_NAME VARCHAR2(240),

FILE_CREATION_DATE DATE,

FILE_CREATION_TIME VARCHAR2(30),

FILE_MODIFIER VARCHAR2(30),

FORMAT_CODE VARCHAR2(30),

IMMEDIATE_DESTINATION VARCHAR2(240),

IMMEDIDATE_DESTINATION_NAME VARCHAR2(240),

TOTAL_CREDIT_AMOUNT NUMBER,

TOTAL_DEBIT_AMOUNT NUMBER,

CONC_PROCESS_DATE DATE

);

DROP TABLE DSDAP_ACH_BATCH_HEADER;

CREATE TABLE DSDAP_ACH_BATCH_HEADER

(BATCH_HEADER_ID NUMBER, 

FILE_ID NUMBER,

BATCH_NUMBER VARCHAR2(30),

COMPANY_DESCRIPTIVE_DATE DATE,

COMPANY_DISCRETIONARY_DATA VARCHAR2(300),

COMPANY_ID VARCHAR2(30), 

COMPANY_NAME VARCHAR2(300),

EFFECTIVE_ENTRY_DATE DATE,

TOTAL_CREDIT_AMOUNT NUMBER,

TOTAL_DEBIT_AMOUNT NUMBER,

CONC_PROCESS_DATE DATE

);

DROP TABLE DSDAP_ACH_BATCH_LINES;

CREATE TABLE DSDAP_ACH_BATCH_LINES

(

BATCH_LINE_ID NUMBER,

BATCH_HEADER_ID NUMBER,

RECORD_TYPE_CODE VARCHAR2(1),

AMOUNT NUMBER,

CHECK_DIGIT NUMBER,

DFI_ACCOUNT_NUMBER VARCHAR2(30), 

DISCRETIONARY_DATA VARCHAR2(240),

INDIVIDUAL_NUMBER VARCHAR2(30), 

INDVIDUAL_NAME VARCHAR2(240),

RECEIVING_DFI_ID VARCHAR2(30)

);

CREATE SEQUENCE DSDAP_ACH_FILE_ID START WITH 100 NOCYCLE;

CREATE SEQUENCE DSDAP_ACH_BATCH_HEADER_ID START WITH 100 NOCYCLE;

CREATE SEQUENCE DSDAP_ACH_BATCH_LINE_ID START WITH 100 NOCYCLE;

CREATE OR REPLACE VIEW DSDAP_ACH_BATCH_LINES_V

AS

SELECT mfile.file_id, 

mfile.file_creation_date, 

mfile.file_name, 

mfile.source_code,

mfile.total_credit_amount file_Control_tot_CR,

mfile.total_debit_amount file_Control_tot_DR,

mfile.format_code file_format_code, 

mfile.immediate_destination, 

mfile.immedidate_destination_name,

mfile.conc_process_date,

mBHeader.Batch_Header_Id, 

mBHeader.Batch_Number, 

mBHeader.Total_Credit_Amount AS Batch_Control_Tot_CR, 

mBHeader.Total_Debit_Amount AS Batch_Control_Tot_DB,

mbLines.Batch_Line_Id, 

mbLines.Amount Batch_line_amount,

mbLines.Check_Digit,

mbLines.Dfi_Account_Number, 

mbLines.Discretionary_Data, 

mbLines.Individual_Number, 

mbLines.Indvidual_Name, 

mbLines.Receiving_Dfi_Id, 

mbLines.Record_Type_Code Batch_Line_Record_Type

FROM DSDAP_ACH_FILE_HEADER mfile, 

DSDAP_ACH_BATCH_HEADER mBHeader, 

DSDAP_ACH_BATCH_LINES mBLines

WHERE mfile.FILE_ID  = mBHeader.FILE_ID

AND mBHeader.BATCH_HEADER_ID = mBLines.BATCH_HEADER_ID

ORDER BY mfile.file_id, mBHeader.Batch_Header_Id, mbLines.Batch_Line_Id ASC;

Register Java Concurrent Program:

Download ACHViewer.jar from sourceforge.net and deploy on Application Server. 

Follow the steps for registering a Jar file. The other workaround is simply to unzip the jar and place in $JAVA_TOP 
//package com.ach.achViewer.ach;
package dsd.oracle.apps.dsdap.ach;


import com.ach.achViewer.ach.ACHBatch;
import com.ach.achViewer.ach.ACHEntry;
import com.ach.achViewer.ach.ACHFile;


import com.ach.achViewer.ach.ACHRecordBatchHeader;
import com.ach.achViewer.ach.ACHRecordFileHeader;

import java.text.ParseException;

import java.util.Vector;
import java.util.Iterator;

import java.sql.*;

import oracle.apps.fnd.cp.request.*;

import java.text.DateFormat;
import java.text.SimpleDateFormat;

import oracle.apps.fnd.util.*;

import oracle.jdbc.OracleCallableStatement;


public class DsdTransformAchFileToTable implements JavaConcurrentProgram {
    String fileName = "";
    String filePath = "";

    int fileHeaderId;
    int batchHeaderId;

    Connection mJConn;
    ReqCompletion lRC;
    ParameterList lPara;
    OutFile lOF;
    LogFile lLF;

    java.util.Date date = new java.util.Date();
    java.sql.Timestamp sqlTimeStamp = new java.sql.Timestamp(date.getTime());

    double fileControlTotalDr = 0;
    double fileControlTotalCr = 0;

    double batchControlTotalDr = 0;
    double batchControlTotalCr = 0;

    double batchLineTotalDr = 0;
    double batchLineTotalCr = 0;


    public void runProgram(CpContext pCpContext) {

        int requestId = pCpContext.getReqDetails().getRequestId();
        int error_flag = 0;


        // get parameter list object from CpContext 
        lPara = pCpContext.getParameterList();


        // get ReqCompletion object from CpContext 
        lRC = pCpContext.getReqCompletion();

        while (lPara.hasMoreElements()) {
            NameValueType aNVT = lPara.nextParameter();
            if (aNVT.getName().equals("p_filename"))
                fileName = aNVT.getValue();
            else if (aNVT.getName().equals("p_filepath"))
                filePath = aNVT.getValue();
        }

        mJConn = pCpContext.getJDBCConnection();

        // get OutFile object from CpContext 
        lOF = pCpContext.getOutFile();

        // get LogFile object from CpContext 
        lLF = pCpContext.getLogFile();

        ACHFile achFile = null;

        try {

            if (filePath.equals("")) {
                filePath = "/tmp";
            }

            int lastPosition = filePath.length();
            char lastChar = filePath.charAt(lastPosition - 1);

            if (lastChar != '/') {
                filePath = filePath + "/";
            }
            //"/u00/ebstst/apps/apps_st/comn/java/classes/dsd/oracle/apps/dsdap/ach/resources/nacha_payroll_072520171012.dat";

            achFile = new ACHFile(filePath + fileName);
            achFile.toString();
        } catch (Exception e) {
            logMessage(e.toString());
            e.printStackTrace();
            lRC.setCompletion(ReqCompletion.ERROR, e.toString());
            error_flag = 1;
            return;
        }

        logMessage("filePath" + filePath);


        logMessage("------------");
        logMessage("File Header");
        logMessage("------------");

        ACHRecordFileHeader fileHdr = achFile.getFileHeader();
        logMessage("fileHdr.getRecordTypeCode() " + fileHdr.getRecordTypeCode()); //new

        logMessage("fileHdr.getPriorityCode() " + fileHdr.getPriorityCode()); //new
        logMessage("fileHdr.getImmediateDestination() " + fileHdr.getImmediateDestination()); //group number
        logMessage("fileHdr.getImmediateOrigin() " + fileHdr.getImmediateOrigin()); //new wellsfargo r/t number

        logMessage("fileHdr.getFileCreationDate() " + fileHdr.getFileCreationDate());
        logMessage("fileHdr.getFileCreationTime() " + fileHdr.getFileCreationTime());
        logMessage("fileHdr.getFileIdModifier() " + fileHdr.getFileIdModifier());
        logMessage("fileHdr.getRecordSize() " + fileHdr.getRecordSize()); //new
        logMessage("fileHdr.getBlockingFactor() " + fileHdr.getBlockingFactor()); //new
        logMessage("fileHdr.getFormatCode() " + fileHdr.getFormatCode());
        logMessage("fileHdr.getImmediateDestinationName() " + 
                   fileHdr.getImmediateDestinationName()); //new company name
        logMessage("fileHdr.getImmediateOriginName() " + fileHdr.getImmediateOriginName()); //new origination bank
        logMessage("fileHdr.getReferenceCode() " + fileHdr.getReferenceCode());

        logMessage("------------");
        logMessage("File Control");
        logMessage("------------");
        logMessage("achFile.getFileControl().getTotCreditDollarAmt() " + 
                   achFile.getFileControl().getTotCreditDollarAmt());
        logMessage("achFile.getFileControl().getTotDebitDollarAmt() " + 
                   achFile.getFileControl().getTotDebitDollarAmt());


        String sql = 
            "INSERT INTO DSDAP_ACH_FILE_HEADER (FILE_ID, SOURCE_CODE, FILE_NAME, FILE_CREATION_DATE, FILE_CREATION_TIME, " + 
            "FILE_MODIFIER, FORMAT_CODE, IMMEDIATE_DESTINATION, IMMEDIDATE_DESTINATION_NAME, TOTAL_CREDIT_AMOUNT, TOTAL_DEBIT_AMOUNT, " + 
            "CONC_PROCESS_DATE, RECORD_TYPE_CODE, PRIORITY_CODE, IMMEDIATE_ORIGIN, RECORD_SIZE, BLOCK_FACTOR, IMMIEDIATE_ORIGIN_NAME, REFERENCE_CODE, REQUEST_ID)" + 
            "VALUES (:1,:2,:3,:4,:5,:6,:7,:8, :9, :10, :11, :12,:13,:14,:15, :16, :17, :18, :19, :20)";

        DateFormat df = new SimpleDateFormat("yymmdd");

        fileHeaderId = getNextSeqValue("DSDAP_ACH_FILE_ID");

        try {
            PreparedStatement lStmt = mJConn.prepareStatement(sql);
            try {
                lStmt.setInt(1, fileHeaderId);
                lStmt.setString(2, "DC_WATER_BILL");
                lStmt.setString(3, fileName);
                if (!fileHdr.getFileCreationDate().trim().equals("")) {
                    lStmt.setDate(4, new java.sql.Date(df.parse(fileHdr.getFileCreationDate()).getTime()));
                } else {
                    lStmt.setDate(4, null);
                }

                lStmt.setString(5, fileHdr.getFileCreationTime());
                lStmt.setString(6, fileHdr.getFileIdModifier());
                lStmt.setString(7, fileHdr.getFormatCode());
                lStmt.setString(8, fileHdr.getImmediateDestination());
                lStmt.setString(9, fileHdr.getImmediateDestinationName());


                fileControlTotalCr = Double.parseDouble(achFile.getFileControl().getTotCreditDollarAmt()) / 100;
                lStmt.setDouble(10, fileControlTotalCr);

                fileControlTotalDr = Double.parseDouble(achFile.getFileControl().getTotDebitDollarAmt()) / 100;
                lStmt.setDouble(11, fileControlTotalDr);

                lStmt.setTimestamp(12, sqlTimeStamp);
                lStmt.setString(13, Character.toString(fileHdr.getRecordTypeCode()));
                lStmt.setString(14, fileHdr.getPriorityCode());
                lStmt.setString(15, fileHdr.getImmediateOrigin());
                lStmt.setString(16, fileHdr.getRecordSize());
                lStmt.setString(17, fileHdr.getBlockingFactor());
                lStmt.setString(18, fileHdr.getImmediateOriginName());
                lStmt.setString(19, fileHdr.getReferenceCode());
                lStmt.setInt(20, requestId);

            } catch (ParseException e) {
                logMessage(e.toString());
                error_flag = 1;
                return;
            }

            lStmt.executeUpdate();

            Vector<ACHBatch> achBatches = achFile.getBatches();
            Iterator batchIter = achBatches.iterator();

            while (batchIter.hasNext()) {

                logMessage("------------");
                logMessage("Batch Header");
                logMessage("------------");

                ACHBatch batch = (ACHBatch)batchIter.next();
                ACHRecordBatchHeader batchHdr = batch.getBatchHeader();

                logMessage("batchHdr.getBatchNumber() = " + batchHdr.getBatchNumber());
                logMessage("batchHdr.getCompanyDescriptiveDate() = " + batchHdr.getCompanyDescriptiveDate());
                logMessage("batchHdr.getCompanyDiscretionaryData() = " + batchHdr.getCompanyDiscretionaryData());
                logMessage("batchHdr.getCompanyId() = " + batchHdr.getCompanyId());
                logMessage("batchHdr.getCompanyName() = " + batchHdr.getCompanyName());
                logMessage("batchHdr.getEffectiveEntryDate() " + batchHdr.getEffectiveEntryDate());

                sql = 
"INSERT INTO DSDAP_ACH_BATCH_HEADER " + "(BATCH_HEADER_ID, FILE_ID, BATCH_NUMBER, COMPANY_DESCRIPTIVE_DATE, COMPANY_DISCRETIONARY_DATA, COMPANY_ID, COMPANY_NAME, " + 
  "EFFECTIVE_ENTRY_DATE, TOTAL_CREDIT_AMOUNT, TOTAL_DEBIT_AMOUNT, CONC_PROCESS_DATE, REQUEST_ID)" + 
  "VALUES (:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11, :12)";
                lStmt = mJConn.prepareStatement(sql);
                batchHeaderId = getNextSeqValue("DSDAP_ACH_BATCH_HEADER_ID");

                try {
                    lStmt.setInt(1, batchHeaderId); //batch_header_id
                    lStmt.setInt(2, fileHeaderId); //file_id
                    lStmt.setString(3, batchHdr.getBatchNumber()); //BATCH_NUMBER

                    if (!batchHdr.getCompanyDescriptiveDate().trim().equals("")) {
                        lStmt.setDate(4,
                                      new java.sql.Date(df.parse(batchHdr.getCompanyDescriptiveDate()).getTime())); //COMPANY_DESCRIPTIVE_DATE
                    } else {
                        lStmt.setDate(4, null);
                    }
                    lStmt.setString(5, batchHdr.getCompanyDiscretionaryData()); //COMPANY_DISCRETIONARY_DATA
                    lStmt.setString(6, batchHdr.getCompanyId()); //COMPANY_ID
                    lStmt.setString(7, batchHdr.getCompanyName()); //COMPANY_NAME

                    if (!batchHdr.getEffectiveEntryDate().trim().equals("")) {
                        lStmt.setDate(8, 
                                      new java.sql.Date(df.parse(batchHdr.getEffectiveEntryDate()).getTime())); //EFFECTIVE_ENTRY_DATE
                    } else {
                        lStmt.setDate(8, null);
                    }
                    lStmt.setDouble(9, 
                                    Double.parseDouble(batch.getBatchControl().getTotCreditDollarAmt()) / 100); //TOTAL_CREDIT_AMOUNT
                    lStmt.setDouble(10, 
                                    Double.parseDouble(batch.getBatchControl().getTotDebitDollarAmt()) / 100); //TOTAL_DEBIT_AMOUNT
                    lStmt.setTimestamp(11, sqlTimeStamp); //CONC_PROCESS_DATE
                    lStmt.setInt(12, requestId);
                    lStmt.executeUpdate();
                } catch (ParseException e) {
                    logMessage(e.toString());
                    e.printStackTrace();
                    error_flag = 1;
                    return;

                }

                Vector<ACHEntry> achEntry = batch.getEntryRecs();
                Iterator entryIter = achEntry.iterator();
                logMessage("------------");
                logMessage("Batch Lines");
                logMessage("------------");

                while (entryIter.hasNext()) {

                    logMessage("");
                    ACHEntry entry = (ACHEntry)entryIter.next();
                    logMessage("getRecordTypeCode =" + entry.getEntryDetail().getRecordTypeCode());
                    logMessage("getTransactionCode =" + entry.getEntryDetail().getTransactionCode()); //new
                    logMessage("getReceivingDfiId =" + 
                               entry.getEntryDetail().getReceivingDfiId()); //receiving_dfi_rt_no
                    logMessage("getReceivingDfiId =" + entry.getEntryDetail().getDfiAcctNbr()); //receiving_dfi_acct_no
                    logMessage("getCheckDigit =" + entry.getEntryDetail().getCheckDigit());
                    logMessage("getAmount =" + entry.getEntryDetail().getAmount());
                    logMessage("getIndividualIdNbr =" + entry.getEntryDetail().getIndividualIdNbr());
                    logMessage("getIndividualName =" + entry.getEntryDetail().getIndividualName());
                    logMessage("getDiscretionaryData =" + entry.getEntryDetail().getDiscretionaryData());
                    logMessage("getTraceNumber =" + 
                               entry.getEntryDetail().getTraceNumber()); //new trace_number   80-87 88-94


                    sql = 
"INSERT INTO DSDAP_ACH_BATCH_LINES (BATCH_LINE_ID, BATCH_HEADER_ID, RECORD_TYPE_CODE, AMOUNT, " + 
  "CHECK_DIGIT, DFI_ACCOUNT_NUMBER, DISCRETIONARY_DATA, INDIVIDUAL_NUMBER, INDVIDUAL_NAME, RECEIVING_DFI_ID, TRANSACTION_CODE, TRACE_NUMBER, REQUEST_ID) VALUES " + 
  "(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11, :12, :13)";

                    lStmt = mJConn.prepareStatement(sql);
                    lStmt.setInt(1, getNextSeqValue("DSDAP_ACH_BATCH_LINE_ID")); //BATCH_LINE_ID
                    lStmt.setInt(2, batchHeaderId); //BATCH_HEADER_ID
                    lStmt.setString(3, 
                                    Character.toString(entry.getEntryDetail().getRecordTypeCode())); //RECORD_TYPE_CODE
                    lStmt.setDouble(4, Double.parseDouble(entry.getEntryDetail().getAmount()) / 100); //AMOUNT
                    lStmt.setInt(5, Integer.parseInt(entry.getEntryDetail().getCheckDigit())); //CHECK_DIGIT
                    lStmt.setString(6, entry.getEntryDetail().getDfiAcctNbr()); //DFI_ACCOUNT_NUMBER
                    lStmt.setString(7, entry.getEntryDetail().getDiscretionaryData()); //DFI_ACCOUNT_NUMBER
                    lStmt.setString(8, entry.getEntryDetail().getIndividualIdNbr()); //INDIVIDUAL_NUMBER
                    lStmt.setString(9, entry.getEntryDetail().getIndividualName()); //INDVIDUAL_NAME
                    lStmt.setString(10, entry.getEntryDetail().getReceivingDfiId()); //RECEIVING_DFI_ID
                    lStmt.setString(11, entry.getEntryDetail().getTransactionCode()); //new
                    lStmt.setString(12, entry.getEntryDetail().getTraceNumber()); //new
                    lStmt.setInt(13, requestId);


                    lStmt.executeUpdate();

                }
                logMessage("-------------");
                logMessage("Batch Control");
                logMessage("-------------");
                logMessage("batch.getBatchControl().getTotCreditDollarAmt() " + 
                           batch.getBatchControl().getTotCreditDollarAmt());
                logMessage("batch.getBatchControl().getTotDebitDollarAmt() " + 
                           batch.getBatchControl().getTotDebitDollarAmt());

                mJConn.commit();

            }

        } catch (SQLException e) {
            logMessage(e.toString());
            e.printStackTrace();
            error_flag = 1;
        } finally {
            if (error_flag == 1) {
                logMessage("Executing Finally Block with error_flag = 1");
                lRC.setCompletion(ReqCompletion.ERROR, "Request Completed with Error");
                cleanUpInterfaceTables(requestId);
                pCpContext.releaseJDBCConnection();
            } else {
                logMessage("Executing Finally Block with error_flag = 0");
                lRC.setCompletion(ReqCompletion.NORMAL, "Request Completed Normal");
                pCpContext.releaseJDBCConnection();
            }
        }

        //lRC.setCompletion(ReqCompletion.NORMAL, "Request Completed Normal");

    }

    private int getNextSeqValue(String seqName) {

        String seqStmt = " BEGIN " + " SELECT " + seqName + ".NEXTVAL INTO :1 FROM DUAL; " + " END;";

        int seqVal = 0;
        OracleCallableStatement callableStmt = null;

        try {

            callableStmt = (OracleCallableStatement)mJConn.prepareCall(seqStmt);
            callableStmt.registerOutParameter(1, Types.INTEGER);
            callableStmt.execute();
            seqVal = callableStmt.getInt(1);
            callableStmt.close();


        } catch (SQLException e) {
            lRC.setCompletion(ReqCompletion.WARNING, e.toString());
            logMessage(e.toString());

        } finally {
            try {
                if (callableStmt != null)
                    callableStmt.close();
            } catch (SQLException e) {
                logMessage(e.toString());
                e.printStackTrace();
            }
        }

        return (seqVal);


    }


    private void logMessage(String str) {
        java.util.Date now = new java.util.Date();
        lLF.writeln(now.toString() + ": " + str, LogFile.STATEMENT);
    }

    private void cleanUpInterfaceTables(int requestId) {
        logMessage("cleanUpInterfaceTables() called");
        try {

            String deleteSql = 
                "BEGIN " + "DELETE FROM dsdap_ach_file_header WHERE REQUEST_ID = :1; " + "DELETE FROM DSDAP_ACH_BATCH_HEADER WHERE REQUEST_ID = :2; " + 
                "DELETE FROM DSDAP_ACH_BATCH_LINES WHERE REQUEST_ID = :3; " + "END;";
            PreparedStatement lStmt = mJConn.prepareStatement(deleteSql);
            lStmt.setInt(1, requestId);
            lStmt.setInt(2, requestId);
            lStmt.setInt(3, requestId);
            lStmt.execute();
            mJConn.commit();

            logMessage("cleanUpInterfaceTables() finished successfully");

        } catch (SQLException e) {
            e.printStackTrace();
            logMessage(e.toString());
        }
    }


}

Duplicate PO Emails being sent out to Supplier that is registered on iSupplier Portal

Known Oracle Issue: Metalink Note References:

How Suppliers Can Avoid E-mail Notifications? (Doc ID 461963.1)

Fix: Customize PO Approval Workflow:

Open up Email PO Process.

This Process calls Function: Send Notification all web Suppliers

CREATE OR REPLACE PACKAGE XXPO_REQAPPROVAL_INIT1 AS procedure Send_WS_Notif_Yes_No(     itemtype        in varchar2,                               itemkey         in varchar2,                               actid           in number,                               funcmode        in varchar2,                               resultout       out NOCOPY varchar2    ) ; END; /                             CREATE OR REPLACE PACKAGE BODY XXPO_REQAPPROVAL_INIT1 AS procedure Send_WS_Notif_Yes_No(     itemtype        in varchar2,                               itemkey         in varchar2,                               actid           in number,                               funcmode        in varchar2,                               resultout       out NOCOPY varchar2    )  IS l_send_notif varchar2(1):= 'N';                              BEGIN                                 l_send_notif := 'N';     resultout := wf_engine.eng_completed || ':' || l_send_notif ; END; END; /

Extract PO Attachments

CREATE OR REPLACE PROCEDURE dsdpo_extract_po_files
AS

  v_file     utl_file.file_type;
  v_line     varchar2(1000);
  v_blob_len number;
  v_pos      number;
  v_buffer   raw(32764);
  v_amt      binary_integer := 32764;

  cursor cur_files
  is
    SELECT ad.entity_name, ad.seq_num Seq , ad.pk1_value, h.segment1, fl.file_name, fl.file_data
    FROM fnd_documents d,
    fnd_attached_documents ad,
    fnd_documents_vl dtl,
    po_headers_all h,
    fnd_lobs fl,
    fnd_document_datatypes fdd
    WHERE d.document_id = ad.document_id
    AND ad.entity_name = 'PO_HEAD'
    AND ad.pk1_value = to_char(h.po_header_id)
--    AND h.segment1 = '436223'
    AND d.document_id = dtl.document_id
    AND fl.file_id = d.media_id
    AND fdd.user_name = 'File'
    and h.po_header_id in(

    select po_header_id from apps.po_headers_all
    where vendor_id =7951
    and creation_date >'01-JAN-2012' and type_lookup_code='STANDARD')
    and dtl.file_name like '%.pdf';

begin
  -- File Attachments
  for c_file in cur_files
  loop
    v_file := utl_file.fopen('/applcsf/ebsdev/temp','dsd_'||c_file.file_name, 'wb', 32764);

    v_blob_len := dbms_lob.getlength(c_file.file_data);
    v_pos := 1;
    while v_pos < v_blob_len
    loop
      dbms_lob.read(c_file.file_data,v_amt,v_pos,v_buffer);
      utl_file.put_raw(v_file,v_buffer,true);
      v_pos := v_pos + v_amt;
    end loop;
    utl_file.fclose(v_file);
  end loop;

end;