Oracle Blog: 2017

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

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());
}
}

}