APPS.XTR_EXP_TRANSFERS_PKG SQL Statements

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

Line: 13

     select text
     from   xtr_sys_languages_vl
     where  item_name = p_Field_Name;

Line: 88

    select ACTION_CODE
      from XTR_AMOUNT_ACTIONS_V
       where DEAL_TYPE = p_deal_type
         and AMOUNT_TYPE = 'AMOUNT'
         and USER_ACTION_CODE = p_user_action_code;

Line: 121

      select deal_type from xtr_deal_types
	where user_deal_type = p_user_deal_type;

Line: 153

      select deal_subtype from xtr_deal_subtypes
	where user_deal_subtype = p_user_deal_subtype
	and deal_type = 'EXP'; --p_deal_type;  -- fails with different user deal types

Line: 183

	SELECT COUNT(*) FROM xtr_parties_v
	WHERE party_type='C' AND party_code=p_comp;

Line: 237

	SELECT COUNT(*) FROM XTR_EXPOSURE_TYPES_V
	WHERE company_code=p_comp
	AND exposure_type=p_exposure_type
	AND tax_brokerage_type IS NULL;

Line: 282

	select COUNT(*)
	from xtr_deal_subtypes_v
	where deal_type='EXP' and authorised='Y'
	and user_deal_subtype = p_deal_subtype;

Line: 318

	select COUNT(*)
	from   xtr_portfolios_v
	where  company_code = p_comp
	and    portfolio = p_portfolio
        and    nvl(cmf_yn,'N') = 'N'
        and    nvl(external_portfolio,'N') = 'N';

Line: 356

	select COUNT(*)
	from   xtr_amount_actions_v
	where  amount_type = 'AMOUNT'
	and    deal_type = 'EXP'
	and user_action_code = p_action;

Line: 392

	select COUNT(*)
	from   xtr_master_currencies_v
	where  currency = p_curr
	and    NVL(authorised,'N')='Y';

Line: 429

	select COUNT(*)
	from   xtr_bank_accounts_v
	where  party_code = p_comp
	and    currency = p_curr
	and account_number = p_comp_acct
	and NVL(authorised,'N') = 'Y';

Line: 504

	select COUNT(*)
	from   xtr_party_info_v
	where  party_code <> p_comp
	and    party_code = p_cparty
	and NVL(authorised,'N') = 'Y';

Line: 549

	select COUNT(*)
	from   xtr_bank_accounts_v
	where  party_code = p_cparty
	and    account_number = p_cparty_account_no
	and    currency = p_curr
	and NVL(authorised,'N') = 'Y';

Line: 584

   select COUNT(*)
   from xtr_dealer_codes_v
   where dealer_code = p_dealer_code;

Line: 609

	select COUNT(*)
	from   xtr_deal_linking_v
	where  deal_linking_code = p_deal_link_code;

Line: 685

      select home_currency
	from XTR_parties_V
	where party_code = p_company_code;

Line: 690

      select round(hce_rate,5)
	from XTR_master_currencies_V
	where currency = p_curr;

Line: 798

      g_main_rec.PURCHASING_MODULE := 'N'; --refer to PRE-INSERT trigger

Line: 799

      g_main_rec.SELECT_ACTION := NULL;

Line: 800

      g_main_rec.SELECT_REFERENCE := NULL;

Line: 809

      g_main_rec.UPDATED_BY := null;

Line: 810

      g_main_rec.UPDATED_ON := null;

Line: 833

      g_main_rec.PROGRAM_UPDATE_DATE := g_curr_date;

Line: 860

to insert the deal later on.
------------------------------------------------------------------------*/
procedure COPY_TO_EXP
	(ARec IN OUT NOCOPY XTR_EXPOSURE_TRANSACTIONS%rowtype) IS

BEGIN
   IF xtr_risk_debug_pkg.g_Debug THEN
      xtr_risk_debug_pkg.dpush('XTR_EXP_TRANSFERS.COPY_TO_EXP');

Line: 912

      g_main_rec.SELECT_ACTION := ARec.select_action;

Line: 913

      g_main_rec.SELECT_REFERENCE := ARec.select_reference;

Line: 922

      g_main_rec.UPDATED_BY := null;

Line: 923

      g_main_rec.UPDATED_ON := null;

Line: 946

      g_main_rec.PROGRAM_UPDATE_DATE := g_curr_date;

Line: 1276

      select XTR_EXPOSURE_TRANS_S.NEXTVAL
      from DUAL;

Line: 1302

      select ACCOUNT_NUMBER
      from  XTR_BANK_ACCOUNTS_V
      where PARTY_CODE = p_cparty_code
      and   CURRENCY   = p_curr
      and   BANK_SHORT_CODE = p_cparty_ref;

Line: 1333

    select dealer_code
    from   xtr_dealer_codes_v
    where  user_id = p_fnd_user;

Line: 1362

   INSERT INTO xtr_exposure_transactions (
	ACCOUNT_NO,
	ACTION_CODE,
	AMOUNT ,
	AMOUNT_HCE,
	AMOUNT_TYPE,
	ARCHIVE_BY,
	ARCHIVE_DATE,
	AUDIT_INDICATOR,
	AVG_RATE,
	BALANCE,
	BENEFICIARY_CODE,
	COMMENTS,
	COMPANY_CODE,
	CONTRA_NZD_AMOUNT,
	COVERED_BY_FX_CONTRACT ,
	CPARTY_CODE,
	CPARTY_ACCOUNT_NO, -- CE BANK MIGRATION
	CPARTY_REF,
	CREATED_BY,
	CREATED_ON,
	CURRENCY,
	DEAL_STATUS,
	DEAL_SUBTYPE,
	DEAL_TYPE,
	ESTIMATE_AMOUNT,
	ESTIMATE_DATE,
	EXPOSURE_TYPE,
	FIS_FOB,
	INTERMEDIARY_BANK_DETAILS,
	NZD_AMOUNT,
	PAYMENT_AMOUNT ,
	PAYMENT_STATUS ,
	PORTFOLIO_CODE ,
	PROFIT_LOSS,
	PURCHASING_MODULE,
	SELECT_ACTION,
	SELECT_REFERENCE,
	SETTLE_ACTION_REQD,
	STATUS_CODE,
	SUBSIDIARY_REF ,
	TAX_BROKERAGE_TYPE,
	THIRDPARTY_CODE,
	TRANSACTION_NUMBER,
	UPDATED_BY,
	UPDATED_ON,
	VALUE_DATE,
	WHOLESALE_REFERENCE,
	ATTRIBUTE_CATEGORY,
	ATTRIBUTE1,
	ATTRIBUTE2,
	ATTRIBUTE3,
	ATTRIBUTE4,
	ATTRIBUTE5,
	ATTRIBUTE6,
	ATTRIBUTE7,
	ATTRIBUTE8,
	ATTRIBUTE9,
	ATTRIBUTE10,
	ATTRIBUTE11,
	ATTRIBUTE12,
	ATTRIBUTE13,
	ATTRIBUTE14,
	ATTRIBUTE15,
	EXTERNAL_DEAL_ID,
	REQUEST_ID,
	PROGRAM_APPLICATION_ID ,
	PROGRAM_ID,
	PROGRAM_UPDATE_DATE,
	INTERNAL_COMMENTS,
	EXTERNAL_COMMENTS,
	DEAL_LINK_CODE,
	DUAL_AUTHORISATION_BY,
	DUAL_AUTHORISATION_ON,
	CASH_POSITION_EXPOSURE
	)
	VALUES (
	Arec_Exp.ACCOUNT_NO,
	Arec_Exp.ACTION_CODE,
	Arec_Exp.AMOUNT ,
	Arec_Exp.AMOUNT_HCE,
	Arec_Exp.AMOUNT_TYPE,
	Arec_Exp.ARCHIVE_BY,
	Arec_Exp.ARCHIVE_DATE,
	Arec_Exp.AUDIT_INDICATOR,
	Arec_Exp.AVG_RATE,
	Arec_Exp.BALANCE,
	Arec_Exp.BENEFICIARY_CODE,
	Arec_Exp.COMMENTS,
	Arec_Exp.COMPANY_CODE,
	Arec_Exp.CONTRA_NZD_AMOUNT,
	Arec_Exp.COVERED_BY_FX_CONTRACT ,
	Arec_Exp.CPARTY_CODE,
	Arec_Exp.CPARTY_ACCOUNT_NO,
	Arec_Exp.CPARTY_REF,
	Arec_Exp.CREATED_BY,
	Arec_Exp.CREATED_ON,
	Arec_Exp.CURRENCY,
	Arec_Exp.DEAL_STATUS,
	Arec_Exp.DEAL_SUBTYPE,
	Arec_Exp.DEAL_TYPE,
	Arec_Exp.ESTIMATE_AMOUNT,
	Arec_Exp.ESTIMATE_DATE,
	Arec_Exp.EXPOSURE_TYPE,
	Arec_Exp.FIS_FOB,
	Arec_Exp.INTERMEDIARY_BANK_DETAILS,
	Arec_Exp.NZD_AMOUNT,
	Arec_Exp.PAYMENT_AMOUNT ,
	Arec_Exp.PAYMENT_STATUS ,
	Arec_Exp.PORTFOLIO_CODE ,
	Arec_Exp.PROFIT_LOSS,
	Arec_Exp.PURCHASING_MODULE,
	Arec_Exp.SELECT_ACTION,
	Arec_Exp.SELECT_REFERENCE,
	Arec_Exp.SETTLE_ACTION_REQD,
	Arec_Exp.STATUS_CODE,
	Arec_Exp.SUBSIDIARY_REF ,
	Arec_Exp.TAX_BROKERAGE_TYPE,
	Arec_Exp.THIRDPARTY_CODE,
	Arec_Exp.TRANSACTION_NUMBER,
	Arec_Exp.UPDATED_BY,
	Arec_Exp.UPDATED_ON,
	Arec_Exp.VALUE_DATE,
	Arec_Exp.WHOLESALE_REFERENCE,
	Arec_Exp.ATTRIBUTE_CATEGORY,
	Arec_Exp.ATTRIBUTE1,
	Arec_Exp.ATTRIBUTE2,
	Arec_Exp.ATTRIBUTE3,
	Arec_Exp.ATTRIBUTE4,
	Arec_Exp.ATTRIBUTE5,
	Arec_Exp.ATTRIBUTE6,
	Arec_Exp.ATTRIBUTE7,
	Arec_Exp.ATTRIBUTE8,
	Arec_Exp.ATTRIBUTE9,
	Arec_Exp.ATTRIBUTE10,
	Arec_Exp.ATTRIBUTE11,
	Arec_Exp.ATTRIBUTE12,
	Arec_Exp.ATTRIBUTE13,
	Arec_Exp.ATTRIBUTE14,
	Arec_Exp.ATTRIBUTE15,
	Arec_Exp.EXTERNAL_DEAL_ID,
	Arec_Exp.REQUEST_ID,
	Arec_Exp.PROGRAM_APPLICATION_ID ,
	Arec_Exp.PROGRAM_ID,
	Arec_Exp.PROGRAM_UPDATE_DATE,
	Arec_Exp.INTERNAL_COMMENTS,
	Arec_Exp.EXTERNAL_COMMENTS,
	Arec_Exp.DEAL_LINK_CODE,
	l_dual_user,                     --Bug 2254853
	l_dual_date,                     --Bug 2254853
	Arec_Exp.CASH_POSITION_EXPOSURE
	);

Line: 1516

      UPDATE xtr_confirmation_details
      SET    confirmation_validated_by = l_dual_user,
	     confirmation_validated_on = l_dual_date
      WHERE  deal_type = 'EXP'
      AND    transaction_no = Arec_Exp.TRANSACTION_NUMBER;

Line: 1549

  select DEALER_CODE
  from XTR_DEALER_CODES_V
  where user_id = g_user_id;

Line: 1619

 insert into XTR_DEAL_DATE_AMOUNTS_V
        (deal_type,amount_type,date_type,product_type,
         deal_number,transaction_number,transaction_date,
         currency,amount,hce_amount,amount_date,
         cashflow_amount,company_code,account_no,action_code,
         cparty_account_no,cparty_code,status_code,settle,
         exp_settle_reqd,deal_subtype,portfolio_code,balance_sheet_exposure,
         dual_authorisation_by, dual_authorisation_on,
	 dealer_code, comments)
	-- bug 1849281
 values ('EXP','AMOUNT','VALUE',ARec_Exp.EXPOSURE_TYPE,
         0,ARec_Exp.TRANSACTION_NUMBER,
         nvl(ARec_Exp.VALUE_DATE,AREC_EXP.ESTIMATE_DATE),AREC_EXP.CURRENCY,
	 nvl(nvl(AREC_EXP.AMOUNT,AREC_EXP.ESTIMATE_AMOUNT),0),
         nvl(AREC_EXP.AMOUNT_HCE,nvl(nvl(AREC_EXP.AMOUNT,
	 AREC_EXP.ESTIMATE_AMOUNT),0)),
	 nvl(AREC_EXP.VALUE_DATE,AREC_EXP.ESTIMATE_DATE),
         decode(AREC_EXP.ACTION_CODE,'PAY',(-1),1)*v_cashflow_amount,
         AREC_EXP.COMPANY_CODE,AREC_EXP.ACCOUNT_NO,AREC_EXP.ACTION_CODE,
         v_cparty_account_no,
         AREC_EXP.THIRDPARTY_CODE,AREC_EXP.STATUS_CODE,'N',
         nvl(AREC_EXP.SETTLE_ACTION_REQD,'N'),AREC_EXP.DEAL_SUBTYPE,
         v_portfolio_code,v_balance_sheet_exposure,
         v_dual_user, v_dual_date, --Bug 2254853
	 v_dealer, v_comments);

Line: 1651

    insert into XTR_DEAL_DATE_AMOUNTS_V
        (deal_type,amount_type,date_type,product_type,
         deal_number,transaction_number,transaction_date,
         currency,amount,hce_amount,amount_date,
         cashflow_amount,company_code,account_no,action_code,
         cparty_account_no,cparty_code,status_code,settle,
         exp_settle_reqd,deal_subtype,portfolio_code,balance_sheet_exposure,
         dual_authorisation_by, dual_authorisation_on, dealer_code, comments)
	 -- bug 1849281
    values ('EXP','N/A','DEALT',ARec_Exp.EXPOSURE_TYPE,
         0,AREC_EXP.TRANSACTION_NUMBER,
         trunc(SYSDATE),AREC_EXP.CURRENCY,0,
         0,trunc(SYSDATE),
         0,AREC_EXP.COMPANY_CODE,NULL,NULL,NULL,
         AREC_EXP.THIRDPARTY_CODE,AREC_EXP.STATUS_CODE,'N',
         nvl(AREC_EXP.SETTLE_ACTION_REQD,'N'),AREC_EXP.DEAL_SUBTYPE,
         v_portfolio_code,v_balance_sheet_exposure,
         v_dual_user, v_dual_date,  --Bug 2254853
	 v_dealer, v_comments);

Line: 1712

     select dealer_code
     from   xtr_dealer_codes_v
     where  user_id = p_fnd_user;

Line: 1740

      delete from xtr_interface_errors
        where  external_deal_id = ARec_Interface.external_deal_id
        and    deal_type        = ARec_Interface.deal_type;

Line: 1800

   xtr_risk_debug_pkg.dlog('TRANSFER_EXP_DEALS: ' || 'After inserting to XTR_EXPOSURE_TRANSACTIONS');

Line: 1809

   xtr_risk_debug_pkg.dlog('TRANSFER_EXP_DEALS: ' || 'After inserting to XTR_DEAL_DATE_AMOUNTS');

Line: 1817

         delete from xtr_deals_interface
             where external_deal_id = ARec_Interface.external_deal_id
             and   deal_type        = ARec_Interface.deal_type;

Line: 1827

           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;

Line: 1864

     select dealer_code
     from   xtr_dealer_codes_v
     where  user_id = p_fnd_user;

Line: 1908

   xtr_risk_debug_pkg.dlog('TRANSFER_EXP_DEALS: ' || 'After inserting to XTR_EXPOSURE_TRANSACTIONS');

Line: 1915

   xtr_risk_debug_pkg.dlog('TRANSFER_EXP_DEALS: ' || 'After inserting to XTR_DEAL_DATE_AMOUNTS');