[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
INSERT INTO ar_cmgt_cf_dnb_dtls (
case_folder_id,
source_table_name,
source_key,
source_key_type,
source_key_column_name,
source_key_column_type_name,
last_updated_by,
last_update_date,
created_by,
creation_date,
last_update_login)
VALUES
( p_case_folder_id,
p_source_table_name,
p_source_key,
p_source_key_type,
p_source_key_column_name,
p_source_key_column_type,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
sysdate,
fnd_global.login_id);
p_errmsg := 'Error while inserting into ar_cmgt_cf_dnb_dtls '||sqlerrm;
select to_date(p_data_point_value, return_date_format)
INTO l_date_check
from ar_cmgt_data_points_vl
where data_point_id = p_data_point_id
and return_data_type = 'D';
SELECT ar_cmgt_cf_dtls_s.nextval
INTO p_case_folder_detail_id
FROM dual;
INSERT INTO ar_cmgt_cf_dtls
( case_folder_detail_id,
case_folder_id,
data_point_id,
sequence_number,
parent_data_point_id,
parent_cf_detail_id,
data_point_value,
score,
included_in_checklist,
data_point_value_id,
last_updated_by,
created_by,
creation_date,
last_update_login,
last_update_date)
VALUES ( p_case_folder_detail_id,
p_case_folder_id,
p_data_point_id,
p_sequence_number,
p_parent_data_point_id,
p_parent_cf_detail_id,
p_data_point_value,
p_score,
p_included_in_checklist,
p_data_point_value_id,
fnd_global.user_id,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
sysdate);
SELECT ar_cmgt_case_folders_s.nextval
INTO l_case_folder_id
FROM DUAL;
SELECT ar_cmgt_case_folder_number_s.nextval
INTO l_case_folder_number
FROM DUAL;
INSERT INTO ar_cmgt_case_folders (
case_folder_id,
case_folder_number,
credit_request_id,
check_list_id,
status,
cust_account_id,
party_id,
site_use_id,
score_model_id,
credit_classification,
review_type,
type,
limit_currency,
exchange_rate_type,
last_updated,
last_updated_by,
last_update_date,
last_update_login,
creation_date,
created_by,
creation_date_time)
VALUES ( l_case_folder_id,
l_case_folder_number,
p_credit_request_id,
p_check_list_id,
'CREATED',
nvl(p_cust_account_id,-99),
p_party_id,
nvl(p_cust_acct_site_id,-99),
p_score_model_id,
p_credit_classification,
p_review_type,
p_type,
p_limit_currency,
p_exchange_rate_type,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
SYSDATE);
PROCEDURE UPDATE_CASE_FOLDER_DETAILS (
p_case_folder_id IN NUMBER,
p_data_point_id IN NUMBER,
p_data_point_value IN VARCHAR2,
p_score IN NUMBER default NULL,
p_errmsg OUT NOCOPY VARCHAR2,
p_resultout OUT NOCOPY VARCHAR2) IS
BEGIN
p_resultout := 0;
UPDATE ar_cmgt_cf_dtls
SET score = nvl(p_score,score),
data_point_value = nvl(p_data_point_value, data_point_value),
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
WHERE case_folder_id = p_case_folder_id
AND data_point_id = p_data_point_id;
p_errmsg := 'Error While trying to update Case folder Details '||sqlerrm;
PROCEDURE UPDATE_CF_ADP_DETAILS (
p_case_folder_id IN NUMBER,
p_data_point_id IN NUMBER,
p_sequence_number IN NUMBER,
p_parent_data_point_id IN NUMBER,
p_parent_cf_detail_id IN NUMBER,
p_data_point_value IN VARCHAR2,
p_score IN NUMBER default NULL,
p_included_in_checklist IN VARCHAR2 default NULL,
p_data_point_value_id IN NUMBER,
p_case_folder_detail_id IN OUT NOCOPY NUMBER,
x_errmsg OUT NOCOPY VARCHAR2,
x_resultout OUT NOCOPY VARCHAR2)
IS
BEGIN
x_resultout := 0;
UPDATE ar_cmgt_cf_dtls
SET score = nvl(p_score,score),
data_point_value = nvl(p_data_point_value, data_point_value),
last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id
WHERE case_folder_detail_id = p_case_folder_detail_id
AND data_point_id = p_data_point_id;
x_errmsg := 'Error While trying to update Case folder Details '||sqlerrm;
SELECT recommendation_name,source_resp_appln_id
INTO l_recommendation_name, l_appl_id
FROM ar_cmgt_credit_requests
WHERE credit_request_id = p_credit_request_id;
p_errmsg := 'Error While Selecting Recommendation Name'||sqlerrm;
insert into ar_cmgt_cf_recommends
( Recommendation_id,
case_folder_id,
credit_request_id,
credit_review_date,
credit_recommendation,
recommendation_value1,
recommendation_value2,
status,
last_updated_by,
last_update_date,
last_update_login,
creation_date,
created_by,
credit_type,
recommendation_name,
application_id) values
( ar_cmgt_cf_recommends_s.nextval,
p_case_folder_id,
p_credit_request_id,
p_credit_review_date,
p_credit_recommendation,
p_recommendation_value1,
p_recommendation_value2,
p_status,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
sysdate,
fnd_global.user_id,
p_credit_type,
l_recommendation_name,
l_appl_id);
SELECT ar_cmgt_data_points_s.nextval
INTO p_data_point_id
FROM dual;
AR_CMGT_DP_TABLE_HANDLER.insert_row(
p_data_point_name => p_data_point_name,
p_description => null,
p_data_point_category => p_data_point_category,
p_user_defined_flag => p_user_defined_flag,
p_scorable_flag => p_scorable_flag,
p_display_on_checklist => p_display_on_checklist,
p_created_by => fnd_global.user_id,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_data_point_id => p_data_point_id,
p_return_data_type => 'C',
p_return_date_format => null,
p_application_id => 222,
p_parent_data_point_id => null,
p_enabled_flag => 'N',
p_package_name => null,
p_function_name => null,
p_data_point_sub_category => null,
p_data_point_code => p_data_point_code);
SELECT ar_cmgt_data_points_s.nextval
INTO l_dp_id
FROM dual;
AR_CMGT_DP_TABLE_HANDLER.insert_adp_row(
p_data_point_code => p_data_point_code,
p_data_point_name => p_data_point_name,
p_description => p_description,
p_data_point_sub_category => p_data_point_sub_category,
p_data_point_category => p_data_point_category,
p_user_defined_flag => p_user_defined_flag,
p_scorable_flag => p_scorable_flag,
p_display_on_checklist => p_display_on_checklist,
p_created_by => fnd_global.user_id,
p_last_updated_by => fnd_global.user_id,
p_last_update_login => fnd_global.login_id,
p_data_point_id => l_dp_id,
p_application_id => p_application_id,
p_parent_data_point_id => p_parent_data_point_id,
p_enabled_flag => p_enabled_flag,
p_package_name => p_package_name,
p_function_name => p_function_name,
p_function_type => p_function_type,
p_return_data_type => p_return_data_type,
p_return_date_format => p_return_date_format );
INSERT INTO AR_CMGT_CF_AGING_DTLS
(
case_folder_id,
last_updated_by,
last_update_date,
last_update_login,
creation_date,
created_by,
aging_bucket_id,
aging_bucket_line_id,
amount
)
values
(p_case_folder_id,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
sysdate,
fnd_global.user_id,
p_aging_bucket_id,
p_aging_bucket_line_id,
p_amount
);
PROCEDURE update_aging_dtls(
p_case_folder_id IN NUMBER,
p_aging_bucket_id IN NUMBER,
p_aging_bucket_line_id IN NUMBER,
p_amount IN NUMBER,
p_error_msg OUT NOCOPY VARCHAR2,
p_resultout OUT NOCOPY VARCHAR2) IS
BEGIN
UPDATE AR_CMGT_CF_AGING_DTLS
SET last_updated_by = fnd_global.user_id,
last_update_date = sysdate,
last_update_login = fnd_global.login_id,
amount = p_amount
WHERE case_folder_id = p_case_folder_id
AND aging_bucket_id = p_aging_bucket_id
AND aging_bucket_line_id = p_aging_bucket_line_id;
INSERT INTO AR_CMGT_CASE_FOLDERS
(
CASE_FOLDER_ID,
CASE_FOLDER_NUMBER,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
CREDIT_REQUEST_ID,
CHECK_LIST_ID,
STATUS,
CUST_ACCOUNT_ID,
PARTY_ID,
SCORE_MODEL_ID,
SITE_USE_ID,
CREDIT_CLASSIFICATION,
REVIEW_TYPE,
CREDIT_ANALYST_ID,
TYPE,
DISPLAY_FLAG,
CREATION_DATE_TIME,
LAST_UPDATED,
LIMIT_CURRENCY,
EXCHANGE_RATE_TYPE,
REVIEW_CYCLE)
( SELECT AR_CMGT_CASE_FOLDERS_s.NEXTVAL,
ar_cmgt_case_folder_number_s.nextval,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
l_credit_request_id,
CHECK_LIST_ID,
'CREATED',
CUST_ACCOUNT_ID,
PARTY_ID,
SCORE_MODEL_ID,
SITE_USE_ID,
CREDIT_CLASSIFICATION,
REVIEW_TYPE,
CREDIT_ANALYST_ID,
TYPE,
DISPLAY_FLAG,
SYSDATE,
SYSDATE,
LIMIT_CURRENCY,
EXCHANGE_RATE_TYPE,
REVIEW_CYCLE
FROM AR_CMGT_CASE_FOLDERS
WHERE CASE_FOLDER_ID = l_case_folders_id
AND TYPE = 'CASE') ;
CURSOR select_dtls IS
SELECT case_folder_id,
DATA_POINT_ID,
DATA_POINT_VALUE,
INCLUDED_IN_CHECKLIST,
SCORE,
CASE_FOLDER_DETAIL_ID,
SEQUENCE_NUMBER,
PARENT_DATA_POINT_ID,
PARENT_CF_DETAIL_ID
FROM AR_CMGT_CF_DTLS
WHERE CASE_FOLDER_ID=p_parnt_case_folder_id;
SELECT CASE_FOLDER_ID
INTO l_case_folders_id
FROM AR_CMGT_CASE_FOLDERS
WHERE CREDIT_REQUEST_ID = p_credit_request_id;
FOR select_dtls_rec IN select_dtls
LOOP
BEGIN
SELECT ar_cmgt_cf_dtls_s.NEXTVAL
INTO l_seq_num
FROM dual;
l_rec_changed.parent := select_dtls_rec.CASE_FOLDER_DETAIL_ID;
--insert the records into case folder details.
INSERT INTO AR_CMGT_CF_DTLS
(CASE_FOLDER_ID,
DATA_POINT_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
DATA_POINT_VALUE,
INCLUDED_IN_CHECKLIST,
SCORE,
CASE_FOLDER_DETAIL_ID,
SEQUENCE_NUMBER,
PARENT_DATA_POINT_ID,
PARENT_CF_DETAIL_ID)
(select l_case_folders_id,
DATA_POINT_ID,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
SYSDATE,
fnd_global.user_id,
DATA_POINT_VALUE,
INCLUDED_IN_CHECKLIST,
SCORE,
l_seq_num,
SEQUENCE_NUMBER,
PARENT_DATA_POINT_ID,
PARENT_CF_DETAIL_ID
FROM AR_CMGT_CF_DTLS
WHERE CASE_FOLDER_ID=p_parnt_case_folder_id
AND CASE_FOLDER_DETAIL_ID = select_dtls_rec.CASE_FOLDER_DETAIL_ID);
--update the records with parent cf detail id
--with the new changed values
if l_processing_flag = 'Y'
THEN
counter1 := 1;
UPDATE ar_cmgt_cf_dtls
SET parent_cf_detail_id = l_rec_changed.changed
WHERE case_folder_id = l_case_folders_id
AND PARENT_CF_DETAIL_ID = l_rec_changed.parent;
SELECT CASE_FOLDER_ID
INTO l_case_folders_id
FROM AR_CMGT_CASE_FOLDERS
WHERE CREDIT_REQUEST_ID = p_credit_request_id;
INSERT INTO AR_CMGT_CF_AGING_DTLS
(CASE_FOLDER_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
AGING_BUCKET_ID,
AGING_BUCKET_LINE_ID,
AMOUNT,
COUNT)
(SELECT l_case_folders_id,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
SYSDATE,
fnd_global.user_id,
AGING_BUCKET_ID,
AGING_BUCKET_LINE_ID,
AMOUNT,
COUNT
FROM AR_CMGT_CF_AGING_DTLS
WHERE CASE_FOLDER_ID=p_parnt_case_folder_id);
SELECT CASE_FOLDER_ID
INTO l_case_folders_id
FROM AR_CMGT_CASE_FOLDERS
WHERE CREDIT_REQUEST_ID = p_credit_request_id;
INSERT INTO AR_CMGT_CF_DNB_DTLS
(CASE_FOLDER_ID,
SOURCE_TABLE_NAME,
SOURCE_KEY,
SOURCE_KEY_TYPE,
SOURCE_KEY_COLUMN_NAME,
SOURCE_KEY_COLUMN_TYPE_NAME,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY)
(SELECT l_case_folders_id,
SOURCE_TABLE_NAME,
SOURCE_KEY,
SOURCE_KEY_TYPE,
SOURCE_KEY_COLUMN_NAME,
SOURCE_KEY_COLUMN_TYPE_NAME,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
sysdate,
fnd_global.user_id
FROM AR_CMGT_CF_DNB_DTLS
WHERE CASE_FOLDER_ID=p_parnt_case_folder_id);
SELECT CASE_FOLDER_ID
INTO l_case_folders_id
FROM AR_CMGT_CASE_FOLDERS
WHERE CREDIT_REQUEST_ID = p_credit_request_id;
INSERT INTO AR_CMGT_FINANCIAL_DATA
(FINANCIAL_DATA_ID,
CREDIT_REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
REPORTING_CURRENCY,
MONETARY_UNIT,
CURR_FIN_ST_DATE,
REPORTING_PERIOD,
CASH,
ACCOUNTS_PAYABLE,
NET_RECEIVABLES,
SHORT_TERM_DEBT,
INVENTORIES,
OTHER_CUR_LIABILITIES,
OTHER_CUR_ASSETS,
TOTAL_CUR_LIABILITIES,
TOTAL_CUR_ASSETS,
LONG_TERM_DEBT,
NET_FIXED_ASSETS,
OTHER_NON_CUR_LIABILITIES,
OTHER_NON_CUR_ASSETS,
TOTAL_LIABILITIES,
TOTAL_ASSETS,
STOCKHOLDER_EQUITY,
TOTAL_LIABILITIES_EQUITY,
REVENUE,
NON_OPERATING_INCOME,
COST_OF_GOODS_SOLD,
NON_OPERATING_EXPENSES,
SGA_EXPENSES,
PRE_TAX_NET_INCOME,
OPERATING_INCOME,
INCOME_TAXES,
OPERATING_MARGIN,
NET_INCOME,
EARNINGS_PER_SHARE,
CASE_FOLDER_ID,
PARTY_ID,
CUST_ACCOUNT_ID,
SITE_USE_ID)
(SELECT AR_CMGT_FINANCIAL_DATA_S.nextval,
l_credit_request_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
REPORTING_CURRENCY,
MONETARY_UNIT,
CURR_FIN_ST_DATE,
REPORTING_PERIOD,
CASH,
ACCOUNTS_PAYABLE,
NET_RECEIVABLES,
SHORT_TERM_DEBT,
INVENTORIES,
OTHER_CUR_LIABILITIES,
OTHER_CUR_ASSETS,
TOTAL_CUR_LIABILITIES,
TOTAL_CUR_ASSETS,
LONG_TERM_DEBT,
NET_FIXED_ASSETS,
OTHER_NON_CUR_LIABILITIES,
OTHER_NON_CUR_ASSETS,
TOTAL_LIABILITIES,
TOTAL_ASSETS,
STOCKHOLDER_EQUITY,
TOTAL_LIABILITIES_EQUITY,
REVENUE,
NON_OPERATING_INCOME,
COST_OF_GOODS_SOLD,
NON_OPERATING_EXPENSES,
SGA_EXPENSES,
PRE_TAX_NET_INCOME,
OPERATING_INCOME,
INCOME_TAXES,
OPERATING_MARGIN,
NET_INCOME,
EARNINGS_PER_SHARE,
nvl(l_case_folders_id,-99),
PARTY_ID,
CUST_ACCOUNT_ID,
SITE_USE_ID
FROM AR_CMGT_FINANCIAL_DATA
WHERE CREDIT_REQUEST_ID = p_parnt_credit_req_id);
SELECT CASE_FOLDER_ID
INTO l_case_folders_id
FROM AR_CMGT_CASE_FOLDERS
WHERE CREDIT_REQUEST_ID = p_credit_request_id;
INSERT INTO ar_cmgt_trade_ref_data
(DATAPOINT_ID,
CREDIT_REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
COUNTRY,
TAX_NUMBER,
REFERENCE_NAME,
CONTACT_NAME,
ADDRESS,
PHONE_NUMBER,
CITY,
FAX_NUMBER,
STATE,
EMAIL,
POSTAL_CODE,
PROVINCE,
URL,
REPORT_DATE,
LAST_TRANSACTION_DATE,
NUMBERS_OF_YEARS_IN_TRADE,
PAYMENT_TERMS,
CREDIT_LIMIT,
CURRENCY,
AMOUNT_OWED,
CREDIT_BALANCE,
PAST_DUE_AMOUNT,
INTERNAL_TRADE_RATING,
CASE_FOLDER_ID,
NOTES)
(SELECT AR_CMGT_TRADE_REF_DATA_S.NEXTVAL,
l_credit_request_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
COUNTRY,
TAX_NUMBER,
REFERENCE_NAME,
CONTACT_NAME,
ADDRESS,
PHONE_NUMBER,
CITY,
FAX_NUMBER,
STATE,
EMAIL,
POSTAL_CODE,
PROVINCE,
URL,
REPORT_DATE,
LAST_TRANSACTION_DATE,
NUMBERS_OF_YEARS_IN_TRADE,
PAYMENT_TERMS,
CREDIT_LIMIT,
CURRENCY,
AMOUNT_OWED,
CREDIT_BALANCE,
PAST_DUE_AMOUNT,
INTERNAL_TRADE_RATING,
nvl(l_case_folders_id,-99),
NOTES
FROM AR_CMGT_TRADE_REF_DATA
WHERE CREDIT_REQUEST_ID= p_parnt_credit_req_id);
SELECT DATAPOINT_ID
FROM ar_cmgt_bank_ref_data
WHERE CREDIT_REQUEST_ID = p_parnt_credit_req_id;
SELECT CASE_FOLDER_ID
INTO l_case_folders_id
FROM AR_CMGT_CASE_FOLDERS
WHERE CREDIT_REQUEST_ID = p_credit_request_id;
SELECT AR_CMGT_BANK_REF_DATA_S.NEXTVAL
INTO l_next_val FROM dual;
INSERT INTO ar_cmgt_bank_ref_data
(DATAPOINT_ID,
CREDIT_REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
BANK_NAME,
ADDRESS,
CITY,
STATE,
POSTAL_CODE,
PROVINCE,
COUNTRY,
CONTACT_NAME,
PHONE,
FAX,
EMAIL,
URL,
BANK_ROUTING_NUMBER,
CASE_FOLDER_ID,
NOTES)
(SELECT l_next_val,
l_credit_request_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
BANK_NAME,
ADDRESS,
CITY,
STATE,
POSTAL_CODE,
PROVINCE,
COUNTRY,
CONTACT_NAME,
PHONE,
FAX,
EMAIL,
URL,
BANK_ROUTING_NUMBER,
nvl(l_case_folders_id,-99),
NOTES
FROM ar_cmgt_bank_ref_data
WHERE CREDIT_REQUEST_ID = p_parnt_credit_req_id
AND DATAPOINT_ID = l_data_point_id);
INSERT INTO AR_CMGT_BANK_REF_ACCTS
(BANK_REFERENCE_ACCOUNT_ID,
DATAPOINT_ID,
CREDIT_REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
ACCOUNT_NUMBER,
ACCOUNT_TYPE,
DATE_OPENED,
CURRENCY,
CURRENT_BALANCE,
BALANCE_DATE,
AVERAGE_BALANCE)
( SELECT AR_CMGT_BANK_REF_ACCTS_S.NEXTVAL,
l_next_val,
l_credit_request_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
ACCOUNT_NUMBER,
ACCOUNT_TYPE,
DATE_OPENED,
CURRENCY,
CURRENT_BALANCE,
BALANCE_DATE,
AVERAGE_BALANCE
FROM AR_CMGT_BANK_REF_ACCTS
WHERE CREDIT_REQUEST_ID= p_parnt_credit_req_id
AND DATAPOINT_ID = l_data_point_id);
SELECT CASE_FOLDER_ID
INTO l_case_folders_id
FROM AR_CMGT_CASE_FOLDERS
WHERE CREDIT_REQUEST_ID = p_credit_request_id;
INSERT INTO ar_cmgt_collateral_data
(DATAPOINT_ID,
CREDIT_REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
COLLATERAL_DESCRIPTION,
COLLATERAL_CATEGORY,
COLLATERAL_VALUE,
COLLATERAL_CURRENCY,
VALUATION_TYPE,
VALUATION_DATE,
PREV_VALUATION_DATE,
APPRAISER_NAME,
APPRAISER_PHONE_NUMBER,
COLLATERAL_LOCATION,
CASE_FOLDER_ID,
NOTES)
( SELECT AR_CMGT_COLLATERAL_DATA_S.NEXTVAL,
l_credit_request_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
COLLATERAL_DESCRIPTION,
COLLATERAL_CATEGORY,
COLLATERAL_VALUE,
COLLATERAL_CURRENCY,
VALUATION_TYPE,
VALUATION_DATE,
PREV_VALUATION_DATE,
APPRAISER_NAME,
APPRAISER_PHONE_NUMBER,
COLLATERAL_LOCATION,
nvl(l_case_folders_id,-99),
NOTES
FROM ar_cmgt_collateral_data
WHERE CREDIT_REQUEST_ID = p_parnt_credit_req_id);
SELECT CASE_FOLDER_ID
INTO l_case_folders_id
FROM AR_CMGT_CASE_FOLDERS
WHERE CREDIT_REQUEST_ID = p_credit_request_id;
INSERT INTO ar_cmgt_other_data
(DATAPOINT_ID,
CREDIT_REQUEST_ID,
LAST_UPDATE_DATE,
LAST_UPDATED_BY,
CREATION_DATE,
CREATED_BY,
LAST_UPDATE_LOGIN,
COUNTRY,
KEY_EXECUTIVE,
VENTURE_CAPITAL_NAME,
VC_CONTACT_NAME,
VC_ADDRESS,
VC_CONTACT_PHONE,
VC_STATE,
VC_CONTACT_FAX,
VC_POSTAL_CODE,
VC_PROVINCE,
VC_CONTACT_EMAIL,
CAPITAL_STAGE_COMPLETED,
CURRENCY,
FUNDING_AMOUNT,
PERCENT_INVESTED,
BURN_RATE,
FUTURE_FUNDING_PLANS,
NOTES,
CASE_FOLDER_ID)
( SELECT AR_CMGT_OTHER_DATA_S.NEXTVAL,
l_credit_request_id,
SYSDATE,
fnd_global.user_id,
SYSDATE,
fnd_global.user_id,
fnd_global.login_id,
COUNTRY,
KEY_EXECUTIVE,
VENTURE_CAPITAL_NAME,
VC_CONTACT_NAME,
VC_ADDRESS,
VC_CONTACT_PHONE,
VC_STATE,
VC_CONTACT_FAX,
VC_POSTAL_CODE,
VC_PROVINCE,
VC_CONTACT_EMAIL,
CAPITAL_STAGE_COMPLETED,
CURRENCY,
FUNDING_AMOUNT,
PERCENT_INVESTED,
BURN_RATE,
FUTURE_FUNDING_PLANS,
NOTES,
nvl(l_case_folders_id,-99)
FROM ar_cmgt_other_data
WHERE CREDIT_REQUEST_ID = p_parnt_credit_req_id);
SELECT CASE_FOLDER_ID
INTO l_case_folders_id
FROM AR_CMGT_CASE_FOLDERS
WHERE CREDIT_REQUEST_ID = p_credit_request_id;
INSERT INTO AR_CMGT_CF_RECOMMENDS
(RECOMMENDATION_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
CASE_FOLDER_ID,
CREDIT_REQUEST_ID,
CREDIT_REVIEW_DATE,
CREDIT_RECOMMENDATION,
RECOMMENDATION_VALUE1,
RECOMMENDATION_VALUE2,
STATUS,
CREDIT_TYPE,
RECOMMENDATION_NAME,
APPLICATION_ID)
(SELECT AR_CMGT_CF_RECOMMENDS_S.NEXTVAL,
fnd_global.user_id,
SYSDATE,
fnd_global.login_id,
SYSDATE,
fnd_global.user_id,
l_case_folders_id,
l_credit_request_id,
CREDIT_REVIEW_DATE,
CREDIT_RECOMMENDATION,
RECOMMENDATION_VALUE1,
RECOMMENDATION_VALUE2,
STATUS,
CREDIT_TYPE,
RECOMMENDATION_NAME,
APPLICATION_ID
FROM AR_CMGT_CF_RECOMMENDS
WHERE CASE_FOLDER_ID= p_parnt_case_folder_id);
SELECT CASE_FOLDER_ID
INTO l_case_folders_id
FROM AR_CMGT_CASE_FOLDERS
WHERE CREDIT_REQUEST_ID = p_credit_request_id;
INSERT INTO AR_CMGT_CF_ANL_NOTES
(ANALYSIS_NOTES_ID,
CASE_FOLDER_ID,
LAST_UPDATED_BY,
LAST_UPDATE_DATE,
LAST_UPDATE_LOGIN,
CREATION_DATE,
CREATED_BY,
TOPIC,
DISPLAY,
IMPORTANCE,
NOTES,
DATE_OPENED)
(SELECT AR_CMGT_CF_ANL_NOTES_S.NEXTVAL,
l_case_folders_id,
fnd_global.user_id,
sysdate,
fnd_global.login_id,
sysdate,
fnd_global.user_id,
TOPIC,
DISPLAY,
IMPORTANCE,
NOTES,
DATE_OPENED
FROM AR_CMGT_CF_ANL_NOTES
WHERE CASE_FOLDER_ID= p_parnt_case_folder_id);
PROCEDURE UPDATE_CASEFOLDER_DETAILS(
P_DATA_POINT_ID IN NUMBER,
P_CASE_FOLDER_ID IN NUMBER,
P_RESULT OUT NOCOPY NUMBER) IS
BEGIN
--initialization
P_RESULT:=0;
update ar_cmgt_cf_dtls
set included_in_checklist = 'Y'
where case_folder_id=P_CASE_FOLDER_ID
and (data_point_id=P_DATA_POINT_ID
OR PARENT_DATA_POINT_ID =P_DATA_POINT_ID);
PROCEDURE UPDATE_CF_DETAILS_NEGATION(
P_DATA_POINT_ID IN NUMBER,
P_CASE_FOLDER_ID IN NUMBER,
P_RESULT OUT NOCOPY NUMBER) IS
BEGIN
--initialization
P_RESULT:=0;
update ar_cmgt_cf_dtls
set included_in_checklist = 'N'
where case_folder_id=P_CASE_FOLDER_ID
and data_point_id=P_DATA_POINT_ID;