R12 LOAN CREATION API

1) API Description:
This Loan API is used to create incomplete loan in the Oracle Loans system.

The following must be setup before using the API:

• Loans system option.
• Loans product, loan class and loan type on which the loan is created.
• Loans index rates.
• Used HZ participant party_id, cust_account_id and bill_to_acct_site_ids.
• Accounting for used loan class and loan type

API creates:
a) Loan header record with all passed or defaulted loan details
b) Loan term record
c) Loan lines records
d) Loan rate schedule records
e) Loan participant records: Primary Borrower, Co-Borrowers and Guarantors
f) Loan custom schedule records if this loan has its own custom schedule defined.
g) Loan fee assignments and fee schedule records defaulted from the product
h) Loan accounting distribution records defaulted from accounting setup

2) Loan Creation API Syntax:

LNS_LOAN_PUB.CREATE_LOAN (
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_VALIDATION_LEVEL IN NUMBER,
P_LOAN_DETAILS_REC IN OUT NOCOPY
LNS_LOAN_PUB.LOAN_DETAILS_REC_TYPE, -- MANDATORY
P_LOAN_LINES_TBL IN OUT NOCOPY
LNS_LOAN_PUB.LOAN_LINES_TBL_TYPE, --ONLY FOR ERS LOAN
P_LOAN_PART_TBL IN OUT NOCOPY
LNS_LOAN_PUB.LOAN_PART_TBL_TYPE, -- OPTIONAL
P_OPEN_RATES_TBL IN OUT NOCOPY
LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE, -- OPTIONAL
P_TERM_RATES_TBL IN OUT NOCOPY
LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE, -- OPTIONAL
P_LOAN_CUST_SCHED_TBL IN OUT NOCOPY
LNS_LOAN_PUB.LOAN_CUST_SCHED_TBL_TYPE, -- OPTIONAL
P_APPLICATION_ID IN NUMBER,
P_CREATED_BY_MODULE IN VARCHAR2,
X_LOAN_ID OUT NOCOPY NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
 3) Input Parameters:
a. P_API_VERSION:
The API Version of the procedure. Mandatory to pass 1.0
b. P_INIT_MSG_LIST:
Passing ‘Y’ initiates messages to log.
c. P_COMMIT:
Passing 'Y' will result in an explicit commit being issued in the API
d. P_VALIDATION_LEVEL:
Reserved
e. P_LOAN_DTL_REC (Mandatory)

P_LOAN_DTL_REC record is Mandatory to create a loan. It is input using parameter of

type ‘LNS_LOAN_PUB.Loan_Details_Rec_Type’.

Field Name Data Type Mandatory Description

Product_Id Number Mandatory FK to lns_loan_products.product_id.
Must be for ERS class. Any missed
parameters will be defaulted from this
product.
Loan_Number Varchar2(20) Optional Free Form Text only used if profile
LNS_GENERATE_LOAN_NUMBER
= ‘N’
Loan_Description Varchar2(250) Optional Loan Description
Loan_Assigned_To Number Mandatory FK to jtf_rs_resource_extns.resource_id
Legal_Entity_Id Number Optional FK to
xle_entity_profiles.legal_entity_id.
Mandatory only if product's
legal_entity_id is null
Requested_Amount Number Used for Loan requested amount; If null will be
direct loans defaulted from loan product
only
Loan_Application_Date Date Optional The application date of the loan. If null
will be defaulted to loan_start_date
Exchange_Rate_Type Varchar2(30) Optional FK to
gl_daily_conversion_types.conversion_t
ype. Mandatory only if the ‘loan
currency’ is different from
‘FUNCTIONAL CURRENCY’.
Exchange_Date Date Optional Mandatory only if the loan currency is
different from Functional Currency and
‘Exchange_Rate_Type’ is ‘USER’
Exchange_Rate Number Optional Mandatory only if the loan currency is
different from Functional Currency and
‘Exchange_Rate_Type’ is ‘USER’
Loan_Purpose_Code Varchar2(30) Optional FK to lns_lookups.lookup_code.
lookup_type = 'LOAN_PURPOSE'. If
null will be defaulted to ‘OTHER’
Loan_Subtype Varchar2(30) Optional FK to lns_lookups.lookup_code.
lookup_type =' LOAN_SUBTYPE'. If
 null will be defaulted to ‘UnSecured’
Credit_Review_Flag Varchar2(1) Optional Credit review flag. Valid values: 'Y' or
'N' or null
Trx_Type_Id Number(15) Mandatory Principal trx type. FK to
ra_cust_trx_types_all. cust_trx_type_id
for ‘Invoice’ and ‘Debit memo’
transactions.
Collateral_Percent Number Optional If SubType is ‘Secured’ then mention
the ‘collateral percent’ value between 1
and 100. Mandatory only if the loan
subtype is ‘SECURED’
Custom_Payments_Flag Varchar2(1) Optional The value ‘Y’ if the loan amortization
schedule used is customized. Otherwise
will be defaulted to ‘N’
Forgiveness_Flag Varchar2(1) Optional Enable forgiveness: valid values: Y and
N. If null will be defaulted from
product_id
Forgiveness_Percent Number Optional Forgiveness percent; valid values from
0 to 100. If null will be defaulted from
product_id
Org_Id Number(15) Do Not Pass Org id. Will be returned by the api
based on product_id
Loan_Type_Id Number Do Not Pass Will be returned by the api based on
product_id
Loan_Class_Code Number Do Not Pass Will be returned by the api based on
product_id
Loan_Currency Varchar2(15) Do Not Pass Will be returned by the api based on
product_id
Maturity_Date Date Do Not Pass Will be returned by the api
Open_Maturity_Date Date Do Not Pass Will be returned by the api
Open_Payment_Frequency Varchar2(30) Do Not Pass Will be returned by the api
Primary_Borrower_Id Number(15) Mandatory Primary borrower. FK to
hz_parties.party_id
Status should be 'A'.
Cust_Account_Id Number(15) Mandatory Primary borrower account. FK to
hz_cust_accounts_all.cust_account_id.
Status should be 'A'.
Bill_To_Acct_Site_Id Number(15) Mandatory Primary borrower bill to account site.
FK to
hz_cust_acct_sites_all.cust_acct_site_id
Contact_Pers_Party_Id Number(15) Optional FK to hz_relationships.party_id
Contact_Rel_Party_Id Number(15) Optional FK to hz_relationships.party_id
Rate_Type Varchar2(30) Optional Rate type. FK to
lns_lookups.lookup_code for
lookup_type = ‘RATE_TYPE’. If null
will be defaulted from product_id
Index_Rate_Id Number Optional Index rate. Valid id from
LNS_INT_RATE_HEADERS table.
FK to
 lns_int_rate_headers.interest_rate_id. If
null will be defaulted from product_id
Day_Count_Method Varchar2(50) Optional Day count method. FK to
lns_lookups.lookup_code.
lookup_type =
'DAY_COUNT_METHOD'. If null will
be defaulted from product_id.
Loan_Payment_Frequency Varchar2(30) Optional Loan/interest payment frequency. FK to
lns_lookups.lookup_code for
lookup_type = ‘FREQUENCY’. If null
will be defaulted from product_id.
Calculation_Method Varchar2(30) Optional Interest calculation method. FK to
lns_lookups.lookup_code for
lookup_type =
‘INTEREST_CALCULATION_METH
OD’. If null will be defaulted from
product_id.
Interest_Compounding_Fr Varchar2(30) Optional Interest compounding frequency. FK to
eq lns_lookups.lookup_code for
lookup_type = ‘FREQUENCY’. If null
will be defaulted from product_id.
Payment_Calc_Method Varchar2(30) Optional Payment calculation method. FK to
lns_lookups.lookup_code for
lookup_type =
‘PAYMENT_CALCULATION_METH
OD’. If null will be defaulted to
‘EQUAL_PAYMENT’
Custom_Calc_Method Varchar2(30) Optional Custom schedule calculation method.
FK to lns_lookups.lookup_code for
lookup_type =
‘CUSTOM_CALCULATION_METHO
D’. Mandatory only if
Custom_Payments_Flag = ‘Y’
Orig_Pay_Calc_Method Varchar2(30) Optional Used for calculating original schedule
only if Custom_Payments_Flag = ‘Y’.
FK to lns_lookups.lookup_code for
lookup_type =
‘PAYMENT_CALCULATION_METH
OD’.
Penal_Int_Rate Number Optional Penal Interest Rate. Used to calculate
penal interest on unpaid principal or
unpaid interest or both depend on
Calculation_Method. If null will be
defaulted to 0
Penal_Int_Grace_Days Number Optional Number of days past installment due
date during which a penal interest
that would be imposed for being late
is waived. If null will be defaulted to 0
Lock_Date Date Optional Interest rate lock date.
Lock_Exp_Date Date Optional Interest rate lock expiration date.
Loan_Term Number Optional Loan term of this loan. If null will be
defaulted from product_id
Loan_Term_Period Varchar2(30) Optional The term period used of this loan. Ex:
Monthly, Annually etc… FK to
lns_lookups.lookup_code; lookup_type
= 'PERIOD'. If null will be defaulted
from product_id
Balloon_Payment_Type Varchar2(30) Optional Loan balloon type of this loan. FK to
lns_lookups.lookup_code; lookup_type
= 'BALLOON_PAYMENT_TYPE'. If
null will be defaulted to ‘TERM’
Balloon_Payment_Amount Number Optional Balloon amount if
balloon_payment_type = ‘AMOUNT’.
If null will be defaulted to 0
Balloon_Term Number Optional Balloon term if balloon_payment_type
= ‘TERM. If null will be defaulted to
Loan_Term
Loan_Start_Date Date Optional The start date of the loan. If null will be
defaulted to sysdate
First_Payment_Date Date Optional The first payment date. Should be equal
or later than the loan start date. Default
to Value of Loan_start_date + 1
payment period
Prin_First_Pay_Date Date Optional Principal first payment date. Mandatory
only if Payment_Calc_Method =
‘SEPARATE_SCHEDULES’. If null
will be defaulted to
First_Payment_Date
Prin_Payment_Frequency Varchar2(30) Optional Principal payment frequency.
Mandatory only if
Payment_Calc_Metho’ =
‘SEPARATE_SCHEDULES’. FK to
lns_lookups.lookup_code for
lookup_type = ‘FREQUENCY’. If null
will be defaulted to
Loan_Payment_Frequency
Floor_Rate Number Optional Floor rate. Only applies when
RATE_TYPE = ‘FLOATING’
Ceiling_Rate Number Optional Ceiling rate. Only applies when
RATE_TYPE = ‘FLOATING’
Percent_Increase Number Optional Max sequential rate percent increase.
Only applies when RATE_TYPE =
‘FLOATING’
Percent_Increase_Life Number Optional Max rate percent increase for life of the
loan. Only applies when RATE_TYPE
= ‘FLOATING’
Open_Loan_Term Number Optional Open phase term; mandatory if its loan
with disbursement phase
Open_Loan_Term_Period Varchar2(30) Optional FK to lns_lookups.lookup_code;
lookup_type = 'PERIOD'; mandatory if
 its loan with disbursement phase
Open_Loan_Start_Date Date Optional Disbursement phase start date;
mandatory if its loan with disbursement
phase
Open_First_Payment_Date Date Optional Disbursement phase first payment date;
mandatory if its loan with disbursement
phase
Open_To_Term_Event Varchar2(30) Optional FK to lns_lookups.lookup_code;
lookup_type =
'OPEN_TO_TERM_EVENT';
mandatory if its loan with disbursement
phase
Open_Floor_Rate Number Optional Open phase floor rate. Only applies
when loan with disbursement phase and
RATE_TYPE = ‘FLOATING’
Open_Ceiling_Rate Number Optional Open phase ceiling rate. Only applies
when loan with disbursement phase and
RATE_TYPE = ‘FLOATING’
Open_Percent_Increase Number Optional Open phase max sequential rate percent
increase. Only applies when loan with
disbursement phase and RATE_TYPE
= ‘FLOATING’
Open_Percent_Increase_Li Number Optional Open phase max rate percent increase
fe for life of the loan. Only applies when
loan with disbursement phase and
RATE_TYPE = ‘FLOATING’
Reamortize_Over_Paymen Varchar2(1) Optional Pass ‘Y’ if we want to recalculate the
t amortization schedule after over
payment else pass ‘N’. If null will be
defaulted to ‘N’
Delinquency_Threshold_A Number Optional The delinquency amount of the loan. If
mount null will be defaulted to calculated
amount by the api.
Payment_Application_Ord Varchar2(30) Optional The order to apply the payment. Ex:
er INT_PRI_FEE. If null will be defaulted
from loan type
Pmt_Appl_Order_Scope Varchar2(30) Optional The scope to apply the payment. If null
will be defaulted from loan type
Ussgl_Transaction_Code Varchar2(30) Optional The transaction code.
Attribute_Category Number(15) Optional The attribute category if we use the
attributes.
Attribute1 Varchar2(150) Optional Value of Attribute1
Attribute2 Varchar2(150) Optional Value of Attribute2
Attribute3 Varchar2(150) Optional Value of Attribute3
Attribute4 Varchar2(150) Optional Value of Attribute4
Attribute5 Varchar2(150) Optional Value of Attribute5
Attribute6 Varchar2(150) Optional Value of Attribute6
Attribute7 Varchar2(150) Optional Value of Attribute7
Attribute8 Varchar2(150) Optional Value of Attribute8
Attribute9 Varchar2(150) Optional Value of Attribute9
Attribute10 Varchar2(150) Optional Value of Attribute10
Attribute11 Varchar2(150) Optional Value of Attribute11
Attribute12 Varchar2(150) Optional Value of Attribute12
Attribute13 Varchar2(150) Optional Value of Attribute13
Attribute14 Varchar2(150) Optional Value of Attribute14
Attribute15 Varchar2(150) Optional Value of Attribute15
Attribute16 Varchar2(150) Optional Value of Attribute16
Attribute17 Varchar2(150) Optional Value of Attribute17
Attribute18 Varchar2(150) Optional Value of Attribute18
Attribute19 Varchar2(150) Optional Value of Attribute19
Attribute20 Varchar2(150) Optional Value of Attribute20

f. P_LOAN_LINES_TBL (Mandatory)

This parameter is required only for creation of ERS loans. The ‘original receivables’ are input
using parameter of type ‘LNS_LOAN_PUB.Loan_Lines_Tbl_Type’. This parameter must have
at least one loan line record.

Field Name Data type Mandatory Description

Line_Number Number Required Any unique serial number
Payment_Schedule_Id Number Required FK to
ar_payment_schedules_all.payment_sch
edule_id
Requested_Amount Number Required The loan amount requested through this
loan line.
Reference_Number Varchar2(60) Do not pass Do not pass; will be set to trx_number
Reference_Description Varchar2(250) Optional Free form text for this loan line
Customer_Trx_Id Number Do not pass Do not pass; will be set to
customer_trx_id
Remaining_Balance Number Do not pass Do not pass; will be set to
amount_due_remaining
Installment_Number Number Do not pass Do not pass; will be set to
terms_sequence_number

g. P_LOAN_PART_TBL (Optional)

The co-borrower and guarantor details of the loan are input using parameter of type '
LNS_LOAN_PUB.Loan_Part_Tbl_Type'. Don’t need to pass primary borrower info. The
primary borrower record will be automatically added to this table from
LNS_LOAN_PUB.Loan_Details_Rec_Type with ‘Loan_Participant_Type’ =
‘PRIMARY_BORROWER’.

Field Name Data type Mandatory Description

Hz_Party_Id Number(15) Mandatory FK to hz_parties.party_id and status
should be 'A'.
Loan_Participant_Type Varchar2(30) Mandatory It might be COBORROWER or
 GUARANTOR. FK to
lns_lookups.lookup_code for
lookup_type =
‘LNS_PARTICIPANT_TYPE’.
Start_Date_Active Date Optional The participant active start date.
End_Date_Active Date Optional The participant active end date.
Cust_Account_Id Number(15) Mandatory FK to
hz_cust_accounts_all.cust_account_id.
Status should be 'A'.
Bill_To_Acct_Site_Id Number(15) Mandatory FK to
hz_cust_acct_sites_all.cust_acct_site_id
Contact_Pers_Party_Id Number(15) Optional FK to hz_relationships.party_id
Contact_Rel_Party_Id Number(15) Optional FK to hz_relationships.party_id

h. P_OPEN_RATES_TBL (Optional)

The loan interest rates schedule for open phase is input using parameter of type '
LNS_LOAN_PUB.Loan_Rates_Tbl_Type'. This parameter applies only when loan has
disbursement phase.

Field Name Data type Mandatory Description

Index_Rate Number Mandatory The interest rate for this loan in a period
Spread Number Optional The spread percentage for this loan in a
period.
Index_Date Date Optional The index date. If passed,
INDEX_RATE will be defaulted from
this date
Begin_Installment_Number Number Mandatory The first installment number for this
interest rate.
End_Installment_Number Number Mandatory The last installment number for this
interest rate. End_Installment_Number
of the last rate row will be assigned by
the system.
Interest_Only_Flag Varchar2(1) Optional Pass ‘Y’ if it requires calculating only
interest for this period. If null will be
defaulted to ‘N’

i. P_TERM_RATES_TBL (Optional)

The loan interest rates schedule for term phase is input using parameter of type '
LNS_LOAN_PUB.Loan_Rates_Tbl_Type'.

Field Name Data type Mandatory Description

Index_Rate Number Mandatory The interest rate for this loan in a period
Spread Number Optional The spread percentage for this loan in a
period.
Index_Date Date Optional The index date. If passed,
 INDEX_RATE will be defaulted from
this date
Begin_Installment_Number Number Mandatory The first installment number for this
interest rate.
End_Installment_Number Number Mandatory The last installment number for this
interest rate. End_Installment_Number
of the last rate row will be assigned by
the system.
Interest_Only_Flag Varchar2(1) Optional Pass ‘Y’ if it requires calculating only
interest for this period. If null will be
defaulted to ‘N’

j. P_LOAN_CUST_SCHED_TBL (Optional)

If the loan has custom schedules, instead of the loan system generated amortization schedule,
then this custom schedule is input using parameter of type '
LNS_LOAN_PUB.Loan_Cust_Sched_Tbl_Type'.

Field Name Data type Mandatory Description

Payment_Number Number Mandatory The payment number or installment
number.
Due_Date Date Mandatory The due date for this installment.
Principal_Amount Number Mandatory The principal amount in this
installment.
Interest_Amount Number Mandatory The interest amount in this installment.
Lock_Prin Varchar2(1) Optional Freeze passed principal amount or let
the system calculate it based on
Loan_Details_Rec_Type
.Custom_Calc_Method. If null will be
defaulted to ‘Y’
Lock_Int Varchar2(1) Optional Freeze passed interest amount or let the
system calculate it based on
Loan_Details_Rec_Type
.Custom_Calc_Method, passed rates
and custom due dates. If null will be
defaulted to ‘Y’

k. P_APPLICATION_ID

Pass ID of the application that creates this loan.

l. P_CREATED_BY_MODULE

Pass name of the module that creates this loan.

m. X_LOAN_ID:

If the loan creates successfully then the generated loanId is returned using this parameter.
 n. X_RETURN_STATUS:

This returns ‘S’ if the procedure executes successfully and ‘E’ if it fails.

o. X_MSG_COUNT:

If the procedure fails then it returns the number count of error messages failed in the
procedure and returns ‘0’ if it is success.

p. X_MSG_DATA:

If the procedure fails then the error messages are returned in this out parameter. The
number of returned error messages is equal to the x_msg_count parameter value.

4) Example:
Example 1 – create direct loan with:
• All loan parameters are defaulted from loan product
• No additional participants
• No rate schedule (will be defaulted from index rate)

set serveroutput on size 100000;

WHENEVER SQLERROR continue;

DECLARE

l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(2000);

l_loan_id NUMBER;
l_loan_number VARCHAR2(60);
l_index number := 0;
l_indexNo number := 1;
l_msg varchar2(4000) := null;

l_LOAN_DTL_REC LNS_LOAN_PUB.Loan_Details_Rec_Type;
l_LOAN_LINES_TBL LNS_LOAN_PUB.Loan_Lines_Tbl_Type;
l_LOAN_PART_TBL LNS_LOAN_PUB.LOAN_PART_TBL_TYPE;
l_OPEN_RATES_TBL LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE;
l_TERM_RATES_TBL LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE;
l_loan_cust_sched_tbl LNS_LOAN_PUB.loan_cust_sched_tbl_type;

BEGIN

fnd_global.apps_initialize(1011864, 25323, 206, 0

MO_GLOBAL.INIT('LNS');
MO_GLOBAL.set_policy_context('S',204);

l_LOAN_DTL_REC.product_id := 10011;
l_LOAN_DTL_REC.LOAN_NUMBER := 'api001';
 l_LOAN_DTL_REC.LOAN_DESCRIPTION := 'API Created direct - all
defaulted from product';

-- set LOAN_ASSIGNED_TO
select resource_id into l_LOAN_DTL_REC.LOAN_ASSIGNED_TO
from jtf_rs_resource_extns
where UPPER(SOURCE_FIRST_NAME) = 'ART'
and category = 'EMPLOYEE'
and rownum <2;

l_LOAN_DTL_REC.legal_entity_id := null;
l_LOAN_DTL_REC.requested_amount := null;
l_LOAN_DTL_REC.LOAN_APPLICATION_DATE := null;
l_LOAN_DTL_REC.EXCHANGE_RATE_TYPE := null;
l_LOAN_DTL_REC.EXCHANGE_DATE := null;
l_LOAN_DTL_REC.EXCHANGE_RATE := null;
l_LOAN_DTL_REC.LOAN_PURPOSE_CODE := null;
l_LOAN_DTL_REC.LOAN_SUBTYPE := null;
l_LOAN_DTL_REC.credit_review_flag := null;

-- set trx_type_id
select cust_trx_type_id into l_LOAN_DTL_REC.trx_type_id
from ra_cust_trx_types_all
where name = 'Loan Principal' and org_id = 204;

l_LOAN_DTL_REC.COLLATERAL_PERCENT := null;
l_LOAN_DTL_REC.CUSTOM_PAYMENTS_FLAG := null;
l_LOAN_DTL_REC.FORGIVENESS_FLAG := null;
l_LOAN_DTL_REC.FORGIVENESS_PERCENT := null;

-- primary borrower attributes

l_LOAN_DTL_REC.primary_borrower_party_id := 1006;
l_LOAN_DTL_REC.CUST_ACCOUNT_ID := 1006;
l_LOAN_DTL_REC.BILL_TO_ACCT_SITE_ID := 1826;
l_LOAN_DTL_REC.contact_rel_party_id := null;
l_LOAN_DTL_REC.CONTACT_PERS_PARTY_ID := null;

-- common term attributes

l_LOAN_DTL_REC.RATE_TYPE := null;
l_LOAN_DTL_REC.INDEX_RATE_ID := null;
l_LOAN_DTL_REC.DAY_COUNT_METHOD := null;
l_LOAN_DTL_REC.LOAN_PAYMENT_FREQUENCY := null;
l_LOAN_DTL_REC.CALCULATION_METHOD := null;
l_LOAN_DTL_REC.INTEREST_COMPOUNDING_FREQ := null;
l_LOAN_DTL_REC.PAYMENT_CALC_METHOD := null;
l_LOAN_DTL_REC.CUSTOM_CALC_METHOD := null;
l_LOAN_DTL_REC.ORIG_PAY_CALC_METHOD := null;
l_LOAN_DTL_REC.PENAL_INT_RATE := null;
l_LOAN_DTL_REC.PENAL_INT_GRACE_DAYS := null;
l_LOAN_DTL_REC.LOCK_DATE := null;
l_LOAN_DTL_REC.LOCK_EXP_DATE := null;

-- 'term phase' term attributes

l_LOAN_DTL_REC.LOAN_TERM := null;
l_LOAN_DTL_REC.LOAN_TERM_PERIOD := null;
l_LOAN_DTL_REC.balloon_payment_type := null;
l_LOAN_DTL_REC.balloon_payment_amount := null;
 l_LOAN_DTL_REC.balloon_term := null;
l_LOAN_DTL_REC.loan_start_date := to_date('01-01-2009','dd-mm-
yyyy');
l_LOAN_DTL_REC.FIRST_PAYMENT_DATE := null;
l_LOAN_DTL_REC.PRIN_FIRST_PAY_DATE := null;
l_LOAN_DTL_REC.PRIN_PAYMENT_FREQUENCY := null;
l_LOAN_DTL_REC.floor_rate := null;
l_LOAN_DTL_REC.ceiling_rate := null;
l_LOAN_DTL_REC.percent_increase := null;
l_LOAN_DTL_REC.percent_increase_life := null;

-- 'disbursement phase' term attributes

l_LOAN_DTL_REC.open_loan_term := null;
l_LOAN_DTL_REC.open_loan_term_period := null;
l_LOAN_DTL_REC.open_loan_start_date := null;
l_LOAN_DTL_REC.open_first_payment_date := null;
l_LOAN_DTL_REC.open_to_term_event := null;
l_LOAN_DTL_REC.open_floor_rate := null;
l_LOAN_DTL_REC.open_ceiling_rate := null;
l_LOAN_DTL_REC.open_percent_increase := null;
l_LOAN_DTL_REC.open_percent_increase_life := null;

-- Terms for Payment attributes

l_LOAN_DTL_REC.REAMORTIZE_OVER_PAYMENT := null;
l_LOAN_DTL_REC.DELINQUENCY_THRESHOLD_AMOUNT := null;
l_LOAN_DTL_REC.PAYMENT_APPLICATION_ORDER := null;
l_LOAN_DTL_REC.PMT_APPL_ORDER_SCOPE := null;

-- additional optional attributes

l_LOAN_DTL_REC.ATTRIBUTE_CATEGORY := null;
l_LOAN_DTL_REC.ATTRIBUTE1 := null;
l_LOAN_DTL_REC.ATTRIBUTE2 := null;
l_LOAN_DTL_REC.ATTRIBUTE3 := null;
l_LOAN_DTL_REC.ATTRIBUTE4 := null;
l_LOAN_DTL_REC.ATTRIBUTE5 := null;
l_LOAN_DTL_REC.ATTRIBUTE6 := null;
l_LOAN_DTL_REC.ATTRIBUTE7 := null;
l_LOAN_DTL_REC.ATTRIBUTE8 := null;
l_LOAN_DTL_REC.ATTRIBUTE9 := null;
l_LOAN_DTL_REC.ATTRIBUTE10 := null;
l_LOAN_DTL_REC.ATTRIBUTE11 := null;
l_LOAN_DTL_REC.ATTRIBUTE12 := null;
l_LOAN_DTL_REC.ATTRIBUTE13 := null;
l_LOAN_DTL_REC.ATTRIBUTE14 := null;
l_LOAN_DTL_REC.ATTRIBUTE15 := null;
l_LOAN_DTL_REC.ATTRIBUTE16 := null;
l_LOAN_DTL_REC.ATTRIBUTE17 := null;
l_LOAN_DTL_REC.ATTRIBUTE18 := null;
l_LOAN_DTL_REC.ATTRIBUTE19 := null;
l_LOAN_DTL_REC.ATTRIBUTE20 := null;

dbms_output.put_line ( 'Calling LNS_LOAN_PUB.CREATE_LOAN...');

LNS_LOAN_PUB.CREATE_LOAN(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
P_COMMIT => 'F',
P_VALIDATION_LEVEL => 100,
 P_Loan_Details_Rec => l_LOAN_DTL_REC,
P_Loan_Lines_Tbl => l_LOAN_LINES_TBL,
P_LOAN_PART_TBL => l_LOAN_PART_TBL,
P_OPEN_RATES_TBL => l_OPEN_RATES_TBL,
P_TERM_RATES_TBL => l_TERM_RATES_TBL,
p_loan_cust_sched_tbl => l_loan_cust_sched_tbl,
P_Application_id => 206,
P_Created_by_module => 'test script',
X_LOAN_ID => l_loan_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);

dbms_output.put_line ( 'l_return_status ' || l_return_status);

dbms_output.put_line ( 'l_msg_count ' || l_msg_count);

IF l_return_status <> 'S' THEN

dbms_output.put_line('Errors:');
while (l_indexNo <= l_msg_Count ) loop
fnd_msg_pub.get(l_indexNo, 'F', l_msg, l_index);
dbms_output.put_line(l_msg);
l_indexNo := l_indexNo + 1;
End Loop;

ELSE
commit;
dbms_output.put_line('Success!') ;
dbms_output.put_line('New loan_id: ' || l_loan_id);
dbms_output.put_line('New loan number: ' ||
l_LOAN_DTL_REC.loan_number);
END IF;

END;
/

Example 2 – create ERS loan with:

• All loan parameters are defaulted from loan product
• 2 loan lines
• 1 additional participant
• Rate schedule

set serveroutput on size 100000;

WHENEVER SQLERROR continue;

DECLARE

l_return_status varchar2(1);
l_msg_count number;
l_msg_data varchar2(2000);

l_loan_id NUMBER;
l_loan_number VARCHAR2(60);
 l_index number := 0;
l_indexNo number := 1;
l_msg varchar2(4000) := null;

l_LOAN_DTL_REC LNS_LOAN_PUB.Loan_Details_Rec_Type;
l_LOAN_LINES_TBL LNS_LOAN_PUB.Loan_Lines_Tbl_Type;
l_LOAN_PART_TBL LNS_LOAN_PUB.LOAN_PART_TBL_TYPE;
l_OPEN_RATES_TBL LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE;
l_TERM_RATES_TBL LNS_LOAN_PUB.LOAN_RATES_TBL_TYPE;
l_loan_cust_sched_tbl LNS_LOAN_PUB.loan_cust_sched_tbl_type;

BEGIN

fnd_global.apps_initialize(1011864, 25323, 206, 0);

MO_GLOBAL.INIT('LNS');
MO_GLOBAL.set_policy_context('S',204);

l_LOAN_DTL_REC.product_id := 10010;
l_LOAN_DTL_REC.LOAN_NUMBER := 'api002';
l_LOAN_DTL_REC.LOAN_DESCRIPTION := 'API Created ERS - all defaulted
from product';

-- set LOAN_ASSIGNED_TO
select resource_id into l_LOAN_DTL_REC.LOAN_ASSIGNED_TO
from jtf_rs_resource_extns
where UPPER(SOURCE_FIRST_NAME) = 'ART'
and category = 'EMPLOYEE'
and rownum <2;

l_LOAN_DTL_REC.legal_entity_id := null;
l_LOAN_DTL_REC.requested_amount := null;
l_LOAN_DTL_REC.LOAN_APPLICATION_DATE := null;
l_LOAN_DTL_REC.EXCHANGE_RATE_TYPE := null;
l_LOAN_DTL_REC.EXCHANGE_DATE := null;
l_LOAN_DTL_REC.EXCHANGE_RATE := null;
l_LOAN_DTL_REC.LOAN_PURPOSE_CODE := null;
l_LOAN_DTL_REC.LOAN_SUBTYPE := null;
l_LOAN_DTL_REC.credit_review_flag := null;

-- set trx_type_id
select cust_trx_type_id into l_LOAN_DTL_REC.trx_type_id
from ra_cust_trx_types_all
where name = 'Loan Principal' and org_id = 204;

l_LOAN_DTL_REC.COLLATERAL_PERCENT := null;
l_LOAN_DTL_REC.CUSTOM_PAYMENTS_FLAG := null;
l_LOAN_DTL_REC.FORGIVENESS_FLAG := null;
l_LOAN_DTL_REC.FORGIVENESS_PERCENT := null;

-- primary borrower attributes

l_LOAN_DTL_REC.primary_borrower_party_id := 1006;
l_LOAN_DTL_REC.CUST_ACCOUNT_ID := 1006;
l_LOAN_DTL_REC.BILL_TO_ACCT_SITE_ID := 1826;
l_LOAN_DTL_REC.contact_rel_party_id := null;
l_LOAN_DTL_REC.CONTACT_PERS_PARTY_ID := null;

-- common term attributes

 l_LOAN_DTL_REC.RATE_TYPE := null;
l_LOAN_DTL_REC.INDEX_RATE_ID := null;
l_LOAN_DTL_REC.DAY_COUNT_METHOD := null;
l_LOAN_DTL_REC.LOAN_PAYMENT_FREQUENCY := null;
l_LOAN_DTL_REC.CALCULATION_METHOD := null;
l_LOAN_DTL_REC.INTEREST_COMPOUNDING_FREQ := null;
l_LOAN_DTL_REC.PAYMENT_CALC_METHOD := null;
l_LOAN_DTL_REC.CUSTOM_CALC_METHOD := null;
l_LOAN_DTL_REC.ORIG_PAY_CALC_METHOD := null;
l_LOAN_DTL_REC.PENAL_INT_RATE := null;
l_LOAN_DTL_REC.PENAL_INT_GRACE_DAYS := null;
l_LOAN_DTL_REC.LOCK_DATE := null;
l_LOAN_DTL_REC.LOCK_EXP_DATE := null;

-- 'term phase' term attributes

l_LOAN_DTL_REC.LOAN_TERM := null;
l_LOAN_DTL_REC.LOAN_TERM_PERIOD := null;
l_LOAN_DTL_REC.balloon_payment_type := null;
l_LOAN_DTL_REC.balloon_payment_amount := null;
l_LOAN_DTL_REC.balloon_term := null;
l_LOAN_DTL_REC.loan_start_date := to_date('01-01-2009','dd-mm-
yyyy');
l_LOAN_DTL_REC.FIRST_PAYMENT_DATE := null;
l_LOAN_DTL_REC.PRIN_FIRST_PAY_DATE := null;
l_LOAN_DTL_REC.PRIN_PAYMENT_FREQUENCY := null;
l_LOAN_DTL_REC.floor_rate := null;
l_LOAN_DTL_REC.ceiling_rate := null;
l_LOAN_DTL_REC.percent_increase := null;
l_LOAN_DTL_REC.percent_increase_life := null;

-- 'disbursement phase' term attributes

l_LOAN_DTL_REC.open_loan_term := null;
l_LOAN_DTL_REC.open_loan_term_period := null;
l_LOAN_DTL_REC.open_loan_start_date := null;
l_LOAN_DTL_REC.open_first_payment_date := null;
l_LOAN_DTL_REC.open_to_term_event := null;
l_LOAN_DTL_REC.open_floor_rate := null;
l_LOAN_DTL_REC.open_ceiling_rate := null;
l_LOAN_DTL_REC.open_percent_increase := null;
l_LOAN_DTL_REC.open_percent_increase_life := null;

-- Terms for Payment attributes

l_LOAN_DTL_REC.REAMORTIZE_OVER_PAYMENT := null;
l_LOAN_DTL_REC.DELINQUENCY_THRESHOLD_AMOUNT := null;
l_LOAN_DTL_REC.PAYMENT_APPLICATION_ORDER := null;
l_LOAN_DTL_REC.PMT_APPL_ORDER_SCOPE := null;

-- additional optional attributes

l_LOAN_DTL_REC.ATTRIBUTE_CATEGORY := null;
l_LOAN_DTL_REC.ATTRIBUTE1 := null;
l_LOAN_DTL_REC.ATTRIBUTE2 := null;
l_LOAN_DTL_REC.ATTRIBUTE3 := null;
l_LOAN_DTL_REC.ATTRIBUTE4 := null;
l_LOAN_DTL_REC.ATTRIBUTE5 := null;
l_LOAN_DTL_REC.ATTRIBUTE6 := null;
l_LOAN_DTL_REC.ATTRIBUTE7 := null;
l_LOAN_DTL_REC.ATTRIBUTE8 := null;
 l_LOAN_DTL_REC.ATTRIBUTE9 := null;
l_LOAN_DTL_REC.ATTRIBUTE10 := null;
l_LOAN_DTL_REC.ATTRIBUTE11 := null;
l_LOAN_DTL_REC.ATTRIBUTE12 := null;
l_LOAN_DTL_REC.ATTRIBUTE13 := null;
l_LOAN_DTL_REC.ATTRIBUTE14 := null;
l_LOAN_DTL_REC.ATTRIBUTE15 := null;
l_LOAN_DTL_REC.ATTRIBUTE16 := null;
l_LOAN_DTL_REC.ATTRIBUTE17 := null;
l_LOAN_DTL_REC.ATTRIBUTE18 := null;
l_LOAN_DTL_REC.ATTRIBUTE19 := null;
l_LOAN_DTL_REC.ATTRIBUTE20 := null;

-- loan lines
l_LOAN_LINES_TBL(1).line_number := 1;
l_LOAN_LINES_TBL(1).payment_schedule_id := 168630;
l_LOAN_LINES_TBL(1).requested_amount := 1000;
l_LOAN_LINES_TBL(1).REFERENCE_DESCRIPTION := 'Description of
Reference1';

l_LOAN_LINES_TBL(2).line_number := 2;
l_LOAN_LINES_TBL(2).payment_schedule_id := 167828;
l_LOAN_LINES_TBL(2).requested_amount := 2000;
l_LOAN_LINES_TBL(2).REFERENCE_DESCRIPTION := 'Description of
Reference2';

-- additional participants
-- primary borrower is created by default based on info from
Loan_Details_Rec_Type
l_LOAN_PART_TBL(1).HZ_PARTY_ID := 1146;
l_LOAN_PART_TBL(1).LOAN_PARTICIPANT_TYPE := 'COBORROWER';
l_LOAN_PART_TBL(1).START_DATE_ACTIVE := sysdate-365;
l_LOAN_PART_TBL(1).END_DATE_ACTIVE := null;
l_LOAN_PART_TBL(1).CUST_ACCOUNT_ID := 1146;
l_LOAN_PART_TBL(1).BILL_TO_ACCT_SITE_ID := 1187;
l_LOAN_PART_TBL(1).CONTACT_PERS_PARTY_ID := null;
l_LOAN_PART_TBL(1).CONTACT_REL_PARTY_ID := null;

--rate schedule
l_LOAN_RATES_TBL(1).INDEX_RATE := 10;
l_LOAN_RATES_TBL(1).SPREAD := 2;
l_LOAN_RATES_TBL(1).BEGIN_INSTALLMENT_NUMBER := 1;
l_LOAN_RATES_TBL(1).END_INSTALLMENT_NUMBER := -1; -- will be
corrected by the api
l_LOAN_RATES_TBL(1).INTEREST_ONLY_FLAG := null;

dbms_output.put_line ( 'Calling LNS_LOAN_PUB.CREATE_LOAN...');

LNS_LOAN_PUB.CREATE_LOAN(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => 'T',
P_COMMIT => 'F',
P_VALIDATION_LEVEL => 100,
P_Loan_Details_Rec => l_LOAN_DTL_REC,
P_Loan_Lines_Tbl => l_LOAN_LINES_TBL,
P_LOAN_PART_TBL => l_LOAN_PART_TBL,
P_OPEN_RATES_TBL => l_OPEN_RATES_TBL,
P_TERM_RATES_TBL => l_TERM_RATES_TBL,
 p_loan_cust_sched_tbl => l_loan_cust_sched_tbl,
P_Application_id => 206,
P_Created_by_module => 'test script',
X_LOAN_ID => l_loan_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);

dbms_output.put_line ( 'l_return_status ' || l_return_status);

dbms_output.put_line ( 'l_msg_count ' || l_msg_count);

IF l_return_status <> 'S' THEN

dbms_output.put_line('Errors:');
while (l_indexNo <= l_msg_Count ) loop
fnd_msg_pub.get(l_indexNo, 'F', l_msg, l_index);
dbms_output.put_line(l_msg);
l_indexNo := l_indexNo + 1;
End Loop;

ELSE
commit;
dbms_output.put_line('Success!') ;
dbms_output.put_line('New loan_id: ' || l_loan_id);
dbms_output.put_line('New loan number: ' ||
l_LOAN_DTL_REC.loan_number);
END IF;

END;
/