ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN B284620A00B765C5E0530100007F3335Y select trx.ledger ,trx.operating_unit ,trx.balancing_segment ,trx.account_segment ,trx.account "Accounting Flexfield" ,trx.account_description "Accouting Flexfield Desc." &lp_report_format_cols &il_reg_columns &il_cols_outer from (select (select sob.name from gl_ledgers sob where sob.ledger_id = :g_ledger_id) ledger, (select haou.name from hr_all_organization_units haou where haou.organization_id = :p_org_id) operating_unit, opit.balancing_segment, opit.account_segment, opit.code_combination_id, opit.account, fnd_flex_xml_publisher_apis.process_kff_combination_1('account_description', 'SQLGL', 'GL#', :p_coa_id, null, opit.code_combination_id, 'ALL', 'Y', 'DESCRIPTION') account_description, opit.party_id, opit.party_site_id, opit.vendor_id, opit.party_name vendor_name, opit.vendor_number, opit.vendor_site_id, opit.vendor_site_code, opit.txn_id, opit.txn_number, opit.txn_type_lookup_code, opit.txn_date, opit.txn_currency_code, opit.payment_currency_code pmt_currency_code, round(opit.txn_base_exchange_rate, 5) exchange_rate, opit.payment_cross_rate, nvl2(opit.revaluation_rate, to_char(round(opit.revaluation_rate, 5)), 'No Rate') revaluation_rate, opit.payment_status_flag, opit.entered_amount, opit.accounted_amount, opit.open_entered_amount, opit.open_accounted_amount, nvl(to_char(round(opit.open_entered_amount * opit.revaluation_rate, :g_base_precision)), '*') revalued_amount_dsp, round(opit.open_entered_amount * opit.revaluation_rate, :g_base_precision) revalued_amount, case when opit.revaluation_rate is null or opit.open_accounted_amount > round(opit.open_entered_amount * opit.revaluation_rate, :g_base_precision) then opit.open_accounted_amount else round(opit.open_entered_amount * opit.revaluation_rate, :g_base_precision) end open_revalued_amount, ap_open_items_reval_pkg.get_due_date(opit.txn_id, opit.txn_type_lookup_code) due_date from (select b.balancing_segment, b.account_segment, b.code_combination_id, b.account, b.party_id, b.party_site_id, b.party_name, b.vendor_id, b.vendor_number, b.vendor_site_id, b.vendor_site_code, b.txn_id, b.txn_number, b.txn_type_lookup_code, b.txn_date, b.txn_currency_code, b.payment_currency_code, b.txn_base_exchange_rate, b.payment_cross_rate, ap_open_items_reval_pkg.get_revaluation_rate(b.txn_currency_code, b.payment_cross_rate_type) revaluation_rate, b.payment_status_flag, b.entered_amount, b.accounted_amount, nvl(round((pay_cur_inv_entered_amt - payment_entered_amount)/b.payment_cross_rate, 2), b.entered_amount) open_entered_amount, nvl(round(round((pay_cur_inv_entered_amt - payment_entered_amount)/b.payment_cross_rate, 2) * b.txn_base_exchange_rate, :g_base_precision), b.accounted_amount) open_accounted_amount from (select /*+ leading (aoi aip) parallel(aoi)*/ distinct aoi.code_combination_id, aoi.party_id, aoi.party_site_id, aoi.vendor_id, aoi.vendor_number, aoi.vendor_site_id, aoi.txn_id invoice_id, aoi.txn_currency_code, aoi.payment_currency_code, aoi.txn_base_exchange_rate, aoi.payment_cross_rate, aoi.txn_type_lookup_code, --bug13613111 round(aoi.entered_amount * aoi.payment_cross_rate, 2) pay_cur_inv_entered_amt, sum((nvl(aip.amount, 0) + nvl(aip.discount_taken, 0))) payment_entered_amount from ap_open_items_reval_gt aoi, ap_invoice_payments_all aip, ap_checks_all ac where aip.invoice_id = aoi.txn_id and aip.set_of_books_id = :g_ledger_id and aip.org_id = :p_org_id and ac.check_id = aip.check_id and aip.accounting_date <= :g_revaluation_date and aoi.txn_type_lookup_code <> 'Payment' &lp_cleared_items_clause1 group by aoi.code_combination_id, aoi.party_id, aoi.party_site_id, aoi.vendor_id, aoi.vendor_number, aoi.vendor_site_id, aoi.txn_id , aoi.txn_currency_code, aoi.payment_currency_code, aoi.txn_base_exchange_rate, aoi.payment_cross_rate, aoi.entered_amount, aoi.txn_type_lookup_code ) a, ap_open_items_reval_gt b where b.txn_id = a.invoice_id(+) and b.txn_type_lookup_code = a.txn_type_lookup_code(+) and b.code_combination_id = a.code_combination_id(+) and nvl((a.pay_cur_inv_entered_amt - a.payment_entered_amount), b.entered_amount) <> 0 and decode(nvl(sign(abs(a.pay_cur_inv_entered_amt - a.payment_entered_amount)-1), 1),-1, decode(nvl(b.payment_status_flag, 'N'),'Y', 0, 1), 1) <> 0 and :p_transfer_to_gl_only = 'N' union select /*+ parallal b */distinct b.balancing_segment, b.account_segment, b.code_combination_id, b.account, b.party_id, b.party_site_id, b.party_name, b.vendor_id, b.vendor_number, b.vendor_site_id, b.vendor_site_code, b.txn_id, b.txn_number, b.txn_type_lookup_code, b.txn_date, b.txn_currency_code, b.payment_currency_code, b.txn_base_exchange_rate, b.payment_cross_rate, ap_open_items_reval_pkg.get_revaluation_rate(b.txn_currency_code, b.payment_cross_rate_type) revaluation_rate, b.payment_status_flag, b.entered_amount, b.accounted_amount, b.entered_amount - sum(nvl(a.entered_amount, 0)) open_entered_amount, b.accounted_amount - sum(nvl(a.accounted_amount, 0)) open_accounted_amount from (select /*+ parallel(aoi) leading(aoi)*/ distinct 200 application_id, null ref_ae_header_id, null temp_line_num, xah.ae_header_id, xal.ae_line_num, aoi.code_combination_id, aoi.account, aoi.txn_id invoice_id, aoi.txn_base_exchange_rate, aoi.txn_type_lookup_code, txn_amount invoice_amount, txn_base_amount invoice_base_amount, nvl(xal.entered_dr, 0) - nvl(xal.entered_cr, 0) entered_amount, nvl(xal.accounted_dr, 0) - nvl(xal.accounted_cr, 0) accounted_amount from ap_open_items_reval_gt aoi, ap_checks_all ac, xla_transaction_entities xte, xla_ae_headers xah, xla_ae_lines xal where 1=1 and ac.check_id in (select bk.check_id from ap_invoice_payments_all bk where bk.invoice_id = aoi.txn_id ) and nvl(xte.source_id_int_1, -99) = ac.check_id and nvl(xte.security_id_int_1, -99) = :p_org_id and xte.ledger_id = :g_ledger_id and xte.entity_code = 'AP_PAYMENTS' and xte.application_id = 200 and xah.entity_id = xte.entity_id and xah.ledger_id = :g_ledger_id and xah.gl_transfer_status_code = 'Y' and xah.event_type_code <> 'MANUAL' and xah.application_id = 200 and xah.accounting_date <= :g_revaluation_date and xah.upg_batch_id is not null and xal.ae_header_id = xah.ae_header_id and xal.code_combination_id = aoi.code_combination_id and xal.accounting_class_code= 'LIABILITY' and xal.application_id = 200 and ( ( xal.source_table = 'AP_INVOICE_PAYMENTS' and exists (select 1 from ap_invoice_payments_all aip where aip.invoice_id = aoi.txn_id and aip.invoice_payment_id = xal.source_id) ) or ( xal.source_table = 'AP_INVOICES' and xal.source_id = aoi.txn_id ) or ( xal.source_table = 'AP_INVOICE_DISTRIBUTIONS' and exists (select 1 from ap_invoice_distributions_all aid where aid.invoice_id = aoi.txn_id and aid.invoice_distribution_id = xal.source_id) ) ) and aoi.txn_type_lookup_code <> 'Payment' and :p_transfer_to_gl_only = 'Y' &lp_cleared_items_clause1 union select /*+ leading (aoi aip xte xah xal xdl) parallel(aoi)*/ distinct xdl.application_id, xdl.ref_ae_header_id, xdl.temp_line_num, xdl.ae_header_id, null ae_line_num, aoi.code_combination_id, aoi.account, aip.invoice_id, aoi.txn_base_exchange_rate, aoi.txn_type_lookup_code, txn_amount invoice_amount, txn_base_amount invoice_base_amount, nvl(xdl.unrounded_entered_dr, 0) - nvl(xdl.unrounded_entered_cr, 0) entered_amount, nvl(xdl.unrounded_accounted_dr, 0) - nvl(xdl.unrounded_accounted_cr, 0) accounted_amount from ap_open_items_reval_gt aoi, ap_invoice_payments_all aip, ap_checks_all ac, xla_transaction_entities xte, xla_ae_headers xah, xla_ae_lines xal, xla_distribution_links xdl where aip.invoice_id = aoi.txn_id and nvl(xte.source_id_int_1, -99) = aip.check_id and nvl(xte.security_id_int_1, -99) = :p_org_id and aip.set_of_books_id = :g_ledger_id and aip.org_id = :p_org_id and xte.ledger_id = :g_ledger_id and ac.check_id = aip.check_id and xah.entity_id = xte.entity_id and xah.ledger_id = :g_ledger_id and xah.gl_transfer_status_code = 'Y' and xah.event_type_code <> 'MANUAL' and xah.application_id = 200 and xte.application_id = 200 and xte.entity_code = 'AP_PAYMENTS' and xah.accounting_date <= :g_revaluation_date and xah.upg_batch_id is null and xal.ae_header_id = xah.ae_header_id and xdl.ae_header_id = xah.ae_header_id and xdl.ae_line_num = xal.ae_line_num and xal.code_combination_id = aoi.code_combination_id and xdl.applied_to_source_id_num_1 = aip.invoice_id and xal.accounting_class_code = 'LIABILITY' and xdl.applied_to_entity_code = 'AP_INVOICES' and xdl.application_id = 200 and aoi.txn_type_lookup_code <> 'Payment' &lp_cleared_items_clause1 ) a, ap_open_items_reval_gt b where b.txn_id = a.invoice_id(+) and b.txn_type_lookup_code = a.txn_type_lookup_code(+) and b.code_combination_id = a.code_combination_id(+) and :p_transfer_to_gl_only = 'Y' group by b.balancing_segment, b.account_segment, b.code_combination_id, b.account, b.party_id, b.party_site_id, b.party_name, b.vendor_id, b.vendor_number, b.vendor_site_id, b.vendor_site_code, b.txn_id, b.txn_number, b.txn_type_lookup_code, b.txn_date, b.txn_currency_code, b.payment_currency_code, b.txn_base_exchange_rate, b.payment_cross_rate, b.payment_cross_rate_type, b.payment_status_flag, b.entered_amount, b.accounted_amount having b.entered_amount <> sum(nvl(a.entered_amount, 0)) or b.accounted_amount <> sum(nvl(a.accounted_amount, 0)) ) opit order by balancing_segment, account_segment, account, party_name, vendor_number, vendor_site_code, txn_number, txn_type_lookup_code, txn_date ) trx &il_from &il_where &lp_group_by_cols Added India GST columns, displayed only when India Localization is installed. SQLAP APOPITRN #,##0.00;(#,##0.00) (:Rate_Type = 'DAILY' and (:Daily_Rate_Type is not null and :Daily_Rate_Date is not null)) or (:Rate_Type = 'PERIOD') AR AP Open Items Revaluation Imported from BI Publisher Description: تقرير إعادة تقييم البنود المفتوحة Application: المدفوعات Source: تقرير إعادة تقييم البنود المفتوحة (بتنسيق XML) Short Name: APOPITRN DB package: AP_OPEN_ITEMS_REVAL_PKG D AP Open Items Revaluation Imported from BI Publisher Description: Offene Posten - Neubewertung Application: Kreditoren Source: Offene Posten - Neubewertung (XML) Short Name: APOPITRN DB package: AP_OPEN_ITEMS_REVAL_PKG E AP Open Items Revaluation Importado de BI Publisher Descripción: Informe de revalorización de partidas abiertas Aplicación: Acreedores Fuente: Informe de revalorización de partidas abiertas (XML) Nombre corto: APOPITRN Paquete DB: AP_OPEN_ITEMS_REVAL_PKG F AP Open Items Revaluation Imported from BI Publisher Description: Etat de réévaluation des mouvements non soldés Application: Payables Source: Etat de réévaluation des mouvements non soldés (XML) Short Name: APOPITRN DB package: AP_OPEN_ITEMS_REVAL_PKG I AP Open Items Revaluation Imported from BI Publisher Description: Rapporto Rivalutazione voci aperte Application: Payables Source: Rapporto Rivalutazione voci aperte (XML) Short Name: APOPITRN DB package: AP_OPEN_ITEMS_REVAL_PKG JA AP Open Items Revaluation BI Publisherからのインポート 内容を記載しています。オープンアイテム再評価報告書 アプリケーション代金支払い 出典はこちらです。オープンアイテム再評価報告書(XML) 略称：APOPITRNアポピトルン DBパッケージです。AP_OPEN_ITEMS_REVAL_PKG KO AP Open Items Revaluation BI Publisher에서 가져옴 설명 : 미결 항목 재평가 보고서 신청 : Payables 출처 : 미결 항목 재평가 보고서 (XML) 짧은 이름 : APOPITRN DB 패키지 : AP_OPEN_ITEMS_REVAL_PKG PTB AP Open Items Revaluation Importado da editora BI Descrição: Relatório de reavaliação de partidas em aberto Aplicação: Contas a pagar Fonte: Relatório de reavaliação de partidas em aberto (XML) Nome curto: APOPITRN Pacote DB: AP_OPEN_ITEMS_REVAL_PKG RU AP Open Items Revaluation Импортировано из Издательства БИ Описание: Отчет о переоценке открытых позиций Приложение: Кредиторская задолженность Источник: Отчет о переоценке открытых позиций (XML) Короткое имя: АПОПИТРН БД-пакет: AP_OPEN_ITEMS_REVAL_PKG S AP Open Items Revaluation Importerad från BI Publisher Beskrivning: Omvärderingsrapport om öppna artiklar Ansökan: Skulder Källa: Revalueringsrapport om öppna poster (XML) Kort namn: APOPITRN DB-paket: AP_OPEN_ITEMS_REVAL_PKG TR AP Open Items Revaluation BI Publisher'dan içe aktarıldı Açıklama: Kalemler Yeniden Değerleme Raporunu Aç Uygulama: Borçlar Kaynak: Açık Öğeler Yeniden Değerleme Raporu (XML) Kısa isim: APOPITRN DB paketi: AP_OPEN_ITEMS_REVAL_PKG US AP Open Items Revaluation Imported from BI Publisher Description: Open Items Revaluation Report Application: Payables Source: Open Items Revaluation Report (XML) Short Name: APOPITRN DB package: AP_OPEN_ITEMS_REVAL_PKG When India Localization is installed, the following GST columns are also included: GST Registration No, GST PAN No, GST TAN No, CGST Amount, SGST Amount, IGST Amount, CGST Rate, SGST Rate, IGST Rate, Custom Amount, Unclassified Tax Amount, Taxable Value, HSN SAC Code. Please Enter a Daily Rate Type and Daily Rate Date when using a Rate Type of Daily ZHS AP Open Items Revaluation Imported from BI Publisher Description: 未结项目重估报表 Application: 应付帐款 Source: 未结项目重估报表 (XML) Short Name: APOPITRN DB package: AP_OPEN_ITEMS_REVAL_PKG Enginatics R12 only &il_cols_outer &il_from &il_reg_columns &il_where &lp_cleared_items_clause1 &lp_group_by_cols &lp_report_format_cols 1=1 :g_base_currency_code :g_base_currency_desc :g_base_min_acct_unit :g_base_precision :g_cleared_only_dsp :g_coa_id :g_daily_rate_error :g_daily_rate_information :g_daily_rate_type_dsp :g_eur_code :g_gl_name_dsp :g_ledger_id :g_operating_unit_dsp :g_rate_type_dsp :g_revaluation_date :g_sql_statement :g_trans_to_gl_only_dsp :p_cleared_only :p_coa_id :p_daily_rate_date :p_daily_rate_type :p_debug_switch :p_due_date :p_from_balancing_segment :p_org_id :p_rate_type_lookup_code :p_revaluation_period :p_to_balacing_segment :p_trace_switch :p_transfer_to_gl_only 1 -40 1=1 :India_Localization=:India_Localization Char Y xxen_util.jai_installed US India Localization 2 &il_cols_outer ,il_tax.cgst_amount "CGST Amount" ,il_tax.sgst_amount "SGST Amount" ,il_tax.igst_amount "IGST Amount" ,il_tax.cgst_tax_rate "CGST Rate" ,il_tax.sgst_tax_rate "SGST Rate" ,il_tax.igst_tax_rate "IGST Rate", il_tax.custom_amount, il_tax.unclassified_amount "Unclassified Tax Amount", il_tax.taxable_value, il_tax.hsn_sac_code "HSN SAC Code" Y US India Localization 3 &il_from ,(select jpr.party_id, jpr.party_site_id, coalesce(max(case when jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.registration_type_code in ('GST','GSTIN') then nvl(jprl.registration_number,jprl.secondary_registration_number) end),max(case when regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') then nvl(jprl.registration_number,jprl.secondary_registration_number) end)) gstin, max(decode(jprl.registration_type_code,'PAN',jprl.registration_number)) pan_site, max(decode(jprl.registration_type_code,'TAN',jprl.registration_number)) tan_site, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_site, max(decode(jprl.sec_registration_type_code,'TAN',jprl.secondary_registration_number)) sec_tan_site from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY_SITE' and jpr.supplier_flag='Y' and jprl.effective_from<=sysdate and (jprl.effective_to is null or jprl.effective_to>=sysdate) group by jpr.party_id, jpr.party_site_id) il_reg_site ,(select jpr.party_id, coalesce(max(case when jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') and jprl.registration_type_code in ('GST','GSTIN') then nvl(jprl.registration_number,jprl.secondary_registration_number) end),max(case when regexp_like(nvl(jprl.registration_number,jprl.secondary_registration_number),'^[0-9]{2}[A-Z]{5}[0-9]{4}[A-Z][0-9A-Z]Z[0-9A-Z]$') and jprl.regime_id in (select jr.regime_id from jai_regimes jr where jr.regime_type='T') then nvl(jprl.registration_number,jprl.secondary_registration_number) end)) gstin_hdr, max(decode(jprl.registration_type_code,'PAN',jprl.registration_number)) pan_hdr, max(decode(jprl.registration_type_code,'TAN',jprl.registration_number)) tan_hdr, max(decode(jprl.sec_registration_type_code,'PAN',jprl.secondary_registration_number)) sec_pan_hdr, max(decode(jprl.sec_registration_type_code,'TAN',jprl.secondary_registration_number)) sec_tan_hdr from jai_party_regs_v jpr, jai_party_reg_lines_v jprl where jpr.party_reg_id=jprl.party_reg_id and jpr.party_type_code='THIRD_PARTY' and jpr.supplier_flag='Y' and jprl.effective_from<=sysdate and (jprl.effective_to is null or jprl.effective_to>=sysdate) group by jpr.party_id) il_reg_hdr ,(select jtlv.trx_id, sum(decode(jrav.reporting_code,'CGST',jtlv.rounded_tax_amt_trx_curr)) cgst_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.rounded_tax_amt_trx_curr)) sgst_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.rounded_tax_amt_trx_curr)) igst_amount, sum(decode(jrav.reporting_code,'CESS',jtlv.rounded_tax_amt_trx_curr)) cess_amount, max(decode(jrav.reporting_code,'CGST',jtlv.tax_rate_code)) cgst_tax_rate, max(decode(jrav.reporting_code,'SGST',jtlv.tax_rate_code)) sgst_tax_rate, max(decode(jrav.reporting_code,'IGST',jtlv.tax_rate_code)) igst_tax_rate, max(decode(jrav.reporting_code,'CESS',jtlv.tax_rate_code)) cess_tax_rate, sum(decode(jrav.reporting_code,'CGST',jtlv.rec_tax_amt_trx_curr)) cgst_rec_amount, sum(decode(jrav.reporting_code,'CGST',jtlv.nrec_tax_amt_trx_curr)) cgst_nrec_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.rec_tax_amt_trx_curr)) sgst_rec_amount, sum(decode(jrav.reporting_code,'SGST',jtlv.nrec_tax_amt_trx_curr)) sgst_nrec_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.rec_tax_amt_trx_curr)) igst_rec_amount, sum(decode(jrav.reporting_code,'IGST',jtlv.nrec_tax_amt_trx_curr)) igst_nrec_amount, max(jtlv.self_assessed_flag) reverse_charge_flag, sum(decode(jrav.reporting_code,'CUSTOM',jtlv.rounded_tax_amt_trx_curr)) custom_amount, sum(decode(jrav.reporting_code,null,jtlv.rounded_tax_amt_trx_curr)) unclassified_amount, sum(decode(jrav.reporting_code,'CGST',jtlv.rounded_taxable_amt_trx_curr,'IGST',jtlv.rounded_taxable_amt_trx_curr)) taxable_value, max(jrc.reporting_code) hsn_sac_code from jai_tax_lines_v jtlv, jai_reporting_associations_v jrav, jai_tax_det_factors jtdf, jai_reporting_codes jrc where jrav.entity_id(+)=jtlv.tax_type_id and jrav.entity_code(+)='TAX_TYPE' and jrav.reporting_type_code(+)='TAX_TYPES_CLASSIFICATION' and jtlv.det_factor_id=jtdf.det_factor_id(+) and jtdf.application_id(+)=200 and nvl(jtdf.hsn_code_id,jtdf.sac_code_id)=jrc.reporting_code_id(+) and jtlv.application_id=200 group by jtlv.trx_id ) il_tax Y US India Localization 4 &il_reg_columns ,nvl(il_reg_site.gstin,il_reg_hdr.gstin_hdr) "GST Registration No", nvl(il_reg_site.pan_site,nvl(il_reg_site.sec_pan_site,nvl(il_reg_hdr.pan_hdr,il_reg_hdr.sec_pan_hdr))) "GST PAN No", nvl(il_reg_site.tan_site,nvl(il_reg_site.sec_tan_site,nvl(il_reg_hdr.tan_hdr,il_reg_hdr.sec_tan_hdr))) "GST TAN No" Y US India Localization 5 &il_where where trx.vendor_site_id=il_reg_site.party_site_id(+) and trx.vendor_id=il_reg_hdr.party_id(+) and trx.txn_id=il_tax.trx_id(+) Y US India Localization 6 -20 :p_coa_id Char select sb.chart_of_accounts_id from gl_sets_of_books sb, ap_system_parameters sp where sb.set_of_books_id = sp.set_of_books_id US Chart of Accounts Id 7 -10 :p_hungarian_rules LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description N US Hungarian Rules 8 10 :p_org_id LOV Oracle FND_MO_OU Y select organization_id id, organization_name value, null description from mo_glob_org_access_tmp order by value,description coalesce(xxen_util.default_operating_unit,xxen_util.previous_parameter_value(:parameter_id)) Y US Operating Unit Operating Unit 9 20 &lp_report_format_cols ,trx.vendor_name "Trading Partner" ,trx.vendor_number "Supplier Number" ,trx.vendor_site_code "Supplier Site" ,trx.txn_number "Transaction Number" ,trx.txn_type_lookup_code "Type" ,trx.txn_date "Date" ,trx.due_date "Due Date" ,trx.txn_currency_code "Currency" ,trx.open_entered_amount "Open Original Amount" ,trx.exchange_rate "Exchange Rate" ,trx.open_accounted_amount "Open Functional Amount (A)" ,trx.revaluation_rate "Revaluation Rate" ,trx.revalued_amount "Revalued Amount (B)" ,(nvl(trx.revalued_amount,0) - nvl(trx.open_accounted_amount,0)) "Diff (B-A)" ,trx.open_revalued_amount "Open Revalued Amount (C)" ,(nvl(open_revalued_amount,0) - nvl(trx.open_accounted_amount,0)) "Diff (C-A)" ,nvl2(trx.revaluation_rate,null,'*') "No Revaluation Rate" LOV Oracle AR_REPORT_TYPE Y select lookup_code id, meaning value, null description from ar_lookups where lookup_type='REPORT_TYPE_SD' and enabled_flag='Y' and sysdate between nvl(start_date_active,sysdate) and nvl(end_date_active,sysdate) order by meaning,lookup_code D D Y US Report Format 10 &lp_report_format_cols ,sum(trx.open_accounted_amount) "Open Functional Amount (A)" ,sum(trx.revalued_amount) "Revalued Amount (B)" ,sum(nvl(trx.revalued_amount,0) - nvl(trx.open_accounted_amount,0)) "Diff (B-A)" ,sum(trx.open_revalued_amount) "Open Revalued Amount (C)" ,sum(nvl(open_revalued_amount,0) - nvl(trx.open_accounted_amount,0)) "Diff (C-A)" ,max(nvl2(trx.revaluation_rate,null,'*')) "No Revaluation Rate" S US Report Format 11 &lp_group_by_cols select null from dual D US Report Format 12 &lp_group_by_cols group by trx.ledger, trx.operating_unit, trx.balancing_segment, trx.account_segment, trx.account, trx.account_description S US Report Format 13 30 :p_revaluation_period LOV Oracle AP_SRS_REV_PERIOD_NAMES_OIR Y select gps.period_name id, gps.period_name value, gsob.name description from gl_period_statuses gps,gl_sets_of_books gsob where gps.set_of_books_id in (select distinct set_of_books_id from ap_system_parameters where org_id=nvl(:$flex$.fnd_mo_ou:null,org_id)) and gps.set_of_books_id=gsob.set_of_books_id and gps.application_id=200 order by gps.period_year desc,gps.period_num desc Y US Revaluation Period Revaluation Period 14 40 :p_due_date Date US Include Up to Due Date Include Up To Due Date 15 50 :p_rate_type_lookup_code LOV Oracle AP_SRS_APXINREV_RATE_TYPE Y select lookup_code id, displayed_field value, null description from ap_lookup_codes where lookup_type='APXINREV_RATE_TYPE' order by value,description PERIOD Y US Rate Type Rate Type 16 60 :p_daily_rate_type LOV Oracle AP_SRS_APXINREV_DAILY_RATE_TYPE Y select conversion_type id, user_conversion_type value, null description from gl_daily_conversion_types where conversion_type<>'User' order by upper (user_conversion_type) US Daily Rate Type Daily Rate Type 17 70 :p_daily_rate_date Date US Daily Rate Date Daily Rate Date 18 80 :p_from_balancing_segment LOV GL Balancing F26C78167A214EFBE0530100007F336B Y select substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_merge*/ nvl(min(decode(fifs.security_enabled_flag,'N',fifs.id_flex_num)),min(fifs.id_flex_num)) id_flex_num, fifs.application_column_name from fnd_id_flex_segments fifs where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.enabled_flag='Y' and (fifs.application_id, fifs.id_flex_code, fifs.id_flex_num, fifs.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_BALANCING' and fsav.attribute_value='Y') and (:$flex$.chart_of_accounts_id is not null or :$flex$.chart_of_accounts is not null or :$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null or :$flex$.book is not null) and fifs.id_flex_num in ( select /*+ no_unnest*/ fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and ( fifsv.id_flex_num=:$flex$.chart_of_accounts_id or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y' or (:$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null) and fifsv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) ) ) or fifsv.id_flex_num in (select fbc.accounting_flex_structure from fa_book_controls fbc where xxen_util.contains(:$flex$.book,fbc.book_type_code)='Y') ) ) group by fifs.flex_value_set_id, fifs.application_column_name ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>s.application_column_name, p_enabled_only=>'N' )) l order by 1 US Balancing Segment Low Balancing Segment Low 19 90 :p_to_balacing_segment LOV GL Balancing F26C78167A214EFBE0530100007F336B Y select substrb(l.column_value,instrb(l.column_value,'~',1,1)+1,instrb(l.column_value,'~',1,2)-instrb(l.column_value,'~',1,1)-1) value, substrb(l.column_value,instrb(l.column_value,'~',1,2)+1) description from ( select /*+ no_merge*/ nvl(min(decode(fifs.security_enabled_flag,'N',fifs.id_flex_num)),min(fifs.id_flex_num)) id_flex_num, fifs.application_column_name from fnd_id_flex_segments fifs where fifs.application_id=101 and fifs.id_flex_code='GL#' and fifs.enabled_flag='Y' and (fifs.application_id, fifs.id_flex_code, fifs.id_flex_num, fifs.application_column_name) in (select fsav.application_id, fsav.id_flex_code, fsav.id_flex_num, fsav.application_column_name from fnd_segment_attribute_values fsav where fsav.segment_attribute_type='GL_BALANCING' and fsav.attribute_value='Y') and (:$flex$.chart_of_accounts_id is not null or :$flex$.chart_of_accounts is not null or :$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null or :$flex$.book is not null) and fifs.id_flex_num in ( select /*+ no_unnest*/ fifsv.id_flex_num from fnd_id_flex_structures_vl fifsv where fifsv.application_id=101 and fifsv.id_flex_code='GL#' and ( fifsv.id_flex_num=:$flex$.chart_of_accounts_id or xxen_util.contains(:$flex$.chart_of_accounts,fifsv.id_flex_structure_name)='Y' or (:$flex$.operating_unit is not null or :$flex$.ledger is not null or :$flex$.ledger_category is not null) and fifsv.id_flex_num in ( select gl.chart_of_accounts_id from gl_ledgers gl where (:$flex$.operating_unit is null or gl.ledger_id in (select hou.set_of_books_id from hr_operating_units hou where xxen_util.contains(:$flex$.operating_unit,hou.name)='Y')) and (:$flex$.ledger is null or xxen_util.contains(:$flex$.ledger,gl.name)='Y') and (:$flex$.ledger_category is null or gl.ledger_category_code=xxen_util.lookup_code(:$flex$.ledger_category,'GL_ASF_LEDGER_CATEGORY',101,'Y') and gl.ledger_id in (select nvl(glsnav.ledger_id,gasna.ledger_id) from gl_access_set_norm_assign gasna, gl_ledger_set_norm_assign_v glsnav where gasna.access_set_id=fnd_profile.value('GL_ACCESS_SET_ID') and gasna.ledger_id=glsnav.ledger_set_id(+)) ) ) or fifsv.id_flex_num in (select fbc.accounting_flex_structure from fa_book_controls fbc where xxen_util.contains(:$flex$.book,fbc.book_type_code)='Y') ) ) group by fifs.flex_value_set_id, fifs.application_column_name ) s, table(xxen_util.kff_segment_lov( p_application_id=>101, p_id_flex_code=>'GL#', p_id_flex_num=>s.id_flex_num, p_application_column_name=>s.application_column_name, p_enabled_only=>'N' )) l order by 1 :$flex$.Balancing_Segment_Low US Balancing Segment High Balancing Segment High 20 100 :p_transfer_to_gl_only LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y US Transferred to GL only Apply Payments and acknowledge open items only if transferred to GL 21 110 :p_cleared_only LOV Oracle Yes_No Y select lookup_code id, meaning value, null description from fnd_lookups where fnd_lookups.lookup_type='YES_NO' order by value,description Y Y US Cleared only Apply payments and acknowledge open items only if they are cleared 22 &lp_cleared_items_clause1 select null from dual N US Cleared only 23 &lp_cleared_items_clause1 and ac.status_lookup_code IN ('CLEARED','RECONCILED','CLEARED BUT UNACCOUNTED','RECONCILED UNACCOUNTED') Y US Cleared only 1C1B6FF2868A01A6748CCA0DFEC90008 Pivot: Default Template Y Summary by Ledger, Operating Unit,Account, Trading Partner with drilldown to Transactions SYSADMIN 4 ACCOUNT_SEGMENT 5 Accounting Flexfield 6 Accouting Flexfield Desc. 3 BALANCING_SEGMENT 27 CGST Amount 30 CGST Rate 14 Currency 12 Date 20 Diff (B-A) 22 Diff (C-A) 13 Due Date 16 Exchange Rate 25 GST PAN No 24 GST Registration No 26 GST TAN No 34 HSN SAC Code 29 IGST Amount 32 IGST Rate 1 LEDGER 23 No Revaluation Rate 2 OPERATING_UNIT 17 Open Functional Amount (A) 15 Open Original Amount 21 Open Revalued Amount (C) 18 Revaluation Rate 19 Revalued Amount (B) 28 SGST Amount 31 SGST Rate 8 Supplier Number 9 Supplier Site 7 Trading Partner 10 Transaction Number 11 Type 33 Unclassified Tax Amount Accounting Flexfield ROW 9 Currency FILTER 2 Diff (B-A) VALUE 10 SUM Diff (C-A) VALUE 15 SUM LEDGER ROW 3 OPERATING_UNIT ROW 6 Open Functional Amount (A) VALUE 4 SUM Open Revalued Amount (C) VALUE 12 SUM Revalued Amount (B) VALUE 7 SUM Trading Partner ROW 13 S Site 1C1B6FF2868A01A6748CCA0DFEC90008