com.intuit.it.billing.data.BillingDAOImpl.java Source code

Java tutorial

Introduction

Here is the source code for com.intuit.it.billing.data.BillingDAOImpl.java

Source

package com.intuit.it.billing.data;

import java.sql.CallableStatement;
import java.sql.Connection;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import javax.naming.Context;
import javax.naming.InitialContext;
import javax.naming.NamingException;
import javax.sql.DataSource;
import oracle.jdbc.OracleTypes;

import org.springframework.stereotype.Repository;
import org.springframework.stereotype.Service;
import java.sql.SQLException;

import com.intuit.it.billing.model.Allocation;
import com.intuit.it.billing.model.Customer;
import com.intuit.it.billing.model.LineItem;
import com.intuit.it.billing.model.PaymentInfoCC;
import com.intuit.it.billing.model.PaymentInfoCheck;
import com.intuit.it.billing.model.PaymentInfoEFT;
import com.intuit.it.billing.util.JSONException;

@Repository("billingDAO")
public class BillingDAOImpl implements BillingDAO {

    /**
      * @section dao_section class BillingDAOImpl
     * getAllocation
     * 
     * To be used in a caching method where we are pulling all of the allocations at once.  The way we can do this
     * is to merge a date range based set of billing history records with a date range set of allocations.
     * <p/>
     * <p/>
     * <b>DATABASE PROCEDURE:</b>
     *  
     * @code
     *     FUNCTION fn_get_item_allocations(
     *           item_no IN VARCHAR2 )
     *        RETURN ref_cursor;
     * @endcode
     * <p/>
     * <b>DATABASE RESULT SET:</b>
     * <ul>
     *    <li>ALLOCATION_DATE,</li>
     *    <li>ALLOCATION_T, </li>
     *    <li>ALLOCATION_AMT,</li>
     *    <li>AR_ITEM_NO, </li>
     *    <li>BILL_ITEM_NO, </li>
     *    <li>ITEM_DESCRIPTION, </li>
     *    <li>ITEM_CODE,</li> 
     *    <li>AR_ITEM_DATE, </li>
     *    <li>BILL_ITEM_DATE </li>
     *    <li>LICENSE</li>
     *  </ul>
     *  
     * @param customer  :  The Customer.accountNo account number of the customer who's allocations we need
     * @param startDate : The starting date of the allocation - to be merged with a billing history record set
     * @param endDate  :  The ending date of the allocation - to be merged with a billing history record set
     * 
     * @return A list of Allocation objects. 
     * @throws JSONException 
     * 
     * 
     * 
     */
    @Override
    public List<Allocation> getAllocation(String itemNo) throws JSONException {

        List<Allocation> allocs = new ArrayList<Allocation>();

        String query = "begin ? := billing_inquiry.fn_get_item_allocations( ? ); end;"; //TODO: configure

        ResultSet rs = null;
        Connection conn = null;

        // DB Connection
        try {
            conn = this.getConnection();
        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        } catch (NamingException e) {
            throw JSONException.namingError(e.toString());
        }

        try {
            CallableStatement stmt = conn.prepareCall(query);
            stmt.registerOutParameter(1, OracleTypes.CURSOR);
            stmt.setString(2, itemNo);

            stmt.execute();
            rs = (ResultSet) stmt.getObject(1);

            while (rs.next()) {

                Allocation a = new Allocation();
                a.setAllocatedFromItem(rs.getString("AR_ITEM_NO"));
                a.setAllocatedToItem(rs.getString("BILL_ITEM_NO"));
                a.setAllocationAmount(rs.getBigDecimal("ALLOCATION_AMT"));
                a.setAllocationDate(rs.getTimestamp("ALLOCATION_DATE"));
                a.setItemCode(rs.getString("ITEM_CODE"));
                a.setItemDescription(rs.getString("ITEM_DESCRIPTION"));
                a.setLicense(rs.getString("LICENSE"));
                allocs.add(a);
            }
            conn.close();
            rs.close();

        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        }

        if (allocs == null || allocs.isEmpty()) {
            throw JSONException.noDataFound("Null set returned - no data found");
        }

        return allocs;
    }

    /**
     * getAllocationsList
     * 
     * To be used in a caching method where we are pulling all of the allocations at once.  The way we can do this
     * is to merge a date range based set of billing history records with a date range set of allocations.
     * <p/>
     * <p/>
     * <b>DATABASE PROCEDURE:</b>
     *  
     * @code
     *     FUNCTION fn_get_allocations(
     *           customer IN VARCHAR2,
     *           start_date IN DATE,
     *           end_date   IN DATE )
     *        RETURN ref_cursor;
     * @endcode
     * <p/>
     * <b>DATABASE RESULT SET:</b>
     * <ul>
     *    <li>ALLOCATION_DATE,</li>
     *    <li>ALLOCATION_T, </li>
     *    <li>ALLOCATION_AMT,</li>
     *    <li>AR_ITEM_NO, </li>
     *    <li>BILL_ITEM_NO, </li>
     *    <li>ITEM_DESCRIPTION, </li>
     *    <li>ITEM_CODE,</li> 
     *    <li>AR_ITEM_DATE, </li>
     *    <li>BILL_ITEM_DATE </li>
     *    <li>LICENSE</li>
     *  </ul>
     *  
     * @param customer  :  The Customer.accountNo account number of the customer who's allocations we need
     * @param startDate : The starting date of the allocation - to be merged with a billing history record set
     * @param endDate  :  The ending date of the allocation - to be merged with a billing history record set
     * 
     * @return A list of Allocation objects. 
     * 
     */
    @Override
    public List<Allocation> getAllocationsList(String customer, Date startDate, Date endDate) throws JSONException {

        List<Allocation> allocs = new ArrayList<Allocation>();

        java.sql.Date sqlStartDate = new java.sql.Date(startDate.getTime());
        java.sql.Date sqlEndDate = new java.sql.Date(endDate.getTime());

        String query = "begin ? := billing_inquiry.fn_get_allocations( ?, ?, ? ); end;";

        Connection conn = null;
        ResultSet rs = null;

        // DB Connection
        try {
            conn = this.getConnection();
        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        } catch (NamingException e) {
            throw JSONException.namingError(e.toString());
        }

        try {
            CallableStatement stmt = conn.prepareCall(query);
            stmt.registerOutParameter(1, OracleTypes.CURSOR);
            stmt.setString(2, customer);
            stmt.setDate(3, sqlStartDate);
            stmt.setDate(4, sqlEndDate);

            stmt.execute();
            rs = (ResultSet) stmt.getObject(1);

            while (rs.next()) {

                Allocation a = new Allocation();
                a.setAllocatedFromItem(rs.getString("AR_ITEM_NO"));
                a.setAllocatedToItem(rs.getString("BILL_ITEM_NO"));
                a.setAllocationAmount(rs.getBigDecimal("ALLOCATION_AMT"));
                a.setAllocationDate(rs.getTimestamp("ALLOCATION_DATE"));
                a.setItemCode(rs.getString("ITEM_CODE"));
                a.setItemDescription(rs.getString("ITEM_DESCRIPTION"));
                a.setLicense(rs.getString("LICENSE"));
                allocs.add(a);
            }
            conn.close();
            rs.close();

        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        }

        if (allocs == null || allocs.isEmpty()) {
            throw JSONException.noDataFound("Null set returned - no data found");
        }

        return allocs;
    }

    /**
     * getBillingHistory is to be used to get  get the billing history of a given customer.
     * <p/>
     * <p/>
     * <b>DATABASE PROCEDURE:</b>
     *  
     * @code
     *  FUNCTION fn_get_history(
     *    customer   IN VARCHAR2,
     *    start_date IN DATE,
     *    end_date   IN DATE,
     *    page       IN INTEGER,
     *    records    IN INTEGER )
     *  RETURN ref_cursor;
     *  @endcode
     * <p/>
     * <b>DATABASE RESULT SET:</b>
     * <ul>
     *    <li>ITEM_ID,</li>
     *    <li>BILL_ITEM_NO,</li>
     *    <li>AR_ACCOUNT_NO,</li>
     *    <li>ACCOUNT_NO,</li>
     *    <li>ORDER_NO,</li>
     *    <li>ORDER_LINE_NO,</li>
     *    <li>EVENT_TYPE,</li>
     *    <li>CHARGE_TYPE,</li>
     *    <li> CURRENCY,</li>
     *    <li>CREATED_DATE,</li>
     *    <li>BILL_DATE,</li>
     *    <li>DUE_DATE,</li>
     *    <li>STATUS,</li>
     *    <li>REASON_CODE,</li>
     *    <li>ITEM_TOTAL,</li>
     *    <li>ITEM_DUE,</li>
     *    <li>ITEM_DISPUTED,</li>
     *    <li>ITEM_BASE,</li>
     *    <li>ITEM_TAX,</li>
     *    <li>ITEM_DESCRIPTION,</li>
     *    <li>ITEM_CODE,</li>
     *    <li>LICENSE,</li>
     *    <li>PAY_TYPE,</li>
     *    <li>PAY_DESCR,</li>
     *    <li>PAY_ACCT_TYPE,
     *    <li>PAY_PSON,</li>
     *    <li>QUANTITY </li>
     *  </ul>
     *  
     * @param customer  :  The Customer.accountNo of the customer we want history for
     * @param startDate : The starting date of the allocation - to be merged with a billing history record set
     * @param endDate   :  The ending date of the allocation - to be merged with a billing history record set
     * @param skip      :  Starting record for server side paging
     * @param pageSize  :  How many records to retrieve 
     * 
     * @return A list of LineItem objects in reverse date order sort
     */
    @Override
    public List<LineItem> getBillingHistory(String cust, Date startDate, Date endDate, Integer startPage,
            Integer pageSize) throws JSONException {

        List<LineItem> history = new ArrayList<LineItem>();

        java.sql.Date sqlStartDate = new java.sql.Date(startDate.getTime());
        java.sql.Date sqlEndDate = new java.sql.Date(endDate.getTime());

        String query = "begin ? := billing_inquiry.fn_get_history( ?, ?, ?, ?, ? ); end;";

        Connection conn = null;
        ResultSet rs = null;

        // DB Connection
        try {
            conn = this.getConnection();
        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        } catch (NamingException e) {
            throw JSONException.namingError(e.toString());
        }

        try {

            CallableStatement stmt = conn.prepareCall(query);
            stmt.registerOutParameter(1, OracleTypes.CURSOR);
            stmt.setString(2, cust);
            stmt.setDate(3, sqlStartDate);
            stmt.setDate(4, sqlEndDate);
            stmt.setInt(5, startPage);
            stmt.setInt(6, pageSize);

            stmt.execute();
            rs = (ResultSet) stmt.getObject(1);

            while (rs.next()) {

                LineItem l = new LineItem();
                l.setRowId(rs.getInt("ROW_ID"));
                l.setBaseAmount(rs.getBigDecimal("ITEM_BASE"));
                l.setItemTotal(rs.getBigDecimal("ITEM_TOTAL"));
                l.setItemDue(rs.getBigDecimal("ITEM_DUE"));
                l.setItemDisputed(rs.getBigDecimal("ITEM_DISPUTED"));
                l.setTaxAmount(rs.getBigDecimal("ITEM_TAX"));
                l.setBillDate(rs.getTimestamp("BILL_DATE"));
                l.setBillItemNo(rs.getString("BILL_ITEM_NO"));
                l.setBillTo(rs.getString("AR_ACCOUNT_NO"));
                l.setChargeType(rs.getString("CHARGE_TYPE"));
                l.setCreatedDate(rs.getTimestamp("CREATED_DATE"));
                l.setCurrency(rs.getString("CURRENCY"));
                l.setDueDate(rs.getTimestamp("DUE_DATE"));
                l.setEventType(rs.getString("EVENT_TYPE"));
                l.setItemCode(rs.getString("ITEM_CODE"));
                l.setItemDescription(rs.getString("ITEM_DESCRIPTION"));
                l.setLicense(rs.getString("LICENSE"));
                l.setItemID(rs.getString("ITEM_ID"));
                l.setOrderLine(rs.getString("ORDER_LINE_NO"));
                l.setOrderNo(rs.getString("ORDER_NO"));
                l.setPayAccountType(rs.getString("PAY_ACCT_TYPE"));
                l.setPayDescription(rs.getString("PAY_DESCR"));
                l.setPayType(rs.getString("PAY_TYPE"));
                l.setpSON(rs.getString("PAY_PSON"));
                l.setQuantity(rs.getInt("QUANTITY"));
                l.setReasonCode(rs.getString("REASON_CODE"));
                l.setStatus(rs.getInt("STATUS"));
                history.add(l);
            }

            conn.close();
            rs.close();

        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        }

        if (history == null || history.isEmpty()) {
            throw JSONException.noDataFound("Null set returned - no data found");
        }

        return history;
    }

    /**
     * getCustomerInfo
     * <p/>
     * <p/>
     * <b>DATABASE PROCEDURE:</b>
     *  
     * @code
     *  FUNCTION fn_get_customer
     *  (
     *    customer     IN VARCHAR2,
     *    bill_item_no IN VARCHAR2,
     *    order_no     IN VARCHAR2,
     *    pson         IN VARCHAR2
     *  )
     *  RETURN ref_cursor;
     * @endcode
     * <p/>
     * <b>DATABASE RESULT SET:</b>
     * <ul>
     *    <li>ACCOUNT_NO,</li>
     *    <li>FIRST_NAME,</li>
     *    <li>LAST_NAME,</li>
     *    <li>COMPANY,</li>
     *    <li>PHONE,</li>
     *    <li>ADDRESS,</li>
     *    <li>CITY,</li>
      *    <li>STATE,</li>
     *    <li>ZIP,</li>
     *    <li>COUNTRY </li>
     *  </ul>
     *  
     * @param customer - The Customer.accountNo of the customer we want to find
     * @param billNo -  A bill number (e.g. B1-1111) OR bill line item number (e.g. B1-111,5) of the customer we want to find  - remove everything after the comma
     * @param orderNo - An order number (e.g. 200000011111) OR order line item number (e.g. 200000011111,5) of the customer we want to find - remove everything after the comma
     * @param pson - A BRM trans_id number (Paymenttech merchant number) of the payment event
     * 
     * @return A  Customer object
     * 
     */
    @Override
    public Customer getCustomerInfo(String customer, String billNo, String orderNo, String pson)
            throws JSONException {

        Customer c = null;

        String query = "begin ? := billing_inquiry.fn_get_customer( ?, ?, ?, ? ); end;";

        Connection conn = null;
        ResultSet rs = null;

        // DB Connection
        try {
            conn = this.getConnection();
        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        } catch (NamingException e) {
            throw JSONException.namingError(e.toString());
        }

        try {

            CallableStatement stmt = conn.prepareCall(query);
            stmt.registerOutParameter(1, OracleTypes.CURSOR);
            stmt.setString(2, customer);
            stmt.setString(3, billNo);
            stmt.setString(4, orderNo);
            stmt.setString(5, pson);

            stmt.execute();
            rs = (ResultSet) stmt.getObject(1);

            while (rs.next()) {

                c = new Customer();
                c.setAccountNo(rs.getString("ACCOUNT_NO"));
                c.setFirstName(rs.getString("FIRST_NAME"));
                c.setLastName(rs.getString("LAST_NAME"));
                c.setCompany(rs.getString("COMPANY"));
                c.setPhone(rs.getString("PHONE"));
                c.setAddress(rs.getString("ADDRESS"));
                c.setCity(rs.getString("CITY"));
                c.setState(rs.getString("STATE"));
                c.setZip(rs.getString("ZIP"));
                c.setCountry(rs.getString("COUNTRY"));
                break;
            }
            conn.close();
            rs.close();

        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        }

        if (c == null) {
            throw JSONException.noDataFound("Null set returned - no data found");
        }

        return c;
    }

    /**
     * getCustomerList
     * 
     * <p/>
     * <p/>
     * <b>DATABASE PROCEDURE:</b>
     *  
     * @code
     *FUNCTION fn_search_customers
     * (
     *    phone      IN VARCHAR2,
     *    first_name IN VARCHAR2,
     *    last_name  IN VARCHAR2,
     *    company    IN VARCHAR2,
     *    cc_num     IN VARCHAR2,
     *    eft_num    IN VARCHAR2,
     *    records    IN INTEGER
     *  )
     *  RETURN ref_cursor;
     * @endcode
     *  
     * <p/>
     * <b>DATABASE RESULT SET:</b>
     * <ul>
     *    <li>ACCOUNT_NO,</li>
     *    <li>FIRST_NAME,</li>
     *    <li>LAST_NAME,</li>
     *    <li>COMPANY,</li>
     *    <li>PHONE,</li>
     *    <li>ADDRESS,</li>
     *    <li>CITY,</li>
      *    <li>STATE,</li>
     *    <li>ZIP,</li>
     *    <li>COUNTRY </li>
     *  </ul>
     *  
     * @param phone - The customer's phone number
     * @param firstName -  A wild-carded first name
     * @param lastName -  A wild-carded last name
     * @param company -  A wild-carded company name
     * @param ccNum -  A four digit last-four of a credit card
     * @param eftNum -  A four digit last-four of a bank account number
     * @param pageSize  :  How many records to retrieve 
     * 
     * @return A list of Customer objects in lastname, company alphabetical order
     *         - if rows returned is greater than pageSize will not return TOO MANY rows erros
     * 
     */
    @Override
    public List<Customer> getCustomerList(String phone, String firstName, String lastName, String company,
            String ccNum, String eftNum, Integer pageSize)

            throws JSONException {

        Integer startPage = 1;
        List<Customer> customers = new ArrayList<Customer>();

        String query = "begin ? := billing_inquiry.fn_search_customers( ?, ?, ?, ?, ?, ?, ?, ? ); end;";

        Connection conn = null;
        ResultSet rs = null;

        // DB Connection
        try {
            conn = this.getConnection();
        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        } catch (NamingException e) {
            throw JSONException.namingError(e.toString());
        }

        try {

            CallableStatement stmt = conn.prepareCall(query);
            stmt.registerOutParameter(1, OracleTypes.CURSOR);
            stmt.setString(2, phone);
            stmt.setString(3, firstName);
            stmt.setString(4, lastName);
            stmt.setString(5, company);
            stmt.setString(6, ccNum);
            stmt.setString(7, eftNum);
            stmt.setInt(8, startPage);
            stmt.setInt(9, pageSize);

            stmt.execute();
            rs = (ResultSet) stmt.getObject(1);

            while (rs.next()) {

                Customer c = new Customer();
                c.setAccountNo(rs.getString("ACCOUNT_NO"));
                c.setFirstName(rs.getString("FIRST_NAME"));
                c.setLastName(rs.getString("LAST_NAME"));
                c.setCompany(rs.getString("COMPANY"));
                c.setPhone(rs.getString("PHONE"));
                c.setAddress(rs.getString("ADDRESS"));
                c.setCity(rs.getString("CITY"));
                c.setState(rs.getString("STATE"));
                c.setZip(rs.getString("ZIP"));
                c.setCountry(rs.getString("COUNTRY"));
                customers.add(c);
            }
            conn.close();
            rs.close();

        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        }

        if (customers == null || customers.isEmpty()) {
            throw JSONException.noDataFound("Null set returned - no data found");
        }

        return customers;
    }

    /**
     * PaymentInfoCC
     * 
     * <p/>
     * <p/>
     * <b>DATABASE PROCEDURE:</b>
     *  
     * @code
     *FUNCTION fn_get_payinfo_cc
     * (
     *    item_no      IN VARCHAR2
     *  )
     *  RETURN ref_cursor;
     * @endcode
     *  
     * <p/>
     * <b>DATABASE RESULT SET:</b>
     * <ul>
     *    <li>BILLING_PROFILE_ID,</li>
     *    <li>BDOM,</li>
     *    <li>PAYINFO_NAME,</li>
     *    <li>CC_NUMBER,</li>
     *    <li>EXPIRE_MM,</li>
     *    <li>EXPIRE_YYYY,</li>
     *  </ul>
     *  
     * @param itemNo - The item number (e.g. P1-111)of the item we want payment info for
     * 
     * @return A single Payment Info record
     * 
     * TODO 
     * Decide how to determine the card type from the token
     * 
     */
    @Override
    public PaymentInfoCC getPayinfoCC(String itemNo) throws JSONException {
        // TODO Auto-generated method stub

        String query = "begin ? := billing_inquiry.fn_get_payinfo_cc( ? ); end;";

        Connection conn = null;
        ResultSet rs = null;
        PaymentInfoCC c = null;

        // DB Connection
        try {
            conn = this.getConnection();
        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        } catch (NamingException e) {
            throw JSONException.namingError(e.toString());
        }

        try {

            CallableStatement stmt = conn.prepareCall(query);
            stmt.registerOutParameter(1, OracleTypes.CURSOR);
            stmt.setString(2, itemNo);

            stmt.execute();
            rs = (ResultSet) stmt.getObject(1);

            while (rs.next()) {

                c = new PaymentInfoCC();
                c.setBillingProfile(rs.getString("BILLING_PROFILE_ID"));
                c.setProfileBdom(rs.getInt("BDOM"));
                c.setCardholderName(rs.getString("PAYINFO_NAME"));
                c.setCcNumber(rs.getString("CC_NUMBER"));
                c.setExpiryMonth(rs.getString("EXPIRE_MM"));
                c.setExpiryYear(rs.getString("EXPIRE_YYYY"));
                c.setCardType("TODO");
            }

            conn.close();
            rs.close();

        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        }

        if (c == null) {
            throw JSONException.noDataFound("Null set returned - no data found");
        }

        return c;
    }

    /**
     * PaymentInfoEFT
     * 
     * <p/>
     * <p/>
     * <b>DATABASE PROCEDURE:</b>
     *  
     * @code
     *FUNCTION fn_get_payinfo_eft
     * (
     *    item_no      IN VARCHAR2
     *  )
     *  RETURN ref_cursor
     * @endcode
     * <p/>
     * <b>DATABASE RESULT SET:</b>
     * <ul>
     *    <li>BILLING_PROFILE_ID,</li>
     *    <li>BDOM,</li>
     *    <li>PAYINFO_NAME,</li>
     *    <li>BANK_ACCOUNT_NO,</li>
     *    <li>BANK_NUMBER,</li>
     *  </ul>
     *  
     * @param itemNo - The item number (e.g. P1-111)of the item we want payment info for
     * 
     * @return A single Payment Info record
     * 
     */
    @Override
    public PaymentInfoEFT getPayinfoEFT(String itemNo) throws JSONException {

        String query = "begin ? := billing_inquiry.fn_get_payinfo_eft( ? ); end;";

        Connection conn = null;
        PaymentInfoEFT c = null;
        ResultSet rs = null;

        // DB Connection
        try {
            conn = this.getConnection();
        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        } catch (NamingException e) {
            throw JSONException.namingError(e.toString());
        }

        try {

            CallableStatement stmt = conn.prepareCall(query);
            stmt.registerOutParameter(1, OracleTypes.CURSOR);
            stmt.setString(2, itemNo);

            stmt.execute();
            rs = (ResultSet) stmt.getObject(1);

            while (rs.next()) {

                c = new PaymentInfoEFT();
                c.setBillingProfile(rs.getString("BILLING_PROFILE_ID"));
                c.setProfileBdom(rs.getInt("BDOM"));
                c.setCustomerName(rs.getString("PAYINFO_NAME"));
                c.setBankAccountNumber(rs.getString("BANK_ACCOUNT_NO"));
                c.setBankNumber(rs.getString("BANK_NUMBER"));
            }

            conn.close();
            rs.close();

        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        }

        if (c == null) {
            throw JSONException.noDataFound("Null set returned - no data found");
        }

        return c;
    }

    /**
     * PaymentInfoCheck
     * <p/>
     * <p/>
     * <b>DATABASE PROCEDURE:</b>
     *  
     * @code
     *FUNCTION fn_get_payinfo_check
     * (
     *    item_no      IN VARCHAR2
     *  )
     *  RETURN ref_cursor;
     * @endcode
     * <p/>
     * <b>DATABASE RESULT SET:</b>
     * <ul>
     *    <li>BILLING_PROFILE_ID,</li>
     *    <li>BDOM,</li>
     *    <li>PAYINFO_NAME,</li>
     *    <li>NET_TERMS,</li>
     *    <li>CHECK_NO,</li>
     *  </ul>
     *  
     * @param itemNo - The item number (e.g. P1-111)of the item we want payment info for
     * 
     * @return A single Payment Info record
     * 
     */
    @Override
    public PaymentInfoCheck getPayinfoCheck(String itemNo) throws JSONException {

        String query = "begin ? := billing_inquiry.fn_get_payinfo_check( ? ); end;"; // TODO: configure strings

        ResultSet rs = null;
        Connection conn = null;
        PaymentInfoCheck c = null;

        // DB Connection
        try {
            conn = this.getConnection();
        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        } catch (NamingException e) {
            throw JSONException.namingError(e.toString());
        }

        try {

            CallableStatement stmt = conn.prepareCall(query);
            stmt.registerOutParameter(1, OracleTypes.CURSOR);
            stmt.setString(2, itemNo);

            stmt.execute();
            rs = (ResultSet) stmt.getObject(1);

            while (rs.next()) {

                c = new PaymentInfoCheck();
                c.setBillingProfile(rs.getString("BILLING_PROFILE_ID")); // TODO: configure strings
                c.setProfileBdom(rs.getInt("BDOM"));
                c.setCustomerName(rs.getString("PAYINFO_NAME"));
                c.setNetTerms(rs.getString("NET_TERMS"));
                c.setCheckNumber(rs.getString("CHECK_NO"));
            }

            conn.close();
            rs.close();

        } catch (SQLException e) {
            throw JSONException.sqlError(e);
        }

        if (c == null) {
            throw JSONException.noDataFound("Null set returned - no data found");
        }

        return c;
    }

    Connection getConnection() throws NamingException, SQLException {
        Context initContext = null;
        Context envContext = null;
        Connection conn = null;
        DataSource ds = null;

        initContext = new InitialContext();
        envContext = (Context) initContext.lookup("java:/comp/env");
        ds = (DataSource) envContext.lookup("datasources/OracleDS");
        conn = ds.getConnection();

        return conn;
    }
}