[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
PROCEDURE UPDATE_LOAN_FUNDING_STATUS(P_LOAN_ID number);
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...');
select nvl(max(disbursement_number),0)+1 into l_disb_number
from lns_disb_headers
where loan_id = P_DISB_HEADER_REC.LOAN_ID and phase = l_phase;
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 => l_disb_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,
P_PHASE => l_phase,
P_DESCRIPTION => P_DISB_HEADER_REC.DESCRIPTION);
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,
P_PHASE => P_DISB_HEADER_REC.PHASE,
P_DESCRIPTION => P_DISB_HEADER_REC.DESCRIPTION);
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);
delete from lns_distributions where disb_header_id = 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,
P_PARTY_SITE_ID => P_DISB_LINE_REC.PARTY_SITE_ID,
P_INCOME_TAX_REGION => P_DISB_LINE_REC.INCOME_TAX_REGION,
P_TYPE_1099 => P_DISB_LINE_REC.TYPE_1099);
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,
P_PARTY_SITE_ID => P_DISB_LINE_REC.PARTY_SITE_ID,
P_INCOME_TAX_REGION => P_DISB_LINE_REC.INCOME_TAX_REGION,
P_TYPE_1099 => P_DISB_LINE_REC.TYPE_1099);
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 nvl(loan.current_phase, 'TERM')
from LNS_LOAN_HEADERS loan
where loan.LOAN_ID = P_LOAN_ID;
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.DESCRIPTION,
head.HEADER_AMOUNT,
head.HEADER_PERCENT,
head.PAYMENT_REQUEST_DATE,
head.phase,
decode(loan.current_phase, 'OPEN', loan.OPEN_LOAN_START_DATE, loan.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 and
nvl(loan.current_phase, 'TERM') = nvl(head.phase, 'OPEN');
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),
decode(nvl(loan.CURRENT_PHASE, 'TERM'), 'TERM', loan.LOAN_MATURITY_DATE, loan.OPEN_MATURITY_DATE),
loan.loan_status,
head.payment_request_date,
head.phase,
head.description
from LNS_DISB_HEADERS head,
LNS_LOAN_HEADERS_ALL loan
where loan.LOAN_ID = P_LOAN_ID and
head.LOAN_ID = loan.LOAN_ID and
nvl(loan.current_phase, 'TERM') = nvl(head.phase, 'OPEN')
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
,llh.current_phase
,llh.requested_amount + nvl(llh.ADD_REQUESTED_AMOUNT, 0)
,llh.LOAN_CURRENCY
INTO l_payee_party_id
,l_org_id
,l_legal_entity_id
,l_current_phase
,l_requested_amount
,l_currency
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
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Creating default disbursement');
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_HEADER_AMOUNT => l_disb_amount,
P_OBJECT_VERSION_NUMBER => 1,
P_PHASE => l_current_phase);
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_LINE_AMOUNT => l_disb_amount,
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,
nvl(lkp.meaning, head.DESCRIPTION),
loan.LOAN_NUMBER,
loan.EXCHANGE_RATE_TYPE,
loan.EXCHANGE_DATE,
loan.EXCHANGE_RATE,
loan.LOAN_CURRENCY,
loan.ORG_ID,
loan.LEGAL_ENTITY_ID,
loan.current_phase,
line.INVOICE_INTERFACE_ID,
line.PARTY_SITE_ID,
line.INCOME_TAX_REGION,
line.TYPE_1099
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;
select project_id, task_id, award_id, expenditure_type, expenditure_item_date
from LNS_DISTRIBUTIONS dist
where dist.loan_id = P_LOAN_ID
and line_type = 'CLEAR'
and account_name = 'LOAN_PAYABLE'
and account_type = 'CR'
and distribution_type='ORIGINATION'
and activity ='LNS_APPROVAL';
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 => null, -- fix for bug 9265018: let ap to default terms_id
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 => l_DISB_LINE_REC.PARTY_SITE_ID,
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 => null, -- fix for bug 9265018: let ap to default terms_id
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 => l_DISB_LINE_REC.PARTY_SITE_ID,
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 => l_project_id,
X_TASK_ID => l_task_id,
X_EXPENDITURE_TYPE => l_expenditure_type,
X_EXPENDITURE_ITEM_DATE => l_expenditure_item_date,
X_EXPENDITURE_ORGANIZATION_ID => l_expenditure_organization_id,
X_PROJECT_ACCOUNTING_CONTEXT => l_project_accounting_context,
X_PA_ADDITION_FLAG => null,
X_PA_QUANTITY => null,
X_STAT_AMOUNT => null,
X_TYPE_1099 => l_DISB_LINE_REC.TYPE_1099,
X_INCOME_TAX_REGION => l_DISB_LINE_REC.INCOME_TAX_REGION,
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 => l_award_id,
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 head.DISB_HEADER_ID,
nvl(head.AUTOFUNDING_FLAG, 'N')
from LNS_LOAN_HEADERS_ALL loan,
LNS_DISB_HEADERS head
where loan.LOAN_ID = P_LOAN_ID and
loan.loan_id = head.loan_id and
head.DISBURSEMENT_NUMBER = 1 and
nvl(loan.CURRENT_PHASE, 'TERM') = nvl(head.PHASE, 'OPEN');
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));
select object_version_number
from lns_loan_headers
where loan_id = c_loan_id;
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,
head.PAYMENT_REQUEST_DATE,
loan.OBJECT_VERSION_NUMBER,
loan.REQUESTED_AMOUNT + nvl(loan.ADD_REQUESTED_AMOUNT, 0),
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,
head.phase,
loan.LOAN_CURRENCY,
decode(head.ACTIVITY_CODE, null, head.DESCRIPTION, lns_utility_pub.get_lookup_meaning('DISB_ACTIVITY', head.ACTIVITY_CODE))
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,
lns_loan_headers_all loan
where head.LOAN_ID = P_LOAN_ID and
head.loan_id = loan.loan_id and
--nvl(loan.current_phase, 'TERM') = nvl(head.phase, 'OPEN') 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'
-- Only for Term Pase, check the Event_conversion Fees
OR billing_option = decode(nvl(C_PHASE, 'TERM'), 'TERM','TERM_CONVERSION', null)
)
and loan_id = C_LOAN_ID
and phase = C_PHASE;
select INVOICE_AMOUNT, AMOUNT_PAID
from ap_invoices_all
where invoice_id = P_INVOICE_ID;
select min(check_date)
from AP_INVOICE_PAYMENTS_V
where
INVOICE_ID = P_INVOICE_ID;
select dh.disb_header_id
from lns_disb_headers dh
where dh.loan_id = l_loan_header_rec.loan_id
and dh.disb_header_id >= c_disb_hdr_id;
select begin_installment_number
from lns_fee_assignments
where loan_id = p_loan_id
and disb_header_id = p_disb_header_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);
FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
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');
FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
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');
/* No need to call this now, as the below method inserts records of conversionFees into feeSchds table
However, now the conversionFees insert into feeScheds when this fee is assigned to the loan
lns_fee_engine.processDisbursementFees(
p_init_msg_list => FND_API.G_TRUE,
p_commit => FND_API.G_FALSE,
p_phase => 'TERM',
p_loan_id => l_loan_header_rec.loan_id,
p_disb_head_id => null,
x_return_status => l_return_status,
x_msg_count => l_msg_count,
x_msg_data => l_msg_data);
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');
FND_MSG_PUB.DELETE_MSG(FND_MSG_PUB.COUNT_MSG);
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'),
head.LOAN_ID
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;
select count(1)
from lns_distributions
where disb_header_id = c_disb_header_id
and activity = 'LNS_SUBMIT_DISBURSEMENT';
and NOT EXISTS (select 'X' from lns_distributions
where disb_header_id = c_disb_header_id
and activity = 'LNS_SUBMITTED_DISB_CANCEL'
);
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');
UPDATE_LOAN_FUNDING_STATUS(l_DISB_HEADER_REC.LOAN_ID);
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 and
nvl(loan.current_phase, 'TERM') = nvl(head.phase, 'OPEN');
select count(head.DISB_HEADER_ID)
from LNS_DISB_HEADERS head,
LNS_LOAN_HEADERS loan
where head.LOAN_ID = P_LOAN_ID and
head.status <> 'CANCELLED' and
head.LOAN_ID = loan.LOAN_ID and
nvl(loan.current_phase, 'TERM') = nvl(head.phase, 'OPEN');
select nvl(loan.current_phase, 'TERM'),
loan.OBJECT_VERSION_NUMBER
from LNS_LOAN_HEADERS loan
where loan.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 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
head.DISB_HEADER_ID,
head.OBJECT_VERSION_NUMBER
from LNS_DISB_HEADERS head,
LNS_LOAN_HEADERS_ALL loan
where loan.LOAN_ID = P_LOAN_ID and
head.LOAN_ID = loan.LOAN_ID and
head.DISBURSEMENT_NUMBER = 1 and
nvl(loan.current_phase, 'TERM') = nvl(head.phase, 'OPEN');
select count(1)
from LNS_CONDITIONS_VL cond,
LNS_COND_ASSIGNMENTS cond_ass
where cond_ass.DISB_HEADER_ID = P_DISB_HEADER_ID and
cond_ass.MANDATORY_FLAG = 'Y' and
cond_ass.condition_id = cond.condition_id and
cond.CUSTOM_PROCEDURE is null and
(cond_ass.CONDITION_MET_FLAG is null or cond_ass.CONDITION_MET_FLAG = 'N') and
cond_ass.end_date_active is null;
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';
select lines.DISB_LINE_ID,
lines.STATUS,
lines.OBJECT_VERSION_NUMBER,
head.DISB_HEADER_ID,
inv.INVOICE_NUM,
inv.cancelled_date
from LNS_DISB_LINES lines,
LNS_DISB_HEADERS head,
ap_invoices_all inv
where head.loan_id = P_LOAN_ID and
head.DISB_HEADER_ID = lines.DISB_HEADER_ID and
lines.status is not null and
lines.status <> 'CANCELLED' and
lines.invoice_id is not null and
lines.invoice_id = inv.invoice_id and
inv.cancelled_date is not null;
select head.DISB_HEADER_ID,
head.STATUS,
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 max(DISB_HISTORY_ID), max(OBJECT_VERSION_NUMBER+1)
from lns_disb_histories_h
where DISB_LINE_ID = P_DISB_LINE_ID;
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');
UPDATE_LOAN_FUNDING_STATUS(P_LOAN_ID);
PROCEDURE UPDATE_LOAN_FUNDING_STATUS(P_LOAN_ID number)
IS
/*-----------------------------------------------------------------------+
| Local Variable Declarations and initializations |
+-----------------------------------------------------------------------*/
l_api_name CONSTANT VARCHAR2(30) := 'UPDATE_LOAN_FUNDING_STATUS';
select
loan.loan_id,
loan.OBJECT_VERSION_NUMBER,
loan.REQUESTED_AMOUNT + nvl(loan.ADD_REQUESTED_AMOUNT, 0),
loan.loan_status,
loan.SECONDARY_STATUS,
nvl(loan.CURRENT_PHASE, 'TERM')
from LNS_LOAN_HEADERS loan
where loan.LOAN_ID = P_LOAN_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 count(1)
from LNS_DISB_HEADERS head
where head.LOAN_ID = P_LOAN_ID and
(head.STATUS is not null and head.STATUS = P_STATUS);
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);
SELECT llh.primary_borrower_id
,llh.org_id
,llh.legal_entity_id
,llh.current_phase
,llh.LOAN_CURRENCY
INTO l_payee_party_id
,l_org_id
,l_legal_entity_id
,l_current_phase
,l_currency
FROM lns_loan_headers_all llh
WHERE llh.loan_id = p_loan_id;
select lns_disb_headers_s.NEXTVAL into l_DISB_HEADER_REC.DISB_HEADER_ID from dual;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling INSERT_DISB_HEADER...');
INSERT_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_STATEMENT, 'Call to INSERT_DISB_HEADER failed');
select lns_disb_lines_s.NEXTVAL into l_DISB_LINE_REC.DISB_LINE_ID from dual;
LogMessage(FND_LOG.LEVEL_STATEMENT, 'Calling INSERT_DISB_LINE...');
INSERT_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_STATEMENT, 'Call to INSERT_DISB_LINE failed');