ENGINATICS SYSTEM_ADMINISTRATOR SYSADMIN B28805F4F09E1687E0530100007FC83DY select trx.ledger, trx.operating_unit, trx.account accounting_flexfield, trx.vendor_name trading_partner, trx.vendor_number supplier_number, trx.txn_number invoice_number, trx.txn_type_lookup_code type, trx.internal_inv_number internal_invoice_number, trx.txn_date "Date", trx.due_date, trx.entered_amount invoice_amount, trx.open_entered_amount amount_due, trx.txn_currency_code currency, trx.exchange_rate, trx.open_accounted_amount open_functional_amount, trx.revaluation_rate, trx.revalued_amount open_revalued_amount, nvl(trx.open_accounted_amount,0) - nvl(trx.revalued_amount,0) "Profit/Loss", nvl2(trx.revaluation_rate,null,'*') no_revaluation_rate &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, (select nvl(aia.doc_sequence_value,aia.voucher_num) from ap_invoices_all aia where opit.txn_id=aia.invoice_id) internal_inv_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 ac.check_id in (select bk.check_id from ap_invoice_payments_all bk where aoi.txn_id=bk.invoice_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 aoi.txn_id=aip.invoice_id and xal.source_id=aip.invoice_payment_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 aoi.txn_id=aid.invoice_id and xal.source_id=aid.invoice_distribution_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 where 1=1 &il_where 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 Balances Revaluation Imported from BI Publisher Application: المدفوعات Source: حساب المدفوعات - تقرير إعادة تقييم الأرصدة المفتوحة Short Name: APOBRR DB package: AP_OPEN_BAL_REV_RPT_PKG D AP Open Balances Revaluation Imported from BI Publisher Description: Auswertung "KR Neubewertung Offene Salden" Application: Kreditoren Source: KR Neubewertung Offene Salden Short Name: APOBRR DB package: AP_OPEN_BAL_REV_RPT_PKG E AP Open Balances Revaluation Importado de BI Publisher Aplicación: Cuentas por pagar Fuente: Informe de revalorización de saldos pendientes AP Nombre corto: APOBRR Paquete DB: AP_OPEN_BAL_REV_RPT_PKG F AP Open Balances Revaluation Imported from BI Publisher Application: Payables Source: Etat de réévaluation des soldes AP Short Name: APOBRR DB package: AP_OPEN_BAL_REV_RPT_PKG I AP Open Balances Revaluation Imported from BI Publisher Application: Payables Source: Rapporto Rivalutazione saldi aperti AP Short Name: APOBRR DB package: AP_OPEN_BAL_REV_RPT_PKG JA AP Open Balances Revaluation BI Publisherからのインポート アプリケーション代金支払い 出典は、「APオープン残高再評価報告書」です。APオープン残高再評価報告書 略称です。APOBRR DBパッケージです。AP_OPEN_BAL_REV_RPT_PKG KO AP Open Balances Revaluation BI Publisher에서 가져옴 신청 : Payables 출처 : AP 미결 잔액 재평가 보고서 짧은 이름 : APOBRR DB 패키지 : AP_OPEN_BAL_REV_RPT_PKG PTB AP Open Balances Revaluation Importado da editora BI Aplicação: Contas a pagar Fonte: Relatório de reavaliação de saldos em aberto da AP Nome curto: APOBRR Pacote DB: AP_OPEN_BAL_REV_RPT_PKG RU AP Open Balances Revaluation Импортировано из Издательства БИ Приложение: Кредиторская задолженность Источник: AP Отчет о переоценке открытого сальдо Короткое имя: APOBRR БД-пакет: AP_OPEN_BAL_REV_RPT_PKG S AP Open Balances Revaluation Importerad från BI Publisher Ansökan: Skulder Källa: AP Open Balances Revaluation Report Kort namn: APOBRR DB-paket: AP_OPEN_BAL_REV_RPT_PKG TR AP Open Balances Revaluation BI Publisher'dan içe aktarıldı Uygulama: Borçlar Kaynak: AP Açık Bakiyeleri Yeniden Değerleme Raporu Kısa isim: APOBRR DB paketi: AP_OPEN_BAL_REV_RPT_PKG US AP Open Balances Revaluation Description: AP Open Balances Revaluation Report This report is in the same format as the AP Open Balances Revaluation Report which is no longer supported and should no loinger be used. The data source for this report is the same as the AP 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 Balances Revaluation Imported from BI Publisher Application: 应付帐款 Source: AP 未结余额重估报表 Short Name: APOBRR DB package: AP_OPEN_BAL_REV_RPT_PKG Enginatics R12 only &il_cols_outer &il_from &il_reg_columns &il_where &lp_cleared_items_clause1 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 -100 :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 2 -20 1=1 :India_Localization=:India_Localization Char Y xxen_util.jai_installed US India Localization 3 &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 4 &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 5 &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 6 &il_where and 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 7 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 8 20 :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 9 30 :p_due_date Date US Include Up to Due Date Include Up To Due Date 10 40 :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 11 50 :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 12 60 :p_daily_rate_date Date US Daily Rate Date Daily Rate Date 13 70 :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 14 80 :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 15 90 :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 16 100 :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 17 &lp_cleared_items_clause1 select null from dual N US Cleared only 18 &lp_cleared_items_clause1 and ac.status_lookup_code IN ('CLEARED','RECONCILED','CLEARED BUT UNACCOUNTED','RECONCILED UNACCOUNTED') Y US Cleared only 19 110 1=1 :p_include_dom_inv = 'N' and trx.txn_currency_code != (select gl.currency_code from gl_ledgers gl where gl.ledger_id = :g_ledger_id) 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 Y Y US Include Domestic Invoice Include Domestic Invoice 20 120 1=1 trx.txn_currency_code = :p_txn_currency_code LOV Oracle AP_SRS_CURRENCY Y select currency_code id, currency_code value, description description from fnd_currencies_vl where currency_flag='Y' and enabled_flag in ('Y','N') order by currency_code US Transaction Currency Transaction Currency to Revalue 21 130 1=1 trx.vendor_id = :p_vendor_id LOV Oracle AP_SRS_VENDOR_NAME Y select vendor_id id, vendor_name value, null description from po_vendors order by value,description US Supplier Supplier 367937A2128FCE597CACD23B5B6B9EAE Pivot: Default Template Y Summary by Ledger, Operating Unit, Account, Trading Partner with drilldown to transactions SYSADMIN 3 ACCOUNTING_FLEXFIELD 12 AMOUNT_DUE 23 CGST Amount 26 CGST Rate 13 CURRENCY 9 Date 10 Due Date 14 EXCHANGE_RATE 21 GST PAN No 20 GST Registration No 22 GST TAN No 30 HSN SAC Code 25 IGST Amount 28 IGST Rate 8 INTERNAL_INVOICE_NUMBER 11 INVOICE_AMOUNT 6 INVOICE_NUMBER 1 LEDGER 19 NO_REVALUATION_RATE 15 OPEN_FUNCTIONAL_AMOUNT 17 OPEN_REVALUED_AMOUNT 2 OPERATING_UNIT 18 Profit/Loss 16 REVALUATION_RATE 24 SGST Amount 27 SGST Rate 5 SUPPLIER_NUMBER 4 TRADING_PARTNER 7 TYPE 29 Unclassified Tax Amount ACCOUNTING_FLEXFIELD ROW 9 CURRENCY FILTER 2 LEDGER ROW 3 OPEN_FUNCTIONAL_AMOUNT VALUE 4 SUM OPEN_REVALUED_AMOUNT VALUE 6 SUM OPERATING_UNIT ROW 7 Profit/Loss VALUE 10 SUM TRADING_PARTNER ROW 12 S Site 367937A2128FCE597CACD23B5B6B9EAE