APPS.XTR_HEDGE_PROCESS_P SQL Statements

The following lines contain the word 'select', 'insert', 'update' or 'delete':

Line: 72

select s.hedge_approach, h.hedge_amount, h.exclusion_item_code
from   xtr_hedge_strategies s, xtr_hedge_attributes h
where  s.strategy_code = h.strategy_code
and    h.hedge_attribute_id = p_hedge_no;

Line: 78

select abs(sum(r.reference_amount)) ref_amt
from   xtr_hedge_relationships r
where  r.hedge_attribute_id = p_hedge_no
and    instrument_item_flag = 'I';

Line: 84

select reclass_hedge_amt
from   xtr_reclass_details
where  reclass_details_id = p_reclass_id;

Line: 89

select sum(reclass_hedge_amt), sum(reclass_gain_loss_amt)
from   xtr_reclass_details
where  hedge_attribute_id = p_hedge_no
and    reclass_date < p_date;

Line: 95

select eff_cum_gain_loss_amt
from   xtr_hedge_retro_tests T1
where  hedge_attribute_id = p_hedge_no
and    result_date = (select max(result_date) from xtr_hedge_retro_tests T2
                      where T1.hedge_attribute_id = T2.hedge_attribute_id
		      and result_code is not null);

Line: 106

select count(primary_code) deal_count,
       abs(sum(nvl(reference_amount,0))) deal_total
from   xtr_hedge_relationships
where  hedge_attribute_id = p_hedge_no
and    instrument_item_flag = 'U';

Line: 113

select primary_code deal_no,
       abs(nvl(reference_amount,0)) alloc_ref_amt
from   xtr_hedge_relationships
where  hedge_attribute_id = p_hedge_no
and    instrument_item_flag = 'U';

Line: 120

select item_cum_gain_loss_amt, inst_cum_gain_loss_amt, eff_cum_gain_loss_amt,
       ineff_cum_gain_loss_amt, excluded_cum_gain_loss_amt
from   xtr_hedge_retro_tests
where  hedge_attribute_id = p_hedge_no
and    result_date = (select max(result_date) from xtr_hedge_retro_tests r
                   where  r. hedge_attribute_id = p_hedge_no
                   and    r.batch_id <= p_batch_id);

Line: 130

select eff_cum_gain_loss_amt, ineff_cum_gain_loss_amt, excluded_prd_gain_loss_amt
from   xtr_deal_retro_tests
where  hedge_attribute_id = p_hedge_no
and    deal_number = p_deal_no
and    result_date = (select max(result_date) from xtr_deal_retro_tests r
                   where  r. hedge_attribute_id = p_hedge_no
                   and    r.deal_number = p_deal_no
                   and    r.batch_id <= p_batch_id);

Line: 140

select rounding_factor
from   xtr_master_currencies_v
where  currency = p_ccy;

Line: 211

  update xtr_reclass_details
  set    reclass_gain_loss_amt  = nvl(l_rec_gl_amt,0),
         retro_batch_id         = p_batch_id
  where  reclass_details_id     = p_reclass_id;

Line: 218

  update xtr_hedge_relationships
  set    cur_pct_allocation = round(pct_allocation * (l_rem_hedge_amt - l_rec_hdg_amt)/l_orig_hedge_amt,2),
         cur_reference_amt  = round(reference_amount * (l_rem_hedge_amt - l_rec_hdg_amt)/l_orig_hedge_amt, l_round)
  where  hedge_attribute_id = p_hedge_no;

Line: 225

  update xtr_hedge_retro_tests
  set    reclass_gain_loss_amt = nvl(l_rec_gl_amt,0)
  where  hedge_attribute_id = p_hedge_no
  and    result_date        = p_date;

Line: 231

     LOG_MSG('event', 'updated xtr_hedge_retro_tests sucessfully!');

Line: 233

     LOG_MSG('event', 'could not update xtr_hedge_retro_tests. The hedge already matured.');

Line: 242

    select xtr_hedge_retro_tests_s.nextval into l_retro_test_id from dual;

Line: 244

   insert into xtr_hedge_retro_tests
	(HEDGE_RETRO_TEST_ID, COMPANY_CODE, HEDGE_ATTRIBUTE_ID, RESULT_CODE,
	BATCH_ID, RESULT_DATE,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
	LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,PROGRAM_ID, PROGRAM_LOGIN_ID,
	PROGRAM_APPLICATION_ID, REQUEST_ID, COMPLETE_FLAG,GAIN_LOSS_CCY,
	AMOUNT_TYPE, ITEM_PRD_GAIN_LOSS_AMT, INST_PRD_GAIN_LOSS_AMT,
	ITEM_CUM_GAIN_LOSS_AMT, INST_CUM_GAIN_LOSS_AMT, PCT_EFFECTIVE, EFF_PRD_GAIN_LOSS_AMT,
	INEFF_PRD_GAIN_LOSS_AMT, EXCLUDED_PRD_GAIN_LOSS_AMT,
        RECLASS_GAIN_LOSS_AMT,
        EFF_CUM_GAIN_LOSS_AMT, INEFF_CUM_GAIN_LOSS_AMT, EXCLUDED_CUM_GAIN_LOSS_AMT
	)
   values (l_retro_test_id, p_company, p_hedge_no, NULL,
           p_batch_id, p_date, fnd_global.user_id, sysdate, fnd_global.user_id,
           sysdate, fnd_global.login_id, fnd_global.conc_program_id, fnd_global.conc_login_id,
           fnd_global.prog_appl_id, fnd_global.conc_request_id,'Y', l_gain_loss_ccy,
           l_amount_type, 0, 0, l_item_prv_cum, l_inst_prv_cum, NULL, 0,0,0,
           nvl(l_rec_gl_amt,0), l_eff_prv_cum, l_ineff_prv_cum, l_excl_prv_cum
	  );

Line: 274

    update xtr_deal_retro_tests
    set    reclass_gain_loss_amt = nvl(l_rec_gl_amt,0) * round(r.alloc_ref_amt/l_deal_total, l_round)
    where  hedge_attribute_id = p_hedge_no
    and    deal_number = r.deal_no
    and    result_date = p_date;

Line: 282

      LOG_MSG('event', 'updated xtr_deal_retro_tests sucessfully!');

Line: 286

      LOG_MSG('event', 'could not update xtr_deal_retro_tests. The hedge already matured.');

Line: 294

      insert into xtr_deal_retro_tests
	(DEAL_RETRO_TEST_ID,
	 HEDGE_RETRO_TEST_ID,
         BATCH_ID,
         HEDGE_ATTRIBUTE_ID,
         CREATED_BY,
         CREATION_DATE,
         LAST_UPDATED_BY,
         LAST_UPDATE_DATE,
         LAST_UPDATE_LOGIN,
         PROGRAM_ID,
         PROGRAM_LOGIN_ID,
	 PROGRAM_APPLICATION_ID,
         REQUEST_ID,
         DEAL_NUMBER,
         AMOUNT_TYPE,
         EFF_PRD_GAIN_LOSS_AMT,
         INEFF_PRD_GAIN_LOSS_AMT,
         EXCLUDED_PRD_GAIN_LOSS_AMT,
         RECLASS_GAIN_LOSS_AMT,
         EFF_CUM_GAIN_LOSS_AMT,
         INEFF_CUM_GAIN_LOSS_AMT,
         EXCLUDED_CUM_GAIN_LOSS_AMT,
         RESULT_DATE
        )
      values (
         xtr_deal_retro_tests_s.nextval,
         l_retro_test_id,
         p_batch_id,
         p_hedge_no,
         fnd_global.user_id,
         sysdate,
         fnd_global.user_id,
         sysdate,
         fnd_global.login_id,
         fnd_global.conc_program_id,
         fnd_global.conc_login_id,
         fnd_global.prog_appl_id,
         fnd_global.conc_request_id,
         r.deal_no,
         l_amount_type,
         0,
         0,
         0,
	 nvl(l_rec_gl_amt,0) * round(r.alloc_ref_amt/l_deal_total, l_round),
         l_hd_eff_prv_cum,
         l_hd_ineff_prv_cum,
         l_hd_excl_prv_cum,
         p_date
	 );

Line: 380

select deal_number, amount_type, result_date,
nvl(sum(eff_prd_gain_loss_amt),0) eff_prd_gain_loss_amt,
nvl(sum(reclass_gain_loss_amt),0) reclass_gain_loss_amt
from   xtr_deal_retro_tests d
where  d.batch_id 	     = p_batch_id
and    d.hedge_attribute_id in (select hedge_attribute_id
                                from xtr_hedge_attributes ha, xtr_hedge_strategies hs
                                where ha.strategy_code = hs.strategy_code
                                and   hs.hedge_type = 'CASHFLOW')
group by deal_number, amount_type, result_date
order by deal_number, amount_type, result_date;

Line: 393

select nvl(sum(reclass_gain_loss_amt),0)
from   xtr_deal_retro_tests
where  deal_number = p_deal_no
and    result_date <= l_temp_date;

Line: 400

select decode(p_amount_type, 'UNREAL', unrealised_pl, curr_gain_loss_amount) reval_amt
from   xtr_revaluation_details
where  revaluation_details_id = p_reval_id;

Line: 405

select 'Y' from xtr_revaluation_details
where  deal_no = p_deal_no
and    realized_flag = 'Y'
and    period_to < p_date;

Line: 411

select 'Y' from xtr_revaluation_details
where  deal_no = p_deal_no
and    realized_flag = 'Y'
and    period_to = p_date;

Line: 417

select 'Y' from xtr_deal_retro_tests
where  deal_number = p_deal_no
and    nvl(reclass_gain_loss_amt,0) <> 0
and    result_date < p_date;

Line: 424

select 'Y' from xtr_deal_retro_tests
where  deal_no = p_deal_no
and    nvl(reclass_gain_loss_amt,0) <> 0
and    result_date = p_date;

Line: 431

select sum(DECODE(ACTION, 'LOSS',-AMOUNT, AMOUNT)) from xtr_gain_loss_dnm
where journal_date <= l_temp_date
and deal_number = p_deal_no
and reval_eff_flag = p_flag
and amount_type = p_amount_type
and date_type = p_date_type;

Line: 439

 select revaluation_details_id from xtr_revaluation_details
 where  deal_no   = p_deal_no
 and    period_from >= nvl(l_mbst_date, period_from)
-- and    period_to = l_temp_date  -- Unit Test Change
 and    period_to <= l_temp_date
 and    realized_flag = 'N'
 and    batch_id  = p_batch_id
 and    deal_type = 'FX'
 and    deal_subtype = 'FORWARD';

Line: 451

select max(period_from)
from   xtr_revaluation_details
where  batch_id = p_batch_id
and    deal_no  = p_deal_no
and    deal_type = 'FX'
and    deal_subtype = 'FORWARD'
and    period_to <= p_end_date
and    period_from <= p_end_date;

Line: 473

  select XTR_GAIN_LOSS_DNM_S.nextval into l_dnm_id from dual;

Line: 483

      XTR_GAIN_LOSS_DNM_PKG.INSERT_ROW(
	l_row_id,
   	l_dnm_id ,
	p_batch_id,
	p_company,
	p_deal_no,
	1,
	p_date_type,
	abs(p_amount),
	p_amount_type,
	l_action,
	l_gl_ccy,
        p_date,
        'T',
	sysdate,
	fnd_global.user_id,
	sysdate,
	fnd_global.user_id,
	fnd_global.login_id
	);

Line: 692

 Update XTR_BATCH_EVENTS
 set    AUTHORIZED 	   = 'Y',
	AUTHORIZED_BY 	   = FND_GLOBAL.USER_ID,
	AUTHORIZED_ON 	   = SYSDATE,
        LAST_UPDATED_BY    = FND_GLOBAL.USER_ID,
	LAST_UPDATE_DATE   = SYSDATE,
	LAST_UPDATE_LOGIN  = FND_GLOBAL.LOGIN_ID
 where  BATCH_ID	   = P_BATCH_ID
 and    EVENT_CODE  	   = 'RETROET'
 and    nvl(AUTHORIZED,'N')  <> 'Y';

Line: 726

   select nvl(reference_amount,0) alloc_ref_amt
   from   xtr_hedge_relationships
   where  hedge_attribute_id = p_hedge_no
   and    primary_code = p_deal_no
   and    instrument_item_flag = 'U';

Line: 734

   select sum(nvl(reference_amount,0)) deal_total
   from   xtr_hedge_relationships
   where  hedge_attribute_id = p_hedge_no
   and    instrument_item_flag = 'U';

Line: 766

   select distinct reval_ccy
   from   xtr_revaluation_details
   where  deal_no = p_deal_no;

Line: 771

   select set_of_books_currency
   from   xtr_parties_v
   where  party_code = p_company
   and party_type = 'C';

Line: 806

   select parameter_value_code
   from   XTR_COMPANY_PARAMETERS
   where  company_code   = p_company
   and    parameter_code = 'ACCNT_BTEST';

Line: 914

select
       deal_no 			hedge_no
       ,revaluation_details_id  rec_id
       ,'10'     		rec_source
       ,period_from 		period_from
       ,period_to   		period_to
       ,unrealised_pl		unrealised_pl
       ,realised_pl		realised_pl
       ,curr_gain_loss_amount   curr_gain_loss_amount
       ,realized_flag		realized_flag
from   xtr_revaluation_details r
where  batch_id is not null
and    company_code = p_company
and    not exists (select 'Y' from xtr_batch_events e
                   where e.batch_id =  r.batch_id and
                   e.event_code = 'RETROET')
and    batch_id in (select batch_id from xtr_batches b where
                    b.period_end <= p_cb_end_date)
and    exists (select 'Y' from XTR_COMPANY_PARAMETERS
               where company_code   = p_company
               and    parameter_code = 'ACCNT_BTEST'
               and    parameter_value_code = 'Y')
and    deal_type = 'HEDGE'
UNION
select DISTINCT
       ha.deal_no		hedge_no
       ,-9999			rec_id
       ,'20'     		rec_source
       ,period_from 		period_from
       ,period_to   		period_to
       ,0			unrealised_pl
       ,0			realised_pl
       ,0			curr_gain_loss_amount
       ,NULL			realized_flag
from   xtr_revaluation_details r, xtr_eligible_hedges_v ha, xtr_hedge_relationships hr
where  batch_id is not null
and    r.company_code = p_company
and    r.deal_no = hr.primary_code
and    r.deal_type = 'FX'
and    r.deal_subtype = 'FORWARD'
and    r.realized_flag = 'N'
and    hr.instrument_item_flag = 'U'
and    ha.deal_no = hr.hedge_attribute_id
and    r.period_to > ha.maturity_date
and    not exists (select 'Y' from xtr_batch_events e
                where e.batch_id =  r.batch_id and
                e.event_code = 'RETROET')
and    batch_id in (select batch_id from xtr_batches b where
                 b.period_end <= p_cb_end_date)
and    exists (select 'Y' from XTR_COMPANY_PARAMETERS
               where company_code   = p_company
               and    parameter_code = 'ACCNT_BTEST'
               and    parameter_value_code = 'Y')
UNION
select
     r.hedge_attribute_id	hedge_no
    ,reclass_details_id 	rec_id
    ,'30'         		rec_source
    ,reclass_date 		period_from
    ,reclass_date 		period_to
    ,NULL         		unrealised_pl
    ,NULL         		realised_pl
    ,NULL         		curr_gain_loss_amount
    ,NULL			realized_flag
from   xtr_reclass_details r, xtr_hedge_attributes h, xtr_hedge_strategies s
where  r.hedge_attribute_id = h.hedge_attribute_id
and    h.strategy_code =  s.strategy_code
and    h.company_code = p_company
and    r.reclass_date <= p_cb_end_date
and    r.reclass_date >  nvl(p_pb_end_date, r.reclass_date - 5)
and    h.hedge_status not in ('DESIGNATE', 'CANCELLED')
and    exists (select 'Y' from XTR_COMPANY_PARAMETERS
               where company_code   = p_company
               and    parameter_code = 'ACCNT_BTEST'
               and    parameter_value_code = 'Y')
and    retro_batch_id is NULL
and    NVL(h.retro_method,'@@') <> 'NOTEST'
and    h.exclusion_item_code is not NULL
and    s.hedge_type = 'CASHFLOW'
order  by hedge_no, period_to, rec_source asc;

Line: 1001

select item_cum_gain_loss_amt, inst_cum_gain_loss_amt, eff_cum_gain_loss_amt,
       ineff_cum_gain_loss_amt, excluded_cum_gain_loss_amt
from   xtr_hedge_retro_tests
where  hedge_attribute_id = p_hedge_no
and    result_date = (select max(result_date) from xtr_hedge_retro_tests r
                   where  r. hedge_attribute_id = p_hedge_no
                   and    r.batch_id <= p_batch_id);

Line: 1011

select result_code, pct_effective
from   xtr_hedge_retro_tests
where  hedge_attribute_id = p_hedge_no
and    batch_id = p_batch_id;

Line: 1022

select distinct deal_number
from   xtr_deal_retro_tests
where  batch_id = p_batch_id;

Line: 1027

select deal_number, hedge_attribute_id, hedge_retro_test_id,deal_retro_test_id,
       ineff_cum_gain_loss_amt
from   xtr_deal_retro_tests
where  batch_id = p_batch_id;

Line: 1037

select eff_cum_gain_loss_amt, ineff_cum_gain_loss_amt
from   xtr_deal_retro_tests
where  hedge_attribute_id = p_hedge_no
and    deal_number = p_deal_no
and    result_date = (select max(result_date) from xtr_deal_retro_tests r
                   where  r. hedge_attribute_id = p_hedge_no
                   and    r.deal_number = p_deal_no
                   and    r.batch_id <= p_batch_id);

Line: 1051

select sum(eff_prd_gain_loss_amt)
from   xtr_deal_retro_tests
where  deal_number = p_deal_no
and    hedge_retro_test_id = p_hedge_retro_test_id
group  by deal_number, hedge_retro_test_id;

Line: 1062

select primary_code   deal_no,
       cur_pct_allocation deal_pct,
       abs(nvl(reference_amount,0)) alloc_ref_amt
from   xtr_hedge_relationships
where  hedge_attribute_id = p_hedge_no
and    instrument_item_flag = 'U';

Line: 1073

select unrealised_pl, realised_pl, curr_gain_loss_amount,
       (nvl(unrealised_pl,0)-nvl(curr_gain_loss_amount,0)) excluded_amt,
       revaluation_details_id
from   xtr_revaluation_details
where  deal_no       = l_deal_no
--and    period_from   = l_from_date
--and    period_to     = l_to_date
and    period_from   >= l_from_date
and    period_to     <= l_to_date
and    realized_flag = 'N';

Line: 1089

select DECODE(l_excl_item, 'NONE', unrealised_pl, 'TIME',curr_gain_loss_amount) deal_pl
from   xtr_revaluation_details
where  deal_no       = p_deal_no
and    period_from   = l_from_date
and    period_to     = l_to_date
and    batch_id      = p_batch_id;

Line: 1101

select period_end from xtr_batches
where  batch_id = p_batch_id;

Line: 1108

select max(period_end) from xtr_batches b, xtr_batch_events e
where  b.batch_id = e.batch_id
and    b.batch_id < p_batch_id
and    e.event_code = 'RETROET'
and    b.company_code  = p_company;

Line: 1118

select retro_method, retro_tolerance_min,retro_tolerance_max,exclusion_item_code
from   xtr_hedge_attributes
where  hedge_attribute_id = p_hedge_no;

Line: 1123

select pct_effective
from   xtr_hedge_retro_tests
where  batch_id = p_batch_id
and    hedge_attribute_id = p_hedge_no;

Line: 1134

select count(primary_code) deal_count,
       abs(sum(nvl(reference_amount,0))) deal_total
from   xtr_hedge_relationships
where  hedge_attribute_id = p_hedge_no
and    instrument_item_flag = 'U';

Line: 1141

select rounding_factor
from   xtr_master_currencies_v
where  currency = p_ccy;

Line: 1347

       LOG_MSG('event', 'inserting MANUAL test - part of retro calculations');

Line: 1352

        select xtr_hedge_retro_tests_s.nextval into l_retro_test_id from dual;

Line: 1354

        insert into xtr_hedge_retro_tests
	(HEDGE_RETRO_TEST_ID, COMPANY_CODE, HEDGE_ATTRIBUTE_ID, RESULT_CODE,
	BATCH_ID, RESULT_DATE,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
	LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,PROGRAM_ID, PROGRAM_LOGIN_ID,
	PROGRAM_APPLICATION_ID, REQUEST_ID, COMPLETE_FLAG,GAIN_LOSS_CCY,
	AMOUNT_TYPE, ITEM_PRD_GAIN_LOSS_AMT, INST_PRD_GAIN_LOSS_AMT,
	ITEM_CUM_GAIN_LOSS_AMT, INST_CUM_GAIN_LOSS_AMT, PCT_EFFECTIVE, EFF_PRD_GAIN_LOSS_AMT,
	INEFF_PRD_GAIN_LOSS_AMT, EXCLUDED_PRD_GAIN_LOSS_AMT, RECLASS_GAIN_LOSS_AMT,
	EFF_CUM_GAIN_LOSS_AMT, INEFF_CUM_GAIN_LOSS_AMT, EXCLUDED_CUM_GAIN_LOSS_AMT)
   values (l_retro_test_id, p_company, hdg_rec.hedge_no, NULL,
           p_batch_id, hdg_rec.period_to, fnd_global.user_id, sysdate, fnd_global.user_id,
           sysdate, fnd_global.login_id, fnd_global.conc_program_id, fnd_global.conc_login_id,
           fnd_global.prog_appl_id, fnd_global.conc_request_id,'N',l_gain_loss_ccy,
           l_amount_type, l_item_prd, l_inst_prd, l_item_cum, l_inst_cum, NULL, NULL,
           NULL, l_excl_prd, NULL, NULL, NULL, l_excl_cum);

Line: 1393

   select xtr_hedge_retro_tests_s.nextval into l_retro_test_id from dual;

Line: 1395

      LOG_MSG('event', 'inserting NON-MANUAL test - part of retro calculations');

Line: 1397

      insert into xtr_hedge_retro_tests
	(HEDGE_RETRO_TEST_ID, COMPANY_CODE, HEDGE_ATTRIBUTE_ID, RESULT_CODE,
	BATCH_ID, RESULT_DATE,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
	LAST_UPDATE_DATE, LAST_UPDATE_LOGIN,PROGRAM_ID, PROGRAM_LOGIN_ID,
	PROGRAM_APPLICATION_ID, REQUEST_ID, COMPLETE_FLAG, GAIN_LOSS_CCY,
	AMOUNT_TYPE, ITEM_PRD_GAIN_LOSS_AMT, INST_PRD_GAIN_LOSS_AMT,
	ITEM_CUM_GAIN_LOSS_AMT, INST_CUM_GAIN_LOSS_AMT, PCT_EFFECTIVE, EFF_PRD_GAIN_LOSS_AMT,
	INEFF_PRD_GAIN_LOSS_AMT, EXCLUDED_PRD_GAIN_LOSS_AMT, RECLASS_GAIN_LOSS_AMT,
	EFF_CUM_GAIN_LOSS_AMT, INEFF_CUM_GAIN_LOSS_AMT, EXCLUDED_CUM_GAIN_LOSS_AMT)
      values (l_retro_test_id, p_company, hdg_rec.hedge_no, l_result,
           p_batch_id, hdg_rec.period_to, fnd_global.user_id, sysdate, fnd_global.user_id,
           sysdate, fnd_global.login_id, fnd_global.conc_program_id, fnd_global.conc_login_id,
           fnd_global.prog_appl_id, fnd_global.conc_request_id,'Y',l_gain_loss_ccy,
           l_amount_type, l_item_prd, l_inst_prd, l_item_cum, l_inst_cum, l_pct_eff, l_eff_prd,
           l_ineff_prd, l_excl_prd, NULL, l_eff_cum, l_ineff_cum, l_excl_cum);

Line: 1419

      LOG_MSG('status', 'INSERT sucessful for xtr_hedge_retro_tests');

Line: 1450

      insert into xtr_deal_retro_tests
	(DEAL_RETRO_TEST_ID, HEDGE_RETRO_TEST_ID, BATCH_ID, HEDGE_ATTRIBUTE_ID, CREATED_BY, CREATION_DATE,
        LAST_UPDATED_BY, LAST_UPDATE_DATE, LAST_UPDATE_LOGIN, PROGRAM_ID, PROGRAM_LOGIN_ID,
	PROGRAM_APPLICATION_ID, REQUEST_ID, DEAL_NUMBER, AMOUNT_TYPE,
        EFF_PRD_GAIN_LOSS_AMT, INEFF_PRD_GAIN_LOSS_AMT, EXCLUDED_PRD_GAIN_LOSS_AMT,
        RECLASS_GAIN_LOSS_AMT, EFF_CUM_GAIN_LOSS_AMT,  INEFF_CUM_GAIN_LOSS_AMT,
        EXCLUDED_CUM_GAIN_LOSS_AMT, RESULT_DATE)

      values (xtr_deal_retro_tests_s.nextval, l_retro_test_id, p_batch_id, hdg_rec.hedge_no,
              fnd_global.user_id, sysdate, fnd_global.user_id, sysdate, fnd_global.login_id,
              fnd_global.conc_program_id, fnd_global.conc_login_id, fnd_global.prog_appl_id,
              fnd_global.conc_request_id, deal_rec.deal_no, l_amount_type,
              round(l_eff_prd * (deal_rec.alloc_ref_amt/l_deal_total), l_round),
	      l_hd_ineff_prd,
              round(l_excl_prd * (deal_rec.alloc_ref_amt/l_deal_total), l_round), NULL,
              round(l_eff_cum * (deal_rec.alloc_ref_amt/l_deal_total), l_round),
	      nvl(l_hd_ineff_prv_cum,0) + nvl(l_hd_ineff_prd,0),
              round(l_excl_cum * (deal_rec.alloc_ref_amt/l_deal_total), l_round), hdg_rec.period_to
	      );

Line: 1471

            LOG_MSG('status', 'INSERT successful for xtr_deal_retro_tests');

Line: 1473

            LOG_MSG('status', 'INSERT failed for xtr_deal_retro_tests');

Line: 1479

    else -- did not insert record into  xtr_hedge_retro_tests

       LOG_MSG('status', 'INSERT failed for xtr_hedge_retro_tests');

Line: 1539

   This procedure inserts a new RETROET event
   into XTR_BATCH_EVENTS table
***************************************************************/
PROCEDURE ins_retro_event(p_batch_id  IN NUMBER, p_event in VARCHAR2) is

Cursor CHK_BATCH_RUN is
Select 'Y'
From   XTR_BATCH_EVENTS
Where  batch_id = p_batch_id
and    event_code = p_event;

Line: 1563

    select XTR_BATCH_EVENTS_S.NEXTVAL into l_event_id from DUAL;

Line: 1565

    Insert into XTR_BATCH_EVENTS(batch_event_id, batch_id, event_code, authorized,
                                   authorized_by, authorized_on, created_by, creation_date,
                                   last_updated_by, last_update_date, last_update_login)
    values(l_event_id, p_batch_id, p_event, 'N', null, null, fnd_global.user_id,
             l_sysdate, fnd_global.user_id, l_sysdate, fnd_global.login_id);

Line: 1607

TYPE X_LAST_UPDATED_BY     is table of XTR_HEDGE_ITEMS_TEMP.LAST_UPDATED_BY%type 	Index By BINARY_INTEGER;

Line: 1608

TYPE X_LAST_UPDATE_DATE    is table of XTR_HEDGE_ITEMS_TEMP.LAST_UPDATE_DATE%type 	Index By BINARY_INTEGER;

Line: 1609

TYPE X_LAST_UPDATE_LOGIN   is table of XTR_HEDGE_ITEMS_TEMP.LAST_UPDATE_LOGIN %type 	Index By BINARY_INTEGER;

Line: 1628

LAST_UPDATED_BY            X_LAST_UPDATED_BY;

Line: 1629

LAST_UPDATE_DATE           X_LAST_UPDATE_DATE;

Line: 1630

LAST_UPDATE_LOGIN          X_LAST_UPDATE_LOGIN;

Line: 1641

SELECT PRIMARY_CODE TRX_INV_ID, SECONDARY_CODE PAY_SCHEDULE_ID, HEDGE_ATTRIBUTE_ID,
       PCT_ALLOCATION, REFERENCE_AMOUNT HEDGE_AMOUNT
FROM   XTR_HEDGE_RELATIONSHIPS
WHERE  HEDGE_ATTRIBUTE_ID = HEDGE_NO
AND INSTRUMENT_ITEM_FLAG = 'I';

Line: 1648

select trx_number, trx_date, batch_source_id, cust_trx_type_id,
       org_id, invoice_currency_code
from ra_customer_trx_all
where customer_trx_id = p_trx_id;

Line: 1659

select due_date,customer_id
from ar_payment_schedules_all
where customer_trx_id = p_trx_id
and payment_schedule_id = p_paysch_id;

Line: 1665

select name
from ra_cust_trx_types_all
where cust_trx_type_id = l_ar_trxtype_id
and  org_id =  l_ar_org_id;

Line: 1671

select name
from ra_batch_sources_all
where batch_source_id = l_batch_source_id
and  org_id =  l_ar_org_id;

Line: 1680

select customer_name
from   ra_customers
where  customer_id = p_customer_id;

Line: 1687

select substrb(PARTY.PARTY_NAME,1,50)
from   hz_parties party, hz_cust_accounts cust_acct
where  cust_acct.party_id = party.party_id
and    cust_acct.cust_account_id = p_customer_id;

Line: 1693

select name
from hr_operating_units
where organization_id = l_ar_org_id;

Line: 1707

select invoice_num, invoice_date, source, invoice_type_lookup_code,
       vendor_id, org_id, payment_currency_code
from ap_invoices_all
where invoice_id = p_invoice_id;

Line: 1713

select due_date
from ap_payment_schedules_all
where invoice_id = p_invoice_id
and payment_num = p_payment_num;

Line: 1719

select displayed_field
from ap_lookup_codes k
where lookup_type = 'INVOICE TYPE'
and  lookup_code =  l_ap_invtype;

Line: 1725

select displayed_field
from   ap_lookup_codes k
where  lookup_type = 'SOURCE'
and    lookup_code =  l_ap_source;

Line: 1731

select vendor_name
from   po_vendors
where  vendor_id = p_vendor_id;

Line: 1736

select name
from   hr_operating_units
where  organization_id = l_ap_org_id;

Line: 1745

select hedge_status
from   xtr_hedge_attributes
where  hedge_attribute_id =  hedge_no;

Line: 1752

select count(*) from xtr_hedge_relationships
where  hedge_attribute_id = p_hedge_no
and    instrument_item_flag = 'I';

Line: 1771

      l_query := 'SELECT INVOICE_ID TRX_INV_ID, PAYMENT_NUM PAY_SCHEDULE_ID, SOURCE_NAME, TRX_TYPE_NAME, ORG_NAME,
                  VENDOR_NAME VEND_CUST_NAME, TRX_DATE, TRX_NUMBER, CURRENCY_CODE, AMOUNT, DUE_DATE FROM
                  XTR_AP_ORIG_TRX_V '||get_where_clause(p_hedge_no);

Line: 1775

      l_query := 'SELECT CUSTOMER_TRX_ID TRX_INV_ID, PAYMENT_SCHEDULE_ID PAY_SCHEDULE_ID, SOURCE_NAME, TRX_TYPE_NAME,
                  ORG_NAME, CUSTOMER_NAME VEND_CUST_NAME, TRX_DATE, TRX_NUMBER, CURRENCY_CODE, AMOUNT, DUE_DATE FROM
                  XTR_AR_ORIG_TRX_V '||get_where_clause(p_hedge_no);

Line: 1793

      LAST_UPDATED_BY(idx)   := FND_GLOBAL.USER_ID;

Line: 1794

      LAST_UPDATE_DATE(idx)  := SYSDATE;

Line: 1795

      LAST_UPDATE_LOGIN(idx) := FND_GLOBAL.LOGIN_ID;

Line: 1801

   l_query := 'SELECT PRIMARY_CODE TRX_INV_ID, SECONDARY_CODE PAY_SCHEDULE_ID, HEDGE_ATTRIBUTE_ID, AMOUNT,
               PCT_ALLOCATION, REFERENCE_AMOUNT FROM XTR_HEDGE_RELATIONSHIPS WHERE
               INSTRUMENT_ITEM_FLAG = :iu_flag AND HEDGE_ATTRIBUTE_ID = :HEDGE_NO';

Line: 1843

            select decode(l_ap_source,'Manual Invoice Entry',
                          fnd_message.get_string('XTR','XTR_AP_SOURCE_MANUAL'),
                          fnd_message.get_string('XTR','XTR_AP_SOURCE_OTHER'))
            INTO SOURCE_NAME(cnt) from dual;

Line: 1919

      LAST_UPDATED_BY(cnt)   := FND_GLOBAL.USER_ID;

Line: 1920

      LAST_UPDATE_DATE(cnt)  := SYSDATE;

Line: 1921

      LAST_UPDATE_LOGIN(cnt) := FND_GLOBAL.LOGIN_ID;

Line: 1929

      insert into XTR_HEDGE_ITEMS_TEMP(HEDGE_ATTRIBUTE_ID,
         TRX_INV_ID,PAY_SCHEDULE_ID,SOURCE_NAME,
         TRX_TYPE_NAME, ORG_NAME,VEND_CUST_NAME,TRX_DATE,
         TRX_NUMBER,CURRENCY_CODE, AMOUNT,PCT_ALLOCATION, REFERENCE_AMOUNT,
         DUE_DATE,REQUEST_ID,CREATED_BY, CREATION_DATE, LAST_UPDATED_BY,
         LAST_UPDATE_DATE, LAST_UPDATE_LOGIN
         )
      values (HEDGE_ATTRIBUTE_ID(j),
             TRX_INV_ID(j),PAY_SCHEDULE_ID(j),SOURCE_NAME(j),
             TRX_TYPE_NAME(j), ORG_NAME(j),VEND_CUST_NAME(j),TRX_DATE(j),
             TRX_NUMBER(j),CURRENCY_CODE(j), AMOUNT(j),PCT_ALLOCATION(j),REFERENCE_AMOUNT(j),
             DUE_DATE(j),REQUEST_ID(j),CREATED_BY(j), CREATION_DATE(j), LAST_UPDATED_BY(j),
             LAST_UPDATE_DATE(j), LAST_UPDATE_LOGIN(j)
             );

Line: 1969

TYPE X_LAST_UPDATED_BY	     is table of xtr_hedge_relationships.LAST_UPDATED_BY%type 	    Index By BINARY_INTEGER;

Line: 1970

TYPE X_LAST_UPDATE_DATE	     is table of xtr_hedge_relationships.LAST_UPDATE_DATE%type 	    Index By BINARY_INTEGER;

Line: 1971

TYPE X_LAST_UPDATE_LOGIN     is table of xtr_hedge_relationships.LAST_UPDATE_LOGIN%type     Index By BINARY_INTEGER;

Line: 1984

LAST_UPDATED_BY		X_LAST_UPDATED_BY;

Line: 1985

LAST_UPDATE_DATE	X_LAST_UPDATE_DATE;

Line: 1986

LAST_UPDATE_LOGIN	X_LAST_UPDATE_LOGIN;

Line: 1989

   select nvl(sum(pct_allocation),0) from
   xtr_hedge_relationships hr, xtr_hedge_attributes ha
   where hr.hedge_attribute_id = ha.hedge_attribute_id
   and   ((ha.hedge_status in ('DESIGNATE','CURRENT','FULFILLED'))
          OR (hedge_status in ('FAILED','DEDESIGNATED') and ha.start_date <= ha.discontinue_date)
          )
   and primary_code   = p_prim_code
   and secondary_code = p_sec_code;

Line: 2001

select st.source_type_id, instrument_item_flag
from   xtr_hedge_criteria hc,
       xtr_source_types st
where  hedge_attribute_id =  hedge_no
and    hc.from_value 	  =  st.source_code
and    criteria_type 	  =  'BASIC'
and    criteria_code 	  =  'ITEM_SOURCE';

Line: 2014

l_select   VARCHAR2(350);

Line: 2023

   l_query := ' SELECT INVOICE_ID, PAYMENT_NUM, AMOUNT FROM XTR_AP_ORIG_TRX_V '||get_where_clause(P_HEDGE_NO);

Line: 2025

   l_query := ' SELECT CUSTOMER_TRX_ID, PAYMENT_SCHEDULE_ID, AMOUNT FROM XTR_AR_ORIG_TRX_V '||get_where_clause(P_HEDGE_NO);

Line: 2045

      select xtr_hedge_relationships_s.nextval into HEDGE_RELATIONSHIP_ID(k) from dual;

Line: 2052

      LAST_UPDATED_BY(k)       := fnd_global.user_id;

Line: 2053

      LAST_UPDATE_DATE(k)      := sysdate;

Line: 2054

      LAST_UPDATE_LOGIN(k)     := fnd_global.login_id;

Line: 2058

      insert into xtr_hedge_relationships
             (HEDGE_RELATIONSHIP_ID,HEDGE_ATTRIBUTE_ID,SOURCE_TYPE_ID,PRIMARY_CODE,
              SECONDARY_CODE,INSTRUMENT_ITEM_FLAG,PCT_ALLOCATION,REFERENCE_AMOUNT,AMOUNT,
              CREATED_BY,CREATION_DATE,LAST_UPDATED_BY,LAST_UPDATE_DATE,LAST_UPDATE_LOGIN)
      values (HEDGE_RELATIONSHIP_ID(j),HEDGE_ATTRIBUTE_ID(j),SOURCE_TYPE_ID(j),PRIMARY_CODE(j),
              SECONDARY_CODE(j),INSTRUMENT_ITEM_FLAG(j),PCT_ALLOCATION(j),REFERENCE_AMOUNT(j),
              AMOUNT(j),CREATED_BY(j),CREATION_DATE(j),LAST_UPDATED_BY(j),LAST_UPDATE_DATE(j),
              LAST_UPDATE_LOGIN(j));

Line: 2119

       select '(BATCH_SOURCE_ID = '|| batch_source_id || ' AND ORG_ID = '||org_id||') OR '  source
from   ra_batch_sources_all bs,
       hr_operating_units ho,
       xtr_parties_v xp
where  bs.org_id = ho.organization_id
       and  ho.set_of_books_id = (SELECT glle.ledger_id FROM gl_ledger_le_v glle
             WHERE  glle.legal_entity_id = xp.legal_entity_id
          AND glle.ledger_category_code = 'PRIMARY')   -- bug 4654775
       and xp.party_code = nvl(p_company,xp.party_code) --BUG 3002000 #7
       and bs.name = p_source;

Line: 2131

       select '(CUST_TRX_TYPE_ID = '|| cust_trx_type_id || ' AND ORG_ID = '||org_id||') OR '  trxtype
       from   ra_cust_trx_types_all tt,
              hr_operating_units ho,
              xtr_parties_v xp
       where  tt.org_id = ho.organization_id
          and     ho.set_of_books_id = (SELECT glle.ledger_id FROM gl_ledger_le_v glle
             WHERE  glle.legal_entity_id = xp.legal_entity_id
          AND glle.ledger_category_code = 'PRIMARY')   -- bug 4654775
              and xp.party_code = nvl(p_company,xp.party_code) -- BUG 3002000 #7
              and tt.name = p_trxtype
       union all
       select '(CUST_TRX_TYPE_ID = -99999) OR '  trxtype
       from   DUAL
       where  substr(fnd_message.get_string('XTR','XTR_AR_TRX_TYPE_CASH'),1,20) = p_trxtype;

Line: 2149

SELECT CUSTOMER_ID ||','  customer
FROM   RA_CUSTOMERS
WHERE  CUSTOMER_NAME = p_customer;

Line: 2156

SELECT cust_acct.cust_account_id||','  customer
FROM   hz_parties party, hz_cust_accounts cust_acct
WHERE  substrb(PARTY.PARTY_NAME,1,50) = p_customer
and    cust_acct.party_id = party.party_id;

Line: 2354

select company_code, hedge_currency
from xtr_hedge_attributes
where hedge_attribute_id = hedge_no;

Line: 2362

select from_value
from xtr_hedge_criteria
where hedge_attribute_id = hedge_no
and criteria_type = 'BASIC'
and criteria_code = 'ITEM_SOURCE';

Line: 2372

select fnd_date.canonical_to_date(from_value), fnd_date.canonical_to_date(to_value)
from xtr_hedge_criteria
where hedge_attribute_id = hedge_no
and criteria_type = 'BASIC'
and criteria_code = 'TRX_DATES';

Line: 2385

select criteria_code,operator,from_value
from xtr_hedge_criteria
where criteria_type = 'ADVANCED'
and hedge_attribute_id = hedge_no;

Line: 2438

select st.source_code
from   xtr_hedge_criteria hc,
       xtr_source_types st
where  hedge_attribute_id =  hedge_no
and    hc.from_value 	  =  st.source_code
and    criteria_type 	  =  'BASIC'
and    criteria_code 	  =  'ITEM_SOURCE';

Line: 2485

    SELECT PARAM_VALUE
    INTO   p_crit_set.currency
    FROM   xtr_pro_param
    WHERE  param_name = 'SYSTEM_FUNCTIONAL_CCY';

Line: 2570

     v_select Varchar2(5000) := 'Select company_code company, sob_currency_code sob_curreny, k.meaning source, sum(amount) amount from ';

Line: 2571

     v_select1 Varchar2(10000) :=
     'Select company_code, sob_currency_code sob_currency, k.meaning source, sum( '
      ||' decode( '
      ||' nvl('
      ||''''
      ||p_crit_set.Discount
      ||''''
      ||', ''NONE''), '
      ||' ''NONE'', Amount, '
      ||' ''MAX'', Max_Discounted_Amount, '
      ||' ''MIN'', Min_Discounted_Amount '
      ||')'
      ||') amount from ';

Line: 2624

          v_select := v_select||v_tablename1;

Line: 2627

          v_select := v_select1||v_tablename2;

Line: 2687

          p_query  := v_Select||v_where||v_and||v_group||v_order;

Line: 2690

          p_query := v_Select||v_tablename1||v_where||v_and1||v_group||' union '||
                     v_Select1||v_tablename2||v_where||v_and2||v_group||v_order;

Line: 2738

    select count(*)
    into   n_dummy
    from   xtr_hedge_criteria
    where  criteria_set = p_criteria_set_name
    and    criteria_set_owner = p_criteria_set_owner;

Line: 2825

         delete all rows that belong to the matching set
   3) For every non null criteria add a row into xtr_hedge_criteria
      Note: date is saved relative to system date.
*/
PROCEDURE SAVE_CRITERIA_SET(p_crit_set CRITERIA_SET_REC_TYPE) IS

v_user_id     NUMBER := FND_GLOBAL.user_id;

Line: 2838

select created_by,creation_date
from   xtr_hedge_criteria
where  criteria_set = p_criteria_set
and    (criteria_set_owner = p_criteria_set_owner
        or (criteria_set_owner is null
            and p_criteria_set_owner is null
           )
       );

Line: 2855

      insert into xtr_hedge_criteria(hedge_criteria_id,
                                     criteria_type,
                                     criteria_code,
                                     operator,
                                     from_value,
                                     to_value,
                                     created_by,
                                     creation_date,
                                     last_updated_by,
                                     last_update_date,
                                     last_update_login,
                                     criteria_set,
                                     criteria_set_owner)
      values(xtr_hedge_criteria_s.nextval,
             p_type,
             p_code,
             p_operator,
             p_from,
             p_to,
             v_create_id,
             v_create_date,
             v_user_id,
             v_date,
             v_log_in_id,
             p_crit_set.criteria_set,
             p_crit_set.criteria_set_owner);

Line: 2893

    DELETE_CRITERIA_SET(p_crit_set);

Line: 2932

select criteria_code,operator,from_value
from   xtr_hedge_criteria
where  criteria_type='ADVANCED'
and    criteria_set = p_crit_set.criteria_set
and    (criteria_set_owner = p_crit_set.criteria_set_owner
        or (criteria_set_owner is null
            and p_crit_set.criteria_set_owner is null
           )
       );

Line: 2943

select from_value,to_value
from   xtr_hedge_criteria
where  criteria_type = 'BASIC'
and    criteria_code = p_criteria_code
and    criteria_set = p_crit_set.criteria_set
and    (criteria_set_owner = p_crit_set.criteria_set_owner
        or (criteria_set_owner is null
            and p_crit_set.criteria_set_owner is null
           )
       );

Line: 2995

/* DELETE_CRITERIA_SET removes all rows from xtr_hedge_criteria
   that match the set name and owner
*/
PROCEDURE DELETE_CRITERIA_SET(p_crit_set CRITERIA_SET_REC_TYPE) IS
BEGIN
  DELETE xtr_hedge_criteria
  WHERE  criteria_set = p_crit_set.criteria_set
  AND    (
          criteria_set_owner = p_crit_set.criteria_set_owner
          or
          (
           criteria_set_owner is null
           and
           p_crit_set.criteria_set_owner is null
          )
         );

Line: 3012

END DELETE_CRITERIA_SET;