eTRM uses javascript that opens and focuses a separate window to display reports. Your browser does not appear to support javascript. This should not prevent you from using eTRM but it does mean the window focus feature will not work. Please check the contents of other windows on your desktop if you click on a link that does not appear to do anything.

APPS.LNS_BILLING_BATCH_PUB dependencies on LNS_LOAN_HEADERS

Line 281: | This procedure stores last payment number and last amortization id in lns_loan_headers_all

277: /*========================================================================
278: | PRIVATE PROCEDURE STORE_LAST_PAYMENT_NUMBER
279: |
280: | DESCRIPTION
281: | This procedure stores last payment number and last amortization id in lns_loan_headers_all
282: |
283: | CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
284: |
285: | CALLS PROCEDURES/FUNCTIONS (local to this package body)

Line 339: LNS_LOAN_HEADERS head

335: head.LAST_PAYMENT_NUMBER,
336: head.LAST_AMORTIZATION_ID,
337: nvl(head.CURRENT_PHASE, 'TERM')
338: from
339: LNS_LOAN_HEADERS head
340: where
341: head.loan_id = P_LOAN_ID;
342:
343: CURSOR pay_number_cur(P_LOAN_ID NUMBER, P_PHASE VARCHAR2) IS

Line 418: LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');

414:
415: LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
416:
417: IF l_return_status = 'S' THEN
418: LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');
419: ELSE
420: FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
421: FND_MSG_PUB.Add;
422: LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));

Line 510: LNS_LOAN_HEADERS head

506: head.loan_id,
507: head.loan_number
508: from
509: LNS_PAY_SUM_V sum,
510: LNS_LOAN_HEADERS head
511: where
512: head.loan_id = nvl(P_LOAN_ID, head.loan_id) and
513: head.loan_id = sum.loan_id and
514: head.loan_status in ('ACTIVE', 'DEFAULT', 'DELINQUENT') and

Line 622: LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');

618:
619: LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
620:
621: IF l_return_status = 'S' THEN
622: LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');
623: ELSE
624: FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
625: FND_MSG_PUB.Add;
626: LogMessage(FND_LOG.LEVEL_UNEXPECTED, FND_MSG_PUB.Get(p_encoded => 'F'));

Line 747: LNS_LOAN_HEADERS head

743: head.OBJECT_VERSION_NUMBER,
744: head.loan_id,
745: head.loan_number
746: from
747: LNS_LOAN_HEADERS head
748: where
749: head.loan_status = 'PAIDOFF' and
750: ((select nvl(sum(total_remaining_amount),0)
751: from LNS_AM_SCHEDS_V

Line 795: LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully updated LNS_LOAN_HEADERS_ALL');

791:
792: LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
793:
794: IF l_return_status = 'S' THEN
795: LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully updated LNS_LOAN_HEADERS_ALL');
796: ELSE
797: FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
798: FND_MSG_PUB.Add;
799: LogMessage(FND_LOG.LEVEL_ERROR, FND_MSG_PUB.Get(p_encoded => 'F'));

Line 1215: from LNS_LOAN_HEADERS

1211:
1212: /* query trx_type_id */
1213: CURSOR trx_type_cur(P_LOAN_ID number) IS
1214: select REFERENCE_TYPE_ID
1215: from LNS_LOAN_HEADERS
1216: where loan_id = P_LOAN_ID;
1217:
1218: /* query for site_use_id */
1219: CURSOR site_use_id_cur(P_SITE_ID number) IS

Line 3094: from lns_loan_headers loan,

3090: to_char(P_PRIN_AMOUNT_DUE, FND_CURRENCY.SAFE_GET_FORMAT_MASK(loan.LOAN_CURRENCY,50)),
3091: to_char(P_INT_AMOUNT_DUE, FND_CURRENCY.SAFE_GET_FORMAT_MASK(loan.LOAN_CURRENCY,50)),
3092: to_char(P_FEE_AMOUNT_DUE, FND_CURRENCY.SAFE_GET_FORMAT_MASK(loan.LOAN_CURRENCY,50)),
3093: to_char((P_PRIN_AMOUNT_DUE + P_INT_AMOUNT_DUE + P_FEE_AMOUNT_DUE), FND_CURRENCY.SAFE_GET_FORMAT_MASK(loan.LOAN_CURRENCY,50))
3094: from lns_loan_headers loan,
3095: hz_parties party
3096: where party.party_id = loan.PRIMARY_BORROWER_ID and
3097: loan.loan_id = P_LOAN_ID;
3098:

Line 3233: select loan_number from lns_loan_headers where loan_id = P_LOAN_ID;

3229: select party_name from hz_parties party where party_id = P_BORROWER_ID;
3230:
3231: /* query for loan number */
3232: CURSOR loan_cur(P_LOAN_ID number) IS
3233: select loan_number from lns_loan_headers where loan_id = P_LOAN_ID;
3234:
3235: /* query for org name */
3236: CURSOR org_cur(P_ORG_ID number) IS
3237: select name

Line 3414: from lns_loan_headers_all loan

3410: +-----------------------------------------------------------------------*/
3411:
3412: CURSOR c_get_currency_format_mask(pLoanId Number) is
3413: select FND_CURRENCY.SAFE_GET_FORMAT_MASK(loan.LOAN_CURRENCY,50) mask
3414: from lns_loan_headers_all loan
3415: where loan.loan_id = pLoanId;
3416:
3417: BEGIN
3418:

Line 3559: 'FROM lns_loan_headers_all_vl loan, ' ||

3555: 'payment_history.AMORTIZATION_SCHEDULE_ID < PAY_SUM.next_payment_amortization_id ' ||
3556: 'order by payment_history.AMORTIZATION_SCHEDULE_ID) ' ||
3557: 'AS Recent_Payment_History ' ||
3558:
3559: 'FROM lns_loan_headers_all_vl loan, ' ||
3560: 'hz_parties party, ' ||
3561: 'fnd_territories_tl terr, ' ||
3562: 'hr_all_organization_units_tl org, ' ||
3563: 'LNS_PAY_SUM_V PAY_SUM, ' ||

Line 3736: lns_loan_headers head

3732: CURSOR verify_amount_cur(P_LOAN_ID number) IS
3733: select nvl(sum(am.PRINCIPAL_AMOUNT), 0)
3734: from
3735: LNS_AMORTIZATION_SCHEDS am,
3736: lns_loan_headers head
3737: where
3738: head.loan_id = P_LOAN_ID
3739: and head.loan_id = am.LOAN_ID
3740: and (am.REVERSED_FLAG is null or am.REVERSED_FLAG = 'N')

Line 3885: from LNS_LOAN_HEADERS loan,

3881:
3882: /* get statement after its billed */
3883: CURSOR get_statement_cur(P_LOAN_ID number) IS
3884: select STATEMENT_XML
3885: from LNS_LOAN_HEADERS loan,
3886: lns_amortization_scheds am
3887: where loan.loan_id = am.loan_id and
3888: am.AMORTIZATION_SCHEDULE_ID = loan.LAST_AMORTIZATION_ID and
3889: am.PAYMENT_NUMBER = loan.LAST_PAYMENT_NUMBER and

Line 3895: from LNS_LOAN_HEADERS

3891:
3892: -- getting loan version
3893: CURSOR loan_version_cur(P_LOAN_ID number) IS
3894: select OBJECT_VERSION_NUMBER
3895: from LNS_LOAN_HEADERS
3896: where LOAN_ID = P_LOAN_ID;
3897: /*
3898: cursor cur_floating(p_loan_id number, p_phase varchar2, p_installment number) is
3899: select nvl(floating_flag, 'N')

Line 4307: LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');

4303:
4304: LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
4305:
4306: IF l_return_status = 'S' THEN
4307: LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');
4308: ELSE
4309: -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: LNS_LOAN_HEADER_PUB.UPDATE_LOAN returned error: ' || substr(l_msg_data,1,225));
4310: FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
4311: FND_MSG_PUB.Add;

Line 4495: from LNS_LOAN_HEADERS

4491:
4492: /* get loan current phase */
4493: CURSOR loan_cur_phase_cur(P_LOAN_ID number) IS
4494: select nvl(CURRENT_PHASE, 'TERM')
4495: from LNS_LOAN_HEADERS
4496: where LOAN_ID = P_LOAN_ID;
4497:
4498: BEGIN
4499:

Line 4596: LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');

4592:
4593: LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_return_status: ' || l_return_status);
4594:
4595: IF l_return_status = 'S' THEN
4596: LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');
4597: ELSE
4598: -- LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'ERROR: LNS_LOAN_HEADER_PUB.UPDATE_LOAN returned error: ' || substr(l_msg_data,1,225));
4599: FND_MESSAGE.SET_NAME('LNS', 'LNS_UPD_LOAN_FAIL');
4600: FND_MSG_PUB.Add;

Line 5350: from LNS_LOAN_HEADERS_VL head,

5346: nvl(head.CURRENT_PHASE, 'TERM'),
5347: nvl(head.FORGIVENESS_FLAG, 'N'),
5348: nvl(head.FORGIVENESS_PERCENT, 0),
5349: nvl(head.DISABLE_BILLING_FLAG, 'N')
5350: from LNS_LOAN_HEADERS_VL head,
5351: LNS_TERMS term
5352: where head.LOAN_STATUS in ('ACTIVE', 'DEFAULT', 'DELINQUENT')
5353: and head.loan_id = term.loan_id
5354: and LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(head.LOAN_ID) < LNS_FIN_UTILS.getNumberInstallments(head.LOAN_ID, nvl(head.current_phase, 'TERM'))

Line 5738: from LNS_LOAN_HEADERS head,

5734: head.LOAN_NUMBER,
5735: head.OBJECT_VERSION_NUMBER,
5736: LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(head.LOAN_ID) + 1,
5737: head.CUSTOM_PAYMENTS_FLAG
5738: from LNS_LOAN_HEADERS head,
5739: LNS_TERMS term
5740: where head.LOAN_STATUS in ('ACTIVE', 'DEFAULT', 'DELINQUENT')
5741: and head.loan_id = term.loan_id
5742: and (head.BILLED_FLAG = 'Y' or head.BILLED_FLAG is null) -- scherkas; fix for bug 5687852

Line 5978: from LNS_LOAN_HEADERS

5974: select
5975: LOAN_NUMBER,
5976: OBJECT_VERSION_NUMBER,
5977: CUSTOM_PAYMENTS_FLAG
5978: from LNS_LOAN_HEADERS
5979: where LOAN_ID = P_LOAN_ID;
5980:
5981: CURSOR terms_cur(P_LOAN_ID number) IS
5982: select rate_type

Line 6406: from LNS_LOAN_HEADERS

6402: select
6403: LOAN_NUMBER,
6404: OBJECT_VERSION_NUMBER,
6405: CUSTOM_PAYMENTS_FLAG
6406: from LNS_LOAN_HEADERS
6407: where LOAN_ID = P_LOAN_ID;
6408:
6409: BEGIN
6410:

Line 6667: from LNS_LOAN_HEADERS_VL head,

6663: nvl(head.CURRENT_PHASE, 'TERM'),
6664: nvl(head.FORGIVENESS_FLAG, 'N'),
6665: nvl(head.FORGIVENESS_PERCENT, 0),
6666: decode(am.AMORTIZATION_SCHEDULE_ID, null, head.FUNDED_AMOUNT, am.FUNDED_AMOUNT)
6667: from LNS_LOAN_HEADERS_VL head,
6668: LNS_TERMS term,
6669: LNS_AMORTIZATION_SCHEDS am
6670: where head.LOAN_ID = P_LOAN_ID
6671: -- Bug#6830765 - Enable billing for all loan status, used in Application fee 'SubmitForApproval Fee'

Line 6683: from LNS_LOAN_HEADERS

6679:
6680: -- getting loan version
6681: CURSOR loan_version_cur(P_LOAN_ID number) IS
6682: select OBJECT_VERSION_NUMBER
6683: from LNS_LOAN_HEADERS
6684: where LOAN_ID = P_LOAN_ID;
6685:
6686:
6687: BEGIN

Line 7249: l_lns_status LNS_LOAN_HEADERS_ALL.loan_status%TYPE;

7245: l_TRX_NUMBER varchar2(20);
7246: l_ORG_ID number;
7247:
7248: l_reverse_tbl LNS_BILLING_BATCH_PUB.REVERSE_TBL;
7249: l_lns_status LNS_LOAN_HEADERS_ALL.loan_status%TYPE;
7250:
7251: /*-----------------------------------------------------------------------+
7252: | Cursor Declarations |
7253: +-----------------------------------------------------------------------*/

Line 7426: SELECT lhdr.loan_status INTO l_lns_status FROM lns_loan_headers lhdr WHERE lhdr.loan_id = l_LOAN_ID;

7422: ,P_REVERSED_DATE => sysdate);
7423:
7424: LogMessage(FND_LOG.LEVEL_STATEMENT, 'Update successfull!');
7425:
7426: SELECT lhdr.loan_status INTO l_lns_status FROM lns_loan_headers lhdr WHERE lhdr.loan_id = l_LOAN_ID;
7427:
7428: LogMessage(FND_LOG.LEVEL_PROCEDURE, 'The LoanStatus of loanid '||l_LOAN_ID||' is '||l_lns_status) ;
7429:
7430: -- If the loanStatus is INCOMPLETE, then only update the lns_fee_schedules.billed_flag = 'N' for the submitForApproval fees

Line 7592: from LNS_LOAN_HEADERS_VL loan,

7588: trx.legal_entity_id,
7589: lines.REC_ADJUSTMENT_ID,
7590: nvl(lines.installment_number, 1),
7591: loan.LOAN_START_DATE
7592: from LNS_LOAN_HEADERS_VL loan,
7593: RA_CUSTOMER_TRX trx,
7594: ar_payment_schedules psa,
7595: lns_loan_lines lines
7596: where loan.loan_id = P_LOAN_ID and

Line 7924: from LNS_LOAN_HEADERS

7920: /* querying loan number */
7921: CURSOR loan_cur(P_LOAN_ID number) IS
7922: select
7923: LOAN_NUMBER, nvl(CURRENT_PHASE, 'TERM')
7924: from LNS_LOAN_HEADERS
7925: where LOAN_ID = P_LOAN_ID;
7926:
7927: BEGIN
7928:

Line 8229: from LNS_LOAN_HEADERS loan

8225: select
8226: nvl(loan.FORGIVENESS_FLAG, 'N'),
8227: nvl(loan.FORGIVENESS_PERCENT, 0),
8228: loan.LOAN_CURRENCY
8229: from LNS_LOAN_HEADERS loan
8230: where
8231: loan.loan_id = P_LOAN_ID;
8232:
8233: BEGIN

Line 8408: from LNS_LOAN_HEADERS_VL loan,

8404: psa.GL_DATE,
8405: nvl(loan.CURRENT_PHASE, 'TERM'),
8406: psa.org_id,
8407: loan.LOAN_CURRENCY
8408: from LNS_LOAN_HEADERS_VL loan,
8409: lns_amortization_scheds am,
8410: ar_payment_schedules psa
8411: where
8412: loan.loan_id = P_LOAN_ID and

Line 8683: from lns_loan_headers_all

8679: +-----------------------------------------------------------------------*/
8680:
8681: cursor c_loanInfo(p_loan_id number) is
8682: select loan_number, LOAN_CURRENCY, nvl(EXCHANGE_RATE, 1), nvl(CURRENT_PHASE, 'TERM')
8683: from lns_loan_headers_all
8684: where loan_id = p_loan_id;
8685:
8686: /* query trx_type_id */
8687: CURSOR prin_trx_type_cur(P_LOAN_ID number) IS

Line 8689: from LNS_LOAN_HEADERS_ALL

8685:
8686: /* query trx_type_id */
8687: CURSOR prin_trx_type_cur(P_LOAN_ID number) IS
8688: select REFERENCE_TYPE_ID
8689: from LNS_LOAN_HEADERS_ALL
8690: where loan_id = P_LOAN_ID;
8691:
8692: CURSOR trx_type_cur(p_trx_type_id number) IS
8693: select NAME

Line 9080: from LNS_LOAN_HEADERS_ALL_VL head,

9076: head.EXCHANGE_RATE,
9077: nvl(head.FORGIVENESS_FLAG, 'N'),
9078: nvl(head.FORGIVENESS_PERCENT, 0),
9079: nvl(head.CURRENT_PHASE,'TERM')
9080: from LNS_LOAN_HEADERS_ALL_VL head,
9081: LNS_TERMS term
9082: where head.loan_id = term.loan_id
9083: and head.LOAN_ID = P_LOAN_ID;
9084:

Line 9087: from LNS_LOAN_HEADERS_all loan

9083: and head.LOAN_ID = P_LOAN_ID;
9084:
9085: CURSOR get_last_am_cur(P_LOAN_ID number) IS
9086: select loan.LAST_AMORTIZATION_ID
9087: from LNS_LOAN_HEADERS_all loan
9088: where loan.loan_id = P_LOAN_ID;
9089:
9090: BEGIN
9091:

Line 9375: lns_loan_headers loan,

9371: psa.invoice_currency_code,
9372: nvl(loan.EXCHANGE_RATE, 1)
9373: from
9374: lns_amortization_scheds am,
9375: lns_loan_headers loan,
9376: ar_payment_schedules psa,
9377: lns_lookups look
9378: where
9379: am.loan_id = P_LOAN_ID and

Line 9820: from LNS_LOAN_HEADERS head,

9816: head.LOAN_NUMBER,
9817: head.OBJECT_VERSION_NUMBER,
9818: LNS_BILLING_UTIL_PUB.LAST_PAYMENT_NUMBER(head.LOAN_ID) + 1,
9819: head.CUSTOM_PAYMENTS_FLAG
9820: from LNS_LOAN_HEADERS head,
9821: LNS_TERMS term
9822: where head.LOAN_STATUS in ('ACTIVE', 'DEFAULT', 'DELINQUENT')
9823: and head.loan_id = term.loan_id
9824: and (head.BILLED_FLAG = 'Y' or head.BILLED_FLAG is null)

Line 9832: from LNS_LOAN_HEADERS head,

9828: and head.LOAN_ID = P_LOAN_ID;
9829:
9830: CURSOR can_bill_cur(P_LOAN_ID NUMBER) IS
9831: select 'x'
9832: from LNS_LOAN_HEADERS head,
9833: LNS_TERMS term
9834: where head.LOAN_STATUS in ('ACTIVE', 'DEFAULT', 'DELINQUENT')
9835: and head.loan_id = term.loan_id
9836: and (head.BILLED_FLAG is null or head.BILLED_FLAG = 'N')

Line 10016: lns_loan_headers loan,

10012: nvl(loan.EXCHANGE_RATE, 1),
10013: trx_type.name
10014: from
10015: lns_amortization_scheds am,
10016: lns_loan_headers loan,
10017: ar_payment_schedules psa,
10018: RA_CUST_TRX_TYPES_ALL trx_type
10019: where
10020: am.loan_id = P_LOAN_ID and

Line 10205: from LNS_LOAN_HEADERS_all loan

10201: +-----------------------------------------------------------------------*/
10202:
10203: CURSOR get_last_am_cur(P_LOAN_ID number) IS
10204: select loan.LAST_AMORTIZATION_ID
10205: from LNS_LOAN_HEADERS_all loan
10206: where loan.loan_id = P_LOAN_ID;
10207:
10208: CURSOR get_new_am_cur(P_LOAN_ID number, P_LAST_AM_SCHED number) IS
10209: select AMORTIZATION_SCHEDULE_ID

Line 10441: from LNS_LOAN_HEADERS

10437: CURSOR loan_cur(P_LOAN_ID number) IS
10438: select LOAN_NUMBER,
10439: nvl(current_phase, 'TERM'),
10440: decode(current_phase, 'OPEN', OPEN_LOAN_START_DATE, LOAN_START_DATE)
10441: from LNS_LOAN_HEADERS
10442: where LOAN_ID = P_LOAN_ID;
10443:
10444: BEGIN
10445:

Line 10798: FROM LNS_LOAN_HEADERS_ALL head

10794: +-----------------------------------------------------------------------*/
10795:
10796: CURSOR cur_disable_bill(C_LOAN_ID NUMBER) IS
10797: SELECT head.DISABLE_BILLING_FLAG
10798: FROM LNS_LOAN_HEADERS_ALL head
10799: where head.loan_id = C_LOAN_ID;
10800: BEGIN
10801:
10802: --LogMessage(FND_LOG.LEVEL_PROCEDURE, G_PKG_NAME || '.' || l_api_name || ' +');

Line 10939: from LNS_LOAN_HEADERS_VL loan,

10935: trx.org_id,
10936: trx.legal_entity_id,
10937: lines.REC_ADJUSTMENT_ID,
10938: nvl(lines.installment_number, 1)
10939: from LNS_LOAN_HEADERS_VL loan,
10940: RA_CUSTOMER_TRX trx,
10941: ar_payment_schedules psa,
10942: lns_loan_lines lines
10943: where lines.LOAN_LINE_ID = P_LOAN_LINE_ID and