[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
select
CASE
WHEN ((nvl(loan.CURRENT_PHASE, 'TERM') = 'OPEN' and (loan.LOAN_STATUS = 'ACTIVE' OR loan.LOAN_STATUS = 'APPROVED')) OR
(nvl(loan.CURRENT_PHASE, 'TERM') = 'TERM' and loan.MULTIPLE_FUNDING_FLAG = 'N' and loan.LOAN_STATUS = 'APPROVED'))
THEN
CASE
/*
WHEN ((select count(1) from LNS_COND_ASSIGNMENTS where DISB_HEADER_ID = head.DISB_HEADER_ID and
MANDATORY_FLAG = 'Y' and (CONDITION_MET_FLAG is null or CONDITION_MET_FLAG = 'N') and
(end_date_active is null or trunc(end_date_active) > trunc(sysdate))) > 0) THEN
'N'
ELSE
CASE
*/
WHEN (head.status = 'IN_FUNDING') THEN
'N'
WHEN (head.status = 'FULLY_FUNDED') THEN
'N'
WHEN (head.status = 'PARTIALLY_FUNDED') THEN
CASE
WHEN ((select nvl(count(1), 0) from lns_disb_lines
where disb_header_id = head.DISB_HEADER_ID and (status is null or status = 'FUNDING_ERROR')) > 0) THEN
'Y'
ELSE
'N'
END
WHEN (head.status = 'FUNDING_ERROR') THEN
'Y'
WHEN (head.status = 'CANCELLED') THEN
'N'
WHEN (head.status is null) THEN
CASE
WHEN ((select nvl(max(DISBURSEMENT_NUMBER), 0) + 1 from lns_disb_headers
where loan_id = head.loan_id and status = 'FULLY_FUNDED') = head.DISBURSEMENT_NUMBER) THEN
'Y'
ELSE
'N'
END
END
-- END
ELSE
'N'
END
from lns_disb_headers head,
lns_loan_headers loan
where head.DISB_HEADER_ID = P_DISB_HEADER_ID and
head.LOAN_ID = loan.LOAN_ID;
select
CASE
WHEN ((nvl(loan.CURRENT_PHASE, 'TERM') = 'OPEN' and
(loan.LOAN_STATUS = 'ACTIVE' OR loan.LOAN_STATUS = 'APPROVED')) OR
(nvl(loan.CURRENT_PHASE, 'TERM') = 'TERM' and
loan.MULTIPLE_FUNDING_FLAG = 'N' and loan.LOAN_STATUS = 'APPROVED'))
THEN
CASE
WHEN ((select nvl(count(1), 0) from lns_disb_lines line, lns_disb_headers head
where head.loan_id = loan.LOAN_ID and head.disb_header_id = line.disb_header_id and
(line.status is null or line.status = 'FUNDING_ERROR' or line.status = 'IN_FUNDING')) > 0) THEN
'Y'
ELSE
'N'
END
ELSE
'N'
END
from lns_loan_headers_all loan
where loan.LOAN_ID = P_LOAN_ID;
select
CASE
WHEN ((nvl(loan.CURRENT_PHASE, 'TERM') = 'OPEN' and (loan.LOAN_STATUS = 'ACTIVE' OR loan.LOAN_STATUS = 'APPROVED')) OR
(nvl(loan.CURRENT_PHASE, 'TERM') = 'TERM' and loan.MULTIPLE_FUNDING_FLAG = 'N' and loan.LOAN_STATUS = 'APPROVED'))
THEN
CASE
WHEN (head.status is not null and (head.status = 'FULLY_FUNDED' or head.status = 'CANCELLED' or head.status = 'IN_FUNDING')) THEN
'Y'
WHEN (head.status is not null and head.status = 'PARTIALLY_FUNDED') THEN
CASE
WHEN ((select nvl(count(1), 0) from lns_disb_lines
where disb_header_id = head.DISB_HEADER_ID and (status is null or status = 'FUNDING_ERROR')) > 0) THEN
'N'
ELSE
'Y'
END
ELSE
'N'
END
ELSE
'Y'
END
from lns_disb_headers head,
lns_loan_headers_all loan
where head.DISB_HEADER_ID = P_DISB_HEADER_ID and
head.loan_id = loan.loan_id;
select
CASE
WHEN (loan.loan_status = 'INCOMPLETE') THEN
'N'
WHEN LNS_FUNDING_PUB.IS_DISB_HDR_READ_ONLY(line.DISB_HEADER_ID) = 'Y' THEN
'Y'
ELSE
CASE
WHEN (line.status is not null and (line.status = 'FULLY_FUNDED' or
line.status = 'PARTIALLY_FUNDED' or line.status = 'CANCELLED' or
line.status = 'IN_FUNDING'))
THEN
'Y'
ELSE
'N'
END
END
from lns_disb_lines line,
lns_disb_headers head,
lns_loan_headers_all loan
where line.DISB_LINE_ID = P_DISB_LINE_ID and
line.disb_header_id = head.disb_header_id and
head.loan_id = loan.loan_id;
select
CASE
WHEN (loan.current_phase = 'OPEN' and loan.OPEN_TO_TERM_FLAG = 'Y' and loan.OPEN_TO_TERM_EVENT = 'AUTO_FINAL_DISBURSEMENT') THEN
CASE
WHEN ((select max(DISBURSEMENT_NUMBER) from lns_disb_headers where loan_id = loan.loan_id) = head.DISBURSEMENT_NUMBER) THEN
'Y'
ELSE
'N'
END
ELSE
'N'
END
from lns_disb_headers head,
lns_loan_headers_all loan
where head.DISB_HEADER_ID = P_DISB_HEADER_ID and
head.loan_id = loan.loan_id;
| PUBLIC PROCEDURE INSERT_DISB_HEADER
|
| DESCRIPTION
| This procedure inserts new disbursement header
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| None
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| LogMessage
|
| PARAMETERS
| P_API_VERSION IN Standard in parameter
| P_INIT_MSG_LIST IN Standard in parameter
| P_COMMIT IN Standard in parameter
| P_VALIDATION_LEVEL IN Standard in parameter
| P_DISB_HEADER_REC IN LNS_FUNDING_PUB.LNS_DISB_HEADERS_REC
| X_RETURN_STATUS OUT NOCOPY Standard out parameter
| X_MSG_COUNT OUT NOCOPY Standard out parameter
| X_MSG_DATA OUT NOCOPY Standard out parameter
|
| KNOWN ISSUES
| None
|
| NOTES
| Any interesting aspect of the code in the package body which needs
| to be stated.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 09-22-2004 scherkas Created
|
*=======================================================================*/
PROCEDURE INSERT_DISB_HEADER(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_VALIDATION_LEVEL IN NUMBER,
P_DISB_HEADER_REC IN LNS_FUNDING_PUB.LNS_DISB_HEADERS_REC,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_DISB_HEADER';
SAVEPOINT INSERT_DISB_HEADER;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting disbursement header...');
LNS_DISB_HEADERS_PKG.Insert_Row(
X_DISB_HEADER_ID => l_pk_id,
P_LOAN_ID => P_DISB_HEADER_REC.LOAN_ID,
P_ACTIVITY_CODE => P_DISB_HEADER_REC.ACTIVITY_CODE,
P_DISBURSEMENT_NUMBER => P_DISB_HEADER_REC.DISBURSEMENT_NUMBER,
P_HEADER_AMOUNT => P_DISB_HEADER_REC.HEADER_AMOUNT,
P_HEADER_PERCENT => P_DISB_HEADER_REC.HEADER_PERCENT,
P_STATUS => P_DISB_HEADER_REC.STATUS,
P_TARGET_DATE => P_DISB_HEADER_REC.TARGET_DATE,
P_PAYMENT_REQUEST_DATE => P_DISB_HEADER_REC.PAYMENT_REQUEST_DATE,
P_OBJECT_VERSION_NUMBER => nvl(P_DISB_HEADER_REC.OBJECT_VERSION_NUMBER, 1),
P_AUTOFUNDING_FLAG => P_DISB_HEADER_REC.AUTOFUNDING_FLAG);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully inserted disbursement header' || l_pk_id);
ROLLBACK TO INSERT_DISB_HEADER;
ROLLBACK TO INSERT_DISB_HEADER;
ROLLBACK TO INSERT_DISB_HEADER;
| PUBLIC PROCEDURE UPDATE_DISB_HEADER
|
| DESCRIPTION
| This procedure updates disbursement header
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| None
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| init
| LogMessage
|
| PARAMETERS
| P_API_VERSION IN Standard in parameter
| P_INIT_MSG_LIST IN Standard in parameter
| P_COMMIT IN Standard in parameter
| P_VALIDATION_LEVEL IN Standard in parameter
| P_DISB_HEADER_REC IN LNS_FUNDING_PUB.LNS_DISB_HEADERS_REC
| X_RETURN_STATUS OUT NOCOPY Standard out parameter
| X_MSG_COUNT OUT NOCOPY Standard out parameter
| X_MSG_DATA OUT NOCOPY Standard out parameter
|
| KNOWN ISSUES
| None
|
| NOTES
| Any interesting aspect of the code in the package body which needs
| to be stated.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 09-22-2004 scherkas Created
|
*=======================================================================*/
PROCEDURE UPDATE_DISB_HEADER(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_VALIDATION_LEVEL IN NUMBER,
P_DISB_HEADER_REC IN LNS_FUNDING_PUB.LNS_DISB_HEADERS_REC,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_DISB_HEADER';
select OBJECT_VERSION_NUMBER
from lns_disb_headers
where disb_header_id = P_DISB_HEADER_ID;
SAVEPOINT UPDATE_DISB_HEADER;
LNS_DISB_HEADERS_PKG.Update_Row(
P_DISB_HEADER_ID => P_DISB_HEADER_REC.DISB_HEADER_ID,
P_LOAN_ID => P_DISB_HEADER_REC.LOAN_ID,
P_ACTIVITY_CODE => P_DISB_HEADER_REC.ACTIVITY_CODE,
P_DISBURSEMENT_NUMBER => P_DISB_HEADER_REC.DISBURSEMENT_NUMBER,
P_HEADER_AMOUNT => P_DISB_HEADER_REC.HEADER_AMOUNT,
P_HEADER_PERCENT => P_DISB_HEADER_REC.HEADER_PERCENT,
P_STATUS => P_DISB_HEADER_REC.STATUS,
P_TARGET_DATE => P_DISB_HEADER_REC.TARGET_DATE,
P_PAYMENT_REQUEST_DATE => P_DISB_HEADER_REC.PAYMENT_REQUEST_DATE,
P_OBJECT_VERSION_NUMBER => l_disb_header_version,
P_AUTOFUNDING_FLAG => P_DISB_HEADER_REC.AUTOFUNDING_FLAG);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully updated disbursement header ' || P_DISB_HEADER_REC.DISB_HEADER_ID);
ROLLBACK TO UPDATE_DISB_HEADER;
ROLLBACK TO UPDATE_DISB_HEADER;
ROLLBACK TO UPDATE_DISB_HEADER;
| PUBLIC PROCEDURE DELETE_DISB_HEADER
|
| DESCRIPTION
| This procedure updates disbursement header
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| None
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| init
| LogMessage
|
| PARAMETERS
| P_API_VERSION IN Standard in parameter
| P_INIT_MSG_LIST IN Standard in parameter
| P_COMMIT IN Standard in parameter
| P_VALIDATION_LEVEL IN Standard in parameter
| P_DISB_HEADER_IDC IN Disbursement Header ID
| X_RETURN_STATUS OUT NOCOPY Standard out parameter
| X_MSG_COUNT OUT NOCOPY Standard out parameter
| X_MSG_DATA OUT NOCOPY Standard out parameter
|
| KNOWN ISSUES
| None
|
| NOTES
| Any interesting aspect of the code in the package body which needs
| to be stated.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 09-22-2004 scherkas Created
|
*=======================================================================*/
PROCEDURE DELETE_DISB_HEADER(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_VALIDATION_LEVEL IN NUMBER,
P_DISB_HEADER_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_DISB_HEADER';
select status
from lns_disb_headers
where disb_header_id = P_DISB_HEADER_ID;
select
DISB_LINE_ID
from LNS_DISB_LINES
where DISB_HEADER_ID = P_DISB_HEADER_ID;
SAVEPOINT DELETE_DISB_HEADER;
DELETE_DISB_LINE(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_FALSE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_DISB_LINE_ID => l_disb_line_id,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Call to DELETE_DISB_LINE failed');
LNS_FEE_ASSIGNMENT_PUB.delete_DISB_FEE_ASSIGNMENT(P_DISB_HEADER_ID);
LNS_COND_ASSIGNMENT_PUB.delete_DISB_COND_ASSIGNMENT(P_DISB_HEADER_ID);
LNS_DISB_HEADERS_PKG.Delete_Row(P_DISB_HEADER_ID);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully deleted disbursement header ' || P_DISB_HEADER_ID);
ROLLBACK TO DELETE_DISB_HEADER;
ROLLBACK TO DELETE_DISB_HEADER;
ROLLBACK TO DELETE_DISB_HEADER;
| PUBLIC PROCEDURE INSERT_DISB_LINE
|
| DESCRIPTION
| This procedure inserts new disbursement LINE
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| None
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| init
| LogMessage
|
| PARAMETERS
| P_API_VERSION IN Standard in parameter
| P_INIT_MSG_LIST IN Standard in parameter
| P_COMMIT IN Standard in parameter
| P_VALIDATION_LEVEL IN Standard in parameter
| P_DISB_LINE_REC IN LNS_FUNDING_PUB.LNS_DISB_LINES_REC
| X_RETURN_STATUS OUT NOCOPY Standard out parameter
| X_MSG_COUNT OUT NOCOPY Standard out parameter
| X_MSG_DATA OUT NOCOPY Standard out parameter
|
| KNOWN ISSUES
| None
|
| NOTES
| Any interesting aspect of the code in the package body which needs
| to be stated.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 09-22-2004 scherkas Created
|
*=======================================================================*/
PROCEDURE INSERT_DISB_LINE(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_VALIDATION_LEVEL IN NUMBER,
P_DISB_LINE_REC IN LNS_FUNDING_PUB.LNS_DISB_LINES_REC,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'INSERT_DISB_LINE';
SAVEPOINT INSERT_DISB_LINE;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting disbursement line...');
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling LNS_DISB_LINES_PKG.Insert_Row');
LNS_DISB_LINES_PKG.Insert_Row(
X_DISB_LINE_ID => l_pk_id,
P_DISB_HEADER_ID => P_DISB_LINE_REC.DISB_HEADER_ID,
P_DISB_LINE_NUMBER => P_DISB_LINE_REC.DISB_LINE_NUMBER,
P_LINE_AMOUNT => P_DISB_LINE_REC.LINE_AMOUNT,
P_LINE_PERCENT => P_DISB_LINE_REC.LINE_PERCENT,
P_PAYEE_PARTY_ID => P_DISB_LINE_REC.PAYEE_PARTY_ID,
P_BANK_ACCOUNT_ID => P_DISB_LINE_REC.BANK_ACCOUNT_ID,
P_PAYMENT_METHOD_CODE => P_DISB_LINE_REC.PAYMENT_METHOD_CODE,
P_STATUS => P_DISB_LINE_REC.STATUS,
P_REQUEST_DATE => P_DISB_LINE_REC.REQUEST_DATE,
P_DISBURSEMENT_DATE => P_DISB_LINE_REC.DISBURSEMENT_DATE,
P_OBJECT_VERSION_NUMBER => nvl(P_DISB_LINE_REC.OBJECT_VERSION_NUMBER, 1),
P_INVOICE_INTERFACE_ID => P_DISB_LINE_REC.INVOICE_INTERFACE_ID,
P_INVOICE_ID => P_DISB_LINE_REC.INVOICE_ID);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully inserted disbursement line ' || l_pk_id);
ROLLBACK TO INSERT_DISB_LINE;
ROLLBACK TO INSERT_DISB_LINE;
ROLLBACK TO INSERT_DISB_LINE;
| PUBLIC PROCEDURE UPDATE_DISB_LINE
|
| DESCRIPTION
| This procedure updates disbursement LINE
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| None
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| init
| LogMessage
|
| PARAMETERS
| P_API_VERSION IN Standard in parameter
| P_INIT_MSG_LIST IN Standard in parameter
| P_COMMIT IN Standard in parameter
| P_VALIDATION_LEVEL IN Standard in parameter
| P_DISB_LINE_REC IN LNS_FUNDING_PUB.LNS_DISB_LINES_REC
| X_RETURN_STATUS OUT NOCOPY Standard out parameter
| X_MSG_COUNT OUT NOCOPY Standard out parameter
| X_MSG_DATA OUT NOCOPY Standard out parameter
|
| KNOWN ISSUES
| None
|
| NOTES
| Any interesting aspect of the code in the package body which needs
| to be stated.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 09-22-2004 scherkas Created
|
*=======================================================================*/
PROCEDURE UPDATE_DISB_LINE(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_VALIDATION_LEVEL IN NUMBER,
P_DISB_LINE_REC IN LNS_FUNDING_PUB.LNS_DISB_LINES_REC,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_DISB_LINE';
select OBJECT_VERSION_NUMBER
from lns_disb_lines
where disb_line_id = P_DISB_LINE_ID;
SAVEPOINT UPDATE_DISB_LINE;
LNS_DISB_LINES_PKG.Update_Row(
P_DISB_LINE_ID => P_DISB_LINE_REC.DISB_LINE_ID,
P_DISB_HEADER_ID => P_DISB_LINE_REC.DISB_HEADER_ID,
P_DISB_LINE_NUMBER => P_DISB_LINE_REC.DISB_LINE_NUMBER,
P_LINE_AMOUNT => P_DISB_LINE_REC.LINE_AMOUNT,
P_LINE_PERCENT => P_DISB_LINE_REC.LINE_PERCENT,
P_PAYEE_PARTY_ID => P_DISB_LINE_REC.PAYEE_PARTY_ID,
P_BANK_ACCOUNT_ID => P_DISB_LINE_REC.BANK_ACCOUNT_ID,
P_PAYMENT_METHOD_CODE => P_DISB_LINE_REC.PAYMENT_METHOD_CODE,
P_STATUS => P_DISB_LINE_REC.STATUS,
P_REQUEST_DATE => P_DISB_LINE_REC.REQUEST_DATE,
P_DISBURSEMENT_DATE => P_DISB_LINE_REC.DISBURSEMENT_DATE,
P_OBJECT_VERSION_NUMBER => l_disb_line_version,
P_INVOICE_INTERFACE_ID => P_DISB_LINE_REC.INVOICE_INTERFACE_ID,
P_INVOICE_ID => P_DISB_LINE_REC.INVOICE_ID);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully updated disbursement line ' || P_DISB_LINE_REC.DISB_LINE_ID);
ROLLBACK TO UPDATE_DISB_LINE;
ROLLBACK TO UPDATE_DISB_LINE;
ROLLBACK TO UPDATE_DISB_LINE;
| PUBLIC PROCEDURE DELETE_DISB_LINE
|
| DESCRIPTION
| This procedure updates disbursement LINE
|
| CALLED FROM PROCEDURES/FUNCTIONS (local to this package body)
| None
|
| CALLS PROCEDURES/FUNCTIONS (local to this package body)
| init
| LogMessage
|
| PARAMETERS
| P_API_VERSION IN Standard in parameter
| P_INIT_MSG_LIST IN Standard in parameter
| P_COMMIT IN Standard in parameter
| P_VALIDATION_LEVEL IN Standard in parameter
| P_DISB_LINE_IDC IN Disbursement LINE ID
| X_RETURN_STATUS OUT NOCOPY Standard out parameter
| X_MSG_COUNT OUT NOCOPY Standard out parameter
| X_MSG_DATA OUT NOCOPY Standard out parameter
|
| KNOWN ISSUES
| None
|
| NOTES
| Any interesting aspect of the code in the package body which needs
| to be stated.
|
| MODIFICATION HISTORY
| Date Author Description of Changes
| 09-22-2004 scherkas Created
|
*=======================================================================*/
PROCEDURE DELETE_DISB_LINE(
P_API_VERSION IN NUMBER,
P_INIT_MSG_LIST IN VARCHAR2,
P_COMMIT IN VARCHAR2,
P_VALIDATION_LEVEL IN NUMBER,
P_DISB_LINE_ID IN NUMBER,
X_RETURN_STATUS OUT NOCOPY VARCHAR2,
X_MSG_COUNT OUT NOCOPY NUMBER,
X_MSG_DATA OUT NOCOPY VARCHAR2)
IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'DELETE_DISB_LINE';
select hdr.status
from lns_disb_headers hdr,
lns_disb_lines ln
where hdr.disb_header_id = ln.disb_header_id and
ln.disb_line_id = P_DISB_LINE_ID;
SAVEPOINT DELETE_DISB_LINE;
LNS_DISB_LINES_PKG.Delete_Row(P_DISB_LINE_ID);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully deleted disbursement line ' || P_DISB_LINE_ID);
ROLLBACK TO DELETE_DISB_LINE;
ROLLBACK TO DELETE_DISB_LINE;
ROLLBACK TO DELETE_DISB_LINE;
select line.PAYEE_PARTY_ID,
loan.org_id
from LNS_DISB_LINES line,
LNS_DISB_HEADERS head,
LNS_LOAN_HEADERS_ALL loan
where head.DISB_HEADER_ID = P_DISB_HEADER_ID and
head.DISB_HEADER_ID = line.DISB_HEADER_ID and
head.loan_id = loan.loan_id and not exists
(select PAYEE_PARTY_ID
from IBY_EXTERNAL_PAYEES_ALL
where PAYEE_PARTY_ID = line.PAYEE_PARTY_ID and org_id = loan.org_id and payment_function = 'LOANS_PAYMENTS');
select
loan.REQUESTED_AMOUNT,
loan.LOAN_CURRENCY,
head.DISB_HEADER_ID,
head.LOAN_ID,
head.ACTIVITY_CODE,
head.DISBURSEMENT_NUMBER,
head.HEADER_AMOUNT,
head.HEADER_PERCENT,
head.STATUS,
head.TARGET_DATE,
decode(nvl(loan.CURRENT_PHASE, 'TERM'), 'TERM', loan.LOAN_START_DATE, loan.OPEN_LOAN_START_DATE),
loan.OPEN_MATURITY_DATE
from LNS_DISB_HEADERS head,
LNS_LOAN_HEADERS_ALL loan
where loan.LOAN_ID = P_LOAN_ID and
head.LOAN_ID = loan.LOAN_ID
order by head.DISBURSEMENT_NUMBER;
select
line.disb_header_id,
line.DISB_LINE_NUMBER,
line.LINE_AMOUNT,
line.LINE_PERCENT,
line.PAYEE_PARTY_ID,
line.BANK_ACCOUNT_ID,
line.PAYMENT_METHOD_CODE,
line.REQUEST_DATE,
nvl(head.PAYMENT_REQUEST_DATE, head.TARGET_DATE)
from LNS_DISB_LINES line,
LNS_DISB_HEADERS head
where line.DISB_HEADER_ID = P_DISB_HEADER_ID and
line.DISB_HEADER_ID = head.DISB_HEADER_ID
order by line.DISB_LINE_NUMBER;
SELECT party_site_id
FROM HZ_Party_Sites HPS
WHERE HPS.Party_ID = P_PARTY_ID
AND HPS.Identifying_Address_Flag = 'Y'
AND NVL(HPS.Start_Date_Active, P_DATE) = P_DATE;
select
head.ACTIVITY_CODE,
head.HEADER_AMOUNT,
head.HEADER_PERCENT,
head.PAYMENT_REQUEST_DATE,
loan.OPEN_LOAN_START_DATE,
(select count(1) from lns_disb_lines where disb_header_id = head.DISB_HEADER_ID and
PAYMENT_METHOD_CODE is null)
from LNS_DISB_HEADERS head,
LNS_LOAN_HEADERS_ALL loan
where head.DISB_HEADER_ID = P_DISB_HEADER_ID and
head.LOAN_ID = loan.LOAN_ID;
select
line.DISB_LINE_ID,
line.DISB_HEADER_ID,
line.DISB_LINE_NUMBER,
line.LINE_AMOUNT,
line.LINE_PERCENT,
line.PAYEE_PARTY_ID,
line.BANK_ACCOUNT_ID,
line.PAYMENT_METHOD_CODE,
line.REQUEST_DATE,
line.OBJECT_VERSION_NUMBER,
line.status,
line.DISBURSEMENT_DATE,
head.HEADER_AMOUNT,
head.DISBURSEMENT_NUMBER,
loan.LOAN_CURRENCY,
loan.ORG_ID,
loan.LEGAL_ENTITY_ID,
nvl(head.PAYMENT_REQUEST_DATE, head.TARGET_DATE)
from LNS_DISB_LINES line,
LNS_DISB_HEADERS head,
LNS_LOAN_HEADERS_ALL loan
where line.DISB_HEADER_ID = P_DISB_HEADER_ID and
line.DISB_HEADER_ID = head.DISB_HEADER_ID and
head.LOAN_ID = loan.LOAN_ID
order by line.DISB_LINE_ID;
SELECT party_site_id
FROM HZ_Party_Sites HPS
WHERE HPS.Party_ID = P_PARTY_ID
AND HPS.Identifying_Address_Flag = 'Y'
AND NVL(HPS.Start_Date_Active, P_DATE) = P_DATE;
UPDATE_DISB_LINE(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_FALSE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_DISB_LINE_REC => l_DISB_LINES_TBL(l_Count1),
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Call to UPDATE_DISB_LINE failed');
select
loan.REQUESTED_AMOUNT,
loan.LOAN_CURRENCY,
head.DISB_HEADER_ID,
head.LOAN_ID,
head.ACTIVITY_CODE,
head.DISBURSEMENT_NUMBER,
head.HEADER_AMOUNT,
head.HEADER_PERCENT,
head.STATUS,
head.TARGET_DATE,
head.OBJECT_VERSION_NUMBER,
decode(nvl(loan.CURRENT_PHASE, 'TERM'), 'TERM', loan.LOAN_START_DATE, loan.OPEN_LOAN_START_DATE),
loan.OPEN_MATURITY_DATE,
loan.loan_status,
head.payment_request_date
from LNS_DISB_HEADERS head,
LNS_LOAN_HEADERS_ALL loan
where loan.LOAN_ID = P_LOAN_ID and
head.LOAN_ID = loan.LOAN_ID
order by head.TARGET_DATE;
select
CASE
WHEN (nvl(loan.CURRENT_PHASE, 'TERM') = 'OPEN') THEN
sign(trunc(loan.OPEN_MATURITY_DATE) -
(select trunc(max(PAYMENT_REQUEST_DATE)) from LNS_DISB_HEADERS where LOAN_ID = loan.LOAN_ID))
WHEN (nvl(loan.CURRENT_PHASE, 'TERM') = 'TERM' and loan.MULTIPLE_FUNDING_FLAG = 'N') THEN
sign(trunc(loan.LOAN_MATURITY_DATE) -
(select trunc(max(PAYMENT_REQUEST_DATE)) from LNS_DISB_HEADERS where LOAN_ID = loan.LOAN_ID))
ELSE
1
END
from lns_loan_headers_all loan
where loan.LOAN_ID = P_LOAN_ID;
UPDATE_DISB_HEADER(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_FALSE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_DISB_HEADER_REC => l_DISB_HEADERS_TBL(l_Count1),
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Call to UPDATE_DISB_HEADER failed');
select masked_bank_account_num,
bank_account_name
from IBY_EXT_BANK_ACCOUNTS
where ext_bank_account_id = P_BANK_ACC_ID;
SELECT llh.primary_borrower_id
,llh.org_id
,llh.legal_entity_id
INTO l_payee_party_id
,l_org_id
,l_legal_entity_id
FROM lns_loan_headers_all llh
WHERE llh.loan_id = p_loan_id;
FOR Disb_Rec in (SELECT lns_disb_headers_s.NEXTVAL disb_header_id
, loan_product_lines_id
, activity_code
, disb_percent
, sequence_number
, llh.primary_borrower_id payee_party_id
, lns_disb_lines_s.NEXTVAL disb_line_id
FROM lns_loan_headers_all llh, lns_loan_product_lines lpl
WHERE llh.loan_id = p_loan_id
-- AND llh.multiple_funding_flag = 'Y' Bug#6613708
AND lpl.LOAN_PRODUCT_ID = llh.PRODUCT_ID
AND lpl.LOAN_PRODUCT_LINE_TYPE = 'DISBURSEMENT'
AND SYSDATE BETWEEN NVL(lpl.start_date_active,SYSDATE)
AND NVL(lpl.end_date_active,SYSDATE)
AND NOT EXISTS (select 1
from lns_disb_headers ldh
where ldh.loan_id = llh.loan_id)) LOOP
-- Default Product Disbursements
LNS_DISB_HEADERS_PKG.Insert_Row(
X_DISB_HEADER_ID => Disb_Rec.disb_header_id,
P_LOAN_ID => p_loan_id,
P_ACTIVITY_CODE => Disb_Rec.activity_code,
P_DISBURSEMENT_NUMBER => Disb_Rec.sequence_number,
P_HEADER_PERCENT => Disb_Rec.disb_percent,
P_OBJECT_VERSION_NUMBER => 1);
LNS_DISB_LINES_PKG.Insert_Row(
X_DISB_LINE_ID => Disb_Rec.disb_line_id,
P_DISB_HEADER_ID => Disb_Rec.disb_header_id,
P_DISB_LINE_NUMBER => 1,
P_LINE_PERCENT => 100,
P_PAYEE_PARTY_ID => Disb_Rec.payee_party_id,
P_PAYMENT_METHOD_CODE => l_payment_method_code,
P_BANK_ACCOUNT_ID => l_bank_account_id,
P_OBJECT_VERSION_NUMBER => 1);
select
line.DISB_LINE_ID,
line.DISB_HEADER_ID,
line.DISB_LINE_NUMBER,
line.LINE_AMOUNT,
line.LINE_PERCENT,
line.PAYEE_PARTY_ID,
line.BANK_ACCOUNT_ID,
line.PAYMENT_METHOD_CODE,
line.STATUS,
line.REQUEST_DATE,
line.OBJECT_VERSION_NUMBER,
head.LOAN_ID,
head.ACTIVITY_CODE,
head.DISBURSEMENT_NUMBER,
head.HEADER_AMOUNT,
head.HEADER_PERCENT,
head.STATUS,
head.TARGET_DATE,
head.PAYMENT_REQUEST_DATE,
head.OBJECT_VERSION_NUMBER,
lkp.meaning,
loan.LOAN_NUMBER,
loan.EXCHANGE_RATE_TYPE,
loan.EXCHANGE_DATE,
loan.EXCHANGE_RATE,
loan.LOAN_CURRENCY,
loan.ORG_ID,
loan.LEGAL_ENTITY_ID,
line.INVOICE_INTERFACE_ID
from LNS_DISB_LINES line,
LNS_DISB_HEADERS head,
lns_lookups lkp,
LNS_LOAN_HEADERS_ALL loan
where line.DISB_LINE_ID = P_DISB_LINE_ID and
head.DISB_HEADER_ID = line.DISB_HEADER_ID and
lkp.lookup_type = 'DISB_ACTIVITY' and
lkp.lookup_code = head.ACTIVITY_CODE and
loan.loan_id = head.loan_id;
select count(1) + 1
from LNS_DISB_HISTORIES_H
where DISB_LINE_ID = P_DISB_LINE_ID;
select CODE_COMBINATION_ID,
DISTRIBUTION_PERCENT
from lns_distributions
where LOAN_ID = P_LOAN_ID and
account_type = 'CR' and
account_name = 'LOAN_LIABILITY' and
line_type = 'PRIN';
select CODE_COMBINATION_ID,
DISTRIBUTION_PERCENT
from lns_distributions
where LOAN_ID = P_LOAN_ID and
account_name = 'LOAN_PAYABLE' and
line_type = 'CLEAR' and
disb_header_id = P_DISB_HEADER_ID;
select AP_INVOICES_INTERFACE_S.NEXTVAL from dual;
select AP_INVOICE_LINES_INTERFACE_S.NEXTVAL from dual;
select description
from AP_LOOKUP_CODES
where lookup_type = 'REJECT CODE' and
lookup_code = P_CODE;
select party.party_name
from AP_INVOICES_INTERFACE inv,
hz_parties party
where inv.INVOICE_ID = P_ID and
inv.party_id = party.party_id;
select party.party_name
from AP_INVOICES_INTERFACE inv,
AP_INVOICE_LINES_INTERFACE line,
hz_parties party
where line.INVOICE_LINE_ID = P_ID and
line.INVOICE_ID = inv.INVOICE_ID and
inv.party_id = party.party_id;
select invoice_id
from ap_invoices_all
where invoice_num = P_INVOICE_NUM and
party_id = P_PARTY_ID;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting AP invoice into interface table...');
AP_INVOICES_INTERFACE_PKG.INSERT_ROW(
X_ROWID => l_invoice_row_id,
X_INVOICE_ID => l_invoice_id,
X_INVOICE_NUM => l_invoice_number,
X_INVOICE_TYPE_LOOKUP_CODE => 'PAYMENT REQUEST',
X_INVOICE_DATE => l_due_date,
X_PO_NUMBER => null,
X_VENDOR_ID => null,
X_VENDOR_SITE_ID => null,
X_INVOICE_AMOUNT => l_DISB_LINE_REC.LINE_AMOUNT,
X_INVOICE_CURRENCY_CODE => l_loan_currency,
X_PAYMENT_CURRENCY_CODE => null,
X_PAYMENT_CROSS_RATE => null,
X_PAYMENT_CROSS_RATE_TYPE => null,
X_PAYMENT_CROSS_RATE_DATE => null,
X_EXCHANGE_RATE => l_EXCHANGE_RATE,
X_EXCHANGE_RATE_TYPE => l_EXCHANGE_RATE_TYPE,
X_EXCHANGE_DATE => l_EXCHANGE_DATE,
X_TERMS_ID => 10001,
X_DESCRIPTION => l_description,
X_AWT_GROUP_ID => null,
X_AMT_APPLICABLE_TO_DISCOUNT => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_GLOBAL_ATTRIBUTE_CATEGORY => null,
X_GLOBAL_ATTRIBUTE1 => null,
X_GLOBAL_ATTRIBUTE2 => null,
X_GLOBAL_ATTRIBUTE3 => null,
X_GLOBAL_ATTRIBUTE4 => null,
X_GLOBAL_ATTRIBUTE5 => null,
X_GLOBAL_ATTRIBUTE6 => null,
X_GLOBAL_ATTRIBUTE7 => null,
X_GLOBAL_ATTRIBUTE8 => null,
X_GLOBAL_ATTRIBUTE9 => null,
X_GLOBAL_ATTRIBUTE10 => null,
X_GLOBAL_ATTRIBUTE11 => null,
X_GLOBAL_ATTRIBUTE12 => null,
X_GLOBAL_ATTRIBUTE13 => null,
X_GLOBAL_ATTRIBUTE14 => null,
X_GLOBAL_ATTRIBUTE15 => null,
X_GLOBAL_ATTRIBUTE16 => null,
X_GLOBAL_ATTRIBUTE17 => null,
X_GLOBAL_ATTRIBUTE18 => null,
X_GLOBAL_ATTRIBUTE19 => null,
X_GLOBAL_ATTRIBUTE20 => null,
X_STATUS => null,
X_SOURCE => l_source,
X_GROUP_ID => l_invoice_number,
X_WORKFLOW_FLAG => null,
X_DOC_CATEGORY_CODE => null,
X_VOUCHER_NUM => null,
X_PAY_GROUP_LOOKUP_CODE => null, --'Standard',
X_GOODS_RECEIVED_DATE => null,
X_INVOICE_RECEIVED_DATE => sysdate,
X_GL_DATE => l_due_date,
X_ACCTS_PAY_CCID => l_cc_id,
-- X_USSGL_TRANSACTION_CODE => null,
X_EXCLUSIVE_PAYMENT_FLAG => null,
X_INVOICE_INCLUDES_PREPAY_FLAG => null,
X_PREPAY_NUM => null,
X_PREPAY_APPLY_AMOUNT => null,
X_PREPAY_GL_DATE => null,
X_CREATION_DATE => LNS_UTILITY_PUB.CREATION_DATE,
X_CREATED_BY => LNS_UTILITY_PUB.CREATED_BY,
X_LAST_UPDATE_DATE => LNS_UTILITY_PUB.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => LNS_UTILITY_PUB.LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => LNS_UTILITY_PUB.LAST_UPDATE_LOGIN,
X_ORG_ID => l_org_id,
X_MODE => null,
X_TERMS_DATE => null,
X_REQUESTER_ID => null,
X_OPERATING_UNIT => null,
-- Invoice LINes Project Stage 1
X_PREPAY_LINE_NUM => null,
X_REQUESTER_FIRST_NAME => null,
X_REQUESTER_LAST_NAME => null,
X_REQUESTER_EMPLOYEE_NUM => null,
-- eTax Uptake
X_CALC_TAX_DURING_IMPORT_FLAG => null,
X_CONTROL_AMOUNT => null,
X_ADD_TAX_TO_INV_AMT_FLAG => null,
X_TAX_RELATED_INVOICE_ID => null,
X_TAXATION_COUNTRY => null,
X_DOCUMENT_SUB_TYPE => null,
X_SUPPLIER_TAX_INVOICE_NUMBER => null,
X_SUPPLIER_TAX_INVOICE_DATE => null,
X_SUPPLIER_TAX_EXCHANGE_RATE => null,
X_TAX_INVOICE_RECORDING_DATE => null,
X_TAX_INVOICE_INTERNAL_SEQ => null,
X_LEGAL_ENTITY_ID => l_le_id,
x_PAYMENT_METHOD_CODE => l_DISB_LINE_REC.PAYMENT_METHOD_CODE,
x_PAYMENT_REASON_CODE => null,
X_PAYMENT_REASON_COMMENTS => null,
x_UNIQUE_REMITTANCE_IDENTIFIER => null,
x_URI_CHECK_DIGIT => null,
x_BANK_CHARGE_BEARER => null,
x_DELIVERY_CHANNEL_CODE => null,
x_SETTLEMENT_PRIORITY => null,
x_remittance_message1 => null,
x_remittance_message2 => null,
x_remittance_message3 => null,
x_NET_OF_RETAINAGE_FLAG => null,
x_PORT_OF_ENTRY_CODE => null,
X_APPLICATION_ID => 206,
X_PRODUCT_TABLE => null,
X_REFERENCE_KEY1 => null,
X_REFERENCE_KEY2 => null,
X_REFERENCE_KEY3 => null,
X_REFERENCE_KEY4 => null,
X_REFERENCE_KEY5 => null,
X_PARTY_ID => l_DISB_LINE_REC.PAYEE_PARTY_ID,
X_PARTY_SITE_ID => null,
X_PAY_PROC_TRXN_TYPE_CODE => 'LOAN_PAYMENT',
X_PAYMENT_FUNCTION => 'LOANS_PAYMENTS',
X_PAYMENT_PRIORITY => null);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully inserted new invoice');
AP_INVOICES_INTERFACE_PKG.UPDATE_ROW(
X_INVOICE_ID => l_invoice_id,
X_INVOICE_NUM => l_invoice_number,
X_INVOICE_TYPE_LOOKUP_CODE => 'PAYMENT REQUEST',
X_INVOICE_DATE => l_due_date,
X_PO_NUMBER => null,
X_VENDOR_ID => null,
X_VENDOR_SITE_ID => null,
X_INVOICE_AMOUNT => l_DISB_LINE_REC.LINE_AMOUNT,
X_INVOICE_CURRENCY_CODE => l_loan_currency,
X_PAYMENT_CURRENCY_CODE => null,
X_PAYMENT_CROSS_RATE => null,
X_PAYMENT_CROSS_RATE_TYPE => null,
X_PAYMENT_CROSS_RATE_DATE => null,
X_EXCHANGE_RATE => l_EXCHANGE_RATE,
X_EXCHANGE_RATE_TYPE => l_EXCHANGE_RATE_TYPE,
X_EXCHANGE_DATE => l_EXCHANGE_DATE,
X_TERMS_ID => 10001,
X_DESCRIPTION => l_description,
X_AWT_GROUP_ID => null,
X_AMT_APPLICABLE_TO_DISCOUNT => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_GLOBAL_ATTRIBUTE_CATEGORY => null,
X_GLOBAL_ATTRIBUTE1 => null,
X_GLOBAL_ATTRIBUTE2 => null,
X_GLOBAL_ATTRIBUTE3 => null,
X_GLOBAL_ATTRIBUTE4 => null,
X_GLOBAL_ATTRIBUTE5 => null,
X_GLOBAL_ATTRIBUTE6 => null,
X_GLOBAL_ATTRIBUTE7 => null,
X_GLOBAL_ATTRIBUTE8 => null,
X_GLOBAL_ATTRIBUTE9 => null,
X_GLOBAL_ATTRIBUTE10 => null,
X_GLOBAL_ATTRIBUTE11 => null,
X_GLOBAL_ATTRIBUTE12 => null,
X_GLOBAL_ATTRIBUTE13 => null,
X_GLOBAL_ATTRIBUTE14 => null,
X_GLOBAL_ATTRIBUTE15 => null,
X_GLOBAL_ATTRIBUTE16 => null,
X_GLOBAL_ATTRIBUTE17 => null,
X_GLOBAL_ATTRIBUTE18 => null,
X_GLOBAL_ATTRIBUTE19 => null,
X_GLOBAL_ATTRIBUTE20 => null,
X_STATUS => null,
X_SOURCE => l_source,
X_GROUP_ID => l_invoice_number,
X_WORKFLOW_FLAG => null,
X_DOC_CATEGORY_CODE => null,
X_VOUCHER_NUM => null,
X_PAY_GROUP_LOOKUP_CODE => null, --'Standard',
X_GOODS_RECEIVED_DATE => null,
X_INVOICE_RECEIVED_DATE => sysdate,
X_GL_DATE => l_due_date,
X_ACCTS_PAY_CCID => l_cc_id,
-- X_USSGL_TRANSACTION_CODE => null,
X_EXCLUSIVE_PAYMENT_FLAG => null,
X_INVOICE_INCLUDES_PREPAY_FLAG => null,
X_PREPAY_NUM => null,
X_PREPAY_APPLY_AMOUNT => null,
X_PREPAY_GL_DATE => null,
X_LAST_UPDATE_DATE => LNS_UTILITY_PUB.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => LNS_UTILITY_PUB.LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => LNS_UTILITY_PUB.LAST_UPDATE_LOGIN,
X_MODE => null,
X_TERMS_DATE => null,
X_REQUESTER_ID => null,
X_OPERATING_UNIT => null,
-- Invoice LINes Project Stage 1
X_PREPAY_LINE_NUM => null,
X_REQUESTER_FIRST_NAME => null,
X_REQUESTER_LAST_NAME => null,
X_REQUESTER_EMPLOYEE_NUM => null,
-- eTax Uptake
X_CALC_TAX_DURING_IMPORT_FLAG => null,
X_CONTROL_AMOUNT => null,
X_ADD_TAX_TO_INV_AMT_FLAG => null,
X_TAX_RELATED_INVOICE_ID => null,
X_TAXATION_COUNTRY => null,
X_DOCUMENT_SUB_TYPE => null,
X_SUPPLIER_TAX_INVOICE_NUMBER => null,
X_SUPPLIER_TAX_INVOICE_DATE => null,
X_SUPPLIER_TAX_EXCHANGE_RATE => null,
X_TAX_INVOICE_RECORDING_DATE => null,
X_TAX_INVOICE_INTERNAL_SEQ => null,
X_LEGAL_ENTITY_ID => l_le_id,
x_PAYMENT_METHOD_CODE => l_DISB_LINE_REC.PAYMENT_METHOD_CODE,
x_PAYMENT_REASON_CODE => null,
X_PAYMENT_REASON_COMMENTS => null,
x_UNIQUE_REMITTANCE_IDENTIFIER => null,
x_URI_CHECK_DIGIT => null,
x_BANK_CHARGE_BEARER => null,
x_DELIVERY_CHANNEL_CODE => null,
x_SETTLEMENT_PRIORITY => null,
x_remittance_message1 => null,
x_remittance_message2 => null,
x_remittance_message3 => null,
x_NET_OF_RETAINAGE_FLAG => null,
x_PORT_OF_ENTRY_CODE => null,
X_APPLICATION_ID => 206,
X_PRODUCT_TABLE => null,
X_REFERENCE_KEY1 => null,
X_REFERENCE_KEY2 => null,
X_REFERENCE_KEY3 => null,
X_REFERENCE_KEY4 => null,
X_REFERENCE_KEY5 => null,
X_PARTY_ID => l_DISB_LINE_REC.PAYEE_PARTY_ID,
X_PARTY_SITE_ID => null,
X_PAY_PROC_TRXN_TYPE_CODE => 'LOAN_PAYMENT',
X_PAYMENT_FUNCTION => 'LOANS_PAYMENTS',
X_PAYMENT_PRIORITY => null);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully updated invoice');
LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to insert invoice into interface table');
LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to update invoice into interface table');
delete from AP_INVOICE_LINES_INTERFACE
where INVOICE_ID = l_invoice_id;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Deleted');
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting AP invoice line ' || l_Count1 || ' into interface table...');
AP_INVOICE_LINES_INTERFACE_PKG.INSERT_ROW(
X_ROWID => l_inv_line_row_id,
X_INVOICE_ID => l_invoice_id,
X_INVOICE_LINE_ID => l_invoice_line_id,
X_LINE_NUMBER => l_Count1,
X_LINE_TYPE_LOOKUP_CODE => 'ITEM',
X_LINE_GROUP_NUMBER => null,
X_AMOUNT => l_line_amount,
X_ACCOUNTING_DATE => l_due_date,
X_DESCRIPTION => l_description,
-- X_AMOUNT_INCLUDES_TAX_FLAG => null,
X_PRORATE_ACROSS_FLAG => null,
X_TAX_CODE => null,
X_TAX_CODE_ID => null,
-- X_TAX_CODE_OVERRIDE_FLAG => null,
-- X_TAX_RECOVERY_RATE => null,
-- X_TAX_RECOVERY_OVERRIDE_FLAG => null,
-- X_TAX_RECOVERABLE_FLAG => null,
X_FINAL_MATCH_FLAG => null,
X_PO_HEADER_ID => null,
X_PO_LINE_ID => null,
X_PO_LINE_LOCATION_ID => null,
X_PO_DISTRIBUTION_ID => null,
X_UNIT_OF_MEAS_LOOKUP_CODE => null,
X_INVENTORY_ITEM_ID => null,
X_QUANTITY_INVOICED => null,
X_UNIT_PRICE => null,
X_DISTRIBUTION_SET_ID => null,
X_DIST_CODE_CONCATENATED => null,
X_DIST_CODE_COMBINATION_ID => l_cc_id,
X_AWT_GROUP_ID => null,
X_ATTRIBUTE_CATEGORY => null,
X_ATTRIBUTE1 => null,
X_ATTRIBUTE2 => null,
X_ATTRIBUTE3 => null,
X_ATTRIBUTE4 => null,
X_ATTRIBUTE5 => null,
X_ATTRIBUTE6 => null,
X_ATTRIBUTE7 => null,
X_ATTRIBUTE8 => null,
X_ATTRIBUTE9 => null,
X_ATTRIBUTE10 => null,
X_ATTRIBUTE11 => null,
X_ATTRIBUTE12 => null,
X_ATTRIBUTE13 => null,
X_ATTRIBUTE14 => null,
X_ATTRIBUTE15 => null,
X_GLOBAL_ATTRIBUTE_CATEGORY => null,
X_GLOBAL_ATTRIBUTE1 => null,
X_GLOBAL_ATTRIBUTE2 => null,
X_GLOBAL_ATTRIBUTE3 => null,
X_GLOBAL_ATTRIBUTE4 => null,
X_GLOBAL_ATTRIBUTE5 => null,
X_GLOBAL_ATTRIBUTE6 => null,
X_GLOBAL_ATTRIBUTE7 => null,
X_GLOBAL_ATTRIBUTE8 => null,
X_GLOBAL_ATTRIBUTE9 => null,
X_GLOBAL_ATTRIBUTE10 => null,
X_GLOBAL_ATTRIBUTE11 => null,
X_GLOBAL_ATTRIBUTE12 => null,
X_GLOBAL_ATTRIBUTE13 => null,
X_GLOBAL_ATTRIBUTE14 => null,
X_GLOBAL_ATTRIBUTE15 => null,
X_GLOBAL_ATTRIBUTE16 => null,
X_GLOBAL_ATTRIBUTE17 => null,
X_GLOBAL_ATTRIBUTE18 => null,
X_GLOBAL_ATTRIBUTE19 => null,
X_GLOBAL_ATTRIBUTE20 => null,
X_PO_RELEASE_ID => null,
X_BALANCING_SEGMENT => null,
X_COST_CENTER_SEGMENT => null,
X_ACCOUNT_SEGMENT => null,
X_PROJECT_ID => null,
X_TASK_ID => null,
X_EXPENDITURE_TYPE => null,
X_EXPENDITURE_ITEM_DATE => null,
X_EXPENDITURE_ORGANIZATION_ID => null,
X_PROJECT_ACCOUNTING_CONTEXT => null,
X_PA_ADDITION_FLAG => null,
X_PA_QUANTITY => null,
X_STAT_AMOUNT => null,
X_TYPE_1099 => null,
X_INCOME_TAX_REGION => null,
X_ASSETS_TRACKING_FLAG => null,
X_PRICE_CORRECTION_FLAG => null,
-- X_USSGL_TRANSACTION_CODE => null,
X_RECEIPT_NUMBER => null,
X_MATCH_OPTION => null,
X_RCV_TRANSACTION_ID => null,
X_CREATION_DATE => LNS_UTILITY_PUB.CREATION_DATE,
X_CREATED_BY => LNS_UTILITY_PUB.CREATED_BY,
X_LAST_UPDATE_DATE => LNS_UTILITY_PUB.LAST_UPDATE_DATE,
X_LAST_UPDATED_BY => LNS_UTILITY_PUB.LAST_UPDATED_BY,
X_LAST_UPDATE_LOGIN => LNS_UTILITY_PUB.LAST_UPDATE_LOGIN,
X_ORG_ID => l_org_id,
X_MODE => null,
X_Calling_Sequence => null,
X_award_id => null,
X_price_correct_inv_num => null,
-- Invoice Lines Project Stage 1
X_PRICE_CORRECT_INV_LINE_NUM => null,
X_SERIAL_NUMBER => null,
X_MANUFACTURER => null,
X_MODEL_NUMBER => null,
X_WARRANTY_NUMBER => null,
X_ASSET_BOOK_TYPE_CODE => null,
X_ASSET_CATEGORY_ID => null,
X_REQUESTER_FIRST_NAME => null,
X_REQUESTER_LAST_NAME => null,
X_REQUESTER_EMPLOYEE_NUM => null,
X_REQUESTER_ID => null,
X_DEFERRED_ACCTG_FLAG => null,
X_DEF_ACCTG_START_DATE => null,
X_DEF_ACCTG_END_DATE => null,
X_DEF_ACCTG_NUMBER_OF_PERIODS => null,
X_DEF_ACCTG_PERIOD_TYPE => null,
-- eTax Uptake
X_CONTROL_AMOUNT => null,
X_ASSESSABLE_VALUE => null,
X_DEFAULT_DIST_CCID => null,
X_PRIMARY_INTENDED_USE => null,
X_SHIP_TO_LOCATION_ID => null,
X_PRODUCT_TYPE => null,
X_PRODUCT_CATEGORY => null,
X_PRODUCT_FISC_CLASSIFICATION => null,
X_USER_DEFINED_FISC_CLASS => null,
X_TRX_BUSINESS_CATEGORY => null,
X_TAX_REGIME_CODE => null,
X_TAX => null,
X_TAX_JURISDICTION_CODE => null,
X_TAX_STATUS_CODE => null,
X_TAX_RATE_ID => null,
X_TAX_RATE_CODE => null,
X_TAX_RATE => null,
X_INCL_IN_TAXABLE_LINE_FLAG => null,
X_PURCHASING_CATEGORY => null,
X_PURCHASING_CATEGORY_ID => null,
X_COST_FACTOR_NAME => null,
X_COST_FACTOR_ID => null);
LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Failed to insert invoice line into interface table');
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully inserted new invoice line.');
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting new history record...');
LNS_DISB_HISTORIES_H_PKG.Insert_Row(
X_DISB_HISTORY_ID => l_hist_id,
P_DISB_LINE_ID => l_DISB_LINE_REC.DISB_LINE_ID,
P_ATTEMPT_NUMBER => l_attempt,
P_BANK_ACCOUNT_ID => l_DISB_LINE_REC.BANK_ACCOUNT_ID,
P_PAYMENT_METHOD_CODE => l_DISB_LINE_REC.PAYMENT_METHOD_CODE,
P_REQUEST_DATE => l_DISB_LINE_REC.REQUEST_DATE,
P_PAYMENT_REQUEST_DATE => l_due_date,
P_STATUS => l_DISB_LINE_REC.STATUS,
P_MESSAGES => null,
P_OBJECT_VERSION_NUMBER => 1);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully inserted new history record with DISB_HISTORY_ID: ' || l_hist_id);
UPDATE_DISB_LINE(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_FALSE,
P_COMMIT => FND_API.G_TRUE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_DISB_LINE_REC => l_DISB_LINE_REC,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Call to UPDATE_DISB_LINE failed');
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Inserting new history record...');
LNS_DISB_HISTORIES_H_PKG.Insert_Row(
X_DISB_HISTORY_ID => l_hist_id,
P_DISB_LINE_ID => l_DISB_LINE_REC.DISB_LINE_ID,
P_ATTEMPT_NUMBER => l_attempt,
P_BANK_ACCOUNT_ID => l_DISB_LINE_REC.BANK_ACCOUNT_ID,
P_PAYMENT_METHOD_CODE => l_DISB_LINE_REC.PAYMENT_METHOD_CODE,
P_REQUEST_DATE => sysdate,
P_PAYMENT_REQUEST_DATE => l_due_date,
P_STATUS => l_DISB_LINE_REC.STATUS,
P_MESSAGES => null,
P_OBJECT_VERSION_NUMBER => 1);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully inserted new history record with DISB_HISTORY_ID: ' || l_hist_id);
UPDATE_DISB_LINE(P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_FALSE,
P_COMMIT => FND_API.G_TRUE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_DISB_LINE_REC => l_DISB_LINE_REC,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Call to UPDATE_DISB_LINE failed');
select DISB_HEADER_ID,
nvl(AUTOFUNDING_FLAG, 'N')
from LNS_DISB_HEADERS
where LOAN_ID = P_LOAN_ID and
DISBURSEMENT_NUMBER = 1;
select
DISB_LINE_ID,
DISB_HEADER_ID,
DISB_LINE_NUMBER,
LINE_AMOUNT,
LINE_PERCENT,
PAYEE_PARTY_ID,
BANK_ACCOUNT_ID,
PAYMENT_METHOD_CODE,
STATUS,
REQUEST_DATE,
OBJECT_VERSION_NUMBER
from LNS_DISB_LINES
where DISB_HEADER_ID = P_DISB_HEADER_ID and
(status is null or status = 'FUNDING_ERROR');
select loan.LOAN_ID,
loan.LOAN_NUMBER,
loan.OBJECT_VERSION_NUMBER,
loan.LOAN_STATUS,
head.DISBURSEMENT_NUMBER,
nvl(loan.FUNDS_RESERVED_FLAG,'N')
from LNS_LOAN_HEADERS_ALL loan,
LNS_DISB_HEADERS head
where head.DISB_HEADER_ID = P_DISB_HEADER_ID and
head.LOAN_ID = loan.LOAN_ID;
select count(1)
from LNS_COND_ASSIGNMENTS
where
DISB_HEADER_ID = P_DISB_HEADER_ID and
MANDATORY_FLAG = 'Y' and
(CONDITION_MET_FLAG is null or CONDITION_MET_FLAG = 'N') and
(end_date_active is null or trunc(end_date_active) > trunc(sysdate));
UPDATE_DISB_HEADER(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_FALSE,
P_COMMIT => FND_API.G_FALSE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_DISB_HEADER_REC => l_DISB_HEADER_REC,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Call to UPDATE_DISB_HEADER failed');
LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_loan_version,
P_LOAN_HEADER_REC => l_loan_header_rec,
P_INIT_MSG_LIST => FND_API.G_FALSE,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');
| 06-02-2006 karamach If this is the first funding for the disb header, then update the payment_request_date to be the sysdate to fix bug5232416
| 13-JUL-2007 mbolli Bug#6169438 - If the loan dates are shifted then regenerate the Loan Agreements
*=======================================================================*/
PROCEDURE PAY_SINGLE_INVOICE
(P_INVOICE_ID IN NUMBER,
P_COMPLETE_FLAG IN VARCHAR2,
X_Return_Status OUT NOCOPY VARCHAR2,
X_Msg_Count OUT NOCOPY NUMBER,
X_Msg_Data OUT NOCOPY VARCHAR2)
IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'PAY_SINGLE_INVOICE';
select
lines.DISB_LINE_ID,
lines.DISB_HEADER_ID,
lines.OBJECT_VERSION_NUMBER,
head.loan_id,
head.OBJECT_VERSION_NUMBER,
head.status,
loan.OBJECT_VERSION_NUMBER,
loan.REQUESTED_AMOUNT,
loan.current_phase,
loan.OPEN_TO_TERM_FLAG,
loan.OPEN_TO_TERM_EVENT,
loan.loan_status,
head.DISBURSEMENT_NUMBER,
loan.secondary_status,
loan.MULTIPLE_FUNDING_FLAG
from LNS_DISB_LINES lines,
LNS_DISB_HEADERS head,
LNS_LOAN_HEADERS loan
where lines.INVOICE_ID = P_INVOICE_ID and
lines.DISB_HEADER_ID = head.DISB_HEADER_ID and
lines.status is not null and lines.status <> 'FULLY_FUNDED' and
head.LOAN_ID = loan.LOAN_ID;
select count(1) from LNS_DISB_LINES
where DISB_HEADER_ID = P_DISB_HEADER_ID and STATUS IN ('FULLY_FUNDED', 'PARTIALLY_FUNDED');
select
(select count(1) from LNS_DISB_LINES
where DISB_HEADER_ID = P_DISB_HEADER_ID)
-
(select count(1) from LNS_DISB_LINES
where DISB_HEADER_ID = P_DISB_HEADER_ID and (STATUS is not null and STATUS = 'FULLY_FUNDED'))
from dual;
select nvl(sum(inv.amount_paid), 0)
from LNS_DISB_LINES lines,
LNS_DISB_HEADERS head,
ap_invoices_all inv
where head.LOAN_ID = P_LOAN_ID and
lines.DISB_HEADER_ID = head.DISB_HEADER_ID and
lines.invoice_id is not null and
lines.invoice_id = inv.invoice_id;
select nvl(sum(lines.LINE_AMOUNT), 0)
from LNS_DISB_LINES lines,
LNS_DISB_HEADERS head
where head.LOAN_ID = P_LOAN_ID and
lines.DISB_HEADER_ID = head.DISB_HEADER_ID and
(lines.STATUS is not null and lines.STATUS = 'FULLY_FUNDED') and
lines.DISBURSEMENT_DATE is not null;
select max(DISB_HISTORY_ID), max(OBJECT_VERSION_NUMBER+1)
from lns_disb_histories_h
where DISB_LINE_ID = P_DISB_LINE_ID;
select TERM_ID,
OBJECT_VERSION_NUMBER
from LNS_TERMS
where LOAN_ID = P_LOAN_ID;
select nvl(count(1),0)
from lns_fee_assignments
where begin_installment_number = 0
and end_installment_number = 0
and end_date_active is null
and billing_option = 'ORIGINATION'
and (loan_id = P_LOAN_ID
-- Bug#6613647, The loanId is NULL if for the disbFees in lns_fee_assignments.
-- So to check any existed 0th installment fees for a loan, also check existed fees for disb_header_id.
OR disb_header_id in (select disb_header_id from lns_disb_headers where loan_id = P_LOAN_ID) -- Bug#6613647
);
select INVOICE_AMOUNT, AMOUNT_PAID from ap_invoices_all where invoice_id = P_INVOICE_ID;
UPDATE_DISB_LINE(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_FALSE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_DISB_LINE_REC => l_DISB_LINE_REC,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Call to UPDATE_DISB_LINE failed');
UPDATE_DISB_HEADER(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_FALSE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_DISB_HEADER_REC => l_DISB_HEADER_REC,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Call to UPDATE_DISB_HEADER failed');
LNS_DISB_HISTORIES_H_PKG.Update_Row(
P_DISB_HISTORY_ID => l_hist_id,
P_STATUS => l_DISB_LINE_REC.STATUS,
P_OBJECT_VERSION_NUMBER => l_hist_version);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully updated history record with ID: ' || l_hist_id);
LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_TERM_VERSION_NUMBER,
p_init_msg_list => FND_API.G_FALSE,
p_loan_term_rec => l_term_rec,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_TERMS');
LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_loan_version,
P_LOAN_HEADER_REC => l_loan_header_rec,
P_INIT_MSG_LIST => FND_API.G_FALSE,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');
SELECT
lower(iso_language),iso_territory
INTO
l_iso_language,l_iso_territory
FROM
FND_LANGUAGES
WHERE
language_code = USERENV('LANG');
LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_loan_version,
P_LOAN_HEADER_REC => l_loan_header_rec,
P_INIT_MSG_LIST => FND_API.G_FALSE,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');
LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_TERM_VERSION_NUMBER,
p_init_msg_list => FND_API.G_FALSE,
p_loan_term_rec => l_term_rec,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_TERMS');
LNS_TERMS_PUB.update_term(P_OBJECT_VERSION_NUMBER => l_TERM_VERSION_NUMBER,
p_init_msg_list => FND_API.G_FALSE,
p_loan_term_rec => l_term_rec,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_TERMS');
LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_loan_version,
P_LOAN_HEADER_REC => l_loan_header_rec,
P_INIT_MSG_LIST => FND_API.G_FALSE,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');
SELECT
lower(iso_language),iso_territory
INTO
l_iso_language,l_iso_territory
FROM
FND_LANGUAGES
WHERE
language_code = USERENV('LANG');
select distinct(invoice_id)
from AP_INVOICE_PAYMENTS_ALL
where check_id = P_CHECK_ID;
select line.INVOICE_ID
from LNS_DISB_LINES line,
LNS_DISB_HEADERS head
where head.LOAN_ID = P_LOAN_ID and
head.DISB_HEADER_ID = line.DISB_HEADER_ID and
line.status is not null and
line.status = 'IN_FUNDING' and
line.invoice_id is not null;
l_last_update_date date;
select head.DISB_HEADER_ID,
head.OBJECT_VERSION_NUMBER,
(select count(1)
from lns_disb_lines
where DISB_HEADER_ID = head.DISB_HEADER_ID and
status <> 'CANCELLED')
from LNS_DISB_HEADERS head
where head.DISB_HEADER_ID = P_DISB_HEADER_ID;
select line.DISB_LINE_ID,
line.OBJECT_VERSION_NUMBER,
line.STATUS,
line.INVOICE_ID
from LNS_DISB_LINES line
where line.DISB_HEADER_ID = P_DISB_HEADER_ID;
select max(DISB_HISTORY_ID), max(OBJECT_VERSION_NUMBER+1)
from lns_disb_histories_h
where DISB_LINE_ID = P_DISB_LINE_ID;
P_LAST_UPDATED_BY => LNS_UTILITY_PUB.LAST_UPDATED_BY,
P_LAST_UPDATE_LOGIN => LNS_UTILITY_PUB.LAST_UPDATE_LOGIN,
P_ACCOUNTING_DATE => sysdate,
P_MESSAGE_NAME => l_message_name,
P_INVOICE_AMOUNT => l_invoice_amount,
P_BASE_AMOUNT => l_base_amount,
P_TEMP_CANCELLED_AMOUNT => l_temp_cancelled_amount,
P_CANCELLED_BY => l_cancelled_by,
P_CANCELLED_AMOUNT => l_cancelled_amount,
P_CANCELLED_DATE => l_cancelled_date,
P_LAST_UPDATE_DATE => l_last_update_date,
P_ORIGINAL_PREPAYMENT_AMOUNT => l_original_prepayment_amount,
P_PAY_CURR_INVOICE_AMOUNT => l_pay_curr_invoice_amount,
P_TOKEN => l_Token,
P_CALLING_SEQUENCE => G_PKG_NAME);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'l_last_update_date: ' || l_last_update_date);
LNS_DISB_HISTORIES_H_PKG.Update_Row(
P_DISB_HISTORY_ID => l_hist_id,
P_STATUS => 'CANCELLED',
P_OBJECT_VERSION_NUMBER => l_hist_version);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully updated history record with ID: ' || l_hist_id);
UPDATE_DISB_LINE(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_FALSE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_DISB_LINE_REC => l_DISB_LINE_REC,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Call to UPDATE_DISB_LINE failed');
UPDATE_DISB_HEADER(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_FALSE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_DISB_HEADER_REC => l_DISB_HEADER_REC,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Call to UPDATE_DISB_HEADER failed');
select head.DISB_HEADER_ID,
loan.LOAN_STATUS,
loan.OBJECT_VERSION_NUMBER
from LNS_DISB_HEADERS head,
LNS_LOAN_HEADERS loan
where head.LOAN_ID = P_LOAN_ID and
head.LOAN_ID = loan.LOAN_ID;
select count(head.DISB_HEADER_ID)
from LNS_DISB_HEADERS head
where head.LOAN_ID = P_LOAN_ID and
head.status <> 'CANCELLED';
LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_loan_version,
P_LOAN_HEADER_REC => l_loan_header_rec,
P_INIT_MSG_LIST => FND_API.G_FALSE,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');
select loan.OBJECT_VERSION_NUMBER
from LNS_LOAN_HEADERS loan
where loan.LOAN_ID = P_LOAN_ID;
select old_value
from lns_loan_histories_h
where table_name = 'LNS_LOAN_HEADERS_ALL' and
column_name = 'LOAN_STATUS' and
new_value = 'PENDING_CANCELLATION' and
loan_id = P_LOAN_ID and
loan_history_id =
(select max(loan_history_id)
from lns_loan_histories_h
where table_name = 'LNS_LOAN_HEADERS_ALL' and
column_name = 'LOAN_STATUS' and
loan_id = P_LOAN_ID);
LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_loan_version,
P_LOAN_HEADER_REC => l_loan_header_rec,
P_INIT_MSG_LIST => FND_API.G_FALSE,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');
select loan.OBJECT_VERSION_NUMBER
from LNS_LOAN_HEADERS loan
where loan.LOAN_ID = P_LOAN_ID;
select nvl(prod.APPR_REQ_FOR_CNCL_FLAG, 'N')
from lns_loan_products_all prod,
lns_loan_headers_all loan
where loan.loan_id = P_LOAN_ID and
loan.product_id = prod.loan_product_id;
LNS_LOAN_HEADER_PUB.UPDATE_LOAN(P_OBJECT_VERSION_NUMBER => l_loan_version,
P_LOAN_HEADER_REC => l_loan_header_rec,
P_INIT_MSG_LIST => FND_API.G_FALSE,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully update LNS_LOAN_HEADERS_ALL');
select USER_DEFINED_VENDOR_NUM_CODE
from FINANCIALS_SYSTEM_PARAMETERS;
select max(to_number(segment1)) + 1 from PO_VENDORS;
select
DISB_HEADER_ID,
OBJECT_VERSION_NUMBER
from LNS_DISB_HEADERS
where LOAN_ID = P_LOAN_ID and
DISBURSEMENT_NUMBER = 1;
select count(1)
from LNS_COND_ASSIGNMENTS
where
DISB_HEADER_ID = P_DISB_HEADER_ID and
MANDATORY_FLAG = 'Y' and
(CONDITION_MET_FLAG is null or CONDITION_MET_FLAG = 'N') and
(end_date_active is null or trunc(end_date_active) > trunc(sysdate));
UPDATE_DISB_HEADER(
P_API_VERSION => 1.0,
P_INIT_MSG_LIST => FND_API.G_TRUE,
P_COMMIT => FND_API.G_FALSE,
P_VALIDATION_LEVEL => FND_API.G_VALID_LEVEL_FULL,
P_DISB_HEADER_REC => l_DISB_HEADER_REC,
X_RETURN_STATUS => l_return_status,
X_MSG_COUNT => l_msg_count,
X_MSG_DATA => l_msg_data);
LogMessage(FND_LOG.LEVEL_UNEXPECTED, 'Call to UPDATE_DISB_HEADER failed');
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Successfully updated record into LNS_DISB_HEADERS');
select nvl(count(line.DISB_LINE_ID), 0)
from LNS_DISB_LINES line,
LNS_DISB_HEADERS head
where head.LOAN_ID = P_LOAN_ID and
line.DISB_HEADER_ID = head.DISB_HEADER_ID and
line.status is not null and
line.status = 'IN_FUNDING';