[Home] [Help]
The following lines contain the word 'select', 'insert', 'update' or 'delete':
delete from xtr_interface_errors
where external_deal_id = ARec_Interface.external_deal_id
and deal_type = ARec_Interface.deal_type;
/*wdk: would like to do get deal_no here, and then calc trans and various inserts*/
CHECK_ACCRUAL_REVAL(ARec_Interface);
/* Call the insert procedure to insert into xtr_deals */
/*----------------------------------------------------*/
CREATE_NI_DEAL;
XTR_LIMITS_P.UPDATE_LIMIT_EXCESS_LOG(G_Ni_Main_Rec.DEAL_NO,
1,
Fnd_Global.User_Id,
v_limit_log_return);
/* Since the insert is done, we can now delete the rows from the interface tables. */
/*----------------------------------------------------------------------------------*/
delete from xtr_deals_interface
where external_deal_id = ARec_Interface.external_deal_id
and deal_type = ARec_Interface.deal_type;
delete from xtr_transactions_interface
where external_deal_id = ARec_Interface.external_deal_id
and deal_type = ARec_Interface.deal_type;
update xtr_deals_interface
set load_status_code = 'ERROR',
last_update_date = G_Curr_Date,
Last_Updated_by = g_user_id
where external_deal_id = ARec_Interface.external_deal_id
and deal_type = ARec_Interface.deal_type;
SELECT *
FROM XTR_TRANSACTIONS_INTERFACE
WHERE EXTERNAL_DEAL_ID = ARec_Interface.external_deal_id
AND DEAL_TYPE = ARec_Interface.deal_type;
SELECT *
FROM XTR_TRANSACTIONS_INTERFACE
WHERE EXTERNAL_DEAL_ID = ARec_Interface.external_deal_id
AND DEAL_TYPE = ARec_Interface.deal_type;
select count(*)
into l_count
from xtr_parties_v
where party_type = 'CP'
and party_category = 'CL'
and party_code = p_client_code;
select count(*)
into l_count
from xtr_portfolios_v
where company_code = p_company_code
and (external_party is null or external_party = p_cparty_code)
and portfolio = p_portfolio_code;
Select count(*)
Into l_count
from xtr_master_currencies_v
where nvl(authorised,'N') = 'Y'
And currency = p_currency;
select count(*)
into l_count
from xtr_bank_accounts_v
where party_code = p_company_code
and currency = p_currency
and nvl(setoff_account_yn,'N') = 'N'
and account_number = p_account_no;
select count(*)
into l_count
from xtr_bank_accounts_v
where currency = p_currency
and party_code = p_cparty_code
and account_number = p_cparty_account_no
and nvl(authorised,'N') = 'Y'
and bank_short_code = p_cparty_ref;
select count(*)
into l_count
from xtr_deal_linking_v
where deal_linking_code = p_deal_linking_code;
select count(*)
into l_count
from xtr_tax_brokerage_setup_v a, xtr_deduction_calcs_v b
where a.reference_code = p_brokerage_code
and a.deal_type = 'NI'
and a.deduction_type = 'B'
and a.deal_type = b.deal_type
and a.calc_type = b.calc_type
and nvl(a.authorised,'N') = 'Y';
select count(*)
into l_count
from xtr_dealer_codes_v
where dealer_code = p_dealer_code;
select count(*)
into l_count
from xtr_party_info_v
where party_type in ('CP','C')
and ((party_type = 'CP' and mm_cparty='Y')
or party_type = 'C')
and party_code = p_cparty_code
and party_code <> p_company_code
and nvl(authorised,'N') = 'Y';
select deal_subtype
from xtr_deal_subtypes
where deal_type = 'NI'
and user_deal_subtype = p_user_deal_subtype
and nvl(authorised,'N') = 'Y'
and rownum = 1;
select count(*)
into l_count
from xtr_auth_deal_subtypes_v a,
xtr_deal_subtypes b
where a.deal_type = 'NI'
and a.deal_type = b.deal_type
and a.deal_subtype = b.user_deal_subtype
and b.deal_subtype in ('BUY','ISSUE')
and b.deal_subtype = g_ni_deal_subtype;
select count(*)
into l_count
from xtr_product_types_v
where deal_type = 'NI'
and product_auth = 'Y'
and product_type = p_product_type
and product_type in(select product_type
from xtr_auth_product_types_v
where deal_type='NI'
and deal_subtype=p_deal_subtype);
select count(*)
into l_count
from xtr_price_models
where code = p_pricing_model
and deal_type = 'NI'
and nvl(authorized,'N') = 'Y';
select count(*)
into l_count
from xtr_rm_md_sets
where set_code = p_market_data_set
and nvl(authorized_yn,'N') = 'Y';
select count(*)
into l_count
from xtr_parties_v
where party_code=p_party_code;
select count(*)
into l_count
from xtr_counterparty_limits_v a,
xtr_limit_types_v b
where a.company_code = p_company_code
and (a.cparty_code = p_acceptor_code or a.cparty_code = p_endorser_code or a.cparty_code = p_drawer_code)
and a.limit_code <> 'SETTLE'
and a.limit_type = b.limit_type
and ((fx_invest_fund_type in ('X','I') and g_ni_deal_subtype in ('BUY','COVER')) or
(fx_invest_fund_type in ('X','F') and g_ni_deal_subtype in ('SELL','SHORT','ISSUE')))
and a.authorised='Y' and nvl(a.expiry_date,sysdate+1)>sysdate
and a.limit_code = p_limit_code;
select count(*)
into l_count
from fnd_lookups
where lookup_type='XTR_ROUNDING_TYPE'
and lookup_code=p_rounding_type;
select count(*)
into l_count
from fnd_lookups
where lookup_type='XTR_DAY_COUNT_TYPE'
and lookup_code=p_day_count_type;
select count(*)
into l_count
from fnd_lookups
where lookup_type='XTR_DAY_COUNT_BASIS'
AND lookup_code IN ('30/','30E+/','30E/','ACTUAL/ACTUAL','ACTUAL360','ACTUAL365')
and lookup_code=p_year_calc_type;
select count(*)
into l_count
from fnd_lookups
where lookup_type='XTR_DISCOUNT_YIELD'
and lookup_code=p_basis_type;
select count(*)
into l_count
from fnd_lookups
where lookup_type='XTR_PRINCIPAL_SETTLED_BY'
and lookup_code=p_client_settle;
select count(*)
into l_count
from xtr_tax_brokerage_setup a,
fnd_lookups lu, xtr_tax_deduction_calcs c
where a.deal_type = 'NI'
and lu.lookup_type='XTR_TAX_CALC_TYPES'
and lu.lookup_code=a.calc_type
and a.deduction_type = 'T'
and a.deal_type = c.deal_type
and a.calc_type = c.calc_type
and a.tax_settle_method = c.tax_settle_method
and nvl(a.authorised, 'N') = 'Y'
and c.principal_or_income_tax='P'
and c.deal_subtype=g_ni_deal_subtype
and a.reference_code=p_tax_code;
select count(*)
into l_count
from xtr_tax_brokerage_setup a,
fnd_lookups lu, xtr_tax_deduction_calcs c
where a.deal_type = 'NI'
and lu.lookup_type='XTR_TAX_CALC_TYPES'
and lu.lookup_code=a.calc_type
and a.deduction_type = 'T'
and a.deal_type = c.deal_type
and a.calc_type = c.calc_type
and a.tax_settle_method = c.tax_settle_method
and nvl(a.authorised, 'N') = 'Y'
and c.principal_or_income_tax='I'
and c.deal_subtype=g_ni_deal_subtype
and a.reference_code=p_tax_code;
Select allow_override
From Xtr_Dealer_Codes
Where user_id = fnd_global.user_id;
select to_number(
decode(it.company_code,null,0,8)
+ decode(it.cparty_code, null,0,4)
+ decode(it.deal_type, null,0,2)
+ decode(it.currency_code,null,0,1)) total_weight,
it.limit_amount,
it.amount_percent,
it.currency_code,
it.interest_tolerance_id
from xtr_interest_tolerances it
where it.company_code = p_company_code
and nvl(it.cparty_code, p_cparty_code) = p_cparty_code
and nvl(it.deal_type, p_deal_type) = p_deal_type
and nvl(it.currency_code, p_currency_code) = p_currency_code
and authorized='Y';
select PARAM_VALUE
from XTR_PRO_PARAM
where param_name ='SYSTEM_FUNCTIONAL_CCY';
select nvl(set_of_books_currency,l_system_currency)
into l_function_currency
from xtr_parties_v
where party_code = p_company_code;
select count(*)
into l_count
from xtr_bill_bond_issues_v
where ni_or_bond='NI'
and issue_date is null
and status is null
and serial_number=p_serial_number;
select tolerance
from xtr_deal_subtypes
where deal_type='NI'
and deal_subtype=g_ni_deal_subtype;
select PARAMETER_VALUE_CODE from XTR_COMPANY_PARAMETERS P
where p.company_code = p_company
and p.parameter_code = 'ACCNT_TSDTM';
SELECT count(*)
FROM
xtr_batches b,xtr_batch_events e
WHERE
b.company_code = p_company
AND b.batch_id = e.batch_id
AND e.event_code = 'REVAL'
AND b.period_end >= p_date;
select PARAMETER_VALUE_CODE from XTR_COMPANY_PARAMETERS P
where p.company_code = p_company
and p.parameter_code = 'ACCNT_TSDTM';
SELECT count(*)
FROM
xtr_batches b,xtr_batch_events e
WHERE
b.company_code = p_company
AND b.batch_id = e.batch_id
AND e.event_code = 'ACCRUAL'
AND b.period_end >= p_date;
select a.cparty_code
from xtr_counterparty_limits_v a,
xtr_limit_types_v b
where a.company_code = G_Ni_Main_Rec.company_code
and (a.cparty_code = G_Ni_Main_Rec.acceptor_code
or a.cparty_code = G_Ni_Main_Rec.endorser_code
or a.cparty_code = G_Ni_Main_Rec.drawer_code)
and a.limit_code <> 'SETTLE'
and a.limit_type = b.limit_type
and ((fx_invest_fund_type in ('X','I') and G_Ni_Main_Rec.deal_subtype in ('BUY','COVER')) or
(fx_invest_fund_type in ('X','F') and G_Ni_Main_Rec.deal_subtype in ('SELL','SHORT','ISSUE')))
and a.authorised='Y' and nvl(a.expiry_date,sysdate+1)>sysdate
and a.limit_code=G_Ni_Main_Rec.riskparty_limit_code
and rownum=1;
SELECT *
FROM XTR_TRANSACTIONS_INTERFACE
WHERE EXTERNAL_DEAL_ID = ARec_Interface.external_deal_id
AND DEAL_TYPE = ARec_Interface.deal_type;
G_Ni_Parcel_Rec.DELETE;
G_Ni_Trans_Flex.DELETE;
G_Ni_Parcel_Rec(i).SELECT_NUMBER := NULL;
G_Ni_Parcel_Rec(i).SELECT_FV_AMOUNT := NULL;
G_Ni_Parcel_Rec(i).OLD_SELECT_NUMBER := NULL;
select xtr_deals_s.nextval
into G_Ni_Main_Rec.DEAL_NO
from dual;
select m.rounding_factor
from xtr_master_currencies_v m
where m.currency = p_curr;
select nvl(m.HCE_RATE,1)
from xtr_MASTER_CURRENCIES_v m
where m.CURRENCY = G_Ni_Main_Rec.CURRENCY;
select m.ROUNDING_FACTOR
from xtr_PARTIES_v p,
xtr_MASTER_CURRENCIES_v m
where p.PARTY_CODE = G_Ni_Main_Rec.COMPANY_CODE
and p.PARTY_TYPE = 'C'
and m.CURRENCY = p.HOME_CURRENCY;
select tax_settle_method,calc_type
from xtr_tax_brokerage_setup_v
where reference_code = p_tax_code;
select tax_code,income_tax_code from xtr_deals
where deal_no=p_deal_no;
select nvl(m.ROUNDING_FACTOR,2)
from xtr_MASTER_CURRENCIES_v m
where m.CURRENCY = p_ccy;
select tax_rounding_rule,tax_rounding_precision
from XTR_TAX_BROKERAGE_SETUP
where reference_code=p_tax_code;
G_FV_AMT_HCE.DELETE;
G_INTEREST_HCE.DELETE;
G_PRN_TAX_AMOUNT.DELETE;
G_INT_TAX_AMOUNT.DELETE;
select xtr_exposure_trans_s.nextval
into G_Ni_Parcel_Rec(i).PARCEL_SPLIT_NO --This overwrites old transaction tracking number
from dual;
select m.rounding_factor
from xtr_parties_v p,
xtr_master_currencies_v m
where p.party_code = G_Ni_Main_Rec.company_code
and p.party_type = 'C'
and m.currency = p.home_currency;
select round((G_Ni_Main_Rec.START_AMOUNT / nvl(s.HCE_RATE,1)),nvl(l_roundfac,2)),
round((G_Ni_Main_Rec.MATURITY_AMOUNT / nvl(s.HCE_RATE,1)),nvl(l_roundfac,2)),
round((G_Ni_Main_Rec.INTEREST_AMOUNT / nvl(s.HCE_RATE,1)),nvl(l_roundfac,2)),
round((nvl(G_Ni_Main_Rec.BROKERAGE_AMOUNT,0) / nvl(s.HCE_RATE,1)),nvl(l_roundfac,2))
from XTR_MASTER_CURRENCIES_v s
where s.CURRENCY = G_Ni_Main_Rec.CURRENCY;
select dealer_code
from xtr_dealer_codes_v
where user_id = p_fnd_user;
select bank_code
from xtr_bill_bond_issues_v
where ni_or_bond='NI'
and serial_number=p_serial_number;
select BANK_CODE
from XTR_BANK_ACCOUNTS_V
where ACCOUNT_NUMBER = G_Ni_Main_Rec.Maturity_Account_No
and PARTY_CODE = G_Ni_Main_Rec.Company_Code;
/* -------- to insert parcels into transaction table -------*/
/*------------------------------------------------------------*/
PROCEDURE CREATE_TRANSACTIONS IS
/*------------------------------------------------------------*/
l_tran_no NUMBER:=0;
insert into XTR_ROLLOVER_TRANSACTIONS_V
(DEAL_NUMBER,DEAL_DATE,TRANSACTION_NUMBER,DEAL_TYPE,DEAL_SUBTYPE,
BALANCE_OUT,CURRENCY,PARCEL_SPLIT_NO,CREATED_BY,CREATED_ON,
INTEREST_RATE,START_DATE,MATURITY_DATE,NO_OF_DAYS,COMPANY_CODE,
STATUS_CODE,PRODUCT_TYPE,CLIENT_CODE,INTEREST,ENDORSER_CODE,
ENDORSER_NAME,ACCEPTOR_CODE,ACCEPTOR_NAME,DRAWER_CODE,DRAWER_NAME,
CPARTY_CODE,DEALER_CODE,BALANCE_OUT_HCE,INTEREST_HCE,PORTFOLIO_CODE,
ATTRIBUTE_CATEGORY,ATTRIBUTE1,ATTRIBUTE2,ATTRIBUTE3,ATTRIBUTE4,ATTRIBUTE5,
ATTRIBUTE6,ATTRIBUTE7,ATTRIBUTE8,ATTRIBUTE9,ATTRIBUTE10,
ATTRIBUTE11,ATTRIBUTE12,ATTRIBUTE13,ATTRIBUTE14,ATTRIBUTE15,
ORIGINAL_AMOUNT, --Add Interest Override
PRINCIPAL_TAX_CODE ,PRINCIPAL_TAX_RATE,
PRINCIPAL_TAX_AMOUNT,PRINCIPAL_TAX_SETTLED_REF,
TAX_CODE ,TAX_RATE,
TAX_AMOUNT ,TAX_SETTLED_REFERENCE
)
values
------------
-- NEW NI --
------------
(G_Ni_Main_Rec.DEAL_NO,G_Ni_Main_Rec.DEAL_DATE,l_tran_no,'NI',G_Ni_Main_Rec.DEAL_SUBTYPE,
G_Ni_Parcel_Rec(i).FACE_VALUE_AMOUNT,G_Ni_Main_Rec.CURRENCY,G_Ni_Parcel_Rec(i).PARCEL_SPLIT_NO,
G_Ni_Main_Rec.CREATED_BY,G_Ni_Main_Rec.CREATED_ON,G_Ni_Main_Rec.INTEREST_RATE,G_Ni_Main_Rec.START_DATE,
G_Ni_Main_Rec.MATURITY_DATE,G_Ni_Main_Rec.NO_OF_DAYS,G_Ni_Main_Rec.COMPANY_CODE,G_Ni_Main_Rec.STATUS_CODE,
G_Ni_Main_Rec.PRODUCT_TYPE,G_Ni_Main_Rec.CLIENT_CODE,G_Ni_Parcel_Rec(i).INTEREST,G_Ni_Main_Rec.ENDORSER_CODE,
G_Ni_Main_Rec.ENDORSER_NAME,G_Ni_Main_Rec.ACCEPTOR_CODE,G_Ni_Main_Rec.ACCEPTOR_NAME,G_Ni_Main_Rec.DRAWER_CODE,
G_Ni_Main_Rec.DRAWER_NAME,G_Ni_Main_Rec.CPARTY_CODE,G_Ni_Main_Rec.DEALER_CODE,G_FV_AMT_HCE(i),
G_INTEREST_HCE(i),G_Ni_Main_Rec.PORTFOLIO_CODE,
G_Ni_Trans_Flex(i).ATTRIBUTE_CATEGORY,G_Ni_Trans_Flex(i).ATTRIBUTE1,G_Ni_Trans_Flex(i).ATTRIBUTE2,
G_Ni_Trans_Flex(i).ATTRIBUTE3,G_Ni_Trans_Flex(i).ATTRIBUTE4,G_Ni_Trans_Flex(i).ATTRIBUTE5,
G_Ni_Trans_Flex(i).ATTRIBUTE6,G_Ni_Trans_Flex(i).ATTRIBUTE7,G_Ni_Trans_Flex(i).ATTRIBUTE8,G_Ni_Trans_Flex(i).ATTRIBUTE9,
G_Ni_Trans_Flex(i).ATTRIBUTE10,G_Ni_Trans_Flex(i).ATTRIBUTE11,G_Ni_Trans_Flex(i).ATTRIBUTE12,G_Ni_Trans_Flex(i).ATTRIBUTE13,
G_Ni_Trans_Flex(i).ATTRIBUTE14,G_Ni_Trans_Flex(i).ATTRIBUTE15,
G_Ni_Parcel_Rec(i).ORIGINAL_AMOUNT, --Add Interest Override
G_Ni_Main_Rec.TAX_CODE ,G_Ni_Main_Rec.TAX_RATE,
G_PRN_TAX_AMOUNT(i) ,null,
G_Ni_Main_Rec.INCOME_TAX_CODE,G_Ni_Main_Rec.INCOME_TAX_RATE,
G_INT_TAX_AMOUNT(i) ,null
);
select nvl(a.ROUNDING_FACTOR,2),
nvl(b.ROUNDING_FACTOR,2)
from XTR_MASTER_CURRENCIES_v a,
xtr_MASTER_CURRENCIES_v b,
xtr_PARTIES_v p
where a.CURRENCY = G_Ni_Main_Rec.CURRENCY
and p.PARTY_CODE = G_Ni_Main_Rec.COMPANY_CODE
and p.PARTY_TYPE = 'C'
and b.CURRENCY = p.HOME_CURRENCY;
select count(deal_number),
sum(balance_out),
sum(balance_out_hce)
into l_total_parcel,
l_total_face_value,
l_total_face_value_hce
from XTR_ROLLOVER_TRANSACTIONS
where company_code = G_Ni_Main_Rec.COMPANY_CODE
and deal_number = G_Ni_Main_Rec.DEAL_NO
and deal_type = 'NI'
and brokerage_amount is null;
update XTR_ROLLOVER_TRANSACTIONS_V
set BROKERAGE_AMOUNT = round(decode(l_total_parcel,
1,G_Ni_Main_Rec.BROKERAGE_AMOUNT- l_running_bkge_amt,
(G_Ni_Main_Rec.BROKERAGE_AMOUNT/l_total_face_value)*balance_out),
l_rounding),
BROKERAGE_AMOUNT_HCE = round(decode(l_total_parcel,
1, G_Ni_Main_Rec.BROKERAGE_AMOUNT_HCE- l_running_bkge_amt_hce,
(G_Ni_Main_Rec.BROKERAGE_AMOUNT_HCE/l_total_face_value_hce)*balance_out_hce),
l_hce_rounding)
where company_code = G_Ni_Main_Rec.COMPANY_CODE
and deal_number = G_Ni_Main_Rec.DEAL_NO
and deal_type = 'NI'
and transaction_number = l_tran_no
and brokerage_amount is null;
select balance_out,
balance_out_hce
into l_tran_face_value,
l_tran_face_value_hce
from XTR_ROLLOVER_TRANSACTIONS
where company_code = G_Ni_Main_Rec.COMPANY_CODE
and deal_number = G_Ni_Main_Rec.DEAL_NO
and deal_type = 'NI'
and transaction_number = l_tran_no;
select transaction_number
from xtr_rollover_transactions
where company_code = G_Ni_Main_Rec.company_code
and deal_number = G_Ni_Main_Rec.deal_no
and deal_type = 'NI'
and initial_fair_value is null;
update xtr_deals
set tax_settled_reference=p_prn_exp_number
where deal_no=G_Ni_Main_Rec.deal_no;
update xtr_deals
set income_tax_settled_ref=p_int_exp_number
where deal_no=G_Ni_Main_Rec.deal_no;
select short_name
from xtr_parties_v
where party_code=p_party_code;
INSERT INTO XTR_DEALS
(
EXTERNAL_DEAL_ID,
FREQUENCY,
DEAL_TYPE,
BROKERAGE_AMOUNT_HCE,
TAX_AMOUNT_HCE,
MATURITY_BALANCE_HCE_AMOUNT,
RISKPARTY_CODE,
YEAR_BASIS,
INTEREST_HCE_AMOUNT,
START_HCE_AMOUNT,
PORTFOLIO_AMOUNT,
MATURITY_HCE_AMOUNT,
PREMIUM_ACCOUNT_NO,
NI_DEAL_NO,
RENEG_DATE,
DEAL_NO,
STATUS_CODE,
DEALER_CODE,
DEAL_DATE,
COMPANY_CODE,
CPARTY_CODE,
CLIENT_CODE,
PORTFOLIO_CODE,
KNOCK_TYPE,
NI_PROFIT_LOSS,
DEAL_SUBTYPE,
PRODUCT_TYPE,
CURRENCY,
YEAR_CALC_TYPE,
START_DATE,
MATURITY_DATE,
NO_OF_DAYS,
MATURITY_AMOUNT,
MATURITY_BALANCE_AMOUNT,
START_AMOUNT,
CALC_BASIS,
INTEREST_RATE,
INTEREST_AMOUNT,
ORIGINAL_AMOUNT,
ROUNDING_TYPE,
DAY_COUNT_TYPE,
COMMENTS,
INTERNAL_TICKET_NO,
EXTERNAL_COMMENTS,
EXTERNAL_CPARTY_NO,
MATURITY_ACCOUNT_NO,
CPARTY_ACCOUNT_NO,
CPARTY_REF,
PRINCIPAL_SETTLED_BY,
SECURITY_ID,
MARGIN,
PRICING_MODEL,
MARKET_DATA_SET,
DEAL_LINKING_CODE,
ACCEPTOR_CODE,
ACCEPTOR_NAME,
DRAWER_CODE,
DRAWER_NAME,
ENDORSER_CODE,
ENDORSER_NAME,
RISKPARTY_LIMIT_CODE,
BROKERAGE_CODE,
BROKERAGE_RATE,
BROKERAGE_AMOUNT,
BROKERAGE_CURRENCY,
TAX_CODE,
TAX_RATE,
TAX_AMOUNT,
TAX_SETTLED_REFERENCE,
INCOME_TAX_CODE,
INCOME_TAX_RATE,
INCOME_TAX_AMOUNT,
INCOME_TAX_SETTLED_REF,
DUAL_AUTHORISATION_BY,
DUAL_AUTHORISATION_ON,
CREATED_BY,
CREATED_ON,
ATTRIBUTE_CATEGORY,
ATTRIBUTE1,
ATTRIBUTE2,
ATTRIBUTE3,
ATTRIBUTE4,
ATTRIBUTE5,
ATTRIBUTE6,
ATTRIBUTE7,
ATTRIBUTE8,
ATTRIBUTE9,
ATTRIBUTE10,
ATTRIBUTE11,
ATTRIBUTE12,
ATTRIBUTE13,
ATTRIBUTE14,
ATTRIBUTE15,
REQUEST_ID,
PROGRAM_APPLICATION_ID,
PROGRAM_ID,
PROGRAM_UPDATE_DATE
)
VALUES
(
G_Ni_Main_Rec.EXTERNAL_DEAL_ID,
G_Ni_Main_Rec.FREQUENCY,
G_Ni_Main_Rec.DEAL_TYPE,
G_Ni_Main_Rec.BROKERAGE_AMOUNT_HCE,
G_Ni_Main_Rec.TAX_AMOUNT_HCE,
G_Ni_Main_Rec.MATURITY_BALANCE_HCE_AMOUNT,
G_Ni_Main_Rec.RISKPARTY_CODE,
G_Ni_Main_Rec.YEAR_BASIS,
G_Ni_Main_Rec.INTEREST_HCE_AMOUNT,
G_Ni_Main_Rec.START_HCE_AMOUNT,
G_Ni_Main_Rec.PORTFOLIO_AMOUNT,
G_Ni_Main_Rec.MATURITY_HCE_AMOUNT,
G_Ni_Main_Rec.PREMIUM_ACCOUNT_NO,
G_Ni_Main_Rec.NI_DEAL_NO,
G_Ni_Main_Rec.RENEG_DATE,
G_Ni_Main_Rec.DEAL_NO,
G_Ni_Main_Rec.STATUS_CODE,
G_Ni_Main_Rec.DEALER_CODE,
G_Ni_Main_Rec.DEAL_DATE,
G_Ni_Main_Rec.COMPANY_CODE,
G_Ni_Main_Rec.CPARTY_CODE,
G_Ni_Main_Rec.CLIENT_CODE,
G_Ni_Main_Rec.PORTFOLIO_CODE,
G_Ni_Main_Rec.KNOCK_TYPE,
G_Ni_Main_Rec.NI_PROFIT_LOSS,
G_Ni_Main_Rec.DEAL_SUBTYPE,
G_Ni_Main_Rec.PRODUCT_TYPE,
G_Ni_Main_Rec.CURRENCY,
G_Ni_Main_Rec.YEAR_CALC_TYPE,
G_Ni_Main_Rec.START_DATE,
G_Ni_Main_Rec.MATURITY_DATE,
G_Ni_Main_Rec.NO_OF_DAYS,
G_Ni_Main_Rec.MATURITY_AMOUNT,
G_Ni_Main_Rec.MATURITY_BALANCE_AMOUNT,
G_Ni_Main_Rec.START_AMOUNT,
G_Ni_Main_Rec.CALC_BASIS,
G_Ni_Main_Rec.INTEREST_RATE,
G_Ni_Main_Rec.INTEREST_AMOUNT,
G_Ni_Main_Rec.ORIGINAL_AMOUNT,
G_Ni_Main_Rec.ROUNDING_TYPE,
G_Ni_Main_Rec.DAY_COUNT_TYPE,
G_Ni_Main_Rec.COMMENTS,
G_Ni_Main_Rec.INTERNAL_TICKET_NO,
G_Ni_Main_Rec.EXTERNAL_COMMENTS,
G_Ni_Main_Rec.EXTERNAL_CPARTY_NO,
G_Ni_Main_Rec.MATURITY_ACCOUNT_NO,
G_Ni_Main_Rec.CPARTY_ACCOUNT_NO,
G_Ni_Main_Rec.CPARTY_REF,
G_Ni_Main_Rec.PRINCIPAL_SETTLED_BY,
G_Ni_Main_Rec.SECURITY_ID,
G_Ni_Main_Rec.MARGIN,
G_Ni_Main_Rec.PRICING_MODEL,
G_Ni_Main_Rec.MARKET_DATA_SET,
G_Ni_Main_Rec.DEAL_LINKING_CODE,
G_Ni_Main_Rec.ACCEPTOR_CODE,
G_Ni_Main_Rec.ACCEPTOR_NAME,
G_Ni_Main_Rec.DRAWER_CODE,
G_Ni_Main_Rec.DRAWER_NAME,
G_Ni_Main_Rec.ENDORSER_CODE,
G_Ni_Main_Rec.ENDORSER_NAME,
G_Ni_Main_Rec.RISKPARTY_LIMIT_CODE,
G_Ni_Main_Rec.BROKERAGE_CODE,
G_Ni_Main_Rec.BROKERAGE_RATE,
G_NI_Main_Rec.BROKERAGE_AMOUNT,
G_Ni_Main_Rec.BROKERAGE_CURRENCY,
G_Ni_Main_Rec.TAX_CODE,
G_Ni_Main_Rec.TAX_RATE,
G_Ni_Main_Rec.TAX_AMOUNT,
G_Ni_Main_Rec.TAX_SETTLED_REFERENCE,
G_Ni_Main_Rec.INCOME_TAX_CODE,
G_Ni_Main_Rec.INCOME_TAX_RATE,
G_Ni_Main_Rec.INCOME_TAX_AMOUNT,
G_Ni_Main_Rec.INCOME_TAX_SETTLED_REF,
l_dual_user,
l_dual_date,
G_Ni_Main_Rec.CREATED_BY,
G_Ni_Main_Rec.CREATED_ON,
G_Ni_Main_Rec.ATTRIBUTE_CATEGORY,
G_Ni_Main_Rec.ATTRIBUTE1,
G_Ni_Main_Rec.ATTRIBUTE2,
G_Ni_Main_Rec.ATTRIBUTE3,
G_Ni_Main_Rec.ATTRIBUTE4,
G_Ni_Main_Rec.ATTRIBUTE5,
G_Ni_Main_Rec.ATTRIBUTE6,
G_Ni_Main_Rec.ATTRIBUTE7,
G_Ni_Main_Rec.ATTRIBUTE8,
G_Ni_Main_Rec.ATTRIBUTE9,
G_Ni_Main_Rec.ATTRIBUTE10,
G_Ni_Main_Rec.ATTRIBUTE11,
G_Ni_Main_Rec.ATTRIBUTE12,
G_Ni_Main_Rec.ATTRIBUTE13,
G_Ni_Main_Rec.ATTRIBUTE14,
G_Ni_Main_Rec.ATTRIBUTE15,
FND_GLOBAL.conc_request_id,
FND_GLOBAL.prog_appl_id,
FND_GLOBAL.conc_program_id,
g_curr_date
);
update XTR_bill_bond_issues_V
set issue_date = G_Ni_Main_Rec.DEAL_DATE,
deal_number = G_Ni_Main_Rec.DEAL_NO,
status = G_Ni_Parcel_Rec(i).STATUS_CODE,
parcel_split_no = G_Ni_Parcel_Rec(i).PARCEL_SPLIT_NO,
due_date = G_Ni_Main_Rec.MATURITY_DATE,
currency = G_Ni_Main_Rec.CURRENCY,
amount = G_Ni_Parcel_Rec(i).FACE_VALUE_AMOUNT,
bank_code = l_bank_code
where ni_or_bond = 'NI'
and serial_number = G_Ni_Parcel_Rec(i).SERIAL_NUMBER;
insert into xtr_parcel_splits(
DEAL_NO,
PARCEL_SPLIT_NO,
PARCEL_SIZE,
FACE_VALUE_AMOUNT,
CONSIDERATION,
INTEREST,
STATUS_CODE,
DEAL_SUBTYPE,
AVAILABLE_FOR_RESALE,
PARCEL_REMAINING,
SELECT_NUMBER,
SELECT_FV_AMOUNT,
REFERENCE_NUMBER,
RESERVE_PARCEL,
OLD_SELECT_NUMBER,
SERIAL_NUMBER,
SERIAL_NUMBER_IN,
ISSUE_BANK,
ORIGINAL_AMOUNT
)
Values(
G_Ni_Parcel_Rec(i).DEAL_NO,
G_Ni_Parcel_Rec(i).PARCEL_SPLIT_NO,
G_Ni_Parcel_Rec(i).PARCEL_SIZE,
G_Ni_Parcel_Rec(i).FACE_VALUE_AMOUNT,
G_Ni_Parcel_Rec(i).CONSIDERATION,
G_Ni_Parcel_Rec(i).INTEREST,
G_Ni_Parcel_Rec(i).STATUS_CODE,
G_Ni_Parcel_Rec(i).DEAL_SUBTYPE,
G_Ni_Parcel_Rec(i).AVAILABLE_FOR_RESALE,
G_Ni_Parcel_Rec(i).PARCEL_REMAINING,
G_Ni_Parcel_Rec(i).SELECT_NUMBER,
G_Ni_Parcel_Rec(i).SELECT_FV_AMOUNT,
G_Ni_Parcel_Rec(i).REFERENCE_NUMBER,
G_Ni_Parcel_Rec(i).RESERVE_PARCEL,
G_Ni_Parcel_Rec(i).OLD_SELECT_NUMBER,
G_Ni_Parcel_Rec(i).SERIAL_NUMBER,
G_Ni_Parcel_Rec(i).SERIAL_NUMBER_IN,
G_Ni_Parcel_Rec(i).ISSUE_BANK,
G_Ni_Parcel_Rec(i).ORIGINAL_AMOUNT
);
UPDATE xtr_deal_date_amounts
SET dual_authorisation_by = l_dual_user,
dual_authorisation_on = l_dual_date
WHERE deal_number = G_Ni_Main_Rec.DEAL_NO;
UPDATE xtr_confirmation_details
SET confirmation_validated_by = l_dual_user,
confirmation_validated_on = l_dual_date
WHERE deal_no = G_Ni_Main_Rec.DEAL_NO;
UPDATE xtr_deals
SET dual_authorisation_on = l_dual_date
WHERE deal_no = G_Ni_Main_Rec.DEAL_NO;