ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN 82288223F55F3869E053B46B63588994Y SELECT a.batch_id, a.ref_number Ref_Number, a.transaction_no Transaction_No, a.currencya Base_Ccy, a.currencyb Contra_Ccy, a.company_code Company, a.deal_type Deal_Type, decode(a.deal_type,'CA',NULL,'IG',NULL,a.deal_subtype) Deal_Subtype, a.product_type Product_type, a.portfolio_code Portfolio_Code, a.face_value/NVL(:P_UNIT,1000) Base_Amount, a.reval_ccy Reval_Ccy, a.effective_date Deal_End_Date, b.period_start Period_Start, b.period_end Period_End, a.exchange_rate_one Excahnge_Rate_One, a.exchange_rate_two Excahnge_Rate_Two, decode(nvl(:P_GROUPBY,'PORTFOLIO') ,'DEALTYPE',a.deal_type||a.deal_subtype||a.portfolio_code||a.product_type ,a.portfolio_code||a.deal_type||a.deal_subtype||a.product_type) Orderby_Columns, round(curr_gain_loss_amount,6)/nvl(:P_UNIT,1000) Gain_Loss, XTR_XTRCCYGL_XMLP_PKG.co_sht_nameformula(a.company_code) CO_SHT_NAME, XTR_XTRCCYGL_XMLP_PKG.report_prdformula(:C_DATEFORMAT) REPORT_PRD, XTR_XTRCCYGL_XMLP_PKG.sob_ccyformula(a.company_code) SOB_CCY, XTR_XTRCCYGL_XMLP_PKG.user_deal_subtypeformula(decode ( a.deal_type , 'CA' , NULL , 'IG' , NULL , a.deal_subtype ), a.deal_type) USER_DEAL_SUBTYPE, XTR_XTRCCYGL_XMLP_PKG.user_deal_typeformula(a.deal_type) USER_DEAL_TYPE, XTR_XTRCCYGL_XMLP_PKG.base_amt_rndformula(a.reval_ccy, a.face_value / NVL ( :P_UNIT , 1000 )) Base_Amt_Rnd, XTR_XTRCCYGL_XMLP_PKG.gain_loss_rndformula(a.reval_ccy, round ( curr_gain_loss_amount , 6 ) / nvl ( :P_UNIT , 1000 )) Gain_Loss_Rnd, XTR_XTRCCYGL_XMLP_PKG.fair_valueformula(a.company_code, a.ref_number, b.period_end, a.batch_id, a.deal_type) FAIR_VALUE, XTR_XTRCCYGL_XMLP_PKG.fair_value_rndformula(a.reval_ccy, XTR_XTRCCYGL_XMLP_PKG.fair_valueformula(a.company_code, a.ref_number, b.period_end, a.batch_id, a.deal_type)) FAIR_VALUE_RND, XTR_XTRCCYGL_XMLP_PKG.begin_rateformula(a.company_code, a.ref_number, b.period_start, a.deal_type) Begin_Rate, XTR_XTRCCYGL_XMLP_PKG.end_rateformula(a.company_code, a.ref_number, b.period_end, a.deal_type) End_Rate FROM xtr_revaluation_details_sum_v a, xtr_batches b WHERE a.batch_id = b.batch_id AND a.batch_id between nvl(:P_BATCH_ID_FROM,a.batch_id) and nvl(:P_BATCH_ID_TO,a.batch_id) AND ((b.period_start >= nvl(:P_DATE_FROM_T,b.period_start) and b.period_end <= nvl(:P_DATE_TO_T,b.period_end)) or :P_BATCH_ID_FROM is not null or :P_BATCH_ID_TO is not null ) AND a.curr_gain_loss_amount is not NULL AND a.reval_ccy = nvl(:P_CURRENCY, a.reval_ccy) AND a.deal_type = nvl(:P_DEAL_TYPE,a.deal_type) AND a.deal_type not in ('CA','IG','ONC') AND a.portfolio_code = nvl(:P_PORTFOLIO,a.portfolio_code) AND a.product_type = nvl(:P_PRODUCT_TYPE,a.product_type) AND a.company_code = nvl(:P_COMPANY,a.company_code) --AND a.realized_flag=nvl(:P_REALIZED_FLAG,'N') AND a.realized_flag=nvl(:P_REALIZED_FLAG_T,'N') AND b.upgrade_batch <> 'I' UNION ALL SELECT a.batch_id, a.ref_number Ref_Number, a.transaction_no Transaction_No, a.currencya Base_Ccy, a.currencyb Contra_Ccy, a.company_code Company, a.deal_type Deal_Type, decode(a.deal_type,'CA',NULL,'IG',NULL,a.deal_subtype) Deal_Subtype, a.product_type Product_type, a.portfolio_code Portfolio_Code, a.face_value/NVL(:P_UNIT,1000) Base_Amount, a.reval_ccy Reval_Ccy, a.effective_date Deal_End_Date, b.period_start Period_Start, b.period_end Period_End, a.exchange_rate_one Excahnge_Rate_One, a.exchange_rate_two Excahnge_Rate_Two, decode(nvl(:P_GROUPBY,'PORTFOLIO') ,'DEALTYPE',a.deal_type||a.deal_subtype||a.portfolio_code||a.product_type ,a.portfolio_code||a.deal_type||a.deal_subtype||a.product_type) Orderby_Columns, round(curr_gain_loss_amount,6)/nvl(:P_UNIT,1000) Gain_Loss, XTR_XTRCCYGL_XMLP_PKG.co_sht_nameformula(a.company_code) CO_SHT_NAME, XTR_XTRCCYGL_XMLP_PKG.report_prdformula(:C_DATEFORMAT) REPORT_PRD, XTR_XTRCCYGL_XMLP_PKG.sob_ccyformula(a.company_code) SOB_CCY, XTR_XTRCCYGL_XMLP_PKG.user_deal_subtypeformula(decode(a.deal_type,'CA',NULL,'IG',NULL,a.deal_subtype), a.deal_type) USER_DEAL_SUBTYPE, XTR_XTRCCYGL_XMLP_PKG.user_deal_typeformula(a.deal_type) USER_DEAL_TYPE, XTR_XTRCCYGL_XMLP_PKG.base_amt_rndformula(a.reval_ccy, a.face_value / NVL ( :P_UNIT , 1000 )) Base_Amt_Rnd, XTR_XTRCCYGL_XMLP_PKG.gain_loss_rndformula(a.reval_ccy, round ( curr_gain_loss_amount , 6 ) / nvl ( :P_UNIT , 1000 )) Gain_Loss_Rnd, XTR_XTRCCYGL_XMLP_PKG.fair_valueformula(a.company_code, a.ref_number, b.period_end, a.batch_id, a.deal_type) FAIR_VALUE, XTR_XTRCCYGL_XMLP_PKG.fair_value_rndformula(a.reval_ccy, XTR_XTRCCYGL_XMLP_PKG.fair_valueformula(a.company_code, a.ref_number, b.period_end, a.batch_id, a.deal_type)) FAIR_VALUE_RND, XTR_XTRCCYGL_XMLP_PKG.begin_rateformula(a.company_code, a.ref_number, b.period_start, a.deal_type) Begin_Rate, XTR_XTRCCYGL_XMLP_PKG.end_rateformula(a.company_code, a.ref_number, b.period_end, a.deal_type) End_Rate FROM xtr_revaluation_details_sum_v a, xtr_batches b WHERE a.batch_id = b.batch_id AND a.batch_id between nvl(:P_BATCH_ID_FROM,a.batch_id) and nvl(:P_BATCH_ID_TO,a.batch_id) AND ((b.period_start >= nvl(:P_DATE_FROM_T,b.period_start) and b.period_end <= nvl(:P_DATE_TO_T,b.period_end)) or :P_BATCH_ID_FROM is not null or :P_BATCH_ID_TO is not null ) AND a.curr_gain_loss_amount IS NOT NULL AND a.reval_ccy = nvl(:P_CURRENCY, a.reval_ccy) AND a.deal_type = nvl(:P_DEAL_TYPE,a.deal_type) AND a.deal_type in ('CA','IG','ONC') AND a.company_code = nvl(:P_COMPANY,a.company_code) --AND a.realized_flag=nvl(:P_REALIZED_FLAG,'N') AND a.realized_flag=nvl(:P_REALIZED_FLAG_T,'N') AND b.upgrade_batch <> 'I' ORDER BY company,reval_ccy, orderby_columns, ref_number,transaction_no,batch_id XTR XTRCCYGL_XML AR XTR المراكز - تقرير إعادة تقييم ربح/خسارة العملة (بتنسيق XML) Application: الخزانة Source: المراكز - تقرير إعادة تقييم ربح/خسارة العملة (بتنسيق XML) Short Name: XTRCCYGL_XML DB package: XTR_XTRCCYGL_XMLP_PKG D XTR Neubewertung Währungsgewinne/-verluste Application: Treasury Source: Positionen: Neubewertung Währungsgewinne/-verluste (XML) Short Name: XTRCCYGL_XML DB package: XTR_XTRCCYGL_XMLP_PKG F XTR Positions - Etat de réévaluation des écarts de change Application: Treasury Source: Positions - Etat de réévaluation des écarts de change (XML) Short Name: XTRCCYGL_XML DB package: XTR_XTRCCYGL_XMLP_PKG US XTR Positions - Currency Gain/Loss Revaluation Application: Treasury Source: Positions - Currency Gain/Loss Revaluation Report (XML) Short Name: XTRCCYGL_XML DB package: XTR_XTRCCYGL_XMLP_PKG ZHS XTR 头寸 - 币种损益重估报表 Application: 理财管理系统 Source: 头寸 - 币种损益重估报表 (XML) Short Name: XTRCCYGL_XML DB package: XTR_XTRCCYGL_XMLP_PKG BI Publisher :c_dateformat :p_batch_id_from :p_batch_id_to :p_company :p_conc_request_id :p_currency :p_date_from :p_date_from_t :p_date_to :p_date_to_t :p_deal_type :p_factor :p_groupby :p_portfolio :p_product_type :p_realized_flag :p_realized_flag_t :p_sql_trace :p_unit :p_user_company :p_user_date_from :p_user_date_to :p_user_deal_subtype :p_user_deal_type :p_user_factor :p_user_groupby :z1batch_id_from :z1batch_id_to :z1date_from :z1date_to :z1factor :z1para_grouping :z1real_unreal :z1rephead_real :z1rephead_unreal :z2amount :z2begin :z2buy :z2ccy :z2ccy_sht :z2company :z2deal_subtype :z2deal_type :z2end :z2end_of_report :z2fair_value :z2gain_loss :z2gl_rate :z2no_data_found :z2period :z2portfolio :z2prdend :z2product_type :z2realized_flag :z2reference :z2report_prd :z2reval_ccy :z2sell :z2sob_ccy :z2start :z2total :z2trans 1 10 :p_company LOV Oracle XTR_COMPANY Y select party_code id, party_code value, short_name description from xtr_parties_v where party_type='C' order by party_code AR الشركة D Firma F Société US Company ZHS 公司 2 20 :p_batch_id_from LOV Oracle XTR_BATCH_ID_FROM Y select batch_id id, batch_id value, null description from xtr_batches where company_code=nvl(:$flex$.xtr_company,company_code) and batch_id in (select batch_id from xtr_batch_events where event_code='REVAL') order by batch_id asc AR معرف المجموعة من D Von Stapelkennung F ID lot - De US Batch ID From ZHS 起始批标识 3 30 :p_batch_id_to LOV Oracle XTR_BATCH_ID_TO Y select batch_id id, batch_id value, null description from xtr_batches where company_code=nvl(:$flex$.xtr_company,company_code) and batch_id>=nvl(:$flex$.xtr_batch_id_from,batch_id) and batch_id in (select batch_id from xtr_batch_events where event_code='REVAL') order by batch_id asc AR معرف المجموعة إلى D Bis Stapelkennung F ID lot - A US Batch ID To ZHS 终止批标识 4 40 :p_date_from Date AR من تاريخ D Von Datum F Date de début US Date From ZHS 起始日期 5 50 :p_date_to Date AR إلى تاريخ D Bis Datum F Date de fin US Date To ZHS 日期至 6 60 :p_realized_flag LOV Oracle XTR_REALIZED_FLAG Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='XTR_MISC' and lookup_code in ('REAL','UNREAL') order by value,description select meaning from fnd_lookups where lookup_type = 'XTR_MISC' AND lookup_code = 'UNREAL' Y AR محقق أو غير محقق D Realisiert oder Nicht realisiert F Réalisé ou Non réalisé US Realized or Unrealized ZHS 已实现或未实现 7 70 :p_deal_type LOV Oracle XTR_DEAL_TYPE Y select deal_type id, user_deal_type value, name description from xtr_deal_types_v where authorised='Y' order by user_deal_type AR نوع الصفقة D Geschäftsart F Type d'opération US Deal Type ZHS 交易类型 8 80 :p_portfolio LOV Oracle XTR_PORTFOLIO Y select portfolio id, portfolio value, name description from xtr_portfolios_v where company_code=nvl(:$flex$.xtr_company,company_code) order by company_code AR سندات تجارية F Portefeuille US Portfolio ZHS 资产组合 9 90 :p_product_type LOV Oracle XTR_PRODUCT_TYPE Y select product_type id, product_type value, product_desc description from xtr_product_types_v where product_auth='Y' and deal_type=nvl(:$flex$.xtr_deal_type,deal_type) order by product_type AR نوع المنتج D Produktart F Type de produit US Product Type ZHS 产品类型 10 100 :p_currency LOV Oracle XTR_CURRENCY Y select currency id, currency value, name description from xtr_master_currencies_v where authorised='Y' order by currency AR العملة D Währung F Devise US Currency ZHS 币种 11 110 :p_groupby LOV Oracle XTR_REVAL_GROUP_BY Y select lookup_code id, meaning value, null description from fnd_lookups where lookup_type='XTR_MISC' and lookup_code in ('DEALTYPE','PORTFOLIO') order by value,description select meaning from fnd_lookups where lookup_type = 'XTR_MISC' AND lookup_code = 'PORTFOLIO' AR التجميع الأساسي D Primäre Gruppierung F Regroupement principal US Primary Grouping ZHS 主要分组 12 120 :p_factor LOV Oracle XTR_FACTOR Y select lookup_code id, meaning value, description description from fnd_lookups where lookup_type='XTR_MISC' and lookup_code in ('UNIT','THOUSAND','MILLION','BILLION') order by lookup_code select meaning from fnd_lookups where lookup_type = 'XTR_MISC' AND lookup_code = 'THOUSAND' Y AR المعامل D Faktor F Facteur US Factor ZHS 贴现